<?php


class Rv_px extends MY_Controller
{
   var $db_smartone;

   public function index()
   {
      echo "RE Px API";
   }

   public function __construct()
   {
      parent::__construct();
      $this->db_smartone = $this->load->database("onedev", true);
   }

   public function multi_verification() {
      $prm = $this->sys_input;
      $od_id = $prm["order_id"];
      $userID = $this->sys_user["M_UserID"];
       if ( ! $this->isLogin ) {
         echo json_encode(
            array("status"=>"ERR", "message"=> "Invalid Token")
         );
         exit;
       }
      $s_ids = "0 ";
      foreach($od_id as $id) {
         $s_ids .= ",$id";
      }
      $sql = "update t_orderdetail 
            set T_OrderDetailVerification= 'Y'
            , T_OrderDetailVerUserID = ?
            , T_OrderDetailVerDate = now()
            where T_OrderDetailID in ($s_ids) ";
      $this->db_smartone->query($sql, array($userID));
      foreach($od_id as $id ) {
         $this->db_smartone->query("call sp_verif_parent(?)", array($id));
         $this->clean_mysqli_connection($this->db_smartone->conn_id);
      }
      //log
      $sql = "insert into one_log.log_process(Log_ProcessCode, Log_ProcessOrderID, Log_ProcessOrderNumber,
         Log_ProcessJson,Log_ProcessUserID)
         select 'PROCESS.Result.Verification' , T_OrderDetailT_OrderHeaderID, T_OrderHeaderLabNumber,
         '',$userID
         from t_orderdetail
         join t_orderheader on T_OrderDetailT_OrderHeaderID = T_OrderHeaderID and T_OrderDetailID 
         in ( $s_ids ) "; 

      $this->db_smartone->query($sql);

      $result = array("status"=>"OK","message" => "");
      // update branch tx
      $sql ="select T_OrderDetailT_OrderHeaderID from t_orderdetail where T_OrderDetailID in ( $s_ids ) 
         limit 0,1";
      $qry = $this->db_smartone->query($sql);
      $orderHeaderID= 0;
      if ($qry) {
         $rows = $qry->result_array();
         if (count($rows) > 0 ) {
            $orderHeaderID = $rows[0]["T_OrderDetailT_OrderHeaderID"];
         }
      }
      $sql = "select 
         if(T_OrderHeaderAddOnLabNumberOrigin is null,'N','Y') isRujukan
         from 
         t_orderheaderaddon 
         where T_OrderHeaderAddOnT_OrderHeaderID = ?";
      $qry = $this->db->query($sql,array($orderHeaderID));
      $flag_rujukan = false;
      if ($qry) {
         $rows = $qry->result_array();
         if (count($rows) > 0 ) {
            if ($rows[0]["isRujukan"] == "Y") $flag_rujukan = true;
         } 
      }
      if ($flag_rujukan) {
         $this->load->library("Txbranchstatus");
         $this->txbranchstatus->update("VERIFY",$id,$this->sys_user['M_StaffName']);
      }

      $this->sys_ok($result);
   }

   public function single_verification() {
      $prm = $this->sys_input;
      $od_id = $prm["order_id"];
      $validation = $prm["validation"];
      $userID = $this->sys_user["M_UserID"];
       if ( ! $this->isLogin ) {
         echo json_encode(
            array("status"=>"ERR", "message"=> "Invalid Token")
         );
         exit;
       }
      if ($validation == "Y") {
         $sql = "update t_orderdetail 
            set T_OrderDetailVerification= 'Y'
            , T_OrderDetailVerUserID = ?
            , T_OrderDetailVerDate = now()
            where T_OrderDetailID = ?";
      } else {
         $sql = "update t_orderdetail 
            set T_OrderDetailVerification= 'N'
            , T_OrderDetailVerUserID= ?
            , T_OrderDetailVerDate= now()
            where T_OrderDetailID = ?";
      }
      $this->db_smartone->query($sql, array($userID,$od_id));
      $this->db_smartone->query("call sp_verif_parent(?)", array($od_id));
      $this->clean_mysqli_connection($this->db_smartone->conn_id);
      //log
      $order = $this->db_smartone->select('T_OrderDetailT_OrderHeaderID id, T_OrderHeaderLabNumber lab_number,
         T_OrderDetailT_TestName, T_OrderDetailID, T_OrderDetailResult', false)
           ->join('t_orderheader', 'T_OrderHeaderID = T_OrderDetailT_OrderHeaderID')
           ->where('T_OrderDetailID', $od_id)
           ->get('t_orderdetail')
           ->row();
      $dblog = $this->load->database("onelog", true);
      if ($validation == "Y") {
         $dblog->set('Log_ProcessCode', 'PROCESS.Result.Verification')
               ->set('Log_ProcessOrderID', $order->id)
               ->set('Log_ProcessOrderNumber', $order->lab_number)
               ->set('Log_ProcessJson', json_encode($data))
               ->set('Log_ProcessUserID', $this->sys_user['M_UserID'])
               ->insert('log_process');
      } else {
         $dblog->set('Log_ProcessCode', 'PROCESS.Result.Unverification')
               ->set('Log_ProcessOrderID', $order->id)
               ->set('Log_ProcessOrderNumber', $order->lab_number)
               ->set('Log_ProcessJson', json_encode($data))
               ->set('Log_ProcessUserID', $this->sys_user['M_UserID'])
               ->insert('log_process');
      }
      $result = array("status"=>"OK","message" => "");
      // update branch tx
      $sql = "select 
         if(T_OrderHeaderAddOnLabNumberOrigin is null,'N','Y') isRujukan
         from 
         t_orderheaderaddon 
         where T_OrderHeaderAddOnT_OrderHeaderID = ?";
      $qry = $this->db->query($sql,array($order->id));
      $flag_rujukan = false;
      if ($qry) {
         $rows = $qry->result_array();
         if (count($rows) > 0 ) {
            if ($rows[0]["isRujukan"] == "Y") $flag_rujukan = true;
         } 
      }
      if ($flag_rujukan && $validation == "Y") {
         $this->load->library("Txbranchstatus");
         $this->txbranchstatus->update("VERIFY",$od_id,$this->sys_user['M_StaffName']);
      }

      $this->sys_ok($result);
   }

   public function search()
   {
      $prm = $this->sys_input;
      $max_rst = 99;

      $id = $prm["order_id"];
      $lang = $this->lang_default_code;
      $lang_id = isset($prm['lang_id']) ? $prm['lang_id'] : '0';

      if ($lang_id != 0)
      {
         $l = $this->db_smartone->where("Nat_LangID", $lang_id)->get("nat_lang")->row();
         $lang = $l->Nat_LangCode;
      }

      $tot_count = 0;
      $lq = "";
      $group_id = 0;
      if (isset($prm['group_id']))
          $group_id = $prm['group_id'];
          if ($group_id != 0)
          {
        
	  $sql = "select distinct T_WorklistDetailNat_TestID Nat_TestID
		from t_worklistdetailv2
		where T_WorklistDetailT_WorklistID = {$group_id}";
          $qry = $this->db_smartone->query($sql);
          $rows = $qry->result_array();
          $nat_test_ids = "0";
          foreach($rows as $r) {
             $nat_test_ids .= ", " . $r["Nat_TestID"];
          } 
          $q_group = "  ( $nat_test_ids ) ";

          $sql = "SELECT *, cOUNT(a2.T_OrderDetailID) level FROM (
                SELECT IFNULL(y.T_OrderDetailT_TestID, x.T_OrderDetailT_TestID) t_testid, 
                IFNULL(y.T_OrderDetailT_TestName, y.T_OrderDetailT_TestName) t_testname,
                  y.T_OrderDetailResult result, y.T_OrderDetailResult result_old, y.T_OrderDetailNote note, y.T_OrderDetailNote note_old, y.T_OrderDetailID id,
                  y.T_OrderDetailT_TestIsResult is_result, a.T_TestIsQuantitative is_quantitative,
                  y.T_OrderDetailResultFlag result_flag,
                  y.T_OrderDetailVerDeltaCheck delta_check,
                    y.T_OrderDetailVerTrendAnalysis trend_analysis,
                    y.T_OrderDetailVerification verification,
                    y.T_OrderDetailVerification verification_old,
                    y.T_OrderDetailValidation validation,
                    y.T_OrderDetailReqStatus sample_handling_perfect,
                  y.T_OrderDetailNat_NormalValueID normal_id,
                  y.T_OrderDetailNormalValueNote normal_note, y.T_OrderDetailNat_UnitName unit_name,
                  y.T_OrderdetailNat_MethodeID methode_id, y.T_OrderdetailNat_MethodeName methode_name,
                  a.T_TestNat_TestID nattest_id, a.T_TestID tx_id, count(T_ResultInstrumentID) as result_instrument_n,
                  y.T_OrderDetailVerification, T_OrderSampleReceive sample_receive,
                  T_OrderSampleProcessing sample_processing, T_OrderSampleWorklistReceive sample_worklist_receive,
                  IF(T_OrderDetailAddOnPreAnalytic = 'Y', T_OrderDetailAddOnPreAnalytic, PreAnalyticIsOk) pre_analytic,
                  T_OrderHeaderID order_id, y.T_OrderDetailT_TestSasCode test_sas_code,
                  tx.T_TestSasCode,
                  T_OrderDetailAddOnResample `resample`, T_OrderDetailAddOnResampleStatus resample_status,
                    T_OrderDetailAddOnRef ref,
					fn_process_group_result(x.T_OrderDetailT_TestID) ResultGroupName
               FROM t_orderdetail x

                JOIN t_test tx ON x.T_OrderDetailT_TestID = T_TestID
               JOIN t_test tcheck on  tcheck.T_TestNat_TestID in {$q_group} 
               and ( tx.T_TestID  = tcheck.T_TestID  or tx.T_TestSasCode like concat(tcheck.T_TestSasCode,'%'))
                JOIN documentation_group_detail ON DocumentationGroupDetailNat_SubGroupID = tx.T_TestNat_SubGroupID
                    AND DocumentationGroupDetailIsActive = 'Y'
                JOIN documentation_group ON DocumentationGroupDetailDocumentationGroupID = DocumentationGroupID
                    AND DocumentationGroupName = 'lab'

               LEFT JOIN t_orderdetail y ON x.T_OrderDetailT_TestSasCode LIKE CONCAT(y.T_OrderDetailT_TestSasCode, '%')
                    AND x.T_OrderDetailT_OrderHeaderID = y.T_OrderDetailT_OrderHeaderID
                    AND y.T_OrderDetailIsActive = 'Y'

                LEFT JOIN t_orderdetailaddon ON x.T_OrderDetailID = T_OrderDetailAddOnT_OrderDetailID
                
               JOIN t_orderheader ON y.T_OrderDetailT_OrderHeaderID = T_OrderHeaderID
               LEFT JOIN t_test a ON y.T_OrderDetailT_TestID = a.T_TestID
                AND a.T_TestIsNonLab = ''
                LEFT JOIN (SELECT T_OrderSampleT_SampleTypeID, T_OrderSampleReceive, T_OrderSampleProcessing, T_OrderSampleWorklistReceive
                        FROM t_ordersample WHERE T_OrderSampleT_OrderHeaderID = ?
                        AND T_OrderSampleIsactive = 'Y'
                        GROUP BY T_OrderSampleT_SampleTypeID) os ON a.T_TestT_SampleTypeID = os.T_OrderSampleT_SampleTypeID
                                          or os.T_OrderSampleT_SampleTypeID  = fn_sampletype_from_local(a.T_TestNat_TestID)

               LEFT JOIN t_resultinstrument ON T_ResultInstrumentT_OrderDetailID = x.T_OrderDetailID
               LEFT JOIN pre_analytic ON PreAnalyticNat_TestID = a.T_TestNat_TestID AND PreAnalyticDate = date(now()) and PreAnalyticIsActive = 'Y'
               WHERE x.T_OrderDetailT_OrderHeaderID = ?
               AND x.T_OrderDetailIsActive = 'Y'
               GROUP BY y.T_OrderDetailT_TestID
               ORDER BY y.T_OrderDetailT_TestSasCode ASC) a1

               LEFT JOIN t_orderdetail a2 ON a2.T_OrderDetailT_OrderHeaderID = a1.order_id
                AND a2.T_OrderDetailIsActive = 'Y'
                AND a1.test_sas_code LIKE CONCAT(a2.T_OrderDetailT_testSasCode, '%')

                GROUP BY a1.t_testid
                order by a1.T_TestSasCode
            ";
            $query = $this->db_smartone->query($sql, [$id, $id]);
          $lq = $this->db_smartone->last_query();
          }
          else
          {
            $sql = "SELECT *, cOUNT(a2.T_OrderDetailID) level FROM (
                SELECT IFNULL(y.T_OrderDetailT_TestID, x.T_OrderDetailT_TestID) t_testid, 
                IFNULL(y.T_OrderDetailT_TestName, y.T_OrderDetailT_TestName) t_testname,
                  y.T_OrderDetailResult result, y.T_OrderDetailResult result_old, y.T_OrderDetailNote note, y.T_OrderDetailNote note_old, y.T_OrderDetailID id,
                  y.T_OrderDetailT_TestIsResult is_result, a.T_TestIsQuantitative is_quantitative,
                  y.T_OrderDetailResultFlag result_flag,
                  y.T_OrderDetailVerDeltaCheck delta_check,
                    y.T_OrderDetailVerTrendAnalysis trend_analysis,
                    y.T_OrderDetailVerification verification,
                    y.T_OrderDetailVerification verification_old,
                    y.T_OrderDetailValidation validation,
                    y.T_OrderDetailReqStatus sample_handling_perfect,
                  y.T_OrderDetailNat_NormalValueID normal_id,
                  y.T_OrderDetailNormalValueNote normal_note, y.T_OrderDetailNat_UnitName unit_name,
                  y.T_OrderdetailNat_MethodeID methode_id, y.T_OrderdetailNat_MethodeName methode_name,
                  a.T_TestNat_TestID nattest_id, a.T_TestID tx_id, count(T_ResultInstrumentID) as result_instrument_n,
                  y.T_OrderDetailVerification, T_OrderSampleReceive sample_receive,
                  T_OrderSampleProcessing sample_processing, T_OrderSampleWorklistReceive sample_worklist_receive,
                  IF(T_OrderDetailAddOnPreAnalytic = 'Y', T_OrderDetailAddOnPreAnalytic, PreAnalyticIsOk) pre_analytic,
                  T_OrderHeaderID order_id, y.T_OrderDetailT_TestSasCode test_sas_code,
                  tx.T_TestSasCode,
                  T_OrderDetailAddOnResample `resample`, T_OrderDetailAddOnResampleStatus resample_status,
                    T_OrderDetailAddOnRef ref,
					fn_process_group_result(x.T_OrderDetailT_TestID) ResultGroupName
               FROM t_orderdetail x

                JOIN t_test tx ON x.T_OrderDetailT_TestID = T_TestID
                JOIN documentation_group_detail ON DocumentationGroupDetailNat_SubGroupID = tx.T_TestNat_SubGroupID
                    AND DocumentationGroupDetailIsActive = 'Y'
                JOIN documentation_group ON DocumentationGroupDetailDocumentationGroupID = DocumentationGroupID
                    AND DocumentationGroupName = 'lab'

               LEFT JOIN t_orderdetail y ON x.T_OrderDetailT_TestSasCode LIKE CONCAT(y.T_OrderDetailT_TestSasCode, '%')
                    AND x.T_OrderDetailT_OrderHeaderID = y.T_OrderDetailT_OrderHeaderID
                    AND y.T_OrderDetailIsActive = 'Y'

                LEFT JOIN t_orderdetailaddon ON x.T_OrderDetailID = T_OrderDetailAddOnT_OrderDetailID
                
               JOIN t_orderheader ON y.T_OrderDetailT_OrderHeaderID = T_OrderHeaderID
               LEFT JOIN t_test a ON y.T_OrderDetailT_TestID = a.T_TestID
                AND a.T_TestIsNonLab = ''
                LEFT JOIN (SELECT T_OrderSampleT_SampleTypeID, T_OrderSampleReceive, T_OrderSampleProcessing, T_OrderSampleWorklistReceive
                        FROM t_ordersample WHERE T_OrderSampleT_OrderHeaderID = ?
                        AND T_OrderSampleIsactive = 'Y'
                        GROUP BY T_OrderSampleT_SampleTypeID) os ON a.T_TestT_SampleTypeID = os.T_OrderSampleT_SampleTypeID
                                                   or os.T_OrderSampleT_SampleTypeID  = fn_sampletype_from_local(a.T_TestNat_TestID)
               LEFT JOIN t_resultinstrument ON T_ResultInstrumentT_OrderDetailID = x.T_OrderDetailID
               LEFT JOIN pre_analytic ON PreAnalyticNat_TestID = a.T_TestNat_TestID AND PreAnalyticDate = date(now()) and PreAnalyticIsActive = 'Y'
               WHERE x.T_OrderDetailT_OrderHeaderID = ?
               AND x.T_OrderDetailIsActive = 'Y'
               GROUP BY y.T_OrderDetailT_TestID
               ORDER BY y.T_OrderDetailT_TestSasCode ASC) a1

               LEFT JOIN t_orderdetail a2 ON a2.T_OrderDetailT_OrderHeaderID = a1.order_id
                AND a2.T_OrderDetailIsActive = 'Y'
                AND a1.test_sas_code LIKE CONCAT(a2.T_OrderDetailT_testSasCode, '%')

                GROUP BY a1.t_testid
                order by a1.T_TestSasCode
            ";
            $query = $this->db_smartone->query($sql, [$id, $id]);
            $lq = $this->db_smartone->last_query();
          }
      
      if ($query) {
         $rst = [];
         $rows = $query->result_array();
         foreach($rows as $k => $v) 
         {
            if ($v['is_result'] == "Y" && $v['is_quantitative'] == "N")
            {
               $v['template'] = [];
               $x = $this->db_smartone->query("CALL sp_master_resulttemplate_get('{$v['t_testid']}')");
               $this->clean_mysqli_connection($this->db_smartone->conn_id);
               if ($x)
                  $v['template'] = $x->result_array();
            }

            $v['requirement'] = array('status' => 'Y' , 'note' => '');
            if ($v['is_result'] == "Y" && $v['is_quantitative'] == "Y") {
               try {
		       $qry  = $this->db_smartone->query("SELECT fn_process_re_trendanalysis_v2('{$v['id']}') x");
		       if ( $qry ) {
			       $tmp = $qry->row();
			       $v['trend_analysis'] = json_decode($tmp->x);
		       }
               } catch(Exception $e) {
               }
               try {
                  $qry = $this->db_smartone->query("SELECT fn_process_re_deltacheck('{$v['id']}') x");
		       if ( $qry ) {
			       $tmp = $qry->row();
                  		$v['delta_check'] = json_decode($tmp->x);
		       }
               } catch(Exception $e) {
               }
               //requirement
               $orderHeaderID = $v["T_OrderDetailT_OrderHeaderID"];
               $sql = "select d.*, Nat_PositionName 
                  from t_orderreq  d
                  join nat_position on T_OrderReqNat_PositionID = Nat_PositionID 
                  where T_OrderReqT_OrderHeaderID = ?";  
               $qry = $this->db_smartone->query($sql, array($orderHeaderID));
               if ($qry) {
                  $r_rows = $qry->result_array();
                  if(count($r_rows) > 0 ) {
                     $r_notes = '';
                     foreach($r_rows as $r_r) {
                        $a_test = json_decode($r_r["T_OrderReqT_TestID"],true);
                        if ( in_array($v["t_testid"],$a_test) ) {
                           $a_req = json_decode($r_r["T_OrderReqs"],true);
			   $s_req = implode(",",$a_req);
			   if ($s_req == "" ) $s_req = "0";
                           $sql = "select group_concat(Nat_RequirementName) reqs 
                              from nat_requirement where Nat_RequirementID in ( 0, $s_req )
                              and Nat_RequirementIsActive = 'Y'";
                           $qry = $this->db_smartone->query($sql);
                           if ($qry) {
                              $r2_rows = $qry->result_array();
                              foreach($r2_rows as $r2_r) {
                                 if ($r_notes != "") $r_notes .= "<br/>";
                                 $r_notes .=  $r_r["Nat_PositionName"] . " : " . $r2_r["reqs"];
                              }
			   } else {
			//		echo $this->db_smartone->last_query();
                              print_r($this->db_smartone->error());
                           }
                        }
                     }
                     if ($r_notes != "" ) {
                        $v['requirement'] = array('status' => 'X' , 'note' => $r_notes);
                     }
                  }
               }
            }
            // IF Rujukan, by pas Pre Analytik
            if ($v['ref'] == 'Y')
                $v['pre_analytic'] = 'Y';


            
            $rst[] = $v;
         }
            
         $result = array("total" => $tot_count, "records" => $rst, "total_display" => sizeof($rows), "q" => $lq);
         $this->sys_ok($result);
      } 
      else {
         $this->sys_error_db("RE Px rows", $this->db_smartone);
         exit;
      }
   }

   public function search_old_2()
   {
      $prm = $this->sys_input;
      $max_rst = 99;

      $id = $prm["order_id"];
      $lang = $this->lang_default_code;
      $lang_id = isset($prm['lang_id']) ? $prm['lang_id'] : '0';

      if ($lang_id != 0)
      {
         $l = $this->db_smartone->where("Nat_LangID", $lang_id)->get("nat_lang")->row();
         $lang = $l->Nat_LangCode;
      }

      $tot_count = 0;
      $group_id = 0;
      if (isset($prm['group_id']))
        $group_id = $prm['group_id'];

    //   if ($lang == $this->lang_default_code)
    //   {
        if ($group_id != 0)
          {
            $sql = "SELECT IFNULL(z.T_TesTID, x.t_testid) t_testid, 
            IFNULL(z.T_TestName, x.t_testname) t_testname, 
            IFNULL(z.T_TestNat_TesTID, x.nattest_id) nattest_id,
            IFNULL(y.T_OrderDetailResult, x.result) result,
            IFNULL(y.T_OrderDetailResult, x.result_old) result_old,
            IFNULL(y.T_OrderDetailNote, x.note) note,
            IFNULL(y.T_OrderDetailNote, x.note_old) note_old,
            IFNULL(y.T_OrderDetailID, x.id) id,
            IFNULL(y.T_OrderDetailT_TestIsResult, x.is_result) is_result,
            IFNULL(z.T_TestIsQuantitative, x.is_quantitative) is_quantitative,
            IFNULL(y.T_OrderDetailVerDeltaCheck, x.delta_check) delta_check,
            IFNULL(y.T_OrderDetailVerTrendAnalysis, x.trend_analysis) trend_analysis,
            IFNULL(y.T_OrderDetailVerification, x.verification) verification,
            IFNULL(y.T_OrderDetailVerification, x.verification_old) verification_old,
            IFNULL(y.T_OrderDetailReqStatus, x.sample_handling_perfect) sample_handling_perfect,
            IFNULL(y.T_OrderDetailNat_NormalValueID, x.normal_id) normal_id,
            IFNULL(y.T_OrderDetailNormalValueNote, x.normal_note) normal_note,
            IFNULL(y.T_OrderDetailNat_UnitName, x.unit_name) unit_name,
            IFNULL(y.T_OrderdetailNat_MethodeID, x.methode_id) methode_id,
            IFNULL(y.T_OrderdetailNat_MethodeName, x.methode_name) methode_name,
            IFNULL(y.T_OrderDetailVerification, x.T_OrderDetailVerification) T_OrderDetailVerification,
            IFNULL(y.T_OrderDetailT_OrderHeaderID, x.T_OrderDetailT_OrderHeaderID) T_OrderDetailT_OrderHeaderID,
            IFNULL(y.T_OrderDetailT_TestSasCode, x.T_OrderDetailT_TestSasCode) T_OrderDetailT_TestSasCode,
            IFNULL(os1.T_OrderSampleReceive, x.T_OrderSampleReceive) sample_receive,
            IFNULL(os1.T_OrderSampleProcessing, x.T_OrderSampleProcessing) sample_processing,
            CAST( level + (LENGTH(y.T_OrderDetailT_TestSasCode) - LENGTH(x.T_OrderDetailT_TestSasCode))/2 as SIGNED) level
                FROM (
        SELECT IFNULL(a.t_testid, b.t_testid) t_testid, IFNULL(a.t_testname, b.t_testname) t_testname,
              T_OrderDetailResult result, T_OrderDetailResult result_old, T_OrderDetailNote note, T_OrderDetailNote note_old, T_OrderDetailID id,
              T_OrderDetailT_TestIsResult is_result, a.T_TestIsQuantitative is_quantitative,
              COUNT(b.T_TestID) level, 
              T_OrderDetailVerDeltaCheck delta_check,
                T_OrderDetailVerTrendAnalysis trend_analysis,
                T_OrderDetailVerification verification,
                T_OrderDetailVerification verification_old,
                T_OrderDetailReqStatus sample_handling_perfect,
                T_OrderDetailNat_NormalValueID normal_id,
              T_OrderDetailNormalValueNote normal_note, T_OrderDetailNat_UnitName unit_name,
              T_OrderdetailNat_MethodeID methode_id, T_OrderdetailNat_MethodeName methode_name,
              a.T_TestNat_TestID nattest_id, count(T_ResultInstrumentID) as result_instrument_n,
              T_OrderDetailVerification, T_OrderDetailT_OrderHeaderID, IFNULL(T_OrderDetailT_TestSasCode, b.T_TestSasCode) T_OrderDetailT_TestSasCode,
              T_OrderSampleReceive, T_OrderSampleProcessing
           FROM t_orderdetail
           JOIN t_test a ON T_OrderDetailT_TestID = a.T_TestID
            
            LEFT JOIN (SELECT T_OrderSampleT_SampleTypeID, T_OrderSampleReceive, T_OrderSampleProcessing
                        FROM t_ordersample WHERE T_OrderSampleT_OrderHeaderID = ?
                        AND T_OrderSampleIsactive = 'Y'
                        GROUP BY T_OrderSampleT_SampleTypeID) os ON a.T_TestT_SampleTypeID = os.T_OrderSampleT_SampleTypeID
            
           LEFT JOIN t_test b ON a.T_TestSasCode LIKE CONCAT(b.T_TestSasCode, '%')
           
           LEFT JOIN t_resultinstrument ON T_ResultInstrumentT_OrderDetailID = T_OrderDetailID
           WHERE T_OrderDetailT_OrderHeaderID = ?
           AND T_OrderDetailIsActive = 'Y'
           AND T_OrderDetailT_TestID IN 
           ( SELECT T_WorklistDetailT_TestID 
            FROM t_worklistdetail
            WHERE T_WorklistDetailIsactive = 'Y' ANd T_WorklistDetailT_WorklistID = ?
           )
           GROUP BY a.T_TestID
           ORDER BY T_OrderDetailT_TestSasCode ASC) x
           
         LEFT JOIN t_orderdetail y ON x.T_OrderDetailT_OrderHeaderID = y.T_OrderDetailT_OrderHeaderID
            AND y.T_OrderDetailIsActive = 'Y'
            AND y.T_OrderDetailT_TestSasCode LIKE CONCAT(x.T_OrderDetailT_TestSasCode, '%')
        LEFT JOIN t_test z ON y.T_OrderDetailT_TestID = z.T_TestID
        
        LEFT JOIN (SELECT T_OrderSampleT_SampleTypeID, T_OrderSampleReceive, T_OrderSampleProcessing
                        FROM t_ordersample WHERE T_OrderSampleT_OrderHeaderID = ?
                        AND T_OrderSampleIsactive = 'Y'
                        GROUP BY T_OrderSampleT_SampleTypeID) os1 ON z.T_TestT_SampleTypeID = os1.T_OrderSampleT_SampleTypeID";
        
            $query = $this->db_smartone->query($sql, [$id, $id, $group_id, $id]);
                    $lq = $this->db_smartone->last_query();
          }
          else
          {
            $sql = "SELECT a.t_testid, a.t_testname,
                    T_OrderDetailResult result, T_OrderDetailNote note, T_OrderDetailID id,
                    T_OrderDetailT_TestIsResult is_result, COUNT(b.T_TestID) level,
                    T_OrderDetailVerDeltaCheck delta_check,
                    T_OrderDetailVerTrendAnalysis trend_analysis,
                    T_OrderDetailVerification verification,
                    T_OrderDetailVerification verification_old,
                    T_OrderDetailReqStatus sample_handling_perfect, T_OrderDetailNat_NormalValueID normal_id,
                  T_OrderDetailNormalValueNote normal_note, T_OrderDetailNat_UnitName unit_name,
                  T_OrderdetailNat_MethodeID methode_id, T_OrderdetailNat_MethodeName methode_name,
                  a.T_TestNat_TestID nattest_id, count(T_ResultInstrumentID) as result_instrument_n,
                  T_OrderDetailVerification, T_OrderSampleReceive sample_receive,
                  T_OrderSampleProcessing sample_processing

               FROM t_orderdetail
               JOIN t_test a ON T_OrderDetailT_TestID = a.T_TestID
                AND a.T_TestIsNonLab = ''
                JOIN (SELECT T_OrderSampleT_SampleTypeID, T_OrderSampleReceive, T_OrderSampleProcessing
                        FROM t_ordersample WHERE T_OrderSampleT_OrderHeaderID = ?
                        AND T_OrderSampleIsactive = 'Y'
                        GROUP BY T_OrderSampleT_SampleTypeID) os ON a.T_TestT_SampleTypeID = os.T_OrderSampleT_SampleTypeID
               LEFT JOIN t_test b ON a.T_TestSasCode LIKE CONCAT(b.T_TestSasCode, '%')
               LEFT JOIN t_resultinstrument ON T_ResultInstrumentT_OrderDetailID = T_OrderDetailID
               WHERE T_OrderDetailT_OrderHeaderID = ?
               AND T_OrderDetailIsActive = 'Y'
               GROUP BY a.T_TestID
               ORDER BY T_OrderDetailT_TestSasCode ASC
            ";
            $query = $this->db_smartone->query($sql, [$id, $id]);
          }
      
      if ($query) {
         $rst = [];
         $rows = $query->result_array();

         foreach($rows as $k => $v) 
         {
            if ($v['is_result'] == "Y" && $v['is_quantitative'] == "N")
            {
               $v['template'] = [];
               $x = $this->db_smartone->query("CALL sp_master_resulttemplate_get('{$v['t_testid']}')");
               $this->clean_mysqli_connection($this->db_smartone->conn_id);
               if ($x)
                  $v['template'] = $x->result_array();
            }
            
            if ($v['delta_check'] == "X")
            {
                $tmp = $this->db_smartone->query("SELECT fn_process_re_deltacheck('{$v['id']}') x")
                                        ->row();
                $v['delta_check'] = $tmp->x;
            }

            $tmp = $this->db_smartone->query("SELECT fn_process_re_trendanalysis('{$v['id']}') x")->row();
            $v['trend_analysis'] = $tmp->x;

            // Sample Handling & Verification Perfect
            if ($v['sample_handling_perfect'] == 'X')
            {
                $y = $this->db_smartone->query("SELECT fn_process_sample_handling_perfect('{$id}', '{$v['t_testid']}') c")
                                    ->row();
                $v['sample_handling_perfect'] = $y->c;    
            }
            
            // Override
            if ($y->c == "N")
            {
                    $v['delta_check'] = "N";
                    $v['trend_analysis'] = "N";
            }
            $rst[] = $v;
         }
            
         $result = array("total" => $tot_count, "records" => $rst, "total_display" => sizeof($rows), "q" => $this->db_smartone->last_query());
         $this->sys_ok($result);
      } 
      else {
         $this->sys_error_db("RE Px rows", $this->db_smartone);
         exit;
      }
   }

   public function confirm()
   {
      $prm = $this->sys_input;
      $data = json_decode($prm["id"]);

      if (sizeof($data) < 1)
      {
         $this->sys_error_db("RE Verification Confirmation", $this->db_smartone);
         exit;
      }
      else
      {
         $ids = [];
         foreach ($data as $k => $v)
         {
            $this->db_smartone->set('T_OrderDetailVerDeltaCheck', $v->delta)
                              ->set('T_OrderDetailVerTrendAnalysis', $v->trend)
                              ->set('T_OrderDetailVerification', $v->verification)
                              ->set('T_OrderDetailVerDate', date('Y-m-d H:i:s'))
                              ->set('T_OrderDetailVerUserID', $this->sys_user->M_UserID)
                              ->where('T_OrderDetailID', $v->id)
                              ->update('t_orderdetail');
            $ids[] = $v->id;
         }

         $this->sys_ok($ids);
      }
   }

    public function search_group()
    {
        $prm = $this->sys_input;
        $max_rst = 100;

        // QUERY TOTAL
        $sql = "select count(*) total
                    from t_worklist
                    where T_WorklistIsActive = 'Y'
                    order by T_WorklistName ASC";
        $query = $this->db_smartone->query($sql);

        if ($query) { 
            $tot_count = $query->result_array()[0]["total"]; 
        } 
        else {
            $this->sys_error_db("worklist count", $this->db_smartone);
            exit;
        }

        $sql = "select T_WorklistID group_id, T_WorklistName group_name
                    from t_worklist
                    where T_WorklistIsActive = 'Y'
                    order by T_WorklistName ASC";
         //           limit 0, {$max_rst}";
        $query = $this->db_smartone->query($sql);

        if ($query) { 
            $rows = $query->result_array();
            foreach ($rows as $k => $v)
                $rows[$k]['data'] = json_decode($v['data']);
            $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows), "q" => $this->db_smartone->last_query());
            $this->sys_ok($result);
        } 
        else {
            $this->sys_error_db("worklist rows", $this->db_smartone);
            exit;
        }
    }
}