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



date_default_timezone_set($_SESSION['SYSTimezone']);

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

			
			$d1 			= $_POST['d1'];
			$d2 			= $_POST['d2'];
			$product_id 	= $_POST['product_id'];
			$pharmacy_id 	= $_POST['pharmacy_id'];
			$saleman_id 	= $_POST['saleman_id'];
			$doctor_id 		= $_POST['doctor_id'];
			$city_id 		= $_POST['city_id'];
			$zone_id 		= $_POST['zone_id'];
			$sub_zone_id 	= $_POST['sub_zone_id'];
			$quarter_id 	= $_POST['quarter_id'];
			$street_id 		= $_POST['street_id'];

		


			
			$cond = '';

			$cond = (isset($_POST['d1']) && isset($_POST['d2']))? ' AND (R.report_date) BETWEEN "'.$_POST['d1'].'" AND "'.$_POST['d2'].'"':'';


			if($product_id > 0){ 
				$cond .= " AND RPP.product_id_fk =$product_id ";
			}
			if($pharmacy_id > 0){ 
				$cond .= " AND RPP.pharmacy_id_fk =$pharmacy_id ";
			}

			if($saleman_id > 0){ 
				$cond .= " AND R.saleman_id_fk =$saleman_id ";
			}
			if($doctor_id > 0){ 
				$cond .= " AND R.doctor_id_fk =$doctor_id ";
			}
			if($city_id > 0){ 
				$cond .= " AND R.city_id_fk =$city_id ";
			}

			if($zone_id > 0){ 
				$cond .= " AND R.zone_id_fk =$zone_id ";
			}

			

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

			$res = $db->query("SELECT
										report_id,
										report_number,
										report_title,
										report_date,
										doctor_offer,
										offer_money,
										ordered_quantity,
										order_sell_price,
										rate_percent,
										opinion,
										pharmacy_offer,
										is_delivered,
										product_name,
										category,
										username,
										city_name,
										zone_name,
										doctor_name,
										pharmacy_name,
										product_id



								FROM report R

									LEFT JOIN report_product_pharmacy RPP ON (RPP.report_id_fk=R.report_id)
									LEFT JOIN product P ON (P.product_id=RPP.product_id_fk)
									LEFT JOIN category G ON (G.category_id=P.category_id_fk)
									LEFT JOIN pharmacy PH ON (PH.pharmacy_id=R.pharmacy_id_fk)
									LEFT JOIN doctor D ON (D.doctor_id=R.doctor_id_fk)
									LEFT JOIN staff S ON (S.staff_id=R.saleman_id_fk)
									LEFT JOIN city C ON (C.city_id=R.city_id_fk)
									LEFT JOIN zone Z ON (Z.zone_id=R.zone_id_fk)


								WHERE R.is_deleted=0 AND RPP.is_deleted=0  $cond");

			if ($res) {				
				
				
				while ($row = $res->fetch_assoc()) {	

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

						$result['report_id']  		= $row['report_id'];
						$result['report_number']  	= $row['report_number'];
						$result['report_title']  	= $row['report_title'];
						$result['report_date']  	= $time;
						$result['doctor_offer']  	= $row['doctor_offer'];
						$result['offer_money']  	= $row['offer_money'];
						$result['ordered_quantity'] = $row['ordered_quantity'];
						$result['order_sell_price'] = $row['order_sell_price'];
						$result['rate_percent']  	= $row['rate_percent'];
						$result['opinion']  		= $row['opinion'];
						$result['pharmacy_offer']  	= $row['pharmacy_offer'];
						$result['is_delivered']  	= $row['is_delivered'];
						$result['product_id']  		= $row['product_id'];
						$result['product_name']  	= $row['product_name'];
						$result['category']  		= $row['category'];
						$result['username']  		= $row['username'];
						$result['city_name']  		= $row['city_name'];
						$result['zone_name']  		= $row['zone_name'];		
						$result['doctor_name']  	= $row['doctor_name'];		
						$result['pharmacy_name']  	= $row['pharmacy_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']=="doctor_offer") {

			
			$d1 			= $_POST['d1'];
			$d2 			= $_POST['d2'];
			$product_id 	= $_POST['product_id'];
			$pharmacy_id 	= $_POST['pharmacy_id'];
			$saleman_id 	= $_POST['saleman_id'];
			$doctor_id 		= $_POST['doctor_id'];
			$city_id 		= $_POST['city_id'];
			$zone_id 		= $_POST['zone_id'];
			$sub_zone_id 	= $_POST['sub_zone_id'];
			$quarter_id 	= $_POST['quarter_id'];
			$street_id 		= $_POST['street_id'];

		


			
			$cond = '';

			$cond = (isset($_POST['d1']) && isset($_POST['d2']))? ' AND (R.report_date) BETWEEN "'.$_POST['d1'].'" AND "'.$_POST['d2'].'"':'';


			

			if($saleman_id > 0){ 
				$cond .= " AND R.saleman_id_fk =$saleman_id ";
			}
			if($doctor_id > 0){ 
				$cond .= " AND R.doctor_id_fk =$doctor_id ";
			}
			if($city_id > 0){ 
				$cond .= " AND R.city_id_fk =$city_id ";
			}

			if($zone_id > 0){ 
				$cond .= " AND R.zone_id_fk =$zone_id ";
			}

			

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

			$res = $db->query("SELECT
										report_id,
										report_number,
										report_title,
										report_date,
										doctor_offer,
										offer_money,
										rating_doctor,
										SP.doctor_specialty,
										username,
										city_name,
										zone_name,
										doctor_name



								FROM report R

									
									LEFT JOIN doctor D ON (D.doctor_id=R.doctor_id_fk)
									LEFT JOIN staff S ON (S.staff_id=R.saleman_id_fk)
									LEFT JOIN city C ON (C.city_id=R.city_id_fk)
									LEFT JOIN zone Z ON (Z.zone_id=R.zone_id_fk)
									LEFT JOIN specialty SP ON (D.specialty_id_fk = SP.specialty_id)


								WHERE R.is_deleted=0  $cond");

			if ($res) {				
				
				
				while ($row = $res->fetch_assoc()) {				   
						$dt 						= new DateTime($row['report_date']);
						$time 						= $dt->format('Y-m-d');

						$result['report_id']  		= $row['report_id'];
						$result['report_number']  	= $row['report_number'];
						$result['report_title']  	= $row['report_title'];
						$result['report_date']  	= $time;
						$result['doctor_offer']  	= $row['doctor_offer'];
						$result['offer_money']  	= $row['offer_money'];
						$result['rating_doctor'] 	= $row['rating_doctor'];
						
						$result['username']  		= $row['username'];
						$result['city_name']  		= $row['city_name'];
						$result['zone_name']  		= $row['zone_name'];		
						$result['doctor_name']  	= $row['doctor_name'];		
						$result['doctor_specialty'] = $row['doctor_specialty'];		
						
						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']=="ordered_product_by_qty") {

			
			$d1 			= $_POST['d1'];
			$d2 			= $_POST['d2'];
			$product_id 	= $_POST['product_id'];
			$pharmacy_id 	= $_POST['pharmacy_id'];
			$saleman_id 	= $_POST['saleman_id'];
			$doctor_id 		= $_POST['doctor_id'];
			$city_id 		= $_POST['city_id'];
			$zone_id 		= $_POST['zone_id'];
			$sub_zone_id 	= $_POST['sub_zone_id'];
			$quarter_id 	= $_POST['quarter_id'];
			$street_id 		= $_POST['street_id'];

		


			
			$cond = '';

			$cond = (isset($_POST['d1']) && isset($_POST['d2']))? ' AND (R.report_date) BETWEEN "'.$_POST['d1'].'" AND "'.$_POST['d2'].'"':'';


			if($product_id > 0){ 
				$cond .= " AND RPP.product_id_fk =$product_id ";
			}
			if($pharmacy_id > 0){ 
				$cond .= " AND RPP.pharmacy_id_fk =$pharmacy_id ";
			}

			if($saleman_id > 0){ 
				$cond .= " AND R.saleman_id_fk =$saleman_id ";
			}
			if($doctor_id > 0){ 
				$cond .= " AND R.doctor_id_fk =$doctor_id ";
			}
			if($city_id > 0){ 
				$cond .= " AND R.city_id_fk =$city_id ";
			}

			if($zone_id > 0){ 
				$cond .= " AND R.zone_id_fk =$zone_id ";
			}

			

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

			$res = $db->query("SELECT
										report_id,
										report_number,
										report_title,
										report_date,
										doctor_offer,
										offer_money,
										SUM(ordered_quantity) as total_qty,
										order_sell_price,
										rate_percent,
										opinion,
										pharmacy_offer,
										is_delivered,
										product_name,
										category,
										username,
										city_name,
										zone_name,
										doctor_name,
										product_code,
										made_in,
										pharmacy_name,
										product_id



								FROM report R

									LEFT JOIN report_product_pharmacy RPP ON (RPP.report_id_fk=R.report_id)
									LEFT JOIN product P ON (P.product_id=RPP.product_id_fk)
									LEFT JOIN category G ON (G.category_id=P.category_id_fk)
									LEFT JOIN pharmacy PH ON (PH.pharmacy_id=R.pharmacy_id_fk)
									LEFT JOIN doctor D ON (D.doctor_id=R.doctor_id_fk)
									LEFT JOIN staff S ON (S.staff_id=R.saleman_id_fk)
									LEFT JOIN city C ON (C.city_id=R.city_id_fk)
									LEFT JOIN zone Z ON (Z.zone_id=R.zone_id_fk)


								WHERE R.is_deleted=0 AND RPP.is_deleted=0  $cond GROUP BY RPP.product_id_fk ORDER BY total_qty DESC");

			if ($res) {				
				
				
				while ($row = $res->fetch_assoc()) {

					$pharmacy_name 	= "لكل الصيدليات";
					$doctor_name 	= "لجميع الاطباء";
					$city_name 		= "في كل المحافظات";
					$zone_name 		= "في كل المناطق";
					$saleman_name 	= "لجميع المندوبين";


					if($pharmacy_id > 0){ 
						$pharmacy_name = $row['pharmacy_name'];
					}

					if($saleman_id > 0){ 
						$saleman_name = $row['username'];
					}
					if($doctor_id > 0){ 
						$doctor_name = $row['doctor_name'];
					}
					if($city_id > 0){ 
						$city_name = $row['city_name'];
					}

					if($zone_id > 0){ 
						$zone_name = $row['zone_name'];
					}	
					
					$result['ordered_quantity'] = $row['total_qty'];
					$result['order_sell_price'] = $row['order_sell_price'];
					$result['product_id']  		= $row['product_id'];
					$result['product_name']  	= $row['product_name'];
					$result['product_code']  	= $row['product_code'];
					$result['made_in']  		= $row['made_in'];
					$result['category']  		= $row['category'];
					$result['username']  		= $saleman_name;
					$result['city_name']  		= $city_name;
					$result['zone_name']  		= $zone_name;		
					$result['doctor_name']  	= $doctor_name;		
					$result['pharmacy_name']  	= $pharmacy_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']=="saleman_visit") {

			
			$d1 			= $_POST['d1'];
			$d2 			= $_POST['d2'];
			$product_id 	= $_POST['product_id'];
			$pharmacy_id 	= $_POST['pharmacy_id'];
			$saleman_id 	= $_POST['saleman_id'];
			$doctor_id 		= $_POST['doctor_id'];
			$city_id 		= $_POST['city_id'];
			$zone_id 		= $_POST['zone_id'];
			$sub_zone_id 	= $_POST['sub_zone_id'];
			$quarter_id 	= $_POST['quarter_id'];
			$street_id 		= $_POST['street_id'];

		


			
			$cond = '';

			$cond = (isset($_POST['d1']) && isset($_POST['d2']))? ' AND (R.report_date) BETWEEN "'.$_POST['d1'].'" AND "'.$_POST['d2'].'"':'';


			

			if($saleman_id > 0){ 
				$cond .= " AND R.saleman_id_fk =$saleman_id ";
			}
			if($doctor_id > 0){ 
				$cond .= " AND R.doctor_id_fk =$doctor_id ";
			}
			if($city_id > 0){ 
				$cond .= " AND R.city_id_fk =$city_id ";
			}

			if($zone_id > 0){ 
				$cond .= " AND R.zone_id_fk =$zone_id ";
			}

			

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

			$res = $db->query("SELECT
										report_id,
										report_number,
										report_title,
										report_date,
										doctor_offer,
										offer_money,
										rating_doctor,
										SP.doctor_specialty,
										username,
										city_name,
										zone_name,
										report_type,
										visit_type,
										is_out_plan,
										doctor_name



								FROM report R

									
									LEFT JOIN doctor D ON (D.doctor_id=R.doctor_id_fk)
									LEFT JOIN staff S ON (S.staff_id=R.saleman_id_fk)
									LEFT JOIN city C ON (C.city_id=R.city_id_fk)
									LEFT JOIN zone Z ON (Z.zone_id=R.zone_id_fk)
									LEFT JOIN specialty SP ON (D.specialty_id_fk = SP.specialty_id)


								WHERE R.is_deleted=0  $cond");

			if ($res) {				
				
				
				while ($row = $res->fetch_assoc()) {				   
						$dt 						= new DateTime($row['report_date']);
						$time 						= $dt->format('Y-m-d');

						$result['report_id']  		= $row['report_id'];
						$result['report_number']  	= $row['report_number'];
						$result['report_title']  	= $row['report_title'];
						$result['report_date']  	= $time;
						$result['doctor_offer']  	= $row['doctor_offer'];
						$result['offer_money']  	= $row['offer_money'];
						$result['rating_doctor'] 	= $row['rating_doctor'];
						$result['report_type'] 		= $row['report_type'];
						$result['visit_type'] 		= $row['visit_type'];
						$result['is_out_plan'] 		= $row['is_out_plan'];
						
						$result['username']  		= $row['username'];
						$result['city_name']  		= $row['city_name'];
						$result['zone_name']  		= $row['zone_name'];		
						$result['doctor_name']  	= $row['doctor_name'];		
						$result['doctor_specialty'] = $row['doctor_specialty'];		
						
						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']=="total_saleman_visit") {

			
			$d1 			= $_POST['d1'];
			$d2 			= $_POST['d2'];
			$product_id 	= $_POST['product_id'];
			$pharmacy_id 	= $_POST['pharmacy_id'];
			$saleman_id 	= $_POST['saleman_id'];
			$doctor_id 		= $_POST['doctor_id'];
			$city_id 		= $_POST['city_id'];
			$zone_id 		= $_POST['zone_id'];
			$sub_zone_id 	= $_POST['sub_zone_id'];
			$quarter_id 	= $_POST['quarter_id'];
			$street_id 		= $_POST['street_id'];

		


			
			$cond = '';

			$cond = (isset($_POST['d1']) && isset($_POST['d2']))? ' AND (R.report_date) BETWEEN "'.$_POST['d1'].'" AND "'.$_POST['d2'].'"':'';


			

			if($saleman_id > 0){ 
				$cond .= " AND R.saleman_id_fk 	=$saleman_id ";
			}
			if($pharmacy_id > 0){ 
				$cond .= " AND R.pharmacy_id_fk	=$pharmacy_id ";
			}
			if($doctor_id > 0){ 
				$cond .= " AND R.doctor_id_fk 	=$doctor_id ";
			}
			if($city_id > 0){ 
				$cond .= " AND R.city_id_fk 	=$city_id ";
			}

			if($zone_id > 0){ 
				$cond .= " AND R.zone_id_fk 	=$zone_id ";
			}

			

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

			$res = $db->query("SELECT
										report_id,
										COUNT(report_id) as total_visits,

										(SUM(CASE WHEN R.visit_type = '0'
		                    				 THEN 1 
		                    				 ELSE 0 end)
		                     			) AS total_visit_doctor,

		                     			(SUM(CASE WHEN R.visit_type = '1'
		                    				 THEN 1 
		                    				 ELSE 0 end)
		                     			) AS total_visit_pharmacy,

										saleman_id_fk,
										
										username,
										city_name,
										zone_name,
										report_type,
										visit_type,
										pharmacy_name,
										doctor_name



								FROM report R

									
									LEFT JOIN doctor D ON (D.doctor_id=R.doctor_id_fk)
									LEFT JOIN staff S ON (S.staff_id=R.saleman_id_fk)
									LEFT JOIN city C ON (C.city_id=R.city_id_fk)
									LEFT JOIN zone Z ON (Z.zone_id=R.zone_id_fk)
									LEFT JOIN pharmacy PH ON (R.pharmacy_id_fk = PH.pharmacy_id)


								WHERE R.is_deleted=0  $cond  GROUP BY R.saleman_id_fk ORDER BY total_visits DESC ");

			if ($res) {				
				
				
				while ($row = $res->fetch_assoc()) {

					$pharmacy_name 	= "لكل الصيدليات";
					$doctor_name 	= "لجميع الاطباء";
					$city_name 		= "في كل المحافظات";
					$zone_name 		= "في كل المناطق";
					

					if($pharmacy_id > 0){ 
						$pharmacy_name = $row['pharmacy_name'];
					}
					
					if($doctor_id > 0){ 
						$doctor_name = $row['doctor_name'];
					}
					if($city_id > 0){ 
						$city_name = $row['city_name'];
					}

					if($zone_id > 0){ 
						$zone_name = $row['zone_name'];
					}

					

					$result['saleman_id_fk']  			= $row['saleman_id_fk'];
					$result['total_visits']  			= $row['total_visits'];
					$result['total_visit_doctor']  		= $row['total_visit_doctor'];
					$result['total_visit_pharmacy']  	= $row['total_visit_pharmacy'];
					
					$result['username']  				= $row['username'];
					$result['city_name']  				= $city_name;
					$result['zone_name']  				= $zone_name;		
					$result['doctor_name']  			= $doctor_name;		
					$result['pharmacy_name'] 			= $pharmacy_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;
			}
			
		}







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