BE_CPONE/application/controllers/mockup/process/resultunvalidation/Rv_px.php

<?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 search()
   {
      $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,
                        'N' unvalidation
                        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,
                        'N' unvalidation
                        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
               $y = $this->db_smartone->query("SELECT fn_process_sample_handling_perfect('{$id}', '{$v['t_testid']}') c")
                                       ->row();
               $v['sample_handling_perfect'] = $y->c;
               $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;
        }
    }
}