<style>
.highlight {
	background-color:powderblue;	
}
.warning {
	background-color:white;	
	color:red;
}
.total {
	background-color:gold;	
}
.grandTotal {
	background-color:yellowgreen;	
}
</style>

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
//include wp libriries and set timezone to HK
require_once('wp-load.php'); 
date_default_timezone_set("Asia/Hong_Kong"); 
$_SESSION['grandTotal'] = 0;
/*
function get_paper_cost($key){
	global $wpdb;
	
	$cost = array("base_cost"=>0, "additional_cost_per_sheet"=>0);
	
	$sql_cost = "SELECT * FROM custom_lookup_paper_cost WHERE attribute_name = '".$key."' LIMIT 1 ";
	
	$result = $wpdb->get_results($sql_cost);
	
	foreach($result as $record){
	   $cost['base_cost'] = $record->base_cost;
	   $cost['additional_cost_per_sheet'] = $record->additional_cost_per_sheet;
	}		
	
	return $cost;
	
}

function get_extra_cost($key, $value){
	global $wpdb;
	
	$cost = 0;
	
	$sql_cost = "SELECT * FROM custom_lookup_extra_cost WHERE attribute_name = '".$key."' AND option_value = '".$value."' LIMIT 1 ";
	
	$result = $wpdb->get_results($sql_cost);
	
	foreach($result as $record){
	   $cost = $record->cost;	  
	}		
	
	return $cost;
	
}

*/
function get_order_cost($order_id){
	global $wpdb;
		
	$order = wc_get_order($order_id);

	if($order===false){
		echo "order not found!";
		exit();	
	}

	$order_no = $order->get_order_number();
	
	foreach( $order->get_items() as $item ){
	
		$item_data = $item->get_data();
		$item_meta_data = $item->get_meta_data();
		
		$item_id = $item->get_ID();	
		$item_name = $item->get_name();		
		$qty = $item->get_quantity();
		
		if(stripos($item_name, "Fotomax")!==false){
			
			$base_spread = 0;		
			$base_cost = 0;		
			$additional_cost_per_sheet = 0;	
			$box = "";
			$box_cost = 0;
			$logo_cost = 0;		
			$logo = "";
			$sql_spreads = "SELECT * FROM wp_woocommerce_order_itemmeta WHERE order_item_id = '".$item_id."' AND meta_key LIKE '%size' AND meta_key NOT LIKE '#_%' ESCAPE '#' ";
			
			$result = $wpdb->get_results($sql_spreads);			
			
			foreach($result as $record){
			   $size_key = $record->meta_key;
			   $size = $record->meta_value;
			}
			
			$sql_binding = "SELECT * FROM wp_woocommerce_order_itemmeta WHERE order_item_id = '".$item_id."' AND meta_key = 'fotomaxbinding' ";
			
			$result = $wpdb->get_results($sql_binding);			
			
			foreach($result as $record){
			   $binding_key = $record->meta_key;
			   $binding = $record->meta_value;
			   if($binding=="Magazine"){
					$base_spread = 8;	  
			   } elseif($binding = "Flush Mount") {
					$base_spread = 8;	  
			   }
			}
			
			
			$sql_spreads = "SELECT * FROM wp_woocommerce_order_itemmeta WHERE order_item_id = '".$item_id."' AND meta_key LIKE '%spread%' AND meta_key NOT LIKE '#_%' ESCAPE '#' ";
			
			$result = $wpdb->get_results($sql_spreads);		
			
			foreach($result as $record){
			   $spread_key = $record->meta_key;
			   $total_spread = $record->meta_value;		
				$paper_cost_result = get_paper_cost($spread_key);
				$base_cost = $paper_cost_result['base_cost'];
				$additional_cost_per_sheet = $paper_cost_result['additional_cost_per_sheet']*($total_spread-$base_spread);	
				if($additional_cost_per_sheet<0){
					$additional_cost_per_sheet = 0;
				}
			}		
			
			
			
			$sql_box = "SELECT * FROM wp_woocommerce_order_itemmeta WHERE order_item_id = '".$item_id."' AND meta_key LIKE '%box' AND meta_key NOT LIKE '#_%' ESCAPE '#' ";
			
			$result = $wpdb->get_results($sql_box);		
			
			foreach($result as $record){
			   $box_key = $record->meta_key;
			   $box = $record->meta_value;		
			   if($box=="Yes"){
				   $lookup_key = "box_".$size;
				   $box_material = "Matte Box";
				   $box_cost = get_extra_cost($lookup_key, $box_material);				   
			   }
				
			}				
			
			
			
			

			$sql_logo = "SELECT * FROM wp_woocommerce_order_itemmeta WHERE order_item_id = '".$item_id."' AND meta_key = 'fotomaxletterpress' ";
			
			$result = $wpdb->get_results($sql_logo);		
			
			foreach($result as $record){
			   $logo_key = $record->meta_key;
			   $logo = $record->meta_value;	
			   if($logo=="Yes"){
				   $logo_cost = 3*10;
			   } elseif($logo=="None") {
				   $logo_cost = 10;
			   }
			}			
				
				
			$total_cost = $base_cost + $additional_cost_per_sheet + $box_cost + $logo_cost;
		   	$_SESSION['grandTotal'] += $total_cost*$qty;
	
						
			
		}elseif($item_name!="Canvas") {
		
			//$binding = $item->get_meta("binding");
			
			// calculate paper cost
			$sql_spreads = "SELECT * FROM wp_woocommerce_order_itemmeta WHERE order_item_id = '".$item_id."' AND meta_key LIKE '%spreads%' AND meta_key NOT LIKE '#_%' ESCAPE '#' ";
			
			$result = $wpdb->get_results($sql_spreads);
			
			foreach($result as $record){
			   $spread_key = $record->meta_key;
			   $total_spread = $record->meta_value;
			}
			
			$arr_spread_key = explode("_",$spread_key);
			
			$layout = $arr_spread_key[0];
			
			if($layout=="m"){
				$binding = "Magazine";
				$base_spread = 15;	
			} elseif($layout=="f"){
				$binding = "Flush Mount";
				$base_spread = 10;			
			}
			
			$size = $arr_spread_key[1];
			
			$paper_cost_result = get_paper_cost($spread_key);
			$base_cost = $paper_cost_result['base_cost'];
			$additional_cost_per_sheet = $paper_cost_result['additional_cost_per_sheet']*($total_spread-$base_spread);
			
			// calculate box cost
			$sql_box = "SELECT * FROM wp_woocommerce_order_itemmeta WHERE order_item_id = '".$item_id."' AND meta_key LIKE '%box%' AND meta_key NOT LIKE '#_%' ESCAPE '#' ";
			
			$result = $wpdb->get_results($sql_box);
			
			$has_box = false;
			
			foreach($result as $record){
			   $box_key = $record->meta_key;
			   $box = $record->meta_value;
			   
			   if($record->meta_value!="None" && $record->meta_value!="" ){
				   $has_box = true;
			   }
			   
			}
			
			$box_cost = get_extra_cost($box_key, $box);
			
			// calculate logo cost
			$sql_logo = "SELECT * FROM wp_woocommerce_order_itemmeta WHERE order_item_id = '".$item_id."' AND meta_key LIKE '%logo' AND meta_key NOT LIKE '#_%' ESCAPE '#' ";
			
			$result = $wpdb->get_results($sql_logo);		
			
			$logo_cost = 0;
			
			foreach($result as $record){
			   $logo_key = $record->meta_key;
			   $logo = $record->meta_value;
			   
			   if($record->meta_value=="Yes"){
				  $logo_cost = get_extra_cost($logo_key, $logo); 
			   }		   
			}		
			
		   if($has_box){
			   $logo_cost = $logo_cost * 2;
		   }		
		   
		   $total_cost = $base_cost + $additional_cost_per_sheet + $box_cost + $logo_cost;
		   $_SESSION['grandTotal'] += $total_cost*$qty;
		   
		} else {
			
			$binding = "";	
			$base_spread = "";
			$base_cost = "";
			$total_spread = "";
			$additional_cost_per_sheet = "";
			$box = "";
			$box_cost = "";
			$logo = "";
			$logo_cost = "";			
			
			// calculate logo cost
			//$sql_canvassize = "SELECT * FROM wp_woocommerce_order_itemmeta WHERE order_item_id = '".$item_id."' AND meta_key LIKE '%canvassize' ";
			$sql_canvassize = "SELECT * FROM wp_woocommerce_order_itemmeta WHERE order_item_id = '".$item_id."' AND meta_key LIKE '%size2' ";
			
			$result = $wpdb->get_results($sql_canvassize);		
			
			$total_cost = 0;
			
			foreach($result as $record){
			   $size_key = $record->meta_key;
			   $size = $record->meta_value;
			   
			   $total_cost = get_extra_cost($size_key, $size);  
			}		
			
		   $_SESSION['grandTotal'] += $total_cost*$qty;						
		}
		
		
		echo "<tr>";
			echo "<td>#".$order_no."</td>";
			echo "<td>".$item_id."</td>";
			echo "<td>".$item_name."</td>";
			echo "<td>".$qty."</td>";
			echo "<td>".$size."</td>";
			echo "<td>".$binding."</td>";
			echo "<td>".$base_spread."</td>";			
			echo "<td class='highlight'>".$base_cost."</td>";		
			echo "<td>".$total_spread."</td>";																
			echo "<td class='highlight'>".$additional_cost_per_sheet."</td>";	
			echo "<td>".$box."</td>";	
			echo "<td class='highlight'>".$box_cost."</td>";
			echo "<td>".$logo."</td>";	
			echo "<td class='highlight'>".$logo_cost."</td>";	
			echo "<td class='total'>".$total_cost."</td>";	
			echo "<td class='total'>".$qty*$total_cost."</td>";																					
		echo "<tr>";
		
	}
}

echo "<form action='?' id='' name='' method='post'>";	
	echo "<table style='border-collapse: collapse;' border='1' cellpadding='5' cellspacing='5'>";
		echo "<tr>";
			echo "<td>";
				echo "Order# <input type='text' name='order_number' id='order_number' value='".$_REQUEST['order_number']."'>";
			echo "</td>";	
			echo "<td>";
				echo "<input type='submit' name='submit' id='submit' value='check'>";
			echo "</td>";	
			echo "<td class='warning'>";
				echo "**Only applicable to those orders having new attribute set";
			echo "</td>";						
		echo "</tr>";
	echo "</table>";	
echo "</form>";		
	
echo "<table style='border-collapse: collapse;' border='1' cellpadding='5' cellspacing='5' width='100%'>";
	echo "<tr>";
		echo "<td>Order#</td>";
		echo "<td>Item ID</td>";
		echo "<td>Product</td>";
		echo "<td>Order Qty</td>";
		echo "<td>Size</td>";
		echo "<td>Binding</td>";
		echo "<td>Base Spread</td>";					
		echo "<td>Base Cost</td>";				
		echo "<td>Total Spread</td>";													
		echo "<td>Additional Spread Cost</td>";		
		echo "<td>Box</td>";	
		echo "<td>Box Cost</td>";
		echo "<td>Logo</td>";	
		echo "<td>Logo Cost</td>";	
		echo "<td>Unit Material Cost</td>";																				
		echo "<td>Total Material Cost</td>";																				
	echo "<tr>";
	if($_REQUEST['order_number']!=""){
		get_order_cost($_REQUEST['order_number']);	
	}
	echo "<tr>";
		echo "<td colspan='15' align='right'>Grand Total</td>";																				
		echo "<td class='grandTotal'>".$_SESSION['grandTotal']."</td>";																				
	echo "<tr>";	
echo "</table>";