<?php

class Approvalallrequest extends MY_Controller
{
    var $db;
    public function index()
    {
        echo "API APPROVAL ALL";
        // $cek = $this->db->query("select database() as current_db")->result();
        // print_r($cek);
    }
    public function __construct()
    {
        parent::__construct();
    }

    public function listApprove()
    {
        if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;

        $sql = "SELECT S_MenuID,
                    S_MenuName as menuname,
                    S_MenuName as name,
                    S_MenuRegional,
                    S_MenuUrl,
                    S_MenuIcon,
                    S_MenuParentS_MenuID,
                    S_MenuLevel,
                    S_MenuIsParent,
                    S_MenuOrder,
                    S_MenuIsActive

                FROM s_menu
                WHERE S_MenuID IN (31, 69, 82)";
        $qry = $this->db->query($sql);
        if (!$qry) {
            $this->sys_error_db("Error get list menu");
            exit;
        }

        $rows = $qry->result_array();

        $result = array(
            "records" => $rows
        );
        $this->sys_ok($result);
    }

    public function search()
    {
        if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;

        $user = $this->sys_user;
        $regionalID = $user['S_RegionalID'];
        $branchCode = $user['M_BranchCode'];
        $userID = $user['M_UserID'];
        $approveLevelID = $user['M_UserM_ApproveLevelID'];
        $loginType = $user['M_UserLocationFlag'];
        $sqlBranch = '';
        if ($loginType == 'B' || $loginType == 'RB') {
            $sqlBranch = "AND PurchaseRequestM_BranchCode = '{$branchCode}'";
        }

        $sql = "SELECT m_approve_level.* FROM m_user
                JOIN m_approve_level
                ON M_UserM_ApproveLevelID = M_ApproveLevelID
                WHERE M_UserID = ?;";
        $qry = $this->db->query($sql, [$userID]);
        if (!$qry) {
            $this->sys_error_db("Error cek approval level");
            exit;
        }
        $approvalLevel = $qry->result_array();
        if (count($approvalLevel) == 0) {
            $result = array(
                'total' => 0,
                'records' => []
            );
            $this->sys_ok($result);
            exit;
        }

        $totalStart = $approvalLevel[0]['M_ApproveLevelStartTotal'];
        $totalEnd = $approvalLevel[0]['M_ApproveLevelEndTotal'];

        $params = [$regionalID, $regionalID];

        if ($approveLevelID == '1') {
            // manager -> verifikator
            $sqlSupplierPayment = " AND SupplierPaymentIsVerif = 'N' AND SupplierPaymentStatus != 'Verif' ";
            $sqlStockRequest = " AND (PurchaseRequestVerifiedBy = 0 OR PurchaseRequestVerifiedBy IS NULL) ";
            $sqlPurchaseOrder = " AND PurchaseOrderApprovedManagerUserID = 0";
            $sqlRIO = " AND RequestItemOutVerifiedUserID = 0";
        } else if ($approveLevelID == '2') {
            // kacab / regional -> approver
            $sqlSupplierPayment = " AND SupplierPaymentIsApproved = 'N' AND SupplierPaymentStatus = 'Verif'";
            $sqlStockRequest = " AND PurchaseRequestApprovedBy IS NULL";
            $sqlPurchaseOrder = " AND PurchaseOrderApprovedUserID IS NULL";
            $sqlRIO = " AND RequestItemOutVerifiedUserID != 0";
        }
        $sqlSelect = "SELECT
                        COUNT(*) total,
                        GROUP_CONCAT(PurchaseRequestNumber SEPARATOR ', ') AS number,
                        MIN(PurchaseRequestDate) AS date,
                        PurchaseRequestStatus as status,
                        'STOCK REQUEST (P)' as type,
                        'p' as flag
                    FROM purchase_request
                    WHERE PurchaseRequestIsActive = 'Y'
                    AND PurchaseRequestStatus = 'Pending'
                    AND PurchaseRequestItemCategoryID = 1
                    AND PurchaseRequestS_RegionalID = ?
                    $sqlBranch
                    $sqlStockRequest
                
                UNION 

                SELECT
                    COUNT(*) total,
                    GROUP_CONCAT(PurchaseRequestNumber SEPARATOR ', ') AS number,
                    MIN(PurchaseRequestDate) AS date,
                    PurchaseRequestStatus as status,
                    'PURCHASE REQUEST (NP)' as type,
                    'np' as flag
                FROM purchase_request
                WHERE PurchaseRequestIsActive = 'Y'
                AND PurchaseRequestStatus = 'Pending'
                AND PurchaseRequestItemCategoryID <> 1
                AND PurchaseRequestS_RegionalID = ?
                $sqlBranch
                $sqlStockRequest

                UNION
                
                SELECT COUNT(*) as total,
                    GROUP_CONCAT(PurchaseOrderNumber SEPARATOR ', ') AS number,
                    MIN(PurchaseOrderDate) AS date,
                    PurchaseOrderStatus as status,
                    'PURCHASE ORDER' as type,
                    'po' as flag
                FROM purchase_order
                WHERE PurchaseOrderIsActive = 'Y'
                AND PurchaseOrderStatus = 'Pending'
                $sqlPurchaseOrder

                UNION

                SELECT COUNT(*) as total,
                    GROUP_CONCAT(PurchaseRequestDirectNumber SEPARATOR ', ') as number,
                    MIN(PurchaseRequestDirectDate) as date,
                    PurchaseRequestDirectStatus as status,
                    'PURCHASE REQUEST DIRECT' as type,
                    'prd' as flag
                FROM purchase_request_direct
                WHERE PurchaseRequestDirectStatus = 'Pending'
                AND PurchaseRequestDirectTotalEstimation BETWEEN $totalStart AND $totalEnd
                AND PurchaseRequestDirectIsActive = 'Y'
                    
                UNION

                SELECT COUNT(*) as total,
                    GROUP_CONCAT(PaymentVoucherNumber SEPARATOR ', ') as number,
                    MIN(DATE(PaymentVoucherDate)) as date,
                    PaymentVoucherStatus as status,
                    'KASIR (REALISASI)' as type,
                    'r' as flag
                FROM payment_voucher
                WHERE PaymentVoucherIsActive = 'Y'
                AND PaymentVoucherStatus = 'Paid'

                UNION

                SELECT 
                COUNT(*) as total,
                    GROUP_CONCAT(SupplierInvoiceNumber SEPARATOR ', ') as number,
                  MIN(DATE(SupplierInvoiceDraftPaymentDate)) as date,
                    SupplierPaymentStatus as status,
                    'PAYMENT APPROVED' as type,
                    'pa' as flag
				FROM supplier_payment
				LEFT JOIN supplier_invoice ON SupplierPaymentSupplierInvoiceID = SupplierInvoiceID
				WHERE SupplierPaymentIsActive = 'Y'
                $sqlSupplierPayment
                
                UNION
                
                SELECT COUNT(*) as total,
                    GROUP_CONCAT(RequestItemOutNumber SEPARATOR ', ') as number,
                    MIN(DATE(RequestItemOutDate)) as date,
                    IF(RequestItemOutStatus = 'Send Request' , 'SendRequest', '') as status,
                    'REQUEST PENGELUARAN BARANG' as type,
                    'rio' as flag
                FROM request_item_out
                WHERE RequestItemOutIsActive = 'Y'
                AND RequestItemOutStatus = 'Send Request'
                $sqlRIO
                ";

        $qry_end = $this->db->query($sqlSelect, $params);
        if ($qry_end) {
            $rows = $qry_end->result_array();
        } else {
            $this->sys_error_db("Error searching");
            exit;
        }

        // echo $this->db->last_query();
        // exit;

        $result = array(
            'records' => $rows,
            "qry" => $this->db->last_query()
        );

        $this->sys_ok($result);
    }
    public function searchold()
    {
        if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;
        $search = '%' . $prm['search'] . '%';

        $date = $prm['date'];
        $status = $prm['status'];
        $page = $prm['page'];
        $user = $this->sys_user;
        $regionalID = $user['S_RegionalID'];
        $branchCode = $user['M_BranchCode'];
        $loginType = $user['M_UserLocationFlag'];
        $userID = $user['M_UserID'];

        $sqlBranch = '';
        if ($loginType == 'B' || $loginType == 'RB') {
            $sqlBranch = "AND PurchaseRequestM_BranchCode = '{$branchCode}'";
        }

        $sql = "SELECT m_approve_level.* FROM m_user
                JOIN m_approve_level
                ON M_UserM_ApproveLevelID = M_ApproveLevelID
                WHERE M_UserID = ?;";
        $qry = $this->db->query($sql, [$userID]);
        if (!$qry) {
            $this->sys_error_db("Error cek approval level");
            exit;
        }
        $approvalLevel = $qry->result_array();
        if (count($approvalLevel) == 0) {
            $result = array(
                'total' => 0,
                'records' => []
            );
            $this->sys_ok($result);
            exit;
        }

        $params = [$search, $search, $regionalID, $search];

        // $sql = "SELECT
        //         PurchaseRequestID prID,
        //         DATE_FORMAT(PurchaseRequestDate, '%d-%m-%Y') prDate,
        //         PurchaseRequestNumber prNumber,
        //         PurchaseRequestRefNumber prRefNumber,
        //         PurchaseRequestNote prNote,
        //         PurchaseRequestTotal prTotal,
        //         PurchaseRequestItemCategoryID prItemType,
        //         ItemCategoryName prItemTypeName,
        //         PurchaseRequestStatus prStatus,
        //         DATE_FORMAT(PurchaseRequestApprovedDate, '%d-%m-%Y %H:%i') prApprovedDate,
        //         a.M_userUserName prApprovedBy,
        //         b.M_UserID prRequestedByID,
        //         b.M_userUserName prRequestedBy,
        //         b.M_UserM_BranchID prRequestedByFromBranch,
        //         S_RegionalName prRegionalName,
        //         M_BranchName prBranchName,
        //         IFNULL(`fn_getitemnamebyprid`(PurchaseRequestID), '') prItemName
        //         FROM purchase_request
        //         JOIN s_regional
        //         ON PurchaseRequestS_RegionalID = S_RegionalID
        //         AND S_RegionalIsActive = 'Y'
        //         JOIN item_category 
        //         ON PurchaseRequestItemCategoryID  = ItemCategoryID
        //         AND ItemCategoryIsActive = 'Y'
        //         JOIN m_user b
        //         ON PurchaseRequestRequestedBy = b.M_UserID
        //         AND b.M_UserIsActive = 'Y'
        //         LEFT JOIN m_user a
        //         ON PurchaseRequestApprovedBy = a.M_UserID
        //         AND a.M_UserIsActive = 'Y'
        //         LEFT JOIN 
        //         m_branch
        //         ON PurchaseRequestM_BranchCode = M_BranchCode
        //         WHERE PurchaseRequestIsActive = 'Y'
        //         AND (PurchaseRequestNumber LIKE ? OR PurchaseRequestRefNumber LIKE ? OR  PurchaseRequestRequestedBy LIKE ?)
        //         AND PurchaseRequestS_RegionalID = ?
        //         AND PurchaseRequestStatus = 'Pending'
        //         $sqlBranch
        //         ORDER BY prDate,prNumber
        //         DESC
        //         LIMIT ? OFFSET ?";

        $sqlSelect = "SELECT
                        PurchaseRequestID AS ID,
                        'PR' AS SourceType,
                        DATE_FORMAT(PurchaseRequestDate, '%d-%m-%Y') AS Date,
                        PurchaseRequestNumber AS Number,
                        PurchaseRequestRefNumber AS RefNumber,
                        PurchaseRequestNote AS Note,
                        PurchaseRequestTotal AS Total,
                        PurchaseRequestItemCategoryID AS ItemCategoryID,
                        ItemCategoryName AS ItemCategoryName,
                        PurchaseRequestStatus AS Status,
                        DATE_FORMAT(PurchaseRequestApprovedDate, '%d-%m-%Y %H:%i') AS ApprovedDate,
                        a.M_userUserName AS ApprovedBy,
                        b.M_UserID AS RequestedByID,
                        b.M_userUserName AS RequestedBy,
                        b.M_UserM_BranchID AS RequestedByFromBranch,
                        S_RegionalName AS RegionalName,
                        M_BranchName AS BranchName,
                        IFNULL(`fn_getitemnamebyprid`(PurchaseRequestID), '') AS ItemName,
                        NULL AS DiscountType,
                        NULL AS DiscountAmount,
                        NULL AS TaxAmount,
                        NULL AS SupplierCode,
                        NULL AS SupplierName,
                        NULL AS WarehouseCode,
                        NULL AS WarehouseName,
                        NULL AS DisplayDate
                    FROM purchase_request
                    JOIN s_regional ON PurchaseRequestS_RegionalID = S_RegionalID AND S_RegionalIsActive = 'Y'
                    JOIN item_category ON PurchaseRequestItemCategoryID = ItemCategoryID AND ItemCategoryIsActive = 'Y'
                    JOIN m_user b ON PurchaseRequestRequestedBy = b.M_UserID AND b.M_UserIsActive = 'Y'
                    LEFT JOIN m_user a ON PurchaseRequestApprovedBy = a.M_UserID AND a.M_UserIsActive = 'Y'
                    LEFT JOIN m_branch ON PurchaseRequestM_BranchCode = M_BranchCode
                    WHERE PurchaseRequestIsActive = 'Y'
                    AND PurchaseRequestStatus = 'Pending'
                    $sqlBranch
                    AND (PurchaseRequestNumber LIKE ? OR PurchaseRequestRefNumber LIKE ?)
                    AND PurchaseRequestS_RegionalID = ?
                
                UNION
                (
                    SELECT
                        PurchaseOrderID AS ID,
                        'PO' AS SourceType,
                        DATE_FORMAT(PurchaseOrderDate, '%d-%m-%Y') AS Date,
                        PurchaseOrderNumber AS Number,
                        NULL AS RefNumber,
                        PurchaseOrderNote AS Note,
                        0 AS Total,
                        itemCategoryID AS ItemCategoryID,
                        itemCategoryName AS ItemCategoryName,
                        PurchaseOrderStatus AS Status,
                        NULL AS ApprovedDate,
                        NULL AS ApprovedBy,
                        NULL AS RequestedByID,
                        NULL AS RequestedBy,
                        NULL AS RequestedByFromBranch,
                        S_RegionalName AS RegionalName,
                        M_BranchName AS BranchName,
                        NULL AS ItemName,
                        CASE 
                            WHEN PurchaseOrderDiscountPercent = 0 THEN 'Absolute'
                            WHEN PurchaseOrderDiscountAmount = 0 THEN 'Percentage'
                            ELSE ''
                        END AS DiscountType,
                        CASE 
                            WHEN PurchaseOrderDiscountPercent <> 0 THEN (PurchaseOrderDiscountPercent / 100) * PurchaseOrderSubTotal
                            WHEN PurchaseOrderDiscountAmount <> 0 THEN PurchaseOrderDiscountAmount
                            ELSE 0
                        END AS DiscountAmount,
                        PurchaseOrderTaxAmountPph + PurchaseOrderTaxAmountPpn AS TaxAmount,
                        SupplierCode,
                        SupplierName,
                        WarehouseCode,
                        CASE
                            WHEN WarehouseType = 'B' THEN CONCAT(WarehouseCode,' ', WarehouseName, ' - ', M_BranchName) 
                            WHEN WarehouseType = 'R' THEN CONCAT(WarehouseCode,' ', WarehouseName, ' - ', S_RegionalName) 
                            ELSE ''
                        END AS WarehouseName,
                        DATE_FORMAT(PurchaseOrderDate, '%d %M %Y') AS DisplayDate
                    FROM purchase_order
                    LEFT JOIN item_category ON PurchaseOrderItemCategoryID = itemCategoryID
                    LEFT JOIN supplier ON SupplierID = PurchaseOrderSupplierID
                    LEFT JOIN warehouse ON WarehouseID = PurchaseOrderWarehouseID
                    LEFT JOIN s_regional ON S_RegionalID = WarehouseS_RegionalID
                    LEFT JOIN m_branch ON M_BranchID = WarehouseM_BranchID
                    WHERE PurchaseOrderIsActive = 'Y'
                    AND PurchaseOrderStatus = 'Pending'
                    AND PurchaseOrderNumber LIKE ?
                )";

        $sqlTotal = "SELECT COUNT(*) AS total FROM ($sqlSelect) AS x";
        $qry = $this->db->query($sqlTotal, $params);

        $number_limit = 20;
        $number_offset = 0;
        if ($page > 0) {
            $number_offset = ($prm['page'] - 1) * $number_limit;
        }

        $totalCount = 0;
        $totalPage = 0;
        if ($qry) {
            $totalCount = $qry->row_array()["total"];
            $totalPage = ceil($totalCount / $number_limit);
        } else {
            $this->sys_error_db("Error searching count");
            exit;
        }

        $sql_painated = $sqlSelect . " LIMIT ? OFFSET ?";
        $params_paginated = array_merge($params, [$number_limit, $number_offset]);

        $qry_end = $this->db->query($sql_painated, $params_paginated);
        if ($qry_end) {
            $rows = $qry_end->result_array();
        } else {
            $this->sys_error_db("Error searching");
            exit;
        }

        // echo $this->db->last_query();
        // exit;

        $result = array(
            'total' => $totalPage,
            'records' => $rows,
            // "qry" => $this->db->last_query()
        );

        $this->sys_ok($result);
    }

    public function search_x()
    {
        if (! $this->isLogin) {
            $this->sys_error("Invalid Token");
            exit;
        }
        $prm = $this->sys_input;
        $search = '%' . $prm['search'] . '%';

        $date = $prm['date'];
        $status = $prm['status'];
        $page = $prm['page'];
        $user = $this->sys_user;
        $regionalID = $user['S_RegionalID'];
        $branchCode = $user['M_BranchCode'];
        $loginType = $user['M_UserLocationFlag'];
        $userID = $user['M_UserID'];

        $sqlBranch = '';
        if ($loginType == 'B' || $loginType == 'RB') {
            $sqlBranch = "AND PurchaseRequestM_BranchCode = '{$branchCode}'";
        }

        $sql = "SELECT m_approve_level.* FROM m_user
                JOIN m_approve_level
                ON M_UserM_ApproveLevelID = M_ApproveLevelID
                WHERE M_UserID = ?;";
        $qry = $this->db->query($sql, [$userID]);
        if (!$qry) {
            $this->sys_error_db("Error cek approval level");
            exit;
        }
        $approvalLevel = $qry->result_array();
        if (count($approvalLevel) == 0) {
            $result = array(
                'total' => 0,
                'records' => []
            );
            $this->sys_ok($result);
            exit;
        }

        $params = [$search, $search, $regionalID, $search];

        $sql = "SELECT
                PurchaseRequestID prID,
                DATE_FORMAT(PurchaseRequestDate, '%d-%m-%Y') prDate,
                PurchaseRequestNumber prNumber,
                PurchaseRequestRefNumber prRefNumber,
                PurchaseRequestNote prNote,
                PurchaseRequestTotal prTotal,
                PurchaseRequestItemCategoryID prItemType,
                ItemCategoryName prItemTypeName,
                PurchaseRequestStatus prStatus,
                DATE_FORMAT(PurchaseRequestApprovedDate, '%d-%m-%Y %H:%i') prApprovedDate,
                a.M_userUserName prApprovedBy,
                b.M_UserID prRequestedByID,
                b.M_userUserName prRequestedBy,
                b.M_UserM_BranchID prRequestedByFromBranch,
                S_RegionalName prRegionalName,
                M_BranchName prBranchName,
                IFNULL(`fn_getitemnamebyprid`(PurchaseRequestID), '') prItemName
                FROM purchase_request
                JOIN s_regional
                ON PurchaseRequestS_RegionalID = S_RegionalID
                AND S_RegionalIsActive = 'Y'
                JOIN item_category 
                ON PurchaseRequestItemCategoryID  = ItemCategoryID
                AND ItemCategoryIsActive = 'Y'
                JOIN m_user b
                ON PurchaseRequestRequestedBy = b.M_UserID
                AND b.M_UserIsActive = 'Y'
                LEFT JOIN m_user a
                ON PurchaseRequestApprovedBy = a.M_UserID
                AND a.M_UserIsActive = 'Y'
                LEFT JOIN 
                m_branch
                ON PurchaseRequestM_BranchCode = M_BranchCode
                WHERE PurchaseRequestIsActive = 'Y'
                AND (PurchaseRequestNumber LIKE ? OR PurchaseRequestRefNumber LIKE ? OR  PurchaseRequestRequestedBy LIKE ?)
                AND PurchaseRequestS_RegionalID = ?
                AND PurchaseRequestStatus = 'Pending'
                $sqlBranch
                ORDER BY prDate,prNumber
                DESC
                LIMIT ? OFFSET ?";

        $sqlSelect = "(SELECT
                        PurchaseRequestID AS ID,
                        'PR' AS SourceType,
                        DATE_FORMAT(PurchaseRequestDate, '%d-%m-%Y') AS Date,
                        PurchaseRequestNumber AS Number,
                        PurchaseRequestRefNumber AS RefNumber,
                        PurchaseRequestNote AS Note,
                        PurchaseRequestTotal AS Total,
                        PurchaseRequestItemCategoryID AS ItemCategoryID,
                        ItemCategoryName AS ItemCategoryName,
                        PurchaseRequestStatus AS Status,
                        DATE_FORMAT(PurchaseRequestApprovedDate, '%d-%m-%Y %H:%i') AS ApprovedDate,
                        a.M_userUserName AS ApprovedBy,
                        b.M_UserID AS RequestedByID,
                        b.M_userUserName AS RequestedBy,
                        b.M_UserM_BranchID AS RequestedByFromBranch,
                        S_RegionalName AS RegionalName,
                        M_BranchName AS BranchName,
                        IFNULL(`fn_getitemnamebyprid`(PurchaseRequestID), '') AS ItemName,
                        NULL AS DiscountType,
                        NULL AS DiscountAmount,
                        NULL AS TaxAmount,
                        NULL AS SupplierCode,
                        NULL AS SupplierName,
                        NULL AS WarehouseCode,
                        NULL AS WarehouseName,
                        NULL AS DisplayDate
                    FROM purchase_request
                    JOIN s_regional ON PurchaseRequestS_RegionalID = S_RegionalID AND S_RegionalIsActive = 'Y'
                    JOIN item_category ON PurchaseRequestItemCategoryID = ItemCategoryID AND ItemCategoryIsActive = 'Y'
                    JOIN m_user b ON PurchaseRequestRequestedBy = b.M_UserID AND b.M_UserIsActive = 'Y'
                    LEFT JOIN m_user a ON PurchaseRequestApprovedBy = a.M_UserID AND a.M_UserIsActive = 'Y'
                    LEFT JOIN m_branch ON PurchaseRequestM_BranchCode = M_BranchCode
                    WHERE PurchaseRequestIsActive = 'Y'
                    AND PurchaseRequestStatus = 'Pending'
                    $sqlBranch
                    AND (PurchaseRequestNumber LIKE ? OR PurchaseRequestRefNumber LIKE ?)
                    AND PurchaseRequestS_RegionalID = ?
                )
                UNION
                (
                    SELECT
                        PurchaseOrderID AS ID,
                        'PO' AS SourceType,
                        DATE_FORMAT(PurchaseOrderDate, '%d-%m-%Y') AS Date,
                        PurchaseOrderNumber AS Number,
                        NULL AS RefNumber,
                        PurchaseOrderNote AS Note,
                        0 AS Total,
                        itemCategoryID AS ItemCategoryID,
                        itemCategoryName AS ItemCategoryName,
                        PurchaseOrderStatus AS Status,
                        NULL AS ApprovedDate,
                        NULL AS ApprovedBy,
                        NULL AS RequestedByID,
                        NULL AS RequestedBy,
                        NULL AS RequestedByFromBranch,
                        S_RegionalName AS RegionalName,
                        M_BranchName AS BranchName,
                        NULL AS ItemName,
                        CASE 
                            WHEN PurchaseOrderDiscountPercent = 0 THEN 'Absolute'
                            WHEN PurchaseOrderDiscountAmount = 0 THEN 'Percentage'
                            ELSE ''
                        END AS DiscountType,
                        CASE 
                            WHEN PurchaseOrderDiscountPercent <> 0 THEN (PurchaseOrderDiscountPercent / 100) * PurchaseOrderSubTotal
                            WHEN PurchaseOrderDiscountAmount <> 0 THEN PurchaseOrderDiscountAmount
                            ELSE 0
                        END AS DiscountAmount,
                        PurchaseOrderTaxAmountPph + PurchaseOrderTaxAmountPpn AS TaxAmount,
                        SupplierCode,
                        SupplierName,
                        WarehouseCode,
                        CASE
                            WHEN WarehouseType = 'B' THEN CONCAT(WarehouseCode,' ', WarehouseName, ' - ', M_BranchName) 
                            WHEN WarehouseType = 'R' THEN CONCAT(WarehouseCode,' ', WarehouseName, ' - ', S_RegionalName) 
                            ELSE ''
                        END AS WarehouseName,
                        DATE_FORMAT(PurchaseOrderDate, '%d %M %Y') AS DisplayDate
                    FROM purchase_order
                    LEFT JOIN item_category ON PurchaseOrderItemCategoryID = itemCategoryID
                    LEFT JOIN supplier ON SupplierID = PurchaseOrderSupplierID
                    LEFT JOIN warehouse ON WarehouseID = PurchaseOrderWarehouseID
                    LEFT JOIN s_regional ON S_RegionalID = WarehouseS_RegionalID
                    LEFT JOIN m_branch ON M_BranchID = WarehouseM_BranchID
                    WHERE PurchaseOrderIsActive = 'Y'
                    AND PurchaseOrderStatus = 'Pending'
                    AND PurchaseOrderNumber LIKE ?
                )
                ORDER BY Date DESC";

        $sqlTotal = "SELECT COUNT(*) AS total FROM ($sqlSelect) AS x";
        $qry = $this->db->query($sqlTotal, $params);

        $number_limit = 20;
        $number_offset = 0;
        if ($page > 0) {
            $number_offset = ($prm['page'] - 1) * $number_limit;
        }

        $totalCount = 0;
        $totalPage = 0;
        if ($qry) {
            $totalCount = $qry->row_array()["total"];
            $totalPage = ceil($totalCount / $number_limit);
        } else {
            $this->sys_error_db("Error searching count");
            exit;
        }

        $sql_painated = $sqlSelect . " LIMIT ? OFFSET ?";
        $params_paginated = array_merge($params, [$number_limit, $number_offset]);

        $qry_end = $this->db->query($sql_painated, $params_paginated);
        if ($qry_end) {
            $rows = $qry_end->result_array();
        } else {
            $this->sys_error_db("Error searching");
            exit;
        }

        echo $this->db->last_query();
        exit;

        $result = array(
            'total' => $totalPage,
            'records' => $rows,
            // "qry" => $this->db->last_query()
        );

        $this->sys_ok($result);
    }
}