<?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
			$cond1 = isset($_GET['id'])? ' AND D.class_id_fk='.$_GET['id']:'';
			
			$cond2 = isset($_GET['zid'])? ' AND D.zone_id_fk="'.$_GET['zid'].'"':'';
			$cond3 = isset($_GET['sid'])? ' AND D.sub_class_id_fk="'.$_GET['sid'].'"':'';
			$cond4 = isset($_GET['cid'])? ' AND D.city_id_fk="'.$_GET['cid'].'"':'';
			$cond5 = isset($_GET['city_id'])? ' AND D.city_id_fk="'.$_GET['city_id'].'"':'';

			$cond5 = '';

			if (isset($_GET['city_id'])) {
				$pidc = explode(',', $_GET['city_id']);

				$cond5 = ' AND ( ';
				$orr = ' ';
				for ($mr=0; $mr <count($pidc) ; $mr++) { 

					if ($pidc[$mr] !='') {
						$cond5 =   $cond5 . $orr . ' D.city_id_fk='.$pidc[$mr] ;
						$orr = ' OR ';
					}
					
					
				}
				
				$cond5 = $cond5 . ' ) ';

			}

			$cond6 = '';

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

				$cond6 = ' AND ( ';
				$orr = ' ';
				for ($mr=0; $mr <count($pids) ; $mr++) { 

					if ($pids[$mr] !='') {
						$cond6 =   $cond6 . $orr . ' D.specialty_id_fk='.$pids[$mr] ;
						$orr = ' OR ';
					}
					
					
				}
				
				$cond6 = $cond6 . ' ) ';

			}

			$cond = $cond1 . " " . $cond2 . " " . $cond3 . " " . $cond4 . " " . $cond5 . " " . $cond6;
			$table  =  '(
				SELECT
					doctor_id,
					
					doctor_name,
					doctor_address,
					SP.doctor_specialty,
					SP.specialty_description,
					doctor_phone_no1,
					doctor_phone_no2,
					doctor_location,
					doctor_email,
					doctor_note,
					class_name,
					sub_class_name,
					city_name,
					full_name,
					zone_name,
					sub_zone_name,
					quarter_name,
					street_name
					
					
					
				FROM
					doctor D
					LEFT JOIN class CL ON (D.class_id_fk = CL.class_id)
					LEFT JOIN city C ON (D.city_id_fk = C.city_id)
					LEFT JOIN sub_class S ON (D.sub_class_id_fk = S.sub_class_id)
					LEFT JOIN zone Z ON (D.zone_id_fk = Z.zone_id)
					LEFT JOIN sub_zone SZ ON (D.sub_zone_id_fk = SZ.sub_zone_id)
					LEFT JOIN quarter Q ON (D.quarter_id_fk = Q.quarter_id)
					LEFT JOIN street SR ON (D.street_id_fk = SR.street_id)
                    LEFT JOIN staff ST ON (D.created_by = ST.staff_id)
                    LEFT JOIN specialty SP ON (D.specialty_id_fk = SP.specialty_id)

					
					
				WHERE
					( D.is_deleted = 0  '.$cond.') 
			) AS ALLTBL';
			// Table's primary key
			//echo $table;
			$primaryKey = 'doctor_id';
			$columns = array(
			    array( 'db' => 'doctor_id', 'dt' => 0 ),
			    array( 'db' => 'doctor_name', 'dt' => 1 ),

			    array( 'db' => 'doctor_address', 'dt' => 2 ),
			    array( 'db' => 'doctor_specialty', 'dt' => 3 ),
			    array( 'db' => 'specialty_description', 'dt' => 4 ),
			    array( 'db' => 'doctor_phone_no1', 'dt' => 5 ),
			    array( 'db' => 'doctor_note', 'dt' => 6 ),
			    array( 'db' => 'class_name', 'dt' => 7 ),
			    array( 'db' => 'sub_class_name', 'dt' => 8 ),
			    array( 'db' => 'street_name', 'dt' => 9 ),
			    array( 'db' => 'quarter_name', 'dt' => 10 ),
			    array( 'db' => 'sub_zone_name', 'dt' => 11 ),
			    array( 'db' => 'zone_name', 'dt' => 12 ),
			    array( 'db' => 'city_name', 'dt' => 13 ),	
			    array( 'db' => 'full_name', '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") {

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

			$res = $db->query("SELECT 	doctor_id,
										doctor_name,
										S.doctor_specialty ,
										class_id_fk,
										sub_class_id_fk,
										city_id_fk,
										zone_id_fk 
									FROM doctor D 

									LEFT JOIN specialty S ON (D.specialty_id_fk = S.specialty_id)

								WHERE D.is_deleted = 0 ORDER BY D.doctor_name DESC");
			if ($res) {
				//$row = $res->fetch_all();
				$op  = array();
				while ($row = $res->fetch_assoc()) {
				   
						$result['doctor_id']  		= $row['doctor_id'];
						$result['doctor_name']  	= $row['doctor_name'];
						$result['doctor_specialty'] = $row['doctor_specialty'];
						$result['class_id_fk']  	= $row['class_id_fk'];
						$result['sub_class_id_fk']  = $row['sub_class_id_fk'];
						$result['city_id_fk']  		= $row['city_id_fk'];
						$result['zone_id_fk']  		= $row['zone_id_fk'];
						array_push($op,$result);
				}
				echo json_encode($op);
				exit;
			}else{
				echo 0;
				exit;
			}
		}

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

			$id = $_POST['id'];

			$cond= '';
			if ($id>0) {
				$cond = ' AND D.city_id_fk='.$id;
			}

			$res = $db->query("SELECT 	doctor_id,
										doctor_name,
										S.doctor_specialty ,
										class_id_fk,
										sub_class_id_fk,
										city_id_fk,
										zone_id_fk 
									FROM doctor D 

									LEFT JOIN specialty S ON (D.specialty_id_fk = S.specialty_id)


								WHERE D.is_deleted = 0  $cond ORDER BY D.doctor_name DESC");
			if ($res) {
				//$row = $res->fetch_all();
				$op  = array();
				while ($row = $res->fetch_assoc()) {
				   
						$result['doctor_id']  		= $row['doctor_id'];
						$result['doctor_name']  	= $row['doctor_name'];
						$result['doctor_specialty'] = $row['doctor_specialty'];
						$result['class_id_fk']  	= $row['class_id_fk'];
						$result['sub_class_id_fk']  = $row['sub_class_id_fk'];
						$result['city_id_fk']  		= $row['city_id_fk'];
						$result['zone_id_fk']  		= $row['zone_id_fk'];
						array_push($op,$result);
				}
				echo json_encode($op);
				exit;
			}else{
				echo 0;
				exit;
			}
		}

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

			$id 	= $_POST['id'];
			$typo 	= $_POST['typo'];

			$cond= '';

			if ($typo=='city') {
				$cond = ' AND D.city_id_fk='.$id;
			}elseif($typo=='zone'){
				$cond = ' AND D.zone_id_fk='.$id;
			}elseif($typo=='sub-zone'){
				$cond = ' AND D.sub_zone_id_fk='.$id;
			}elseif($typo=='quarter'){
				$cond = ' AND D.quarter_id_fk='.$id;
			}elseif($typo=='street'){
				$cond = ' AND D.street_id_fk='.$id;
			}

			$res = $db->query("SELECT 	doctor_id,
										doctor_name,
										S.doctor_specialty ,
										S.specialty_description,
										class_id_fk,
										sub_class_id_fk,
										D.city_id_fk,
										D.zone_id_fk, 
										D.sub_zone_id_fk, 
										D.quarter_id_fk, 
										D.street_id_fk,
										city_name,
										zone_name,
										sub_zone_name,
										quarter_name,
										street_name

								FROM doctor D
									LEFT JOIN specialty S ON (D.specialty_id_fk = S.specialty_id)
									LEFT JOIN city C ON (D.city_id_fk = C.city_id)
									LEFT JOIN zone Z ON (D.zone_id_fk = Z.zone_id)
									LEFT JOIN sub_zone SZ ON (D.sub_zone_id_fk = SZ.sub_zone_id)
									LEFT JOIN quarter Q ON (D.quarter_id_fk = Q.quarter_id)
									LEFT JOIN street SR ON (D.street_id_fk = SR.street_id)
								WHERE D.is_deleted = 0  $cond ORDER BY D.doctor_name DESC");
			if ($res) {
				//$row = $res->fetch_all();
				$op  = array();
				while ($row = $res->fetch_assoc()) {
				   
						$result['doctor_id']  		= $row['doctor_id'];
						$result['doctor_name']  	= $row['doctor_name'];
						$result['doctor_specialty'] = $row['doctor_specialty'];
						$result['specialty_description'] = $row['specialty_description'];
						$result['class_id_fk']  	= $row['class_id_fk'];
						$result['sub_class_id_fk']  = $row['sub_class_id_fk'];
						$result['city_id_fk']  		= $row['city_id_fk'];
						$result['zone_id_fk']  		= $row['zone_id_fk'];
						$result['sub_zone_id_fk']  	= $row['sub_zone_id_fk'];
						$result['quarter_id_fk']  	= $row['quarter_id_fk'];
						$result['street_id_fk']  	= $row['street_id_fk'];
						$result['city_name']  		= $row['city_name'];
						$result['zone_name']  		= $row['zone_name'];
						$result['sub_zone_name']  	= $row['sub_zone_name'];
						$result['quarter_name']  	= $row['quarter_name'];
						$result['street_name']  	= $row['street_name'];
						array_push($op,$result);
				}
				echo json_encode($op);
				exit;
			}else{
				echo 0;
				exit;
			}
		}

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

			
			$typo 	= $_POST['typo'];

			$cond= '';
			$ids = $_POST['id'];

			$cond = ' AND ( ';
			$orr = ' ';
				
			$orders =' D.doctor_name ';

			if ($typo=='city') {
				foreach ($ids as $sz_id){
					if ($sz_id !='') {
						$cond =   $cond . $orr . ' D.city_id_fk='.$sz_id;
						$orr = ' OR ';
					}
				}
				$orders =' D.city_id_fk ';

			}elseif($typo=='zone'){
				foreach ($ids as $sz_id){
					if ($sz_id !='') {
						$cond =   $cond . $orr . ' D.zone_id_fk='.$sz_id;
						$orr = ' OR ';
					}
				}
				$orders =' D.zone_id_fk ';

			}elseif($typo=='sub-zone'){
				foreach ($ids as $sz_id){
					if ($sz_id !='') {
						$cond =   $cond . $orr . ' D.sub_zone_id_fk='.$sz_id;
						$orr = ' OR ';
					}
				}
				$orders =' D.sub_zone_id_fk ';

			}elseif($typo=='quarter'){
				foreach ($ids as $sz_id){
					if ($sz_id !='') {
						$cond =   $cond . $orr . ' D.quarter_id_fk='.$sz_id;
						$orr = ' OR ';
					}
				}
				$orders =' D.quarter_id_fk ';

			}elseif($typo=='street'){
				foreach ($ids as $sz_id){
					if ($sz_id !='') {
						$cond =   $cond . $orr . ' D.street_id_fk='.$sz_id;
						$orr = ' OR ';
					}
				}
				$orders =' D.street_id_fk ';

			}

			$cond = $cond . ' ) ';


			$res = $db->query("SELECT 	doctor_id,
										doctor_name,
										S.doctor_specialty ,
										S.specialty_description,
										class_id_fk,
										sub_class_id_fk,
										D.city_id_fk,
										D.zone_id_fk, 
										D.sub_zone_id_fk, 
										D.quarter_id_fk, 
										D.street_id_fk,
										city_name,
										zone_name,
										sub_zone_name,
										quarter_name,
										street_name

								FROM doctor D
									LEFT JOIN specialty S ON (D.specialty_id_fk = S.specialty_id)
									LEFT JOIN city C ON (D.city_id_fk = C.city_id)
									LEFT JOIN zone Z ON (D.zone_id_fk = Z.zone_id)
									LEFT JOIN sub_zone SZ ON (D.sub_zone_id_fk = SZ.sub_zone_id)
									LEFT JOIN quarter Q ON (D.quarter_id_fk = Q.quarter_id)
									LEFT JOIN street SR ON (D.street_id_fk = SR.street_id)
                    


								WHERE D.is_deleted = 0  $cond ORDER BY $orders DESC");
			if ($res) {
				//$row = $res->fetch_all();
				$op  = array();
				while ($row = $res->fetch_assoc()) {
				   
						$result['doctor_id']  		= $row['doctor_id'];
						$result['doctor_name']  	= $row['doctor_name'];
						$result['doctor_specialty'] = $row['doctor_specialty'];
						$result['specialty_description'] = $row['specialty_description'];
						$result['class_id_fk']  	= $row['class_id_fk'];
						$result['sub_class_id_fk']  = $row['sub_class_id_fk'];
						$result['city_id_fk']  		= $row['city_id_fk'];
						$result['zone_id_fk']  		= $row['zone_id_fk'];
						$result['sub_zone_id_fk']  	= $row['sub_zone_id_fk'];
						$result['quarter_id_fk']  	= $row['quarter_id_fk'];
						$result['street_id_fk']  	= $row['street_id_fk'];
						$result['city_name']  		= $row['city_name'];
						$result['zone_name']  		= $row['zone_name'];
						$result['sub_zone_name']  	= $row['sub_zone_name'];
						$result['quarter_name']  	= $row['quarter_name'];
						$result['street_name']  	= $row['street_name'];
						array_push($op,$result);
				}
				echo json_encode($op);
				exit;
			}else{
				echo $db->error;
				exit;
			}
		}

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

			$id = $_POST['sub_zone_id'];

			$cond= '';
			if ($id>0) {
				$cond = ' AND D.sub_zone_id_fk='.$id;
			}

			$res = $db->query("SELECT 	doctor_id,
										doctor_name,
										S.doctor_specialty ,
										S.specialty_description ,
										class_id_fk,
										sub_class_id_fk,
										city_id_fk,
										zone_id_fk 
									FROM doctor D 
									LEFT JOIN specialty S ON (D.specialty_id_fk = S.specialty_id)
									

								WHERE D.is_deleted = 0  $cond ORDER BY D.doctor_name DESC");
			if ($res) {
				//$row = $res->fetch_all();
				$op  = array();
				while ($row = $res->fetch_assoc()) {
				   
						$result['doctor_id']  		= $row['doctor_id'];
						$result['doctor_name']  	= $row['doctor_name'];
						$result['doctor_specialty'] = $row['doctor_specialty'];
						$result['class_id_fk']  	= $row['class_id_fk'];
						$result['sub_class_id_fk']  = $row['sub_class_id_fk'];
						$result['city_id_fk']  		= $row['city_id_fk'];
						$result['zone_id_fk']  		= $row['zone_id_fk'];
						array_push($op,$result);
				}
				echo json_encode($op);
				exit;
			}else{
				echo 0;
				exit;
			}
		}

		


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

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

			$res = $db->query("SELECT 
									doctor_id,
				
									doctor_name,
									doctor_address,
									specialty_id_fk,
									doctor_phone_no1,
									doctor_phone_no2,
									doctor_email,
									doctor_note,
									class_id_fk,
									sub_class_id_fk,
									zone_id_fk,
									
									city_id_fk,
									sub_zone_id_fk,
									quarter_id_fk,
									street_id_fk


				 				FROM doctor D  WHERE D.is_deleted = 0 AND doctor_id = '$id'");
			if ($res) {
				
				
				while ($row = $res->fetch_assoc()) {
				   
						$result['doctor_id']  		= $row['doctor_id'];
						$result['doctor_name']  	= $row['doctor_name'];
						$result['doctor_address']  	= $row['doctor_address'];
						$result['specialty_id_fk']  = $row['specialty_id_fk'];
						$result['doctor_phone_no2'] = $row['doctor_phone_no2'];
						$result['doctor_phone_no1'] = $row['doctor_phone_no1'];
						$result['doctor_email']  	= $row['doctor_email'];
						$result['doctor_note']  	= $row['doctor_note'];
						$result['class_id_fk']  	= $row['class_id_fk'];
						$result['sub_class_id_fk']  = $row['sub_class_id_fk'];
						$result['zone_id_fk']  		= $row['zone_id_fk'];
						$result['city_id_fk']  		= $row['city_id_fk'];
						$result['sub_zone_id_fk']  	= $row['sub_zone_id_fk'];
						$result['quarter_id_fk']  	= $row['quarter_id_fk'];
						$result['street_id_fk']  	= $row['street_id_fk'];
						
						array_push($op['data'],$result);
				}
				$op['is_success'] = true;
				echo json_encode($op);
				exit;
			}else{
				$op['is_success'] = false;
				$op['data'] 	  = "No Data";
				echo json_encode($op);

				exit;
			}
		}


	


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

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

			$reg_date = date("Y-m-d h:i:s", time());
			$lastaction = "Delete Image";

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

			

			

	  		$res = $db->query("UPDATE product SET image_path = '',last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE product_id=$id");
	    	if ($res){
	        $op['is_success'] = true;
	        		if(@unlink('../'.$img_path)){

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

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

			$user_id = $_POST['obj_id'];

			$res = $db->query("SELECT * FROM product_image WHERE is_deleted=0 AND product_id_fk = '$user_id'");
			if ($res) {
				//$row = $res->fetch_all();
				$op  = array();
				while ($row = $res->fetch_assoc()) {
				    // do what you need.
						$result[0] = $row['image_path'];
						array_push($op,$result);
				}

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


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

			$city_id_fk				=addslashes($_POST["print_city_id_fk"]);
      		$zone_id_fk				=addslashes($_POST["print_zone_id_fk"]);
      		$class_id_fk			=addslashes($_POST["print_class_id_fk"]);
      		$sub_class_id_fk		=addslashes($_POST["print_sub_class_id_fk"]);
      		$sub_zone_id_fk			=addslashes($_POST["print_sub_zone_id_fk"]);
			$quarter_id_fk			=addslashes($_POST["print_quarter_id_fk"]);
			$street_id_fk			=addslashes($_POST["print_street_id_fk"]);
			$specialty_id_fk		=$_POST["print_specialty_id_fk"];

			$cond1 	= "";
			$cond 	= "";

			
			

			if ($city_id_fk > 0) {
				$cond .= " AND D.city_id_fk=$city_id_fk ";
			}
			if ($zone_id_fk > 0) {
				$cond .= " AND D.zone_id_fk=$zone_id_fk ";
			}
			if ($class_id_fk > 0) {
				$cond .= " AND D.class_id_fk=$class_id_fk ";
			}
			if ($sub_class_id_fk > 0) {
				$cond .= " AND D.sub_class_id_fk=$sub_class_id_fk ";
			}
			if ($sub_zone_id_fk > 0) {
				$cond .= " AND D.sub_zone_id_fk=$sub_zone_id_fk ";
			}
			if ($quarter_id_fk > 0) {
				$cond .= " AND D.quarter_id_fk=$quarter_id_fk ";
			}
			if ($street_id_fk > 0) {
				$cond .= " AND D.street_id_fk=$street_id_fk ";
			}

		
			$ii = 0;

			foreach ( $specialty_id_fk as $doc_sp_id){

				if ($doc_sp_id > 0) {
					if($ii == 0){
						$cond .= " AND ( D.specialty_id_fk=$doc_sp_id ";

					}else{
						$cond .= " OR D.specialty_id_fk=$doc_sp_id ";

					}
					$ii++;
				}


			}

			if ($ii >0) {
				$cond 	.= " ) ";
			}
			
			if ($cond=='') {
				$cond 	= $cond1;
			}

			

			$res = $db->query('SELECT
									doctor_id,
									doctor_name,
									doctor_address,
									SP.doctor_specialty,
									doctor_phone_no1,
									doctor_phone_no2,
									doctor_location,
									doctor_email,
									doctor_note,
									class_name,
									sub_class_name,
									city_name,
									full_name,
									zone_name,
									sub_zone_name,
									quarter_name,
									street_name
									
								FROM
									doctor D
									LEFT JOIN class CL ON (D.class_id_fk = CL.class_id)
									LEFT JOIN city C ON (D.city_id_fk = C.city_id)
									LEFT JOIN sub_class S ON (D.sub_class_id_fk = S.sub_class_id)
									LEFT JOIN zone Z ON (D.zone_id_fk = Z.zone_id)
									LEFT JOIN sub_zone SZ ON (D.sub_zone_id_fk = SZ.sub_zone_id)
									LEFT JOIN quarter Q ON (D.quarter_id_fk = Q.quarter_id)
									LEFT JOIN street SR ON (D.street_id_fk = SR.street_id)
				                    LEFT JOIN staff ST ON (D.created_by = ST.staff_id)
				                    LEFT JOIN specialty SP ON (D.specialty_id_fk = SP.specialty_id)

										
									WHERE
										( D.is_deleted = 0 '.$cond.') 

								');
			if ($res) {
				
				$op  = array('doctor'=>array(),'details'=>array());
				while ($row = $res->fetch_assoc()) {
				   
					$result['doctor_id']  					= $row['doctor_id'];
					$result['doctor_name']  				= $row['doctor_name'];
					$result['doctor_address']  				= $row['doctor_address'];
					$result['doctor_specialty']  			= $row['doctor_specialty'];
					$result['doctor_phone_no1'] 			= $row['doctor_phone_no1'];
					$result['doctor_phone_no2']  			= $row['doctor_phone_no2'];
					$result['doctor_location']  			= $row['doctor_location'];
					$result['doctor_email']  				= $row['doctor_email'];
					$result['doctor_note']  				= $row['doctor_note'];
					$result['class_name']  					= $row['class_name'];
					$result['sub_class_name']  				= $row['sub_class_name'];
					$result['city_name']					= $row['city_name'];
					$result['full_name']  					= $row['full_name'];

					$result['zone_name']  					= $row['zone_name'];
					$result['sub_zone_name']  				= $row['sub_zone_name'];
					$result['quarter_name']  				= $row['quarter_name'];
					$result['street_name']  				= $row['street_name'];
					
					array_push($op['doctor'],$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'] == "excel") {

      		$city_id_fk				=addslashes($_POST["excel_city_id_fk"]);
      		$zone_id_fk				=addslashes($_POST["excel_zone_id_fk"]);
      		$class_id_fk			=addslashes($_POST["excel_class_id_fk"]);
      		$sub_class_id_fk		=addslashes($_POST["excel_sub_class_id_fk"]);
      		$sub_zone_id_fk			=addslashes($_POST["excel_sub_zone_id_fk"]);
			$quarter_id_fk			=addslashes($_POST["excel_quarter_id_fk"]);
			$street_id_fk			=addslashes($_POST["excel_street_id_fk"]);
			$specialty_id_fk		=addslashes($_POST["excel_specialty_id_fk"]);

			$first_row				=addslashes($_POST["first_row"]);
			$last_row				=addslashes($_POST["last_row"]);

			

			if ($zone_id_fk =='') {
				$zone_id_fk = 0;
			}
			if ($class_id_fk =='') {
				$class_id_fk = 0;
			}
			if ($sub_class_id_fk =='') {
				$sub_class_id_fk = 0;
			}
			if ($sub_zone_id_fk =='') {
				$sub_zone_id_fk = 0;
			}
			if ($quarter_id_fk =='') {
				$quarter_id_fk = 0;
			}
			if ($street_id_fk =='') {
				$street_id_fk = 0;
			}
			if ($specialty_id_fk =='') {
				$specialty_id_fk = 0;
			}

			$delete = 0;
			$reg_date = date("Y-m-d h:i:s", time());
			$lastaction = "Import Doctor List";


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






			if(isset($_FILES['uploadFile']['name']) && $_FILES['uploadFile']['name'] != "") {

		        $allowedExtensions = array("xls","xlsx");
		        $ext = pathinfo($_FILES['uploadFile']['name'], PATHINFO_EXTENSION);

		        $excel_file = rand(100000,10000000) .".".$ext;
				
				$excel_file 		= basename($excel_file);


		        if(in_array($ext, $allowedExtensions)) {
		           $file_size = $_FILES['uploadFile']['size'] / 1024;

		           if($file_size < 500) {

		               $file = "uploads/".$excel_file;
		               $isUploaded = move_uploaded_file($_FILES['uploadFile']['tmp_name'], $file);
		               if($isUploaded) {
		                    
							require_once dirname(__FILE__) . '/phpexcel-1.8/Classes/PHPExcel/IOFactory.php';

		                    $objPHPExcel = PHPExcel_IOFactory::load($file);


							$objPHPExcel->setActiveSheetIndex(0);
		                    
		                    $sheet = $objPHPExcel->getActiveSheet();
		                   

		                    $total_rows = $sheet->getHighestRow();
		                    //It returns the highest number of columns

		                    $total_rows =  $total_rows -1;
		                    $start_row 	= 2;	

		                    
		                    if ($last_row >0) {
		                    	$total_rows = $last_row;	
		                    }

		                    if ($first_row >0) {
		                    	$start_row = $first_row;	
		                    }

		                    $total_columns = "F";
		                    
		                    


		                    $unq 						= uniqid();
							
							$barcode 					= $unq;
		                  	
		                    //Creating a dynamic query based on the rows from the excel file

		                  	$query = "insert into `doctor` (`doctor_name`, 
		                  									`class_id_fk`,
															`sub_class_id_fk`,
		                  									`specialty_id_fk`, 
															`street_id_fk`,
															`quarter_id_fk`,
															`sub_zone_id_fk`,
															`zone_id_fk`,
															`city_id_fk`, 
		                  									`created_by`, 
		                  									`last_action`, 
		                  									`last_action_by`) VALUES ";
		                   
		                    //Loop through each row of the worksheet
		                    for($row =$start_row; $row <= $total_rows; $row++) {
		                        //Read a single row of data and store it as a array.
		                        //This line of code selects range of the cells like A1:D1
		                        $single_row = $sheet->rangeToArray('A' . $row . ':' . $total_columns . $row, NULL, TRUE, FALSE);

		                        $final_salary[] 	= $single_row[0][0];
		                        $doctors[]     		= $single_row[0][1];
		                        $pharmacies[]     	= $single_row[0][2];
		                        $class[]     		= $single_row[0][3];
		                        $sub_class[]     	= $single_row[0][4];
		                        $specialty[]     	= $single_row[0][5];

		                       
		                        // $query .= "(";

		                        $query .= "('".mysqli_real_escape_string($db, $single_row[0][1])."',";
		                        $query .= "'".mysqli_real_escape_string($db, $single_row[0][3])."',";
		                        $query .= "'".mysqli_real_escape_string($db, $single_row[0][4])."',";
		                        $query .= "'".mysqli_real_escape_string($db, $single_row[0][5])."',";

		                        // $query = substr($query, 0, -1);
		                        $query .= " 
		                        			'$street_id_fk', 
		                        			'$quarter_id_fk', 
		                        			'$sub_zone_id_fk', 
		                        			'$zone_id_fk', 
		                        			'$city_id_fk', 
		                        			'".$_SESSION['user_id']."', 
		                        			'$lastaction', 
		                        			'".$_SESSION['user_id']."'),";

		                       

		                    }

		                    $query = substr($query, 0, -1);


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

							if($res) {  

							  	// now we should insert into pharmacy table the name of pharamacy

							  	$last_inserted_id_doc = $db->insert_id;


								$reg_date 	= date("Y-m-d h:i:s", time());
								$lastaction = 'Add Pharmacy Excell';
								$safe_note 	= "Add New Salary  ID : " . $last_inserted_id_doc;

								$sql1 ="INSERT INTO pharmacy (
																pharmacy_name,
																							
																street_id_fk,
																quarter_id_fk,
																sub_zone_id_fk,
																zone_id_fk,
																city_id_fk,
																created_by,
																last_action,
																last_action_by,
																created_at,
																updated_at)
												VALUES ";

									


									for ($pp=0; $pp < count($pharmacies); $pp++) { 

										$sql1 .= "(";

										$sql1 .=   "'$pharmacies[$pp]',
													'$street_id_fk', 
				                        			'$quarter_id_fk', 
				                        			'$sub_zone_id_fk', 
				                        			'$zone_id_fk', 
				                        			'$city_id_fk',
													'".$_SESSION['user_id']."', 
		                        					'$lastaction', 
		                        					'".$_SESSION['user_id']."',
													'$reg_date',
													'$reg_date'),";
									}

									
			                    	$sql1 = substr($sql1, 0, -1);

									// echo $sql1;

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

									if ($res1) {

										// now we should insert into pharmacy_doctor table

										$last_inserted_id_phy = $db->insert_id;

										$xx = 0;

										for ($pp=0; $pp < count($doctors); $pp++) { 

											$last_inserted_id_doc 	= $last_inserted_id_doc + $xx;
											$last_inserted_id_phy 	= $last_inserted_id_phy + $xx;

											$xx = 1;

											$sql2 ="INSERT INTO pharmacy_doctor (	
																		doctor_id_fk, 
																		pharmacy_id_fk,
																		
																		
																		created_by,
																		created_at,
																		updated_at,
																		last_action,
																		last_action_by)

					 								 			VALUES( '$last_inserted_id_doc',
						 								 				
						 								 				'$last_inserted_id_phy',
						 								 				'".$_SESSION['user_id']."',
						 								 				'$reg_date',
						 								 				'$reg_date',
						 								 				'$lastaction',
						 								 				'".$_SESSION['user_id']."')";
										
											$res2 = $db->query($sql2);

										}

										$db->commit();
										$op['is_success'] = true;
										$op['data'] 	  = "تمت عملية الاضافة بنجاح";

										echo json_encode($op);
										exit;	

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

									}


		                    		
									

		                    } else {

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


		                     
		                    // Finally we will remove the file from the uploads folder (optional) 
		                    unlink($file);
		                } else {
		                    echo '<span class="msg">File not uploaded!</span>';
		                }
		            } else {
		                echo '<span class="msg">Maximum file size should not cross 500 KB on size!</span>';  
		            }
		        } else {
		            echo '<span class="msg">This type of file not allowed!</span>';
		        }
			} else {
			        echo '<span class="msg">Select an excel file first!</span>';
			}


		
			

		}

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

			$city_id_fk				=addslashes($_POST["delete_city_id_fk"]);
      		$zone_id_fk				=addslashes($_POST["delete_zone_id_fk"]);
      		$class_id_fk			=addslashes($_POST["delete_class_id_fk"]);
      		$sub_class_id_fk		=addslashes($_POST["delete_sub_class_id_fk"]);
      		$sub_zone_id_fk			=addslashes($_POST["delete_sub_zone_id_fk"]);
			$quarter_id_fk			=addslashes($_POST["delete_quarter_id_fk"]);
			$street_id_fk			=addslashes($_POST["delete_street_id_fk"]);
			$specialty_id_fk		=addslashes($_POST["delete_specialty_id_fk"]);

			$cond1 	= " is_deleted=1 ";
			$cond 	= " is_deleted=0 ";

			if($_SESSION['profile_type'] != 5 ){
				$Validator['is_success'] = false;
				$Validator['data'] = "You Are Not Allowed To perform this Action";
				echo json_encode($Validator);
				exit;
			}
			

			if ($city_id_fk > 0) {
				$cond .= " AND city_id_fk=$city_id_fk ";
			}
			if ($zone_id_fk > 0) {
				$cond .= " AND zone_id_fk=$zone_id_fk ";
			}
			if ($class_id_fk > 0) {
				$cond .= " AND class_id_fk=$class_id_fk ";
			}
			if ($sub_class_id_fk > 0) {
				$cond .= " AND sub_class_id_fk=$sub_class_id_fk ";
			}
			if ($sub_zone_id_fk > 0) {
				$cond .= " AND sub_zone_id_fk=$sub_zone_id_fk ";
			}
			if ($quarter_id_fk > 0) {
				$cond .= " AND quarter_id_fk=$quarter_id_fk ";
			}
			if ($street_id_fk > 0) {
				$cond .= " AND street_id_fk=$street_id_fk ";
			}
			if ($specialty_id_fk > 0) {
				$cond .= " AND specialty_id_fk=$specialty_id_fk ";
			}
			
			if ($cond=='') {
				$cond 	= $cond1;
			}


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

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


	  		$res = $db->query("UPDATE doctor SET 	is_deleted = 1 ,
	  												deleted_at = '$reg_date',
	  												last_action='$lastaction',
	  												last_action_by='".$_SESSION['user_id']."' 
	  							WHERE $cond");
	    	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['edit_objid'];
			$class_id_fk				=addslashes($_POST["edit_class_id_fk"]);
			$sub_class_id_fk			=addslashes($_POST["edit_sub_class_id_fk"]);
			$city_id_fk					=addslashes($_POST["edit_city_id_fk"]);
			$zone_id_fk					=addslashes($_POST["edit_zone_id_fk"]);
			$sub_zone_id_fk				=addslashes($_POST["edit_sub_zone_id_fk"]);
			$quarter_id_fk				=addslashes($_POST["edit_quarter_id_fk"]);
			$street_id_fk				=addslashes($_POST["edit_street_id_fk"]);
			
			$doctor_name				=addslashes($_POST["edit_doctor_name"]);
			$doctor_address				=addslashes($_POST["edit_doctor_address"]);
			$specialty_id_fk			=addslashes($_POST["edit_specialty_id_fk"]);			
			$doctor_phone_no1			=addslashes($_POST["edit_doctor_phone_no1"]);
			$doctor_phone_no2			=addslashes($_POST["edit_doctor_phone_no2"]);
			$doctor_email				=addslashes($_POST["edit_doctor_email"]);
			$doctor_note				=addslashes($_POST["edit_doctor_note"]);
			

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

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

			$res = $db->query("SELECT * FROM doctor WHERE is_deleted=0 AND doctor_name = '$doctor_name' AND city_id_fk = $city_id_fk AND zone_id_fk=$zone_id_fk AND doctor_id != '$id'");
			if (mysqli_num_rows($res)>0) {
					$op['is_success'] = false;
					$op['data'] 	  = "خطأ اسم  مكررة";
					echo json_encode($op);
					exit;
			}

							


			$lastaction = "Update Doctor";
			$update_qry = "UPDATE doctor SET  
									doctor_name 		= '$doctor_name',
									doctor_address 		='$doctor_address',
									specialty_id_fk 	='$specialty_id_fk',
									doctor_phone_no1 	='$doctor_phone_no1',
									doctor_phone_no2 	='$doctor_phone_no2',
									doctor_email 		='$doctor_email',
									doctor_note 		='$doctor_note',
									
									class_id_fk 		='$class_id_fk',
									sub_class_id_fk 	='$sub_class_id_fk',
									street_id_fk 		='$street_id_fk',
									quarter_id_fk 		='$quarter_id_fk',
									sub_zone_id_fk 		='$sub_zone_id_fk',
									zone_id_fk 			='$zone_id_fk',
									city_id_fk 			='$city_id_fk',
									updated_at			='$reg_date',
									last_action			='$lastaction',
									last_action_by		='".$_SESSION['user_id']."' 
							WHERE   doctor_id = $id";


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

				// $target_dir = '../product_img/'.$id;
				// if(! file_exists($target_dir)){
				// 	mkdir($target_dir);
				// }

				// $image_path		='';
			    //             $manual_path 	='';
			    //             $State 			=true;
			    //             $State1			=true;
			    //             $error 			= '';			
			    //             $error1			= '';
			    //             $msgg			= "تمت عملية التحديث بنجاح";

				
				// if($_FILES['edit_image_path']['size'] > 0 ){
					
				// 	$ext11  = pathinfo(basename($_FILES['edit_image_path']['name']),PATHINFO_EXTENSION);
					
				// 	$image_file = $id ."_". rand(100000,10000000) ."_.".$ext11;
					
				// 	$target_file 		= $target_dir .'/'. basename($image_file);
					
				// 	$uploadOk = 1;
				// 	$imageFileType  = pathinfo($target_file,PATHINFO_EXTENSION);
										
				// 	$State1=true;
				// 	// Check file size
				// 	if ($_FILES["edit_image_path"]["size"] > 4000000) {
				// 			$error1= " Sorry, your Image is too large. ";
				// 			$State1=false;
				// 	}

					
				// 	// Allow certain file formats
				// 	if(strtolower($imageFileType) != "jpg" && strtolower($imageFileType) != "png" && strtolower($imageFileType) != "jpeg" && strtolower($imageFileType) != "gif" && strtolower($imageFileType) != "bmp" && strtolower($imageFileType) != "pdf" ) {
				// 		$error1= " Sorry, only JPG, JPEG, PNG & GIF & PDF files are allowed. ";
				// 		$State1=false;
				// 	}

				// 	// Check if $uploadOk is set to 0 by an error
				// 	if ($State1 == true) {
					
				// 		if (move_uploaded_file($_FILES["edit_image_path"]["tmp_name"], $target_file)){

				// 			$image_path  = 'product_img/'.$id .'/'.$image_file;
				// 			$State1=true;											  

				// 		} else {
				// 			$error1= " Sorry, there was an error uploading your file. ";
				// 			$State1=false;
				// 		}
				// 	}
				// }		
                
    			//             $tmp 			='';
		  		//       $update_file 	= '';
		        
				  //       if($image_path !=''){
				  //       	$update_file = "image_path = '$image_path'";

				  //       }

		       
				  //       // echo $update_file;
				  //       if($update_file !=''){
				  //       	$sql ="UPDATE  product SET $update_file WHERE product_id ='$id'";
									
						// 	$query = $db->query($sql);
				  //       }
		        
					



					$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 Doctor";

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

			$res = $db->query("SELECT * FROM plan_doctor WHERE is_deleted=0 AND doctor_id_fk = '$id'");
			if (@mysqli_num_rows($res)>0) {
				$op['is_success'] = false;
				$op['data'] 	  = "لا يمكن حذف هذا الدكتور فقد تم اضافته ضمن احدى الخطط";
				echo json_encode($op);
				exit;
			}

			$res = $db->query("SELECT * FROM report WHERE is_deleted=0 AND doctor_id_fk = '$id'");
			if (mysqli_num_rows($res)>0) {
				$op['is_success'] = false;
				$op['data'] 	  = "لا يمكن حذف هذا الدكتور فقد تم اضافته في احدى التقارير";
				echo json_encode($op);
				exit;
			}

			

	  		$res = $db->query("UPDATE doctor SET is_deleted = 1 ,deleted_at = '$reg_date',last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE doctor_id=$id");
	    	if ($res){
	        		$op['is_success'] = true;
					$op['data'] 	  = "تمت عملية الحذف بنجاح";
					echo json_encode($op);
					exit;
	     	}else{
	      			$op['is_success'] = false;
					$op['data'] 	  = "حصل خطأ: الرجاء اعادة تحميل الصفحة والمحاولة من جديد";
					echo json_encode($op);
					exit;
	     	}
		}

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

      		$city_id_fk				=addslashes($_POST["city_id_fk"]);
      		$zone_id_fk				=addslashes($_POST["zone_id_fk"]);
      		$class_id_fk			=addslashes($_POST["class_id_fk"]);
      		$sub_class_id_fk		=addslashes($_POST["sub_class_id_fk"]);

      		$sub_zone_id_fk			=addslashes($_POST["sub_zone_id_fk"]);
			$quarter_id_fk			=addslashes($_POST["quarter_id_fk"]);
			$street_id_fk			=addslashes($_POST["street_id_fk"]);
			
			
			$doctor_name			=addslashes($_POST["doctor_name"]);
			$doctor_address			=addslashes($_POST["doctor_address"]);
			$specialty_id_fk		=addslashes($_POST["specialty_id_fk"]);
			$doctor_phone_no1		=addslashes($_POST["doctor_phone_no1"]);
			$doctor_phone_no2		=addslashes($_POST["doctor_phone_no2"]);
			$doctor_email			=addslashes($_POST["doctor_email"]);			
			$doctor_note			=addslashes($_POST["doctor_note"]);			
			


			$res = $db->query("SELECT * FROM doctor WHERE is_deleted=0 AND doctor_name = '$doctor_name' AND city_id_fk = $city_id_fk AND zone_id_fk=$zone_id_fk");
			if (mysqli_num_rows($res)>0) {
					$op['is_success'] = false;
					$op['data'] 	  = "خطأ اسم  مكررة";
					echo json_encode($op);
					exit;
			}

			


			

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

			$sql ="INSERT INTO doctor (
										doctor_name,
										doctor_address,
										specialty_id_fk,
										doctor_phone_no1,
										doctor_phone_no2,
										doctor_email,
										doctor_note,
										class_id_fk,
										sub_class_id_fk,
										street_id_fk,
										quarter_id_fk,
										sub_zone_id_fk,
										zone_id_fk,
										city_id_fk,
										created_by,
										last_action,
										last_action_by,
										created_at,
										updated_at)
								VALUES( '$doctor_name',
										
										'$doctor_address',
										'$specialty_id_fk',
										'$doctor_phone_no1',
										'$doctor_phone_no2',
										'$doctor_email',
										'$doctor_note',
										'$class_id_fk',
										'$sub_class_id_fk',
										'$street_id_fk',
										'$quarter_id_fk',
										'$sub_zone_id_fk',
										'$zone_id_fk',
										'$city_id_fk',
										'".$_SESSION['user_id']."',
										'$lastaction',
										'".$_SESSION['user_id']."',
										'$reg_date',
										'$reg_date')";

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

				if ($res) {

					
					



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

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