<?php

class Deliveryorder extends MY_Controller
{
    var $db_regional;
    var $load;
    var $db;
    public function __construct()
    {
        parent::__construct();
        $this->db_regional = $this->load->database("regional", true);
        if (!$this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }

        $userID = $this->sys_user['M_UserID'];

        $sql_cek_token = "SELECT  M_UserActiveToken
        from one_mitra.m_user
        WHERE M_UserID = ?
        AND M_UserActiveToken IS NOT NULL";

        $qry_token = $this->db->query($sql_cek_token, [$userID]);
        if (!$qry_token) {
            $this->sys_error('Invalid token');
            exit;
        }

        $rows_token = $qry_token->result_array();
        if (count($rows_token) == 0) {
            $this->sys_error('Invalid token');
            exit;
        }
    }
    function getdeliverytype()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $sql = "SELECT 
                    T_DeliveryTypeID AS id,
                    T_DeliveryTypeName AS name,
                    T_DeliveryTypeIsAgent AS isAgent
                    FROM one_mitra.t_deliverytype
                    WHERE T_DeliveryTypeIsActive = 'Y'";
            $query = $this->db->query($sql, []);
            if (!$query) {
                $message = $this->db->error();
                $this->sys_error($message);
                exit;
            }
            $search = $query->result_array();
            $this->sys_ok($search);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function getorder()
    {
        try {
            $prm = $this->sys_input;
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $userID = $this->sys_user['M_UserID'];
            $company_id = 0;
            if (isset($prm['company_id'])) {
                $company_id = trim($prm["company_id"]);
                $company_id =  $prm['company_id'];
            } else {
                $this->sys_error("company_id is mandatory");
            }
            $regional_id = 0;
            if (isset($prm['regional_id'])) {
                $regional_id = trim($prm["regional_id"]);
                $regional_id =  $prm['regional_id'];
            } else {
                $this->sys_error("regional_id is mandatory");
            }
            $sql = "SELECT 
                    T_OrderID AS order_id,
                    T_OrderNumber AS order_number,
                    M_PatientID AS patient_id,
                    M_PatientName AS patient_name,
                    T_OrderM_MouID AS mouID,
                    GROUP_CONCAT(DISTINCT T_OrderDetailTestName SEPARATOR '|') AS test,
                    GROUP_CONCAT(DISTINCT T_OrderDetailPacketName SEPARATOR '|') AS packet
                    FROM one_mitra.t_order
                    JOIN one_mitra.m_patient ON T_OrderM_PatientID = M_PatientID
                    AND M_PatientIsActive = 'Y'
                    LEFT JOIN one_mitra.t_orderdetail
                    ON T_OrderID  = T_OrderDetailOrderID
                    AND T_OrderDetailIsActive = 'Y'
                    LEFT JOIN one_mitra.t_orderdetailpacket
                    ON T_OrderID  = T_OrderDetailPacketOrderID
                    AND T_OrderDetailPacketIsActive = 'Y'
                    WHERE T_OrderM_CompanyID = ?
                    AND T_OrderIsActive = 'Y'
                    AND T_OrderS_RegionalID = ?
                    AND T_OrderID NOT IN (SELECT T_OrderDetailDeliveryT_OrderID FROM 
                    one_mitra.t_orderdetaildelivery WHERE T_OrderDetailDeliveryIsActive ='Y'
                    AND T_OrderDetailDeliveryM_CompanyID = ?)
                    GROUP BY T_OrderID";
            $query = $this->db->query($sql, [$company_id, $regional_id, $company_id]);
            if (!$query) {
                $message = $this->db->error();
                $this->sys_error($message);
                exit;
            }
            $arrOrder = $query->result_array();
            $result = [];
            for ($i = 0; $i < count($arrOrder); $i++) {
                $test = explode('|', $arrOrder[$i]['test']);
                $packet = explode('|', $arrOrder[$i]['packet']);

                $sql = "SELECT 
                M_UserMouID as userMouID,
                M_UserMouM_MouID as userMouMouID,
                M_UserMouAliasName as userMouName,
                M_UserMouIsDefault as userMouIsDefault
                FROM one_mitra.m_user_mou
                WHERE M_UserMouM_UserID = ? AND M_UserMouM_MouID = ?";
                $qry = $this->db_regional->query($sql, [$userID, $arrOrder[$i]['mouID']]);
                if (!$qry) {
                    $this->sys_error('Error get mou');
                    exit;
                }
                $mou = $qry->result_array();
                $fnlMou = array();
                if (count($mou) > 0) {
                    $fnlMou = $mou[0];
                } else {
                    $fnlMou = array(
                        "userMouID" => "0",
                        "userMouMouID" => '0',
                        "userMouName" => '',
                        "userMouIsDefault" => ''
                    );
                }

                $result[] = [
                    "order_id" => $arrOrder[$i]['order_id'],
                    "order_number" => $arrOrder[$i]['order_number'],
                    "patient_id" => $arrOrder[$i]['patient_id'],
                    "patient_name" => $arrOrder[$i]['patient_name'],
                    "sample" => [],
                    "mou" => $fnlMou,
                    "bahan" => [],
                    "tests" => array_merge($test, $packet)
                ];
            };
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function getdestination()
    {
        try {
            $prm = $this->sys_input;
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $sql = "SELECT 
                    M_BranchID AS branch_id,
                    M_BranchCode AS branch_code,
                    M_BranchName branch_name
                    FROM m_branch 
                    WHERE M_BranchIsActive = 'Y'";
            $query = $this->db_regional->query($sql, []);
            if (!$query) {
                $message = $this->db_regional->error();
                $this->sys_error($message);
                exit;
            }
            $search = $query->result_array();
            $this->sys_ok($search);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function addDelivery()
    {
        try {
            $prm = $this->sys_input;
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $order = $prm['order'];
            $orderDetail = $prm['order_detail'];
            // T_OrderDeliveryID	int(11) Auto Increment	
            // T_OrderDeliveryNumber	varchar(25)	
            // T_OrderDeliveryStaffID	int(11)	
            // T_OrderDeliveryNoRef	varchar(25)	
            // T_OrderDeliveryDate	date	
            // T_OrderDeliveryDestination	int(11)	Branch ID
            // T_OrderDeliveryBoxTemperature	varchar(25)	
            // T_OrderDeliveryT_DeliverytypeID	int(11)	
            // T_OrderDeliveryReciptNumber	varchar(40)	
            // T_OrderDeliveryNote	tinytext	
            // T_OrderDeliveryIsActive	char(1) [Y]	
            // T_OrderDeliveryCreated	datetime [current_timestamp()]	
            // T_OrderDeliveryLastUpdated
            $this->db->trans_begin();
            $sql = "SELECT one_mitra.fn_numbering('SJ') as number";
            $qry = $this->db->query($sql, []);
            if (!$qry) {
                $message = $this->db->error();
                $this->db->trans_rollback();
                $this->sys_error($message);
                exit;
            }
            $number = $qry->result_array()[0]['number'];
            $get2first = substr($number, 0, 2);
            $getDate = strval(date("ym"));
            $newNumber = $get2first . $order['branch_code'] . $getDate . substr($number, -3);
            $orderDelivery = [
                "T_OrderDeliveryStaffID" => $order['staff_id'],
                "T_OrderDeliveryNumber" => $newNumber,
                "T_OrderDeliveryNoRef" => $order['no_ref'],
                "T_OrderDeliveryDate" => date('Y-m-d', strtotime($order['date'])),
                "T_OrderDeliveryDestination" => $order['destination_id'],
                "T_OrderDeliveryRegionalID" => $order['regional_id'],
                "T_OrderDeliveryBoxTemperature" => $order['temperature'],
                "T_OrderDeliveryT_DeliverytypeID" => $order['type_id'],
                "T_OrderDeliveryReciptNumber" => $order['no_resi'],
                "T_OrderDeliveryNote" => $order['note'],
                "T_OrderDeliveryM_CompanyID" => $order['company_id'],
            ];
            $this->db->insert('one_mitra.t_orderdelivery', $orderDelivery);
            $err = $this->db->error();
            if (
                $err['message'] != ""
            ) {
                $this->sys_error_db("ERROR INSERT ORDER DELIVERY", $this->db);
                $this->db->trans_rollback();
                exit;
            }
            $deliveryID = $this->db->insert_id();

            for ($i = 0; $i < count($orderDetail); $i++) {
                // Column	Type	Comment
                // T_OrderDetailDeliveryID	int(11) Auto Increment	
                // T_OrderDetailDeliveryT_OrderDeliveryID	int(11)	
                // T_OrderDetailDeliveryT_OrderID	int(11)	
                // T_OrderDetailDeliveryIsActive	char(1) [Y]	
                // T_OrderDetailDeliveryCreated	datetime [current_timestamp()]	
                // T_OrderDetailDeliveryLastUpdated

                $deliveryDetail = [
                    "T_OrderDetailDeliveryT_OrderDeliveryID" => $deliveryID,
                    "T_OrderDetailDeliveryT_OrderID" => $orderDetail[$i]['order_id'],
                    "T_OrderDetailDeliveryM_CompanyID" => $order['company_id'],
                ];
                $this->db->insert('one_mitra.t_orderdetaildelivery', $deliveryDetail);
                $err = $this->db->error();
                if (
                    $err['message'] != ""
                ) {
                    $this->sys_error_db("ERROR INSERT ORDER DELIVERY DETAIL", $this->db);
                    $this->db->trans_rollback();
                    exit;
                }
            }
            $this->db->trans_commit();
            $result = [
                "deliveryID" => $deliveryID,
                "orderNumber" => $newNumber,
            ];
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function getdelivery()
    {
        try {
            $prm = $this->sys_input;
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $regionalID = $this->sys_user["M_UserS_RegionalID"];
            // print_r($prm);


            $keyword = '%%';
            if (isset($prm['keyword'])) {
                $keyword = '%' . $prm['keyword'] . '%';
            }
            $keyword = '%%';
            if (isset($prm['keyword'])) {
                $keyword = '%' . $prm['keyword'] . '%';
            }
            $page = $prm['page'];
            $rowPerPage = $prm['rpp'];
            $companyID = $prm['company_id'];
            $startDate = $prm['start_date'];
            $endDate = $prm['end_date'];
            // 1 => tanggal surat jalan
            // 2 => tanggal kedatangan
            $datetype = $prm['date_type'];
            $datetypeSql = "T_OrderDeliveryCreated";
            if (intval($datetype) == 1) {
                $datetypeSql = "T_OrderDeliveryCreated";
            } else if (intval($datetype) == 2) {
                $datetypeSql = "T_OrderDeliveryDate";
            }

            $start_offset = 0;
            if (isset($prm['page'])) {
                if (is_numeric((int)$prm['page']) && $prm['page'] > 0) {
                    $start_offset = ($page - 1) * intval($rowPerPage);
                }
            }
            $sql_total = "SELECT COUNT(T_OrderDeliveryID) AS total
                FROM one_mitra.t_orderdelivery
                JOIN one_mitra.m_user
                ON T_OrderDeliveryStaffID = M_UserID
                AND M_UserIsActive = 'Y'
                JOIN m_branch
                ON T_OrderDeliveryDestination = M_BranchID
                AND M_BranchIsActive = 'Y'
                WHERE 
                T_OrderDeliveryIsActive = 'Y' AND
                T_OrderDeliveryM_CompanyID = ?
                AND (T_OrderDeliveryNumber LIKE ?
                    OR M_UserUsername LIKE ? OR M_BranchName LIKE ?)
                AND T_OrderDeliveryRegionalID = ?
                AND $datetypeSql >= ? AND $datetypeSql <= ?";
            $query_total = $this->db->query($sql_total, [$companyID, $keyword, $keyword, $keyword, $regionalID, $startDate, $endDate]);
            if (!$query_total) {
                $message = $this->db->error();
                $this->sys_error($message);
                exit;
            }
            $last_qry = $this->db->last_query();
            $totals = $query_total->result_array()[0]['total'];
            // print_r($totals);

            $sql = "SELECT 
                T_OrderDeliveryID AS id,
                DATE_FORMAT(T_OrderDeliveryDate, '%d/%m/%Y')  AS date,
                DATE_FORMAT(T_OrderDeliveryCreated, '%d/%m/%Y')  AS date_sj,
                T_OrderDeliveryNumber AS order_number,
                M_UserUsername AS pic,
                T_OrderDeliveryStatus AS status,
                M_BranchName AS destination
                FROM one_mitra.t_orderdelivery
                JOIN one_mitra.m_user
                ON T_OrderDeliveryStaffID = M_UserID
                AND M_UserIsActive = 'Y'
                JOIN m_branch
                ON T_OrderDeliveryDestination = M_BranchID
                AND M_BranchIsActive = 'Y'
                WHERE 
                T_OrderDeliveryIsActive = 'Y' AND
                T_OrderDeliveryM_CompanyID = ?
                AND (T_OrderDeliveryNumber LIKE ?
                    OR M_UserUsername LIKE ? OR M_BranchName LIKE ?)
                AND T_OrderDeliveryRegionalID = ?
                AND DATE_FORMAT($datetypeSql, '%Y-%m-%d') >= ? AND DATE_FORMAT($datetypeSql, '%Y-%m-%d') <= ?
                ORDER BY $datetypeSql DESC
                LIMIT ? OFFSET ?";
            $query = $this->db->query($sql, [$companyID, $keyword, $keyword, $keyword,  $regionalID, $startDate, $endDate, intval($rowPerPage), intval($start_offset)]);
            if (!$query) {
                $message = $this->db->error();
                $this->sys_error($message);
                exit;
            }
            $search = $query->result_array();
            // print_r($search);
            $result = [
                "data" => $search,
                "total" => $totals,
                "total_page" => ceil($totals / $rowPerPage),
                "qry_total" => $this->db->last_query(),
            ];
            $this->sys_ok($result);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function detaildelivery()
    {
        try {
            $prm = $this->sys_input;
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $sql = "SELECT 
                    T_OrderDeliveryID AS delivery_id,
                    T_OrderDeliveryNumber AS delivery_number, 
                    T_OrderNumber AS order_number,
                    T_OrderDetailDeliveryID AS delivery_detail_id,
                    T_OrderDetailDeliveryT_OrderID AS order_id,
                    DATE_FORMAT(T_OrderDate, '%d/%m/%Y')  AS date,
                    M_PatientName AS patient_name,
                    T_OrderStatus AS status,
                    one_mitra.fn_get_acc_sample(T_OrderDetailDeliveryT_OrderID) AS accepted_sample,
                    one_mitra.fn_get_rejct_sample(T_OrderDetailDeliveryT_OrderID) AS rejected_sample
                    FROM 
                    one_mitra.t_orderdelivery
                    JOIN one_mitra.t_orderdetaildelivery
                    ON T_OrderDeliveryID  = T_OrderDetailDeliveryT_OrderDeliveryID
                    AND T_OrderDetailDeliveryIsActive = 'Y'
                    JOIN one_mitra.t_order 
                    ON T_OrderDetailDeliveryT_OrderID = T_OrderID
                    AND T_OrderDetailDeliveryIsActive = 'Y'
                    AND T_OrderIsActive = 'Y'
                    JOIN one_mitra.m_patient 
                    ON T_OrderM_PatientID = M_PatientID
                    AND M_PatientIsActive = 'Y'
                    WHERE T_OrderDeliveryID = ?
                    AND T_OrderDeliveryIsActive = 'Y'";
            $query = $this->db->query($sql, [$prm['id']]);
            if (!$query) {
                $message = $this->db->error();
                $this->sys_error($message);
                exit;
            }
            $search = $query->result_array();
            $this->sys_ok($search);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function cancel()
    {
        try {
            $prm = $this->sys_input;
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $this->db->trans_begin();
            $sql = "UPDATE one_mitra.t_orderdelivery SET T_OrderDeliveryIsActive = 'N'
                    WHERE T_OrderDeliveryID = ?
                    ";
            $query = $this->db->query($sql, [$prm['id']]);
            if (!$query) {
                $message = $this->db->error();
                $this->sys_error($message);
                $this->db->trans_rollback();
                exit;
            }
            $sql = "UPDATE one_mitra.t_orderdetaildelivery SET T_OrderDetailDeliveryIsActive = 'N'
                    WHERE T_OrderDetailDeliveryT_OrderDeliveryID = ?;
                    ";
            $query = $this->db->query($sql, [$prm['id']]);
            if (!$query) {
                $message = $this->db->error();
                $this->sys_error($message);
                $this->db->trans_rollback();
                exit;
            }

            $this->db->trans_commit();
            $this->sys_ok("ok");
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function send()
    {
        try {
            //             $aql = "UPDATE t_orderdelivery SET T_OrderDeliveryStatus = 'S'
            // WHERE T_OrderDeliveryID = 1;

            // UPDATE t_order SET T_OrderStatus = 'S'
            // WHERE T_OrderID IN (
            // SELECT T_OrderDetailDeliveryT_OrderID 
            // FROM t_orderdetaildelivery 
            // WHERE T_OrderDetailDeliveryT_OrderDeliveryID = 1)";

            $prm = $this->sys_input;
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $this->db->trans_begin();
            $sql = "UPDATE one_mitra.t_orderdelivery SET T_OrderDeliveryStatus = 'S'
                    WHERE T_OrderDeliveryID = ?;

                    ";
            $query = $this->db->query($sql, [$prm['id']]);
            if (!$query) {
                $message = $this->db->error();
                $this->sys_error($message);
                $this->db->trans_rollback();
                exit;
            }
            $sql = "UPDATE one_mitra.t_order SET T_OrderStatus = 'S'
                    WHERE T_OrderID IN (
                    SELECT T_OrderDetailDeliveryT_OrderID 
                    FROM one_mitra.t_orderdetaildelivery 
                    WHERE T_OrderDetailDeliveryT_OrderDeliveryID = ?)
                    ";
            $query = $this->db->query($sql, [$prm['id']]);
            if (!$query) {
                $message = $this->db->error();
                $this->sys_error($message);
                $this->db->trans_rollback();
                exit;
            }

            $this->db->trans_commit();
            $this->sys_ok("ok");
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    function getregional()
    {
        try {

            $sql_regional = "SELECT 
                            S_RegionalID AS regional_id,
                            S_RegionalName AS regional_name
                            FROM s_regional WHERE S_RegionalIsActive = 'Y'";
            $query_regional = $this->db->query($sql_regional, []);
            if (!$query_regional) {
                $message = $this->db->error();
                $this->sys_error($message);
                exit;
            }
            $regionals = $query_regional->result_array();
            $sql_branch = "SELECT 
                            M_BranchID AS branch_id,
                            M_BranchCode AS branch_code,
                            M_BranchName AS branch_name,
                            M_BranchS_RegionalID AS regional_id
                            FROM m_branch Where M_BranchIsActive = 'Y'";
            $query_branch = $this->db->query($sql_branch, []);
            if (!$query_branch) {
                $message = $this->db->error();
                $this->sys_error($message);
                exit;
            }
            $branchs = $query_branch->result_array();
            for ($i = 0; $i < count($regionals); $i++) {
                $regionals[$i]['branch'] = [];
            }
            for ($i = 0; $i < count($regionals); $i++) {
                for ($j = 0; $j < count($branchs); $j++) {
                    if ($regionals[$i]['regional_id'] == $branchs[$j]['regional_id']) {
                        $regionals[$i]['branch'][] = $branchs[$j];
                    }
                }
            }
            $this->sys_ok($regionals);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
    function sendqrcode()
    {
        try {
            $prm = $this->sys_input;
            if (!$this->isLogin) {
                $this->sys_error("Invalid Token");
                exit;
            }
            $arr_order_id = 0;
            if (isset($prm['arr_order_id'])) {
                $arr_order_id =  $prm['arr_order_id'];
            } else {
                $this->sys_error("arr_order_id is mandatory");
            }
            $arr_order_id = implode(",", $arr_order_id);

            $sql = "UPDATE one_mitra.t_order 
                    SET T_OrderIsQRCode = 'Y'
                    WHERE T_OrderID IN ($arr_order_id)
                    AND T_OrderIsActive = 'Y'";
            $qry = $this->db->query($sql, []);
            if (!$qry) {
                print_r($this->db->last_query());
                $message = $this->db->error();
                $this->sys_error($message);
                exit;
            }

            $this->sys_ok($this->db->last_query());
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }
}