<?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['typ'])? ' AND Inv.is_single='.$_GET['typ']:'';
			$cond1 = isset($_GET['ip'])? ' AND Inv.invoice_status='.$_GET['ip']:'';
			
			if(isset($_GET['pt']) && $_GET['pt'] ==0){ 
				// in term
				$cond1 = $cond1 . ' AND Inv.invoice_date BETWEEN DATE_SUB(CURDATE(), INTERVAL Inv.pay_term DAY) AND CURDATE() ';
			}elseif(isset($_GET['pt']) && $_GET['pt'] ==1){
				// out term
				  $cond1 = $cond1 . ' AND Inv.invoice_date NOT BETWEEN DATE_SUB(CURDATE(), INTERVAL Inv.pay_term DAY) AND CURDATE() ';
			}
			
			$table  =  '(
				(
					SELECT
					invoice_id,
					invoice_number,
					invoice_date,
					invoice_status,
					invoice_usd_rate,
					invoice_currency,
					invoice_total_price_actual,
					invoice_total_net_balance,
					invoice_note,
					customer_id_fk,
					pay_terms,
					return_date,
					(SELECT sum(paid_amount) from invoice_payment IP where IP.invoice_id_fk = Inv.invoice_id AND IP.is_deleted=0 AND IP.is_returned=0) as total_paid,
					

					(
					 (

	          			COALESCE((SELECT sum(sell_item_detail_height * item_sell_price_per_meter) FROM sell_item_detail SID, sell_item S 
	          			
					        WHERE Inv.invoice_id = SID.invoice_id_fk AND S.invoice_id_fk = Inv.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 AND SID.sell_item_detail_width = 0),0)
					    +
					   COALESCE((SELECT sum(sell_item_detail_height * sell_item_detail_width * item_sell_price_per_meter) FROM sell_item_detail SID, sell_item S 
	          			
					        WHERE Inv.invoice_id = SID.invoice_id_fk AND S.invoice_id_fk = Inv.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 AND SID.sell_item_detail_width > 0),0)
					    +
					   COALESCE((SELECT sum(sell_item_detail_width * decorate_price_per_meter) FROM sell_item_detail SID, sell_item S 
	          			
					        WHERE Inv.invoice_id = SID.invoice_id_fk AND S.invoice_id_fk = Inv.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 AND SID.sell_item_detail_width > 0),0)

					    +
					    COALESCE((SELECT sum(sell_item_quantity * item_sell_price_per_meter) FROM sell_item S  
											WHERE Inv.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 = Inv.invoice_id AND sell_item.is_deleted=0 )
					)
					-
            		COALESCE((SELECT sum(paid_amount) from invoice_payment IP where IP.invoice_id_fk = Inv.invoice_id AND IP.is_deleted=0 AND IP.is_returned=0),0) 
            		)  AS total_remained,

          
          			(

          			COALESCE((SELECT sum(sell_item_detail_height * item_sell_price_per_meter) FROM sell_item_detail SID, sell_item S 
          			
				        WHERE Inv.invoice_id = SID.invoice_id_fk AND S.invoice_id_fk = Inv.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 AND SID.sell_item_detail_width = 0),0)
				    +
				   COALESCE((SELECT sum(sell_item_detail_height * sell_item_detail_width * item_sell_price_per_meter) FROM sell_item_detail SID, sell_item S 
          			
				        WHERE Inv.invoice_id = SID.invoice_id_fk AND S.invoice_id_fk = Inv.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 AND SID.sell_item_detail_width > 0),0)
				    +
				   COALESCE((SELECT sum(sell_item_detail_width * decorate_price_per_meter) FROM sell_item_detail SID, sell_item S 
          			
				        WHERE Inv.invoice_id = SID.invoice_id_fk AND S.invoice_id_fk = Inv.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 AND SID.sell_item_detail_width > 0),0)

				    +
				    COALESCE((SELECT sum(sell_item_quantity * item_sell_price_per_meter) FROM sell_item S  
										WHERE Inv.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 = Inv.invoice_id AND sell_item.is_deleted=0 )
					) as total_invoice_price,

					(SELECT sum(actual_discount) from sell_item where sell_item.invoice_id_fk = Inv.invoice_id AND sell_item.is_deleted=0 )  as total_discount,
						customer_full_name AS name,
						customer_email AS email,
						customer_phone AS phone
					 FROM
					 invoice Inv, customer Cus
				WHERE
					(Inv.customer_id_fk=Cus.customer_id  AND Inv.is_deleted = 0 AND Inv.invoice_type = 1 '.$cond.' '.$cond1.')
				)
			) 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' => 'total_invoice_price', 'dt' => 4 ),
		    array( 'db' => 'total_paid', 'dt' => 5 ),
				array( 'db' => 'total_discount', 'dt' => 6 ),
				array( 'db' => 'total_remained', 'dt' => 7 ),
				

			  array( 'db' => 'invoice_status', 'dt' => 8 ),
				array( 'db' => 'invoice_note', 'dt' => 9 ),
				array( 'db' => 'pay_terms', 'dt' => 10 ),
				array( 'db' => 'customer_id_fk', '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']=="search") {
			$q = $_POST["q"];
			//$invid = $_POST["invid"];

			$res = $db->query( "(
					SELECT
						customer_id AS CID,
						customer_full_name AS name,
						customer_email AS email,
						customer_phone AS phone,
    					('0') AS type
					 FROM
					 customer
				WHERE
					(customer_full_name like '%$q%' OR customer_email like '%$q%' OR customer_phone like '%$q%' AND is_deleted = 0 )
				)
			LIMIT 0,15");
			$row=[];
			header('Content-Type: application/json');

			if ($res) {
				$i=0;
				while( $r = $res->fetch_assoc() ){
					$row[$i++] = array(
						"name"	 => $r['name'],
						"email"  => $r['email'],
						"phone"  => $r['phone'],
						"type"   => $r['type'],
						"cid"    => $r['CID']
					);
				}
				echo json_encode( ['status'=>true, 'error'=>null, 'data'=>["company"=>$row]]);
			}else{
				echo json_encode( ['status'=>false, 'error'=>null, 'data'=>["company"=>$row]]);
			}
			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_note, 
															  sell_item_quantity, 
															  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, 
																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,


																(

											          COALESCE((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 AND SID.sell_item_detail_width = 0),0)
															    +
															   COALESCE((SELECT sum(sell_item_detail_height * sell_item_detail_width * 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 AND SID.sell_item_detail_width > 0),0)
															    +
															   COALESCE((SELECT sum(sell_item_detail_width * decorate_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 AND SID.sell_item_detail_width > 0),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,
																(SELECT sum(sell_item_detail_height * sell_item_detail_width) 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 AND SID.sell_item_detail_width > 0) AS total_area,
																COALESCE((SELECT sum(sell_item_detail_width) 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 AND SID.sell_item_detail_width > 0),0) AS total_decor_width

												FROM invoice I
											 		LEFT JOIN sell_item S ON (S.invoice_id_fk = I.invoice_id 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['total_area']  								= $row['total_area'];
					$result['total_decor_width']  				= $row['total_decor_width'];

					
						array_push($op['invoice'],$result);


						$res1 = $db->query("SELECT sell_item_detail_id, 
																			sell_item_detail_height, 
																			sell_item_detail_width, 
																			is_part_sold, 
																		  damage_length, 
																		  part_status,
																		  invoice_id_fk,
																		  sell_item_id_fk
																		  
															FROM sell_item_detail SID
														 	WHERE SID.invoice_id_fk='$id' AND SID.sell_item_id_fk='$row[sell_item_id]' AND part_status=0 AND SID.is_deleted = 0");
						
						while ($row1 = $res1->fetch_assoc()) {
								$result1['sell_item_detail_id']  		= $row1['sell_item_detail_id'];
								$result1['sell_item_detail_height'] = $row1['sell_item_detail_height'];
								$result1['sell_item_detail_width']  = $row1['sell_item_detail_width'] ;
								$result1['is_part_sold']  					= $row1['is_part_sold'];
								$result1['damage_length'] 					= $row1['damage_length'];
								$result1['part_status']  						= $row1['part_status'];
								$result1['sell_item_id_fk']  				= $row1['sell_item_id_fk'];
								
								array_push($op['details'],$result1);
						}
						
				}		
				echo json_encode($op);
				exit;
			}else{
				echo 0;
				exit;
			}
		}


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

			$id = $_POST['id'];


			$res = $db->query("SELECT invoice_id, 
																invoice_number, 
																invoice_date,
																invoice_note,																 
															  sell_item_quantity,															  
															  sell_item_notes, 
															  S.item_type_id_fk, 
															  other_id_fk, 
															  S.product_id_fk, 
															  sell_item_id,
															  customer_full_name,
															  sell_item_delivery_name,
															  sell_item_detail_height,
															  sell_item_detail_width,
															  CONCAT(COALESCE(erbil_code,''), COALESCE(other_name,'')) AS product_name
																
																

													FROM invoice I
											 		LEFT JOIN sell_item S ON (S.invoice_id_fk = I.invoice_id AND S.is_deleted=0)
                          LEFT JOIN sell_item_detail SID ON (SID.invoice_id_fk = I.invoice_id AND SID.sell_item_id_fk=S.sell_item_id AND part_status=0 AND SID.is_deleted = 0 AND SID.sell_item_detail_width > 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 AND SID.sell_item_detail_width > 0");
			if ($res) {
				
				$op  = array('sticker'=>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['sell_item_quantity']  				= $row['sell_item_quantity'];
					$result['sell_item_notes']  					= $row['sell_item_notes'];
					$result['customer_full_name']  				= $row['customer_full_name'];
					$result['sell_item_delivery_name'] 		= $row['sell_item_delivery_name'];
					$result['sell_item_detail_height']  	= $row['sell_item_detail_height'];
					$result['sell_item_detail_width']  		= $row['sell_item_detail_width'];
					$result['product_name']  							= $row['product_name'];
					$result['invoice_note']  							= $row['invoice_note'];
					

					
						array_push($op['sticker'],$result);

				}		
				echo json_encode($op);
				exit;
			}else{
				echo 0;
				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(actual_discount) FROM sell_item SI WHERE I.invoice_id = SI.invoice_id_fk AND SI.is_deleted =0) AS total_discount,

																(

											          COALESCE((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 AND SID.sell_item_detail_width = 0),0)
															    +
															   COALESCE((SELECT sum(sell_item_detail_height * sell_item_detail_width * 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 AND SID.sell_item_detail_width > 0),0)
															    +
															   COALESCE((SELECT sum(sell_item_detail_width * decorate_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 AND SID.sell_item_detail_width > 0),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(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(sell_item_detail_height * sell_item_detail_width) 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 AND SID.sell_item_detail_width > 0) AS total_area,
																(SELECT sum(sell_item_detail_width) 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 AND SID.sell_item_detail_width > 0) AS total_decor_width

												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'];
					$result['total_area']  								= $row['total_area'];
					$result['total_decor_width']  				= $row['total_decor_width'];
					
						array_push($op['invoice'],$result);
						
				}		
				echo json_encode($op);
				exit;
			}else{
				echo 0;
				exit;
			}
		}


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

			$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, 
															  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, 
																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(actual_discount) FROM sell_item SI WHERE I.invoice_id = SI.invoice_id_fk AND SI.is_deleted =0) AS total_discount,

																(

											          COALESCE((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 AND SID.sell_item_detail_width = 0),0)
															    +
															   COALESCE((SELECT sum(sell_item_detail_height * sell_item_detail_width * 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 AND SID.sell_item_detail_width > 0),0)
															    +
															   COALESCE((SELECT sum(sell_item_detail_width * decorate_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 AND SID.sell_item_detail_width > 0),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(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 * sell_item_detail_width) 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 AND SID.sell_item_detail_width > 0) AS total_area,
																(SELECT sum(sell_item_detail_width) 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 AND SID.sell_item_detail_width > 0) AS total_decor_width

												FROM invoice I
											 		LEFT JOIN sell_item S ON (S.invoice_id_fk = I.invoice_id 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['total_area']  								= $row['total_area'];
					$result['total_decor_width']  				= $row['total_decor_width'];
					
					$result['total_paid_per_product']  		= $row['total_paid_per_product'];
					
						array_push($op['invoice'],$result);
						
				}		
				echo json_encode($op);
				exit;
			}else{
				echo 0;
				exit;
			}
		}


		if ($_POST['type']=="PDF" || $_POST['type']=="Print") {

				$invoice_html = $_POST['html'];
				$inv_no		  	= $_POST['inv_no'];
				$pay    	  	= $_POST['pay'];

				if($pay != NULL || $pay !=''){ // print Payments
						$mpdf = new mPDF('utf-8','A4',0,'sans-serif',15,15,12,12, 0, 0, 'P');
						$mpdf->autoScriptToLang = true;
						$mpdf->autoLangToFont = true;
						$mpdf->allow_charset_conversion = false;

						$mpdf->WriteHTML($invoice_html);
						$inv_dir = 'invoices/'.$inv_no;
						if(! file_exists('../'.$inv_dir)){
							mkdir('../'.$inv_dir);
						}
						$invoice_url = $inv_dir.'/'.$inv_no.'_Payments.pdf';
						$invoice_real_url = '../'.$invoice_url;
						$mpdf->Output($invoice_real_url,'F');
				}else{
						$mpdf = new mPDF('utf-8','A4',0,'sans-serif',15,15,12,12, 0, 0, 'P');
						$mpdf->WriteHTML($invoice_html);
						$inv_dir = 'invoices/'.$inv_no;
						if(! file_exists('../'.$inv_dir)){
							mkdir('../'.$inv_dir);
						}
						$invoice_url = $inv_dir.'/'.$inv_no.'.pdf';
						$invoice_real_url = '../'.$invoice_url;
						$mpdf->Output($invoice_real_url,'F');
				}



					$out['is_success'] = true;
					$out['data'] 	   = $invoice_url;
				echo json_encode($out);

		}

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

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

			$lastaction = "Add New 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'];
				
			}

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

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

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

							$sql ="INSERT INTO invoice_payment (pay_usd_rate, 
																									pay_currency, 
																									paid_amount,
																									paid_date,
																									next_payment_date,
																									next_payment_amount,
																									payment_note,
																									sell_item_id_fk, 
																									invoice_id_fk,
																									customer_id_fk, 
																									created_by, 
																									last_action, 
																									last_action_by)
										 											VALUES(	'".$_SESSION['USD_TO_IQD']."',
										 															'$invoice_currency',
										 															'$total_paid[$i]',
										 														 	'$reg_date',
										 														 	'$next_payment_date',
										 														 	'$next_payment_amount',
										 														 	'$notes[$i]',
										 														 	'$sell_ids[$i]',
										 														 	'$id',
										 														 	'$customer_id_fk',
										 														 '".$_SESSION['user_id']."',
										 														 '$lastaction',
										 														 '".$_SESSION['user_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 * sell_item_detail_width) 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 AND SID.sell_item_detail_width > 0) AS total_area,
																								(SELECT sum(sell_item_detail_width) 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 AND SID.sell_item_detail_width > 0) AS total_decor_width,

																								(

																			          COALESCE((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 AND SID.sell_item_detail_width = 0),0)
																						    +
																						   COALESCE((SELECT sum(sell_item_detail_height * sell_item_detail_width * 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 AND SID.sell_item_detail_width > 0),0)
																						    +
																						   COALESCE((SELECT sum(sell_item_detail_width * decorate_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 AND SID.sell_item_detail_width > 0),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[$i]')
											 		
											 									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'];
										$total_area 										= $row1['total_area'];
										$total_decor_width 							= $row1['total_decor_width'];
										
										// compute total product price

										$total_product_price = 0;

										if($total_length > 0 && $total_length !=NULL){

											if($total_area > 0 && $total_area !=NULL){ // it mean curtain
												$total_product_price = ($total_area * $item_sell_price_per_meter) + ($total_decor_width * $decorate_price_per_meter) -  $actual_discount;

											}else{
												$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 ){
											$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[$i]' ";

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

												// update invoice status
												

												$lastaction = "Update Invoice status after Adding payments";
												if($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();
													
													$successfully = true;
													// 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{

								$db->rollback();
								$op['is_success'] = false;
								$op['data'] 	  = "حصل خطأ : لم يتم اضافة الدفوعات ";

								echo json_encode($op);
								exit;
							}
					} // if total_paid >0

			}  // for i

			if($successfully == false){
				$op['is_success'] = false;
				$op['data'] 	  = "الرجاء قم بادخال دفعة واحدة على الاقل";
				echo json_encode($op);
				exit;
			}else{
				$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 * sell_item_detail_width) 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 AND SID.sell_item_detail_width > 0) AS total_area,

																								(SELECT sum(sell_item_detail_width) 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 AND SID.sell_item_detail_width > 0) AS total_decor_width,

																								(

																			          COALESCE((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 AND SID.sell_item_detail_width = 0),0)
																						    +
																						   COALESCE((SELECT sum(sell_item_detail_height * sell_item_detail_width * 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 AND SID.sell_item_detail_width > 0),0)
																						    +
																						   COALESCE((SELECT sum(sell_item_detail_width * decorate_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 AND SID.sell_item_detail_width > 0),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'];
										$total_area 										= $row1['total_area'];
										$total_decor_width 							= $row1['total_decor_width'];

										// compute total product price

										$total_product_price = 0;

										if($total_length > 0 && $total_length !=NULL){

											if($total_area > 0 && $total_area !=NULL){ // it mean curtain
												$total_product_price = ($total_area * $item_sell_price_per_meter) + ($total_decor_width * $decorate_price_per_meter) -  $actual_discount;

											}else{
												$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 * sell_item_detail_width) 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 AND SID.sell_item_detail_width > 0) AS total_area,
																								(SELECT sum(sell_item_detail_width) 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 AND SID.sell_item_detail_width > 0) AS total_decor_width,

																								(

																			          COALESCE((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 AND SID.sell_item_detail_width = 0),0)
																						    +
																						   COALESCE((SELECT sum(sell_item_detail_height * sell_item_detail_width * 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 AND SID.sell_item_detail_width > 0),0)
																						    +
																						   COALESCE((SELECT sum(sell_item_detail_width * decorate_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 AND SID.sell_item_detail_width > 0),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'];
										$total_area 										= $row1['total_area'];
										$total_decor_width 							= $row1['total_decor_width'];
										
										// compute total product price

										$total_product_price = 0;

										if($total_length > 0 && $total_length !=NULL){

											if($total_area > 0 && $total_area !=NULL){ // it mean curtain
												$total_product_price = ($total_area * $item_sell_price_per_meter) + ($total_decor_width * $decorate_price_per_meter) -  $actual_discount;

											}else{
												$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']=="get_edit_invoice") {

			$id = $_POST['id'];
					
			$res = $db->query("SELECT * FROM invoice_pay  WHERE invoice_id_fk ='$id' AND is_returned=0  AND is_deleted = 0");
								
			if ($res) {
				//$row = $res->fetch_all();
				$op  = array('is_success'=>false, 'payments' => array(), 'sell'=>array());
				while ($row = $res->fetch_assoc()) {
				   
						$result[0]  = $row['invoice_pay_id'];
						$result[1]  = $row['pay_usd_rate'];
						$result[2]  = $row['pay_currency'];
						$result[3]  = $row['paid_amount'];
						$result[4]  = $row['paid_date'];
						$result[5]  = $row['is_returned'];
						$result[6]  = $row['returned_date'];
						$result[7]  = $row['invoice_id_fk'];
						array_push($op['payments'],$result);
				}

			}else{
				echo 0;
				exit;
			}

			$res = $db->query("SELECT S.* ,P.product
				FROM sell_item S, product P WHERE S.product_id_fk=P.product_id AND S.invoice_id_fk ='$id'  AND S.is_deleted = 0");
			if ($res) {
				
				while ($row = $res->fetch_assoc()) {
				   
						$result[0]  = $row['sell_item_id'];
						$result[1]  = $row['sell_quantity'];
						$result[2]  = $row['sell_discount'];
						$result[3]  = $row['sell_unit_price'];
						$result[4]  = $row['sell_date'];
						$result[5]  = $row['product_id_fk'];
						$result[6]  = $row['invoice_id_fk'];
						$result[7]  = $row['product'];
						$result[8]  = $row['sell_avg_price'];
						
						array_push($op['sell'],$result);
				}
				
			}else{
				echo 0;
				exit;
			}

			$op['is_success'] = true;

			echo json_encode($op);

			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']=="get_others") {

			
			$res = $db->query(" SELECT * FROM others WHERE is_deleted= 0 ");
			if ($res) {
				
				$op  = array();
				while ($row = $res->fetch_assoc()) {
				   
						$result[0]  = $row['other_id'];
						$result[1]  = $row['other_name'];
						$result[2]  = $row['other_buy_price_per_unit'];
						
						array_push($op,$result);
				}
				echo json_encode($op);

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

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

						$other_name									=addslashes($_POST["other_name"]);
						$other_description 					=addslashes($_POST["other_description"]);
						$other_buy_price_per_unit		=addslashes($_POST["other_buy_price_per_unit"]);

						$lastaction = "Add New Ohters";

						$res = $db->query(" SELECT * FROM others WHERE is_deleted= 0 AND other_name = '$other_name'");

						if (mysqli_num_rows($res)>0) {
							$op['is_success'] = false;
							$op['data'] 	  = "خطأ الاسم مكرر";
							echo json_encode($op);
							exit;
						}

						$sql ="INSERT INTO others (other_name, other_description, other_buy_price_per_unit, created_by, last_action, last_action_by)
									 VALUES('$other_name','$other_description','$other_buy_price_per_unit','".$_SESSION['user_id']."','$lastaction','".$_SESSION['user_id']."')";

							$res = $db->query($sql);
							if ($res) {
								$op['is_success'] = true;
								$op['data'] 	  = "تمت الاضافة بنجاح";
								echo json_encode($op);
								exit;
							}else{
								$op['is_success'] = false;
								$op['data'] 	  = "خطأ لم تتم الاضافة بنجاح";
								echo json_encode($op);
								exit;
							}
		}

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



			$id 										= $_POST['obj_id'];

			$quantity								=$_POST["quantity"];
			$discount								=$_POST["discount"];
			$product_id							=$_POST["product_id"];
			$unit_price							=$_POST["unit_price"];
			$product_avg_price			=$_POST["product_avg_price"];
			$customer_id						=addslashes($_POST["customer_id"]);
			$pay_term 							=addslashes($_POST["pay_term"]);
			$customer_type					=addslashes($_POST["customer_type"]);
			$total_invoice			  	=addslashes($_POST["total_invoice"]);
			$net_balance				  	=addslashes($_POST["net_balance"]);
			$total_paid	  					=addslashes($_POST["total_paid"]);
			$is_offer								=addslashes($_POST["is_offer"]);

			if($is_offer == 1 AND $total_paid > 0){
				echo "This is an offer it can't contain Paid Price!!";
				echo 0;
				exit;
			}

			if($net_balance ==0 OR $net_balance =='' OR $net_balance ==NULL){
				echo "Please Enter Net Balance Price!!";
				echo 0;
				exit;
			}
			// check invoice status is it complete or partially or not paid

			$is_paid = 0; // not paid
			if($total_paid >= $net_balance AND $is_offer == 0){
				$is_paid = 2; // complete paid
				$pay_term = 0;
			}elseif($total_paid < $net_balance AND $total_paid >0 AND $is_offer == 0){
				$is_paid = 1; // partially paid
			}elseif($is_offer == 1){
				$is_paid = 4; // Offer
			}

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

			
			// extract array VALUES


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

			$sql = "UPDATE invoice SET  is_paid = '$is_paid', total_price_actual='$total_invoice',total_net_balance='$net_balance', pay_term ='$pay_term', customer_id_fk ='$customer_id',
										customer_type='$customer_type',last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE invoice_id = $id";

			$res = $db->query($sql);
				if ($res) {
						$last_inserted_id = $id;

						// insert into payment table the paid price
						/*$lastaction = "Add New Payment";
						if($total_paid > 0){
							$sql ="INSERT INTO invoice_pay (paid_amount, pay_usd_rate, pay_currency, invoice_id_fk, created_by, last_action, last_action_by)
										 VALUES('$total_paid','','','$last_inserted_id','".$_SESSION['user_id']."','$lastaction','".$_SESSION['user_id']."')";
							$res = $db->query($sql);
							if (!$res){
								$db->rollback();
								echo $db->error;
								echo 0;
								exit;
							}
						}*/

						// delete all sold items for this invoice then we add the new sells
						$lastaction = "Delete items by updated invoice";

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

						$res = $db->query($sql);

						if (!$res){
								$db->rollback();
								echo $db->error;
								echo 0;
								exit;
						}


						// insert into sell items the sold products
						$lastaction = "Add New Sell Product";
						$check_all_inserted = false;
						for ($i=0;$i<count($quantity);$i++){

							$sql ="INSERT INTO sell_item (sell_quantity, sell_discount, sell_unit_price, product_id_fk, invoice_id_fk,sell_avg_price, created_by, last_action, last_action_by)
										 VALUES('$quantity[$i]','$discount[$i]','$unit_price[$i]','$product_id[$i]','$last_inserted_id','$product_avg_price[$i]','".$_SESSION['user_id']."','$lastaction','".$_SESSION['user_id']."')";
							$res = $db->query($sql);
							if ($res) {
								$check_all_inserted = true;
							}else{
								$check_all_inserted = false;
								$db->rollback();
								echo $db->error;
								echo 0;
								exit;
							}

						} // for i

						if($check_all_inserted == true){
							$db->commit();
							echo 1;
							exit;
						}else{
							$db->rollback();
							echo $db->error;
							echo 0;
							exit;
						}
				}else{
					$db->rollback();
					echo $db->error;
					echo 0;
				}


		}


		if ($_POST['type']=="delete") {
			$id	 = addslashes($_POST["obj_id"]);
			$reg_date = date("Y-m-d h:i:s", time());
			$lastaction = "Delete Invoice";

			$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

					$lastaction = "Delete Invoice and  All its Payments  ";
					$sql ="UPDATE invoice_pay SET is_deleted = '1',
							 deleted_at='$reg_date', last_action='$lastaction', last_action_by='".$_SESSION['user_id']."'  WHERE invoice_id_fk = $id";
					$res = $db->query($sql);

					if ($res) {
							$lastaction = "Delete Invoice and  All its Sell Items  ";
							$sql ="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";
							$res = $db->query($sql);

							if ($res) {
									$db->commit();
									echo 1;
									exit;
							}else{
									$db->rollback();
									echo $db->error;
									echo 0;
									exit;
							}
					}else{
						$db->rollback();
						echo $db->error;
						echo 0;
						exit;
					}


			}
			else{
				echo $db->error;
				$db->rollback();

				echo 0;
				exit;
			}



		}

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

            $product_id							= $_POST["product_id"];
						$quantity								= $_POST["quantity"];
						$item_length						= $_POST["item_length"];
						$sell_price							= $_POST["sell_price"];
						$discount								= $_POST["discount"];
						$discount_type					= $_POST["discount_type"];
						$discount_actual				= $_POST["discount_actual"];
						$roll_id_fk							= $_POST["roll_id_fk"];
						$sell_item_note					= $_POST["sell_item_note"];
						$sell_item_deivery			= $_POST["sell_item_deivery"];
						$row_price							= $_POST["row_price"];
						$row_payments						= $_POST["row_payments"];
						$item_type 							= $_POST["item_type"];
						$original_length				= $_POST["original_length"];
						$decor_price						= $_POST["decor_price"];


						$invoice_type						= addslashes($_POST["invoice_type"]);
						$is_single							= addslashes($_POST["is_single"]);
						$customer_id_fk 				= addslashes($_POST["customer_id_fk"]);
						$pay_term								= addslashes($_POST["pay_term"]);
						$invoice_date			  		= addslashes($_POST["invoice_date"]);
						$price_profile_id				= addslashes($_POST["price_profile_id"]);
						$invoice_note	  				= addslashes($_POST["invoice_note"]);
						$other_id_fk						= addslashes($_POST["other_id_fk"]);
						$other_quantity_1				= addslashes($_POST["other_quantity_1"]);
						$other_sell_price				= addslashes($_POST["other_sell_price"]);
						$other_discount					= addslashes($_POST["other_discount"]);
						$other_discount_type		= addslashes($_POST["other_discount_type"]);
						$other_discount_actual	= addslashes($_POST["other_discount_actual"]);
						$other_note							= addslashes($_POST["other_note"]);
						$other_row_price				= addslashes($_POST["other_row_price"]);
						$other_pay							= addslashes($_POST["other_pay"]);

						$total_invoice					= addslashes($_POST["total_invoice"]);
						$total_paid							= addslashes($_POST["total_paid"]);
						
						$is_offer = 0; // use it later
						$invoice_currency					= 1; //USD
						$term_and_conditions = "";

						// if($is_offer == 1 AND $total_paid > 0){
						// 	echo "This is an offer it can't contain Paid Price!!";
						// 	echo 0;
						// 	exit;
						// }

						// if($net_balance ==0 OR $net_balance =='' OR $net_balance ==NULL){
						// 	echo "Please Enter Net Balance Price!!";
						// 	echo 0;
						// 	exit;
						// }
						// check invoice status is it complete or partially or not paid

						$is_paid = 0; // not paid
						if($total_paid > $total_invoice ){
							$op['is_success'] = false;
							$op['data'] 	  = "هناك خطأ : المبلغ الكلي المدفوع اكبر من سعر الكلي للوصل, الرجاء التأكد من قيم الدفعات";
							echo json_encode($op);
							exit;
						}elseif($total_paid == $total_invoice AND $is_offer == 0){
							$is_paid = 2; // complete paid
							$pay_term = 0;
						}elseif($total_paid < $total_invoice AND $total_paid >0 AND $is_offer == 0){
							$is_paid = 1; // partially paid
						}elseif($is_offer == 1){
							$is_paid = 4; // Offer
						}

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

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

						$dd = date("d-m-y", time());
						$invoice_number = "INV-".$dd."-".($last_invoice_id +1);
						// extract array VALUES

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

						$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_note, invoice_type,is_single, term_and_conditions, pay_terms, customer_id_fk, created_by, last_action, last_action_by)
									 VALUES('$invoice_number','$invoice_date','$is_paid','".$_SESSION['USD_TO_IQD']."','$invoice_currency','$total_invoice','$invoice_note','$invoice_type','$is_single','$term_and_conditions','$pay_term','$customer_id_fk','".$_SESSION['user_id']."','$lastaction','".$_SESSION['user_id']."')";
						
						$res = $db->query($sql);
						if ($res) {
									$invoice_inserted_id = $db->insert_id;




									// insert Others product
									if($other_id_fk > 0){
											$lastaction = "Add New Others";
											if($other_pay > $other_row_price ){
												$db->rollback();
												$op['is_success'] = false;
												$op['data'] 	  = "هناك خطأ : المبلغ المدفوع للمواد الاخرى اكبر من السعر الكلي للمادة, الرجاء التأكد من قيم الدفعات";
												echo json_encode($op);
												exit;
											}elseif( $other_pay == $other_row_price){
												$is_paid = 2;
											}elseif($other_pay < $other_row_price && $other_pay >0){
												$is_paid = 1;
											}else{
												$is_paid = 0;
											}

											$sql_oth ="INSERT INTO sell_item (sell_item_quantity, 
																									sell_discount,
																									sell_discount_type,
																									actual_discount,
																									item_buy_price_per_meter,
																									item_sell_price_per_meter,
																									decorate_price_per_meter,
																									sell_item_barcode,
																									sell_date,
																									is_paid,
																									sell_item_type,
																									sell_item_notes,
																									item_type_id_fk,
																									other_id_fk,
																									product_id_fk,
																									invoice_id_fk,
																									created_by,
																									last_action,
																									last_action_by)
													 								 VALUES('$other_quantity_1',
													 								 				'$other_discount',
													 								 				'$other_discount_type',
													 								 				'$other_discount_actual',
													 								 				'',
													 								 				'$other_sell_price',
													 								 				'',
													 								 				'',
													 								 				'$invoice_date',
													 								 				'$is_paid',
													 								 				'$invoice_type',
													 								 				'$other_note',
													 								 				'0',
													 								 				'$other_id_fk',
													 								 				'0',
													 								 				'$invoice_inserted_id',
													 								 				'".$_SESSION['user_id']."',
													 								 				'$lastaction',
													 								 				'".$_SESSION['user_id']."')";
												$res_oth = $db->query($sql_oth);
												if (!$res_oth) {

													$db->rollback();
													$op['is_success'] = false;
													$op['data'] 	  	= "خطأ : لم تتم اضافة المواد الاخرى بنجاح";
													echo json_encode($op);
													exit;
												}else{
													// insert into payment table the paid price of Other
													$last_inserted_other_id = $db->insert_id;
													$lastaction = "Add New Other Payment";
													if($total_paid > 0){

														if($is_paid > 0 ){

																$sql_p ="INSERT INTO invoice_payment (pay_usd_rate,
																																	pay_currency,
																																	paid_amount,
																																	paid_date,
																																	sell_item_id_fk,
																																	invoice_id_fk,
																																	customer_id_fk,
																																	created_by,
																																	last_action, 
																																	last_action_by)
																				 									VALUES('".$_SESSION['USD_TO_IQD']."',
																				 												 '$invoice_currency',
																				 												 '$other_pay',
																				 												 '$invoice_date',
																				 												 '$last_inserted_other_id',
																				 												 '$invoice_inserted_id',
																				 												 '$customer_id_fk',
																				 												 '".$_SESSION['user_id']."',
																				 												 '$lastaction',
																				 												 '".$_SESSION['user_id']."')";
																	$res = $db->query($sql_p);
																	if (!$res){
																		// $op['data'] 	  	= $db->error;

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


													}

													// end payment insertion
												}


									}
																			
									// end other insertion

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

									$sql_multi_update ="";
									$sql_on_update ="";


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

										$actual_qty = count($item_length[$i])-1;
										$item_buy_price_per_meter ='';
										$decorate_price =$decor_price[$i];
										$barcode = '';

										if($row_payments[$i] > $row_price[$i] ){
											$db->rollback();
											$op['is_success'] = false;
											$op['data'] 	  = "هناك خطأ : المبلغ المدفوع للمنتج اكبر من السعر الكلي للمادة, الرجاء التأكد من قيم الدفعات";
											echo json_encode($op);
											exit;
										}elseif($row_payments[$i] == $row_price[$i] ){
											$is_paid = 2;
										}elseif($row_payments[$i] < $row_price[$i]  && $row_payments[$i] >0){
											$is_paid = 1;
										}else{
											$is_paid = 0;
										}

										$sql ="INSERT INTO sell_item (sell_item_quantity, 
																									sell_discount,
																									sell_discount_type,
																									actual_discount,
																									item_buy_price_per_meter,
																									item_sell_price_per_meter,
																									decorate_price_per_meter,
																									sell_item_barcode,
																									sell_date,
																									is_paid,
																									sell_item_type,
																									sell_item_notes,
																									sell_item_delivery_name,
																									item_type_id_fk,
																									other_id_fk,
																									product_id_fk,
																									invoice_id_fk,
																									created_by,
																									last_action,
																									last_action_by)
													 								 VALUES('$actual_qty',
													 								 				'$discount[$i]',
													 								 				'$discount_type[$i]',
													 								 				'$discount_actual[$i]',
													 								 				'$item_buy_price_per_meter',
													 								 				'$sell_price[$i]',
													 								 				'$decorate_price',
													 								 				'$barcode',
													 								 				'$invoice_date',
													 								 				'$is_paid',
													 								 				'$invoice_type',
													 								 				'$sell_item_note[$i]',
													 								 				'$sell_item_deivery[$i]',
													 								 				'$item_type[$i]',
													 								 				'0',
													 								 				'$product_id[$i]',
													 								 				'$invoice_inserted_id',
													 								 				'".$_SESSION['user_id']."',
													 								 				'$lastaction',
													 								 				'".$_SESSION['user_id']."')";
										$res = $db->query($sql);

										if ($res) {
											$check_all_inserted_items = true;
											$check_all_inserted_items_details = false;

											
											$last_inserted_sell_item_id = $db->insert_id;


											// insert into payment table the paid price
											$lastaction = "Add New Payment";
											if($total_paid > 0){

												if($is_paid > 0 ){

														$sql_p ="INSERT INTO invoice_payment (pay_usd_rate,
																															pay_currency,
																															paid_amount,
																															paid_date,
																															sell_item_id_fk,
																															invoice_id_fk,
																															customer_id_fk,
																															created_by,
																															last_action, 
																															last_action_by)
																		 									VALUES('".$_SESSION['USD_TO_IQD']."',
																		 												 '$invoice_currency',
																		 												 '$row_payments[$i]',
																		 												 '$invoice_date',
																		 												 '$last_inserted_sell_item_id',
																		 												 '$invoice_inserted_id',
																		 												 '$customer_id_fk',
																		 												 '".$_SESSION['user_id']."',
																		 												 '$lastaction',
																		 												 '".$_SESSION['user_id']."')";
															$res = $db->query($sql_p);
															if (!$res){
																$db->rollback();
																$op['is_success'] = false;
																$op['data'] 	  = "هناك خطأ : لم يتم اضافة الدفوعات الرجاء اعادة تحميل الصفحة والمحاولة من جديد";
																echo json_encode($op);
																exit;
															}
																
												}


											}

											// end payment insertion


											// insert into sell_item_detail Table

											$lastaction = "Add New Sell item detail";


											$sql_multi 				="";
											

											for ($j=1;$j<count($item_length[$i]);$j++){
												$item_height = $item_length[$i][$j];
												$item_width  = '';  // for curtain only
												$damge_len   = '';  // for future
												$part_status = 0; // flag as sold

												$RollID_fk   = $roll_id_fk[$i][$j];

												// check item if it have original length that mean it should be have roll_id_fk

												if($RollID_fk > 0 && $RollID_fk != 'c'){ // it have purchase detail and original length
													
													$org_length  = $original_length[$i][$j];

													if($item_height == $org_length){
														$is_part_sold = 0;
													}elseif($item_height < $org_length && $item_height > 0){
														$is_part_sold = 1;
													}else{
														$is_part_sold = '';
													}

												}else{
													$is_part_sold = '';
												}

												// this is for curtains we should check if $RollID = c if true we should separate the length and insert it as height and width

												if($RollID_fk == 'c'){
													$curt_area	 = explode(';', $item_height) ;
													$item_height = $curt_area[0];
													$item_width  = $curt_area[1];  // for curtain only
												}

												$sql_multi = $sql_multi . "  ('$invoice_date',
																											'$item_height',
																											'$item_width',
																											'$barcode',
																											'$is_part_sold',
																											'$damge_len',
																											'$part_status',
																											'$last_inserted_sell_item_id',
																											'$product_id[$i]',
																											'$invoice_inserted_id',
																											'$RollID_fk',
																											'".$_SESSION['user_id']."',
																											'$lastaction',
																											'".$_SESSION['user_id']."') ,";
												if($RollID_fk > 0 && $RollID_fk != 'c'){
													// now we should check item_status to update it according to new sell, so we should check the remained length to set it may be as finish or completly sold or partially sold .. etc
													$res_itm = $db->query("SELECT purchase_item_detail_id, item_length, 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
													
													WHERE PID.is_deleted=0 AND PID.purchase_item_detail_id = $RollID_fk");

													$row_itm = $res_itm->fetch_assoc();

													$current_item_status 				= $row_itm['item_status'];
													$current_remaind 		 				= $row_itm['remained_length'];
													$current_original_length 		= $row_itm['item_length'];

													// check may item status changed by another person during making current invoice 
													if ($current_item_status==1 || $current_item_status==4 || $current_item_status==6 || $current_item_status==7 || $current_item_status==10) {
															
															$check_all_inserted_items_details = false;
															$db->rollback();
															// echo $db->error;
															$op['is_success'] = false;
															$op['data'] 	  = "حصل خطأ : لقد تم تغيير حالة احدى المواد المباعة في هذا الوصل الى حالة لايمكن بيعها الرجاء اعادة تحميل الصفحة والمحاولة من جديد  (رقم المادة هو : ".$RollID_fk.")";
															echo json_encode($op);
															exit;
													}

													// check may item sold completly or partially by another person during making current invoice so we should compare remained length with sold length
													if ($item_height >  $current_remaind) {
															
															$check_all_inserted_items_details = false;
															$db->rollback();
															// echo $db->error;
															$op['is_success'] = false;
															$op['data'] 	  = "حصل خطأ : الكمية المباعة اكبر من الكمية المتبقية , اما بسبب انه تم بيع المادة اثناء عمل هذا الوصل الرجاء اعادة تحميل الصفحة والمحاولة من جديد  (رقم المادة هو : ".$RollID_fk.")";
															echo json_encode($op);
															exit;
													}

													switch ($current_item_status) {
												       case 0:
												       case 2:
												       case 5:
												       case 8:
												       case 11:
												          if ($item_height >=  $current_remaind) {
												          	$item_status = 1 ; // completly sold
												          }else{
												          	$item_status = 5 ; // Partially sold
												          }
												          break;
												       case 3:
												          if ($item_height >=  $current_remaind) {
												          	$item_status = 10 ; // finished (used in production and partially sold)
												          }else{
												          	$item_status = 9 ; // used in production and partially sold
												          }
												          break;
												       case 9:
												          if ($item_height >=  $current_remaind) {
												          	$item_status = 10 ; // finished (used in production and partially sold)
												          }else{
												          	$item_status = 9 ; // used in production and partially sold
												          }
												          break;
												       
													}

													
													if($RollID_fk > 0 && $RollID_fk != 'c'){

														$sql_multi_update = $sql_multi_update . "  ('$RollID_fk',
																																		'$item_status',
																															'Update Item Status',
																															'".$_SESSION['user_id']."') ,";

														$sql_on_update = $sql_on_update . " item_status = VALUES(item_status) ,
																															last_action = VALUES(last_action) ,
																															last_action_by = VALUES(last_action_by) ,";

													}
													

												}
												
											} // for j
											
											$sql_multi = rtrim( $sql_multi,',');

										  $sql_m ="INSERT INTO sell_item_detail(sell_date_detail,
																													sell_item_detail_height,
																													sell_item_detail_width,
																													sell_item_detail_barcode,
																													is_part_sold,
																													damage_length,
																													part_status,
																													sell_item_id_fk,
																													product_id_fk,
																													invoice_id_fk,
																													purchase_item_detail_id_fk,
																													created_by,
																													last_action,
																													last_action_by)
														VALUES " . $sql_multi;

											$res = $db->query($sql_m);

														if ($res) {
															$check_all_inserted_items_details = true;


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

														}

											


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

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

									} // for i

										if($check_all_inserted_items == true && $check_all_inserted_items_details == true ){

															// $check_update_purchase_items_details = false;
															// we should also update purchase_item_details Table & set item_status=1
														// we should check may this invoice does not contain any sold item that have purchase so we should just commit

														if($sql_multi_update !=''){

															$sql_multi_update = rtrim( $sql_multi_update,',');
															$sql_on_update 		= rtrim( $sql_on_update,',');

														  $sql_m_u ="INSERT INTO purchase_item_detail(
														  										  							purchase_item_detail_id,
																																	item_status,
																																	last_action,
																																	last_action_by)
																		VALUES " . $sql_multi_update . " ON DUPLICATE KEY UPDATE " . $sql_on_update;

															$res = $db->query($sql_m_u);
															if ($res) {
																// $check_update_purchase_items_details = true;

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

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

															// end update purchase_item_details

														}else{
																$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 create

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