<?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['id'])? ' AND R.doctor_id_fk='.$_GET['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_description,
						report_date,
						doctor_offer,
						offer_money,
						rating_doctor,
						report_type,
						visit_type,
						report_note,
						is_out_plan,
						doctor_id_fk,
						saleman_id_fk,
						R.city_id_fk,
						R.zone_id_fk,
						plan_number,
						plan_id,
						ST.full_name,
						ST1.full_name as saleman_name,
						city_name,
						doctor_name,
						pharmacy_name,
						zone_name

					 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 doctor D ON (R.doctor_id_fk = D.doctor_id)
							LEFT JOIN pharmacy PH ON (R.pharmacy_id_fk = PH.pharmacy_id)
							LEFT JOIN plan PL ON (R.plan_id_fk = PL.plan_id)
							LEFT JOIN staff ST1 ON (R.saleman_id_fk = ST1.staff_id)
		                    
		                    LEFT JOIN staff ST ON (R.created_by = ST.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_description', 'dt' => 3 ),
		    	array( 'db' => 'report_date', 'dt' => 4, 'formatter'=> function($d){
							return date('Y-m-d',strtotime($d));
					} ),
		    	
		    	array( 'db' => 'doctor_name', 'dt' => 5 ),
		    	// array( 'db' => 'doctor_offer', 'dt' => 6 ),
		    	// array( 'db' => 'offer_money', 'dt' => 7 ),
			  	array( 'db' => 'rating_doctor', 'dt' => 6 ),
				array( 'db' => 'report_type', 'dt' => 7 ),
				array( 'db' => 'report_note', 'dt' => 8 ),
				array( 'db' => 'plan_number', 'dt' => 9 ),
				array( 'db' => 'city_name', 'dt' => 10 ),
				array( 'db' => 'zone_name', 'dt' => 11 ),
				array( 'db' => 'saleman_name', 'dt' => 12 ),
				array( 'db' => 'visit_type', 'dt' => 13 ),
				array( 'db' => 'pharmacy_name', 'dt' => 14 ),
				array( 'db' => 'plan_id', 'dt' => 15 ),
				
			);
			// 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']=="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,
									sub_zone_name,
									quarter_name,
									street_name,
									doctor_name,
									doctor_address,
									SP.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 sub_zone SZ ON (R.sub_zone_id_fk = SZ.sub_zone_id)
								LEFT JOIN quarter Q ON (R.quarter_id_fk = Q.quarter_id)
								LEFT JOIN street ST ON (R.street_id_fk = ST.street_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 specialty SP ON (SP.specialty_id = D.specialty_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['sub_zone_name']  		= $row['sub_zone_name'];
					$result['quarter_name']  		= $row['quarter_name'];
					$result['street_name']  		= $row['street_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,
										SP.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)
            							LEFT JOIN specialty SP ON (SP.specialty_id = D.specialty_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'] == "update") {

			
			

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

			
			@$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"]);
			$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;
			}			

			// visit should recored according to doctor or pharmacy city and zone

			if ($visit_type ==0) {
				$res = $db->query("SELECT 	city_id_fk,
											zone_id_fk,
											sub_zone_id_fk,
											quarter_id_fk,
											street_id_fk
									FROM doctor 
									WHERE doctor_id =$doctor_id_fk");
				
			}else{
				$res = $db->query("SELECT 	city_id_fk,
											zone_id_fk,
											sub_zone_id_fk,
											quarter_id_fk,
											street_id_fk
									FROM pharmacy 
									WHERE pharmacy_id =$pharmacy_id_fk");

			}

			$row = $res->fetch_assoc();
			$city_id_fk 	= $row['city_id_fk'];
			$zone_id_fk 	= $row['zone_id_fk'];
			$sub_zone_id_fk = $row['sub_zone_id_fk'];
			$quarter_id_fk 	= $row['quarter_id_fk'];
			$street_id_fk 	= $row['street_id_fk'];
			

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

			
			$dd = date("y", time());
			
			

			$db->autocommit(FALSE); // to rollback insertion if update fail
			
			$sql ="UPDATE report SET   
								report_title 			= '$report_title', 		
								report_date 			= '$report_date',
										
								doctor_offer 			= '$doctor_offer', 
								offer_money 			= '$offer_money', 
								rating_doctor 			= '$rating_doctor', 
								report_type 			= '$report_type', 
								visit_type 				= '$visit_type', 
								report_note 			= '$report_note', 
								is_out_plan 			= '$is_out_plan', 
								pharmacy_id_fk 			= '$pharmacy_id_fk', 
								doctor_id_fk 			= '$doctor_id_fk', 
								
								city_id_fk 				= '$city_id_fk', 
								zone_id_fk 				= '$zone_id_fk', 
								sub_zone_id_fk 			= '$sub_zone_id_fk', 
								quarter_id_fk 			= '$quarter_id_fk', 
								street_id_fk 			= '$street_id_fk', 
								plan_id_fk 				= '$plan_id_fk', 
								
								updated_at 					= '$reg_date', 
								last_action 				= '$lastaction', 
								last_action_by 				= '".$_SESSION['user_id']."'
						 		WHERE report_id = '$id'";
			
			$res = $db->query($sql);
			if ($res) {

				$last_inserted_id = $id;

				$chek_insertion = true;


				$delete_qry = "DELETE FROM report_product_pharmacy WHERE report_id_fk = $id";

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

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


				$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'] 	  = "تمت عملية التحديث بنجاح";
					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"];

			@$product_id 		= explode(',', $product_id);
			@$quantity 			= explode(',', $quantity);
			@$sell_price 		= explode(',', $sell_price);
			@$ratio 			= explode(',', $ratio);
			@$opinion 			= explode(',', $opinion);
			@$pharmacy_id 		= explode(',', $pharmacy_id);
			@$pharmacy_offer 	= explode(',', $pharmacy_offer);
			@$notes 			= explode(',', $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;
			}			

			// visit should recored according to doctor or pharmacy city and zone

			if ($visit_type ==0) {
				$res = $db->query("SELECT 	city_id_fk,
											zone_id_fk,
											sub_zone_id_fk,
											quarter_id_fk,
											street_id_fk
									FROM doctor 
									WHERE doctor_id =$doctor_id_fk");
				
			}else{
				$res = $db->query("SELECT 	city_id_fk,
											zone_id_fk,
											sub_zone_id_fk,
											quarter_id_fk,
											street_id_fk
									FROM pharmacy 
									WHERE pharmacy_id =$pharmacy_id_fk");

			}

			$row = $res->fetch_assoc();
			$city_id_fk 	= $row['city_id_fk'];
			$zone_id_fk 	= $row['zone_id_fk'];
			$sub_zone_id_fk = $row['sub_zone_id_fk'];
			$quarter_id_fk 	= $row['quarter_id_fk'];
			$street_id_fk 	= $row['street_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_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,
										sub_zone_id_fk,
										quarter_id_fk,
										street_id_fk,
										plan_id_fk,
										
										created_by, 
										last_action, 
										last_action_by)
						 		VALUES( '$report_number',
						 				'$report_title',
						 				'$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',
						 				'$sub_zone_id_fk',
						 				'$quarter_id_fk',
						 				'$street_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


				
				/// for file upload
				$target_dir = '../visit_record/'.$last_inserted_id;
				if(! file_exists($target_dir)){
					mkdir($target_dir);
				}
					

				$count=0;

				$image_path		='';
                $manual_path 	='';
                $State 			=true;
                $State1			=true;
                $error 			= '';			
                $error1			= '';


					
				if(isset($_FILES['audio_data']) && $_FILES['audio_data']['size'] > 0 ){
					
					
					$image_file = $last_inserted_id."_".rand(100000,10000000) ."_.wav";
									
					$target_file 		= $target_dir .'/'. basename($image_file);
					
					$uploadOk = 1;
					$imageFileType  = pathinfo($target_file,PATHINFO_EXTENSION);
										
					$State1=true;
					// Check file size
					if ($_FILES["audio_data"]["size"] > 6000000) {
							$error1= " Sorry, your file is too large. ";
							$State1=false;
					}

					
					

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

							$image_path  = 'visit_record/'.$last_inserted_id .'/'.$image_file;
							$State1=true;											  

						} else {
								$error1= " Sorry, there was an error uploading your file. ";
								$State1=false;
						}
					}
				}		
                
                $tmp 			='';
		        
		        $sql ="UPDATE  report SET report_description = '$image_path' WHERE report_id ='$last_inserted_id'";
							
				$query = $db->query($sql);
				if (!$query) {
					$msgg= "Image Not Uploaded";
					$State=false;

				}else{
					$msgg= "Successfully Added ";
					$State=true;
				}


				


				if($chek_insertion == true ){

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

					
					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;
	}
?>
