<?php


class Handling_patient extends MY_Controller
{
   var $db_smartone;
   var $nat_pos;
   var $nat_pos_2;

   public function index()
   {
      echo "Handling API";
   }

   public function __construct()
   {
      parent::__construct();
      $this->db_smartone = $this->load->database("onedev", true);
      $this->nat_pos = 'SAMP.VER';
      $this->nat_pos_2 = 'PRE.AN';
   }
   
   public function search()
   {
      $prm = $this->sys_input;
      $max_rst = 12;

      $nolab = '%' . $prm["nolab"] . '%';
      $search = '%' . $prm["search"] . '%';

      if ($prm['nolab'] != '')
         $nolab = "%{$prm['nolab']}%";

      // QUERY TOTAL
      $sql = "select count(*) total
            from t_ordersample
            join t_orderheader on t_ordersamplet_orderheaderid = t_orderheaderid
            join m_patient on t_orderheaderm_patientid = m_patientid
            join m_title on M_PatientM_TitleID = M_TitleID
            join last_statussample on t_ordersamplet_orderheaderid = last_statussamplet_orderheaderid
               and t_ordersamplet_sampletypeid = last_statussamplet_sampletypeid
               and t_ordersamplebarcode = last_statussamplebarcode
            join m_statussample on last_statussamplem_statussampleid = m_statussampleid
               and m_statussamplecode = 'SAMPLING.Handling.From.Verification'
            where T_OrderHeaderLabNumber like ?
               and M_PatientName LIKE ?";
      $query = $this->db_smartone->query($sql, array($nolab, $search));

      if ($query) { 
         $tot_count = $query->result_array()[0]["total"]; 
      } 
      else {
         $this->sys_error_db("m_patient count", $this->db_smartone);
         exit;
      }

      $p = $this->db_smartone->query("SELECT Nat_PositionID id FROM nat_position WHERE Nat_PositionCode = '{$this->nat_pos}'")
                           ->row();
        $p_2 = $this->db_smartone->query("SELECT Nat_PositionID id FROM nat_position WHERE Nat_PositionCode = '{$this->nat_pos_2}'")
                           ->row();

      $sql = "select T_OrderHeaderID, T_OrderHeaderLabNumber lab, T_OrderHeaderDate `date`,
            M_PatientID, M_PatientNoReg,
            concat(M_TitleName,' ',M_PatientName) name,
            T_OrderSampleBarcode sid, '' status, last_statussampleid id, t_sampletypename sample_name,
            T_OrderSampleID order_sample_id,
            json_object('clott', IFNULL(T_OrderSampleAddonHandlingClott, 'N'),
                        'lipemik', IFNULL(T_OrderSampleAddonHandlingLipemik, 'N'),
                        'lisis', IFNULL(T_OrderSampleAddonHandlingLisis, 'N'),
                        'clott_note', IFNULL(T_OrderSampleAddonHandlingClottNote, ''),
                        'lipemik_note', IFNULL(T_OrderSampleAddonHandlingLipemikNote, ''),
                        'lisis_note', IFNULL(T_OrderSampleAddonHandlingLisisNote, ''),
                        'note', IFNULL(T_OrderSampleAddonVerificationNote, '')) data,
            if (T_OrderSampleReceiveDate is not null and T_OrderSampleReceiveTime is not null,
               timediff(now(), concat(T_OrderSampleReceiveDate, ' ', T_OrderSampleReceiveTime) ), '00:00:00') sampling_duration,
            ifnull (ua.M_UserFullName, '-') user_sampling,
            ifnull (ub.M_UserFullName, '-') user_receive,
            ifnull (uc.M_UserFullName, '-') user_verification,
            T_OrderSampleAddonVerificationNote verification_note, T_SampleTypeID sample_id,
            IFNULL(sra.T_OrderSampleReqStatus, 'X') req_status_handling, IFNULL(sra.T_OrderSampleReqs, '[]') selected_reqs_handling,
            IFNULL(srb.T_OrderSampleReqStatus, 'X') req_status_process, IFNULL(srb.T_OrderSampleReqs, '[]') selected_reqs_process
            from t_ordersample
            join t_sampletype on t_ordersamplet_sampletypeid = t_sampletypeid
            join t_orderheader on t_ordersamplet_orderheaderid = t_orderheaderid
            join m_patient on t_orderheaderm_patientid = m_patientid
            join m_title on M_PatientM_TitleID = M_TitleID
            join last_statussample on t_ordersamplet_orderheaderid = last_statussamplet_orderheaderid
               and t_ordersamplet_sampletypeid = last_statussamplet_sampletypeid
               and t_ordersamplebarcode = last_statussamplebarcode
            join m_statussample on last_statussamplem_statussampleid = m_statussampleid
               and m_statussamplecode = 'SAMPLING.Handling.From.Verification'
            left join t_ordersampleaddon on t_ordersampleid = t_ordersampleaddont_ordersampleid
            left join m_user ua on T_OrderSampleSamplingUserID = ua.M_UserID
            left join m_user ub on T_OrderSampleReceiveUserID = ub.M_UserID
            left join m_user uc on T_OrderSampleVerificationUserID = uc.M_UserID
            left join t_ordersamplereq sra on sra.T_OrderSampleReqT_OrderHeaderID = T_OrderHeaderID
               and sra.T_OrderSampleReqNat_PositionID = ?
               and sra.T_OrderSampleReqT_OrderSampleID = T_OrderSampleID
            left join t_ordersamplereq srb on srb.T_OrderSampleReqT_OrderHeaderID = T_OrderHeaderID
               and srb.T_OrderSampleReqNat_PositionID = ?
               and srb.T_OrderSampleReqT_OrderSampleID = T_OrderSampleID
            where T_OrderHeaderLabNumber like ?
               and M_PatientName LIKE ?
            limit 0, {$max_rst}";
      $query = $this->db_smartone->query($sql, array($p->id, $p_2->id, $nolab, $search));

      if ($query) { 
         $rows = $query->result_array();
         foreach ($rows as $k => $v)
         {
            $rows[$k]['data'] = json_decode($v['data']);
            $rows[$k]['selected_reqs_handling'] = json_decode($v['selected_reqs_handling']);
            $rows[$k]['selected_reqs_process'] = json_decode($v['selected_reqs_process']);
            $rows[$k]['reqs_handling'] = [];
            $rows[$k]['reqs_process'] = [];
            
            $sql = "SELECT fn_specimen_get_requirement(?, ?, ?) as x";
            $query = $this->db_smartone->query($sql, [$v['T_OrderHeaderID'], $v['sample_id'], $this->nat_pos]);

            if ($query) 
            { 
                $rowss = $query->row();
                if ($rowss->x != null)
                    $rows[$k]['reqs_handling'] = json_decode($rowss->x);
            } 

            $sql = "SELECT fn_specimen_get_requirement(?, ?, ?) as x";
            $query = $this->db_smartone->query($sql, [$v['T_OrderHeaderID'], $v['sample_id'], $this->nat_pos_2]);

            if ($query) 
            { 
               $rowss = $query->row();
               if ($rowss->x != null)
                  $rows[$k]['reqs_process'] = json_decode($rowss->x);
            } 
         }
            
         $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("m_patient rows", $this->db_smartone);
         exit;
      }
   }

   public function search_sent()
   {
      $prm = $this->sys_input;
      $max_rst = 12;

      $nolab = '%' . $prm["nolab"] . '%';
      $search = '%' . $prm["search"] . '%';

      if ($prm['nolab'] != '')
         $nolab = "%{$prm['nolab']}%";

      // QUERY TOTAL
      $sql = "select count(*) total
            from t_ordersample
            join t_orderheader on t_ordersamplet_orderheaderid = t_orderheaderid
            join m_patient on t_orderheaderm_patientid = m_patientid
            join m_title on M_PatientM_TitleID = M_TitleID
            join last_statussample on t_ordersamplet_orderheaderid = last_statussamplet_orderheaderid
               and t_ordersamplet_sampletypeid = last_statussamplet_sampletypeid
               and t_ordersamplebarcode = last_statussamplebarcode
            join m_statussample on last_statussamplem_statussampleid = m_statussampleid
               and m_statussamplecode = 'SAMPLING.Verification.From.Sampling'
            where T_OrderHeaderLabNumber like ?
               and M_PatientName LIKE ?";
      $query = $this->db_smartone->query($sql, array($nolab, $search));

      if ($query) { 
         $tot_count = $query->result_array()[0]["total"]; 
      } 
      else {
         $this->sys_error_db("m_patient count", $this->db_smartone);
         exit;
      }

      $sql = "select T_OrderHeaderID, T_OrderHeaderLabNumber lab, T_OrderHeaderDate `date`,
            M_PatientID, M_PatientNoReg,
            concat(M_TitleName,' ',M_PatientName) name,
            T_OrderSampleBarcode sid, m_statussamplecode status, last_statussampleid id
            from t_ordersample
            join t_orderheader on t_ordersamplet_orderheaderid = t_orderheaderid
            join m_patient on t_orderheaderm_patientid = m_patientid
            join m_title on M_PatientM_TitleID = M_TitleID
            join last_statussample on t_ordersamplet_orderheaderid = last_statussamplet_orderheaderid
               and t_ordersamplet_sampletypeid = last_statussamplet_sampletypeid
               and t_ordersamplebarcode = last_statussamplebarcode
            join m_statussample on last_statussamplem_statussampleid = m_statussampleid
               and (m_statussamplecode = 'SAMPLING.Verification.From.Sampling')
            where T_OrderHeaderLabNumber like ?
               and M_PatientName LIKE ?
            limit 0, {$max_rst}";
      $query = $this->db_smartone->query($sql, array($nolab, $search));

      if ($query) { 
         $rows = $query->result_array();

         $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("m_patient rows", $this->db_smartone);
         exit;
      }
   }

   function verify()
   {
      $prm = $this->sys_input;

      $data = json_encode($prm['data']);
      $sql = "CALL sp_sampling_handling_02_process_reject('PRC', '{$prm['orderid']}', '{$prm['laststatusid']}', '{$data}', '{$this->sys_user['M_UserID']}')";
      
      $query = $this->db_smartone->query($sql);

      if ($query) 
      { 
         $row = $query->row();
         $this->sys_ok($row);
      } 
      else {
         $this->sys_error_db("verification verify", $this->db_smartone);
         exit;
      }
   }

   function reject()
   {
      $prm = $this->sys_input;

      $data = json_encode($prm['data']);
      $sql = "CALL sp_sampling_handling_02_process_reject('RJC', '{$prm['orderid']}', '{$prm['laststatusid']}', '{$data}', '{$this->sys_user['M_UserID']}')";
      
      $query = $this->db_smartone->query($sql);

      if ($query) 
      { 
         $row = $query->row();
         $this->sys_ok($row);
      } 
      else {
         $this->sys_error_db("verification reject", $this->db_smartone);
         exit;
      }
   }

   public function get_requirement()
   {
        $prm = $this->sys_input;

        $sql = "SELECT fn_specimen_get_requirement(?, ?, ?) as x";
        $query = $this->db_smartone->query($sql, [$prm['order_id'], $prm['sample_id'], $this->nat_pos]);

        if ($query) 
        { 
            $rows = $query->row();
            $y = [];
            if ($rows->x != null)
                $y = json_decode($rows->x);
            $this->sys_ok($y);
        } 
        else 
        {
            $this->sys_error_db("Get Requirement row", $this->db_smartone);
            exit;
        }
   }

   public function save_requirement()
   {
      $prm = $this->sys_input;
      $order_id = $prm['order_id'];
      $reqs = $prm['reqs'];
      $status = $prm['status'];
      $sample_id = $prm['sample_id'];

      $pos_code = $this->nat_pos;

      // GET POSITION
      $p = $this->db_smartone->query("SELECT Nat_PositionID id FROM nat_position WHERE Nat_PositionCode = ?", [$pos_code])
                              ->row();

      $sql = "SELECT T_OrderSampleReqID id FROM t_ordersamplereq 
               JOIN nat_position on T_OrderSampleReqNat_PositionID = Nat_PositionID 
                  AND Nat_PositionCode = ?
               WHERE T_OrderSampleReqT_OrderHeaderID = ?
               AND T_OrderSampleReqT_OrderSampleID = ?
               AND T_OrderSampleReqIsActive = 'Y'";
      $query = $this->db_smartone->query($sql, [$pos_code, $order_id, $sample_id]);
      $r = $query->row();
      
      if ($r)
      {
         $sql = "UPDATE t_ordersamplereq
                  SET T_OrderSampleReqStatus = ?, T_OrderSampleReqs = ?
                  WHERE T_OrderSampleReqID = ?";
         $query = $this->db_smartone->query($sql, [$status, $reqs, $r->id]);

         $this->sys_ok($r->id);
         exit;
      }
      else
      {
         $sql = "INSERT INTO t_ordersamplereq(T_OrderSampleReqT_OrderHeaderID, T_OrderSampleReqT_OrderSampleID, T_OrderSampleReqStatus, T_OrderSampleReqs, T_OrderSampleReqNat_PositionID)
                  VALUES(?, ?, ?, ?, ?)";
         $query = $this->db_smartone->query($sql, [$order_id, $sample_id, $status, $reqs, $p->id]);
         if ($query)
         {
               $this->sys_ok($this->db_smartone->insert_id());
               exit;
         }
      }

      $this->sys_error_db("Sample Handling Req", $this->db_smartone);
   }

   // PROCESS SECTION
   function verify_process()
   {
      $prm = $this->sys_input;

      $data = json_encode($prm['data']);
      $sql = "CALL sp_sampling_handling_02_process_reject('PRC', '{$prm['orderid']}', '{$prm['laststatusid']}', '{$data}', '{$this->sys_user['M_UserID']}')";
      
      $query = $this->db_smartone->query($sql);

      if ($query) 
      { 
         $row = $query->row();
         $this->sys_ok($row);
      } 
      else {
         $this->sys_error_db("verification verify", $this->db_smartone);
         exit;
      }
   }

   function reject_process()
   {
      $prm = $this->sys_input;

      $data = json_encode($prm['data']);
      $sql = "CALL sp_sampling_handling_02_process_reject('RJC', '{$prm['orderid']}', '{$prm['laststatusid']}', '{$data}', '{$this->sys_user['M_UserID']}')";
      
      $query = $this->db_smartone->query($sql);

      if ($query) 
      { 
         $row = $query->row();
         $this->sys_ok($row);
      } 
      else {
         $this->sys_error_db("verification reject", $this->db_smartone);
         exit;
      }
   }
}