<?php
class Priceeditadmin_v6 extends MY_Controller
{
   var $db_regional;

   public function index()
   {
      echo "Price API";
   }

   public function __construct()
   {
      parent::__construct();
      $this->db_regional = $this->load->database("regional", true);
   }

   public function search_company()
   {
      $prm = $this->sys_input;
      $max_rst = 999999999;

      // QUERY TOTAL
      $sql = "select distinct count(*) total
         from m_company
         join m_mou on M_MouM_CompanyID = M_CompanyID and M_MouIsActive = 'Y'
         and M_MouStartDate <= date(now()) and M_MouEndDate >= date(now())
         where M_CompanyIsActive = 'Y' and M_CompanyName LIKE ?
         order by M_CompanyName ASC";
      $query = $this->db_regional->query($sql, ['%'.$prm['search'].'%']);

      if ($query) { 
         $tot_count = $query->result_array()[0]["total"]; 
      } 
      else {
         $this->sys_error_db("price company count", $this->db_regional);
         exit;
      }

      $sql = "select distinct M_CompanyID, M_CompanyName
         from m_company
         join m_mou on M_MouM_CompanyID = M_CompanyID and M_MouIsActive = 'Y'
         -- and M_MouStartDate <= date(now()) 
         and M_MouEndDate >= date(now())
         where M_CompanyIsActive = 'Y' and M_CompanyName LIKE ?
         order by M_CompanyName ASC
         limit 0, {$max_rst}";
      $query = $this->db_regional->query($sql, ['%'.$prm['search'].'%']);

      if ($query) 
      { 
         $rows = $query->result_array();

         $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows), "q" => $this->db_regional->last_query());
         $this->sys_ok($result);
      } 
      else {
         $this->sys_error_db("price company rows", $this->db_regional);
         exit;
      }
   }

   public function search_mou()
   {
      $prm = $this->sys_input;
      $max_rst = 999999999;

      $search = '%' . $prm["search"] . '%';
      $company = $prm["company_id"];

      // QUERY TOTAL
      $sql = "select count(*) total
         from m_mou
         where M_MouIsActive = 'Y'
         and M_MouM_CompanyID = ?
         and M_MouName LIKE ?

         -- and date(now()) >= M_MouStartDate
         and date(now()) <= M_MouEndDate
         order by M_MouName ASC";
      $query = $this->db_regional->query($sql, [$company, $search]);

      if ($query) { 
         $tot_count = $query->result_array()[0]["total"]; 
      } 
      else {
         $this->sys_error_db("price mou count", $this->db_regional);
         exit;
      }

      $sql = "select M_MouID, M_MouName, M_MouStartDate, M_MouEndDate, M_MouIsVerified
         from m_mou
         where M_MouIsActive = 'Y'
         and M_MouM_CompanyID = ?
         and M_MouName LIKE ?

         -- and date(now()) >= M_MouStartDate
         and date(now()) <= M_MouEndDate
         order by M_MouName ASC
         limit 0, {$max_rst}";
      $query = $this->db_regional->query($sql, [$company, $search]);

      if ($query) 
      { 
         $rows = $query->result_array();

         $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows), "q" => $this->db_regional->last_query());
         $this->sys_ok($result);
      } 
      else {
         $this->sys_error_db("price mou rows", $this->db_regional);
         exit;
      }
   }

   public function search_price()
   {
      $prm = $this->sys_input;
      $max_rst = 10;

      $search = '%' . $prm["search"] . '%';
      $mou = $prm["mou_id"];
      $page = $prm['page'];

      if ($page == null)
         $page = 1;

      $offset = ($page - 1) * $max_rst;

      // QUERY TOTAL
      $sql = "select count(*) total
         from t_price
         join t_test on t_pricet_testid = t_testid
         where T_PriceIsActive = 'Y'
         and T_PriceM_MouID = ?
         and T_TestName LIKE ?
         order by T_TestName ASC";
      $query = $this->db_regional->query($sql, [$mou, $search]);

      if ($query) { 
         $tot_count = $query->result_array()[0]["total"]; 
      } 
      else {
         $this->sys_error_db("price mou count", $this->db_regional);
         exit;
      }

      $sql = "select T_PriceID, T_TestID, T_TestCode, T_TestName, T_PriceIsCito,
         T_TestSasCode,
         T_PriceM_CompanyID,
         T_PriceM_MouID,
         T_PricePriority,
         T_PriceAmount,
         T_PriceDisc,
         T_PriceDiscRp,
         T_PriceSubTotal,
         T_PriceOther,
         T_PriceTotal,
         JSON_OBJECT('T_TestID', T_TestID, 'T_TestCode', T_TestCode, 'T_TestName', T_TestName) px,
         M_MouIsVerified
         from t_price
         join t_test on t_pricet_testid = t_testid
         join m_mou on t_pricem_mouid = m_mouid

         where T_PriceIsActive = 'Y'
         and T_PriceM_MouID = ?
         and T_TestName LIKE ?
         order by T_TestSasCode ASC
         limit {$offset}, {$max_rst}";
      $query = $this->db_regional->query($sql, [$mou, $search]);
      //echo $this->db_regional->last_query();

      if ($query) 
      { 
         $rows = $query->result_array();

         foreach ($rows as $k => $v)
         {
            $rows[$k]['px'] = json_decode($v['px']);

            $sql = "select t_addonid id, t_addonname name, t_priceaddonamount price
               from t_priceaddon
               join t_addon on t_priceaddont_addonid = t_addonid
               where t_priceaddont_priceid = ?
               and t_priceaddonisactive = 'Y'";

            $query = $this->db_regional->query($sql, [$v['T_PriceID']]);

            $rows2 = [];
            if ($query) 
            {
               $rows2 = $query->result_array();
            }

            $rows[$k]['others'] = $rows2;
         }

         $result = array("total" => $tot_count, "records" => $rows, "total_page" => ceil($tot_count/$max_rst), "cur_page" => $page, "total_display" => sizeof($rows), "q" => $this->db_regional->last_query());
         $this->sys_ok($result);
      } 
      else {
         $this->sys_error_db("price mou rows", $this->db_regional);
         exit;
      }
   }

   public function del_price()
   {
      $prm = $this->sys_input;
      $id = $prm["id"];

      $sql = "update t_price
         set t_priceisactive = 'N'
         where T_PriceIsActive = 'Y'
         and T_PriceID = ?";
      $query = $this->db_regional->query($sql, [$id]);

      if ($query) 
      { 
         $sql = "update t_priceaddon
            set t_priceaddonisactive = 'N'
            where T_PriceaddonIsActive = 'Y'
            and t_priceaddont_priceid = ?";
         $query = $this->db_regional->query($sql, [$id]);

         $result = array("query"=>$this->db_regional->last_query(), "id"=>$id);
         $this->sys_ok($result);
      } 
      else {
         $this->sys_error_db("price mou rows", $this->db_regional);
         exit;
      }
   }

   public function search_px()
   {
      $prm = $this->sys_input;
      $max_rst = 50;

      $search = '%' . $prm["search"] . '%';
      $cito = $prm["cito"];

      // QUERY TOTAL
      $sql = "select count(*) total
         from t_test
         JOIN nat_test ON T_TestNat_TestID = Nat_TestID
         JOIN nat_testtype ON Nat_TestNat_TestTypeID = Nat_TestTypeID
         where T_TestIsActive = 'Y'
         and T_TestName LIKE ?
         and T_TestIsPrice = 'Y' AND Nat_TestTypeName <> 'Profile'
         and T_TestID not in ( 
            select T_PriceT_TestID from t_price where T_PriceM_MouID = ? and T_PriceIsActive = 'Y'
            and T_PriceIsCito = ?  )
            order by T_TestName ASC";
      $mou_id = $prm["mou_id"];
      $query = $this->db_regional->query($sql, [$search,$mou_id,$cito]);

      if ($query) { 
         $tot_count = $query->result_array()[0]["total"]; 
      } 
      else {
         $this->sys_error_db("px count", $this->db_regional);
         exit;
      }

      $sql = "select T_TestID, T_TestCode, T_TestSasCode,CONCAT('[',T_TestSasCode,'] ',T_TestName) T_TestName, Nat_TestTypeName, CONCAT(Nat_TestTypeName, ' | ', T_TestSasCode) as description 
         from t_test
         JOIN nat_test ON T_TestNat_TestID = Nat_TestID
         JOIN nat_testtype ON Nat_TestNat_TestTypeID = Nat_TestTypeID
         where T_TestIsActive = 'Y'
         and T_TestName LIKE ?
         and T_TestIsPrice = 'Y' AND Nat_TestTypeName <> 'Profile'
         and T_TestID not in ( 
            select T_PriceT_TestID from t_price where T_PriceM_MouID = ? and T_PriceIsActive = 'Y' 
            and T_PriceIsCito = ? )
            order by T_TestSasCode ASC
            limit 0, {$max_rst}";
      $query = $this->db_regional->query($sql, [$search,$mou_id,$cito]);

      if ($query) 
      { 
         $rows = $query->result_array();

         $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows), "q" => $this->db_regional->last_query());
         $this->sys_ok($result);
      } 
      else {
         $this->sys_error_db("px rows", $this->db_regional);
         exit;
      }
   }

   public function save_px()
   {
      $prm = $this->sys_input;
      $max_rst = 50;

      $data = $prm["data"];
      $data_others = $prm["data_others"];
      $j_data = json_decode($data,true);

      // QUERY
      // $sql = "INSERT INTO t_addon(T_AddonName) values('{$name}')";
      $query = $this->db_regional->query("CALL sp_master_price_save_v4b('{$data}', '{$data_others}', '0')");
      if ($query) { 
         $this->clean_mysqli_connection($this->db_regional->conn_id);
         $p_data = json_decode($data,true);
         $test_id = $p_data["test_id"];
         $mou_id = $p_data["mou_id"]; 
         $cito = $j_data["cito"];
         //snapshot to ss_price_mou
         $this->load->library("SsPriceMou_v4");
         $result =  $this->sspricemou_v4->edit($mou_id,$test_id,$cito);
         if (! $result[0] ) {
            $this->sys_error("SsPriceMou Edit : " . $result[1]);
            exit;
         } 
	 $result = $this->upload_edit_price($mou_id,  $test_id, $cito);

	 // sipe: add snapshot to ss_price_mou_px
	 try {
	   $this->load->library("SsPriceMouPx");
           $this->sspricemoupx->create($mou_id);
	 } catch(Exception $exc2) {
		 $message = $exc->getMessage();
		 $this->sys_error($message);
	 }

         if ($result[0] ) {
            $this->sys_ok($result);
         } else {
            $this->sys_error($result);
         }
         exit;
      } else {
         $this->sys_error_db("addon new", $this->db_regional);
         exit;
      }
   }

   public function upload_edit_price( $mou_id, $test_id, $cito ) {
      $sql = $this->db_regional->query("SELECT T_TestID as parent_test_id
      FROM (SELECT LEFT(T_TestSasCode,length(T_TestSasCode)-2) as sascode FROM t_test
      WHERE T_TestID = '{$test_id}' AND T_TestIsPrice = 'Y') a
      JOIN t_test ON T_TestSasCode = sascode")->row();
      $parent_testid = $sql->parent_test_id;
      if(!empty($parent_testid)){
            $parent_test_id = $sql->parent_test_id;
      }else{
            $parent_test_id = '0';
      }

      
      $sql = $this->db_regional->query("select group_concat(DISTINCT T_PacketID SEPARATOR ',') as packet_id from t_packet
      join t_packetdetail on T_PacketDetailT_PacketID = T_PacketID 
      where T_PacketM_MouID = '{$mou_id}' AND T_PacketDetailT_TestID = '{$test_id}'")->row();
      $packetid = $sql->packet_id;
      if(!empty($packetid)){
            $packet_id = $sql->packet_id;
            $sql = "select * from ss_price_mou where Ss_PriceMouM_MouID= $mou_id
            and T_TestID = $test_id and T_PriceIsCito = '{$cito}'
            union
            select * from ss_price_mou where Ss_PriceMouM_MouID = $mou_id
                     and T_TestID in ($parent_test_id)  and T_PriceIsCito = '{$cito}'
          union
          select * from ss_price_mou where Ss_PriceMouM_MouID = $mou_id
                   and packet_id in ($packet_id)";
         $qry = $this->db_regional->query($sql);
         if (! $qry) {
            return array(false, print_r($this->db_regional->error(),true));
         }
         $price_rows = $qry->result_array();
         if (count($price_rows) == 0 ) {
            return array(false, "No Ss Price Mou");
         }
      }else{
            $sql = "select * from ss_price_mou where Ss_PriceMouM_MouID= $mou_id
            and T_TestID = $test_id and T_PriceIsCito = '{$cito}'
            union
            select * from ss_price_mou where Ss_PriceMouM_MouID = $mou_id
                     and T_TestID in ($parent_test_id)  and T_PriceIsCito = '{$cito}'";
         $qry = $this->db_regional->query($sql);
         if (! $qry) {
            return array(false, print_r($this->db_regional->error(),true));
         }
         $price_rows = $qry->result_array();
         if (count($price_rows) == 0 ) {
            return array(false, "No Ss Price Mou");
         }
      }

      $sql = "select * from t_price where T_PriceM_MouID= ? and T_PriceT_TestId = ? 
         and T_PriceIsCito = ? ";
      $qry = $this->db_regional->query($sql, array($mou_id, $test_id, $cito));
      if (! $qry) {
         return array(false, print_r($this->db_regional->error(),true));
      }
      $t_rows = $qry->result_array();
      foreach($t_rows as $idx => $r) {
	      unset($t_rows[$idx]["T_PriceID"]);
      }
      // PAKET
      $sql = "select t_packet.* from t_packet
      join t_packetdetail on T_PacketDetailT_PacketID = T_PacketID 
      where T_PacketM_MouID = ? AND T_PacketDetailT_TestID = ?";
      $qry = $this->db_regional->query($sql, array($mou_id,$test_id));
      if (! $qry) {
         return array(false, print_r($this->db_regional->error(),true));
      } 
      $pkt_rows = $qry->result_array();

      // PAKET DETAIL
      $sql = "select t_packetdetail.* 
         from t_packet 
         join t_packetdetail on T_PacketDetailT_PacketID = T_PacketID 
         where T_PacketM_MouID = ? AND T_PacketDetailT_TestID = ?";
      $qry = $this->db_regional->query($sql, array($mou_id,$test_id));
      if (! $qry) {
         return array(false, print_r($this->db_regional->error(),true));
      } 
      $pd_rows = $qry->result_array();

      $param = array("price" => $price_rows, 
      "t_price" => $t_rows,
      "packet" => $pkt_rows,
      "packet_detail" => $pd_rows );
      $param_md5 = md5(json_encode($param));
      $j_param = json_encode(array("param" => $param, "md5" => $param_md5 ));


      $sql = "select * from m_branch
      JOIN s_regional ON M_BranchS_RegionalID = S_RegionalID AND S_RegionalIsDefault = 'Y'
      where M_BranchIsActive = 'Y'";
      $qry = $this->db_regional->query($sql);
      if (! $qry) {
         return array(false, print_r($this->db_regional->error(),true));
      }
      $rows = $qry->result_array();
      if (count($rows) == 0 ) {
         return array(false, "No Branch Active");
      }

      $sql = "insert into tx_mou(TxMouM_BranchIPAddress,TxMouT_TestID,
         TxMouM_BranchCode,TxMouM_MouID, TxMouJson,TxMouM_UserID )
         values(?,?,?,?,?,?)";
      $flag_error = false;
      $err_msg = array();
      $userID = $this->sys_user["M_UserID"];
      foreach($rows as $r) {
         $branchCode = $r["M_BranchCode"];
         $ipAddress = $r["M_BranchIPAddress"];
         $url = "http://$ipAddress/one-api/tools/price/r_mou_v5/edit";
         $qry =  $this->db_regional->query($sql, array($ipAddress, $test_id, $branchCode, $mou_id, $j_param,$userID));
         if (! $qry) {
            return array(false,  print_r($this->db_regional->error(),true));
         }
         try {
            $txMouID = $this->db_regional->insert_id();
            $post_rst = $this->post($url,$j_param);
            $j_rst = json_decode($post_rst,true);
            if ($j_rst["status"] != "OK" ) {
               $err_msg[] = $post_rst;
               $flag_error = true;
               $this->db_regional->query("update tx_mou set TxMouRetry = 1 where TxMouID = ? ", array($txMouID));
            } else {
               $this->db_regional->query("update tx_mou set TxMouStatus = 'Y' , TxMouRetry = 1 where TxMouID = ? ", array($txMouID));
            }
         } catch(Exception $e) {
            $err_msg[] = $e->getMessage();
            $flag_error = true;
         }
      }	  
      if ( $flag_error) {
         return array(false, join(",",$err_msg));
      }
      return array(true,"OK");		
   }

   public function search_addon()
   {
      $prm = $this->sys_input;
      $max_rst = 50;

      $search = '%' . $prm["search"] . '%';

      // QUERY TOTAL
      $sql = "select count(*) total
         from t_addon
         where T_AddonIsActive = 'Y'
         and T_AddonName LIKE ?
         order by T_AddonName ASC";
      $query = $this->db_regional->query($sql, [$search]);

      if ($query) { 
         $tot_count = $query->result_array()[0]["total"]; 
      } 
      else {
         $this->sys_error_db("addon count", $this->db_regional);
         exit;
      }

      $sql = "select T_AddonID, T_AddonName
         from t_addon
         where T_AddonIsActive = 'Y'
         and T_AddonName LIKE ?
         order by T_AddonName ASC
         limit 0, {$max_rst}";
      $query = $this->db_regional->query($sql, [$search]);

      if ($query) 
      { 
         $rows = $query->result_array();

         $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows), "q" => $this->db_regional->last_query());
         $this->sys_ok($result);
      } 
      else {
         $this->sys_error_db("addon rows", $this->db_regional);
         exit;
      }
   }

   public function save_addon()
   {
      $prm = $this->sys_input;
      $max_rst = 50;

      $name = $prm["name"];

      // QUERY
      // $sql = "INSERT INTO t_addon(T_AddonName) values('{$name}')";
      $query = $this->db_regional->set('T_AddonName', $name)
         ->insert('t_addon');

      if ($query) { 
         $result = array("inserted_id" => $this->db_regional->insert_id(), "q" => $this->db_regional->last_query());
         $this->sys_ok($result);

         exit;
      } 
      else {
         $this->sys_error_db("addon new", $this->db_regional);
         exit;
      }
   }

   public function search_company_2()
   {
      $prm = $this->sys_input;
      $search = '%'.$prm['query'].'%';

      $max_rst = 25;
      $sql = "SELECT M_CompanyID, M_CompanyName,
         IFNULL( concat('[', group_concat( json_object('M_MouID', M_MouID, 'M_MouName', M_MouName, 'M_MouStartDate', M_MouStartDate, 'M_MouEndDate', M_MouEndDate) ), ']'), '[]') as mou
         from m_company
         JOIN m_mou on M_MouM_CompanyID = M_CompanyID and M_MouIsActive = 'Y'
         and M_MouStartDate <= date(now()) and M_MouEndDate >= date(now())
         where M_CompanyIsActive = 'Y' 
         -- sipe ndoy minta di buka
         -- AND M_CompanyID <> {$prm['M_CompanyID']}
         and M_CompanyName LIKE ?
         group by m_companyid
         order by M_CompanyName ASC
         limit 0, {$max_rst}";
      $query = $this->db_regional->query($sql, [$search]);

      if ($query) 
      { 
         $rows = $query->result_array();
         foreach ($rows as $k => $v)
            $rows[$k]['mou'] = json_decode($v['mou']);

         $result = array("total" => 1, "records" => $rows, "total_display" => sizeof($rows), "q" => $this->db_regional->last_query());
         $this->sys_ok($result);
      } 
      else {
         $this->sys_error_db("price company rows", $this->db_regional);
         exit;
      }
   }

   public function copy_price()
   {
      $prm = $this->sys_input;
      $userid = $this->sys_user["M_UserID"];
      $groups = $prm['trx'];
      $copyall = $prm['copyall'];

      //$sql = "CALL sp_master_price_copy(?, ?, ?, 'N')";
      //$query = $this->db_regional->query($sql, [$prm['source_id'], $prm['target_id'], $prm['disc']]);
      if($copyall == 'N'){
         foreach($groups as $k => $v){
            if($v['chex']){
               $sql = "
                                                INSERT INTO t_price(
                                                T_PriceT_TestID,
                                                T_PriceIsCito,
                                                T_PriceM_MouID,
                                                T_PricePriority,
                                                T_PriceAmount,
                                                T_PriceDisc,
                                                T_PriceSubTotal,
                                                T_PriceTotal,
                                                T_PriceUserID)
                                                SELECT T_PriceT_TestID, 
                                                T_PriceIsCito, 
                                                {$prm['target_id']}, 
                                                T_PricePriority, 
                                                T_PriceAmount, 
                                                {$v['discount']},
                                                T_PriceAmount - (({$v['discount']}/100) * T_PriceAmount),
                                                T_PriceAmount - (({$v['discount']}/100) * T_PriceAmount),
                                                {$userid}
                                                FROM t_price
                                                JOIN t_test ON T_PriceT_TestID = T_TestID
                                                JOIN jpa_test ON JPA_TestNat_TestID = T_TestNat_TestID AND JPA_TestNat_JpaGroupID = {$v['Nat_JPAGroupID']}
                                                WHERE T_PriceM_MouID = {$prm['source_id']} AND T_PriceIsActive = 'Y'";
               $rows = $this->db_regional->query($sql);
            }
         }
      }
      else{
         $sql = "
                                INSERT INTO t_price(
                                T_PriceT_TestID,
                                T_PriceIsCito,
                                T_PriceM_MouID,
                                T_PricePriority,
                                T_PriceAmount,
                                T_PriceDisc,
                                T_PriceSubTotal,
                                T_PriceTotal,
                                T_PriceUserID)
                                SELECT T_PriceT_TestID, 
                                T_PriceIsCito, 
                                {$prm['target_id']}, 
                                T_PricePriority, 
                                T_PriceAmount, 
                                T_PriceDisc,
                                T_PriceSubTotal,
                                T_PriceTotal,
                                {$userid}
                                FROM t_price
                                JOIN t_test ON T_PriceT_TestID = T_TestID
                                WHERE T_PriceM_MouID = {$prm['source_id']} AND T_PriceIsActive = 'Y'
                                GROUP BY T_PriceT_TestID";

         $rows = $this->db_regional->query($sql);

         $sql = "SELECT *
            FROM t_packet
            WHERE
            T_PacketM_MouID = {$prm['source_id']} AND T_PacketIsActive = 'Y' ";
         $rows_parent = $this->db_regional->query($sql)->result();
         if($rows_parent){
            foreach($rows_parent as $k => $v){
               $sql = "INSERT INTO t_packet (
                  T_PacketM_CompanyID,
                  T_PacketM_MouID,
                  T_PacketType,
                  T_PacketName,
                  T_PacketPrice,
                  T_PacketOriginalPrice,
                  T_PacketIsNota,
                  T_PacketSequence,
                  T_PacketSasCode
               )
               VALUES(
            {$prm['company_target_id']},
            {$prm['target_id']},
            '{$v->T_PacketType}',
            '{$v->T_PacketName}',
            '{$v->T_PacketPrice}',
            '{$v->T_PacketOriginalPrice}',
            '{$v->T_PacketIsNota}',
            '{$v->T_PacketSequence}',
            '{$v->T_PacketSasCode}'
         )";
               $this->db_regional->query($sql);
               $last_id = $this->db_regional->insert_id();
               $sql = "SELECT * FROM t_packetdetail WHERE T_PacketDetailT_PacketID = {$v->T_PacketID} AND T_PacketDetailIsActive = 'Y'";
               $details = $this->db_regional->query($sql)->result();
               if($details){
                  foreach($details as $ik => $iv){
                     $sql = "INSERT INTO t_packetdetail (
                        T_PacketDetailT_PacketID,
                        T_PacketDetailT_TestID,
                        T_PacketDetailPrice
                     )
                     VALUES(
                  {$last_id},
                  {$iv->T_PacketDetailT_TestID},
                  {$iv->T_PacketDetailPrice}
               )";
                     $this->db_regional->query($sql);
                  }
               }
            }

         }
      }


      $result = array("total" => count($rows), "records" => $rows);
      $this->sys_ok($result);
   }

   public function search_single_company()
   {
      $prm = $this->sys_input;
      $sql = "SELECT M_CompanyID, M_CompanyName, CONCAT('[', GROUP_CONCAT(JSON_OBJECT('M_MouID', M_MouID, 'M_MouName', M_MouName, 'M_MouStartDate', M_MouStartDate, 'M_MouEndDate', M_MouEndDate, 'M_MouIsVerified', M_MouIsVerified) SEPARATOR ','), ']') mou
         from m_company
         join m_mou on M_MouM_CompanyID = M_CompanyID and M_MouIsActive = 'Y'
         -- and M_MouStartDate <= date(now()) 
         and M_MouEndDate >= date(now()) AND M_MouID = ?
         where M_CompanyIsActive = 'Y' 
         GROUP BY M_CompanyID
         order by M_CompanyName ASC";
      $query = $this->db_regional->query($sql, [$prm['id']]);

      if ($query) 
      { 
         $rows = $query->result_array();
         $rows[0]['mou'] = json_decode($rows[0]['mou']);

         $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows), "q" => $this->db_regional->last_query());
         $this->sys_ok($result);
      } 
      else {
         $this->sys_error_db("price company rows", $this->db_regional);
         exit;
      }
   }

   public function getjpagroups()
   {
      $prm = $this->sys_input;

      $sql = "select 'N' as chex, 0 as discount, Nat_JPAGroupID, Nat_JPAGroupName, Nat_JpaGroupMaxPxDisc
         from nat_jpagroup
         where Nat_JPAGroupIsActive = 'Y'";
      $query = $this->db_regional->query($sql, [$company, $search]);

      if ($query) 
      { 
         $rows = $query->result_array();
         foreach($rows as $k => $v){
            $rows[$k]['chex'] = false;
         }

         $result = array("total" => count($rows), "records" => $rows);
         $this->sys_ok($result);
      } 
      else {
         $this->sys_error_db("price mou rows", $this->db_regional);
         exit;
      }
   }

   public function search_price_packet()
   {
      $prm = $this->sys_input;
      $max_rst = 10;

      $search = '%' . $prm["search"] . '%';
      $mou = $prm["mou_id"];
      $page = $prm['page'];

      if ($page == null)
         $page = 1;

      $offset = ($page - 1) * $max_rst;

      // QUERY TOTAL
      $sql = "select count(*) total
         from t_packet
         where T_PacketIsActive = 'Y'
         and T_PacketM_MouID = ?
         and T_PacketName LIKE ?
         order by T_PacketName ASC";
      $query = $this->db_regional->query($sql, [$mou, $search]);

      if ($query) { 
         $tot_count = $query->result_array()[0]["total"]; 
      } 
      else {
         $this->sys_error_db("price mou count", $this->db_regional);
         exit;
      }

      $sql = "select T_PacketID, T_PacketName, T_PacketType,
         T_PacketPrice,	T_PacketOriginalPrice,
         T_PacketOriginalPrice - T_PacketPrice discrp
         from t_packet
         where T_PacketIsActive = 'Y'
         and T_PacketM_MouID = ?
         and T_PacketName LIKE ?
         order by T_PacketName ASC
         limit {$offset}, {$max_rst}";
      $query = $this->db_regional->query($sql, [$mou, $search]);

      if ($query) 
      { 
         $rows = $query->result_array();

         $result = array("total" => $tot_count, "records" => $rows, "total_page" => ceil($tot_count/$max_rst), "cur_page" => $page, "total_display" => sizeof($rows), "q" => $this->db_regional->last_query());
         $this->sys_ok($result);
      } 
      else {
         $this->sys_error_db("packet mou rows", $this->db_regional);
         exit;
      }
   }

   function post($url,$data) {
      $ch = curl_init($url);
      curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
      curl_setopt($ch, CURLOPT_POSTFIELDS, $data);
      curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
      curl_setopt($ch, CURLOPT_TIMEOUT, 100);
      curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 5);
      curl_setopt($ch, CURLOPT_HTTPHEADER, array(
         'Content-Type: application/json',
         'Content-Length: ' . strlen($data))
      );
      $result = curl_exec($ch);
      if (curl_errno($ch)){
         return json_encode( array("status" => "ERR",
            "message" => curl_error($ch)) );
      }
      curl_close($ch);

      return $result;
   }



}
?>