<?php
class Sendemail extends MY_Controller
{
    var $db_onedev;
    var $load;
    var $sspricemou;
    var $hostname;

    public function index()
    {
        echo "CPONE MD PRICE API";
    }

    public function __construct()
    {
        parent::__construct();
        $this->db_onedev = $this->load->database("onedev", true);
        $this->hostname = 'cpone.aplikasi.web.id';
        // $this->load->library("SsPriceMou");
    }
    function getsetup()
    {
        try {
            // if (!$this->isLogin) {
            // 	$this->sys_error("Invalid Token");
            // 	exit;
            // }
            $sql = "SELECT * 
					FROM mgm_mcu 
					WHERE Mgm_McuIsActive = 'Y'
					ORDER BY Mgm_McuStartDate ASC, Mgm_McuEndDate ASC";
            $qry = $this->db_onedev->query($sql, []);
            $last_qry = $this->db_onedev->last_query();
            if (!$qry) {
                $message = $this->db_onedev->error();
                $message['last_qry'] = $last_qry;
                $this->sys_error($message);
                exit;
            }
            $data = $qry->result_array();
            $result = [
                "records" => $data,
            ];
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function search()
    {
        if (!$this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;
        $page = $prm["page"];
        $startDate = $prm["startDate"];
        $endDate = $prm["endDate"];
        $setupID = $prm["setupID"];
        $ROW_PER_PAGE = 20;
        $start_offset = 0;
        // print_r($prm);

        if (isset($prm["page"])) {
            if (
                is_numeric($prm["page"]) && $prm["page"] > 0
            ) {
                $start_offset = ($page - 1) * $ROW_PER_PAGE;
            }
        }
        $sql = "SELECT 
                count(T_ResultHandoverID) as total
                FROM 
                t_resulthandover
                LEFT JOIN m_user ON T_ResultHandoverUserID= M_UserID
                LEFT JOIN m_staff ON M_UserM_StaffID = M_StaffID 
                WHERE T_ResultHandoverIsActive = 'Y'
                AND T_ResultHandoverDate BETWEEN ? AND ?
                AND T_ResultHandoverMgm_McuID =? ";
        $query = $this->db_onedev->query($sql, [$startDate, $endDate, $setupID]);
        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->sys_error($message);
            exit;
        }
        $total = $query->row_array()['total'];
        $sql = "SELECT 
                T_ResultHandoverID as handoverID,
                T_ResultHandoverCode as handoverCode,
                T_ResultHandoverReceivedBy as handoverReceivedBy,
                T_ResultHandoverNote as handovernote,
                DATE_FORMAT(T_ResultHandoverDate, '%d-%m-%Y %H:%i') as handoverDate,
                T_ResultHandoverMgm_McuID as hanoverSetupID,
                M_StaffName as handoverStaff
                FROM 
                t_resulthandover
                LEFT JOIN m_user ON T_ResultHandoverUserID= M_UserID
                LEFT JOIN m_staff ON M_UserM_StaffID = M_StaffID 
                WHERE T_ResultHandoverIsActive = 'Y'
                AND T_ResultHandoverDate BETWEEN ? AND ?
                AND T_ResultHandoverMgm_McuID = ? 
                LIMIT ? OFFSET ? ";
        $query = $this->db_onedev->query($sql, [$startDate, $endDate, $setupID, $ROW_PER_PAGE, $start_offset]);
        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->sys_error($message);
            exit;
        }
        $result = [
            "total" => ceil($total / $ROW_PER_PAGE),
            "records" => $query->result_array()
        ];
        $this->sys_ok($result);
    }
    function getdetail()
    {
        if (!$this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;
        $page = $prm["page"];
        $startDate = $prm["startDate"];
        $endDate = $prm["endDate"];
        $setupID = $prm["setupID"];
        $handoverID = $prm["handoverID"];
        $search = '%' . $prm['search'] . '%';
        $status = $prm["status"];
        $ROW_PER_PAGE = 20;
        $start_offset = 0;
        // print_r($prm);

        if (isset($prm["page"])) {
            if (
                is_numeric($prm["page"]) && $prm["page"] > 0
            ) {
                $start_offset = ($page - 1) * $ROW_PER_PAGE;
            }
        }
        $whereStatus = "";
        if ($status == "Y") {
            $whereStatus = "AND XEmailOutboxID IS NOT NULL AND XEmailOutboxIsSent = 'Y'";
        }
        if ($status == "NO") {
            $whereStatus = "AND (XEmailOutboxID IS NULL)";
        }
        if ($status == "N") {
            //prosess
            $whereStatus = "AND (XEmailOutboxID IS NOT NULL AND (XEmailOutboxIsSent = 'N' || XEmailOutboxIsSent = 'R'))";
        }
        $sql = "SELECT 
                COUNT(T_OrderHeaderID) as totalAll
                FROM t_orderheader
                LEFT JOIN x_email_outbox
                ON T_OrderHeaderID = XEmailOutboxRefID 
                AND XEmailOutboxIsActive = 'Y'
                AND XEmailOutboxType = 'RESULT'
                JOIN m_patient 
                ON T_OrderHeaderM_PatientID = M_PatientID
                LEFT JOIN m_title 
                ON M_PatientM_TitleID = M_TitleID
                -- LEFT JOIN m_user ON T_ResultHandoverDetailUserID = M_UserID
                -- LEFT JOIN m_staff ON M_UserM_StaffID = M_StaffID 
                WHERE 
                T_OrderHeaderIsActive = 'Y'
                AND T_OrderHeaderMgm_McuID = ?
                ";
        $query = $this->db_onedev->query($sql, [$setupID]);
        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->sys_error($message);
            exit;
        }
        // print_r($this->db_onedev->last_query());
        $totalALL = $query->row_array()['totalAll'];
        $sql = "SELECT 
                COUNT(T_OrderHeaderID) as totalSend
                FROM t_orderheader
                LEFT JOIN x_email_outbox
                ON T_OrderHeaderID = XEmailOutboxRefID 
                AND XEmailOutboxIsActive = 'Y'
                AND XEmailOutboxType = 'RESULT'
                JOIN m_patient 
                ON T_OrderHeaderM_PatientID = M_PatientID
                LEFT JOIN m_title 
                ON M_PatientM_TitleID = M_TitleID
                -- LEFT JOIN m_user ON T_ResultHandoverDetailUserID = M_UserID
                -- LEFT JOIN m_staff ON M_UserM_StaffID = M_StaffID 
                WHERE 
                T_OrderHeaderIsActive = 'Y'
                AND XEmailOutboxID IS NOT NULL 
                AND XEmailOutboxIsSent = 'Y'
                AND T_OrderHeaderMgm_McuID = ?
                ";
        $query = $this->db_onedev->query($sql, [$setupID]);
        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->sys_error($message);
            exit;
        }
        $totalSend = $query->row_array()['totalSend'];
        $sql = "SELECT 
                COUNT(T_OrderHeaderID) as totalError
                FROM t_orderheader
                LEFT JOIN x_email_outbox
                ON T_OrderHeaderID = XEmailOutboxRefID 
                AND XEmailOutboxIsActive = 'Y'
                AND XEmailOutboxType = 'RESULT'
                JOIN m_patient 
                ON T_OrderHeaderM_PatientID = M_PatientID
                LEFT JOIN m_title 
                ON M_PatientM_TitleID = M_TitleID
                -- LEFT JOIN m_user ON T_ResultHandoverDetailUserID = M_UserID
                -- LEFT JOIN m_staff ON M_UserM_StaffID = M_StaffID 
                WHERE 
                T_OrderHeaderIsActive = 'Y'
                AND XEmailOutboxID IS NOT NULL 
                AND XEmailOutboxIsSent = 'E'
                AND T_OrderHeaderMgm_McuID = ?
                ";
        $query = $this->db_onedev->query($sql, [$setupID]);
        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->sys_error($message);
            exit;
        }
        $totalError = $query->row_array()['totalError'];
        $sql = "SELECT 
                COUNT(T_OrderHeaderID) as totalNotSend
                FROM t_orderheader
                LEFT JOIN x_email_outbox
                ON T_OrderHeaderID = XEmailOutboxRefID 
                AND XEmailOutboxIsActive = 'Y'
                AND XEmailOutboxType = 'RESULT'
                JOIN m_patient 
                ON T_OrderHeaderM_PatientID = M_PatientID
                LEFT JOIN m_title 
                ON M_PatientM_TitleID = M_TitleID
                -- LEFT JOIN m_user ON T_ResultHandoverDetailUserID = M_UserID
                -- LEFT JOIN m_staff ON M_UserM_StaffID = M_StaffID 
                WHERE 
                T_OrderHeaderIsActive = 'Y'
                AND (XEmailOutboxID IS NULL OR XEmailOutboxIsSent = 'N')
                AND T_OrderHeaderMgm_McuID = ?
                ";
        $query = $this->db_onedev->query($sql, [$setupID]);
        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->sys_error($message);
            exit;
        }
        $totalNotSend = $query->row_array()['totalNotSend'];
        $sql = "SELECT 
                COUNT(T_OrderHeaderID) as total
                FROM t_orderheader
                LEFT JOIN x_email_outbox
                ON T_OrderHeaderID = XEmailOutboxRefID 
                AND XEmailOutboxIsActive = 'Y'
                AND XEmailOutboxType = 'RESULT'
                JOIN m_patient 
                ON T_OrderHeaderM_PatientID = M_PatientID
                LEFT JOIN m_title 
                ON M_PatientM_TitleID = M_TitleID
                LEFT JOIN corporate ON T_OrderHeaderCorporateID= CorporateID
                -- LEFT JOIN m_user ON T_ResultHandoverDetailUserID = M_UserID
                -- LEFT JOIN m_staff ON M_UserM_StaffID = M_StaffID 
                WHERE 
                T_OrderHeaderIsActive = 'Y'
                {$whereStatus}
                AND DATE(T_OrderHeaderDate) BETWEEN ? AND ?
                AND (M_PatientName LIKE ? OR T_OrderHeaderLabNumber LIKE ?)
                AND T_OrderHeaderMgm_McuID = ?
                ";
        $query = $this->db_onedev->query($sql, [$startDate, $endDate, $search, $search, $setupID]);
        if (!$query) {
            $message = $this->db_onedev->error()['message'];
            // $message['qry'] = $this->db_onedev->last_query();
            $this->sys_error("Error Search");
            exit;
        }
        $total = $query->row_array()['total'];
        // print_r($this->db_onedev->last_query());
        $sql = "SELECT 
                T_OrderHeaderID as orderID,
                T_OrderHeaderLabNumber as orderNumber,
                DATE_FORMAT(T_OrderHeaderDate, '%d-%m-%Y') as orderDate,
                T_OrderHeaderM_PatientID as patientID,
                DATE_FORMAT(M_PatientDOB, '%d%m%Y') as patientDOB,
                M_PatientDOB,
                CONCAT(IF(ISNULL(M_TitleName),'',CONCAT(M_TitleName,'.')),
                        ' ',
                        IFNULL(M_PatientPrefix,''),
                        ' ',
                        M_PatientName,
                        ' ',
                        IFNULL(M_PatientSuffix,''))  as patientName,
                M_PatientEmail as patientEmail,
                M_PatientEmail as patientEmailOld,
                XEmailOutboxID as sendEmailID,
                CorporateName,
                XEmailOutboxIsSent,
                IFNULL(XEmailOutboxRetry , 0) as retry,
                DATE_FORMAT(XEmailOutboxSentDate, '%d-%m-%Y %H:%i') as sentDate,
                XEmailOutboxType as sendEmailType,
                CASE 
                WHEN XEmailOutboxID IS NULL THEN 'NO'
                WHEN XEmailOutboxID IS NOT NULL THEN XEmailOutboxIsSent
                END as status
                FROM t_orderheader
                LEFT JOIN x_email_outbox
                ON T_OrderHeaderID = XEmailOutboxRefID 
                AND XEmailOutboxIsActive = 'Y'
                AND XEmailOutboxType = 'RESULT'
                JOIN m_patient 
                ON T_OrderHeaderM_PatientID = M_PatientID
                LEFT JOIN m_title 
                ON M_PatientM_TitleID = M_TitleID
                LEFT JOIN corporate ON T_OrderHeaderCorporateID = CorporateID
                -- LEFT JOIN m_user ON T_ResultHandoverDetailUserID = M_UserID
                -- LEFT JOIN m_staff ON M_UserM_StaffID = M_StaffID 
                WHERE 
                T_OrderHeaderIsActive = 'Y'
                {$whereStatus}
                AND DATE(T_OrderHeaderDate) BETWEEN ? AND ?
                AND (M_PatientName LIKE ? OR T_OrderHeaderLabNumber LIKE ?)
                AND T_OrderHeaderMgm_McuID = ?
                GROUP BY T_OrderHeaderID
                LIMIT ? OFFSET ? ";
        $query = $this->db_onedev->query($sql, [$startDate, $endDate, $search, $search, $setupID, $ROW_PER_PAGE, $start_offset]);
        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->sys_error("Error search");
            exit;
        }
        $result = [
            "total" => ceil($total / $ROW_PER_PAGE),
            "totalAll" => $totalALL,
            "totalSend" => $totalSend,
            "totalNotSend" => $totalNotSend,
            "totalError" => $totalError,
            "records" => $query->result_array()
        ];
        $this->sys_ok($result);
    }
    function sendEmail()
    { // $this->db_onedev->trans_begin();
        // $this->db_onedev->trans_rollback();
        // $this->db_onedev->trans_commit();
        $this->db_onedev->trans_begin();
        if (!$this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;
        $data = $prm["data"];

        $setupID = $prm["setupID"];

        $userid = $this->sys_user["M_UserID"];

        $date = date("Y-m-d H:i:s");

        if (count($data) == 0) {
            $this->sys_error("Tidak ada yang bisa dikirimkan ");
            exit;
        }
        //belum ada
        $sql = "SELECT * FROM m_emailconfig 
                WHERE M_EmailConfigType = 'R'";
        $query = $this->db_onedev->query($sql, []);
        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->db_onedev->trans_rollback();
            $this->sys_error("Error get config");
            exit;
        }
        $config = $query->row_array();
        $sendCC = json_encode([
            ["name" => $config['M_EmailConfigCc'], "email" => $config['M_EmailConfigCc']]
        ]);

        $sql = "SELECT `Mgm_McuReportHasil`, `Mgm_McuID` FROM `mgm_mcu` WHERE `Mgm_McuID` = ?";
        $query = $this->db_onedev->query($sql, [$setupID]);
        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->db_onedev->trans_rollback();
            $this->sys_error("Error get config");
            exit;
        }
        $reportType = $query->row_array()['Mgm_McuReportHasil'];
        for ($i = 0; $i < count($data); $i++) {
            $e = $data[$i];
            $msg = $config['M_EmailConfigResultFormatAPS'];
            $msg = str_replace('{PASIEN}', $e['patientName'], $msg);
            $msg = str_replace('{TANGGAL}', $e['orderDate'], $msg);
            //update m_patient
            if ($e['patientEmail'] != $e['patientEmailOld']) {
                $sql = "UPDATE m_patient
                    SET  M_PatientEmail = ?,
                    M_PatientLastUpdatedUserID = {$userid},
                    M_PatientLastUpdated = NOW()
                    WHERE M_PatientID = ?
                    ";
                $query = $this->db_onedev->query($sql, [$e['patientEmail'], $e['patientID']]);
                if (!$query) {
                    $message = $this->db_onedev->error();
                    $message['qry'] = $this->db_onedev->last_query();
                    $this->db_onedev->trans_rollback();
                    $this->sys_error("Error update patient");
                    exit;
                }
            }


            $sql = "SELECT XEmailOutboxID
                    FROM x_email_outbox
                    WHERE XEmailOutboxRefID = ?
                    AND XEmailOutboxIsActive = 'Y'
                    AND XEmailOutboxType = 'RESULT'";
            $query = $this->db_onedev->query($sql, [$e['orderID']]);
            if (!$query) {
                $message = $this->db_onedev->error();
                $message['qry'] = $this->db_onedev->last_query();
                $this->db_onedev->trans_rollback();
                $this->sys_error("Error cek outbox");
                exit;
            }
            $penerima = json_encode([
                ["name" => $e['patientName'], "email" => $e['patientEmail']]
            ]);
            $url = "/one-api/tools/listrptpatienttahunanportal/get_list_patient_rpt/";
            if (intval($reportType) == 1) {
                $url = "/one-api/tools/listrptpatienttahunanportal/get_list_patient_rpt/";
                # code...
            } else if ($reportType == 2) {
                // https://cpone.aplikasi.web.id/one-api/tools/listrptpatientportal/get_list_patient_rpt/1424
                $url = "/one-api/tools/listrptpatientportal/get_list_patient_rpt/";
            }
            // https://cpone.aplikasi.web.id/one-api/tools/listrptpatienttahunanportal/get_list_patient_rpt/1424/090101
            $attachment = json_encode([[
                "name" => "Report_Bundling_" . str_replace(" ", "_", $e['patientName']) . ".pdf",
                "url" => "https://" . $this->hostname . $url . $e['orderID'] . '/' . $e['patientDOB']

            ]]);
            $dataCek = $query->result_array();
            if (count($dataCek) == 0) {
                // INSERT
                $sql = "INSERT INTO x_email_outbox(
                        XEmailOutboxSubject,
                        XEmailOutboxRecipients,
                        XEmailOutboxAttachment,
                        XEmailOutboxCc,
                        XEmailOutboxIsHtml,
                        XEmailOutboxBody,
                        XEmailOutboxType,
                        XEmailOutboxRefID,
                        XEmailOutboxCreated)
                        VALUES(?,?,?,?,'Y',?,?,?,NOW())";
                $query = $this->db_onedev->query($sql, [
                    'Hasil Email',
                    $penerima,
                    $attachment,
                    $sendCC,
                    $msg,
                    'RESULT',
                    $e['orderID']
                ]);
                if (!$query) {
                    $message = $this->db_onedev->error();
                    $message['qry'] = $this->db_onedev->last_query();
                    $this->db_onedev->trans_rollback();
                    $this->sys_error("Error insert outbox");
                    exit;
                }
            } else {
                // (validateEmail(props . item . patientEmail) &&
                //     props . item . status === 'N' &&
                //     props . item . retry >= 5) ||
                //     (validateEmail(props . item . patientEmail) &&
                //         props . item . status === 'Y')
                // kirim ulang
                if ($e['status'] == 'Y' || ($e['status'] == 'E' && intval($e['retry']) >= 5)) {
                    $sql = "UPDATE x_email_outbox SET 
                        XEmailOutboxRecipients = ?,
                        XEmailOutboxAttachment = ?,
                        XEmailOutboxBody = ?,
                        XEmailOutboxIsSent = 'R',
                        XEmailOutboxRetry = 0, 
                        XEmailOutboxLastUpdated = NOW(),
                        XEmailOutboxCc = ?
                        WHERE 
                        XEmailOutboxID = ?
                        ";
                    $query = $this->db_onedev->query($sql, [
                        $penerima,
                        $attachment,
                        $msg,
                        $sendCC,
                        $dataCek[0]['XEmailOutboxID']
                    ]);
                    if (!$query) {
                        $message = $this->db_onedev->error();
                        $message['qry'] = $this->db_onedev->last_query();
                        // print_r($message);
                        $this->db_onedev->trans_rollback();
                        $this->sys_error("Error update outbox");
                        exit;
                    }
                }
            }
        }
        // kirim ulang yang Y
        // kirim ulang yang error lebih dari 5

        $this->db_onedev->trans_commit();
        $this->sys_ok('OK');
    }
    function saveHandover()
    {
        // $this->db_onedev->trans_begin();
        // $this->db_onedev->trans_rollback();
        // $this->db_onedev->trans_commit();
        $this->db_onedev->trans_begin();
        if (!$this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;
        $data = $prm["data"];
        $note = $prm["note"];
        $setupID = $prm["setupID"];
        $penerima = $prm["receiver"];
        $userid = $this->sys_user["M_UserID"];
        $handoverID = $prm["handoverID"];
        $date = date("Y-m-d H:i:s");
        if (!isset($prm["receiver"]) || trim($penerima) == "") {
            $this->sys_error("Penerima tidak boleh kosong");
            exit;
        }
        if (count($data) == 0) {
            $this->sys_error("Tidak ada yang bisa diserahkan ");
            exit;
        }
        if (intval($handoverID) == 0) {
            //insert
            $sql = "SELECT fn_numbering('H') as number";
            $query = $this->db_onedev->query($sql, []);
            if (!$query) {
                $message = $this->db_onedev->error();
                $message['qry'] = $this->db_onedev->last_query();
                $this->db_onedev->trans_rollback();
                $this->sys_error($message);
                exit;
            }
            $number = $query->row_array()['number'];
            $sql = "INSERT INTO t_resulthandover
                    (T_ResultHandoverCode,
                    T_ResultHandoverDate,
                    T_ResultHandoverReceivedBy,
                    T_ResultHandoverNote,
                    T_ResultHandoverMgm_McuID,
                    T_ResultHandoverUserID,
                    T_ResultHandoverCreated,
                    T_ResultHandoverCreatedUserID)
                    VALUES(?,'{$date}',?,?,?,?,'{$date}',?)";
            $query = $this->db_onedev->query($sql, [$number, $penerima, $note, $setupID, $userid, $userid]);
            if (!$query) {
                $message = $this->db_onedev->error();
                $message['qry'] = $this->db_onedev->last_query();
                $this->db_onedev->trans_rollback();
                $this->sys_error($message);
                exit;
            }
            $handoverID = $this->db_onedev->insert_id();
        }

        for ($i = 0; $i < count($data); $i++) {
            $e = $data[$i];
            $sql = "INSERT INTO t_resulthandoverdetail (
                    T_ResultHandoverDetailT_ResultHandoverID,
                    T_ResultHandoverDetailT_OrderheaderID,
                    T_ResultHandoverDetailReceivedBy,
                    T_ResultHandoverDetailUserID,
                    T_ResultHandoverDetailCreated,
                    T_ResultHandoverDetailCreatedUserID)
                    VALUES(?,?,?,?,'{$date}',?)";
            $query = $this->db_onedev->query($sql, [$handoverID, $e['orderID'], $penerima, $userid, $userid]);
            if (!$query) {
                $message = $this->db_onedev->error();
                $message['qry'] = $this->db_onedev->last_query();
                $this->db_onedev->trans_rollback();
                $this->sys_error($message);
                exit;
            }
        }
        $this->db_onedev->trans_commit();
        $this->sys_ok("OK");
    }
    function deletehandoverdetail()
    {
        // $this->db_onedev->trans_begin();
        // $this->db_onedev->trans_rollback();
        // $this->db_onedev->trans_commit();
        $this->db_onedev->trans_begin();
        if (!$this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;
        $detailID = $prm["detailID"];
        $userid = $this->sys_user["M_UserID"];



        $sql = "UPDATE t_resulthandoverdetail
                SET T_ResultHandoverDetailIsActive = 'N',
                T_ResultHandoverDetailDeleted = NOW(),
                T_ResultHandoverDetailDeletedUserID = '{$userid}'
                WHERE T_ResultHandoverDetailID = ?
                ";
        $query = $this->db_onedev->query($sql, [$detailID]);
        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->db_onedev->trans_rollback();
            $this->sys_error($message);
            exit;
        }

        $this->db_onedev->trans_commit();
        $this->sys_ok("OK");
    }
    function searchpriceheader()
    {
        if (!$this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }

        $prm = $this->sys_input;
        $search = '%' . $prm['search'] . '%';
        $page = $prm["page"];
        $ROW_PER_PAGE = 20;
        $start_offset = 0;
        // print_r($prm);

        if (isset($prm["page"])) {
            if (
                is_numeric($prm["page"]) && $prm["page"] > 0
            ) {
                $start_offset = ($page - 1) * $ROW_PER_PAGE;
            }
        }
        $sql = "SELECT 
                count(T_PriceHeaderID ) as total
                FROM t_priceheader
                WHERE T_PriceHeaderName LIKE '{$search}'
                AND T_PriceHeaderIsActive = 'Y'
                 ";
        $query = $this->db_onedev->query($sql, []);
        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->sys_error($message);
            exit;
        }
        $total = $query->row_array()['total'];
        $sql = "SELECT 
                T_PriceHeaderID AS headerID,
                T_PriceHeaderName AS headerName,
                T_PriceHeaderStartDate AS headerStartDate,
                T_PriceHeaderEndDate AS headerEndDate,
                T_PriceHeaderCode AS headerCode,
                T_PriceHeaderValidasi AS headerValidate,
                CASE 
                    WHEN `fn_get_count_price_notgenerate`(T_PriceHeaderID) > 0 THEN 'Y'
                    ELSE 'N'
                END AS readyValidate,
                CASE 
                    WHEN `fn_get_status_validate_packet`(T_PriceHeaderID) > 0 THEN 'Y'
                    ELSE 'N'
                END AS validatePacket
                FROM t_priceheader
                WHERE T_PriceHeaderName LIKE '{$search}'
                AND T_PriceHeaderIsActive = 'Y'
                LIMIT ? OFFSET ? ";
        $query = $this->db_onedev->query($sql, [$ROW_PER_PAGE, $start_offset]);
        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->sys_error($message);
            exit;
        }
        $result = [
            "total" => ceil($total / $ROW_PER_PAGE),
            "records" => $query->result_array()
        ];
        $this->sys_ok($result);
    }
    function insertpriceheader()
    {
        if (!$this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }

        $prm = $this->sys_input;
        $userid = $this->sys_user["M_UserID"];
        if (!isset($prm['name'])) {
            $this->sys_error("Nama harus diisi");
            exit;
        }
        if (trim($prm['name']) == "") {
            $this->sys_error("Nama harus diisi");
            exit;
        }
        // if (!isset($prm['sd'])) {
        //     $this->sys_error("Tanggal awal harus diisi");
        //     exit;
        // }
        // if (!isset($prm['ed'])) {
        //     $this->sys_error("Tanggal akhir harus diisi");
        //     exit;
        // }
        $name = $prm['name'];
        $startDate = $prm['sd'];
        $endDate = $prm['ed'];
        $nw = strtotime($startDate);
        $nwe = strtotime($endDate);

        $sql = "SELECT fn_numbering('PH') as number";
        $query = $this->db_onedev->query($sql, []);
        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->sys_error($message);
            exit;
        }
        $number = $query->row_array()['number'];

        $sql = "INSERT INTO t_priceheader (
                T_PriceHeaderName,
                T_PriceHeaderCreated,
                T_PriceHeaderCraetdUserID,
                T_PriceHeaderCode)
                VALUES (?,NOW(),?,?)";
        $query = $this->db_onedev->query($sql, [$name, $userid, $number]);
        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->sys_error($message);
            exit;
        }


        // "total" => ceil($total / $ROW_PER_PAGE),

        $this->sys_ok("OK");
    }
    function editpriceheader()
    {
        if (!$this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }

        $prm = $this->sys_input;
        $userid = $this->sys_user["M_UserID"];
        if (!isset($prm['name'])) {
            $this->sys_error("Nama harus diisi");
            exit;
        }
        if (trim($prm['name']) == "") {
            $this->sys_error("Nama harus diisi");
            exit;
        }
        // if (!isset($prm['sd'])) {
        //     $this->sys_error("Tanggal awal harus diisi");
        //     exit;
        // }
        // if (!isset($prm['ed'])) {
        //     $this->sys_error("Tanggal akhir harus diisi");
        //     exit;
        // }
        $name = $prm['name'];
        $startDate = $prm['sd'];
        $endDate = $prm['ed'];
        $id = $prm['id'];
        $nw = strtotime($startDate);
        $nwe = strtotime($endDate);

        $sql = "UPDATE t_priceheader 
                SET T_PriceHeaderName = '{$name}',
                T_PriceHeaderLastUpdated = NOW(),
                T_PriceHeaderLastUpdatedUserID = {$userid}
                WHERE T_PriceHeaderID = $id";
        $query = $this->db_onedev->query($sql, []);
        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->sys_error($message);
            exit;
        }


        // "total" => ceil($total / $ROW_PER_PAGE),

        $this->sys_ok("OK");
    }
    function deletepriceheader()
    {
        if (!$this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }

        $prm = $this->sys_input;
        $userid = $this->sys_user["M_UserID"];
        $id = $prm['id'];

        $sql = "UPDATE t_priceheader 
                SET T_PriceHeaderIsActive = 'N',
                T_PriceHeaderDeleted = NOW(),
                T_PriceHeaderDeletedUserID = {$userid}
                WHERE T_PriceHeaderID = $id";
        $query = $this->db_onedev->query($sql, []);
        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->sys_error($message);
            exit;
        }
        $sql = "UPDATE t_price 
                SET T_PriceIsActive = 'N',
                T_PriceLastUpdated = NOW(),
                T_PriceUserID = {$userid}
                WHERE T_PriceT_PriceHeaderID = $id";
        $query = $this->db_onedev->query($sql, []);
        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->sys_error($message);
            exit;
        }


        // "total" => ceil($total / $ROW_PER_PAGE),

        $this->sys_ok("OK");
    }
    function getfilterprice()
    {
        if (!$this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }



        $sql = "SELECT Nat_SubGroupID AS id, Nat_SubGroupName AS name
                FROM nat_subgroup 
                WHERE Nat_SubGroupIsActive= 'Y'";
        $query = $this->db_onedev->query($sql, []);
        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->sys_error($message);
            exit;
        }
        $subgroup = $query->result_array();
        array_push($subgroup, [
            'id' => '0',
            'name' => 'Semua'
        ]);

        $status = [[
            'id' => 'A',
            'name' => 'Semua'
        ], [
            'id' => 'Y',
            'name' => 'Ya'
        ], [
            'id' => 'N',
            'name' => 'Tidak'
        ]];


        // "total" => ceil($total / $ROW_PER_PAGE),
        $rst = [
            "subgroup" => $subgroup,
            'status' => $status,
        ];

        $this->sys_ok($rst);
    }
    function searchpricetest()
    {
        if (!$this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }

        $prm = $this->sys_input;
        $search = '%' . $prm['search'] . '%';
        $subgroup =  $prm['subgroup'];
        $headerID =  $prm['headerid'];
        $status =  $prm['status'];
        $page = $prm["page"];
        $ROW_PER_PAGE = 20;
        $start_offset = 0;
        // print_r($prm);
        $filterSubGroup = "";
        if ($subgroup != '0') {
            $filterSubGroup = "AND Nat_SubGroupID = $subgroup";
        }
        $filterStatus = "";
        if ($status == 'N') {
            $filterStatus = 'AND T_PriceID IS NULL ';
        }
        if ($status == 'Y') {
            $filterStatus = 'AND T_PriceID IS NOT NULL ';
        }

        if (isset($prm["page"])) {
            if (
                is_numeric($prm["page"]) && $prm["page"] > 0
            ) {
                $start_offset = ($page - 1) * $ROW_PER_PAGE;
            }
        }
        $sql = "SELECT 
                COUNT(T_TestID) as total
                FROM 
                t_test 
                JOIN nat_subgroup 
                ON T_TestNat_SubgroupID = Nat_SubGroupID
                AND Nat_SubGroupIsActive = 'Y'
                AND `T_TestIsPrice` = 'Y' 
                AND (T_TestName LIKE ? OR T_TestSasCode LIKE ?)
                {$filterSubGroup}
                LEFT JOIN t_price
                ON T_TestID = T_PriceT_TestID
                AND T_PriceT_PriceHeaderID = ?
                AND T_PriceIsActive = 'Y'
                LEFT JOIN t_priceheader
                ON T_PriceT_PriceHeaderID = T_PriceHeaderID
                AND T_PriceHeaderIsActive = 'Y'
                WHERE 
                `T_TestIsActive` = 'Y' 
                {$filterStatus}
                 ";
        $query = $this->db_onedev->query($sql, [$search, $search, $headerID,]);
        $qryTotal = $this->db_onedev->last_query();
        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->sys_error($message);
            exit;
        }
        $total = $query->row_array()['total'];
        $sql = "SELECT 
                T_TestID AS testID,
                T_TestSasCode AS testCode,
                T_TestName AS testName,
                T_TestIsPrice AS testIsPrice,
                T_PriceAmount AS priceAmount,
                T_PriceDisc AS priceDisc,
                T_PriceDiscRp AS priceDiscRp,
                T_PriceSubTotal AS subTotal,
                T_PriceID AS priceID, 
                T_PriceT_PriceHeaderID AS headerID,
                T_PriceHeaderName AS headerName,
                Nat_SubGroupID AS subGroupID,
                Nat_SubGroupName AS subGroupName,
                'N' AS isChange, 
                CASE 
                WHEN T_PriceID IS NULL THEN 'N'
                WHEN T_PriceID IS NOT NULL THEN 'Y'
                ELSE 'N'
                END as status
                FROM 
                t_test 
                JOIN nat_subgroup 
                ON T_TestNat_SubgroupID = Nat_SubGroupID
                AND Nat_SubGroupIsActive = 'Y'
                AND `T_TestIsPrice` = 'Y' 
                AND (T_TestName LIKE ? OR T_TestSasCode LIKE ?)
                {$filterSubGroup}
                LEFT JOIN t_price
                ON T_TestID = T_PriceT_TestID
                AND T_PriceT_PriceHeaderID = ?
                AND T_PriceIsActive = 'Y'
                LEFT JOIN t_priceheader
                ON T_PriceT_PriceHeaderID = T_PriceHeaderID
                AND T_PriceHeaderIsActive = 'Y'
                WHERE 
                `T_TestIsActive` = 'Y' 
                {$filterStatus}
                LIMIT ? OFFSET ? ";
        $query = $this->db_onedev->query($sql, [$search, $search, $headerID, $ROW_PER_PAGE, $start_offset]);
        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->sys_error($message);
            exit;
        }
        $result = [
            "total" => ceil($total / $ROW_PER_PAGE),
            "ttl" => $total,
            "records" => $query->result_array(),
            "qry" => $this->db_onedev->last_query(),
            "qryttl" => $qryTotal,
        ];
        $this->sys_ok($result);
    }
    function savetest()
    {
        if (!$this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $userid = $this->sys_user["M_UserID"];
        // {
        //     "testID": "19",
        //     "testCode": "10110200",
        //     "testName": "Hematologi Rutin",
        //     "testIsPrice": "Y",
        //     "priceAmount": "10000",
        //     "priceDisc": null,
        //     "priceDiscRp": null,
        //     "subTotal": 10000,
        //     "priceID": null,
        //     "headerID": null,
        //     "headerName": null,
        //     "subGroupID": "1",
        //     "subGroupName": "Hematologi",
        //     "isChange": "Y",
        //     "status": "N"
        // }
        $prm = $this->sys_input;
        $test = $prm['test'];
        $headerid = $prm['headerid'];
        for ($i = 0; $i < count($test); $i++) {
            $data = $test[$i];
            $diskon = 0;
            if ($data['priceDisc'] != null) {
                $diskon = $data['priceDisc'];
            }
            $diskonRp = 0;
            if ($data['priceDiscRp'] != null) {
                $diskonRp = $data['priceDiscRp'];
            }
            if ($data['priceID'] === null || $data['priceID'] === '0') {

                $sql = "INSERT INTO t_price 
                        (T_PriceT_PriceHeaderID,
                        T_PriceT_TestID,
                        T_PriceAmount,
                        T_PriceDisc,
                        T_PriceDiscRp,
                        T_PriceSubTotal,
                        T_PriceTotal,
                        T_PriceCreated,
                        T_PriceUserID
                        )
                        VALUES(?,?,?,?,?,?,?,NOW(),?)";
                $query = $this->db_onedev->query($sql, [
                    $headerid,
                    $data['testID'],
                    $data['priceAmount'],
                    $diskon,
                    $diskonRp,
                    $data['subTotal'],
                    $data['subTotal'],
                    $userid,
                ]);
                if (!$query) {
                    $message = $this->db_onedev->error();
                    $message['qry'] = $this->db_onedev->last_query();
                    $this->sys_error($message);
                    exit;
                }
            } else {
                $sql = "UPDATE t_price
                    SET T_PriceAmount = ?,
                    T_PriceDisc = ?,
                    T_PriceDiscRp = ?,
                    T_PriceSubTotal = ?,
                    T_PriceTotal = ?,
                    T_PriceLastUpdated = NOW(),
                    T_PriceUserID = ?
                    WHERE T_PriceT_TestID = ?
                    AND T_PriceID = ?
                    AND T_PriceT_PriceHeaderID = ?";
                $query = $this->db_onedev->query($sql, [
                    $data['priceAmount'],
                    $diskon,
                    $diskonRp,
                    $data['subTotal'],
                    $data['subTotal'],
                    $userid,
                    $data['testID'],
                    $data['priceID'],
                    $headerid,
                ]);
                if (!$query) {
                    $message = $this->db_onedev->error();
                    $message['qry'] = $this->db_onedev->last_query();
                    $this->sys_error($message);
                    exit;
                }
            }
        }
        $this->sys_ok(["last_qry" => $this->db_onedev->last_query()]);
    }
    function searchpriceheaderautocomplete()
    {
        if (!$this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }

        $prm = $this->sys_input;
        $search = '%' . $prm['search'] . '%';

        $sql = "SELECT 
                T_PriceHeaderID AS headerID,
                CONCAT(T_PriceHeaderCode, ' ', T_PriceHeaderName) AS headerName,
                T_PriceHeaderStartDate AS headerStartDate,
                T_PriceHeaderEndDate AS headerEndDate,
                T_PriceHeaderCode AS headerCode,
                T_PriceHeaderValidasi AS headerValidate,
                CASE 
                    WHEN `fn_get_count_price`(T_PriceHeaderID) > 0 THEN 'Y'
                    ELSE 'N'
                END AS readyValidate
                FROM t_priceheader
                WHERE CONCAT(T_PriceHeaderCode, ' ', T_PriceHeaderName) LIKE '{$search}' 
                AND fn_get_count_price(T_PriceHeaderID) > 0
                AND T_PriceHeaderIsActive = 'Y'
                ";
        $query = $this->db_onedev->query($sql, []);
        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->sys_error($message);
            exit;
        }
        $result = [
            "records" => $query->result_array()
        ];
        $this->sys_ok($result);
    }
    function copyharga()
    {
        if (!$this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        // $this->db_onedev->trans_begin();
        // $this->db_onedev->trans_rollback();
        // $this->db_onedev->trans_commit();
        $this->db_onedev->trans_begin();
        $prm = $this->sys_input;
        $headerid = $prm['headerid'];
        $name = $prm['name'];
        $copypacket = $prm['copypacket'];
        $userid = $this->sys_user["M_UserID"];
        $sql = "SELECT fn_numbering('PH') as number";
        $query = $this->db_onedev->query($sql, []);
        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->sys_error($message);
            exit;
        }
        $numberHeader = $query->row_array()['number'];

        //insert price header
        $sql = "INSERT INTO t_priceheader (
                T_PriceHeaderName,
                T_PriceHeaderCreated,
                T_PriceHeaderCraetdUserID,
                T_PriceHeaderCode)
                VALUES(?,NOW(),?,?)";
        $query = $this->db_onedev->query($sql, [$name, $userid, $numberHeader]);
        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->db_onedev->trans_rollback();
            $this->sys_error($message);
            exit;
        }
        $newHeaderid = $this->db_onedev->insert_id();

        $sql = "INSERT INTO t_price 
                ( T_PriceT_PriceHeaderID,
                T_PriceT_TestID,
                T_PriceAmount,
                T_PriceDisc,
                T_PriceDiscRp,
                T_PriceSubTotal,
                T_PriceTotal,
                T_PriceCreated,
                T_PriceUserID)
                SELECT 
                '{$newHeaderid}' AS headerid,
                T_PriceT_TestID,
                T_PriceAmount,
                T_PriceDisc,
                T_PriceDiscRp,
                T_PriceSubTotal,
                T_PriceTotal,
                NOW() as created,
                '{$userid}' as userID
                FROM t_price 
                WHERE T_PriceT_PriceHeaderID = ?
                AND T_PriceIsActive = 'Y'
                ";
        $query = $this->db_onedev->query($sql, [$headerid]);

        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->db_onedev->trans_rollback();
            $this->sys_error($message);
            exit;
        }
        // echo $this->db_onedev->last_query();
        // exit;
        if ($copypacket) {
            $sql = 'SELECT * FROM t_packet WHERE T_PacketT_PriceHeaderID = ? AND T_PacketIsActive = "Y"';
            $query = $this->db_onedev->query($sql, [$headerid]);
            if (!$query) {
                $message = $this->db_onedev->error();
                $message['qry'] = $this->db_onedev->last_query();
                $this->db_onedev->trans_rollback();
                $this->sys_error($message);
                exit;
            }
            $pcktlst = $query->result_array();
            for ($i = 0; $i < count($pcktlst); $i++) {
                $p = $pcktlst[$i];
                $name = $p['T_PacketName'] . " " . $numberHeader;
                $oldPHid = $p['T_PacketID'];
                $sql = "SELECT fn_numbering('PC') as number";
                $query = $this->db_onedev->query($sql, []);
                if (!$query) {
                    $message = $this->db_onedev->error();
                    $message['qry'] = $this->db_onedev->last_query();
                    $this->db_onedev->trans_rollback();
                    $this->sys_error($message);
                    exit;
                }
                $number = $query->row_array()['number'];

                $sql = "INSERT INTO t_packet
                        (T_PacketT_PriceHeaderID,
                        T_PacketType,
                        T_PacketName,
                        T_PacketPrice,
                        T_PacketOriginalPrice,
                        T_PacketSasCode,
                        T_PacketStartDate,
                        T_PacketEndDate,
                        T_PacketCreated,
                        T_PacketOriginalBruto)
                        VALUES(?,?,?,?,?,?,?,?,NOW(),?)";
                $query = $this->db_onedev->query($sql, [
                    $newHeaderid,
                    $p['T_PacketType'],
                    $name,
                    $p['T_PacketPrice'],
                    $p['T_PacketOriginalPrice'],
                    $number,
                    $p['T_PacketStartDate'],
                    $p['T_PacketEndDate'],
                    $p['T_PacketOriginalBruto'],
                ]);
                if (!$query) {
                    $message = $this->db_onedev->error();
                    $message['qry'] = $this->db_onedev->last_query();
                    $this->db_onedev->trans_rollback();
                    $this->sys_error($message);
                    exit;
                }
                $newPHid = $this->db_onedev->insert_id();

                $sql = "INSERT INTO t_packetdetail 
                        (T_PacketDetailT_PacketID,
                        T_PacketDetailT_TestID,
                        T_PacketDetailOriginalPrice,
                        T_PacketDetailPrice,
                        T_PacketDetailCreated,
                        T_PacketDetailPriceAmount,
                        T_PacketDetailPriceDisc,
                        T_PacketDetailPriceDiscRp,
                        T_PacketDetailPriceSubTotal)
                        SELECT 
                        '{$newPHid}' as pid,
                        T_PacketDetailT_TestID,
                        T_PacketDetailOriginalPrice,
                        T_PacketDetailPrice,
                        NOW() as created,
                        T_PacketDetailPriceAmount,
                        T_PacketDetailPriceDisc,
                        T_PacketDetailPriceDiscRp,
                        T_PacketDetailPriceSubTotal
                        FROM t_packetdetail  
                        WHERE T_PacketDetailT_PacketID = ?
                        AND T_PacketDetailIsActive = 'Y'";
                $query = $this->db_onedev->query($sql, [
                    $oldPHid
                ]);
                if (!$query) {
                    $message = $this->db_onedev->error();
                    $message['qry'] = $this->db_onedev->last_query();
                    $this->db_onedev->trans_rollback();
                    $this->sys_error($message);
                    exit;
                }
            }
        }
        $this->db_onedev->trans_commit();

        // $result = [
        //     "records" => $query->result_array()
        // ];
        $this->sys_ok('OK');
    }
    function create($id)
    {
        $this->load->library("SsPriceMou");
        list($status, $message)  = $this->sspricemou->create($id);
        if ($status) {
            return [
                "status" => 'OK',
                "msg" => $message
            ];
        } else {
            return [
                "status" => 'OK',
                "msg" => $message
            ];
        }
    }
    function validateheader()
    {
        if (!$this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        // $this->db_onedev->trans_begin();
        // $this->db_onedev->trans_rollback();
        // $this->db_onedev->trans_commit();
        $this->db_onedev->trans_begin();

        $prm = $this->sys_input;
        $userid = $this->sys_user["M_UserID"];

        $id = $prm['id'];

        $sql = "UPDATE t_priceheader 
                SET T_PriceHeaderValidasi = 'Y',
                T_PriceHeaderValidasiDate = NOW(),
                T_PriceHeaderValidasiUserID = {$userid},
                T_PriceHeaderIsGenerated = 'Y',
                T_PriceHeaderGeneratedDate = NOW()
                WHERE T_PriceHeaderID = $id";
        $query = $this->db_onedev->query($sql, []);
        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->db_onedev->trans_rollback();
            $this->sys_error($message);
            exit;
        }
        $sql = "UPDATE t_price 
                SET T_PriceValidasi = 'Y',
                T_PriceValidasiDate = NOW(),
                T_PriceValidasiDateUserID = {$userid},
                T_PriceIsGenerated = 'Y',
                T_PriceIsGeneratedDate = NOW()
                WHERE T_PriceT_PriceHeaderID = $id
                AND T_PriceValidasi = 'N'";
        $query = $this->db_onedev->query($sql, []);
        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->db_onedev->trans_rollback();
            $this->sys_error($message);
            exit;
        }

        $sql = "UPDATE t_packet 
                SET T_PacketIsGenerated = 'Y',
                T_PacketGeneratedDate = NOW()
                WHERE T_PacketT_PriceHeaderID = $id
                AND T_PacketIsGenerated= 'N'";
        $query = $this->db_onedev->query($sql, []);
        if (!$query) {
            $message = $this->db_onedev->error();
            $message['qry'] = $this->db_onedev->last_query();
            $this->db_onedev->trans_rollback();
            $this->sys_error($message);
            exit;
        }

        $rtn = $this->create($id);
        if ($rtn['status'] !== 'OK') {
            $message = $rtn['msg'];
            $message['qry'] = $this->db_onedev->last_query();
            $this->db_onedev->trans_rollback();
            $this->sys_error($message);
            exit;
        }


        // "total" => ceil($total / $ROW_PER_PAGE),
        $this->db_onedev->trans_commit();
        $this->sys_ok([
            "msg" => "OK",
            "generate" => $rtn
        ]);
    }
}