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



date_default_timezone_set($_SESSION['SYSTimezone']);

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

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


			$cond1 = isset($_GET['id'])? ' AND S.stock_id='.$_GET['id']:'';
			$cond2 = isset($_GET['pid'])? ' AND P.product_id='.$_GET['pid']:'';

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

			
			
			$cond_sub = $cond3 . $cond4 ;


			$table  =  '(

				SELECT
					
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Purchase"  '.$cond_sub.'),0)
					    +
					 COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Initial"  '.$cond_sub.'),0)
					    +
					 COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Return"  '.$cond_sub.'),0)
					    +

					 COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Moved In"  '.$cond_sub.'),0)
					    -
					 COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Moved Out"  '.$cond_sub.'),0)
					) AS recieved_quantity,

					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Invoice"  '.$cond_sub.'),0)
					) AS sold_quantity,

					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Initial"  '.$cond_sub.'),0)
					) AS initial_quantity,

					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Damage"  '.$cond_sub.'),0)
					) AS damage_quantity,

					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Return"  '.$cond_sub.'),0)
					) AS return_quantity,

					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Moved In"  '.$cond_sub.'),0)
					) AS moved_in_quantity,

					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Moved Out"  '.$cond_sub.'),0)
					) AS moved_out_quantity,

					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Used"  '.$cond_sub.'),0)
					) AS used_quantity,

					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Offer"  '.$cond_sub.'),0)
					) AS offered_quantity,

					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Expired"  '.$cond_sub.'),0)
					) AS expired_quantity,


					(

					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Purchase"  '.$cond_sub.'),0)
					) 
					+
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Initial"  '.$cond_sub.'),0)
					) 
					+
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Return"  '.$cond_sub.'),0)
					) 
					+

					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Moved In"  '.$cond_sub.'),0)
					) 
					-
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Invoice"  '.$cond_sub.'),0)
					) 
					-
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Damage"  '.$cond_sub.'),0)
					) 

					
					-
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Moved Out"  '.$cond_sub.'),0)
					) 
					-
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Used"  '.$cond_sub.'),0)
					) 
					-
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Offer"  '.$cond_sub.'),0)
					) 
					-
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Expired"  '.$cond_sub.'),0)
					) 


					) AS on_hand_quantity,

					(SELECT cost_expenses FROM `purchase_item` PI WHERE P.product_id =PI.product_id_fk ORDER BY purchase_item_id DESC LIMIT 1) as cost_expenses,

					stock_id,
					stock_name,
					product_id,
					product_name,
					P.sell_price
					
				FROM
					product  P
					LEFT JOIN stock S ON (S.is_deleted=0)
					
					
				WHERE
					( P.is_deleted = 0 '.$cond.') 
		
				) AS ALLTBL';
			
			if(!isset($_GET['id']) || ($_GET['id']=='all')){
				
				$table  =  '(

				SELECT
					
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0  AND PSK.stock_transaction = "Purchase"  '.$cond_sub.'),0)
					     +
					 COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0  AND PSK.stock_transaction = "Initial"  '.$cond_sub.'),0)
					     +

					 COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0  AND PSK.stock_transaction = "Return"  '.$cond_sub.'),0)
					     +

					 COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0  AND PSK.stock_transaction = "Moved In"  '.$cond_sub.'),0)
					    -
					 COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0  AND PSK.stock_transaction = "Moved Out"  '.$cond_sub.'),0)
					) AS recieved_quantity,

					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0  AND PSK.stock_transaction = "Invoice"  '.$cond_sub.'),0)
					) AS sold_quantity,

					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0  AND PSK.stock_transaction = "Initial"  '.$cond_sub.'),0)
					) AS initial_quantity,

					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0  AND PSK.stock_transaction = "Return"  '.$cond_sub.'),0)
					) AS return_quantity,

					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0  AND PSK.stock_transaction = "Damage"  '.$cond_sub.'),0)
					) AS damage_quantity,

					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0  AND PSK.stock_transaction = "Moved In"  '.$cond_sub.'),0)
					) AS moved_in_quantity,

					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0  AND PSK.stock_transaction = "Moved Out"  '.$cond_sub.'),0)
					) AS moved_out_quantity,

					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0  AND PSK.stock_transaction = "Used"  '.$cond_sub.'),0)
					) AS used_quantity,

					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_transaction = "Offer"  '.$cond_sub.'),0)
					) AS offered_quantity,

					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0  AND PSK.stock_transaction = "Expired"  '.$cond_sub.'),0)
					) AS expired_quantity,

					
					(
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0  AND PSK.stock_transaction = "Purchase"  '.$cond_sub.'),0)
					) 
					+
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0  AND PSK.stock_transaction = "Initial"  '.$cond_sub.'),0)
					) 

					+
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0  AND PSK.stock_transaction = "Return"  '.$cond_sub.'),0)
					) 
					+
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0  AND PSK.stock_transaction = "Moved In"  '.$cond_sub.'),0)
					) 
					-
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0  AND PSK.stock_transaction = "Invoice"  '.$cond_sub.'),0)
					) 
					-
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0  AND PSK.stock_transaction = "Damage"  '.$cond_sub.'),0)
					) 
					
					-
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0  AND PSK.stock_transaction = "Moved Out"  '.$cond_sub.'),0)
					) 
					-
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0  AND PSK.stock_transaction = "Used"  '.$cond_sub.'),0)
					) 
					-
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_transaction = "Offer"  '.$cond_sub.'),0)
					) 
					-
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0  AND PSK.stock_transaction = "Expired"  '.$cond_sub.'),0)
					) 

					) AS on_hand_quantity,

					(SELECT cost_expenses FROM `purchase_item` PI WHERE P.product_id =PI.product_id_fk ORDER BY purchase_item_id DESC LIMIT 1) as cost_expenses,

					"all" as stock_id,
					"All Stocks" AS stock_name,
					product_id,
					product_name,
					P.sell_price

					
				FROM
					product  P
					
					
				WHERE
					( P.is_deleted = 0 '.$cond2.') 
		
				) AS ALLTBL';
			}
			// Table's primary key
			//echo $table;
			$primaryKey = 'product_id';
			$columns =  array(
				  			  	array( 'db' => 'product_id', 'dt' => 1 ),
			    				array( 'db' => 'product_name','dt' => 2 ),
			    				array( 'db' => 'stock_name','dt' => 3 ),
			    				array( 'db' => 'recieved_quantity','dt' => 4 ),
			    				array( 'db' => 'on_hand_quantity','dt' => 5 ),
			    				array( 'db' => 'sold_quantity','dt' => 6 ),
			    				array( 'db' => 'damage_quantity','dt' => 7 ),
			    				array( 'db' => 'used_quantity','dt' => 8 ),
			    				
			    				array( 'db' => 'moved_in_quantity','dt' => 9 ),
			    				array( 'db' => 'moved_out_quantity','dt' => 10 ),
			    				
			    				array( 'db' => 'expired_quantity','dt' => 11 ),
			    				array( 'db' => 'initial_quantity','dt' => 12 ),
			    				array( 'db' => 'stock_id','dt' => 13 ),
			    				array( 'db' => 'product_id','dt' => 14 ),
			    				array( 'db' => 'sell_price','dt' => 15 ),
			    				array( 'db' => 'return_quantity','dt' => 16 ),
			    				array( 'db' => 'cost_expenses','dt' => 17 ),

			    				// array( 'db' => 'sold_quantity','dt' => 9 ),
			    				// array( 'db' => 'damage_quantity','dt' => 10 ),
			    				// array( 'db' => 'moved_in_quantity','dt' => 11 ),
			    				// array( 'db' => 'moved_out_quantity','dt' => 12 ),
			    				
			    				// array( 'db' => 'expired_quantity','dt' => 13 ),
			    				// array( 'db' => 'initial_quantity','dt' => 14 ),
			    				// array( 'db' => 'stock_id','dt' => 15 ),
			    				// array( 'db' => 'product_id','dt' => 16 ),
								
			);
			// SQL server connection information
			$sql_details = array(
			    'user' => $mysql_user,
			    'pass' => $mysql_password,
			    'db'   => $mysql_db,
			    'host' => $mysql_server
			);
			require( 'ssp.php' );
			echo json_encode(
			    SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns ,null, '' )
			);
			exit;


		}

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

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

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

			// $cond1 = isset($_POST['id'])? ' AND S.stock_id='.$_POST['id']:'';

			$cond1 = '';
			$cond2 = '';
			$cond3 = '';
			$cond4 = '';

			if($_POST['id'] >0){ 
				$cond1 = ' AND S.stock_id='.$_POST['id'];
			}

			if($_POST['pid'] >0){ 
				$cond2 = ' AND P.product_id='.$_POST['pid'];
			}

			// $cond2 = isset($_POST['pid'])? ' AND P.product_id='.$_POST['pid']:'';

			$cond5 = '';
			if(isset($_POST['branch_id']) && $_POST['branch_id'] !=0){ 
				$cond5 = ' AND S.branch_id_fk ='.$_POST['branch_id'];
			}
			
			$cond = $cond1 . $cond2 . $cond5 ;

			if($_POST['bid'] >0){ 
				$cond3 = ' AND PSK.branch_id_fk='.$_POST['bid'];
			}

			if($_POST['sid'] >0){ 
				$cond4 = ' AND PSK.supplier_id_fk='.$_POST['sid'];
			}
			
			// $cond3 = isset($_POST['bid'])? ' AND PSK.branch_id_fk='.$_POST['bid']:'';
			// $cond4 = isset($_POST['sid'])? ' AND PSK.supplier_id_fk='.$_POST['sid']:'';

			
			
			$cond_sub = $cond3 . $cond4 ;


			$res = $db->query('SELECT
					
					

							(

								(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
								        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Purchase"  '.$cond_sub.'),0)
								) 
								+
								(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
								        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Initial"  '.$cond_sub.'),0)
								) 
								+
								(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
								        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Return"  '.$cond_sub.'),0)
								) 
								+

								(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
								        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Moved In"  '.$cond_sub.'),0)
								) 
								-
								(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
								        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Invoice"  '.$cond_sub.'),0)
								) 
								-
								(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
								        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Damage"  '.$cond_sub.'),0)
								) 

								
								-
								(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
								        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Moved Out"  '.$cond_sub.'),0)
								) 
								-
								(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
								        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Used"  '.$cond_sub.'),0)
								) 
								-
								(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
								        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Offer"  '.$cond_sub.'),0)
								) 
								-
								(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
								        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Expired"  '.$cond_sub.'),0)
								) 


							) AS on_hand_quantity,

							(SELECT cost_expenses FROM `purchase_item` PI WHERE P.product_id =PI.product_id_fk ORDER BY purchase_item_id DESC LIMIT 1) as cost_expenses,

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

			if ($res) {
				$op  = array('total_on_hand'=>0,'total_cost'=>0, 'total_sell'=>0);

				$total_on_hand 	= 0;
				$total_cost 	= 0;
				$total_sell 	= 0;

				while ($row = $res->fetch_assoc()) {
				    
				    $total_on_hand 	= $total_on_hand + $row['on_hand_quantity'];
				    $total_cost 	= $total_cost + ($row['on_hand_quantity'] * $row['cost_expenses']);
				    $total_sell 	= $total_sell + ($row['on_hand_quantity']* $row['sell_price']);

						
				}

				$op['total_on_hand']=$total_on_hand;
				$op['total_cost']=$total_cost;
				$op['total_sell']=$total_sell;

				echo json_encode($op);
				exit;
			}else{
				echo $db->error;
				exit;
			}
		}



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

			$id 	= $_POST['stock_id'];
			$pid 	= $_POST['product_id'];

			$res = $db->query('
								SELECT
			
					(

					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Purchase" ),0)
					) 
					+
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Moved In" ),0)
					) 
					+
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Initial" ),0)
					) 

					+
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Return" ),0)
					) 
					-
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Invoice" ),0)
					) 
					-
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Damage" ),0)
					) 

					
					-
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Moved Out" ),0)
					) 
					-
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Used" ),0)
					) 
					-
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Offer" ),0)
					) 
					-
					(COALESCE((SELECT sum(recieved_quantity) FROM product_stock PSK  	
					        	WHERE PSK.product_id_fk = P.product_id AND  PSK.is_deleted =0 AND PSK.stock_id_fk = S.stock_id AND PSK.stock_transaction = "Expired" ),0)
					) 


					) AS on_hand_quantity,

					stock_id,
					stock_name,
					product_id,
					product_name,
					product_code

					
					
				FROM
					product  P
					LEFT JOIN stock S ON (S.is_deleted=0)
					
				WHERE
					( P.is_deleted = 0 AND P.product_id ='.$pid.' AND S.stock_id='.$id.' )
								');
			if ($res) {
				$op  = array('is_success'=>false, 'data' =>array());
				while ($row = $res->fetch_assoc()) {
				    // do what you need.
						$result['stock_id'] = $row['stock_id'];
						$result['stock_name'] = $row['stock_name'];
						$result['on_hand_quantity'] = $row['on_hand_quantity'];
						$result['product_id'] = $row['product_id'];
						$result['product_name'] = $row['product_name'];
						$result['product_code'] = $row['product_code'];
						array_push($op['data'],$result);
				}
				$op['is_success'] = true;
				echo json_encode($op);
				exit;
			}else{
				$op['is_success'] = false;
				$op['data'] = "لا تتوفر معلومات";
				echo json_encode($op);
				
				exit;
			}
		}

		

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

			$id = $_POST['obj_id'];
			$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"]);
            			$to_stock_id		=addslashes($_POST["to_stock_id"]);
            			$recieved_quantity 	=addslashes($_POST["moved_qty"]);
            			$id 				=addslashes($_POST["obj_id"]);

						

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

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

						$sql ="INSERT INTO product_stock (recieved_quantity,
														  stock_transaction,
														  stock_transaction_type,
														  transaction_date,
														  product_id_fk,
														  stock_id_fk,
														  created_by,
														  last_action,
														  last_action_by)
												   VALUES('$recieved_quantity',
												   		  '$tansaction',
												   		  '2',
												   		  '$reg_date',
												   		  '$id',
												   		  '$stock_id',
												   		  '".$_SESSION['user_id']."',
												   		  '$lastaction',
												   		  '".$_SESSION['user_id']."')";

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

		     			if ($res){

		     				$tansaction = "Moved In";
		     				$sql1 ="INSERT INTO product_stock (recieved_quantity,
														  stock_transaction,
														  stock_transaction_type,
														  transaction_date,
														  product_id_fk,
														  stock_id_fk,
														  created_by,
														  last_action,
														  last_action_by)
												   VALUES('$recieved_quantity',
												   		  '$tansaction',
												   		  '1',
												   		  '$reg_date',
												   		  '$id',
												   		  '$to_stock_id',
												   		  '".$_SESSION['user_id']."',
												   		  '$lastaction',
												   		  '".$_SESSION['user_id']."')";

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

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

		}

		

		

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

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

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

			$tansaction = "Damage";
 			
 			$sql1 ="INSERT INTO product_stock (recieved_quantity,
 											  resons,
											  stock_transaction,
											  stock_transaction_type,
											  transaction_date,
											  product_id_fk,
											  stock_id_fk,
											  created_by,
											  last_action,
											  last_action_by)
									   VALUES('$damage_qty',
									   		  '$damage_resons',
									   		  '$tansaction',
									   		  '2',
									   		  '$reg_date',
									   		  '$id',
									   		  '$stock_id_damage',
									   		  '".$_SESSION['user_id']."',
									   		  '$lastaction',
									   		  '".$_SESSION['user_id']."')";

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

			if ($res1){
				
 				$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_expire") {

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

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

			$tansaction = "Expired";
 			
 			$sql1 ="INSERT INTO product_stock (recieved_quantity,
 											  resons,
											  stock_transaction,
											  stock_transaction_type,
											  transaction_date,
											  product_id_fk,
											  stock_id_fk,
											  created_by,
											  last_action,
											  last_action_by)
									   VALUES('$expire_qty',
									   		  '$expire_resons',
									   		  '$tansaction',
									   		  '2',
									   		  '$reg_date',
									   		  '$id',
									   		  '$stock_id_expire',
									   		  '".$_SESSION['user_id']."',
									   		  '$lastaction',
									   		  '".$_SESSION['user_id']."')";

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

			if ($res1){
				
 				$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"]);
            			$qty 				=addslashes($_POST["qty"]);
            			$stock_id_used 		=addslashes($_POST["stock_id_used"]);
            			$used_resons 		='';
            			// $used_resons 		=addslashes($_POST["used_resons"]);

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

						$tansaction = "Used";
		     			
		     			$sql1 ="INSERT INTO product_stock (recieved_quantity,
		     											  resons,
														  stock_transaction,
														  stock_transaction_type,
														  transaction_date,
														  product_id_fk,
														  stock_id_fk,
														  created_by,
														  last_action,
														  last_action_by)
												   VALUES('$qty',
												   		  '$used_resons',
												   		  '$tansaction',
												   		  '2',
												   		  '$reg_date',
												   		  '$id',
												   		  '$stock_id_used',
												   		  '".$_SESSION['user_id']."',
												   		  '$lastaction',
												   		  '".$_SESSION['user_id']."')";

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

							if ($res1){
								
			     				$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;
	}
?>

