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

require_once 'vendor/autoload.php';
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['city_id'])? ' AND R.city_id_fk='.$_GET['city_id']:'';
			$cond1 = isset($_GET['user_id'])? ' AND R.saleman_id_fk='.$_GET['user_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 . ' R.city_id_fk='.$pidc[$mr] ;
						$orr = ' OR ';
					}
					
					
				}
				
				$cond5 = $cond5 . ' ) ';

			}

			$cond = $cond5;
				
			$limits = "";
			


			$table  =  '(
				(
					SELECT
						report_id,
						report_number,
						report_title,
						report_date,				
						plan_number,
						R.plan_id_fk,
						
						ST1.full_name as saleman_name,
						city_name,
						doctor_name,
						PH.pharmacy_name,
						
						(COALESCE(doctor_name,PH1.pharmacy_name)) as d_p_name,
						ordered_quantity,
						is_delivered,
						product_name,
						RP.report_product_id,
						zone_name

					 FROM 	report_product_pharmacy RP

							LEFT JOIN report R ON (RP.report_id_fk = R.report_id)
					   		LEFT JOIN city C ON (R.city_id_fk = C.city_id)
							LEFT JOIN zone Z ON (R.zone_id_fk = Z.zone_id)
							LEFT JOIN doctor D ON (R.doctor_id_fk = D.doctor_id)
							LEFT JOIN plan PL ON (R.plan_id_fk = PL.plan_id)
							LEFT JOIN pharmacy PH ON (RP.pharmacy_id_fk = PH.pharmacy_id)
							LEFT JOIN pharmacy PH1 ON (R.pharmacy_id_fk = PH1.pharmacy_id)
							LEFT JOIN product P ON (RP.product_id_fk = P.product_id)
							LEFT JOIN staff ST1 ON (R.saleman_id_fk = ST1.staff_id)
		                    
				WHERE
					(R.is_deleted = 0   '.$cond.' '.$cond1.') '.$limits.'
				)
			) AS ALLTBL';
			// Table's primary key
			//echo $table;
			$primaryKey = 'report_id';
			$columns = array(
		    	array( 'db' => 'report_id', 'dt' => 0 ),
		    	array( 'db' => 'report_number', 'dt' => 1 ),
		    	array( 'db' => 'report_title', 'dt' => 2 ),
		    	array( 'db' => 'report_date', 'dt' => 3, 'formatter'=> function($d){
							return date('Y-m-d',strtotime($d));
					} ),
		    	
		    	array( 'db' => 'd_p_name', 'dt' => 4 ),
			  	array( 'db' => 'plan_number', 'dt' => 5 ),
				array( 'db' => 'product_name', 'dt' => 6 ),
				array( 'db' => 'ordered_quantity', 'dt' => 7 ),
				array( 'db' => 'pharmacy_name', 'dt' => 8 ),
				array( 'db' => 'city_name', 'dt' => 9 ),
				array( 'db' => 'zone_name', 'dt' => 10 ),
				array( 'db' => 'saleman_name', 'dt' => 11 ),
				array( 'db' => 'is_delivered', 'dt' => 12 ),
				array( 'db' => 'report_product_id', 'dt' => 13 ),
				array( 'db' => 'plan_id_fk', 'dt' => 14 ),
				
			);
			// SQL server connection information
			$sql_details = array(
			    'user' => $mysql_user,
			    'pass' => $mysql_password,
			    'db'   => $mysql_db,
			    'host' => $mysql_server
			);
			require( 'ssp.php' );
			echo json_encode(
			    SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns ,null, '' )
			);
			exit;
		}


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

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

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

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


			$db->autocommit(FALSE); // to rollback update if update fail
			$res = $db->query("UPDATE report_product_pharmacy SET 
											is_delivered 	= '$val' ,
											updated_at 		= '$reg_date',
											last_action 	='$lastaction',
											last_action_by 	='".$_SESSION['user_id']."' 
									WHERE report_product_id=$id");
			if ($res){

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

						
			

			}
			else{
				$op['data'] 	  = $db->error;

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



		}


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

			$id = $_POST['id'];


			$res = $db->query("SELECT 
									report_id, 
									report_number, 
									report_title, 
									report_description, 
									report_date, 
									doctor_offer,
									offer_money, 
									rating_doctor, 
									report_type,
									report_note, 
									is_out_plan,

									city_name,
									zone_name,
									doctor_name,
									doctor_address,
									doctor_specialty,
									full_name,
									PH.pharmacy_name,
									PH1.pharmacy_name as pharmacy_n,
									product_name,
									ordered_quantity,
									order_sell_price,
									rate_percent,
									opinion,
									report_product_notes,
									pharmacy_offer

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

								WHERE R.report_id='$id'  AND R.is_deleted = 0 ");

			

			if ($res ) {
				

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

					
				   
					$result['report_id']  			= $row['report_id'];
					$result['report_number']  		= $row['report_number'];
					$result['report_date']  		= date('Y-m-d',strtotime($row['report_date']));
					
					$result['report_title']  		= $row['report_title'];
					$result['report_description']  	= $row['report_description'];
					$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['report_note']  		= $row['report_note'];
					$result['is_out_plan']  		= $row['is_out_plan'];
					$result['city_name']  			= $row['city_name'];
					$result['zone_name']  			= $row['zone_name'];
					$result['doctor_name']  		= $row['doctor_name'];
					$result['pharmacy_n']  			= $row['pharmacy_n'];
					$result['doctor_address']  		= $row['doctor_address'];
					$result['doctor_specialty']  	= $row['doctor_specialty'];
					$result['full_name']  			= $row['full_name'];
					
					$result['product_name']  		= $row['product_name'];
					$result['pharmacy_name']  		= $row['pharmacy_name'];
					$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['report_product_notes'] = $row['report_product_notes'];
					
					array_push($op['report'],$result);
			
				}

					
				echo json_encode($op);
				exit;
			}else{
				echo $db->error;
				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']=="get_edit_report") {

			$id = $_POST['id'];


			$res = $db->query("SELECT 
										report_id, 
										report_number, 
										report_title, 
										report_description, 
										report_date, 
										doctor_offer,
										offer_money, 
										rating_doctor, 
										report_type,
										report_note, 
										is_out_plan,
										visit_type,
										R.pharmacy_id_fk as pharm_id_fk,
										doctor_id_fk,
										city_id_fk,
										zone_id_fk,
										R.plan_id_fk,


										product_id_fk,
										ordered_quantity,
										order_sell_price,
										rate_percent,
										opinion,
										report_product_notes,
										pharmacy_offer,
										RP.pharmacy_id_fk 



									FROM report R
										
                    					LEFT JOIN report_product_pharmacy RP ON (RP.report_id_fk = R.report_id)

										WHERE R.report_id='$id'  AND R.is_deleted = 0 ");


			
			if ($res ) {
				

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

					
				   
					$result['report_id']  			= $row['report_id'];
					$result['report_number']  		= $row['report_number'];
					$result['report_date']  		= date('Y-m-d',strtotime($row['report_date']));
					$result['report_title']  		= $row['report_title'];
					$result['report_description']  	= $row['report_description'];
					$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['report_note']  		= $row['report_note'];
					$result['is_out_plan']  		= $row['is_out_plan'];
					$result['visit_type']  			= $row['visit_type'];
					$result['pharm_id_fk']  		= $row['pharm_id_fk'];
					$result['doctor_id_fk']  		= $row['doctor_id_fk'];
					$result['city_id_fk']  			= $row['city_id_fk'];
					$result['zone_id_fk']  			= $row['zone_id_fk'];
					$result['plan_id_fk']  			= $row['plan_id_fk'];
					

					$result['product_id_fk']  		= $row['product_id_fk'];
					$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['report_product_notes'] = $row['report_product_notes'];
					$result['pharmacy_offer']  		= $row['pharmacy_offer'];
					$result['pharmacy_id_fk']  		= $row['pharmacy_id_fk'];

					array_push($op['plan'],$result);
			
				}

					
				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 Report";

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


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

					// update Product table for sell price and avarage price

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

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

					if ($res1) {


					
							$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'] == "create") {

            @$product_id						= $_POST["product_id"];
			@$quantity						= $_POST["quantity"];
			@$sell_price						= $_POST["sell_price"];
			@$ratio							= $_POST["ratio"];
			@$opinion						= $_POST["opinion"];
			
			@$pharmacy_id					= $_POST["pharmacy_id"];
			@$pharmacy_offer					= $_POST["pharmacy_offer"];
			@$notes							= $_POST["notes"];
			
			
			$report_title 					= addslashes($_POST["report_title"]);
			$report_description				= addslashes($_POST["report_description"]);
			$report_date			  		= addslashes($_POST["report_date"]);
			$doctor_offer					= addslashes($_POST["doctor_offer"]);
			$offer_money	  				= ($_POST["offer_money"]);
			$rating_doctor	  				= addslashes($_POST["rating_doctor"]);
			$report_note	  				= addslashes($_POST["report_note"]);
			$report_type	  				= addslashes($_POST["report_type"]);
			$visit_type	  					= addslashes($_POST["visit_type"]);
			$city_id_fk	  					= addslashes($_POST["city_id_fk"]);
			$zone_id_fk	  					= addslashes($_POST["zone_id_fk"]);
			$plan_id_fk	  					= addslashes($_POST["plan_id_fk"]);
			$doctor_id_fk	  				= addslashes($_POST["doctor_id_fk"]);
			$out_doctor_id_fk	  			= addslashes($_POST["out_doctor_id_fk"]);
			$pharmacy_id_fk	  				= addslashes($_POST["pharmacy_id_fk"]);

			
			$is_out_plan = 0;

			if ($doctor_id_fk =="" && $pharmacy_id_fk=="") {
				$is_out_plan = 1;
				$doctor_id_fk = $out_doctor_id_fk;
			}			

			

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

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

			$dd = date("y", time());
			
			
			$report_number = "R-".($report_sequence_no);
			
			


			$db->autocommit(FALSE); // to rollback insertion if update fail
			$sql ="INSERT INTO report (report_number,
										report_title, 		
										report_description,
										report_date, 
										doctor_offer, 
										offer_money, 
										rating_doctor, 
										report_type, 
										visit_type, 
										report_note, 
										report_sequence_no, 
										is_out_plan, 
										pharmacy_id_fk, 
										doctor_id_fk, 
										saleman_id_fk, 
										city_id_fk, 
										zone_id_fk,
										plan_id_fk,
										
										created_by, 
										last_action, 
										last_action_by)
						 		VALUES( '$report_number',
						 				'$report_title',
						 				'$report_description',
						 				'$report_date',
						 				'$doctor_offer',
						 				'$offer_money',
						 				'$rating_doctor',
						 				'$report_type',
						 				'$visit_type',
						 				'$report_note',
						 				'$report_sequence_no',
						 				'$is_out_plan',
						 				'$pharmacy_id_fk',
						 				'$doctor_id_fk',
						 				'".$_SESSION['user_id']."',
						 				'$city_id_fk',
						 				'$zone_id_fk',
						 				'$plan_id_fk',
						 				
						 				'".$_SESSION['user_id']."',
						 				'$lastaction',
						 				'".$_SESSION['user_id']."')";
			
			$res = $db->query($sql);
			if ($res) {

				$last_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 report_product_pharmacy (	
													ordered_quantity,
													order_sell_price,
													rate_percent,
													opinion, 
													report_product_date,
													report_product_notes,
													pharmacy_offer,
													pharmacy_id_fk,
													product_id_fk,
													report_id_fk,
													plan_id_fk,
													
													created_by,
													created_at,
													updated_at,
													last_action,
													last_action_by)

 								 			VALUES( '$quantity[$i]',
	 								 				'$sell_price[$i]',
	 								 				'$ratio[$i]',
	 								 				'$opinion[$i]',
	 								 				'$report_date',
	 								 				'$notes[$i]',
	 								 				'$pharmacy_offer[$i]',
	 								 				'$pharmacy_id[$i]',
	 								 				'$product_id[$i]',
	 								 				'$last_inserted_id',
	 								 				'$plan_id_fk',

	 								 				'".$_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


				
				

				


				if($chek_insertion == true ){

					$db->commit();
					$op['is_success'] = true;
					$op['data'] 	  = "تمت عملية الاضافة بنجاح";
					$op['id'] = $last_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;
	}
?>
