<?php
class Emailconfirmation extends MY_Controller
{
    var $db_onedev;
    var $load;
    function __construct()
    {
        parent::__construct();
        $this->db_onedev = $this->load->database("onedev", true);
    }

    function index()
    {
        echo "Meshbio: Email Confirmation";
    }

    function search()
    {
        try {
            // cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;
            $search = "%%";
            if (isset($prm["search"])) {
                $search = trim($prm["search"]);
                if ($search != "") {
                    $search = "%" . $prm["search"] . "%";
                } else {
                    $search = "%%";
                }
            }
            $start_date = date("Y-m-d");
            if (isset($prm['start_date'])) {
                $start_date = $prm["start_date"];
            }
            $end_date = date("Y-m-d");
            if (isset($prm['end_date'])) {
                $end_date = $prm["end_date"];
            }

            $number_offset = 0;
            $number_limit = 10;
            if ($prm["current_page"] > 0) {
                $number_offset = ($prm["current_page"] - 1) * $number_limit;
            }

            $sql_count = "SELECT count(*) as total
                FROM(
                    SELECT T_OrderHeaderID
                    FROM t_orderheader
                    JOIN t_orderdetail ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID AND T_OrderDetailIsActive = 'Y'
                JOIN t_test ON T_OrderDetailT_TestID = T_TestID 
                AND T_TestIsActive = 'Y'
                JOIN nat_test ON T_TestNat_TestID = Nat_TestID 
                AND Nat_TestIsActive = 'Y' AND Nat_TestCode IN ('40122100','40122200','40122300')
                JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID
                AND M_PatientIsActive = 'Y'
                LEFT JOIN t_orderdelivery ON T_OrderHeaderID = T_OrderDeliveryT_OrderHeaderID
                AND T_OrderDeliveryIsActive = 'Y'
                JOIN m_delivery ON T_OrderDeliveryM_DeliveryID = M_DeliveryID
                AND M_DeliveryIsActive = 'Y'
                    WHERE T_OrderHeaderIsActive = 'Y' AND (DATE(T_OrderHeaderDate)  BETWEEN ? AND ?) AND
                    (M_PatientName LIKE ? OR T_OrderHeaderLabNumber LIKE ? OR T_OrderHeaderLabNumberExt LIKE ?) 
                    group by T_OrderHeaderID
                )x";
            // echo $sql_count;
            $qry_count = $this->db_onedev->query($sql_count, array($start_date, $end_date, $search, $search, $search));
            $tot_count = 0;
            $tot_page = 0;
            if ($qry_count) {
                $tot_count = $qry_count->result_array()[0]["total"];
                $tot_page = ceil($tot_count / $number_limit);
            } else {
                $this->sys_error_db("patient confirmation count", $this->db_onedev);
                exit;
            }

            $sql_data = "SELECT * FROM (
                SELECT T_OrderHeaderID,
                T_OrderHeaderLabNumber,
                T_OrderHeaderIsCito as cito,
                T_OrderHeaderLabNumberExt as external_numbering,
                '' as rownumber,
                trim(M_UserUsername) as username,
                M_PatientNoReg,
                concat(M_TitleName,'. ',M_PatientName) as patient_fullname,
                concat(M_DoctorCode,' <br/>',M_DoctorPrefix,M_DoctorPrefix2,' ',M_DoctorName,' ',M_DoctorSufix,M_DoctorSufix2,M_DoctorSufix3) as doctor_fullname,
                M_CompanyName,
                M_MouName,
                DATE(T_OrderHeaderDate) as order_date,
                T_OrderHeaderIsCito,
                '' as test ,
                fn_report_promise_list(T_OrderHeaderID) as janji,
                T_OrderHeaderAddonIsComing as status_coming,
                T_OrderHeaderAddonIsComingDate as date_coming,
                DATE_FORMAT(T_OrderHeaderAddonIsComingDate,'%d-%m-%Y %H:%i') as date_coming_ina,
                DATE_FORMAT(Fo_StatusCreated,'%d-%m-%Y %H:%i') as order_datetime,
                IFNULL(meshbioSessionUrl,'') as xurl,
                IF(ISNULL(meshbioSessionhl7status),'X',meshbioSessionhl7status) as mstatus,
                M_DeliveryName,
                M_DeliveryPosition,
                T_OrderDeliveryM_DeliveryTypeID,
                T_OrderDeliveryID,
                T_OrderDeliveryDestination,
                IF(ISNULL(meshbio_emailIsSent), 'X', meshbio_emailIsSent) as emailstatus
                FROM t_orderheader 
                JOIN t_orderdetail ON T_OrderDetailT_OrderHeaderID = T_OrderHeaderID AND T_OrderDetailIsActive = 'Y'
                JOIN t_test ON T_OrderDetailT_TestID = T_TestID AND T_TestIsActive = 'Y'
                JOIN nat_test ON T_TestNat_TestID = Nat_TestID AND Nat_TestIsActive = 'Y' AND Nat_TestCode IN ('40122100','40122200','40122300')
                JOIN t_orderheaderaddon ON T_OrderHeaderAddOnT_OrderHeaderID = T_OrderHeaderID
                JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID
                AND M_PatientIsActive = 'Y'
                JOIN m_doctor ON T_OrderHeaderSenderM_DoctorID = M_DoctorID
                AND M_DoctorIsActive = 'Y'
                LEFT JOIN m_title ON M_PatientM_TitleID = M_TitleID
                AND M_TitleIsActive = 'Y'
                JOIN m_company ON T_OrderHeaderM_CompanyID = M_CompanyID
                AND M_CompanyIsActive = 'Y'
                JOIN m_mou ON T_OrderHeaderM_MouID = M_MouID
                AND M_MouIsActive = 'Y'
                JOIN fo_status ON Fo_StatusT_OrderHeaderID = T_OrderHeaderID AND Fo_StatusM_StatusID = 1
                JOIN m_user ON Fo_StatusM_UserID = M_UserID
                JOIN meshbio_session ON meshbioSessionT_OrderHeaderID  = T_OrderHeaderID 
                LEFT JOIN t_orderdelivery ON T_OrderHeaderID = T_OrderDeliveryT_OrderHeaderID AND T_OrderDeliveryM_DeliveryTypeID = 3
                AND T_OrderDeliveryIsActive = 'Y'
                LEFT JOIN meshbio_email ON T_OrderHeaderID = meshbio_emailT_OrderHeaderID AND T_OrderDeliveryID = meshbio_emailT_OrderDeliveryID 
                JOIN m_delivery ON T_OrderDeliveryM_DeliveryID = M_DeliveryID
                AND M_DeliveryIsActive = 'Y'
                    WHERE T_OrderHeaderIsActive = 'Y' AND (DATE(T_OrderHeaderDate)  BETWEEN ? AND ?) AND 
                    (M_PatientName LIKE ? OR T_OrderHeaderLabNumber LIKE ? OR T_OrderHeaderLabNumberExt LIKE ?) 
                    GROUP BY T_OrderHeaderID ) x
                    ORDER BY T_OrderHeaderIsCito DESC, T_OrderHeaderID ASC
                    LIMIT ? OFFSET ?";
            $qry_data = $this->db_onedev->query($sql_data, array(
                $start_date,
                $end_date,
                $search,
                $search,
                $search,
                $number_limit,
                $number_offset
            ));
            // $last_qry = $this->db_onedev->last_query();
            // print_r($last_qry);
            if ($sql_data) {
                $rows = $qry_data->result_array();
            } else {
                $this->sys_error_db("patient confirmation select", $this->db_onedev);
                exit;
            }
            // print_r($rows);
            if ($rows) {
                foreach ($rows as $k => $y) {
                    $xno = ($k + 1) + $number_offset;
                    $rows[$k]['rownumber'] = $xno;
                    $dt_test = [];
                    $sql = "SELECT concat(T_OrderDetailT_TestName,'^',T_OrderDetailIsCito) as x_test
                            FROM t_orderdetail
                            WHERE
                                T_OrderDetailT_OrderHeaderID = {$y['T_OrderHeaderID']} AND T_OrderDetailT_TestIsPrice = 'Y' AND T_OrderDetailIsActive = 'Y'";
                    $xdt_test = $this->db_onedev->query($sql)->result_array();
                    foreach ($xdt_test as $i => $j) {
                        array_push($dt_test, $j['x_test']);
                    }
                    $rows[$k]['test'] = implode(",", $dt_test);
                }
            }
            // print_r($rows);

            $result = array(
                "total" => $tot_page,
                "total_filter" => $tot_count,
                "records" => $rows
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function confirm()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;
            $orderheaderid = $prm["orderheaderid"];
            $orderdeliveryid = $prm["orderdeliveryid"];
            $destination = "";
            if (isset($prm['destination'])) {
                $destination = trim($prm["destination"]);
            }
            $emailresend = $prm["meshbio_emailIsSent"];

            $sql = "SELECT meshbio_emailID,
                meshbio_emailT_OrderHeaderID,
                meshbio_emailT_OrderDeliveryID,
                meshbio_emailDestination,
                meshbio_emailIsSent,
                meshbio_emaiSentDate
                FROM meshbio_email
                WHERE meshbio_emailT_OrderHeaderID = ?
                AND meshbio_emailT_OrderDeliveryID = ?";
            $qry = $this->db_onedev->query($sql, array($orderheaderid, $orderdeliveryid));
            if ($qry) {
                $rows = $qry->result_array();
                // print_r($rows);
            } else {
                $this->sys_error_db("meshbio email select error", $this->db_onedev);
                exit;
            }

            if (count($rows) > 0) {
                $meshbioemailid = $rows[0]["meshbio_emailID"];
                $sql_update = "UPDATE meshbio_email SET
                    meshbio_emailDestination = ?,
                    meshbio_emailIsSent = 'R',
                    meshbio_emaiSentDate = NOW(),
                    meshbio_emailLastUpdated = NOW()
                    WHERE meshbio_emailID = ?";
                $qry_update = $this->db_onedev->query($sql_update, array($destination, $meshbioemailid));
                if (!$qry_update) {
                    $this->sys_error_db("meshbio email update error", $this->db_onedev);
                    exit;
                }
            } else {
                $sql_insert = "INSERT INTO meshbio_email(
                    meshbio_emailT_OrderHeaderID,
                    meshbio_emailT_OrderDeliveryID,
                    meshbio_emailDestination,
                    meshbio_emailIsSent,
                    meshbio_emaiSentDate,
                    meshbio_emailCreated,
                    meshbio_emailLastUpdated) VALUES(?,?,?,'N',NOW(),NOW(),NOW())";
                $qry_insert = $this->db_onedev->query($sql_insert, array(
                    $orderheaderid,
                    $orderdeliveryid,
                    $destination
                ));
                if (!$qry_insert) {
                    $this->db_onedev->trans_rollback();
                    $this->sys_error_db("meshbio email insert", $this->db_onedev);
                    exit;
                }
            }

            $this->db_onedev->trans_commit();
            $result = array(
                "total" => 1,
                "affected_rows" => $this->db_onedev->affected_rows()
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
}