<?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 P.category_id_fk='.$_GET['id']:'';
			$table  =  '(
				SELECT
					product_id,
					category,
					category_type,
					product,
					part_number,
					made_in,
					(SELECT sum(buy_price * purchase_quantity) from purchase where purchase.product_id_fk = P.product_id AND purchase.is_deleted=0) as total_cost,
					sell_price,
					sell_price_percentage,
					average_price,
					category_id_fk,
					P.created_at AS create_date,
					(SELECT sum(purchase_quantity) from purchase where purchase.product_id_fk = P.product_id AND purchase.is_deleted=0) as total_quantity,
					(SELECT sum(sell_quantity) from sell_item where sell_item.product_id_fk = P.product_id AND sell_item.is_deleted=0) as sold_quantity,
					(COALESCE((SELECT sum(COALESCE(purchase_quantity, 0)) from purchase where purchase.product_id_fk = P.product_id AND purchase.is_deleted=0),0)  -
            COALESCE((SELECT sum(COALESCE(sell_quantity, 0)) from sell_item where sell_item.product_id_fk = P.product_id AND sell_item.is_deleted=0),0) )  AS Remianed_quantity
				FROM
					product P,  category C
				WHERE
					(P.category_id_fk=C.category_id AND P.is_deleted = 0 '.$cond.')
			) AS ALLTBL';
			// Table's primary key
			//echo $table;
			$primaryKey = 'product_id';
			$columns = array(
			    array( 'db' => 'product_id', 'dt' => 0 ),

			    array( 'db' => 'product', 'dt' => 1 ),
			    array( 'db' => 'part_number', 'dt' => 2 ),
			    array( 'db' => 'made_in', 'dt' => 3 ),
			    array( 'db' => 'total_cost', 'dt' => 4 ),
			    array( 'db' => 'sell_price', 'dt' => 5 ),
			    array( 'db' => 'sell_price_percentage', 'dt' => 6 ),
					array( 'db' => 'average_price', 'dt' => 7 ),
			    array( 'db' => 'create_date', 'dt' => 8, 'formatter'=> function($d){

							return date('d-M-Y',strtotime($d));
					} ),
			    array( 'db' => 'total_quantity', 'dt' => 9 ),
					array( 'db' => 'Remianed_quantity', 'dt' => 10 ),
					array( 'db' => 'category', 'dt' => 11 ),
			    array( 'db' => 'category_type', 'dt' => 12 ),
			    array( 'db' => 'sold_quantity', 'dt' => 13 ),
					array( 'db' => 'category_id_fk', 'dt' => 14 )

			);
			// 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_sell_date") {

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

			$res = $db->query("SELECT *
				FROM sell_item WHERE is_deleted = 0 ORDER BY created_at ASC LIMIT 1");
			if ($res) {				
				$op  = array();
				while ($row = $res->fetch_assoc()) {				   
						$result[0]  = $row['created_at'];						
						//array_push($op,$result);
						$dStart = new DateTime($row['created_at']);
				}

				$res = $db->query("SELECT *
				FROM sell_item WHERE is_deleted = 0 ORDER BY created_at DESC LIMIT 1");
				while ($row = $res->fetch_assoc()) {				   
						//$result[1]  = $row['created_at'];	
						$dEnd 			= new DateTime($row['created_at']);			
						//array_push($op,$result);
				}

				$dDiff = $dStart->diff($dEnd);
   			//echo $dDiff->format('%R'); // use for point out relation: smaller/greater
				$months = $dDiff->m;
				$day = $dDiff->d;
   			if($months == 0){
   					$op[0] = date('Y-m-d',strtotime($result[0]));
   			}else{
   				for($i=0;$i<$months;$i++){
   					$op[$i] = date('Y-m-d', strtotime("+".$i." months", strtotime($result[0])));
   				}
   			}
   			

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

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

			$cond_y = (isset($_POST['yy']) && $_POST['yy'] !='not')? ' AND YEAR(P.purchase_date)  	='.$_POST['yy']:'';
			$cond_m = (isset($_POST['mm']) && $_POST['mm'] !='not')? ' AND MONTH(P.purchase_date) 	='.$_POST['mm']:'';
			$cond_d = (isset($_POST['dd']) && $_POST['dd'] !='not')? ' AND DAY(P.purchase_date) 	='.$_POST['dd']:'';

			$cond   = $cond_y . ' ' . $cond_m . ' ' . $cond_d;


			$res = $db->query("SELECT					
									

									sum(buy_price_per_meter * item_length) AS total_price
					
					 			FROM purchase_item_detail PID
					 	 		
					 	 		LEFT JOIN purchase_item PP ON (PID.purchase_item_id_fk = PP.purchase_item_id)
					 	 		LEFT JOIN purchase P ON (PP.purchase_id_fk = P.purchase_id)

					 	 
								WHERE 
								(P.is_deleted = 0 AND PP.is_deleted = 0 AND PID.is_deleted = 0 AND  PID.is_part=0 $cond ) "); // all type
			
			if ($res) {				
				$op  = array();
				$row = $res->fetch_assoc();

				$result['total_price']  			= $row['total_price'];
					
				

				array_push($op,$result);

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


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

			$cond_y = (isset($_POST['yy']) && $_POST['yy'] !='not')? ' AND YEAR(E.expenses_date)  	='.$_POST['yy']:'';
			$cond_m = (isset($_POST['mm']) && $_POST['mm'] !='not')? ' AND MONTH(E.expenses_date) 	='.$_POST['mm']:'';
			$cond_d = (isset($_POST['dd']) && $_POST['dd'] !='not')? ' AND DAY(E.expenses_date) 	='.$_POST['dd']:'';

			$cond   = $cond_y . ' ' . $cond_m . ' ' . $cond_d;


			$res = $db->query("SELECT 

								sum(expenses_quantity * expenses_unit_price / expenses_usd_rate)  AS total_price
								FROM expenses E 

								WHERE E.is_deleted =0 AND E.expenses_currency=0  $cond "); // all type

			$res2 = $db->query("SELECT 

								sum(expenses_quantity * expenses_unit_price )  AS total_price
								FROM expenses E 

								WHERE E.is_deleted =0 AND E.expenses_currency=1  $cond "); // all type
			
			if ($res && $res2) {				
				$op  	= array();
				$row 	= $res->fetch_assoc();
				$row2 	= $res2->fetch_assoc();

				$result['total_price']  	= $row['total_price'] + $row2['total_price'];
					
				

				array_push($op,$result);

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


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

			$cond_y = (isset($_POST['yy']) && $_POST['yy'] !='not')? ' AND YEAR(C.date_of_add)  	='.$_POST['yy']:'';
			$cond_m = (isset($_POST['mm']) && $_POST['mm'] !='not')? ' AND MONTH(C.date_of_add) 	='.$_POST['mm']:'';
			$cond_d = (isset($_POST['dd']) && $_POST['dd'] !='not')? ' AND DAY(C.date_of_add) 	='.$_POST['dd']:'';

			$cond   = $cond_y . ' ' . $cond_m . ' ' . $cond_d;


			$res = $db->query("SELECT sum(capital_amount) AS total_price 
							   FROM capital C  
							   WHERE C.is_deleted =0   $cond "); // all type
			
			if ($res) {				
				$op  = array();
				$row = $res->fetch_assoc();

				$result['total_price']  			= $row['total_price'];
					
				

				array_push($op,$result);

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


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

			$cond_y = (isset($_POST['yy']) && $_POST['yy'] !='not')? ' AND YEAR(C.withdraw_date)  	='.$_POST['yy']:'';
			$cond_m = (isset($_POST['mm']) && $_POST['mm'] !='not')? ' AND MONTH(C.withdraw_date) 	='.$_POST['mm']:'';
			$cond_d = (isset($_POST['dd']) && $_POST['dd'] !='not')? ' AND DAY(C.withdraw_date) 	='.$_POST['dd']:'';

			$cond   = $cond_y . ' ' . $cond_m . ' ' . $cond_d;


			$res = $db->query("SELECT sum(withdraw_amount) AS total_price 
							   FROM capital_withdraw C  
							   WHERE C.is_deleted =0    $cond  "); // all type
			
			if ($res) {				
				$op  = array();
				$row = $res->fetch_assoc();

				$result['total_price']  			= $row['total_price'];
					
				

				array_push($op,$result);

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


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

			$cond_y = (isset($_POST['yy']) && $_POST['yy'] !='not')? ' AND YEAR(Inv.invoice_date)  	='.$_POST['yy']:'';
			$cond_m = (isset($_POST['mm']) && $_POST['mm'] !='not')? ' AND MONTH(Inv.invoice_date) 	='.$_POST['mm']:'';
			$cond_d = (isset($_POST['dd']) && $_POST['dd'] !='not')? ' AND DAY(Inv.invoice_date) 	='.$_POST['dd']:'';

			$cond   = $cond_y . ' ' . $cond_m . ' ' . $cond_d;


			$res = $db->query("SELECT
					
									SUM(invoice_total_price_actual
									
									) AS total_price
											
						
								 FROM
								 invoice Inv
								 
								 
							WHERE
								(Inv.is_deleted = 0  AND Inv.invoice_status <3   $cond )"); // all type
			
			if ($res) {				
				$op  = array();
				$row = $res->fetch_assoc();

				$result['total_price']  			= $row['total_price'];
					
				

				array_push($op,$result);

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

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

			$cond_y = (isset($_POST['yy']) && $_POST['yy'] !='not')? ' AND YEAR(Inv.invoice_date)  	='.$_POST['yy']:'';
			$cond_m = (isset($_POST['mm']) && $_POST['mm'] !='not')? ' AND MONTH(Inv.invoice_date) 	='.$_POST['mm']:'';
			$cond_d = (isset($_POST['dd']) && $_POST['dd'] !='not')? ' AND DAY(Inv.invoice_date) 	='.$_POST['dd']:'';

			$cond   = $cond_y . ' ' . $cond_m . ' ' . $cond_d;


			$res = $db->query("SELECT
					
									SUM(invoice_total_price_actual			
									
									) AS total_price
											
						
								 FROM
								 invoice Inv
								 
								 
							WHERE
								(Inv.is_deleted = 0 AND Inv.invoice_status <3  AND Inv.invoice_type = 0  $cond )"); // all type
			
			if ($res) {				
				$op  = array();
				$row = $res->fetch_assoc();

				$result['total_price']  			= $row['total_price'];
					
				

				array_push($op,$result);

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

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

			$cond_y = (isset($_POST['yy']) && $_POST['yy'] !='not')? ' AND YEAR(Inv.invoice_date)  	='.$_POST['yy']:'';
			$cond_m = (isset($_POST['mm']) && $_POST['mm'] !='not')? ' AND MONTH(Inv.invoice_date) 	='.$_POST['mm']:'';
			$cond_d = (isset($_POST['dd']) && $_POST['dd'] !='not')? ' AND DAY(Inv.invoice_date) 	='.$_POST['dd']:'';

			$cond   = $cond_y . ' ' . $cond_m . ' ' . $cond_d;


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

								   
									
									) AS total_price
											
						
								 FROM
								 invoice Inv
								 
								 
							WHERE
								(Inv.is_deleted = 0 AND Inv.invoice_status <3  AND Inv.invoice_type = 1 AND Inv.is_single=0  $cond )"); // all type
			
			if ($res) {				
				$op  = array();
				$row = $res->fetch_assoc();

				$result['total_price']  			= $row['total_price'];
					
				

				array_push($op,$result);

				echo json_encode($op);
				exit;
			}else{
				
				echo 0;
				exit;
			}
		}
		if ($_POST['type']=="get_total_sells_single") {

			$cond_y = (isset($_POST['yy']) && $_POST['yy'] !='not')? ' AND YEAR(Inv.invoice_date)  	='.$_POST['yy']:'';
			$cond_m = (isset($_POST['mm']) && $_POST['mm'] !='not')? ' AND MONTH(Inv.invoice_date) 	='.$_POST['mm']:'';
			$cond_d = (isset($_POST['dd']) && $_POST['dd'] !='not')? ' AND DAY(Inv.invoice_date) 	='.$_POST['dd']:'';

			$cond   = $cond_y . ' ' . $cond_m . ' ' . $cond_d;


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

									
									) AS total_price
											
						
								 FROM
								 invoice Inv
								 
								 
							WHERE
								(Inv.is_deleted = 0 AND Inv.invoice_status <3  AND Inv.invoice_type = 1 AND Inv.is_single=1  $cond )"); // all type
			
			if ($res) {				
				$op  = array();
				$row = $res->fetch_assoc();

				$result['total_price']  			= $row['total_price'];
					
				

				array_push($op,$result);

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


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

			$cond_y = (isset($_POST['yy']) && $_POST['yy'] !='not')? ' AND YEAR(PY.paid_date)  	='.$_POST['yy']:'';
			$cond_m = (isset($_POST['mm']) && $_POST['mm'] !='not')? ' AND MONTH(PY.paid_date) 	='.$_POST['mm']:'';
			$cond_d = (isset($_POST['dd']) && $_POST['dd'] !='not')? ' AND DAY(PY.paid_date) 	='.$_POST['dd']:'';

			$cond   = $cond_y . ' ' . $cond_m . ' ' . $cond_d;


			$res = $db->query("
								SELECT sum(paid_amount) AS total_price  
								FROM payments PY  
								WHERE PY.is_deleted =0 AND PY.payment_transaction ='Payment' $cond

							  "); // all type

			$res2 = $db->query("
								SELECT sum(paid_amount) AS total_price  
								FROM payments PY  
								WHERE PY.is_deleted =0 AND PY.payment_transaction ='Return' $cond

							  "); // all type
			
			if ($res && $res2) {				
				$op  = array();
				$row = $res->fetch_assoc();
				$row2 = $res2->fetch_assoc();

				$result['total_price']  			= $row['total_price'] - $row2['total_price'];
					
				

				array_push($op,$result);

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

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

			$cond_y = (isset($_POST['yy']) && $_POST['yy'] !='not')? ' AND YEAR(PY.paid_date)  	='.$_POST['yy']:'';
			$cond_m = (isset($_POST['mm']) && $_POST['mm'] !='not')? ' AND MONTH(PY.paid_date) 	='.$_POST['mm']:'';
			$cond_d = (isset($_POST['dd']) && $_POST['dd'] !='not')? ' AND DAY(PY.paid_date) 	='.$_POST['dd']:'';

			$cond   = $cond_y . ' ' . $cond_m . ' ' . $cond_d;


			$res = $db->query("
								SELECT sum(paid_amount) AS total_price  
								FROM payments PY  
								WHERE PY.is_deleted =0 AND PY.payment_transaction ='Discount' $cond

							  "); // all type

			
			
			if ($res ) {				
				$op  = array();
				$row = $res->fetch_assoc();
				

				$result['total_price']  	= $row['total_price'];
					
				

				array_push($op,$result);

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

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

			$cond_y = (isset($_POST['yy']) && $_POST['yy'] !='not')? ' AND YEAR(PY.paid_date)  	='.$_POST['yy']:'';
			$cond_m = (isset($_POST['mm']) && $_POST['mm'] !='not')? ' AND MONTH(PY.paid_date) 	='.$_POST['mm']:'';
			$cond_d = (isset($_POST['dd']) && $_POST['dd'] !='not')? ' AND DAY(PY.paid_date) 	='.$_POST['dd']:'';

			$cond   = $cond_y . ' ' . $cond_m . ' ' . $cond_d;


			$res = $db->query("
								SELECT sum(paid_amount) AS total_price  
								FROM payments PY  
								WHERE PY.is_deleted =0 AND PY.payment_transaction ='Return' $cond

							  "); // all type

			
			
			if ($res ) {				
				$op  = array();
				$row = $res->fetch_assoc();
				

				$result['total_price']  	= $row['total_price'];
					
				

				array_push($op,$result);

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

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

			$cond_y = (isset($_POST['yy']) && $_POST['yy'] !='not')? ' AND YEAR(Inv.invoice_date)  	='.$_POST['yy']:'';
			$cond_m = (isset($_POST['mm']) && $_POST['mm'] !='not')? ' AND MONTH(Inv.invoice_date) 	='.$_POST['mm']:'';
			$cond_d = (isset($_POST['dd']) && $_POST['dd'] !='not')? ' AND DAY(Inv.invoice_date) 	='.$_POST['dd']:'';

			$cond   = $cond_y . ' ' . $cond_m . ' ' . $cond_d;



			$cond_y1 = (isset($_POST['yy']) && $_POST['yy'] !='not')? ' AND YEAR(PY.paid_date)  	='.$_POST['yy']:'';
			$cond_m1 = (isset($_POST['mm']) && $_POST['mm'] !='not')? ' AND MONTH(PY.paid_date) 	='.$_POST['mm']:'';
			$cond_d1 = (isset($_POST['dd']) && $_POST['dd'] !='not')? ' AND DAY(PY.paid_date) 	='.$_POST['dd']:'';

			$cond1   = $cond_y1 . ' ' . $cond_m1 . ' ' . $cond_d1;


			$cond_y2 = (isset($_POST['yy']) && $_POST['yy'] !='not')? ' AND YEAR(C.withdraw_date)  	='.$_POST['yy']:'';
			$cond_m2 = (isset($_POST['mm']) && $_POST['mm'] !='not')? ' AND MONTH(C.withdraw_date) 	='.$_POST['mm']:'';
			$cond_d2 = (isset($_POST['dd']) && $_POST['dd'] !='not')? ' AND DAY(C.withdraw_date) 	='.$_POST['dd']:'';

			$cond2   = $cond_y2 . ' ' . $cond_m2 . ' ' . $cond_d2;


			$res2 = $db->query("SELECT sum(withdraw_amount) AS total_price 
							   FROM capital_withdraw C  
							   WHERE C.is_deleted =0    $cond2  "); // all type




			$res = $db->query("SELECT
					

									(
                    
					                    COALESCE((SELECT sum(paid_amount) AS total_price  
													FROM payments PY  
													WHERE PY.is_deleted =0 AND PY.payment_transaction ='Payment' $cond1),0)
					                        +
					                    COALESCE((SELECT sum(paid_amount) AS total_price  
													FROM payments PY  
													WHERE PY.is_deleted =0 AND PY.payment_transaction ='Discount' $cond1),0)    
					                        -
					                      COALESCE((SELECT sum(paid_amount) AS total_price  
													FROM payments PY  
													WHERE PY.is_deleted =0 AND PY.payment_transaction ='Return' $cond1),0)  
					                    
					                    ) AS total_paid,

					                SUM(invoice_total_price_actual
									
									) AS total_price

									
											
						
								 FROM
								 invoice Inv
								 
								 
							WHERE
								(Inv.is_deleted = 0 AND Inv.invoice_status <3  $cond )"); // all type
			
			if ($res && $res2) {				
				$op  	= array();
				$row 	= $res->fetch_assoc();
				$row2 	= $res2->fetch_assoc();

				$result['total_price']  	= $row['total_price'] - $row['total_paid'] + $row2['total_price'];
					
				

				array_push($op,$result);

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





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

			

			$res = $db->query("SELECT
					
									COUNT(*) AS total_price
								FROM product P  	
					        	WHERE   P.is_deleted =0  "); // all type
			// echo $cond;
			if ($res) {				
				$op  = array();
				$row = $res->fetch_assoc();

				$result['total_price']  	= $row['total_price'];
					
				

				array_push($op,$result);

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



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

			$cond_y = (isset($_POST['yy']) && $_POST['yy'] !='not')? ' AND YEAR(PY.payment_date)  	='.$_POST['yy']:'';
			$cond_m = (isset($_POST['mm']) && $_POST['mm'] !='not')? ' AND MONTH(PY.payment_date) 	='.$_POST['mm']:'';
			$cond_d = (isset($_POST['dd']) && $_POST['dd'] !='not')? ' AND DAY(PY.payment_date) 	='.$_POST['dd']:'';

			$cond   = $cond_y . ' ' . $cond_m . ' ' . $cond_d;


			$res = $db->query("
								SELECT sum(payment_amount) AS total_price  
								FROM supplier_payments PY  
								WHERE PY.is_deleted =0  $cond  "); // all type

			
			
			if ($res) {				
				$op  = array();
				$row = $res->fetch_assoc();
				
				$result['total_price']  	= $row['total_price'];
					
				

				array_push($op,$result);

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



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

			if(isset($_POST['yy']) && $_POST['yy'] !='not'){

				$yr_id = $_POST['yy'] - 2014;
				$cond_y = ' AND WH1.year_id_fk  	='.$yr_id;
			}else{
				$cond_y = '';
			}

			
			$cond_m = (isset($_POST['mm']) && $_POST['mm'] !='not')? ' AND WH1.month_id_fk 	='.$_POST['mm']:'';
			$cond_d = (isset($_POST['dd']) && $_POST['dd'] !='not')? ' AND WH1.day_id_fk 	='.$_POST['dd']:'';

			$cond   = $cond_y . ' ' . $cond_m . ' ' . $cond_d;


			$res = $db->query("SELECT
					
									sum(hours) AS total_price
								FROM work_hours WH1  	
					        	WHERE   WH1.is_deleted =0  AND WH1.hour_transaction = 'Reward' 

								 $cond "); // all type
			// echo $cond;
			if ($res) {				
				$op  = array();
				$row = $res->fetch_assoc();

				$result['total_price']  			= $row['total_price'];
					
				

				array_push($op,$result);

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


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

			if(isset($_POST['yy']) && $_POST['yy'] !='not'){

				$yr_id = $_POST['yy'] - 2014;
				$cond_y = ' AND WH1.year_id_fk  	='.$yr_id;
			}else{
				$cond_y = '';
			}

			
			$cond_m = (isset($_POST['mm']) && $_POST['mm'] !='not')? ' AND WH1.month_id_fk 	='.$_POST['mm']:'';
			$cond_d = (isset($_POST['dd']) && $_POST['dd'] !='not')? ' AND WH1.day_id_fk 	='.$_POST['dd']:'';

			$cond   = $cond_y . ' ' . $cond_m . ' ' . $cond_d;


			$res = $db->query("SELECT
					
									sum(hours) AS total_price
								FROM work_hours WH1  	
					        	WHERE   WH1.is_deleted =0  AND WH1.hour_transaction = 'Penalty' 

								 $cond "); // all type
			// echo $cond;
			if ($res) {				
				$op  = array();
				$row = $res->fetch_assoc();

				$result['total_price']  			= $row['total_price'];
					
				

				array_push($op,$result);

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



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

			if(isset($_POST['yy']) && $_POST['yy'] !='not'){

				$yr_id = $_POST['yy'] - 2014;
				$cond_y = ' AND WH1.year_id_fk  	='.$yr_id;
			}else{
				$cond_y = '';
			}

			
			$cond_m = (isset($_POST['mm']) && $_POST['mm'] !='not')? ' AND WH1.month_id_fk 	='.$_POST['mm']:'';
			$cond_d = (isset($_POST['dd']) && $_POST['dd'] !='not')? ' AND WH1.day_id_fk 	='.$_POST['dd']:'';

			$cond   = $cond_y . ' ' . $cond_m . ' ' . $cond_d;


			$res = $db->query("	SELECT
					
					
					
					
					


					sum(

					(


					    (	COALESCE((SELECT sum(hours ) FROM work_hours WH1  	
											        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = 'Attend' $cond),0)
						*
							(	(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
										        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = 'Salary' $cond_y $cond_m),0)
								) / 240
							)
						)



					)
					+
					(
						(	COALESCE((SELECT sum(hours * 1.3333 ) FROM work_hours WH1  	
											        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = 'Extra Hours' $cond),0)
						*
							(	(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
										        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = 'Salary' $cond_y $cond_m),0)
								) / 240
							)
						)
					) 
					+
					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0 AND WH1.hour_transaction = 'Reward' $cond),0)
					) 
					-
					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = 'Penalty' $cond),0)
					) 

					-
					(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
					        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = 'Refund' $cond_y $cond_m),0)
					) 
					-
					(		(COALESCE((SELECT sum(hours ) FROM work_hours WH1  	
										        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.is_subtracted = 1 $cond),0)
							)

							*
							(
								(	(COALESCE((SELECT sum(hours) FROM work_hours WH1  	
										        	WHERE WH1.employe_id_fk = E.employe_id AND  WH1.is_deleted =0  AND WH1.hour_transaction = 'Salary' $cond_y $cond_m),0)
									) / 240
								)

							)
					)


					) AS total_price
					
					
				FROM
					employe  E
					
					
				WHERE
					( E.is_deleted = 0 )
								 "); // all type
			// echo $cond;
			if ($res) {				
				$op  = array();
				$row = $res->fetch_assoc();

				$result['total_price']  			= $row['total_price'];
					
				

				array_push($op,$result);

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



		if ($_POST['type']=="get_daily_sells") {
			$reg_date = date("Y-m-d", time());

			$res = $db->query("SELECT S.*, P.product_name  from sell_item S LEFT JOIN product P ON (S.product_id_fk = P.product_id) where S.is_deleted = 0 AND S.sell_date >= '$reg_date' "); // all type
			if ($res) {				
				$op  = array();


				while ($row = $res->fetch_assoc()) {				   
						$result[0]  = $row['item_sell_price_per_unit'];
						$result[1]  = $row['sell_item_quantity'];
						$result[2]  = $row['sell_item_barcode'];

						$dt = new DateTime($row['sell_date']);
						$time = $dt->format('h:i A');

						$result[3]  = $time;				
						$result[4]  = $row['product_name'];				
						array_push($op,$result);
				}

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


		

		if ($_POST['type']=="get_daily_invoice") {
			
			$reg_date = date("Y-m-d", time());
			// $reg_date = date( '2018-05-01');

			$res = $db->query("SELECT

									invoice_number,
									invoice_date,
									
									invoice_total_net_balance,
									customer_id_fk,
									customer_full_name AS name,
									customer_email AS email,
									customer_phone AS phone,
									
									Inv.created_at,  
									
									(

				          			COALESCE((SELECT sum(sell_item_detail_height * item_sell_price_per_meter) FROM sell_item_detail SID, sell_item S 
				          			
								        WHERE Inv.invoice_id = SID.invoice_id_fk AND S.invoice_id_fk = Inv.invoice_id AND S.is_deleted=0 AND  SID.part_status =0 AND SID.is_deleted=0 AND S.sell_item_id = SID.sell_item_id_fk AND S.other_id_fk =0 AND SID.sell_item_detail_width = 0),0)
								    +
								   	COALESCE((SELECT sum(sell_item_detail_height * sell_item_detail_width * item_sell_price_per_meter) FROM sell_item_detail SID, sell_item S 
				          			
								        WHERE Inv.invoice_id = SID.invoice_id_fk AND S.invoice_id_fk = Inv.invoice_id AND S.is_deleted=0 AND  SID.part_status =0 AND SID.is_deleted=0 AND S.sell_item_id = SID.sell_item_id_fk AND S.other_id_fk =0 AND SID.sell_item_detail_width > 0),0)
								    +
								   	COALESCE((SELECT sum(sell_item_detail_width * decorate_price_per_meter) FROM sell_item_detail SID, sell_item S 
				          			
								        WHERE Inv.invoice_id = SID.invoice_id_fk AND S.invoice_id_fk = Inv.invoice_id AND S.is_deleted=0 AND  SID.part_status =0 AND SID.is_deleted=0 AND S.sell_item_id = SID.sell_item_id_fk AND S.other_id_fk =0 AND SID.sell_item_detail_width > 0),0)

								    +
								    COALESCE((SELECT sum(sell_item_quantity * item_sell_price_per_meter) FROM sell_item S  
														WHERE Inv.invoice_id = S.invoice_id_fk AND  S.other_id_fk >0 AND S.is_deleted=0),0)
										-
								 	(SELECT sum(actual_discount) from sell_item where sell_item.invoice_id_fk = Inv.invoice_id AND sell_item.is_deleted=0 )
									
									
									) AS final_price,

									(SELECT sum(actual_discount) from sell_item where sell_item.invoice_id_fk = Inv.invoice_id AND sell_item.is_deleted=0 ) as actual_discount
											
						
								FROM
								 invoice Inv
								 LEFT JOIN customer Cus ON (Inv.customer_id_fk=Cus.customer_id) 
								 
							WHERE
								(Inv.is_deleted = 0  AND Inv.invoice_date >= '$reg_date') ORDER BY invoice_id DESC

					 "); // all type
			if ($res) {				
				$op  = array();

				
				while ($row = $res->fetch_assoc()) {				   
						$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('h:i A');

						$result['time']  				= $time;				
						$result['name']  				= $row['name'];
						$result['email']  				= $row['email'];
						$result['phone']  				= $row['phone'];
						$result['customer_id_fk']  		= $row['customer_id_fk'];
						$result['invoice_transaction']  = "Invoice";					
						array_push($op,$result);
				}

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


		if ($_POST['type']=="get_daily_expenses") {
			$reg_date = date("Y-m-d", time());
			// $reg_date = date( '2018-05-01');

			

			$res = $db->query("SELECT 
										expenses_name, 
										expenses_date,
										expenses_quantity,
										expenses_unit_price,
										expenses_currency,
										created_at,
										sum(expenses_quantity * expenses_unit_price ) as total_expenses
						    	from expenses 
						    	WHERE expenses_date >= '$reg_date' AND is_deleted=0 AND expenses_currency=1 "); // all type

			$res2 = $db->query("SELECT 
										expenses_name, 
										expenses_date,
										expenses_quantity,
										expenses_unit_price,
										expenses_currency,
										created_at,
										sum(expenses_quantity * expenses_unit_price / expenses_usd_rate) as total_expenses
						    	from expenses 
						    	WHERE expenses_date >= '$reg_date' AND is_deleted=0 AND expenses_currency=0 "); // all type

			if ($res && $res2) {				
				$op  = array();
				print_r("SELECT 
										expenses_name, 
										expenses_date,
										expenses_quantity,
										expenses_unit_price,
										expenses_currency,
										created_at,
										sum(expenses_quantity * expenses_unit_price / expenses_usd_rate) as total_expenses
						    	from expenses 
						    	WHERE expenses_date >= '$reg_date' AND is_deleted=0 AND expenses_currency=0 ");
				while ($row = $res->fetch_assoc()) {				   
					$result['expenses_name']  		= $row['expenses_name'];
					$result['expenses_quantity']  	= $row['expenses_quantity'];
					$result['expenses_unit_price']  = $row['expenses_unit_price'];

					$dt 							= new DateTime($row['created_at']);
					$time 							= $dt->format('h:i A');

					$result['time']  				= $time;
					$result['total_expenses']  		= $row['total_expenses'];
					$result['expenses_currency']  	= $row['expenses_currency'];

					array_push($op,$result);
				}

				while ($row2 = $res2->fetch_assoc()) {				   
					$result['expenses_name']  		= $row2['expenses_name'];
					$result['expenses_quantity']  	= $row2['expenses_quantity'];
					$result['expenses_unit_price']  = $row2['expenses_unit_price'];

					$dt 							= new DateTime($row2['created_at']);
					$time 							= $dt->format('h:i A');

					$result['time']  				= $time;
					$result['total_expenses']  		= $row2['total_expenses'];
					$result['expenses_currency']  	= $row2['expenses_currency'];

					array_push($op,$result);
				}

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


		if ($_POST['type']=="get_purchase") {
			$reg_date = date("Y-m-d", time());
			

			$cond_y = (isset($_POST['yy']) && $_POST['yy'] !='not')? ' AND YEAR(P.purchase_date)  	='.$_POST['yy']:'';
			$cond_m = (isset($_POST['mm']) && $_POST['mm'] !='not')? ' AND MONTH(P.purchase_date) 	='.$_POST['mm']:'';
			$cond_d = (isset($_POST['dd']) && $_POST['dd'] !='not')? ' AND DAY(P.purchase_date) 	='.$_POST['dd']:'';

			$cond   = $cond_y . ' ' . $cond_m . ' ' . $cond_d;

			if(strlen($cond) < 15){

				$limits = 'LIMIT 15';

			}else{
				$limits = '';

			}

			
			$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,
					buy_price_per_unit,
					stock_name,
					P.created_at,
					sum(buy_price_per_unit * purchase_quantity * item_width) AS total_price
					
					 FROM
					 	 purchase P
					 	 LEFT JOIN stock S ON (S.stock_id = P.stock_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 ORDER BY purchase_id DESC  $limits "); // all type
			if ($res) {				
				$op  = array();
	
				while ($row = $res->fetch_assoc()) {				   
					$result['purchase_auto_number'] 	= $row['purchase_auto_number'];
					$result['supplier_full_name']  		= $row['supplier_full_name'];
					$result['purchase_transaction']  	= $row['purchase_transaction'];

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

					$result['time']  					= $time;
					$result['total_price']  			= $row['total_price'];
													
					array_push($op,$result);
				}

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



		if ($_POST['type']=="get_order") {
			$reg_date = date("Y-m-d", time());
			

			$cond_y = (isset($_POST['yy']) && $_POST['yy'] !='not')? ' AND YEAR(Inv.invoice_date)  	='.$_POST['yy']:'';
			$cond_m = (isset($_POST['mm']) && $_POST['mm'] !='not')? ' AND MONTH(Inv.invoice_date) 	='.$_POST['mm']:'';
			$cond_d = (isset($_POST['dd']) && $_POST['dd'] !='not')? ' AND DAY(Inv.invoice_date) 	='.$_POST['dd']:'';

			$cond   = $cond_y . ' ' . $cond_m . ' ' . $cond_d;

			if(strlen($cond) < 15){

				$limits = 'LIMIT 15';

			}else{
				$limits = '';

			}

			
			$res = $db->query("SELECT

									Inv.invoice_number,
									Inv.invoice_date,
									Inv.delivery_time,
									Inv.delivery_status,
									Inv.author,
									Inv.customer_id_fk,
									customer_full_name AS name,
									customer_email AS email,
									customer_phone AS phone,
									Inv.invoice_transaction,
									Inv.original_invoice_id_fk,
									Inv1.invoice_number AS original_no
									
											
						
								FROM
								 invoice Inv
								 LEFT JOIN customer Cus ON (Inv.customer_id_fk=Cus.customer_id) 
								 LEFT JOIN invoice Inv1 ON (Inv.original_invoice_id_fk=Inv1.invoice_id) 
								 
							WHERE
								(Inv.is_deleted = 0  $cond ) ORDER BY Inv.invoice_id DESC  $limits

					 "); // all type

			
			if ($res) {				
				$op  = array();
	
				while ($row = $res->fetch_assoc()) {				   
					$result['invoice_number'] 			= $row['invoice_number'];
					$result['delivery_time']  			= $row['delivery_time'];
					$result['delivery_status']  		= $row['delivery_status'];
					$result['author']  					= $row['author'];
					$result['name']  					= $row['name'];
					$result['phone']  					= $row['phone'];

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

					$result['time']  					= $time;
					$result['invoice_transaction']  	= $row['invoice_transaction'];
					$result['original_invoice_id_fk']  	= $row['original_no'];
													
					array_push($op,$result);
				}

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



		if ($_POST['type']=="get_pie_order") {
			
			$reg_date = date("Y", time());
			

			$cond_y = (isset($_POST['yy']) && $_POST['yy'] !='not')? ' AND YEAR(Inv.invoice_date)  	='.$_POST['yy']:'';
			

			$cond   = $cond_y ;

			if(strlen($cond) < 15){

				$cond = ' AND YEAR(Inv.invoice_date)  ='.$reg_date;
			
				$yyr 	= 	$reg_date;

			}else{
				$yyr 	= 	$_POST['yy'];

			}

			
			$res = $db->query("SELECT

									count(*) AS no_of_orders,
									MONTH(Inv.invoice_date) AS montn_no,
									MONTHNAME(Inv.invoice_date) AS month_name
											
						
								FROM
								 invoice Inv
								
								 
							WHERE
								(Inv.is_deleted = 0  $cond )  GROUP BY MONTH(Inv.invoice_date)

					 "); // all type

			
			if ($res) {				
				$op  = array();
	
				while ($row = $res->fetch_assoc()) {				   
					$result['no_of_orders'] 			= $row['no_of_orders'];
					$result['montn_no']  				= $row['montn_no'];
					$result['month_name']  				= $row['month_name'];
					$result['year']  					= $yyr;								
					array_push($op,$result);
				}

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



		if ($_POST['type']=="get_pie_sells") {
			
			$reg_date = date("Y", time());
			

			$cond_y = (isset($_POST['yy']) && $_POST['yy'] !='not')? ' AND YEAR(Inv.invoice_date)  	='.$_POST['yy']:'';
			

			$cond   = $cond_y ;

			if(strlen($cond) < 15){

				$cond = ' AND YEAR(Inv.invoice_date)  ='.$reg_date;
			
				$yyr 	= 	$reg_date;

			}else{
				$yyr 	= 	$_POST['yy'];

			}

			
			$res = $db->query("SELECT

									count(*) AS no_of_orders,
									MONTH(Inv.invoice_date) AS montn_no,
									MONTHNAME(Inv.invoice_date) AS month_name,

									SUM(invoice_total_price_actual) AS final_price
											
						
								FROM
								 invoice Inv
								
								 
							WHERE
								(Inv.is_deleted = 0  $cond )  GROUP BY MONTH(Inv.invoice_date)

					 "); // all type

			// echo $cond;
			if ($res) {				
				$op  = array();
	
				while ($row = $res->fetch_assoc()) {				   
					$result['no_of_orders'] 			= $row['no_of_orders'];
					$result['montn_no']  				= $row['montn_no'];
					$result['month_name']  				= $row['month_name'];
					$result['final_price']  			= round($row['final_price'],2);
					$result['year']  					= $yyr;								
					array_push($op,$result);
				}

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




		if ($_POST['type']=="get_pie_expenses") {
			
			$reg_date = date("Y", time());
			

			$cond_y = (isset($_POST['yy']) && $_POST['yy'] !='not')? ' AND YEAR(E.expenses_date)  	='.$_POST['yy']:'';
			

			$cond   = $cond_y ;

			if(strlen($cond) < 15){

				$cond = ' AND YEAR(E.expenses_date)  ='.$reg_date;
			
				$yyr 	= 	$reg_date;

			}else{
				$yyr 	= 	$_POST['yy'];

			}

			
			$res = $db->query("SELECT

									count(*) AS no_of_orders,
									MONTH(E.expenses_date) AS montn_no,
									MONTHNAME(E.expenses_date) AS month_name,

									sum(expenses_quantity * expenses_unit_price / expenses_usd_rate) AS final_price
											
						
								FROM
								 expenses E
								
								 
							WHERE
								(E.is_deleted = 0 AND E.expenses_currency=0 $cond )  GROUP BY MONTH(E.expenses_date)

					 "); // all type

			$res2 = $db->query("SELECT

									count(*) AS no_of_orders,
									MONTH(E.expenses_date) AS montn_no,
									MONTHNAME(E.expenses_date) AS month_name,

									sum(expenses_quantity * expenses_unit_price ) AS final_price
											
						
								FROM
								 expenses E
								
								 
							WHERE
								(E.is_deleted = 0 AND E.expenses_currency=1 $cond )  GROUP BY MONTH(E.expenses_date)

					 "); // all type

			// echo $cond;
			if ($res && $res2) {				
				$op  = array();
	
				while ($row = $res->fetch_assoc()) {				   
					$result['no_of_orders'] 			= $row['no_of_orders'];
					$result['montn_no']  				= $row['montn_no'];
					$result['month_name']  				= $row['month_name'];
					$result['final_price']  			= round($row['final_price'],2);
					$result['year']  					= $yyr;								
					array_push($op,$result);
				}

				while ($row2 = $res2->fetch_assoc()) {				   
					$result['no_of_orders'] 			= $row2['no_of_orders'];
					$result['montn_no']  				= $row2['montn_no'];
					$result['month_name']  				= $row2['month_name'];
					$result['final_price']  			= round($row2['final_price'],2);
					$result['year']  					= $yyr;								
					array_push($op,$result);
				}

				$prev_month  = '';
				$prev_price  = '';
				for ($i=0; $i <count($op) ; $i++) { 
					$curnt_month  = $op[$i]['montn_no'];
					if($curnt_month == $prev_month  ){
						$op[$i]['final_price'] = $op[$i]['final_price'] + $prev_price;
					}

					$prev_month  = $op[$i]['montn_no'];
					$prev_price  = $op[$i]['final_price'];

					# code...
				}

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



		if ($_POST['type']=="get_pie_purchase") {
			
			$reg_date = date("Y", time());
			

			$cond_y = (isset($_POST['yy']) && $_POST['yy'] !='not')? ' AND YEAR(P.purchase_date)  	='.$_POST['yy']:'';
			

			$cond   = $cond_y ;

			if(strlen($cond) < 15){

				$cond = ' AND YEAR(P.purchase_date)  ='.$reg_date;
			
				$yyr 	= 	$reg_date;

			}else{
				$yyr 	= 	$_POST['yy'];

			}


			$res = $db->query("SELECT					
									

									sum(buy_price_per_meter * item_length) AS total_price
					
					 			FROM purchase_item_detail PID
					 	 		
					 	 		LEFT JOIN purchase_item PP ON (PID.purchase_item_id_fk = PP.purchase_item_id)
					 	 		LEFT JOIN purchase P ON (PP.purchase_id_fk = P.purchase_id)

					 	 
								WHERE 
								(P.is_deleted = 0 AND PP.is_deleted = 0 AND PID.is_deleted = 0 AND  PID.is_part=0 $cond ) "); // all type
			
			$res = $db->query("SELECT

									count(*) AS no_of_orders,
									MONTH(P.purchase_date) AS montn_no,
									MONTHNAME(P.purchase_date) AS month_name,

									sum(buy_price_per_meter * item_length) AS final_price
											
						
								FROM
								 purchase P , purchase_item PP, purchase_item_detail PID
								
								 
							WHERE
								(PP.purchase_id_fk = P.purchase_id AND PID.purchase_item_id_fk = PP.purchase_item_id AND P.is_deleted = 0 AND PP.is_deleted = 0 AND PID.is_deleted = 0  AND  PID.is_part=0  $cond )  GROUP BY MONTH(P.purchase_date)

					 "); // all type

			// echo $cond;
			if ($res) {				
				$op  = array();
	
				while ($row = $res->fetch_assoc()) {				   
					$result['no_of_orders'] 			= $row['no_of_orders'];
					$result['montn_no']  				= $row['montn_no'];
					$result['month_name']  				= $row['month_name'];
					$result['final_price']  			= round($row['final_price'],2);
					$result['year']  					= $yyr;								
					array_push($op,$result);
				}

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





		if ($_POST['type']=="get_user_sells") {
			$reg_date  =  date("Y-m-d", time());
			$yesterday = date('Y-m-d',strtotime("-1 days"));

			$res = $db->query("Select *   from  (SELECT sum(sell_item_quantity) as qty , sum(sell_item_quantity * item_sell_price_per_unit) AS total_price,sell_item.created_by,sell_date, staff.username, staff.full_name,profile_img  FROM sell_item LEFT JOIN staff ON (staff.staff_id = sell_item.created_by) where sell_item.is_deleted = 0 AND sell_date >= '$reg_date' GROUP By sell_item.created_by)  AS tmp ORDER By qty DESC
			 "); // all type

			$res1 = $db->query("Select *   from  (SELECT sum(sell_item_quantity) as qty , sum(sell_item_quantity * item_sell_price_per_unit) AS total_price,sell_item.created_by,sell_date, staff.username, staff.full_name,profile_img  FROM sell_item LEFT JOIN staff ON (staff.staff_id = sell_item.created_by) where sell_item.is_deleted = 0 AND sell_date >= '$yesterday' AND sell_date < '$reg_date' GROUP By sell_item.created_by)  AS tmp ORDER By qty DESC
			 "); // all type
			if ($res) {				
				$op  = array();


				while ($row = $res->fetch_assoc()) {				   
						$result[0]  = $row['qty'];
						$result[1]  = $row['total_price'];
						$result[2]  = $row['full_name'];
						$result[3]  = $row['username'];
						$result[4]  = $row['profile_img'];
						$result[5]  = '0';					
						array_push($op,$result);
				}

				while ($row = $res1->fetch_assoc()) {				   
						$result[0]  = $row['qty'];
						$result[1]  = $row['total_price'];
						$result[2]  = $row['full_name'];
						$result[3]  = $row['username'];
						$result[4]  = $row['profile_img'];
						$result[5]  = '1';					
						array_push($op,$result);
				}

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










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

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

			$res = $db->query("SELECT P.*,C.category,C.category_type,
				(SELECT sum(purchase_quantity) from purchase where purchase.product_id_fk = P.product_id AND purchase.is_deleted=0) as total_quantity,
				(SELECT sum(sell_quantity) from sell_item where sell_item.product_id_fk = P.product_id AND sell_item.is_deleted=0) as sold_quantity,
				(COALESCE((SELECT sum(COALESCE(purchase_quantity, 0)) from purchase where purchase.product_id_fk = P.product_id AND purchase.is_deleted=0),0)  -
					COALESCE((SELECT sum(COALESCE(sell_quantity, 0)) from sell_item where sell_item.product_id_fk = P.product_id AND sell_item.is_deleted=0),0) )  AS Remianed_quantity
				FROM product P, category C WHERE P.category_id_fk=C.category_id AND P.is_deleted = 0 AND C.category_type=0");
			if ($res) {
				//$row = $res->fetch_all();
				$op  = array();
				while ($row = $res->fetch_assoc()) {
				   
						$result[0]  = $row['product_id'];
						$result[1]  = $row['product'];
						$result[2]  = $row['part_number'];
						$result[3]  = $row['made_in'];
						$result[4]  = $row['sell_price'];
						$result[5]  = $row['sell_price_percentage'];
						$result[6]  = $row['average_price'];
						$result[7]  = $row['category_id_fk'];
						$result[8]  = $row['is_deleted'];
						$result[9]  = $row['created_at'];
						$result[10] = $row['updated_at'];
						$result[11] = $row['deleted_at'];
						$result[12] = $row['created_by'];
						$result[13] = $row['last_action'];
						$result[14] = $row['last_action_by'];
						$result[15] = $row['category'];
						$result[16] = $row['category_type'];
						$result[17] = $row['total_quantity'];
						$result[18] = $row['sold_quantity'];
						$result[19] = $row['Remianed_quantity'];
						array_push($op,$result);
				}
				echo json_encode($op);
				exit;
			}else{
				echo 0;
				exit;
			}
		}



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