<?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';
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['branch_id']) && $_GET['branch_id'] !=0){ 
				// in term
				$cond1 = $cond1 . ' AND Inv.branch_id_fk ='.$_GET['branch_id'];
			}
			

			// $limits = "LIMIT 0";
			$limits = "";
			if( $_GET['search']['value'] !=''){
				$limits =  "";
			}

			for($i=0; $i<10;$i++){

				if( $_GET['columns'][$i]['search']['value'] !=''){
					$limits =  "";
				}
			}


			$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,
						
						discount,
						discount_type,
						author,
						Inv.actual_discount,
						invoice_sequence_no,
						
						customer_id_fk,
						
						invoice_transaction,
						
											
						customer_full_name AS name,
						customer_company,
						customer_phone AS phone,
						full_name,
						branch_name,
						saleman_full_name

					 FROM  invoice Inv
					 		LEFT JOIN customer Cus 	ON (Inv.customer_id_fk 	= Cus.customer_id) 
					 		LEFT JOIN staff Stf 	ON (Inv.created_by 		= Stf.staff_id) 
					 		LEFT JOIN saleman SM 	ON (Inv.saleman_id_fk 	= SM.saleman_id) 
					 		LEFT JOIN branch B 		ON (Inv.branch_id_fk 	= B.branch_id) 
					 
				WHERE
					(Inv.is_deleted = 0 AND Inv.is_returned = 1  '.$cond.' '.$cond1.') '.$limits.'
				)
			) 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' => 'name', 'dt' => 3 ),
		    	// array( 'db' => 'author', 'dt' => 4 ),
		    	array( 'db' => 'invoice_total_net_balance', 'dt' => 4 ),
			  	array( 'db' => 'invoice_note', 'dt' => 5 ),
				array( 'db' => 'invoice_status', 'dt' => 6 ),
				array( 'db' => 'branch_name', 'dt' => 7 ),
				array( 'db' => 'saleman_full_name', 'dt' => 8 ),
				array( 'db' => 'full_name', 'dt' => 9 ),

				array( 'db' => 'customer_id_fk', 'dt' => 10 ),
				array( 'db' => 'invoice_transaction', 'dt' => 11 ),

			);
			// 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, 
										I.discount,
										I.discount_type, 
										I.actual_discount, 
										invoice_note,
										author,
										invoice_sequence_no,
										
										invoice_barcode,
										customer_id_fk, 
										sell_item_id,
										sell_item_quantity,
										sell_discount,
										sell_discount_type,
										SI.actual_discount as sell_actual_discount, 
										item_buy_price_per_unit, 
										item_sell_price_per_unit, 
										tax_per_unit, 
										sell_date,
										sell_item_barcode,
										sell_item_notes,
										product_id_fk, 
										customer_full_name,
										customer_email,
										customer_phone,
										customer_address,									  
									  	customer_company,


									  	Pr1.product_name,
									  	
									  													
									  	(0 ) AS row_price,

									  	branch_name,
									  	saleman_full_name,
									  	I.branch_id_fk,
									  	saleman_id_fk,
									  	SI.invoice_id_fk,

									


									

									(SELECT sum(paid_amount) FROM payments_details PY WHERE PY.invoice_id_fk = I.invoice_id AND  PY.is_deleted = 0 AND PY.payment_transaction ='Payment') AS total_paid

								  

										FROM invoice I
										LEFT JOIN sell_item SI ON (SI.invoice_id_fk = I.invoice_id AND SI.is_deleted=0)
										LEFT JOIN branch B ON (I.branch_id_fk = B.branch_id )
										LEFT JOIN product Pr1 ON (SI.product_id_fk = Pr1.product_id )
					 	 				LEFT JOIN customer C ON (C.customer_id = I.customer_id_fk)
					 	 				LEFT JOIN saleman SM ON (I.saleman_id_fk = SM.saleman_id)

										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['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['invoice_barcode']  			= $row['invoice_barcode'];
					$result['customer_id_fk']  				= $row['customer_id_fk'];
					$result['branch_id_fk']  				= $row['branch_id_fk'];
					$result['saleman_id_fk']  				= $row['saleman_id_fk'];

					$result['sell_item_id']  				= $row['sell_item_id'];
					$result['sell_item_quantity']  			= $row['sell_item_quantity'];
					$result['sell_discount']  				= $row['sell_discount'];
					$result['sell_discount_type']  			= $row['sell_discount_type'];
					$result['sell_actual_discount']  		= $row['sell_actual_discount'];
					$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['sell_item_barcode']  			= $row['sell_item_barcode'];
					$result['sell_date']  					= $row['sell_date'];
					$result['sell_item_notes']  			= $row['sell_item_notes'];
					$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['product_id_fk']  				= $row['product_id_fk'];
					$result['invoice_id_fk']  				= $row['invoice_id_fk'];

					$result['product_name']  				= $row['product_name'];
					$result['branch_name']  				= $row['branch_name'];
					$result['saleman_full_name']  			= $row['saleman_full_name'];
					
					
					$result['total_paid']  					= $row['total_paid'];
					$result['total_remained']  				= $row['invoice_total_net_balance'] - $row['total_paid'];
					$result['row_price']  					= $row['sell_item_quantity'] * $row['item_sell_price_per_unit'] - $row['sell_actual_discount'];
					
					array_push($op['invoice'],$result);
			
				}		
				echo json_encode($op);
				exit;
			}else{
				echo $db->error;
				exit;
			}
		}


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

			$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_note, 
															  sell_item_quantity, 
															  I.customer_id_fk, 
															  sell_discount, 
															  sell_discount_type, 
															  actual_discount,
															  item_sell_price_per_meter,
															  decorate_price_per_meter, 
															  is_paid,
															  sell_item_notes, 
															  S.item_type_id_fk, 
															  other_id_fk, 
															  S.product_id_fk, 
															  sell_item_id,
															  customer_full_name,
															  customer_email,
															  customer_phone,
															  customer_address,
															  invoice_payment_id,
																pay_usd_rate,
																pay_currency,
																paid_amount,
																paid_date,
																next_payment_date,
																next_payment_amount,
																payment_note, 
																CONCAT(COALESCE(erbil_code,''), COALESCE(other_name,'')) AS product_name,
																(SELECT sum(paid_amount) FROM invoice_payment IP WHERE I.invoice_id = IP.invoice_id_fk AND IP.is_returned=0 AND IP.is_deleted =0) AS total_paid,

																((SELECT sum(sell_item_detail_height * item_sell_price_per_meter) FROM sell_item_detail SID, sell_item S 
          			
															        WHERE I.invoice_id = SID.invoice_id_fk AND S.invoice_id_fk = I.invoice_id AND S.is_deleted=0 AND  SID.part_status =0 AND SID.is_deleted=0 AND S.sell_item_id = SID.sell_item_id_fk AND S.other_id_fk =0)
															    +
															    COALESCE((SELECT sum(sell_item_quantity * item_sell_price_per_meter) FROM sell_item S  
																					WHERE I.invoice_id = S.invoice_id_fk AND  S.other_id_fk >0 AND S.is_deleted=0),0)
																	-
															 	(SELECT sum(actual_discount) from sell_item where sell_item.invoice_id_fk = I.invoice_id AND sell_item.is_deleted=0 )
																) as total_invoice_price,

																(SELECT sum(actual_discount) FROM sell_item SI WHERE I.invoice_id = SI.invoice_id_fk AND SI.is_deleted =0) AS total_discount,
																(SELECT sum(sell_item_detail_height) FROM sell_item_detail SID WHERE SID.invoice_id_fk = '$id' AND SID.sell_item_id_fk=S.sell_item_id AND part_status=0 AND SID.is_deleted = 0) AS total_length

												FROM invoice I
													LEFT JOIN invoice_payment IPP ON (I.invoice_id = IPP.invoice_id_fk AND IPP.is_returned=0 AND IPP.is_deleted =0)
											 		LEFT JOIN sell_item S ON (S.sell_item_id = IPP.sell_item_id_fk AND S.is_deleted=0)
											 		LEFT JOIN others O ON (S.other_id_fk = O.other_id AND S.other_id_fk >0)
													LEFT JOIN product P ON (S.product_id_fk = P.product_id )
													LEFT JOIN customer C ON (I.customer_id_fk = C.customer_id )
											 	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['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['total_invoice_price'];
					$result['invoice_note']  							= $row['invoice_note'];
					$result['sell_item_quantity']  				= $row['sell_item_quantity'];
					$result['sell_discount']  						= $row['sell_discount'];
					$result['sell_discount_type']  				= $row['sell_discount_type'];
					$result['actual_discount']  					= $row['actual_discount'];
					$result['item_sell_price_per_meter']  = $row['item_sell_price_per_meter'];
					$result['decorate_price_per_meter']  	= $row['decorate_price_per_meter'];
					$result['is_paid']  									= $row['is_paid'];
					$result['sell_item_notes']  					= $row['sell_item_notes'];
					$result['product_name']  							= $row['product_name'];
					$result['customer_full_name']  				= $row['customer_full_name'];
					$result['customer_phone']  						= $row['customer_phone'];
					$result['customer_address']  					= $row['customer_address'];
					$result['total_paid']  								= $row['total_paid'];
					$result['product_id_fk']  						= $row['product_id_fk'];
					$result['other_id_fk']  							= $row['other_id_fk'];
					$result['sell_item_id']  							= $row['sell_item_id'];
					$result['total_discount']  						= $row['total_discount'];
					$result['total_remained']  						= $row['total_invoice_price'] - $row['total_paid'];
					$result['total_length']  							= $row['total_length'];
					$result['invoice_payment_id']  				= $row['invoice_payment_id'];
					$result['pay_usd_rate']  							= $row['pay_usd_rate'];
					$result['pay_currency']  							= $row['pay_currency'];
					$result['paid_amount']  							= $row['paid_amount'];
					$result['paid_date']  								= date('Y-m-d',strtotime($row['paid_date']));
					$result['next_payment_date']  				= $row['next_payment_date'];
					$result['next_payment_amount']  			= $row['next_payment_amount'];
					$result['payment_note']  							= $row['payment_note'];
					
						array_push($op['invoice'],$result);
						
				}		
				echo json_encode($op);
				exit;
			}else{
				echo 0;
				exit;
			}
		}


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

			$id 		= $_POST['id'];
			// $cust_id 	= $_POST['cust_id'];

			$res = $db->query("SELECT 
									  customer_full_name,
									  customer_email,
									  customer_phone,
									  customer_address, 
									  sum(invoice_total_net_balance) AS total_invoices_price,

									  

									  (SELECT sum(paid_amount) FROM payments IP WHERE IP.customer_id_fk = I.customer_id_fk AND IP.is_returned=0 AND IP.is_deleted =0 AND IP.payment_transaction ='Payment') AS total_paid,

									  (SELECT sum(paid_amount) FROM payments IP WHERE IP.customer_id_fk = I.customer_id_fk AND IP.is_returned=0 AND IP.is_deleted =0 AND IP.payment_transaction ='Discount') AS total_payment_discount,

									  (SELECT sum(paid_amount) FROM payments IP WHERE IP.customer_id_fk = I.customer_id_fk AND IP.is_returned=0 AND IP.is_deleted =0 AND IP.payment_transaction ='Return') AS total_payment_return

									FROM invoice I
								 		
										LEFT JOIN customer C ON (I.customer_id_fk = C.customer_id )
								 	WHERE I.customer_id_fk='$id' AND I.invoice_status <3  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['customer_full_name'] 		= $row['customer_full_name'];
					$result['customer_email']  			= $row['customer_email'];
					$result['customer_phone']  			= $row['customer_phone'];
					$result['customer_address']  		= $row['customer_address'];
					$result['total_invoices_price']  	= $row['total_invoices_price'];
					$result['total_paid']  				= $row['total_paid'] - $row['total_payment_return'];
					$result['total_payment_discount']  	= $row['total_payment_discount'];
					$result['total_payment_return']  	= $row['total_payment_return'];
					
					$result['total_remained']  			= $row['total_invoices_price'] - $row['total_paid'] - $row['total_payment_discount'] + $row['total_payment_return'];
					
					array_push($op['invoice'],$result);
						
				}		
				echo json_encode($op);
				exit;
			}else{
				echo 0;
				exit;
			}
		}

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

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

			$res = $db->query("SELECT 
									  customer_full_name,
									  customer_email,
									  customer_phone,
									  customer_address, 
									  invoice_total_net_balance,

									  

									  (SELECT sum(paid_amount) FROM payments_details IP WHERE IP.customer_id_fk = I.customer_id_fk AND IP.is_returned=0 AND IP.is_deleted =0 AND IP.payment_transaction ='Payment' AND IP.invoice_id_fk= I.invoice_id) AS total_paid

									  

									FROM invoice I
								 		
										LEFT JOIN customer C ON (I.customer_id_fk = C.customer_id )
								 	WHERE I.customer_id_fk='$cust_id' AND I.invoice_status <3 AND I.invoice_id='$id'  AND I.is_deleted = 0");

		
			if ($res) {
				
				$op  = array('invoice'=>array(),'details'=>array());
				while ($row = $res->fetch_assoc()) {
				   
					
					$result['customer_full_name'] 			= $row['customer_full_name'];
					$result['customer_email']  				= $row['customer_email'];
					$result['customer_phone']  				= $row['customer_phone'];
					$result['customer_address']  			= $row['customer_address'];
					$result['invoice_total_net_balance']  	= $row['invoice_total_net_balance'];
					$result['total_paid']  					= $row['total_paid'];
					$result['total_remained']  				= $row['invoice_total_net_balance'] - $row['total_paid'];
					
					array_push($op['invoice'],$result);
						
				}		
				echo json_encode($op);
				exit;
			}else{
				echo 0;
				exit;
			}
		}



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

			$customer_id_fk    	= $_POST['cust_id'];
			$invoice_id_fk 		= $_POST['inv_id'];
			$total_paid 		= $_POST['row_payments'];
			$notes 				= $_POST['notes'];
			$paper_no 			= $_POST['paper_no'];
			$branch_id_fk 		= $_POST['branch_id_fk'];

			$lastaction 		= "Add New Payment";
			$invoice_currency	= 1; //USD

			if(!isset($_POST['paid_date'])){
				$reg_date = date("Y-m-d H:i:s", time());
			}else{
				$reg_date = $_POST['paid_date'];
			}
			
			$dd 	  = date("y", time());
			
			$next_payment_date 		='';
			$next_payment_amount 	= '';



			$successfully = false;

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

				if($total_paid[$i] > 0){

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

						// get branch

						$resI = $db->query("SELECT branch_id_fk FROM invoice WHERE invoice_id=$invoice_id_fk");
						$rowI = $resI->fetch_assoc();
						$branch_id_fk 	= $rowI['branch_id_fk'];


						$resP = $db->query("SELECT pay_sequence_no FROM payments ORDER BY payment_id DESC LIMIT 1");
						$rowP = $resP->fetch_assoc();
						$pay_sequence_no 	= $rowP['pay_sequence_no'] +1;

				
						$payment_number = "PAY".$dd.($pay_sequence_no);
						$payment_transaction = 'Payment';
						$payment_note = $notes[$i];


					
						
						if($payment_note ==""){
							$payment_note = "Add Payment for Invoice ID: ".$invoice_id_fk;
						}


						$sql_p ="INSERT INTO payments(payment_number,
													  paper_number,
				 									  pay_usd_rate,
				 									  pay_currency,
				 									  paid_amount,
				 									  paid_date,
				 									  payment_note,
				 									  payment_transaction,
				 									  pay_sequence_no,
				 									  customer_id_fk,
				 									  invoice_id_fk,
				 									  branch_id_fk,
				 									  created_by,
				 									  last_action,
				 									  last_action_by)
				 							   VALUES('$payment_number',
				 							   		  '$paper_no[$i]',
				 							   		  '".$_SESSION['USD_TO_IQD']."',
				 							    	  '$invoice_currency',
				 							    	  '$total_paid[$i]',
				 							    	  '$reg_date',
				 							    	  '$payment_note',
				 							    	  '$payment_transaction',
				 							    	  '$pay_sequence_no',
				 							    	  '$customer_id_fk',
				 							    	  '$invoice_id_fk',
				 							    	  '$branch_id_fk',
				 							    	  '".$_SESSION['user_id']."',
				 							    	  '$lastaction',
				 							    	  '".$_SESSION['user_id']."')";
						


						$res_P = $db->query($sql_p);

						if ($res_P){
							$last_inserted_payment_id = $db->insert_id;


				 			$sql_pd ="INSERT INTO payments_details
				 									 (
				 									  paid_amount,
				 									  paid_date,
				 									  payment_note,
				 									  payment_transaction,
				 									  payment_id_fk,
				 									  invoice_id_fk,
				 									  customer_id_fk,
				 									  created_by,
				 									  last_action,
				 									  last_action_by)
				 							   VALUES(
				 							    	  '$total_paid[$i]',
				 							    	  '$reg_date',
				 							    	  '$payment_note',
				 							    	  '$payment_transaction',
				 							    	  '$last_inserted_payment_id',
				 							    	  '$invoice_id_fk',
				 							    	  '$customer_id_fk',
				 							    	  '".$_SESSION['user_id']."',
				 							    	  '$lastaction',
				 							    	  '".$_SESSION['user_id']."')";
							
							$res_PD = $db->query($sql_pd);
							
							if($res_PD){

								$res = $db->query("SELECT * FROM safe  ORDER BY safe_id DESC LIMIT 1");
								$row = $res->fetch_assoc();
								$safe_sequence_no 	= $row['safe_sequence_no'] +1;

								$dd = date("y", time());
								$safe_number = "PY".$dd.($safe_sequence_no);

								// Upadte purchase_sequence_no

								$safe_transaction = "Payment";
								$transaction_type = "1";  // 1: add ; 2:subtract;
							
								

								$reg_date 	= date("Y-m-d H:i:s", time());
								$lastaction = "Add New Payment";
								$safe_note 	= "Add New Payment  ID : " . $last_inserted_payment_id;

								$sql1 ="INSERT INTO safe 
												   (safe_number,
													safe_amount,
													date_of_add,
													safe_transaction,
													transaction_type,
													capital_usd_rate,
													capital_currency,
													table_name,
													id_fk,
													safe_sequence_no,
													safe_note,
													staff_id_fk,
													branch_id_fk,
													created_by,
													created_at,
												    updated_at,
													last_action,
													last_action_by)
											VALUES( '$safe_number',
													'$total_paid[$i]',
													'$reg_date',
													'$safe_transaction',
													'$transaction_type',
													'".$_SESSION['USD_TO_IQD']."',
													'$invoice_currency',
													'payments',
													'$last_inserted_payment_id',
													'$safe_sequence_no',
													'$safe_note',
													'".$_SESSION['user_id']."',
													'$branch_id_fk',
													'".$_SESSION['user_id']."',
													'$reg_date',
													'$reg_date',
													'$lastaction',
													'".$_SESSION['user_id']."')";

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

								if(!$res1){

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


					
						$successfully = true;
						
				} // if total_paid >0

			}  // for i

			if($successfully == false){
				$db->rollback();
				$op['is_success'] = false;
				$op['data'] 	  = "الرجاء قم بادخال دفعة واحدة على الاقل";
				echo json_encode($op);
				exit;
			}else{

				// now we should change Invoice status 

				$res = $db->query("SELECT 
									  
									  invoice_total_net_balance,

									  

									  (SELECT sum(paid_amount) FROM payments_details IP WHERE IP.customer_id_fk = I.customer_id_fk AND IP.is_returned=0 AND IP.is_deleted =0 AND IP.payment_transaction ='Payment' AND IP.invoice_id_fk= I.invoice_id) AS total_paid

									  

									FROM invoice I
								 		
								 	WHERE I.customer_id_fk='$customer_id_fk' AND I.invoice_id='$invoice_id_fk'  AND I.is_deleted = 0");

		
				if ($res) {
					
					while ($row = $res->fetch_assoc()) {
					   
						
						$result['invoice_total_net_balance']  	= $row['invoice_total_net_balance'];
						$result['total_paid']  					= $row['total_paid'];
						$result['total_remained']  				= $row['invoice_total_net_balance'] - $row['total_paid'];
							
					}		
					
				}

				if($result['total_remained']  <= 0){
					$invoice_status = 2;
				}else if($result['total_remained'] > 0 && $result['total_paid'] > 0){
					$invoice_status = 1;
				}

				if($invoice_status > 0){
					$sql ="UPDATE invoice SET   
									
								invoice_status 				= '$invoice_status',
								updated_at 					= '$reg_date', 
								last_action 				= '$lastaction', 
								last_action_by 				= '".$_SESSION['user_id']."'
						 		WHERE invoice_id = '$invoice_id_fk'";
			
					$resI = $db->query($sql);

					if(!$resI){
						$db->rollback();
						$op['is_success'] = false;
						$op['data'] 	  = "الرجاء قم بادخال دفعة واحدة على الاقل";
						echo json_encode($op);
						exit;
					}
				}


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


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

			$customer_id_fk    	= $_POST['cust_id'];
			$total_paid 		= $_POST['row_payments'];
			$notes 				= $_POST['notes'];
			$paper_no 			= $_POST['paper_number'];
			$branch_id_fk 		= $_POST['branch_id_fk'];

			$lastaction 		= "Add New Payment";
			$invoice_currency	= 1; //USD

			if(!isset($_POST['paid_date'])){
				$reg_date = date("Y-m-d H:i:s", time());
			}else{
				$reg_date = $_POST['paid_date'];
			}
			
			$dd 	  = date("y", time());
			
			$next_payment_date 		='';
			$next_payment_amount 	= '';



			$successfully = false;

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

				if($total_paid[$i] > 0){

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

						
						$resP = $db->query("SELECT pay_sequence_no FROM payments ORDER BY payment_id DESC LIMIT 1");
						$rowP = $resP->fetch_assoc();
						$pay_sequence_no 	= $rowP['pay_sequence_no'] +1;

				
						$payment_number 		= "PAY".$dd.($pay_sequence_no);
						$payment_transaction 	= 'Payment';
						$payment_note 			= $notes[$i];

						$current_payment 		= $total_paid[$i];
						$current_remain 		= $total_paid[$i];

						$is_finished 			= false;

						

						$sql_p ="INSERT INTO payments(payment_number,
													  paper_number,
				 									  pay_usd_rate,
				 									  pay_currency,
				 									  paid_amount,
				 									  paid_date,
				 									  payment_note,
				 									  payment_transaction,
				 									  pay_sequence_no,
				 									  customer_id_fk,
				 									  invoice_id_fk,
				 									  branch_id_fk,
				 									  created_by,
				 									  last_action,
				 									  last_action_by)
				 							   VALUES('$payment_number',
				 							   		  '$paper_no',
				 							   		  '".$_SESSION['USD_TO_IQD']."',
				 							    	  '$invoice_currency',
				 							    	  '$total_paid[$i]',
				 							    	  '$reg_date',
				 							    	  '$payment_note',
				 							    	  '$payment_transaction',
				 							    	  '$pay_sequence_no',
				 							    	  '$customer_id_fk',
				 							    	  '0',
				 							    	  '$branch_id_fk',
				 							    	  '".$_SESSION['user_id']."',
				 							    	  '$lastaction',
				 							    	  '".$_SESSION['user_id']."')";
						


						$res_P = $db->query($sql_p);

						if ($res_P){
							$last_inserted_payment_id = $db->insert_id;




							$res = $db->query("SELECT * FROM safe  ORDER BY safe_id DESC LIMIT 1");
							$row = $res->fetch_assoc();
							$safe_sequence_no 	= $row['safe_sequence_no'] +1;

							$dd = date("y", time());
							$safe_number = "PY".$dd.($safe_sequence_no);

							// Upadte purchase_sequence_no

							$safe_transaction = "Payment";
							$transaction_type = "1";  // 1: add ; 2:subtract;
						
							

							$reg_date 	= date("Y-m-d H:i:s", time());
							$lastaction = "Add New Payment";
							$safe_note 	= "Add New Payment ID : " . $last_inserted_payment_id;

							$sql1 ="INSERT INTO safe 
											   (safe_number,
												safe_amount,
												date_of_add,
												safe_transaction,
												transaction_type,
												capital_usd_rate,
												capital_currency,
												table_name,
												id_fk,
												safe_sequence_no,
												safe_note,
												staff_id_fk,
												branch_id_fk,
												created_by,
												created_at,
											    updated_at,
												last_action,
												last_action_by)
										VALUES( '$safe_number',
												'$total_paid[$i]',
												'$reg_date',
												'$safe_transaction',
												'$transaction_type',
												'".$_SESSION['USD_TO_IQD']."',
												'$invoice_currency',
												'payments',
												'$last_inserted_payment_id',
												'$safe_sequence_no',
												'$safe_note',
												'".$_SESSION['user_id']."',
												'$branch_id_fk',
												'".$_SESSION['user_id']."',
												'$reg_date',
												'$reg_date',
												'$lastaction',
												'".$_SESSION['user_id']."')";

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

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


							// now we should divide this payment to un-paid invoices


							$res = $db->query("SELECT 
									  
									 		 invoice_total_net_balance,

									  		 invoice_id,
									  		 invoice_status,

									  		(SELECT sum(paid_amount) FROM payments_details IP WHERE IP.customer_id_fk = I.customer_id_fk AND IP.is_returned=0 AND IP.is_deleted =0 AND IP.payment_transaction ='Payment' AND IP.invoice_id_fk= I.invoice_id) AS total_paid

									FROM invoice I
								 		
								 	WHERE I.customer_id_fk='$customer_id_fk' AND I.invoice_status < 2  AND I.is_deleted = 0");

					
							if ($res) {
								
								while ($row = $res->fetch_assoc()) {
								   
									
									$result['invoice_total_net_balance']  	= $row['invoice_total_net_balance'];
									$result['total_paid']  					= $row['total_paid'];
									$invoice_id  							= $row['invoice_id'];
									$result['invoice_status']  				= $row['invoice_status'];
									$result['total_remained']  				= $row['invoice_total_net_balance'] - $row['total_paid'];


									if($result['total_remained']  > $current_payment){
										$is_finished 		= true;
										$invoice_status 	= 1;
										$inserted_val 		= $current_payment;
										$current_payment 	= 0;

									}else if($result['total_remained'] == $current_payment){
										$is_finished 		= true;
										$invoice_status 	= 2;
										$inserted_val 		= $current_payment;
										$current_payment 	= 0;

									}else{
										$invoice_status 	= 2;
										$inserted_val 		= $result['total_remained'];
										$current_payment 	= $current_payment - $result['total_remained'];

									}


									$sql_pd ="INSERT INTO payments_details
				 									 (
				 									  paid_amount,
				 									  paid_date,
				 									  payment_note,
				 									  payment_transaction,
				 									  payment_id_fk,
				 									  invoice_id_fk,
				 									  customer_id_fk,
				 									  created_by,
				 									  last_action,
				 									  last_action_by)
				 							   VALUES(
				 							    	  '$inserted_val',
				 							    	  '$reg_date',
				 							    	  '$payment_note',
				 							    	  '$payment_transaction',
				 							    	  '$last_inserted_payment_id',
				 							    	  '$invoice_id',
				 							    	  '$customer_id_fk',
				 							    	  '".$_SESSION['user_id']."',
				 							    	  '$lastaction',
				 							    	  '".$_SESSION['user_id']."')";
							
									$res_PD = $db->query($sql_pd);
									
									if(!$res_PD){
										$db->rollback();
										$op['is_success'] = false;
										$op['data'] 	  = "هناك خطأ : لم يتم اضافة الدفوعات الرجاء اعادة تحميل الصفحة والمحاولة من جديد";
										echo json_encode($op);
										exit;
									}


									// now we should change Invoice status 

									if($invoice_status > 0){
										$sql ="UPDATE invoice SET   
														
													invoice_status 				= '$invoice_status',
													updated_at 					= '$reg_date', 
													last_action 				= '$lastaction', 
													last_action_by 				= '".$_SESSION['user_id']."'
											 		WHERE invoice_id = '$invoice_id'";
								
										$resI = $db->query($sql);

										if(!$resI){
											$db->rollback();
											$op['is_success'] = false;
											$op['data'] 	  = "الرجاء قم بادخال دفعة واحدة على الاقل";
											echo json_encode($op);
											exit;
										}
									}




									if($is_finished == true){
										break;
									}


										
								} // while		
								
							}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;
						}


					
						$successfully = true;
						
				} // if total_paid >0

			}  // for i

			if($successfully == false){
				$db->rollback();
				$op['is_success'] = false;
				$op['data'] 	  = "الرجاء قم بادخال دفعة واحدة على الاقل";
				echo json_encode($op);
				exit;
			}else{


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


		

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

			$id 										= $_POST['inv_id'];
			$total_paid 						= $_POST['paid'];
			$sell_ids 							= $_POST['sell_ids'];
			$notes 									= $_POST['notes'];
			$payment_id 						= $_POST['payment_id'];


			$lastaction 						= "Update Payment";
			$invoice_currency				= 1; //USD
			$reg_date 							= date("Y-m-d h:i:s", time());
			
			$next_payment_date 			=	'';
			$next_payment_amount 		= '';

			$res = $db->query("SELECT customer_id_fk FROM invoice WHERE invoice_id = $id");
			if ($res) {
				$row = $res->fetch_assoc();
				$customer_id_fk = $row['customer_id_fk'];
				
			}

			if($total_paid > 0){

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

					$sql ="UPDATE invoice_payment SET paid_amount = '$total_paid', payment_note='$notes', customer_id_fk='$customer_id_fk',
					 created_by='".$_SESSION['user_id']."', last_action='$lastaction', last_action_by='".$_SESSION['user_id']."'  WHERE invoice_payment_id = '$payment_id' ";
					
					$res = $db->query($sql);

					if ($res){

										// we should update sell_item pay status 

										$res1 = $db->query("SELECT 	sell_item_id,
																								invoice_total_price_actual, 
															  
																								sell_item_quantity, 
															 
																								actual_discount,
	 																							item_sell_price_per_meter,
	 																							decorate_price_per_meter, 
																								is_paid,
																								(SELECT sum(paid_amount) FROM invoice_payment IP WHERE I.invoice_id = IP.invoice_id_fk AND IP.is_returned=0 AND IP.is_deleted =0) AS total_paid,
	
																								(SELECT sum(sell_item_detail_height) FROM sell_item_detail SID WHERE SID.invoice_id_fk = '$id' AND SID.sell_item_id_fk=S.sell_item_id AND part_status=0 AND SID.is_deleted = 0) AS total_length,

        																				(SELECT sum(paid_amount) FROM invoice_payment IP1 WHERE 		I.invoice_id = IP1.invoice_id_fk AND IP1.sell_item_id_fk=S.sell_item_id AND IP1.is_returned=0 AND IP1.is_deleted =0 ) AS total_paid_per_product,

        																				((SELECT sum(sell_item_detail_height * item_sell_price_per_meter) FROM sell_item_detail SID, sell_item S 
          			
																					        WHERE I.invoice_id = SID.invoice_id_fk AND S.invoice_id_fk = I.invoice_id AND S.is_deleted=0 AND  SID.part_status =0 AND SID.is_deleted=0 AND S.sell_item_id = SID.sell_item_id_fk AND S.other_id_fk =0)
																					    	+
																					    	COALESCE((SELECT sum(sell_item_quantity * item_sell_price_per_meter) FROM sell_item S  
																											WHERE I.invoice_id = S.invoice_id_fk AND  S.other_id_fk >0 AND S.is_deleted=0),0)
																								-
																					 			(SELECT sum(actual_discount) from sell_item where sell_item.invoice_id_fk = I.invoice_id AND sell_item.is_deleted=0 )
																								) as total_invoice_price

																				FROM invoice I
											 									LEFT JOIN sell_item S ON (S.invoice_id_fk = I.invoice_id AND S.is_deleted=0 AND S.sell_item_id='$sell_ids')
											 		
											 									WHERE I.invoice_id='$id'  AND I.is_deleted = 0");
										$row1 = $res1->fetch_assoc();

										$total_length 									= $row1['total_length'];
										$total_paid_per_product 				= $row1['total_paid_per_product'];
										$total_payments									= $row1['total_paid'];
										$net_balance 										= $row1['total_invoice_price'];
										$sell_item_quantity 						= $row1['sell_item_quantity'];
										$actual_discount 								= $row1['actual_discount'];
										$item_sell_price_per_meter 			= $row1['item_sell_price_per_meter'];
										$decorate_price_per_meter 			= $row1['decorate_price_per_meter'];
										$is_paid 												= $row1['is_paid'];
										
										// compute total product price

										$total_product_price = 0;

										if($total_length > 0 && $total_length !=NULL){
											$total_product_price = ($total_length * $item_sell_price_per_meter) -  $actual_discount;

										}else{
											$total_product_price = ($sell_item_quantity * $item_sell_price_per_meter) -  $actual_discount;

										}

										if($total_paid_per_product > $total_product_price ){
													$db->rollback();
													$op['is_success'] = false;
													$op['data'] 	  = "المبلغ المدفوع لهذه المادو اصبح اكبر من سعر المادة الكلي الرجاء التاكد من القيمة واعادة الادخال";
													echo json_encode($op);
													exit;

										}elseif($total_paid_per_product == $total_product_price  ){
											$is_paid_sell = 2;

										}elseif($total_paid_per_product < $total_product_price AND $total_paid_per_product >0 ){
											$is_paid_sell = 1;

										}else{
											$is_paid_sell = 0;

										}

										$update_qry = "UPDATE sell_item SET  is_paid='$is_paid_sell',last_action='$lastaction',
											last_action_by='".$_SESSION['user_id']."' WHERE invoice_id_fk = $id AND sell_item_id='$sell_ids' ";

										$res2 = $db->query($update_qry);
										if ($res2) {

												// update invoice status

												$lastaction = "Update Invoice status after Adding payments";
												if($total_payments > $net_balance ){
													$db->rollback();
													$op['is_success'] = false;
													$op['data'] 	  = "المبلغ الكلي المدفوع لهذا الوصل اصبح اكبر من السعر  الكلي للوصل الرجاء التاكد من القيمة واعادة الادخال";
													echo json_encode($op);
													exit;

												}elseif($total_payments == $net_balance ){
													$is_paid = 2; // complete paid
													$pay_term = 0;
													$update_qry1 = "UPDATE invoice SET  invoice_status='$is_paid',pay_terms='$pay_term',last_action='$lastaction',
													last_action_by='".$_SESSION['user_id']."' WHERE invoice_id = $id";

												}elseif($total_payments < $net_balance AND $total_payments >0 ){
													$is_paid = 1; // partially paid
													$update_qry1 = "UPDATE invoice SET  invoice_status='$is_paid',last_action='$lastaction',
													last_action_by='".$_SESSION['user_id']."' WHERE invoice_id = $id";
												}

												$res3 = $db->query($update_qry1);
												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{
						// $op['data'] 	  =$db->error;

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

			}else{

				$op['is_success'] = false;
				$op['data'] 	  = "الرجاء قم بادخال قيمة مبلغ اكبر من 0";
				echo json_encode($op);
				exit;
			}
		}


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

			$id 										= $_POST['inv_id'];
			$total_paid 						= $_POST['paid'];
			$sell_ids 							= $_POST['sell_ids'];
			$notes 									= $_POST['notes'];
			$payment_id 						= $_POST['payment_id'];


			$lastaction 						= "Delete Payment";
			$invoice_currency				= 1; //USD
			$reg_date 							= date("Y-m-d h:i:s", time());
			
			$next_payment_date 			=	'';
			$next_payment_amount 		= '';



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

					$sql ="UPDATE invoice_payment SET is_deleted = '1', deleted_at = '$reg_date',
					 created_by='".$_SESSION['user_id']."', last_action='$lastaction', last_action_by='".$_SESSION['user_id']."'  WHERE invoice_payment_id = '$payment_id' ";
					
					$res = $db->query($sql);

					if ($res){

										// we should update sell_item pay status 

										$res1 = $db->query("SELECT 	sell_item_id,
																								invoice_total_price_actual, 
															  
																								sell_item_quantity, 
															 
																								actual_discount,
	 																							item_sell_price_per_meter,
	 																							decorate_price_per_meter, 
																								is_paid,
																								(SELECT sum(paid_amount) FROM invoice_payment IP WHERE I.invoice_id = IP.invoice_id_fk AND IP.is_returned=0 AND IP.is_deleted =0) AS total_paid,
	
																								(SELECT sum(sell_item_detail_height) FROM sell_item_detail SID WHERE SID.invoice_id_fk = '$id' AND SID.sell_item_id_fk=S.sell_item_id AND part_status=0 AND SID.is_deleted = 0) AS total_length,

        																				(SELECT sum(paid_amount) FROM invoice_payment IP1 WHERE 		I.invoice_id = IP1.invoice_id_fk AND IP1.sell_item_id_fk=S.sell_item_id AND IP1.is_returned=0 AND IP1.is_deleted =0 ) AS total_paid_per_product,

        																				((SELECT sum(sell_item_detail_height * item_sell_price_per_meter) FROM sell_item_detail SID, sell_item S 
          			
																					        WHERE I.invoice_id = SID.invoice_id_fk AND S.invoice_id_fk = I.invoice_id AND S.is_deleted=0 AND  SID.part_status =0 AND SID.is_deleted=0 AND S.sell_item_id = SID.sell_item_id_fk AND S.other_id_fk =0)
																					    	+
																					    	COALESCE((SELECT sum(sell_item_quantity * item_sell_price_per_meter) FROM sell_item S  
																											WHERE I.invoice_id = S.invoice_id_fk AND  S.other_id_fk >0 AND S.is_deleted=0),0)
																								-
																					 			(SELECT sum(actual_discount) from sell_item where sell_item.invoice_id_fk = I.invoice_id AND sell_item.is_deleted=0 )
																								) as total_invoice_price

																				FROM invoice I
											 									LEFT JOIN sell_item S ON (S.invoice_id_fk = I.invoice_id AND S.is_deleted=0 AND S.sell_item_id='$sell_ids')
											 		
											 									WHERE I.invoice_id='$id'  AND I.is_deleted = 0");
										$row1 = $res1->fetch_assoc();

										$total_length 									= $row1['total_length'];
										$total_paid_per_product 				= $row1['total_paid_per_product'];
										$total_payments									= $row1['total_paid'];
										$net_balance 										= $row1['total_invoice_price'];
										$sell_item_quantity 						= $row1['sell_item_quantity'];
										$actual_discount 								= $row1['actual_discount'];
										$item_sell_price_per_meter 			= $row1['item_sell_price_per_meter'];
										$decorate_price_per_meter 			= $row1['decorate_price_per_meter'];
										$is_paid 												= $row1['is_paid'];
										
										// compute total product price

										$total_product_price = 0;

										if($total_length > 0 && $total_length !=NULL){
											$total_product_price = ($total_length * $item_sell_price_per_meter) -  $actual_discount;

										}else{
											$total_product_price = ($sell_item_quantity * $item_sell_price_per_meter) -  $actual_discount;

										}

										if($total_paid_per_product > $total_product_price ){
													$db->rollback();
													$op['is_success'] = false;
													$op['data'] 	  = "المبلغ المدفوع لهذه المادو اصبح اكبر من سعر المادة الكلي الرجاء التاكد من القيمة واعادة الادخال";
													echo json_encode($op);
													exit;

										}elseif($total_paid_per_product == $total_product_price  ){
											$is_paid_sell = 2;

										}elseif($total_paid_per_product < $total_product_price AND $total_paid_per_product >0 ){
											$is_paid_sell = 1;

										}else{
											$is_paid_sell = 0;

										}

										$update_qry = "UPDATE sell_item SET  is_paid='$is_paid_sell',last_action='$lastaction',
											last_action_by='".$_SESSION['user_id']."' WHERE invoice_id_fk = $id AND sell_item_id='$sell_ids' ";

										$res2 = $db->query($update_qry);
										if ($res2) {

												// update invoice status

												$lastaction = "Update Invoice status after Adding payments";
												if($total_payments > $net_balance ){
													$db->rollback();
													$op['is_success'] = false;
													$op['data'] 	  = "المبلغ الكلي المدفوع لهذا الوصل اصبح اكبر من السعر  الكلي للوصل الرجاء التاكد من القيمة واعادة الادخال";
													echo json_encode($op);
													exit;

												}elseif($total_payments == $net_balance ){
													$is_paid = 2; // complete paid
													$pay_term = 0;
													$update_qry1 = "UPDATE invoice SET  invoice_status='$is_paid',pay_terms='$pay_term',last_action='$lastaction',
													last_action_by='".$_SESSION['user_id']."' WHERE invoice_id = $id";

												}elseif($total_payments < $net_balance AND $total_payments >0 ){
													$is_paid = 1; // partially paid
													$update_qry1 = "UPDATE invoice SET  invoice_status='$is_paid',last_action='$lastaction',
													last_action_by='".$_SESSION['user_id']."' WHERE invoice_id = $id";
												}else{
													$is_paid = 0; // Not paid
													$update_qry1 = "UPDATE invoice SET  invoice_status='$is_paid',last_action='$lastaction',
													last_action_by='".$_SESSION['user_id']."' WHERE invoice_id = $id";
												}

												$res3 = $db->query($update_qry1);
												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{
						// $op['data'] 	  =$db->error;

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

			
		}


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

				
			$res = $db->query("SELECT 	invoice_id,
										invoice_number,
										invoice_date,
										customer_full_name,							  
									  	customer_company
										FROM invoice  I
										LEFT JOIN customer C ON (I.customer_id_fk = C.customer_id)
										WHERE  I.is_deleted = 0");
								
			if ($res) {
				//$row = $res->fetch_all();
				$op  = array('is_success'=>false, 'data' => array());
				while ($row = $res->fetch_assoc()) {
				   
						$result['invoice_id']  			= $row['invoice_id'];
						$result['invoice_number']  		= $row['invoice_number'];
						$result['invoice_date']  		= date('Y-m-d',strtotime($row['invoice_date'])) ;
						$result['customer_full_name']  	= $row['customer_full_name'];
						$result['customer_company']  	= $row['customer_company'];
						
						array_push($op['data'],$result);
				}

			}else{
				echo 0;
				exit;
			}

			

			$op['is_success'] = true;

			echo json_encode($op);

			exit();
					
		}


		


		if ($_POST['type']=="get_edit_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, 
										I.discount,
										I.discount_type, 
										I.actual_discount, 
										invoice_note,
										author,
										invoice_sequence_no,
										invoice_transaction,
										
										invoice_barcode,
										customer_id_fk, 
										sell_item_id,
										sell_item_quantity,
										sell_discount,
										sell_discount_type,
										SI.actual_discount as sell_actual_discount, 
										item_buy_price_per_unit, 
										item_sell_price_per_unit, 
										tax_per_unit, 
										sell_date,
										sell_item_barcode,
										sell_item_notes,
										product_id_fk, 
										customer_full_name,
										customer_email,
										customer_phone,
										customer_address,									  
									  	customer_company,


									  	Pr1.product_name,
									  	
									  													
									  	(0 ) AS row_price,

									  	branch_name,
									  	saleman_full_name,
									  	I.branch_id_fk,
									  	saleman_id_fk,
									  	SI.invoice_id_fk,
									  	I.stock_id_fk,
									  	


									

									(SELECT sum(paid_amount) FROM payments_details PY WHERE PY.invoice_id_fk = I.invoice_id AND  PY.is_deleted = 0 AND PY.payment_transaction ='Payment') AS total_paid

								  

									FROM invoice I
										LEFT JOIN sell_item SI ON (SI.invoice_id_fk = I.invoice_id AND SI.is_deleted=0)
										LEFT JOIN branch B ON (I.branch_id_fk = B.branch_id )
										LEFT JOIN product Pr1 ON (SI.product_id_fk = Pr1.product_id )
					 	 				LEFT JOIN customer C ON (C.customer_id = I.customer_id_fk)
					 	 				LEFT JOIN saleman SM ON (I.saleman_id_fk = SM.saleman_id)

										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['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['invoice_barcode']  			= $row['invoice_barcode'];
					$result['customer_id_fk']  				= $row['customer_id_fk'];
					$result['branch_id_fk']  				= $row['branch_id_fk'];
					$result['saleman_id_fk']  				= $row['saleman_id_fk'];

					$result['sell_item_id']  				= $row['sell_item_id'];
					$result['sell_item_quantity']  			= $row['sell_item_quantity'];
					$result['sell_discount']  				= $row['sell_discount'];
					$result['sell_discount_type']  			= $row['sell_discount_type'];
					$result['sell_actual_discount']  		= $row['sell_actual_discount'];
					$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['sell_item_barcode']  			= $row['sell_item_barcode'];
					$result['sell_date']  					= $row['sell_date'];
					$result['sell_item_notes']  			= $row['sell_item_notes'];
					$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['product_id_fk']  				= $row['product_id_fk'];
					$result['invoice_id_fk']  				= $row['invoice_id_fk'];

					$result['product_name']  				= $row['product_name'];
					$result['branch_name']  				= $row['branch_name'];
					$result['stock_id_fk']  				= $row['stock_id_fk'];
					$result['saleman_full_name']  			= $row['saleman_full_name'];
					
					
					$result['total_row_price']  			= $row['sell_item_quantity'] * $row['item_sell_price_per_unit'] - $row['sell_actual_discount'];


					$result['invoice_transaction']  	= $row['invoice_transaction'];
					

					array_push($op['invoice'],$result);
			
				}		
				echo json_encode($op);
				exit;
			}else{
				echo $db->error;
				exit;
			}
		}



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

			$p_id = $_POST['p_id'];

			// we should show item that can be sell 
			// 0 : avaialbe in store no damge
			// 2 : avaible in production no damge
			// 3 : in production used not finished
			// 5 : partially sold
			// 8 : avaiable in store with damage
			// 9 : used in production + partially sold
			// 11: available in production with damage
			// And we should also send the remianed length of each roll 

			$res = $db->query("SELECT purchase_item_detail_id, item_length,stock_id_fk, stock_name,item_status,
													(	COALESCE(item_length,0) - 
														(COALESCE(damage_length,0) + 
														COALESCE(used_length,0) + 	
														COALESCE((select sum(sell_item_detail_height) FROM sell_item_detail SID WHERE SID.purchase_item_detail_id_fk = PID.purchase_item_detail_id AND SID.part_status=0 AND SID.is_deleted=0),0) + 
														COALESCE((select sum(damage_length) FROM sell_item_detail SID WHERE SID.purchase_item_detail_id_fk = PID.purchase_item_detail_id AND SID.part_status=0 AND SID.is_deleted=0),0))

													) as remained_length
													FROM purchase_item_detail PID
													LEFT JOIN stock S ON (PID.stock_id_fk = S.stock_id)
													WHERE (PID.item_status= 0 || PID.item_status= 2 || PID.item_status= 3 || PID.item_status= 5 || PID.item_status= 8 || PID.item_status= 9 || PID.item_status= 11  ) 
													AND PID.is_deleted=0 AND PID.product_id_fk = $p_id");
			if ($res) {
				
				$op  = array('data'=>array(),'stocks'=>array());
				while ($row = $res->fetch_assoc()) {
				   
						$result[0]  = $row['purchase_item_detail_id'];
						$result[1]  = $row['item_length'];
						$result[2]  = $row['stock_name'];
						$result[3]  = $row['stock_id_fk'];
						$result[4]  = $row['item_status'];
						$result[5]  = $row['remained_length'];
						
						
						array_push($op['data'],$result);
				}
				$res1 = $db->query("SELECT stock_id, stock_name, stock_type,
														(SELECT COUNT(*) FROM purchase_item_detail P WHERE P.stock_id_fk = S.stock_id AND P.is_deleted=0 AND (P.item_status= 0 || P.item_status= 2 || P.item_status= 3 || P.item_status= 5 || P.item_status= 8 || P.item_status= 9 || P.item_status= 11  ) AND P.product_id_fk = $p_id) AS roll_no
														FROM stock S
														WHERE  S.is_deleted = 0");
				
				while ($row1 = $res1->fetch_assoc()) {
				   
						$result1[0]  = $row1['stock_id'];
						$result1[1]  = $row1['stock_name'];
						$result1[2]  = $row1['stock_type'];
						$result1[3]  = $row1['roll_no'];
						array_push($op['stocks'],$result1);
				}

				echo json_encode($op);

				exit;
			}else{
				echo 0;
				exit;
			}
		}


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

			$id = $_POST['obj_id'];

			$res = $db->query("SELECT * FROM invoice_images WHERE is_deleted=0 AND invoice_id_fk = '$id'");
			if ($res) {
				//$row = $res->fetch_all();
				$op  = array();
				while ($row = $res->fetch_assoc()) {
				    // do what you need.
						$result[0] = $row['image_id'];
						$result[1] = $row['image_path'];
						array_push($op,$result);
				}

				echo json_encode($op);
				exit;
			}else{
				echo 0;
				exit;
			}
		}

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

			$id 		=addslashes($_POST["obj_id"]);
			$img_path 	=addslashes($_POST["img_path"]);

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

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

			

			

	  		$res = $db->query("UPDATE invoice_images SET is_deleted = 1 ,deleted_at = '$reg_date',last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE image_id=$id");
	    	if ($res){
	        $op['is_success'] = true;
	        		if(unlink('../'.$img_path)){

    					$op['data'] 	  = "تمت عملية الحذف بنجاح";
						echo json_encode($op);
						exit;
	        		}
					
	     	}else{
	      			$op['is_success'] = false;
					$op['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]',
												   		  'Return',
												   		  '1',
												   		  '$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") {

            $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 = "";

						

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

			}elseif($total_paid >= $total_invoice ){
			
				$is_paid = 2; // complete paid
				$pay_term = 0;
			}elseif($total_paid < $total_invoice AND $total_paid >0){
				$is_paid = 1; // partially paid
			}

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

			$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 = "RT-".($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 = $discount_actual;

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

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

			}



			$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, 
										is_returned, 
										created_by, 
										last_action, 
										last_action_by)
						 		VALUES( '$invoice_number',
						 				'$invoice_date',
						 				'$is_paid',
						 				'".$_SESSION['USD_TO_IQD']."',
						 				'$invoice_currency',
						 				'$invoice_total_no_disc',
						 				'$total_invoice',
						 				'$discount',
						 				'$discount_type',
						 				'$discount_actual',
						 				'$invoice_note',
						 				'$invoice_sequence_no',
						 				'$author',
						 				'$branch_id_fk',
						 				'$stock_id_fk',
						 				'$term_and_conditions',
						 				'$saleman_id_fk',
						 				'$invoice_transaction',
						 				'$customer_id_fk',
						 				'1',
						 				'".$_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=1;$i<count($product_id);$i++){

					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]',
												   		  'Return',
												   		  '1',
												   		  '$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


				// insert into payment table the paid price

				if($invoice_type < 3){

				

					$lastaction = "Add New Payment";
					if($total_paid > 0){

						

						$resP = $db->query("SELECT pay_sequence_no FROM payments ORDER BY payment_id DESC LIMIT 1");
						$rowP = $resP->fetch_assoc();
						$pay_sequence_no 	= $rowP['pay_sequence_no'] +1;

						
						$payment_number = "PAY".$dd.($pay_sequence_no);
						$payment_transaction = 'Payment';
						$payment_note = "Add Payment for Invoice : ".$invoice_number;
						$sql_p ="INSERT INTO payments(payment_number,
				 									  pay_usd_rate,
				 									  pay_currency,
				 									  paid_amount,
				 									  paid_date,
				 									  payment_note,
				 									  payment_transaction,
				 									  pay_sequence_no,
				 									  customer_id_fk,
				 									  invoice_id_fk,
				 									  branch_id_fk,
				 									  created_by,
				 									  last_action,
				 									  last_action_by)
				 							   VALUES('$payment_number',
				 							   		  '".$_SESSION['USD_TO_IQD']."',
				 							    	  '$invoice_currency',
				 							    	  '$total_paid',
				 							    	  '$invoice_date',
				 							    	  '',
				 							    	  '$payment_transaction',
				 							    	  '$pay_sequence_no',
				 							    	  '$customer_id_fk',
				 							    	  '$invoice_inserted_id',
				 							    	  '$branch_id_fk',
				 							    	  '".$_SESSION['user_id']."',
				 							    	  '$lastaction',
				 							    	  '".$_SESSION['user_id']."')";
						


						$res_P = $db->query($sql_p);

						if ($res_P){
							$last_inserted_payment_id = $db->insert_id;


				 			$sql_pd ="INSERT INTO payments_details
				 									 (
				 									  paid_amount,
				 									  paid_date,
				 									  payment_note,
				 									  payment_transaction,
				 									  payment_id_fk,
				 									  invoice_id_fk,
				 									  customer_id_fk,
				 									  created_by,
				 									  last_action,
				 									  last_action_by)
				 							   VALUES(
				 							    	  '$total_paid',
				 							    	  '$invoice_date',
				 							    	  '',
				 							    	  '$payment_transaction',
				 							    	  '$last_inserted_payment_id',
				 							    	  '$invoice_inserted_id',
				 							    	  '$customer_id_fk',
				 							    	  '".$_SESSION['user_id']."',
				 							    	  '$lastaction',
				 							    	  '".$_SESSION['user_id']."')";
							
							$res_PD = $db->query($sql_pd);
							
							if(!$res_PD){
								$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;
						}


					}
				}
				// end payment insertion


				


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