<?php
class Courierv2 extends MY_Controller
{
   var $db_onedev;
   public function index()
   {
      echo "Management Courier API";
   }
   
   public function __construct()
   {
      parent::__construct();
      $this->db_onedev = $this->load->database("onedev", true);
   }
   
   public function search()
   {
      $prm = $this->sys_input;
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}

      $xdate = $prm["xdate"];
	  $city = $prm["city"];
	  $district = $prm["district"];
	  $kelurahan = $prm["kelurahan"];
	  $status = $prm["status"];
	  
	  $filter_city = '';
	  $filter_district = '';
	  $filter_kelurahan = '';
	  if($city['id'] !== 'all' && $district['id'] == 'all' && $kelurahan['id'] == 'all'){
		  $filter_city = "AND M_CityID = {$city['id']}";
	  }
	  
	  if($district['id'] != 'all' && $kelurahan['id'] == 'all'){
		  $filter_district = "AND M_DistrictID = {$district['id']}";
	  }
	  
	  if($kelurahan['id'] != 'all'){
		  $filter_kelurahan = "AND M_KelurahanID = {$kelurahan['id']}";
	  }
	  
	  $having = "";
	  if($status != 'A'){
		  $having = "HAVING status = '{$prm['status']}'";
	  }

	 // echo $norm;
	  
    
	$number_limit = 10;
	$number_offset = ($prm['current_page'] - 1) * $number_limit ;

      $sql = "	SELECT count(*) as total
                FROM (
					SELECT xid, xnumber, xdate, courier_id, courier_name, kelurahanid, districtid, cityid, xtype_name, xtype_code,
				GROUP_CONCAT(DISTINCT M_DistrictName separator ' , ') as districts,
				GROUP_CONCAT(DISTINCT M_KelurahanName separator ' , ') as kelurahans,
				IF(ISNULL(Management_CourierID),'N','Y') as status
				 FROM (
					SELECT So_WalkLetterCourierID as xid,
					So_WalkLetterCourierNumbering as xnumber,
					DATE_FORMAT(So_WalkLetterCourierDate,'%d-%m-%Y') as xdate,
					M_CourierID as courier_id,
					M_StaffName as courier_name,
					M_KelurahanID as kelurahanid,
					M_DistrictID as districtid,
					M_CityID as cityid,
					'KIRIM IMAGE' as xtype_name,
					'SI' as xtype_code
					FROM so_walklettercourier
					JOIN m_staff ON So_WalkLetterCourierM_StaffID = M_StaffID
					JOIN m_courier ON M_COurierM_StaffID = M_StaffID
					JOIN m_doctoraddress ON So_WalkLetterCourierM_DoctorAddressID = M_DoctorAddressID
					JOIN m_kelurahan ON M_DoctorAddressM_KelurahanID = M_KelurahanID $filter_kelurahan
					JOIN m_district ON M_KelurahanM_DistrictID = M_DistrictID $filter_district
					JOIN m_city ON M_DistrictM_CityID = M_CityID $filter_city
					WHERE
					So_WalkLetterCourierIsActive = 'Y' AND So_WalkLetterCourierDate = '{$xdate}'
					UNION
					SELECT Courier_AdHocID as xid,
					Courier_AdHocNumbering as xnumber,
					DATE_FORMAT(Courier_AdHocDate,'%d-%m-%Y') as xdate,
					M_CourierID as courier_id,
					M_StaffName as courier_name,
					M_KelurahanID as kelurahanid,
					M_DistrictID as districtid,
					M_CityID as cityid,
					Courier_AdHocTaskLabel as xtype_name,
					'AC' as xtype_code
					FROM courier_adhoc
					JOIN m_courier ON Courier_AdHocM_CourierID = M_CourierID
					JOIN m_staff ON M_CourierM_StaffID = M_StaffID
					JOIN m_kelurahan ON Courier_AdHocM_KelurahanID = M_KelurahanID $filter_kelurahan
					JOIN m_district ON M_KelurahanM_DistrictID = M_DistrictID $filter_district
					JOIN m_city ON M_DistrictM_CityID = M_CityID $filter_city 
					WHERE
					Courier_AdHocIsActive = 'Y' AND Courier_AdHocDate = '{$xdate}'
					UNION
					SELECT So_WalkLetterResultID as xid,
					So_WalkLetterResultNumbering as xnumber,
					DATE_FORMAT(So_WalkLetterResultDate,'%d-%m-%Y') as xdate,
					M_CourierID as courier_id,
					M_StaffName as courier_name,
					M_KelurahanID as kelurahanid,
					M_DistrictID as districtid,
					M_CityID as cityid,
					'KIRIM HASIL DOKTER' as xtype_name,
					'SR' as xtype_code
					FROM so_walkletterresult
					JOIN m_staff ON So_WalkLetterResultM_StaffID = M_StaffID
					JOIN m_courier ON M_COurierM_StaffID = M_StaffID
					JOIN m_doctoraddress ON So_WalkLetterResultM_DoctorAddressID = M_DoctorAddressID
					JOIN m_kelurahan ON M_DoctorAddressM_KelurahanID = M_KelurahanID $filter_kelurahan
					JOIN m_district ON M_KelurahanM_DistrictID = M_DistrictID $filter_district
					JOIN m_city ON M_DistrictM_CityID = M_CityID $filter_city
					WHERE
					So_WalkLetterResultIsActive = 'Y' AND So_WalkLetterResultDate = '{$xdate}'
					UNION
					SELECT Result_CourierSPKID as xid,
					Result_CourierSPKNumbering as xnumber,
					DATE_FORMAT(Result_CourierSPKDate,'%d-%m-%Y') as xdate,
					M_CourierID as courier_id,
					M_StaffName as courier_name,
					M_KelurahanID as kelurahanid,
					M_DistrictID as districtid,
					M_CityID as cityid,
					'PENGIRIMAN HASIL' as xtype_name,
					'DR' as xtype_code
					FROM result_courierspk
					JOIN result_courierspk_detail  ON Result_CourierSPKDetailResult_CourierSPKID = Result_CourierSPKID
					JOIN m_courier ON Result_CourierSPKM_CourierID = M_CourierID
					JOIN m_staff ON M_CourierM_StaffID = M_StaffID
					JOIN t_orderdelivery ON Result_CourierSPKDetailT_OrderDeliveryID = T_OrderDeliveryID
					JOIN m_kelurahan ON T_OrderDeliveryM_KelurahanID = M_KelurahanID $filter_kelurahan
					JOIN m_district ON M_KelurahanM_DistrictID = M_DistrictID $filter_district
					JOIN m_city ON M_DistrictM_CityID = M_CityID $filter_city
					WHERE
					Result_CourierSPKIsActive = 'Y' AND Result_CourierSPKDate = '{$xdate}'
					GROUP BY Result_CourierSPKID
					UNION
					SELECT
					T_ReceiverefDistributCourierID as xid,
					T_ReceiverefDistributCourierNumber as xnumber,
					DATE_FORMAT(T_ReceiverefDistributCourierDate,'%d-%m-%Y') as xdate,
					M_CourierID as courier_id,
					M_StaffName as courier_name,
					M_CompanyM_KelurahanID as kelurahanid,  
					M_CompanyM_DistrictID as districtid,
					M_CompanyM_CityID as cityid,
					'RUJUKAN EKSTERNAL' as xtype_name,
					'EL' as xtype_code
					FROM t_receiverefdistributcourier
					JOIN t_receiverefdistributcourierdetail ON T_ReceiverefDistributCourierID = T_ReceiverefDistributCourierDetailT_ReceiverefDistributCourierID AND T_ReceiverefDistributCourierDetailIsActive = 'Y'
					JOIN t_receivereferencedelivery ON T_ReceiverefDistributCourierDetailT_ReceiveReferenceDeliveryID = T_ReceiveReferenceDeliveryID
					JOIN m_company ON T_ReceiveReferenceDeliveryM_CompanyID = M_CompanyID
					JOIN m_staff ON T_ReceiverefDistributCourierM_StaffID = M_StaffID
					JOIN m_courier ON M_COurierM_StaffID = M_StaffID
					JOIN m_kelurahan ON M_CompanyM_KelurahanID = M_KelurahanID $filter_kelurahan
					JOIN m_district ON M_CompanyM_DistrictID = M_DistrictID $filter_district
					JOIN m_city ON M_CompanyM_CityID = M_CityID $filter_city
					WHERE
					T_ReceiverefDistributCourierIsActive = 'Y' AND T_ReceiverefDistributCourierDate = '{$xdate}'
					UNION
					SELECT T_RefDeliveryOrderID as xid,
					T_RefDeliveryOrderNumber as xnumber,
					DATE_FORMAT(T_RefDeliveryOrderDate,'%d-%m-%Y') as xdate,
					T_RefDeliveryOrderM_CourierID as courier_id,
					M_StaffName as courier_name,
					T_RefDeliveryOrderM_KelurahanID as kelurahanid,
					T_RefDeliveryOrderM_DistrictID as districtid,
					T_RefDeliveryOrderM_CityID as cityid,
					'SURAT JALAN RUJUKAN' as xtype_name,
					'SJR' as xtype_code
					FROM t_ref_deliveryorder
					JOIN m_courier ON T_RefDeliveryOrderM_CourierID = M_CourierID
					JOIN m_staff ON M_CourierM_StaffID = M_StaffID
					JOIN m_kelurahan ON T_RefDeliveryOrderM_KelurahanID = M_KelurahanID $filter_kelurahan
					JOIN m_district ON T_RefDeliveryOrderM_DistrictID = M_DistrictID $filter_district
					JOIN m_city ON T_RefDeliveryOrderM_CityID = M_CityID $filter_city
					WHERE
					T_RefDeliveryOrderIsActive = 'Y' AND T_RefDeliveryOrderDate = '{$xdate}'
					GROUP BY T_RefDeliveryOrderID
				 )x
				 JOIN m_district ON M_DistrictID = x.districtid
				 JOIN m_kelurahan ON M_KelurahanID = x.kelurahanid
				 LEFT JOIN management_courier ON Management_CourierReffNumbering = xnumber AND Management_CourierReffID = xid AND Management_CourierIsActive = 'Y'
				 GROUP BY xnumber
				 $having
				) cc
				";
//echo $sql;
		$query = $this->db_onedev->query($sql, $sql_param);

		$tot_count = 0;
		$tot_page = 0;
		if ($query) {
			$tot_count = $query->result_array()[0]["total"];
			$tot_page = ceil($tot_count/$number_limit);
		} else {
			$this->sys_error_db("m_doctor count", $this->db_onedev);
			exit;
		}  
	  
		$sql = 	"SELECT xid, xnumber, xdate, courier_id, courier_name, kelurahanid, districtid, cityid, xtype_name, xtype_code,
				GROUP_CONCAT(DISTINCT M_DistrictName separator ' , ') as districts,
				GROUP_CONCAT(DISTINCT M_KelurahanName separator ' , ') as kelurahans,
				IF(ISNULL(Management_CourierID),'N','Y') as status
				 FROM (
					SELECT So_WalkLetterCourierID as xid,
					So_WalkLetterCourierNumbering as xnumber,
					DATE_FORMAT(So_WalkLetterCourierDate,'%d-%m-%Y') as xdate,
					M_CourierID as courier_id,
					M_StaffName as courier_name,
					M_KelurahanID as kelurahanid,
					M_DistrictID as districtid,
					M_CityID as cityid,
					'KIRIM IMAGE' as xtype_name,
					'SI' as xtype_code
					FROM so_walklettercourier
					JOIN m_staff ON So_WalkLetterCourierM_StaffID = M_StaffID
					JOIN m_courier ON M_COurierM_StaffID = M_StaffID
					JOIN m_doctoraddress ON So_WalkLetterCourierM_DoctorAddressID = M_DoctorAddressID
					JOIN m_kelurahan ON M_DoctorAddressM_KelurahanID = M_KelurahanID $filter_kelurahan
					JOIN m_district ON M_KelurahanM_DistrictID = M_DistrictID $filter_district
					JOIN m_city ON M_DistrictM_CityID = M_CityID $filter_city
					WHERE
					So_WalkLetterCourierIsActive = 'Y' AND So_WalkLetterCourierDate = '{$xdate}'
					UNION
					SELECT Courier_AdHocID as xid,
					Courier_AdHocNumbering as xnumber,
					DATE_FORMAT(Courier_AdHocDate,'%d-%m-%Y') as xdate,
					M_CourierID as courier_id,
					M_StaffName as courier_name,
					M_KelurahanID as kelurahanid,
					M_DistrictID as districtid,
					M_CityID as cityid,
					Courier_AdHocTaskLabel as xtype_name,
					'AC' as xtype_code
					FROM courier_adhoc
					JOIN m_courier ON Courier_AdHocM_CourierID = M_CourierID
					JOIN m_staff ON M_CourierM_StaffID = M_StaffID
					JOIN m_kelurahan ON Courier_AdHocM_KelurahanID = M_KelurahanID $filter_kelurahan
					JOIN m_district ON M_KelurahanM_DistrictID = M_DistrictID $filter_district
					JOIN m_city ON M_DistrictM_CityID = M_CityID $filter_city 
					WHERE
					Courier_AdHocIsActive = 'Y' AND Courier_AdHocDate = '{$xdate}'
					UNION
					SELECT So_WalkLetterResultID as xid,
					So_WalkLetterResultNumbering as xnumber,
					DATE_FORMAT(So_WalkLetterResultDate,'%d-%m-%Y') as xdate,
					M_CourierID as courier_id,
					M_StaffName as courier_name,
					M_KelurahanID as kelurahanid,
					M_DistrictID as districtid,
					M_CityID as cityid,
					'KIRIM HASIL DOKTER' as xtype_name,
					'SR' as xtype_code
					FROM so_walkletterresult
					JOIN m_staff ON So_WalkLetterResultM_StaffID = M_StaffID
					JOIN m_courier ON M_COurierM_StaffID = M_StaffID
					JOIN m_doctoraddress ON So_WalkLetterResultM_DoctorAddressID = M_DoctorAddressID
					JOIN m_kelurahan ON M_DoctorAddressM_KelurahanID = M_KelurahanID $filter_kelurahan
					JOIN m_district ON M_KelurahanM_DistrictID = M_DistrictID $filter_district
					JOIN m_city ON M_DistrictM_CityID = M_CityID $filter_city
					WHERE
					So_WalkLetterResultIsActive = 'Y' AND So_WalkLetterResultDate = '{$xdate}'
					UNION
					SELECT Result_CourierSPKID as xid,
					Result_CourierSPKNumbering as xnumber,
					DATE_FORMAT(Result_CourierSPKDate,'%d-%m-%Y') as xdate,
					M_CourierID as courier_id,
					M_StaffName as courier_name,
					M_KelurahanID as kelurahanid,
					M_DistrictID as districtid,
					M_CityID as cityid,
					'PENGIRIMAN HASIL' as xtype_name,
					'DR' as xtype_code
					FROM result_courierspk
					JOIN result_courierspk_detail  ON Result_CourierSPKDetailResult_CourierSPKID = Result_CourierSPKID
					JOIN m_courier ON Result_CourierSPKM_CourierID = M_CourierID
					JOIN m_staff ON M_CourierM_StaffID = M_StaffID
					JOIN t_orderdelivery ON Result_CourierSPKDetailT_OrderDeliveryID = T_OrderDeliveryID
					JOIN m_kelurahan ON T_OrderDeliveryM_KelurahanID = M_KelurahanID $filter_kelurahan
					JOIN m_district ON M_KelurahanM_DistrictID = M_DistrictID $filter_district
					JOIN m_city ON M_DistrictM_CityID = M_CityID $filter_city
					WHERE
					Result_CourierSPKIsActive = 'Y' AND Result_CourierSPKDate = '{$xdate}'
					GROUP BY Result_CourierSPKID
					UNION
					SELECT
					T_ReceiverefDistributCourierID as xid,
					T_ReceiverefDistributCourierNumber as xnumber,
					DATE_FORMAT(T_ReceiverefDistributCourierDate,'%d-%m-%Y') as xdate,
					M_CourierID as courier_id,
					M_StaffName as courier_name,
					M_CompanyM_KelurahanID as kelurahanid,  
					M_CompanyM_DistrictID as districtid,
					M_CompanyM_CityID as cityid,
					'RUJUKAN EKSTERNAL' as xtype_name,
					'EL' as xtype_code
					FROM t_receiverefdistributcourier
					JOIN t_receiverefdistributcourierdetail ON T_ReceiverefDistributCourierID = T_ReceiverefDistributCourierDetailT_ReceiverefDistributCourierID AND T_ReceiverefDistributCourierDetailIsActive = 'Y'
					JOIN t_receivereferencedelivery ON T_ReceiverefDistributCourierDetailT_ReceiveReferenceDeliveryID = T_ReceiveReferenceDeliveryID
					JOIN m_company ON T_ReceiveReferenceDeliveryM_CompanyID = M_CompanyID
					JOIN m_staff ON T_ReceiverefDistributCourierM_StaffID = M_StaffID
					JOIN m_courier ON M_COurierM_StaffID = M_StaffID
					JOIN m_kelurahan ON M_CompanyM_KelurahanID = M_KelurahanID $filter_kelurahan
					JOIN m_district ON M_CompanyM_DistrictID = M_DistrictID $filter_district
					JOIN m_city ON M_CompanyM_CityID = M_CityID $filter_city
					WHERE
					T_ReceiverefDistributCourierIsActive = 'Y' AND T_ReceiverefDistributCourierDate = '{$xdate}'
					UNION
					SELECT T_RefDeliveryOrderID as xid,
					T_RefDeliveryOrderNumber as xnumber,
					DATE_FORMAT(T_RefDeliveryOrderDate,'%d-%m-%Y') as xdate,
					T_RefDeliveryOrderM_CourierID as courier_id,
					M_StaffName as courier_name,
					T_RefDeliveryOrderM_KelurahanID as kelurahanid,
					T_RefDeliveryOrderM_DistrictID as districtid,
					T_RefDeliveryOrderM_CityID as cityid,
					'SURAT JALAN RUJUKAN' as xtype_name,
					'SJR' as xtype_code
					FROM t_ref_deliveryorder
					JOIN m_courier ON T_RefDeliveryOrderM_CourierID = M_CourierID
					JOIN m_staff ON M_CourierM_StaffID = M_StaffID
					JOIN m_kelurahan ON T_RefDeliveryOrderM_KelurahanID = M_KelurahanID $filter_kelurahan
					JOIN m_district ON T_RefDeliveryOrderM_DistrictID = M_DistrictID $filter_district
					JOIN m_city ON T_RefDeliveryOrderM_CityID = M_CityID $filter_city
					WHERE
					T_RefDeliveryOrderIsActive = 'Y' AND T_RefDeliveryOrderDate = '{$xdate}'
					GROUP BY T_RefDeliveryOrderID
				 )x
				 JOIN m_district ON M_DistrictID = x.districtid
				 JOIN m_kelurahan ON M_KelurahanID = x.kelurahanid
				 LEFT JOIN management_courier ON Management_CourierReffNumbering = xnumber AND Management_CourierReffID = xid AND Management_CourierIsActive = 'Y'
				 GROUP BY xnumber
				 $having
				limit $number_limit offset $number_offset";

		$query = $this->db_onedev->query($sql, $sql_param);
		//echo $this->db_onedev->last_query();
		$rows = $query->result_array();
		if($rows){
			foreach($rows as $k => $v){				
				//$rows[$k]['verification_px'] = $this->add_verification_test($v['M_PatientID']);
				
			}
		}
	  
	  
		//$this->_add_address($rows);
		$result = array("total" => $tot_page, "records" => $rows, "sql"=> $this->db_onedev->last_query());
		$this->sys_ok($result);
		exit;
   }
   
	function getinitdata(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$rows = [];
		$query ="	SELECT M_CityID as id, M_CityName as name
					FROM m_city 
					WHERE	
						M_CityIsActive = 'Y'
						ORDER BY M_CityName ASC
				";
				//echo $query;
		$cities = $this->db_onedev->query($query)->result_array();
		$rows['cities'] = array();
		$rows['cities_form'] = $cities;
		array_push($rows['cities'],array('id'=>'all','name'=>'Semua Kota'));
		$query ="	SELECT M_CityID as id, M_CityName as name
					FROM m_city 
					JOIN m_branch ON M_BranchM_CityID = M_CityID AND M_BranchIsActive = 'Y' AND M_BranchIsDefault = 'Y'
					WHERE	
						M_CityIsActive = 'Y' LIMIT 1
				";
				//echo $query;
		$rows['selected_city_form'] = $this->db_onedev->query($query)->row_array();
		$rows['selected_city'] = array('id'=>'all','name'=>'Semua Kota');
		$query ="	SELECT M_DistrictID as id, M_DistrictName as name
					FROM m_district 
					WHERE	
						M_DistrictM_CityID = {$rows['selected_city_form']['id']} AND M_DistrictIsActive = 'Y'
					
				";
				//echo $query;
				
		$rows['districts'] = array();
		$rows['districts_form'] = $rows['districts'];
		array_push($rows['districts'],array('id'=>'all','name'=>'Semua Kecamatan'));
		$rows['selected_district'] = array('id'=>'all','name'=>'Semua Kecamatan');
		$rows['kelurahans'] = array(array('id'=>'all','name'=>'Semua Kelurahan'));
		$rows['selected_kelurahan'] = array('id'=>'all','name'=>'Semua Kelurahan');
		$query ="	SELECT M_CourierID as id, M_StaffName as name
					FROM m_courier
					JOIN m_staff ON M_CourierM_StaffID = M_StaffID 
					WHERE	
						M_StaffIsActive = 'Y'
				";
				//echo $query;
		$rows['couriers'] = $this->db_onedev->query($query)->result_array();
		

		$result = array(
			"total" => count($rows) , 
			"records" => $rows, 
		); 
		$this->sys_ok($result);
		exit;
	}

	function searchcity(){
		if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }
		$prm = $this->sys_input;

		$max_rst = 12;
		$tot_count =0;

		$q = [
			'search'     => '%'
		];

		if ($prm['search'] != '')
		{
			$q['search'] = "%{$prm['search']}%";  
		}

		  // QUERY TOTAL
		$sql = "SELECT count(*) as total
				FROM m_city
				WHERE 
				M_CityName like ?
				AND M_CityIsActive = 'Y'";
		$query = $this->db_onedev->query($sql,$q['search']);
		//echo $query;
		if ($query) { 
			$tot_count = $query->result_array()[0]["total"]; 
		} 
		else {
			$this->sys_error_db("m_city count",$this->db_onedev);
			exit;
		}

		$sql = "
			SELECT M_CityID as id, M_CityName as name
			FROM m_city
			WHERE 
			M_CityName like ?
			AND M_CityIsActive = 'Y'
			ORDER BY M_CityName DESC
		  ";
		  $query = $this->db_onedev->query($sql, array($q['search']));
			
		  if ($query) { 
			 $rows = $query->result_array();
			 array_push($rows,array('id'=>'all','name'=>'Semua Kota'));
			 //echo $this->db_onedev->last_query();
			 $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
			 $this->sys_ok($result);
		  } 
		  else {
			 $this->sys_error_db("m_city rows",$this->db_onedev);
			 exit;
		  }
	}
	
	function getdistrict(){
		if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }
		$prm = $this->sys_input;
		$query ="	SELECT M_DistrictID as id, M_DistrictName as name
					FROM m_district 
					WHERE	
						M_DistrictIsActive = 'Y' AND M_DistrictM_CityID = ?
				";
				//echo $query;
		$rows = $this->db_onedev->query($query,array($prm['id']))->result_array();
		
		$result = array(
			"total" => count($rows) , 
			"records" => $rows, 
		); 
		$this->sys_ok($result);
		exit;
	}
	
	function getkelurahan(){
		if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }
		$prm = $this->sys_input;
		$query ="	SELECT M_KelurahanID as id, M_KelurahanName as name
					FROM m_kelurahan 
					WHERE	
						M_KelurahanIsActive = 'Y' AND M_KelurahanM_DistrictID = ?
				";
				//echo $query;
		$rows = $this->db_onedev->query($query,array($prm['id']))->result_array();
		
		$result = array(
			"total" => count($rows) , 
			"records" => $rows, 
		); 
		$this->sys_ok($result);
		exit;
	}
	
	function save(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$query ="UPDATE m_doctor SET
					M_DoctorPrefix = '{$prm['M_DoctorPrefix']}',
					M_DoctorName = '{$prm['M_DoctorName']}',
					M_DoctorSufix = '{$prm['M_DoctorSufix']}',
					M_DoctorM_SexID = '{$prm['M_DoctorM_SexID']}',
					M_DoctorM_ReligionID = '{$prm['M_DoctorM_ReligionID']}',
					M_DoctorEmail = '{$prm['M_DoctorEmail']}',
					M_DoctorHP = '{$prm['M_DoctorHP']}',
					M_DoctorPhone = '{$prm['M_DoctorPhone']}',
					M_DoctorIsMarketingConfirm = '{$prm['M_DoctorIsMarketingConfirm']}',
					M_DoctorIsPJ = '{$prm['M_DoctorIsPJ']}',
					M_DoctorIsDefaultPJ = '{$prm['M_DoctorIsDefaultPJ']}',
					M_DoctorIsClinic = '{$prm['M_DoctorIsClinic']}',
					M_DoctorIsDefault = '{$prm['M_DoctorIsDefault']}'
				WHERE
					M_DoctorID = '{$prm['M_DoctorID']}'
				";
				//echo $query;
		$rows = $this->db_onedev->query($query);
		
		$result = array(
			"total" => 1 , 
			"records" => array('status'=>'OK')
		); 
		$this->sys_ok($result);
		exit;
	}
	
	function newdoctor(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$query ="INSERT INTO m_doctor (
						M_DoctorPrefix,
						M_DoctorName,
						M_DoctorSufix,
						M_DoctorM_SexID,
						M_DoctorM_ReligionID,
						M_DoctorEmail,
						M_DoctorHP,
						M_DoctorPhone,
						M_DoctorIsMarketingConfirm,
						M_DoctorIsPJ,
						M_DoctorIsDefaultPJ,
						M_DoctorIsClinic,
						M_DoctorIsDefault,
						M_DoctorCreated
					)
					VALUES(
						'{$prm['M_DoctorPrefix']}',
						'{$prm['M_DoctorName']}',
						'{$prm['M_DoctorSufix']}',
						'{$prm['M_DoctorM_SexID']}',
						'{$prm['M_DoctorM_ReligionID']}',
						'{$prm['M_DoctorEmail']}',
						'{$prm['M_DoctorHP']}',
						'{$prm['M_DoctorPhone']}',
						'{$prm['M_DoctorIsMarketingConfirm']}',
						'{$prm['M_DoctorIsPJ']}',
						'{$prm['M_DoctorIsDefaultPJ']}',
						'{$prm['M_DoctorIsClinic']}',
						'{$prm['M_DoctorIsDefault']}',
						NOW()
					)
				";
				//echo $query;
		$rows = $this->db_onedev->query($query);
		$last_id = $this->db_onedev->insert_id();
		$result = array(
			"total" => 1 , 
			"records" => array('status'=>'OK'),
			"id" => $last_id 
		); 
		$this->sys_ok($result);
		exit;
	}
	
	function dochangecourier(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		
		$prm = $this->sys_input;
		if($prm['xtype_code'] === 'AC'){
			$query ="UPDATE courier_adhoc SET
						Courier_AdHocM_CourierID = '{$prm['courier_change']['id']}'
					WHERE
						Courier_AdHocID = '{$prm['xid']}' AND Courier_AdHocNumbering = '{$prm['xnumber']}'
					";
					//echo $query;
			$rows = $this->db_onedev->query($query);
		}
		
		if($prm['xtype_code'] === 'SI'){
			$query = "	SELECT * 
						FROM m_courier 
						WHERE
						M_CourierID = '{$prm['courier_change']['id']}'";
			$m_staffid = $this->db_onedev->query($query)->row()->M_CourierM_StaffID;
			$query ="UPDATE so_walklettercourier SET
						So_WalkLetterCourierM_StaffID = '{$m_staffid}'
					WHERE
						So_WalkLetterCourierID = '{$prm['xid']}' AND So_WalkLetterCourierNumbering = '{$prm['xnumber']}'
					";
					//echo $query;
			$rows = $this->db_onedev->query($query);
		}
		
		if($prm['xtype_code'] === 'SR'){
			$query = "	SELECT * 
						FROM m_courier 
						WHERE
						M_CourierID = '{$prm['courier_change']['id']}'";
			$m_staffid = $this->db_onedev->query($query)->row()->M_CourierM_StaffID;
			$query ="UPDATE so_walkletterresult SET
						So_WalkLetterResultM_StaffID = '{$m_staffid}'
					WHERE
						So_WalkLetterResultID = '{$prm['xid']}' AND So_WalkLetterResultNumbering = '{$prm['xnumber']}'
					";
					//echo $query;
			$rows = $this->db_onedev->query($query);
		}
		
		if($prm['xtype_code'] === 'DR'){
			$query ="UPDATE result_courierspk SET
						Result_CourierSPKM_CourierID = '{$prm['courier_change']['id']}'
					WHERE
						Result_CourierSPKID = '{$prm['xid']}' AND Result_CourierSPKNumbering = '{$prm['xnumber']}'
					";
					//echo $query;
			$rows = $this->db_onedev->query($query);
		}
		
		if($prm['xtype_code'] === 'EL'){
			//echo $prm['courier_change']['id'];
			$query = "	SELECT * 
						FROM m_courier 
						WHERE
						M_CourierID = '{$prm['courier_change']['id']}'";
			//echo $query;
			$m_staffid = $this->db_onedev->query($query)->row()->M_CourierM_StaffID;
			//echo $m_staffid;
			$query ="UPDATE t_receiverefdistributcourier SET
						T_ReceiverefDistributCourierM_StaffID = '{$m_staffid}'
					WHERE
						T_ReceiverefDistributCourierID = '{$prm['xid']}' AND T_ReceiverefDistributCourierNumber = '{$prm['xnumber']}'
					";
					//echo $query;
			$rows = $this->db_onedev->query($query);
		}
		
		if($prm['xtype_code'] === 'SJR'){
			$query = "	SELECT * 
						FROM m_courier 
						WHERE
						M_CourierID = '{$prm['courier_change']['id']}'";
			$m_staffid = $this->db_onedev->query($query)->row()->M_CourierM_StaffID;
			$query ="UPDATE t_ref_deliveryorder SET
						T_RefDeliveryOrderM_CourierID = '{$prm['courier_change']['id']}'
					WHERE
						T_RefDeliveryOrderID = '{$prm['xid']}' AND T_RefDeliveryOrderNumber = '{$prm['xnumber']}'
					";
					//echo $query;
			$rows = $this->db_onedev->query($query);
		}
		
		
		
		$result = array(
			"total" => 1 , 
			"records" => array('status'=>'OK')
		); 
		$this->sys_ok($result);
		exit;
	}
	
	function deletedoctor(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$query ="UPDATE m_doctor SET
					M_DoctorIsActive = 'N'
				WHERE
					M_DoctorID = '{$prm['M_DoctorID']}'
				";
				//echo $query;
		$rows = $this->db_onedev->query($query);
		
		$result = array(
			"total" => 1 , 
			"records" => array('status'=>'OK')
		); 
		$this->sys_ok($result);
		exit;
	}
	
	function getaddress(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$query ="	SELECT m_doctoraddress.*,
					M_KelurahanName,
					M_DistrictID,
					M_DistrictName,
					M_CityID,
					M_CityName,
					'' as action
					FROM m_doctoraddress
					JOIN m_kelurahan ON M_DoctorAddressM_KelurahanID = M_KelurahanID
					JOIN m_district ON M_KelurahanM_DistrictID = M_DistrictID
					JOIN m_city ON M_DistrictM_CityID = M_CityID
					WHERE	
						M_DoctorAddressIsActive = 'Y' AND M_DoctorAddressM_DoctorID = ?
				";
				//echo $query;
		$rows = $this->db_onedev->query($query,array($prm['id']))->result_array();
		if($rows){
			foreach($rows as $k => $v){				
				$rows[$k]['action'] = '<v-icon color="error" @click="deleteAddress(props.item)">delete</v-icon>';
				$rows[$k]['action'] .= '<v-icon color="primary" @click="deleteAddress(props.item)">edit</v-icon>';
				
			}
		}
		$result = array(
			"total" => count($rows) , 
			"records" => $rows, 
		); 
		$this->sys_ok($result);
		exit;
	}
	
	function savenewtask(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		$userid = $this->sys_user["M_UserID"];
		$numbering = $this->db_onedev->query("SELECT fn_numbering('AC') as numbering")->row()->numbering;
		$query ="INSERT INTO courier_adhoc (
						Courier_AdHocNumbering,
						Courier_AdHocDate,
						Courier_AdHocTime,
						Courier_AdHocM_CourierID,
						Courier_AdHocTaskLabel,
						Courier_AdHocTaskNote,
						Courier_AdHocM_KelurahanID,
						Courier_AdHocUserID,
						Courier_AdHocCreated
					)
					VALUES(
						'{$numbering}',
						'{$prm['xdate']}',
						'{$prm['xtime']}',
						'{$prm['courier']['id']}',
						'{$prm['xlabel']}',
						'{$prm['xnote']}',
						'{$prm['kelurahan']['id']}',
						{$userid},
						NOW()
					)
				";
				//echo $query;
		$rows = $this->db_onedev->query($query);
		
		$result = array(
			"total" => 1 , 
			"records" => array('status'=>'OK')
		); 
		$this->sys_ok($result);
		exit;
	}
	
	function getcourier(){
	   if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		
		
		$query ="	SELECT M_CourierID as id, M_StaffName as name
					FROM m_courier
					JOIN m_staff ON M_CourierM_StaffID = M_StaffID 
					WHERE	
						M_StaffIsActive = 'Y'
				";
				//echo $query;
		$rows = $this->db_onedev->query($query)->result_array();
		

		$result = array(
			"total" => count($rows) , 
			"records" => $rows, 
		); 
		$this->sys_ok($result);
		exit;
   }
	
	function saveeditaddress(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		
		$query ="UPDATE m_doctoraddress SET 
						M_DoctorAddressM_DoctorID = '{$prm['M_DoctorAddressM_DoctorID']}',
						M_DoctorAddressNote = '{$prm['M_DoctorAddressNote']}',
						M_DoctorAddressDescription = '{$prm['M_DoctorAddressDescription']}',
						M_DoctorAddressM_KelurahanID = '{$prm['M_DoctorAddressM_KelurahanID']}'
					WHERE
						M_DoctorAddressID = '{$prm['M_DoctorAddressID']}'
				";
				//echo $query;
		$rows = $this->db_onedev->query($query);
		
		$result = array(
			"total" => 1 , 
			"records" => array('status'=>'OK')
		); 
		$this->sys_ok($result);
		exit;
	}
	
	function deleteaddress(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		$prm = $this->sys_input;
		
		$query ="UPDATE m_doctoraddress SET 
						M_DoctorAddressIsActive = 'N'
					WHERE
						M_DoctorAddressID = '{$prm['M_DoctorAddressID']}'
				";
				//echo $query;
		$rows = $this->db_onedev->query($query);
		
		$result = array(
			"total" => 1 , 
			"records" => array('status'=>'OK')
		); 
		$this->sys_ok($result);
		exit;
	}
	
	function approve(){
		if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
		}
		
		$prm = $this->sys_input;
		$userid = $this->sys_user["M_UserID"];
		
		
		$query = "	INSERT management_courier (
						Management_CourierDate,
						Management_CourierType,
						Management_CourierReffID,
						Management_CourierReffNumbering,
						Management_CourierUserID,
						Management_CourierCreated
					)
					VALUES(
						NOW(),
						'{$prm['xtype_code']}',
						'{$prm['xid']}',
						'{$prm['xnumber']}',
						'{$userid}',
						NOW()
					)";
				//echo $query;
		$this->db_onedev->query($query);
		
		$result = array(
			"total" => 1 , 
			"records" => array('status'=>'OK')
		); 
		$this->sys_ok($result);
		exit;
	}

}