<?php
if(!isset($_SESSION)) 
{ 
session_start(); 
} 
if(!isset($_SESSION['is_login']) ){
header("location: login.php");
?>
<script>
window.location="login.php";
</script>
<?php
}

// $permission  = explode(',',$_SESSION['permission_id']);
// if(!in_array(19,$permission) && !in_array(1,$permission)  ){

// $op['is_success'] = false;
// $op['data'] 	  = "عذرا انت غير مخول للوصل الى هذه الصفحة";
// echo json_encode($op);
// exit;

// }

date_default_timezone_set($_SESSION['SYSTimezone']);

require_once 'vendor/autoload.php';

// for barcode


include('barcode/src/BarcodeGenerator.php');
include('barcode/src/BarcodeGeneratorPNG.php');
include('barcode/src/BarcodeGeneratorSVG.php');
include('barcode/src/BarcodeGeneratorJPG.php');
include('barcode/src/BarcodeGeneratorHTML.php');


if (isset($_POST['type']) || isset($_GET['type'])){
		require("db_connect.php");

		if( isset($_GET['type']) && ($_GET['type'] == "load")){
			// DB table to use
			// show All products for specific category
			$cond = isset($_GET['id'])? ' AND Inv.customer_id_fk='.$_GET['id']:'';
			$cond1 = isset($_GET['ip'])? ' AND Inv.invoice_status='.$_GET['ip']:'';
			
			if(isset($_GET['pt']) && $_GET['pt'] ==0){ 
				// in term
				$cond1 = $cond1 . ' AND Inv.invoice_date BETWEEN DATE_SUB(CURDATE(), INTERVAL Inv.pay_term DAY) AND CURDATE() ';
			}elseif(isset($_GET['pt']) && $_GET['pt'] ==1){
				// out term
				  $cond1 = $cond1 . ' AND Inv.invoice_date NOT BETWEEN DATE_SUB(CURDATE(), INTERVAL Inv.pay_term DAY) AND CURDATE() ';
			}
			
			$table  =  '(
				(
					SELECT
						invoice_id,
						invoice_number,
						invoice_date,
						invoice_status,
						invoice_usd_rate,
						invoice_currency,
						invoice_total_price_actual,
						invoice_total_net_balance,
						invoice_note,
						delivery_status,
						delivery_time,
						discount,
						discount_type,
						author,
						Inv.actual_discount,
						invoice_sequence_no,
						destination,
						customer_id_fk,
						pay_terms,
						return_date,
						invoice_transaction,
						original_invoice_id_fk,

						sum(item_sell_price_per_unit) AS total_price,
											
						customer_full_name AS name,
						customer_company,
						customer_phone AS phone
					 FROM
					 invoice Inv
					 LEFT JOIN customer Cus ON (Inv.customer_id_fk=Cus.customer_id) 
					 ,sell_item SI
				WHERE
					(SI.invoice_id_fk = Inv.invoice_id AND SI.is_deleted=0 AND Inv.delivery_status BETWEEN 8 AND 10   AND Inv.is_deleted = 0  '.$cond.' '.$cond1.') GROUP BY SI.invoice_id_fk
				)
			) AS ALLTBL';
			// Table's primary key
			//echo $table;
			$primaryKey = 'invoice_id';
			$columns = array(
		    	array( 'db' => 'invoice_id', 'dt' => 0 ),
		    	array( 'db' => 'invoice_number', 'dt' => 1 ),
		    	array( 'db' => 'invoice_date', 'dt' => 2, 'formatter'=> function($d){
							return date('Y-m-d',strtotime($d));
					} ),
		    	array( 'db' => 'delivery_time', 'dt' => 3, 'formatter'=> function($d){
							return date('Y-m-d',strtotime($d));
					} ),
		    	array( 'db' => 'name', 'dt' => 4 ),
		    	array( 'db' => 'author', 'dt' => 5 ),
		    	array( 'db' => 'customer_company', 'dt' => 6 ),
				
				array( 'db' => 'invoice_number', 'dt' => 7 , 'formatter'=> function($d){
							$generatorPNG = new Picqer\Barcode\BarcodeGeneratorPNG();

							return '<img src="data:image/png;base64,'.base64_encode($generatorPNG->getBarcode($d, $generatorPNG::TYPE_CODE_128,1)) . '">';
					}),
				array( 'db' => 'delivery_status', 'dt' => 8 ),
				

			  	array( 'db' => 'invoice_note', 'dt' => 9 ),
				array( 'db' => 'destination', 'dt' => 10 ),
				array( 'db' => 'pay_terms', 'dt' => 11 ),
				array( 'db' => 'customer_id_fk', 'dt' => 12 ),
				array( 'db' => 'invoice_transaction', 'dt' => 13 ),
				array( 'db' => 'original_invoice_id_fk', 'dt' => 14 ),
			);
			// SQL server connection information
			$sql_details = array(
			    'user' => $mysql_user,
			    'pass' => $mysql_password,
			    'db'   => $mysql_db,
			    'host' => $mysql_server
			);
			require( 'ssp.php' );
			echo json_encode(
			    SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns ,null, '' )
			);
			exit;
		}



		if ($_POST['type']=="show_invoice") {

			$id = $_POST['id'];


			$res = $db->query("SELECT 
										invoice_id, 
										invoice_number, 
										invoice_date, 
										invoice_status, 
										invoice_usd_rate, 
										invoice_currency,
										invoice_total_price_actual, 
										invoice_total_net_balance, 
										discount,
										discount_type, 
										I.actual_discount, 
										invoice_note,
										author,
										invoice_sequence_no,
										delivery_status, 
										delivery_time,
										destination, 
										offer_number, 
										date_of_offer, 
										term_and_conditions, 
										invoice_barcode,
										customer_id_fk, 
										pay_terms,
										request_for_quta_number,
										sell_item_id,
										sell_item_quantity,
										sell_item_height,
										sell_item_width, 
										sell_item_height_production, 
										sell_item_width_production, 
										item_buy_price_per_unit, 
										item_sell_price_per_unit, 
										tax_per_unit, 
										sell_item_barcode,
										sell_item_notes,
										sell_item_comment,
										cell_color,
										item_production_stage,
										sell_item_delivery_name, 
										SI.model_id_fk,
										color_product_id_fk, 
										customer_full_name,
										customer_email,
										customer_phone,
										customer_address,									  
									  	customer_company,

									  	M.model_name,
									  	M.model_code,
									  	M.model_type,

									  	Pr1.product_name AS product_name_color,
									  	Pr1.product_code AS product_code_color,
									  	Pr1.product_type AS product_type_color
									  		
									  													
									  	

									

								  

										FROM invoice I
										LEFT JOIN sell_item SI ON (SI.invoice_id_fk = I.invoice_id AND SI.is_deleted=0)
										LEFT JOIN model M ON (SI.model_id_fk = M.model_id )
										LEFT JOIN product Pr1 ON (SI.color_product_id_fk = Pr1.product_id )
					 	 				LEFT JOIN customer C ON (C.customer_id = I.customer_id_fk)

										WHERE I.invoice_id='$id'  AND I.is_deleted = 0");
			if ($res) {
				//$row = $res->fetch_all();
				//echo json_encode($row);

				$op  = array('invoice'=>array(),'details'=>array());
				while ($row = $res->fetch_assoc()) {

					if($row['delivery_status'] ==4){
						$deliver = 'Delivered In Factory';
					}elseif($row['delivery_status'] ==3){
						$deliver = 'Delivered In Shop';

					}elseif($row['delivery_status'] ==2){
						$deliver = 'Finished Production';

					}elseif($row['delivery_status'] ==1){
						$deliver = 'In Production Progress';

					}else{
						$deliver = 'Not Start Yet';

					}

					$generatorPNG = new Picqer\Barcode\BarcodeGeneratorPNG();

							

				   	$result['invoice_barcode1']  = '<img src="data:image/png;base64,'.base64_encode($generatorPNG->getBarcode($row['invoice_number'], $generatorPNG::TYPE_CODE_128,2)) . '">';

				   	$result['item_barcode']  = '<img src="data:image/png;base64,'.base64_encode($generatorPNG->getBarcode($row['sell_item_barcode'], $generatorPNG::TYPE_CODE_128,1)) . '">';



					$result['invoice_id']  					= $row['invoice_id'];
					$result['invoice_number']  				= $row['invoice_number'];
					$result['invoice_date']  				= date('Y-m-d',strtotime($row['invoice_date'])) ;
					$result['invoice_status']  				= $row['invoice_status'];
					$result['invoice_usd_rate']  			= $row['invoice_usd_rate'];
					$result['invoice_currency']  			= $row['invoice_currency'];
					$result['invoice_total_price_actual'] 	= $row['invoice_total_price_actual'];
					$result['invoice_total_net_balance']  	= $row['invoice_total_net_balance'];
					$result['discount']  					= $row['discount'];
					$result['discount_type']  				= $row['discount_type'];
					$result['actual_discount']  			= $row['actual_discount'];
					$result['invoice_note']  				= $row['invoice_note'];
					$result['invoice_sequence_no']  		= $row['invoice_sequence_no'];
					$result['author']  						= $row['author'];
					$result['destination']  				= $row['destination'];
					$result['sell_item_width']  			= $row['sell_item_width'];
					$result['sell_item_height']  			= $row['sell_item_height'];
					$result['sell_item_height_production']  = $row['sell_item_height_production'];
					$result['sell_item_width_production']  	= $row['sell_item_width_production'];
					$result['term_and_conditions']  		= $row['term_and_conditions'];
					$result['invoice_barcode']  			= $row['invoice_barcode'];
					$result['customer_id_fk']  				= $row['customer_id_fk'];
					$result['sell_item_id']  				= $row['sell_item_id'];
					$result['sell_item_quantity']  			= $row['sell_item_quantity'];
					$result['item_buy_price_per_unit']  	= $row['item_buy_price_per_unit'];
					$result['item_sell_price_per_unit']  	= $row['item_sell_price_per_unit'];
					$result['tax_per_unit']  				= $row['tax_per_unit'];
					$result['cell_color']  					= $row['cell_color'];
					$result['sell_item_barcode']  			= $row['sell_item_barcode'];
					$result['sell_item_notes']  			= $row['sell_item_notes'];
					$result['sell_item_comment']  			= $row['sell_item_comment'];
					$result['item_production_stage']  		= $row['item_production_stage'];

					$result['model_id_fk']  				= $row['model_id_fk'];
					$result['color_product_id_fk']  		= $row['color_product_id_fk'];
					$result['customer_full_name']  			= $row['customer_full_name'];
					$result['customer_phone']  				= $row['customer_phone'];
					$result['customer_address']  			= $row['customer_address'];
					$result['customer_email']  				= $row['customer_email'];
					$result['customer_company']  			= $row['customer_company'];
					
					$result['model_name']  					= $row['model_name'];
					$result['model_code']  					= $row['model_code'];
					$result['model_type']  					= $row['model_type'];
					$result['product_name_color']  			= $row['product_name_color'];
					$result['product_code_color']  			= $row['product_code_color'];
					$result['product_type_color']  			= $row['product_type_color'];
					
					$result['delivery_time']  				= $row['delivery_time'];
					$result['pay_terms']  					= $row['pay_terms'];
					
					$result['delivery_status']  			= $deliver;
					
					array_push($op['invoice'],$result);
			
				}		
				echo json_encode($op);
				exit;
			}else{
				echo 0;
				exit;
			}
		}



		if ($_POST['type']=="show_stickers") {

			$id = $_POST['id'];


			$res = $db->query("SELECT 
										invoice_id, 
										invoice_number, 
										
										author,
										
										
										customer_full_name,
										customer_email,
										customer_phone,
										customer_address,									  
									  	customer_company,

									  	packaging
									  		
									  													
									  	

									

								  

										FROM invoice I
										
					 	 				LEFT JOIN customer C ON (C.customer_id = I.customer_id_fk)

										WHERE I.invoice_id='$id'  AND I.is_deleted = 0");
			if ($res) {
				//$row = $res->fetch_all();
				//echo json_encode($row);

				$op  = array('invoice'=>array(),'details'=>array());
				while ($row = $res->fetch_assoc()) {

					

				

					$result['invoice_id']  					= $row['invoice_id'];
					$result['invoice_number']  				= $row['invoice_number'];
					
					$result['author']  						= $row['author'];
					
					
					$result['customer_full_name']  			= $row['customer_full_name'];
					$result['customer_phone']  				= $row['customer_phone'];
					$result['customer_address']  			= $row['customer_address'];
					$result['customer_email']  				= $row['customer_email'];
					$result['customer_company']  			= $row['customer_company'];
					
					$result['packaging']  					= $row['packaging'];
					
					
					
					array_push($op['invoice'],$result);
			
				}		
				echo json_encode($op);
				exit;
			}else{
				echo 0;
				exit;
			}
		}


		if ($_POST['type']=="set_package") {

			$id = $_POST['id'];


			$res = $db->query("SELECT 
										invoice_id, 
										packaging
																				
										FROM invoice I
										

										WHERE I.invoice_id='$id'  AND I.is_deleted = 0");
			if ($res) {
			
				$op  = array('invoice'=>array(),'details'=>array());
				while ($row = $res->fetch_assoc()) {

					



					$result['invoice_id']  					= $row['invoice_id'];
					$result['packaging']  					= $row['packaging'];
					
					
					array_push($op['invoice'],$result);
			
				}		
				echo json_encode($op);
				exit;
			}else{
				echo 0;
				exit;
			}
		}




		if ($_POST['type']=="change_delivery_status") {

			$id 						= $_POST['id'];
			
			$delivery_status 			= $_POST['delivery_status'];
			

			$lastaction 				= "Set Delivered";
			
			$reg_date 					= date("Y-m-d h:i:s", time());

			$db->autocommit(FALSE); // to rollback insertion if update fail

			$sql ="UPDATE invoice SET 	delivery_status 		= '$delivery_status',
										updated_at				= '$reg_date', 
										last_action 			= '$lastaction', 
										last_action_by 			= '".$_SESSION['user_id']."'  
							WHERE invoice_id = '$id' ";
					
			$res = $db->query($sql);

			if ($res){

				$sql2 ="UPDATE sell_item SET 	item_production_stage 	= '$delivery_status',
										updated_at				= '$reg_date', 
										last_action 			= '$lastaction', 
										last_action_by 			= '".$_SESSION['user_id']."'  
							WHERE invoice_id_fk = '$id' ";
					
				$res2 = $db->query($sql2);

				if ($res2){

					$sql3 ="UPDATE sell_item_status SET 	item_production_stage 	= '$delivery_status',
										st_stage_9				= '$reg_date', 
										updated_at				= '$reg_date', 
										last_action 			= '$lastaction', 
										last_action_by 			= '".$_SESSION['user_id']."'  
							WHERE invoice_id_fk = '$id' ";
					
					$res3 = $db->query($sql3);
					
					if ($res3){

						$sql4 ="UPDATE invoice_delivery SET 
										delivery_status 	= '$delivery_status',
										
										updated_at				= '$reg_date', 
										last_action 			= '$lastaction', 
										last_action_by 			= '".$_SESSION['user_id']."'  
							WHERE invoice_id_fk = '$id' ";
					
						$res4 = $db->query($sql4);

						if($res4){
							$db->commit();
							$op['is_success'] = true;
							$op['data'] 	  = "تمت عملية التحديث بنجاح";
							echo json_encode($op);
							exit;
						}else{
							$db->rollback();
							$op['is_success'] = false;
							$op['data'] 	  = "حصل خطأ : لم يتم تحديث حالة التسليم بنجاح ";
							echo json_encode($op);
							exit;
						}	

						

					}else{
						$db->rollback();
						$op['is_success'] = false;
						$op['data'] 	  = "حصل خطأ : لم يتم تحديث حالة التسليم بنجاح ";
						echo json_encode($op);
						exit;
					}

				}else{
					$db->rollback();
					$op['is_success'] = false;
					$op['data'] 	  = "حصل خطأ : لم يتم تحديث حالة التسليم بنجاح ";
					echo json_encode($op);
					exit;
				}

			}else{
				$db->rollback();
				$op['is_success'] = false;
				$op['data'] 	  = "حصل خطأ : لم يتم تحديث حالة التسليم بنجاح ";
				echo json_encode($op);
				exit;
			}

			
		}


		if ($_POST['type']=="update_status") {

			$recieved_barcode 				= $_POST['recieved_barcode'];
			$move_to_shop_barcode 			= $_POST['move_to_shop_barcode'];
			$delivery_to_customer_barcode 	= $_POST['delivery_to_customer_barcode'];
			
			$time_ 							= "";
			
			$lastaction 					= "Set Delivered";
			
			$reg_date 						= date("Y-m-d h:i:s", time());


			if($recieved_barcode  != null AND $recieved_barcode  != ''){
				$delivery_status 	= 9;
				$bar_code 			= $recieved_barcode;
				$time_ 				= "received_in_factory_time = '$reg_date',";
			}

			if($move_to_shop_barcode  != null AND $move_to_shop_barcode  != ''){
				$delivery_status 	= 10;
				$bar_code 			= $move_to_shop_barcode;
				$time_ 				= "out_factory_to_shop_time = '$reg_date',";

			}

			if($delivery_to_customer_barcode  != null AND $delivery_to_customer_barcode  != ''){
				$delivery_status 	= 11;
				$bar_code 			= $delivery_to_customer_barcode;
				$time_ 				= "delivery_to_customer_factory_time = '$reg_date',";

			}
			
			

			$db->autocommit(FALSE); // to rollback insertion if update fail

			$sql ="UPDATE invoice_delivery SET 
										delivery_status 	= '$delivery_status',
										".$time_ ."
										updated_at			= '$reg_date', 
										last_action 		= '$lastaction', 
										last_action_by 		= '".$_SESSION['user_id']."'  
							WHERE pack_barcode = '$bar_code' ";
					
			$res = $db->query($sql);


			

			if ($res){

				$Sel = $db->query("SELECT 	delivery_status,
											invoice_id_fk
										
										FROM invoice_delivery  
										
									WHERE  pack_barcode = '$bar_code' AND is_deleted = 0");
				$row 	= 	$Sel->fetch_assoc();

				$id 	= 	$row['invoice_id_fk'];


				$Sel = $db->query("SELECT COUNT(*) AS Pack_CNT
										
										FROM invoice_delivery  
										
									WHERE  invoice_id_fk = '$id' AND is_deleted = 0");
				$row 		= 	$Sel->fetch_assoc();

				$Pack_CNT 	= 	$row['Pack_CNT'];


				$Sel = $db->query("SELECT COUNT(*) AS Status_CNT
										
										FROM invoice_delivery  
										
									WHERE  invoice_id_fk = '$id' AND is_deleted = 0 AND delivery_status >= '$delivery_status'");
				$row 		= 	$Sel->fetch_assoc();

				$Status_CNT = 	$row['Status_CNT'];



				if($Status_CNT >= $Pack_CNT){

					$sql ="UPDATE invoice SET 	delivery_status = '$delivery_status',
										updated_at				= '$reg_date', 
										last_action 			= '$lastaction', 
										last_action_by 			= '".$_SESSION['user_id']."'  
							WHERE invoice_id = '$id' ";
					
					$res = $db->query($sql);

					if($res){

						$sql2 ="UPDATE sell_item SET 	
										item_production_stage 	= '$delivery_status',
										updated_at				= '$reg_date', 
										last_action 			= '$lastaction', 
										last_action_by 			= '".$_SESSION['user_id']."'  
							WHERE invoice_id_fk = '$id' ";
					
						$res2 = $db->query($sql2);

						if ($res2){

							$sql3 ="UPDATE sell_item_status SET 
										item_production_stage 	= '$delivery_status',
										st_stage_9				= '$reg_date', 
										updated_at				= '$reg_date', 
										last_action 			= '$lastaction', 
										last_action_by 			= '".$_SESSION['user_id']."'  
									WHERE invoice_id_fk = '$id' ";
							
							$res3 = $db->query($sql3);
							
							if ($res3){
								
								$db->commit();
								$op['is_success'] = true;
								$op['data'] 	  = "تمت عملية التحديث بنجاح";
								echo json_encode($op);
								exit;

							}else{
								$db->rollback();
								$op['is_success'] = false;
								$op['data'] 	  = "حصل خطأ : لم يتم تحديث حالة التسليم بنجاح ";
								echo json_encode($op);
								exit;
							}

						}else{
							$db->rollback();
							$op['is_success'] = false;
							$op['data'] 	  = "حصل خطأ : لم يتم تحديث حالة التسليم بنجاح ";
							echo json_encode($op);
							exit;
						}


					}else{

						$db->rollback();
						$op['is_success'] = false;
						$op['data'] 	  = "حصل خطأ : لم يتم تحديث حالة التسليم بنجاح ";
						echo json_encode($op);
						exit;

					}


				} // if $Pack_CNT

				
				$db->commit();
				$op['is_success'] = true;
				$op['data'] 	  = "تمت عملية التحديث بنجاح";
				echo json_encode($op);
				exit;
				
			}else{
				$db->rollback();
				$op['is_success'] = false;
				$op['data'] 	  = "حصل خطأ : لم يتم تحديث حالة التسليم بنجاح ";
				echo json_encode($op);
				exit;
			}

			
		}



	}
	else{
		header("Location:../");
		exit;
	}
?>
