BE_CPONE/application/controllers/mockup/process/bkp-sipe-resultverification/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);
      $this->db_one = $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_OrderDetailVerDeltaCheck delta_check,
                  T_OrderDetailVerTrendAnalysis trend_analysis,
                  T_OrderDetailVerification verification,
                  T_OrderDetailVerification verification_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_OrderDetailVerDeltaCheck delta_check,
                  T_OrderDetailVerTrendAnalysis trend_analysis,
                  T_OrderDetailVerification verification,
                  T_OrderDetailVerification verification_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['delta_check'] == "X")
               {
                  $tmp = $this->db_smartone->query("SELECT fn_process_re_deltacheck('{$v['id']}') x")
                     ->row();
                  $v['delta_check'] = $tmp->x;
               }

               if ($v['trend_analysis'] == "X")
               {
                  $trend_sql = "SELECT fn_process_re_trendanalysis('{$v['id']}') x";
                  $tmp = $this->db_smartone->query($trend_sql)
                     ->row();
                  $j_trend_analysis= json_decode($tmp->x);
                  $v['trend_analysis'] = $j_trend_analysis->status;
               }
               // Sample Handling & Verification Perfect
               // sipe always checked
               if (true || $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);
      }
   }
}