<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class SsPriceMou{

   // retrun array status, message
   public function create($mouID) {
      $CI =& get_instance();
      $this->db = $CI->load->database("regional",true);

      $sql = "select * from m_mou where M_MouID  = ?";
      $qry = $this->db->query($sql, array($mouID));
      if (! $qry ) {
         return array(false, print_r($this->db->error(),true));
      }
      $rows = $qry->result_array();
      if (count($rows) == 0 ) {
         return array(false, "MOU ID : $mouID not found");
      }
      $companyID = $rows[0]["M_MouM_CompanyID"];

      $sql = "select distinct T_PriceM_MouID, T_TestID, T_TestName, 'N' IsFromPanel, Nat_TestID,
         T_PriceT_TestID, T_PriceIsCito, T_PriceM_CompanyID, T_PriceM_MouID,
         T_PricePriority, T_PriceAmount, T_PriceDisc, T_PriceDiscRp, T_PriceSubTotal,
         T_PriceOther, T_PriceTotal, T_TestForceSell, 'N' is_packet, 0 packet_id,
         'PX' px_type, '[]' nat_test, '[]' child_test, 'N' IsFavourite,
         Nat_TestNat_TestTypeID, T_TestSasCode, $mouID Ss_PriceMouM_MouID
         from t_price 
         join t_test on T_PriceT_TestID = T_TestID 
         and T_PriceIsActive = 'Y' and T_TestIsActive = 'Y' 
         and T_TestIsPrice  = 'Y' 
         join nat_test on T_TestNat_TestID = Nat_TestID 
         and Nat_TestIsActive = 'Y' and Nat_TestNat_TestTypeID <> 5
         where T_PriceM_MouID = ?
         and length(T_TestSasCode) = 8 ";
      $qry = $this->db->query($sql, array($mouID));

      if (! $qry ) {
         return array(false, "Regional select t_price " . print_r($this->db->error(),true));
      }
      $rows = $qry->result_array();
      $flag_error = false;
      foreach($rows as $idx => $r) {
         $nat_testType = $r["Nat_TestNat_TestTypeID"];
         switch($nat_testType) {
         case 1: //Single 
            $rows[$idx]['nat_test'] = '[' . $r['Nat_TestID'] . ']';
            break;
         case 3: //Multi
         case 4: //Panel 
            $sasCode = $r["T_TestSasCode"] . '%';
            $sql = "select T_TestNat_TestID  
               from t_test
               where T_TestIsResult = 'Y'
               and T_TestSasCode like ? 
               and T_TestIsActive = 'Y'";
            $qry = $this->db->query($sql,array($sasCode));
            if (!$qry )  {
               return array(false, "Regional " . print_r($this->db->error(),true));
            }
            $nt_rows = $qry->result_array();
            $t_rows = array($r["Nat_TestID"]);
            foreach($nt_rows as $nr) {
               $t_rows[] = $nr["T_TestNat_TestID"];
            }
            $rows[$idx]['nat_test'] = "[" . join(",",$t_rows) . "]";
            break;
         default :
            $rows[$idx]['nat_test'] = '[' . $r['Nat_TestID'] . ']';
            break;
         }
         unset($rows[$idx]["Nat_TestNat_TestTypeID"]);
         unset($rows[$idx]["T_TestSasCode"]);
      }
      //Test Profile 
      // wip profile
      $sql = "select distinct $mouID T_PriceM_MouID, T_TestID, T_TestName, 'N' IsFromPanel, Nat_TestID,
         T_TestID T_PriceT_TestID, 'N' T_PriceIsCito, $companyID T_PriceM_CompanyID, $mouID T_PriceM_MouID,
         0 T_PricePriority, 0 T_PriceAmount, 0 T_PriceDisc, 0 T_PriceDiscRp, 0 T_PriceSubTotal,
         0 T_PriceOther, 0 T_PriceTotal, T_TestForceSell, 'N' is_packet, 0 packet_id,
         'PXR' px_type, '[]' nat_test, '[]' child_test, 'N' IsFavourite,
         Nat_TestNat_TestTypeID, T_TestSasCode, $mouID Ss_PriceMouM_MouID
         from t_test  
         join nat_test on T_TestNat_TestID = Nat_TestID 
         and Nat_TestIsActive = 'Y' and Nat_TestNat_TestTypeID = 5
         where length(T_TestSasCode) = 8 ";
      $qry = $this->db->query($sql, array($mouID));
      if (! $qry ) {
         return array(false, "Regional " . print_r($this->db->error(),true));
      }
      $p_rows = $qry->result_array();

      $sql = "select distinct substr(T_TestSasCode,1,8) parentCode, T_PriceM_MouID, T_TestID, T_TestName, 'N' IsFromPanel, Nat_TestID,
         T_PriceT_TestID, T_PriceIsCito, T_PriceM_CompanyID, T_PriceM_MouID,
         T_PricePriority, T_PriceAmount, T_PriceDisc, T_PriceDiscRp, T_PriceSubTotal,
         T_PriceOther, T_PriceTotal, T_TestForceSell, 'N' is_packet, 0 packet_id,
         'PX' px_type, concat('[', T_TestNat_TestID , ']') nat_test, '[]' child_test, 'N' IsFavourite,
         Nat_TestNat_TestTypeID, T_TestSasCode,T_TestIsResult, T_TestCode
         from t_price 
         join t_test on T_PriceT_TestID = T_TestID 
         and T_PriceIsActive = 'Y' and T_TestIsActive = 'Y' 
         and T_TestIsPrice  = 'Y' 
         and T_PriceIsCito = 'N'
         and length(T_TestSasCode) = 10
         join nat_test on T_TestNat_TestID = Nat_TestID 
         and Nat_TestIsActive = 'Y' 
         where T_PriceM_MouID = ?  ";
      $qry = $this->db->query($sql,array($mouID));
      if (!$qry )  {
         return array(false, "Regional child test " . print_r($this->db->error(),true));
      }
      $xrows = $qry->result_array();
      $arr_child = array();
      $p_codes = "'0'";
      foreach($xrows as $r ) {
         $pCode = $r["parentCode"];
         if ( ! isset($arr_child[$pCode])) {
            $arr_child[$pCode] = array();
         }
         $cCode = $r["T_TestSasCode"];
         $p_codes .= ", '$cCode'";
         unset($r["parentCode"]);
         $arr_child[$pCode][] = $r;
      }
      $sql = "select substr(T_TestSasCode,1,8) parentCode, 
         group_concat(distinct T_TestNat_TestID) nat  
         from t_test
         where ( T_TestIsResult = 'Y' or T_TestIsPrice = 'Y' )
         and T_TestSasCode in ( $p_codes )  
         and T_TestIsActive = 'Y'
         group by parentCode";
      $qry = $this->db->query($sql,array($mouID));
      if (!$qry )  {
         return array(false, "Regional " . print_r($this->db->error(),true));
      }
      $xrows = $qry->result_array();
      $arr_nat = array();
      foreach($xrows as $r ) {
         $pCode = $r["parentCode"];
         //if ( ! isset($arr_nat[$pCode])) {
         //   $arr_nat[$pCode] = array();
         //}
         $arr_nat[$pCode] = $r["nat"];
      }
      $flag_error = false;
      foreach($p_rows as $idx => $r) {
         $T_TestName= $r["T_TestName"] ;
         $sasCode = $r["T_TestSasCode"];
         if ( isset($arr_child[$sasCode]) ) {
            $the_childs = $arr_child[$sasCode];
            $p_rows[$idx]['child_test'] = json_encode($the_childs,true);
            if ( isset($arr_nat[$sasCode] )) {
               $p_rows[$idx]['nat_test'] = "[" . $arr_nat[$sasCode] . "]";
            } 
            unset($p_rows[$idx]["Nat_TestNat_TestTypeID"]);
            unset($p_rows[$idx]["T_TestSasCode"]);
         } else {
            unset($p_rows[$idx]);
         }
      }
      //Paket Panel / Profile
      $sql = "select distinct $mouID T_PriceM_MouID, T_PacketID T_TestID, T_PacketName T_TestName, 'N' IsFromPanel, 0 Nat_TestID,
         T_PacketID T_PriceT_TestID, 'N' T_PriceIsCito, $companyID T_PriceM_CompanyID, $mouID T_PriceM_MouID,
         0 T_PricePriority, T_PacketOriginalBruto T_PriceAmount, 0 T_PriceDisc, 
         (T_PacketOriginalBruto - T_PacketPrice) T_PriceDiscRp, 0 T_PriceSubTotal,
         0 T_PriceOther, T_PacketPrice T_PriceTotal, 'Y' T_TestForceSell, 'Y' is_packet, T_PacketID  packet_id,
         T_PacketType px_type, '[]' nat_test, '[]' child_test, 'N' IsFavourite,
         $mouID Ss_PriceMouM_MouID
         from 
         t_packet
         where 
         T_PacketIsActive = 'Y' 
         and T_PacketM_MouID = ?";

      $qry = $this->db->query($sql, array($mouID));
      if (! $qry ) {
         return array(false, "Regional " . print_r($this->db->error(),true));
      }
      $pn_rows = $qry->result_array();
      foreach($pn_rows as $idx => $pnr) {
         $packetID = $pnr["packet_id"];
         //child test
         $sql = "select distinct $mouID T_PriceM_MouID, T_TestID, T_TestName, 'N' IsFromPanel, Nat_TestID,
            T_TestID T_PriceT_TestID, 'N' T_PriceIsCito, $companyID T_PriceM_CompanyID, 
            $mouID T_PriceM_MouID, 0 T_PricePriority, T_PacketDetailPriceAmount T_PriceAmount, 
            T_PacketDetailPriceDisc T_PriceDisc, T_PacketDetailPriceDiscRp T_PriceDiscRp, T_PacketDetailPriceSubTotal T_PriceSubTotal,
            0 T_PriceOther, T_PacketDetailPrice T_PriceTotal, 
            'Y' T_TestForceSell, 'N' is_packet, 0 packet_id,
            'PX' px_type, concat('[', T_TestNat_TestID , ']') nat_test, '[]' child_test, 'N' IsFavourite, T_TestSasCode
            from t_packetdetail
            join t_test on T_PacketDetailT_TestID = T_TestID 
            and T_PacketDetailIsActive = 'Y' and T_TestIsActive = 'Y' 
            and T_PacketDetailT_PacketID = ?
            join nat_test on T_TestNat_TestID = Nat_TestID 
            and Nat_TestIsActive = 'Y'";
         $qry = $this->db->query($sql,array($packetID));
         if (!$qry )  {
            return array(false, print_r($this->db->error(),true));
         }
         $ct_rows = $qry->result_array();
         $p_nat_test = array();
         foreach($ct_rows as $ct_idx => $cr) {
            $sasCode = $cr["T_TestSasCode"] . '%';
            $sql = "select distinct T_TestNat_TestID 
               from t_test
               where T_TestSasCode like ? 
               and T_TestIsActive = 'Y'";
            $qry = $this->db->query($sql,array($sasCode));
            if (!$qry )  {
               return array(false, print_r($this->db->error(),true));
            }
            $nt_rows = $qry->result_array();
            $t_rows = array();
            foreach($nt_rows as $nr) {
               $t_rows[] = intval( $nr["T_TestNat_TestID"]);
               $p_nat_test[]= intval( $nr["T_TestNat_TestID"]);
            }
            $ct_rows[$ct_idx]['nat_test'] = json_encode($t_rows,JSON_NUMERIC_CHECK);
         }
         if (count($ct_rows) > 0 ) {
            $x_arr = array();
            foreach($ct_rows as $x_cr) {
               $x_arr[] = $x_cr;
            }
            $pn_rows[$idx]['child_test'] = json_encode($x_arr,true);
            $pn_rows[$idx]['nat_test'] = json_encode($p_nat_test,true);
         }
         unset($pn_rows[$idx]["Nat_TestNat_TestTypeID"]);
         unset($pn_rows[$idx]["T_TestSasCode"]);
      }

      $rows = array_merge($rows,$p_rows, $pn_rows);
      $qry = $this->db->query("delete from ss_price_mou where Ss_PriceMouM_MouID=?", array($mouID));
      if ( ! $qry ) {
         return array(false, "Regional " . print_r($this->db->error(),true));
      }
      $qry = $this->db->insert_batch("ss_price_mou", $rows);
      if ( ! $qry ) {
         return array(false, "Regional " . print_r($this->db->error(),true));
      }
      return array(true, "OK");
   }
   public function edit($mouID,$testID,$cito) {
      $CI =& get_instance();
      $this->db = $CI->load->database("regional",true);

      $sql = "select * from t_test where T_TestID = ? ";
      $qry = $this->db->query($sql, array($testID));
      if (! $qry ) {
         return array(false, print_r($this->db->error(),true));
      }
      $rows = $qry->result_array();
      if (count($rows) == 0 ) {
         return array(false, "No Test $testID found");
      }
      $sasCode = $rows[0]["T_TestSasCode"];
      if ( strlen($sasCode) == 8 ) {
         $flagProfile = false;
      } else {
         $flagProfile = true;
      } 

      $sql = "select * from m_mou where M_MouID  = ?";
      $qry = $this->db->query($sql, array($mouID));
      if (! $qry ) {
         return array(false, print_r($this->db->error(),true));
      }
      $rows = $qry->result_array();
      if (count($rows) == 0 ) {
         return array(false, "No MOU $mouID found");
      }
      $mouName = $rows[0]["M_MouName"];
      $companyID = $rows[0]["M_MouM_CompanyID"];

      $sql = "select distinct T_PriceM_MouID, T_TestID, T_TestName, 'N' IsFromPanel, Nat_TestID,
         T_PriceT_TestID, T_PriceIsCito, T_PriceM_CompanyID, T_PriceM_MouID,
         T_PricePriority, T_PriceAmount, T_PriceDisc, T_PriceDiscRp, T_PriceSubTotal,
         T_PriceOther, T_PriceTotal, T_TestForceSell, 'N' is_packet, 0 packet_id,
         'PX' px_type, '[]' nat_test, '[]' child_test, 'N' IsFavourite,
         Nat_TestNat_TestTypeID, T_TestSasCode, $mouID Ss_PriceMouM_MouID
         from t_price 
         join t_test on T_PriceT_TestID = T_TestID and T_TestID = ?
         and T_PriceIsActive = 'Y' and T_TestIsActive = 'Y' 
         and T_TestIsPrice  = 'Y' and T_PriceIsCito = ?
         join nat_test on T_TestNat_TestID = Nat_TestID 
         and Nat_TestIsActive = 'Y' and Nat_TestNat_TestTypeID <> 5
         where T_PriceM_MouID = ?";
      $qry = $this->db->query($sql, array($testID, $cito, $mouID));
      if (! $qry ) {
         return array(false, print_r($this->db->error(),true));
      }
      $rows = $qry->result_array();

      foreach($rows as $idx => $r) {
         $nat_testType = $r["Nat_TestNat_TestTypeID"];
         switch($nat_testType) {
         case 1: //Single 
            $rows[$idx]['nat_test'] = '[' . $r['Nat_TestID'] . ']';
            break;
         case 3: //Multi
         case 4: //Panel 
            $sasCode = $r["T_TestSasCode"] . '%';
            $sql = "select T_TestNat_TestID  
               from t_test
               where T_TestIsResult = 'Y'
               and T_TestSasCode like ? 
               and T_TestIsActive = 'Y'";
            $qry = $this->db->query($sql,array($sasCode));
            if (!$qry )  {
               return array(false, print_r($this->db->error(),true));
            }
            $nt_rows = $qry->result_array();
            $t_rows = array($r["Nat_TestID"]);
            foreach($nt_rows as $nr) {
               $t_rows[] = $nr["T_TestNat_TestID"];
            }
            $rows[$idx]['nat_test'] = "[" . join(",",$t_rows) . "]";
            break;
         default :
            $rows[$idx]['nat_test'] = '[' . $r['Nat_TestID'] . ']';
            break;
         }
         unset($rows[$idx]["Nat_TestNat_TestTypeID"]);
      }
      if ( count($rows) > 0 ) {
         $r = $rows[0];
         unset($r["T_TestSasCode"]);
         $this->db->where("T_PriceM_MouID", $r["T_PriceM_MouID"]);
         $this->db->where("T_TestID", $r["T_TestID"]);
         $this->db->where("T_PriceIsCito", $r["T_PriceIsCito"]);
         $qry = $this->db->update("ss_price_mou",$r);
         if (! $qry ) {
            return array(false, print_r($this->db->error(),true));
         }
      }
      $sasCode = substr($sasCode,0,8);
      foreach($rows as $idx => $r ) {
         if ( strlen($r["T_TestSasCode"]) > 8  ) {
            unset($rows[$idx]);
         }  else {
            unset($rows[$idx]["T_TestSasCode"]);
         }
      }
      if ($cito == 'Y' ) {
         return array(true,"OK",$rows);
      }
      // for non cito
      if($flagProfile ) {
         //wip profile
         $sql = "select distinct $mouID T_PriceM_MouID, T_TestID, T_TestName, 'N' IsFromPanel, Nat_TestID,
            T_TestID T_PriceT_TestID, 'N' T_PriceIsCito, $companyID T_PriceM_CompanyID, $mouID T_PriceM_MouID,
            0 T_PricePriority, 0 T_PriceAmount, 0 T_PriceDisc, 0 T_PriceDiscRp, 0 T_PriceSubTotal,
            0 T_PriceOther, 0 T_PriceTotal, T_TestForceSell, 'N' is_packet, 0 packet_id,
            'PXR' px_type, '[]' nat_test, '[]' child_test, 'N' IsFavourite,
            Nat_TestNat_TestTypeID, T_TestSasCode, $mouID Ss_PriceMouM_MouID
            from t_test  
            join nat_test on T_TestNat_TestID = Nat_TestID and T_TestSasCode = ?
            and Nat_TestIsActive = 'Y' and Nat_TestNat_TestTypeID = 5
            where length(T_TestSasCode) = 8 ";
         $qry = $this->db->query($sql, array($sasCode));

         if (! $qry ) {
            return array(false, print_r($this->db->error(),true));
         }
         $p_rows = $qry->result_array();

         $sasCodeLike = $sasCode . "%";
         $sql = "select distinct substr(T_TestSasCode,1,8) parentCode, T_PriceM_MouID, T_TestID, T_TestName, 'N' IsFromPanel, Nat_TestID,
            T_PriceT_TestID, T_PriceIsCito, T_PriceM_CompanyID, T_PriceM_MouID,
            T_PricePriority, T_PriceAmount, T_PriceDisc, T_PriceDiscRp, T_PriceSubTotal,
            T_PriceOther, T_PriceTotal, T_TestForceSell, 'N' is_packet, 0 packet_id,
            'PX' px_type, concat('[', T_TestNat_TestID , ']') nat_test, '[]' child_test, 'N' IsFavourite,
            Nat_TestNat_TestTypeID, T_TestSasCode,T_TestIsResult, T_TestCode
            from t_price 
            join t_test on T_PriceT_TestID = T_TestID 
            and T_PriceIsActive = 'Y' and T_TestIsActive = 'Y' 
            and T_TestIsPrice  = 'Y' and T_PriceIsCito = 'N'
            and T_TestSasCode like ?
            join nat_test on T_TestNat_TestID = Nat_TestID 
            and Nat_TestIsActive = 'Y' 
            where T_PriceM_MouID = ?  ";
         $qry = $this->db->query($sql,array($sasCodeLike,$mouID));
         if (!$qry )  {
            return array(false, "Regional child_test " . print_r($this->db->error(),true));
         }
         $xrows = $qry->result_array();
         $arr_child = array();
         $p_codes = "'0'";
         foreach($xrows as $r ) {
            $pCode = $r["parentCode"];
            if ( ! isset($arr_child[$pCode])) {
               $arr_child[$pCode] = array();
            }
            $cCode = $r["T_TestSasCode"];
            $p_codes .= ", '$cCode'";
            unset($r["parentCode"]);
            $arr_child[$pCode][] = $r;
         }

         $sql = "select substr(T_TestSasCode,1,8) parentCode, 
            group_concat(distinct T_TestNat_TestID) nat  
            from t_test
            where ( T_TestIsResult = 'Y' or T_TestIsPrice = 'Y' )
            and T_TestSasCode in ( $p_codes )  
            and T_TestIsActive = 'Y'
            group by parentCode";
         $qry = $this->db->query($sql,array($mouID));
         if (!$qry )  {
            return array(false, "Regional nat_test " . print_r($this->db->error(),true));
         }
         $xrows = $qry->result_array();
         $arr_nat = array();
         foreach($xrows as $r ) {
            $pCode = $r["parentCode"];
            //if ( ! isset($arr_nat[$pCode])) {
            //   $arr_nat[$pCode] = array();
            //}
            $arr_nat[$pCode] = $r["nat"];
         }

         $flag_error = false;
         foreach($p_rows as $idx => $r) {
            $T_TestName= $r["T_TestName"] ;
            $sasCode = $r["T_TestSasCode"];
            if ( isset($arr_child[$sasCode]) ) {
               $the_childs = $arr_child[$sasCode];
               $p_rows[$idx]['child_test'] = json_encode($the_childs,true);
               if ( isset($arr_nat[$sasCode] )) {
                  $p_rows[$idx]['nat_test'] = "[" . $arr_nat[$sasCode] . "]";
               } 
               unset($p_rows[$idx]["Nat_TestNat_TestTypeID"]);
               unset($p_rows[$idx]["T_TestSasCode"]);
            } else {
               unset($p_rows[$idx]);
            }
         }

         foreach($p_rows as $r) {
            $this->db->where("T_PriceM_MouID", $r["T_PriceM_MouID"]);
            $this->db->where("T_TestID", $r["T_TestID"]);
            $this->db->where("T_PriceIsCito", $r["T_PriceIsCito"]);
            $qry = $this->db->update("ss_price_mou",$r);
            if (! $qry ) {
               return array(false, "Err Update Ss_priceMou " . print_r($this->db->error(),true));
            }
         }
         $rows = array_merge($rows,$p_rows);
      }
      //Update Panel yang mengandung Test 
      $sql = "select 
         distinct T_PacketDetailT_PacketID
         from 
         t_packetdetail
         where T_PacketDetailIsActive = 'Y'
         and T_PacketDetailT_TestID = ?";
      $qry = $this->db->query($sql, array($testID));
      if (!$qry) {
         return array(false, print_r($this->db->error(),true));
      }
      $xrows = $qry->result_array();
      $packet_ids = "0";
      foreach($xrows as $r ) {
         $packet_ids .= "," . $r["T_PacketDetailT_PacketID"]; 
      }

      $sql = "select distinct $mouID T_PriceM_MouID, T_PacketID T_TestID, T_PacketName T_TestName, 'N' IsFromPanel, 0 Nat_TestID,
         T_PacketID T_PriceT_TestID, 'N' T_PriceIsCito, $companyID T_PriceM_CompanyID, $mouID T_PriceM_MouID,
         0 T_PricePriority, T_PacketOriginalBruto T_PriceAmount, 0 T_PriceDisc, 
         (T_PacketOriginalBruto - T_PacketPrice) T_PriceDiscRp, 0 T_PriceSubTotal,
         0 T_PriceOther, T_PacketPrice T_PriceTotal, 'Y' T_TestForceSell, 'Y' is_packet, T_PacketID  packet_id,
         T_PacketType px_type, '[]' nat_test, '[]' child_test, 'N' IsFavourite,
         $mouID Ss_PriceMouM_MouID
         from 
         t_packet
         where 
         T_PacketIsActive = 'Y' and T_PacketID in ( $packet_ids )
         and T_PacketM_MouID = ?";

      $qry = $this->db->query($sql, array($mouID));
      if (! $qry ) {
         return array(false, print_r($this->db->error(),true));
      }
      $pn_rows = $qry->result_array();
      foreach($pn_rows as $idx => $pnr) {
         $packetID = $pnr["packet_id"];
         //child test
         $sql = "select $mouID T_PriceM_MouID, T_TestID, T_TestName, 'N' IsFromPanel, Nat_TestID,
            T_TestID T_PriceT_TestID, 'N' T_PriceIsCito, $companyID T_PriceM_CompanyID, 
            $mouID T_PriceM_MouID, 0 T_PricePriority, T_PacketDetailPriceAmount T_PriceAmount, 
            T_PacketDetailPriceDisc T_PriceDisc, T_PacketDetailPriceDiscRp T_PriceDiscRp, T_PacketDetailPriceSubTotal T_PriceSubTotal,
            0 T_PriceOther, T_PacketDetailPrice T_PriceTotal, 
            'Y' T_TestForceSell, 'N' is_packet, 0 packet_id,
            'PX' px_type, '[]' nat_test, '[]' child_test, 'N' IsFavourite, T_TestSasCode
            from t_packetdetail
            join t_test on T_PacketDetailT_TestID = T_TestID 
            and T_PacketDetailIsActive = 'Y' and T_TestIsActive = 'Y' 
            and T_PacketDetailT_PacketID = ?
            join nat_test on T_TestNat_TestID = Nat_TestID 
            and Nat_TestIsActive = 'Y' 
";
         $qry = $this->db->query($sql,array($packetID));
         if (!$qry )  {
            return array(false, print_r($this->db->error(),true));
         }
         $ct_rows = $qry->result_array();
         $p_nat_test = array();
         foreach($ct_rows as $ct_idx => $cr) {
            $sasCode = $cr["T_TestSasCode"] . '%';
            $sql = "select distinct T_TestNat_TestID 
               from t_test
               where T_TestSasCode like ? 
               and T_TestIsActive = 'Y'";
            $qry = $this->db->query($sql,array($sasCode));
            if (!$qry )  {
               return array(false, print_r($this->db->error(),true));
            }
            $nt_rows = $qry->result_array();
            $t_rows = array();
            foreach($nt_rows as $nr) {
               $t_rows[] = intval( $nr["T_TestNat_TestID"]);
               $p_nat_test[]= intval( $nr["T_TestNat_TestID"]);
            }
            $ct_rows[$ct_idx]['nat_test'] = json_encode($t_rows,JSON_NUMERIC_CHECK);
         }
         if (count($ct_rows) > 0 ) {
            $x_arr = array();
            foreach($ct_rows as $x_cr) {
               $x_arr[] = $x_cr;
            }
            $pn_rows[$idx]['child_test'] = json_encode($x_arr,true);
            $pn_rows[$idx]['nat_test'] = json_encode($p_nat_test,true);
         }
         unset($pn_rows[$idx]["Nat_TestNat_TestTypeID"]);
         unset($pn_rows[$idx]["T_TestSasCode"]);
      }
      foreach($pn_rows as $r ) {
         $this->db->where("T_PriceM_MouID", $r["T_PriceM_MouID"]);
         $this->db->where("T_TestID", $r["T_TestID"]);
         $this->db->where("T_PriceIsCito", $r["T_PriceIsCito"]);
         $qry = $this->db->update("ss_price_mou",$r);
         if (! $qry ) {
            return array(false, print_r($this->db->error(),true));
         }
      }
      $rows = array_merge($rows,$pn_rows);
      return array(true,"OK",$rows);
   }
}