<?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;
            }
            $prm = $this->sys_input;
            $search = "%" . $prm["search"] . "%";
            $sql = "SELECT * ,
                    CONCAT(Mgm_McuNumber,' - ', Mgm_McuLabel) display
					FROM mgm_mcu 
					WHERE Mgm_McuIsActive = 'Y'
                    AND CONCAT(Mgm_McuNumber,' - ', Mgm_McuLabel) LIKE ?
					ORDER BY Mgm_McuStartDate ASC, Mgm_McuEndDate ASC
                    LIMIT 100";
            $qry = $this->db_onedev->query($sql, [$search]);
            $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 * ,
                CONCAT(Mgm_McuNumber,' - ', Mgm_McuLabel) display
                FROM mgm_mcu 
                LEFT JOIN mgm_mcuemailresult
                ON Mgm_McuID = Mgm_McuEmailResultMgm_McuID
                AND Mgm_McuEmailResultIsActive = 'Y'
                WHERE Mgm_McuIsActive = 'Y'
                AND Mgm_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());
        $dataSetup = $query->row_array();

        $sql = "SELECT COUNT(total) as totalAll FROM (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 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 = ?
                GROUP BY T_OrderHeaderID)x
                ";
        $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
                JOIN m_patient 
                ON T_OrderHeaderM_PatientID = M_PatientID
                LEFT JOIN x_email_outbox
                ON T_OrderHeaderID = XEmailOutboxRefID 
                AND XEmailOutboxIsActive = 'Y'
                AND XEmailOutboxType = 'RESULT'
                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'
                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,
                CorporateEmail,
                GROUP_CONCAT(
                CONCAT(XEmailOutboxID,'/', XEmailOutboxRecepientType, '/', XEmailOutboxIsSent, '/', XEmailOutboxRetry, '/',IFNULL(DATE_FORMAT(XEmailOutboxSentDate, '%d-%m-%Y %H:%i'),''),'/',
                    CASE 
                        WHEN XEmailOutboxRecepientType = 'P' THEN M_PatientEmail
                        WHEN XEmailOutboxRecepientType  = 'C' THEN CorporateEmail
                        ELSE''
                    END)
                SEPARATOR '|'
                ) as arrEmail
                FROM t_orderheader
                JOIN m_patient 
                ON T_OrderHeaderM_PatientID = M_PatientID
                LEFT JOIN x_email_outbox
                ON T_OrderHeaderID = XEmailOutboxRefID 
                AND XEmailOutboxIsActive = 'Y'
                AND XEmailOutboxType = 'RESULT'
                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'
                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;
        }
        $rst = $query->result_array();
        $selectedPatient =  false;
        $selectedCompany = false;
        $dsiablePatient = false;
        $dsiableCompany = false;
        if ($dataSetup['Mgm_McuEmailResultIsPatientEmail'] == 'P') {
            $selectedPatient =  true;
            $selectedCompany = false;
            $dsiablePatient = false;
            $dsiableCompany = true;
        }
        if ($dataSetup['Mgm_McuEmailResultIsPatientEmail'] == 'C') {
            $selectedPatient =  false;
            $selectedCompany = true;
            $dsiablePatient = true;
            $dsiableCompany = false;
        }
        if ($dataSetup['Mgm_McuEmailResultIsPatientEmail'] == 'X') {
            $selectedPatient =  true;
            $selectedCompany = true;
            $dsiablePatient = false;
            $dsiableCompany = false;
        }
        $cekMasuk = "";
        foreach ($rst as $key => $v) {
            $strArrEmail = $v['arrEmail'];
            $defaultEmailCompany = [
                "sendEmailID" => 0,
                "sendEmailType" => 'C',
                "sendEmailIsSent" => 'NO',
                "sendEmailIsRetry" => 0,
                "sendEmailDate" => '',
                "sendEmailReceiver" => $v['CorporateEmail'],
                "default" => 'Y',
                "selected" => $selectedCompany,
                "disable" => $selectedCompany
            ];
            $defaultEmailPatient = [
                "sendEmailID" => 0,
                "sendEmailType" => 'P',
                "sendEmailIsSent" => 'NO',
                "sendEmailIsRetry" => 0,
                "sendEmailDate" => '',
                "sendEmailReceiver" => $v['patientEmail'],
                "default" => 'Y',
                "selected" => $selectedPatient,
                "disable" => $dsiablePatient
            ];
            if ($strArrEmail != null) {
                $cekMasuk = "";
                $arrEmail = explode("|", $strArrEmail);
                if (count($arrEmail) == 1) {
                    $cekMasuk = "arrEMail 1";
                    $arrData = explode('/', $arrEmail[0]);
                    $email = [
                        "sendEmailID" => $arrData[0],
                        "sendEmailType" => $arrData[1],
                        "sendEmailIsSent" => $arrData[2],
                        "sendEmailIsRetry" => $arrData[3],
                        "sendEmailDate" => $arrData[4],
                        "sendEmailReceiver" => $arrData[5],
                        "selected" => $arrData[1] == 'P' ? $selectedPatient : ($arrData[1] == 'C' ? $selectedCompany : false),
                        "disable" => $arrData[1] == 'P' ? $dsiablePatient : ($arrData[1] == 'C' ? $dsiableCompany : false)
                    ];
                    $rst[$key]['dataEmailPatient'] = $arrData[1] == 'P' ? $email : $defaultEmailPatient;
                    $rst[$key]['dataEmailCompany'] = $arrData[1] == 'C' ? $email : $defaultEmailCompany;
                } else if (count($arrEmail) == 2) {
                    $cekMasuk = "arrEMail 2";

                    $arrData1 = explode('/', $arrEmail[0]);
                    $arrData2 = explode('/', $arrEmail[1]);
                    $email1 = [
                        "sendEmailID" => $arrData1[0],
                        "sendEmailType" => $arrData1[1],
                        "sendEmailIsSent" => $arrData1[2],
                        "sendEmailIsRetry" => $arrData1[3],
                        "sendEmailDate" => $arrData1[4],
                        "sendEmailReceiver" => $arrData1[5],
                        "selected" => $arrData1[1] == 'P' ? $selectedPatient : ($arrData1[1] == 'C' ? $selectedCompany : false),
                        "disable" => $arrData1[1] == 'P' ? $dsiablePatient : ($arrData1[1] == 'C' ? $dsiableCompany : false),
                        "em" => "1"
                    ];
                    $email2 = [
                        "sendEmailID" => $arrData2[0],
                        "sendEmailType" => $arrData2[1],
                        "sendEmailIsSent" => $arrData2[2],
                        "sendEmailIsRetry" => $arrData2[3],
                        "sendEmailDate" => $arrData2[4],
                        "sendEmailReceiver" => $arrData2[5],
                        "selected" => $arrData2[1] == 'P' ? $selectedPatient : ($arrData2[1] == 'C' ? $selectedCompany : false),
                        "disable" => $arrData2[1] == 'P' ? $dsiablePatient : ($arrData2[1] == 'C' ? $dsiableCompany : false),
                        "em" => "1"

                    ];
                    if ($arrData1[1] === 'P' && $arrData2[1] === 'C') {
                        $rst[$key]['dataEmailPatient'] = $email1;
                        $rst[$key]['dataEmailCompany'] = $email2;
                    } elseif ($arrData1[1] === 'C' && $arrData2[1] === 'P') {
                        $rst[$key]['dataEmailPatient'] = $email2;
                        $rst[$key]['dataEmailCompany'] = $email1;
                    } else {
                        $rst[$key]['dataEmailPatient'] = $defaultEmailPatient;
                        $rst[$key]['dataEmailCompany'] = $defaultEmailCompany;
                    }

                    // $rst[$key]['dataEmailPatient'] = $arrData1[1] == "P" ? $email1 : $email2;
                    // $rst[$key]['dataEmailCompany'] = $arrData1[1] == "C" ? $email1 : $email2;
                } else if (count($arrEmail) > 2) {
                    $cekMasuk = "arrEMail lebih dari 2";

                    $rst[$key]['dataEmailPatient'] = $defaultEmailPatient;
                    $rst[$key]['dataEmailCompany'] = $defaultEmailCompany;
                } else {
                    $cekMasuk = "default tapi tdk sama dgn null";

                    $rst[$key]['dataEmailPatient'] = $defaultEmailPatient;
                    $rst[$key]['dataEmailCompany'] = $defaultEmailCompany;
                }
            } else {
                $cekMasuk = "default karena null";
                $rst[$key]['dataEmailPatient'] = $defaultEmailPatient;
                $rst[$key]['dataEmailCompany'] = $defaultEmailCompany;
            }
            $rst[$key]['cek'] = $cekMasuk;

            # code...
        }
        $result = [
            "total" => ceil($total / $ROW_PER_PAGE),
            "totalAll" => $totalALL,
            "totalSend" => $totalSend,
            "totalNotSend" => $totalNotSend,
            "totalError" => $totalError,
            "records" => $rst,
            "cek" => $cekMasuk,
            "qry" => $this->db_onedev->last_query()
        ];
        $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'];
            $msgCompany = $config['M_EmailConfigResultFormatAPS'];
            $msg = str_replace('{PASIEN}', $e['patientName'], $msg);
            $msgCompany = str_replace('{PASIEN}', $e['CorporateName'], $msgCompany);
            $msg = str_replace('{TANGGAL}', $e['orderDate'], $msg);
            $msgCompany = str_replace('{TANGGAL}', $e['orderDate'], $msgCompany);
            //update m_patient
            if ($e['dataEmailPatient']['sendEmailReceiver'] != $e['patientEmailOld'] && $e['dataEmailPatient']['selected']) {
                $sql = "SELECT * FROM m_patient WHERE M_PatientID = ?";
                $query = $this->db_onedev->query($sql, [$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;
                }
                $patientBefore = $query->row_array();
                $sql = "UPDATE m_patient
                    SET  M_PatientEmail = ?,
                    M_PatientLastUpdatedUserID = {$userid},
                    M_PatientLastUpdated = NOW()
                    WHERE M_PatientID = ?
                    ";
                $query = $this->db_onedev->query($sql, [$e['dataEmailPatient']['sendEmailReceiver'], $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 * FROM m_patient WHERE M_PatientID = ?";
                $query = $this->db_onedev->query($sql, [$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;
                }
                $patientAfter = $query->row_array();

                $sql = "INSERT INTO cpone_log.log_patient(
				Log_PatientM_PatientID,
				Log_PatientDate,
				Log_PatientCode,
				Log_PatientJsonBefore,
				Log_PatientJsonAfter,
				Log_PatientUserID)
				VALUES (?, NOW(), 'EDIT', ?, ?, ?)";
                $qry = $this->db_onedev->query($sql, [
                    $e['patientID'],
                    json_encode($patientBefore),
                    json_encode($patientAfter),
                    $userid
                ]);
                if (!$qry) {
                    $message = $this->db_onedev->error();
                    $message['qry'] = $this->db_onedev->last_query();
                    $this->db_onedev->trans_rollback();
                    $this->sys_error("Error update patient");
                    exit;
                }
            }



            $penerima = json_encode([
                ["name" => $e['patientName'], "email" => $e['dataEmailPatient']['sendEmailReceiver']]
            ]);
            $penerimaCompany = json_encode([
                ["name" => $e['CorporateName'], "email" => $e['dataEmailCompany']['sendEmailReceiver']]
            ]);
            /*$url = "/one-api/tools/listrptpatienttahunanportal/get_list_patient_rpt/";
            if (intval($reportType) == 1) {
                $url = "/one-api/tools/listrptpatienttahunanportal/get_list_patient_rpt/";
            } else if ($reportType == 2) {
                $url = "/one-api/tools/listrptpatientportal/get_list_patient_rpt/";
            }
            $attachment = json_encode([[
                "name" => "Report_Bundling_" . str_replace(" ", "_", $e['patientName']) . ".pdf",
                "url" => "https://" . $this->hostname . $url . $e['orderID'] . '/' . $e['patientDOB']

            ]]);
            $attachmentCompany = json_encode([[
                "name" => "Report_Bundling_" . str_replace(" ", "_", $e['patientName']) . ".pdf",
                "url" => "https://" . $this->hostname . $url . $e['orderID']

            ]]);*/

            $subject = "{$e['orderNumber']} {$e['patientName']}";
            // echo $subject;
            // exit;
            if ($e['dataEmailPatient']['selected']) {
                $url = "/one-api/tools/listrptpatienttahunanportal/get_list_patient_rpt_email/";
                if (intval($reportType) == 1) {
                    $url = "/one-api/tools/listrptpatienttahunanportal/get_list_patient_rpt_email/";
                    # 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_email/";
                }
                $attachment = json_encode([[
                    "name" => "Report_Bundling_" . str_replace(" ", "_", $e['patientName']) . ".pdf",
                    "url" => "https://" . $this->hostname . $url . $e['orderID'] . '/' . $e['patientDOB'] 
    
                ]]);
                # code...
                $sql = "SELECT XEmailOutboxID
                        FROM x_email_outbox
                        WHERE XEmailOutboxRefID = ?
                        AND XEmailOutboxRecepientType = 'P'
                        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;
                }
                $dataCek = $query->result_array();

                if (count($dataCek) == 0) {
                    // INSERT
                    $sql = "INSERT INTO x_email_outbox(
                            XEmailOutboxSubject,
                            XEmailOutboxRecipients,
                            XEmailOutboxAttachment,
                            XEmailOutboxCc,
                            XEmailOutboxIsHtml,
                            XEmailOutboxBody,
                            XEmailOutboxType,
                            XEmailOutboxRefID,
                            XEmailOutboxCreated,
                            XEmailOutboxRecepientType)
                            VALUES(?,?,?,?,'Y',?,?,?,NOW(),'P')";
                    $query = $this->db_onedev->query($sql, [
                        $subject,
                        $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 patient");
                        exit;
                    }
                } else {
                    // echo "data ditemukan";
                    // (validateEmail(props . item . patientEmail) &&
                    //     props . item . status === 'N' &&
                    //     props . item . retry >= 5) ||
                    //     (validateEmail(props . item . patientEmail) &&
                    //         props . item . status === 'Y')
                    // kirim ulang
                    if (($e['dataEmailPatient']['sendEmailIsSent'] == 'Y' || $e['dataEmailPatient']['sendEmailIsSent'] == 'E')
                        && intval($e['dataEmailPatient']['sendEmailIsRetry']) <= 5
                    ) {
                        // echo "data masuk update";
                        $sql = "UPDATE x_email_outbox SET 
                            XEmailOutboxSubject = ?,
                            XEmailOutboxRecipients = ?,
                            XEmailOutboxAttachment = ?,
                            XEmailOutboxBody = ?,
                            XEmailOutboxIsSent = 'R',
                            XEmailOutboxRetry = 0, 
                            XEmailOutboxLastUpdated = NOW(),
                            XEmailOutboxCc = ?
                            WHERE 
                            XEmailOutboxID = ?
                            ";
                        $query = $this->db_onedev->query($sql, [
                            $subject,
                            $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 patient");
                            exit;
                        }
                    }
                }
            }
            if ($e['dataEmailCompany']['selected']) {
                
                $url = "/one-api/tools/listrptpatienttahunanportal/get_list_patient_rpt_email/";
                if (intval($reportType) == 1) {
                    $url = "/one-api/tools/listrptpatienttahunanportal/get_list_patient_rpt_email/";
                    # 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_email/";
                }
                $attachmentCompany = json_encode([[
                    "name" => "Report_Bundling_" . str_replace(" ", "_", $e['patientName']) . ".pdf",
                    "url" => "https://" . $this->hostname . $url . $e['orderID'] 
    
                ]]);
                # code...
                $sql = "SELECT XEmailOutboxID
                        FROM x_email_outbox
                        WHERE XEmailOutboxRefID = ?
                        AND XEmailOutboxRecepientType = 'C'
                        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;
                }
                $dataCek = $query->result_array();
                if (count($dataCek) == 0) {
                    // INSERT
                    $sql = "INSERT INTO x_email_outbox(
                            XEmailOutboxSubject,
                            XEmailOutboxRecipients,
                            XEmailOutboxAttachment,
                            XEmailOutboxCc,
                            XEmailOutboxIsHtml,
                            XEmailOutboxBody,
                            XEmailOutboxType,
                            XEmailOutboxRefID,
                            XEmailOutboxCreated,
                            XEmailOutboxRecepientType)
                            VALUES(?,?,?,?,'Y',?,?,?,NOW(),'C')";
                    $query = $this->db_onedev->query($sql, [
                        $subject,
                        $penerimaCompany,
                        $attachmentCompany,
                        $sendCC,
                        $msgCompany,
                        '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 patient");
                        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['dataEmailCompany']['sendEmailIsSent'] == 'Y' || $e['dataEmailCompany']['sendEmailIsSent'] == 'E')
                        && intval($e['dataEmailCompany']['sendEmailIsRetry']) <= 5
                    ) {
                        $sql = "UPDATE x_email_outbox SET 
                            XEmailOutboxSubject = ?,
                            XEmailOutboxRecipients = ?,
                            XEmailOutboxAttachment = ?,
                            XEmailOutboxBody = ?,
                            XEmailOutboxIsSent = 'R',
                            XEmailOutboxRetry = 0, 
                            XEmailOutboxLastUpdated = NOW(),
                            XEmailOutboxCc = ?
                            WHERE 
                            XEmailOutboxID = ?
                            ";
                        $query = $this->db_onedev->query($sql, [
                            $subject,
                            $penerimaCompany,
                            $attachmentCompany,
                            $msgCompany,
                            $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 patient");
                            exit;
                        }
                    }
                }
            }
        }
        // kirim ulang yang Y
        // kirim ulang yang error lebih dari 5

        $this->db_onedev->trans_commit();
        $this->sys_ok('OK');
    }
    function sendEmailold()
    { // $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
        ]);
    }
}