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



date_default_timezone_set($_SESSION['SYSTimezone']);


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


		$sold_len 		= '(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)
					) ';

		

		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 == 'sold_length' ||  $col == 'damge_length' ||  $col == 'part_lengths' ||  $col == 'remained_length'){

					// 	// $globalSearch[] = " ";

					// }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 == 'sold_length' ||  $col == 'damge_length' ||  $col == 'part_lengths' ||  $col == 'remained_length'){

					// 	// $columnSearch[] = " ";

					// }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_once "db_connect.php";

		if( isset($_GET['type']) && ($_GET['type'] == "load")){


			$cond1 = isset($_GET['id'])? ' AND PID.stock_id_fk='.$_GET['id']:'';

			// multi value explose
			$cond2 = '';

			if (isset($_GET['pid'])) {
				$pids = explode(',', $_GET['pid']);

				$cond2 = ' AND ( ';
				$orr = ' ';
				for ($mr=0; $mr <count($pids) ; $mr++) { 
					$cond2 =   $cond2 . $orr . ' P.product_id='.$pids[$mr] ;
					$orr = ' OR ';
					
				}
				
				$cond2 = $cond2 . ' ) ';

			}

			// echo $cond2;
			
			// $cond2 = isset($_GET['pid'])? ' AND PID.product_id_fk='.$_GET['pid']:'';

			$cond3 = isset($_GET['sid'])? ' AND PID.item_status='.$_GET['sid']:'';



			$primaryKey = 'product_id';
			$columns =  array(
				  			  	array( 'db' => 'product_id', 'dt' => 0 ),
			    				array( 'db' => 'erbil_code','dt' => 1 ),
			    				array( 'db' => 'turkish_code','dt' => 2 ),
			    				array( 'db' => 'barcode','dt' => 3 ),
			    				array( 'db' => 'item_name','dt' => 4 ),
			    				array( 'db' => 'total_lengths_cnt','dt' => 5 ),
			    				
			    				array( 'db' => 'total_lengths','dt' => 6 ),
			    				array( 'db' => 'total_used_length','dt' => 7 ),
			    				array( 'db' => 'total_sold_length','dt' => 8 ),
			    				array( 'db' => 'total_damage_length','dt' => 9 ),
			    				array( 'db' => 'total_part_lengths','dt' => 10 ),
			    				
								
			);


			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);



			$cond 	= $cond1 . $cond3  . $fltr;
			$condsq = $cond1 . $cond3  ;


			// New method on 4-06-2020 convert on future it reduced time from 11.5 sec to 0.7 sec

			// 			SELECT
			// 					product_id,
			// 					erbil_code,
			// 					turkish_code,
			// 					barcode,
			// 					item_name,
			// 					SQTL.total_lengths,
			//                     SQSL.total_sold_length,
								
			// 					(SQDL.total_damage_length + SQDL2.dmg) as total_dmg
								

								


			                    
								
								
			// 					FROM 	product P

								 	
			// 					 	LEFT JOIN item_type IT ON (P.item_type_id_fk = IT.item_type_id)
			//                         LEFT JOIN (SELECT  sum(item_length) AS total_lengths, product_id_fk FROM purchase_item_detail PID WHERE  PID.is_deleted=0 AND PID.item_status !=7  GROUP BY product_id_fk)  SQTL ON (P.product_id =SQTL.product_id_fk )
			                        
			//                         LEFT JOIN ((select sum(sell_item_detail_height) as total_sold_length, SID.product_id_fk FROM sell_item_detail SID 
			//                         			LEFT JOIN  purchase_item_detail PID ON (SID.purchase_item_detail_id_fk = PID.purchase_item_detail_id AND PID.is_deleted=0)
			//                         			WHERE  SID.part_status=0 AND SID.is_deleted=0  GROUP BY  SID.product_id_fk)) SQSL ON ( P.product_id =SQSL.product_id_fk)
			                                    
			// LEFT JOIN (
			//                select sum(SID.damage_length)as total_damage_length, SID.product_id_fk  FROM sell_item_detail SID 
			//                               LEFT JOIN  purchase_item_detail PID ON (SID.purchase_item_detail_id_fk = PID.purchase_item_detail_id AND PID.is_deleted=0)
			//                               WHERE  SID.part_status=0 AND SID.is_deleted=0   GROUP BY SID.product_id_fk 
			// 		) SQDL ON (P.product_id =SQDL.product_id_fk)
			        
			// LEFT JOIN (SELECT  sum(damage_length) AS dmg,product_id_fk  FROM purchase_item_detail PID WHERE  PID.is_deleted=0 AND PID.item_status !=7    GROUP BY product_id_fk)  SQDL2 ON (P.product_id =SQDL2.product_id_fk )
			                    
								 	 
			// 					WHERE 
			// 					P.is_deleted = 0

			$table  =  "(
						SELECT
								product_id,
								erbil_code,
								turkish_code,
								barcode,
								item_name,
								IFNULL(SQTL.total_lengths,0) AS total_lengths,
								IFNULL(SQTLC.total_lengths_cnt,0) AS total_lengths_cnt,
								IFNULL(SQTUL.total_used_length,0) AS total_used_length,
			                    IFNULL(SQSL.total_sold_length,0) AS total_sold_length,
			                    IFNULL(SQTPL.total_part_lengths,0) AS total_part_lengths,
								
								(IFNULL(SQDL.total_damage_length,0) + IFNULL(SQDL2.dmg,0)) as total_damage_length
								
								
						FROM 	product P

								 	
						LEFT JOIN item_type IT ON (P.item_type_id_fk = IT.item_type_id)
			            
			            LEFT JOIN (SELECT  sum(item_length) AS total_lengths, product_id_fk 
			            			FROM purchase_item_detail PID 
			            			WHERE  PID.is_deleted=0 AND PID.item_status !=7 $condsq GROUP BY product_id_fk
			            			)  SQTL ON (P.product_id =SQTL.product_id_fk )

			            LEFT JOIN (SELECT  COUNT(item_length) AS total_lengths_cnt, product_id_fk 
			            			FROM purchase_item_detail PID 
			            			WHERE  PID.is_deleted=0 AND PID.item_status !=7 $condsq GROUP BY product_id_fk
			            			)  SQTLC ON (P.product_id =SQTLC.product_id_fk )

			            
			            LEFT JOIN (SELECT  sum(used_length) AS total_used_length, product_id_fk 
			            			FROM purchase_item_detail PID 
			            			WHERE  PID.is_deleted=0 AND PID.item_status !=7 $condsq GROUP BY product_id_fk
			            			)  SQTUL ON (P.product_id =SQTUL.product_id_fk )


			            LEFT JOIN ((select sum(sell_item_detail_height) as total_sold_length, SID.product_id_fk 
			            			FROM sell_item_detail SID 
			                        			
			           				LEFT JOIN  purchase_item_detail PID ON (SID.purchase_item_detail_id_fk = PID.purchase_item_detail_id AND PID.is_deleted=0)
			                        	
			           				 WHERE  SID.part_status=0 AND SID.is_deleted=0 $condsq GROUP BY  SID.product_id_fk)
			           				) SQSL ON ( P.product_id =SQSL.product_id_fk)
			                                    
						LEFT JOIN (
			               			select sum(SID.damage_length)as total_damage_length, SID.product_id_fk  
			               			FROM sell_item_detail SID 
			                        LEFT JOIN  purchase_item_detail PID ON (SID.purchase_item_detail_id_fk = PID.purchase_item_detail_id AND PID.is_deleted=0)
			                        WHERE  SID.part_status=0 AND SID.is_deleted=0 $condsq  GROUP BY SID.product_id_fk 
									) SQDL ON (P.product_id =SQDL.product_id_fk)
			        
						LEFT JOIN (SELECT  sum(damage_length) AS dmg, product_id_fk  
									FROM purchase_item_detail PID 
									WHERE  PID.is_deleted=0 AND PID.item_status !=7 $condsq   GROUP BY product_id_fk
									)  SQDL2 ON (P.product_id =SQDL2.product_id_fk )


						LEFT JOIN (SELECT  sum(item_length) AS total_part_lengths, product_id_fk  
									FROM purchase_item_detail PID 
									WHERE  PID.is_deleted=0 AND PID.item_status !=7 AND PID.is_part=1 $condsq   GROUP BY product_id_fk
									)  SQTPL ON (P.product_id =SQTPL.product_id_fk )
			                    
								 	 
						WHERE 
								P.is_deleted = 0  $cond2  $fltr $ords  $limit
						) AS ALLTBL";

			
			$table1  =  '(
				SELECT
					product_id,
					erbil_code,
					turkish_code,
					barcode,
					item_name,
					
					(SELECT  sum(item_length) FROM purchase_item_detail PID WHERE  PID.is_deleted=0 AND PID.item_status !=7 AND P.product_id =PID.product_id_fk '.$cond.') AS total_lengths,

					(SELECT  COUNT(item_length) FROM purchase_item_detail PID WHERE  PID.is_deleted=0 AND PID.item_status !=7 AND P.product_id =PID.product_id_fk '.$cond.') AS total_lengths_cnt,

					(SELECT  sum(used_length) FROM purchase_item_detail PID WHERE  PID.is_deleted=0 AND PID.item_status !=7 AND P.product_id =PID.product_id_fk '.$cond.') AS total_used_length,

					(COALESCE((select sum(sell_item_detail_height) FROM sell_item_detail SID 
                        LEFT JOIN  purchase_item_detail PID ON (SID.purchase_item_detail_id_fk = PID.purchase_item_detail_id AND PID.is_deleted=0)
                        WHERE  SID.part_status=0 AND SID.is_deleted=0 AND P.product_id =SID.product_id_fk '.$cond.' ),0)) 
                    as total_sold_length,

                     (
                        COALESCE((SELECT  sum(damage_length) FROM purchase_item_detail PID WHERE  PID.is_deleted=0 AND PID.item_status !=7 AND P.product_id =PID.product_id_fk '.$cond.'),0)
                        +
                        COALESCE((select sum(SID.damage_length) FROM sell_item_detail SID 
                              LEFT JOIN  purchase_item_detail PID ON (SID.purchase_item_detail_id_fk = PID.purchase_item_detail_id AND PID.is_deleted=0)
                              WHERE  SID.part_status=0 AND SID.is_deleted=0 AND P.product_id =SID.product_id_fk '.$cond.' ),0)) 
                    as total_damage_length,

                    (COALESCE((select sum(item_length) FROM purchase_item_detail PID WHERE  PID.item_status !=7 AND PID.is_deleted=0 AND PID.is_part=1 AND P.product_id =PID.product_id_fk '.$cond.'),0) 
					) AS total_part_lengths
					
					
					FROM 	product P

					 	
					 	LEFT JOIN item_type IT ON (P.item_type_id_fk = IT.item_type_id)
					 	 
					WHERE 
					(P.is_deleted = 0 '.$cond2 .'  '. $fltr.') '. $ords. ' '.$limit.'			
				) AS ALLTBL';
			// Table's primary key
			//echo $table;

			$raw_table  =  "(
						SELECT
								product_id,
								erbil_code,
								turkish_code,
								barcode,
								item_name,
								IFNULL(SQTL.total_lengths,0) AS total_lengths,
								IFNULL(SQTLC.total_lengths_cnt,0) AS total_lengths_cnt,
								IFNULL(SQTUL.total_used_length,0) AS total_used_length,
			                    IFNULL(SQSL.total_sold_length,0) AS total_sold_length,
			                    IFNULL(SQTPL.total_part_lengths,0) AS total_part_lengths,
								
								(IFNULL(SQDL.total_damage_length,0) + IFNULL(SQDL2.dmg,0)) as total_damage_length
								
								
						FROM 	product P

								 	
						LEFT JOIN item_type IT ON (P.item_type_id_fk = IT.item_type_id)
			            
			            LEFT JOIN (SELECT  sum(item_length) AS total_lengths, product_id_fk 
			            			FROM purchase_item_detail PID 
			            			WHERE  PID.is_deleted=0 AND PID.item_status !=7 $condsq GROUP BY product_id_fk
			            			)  SQTL ON (P.product_id =SQTL.product_id_fk )

			            LEFT JOIN (SELECT  COUNT(item_length) AS total_lengths_cnt, product_id_fk 
			            			FROM purchase_item_detail PID 
			            			WHERE  PID.is_deleted=0 AND PID.item_status !=7 $condsq GROUP BY product_id_fk
			            			)  SQTLC ON (P.product_id =SQTLC.product_id_fk )

			            
			            LEFT JOIN (SELECT  sum(used_length) AS total_used_length, product_id_fk 
			            			FROM purchase_item_detail PID 
			            			WHERE  PID.is_deleted=0 AND PID.item_status !=7 $condsq GROUP BY product_id_fk
			            			)  SQTUL ON (P.product_id =SQTUL.product_id_fk )


			            LEFT JOIN ((select sum(sell_item_detail_height) as total_sold_length, SID.product_id_fk 
			            			FROM sell_item_detail SID 
			                        			
			           				LEFT JOIN  purchase_item_detail PID ON (SID.purchase_item_detail_id_fk = PID.purchase_item_detail_id AND PID.is_deleted=0)
			                        	
			           				 WHERE  SID.part_status=0 AND SID.is_deleted=0 $condsq GROUP BY  SID.product_id_fk)
			           				) SQSL ON ( P.product_id =SQSL.product_id_fk)
			                                    
						LEFT JOIN (
			               			select sum(SID.damage_length)as total_damage_length, SID.product_id_fk  
			               			FROM sell_item_detail SID 
			                        LEFT JOIN  purchase_item_detail PID ON (SID.purchase_item_detail_id_fk = PID.purchase_item_detail_id AND PID.is_deleted=0)
			                        WHERE  SID.part_status=0 AND SID.is_deleted=0 $condsq  GROUP BY SID.product_id_fk 
									) SQDL ON (P.product_id =SQDL.product_id_fk)
			        
						LEFT JOIN (SELECT  sum(damage_length) AS dmg, product_id_fk  
									FROM purchase_item_detail PID 
									WHERE  PID.is_deleted=0 AND PID.item_status !=7 $condsq   GROUP BY product_id_fk
									)  SQDL2 ON (P.product_id =SQDL2.product_id_fk )


						LEFT JOIN (SELECT  sum(item_length) AS total_part_lengths, product_id_fk  
									FROM purchase_item_detail PID 
									WHERE  PID.is_deleted=0 AND PID.item_status !=7 AND PID.is_part=1 $condsq   GROUP BY product_id_fk
									)  SQTPL ON (P.product_id =SQTPL.product_id_fk )
			                    
								 	 
						WHERE 
								P.is_deleted = 0  $cond2  $fltr 
						) AS ALLTBL";

			$raw_table1  =  '(
				SELECT
					product_id,
					erbil_code,
					turkish_code,
					barcode,
					item_name,
					

					(SELECT  sum(item_length) FROM purchase_item_detail PID WHERE  PID.is_deleted=0 AND PID.item_status !=7 AND P.product_id =PID.product_id_fk '.$cond.') AS total_lengths,

					(SELECT  COUNT(item_length) FROM purchase_item_detail PID WHERE  PID.is_deleted=0 AND PID.item_status !=7 AND P.product_id =PID.product_id_fk '.$cond.') AS total_lengths_cnt,

					(SELECT  sum(used_length) FROM purchase_item_detail PID WHERE  PID.is_deleted=0 AND PID.item_status !=7 AND P.product_id =PID.product_id_fk '.$cond.') AS total_used_length,

					(COALESCE((select sum(sell_item_detail_height) FROM sell_item_detail SID 
                        LEFT JOIN  purchase_item_detail PID ON (SID.purchase_item_detail_id_fk = PID.purchase_item_detail_id AND PID.is_deleted=0)
                        WHERE  SID.part_status=0 AND SID.is_deleted=0 AND P.product_id =SID.product_id_fk '.$cond.' ),0)) 
                    as total_sold_length,

                     (
                        COALESCE((SELECT  sum(damage_length) FROM purchase_item_detail PID WHERE  PID.is_deleted=0 AND PID.item_status !=7 AND P.product_id =PID.product_id_fk '.$cond.'),0)
                        +
                        COALESCE((select sum(SID.damage_length) FROM sell_item_detail SID 
                              LEFT JOIN  purchase_item_detail PID ON (SID.purchase_item_detail_id_fk = PID.purchase_item_detail_id AND PID.is_deleted=0)
                              WHERE  SID.part_status=0 AND SID.is_deleted=0 AND P.product_id =SID.product_id_fk '.$cond.' ),0)) 
                    as total_damage_length,

                    (COALESCE((select sum(item_length) FROM purchase_item_detail PID WHERE  PID.item_status !=7 AND PID.is_deleted=0 AND PID.is_part=1 AND P.product_id =PID.product_id_fk '.$cond.'),0) 
					) AS total_part_lengths
					
					
					FROM 	product P

					 	
					 	LEFT JOIN item_type IT ON (P.item_type_id_fk = IT.item_type_id)
					 	 
					 	 
					WHERE 
					(P.is_deleted = 0  '.$cond2 .'  '. $fltr.' ) 
				) AS ALLTBL';
			
			// 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']=="get") {

			//$user_id = $_POST['cusid'];

			$res = $db->query("SELECT * FROM stock WHERE is_deleted = 0");
			if ($res) {
				$op  = array();
				while ($row = $res->fetch_assoc()) {
				    // do what you need.
						$result[0] = $row['stock_id'];
						$result[1] = $row['stock_name'];
						$result[2] = $row['stock_type'];
						array_push($op,$result);
				}
				echo json_encode($op);
				exit;
			}else{
				echo 0;
				exit;
			}
		}

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

			$cond1 = (isset($_POST['id']) && $_POST['id']!='not')? ' AND PID.stock_id_fk='.$_POST['id']:'';

			// multi value explose
			$cond2 = '';

			if (isset($_POST['pid']) && $_POST['pid']!='not') {
				$pids = explode(',', $_POST['pid']);

				$cond2 = ' AND ( ';
				$orr = ' ';
				for ($mr=0; $mr <count($pids) ; $mr++) { 
					$cond2 =   $cond2 . $orr . ' P.product_id='.$pids[$mr] ;
					$orr = ' OR ';
					
				}
				
				$cond2 = $cond2 . ' ) ';

			}


			$columns =  array(
				  			  	array( 'db' => 'product_id', 'dt' => 0 ),
			    				array( 'db' => 'erbil_code','dt' => 1 ),
			    				array( 'db' => 'turkish_code','dt' => 2 ),
			    				array( 'db' => 'barcode','dt' => 3 ),
			    				array( 'db' => 'item_name','dt' => 4 ),
			    				array( 'db' => 'total_lengths_cnt','dt' => 5 ),
			    				
			    				array( 'db' => 'total_lengths','dt' => 6 ),
			    				array( 'db' => 'total_used_length','dt' => 7 ),
			    				array( 'db' => 'total_sold_length','dt' => 8 ),
			    				array( 'db' => 'total_damage_length','dt' => 9 ),
			    				array( 'db' => 'total_part_lengths','dt' => 10 ),
			    				
								
			);

			// echo $cond2;
			
			// $cond2 = isset($_GET['pid'])? ' AND PID.product_id_fk='.$_GET['pid']:'';

			$cond3 = (isset($_POST['sid']) && $_POST['sid'] !='not')? ' AND PID.item_status='.$_POST['sid']:'';

			$fltr = filterss($columns);



			$cond 	= $cond1 . $cond3  . $fltr;
			$condsq = $cond1 . $cond3  ;


			$res = $db->query("
						SELECT
								SUM(

								IFNULL(SQTL.total_lengths,0) 
								-
								IFNULL(SQTPL.total_part_lengths,0)
								-
								IFNULL(SQSL.total_sold_length,0)
								-
								IFNULL(SQTUL.total_used_length,0)
								-
								(IFNULL(SQDL.total_damage_length,0) + IFNULL(SQDL2.dmg,0))
								) AS total_remain
								
								
						FROM 	product P

								 	
						LEFT JOIN item_type IT ON (P.item_type_id_fk = IT.item_type_id)
			            
			            LEFT JOIN (SELECT  sum(item_length) AS total_lengths, product_id_fk 
			            			FROM purchase_item_detail PID 
			            			WHERE  PID.is_deleted=0 AND PID.item_status !=7 $condsq GROUP BY product_id_fk
			            			)  SQTL ON (P.product_id =SQTL.product_id_fk )

			            LEFT JOIN (SELECT  COUNT(item_length) AS total_lengths_cnt, product_id_fk 
			            			FROM purchase_item_detail PID 
			            			WHERE  PID.is_deleted=0 AND PID.item_status !=7 $condsq GROUP BY product_id_fk
			            			)  SQTLC ON (P.product_id =SQTLC.product_id_fk )

			            
			            LEFT JOIN (SELECT  sum(used_length) AS total_used_length, product_id_fk 
			            			FROM purchase_item_detail PID 
			            			WHERE  PID.is_deleted=0 AND PID.item_status !=7 $condsq GROUP BY product_id_fk
			            			)  SQTUL ON (P.product_id =SQTUL.product_id_fk )


			            LEFT JOIN ((select sum(sell_item_detail_height) as total_sold_length, SID.product_id_fk 
			            			FROM sell_item_detail SID 
			                        			
			           				LEFT JOIN  purchase_item_detail PID ON (SID.purchase_item_detail_id_fk = PID.purchase_item_detail_id AND PID.is_deleted=0)
			                        	
			           				 WHERE  SID.part_status=0 AND SID.is_deleted=0 $condsq GROUP BY  SID.product_id_fk)
			           				) SQSL ON ( P.product_id =SQSL.product_id_fk)
			                                    
						LEFT JOIN (
			               			select sum(SID.damage_length)as total_damage_length, SID.product_id_fk  
			               			FROM sell_item_detail SID 
			                        LEFT JOIN  purchase_item_detail PID ON (SID.purchase_item_detail_id_fk = PID.purchase_item_detail_id AND PID.is_deleted=0)
			                        WHERE  SID.part_status=0 AND SID.is_deleted=0 $condsq  GROUP BY SID.product_id_fk 
									) SQDL ON (P.product_id =SQDL.product_id_fk)
			        
						LEFT JOIN (SELECT  sum(damage_length) AS dmg, product_id_fk  
									FROM purchase_item_detail PID 
									WHERE  PID.is_deleted=0 AND PID.item_status !=7 $condsq   GROUP BY product_id_fk
									)  SQDL2 ON (P.product_id =SQDL2.product_id_fk )


						LEFT JOIN (SELECT  sum(item_length) AS total_part_lengths, product_id_fk  
									FROM purchase_item_detail PID 
									WHERE  PID.is_deleted=0 AND PID.item_status !=7 AND PID.is_part=1 $condsq   GROUP BY product_id_fk
									)  SQTPL ON (P.product_id =SQTPL.product_id_fk )
			                    
								 	 
						WHERE 
								P.is_deleted = 0  $cond2  $fltr ");

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

			while ($row = $res->fetch_assoc()) {
				    
						$result[0] = $row['total_remain'];
						
						array_push($op['data'],$result);
			}
			echo json_encode($op);
			exit;

		}

		

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

			$id = $_POST['obj_id'];
			$name=addslashes($_POST["name"]);
			$stock_type=addslashes($_POST["stock_type"]);
			$stock_note=addslashes($_POST["stock_note"]);

			$op  = array('is_success'=>false, 'data' => '');
			$lastaction = "Update stock type";

			$res = $db->query("SELECT * FROM stock WHERE is_deleted=0 AND stock_name = '$name' AND stock_id != '$id'");

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

			// checking for changing stock type
			$res1 = $db->query("SELECT * FROM stock WHERE is_deleted=0  AND stock_id = '$id'");

			$row = $res1->fetch_assoc();

			// check if changing stock type from production to store
			if($row['stock_type'] != $stock_type AND $row['stock_type'] ==1){

				// checking if this stock has rolls inside it and not finished yet
				$res2 = $db->query("SELECT * FROM purchase_item_detail WHERE is_deleted=0 AND item_status >2 AND stock_id_fk = '$id'");

					if (mysqli_num_rows($res2)>0) {
						$op['is_success'] = false;
						$op['data'] 	  = "لا يمكنك تحويل نوع المخزن من مخزن انتاج الى مخزن خزن لان المخزن الحالي يحوي على طوب قماش مستخدم وبردات تم تصنيعها , الرجاء قم بنقل الطوب المستخدمة والتي تم تصنيع منها بردات الى مخزن انتاج اخر لكي تستطيع اتمام عملية التحديث بنجاح";
						echo json_encode($op);
						exit;
					}


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

						$update_qry = "UPDATE stock SET  stock_name = '$name', stock_type='$stock_type',stock_note='$stock_note',last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE stock_id = $id";

						$res3 = $db->query($update_qry);

						if ($res3) {

								$lastaction = "Change roll fabric item_status in purchase_item_detail to be 0 because we change stock to store type";

								$sql = "UPDATE purchase_item_detail SET  item_status = 0,  last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE item_status = 2 AND stock_id_fk = $id";

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

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

										$lastaction = "Update Stock roles";
										$role_name = 'Access_مخزن_'.$name;
										
										$update_qry1 = "UPDATE role SET  role = '$role_name',last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE id_fk = $id AND table_name = 'stock' AND action ='view'";
										$res1 = $db->query($update_qry1);

										$role_name = 'يمكنه_النقل_من_مخزن_'.$name;
										$update_qry2 = "UPDATE role SET  role = '$role_name',last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE id_fk = $id AND table_name = 'stock' AND action ='transfer'";
										$res2 = $db->query($update_qry2);

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

						}

			// check if changing stock type from stre to production		
			}elseif($row['stock_type'] != $stock_type AND $row['stock_type'] ==0){

				// check if this store has sold roll
				$res2 = $db->query("SELECT * FROM purchase_item_detail WHERE is_deleted=0 AND item_status =1 AND stock_id_fk = '$id'");

				if (mysqli_num_rows($res2)>0) {
					$op['is_success'] = false;
					$op['data'] 	  = "لا يمكنك تحويل نوع المخزن من مخزن خزن الى مخزن انتاج لان المخزن الحالي يحوي على طوب قماش تم بيعهم , الرجاء قم بنقل الطوب االتي تم بيعهم الى مخزن خزن اخر لكي تستطيع اتمام عملية التحديث بنجاح";
					echo json_encode($op);
					exit;
				}


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

				$update_qry = "UPDATE stock SET  stock_name = '$name', stock_type='$stock_type',stock_note='$stock_note',last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE stock_id = $id";

				$res3 = $db->query($update_qry);

				if ($res3) {

						$lastaction = "Change roll fabric item_status in purchase_item_detail to be 2 because we change stock to production type";

						$sql = "UPDATE purchase_item_detail SET  item_status = 2,  last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE item_status = 0 AND stock_id_fk = $id";

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

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

								$lastaction = "Update Stock roles";
								$role_name = 'Access_مخزن_'.$name;
								
								$update_qry1 = "UPDATE role SET  role = '$role_name',last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE id_fk = $id AND table_name = 'stock' AND action ='view'";
								$res1 = $db->query($update_qry1);

								$role_name = 'يمكنه_النقل_من_مخزن_'.$name;
								$update_qry2 = "UPDATE role SET  role = '$role_name',last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE id_fk = $id AND table_name = 'stock' AND action ='transfer'";
								$res2 = $db->query($update_qry2);


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

				} // if res3
			}

			




			$lastaction = "Update Stock information";
			$update_qry = "UPDATE stock SET  stock_name = '$name', stock_note='$stock_note',last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE stock_id = $id";


			$res = $db->query($update_qry);
			if ($res) {
					// update roles

					$lastaction = "Update Stock roles";
					$role_name = 'Access_مخزن_'.$name;
					
					$update_qry1 = "UPDATE role SET  role = '$role_name',last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE id_fk = $id AND table_name = 'stock' AND action ='view'";
					$res1 = $db->query($update_qry1);

					$role_name = 'يمكنه_النقل_من_مخزن_'.$name;
					$update_qry2 = "UPDATE role SET  role = '$role_name',last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE id_fk = $id AND table_name = 'stock' AND action ='transfer'";
					$res2 = $db->query($update_qry2);


					$op['is_success'] = true;
					$op['data'] 	 	  = "تم تحديث المخزن بنجاح";
					echo json_encode($op);
					exit;
			}else{
				$op['is_success'] = false;
				$op['data'] 	  	= "Something went Wrong";
				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 Stock";


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

			$res = $db->query("SELECT * FROM purchase WHERE is_deleted=0  AND stock_id_fk = '$id'");
			if (mysqli_num_rows($res)>0) {
			
				$op['is_success'] = false;
				$op['data'] 	  = "لا يمكنك حذف هذا المخزن فهنالك قوائم مشتريات تابعة له";
				echo json_encode($op);
				exit;
			}

			$res1 = $db->query("SELECT * FROM purchase_item_detail WHERE is_deleted=0  AND stock_id_fk = '$id'");
			if (mysqli_num_rows($res1)>0) {
			
				$op['is_success'] = false;
				$op['data'] 	  = "لا يمكنك حذف المخزن هناك مواد داخله في هذا المخزن";
				echo json_encode($op);
				exit;
			}

			

			


      				$res = $db->query("UPDATE stock SET is_deleted = 1 ,deleted_at = '$reg_date',last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE stock_id=$id");
     			if ($res){
     				// we should delete stock roles
     				$lastaction = "Delete Stock roles";
					
					$update_qry1 = "UPDATE role SET  is_deleted = 1 ,deleted_at = '$reg_date',last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE id_fk = $id AND table_name = 'stock' AND action ='view'";
					$res1 = $db->query($update_qry1);

					$update_qry2 = "UPDATE role SET  is_deleted = 1 ,deleted_at = '$reg_date',last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE id_fk = $id AND table_name = 'stock' AND action ='transfer'";
					$res2 = $db->query($update_qry2);

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

            			$stock_id=addslashes($_POST["stock_id"]);
            			$id=addslashes($_POST["obj_id"]);
						$res = $db->query("SELECT * FROM purchase_item_detail WHERE purchase_item_detail_id = '$id'");

						$row = $res->fetch_assoc();
						$item_status 	= $row['item_status'];
						$stock_id_fk 	= $row['stock_id_fk'];
						$damage_length  = $row['damage_length'];

						if ($item_status==1 || $item_status==4 || $item_status==6 || $item_status==10) {
							$op['is_success'] = false;
							$op['data'] 	  = "لا يمكن نقل هذه المادة ";
							echo json_encode($op);
							exit;
						}

						$res = $db->query("SELECT * FROM stock WHERE stock_id = '$stock_id'");

						$row = $res->fetch_assoc();
						$stock_type = $row['stock_type'];

						$res = $db->query("SELECT * FROM stock WHERE stock_id = '$stock_id_fk'");

						$row = $res->fetch_assoc();
						$stock_type_old = $row['stock_type'];

						if($stock_type != $stock_type_old && $item_status==3){
							$op['is_success'] = false;
							$op['data'] 	  = "لا يمكن نقل هذه المادة لكن بالامكان بيعها";
							echo json_encode($op);
							exit;
						}

						if($stock_type != $stock_type_old && $item_status==5){
							$op['is_success'] = false;
							$op['data'] 	  = "لا يمكن نقل هذه المادة الى مخزن الانتاج لكن بالامكان اضافة الجزء المتبقي في الانتاج";
							echo json_encode($op);
							exit;
						}

						if($stock_type != $stock_type_old){
							
							switch ($item_status) {
								case '0':
									$item_status='2'; // that mean it was in store stock and change to production stock so we will cahnge its status to 2
									break;
								case '2':
									$item_status='0'; // that mean it was in production stock not used and change to store stock so we will cahnge its status to 0
									break;
								case '8':
									$item_status='11'; // that mean it was in store stock with damge and change to production stock so we will cahnge its status to 11
									break;
								case '11':
									$item_status='8'; // that mean it was in production stock with damage and change to store stock so we will cahnge its status to 8
									break;
								// about case 7 i will thing abut it later when make return item store because it may contain damage length so its status could be 0 or 8 if it transfered to store stock and could be 2 or 11 if transferred to production store
								
								
								
								default:
									$item_status= $item_status; // 9 
									break;
							}
						}else{
							if($item_status == 7){

								$item_status='0'; // that mean it was in store stock and complty return and change to store stock so we will cahnge its status to 0
							}
						}

							
						$delete 	= 0;
						$reg_date 	= date("Y-m-d h:i:s", time());
						$lastaction = "Transfer Item to another Store";

						$res = $db->query("UPDATE purchase_item_detail SET stock_id_fk = '$stock_id', item_status='$item_status' ,last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE purchase_item_detail_id=$id");

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

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

						$reg_date 	= date("Y-m-d h:i:s", time());
						$lastaction = "Set is as Finished in production";

						$res = $db->query("SELECT * FROM purchase_item_detail WHERE purchase_item_detail_id = '$id' AND is_deleted=0");

						$row = $res->fetch_assoc();
						$item_status 	= $row['item_status'];
						$used_length 	= $row['used_length'];
						$damage_length 	= $row['damage_length'];
						$item_length 	= $row['item_length'];

						// prevent make fully damage to these status
						if ($item_status==0 || $item_status==1 || $item_status==4 || $item_status==5 || $item_status==6 || $item_status==7 || $item_status==8 || $item_status==9 || $item_status==10  ) {
							$op['is_success'] = false;
							$op['data'] 	  = "لا يمكن اعتبار هذا العنصر مستخدم كليا ";
							echo json_encode($op);
							exit;
						}

						// compute remained length and set it as used length to keep if it have damage length

						$remained_length = $item_length - $damage_length;

						$res = $db->query("UPDATE purchase_item_detail SET  item_status='4', used_length ='$remained_length' ,last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE purchase_item_detail_id=$id");

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

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

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

						$res = $db->query("SELECT * FROM purchase_item_detail WHERE purchase_item_detail_id = '$id' AND is_deleted=0");

						$row = $res->fetch_assoc();
						$item_status 	= $row['item_status'];
						$used_length 	= $row['used_length'];
						$damage_length 	= $row['damage_length'];
						$item_length 	= $row['item_length'];

						// prevent make fully damage to these status
						if ($item_status==1 || $item_status==3 || $item_status==4 || $item_status==5 || $item_status==6 || $item_status==9 || $item_status==10) {
							$op['is_success'] = false;
							$op['data'] 	  = "لا يمكن اعتبار هذه المادة تالفة بالكامل";
							echo json_encode($op);
							exit;
						}

						$res = $db->query("UPDATE purchase_item_detail SET  item_status='6',damage_length ='$item_length' ,last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE purchase_item_detail_id=$id");

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

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

						$reg_date 	= date("Y-m-d h:i:s", time());
						$lastaction = "Add Damage Length";

						$res = $db->query("SELECT * FROM purchase_item_detail WHERE purchase_item_detail_id = '$id' AND is_deleted=0");

						$row = $res->fetch_assoc();
						$item_status 	= $row['item_status'];
						$used_length 	= $row['used_length'];
						$damage_length 	= $row['damage_length'];
						$item_length 	= $row['item_length'];

						// prevent adding damges length to these status
						if ($item_status==1 || $item_status==4 || $item_status==6 || $item_status==7 || $item_status==10) {
							$op['is_success'] = false;
							$op['data'] 	  = "لا يمكن اضافة تالف الى هذه المادة (انظر الى الحالة)";
							echo json_encode($op);
							exit;
						}

						

						

						// we should compute the remained item length to check if he can add damage length according to the remaining length and not the original length

						$res1 = $db->query("SELECT sum(sell_item_detail_height) as sold_length, 
												   sum(damage_length) as sell_damage_length  
											FROM sell_item_detail 
											WHERE purchase_item_detail_id_fk = '$id' AND part_status =0 AND is_deleted=0");

						$row1 = $res1->fetch_assoc();
						$sold_length 			= $row1['sold_length'];
						$sell_damage_length 	= $row1['sell_damage_length'];
						


						$new_damage_length = $damage_length + $length_m;

						$remained_length = $item_length - ($used_length + $damage_length + $sold_length + $sell_damage_length);
						

						// prevent adding damges length greater than original roll length
						if ($length_m > $remained_length) {
							$op['is_success'] = false;
							$op['data'] 	  = " خطأ اصبح الطول الكلي التالف اكبر من طول العنصر المتبقي.. الرجاء التأكد من القيمة ";
							echo json_encode($op);
							exit;
						}

						// now we should compute remained length again may be item remained length will be 0 so we should change item status to finished because we havnt any thing to use or sell

						$new_remained_length = $item_length - ($used_length + $damage_length + $sold_length + $sell_damage_length + $length_m);

						// change item status for not used rolls only
						$new_item_status ='';

						if ($new_remained_length <=0 ) {

							if ($item_status==0 || $item_status==2 || $item_status==8 || $item_status==11) {
								$new_item_status = 6; // that mean all roll became damage
							}

							if ( $item_status==3) {
								$new_item_status = 4; // that mean it were used in production and now finished
							}
							if ( $item_status==5) {
								$new_item_status = 1; // that mean it were partially sold and now finished
							}
							if ( $item_status==9) {
								$new_item_status = 10; // that mean it were partially sold and used in production and now finished
							}
						}else{
							if ($item_status==0 ) {
								$new_item_status = 8;
							}

							if ( $item_status==2) {
								$new_item_status = 11;
							}
						}

						

						if ( $new_item_status !='') {
							$res = $db->query("UPDATE purchase_item_detail SET  item_status='$new_item_status',damage_length='$new_damage_length' ,last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE purchase_item_detail_id=$id");
						}else{
							$res = $db->query("UPDATE purchase_item_detail SET  damage_length='$new_damage_length' ,last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE purchase_item_detail_id=$id");
						}

						

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

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

						$reg_date 	= date("Y-m-d h:i:s", time());
						$lastaction = "Add Used Length";

						$res = $db->query("SELECT * FROM purchase_item_detail WHERE purchase_item_detail_id = '$id' AND is_deleted=0");

						$row = $res->fetch_assoc();
						$item_status 	= $row['item_status'];
						$used_length 	= $row['used_length'];
						$damage_length 	= $row['damage_length'];
						$item_length 	= $row['item_length'];

						// prevent adding used length to these status
						if ($item_status==0 || $item_status==1 || $item_status==4 || $item_status==6 || $item_status==7 || $item_status==8 || $item_status==10) {
							$op['is_success'] = false;
							$op['data'] 	  = "لا يمكن اضافة طول لاستخدامه في الانتاج من هذه المادة (انظر الى حالة المادة)";
							echo json_encode($op);
							exit;
						}


						// we should compute the remained item length to check if he can add used length according to the remaining length and not the original length

						$res1 = $db->query("SELECT sum(sell_item_detail_height) as sold_length, 
												   sum(damage_length) as sell_damage_length  
											FROM sell_item_detail 
											WHERE purchase_item_detail_id_fk = '$id' AND part_status =0 AND is_deleted=0");

						$row1 = $res1->fetch_assoc();
						$sold_length 			= $row1['sold_length'];
						$sell_damage_length 	= $row1['sell_damage_length'];
						


						$new_length = $used_length + $length_m;

						$remained_length = $item_length - ($used_length + $damage_length + $sold_length + $sell_damage_length);

						// prevent adding damges length greater than original roll length
						if ($length_m > $remained_length) {
							$op['is_success'] = false;
							$op['data'] 	  = " خطأ اصبح الطول الكلي المستخدم اكبر من طول العنصر المتبقي.. الرجاء التأكد من القيمة ";
							echo json_encode($op);
							exit;
						}

						
						// now we should compute remained length again may be item remained length will be 0 so we should change item status to finished because we havnt any thing to use or sell

						$new_remained_length = $item_length - ($used_length + $damage_length + $sold_length + $sell_damage_length + $length_m);

						// change item status according to remaining 
						$new_item_status ='';

						if ($new_remained_length <=0 ) {

							if ($item_status==2 || $item_status==3  || $item_status==11) {
								$new_item_status = 4; // that mean all roll became used
							}

							
							if ( $item_status==5 || $item_status==9) {
								$new_item_status = 10; // that mean it were partially sold and used in production and now finished
							}
						}else{
							
							// change item status for not used rolls only
							if ($item_status==2 || $item_status==11) {
								$new_item_status = 3; // it mean item were used 
							}

							if ($item_status==5 ) {
								$new_item_status = 9; // it mean item were partially sold and now used in production 
							}

						}

						
						

						if ( $new_item_status !='') {
							$res = $db->query("UPDATE purchase_item_detail SET  item_status='$new_item_status',used_length='$new_length' ,last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE purchase_item_detail_id=$id");
						}else{
							$res = $db->query("UPDATE purchase_item_detail SET  used_length='$new_length' ,last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE purchase_item_detail_id=$id");
						}

						

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

            $name=addslashes($_POST["name"]);
						$res = $db->query("SELECT * FROM stock WHERE stock_name = '$name'");
						if (mysqli_num_rows($res)>0) {
							$op['is_success'] = false;
							$op['data'] 	  = "خطأ الاسم مكرر";
							echo json_encode($op);
							exit;
						}else{
							$stock_type=addslashes($_POST["stock_type"]);
							$stock_note=addslashes($_POST["stock_note"]);

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

						$sql ="INSERT INTO stock(stock_name,stock_type,stock_note,created_by,last_action,last_action_by)
						VALUES('$name','$stock_type','$stock_note','".$_SESSION['user_id']."','$lastaction','".$_SESSION['user_id']."')";

						$res = $db->query($sql);
							if ($res) {
								$last_inserted_id = $db->insert_id;
								// create Role for this stock
								$lastaction = "Create New Role for stock ".$name;
								$role_name = 'Access_مخزن_'.$name;
								$sql1 ="INSERT INTO role (role,id_fk,table_name,action,created_by,last_action,last_action_by)
								VALUES('$role_name','$last_inserted_id','stock','view','".$_SESSION['user_id']."','$lastaction','".$_SESSION['user_id']."')";
								$res1 = $db->query($sql1);

								$role_name = 'يمكنه_النقل_من_مخزن_'.$name;
								$sql2 ="INSERT INTO role (role,id_fk,table_name,action,created_by,last_action,last_action_by)
								VALUES('$role_name','$last_inserted_id','stock','transfer','".$_SESSION['user_id']."','$lastaction','".$_SESSION['user_id']."')";
								$res2 = $db->query($sql2);

								

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

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

