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

$production_date = $_GET['production_date_day'];
$production_no = $_GET['production_no'];
$mix_design_no = $_GET['mix_design_no'];
$batch_code = $_GET['batch_code_string'];
$batch_no = $_GET['batch_no_text'];
$skips_no = $_GET['no_skips_text'];
$est_qty = $_GET['est_qty_text'];
$produced = $_GET['produced_text'];
$product_item_id = $_GET['product_item_id'];
$production_cost = 0;
$skip_products_no = $skips_no * $est_qty;
$production_type = 'salt_block';

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

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

$stock_alert = 'no';
$production_alert = 'no';
$alert_array = array();
$alert2_array = array();
$stock_alert_text = '';
$production_alert_text = '';
while($row = $components_result->fetch_assoc()) {
		$store_item_id = $row["store_item_id"];
		$quantity = $row["quantity"];
		$used_quantity = $quantity * $skips_no;
							  
		$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_mix_lines";
		$mix_lines_query = "INSERT INTO $table_name (production_no, store_item_id, item_name, item_quantity, item_price ) VALUES ('".$production_no."', '".$store_item_id."', '".$item_name."', '".$quantity."', '".$item_price."'  ) ";
		$mix_lines_result = $con->query($mix_lines_query);

		$line_cost = $used_quantity * $item_price;
		$production_cost = $production_cost + $line_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."', '20', '".$production_no."', '-".$used_quantity."',  '".$stock_quantity."', '".$production_date."','".$entry_date."','".$_SESSION['username']."')";
		$history_result = $con->query($history_query);
	}
	
	$table_name = $_SESSION['db_prefix']."_production";
	$production_query = "INSERT INTO $table_name (production_no, production_type, production_run,  batch_no, production_date, product_item_id, mix_design_no, skips_no, skip_products_no, production_cost, batch_code, produced_no  ) VALUES ('".$production_no."', '".$production_type."', 'yes', '".$batch_no."', '".$production_date."', '".$product_item_id."', '".$mix_design_no."', '".$skips_no."', '".$skip_products_no."', '".$production_cost."', '".$batch_code."', '".$produced."') ";
	$production_result = $con->query($production_query);
	
	$table_name = $_SESSION['db_prefix']."_mix_design";
	$design_cost_query = "SELECT design_cost, est_qty, mix_design FROM $table_name WHERE design_no = '".$mix_design_no."' ";
	$design_cost_result = $con->query($design_cost_query);
	$row = mysqli_fetch_assoc($design_cost_result);
	$mix_design_cost = $row['design_cost'];
	$mix_est_qty = $row['est_qty'];
	$mix_design = $row['mix_design'];
	
	$unit_est_cost = $mix_design_cost / $mix_est_qty;
	$unit_prod_cost = $production_cost / $produced;
		
	$margin = 100 - (($unit_est_cost / $unit_prod_cost) *100);
	
	if ($margin > $production_margin) {
		  $production_alert_text = 'Production margin of '.$production_margin.'% exceded on production no '.$production_no.' for <b>'.$mix_design.'</b> on <b>'.date("l, d-m-Y", strtotime($production_date)).'</b> by <b>'.number_format($margin, 4, '.', ',').'%</b>.' ;	
		  $production_alert = 'yes';
	}		
	
	$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."' AND quantity_out < quantity_in GROUP BY product_item_id ";
	$stock_quantity_result = $con->query($stock_quantity_query);
	$row = mysqli_fetch_assoc($stock_quantity_result);
	$stock_quantity = $row['stock_quantity'];
	
	$new_stock_quantity = $stock_quantity + $produced;
	
		
	$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);
	
	
	$table_name = $_SESSION['db_prefix']."_product_store_lines";
	$product_stock_query = "INSERT INTO $table_name (product_item_id, production_no, production_date, batch_code, quantity_in ) VALUES ('".$product_item_id."', '".$production_no."', '".$production_date."', '".$batch_code."', '".$produced."'  ) ";
	$product_stock_result = $con->query($product_stock_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."' GROUP BY  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."', '21', '".$production_no."', '".$produced."',  '".$total_stock_quantity."', '".$production_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']."', '61', '".$production_no."' ,'".$production_date."','".$skips_no."','".$produced."' )";
	$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']."', '36', '".$production_no."' ,'".$product_item_id."','".$production_date."','".$produced."' )";
	$tracking_result = $con->query($tracking_query);
	

	if ($production_alert=='yes') {
		include('../include/production_margin_alert.php');
}

	if ($stock_alert=='yes') {
		include('../include/min_direct_materials_alert.php');
}

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