<?php
class Test extends CI_Controller
{
   public function __construct()
   {
      parent::__construct();
      $this->db_onedev = $this->load->database("onedev", true);
   }

   function index() {
      $this->load->library("Resultcalc");
     
      $id = 946;
      $rows = $this->resultcalc->auto($id);
      echo "<pre> $rows";
   }

    function add_template_haji_order(){
        $sql = "SELECT T_OrderHeaderID, So_ResultEntryID
            FROM t_orderheader
            JOIN mgm_mcu ON T_OrderHeaderMgm_McuID = Mgm_McuID 
            JOIN mgm_mcutemplate ON Mgm_McuTemplateMgm_McuID = Mgm_McuID AND 
            Mgm_McuTemplateFisikTemplateMappingID = 9
            AND T_OrderHeaderMgm_McuID <> 56
            JOIN so_resultentry ON So_ResultEntryT_OrderHeaderID = T_OrderHeaderID AND
            So_ResultEntryNonlab_TemplateID = 27
            ";
        $dt = $this->db_onedev->query($sql)->result_array();
        foreach ($dt as $key => $value) {
            $sql = "SELECT FisikTemplateTitle as template_name,
                    FisikTemplateMappingDetailFisikTemplateID as template_id,
                    FisikTemplateJSON as details
                    FROM fisik_template_mapping_detail
                    JOIN fisik_template ON FisikTemplateMappingDetailFisikTemplateID = FisikTemplateID
                    WHERE
                    FisikTemplateMappingDetailFisikTemplateMappingID = 9 AND 
                    FisikTemplateMappingDetailIsActive = 'Y' AND FisikTemplateIsActive = 'Y'";
            $dt_template = $this->db_onedev->query($sql)->result_array();
            //$exist_dt = 0;
            foreach ($dt_template as $k => $v) {
                $sql = "SELECT *
                        FROM so_resultentry_fisik_umum
                        WHERE 
                            So_ResultEntryFisikUmumSo_ResultEntryID = {$value['So_ResultEntryID']} AND
                            So_ResultEntryFisikUmumFisikTemplateID = {$v['template_id']} AND So_ResultEntryFisikUmumIsActive = 'Y'";
                $chk_exist = $this->db_onedev->query($sql)->result_array();
                //echo count($chk_exist) ;

                if(count($chk_exist) == 0){
                    //$exist_dt += 1;
                    //echo $v['template_name']."^".count($chk_exist).'|';
                    $sql = "INSERT INTO so_resultentry_fisik_umum (
                                So_ResultEntryFisikUmumSo_ResultEntryID,
                                So_ResultEntryFisikUmumFisikTemplateID,
                                So_ResultEntryFisikUmumDetails,
                                So_ResultEntryFisikUmumCreated,
                                So_ResultEntryFisikUmumCreatedUserID
                            )
                            VALUES(
                                {$value['So_ResultEntryID']},
                                {$v['template_id']},
                                '{$v['details']}',
                                NOW(),
                                575
                            )";
                            //if( $exist_dt  ==1)
                            //echo $sql.';';
                    $ins = $this->db_onedev->query($sql); 
                    
                 
                }
            }
        }
    }

   function generatessprice($mgmmcuid){
      $this->load->library('SsPriceMou');
      $this->sspricemou->create($mgmmcuid);
      echo "selesai";
   }

   function genqrcode($nomorlab,$id){
      $this->load->library('ciqrcode'); //pemanggilan library QR CODE
      $home_dir = "/home/one/project/one/";
      $target_dir = $home_dir . "one-media/one-qrcontrolcard/";
      $config['cacheable']    = false; //boolean, the default is true
      //$config['cachedir']             = './assets/'; //string, the default is application/cache/
      //$config['errorlog']             = './assets/'; //string, the default is application/logs/
      $config['imagedir']             = $target_dir;  //direktori penyimpanan qr code
      $config['quality']              = true; //boolean, the default is true
      $config['size']                 = '1024'; //interger, the default is 1024
      $config['black']                = array(224,255,255); // array, default is array(255,255,255)
      $config['white']                = array(70,130,180); // array, default is array(0,0,0)
      $this->ciqrcode->initialize($config);

      $image_name="qrcode_".$nomorlab.".png"; //buat name dari qr code sesuai dengan nim

      $params['data'] = "http://devcpone.aplikasi.web.id/one-ui/test/vuex/cpone-control-card/?noreg=".$nomorlab."&id=".$id; //data yang akan di jadikan QR CODE
      $params['level'] = 'H'; //H=High
      $params['size'] = 10;
      $params['savename'] = $config['imagedir'].$image_name; //simpan image QR CODE ke folder assets/images/
      $this->ciqrcode->generate($params); // fungsi untuk generate QR CODE
      echo "http://devcpone.aplikasi.web.id/one-api/assets/images/".$image_name;

      
   }

    function inject_price(){
        $sql = "SELECT *
                FROM inject_price
                JOIN t_test ON CODE_TEST_CP_ONE = T_TestSasCode AND T_TestIsActive = 'Y' 
                WHERE
                    DONE = 'N' AND ID_COMPANY = '00000422' AND PRICE_ID = '7370'
                GROUP BY CODE_TEST_CP_ONE
                ";
        $query = $this->db_onedev->query($sql);
        //echo $this->db_onedev->last_query();
        $datas = $query->result_array();
        foreach ($datas as $key => $value) {
            $sql = "SELECT *
                    FROM t_price
                    WHERE
                        T_PriceT_PriceHeaderID = 1 AND 
                        T_PriceIsActive = 'Y' AND
                        T_PriceT_TestID = {$value['T_TestID']}
                    LIMIT 1
                    ";
            $query = $this->db_onedev->query($sql);
           
            $price_exist =  $query->result_array();
            echo count($price_exist);
            if(count($price_exist) == 0){
                $sql = "INSERT INTO t_price(
                            T_PriceT_PriceHeaderID,
                            T_PriceT_TestID,
                            T_PriceAmount,
                            T_PriceDiscRp,
                            T_PriceSubTotal,
                            T_PriceTotal,
                            T_PriceCreated,
                            T_PriceUserID
                        )
                        VALUES(
                            ?,?,?,?,?,?,NOW(),555
                        )";
                $query = $this->db_onedev->query($sql,array(
                    1,
                    $value['T_TestID'],
                    $value['PRICE_LIST'],
                    0,
                    $value['PRICE_LIST'],
                    $value['PRICE_LIST']

                ));

                $last_id = $this->db_onedev->insert_id();

                $sql = "SELECT *
                        FROM t_price
                        WHERE
                            T_PriceID = {$last_id}
                        ";
                //echo $sql;
                $query = $this->db_onedev->query($sql);
                $price_exist =  $query->result_array();
            }

            $sql = "SELECT *
                    FROM t_packet
                    WHERE
                        T_PacketOldPriceID = '{$value['PRICE_ID']}' AND 
                        T_PacketIsActive = 'Y'";
                       // echo $sql;
            $query = $this->db_onedev->query($sql);
            $row_packet = $query->result_array();
            if(count($row_packet) == 0){
                $sql_get_numbering = "SELECT fn_numbering_cpone('PCPR') as numberx";
                $qry_get_numbering = $this->db_onedev->query($sql_get_numbering);
                $data_numbering = $qry_get_numbering->result_array();

                $T_PacketSasCode = $data_numbering[0]['numberx'];
                $sql = "INSERT INTO t_packet(
                            T_PacketOldPriceID,
                            T_PacketT_PriceHeaderID,
                            T_PacketType,
                            T_PacketName,
                            T_PacketSasCode,
                            T_PacketStartDate,
                            T_PacketEndDate,
                            T_PacketCreated
                        )
                        VALUES(
                            '{$value['PRICE_ID']}',
                            1,
                            'PR',
                            '{$value['NAMA_PAKET']}',
                            '{$T_PacketSasCode}',
                            '{$value['BEGIN_DATE']}',
                            '{$value['EXPIRY_DATE']}',
                            NOW()
                        )";
                $this->db_onedev->query($sql);
                //echo $sql;
                $last_id = $this->db_onedev->insert_id();
                $sql = "SELECT *
                        FROM t_packet
                        WHERE
                            T_PacketOldPriceID = '{$value['PRICE_ID']}' AND 
                            T_PacketIsActive = 'Y'";
                $query = $this->db_onedev->query($sql);
                //echo $sql;
                $row_packet = $query->result_array();
                //print_r($row_packet);
            }

            $sql = "SELECT * 
                    FROM t_packetdetail 
                    WHERE  
                        T_PacketDetailT_PacketID = {$row_packet[0]['T_PacketID']} AND 
                        T_PacketDetailT_TestID = {$value['T_TestID']} AND 
                        T_PacketDetailIsActive = 'Y'";
            $query = $this->db_onedev->query($sql);
            $exist_packetdetail =   $query->result_array();
            if(count( $exist_packetdetail) == 0){
                $sql = "INSERT INTO t_packetdetail(
                            T_PacketDetailT_PacketID,
                            T_PacketDetailT_TestID,
                            T_PacketDetailOriginalPrice,
                            T_PacketDetailPrice,
                            T_PacketDetailCreated
                        )
                        VALUES(?,?,?,?,NOW())";
                $query = $this->db_onedev->query($sql,array(
                    $row_packet[0]['T_PacketID'],
                    $value['T_TestID'],
                    $value['PRICE_LIST'],
                    $value['NET_PRICE']
                ));

                $sql = "SELECT SUM(T_PacketDetailOriginalPrice) as original_price,
                        SUM(T_PacketDetailPrice) as net_price
                        FROM t_packetdetail
                        WHERE
                            T_PacketDetailIsActive = 'Y' AND 
                            T_PacketDetailT_PacketID = {$row_packet[0]['T_PacketID']}";
                $query = $this->db_onedev->query($sql);
                $packet_now = $query->result_array();

                $sql = "UPDATE t_packet SET T_PacketOriginalPrice = '{$packet_now[0]['original_price']}', T_PacketPrice = '{$packet_now[0]['net_price']}'
                        WHERE
                        T_PacketID = {$row_packet[0]['T_PacketID']}";
                $query = $this->db_onedev->query($sql);
            }
            
        }

        $sql = "SELECT *
                FROM inject_price
                JOIN t_test ON CODE_TEST_CP_ONE = T_TestSasCode AND T_TestIsActive = 'Y' 
                WHERE
                    DONE = 'N' AND ID_COMPANY = '00000422' AND PRICE_ID = '7370'
                GROUP BY ID_COMPANY
                ";
        $query = $this->db_onedev->query($sql);
        $all_company = $query->result_array();
        foreach ($all_company as $k => $v) {
            $sql = "SELECT *
                    FROM corporate
                    WHERE
                    CorporateOldCompanyID = '{$v['ID_COMPANY']}' AND CorporateIsActive = 'Y' LIMIT 1";
                    //echo $sql;
            $query = $this->db_onedev->query($sql);
            $row_company = $query->result_array();
            
            if(count($row_company) > 0){
                $sql_get_numbering = "SELECT fn_numbering_cpone('SM') as numberx";
                $qry_get_numbering = $this->db_onedev->query($sql_get_numbering);
                if (!$qry_get_numbering) {
                    $this->db_onedev->trans_rollback();
                    $this->sys_error_db("mgm_mcu numbering cpone", $this->db_onedev);
                    exit;
                }

                $data_numbering = $qry_get_numbering->result_array();

                $Mgm_McuNumber = $data_numbering[0]['numberx'];
                $sql = "INSERT INTO mgm_mcu(
                            Mgm_McuNumber,
                            Mgm_McuT_PriceHeaderID,
                            Mgm_McuM_BranchID,
                            Mgm_McuLabel,
                            Mgm_McuBisaTambahPemeriksaan,
                            Mgm_McuCorporateID,
                            Mgm_McuStartDate,
                            Mgm_McuEndDate,
                            Mgm_McuCreated,
                            Mgm_McuCreatedUserID
                        )
                        VALUES(
                            '{$Mgm_McuNumber}',
                            1,
                            100,
                            '{$v['COMPANY']} (Walk In)',
                            'Y',
                            '{$row_company[0]['CorporateID']}',
                            CURDATE(),
                            '2025-12-31',
                            NOW(),
                            575
                        )";
                //echo $sql;
                $query = $this->db_onedev->query($sql);

                $mgm_mcuID = $this->db_onedev->insert_id();

                $sql = "INSERT INTO mgm_mcutemplate(
                            Mgm_McuTemplateMgm_McuID,
                            Mgm_McuTemplateFisikTemplateMappingID,
                            Mgm_McuTemplateCreated,
                            Mgm_McuTemplateCreatedUserID
                        )
                        VALUES(
                            {$mgm_mcuID},
                            5,
                            NOW(),
                            575
                        )";
                $this->db_onedev->query($sql);

                $sql = "SELECT *
                        FROM inject_price
                        JOIN t_packet ON T_PacketOldPriceID = PRICE_ID
                        WHERE
                            ID_COMPANY =  '{$v['ID_COMPANY']}' 
                            GROUP BY PRICE_ID";
                $query = $this->db_onedev->query($sql);
                //echo $sql;
                $loop_packet = $query->result_array();

                foreach ($loop_packet as $i => $j) {
                    $sql = "INSERT INTO mgm_mcupacket(
                                Mgm_McuPacketMgm_McuID,
                                Mgm_McuPacketT_PacketID,
                                Mgm_McuPacketCreated,
                                Mgm_McuPacketCreatedUserID
                            )
                            VALUES(
                                {$mgm_mcuID},
                                {$j['T_PacketID']},
                                NOW(),
                                555
                            )";
                    $query = $this->db_onedev->query($sql);
                }
            }
            
                
        }
    }

   function push_order($nomorreg){
    $this->load->library('Apilis');
        $kesimpulan = $this->apilis->GetOrderDetail($nomorreg);
        echo json_encode($kesimpulan);
   }

   function gen_kesimpulan_fisik($id){
      $this->load->library('kesimpulanfisik');
      $kesimpulan = $this->kesimpulanfisik->kesimpulan($id);
      echo $kesimpulan;

   }

   function generate_kelainan_fisik_bydate($stardate,$endate){
    $sql = "SELECT *
            FROM so_resultentry
            JOIN t_orderheader ON So_ResultEntryT_OrderHeaderID = T_OrderHeaderID AND T_OrderHeaderIsActive = 'Y' AND 
            ( DATE(T_OrderHeaderDate) BETWEEN ? AND ? ) AND T_OrderHeaderIsActive = 'Y'
            WHERE
            So_ResultEntryNonlab_TemplateID = 27 AND So_ResultEntryIsActive = 'Y'
            ";
    $query = $this->db_onedev->query($sql,array($stardate,$endate));
    //echo $this->db_onedev->last_query();
    $datas = $query->result_array();
    foreach ($datas as $key => $value) {
        $this->gen_kelainan_fisik($value['So_ResultEntryID']);
    }
}

   
    /*function gen_kelainan_fisik_all($mgmmcuid,$limit){
        $this->load->library('kesimpulanfisik');
        $kesimpulan = $this->kesimpulanfisik->generate_all_kelainan_fisik($mgmmcuid,$limit);
        echo json_encode($kesimpulan);
  
     }

   function gen_kelainan_fisik($id){
      $this->load->library('kesimpulanfisik');
      $kesimpulan = $this->kesimpulanfisik->generate_kelainan_fisik($id);
      echo json_encode($kesimpulan);

   }

   function gen_nonlab_template($id){
      $this->load->library('nonlabtemplate');
      $kesimpulan = $this->nonlabtemplate->generate($id);
      print_r($kesimpulan);

   }*/


   function genpatientqrcode($nomorlab){
      $this->load->library('ciqrcode'); //pemanggilan library QR CODE
      $home_dir = "/home/one/project/one/";
      $target_dir = $home_dir . "one-media/one-qrpatient/";
      $config['cacheable']    = false; //boolean, the default is true
      //$config['cachedir']             = './assets/'; //string, the default is application/cache/
      //$config['errorlog']             = './assets/'; //string, the default is application/logs/
      $config['imagedir']             = $target_dir; //direktori penyimpanan qr code
      $config['quality']              = true; //boolean, the default is true
      $config['size']                 = '1024'; //interger, the default is 1024
      $config['black']                = array(224,255,255); // array, default is array(255,255,255)
      $config['white']                = array(70,130,180); // array, default is array(0,0,0)
      $this->ciqrcode->initialize($config);

      $image_name="patient_qr_".$nomorlab.".png"; //buat name dari qr code sesuai dengan nim

      $params['data'] = $nomorlab; //data yang akan di jadikan QR CODE
      $params['level'] = 'H'; //H=High
      $params['size'] = 10;
      $params['savename'] = $config['imagedir'].$image_name; //simpan image QR CODE ke folder assets/images/
      $this->ciqrcode->generate($params); // fungsi untuk generate QR CODE
      echo "https://devcpone.aplikasi.web.id/one-media/one-qrpatient/".$image_name;
      //echo "selesai";

      
   }

   function truncatextable(){
      $this->db = $this->load->database("onedev",true);
      $xgroup= array('PRICE','ORDER');
      $where_in = "";
      foreach ($xgroup as $key => $value) {
         if($where_in != "") $where_in .= " or ";
         $where_in .= "X_TableTransactionGroupName = '".$value."'";
      }
      

      $sql = "SELECT * FROM x_table_transaction 
            WHERE
            X_TableTransactionIsActive = 'Y' AND ( $where_in )";
            //echo $sql;
      $data_tables = $this->db->query($sql)->result_array();
      foreach($data_tables as $k => $v){
         $sql = "TRUNCATE TABLE {$v['X_TableTransactionTableName']}";
         //echo $sql;
         //$this->db->query($sql);
      }
      echo 'selesai';
  }

   function exporttoxls(){
	    $this->db = $this->load->database("onedev",true);
		$sql = "SELECT * FROM xtable";
		$data_asal = $this->db->query($sql)->result_array();
		foreach($data_asal as $k => $v){
			$xplode = explode(' ',$v['TANGGAL_LAHIR']);
			$tgl = sprintf("%02d", $xplode[0]);
			$bln = $this->monthtonumber($xplode[1]);
			$thn = $xplode[2];
			$xdate = $tgl.'-'.$bln.'-'.$thn;
			$sql = "UPDATE xtable SET TANGGAL_LAHIR_INA = '{$xdate}' WHERE id = {$v['id']}";
			 $this->db->query($sql);
		}
		echo 'selesai';
   }
   
   function monthtonumber($xmonth){
	   $rst = 0;
	   if($xmonth == 'Januari')
		   $rst = '01';
	   if($xmonth == 'Februari')
		   $rst = '02';
	   if($xmonth == 'Maret')
		   $rst = '03';
	   if($xmonth == 'April')
		   $rst = '04';
	   if($xmonth == 'Mei')
		   $rst = '05';
	   if($xmonth == 'Juni')
		   $rst = '06';
	   if($xmonth == 'Juli')
		   $rst = '07';
	   if($xmonth == 'Agustus')
		   $rst = '08';
	   if($xmonth == 'September')
		   $rst = '09';
	   if($xmonth == 'Oktober')
		   $rst = '10';
	   if($xmonth == 'November')
		   $rst = '11';
	   if($xmonth == 'Desember')
		   $rst = '12';
	   return $rst;
   }
   
   function two() {
       $this->db = $this->load->database("onedev",true);

      $id = 946;

       $sql = "select 
          T_TestCalculationID,T_TestCalculationFormula,
          T_OrderDetailID, T_OrderDetailT_TestID, T_TestNat_TestID,
          T_TestCalculationID,T_OrderDetailResult, 
          fn_global_age_count_day(M_PatientDOB, date(T_OrderHeaderDate)) / 365 AgeInYear,
          T_TestCalculationNat_SexID
       from t_orderdetail
       join t_orderheader on T_OrderDetailT_OrderHeaderID = T_OrderHeaderID 
            and T_OrderHeaderID = ?
       join m_patient on T_OrderHeaderM_PatientID = M_PatientID 
       join t_test on T_OrderDetailT_TestID = T_TestID 
         and T_OrderDetailIsActive = 'Y' and T_TestIsActive = 'Y'
       join t_testcalculation on T_TestNat_TestID  = T_TestCalculationNat_TestID 
         and T_TestCalculationIsActive = 'Y'
         and (
            T_TestCalculationNat_SexID = M_PatientM_SexID
            or 
            T_TestCalculationNat_SexID = 0
         )
       ";

       $sql_det = "select T_TestCalculationDetailCode, T_OrderDetailResult
          from 
         t_testcalculation_detail td 
         join t_testcalculation on T_TestCalculationID = T_TestCalculationDetailT_TestCalculationID
            and T_TestCalculationID = ?
         join t_test t on td.T_TestCalculationDetailNat_TestID = T_TestNat_TestID 
         left join t_orderdetail on T_TestID = T_OrderDetailT_TestID and T_OrderDetailIsActive = 'Y'
            and T_OrderDetailT_OrderHeaderID = ?
         where T_OrderDetailID is not null";
      

       $qry = $this->db->query($sql, array($id));
       $rows = $qry->result_array();
       if ( count($rows) > 0 ) {
          foreach($rows as $r) {
             if (false && $r["T_OrderDetailResult"] != "") continue;
             $tc_id = $r["T_TestCalculationID"];
             $qry_det = $this->db->query($sql_det, array($tc_id, $id));
             $drows = $qry_det->result_array();
             $formula = $r["T_TestCalculationFormula"];
             $have_all = true;
             $have_one = false;
             $formula = str_replace("AGE",$r["AgeInYear"], $formula);
             foreach($drows as $dr) {
                if($dr["T_OrderDetailResult"] == "" ) {
                   $have_all = false;
                   break;
                }
                $have_one = true;
                $code = $dr["T_TestCalculationDetailCode"];
                $value = $dr["T_OrderDetailResult"];
                $formula = str_replace($code, $value,$formula);
             }
             if ($have_all && $have_one) {
                echo "Formula : $formula <br/>";
                eval("\$f_value = $formula;");
                $od_id = $r["T_OrderDetailID"]; 
                $sql = "update t_orderdetail set T_OrderDetailResult = ?
                where T_OrderDetailID = ?";
                $this->db->query($sql, array($f_value,$od_id));
             } 
         } 
       }
   }

   
   function dynamic_comparison($varleft, $op, $varright)
   {

       switch ($op) {
           case "=":
               return $varleft == $varright;
           case "!=":
               return $varleft != $varright;
           case ">=":
               return $varleft >= $varright;
           case "<=":
               return $varleft <= $varright;
           case ">":
               return $varleft >  $varright;
           case "<":
               return $varleft <  $varright;
           default:
               return true;
       }
   }


   function usg_abdomen(){
    $rtn = [];
        $this->load->library('nonlabtemplate');
        $sql = "SELECT T_SamplingSoID, T_OrderDetailID, T_OrderDetailT_OrderHeaderID, So_ResultEntryID
                FROM `t_orderdetail`
                JOIN t_samplingso ON T_SamplingSoT_OrderHeaderID = T_OrderDetailT_OrderHeaderID AND
                T_SamplingSoT_TestID = T_OrderDetailT_TestID
                LEFT JOIN so_resultentry ON T_OrderDetailT_OrderHeaderID = So_ResultEntryT_OrderHeaderID AND 
                So_ResultEntryT_OrderDetailID = T_OrderDetailID AND so_resultentryIsActive = 'Y'
                WHERE 
                `T_OrderDetailT_TestID` = '3385' AND So_ResultEntryID IS NULL AND
                T_OrderDetailIsActive = 'Y'";
        $query = $this->db_onedev->query($sql);
        $datas = $query->result_array();
        foreach ($datas as $key => $value) {
            
            $kesimpulan = $this->nonlabtemplate->generate($value['T_SamplingSoID']);
            $rtn[] = $kesimpulan['So_ResultEntryID'];
        }

        echo join(", ",$rtn);
   }


   function updatetotalheader(){
    $rtn = [];

        $sql = "SELECT T_OrderDetailT_OrderHeaderID
                FROM `t_orderdetail`
                JOIN t_orderdetailorder ON T_OrderDetailT_OrderDetailOrderID = T_OrderDetailOrderID AND T_OrderDetailOrderIsActive = 'Y' AND
                `T_OrderDetailIsActive` = 'Y' AND T_OrderDetailIsActive = 'Y' 
                JOIN t_packet ON T_OrderDetailOrderT_PacketID = T_PacketID AND T_PacketSasCode IN ('PN2400170','PN2400169','PN2400168','PN2400167','PN2400166','PN2400165','PN2400164','PN2400163','PN2400162',
                'PN2400161','PN2400160','PN2400159','PN2400158','PN2400157','PN2400156','PN2400155','PN2400154','PN2400175')
                GROUP BY T_OrderDetailT_OrderHeaderID";
        $query = $this->db_onedev->query($sql);
        $datas = $query->result_array();
        foreach ($datas as $key => $value) {
            $sql = "SELECT SUM(T_OrderDetailPrice) as total
                    FROM `t_orderdetail`
                    WHERE `T_OrderDetailIsActive` = 'Y' AND 
                    `T_OrderDetailT_OrderHeaderID` = {$value['T_OrderDetailT_OrderHeaderID']} AND 
                    `T_OrderDetailPrice` > '0'
                ";
            $query = $this->db_onedev->query($sql);
            //echo $this->db_onedev->last_query();
            $total = $query->row()->total;

            $sql = "UPDATE t_orderheader SET T_OrderHeaderSubTotal = {$total}, T_OrderHeaderLastUpdatedUserID = 181024, T_OrderHeaderLastUpdated = NOW()
                    WHERE T_OrderHeaderID = {$value['T_OrderDetailT_OrderHeaderID']}

                ";
            $query = $this->db_onedev->query($sql);
            //echo $this->db_onedev->last_query();

            $rtn[] = $value['T_OrderDetailT_OrderHeaderID'];
        }

        echo join(", ",$rtn);
   }

   function billirubin_total(){
        $rtn = [];
        $tests = [900,901,902];
        foreach ($tests as $val) {
            $sql = "SELECT *
                    FROM (
                    SELECT GROUP_CONCAT(T_OrderDetailT_TestID) as exist, 
                    T_TestID,
                    T_TestCode,
                    T_TestName, 
                    T_TestSasCode,
                    T_TestIsResult,
                    T_TestIsPrice,
                    T_OrderDetailT_OrderHeaderID,
                    T_OrderDetailT_OrderDetailOrderID
                    FROM `t_orderdetail` 
                    JOIN t_test ON T_TestID = ?
                    WHERE
                    T_OrderDetailT_TestID IN (899,900,901,902)  AND T_OrderDetailIsActive = 'Y'
                    GROUP BY T_OrderDetailT_OrderHeaderID
                    ) x
                    WHERE
                    exist = '899'";
            $query = $this->db_onedev->query($sql,[$val]);
            //if($val == 900)
            //echo $this->db_onedev->last_query();
            $datas = $query->result_array();
            foreach ($datas as $key => $value) {
                $sql = "INSERT INTO t_orderdetail(
                            T_OrderDetailT_OrderHeaderID,
                            T_OrderDetailT_OrderDetailOrderID,
                            T_OrderDetailT_TestID,
                            T_OrderDetailT_TestCode,
                            T_OrderDetailT_TestSasCode,
                            T_OrderDetailT_TestName,
                            T_OrderDetailT_TestIsResult,
                            T_OrderDetailT_TestIsPrice,
                            T_OrderDetailCreatedUserID,
                            T_OrderDetailCreated
                        )
                        VALUES(?,?,?,?,?,?,?,?,?,NOW())";
                $query = $this->db_onedev->query($sql,array(
                    $value['T_OrderDetailT_OrderHeaderID'],
                    $value['T_OrderDetailT_OrderDetailOrderID'],
                    $value['T_TestID'],
                    $value['T_TestCode'],
                    $value['T_TestSasCode'],
                    $value['T_TestName'],
                    $value['T_TestIsResult'],
                    $value['T_TestIsPrice'],
                    270924
                ));
                //if($key == 0)
                //echo $this->db_onedev->last_query();

                $rtn[] = $this->db_onedev->insert_id();
            }
        }

        echo json_encode($rtn);
   }


   function tonometri_remove(){
    $rtn = [];
    $sql = "SELECT So_ResultEntryID, So_ResultEntryT_OrderHeaderID
            FROM `t_orderdetail`
            JOIN so_resultentry ON So_ResultEntryT_OrderHeaderID = T_OrderDetailT_OrderHeaderID AND
            So_ResultEntryNonlab_TemplateID = 27
            WHERE `T_OrderDetailT_TestName` LIKE '%tonometri%' AND `T_OrderDetailIsActive` = 'Y'";
    $query = $this->db_onedev->query($sql);

    if (!$query) {
        //$this->db_onedev->trans_rollback();
        echo $this->db_onedev->last_query();
        $this->sys_error_db('error', 'Error get order: ' . $this->db_onedev);
        exit;
    }

    $datas = $query->result_array();
    foreach ($datas as $key => $value) {
        $sql = "SELECT * FROM so_resultentry_fisik_umum WHERE So_ResultEntryFisikUmumSo_ResultEntryID = ?";
        $query = $this->db_onedev->query($sql,[$value['So_ResultEntryID']]);
        $xcount = $query->result_array();
        if(count($xcount) ==  1 && $xcount[0]['So_ResultEntryFisikUmumFisikTemplateID'] == 41){
            $sql = "UPDATE so_resultentry_fisik_umum SET So_ResultEntryFisikUmumIsActive = 'N', So_ResultEntryFisikUmumDeleted = NOW(), So_ResultEntryFisikUmumDeletedUserID = 240924
                    WHERE So_ResultEntryFisikUmumID = ?";
            $query = $this->db_onedev->query($sql,[$xcount[0]['So_ResultEntryFisikUmumID']]);
            $rtn[] = $xcount[0]['So_ResultEntryFisikUmumID'];
        }
    }

    echo join(", ",$rtn);

    
   }

    function generate_kelainan_lab_all($mgmmcuid,$limit){
        $this->load->library('Etlfisik');
        $sql = "SELECT *
                FROM t_orderheader
                WHERE
                T_OrderHeaderMgm_McuID = ? AND T_OrderHeaderIsActive = 'Y' LIMIT ? ";
        $query = $this->db_onedev->query($sql,array($mgmmcuid,intval($limit)));
        //echo $this->db_onedev->last_query();
        $datas = $query->result_array();
        foreach ($datas as $key => $value) {
            //$this->generate_kelainan_lab($value['T_OrderHeaderID']);
            $userID = 1;
            $this->etlfisik->generate_kelainan_lab($value['T_OrderHeaderID'],$userID);
        }
    }

    function generate_all_kelainan_by_order(){

    }

    function fix_panel_satuan_lemak_update(){
        $id_updates = [];
        $id_inserts = [];
        $sql = "SELECT T_OrderHeaderID, T_OrderDetailOrderID
                FROM t_orderheader
                JOIN t_orderdetailorder ON T_OrderDetailOrderT_OrderHeaderID = T_OrderHeaderID AND 
                T_OrderHeaderIsActive = 'Y'
                JOIN t_packet ON T_OrderDetailOrderT_PacketID = T_PacketID AND T_PacketSasCode IN (
                    'PN2400193'
                )
                GROUP BY T_OrderHeaderID
                ";
        $query = $this->db_onedev->query($sql);
        //echo $this->db_onedev->last_query();
        $datas = $query->result_array();
        foreach ($datas as $key => $value) {
            /*" SELECT * 
                FROM `t_orderdetail` WHERE `T_OrderDetailT_OrderHeaderID` = '10880' AND 
                `T_OrderDetailIsActive` = 'Y' AND `T_OrderDetailT_TestSasCode` LIKE '10520200%' "*/

            $sql = "UPDATE t_orderdetail SET T_OrderDetailIsActive = 'N', T_OrderDetailDeleted = NOW(), T_OrderDetailDeletedUserID = 231077
                    WHERE T_OrderDetailT_OrderHeaderID = ? AND `T_OrderDetailT_TestSasCode` LIKE '10520200%' AND T_OrderDetailIsActive = 'Y'";
            $query = $this->db_onedev->query($sql,array($value['T_OrderHeaderID']));
            if (!$query) {
                //$this->db_onedev->trans_rollback();
                echo $this->db_onedev->last_query();
                $this->sys_error_db('error', 'Error update : ' . $this->db_onedev);
                exit;
            }
            $id_updates[] = $value['T_OrderHeaderID'];

            $sql = "SELECT COUNT(*) as x FROM t_orderdetail WHERE T_OrderDetailT_OrderHeaderID = ? AND T_OrderDetailT_TestSasCode = '10520800' AND 
                    T_OrderDetailIsActive = 'Y'";
            $query = $this->db_onedev->query($sql,array($value['T_OrderHeaderID']));
            $xcount = $query->row()->x;

            if($xcount == 0){
                $sql = "INSERT INTO t_orderdetail (
                    T_OrderDetailT_OrderHeaderID,
                    T_OrderDetailT_OrderDetailOrderID,
                    T_OrderDetailT_TestID,
                    T_OrderDetailT_TestCode,
                    T_OrderDetailT_TestSasCode,
                    T_OrderDetailT_TestName,
                    T_OrderDetailT_TestIsResult,
                    T_OrderDetailT_TestIsPrice,
                    T_OrderDetailPrice,
                    T_OrderDetailPriceForDisc,
                    T_OrderDetailTotal,
                    T_OrderDetailCreatedUserID,
                    T_OrderDetailCreated
                )
                VALUES(?,?,?,?,?,?,?,?,?,?,?,6677,NOW())";
                $query = $this->db_onedev->query($sql,array(
                    $value['T_OrderHeaderID'],
                    $value['T_OrderDetailOrderID'],
                    "955",
                    '10520800',
                    '10520800',
                    'Cholesterol/HDL Chol. Ratio',
                    'Y',
                    'Y',
                    0,
                    0,
                    0
                ));
                if (!$query) {
                    //$this->db_onedev->trans_rollback();
                    echo $this->db_onedev->last_query();
                    $this->sys_error_db('error', 'Error insert : ' . $this->db_onedev);
                    exit;
                }
                $id_inserts[] = $this->db_onedev->insert_id();
            }

            
        }
        $rtn = array(
            "id_inserts" => join(", ",$id_inserts),
            "header_id_update" => join(", ",$id_updates)
        );

        echo json_encode($rtn);
    }

    function generate_kelainan_fisik_all($mgmmcuid,$limit){
        $this->load->library('Etlfisik');
        $sql = "SELECT *
                FROM t_orderheader
                JOIN so_resultentry ON So_ResultEntryT_OrderHeaderID = T_OrderHeaderID AND So_ResultEntryNonlab_TemplateID = 27 AND 
                So_ResultEntryIsActive = 'Y'
                WHERE
                T_OrderHeaderMgm_McuID = ? AND T_OrderHeaderIsActive = 'Y' LIMIT ? ";
        $query = $this->db_onedev->query($sql,array($mgmmcuid,intval($limit)));
        //echo $this->db_onedev->last_query();
        $datas = $query->result_array();
        foreach ($datas as $key => $value) {
            $userID = 1;
            $this->generate_etl_fisik($value['T_OrderHeaderID']);
            $this->generate_summaries($value['T_OrderHeaderID'], $value['So_ResultEntryID'], $userID);
        }
    }

    function get_kelainan_order($orderID,$userID){
        $this->load->library('Etlfisik');
        $rtn = $this->etlfisik->generate_kelainan_by_order($orderID,$userID);
        echo json_encode($rtn);
    }

    function generate_kelainan_non_lab_all($mgmmcuid,$limit){
        $this->load->library('Etlfisik');
        $sql = "SELECT *
                FROM t_orderheader
                WHERE
                T_OrderHeaderMgm_McuID = ? AND T_OrderHeaderIsActive = 'Y' LIMIT ? ";
        $query = $this->db_onedev->query($sql,array($mgmmcuid,intval($limit)));
        //echo $this->db_onedev->last_query();
        $datas = $query->result_array();
        foreach ($datas as $key => $value) {
            //$this->generate_kelainan_lab($value['T_OrderHeaderID']);
            $userID = 1;
            $this->etlfisik->generate_kelainan_nonlab($value['T_OrderHeaderID'],$userID);
        }
    }

    function inject_fix_gol_darah(){
        $sql = "SELECT *
                FROM t_orderdetail
                WHERE
                T_OrderDetailT_TestID = 292 AND T_OrderDetailIsActive = 'N'; ";
        $query = $this->db_onedev->query($sql,array($mgmmcuid,intval($limit)));
        //echo $this->db_onedev->last_query();
        $datas = $query->result_array();
        foreach ($datas as $key => $value) {
            $sql = "INSERT INTO t_orderdetail (
                    T_OrderDetailT_OrderHeaderID,
                    T_OrderDetailT_OrderDetailOrderID,
                    T_OrderDetailT_TestID,
                    T_OrderDetailT_TestCode,
                    T_OrderDetailT_TestSasCode,
                    T_OrderDetailT_TestName,
                    T_OrderDetailPrice,
                    T_OrderDetailPriceForDisc,
                    T_OrderDetailTotal,
                    T_OrderDetailCreatedUserID,
                    T_OrderDetailCreated
                )
                VALUES(?,?,?,?,?,?,?,?,?,555,NOW())";
            $query = $this->db_onedev->query($sql,array(
                $value['T_OrderDetailT_OrderHeaderID'],
                $value['T_OrderDetailT_OrderDetailOrderID'],
                "288",
                '10130100',
                '10130100',
                'Golongan Darah',
                35000,
                35000,
                35000
            ));
            $sql = "INSERT INTO t_orderdetail (
                T_OrderDetailT_OrderHeaderID,
                T_OrderDetailT_OrderDetailOrderID,
                T_OrderDetailT_TestID,
                T_OrderDetailT_TestCode,
                T_OrderDetailT_TestSasCode,
                T_OrderDetailT_TestName,
                T_OrderDetailPrice,
                T_OrderDetailPriceForDisc,
                T_OrderDetailTotal,
                T_OrderDetailCreatedUserID,
                T_OrderDetailCreated
            )
            VALUES(?,?,?,?,?,?,?,?,?,555,NOW())";
            $query = $this->db_onedev->query($sql,array(
                $value['T_OrderDetailT_OrderHeaderID'],
                $value['T_OrderDetailT_OrderDetailOrderID'],
                "289",
                '10130200',
                '1013010001',
                'Golongan Darah ABO System',
                0,
                0,
                0
            ));

            if (!$query) {
                //$this->db_onedev->trans_rollback();
                echo $this->db_onedev->last_query();
                $this->sys_error_db('error', 'Error update : ' . $this->db_onedev);
                exit;
            }

            $sql = "INSERT INTO t_orderdetail (
                T_OrderDetailT_OrderHeaderID,
                T_OrderDetailT_OrderDetailOrderID,
                T_OrderDetailT_TestID,
                T_OrderDetailT_TestCode,
                T_OrderDetailT_TestSasCode,
                T_OrderDetailT_TestName,
                T_OrderDetailPrice,
                T_OrderDetailPriceForDisc,
                T_OrderDetailTotal,
                T_OrderDetailCreatedUserID,
                T_OrderDetailCreated
            )
            VALUES(?,?,?,?,?,?,?,?,?,555,NOW())";
            $query = $this->db_onedev->query($sql,array(
                $value['T_OrderDetailT_OrderHeaderID'],
                $value['T_OrderDetailT_OrderDetailOrderID'],
                "290",
                '10130300',
                '1013010002',
                'Golongan Darah Rhesus',
                0,
                0,
                0
            ));

            if (!$query) {
                //$this->db_onedev->trans_rollback();
                echo $this->db_onedev->last_query();
                $this->sys_error_db('error', 'Error insert : ' . $this->db_onedev);
                exit;
            }
        }
    }

    function generate_etl_fisik($orderID){
        $this->load->library('Etlfisik');
        $userID = 1;
        $this->etlfisik->generate_all_fisik($orderID,$userID);
    }

    function generate_summaries($orderID,$soReID,$userID,$debugID=0){
        $this->load->library('Etlfisik');
        $userID = 1;
        $this->etlfisik->generate_summaries($orderID,$soReID,$userID,$debugID=0);
    }

    function generate_summaries_lab($orderID){
        $this->load->library('Etlfisik');
        $userID = 1;
        $this->etlfisik->generate_kelainan_lab($orderID,$userID);
    }


    function replace_packet($labnumber,$oldpacket_code,$newpacket_code, $userid){
        

        $sql = " SELECT *
                 FROM t_packet 
                 JOIN mgm_mcupacket ON Mgm_McuPacketT_PacketID = T_PacketID AND Mgm_McuPacketIsActive = 'Y'
                 JOIN t_orderheader ON T_OrderHeaderMgm_McuID = Mgm_McuPacketMgm_McuID AND T_OrderHeaderLabNumber = ?
                 WHERE
                 T_PacketSasCode = ? AND T_PacketIsActive = 'Y' LIMIT 1";
        $query = $this->db_onedev->query($sql,array($labnumber,$newpacket_code));

        if (!$query) {
            //$this->db_onedev->trans_rollback();
            echo $this->db_onedev->last_query();
            $this->sys_error_db('error', 'Error get order: ' . $this->db_onedev);
            exit;
        }

        $data_new_packet = $query->row_array();

        if($data_new_packet){
            $sql = " SELECT *
                    FROM t_orderheader
                    JOIN t_orderdetailorder ON T_OrderDetailOrderT_OrderHeaderID = T_OrderHeaderID AND T_OrderDetailOrderIsActive = 'Y' AND
                    T_OrderDetailOrderIsPacket = 'Y'
                    JOIN t_packet ON T_PacketSasCode = ? AND T_OrderDetailOrderT_PacketID = T_PacketID
                    WHERE
                    T_OrderHeaderLabNumber = ? LIMIT 1";
            $query = $this->db_onedev->query($sql,array($oldpacket_code,$labnumber));
            //echo $this->db_onedev->last_query();
            if (!$query) {
                //$this->db_onedev->trans_rollback();
                echo $this->db_onedev->last_query();
                $this->sys_error_db('error', 'Error get order: ' . $this->db_onedev);
                exit;
            }
            //echo $this->db_onedev->last_query();

            $order = $query->row_array();

            if($order){
                $this->db_onedev->trans_begin();

                $new_packet_id = $data_new_packet['T_PacketID'];
                $old_packet_id = $order['T_PacketID'];

                //INSERT NEW PACKET INTO ORDER DETAIL ORDER
                $sql = "INSERT INTO t_orderdetailorder (
                            T_OrderDetailOrderT_OrderHeaderID,
                            T_OrderDetailOrderIsPacket,
                            T_OrderDetailOrderPacketType,
                            T_OrderDetailOrderT_PacketID,
                            T_OrderDetailOrderT_PacketName,
                            T_OrderDetailOrderCreated,
                            T_OrderDetailOrderCreatedUserID
                        )
                        VALUES(?,'Y',?,?,?,NOW(),?)";

                $query = $this->db_onedev->query($sql,array(
                    $order['T_OrderHeaderID'],
                    $data_new_packet['T_PacketType'],
                    $data_new_packet['T_PacketID'],
                    $data_new_packet['T_PacketName'],
                    $userid
                ));

                if (!$query) {
                    $this->db_onedev->trans_rollback();
                    echo $this->db_onedev->last_query();
                    $this->sys_error_db('error', 'Error insert new packet into orderdetail order: ' . $this->db_onedev);
                    exit;
                }

                $new_orderdetail_orderid = $this->db_onedev->insert_id();

                $sql = "SELECT * 
                        FROM t_packetdetail
                        JOIN t_test ON T_PacketDetailT_TestID = T_TestID
                        WHERE
                        T_PacketDetailT_PacketID = ? AND 
                        T_PacketDetailIsActive = 'Y'";
                
                $query = $this->db_onedev->query($sql,array($new_packet_id));
                
                if (!$query) {
                    $this->db_onedev->trans_rollback();
                    echo $this->db_onedev->last_query();
                    $this->sys_error_db('error', 'Error get packet detail: ' . $this->db_onedev);
                    exit;
                }

                $packet_details = $query->result_array();
                foreach ($packet_details as $key => $value) {
                    $data_detail = [];
                    $sql = "SELECT *
                            FROM t_orderdetail
                            WHERE
                            T_OrderDetailT_OrderHeaderID = ? AND 
                            T_OrderDetailT_OrderDetailOrderID = ? AND
                            T_OrderDetailT_TestID = ? AND
                            T_OrderDetailIsActive = 'Y'";
                    $query = $this->db_onedev->query($sql,array($order['T_OrderHeaderID'],$order['T_OrderDetailOrderID'],$value['T_PacketDetailT_TestID']));
                
                    if (!$query) {
                        $this->db_onedev->trans_rollback();
                        echo $this->db_onedev->last_query();
                        $this->sys_error_db('error', 'Error get packet detail: ' . $this->db_onedev);
                        exit;
                    }

                    //echo $this->db_onedev->last_query();

                    $data_detail = $query->result_array();
                    $orderdetail_sascode = $value['T_TestSasCode']."%";
                    //echo "exist-order-lama-".count($data_detail);
                    if(count($data_detail) > 0){
                        // UPDATE order detail lama diganti orderdetailt_orderdetailorderid baru
                        $detail = $data_detail[0];
                        $sql = "UPDATE t_orderdetail SET 
                                    T_OrderDetailT_OrderDetailOrderID = ?, 
                                    T_OrderDetailLastUpdatedUserID = ?
                                WHERE
                                    T_OrderDetailT_OrderDetailOrderID = ? AND
                                    T_OrderDetailT_TestSasCode LIKE ?
                                ";

                        $query = $this->db_onedev->query($sql,array(
                            $new_orderdetail_orderid,
                            $userid,
                            $order['T_OrderDetailOrderID'],
                            $orderdetail_sascode
                        ));

                        if (!$query) {
                            $this->db_onedev->trans_rollback();
                            echo $this->db_onedev->last_query();
                            $this->sys_error_db('error', 'Error update order detail: ' . $this->db_onedev);
                            exit;
                        }


                    }else{
                        //insert ke orderdetail jika tidak ditemukan
                        $sql = "INSERT INTO t_orderdetail (
                                    T_OrderDetailT_OrderHeaderID,
                                    T_OrderDetailT_OrderDetailOrderID,
                                    T_OrderDetailT_TestID,
                                    T_OrderDetailT_TestCode,
                                    T_OrderDetailT_TestSasCode,
                                    T_OrderDetailT_TestName,
                                    T_OrderDetailT_TestIsResult,
                                    T_OrderDetailT_TestIsPanel,
                                    T_OrderDetailT_TestIsPrice,
                                    T_OrderDetailPrice,
                                    T_OrderDetailTotal,
                                    T_OrderDetailCreated,
                                    T_OrderDetailCreatedUserID
                                )
                                SELECT ?,
                                ?,
                                T_TestID,
                                T_TestCode,
                                T_TestSasCode,
                                T_TestName,
                                T_TestIsResult,
                                'N',
                                T_TestIsPrice,
                                if(T_TestSasCode = ?, ?,0),
                                if(T_TestSasCode = ?, ?,0),
                                NOW(),
                                ?
                                FROM t_test
                                WHERE
                                T_TestSasCode LIKE ?
                                ";
                        $query = $this->db_onedev->query($sql,array(
                            $order['T_OrderHeaderID'],
                            $new_orderdetail_orderid,
                            $value['T_TestSasCode'],
                            $value['T_PacketDetailPrice'],
                            $value['T_TestSasCode'],
                            $value['T_PacketDetailPrice'],
                            $userid,
                            $orderdetail_sascode
                        ));

                        //echo $this->db_onedev->last_query();
                        if (!$query) {
                            $this->db_onedev->trans_rollback();
                            echo $this->db_onedev->last_query();
                            $this->sys_error_db('error', 'Error insert order detail: ' . $this->db_onedev);
                            exit;
                        }
                    }
                }

                //update isactive = N test lama yg tdk ada di paket baru
                $sql = "UPDATE t_orderdetail SET T_OrderDetailIsActive = 'N', T_OrderDetailDeleted = NOW(), T_OrderDetailDeletedUserID = ?
                        WHERE
                        T_OrderDetailT_OrderDetailOrderID = ? AND T_OrderDetailIsActive = 'Y'";
                $query = $this->db_onedev->query($sql,array(
                    $userid,
                    $order['T_OrderDetailOrderID']
                ));

                if (!$query) {
                    $this->db_onedev->trans_rollback();
                    echo $this->db_onedev->last_query();
                    $this->sys_error_db('error', 'Error update isactive N order detail: ' . $this->db_onedev);
                    exit;
                }

                $sql = "UPDATE t_orderdetailorder SET T_OrderDetailOrderIsActive = 'N', T_OrderDetailOrderDeleted = NOW(), T_OrderDetailOrderDeletedUserID = ?
                        WHERE
                        T_OrderDetailOrderID = ?";
                $query = $this->db_onedev->query($sql,array(
                    $userid,
                    $order['T_OrderDetailOrderID']
                ));

                if (!$query) {
                    $this->db_onedev->trans_rollback();
                    echo $this->db_onedev->last_query();
                    $this->sys_error_db('error', 'Error update isactive N order detail order: ' . $this->db_onedev);
                    exit;
                }

                $sql = "SELECT T_SampleStationID, M_LocationID
                        FROM t_orderdetail
                        JOIN t_test ON T_OrderDetailT_TestID = T_TestID AND T_TestIsActive = 'Y'
                        JOIN t_sampletype ON T_TestT_SampleTypeID = T_SampleTypeID
                        JOIN t_bahan ON T_SampleTypeT_BahanID = T_BahanID
                        JOIN t_samplestation ON T_BahanT_SampleStationID = T_SampleStationID AND T_SampleStationIsActive = 'Y'
                        JOIN m_location ON M_LocationT_SampleStationID = T_SampleStationID AND M_LocationIsActive = 'Y'
                        WHERE
                        T_OrderDetailT_OrderHeaderID = ? AND T_OrderDetailIsActive = 'Y'
                        GROUP BY T_SampleStationID ";
                $query = $this->db_onedev->query($sql,array(
                    $order['T_OrderHeaderID']
                ));

                if (!$query) {
                    $this->db_onedev->trans_rollback();
                    echo $this->db_onedev->last_query();
                    $this->sys_error_db('error', 'Error get station: ' . $this->db_onedev);
                    exit;
                }

                $order_locations = $query->result_array();
                if(count($order_locations) > 0){
                    $sql = "UPDATE t_order_location SET T_OrderLocationIsActive = 'X' WHERE T_OrderLocationT_OrderHeaderID = ? AND T_OrderLocationIsActive = 'Y'";
                    $query = $this->db_onedev->query($sql,array(
                        $order['T_OrderHeaderID']
                    ));

                    foreach ($order_locations as $k => $v) {
                        $exist_order_location = [];
                    $sql = " SELECT *
                                FROM t_order_location
                                WHERE
                                T_OrderLocationT_OrderHeaderID = ? AND 
                                T_OrderLocationT_SampleStationID= ? AND 
                                T_OrderLocationM_LocationID = ? AND
                                T_OrderLocationIsActive = 'X' LIMIT 1";
                        $query = $this->db_onedev->query($sql,array(
                            $order['T_OrderHeaderID'],
                            $v['T_SampleStationID'],
                            $v['M_LocationID']
                        ));
                        $dt_exist_order_location = $query->result_array();
                        if(count($dt_exist_order_location) > 0){
                            $exist_order_location = $dt_exist_order_location[0];
                            $sql = "UPDATE t_order_location SET T_OrderLocationIsActive = 'Y' WHERE T_OrderLocationID = ? ";
                            $query = $this->db_onedev->query($sql,array(
                                $exist_order_location['T_OrderLocationID']
                            ));
                        }else{
                            $sql = "INSERT INTO t_order_location (
                                T_OrderLocationT_OrderHeaderID,
                                T_OrderLocationM_LocationID,
                                T_OrderLocationT_SampleStationID,
                                T_OrderLocationCreated,
                                T_OrderLocationUserID
                            )
                            VALUES(?,?,?,NOW(),?)";
                            $query = $this->db_onedev->query($sql,array(
                                $order['T_OrderHeaderID'],
                                $v['M_LocationID'],
                                $v['T_SampleStationID'],
                                $userid
                            ));
                        }
                    }

                    $sql = "UPDATE t_order_location SET T_OrderLocationIsActive = 'N', T_OrderLocationUserID = ? WHERE T_OrderLocationT_OrderHeaderID = ? AND T_OrderLocationIsActive = 'X'";
                    $query = $this->db_onedev->query($sql,array(
                        $userid,
                        $order['T_OrderHeaderID']
                    ));
                }

                $sql = "SELECT T_OrderHeaderLabNumber, T_TestID, T_SampleTypeID , T_SampleTypeSuffix, T_SampleStationIsNonLab, T_SampleStationID
                        FROM t_orderheader
                        JOIN t_orderdetail ON t_orderheaderid = t_orderdetailt_orderheaderid AND t_orderdetailIsActive = 'Y'
                        JOIN t_test ON t_orderdetailt_testid = t_testid AND T_TestIsResult = 'Y'
                        JOIN group_resultdetail ON Group_ResultDetailT_TestID = T_TestID AND Group_resultDetailIsActive = 'Y'
                        JOIN group_result ON Group_ResultDetailGroup_ResultID = Group_ResultID AND Group_ResultFlagNonLab= 'N'
                        JOIN t_sampletype ON T_TestT_SampleTypeID = T_SampleTypeID
                        JOIN t_bahan ON T_SampleTypeT_BahanID = T_BahanID
                        JOIN t_samplestation ON T_BahanT_SampleStationID = T_SampleStationID
                        WHERE T_OrderHeaderID = ?
                        GROUP BY T_SampleTypeID";
                $query = $this->db_onedev->query($sql,array(
                    $order['T_OrderHeaderID']
                ));

                $order_samples = $query->result_array();
                if($order_samples){
                    $sql = "UPDATE t_ordersample SET T_OrderSampleIsActive = 'X'
                            WHERE
                            T_OrderSampleT_OrderHeaderID = ? AND T_OrderSampleIsActive = 'Y'";
                    $this->db_onedev->query($sql,array(
                        $order['T_OrderHeaderID']
                    ));

                    foreach ($order_samples as $sample) {
                        $exist_sample = [];
                        $sql = "SELECT *
                                FROM t_ordersample
                                WHERE
                                T_OrderSampleT_OrderHeaderID = ? AND 
                                T_OrderSampleT_SampleTypeID = ? AND 
                                T_OrderSampleIsActive = 'X'";
                        $query = $this->db_onedev->query($sql,array(
                            $order['T_OrderHeaderID'],
                            $sample['T_SampleTypeID']
                        ));
                        //echo  $this->db_onedev->last_query();
                        $dt_exist_sample = $query->result_array();
                        if(count($dt_exist_sample) > 0){
                            $exist_sample = $dt_exist_sample[0];
                            $sql  = "UPDATE t_ordersample SET T_OrderSampleIsActive = 'Y' WHERE T_OrderSampleID = ?";
                            $query = $this->db_onedev->query($sql,array(
                                $exist_sample['T_OrderSampleID']
                            ));
                            //echo $this->db_onedev->last_query();
                        }else{
                            $barcode = $labnumber . $sample['T_SampleTypeSuffix']."1";

                            $sql = "INSERT INTO t_barcodelab(
                                    T_BarcodeLabT_OrderHeaderID,
                                    T_BarcodeLabBarcode,
                                    T_BarcodeLabT_SampleTypeID,
                                    T_BarcodeLabCounter,
                                    T_BarcodeLabCreated,
                                    T_BarcodeLabCreatedUserID)
                                    VALUES (?,?,?,?,NOW(),?)";
                            $query = $this->db_onedev->query($sql,array(
                                $order['T_OrderHeaderID'],
                                $barcode,
                                $sample['T_SampleTypeID'],
                                "1",
                                $userid
                            ));

                            $last_id_barcode = $this->db_onedev->insert_id();

                            $sql = "INSERT INTO t_ordersample(
                                    T_OrderSampleT_OrderHeaderID,
                                    T_OrderSampleT_SampleTypeID,
                                    T_OrderSampleT_BarcodeLabID,
                                    T_OrderSampleBarcode,
                                    T_OrderSampleT_SampleStationID,
                                    T_OrderSampleCreated,
                                    T_OrderSampleCreatedUserID)
                                    VALUES(
                                        ?,?,?,?,?,NOW(),?
                                    )";
                            $query = $this->db_onedev->query($sql,array(
                                $order['T_OrderHeaderID'],
                                $sample['T_SampleTypeID'],
                                $last_id_barcode,
                                $barcode,
                                $sample['T_SampleStationID'],
                                $userid
                            ));
                            //echo $this->db_onedev->last_query();
                        }
                    }

                    $sql = "UPDATE t_ordersample SET T_OrderSampleIsActive = 'N'
                            WHERE
                            T_OrderSampleT_OrderHeaderID = ? AND T_OrderSampleIsActive = 'X'";
                    $this->db_onedev->query($sql,array(
                        $order['T_OrderHeaderID']
                    ));
                }   

                $sql = "SELECT *
                        FROM t_orderdetail
                        JOIN t_test ON T_OrderDetailT_TestID = T_TestID 
                        JOIN nonlab_template_mapping ON NonlabTemplateMappingNat_TestID = T_TestNat_TestID AND NonlabTemplateMappingIsActive = 'Y'
                        JOIN nonlab_template ON NonlabTemplateMappingNonlabTemplateID = NonlabTemplateID AND NonlabTemplateIsActive = 'Y'
                        JOIN group_resultdetail ON Group_ResultDetailT_TestID  = T_TestID 
                        JOIN group_result ON Group_ResultDetailGroup_ResultID = Group_ResultID AND Group_ResultFlagNonLab = 'Y'
                        WHERE
                        T_OrderDetailT_OrderHeaderID = {$order['T_OrderHeaderID']} AND T_OrderDetailIsActive = 'Y'
                        GROUP BY T_TestID";
                    // echo $sql;
                $query = $this->db_onedev->query($sql);

                $order_nonlab = $query->result_array();
                if(count($order_nonlab) > 0){
                    $sql = "UPDATE so_resultentry SET So_ResultEntryIsActive = 'X'
                            WHERE
                            So_ResultEntryT_OrderHeaderID = ? AND So_ResultEntryIsActive = 'Y'";
                    $this->db_onedev->query($sql,array(
                        $order['T_OrderHeaderID']
                    ));

                    foreach ($order_nonlab as $nonlab) {
                        $exist_so = [];
                        $sql = "SELECT *
                                FROM so_resultentry
                                WHERE
                                So_ResultEntryT_OrderHeaderID = ? AND
                                So_ResultEntryT_OrderDetailID = ? AND
                                So_ResultEntryIsActive = 'X'";
                        $query = $this->db_onedev->query($sql,array(
                            $order['T_OrderHeaderID'],
                            $nonlab['T_OrderDetailID']
                        ));
            
                        $dt_exist_so = $query->result_array();
                        if(count($dt_exist_so) > 0){
                            $exist_so = $dt_exist_so[0];
                            $sql = "UPDATE so_resultentry SET So_ResultEntryIsActive = 'Y'
                                WHERE
                                So_ResultEntryID = ? AND So_ResultEntryIsActive = 'X'";
                            $this->db_onedev->query($sql,array(
                                $exist_so['So_ResultEntryID']
                            ));
                        }
                    }

                    $sql = "UPDATE so_resultentry SET So_ResultEntryIsActive = 'N'
                            WHERE
                            So_ResultEntryT_OrderHeaderID = ? AND So_ResultEntryIsActive = 'X'";
                    $this->db_onedev->query($sql,array(
                        $order['T_OrderHeaderID']
                    ));

                }
                
                $results = ["status" => true, "msg" => "Ganti Paket Berhasil"];


            }else{
                $results = ["status" => false, "msg" => "Gagal ganti paket, kode paket tidak ditemukan"];
            }

            $this->db_onedev->trans_commit();

        }

        echo json_encode($results);
    }

    function get_hostname(){
        echo $_SERVER['SERVER_NAME'];
    }

    function fix_kultur_faeces(){
        $sql = "SELECT T_OrderHeaderID, T_OrderDetailT_OrderDetailOrderID as detailorderid
                FROM t_orderdetail
                JOIN t_orderheader ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID AND
                T_OrderHeaderMgm_McuID = 147 AND T_OrderDetailT_TestID = 1514 AND 
                DATE(T_OrderHeaderDate) < '2024-11-11'
                GROUP BY T_OrderHeaderID";
        $query = $this->db_onedev->query($sql);
        $datas = $query->result_array();
        foreach($datas as $k => $v){
            $tests = [3487,3488,3489,3490,3491];
            foreach ($tests as $value) {
                $sql = "SELECT COUNT(*) as xcount
                        FROM t_orderdetail
                        WHERE
                        T_OrderDetailT_OrderHeaderID = ? AND T_OrderDetailIsActive = 'Y' AND
                        T_OrderDetailT_TestID = ?";
                $query = $this->db_onedev->query($sql,array($v['T_OrderHeaderID'],$value));
                $dt_exist = $query->row()->xcount;
                if($dt_exist == 0){
                    $sql = "INSERT INTO t_orderdetail (
                                T_OrderDetailT_OrderHeaderID,
                                T_OrderDetailT_OrderDetailOrderID,
                                T_OrderDetailT_TestID,
                                T_OrderDetailT_TestCode,
                                T_OrderDetailT_TestSasCode,
                                T_OrderDetailT_TestName,
                                T_OrderDetailT_TestIsResult,
                                T_OrderDetailT_TestIsPrice,
                                T_OrderDetailCreated,
                                T_OrderDetailCreatedUserID
                            )
                            SELECT {$v['T_OrderHeaderID']},
                                    {$v['detailorderid']},
                                    T_TestID,
                                    T_TestCode,
                                    T_TestSasCode,
                                    T_TestName,
                                    T_TestIsResult,
                                    T_TestIsPrice,
                                    NOW(),
                                    122467
                            FROM t_test
                            WHERE T_TestID = ?";
                    $query = $this->db_onedev->query($sql,array($value));
                }
            }
            
        }

    }
}