<?php

class Pricerujukan extends MY_Controller
{
   var $db_regional;
   public function index()
   {
      echo "PRICE RUJUKAN API";
   }
   public function __construct()
   {
      parent::__construct();
      $this->db_regional = $this->load->database("regional", true);
   }
	
    function lookuppricerujukanbyname(){
        try {
            //# cek token valid
            if (! $this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
    
            $prm = $this->sys_input;
            $rujukantype = $prm['rujukantype'];
        $pricerujukan = $prm['pricerujukan'];
        $regional = $prm['regional'];
        $test = $prm['test'];
        $price = $prm['price'];
            $all = $prm['all'];
            $limit = '';
            if($all == 'N'){
                $limit = ' LIMIT 10';
            }
            $number_limit = 10;
            $number_offset = ($prm['current_page'] - 1) * $number_limit ;
            if($price == ''){
                $sql = "select COUNT(*) as total
                FROM(SELECT *
                        from nat_price_rujukan 
                        JOIN nat_price_rujukantype ON Nat_PriceRujukanNat_PriceRujukanTypeCode = Nat_PriceRujukanTypeCode AND Nat_PriceRujukanTypeIsActive = 'Y'
                        JOIN t_test ON Nat_PriceRujukanT_TestID	= T_TestID
                        JOIN s_regional ON Nat_PriceRujukanS_RegionalID = S_RegionalID
                        WHERE
                        Nat_PriceRujukanNat_PriceRujukanTypeCode = '{$rujukantype}' AND
                        ($test = 0 OR($test > 0 AND T_TestID = $test)) AND
                        ($regional = 0 OR($regional > 0 AND S_RegionalID = $regional)) AND
                    Nat_PriceRujukanIsActive = 'Y' GROUP BY Nat_PriceRujukanID) a";
              //  $total = $this->db_regional->query($sql,$sql_param)->row()->total;
              $query = $this->db_regional->query($sql);
              //echo $this->db_regional->last_query();
              $tot_count = 0;
              $tot_page = 0;
              if ($query) {
                 $tot_count = $query->result_array()[0]["total"];
                 $tot_page = ceil($tot_count/$number_limit);
              } else {
                 $this->sys_error_db("nat_price_rujukan count", $this->db_regional);
                 exit;
              }
                
                $sql = "select Nat_PriceRujukanID as id,
                    Nat_PriceRujukanTypeID,
                    Nat_PriceRujukanTypeCode,
                    Nat_PriceRujukanTypeName,
                    S_RegionalID,
                    S_RegionalName,
                    T_TestID,	
                    T_TestName,
                    CONCAT(Nat_TestTypeName, ' | ', T_TestSasCode) as description,
                    nat_price_rujukan.*
                            from nat_price_rujukan 
                            JOIN nat_price_rujukantype ON Nat_PriceRujukanNat_PriceRujukanTypeCode = Nat_PriceRujukanTypeCode AND Nat_PriceRujukanTypeIsActive = 'Y'
                            LEFT JOIN s_regional ON Nat_PriceRujukanS_RegionalID = S_RegionalID
                            LEFT JOIN t_test ON Nat_PriceRujukanT_TestID = T_TestID      
        JOIN nat_test ON T_TestNat_TestID = Nat_TestID
        JOIN nat_testtype ON Nat_TestNat_TestTypeID = Nat_TestTypeID
                            WHERE
                            Nat_PriceRujukanNat_PriceRujukanTypeCode = '{$rujukantype}' AND
                            ($test = 0 OR($test > 0 AND T_TestID = $test)) AND
                            ($regional = 0 OR($regional > 0 AND S_RegionalID = $regional)) AND 
                            Nat_PriceRujukanIsActive = 'Y'
                            GROUP BY Nat_PriceRujukanID
                        ORDER BY Nat_PriceRujukanTypeName ASC, T_TestSasCode ASC
                        limit $number_limit offset $number_offset";
                $sql_param = array($search);
                $query = $this->db_regional->query($sql);
                //echo $this->db_regional->last_query();
                 
            }else{
                $sql = "select COUNT(*) as total
                FROM(SELECT *
                        from nat_price_rujukan 
                        JOIN nat_price_rujukantype ON Nat_PriceRujukanNat_PriceRujukanTypeCode = Nat_PriceRujukanTypeCode AND Nat_PriceRujukanTypeIsActive = 'Y'
                        JOIN t_test ON Nat_PriceRujukanT_TestID	= T_TestID
                        JOIN s_regional ON Nat_PriceRujukanS_RegionalID = S_RegionalID
                        WHERE
                        Nat_PriceRujukanNat_PriceRujukanTypeCode = '{$rujukantype}' AND
                        ($test = 0 OR($test > 0 AND T_TestID = $test)) AND
                        ($regional = 0 OR($regional > 0 AND S_RegionalID = $regional)) AND
                        Nat_PriceRujukanAmount = '{$price}' AND
                    Nat_PriceRujukanIsActive = 'Y' GROUP BY Nat_PriceRujukanID) a";
              //  $total = $this->db_regional->query($sql,$sql_param)->row()->total;
              $query = $this->db_regional->query($sql);
              //echo $this->db_regional->last_query();
              $tot_count = 0;
              $tot_page = 0;
              if ($query) {
                 $tot_count = $query->result_array()[0]["total"];
                 $tot_page = ceil($tot_count/$number_limit);
              } else {
                 $this->sys_error_db("nat_price_rujukan count", $this->db_regional);
                 exit;
              }
                
                $sql = "select Nat_PriceRujukanID as id,
                    Nat_PriceRujukanTypeID,
                    Nat_PriceRujukanTypeCode,
                    Nat_PriceRujukanTypeName,
                    S_RegionalID,
                    S_RegionalName,
                    T_TestID,	
                    T_TestName,
                    CONCAT(Nat_TestTypeName, ' | ', T_TestSasCode) as description,
                    nat_price_rujukan.*
                            from nat_price_rujukan 
                            JOIN nat_price_rujukantype ON Nat_PriceRujukanNat_PriceRujukanTypeCode = Nat_PriceRujukanTypeCode AND Nat_PriceRujukanTypeIsActive = 'Y'
                            LEFT JOIN s_regional ON Nat_PriceRujukanS_RegionalID = S_RegionalID
                            LEFT JOIN t_test ON Nat_PriceRujukanT_TestID = T_TestID            
        JOIN nat_test ON T_TestNat_TestID = Nat_TestID
        JOIN nat_testtype ON Nat_TestNat_TestTypeID = Nat_TestTypeID
                            WHERE
                            Nat_PriceRujukanNat_PriceRujukanTypeCode = '{$rujukantype}' AND
                            ($test = 0 OR($test > 0 AND T_TestID = $test)) AND
                            ($regional = 0 OR($regional > 0 AND S_RegionalID = $regional)) AND
                            Nat_PriceRujukanAmount = '{$price}' AND
                            Nat_PriceRujukanIsActive = 'Y'
                            GROUP BY Nat_PriceRujukanID
                        ORDER BY Nat_PriceRujukanTypeName ASC, T_TestSasCode ASC
                        limit $number_limit offset $number_offset";
                $sql_param = array($search);
                $query = $this->db_regional->query($sql);
                //echo $this->db_regional->last_query();
                
            }
            if ($query) {
                $rows = $query->result_array();
                
                
            } else {
              $this->sys_error_db("nat_price_rujukan select");
              exit;
            }
    
            $result = array ("total" => $tot_page, "total_filter"=>count($rows),"records" => $rows);
            $this->sys_ok($result);
    
        } catch(Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
   public function lookuprujukantypebyname()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
        }

        $prm = $this->sys_input;
        $rujukantype = $prm['rujukantype'];
		$pricerujukan = $prm['pricerujukan'];
		$limit = '';
		if($all == 'N'){
			$limit = ' LIMIT 10';
        }
        $number_limit = 10;
        $number_offset = ($prm['current_page'] - 1) * $number_limit ;
		$sql = "select COUNT(*) as total
		FROM(SELECT *
                from nat_price_rujukantype
                LEFT JOIN nat_price_rujukan ON Nat_PriceRujukanTypeCode = Nat_PriceRujukanNat_PriceRujukanTypeCode AND Nat_PriceRujukanIsActive = 'Y'
                where 
                Nat_PriceRujukanTypeName LIKE CONCAT('%','{$rujukantype}','%') AND
				Nat_PriceRujukanTypeIsActive = 'Y'
				GROUP BY Nat_PriceRujukanTypeID) a";
        $sql_param = array($search);
      //  $total = $this->db_regional->query($sql,$sql_param)->row()->total;
      $query = $this->db_regional->query($sql);
      $tot_count = 0;
      $tot_page = 0;
      if ($query) {
         $tot_count = $query->result_array()[0]["total"];
         $tot_page = ceil($tot_count/$number_limit);
      } else {
         $this->sys_error_db("nat_price_rujukantype count", $this->db_regional);
         exit;
      }
		
        $sql = "select Nat_PriceRujukanTypeID as id,
        Nat_PriceRujukanTypeName as name,
        Nat_PriceRujukanTypeName as namex,
        Nat_PriceRujukanTypeCode as code,
        nat_price_rujukantype.*
                from nat_price_rujukantype 
                LEFT JOIN nat_price_rujukan ON Nat_PriceRujukanTypeCode = Nat_PriceRujukanNat_PriceRujukanTypeCode AND Nat_PriceRujukanIsActive = 'Y'
                where 
                Nat_PriceRujukanTypeName LIKE CONCAT('%','{$rujukantype}','%') AND
				Nat_PriceRujukanTypeIsActive = 'Y'
                GROUP BY Nat_PriceRujukanTypeID
                ORDER BY Nat_PriceRujukanTypeName ASC
                limit $number_limit offset $number_offset";
        $sql_param = array($search);
        $query = $this->db_regional->query($sql);
		//echo $this->db_regional->last_query();
        if ($query) {
            $rows = $query->result_array();
			
            
        } else {
          $this->sys_error_db("nat_price_rujukantype select");
          exit;
        }

        $result = array ("total" => $tot_page, "total_filter"=>count($rows),"records" => $rows);
        $this->sys_ok($result);

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   public function addnewrujukantype()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
			$this->sys_error("Invalid Token");
			exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
        $code = $prm['code'];
        $name = $prm['name'];
        $query = "SELECT COUNT(*) as exist FROM nat_price_rujukantype WHERE Nat_PriceRujukanTypeIsActive = 'Y' AND Nat_PriceRujukanTypeCode = '{$code}'";
        $exist_name =  $this->db_regional->query($query)->row()->exist;
        if($exist_name == 0  ){
            $sql = "insert into nat_price_rujukantype(
                Nat_PriceRujukanTypeCode,
                Nat_PriceRujukanTypeName
				) 
				values( ?, ?)";
			$query = $this->db_regional->query($sql,
				array(
                    $code,
                    $name
				)
			);
			//echo $query;
			if (!$query) {
				$this->sys_error_db("nat_price_rujukantype insert");
				exit;
            }
            $last_id = $this->db_regional->insert_id();
			$result = array ("total" => 1, "records" => array("xid" => 0));
            $this->sys_ok($result);
            $last_id = $this->db_regional->insert_id();
        
            
        } else{
            $errors = array();

            if($exist_name != 0){
               array_push($errors,array('field'=>'name','msg'=>'Kode sudah ada yang pakai dong'));
            }

            $result = array ("total" => -1,"errors" => $errors, "records" => 0);
            $this->sys_ok($result);
         } 
            
        
            
    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   
   public function editrujukantype()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
        $id = $prm['id'];
        $code = $prm['code'];
		$name = $prm['name'];
        $userid = $this->sys_user["M_UserID"];
        
    $query = "SELECT COUNT(*) as exist FROM nat_price_rujukantype WHERE Nat_PriceRujukanTypeIsActive = 'Y' AND Nat_PriceRujukanTypeCode = '{$code}' AND Nat_PriceRujukanTypeID <> '{$id}'";
        $exist_name =  $this->db_regional->query($query)->row()->exist;
        
if($exist_name == 0){
            $sqltest = "update nat_price_rujukantype SET 
            Nat_PriceRujukanTypeCode = ?,
            Nat_PriceRujukanTypeName = ?
                where
                Nat_PriceRujukanTypeID = ?
                ";
            $querytest = $this->db_regional->query($sqltest,
                array(
                    $name,
                  $id
                )
            );
          //  echo $query;
            if (!$querytest) {
                $this->sys_error_db("nat_price_rujukantype update");
                exit;
            } 
            $result = array ("total" => 1, "records" => array("xid" => $id));
            $this->sys_ok($result);  
        }else{
            $errors = array();
         
         
            if($exist_name != 0){
               array_push($errors,array('field'=>'name','msg'=>'Kode sudah ada yang pakai dong'));
            }
         
            $result = array ("total" => -1,"errors" => $errors, "records" => 0);
            $this->sys_ok($result);
         }
           
    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   public function addnewpricerujukan()
   {
      try {
         //# cek token valid
         if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
         }

         //# ambil parameter input
         $prm = $this->sys_input;
         $rujukantypeid = $prm['rujukantypeid'];
         $regionalid = $prm['regionalid'];
         $testid = $prm['testid'];
         $price = $prm['price'];
         $userid = $this->sys_user["M_UserID"];

         if($testid == 0){
            $errors = array(); 
            if($testid == 0){
            array_push($errors,array('field'=>'testid','msg'=>'Pemeriksaan dipilih dulu dong'));  
            }           
            $result = array ("total" => -1,"errors" => $errors, "records" => 0);
            $this->sys_ok($result);
         }else{           
            if($prm['xid'] == 0){
                    $sql = "insert into nat_price_rujukan(
                        Nat_PriceRujukanNat_PriceRujukanTypeCode,
                        Nat_PriceRujukanS_RegionalID,
                        Nat_PriceRujukanT_TestID,
                        Nat_PriceRujukanAmount,
                        Nat_PriceRujukanM_UserID)
                     values(?,?,?,?,?)";
        $query = $this->db_regional->query($sql,
        array(
            $rujukantypeid,
            $regionalid,
            $testid,
            $price,
            $userid
        )
        );
        if (!$query) {
        $this->sys_error_db("nat_price_rujukan insert",$this->db_regional);
        exit;
        }
        $last_id = $this->db_regional->insert_id();
        if ($isdefault == "Y" ) {
            $sql = "update nat_price_rujukan set Nat_PriceRujukanIsDefault= 'N'
               where Nat_PriceRujukanID <> ? ";
            $this->db_regional->query($sql,array($last_id));
         }
        $result = array ("total" => 1, "records" => array("xid" => 0));
        $this->sys_ok($result);
                         
                            
          }else{
                $sql = "UPDATE nat_price_rujukan SET Nat_PriceRujukanAmount = '{$price}',
               Nat_PriceRujukanS_RegionalID = '{$regionalid}',
               Nat_PriceRujukanT_TestID = '{$testid}',	
                        Nat_PriceRujukanM_UserID = '{$userid}'
                WHERE Nat_PriceRujukanID = '{$prm['xid']}'";
               //echo $sql;
              $query = $this->db_regional->query($sql);
              $result = array ("total" => 1, "records" => array("xid" => 0));
              $this->sys_ok($result);
            
                 
             
          }
         }
         

      } catch(Exception $exc) {
         $message = $exc->getMessage();
         $this->sys_error($message);
      }
   }
   
   public function deleterujukantype()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
        $userid = $this->sys_user["M_UserID"];
        $sql = "update nat_price_rujukantype SET 
        Nat_PriceRujukanTypeIsActive = 'N'
				WHERE
				Nat_PriceRujukanTypeID = ?
            
				";

            $query = $this->db_regional->query($sql,
                array(
                    $prm['id']
                )
            );
           // echo $query;
            if (!$query) {
                $this->sys_error_db("nat_price_rujukantype delete");
                exit;
            } 
            $sql = "update nat_price_rujukan SET 
            Nat_PriceRujukanIsActive = 'N'
                    WHERE
                    Nat_PriceRujukanNat_PriceRujukanTypeCode = ?
                
                    ";
    
                $query = $this->db_regional->query($sql,
                    array(
                        $prm['id']
                    )
                );
               // echo $query;
                if (!$query) {
                    $this->sys_error_db("nat_price_rujukan delete");
                    exit;
                } 
                
                    
        $result = array ("total" => 1, "records" => array("xid" => 0));
        $this->sys_ok($result);

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
   public function deletepricerujukan()
   {
    try {
        //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }

        //# ambil parameter input
        $prm = $this->sys_input;
        $userid = $this->sys_user["M_UserID"];
            $sql = "update nat_price_rujukan SET 
            Nat_PriceRujukanIsActive = 'N'
                    WHERE
                    Nat_PriceRujukanID = ?
                
                    ";
    
                $query = $this->db_regional->query($sql,
                    array(
                        $prm['id']
                    )
                );
               // echo $query;
                if (!$query) {
                    $this->sys_error_db("nat_price_rujukan delete");
                    exit;
                } 

               
        $result = array ("total" => 1, "records" => array("xid" => 0));
        $this->sys_ok($result);

    } catch(Exception $exc) {
        $message = $exc->getMessage();
        $this->sys_error($message);
    }
   }
  
    function searchrujukantype(){
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }
        $prm = $this->sys_input;
    
        $max_rst = 12;
        $tot_count =0;
    
        $q = [
            'search'     => '%'
        ];
    
        if ($prm['search'] != '')
        {
            $q['search'] = "%{$prm['search']}%";  
        }
    
          // QUERY TOTAL
        $sql = "SELECT count(*) as total
                FROM nat_price_rujukantype
                WHERE 
                Nat_PriceRujukanTypeName like ?
                AND Nat_PriceRujukanTypeIsActive = 'Y'";
        $query = $this->db_regional->query($sql,$q['search']);
        //echo $query;
        if ($query) { 
            $tot_count = $query->result_array()[0]["total"]; 
        } 
        else {
            $this->sys_error_db("nat_price_rujukantype count",$this->db_regional);
            exit;
        }
    
        $sql = "
        SELECT Nat_PriceRujukanTypeID, Nat_PriceRujukanTypeName
        FROM nat_price_rujukantype
            WHERE 
            Nat_PriceRujukanTypeName like ?
            AND Nat_PriceRujukanTypeIsActive = 'Y'
            ORDER BY Nat_PriceRujukanTypeName ASC
          ";
          $query = $this->db_regional->query($sql, array($q['search']));
            
          if ($query) { 
             $rows = $query->result_array();
             //echo $this->db_regional->last_query();
             $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
             $this->sys_ok($result);
          } 
          else {
             $this->sys_error_db("nat_price_rujukantype rows",$this->db_regional);
             exit;
          }
    }
    function searchrujukantypebyname(){
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }
        $prm = $this->sys_input;
    
        $max_rst = 12;
        $tot_count =0;
    
        $q = [
            'search'     => '%'
        ];
    
        if ($prm['search'] != '')
        {
            $q['search'] = "%{$prm['search']}%";  
        }
    
          // QUERY TOTAL
        $sql = "SELECT count(*) as total
                FROM nat_price_rujukantype
                WHERE 
                Nat_PriceRujukanTypeName like ?
                AND Nat_PriceRujukanTypeIsActive = 'Y'";
        $query = $this->db_regional->query($sql,$q['search']);
        //echo $query;
        if ($query) { 
            $tot_count = $query->result_array()[0]["total"]; 
        } 
        else {
            $this->sys_error_db("nat_price_rujukantype count",$this->db_regional);
            exit;
        }
    
        $sql = "
        SELECT Nat_PriceRujukanTypeCode, Nat_PriceRujukanTypeName
        FROM nat_price_rujukantype
            WHERE 
            Nat_PriceRujukanTypeName like ?
            AND Nat_PriceRujukanTypeIsActive = 'Y'
            ORDER BY Nat_PriceRujukanTypeName ASC
          ";
          $query = $this->db_regional->query($sql, array($q['search']));
            
          if ($query) { 
             $rows = $query->result_array();
             //echo $this->db_regional->last_query();
             $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
             $this->sys_ok($result);
          } 
          else {
             $this->sys_error_db("nat_price_rujukantype rows",$this->db_regional);
             exit;
          }
    }
    
    
    function searchregional(){
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }
        $prm = $this->sys_input;
    
        $max_rst = 12;
        $tot_count =0;
    
        $q = [
            'search'     => '%'
        ];
    
        if ($prm['search'] != '')
        {
            $q['search'] = "%{$prm['search']}%";  
        }
    
          // QUERY TOTAL
        $sql = "SELECT count(*) as total
                FROM s_regional
                WHERE 
                S_RegionalName like ?
                AND S_RegionalIsActive = 'Y'";
        $query = $this->db_regional->query($sql,$q['search']);
        //echo $query;
        if ($query) { 
            $tot_count = $query->result_array()[0]["total"]; 
        } 
        else {
            $this->sys_error_db("s_regional count",$this->db_regional);
            exit;
        }
    
        $sql = "
            SELECT *
            FROM s_regional
            WHERE 
            S_RegionalName like ?
            AND S_RegionalIsActive = 'Y'
            ORDER BY S_RegionalName ASC
          ";
          $query = $this->db_regional->query($sql, array($q['search']));
            
          if ($query) { 
             $rows = $query->result_array();
             //echo $this->db_regional->last_query();
             $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
             $this->sys_ok($result);
          } 
          else {
             $this->sys_error_db("s_regional rows",$this->db_regional);
             exit;
          }
    }

function searchdistrict(){
    if (! $this->isLogin) {
    $this->sys_error("Invalid Token");
    exit;
    }
    $prm = $this->sys_input;
    $id = $prm['id'];

    $max_rst = 12;
    $tot_count =0;

    $q = [
        'search'     => '%'
    ];

    if ($prm['search'] != '')
    {
        $q['search'] = "%{$prm['search']}%";  
    }

      // QUERY TOTAL
    $sql = "SELECT count(*) as total
            FROM m_district
            WHERE 
            M_DistrictName like ?
            AND M_DistrictS_RegionalID = '{$id}'
            AND M_DistrictIsActive = 'Y'";
    $query = $this->db_regional->query($sql,$q['search']);
    //echo $query;
    if ($query) { 
        $tot_count = $query->result_array()[0]["total"]; 
    } 
    else {
        $this->sys_error_db("m_district count",$this->db_regional);
        exit;
    }

    $sql = "
        SELECT *
        FROM m_district
        WHERE 
        M_DistrictName like ?
        AND M_DistrictS_RegionalID = '{$id}'
        AND M_DistrictIsActive = 'Y'
        ORDER BY M_DistrictName ASC
      ";
      $query = $this->db_regional->query($sql, array($q['search']));
        
      if ($query) { 
         $rows = $query->result_array();
         //echo $this->db_regional->last_query();
         $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
         $this->sys_ok($result);
      } 
      else {
         $this->sys_error_db("m_district rows",$this->db_regional);
         exit;
      }
}
function searchkelurahan(){
    if (! $this->isLogin) {
    $this->sys_error("Invalid Token");
    exit;
    }
    $prm = $this->sys_input;
    $id = $prm['id'];

    $max_rst = 12;
    $tot_count =0;

    $q = [
        'search'     => '%'
    ];

    if ($prm['search'] != '')
    {
        $q['search'] = "%{$prm['search']}%";  
    }

      // QUERY TOTAL
    $sql = "SELECT count(*) as total
            FROM m_kelurahan
            WHERE 
            M_KelurahanName like ?
            AND M_KelurahanM_DistrictID = '{$id}'
            AND M_KelurahanIsActive = 'Y'";
    $query = $this->db_regional->query($sql,$q['search']);
    //echo $query;
    if ($query) { 
        $tot_count = $query->result_array()[0]["total"]; 
    } 
    else {
        $this->sys_error_db("m_district count",$this->db_regional);
        exit;
    }

    $sql = "
        SELECT *
        FROM m_kelurahan
        WHERE 
        M_KelurahanName like ?
        AND M_KelurahanM_DistrictID = '{$id}'
        AND M_KelurahanIsActive = 'Y'
        ORDER BY M_KelurahanName ASC
      ";
      $query = $this->db_regional->query($sql, array($q['search']));
        
      if ($query) { 
         $rows = $query->result_array();
         //echo $this->db_regional->last_query();
         $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
         $this->sys_ok($result);
      } 
      else {
         $this->sys_error_db("m_district rows",$this->db_regional);
         exit;
      }
}
function searchtest(){
    if (! $this->isLogin) {
    $this->sys_error("Invalid Token");
    exit;
    }
    $prm = $this->sys_input;

    $max_rst = 12;
    $tot_count =0;

    $q = [
        'search'     => '%'
    ];

    if ($prm['search'] != '')
    {
        $q['search'] = "%{$prm['search']}%";  
    }

      // QUERY TOTAL
    $sql = "SELECT count(*) as total
            FROM t_test
            WHERE 
            T_TestName like ?
            AND T_TestIsActive = 'Y'";
    $query = $this->db_regional->query($sql,$q['search']);
    //echo $query;
    if ($query) { 
        $tot_count = $query->result_array()[0]["total"]; 
    } 
    else {
        $this->sys_error_db("t_test count",$this->db_regional);
        exit;
    }

    $sql = "
        SELECT T_TestID, T_TestCode, T_TestSasCode, 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_TestName like ?
        AND T_TestIsActive = 'Y'
        AND T_TestIsPrice = 'Y'
        ORDER BY T_TestName ASC
      ";
      $query = $this->db_regional->query($sql, array($q['search']));
        
      if ($query) { 
         $rows = $query->result_array();
         //echo $this->db_regional->last_query();
         $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
         $this->sys_ok($result);
      } 
      else {
         $this->sys_error_db("t_test rows",$this->db_regional);
         exit;
      }
}
function searchmou(){
    if (! $this->isLogin) {
    $this->sys_error("Invalid Token");
    exit;
    }
    $prm = $this->sys_input;
    $id = $prm['id'];

    $max_rst = 12;
    $tot_count =0;

    $q = [
        'search'     => '%'
    ];

    if ($prm['search'] != '')
    {
        $q['search'] = "%{$prm['search']}%";  
    }

      // QUERY TOTAL
    $sql = "SELECT count(*) as total
            FROM m_mou
            WHERE 
            M_MouName like ?
            AND M_MouT_TestID = '{$id}'
            AND M_MouIsActive = 'Y'";
    $query = $this->db_regional->query($sql,$q['search']);
    //echo $query;
    if ($query) { 
        $tot_count = $query->result_array()[0]["total"]; 
    } 
    else {
        $this->sys_error_db("m_mou count",$this->db_regional);
        exit;
    }

    $sql = "SELECT *
        FROM m_mou
        WHERE 
        M_MouName like ?
        AND M_MouT_TestID = '{$id}'
        AND M_MouIsActive = 'Y'
        ORDER BY M_MouName ASC
      ";
      $query = $this->db_regional->query($sql, array($q['search']));
        
      if ($query) { 
         $rows = $query->result_array();
       //  echo $this->db_regional->last_query();
         $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
         $this->sys_ok($result);
      } 
      else {
         $this->sys_error_db("m_mou rows",$this->db_regional);
         exit;
      }
}  
function searchdoctor(){
    if (! $this->isLogin) {
    $this->sys_error("Invalid Token");
    exit;
    }
    $prm = $this->sys_input;

    $max_rst = 12;
    $tot_count =0;

    $q = [
        'search'     => '%'
    ];

    if ($prm['search'] != '')
    {
        $q['search'] = "%{$prm['search']}%";  
    }

      // QUERY TOTAL
    $sql = "SELECT count(*) as total
            FROM(SELECT M_DoctorID, CONCAT(M_DoctorPrefix, ' ',M_DoctorName) as M_DoctorName
            FROM m_doctor
            WHERE M_DoctorIsActive = 'Y') a
                WHERE 
                M_DoctorName like ?";
    $query = $this->db_regional->query($sql,$q['search']);
    //echo $query;
    if ($query) { 
        $tot_count = $query->result_array()[0]["total"]; 
    } 
    else {
        $this->sys_error_db("m_doctor count",$this->db_regional);
        exit;
    }

    $sql = "SELECT * FROM(SELECT M_DoctorID, CONCAT(M_DoctorPrefix, ' ',M_DoctorName) as M_DoctorName
    FROM m_doctor
    WHERE M_DoctorIsActive = 'Y') a
        WHERE 
        M_DoctorName like ?
        ORDER BY M_DoctorName ASC
      ";
      $query = $this->db_regional->query($sql, array($q['search']));
        
      if ($query) { 
         $rows = $query->result_array();
         //echo $this->db_regional->last_query();
         $result = array("total" => $tot_count, "records" => $rows, "total_display" => sizeof($rows));
         $this->sys_ok($result);
      } 
      else {
         $this->sys_error_db("m_doctor rows",$this->db_regional);
         exit;
      }
}
function selectaddressdoctor(){
    
        try {
            //# cek token valid
            if (! $this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;
            $rows = [];
            $query ="SELECT M_DoctorAddressID,
            CONCAT(M_DoctorAddressNote, ': ',M_DoctorAddressDescription) as M_DoctorAddressNote
            FROM
            m_doctoraddress
            WHERE M_DoctorAddressIsActive = 'Y' AND M_DoctorAddressM_DoctorID = '{$id}'";
                    //echo $query;
            $rows['addressdoctors'] = $this->db_regional->query($query)->result_array();
            
        
            $result = array(
                "total" => count($rows) , 
                "records" => $rows, 
            ); 
            $this->sys_ok($result);
    
    
        } catch(Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
        
    }
}