feature: file receiveItemAset & change some query purchaseorderasest

application/controllers/mockup/purchase/order/PurchaseOrderAset.php

@@ -81,59 +81,58 @@ class PurchaseOrderAset extends MY_Controller {
             }
 
             $limit = 10;
-            $offset = 0;
-            if ($para['currpage'] > 0) {
-                $offset = ($para['currpage'] - 1) * $limit;
-            }
 
             $sql = "SELECT
-                PurchaseRequestID,
-                PurchaseRequestNumber,
-                PurchaseRequestDetailID,
-                PurchaseRequestFlagID,
-                PurchaseRequestFlagM_BranchCode AS BranchCode,
-                PurchaseRequestItemCategoryID AS ItemCategoryID,
-                PurchaseRequestFlagQtyRest - PurchaseRequestFlagQtyProses AS UnprocessFlagQty,
-                PurchaseRequestDetailQty AS RequestQty,
-                PurchaseRequestDetailQty AS OriginalQty,
-                M_BranchName,
-                M_ItemID,
-                M_ItemCode,
-                M_ItemDesc,
-                ItemUnitID,
-                ItemUnitName,
-                SupplierPricePrice as SupplierPrice
-            FROM purchase_request
-            JOIN purchase_request_detail ON PurchaseRequestDetailPurchaseRequestID = PurchaseRequestID
-                AND PurchaseRequestDetailIsActive = 'Y'
-                AND PurchaseRequestM_BranchCode = ?
-                AND PurchaseRequestItemCategoryID = '3' -- id category item asset
-                AND PurchaseRequestNumber LIKE ?
-            JOIN purchase_request_flag ON PurchaseRequestFlagPurchaseRequestDetailID = PurchaseRequestDetailID
-                AND PurchaseRequestFlagStatus = 'PO'
-                AND PurchaseRequestFlagIsActive = 'Y'
-            JOIN m_item ON M_ItemID = PurchaseRequestDetailM_ItemID
-            JOIN itemunit ON ItemUnitID = PurchaseRequestDetailItemUnitID
-            JOIN supplier_price ON SupplierPriceSupplierID = ?
-                AND SupplierPriceM_ItemID = M_ItemID
-                AND SupplierPriceItemUnitID = ItemUnitID
-                AND SupplierPriceIsActive = 'Y'
-            JOIN m_branch ON M_BranchCode = PurchaseRequestFlagM_BranchCode
-                AND M_BranchIsActive = 'Y'
-            WHERE NOT EXISTS (
-                SELECT 1
-                FROM purchase_order_detail
-                JOIN purchase_order ON PurchaseOrderDetailPurchaseOrderID = PurchaseOrderID
-                    AND PurchaseOrderStatus = 'Approved'
-                    AND PurchaseOrderIsActive = 'Y'
-                    AND PurchaseOrderDetailIsActive = 'Y'
-                WHERE PurchaseOrderDetailPurchaseRequestDetailID = PurchaseRequestDetailID
-            )";
+                    PurchaseRequestID,
+                    PurchaseRequestNumber,
+                    PurchaseRequestDetailID,
+                    PurchaseRequestFlagID,
+                    M_BranchID,
+                    PurchaseRequestFlagM_BranchCode AS BranchCode,
+                    PurchaseRequestItemCategoryID AS ItemCategoryID,
+                    PurchaseRequestFlagQtyRest - PurchaseRequestFlagQtyProses AS UnprocessFlagQty,
+                    PurchaseRequestDetailQty AS RequestQty,
+                    PurchaseRequestDetailQty AS OriginalQty,
+                    M_BranchName,
+                    M_ItemID,
+                    M_ItemCode,
+                    M_ItemDesc,
+                    ItemUnitID,
+                    ItemUnitName,
+                    SupplierPricePrice as SupplierPrice,
+                    M_UserUsername AS Requestedby
+                FROM purchase_request
+                JOIN purchase_request_detail ON PurchaseRequestDetailPurchaseRequestID = PurchaseRequestID
+                    AND PurchaseRequestDetailIsActive = 'Y'
+                    AND PurchaseRequestItemCategoryID = '3' -- id category item asset
+                JOIN m_item ON M_ItemID = PurchaseRequestDetailM_ItemID
+                    AND (PurchaseRequestNumber LIKE ? OR M_ItemDesc LIKE ?)
+                JOIN purchase_request_flag ON PurchaseRequestFlagPurchaseRequestDetailID = PurchaseRequestDetailID
+                    AND PurchaseRequestFlagStatus = 'PO'
+                    AND PurchaseRequestFlagIsActive = 'Y'
+                JOIN m_branch ON M_BranchCode = PurchaseRequestFlagM_BranchCode
+                    AND M_BranchID = ?
+                    AND M_BranchIsActive = 'Y'
+                JOIN itemunit ON ItemUnitID = PurchaseRequestDetailItemUnitID
+                JOIN supplier_price ON SupplierPriceSupplierID = ?
+                    AND SupplierPriceM_ItemID = M_ItemID
+                    AND SupplierPriceItemUnitID = ItemUnitID
+                    AND SupplierPriceIsActive = 'Y'
+                JOIN m_user ON M_UserID = PurchaseRequestRequestedBy
+                WHERE NOT EXISTS (
+                    SELECT 1
+                    FROM purchase_order_detail
+                    JOIN purchase_order ON PurchaseOrderDetailPurchaseOrderID = PurchaseOrderID
+                        AND PurchaseOrderStatus = 'Approved'
+                        AND PurchaseOrderIsActive = 'Y'
+                        AND PurchaseOrderDetailIsActive = 'Y'
+                    WHERE PurchaseOrderDetailPurchaseRequestDetailID = PurchaseRequestDetailID
+                )";
 
-            $sql_data = $sql . " LIMIT ? OFFSET ? ";
+            $sql_data = $sql . " LIMIT ? ";
             $que_data = $this->db->query($sql_data, [
-                $para['branchcode'], $keyword, $para['supplierID'],
-                $limit, $offset
+                $keyword, $keyword, $para['branchID'], $para['supplierID'],
+                $limit
             ]);
             if (!$que_data) {
                 $this->sys_error_db("[Error] get daftar request data");
@@ -143,7 +142,7 @@ class PurchaseOrderAset extends MY_Controller {
 
             $sql_total = "SELECT COUNT(*) AS total FROM ($sql) AS x";
             $que_total = $this->db->query($sql_total, [
-                $para['branchcode'], $keyword, $para['supplierID']
+                $keyword, $keyword, $para['branchID'], $para['supplierID']
             ]);
             if (!$que_total) {
                 $this->sys_error_db("[Error] get total request aset");
@@ -255,14 +254,13 @@ class PurchaseOrderAset extends MY_Controller {
             $para = $this->sys_input;
 
             $sql = "SELECT
-                PurchaseOrderAssetAttachmentID AS attach_id,
-                PurchaseOrderAssetAttachmentName AS img_url,
-                PurchaseOrderAssetAttachmentCreated AS created
-            FROM purchase_order_asset_attachment
-            WHERE PurchaseOrderAssetAttachmentIsActive = 'Y'
-                AND PurchaseOrderAssetAttachmentContractID = ?
-                AND PurchaseOrderAssetAttachmentPurchaseOrderID = ?
-                AND PurchaseOrderAssetAttachmentType = 'order'";
+                ContractAssetAttachmentID AS attach_id,
+                ContractAssetAttachmentFileName AS img_url,
+                ContractAssetAttachmentDate AS created
+            FROM contract_asset_attachment
+            WHERE ContractAssetAttachmentIsActive = 'Y'
+                AND ContractAssetAttachmentPurchaseOrderAssetContractID = ?
+                AND ContractAssetAttachmentPurchaseOrderID = ?";
             $que = $this->db->query($sql, [
                 $para['contractID'], $para['poID']
             ]);
@@ -460,6 +458,28 @@ class PurchaseOrderAset extends MY_Controller {
         }
     }
 
+    public function getUserApproveLevel() {
+        try {
+            if (!$this->isLogin) {
+                $this->sys_error("invalid token");
+                exit;
+            }
+
+            $user = $this->sys_user;
+            $sql = "SELECT M_UserM_ApproveLevelID FROM m_user
+            WHERE M_UserIsActive = 'Y' AND M_UserID = ? ";
+            $que = $this->db->query($sql, [$user['M_UserID']]);
+            if (!$que) {
+                $this->sys_error_db("[Error] failed get approval level user");
+                exit;
+            }
+            $data = $que->row_array();
+            $this->sys_ok($data);
+        } catch (Exception $exc) {
+            $this->sys_error($exc->getMessage());
+        }
+    }
+
     ## MUTATIONS ##
     public function createPoAsset() {
         try {
@@ -1028,7 +1048,6 @@ class PurchaseOrderAset extends MY_Controller {
         }
     }
 
-    // upload file
     public function uploadAttachment() {
         try {
             if (!$this->isLogin) {
@@ -1071,19 +1090,18 @@ class PurchaseOrderAset extends MY_Controller {
                         $upload_data = $this->upload->data();
                         $filename = $upload_data['file_name'];
 
-                        $sql_insert = "INSERT INTO purchase_order_asset_attachment (
-                            PurchaseOrderAssetAttachmentContractID,
-                            PurchaseOrderAssetAttachmentPurchaseOrderID,
-                            PurchaseOrderAssetAttachmentName,
-                            PurchaseOrderAssetAttachmentType,
-                            PurchaseOrderAssetAttachmentCreated
-                        ) VALUES (?,?,?,?,NOW())";
+                        $sql_insert = "INSERT INTO contract_asset_attachment (
+                            ContractAssetAttachmentPurchaseOrderAssetContractID,
+                            ContractAssetAttachmentPurchaseOrderID,
+                            ContractAssetAttachmentFileName,
+                            ContractAssetAttachmentCreated
+                        ) VALUES (?,?,?,NOW())";
                         $que_insert = $this->db->query($sql_insert, [
-                            $para['contractID'], $para['poID'], $filename, 'order'
+                            $para['contractID'], $para['poID'], $filename
                         ]);
                         if (!$que_insert) {
                             $this->db->trans_rollback();
-                            $this->sys_error_db("[Error] failed insert attachment table purchase order asset attachment");
+                            $this->sys_error_db("[Error] failed insert attachment table contract asset attachment");
                             exit;
                         }
                     } else {
@@ -1168,28 +1186,6 @@ class PurchaseOrderAset extends MY_Controller {
         }
     }
 
-    public function getUserApproveLevel() {
-        try {
-            if (!$this->isLogin) {
-                $this->sys_error("invalid token");
-                exit;
-            }
-
-            $user = $this->sys_user;
-            $sql = "SELECT M_UserM_ApproveLevelID FROM m_user
-            WHERE M_UserIsActive = 'Y' AND M_UserID = ? ";
-            $que = $this->db->query($sql, [$user['M_UserID']]);
-            if (!$que) {
-                $this->sys_error_db("[Error] failed get approval level user");
-                exit;
-            }
-            $data = $que->row_array();
-            $this->sys_ok($data);
-        } catch (Exception $exc) {
-            $this->sys_error($exc->getMessage());
-        }
-    }
-
     private function insertLog($poID, $type, $prm, $dataBefore, $dataAfter, $userID, $desc = '') {
         $sql = "INSERT INTO acc_one_log.purchase_order_log (
             PurchaseOrderLogPurchaseOrderID,