<?php
include('../include/session.php');
date_default_timezone_set('Africa/Gaborone');
$entry_date = date("Y-m-d H:i:s", time()); 

$production_no =$_GET['production_no_text'];
$mix_design_name =$_GET['mix_design_name_text'];
$pallets = $_GET['pallets_text'];
$count =$_GET['counted_text'];
$count_date =$_GET['count_date'];
$final =$_GET['final_group'];

$table_name = $_SESSION['db_prefix']."_store_items";
$item_id_query = "SELECT store_item_id FROM $table_name WHERE item_name = '".$mix_design_name."' ";
$item_id_result = mySQL_query($item_id_query);
$store_item_id = mysql_result($item_id_result, 0, "store_item_id");

$table_name = $_SESSION['db_prefix']."_production_plan";
$update_query = "UPDATE $table_name SET depalleted = '".$final."' WHERE production_no = '".$production_no."' ";
execsql($update_query);

$table_name = $_SESSION['db_prefix']."_production_plan_lines";
$production_query = "INSERT INTO $table_name (production_no, mix_design_name, action, action_date, quantity) VALUES ('".$production_no."', '".$mix_design_name."', 'dep_count', '".$count_date."', '".$count."' ) ";
execsql($production_query);

$table_name = $_SESSION['db_prefix']."_production_plan_lines";
$pallets_query = "INSERT INTO $table_name (production_no, mix_design_name, action, action_date, quantity) VALUES ('".$production_no."', '".$mix_design_name."', 'pallets', '".$count_date."', '".$pallets."' ) ";
execsql($pallets_query);

$table_name = $_SESSION['db_prefix']."_store_items";
$stock_query = "UPDATE $table_name SET quantity = quantity + '".$count."' WHERE item_name = '".$mix_design_name."' ";
execsql($stock_query);

$table_name = $_SESSION['db_prefix']."_store_items";
$qty_query = "SELECT quantity FROM $table_name WHERE item_name = '".$mix_design_name."' ";
$qty_result = mySQL_query($qty_query);
$stock_quantity = mysql_result($qty_result, 0, "quantity");


$table_name2 = $_SESSION['db_prefix']."_item_history";	
				$historyQuery = "INSERT INTO $table_name2 (store_item_id, entry, reference, quantity, total, date, entryDate, userId) VALUES ('".$store_item_id."', '21', '".$production_no."', '".$count."',  '".$stock_quantity."', '".$entry_date."','".$entry_date."','".$_SESSION['username']."')";
				$historyResult = mySQL_query($historyQuery);



$table_name = $_SESSION['db_prefix']."_mix_design";
		$packaging_query = "SELECT packaging_design FROM $table_name WHERE mix_design = '".$mix_design_name."' ";
		$packaging_result = mySQL_query($packaging_query);
		$packaging = mysql_result($packaging_result, 0, "packaging_design");
		
	   	$table_name = $_SESSION['db_prefix']."_packaging_design_lines";
		$components_query = "SELECT store_item_id, quantity FROM $table_name WHERE packaging_design = '".$packaging."'  ";
		
		
						
			$components_result = mySQL_query($components_query);
			$components_num = mysql_num_rows($components_result);
			$j=0;
			while ($j<$components_num) {
			  	$store_item_id = mysql_result($components_result, $j, "store_item_id");
			  	$pack_quantity = mysql_result($components_result, $j, "quantity"); 
				//echo '<br>*****'.$store_item_id;
				if ($store_item_id!='10001') {
				
			  	$used_quantity = $pack_quantity * $pallets;
				
			    $table_name = $_SESSION['db_prefix']."_store_items";
				$stock_query = "UPDATE $table_name SET quantity = quantity-'".$used_quantity."' WHERE store_item_id = '".$store_item_id."' ";
				execsql($stock_query); 
				
				$table_name = $_SESSION['db_prefix']."_store_items";
				$stock_query = "SELECT quantity FROM $table_name WHERE store_item_id = '".$store_item_id."' ";
				$stock_result = mySQL_query($stock_query);
				$stock_quantity = mysql_result($stock_result, 0, "quantity");
				
				$table_name2 = $_SESSION['db_prefix']."_item_history";	
				$historyQuery = "INSERT INTO $table_name2 (store_item_id, entry, reference, quantity, total, date, entryDate, userId) VALUES ('".$store_item_id."', '20', '".$production_no."', '".$used_quantity."',  '".$stock_quantity."', '".$entry_date."','".$entry_date."','".$_SESSION['username']."')";
				$historyResult = mySQL_query($historyQuery);
				
				}
				
				$j++;
			}
			

if ($final=='yes') {
	
$table_name = $_SESSION['db_prefix']."_production_plan_lines";
$produced_query = "SELECT SUM(quantity) AS produced FROM $table_name WHERE production_no = '".$production_no."' AND action = 'dep_count' ";
$produced_result = mySQL_query($produced_query);
$produced = mysql_result($produced_result, 0, "produced");	

$table_name = $_SESSION['db_prefix']."_production_plan_lines";
$skips_query = "SELECT SUM(quantity) AS skips FROM $table_name WHERE production_no = '".$production_no."' AND action = 'skip_count' ";
$skips_result = mySQL_query($skips_query);
$skips = mysql_result($skips_result, 0, "skips");	

$table_name = $_SESSION['db_prefix']."_mix_design_lines";
$line_query = "SELECT store_item_id, quantity FROM $table_name WHERE mix_design = '".$mix_design_name."' ";
$line_result = mySQL_query($line_query);
$line_num = mysql_num_rows($line_result);
$skip_cost = ''; 
$k=0;
while ($k<$line_num) {
$store_item_id = mysql_result($line_result, $k, "store_item_id");
	  	$table_name = $_SESSION['db_prefix']."_store_items";
		$name_query = "SELECT item_name, unit, price FROM $table_name WHERE store_item_id = '".$store_item_id."'  ";
		$name_result = mySQL_query($name_query);
		$item_name = mysql_result($name_result, 0, "item_name");
		$unit = mysql_result($name_result, 0, "unit");
		$price = mysql_result($name_result, 0, "price");
$quantity = mysql_result($line_result, $k, "quantity");
$cost = $quantity * $price;
$skip_cost = $skip_cost + $cost;
$k++;
}
		
$table_name = $_SESSION['db_prefix']."_mix_design";
$est_qty_query = "SELECT est_qty FROM $table_name WHERE mix_design = '".$mix_design_name."' ";
$est_qty_result = mySQL_query($est_qty_query);
$est_qty = mysql_result($est_qty_result, 0, "est_qty");
$est_cost = $skip_cost / $est_qty;

$production_cost = $skip_cost * $skips;  
$tile_cost = $production_cost / $produced;
		
$production_margin = (($tile_cost/$est_cost)*100)-100;
//echo '<br>*****'.$production_margin;
include('../include/prod_margin_alert.php');



}

$table_name = $_SESSION['db_prefix']."_tracking_production";
$trackingQuery = "INSERT INTO $table_name (entry_date, user_name, action, details_1, details_2, details_3, details_4) VALUES ('".$entry_date."' , '".$_SESSION['username']."', '63', '".$count_date."' ,'".$production_no."','".$pallets."','".$count."' )";
$trackingResult = mySQL_query($trackingQuery);	

$table_name = $_SESSION['db_prefix']."_tracking_store";
$trackingQuery = "INSERT INTO $table_name (entry_date, user_name, action, details_1, details_2, details_3, details_4) VALUES ('".$entry_date."' , '".$_SESSION['username']."', '12', '".$count_date."' ,'".$production_no."','".$pallets."','".$count."' )";
$trackingResult = mySQL_query($trackingQuery);	

?>

<script language="javascript" type="text/javascript">
         window.location="production_process.php";
</script>

