<?php

class Refdeliverysamplev6 extends MY_Controller
{
   var $db_onedev;
   public function index()
   {
      echo "REF DELIVERY SAMPLE API";
   }
   public function __construct()
   {
      parent::__construct();
      $this->db_onedev = $this->load->database("onedev", true);
   }
	
    function lookupdeliveryorderdetailbyname(){
        try {
            //# cek token valid
            if (! $this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
    
            $prm = $this->sys_input;
            $deliveryorder = $prm['deliveryorder'];
		$deliveryorderdetail = $prm['deliveryorderdetail'];;
            $all = $prm['all'];
            $limit = '';
            if($all == 'N'){
                $limit = ' LIMIT 10';
            }
            $number_limit = 10;
            $number_offset = ($prm['current_page'] - 1) * $number_limit ;
            $sql = "select COUNT(*) as total
			FROM(SELECT *
                    from t_ref_deliveryorder_detail 
                    JOIN t_ref_deliveryorder ON T_RefDeliveryOrderDetailT_RefDeliveryOrderID = T_RefDeliveryOrderID AND T_RefDeliveryOrderIsActive = 'Y'
                    JOIN t_orderheader ON T_RefDeliveryOrderDetailT_OrderHeaderID = T_OrderHeaderID
                    JOIN t_test ON T_RefDeliveryOrderDetailT_TestID = T_TestID
                    WHERE
                    T_RefDeliveryOrderDate = '{$deliveryorder}' AND
				T_OrderHeaderLabNumber LIKE CONCAT('%','{$deliveryorderdetail}','%') AND
				T_RefDeliveryOrderDetailIsActive = 'Y' GROUP BY T_RefDeliveryOrderDetailID) a";
          //  $total = $this->db_onedev->query($sql,$sql_param)->row()->total;
          $query = $this->db_onedev->query($sql);
          //echo $this->db_onedev->last_query();
          $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("t_ref_deliveryorder_detail count", $this->db_onedev);
             exit;
          }
            
            $sql = "select T_RefDeliveryOrderDetailID as id,
            T_OrderHeaderLabNumber,
            CONCAT(M_TitleName, ' ', M_PatientName) as M_PatientName,
            T_TestName,
            T_RefDeliveryOrderID,
            t_ref_deliveryorder_detail.*
                    from t_ref_deliveryorder_detail 
                    JOIN t_ref_deliveryorder ON T_RefDeliveryOrderDetailT_RefDeliveryOrderID = T_RefDeliveryOrderID AND T_RefDeliveryOrderIsActive = 'Y'
                    JOIN t_orderheader ON T_RefDeliveryOrderDetailT_OrderHeaderID = T_OrderHeaderID
                    JOIN t_test ON T_RefDeliveryOrderDetailT_TestID = T_TestID
                    WHERE
                        T_RefDeliveryOrderDate = '{$deliveryorder}' AND
				T_OrderHeaderLabNumber LIKE CONCAT('%','{$deliveryorderdetail}','%') AND
                        T_RefDeliveryOrderDetailIsActive = 'Y'
                        GROUP BY T_RefDeliveryOrderDetailID
                    ORDER BY T_RefDeliveryOrderID ASC, T_OrderHeaderLabNumber ASC
                    limit $number_limit offset $number_offset";
            $sql_param = array($search);
            $query = $this->db_onedev->query($sql);
            //echo $this->db_onedev->last_query();
            if ($query) {
                $rows = $query->result_array();
                
                
            } else {
              $this->sys_error_db("t_ref_deliveryorder_detail select");
              exit;
            }
    
            $result = array ("total" => $tot_page, "total_filter"=>count($rows),"records" => $rows);
            $this->sys_ok($result);
    
        } catch(Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function lookupdeliveryorderdetailbyid(){
        try {
            //# cek token valid
            if (! $this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
    
            $prm = $this->sys_input;
            $id = $prm['id'];
            $all = $prm['all'];
            $limit = '';
            if($all == 'N'){
                $limit = ' LIMIT 10';
            }
            $number_limit = 10;
            $number_offset = ($prm['current_page'] - 1) * $number_limit ;
            $sql = "select COUNT(*) as total
			FROM(SELECT *
                    from t_ref_deliveryorder_detail 
                    JOIN t_ref_deliveryorder ON T_RefDeliveryOrderDetailT_RefDeliveryOrderID = T_RefDeliveryOrderID AND T_RefDeliveryOrderIsActive = 'Y'
                    JOIN t_orderheader ON T_RefDeliveryOrderDetailT_OrderHeaderID = T_OrderHeaderID
                    JOIN t_test ON T_RefDeliveryOrderDetailT_TestID = T_TestID
                    WHERE
                    T_RefDeliveryOrderID = '{$id}' AND
				T_RefDeliveryOrderDetailIsActive = 'Y' GROUP BY T_RefDeliveryOrderDetailID) a";
          //  $total = $this->db_onedev->query($sql,$sql_param)->row()->total;
          $query = $this->db_onedev->query($sql);
         // echo $this->db_onedev->last_query();
          $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("t_ref_deliveryorder_detail count", $this->db_onedev);
             exit;
          }
            
            $sql = "select T_RefDeliveryOrderDetailID as id,
            T_OrderHeaderLabNumber,
            CONCAT(M_TitleName, ' ', M_PatientName) as M_PatientName,
            T_TestName,
            T_RefDeliveryOrderDetailT_OrderHeaderID,	
            T_RefDeliveryOrderDetailT_OrderDetailID,
            T_RefDeliveryOrderDetailT_TestID,	
            T_RefDeliveryOrderDetailResult,	
            T_RefDeliveryOrderDetailStatus,
            T_RefDeliveryOrderDetailPromise,
            'N' as ischoose,
            'N' as isnew
                        from t_ref_deliveryorder_detail 
                        JOIN t_ref_deliveryorder ON T_RefDeliveryOrderDetailT_RefDeliveryOrderID = T_RefDeliveryOrderID AND T_RefDeliveryOrderIsActive = 'Y'
                        JOIN t_orderheader ON T_RefDeliveryOrderDetailT_OrderHeaderID = T_OrderHeaderID
                        JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID
                        JOIN m_title ON M_PatientM_TitleID = M_TitleID
                        JOIN t_test ON T_RefDeliveryOrderDetailT_TestID = T_TestID
                        WHERE
                        T_RefDeliveryOrderID = '{$id}' AND
                        T_RefDeliveryOrderDetailIsActive = 'Y'
                        GROUP BY T_RefDeliveryOrderDetailID
                    ORDER BY T_RefDeliveryOrderID ASC, T_OrderHeaderLabNumber ASC
                    limit $number_limit offset $number_offset";
            $sql_param = array($search);
            $query = $this->db_onedev->query($sql);
            //echo $this->db_onedev->last_query();
            if ($query) {
                $rows = $query->result_array();
                
                
            } else {
              $this->sys_error_db("t_ref_deliveryorder_detail select");
              exit;
            }
    
            $result = array ("total" => $tot_page, "total_filter"=>count($rows),"records" => $rows);
            $this->sys_ok($result);
    
        } catch(Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function lookupdeliveryorderdetailbyworklistid(){
        try {
            //# cek token valid
            if (! $this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
    
            $prm = $this->sys_input;
            $id = $prm['id'];
            $all = $prm['all'];
            $limit = '';
            if($all == 'N'){
                $limit = ' LIMIT 10';
            }
            $number_limit = 10;
            $number_offset = ($prm['current_page'] - 1) * $number_limit ;
            $sql = "SELECT COUNT(*) as total
            FROM (select t_worklist_ref_confirmdetail.*, T_WorklistID, T_WorklistName,  T_OrderHeaderLabNumber,
                CONCAT(M_TitleName, ' ', M_PatientName) as M_PatientName,
                T_TestName
               from t_worklist_ref_confirmdetail 
               join t_test ON T_WorklistRefConfirmDetailT_TestID	 = T_TestID
               join nat_test ON T_TestNat_TestID = Nat_TestID
               join t_worklistdetailv2 on Nat_TestID  = T_WorklistDetailNat_TestID AND T_WorklistDetailIsActive = 'Y'
               join t_worklist ON T_WorklistDetailT_WorklistID = T_WorklistID
               JOIN t_orderheader ON T_WorklistRefConfirmDetailT_OrderHeaderID = T_OrderHeaderID
               JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID
               JOIN m_title ON M_PatientM_TitleID = M_TitleID            
               where 
               T_WorklistRefConfirmDetailIsActive = 'Y' AND T_WorklistID = '{$id}') a";
          //  $total = $this->db_onedev->query($sql,$sql_param)->row()->total;
          $query = $this->db_onedev->query($sql);
         // echo $this->db_onedev->last_query();
          $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("t_ref_deliveryorder_detail count", $this->db_onedev);
             exit;
          }
            
            $sql = "select T_WorklistRefConfirmDetailID as id,
            T_OrderHeaderLabNumber,
            CONCAT(M_TitleName, ' ', M_PatientName) as M_PatientName,
            T_TestName,
            T_WorklistRefConfirmDetailT_OrderHeaderID as T_RefDeliveryOrderDetailT_OrderHeaderID,	
            T_WorklistRefConfirmDetailT_OrderDetailID as T_RefDeliveryOrderDetailT_OrderDetailID,
            T_WorklistRefConfirmDetailT_TestID as T_RefDeliveryOrderDetailT_TestID,	
            T_WorklistRefConfirmDetailResult as T_RefDeliveryOrderDetailResult,	
            T_WorklistRefConfirmDetailStatus as T_RefDeliveryOrderDetailStatus,
            T_WorklistRefConfirmDetailPromise as T_RefDeliveryOrderDetailPromise,
            'N' as ischoose,
            'Y' as isnew
           from t_worklist_ref_confirmdetail 
           join t_test ON T_WorklistRefConfirmDetailT_TestID	 = T_TestID
           join nat_test ON T_TestNat_TestID = Nat_TestID
           join t_worklistdetailv2 on Nat_TestID  = T_WorklistDetailNat_TestID AND T_WorklistDetailIsActive = 'Y'
           join t_worklist ON T_WorklistDetailT_WorklistID = T_WorklistID
           JOIN t_orderheader ON T_WorklistRefConfirmDetailT_OrderHeaderID = T_OrderHeaderID
           JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID
           JOIN m_title ON M_PatientM_TitleID = M_TitleID            
           where 
           T_WorklistRefConfirmDetailIsActive = 'Y' AND T_WorklistID = '{$id}'
                        GROUP BY T_WorklistRefConfirmDetailID
                    ORDER BY T_WorklistRefConfirmDetailID ASC, T_OrderHeaderLabNumber ASC
                    limit $number_limit offset $number_offset";
            $sql_param = array($search);
            $query = $this->db_onedev->query($sql);
            //echo $this->db_onedev->last_query();
            if ($query) {
                $rows = $query->result_array();
                
                
            } else {
              $this->sys_error_db("t_ref_deliveryorder_detail select");
              exit;
            }
    
            $result = array ("total" => $tot_page, "total_filter"=>count($rows),"records" => $rows);
            $this->sys_ok($result);
    
        } catch(Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
   public function lookupdeliveryorderbyname()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
        }

        $prm = $this->sys_input;
        $deliveryorder = $prm['deliveryorder'];
        $sql = $this->db_onedev->query("SELECT M_BranchID, M_BranchName FROM m_branch
		WHERE M_BranchIsActive = 'Y' AND M_BranchIsDefault = 'Y'
		ORDER BY M_BranchID DESC LIMIT 1")->row();		
		$branchid = $sql->M_BranchID;
		$branchname = $sql->M_BranchName;
		$limit = '';
		if($all == 'N'){
			$limit = ' LIMIT 10';
        }
        $number_limit = 10;
        $number_offset = ($prm['current_page'] - 1) * $number_limit ;
		$sql = "select COUNT(*) as total
		FROM(SELECT *
                from t_ref_deliveryorder
                LEFT JOIN t_ref_deliveryorder_detail ON T_RefDeliveryOrderID = T_RefDeliveryOrderDetailT_RefDeliveryOrderID AND T_RefDeliveryOrderDetailIsActive = 'Y'
                LEFT JOIN t_orderheader ON T_RefDeliveryOrderDetailT_OrderHeaderID = T_OrderHeaderID
                where 
                T_RefDeliveryOrderDate = '{$deliveryorder}' AND
                T_RefDeliveryOrderIsActive = 'Y'
				GROUP BY T_RefDeliveryOrderID) a";
        $sql_param = array($search);
      //  $total = $this->db_onedev->query($sql,$sql_param)->row()->total;
      $query = $this->db_onedev->query($sql);
      $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("t_ref_deliveryorder count", $this->db_onedev);
         exit;
      }
		
        $sql = "SELECT * FROM(
        SELECT
        t_ref_deliveryorder.*, 
        T_RefDeliveryOrderID as id,
        'Internal' as isexternal,
        b.M_BranchID as M_BranchID,
        b.M_BranchName as M_BranchName,
        b.M_BranchAddress as M_BranchAddress,
        d.M_BranchID as M_DestinationID,
        d.M_BranchName as M_DestinationName,
        T_RefDeliveryOrderAddress as M_DestinationAddress,
        T_RefDeliveryOrderUp as M_DestinationUp,
        d.M_BranchName as destination,
        M_CityID,
        M_CityName,
        M_DistrictID,
        M_DistrictName,
        M_KelurahanID,
        M_KelurahanName,
        M_CourierID,
        M_StaffName,
        T_WorklistID,
        T_WorklistName,    
        CASE
    WHEN T_RefDeliveryOrderIsConfirm = 'N' THEN 'Belum Konfirmasi'
    WHEN T_RefDeliveryOrderIsConfirm = 'P' THEN 'Sebagian Konfirmasi'
    ELSE 'Semua Konfirmasi'
END as status,
        DATE_FORMAT(T_RefDeliveryOrderDate,'%d%m%Y') as startdate,
        DATE_FORMAT(T_RefDeliveryOrderDate,'%d-%m-%Y') as T_RefDeliveryOrderDatex
        FROM t_ref_deliveryorder 
                LEFT JOIN t_ref_deliveryorder_detail ON T_RefDeliveryOrderID = T_RefDeliveryOrderDetailT_RefDeliveryOrderID AND T_RefDeliveryOrderDetailIsActive = 'Y'
                LEFT JOIN m_branch b ON T_RefDeliveryOrderM_BranchID = b.M_BranchID
                LEFT JOIN t_worklist ON T_RefDeliveryOrderT_WorklistID = T_WorklistID
                LEFT JOIN m_branch d ON T_RefDeliveryOrderM_DestinationID = d.M_BranchID
                LEFT JOIN m_city ON d.M_BranchM_CityID = M_CityID
                LEFT JOIN m_district ON d.M_BranchM_DistrictID = M_DistrictID
                LEFT JOIN m_kelurahan ON d.M_BranchM_KelurahanID = M_KelurahanID
                LEFT JOIN m_courier ON T_RefDeliveryOrderM_CourierID = M_CourierID
                LEFT JOIN m_staff ON M_CourierM_StaffID = M_StaffID
                LEFT JOIN t_orderheader ON T_RefDeliveryOrderDetailT_OrderHeaderID = T_OrderHeaderID
                where 
                T_RefDeliveryOrderDate = '{$deliveryorder}' AND
                T_RefDeliveryOrderIsActive = 'Y' AND
                T_RefDeliveryOrderIsExternal = 'N'
                
        UNION SELECT
        t_ref_deliveryorder.*, 
        T_RefDeliveryOrderID as id,
        'External' as isexternal,
        M_BranchID,
        M_BranchName,
        M_BranchAddress,
        M_LabReferenceID as M_DestinationID,
        M_LabReferenceName as M_DestinationName,
        T_RefDeliveryOrderAddress as M_DestinationAddress,
        T_RefDeliveryOrderUp as M_DestinationUp,
        M_LabReferenceName as destination,
        M_CityID,
        M_CityName,
        M_DistrictID,
        M_DistrictName,
        M_KelurahanID,
        M_KelurahanName,
        M_CourierID,
        M_StaffName,
        T_WorklistID,
        T_WorklistName,            
        CASE
    WHEN T_RefDeliveryOrderIsConfirm = 'N' THEN 'Belum Konfirmasi'
    WHEN T_RefDeliveryOrderIsConfirm = 'P' THEN 'Sebagian Konfirmasi'
    ELSE 'Semua Konfirmasi'
END as status, 
        DATE_FORMAT(T_RefDeliveryOrderDate,'%d%m%Y') as startdate,
        DATE_FORMAT(T_RefDeliveryOrderDate,'%d-%m-%Y') as T_RefDeliveryOrderDatex
        FROM t_ref_deliveryorder 
                LEFT JOIN t_ref_deliveryorder_detail ON T_RefDeliveryOrderID = T_RefDeliveryOrderDetailT_RefDeliveryOrderID AND T_RefDeliveryOrderDetailIsActive = 'Y'
                LEFT JOIN m_branch ON T_RefDeliveryOrderM_BranchID = M_BranchID
                LEFT JOIN t_worklist ON T_RefDeliveryOrderT_WorklistID = T_WorklistID
                LEFT JOIN m_labreference d ON T_RefDeliveryOrderM_DestinationID = M_LabReferenceID
                LEFT JOIN m_city ON M_LabReferenceM_CityID = M_CityID
                LEFT JOIN m_district ON M_LabReferenceM_DistrictID = M_DistrictID
                LEFT JOIN m_kelurahan ON M_LabReferenceM_KelurahanID = M_KelurahanID
                LEFT JOIN m_courier ON T_RefDeliveryOrderM_CourierID = M_CourierID
                LEFT JOIN m_staff ON M_CourierM_StaffID = M_StaffID
                LEFT JOIN t_orderheader ON T_RefDeliveryOrderDetailT_OrderHeaderID = T_OrderHeaderID
                where 
                T_RefDeliveryOrderDate = '{$deliveryorder}' AND
                T_RefDeliveryOrderIsActive = 'Y' AND
                T_RefDeliveryOrderIsExternal = 'Y'
                ) a
                GROUP BY T_RefDeliveryOrderID
                ORDER BY T_RefDeliveryOrderID DESC";
                //limit $number_limit offset $number_offset;
        $sql_param = array($search);
        $query = $this->db_onedev->query($sql);
		//echo $this->db_onedev->last_query();
        if ($query) {
            $rows = $query->result_array();
			
            
        } else {
          $this->sys_error_db("t_ref_deliveryorder select");
          exit;
        }

        $result = array ("total" => $tot_page, "total_filter"=>count($rows),"records" => $rows, "branchid" => $branchid, "branchname" => $branchname);
        $this->sys_ok($result);

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   public function lookupdeliveryorderbybarcode()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
        }

        $prm = $this->sys_input;
        $deliveryorder = $prm['deliveryorder'];
        $barcode = $prm['search'];
        $sql = $this->db_onedev->query("SELECT M_BranchID, M_BranchName FROM m_branch
		WHERE M_BranchIsActive = 'Y' AND M_BranchIsDefault = 'Y'
		ORDER BY M_BranchID DESC LIMIT 1")->row();		
		$branchid = $sql->M_BranchID;
		$branchname = $sql->M_BranchName;
		$limit = '';
		if($all == 'N'){
			$limit = ' LIMIT 10';
        }
        $number_limit = 10;
        $number_offset = ($prm['current_page'] - 1) * $number_limit ;
		$sql = "select COUNT(*) as total
		FROM(SELECT *
                from t_ref_deliveryorder
                LEFT JOIN t_ref_deliveryorder_detail ON T_RefDeliveryOrderID = T_RefDeliveryOrderDetailT_RefDeliveryOrderID AND T_RefDeliveryOrderDetailIsActive = 'Y'
                LEFT JOIN t_orderheader ON T_RefDeliveryOrderDetailT_OrderHeaderID = T_OrderHeaderID
                where 
                T_RefDeliveryOrderDate = '{$deliveryorder}' AND
                LEFT(T_RefDeliveryOrderDetailT_BarcodeLabBarcode,12) = '{$barcode}' AND
                T_RefDeliveryOrderIsActive = 'Y'
                GROUP BY T_RefDeliveryOrderID) a";
           //     echo $sql;
        $sql_param = array($search);
      //  $total = $this->db_onedev->query($sql,$sql_param)->row()->total;
      $query = $this->db_onedev->query($sql);
      $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("t_ref_deliveryorder count", $this->db_onedev);
         exit;
      }
		
        $sql = "SELECT * FROM(
        SELECT
        t_ref_deliveryorder.*, 
        T_RefDeliveryOrderID as id,
        'Internal' as isexternal,
        b.M_BranchID as M_BranchID,
        b.M_BranchName as M_BranchName,
        b.M_BranchAddress as M_BranchAddress,
        d.M_BranchID as M_DestinationID,
        d.M_BranchName as M_DestinationName,
        T_RefDeliveryOrderAddress as M_DestinationAddress,
        T_RefDeliveryOrderUp as M_DestinationUp,
        d.M_BranchName as destination,
        M_CityID,
        M_CityName,
        M_DistrictID,
        M_DistrictName,
        M_KelurahanID,
        M_KelurahanName,
        M_CourierID,
        M_StaffName,
        T_WorklistID,
        T_WorklistName,    
        CASE
    WHEN T_RefDeliveryOrderIsConfirm = 'N' THEN 'Belum Konfirmasi'
    WHEN T_RefDeliveryOrderIsConfirm = 'P' THEN 'Sebagian Konfirmasi'
    ELSE 'Semua Konfirmasi'
END as status,
        DATE_FORMAT(T_RefDeliveryOrderDate,'%d%m%Y') as startdate,
        DATE_FORMAT(T_RefDeliveryOrderDate,'%d-%m-%Y') as T_RefDeliveryOrderDatex
        FROM t_ref_deliveryorder 
                LEFT JOIN t_ref_deliveryorder_detail ON T_RefDeliveryOrderID = T_RefDeliveryOrderDetailT_RefDeliveryOrderID AND T_RefDeliveryOrderDetailIsActive = 'Y'
                LEFT JOIN m_branch b ON T_RefDeliveryOrderM_BranchID = b.M_BranchID
                LEFT JOIN t_worklist ON T_RefDeliveryOrderT_WorklistID = T_WorklistID
                LEFT JOIN m_branch d ON T_RefDeliveryOrderM_DestinationID = d.M_BranchID
                LEFT JOIN m_city ON d.M_BranchM_CityID = M_CityID
                LEFT JOIN m_district ON d.M_BranchM_DistrictID = M_DistrictID
                LEFT JOIN m_kelurahan ON d.M_BranchM_KelurahanID = M_KelurahanID
                LEFT JOIN m_courier ON T_RefDeliveryOrderM_CourierID = M_CourierID
                LEFT JOIN m_staff ON M_CourierM_StaffID = M_StaffID
                LEFT JOIN t_orderheader ON T_RefDeliveryOrderDetailT_OrderHeaderID = T_OrderHeaderID
                where 
                T_RefDeliveryOrderDate = '{$deliveryorder}' AND
                LEFT(T_RefDeliveryOrderDetailT_BarcodeLabBarcode,12) = '{$barcode}' AND
                T_RefDeliveryOrderIsActive = 'Y' AND
                T_RefDeliveryOrderIsExternal = 'N'
                
        UNION SELECT
        t_ref_deliveryorder.*, 
        T_RefDeliveryOrderID as id,
        'External' as isexternal,
        M_BranchID,
        M_BranchName,
        M_BranchAddress,
        M_LabReferenceID as M_DestinationID,
        M_LabReferenceName as M_DestinationName,
        T_RefDeliveryOrderAddress as M_DestinationAddress,
        T_RefDeliveryOrderUp as M_DestinationUp,
        M_LabReferenceName as destination,
        M_CityID,
        M_CityName,
        M_DistrictID,
        M_DistrictName,
        M_KelurahanID,
        M_KelurahanName,
        M_CourierID,
        M_StaffName,
        T_WorklistID,
        T_WorklistName,            
        CASE
    WHEN T_RefDeliveryOrderIsConfirm = 'N' THEN 'Belum Konfirmasi'
    WHEN T_RefDeliveryOrderIsConfirm = 'P' THEN 'Sebagian Konfirmasi'
    ELSE 'Semua Konfirmasi'
END as status, 
        DATE_FORMAT(T_RefDeliveryOrderDate,'%d%m%Y') as startdate,
        DATE_FORMAT(T_RefDeliveryOrderDate,'%d-%m-%Y') as T_RefDeliveryOrderDatex
        FROM t_ref_deliveryorder 
                LEFT JOIN t_ref_deliveryorder_detail ON T_RefDeliveryOrderID = T_RefDeliveryOrderDetailT_RefDeliveryOrderID AND T_RefDeliveryOrderDetailIsActive = 'Y'
                LEFT JOIN m_branch ON T_RefDeliveryOrderM_BranchID = M_BranchID
                LEFT JOIN t_worklist ON T_RefDeliveryOrderT_WorklistID = T_WorklistID
                LEFT JOIN m_labreference d ON T_RefDeliveryOrderM_DestinationID = M_LabReferenceID
                LEFT JOIN m_city ON M_LabReferenceM_CityID = M_CityID
                LEFT JOIN m_district ON M_LabReferenceM_DistrictID = M_DistrictID
                LEFT JOIN m_kelurahan ON M_LabReferenceM_KelurahanID = M_KelurahanID
                LEFT JOIN m_courier ON T_RefDeliveryOrderM_CourierID = M_CourierID
                LEFT JOIN m_staff ON M_CourierM_StaffID = M_StaffID
                LEFT JOIN t_orderheader ON T_RefDeliveryOrderDetailT_OrderHeaderID = T_OrderHeaderID
                where 
                T_RefDeliveryOrderDate = '{$deliveryorder}' AND
                LEFT(T_RefDeliveryOrderDetailT_BarcodeLabBarcode,12) = '{$barcode}' AND
                T_RefDeliveryOrderIsActive = 'Y' AND
                T_RefDeliveryOrderIsExternal = 'Y'
                ) a
                GROUP BY T_RefDeliveryOrderID
                ORDER BY T_RefDeliveryOrderID DESC
                limit $number_limit offset $number_offset";
        $sql_param = array($search);
        $query = $this->db_onedev->query($sql);
		//echo $this->db_onedev->last_query();
        if ($query) {
            $rows = $query->result_array();
			
            
        } else {
          $this->sys_error_db("t_ref_deliveryorder select");
          exit;
        }

        $result = array ("total" => $tot_page, "total_filter"=>count($rows),"records" => $rows, "branchid" => $branchid, "branchname" => $branchname);
        $this->sys_ok($result);

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   function searchworklistold(){
    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 (select t_worklist_ref_confirmdetail.*, T_WorklistID, T_WorklistName
            from t_worklist_ref_confirmdetail 
            join t_test ON T_WorklistRefConfirmDetailT_TestID = T_TestID
            join nat_test ON T_TestNat_TestID = Nat_TestID
            join t_worklistdetailv2 on Nat_TestID  = T_WorklistDetailNat_TestID AND T_WorklistDetailIsActive = 'Y'
            join t_worklist ON T_WorklistDetailT_WorklistID = T_WorklistID
            where 
            T_WorklistRefConfirmDetailIsActive = 'Y' AND T_WorklistName like ? AND T_WorklistRefConfirmDetailIsDeliveryOrder = 'N' GROUP BY T_WorklistID) a
            ";
    $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_branch count",$this->db_onedev);
        exit;
    }

    $sql = "SELECT *
    FROM (select t_worklist_ref_confirmdetail.*, T_WorklistID, T_WorklistName
    from t_worklist_ref_confirmdetail 
    join t_test ON T_WorklistRefConfirmDetailT_TestID	 = T_TestID
    join nat_test ON T_TestNat_TestID = Nat_TestID
    join t_worklistdetailv2 on Nat_TestID  = T_WorklistDetailNat_TestID AND T_WorklistDetailIsActive = 'Y'
    join t_worklist ON T_WorklistDetailT_WorklistID = T_WorklistID
    where 
    T_WorklistRefConfirmDetailIsActive = 'Y' AND T_WorklistName like ? AND T_WorklistRefConfirmDetailIsDeliveryOrder = 'N') a
    GROUP BY T_WorklistID
    ORDER BY T_WorklistName ASC";
      $query = $this->db_onedev->query($sql, array($q['search']));
        
      if ($query) { 
         $rows = $query->result_array();
         //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_branch rows",$this->db_onedev);
         exit;
      }
}
function searchworklist(){
    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 t_worklist
            where 
            T_WorklistIsActive = 'Y' AND T_WorklistName like ?
            ";
    $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_branch count",$this->db_onedev);
        exit;
    }

    $sql = "SELECT *
    FROM t_worklist
    where 
    T_WorklistIsActive = 'Y' AND T_WorklistName like ?
    GROUP BY T_WorklistID
    ORDER BY T_WorklistName ASC";
      $query = $this->db_onedev->query($sql, array($q['search']));
        
      if ($query) { 
         $rows = $query->result_array();
         //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_branch rows",$this->db_onedev);
         exit;
      }
}
function searchcourier(){
    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_courier
            JOIN m_staff ON M_CourierM_StaffID = M_StaffID
            WHERE 
            M_StaffName like ?
            AND M_CourierIsActive = '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_branch count",$this->db_onedev);
        exit;
    }

    $sql = "SELECT * 
    FROM m_courier
    JOIN m_staff ON M_CourierM_StaffID = M_StaffID
    WHERE 
    M_StaffName like ?
    AND M_CourierIsActive = 'Y'
        ORDER BY M_StaffName ASC
      ";
      $query = $this->db_onedev->query($sql, array($q['search']));
        
      if ($query) { 
         $rows = $query->result_array();
         //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_branch rows",$this->db_onedev);
         exit;
      }
}
function searchbranch(){
    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_branch
            WHERE 
            M_BranchName like ?
            AND M_BranchIsActive = '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_branch count",$this->db_onedev);
        exit;
    }

    $sql = "SELECT * 
    FROM m_branch
    LEFT JOIN m_city ON M_BranchM_CityID = M_CityID
    LEFT JOIN m_district ON M_BranchM_DistrictID = M_DistrictID
    LEFT JOIN m_kelurahan ON M_BranchM_KelurahanID = M_KelurahanID
        WHERE 
        M_BranchName like ?
        AND M_BranchIsActive = 'Y'
        ORDER BY M_BranchName ASC
      ";
      $query = $this->db_onedev->query($sql, array($q['search']));
        
      if ($query) { 
         $rows = $query->result_array();
         //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_branch rows",$this->db_onedev);
         exit;
      }
}
function searchdestination(){
    if (! $this->isLogin) {
    $this->sys_error("Invalid Token");
    exit;
    }
    $prm = $this->sys_input;
    $tipe = $prm['tipe'];
    $max_rst = 12;
    $tot_count =0;

    $q = [
        'search'     => '%'
    ];

    if ($prm['search'] != '')
    {
        $q['search'] = "%{$prm['search']}%";  
    }

      // QUERY TOTAL
    if ($tipe == 'Internal'){
        $sql = "SELECT count(*) as total
        FROM
        (SELECT
        M_BranchID as M_DestinationID, 
        M_BranchName as M_DestinationName, 
        M_BranchAddress as M_DestinationAddress, 
        '' as M_DestinationUp,
        M_CityID, 
        M_CityName, 
        M_DistrictID, 
        M_DistrictName, 
        M_KelurahanID, 
        M_KelurahanName 
        FROM m_branch
        LEFT JOIN m_city ON M_BranchM_CityID = M_CityID
        LEFT JOIN m_district ON M_BranchM_DistrictID = M_DistrictID
        LEFT JOIN m_kelurahan ON M_BranchM_KelurahanID = M_KelurahanID
        WHERE 
        M_BranchName like ?
        AND M_BranchIsActive = 'Y' 
        ORDER BY M_BranchName ASC )a
                ";
        $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_branch count",$this->db_onedev);
            exit;
        }

        $sql = "SELECT
        M_BranchID as M_DestinationID, 
        M_BranchName as M_DestinationName, 
        M_BranchAddress as M_DestinationAddress, 
        '' as M_DestinationUp,
        M_CityID, 
        M_CityName, 
        M_DistrictID, 
        M_DistrictName, 
        M_KelurahanID, 
        M_KelurahanName 
        FROM m_branch
        LEFT JOIN m_city ON M_BranchM_CityID = M_CityID
        LEFT JOIN m_district ON M_BranchM_DistrictID = M_DistrictID
        LEFT JOIN m_kelurahan ON M_BranchM_KelurahanID = M_KelurahanID
        WHERE 
        M_BranchName like ?
        AND M_BranchIsActive = 'Y' 
        ORDER BY M_BranchName ASC";
          $query = $this->db_onedev->query($sql, array($q['search']));
            
          if ($query) { 
             $rows = $query->result_array();
             //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_branch rows",$this->db_onedev);
             exit;
          }
    }else{
        $sql = "SELECT count(*) as total
        FROM
        (SELECT
        M_LabReferenceID as M_DestinationID, 
        M_LabReferenceName as M_DestinationName, 
        M_LabReferenceAddress as M_DestinationAddress, 
        M_LabReferenceUp as M_DestinationUp,
        M_CityID, 
        M_CityName, 
        M_DistrictID, 
        M_DistrictName, 
        M_KelurahanID, 
        M_KelurahanName 
        FROM m_labreference
        LEFT JOIN m_city ON M_LabReferenceM_CityID = M_CityID
        LEFT JOIN m_district ON M_LabReferenceM_DistrictID = M_DistrictID
        LEFT JOIN m_kelurahan ON M_LabReferenceM_KelurahanID = M_KelurahanID
        WHERE 
        M_LabReferenceName like ?
        AND M_LabReferenceIsActive = 'Y' 
        ORDER BY M_LabReferenceName ASC)a
                ";
        $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_branch count",$this->db_onedev);
            exit;
        }

        $sql = "SELECT
        M_LabReferenceID as M_DestinationID, 
        M_LabReferenceName as M_DestinationName, 
        M_LabReferenceAddress as M_DestinationAddress, 
        M_LabReferenceUp as M_DestinationUp,
        M_CityID, 
        M_CityName, 
        M_DistrictID, 
        M_DistrictName, 
        M_KelurahanID, 
        M_KelurahanName 
        FROM m_labreference
        LEFT JOIN m_city ON M_LabReferenceM_CityID = M_CityID
        LEFT JOIN m_district ON M_LabReferenceM_DistrictID = M_DistrictID
        LEFT JOIN m_kelurahan ON M_LabReferenceM_KelurahanID = M_KelurahanID
        WHERE 
        M_LabReferenceName like ?
        AND M_LabReferenceIsActive = 'Y' 
        ORDER BY M_LabReferenceName ASC";
          $query = $this->db_onedev->query($sql, array($q['search']));
            
          if ($query) { 
             $rows = $query->result_array();
             //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_branch rows",$this->db_onedev);
             exit;
          }
    }
}
   function selected_sample()
   {
    try {
       //# cek token valid
       if (! $this->isLogin) {
          $this->sys_error("Invalid Token");
          exit;
       }
       $prm = $this->sys_input;
       $id = $prm['id'];
       $barcode = $prm['barcode'];
       $sql = $this->db_onedev->query("SELECT COUNT(*) as semua FROM t_ref_deliveryorder_detail
       WHERE T_RefDeliveryOrderDetailT_RefDeliveryOrderID = '{$id}' AND T_RefDeliveryOrderDetailIsActive = 'Y'")->row();		
       $semua = $sql->semua;

       $sql = $this->db_onedev->query("SELECT count(*) as terima FROM t_ref_deliveryorder_detail
       WHERE T_RefDeliveryOrderDetailT_RefDeliveryOrderID = '{$id}' AND T_RefDeliveryOrderDetailIsActive = 'Y' AND T_RefDeliveryOrderDetailIsConfirm <> 'N'")->row();		
       $terima = $sql->terima;
       $sql = $this->db_onedev->query("SELECT incomingRefIsConfirm as konfirmasi FROM incoming_ref
       WHERE incomingRefID = '{$id}'")->row();	
          $konfirmasi = $sql->konfirmasi;

       $sql = "SELECT *,IF(T_RefDeliveryOrderDetailIsConfirm = 'N' AND nosample = '{$barcode}', 'Y', T_RefDeliveryOrderDetailIsConfirm) as ischoose FROM (SELECT t_ref_deliveryorder_detail.*,T_RefDeliveryOrderDetailIsConfirm as ischoose, LEFT(T_RefDeliveryOrderDetailT_BarcodeLabBarcode,12) as nosample
          from t_ref_deliveryorder_detail 
          where 
          T_RefDeliveryOrderDetailIsActive = 'Y' AND T_RefDeliveryOrderDetailT_RefDeliveryOrderID = '{$id}' AND T_RefDeliveryOrderDetailT_BarcodeLabBarcode <> '') a
          GROUP BY nosample";
       $query = $this->db_onedev->query($sql);
       if ($query) {
          $rows = $query->result_array();
       } else {
          $this->sys_error_db( $this->db_onedev->last_query() );
          exit;
       }
       $result = array ("total" => count($rows), "records" => $rows,"semua" => $semua, 'terima' => $terima, "konfirmasi" => $konfirmasi);
       $this->sys_ok($result);

    } catch(Exception $exc) {
       $message = $exc->getMessage();
       $this->sys_error($message);
    }
 }
 function selected_samplebybarcode()
 {
  try {
     //# cek token valid
     if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
     }
     $prm = $this->sys_input;
     $id = $prm['id'];
     $barcode = $prm['barcode'];
     $sql = $this->db_onedev->query("SELECT COUNT(*) as semua FROM t_ref_deliveryorder_detail
     WHERE T_RefDeliveryOrderDetailT_RefDeliveryOrderID = '{$id}' AND T_RefDeliveryOrderDetailIsActive = 'Y'")->row();		
     $semua = $sql->semua;

     $sql = $this->db_onedev->query("SELECT count(*) as terima FROM t_ref_deliveryorder_detail
     WHERE T_RefDeliveryOrderDetailT_RefDeliveryOrderID = '{$id}' AND T_RefDeliveryOrderDetailIsActive = 'Y' AND T_RefDeliveryOrderDetailIsConfirm <> 'N'")->row();		
     $terima = $sql->terima;

     $sql = "SELECT *,IF(T_RefDeliveryOrderDetailIsConfirm = 'N' AND nosample = '{$barcode}', 'Y', T_RefDeliveryOrderDetailIsConfirm) as ischoose FROM (SELECT t_ref_deliveryorder_detail.*,T_RefDeliveryOrderDetailIsConfirm as ischoose, LEFT(T_RefDeliveryOrderDetailT_BarcodeLabBarcode,12) as nosample
     from t_ref_deliveryorder_detail 
     where 
     T_RefDeliveryOrderDetailIsActive = 'Y' AND T_RefDeliveryOrderDetailT_RefDeliveryOrderID = '{$id}' AND T_RefDeliveryOrderDetailT_BarcodeLabBarcode <> '') a
     GROUP BY nosample";
     $query = $this->db_onedev->query($sql);
     if ($query) {
        $rows = $query->result_array();
     } else {
        $this->sys_error_db( $this->db_onedev->last_query() );
        exit;
     }
     $result = array ("total" => count($rows), "records" => $rows,"semua" => $semua, 'terima' => $terima);
     $this->sys_ok($result);

  } catch(Exception $exc) {
     $message = $exc->getMessage();
     $this->sys_error($message);
  }
}
 function lookupsamplebyname()
 {
  try {
     //# cek token valid
     if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
     }
     $prm = $this->sys_input;
     $id = $prm['id'];
     $search = $prm['search'];
     $sql = $this->db_onedev->query("SELECT COUNT(*) as semua FROM t_ref_deliveryorder_detail
     WHERE T_RefDeliveryOrderDetailT_RefDeliveryOrderID = '{$id}' AND T_RefDeliveryOrderDetailIsActive = 'Y'")->row();		
     $semua = $sql->semua;

     $sql = $this->db_onedev->query("SELECT count(*) as terima FROM t_ref_deliveryorder_detail
     WHERE T_RefDeliveryOrderDetailT_RefDeliveryOrderID = '{$id}' AND T_RefDeliveryOrderDetailIsActive = 'Y' AND T_RefDeliveryOrderDetailStatus <> 'N'")->row();		
     $terima = $sql->terima;

     $sql = "SELECT * FROM (SELECT t_ref_deliveryorder_detail.*,'Y' as ischoose, LEFT(T_RefDeliveryOrderDetailT_BarcodeLabBarcode,12) as nosample
        from t_ref_deliveryorder_detail 
        where 
        T_RefDeliveryOrderDetailIsActive = 'Y' AND T_RefDeliveryOrderDetailT_RefDeliveryOrderID = '{$id}' AND LEFT(T_RefDeliveryOrderDetailT_BarcodeLabBarcode,12) = '{$search}' AND T_RefDeliveryOrderDetailT_BarcodeLabBarcode <> '') a
        GROUP BY nosample";
     $query = $this->db_onedev->query($sql);
     if ($query) {
        $rows = $query->result_array();
     } else {
        $this->sys_error_db( $this->db_onedev->last_query() );
        exit;
     }
     $result = array ("total" => count($rows), "records" => $rows,"semua" => $semua, 'terima' => $terima);
     $this->sys_ok($result);

  } catch(Exception $exc) {
     $message = $exc->getMessage();
     $this->sys_error($message);
  }
}
   public function addnewdeliveryorder()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
        $userid = $this->sys_user["M_UserID"];
        $numbering = $this->db_onedev->query("SELECT fn_numbering('RDO') as numbering")->row()->numbering;
        $sdate = date('Y-m-d',strtotime($prm['sdate']));    
        $isexternal = $prm['isexternal'];
        $branchid = $prm['branchid'];
        $destinationid = $prm['destinationid'];
        $worklistid = $prm['worklistid'];
        $address = $prm['address'];
        $up = $prm['up'];
        $courierid = $prm['courierid'];
        $cityid = $prm['cityid'];
        $districtid = $prm['districtid'];
        $kelurahanid = $prm['kelurahanid'];
            $sql = "insert into t_ref_deliveryorder(
                T_RefDeliveryOrderNumber,	
                T_RefDeliveryOrderDate,
                T_RefDeliveryOrderIsExternal,
                T_RefDeliveryOrderM_BranchID,	
                T_RefDeliveryOrderAddress,	
                T_RefDeliveryOrderUp,	
                T_RefDeliveryOrderM_DestinationID,	
                T_RefDeliveryOrderT_WorklistID,
                T_RefDeliveryOrderUserID,
                T_RefDeliveryOrderM_CourierID,	
                T_RefDeliveryOrderM_CityID,	
                T_RefDeliveryOrderM_DistrictID,	
                T_RefDeliveryOrderM_KelurahanID,
                T_RefDeliveryOrderCreated,	
                T_RefDeliveryOrderLastUpdated
				) 
				values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), now())";
			$query = $this->db_onedev->query($sql,
				array(
                    $numbering,
                    $sdate,
                    $isexternal,
                    $branchid,
                    $address,
                    $up,
                    $destinationid,
                    $worklistid,
                    $userid,
                    $courierid,
                    $cityid,
                    $districtid,
                    $kelurahanid
				)
			);
			//echo $query;
			if (!$query) {
				$this->sys_error_db("t_ref_deliveryorder insert");
				exit;
            } 
            $last_id = $this->db_onedev->insert_id();
            $sql = "CALL `sp_refdeliveryorderdetail_add`(".$last_id.",".$worklistid.",".$userid.")";					
            $query = $this->db_onedev->query($sql);
            
            if (!$query) {
                $this->sys_error_db("t_ref_deliveryorder_detail cash insert");
                exit;
            }
			$result = array ("total" => 1, "records" => array("xid" => 0));
            $this->sys_ok($result);
            $last_id = $this->db_onedev->insert_id();
        
            
        
            
    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   
   public function editdeliveryorder()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
        $id = $prm['id'];
		$sdate = date('Y-m-d',strtotime($prm['sdate']));    
        $isexternal = $prm['isexternal'];
        $branchid = $prm['branchid'];
        $destinationid = $prm['destinationid'];
        $address = $prm['address'];
        $up = $prm['up'];
        $courierid = $prm['courierid'];
        $cityid = $prm['cityid'];
        $districtid = $prm['districtid'];
        $kelurahanid = $prm['kelurahanid'];
        $userid = $this->sys_user["M_UserID"];
            $sqlcompany = "update t_ref_deliveryorder SET 
            T_RefDeliveryOrderDate = ?,
            T_RefDeliveryOrderIsExternal = ?,
            T_RefDeliveryOrderM_BranchID = ?,
            T_RefDeliveryOrderM_DestinationID = ?,
            T_RefDeliveryOrderAddress = ?,
            T_RefDeliveryOrderUp = ?,
            T_RefDeliveryOrderUserID = ?,
            T_RefDeliveryOrderM_CourierID = ?,	
            T_RefDeliveryOrderM_CityID = ?,
            T_RefDeliveryOrderM_DistrictID = ?,
            T_RefDeliveryOrderM_KelurahanID = ?,
            T_RefDeliveryOrderLastUpdated = now()
                where
                T_RefDeliveryOrderID = ?
                ";
            $querycompany = $this->db_onedev->query($sqlcompany,
                array(
                    $sdate,
                    $isexternal,
                    $branchid,
                    $destinationid,
                    $address,
                    $up,
                    $userid,
                    $courierid,
                    $cityid,
                    $districtid,
                    $kelurahanid,
                  $id
                )
            );
          //  echo $query;
            if (!$querycompany) {
                $this->sys_error_db("t_ref_deliveryorder update");
                exit;
            } 
            $result = array ("total" => 1, "records" => array("xid" => $id));
            $this->sys_ok($result);  
        
           
    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   function confirmdeliveryorder(){
    if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
    }
    $prm = $this->sys_input;
    $userid = $this->sys_user["M_UserID"];
    $query ="UPDATE t_ref_deliveryorder SET T_RefDeliveryOrderM_CourierID = '{$prm['courierid']}',
    T_RefDeliveryOrderIsConfirm = 'P'
    WHERE T_RefDeliveryOrderID = '{$prm['id']}'";
         //   echo $query;
    $rows = $this->db_onedev->query($query);
    foreach($prm['dodetail'] as $k=>$v){
            $query = "UPDATE t_ref_deliveryorder_detail SET
            T_RefDeliveryOrderDetailIsConfirm = ?,
            T_RefDeliveryOrderDetailConfirmUserID = ?,
            T_RefDeliveryOrderDetailConfirmDate = now()
        WHERE T_RefDeliveryOrderDetailIsActive = 'Y' AND T_RefDeliveryOrderDetailT_RefDeliveryOrderID = ? AND LEFT(T_RefDeliveryOrderDetailT_BarcodeLabBarcode,12) = ?";
        $insert_new_test = $this->db_onedev->query($query,array(
                                            $v['ischoose'],
                                            $userid,
                                            $prm['id'],
                                            $v['nosample']
                                        ));
        
    }	
    			
    $result = array(
        "total" => 1 , 
        "records" => array('status'=>'OK')
    ); 
    $sql = $this->db_onedev->query("SELECT count(*) as jml FROM t_ref_deliveryorder_detail
    WHERE T_RefDeliveryOrderDetailIsActive = 'Y' AND T_RefDeliveryOrderDetailT_RefDeliveryOrderID = '{$id}'")->row();		
    $jml = $sql->jml;

    $sql = $this->db_onedev->query("SELECT count(*) as jmlconfirm FROM t_ref_deliveryorder_detail
    WHERE T_RefDeliveryOrderDetailIsActive = 'Y' AND T_RefDeliveryOrderDetailIsConfirm = 'Y' AND T_RefDeliveryOrderDetailT_RefDeliveryOrderID = '{$id}'")->row();		
    $jmlconfirm = $sql->jmlconfirm;

    if($jml == $jmlconfirm){
        $isconfirm = 'Y';
    }else{
        $isconfirm = 'P';
    }
    $query ="UPDATE t_ref_deliveryorder SET T_RefDeliveryOrderIsConfirm = IF($jml = $jmlconfirm, 'Y', 'P')
    WHERE T_RefDeliveryOrderID = '{$prm['id']}'";
    $rows = $this->db_onedev->query($query);
    $this->sys_ok($result);
    exit;
}
function tolak_all(){
    if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
    }
    $prm = $this->sys_input;
    $userid = $this->sys_user["M_UserID"];
    $query ="UPDATE t_ref_deliveryorder SET T_RefDeliveryOrderM_CourierID = '{$prm['courierid']}',
    T_RefDeliveryOrderIsConfirm = 'S'
    WHERE T_RefDeliveryOrderID = '{$prm['id']}'";
         //   echo $query;
    $rows = $this->db_onedev->query($query);
    foreach($prm['dodetail'] as $k=>$v){
            $query = "UPDATE t_ref_deliveryorder_detail SET
            T_RefDeliveryOrderDetailIsConfirm = 'N',
            T_RefDeliveryOrderDetailConfirmUserID = ?,
            T_RefDeliveryOrderDetailConfirmDate = now()
        WHERE T_RefDeliveryOrderDetailIsActive = 'Y' AND T_RefDeliveryOrderDetailT_RefDeliveryOrderID = ? AND LEFT(T_RefDeliveryOrderDetailT_BarcodeLabBarcode,12) = ?";
        $insert_new_test = $this->db_onedev->query($query,array(
                                            $userid,
                                            $prm['id'],
                                            $v['nosample']
                                        ));
        
    }	
    			
    $result = array(
        "total" => 1 , 
        "records" => array('status'=>'OK')
    ); 
    $this->sys_ok($result);
    exit;
}
   public function addnewdeliveryorderdetail()
   {
      try {
         //# cek token valid
         if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
         }

         //# ambil parameter input
         $prm = $this->sys_input;
         $deliveryorderid = $prm['deliveryorderid'];
         $code = $prm['code'];
         $name = $prm['name'];
         $address = $prm['address'];
         $phone = $prm['phone'];
         $cityid = $prm['cityid'];
         $districtid = $prm['districtid'];
         $kelurahanid = $prm['kelurahanid'];
         $ipadrress = $prm['ipaddress'];
         $basename = $prm['basename'];
         $isdefault = $prm['isdefault'];
         $userid = $this->sys_user["M_UserID"];

         if($deliveryorderid == 0 || $cityid == 0 || $districtid == 0 || $kelurahanid == 0){
            $errors = array(); 
            if($deliveryorderid == 0){
            array_push($errors,array('field'=>'deliveryorder','msg'=>'Regional dipilih dulu dong'));  
            }
            if($cityid == 0){
                array_push($errors,array('field'=>'city','msg'=>'Kota dipilih dulu dong')); 
             } 
             if($districtid == 0){
                array_push($errors,array('field'=>'district','msg'=>'Kecamatan dipilih dulu dong'));  
             }  
             if($kelurahanid == 0){
                array_push($errors,array('field'=>'kelurahan','msg'=>'Kelurahan dipilih dulu dong'));
             }           
            $result = array ("total" => -1,"errors" => $errors, "records" => 0);
            $this->sys_ok($result);
         }else{           
            if($prm['xid'] == 0){
                $query = "SELECT COUNT(*) as exist FROM t_ref_deliveryorder_detail WHERE T_RefDeliveryOrderDetailIsActive = 'Y' AND T_RefDeliveryOrderDetailCode = '{$code}'";
                $exist_code =  $this->db_onedev->query($query)->row()->exist; 
                if($exist_code == 0){
                    $sql = "insert into t_ref_deliveryorder_detail(
                        T_RefDeliveryOrderDetailT_RefDeliveryOrderID,	
                        T_RefDeliveryOrderDetailCode,	
                        T_OrderHeaderLabNumber,
                        T_RefDeliveryOrderDetailAddress,
                        T_RefDeliveryOrderDetailPhone,
                        T_RefDeliveryOrderDetailM_CityID,	
                        T_RefDeliveryOrderDetailM_DistrictID,
                        T_RefDeliveryOrderDetailM_KelurahanID,
                        T_RefDeliveryOrderDetailIPAddress,	
                        T_RefDeliveryOrderDetailBaseName,
                        T_RefDeliveryOrderDetailIsDefault,
                        T_RefDeliveryOrderDetailUserID,
                        T_RefDeliveryOrderDetailLastUpdated,
                        T_RefDeliveryOrderDetailCreated)
                     values(?,?,?,?,?,?,?,?,?,?,?,?,now(),now())";
        $query = $this->db_onedev->query($sql,
        array(
            $deliveryorderid,
            $code,
            $name,
            $address,
            $phone,
            $cityid,
            $districtid,
            $kelurahanid,
            $ipadrress,
            $basename,
            $isdefault,
            $userid
        )
        );
        if (!$query) {
        $this->sys_error_db("t_ref_deliveryorder_detail insert",$this->db_onedev);
        exit;
        }
        $last_id = $this->db_onedev->insert_id();
        if ($isdefault == "Y" ) {
            $sql = "update t_ref_deliveryorder_detail set T_RefDeliveryOrderDetailIsDefault= 'N'
               where T_RefDeliveryOrderDetailID <> ? ";
            $this->db_onedev->query($sql,array($last_id));
         }
        $result = array ("total" => 1, "records" => array("xid" => 0));
        $this->sys_ok($result);
                }else{
                    $errors = array(); 
                    if($exist_code != 0){
                        array_push($errors,array('field'=>'code','msg'=>'Kode sudah ada yang pakai dong'));
                    }            
                    $result = array ("total" => -1,"errors" => $errors, "records" => 0);
                    $this->sys_ok($result);
                }          
                            
          }else{
            $query = "SELECT COUNT(*) as exist FROM t_ref_deliveryorder_detail WHERE T_RefDeliveryOrderDetailIsActive = 'Y' AND T_RefDeliveryOrderDetailCode = '{$code}' AND T_RefDeliveryOrderDetailID <> {$prm['xid']}";
            //echo $query;
            $exist_code =  $this->db_onedev->query($query)->row()->exist;
            // echo $exist_code;
             if($exist_code == 0){
                $sql = "UPDATE t_ref_deliveryorder_detail SET T_RefDeliveryOrderDetailT_RefDeliveryOrderID = '{$deliveryorderid}',
                T_RefDeliveryOrderDetailCode = '{$code}',
                T_OrderHeaderLabNumber = '{$name}',
                T_RefDeliveryOrderDetailAddress = '{$address}',
                T_RefDeliveryOrderDetailPhone = '{$phone}',
               T_RefDeliveryOrderDetailM_CityID = '{$cityid}',	
               T_RefDeliveryOrderDetailM_DistrictID = '{$districtid}',
               T_RefDeliveryOrderDetailM_KelurahanID = '{$kelurahanid}',
               T_RefDeliveryOrderDetailIPAddress = '{$ipadrress}',	
               T_RefDeliveryOrderDetailBaseName = '{$basename}',
               T_RefDeliveryOrderDetailIsDefault = '{$isdefault}',
               T_RefDeliveryOrderDetailUserID = '{$userid}'
                WHERE T_RefDeliveryOrderDetailID = '{$prm['xid']}'";
               //echo $sql;
              $query = $this->db_onedev->query($sql);
              if ($isdefault == "Y" ) {
               $sql = "update t_ref_deliveryorder_detail set T_RefDeliveryOrderDetailIsDefault= 'N'
                  where T_RefDeliveryOrderDetailID <> ? ";
               $this->db_onedev->query($sql,array($prm['xid']));
            }
              $result = array ("total" => 1, "records" => array("xid" => 0));
              $this->sys_ok($result);
             }else{
                $errors = array(); 
                if($exist_code != 0){
                    array_push($errors,array('field'=>'code','msg'=>'Kode sudah ada yang pakai dong'));
                }            
                $result = array ("total" => -1,"errors" => $errors, "records" => 0);
                $this->sys_ok($result);
            }
                 
             
          }
         }
         

      } catch(Exception $exc) {
         $message = $exc->getMessage();
         $this->sys_error($message);
      }
   }
   
   public function deletedeliveryorder()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
        $userid = $this->sys_user["M_UserID"];
        $id = $prm['id'];
        $sql = "update t_ref_deliveryorder SET 
        T_RefDeliveryOrderIsActive = 'N',
        T_RefDeliveryOrderLastUpdated = now()
				WHERE
				T_RefDeliveryOrderID = ?
            
				";

            $query = $this->db_onedev->query($sql,
                array(
                    $prm['id']
                )
            );
           // echo $query;
            if (!$query) {
                $this->sys_error_db("t_ref_deliveryorder delete");
                exit;
            } 
            $sql = "update t_ref_deliveryorder_detail SET 
            T_RefDeliveryOrderDetailIsActive = 'N',
            T_RefDeliveryOrderDetailLastUpdated = now()
                    WHERE
                    T_RefDeliveryOrderDetailT_RefDeliveryOrderID = ?
                
                    ";
    
                $query = $this->db_onedev->query($sql,
                    array(
                        $prm['id']
                    )
                );
               // echo $query;
                if (!$query) {
                    $this->sys_error_db("t_ref_deliveryorder_detail delete");
                    exit;
                } 
                $sql = "CALL `sp_refdeliveryorderdetail_delete`(".$id.",".$userid.")";					
                $query = $this->db_onedev->query($sql);
                
                if (!$query) {
                    $this->sys_error_db("t_ref_deliveryorder_detail delete");
                    exit;
                }
                    
        $result = array ("total" => 1, "records" => array("xid" => 0));
        $this->sys_ok($result);

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   public function deletedeliveryorderdetail()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
        $userid = $this->sys_user["M_UserID"];
            $sql = "update t_ref_deliveryorder_detail SET 
            T_RefDeliveryOrderDetailIsActive = 'N',
            T_RefDeliveryOrderDetailLastUpdated = now()
                    WHERE
                    T_RefDeliveryOrderDetailID = ?
                
                    ";
    
                $query = $this->db_onedev->query($sql,
                    array(
                        $prm['id']
                    )
                );
               // echo $query;
                if (!$query) {
                    $this->sys_error_db("t_ref_deliveryorder_detail delete");
                    exit;
                } 

               
        $result = array ("total" => 1, "records" => array("xid" => 0));
        $this->sys_ok($result);

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
  
    function searchdeliveryorder(){
        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 t_ref_deliveryorder
                WHERE 
                T_RefDeliveryOrderName like ?
                AND T_RefDeliveryOrderIsActive = '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("t_ref_deliveryorder count",$this->db_onedev);
            exit;
        }
    
        $sql = "
        SELECT T_RefDeliveryOrderID, T_RefDeliveryOrderName
        FROM t_ref_deliveryorder
            WHERE 
            T_RefDeliveryOrderName like ?
            AND T_RefDeliveryOrderIsActive = 'Y'
            ORDER BY T_RefDeliveryOrderName ASC
          ";
          $query = $this->db_onedev->query($sql, array($q['search']));
            
          if ($query) { 
             $rows = $query->result_array();
             //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("t_ref_deliveryorder rows",$this->db_onedev);
             exit;
          }
    }
    function searchdeliveryorderbyname(){
        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 t_ref_deliveryorder
                WHERE 
                T_RefDeliveryOrderName like ?
                AND T_RefDeliveryOrderIsActive = '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("t_ref_deliveryorder count",$this->db_onedev);
            exit;
        }
    
        $sql = "
        SELECT T_RefDeliveryOrderID, T_RefDeliveryOrderName
        FROM t_ref_deliveryorder
            WHERE 
            T_RefDeliveryOrderName like ?
            AND T_RefDeliveryOrderIsActive = 'Y'
            ORDER BY T_RefDeliveryOrderName ASC
          ";
          $query = $this->db_onedev->query($sql, array($q['search']));
            
          if ($query) { 
             $rows = $query->result_array();
             //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("t_ref_deliveryorder rows",$this->db_onedev);
             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 *
            FROM m_city
            WHERE 
            M_CityName like ?
            AND M_CityIsActive = 'Y'
            ORDER BY M_CityName ASC
          ";
          $query = $this->db_onedev->query($sql, array($q['search']));
            
          if ($query) { 
             $rows = $query->result_array();
             //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 searchdistrict(){
    if (! $this->isLogin) {
    $this->sys_error("Invalid Token");
    exit;
    }
    $prm = $this->sys_input;
    $id = $prm['id'];

    $max_rst = 12;
    $tot_count =0;

    $q = [
        'search'     => '%'
    ];

    if ($prm['search'] != '')
    {
        $q['search'] = "%{$prm['search']}%";  
    }

      // QUERY TOTAL
    $sql = "SELECT count(*) as total
            FROM m_district
            WHERE 
            M_DistrictName like ?
            AND M_DistrictM_CityID = '{$id}'
            AND M_DistrictIsActive = '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_district count",$this->db_onedev);
        exit;
    }

    $sql = "
        SELECT *
        FROM m_district
        WHERE 
        M_DistrictName like ?
        AND M_DistrictM_CityID = '{$id}'
        AND M_DistrictIsActive = 'Y'
        ORDER BY M_DistrictName ASC
      ";
      $query = $this->db_onedev->query($sql, array($q['search']));
        
      if ($query) { 
         $rows = $query->result_array();
         //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_district rows",$this->db_onedev);
         exit;
      }
}
function searchkelurahan(){
    if (! $this->isLogin) {
    $this->sys_error("Invalid Token");
    exit;
    }
    $prm = $this->sys_input;
    $id = $prm['id'];

    $max_rst = 12;
    $tot_count =0;

    $q = [
        'search'     => '%'
    ];

    if ($prm['search'] != '')
    {
        $q['search'] = "%{$prm['search']}%";  
    }

      // QUERY TOTAL
    $sql = "SELECT count(*) as total
            FROM m_kelurahan
            WHERE 
            M_KelurahanName like ?
            AND M_KelurahanM_DistrictID = '{$id}'
            AND M_KelurahanIsActive = '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_district count",$this->db_onedev);
        exit;
    }

    $sql = "
        SELECT *
        FROM m_kelurahan
        WHERE 
        M_KelurahanName like ?
        AND M_KelurahanM_DistrictID = '{$id}'
        AND M_KelurahanIsActive = 'Y'
        ORDER BY M_KelurahanName ASC
      ";
      $query = $this->db_onedev->query($sql, array($q['search']));
        
      if ($query) { 
         $rows = $query->result_array();
         //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_district rows",$this->db_onedev);
         exit;
      }
}

   
}