<?php
class Supervisor extends MY_Controller
{
   var $db_onedev;
   public function index()
   {
      echo "Patient API";
   }
   public function __construct()
   {
      parent::__construct();
      $this->db_onedev = $this->load->database("onedev", true);
   }		
		

   function read()
   {
      //# cek token valid
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }

      //# ambil parameter input
      $xuserid = $this->sys_user['M_UserID'];
      $prm = $this->sys_input;
      $id = $prm['id'];
      $msg = $prm['msg'];
      $type = $prm['type'];
      $orderid = $prm['orderid'];
      $sql = "UPDATE t_ordermessage SET T_OrderMessageRead = 'Y', T_OrderMessageReadUserID = '{$xuserid}', T_OrderMessageLastUpdated = NOW() WHERE T_OrderMessageID = {$id}";
      //echo $sql;
      $query = $this->db_onedev->query($sql);
      if (!$query) {
         $this->sys_error_db("t_ordermessage update");
         exit;
      } 

      $sql = "INSERT INTO t_ordermessage(
         T_OrderMessageReffID,
         T_OrderMessageT_OrderHeaderID,
         T_OrderMessageType,
         T_OrderMessageMessage,
         T_OrderMessageFromUserID,
         T_OrderMessageCreated,
         T_OrderMessageLastUpdated
      )
      VALUES(
         ?,
         ?,
         ?,
         ?,
         ?,
         now(),
         now()
      )";
      //echo $sql;
      $query = $this->db_onedev->query($sql,array(
         $id,
         $orderid,
         $type.'.reply',
         $msg,
         $xuserid
      ));
      if (!$query) {
         $this->sys_error_db("t_ordermessage insert");
         exit;
      } 

      $result = array(
         "total" => 1 , 
         "records" => array('prm'=>$prm)
      ); 
      $this->sys_ok($result);
      exit;
   }
   
    function removealias()
   {
      //# cek token valid
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }


      $rst = array('status'=>'OK');
      //# ambil parameter input
      $xuserid = $this->sys_user['M_UserID'];
      $prm = $this->sys_input;
      $id = $prm['id'];
      $orderid = $prm['orderid'];

      $sql = "UPDATE t_orderheaderaddon SET T_OrderHeaderAddOnAliasDoctorName = '', T_OrderHeaderAddOnAliasDoctorAddress = ''
				WHERE
				T_OrderHeaderAddOnT_OrderHeaderID = {$orderid}";
         //echo $sql;
         $query = $this->db_onedev->query($sql);
         if (!$query) {
            $this->sys_error_db("t_ordermessage update");
            exit;
         } 
		 
		 $sql = "UPDATE t_orderheaderaddon SET T_OrderHeaderAddOnAliasDoctorName = '', T_OrderHeaderAddOnAliasDoctorAddress = ''
				WHERE
				T_OrderHeaderAddOnAliasDoctorName = '-'";
         //echo $sql;
         $query = $this->db_onedev->query($sql);
         if (!$query) {
            $this->sys_error_db("t_ordermessage update");
            exit;
         } 

      $result = array(
         "total" => 1 , 
         "records" => $rst
      ); 
      $this->sys_ok($result);
      exit;
   }

   function actvoid()
   {
      //# cek token valid
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }


      $rst = array('status'=>'OK');
      //# ambil parameter input
      $xuserid = $this->sys_user['M_UserID'];
      $prm = $this->sys_input;
      $id = $prm['id'];
      $msg = $prm['msg'];
      $type = $prm['type'];
      $orderid = $prm['orderid'];

      $sql = "SELECT COUNT(*) as xcount FROM t_orderdetail WHERE T_OrderDetailT_OrderHeaderID = {$orderid} AND T_OrderDetailIsActive = 'Y'";
      //echo $sql;
      $xcount = $this->db_onedev->query($sql)->row()->xcount;
      if($xcount == 0){
         $sql = "UPDATE t_ordermessage SET T_OrderMessageIsActive = 'N', T_OrderMessageLastUpdated = NOW() WHERE T_OrderMessageID = {$id}";
         //echo $sql;
         $query = $this->db_onedev->query($sql);
         if (!$query) {
            $this->sys_error_db("t_ordermessage update");
            exit;
         } 

         $sql = "UPDATE t_orderheader SET 
            T_OrderHeaderIsActive = 'N', 
            T_OrderHeaderUserID = {$xuserid}, 
            T_OrderHeaderLastUpdated = NOW() 
            WHERE 
            T_OrderHeaderID = {$orderid}";
         //echo $sql;
         $query = $this->db_onedev->query($sql);
         if (!$query) {
            $this->sys_error_db("t_orderheader update");
            exit;
         } 

         $sql = " INSERT INTO order_attr (
            orderAttrT_OrderHeaderID,
            orderAttrType,
            orderAttrJson,
            orderAttrUserID
         )
         VALUES(
            {$orderid},
            'spv_order_void',
            '{$msg}',
            {$xuserid}
         )";
         $query = $this->db_onedev->query($sql);
         if (!$query) {
            $this->sys_error_db("order_attr insert");
            exit;
         } 
      }
      else{
         $rst = array('status'=>'NOTOK');
      }


      $result = array(
         "total" => 1 , 
         "records" => $rst
      ); 
      $this->sys_ok($result);
      exit;
   }

   function verify()
   {
      //# cek token valid
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }

      //# ambil parameter input
      $xuserid = $this->sys_user['M_UserID'];
      $prm = $this->sys_input;
      $id = $prm['id'];
      $msg = $prm['msg'];
      $type = $prm['type'];
      $orderid = $prm['orderid'];
      $sql = "UPDATE t_ordermessage SET  
         T_OrderMessageRead = 'Y',
         T_OrderMessageDone = 'Y', 
         T_OrderMessageDoneNote = '{$msg}',
         T_OrderMessageDoneUserID = {$xuserid}, 
         T_OrderMessageLastUpdated = NOW() 
         WHERE 
         T_OrderMessageID = {$id}";
      //echo $sql;
      $query = $this->db_onedev->query($sql);
      if (!$query) {
         $this->sys_error_db("t_ordermessage update");
         exit;
      } 

      $sql = "insert into fo_status(
         Fo_StatusDate,
         Fo_StatusT_OrderHeaderID, 
         Fo_StatusM_StatusID, 
         Fo_StatusM_UserID,
         Fo_StatusCreated,
         Fo_StatusUpdated) 
         values( now(), ?, ?, ?, now(),now())";
      //echo $sql; 
      $query = $this->db_onedev->query($sql,
         array(
            $orderid,
            5,
            $xuserid
         )
      );

      if (!$query) {
         $this->sys_error_db("fo_status insert");
         exit;
      } 

      $fologcode = 'FO.VERIFICATION.CONFIRM';
      $sql = "SELECT * FROM fo_verificationsvalue WHERE Fo_VerificationsValueT_OrderHeaderID = {$orderid}";

      $data_log = array();
      $data_log['orderid'] = $orderid;
      $data_log['values'] = $this->db_onedev->query($sql)->result_array();
      $data_log['note'] = $msg;

      $json_dt_log = json_encode($data_log);
      $sql = "insert into one_log.log_fo(
         Log_FoDate,
         Log_FoCode, 
         Log_FoJson, 
         Log_FoUserID) 
         values( now(), ?, ?, ?)";
      //echo $sql; 
      $query = $this->db_onedev->query($sql,
         array(
            $fologcode,
            $json_dt_log,
            $xuserid
         )
      );

      if (!$query) {
         $this->sys_error_db("one_log.fo_log insert");
         exit;
      }

      //$sql = "call sp_fo_barcode_generate_again_not_exist(" . $orderid. ")";
      //$this->db_onedev->query($sql);
	  $sql = "
				SELECT T_OrderHeaderLabNumber,
				T_TestID,
				IFNULL(local_sample_id,nas_sample_id) as xsample_id,
				IFNULL(local_sample_code,nas_sample_code) as xsample_code
				FROM (
				SELECT T_OrderHeaderLabNumber, T_TestID, 
				a.T_SampleTypeID as nas_sample_id, 
				a.T_SampleTypeSuffix as nas_sample_code, 
				b.T_SampleTypeID as local_sample_id, 
				b.T_SampleTypeSuffix as local_sample_code
				from t_orderheader
				JOIN t_orderdetail ON t_orderheaderid = t_orderdetailt_orderheaderid AND t_orderdetailIsActive = 'Y'
				JOIN t_test ON t_orderdetailt_testid = t_testid AND T_TestIsResult = 'Y'
				JOIN t_sampletype a ON T_TestT_SampleTypeID = a.T_SampleTypeID
				LEFT JOIN t_specimenlocal ON T_SpecimenLocalNat_TestID = T_TestNat_TestID AND T_SpecimenLocalIsActive = 'Y'
				LEFT JOIN t_sampletype b ON T_SpecimenLocalT_SampleTypeID = b.T_SampleTypeID
				where T_OrderHeaderID = {$orderid}
				group by a.T_SampleTypeID, b.T_SampleTypeID ) x
				GROUP BY xsample_id ";
		$dt_barcode =  $this->db_onedev->query($sql)->result_array();
		foreach($dt_barcode as $kb => $vb){
			$new_barcode =  $vb['T_OrderHeaderLabNumber'].$vb['xsample_code'].'1';
			$sql = " 	SELECT COUNT(*) as exist_sample 
						FROM t_barcodelab
						WHERE
							T_BarcodeLabT_OrderHeaderID = {$orderid} AND 
							( T_BarcodeLabT_SampleTypeID = {$vb['xsample_id']} ) AND 
							T_BarcodeLabIsActive = 'Y'";
			$exist_sample = $this->db_onedev->query($sql)->row()->exist_sample;
			if(intval($exist_sample) == 0){
				$sql = "INSERT INTO t_barcodelab(T_BarcodeLabT_OrderHeaderID,
							T_BarcodeLabBarcode,
							T_BarcodeLabT_SampleTypeID,
							T_BarcodeLabCounter)
							SELECT {$orderid}, '{$new_barcode}', {$vb['xsample_id']}, 1;

					";
				$this->db_onedev->query($sql);
				$sql = "SELECT T_BahanT_SampleStationID as stationid
						FROM t_sampletype 
						JOIN t_bahan ON T_SampleTypeT_BahanID = T_BahanID	
						WHERE T_SampleTypeID = {$vb['xsample_id']} LIMIT 1";
				$stationid = $this->db_onedev->query($sql)->row()->stationid;
				
				$sql = "SELECT T_SamplingQueueLastStatusT_SamplingQueueStatusID  as status_queue
						FROM t_sampling_queue_last_status 
						WHERE
						T_SamplingQueueLastStatusT_OrderHeaderID = {$orderid}  AND 
						T_SamplingQueueLastStatusT_SampleStationID = {$stationid}";
				$status_queue = $this->db_onedev->query($sql)->row()->status_queue;
				if(intval($status_queue) == 5){
					$sql = "UPDATE t_sampling_queue_last_status 
							SET T_SamplingQueueLastStatusT_SamplingQueueStatusID = 2 
							WHERE 
							T_SamplingQueueLastStatusT_OrderHeaderID = {$orderid}  AND 
							T_SamplingQueueLastStatusT_SampleStationID = {$stationid}";
					$this->db_onedev->query($sql);
				}
			}

		}
	  

      //$this->save_barcode_new($orderid);
      $sql = "SELECT * 
         FROM t_orderdetail
         JOIN t_samplingso ON T_SamplingSoT_OrderHeaderID = T_OrderDetailT_OrderHeaderID AND 
         T_SamplingSoT_TestID = T_OrderDetailT_TestID AND
         T_SamplingSoIsActive = 'Y' AND T_SamplingSoDoneDate IS NOT NULL
         LEFT JOIN so_resultentry ON So_ResultEntryT_OrderHeaderID = T_OrderDetailT_OrderHeaderID AND
         So_ResultEntryT_OrderDetailID = T_OrderDetailID AND
         So_ResultEntryIsActive = 'Y'
         WHERE
         T_OrderDetailT_OrderHeaderID = {$orderid} AND ISNULL(So_ResultEntryID) AND T_OrderDetailIsActive = 'Y'";
      $dt_sore =  $this->db_onedev->query($sql)->result_array();
      foreach($dt_sore as $k => $v){
         $sql = "CALL sp_insert_into_resultentry_so({$v['T_OrderDetailT_OrderHeaderID']},{$v['T_SamplingSoT_TestID']},{$xuserid})";
         //echo $sql;
         $this->db_onedev->query($sql);
      }

      $sql = "SELECT so_resultentry.*,so_resultentrydetail.* 
         FROM t_orderdetail
         JOIN t_samplingso ON T_SamplingSoT_OrderHeaderID = T_OrderDetailT_OrderHeaderID AND 
         T_SamplingSoT_TestID = T_OrderDetailT_TestID AND
         T_SamplingSoIsActive = 'Y' AND T_SamplingSoDoneDate IS NOT NULL
         JOIN so_resultentry ON So_ResultEntryT_OrderHeaderID = T_OrderDetailT_OrderHeaderID AND
         So_ResultEntryT_OrderDetailID = T_OrderDetailID AND
         So_ResultEntryIsActive = 'Y'
         JOIN so_resultentrydetail ON So_ResultEntryDetailSo_ResultEntryID = So_ResultEntryID AND 
         So_ResultEntryDetailIsActive = 'Y'
         WHERE
         T_OrderDetailT_OrderHeaderID = {$orderid} AND T_OrderDetailIsActive = 'Y'
         GROUP BY So_ResultEntryDetailID";
      $dt_priority =  $this->db_onedev->query($sql)->result_array();
      foreach($dt_priority as $k_prior => $v_prior){
         $sql = "SELECT * 
            FROM so_template
            JOIN so_templatedetail ON So_TemplateDetailSo_TemplateID = So_TemplateID AND 
            So_TemplateDetailName = '{$v_prior['So_ResultEntryDetailSo_TemplateDetailName']}' AND 
            So_TemplateDetailIsActive = 'Y'
            WHERE 
            So_TemplateName = '{$v_prior['So_ResultEntrySo_TemplateName']}' AND So_TemplateIsActive = 'Y'
            GROUP BY So_TemplateDetailID
            LIMIT 1";
         $xtemp_detail = $this->db_onedev->query($sql)->row_array();

         $sql = "UPDATE so_resultentrydetail SET 
            So_ResultEntryDetailSo_TemplateDetailPriority = '{$xtemp_detail['So_TemplateDetailPriority']}',
            So_ResultEntryDetailSo_TemplateDetailUnit = '{$xtemp_detail['So_TemplateDetailUnit']}'
            WHERE
            So_ResultEntryDetailID = {$v_prior['So_ResultEntryDetailID']}
		";

         $this->db_onedev->query($sql);
      }
		
		$sql = "UPDATE t_orderdetail 
				JOIN t_test ON T_OrderDetailT_TestID = T_TestID
				SET 
				T_OrderDetailT_TestName = T_TestName,
				T_OrderDetailT_TestCode = T_TestCode,
            T_OrderDetailT_TestIsResult = T_TestIsResult

				WHERE
				( T_OrderDetailT_TestName = '' OR T_OrderDetailT_TestCode  = '' OR T_OrderDetailT_TestIsResult = '') AND
				T_OrderDetailT_OrderHeaderID = {$orderid}";
		$this->db_onedev->query($sql);
		
		$sql = "UPDATE  t_barcodelab
				LEFT JOIN t_ordersample ON T_OrderSampleT_BarcodeLabID = T_BarcodeLabID AND 
				T_OrderSampleIsActive = 'Y'
				SET T_BarcodeLabIsActive = 'N',
				T_BarcodeLabUserID = {$xuserid}
				WHERE
				T_BarcodeLabT_OrderHeaderID = {$orderid} AND ISNULL(T_OrderSampleID)";
		$this->db_onedev->query($sql);


      $result = array(
         "total" => 1 , 
         "records" => array('prm'=>$prm)
      ); 
      $this->broadcast("fo-verification-y");
      $this->sys_ok($result);
      exit;
   }

   function lookup_supplies(){
      //# cek token valid
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }

      $prm = $this->sys_input;
      $orderid = $prm['orderid'];
      $query ="	SELECT M_SuppliesID as id, 
         IFNULL(T_OrderSuppliesID,0) as xid,
         IF(ISNULL(T_OrderSuppliesID),'N',T_OrderSuppliesIsActive) as chex,
         IFNULL(T_OrderSuppliesQty,1) as qty,
         M_SuppliesName as name,
         IF(ISNULL(T_OrderSuppliesID),NOW(),T_OrderSuppliesLastUpdated) as lastupdated,
         IF(ISNULL(T_OrderSuppliesID),NOW(),T_OrderSuppliesLastUpdated) as tx_lastupdated
         FROM m_supplies
         LEFT JOIN t_ordersupplies ON T_OrderSuppliesT_OrderHeaderID =  $orderid AND T_OrderSuppliesM_SuppliesID = M_SuppliesID
         WHERE
         M_SuppliesIsActive = 'Y'
";
      //echo $query ;
      $rows = $this->db_onedev->query($query)->result_array();
      foreach($rows as $k => $v){
         if($v['chex'] == 'N')
            $rows[$k]['chex'] = false;
         else
            $rows[$k]['chex'] = true;
      }

      $result = array(
         "total" => 1 , 
         "records" =>$rows
      ); 
      $this->sys_ok($result);
      exit;
   }

   function save_supplies()
   {
      try {
         //# cek token valid
         if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
         }

         //# ambil parameter input
         $xuserid = $this->sys_user['M_UserID'];
         $prm = $this->sys_input;
         $orderid = $prm['orderid'];
         $supplies = $prm['supplies'];
         $query ="	SELECT T_SampleTypeID as id, 
            T_SampleTypeName as name,
            '' as children
            FROM t_orderheader
            JOIN t_orderdetail ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID
            JOIN t_test ON T_OrderDetailT_TestID = T_TestID
            JOIN t_sampletype ON T_TestT_SampleTypeID = T_SampleTypeID
            WHERE
            T_OrderHeaderID = {$orderid}
            GROUP BY T_SampleTypeID
";
         //	echo $query ;
         $barcode = $this->db_onedev->query($query)->result();
         foreach($barcode as $k => $v){
            $query = "SELECT T_SampleTypeID as id, IFNULL(T_BarcodeLabID,0) as xid, 
               IF(ISNULL(T_BarcodeLabID),'Y',T_BarcodeLabIsActive) as chex, 
               T_TestName as testname,
               T_SampleTypeName as samplename, 
               IF(ISNULL(T_BarcodeLabID),CONCAT(T_OrderHeaderLabNumber,'.',T_SampleTypeID,'.',1),T_BarcodeLabBarcode) as barcodenumber
               FROM t_orderdetail 
               JOIN t_orderheader ON T_OrderHeaderID = T_OrderDetailT_OrderHeaderID 
               JOIN t_test ON T_OrderDetailT_TestID = T_TestID 
               JOIN t_sampletype ON T_TestT_SampleTypeID = T_SampleTypeID 
               LEFT JOIN t_barcodelab ON T_BarcodeLabT_OrderHeaderID = T_OrderDetailT_OrderHeaderID AND 
               T_BarcodeLabT_SampleTypeID = T_TestT_SampleTypeID AND T_BarcodeLabIsActive = 'Y'
               WHERE  
               T_OrderDetailT_OrderHeaderID = {$orderid} AND T_SampleTypeID = {$v->id}
               GROUP BY T_BarcodeLabID ";
            $v->children = $this->db_onedev->query($query)->result_array();
            foreach($v->children as $ki => $vi){
               if($vi['chex'] == 'N')
                  $v->children[$ki]['chex'] = false;
               else
                  $v->children[$ki]['chex'] = true;
            }
         }

         $dt_log = array('orderid'=>$orderid,'supplies'=>$supplies,'barcode'=>$barcode);
         $fologcode = 'FO.Verification.BarcodeSupplies';
         $json_dt_log = json_encode($dt_log);
         $sql = "insert into one_log.log_supplies_barcode(
            Log_SuppliesBarcodeDate,
            Log_SuppliesBarcodeCode, 
            Log_SuppliesBarcodeJson, 
            Log_SuppliesBarcodeUserID) 
            values( now(), ?, ?, ?)";
         //echo $sql; 
         $query = $this->db_onedev->query($sql,
            array(
               $fologcode,
               $json_dt_log,
               $xuserid
            )
         );

         if (!$query) {
            $this->sys_error_db("one_log.log_supplies_barcode insert");
            exit;
         }
         //print_r($supplies);
         foreach($supplies as $k => $v){
            if(intval($v['xid']) == 0 && $v['chex'] == true){
               $sql = "insert into t_ordersupplies(
                  T_OrderSuppliesT_OrderHeaderID,
                  T_OrderSuppliesM_SuppliesID, 
                  T_OrderSuppliesQty, 
                  T_OrderSuppliesCreated,
                  T_OrderSuppliesLastUpdated,
                  T_OrderSuppliesUserID) 
                  values( ?, ?, ?, now(),now(),?)";
               //echo $sql; 
               $query = $this->db_onedev->query($sql,
                  array(
                     $orderid,
                     $v['id'],
                     $v['qty'],
                     $xuserid
                  )
               );
               if (!$query) {
                  $this->sys_error_db("t_ordersupplies insert");
                  exit;
               } 
            }
            if(intval($v['xid']) > 0 && $v['chex'] == true){
               $sql = "update t_ordersupplies 
                  set 
                  T_OrderSuppliesQty = {$v['qty']}, 
                  T_OrderSuppliesLastUpdated = now(),
                  T_OrderSuppliesUserID = {$xuserid}
                  where
                  T_OrderSuppliesID = ?
";
               //echo $sql; 
               $query = $this->db_onedev->query($sql,
                  array(
                     $v['xid']
                  )
               );
               if (!$query) {
                  $this->sys_error_db("t_ordersupplies update");
                  exit;
               } 
            }
            if(intval($v['xid']) > 0 && $v['chex'] == false){
               $sql = "update t_ordersupplies 
                  set 
                  T_OrderSuppliesIsActive = 'N' , 
                  T_OrderSuppliesLastUpdated = now(),
                  T_OrderSuppliesUserID = $xuserid
                  where
                  T_OrderSuppliesID = ?
";
               //echo $sql; 
               $query = $this->db_onedev->query($sql,
                  array(
                     $v['xid']
                  )
               );
               if (!$query) {
                  $this->sys_error_db("t_ordersupplies update");
                  exit;
               } 
            }

         }

         $sql = "UPDATE t_ordermessage SET 
            T_OrderMessageRead = 'Y', 
            T_OrderMessageReadUserID = '{$xuserid}', 
            T_OrderMessageLastUpdated = NOW() 
            WHERE 
            T_OrderMessageT_OrderHeaderID = {$orderid} AND T_OrderMessageType = 'FO.VERIFICATION.REJECT'";
         //echo $sql;
         $query = $this->db_onedev->query($sql);
         if (!$query) {
            $this->sys_error_db("t_ordermessage update");
            exit;
         } 

         $result = array ("total" => 1, "records" => array('supplies'=>$supplies));
         $this->sys_ok($result);

      } catch(Exception $exc) {
         $message = $exc->getMessage();
         $this->sys_error($message);
      }
   }

   function lookup_barcode(){
      //# cek token valid
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }

      $prm = $this->sys_input;
      $orderid = $prm['orderid'];
      $query ="	SELECT T_SampleTypeID as id, 
         T_SampleTypeName as name,
         '' as children
         FROM t_orderheader
         JOIN t_orderdetail ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID
         JOIN t_test ON T_OrderDetailT_TestID = T_TestID
         JOIN t_sampletype ON T_TestT_SampleTypeID = T_SampleTypeID
         WHERE
         T_OrderHeaderID = {$orderid}
         GROUP BY T_SampleTypeID
";
      //echo $query ;
      $rows = $this->db_onedev->query($query)->result();
      foreach($rows as $k => $v){
         $query = "SELECT T_SampleTypeID as id, IFNULL(T_BarcodeLabID,0) as xid, 
            IF(ISNULL(T_BarcodeLabID),'Y',T_BarcodeLabIsActive) as chex, 
            T_TestName as testname,
            T_SampleTypeName as samplename, 
            IF(ISNULL(T_BarcodeLabID),CONCAT(T_OrderHeaderLabNumber,'.',T_SampleTypeID,'.',1),T_BarcodeLabBarcode) as barcodenumber
            FROM t_orderdetail 
            JOIN t_orderheader ON T_OrderHeaderID = T_OrderDetailT_OrderHeaderID 
            JOIN t_test ON T_OrderDetailT_TestID = T_TestID 
            JOIN t_sampletype ON T_TestT_SampleTypeID = T_SampleTypeID 
            LEFT JOIN t_barcodelab ON T_BarcodeLabT_OrderHeaderID = T_OrderDetailT_OrderHeaderID AND 
            T_BarcodeLabT_SampleTypeID = T_TestT_SampleTypeID AND T_BarcodeLabIsActive = 'Y'
            WHERE  
            T_OrderDetailT_OrderHeaderID = {$orderid} AND T_SampleTypeID = {$v->id}
            GROUP BY T_BarcodeLabID ";
         $v->children = $this->db_onedev->query($query)->result_array();
         foreach($v->children as $ki => $vi){
            if($vi['chex'] == 'N')
               $v->children[$ki]['chex'] = false;
            else
               $v->children[$ki]['chex'] = true;
         }
      }

      $result = array(
         "total" => 1 , 
         "records" =>$rows
      ); 
      $this->sys_ok($result);
      exit;
   }

   function lookup_promises(){
      //# cek token valid
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }

      $prm = $this->sys_input;
      $orderid = $prm['orderid'];
      $query ="	SELECT T_OrderPromiseID as id, 
         DATE_FORMAT(T_OrderPromiseDateTime, '%d-%m-%Y %H:%i') as name
         FROM t_orderpromise
         WHERE
         T_OrderPromiseT_OrderHeaderID = {$orderid} AND T_OrderPromiseIsActive = 'Y'
";
      //echo $query ;
      $rows = $this->db_onedev->query($query)->result();

      $result = array(
         "total" => 1 , 
         "records" =>$rows
      ); 
      $this->sys_ok($result);
      exit;
   }

   function save_barcode_new($orderid){
      $xuserid = $this->sys_user['M_UserID'];



      //insert log
      $supplies = array();
      $query ="	SELECT M_SuppliesID as id, 
         IFNULL(T_OrderSuppliesID,0) as xid,
         IF(ISNULL(T_OrderSuppliesID),'N',T_OrderSuppliesIsActive) as chex,
         IFNULL(T_OrderSuppliesQty,1) as qty,
         M_SuppliesName as name,
         IF(ISNULL(T_OrderSuppliesID),NOW(),T_OrderSuppliesLastUpdated) as lastupdated,
         IF(ISNULL(T_OrderSuppliesID),NOW(),T_OrderSuppliesLastUpdated) as tx_lastupdated
         FROM m_supplies
         JOIN t_ordersupplies ON T_OrderSuppliesT_OrderHeaderID =  $orderid AND T_OrderSuppliesM_SuppliesID = M_SuppliesID
         WHERE
         M_SuppliesIsActive = 'Y'
";
      //echo $query ;
      $supplies = $this->db_onedev->query($query)->result_array();

      foreach($supplies as $k => $v){
         if($v['chex'] == 'N')
            $supplies[$k]['chex'] = false;
         else
            $supplies[$k]['chex'] = true;
      }

      $dt_log = array('orderid'=>$orderid,'supplies'=>$supplies,'barcode'=>array());
      $fologcode = 'FO.Verification.BarcodeSupplies';
      $json_dt_log = json_encode($dt_log);
      $sql = "insert into one_log.log_supplies_barcode(
         Log_SuppliesBarcodeDate,
         Log_SuppliesBarcodeCode, 
         Log_SuppliesBarcodeJson, 
         Log_SuppliesBarcodeUserID) 
         values( now(), ?, ?, ?)";
      //echo $sql; 
      $query = $this->db_onedev->query($sql,
         array(
            $fologcode,
            $json_dt_log,
            $xuserid
         )
      );

      if (!$query) {
         $this->sys_error_db("one_log.log_supplies_barcode insert");
         exit;
      }
   }

   
   function lookup_delivery(){
      //# cek token valid
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }

      $prm = $this->sys_input;
      $orderid = $prm['orderid'];


      $query 	="	
         SELECT T_OrderDeliveryID as id, 
         0 as xid,
         M_DeliveryTypeCode as code,
         'Y' as chex,
         M_DeliveryID as deliveryid,
         M_DeliveryTypeID as typedeliveryid,
         T_OrderDeliveryM_KelurahanID as vilageid,
         IF(ISNULL(T_OrderDeliveryNoteID),'',T_OrderDeliveryNoteValue) as note,
         'spvadditional' as type,
         M_DeliveryName as label,
         T_OrderDeliveryDestination as destination,
         T_OrderDeliveryM_DeliveryID as addressid
         FROM t_orderdelivery
         JOIN m_delivery ON T_OrderDeliveryM_DeliveryID = M_DeliveryID
         JOIN m_deliverytype ON T_OrderDeliveryM_DeliveryTypeID = M_DeliveryTypeID
         LEFT JOIN t_orderdeliverynote ON T_OrderDeliveryNoteT_OrderDeliveryID = T_OrderDeliveryID AND T_OrderDeliveryNoteIsActive = 'Y'
         WHERE
         T_OrderDeliveryT_OrderHeaderID =  {$orderid} AND T_OrderDeliveryIsActive = 'Y' 
         GROUP BY T_OrderDeliveryID
         UNION
         SELECT 0 as id, 
         M_DeliveryID as xid,
         M_DeliveryTypeCode as code,
         'N' as chex,
         M_DeliveryID as deliveryid,
         M_DeliveryTypeID as typedeliveryid,
         0 as vilageid,
         '' as note,
         'spvadditional' as type,
         M_DeliveryName as label,
         '' as destination,
         0 as addressid
         FROM m_delivery
         JOIN m_deliverytype ON M_DeliveryM_DeliveryTypeID = M_DeliveryTypeID
         LEFT JOIN t_orderdelivery ON T_OrderDeliveryT_OrderHeaderID = {$orderid} AND T_OrderDeliveryM_DeliveryID = M_DeliveryID AND T_OrderDeliveryIsActive = 'Y'
         WHERE 
         M_DeliveryM_DeliveryTypeID = 1 AND ISNULL(T_OrderDeliveryID) AND M_DeliveryIsActive = 'Y'
         UNION
         SELECT 0 as id, 
         M_DeliveryID as xid,
         M_DeliveryTypeCode as code,
         'N' as chex,
         M_DeliveryID as deliveryid,
         M_DeliveryTypeID as typedeliveryid,
         M_PatientAddressM_KelurahanID as vilageid,
         '' as note,
         'spvadditional' as type,
         M_DeliveryName as label,
         CONCAT(M_PatientAddressDescription,' ',M_KelurahanName,', ',M_DistrictName,', ',M_CityName) as destination,
         M_PatientAddressID as addressid
         FROM t_orderheader 
         JOIN m_patientaddress ON M_PatientAddressM_PatientID = T_OrderHeaderM_PatientID
         JOIN m_kelurahan ON M_PatientAddressM_KelurahanID = M_KelurahanID
         JOIN m_district ON M_KelurahanM_DistrictID = M_DistrictID
         JOIN m_city ON M_DistrictM_CityID = M_CityID
         JOIN m_delivery ON M_DeliveryID = 2 AND M_DeliveryIsActive = 'Y'
         JOIN m_deliverytype ON M_DeliveryM_DeliveryTypeID = M_DeliveryTypeID
         LEFT JOIN t_orderdelivery ON T_OrderDeliveryT_OrderHeaderID = T_OrderHeaderID AND T_OrderDeliveryM_DeliveryID = M_DeliveryID AND
         T_OrderDeliveryAddressID = M_PatientAddressID AND T_OrderDeliveryIsActive = 'Y'
         WHERE 
         T_OrderHeaderID = {$orderid} AND ISNULL(T_OrderDeliveryID) 
         GROUP BY M_PatientAddressID
         UNION
         SELECT 0 as id, 
         M_DeliveryID as xid,
         M_DeliveryTypeCode as code,
         'N' as chex,
         M_DeliveryID as deliveryid,
         M_DeliveryTypeID as typedeliveryid,
         M_DoctorAddressM_KelurahanID as vilageid,
         '' as note,
         'spvadditional' as type,
         M_DeliveryName as label,
         CONCAT(M_DoctorAddressDescription,' ',M_KelurahanName,', ',M_DistrictName,', ',M_CityName) as destination,
         M_DoctorAddressID as addressid
         FROM t_orderheader 
         JOIN m_doctoraddress ON M_DoctorAddressM_DoctorID = T_OrderHeaderSenderM_DoctorID
         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
         JOIN m_delivery ON M_DeliveryID = 4 AND M_DeliveryIsActive = 'Y'
         JOIN m_deliverytype ON M_DeliveryM_DeliveryTypeID = M_DeliveryTypeID
         LEFT JOIN t_orderdelivery ON T_OrderDeliveryT_OrderHeaderID = T_OrderHeaderID AND T_OrderDeliveryM_DeliveryID = M_DeliveryID AND
         T_OrderDeliveryAddressID = M_DoctorAddressID AND T_OrderDeliveryIsActive = 'Y'
         WHERE 
         T_OrderHeaderID = {$orderid} AND ISNULL(T_OrderDeliveryID) 
         GROUP BY M_DoctorAddressID
         UNION
         SELECT 0 as id, 
         M_DeliveryID as xid,
         M_DeliveryTypeCode as code,
         'N' as chex,
         M_DeliveryID as deliveryid,
         M_DeliveryTypeID as typedeliveryid,
         M_CompanyM_KelurahanID as vilageid,
         '' as note,
         'spvadditional' as type,
         M_DeliveryName as label,
         CONCAT(M_CompanyAddress,' ',M_KelurahanName,', ',M_DistrictName,', ',M_CityName) as destination,
         M_CompanyID  as addressid
         FROM t_orderheader 
         JOIN m_company ON M_CompanyID = T_OrderHeaderM_CompanyID
         JOIN m_kelurahan ON M_CompanyM_KelurahanID = M_KelurahanID
         JOIN m_district ON M_KelurahanM_DistrictID = M_DistrictID
         JOIN m_city ON M_DistrictM_CityID = M_CityID
         JOIN m_delivery ON M_DeliveryID = 10 AND M_DeliveryIsActive = 'Y'
         JOIN m_deliverytype ON M_DeliveryM_DeliveryTypeID = M_DeliveryTypeID
         LEFT JOIN t_orderdelivery ON T_OrderDeliveryT_OrderHeaderID = T_OrderHeaderID AND T_OrderDeliveryM_DeliveryID = M_DeliveryID AND
         T_OrderDeliveryAddressID = M_CompanyID AND T_OrderDeliveryIsActive = 'Y'
         WHERE 
         T_OrderHeaderID = {$orderid} AND ISNULL(T_OrderDeliveryID) 
         UNION
         SELECT 0 as id, 
         M_DeliveryID as xid,
         M_DeliveryTypeCode as code,
         'N' as chex,
         M_DeliveryID as deliveryid,
         M_DeliveryTypeID as typedeliveryid,
         0 as vilageid,
         '' as note,
         'spvadditional' as type,
         M_DeliveryName as label,
         CASE
         WHEN M_DeliveryID = 3 THEN M_PatientEmail
         WHEN M_DeliveryID = 5 THEN M_DoctorEmail
         WHEN M_DeliveryID = 11 THEN M_MouEmail
         END as destination,
         0 as addressid
         FROM t_orderheader 
         JOIN m_doctor ON M_DoctorID = T_OrderHeaderSenderM_DoctorID
         JOIN m_patient ON M_PatientID = T_OrderHeaderM_PatientID
         JOIN m_mou ON M_MouID = T_OrderHeaderM_MouID
         JOIN m_delivery ON M_DeliveryM_DeliveryTypeID = 3 AND M_DeliveryIsActive = 'Y'
         JOIN m_deliverytype ON M_DeliveryM_DeliveryTypeID = M_DeliveryTypeID
         LEFT JOIN t_orderdelivery ON T_OrderDeliveryT_OrderHeaderID = T_OrderHeaderID AND T_OrderDeliveryM_DeliveryID = M_DeliveryID AND T_OrderDeliveryIsActive = 'Y'
         WHERE 
         T_OrderHeaderID = {$orderid} AND ISNULL(T_OrderDeliveryID) 
         UNION
         SELECT 0 as id, 
         M_DeliveryID as xid,
         M_DeliveryTypeCode as code,
         'N' as chex,
         M_DeliveryID as deliveryid,
         M_DeliveryTypeID as typedeliveryid,
         0 as vilageid,
         '' as note,
         'spvadditional' as type,
         M_DeliveryName as label,
         CASE
         WHEN M_DeliveryID = 6 THEN M_PatientHP
         WHEN M_DeliveryID = 7 THEN M_DoctorHP
         END as destination,
         0 as addressid
         FROM t_orderheader 
         JOIN m_doctor ON M_DoctorID = T_OrderHeaderSenderM_DoctorID
         JOIN m_patient ON M_PatientID = T_OrderHeaderM_PatientID
         JOIN m_delivery ON M_DeliveryM_DeliveryTypeID = 4 AND M_DeliveryIsActive = 'Y'
         JOIN m_deliverytype ON M_DeliveryM_DeliveryTypeID = M_DeliveryTypeID
         LEFT JOIN t_orderdelivery ON T_OrderDeliveryT_OrderHeaderID = T_OrderHeaderID AND T_OrderDeliveryM_DeliveryID = M_DeliveryID AND T_OrderDeliveryIsActive = 'Y'
         WHERE 
         T_OrderHeaderID = {$orderid} AND ISNULL(T_OrderDeliveryID) 
         UNION
         SELECT 0 as id, 
         M_DeliveryID as xid,
         M_DeliveryTypeCode as code,
         'N' as chex,
         M_DeliveryID as deliveryid,
         M_DeliveryTypeID as typedeliveryid,
         0 as vilageid,
         '' as note,
         'spvadditional' as type,
         M_DeliveryName as label,
         CASE
         WHEN M_DeliveryID = 8 THEN M_PatientHP
         WHEN M_DeliveryID = 9 THEN M_DoctorHP
         END as destination,
         0 as addressid
         FROM t_orderheader 
         JOIN m_doctor ON M_DoctorID = T_OrderHeaderSenderM_DoctorID
         JOIN m_patient ON M_PatientID = T_OrderHeaderM_PatientID
         JOIN m_delivery ON M_DeliveryM_DeliveryTypeID = 5 AND M_DeliveryIsActive = 'Y'
         JOIN m_deliverytype ON M_DeliveryM_DeliveryTypeID = M_DeliveryTypeID
         LEFT JOIN t_orderdelivery ON T_OrderDeliveryT_OrderHeaderID = T_OrderHeaderID AND T_OrderDeliveryM_DeliveryID = M_DeliveryID AND T_OrderDeliveryIsActive = 'Y'
         WHERE 
         T_OrderHeaderID = {$orderid} AND ISNULL(T_OrderDeliveryID) 
		 UNION
		 SELECT 0 as id, 
         M_DeliveryID as xid,
         M_DeliveryTypeCode as code,
         'N' as chex,
         M_DeliveryID as deliveryid,
         M_DeliveryTypeID as typedeliveryid,
         0 as vilageid,
         '' as note,
         'spvadditional' as type,
         M_DeliveryName as label,
         'Kirim ke eHac' as destination,
         0 as addressid
         FROM t_orderheader 
         JOIN m_doctor ON M_DoctorID = T_OrderHeaderSenderM_DoctorID
         JOIN m_patient ON M_PatientID = T_OrderHeaderM_PatientID
         JOIN m_delivery ON M_DeliveryM_DeliveryTypeID = 6 AND M_DeliveryIsActive = 'Y'
         JOIN m_deliverytype ON M_DeliveryM_DeliveryTypeID = M_DeliveryTypeID
         LEFT JOIN t_orderdelivery ON T_OrderDeliveryT_OrderHeaderID = T_OrderHeaderID AND T_OrderDeliveryM_DeliveryID = M_DeliveryID AND T_OrderDeliveryIsActive = 'Y'
         WHERE 
         T_OrderHeaderID = {$orderid} AND ISNULL(T_OrderDeliveryID) 
         UNION
		   SELECT 0 as id, 
         M_DeliveryID as xid,
         M_DeliveryTypeCode as code,
         'N' as chex,
         M_DeliveryID as deliveryid,
         M_DeliveryTypeID as typedeliveryid,
         0 as vilageid,
         '' as note,
         'spvadditional' as type,
         M_DeliveryName as label,
         'Kirim ke eHac' as destination,
         0 as addressid
         FROM t_orderheader 
         JOIN m_doctor ON M_DoctorID = T_OrderHeaderSenderM_DoctorID
         JOIN m_patient ON M_PatientID = T_OrderHeaderM_PatientID
         JOIN m_delivery ON M_DeliveryM_DeliveryTypeID = 10 AND M_DeliveryIsActive = 'Y'
         JOIN m_deliverytype ON M_DeliveryM_DeliveryTypeID = M_DeliveryTypeID
         LEFT JOIN t_orderdelivery ON T_OrderDeliveryT_OrderHeaderID = T_OrderHeaderID AND T_OrderDeliveryM_DeliveryID = M_DeliveryID AND T_OrderDeliveryIsActive = 'Y'
         WHERE 
         T_OrderHeaderID = {$orderid} AND ISNULL(T_OrderDeliveryID) 
		 

";
      //echo $query ;

      $rows_other = $this->db_onedev->query($query)->result_array();

      $result = array(
         "total" => 1 , 
         "records" => array('delivery_register'=>array(),'delivery_verification'=>array(),'delivery_other'=>$rows_other)
      ); 
      $this->sys_ok($result);
      exit;
   }

   

   function save_delivery_additional_verification()
   {
      try {
         //# cek token valid
         if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
         }

         //# ambil parameter input
         $xuserid = $this->sys_user['M_UserID'];
         $prm = $this->sys_input;
         $orderid = $prm['orderid'];
         $delver = $prm['delver'];


         foreach($delver as $i => $v){
            $sql = "insert into t_orderdelivery(
               T_OrderDeliveryT_OrderHeaderID,
               T_OrderDeliveryM_DeliveryID, 
               T_OrderDeliveryM_DeliveryTypeID, 
               T_OrderDeliveryDestination,
               T_OrderDeliveryAddressID,
               T_OrderDeliveryM_KelurahanID,
               T_OrderDeliveryUserID,
               T_OrderDeliveryCreated,
               T_OrderDeliveryLastUpdated) 
               values( ?, ?, ?, ?, ?, ?, ?,now(), now())";

            $query = $this->db_onedev->query($sql,
               array(
                  $orderid,
                  $v['deliveryid'],
                  $v['typedeliveryid'],
                  $v['destination'],
                  $v['addressid'],
                  $v['vilageid'],
                  $xuserid
               )
            );
            //echo $this->db_onedev->last_query();
            if (!$query) {
               $this->sys_error_db("t_orderdelivery insert");
               exit;
            }else{
               $lastid = $this->db_onedev->insert_id();
               $sql = "update fo_verification_delivery_add 
                  set 
                  Fo_VerificationDeliveryAddT_OrderDeliveryID = {$lastid} , 
                  Fo_VerificationDeliveryAddLastUpdated = now(),
                  Fo_VerificationDeliveryAddUserID = {$xuserid}
                  where
                  Fo_VerificationDeliveryAddID = {$v['xid']}
";
               //echo $sql; 
               $query = $this->db_onedev->query($sql);
               if (!$query) {
                  $this->sys_error_db("fo_verification_delivery_add update");
                  exit;
               } 
            }
         }

         $sql = "SELECT * FROM fo_verification_delivery WHERE Fo_VerificationDeliveryT_OrderHeaderID = {$orderid} AND Fo_VerificationDeliveryIsOK = 'N'";
         $nonactive_rst = $this->db_onedev->query($sql)->result();
         if($nonactive_rst){
            foreach($nonactive_rst as $i=>$val)
               $sql = "UPDATE t_orderdelivery SET 
               T_OrderDeliveryIsActive = 'N',
               T_OrderDeliveryUserID = {$xuserid},
               T_OrderDeliveryLastUpdated = now()
               WHERE 
               T_OrderDeliveryID = {$val->Fo_VerificationDeliveryT_OrderDeliveryID}
";
            //echo $sql;
            $query = $this->db_onedev->query($sql);
            if (!$query) {
               $this->sys_error_db("t_orderdelivery update");
               exit;
            } 
         }

         $sql = "UPDATE t_ordermessage SET 
            T_OrderMessageRead = 'Y', 
            T_OrderMessageReadUserID = '{$xuserid}', 
            T_OrderMessageLastUpdated = NOW() 
            WHERE 
            T_OrderMessageT_OrderHeaderID = {$orderid} AND T_OrderMessageType = 'FO.VERIFICATION.REJECT'";
         //echo $sql;
         $query = $this->db_onedev->query($sql);
         if (!$query) {
            $this->sys_error_db("t_ordermessage update");
            exit;
         } 
         //$orderid = $prm['orderid'];
         $query 	="	SELECT T_OrderDeliveryID as id, 
            IFNULL(Fo_VerificationDeliveryID,0) as xid,
            M_DeliveryTypeCode as code,
            IF(ISNULL(Fo_VerificationDeliveryID),'N',Fo_VerificationDeliveryIsOK) as chex,
            M_DeliveryID as deliveryid,
            M_DeliveryTypeID as typedeliveryid,
            T_OrderDeliveryM_KelurahanID as vilageid,
            IF(ISNULL(Fo_VerificationDeliveryID),'',Fo_VerificationDeliveryReason) as note,
            'registration' as type,
            M_DeliveryName as label,
            CASE
            WHEN T_OrderDeliveryM_DeliveryID = 1 THEN ''
            WHEN T_OrderDeliveryM_DeliveryID = 4 THEN M_DoctorAddressDescription
            WHEN T_OrderDeliveryM_DeliveryID = 2 THEN M_PatientAddressDescription
            ELSE 
            T_OrderDeliveryDestination
            END as destination,
            CASE
            WHEN T_OrderDeliveryM_DeliveryID = 4 THEN M_DoctorAddressID
            WHEN T_OrderDeliveryM_DeliveryID = 2 THEN M_PatientAddressID
            ELSE 
            0
            END as addressid
            FROM t_orderdelivery
            JOIN m_delivery ON T_OrderDeliveryM_DeliveryID = M_DeliveryID
            JOIN m_deliverytype ON T_OrderDeliveryM_DeliveryTypeID = M_DeliveryTypeID
            LEFT JOIN m_doctoraddress ON T_OrderDeliveryAddressID = M_DoctorAddressID AND T_OrderDeliveryM_DeliveryID = 4
            LEFT JOIN m_patientaddress ON T_OrderDeliveryAddressID = M_PatientAddressID AND T_OrderDeliveryM_DeliveryID = 2
            LEFT JOIN fo_verification_delivery ON Fo_VerificationDeliveryT_OrderHeaderID =  T_OrderDeliveryT_OrderHeaderID AND Fo_VerificationDeliveryIsActive = 'Y'
            WHERE
            T_OrderDeliveryT_OrderHeaderID =  {$orderid} AND T_OrderDeliveryIsActive = 'Y' 
            GROUP BY T_OrderDeliveryID
";
         //echo $query ;

         $rows_register = $this->db_onedev->query($query)->result_array();


         $result = array ("total" => 1, "records" => $rows_register);
         $this->sys_ok($result);

      } catch(Exception $exc) {
         $message = $exc->getMessage();
         $this->sys_error($message);
      }
   }

   function save_delivery_additional_other()
   {
      try {
         //# cek token valid
         if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
         }

         //# ambil parameter input
         $xuserid = $this->sys_user['M_UserID'];
         $prm = $this->sys_input;
         $orderid = $prm['orderid'];
         $delot = $prm['delot'];
         $sql = "SELECT T_OrderDeliveryID as id, 
            0 as xid,
            M_DeliveryTypeCode as code,
            'Y' as chex,
            M_DeliveryID as deliveryid,
            M_DeliveryTypeID as typedeliveryid,
            T_OrderDeliveryM_KelurahanID as vilageid,
            IF(ISNULL(T_OrderDeliveryNoteID),'',T_OrderDeliveryNoteValue) as note,
            'spvadditional' as type,
            M_DeliveryName as label,
            T_OrderDeliveryDestination as destination,
            T_OrderDeliveryM_DeliveryID as addressid
            FROM t_orderdelivery
            JOIN m_delivery ON T_OrderDeliveryM_DeliveryID = M_DeliveryID
            JOIN m_deliverytype ON T_OrderDeliveryM_DeliveryTypeID = M_DeliveryTypeID
            LEFT JOIN t_orderdeliverynote ON T_OrderDeliveryNoteT_OrderDeliveryID = T_OrderDeliveryID AND T_OrderDeliveryNoteIsActive = 'Y'
            WHERE
            T_OrderDeliveryT_OrderHeaderID =  {$orderid} AND T_OrderDeliveryIsActive = 'Y' 
            GROUP BY T_OrderDeliveryID";
         $result_old = $this->db_onedev->query($sql)->result_array();

         foreach($delot as $i => $v){
            $v['destination'] = str_replace("'", "\\'", $v['destination']);
            if(($v['id'] == 0 || $v['id'] == '0') && $v['chex'] == 'Y'){
               if($v['code'] == 'EMAIL' || $v['code'] == 'WHATSAPP' || $v['code'] == 'TELEGRAM'){
                  if($v['note'] != '')
                     $v['destination'] = $v['note'];
               }
               $sql = "insert into t_orderdelivery(
                  T_OrderDeliveryT_OrderHeaderID,
                  T_OrderDeliveryM_DeliveryID, 
                  T_OrderDeliveryM_DeliveryTypeID, 
                  T_OrderDeliveryDestination,
                  T_OrderDeliveryAddressID,
                  T_OrderDeliveryM_KelurahanID,
                  T_OrderDeliveryUserID,
                  T_OrderDeliveryCreated,
                  T_OrderDeliveryLastUpdated) 
                  values( 
               {$orderid}, 
               {$v['deliveryid']}, 
               {$v['typedeliveryid']}, 
               '{$v['destination']}', 
               {$v['addressid']}, 
               {$v['vilageid']},
               {$xuserid}, now(), now()
            )";

               $query = $this->db_onedev->query($sql);
               //echo $this->db_onedev->last_query();
               $last_insert_id = $this->db_onedev->insert_id();
               if($last_insert_id && $v['note'] != '' && $v['code'] != 'EMAIL' && $v['code'] != 'WHATSAPP' && $v['code'] != 'TELEGRAM'){
                  $sql = "INSERT INTO t_orderdeliverynote(
                     T_OrderDeliveryNoteT_OrderDeliveryID,
                     T_OrderDeliveryNoteValue,
                     T_OrderDeliveryNoteCreated,
                     T_OrderDeliveryNoteUserID
                  )
                  VALUES(
               {$last_insert_id},
               '{$v['note']}',
               NOW(),
               {$xuserid}
            )";
                  $this->db_onedev->query($sql);
               }

               if (!$query) {
                  $this->sys_error_db("t_orderdelivery insert");
                  exit;
               }
            }

            if(($v['id'] != 0 || $v['id'] != '0') && $v['chex'] == 'Y'){
               if($v['code'] == 'EMAIL' || $v['code'] == 'WHATSAPP' || $v['code'] == 'TELEGRAM'){
                  if($v['note'] != '')
                     $v['destination'] = $v['note'];
               }
               $sql = "update t_orderdelivery 
                  set 
                  T_OrderDeliveryUserID = {$xuserid},
                  T_OrderDeliveryDestination = '{$v['destination']}' , 
                  T_OrderDeliveryLastUpdated = now()
                  where
                  T_OrderDeliveryID = {$v['id']}
";
               //echo $sql; 
               $query = $this->db_onedev->query($sql);

               if($v['note'] != '' && $v['code'] != 'EMAIL' && $v['code'] != 'WHATSAPP' && $v['code'] != 'TELEGRAM'){
                  $sql = "UPDATE t_orderdeliverynote SET T_OrderDeliveryNoteIsActive = 'N' WHERE T_OrderDeliveryNoteT_OrderDeliveryID = {$v['id']}";
                  $this->db_onedev->query($sql);
                  $sql = "INSERT INTO t_orderdeliverynote(
                     T_OrderDeliveryNoteT_OrderDeliveryID,
                     T_OrderDeliveryNoteValue,
                     T_OrderDeliveryNoteCreated,
                     T_OrderDeliveryNoteUserID
                  )
                  VALUES(
                  {$v['id']},
                  '{$v['note']}',
                  NOW(),
                  {$xuserid}
               )";
                  $this->db_onedev->query($sql);
                  //echo $sql;
               }

               if (!$query) {
                  $this->sys_error_db("t_orderdelivery update");
                  exit;
               } 
            }

            if(($v['id'] != 0 || $v['id'] != '0') && $v['chex'] == 'N'){
               $sql = "update t_orderdelivery 
                  set 
                  T_OrderDeliveryUserID = {$xuserid},
                  T_OrderDeliveryIsActive = 'N' , 
                  T_OrderDeliveryLastUpdated = now()
                  where
                  T_OrderDeliveryID = {$v['id']}
";
               //echo $sql; 
               $query = $this->db_onedev->query($sql);
               if (!$query) {
                  $this->sys_error_db("t_orderdelivery update");
                  exit;
               } 
            }
         }

         $sql = "UPDATE t_ordermessage SET 
            T_OrderMessageRead = 'Y', 
            T_OrderMessageReadUserID = '{$xuserid}', 
            T_OrderMessageLastUpdated = NOW() 
            WHERE 
            T_OrderMessageT_OrderHeaderID = {$orderid} AND T_OrderMessageType = 'FO.VERIFICATION.REJECT'";
         //echo $sql;
         $query = $this->db_onedev->query($sql);
         if (!$query) {
            $this->sys_error_db("t_ordermessage update");
            exit;
         } 

         $sql = "SELECT T_OrderDeliveryID as id, 
            0 as xid,
            M_DeliveryTypeCode as code,
            'Y' as chex,
            M_DeliveryID as deliveryid,
            M_DeliveryTypeID as typedeliveryid,
            T_OrderDeliveryM_KelurahanID as vilageid,
            IF(ISNULL(T_OrderDeliveryNoteID),'',T_OrderDeliveryNoteValue) as note,
            'spvadditional' as type,
            M_DeliveryName as label,
            T_OrderDeliveryDestination as destination,
            T_OrderDeliveryM_DeliveryID as addressid
            FROM t_orderdelivery
            JOIN m_delivery ON T_OrderDeliveryM_DeliveryID = M_DeliveryID
            JOIN m_deliverytype ON T_OrderDeliveryM_DeliveryTypeID = M_DeliveryTypeID
            LEFT JOIN t_orderdeliverynote ON T_OrderDeliveryNoteT_OrderDeliveryID = T_OrderDeliveryID AND T_OrderDeliveryNoteIsActive = 'Y'
            WHERE
            T_OrderDeliveryT_OrderHeaderID =  {$orderid} AND T_OrderDeliveryIsActive = 'Y' 
            GROUP BY T_OrderDeliveryID";
         $result_new = $this->db_onedev->query($sql)->result_array();

         $json_del = json_encode(array('old'=>$result_old,'new'=>$result_new));
         $sql= "	INSERT order_delivery (
            Order_DeliveryT_OrdeheaderID,
            Order_DeliveryJson,
            Order_DeliveryUserID
         )
         VALUES(
         {$orderid},
         '{$json_del}',
         {$xuserid}
      )";
         $this->db_onedev->query($sql);

         $result = array ("total" => 1, "records" => $delver);
         $this->sys_ok($result);

      } catch(Exception $exc) {
         $message = $exc->getMessage();
         $this->sys_error($message);
      }
   }




   function lookup_order(){
      //# cek token valid
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }

      $prm = $this->sys_input;
      $orderid = $prm['orderid'];
      $query ="	SELECT t_orderheader.*, 
         M_DoctorID, 
         CONCAT('[ ',M_DoctorCode,' ] ',IFNULL(M_DoctorPrefix,''),'',IFNULL(M_DoctorPrefix2,''),' ',M_DoctorName,' ',IFNULL(M_DoctorSufix,''),IFNULL(M_DoctorSufix2,''),IFNULL(M_DoctorSufix3,'')) M_DoctorName, 
         M_DoctorAddressID, 
         M_DoctorAddressNote, 
         M_DoctorAddressDescription,
         M_CompanyID,
         M_CompanyName,
         M_MouID,
         M_MouName,
         M_MouStartDate,
         M_MouEndDate,
         CONCAT(M_MouName,' (',DATE_FORMAT(M_MouStartDate,'%d-%m-%Y'),' sd ',DATE_FORMAT(M_MouEndDate,'%d-%m-%Y'),') ') as mouname,
         '' as tests,
         '' as testsadd,
         '' as doctoraddresses,
         '' as doctors,
         '' as companies,
         '' as mous
         FROM t_orderheader
         JOIN m_doctor ON T_OrderHeaderSenderM_DoctorID = M_DoctorID
         JOIN m_doctoraddress ON T_OrderHeaderSenderM_DoctorAddressID = M_DoctorAddressID
         JOIN m_company ON T_OrderHeaderM_CompanyID = M_CompanyID
         JOIN m_mou ON T_OrderHeaderM_MouID = M_MouID
         WHERE
         T_OrderHeaderID = {$orderid} 
         GROUP BY T_OrderHeaderID
";
      //echo $query ;
      $rowx = $this->db_onedev->query($query)->row();

      $sql = "select M_LangID as id, M_LangName as name
         from m_lang
         where M_LangIsActive = 'Y'";
      $langs = $this->db_onedev->query($sql)->result();
      $arr_lang = array();
      foreach($langs as $klang => $xlang){
         array_push($arr_lang,array('id'=>$xlang->id,'si'=>'N','code'=>$xlang->id.'-N','name'=>$xlang->name));
         array_push($arr_lang,array('id'=>$xlang->id,'si'=>'Y','code'=>$xlang->id.'-Y','name'=>$xlang->name.' ( SI )'));
      }
      $rtn_langs = array();
      foreach($arr_lang as $kklang => $xxlang){
         if(intval($xxlang['id']) != 1){
            array_push($rtn_langs,$xxlang);
         }
      }

      $sql = "SELECT * 
         FROM t_orderheaderaddon
         JOIN m_lang ON T_OrderHeaderAddOnSecondM_LangID = M_LangID
         WHERE
         T_OrderHeaderAddOnT_OrderHeaderID = {$orderid}";
      $lang_second_now = $this->db_onedev->query($sql)->row_array();
      $selected_lang_second = array();
      if($lang_second_now){
         $name = $lang_second_now['M_LangName'];
         if($lang_second_now['T_OrderHeaderAddOnSecondLangIsSI'] == 'Y')
            $name = $lang_second_now['M_LangName'].' ( SI )';

         $selected_lang_second = array(
            'id'=>$lang_second_now['M_LangID'],
            'si'=>$lang_second_now['T_OrderHeaderAddOnSecondLangIsSI'],
            'code'=>$lang_second_now['M_LangID'].'-'.$lang_second_now['T_OrderHeaderAddOnSecondLangIsSI'],
            'name'=>$name
         );
      }
      $rowx->langs = $rtn_langs;
      $rowx->selected_lang = $selected_lang_second;

      $query = "	SELECT M_DoctorAddressID as id, M_DoctorAddressDescription as name
         FROM m_doctoraddress 
         WHERE  
         M_DoctorAddressM_DoctorID = {$rowx->M_DoctorID} AND M_DoctorAddressIsActive = 'Y'";
      $rowx->doctoraddresses = $this->db_onedev->query($query)->result_array();
      $rowx->doctors = array(array('id'=>$rowx->M_DoctorID,'name'=>$rowx->M_DoctorName));
      $query = "	SELECT M_MouID as id, CONCAT(M_MouName,' (',DATE_FORMAT(M_MouStartDate,'%d-%m-%Y'),' sd ',DATE_FORMAT(M_MouEndDate,'%d-%m-%Y'),') ') as name
         FROM m_mou 
         WHERE  
         M_MouM_CompanyID = {$rowx->M_CompanyID} AND M_MouIsReleased = 'Y' AND M_MouIsActive = 'Y'  AND ( CURDATE() >= DATE(M_MouStartDate) AND CURDATE() <= DATE(M_MouEndDate) )";
      $rowx->mous = $this->db_onedev->query($query)->result_array();
      $rowx->companies = array(array('id'=>$rowx->M_CompanyID,'name'=>$rowx->M_CompanyName));
      $result = array(
         "total" => 1 , 
         "records" =>$rowx
      ); 
      $this->sys_ok($result);
      exit;
   }



   function searchdoctor(){
      //# cek token valid
      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_doctor
         WHERE 
         M_DoctorName like ?
         AND M_DoctorIsActive = '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_doctor count",$this->db_onedev);
         exit;
      }

      $sql = "
                        SELECT M_DoctorID as id, CONCAT('[ ',M_DoctorCode,' ] ',IFNULL(M_DoctorPrefix,''),'',IFNULL(M_DoctorPrefix2,''),' ',M_DoctorName,' ',IFNULL(M_DoctorSufix,''),IFNULL(M_DoctorSufix2,''),IFNULL(M_DoctorSufix3,'')) as name, '' as xaddress
                        FROM m_doctor
                        WHERE 
                        CONCAT('[ ',M_DoctorCode,' ] ',IFNULL(M_DoctorPrefix,''),'',IFNULL(M_DoctorPrefix2,''),' ',M_DoctorName,' ',IFNULL(M_DoctorSufix,''),IFNULL(M_DoctorSufix2,''),IFNULL(M_DoctorSufix3,'')) like '{$q['search']}'
                        AND M_DoctorIsActive = 'Y'
                        group by M_DoctorID
                        ORDER BY M_DoctorName ASC
                        LIMIT 20
                  ";
      // echo $sql;
      $query = $this->db_onedev->query($sql);

      if ($query) { 
         $rows = $query->result_array();
                        /*foreach($rows as $k => $v){
                                $sql = "SELECT * FROM m_doctoraddress WHERE M_DoctorAddressM_DoctorID = {$v->id} AND M_DoctorAddressIsActive = 'Y'";
                                $rows[$k]['xaddress'] = $this->db_onedev->query($sql)->result();
                        }*/
         //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_doctor rows",$this->db_onedev);
         exit;
      }
   }
   function searchcompany(){
      //# cek token valid


      $prm = $this->sys_input;

      $max_rst = 12;
      $tot_count =0;

      $q = [
         'search'     => '%'
      ];
      $xsearch = $prm['search']['search'];
      if ($prm['search'] != '')
      {
         $q['search'] = "%{$xsearch}%";  
      }

      // QUERY TOTAL
      
      $sql = "
                        SELECT M_CompanyID as id, M_CompanyName as name
                        FROM m_company
                        WHERE 
                        M_CompanyName like CONCAT('%','{$prm['search']}','%')
                        AND M_CompanyIsActive = 'Y'
                        ORDER BY M_CompanyName ASC LIMIT 20
                  ";
      $query = $this->db_onedev->query($sql);
      //echo $this->db_onedev->last_query();
      if ($query) { 
         $rows = $query->result_array();
         //echo $this->db_onedev->last_query();
         $result = array("total" => 1, "records" => $rows, "total_display" => sizeof($rows));
         $this->sys_ok($result);
      } 
      else {
         $this->sys_error_db("m_company rows",$this->db_onedev);
         exit;
      }
   }

   function getdoctoraddress(){
      //# cek token valid
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }

      $prm = $this->sys_input;
      $query ="	SELECT M_DoctorAddressID as id, M_DoctorAddressDescription as name
         FROM m_doctoraddress 
         WHERE M_DoctorAddressM_DoctorID = ? AND M_DoctorAddressIsActive = 'Y' 
";
      //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 getmou(){
      //# cek token valid


      $prm = $this->sys_input;
      $query ="	SELECT M_MouID as id, CONCAT(M_MouName,' (',DATE_FORMAT(M_MouStartDate,'%d-%m-%Y'),' sd ',DATE_FORMAT(M_MouEndDate,'%d-%m-%Y'),') ') as name
         FROM m_mou 
         WHERE M_MouM_CompanyID = ? AND M_MouIsActive = 'Y' AND M_MouIsReleased = 'Y' AND ( CURDATE() >= DATE(M_MouStartDate) AND CURDATE() <= DATE(M_MouEndDate) )
";
      //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 checkprice(){
      //# cek token valid
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }

      $prm = $this->sys_input;
      $mouid = $prm['mouid'];
      $query ="	SELECT t_orderdetail.*, 
         json_extract(fn_price_spv(T_OrderDetailT_TestID,T_OrderHeaderDate,T_OrderDetailIsCito,$mouid), '$.test_total') as checkprice

         FROM t_orderdetail
         JOIN t_orderheader ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID 
         WHERE
         T_OrderDetailT_OrderHeaderID = {$prm['orderid']} AND T_OrderDetailT_TestIsPrice = 'Y' AND T_OrderDetailIsActive = 'Y'
         HAVING checkprice < 0
";
      //echo $query;
      $rows = $this->db_onedev->query($query)->result_array();
      //echo $this->db_onedev->last_query();
      if($rows){
         $rtn = array('status'=>'FAILED', 'data'=>$rows);
      }
      else{
         $rtn = array('status'=>'OK', 'data'=>array());
      }

      $result = array(
         "total" => 1, 
         "records" => $rtn, 
      ); 
      $this->sys_ok($result);
      exit;
   }

   function flagmcu(){
      //# cek token valid
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }

      $prm = $this->sys_input;
      $query ="	UPDATE t_orderheaderaddon SET T_OrderHeaderAddOnPatientMcu = '{$prm['value']}' WHERE T_OrderHeaderAddOnT_OrderHeaderID = {$prm['orderid']}";
      //echo $query;
      $rows = $this->db_onedev->query($query);

      $result = array(
         "total" => 1, 
         "records" => array(), 
      ); 
      $this->sys_ok($result);
      exit;
   }

   function saveorder()
   {
      try {
         //# cek token valid
         if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
         }

         //# ambil parameter input
         $xuserid = $this->sys_user['M_UserID'];
         $prm = $this->sys_input;
         $orderid = $prm['orderid'];
         $sql = "SELECT * FROM t_orderheader WHERE T_OrderHeaderID = '{$prm['orderid']}'";
         $orderold = $this->db_onedev->query($sql)->row_array();

         $sql = "UPDATE t_orderheader SET 
            T_OrderHeaderSenderM_DoctorID = '{$prm['doctorid']}', 
            T_OrderHeaderSenderM_DoctorAddressID = '{$prm['doctoraddressid']}', 
            T_OrderHeaderM_CompanyID = '{$prm['company']}', 
            T_OrderHeaderM_MouID = '{$prm['mou']}', 
            T_OrderHeaderUserID = '{$xuserid}', 
            T_OrderHeaderLastUpdated = NOW() 
            WHERE 
            T_OrderHeaderID = {$prm['orderid']}";
         //echo $sql;
         $query = $this->db_onedev->query($sql);
         if($orderold['T_OrderHeaderM_CompanyID'] != $prm['company']){
            $log_attr_type = "spv_order_company";
            $sql = "SELECT M_CompanyID as id, M_CompanyName as name
               FROM m_company
               WHERE 
               M_CompanyID = {$orderold['T_OrderHeaderM_CompanyID']}";
            $x_old_company = $this->db_onedev->query($sql)->row_array();
            $sql = "SELECT M_CompanyID as id, M_CompanyName as name
               FROM m_company
               WHERE 
               M_CompanyID = {$prm['company']}";
            $x_new_company = $this->db_onedev->query($sql)->row_array();
            $x_json_company = json_encode(array('old'=>$x_old_company,'new'=>$x_new_company));
            $sql = "INSERT INTO order_attr (
               orderAttrT_OrderHeaderID,
               orderAttrType,
               orderAttrJson,
               orderAttrUserID
            )
            VALUES(
            {$prm['orderid']},
            '{$log_attr_type}',
            '{$x_json_company}',
            '{$xuserid}'
         )";
            $this->db_onedev->query($sql);
         }

		
         if($orderold['T_OrderHeaderSenderM_DoctorID'] != $prm['doctorid'] || $orderold['T_OrderHeaderSenderM_DoctorAddressID'] != $prm['doctoraddressid']){
            $log_attr_type = "spv_order_doctor";
            $sql = "SELECT 	CONCAT(M_DoctorID,'^',M_DoctorAddressID) as id, 
               CONCAT(CONCAT(M_DoctorPrefix,M_DoctorPrefix2,' ',M_DoctorName,' ',M_DoctorSufix,M_DoctorSufix2,M_DoctorSufix3),'^',CONCAT(M_DoctorAddressNote,' : ',M_DoctorAddressDescription)) as name
               FROM m_doctor
               LEFT JOIN m_doctoraddress ON M_DoctorAddressM_DoctorID = M_DoctorID AND
               M_DoctorAddressID = {$orderold['T_OrderHeaderSenderM_DoctorAddressID']}
               WHERE 
               M_DoctorID = {$orderold['T_OrderHeaderSenderM_DoctorID']}";
            //echo $sql;
            $x_old_doctor = $this->db_onedev->query($sql)->row_array();
            $sql = "SELECT 	CONCAT(M_DoctorID,'^',M_DoctorAddressID) as id, 
               CONCAT(CONCAT(M_DoctorPrefix,M_DoctorPrefix2,' ',M_DoctorName,' ',M_DoctorSufix,M_DoctorSufix2,M_DoctorSufix3),'^',CONCAT(M_DoctorAddressNote,' : ',M_DoctorAddressDescription)) as name
               FROM m_doctor
               LEFT JOIN m_doctoraddress ON M_DoctorAddressM_DoctorID = M_DoctorID AND
               M_DoctorAddressID = {$prm['doctoraddressid']}
               WHERE 
               M_DoctorID = {$prm['doctorid']}";
            //echo $sql;
            $x_new_doctor = $this->db_onedev->query($sql)->row_array();
            $x_json_doctor = json_encode(array('old'=>$x_old_doctor,'new'=>$x_new_doctor));
            $sql = "INSERT INTO order_attr (
               orderAttrT_OrderHeaderID,
               orderAttrType,
               orderAttrJson,
               orderAttrUserID
            )
            VALUES(
            {$prm['orderid']},
            '{$log_attr_type}',
            '{$x_json_doctor}',
            '{$xuserid}'
         )";
            $this->db_onedev->query($sql);
         }

         if (!$query) {
            $this->sys_error_db("t_orderheader update");
            exit;
         }else{
            if($orderold['T_OrderHeaderM_MouID'] != $prm['mou']){
               $sql = "SELECT T_OrderDetailID as id, 
                  fn_price_spv(T_OrderDetailT_TestID,T_OrderHeaderDate,T_OrderDetailIsCito,T_OrderHeaderM_MouID) as dttest,
                  T_OrderDetailIsCito, T_OrderDetailT_TestCode, T_OrderDetailT_TestSasCode, T_OrderDetailT_TestName,
                  T_OrderDetailPrice, T_OrderDetailPriceForDisc, T_OrderDetailDisc, 
                  T_OrderDetailDiscAmount, T_OrderDetailDiscTotal, T_OrderDetailTotal
                  FROM t_orderdetail 
                  JOIN t_test ON T_TestID = T_OrderDetailT_TestID AND T_TestIsPrice = 'Y'
                  JOIN t_orderheader ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID  
                  WHERE 
                  T_OrderDetailT_OrderHeaderID = '{$prm['orderid']}' AND T_OrderDetailIsActive = 'Y'";
               //echo $sql;
               $orderdetails = $this->db_onedev->query($sql)->result_array();

               if($orderdetails){
                  $md5_code = md5(date('YmdHis').rand(5, 15));

                  $sql = "SELECT M_MouID, M_MouM_CompanyID, M_MouName, M_CompanyName
                     FROM m_mou 
                     JOIN m_company ON M_MouM_CompanyID = M_CompanyID
                     WHERE 
                     M_MouID = {$orderold['T_OrderHeaderM_MouID']}";
                  $x_old_mou = $this->db_onedev->query($sql)->row_array();
                  $sql = "SELECT M_MouID, M_MouM_CompanyID, M_MouName, M_CompanyName
                     FROM m_mou 
                     JOIN m_company ON M_MouM_CompanyID = M_CompanyID
                     WHERE 
                     M_MouID = {$prm['mou']}";
                  $x_new_mou = $this->db_onedev->query($sql)->row_array();
                  $sql = "SELECT * FROM m_mou WHERE M_MouID = {$orderold['T_OrderHeaderM_MouID']}";
                  $old_mou = $this->db_onedev->query($sql)->row_array();

                  $data_new_log = array();

                  foreach($orderdetails as $k => $v){
                     $xid = $v['id'];
                     $dttest = json_decode($v['dttest']);
                     $dsictot = ((floatval($dttest->test_disc)/100) * intval($dttest->test_price)) + intval($dttest->test_discrp);
                     $total = intval($dttest->test_price) - $dsictot ;
                     $sql = "UPDATE t_orderdetail SET
                        T_OrderDetailPrice = {$dttest->test_price},
                        T_OrderDetailPriceForDisc = {$dttest->test_price},
                        T_OrderDetailDisc = '{$dttest->test_disc}',
                        T_OrderDetailDiscAmount = '{$dttest->test_discrp}',
                        T_OrderDetailDiscTotal = '{$dsictot}',
                        T_OrderDetailTotal = '{$total}',
                        T_OrderDetailUserID = '{$xuserid}',
                        T_OrderDetailLastUpdated = now()
                        WHERE
                        T_OrderDetailID = {$xid}";
                     $updatedetail = $this->db_onedev->query($sql);

                     $arr_new_log = 	array(
                        "action" => 'edit',
                        "orderdetail_id" => $xid,
                        "pxcode" => $v['T_OrderDetailT_TestCode'],
                        "pxsascode" => $v['T_OrderDetailT_TestSasCode'],
                        "pxname" => $v['T_OrderDetailT_TestName'],
                        "pxid" => $v['T_OrderDetailT_TestID'],
                        "bf_cito" => $v['T_OrderDetailIsCito'],
                        "cito" => $v['T_OrderDetailIsCito'],
                        "bf_bruto" => $v['T_OrderDetailPrice'],
                        "bf_discountpersen" => $v['T_OrderDetailDisc'],
                        "bf_discountrp" => $v['T_OrderDetailDiscAmount'],
                        "bf_discounttotal" => $v['T_OrderDetailDiscTotal'],
                        "bf_total" => $v['T_OrderDetailTotal'],
                        "bruto" => $dttest->test_price,
                        "discountpersen" => $dttest->test_disc,
                        "discountrp" => $dttest->test_discrp,
                        "discounttotal" => $dsictot,
                        "total" => $total
                     );
                     array_push($data_new_log,$arr_new_log);
                  }

                  //#begin insert new log status
                  $px_type_new_log = "update_mou";
                  $px_mou_log = json_encode(array('old'=>$x_old_mou,'new'=>$x_new_mou));
                  $json_new_log = json_encode($data_new_log);
                  $sql = "INSERT INTO order_px (
                     OrderPxT_OrderHeaderID,
                     OrderPxMou,
                     OrderPxType,
                     OrderPxJson,
                     OrderPxUserID
                  )
                  VALUES(
                  {$prm['orderid']},
                  '{$px_mou_log}',
                  '{$px_type_new_log}',
                  '{$json_new_log}',
                  {$xuserid}
               )";
                  $this->db_onedev->query($sql);
                  //#end insert new log status
               }
			   
			    $sql = " UPDATE t_sampling_queue_last_status SET 
								T_SamplingQueueLastStatusT_SamplingQueueStatusID = 2
						WHERE
							T_SamplingQueueLastStatusT_OrderHeaderID = {$prm['orderid']} AND 
							T_SamplingQueueLastStatusT_SampleStationID = 1";
				$this->db_onedev->query($sql);
				
				
				$sql = "UPDATE t_orderdetail 
						JOIN t_test ON T_OrderDetailT_TestID = T_TestID
						SET 
						T_OrderDetailT_TestName = T_TestName,
						T_OrderDetailT_TestCode = T_TestCode

						WHERE
						( T_OrderDetailT_TestName = '' OR T_OrderDetailT_TestCode  = '') AND
						T_OrderDetailT_OrderHeaderID = {$prm['orderid']}";
				$this->db_onedev->query($sql);
            }
         } 


         if($prm['lang']){
            $sql = "UPDATE t_orderheaderaddon SET 
               T_OrderHeaderAddOnSecondM_LangID = {$prm['lang']['id']}, 
               T_OrderHeaderAddOnSecondLangIsSI = '{$prm['lang']['si']}'
               WHERE
               T_OrderHeaderAddOnT_OrderHeaderID = {$orderid}";
            $this->db_onedev->query($sql);

            $sql = "SELECT * FROM t_orderheaderaddon WHERE T_OrderHeaderAddOnT_OrderHeaderID = {$orderid}";
            $orderaddonold = $this->db_onedev->query($sql)->row_array();

            if(intval($orderaddonold['T_OrderHeaderAddOnSecondM_LangID']) != intval($prm['lang']['id']) || $orderaddonold['T_OrderHeaderAddOnSecondLangIsSI'] != $prm['lang']['si'] ){
               $log_attr_type = "spv_order_lang";
               $sql = "SELECT M_LangID as id, M_LangName as name, '{$orderaddonold['T_OrderHeaderAddOnSecondLangIsSI']}' as si
                  FROM m_lang
                  WHERE 
                  M_LangID = {$orderaddonold['T_OrderHeaderAddOnSecondM_LangID']}";
               $x_old_lang = $this->db_onedev->query($sql)->row_array();
               $sql = "SELECT M_LangID as id, M_LangName as name, '{$prm['lang']['si']}' as si
                  FROM m_lang
                  WHERE 
                  M_LangID = {$prm['lang']['id']}";
               $x_new_lang = $this->db_onedev->query($sql)->row_array();
               $x_json_lang = json_encode(array('old'=>$x_old_lang,'new'=>$x_new_lang));
               $sql = "INSERT INTO order_attr (
                  orderAttrT_OrderHeaderID,
                  orderAttrType,
                  orderAttrJson,
                  orderAttrUserID
               )
               VALUES(
               {$prm['orderid']},
               '{$log_attr_type}',
               '{$x_json_lang}',
               '{$xuserid}'
            )";
               $this->db_onedev->query($sql);
            }

         }

         $sql = "UPDATE t_ordermessage SET 
            T_OrderMessageRead = 'Y', 
            T_OrderMessageReadUserID = '{$xuserid}', 
            T_OrderMessageLastUpdated = NOW() 
            WHERE 
            T_OrderMessageT_OrderHeaderID = {$orderid} AND T_OrderMessageType = 'FO.VERIFICATION.REJECT'";
         //echo $sql;
         $query = $this->db_onedev->query($sql);
         if (!$query) {
            $this->sys_error_db("t_ordermessage update");
            exit;
         } 


         $result = array ("total" => 1, "records" => array());
         $this->sys_ok($result);

      } catch(Exception $exc) {
         $message = $exc->getMessage();
         $this->sys_error($message);
      }
   }

   function lookup_test(){
      //# cek token valid
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }

      $prm = $this->sys_input;
      $orderid = $prm['orderid'];
      $query ="	SELECT t_orderheader.*,
         '' as testregistration,
         '' as testverification,
         '' as testall,
         '' as promises
         FROM t_orderheader
         WHERE
         T_OrderHeaderID = {$orderid} 
         GROUP BY T_OrderHeaderID
";
      //echo $query ;
      $rowx = $this->db_onedev->query($query)->row();

      $query = "	SELECT T_OrderDetailID as xid,
         T_OrderDetailT_OrderHeaderID as orderid,
         IF(T_OrderDetailT_TestIsPanel = 'Y','PN','PX') as type,
         IF(T_OrderDetailT_TestIsPanel = 'Y','Y','N') as is_packet,
         'Y' as status,
         'N' as active,
         IF(T_OrderDetailIsCito = 'Y','Y','N') as cito,
         IF(T_OrderDetailIsCito = 'Y','Y','N') as cito_before,
         '' as cito_promise,
            T_OrderDetailT_TestID as pxid,
            T_TestNat_TestID nat_testid,
            '' as nat_test,
            T_OrderDetailT_TestCode as pxcode,
            T_OrderDetailT_TestSasCode as pxsascode,
            CONCAT(T_OrderDetailT_TestSasCode,' ',T_TestName) as pxname,
			T_TestName as test_name,
            T_OrderDetailPrice as bruto,
            IF(T_OrderDetailDisc = '' OR ISNULL(T_OrderDetailDisc),0,T_OrderDetailDisc) as discountpersen,
            IF(T_OrderDetailDiscAmount = '' OR ISNULL(T_OrderDetailDiscAmount),0,T_OrderDetailDiscAmount) as discountrp,
            IF(T_OrderDetailDiscTotal = '' OR ISNULL(T_OrderDetailDiscTotal),0,T_OrderDetailDiscTotal) as discount,
            '' as charge,
            T_OrderDetailTotal as total
            FROM t_orderdetail 
            
            join t_test ON T_OrderDetailT_TestID = T_TestID AND T_TestIsActive = 'Y' 
            WHERE  
            T_OrderDetailT_OrderHeaderID = {$orderid} AND  T_TestIsPrice = 'Y'  AND 
			T_OrderDetailIsActive = 'Y'";
      //echo $query;
      $rowx->testregistration = $this->db_onedev->query($query)->result_array();
      $rowx->testall = $rowx->testregistration;
      foreach($rowx->testall as $k => $v){
         $sql = "SELECT T_TestNat_TestID as nat_test
            FROM t_orderdetail
            JOIN t_test ON T_OrderDetailT_TestID = T_TestID
            WHERE T_OrderDetailT_OrderHeaderID = {$orderid} AND T_OrderDetailT_TestSasCode LIKE '{$v['pxsascode']}%' AND T_OrderDetailIsActive = 'Y'";
         //echo $sql;
         $xnattest = $this->db_onedev->query($sql)->result_array();
         $v['nat_test'] = array();
         foreach($xnattest as $xk => $xv){
            array_push($v['nat_test'],$xv['nat_test']);
         }
         $rowx->testall[$k]['nat_test'] = $v['nat_test'];
         if($v['status'] == 'N')
            $rowx->testall[$k]['status'] = false;
         if($v['cito'] == 'N')
            $rowx->testall[$k]['cito'] = false;

         $rowx->testall[$k]['charge'] = array('value'=>0,'label'=>'0%');
      }
      $rowx->testverification = array();
      $promises = '';
      //$promises = $this->get_promises($rowx->testverification);

      $result = array(
         "total" => 1 , 
         "records" =>$rowx,
         "promise"=>$promises
      ); 
      $this->sys_ok($result);
      exit;
   }
   function lookup_packet(){
      //# cek token valid
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }

      $prm = $this->sys_input;
      $orderid = $prm['orderid'];
      $query ="	SELECT t_orderheader.*,
         '' as testregistration,
         '' as testverification,
         '' as testall,
         '' as promises
         FROM t_orderheader
         WHERE
         T_OrderHeaderID = {$orderid} 
         GROUP BY T_OrderHeaderID
      ";
      //echo $query ;
      $rowx = $this->db_onedev->query($query)->row();

      $query = "	SELECT T_OrderDetailID as xid,
         T_OrderDetailOrderT_OrderHeaderID as orderid,
         T_OrderDetailOrderPacketType as type,
         'Y' as is_packet,
         'Y' as status,
         'N' as active,
         'N' as cito,
         'N' as cito_before,
         '' as cito_promise,
            T_OrderDetailOrderT_PacketID as pxid,
            T_OrderDetailOrderT_PacketID nat_testid,
            '' as nat_test,
            T_PacketSasCode as pxcode,
            T_PacketSasCode as pxsascode,
            CONCAT(T_PacketSasCode,' ',T_OrderDetailOrderT_PacketName) as pxname,
			T_OrderDetailOrderT_PacketName as test_name,
            T_PacketOriginalPrice as bruto,
            0 as discountpersen,
            0 as discountrp,
            0 as discount,
            '' as charge,
            T_PacketPrice as total,
            GROUP_CONCAT(T_OrderDetailT_TestName SEPARATOR ',') as px_detail
            FROM t_orderdetailorder
            JOIN t_packet ON T_PacketID = T_OrderDetailOrderT_PacketID
            JOIN t_orderdetail ON T_OrderDetailT_OrderHeaderID = T_OrderDetailOrderT_OrderHeaderID AND T_OrderDetailT_OrderDetailOrderID = T_OrderDetailOrderID
            AND T_OrderDetailIsActive = 'Y' AND T_OrderDetailT_TestIsPrice = 'Y'
            join t_test ON T_OrderDetailT_TestID = T_TestID AND T_TestIsActive = 'Y' 
            WHERE  
            T_OrderDetailOrderT_OrderHeaderID = {$orderid} AND  T_OrderDetailOrderIsPacket = 'Y'
            GROUP BY T_OrderDetailOrderT_PacketID";
      //echo $query;
      $rowx->testregistration = $this->db_onedev->query($query)->result_array();
      $rowx->testall = $rowx->testregistration;
      $rowx->testverification = array();

      $query = "SELECT T_PacketID as pxid,
            T_PacketID nat_testid,
            '' as nat_test,
            T_PacketSasCode as pxcode,
            T_PacketSasCode as pxsascode,
            CONCAT(T_PacketSasCode,' ',T_PacketName) as pxname,
			T_PacketName as test_name,
            T_PacketOriginalPrice as bruto,
            0 as discountpersen,
            0 as discountrp,
            0 as discount,
            '' as charge,
            T_PacketPrice as total,
            GROUP_CONCAT(T_TestName SEPARATOR ',') as px_detail
            FROM t_orderheader
            JOIN mgm_mcu ON Mgm_McuID = T_OrderHeaderMgm_McuID
            JOIN t_packet ON T_PacketT_PriceHeaderID = Mgm_McuT_PriceHeaderID AND T_PacketIsActive = 'Y'
            AND T_PacketID not in ( 
                  select T_OrderDetailOrderT_PacketID from t_orderdetailorder where T_OrderDetailOrderT_OrderHeaderID = {$orderid}
                   AND T_OrderDetailOrderIsPacket = 'Y'
                   AND T_OrderDetailOrderIsActive = 'Y')
            JOIN t_packetdetail ON T_PacketDetailT_PacketID = T_PacketID AND T_PacketDetailIsActive = 'Y'
            JOIN t_test ON T_TestID = T_PacketDetailT_TestID 
            WHERE  
            T_OrderHeaderID = {$orderid}
            GROUP BY T_PacketID
            ";
      $rowx->packetall = $this->db_onedev->query($query)->result_array();
      $promises = '';
      //$promises = $this->get_promises($rowx->testverification);

      $result = array(
         "total" => 1 , 
         "records" =>$rowx,
         "promise"=>$promises
      ); 
      $this->sys_ok($result);
      exit;
   }

   function get_promises($tests){
      return '208-07-2019 14:50';
   }

   function getnewprice(){
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }

      $prm = $this->sys_input;
      $orderid = $prm['orderid'];
      $cito = $prm['cito'] ? 'Y':'N';
      $cito_before = $prm['cito_before'] == 'N' ? 'Y':'N';
      $query ="	SELECT t_orderheader.*
         FROM t_orderheader
         WHERE
         T_OrderHeaderID = {$orderid};
";
      $row_header = $this->db_onedev->query($query)->row();

      $sql = "SELECT {$prm['xid']} as xid,
         Nat_TestID as nat_testid,
         nat_test,
         is_packet,
         packet_id,
         px_type as type,
         ss_price_mou.T_TestID as pxid,
         test.T_TestCode as pxcode,
         test.T_TestSasCode as pxsascode,
         test.T_TestName as pxname,
         test.T_TestIsResult as isresult,
         JSON_UNQUOTE(JSON_EXTRACT(one_json_sum(Ss_PriceMouID),'$.T_PriceAmount')) as bruto,
         JSON_UNQUOTE(JSON_EXTRACT(one_json_sum(Ss_PriceMouID),'$.T_PriceDisc')) as discountpersen,
         JSON_UNQUOTE(JSON_EXTRACT(one_json_sum(Ss_PriceMouID),'$.T_PriceDiscRp')) as discountrp,
         if(JSON_UNQUOTE(JSON_EXTRACT(one_json_sum(Ss_PriceMouID),'$.T_PriceDisc')) <> 0 ,(((JSON_UNQUOTE(JSON_EXTRACT(one_json_sum(Ss_PriceMouID),'$.T_PriceDisc')) / 100) * JSON_UNQUOTE(JSON_EXTRACT(one_json_sum(Ss_PriceMouID),'$.T_PriceAmount'))) - JSON_UNQUOTE(JSON_EXTRACT(one_json_sum(Ss_PriceMouID),'$.T_PriceDiscRp'))),JSON_UNQUOTE(JSON_EXTRACT(one_json_sum(Ss_PriceMouID),'$.T_PriceDiscRp'))) as discount,
            ( JSON_UNQUOTE(JSON_EXTRACT(one_json_sum(Ss_PriceMouID),'$.T_PriceAmount')) - ((JSON_UNQUOTE(JSON_EXTRACT(one_json_sum(Ss_PriceMouID),'$.T_PriceDisc')) / 100) * JSON_UNQUOTE(JSON_EXTRACT(one_json_sum(Ss_PriceMouID),'$.T_PriceAmount'))) - JSON_UNQUOTE(JSON_EXTRACT(one_json_sum(Ss_PriceMouID),'$.T_PriceDiscRp')) ) as total,
            'Y' as status,
            'N' as active,
            '{$cito}' as cito,
            '{$cito_before}' as cito_before,
            '' as promise,
            '' as charge,
            child_test
            FROM ss_price_mou
            JOIN t_test test ON ss_price_mou.T_TestID = test.T_TestID
            WHERE
            ss_price_mou.T_TestID = {$prm['pxid']} AND 
            T_PriceIsCito = '{$cito}' AND 
            T_PriceT_PriceHeaderID = {$row_header->T_OrderHeaderM_MouID} AND 
            is_packet <> 'Y'";
      //echo $sql;
      $rows = $this->db_onedev->query($sql)->result_array();
      if($rows){
         foreach($rows as $k => $v){
            if($v['cito'] == 'N')
               $rows[$k]['cito'] = false;
            else
               $rows[$k]['cito'] = true;
         }
      }

      $result = array(
         "total" => 1 , 
         "records" =>$rows
      ); 
      $this->sys_ok($result);
      exit;
   }


   function get_promise_from_arr($arr,$testid){
      $idx = -1;
      foreach($arr as $k=>$v){
         if($v['testid'] == $testid){
            $idx = $k;
         }
      }
      return $idx;
   }


   function search_test()
   {
      //# cek token valid
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }

      $prm = $this->sys_input;

      $max_rst = 12;
      $tot_count = 0;
      //$cito = $prm['cito'] == true ?'Y':'N';
      $filtertest = $prm['filtertest'];

      $sql = "	SELECT T_OrderHeaderMgm_McuID as mgmmcuid,
         Mgm_McuT_PriceHeaderID as priceheaderid,
         T_OrderHeaderDate as orderdate
         FROM t_orderheader 
         JOIN mgm_mcu ON Mgm_McuID = T_OrderHeaderMgm_McuID
         WHERE T_OrderHeaderID = '{$prm['orderid']}'";
      $dtorder = $this->db_onedev->query($sql)->row_array();

      $orderdate = $dtorder['orderdate'];
      $priceheaderid = $dtorder['priceheaderid'];

      $q['search'] = "%{$prm['search']}%";  

      //$test_exist = $filtertest;

      $sql = "	SELECT * FROM (
         SELECT 0 as xid,
         Nat_TestID as nat_testid,
         nat_test,
         is_packet,
         packet_id,
         px_type as type,
         ss_price_mou.T_TestID as pxid,
         test.T_TestCode as pxcode,
         test.T_TestSasCode as pxsascode,
		 test.T_TestName as test_name,
         CONCAT(test.T_TestSasCode,' ',test.T_TestName) as pxname,
         test.T_TestIsResult as isresult,
         JSON_UNQUOTE(JSON_EXTRACT(one_json_sum(Ss_PriceMouID),'$.T_PriceAmount')) as bruto,
         JSON_UNQUOTE(JSON_EXTRACT(one_json_sum(Ss_PriceMouID),'$.T_PriceDisc')) as discountpersen,
         JSON_UNQUOTE(JSON_EXTRACT(one_json_sum(Ss_PriceMouID),'$.T_PriceDiscRp')) as discountrp,
         if(JSON_UNQUOTE(JSON_EXTRACT(one_json_sum(Ss_PriceMouID),'$.T_PriceDisc')) <> 0 ,(((JSON_UNQUOTE(JSON_EXTRACT(one_json_sum(Ss_PriceMouID),'$.T_PriceDisc')) / 100) * JSON_UNQUOTE(JSON_EXTRACT(one_json_sum(Ss_PriceMouID),'$.T_PriceAmount'))) + JSON_UNQUOTE(JSON_EXTRACT(one_json_sum(Ss_PriceMouID),'$.T_PriceDiscRp'))),JSON_UNQUOTE(JSON_EXTRACT(one_json_sum(Ss_PriceMouID),'$.T_PriceDiscRp'))) as discount,
            ( JSON_UNQUOTE(JSON_EXTRACT(one_json_sum(Ss_PriceMouID),'$.T_PriceAmount')) - ((JSON_UNQUOTE(JSON_EXTRACT(one_json_sum(Ss_PriceMouID),'$.T_PriceDisc')) / 100) * JSON_UNQUOTE(JSON_EXTRACT(one_json_sum(Ss_PriceMouID),'$.T_PriceAmount'))) - JSON_UNQUOTE(JSON_EXTRACT(one_json_sum(Ss_PriceMouID),'$.T_PriceDiscRp')) ) as total,
            'Y' as status,
            'N' as active,
            'N' as cito,
            'N' as cito_before,
            '' as promise,
            '' as charge,
            child_test
            FROM ss_price_mou
            JOIN t_test test ON ss_price_mou.T_TestID = test.T_TestID
            WHERE
            CONCAT(test.T_TestSasCode,' ',test.T_TestName) LIKE '{$q['search']}' AND T_PriceT_PriceHeaderID = {$priceheaderid} AND is_packet <> 'Y' AND T_PriceIsCito <> 'Y'
            ) x
            WHERE
            pxid not in ( 
                  select T_OrderDetailT_TestID from t_orderdetail where T_OrderDetailT_OrderHeaderID = '{$prm['orderid']}' AND T_OrderDetailIsActive = 'Y')
            LIMIT 50";
      //echo $sql;
      $query = $this->db_onedev->query($sql);

      if ($query) { 
         $rows = $query->result_array();
         $rows_filtered = [];
         //echo $this->db_onedev->last_query();
         if($rows){
            //$rows = json_decode($rows);
            foreach($rows as $k => $v){
               if($v['status'] == 'N')
                  $rows[$k]['status'] = false;
               else
                  $rows[$k]['status'] = true;

               if($v['cito'] == 'N')
                  $rows[$k]['cito'] = false;
               else
                  $rows[$k]['cito'] = true;

               $rows[$k]['charge'] = array('value'=>0,'label'=>'0%');
               $nat_test_now = array();
               if($v['type'] == 'PXR'){
                  $sql = "SELECT * 
                     FROM t_test 
                     WHERE 
                     T_TestSasCode LIKE '{$v['pxsascode']}%' AND T_TestID <> {$v['pxid']} AND T_TestIsActive = 'Y'";
                  //echo $sql;
                  $row_nattest = $this->db_onedev->query($sql)->result_array();
                  if($row_nattest){
                     foreach($row_nattest as $xnat => $vnat){
                        array_push($nat_test_now,$vnat['T_TestNat_TestID']);
                     }
                     $rows[$k]['nat_test'] = $nat_test_now;
                  }

                  $child_test = json_decode($v['child_test']);
                  //print_r($child_test);
                  foreach($child_test as $kchild => $vchild){
                     $child_test[$kchild]->nat_test = json_decode($vchild->nat_test);
                     $sql = "SELECT * 
                        FROM t_test 
                        WHERE 
                        T_TestID = {$vchild->T_TestID}";
                     //echo $sql;
                     $row_test = $this->db_onedev->query($sql)->row_array();
                     $child_test[$kchild]->{"isresult"} = $row_test['T_TestIsResult'];
                     $child_test[$kchild]->pxcode = $row_test['T_TestCode'];
                     $child_test[$kchild]->pxsascode = $row_test['T_TestSasCode'];
                  }
                  //print_r($child_test);
                  $rows[$k]['child_test'] = $child_test;
                  //print_r($rows[$k]['child_test']);
               }
               else{
                  $rows[$k]['nat_test'] = json_decode($v['nat_test']);
                  $rows[$k]['child_test'] = json_decode($v['child_test']);
               }
               //$rows[$k]['child_test'] = json_decode($v['child_test']);
            }

         }
         $result = array("total" => count($rows), "records" => $rows, "total_display" => sizeof($rows));
         $this->sys_ok($result);
      } 
      else {
         // echo $this->db_onedev->last_query();
         $this->sys_error_db("m_company rows",$this->db_onedev);
         exit;
      }
   }

   function double_test_insert($orderheaderid,$testid){
	     if(intval($testid) == 0){
			 return TRUE;
		 }
		 else{
	    $get_datatest = "SELECT * FROM t_test WHERE T_TestID = {$testid} LIMIT 1";
	    $datatest = $this->db_onedev->query($get_datatest)->row_array();
	   //echo $get_datatest;
	    $sql = "	SELECT COUNT(*) as total
					FROM t_orderdetail
					JOIN t_test ON T_OrderDetailT_TestID = T_TestID AND T_TestNat_TestID = {$datatest['T_TestNat_TestID']} AND T_TestIsActive = 'Y'
					WHERE
						T_OrderDetailT_OrderHeaderID = {$orderheaderid} AND T_OrderDetailIsActive = 'Y'";
		//echo $sql;
		$check_exist = $this->db_onedev->query($sql)->row_array()->total;
		if($check_exist == 0)
			return TRUE;
		else{
			return FALSE;
		}
		 }
   }

   function save_test_additional(){
		try {
         //# cek token valid
         if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
         }

         //# ambil parameter input
         $xuserid = $this->sys_user['M_UserID'];
         $prm = $this->sys_input;
         $md5_code = md5(date('YmdHis').rand(5, 15));
         $orderid = $prm['orderid'];
         $sql = "SELECT * FROM t_orderheader WHERE T_OrderHeaderID = {$orderid}";
         $order_header = $this->db_onedev->query($sql)->row_array();
         $labnumber = $order_header['T_OrderHeaderLabNumber'];
         $testall = $prm['testall'];
         $data_log = array();
         //#begin data before update
         $query = "	SELECT T_OrderDetailID as xid,
            IF(T_OrderDetailT_TestIsPanel = 'Y','PN','SINGLE') as type,
            'Y' as status,
            T_OrderDetailIsCito as cito,
            T_OrderDetailT_TestID as testid,
            T_OrderDetailT_TestCode as testcode,
            T_OrderDetailT_TestSasCode as testsascode,
            T_OrderDetailT_TestName as testname,
            T_OrderDetailPrice as bruto,
            T_OrderDetailDiscTotal as discount,
            T_OrderDetailTotal as total
            FROM t_orderdetail 
            WHERE  
            T_OrderDetailT_OrderHeaderID = {$orderid} AND T_OrderDetailT_TestIsPrice = 'Y' AND T_OrderDetailIsActive = 'Y'";
         //echo $query;
         $data_log['before']['detail'] = $this->db_onedev->query($query)->result_array(); 

         $sql = "SELECT CONCAT(T_TestT_SampleTypeID,'-',T_SampleTypeSuffix) as id
            FROM t_orderdetail
            JOIN t_test ON T_OrderDetailT_TestID = T_TestID
            JOIN t_sampletype ON T_TestT_SampleTypeID = T_SampleTypeID
            WHERE T_OrderDetailT_OrderHeaderID = '{$orderid}' AND T_OrderDetailIsActive = 'Y' 
            GROUP BY T_TestT_SampleTypeID";
         $rst_sampletypes_before = $this->db_onedev->query($sql)->result();
         $sampletypes_before = array();
         foreach($rst_sampletypes_before as $k => $v){
            $sampletypes_before[] = $v->id;
         }
         //#begin data before update
         $data_new_log = array();
         //#begin loop tests
         foreach($testall as $i => $v){
            //# add test
            if(($v['xid'] == 0 || $v['xid'] == '0') && $v['status'] == 'Y' ){
               $isPanel = 'N';
               $xtestid = $v['pxid'];
               if($v['type'] == 'PN'){
                  $isPanel = 'Y';
                  $xtestid = 0;
               }
               $cito = 'N';
               if($v['cito'])
                  $cito = 'Y';
               $isPanelChildren = 'N';
               $isPanelChildrenPrintNota = 'N';
			   
			    $check_exist = $this->double_test_insert($orderid,$xtestid);
			    if($check_exist){

               $sqlt_orderdetailorder = "INSERT INTO t_orderdetailorder(
						T_OrderDetailOrderT_OrderHeaderID,
						T_OrderDetailOrderT_TestID,
						T_OrderDetailOrderT_TestName,
						T_OrderDetailOrderIsPacket,
						T_OrderDetailOrderPacketType,
						T_OrderDetailOrderT_PacketID,	
						T_OrderDetailOrderT_PacketName,	
						T_OrderDetailOrderCreated,
						T_OrderDetailOrderCreatedUserID,
						T_OrderDetailOrderLastUpdated,
						T_OrderDetailOrderLastUpdatedUserID,
						T_OrderDetailOrderJsonChildren)
			      VALUES(
				      {$orderid},
						{$xtestid},
						'{$v['test_name']}',
						'N',
						'',
						0,	
						'',
						now(),
						{$xuserid},
						now(),
						now(),
						'')";
            //echo $sqlorder;
            $queryt_orderdetailorder = $this->db_onedev->query($sqlt_orderdetailorder);
            if (!$queryt_orderdetailorder) {
              $this->sys_error_db(["status" => "ERR", "message" => "insert t_orderdetailorder | " .
                $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
              exit;
            }
            //$this->db_onedev->trans_commit();
            $detail_id = $this->db_onedev->insert_id();

            

					$sql = "insert into t_orderdetail(
                 T_OrderDetailT_OrderDetailOrderID,
					  T_OrderDetailT_OrderHeaderID,
					  T_OrderDetailT_TestID, 
					  T_OrderDetailT_TestCode,
					  T_OrderDetailT_TestSasCode,							
					  T_OrderDetailT_TestName,
					  T_OrderDetailT_TestIsResult,
					  T_OrderDetailT_TestIsPrice,
					  T_OrderDetailIsCito,
					  T_OrderDetailPrice,
					  T_OrderDetailPriceForDisc,
					  T_OrderDetailDisc,
					  T_OrderDetailDiscAmount,
					  T_OrderDetailDiscTotal,
					  T_OrderDetailTotal,
					  T_OrderDetailT_TestIsPanel,
					  T_OrderDetailT_TestIsPanelChildren,
					  T_OrderDetailT_TestIsPanelChildrenPrintNota,
					  T_OrderDetailUserID,
					  T_OrderDetailCreated,
					  T_OrderDetailLastUpdated
					) 
					values(
                  {$detail_id},
					   {$orderid},
					   {$xtestid},
					   '{$v['pxcode']}',
					   '{$v['pxsascode']}',
					   '{$v['test_name']}',
					   '{$v['isresult']}',
					   'Y',
					   '{$cito}',
					   '{$v['bruto']}',
					   '{$v['bruto']}',
					   '{$v['discountpersen']}',
					   '{$v['discountrp']}',
					   '{$v['discount']}',
					   '{$v['total']}',
					   '{$isPanel}',
					   '{$isPanelChildren}',
					   '{$isPanelChildrenPrintNota}',
					   {$xuserid},
					   NOW(),
					   NOW()
					)";
					//echo $sql;
               
               $query = $this->db_onedev->query($sql);
               $last_detail_id = $this->db_onedev->insert_id();
                     $query_generate_location = $this->generate_location($orderid,$xtestid); 
                     $sql=  $this->db_onedev->query("SELECT *
                     FROM t_orderdetail
                     WHERE
                        T_OrderDetailID = {$last_detail_id}");
               if (!$sql) {
               $this->sys_error_db(["status" => "ERR", "message" => "select t_orderdetail | " .
               $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
               }
               $x_data = $sql->result_array();
               $x_data = json_encode($x_data);
               
               $sqllog = "INSERT INTO cpone_log.log_fo(
               Log_FoT_OrderHeaderID,	
               Log_FoDate,	
               Log_FoCode,	
               Log_FoJson,
               Log_FoUserID)
               VALUES ('{$orderid}',now(),'TAMBAH','{$x_data}','{$xuserid}')";
               $querylog = $this->db_onedev->query($sqllog);
               if (!$querylog) {
               
               $this->sys_error_db(["status" => "ERR", "message" => "update log_fo | " .
               $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
               
               }
				}


               if($v['type'] == 'PX'){

                  $sql = "SELECT * 
                     FROM t_test
                     LEFT JOIN t_sampletype ON T_TestT_SampleTypeID = T_SampleTypeID
                     LEFT JOIN t_bahan ON T_SampleTypeT_BahanID = T_BahanID
                     LEFT JOIN t_samplestation ON T_BahanT_SampleStationID = T_SampleStationID
                     WHERE
                     T_TestSasCode LIKE '{$v['pxsascode']}%' AND T_TestID <> {$v['pxid']} AND T_TestIsActive = 'Y'";
                  //echo $sql;
                  $rst_query = $this->db_onedev->query($sql)->result_array();
                  if($rst_query){
                     foreach($rst_query as $kkxz => $vvxz){
						$check_exist = $this->double_test_insert($orderid,$vvxz['T_TestID']);
						if($check_exist){          
                     
							$sql = "insert into t_orderdetail(
									   T_OrderDetailT_OrderDetailOrderID,
					               T_OrderDetailT_OrderHeaderID,
									   T_OrderDetailT_TestID, 
									   T_OrderDetailT_TestCode,
									   T_OrderDetailT_TestSasCode,							
									   T_OrderDetailT_TestName,
									   T_OrderDetailT_TestIsResult,
									   T_OrderDetailT_TestIsPrice,
									   T_OrderDetailT_TestIsPanel,
									   T_OrderDetailT_TestIsPanelChildren,
									   T_OrderDetailT_TestIsPanelChildrenPrintNota,
									   T_OrderDetailIsCito,
									   T_OrderDetailUserID,
									   T_OrderDetailCreated,
									   T_OrderDetailLastUpdated
					            ) 
					            values(
                              {$detail_id},
										{$orderid},
										{$vvxz['T_TestID']},
										'{$vvxz['T_TestCode']}',
										'{$vvxz['T_TestSasCode']}',
										'{$vvxz['T_TestName']}',
										'{$vvxz['T_TestIsResult']}',
										'{$vvxz['T_TestIsPrice']}',
										'N',
										'N',
										'{$vvxz['T_TestIsPrintNota']}',
										'{$cito}',
										{$xuserid},
										NOW(),
										NOW()
									)";
							//echo $sql;
							
						   $query = $this->db_onedev->query($sql);
                     $last_detail_id = $this->db_onedev->insert_id();
                     $query_generate_location = $this->generate_location($orderid,$vvxz['T_TestID']); 
                     $sql=  $this->db_onedev->query("SELECT *
                     FROM t_orderdetail
                     WHERE
                        T_OrderDetailID = {$last_detail_id}");

               if (!$sql) {
                  $this->sys_error_db(["status" => "ERR", "message" => "select t_orderdetail | " .
                  $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
               }

               $x_data = $sql->result_array();
               $x_data = json_encode($x_data);
               
               $sqllog = "INSERT INTO cpone_log.log_fo(
               Log_FoT_OrderHeaderID,	
               Log_FoDate,	
               Log_FoCode,	
               Log_FoJson,
               Log_FoUserID)
               VALUES ('{$orderid}',now(),'TAMBAH','{$x_data}','{$xuserid}')";
               $querylog = $this->db_onedev->query($sqllog);
               if (!$querylog) {
               
               $this->sys_error_db(["status" => "ERR", "message" => "update log_fo | " .
               $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
               
               }
						}
                        

                     }
                  }
				  
				  
               }

               
			}
            
         
		 }
         //#end loop tests	
		 
		 
		   $sql = " UPDATE t_orderdetail 
                  JOIN t_test ON T_OrderDetailT_TestID = T_TestID
                  SET 
                  T_OrderDetailT_TestName = T_TestName,
                  T_OrderDetailT_TestCode = T_TestCode

                  WHERE
                  ( T_OrderDetailT_TestName = '' OR T_OrderDetailT_TestCode  = '') AND
                  T_OrderDetailT_OrderHeaderID = {$orderid}";
		   $this->db_onedev->query($sql);

         $this->fix_ordersample($labnumber);
     
         //echo $this->db_onedev->last_query();
         $result = array ("total" => 1, "records" => array('testall' => $rtntestregistration));
         $this->sys_ok($result);

      } catch(Exception $exc) {
         $message = $exc->getMessage();
         $this->sys_error($message);
      }
	}

   function fix_ordersample($labnumber){
      $this->db_onedev->trans_begin();
      $sql = "SELECT T_OrderHeaderID,T_OrderHeaderLabNumber, T_TestID, T_SampleTypeID , T_SampleTypeSuffix, T_SampleStationIsNonLab, T_SampleStationID
          from t_orderheader
          JOIN t_orderdetail ON t_orderheaderid = t_orderdetailt_orderheaderid AND t_orderdetailIsActive = 'Y'
          JOIN t_test ON t_orderdetailt_testid = t_testid AND T_TestIsResult = 'Y'
          JOIN t_sampletype ON T_TestT_SampleTypeID = T_SampleTypeID
          JOIN t_bahan ON T_SampleTypeT_BahanID = T_BahanID
          JOIN t_samplestation ON T_BahanT_SampleStationID = T_SampleStationID
          where T_OrderHeaderLabNumber = '{$labnumber}'
          group by T_SampleTypeID";
          $query = $this->db_onedev->query($sql);
          if(!$query){
           echo "error : ".$this->db_onedev->error();
           return;
          }
      $rows = $query->result_array();
      $new_insert = [];
      foreach($rows as $row){
         $lab_no = $row['T_OrderHeaderLabNumber'];
         $test_id = $row['T_TestID'];
         $sample_id = $row['T_SampleTypeID'];
         $sample_code = $row['T_SampleTypeSuffix'];
         $barcode = $lab_no . $sample_code;
         $isnonlab = $row['T_SampleStationIsNonLab'];
         $samplestation_id = $row['T_SampleStationID'];
         $counter_barcode = 1;
         $sql = "SELECT *
                      FROM t_barcodelab
                      WHERE T_BarcodeLabT_OrderHeaderID = '{$row['T_OrderHeaderID']}' AND 
                      T_BarcodeLabT_SampleTypeID = '{$row['T_SampleTypeID']}' AND T_BarcodeLabIsActive = 'Y'";
         $query = $this->db_onedev->query($sql);
         if(!$query){
            echo "error : ".$this->db_onedev->error();
            return;
         }
         $barcodelab = $query->result_array();
         //echo count($barcodelab)."<br>";
         if(count($barcodelab) ==  0){
            $new_insert[] = [
                  'T_OrderHeaderID' => $row['T_OrderHeaderID'],
                  'T_SampleTypeID' => $sample_id,
                  'T_BarcodeLabBarcode' => $barcode,
                  'T_BarcodeLabCounter' => $counter_barcode
            ];
            $barcode = $barcode . $counter_barcode;
            $sql = "INSERT INTO t_barcodelab(
                  T_BarcodeLabT_OrderHeaderID,
                  T_BarcodeLabBarcode,
                  T_BarcodeLabT_SampleTypeID,
                  T_BarcodeLabCounter)
                  VALUES ('{$row['T_OrderHeaderID']}','{$barcode}','{$sample_id}','{$counter_barcode}')";
                  //echo $sql."<br>";
            $qry = $this->db_onedev->query($sql);
            //echo $this->db_smartone->last_query();
            if (!$qry) {
               $this->db_onedev->trans_rollback();
               $this->sys_error_db(["status" => "ERR", "message" => "insert t_barcodelab | " .
               $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
               exit;
            }

            $last_id_barcode = $this->db_onedev->insert_id();
            if ($isnonlab == "") {
                  $sql = "INSERT INTO t_ordersample(
                        T_OrderSampleT_OrderHeaderID,
                        T_OrderSampleT_SampleTypeID,
                        T_OrderSampleT_BarcodeLabID,
                        T_OrderSampleBarcode,
                        T_OrderSampleT_SampleStationID)
                        VALUES ('{$row['T_OrderHeaderID']}','{$sample_id}','{$last_id_barcode}','{$barcode}','{$samplestation_id}')";
                  //echo $sql."<br>";
                  $qry = $this->db_onedev->query($sql);
                  if (!$qry) {
                     $this->db_onedev->trans_rollback();
                     $this->sys_error_db(["status" => "ERR", "message" => "insert t_ordersample | " .
                     $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
                     exit;
                  }
                  
            }
         }
  
      }

      $this->db_onedev->trans_commit();

      return $new_insert;
   }

   function save_test_replace(){
		try {
         //# cek token valid
         if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
         }

         //# ambil parameter input
         $xuserid = $this->sys_user['M_UserID'];
         $prm = $this->sys_input;
         $md5_code = md5(date('YmdHis').rand(5, 15));
         $orderid = $prm['orderid'];
         $sql = "SELECT * FROM t_orderheader WHERE T_OrderHeaderID = {$orderid}";
         $order_header = $this->db_onedev->query($sql)->row_array();
         $testnew = $prm['testnew'];
         $testold = $prm['testold'];
         $data_log = array();
         //print_r($testnew);
         //#begin data before update
         $query = "	SELECT T_OrderDetailID as xid,
            IF(T_OrderDetailT_TestIsPanel = 'Y','PN','SINGLE') as type,
            'Y' as status,
            T_OrderDetailIsCito as cito,
            T_OrderDetailT_TestID as testid,
            T_OrderDetailT_TestCode as testcode,
            T_OrderDetailT_TestSasCode as testsascode,
            T_OrderDetailT_TestName as testname,
            T_OrderDetailPrice as bruto,
            T_OrderDetailDiscTotal as discount,
            T_OrderDetailTotal as total
            FROM t_orderdetail 
            WHERE  
            T_OrderDetailT_OrderHeaderID = {$orderid} AND T_OrderDetailT_TestIsPrice = 'Y' AND T_OrderDetailIsActive = 'Y'";
         //echo $query;
         $data_log['before']['detail'] = $this->db_onedev->query($query)->result_array(); 

         $sql = "SELECT CONCAT(T_TestT_SampleTypeID,'-',T_SampleTypeSuffix) as id
            FROM t_orderdetail
            JOIN t_test ON T_OrderDetailT_TestID = T_TestID
            JOIN t_sampletype ON T_TestT_SampleTypeID = T_SampleTypeID
            WHERE T_OrderDetailT_OrderHeaderID = '{$orderid}' AND T_OrderDetailIsActive = 'Y' 
            GROUP BY T_TestT_SampleTypeID";
         $rst_sampletypes_before = $this->db_onedev->query($sql)->result();
         $sampletypes_before = array();
         foreach($rst_sampletypes_before as $k => $v){
            $sampletypes_before[] = $v->id;
         }
         //#begin data before update
         $data_new_log = array();
         //#begin loop tests
         foreach($testnew as $i => $v){
            //# add test
            if(($v['xid'] == 0 || $v['xid'] == '0')){
               $isPanel = 'N';
               $xtestid = $v['pxid'];
               $xdetailid = $testold['xid'];
               if($v['type'] == 'PN'){
                  $isPanel = 'Y';
                  $xtestid = 0;
               }
               $cito = 'N';
               if($v['cito'])
                  $cito = 'Y';
               $isPanelChildren = 'N';
               $isPanelChildrenPrintNota = 'N';
			// echo $xtestid;
         // echo $xdetailid;  
			    $check_exist = $this->double_test_insert($orderid,$xtestid);
			    if($check_exist){
               $sqlt_orderdetailorder = "INSERT INTO t_orderdetailorder(
						T_OrderDetailOrderT_OrderHeaderID,
						T_OrderDetailOrderT_TestID,
						T_OrderDetailOrderT_TestName,
						T_OrderDetailOrderIsPacket,
						T_OrderDetailOrderPacketType,
						T_OrderDetailOrderT_PacketID,	
						T_OrderDetailOrderT_PacketName,	
						T_OrderDetailOrderCreated,
						T_OrderDetailOrderCreatedUserID,
						T_OrderDetailOrderLastUpdated,
						T_OrderDetailOrderLastUpdatedUserID,
						T_OrderDetailOrderJsonChildren)
			VALUES(
				      {$orderid},
						{$xtestid},
						'{$v['test_name']}',
						'N',
						'',
						0,	
						'',
						now(),
						{$xuserid},
						now(),
						now(),
						'')";
            //echo $sqlorder;
            $queryt_orderdetailorder = $this->db_onedev->query($sqlt_orderdetailorder);
            if (!$queryt_orderdetailorder) {
              $this->sys_error_db(["status" => "ERR", "message" => "insert t_orderdetailorder | " .
                $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
              exit;
            }
            //$this->db_onedev->trans_commit();
            $detail_id = $this->db_onedev->insert_id();

            

					$sql = "insert into t_orderdetail(
               T_OrderDetailT_OrderDetailOrderID,
					  T_OrderDetailT_OrderHeaderID,
					  T_OrderDetailT_TestID, 
					  T_OrderDetailT_TestCode,
					  T_OrderDetailT_TestSasCode,							
					  T_OrderDetailT_TestName,
					  T_OrderDetailT_TestIsResult,
					  T_OrderDetailT_TestIsPrice,
					  T_OrderDetailIsCito,
					  T_OrderDetailPrice,
					  T_OrderDetailPriceForDisc,
					  T_OrderDetailDisc,
					  T_OrderDetailDiscAmount,
					  T_OrderDetailDiscTotal,
					  T_OrderDetailTotal,
					  T_OrderDetailT_TestIsPanel,
					  T_OrderDetailT_TestIsPanelChildren,
					  T_OrderDetailT_TestIsPanelChildrenPrintNota,
					  T_OrderDetailUserID,
					  T_OrderDetailCreated,
					  T_OrderDetailLastUpdated
					) 
					values(
         {$detail_id},
					   {$orderid},
					   {$xtestid},
					   '{$v['pxcode']}',
					   '{$v['pxsascode']}',
					   '{$v['test_name']}',
					   '{$v['isresult']}',
					   'Y',
					   '{$cito}',
					   '0',
					   '0',
					   '0',
					   '0',
					   '0',
					   '0',
					   '{$isPanel}',
					   '{$isPanelChildren}',
					   '{$isPanelChildrenPrintNota}',
					   {$xuserid},
					   NOW(),
					   NOW()
					)";
					//echo $sql;
               
               $query = $this->db_onedev->query($sql);
               $last_detail_id = $this->db_onedev->insert_id();
               $query_generate_location = $this->generate_location($orderid,$xtestid); 

               $sqlt_orderdetailreplace = "INSERT INTO t_orderdetailreplace(
                  T_OrderDetailReplaceT_OrderHeaderID,
                  T_OrderDetailReplaceT_OrderDetailID,	
                  T_OrderDetailReplaceT_TestID,		
                  T_OrderDetailReplaceUserID,
                  T_OrderDetailReplaceCreated)
                           VALUES(
                                    {$orderid},
                                    {$xdetailid},
                                    {$testold['pxid']},
                                    {$xuserid},
                                    now())";
                              //echo $sqlorder;
                              $queryt_orderdetailreplace = $this->db_onedev->query($sqlt_orderdetailreplace);
                              if (!$queryt_orderdetailreplace) {
                                $this->sys_error_db(["status" => "ERR", "message" => "insert t_orderdetailreplace | " .
                                  $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
                                exit;
                              }
               
                              $sqlt_samplingso = "UPDATE t_samplingso SET T_SamplingSoT_TestID = {$xtestid}
                              WHERE T_SamplingSoT_OrderHeaderID = {$orderid} AND T_SamplingSoT_TestID = {$testold['pxid']}";
                                             //echo $sqlorder;
                                             $queryt_samplingso = $this->db_onedev->query($sqlt_samplingso);
                                             if (!$queryt_samplingso) {
                                               $this->sys_error_db(["status" => "ERR", "message" => "insert t_samplingso | " .
                                                 $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
                                               exit;
                                             }
               
                              $data_log = array();
                              $query = "SELECT * FROM t_orderdetail WHERE T_OrderDetailID = {$xdetailid}";
                              $data_log['before']['detail'] = $this->db_onedev->query($query)->result_array(); 
                              $query = "SELECT * FROM t_orderdetail WHERE T_OrderDetailID = {$last_detail_id}";
                              $data_log['after']['detail'] = $this->db_onedev->query($query)->result_array();
                              $json_dt_log = json_encode($data_log);
                              $sqllog = "INSERT INTO cpone_log.log_fo(
                                             Log_FoT_OrderHeaderID,	
                                             Log_FoDate,	
                                             Log_FoCode,	
                                             Log_FoJson,
                                             Log_FoUserID)
                                             VALUES ('{$orderid}',now(),'GANTI','{$json_dt_log}','{$xuserid}')";
                                             $querylog = $this->db_onedev->query($sqllog);
                                             if (!$querylog) {
                                             
                                             $this->sys_error_db(["status" => "ERR", "message" => "update log_fo | " .
                                             $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
                                             
                                             }

				}


               if($v['type'] == 'PX'){
                  $sql = "SELECT * 
                     FROM t_test
                     LEFT JOIN t_sampletype ON T_TestT_SampleTypeID = T_SampleTypeID
                     LEFT JOIN t_bahan ON T_SampleTypeT_BahanID = T_BahanID
                     LEFT JOIN t_samplestation ON T_BahanT_SampleStationID = T_SampleStationID
                     WHERE
                     T_TestSasCode LIKE '{$v['pxsascode']}%' AND T_TestID <> {$v['pxid']} AND T_TestIsActive = 'Y'";
                  //echo $sql;
                  $rst_query = $this->db_onedev->query($sql)->result_array();
                  if($rst_query){
                     foreach($rst_query as $kkxz => $vvxz){
						$check_exist = $this->double_test_insert($orderid,$vvxz['T_TestID']);
						if($check_exist){
                     $sqlt_orderdetailorder = "INSERT INTO t_orderdetailorder(
                        T_OrderDetailOrderT_OrderHeaderID,
                        T_OrderDetailOrderT_TestID,
                        T_OrderDetailOrderT_TestName,
                        T_OrderDetailOrderIsPacket,
                        T_OrderDetailOrderPacketType,
                        T_OrderDetailOrderT_PacketID,	
                        T_OrderDetailOrderT_PacketName,	
                        T_OrderDetailOrderCreated,
                        T_OrderDetailOrderCreatedUserID,
                        T_OrderDetailOrderLastUpdated,
                        T_OrderDetailOrderLastUpdatedUserID,
                        T_OrderDetailOrderJsonChildren)
               VALUES(
                        {$orderid},
                        {$vvxz['T_TestID']},
                        '{$vvxz['T_TestName']}',
                        'N',
                        '',
                        0,	
                        '',
                        now(),
                        {$xuserid},
                        now(),
                        now(),
                        '')";
                  //echo $sqlorder;
                  $queryt_orderdetailorder = $this->db_onedev->query($sqlt_orderdetailorder);
                  if (!$queryt_orderdetailorder) {
                    $this->sys_error_db(["status" => "ERR", "message" => "insert t_orderdetailorder | " .
                      $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
                    exit;
                  }
                  //$this->db_onedev->trans_commit();
                  $detail_id = $this->db_onedev->insert_id();

                  
                     
							$sql = "insert into t_orderdetail(
									   T_OrderDetailT_OrderDetailOrderID,
					  T_OrderDetailT_OrderHeaderID,
									   T_OrderDetailT_TestID, 
									   T_OrderDetailT_TestCode,
									   T_OrderDetailT_TestSasCode,							
									   T_OrderDetailT_TestName,
									   T_OrderDetailT_TestIsResult,
									   T_OrderDetailT_TestIsPrice,
									   T_OrderDetailT_TestIsPanel,
									   T_OrderDetailT_TestIsPanelChildren,
									   T_OrderDetailT_TestIsPanelChildrenPrintNota,
									   T_OrderDetailIsCito,
									   T_OrderDetailUserID,
									   T_OrderDetailCreated,
									   T_OrderDetailLastUpdated
					) 
					values(
         {$detail_id},
										{$orderid},
										{$vvxz['T_TestID']},
										'{$vvxz['T_TestCode']}',
										'{$vvxz['T_TestSasCode']}',
										'{$vvxz['T_TestName']}',
										'{$vvxz['T_TestIsResult']}',
										'{$vvxz['T_TestIsPrice']}',
										'N',
										'N',
										'{$vvxz['T_TestIsPrintNota']}',
										'{$cito}',
										{$xuserid},
										NOW(),
										NOW()
									)";
							//echo $sql;
							
						   $query = $this->db_onedev->query($sql);
                     $last_detail_id = $this->db_onedev->insert_id();
                     $query_generate_location = $this->generate_location($orderid,$vvxz['T_TestID']); 

                     $data_log = array();
                              $query = "SELECT * FROM t_orderdetail WHERE T_OrderDetailID = {$xdetailid}";
                              $data_log['before']['detail'] = $this->db_onedev->query($query)->result_array(); 
                              $query = "SELECT * FROM t_orderdetail WHERE T_OrderDetailID = {$last_detail_id}";
                              $data_log['after']['detail'] = $this->db_onedev->query($query)->result_array();
                              $json_dt_log = json_encode($data_log);
                              $sqllog = "INSERT INTO cpone_log.log_fo(
                                             Log_FoT_OrderHeaderID,	
                                             Log_FoDate,	
                                             Log_FoCode,	
                                             Log_FoJson,
                                             Log_FoUserID)
                                             VALUES ('{$orderid}',now(),'GANTI','{$json_dt_log}','{$xuserid}')";
                                             $querylog = $this->db_onedev->query($sqllog);
                                             if (!$querylog) {
                                             
                                             $this->sys_error_db(["status" => "ERR", "message" => "update log_fo | " .
                                             $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
                                             
                                             }
						}
                        

                     }
                  }
				  
				  
               }

               
			}
            
         
		 }
         //#end loop tests	

      $sql = "SELECT SUM(T_OrderDetailTotal) as total FROM t_orderdetail WHERE T_OrderDetailT_OrderHeaderID = {$orderid} AND T_OrderDetailIsActive = 'Y'";
      $query = $this->db_onedev->query($sql);
      if(!$query){
         $this->sys_error_db(["status" => "ERR", "message" => "error get orderdetail" .
          $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
          exit;
      }
      $get_total = $query->result_array();
      $total = $get_total[0]['total'];

      $sql = "UPDATE t_orderheader SET T_OrderHeaderSubTotal = ?, T_OrderHeaderTotal = ? WHERE T_OrderHeaderID = ?";
      $query = $this->db_onedev->query($sql,array($total,$total,$orderid));
      if(!$query){
         $this->sys_error_db(["status" => "ERR", "message" => "error update orderheader" .
          $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
          exit;
      }


        
		 
		$sql = "UPDATE t_orderdetail 
				JOIN t_test ON T_OrderDetailT_TestID = T_TestID
				SET 
				T_OrderDetailT_TestName = T_TestName,
				T_OrderDetailT_TestCode = T_TestCode

				WHERE
				( T_OrderDetailT_TestName = '' OR T_OrderDetailT_TestCode  = '') AND
				T_OrderDetailT_OrderHeaderID = {$orderid}";
		$this->db_onedev->query($sql);

         //echo $this->db_onedev->last_query();
         $result = array ("total" => 1, "records" => array('testall' => $rtntestregistration));
         $this->sys_ok($result);

      } catch(Exception $exc) {
         $message = $exc->getMessage();
         $this->sys_error($message);
      }
	}

   function save_packet_replace(){
		try {
         //# cek token valid
         if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
         }

         //# ambil parameter input
         $userid = $this->sys_user['M_UserID'];
         $prm = $this->sys_input;
         $orderid = $prm['orderid'];
         $labnumber = $prm['number'];
         $oldpacket_code = $prm['packetold'];
         $newpacket_code = $prm['packetnew'];

         $sql = " SELECT *
                 FROM t_packet 
                 JOIN mgm_mcupacket ON Mgm_McuPacketT_PacketID = T_PacketID AND Mgm_McuPacketIsActive = 'Y'
                 JOIN t_orderheader ON T_OrderHeaderMgm_McuID = Mgm_McuPacketMgm_McuID AND T_OrderHeaderLabNumber = ?
                 WHERE
                 T_PacketSasCode = ? AND T_PacketIsActive = 'Y' LIMIT 1";
        $query = $this->db_onedev->query($sql,array($labnumber,$newpacket_code));

        if (!$query) {
            //$this->db_onedev->trans_rollback();
            echo $this->db_onedev->last_query();
            $this->sys_error_db('error', 'Error get order: ' . $this->db_onedev);
            exit;
        }

        $data_new_packet = $query->row_array();

        if($data_new_packet){
            $sql = " SELECT *
                    FROM t_orderheader
                    JOIN t_orderdetailorder ON T_OrderDetailOrderT_OrderHeaderID = T_OrderHeaderID AND T_OrderDetailOrderIsActive = 'Y' AND
                    T_OrderDetailOrderIsPacket = 'Y'
                    JOIN t_packet ON T_PacketSasCode = ? AND T_OrderDetailOrderT_PacketID = T_PacketID
                    WHERE
                    T_OrderHeaderLabNumber = ? LIMIT 1";
            $query = $this->db_onedev->query($sql,array($oldpacket_code,$labnumber));
            //echo $this->db_onedev->last_query();
            if (!$query) {
                //$this->db_onedev->trans_rollback();
                echo $this->db_onedev->last_query();
                $this->sys_error_db('error', 'Error get order: ' . $this->db_onedev);
                exit;
            }
            //echo $this->db_onedev->last_query();

            $order = $query->row_array();

            if($order){
                $this->db_onedev->trans_begin();

                $new_packet_id = $data_new_packet['T_PacketID'];
                $old_packet_id = $order['T_PacketID'];

                //INSERT NEW PACKET INTO ORDER DETAIL ORDER
                $sql = "INSERT INTO t_orderdetailorder (
                            T_OrderDetailOrderT_OrderHeaderID,
                            T_OrderDetailOrderIsPacket,
                            T_OrderDetailOrderPacketType,
                            T_OrderDetailOrderT_PacketID,
                            T_OrderDetailOrderT_PacketName,
                            T_OrderDetailOrderCreated,
                            T_OrderDetailOrderCreatedUserID
                        )
                        VALUES(?,'Y',?,?,?,NOW(),?)";

                $query = $this->db_onedev->query($sql,array(
                    $order['T_OrderHeaderID'],
                    $data_new_packet['T_PacketType'],
                    $data_new_packet['T_PacketID'],
                    $data_new_packet['T_PacketName'],
                    $userid
                ));

                if (!$query) {
                    $this->db_onedev->trans_rollback();
                    echo $this->db_onedev->last_query();
                    $this->sys_error_db('error', 'Error insert new packet into orderdetail order: ' . $this->db_onedev);
                    exit;
                }

                $new_orderdetail_orderid = $this->db_onedev->insert_id();

                $sql = "SELECT * 
                        FROM t_packetdetail
                        JOIN t_test ON T_PacketDetailT_TestID = T_TestID
                        WHERE
                        T_PacketDetailT_PacketID = ? AND 
                        T_PacketDetailIsActive = 'Y'";
                
                $query = $this->db_onedev->query($sql,array($new_packet_id));
                
                if (!$query) {
                    $this->db_onedev->trans_rollback();
                    echo $this->db_onedev->last_query();
                    $this->sys_error_db('error', 'Error get packet detail: ' . $this->db_onedev);
                    exit;
                }

                $packet_details = $query->result_array();
                foreach ($packet_details as $key => $value) {
                    $data_detail = [];
                    $sql = "SELECT *
                            FROM t_orderdetail
                            WHERE
                            T_OrderDetailT_OrderHeaderID = ? AND 
                            T_OrderDetailT_OrderDetailOrderID = ? AND
                            T_OrderDetailT_TestID = ? AND
                            T_OrderDetailIsActive = 'Y'";
                    $query = $this->db_onedev->query($sql,array($order['T_OrderHeaderID'],$order['T_OrderDetailOrderID'],$value['T_PacketDetailT_TestID']));
                
                    if (!$query) {
                        $this->db_onedev->trans_rollback();
                        echo $this->db_onedev->last_query();
                        $this->sys_error_db('error', 'Error get packet detail: ' . $this->db_onedev);
                        exit;
                    }

                    //echo $this->db_onedev->last_query();

                    $data_detail = $query->result_array();
                    $orderdetail_sascode = $value['T_TestSasCode']."%";
                    //echo "exist-order-lama-".count($data_detail);
                    if(count($data_detail) > 0){
                        $sql = "UPDATE t_orderdetail SET
                                T_OrderDetailPrice = ?,
                                T_OrderDetailPriceForDisc = ?,
                                T_OrderDetailTotal = ?
                                WHERE
                                T_OrderDetailT_OrderHeaderID = ? AND
                                T_OrderDetailT_TestID = ? AND 
                                T_OrderDetailIsActive = 'Y'
                                ";
                        $query = $this->db_onedev->query($sql,array(
                            $value['T_PacketDetailPrice'],
                            $value['T_PacketDetailPrice'],
                            $value['T_PacketDetailPrice'],
                            $order['T_OrderHeaderID'],
                            $value['T_TestID']
                        ));

                        if (!$query) {
                            $this->db_onedev->trans_rollback();
                            echo $this->db_onedev->last_query();
                            $this->sys_error_db('error', 'Error update price and total order detail: ' . $this->db_onedev);
                            exit;
                        }
                                

                        // UPDATE order detail lama diganti orderdetailt_orderdetailorderid baru
                        $detail = $data_detail[0];
                        $sql = "UPDATE t_orderdetail SET 
                                    T_OrderDetailT_OrderDetailOrderID = ?, 
                                    T_OrderDetailLastUpdatedUserID = ?
                                WHERE
                                    T_OrderDetailT_OrderDetailOrderID = ? AND
                                    T_OrderDetailT_TestSasCode LIKE ?
                                ";

                        $query = $this->db_onedev->query($sql,array(
                            $new_orderdetail_orderid,
                            $userid,
                            $order['T_OrderDetailOrderID'],
                            $orderdetail_sascode
                        ));

                        if (!$query) {
                            $this->db_onedev->trans_rollback();
                            echo $this->db_onedev->last_query();
                            $this->sys_error_db('error', 'Error update order detail: ' . $this->db_onedev);
                            exit;
                        }


                    }else{
                        //insert ke orderdetail jika tidak ditemukan
                        $sql = "INSERT INTO t_orderdetail (
                                    T_OrderDetailT_OrderHeaderID,
                                    T_OrderDetailT_OrderDetailOrderID,
                                    T_OrderDetailT_TestID,
                                    T_OrderDetailT_TestCode,
                                    T_OrderDetailT_TestSasCode,
                                    T_OrderDetailT_TestName,
                                    T_OrderDetailT_TestIsResult,
                                    T_OrderDetailT_TestIsPanel,
                                    T_OrderDetailT_TestIsPrice,
                                    T_OrderDetailPrice,
                                    T_OrderDetailTotal,
                                    T_OrderDetailCreated,
                                    T_OrderDetailCreatedUserID
                                )
                                SELECT ?,
                                ?,
                                T_TestID,
                                T_TestCode,
                                T_TestSasCode,
                                T_TestName,
                                T_TestIsResult,
                                'N',
                                T_TestIsPrice,
                                if(T_TestSasCode = ?, ?,0),
                                if(T_TestSasCode = ?, ?,0),
                                NOW(),
                                ?
                                FROM t_test
                                WHERE
                                T_TestSasCode LIKE ?
                                ";
                        $query = $this->db_onedev->query($sql,array(
                            $order['T_OrderHeaderID'],
                            $new_orderdetail_orderid,
                            $value['T_TestSasCode'],
                            $value['T_PacketDetailPrice'],
                            $value['T_TestSasCode'],
                            $value['T_PacketDetailPrice'],
                            $userid,
                            $orderdetail_sascode
                        ));

                        //echo $this->db_onedev->last_query();
                        if (!$query) {
                            $this->db_onedev->trans_rollback();
                            echo $this->db_onedev->last_query();
                            $this->sys_error_db('error', 'Error insert order detail: ' . $this->db_onedev);
                            exit;
                        }
                    }
                }

                //update isactive = N test lama yg tdk ada di paket baru
                $sql = "UPDATE t_orderdetail SET T_OrderDetailIsActive = 'N', T_OrderDetailDeleted = NOW(), T_OrderDetailDeletedUserID = ?
                        WHERE
                        T_OrderDetailT_OrderDetailOrderID = ? AND T_OrderDetailIsActive = 'Y'";
                $query = $this->db_onedev->query($sql,array(
                    $userid,
                    $order['T_OrderDetailOrderID']
                ));

                if (!$query) {
                    $this->db_onedev->trans_rollback();
                    echo $this->db_onedev->last_query();
                    $this->sys_error_db('error', 'Error update isactive N order detail: ' . $this->db_onedev);
                    exit;
                }

                $sql = "UPDATE t_orderdetailorder SET T_OrderDetailOrderIsActive = 'N', T_OrderDetailOrderDeleted = NOW(), T_OrderDetailOrderDeletedUserID = ?
                        WHERE
                        T_OrderDetailOrderID = ?";
                $query = $this->db_onedev->query($sql,array(
                    $userid,
                    $order['T_OrderDetailOrderID']
                ));

                if (!$query) {
                    $this->db_onedev->trans_rollback();
                    echo $this->db_onedev->last_query();
                    $this->sys_error_db('error', 'Error update isactive N order detail order: ' . $this->db_onedev);
                    exit;
                }

                $sql = "SELECT T_OrderHeaderLabNumber, T_TestID, T_SampleTypeID , T_SampleTypeSuffix, T_SampleStationIsNonLab, T_SampleStationID
                        FROM t_orderheader
                        JOIN t_orderdetail ON t_orderheaderid = t_orderdetailt_orderheaderid AND t_orderdetailIsActive = 'Y'
                        JOIN t_test ON t_orderdetailt_testid = t_testid AND T_TestIsResult = 'Y'
                        JOIN group_resultdetail ON Group_ResultDetailT_TestID = T_TestID AND Group_resultDetailIsActive = 'Y'
                        JOIN group_result ON Group_ResultDetailGroup_ResultID = Group_ResultID AND Group_ResultFlagNonLab= 'N'
                        JOIN t_sampletype ON T_TestT_SampleTypeID = T_SampleTypeID
                        JOIN t_bahan ON T_SampleTypeT_BahanID = T_BahanID
                        JOIN t_samplestation ON T_BahanT_SampleStationID = T_SampleStationID
                        WHERE T_OrderHeaderID = ?
                        GROUP BY T_SampleTypeID";
                $query = $this->db_onedev->query($sql,array(
                    $order['T_OrderHeaderID']
                ));

                $order_samples = $query->result_array();
                if($order_samples){
                    $sql = "UPDATE t_ordersample SET T_OrderSampleIsActive = 'X'
                            WHERE
                            T_OrderSampleT_OrderHeaderID = ? AND T_OrderSampleIsActive = 'Y'";
                    $query = $this->db_onedev->query($sql,array(
                        $order['T_OrderHeaderID']
                    ));
                    if(!$query){
                        $this->db_onedev->trans_rollback();
                        $this->sys_error_db(["status" => "ERR", "message" => "error update ordersample temporary start" .
                         $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
                        exit;
                    }

                    foreach ($order_samples as $sample) {
                        $exist_sample = [];
                        $sql = "SELECT *
                                FROM t_ordersample
                                WHERE
                                T_OrderSampleT_OrderHeaderID = ? AND 
                                T_OrderSampleT_SampleTypeID = ? AND 
                                T_OrderSampleIsActive = 'X'";
                        $query = $this->db_onedev->query($sql,array(
                            $order['T_OrderHeaderID'],
                            $sample['T_SampleTypeID']
                        ));
                        //echo  $this->db_onedev->last_query();
                        $dt_exist_sample = $query->result_array();
                        if(count($dt_exist_sample) > 0){
                            $exist_sample = $dt_exist_sample[0];
                            $sql  = "UPDATE t_ordersample SET T_OrderSampleIsActive = 'Y' WHERE T_OrderSampleID = ?";
                            $query = $this->db_onedev->query($sql,array(
                                $exist_sample['T_OrderSampleID']
                            ));
                            if(!$query){
                                $this->db_onedev->trans_rollback();
                                $this->sys_error_db(["status" => "ERR", "message" => "error update ordersample actived" .
                                 $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
                                exit;
                            }
                            //echo $this->db_onedev->last_query();
                        }else{
                            $barcode = $labnumber . $sample['T_SampleTypeSuffix']."1";

                            $sql = "INSERT INTO t_barcodelab(
                                    T_BarcodeLabT_OrderHeaderID,
                                    T_BarcodeLabBarcode,
                                    T_BarcodeLabT_SampleTypeID,
                                    T_BarcodeLabCounter,
                                    T_BarcodeLabCreated,
                                    T_BarcodeLabCreatedUserID)
                                    VALUES (?,?,?,?,NOW(),?)";
                            $query = $this->db_onedev->query($sql,array(
                                $order['T_OrderHeaderID'],
                                $barcode,
                                $sample['T_SampleTypeID'],
                                "1",
                                $userid
                            ));
                            if(!$query){
                                $this->db_onedev->trans_rollback();
                                $this->sys_error_db(["status" => "ERR", "message" => "error insert barcode lab" .
                                 $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
                                exit;
                            }

                            $last_id_barcode = $this->db_onedev->insert_id();

                            $sql = "INSERT INTO t_ordersample(
                                    T_OrderSampleT_OrderHeaderID,
                                    T_OrderSampleT_SampleTypeID,
                                    T_OrderSampleT_BarcodeLabID,
                                    T_OrderSampleBarcode,
                                    T_OrderSampleT_SampleStationID,
                                    T_OrderSampleCreated,
                                    T_OrderSampleCreatedUserID)
                                    VALUES(
                                        ?,?,?,?,?,NOW(),?
                                    )";
                            $query = $this->db_onedev->query($sql,array(
                                $order['T_OrderHeaderID'],
                                $sample['T_SampleTypeID'],
                                $last_id_barcode,
                                $barcode,
                                $sample['T_SampleStationID'],
                                $userid
                            ));
                            //echo $this->db_onedev->last_query();
                        }
                    }

                    $sql = "UPDATE t_ordersample SET T_OrderSampleIsActive = 'N'
                            WHERE
                            T_OrderSampleT_OrderHeaderID = ? AND T_OrderSampleIsActive = 'X'";
                    $this->db_onedev->query($sql,array(
                        $order['T_OrderHeaderID']
                    ));
                    if(!$query){
                        $this->db_onedev->trans_rollback();
                        $this->sys_error_db(["status" => "ERR", "message" => "error update ordersample not used" .
                         $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
                        exit;
                    }
                }   

                $sql = "SELECT *
                        FROM t_orderdetail
                        JOIN t_test ON T_OrderDetailT_TestID = T_TestID 
                        JOIN nonlab_template_mapping ON NonlabTemplateMappingNat_TestID = T_TestNat_TestID AND NonlabTemplateMappingIsActive = 'Y'
                        JOIN nonlab_template ON NonlabTemplateMappingNonlabTemplateID = NonlabTemplateID AND NonlabTemplateIsActive = 'Y'
                        JOIN group_resultdetail ON Group_ResultDetailT_TestID  = T_TestID 
                        JOIN group_result ON Group_ResultDetailGroup_ResultID = Group_ResultID AND Group_ResultFlagNonLab = 'Y'
                        WHERE
                        T_OrderDetailT_OrderHeaderID = {$order['T_OrderHeaderID']} AND T_OrderDetailIsActive = 'Y'
                        GROUP BY T_TestID";
                    // echo $sql;
                $query = $this->db_onedev->query($sql);

                $order_nonlab = $query->result_array();
                if(count($order_nonlab) > 0){
                    $sql = "UPDATE so_resultentry SET So_ResultEntryIsActive = 'X'
                            WHERE
                            So_ResultEntryT_OrderHeaderID = ? AND So_ResultEntryIsActive = 'Y'";
                    $this->db_onedev->query($sql,array(
                        $order['T_OrderHeaderID']
                    ));
                    if(!$query){
                        $this->db_onedev->trans_rollback();
                        $this->sys_error_db(["status" => "ERR", "message" => "error update so_resultentry temporary 1" .
                         $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
                        exit;
                    }

                    foreach ($order_nonlab as $nonlab) {
                        $exist_so = [];
                        $sql = "SELECT *
                                FROM so_resultentry
                                WHERE
                                So_ResultEntryT_OrderHeaderID = ? AND
                                So_ResultEntryT_OrderDetailID = ? AND
                                So_ResultEntryIsActive = 'X'";
                        $query = $this->db_onedev->query($sql,array(
                            $order['T_OrderHeaderID'],
                            $nonlab['T_OrderDetailID']
                        ));
            
                        $dt_exist_so = $query->result_array();
                        if(count($dt_exist_so) > 0){
                            $exist_so = $dt_exist_so[0];
                            $sql = "UPDATE so_resultentry SET So_ResultEntryIsActive = 'Y'
                                WHERE
                                So_ResultEntryID = ? AND So_ResultEntryIsActive = 'X'";
                            $this->db_onedev->query($sql,array(
                                $exist_so['So_ResultEntryID']
                            ));
                        }
                    }

                    $sql = "UPDATE so_resultentry SET So_ResultEntryIsActive = 'N'
                            WHERE
                            So_ResultEntryT_OrderHeaderID = ? AND So_ResultEntryIsActive = 'X'";
                    $this->db_onedev->query($sql,array(
                        $order['T_OrderHeaderID']
                    ));
                    if(!$query){
                        $this->db_onedev->trans_rollback();
                        $this->sys_error_db(["status" => "ERR", "message" => "error delete so_resultentry" .
                         $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
                        exit;
                    }
                    $sqlt_orderdetailreplace = "INSERT INTO t_orderdetailreplace(
                     T_OrderDetailReplaceT_OrderHeaderID,
                     T_OrderDetailReplaceT_OrderDetailID,	
                     T_OrderDetailReplaceT_TestID,	
                     T_OrderDetailReplaceIsPacket,	
                     T_OrderDetailReplaceUserID,
                     T_OrderDetailReplaceCreated)
                              VALUES(
                                       {$orderid},
                                       {$order['T_OrderDetailOrderID']},
                                       {$old_packet_id},
                                       'Y',
                                       {$userid},
                                       now())";
                                 //echo $sqlorder;
                                 $queryt_orderdetailreplace = $this->db_onedev->query($sqlt_orderdetailreplace);
                                 if (!$queryt_orderdetailreplace) {
                                    $this->db_onedev->trans_rollback();
                                   $this->sys_error_db(["status" => "ERR", "message" => "insert t_orderdetailreplace | " .
                                     $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
                                   exit;
                                 }

                }

                $sql = "SELECT T_SampleStationID, M_LocationID
                        FROM t_orderdetail
                        JOIN t_test ON T_OrderDetailT_TestID = T_TestID AND T_TestIsActive = 'Y'
                        JOIN t_sampletype ON T_TestT_SampleTypeID = T_SampleTypeID
                        JOIN t_bahan ON T_SampleTypeT_BahanID = T_BahanID
                        JOIN t_samplestation ON T_BahanT_SampleStationID = T_SampleStationID AND T_SampleStationIsActive = 'Y'
                        JOIN m_location ON M_LocationT_SampleStationID = T_SampleStationID AND M_LocationIsActive = 'Y'
                        WHERE
                        T_OrderDetailT_OrderHeaderID = ? AND T_OrderDetailIsActive = 'Y'
                        GROUP BY T_SampleStationID ";
                $query = $this->db_onedev->query($sql,array(
                    $order['T_OrderHeaderID']
                ));

                if (!$query) {
                    $this->db_onedev->trans_rollback();
                    echo $this->db_onedev->last_query();
                    $this->sys_error_db('error', 'Error get station: ' . $this->db_onedev);
                    exit;
                }
                //echo $this->db_onedev->last_query();

                $order_locations = $query->result_array();
                if(count($order_locations) > 0){
                    $sql = "UPDATE t_order_location SET T_OrderLocationIsActive = 'X' WHERE T_OrderLocationT_OrderHeaderID = ? AND T_OrderLocationIsActive = 'Y'";
                    $query = $this->db_onedev->query($sql,array(
                        $order['T_OrderHeaderID']
                    ));
                    if(!$query){
                        $this->db_onedev->trans_rollback();
                        $this->sys_error_db(["status" => "ERR", "message" => "error update location temporary" .
                         $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
                        exit;
                    }
                    //echo $this->db_onedev->last_query();

                    foreach ($order_locations as $k => $v) {
                        $exist_order_location = [];
                        $sql = " SELECT *
                                FROM t_order_location
                                WHERE
                                T_OrderLocationT_OrderHeaderID = ? AND 
                                T_OrderLocationT_SampleStationID= ? AND 
                                T_OrderLocationM_LocationID = ? AND
                                T_OrderLocationIsActive = 'X' LIMIT 1";
                        $query = $this->db_onedev->query($sql,array(
                            $order['T_OrderHeaderID'],
                            $v['T_SampleStationID'],
                            $v['M_LocationID']
                        ));
                        $dt_exist_order_location = $query->result_array();
                        if(count($dt_exist_order_location) > 0){
                            $exist_order_location = $dt_exist_order_location[0];
                            $sql = "UPDATE t_order_location SET T_OrderLocationIsActive = 'Y' WHERE T_OrderLocationID = ? ";
                            $query = $this->db_onedev->query($sql,array(
                                $exist_order_location['T_OrderLocationID']
                            ));
                           // echo $this->db_onedev->last_query();
                        }else{
                            $sql = "INSERT INTO t_order_location (
                                T_OrderLocationT_OrderHeaderID,
                                T_OrderLocationM_LocationID,
                                T_OrderLocationT_SampleStationID,
                                T_OrderLocationCreated,
                                T_OrderLocationUserID
                            )
                            VALUES(?,?,?,NOW(),?)";
                            $query = $this->db_onedev->query($sql,array(
                                $order['T_OrderHeaderID'],
                                $v['M_LocationID'],
                                $v['T_SampleStationID'],
                                $userid
                            ));
                           // echo $this->db_onedev->last_query();
                        }
                    }

                    $sql = "UPDATE t_order_location SET T_OrderLocationIsActive = 'N', T_OrderLocationUserID = ? WHERE T_OrderLocationT_OrderHeaderID = ? AND T_OrderLocationIsActive = 'X'";
                    $query = $this->db_onedev->query($sql,array(
                        $userid,
                        $order['T_OrderHeaderID']
                    ));
                    if(!$query){
                        $this->db_onedev->trans_rollback();
                        $this->sys_error_db(["status" => "ERR", "message" => "error update location" .
                         $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
                        exit;
                    }
                    //echo $this->db_onedev->last_query();
                }

                $sql = "SELECT SUM(T_OrderDetailTotal) as total 
                        FROM t_orderdetail 
                        WHERE T_OrderDetailT_OrderHeaderID = ? AND T_OrderDetailIsActive = 'Y'";
                $query = $this->db_onedev->query($sql,array(
                    $order['T_OrderHeaderID']
                ));
                $get_total = $query->result_array();
                $total = $get_total[0]['total'];

                $sql = "UPDATE t_orderheader SET T_OrderHeaderSubTotal = ?, T_OrderHeaderTotal = ? WHERE T_OrderHeaderID = ?";
                $query = $this->db_onedev->query($sql,array(
                    $total,
                    $total,
                    $order['T_OrderHeaderID']
                ));
                if(!$query){
                    $this->db_onedev->trans_rollback();
                    $this->sys_error_db(["status" => "ERR", "message" => "error update price total" .
                     $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
                    exit;
                }

                $results = ["status" => "OK", "msg" => "Ganti Paket Berhasil"];


            }else{
                $results = ["status" => "ERROR", "msg" => "Gagal ganti paket, kode paket tidak ditemukan"];
            }

            $this->db_onedev->trans_commit();

        }

       // echo json_encode($results);

        // $results = array ("total" => 1, "records" => array('testall' => $rtntestregistration));
         $this->sys_ok($results);

      } catch(Exception $exc) {
         $message = $exc->getMessage();
         $this->sys_error($message);
      }
	}
   function generate_location($id,$testid)
  {
    $userid = $this->sys_user["M_UserID"];
    $sql = "SELECT T_SampleStationID,T_SampleStationName
FROM t_orderdetail
JOIN t_test ON T_OrderDetailT_TestID = T_TestID AND T_TestIsActive = 'Y'
JOIN t_sampletype ON T_TestT_SampleTypeID = T_SampleTypeID
JOIN t_bahan ON T_SampleTypeT_BahanID = T_BahanID
JOIN t_samplestation ON T_BahanT_SampleStationID = T_SampleStationID
WHERE
T_OrderDetailT_OrderHeaderID = {$id} AND
T_OrderDetailT_TestID = {$testid}
GROUP BY T_SampleStationID ";
    $qry = $this->db_onedev->query($sql);
   // echo $this->db_onedev->last_query();
    $data_samples = $qry->result_array();
    if (count($data_samples) > 0 ){
      foreach ($data_samples as $k => $v) {
         $sample_id = $v['T_SampleStationID'];
         $sql = $this->db_onedev->query("SELECT M_LocationID as loc_id FROM m_location
     WHERE M_LocationT_SampleStationID = $sample_id
     ORDER BY M_LocationPriority DESC, M_LocationID ASC
     LIMIT 1")->row();
         $loc_id = $sql->loc_id;
   
         $query = $this->db_onedev->query("SELECT COUNT(*) as t_orderid
               FROM t_order_location 
         WHERE T_OrderLocationT_OrderHeaderID = {$id} AND T_OrderLocationM_LocationID = {$loc_id} 
         AND T_OrderLocationT_SampleStationID = {$sample_id} AND T_OrderLocationIsActive = 'Y'");
       if (!$query) {
         $this->sys_error_db(["status" => "ERR", "message" => "select t_order_location | " .
           $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
         exit;
       }
       $t_orderid = $query->row()->t_orderid;
       if ($t_orderid == 0){
         $sql = "INSERT INTO t_order_location(
            T_OrderLocationT_OrderHeaderID,	
            T_OrderLocationM_LocationID,
            T_OrderLocationT_SampleStationID,
            T_OrderLocationCreated,	
            T_OrderLocationLastUpdated,	
            T_OrderLocationUserID)
             VALUES ('{$id}','{$loc_id}','{$sample_id}',now(),now(),$userid)";
             $qry = $this->db_onedev->query($sql);
           }
           if (!$qry) {
             $this->sys_error_db(["status" => "ERR", "message" => "insert t_order_location | " .
               $this->db_onedev->error()["message"], "debug" => $this->db_onedev->last_query()]);
             exit;
           }
       }
    }
    

      
  }
   function lookup_data_promise(){
      //# cek token valid
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }

      $prm = $this->sys_input;
      $orderid = $prm['orderid'];
      $query ="	SELECT T_OrderPromiseID as id, 
         false as menudate,
         DATE_FORMAT(T_OrderPromiseDateTime, '%d-%m-%Y') as xdate,
         DATE_FORMAT(T_OrderPromiseDateTime, '%H:%i') as xtime,
         GROUP_CONCAT(DISTINCT T_OrderDetailT_TestName separator ', ') as tests,
         'N' as xerror,
         'N' as xactive
         FROM t_orderpromise
         JOIN t_orderdetail ON T_OrderDetailT_OrderPromiseID = T_OrderPromiseID AND T_OrderDetailT_OrderHeaderID = {$orderid} AND T_OrderDetailIsActive = 'Y'
         WHERE
         T_OrderPromiseT_OrderHeaderID = {$orderid} AND T_OrderPromiseIsActive = 'Y'
         GROUP BY T_OrderPromiseID
";
      //echo $query ;
      $rows = $this->db_onedev->query($query)->result();

      $result = array(
         "total" => 1 , 
         "records" =>$rows
      ); 
      $this->sys_ok($result);
      exit;
   }

   function checkstatus(){
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }

      $prm = $this->sys_input;
      $query ="SELECT COUNT(*) as countval
         FROM t_orderdetail
         JOIN t_test ON T_TestID = T_OrderDetailT_TestID AND
         T_TestIsResult = 'Y'
         WHERE
         T_OrderDetailT_OrderHeaderID = {$prm['orderid']} AND 
         T_OrderDetailT_TestSasCode LIKE '{$prm['pxsascode']}%' AND
         T_OrderDetailIsActive = 'Y' AND
         T_OrderDetailVerification = 'Y'";
      //echo $query ;
      $countval = $this->db_onedev->query($query)->row()->countval;
      $status = 'OK';
      if(intval($countval) > 0){
         $status = 'NOTOK';
      }

      $result = array(
         "total" => 1 , 
         "status" => $status
      ); 
      $this->sys_ok($result);
      exit;
   }


   function getcitos(){
      //# cek token valid
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }

      $prm = $this->sys_input;

      $query ="	SELECT Nat_CitoID as id, Nat_CitoName as name, Nat_CitoDuration as duration,Nat_CitoIsDefault as xdefault
         FROM nat_cito
         WHERE
         Nat_CitoIsActive = 'Y'
";
      //echo $query ;
      $rows = $this->db_onedev->query($query)->result();

      $result = array(
         "total" => 1 , 
         "records" =>$rows
      ); 
      $this->sys_ok($result);
      exit;
   }

   function save_promise(){
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }

      $prm = $this->sys_input;
      $orderid = $prm['orderid'];
      $xuserid = $this->sys_user['M_UserID'];
      $sql = "SELECT * FROM t_orderpromise WHERE T_OrderPromiseT_OrderHeaderID = '{$orderid}' AND T_OrderPromiseIsActive = 'Y'";
      $promiseold = $this->db_onedev->query($sql)->result_array();

      foreach($prm['data'] as $k=>$v){
         $xdatetime = date('Y-m-d H:i', strtotime($v['xdate'].' '.$v['xtime']));
         $query = "UPDATE t_orderpromise SET T_OrderPromiseDateTime = '{$xdatetime}' WHERE T_OrderPromiseID = '{$v['id']}' ";
         $this->db_onedev->query($query);
         //echo $query;
      }

      $query ="	SELECT T_OrderPromiseID as id, 
         false as menudate,
         DATE_FORMAT(T_OrderPromiseDateTime, '%d-%m-%Y') as xdate,
         DATE_FORMAT(T_OrderPromiseDateTime, '%H:%i') as xtime,
         'N' as xerror,
         'N' as xactive
         FROM t_orderpromise
         WHERE
         T_OrderPromiseT_OrderHeaderID = {$orderid} AND T_OrderPromiseIsActive = 'Y'
";
      //echo $query ;
      $rows = $this->db_onedev->query($query)->result();
      $sql = "SELECT * FROM t_orderpromise WHERE T_OrderPromiseT_OrderHeaderID = '{$orderid}' AND T_OrderPromiseIsActive = 'Y'";
      $promisenew = $this->db_onedev->query($sql)->result_array();

      $x_json = json_encode(array('old'=>$promiseold,'new'=>$promisenew));
      $sql = "INSERT INTO order_attr (
         orderAttrT_OrderHeaderID,
         orderAttrType,
         orderAttrJson,
         orderAttrUserID
      )
      VALUES(
      {$orderid},
      'spv_promise',
      '{$x_json}',
      '{$xuserid}'
   )";
      $this->db_onedev->query($sql);

      $result = array(
         "total" => 1 , 
         "records" =>$rows
      ); 
      $this->sys_ok($result);
      exit;
   }

   function checkpromisetests(){
      //# cek token valid
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }

      $prm = $this->sys_input;
      $orderid = $prm['orderid'];
      $tests = array();
      $packets = array();
      $rows = array();
      foreach($prm['tests'] as $k => $v){
         if($v['status']){
            if($v['type'] == 'PX')
               $tests[] = $v['pxid'];
            else
               $packets[] = $v['pxid'];
         }

      }

      $tests_text = join(",",$tests);
      //ada penambahan paket , parameter kedua berupa csv paket id 
      //di blank_kan dulu sipe
      $packets_text = join(",",$packets);
      $query ="SELECT fn_fo_find_promise_by_px('{$tests_text}','{$packets_text}') as promise";
      //echo $query;
      $rows['after'] = $this->db_onedev->query($query)->row()->promise;

      $query = "SELECT fn_fo_find_promise_by_px(GROUP_CONCAT(DISTINCT  T_OrderDetailT_TestID separator ','),'') as promise
         FROM t_orderdetail
         WHERE T_OrderDetailT_OrderHeaderID = {$orderid}
         GROUP BY T_OrderDetailT_OrderHeaderID";
      $rows['before'] = $this->db_onedev->query($query)->row()->promise;

      $result = array(
         "total" => 1 , 
         "records" =>$rows
      ); 
      $this->sys_ok($result);
      exit;
   }

   function getchildrenprofil(){
      if (! $this->isLogin) {
         $this->sys_error("Invalid Token");
         exit;
      }

      $prm = $this->sys_input;
      $sql = "	SELECT T_OrderHeaderM_CompanyID as companyid,
         T_OrderHeaderM_MouID as mouid,
         T_OrderHeaderDate as orderdate
         FROM t_orderheader 
         WHERE T_OrderHeaderID = '{$prm['data']['orderid']}'";
      $dtorder = $this->db_onedev->query($sql)->row_array();

      $sql = "SELECT 0 as xid,
         'SINGLE' as type,
         'Y' as status,
         T_TestID as testid,
         T_TestCode as testcode,
         T_TestSasCode as testsascode,
         T_TestName as testname,
         T_TestIsResult as isresult,
         T_PriceAmount as bruto,
         T_PriceDisc as discount,
         T_PriceDiscRp as discountrp,
         ((T_PriceDisc / 100) * T_PriceAmount) - T_PriceDiscRp as discounttotal,
         ( T_PriceAmount - ((T_PriceDisc / 100) * T_PriceAmount) - T_PriceDiscRp ) as total,
         'N' as cito
         FROM t_packet
         JOIN t_packetdetail ON T_PacketDetailT_PacketID = T_PacketID AND T_PacketDetailIsActive = 'Y'
         JOIN t_test ON T_PacketDetailT_TestID = T_TestID
         JOIN t_price ON T_PriceT_TestID = T_TestID AND T_PriceM_CompanyID = {$dtorder['companyid']} AND T_PriceM_MouID = {$dtorder['mouid']} AND T_PriceIsActive = 'Y'
         WHERE
         T_PacketSasCode = '{$prm['data']['testsascode']}' 
         GROUP BY T_PacketDetailID";
      $rows = $this->db_onedev->query($sql)->result_array();
      $result = array(
         "total" => 1 , 
         "records" =>$rows
      ); 
      $this->sys_ok($result);
      exit;
   }


}