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

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

		if( isset($_GET['type']) && ($_GET['type'] == "load")){
			
			$cond = isset($_GET['city_id'])? ' AND PL.city_id_fk='.$_GET['city_id']:'';
				
			$limits = "";
			


			$table  =  '(
				(
					SELECT
						plan_id,
						plan_number,
						plan_title,
						plan_description,
						plan_start_date,
						plan_end_date,
						plan_status,
						plan_note,
						plan_sequence_no,
						
						plan_author,
						plan_transaction,
						plan_barcode,
						PL.city_id_fk,
						PL.zone_id_fk,
						
						
						full_name,
						city_name,
						zone_name

					 FROM  plan PL
					 		LEFT JOIN city C ON (PL.city_id_fk = C.city_id)
							LEFT JOIN zone Z ON (PL.zone_id_fk = Z.zone_id)
		                    
		                    LEFT JOIN staff ST ON (PL.created_by = ST.staff_id)
					 
				WHERE
					(PL.is_deleted = 0   '.$cond.' ) '.$limits.'
				)
			) AS ALLTBL';
			// Table's primary key
			//echo $table;
			$primaryKey = 'plan_id';
			$columns = array(
		    	array( 'db' => 'plan_id', 'dt' => 0 ),
		    	array( 'db' => 'plan_number', 'dt' => 1 ),
		    	array( 'db' => 'plan_title', 'dt' => 2 ),
		    	array( 'db' => 'plan_description', 'dt' => 3 ),
		    	array( 'db' => 'plan_start_date', 'dt' => 4, 'formatter'=> function($d){
							return date('Y-m-d',strtotime($d));
					} ),
		    	array( 'db' => 'plan_end_date', 'dt' => 5, 'formatter'=> function($d){
							return date('Y-m-d',strtotime($d));
					} ),
		    	array( 'db' => 'plan_note', 'dt' => 6 ),
		    	array( 'db' => 'zone_name', 'dt' => 7 ),
			  	array( 'db' => 'city_name', 'dt' => 8 ),
				array( 'db' => 'plan_status', 'dt' => 9 ),
				array( 'db' => 'full_name', 'dt' => 10 ),
				
			);
			// 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 
										plan_id, 
										plan_number, 
										plan_title 
										
										

									FROM plan P
										
		
								WHERE  P.is_deleted = 0 AND P.plan_end_date >= CURDATE() ");

			
			if ($res ) {
				

				$op  = array('plan'=>array(),'product'=>array(),'saleman'=>array());
				while ($row = $res->fetch_assoc()) {

					
				   
					$result['plan_id']  			= $row['plan_id'];
					$result['plan_number']  		= $row['plan_number'];
					
					$result['plan_title']  			= $row['plan_title'];
					
					
					array_push($op['plan'],$result);
			
				}

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


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

			$id = $_POST['id'];

			$res = $db->query("SELECT 	doctor_id,
										doctor_name,
										doctor_specialty ,
										number_of_visit,
										sub_class_id_fk,
										plan_doctor_notes,
										class_name,
										zone_id_fk 
								
								FROM plan_doctor PD

								LEFT JOIN doctor D ON (PD.doctor_id_fk = D.doctor_id) 
								LEFT JOIN class C ON (D.class_id_fk = C.class_id) 

								WHERE PD.is_deleted = 0  AND PD.plan_id_fk=$id");
			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_name']  		= $row['class_name'];
					$result['number_of_visit']  = $row['number_of_visit'];
					$result['plan_doctor_notes']= $row['plan_doctor_notes'];
					array_push($op,$result);
				}
				echo json_encode($op);
				exit;
			}else{
				echo 0;
				exit;
			}
		}


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

			$id = $_POST['id'];

			$res = $db->query("SELECT 	product_id,
										product_name,
										quantity,
										plan_sell_price,
										plan_product_offer,
										plan_product_notes
										
								
								FROM plan_product PP

								LEFT JOIN product P ON (PP.product_id_fk = P.product_id) 

								WHERE PP.is_deleted = 0  AND PP.plan_id_fk=$id");
			if ($res) {
				//$row = $res->fetch_all();
				$op  = array();
				while ($row = $res->fetch_assoc()) {
				   
					$result['product_id']  			= $row['product_id'];
					$result['product_name']  		= $row['product_name'];
					$result['quantity'] 			= $row['quantity'];
					$result['plan_sell_price']  	= $row['plan_sell_price'];
					$result['plan_product_offer']  	= $row['plan_product_offer'];
					$result['plan_product_notes']	= $row['plan_product_notes'];
					array_push($op,$result);
				}
				echo json_encode($op);
				exit;
			}else{
				echo 0;
				exit;
			}
		}

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

			$id = $_POST['id'];

			$res = $db->query("SELECT 	pharmacy_id,
										pharmacy_name,
										pharmacy_address
										
								
								FROM pharmacy_doctor PD

								LEFT JOIN pharmacy P ON (PD.pharmacy_id_fk = P.pharmacy_id) 

								WHERE PD.is_deleted = 0  AND PD.doctor_id_fk=$id");
			if ($res) {
				//$row = $res->fetch_all();
				$op  = array();
				while ($row = $res->fetch_assoc()) {
				   
					$result['pharmacy_id']  		= $row['pharmacy_id'];
					$result['pharmacy_name']  		= $row['pharmacy_name'];
					$result['pharmacy_address'] 	= $row['pharmacy_address'];
					
					array_push($op,$result);
				}
				echo json_encode($op);
				exit;
			}else{
				echo 0;
				exit;
			}
		}

	

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

			$id = $_POST['id'];


			$res = $db->query("SELECT 
										plan_id, 
										plan_number, 
										plan_title, 
										plan_description, 
										plan_start_date, 
										plan_end_date,
										plan_status, 
										plan_note, 
										plan_author,
										plan_transaction, 
										plan_barcode,

										city_name,
										zone_name,
										doctor_name,
										doctor_address,
										number_of_visit,
										doctor_specialty,
										plan_doctor_notes

									FROM plan P
										LEFT JOIN city C ON (P.city_id_fk = C.city_id)
										LEFT JOIN zone Z ON (P.zone_id_fk = Z.zone_id)
                    					LEFT JOIN plan_doctor PD ON (PD.plan_id_fk = P.plan_id)
                    					LEFT JOIN doctor D ON (D.doctor_id = PD.doctor_id_fk)

										WHERE P.plan_id='$id'  AND P.is_deleted = 0 ");

			$res2 = $db->query("SELECT 
										plan_id,
										quantity,
										plan_sell_price,
										plan_date,
										plan_product_offer,
										plan_product_notes,
										product_name,
										product_code,
										badge_no

									FROM plan P
										
                    					LEFT JOIN plan_product PP ON (PP.plan_id_fk = P.plan_id)
                    					LEFT JOIN product Pr ON (Pr.product_id = PP.product_id_fk)

										WHERE P.plan_id='$id'  AND P.is_deleted = 0 ");

			$res3 = $db->query("SELECT 
										plan_id, 
										full_name 

								FROM plan P

                    					LEFT JOIN plan_team_leader PT ON (PT.plan_id_fk = P.plan_id)
                    					LEFT JOIN staff ST ON (PT.team_leader_id_fk = ST.staff_id)

								WHERE P.plan_id='$id'  AND P.is_deleted = 0 ");

			if ($res && $res2 && $res3) {
				

				$op  = array('plan'=>array(),'product'=>array(),'saleman'=>array());
				while ($row = $res->fetch_assoc()) {

					
				   
					$result['plan_id']  			= $row['plan_id'];
					$result['plan_number']  		= $row['plan_number'];
					$result['plan_start_date']  	= date('Y-m-d',strtotime($row['plan_start_date']));
					$result['plan_end_date']  		= date('Y-m-d',strtotime($row['plan_end_date']));
					$result['plan_title']  			= $row['plan_title'];
					$result['plan_description']  	= $row['plan_description'];
					$result['plan_status']  		= $row['plan_status'];
					$result['plan_note'] 			= $row['plan_note'];
					$result['plan_author']  		= $row['plan_author'];
					$result['plan_transaction']  	= $row['plan_transaction'];
					$result['plan_barcode']  		= $row['plan_barcode'];
					$result['city_name']  			= $row['city_name'];
					$result['zone_name']  			= $row['zone_name'];
					$result['doctor_name']  		= $row['doctor_name'];
					$result['doctor_address']  		= $row['doctor_address'];
					$result['doctor_specialty']  	= $row['doctor_specialty'];
					$result['number_of_visit']  	= $row['number_of_visit'];
					$result['plan_doctor_notes']  	= $row['plan_doctor_notes'];
					
					array_push($op['plan'],$result);
			
				}

				while ($row = $res2->fetch_assoc()) {	
				   
					$result2['plan_id']  					= $row['plan_id'];
					$result2['quantity']  					= $row['quantity'];
					$result2['plan_sell_price']  			= $row['plan_sell_price'];
					$result2['plan_date']  					= $row['plan_date'];
					$result2['plan_product_offer']  		= $row['plan_product_offer'];
					$result2['plan_product_notes']  		= $row['plan_product_notes'];
					$result2['product_name']  				= $row['product_name'];
					$result2['product_code']  				= $row['product_code'];
					$result2['badge_no']  					= $row['badge_no'];
					
					array_push($op['product'],$result2);
			
				}

				while ($row = $res3->fetch_assoc()) {

					$result3['plan_id']  					= $row['plan_id'];
					$result3['full_name']  					= $row['full_name'];
					
					array_push($op['saleman'],$result3);
			
				}		
				echo json_encode($op);
				exit;
			}else{
				echo $db->error;
				exit;
			}
		}


	




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

				
			$res = $db->query("SELECT 	invoice_id,
										invoice_number,
										invoice_date,
										customer_full_name,							  
									  	customer_company
										FROM invoice  I
										LEFT JOIN customer C ON (I.customer_id_fk = C.customer_id)
										WHERE  I.is_deleted = 0");
								
			if ($res) {
				//$row = $res->fetch_all();
				$op  = array('is_success'=>false, 'data' => array());
				while ($row = $res->fetch_assoc()) {
				   
						$result['invoice_id']  			= $row['invoice_id'];
						$result['invoice_number']  		= $row['invoice_number'];
						$result['invoice_date']  		= date('Y-m-d',strtotime($row['invoice_date'])) ;
						$result['customer_full_name']  	= $row['customer_full_name'];
						$result['customer_company']  	= $row['customer_company'];
						
						array_push($op['data'],$result);
				}

			}else{
				echo 0;
				exit;
			}

			

			$op['is_success'] = true;

			echo json_encode($op);

			exit();
					
		}


		


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

			$id = $_POST['id'];


			$res = $db->query("SELECT 
										plan_id, 
										plan_number, 
										plan_title, 
										plan_description, 
										plan_start_date, 
										plan_end_date,
										plan_status, 
										plan_note, 
										plan_author,
										plan_transaction, 
										plan_barcode,

										P.city_id_fk,
										P.zone_id_fk,
										PD.doctor_id_fk,
										PD.plan_doctor_id,
										doctor_address,
										number_of_visit,
										doctor_specialty,
										plan_doctor_notes

									FROM plan P
										LEFT JOIN city C ON (P.city_id_fk = C.city_id)
										LEFT JOIN zone Z ON (P.zone_id_fk = Z.zone_id)
                    					LEFT JOIN plan_doctor PD ON (PD.plan_id_fk = P.plan_id)
                    					LEFT JOIN doctor D ON (D.doctor_id = PD.doctor_id_fk)

										WHERE P.plan_id='$id'  AND P.is_deleted = 0 ");

			$res2 = $db->query("SELECT 
										plan_id,
										quantity,
										plan_sell_price,
										plan_date,
										plan_product_offer,
										plan_product_notes,
										product_id_fk,
										product_code,
										badge_no

									FROM plan P
										
                    					LEFT JOIN plan_product PP ON (PP.plan_id_fk = P.plan_id)
                    					LEFT JOIN product Pr ON (Pr.product_id = PP.product_id_fk)

										WHERE P.plan_id='$id'  AND P.is_deleted = 0 ");

			$res3 = $db->query("SELECT 
										plan_id, 
										PT.team_leader_id_fk 

								FROM plan P

                    					LEFT JOIN plan_team_leader PT ON (PT.plan_id_fk = P.plan_id)
                    					LEFT JOIN staff ST ON (PT.team_leader_id_fk = ST.staff_id)

								WHERE P.plan_id='$id'  AND P.is_deleted = 0 ");

			
			if ($res && $res2 && $res3) {
				

				$op  = array('plan'=>array(),'product'=>array(),'saleman'=>array());
				while ($row = $res->fetch_assoc()) {

					
				   
					$result['plan_id']  			= $row['plan_id'];
					$result['plan_number']  		= $row['plan_number'];
					$result['plan_start_date']  	= date('Y-m-d',strtotime($row['plan_start_date']));
					$result['plan_end_date']  		= date('Y-m-d',strtotime($row['plan_end_date']));
					$result['plan_title']  			= $row['plan_title'];
					$result['plan_description']  	= $row['plan_description'];
					$result['plan_status']  		= $row['plan_status'];
					$result['plan_note'] 			= $row['plan_note'];
					$result['plan_author']  		= $row['plan_author'];
					$result['plan_transaction']  	= $row['plan_transaction'];
					$result['plan_barcode']  		= $row['plan_barcode'];
					$result['city_id_fk']  			= $row['city_id_fk'];
					$result['zone_id_fk']  			= $row['zone_id_fk'];
					$result['doctor_id_fk']  		= $row['doctor_id_fk'];
					$result['doctor_address']  		= $row['doctor_address'];
					$result['doctor_specialty']  	= $row['doctor_specialty'];
					$result['number_of_visit']  	= $row['number_of_visit'];
					$result['plan_doctor_notes']  	= $row['plan_doctor_notes'];
					
					array_push($op['plan'],$result);
			
				}

				while ($row = $res2->fetch_assoc()) {	
				   
					$result2['plan_id']  					= $row['plan_id'];
					$result2['quantity']  					= $row['quantity'];
					$result2['plan_sell_price']  			= $row['plan_sell_price'];
					$result2['plan_date']  					= $row['plan_date'];
					$result2['plan_product_offer']  		= $row['plan_product_offer'];
					$result2['plan_product_notes']  		= $row['plan_product_notes'];
					$result2['product_id_fk']  				= $row['product_id_fk'];
					$result2['product_code']  				= $row['product_code'];
					$result2['badge_no']  					= $row['badge_no'];
					
					array_push($op['product'],$result2);
			
				}

				while ($row = $res3->fetch_assoc()) {

					$result3['plan_id']  					= $row['plan_id'];
					$result3['team_leader_id_fk']  			= $row['team_leader_id_fk'];
					
					array_push($op['saleman'],$result3);
			
				}		
				echo json_encode($op);
				exit;
			}else{
				echo $db->error;
				exit;
			}
		}



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

			$p_id = $_POST['p_id'];

			// we should show item that can be sell 
			// 0 : avaialbe in store no damge
			// 2 : avaible in production no damge
			// 3 : in production used not finished
			// 5 : partially sold
			// 8 : avaiable in store with damage
			// 9 : used in production + partially sold
			// 11: available in production with damage
			// And we should also send the remianed length of each roll 

			$res = $db->query("SELECT purchase_item_detail_id, item_length,stock_id_fk, stock_name,item_status,
													(	COALESCE(item_length,0) - 
														(COALESCE(damage_length,0) + 
														COALESCE(used_length,0) + 	
														COALESCE((select sum(sell_item_detail_height) FROM sell_item_detail SID WHERE SID.purchase_item_detail_id_fk = PID.purchase_item_detail_id AND SID.part_status=0 AND SID.is_deleted=0),0) + 
														COALESCE((select sum(damage_length) FROM sell_item_detail SID WHERE SID.purchase_item_detail_id_fk = PID.purchase_item_detail_id AND SID.part_status=0 AND SID.is_deleted=0),0))

													) as remained_length
													FROM purchase_item_detail PID
													LEFT JOIN stock S ON (PID.stock_id_fk = S.stock_id)
													WHERE (PID.item_status= 0 || PID.item_status= 2 || PID.item_status= 3 || PID.item_status= 5 || PID.item_status= 8 || PID.item_status= 9 || PID.item_status= 11  ) 
													AND PID.is_deleted=0 AND PID.product_id_fk = $p_id");
			if ($res) {
				
				$op  = array('data'=>array(),'stocks'=>array());
				while ($row = $res->fetch_assoc()) {
				   
						$result[0]  = $row['purchase_item_detail_id'];
						$result[1]  = $row['item_length'];
						$result[2]  = $row['stock_name'];
						$result[3]  = $row['stock_id_fk'];
						$result[4]  = $row['item_status'];
						$result[5]  = $row['remained_length'];
						
						
						array_push($op['data'],$result);
				}
				$res1 = $db->query("SELECT stock_id, stock_name, stock_type,
														(SELECT COUNT(*) FROM purchase_item_detail P WHERE P.stock_id_fk = S.stock_id AND P.is_deleted=0 AND (P.item_status= 0 || P.item_status= 2 || P.item_status= 3 || P.item_status= 5 || P.item_status= 8 || P.item_status= 9 || P.item_status= 11  ) AND P.product_id_fk = $p_id) AS roll_no
														FROM stock S
														WHERE  S.is_deleted = 0");
				
				while ($row1 = $res1->fetch_assoc()) {
				   
						$result1[0]  = $row1['stock_id'];
						$result1[1]  = $row1['stock_name'];
						$result1[2]  = $row1['stock_type'];
						$result1[3]  = $row1['roll_no'];
						array_push($op['stocks'],$result1);
				}

				echo json_encode($op);

				exit;
			}else{
				echo 0;
				exit;
			}
		}


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

			$id = $_POST['obj_id'];

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

				echo json_encode($op);
				exit;
			}else{
				echo 0;
				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 invoice_images SET is_deleted = 1 ,deleted_at = '$reg_date',last_action='$lastaction',last_action_by='".$_SESSION['user_id']."' WHERE image_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'] == "update") {

			
			

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

			
			$product_id						= $_POST["product_id"];
			$quantity						= $_POST["quantity"];
			$sell_price						= $_POST["sell_price"];
			$sell_item_note					= $_POST["sell_item_note"];
			
			$discount_array					= $_POST["discount_array"];
			
			$doctor_id						= $_POST["doctor_id"];
			$visit							= $_POST["visit"];
			$doctor_note					= $_POST["doctor_note"];
			
			
			$plan_title 					= addslashes($_POST["plan_title"]);
			$plan_description				= addslashes($_POST["plan_description"]);
			$plan_start_date			  	= addslashes($_POST["plan_start_date"]);
			$plan_end_date					= addslashes($_POST["plan_end_date"]);
			// $doctor_id_fk					= ($_POST["doctor_id_fk"]);
			$team_leader_id_fk	  			= ($_POST["team_leader_id_fk"]);
			$plan_note	  					= addslashes($_POST["plan_note"]);
			$city_id_fk	  					= addslashes($_POST["city_id_fk"]);
			$zone_id_fk	  					= addslashes($_POST["zone_id_fk"]);
						

			

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

			

			$db->autocommit(FALSE); // to rollback insertion if update fail
			$sql ="UPDATE plan SET   
								plan_title 					= '$plan_title', 		
								plan_description 			= '$plan_description',
										
								plan_start_date 			= '$plan_start_date', 
								plan_end_date 				= '$plan_end_date', 
								plan_note 					= '$plan_note', 
								city_id_fk 					= '$city_id_fk', 
								zone_id_fk 					= '$zone_id_fk', 
								
								updated_at 					= '$reg_date', 
								last_action 				= '$lastaction', 
								last_action_by 				= '".$_SESSION['user_id']."'
						 		WHERE plan_id = '$id'";
			
			$res = $db->query($sql);
			if ($res) {

				$invoice_inserted_id = $id;

				$chek_insertion = true;


				$delete_qry = "DELETE FROM plan_product WHERE plan_id_fk = $id";

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

				if(!$res2){
					$db->rollback();
					$op['is_success'] = false;
					$op['data'] 	  = "هناك خطأ : لم يتم تحديث  المواد";
					echo json_encode($op);
					exit;
				}


				$delete_qry1 = "DELETE FROM plan_doctor WHERE plan_id_fk = $id";

				$res_d = $db->query($delete_qry1);

				if(!$res_d){
					$db->rollback();
					$op['is_success'] = false;
					$op['data'] 	  = "هناك خطأ : لم يتم تحديث  الاطباء";
					echo json_encode($op);
					exit;
				}

				$delete_qry2 = "DELETE FROM plan_team_leader WHERE plan_id_fk = $id";

				$res_d2 = $db->query($delete_qry2);

				if(!$res_d2){
					$db->rollback();
					$op['is_success'] = false;
					$op['data'] 	  = "هناك خطأ : لم يتم تحديث  التيم ليدر";
					echo json_encode($op);
					exit;
				}


				$lastaction = "Add Product";
				$barcode    = "";

				for ($i=1;$i<count($product_id);$i++){


					$sql2 ="INSERT INTO plan_product (	
													quantity,
													plan_sell_price,
													plan_date,
													plan_product_offer, 
													plan_product_notes,
													
													product_id_fk,
													plan_id_fk,
													
													created_by,
													created_at,
													updated_at,
													last_action,
													last_action_by)

 								 			VALUES( '$quantity[$i]',
	 								 				'$sell_price[$i]',
 								 					'$plan_start_date',
	 								 				'$discount_array[$i]',
	 								 				'$sell_item_note[$i]',
	 								 				
	 								 				
	 								 				'$product_id[$i]',
	 								 				'$invoice_inserted_id',
	 								 				'".$_SESSION['user_id']."',
	 								 				'$reg_date',
	 								 				'$reg_date',
	 								 				'$lastaction',
	 								 				'".$_SESSION['user_id']."')";
					$res2 = $db->query($sql2);

					if (!$res2) {


						$chek_insertion = false;
						$db->rollback();
						$op['is_success'] = false;
						$op['data'] 	  = "هناك خطأ : لم يتم اضافة المبيعات الرجاء اعادة تحميل الصفحة والمحاولة من جديد";
						echo json_encode($op);
						exit;
					}

				} // for i


				
				for ($i=1;$i<count($doctor_id);$i++){


						$sql2 ="INSERT INTO plan_doctor (	
													
													number_of_visit, 
													plan_doctor_notes, 
													doctor_id_fk, 
													plan_id_fk,
													
													
													created_by,
													created_at,
													updated_at,
													last_action,
													last_action_by)

 								 			VALUES( 

 								 					'$visit[$i]',
 								 					'$doctor_note[$i]',
 								 					'$doctor_id[$i]',
	 								 				
	 								 				'$invoice_inserted_id',
	 								 				'".$_SESSION['user_id']."',
	 								 				'$reg_date',
	 								 				'$reg_date',
	 								 				'$lastaction',
	 								 				'".$_SESSION['user_id']."')";
					
						$res2 = $db->query($sql2);
					        
				}


				foreach ($team_leader_id_fk as $tm_id){


						$sql2 ="INSERT INTO plan_team_leader (	
													team_leader_id_fk, 
													plan_id_fk,
													
													
													created_by,
													created_at,
													updated_at,
													last_action,
													last_action_by)

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


				

			
				


				if($chek_insertion == true ){

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

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


		}


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

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

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


			$db->autocommit(FALSE); // to rollback update if update fail
			$res = $db->query("UPDATE plan SET 
											is_deleted = 1 ,
											deleted_at = '$reg_date',
											last_action='$lastaction',
											last_action_by='".$_SESSION['user_id']."' 
									WHERE plan_id=$id");
			if ($res){

					// update Product table for sell price and avarage price

					$sql1 ="UPDATE plan_doctor SET 
											is_deleted = '1',
								 			deleted_at='$reg_date', 
								 			last_action='$lastaction', 
								 			last_action_by='".$_SESSION['user_id']."'  
								 	WHERE plan_id_fk = $id";

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

					if ($res1) {


						$sql2 ="UPDATE plan_product SET 
												is_deleted = '1',
									 			deleted_at='$reg_date', 
									 			last_action='$lastaction', 
									 			last_action_by='".$_SESSION['user_id']."'  
									 	WHERE plan_id_fk = $id";

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

						$sql3 ="UPDATE plan_team_leader SET 
												is_deleted = '1',
									 			deleted_at='$reg_date', 
									 			last_action='$lastaction', 
									 			last_action_by='".$_SESSION['user_id']."'  
									 	WHERE plan_id_fk = $id";

						$res3 = $db->query($sql3);

						if ($res2 && $res3) {

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

						}else{

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

						}

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


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



		}

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

            $product_id						= $_POST["product_id"];
			$quantity						= $_POST["quantity"];
			$sell_price						= $_POST["sell_price"];
			$sell_item_note					= $_POST["sell_item_note"];
			
			$discount_array					= $_POST["discount_array"];
			
			$doctor_id						= $_POST["doctor_id"];
			$visit							= $_POST["visit"];
			$doctor_note					= $_POST["doctor_note"];
			
			
			$plan_title 					= addslashes($_POST["plan_title"]);
			$plan_description				= addslashes($_POST["plan_description"]);
			$plan_start_date			  	= addslashes($_POST["plan_start_date"]);
			$plan_end_date					= addslashes($_POST["plan_end_date"]);
			// $doctor_id_fk					= ($_POST["doctor_id_fk"]);
			$team_leader_id_fk	  			= ($_POST["team_leader_id_fk"]);
			$plan_note	  					= addslashes($_POST["plan_note"]);
			$city_id_fk	  					= addslashes($_POST["city_id_fk"]);
			$zone_id_fk	  					= addslashes($_POST["zone_id_fk"]);

			
			$term_and_conditions = "";

						

			

			$delete = 0;
			$reg_date = date("Y-m-d H:i:s", time());
			$lastaction = "Add New Plan";

			$res = $db->query("SELECT plan_sequence_no FROM plan ORDER BY plan_id DESC LIMIT 1");
			$row = $res->fetch_assoc();
			$plan_sequence_no 	= $row['plan_sequence_no'] +1;

			$dd = date("y", time());
			
			
			$plan_number = "P-".($plan_sequence_no);
			
			


			$db->autocommit(FALSE); // to rollback insertion if update fail
			$sql ="INSERT INTO plan (plan_number,
										plan_title, 		
										plan_description,
										
										plan_start_date, 
										plan_end_date, 
										plan_note, 
										plan_sequence_no, 
										city_id_fk, 
										zone_id_fk,
										
										created_by, 
										last_action, 
										last_action_by)
						 		VALUES( '$plan_number',
						 				'$plan_title',
						 				'$plan_description',
						 				
						 				'$plan_start_date',
						 				'$plan_end_date',
						 				'$plan_note',
						 				'$plan_sequence_no',
						 				'$city_id_fk',
						 				'$zone_id_fk',
						 				
						 				'".$_SESSION['user_id']."',
						 				'$lastaction',
						 				'".$_SESSION['user_id']."')";
			
			$res = $db->query($sql);
			if ($res) {

				$invoice_inserted_id = $db->insert_id;

				$chek_insertion = true;



				// insert into sell items the sold products
				$lastaction = "Add Product";
				$barcode    = "";

				for ($i=1;$i<count($product_id);$i++){


					$sql2 ="INSERT INTO plan_product (	
													quantity,
													plan_sell_price,
													plan_date,
													plan_product_offer, 
													plan_product_notes,
													
													product_id_fk,
													plan_id_fk,
													
													created_by,
													created_at,
													updated_at,
													last_action,
													last_action_by)

 								 			VALUES( '$quantity[$i]',
	 								 				'$sell_price[$i]',
 								 					'$plan_start_date',
	 								 				'$discount_array[$i]',
	 								 				'$sell_item_note[$i]',
	 								 				
	 								 				
	 								 				'$product_id[$i]',
	 								 				'$invoice_inserted_id',
	 								 				'".$_SESSION['user_id']."',
	 								 				'$reg_date',
	 								 				'$reg_date',
	 								 				'$lastaction',
	 								 				'".$_SESSION['user_id']."')";
					$res2 = $db->query($sql2);

					if (!$res2) {


						$chek_insertion = false;
						$db->rollback();
						$op['is_success'] = false;
						$op['data'] 	  = "هناك خطأ : لم يتم اضافة المبيعات الرجاء اعادة تحميل الصفحة والمحاولة من جديد";
						echo json_encode($op);
						exit;
					}

				} // for i


				
				for ($i=1;$i<count($doctor_id);$i++){


						$sql2 ="INSERT INTO plan_doctor (	
													
													number_of_visit, 
													plan_doctor_notes, 
													doctor_id_fk, 
													plan_id_fk,
													
													
													created_by,
													created_at,
													updated_at,
													last_action,
													last_action_by)

 								 			VALUES( 

 								 					'$visit[$i]',
 								 					'$doctor_note[$i]',
 								 					'$doctor_id[$i]',
	 								 				
	 								 				'$invoice_inserted_id',
	 								 				'".$_SESSION['user_id']."',
	 								 				'$reg_date',
	 								 				'$reg_date',
	 								 				'$lastaction',
	 								 				'".$_SESSION['user_id']."')";
					
						$res2 = $db->query($sql2);
					        
				}


				foreach ($team_leader_id_fk as $tm_id){


						$sql2 ="INSERT INTO plan_team_leader (	
													team_leader_id_fk, 
													plan_id_fk,
													
													
													created_by,
													created_at,
													updated_at,
													last_action,
													last_action_by)

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

				


				if($chek_insertion == true ){

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

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

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

		} // if create

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