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

$packing_no = $_GET['packing_no'];
$packing_design_no = $_GET['pack_design_select'];
//$product_product_item_id = $_GET['product_item_id'];
$product_product_item_id = $packing_design_no;

$packing_date = $_GET['packing_date_edit'];
$batch_code = $_GET['batch_code_string'];
$damaged_no = $_GET['damaged_select'];
$packed_no = $_GET['packed_no_text'];
$packing_cost = 0;

$table_name = $_SESSION['db_prefix']."_packaging_design";
$cost_query = "SELECT design_cost FROM $table_name WHERE design_no = '".$packing_design_no."' ";
$cost_result = $con->query($cost_query);
$row = mysqli_fetch_assoc($cost_result);
$design_cost = $row['design_cost'];

$packing_cost = $packed_no * $design_cost;


$table_name = $_SESSION['db_prefix']."_production";
$saved_packing_query = "SELECT  packed_no, damaged_no, packing_design_no, packing_date  FROM $table_name WHERE packing_no = '".$packing_no."' ";
$saved_packing_result = $con->query($saved_packing_query);
$row = mysqli_fetch_assoc($saved_packing_result);
$saved_damaged_no = $row['damaged_no'];
$saved_packed_no = $row['packed_no'];
$saved_packing_design_no = $row['packing_design_no'];
$saved_packing_date = $row['packing_date'];


$table_name = $_SESSION['db_prefix']."_production";
$update_query = "UPDATE $table_name SET packed_no = '".$packed_no."', damaged_no = '".$damaged_no."', packing_cost = '".$packing_cost."', packing_date = '".$packing_date."',  packing_edit_date = '".$entry_date."' WHERE packing_no = '".$packing_no."' ";
$update_result = $con->query($update_query);

if ($packed_no!=$saved_packed_no)  {
	
	$table_name = $_SESSION['db_prefix']."_product_store";
	$qty_query = "SELECT quantity FROM $table_name WHERE product_item_id = '".$product_product_item_id."'   ";
	$qty_result = $con->query($qty_query);
	$row = mysqli_fetch_assoc($qty_result);
	$stock_quantity = $row['quantity'];
	
	$packed_difference = $packed_no - $saved_packed_no;
	
	$table_name = $_SESSION['db_prefix']."_product_store";
	$item_stock_query = "SELECT quantity FROM $table_name WHERE product_item_id = '".$product_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_difference;
	
	$table_name = $_SESSION['db_prefix']."_product_store";
	$stock_update_query = "UPDATE $table_name SET quantity = '".$new_stock_quantity."' WHERE product_item_id = '".$product_product_item_id."' ";
	$stock_update_result = $con->query($stock_update_query);
	
	$table_name = $_SESSION['db_prefix']."_product_store_lines";
	$stock_update2_query = "UPDATE $table_name SET quantity_in = '".$packed_no."' WHERE packing_no = '".$packing_no."' ";
	$stock_update2_result = $con->query($stock_update2_query);
		
	$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_product_item_id."', '26', '".$packing_no."', '".$packed_difference."',  '".$new_stock_quantity."', '".$packing_date."','".$entry_date."','".$_SESSION['username']."')";
	$history_result = $con->query($history_query);
}


if ($packed_no!=$saved_packed_no) {
	 $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']."', '72', '".$packing_no."' ,'".$saved_packed_no."','".$packed_no."','".$damaged_no."' )";
	 $tracking_result = $con->query($tracking_query);
	 	 
	 $packing_cost = 0;
	 $item_production_cost = 0;
	 	 
	 $table_name = $_SESSION['db_prefix']."_production_packaging_lines";
	 $used_query = "SELECT store_item_id, item_quantity, item_price FROM $table_name WHERE packing_no = '".$packing_no."' ";
	 $used_result = $con->query($used_query);
	 while($row = $used_result->fetch_assoc()) {
		 
	 $store_item_id = $row["store_item_id"];
	 $item_quantity = $row["item_quantity"];
	 $item_price = $row["item_price"];
	 $used_quantity = $saved_packed_no * $item_quantity;
	 $new_used_quantity = $packed_no * $item_quantity;
	 
	 $stock_adjustment = $used_quantity - $new_used_quantity;
	 	 
	 $table_name = $_SESSION['db_prefix']."_store_items";
	 $stock_update_query = "UPDATE $table_name SET quantity = quantity + '".$stock_adjustment."' WHERE store_item_id = '".$store_item_id."'  ";
	 $stock_update_result = $con->query($stock_update_query);
	 
	 $table_name = $_SESSION['db_prefix']."_store_items";
	 $stock_qty_query = "SELECT quantity FROM $table_name WHERE store_item_id = '".$store_item_id."'  ";
	 $stock_qty_result = $con->query($stock_qty_query);
	 $stock_qty_num = mysqli_num_rows($stock_qty_result);
	 $row = mysqli_fetch_assoc($stock_qty_result);
	 $stock_quantity = $row['quantity'];
	 
	 
	 $item_production_cost = $new_used_quantity * $item_price;
	 
	 $packing_cost = $packing_cost + $item_production_cost;
	 
	 $table_name2 = $_SESSION['db_prefix']."_item_history";	
     $history_query = "INSERT INTO $table_name2 (store_item_id, entry, reference, quantity, total, date, entryDate, userId) VALUES ('".$store_item_id."', '27', '".$packing_no."', '".$stock_adjustment."',  '".$stock_quantity."', '".$entry_date."','".$entry_date."','".$_SESSION['username']."')";
	 $history_result = $con->query($history_query);	 
	 
	 }
 
	 $table_name = $_SESSION['db_prefix']."_production";
	 $cost_update_query = "UPDATE $table_name SET packing_cost = '".$packing_cost."' WHERE packing_no = '".$packing_no."' ";
	 $cost_update_result = $con->query($cost_update_query);
}

if  ($packing_design_no!=$saved_packing_design_no) {
	 $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']."', '75', '".$packing_no."' ,'".$packing_design_no."','".$saved_packing_design_no."','' )";
	 $tracking_result = $con->query($tracking_query);
	 
	 $packing_cost = 0;
	 $item_production_cost = 0;
	 	 
	 //$table_name = $_SESSION['db_prefix']."_production_packaging_lines";
//	 $used_query = "SELECT store_item_id, item_quantity, item_price FROM $table_name WHERE packing_no = '".$packing_no."' ";
//	 $used_result = $con->query($used_query);
//	 while($row = $used_result->fetch_assoc()) {
//	 $store_item_id = $row["store_item_id"];
	 
		 
	 $table_name = $_SESSION['db_prefix']."_packaging_design_lines";
	 $saved_item_query = "SELECT store_item_id, quantity FROM $table_name WHERE design_no = '".$saved_packing_design_no."' ";
	 $saved_item_result = $con->query($saved_item_query);
	 while($row = $saved_item_result->fetch_assoc()) {
	 $saved_store_item_id = $row["store_item_id"];
	 $saved_quantity = $row["quantity"];
	 
	 $saved_total_quantity = $saved_quantity * $packed_no; 
	 
	 $table_name = $_SESSION['db_prefix']."_store_items";
	 $stock_update_query = "UPDATE $table_name SET quantity = quantity + '".$saved_total_quantity."' WHERE store_item_id = '".$saved_store_item_id."'  ";
	 $stock_update_result = $con->query($stock_update_query);
	 
	 $table_name = $_SESSION['db_prefix']."_store_items";
	 $stock_qty_query = "SELECT quantity FROM $table_name WHERE store_item_id = '".$saved_store_item_id."'  ";
	 $stock_qty_result = $con->query($stock_qty_query);
	 $stock_qty_num = mysqli_num_rows($stock_qty_result);
	 $row = mysqli_fetch_assoc($stock_qty_result);
	 $stock_quantity = $row['quantity']; 
	 
	 $table_name2 = $_SESSION['db_prefix']."_item_history";	
     $history_query = "INSERT INTO $table_name2 (store_item_id, entry, reference, quantity, total, date, entryDate, userId) VALUES ('".$saved_store_item_id."', '28', '".$packing_no."', '".$saved_total_quantity."',  '".$stock_quantity."', '".$entry_date."','".$entry_date."','".$_SESSION['username']."')";
	 $history_result = $con->query($history_query);
	 }
	 
	 $table_name = $_SESSION['db_prefix']."_packaging_design_lines";
	 $new_item_query = "SELECT store_item_id, quantity FROM $table_name WHERE design_no = '".$packing_design_no."' ";
	 $new_item_result = $con->query($new_item_query);
	 $new_item_num = mysqli_num_rows($new_item_result);
	 
	 
	 $table_name = $_SESSION['db_prefix']."_production_packaging_lines";
	 $delete_query = "DELETE FROM $table_name WHERE  packing_no = '".$packing_no."' "; 
	 $delete_result = $con->query($delete_query);
	 
	 while($row = $new_item_result->fetch_assoc()) {
	 $new_store_item_id = $row["store_item_id"];
	 $new_quantity = $row["quantity"];
	 	 	 
	 $new_total_quantity = $new_quantity * $packed_no;
	 	 
	 $table_name = $_SESSION['db_prefix']."_store_items";
	 $stock_update_query = "UPDATE $table_name SET quantity = quantity - '".$new_total_quantity."' WHERE store_item_id = '".$new_store_item_id."'  ";
	 $stock_update_result = $con->query($stock_update_query);
	 
	 $table_name = $_SESSION['db_prefix']."_store_items";
	 $stock_qty_query = "SELECT quantity, price, item_name FROM $table_name WHERE store_item_id = '".$new_store_item_id."'  ";
	 $stock_qty_result = $con->query($stock_qty_query);
	 $stock_qty_num = mysqli_num_rows($stock_qty_result);
	 $row = mysqli_fetch_assoc($stock_qty_result);
	 $stock_quantity = $row['quantity'];
	 $item_price = $row['price'];
	 $item_name = $row['item_name'];
	 
	 
	 
	 $table_name2 = $_SESSION['db_prefix']."_item_history";	
     $history_query = "INSERT INTO $table_name2 (store_item_id, entry, reference, quantity, total, date, entryDate, userId) VALUES ('".$new_store_item_id."', '28', '".$packing_no."', '-".$new_total_quantity."',  '".$stock_quantity."', '".$entry_date."','".$entry_date."','".$_SESSION['username']."')";
	 $history_result = $con->query($history_query); 
	 
	 $table_name = $_SESSION['db_prefix']."_production";
	 $packing_update_query = "UPDATE $table_name SET packing_design_no = '".$packing_design_no."' WHERE packing_no = '".$packing_no."' ";
	 $packing_update_result = $con->query($packing_update_query);
	 
	 	 
	 $table_name = $_SESSION['db_prefix']."_production_packaging_lines";
	 $packing_update_query = "INSERT INTO $table_name (packing_no, store_item_id, item_name, item_quantity, item_price  ) VALUES ('".$packing_no."', '".$new_store_item_id."', '".$item_name."', '".$new_quantity."', '".$item_price."' ) ";
	 
	 $packing_update_result = $con->query($packing_update_query);
	 
	 $item_production_cost = $new_total_quantity * $item_price;
	 $packing_cost = $packing_cost + $item_production_cost;
	 
	}
	

	 $table_name = $_SESSION['db_prefix']."_production";
	 $cost_update_query = "UPDATE $table_name SET packing_cost = '".$packing_cost."' WHERE packing_no = '".$packing_no."' ";
	 $cost_update_result = $con->query($cost_update_query);
}



if ($damaged_no!=$saved_damaged_no) {
	 $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']."', '74', '".$packing_no."' ,'".$saved_damaged_no."','".$damaged_no."','".$packed_no."' )";
	 $tracking_result = $con->query($tracking_query);

}

if  ($packing_date!=$saved_packing_date) {
	 $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']."', '76', '".$packing_no."' ,'".$packing_date."','".$saved_packing_date."','' )";
	 $tracking_result = $con->query($tracking_query);
}

	
	if (isset($_GET['save_close_button']) )
	   { ?>
       <script>
	 		window.location="../production/packing_slip.php?packing_no=<?php echo $packing_no ?>";
		</script>
        <?php		 
	   }
?>