<?php
class Admin_v2 extends MY_Controller
{
    var $db;
    public function index()
    {
        // $cek = $this->db->query("select database() as current_db")->result();
        // print_r($cek);
        echo "ADMIN MCU API";
    }

    public function __construct()
    {
        parent::__construct();
    }

    function search()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;
            $userid = $this->sys_user['M_UserID'];

            $search = "";
            if (isset($prm["search"])) {
                $search = trim($prm["search"]);
                if ($search != "") {
                    $search = "%" . $prm["search"] . "%";
                } else {
                    $search = "%%";
                }
            }

            $number_offset = 0;
            $number_limit = 10;

            if ($prm["current_page"] > 0) {
                $number_offset = ($prm["current_page"] - 1) * $number_limit;
            }

            $sql = "SELECT 
                    Mgm_McuID,
                    Mgm_McuCorporateID,
                    Mgm_McuNumber,
                    Mgm_McuNumberNational,
                    Mgm_McuStartDate,
                    Mgm_McuEndDate,
                    Mgm_McuTotalParticipant,
                    M_CompanyName ,
                    M_CompanyID,
                    'N' as readyconfirm
                FROM 
                    (SELECT 
                        Mgm_McuID,
                        Mgm_McuCorporateID,
                        Mgm_McuNumber,
                        Mgm_McuNumberNational,
                        Mgm_McuStartDate,
                        Mgm_McuEndDate,
                        Mgm_McuTotalParticipant,
                        CorporateName AS M_CompanyName,
                        CorporateID AS M_CompanyID
                    FROM mgm_mcu 
                        JOIN corporate ON Mgm_McuCorporateID = CorporateID
                        AND CorporateIsActive = 'Y'
                        AND Mgm_McuIsActive = 'Y'
                        AND (Mgm_McuNumber LIKE '{$search}' OR CorporateName LIKE '{$search}')
                        GROUP BY Mgm_McuID) AS subquery
                GROUP BY Mgm_McuID, M_CompanyName
                ORDER BY Mgm_McuID DESC";

            $sql_total = "SELECT COUNT(*) as total FROM ($sql) as x";
            $qry_total = $this->db->query($sql_total);

            $last_qry = $this->db->last_query();
            $tot_count = 0;
            $tot_page = 0;
            if ($qry_total) {
                $tot_count = $qry_total->result_array()[0]["total"];
                $tot_page = ceil($tot_count / $number_limit);
            } else {
                $this->db->trans_rollback();
                $message['last_qry'] = $last_qry;
                $this->sys_error_db("mgm_mcu count error", $this->db);
                exit;
            }

            $sql_select = $sql . " LIMIT $number_limit OFFSET $number_offset";
            $qry_select = $this->db->query($sql_select);
            $last_qry = $this->db->last_query();
            if (!$qry_select) {
                $message = $this->db->error();
                $message['last_qry'] = $last_qry;
                $this->sys_error($message);
                exit;
            }
            $rows = $qry_select->result_array();

            foreach ($rows as $k => $v) {
                $sql = $this->db_onedev->query("SELECT count(*) as x FROM mcu_order
                WHERE Mcu_OrderMgm_McuID = '{$v['Mgm_McuID']}' AND Mcu_OrderStatus = 'N'")->row();		
                $svalue = $sql->x;
                if($svalue > 0){
                    $rows[$k]['readyconfirm'] = 'Y';
                }else{
                    $rows[$k]['readyconfirm'] = 'N';
                }
            }

            $result = [
                "records" => $rows,
                "total_page" => $tot_page,
                "total" => $tot_count,
                "last_qry" => $last_qry
            ];
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function list_mcu_order()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;
            $userid = $this->sys_user['M_UserID'];

            $mgmmcuid = 0;
            if (isset($prm['mgmmcuid'])) {
                $mgmmcuid = trim($prm["mgmmcuid"]);
            }

            $search = "";
            if (isset($prm["search"])) {
                $search = trim($prm["search"]);
                if ($search != "") {
                    $search = "%" . $prm["search"] . "%";
                } else {
                    $search = "%%";
                }
            }

            $number_offset = 0;
            $number_limit = 10;

            if ($prm["current_page"] > 0) {
                $number_offset = ($prm["current_page"] - 1) * $number_limit;
            }

            $sql_total = "SELECT count(*) as total
                FROM mcu_order
                JOIN mgm_mcu ON Mcu_OrderMgm_McuID = Mgm_McuID
                AND Mcu_OrderIsactive = 'Y'
                JOIN t_orderheader ON Mcu_OrderT_OrderHeaderID = T_OrderHeaderID
                AND T_OrderHeaderIsActive = 'Y'
                JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID
                JOIN m_title ON M_PatientM_TitleID = M_TitleID
                -- JOIN m_sex ON M_PatientM_SexID = M_SexID
                WHERE Mgm_McuID = ?
                AND (T_OrderHeaderLabNumber LIKE ? OR concat(M_TitleName,'. ',M_PatientName) LIKE ?)";
            $qry_total = $this->db->query($sql_total, [$mgmmcuid, $search, $search]);
            $last_qry = $this->db->last_query();
            $tot_count = 0;
            $tot_page = 0;
            if ($qry_total) {
                $tot_count = $qry_total->result_array()[0]["total"];
                $tot_page = ceil($tot_count / $number_limit);
            } else {
                $this->db->trans_rollback();
                $message['last_qry'] = $last_qry;
                $this->sys_error_db("mgm_order count error", $this->db);
                exit;
            }

            $sql = "SELECT Mcu_OrderID,
                Mcu_OrderMgm_McuID,
                Mcu_OrderT_OrderHeaderID,
                Mcu_OrderStatus,
                Mcu_OrderIsError,
                Mcu_OrderRetry,
                T_OrderHeaderID,
                T_OrderHeaderDate,
                T_OrderHeaderLabNumber,
                concat(M_TitleName,'. ',M_PatientName) as M_PatientName,
                M_PatientNoReg
                FROM mcu_order
                JOIN mgm_mcu ON Mcu_OrderMgm_McuID = Mgm_McuID
                AND Mcu_OrderIsactive = 'Y'
                JOIN t_orderheader ON Mcu_OrderT_OrderHeaderID = T_OrderHeaderID
                AND T_OrderHeaderIsActive = 'Y'
                JOIN m_patient ON T_OrderHeaderM_PatientID = M_PatientID
                JOIN m_title ON M_PatientM_TitleID = M_TitleID
                -- JOIN m_sex ON M_PatientM_SexID = M_SexID
                WHERE Mgm_McuID = ?
                AND (T_OrderHeaderLabNumber LIKE ? OR concat(M_TitleName,'. ',M_PatientName) LIKE ?)
                limit ? offset ?";
            $qry = $this->db->query($sql, [$mgmmcuid, $search, $search, $number_limit, $number_offset]);
            $last_qry = $this->db->last_query();
            if (!$qry) {
                $message = $this->db->error();
                $message['last_qry'] = $last_qry;
                $this->sys_error($message);
                exit;
            }
            $rows = $qry->result_array();
            $result = [
                "records" => $rows,
                "total_page" => $tot_page,
                "total" => $tot_count,
                "last_qry" => $last_qry
            ];
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function search_company()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $prm = $this->sys_input;

            $search = "";
            $number_limit = 10;
            $tot_count = 0;

            if (isset($prm['search'])) {
                $search = trim($prm["search"]);
                if ($search != "") {
                    $search = '%' . $prm['search'] . '%';
                } else {
                    $search = '%%';
                }
            }

            $sql_filter = "SELECT count(*) as total
                FROM corporate
                WHERE CorporateIsActive = 'Y'
                AND (CorporateName LIKE ?)
                LIMIT ?";
            $qry_filter = $this->db->query($sql_filter, [$search, $number_limit]);
            if ($qry_filter) {
                $tot_count = $qry_filter->result_array()[0]["total"];
            } else {
                $this->sys_error_db("corporate count");
                exit;
            }

            $sql_search = "SELECT CorporateID AS M_CompanyID,
                CorporateName AS M_CompanyName,
                CorporateCode As M_CompanyNumber
                FROM corporate
                WHERE CorporateIsActive = 'Y'
                AND (CorporateName LIKE ?)
                LIMIT ?";
            $qry_search = $this->db->query($sql_search, [$search, $number_limit]);
            if ($qry_search) {
                $rows = $qry_search->result_array();
            } else {
                $this->db->trans_rollback();
                $this->sys_error_db("corporate select error", $this->db);
                exit;
            }

            $result = array(
                "total" => $tot_count,
                "total_display" => sizeof($rows),
                "records" => $rows
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    // function search_mou_multiple()
    // {
    //     try {
    //         if (!$this->isLogin) {
    //             $this->sys_error("Invalid Token");
    //             exit;
    //         }
    //         $prm = $this->sys_input;

    //         $companyId = $prm['companyId'];
    //         $number_limit = 10;
    //         $tot_count = 0;

    //         $sql_filter = "SELECT count(*) as total
    //             FROM m_mou
    //             JOIN m_company ON M_MouM_CompanyID = M_CompanyID
    //             AND M_CompanyIsActive = 'Y'
    //             AND M_MouM_CompanyID = ?
    //                 WHERE M_MouIsActive = 'Y'
    //                 AND M_MouIsReleased = 'Y'";
    //         $qry_filter = $this->db->query($sql_filter, [$companyId]);
    //         if ($qry_filter) {
    //             $tot_count = $qry_filter->result_array()[0]["total"];
    //         } else {
    //             $this->sys_error_db("mou count");
    //             exit;
    //         }

    //         $sql_search = "SELECT M_CompanyID,
    //             M_MouID,
    //             M_MouName,
    //             M_MouNumber
    //             FROM m_mou
    //             JOIN m_company ON M_MouM_CompanyID = M_CompanyID
    //             AND M_CompanyIsActive = 'Y'
    //             AND M_MouM_CompanyID = ?
    //                 WHERE M_MouIsActive = 'Y'
    //                 AND M_MouIsReleased = 'Y'";
    //         $qry_search = $this->db->query($sql_search, [$companyId]);
    //         if ($qry_search) {
    //             $rows = $qry_search->result_array();
    //         } else {
    //             $this->db->trans_rollback();
    //             $this->sys_error_db("mou select error", $this->db);
    //             exit;
    //         }

    //         $result = array(
    //             "total" => $tot_count,
    //             "total_display" => sizeof($rows),
    //             "records" => $rows
    //         );
    //         $this->sys_ok($result);
    //     } catch (Exception $exc) {
    //         $message = $exc->getMessage();
    //         $this->sys_error($message);
    //     }
    // }

    function addMcu()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $this->db->trans_begin();
            $prm = $this->sys_input;

            $sql_mcu = "INSERT INTO mgm_mcu(
                Mgm_McuCorporateID,
                Mgm_McuNumber,
                Mgm_McuNumberNational,
                Mgm_McuStartDate,
                Mgm_McuEndDate,
                Mgm_McuIsActive,
                Mgm_McuCreated,
                Mgm_McuLastUpdated,
                Mgm_McuTotalParticipant
            ) VALUES (
                '{$prm['companyId']}',
                fn_numbering('ETL.MCU'),
                '{$prm['nationalNumber']}',
                '{$prm['startdate']}',
                '{$prm['enddate']}',
                'Y',
                NOW(),
                NOW(),
                '{$prm['participant']}'
            )";
            $qry_mcu = $this->db->query($sql_mcu);
            $last_qry = $this->db->last_query();
            if (!$qry_mcu) {
                $this->db->trans_rollback();
                $error = array(
                    "message" => $this->db->error()["message"],
                    "sql" => $last_qry
                );
                $this->sys_error_db($error, $this->db);
                exit;
            }

            $last_id = $this->db->insert_id();

            $this->db->trans_commit();
            $result = array(
                "total" => 1,
                "affected_rows" => $this->db->affected_rows()
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function insert_mcu_order()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $this->db->trans_begin();
            $prm = $this->sys_input;

            $mgmmcuid = $prm['mgmmcuid'];
            // $orderheaderid = $prm['orderheaderid'];

            $sql_select = "SELECT Mgm_McuID,
                T_OrderHeaderID 
                FROM mgm_mcu 
                JOIN t_orderheader ON T_OrderHeaderMgm_McuID = Mgm_McuID
                AND Mgm_McuID = ?
                AND Mgm_McuIsActive = 'Y' 
                AND T_OrderHeaderDate >= Mgm_McuStartDate 
                AND T_OrderHeaderDate <= Mgm_McuEndDate
                AND T_OrderHeaderIsActive = 'Y'
                AND T_OrderHeaderID NOT IN (
                SELECT Mcu_OrderT_OrderHeaderID
                FROM mcu_order 
                WHERE Mcu_OrderMgm_McuID = ?
                AND Mcu_OrderIsActive = 'Y')";
            $qry_select = $this->db->query($sql_select, array($mgmmcuid, $mgmmcuid));
            if ($qry_select) {
                $rows = $qry_select->result_array();
            } else {
                $this->db->trans_rollback();
                $this->sys_error_db("orderheader select error", $this->db);
                exit;
            }

            if (count($rows) > 0) {
                foreach ($rows as $key => $value) {
                    // print_r($value);
                    $orderheaderid = $value['T_OrderHeaderID'];
                    // print_r($orderheaderid);
                    $sql = "INSERT INTO mcu_order(
                        Mcu_OrderMgm_McuID,
                        Mcu_OrderT_OrderHeaderID,
                        Mcu_OrderCreated,
                        Mcu_OrderLastUpdated) VALUES(?,?,NOW(),NOW())";
                    $qry = $this->db->query($sql, array(
                        $mgmmcuid,
                        $orderheaderid
                    ));
                    $last_qry = $this->db->last_query();
                    if (!$qry) {
                        $this->db->trans_rollback();
                        $error = array(
                            "message" => $this->db->error()["message"],
                            "sql" => $last_qry
                        );
                        $this->sys_error_db($error, $this->db);
                        exit;
                    }
                }
            }

            $this->db->trans_commit();
            $result = array(
                "total" => count($rows),
                "affected_rows" => $this->db->affected_rows()
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function confirm_mcu_order()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $this->db->trans_begin();
            $prm = $this->sys_input;
            $mgmmcuid = $prm['mgmmcuid'];

            $sql = "UPDATE mcu_order SET
                Mcu_OrderStatus = 'C',
                Mcu_OrderLastUpdated = NOW()
                WHERE Mcu_OrderMgm_McuID = ?";
            $res = $this->db->query($sql, array($mgmmcuid));
            if (!$res) {
                $this->db->trans_rollback();
                $this->sys_error_db('error', 'Error confirm: ' . $this->db);
                exit;
            }

            $this->db->trans_commit();
            $result = array(
                "total" => 1,
                "records" => array("xid" => 0)
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function editMcu()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $this->db->trans_begin();
            $prm = $this->sys_input;
            // $selectedMou = $prm['selectedMou'];
            $mgmmcuid = $prm['mgmmcuid'];

            $sql_order = "SELECT Mcu_OrderID,
                Mcu_OrderMgm_McuID,
                Mcu_OrderT_OrderHeaderID
                FROM mcu_order
                WHERE Mcu_OrderMgm_McuID = ?";
            $qry_order = $this->db->query($sql_order, [$mgmmcuid]);
            if ($qry_order) {
                $rows = $qry_order->result_array();
            } else {
                $this->db->trans_rollback();
                $this->sys_error_db("mcu_order select error", $this->db);
                exit;
            }

            if (count($rows) > 0) {
                $result = json_encode(["status" => "OK", "message" => "Tidak bisa di edit karena sudah melakukan generate order!"]);
                echo $result;
                exit;
            }

            $sql_update = "UPDATE mgm_mcu SET 
                Mgm_McuCorporateID = ?,
                Mgm_McuNumberNational = ?,
                Mgm_McuStartDate = ?,
                Mgm_McuEndDate = ?,
                Mgm_McuLastUpdated = NOW(),
                Mgm_McuTotalParticipant = ?
                WHERE Mgm_McuID = ?";
            $qry_update = $this->db->query($sql_update, [
                $prm['companyId'],
                $prm['nationalNumber'],
                $prm['startdate'],
                $prm['enddate'],
                $prm['participant'],
                $mgmmcuid
            ]);
            if (!$qry_update) {
                $this->db->trans_rollback();
                $this->sys_error_db("update mgm_mcu error", $this->db);
                exit;
            }

            // // Ambil data Mgm_MouM_MouID
            // $q = "SELECT Mgm_MouM_MouID
            //         FROM one_etl.mgm_mou
            //         WHERE Mgm_MouMgm_McuID = ?";
            // $qry_mou = $this->db->query($q, array($mgmmcuid));

            // if (!$qry_mou) {
            //     $this->db->trans_rollback();
            //     $this->sys_error_db('error', 'Error select mgm_mou: ' . $this->db);
            //     exit;
            // }
            // $rows = $qry_mou->result_array();

            // $all = array();
            // foreach ($rows as $row) {
            //     $all[] = $row['Mgm_MouM_MouID'];
            // }

            // print_r($all);
            // print_r($selectedMou);
            // exit;
            // Cek dan tambahkan data yang baru
            // foreach ($selectedMou as $v) {

            //     // print_r($v);
            //     // print_r($all);

            //     // echo "\n status : ";
            //     // print_r(in_array($v, $all));

            //     if (!in_array($v, $all)) {
            //         // Jika data belum ada, tambahkan ke database
            //         $q = "SELECT Mgm_MouMgm_McuID
            //                 FROM one_etl.mgm_mou
            //                 WHERE Mgm_MouMgm_McuID = ? AND Mgm_MouM_MouID = ?";
            //         $res = $this->db->query($q, array($mgmmcuid, $v));

            //         if (!$res) {
            //             $this->db->trans_rollback();
            //             $this->sys_error_db('error', 'Error Select count: ' . $this->db);
            //             exit;
            //         }

            //         $rows = $res->result_array();
            //         // echo $this->db->last_query();
            //         // print_r($rows);
            //         // exit;

            //         if (count($rows) > 0) {
            //             $q = "UPDATE one_etl.mgm_mou SET
            //                     Mgm_MouIsActive = 'Y',
            //                     Mgm_MouLastUpdated = NOW(),
            //                     Mgm_MouUserID = $userid
            //                     WHERE Mgm_MouMgm_McuID = ? AND Mgm_MouM_MouID = ?";
            //             $res = $this->db->query($q, array($mgmmcuid, $v));

            //             if (!$res) {
            //                 $this->db->trans_rollback();
            //                 $this->sys_error_db('error', 'Error update: ' . $this->db);
            //                 exit;
            //             }
            //         } else {
            //             $q = "INSERT INTO one_etl.mgm_mou(
            //                     Mgm_MouM_MouID,
            //                     Mgm_MouMgm_McuID,
            //                     Mgm_MouCreated,
            //                     Mgm_MouUserID
            //                     ) VALUES(?,?,NOW(),?)";

            //             $res = $this->db->query($q, array($v, $mgmmcuid, $userid));

            //             if (!$res) {
            //                 $this->db->trans_rollback();
            //                 $this->sys_error_db('error', 'Error insert: ' . $this->db);
            //                 exit;
            //             }
            //         }
            //     } else {
            //         $q = "UPDATE one_etl.mgm_mou SET
            //             Mgm_MouIsActive = 'Y',
            //             Mgm_MouLastUpdated = NOW(),
            //             Mgm_MouUserID = $userid
            //             WHERE Mgm_MouMgm_McuID = ? and Mgm_MouM_MouID = ? and Mgm_MouIsActive = 'N'";
            //         $res = $this->db->query($q, array($mgmmcuid, $v));

            //         // echo $this->db->last_query();
            //         if (!$res) {
            //             $this->db->trans_rollback();
            //             $this->sys_error_db('error', 'Error update active: ' . $this->db);
            //             exit;
            //         }
            //     }
            // }


            // update yang gak ada di selectedmou
            // $select = implode(",", $selectedMou);
            // // print_r($select);

            // if ($select != "") {
            //     $q = "UPDATE one_etl.mgm_mou SET
            //         Mgm_MouIsActive = 'N',
            //         Mgm_MouLastUpdated = NOW(),
            //         Mgm_MouUserID = $userid
            //         WHERE Mgm_MouMgm_McuID = ? and Mgm_MouM_MouID NOT IN ($select)";
            //     $res = $this->db->query($q, array($mgmmcuid));
            //     if (!$res) {
            //         $this->db->trans_rollback();
            //         $this->sys_error_db('error', 'Error update delete: ' . $this->db);
            //         exit;
            //     }
            // }

            // $this->db->last_query();


            $this->db->trans_commit();
            $result = array(
                "total" => 1,
                "records" => array("xid" => 0)
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function deleteMcu()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $this->db->trans_begin();
            $prm = $this->sys_input;
            $mgmmcuid = $prm['mgmmcuid'];

            $sql_order = "SELECT Mcu_OrderID,
                Mcu_OrderMgm_McuID,
                Mcu_OrderT_OrderHeaderID
                FROM mcu_order
                WHERE Mcu_OrderMgm_McuID = ?";
            $qry_order = $this->db->query($sql_order, [$mgmmcuid]);
            if ($qry_order) {
                $rows = $qry_order->result_array();
            } else {
                $this->db->trans_rollback();
                $this->sys_error_db("mcu_order select error", $this->db);
                exit;
            }

            if (count($rows) > 0) {
                $result = json_encode(["status" => "OK", "message" => "Tidak bisa di hapus karena sudah melakukan generate order!"]);
                echo $result;
                exit;
            }

            $sql = "UPDATE mgm_mcu SET
                Mgm_McuIsActive = 'N',
                Mgm_McuLastUpdated = NOW()
                WHERE Mgm_McuID = ?";
            $res = $this->db->query($sql, array( $mgmmcuid));
            if (!$res) {
                $this->db->trans_rollback();
                $this->sys_error_db('error', 'Error delete: ' . $this->db);
                exit;
            }

            $this->db->trans_commit();
            $result = array(
                "total" => 1,
                "records" => array("xid" => 0)
            );
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
}