<!DOCTYPE html>
<html>
<body>

<?php

	$conn = mysql_connect("localhost","root","");
	
	mysql_select_db("chaterhouse1",$conn);

	$sql = "SELECT loc_code,location_name FROM `0_locations`  ";
	$result = mysql_query($sql);
	$j = 0;
	
	$cols[] = 0;
	$cols[] = 75;
	$n = 100;
	 while($loc = mysql_fetch_array($result)){
	 $locations[]=$loc['loc_code'];
	 $locations2[]=$loc['location_name'];
	 
	 $cols[] = $n;
	 $n +=30;
	 $j++;
	 }
	 
	 $cols[] = 720;
	 $cols[] = 800;
	mysql_free_result($result);

//print_r($cols);
////////////////////////////////////
	$sql_main_cat = "SELECT * FROM `0_main_category`";
	
	$result_main_cat = mysql_query($sql_main_cat);

	
	while($row_main_cat = mysql_fetch_array($result_main_cat)){
		
		echo $row_main_cat['main_cat_name'];
		echo '<br>';
		$sql_category = "SELECT * FROM `0_stock_category` WHERE 0_stock_category.main_category_id = ".$row_main_cat['main_category_id']."";
		$result_cat = mysql_query($sql_category);
		while($row_cat = mysql_fetch_array($result_cat)){
			echo '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'.$row_cat['description'];
			echo '<br>';

/////////////////////////////////////////////////	
			$sql1 = "SELECT DISTINCT 0_stock_moves.stock_id,0_stock_master.*, 
			0_stock_master.material_cost + 0_stock_master.labour_cost + 0_stock_master.overhead_cost AS UnitCost,
			(SELECT  0_main_category.main_category_id FROM 0_main_category ,0_stock_category
					WHERE 0_main_category.main_category_id = 0_stock_category.main_category_id 
					AND 0_stock_category.category_id = 0_stock_master.category_id) AS main_cat
			FROM
			0_stock_master
			INNER JOIN 0_stock_moves ON 0_stock_master.stock_id = 0_stock_moves.stock_id 
			WHERE 0_stock_master.stock_id != 'OB' AND 0_stock_master.category_id = ".$row_cat['category_id']."
			GROUP BY 0_stock_master.category_id, 0_stock_moves.stock_id
			limit 10";
			
			//mysql_query($sql1);
			$result1 = mysql_query($sql1);
			$i = 0;
			
			while($row1 = mysql_fetch_array($result1)){
				$tot_qty = 0;
				
				foreach($locations as $loc_code){
					$sql = "SELECT  0_stock_moves.stock_id, loc_code, SUM(qty) as qty_all FROM 0_stock_moves
							WHERE loc_code = '".$loc_code."' and 0_stock_moves.stock_id  = '".$row1['stock_id']."' GROUP BY 0_stock_moves.loc_code";
						$result = mysql_query($sql);
						$num_rows =  mysql_num_rows($result);
						if($num_rows != 0){
							while($row = mysql_fetch_array($result)){
								$item_location_qty[$loc_code] = $row['qty_all'];
								$tot_qty += $row['qty_all'];
							}
						}else{
							$item_location_qty[$loc_code] = 0;
						}
				}
			
				$items[$i]['item'] = $row1['stock_id'];		
				$items[$i]['loc'] = $item_location_qty;
				$items[$i]['unit_cost'] = $row1['UnitCost'];
				$items[$i]['value'] = $row1['UnitCost']*$tot_qty;
				
				if($row1['main_cat'] != $items[$i+1]['main_category']){
					$items[$i]['main_category'] = $row1['main_cat'];
				}
				
				$i++;
			}
//echo '<pre>';
//print_r($items);
//echo '</pre>';

echo '<table border="1">';
	echo '<th>item</th>';

foreach($locations as $location){
	echo '<th>'.$location.'</th>';
}

echo '<th>Unit Cost</th>';
echo '<th>Value</th>';

foreach($items as $car_item){
	
	echo '<tr><td>';
	print_r($car_item['item']);
	echo '</td>';
	
	foreach($car_item['loc'] as $loc_stock){
			print_r('<PRE>');
			echo '<td>';
			print_r($loc_stock);
			echo '</td>';
			print_r('</PRE>');
	
	}
	
	echo '<td>';
	print_r($car_item['unit_cost']);
	echo '</td>';
	echo '<td>';
	print_r($car_item['value']);
	echo '</td>';
			
	
	
	echo '</td></tr>';
}
echo '</table>';

/////////////////////////////////////////////	
	
		}
		
	}
//////////////////////////////////////////////////

?>
</body>
</html>