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


	function pluck ( $a, $prop ){
		$out = array();

		for ( $i=0, $len=count($a) ; $i<$len ; $i++ ) {
			$out[] = $a[$i][$prop];
		}

		return $out;
	}

	function bind ( &$a, $val, $type )
	{
		$key = ':binding_'.count( $a );

		$a[] = array(
			'key' => $key,
			'val' => $val,
			'type' => $type
		);

		return $key;
	}


	function orderss($columns){
		$order = '';

		if ( isset($_GET['order']) && count($_GET['order']) ) {
			$orderBy = array();
			$dtColumns = pluck( $columns, 'dt' );

			for ( $i=0, $ien=count($_GET['order']) ; $i<$ien ; $i++ ) {
				// Convert the column index into the column data property
				$columnIdx = intval($_GET['order'][$i]['column']);
				$requestColumn = $_GET['columns'][$columnIdx];

				$columnIdx = array_search( $requestColumn['data'], $dtColumns );
				$column = $columns[ $columnIdx ];

				if ( $requestColumn['orderable'] == 'true' ) {
					$dir = $_GET['order'][$i]['dir'] === 'asc' ?
						'ASC' :
						'DESC';

				  
					$cols = explode(' AS ',$column['db']);
					$col =  $column['db'] ;
					if( count($cols) > 1 ){
						$col = $cols[1];
					}
					####### IMAD CODE #########
					$orderBy[] = '`'.$col.'` '.$dir;
				}
			}

			$order = 'ORDER BY '.implode(', ', $orderBy);
		}

		return $order;
	}



	function filterss ( $columns )
	{
		$globalSearch 	= array();
		$columnSearch 	= array();
		$dtColumns 		= pluck( $columns, 'dt' );


		$total_disc 		= '(SELECT sum(actual_discount) from sell_item where 
								sell_item.invoice_id_fk = Inv.invoice_id AND sell_item.is_deleted=0 )';

		

		if ( isset($_GET['search']) && $_GET['search']['value'] != '' ) {
			$str = $_GET['search']['value'];

			for ( $i=0, $ien=count($_GET['columns']) ; $i<$ien ; $i++ ) {
				$requestColumn = $_GET['columns'][$i];
				$columnIdx = array_search( $requestColumn['data'], $dtColumns );
				$column = $columns[ $columnIdx ];

				if ( $requestColumn['searchable'] == 'true' ) {
					// $binding = bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
					$cols = explode(' AS ',$column['db']);
					$col =  $column['db'] ;
					if( count($cols) > 1 ){
						$col = $cols[1];
					}


					
					if($col == 'total_invoice_price_no_discount'){

						$globalSearch[] = " (".$total_disc." + invoice_total_price_actual) LIKE "."'%".$str."%'";

					}elseif($col == 'total_discount'){

						$globalSearch[] = " (".$total_disc.") LIKE "."'%".$str."%'";


					}elseif($col == 'total_invoice_price'){
						$globalSearch[] = " (invoice_total_price_actual) LIKE "."'%".$str."%'";

					}else{

						$globalSearch[] = "`".$col."` LIKE "."'%".$str."%'";

					}

					
				}
			}
		}

		// Individual column filtering
		if ( isset( $_GET['columns'] ) ) {
			for ( $i=0, $ien=count($_GET['columns']) ; $i<$ien ; $i++ ) {
				$requestColumn = $_GET['columns'][$i];
				$columnIdx = array_search( $requestColumn['data'], $dtColumns );
					// print_r( $columns);
				
				$column = $columns[ $columnIdx ];

				$str = $requestColumn['search']['value'];

				
				$cols = explode(' AS ',$column['db']);
				$col =  $column['db'] ;
				if( count($cols) > 1 ){
					$col = $cols[1];
				}
				####### IMAD CODE #########

				if ( $requestColumn['searchable'] == 'true' && $str != '' ) {
					
					if($col == 'total_invoice_price_no_discount'){

						$columnSearch[] = " (".$total_disc." + invoice_total_price_actual) LIKE "."'%".$str."%'";

					}elseif($col == 'total_discount'){

						$columnSearch[] = " (".$total_disc.") LIKE "."'%".$str."%'";


					}elseif($col == 'total_invoice_price'){
						$columnSearch[] = " (invoice_total_price_actual) LIKE "."'%".$str."%'";

					}else{

						$columnSearch[] = "`".$col."` LIKE "."'%".$str."%'";

					}

				}
			}
		}

		// Combine the filters into a single string
		$where = '';

		if ( count( $globalSearch ) ) {
			$where = '('.implode(' OR ', $globalSearch).')';
		}

		if ( count( $columnSearch ) ) {
			$where = $where === '' ?
				implode(' AND ', $columnSearch) :
				$where .' AND '. implode(' AND ', $columnSearch);
		}

		if ( $where !== '' ) {
			$where = ' AND '.$where;
		}

		return $where;
	}
	


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

		if( isset($_GET['type']) && ($_GET['type'] == "load")){
			// DB table to use
			// show All products for specific category
			$cond = isset($_GET['id'])? ' AND Inv.customer_id_fk='.$_GET['id']:'';
			$cond1 = isset($_GET['ip'])? ' AND Inv.invoice_status='.$_GET['ip']:'';
			
			$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' => 'customer_full_name', 'dt' => 3 ),
		    	array( 'db' => 'customer_phone', 'dt' => 4 ),
		    	array( 'db' => 'total_invoice_price_no_discount', 'dt' => 5 ),
				array( 'db' => 'total_discount', 'dt' => 6 ),
				array( 'db' => 'total_invoice_price', '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 ),
				array( 'db' => 'deleted_at', 'dt' => 12 ),

			);


			if ( isset($_GET['start']) && $_GET['length'] != -1 ) {
				$limit = "LIMIT ".intval($_GET['start']).", ".intval($_GET['length']);
			}else{
				$limit = "";
			}
			
			$bindings = array();

			$ords = orderss($columns);
			$fltr = filterss($columns);


			
			$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,
					


          			(invoice_total_price_actual) as total_invoice_price,


					(invoice_total_price_actual
				    	+
				    	(SELECT sum(actual_discount) from sell_item where sell_item.invoice_id_fk = Inv.invoice_id AND sell_item.is_deleted=1 )
						
					) as total_invoice_price_no_discount,

					
					(SELECT sum(actual_discount) from sell_item where sell_item.invoice_id_fk = Inv.invoice_id AND sell_item.is_deleted=1 )  as total_discount,

						customer_full_name ,
						customer_email AS email,
						customer_phone,
						Inv.deleted_at 
					 FROM
					 invoice Inv, customer Cus
				WHERE
					(Inv.customer_id_fk=Cus.customer_id  AND Inv.is_deleted = 1 AND Inv.invoice_type = 0 '.$cond.' '.$cond1.'  '.$fltr.') '. $ords. ' '.$limit.'
				)
			) AS ALLTBL';


			$raw_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,
					


          			(invoice_total_price_actual
				 		
					) as total_invoice_price,


					(
					invoice_total_price_actual
					+
					(SELECT sum(actual_discount) from sell_item where sell_item.invoice_id_fk = Inv.invoice_id AND sell_item.is_deleted=1 )
						
					) as total_invoice_price_no_discount,

					
					(SELECT sum(actual_discount) from sell_item where sell_item.invoice_id_fk = Inv.invoice_id AND sell_item.is_deleted=1 )  as total_discount,

						customer_full_name ,
						customer_email AS email,
						customer_phone,
						Inv.deleted_at 
					 FROM
					 invoice Inv, customer Cus
				WHERE
					(Inv.customer_id_fk=Cus.customer_id  AND Inv.is_deleted = 1 AND Inv.invoice_type = 0 '.$cond.' '.$cond1.'  '.$fltr.')
				)
			) AS ALLTBL';
			// Table's primary key
			//echo $table;
			
			// 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_optimized( $_GET, $sql_details, $table, $primaryKey, $columns ,null, '', $raw_table  )
			);
			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_phone2,
									customer_address, 
									CONCAT(COALESCE(erbil_code,''), COALESCE(other_name,'')) AS product_name,
									(SELECT sum(paid_amount) FROM payments IP WHERE I.invoice_id = IP.invoice_id_fk AND IP.is_returned=0 AND IP.is_deleted =0 AND IP.payment_transaction='Payment') AS total_paid,

									((SELECT sum(sell_item_detail_height * item_sell_price_per_meter) FROM sell_item_detail SID, sell_item S 

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

									(SELECT sum(actual_discount) FROM sell_item SI WHERE I.invoice_id = SI.invoice_id_fk AND SI.is_deleted =1) 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 = 1) AS total_length

					FROM invoice I
				 		LEFT JOIN sell_item S ON (S.invoice_id_fk = I.invoice_id AND S.is_deleted=1)
				 		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 = 1");
			if ($res) {
				//$row = $res->fetch_all();
				//echo json_encode($row);

				$op  = array('invoice'=>array(),'details'=>array(),'account'=>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_id_fk']  				= $row['customer_id_fk'];
					$result['customer_full_name']  			= $row['customer_full_name'];
					$result['customer_phone']  				= $row['customer_phone'];
					$result['customer_phone2']  			= $row['customer_phone2'];
					$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['sell_item_notes']  			= $row['sell_item_notes'];
					$result['total_discount']  				= $row['total_discount'];
					$result['total_remained']  				= $row['total_invoice_price'] - $row['total_paid'];
					$result['total_length']  				= $row['total_length'];
					
					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 = 1");
						
					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);
					}
						
				}

				// we will load totals of all customer account

				$id = $result['customer_id_fk'];

				$res = $db->query("SELECT customer_id,
									  customer_full_name,
									  CONCAT(COALESCE(customer_phone,''),';', COALESCE(customer_phone2,''))	AS phones,  

									
									(

				          				COALESCE((SELECT sum(sell_item_detail_height * item_sell_price_per_meter) FROM sell_item_detail SID, sell_item S 
				          				LEFT JOIN invoice I ON (I.invoice_status <3  AND I.is_deleted = 0 )
								        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 AND I.customer_id_fk = C.customer_id),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 
				          				LEFT JOIN invoice I ON (I.invoice_status <3  AND I.is_deleted = 0 )
								        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 AND I.customer_id_fk = C.customer_id),0)
								    +
								   		COALESCE((SELECT sum(sell_item_detail_width * decorate_price_per_meter) FROM sell_item_detail SID, sell_item S 
				          				LEFT JOIN invoice I ON (I.invoice_status <3  AND I.is_deleted = 0 )
								        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 AND I.customer_id_fk = C.customer_id),0)

								    +
								    	COALESCE((SELECT sum(sell_item_quantity * item_sell_price_per_meter) FROM sell_item S  
										LEFT JOIN invoice I ON (I.invoice_status <3  AND I.is_deleted = 0 )
										WHERE I.invoice_id = S.invoice_id_fk AND  S.other_id_fk >0 AND S.is_deleted=0 AND I.customer_id_fk = C.customer_id),0)
									+

										COALESCE((SELECT sum(withdraw_amount) FROM capital_withdraw CW1 WHERE  CW1.is_deleted = 0  AND CW1.gived_to_id_fk = C.customer_id ),0)
									-
								 		COALESCE((SELECT sum(actual_discount) from sell_item 
						 					LEFT JOIN invoice I ON (I.invoice_status <3  AND I.is_deleted = 0 )
						 					where sell_item.invoice_id_fk = I.invoice_id AND sell_item.is_deleted=0 AND I.customer_id_fk = C.customer_id),0)
									
									) as total_invoice_price,



									(
										COALESCE((SELECT sum(paid_amount) FROM payments IP1 
       									
        								WHERE IP1.is_returned=0 AND IP1.is_deleted =0 AND IP1.customer_id_fk = C.customer_id AND IP1.payment_transaction ='Payment' ),0)
        							

        							) as total_Paid,

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

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



									FROM customer C 
									WHERE C.is_deleted=0 AND C.customer_id = $id");

				while ($row = $res->fetch_assoc()) {
				   
					
					$result['total_invoice_price_all']  = $row['total_invoice_price'];
					$result['total_Paid_all']  			= $row['total_Paid'];
					$result['total_payment_discount'] 	= $row['total_payment_discount'];
					$result['total_payment_return']  	= $row['total_payment_return'];
					$result['total_remained_all']  		= $row['total_invoice_price'] - $row['total_Paid'] - $row['total_payment_discount'] + $row['total_payment_return'];
					

					array_push($op['account'],$result);
						
				}	


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



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

			$id = $_POST['id'];


			$res = $db->query("SELECT invoice_id, 
									  invoice_number, 
									  invoice_date, 
									  invoice_status, 
									  invoice_usd_rate, 
									  invoice_currency,
									  invoice_total_price_actual, 
									  invoice_note,
									  I.invoice_type ,
									  is_single,
									  pay_terms,
									  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,
									  payment_id,
									  paid_amount,
									  paid_date,
									 
									  turkish_code,

									  CONCAT(COALESCE(erbil_code,''), COALESCE(other_name,'')) AS product_name,

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

									 (SELECT count(product_id_fk) FROM sell_item SI WHERE I.invoice_id = SI.invoice_id_fk AND SI.is_deleted =0 AND SI.other_id_fk =0) AS total_product_cnt,

									 (SELECT count(product_id_fk) FROM sell_item SI WHERE I.invoice_id = SI.invoice_id_fk AND SI.is_deleted =0 AND SI.other_id_fk >0) AS total_other_cnt,

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

									(SELECT sum(actual_discount) FROM sell_item SI WHERE I.invoice_id = SI.invoice_id_fk AND SI.is_deleted =0) AS total_discount,

									(SELECT sum(sell_item_detail_height) FROM sell_item_detail SID WHERE SID.invoice_id_fk = '$id' AND SID.sell_item_id_fk=S.sell_item_id AND part_status=0 AND SID.is_deleted = 0) AS total_length

								FROM invoice I
							 		LEFT JOIN sell_item S ON (S.invoice_id_fk = I.invoice_id AND S.is_deleted=0)
							 		LEFT JOIN payments PY ON (PY.invoice_id_fk = I.invoice_id  AND PY.is_deleted=0 AND PY.payment_transaction = 'Payment')
							 		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_id_fk']  				= $row['customer_id_fk'];
					$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['payment_id']  					= $row['payment_id'];
					$result['paid_amount']  				= $row['paid_amount'];
					$result['paid_date']  					= $row['paid_date'];
					// $result['pay_sell_item_id_fk']  		= $row['pay_sell_item_id_fk'];
					$result['invoice_type']  				= $row['invoice_type'];
					$result['is_single']  					= $row['is_single'];
					$result['pay_terms']  					= $row['pay_terms'];
					$result['item_type_id_fk']  			= $row['item_type_id_fk'];
					$result['turkish_code']  				= $row['turkish_code'];
					$result['total_product_cnt']  			= $row['total_product_cnt'];
					$result['total_other_cnt']  			= $row['total_other_cnt'];
					
					array_push($op['invoice'],$result);


					$res1 = $db->query("SELECT sell_item_detail_id, 
											   sell_item_detail_height, 
											   sell_item_detail_width, 
											   is_part_sold, 
											   SID.damage_length, 
											   part_status,
											   invoice_id_fk,
											   sell_item_id_fk,
											   SID.purchase_item_detail_id_fk,

											   item_length,
											  	stock_id_fk, 
											  	
											  	item_status,
											  	
											  	(COALESCE(item_length,0) 
											  	- 
											  	(COALESCE(PID.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 sell_item_detail SID
										LEFT JOIN purchase_item_detail PID ON (PID.purchase_item_detail_id = SID.purchase_item_detail_id_fk)
									 	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'];
						$result1['purchase_item_detail_id_fk'] = $row1['purchase_item_detail_id_fk'];
						$result1['item_length'] = $row1['item_length'];
						$result1['stock_id_fk'] = $row1['stock_id_fk'];
						$result1['item_status'] = $row1['item_status'];
						$result1['remained_length'] = $row1['remained_length'];
						
						array_push($op['details'],$result1);
					}
						
				}		
				echo json_encode($op);
				exit;
			}else{
				echo $db->error;
				exit;
			}
		}


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

			$id = $_POST['id'];


			$res = $db->query("SELECT invoice_id, 
																invoice_number, 
																invoice_date, 
																invoice_status, 
															  invoice_usd_rate, 
															  invoice_currency,
															  invoice_total_price_actual, 
															  invoice_note, 
															  sell_item_quantity, 
															  I.customer_id_fk, 
															  sell_discount, 
															  sell_discount_type, 
															  actual_discount,
															  item_sell_price_per_meter,
															  decorate_price_per_meter, 
															  is_paid,
															  sell_item_notes, 
															  S.item_type_id_fk, 
															  other_id_fk, 
															  S.product_id_fk, 
															  sell_item_id,
															  customer_full_name,
															  customer_email,
															  customer_phone,
															  customer_address,
															  invoice_payment_id,
																pay_usd_rate,
																pay_currency,
																paid_amount,
																paid_date,
																next_payment_date,
																next_payment_amount,
																payment_note, 
																CONCAT(COALESCE(erbil_code,''), COALESCE(other_name,'')) AS product_name,
																(SELECT sum(paid_amount) FROM invoice_payment IP WHERE I.invoice_id = IP.invoice_id_fk AND IP.is_returned=0 AND IP.is_deleted =0) AS total_paid,

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

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

												FROM invoice I
													LEFT JOIN invoice_payment IPP ON (I.invoice_id = IPP.invoice_id_fk AND IPP.is_returned=0 AND IPP.is_deleted =0)
											 		LEFT JOIN sell_item S ON (S.sell_item_id = IPP.sell_item_id_fk AND S.is_deleted=0)
											 		LEFT JOIN others O ON (S.other_id_fk = O.other_id AND S.other_id_fk >0)
													LEFT JOIN product P ON (S.product_id_fk = P.product_id )
													LEFT JOIN customer C ON (I.customer_id_fk = C.customer_id )
											 	WHERE I.invoice_id='$id'  AND I.is_deleted = 0");
			if ($res) {
				//$row = $res->fetch_all();
				//echo json_encode($row);

				$op  = array('invoice'=>array(),'details'=>array());
				while ($row = $res->fetch_assoc()) {
				   
					$result['invoice_id']  								= $row['invoice_id'];
					$result['invoice_number']  						= $row['invoice_number'];
					$result['invoice_date']  							= date('Y-m-d',strtotime($row['invoice_date'])) ;
					$result['invoice_status']  						= $row['invoice_status'];
					$result['invoice_usd_rate']  					= $row['invoice_usd_rate'];
					$result['invoice_currency']  					= $row['invoice_currency'];
					$result['invoice_total_price_actual'] = $row['total_invoice_price'];
					$result['invoice_note']  							= $row['invoice_note'];
					$result['sell_item_quantity']  				= $row['sell_item_quantity'];
					$result['sell_discount']  						= $row['sell_discount'];
					$result['sell_discount_type']  				= $row['sell_discount_type'];
					$result['actual_discount']  					= $row['actual_discount'];
					$result['item_sell_price_per_meter']  = $row['item_sell_price_per_meter'];
					$result['decorate_price_per_meter']  	= $row['decorate_price_per_meter'];
					$result['is_paid']  									= $row['is_paid'];
					$result['sell_item_notes']  					= $row['sell_item_notes'];
					$result['product_name']  							= $row['product_name'];
					$result['customer_full_name']  				= $row['customer_full_name'];
					$result['customer_phone']  						= $row['customer_phone'];
					$result['customer_address']  					= $row['customer_address'];
					$result['total_paid']  								= $row['total_paid'];
					$result['product_id_fk']  						= $row['product_id_fk'];
					$result['other_id_fk']  							= $row['other_id_fk'];
					$result['sell_item_id']  							= $row['sell_item_id'];
					$result['total_discount']  						= $row['total_discount'];
					$result['total_remained']  						= $row['total_invoice_price'] - $row['total_paid'];
					$result['total_length']  							= $row['total_length'];
					$result['invoice_payment_id']  				= $row['invoice_payment_id'];
					$result['pay_usd_rate']  							= $row['pay_usd_rate'];
					$result['pay_currency']  							= $row['pay_currency'];
					$result['paid_amount']  							= $row['paid_amount'];
					$result['paid_date']  								= date('Y-m-d',strtotime($row['paid_date']));
					$result['next_payment_date']  				= $row['next_payment_date'];
					$result['next_payment_amount']  			= $row['next_payment_amount'];
					$result['payment_note']  							= $row['payment_note'];
					
						array_push($op['invoice'],$result);
						
				}		
				echo json_encode($op);
				exit;
			}else{
				echo 0;
				exit;
			}
		}


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

			$id = $_POST['id'];

			$res = $db->query("SELECT customer_id,
									  customer_full_name,
									  CONCAT(COALESCE(customer_phone,''),';', COALESCE(customer_phone2,''))	AS phones,  

									
									(

				          			COALESCE((SELECT sum(invoice_total_price_actual) FROM 
				          				 invoice I 
								        WHERE  I.customer_id_fk = C.customer_id AND I.invoice_status <3  AND I.is_deleted = 0),0)
									
								        +
										COALESCE((SELECT sum(withdraw_amount) FROM capital_withdraw CW1 WHERE  CW1.is_deleted = 0  AND CW1.gived_to_id_fk = C.customer_id ),0)
									
									) as total_invoice_price,



									(
										COALESCE((SELECT sum(paid_amount) FROM payments IP1 
       									
        								WHERE IP1.is_returned=0 AND IP1.is_deleted =0 AND IP1.customer_id_fk = C.customer_id AND IP1.payment_transaction ='Payment' ),0)
        							

        							) as total_Paid,

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

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



								FROM customer C 
								WHERE C.is_deleted=0 AND C.customer_id = $id");

		
			if ($res) {
				//$row = $res->fetch_all();
				//echo json_encode($row);

				$op  = array('invoice'=>array(),'details'=>array());
				while ($row = $res->fetch_assoc()) {
				   
					$result['customer_id']  			= $row['customer_id'];
					$result['customer_full_name']  		= $row['customer_full_name'];
					$result['phones']  					= $row['phones'];
					$result['total_invoice_price']  	= $row['total_invoice_price'];
					$result['total_Paid']  				= $row['total_Paid'];
					$result['total_payment_discount'] 	= $row['total_payment_discount'];
					$result['total_payment_return']  	= $row['total_payment_return'];
					$result['total_remained']  			= $row['total_invoice_price'] - $row['total_Paid'] - $row['total_payment_discount'] + $row['total_payment_return'];
					

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


		if ($_POST['type']=="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['cust_id'];
			$total_paid 			= $_POST['payment_amount'];
			$paid_date 				= $_POST['paid_date'];
			$notes 					= $_POST['payment_note'];
			$pay_type 				= $_POST['pay_type'];
			$item_type_id_fk 		= $_POST['item_type_id_fk'];

			$lastaction 			= "Add New Payment";
			$invoice_currency		= 1; //USD
			$reg_date 				= date("Y-m-d h:i:s", time());
			
			$next_payment_date 		='';
			$next_payment_amount 	= '';
			$rnd_no					=rand(100000000,1000000000).uniqid();
			$dd 	  				= date("y", time());

			
			$successfully 			= false;

			if(!isset($_POST['cust_id']) || $_POST['cust_id'] == '' || $_POST['cust_id'] < 1){
				$op['is_success'] = false;
				$op['data'] 	  = "هناك خطأ : لم يتم اضافة الدفوعات الرجاء اعادة تحميل الصفحة والمحاولة من جديد";
				echo json_encode($op);
				exit;
			}
			
			if($total_paid > 0){

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

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

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

				$sql_p ="INSERT INTO payments(payment_number,
		 									  pay_usd_rate,
		 									  pay_currency,
		 									  paid_amount,
		 									  paid_date,
		 									  payment_note,
		 									  payment_transaction,
		 									  pay_sequence_no,
		 									  invoice_id_fk,
		 									  customer_id_fk,
		 									  item_type_id_fk,
		 									  invoice_type,
		 									  created_at,
		 									  updated_at,
		 									  created_by,
		 									  last_action,
		 									  last_action_by)
		 							   VALUES('$payment_number',
		 							   		  '".$_SESSION['USD_TO_IQD']."',
		 							    	  '$invoice_currency',
		 							    	  '$total_paid',
		 							    	  '$paid_date',
		 							    	  '$payment_note',
		 							    	  '$payment_transaction',
		 							    	  '$pay_sequence_no',
		 							    	  '',
		 							    	  '$id',
		 							    	  '$item_type_id_fk',
		 							    	  '$pay_type',
		 							    	  '$reg_date',
		 							    	  '$reg_date',
		 							    	  '".$_SESSION['user_id']."',
		 							    	  '$lastaction',
		 							    	  '".$_SESSION['user_id']."')";
				$res_P = $db->query($sql_p);
				if (!$res_P){
					$db->rollback();
					$op['is_success'] = false;
					$op['data'] 	  = "هناك خطأ : لم يتم اضافة الدفوعات الرجاء اعادة تحميل الصفحة والمحاولة من جديد";
					echo json_encode($op);
					exit;
				}else{
					$db->commit();
					$op['is_success'] = true;
					$op['data'] 	  = "تمت عملية الاضافة بنجاح";
					echo json_encode($op);
					exit;
				}
			} // if total_paid >0

			
		}


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

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


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

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

			if($total_paid > 0){

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

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

					if ($res){

										// we should update sell_item pay status 

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

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

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

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

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

										$total_product_price = 0;

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

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

										}

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

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

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

										}else{
											$is_paid_sell = 0;

										}

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

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

												// update invoice status

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

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

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

												$res3 = $db->query($update_qry1);
												if ($res3) {

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

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



					}else{
						// $op['data'] 	  =$db->error;

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

			}else{

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


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

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


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



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

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

					if ($res){

										// we should update sell_item pay status 

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

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

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

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

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

										$total_product_price = 0;

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

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

										}

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

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

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

										}else{
											$is_paid_sell = 0;

										}

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

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

												// update invoice status

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

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

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

												$res3 = $db->query($update_qry1);
												if ($res3) {

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

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



					}else{
						// $op['data'] 	  =$db->error;

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

			
		}


		



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

			$p_id 		= $_POST['p_id'];
			$sell_id 	= $_POST['sell_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,
								  	sell_item_id_fk,
								  	sell_item_detail_height,
								  	(COALESCE(item_length,0) 
								  	- 
								  	(COALESCE(PID.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)
									 LEFT JOIN sell_item_detail SID  ON (PID.purchase_item_detail_id = SID.purchase_item_detail_id_fk AND SID.is_deleted=0 AND SID.sell_item_id_fk =$sell_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 || PID.purchase_item_detail_id = SID.purchase_item_detail_id_fk  ) 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'];
						$result[6]  = $row['sell_item_id_fk'];
						$result[7]  = $row['sell_item_detail_height'];
						
						
						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_limited_old") {

			$product_id						= $_POST["product_id"];
			$quantity						= $_POST["quantity"];
			$sell_item_id					= $_POST["sell_item_id"];
			$sell_price						= $_POST["sell_price"];
			$discount						= $_POST["discount"];
			$discount_type					= $_POST["discount_type"];
			$discount_actual				= $_POST["discount_actual"];
			$sell_item_note					= $_POST["sell_item_note"];
			$row_price						= $_POST["row_price"];
			// $row_payments					= $_POST["row_payments"];
			// $payments_id 					= $_POST["payments_id"];
			

			$invoice_type					= addslashes($_POST["invoice_type"]);
			$customer_id_fk					= addslashes($_POST["customer_id_fk"]);
			$pay_term 						= addslashes($_POST["pay_term"]);
			$invoice_date					= addslashes($_POST["invoice_date"]);
			$invoice_note			  		= addslashes($_POST["invoice_note"]);
			$invoice_id						= addslashes($_POST["invoice_id"]);
			$is_single	  					= addslashes($_POST["is_single"]);
			$invoice_number	  				= addslashes($_POST["invoice_number"]);
			

			

			$total_invoice					= addslashes($_POST["total_invoice"]);
			$total_paid						= addslashes($_POST["total_paid"]);
			
			$payment_id 					= addslashes($_POST["payment_id"]);

			$is_offer 						= 0; // use it later
			$invoice_currency				= 1; //USD
			$term_and_conditions 			= "";

			$rnd_no							=rand(100000000,1000000000).uniqid();

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

			
			// extract array VALUES

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

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

				$total_rows_product_price = $total_rows_product_price + $row_price[$i];
			}

			// if($no_others == 0){

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

			// 		if($other_id_fk[$i] > 0){						
			// 			$total_rows_others_price = $total_rows_others_price + $other_row_price[$i];
			// 		}
			// 	}
			// }

			$total_invoice_price_calc 	= $total_rows_others_price + $total_rows_product_price;


			$db->autocommit(FALSE); // to rollback insertion if update fail
			
			$sql ="UPDATE invoice SET 	invoice_number 				= '$invoice_number',
									    invoice_date 				= '$invoice_date',
									    invoice_status 				= '$is_paid',
									    invoice_total_price_actual 	= '$total_invoice_price_calc', 
									    invoice_note 				= '$invoice_note', 
									    invoice_type				= '$invoice_type', 
									    term_and_conditions			= '$term_and_conditions', 
									    pay_terms					= '$pay_term', 
									    is_single					= '$is_single', 
									    updated_at 					= '$reg_date', 
									    last_action 				= '$lastaction', 
									    last_action_by				= '".$_SESSION['user_id']."'
										WHERE invoice_id = '$invoice_id'";
						
			$res = $db->query($sql);
			if ($res) {
				
				$invoice_inserted_id = $invoice_id;

				
				$lastaction = "Add New Sell Product";
				$check_all_inserted_items = false;

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


				for ($i=0;$i<count($product_id);$i++){
					
					$item_buy_price_per_meter 	='';
					$decorate_price 			='';
					$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;
					// }

					$sql2 ="UPDATE sell_item SET sell_item_quantity = '$quantity[$i]', 
												sell_discount 	   = '$discount[$i]',
												sell_discount_type = '$discount_type[$i]',
												actual_discount	   = '$discount_actual[$i]',
												item_buy_price_per_meter = '$item_buy_price_per_meter',
												item_sell_price_per_meter = '$sell_price[$i]',
												decorate_price_per_meter  = '$decorate_price',
												
												is_paid 			= '$is_paid',
												
												sell_item_notes     = '$sell_item_note[$i]',
												
												updated_at 			= '$reg_date', 
									    		last_action 		= '$lastaction', 
									    		last_action_by		= '".$_SESSION['user_id']."'
 												
 												WHERE sell_item_id = '$sell_item_id[$i]'";
					$res2 = $db->query($sql2);

					if ($res2) {
						

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

						// 	if($is_paid > 0 ){

						// 		if($payments_id[$i] > 0 && $payments_id[$i] !=null){ // update

						// 			$sql_p ="UPDATE invoice_payment SET 									paid_amount 	= '$row_payments[$i]',
						// 							paid_date 		= '$invoice_date',
						// 							updated_at 		= '$reg_date', 
						// 						    last_action 	= '$lastaction', 
						// 						    last_action_by	= '".$_SESSION['user_id']."'
						// 							WHERE invoice_payment_id = '$payments_id[$i]'";
								

						// 		}else{

						// 			$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,
						// 											  group_no,
						// 											  created_by,
						// 											  last_action,
						// 											  last_action_by)
						// 									 VALUES('".$_SESSION['USD_TO_IQD']."',
						// 									 		 '$invoice_currency',
						// 									 		 '$row_payments[$i]',
						// 									 		 '$reg_date',
						// 									 		 '$sell_item_id[$i]',
						// 									 		 '$invoice_inserted_id',
						// 									 		 '$customer_id_fk',
						// 									 		 '$rnd_no',
						// 									 		 '".$_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



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

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

				} // for i

				

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


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

			
					if($payment_id > 0 && $payment_id !=null){ // update

						$sql_p ="UPDATE payments SET 										
										paid_amount 	= '$total_paid',
										paid_date 		= '$invoice_date',
										updated_at 		= '$reg_date', 
									    last_action 	= '$lastaction', 
									    last_action_by	= '".$_SESSION['user_id']."'
										WHERE payment_id = '$payment_id'";
						
						$res_P = $db->query($sql_p);
						if (!$res_P){
							$db->rollback();
							$op['is_success'] = false;
							$op['data'] 	  = "هناك خطأ : لم يتم اضافة الدفوعات الرجاء اعادة تحميل الصفحة والمحاولة من جديد";
							echo json_encode($op);
							exit;
						}

					}else{

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

						$dd = date("y", time());
						
						$payment_number = "PAY".$dd.($pay_sequence_no);
						$payment_transaction = 'Payment';
						$payment_note = "Add Payment for Invoice : ".$invoice_number;

						$sql_p ="INSERT INTO payments(payment_number,
				 									  pay_usd_rate,
				 									  pay_currency,
				 									  paid_amount,
				 									  paid_date,
				 									  payment_note,
				 									  payment_transaction,
				 									  pay_sequence_no,
				 									  invoice_id_fk,
				 									  customer_id_fk,
				 									  invoice_type,
				 									  created_by,
				 									  created_at,
				 									  updated_at,
				 									  last_action,
				 									  last_action_by)
				 							   VALUES('$payment_number',
				 							   		  '".$_SESSION['USD_TO_IQD']."',
				 							    	  '$invoice_currency',
				 							    	  '$total_paid',
				 							    	  '$invoice_date',
				 							    	  '$payment_note',
				 							    	  '$payment_transaction',
				 							    	  '$pay_sequence_no',
				 							    	  '$invoice_id',
				 							    	  '$customer_id_fk',
				 							    	  'Raw',
				 							    	  '".$_SESSION['user_id']."',
				 							    	  '$reg_date',
				 							    	  '$reg_date',
				 							    	  '$lastaction',
				 							    	  '".$_SESSION['user_id']."')";
						$res_P = $db->query($sql_p);
						if (!$res_P){
							$db->rollback();
							$op['is_success'] = false;
							$op['data'] 	  = "هناك خطأ : لم يتم اضافة الدفوعات الرجاء اعادة تحميل الصفحة والمحاولة من جديد";
							echo json_encode($op);
							exit;
						}
					

					}

					
				


			}

			// end payment insertion


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


		function delete_invoice($id){
				/////////////////////////////////////////////////////////////////////
				////////////////////////////////////////////////////////////////////
				////////////////////// Delete Invoice and Insert it again /////////
				//////////////////////////////////////////////////////////////////
				/////////////////////////////////////////////////////////////////



				// update Product table for sell price and avarage price

				$lastaction = "Delete Invoice and  All its Payments  ";
				$reg_date = date("Y-m-d h:i:s", time());
				require("db_connect.php");
				
			
				$lastaction = "Delete Invoice and  All its Sell Items  ";
				$sql2 ="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";
				$res2 = $db->query($sql2);

				if ($res2) {

					$lastaction = "Delete Invoice and  All its Sell Items details  ";
					$sql3 ="UPDATE sell_item_detail SET is_deleted = '1',
						 deleted_at='$reg_date', last_action='$lastaction', last_action_by='".$_SESSION['user_id']."'  WHERE invoice_id_fk = $id";
					$res3 = $db->query($sql3);

					if ($res3) {
						// we should update item_status in purchase details table and we need to get all purchase_item_detail_id_fk from sell_item_detail table to know which rolls we should change its status

						$res4 = $db->query("SELECT * FROM sell_item_detail WHERE invoice_id_fk ='$id' AND purchase_item_detail_id_fk > 0");

						
						while ($row = $res4->fetch_assoc()) {
							$roll_id_fk 	= $row['purchase_item_detail_id_fk'];
							$sold_length 	= $row['sell_item_detail_height'];
							$is_part_sold 	= $row['is_part_sold'];
							

							$res5 = $db->query("
								SELECT 	purchase_item_detail_id,
										item_length,
										item_status,
										sold_length, 
										damage_length, 
										used_length, 
										stock_type,
										purchase_id_fk,
										purchase_item_id_fk,
										product_id_fk,
										stock_id_fk,
									(	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.is_deleted=0 AND PID.purchase_item_detail_id = $roll_id_fk");

							$row1 = $res5->fetch_assoc();

							$item_status 		= $row1['item_status'];
							$item_length 		= $row1['item_length'];
							$damage_length 		= $row1['damage_length'];
							$used_length 		= $row1['used_length'];
							$stock_type 		= $row1['stock_type'];

							$remained_length 	= $row1['remained_length'];
							
							// just for insert return part to inhired its previous values
							$purchase_id_fk 			= $row1['purchase_id_fk'];
							$purchase_item_id_fk 		= $row1['purchase_item_id_fk'];
							$product_id_fk 				= $row1['product_id_fk'];
							$stock_id_fk 				= $row1['stock_id_fk'];
							$purchase_item_detail_id 	= $row1['purchase_item_detail_id'];
							
							$complete_roll = 0; // Not complete

							switch ($item_status) {

								case 1:
									if ($sold_length >=  $item_length) {
							 			
							 			$previous_status = 2 ; // availabel No damage in production
										
							        }else{
							          	if (($sold_length + $damage_length) >= $item_length) {
							          		
							 				$previous_status = 8 ; // availabel with damage in store

							          	}else{
							          		$previous_status = 5 ; // Partially sold
							          	}

							          	
							        }
									break;

								case 5:
							 		if ($remained_length >=  $item_length) {
							 			
							 			$previous_status = 2 ; // availabel No damage in production
							          	
							        }else{
							          	if (($remained_length + $damage_length) >=  $item_length) {
							          		
							 				$previous_status = 8 ; // availabel with damage in store
								 			
							          	}else{
							          		$previous_status = 5 ; // Partially sold
							          	}

							          	
							        }
									break;


								case 10:
									if (($sold_length + $used_length + $damage_length) >=  $item_length) {
										 			
						 				$previous_status = 3 ; // in production used
										 			
							        }else{
							          	
						          		$previous_status = 9 ; // used in production + Partially sold
							          	
							        }
									break;


							 	case 9:
							 		if (($sold_length + $remained_length ) >=  $item_length) {
										 			
						 				$previous_status = 3 ; // in production used
										 			
							         }else{
							          	
						          		$previous_status = 9 ; // used in production + Partially sold
							          	
							        }
								 	break;


							}
							
							// if($is_part_sold == 0){ // complete roll

								$sql6 ="UPDATE purchase_item_detail SET  
												item_status = '7',
												previous_status = '$previous_status',
												is_part = '0',
				 					 			last_action='$lastaction',
				 					 			last_action_by='".$_SESSION['user_id']."'  
						 				WHERE purchase_item_detail_id = $roll_id_fk";

							// }
							// else{

							// 	$sql6 ="INSERT INTO purchase_item_detail 
							// 							 (item_length,
							// 							  item_status,
							// 							  previous_status,
							// 							  is_part,
							// 							  purchase_item_detail_id_fk,
							// 							  purchase_id_fk,
							// 							  purchase_item_id_fk,
							// 							  product_id_fk,
							// 							  stock_id_fk)
							// 					VALUES ('$sold_length',
							// 							'7',
							// 							'$previous_status',
							// 							'1',
							// 							'$purchase_item_detail_id',
							// 							'$purchase_id_fk',
							// 							'$purchase_item_id_fk',
							// 							'$product_id_fk',
							// 							'$stock_id_fk')";

							// }

							
							$res6 = $db->query($sql6);

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

						} // while
						
						// if the res4 were 0 rows that mean all sold were curtain and other and there is no rolls so we won't update purchase status in purchase table									
						
					}else{

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

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


				
				

				//////////////////////////////////////////////////////////////////
				/////////////////////////////////////////////////////////////////
				/////////////////////// End Deleting Invoice ///////////////////
				////////////////////////////////////////////////////////////////
				///////////////////////////////////////////////////////////////
				
		}

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

			$product_id						= $_POST["product_id"];
			$quantity						= $_POST["quantity"];
			$sell_item_id					= $_POST["sell_item_id"];
			$sell_price						= $_POST["sell_price"];
			$discount						= $_POST["discount"];
			$discount_type					= $_POST["discount_type"];
			$discount_actual				= $_POST["discount_actual"];
			$sell_item_note					= $_POST["sell_item_note"];
			$row_price						= $_POST["row_price"];
			// $row_payments					= $_POST["row_payments"];
			// $payments_id 					= $_POST["payments_id"];

			$item_length					= $_POST["item_length"];
			$roll_id_fk						= $_POST["roll_id_fk"];
			$sell_item_note					= $_POST["sell_item_note"];
			$row_price						= $_POST["row_price"];
			$item_type 						= $_POST["item_type"];
			$original_length				= $_POST["original_length"];




			

			$invoice_type					= addslashes($_POST["invoice_type"]);
			$customer_id_fk					= addslashes($_POST["customer_id_fk"]);
			$pay_term 						= addslashes($_POST["pay_term"]);
			$invoice_date					= addslashes($_POST["invoice_date"]);
			$invoice_note			  		= addslashes($_POST["invoice_note"]);
			$invoice_id						= addslashes($_POST["invoice_id"]);
			$is_single	  					= addslashes($_POST["is_single"]);
			$invoice_number	  				= addslashes($_POST["invoice_number"]);
			


			$other_id_fk					= $_POST["other_id_fk"];
			$other_quantity_1				= $_POST["other_quantity_1"];
			$other_sell_price				= $_POST["other_sell_price"];
			$other_discount					= $_POST["other_discount"];
			$other_discount_type			= $_POST["other_discount_type"];
			$other_discount_actual			= $_POST["other_discount_actual"];
			$other_note						= $_POST["other_note"];
			$other_row_price				= $_POST["other_row_price"];
			

			$total_invoice					= addslashes($_POST["total_invoice"]);
			$total_paid						= addslashes($_POST["total_paid"]);
			
			$payment_id 					= addslashes($_POST["payment_id"]);

			$is_offer 						= 0; // use it later
			$invoice_currency				= 1; //USD
			$term_and_conditions 			= "";

			$rnd_no							=rand(100000000,1000000000).uniqid();

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

			
			// extract array VALUES

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

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

			   $total_rows_product_price = $total_rows_product_price + $row_price[$i];
			}

			

			$total_invoice_price_calc = $total_rows_others_price + $total_rows_product_price;


			$db->autocommit(FALSE); // to rollback insertion if update fail
			
			$sql ="UPDATE invoice SET 	
						invoice_number 				= '$invoice_number',
					    invoice_date 				= '$invoice_date',
					    invoice_status 				= '$is_paid',
					    invoice_total_price_actual 	= '$total_invoice_price_calc', 
					    invoice_note 				= '$invoice_note', 
					    invoice_type				= '$invoice_type', 
					    term_and_conditions			= '$term_and_conditions', 
					    pay_terms					= '$pay_term', 
					    is_single					= '$is_single', 
					    updated_at 					= '$reg_date', 
					    last_action 				= '$lastaction', 
					    last_action_by				= '".$_SESSION['user_id']."'
						WHERE invoice_id = '$invoice_id'";
						
			$res = $db->query($sql);

			if ($res) {


				
				delete_invoice($invoice_id);

				
				$invoice_inserted_id = $invoice_id;


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

					// insert Others product
					if($other_id_fk[$i] > 0){

						$lastaction = "Add New Others";
						

						$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[$i]',
			 								 				'$other_discount[$i]',
			 								 				'$other_discount_type[$i]',
			 								 				'$other_discount_actual[$i]',
			 								 				'',
			 								 				'$other_sell_price[$i]',
			 								 				'',
			 								 				'',
			 								 				'$invoice_date',
			 								 				'$is_paid',
			 								 				'$invoice_type',
			 								 				'$other_note[$i]',
			 								 				'0',
			 								 				'$other_id_fk[$i]',
			 								 				'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;
						}


					}
				} // for i other

				// 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 			= '';
					$barcode 					= '';

				

					$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,
													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]',
	 								 				'$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 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){ // 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 = '';
							}

							

							$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){
								// 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==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 7:
							       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 ){

									$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 ){



					// insert into payment table the paid price
						$lastaction = "Add New Payment";



					if($total_paid > 0){

					

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

						$dd = date("y", time());
						
						$payment_number = "PAY".$dd.($pay_sequence_no);
						$payment_transaction = 'Payment';
						$payment_note = "Add Payment for Invoice : ".$invoice_number;

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


					}


						

						// end payment insertion




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


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

			
				if($payment_id > 0 && $payment_id !=null){ // update

					$sql_p ="UPDATE payments SET 										
									paid_amount 	= '$total_paid',
									paid_date 		= '$invoice_date',
									updated_at 		= '$reg_date', 
								    last_action 	= '$lastaction', 
								    last_action_by	= '".$_SESSION['user_id']."'
									WHERE payment_id = '$payment_id'";
					
					$res_P = $db->query($sql_p);
					if (!$res_P){
						$db->rollback();
						$op['is_success'] = false;
						$op['data'] 	  = "هناك خطأ : لم يتم اضافة الدفوعات الرجاء اعادة تحميل الصفحة والمحاولة من جديد";
						echo json_encode($op);
						exit;
					}

				}else{

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

					$dd = date("y", time());
					
					$payment_number = "PAY".$dd.($pay_sequence_no);
					$payment_transaction = 'Payment';
					$payment_note = "Add Payment for Invoice : ".$invoice_number;

					$sql_p ="INSERT INTO payments(payment_number,
			 									  pay_usd_rate,
			 									  pay_currency,
			 									  paid_amount,
			 									  paid_date,
			 									  payment_note,
			 									  payment_transaction,
			 									  pay_sequence_no,
			 									  invoice_id_fk,
			 									  customer_id_fk,
			 									  invoice_type,
			 									  created_by,
			 									  created_at,
			 									  updated_at,
			 									  last_action,
			 									  last_action_by)
			 							   VALUES('$payment_number',
			 							   		  '".$_SESSION['USD_TO_IQD']."',
			 							    	  '$invoice_currency',
			 							    	  '$total_paid',
			 							    	  '$invoice_date',
			 							    	  '$payment_note',
			 							    	  '$payment_transaction',
			 							    	  '$pay_sequence_no',
			 							    	  '$invoice_id',
			 							    	  '$customer_id_fk',
			 							    	  'Raw',
			 							    	  '".$_SESSION['user_id']."',
			 							    	  '$reg_date',
			 							    	  '$reg_date',
			 							    	  '$lastaction',
			 							    	  '".$_SESSION['user_id']."')";
					$res_P = $db->query($sql_p);
					if (!$res_P){
						$db->rollback();
						$op['is_success'] = false;
						$op['data'] 	  = "هناك خطأ : لم يتم اضافة الدفوعات الرجاء اعادة تحميل الصفحة والمحاولة من جديد";
						echo json_encode($op);
						exit;
					}
				

				}

			}

			// end payment insertion


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


		if ($_POST['type']=="delete1") {
			$id	 = addslashes($_POST["obj_id"]);

			$reg_date = date("Y-m-d h:i:s", time());
			$lastaction = "Delete Invoice";
			// $op  = array('is_success'=>false,'data'=>'');

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

				// update Product table for sell price and avarage price

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

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

					if ($res2) {

						$lastaction = "Delete Invoice and  All its Sell Items details  ";
						$sql3 ="UPDATE sell_item_detail SET is_deleted = '1',
							 deleted_at='$reg_date', last_action='$lastaction', last_action_by='".$_SESSION['user_id']."'  WHERE invoice_id_fk = $id";
						$res3 = $db->query($sql3);

						if ($res3) {
							// we should update item_status in purchase details table and we need to get all purchase_item_detail_id_fk from sell_item_detail table to know which rolls we should change its status

							$res4 = $db->query("SELECT * FROM sell_item_detail WHERE invoice_id_fk ='$id' AND purchase_item_detail_id_fk > 0");

							
							while ($row = $res4->fetch_assoc()) {
								$roll_id_fk 	= $row['purchase_item_detail_id_fk'];
								$sold_length 	= $row['sell_item_detail_height'];
								

								$res5 = $db->query("
									SELECT purchase_item_detail_id, item_length, item_status, sold_length, damage_length, used_length, stock_type,
										(	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.is_deleted=0 AND PID.purchase_item_detail_id = $roll_id_fk");

								$row1 = $res5->fetch_assoc();

								$item_status 		= $row1['item_status'];
								$item_length 		= $row1['item_length'];
								$damage_length 	= $row1['damage_length'];
								$used_length 		= $row1['used_length'];
								$stock_type 		= $row1['stock_type'];

								$remained_length = $row1['remained_length'];
								
								switch ($item_status) {

									case 1:
										if ($sold_length >=  $item_length) {
								 			if ($stock_type == 0) { // store stock

								 				$item_status = 0 ; // availabel No damage in store
								 			}else{
								 				$item_status = 2 ; // availabel No damage in production
								 			}
								          	
								        }else{
								          	if (($sold_length + $damage_length) >=  $item_length) {
								          		if ($stock_type == 0) { // store stock

									 				$item_status = 8 ; // availabel with damage in store
									 			}else{
									 				$item_status = 11 ; // availabel with damage in production
									 			}
								          	}else{
								          		$item_status = 5 ; // Partially sold
								          	}

								          	
								        }
										break;

									case 5:
								 		if ($remained_length >=  $item_length) {
								 			if ($stock_type == 0) { // store stock

								 				$item_status = 0 ; // availabel No damage in store
								 			}else{
								 				$item_status = 2 ; // availabel No damage in production
								 			}
								          	
								        }else{
								          	if (($remained_length + $damage_length) >=  $item_length) {
								          		if ($stock_type == 0) { // store stock

									 				$item_status = 8 ; // availabel with damage in store
									 			}else{
									 				$item_status = 11 ; // availabel with damage in production
									 			}
								          	}else{
								          		$item_status = 5 ; // Partially sold
								          	}

								          	
								        }
										break;


									case 10:
										if (($sold_length + $used_length + $damage_length) >=  $item_length) {
											 			
							 				$item_status = 3 ; // in production used
											 			
								        }else{
								          	
							          		$item_status = 9 ; // used in production + Partially sold
								          	
								        }
										break;


								 	case 9:
								 		if (($sold_length + $remained_length ) >=  $item_length) {
											 			
							 				$item_status = 3 ; // in production used
											 			
								         }else{
								          	
							          		$item_status = 9 ; // used in production + Partially sold
								          	
								        }
									 	break;


								}
								

								$sql6 ="UPDATE purchase_item_detail SET item_status = '$item_status',
							 					 last_action='$lastaction', last_action_by='".$_SESSION['user_id']."'  WHERE purchase_item_detail_id = $roll_id_fk";
								$res6 = $db->query($sql6);

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

							} // while
							
							// if the res4 were 0 rows that mean all sold were curtain and other and there is no rolls so we won't update purchase status in purchase table									
							
						}else{

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

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


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


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


		}


		if ($_POST['type']=="delete") {
			$id	 = addslashes($_POST["obj_id"]);

			$reg_date = date("Y-m-d h:i:s", time());
			$lastaction = "Delete Invoice";
			// $op  = array('is_success'=>false,'data'=>'');

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

				// update Product table for sell price and avarage price

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

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

					if ($res2) {

						$lastaction = "Delete Invoice and  All its Sell Items details  ";
						$sql3 ="UPDATE sell_item_detail SET is_deleted = '1',
							 deleted_at='$reg_date', last_action='$lastaction', last_action_by='".$_SESSION['user_id']."'  WHERE invoice_id_fk = $id";
						$res3 = $db->query($sql3);

						if ($res3) {
							// we should update item_status in purchase details table and we need to get all purchase_item_detail_id_fk from sell_item_detail table to know which rolls we should change its status

							$res4 = $db->query("SELECT * FROM sell_item_detail WHERE invoice_id_fk ='$id' AND purchase_item_detail_id_fk > 0");

							
							while ($row = $res4->fetch_assoc()) {
								$roll_id_fk 	= $row['purchase_item_detail_id_fk'];
								$sold_length 	= $row['sell_item_detail_height'];
								$is_part_sold 	= $row['is_part_sold'];
								

								$res5 = $db->query("
									SELECT 	purchase_item_detail_id,
											item_length,
											item_status,
											sold_length, 
											damage_length, 
											used_length, 
											stock_type,
											purchase_id_fk,
											purchase_item_id_fk,
											product_id_fk,
											stock_id_fk,
										(	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.is_deleted=0 AND PID.purchase_item_detail_id = $roll_id_fk");

								$row1 = $res5->fetch_assoc();

								$item_status 		= $row1['item_status'];
								$item_length 		= $row1['item_length'];
								$damage_length 		= $row1['damage_length'];
								$used_length 		= $row1['used_length'];
								$stock_type 		= $row1['stock_type'];

								$remained_length 	= $row1['remained_length'];
								
								// just for insert return part to inhired its previous values
								$purchase_id_fk 			= $row1['purchase_id_fk'];
								$purchase_item_id_fk 		= $row1['purchase_item_id_fk'];
								$product_id_fk 				= $row1['product_id_fk'];
								$stock_id_fk 				= $row1['stock_id_fk'];
								$purchase_item_detail_id 	= $row1['purchase_item_detail_id'];
								
								$complete_roll = 0; // Not complete

								switch ($item_status) {

									case 1:
										if ($sold_length >=  $item_length) {
								 			
								 			$previous_status = 2 ; // availabel No damage in production
											
								        }else{
								          	if (($sold_length + $damage_length) >= $item_length) {
								          		
								 				$previous_status = 8 ; // availabel with damage in store

								          	}else{
								          		$previous_status = 5 ; // Partially sold
								          	}

								          	
								        }
										break;

									case 5:
								 		if ($remained_length >=  $item_length) {
								 			
								 			$previous_status = 2 ; // availabel No damage in production
								          	
								        }else{
								          	if (($remained_length + $damage_length) >=  $item_length) {
								          		
								 				$previous_status = 8 ; // availabel with damage in store
									 			
								          	}else{
								          		$previous_status = 5 ; // Partially sold
								          	}

								          	
								        }
										break;


									case 10:
										if (($sold_length + $used_length + $damage_length) >=  $item_length) {
											 			
							 				$previous_status = 3 ; // in production used
											 			
								        }else{
								          	
							          		$previous_status = 9 ; // used in production + Partially sold
								          	
								        }
										break;


								 	case 9:
								 		if (($sold_length + $remained_length ) >=  $item_length) {
											 			
							 				$previous_status = 3 ; // in production used
											 			
								         }else{
								          	
							          		$previous_status = 9 ; // used in production + Partially sold
								          	
								        }
									 	break;


								}
								
								if($is_part_sold == 0){ // complete roll

									$sql6 ="UPDATE purchase_item_detail SET  
															item_status = '7',
															previous_status = '$previous_status',
															is_part = '0',
							 					 			last_action='$lastaction',
							 					 			last_action_by='".$_SESSION['user_id']."'  
							 				WHERE purchase_item_detail_id = $roll_id_fk";

								}else{

									$sql6 ="INSERT INTO purchase_item_detail 
															 (item_length,
															  item_status,
															  previous_status,
															  is_part,
															  purchase_item_detail_id_fk,
															  purchase_id_fk,
															  purchase_item_id_fk,
															  product_id_fk,
															  stock_id_fk)
													VALUES ('$sold_length',
															'7',
															'$previous_status',
															'1',
															'$purchase_item_detail_id',
															'$purchase_id_fk',
															'$purchase_item_id_fk',
															'$product_id_fk',
															'$stock_id_fk')";

								}

								
								$res6 = $db->query($sql6);

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

							} // while
							
							// if the res4 were 0 rows that mean all sold were curtain and other and there is no rolls so we won't update purchase status in purchase table									
							
						}else{

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

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


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


			$db->commit();
			$op['is_success'] = true;
			$op['data'] 	  = "تمت عملية الحذف بنجاح";
			echo json_encode($op);
			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"];
			$row_price						= $_POST["row_price"];
			// $row_payments					= $_POST["row_payments"];
			$item_type 						= $_POST["item_type"];
			$original_length				= $_POST["original_length"];


			$invoice_type					= addslashes($_POST["invoice_type"]);
			$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					= $_POST["other_id_fk"];
			$other_quantity_1				= $_POST["other_quantity_1"];
			$other_sell_price				= $_POST["other_sell_price"];
			$other_discount					= $_POST["other_discount"];
			$other_discount_type			= $_POST["other_discount_type"];
			$other_discount_actual			= $_POST["other_discount_actual"];
			$other_note						= $_POST["other_note"];
			$other_row_price				= $_POST["other_row_price"];
			// $other_pay						= $_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 = "";

			$rnd_no						=rand(100000000,1000000000).uniqid();

			$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("y", time());
			$invoice_number = "VR-".$dd."-".($last_invoice_id +1);
			// extract array VALUES

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

			$total_rows_product_price 	= 0;
			$total_rows_others_price 	= 0;
			$total_invoice_price_calc 	= 0;

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

				$total_rows_product_price = $total_rows_product_price + $row_price[$i];
			}

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

				if($other_id_fk[$i] > 0){						
					$total_rows_others_price = $total_rows_others_price + $other_row_price[$i];
				}
			}

			$total_invoice_price_calc 	= $total_rows_others_price + $total_rows_product_price;


			$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, 
									    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_price_calc',
									   '$invoice_note',
									   '$invoice_type',
									   '$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;

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

					// insert Others product
					if($other_id_fk[$i] > 0){

						$lastaction = "Add New Others";
						// if($other_pay[$i] > $other_row_price[$i] ){
						// 	$db->rollback();
						// 	$op['is_success'] = false;
						// 	$op['data'] 	  = "هناك خطأ : المبلغ المدفوع للمواد الاخرى اكبر من السعر الكلي للمادة, الرجاء التأكد من قيم الدفعات";
						// 	echo json_encode($op);
						// 	exit;
						// }elseif( $other_pay[$i] == $other_row_price[$i]){
						
						// 	$is_paid = 2;
						// }elseif($other_pay[$i] < $other_row_price[$i] && $other_pay[$i] >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[$i]',
			 								 				'$other_discount[$i]',
			 								 				'$other_discount_type[$i]',
			 								 				'$other_discount_actual[$i]',
			 								 				'',
			 								 				'$other_sell_price[$i]',
			 								 				'',
			 								 				'',
			 								 				'$invoice_date',
			 								 				'$is_paid',
			 								 				'$invoice_type',
			 								 				'$other_note[$i]',
			 								 				'0',
			 								 				'$other_id_fk[$i]',
			 								 				'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,
							// 		  									  group_no,
							// 		  									  created_by,
							// 		  									  last_action,
							// 		  									  last_action_by)
							// 							 		   VALUES('".$_SESSION['USD_TO_IQD']."',
							// 							 		          '$invoice_currency[$i]',
							// 							 		          '$other_pay[$i]',
							// 							 		          '$invoice_date',
							// 							 		          '$last_inserted_other_id',
							// 							 		          '$invoice_inserted_id',
							// 							 		          '$customer_id_fk',
							// 							 		          '$rnd_no',
							// 							 		          '".$_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
						}


					}
				} // for i other

						// 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 ='';
					$barcode = '';

				

					$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,
													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]',
	 								 				'$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 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){ // 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 = '';
							}

							

							$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){
								// 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 ){

									$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 ){



					// insert into payment table the paid price
						$lastaction = "Add New Payment";



					if($total_paid > 0){

					

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

						$dd = date("y", time());
						
						$payment_number = "PAY".$dd.($pay_sequence_no);
						$payment_transaction = 'Payment';
						$payment_note = "Add Payment for Invoice : ".$invoice_number;

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


					}


						

						// end payment insertion




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