<?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 single_validation() {
      $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_OrderDetailValidation = 'Y'
            , T_OrderDetailValUserID = ?
            , T_OrderDetailValDate = now()
            where T_OrderDetailID = ?";
      } else {
         $sql = "update t_orderdetail 
            set T_OrderDetailValidation = 'N'
            , T_OrderDetailValUserID = ?
            , T_OrderDetailValDate = now()
            where T_OrderDetailID = ?";
      }
      $this->db_smartone->query($sql, array($userID,$od_id));
      $this->db_smartone->query("call sp_valid_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.Validation')
               ->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.Unvalidation')
               ->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" => "");
      $this->sys_ok($result);
   }
   public function search_old()
   {
      $prm = $this->sys_input;
      $max_rst = 99;

      $id = $prm["order_id"];

      // QUERY TOTAL
    //   $sql = "";
    //   $query = $this->db_smartone->query($sql);

    //   if ($query) { 
    //      $tot_count = $query->result_array()[0]["total"]; 
    //   } 
    //   else {
    //      $this->sys_error_db("re count", $this->db_smartone);
    //      exit;
    //   }
    $tot_count = 0;
      $sql = "SELECT T_TestID 
                FROM t_orderdetail JOIN t_test ON T_OrderDetailT_TEstID = T_TestID
                WHERE T_OrderDetailT_OrderHeaderID = ?
                AND T_OrderDetailIsActive = 'Y'
                AND T_TestParentT_TestID = 0";
      $query = $this->db_smartone->query($sql, [$id]);

      if ($query) {
          $rst = [];
         $rows = $query->result_array();
         foreach ($rows as $k => $v)
         {
            $sql = "WITH RECURSIVE ancestors AS 
                        ( 
                        SELECT t_testid, t_testname,
                        T_OrderDetailResult result, T_OrderDetailNote note, T_OrderDetailID id,
                        T_OrderDetailT_TestIsResult is_result, 1 level,
                        T_OrderDetailValMRState mr_state,
                        T_OrderDetailValidation validation,
                        T_OrderDetailValidation validation_old,
                        T_OrderDetailReqStatus sample_handling_perfect
                        FROM t_orderdetail 
                        JOIN t_test ON T_OrderDetailT_TestID = T_TestID
                        WHERE T_OrderDetailIsActive = 'Y'
                        AND T_OrderDetailT_OrderHeaderID = ?
                        AND t_testid = ?

                        UNION

                        SELECT g.t_testid, g.t_testname,
                        T_OrderDetailResult result, T_OrderDetailNote note, T_OrderDetailID id,
                        T_OrderDetailT_TestIsResult is_result, level+1 level,
                        T_OrderDetailValMRState mr_state,
                        T_OrderDetailValidation validation,
                        T_OrderDetailValidation validation_old,
                        T_OrderDetailReqStatus sample_handling_perfect
                        FROM ancestors a, t_orderdetail f
                        JOIN t_test g ON f.T_OrderDetailT_TestID = g.T_TestID
                        
                        WHERE T_OrderDetailIsActive = 'Y'
                        AND T_OrderDetailT_OrderHeaderID = ?
                        AND g.t_testparentt_testid = a.t_testid and g.t_testisactive = 'Y' )
                    SELECT * FROM ancestors;";
            $query = $this->db_smartone->query($sql, [$id, $v['T_TestID'], $id]);

            $r = $query->result_array();
            foreach($r as $k => $v) {
               if ($v['mr_state'] == "X")
               {
                  $tmp = $this->db_smartone->query("SELECT fn_process_rv_mrstate('{$v['id']}') x")
                                          ->row();
                  $v['mr_state'] = $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['mr_state'] = "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 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;

    //   if ($lang == $this->lang_default_code)
    //   {
      $sql = "SELECT a1.*, cOUNT(a2.T_OrderDetailID) level 
            FROM (
            SELECT x.T_OrderDetailVerification verification,
            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, x.T_OrderDetailID id,
              x.T_OrderDetailT_TestIsResult is_result, a.T_TestIsQuantitative is_quantitative,
              x.T_OrderDetailResultFlag result_flag,
              x.T_OrderDetailValMRState mr_state,
                    x.T_OrderDetailValidation validation,
                    x.T_OrderDetailValidation validation_old,
                    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,
              x.T_OrderDetailVerification, T_OrderSampleReceive sample_receive,
              T_OrderSampleProcessing sample_processing, T_OrderSampleWorklistReceive sample_worklist_receive,
              PreAnalyticID 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

           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]);


        //  $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_OrderDetailValMRState mr_state,
        //             T_OrderDetailValidation validation,
        //             T_OrderDetailValidation validation_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
        //        FROM t_orderdetail
        //        JOIN t_test a ON T_OrderDetailT_TestID = a.T_TestID
        //         AND a.T_TestIsNonLab = ''
        //        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]);
    //   }
    //   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_OrderDetailValMRState mr_state,
    //                 T_OrderDetailValidation validation,
    //                 T_OrderDetailValidation validation_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
    //            FROM t_orderdetail
    //            JOIN t_test a ON T_OrderDetailT_TestID = a.T_TestID
    //             AND a.T_TestIsNonLab = ''
    //            LEFT JOIN t_test b ON a.T_TestSasCode LIKE CONCAT(b.T_TestSasCode, '%')
    //            JOIN t_orderdetaillang ON T_OrderDetailLangT_OrderDetailID = T_OrderDetailID
    //               AND T_OrderDetailLangIsActive = 'Y'
    //               AND T_OrderDetailLangNat_LangID = ?
    //            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, [$lang_id, $id ]);
    //   }
      
      if ($query) {
         $rst = [];
         $rows = $query->result_array();
         
        //  echo json_encode($rows); return;
// echo $this->db_smartone->last_query(); return;
         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['mr_state'] == "X")
            {
                $tmp = $this->db_smartone->query("SELECT fn_process_rv_mrstate('{$v['id']}') x")
                                        ->row();
                $v['mr_state'] = $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['mr_state'] = "N";
            }

            //auto suggestion disabled
            //if ($v['sample_handling_perfect']== 'Y' && $v['mr_state'] == 'Y' && $v['verification'] == 'Y')
            //        $v['validation'] = 'Y';

            // 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" => $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 = 12;

        // 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;
        }
    }
}