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



date_default_timezone_set($_SESSION['SYSTimezone']);

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

		if( isset($_GET['type']) && ($_GET['type'] == "load")){
			
			// show All products for specific category
			$cond = isset($_GET['id'])? ' AND E.expenses_group_id_fk='.$_GET['id']:'';
			$table  =  '(
				SELECT
					quarter_id,
					quarter_name,
					Q.city_id_fk,
					Q.zone_id_fk,
					Q.created_at,
					
					city_name,
					zone_name,
					sub_zone_name,
					
					(SELECT COUNT(doctor_id)  FROM doctor D WHERE D.is_deleted=0 AND D.quarter_id_fk = Q.quarter_id) AS doctor_count,


					(SELECT COUNT(pharmacy_id)  FROM pharmacy P WHERE P.is_deleted=0 AND P.quarter_id_fk = Q.quarter_id) AS pharm_count,

					
					(SELECT COUNT(staff_id)  FROM staff S WHERE S.is_deleted=0 AND find_in_set(  Q.quarter_id, S.quarter_id_fk) > 0) AS saleman_count

				FROM
					quarter Q

					LEFT JOIN city CC ON (CC.city_id = Q.city_id_fk)
					LEFT JOIN zone Z ON (Z.zone_id = Q.zone_id_fk)
					LEFT JOIN sub_zone SZ ON (SZ.sub_zone_id = Q.sub_zone_id_fk)
					
					
				WHERE
					( Q.is_deleted = 0 ) 
			) AS ALLTBL';
			
			$primaryKey = 'quarter_id';
			$columns =  array(
				  			  	array( 'db' => 'quarter_id', 'dt' => 0 ),
			    				array( 'db' => 'quarter_name', 'dt' => 1 ),
			    				array( 'db' => 'doctor_count',  'dt' => 2 ),
			    				array( 'db' => 'pharm_count',  'dt' => 3 ),
			    				array( 'db' => 'saleman_count',  'dt' => 4 ),
			    				array( 'db' => 'sub_zone_name',  'dt' => 5 ),
			    				array( 'db' => 'zone_name',  'dt' => 6 ),
			    				array( 'db' => 'city_name',  'dt' => 7 ),
			    				
								array( 'db' => 'city_id_fk',   'dt' => 8 ),
								array( 'db' => 'zone_id_fk',   'dt' => 9 )
			);
			// 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") {

			
			

			$res = $db->query("SELECT * FROM quarter WHERE is_deleted = 0 ");
			if ($res) {
				$op  = array();
				while ($row = $res->fetch_assoc()) {
				    // do what you need.
						$result[0] = $row['quarter_id'];
						$result[1] = $row['quarter_name'];
						$result[2] = $row['zone_id_fk'];
						$result[3] = $row['city_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 sub_zone_id_fk='.$id;
			}

			$res = $db->query("SELECT * FROM quarter WHERE is_deleted = 0 $cond");
			if ($res) {
				$op  = array();
				while ($row = $res->fetch_assoc()) {
				    // do what you need.
						$result[0] = $row['quarter_id'];
						$result[1] = $row['quarter_name'];
						$result[2] = $row['zone_id_fk'];
						$result[3] = $row['city_id_fk'];
						array_push($op,$result);
				}
				echo json_encode($op);
				exit;
			}else{
				echo 0;
				exit;
			}
		}

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

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

			if (isset($_POST['id'])) {
				$cond = ' AND ( ';
				$orr = ' ';
				foreach ($ids as $sz_id){
					if ($sz_id !='') {
						$cond =   $cond . $orr . ' sub_zone_id_fk='.$sz_id;
						$orr = ' OR ';
					}
				}
				$cond = $cond . ' ) ';
			}

			

			$res = $db->query("SELECT * FROM quarter WHERE is_deleted = 0 $cond");
			if ($res) {
				$op  = array();
				while ($row = $res->fetch_assoc()) {
				    // do what you need.
						$result[0] = $row['quarter_id'];
						$result[1] = $row['quarter_name'];
						$result[2] = $row['zone_id_fk'];
						$result[3] = $row['city_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
									quarter_id,
									
									quarter_name,
									sub_zone_id_fk,
									zone_id_fk,
									city_id_fk
									

									FROM
										quarter Q
										
									WHERE
										( Q.is_deleted = 0 AND Q.quarter_id = '.$id.') 

								');
			if ($res) {
				
				
				while ($row = $res->fetch_assoc()) {
				   
					$result['quarter_id']  			= $row['quarter_id'];
					$result['quarter_name']  		= $row['quarter_name'];
					$result['sub_zone_id_fk']  		= $row['sub_zone_id_fk'];
					$result['zone_id_fk']  			= $row['zone_id_fk'];
					$result['city_id_fk']  			= $row['city_id_fk'];
						
						
						
					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'] == "update") {

			$id 				= $_POST['obj_id'];
			$name 				=addslashes($_POST["name"]);
			$sub_zone_id_fk 	=addslashes($_POST["sub_zone_id_fk"]);
			$zone_id_fk 		=addslashes($_POST["zone_id_fk"]);
			$city_id_fk 		=addslashes($_POST["city_id_fk"]);

			

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

			$res = $db->query("SELECT * FROM quarter WHERE is_deleted=0 AND quarter_name = '$name' AND zone_id_fk='$zone_id_fk' AND sub_zone_id_fk='$sub_zone_id_fk' AND quarter_id != '$id'");

			if (mysqli_num_rows($res)>0) {
				$op['is_success'] = false;
				$op['data'] 	  = "اسم  مكرر";
				echo json_encode($op);
				exit;
			}

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


			$lastaction = "Update Quarter";
			$update_qry = "UPDATE quarter SET 	quarter_name = '$name',
												sub_zone_id_fk='$sub_zone_id_fk',
												zone_id_fk='$zone_id_fk',
												city_id_fk='$city_id_fk',
												last_action='$lastaction',
												updated_at='$reg_date',
												last_action_by='".$_SESSION['user_id']."' 
										  WHERE quarter_id = $id";


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



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


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

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


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

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

			

			


	      $res = $db->query("UPDATE quarter SET is_deleted = 1 ,
	      										 deleted_at = '$reg_date',
	      										 last_action='$lastaction',
	      										 updated_at='$reg_date',
	      										 last_action_by='".$_SESSION['user_id']."' 
	      								   WHERE quarter_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") {

            $name 			=addslashes($_POST["name"]);
            $sub_zone_id_fk =addslashes($_POST["sub_zone_id_fk"]);
            $zone_id_fk 	=addslashes($_POST["zone_id_fk"]);
            $city_id_fk 	=addslashes($_POST["city_id_fk"]);

			$res = $db->query("SELECT * FROM quarter WHERE quarter_name = '$name' AND zone_id_fk='$zone_id_fk' AND sub_zone_id_fk='$sub_zone_id_fk' AND is_deleted=0");
			
			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 = "Add New Quarter";

			$sql ="INSERT INTO quarter (	quarter_name,
										 	sub_zone_id_fk,
										 	zone_id_fk,
										 	city_id_fk,
											created_by,
											created_at,
										    updated_at,
											last_action,
											last_action_by)
									VALUES( '$name',
											'$sub_zone_id_fk',
											'$zone_id_fk',
											'$city_id_fk',
											'".$_SESSION['user_id']."',
											'$reg_date',
											'$reg_date',
											'$lastaction',
											'".$_SESSION['user_id']."')";
			$res = $db->query($sql);
			if ($res) {

				

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

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

