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



date_default_timezone_set($_SESSION['SYSTimezone']);

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

		if( isset($_GET['type']) && ($_GET['type'] == "load")){
			
			$cond1 = isset($_GET['id'])? ' AND S.stock_id='.$_GET['id']:'';
			$cond2 = isset($_GET['pid'])? ' AND P.product_id='.$_GET['pid']:'';

			$cond5 = '';
			if(isset($_GET['branch_id']) && $_GET['branch_id'] !=0){ 
				$cond5 = ' AND S.branch_id_fk ='.$_GET['branch_id'];
			}
			
			$cond = $cond1 . $cond2 . $cond5 ;
			
			$cond3 = isset($_GET['bid'])? ' AND PSK.branch_id_fk='.$_GET['bid']:'';
			$cond4 = isset($_GET['sid'])? ' AND PSK.supplier_id_fk='.$_GET['sid']:'';

			
			
			$cond_sub = $cond3 . $cond4 ;


			$table  =  '(

				SELECT
					
						(
							IFNULL(SQTP.total_purchase,0)
							+
							IFNULL(SQTI.total_initial,0)
							+
							IFNULL(SQTR.total_return,0)
							+
							IFNULL(SQMIQ.total_move_in,0)
							-
							IFNULL(SQTINV.total_invoice,0)
							-
							IFNULL(SQTD.total_damage,0)
							-
							IFNULL(SQTMO.total_moved_out,0)
							-
							IFNULL(SQTU.total_used,0)
							-
							IFNULL(SQTOF.total_offer,0)
							-
							IFNULL(SQTEX.total_expire,0)
						)  AS on_hand_quantity,

						

						stock_id,
						stock_name,
						product_id,
						product_name,
						P.sell_price
					
				FROM
					product  P
					LEFT JOIN stock S ON (S.is_deleted=0)

					
					LEFT JOIN (SELECT SUM(recieved_quantity) AS total_purchase, product_id_fk, stock_id_fk

								FROM product_stock PSK 
								WHERE PSK.is_deleted = 0 AND PSK.stock_transaction="Purchase" '.$cond_sub.'  GROUP BY product_id_fk) SQTP ON (SQTP.product_id_fk = P.product_id AND SQTP.stock_id_fk = S.stock_id )

					LEFT JOIN (SELECT SUM(recieved_quantity) AS total_initial, product_id_fk, stock_id_fk

								FROM product_stock PSK 
								WHERE PSK.is_deleted = 0 AND PSK.stock_transaction="Initial" '.$cond_sub.'  GROUP BY product_id_fk) SQTI ON (SQTI.product_id_fk = P.product_id AND SQTI.stock_id_fk = S.stock_id )

					LEFT JOIN (SELECT SUM(recieved_quantity) AS total_return, product_id_fk, stock_id_fk

								FROM product_stock PSK 
								WHERE PSK.is_deleted = 0 AND PSK.stock_transaction="Return" '.$cond_sub.'  GROUP BY product_id_fk) SQTR ON (SQTR.product_id_fk = P.product_id AND SQTR.stock_id_fk = S.stock_id )

					LEFT JOIN (SELECT SUM(recieved_quantity) AS total_move_in, product_id_fk, stock_id_fk

								FROM product_stock PSK 
								WHERE PSK.is_deleted = 0 AND PSK.stock_transaction="Moved In" '.$cond_sub.'  GROUP BY product_id_fk) SQMIQ ON (SQMIQ.product_id_fk = P.product_id AND SQMIQ.stock_id_fk = S.stock_id )

					LEFT JOIN (SELECT SUM(recieved_quantity) AS total_invoice, product_id_fk, stock_id_fk

								FROM product_stock PSK 
								WHERE PSK.is_deleted = 0 AND PSK.stock_transaction="Invoice" '.$cond_sub.'  GROUP BY product_id_fk) SQTINV ON (SQTINV.product_id_fk = P.product_id AND SQTINV.stock_id_fk = S.stock_id )

					LEFT JOIN (SELECT SUM(recieved_quantity) AS total_damage, product_id_fk, stock_id_fk

								FROM product_stock PSK 
								WHERE PSK.is_deleted = 0 AND PSK.stock_transaction="Damage" '.$cond_sub.'  GROUP BY product_id_fk) SQTD ON (SQTD.product_id_fk = P.product_id AND SQTD.stock_id_fk = S.stock_id )

					LEFT JOIN (SELECT SUM(recieved_quantity) AS total_moved_out, product_id_fk, stock_id_fk

								FROM product_stock PSK 
								WHERE PSK.is_deleted = 0 AND PSK.stock_transaction="Moved Out" '.$cond_sub.'  GROUP BY product_id_fk) SQTMO ON (SQTMO.product_id_fk = P.product_id AND SQTMO.stock_id_fk = S.stock_id )

					LEFT JOIN (SELECT SUM(recieved_quantity) AS total_used, product_id_fk, stock_id_fk

								FROM product_stock PSK 
								WHERE PSK.is_deleted = 0 AND PSK.stock_transaction="Used" '.$cond_sub.'  GROUP BY product_id_fk) SQTU ON (SQTU.product_id_fk = P.product_id AND SQTU.stock_id_fk = S.stock_id )

					LEFT JOIN (SELECT SUM(recieved_quantity) AS total_offer, product_id_fk, stock_id_fk

								FROM product_stock PSK 
								WHERE PSK.is_deleted = 0 AND PSK.stock_transaction="Offer" '.$cond_sub.'  GROUP BY product_id_fk) SQTOF ON (SQTOF.product_id_fk = P.product_id AND SQTOF.stock_id_fk = S.stock_id )

					LEFT JOIN (SELECT SUM(recieved_quantity) AS total_expire, product_id_fk, stock_id_fk

								FROM product_stock PSK 
								WHERE PSK.is_deleted = 0 AND PSK.stock_transaction="Expired" '.$cond_sub.'  GROUP BY product_id_fk) SQTEX ON (SQTEX.product_id_fk = P.product_id AND SQTEX.stock_id_fk = S.stock_id )

					
					
					
				WHERE
					( P.is_deleted = 0 '.$cond.') 
		
				) AS ALLTBL';
			
			if(!isset($_GET['id']) || ($_GET['id']=='all')){
			
				$table  =  '(

				SELECT
					
						(
							IFNULL(SQTP.total_purchase,0)
							+
							IFNULL(SQTI.total_initial,0)
							+
							IFNULL(SQTR.total_return,0)
							+
							IFNULL(SQMIQ.total_move_in,0)
							-
							IFNULL(SQTINV.total_invoice,0)
							-
							IFNULL(SQTD.total_damage,0)
							-
							IFNULL(SQTMO.total_moved_out,0)
							-
							IFNULL(SQTU.total_used,0)
							-
							IFNULL(SQTOF.total_offer,0)
							-
							IFNULL(SQTEX.total_expire,0)
						)  AS on_hand_quantity,

						"1" AS stock_id,
						"All" as stock_name,
						product_id,
						product_name,
						P.sell_price
					
				FROM product  P

					
					LEFT JOIN (SELECT SUM(recieved_quantity) AS total_purchase, product_id_fk, stock_id_fk

								FROM product_stock PSK 
								WHERE PSK.is_deleted = 0 AND PSK.stock_transaction="Purchase" '.$cond_sub.'  GROUP BY product_id_fk) SQTP ON (SQTP.product_id_fk = P.product_id  )

					LEFT JOIN (SELECT SUM(recieved_quantity) AS total_initial, product_id_fk, stock_id_fk

								FROM product_stock PSK 
								WHERE PSK.is_deleted = 0 AND PSK.stock_transaction="Initial" '.$cond_sub.'  GROUP BY product_id_fk) SQTI ON (SQTI.product_id_fk = P.product_id )

					LEFT JOIN (SELECT SUM(recieved_quantity) AS total_return, product_id_fk, stock_id_fk

								FROM product_stock PSK 
								WHERE PSK.is_deleted = 0 AND PSK.stock_transaction="Return" '.$cond_sub.'  GROUP BY product_id_fk) SQTR ON (SQTR.product_id_fk = P.product_id  )

					LEFT JOIN (SELECT SUM(recieved_quantity) AS total_move_in, product_id_fk, stock_id_fk

								FROM product_stock PSK 
								WHERE PSK.is_deleted = 0 AND PSK.stock_transaction="Moved In" '.$cond_sub.'  GROUP BY product_id_fk) SQMIQ ON (SQMIQ.product_id_fk = P.product_id )

					LEFT JOIN (SELECT SUM(recieved_quantity) AS total_invoice, product_id_fk, stock_id_fk

								FROM product_stock PSK 
								WHERE PSK.is_deleted = 0 AND PSK.stock_transaction="Invoice" '.$cond_sub.'  GROUP BY product_id_fk) SQTINV ON (SQTINV.product_id_fk = P.product_id )

					LEFT JOIN (SELECT SUM(recieved_quantity) AS total_damage, product_id_fk, stock_id_fk

								FROM product_stock PSK 
								WHERE PSK.is_deleted = 0 AND PSK.stock_transaction="Damage" '.$cond_sub.'  GROUP BY product_id_fk) SQTD ON (SQTD.product_id_fk = P.product_id )

					LEFT JOIN (SELECT SUM(recieved_quantity) AS total_moved_out, product_id_fk, stock_id_fk

								FROM product_stock PSK 
								WHERE PSK.is_deleted = 0 AND PSK.stock_transaction="Moved Out" '.$cond_sub.'  GROUP BY product_id_fk) SQTMO ON (SQTMO.product_id_fk = P.product_id )

					LEFT JOIN (SELECT SUM(recieved_quantity) AS total_used, product_id_fk, stock_id_fk

								FROM product_stock PSK 
								WHERE PSK.is_deleted = 0 AND PSK.stock_transaction="Used" '.$cond_sub.'  GROUP BY product_id_fk) SQTU ON (SQTU.product_id_fk = P.product_id )

					LEFT JOIN (SELECT SUM(recieved_quantity) AS total_offer, product_id_fk, stock_id_fk

								FROM product_stock PSK 
								WHERE PSK.is_deleted = 0 AND PSK.stock_transaction="Offer" '.$cond_sub.'  GROUP BY product_id_fk) SQTOF ON (SQTOF.product_id_fk = P.product_id )

					LEFT JOIN (SELECT SUM(recieved_quantity) AS total_expire, product_id_fk, stock_id_fk

								FROM product_stock PSK 
								WHERE PSK.is_deleted = 0 AND PSK.stock_transaction="Expired" '.$cond_sub.'  GROUP BY product_id_fk) SQTEX ON (SQTEX.product_id_fk = P.product_id )

					
					
					
				WHERE
					( P.is_deleted = 0 '.$cond.') 
		
				) AS ALLTBL';
			
			
			
			}
			// Table's primary key
			//echo $table;
			$primaryKey = 'product_id';
			$columns = array(
			    array( 'db' => 'product_id', 'dt' => 1 ),

			    array( 'db' => 'product_name', 'dt' => 2 ),
			    array( 'db' => 'stock_name', 'dt' => 3 ),
			    array( 'db' => 'sell_price', 'dt' => 4 ),
			    array( 'db' => 'on_hand_quantity', 'dt' => 5 ),
			    array( 'db' => 'stock_id', 'dt' => 6 ),
			    array( 'db' => 'stock_id', 'dt' => 7 ),
			    // array( 'db' => 'group_name', 'dt' => 7 ),
				array( 'db' => 'stock_id', 'dt' => 8 ),
				// array( 'db' => 'expenses_group_id_fk', 'dt' => 9 ),
				array( 'db' => 'stock_id', 'dt' => 9 ),
				
				
			);
			// 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']=="get_edit") {

			$id = $_POST['obj_id'];
			$op  = array('is_success'=>false, 'data' => array());

			$res = $db->query("SELECT 
									asset_id,
				
									expenses_name,
									expenses_date,
									bill_number,
									expenses_unit_price,
									expenses_note,
									branch_id_fk
									
				 				FROM assets  WHERE is_deleted = 0 AND asset_id = '$id'");
			if ($res) {
				
				
				while ($row = $res->fetch_assoc()) {
				   
						$result['asset_id']  				= $row['asset_id'];
						$result['expenses_name']  			= $row['expenses_name'];
						$result['expenses_date']  			= $row['expenses_date'];
						$result['expenses_unit_price']  	= $row['expenses_unit_price'];
						$result['bill_number']  			= $row['bill_number'];
						$result['expenses_note']  			= $row['expenses_note'];
						// $result['expenses_group_id_fk']  	= $row['expenses_group_id_fk'];
						$result['branch_id_fk']  			= $row['branch_id_fk'];
						
						
						array_push($op['data'],$result);
				}
				$op['is_success'] = true;
				echo json_encode($op);
				exit;
			}else{
				$op['is_success'] = false;
				$op['data'] 	  = "No Data";
				echo json_encode($op);

				exit;
			}
		}

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

			$id 	= $_POST['id'];
			$bid 	= $_POST['bid'];
			$sid 	= $_POST['sid'];
			$cid 	= $_POST['cid'];
			$pid 	= $_POST['pid'];
			$cat_id = $_POST['cat_id'];

			$cond_sub 	='';
			$cond 		='';

			if ($sid >0) {
				$cond_sub .= " AND PSK.supplier_id_fk=$sid ";
			}

			if ($bid >0) {
				$cond_sub .= " AND PSK.branch_id_fk=$bid ";
			}


			if ($pid >0) {
				$cond .= " AND P.product_id=$pid ";
			}

			if ($cat_id >0) {
				$cond .= " AND P.category_id_fk=$cat_id ";
			}

			

			$op  = array('is_success'=>false, 'data' => array());

			$res = $db->query("SELECT
					
								(0)  AS on_hand_quantity,

								
								product_id,
								product_name,
								P.sell_price,
								P.cost_price,
								image_path,
								category,
								product_note,
								category_id
					
						FROM product  P

							LEFT JOIN category C ON (P.category_id_fk = C.category_id)
							
							
						WHERE
							 P.is_deleted = 0 AND P.is_hidden=0 $cond  ");
			if ($res) {
				
				
				while ($row = $res->fetch_assoc()) {
				   
						$result['product_id']  				= $row['product_id'];
						$result['product_name']  			= $row['product_name'];
						$result['sell_price']  				= $row['sell_price'];
						$result['cost_price']  				= $row['cost_price'];
						$result['on_hand_quantity']  		= $row['on_hand_quantity'];
						$result['image_path']  				= $row['image_path'];
						$result['category']  				= $row['category'];
						$result['category_id']  			= $row['category_id'];
						$result['product_note']  			= $row['product_note'];
						
						
						array_push($op['data'],$result);
				}
				$op['is_success'] = true;
				echo json_encode($op);
				exit;
			}else{
				$op['is_success'] = false;
				$op['data'] 	  = "No Data";
				echo json_encode($op);

				exit;
			}
		}

		

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

			
			$product_id						= $_POST["product_id"];
			$quantity						= $_POST["quantity"];
			$sell_price						= $_POST["sell_price"];
			$sell_item_note					= $_POST["sell_item_note"];
			
			$buy_price						= $_POST["buy_price"];
			$discount_array					= $_POST["discount_array"];
			$discount_type_array			= $_POST["discount_type_array"];
			$actual_discount_array			= $_POST["actual_discount_array"];
			$row_price						= $_POST["row_price"];
			
			
			$customer_id_fk 				= addslashes($_POST["customer_id_fk"]);
			$author							= addslashes($_POST["author"]);
			$invoice_date			  		= addslashes($_POST["invoice_date"]);
			$branch_id_fk					= addslashes($_POST["branch_id_fk"]);
			$stock_id_fk					= addslashes($_POST["stock_id_fk"]);
			$invoice_note	  				= addslashes($_POST["invoice_note"]);
			$saleman_id_fk	  				= addslashes($_POST["saleman_id_fk"]);

			$discount						= addslashes($_POST["discount"]);
			$discount_type					= addslashes($_POST["discount_type"]);
			$discount_actual				= addslashes($_POST["discount_actual"]);
			
			$invoice_total_no_disc			= addslashes($_POST["invoice_total_no_disc"]);
			$total_invoice					= addslashes($_POST["total_invoice"]);
			$total_paid						= addslashes($_POST["total_paid"]);
			
			$invoice_type 					= addslashes($_POST["invoice_type"]);
			$invoice_transaction 			= addslashes($_POST["invoice_transaction"]);

			
			$invoice_currency					= 1; //USD
			$term_and_conditions = "";

			
			$id 							= addslashes($_POST["invoice_id"]);

			

						

			

			$delete = 0;
			$reg_date = date("Y-m-d h:i:s", time());
			$lastaction = "Update Invoice";

			$res = $db->query("SELECT invoice_status FROM invoice WHERE invoice_id  = $id");
			$row = $res->fetch_assoc();

			$is_paid 	= $row['invoice_status'];

			

			if($invoice_type == 4 ){
				$is_paid = 4; // Offer

			}

			

			if($invoice_date == null || $invoice_date ==''){
				$invoice_date = date("Y-m-d h:i:s", time());
			}
			
			if($saleman_id_fk == null || $saleman_id_fk ==''){
				$saleman_id_fk = 0;
			}

			$db->autocommit(FALSE); // to rollback insertion if update fail
			$sql ="UPDATE invoice SET   
								invoice_date 				= '$invoice_date', 		
								invoice_status 				= '$is_paid',
										
								invoice_total_price_actual 	= '$invoice_total_no_disc', 
								invoice_total_net_balance 	= '$total_invoice', 
								discount 					= '$discount', 
								discount_type 				= '$discount_type', 
								actual_discount 			= '$discount_actual', 
								invoice_note 				= '$invoice_note',
								author 						= '$author', 
										
								invoice_transaction 		= '$invoice_transaction', 
								branch_id_fk 				= '$branch_id_fk', 
								stock_id_fk 				= '$stock_id_fk', 
								customer_id_fk 				= '$customer_id_fk', 
								saleman_id_fk 				= '$saleman_id_fk', 
								updated_at 					= '$reg_date', 
								last_action 				= '$lastaction', 
								last_action_by 				= '".$_SESSION['user_id']."'
						 		WHERE invoice_id = '$id'";
			
			$res = $db->query($sql);
			if ($res) {

				$invoice_inserted_id = $id;

				$chek_insertion = true;


				$delete_qry = "DELETE FROM sell_item WHERE invoice_id_fk = $id";

				$res2 = $db->query($delete_qry);

				if(!$res2){
					$db->rollback();
					$op['is_success'] = false;
					$op['data'] 	  = "هناك خطأ : لم يتم تحديث المبيعات الرجاء اعادة تحميل الصفحة والمحاولة من جديد";
					echo json_encode($op);
					exit;
				}


				$delete_qry1 = "DELETE FROM product_stock WHERE invoice_id_fk = $id";

				$res_d = $db->query($delete_qry1);

				if(!$res_d){
					$db->rollback();
					$op['is_success'] = false;
					$op['data'] 	  = "هناك خطأ : لم يتم تحديث المبيعات الرجاء اعادة تحميل الصفحة والمحاولة من جديد";
					echo json_encode($op);
					exit;
				}


				

				// insert into sell items the sold products
				$lastaction = "Update Sell Product";
				

				for ($i=1;$i<count($product_id);$i++){

					

					$tax_per_unit 				= 0;
					$sell_item_delivery_name 	= '';
					$barcode                   	= "";
					
					if($discount_array[$i]==''){
                        $discount_array[$i] =0;
                        $discount_type_array[$i]=1;
                        $actual_discount_array[$i]=0;
                    }
                    
                    if($buy_price[$i]=='' || $buy_price[$i]==null || $buy_price[$i]==NULL || $buy_price[$i]=='null'){
                        $buy_price[$i] =0;
                        
                        
                    }

					$sql2 ="INSERT INTO sell_item (	sell_item_quantity, 
													sell_discount,
													sell_discount_type,
													actual_discount,
													item_buy_price_per_unit,
													item_sell_price_per_unit,
													tax_per_unit,
													sell_item_barcode,
													sell_date,
													sell_item_notes,
													product_id_fk,
													invoice_id_fk,
													
													created_by,
													created_at,
													updated_at,
													last_action,
													last_action_by)

 								 			VALUES( '$quantity[$i]',
	 								 				'$discount_array[$i]',
	 								 				'$discount_type_array[$i]',
	 								 				'$actual_discount_array[$i]',
	 								 				'$buy_price[$i]',
	 								 				'$sell_price[$i]',
	 								 				'0',
	 								 				'$barcode',
	 								 				'$invoice_date',
	 								 				'$sell_item_note[$i]',
	 								 				'$product_id[$i]',
	 								 				'$invoice_inserted_id',
	 								 				'".$_SESSION['user_id']."',
	 								 				'$reg_date',
	 								 				'$reg_date',
	 								 				'$lastaction',
	 								 				'".$_SESSION['user_id']."')";
					
					$res2 = $db->query($sql2);

					if ($res2) {
						
						if($invoice_type < 3){
						
							$last_inserted_sell_item_id = $db->insert_id;


							
							$sqlS ="INSERT INTO product_stock (
														  recieved_quantity,
														  stock_transaction,
														  stock_transaction_type,
														  transaction_date,
														  invoice_id_fk,
														  sell_item_fk,
														  product_id_fk,
														  stock_id_fk,
														  branch_id_fk,
														  created_by,
														  created_at,
														  updated_at,
														  last_action,
														  last_action_by)
												   VALUES('$quantity[$i]',
												   		  'Invoice',
												   		  '2',
												   		  '$reg_date',
												   		  '$invoice_inserted_id',
												   		  '$last_inserted_sell_item_id',
												   		  '$product_id[$i]',
												   		  '$stock_id_fk',
												   		  '$branch_id_fk',
												   		  '".$_SESSION['user_id']."',
												   		  '$reg_date',
												   		  '$reg_date',
												   		  '$lastaction',
												   		  '".$_SESSION['user_id']."')";

							$res_S = $db->query($sqlS);
							// now we should update sequence no and check if new year here
							if(!$res_S){
								$chek_insertion = false;

								$db->rollback();
								$op['is_success'] = false;
								$op['data'] 	  = "هناك خطأ : لم يتم اضافة المشتريات الرجاء اعادة تحميل الصفحة والمحاولة من جديد";
								echo json_encode($op);
								exit;
							}
						}


					}else{
								// echo $db->error;

						$chek_insertion = false;
						$db->rollback();
						$op['is_success'] = false;
						$op['data'] 	  = "هناك خطأ : لم يتم اضافة المبيعات الرجاء اعادة تحميل الصفحة والمحاولة من جديد";
						echo json_encode($op);
						exit;
					}

				} // for i


				


				if($chek_insertion == true ){

					$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;
			}


		}


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

			$id	 = addslashes($_POST["obj_id"]);
			$reg_date = date("Y-m-d H:i:s", time());
			$lastaction = "Delete Invoice";

			$op  = array('is_success'=>false, 'data' => '');


			$db->autocommit(FALSE); // to rollback update if update fail
			$res = $db->query("UPDATE invoice SET 
											is_deleted = 1 ,
											deleted_at = '$reg_date',
											last_action='$lastaction',
											last_action_by='".$_SESSION['user_id']."' 
									WHERE invoice_id=$id");
			if ($res){

					// update Product table for sell price and avarage price

					$sql1 ="UPDATE sell_item SET 
											is_deleted = '1',
								 			deleted_at='$reg_date', 
								 			last_action='$lastaction', 
								 			last_action_by='".$_SESSION['user_id']."'  
								 	WHERE invoice_id_fk = $id";

					$res1 = $db->query($sql1);

					if ($res1) {


						$sql2 ="UPDATE product_stock SET 
												is_deleted = '1',
									 			deleted_at='$reg_date', 
									 			last_action='$lastaction', 
									 			last_action_by='".$_SESSION['user_id']."'  
									 	WHERE invoice_id_fk = $id";

						$res2 = $db->query($sql2);

						if ($res1) {

							$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 ($_POST['type'] == "create") {

            $customer_id_fk					= $_SESSION["user_id"];
			$invoice_type					= $_POST["invoice_type"];
			$invoice_date					= '';//$_POST["invoice_date"];
			$invoice_transaction			= $_POST["invoice_transaction"];
			$branch_id_fk					= $_SESSION["branch_id"];
			// $stock_id_fk					= $_POST["stock_id_fk"];
			
			$saleman_id_fk					= $_SESSION["user_id"];
			$invoice_note					= ''; //$_POST["invoice_note"];
			$grand_total					= $_POST["grand_total"];
			
			$product_id_fk					= $_POST["product_id_fk"];
			$purchase_quantity				= $_POST["purchase_quantity"];
			$sell_price						= $_POST["sell_price"];
			$discount						= $_POST["discount"];
			$total_item_cost				= $_POST["total_item_cost"];
			$sell_item_note					= $_POST["sell_item_note"];
			
			
			
			$invoice_currency				= 1; //USD
			$term_and_conditions 			= "";
			$author 						='';
						

			$is_paid = 4; // not paid
			

			$delete = 0;
			$reg_date = date("Y-m-d H:i:s", time());
			$lastaction = "Add New Order";

			$res = $db->query("SELECT stock_id FROM stock WHERE is_deleted=0 AND  branch_id_fk=".$branch_id_fk );

			if (mysqli_num_rows($res)>0) {
				$row = $res->fetch_assoc();
				$stock_id_fk 	= $row['stock_id'];
			}else{
				$stock_id_fk 	= 0;

			}

			


			$res = $db->query("SELECT invoice_sequence_no FROM invoice ORDER BY invoice_id DESC LIMIT 1");
			$row = $res->fetch_assoc();
			$invoice_sequence_no 	= $row['invoice_sequence_no'] +1;

			$dd = date("y", time());
			
			
			$invoice_number = "pp-".($invoice_sequence_no);
			
			if($invoice_date == null || $invoice_date ==''){
				$invoice_date = date("Y-m-d H:i:s", time());
			}
			
			if($saleman_id_fk == null || $saleman_id_fk ==''){
				$saleman_id_fk = 0;
			}

			// calculate actual discount all

			$actual_discount_all = 0;

			for ($i=0;$i<count($product_id_fk);$i++){

				$actual_discount_all = $actual_discount_all + $discount[$i];

			}

			$discount_type 	= 1;
			
			$invoice_total_no_disc = $grand_total + $actual_discount_all;

			$db->autocommit(FALSE); // to rollback insertion if update fail
			$sql ="INSERT INTO invoice (invoice_number,
										invoice_date, 		
										invoice_status,
										invoice_usd_rate, 
										invoice_currency, 
										invoice_total_price_actual, 
										invoice_total_net_balance, 
										discount, 
										discount_type, 
										actual_discount, 
										invoice_note,
										invoice_sequence_no, 
										author, 
										branch_id_fk, 
										stock_id_fk, 
										term_and_conditions, 
										saleman_id_fk, 
										invoice_transaction, 
										customer_id_fk, 
										created_by, 
										last_action, 
										last_action_by)
						 		VALUES( '$invoice_number',
						 				'$invoice_date',
						 				'$is_paid',
						 				'".$_SESSION['USD_TO_IQD']."',
						 				'$invoice_currency',
						 				'$invoice_total_no_disc',
						 				'$grand_total',
						 				'$actual_discount_all',
						 				'$discount_type',
						 				'$actual_discount_all',
						 				'$invoice_note',
						 				'$invoice_sequence_no',
						 				'$author',
						 				'$branch_id_fk',
						 				'$stock_id_fk',
						 				'$term_and_conditions',
						 				'$saleman_id_fk',
						 				'$invoice_transaction',
						 				'$customer_id_fk',
						 				'".$_SESSION['user_id']."',
						 				'$lastaction',
						 				'".$_SESSION['user_id']."')";
			
			$res = $db->query($sql);
			if ($res) {

				$invoice_inserted_id = $db->insert_id;

				$chek_insertion = true;



				// insert into sell items the sold products
				$lastaction = "Add New Sell Product";
				$barcode    = "";

				for ($i=0;$i<count($product_id_fk);$i++){

                    if($discount[$i]==''){
                        $discount[$i] =0;
                        
                    }

                    $discount_type_array[$i]=1;
                    $actual_discount_array[$i]=$discount[$i];
                    
                    
                    $buy_price[$i] =0;
                        
                 
                    
                   
                    
					$sql2 ="INSERT INTO sell_item (	sell_item_quantity, 
													sell_discount,
													sell_discount_type,
													actual_discount,
													item_buy_price_per_unit,
													item_sell_price_per_unit,
													tax_per_unit,
													sell_item_barcode,
													sell_date,
													sell_item_notes,
													product_id_fk,
													invoice_id_fk,
													
													created_by,
													created_at,
													updated_at,
													last_action,
													last_action_by)

 								 			VALUES( '$purchase_quantity[$i]',
	 								 				'$discount[$i]',
	 								 				'$discount_type_array[$i]',
	 								 				'$actual_discount_array[$i]',
	 								 				'$buy_price[$i]',
	 								 				'$sell_price[$i]',
	 								 				'0',
	 								 				'$barcode',
	 								 				'$invoice_date',
	 								 				'$sell_item_note[$i]',
	 								 				'$product_id_fk[$i]',
	 								 				'$invoice_inserted_id',
	 								 				'".$_SESSION['user_id']."',
	 								 				'$reg_date',
	 								 				'$reg_date',
	 								 				'$lastaction',
	 								 				'".$_SESSION['user_id']."')";
					$res2 = $db->query($sql2);

					if ($res2) {

						$chek_insertion = true;


					}else{
								// echo $db->error;

						$chek_insertion = false;
						$db->rollback();
						$op['is_success'] = false;
						$op['data'] 	  = "هناك خطأ : لم يتم اضافة المبيعات الرجاء اعادة تحميل الصفحة والمحاولة من جديد";
						echo json_encode($op);
						exit;
					}

				} // for i


			

				


				if($chek_insertion == true ){

					$db->commit();
					$op['is_success'] = true;
					$op['data'] 	  = "تمت عملية الاضافة بنجاح";
					$op['id'] = $invoice_inserted_id;

					
					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 create

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