<?php

class ReceiveItemPOAsset extends MY_Controller
{

    var $db;
    public function index()
    {
        echo "Receive Item PO Asset API";
    }

    public function __construct()
    {
        parent::__construct();
    }

    ## QUERY ##
    public function getListSupplier()
    {
        try {
            if (!$this->isLogin) {
                throw new Exception('invalid token');
            }

            $sql = "SELECT
                SupplierID,
                SupplierName
            FROM supplier
            WHERE SupplierIsActive = 'Y'";
            $que = $this->db->query($sql, []);
            if (!$que) {
                throw new Exception('failed to query daftar supplier', 1);
            }

            $data = $que->result_array();
            $this->sys_ok($data);
        } catch (Exception $exc) {
            $msg = '[Error] ' . $exc->getMessage();
            $code = $exc->getCode();
            if ($code == 1) {
                $this->sys_error_db($msg);
            } else {
                $this->sys_error($msg);
            }
            exit;
        }
    }

    public function getListCabang()
    {
        try {
            if (!$this->isLogin) {
                throw new Exception('invalid token');
            }

            $user = $this->sys_user;

            $sql = "SELECT
                M_BranchID,
                M_BranchCode,
                M_BranchName
            FROM m_branch
            WHERE M_BranchIsActive = 'Y'
                AND M_BranchS_RegionalID = ?";
            $que = $this->db->query($sql, [$user['S_RegionalID']]);
            if (!$que) {
                throw new Exception('failed to query data cabang', 1);
            }

            $data = $que->result_array();
            $this->sys_ok($data);
        } catch (Exception $exc) {
            $msg = '[Error] ' . $exc->getMessage();
            $code = $exc->getCode();
            if ($code == 1) {
                $this->sys_error_db($msg);
            } else {
                $this->sys_error($msg);
            }
            exit;
        }
    }

    public function getListGudang()
    {
        try {
            if (!$this->isLogin) {
                throw new Exception('invalid token');
            }

            $user = $this->sys_user;
            $para = $this->sys_input;

            $sql = "SELECT
                WarehouseID,
                WarehouseCode,
                WarehouseName
            FROM warehouse
            WHERE WarehouseIsActive = 'Y'
                AND WarehouseS_RegionalID = ?
                AND WarehouseM_BranchID = ?
                AND WarehouseIsTransit = 'N'";
            $que = $this->db->query($sql, [
                $user['S_RegionalID'],
                $para['M_BranchID']
            ]);
            if (!$que) {
                throw new Exception('failed to query gudang', 1);
            }

            $data = $que->result_array();
            $this->sys_ok($data);
        } catch (Exception $exc) {
            $msg = '[Error] ' . $exc->getMessage();
            $code = $exc->getCode();
            if ($code == 1) {
                $this->sys_error_db($msg);
            } else {
                $this->sys_error($msg);
            }
            exit;
        }
    }

    public function getListStaff()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("Invalid token");
                exit;
            }

            $user = $this->sys_user;
            $param = $this->sys_input;

            if ($param['isSerahTerima'] == 'Y') {
                $sql = "SELECT DISTINCT
                        u.M_UserID,
                        u.M_UserUsername
                    FROM receive_order_po ro
                    JOIN receive_order_po_detail rod ON rod.ReceiveOrderPoDetailReceiveOrderPoID = ro.ReceiveOrderPoID
                        AND rod.ReceiveOrderPoDetailIsActive = 'Y'
                    JOIN purchase_order_detail pod ON pod.PurchaseOrderDetailID = rod.ReceiveOrderPoDetailPurchaseOrderDetailID
                        AND pod.PurchaseOrderDetailIsActive = 'Y'
                    JOIN purchase_request pr ON pr.PurchaseRequestID = pod.PurchaseOrderDetailPurchaseRequestID
                        AND pr.PurchaseRequestIsActive = 'Y'
                    JOIN m_user u ON u.M_UserID = pr.PurchaseRequestRequestedBy
                        AND u.M_UserIsActive = 'Y'
                    WHERE ro.ReceiveOrderPoID = ?
                        AND ro.ReceiveOrderPoIsActive = 'Y'";
                $que = $this->db->query($sql, [$param['ROID']]);
            } else {
                $sql = "SELECT
                    M_UserID,
                    M_UserUsername
                FROM m_user
                WHERE M_UserM_BranchID = ?
                    AND M_UserS_RegionalID = ?
                    AND M_UserIsActive = 'Y'
                ORDER BY M_UserUsername";
                $que = $this->db->query($sql, [$user['M_BranchID'], $user['S_RegionalID']]);
            }

            if (!$que) {
                $this->sys_error_db("[Error] get staff");
                exit;
            }
            $data = $que->result_array();
            $this->sys_ok($data);
        } catch (Exception $exc) {
            $message = $exc->getMessage();
            $this->sys_error($message);
        }
    }

    public function searchOrderAsset()
    {
        try {
            if (!$this->isLogin) {
                throw new Exception('invalid token');
            }

            $para = $this->sys_input;
            $user = $this->sys_user;

            $keyword = "%";
            if ($para['search'] != '') {
                $keyword .= $para['search'] . "%";
            }

            $sql_data = "SELECT
                    PurchaseOrderID,
                    PurchaseOrderDate,
                    PurchaseOrderNumber,
                    PurchaseOrderNote,
                    PurchaseOrderShippingCost,
                    PurchaseOrderShippingCostStatus,
                    PurchaseOrderAssetContractName,
                    PurchaseOrderAssetContractDate,
                    SupplierID,
                    SupplierCode,
                    SupplierName
                FROM purchase_order
                JOIN purchase_order_asset_contract ON PurchaseOrderAssetContractIsActive = 'Y'
                    AND PurchaseOrderItemCategoryID = 3
                    AND PurchaseOrderAssetContractPurchaseOrderID = PurchaseOrderID
                    AND PurchaseOrderStatus = 'Approved'
                    AND PurchaseOrderSupplierID = ?
                    AND PurchaseOrderS_RegionalID = ?
                    AND PurchaseOrderWarehouseID = ?
                    AND PurchaseOrderIsActive = 'Y'
                JOIN supplier ON SupplierID = PurchaseOrderSupplierID
                WHERE (PurchaseOrderNumber LIKE ? OR PurchaseOrderAssetContractName LIKE ?)
                GROUP BY PurchaseOrderID
                LIMIT 10";
            $que_data = $this->db->query($sql_data, [
                $para['supplierID'],
                $user['S_RegionalID'],
                $para['gudangID'],
                $keyword,
                $keyword
            ]);
            if (!$que_data) {
                throw new Exception('failed to query data purchase order', 1);
            }
            $data = $que_data->result_array();

            foreach ($data as $key => $obj) {
                $sql_detail = "SELECT
                        M_ItemID,
                        M_ItemCode,
                        M_ItemDesc,
                        ItemUnitID,
                        ItemUnitCode,
                        ItemUnitName,
                        PurchaseOrderDetailID AS PODetailID,
                        PurchaseOrderDetailPurchaseSummaryID AS POSummaryID,
                        PurchaseOrderDetailPurchaseOrderID AS PurchaseOrderID,
                        PurchaseOrderDetailQty AS qty,
                        PoItemReceiveID,
                        PoItemReceivePrice AS price
                    FROM purchase_order_detail
                    JOIN po_item_receive ON PoItemReceiveIsActive = 'Y'
                        AND PoItemReceivePurchaseOrderDetailID = PurchaseOrderDetailID
                    JOIN m_item ON M_ItemID = PurchaseOrderDetailItemID
                    JOIN itemunit ON ItemUnitID = PurchaseOrderDetailItemUnitID
                    WHERE PurchaseOrderDetailPurchaseOrderID = ?
                        AND PurchaseOrderDetailIsActive = 'Y'";
                $que_detail = $this->db->query($sql_detail, [
                    $obj['PurchaseOrderID']
                ]);
                if (!$que_detail) {
                    throw new Exception('failed to query detail po asset', 1);
                }

                $detail = $que_detail->result_array();
                $data[$key]['detail'] = $detail;
            }

            $this->sys_ok($data);
        } catch (Exception $e) {
            $msg = '[Error] ' . $e->getMessage();
            $code = $e->getCode();
            if ($code == 1) {
                $this->sys_error_db($msg);
            } else {
                $this->sys_error($msg);
            }
            exit;
        }
    }

    public function getListingROAsset()
    {
        try {
            if (!$this->isLogin) {
                throw new Exception('invalid token');
            }

            $para = $this->sys_input;
            $user = $this->sys_user;

            $keyword = "%";
            if ($para['search'] != '') {
                $keyword .= $para['search'] . "%";
            }

            $offset = 0;
            $limit = 10;
            if ($para['currpage'] > 0) {
                $offset = ($para['currpage'] - 1) * $limit;
            }

            $confirmed = "X";
            if ($para['status'] === 'Confirmed') {
                $confirmed = "Y";
            }
            if ($para['status'] === 'Unconfirmed') {
                $confirmed = 'N';
            }

            $sql_base = "SELECT DISTINCT
                    ReceiveOrderPoID,
                    ReceiveOrderPoNumber,
                    ReceiveOrderPoIDate,
                    ReceiveOrderPoRefNumber,
                    ReceiveOrderPoNote,
                    ReceiveOrderPoConfirmed,
                    SupplierID,
                    SupplierName,
                    PurchaseOrderID,
                    PurchaseOrderNumber,
                    PurchaseOrderAssetContractID,
                    PurchaseOrderAssetContractName
                FROM receive_order_po
                JOIN receive_order_po_detail
                    ON ReceiveOrderPoDetailReceiveOrderPoID = ReceiveOrderPoID
                    AND ReceiveOrderPoIDate BETWEEN DATE(?) AND DATE(?)
                    AND (ReceiveOrderPoConfirmed = ? OR 'X' = ?)
                    AND ReceiveOrderPoNumber LIKE ?
                    AND ReceiveOrderPoDetailIsActive = 'Y'
                JOIN purchase_order
                    ON ReceiveOrderPoDetailPurchaseOrderID = PurchaseOrderID
                    AND PurchaseOrderItemCategoryID = 3 -- Filter category here
                JOIN purchase_order_asset_contract
                    ON PurchaseOrderAssetContractPurchaseOrderID = PurchaseOrderID
                    AND PurchaseOrderAssetContractIsActive = 'Y'
                JOIN m_branch
                    ON M_BranchCode = ReceiveOrderPoM_BranchCode
                    AND ReceiveOrderPoM_BranchCode = ?
                JOIN supplier
                    ON ReceiveOrderPoSupplierID = SupplierID
                WHERE ReceiveOrderPoIsActive = 'Y'
                ORDER BY ReceiveOrderPoID DESC";

            $sql_data = $sql_base . " LIMIT ? OFFSET ? ";
            $que_data = $this->db->query($sql_data, [
                $para['startdate'],
                $para['enddate'],
                $confirmed,
                $confirmed,
                $keyword,
                $user['M_BranchCode'],
                $limit,
                $offset
            ]);
            if (!$que_data) {
                throw new Exception('failed to get data receive order', 1);
            }
            $data = $que_data->result_array();

            $sql_total = "SELECT COUNT(*) AS total FROM ($sql_base) AS x";
            $que_total = $this->db->query($sql_total, [
                $para['startdate'],
                $para['enddate'],
                $confirmed,
                $confirmed,
                $keyword,
                $user['M_BranchCode']
            ]);
            if (!$que_total) {
                throw new Exception('failed to get total data receive order', 1);
            }
            $total = $que_total->row_array()['total'];

            $output = [
                "records" => $data,
                "total" => $total
            ];

            $this->sys_ok($output);
        } catch (Exception $exc) {
            $msg = '[Error] ' . $exc->getMessage();
            $code = $exc->getCode();
            if ($code == 1) {
                $this->sys_error_db($msg);
            } else {
                $this->sys_error($msg);
            }
            exit;
        }
    }

    public function getListAttachment()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("invalid token");
                exit;
            }

            $para = $this->sys_input;

            $sql = "SELECT
                    ReceiveOrderPoDocumentID AS attach_id,
                    ReceiveOrderPoDocumentFile AS img_url,
                    ReceiveOrderPoDocumentCreated AS created
                FROM receive_order_po_document
                WHERE ReceiveOrderPoDocumentIsActive = 'Y'
                    AND ReceiveOrderPoDocumentReceiveOrderPoID = ?
                    AND ReceiveOrderPoDocumentType = 'aset'";
            $que = $this->db->query($sql, [$para['roID']]);
            if (!$que) {
                $this->sys_error_db("[Error] failed get data attachment");
                exit;
            }
            $data = $que->result_array();

            $this->sys_ok($data);
        } catch (Exception $exc) {
            $msg = $exc->getMessage();
            $this->sys_error($msg);
        }
    }

    public function getDetailDataROAsset()
    {
        try {
            if (!$this->isLogin) {
                $this->sys_error("invalid token");
                exit;
            }

            $para = $this->sys_input;

            $sql_header = "SELECT
                ReceiveOrderPoID,
                ReceiveOrderPoNumber,
                ReceiveOrderPoIDate AS date,
                ReceiveOrderPoSupplierID AS supplier,
                ReceiveOrderPoRefNumber AS reference,
                '' AS ponumber,
                M_BranchID AS branchID,
                ReceiveOrderPoWarehouseID AS gudangID,
                ReceiveOrderShippingCostIsPaid AS shippingIsPaid,
                ReceiveOrderShippingCostAmount AS shipping,
                ReceiveOrderPoNote AS catatan
            FROM receive_order_po
            JOIN m_branch
                ON M_BranchCode = ReceiveOrderPoM_BranchCode
            WHERE ReceiveOrderPoID = ?
                AND ReceiveOrderPoIsActive = 'Y'";
            $que_header = $this->db->query($sql_header, [$para['roid']]);
            if (!$que_header) {
                $this->sys_error_db("[Error] failed get header data RO");
                exit;
            }
            $header = $que_header->row_array();

            $sql_detail = "SELECT
                    M_ItemID,
                    M_ItemCode,
                    M_ItemDesc,
                    ItemUnitID,
                    ItemUnitCode,
                    ItemUnitName,
                    ReceiveOrderPoDetailID,
                    ReceiveOrderPoDetailPurchaseOrderID AS PurchaseOrderID,
                    ReceiveOrderPoDetailPurchaseOrderDetailID AS PODetailID,
                    ReceiveOrderPoDetailPurchaseOrderSummaryID AS POSummaryID,
                    ReceiveOrderPoDetailPoItemReceiveID AS PoItemReceiveID,
                    ReceiveOrderPoDetailQty AS qty,
                    ReceiveOrderPoDetailPrice AS price,
                    PurchaseOrderNumber
                FROM receive_order_po_detail
                JOIN purchase_order
                    ON PurchaseOrderID = ReceiveOrderPoDetailPurchaseOrderID
                JOIN m_item ON M_ItemID = ReceiveOrderPoItemID
                JOIN itemunit ON ItemUnitID = ReceiveOrderPoItemUnitID
                WHERE ReceiveOrderPoDetailReceiveOrderPoID = ?
                    AND ReceiveOrderPoDetailIsActive = 'Y'";
            $que_detail = $this->db->query($sql_detail, [$para['roid']]);
            if (!$que_detail) {
                $this->sys_error_db("[Error] failed to get detail data ro");
                exit;
            }
            $detail = $que_detail->result_array();

            $uniquePoNumbers = [];
            foreach ($detail as $obj) {
                $poNumber = $obj['PurchaseOrderNumber'];
                if ($poNumber !== null && $poNumber !== '' && !in_array($poNumber, $uniquePoNumbers, true)) {
                    $uniquePoNumbers[] = $poNumber;
                }
            }
            $header['ponumber'] = implode(', ', $uniquePoNumbers);

            foreach ($detail as $key => $obj) {
                $sql_inspeksi = "SELECT
                    ReceiveOrderPoInspeksiID AS inspeksiID,
                    ReceiveOrderPoInspeksiReceiveOrderPoDetailID AS roID,
                    ReceiveOrderPoInspeksiQtyPesan AS qty_po,
                    ReceiveOrderPoInspeksiQtyActual AS qty_ro,
                    ReceiveOrderPoInspeksiDatePesan AS date_po,
                    ReceiveOrderPoInspeksiDateActual AS date_ro,
                    ReceiveOrderPoInspeksiKeadaanKemasan AS condt_kemasan,
                    ReceiveOrderPoInspeksiKeadaanKemasanNote AS catatan_kemasan,
                    ReceiveOrderPoInspeksiKondisiPengiriman AS condt_pengiriman,
                    ReceiveOrderPoInspeksiKondisiPengirimanNote AS catatan_kirim,
                    ReceiveOrderPoInspeksiSimpulan AS summary,
                    ReceiveOrderPoInspeksiCatatan AS catatan,
                    ReceiveOrderPoInspeksiStaffPenerimaID AS pemeriksa,
                    ReceiveOrderPoInspeksiPengirim AS pengirim
                FROM receive_order_po_inspeksi
                WHERE ReceiveOrderPoInspeksiReceiveOrderPoDetailID = ?
                    AND ReceiveOrderPoInspeksiIsActive = 'Y'";
                $que_inspeksi = $this->db->query($sql_inspeksi, [
                    $obj['ReceiveOrderPoDetailID']
                ]);
                if (!$que_inspeksi) {
                    $this->sys_error_db("[Error] failed to get data inspeksi ro");
                    exit;
                }

                $detail[$key]['inspeksi'] = $que_inspeksi->row_array();
            }

            $this->sys_ok([
                "header" => $header,
                "detail" => $detail
            ]);
        } catch (Exception $exc) {
            $this->sys_error($exc->getMessage());
        }
    }

    ## MUTATION ##
    public function generatePOItemReceive()
    {
        try {
            if (!$this->isLogin) {
                throw new Exception('invalid token');
            }

            $this->db->trans_begin();
            $para = $this->sys_input;
            $user = $this->sys_user;

            $sql_po = "SELECT * FROM purchase_order WHERE PurchaseOrderID = ?";
            $que_po = $this->db->query($sql_po, [$para['poID']]);
            if (!$que_po) {
                $this->db->trans_rollback();
                throw new Exception('failed to get data PO', 1);
            }

            $data_po = $que_po->row_array();
            if ($data_po['PurchaseOrderStatus'] != 'Approved') {
                $this->db->trans_rollback();
                throw new Exception('status PO isnt approved yet');
            }

            $sql_detailpo = "SELECT
                    IFNULL(PoItemReceiveID, 0) as receivedID,
                    IFNULL(PoItemReceiveQtyReceived, 0) as qtyReceived,
                    PurchaseOrderSummaryID as orderSummaryID,
                    PurchaseOrderDetailWarehouseID as gudangID,
                    PurchaseOrderSummaryItemID as itemID,
                    PurchaseOrderSummaryItemUnitID as itemUnitID,
                    PurchaseOrderSummaryQty as qty,
                    PurchaseOrderSummaryPrice as price,
                    m_itemDesc as itemName,
                    ItemUnitName as itemUnitName,
                    PurchaseOrderDetailID
                FROM purchase_order_summary
                JOIN purchase_order_detail ON PurchaseOrderSummaryItemID = PurchaseOrderDetailItemID
                    AND PurchaseOrderDetailPurchaseOrderID = PurchaseOrderSummaryPurchaseOrderID
                    AND PurchaseOrderSummaryItemUnitID = PurchaseOrderDetailItemUnitID
                    AND PurchaseOrderDetailIsActive = 'Y'
                JOIN m_item ON PurchaseOrderSummaryItemID = M_ItemID
                JOIN itemunit ON PurchaseOrderSummaryItemUnitID = ItemUnitID
                LEFT JOIN po_item_receive ON PurchaseOrderSummaryItemID = PoItemReceiveItemID
                    AND PurchaseOrderSummaryPurchaseOrderID = PoItemReceivePurchaseOrderID
                WHERE PurchaseOrderSummaryPurchaseOrderID = ?
                    AND PurchaseOrderSummaryIsActive = 'Y'
                    AND IFNULL(PoItemReceiveQtyReceived, 0) = 0";
            $que_detailpo = $this->db->query($sql_detailpo, [$para['poID']]);
            if (!$que_detailpo) {
                $this->db->trans_rollback();
                throw new Exception('failed to get current po detail data', 1);
            }
            $data_detailpo = $que_detailpo->result_array();

            foreach ($data_detailpo as $key => $ele) {
                $sql_itemrec = "INSERT INTO po_item_receive (
                        PoItemReceivePurchaseOrderID,
                        PoItemReceivePurchaseOrderSummaryID,
                        PoItemReceivePurchaseOrderDetailID,
                        PoItemReceiveWarehouseID,
                        PoItemReceiveItemID,
                        PoItemReceiveItemUnitID,
                        PoItemReceiveQty,
                        PoItemReceiveQtyReceived,
                        PoItemReceiveCreated,
                        PoItemReceiveCreatedUserID,
                        PoItemReceivePrice
                    ) VALUES (?,?,?,?,?,?,?,0,NOW(),?,?)";
                $que_itemrec = $this->db->query($sql_itemrec, [
                    $para['poID'],
                    $ele['orderSummaryID'],
                    $ele['PurchaseOrderDetailID'],
                    $ele['gudangID'],
                    $ele['itemID'],
                    $ele['itemUnitID'],
                    $ele['qty'],
                    $user['M_UserID'],
                    $ele['price']
                ]);
                if (!$que_itemrec) {
                    $this->db->trans_rollback();
                    throw new Exception('failed to insert into po item receive', 1);
                }
            }

            $this->db->trans_commit();
            $this->sys_ok("[success] generate data po item receive");
        } catch (Exception $e) {
            $msg = '[Error] ' . $e->getMessage();
            $code = $e->getCode();
            if ($code == 1) {
                $this->sys_error_db($msg);
            } else {
                $this->sys_error($msg);
            }
            exit;
        }
    }

    private function generateNoLogistik($user, $typeLogistik): string
    {
        $areaid = ($user['loginLevel'] == 'regional') ? $user['S_RegionalID'] : $user['M_BranchID'];
        $areatype = ($user['loginLevel'] == 'regional') ? 'R' : 'B';

        $sqlusrdivisi = "SELECT M_UserDivisionDivisionID
            FROM m_userdivision
            WHERE M_UserDivisionM_UserID = ?
                AND M_UserDivisionIsActive = 'Y'";
        $queusrdivisi = $this->db->query($sqlusrdivisi, [$user['M_UserID']]);
        if (!$queusrdivisi) {
            $this->db->trans_rollback();
            $this->sys_error_db("[Error] get user divisi");
            exit;
        }

        $usrdivisiID = $queusrdivisi->row_array()['M_UserDivisionDivisionID'];
        if (!isset($usrdivisiID)) {
            $this->db->trans_rollback();
            $this->sys_error_db("[Error] user divisi id null");
            exit;
        }

        $sqlnum = "SELECT `fn_penomoran`(?, ?, ?, ?, ?, ?) AS numpd;";
        $quenum = $this->db->query($sqlnum, [$typeLogistik, $usrdivisiID, $areatype, $areaid, 'SM', 'Y']);
        if (!$quenum) {
            $this->db->trans_rollback();
            $this->sys_error_db("[Error] generate number PI");
            exit;
        }

        $numPL = $quenum->row_array()['numpd'];
        if (!isset($numPL)) {
            $this->db->trans_rollback();
            $this->sys_error_db("[Error] number PI null");
            exit;
        }

        return $numPL;
    }

    public function createReceiveOrder()
    {
        try {
            if (!$this->isLogin) {
                throw new Exception('invalid token');
            }

            $this->db->trans_begin();
            $param = $this->sys_input;
            $user = $this->sys_user;

            # generate nomor purchase invoice #
            $numGR = $this->generateNoLogistik($user, 'GR');

            # insert table ro #
            $sqlInsRO = "INSERT INTO receive_order_po (
                ReceiveOrderPoNumber,
                ReceiveOrderPoSupplierID,
                ReceiveOrderPoIDate,
                ReceiveOrderPoWarehouseID,
                ReceiveOrderPoM_BranchCode,
                ReceiveOrderPoS_RegionalID,
                ReceiveOrderShippingCostAmount,
                ReceiveOrderShippingCostIsPaid,
                ReceiveOrderPoNote,
                ReceiveOrderPoRefNumber,
                ReceiveOrderPoDONumber,
                ReceiveOrderPoTypePurchase,
                ReceiveOrderPoCreatedUserID
            ) VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?)";
            $queInsRO = $this->db->query($sqlInsRO, [
                $numGR,
                $param['supplier'],
                $param['date'],
                $param['gudangID'],
                $user['M_BranchCode'],
                $user['S_RegionalID'],
                $param['shipping'],
                $param['shippingIsPaid'],
                $param['catatan'],
                $param['reference'],
                $param['reference'],
                'aset',
                $user['M_UserID']
            ]);
            if (!$queInsRO) {
                $this->db->trans_rollback();
                throw new Exception('failed to insert table receive order po', 1);
            }
            $roID = $this->db->insert_id();

            # inset to table ro detail #
            foreach ($param['detail'] as $key => $obj) {
                $sqlRcvdItem = "SELECT * FROM po_item_receive WHERE PoItemReceiveID = ?";
                $queRcvdItem = $this->db->query($sqlRcvdItem, [$obj['PoItemReceiveID']]);
                if (!$queRcvdItem) {
                    $this->db->trans_rollback();
                    throw new Exception('failed query po item receive', 1);
                }
                $cekReceived = $queRcvdItem->row_array();
                # cek item received #
                if ($cekReceived['PoItemReceiveIsActive'] == 'N') {
                    $this->db->trans_rollback();
                    throw new Exception('failed to check po item receive is active');
                }
                if ($cekReceived['PoItemReceiveQtyReceived'] == $cekReceived['PoItemReceiveQty']) {
                    $this->db->trans_rollback();
                    throw new Exception("item {$obj['M_ItemDesc']} sudah diterima semua");
                }

                if (intval($obj['qty']) > intval($cekReceived['PoItemReceiveQty'])) {
                    $this->db->trans_rollback();
                    throw new Exception(
                        "jumlah item {$obj['M_ItemDesc']} yang tesedia sudah berubah, silakan pilih ulang item"
                    );
                }

                $sql_insde = "INSERT INTO receive_order_po_detail (
                    ReceiveOrderPoDetailReceiveOrderPoID,
                    ReceiveOrderPoDetailPurchaseOrderID,
                    ReceiveOrderPoDetailPurchaseOrderSummaryID,
                    ReceiveOrderPoDetailPurchaseOrderDetailID,
                    ReceiveOrderPoDetailPoItemReceiveID,
                    ReceiveOrderPoDetailQty,
                    ReceiveOrderPoDetailPrice,
                    ReceiveOrderPoDetailTotal,
                    ReceiveOrderPoItemID,
                    ReceiveOrderPoItemUnitID,
                    ReceiveOrderPoDetailCreatedUserID
                ) VALUES (?,?,?,?,?,?,?,?,?,?,?)";
                $que_insde = $this->db->query($sql_insde, [
                    $roID,
                    $obj['PurchaseOrderID'],
                    $obj['POSummaryID'],
                    $obj['PODetailID'],
                    $obj['PoItemReceiveID'],
                    $obj['qty'],
                    $obj['price'],
                    (doubleval($obj['price']) * intval($obj['qty'])),
                    $obj['M_ItemID'],
                    $obj['ItemUnitID'],
                    $user['M_UserID']
                ]);
                if (!$que_insde) {
                    $this->db->trans_rollback();
                    throw new Exception('failed to inset table receive order po detail', 1);
                }
                $ROdetailID = $this->db->insert_id();

                $inspeksi = $obj['inspeksi'];
                $sql_inspeksi = "INSERT INTO receive_order_po_inspeksi (
                        ReceiveOrderPoInspeksiReceiveOrderPoDetailID,
                        ReceiveOrderPoInspeksiQtyPesan,
                        ReceiveOrderPoInspeksiQtyActual,
                        ReceiveOrderPoInspeksiDatePesan,
                        ReceiveOrderPoInspeksiDateActual,
                        ReceiveOrderPoInspeksiKeadaanKemasan,
                        ReceiveOrderPoInspeksiKeadaanKemasanNote,
                        ReceiveOrderPoInspeksiKondisiPengiriman,
                        ReceiveOrderPoInspeksiKondisiPengirimanNote,
                        ReceiveOrderPoInspeksiSimpulan,
                        ReceiveOrderPoInspeksiCatatan,
                        ReceiveOrderPoInspeksiStaffPenerimaID,
                        ReceiveOrderPoInspeksiPengirim,
                        ReceiveOrderPoInspeksiUserID
                    ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                $que_inspeksi = $this->db->query($sql_inspeksi, [
                    $ROdetailID,
                    $inspeksi['qty_po'],
                    $inspeksi['qty_ro'],
                    $inspeksi['date_po'],
                    $inspeksi['date_ro'],
                    $inspeksi['condt_kemasan'],
                    $inspeksi['catatan_kemasan'],
                    $inspeksi['condt_pengiriman'],
                    $inspeksi['catatan_kirim'],
                    $inspeksi['summary'],
                    $inspeksi['catatan'],
                    $inspeksi['pemeriksa'],
                    $inspeksi['pengirim'],
                    $user['M_UserID']
                ]);
                if (!$que_inspeksi) {
                    $this->db->trans_rollback();
                    throw new Exception('failed to insert table receive order po inspeksi', 1);
                }
            }

            $this->db->trans_commit();
            $this->sys_ok("[Success] create receive order asset");
        } catch (Exception $e) {
            $msg = '[Error] ' . $e->getMessage();
            $code = $e->getCode();
            if ($code == 1) {
                $this->sys_error_db($msg);
            } else {
                $this->sys_error($msg);
            }
            exit;
        }
    }

    public function editReceivedOrder()
    {
        try {
            if (!$this->isLogin) {
                throw new Exception('invalid token');
            }

            $this->db->trans_begin();
            $param = $this->sys_input;
            $user = $this->sys_user;

            if (empty($param['ReceiveOrderPoID'])) {
                throw new Exception('ReceiveOrderPoID is required');
            }
            if (!isset($param['detail']) || !is_array($param['detail'])) {
                throw new Exception('detail is required');
            }

            $roID = $param['ReceiveOrderPoID'];

            # update receive order header #
            $sqlHeader = "UPDATE receive_order_po SET
                    ReceiveOrderPoNumber = ?,
                    ReceiveOrderPoSupplierID = ?,
                    ReceiveOrderPoIDate = ?,
                    ReceiveOrderPoWarehouseID = ?,
                    ReceiveOrderPoM_BranchCode = ?,
                    ReceiveOrderPoS_RegionalID = ?,
                    ReceiveOrderShippingCostAmount = ?,
                    ReceiveOrderShippingCostIsPaid = ?,
                    ReceiveOrderPoNote = ?,
                    ReceiveOrderPoRefNumber = ?,
                    ReceiveOrderPoDONumber = ?
                WHERE ReceiveOrderPoID = ?
                    AND ReceiveOrderPoIsActive = 'Y'";
            $queHeader = $this->db->query($sqlHeader, [
                $param['ReceiveOrderPoNumber'],
                $param['supplier'],
                $param['date'],
                $param['gudangID'],
                $user['M_BranchCode'],
                $user['S_RegionalID'],
                $param['shipping'],
                $param['shippingIsPaid'],
                $param['catatan'],
                $param['reference'],
                $param['reference'],
                $roID
            ]);
            if (!$queHeader) {
                throw new Exception('failed to update table receive order po', 1);
            }

            # soft delete the existing receive order details #
            $sqlDetailInactive = "UPDATE receive_order_po_detail SET
                    ReceiveOrderPoDetailIsActive = 'N',
                    ReceiveOrderPoDetailDeletedUserID = ?,
                    ReceiveOrderPoDetailDeleted = NOW()
                WHERE ReceiveOrderPoDetailReceiveOrderPoID = ?
                    AND ReceiveOrderPoDetailIsActive = 'Y'";
            $queDetailInactive = $this->db->query($sqlDetailInactive, [
                $user['M_UserID'],
                $roID
            ]);
            if (!$queDetailInactive) {
                throw new Exception('failed to soft delete receive order po detail', 1);
            }

            # soft delete the inspections belonging to the existing details #
            $sqlInspeksiInactive = "UPDATE receive_order_po_inspeksi
                JOIN receive_order_po_detail
                    ON ReceiveOrderPoDetailID = ReceiveOrderPoInspeksiReceiveOrderPoDetailID
                SET ReceiveOrderPoInspeksiIsActive = 'N',
                    ReceiveOrderPoInspeksiUserID = ?,
                    ReceiveOrderPoInspeksiDeleted = NOW()
                WHERE ReceiveOrderPoDetailReceiveOrderPoID = ?
                    AND ReceiveOrderPoInspeksiIsActive = 'Y'";
            $queInspeksiInactive = $this->db->query($sqlInspeksiInactive, [
                $user['M_UserID'],
                $roID
            ]);
            if (!$queInspeksiInactive) {
                throw new Exception('failed to soft delete receive order po inspeksi', 1);
            }

            # re-insert receive order details and their inspection forms #
            foreach ($param['detail'] as $obj) {
                if (!isset($obj['inspeksi']) || !is_array($obj['inspeksi'])) {
                    throw new Exception("inspection data is required for item {$obj['M_ItemDesc']}");
                }

                $sqlDetail = "INSERT INTO receive_order_po_detail (
                    ReceiveOrderPoDetailReceiveOrderPoID,
                    ReceiveOrderPoDetailPurchaseOrderID,
                    ReceiveOrderPoDetailPurchaseOrderSummaryID,
                    ReceiveOrderPoDetailPurchaseOrderDetailID,
                    ReceiveOrderPoDetailPoItemReceiveID,
                    ReceiveOrderPoDetailQty,
                    ReceiveOrderPoDetailPrice,
                    ReceiveOrderPoDetailTotal,
                    ReceiveOrderPoItemID,
                    ReceiveOrderPoItemUnitID,
                    ReceiveOrderPoDetailCreatedUserID
                ) VALUES (?,?,?,?,?,?,?,?,?,?,?)";
                $queDetail = $this->db->query($sqlDetail, [
                    $roID,
                    $obj['PurchaseOrderID'],
                    $obj['POSummaryID'],
                    $obj['PODetailID'],
                    $obj['PoItemReceiveID'],
                    $obj['qty'],
                    $obj['price'],
                    (doubleval($obj['price']) * intval($obj['qty'])),
                    $obj['M_ItemID'],
                    $obj['ItemUnitID'],
                    $user['M_UserID']
                ]);
                if (!$queDetail) {
                    throw new Exception('failed to insert table receive order po detail', 1);
                }
                $roDetailID = $this->db->insert_id();

                $inspeksi = $obj['inspeksi'];
                $sqlInspeksi = "INSERT INTO receive_order_po_inspeksi (
                    ReceiveOrderPoInspeksiReceiveOrderPoDetailID,
                    ReceiveOrderPoInspeksiQtyPesan,
                    ReceiveOrderPoInspeksiQtyActual,
                    ReceiveOrderPoInspeksiDatePesan,
                    ReceiveOrderPoInspeksiDateActual,
                    ReceiveOrderPoInspeksiKeadaanKemasan,
                    ReceiveOrderPoInspeksiKeadaanKemasanNote,
                    ReceiveOrderPoInspeksiKondisiPengiriman,
                    ReceiveOrderPoInspeksiKondisiPengirimanNote,
                    ReceiveOrderPoInspeksiSimpulan,
                    ReceiveOrderPoInspeksiCatatan,
                    ReceiveOrderPoInspeksiStaffPenerimaID,
                    ReceiveOrderPoInspeksiPengirim,
                    ReceiveOrderPoInspeksiUserID
                ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                $queInspeksi = $this->db->query($sqlInspeksi, [
                    $roDetailID,
                    $inspeksi['qty_po'],
                    $inspeksi['qty_ro'],
                    $inspeksi['date_po'],
                    $inspeksi['date_ro'],
                    $inspeksi['condt_kemasan'],
                    $inspeksi['catatan_kemasan'],
                    $inspeksi['condt_pengiriman'],
                    $inspeksi['catatan_kirim'],
                    $inspeksi['summary'],
                    $inspeksi['catatan'],
                    $inspeksi['pemeriksa'],
                    $inspeksi['pengirim'],
                    $user['M_UserID']
                ]);
                if (!$queInspeksi) {
                    throw new Exception('failed to insert table receive order po inspeksi', 1);
                }
            }

            $this->db->trans_commit();
            $this->sys_ok('[Success] edit receive order asset');
        } catch (Exception $e) {
            $this->db->trans_rollback();
            $msg = '[Error] ' . $e->getMessage();
            if ($e->getCode() == 1) {
                $this->sys_error_db($msg);
            } else {
                $this->sys_error($msg);
            }
            exit;
        }
    }

    public function deleteReceiveOrder()
    {
        try {
            if (!$this->isLogin) {
                throw new Exception('invalid token');
            }

            $this->db->trans_begin();
            $para = $this->sys_input;
            $user = $this->sys_user;

            $roID = $para['roid'];

            # soft delete table receive order po #
            $sql_del = "UPDATE receive_order_po SET
                    ReceiveOrderPoIsActive = 'N',
                    ReceiveOrderPoDeleted = NOW(),
                    ReceiveOrderPoDeletedUserID = ?
                WHERE ReceiveOrderPoID = ?";
            $que_del = $this->db->query($sql_del, [
                $user['M_UserID'],
                $roID
            ]);
            if (!$que_del) {
                $this->db->trans_rollback();
                throw new Exception('failed to soft delete receive order po', 1);
            }

            $sql_deldet = "UPDATE receive_order_po_detail SET
                    ReceiveOrderPoDetailIsActive = 'N',
                    ReceiveOrderPoDetailDeletedUserID = ?,
                    ReceiveOrderPoDetailDeleted = NOW()
                WHERE ReceiveOrderPoDetailReceiveOrderPoID = ?";
            $que_deldet = $this->db->query($sql_deldet, [
                $user['M_UserID'],
                $roID
            ]);
            if (!$que_deldet) {
                $this->db->trans_rollback();
                throw new Exception('failed to soft delete RO detail', 1);
            }

            $sql_inspek = "UPDATE receive_order_po_inspeksi
                JOIN receive_order_po_detail
                    ON ReceiveOrderPoDetailID = ReceiveOrderPoInspeksiReceiveOrderPoDetailID
                SET
                    ReceiveOrderPoInspeksiIsActive = 'N',
                    ReceiveOrderPoInspeksiUserID = ?,
                    ReceiveOrderPoInspeksiDeleted = NOW()
                WHERE ReceiveOrderPoDetailReceiveOrderPoID = ?";
            $que_inspek = $this->db->query($sql_inspek, [
                $user['M_UserID'],
                $roID
            ]);
            if (!$que_inspek) {
                $this->db->trans_rollback();
                throw new Exception('failed to soft delete table inspeksi ro', 1);
            }

            $sql_attachment = "UPDATE receive_order_po_document SET
                ReceiveOrderPoDocumentIsActive = 'N',
                ReceiveOrderPoDocumentLastUpdated = NOW(),
                ReceiveOrderPoDocumentUserID = ?
            WHERE ReceiveOrderPoDocumentReceiveOrderPoID = ?
                AND ReceiveOrderPoDocumentType = 'aset'";
            $que_attachment = $this->db->query($sql_attachment, [
                $user['M_UserID'],
                $roID
            ]);
            if (!$que_attachment) {
                $this->db->trans_rollback();
                throw new Exception('failed to soft delete ro document', 1);
            }

            $this->db->trans_commit();
            $this->sys_ok("[success] delete receive order");
        } catch (Exception $e) {
            $msg = '[Error] ' . $e->getMessage();
            $code = $e->getCode();
            if ($code == 1) {
                $this->sys_error_db($msg);
            } else {
                $this->sys_error($msg);
            }
            exit;
        }
    }

    public function confirmReceiveOrder()
    {
        try {
            if (!$this->isLogin) {
                throw new Exception('invalid token');
            }

            $this->db->trans_begin();
            $para = $this->sys_input;
            $user = $this->sys_user;

            $roID = $para['roid'];

            # DATA ORDER #
            $sql_dataro = "SELECT
                    receive_order_po.*,
                    SupplierName
                FROM receive_order_po
                JOIN supplier ON ReceiveOrderPoSupplierID = SupplierID
                WHERE ReceiveOrderPoID = ?";
            $que_dataro = $this->db->query($sql_dataro, [$roID]);
            if (!$que_dataro) {
                $this->db->trans_rollback();
                throw new Exception('failed to get dara receive order', 1);
            }
            $data_ro = $que_dataro->row_array();

            $sql_detailro = "SELECT
                    receive_order_po_detail.*,
                    PurchaseOrderID,
                    PurchaseOrderNumber,
                    PurchaseOrderTaxPercentPph,
                    PurchaseOrderTaxPercentPpn,
                    PurchaseOrderSummaryDiscountAmount as DiscountPerItem,
                    PurchaseOrderSummaryQty as QtyAllPO,
                    PurchaseOrderSummaryTotal as TotalAllPO,
                    PurchaseOrderDiscountPercent AS DiscPOPercent,
                    PurchaseOrderDiscountAmount AS DiscPORupiah
                FROM receive_order_po_detail
                JOIN purchase_order ON ReceiveOrderPoDetailPurchaseOrderID = PurchaseOrderID
                JOIN purchase_order_summary ON ReceiveOrderPoDetailPurchaseOrderSummaryID = PurchaseOrderSummaryID
                    AND PurchaseOrderSummaryIsActive = 'Y'
                WHERE ReceiveOrderPoDetailReceiveOrderPoID = ?
                    AND ReceiveOrderPoDetailIsActive='Y'
                GROUP BY ReceiveOrderPoDetailID";
            $que_detailro = $this->db->query($sql_detailro, [$roID]);
            if (!$que_detailro) {
                $this->db->trans_rollback();
                throw new Exception('failed get detail data current RO', 1);
            }
            $detail_ro = $que_detailro->result_array();

            # UPDATE STATUS RO #
            $sql_updatero = "UPDATE receive_order_po SET
                    ReceiveOrderPoConfirmed = 'Y',
                    ReceiveOrderPoConfirmedDate = NOW(),
                    ReceiveOrderPoConfirmedUserID = ?
                WHERE ReceiveOrderPoID = ?";
            $que_updatero = $this->db->query($sql_updatero, [$user['M_UserID'], $roID]);
            if (!$que_updatero) {
                $this->db->trans_rollback();
                throw new Exception('failed to update status confirmed ro', 1);
            }

            # PREPARE DATA FOR JURNAL #
            $sql_nojurnal = "SELECT `fn_numbering`('J') AS numbering";
            $que_nojurnal = $this->db->query($sql_nojurnal, []);
            if (!$que_nojurnal) {
                $this->db->trans_rollback();
                throw new Exception('failed to generate no jurnal', 1);
            }
            $nojurnal = $que_nojurnal->row_array()['numbering'];

            $sql_periode = "SELECT periodeID FROM periode
                WHERE DATE(NOW()) BETWEEN periodeStartDate AND periodeEndDate
                    AND periodeIsActive = 'Y' AND periodeIsClosed = 'N'";
            $que_periode = $this->db->query($sql_periode, []);
            if (!$que_periode) {
                $this->db->trans_rollback();
                throw new Exception('failed get periode data', 1);
            }
            if ($que_periode->num_rows() === 0) {
                $this->db->trans_rollback();
                throw new Exception('waktu periode tidak ditemukan', 1);
            }
            $periode_jurnal = $que_periode->row_array()['periodeID'];

            $sql_typejurnal = "SELECT JurnalTypeID FROM jurnal_type WHERE JurnalTypeCode = ? AND JurnalTypeIsActive = 'Y'";
            $que_typejurnal = $this->db->query($sql_typejurnal, ['AUTOGOODRECEIVE']);
            if (!$que_typejurnal) {
                $this->db->trans_rollback();
                throw new Exception('failed to get jurnal type', 1);
            }
            if ($que_typejurnal->num_rows() === 0) {
                $this->db->trans_rollback();
                throw new Exception('tipe jurnal tidak ditemukan', 1);
            }
            $typejurnal = $que_typejurnal->row_array()['JurnalTypeID'];

            # INSERT JURNAL HEADER #
            $title = "Jurnal Pemakaian Jasa RO {$data_ro['ReceiveOrderPoNumber']} ";
            $title .=  "pada tanggal {$data_ro['ReceiveOrderPoIDate']}";
            $desc = "Referensi dari PO {$data_ro['ReceiveOrderPoRefNumber']} ";
            $desc .= "dari supplier {$data_ro['SupplierName']}";

            $sql_headerjurnal = "INSERT INTO jurnal (
                    jurnalM_BranchCompanyID,
                    JurnalS_RegionalID,
                    jurnalM_BranchCode,
                    jurnalperiodeID,
                    jurnalNo,
                    jurnalTitle,
                    jurnalDescription,
                    jurnalDate,
                    jurnalJurnalTypeID,
                    jurnalM_UserID
                ) VALUES (?,?,?,?,?,?,?,NOW(),?,?)";
            $que_headerjurnal = $this->db->query($sql_headerjurnal, [
                $user['M_BranchCompanyID'],
                $user['S_RegionalID'],
                $user['M_BranchCode'],
                $periode_jurnal,
                $nojurnal,
                $title,
                $desc,
                $typejurnal,
                $user['M_UserID']
            ]);
            if (!$que_headerjurnal) {
                $this->db->trans_rollback();
                throw new Exception('failed to insert into jurnal header', 1);
            }
            $jurnalID = $this->db->insert_id();

            # INSERT JURNAL TX #

            $total_debet = 0.00;
            $total_diskonitem = 0.00;
            $total_diskonpo = 0.00;
            foreach ($detail_ro as $key => $elem) {
                $sql_itemdata = "SELECT * FROM m_item WHERE M_ItemID = ?";
                $que_itemdata = $this->db->query($sql_itemdata, [$elem['ReceiveOrderPoItemID']]);
                if (!$que_itemdata) {
                    $this->db->trans_rollback();
                    throw new Exception('failed to get data item', 1);
                }
                $itemdata = $que_itemdata->row_array();

                /* cek kategori item */
                if (empty($itemdata['M_ItemFa_ClassID'])) {
                    $desc = "FA class {$itemdata['M_ItemDesc']} tidak ditemukan pada ";
                    $desc .= "PO {$elem['PurchaseOrderNumber']}";
                    $this->db->trans_rollback();
                    throw new Exception($desc, 1);
                }

                $sql_coaitem = "SELECT
                        Fa_ClassCoaID,
                        Fa_ClassCoaDesc
                    FROM fa_class
                    WHERE Fa_ClassID = ?";
                $que_coaitem = $this->db->query($sql_coaitem, $itemdata['M_ItemFa_ClassID']);
                if (!$que_coaitem) {
                    $this->db->trans_rollback();
                    throw new Exception('failed to get coa asset', 1);
                }

                $coa_asset = $que_coaitem->row_array();
                if ($que_coaitem->num_rows() <= 0) {
                    $this->db->trans_rollback();
                    throw new Exception("asset {$itemdata['M_ItemDesc']} belum di map coa asset");
                }

                $debet = doubleval($elem['ReceiveOrderPoDetailTotal']);
                $total_debet = round($total_debet + $debet, 2);

                /* INSERT table jurnal tx item */
                $sql_jurnaltx = "INSERT INTO jurnal_tx (
                    jurnalTxJurnalID,
                    jurnalTxCoaID,
                    jurnalTxDescription,
                    jurnalTxDebit,
                    jurnalTxCredit,
                    jurnalTxM_UserID
                ) VALUES (?,?,?,?,?,?)";
                $que_jurnaltx = $this->db->query($sql_jurnaltx, [
                    $jurnalID,
                    $coa_asset['Fa_ClassCoaID'],
                    $coa_asset['Fa_ClassCoaDesc'],
                    $debet,
                    0,
                    $user['M_UserID']
                ]);
                if (!$que_jurnaltx) {
                    $this->db->trans_rollback();
                    throw new Exception("[Error] failed insert jurnaltx {$itemdata['M_ItemDesc']}", 1);
                }
                $jurnalTXID = $this->db->insert_id();

                /* INSERT table jurnal addon item */
                $sql_jurnaladdon = "INSERT INTO jurnal_addon (
                    jurnalAddOnJurnalID,
                    jurnalAddOnJurnalTxID,
                    jurnalAddOnCode,
                    jurnalAddOnValue,
                    jurnalAddOnM_ItemID,
                    jurnalAddOnCreated,
                    jurnalAddOnCreatedUserID
                ) VALUES (?,?,'PONUMB',?,?,NOW(),?)";
                $que_jurnaladdon = $this->db->query($sql_jurnaladdon, [
                    $jurnalID,
                    $jurnalTXID,
                    $elem['PurchaseOrderNumber'],
                    $elem['ReceiveOrderPoItemID'],
                    $user['M_UserID']
                ]);
                if (!$que_jurnaladdon) {
                    $this->db->trans_rollback();
                    throw new Exception("[Error] failed insert jurnal addon {$itemdata['M_ItemDesc']}", 1);
                }

                /* INSERT table jurnal tx kredit diskon per item */
                $sql_qtypo = "SELECT
                        SUM(subquery.PurchaseOrderSummaryQty) as totalAllQtyPO,
                        SUM(subquery.PurchaseOrderSummaryTotal) as totalAllPricePO
                    FROM (
                        SELECT
                            PurchaseOrderSummaryQty,
                            PurchaseOrderSummaryTotal
                        FROM receive_order_po_detail
                        JOIN purchase_order_summary ON PurchaseOrderSummaryIsActive = 'Y'
                            AND PurchaseOrderSummaryPurchaseOrderID = ReceiveOrderPoDetailPurchaseOrderID
                        WHERE ReceiveOrderPoDetailReceiveOrderPoID = ?
                        GROUP BY PurchaseOrderSummaryID
                    ) AS subquery";
                $que_qtypo = $this->db->query($sql_qtypo, [$roID]);
                if (!$que_qtypo) {
                    $this->db->trans_rollback();
                    throw new Exception("[Error] get detail qty & price po", 1);
                }
                $data_po = $que_qtypo->row_array();
                /* kalkulasi diskon per item */
                $diskon = round(intval($elem['ReceiveOrderPoDetailQty']) * floatval($elem['DiscountPerItem']), 2);
                $total_diskonitem = round($total_diskonitem + $diskon, 2);

                /* kalkulasi diskon prorata PO per item */
                $diskon_po = doubleval($elem['DiscPORupiah']);
                if (doubleval($elem['DiscPOPercent']) != 0.00) {
                    $diskon_po = doubleval($data_po['totalAllPricePO']) * doubleval($elem['DiscPOPercent']) / 100;
                }
                $diskon_proratapo = $diskon_po * (doubleval($elem['TotalAllPO'])) / doubleval($data_po['totalAllPricePO']);
                $diskon_prorata_item = round((intval($elem['ReceiveOrderPoDetailQty']) / intval($elem['QtyAllPO'])) * $diskon_proratapo, 2);

                $total_diskonpo = round($total_diskonpo + $diskon_prorata_item, 2);
                $input_diskon = [$diskon, $diskon_prorata_item];

                /* get account diskon */
                $sql_coadiskon = "SELECT * FROM coa WHERE coaAccountNo = ? LIMIT 1";
                $que_coadiskon = $this->db->query($sql_coadiskon, ['2190100001']);
                if (!$que_coadiskon) {
                    $this->db->trans_rollback();
                    throw new Exception("[Error] failed get account diskon", 1);
                }
                $coa_diskon = $que_coadiskon->row_array();
                foreach ($input_diskon as $key => $val) {
                    if (doubleval($val) <= 0.00) {
                        continue;
                    }
                    $que_jurnaltx = $this->db->query($sql_jurnaltx, [
                        $jurnalID,
                        $coa_diskon['coaID'],
                        $coa_diskon['coaDescription'],
                        0,
                        $val,
                        $user['M_UserID']
                    ]);
                    if (!$que_jurnaltx) {
                        $this->db->trans_rollback();
                        throw new Exception("[Error] failed insert jurnal tx diskon", 1);
                    }
                    $jurnalTXID_diskon = $this->db->insert_id();

                    $que_jurnaladdon = $this->db->query($sql_jurnaladdon, [
                        $jurnalID,
                        $jurnalTXID_diskon,
                        $elem['PurchaseOrderNumber'],
                        $elem['ReceiveOrderPoItemID'],
                        $user['M_UserID']
                    ]);
                    if (!$que_jurnaladdon) {
                        $this->db->trans_rollback();
                        throw new Exception("[Error] failed insert jurnal addon diskon", 1);
                    }
                }

                /* save diskon prorata item */
                $sql_saveprorate = "UPDATE receive_order_po_detail SET
                        ReceiveOrderPoDetailDiskonPoProrata = ?
                    WHERE ReceiveOrderPoDetailID = ?";
                $que_saveprorate = $this->db->query($sql_saveprorate, [
                    $diskon_prorata_item,
                    $elem['ReceiveOrderPoDetailID']
                ]);
                if (!$que_saveprorate) {
                    $this->db->trans_rollback();
                    throw new Exception("[Error] save diskon prorate", 1);
                }
            }

            # INSERT jurnal tx for GRNI #
            $total_grni = round($total_debet - ($total_diskonitem + $total_diskonpo), 2);
            $sql_grni = "INSERT INTO jurnal_tx (
                    jurnalTxJurnalID,
                    jurnalTxCoaID,
                    jurnalTxDescription,
                    jurnalTxDebit,
                    jurnalTxCredit,
                    jurnalTxM_UserID
                ) SELECT ?, coaID, coaDescription, 0, ?, ?
                FROM coa WHERE coaAccountNo = '2110100030' LIMIT 1";
            $que_grni = $this->db->query($sql_grni, [
                $jurnalID,
                $total_grni,
                $user['M_UserID']
            ]);
            if (!$que_grni) {
                $this->db->trans_rollback();
                throw new Exception('insert jurnal tx GRNI', 1);
            }
            $jurnalTXID_GRNI = $this->db->insert_id();

            $sql_addon_grni = "INSERT INTO jurnal_addon (
                    jurnalAddOnJurnalID,
                    jurnalAddOnJurnalTxID,
                    jurnalAddOnCode,
                    jurnalAddOnValue,
                    jurnalAddOnCreated,
                    jurnalAddOnCreatedUserID
                ) VALUES (?,?,'RONUMB',?,NOW(),?)";
            $que_addon_grni = $this->db->query($sql_addon_grni, [
                $jurnalID,
                $jurnalTXID_GRNI,
                $data_ro['ReceiveOrderPoNumber'],
                $user['M_UserID']
            ]);
            if (!$que_addon_grni) {
                $this->db->trans_rollback();
                throw new Exception('insert jurnal addon GRNI', 1);
            }

            /* generate purchase invoice */
            $this->generatePurchaseInvoice($user, $roID, $total_grni, $total_diskonpo);

            $this->db->trans_commit();
            $this->sys_ok("[Success] order received");
        } catch (Exception $e) {
            $msg = '[Error] ' . $e->getMessage();
            $code = $e->getCode();
            if ($code == 1) {
                $this->sys_error_db($msg);
            } else {
                $this->sys_error($msg);
            }
            exit;
        }
    }

    private function generatePurchaseInvoice($user, $roID, $grni, $diskonPO)
    {
        $num_pi = $this->generateNoLogistik($user, 'PI');

        # prepare data #
        $sql_dataro = "SELECT DISTINCT
                receive_order_po.* ,
                PurchaseOrderID,
                PurchaseOrderTaxPpnType AS tax_type,
                PurchaseOrderTaxPercentPpn AS tax_percent,
                PurchaseOrderTaxAmountPpn AS tax_amount,
                IFNULL(SupplierDownpaymentAmount, 0) AS downpayment_amount
            FROM receive_order_po
            JOIN receive_order_po_detail ON ReceiveOrderPoDetailReceiveOrderPoID = ReceiveOrderPoID
                AND ReceiveOrderPoDetailIsActive = 'Y'
                AND ReceiveOrderPoIsActive = 'Y'
            JOIN purchase_order ON ReceiveOrderPoDetailPurchaseOrderID = PurchaseOrderID
            LEFT JOIN supplier_downpayment
                ON SupplierDownpaymentPurchasOrderID = PurchaseOrderID
            WHERE ReceiveOrderPoID = ?";
        $que_dataro = $this->db->query($sql_dataro, [$roID]);
        if (!$que_dataro) {
            $this->db->trans_rollback();
            throw new Exception('failed get current data for generate PI', 1);
        }
        $data_ro = $que_dataro->row_array();

        $sql_detailro = "SELECT
                receive_order_po_detail.*,
                M_ItemDesc,
                PurchaseOrderSummaryDiscountType AS DiscountType,
                PurchaseOrderSummaryDiscountRupiah AS DiscountRupiah,
                PurchaseOrderSummaryDiscountPercent AS DiscountPercent,
                PurchaseOrderSummaryDiscountAmount as DiscountPerItem
            FROM receive_order_po_detail
            JOIN m_item ON M_ItemID = ReceiveOrderPoItemID AND M_ItemIsActive = 'Y'
            JOIN purchase_order_summary ON ReceiveOrderPoDetailPurchaseOrderSummaryID = PurchaseOrderSummaryID
            WHERE ReceiveOrderPoDetailIsActive = 'Y'
                AND ReceiveOrderPoDetailReceiveOrderPoID = ?";
        $que_detailro = $this->db->query($sql_detailro, [$roID]);
        if (!$que_detailro) {
            $this->db->trans_rollback();
            throw new Exception('failed to get data detail ro for generate PI', 1);
        }
        $detail_ro = $que_detailro->result_array();

        # INSERT header supplier invoice #
        $due_date = new DateTime($data_ro['ReceiveOrderPoIDate']);
        $due_date->add(new DateInterval('P7D'));
        $due_date = $due_date->format('Y-m-d');

        $subtotal = round(($grni + $diskonPO), 2);

        $donumber = "-";
        if ($data_ro['ReceiveOrderPoDONumber'] != '') {
            $donumber = $data_ro['ReceiveOrderPoDONumber'];
        }

        $addedtax = round(($grni + $data_ro['tax_amount']), 2);
        $grandtotal = round(($addedtax - $data_ro['downpayment_amount']), 2);
        $sql_insert_pi = "INSERT INTO supplier_invoice (
                SupplierInvoiceNumber,
                SupplierInvoiceReceiveOrderPoID,
                SupplierInvoiceDate,
                SupplierInvoiceDueDate,
                SupplierInvoiceSupplierID,
                SupplierInvoiceRefNumber,
                SupplierInvoiceDeliveryOrderNumber,
                SupplierInvoiceSubTotal,
                SupplierInvoiceDiscountPercent,
                SupplierInvoiceDiscountAmount,
                SupplierInvoiceTaxPercentPpn,
                SupplierInvoiceTaxAmountPpn,
                SupplierInvoiceGrandTotal,
                SupplierInvoiceUnpaid,
                SupplierInvoiceNote,
                SupplierInvoiceReceiveDate,
                SupplierInvoiceReceivedBy,
                SupplierInvoiceCreatedUserID
            ) VALUES (?,?,NOW(),?,?,?,?,?,?,?,?,?,?,?,?,NOW(),?,?)";
        $que_insert_pi = $this->db->query($sql_insert_pi, [
            $num_pi,
            $data_ro['ReceiveOrderPoID'],
            $due_date,
            $data_ro['ReceiveOrderPoSupplierID'],
            $data_ro['ReceiveOrderPoNumber'],
            $donumber,
            $subtotal,
            0.00,
            $diskonPO,
            $data_ro['tax_percent'],
            $data_ro['tax_amount'],
            $grandtotal,
            $grandtotal,
            $data_ro['ReceiveOrderPoNote'],
            $user['M_UserID'],
            $user['M_UserID']
        ]);
        if (!$que_insert_pi) {
            $this->db->trans_rollback();
            throw new Exception('failed insert data into table PI', 1);
        }
        $Pinvoice_ID = $this->db->insert_id();

        # INSERT supplier invoice detail #
        $sql_ins_itemPI = "INSERT INTO supplier_invoice_detail (
                SupplierInvoiceDetailSupplierInvoiceID,
                SupplierInvoiceDetailPurchaseOrderID,
                SupplierInvoiceDetailPurchaseOrderSummaryID,
                SupplierInvoiceDetailReceiveOrderPoID,
                SupplierInvoiceDetailReceiveOrderPoDetailID,
                SupplierInvoiceDetailItemID,
                SupplierInvoiceDetailItemUnitID,
                SupplierInvoiceDetailDescription,
                SupplierInvoiceDetailQty,
                SupplierInvoiceDetailPrice,
                SupplierInvoiceDetailDiscountPercent,
                SupplierInvoiceDetailDiscountDiscountRupiah,
                SupplierInvoiceDetailDiscountDiscountType,
                SupplierInvoiceDetailDiscountPoProrata,
                SupplierInvoiceDetailDiscountAmount,
                SupplierInvoiceDetailTotal,
                SupplierInvoiceDetailUnpaid,
                SupplierInvoiceDetailCreatedUserID
            ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        foreach ($detail_ro as $key => $elem) {
            $itemprice = doubleval($elem['ReceiveOrderPoDetailPrice']) - doubleval($elem['DiscountPerItem']);
            $totalprice = round($itemprice * intval($elem['ReceiveOrderPoDetailQty']), 2);

            $que_ins_itemPI = $this->db->query($sql_ins_itemPI, [
                $Pinvoice_ID,
                $elem['ReceiveOrderPoDetailPurchaseOrderID'],
                $elem['ReceiveOrderPoDetailPurchaseOrderSummaryID'],
                $elem['ReceiveOrderPoDetailReceiveOrderPoID'],
                $elem['ReceiveOrderPoDetailID'],
                $elem['ReceiveOrderPoItemID'],
                $elem['ReceiveOrderPoItemUnitID'],
                $elem['M_ItemDesc'],
                $elem['ReceiveOrderPoDetailQty'],
                $elem['ReceiveOrderPoDetailPrice'],
                $elem['DiscountPercent'],
                $elem['DiscountRupiah'],
                $elem['DiscountType'],
                $elem['ReceiveOrderPoDetailDiskonPoProrata'],
                $elem['DiscountPerItem'],
                $totalprice,
                $totalprice,
                $user['M_UserID']
            ]);
            if (!$que_ins_itemPI) {
                $this->db->trans_rollback();
                throw new Exception('failed insert table detail PI', 1);
            }
        }
    }

    public function uploadAttachment()
    {
        try {
            if (!$this->isLogin) {
                throw new Exception('invalid token');
            }


            $this->db->trans_begin();
            $para = $this->sys_input;
            $user = $this->sys_user;

            $basepath = "/home/one/project/accone/one-media/order-asset/";
            $year = date("Y");
            $targetpath = $basepath . $year . "/";

            if (!is_dir($targetpath)) {
                mkdir($targetpath, 0755, true);
            }

            $config['upload_path'] = $targetpath;
            $config['allowed_types'] = 'jpg|jpeg|png';
            $config['max_size'] = '10000';
            $count = count($_FILES['files']['name']);
            $this->load->library('upload', $config);

            for ($i = 0; $i < $count; $i++) {
                if (!empty($_FILES['files']['name'][$i])) {
                    $_FILES['file']['name']     = $_FILES['files']['name'][$i];
                    $_FILES['file']['type']     = $_FILES['files']['type'][$i];
                    $_FILES['file']['tmp_name'] = $_FILES['files']['tmp_name'][$i];
                    $_FILES['file']['error']    = $_FILES['files']['error'][$i];
                    $_FILES['file']['size']     = $_FILES['files']['size'][$i];

                    $ext = pathinfo($_FILES['file']['name'], PATHINFO_EXTENSION);
                    $new_filename = "PO" . date("YmdHis") . "_" . $para['ronumber'] . "." . $ext;
                    $config['file_name'] = $new_filename;
                    $this->upload->initialize($config);

                    if ($this->upload->do_upload('file')) {
                        $upload_data = $this->upload->data();
                        $filename = $upload_data['file_name'];

                        $sql_insert = "INSERT INTO receive_order_po_document (
                                ReceiveOrderPoDocumentReceiveOrderPoID,
                                ReceiveOrderPoDocumentDate,
                                ReceiveOrderPoDocumentFile,
                                ReceiveOrderPoDocumentType,
                                ReceiveOrderPoDocumentIsActive,
                                ReceiveOrderPoDocumentCreated,
                                ReceiveOrderPoDocumentUserID
                            ) VALUES (?, NOW(), ?, ?, 'Y', NOW(), ?)";
                        $que_insert = $this->db->query($sql_insert, [
                            $para['roID'],
                            $filename,
                            'aset',
                            $user['M_UserID']
                        ]);
                        if (!$que_insert) {
                            $this->db->trans_rollback();
                            throw new Exception('failed to insert data to table ro document', 1);
                        }

                        $sql_contract = "UPDATE purchase_order_asset_contract SET
                                PurchaseOrderAssetContractReceiveOrderPoID = ?
                            WHERE PurchaseOrderAssetContractID = ?";
                        $que_contract = $this->db->query($sql_contract, [
                            $para['roID'],
                            $para['contractID']
                        ]);
                        if (!$que_contract) {
                            $this->db->trans_rollback();
                            throw new Exception('failed insert roID to po asset contract', 1);
                        }
                    } else {
                        $error = $this->upload->display_errors();
                        $this->db->trans_rollback();
                        throw new Exception($error);
                    }
                }
            }

            $this->db->trans_commit();
            $this->sys_ok("[Success] upload attachment order asset");
        } catch (Exception $e) {
            $msg = '[Error] ' . $e->getMessage();
            $code = $e->getCode();
            if ($code == 1) {
                $this->sys_error_db($msg);
            } else {
                $this->sys_error($msg);
            }
            exit;
        }
    }
}
