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


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 SF.staff_id_fk='.$_GET['id']:'';
			$cond1 = isset($_GET['tr'])? ' AND SF.safe_transaction="'.$_GET['tr'].'"':'';

			if(isset($_GET['branch_id']) && $_GET['branch_id'] !=0){ 
				$cond = $cond . ' AND SF.branch_id_fk ='.$_GET['branch_id'];
			}

			$cond = $cond . $cond1;

			$table  =  '(
				SELECT
					safe_id,
					safe_number,
					safe_amount,
					date_of_add,
					safe_transaction,
					
					transaction_type,
					capital_currency,
					table_name,
					id_fk,
					safe_sequence_no,
					safe_note,
					staff_id_fk,
					SF.branch_id_fk,
					S.full_name,
					branch_name,
					
					
					(SELECT sum(safe_amount) FROM safe SF  WHERE SF.is_deleted =0 AND transaction_type =1 '.$cond.') AS total_amount,

					(SELECT sum(safe_amount) FROM safe SF  WHERE SF.is_deleted =0 AND transaction_type =2 '.$cond.') AS total_subtract
				FROM
					safe SF
					LEFT JOIN staff S ON (SF.staff_id_fk = S.staff_id )
					LEFT JOIN branch B ON (SF.branch_id_fk = B.branch_id )
					
				WHERE
					( SF.is_deleted = 0 '.$cond.') 
			) AS ALLTBL';
			// Table's primary key
			//echo $table;
			$primaryKey = 'safe_id';
			$columns = array(
			    array( 'db' => 'safe_id', 'dt' => 0 ),

			    array( 'db' => 'safe_number', 'dt' => 1 ),
			    array( 'db' => 'safe_amount', 'dt' => 2 ),
			    array( 'db' => 'date_of_add',   'dt' => 3 , 'formatter'=> function($d){
									return date('Y-m-d',strtotime($d));
									 } ),
			    array( 'db' => 'safe_transaction', 'dt' => 4 ),
			    array( 'db' => 'transaction_type', 'dt' => 5 ),
			    array( 'db' => 'full_name', 'dt' => 6 ),
			    array( 'db' => 'safe_note', 'dt' => 7 ),
			    array( 'db' => 'branch_name', 'dt' => 8 ),
			    array( 'db' => 'total_amount', 'dt' => 9 ),
			    array( 'db' => 'total_subtract', 'dt' => 10 ),
			    array( 'db' => 'staff_id_fk', 'dt' => 11 ),
			   
			   
			);
			// SQL server connection information
			$sql_details = array(
			    'user' => $mysql_user,
			    'pass' => $mysql_password,
			    'db'   => $mysql_db,
			    'host' => $mysql_server
			);
			require( 'ssp.php' );
			echo json_encode(
			    SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns ,null, '' )
			);
			exit;
		}

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

			
			

			$cond_d = (isset($_POST['d1']) && isset($_POST['d2']))? ' AND (SI.sell_date) BETWEEN "'.$_POST['d1'].'" AND "'.$_POST['d2'].'"':'';


			$cond_s = (isset($_POST['saleman_id']) && ($_POST['saleman_id']) > 0)? ' AND I.saleman_id_fk = "'.$_POST['saleman_id'].'"':'';
			
			$cond_cus = (isset($_POST['customer_id']) && ($_POST['customer_id']) > 0)? ' AND I.customer_id_fk = "'.$_POST['customer_id'].'"':'';
			
			
			$cond_c = (isset($_POST['category_id_fk']) && ($_POST['category_id_fk']) > 0)? ' AND P.category_id_fk = "'.$_POST['category_id_fk'].'"':'';


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

			$cond2 = '';
			if(isset($_POST['product_id']) && $_POST['product_id'] !=0){ 
				$cond1 = ' AND P.product_id ='.$_POST['product_id'];
			}

			$cond_sub = $cond_d . ' ' . $cond_s . ' ' . $cond_cus ;


			$cond   = $cond_c . ' ' . $cond1 . ' ' . $cond2 ;

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

			$res = $db->query('SELECT
					
									customer_full_name,
									invoice_date,
									invoice_number as invoice_no,
									sell_item_quantity as sold_quantity,

									(sell_item_quantity * item_buy_price_per_unit) as sold_quantity_cost,
									(sell_item_quantity * item_sell_price_per_unit) as sold_quantity_sell,
									SI.actual_discount,
									((sell_item_quantity * item_sell_price_per_unit) - SI.actual_discount) as total_sell,


								

								
					


					

								stock_id,
								stock_name,
								product_id,
								product_name,
								category,
								P.sell_price
								
							FROM
								product  P

								LEFT JOIN stock S ON (S.is_deleted=0)
								LEFT JOIN category C ON (P.category_id_fk = C.category_id)
								LEFT JOIN invoice I ON (S.stock_id = I.stock_id_fk)
								LEFT JOIN sell_item SI  ON (SI.invoice_id_fk = I.invoice_id)
								LEFT JOIN customer CUS ON (I.customer_id_fk = CUS.customer_id)
								
								
							WHERE
								( P.is_deleted = 0 AND SI.product_id_fk = P.product_id AND  SI.is_deleted =0 AND I.is_returned = 1  AND I.is_deleted=0  '.$cond.' '.$cond_sub.') 

					 		'); // all type
			if ($res) {				
				
				
				while ($row = $res->fetch_assoc()) {				   
						$result['product_id']  			= $row['product_id'];
						$result['product_name']  		= $row['product_name'];
						$result['category']  			= $row['category'];
						$result['invoice_no']  			= $row['invoice_no'];
						$result['invoice_date']  		= $row['invoice_date'];
						$result['customer_full_name']  	= $row['customer_full_name'];
						$result['stock_name']  			= $row['stock_name'];
							
						$result['sold_quantity']  		= $row['sold_quantity'];
						$result['sold_quantity_cost']  	= $row['sold_quantity_cost'];
						$result['sold_quantity_sell']  	= $row['sold_quantity_sell'];
						$result['sell_price']  			= $row['sell_price'];					
						$result['actual_discount']  	= $row['actual_discount'];					
						$result['total_sell']  			= $row['total_sell'];					
						
						array_push($op['data'],$result);
						
				}

				$op['is_success'] = true;
				echo json_encode($op);
				exit;
			}else{
				
				$op['is_success'] = false;
				$op['data'] 	  = $db->error;
				echo json_encode($op);

				exit;
			}
			
		}

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

			
			

			$cond_d = (isset($_POST['d1']) && isset($_POST['d2']))? ' AND (SI.sell_date) BETWEEN "'.$_POST['d1'].'" AND "'.$_POST['d2'].'"':'';


			$cond_s = (isset($_POST['saleman_id']) && ($_POST['saleman_id']) > 0)? ' AND I.saleman_id_fk = "'.$_POST['saleman_id'].'"':'';
			
			$cond_cus = (isset($_POST['customer_id']) && ($_POST['customer_id']) > 0)? ' AND I.customer_id_fk = "'.$_POST['customer_id'].'"':'';
			
			
			$cond_c = (isset($_POST['category_id_fk']) && ($_POST['category_id_fk']) > 0)? ' AND P.category_id_fk = "'.$_POST['category_id_fk'].'"':'';


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

			$cond2 = '';
			if(isset($_POST['product_id']) && $_POST['product_id'] !=0){ 
				$cond1 = ' AND P.product_id ='.$_POST['product_id'];
			}

			$cond_sub = $cond_d . ' ' . $cond_s . ' ' . $cond_cus ;


			$cond   = $cond_c . ' ' . $cond1 . ' ' . $cond2 ;

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

			$res = $db->query('SELECT
					
									customer_full_name,
									invoice_date,
									invoice_number as invoice_no,
									sell_item_quantity as sold_quantity,

									(sell_item_quantity * item_buy_price_per_unit) as sold_quantity_cost,
									(sell_item_quantity * item_sell_price_per_unit) as sold_quantity_sell,

									SI.actual_discount,
									((sell_item_quantity * item_sell_price_per_unit) - SI.actual_discount) as total_sell,


								

								
					


					

								stock_id,
								stock_name,
								product_id,
								product_name,
								category,
								P.sell_price
								
							FROM
								product  P

								LEFT JOIN stock S ON (S.is_deleted=0)
								LEFT JOIN category C ON (P.category_id_fk = C.category_id)
								LEFT JOIN invoice I ON (S.stock_id = I.stock_id_fk)
								LEFT JOIN sell_item SI  ON (SI.invoice_id_fk = I.invoice_id)
								LEFT JOIN customer CUS ON (I.customer_id_fk = CUS.customer_id)
								
								
							WHERE
								( P.is_deleted = 0 AND SI.product_id_fk = P.product_id AND  SI.is_deleted =0 AND I.is_returned = 0  AND I.is_deleted=0  '.$cond.' '.$cond_sub.') 

					 		'); // all type
			if ($res) {				
				
				
				while ($row = $res->fetch_assoc()) {				   
						$result['product_id']  			= $row['product_id'];
						$result['product_name']  		= $row['product_name'];
						$result['category']  			= $row['category'];
						$result['invoice_no']  			= $row['invoice_no'];
						$result['invoice_date']  		= $row['invoice_date'];
						$result['customer_full_name']  	= $row['customer_full_name'];
						$result['stock_name']  			= $row['stock_name'];
							
						$result['sold_quantity']  		= $row['sold_quantity'];
						$result['sold_quantity_cost']  	= $row['sold_quantity_cost'];
						$result['sold_quantity_sell']  	= $row['sold_quantity_sell'];
						$result['sell_price']  			= $row['sell_price'];
						$result['actual_discount']  	= $row['actual_discount'];					
						$result['total_sell']  			= $row['total_sell'];						
						
						array_push($op['data'],$result);
						
				}

				$op['is_success'] = true;
				echo json_encode($op);
				exit;
			}else{
				
				$op['is_success'] = false;
				$op['data'] 	  = $db->error;
				echo json_encode($op);

				exit;
			}
			
		}

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

			
			$category_id_fk = $_POST['category_id_fk'];
			$branch_id_fk 	= $_POST['branch_id_fk'];


			$cond_d = (isset($_POST['category_id_fk']) && ($_POST['category_id_fk']) > 0)? ' AND P.category_id_fk = "'.$_POST['category_id_fk'].'"':'';

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

			$cond   = $cond_d . ' ' . $cond1 ;

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

			$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 = "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 = "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 = "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 = "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 = "Expired"  '.$cond_sub.'),0)
					) 


					) AS on_hand_quantity,

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

					 '); // all type
			if ($res) {				
				
				
				while ($row = $res->fetch_assoc()) {				   
						$result['product_id']  			= $row['product_id'];
						$result['product_name']  		= $row['product_name'];
						$result['category']  			= $row['category'];
						$result['stock_name']  			= $row['stock_name'];
							
						$result['recieved_quantity']  	= $row['recieved_quantity'];
						$result['sold_quantity']  		= $row['sold_quantity'];
						$result['damage_quantity']  	= $row['damage_quantity'];
						$result['on_hand_quantity']  	= $row['on_hand_quantity'];
						$result['sell_price']  			= $row['sell_price'];					
						
						array_push($op['data'],$result);
						
				}

				$op['is_success'] = true;
				echo json_encode($op);
				exit;
			}else{
				
				$op['is_success'] = false;
				$op['data'] 	  = $db->error;
				echo json_encode($op);

				exit;
			}
			
		}

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

			
			

			$cond_d = (isset($_POST['d1']) && isset($_POST['d2']))? ' AND (SI.sell_date) BETWEEN "'.$_POST['d1'].'" AND "'.$_POST['d2'].'"':'';


			$cond_s = (isset($_POST['saleman_id']) && ($_POST['saleman_id']) > 0)? ' AND I.saleman_id_fk = "'.$_POST['saleman_id'].'"':'';
			
			$cond_cus = (isset($_POST['customer_id']) && ($_POST['customer_id']) > 0)? ' AND I.customer_id_fk = "'.$_POST['customer_id'].'"':'';
			
			
			$cond_c = (isset($_POST['category_id_fk']) && ($_POST['category_id_fk']) > 0)? ' AND P.category_id_fk = "'.$_POST['category_id_fk'].'"':'';


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

			$cond2 = '';
			if(isset($_POST['product_id']) && $_POST['product_id'] !=0){ 
				$cond1 = ' AND P.product_id ='.$_POST['product_id'];
			}

			$cond_sub = $cond_d . ' ' . $cond_s . ' ' . $cond_cus ;


			$cond   = $cond_c . ' ' . $cond1 . ' ' . $cond2 ;

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

			$res = $db->query('SELECT
					
					

					(COALESCE((SELECT sum(sell_item_quantity) FROM invoice I

						LEFT JOIN sell_item SI  ON (SI.invoice_id_fk = I.invoice_id)
					    WHERE SI.product_id_fk = P.product_id AND  SI.is_deleted =0 AND I.is_returned = 0  AND I.is_deleted=0 AND S.stock_id = I.stock_id_fk '.$cond_sub.'),0)
					) AS sold_quantity,

					(COALESCE((SELECT sum(sell_item_quantity * item_buy_price_per_unit) FROM invoice I

						LEFT JOIN sell_item SI  ON (SI.invoice_id_fk = I.invoice_id)
					    WHERE SI.product_id_fk = P.product_id AND  SI.is_deleted =0 AND I.is_returned = 0  AND I.is_deleted=0 AND S.stock_id = I.stock_id_fk '.$cond_sub.'),0)
					) AS sold_quantity_cost,

					(COALESCE((SELECT sum(sell_item_quantity * item_sell_price_per_unit) FROM invoice I

						LEFT JOIN sell_item SI  ON (SI.invoice_id_fk = I.invoice_id)
					    WHERE SI.product_id_fk = P.product_id AND  SI.is_deleted =0 AND I.is_returned = 0  AND I.is_deleted=0 AND S.stock_id = I.stock_id_fk '.$cond_sub.'),0)
					) AS sold_quantity_sell,

					(COALESCE((SELECT sum(SI.actual_discount) FROM invoice I

						LEFT JOIN sell_item SI  ON (SI.invoice_id_fk = I.invoice_id)
					    WHERE SI.product_id_fk = P.product_id AND  SI.is_deleted =0 AND I.is_returned = 0  AND I.is_deleted=0 AND S.stock_id = I.stock_id_fk '.$cond_sub.'),0)
					) AS actual_discount,

					(COALESCE((SELECT sum((sell_item_quantity * item_sell_price_per_unit) - SI.actual_discount)  FROM invoice I

						LEFT JOIN sell_item SI  ON (SI.invoice_id_fk = I.invoice_id)
					    WHERE SI.product_id_fk = P.product_id AND  SI.is_deleted =0 AND I.is_returned = 0  AND I.is_deleted=0 AND S.stock_id = I.stock_id_fk '.$cond_sub.'),0)
					) AS total_sell,

					


					

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

					 '); // all type
			if ($res) {				
				
				
				while ($row = $res->fetch_assoc()) {				   
						$result['product_id']  			= $row['product_id'];
						$result['product_name']  		= $row['product_name'];
						$result['category']  			= $row['category'];
						$result['stock_name']  			= $row['stock_name'];
							
						$result['sold_quantity']  		= $row['sold_quantity'];
						$result['sold_quantity_cost']  	= $row['sold_quantity_cost'];
						$result['sold_quantity_sell']  	= $row['sold_quantity_sell'];
						$result['sell_price']  			= $row['sell_price'];
						$result['actual_discount']  	= $row['actual_discount'];					
						$result['total_sell']  			= $row['total_sell'];						
											
						
						array_push($op['data'],$result);
						
				}

				$op['is_success'] = true;
				echo json_encode($op);
				exit;
			}else{
				
				$op['is_success'] = false;
				$op['data'] 	  = $db->error;
				echo json_encode($op);

				exit;
			}
			
		}

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

			$d1 			= $_POST['d1'];
			$d2 			= $_POST['d2'];
			$branch_id_fk 	= $_POST['branch_id_fk'];


			$cond_d = (isset($_POST['d1']) && isset($_POST['d2']))? ' AND (P.purchase_date) BETWEEN "'.$_POST['d1'].'" AND "'.$_POST['d2'].'"':'';

			
			$cond1 = '';
			if(isset($_POST['branch_id_fk']) && $_POST['branch_id_fk'] !=0){ 
				$cond1 = ' AND P.branch_id_fk ='.$_POST['branch_id_fk'];
			}

			$cond   = $cond_d . ' ' . $cond1 ;

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

			$res = $db->query("SELECT
								purchase_id,
								purchase_number,
								purchase_auto_number,
								purchase_date,
								purchase_note,
								purchase_sequence_no,
								purchase_transaction,
								supplier_id_fk,
								supplier_full_name,
								P.stock_id_fk,
								purchase_item_id,
								purchase_quantity,
								attach_bill,
								cost_price,
								stock_name,
								branch_name,

								sum(cost_expenses * purchase_quantity) AS total_price,
								sum(cost_price * purchase_quantity) AS total_price_before_expenses
								
								 FROM
								 	 purchase P
								 	 LEFT JOIN stock S ON (S.stock_id = P.stock_id_fk)
								 	 LEFT JOIN branch B ON (B.branch_id = P.branch_id_fk)
								 	 LEFT JOIN suppliers SU ON (SU.supplier_id = P.supplier_id_fk)
								 	 , purchase_item PP 
								WHERE 
								(PP.purchase_id_fk = P.purchase_id AND P.is_deleted = 0 AND PP.is_deleted = 0 $cond )  GROUP BY PP.purchase_id_fk 

					 "); // all type
			if ($res) {				
				
				
				while ($row = $res->fetch_assoc()) {				   
						$result['purchase_id']  			= $row['purchase_id'];
						$result['purchase_number']  		= $row['purchase_number'];
						$result['purchase_auto_number']  	= $row['purchase_auto_number'];
						$result['purchase_note']  			= $row['purchase_note'];

						$dt 							= new DateTime($row['purchase_date']);
						$time 							= $dt->format('Y-m-d');

						$result['purchase_date']  		= $time;				
						$result['stock_name']  			= $row['stock_name'];
						$result['total_price_before_expenses']  				= $row['total_price_before_expenses'];
						$result['total_price']  	= $row['total_price'];
						$result['supplier_full_name']  		= $row['supplier_full_name'];
										
						
						array_push($op['data'],$result);
						
				}

				$op['is_success'] = true;
				echo json_encode($op);
				exit;
			}else{
				
				$op['is_success'] = false;
				$op['data'] 	  = $db->error;
				echo json_encode($op);

				exit;
			}
			
		}


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

			$d1 			= $_POST['d1'];
			$d2 			= $_POST['d2'];
			$branch_id_fk 	= $_POST['branch_id_fk'];


			$cond_d = (isset($_POST['d1']) && isset($_POST['d2']))? ' AND (PY.paid_date) BETWEEN "'.$_POST['d1'].'" AND "'.$_POST['d2'].'"':'';

			
			$cond1 = '';
			if(isset($_POST['branch_id_fk']) && $_POST['branch_id_fk'] !=0){ 
				$cond1 = ' AND P.branch_id_fk ='.$_POST['branch_id_fk'];
			}

			$cond2 = '';
			if(isset($_POST['supplier_id']) && $_POST['supplier_id'] !=0){ 
				$cond2 = ' AND PY.supplier_id_fk ='.$_POST['supplier_id'];
			}

			$cond   = $cond_d . ' ' . $cond1 . ' ' . $cond2 ;

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

			$res = $db->query("
								SELECT
									payment_id,
									payment_number,
									pay_usd_rate,
									pay_currency,
									paid_amount,
									paid_date,
									payment_note,
									payment_transaction,
									purchase_id_fk,
									PY.supplier_id_fk,
									paper_number,
									purchase_number,
									purchase_auto_number,
									supplier_full_name
									
								FROM
									supplier_payments PY
									
									LEFT JOIN suppliers S ON (PY.supplier_id_fk = S.supplier_id )
									LEFT JOIN purchase P ON (PY.purchase_id_fk = P.purchase_id )
									
								WHERE
									( PY.is_deleted = 0 $cond) 

								"); // all type
			if ($res) {				
				
				
				while ($row = $res->fetch_assoc()) {				   
						$result['payment_id']  				= $row['payment_id'];
						$result['supplier_full_name']  		= $row['supplier_full_name'];
						$result['payment_number']  			= $row['payment_number'];
						$result['paid_amount']  			= $row['paid_amount'];

						$dt 								= new DateTime($row['paid_date']);
						$time 								= $dt->format('Y-m-d');

						$result['paid_date']  				= $time;				
						
						$result['payment_note']  			= $row['payment_note'];
						$result['purchase_auto_number']  	= $row['purchase_auto_number'];
										
						
						array_push($op['data'],$result);
						
				}

				$op['is_success'] = true;
				echo json_encode($op);
				exit;
			}else{
				
				$op['is_success'] = false;
				$op['data'] 	  = $db->error;
				echo json_encode($op);

				exit;
			}
			
		}


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

			$d1 			= $_POST['d1'];
			$d2 			= $_POST['d2'];
			$branch_id_fk 	= $_POST['branch_id_fk'];


			$cond_d = (isset($_POST['d1']) && isset($_POST['d2']))? ' AND (E.expenses_date) BETWEEN "'.$_POST['d1'].'" AND "'.$_POST['d2'].'"':'';

			
			$cond1 = '';
			if(isset($_POST['branch_id_fk']) && $_POST['branch_id_fk'] !=0){ 
				$cond1 = ' AND E.branch_id_fk ='.$_POST['branch_id_fk'];
			}

			$cond   = $cond_d . ' ' . $cond1 ;

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

			$res = $db->query("
								SELECT
									expenses_id,
									expenses_name,
									expenses_quantity,
									expenses_unit_price,
									expenses_date,
									expenses_usd_rate,
									expenses_currency,
									expenses_note,
									bill_number,
									attach_bill,
									E.branch_id_fk,
									E.expenses_group_id_fk,
									branch_name,
									group_name
								FROM
									expenses E
									LEFT JOIN expenses_group EG ON (E.expenses_group_id_fk = EG.expenses_group_id AND EG.is_deleted=0)
									LEFT JOIN branch B ON (E.branch_id_fk = B.branch_id )
									
									
								WHERE
									( E.is_deleted = 0 $cond ) 

								"); // all type
			if ($res) {				
				
				
				while ($row = $res->fetch_assoc()) {				   
						$result['expenses_id']  		= $row['expenses_id'];
						$result['expenses_name']  		= $row['expenses_name'];
						$result['expenses_unit_price']  = $row['expenses_unit_price'];
						$result['expenses_note']  		= $row['expenses_note'];

						$dt 							= new DateTime($row['expenses_date']);
						$time 							= $dt->format('Y-m-d');

						$result['expenses_date']  		= $time;				
						
						$result['bill_number']  		= $row['bill_number'];
						$result['branch_name']  		= $row['branch_name'];
										
						
						array_push($op['data'],$result);
						
				}

				$op['is_success'] = true;
				echo json_encode($op);
				exit;
			}else{
				
				$op['is_success'] = false;
				$op['data'] 	  = $db->error;
				echo json_encode($op);

				exit;
			}
			
		}

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

			$d1 			= $_POST['d1'];
			$d2 			= $_POST['d2'];
			$saleman_id 	= $_POST['saleman_id'];
			$branch_id_fk 	= $_POST['branch_id_fk'];
			

			$cond_d = (isset($_POST['d1']) && isset($_POST['d2']))? ' AND (Inv.invoice_date) BETWEEN "'.$_POST['d1'].'" AND "'.$_POST['d2'].'"':'';

			
			$cond1 = '';
			if(isset($_POST['branch_id_fk']) && $_POST['branch_id_fk'] !=0){ 
				$cond1 = ' AND Inv.branch_id_fk ='.$_POST['branch_id_fk'];
			}

			$cond2 = '';
			if(isset($_POST['saleman_id']) && $_POST['saleman_id'] !=0){ 
				$cond2 = ' AND Inv.saleman_id_fk ='.$_POST['saleman_id'];
			}

			$cond_c = '';
			if(isset($_POST['customer_id']) && $_POST['customer_id'] !=0){ 
				$cond_c = ' AND Inv.customer_id_fk ='.$_POST['customer_id'];
			}

			$cond   = $cond_d . ' ' . $cond1 . ' ' . $cond2 . ' ' . $cond_c;

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

			$res = $db->query("SELECT
									invoice_id,
									invoice_number,
									invoice_date,
									Inv.actual_discount,
									invoice_total_net_balance,
									customer_id_fk,
									customer_full_name AS name,
									invoice_note,
									invoice_transaction,
									Inv.created_at, 
									full_name,
									branch_name,
									saleman_full_name, 
									

									(invoice_total_net_balance) AS final_price
											
						
								FROM
								 invoice Inv
								 LEFT JOIN customer Cus ON (Inv.customer_id_fk=Cus.customer_id) 
								 LEFT JOIN staff Stf 	ON (Inv.created_by 		= Stf.staff_id) 
					 			 LEFT JOIN saleman SM 	ON (Inv.saleman_id_fk 	= SM.saleman_id) 
					 			 LEFT JOIN branch B 	ON (Inv.branch_id_fk 	= B.branch_id) 
								 
							WHERE
								(Inv.is_deleted = 0  AND Inv.invoice_status <3 AND Inv.is_returned =0 AND Inv.invoice_transaction ='Invoice'  $cond) ORDER BY invoice_id DESC

					 "); // all type
			if ($res) {				
				
				
				while ($row = $res->fetch_assoc()) {				   
						$result['invoice_id']  			= $row['invoice_id'];
						$result['invoice_number']  		= $row['invoice_number'];
						$result['final_price']  		= $row['final_price'];
						$result['actual_discount']  	= $row['actual_discount'];

						$dt 							= new DateTime($row['created_at']);
						$time 							= $dt->format('Y-m-d');

						$result['time']  				= $time;				
						$result['customer_name']  		= $row['name'];
						$result['staff']  				= $row['full_name'];
						$result['saleman_full_name']  	= $row['saleman_full_name'];
						$result['branch_name']  		= $row['branch_name'];
						$result['invoice_transaction']  = $row['invoice_transaction'];					
						$result['invoice_note']  		= $row['invoice_note'];					
						
						array_push($op['data'],$result);
						
				}

				$op['is_success'] = true;
				echo json_encode($op);
				exit;
			}else{
				
				$op['is_success'] = false;
				$op['data'] 	  = $db->error;
				echo json_encode($op);

				exit;
			}
			
		}


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

			$d1 			= $_POST['d1'];
			$d2 			= $_POST['d2'];
			
			$branch_id_fk 	= $_POST['branch_id_fk'];
			

			$cond_d = (isset($_POST['d1']) && isset($_POST['d2']))? ' AND (Inv.invoice_date) BETWEEN "'.$_POST['d1'].'" AND "'.$_POST['d2'].'"':'';

			
			$cond1 = '';
			if(isset($_POST['branch_id_fk']) && $_POST['branch_id_fk'] !=0){ 
				$cond1 = ' AND Inv.branch_id_fk ='.$_POST['branch_id_fk'];
			}

			$cond2 = '';
			

			$cond_c = '';
			

			$cond   = $cond_d . ' ' . $cond1 . ' ' . $cond2 . ' ' . $cond_c;

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

			$res = $db->query("SELECT
					

									sum((invoice_total_net_balance)) AS total_price
											
						
								 FROM
								 invoice Inv
								 
								 
							WHERE
								(Inv.is_deleted = 0 AND Inv.invoice_status <3 AND Inv.is_returned =0  AND Inv.invoice_transaction ='Invoice' $cond )"); // all type

			$resR = $db->query("SELECT
					

									sum((invoice_total_net_balance)) AS total_invoice_return
											
						
								 FROM
								 invoice Inv
								 
								 
							WHERE
								(Inv.is_deleted = 0 AND Inv.invoice_status <3 AND Inv.is_returned =1  AND Inv.invoice_transaction ='Invoice' $cond )"); // all type

			$res1 = $db->query("SELECT
					



									sum((sell_item_quantity * item_buy_price_per_unit) ) AS total_cost
											
						
								 FROM
								 invoice Inv

								 LEFT JOIN sell_item SI ON (SI.invoice_id_fk = Inv.invoice_id)
								 
								 
							WHERE
								(Inv.is_deleted = 0 AND Inv.invoice_status <3 AND Inv.is_returned =0 AND Inv.invoice_transaction ='Invoice' $cond )"); // all type

			$res3 = $db->query("SELECT
					



									sum((sell_item_quantity * item_buy_price_per_unit) ) AS total_return_cost
											
						
								 FROM
								 invoice Inv

								 LEFT JOIN sell_item SI ON (SI.invoice_id_fk = Inv.invoice_id)
								 
								 
							WHERE
								(Inv.is_deleted = 0 AND Inv.invoice_status <3 AND Inv.is_returned =1 AND Inv.invoice_transaction ='Invoice' $cond )"); // all type


			$cond_e = (isset($_POST['d1']) && isset($_POST['d2']))? ' AND (E.expenses_date) BETWEEN "'.$_POST['d1'].'" AND "'.$_POST['d2'].'"':'';

			
			$cond1e = '';
			if(isset($_POST['branch_id_fk']) && $_POST['branch_id_fk'] !=0){ 
				$cond1e = ' AND E.branch_id_fk ='.$_POST['branch_id_fk'];
			}

			$conde   = $cond_e . ' ' . $cond1e ;

			

			$rese = $db->query("
								SELECT
									
									sum(expenses_unit_price) as total_expenses
								FROM
									expenses E
									
									LEFT JOIN branch B ON (E.branch_id_fk = B.branch_id )
									
									
								WHERE
									( E.is_deleted = 0 $conde ) 

								"); // all type
			

			if ($res && $res1 && $rese  && $res3) {				
				$row 	= $res->fetch_assoc();
				$row1 	= $res1->fetch_assoc();
				$row2 	= $rese->fetch_assoc();
				$row3 	= $resR->fetch_assoc();
				$row4 	= $res3->fetch_assoc();

				$result['a1']  				= $row['total_price'] - $row1['total_cost'];
				$result['a2']  				= -1* $row3['total_invoice_return'] - $row4['total_return_cost'];
				$result['total_profits']  	= $result['a1'] - $result['a2'] - $row2['total_expenses'];

				// + $row3['total_invoice_return'] because its already computed as minus price
				$result['total_price']  				= $row['total_price'];
				$result['total_invoice_cost']  			= $row1['total_cost'];
				$result['total_expenses']  				= $row2['total_expenses'];
				$result['total_invoice_return']  		= -1*$row3['total_invoice_return'];
				$result['total_invoice_return_cost']  	= $row4['total_return_cost'];
					
				
						
				array_push($op['data'],$result);
						
				

				$op['is_success'] = true;
				echo json_encode($op);
				exit;
			}else{
				
				$op['is_success'] = false;
				$op['data'] 	  = $db->error;
				echo json_encode($op);

				exit;
			}
			
		}


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

			$d1 			= $_POST['d1'];
			$d2 			= $_POST['d2'];
			$branch_id_fk 	= $_POST['branch_id_fk'];


			$cond_v = (isset($_POST['d1']) && isset($_POST['d2']))? ' AND (I.invoice_date) BETWEEN "'.$_POST['d1'].'" AND "'.$_POST['d2'].'"':'';
			$cond_p = (isset($_POST['d1']) && isset($_POST['d2']))? ' AND (IP.paid_date) BETWEEN "'.$_POST['d1'].'" AND "'.$_POST['d2'].'"':'';

			
			$cond1 = '';
			if(isset($_POST['branch_id_fk']) && $_POST['branch_id_fk'] !=0){ 
				$cond1 = ' AND C.branch_id_fk ='.$_POST['branch_id_fk'];
			}

			$cond_c = '';
			if(isset($_POST['customer_id']) && $_POST['customer_id'] !=0){ 
				$cond_c = ' AND C.customer_id ='.$_POST['customer_id'];
			}

			$cond3 = '';

			if(isset($_SESSION['branch_id']) && $_SESSION['branch_id'] !=0){ 
				$cond3 = ' AND C.branch_id_fk ='.$_SESSION['branch_id'];
			}

			$cond   = $cond_c . ' ' . $cond1 . $cond3;

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

			$res = $db->query("SELECT 	
									customer_id,
									customer_full_name,
									customer_phone,
									customer_email,
									customer_address,
									customer_company,
									branch_name,
	
				          			(
				          			  
									COALESCE((SELECT sum(invoice_total_net_balance) FROM invoice I
											
											WHERE  I.customer_id_fk = C.customer_id AND I.invoice_transaction='Invoice' AND I.invoice_status <3  AND I.is_deleted = 0  $cond_v ),0)
										
								      	+
								      COALESCE((SELECT sum(withdraw_amount) FROM capital_withdraw CW1 WHERE  CW1.is_deleted = 0 AND CW1.staff_customer=0  AND CW1.gived_to_id_fk = C.customer_id ),0)

								    ) AS total_invoice_price,
								   
								  	
								    (
										COALESCE((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 ='Payment' $cond_p),0)
										-
										COALESCE((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' $cond_p),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' $cond_p
									) AS total_payment_discount,

									
				        				
	        
									(SELECT COUNT(*) FROM invoice I
										WHERE I.invoice_status <3 AND  I.is_deleted = 0  AND  I.customer_id_fk = C.customer_id  $cond_v 
									) as No_of_inv,

									(SELECT GROUP_CONCAT(invoice_date) FROM invoice I
										WHERE I.invoice_status <3 AND  I.is_deleted = 0  AND  I.customer_id_fk = C.customer_id  $cond_v 
									) as invoice_date,

									(SELECT GROUP_CONCAT(invoice_number) FROM invoice I
										WHERE I.invoice_status <3 AND  I.is_deleted = 0  AND  I.customer_id_fk = C.customer_id  $cond_v 
									) as invoice_number,
									(SELECT GROUP_CONCAT(invoice_total_net_balance) FROM invoice I
										WHERE I.invoice_status <3 AND  I.is_deleted = 0  AND  I.customer_id_fk = C.customer_id  $cond_v 
									) as invoice_price


							FROM customer C 
								LEFT JOIN branch B ON (C.branch_id_fk = B.branch_id )

							WHERE C.is_deleted=0 $cond

					 		"); // all type
			if ($res) {				
				
				
				while ($row = $res->fetch_assoc()) {				   
						$result['customer_id']  			= $row['customer_id'];
						$result['customer_full_name']  		= $row['customer_full_name'];
						$result['customer_phone']  			= $row['customer_phone'];
						$result['total_invoice_price']  	= $row['total_invoice_price'];

								
						$result['total_payment_discount']  	= $row['total_payment_discount'];
						$result['total_paid']  				= $row['total_paid'];
						$result['No_of_inv']  				= $row['No_of_inv'];
						$result['branch_name']  			= $row['branch_name'];
						$result['invoice_number']  			= $row['invoice_number'];
						$result['invoice_date']  			= $row['invoice_date'];
						$result['invoice_price']  			= $row['invoice_price'];
						
						
						array_push($op['data'],$result);
						
				}

				$op['is_success'] = true;
				echo json_encode($op);
				exit;
			}else{
				
				$op['is_success'] = false;
				$op['data'] 	  = $db->error;
				echo json_encode($op);

				exit;
			}
			
		}


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

			$d1 			= $_POST['d1'];
			$d2 			= $_POST['d2'];
			$loaner_id 		= $_POST['loaner_id'];
			

			$cond_d = (isset($_POST['d1']) && isset($_POST['d2']))? ' AND (L.paid_date) BETWEEN "'.$_POST['d1'].'" AND "'.$_POST['d2'].'"':'';

			
			$cond1 = '';
			if(isset($_POST['loaner_id']) && $_POST['loaner_id'] !=0){ 
				$cond1 = ' AND L.loaner_id_fk ='.$_POST['loaner_id'];
			}

			

			$cond   = $cond_d . ' ' . $cond1 ;

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

			$res = $db->query("SELECT
									payment_id,
									payment_number,
									pay_usd_rate,
									pay_currency,
									paid_amount,
									paid_date,
									payment_note,
									payment_transaction,
									loaner_id_fk,
									paper_number,
									
									loaner_full_name,
									(SELECT sum(paid_amount) FROM loans L  WHERE is_deleted =0 AND payment_transaction ='Refund' $cond) AS total_refund,
									(SELECT sum(paid_amount) FROM loans L  WHERE is_deleted =0 AND payment_transaction ='Loan' $cond) AS total_loan
								FROM
									loans L
									
									LEFT JOIN loaners LO ON (L.loaner_id_fk = LO.loaner_id )
									
								WHERE
									( L.is_deleted = 0 $cond) 

					 "); // all type
			if ($res) {				
				
				
				while ($row = $res->fetch_assoc()) {				   
						$result['payment_id']  			= $row['payment_id'];
						$result['payment_number']  		= $row['payment_number'];
						$result['paid_amount']  		= $row['paid_amount'];
						$result['payment_note']  		= $row['payment_note'];

						$dt 							= new DateTime($row['paid_date']);
						$time 							= $dt->format('Y-m-d');

						$result['paid_date']  			= $time;				
						$result['payment_transaction']  = $row['payment_transaction'];
						$result['paper_number']  		= $row['paper_number'];
						$result['loaner_full_name']  	= $row['loaner_full_name'];
						$result['total_refund']  		= $row['total_refund'];
						$result['total_loan']  			= $row['total_loan'];					
						
						array_push($op['data'],$result);
						
				}

				$op['is_success'] = true;
				echo json_encode($op);
				exit;
			}else{
				
				$op['is_success'] = false;
				$op['data'] 	  = $db->error;
				echo json_encode($op);

				exit;
			}
			
		}


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

			$d1 			= $_POST['d1'];
			$d2 			= $_POST['d2'];
			$branch_id_fk 	= $_POST['branch_id_fk'];


			$cond_d = (isset($_POST['d1']) && isset($_POST['d2']))? ' AND (E.expenses_date) BETWEEN "'.$_POST['d1'].'" AND "'.$_POST['d2'].'"':'';

			
			$cond1 = '';
			if(isset($_POST['branch_id_fk']) && $_POST['branch_id_fk'] !=0){ 
				$cond1 = ' AND E.branch_id_fk ='.$_POST['branch_id_fk'];
			}

			$cond   = $cond_d . ' ' . $cond1 ;

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

			$res = $db->query("
								SELECT
									asset_id,
									expenses_name,
									expenses_quantity,
									expenses_unit_price,
									expenses_date,
									expenses_usd_rate,
									expenses_currency,
									expenses_note,
									bill_number,
									attach_bill,
									E.branch_id_fk,
									
									branch_name
								FROM
									assets E
									
									LEFT JOIN branch B ON (E.branch_id_fk = B.branch_id )
									
									
								WHERE
									( E.is_deleted = 0 $cond )

								"); // all type
			if ($res) {				
				
				
				while ($row = $res->fetch_assoc()) {				   
						$result['asset_id']  			= $row['asset_id'];
						$result['expenses_name']  		= $row['expenses_name'];
						$result['expenses_unit_price']  = $row['expenses_unit_price'];
						$result['expenses_note']  		= $row['expenses_note'];

						$dt 							= new DateTime($row['expenses_date']);
						$time 							= $dt->format('Y-m-d');

						$result['expenses_date']  		= $time;				
						
						$result['bill_number']  		= $row['bill_number'];
						$result['branch_name']  		= $row['branch_name'];
										
						
						array_push($op['data'],$result);
						
				}

				$op['is_success'] = true;
				echo json_encode($op);
				exit;
			}else{
				
				$op['is_success'] = false;
				$op['data'] 	  = $db->error;
				echo json_encode($op);

				exit;
			}
			
		}


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

			$d1 			= $_POST['d1'];
			$d2 			= $_POST['d2'];
			$owner_id 		= $_POST['owner_id'];


			$cond_d = (isset($_POST['d1']) && isset($_POST['d2']))? ' AND (Cap.date_of_add) BETWEEN "'.$_POST['d1'].'" AND "'.$_POST['d2'].'"':'';

			
			$cond1 = '';
			if(isset($_POST['owner_id']) && $_POST['owner_id'] !=0){ 
				$cond1 = ' AND Cap.staff_id_fk ='.$_POST['owner_id'];
			}

			$cond   = $cond_d . ' ' . $cond1 ;

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

			$res = $db->query("
								SELECT
										capital_id,
										capital_number,
										capital_amount,
										date_of_add,
										staff_id_fk,
										
										capital_usd_rate,
										capital_currency,
										capital_note,
										O.owner_full_name,
										
										capital_transaction,
										(SELECT sum(capital_amount) FROM capital Cap  WHERE Cap.is_deleted =0 AND Cap.capital_transaction='Add Capital Amount' $cond) AS total_amount


									FROM
										capital Cap
										LEFT JOIN owner O ON (Cap.staff_id_fk = O.owner_id )
										
										
									WHERE
										( Cap.is_deleted = 0  $cond) 

								"); // all type
			if ($res) {				
				
				
				while ($row = $res->fetch_assoc()) {				   
						$result['capital_id']  			= $row['capital_id'];
						$result['capital_number']  		= $row['capital_number'];
						$result['capital_amount']  		= $row['capital_amount'];
						$result['capital_note']  		= $row['capital_note'];

						$dt 							= new DateTime($row['date_of_add']);
						$time 							= $dt->format('Y-m-d');

						$result['date_of_add']  		= $time;				
						
						$result['capital_transaction']  = $row['capital_transaction'];
						$result['owner_full_name']  	= $row['owner_full_name'];
						$result['total_amount']  		= $row['total_amount'];
										
						
						array_push($op['data'],$result);
						
				}

				$op['is_success'] = true;
				echo json_encode($op);
				exit;
			}else{
				
				$op['is_success'] = false;
				$op['data'] 	  = $db->error;
				echo json_encode($op);

				exit;
			}
			
		}


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

			$d1 			= $_POST['d1'];
			$d2 			= $_POST['d2'];
			$branch_id_fk 	= $_POST['branch_id_fk'];


			$cond_d = (isset($_POST['d1']) && isset($_POST['d2']))? ' AND (SF.date_of_add) BETWEEN "'.$_POST['d1'].'" AND "'.$_POST['d2'].'"':'';

			
			$cond1 = '';
			if(isset($_POST['branch_id_fk']) && $_POST['branch_id_fk'] !=0){ 
				$cond1 = ' AND SF.branch_id_fk ='.$_POST['branch_id_fk'];
			}

			$cond   = $cond_d . ' ' . $cond1 ;

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

			$res = $db->query("SELECT
									safe_id,
									safe_number,
									safe_amount,
									date_of_add,
									safe_transaction,
									
									transaction_type,
									capital_currency,
									table_name,
									id_fk,
									safe_sequence_no,
									safe_note,
									staff_id_fk,
									SF.branch_id_fk,
									S.full_name,
									branch_name,
									
									
									(SELECT sum(safe_amount) FROM safe SF  WHERE SF.is_deleted =0 AND transaction_type =1 $cond) AS total_amount,

									(SELECT sum(safe_amount) FROM safe SF  WHERE SF.is_deleted =0 AND transaction_type =2 $cond) AS total_subtract
								FROM
									safe SF
									LEFT JOIN staff S ON (SF.staff_id_fk = S.staff_id )
									LEFT JOIN branch B ON (SF.branch_id_fk = B.branch_id )
									
								WHERE
									( SF.is_deleted = 0 $cond) 

					 "); // all type
			if ($res) {				
				
				
				while ($row = $res->fetch_assoc()) {				   
						$result['safe_id']  			= $row['safe_id'];
						$result['safe_number']  		= $row['safe_number'];
						$result['safe_amount']  		= $row['safe_amount'];
						$result['date_of_add']  		= $row['date_of_add'];

					
	
						$result['safe_transaction']  	= $row['safe_transaction'];
						$result['transaction_type']  	= $row['transaction_type'];
						$result['safe_note']  			= $row['safe_note'];
						$result['branch_name']  		= $row['branch_name'];
						$result['full_name']  			= $row['full_name'];					
						$result['total_amount']  		= $row['total_amount'];					
						$result['total_subtract']  		= $row['total_subtract'];					
						
						array_push($op['data'],$result);
						
				}

				$op['is_success'] = true;
				echo json_encode($op);
				exit;
			}else{
				
				$op['is_success'] = false;
				$op['data'] 	  = $db->error;
				echo json_encode($op);

				exit;
			}
			
		}


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

			$d1 			= $_POST['d1'];
			$d2 			= $_POST['d2'];
			$branch_id_fk 	= $_POST['branch_id_fk'];
			$saleman_id_fk 	= $_POST['saleman_id'];


			$cond_d = (isset($_POST['d1']) && isset($_POST['d2']))? ' AND (PY.paid_date) BETWEEN "'.$_POST['d1'].'" AND "'.$_POST['d2'].'"':'';

			
			$cond1 = '';
			if(isset($_POST['branch_id_fk']) && $_POST['branch_id_fk'] !=0){ 
				$cond1 = ' AND PY.branch_id_fk ='.$_POST['branch_id_fk'];
			}

			$cond2 = '';
			if(isset($_POST['saleman_id']) && $_POST['saleman_id'] !=0){ 
				$cond1 = ' AND PY.saleman_id_fk ='.$_POST['saleman_id'];
			}

			$cond   = $cond_d . ' ' . $cond1 . ' ' . $cond2 ;

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

			$res = $db->query('SELECT
									payment_id,
									payment_number,
									pay_usd_rate,
									pay_currency,
									paid_amount,
									paid_date,
									payment_note,
									payment_transaction,
									invoice_id_fk,
									customer_id_fk,
									paper_number,
									saleman_id_fk,
									saleman_full_name,

									customer_full_name,
									ST.full_name,
									branch_name,

									(SELECT sum(paid_amount) FROM payments PY  WHERE is_deleted =0 AND payment_transaction ="Payment" '.$cond.') AS total_paid,
									
									(SELECT sum(paid_amount) FROM payments PY  WHERE is_deleted =0 AND payment_transaction ="Discount" '.$cond.') AS total_account_discount,
									(SELECT sum(paid_amount) FROM payments PY  WHERE is_deleted =0 AND payment_transaction ="Return" '.$cond.') AS total_returned
								FROM
									payments PY
									
									LEFT JOIN customer C ON (PY.customer_id_fk = C.customer_id )
									LEFT JOIN saleman S ON (PY.saleman_id_fk = S.saleman_id )
									LEFT JOIN staff ST ON (PY.created_by = ST.staff_id )
									LEFT JOIN branch B ON (PY.branch_id_fk = B.branch_id )

									
								WHERE
									( PY.is_deleted = 0 '.$cond.') 
									'); // all type
			if ($res) {				
				
				
				while ($row = $res->fetch_assoc()) {				   
						$result['payment_id']  				= $row['payment_id'];
						$result['payment_number']  			= $row['payment_number'];
						$result['paid_amount']  			= $row['paid_amount'];
						$result['paid_date']  				= $row['paid_date'];

					
	
						$result['payment_note']  			= $row['payment_note'];
						$result['payment_transaction']  	= $row['payment_transaction'];
						$result['paper_number']  			= $row['paper_number'];
						$result['saleman_full_name']  		= $row['saleman_full_name'];
						$result['customer_full_name']  		= $row['customer_full_name'];
						$result['branch_name']  			= $row['branch_name'];
						$result['full_name']  				= $row['full_name'];					
						$result['total_paid']  				= $row['total_paid'];					
						$result['total_account_discount']  	= $row['total_account_discount'];
						$result['total_returned']  			= $row['total_returned'];

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

				$op['is_success'] = true;
				echo json_encode($op);
				exit;
			}else{
				
				$op['is_success'] = false;
				$op['data'] 	  = $db->error;
				echo json_encode($op);

				exit;
			}
			
		}

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

			// $user_id = $_POST['obj_id'];
			$op  = array('is_success'=>false, 'data' => array());

			$res = $db->query("SELECT * FROM staff WHERE is_deleted=0 ");
			if ($res) {
				
				$op['is_success'] = true;
				while ($row = $res->fetch_assoc()) {
				    // do what you need.
						$result['staff_id'] = $row['staff_id'];
						$result['full_name'] = $row['full_name'];
						array_push($op['data'],$result);
				}

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

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

			$id=addslashes($_POST["obj_id"]);
			$reg_date = date("Y-m-d h:i:s", time());
			$lastaction = "Accept Request";

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

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

	  		$res = $db->query("UPDATE capital SET is_accepted = 1 ,
	  													  accepted_by = '".$_SESSION['user_id']."',
	  													  updated_at = '$reg_date',
	  													  last_action='$lastaction',
	  													  last_action_by='".$_SESSION['user_id']."' 
	  									WHERE capital_id=$id");
	    	if ($res){

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

				$dd = date("y", time());
				$capital_number = "TRNS".$dd.($capital_sequence_no);

				// Upadte purchase_sequence_no

				$capital_transaction = "Transfer to Factory";

				$res1 = $db->query("SELECT * FROM capital  WHERE capital_id= '$id'");
				$row1 = $res1->fetch_assoc();
				
				$amount_owner_staff_id_fk	= 	$_SESSION['user_id'];
				
				$capital_amount				=	$row1['capital_amount'];
				$capital_note				=	$row1['capital_note'];
				$capital_currency			=1;


				$date_of_add = date("Y-m-d", time());
			

				$reg_date = date("Y-m-d h:i:s", time());
				$lastaction = "Transfer New Amount to Factory Capital";

				$sql2 ="INSERT INTO capital (capital_number,
											capital_amount,
											date_of_add,
											amount_owner_staff_id_fk,
											capital_note,
											capital_usd_rate,
											capital_currency,
											capital_transaction,
											capital_sequence_no,
											is_approved,
											created_by,
											created_at,
											updated_at,
											last_action,
											last_action_by)
									VALUES( '$capital_number',
											'$capital_amount',
											'$date_of_add',
											'$amount_owner_staff_id_fk',
											'$capital_note',
											'".$_SESSION['USD_TO_IQD']."',
											'$capital_currency',
											'$capital_transaction',
											'$capital_sequence_no',
											'0',
											'".$_SESSION['user_id']."',
											'$reg_date',
											'$reg_date',
											'$lastaction',
											'".$_SESSION['user_id']."')";

				$res2 = $db->query($sql2);

				if ($res2) {
					$db->commit();

					$op['is_success'] = true;
					// $op['data'] 	  = "تمت عملية الاضافة بنجاح";
					$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']=="rejected") {

			$id=addslashes($_POST["obj_id"]);
			$reg_date = date("Y-m-d h:i:s", time());
			$lastaction = "Reject Request";

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

			

	  		$res = $db->query("UPDATE capital SET is_accepted = 2 ,
	  													  accepted_by = '".$_SESSION['user_id']."',
	  													  updated_at = '$reg_date',
	  													  last_action='$lastaction',
	  													  last_action_by='".$_SESSION['user_id']."' 
	  									WHERE capital_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'] == "update") {

			$id 			= $_POST['obj_id'];
			$staff_id_fk	=addslashes($_POST["staff_id_fk"]);
			$date_of_add	=addslashes($_POST["date_of_add"]);
			$safe_amount	=addslashes($_POST["capital_amount"]);
			$safe_note		=addslashes($_POST["capital_note"]);
			$branch_id_fk	=addslashes($_POST["branch_id_fk"]);
			
			$op  = array('is_success'=>false, 'data' => '');

					
			$reg_date = date("Y-m-d H:i:s", time());

			$lastaction = "Update Safe";
			$update_qry = "UPDATE safe SET 	staff_id_fk='$staff_id_fk',
											date_of_add='$date_of_add',
											safe_amount='$safe_amount',	
											safe_note='$safe_note',
											branch_id_fk='$branch_id_fk',
											updated_at='$reg_date',
											last_action='$lastaction',
											last_action_by='".$_SESSION['user_id']."' 
										WHERE safe_id = $id";


			$res = $db->query($update_qry);
			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']=="delete") {

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

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

	  		$res = $db->query("UPDATE safe SET 	is_deleted = 1 ,
	  												deleted_at = '$reg_date',
	  												updated_at='$reg_date',
	  												last_action='$lastaction',
	  												last_action_by='".$_SESSION['user_id']."'
	  										WHERE safe_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'] == "withdraw") {
	      		
	      		$staff_id_fk				=addslashes($_POST["staff_name"]);
				$date_of_add				=addslashes($_POST["factory_date_of_add"]);
				$safe_amount				=addslashes($_POST["transfer_amount"]);
				$safe_note				    =addslashes($_POST["transfer_note"]);
				$branch_id					=addslashes($_POST["branch_id_fk"]);
				$capital_currency			=1;
				

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

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

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

				$reg_date 	= date("Y-m-d H:i:s", time());
				$lastaction = "Withdraw Amount";
				// $safe_note 	= "Withdraw Amount from Safe By : " . $staff_id_fk;

			
				$delete = 0;
				if($date_of_add =='' || $date_of_add == NULL){
					$date_of_add = date("Y-m-d", time());
				}

				
				$sql1 ="INSERT INTO safe 
										   (safe_number,
											safe_amount,
											date_of_add,
											safe_transaction,
											transaction_type,
											capital_usd_rate,
											capital_currency,
											table_name,
											id_fk,
											safe_sequence_no,
											safe_note,
											staff_id_fk,
											branch_id_fk,
											created_by,
											created_at,
										    updated_at,
											last_action,
											last_action_by)
									VALUES( '$safe_number',
											'$safe_amount',
											'$date_of_add',
											'$safe_transaction',
											'$transaction_type',
											'".$_SESSION['USD_TO_IQD']."',
											'$capital_currency',
											'safe',
											'0',
											'$safe_sequence_no',
											'$safe_note',
											'$staff_id_fk',
											'$branch_id',
											'".$_SESSION['user_id']."',
											'$reg_date',
											'$reg_date',
											'$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'] == "profit") {
	      		
	      		$staff_id_fk				=addslashes($_POST["staff_name"]);
				$date_of_add				=addslashes($_POST["factory_date_of_add"]);
				$safe_amount				=addslashes($_POST["transfer_amount"]);
				$safe_note					=addslashes($_POST["transfer_note"]);
				$branch_id					=addslashes($_POST["branch_id_fk"]);
				$capital_currency			=1;
				

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

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

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

				$reg_date 	= date("Y-m-d H:i:s", time());
				$lastaction = "Add Profit Amount";
				// $safe_note 	= "Withdraw Amount from Safe By : " . $staff_id_fk;

			
				$delete = 0;
				if($date_of_add =='' || $date_of_add == NULL){
					$date_of_add = date("Y-m-d", time());
				}

				
				$sql1 ="INSERT INTO safe 
										   (safe_number,
											safe_amount,
											date_of_add,
											safe_transaction,
											transaction_type,
											capital_usd_rate,
											capital_currency,
											table_name,
											id_fk,
											safe_sequence_no,
											safe_note,
											staff_id_fk,
											branch_id_fk,
											created_by,
											created_at,
										    updated_at,
											last_action,
											last_action_by)
									VALUES( '$safe_number',
											'$safe_amount',
											'$date_of_add',
											'$safe_transaction',
											'$transaction_type',
											'".$_SESSION['USD_TO_IQD']."',
											'$capital_currency',
											'safe',
											'0',
											'$safe_sequence_no',
											'$safe_note',
											'$staff_id_fk',
											'$branch_id',
											'".$_SESSION['user_id']."',
											'$reg_date',
											'$reg_date',
											'$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") {
	      		
	      		$amount_owner_staff_id_fk	=addslashes($_POST["amount_owner_staff_id_fk"]);
				$date_of_add				=addslashes($_POST["date_of_add"]);
				$capital_amount				=addslashes($_POST["capital_amount"]);
				$capital_note				=addslashes($_POST["capital_note"]);
				$capital_currency			=1;
				

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

				$dd = date("y", time());
				$capital_number = "CAP".$dd.($capital_sequence_no);

				// Upadte purchase_sequence_no

				$capital_transaction = "Add Capital Amount";
			
				$delete = 0;
				if($date_of_add =='' || $date_of_add == NULL){
					$date_of_add = date("Y-m-d", time());
				}

				$reg_date = date("Y-m-d h:i:s", time());
				$lastaction = "Add New Amount to Capital";

				$sql ="INSERT INTO capital (capital_number,
											capital_amount,
											date_of_add,
											amount_owner_staff_id_fk,
											capital_note,
											capital_usd_rate,
											capital_currency,
											capital_transaction,
											capital_sequence_no,
											created_by,
											created_at,
										    updated_at,
											last_action,
											last_action_by)
									VALUES( '$capital_number',
											'$capital_amount',
											'$date_of_add',
											'$amount_owner_staff_id_fk',
											'$capital_note',
											'".$_SESSION['USD_TO_IQD']."',
											'$capital_currency',
											'$capital_transaction',
											'$capital_sequence_no',
											'".$_SESSION['user_id']."',
											'$reg_date',
											'$reg_date',
											'$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;
					}
			

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