<?php
defined('BASEPATH') or exit('No direct script access allowed');

class Wynacom extends MY_Controller
{
    var $db;
    var $load;
    public function __construct()
    {
        parent::__construct();
        $this->db = $this->load->database("default", true);
    }
    public function index()
    {
        $this->sys_ok(array(
            "message" => "Wynacom tools API",
            "endpoints" => array(
                "POST /tools/wynacom/sampling_timestamp",
                "POST /tools/wynacom/glucose",
                "POST /tools/wynacom/generate_token",
                "POST /tools/wynacom/check_token",
                "POST /tools/wynacom/token_setting",
                "POST /tools/wynacom/save_token_setting"
            )
        ));
        exit;
    }

    public function generate_token()
    {
        try {
            $active_token = $this->get_active_wynacom_token();
            if ($active_token) {
                $this->sys_ok(array(
                    "access_token" => $active_token["Api_WynacomTokenAccessToken"],
                    "token_type" => "Bearer",
                    "expired_at" => $active_token["Api_WynacomTokenExpiredAt"],
                    "expires_in" => (int) $active_token["expires_in"]
                ));
                exit;
            }

            $expired_days = $this->get_wynacom_token_expired_days();
            $access_token = $this->create_access_token();
            $expired_at = date("Y-m-d H:i:s", strtotime("+{$expired_days} days"));

            $sql = "INSERT INTO api_wynacom_token (
                    Api_WynacomTokenAccessToken,
                    Api_WynacomTokenExpiredAt,
                    Api_WynacomTokenIsActive,
                    Api_WynacomTokenCreated,
                    Api_WynacomTokenCreatedUserID,
                    Api_WynacomTokenLastUpdated,
                    Api_WynacomTokenLastUpdatedUserID
                ) VALUES (?, ?, 'Y', NOW(), 0, NOW(), 0)";
            $qry = $this->db->query($sql, array(
                $access_token,
                $expired_at
            ));

            if (!$qry) {
                $this->sys_error_db("Gagal generate token Wynacom", $this->db);
                exit;
            }

            $this->sys_ok(array(
                "access_token" => $access_token,
                "token_type" => "Bearer",
                "expired_at" => $expired_at,
                "expired_days" => $expired_days,
                "expires_in" => $expired_days * 86400
            ));
            exit;
        } catch (Exception $exc) {
            $this->sys_error($exc->getMessage());
            exit;
        }
    }

    public function check_token()
    {
        try {
            $credential = $this->validate_wynacom_token($this->get_access_token_param());

            $this->sys_ok(array(
                "valid" => true,
                "token_id" => $credential["Api_WynacomTokenID"],
                "expired_at" => $credential["Api_WynacomTokenExpiredAt"]
            ));
            exit;
        } catch (Exception $exc) {
            $this->sys_error($exc->getMessage());
            exit;
        }
    }

    public function token_setting()
    {
        try {
            $this->sys_ok($this->get_wynacom_token_setting());
            exit;
        } catch (Exception $exc) {
            $this->sys_error($exc->getMessage());
            exit;
        }
    }

    public function save_token_setting()
    {
        try {
            $prm = is_array($this->sys_input) ? $this->sys_input : array();
            $expired_days = isset($prm["token_expired_days"]) ? (int) $prm["token_expired_days"] : 0;

            if ($expired_days < 1) {
                throw new Exception("Parameter token_expired_days harus angka lebih besar dari 0");
            }

            if ($expired_days > 365) {
                throw new Exception("Parameter token_expired_days maksimal 365 hari");
            }

            $sql = "INSERT INTO api_wynacom_setting (
                    Api_WynacomSettingKey,
                    Api_WynacomSettingValue,
                    Api_WynacomSettingDescription,
                    Api_WynacomSettingIsActive,
                    Api_WynacomSettingCreatedUserID,
                    Api_WynacomSettingLastUpdatedUserID
                ) VALUES (
                    'token_expired_days',
                    ?,
                    'Masa berlaku token Wynacom dalam hari',
                    'Y',
                    0,
                    0
                )
                ON DUPLICATE KEY UPDATE
                    Api_WynacomSettingValue = VALUES(Api_WynacomSettingValue),
                    Api_WynacomSettingDescription = VALUES(Api_WynacomSettingDescription),
                    Api_WynacomSettingIsActive = 'Y',
                    Api_WynacomSettingLastUpdated = NOW(),
                    Api_WynacomSettingLastUpdatedUserID = 0";
            $qry = $this->db->query($sql, array((string) $expired_days));

            if (!$qry) {
                $this->sys_error_db("Gagal menyimpan setting token Wynacom", $this->db);
                exit;
            }

            $this->sys_ok($this->get_wynacom_token_setting());
            exit;
        } catch (Exception $exc) {
            $this->sys_error($exc->getMessage());
            exit;
        }
    }

    public function sampling_timestamp()
    {
        try {
            $this->validate_wynacom_token($this->get_access_token_param());

            $order = $this->require_order_header();
            $lab_number = $order["T_OrderHeaderLabNumber"];

            $this->sys_ok($this->union_sampling_timestamp($lab_number));
            exit;
        } catch (Exception $exc) {
            $this->sys_error($exc->getMessage());
            exit;
        }
    }

    public function glucose()
    {
        try {
            $this->validate_wynacom_token($this->get_access_token_param());

            $order = $this->require_order_header();
            $lab_number = $order["T_OrderHeaderLabNumber"];

            $this->sys_ok($this->get_glucose_result($lab_number));
            exit;
        } catch (Exception $exc) {
            $this->sys_error($exc->getMessage());
            exit;
        }
    }

    private function require_order_header()
    {
        $lab_number = $this->get_lab_number_param();

        if ($lab_number === "") {
            throw new Exception("Parameter T_OrderHeaderLabNumber wajib diisi");
        }

        $order = $this->get_order_header($lab_number);
        if (!$order) {
            throw new Exception("Order tidak ditemukan: " . $lab_number);
        }

        return $order;
    }

    private function get_lab_number_param()
    {
        $prm = is_array($this->sys_input) ? $this->sys_input : array();

        if (isset($prm["T_OrderHeaderLabNumber"])) {
            return trim($prm["T_OrderHeaderLabNumber"]);
        }

        if (isset($prm["orderNumber"])) {
            return trim($prm["orderNumber"]);
        }

        if (isset($prm["nolab"])) {
            return trim($prm["nolab"]);
        }

        return "";
    }

    private function get_access_token_param()
    {
        $headers = function_exists("getallheaders") ? getallheaders() : array();

        if (isset($headers["Authorization"])) {
            return trim(str_replace("Bearer", "", $headers["Authorization"]));
        }

        if (isset($headers["authorization"])) {
            return trim(str_replace("Bearer", "", $headers["authorization"]));
        }

        $prm = is_array($this->sys_input) ? $this->sys_input : array();
        if (isset($prm["access_token"])) {
            return trim($prm["access_token"]);
        }

        if (isset($prm["token"])) {
            return trim($prm["token"]);
        }

        return "";
    }

    private function validate_wynacom_token($token)
    {
        if ($token === "") {
            throw new Exception("Token wajib diisi");
        }

        $sql = "SELECT *
            FROM api_wynacom_token
            WHERE Api_WynacomTokenAccessToken = ?
                AND Api_WynacomTokenIsActive = 'Y'
                AND Api_WynacomTokenExpiredAt >= NOW()
                AND (
                    Api_WynacomTokenDeleted IS NULL
                    OR Api_WynacomTokenDeleted = '0000-00-00 00:00:00'
                )
            LIMIT 1";
        $qry = $this->db->query($sql, array($token));

        if (!$qry) {
            $this->sys_error_db("Gagal validasi token Wynacom", $this->db);
            exit;
        }

        $rows = $qry->result_array();
        if (count($rows) === 0) {
            throw new Exception("Token tidak valid atau sudah expired");
        }

        $credential = $rows[0];
        return $credential;
    }

    private function get_active_wynacom_token()
    {
        $sql = "SELECT *,
                TIMESTAMPDIFF(SECOND, NOW(), Api_WynacomTokenExpiredAt) AS expires_in
            FROM api_wynacom_token
            WHERE Api_WynacomTokenExpiredAt >= NOW()
                AND Api_WynacomTokenIsActive = 'Y'
                AND (
                    Api_WynacomTokenDeleted IS NULL
                    OR Api_WynacomTokenDeleted = '0000-00-00 00:00:00'
                )
            ORDER BY Api_WynacomTokenID DESC
            LIMIT 1";
        $qry = $this->db->query($sql);

        if (!$qry) {
            $this->sys_error_db("Gagal mengambil token aktif Wynacom", $this->db);
            exit;
        }

        $rows = $qry->result_array();
        return count($rows) > 0 ? $rows[0] : false;
    }

    private function get_wynacom_token_expired_days()
    {
        $default_days = 3;
        $sql = "SELECT Api_WynacomSettingValue
            FROM api_wynacom_setting
            WHERE Api_WynacomSettingKey = 'token_expired_days'
                AND Api_WynacomSettingIsActive = 'Y'
            ORDER BY Api_WynacomSettingID DESC
            LIMIT 1";
        $qry = $this->db->query($sql);

        if (!$qry) {
            return $default_days;
        }

        $rows = $qry->result_array();
        if (count($rows) === 0) {
            return $default_days;
        }

        $expired_days = (int) $rows[0]["Api_WynacomSettingValue"];
        if ($expired_days < 1) {
            return $default_days;
        }

        return $expired_days;
    }

    private function get_wynacom_token_setting()
    {
        $expired_days = $this->get_wynacom_token_expired_days();

        return array(
            "token_expired_days" => $expired_days,
            "expires_in" => $expired_days * 86400
        );
    }

    private function create_access_token()
    {
        if (function_exists("random_bytes")) {
            return bin2hex(random_bytes(32));
        }

        if (function_exists("openssl_random_pseudo_bytes")) {
            return bin2hex(openssl_random_pseudo_bytes(32));
        }

        return hash("sha256", uniqid("", true) . microtime(true));
    }

    private function get_order_header($lab_number)
    {
        $sql = "SELECT T_OrderHeaderID, T_OrderHeaderLabNumber
            FROM t_orderheader
            WHERE T_OrderHeaderLabNumber = ?
                AND T_OrderHeaderIsActive = 'Y'
            LIMIT 1";
        $qry = $this->db->query($sql, array($lab_number));

        if (!$qry) {
            $this->sys_error_db("Gagal mengambil order header", $this->db);
            exit;
        }

        $rows = $qry->result_array();
        return count($rows) > 0 ? $rows[0] : false;
    }

    private function get_nonlab_timestamp($lab_number)
    {
        $sql = "SELECT
                ss.T_SampleStationName as sample_station_name,
                ml.M_LocationName as location_name,
                LEFT(tt.T_TestSasCode, 8) as cpone_test_code,
                tt.T_TestName as cpone_test_name,
                IFNULL(ntm.Nat_TestMapCode, 'Un-Map') AS lis_test_code,
                CASE
                    WHEN so.T_SamplingSoProcessDate IS NULL THEN NULL
                    ELSE CONCAT(so.T_SamplingSoProcessDate, ' ', IFNULL(so.T_SamplingSoProcessTime, '00:00:00'))
                END AS sampling_check_in_datetime,
                so.T_SamplingSoProcessUserID as sampling_check_in_userid,
                CASE
                    WHEN so.T_SamplingSoDoneDate IS NULL THEN NULL
                    ELSE CONCAT(so.T_SamplingSoDoneDate, ' ', IFNULL(so.T_SamplingSoDoneTime, '00:00:00'))
                END AS sampling_check_out_datetime,
                so.T_SamplingSoDoneUserID as sampling_check_out_userid
            FROM t_orderheader oh
            JOIN t_samplingso so ON so.T_SamplingSoT_OrderHeaderID = oh.T_OrderHeaderID
                AND so.T_SamplingSoIsActive = 'Y'
            JOIN t_test tt ON tt.T_TestID = so.T_SamplingSoT_TestID
                AND tt.T_TestIsActive = 'Y'
            JOIN t_sampletype st ON st.T_SampleTypeID = tt.T_TestT_SampleTypeID
                AND st.T_SampleTypeIsActive = 'Y'
            JOIN t_bahan tb ON tb.T_BahanID = st.T_SampleTypeT_BahanID
                AND tb.T_BahanIsActive = 'Y'
            LEFT JOIN nat_testmap ntm ON ntm.Nat_TestMapNat_TestID = tt.T_TestNat_TestID
                AND ntm.Nat_TestMapIsActive = 'Y'
            LEFT JOIN t_orderdetail od ON od.T_OrderDetailT_OrderHeaderID = oh.T_OrderHeaderID
                AND od.T_OrderDetailT_TestID = tt.T_TestID
                AND od.T_OrderDetailIsActive = 'Y'
            JOIN t_samplestation ss ON ss.T_SampleStationID = tb.T_BahanT_SampleStationID
                AND ss.T_SampleStationIsActive = 'Y'
            LEFT JOIN t_order_location ol ON ol.T_OrderLocationT_OrderHeaderID = oh.T_OrderHeaderID
                AND ol.T_OrderLocationT_SampleStationID = ss.T_SampleStationID
                AND ol.T_OrderLocationIsActive = 'Y'
            LEFT JOIN m_location ml ON ml.M_LocationID = ol.T_OrderLocationM_LocationID
                AND ml.M_LocationIsActive = 'Y'
            WHERE oh.T_OrderHeaderLabNumber = ?
                AND oh.T_OrderHeaderIsActive = 'Y'
            GROUP BY so.T_SamplingSoT_TestID
            ORDER BY ss.T_SampleStationName, Nat_TestMapCode, tt.T_TestName";
        $qry = $this->db->query($sql, array($lab_number));

        if (!$qry) {
            $this->sys_error_db("Gagal mengambil timestamp nonlab", $this->db);
            exit;
        }

        return $qry->result_array();
    }

    private function get_lab_timestamp($lab_number)
    {
        $sql = "SELECT
                ss.T_SampleStationName as sample_station_name,
                ml.M_LocationName as location_name,
                LEFT(effective_test.T_TestSasCode, 8) as cpone_test_code,
                effective_test.T_TestName as cpone_test_name,
                IFNULL(ntm.Nat_TestMapCode, 'Un-Map') as lis_test_code,
                CASE
                    WHEN os.T_OrderSampleSamplingDate IS NULL THEN NULL
                    ELSE CONCAT(os.T_OrderSampleSamplingDate, ' ', IFNULL(os.T_OrderSampleSamplingTime, '00:00:00'))
                END AS sampling_check_in_datetime,
                os.T_OrderSampleSamplingUserID as sampling_check_in_userid,
                CASE
                    WHEN os.T_OrderSampleReceiveDate IS NULL THEN NULL
                    ELSE CONCAT(os.T_OrderSampleReceiveDate, ' ', IFNULL(os.T_OrderSampleReceiveTime, '00:00:00'))
                END AS sampling_check_out_datetime,
                os.T_OrderSampleReceiveUserID as sampling_check_out_userid
            FROM t_orderheader oh
            JOIN t_ordersample os ON os.T_OrderSampleT_OrderHeaderID = oh.T_OrderHeaderID
                AND os.T_OrderSampleIsActive = 'Y'
            JOIN t_sampletype st ON st.T_SampleTypeID = os.T_OrderSampleT_SampleTypeID
                AND st.T_SampleTypeIsActive = 'Y'
            JOIN t_bahan tb ON tb.T_BahanID = st.T_SampleTypeT_BahanID
                AND tb.T_BahanIsActive = 'Y'
            JOIN t_test child_test ON child_test.T_TestT_SampleTypeID = st.T_SampleTypeID
                AND child_test.T_TestIsActive = 'Y'
            JOIN t_test effective_test ON effective_test.T_TestID = CASE
                    WHEN child_test.T_TestParentT_TestID IS NOT NULL
                        AND child_test.T_TestParentT_TestID <> 0
                        THEN child_test.T_TestParentT_TestID
                    ELSE child_test.T_TestID
                END
                AND effective_test.T_TestIsActive = 'Y'
            JOIN t_orderdetail od ON od.T_OrderDetailT_OrderHeaderID = oh.T_OrderHeaderID
                AND od.T_OrderDetailT_TestID = effective_test.T_TestID
                AND od.T_OrderDetailIsActive = 'Y'
            LEFT JOIN nat_testmap ntm ON ntm.Nat_TestMapNat_TestID = effective_test.T_TestNat_TestID
                AND ntm.Nat_TestMapIsActive = 'Y'
            LEFT JOIN t_samplestation ss ON ss.T_SampleStationID = tb.T_BahanT_SampleStationID
                AND ss.T_SampleStationIsActive = 'Y'
            LEFT JOIN t_order_location ol ON ol.T_OrderLocationT_OrderHeaderID = oh.T_OrderHeaderID
                AND ol.T_OrderLocationT_SampleStationID = ss.T_SampleStationID
                AND ol.T_OrderLocationIsActive = 'Y'
            LEFT JOIN m_location ml ON ml.M_LocationID = ol.T_OrderLocationM_LocationID
                AND ml.M_LocationIsActive = 'Y'
            WHERE oh.T_OrderHeaderLabNumber = ?
                AND oh.T_OrderHeaderIsActive = 'Y'
            GROUP BY effective_test.T_TestID
            ORDER BY ss.T_SampleStationName, st.T_SampleTypeName, Nat_TestMapCode, effective_test.T_TestName";
        $qry = $this->db->query($sql, array($lab_number));

        if (!$qry) {
            $this->sys_error_db("Gagal mengambil timestamp lab", $this->db);
            exit;
        }

        return $qry->result_array();
    }

    private function get_glucose_result($lab_number)
    {
        $sql = "SELECT
                LEFT(COALESCE(NULLIF(tt.T_TestSasCode, ''), nt.Nat_TestCode), 8) AS cpone_test_code,
                COALESCE(tt.T_TestName, nt.Nat_TestName) AS cpone_test_name,
                IFNULL(ntm.Nat_TestMapCode, 'Un-Map') AS lis_test_code,
                og.OrderGlucoseResult AS result_glucosa
            FROM t_orderheader oh
            JOIN order_glucose og ON og.OrderGlucoseT_OrderHeaderID = oh.T_OrderHeaderID
                AND og.OrderGlucoseIsActive = 'Y'
            JOIN nat_test nt ON nt.Nat_TestID = og.OrderGlucoseNat_TestID
                AND nt.Nat_TestIsActive = 'Y'
            LEFT JOIN t_orderdetail od ON od.T_OrderDetailT_OrderHeaderID = oh.T_OrderHeaderID
                AND od.T_OrderDetailIsActive = 'Y'
            LEFT JOIN t_test tt ON tt.T_TestID = od.T_OrderDetailT_TestID
                AND tt.T_TestNat_TestID = og.OrderGlucoseNat_TestID
                AND tt.T_TestIsActive = 'Y'
            LEFT JOIN nat_testmap ntm ON ntm.Nat_TestMapNat_TestID = og.OrderGlucoseNat_TestID
                AND ntm.Nat_TestMapIsActive = 'Y'
            WHERE oh.T_OrderHeaderLabNumber = ?
                AND oh.T_OrderHeaderIsActive = 'Y'
            GROUP BY og.OrderGlucoseID
            ORDER BY cpone_test_code, cpone_test_name";
        $qry = $this->db->query($sql, array($lab_number));

        if (!$qry) {
            $this->sys_error_db("Gagal mengambil data glucose", $this->db);
            exit;
        }

        return $qry->result_array();
    }

    private function union_sampling_timestamp($lab_number)
    {
        $sql = "SELECT *
            FROM (
                SELECT
                    ss.T_SampleStationName as sample_station_name,
                    ml.M_LocationName as location_name,
                    LEFT(tt.T_TestSasCode, 8) as cpone_test_code,
                    tt.T_TestName as cpone_test_name,
                    IFNULL(ntm.Nat_TestMapCode, 'Un-Map') AS lis_test_code,
                    CASE
                        WHEN so.T_SamplingSoProcessDate IS NULL THEN NULL
                        ELSE CONCAT(so.T_SamplingSoProcessDate, ' ', IFNULL(so.T_SamplingSoProcessTime, '00:00:00'))
                    END AS sampling_check_in_datetime,
                    so.T_SamplingSoProcessUserID as sampling_check_in_userid,
                    CASE
                        WHEN so.T_SamplingSoDoneDate IS NULL THEN NULL
                        ELSE CONCAT(so.T_SamplingSoDoneDate, ' ', IFNULL(so.T_SamplingSoDoneTime, '00:00:00'))
                    END AS sampling_check_out_datetime,
                    so.T_SamplingSoDoneUserID as sampling_check_out_userid
                FROM t_orderheader oh
                JOIN t_samplingso so ON so.T_SamplingSoT_OrderHeaderID = oh.T_OrderHeaderID
                    AND so.T_SamplingSoIsActive = 'Y'
                JOIN t_test tt ON tt.T_TestID = so.T_SamplingSoT_TestID
                    AND tt.T_TestIsActive = 'Y'
                JOIN t_sampletype st ON st.T_SampleTypeID = tt.T_TestT_SampleTypeID
                    AND st.T_SampleTypeIsActive = 'Y'
                JOIN t_bahan tb ON tb.T_BahanID = st.T_SampleTypeT_BahanID
                    AND tb.T_BahanIsActive = 'Y'
                LEFT JOIN nat_testmap ntm ON ntm.Nat_TestMapNat_TestID = tt.T_TestNat_TestID
                    AND ntm.Nat_TestMapIsActive = 'Y'
                LEFT JOIN t_orderdetail od ON od.T_OrderDetailT_OrderHeaderID = oh.T_OrderHeaderID
                    AND od.T_OrderDetailT_TestID = tt.T_TestID
                    AND od.T_OrderDetailIsActive = 'Y'
                JOIN t_samplestation ss ON ss.T_SampleStationID = tb.T_BahanT_SampleStationID
                    AND ss.T_SampleStationIsActive = 'Y'
                LEFT JOIN t_order_location ol ON ol.T_OrderLocationT_OrderHeaderID = oh.T_OrderHeaderID
                    AND ol.T_OrderLocationT_SampleStationID = ss.T_SampleStationID
                    AND ol.T_OrderLocationIsActive = 'Y'
                LEFT JOIN m_location ml ON ml.M_LocationID = ol.T_OrderLocationM_LocationID
                    AND ml.M_LocationIsActive = 'Y'
                WHERE oh.T_OrderHeaderLabNumber = ?
                    AND oh.T_OrderHeaderIsActive = 'Y'
                GROUP BY so.T_SamplingSoT_TestID

                UNION ALL

                SELECT
                    ss.T_SampleStationName as sample_station_name,
                    ml.M_LocationName as location_name,
                    LEFT(effective_test.T_TestSasCode, 8) as cpone_test_code,
                    effective_test.T_TestName as cpone_test_name,
                    IFNULL(ntm.Nat_TestMapCode, 'Un-Map') as lis_test_code,
                    CASE
                        WHEN os.T_OrderSampleSamplingDate IS NULL THEN NULL
                        ELSE CONCAT(os.T_OrderSampleSamplingDate, ' ', IFNULL(os.T_OrderSampleSamplingTime, '00:00:00'))
                    END AS sampling_check_in_datetime,
                    os.T_OrderSampleSamplingUserID as sampling_check_in_userid,
                    CASE
                        WHEN os.T_OrderSampleReceiveDate IS NULL THEN NULL
                        ELSE CONCAT(os.T_OrderSampleReceiveDate, ' ', IFNULL(os.T_OrderSampleReceiveTime, '00:00:00'))
                    END AS sampling_check_out_datetime,
                    os.T_OrderSampleReceiveUserID as sampling_check_out_userid
                FROM t_orderheader oh
                JOIN t_ordersample os ON os.T_OrderSampleT_OrderHeaderID = oh.T_OrderHeaderID
                    AND os.T_OrderSampleIsActive = 'Y'
                JOIN t_sampletype st ON st.T_SampleTypeID = os.T_OrderSampleT_SampleTypeID
                    AND st.T_SampleTypeIsActive = 'Y'
                JOIN t_bahan tb ON tb.T_BahanID = st.T_SampleTypeT_BahanID
                    AND tb.T_BahanIsActive = 'Y'
                JOIN t_test child_test ON child_test.T_TestT_SampleTypeID = st.T_SampleTypeID
                    AND child_test.T_TestIsActive = 'Y'
                JOIN t_test effective_test ON effective_test.T_TestID = CASE
                        WHEN child_test.T_TestParentT_TestID IS NOT NULL
                            AND child_test.T_TestParentT_TestID <> 0
                            THEN child_test.T_TestParentT_TestID
                        ELSE child_test.T_TestID
                    END
                    AND effective_test.T_TestIsActive = 'Y'
                JOIN t_orderdetail od ON od.T_OrderDetailT_OrderHeaderID = oh.T_OrderHeaderID
                    AND od.T_OrderDetailT_TestID = effective_test.T_TestID
                    AND od.T_OrderDetailIsActive = 'Y'
                LEFT JOIN nat_testmap ntm ON ntm.Nat_TestMapNat_TestID = effective_test.T_TestNat_TestID
                    AND ntm.Nat_TestMapIsActive = 'Y'
                LEFT JOIN t_samplestation ss ON ss.T_SampleStationID = tb.T_BahanT_SampleStationID
                    AND ss.T_SampleStationIsActive = 'Y'
                LEFT JOIN t_order_location ol ON ol.T_OrderLocationT_OrderHeaderID = oh.T_OrderHeaderID
                    AND ol.T_OrderLocationT_SampleStationID = ss.T_SampleStationID
                    AND ol.T_OrderLocationIsActive = 'Y'
                LEFT JOIN m_location ml ON ml.M_LocationID = ol.T_OrderLocationM_LocationID
                    AND ml.M_LocationIsActive = 'Y'
                WHERE oh.T_OrderHeaderLabNumber = ?
                    AND oh.T_OrderHeaderIsActive = 'Y'
                GROUP BY effective_test.T_TestID
            ) x
            ORDER BY sample_station_name, cpone_test_code, cpone_test_name";
        $qry = $this->db->query($sql, array($lab_number, $lab_number));

        if (!$qry) {
            $this->sys_error_db("Gagal mengambil union timestamp", $this->db);
            exit;
        }

        return $qry->result_array();
    }
}