<?php
require_once( '../wp-load.php' );

$bundle_data[] = array(
	'bundle_id' => 'Bundle ID',
	'title' => 'Bundle Title',
	'desc' => 'Bundle Description',
	'product_names' => 'Product Names',
	'product_ids' => 'Product IDs',
	'group' => 'HW Package Group',
	'appname' => 'Application Names',
	'appid' => 'Application IDs',
	'advfeatname' => 'Advanced Features',
	'advfeatid' => 'Advanced Feature IDs',
);
$sql = "SELECT P.ID, P.post_title, P.post_content, BD.meta_value as bundle_data, AA.meta_value as assoc_apps, AAF.meta_value as assoc_adv_features
FROM wp_posts P
LEFT JOIN wp_term_relationships ON (P.ID = wp_term_relationships.object_id)
LEFT JOIN wp_postmeta BD ON P.ID = BD.post_id and BD.meta_key = '_bundle_data'
LEFT JOIN wp_postmeta AA ON P.ID = AA.post_id and AA.meta_key = 'assoc_apps'
LEFT JOIN wp_postmeta AAF ON P.ID = AAF.post_id and AAF.meta_key = 'assoc_advanced_features'
WHERE ( wp_term_relationships.term_taxonomy_id IN (1588) )
AND P.post_type = 'product'
AND (P.post_status IN ('publish','future','draft','pending','private'))
ORDER BY P.post_title ASC;";
$bundles = $wpdb->get_results($sql);

foreach ($bundles as $bundle) {
	$term_names = $product_ids = $product_names = $assoc_app_ids = $assoc_app_names = $adv_feat_ids = $adv_feat_names = array();

	$terms = get_the_terms( $bundle->ID, 'hardware_package' );
	if( !empty($terms) ) :
		foreach( $terms as $term ) :
			$term_names[] = $term->name;
		endforeach;
	endif;

	if( !empty($term_names) ) :
		$group = implode(', ', $term_names);
	else :
		$group = '';
	endif;
	
	if (!empty($bundle->bundle_data)) {
		$products = unserialize($bundle->bundle_data);
		foreach ($products as $product) {
			$product_ids[] = $product['product_id'];
			$product_name = $wpdb->get_var($wpdb->prepare("SELECT post_title FROM wp_posts WHERE ID = %d", $product['product_id']));
			$product_names[] = empty($product_name) ? 'N/A' : $product_name;
		}
	}
	
	if (!empty($bundle->assoc_apps)) {
		$assoc_apps = unserialize($bundle->assoc_apps);
		foreach ($assoc_apps as $assoc_app_id) {
			$assoc_app_ids[] = $assoc_app_id;
			$assoc_app_name = $wpdb->get_var($wpdb->prepare("SELECT post_title FROM wp_posts WHERE ID = %d", $assoc_app_id));
			$assoc_app_names[] = empty($assoc_app_name) ? 'N/A' : $assoc_app_name;
		}
	}
	
	if (!empty($bundle->assoc_adv_features)) {
		$adv_features = unserialize($bundle->assoc_adv_features);
		foreach ($adv_features as $adv_feat_id) {
			$adv_feat_ids[] = $adv_feat_id;
			$adv_feat_name = $wpdb->get_var($wpdb->prepare("SELECT post_title FROM wp_posts WHERE ID = %d", $adv_feat_id));
			$adv_feat_names[] = empty($adv_feat_name) ? 'N/A' : $adv_feat_name;
		}
	}
	
	$bundle_data[] = array(
		'bundle_id' => $bundle->ID,
		'title' => $bundle->post_title,
		'desc' => $bundle->post_content,
		'product_names' => implode(', ', $product_names),
		'product_ids' => implode(', ', $product_ids),
		'group' => $group,
		'appname' => implode(', ', $assoc_app_names),
		'appid' => implode(', ', $assoc_app_ids),
		'advfeatname' => implode(', ', $adv_feat_names),
		'advfeatid' => implode(', ', $adv_feat_ids),
	);
}
//print_r($bundle_data);
outputCSV($bundle_data);
die();

function outputCSV($data) {
	$file =  __DIR__ . "/bundle_report.csv";
	$output = fopen($file, "w");
	fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));
	if ($output) {
		foreach ($data as $row) {
			fputcsv($output, $row); // here you can change delimiter/enclosure
		}
		fclose($output);
	}
	else {
		echo 'file could not be opened for writing';
	}
}


?>