<?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'];
$packing_date = $_GET['packing_date_day'];
$batch_code = $_GET['batch_code_string'];
$batch_qty = $_GET['batch_text'];
$damaged_no = $_GET['damaged_select'];
$packed_no = $_GET['unit_packed_no_text'];
$unit_no = $_GET['unit_no_text'];
$packing_design_no = $_GET['pack_design_select'];
$packing_cost = 0;


//$table_name = $_SESSION['db_prefix']."_presets";
//$margin_query = "SELECT preset_value FROM $table_name WHERE preset_name = 'packing_margin'  ";
//$margin_result = $con->query($margin_query);
//$row = mysqli_fetch_assoc($margin_result);
//$packing_margin = $row['preset_value'];

$table_name = $_SESSION['db_prefix']."_product_store_lines";
$pack_no_query = "SELECT packing_no FROM $table_name WHERE packing_no > 0  ORDER BY product_store_line_id DESC LIMIT 1 ";
$pack_no_result = $con->query($pack_no_query);
$row = mysqli_fetch_assoc($pack_no_result);
$pack_no = $row['packing_no'];
	
$packing_no = $pack_no + 1;


$table_name = $_SESSION['db_prefix']."_production";
$product_query = "SELECT product_item_id FROM $table_name WHERE production_no = '".$production_no."' ";
$product_result = $con->query($product_query);
$row = mysqli_fetch_assoc($product_result);
$product_item_id = $row['product_item_id'];

$table_name = $_SESSION['db_prefix']."_product_store";
$store_product_query = "SELECT product_item_id FROM $table_name WHERE packing_design_no = '".$packing_design_no."' ";
$store_product_result = $con->query($store_product_query);
$row = mysqli_fetch_assoc($store_product_result);
$new_stock_product_item_id = $row['product_item_id'];


$table_name = $_SESSION['db_prefix']."_packaging_design";
$pack_design_qty_query = "SELECT est_qty, pack_qty FROM $table_name WHERE  design_no = '".$packing_design_no."' ";
$pack_design_qty_result = $con->query($pack_design_qty_query);
$row = mysqli_fetch_assoc($pack_design_qty_result);
$pack_design_qty = $row['est_qty'];
$pack_qty = $row['pack_qty'];



$table_name = $_SESSION['db_prefix']."_packaging_design_lines";
$components_query = "SELECT store_item_id, quantity, used_for FROM $table_name WHERE design_no = '".$packing_design_no."'  ";
$components_result = $con->query($components_query);
$components_num = mysqli_num_rows($components_result);

$packing_cost = 0;
$stock_alert = 'no';
//$packing_alert = 'no';
$alert_array = array();
$alert2_array = array();
$stock_alert_text = '';
//$packing_alert_text = '';
while($row = $components_result->fetch_assoc()) {
		$store_item_id = $row["store_item_id"];
		$used_quantity = $row["quantity"];
		$used_for = $row["used_for"];
				
		if ($used_for == 'block') {
			   $used_quantity = $used_quantity * $packed_no;
		}
		
		if ($used_for == 'pallet') {
			   $used_quantity = $used_quantity * $batch_qty;
		}
							  
		$table_name = $_SESSION['db_prefix']."_store_items";
		$stock_query = "UPDATE $table_name SET quantity = quantity-'".$used_quantity."' WHERE store_item_id = '".$store_item_id."' ";
		$stock_result = $con->query($stock_query);
				
		$table_name = $_SESSION['db_prefix']."_store_items";
		$stock_query = "SELECT item_name, quantity, minimumStock, price FROM $table_name WHERE store_item_id = '".$store_item_id."' ";
		$stock_result = $con->query($stock_query);
		$row = mysqli_fetch_assoc($stock_result);
		$stock_quantity = $row['quantity'];
		$item_name = $row['item_name'];
		$item_price = $row['price'];
		$min_stock = $row['minimumStock'];
		
		if ($min_stock > $stock_quantity) {
				$stock_alert = 'yes';
				$alert_array[] = $store_item_id;
				$alert2_array[] = $stock_quantity;
	
				$stock_alert_text = $stock_alert_text.'The stock quantity for <b>'.$item_name.'</b> is below the minimum stock of <b>'.$min_stock.'</b>.<br>The present stock quantity is now <b>'.$stock_quantity.'</b>.<br><br>';
		}
		
		$table_name = $_SESSION['db_prefix']."_production_packaging_lines";
		$mix_lines_query = "INSERT INTO $table_name (production_no, packing_no, store_item_id, item_name, item_quantity, item_price, used_for ) VALUES ('".$production_no."', '".$packing_no."', '".$store_item_id."', '".$item_name."', '".$used_quantity."', '".$item_price."', '".$used_for."'  ) ";
		$mix_lines_result = $con->query($mix_lines_query);

		
		$line_cost = $used_quantity * $item_price;
		$packing_cost = $packing_cost + $line_cost;
		
								
		$table_name2 = $_SESSION['db_prefix']."_item_history";	
		$history_query = "INSERT INTO $table_name2 (store_item_id, entry, reference, reference_2, quantity, total, date, entryDate, userId) VALUES ('".$store_item_id."', '23', '".$production_no."', '".$packing_no."', '-".$used_quantity."',  '".$stock_quantity."', '".$packing_date."','".$entry_date."','".$_SESSION['username']."')";
		$history_result = $con->query($history_query);
	}
	

//production product

	$table_name = $_SESSION['db_prefix']."_production";
	$check_query = "SELECT  production_id FROM $table_name WHERE production_no = '".$production_no."' AND packed_no = '0' ";
	$check_result = $con->query($check_query);
	$check_num = mysqli_num_rows($check_result);
	
	if ($check_num==1) {
		
	//$packing_cost = ($packing_cost / $pack_design_qty) * $packed_no;
		
	$table_name = $_SESSION['db_prefix']."_production";
	$production_query = "UPDATE $table_name SET packing_no = '".$packing_no."', packing_date = '".$packing_date."', packed_batches = '".$batch_qty."', packed_no = '".$packed_no."', packing_cost = '".$packing_cost."', damaged_no = '".$damaged_no."', packing_design_no = '".$packing_design_no."'  WHERE production_no = '".$production_no."' ";
	$production_result = $con->query($production_query);

	}
	else {
		
	//$packing_cost = ($packing_cost / $pack_design_qty) * $packed_no;
		
	$table_name = $_SESSION['db_prefix']."_production";
	$production_query = "SELECT batch_no, production_date, product_item_id, mix_design_no, skips_no, skip_products_no, production_cost, batch_code, produced_no, recycled_no FROM $table_name WHERE production_no = '".$production_no."' ";
	$production_result = $con->query($production_query);
	$row = mysqli_fetch_assoc($production_result);
	$batch_no = $row['batch_no'];
	$production_date = $row['production_date'];
	$product_item_id = $row['product_item_id'];
	$mix_design_no = $row['mix_design_no'];
	$skips_no = $row['skips_no'];
	$skip_products_no = $row['skip_products_no'];
	$production_cost = $row['production_cost'];
	$batch_code = $row['batch_code'];
	$produced_no = $row['produced_no'];
	$recycled_no = $row['recycled_no'];
	
	$table_name = $_SESSION['db_prefix']."_production";
	$added_production_query = "INSERT INTO $table_name (production_no, batch_no, production_date, product_item_id, mix_design_no, skips_no, skip_products_no, production_cost, batch_code, produced_no, recycled_no, packing_no, packing_date, packed_batches, packed_no, packing_cost, damaged_no, packing_design_no ) VALUES ('".$production_no."', '".$batch_no."', '".$production_date."', '".$product_item_id."', '".$mix_design_no."', '".$skips_no."', '".$skip_products_no."', '".$production_cost."', '".$batch_code."', '".$produced_no."', '".$recycled_no."', '".$packing_no."', '".$packing_date."', '".$batch_qty."', '".$packed_no."', '".$packing_cost."', '".$damaged_no."', '".$packing_design_no."') ";
	$added_production_result = $con->query($added_production_query);
	}
	
	$table_name = $_SESSION['db_prefix']."_packaging_design";
	$design_cost_query = "SELECT est_qty, packaging_design, product_item_id FROM $table_name WHERE design_no = '".$packing_design_no."' ";
	$design_cost_result = $con->query($design_cost_query);
	$row = mysqli_fetch_assoc($design_cost_result);
	$packing_est_qty = $row['est_qty'];
	$packaging_design = $row['packaging_design'];
	$packed_product_item_id = $row['product_item_id'];
	
	$unit_est_cost = $packing_cost / $packing_est_qty;
	$unit_packing_cost = $packing_cost / $packed_no;
		
	//$margin = 100 - (($unit_est_cost / $unit_packing_cost) *100);
//	
//	if ($margin > $packing_margin) {
//		  $packing_alert_text = 'Packing margin of '.$packing_margin.'% exceded on packing no '.$packing_no.' for <b>'.$packaging_design.'</b> on <b>'.date("l, d-m-Y", strtotime($packing_date)).'</b> by <b>'.number_format($margin, 4, '.', ',').'%</b>.' ;	
//		  $packing_alert = 'yes';
//	}	
	
	
	$table_name = $_SESSION['db_prefix']."_product_store";
	$item_stock_query = "SELECT quantity FROM $table_name WHERE product_item_id = '".$product_item_id."'  ";
	$item_stock_result = $con->query($item_stock_query);
	$row = mysqli_fetch_assoc($item_stock_result);
	$stock_quantity = $row['quantity'];
	
	$new_stock_quantity = $stock_quantity - ($packed_no*$pack_qty) - $damaged_no;
	
	$table_name = $_SESSION['db_prefix']."_product_store";
	$stock_update_query = "UPDATE $table_name SET quantity = '".$new_stock_quantity."' WHERE product_item_id = '".$product_item_id."' ";
	$stock_update_result = $con->query($stock_update_query);
	
		
	$quantity_out = ($packed_no*$pack_qty) + $damaged_no;
	$table_name = $_SESSION['db_prefix']."_product_store_lines";
	$update_query = "UPDATE $table_name SET quantity_out = quantity_out+'".$quantity_out."', out_date = '".$packing_date."', action = 'Packed' WHERE production_no = '".$production_no."' AND batch_code = '".$batch_code."' AND packing_date IS NULL ";
	$update_result = $con->query($update_query);
	
	$table_name = $_SESSION['db_prefix']."_product_store_lines";
	$stock_quantity_query = "SELECT SUM(quantity_in-quantity_out) AS stock_quantity FROM $table_name WHERE product_item_id = '".$product_item_id."'  ";
	$stock_quantity_result = $con->query($stock_quantity_query);
	$row = mysqli_fetch_assoc($stock_quantity_result);
	$total_stock_quantity = $row['stock_quantity'];
	
	$table_name2 = $_SESSION['db_prefix']."_product_history";	
	$history_query = "INSERT INTO $table_name2 (product_item_id, entry, reference, quantity, total, date, entryDate, userId) VALUES ('".$product_item_id."', '24', '".$production_no."', '-".$quantity_out."',  '".$total_stock_quantity."', '".$packing_date."','".$entry_date."','".$_SESSION['username']."')";
	$history_result = $con->query($history_query);
	
	$table_name = $_SESSION['db_prefix']."_tracking_production";
	$tracking_query = "INSERT INTO $table_name (entry_date, user_name, action, details_1, details_2, details_3, details_4) VALUES ('".$entry_date."' , '".$_SESSION['username']."', '62', '".$production_no."' ,'".$packing_date."','".$new_stock_product_item_id."','".$packed_no."' )";
	$tracking_result = $con->query($tracking_query);
	
	$table_name = $_SESSION['db_prefix']."_tracking_store";
	$tracking_query = "INSERT INTO $table_name (entry_date, user_name, action, details_1, details_2, details_3, details_4) VALUES ('".$entry_date."' , '".$_SESSION['username']."', '37', '".$production_no."' ,'".$packing_no."','".$new_stock_product_item_id."','".$packed_no."' )";
	$tracking_result = $con->query($tracking_query);
	
	

// packed product
        
	$table_name = $_SESSION['db_prefix']."_product_store_lines";
	$product_stock_query = "INSERT INTO $table_name (product_item_id, production_no, packing_no, packing_date, batch_code, quantity_in ) VALUES ('".$new_stock_product_item_id."', '".$production_no."', '".$packing_no."', '".$packing_date."', '".$batch_code."', '".$packed_no."'  ) ";
	$product_stock_result = $con->query($product_stock_query);	
	
	$table_name = $_SESSION['db_prefix']."_product_store";
	$item_stock_query = "SELECT quantity FROM $table_name WHERE product_item_id = '".$new_stock_product_item_id."'  ";
	$item_stock_result = $con->query($item_stock_query);
	$row = mysqli_fetch_assoc($item_stock_result);
	$stock_quantity = $row['quantity'];
	
	$new_stock_quantity = $stock_quantity + $packed_no;
	
	$table_name2 = $_SESSION['db_prefix']."_product_history";	
	$history_query = "INSERT INTO $table_name2 (product_item_id, entry, reference, quantity, total, date, entryDate, userId) VALUES ('".$new_stock_product_item_id."', '24', '".$production_no."', '".$packed_no."',  '".$new_stock_quantity."', '".$packing_date."','".$entry_date."','".$_SESSION['username']."')";
	$history_result = $con->query($history_query);
	
	$table_name = $_SESSION['db_prefix']."_product_store";
	$stock_update_query = "UPDATE $table_name SET quantity = '".$new_stock_quantity."' WHERE product_item_id = '".$new_stock_product_item_id."' ";
	$stock_update_result = $con->query($stock_update_query);
	
	
	$table_name2 = $_SESSION['db_prefix']."_product_store";      
   	$recycle_query = "UPDATE $table_name2 SET recycle_qty = recycle_qty + '".$damaged_no."' WHERE product_item_id = '".$product_item_id."'";
   	$recycle_result = $con->query($recycle_query);
   
   $table_name = $_SESSION['db_prefix']."_product_store";
   $recycle_stock_query = "SELECT recycle_qty FROM $table_name WHERE product_item_id = '".$product_item_id."'";
   $recycle_stock_result = $con->query($recycle_stock_query);
   $row = mysqli_fetch_assoc($recycle_stock_result);
   $recycle_stock = $row['recycle_qty'];
   
   $table_name2 = $_SESSION['db_prefix']."_recycle_history";	
   $recycle_history_query = "INSERT INTO $table_name2 (product_item_id, batch_code, base_item_id, entry, recycle_no, recycle_stock, date, recycle_reason, recycle_notes, entryDate, userId) VALUES ('".$product_item_id."', '".$batch_code."', '".$product_item_id."', '12', '".$damaged_no."', '".$recycle_stock."', '".$packing_date."',  'Packing', 'packing no: ".$packing_no."', '".$entry_date."','".$_SESSION['username']."')";
   $recycle_history_result = $con->query($recycle_history_query);
   
   //if ($packing_alert=='yes') {
//		include('../include/packing_margin_alert.php');
//}
	
	if ($stock_alert=='yes') {
		include('../include/min_direct_materials_alert.php');
}
		
	if (isset($_GET['save_close_button']) )
	   { ?>
       <script>
	 		 window.location="../production/packing_slip.php?packing_no=<?php echo $packing_no?>";
		</script>
        <?php		 
	   }
?>



