<?php
class Requestupload extends MY_Controller
{
    public function __construct()
    {
        parent::__construct();
        // db wilayah contoh surabaya
        // $this->db = $this->load->database("regional", true);

        // string database
        $this->db_str_doctor = 'one_doctor';
        $this->M_MouM_CompanyID = "1235";
    }

    public function index()
    {
        echo "request upload";
    }

    // search v1 tanpa proteksi lunas dan ini yang lama
    public function search_v1()
    {
        try {
            # cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            $prm = $this->sys_input;

            $search = $prm["search"];
            // if (isset($prm['search'])) {
            //     $search = trim($prm["search"]);
            //     if ($search != "") {
            //         $search = '%' . $prm['search'] . '%';
            //     }else{
            //         $search = '%%';
            //     }
            // }


            $where = " T_OrderHeaderIsActive = 'Y' ";

            $startdate = $prm['startdate'];
            $enddate = $prm['enddate'];

            $filter_date = " AND DATE(T_OrderHeaderDate) BETWEEN '{$startdate}' AND '{$enddate}'";
            if ($startdate != '' && $enddate != '') {
                $where = " T_OrderHeaderIsActive = 'Y' $filter_date ";
            }

            if ($search != '')
                $where .= " AND ( M_PatientName LIKE '%{$search}%' OR T_OrderHeaderLabNumber LIKE '%{$search}%' OR T_OrderHeaderLabNumberExt LIKE '%{$search}%' )";


            $sortBy = $prm['sortBy'];
            // $sortStatus = $prm['sortStatus'];
            $q_sort = '';
            $limit = '';
            if ($sortBy) {
                $q_sort = "ORDER BY " . $sortBy;
            }
            if ($all == 'N') {
                $limit = ' LIMIT 10';
            }
            $number_limit = 20;
            // $number_offset = ($prm['current_page'] - 1) * $number_limit ;

            $number_offset = 0;
            if ($prm['current_page'] > 0) {
                $number_offset = ($prm['current_page'] - 1) * $number_limit;
            }

            $sql_filter = "select count(*) as total 
        from (
            select
                T_OrderHeaderLabNumber as no_reg,
                concat(M_TitleName, ' ',
                            ifnull(M_PatientPrefix,' '),
                            M_PatientName,
                            ifnull(M_PatientSuffix,'')
                            )
                            as nama_pasien,
                fn_get_doctor_fullname($this->db_str_doctor.order_fo.OrderFoM_DoctorID) as nama_dokter,
                M_MouName as nama_mou,
                T_OrderHeaderTotal as total,
                if($this->db_str_doctor.request_upload.RequestUploadIsUploaded is NULL,'N',RequestUploadIsUploaded) as RequestIsUploaded
                from t_orderheader 
                join $this->db_str_doctor.order_fo 
                ON OrderFoT_OrderHeaderID = T_OrderHeaderID
                AND OrderFoIsActive = 'Y'
                join $this->db_str_doctor.order_patient 
                ON OrderFoOrderPatientID = OrderPatientID
                AND OrderPatientIsActive = 'Y'
                left join $this->db_str_doctor.request_upload
                ON T_OrderHeaderID = RequestUploadT_OrderHeaderID
                AND RequestUploadIsActive = 'Y'
                AND RequestUploadIsUploaded = 'N'
                join m_mou
                ON OrderFoM_MouID = M_MouID
                AND M_MouIsActive = 'Y'
                AND M_MouIsReleased = 'Y'
                AND M_MouM_CompanyID = 1235 
                join m_patient
                ON T_OrderHeaderM_PatientID = M_PatientID
                join m_title 
                on M_PatientM_TitleID = M_TitleID
                where $where
                group by T_OrderHeaderID
            ) x";
            // $sql_param = array($search);
            //  $total = $this->db->query($sql,$sql_param)->row()->total;

            $query_filter = $this->db->query($sql_filter);
            $tot_count = 0;
            $tot_page = 0;
            if ($query_filter) {
                $tot_count = $query_filter->result_array()[0]["total"];
                $tot_page = ceil($tot_count / $number_limit);
            } else {
                $this->db->trans_rollback();
                $this->sys_error_db("request_upload count", $this->db);
                exit;
            }


            $sql_data = "select
            ifnull(RequestUploadIsUploaded,'x') as RequestUploadIsUploaded,
            if(RequestUploadLastUpdated is null,'', DATE_FORMAT(RequestUploadLastUpdated,'%d-%m-%Y %H:%i')) 
            as RequestUploadLastUpdated,
            T_OrderHeaderID,
            OrderPatientID,
            T_OrderHeaderLabNumber as no_reg,
            concat(M_TitleName, ' ',
                        ifnull(M_PatientPrefix,' '),
                        M_PatientName,
                        ifnull(M_PatientSuffix,'')
                        )
                        as nama_pasien,
            fn_get_doctor_fullname($this->db_str_doctor.order_fo.OrderFoM_DoctorID) as nama_dokter,
            M_MouName as nama_mou,
            T_OrderHeaderTotal as total,
            '' as test
            from t_orderheader 
            join $this->db_str_doctor.order_fo 
            ON OrderFoT_OrderHeaderID = T_OrderHeaderID
            AND OrderFoIsActive = 'Y'
            join $this->db_str_doctor.order_patient 
            ON OrderFoOrderPatientID = OrderPatientID
            AND OrderPatientIsActive = 'Y'
            left join $this->db_str_doctor.request_upload
            ON T_OrderHeaderID = RequestUploadT_OrderHeaderID
            AND RequestUploadIsActive = 'Y'
            AND RequestUploadIsUploaded = 'N'
            join m_mou
            ON OrderFoM_MouID = M_MouID
            AND M_MouIsActive = 'Y'
            AND M_MouIsReleased = 'Y'
            AND M_MouM_CompanyID = 1235 
            join m_patient
            ON T_OrderHeaderM_PatientID = M_PatientID
            join m_title 
            on M_PatientM_TitleID = M_TitleID
            join t_orderdetail 
            ON T_OrderHeaderID = T_OrderDetailT_OrderHeaderID
            AND T_OrderDetailIsActive = 'Y'
            join t_test
            ON T_OrderDetailT_TestID = T_TestID
            AND T_TestIsActive = 'Y'
            where $where
            group by T_OrderHeaderID
            limit $number_limit offset $number_offset";

            // $sql_param = array($search);
            $query_data = $this->db->query($sql_data);
            //echo $this->db->last_query();

            if ($query_data) {
                $rows = $query_data->result_array();
                if (count($rows) > 0) {
                    $sql_dt = "";
                    foreach ($rows as $k => $v) {
                        $order_id = $v['T_OrderHeaderID'];
                        $dt_test = [];
                        $sql_dt = "SELECT T_OrderDetailT_TestName as x_test
                                from t_orderdetail 
                                join t_test
                                    ON T_OrderDetailT_TestID = T_TestID
                                    AND T_TestIsActive = 'Y'
                                WHERE
                                    T_OrderDetailT_OrderHeaderID = $order_id 
                                    AND T_OrderDetailIsActive = 'Y'
                                    AND T_OrderDetailT_TestIsPrice = 'Y'
                                ";

                        // echo $sql_dt;
                        $xdt_test = $this->db->query($sql_dt);
                        // print_r($xdt_test);
                        if (!$xdt_test) {
                            $this->db->trans_rollback();
                            $this->sys_error_db("order detail");
                            exit;
                        } else {
                            $testArr = $xdt_test->result_array();
                            $rows[$k]['test'] = $testArr;
                            $xno = ($k + 1) + $number_offset;
                            $rows[$k]['rownumber'] = $xno;
                        }
                    }
                }
            } else {
                $this->db->trans_rollback();
                $this->sys_error_db("request_upload select");
                exit;
            }


            $result = array(
                "total" => $tot_page,
                "total_all" => $tot_count,
                "total_filter" => count($rows),
                "records" => $rows,
                "sql" => $this->db->last_query()
            );
            // $result = array ("total" => $tot_page, "total_filter"=>count($rows),"records" => $rows, 'sql'=> $this->db->last_query());
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    // search dibawah ini dengan proteksi lunas
    // pas di listing ketambahan WHERE
    // (F_OrderheaderLunas = Y
    // OR M_MouIsBill = Y)
    // AND T_OrderHeaderID ada di tabel result_processtooffice
    // update code tanggal 20-07-2023 

    public function search()
    {
        try {
            # cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            $prm = $this->sys_input;

            $search = $prm["search"];
            // if (isset($prm['search'])) {
            //     $search = trim($prm["search"]);
            //     if ($search != "") {
            //         $search = '%' . $prm['search'] . '%';
            //     }else{
            //         $search = '%%';
            //     }
            // }


            $where = "";

            $startdate = $prm['startdate'];
            $enddate = $prm['enddate'];

            // $str_search_T_OrderHeaderDate = "";
            // $str_search_M_PatientName = "";
            // $str_search_T_OrderHeaderLabNumber = "";
            // $str_search_T_OrderHeaderLabNumberExt = "";

            $filter_date = " DATE(T_OrderHeaderDate) BETWEEN '{$startdate}' AND '{$enddate}'";
            if ($startdate != '' && $enddate != '') {
                $where = "$filter_date ";
                // $str_search_T_OrderHeaderDate = " AND $filter_date";
            }

            if ($search != '') {
                $where .= " AND ( M_PatientName LIKE '%{$search}%' OR T_OrderHeaderLabNumber LIKE '%{$search}%' OR T_OrderHeaderLabNumberExt LIKE '%{$search}%' ) ";
                // $str_search_M_PatientName = "AND M_PatientName LIKE '%{$search}%' ";
                // $str_search_T_OrderHeaderLabNumber = "OR T_OrderHeaderLabNumber LIKE '%{$search}%' ";
                // $str_search_T_OrderHeaderLabNumberExt = "OR T_OrderHeaderLabNumberExt LIKE '%{$search}%' ";
            }


            $sortBy = $prm['sortBy'];
            // $sortStatus = $prm['sortStatus'];
            $q_sort = '';
            $limit = '';
            if ($sortBy) {
                $q_sort = "ORDER BY " . $sortBy;
            }
            if ($all == 'N') {
                $limit = ' LIMIT 10';
            }
            $number_limit = 20;
            // $number_offset = ($prm['current_page'] - 1) * $number_limit ;

            $number_offset = 0;
            if ($prm['current_page'] > 0) {
                $number_offset = ($prm['current_page'] - 1) * $number_limit;
            }

        // EXISTING
        //     $sql_filter = "select count(*) as total 
        // from (
        //         select
        //         T_OrderHeaderLabNumber as no_reg,
        //         concat(M_TitleName, ' ',
        //                     ifnull(M_PatientPrefix,' '),
        //                     M_PatientName,
        //                     ifnull(M_PatientSuffix,'')
        //                     )
        //                     as nama_pasien,
        //         fn_get_doctor_fullname($this->db_str_doctor.order_fo.OrderFoM_DoctorID) as nama_dokter,
        //         M_MouName as nama_mou,
        //         T_OrderHeaderTotal as total,
        //         if($this->db_str_doctor.request_upload.RequestUploadIsUploaded is NULL,'N',RequestUploadIsUploaded) as RequestIsUploaded
        //         from t_orderheader 
        //         join $this->db_str_doctor.order_fo 
        //             ON OrderFoT_OrderHeaderID = T_OrderHeaderID
        //             AND OrderFoIsActive = 'Y'
        //             AND T_OrderHeaderIsActive = 'Y'
        //         join $this->db_str_doctor.order_patient 
        //             ON OrderFoOrderPatientID = OrderPatientID
        //             AND OrderPatientIsActive = 'Y'
        //         left join $this->db_str_doctor.request_upload
        //             ON T_OrderHeaderID = RequestUploadT_OrderHeaderID
        //             AND RequestUploadIsActive = 'Y'
                    
        //         join m_mou
        //             ON OrderFoM_MouID = M_MouID
        //             AND M_MouIsActive = 'Y'
        //             AND M_MouIsReleased = 'Y'
        //             AND M_MouM_CompanyID = $this->M_MouM_CompanyID 
        //             AND M_MouIsBill = 'Y'
        //         join m_patient
        //             ON T_OrderHeaderM_PatientID = M_PatientID
        //         join m_title 
        //             on M_PatientM_TitleID = M_TitleID
        //         JOIN result_processtooffice
        //             ON T_OrderHeaderID = Result_ProcessToOfficeT_OrderHeaderID
        //             AND Result_ProcessToOfficeIsActive = 'Y'
        //         where $where
        //         group by T_OrderHeaderID
        //         UNION
        //         select
        //         T_OrderHeaderLabNumber as no_reg,
        //         concat(M_TitleName, ' ',
        //                     ifnull(M_PatientPrefix,' '),
        //                     M_PatientName,
        //                     ifnull(M_PatientSuffix,'')
        //                     )
        //                     as nama_pasien,
        //         fn_get_doctor_fullname($this->db_str_doctor.order_fo.OrderFoM_DoctorID) as nama_dokter,
        //         M_MouName as nama_mou,
        //         T_OrderHeaderTotal as total,
        //         if($this->db_str_doctor.request_upload.RequestUploadIsUploaded is NULL,'N',RequestUploadIsUploaded) as RequestIsUploaded
        //         from t_orderheader 
        //         join $this->db_str_doctor.order_fo 
        //             ON OrderFoT_OrderHeaderID = T_OrderHeaderID
        //             AND OrderFoIsActive = 'Y'
        //             AND T_OrderHeaderIsActive = 'Y'
        //         join $this->db_str_doctor.order_patient 
        //             ON OrderFoOrderPatientID = OrderPatientID
        //             AND OrderPatientIsActive = 'Y'
        //         left join $this->db_str_doctor.request_upload
        //             ON T_OrderHeaderID = RequestUploadT_OrderHeaderID
        //             AND RequestUploadIsActive = 'Y'
                    
        //         join m_mou
        //             ON OrderFoM_MouID = M_MouID
        //             AND M_MouIsActive = 'Y'
        //             AND M_MouIsReleased = 'Y'
        //             AND M_MouM_CompanyID = $this->M_MouM_CompanyID 
        //         join m_patient
        //             ON T_OrderHeaderM_PatientID = M_PatientID
        //         join m_title 
        //             on M_PatientM_TitleID = M_TitleID
        //         JOIN result_processtooffice
        //             ON T_OrderHeaderID = Result_ProcessToOfficeT_OrderHeaderID
        //             AND Result_ProcessToOfficeIsActive = 'Y'
        //             join f_payment 
        //             ON t_orderheader.T_OrderHeaderID = F_PaymentT_OrderHeaderID
        //             AND F_PaymentIsActive = 'Y'
        //         join f_payment_orderheader 
        //             ON F_PaymentID = F_Payment_OrderHeaderF_PaymentID
        //             AND F_Payment_OrderHeaderIsLunas = 'Y'
        //         where $where
        //         group by T_OrderHeaderID
        //     ) x";

        // DEBUG ONLY
        $sql_filter = "select count(*) as total 
        from (
                select
                T_OrderHeaderLabNumber as no_reg,
                concat(M_TitleName, ' ',
                            ifnull(M_PatientPrefix,' '),
                            M_PatientName,
                            ifnull(M_PatientSuffix,'')
                            )
                            as nama_pasien,
                fn_get_doctor_fullname($this->db_str_doctor.order_fo.OrderFoM_DoctorID) as nama_dokter,
                M_MouName as nama_mou,
                T_OrderHeaderTotal as total,
                if($this->db_str_doctor.request_upload.RequestUploadIsUploaded is NULL,'N',RequestUploadIsUploaded) as RequestIsUploaded
                from t_orderheader 
                join $this->db_str_doctor.order_fo 
                    ON OrderFoT_OrderHeaderID = T_OrderHeaderID
                    AND OrderFoIsActive = 'Y'
                    AND T_OrderHeaderIsActive = 'Y'
                join $this->db_str_doctor.order_patient 
                    ON OrderFoOrderPatientID = OrderPatientID
                    AND OrderPatientIsActive = 'Y'
                left join $this->db_str_doctor.request_upload
                    ON T_OrderHeaderID = RequestUploadT_OrderHeaderID
                    AND RequestUploadIsActive = 'Y'
                    
                join m_mou
                    ON OrderFoM_MouID = M_MouID
                    AND M_MouIsActive = 'Y'
                    AND M_MouIsReleased = 'Y'
                    AND (M_MouM_CompanyID = $this->M_MouM_CompanyID OR M_MouM_CompanyID = 1710)
                    AND M_MouIsBill = 'Y'
                join m_patient
                    ON T_OrderHeaderM_PatientID = M_PatientID
                join m_title 
                    on M_PatientM_TitleID = M_TitleID
                JOIN result_processtooffice
                    ON T_OrderHeaderID = Result_ProcessToOfficeT_OrderHeaderID
                    AND Result_ProcessToOfficeIsActive = 'Y'
                where $where
                group by T_OrderHeaderID
                UNION
                select
                T_OrderHeaderLabNumber as no_reg,
                concat(M_TitleName, ' ',
                            ifnull(M_PatientPrefix,' '),
                            M_PatientName,
                            ifnull(M_PatientSuffix,'')
                            )
                            as nama_pasien,
                fn_get_doctor_fullname($this->db_str_doctor.order_fo.OrderFoM_DoctorID) as nama_dokter,
                M_MouName as nama_mou,
                T_OrderHeaderTotal as total,
                if($this->db_str_doctor.request_upload.RequestUploadIsUploaded is NULL,'N',RequestUploadIsUploaded) as RequestIsUploaded
                from t_orderheader 
                join $this->db_str_doctor.order_fo 
                    ON OrderFoT_OrderHeaderID = T_OrderHeaderID
                    AND OrderFoIsActive = 'Y'
                    AND T_OrderHeaderIsActive = 'Y'
                join $this->db_str_doctor.order_patient 
                    ON OrderFoOrderPatientID = OrderPatientID
                    AND OrderPatientIsActive = 'Y'
                left join $this->db_str_doctor.request_upload
                    ON T_OrderHeaderID = RequestUploadT_OrderHeaderID
                    AND RequestUploadIsActive = 'Y'
                    
                join m_mou
                    ON OrderFoM_MouID = M_MouID
                    AND M_MouIsActive = 'Y'
                    AND M_MouIsReleased = 'Y'
                    AND (M_MouM_CompanyID = $this->M_MouM_CompanyID OR M_MouM_CompanyID = 1710)
                join m_patient
                    ON T_OrderHeaderM_PatientID = M_PatientID
                join m_title 
                    on M_PatientM_TitleID = M_TitleID
                JOIN result_processtooffice
                    ON T_OrderHeaderID = Result_ProcessToOfficeT_OrderHeaderID
                    AND Result_ProcessToOfficeIsActive = 'Y'
                    join f_payment 
                    ON t_orderheader.T_OrderHeaderID = F_PaymentT_OrderHeaderID
                    AND F_PaymentIsActive = 'Y'
                join f_payment_orderheader 
                    ON F_PaymentID = F_Payment_OrderHeaderF_PaymentID
                    AND F_Payment_OrderHeaderIsLunas = 'Y'
                where $where
                group by T_OrderHeaderID
            ) x";

            // $sql_param = array($search);
            //  $total = $this->db->query($sql,$sql_param)->row()->total;

            $query_filter = $this->db->query($sql_filter);
            // echo $this->db->last_query();
            // exit;
            $tot_count = 0;
            $tot_page = 0;
            if ($query_filter) {
                $tot_count = $query_filter->result_array()[0]["total"];
                $tot_page = ceil($tot_count / $number_limit);
            } else {
                $this->db->trans_rollback();
                $this->sys_error_db("request_upload count", $this->db);
                exit;
            }

            // EXISTING
            // $sql_data = "
            // select
            //     ifnull(RequestUploadIsUploaded,'x') as RequestUploadIsUploaded,
            //     if(RequestUploadLastUpdated is null,'', DATE_FORMAT(RequestUploadLastUpdated,'%d-%m-%Y %H:%i')) 
            //     as RequestUploadLastUpdated,
            //     T_OrderHeaderID,
            //     OrderPatientID,
            //     T_OrderHeaderLabNumber as no_reg,
            //     concat(M_TitleName, ' ',
            //                 ifnull(M_PatientPrefix,' '),
            //                 M_PatientName,
            //                 ifnull(M_PatientSuffix,'')
            //                 )
            //                 as nama_pasien,
            //     fn_get_doctor_fullname($this->db_str_doctor.order_fo.OrderFoM_DoctorID) as nama_dokter,
            //     M_MouName as nama_mou,
            //     T_OrderHeaderTotal as total,
            //     '' as test
            //     from t_orderheader 
            //     join $this->db_str_doctor.order_fo 
            //         ON OrderFoT_OrderHeaderID = T_OrderHeaderID
            //         AND OrderFoIsActive = 'Y'
            //         AND T_OrderHeaderIsActive = 'Y'
            //     join $this->db_str_doctor.order_patient 
            //         ON OrderFoOrderPatientID = OrderPatientID
            //         AND OrderPatientIsActive = 'Y'
            //     left join $this->db_str_doctor.request_upload
            //         ON T_OrderHeaderID = RequestUploadT_OrderHeaderID
            //         AND RequestUploadIsActive = 'Y'
                    
            //     join m_mou
            //         ON OrderFoM_MouID = M_MouID
            //         AND M_MouIsActive = 'Y'
            //         AND M_MouIsReleased = 'Y'
            //         AND M_MouM_CompanyID = $this->M_MouM_CompanyID
            //         AND M_MouIsBill = 'Y'
            //     join m_patient
            //         ON T_OrderHeaderM_PatientID = M_PatientID
            //     join m_title 
            //         on M_PatientM_TitleID = M_TitleID
            //     JOIN result_processtooffice
            //         ON T_OrderHeaderID = Result_ProcessToOfficeT_OrderHeaderID
            //         AND Result_ProcessToOfficeIsActive = 'Y' 
            //     where $where
            //     group by T_OrderHeaderID
            //     UNION
            //     select
            //     ifnull(RequestUploadIsUploaded,'x') as RequestUploadIsUploaded,
            //     if(RequestUploadLastUpdated is null,'', DATE_FORMAT(RequestUploadLastUpdated,'%d-%m-%Y %H:%i')) 
            //     as RequestUploadLastUpdated,
            //     T_OrderHeaderID,
            //     OrderPatientID,
            //     T_OrderHeaderLabNumber as no_reg,
            //     concat(M_TitleName, ' ',
            //                 ifnull(M_PatientPrefix,' '),
            //                 M_PatientName,
            //                 ifnull(M_PatientSuffix,'')
            //                 )
            //                 as nama_pasien,
            //     fn_get_doctor_fullname($this->db_str_doctor.order_fo.OrderFoM_DoctorID) as nama_dokter,
            //     M_MouName as nama_mou,
            //     T_OrderHeaderTotal as total,
            //     '' as test
            //     from t_orderheader 
            //     join $this->db_str_doctor.order_fo 
            //         ON OrderFoT_OrderHeaderID = T_OrderHeaderID
            //         AND OrderFoIsActive = 'Y'
            //         AND T_OrderHeaderIsActive = 'Y'
            //     join $this->db_str_doctor.order_patient 
            //         ON OrderFoOrderPatientID = OrderPatientID
            //         AND OrderPatientIsActive = 'Y'
            //     left join $this->db_str_doctor.request_upload
            //         ON T_OrderHeaderID = RequestUploadT_OrderHeaderID
            //         AND RequestUploadIsActive = 'Y'
                    
            //     join m_mou
            //         ON OrderFoM_MouID = M_MouID
            //         AND M_MouIsActive = 'Y'
            //         AND M_MouIsReleased = 'Y'
            //         AND M_MouM_CompanyID = $this->M_MouM_CompanyID
            //     join m_patient
            //         ON T_OrderHeaderM_PatientID = M_PatientID
            //     join m_title 
            //         on M_PatientM_TitleID = M_TitleID
            //     JOIN result_processtooffice
            //         ON T_OrderHeaderID = Result_ProcessToOfficeT_OrderHeaderID
            //         AND Result_ProcessToOfficeIsActive = 'Y'
            //         join f_payment 
            //         ON t_orderheader.T_OrderHeaderID = F_PaymentT_OrderHeaderID
            //         AND F_PaymentIsActive = 'Y'
            //     join f_payment_orderheader 
            //         ON F_PaymentID = F_Payment_OrderHeaderF_PaymentID
            //         AND F_Payment_OrderHeaderIsLunas = 'Y'
            //     where $where
            //     group by T_OrderHeaderID
            //     limit $number_limit offset $number_offset";

            // DEBUG ONLY
            $sql_data = "
            select
                ifnull(RequestUploadIsUploaded,'x') as RequestUploadIsUploaded,
                if(RequestUploadLastUpdated is null,'', DATE_FORMAT(RequestUploadLastUpdated,'%d-%m-%Y %H:%i')) 
                as RequestUploadLastUpdated,
                T_OrderHeaderID,
                OrderPatientID,
                T_OrderHeaderLabNumber as no_reg,
                concat(M_TitleName, ' ',
                            ifnull(M_PatientPrefix,' '),
                            M_PatientName,
                            ifnull(M_PatientSuffix,'')
                            )
                            as nama_pasien,
                fn_get_doctor_fullname($this->db_str_doctor.order_fo.OrderFoM_DoctorID) as nama_dokter,
                M_MouName as nama_mou,
                T_OrderHeaderTotal as total,
                '' as test
                from t_orderheader 
                join $this->db_str_doctor.order_fo 
                    ON OrderFoT_OrderHeaderID = T_OrderHeaderID
                    AND OrderFoIsActive = 'Y'
                    AND T_OrderHeaderIsActive = 'Y'
                join $this->db_str_doctor.order_patient 
                    ON OrderFoOrderPatientID = OrderPatientID
                    AND OrderPatientIsActive = 'Y'
                left join $this->db_str_doctor.request_upload
                    ON T_OrderHeaderID = RequestUploadT_OrderHeaderID
                    AND RequestUploadIsActive = 'Y'
                    
                join m_mou
                    ON OrderFoM_MouID = M_MouID
                    AND M_MouIsActive = 'Y'
                    AND M_MouIsReleased = 'Y'
                    AND (M_MouM_CompanyID = $this->M_MouM_CompanyID OR M_MouM_CompanyID = 1710)
                    AND M_MouIsBill = 'Y'
                join m_patient
                    ON T_OrderHeaderM_PatientID = M_PatientID
                join m_title 
                    on M_PatientM_TitleID = M_TitleID
                JOIN result_processtooffice
                    ON T_OrderHeaderID = Result_ProcessToOfficeT_OrderHeaderID
                    AND Result_ProcessToOfficeIsActive = 'Y' 
                where $where
                group by T_OrderHeaderID
                UNION
                select
                ifnull(RequestUploadIsUploaded,'x') as RequestUploadIsUploaded,
                if(RequestUploadLastUpdated is null,'', DATE_FORMAT(RequestUploadLastUpdated,'%d-%m-%Y %H:%i')) 
                as RequestUploadLastUpdated,
                T_OrderHeaderID,
                OrderPatientID,
                T_OrderHeaderLabNumber as no_reg,
                concat(M_TitleName, ' ',
                            ifnull(M_PatientPrefix,' '),
                            M_PatientName,
                            ifnull(M_PatientSuffix,'')
                            )
                            as nama_pasien,
                fn_get_doctor_fullname($this->db_str_doctor.order_fo.OrderFoM_DoctorID) as nama_dokter,
                M_MouName as nama_mou,
                T_OrderHeaderTotal as total,
                '' as test
                from t_orderheader 
                join $this->db_str_doctor.order_fo 
                    ON OrderFoT_OrderHeaderID = T_OrderHeaderID
                    AND OrderFoIsActive = 'Y'
                    AND T_OrderHeaderIsActive = 'Y'
                join $this->db_str_doctor.order_patient 
                    ON OrderFoOrderPatientID = OrderPatientID
                    AND OrderPatientIsActive = 'Y'
                left join $this->db_str_doctor.request_upload
                    ON T_OrderHeaderID = RequestUploadT_OrderHeaderID
                    AND RequestUploadIsActive = 'Y'
                    
                join m_mou
                    ON OrderFoM_MouID = M_MouID
                    AND M_MouIsActive = 'Y'
                    AND M_MouIsReleased = 'Y'
                    AND (M_MouM_CompanyID = $this->M_MouM_CompanyID OR M_MouM_CompanyID = 1710)
                join m_patient
                    ON T_OrderHeaderM_PatientID = M_PatientID
                join m_title 
                    on M_PatientM_TitleID = M_TitleID
                JOIN result_processtooffice
                    ON T_OrderHeaderID = Result_ProcessToOfficeT_OrderHeaderID
                    AND Result_ProcessToOfficeIsActive = 'Y'
                    join f_payment 
                    ON t_orderheader.T_OrderHeaderID = F_PaymentT_OrderHeaderID
                    AND F_PaymentIsActive = 'Y'
                join f_payment_orderheader 
                    ON F_PaymentID = F_Payment_OrderHeaderF_PaymentID
                    AND F_Payment_OrderHeaderIsLunas = 'Y'
                where $where
                group by T_OrderHeaderID
                limit $number_limit offset $number_offset";

            // $sql_param = array($search);
            $query_data = $this->db->query($sql_data);
            // echo $this->db->last_query();
            // exit;

            if ($query_data) {
                $rows = $query_data->result_array();
                if (count($rows) > 0) {
                    $sql_dt = "";
                    foreach ($rows as $k => $v) {
                        $order_id = $v['T_OrderHeaderID'];
                        $dt_test = [];
                        $sql_dt = "SELECT T_OrderDetailT_TestName as x_test
                                from t_orderdetail 
                                join t_test
                                    ON T_OrderDetailT_TestID = T_TestID
                                    AND T_TestIsActive = 'Y'
                                WHERE
                                    T_OrderDetailT_OrderHeaderID = $order_id 
                                    AND T_OrderDetailIsActive = 'Y'
                                    AND T_OrderDetailT_TestIsPrice = 'Y'
                                ";

                        // echo $sql_dt;
                        $xdt_test = $this->db->query($sql_dt);
                        // print_r($xdt_test);
                        if (!$xdt_test) {
                            $this->db->trans_rollback();
                            $this->sys_error_db("order detail");
                            exit;
                        } else {
                            $testArr = $xdt_test->result_array();
                            $rows[$k]['test'] = $testArr;
                            $xno = ($k + 1) + $number_offset;
                            $rows[$k]['rownumber'] = $xno;
                        }
                    }
                }
            } else {
                $this->db->trans_rollback();
                $this->sys_error_db("request_upload select");
                exit;
            }

            $result = array(
                "total" => $tot_page,
                "total_all" => $tot_count,
                "total_filter" => count($rows),
                "records" => $rows,
                "sql" => $this->db->last_query(),
                "sql_data" => ($sql_data)
            );
            // $result = array ("total" => $tot_page, "total_filter"=>count($rows),"records" => $rows, 'sql'=> $this->db->last_query());
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }


    public function change_reupload()
    {
        try {
            # cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            $prm = $this->sys_input;
            $userID = $this->sys_user['M_UserID'];
            $RequestUploadT_OrderHeaderID = $prm['RequestUploadT_OrderHeaderID'];
            $RequestUploadOrderPatientID = $prm['RequestUploadOrderPatientID'];

            // echo $RequestUploadT_OrderHeaderID;
            // echo $RequestUploadOrderPatientID;

            $sql_update = "UPDATE $this->db_str_doctor.request_upload
            SET RequestUploadIsUploaded = 'R'
            WHERE RequestUploadT_OrderHeaderID = ?
            AND RequestUploadOrderPatientID = ?";

            $qry = $this->db->query($sql_update, [
                $RequestUploadT_OrderHeaderID,
                $RequestUploadOrderPatientID
            ]);

            if (!$qry) {
                $this->db->trans_rollback();
                $this->sys_error_db("update RequestUploadIsUploaded to R failed");
                exit;
            }


            $result = array(
                "total" => 1,
                "records" => $prm
            );
            $this->sys_ok($result);
            exit;
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    public function process_request_upload()
    {
        try {
            # cek token valid
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }

            $prm = $this->sys_input;
            $userID = $this->sys_user['M_UserID'];
            $RequestUploadT_OrderHeaderID = $prm['RequestUploadT_OrderHeaderID'];
            $RequestUploadOrderPatientID = $prm['RequestUploadOrderPatientID'];

            // echo $RequestUploadT_OrderHeaderID;
            // echo $RequestUploadOrderPatientID;

            $sql_insert = "INSERT INTO $this->db_str_doctor.request_upload(
                RequestUploadT_OrderHeaderID,
                RequestUploadOrderPatientID,
                RequestUploadUserID
            ) values (
                ?,
                ?,
                ?
            )";

            $qry = $this->db->query($sql_insert, [
                $RequestUploadT_OrderHeaderID,
                $RequestUploadOrderPatientID,
                $userID
            ]);

            if (!$qry) {
                $this->db->trans_rollback();
                $this->sys_error_db("insert request upload failed");
                exit;
            }


            $result = array(
                "total" => 1,
                "records" => $prm
            );
            $this->sys_ok($result);
            exit;
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
}