| /* |
| * Licensed to the Apache Software Foundation (ASF) under one |
| * or more contributor license agreements. See the NOTICE file |
| * distributed with this work for additional information |
| * regarding copyright ownership. The ASF licenses this file |
| * to you under the Apache License, Version 2.0 (the |
| * "License"); you may not use this file except in compliance |
| * with the License. You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, |
| * software distributed under the License is distributed on an |
| * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| * KIND, either express or implied. See the License for the |
| * specific language governing permissions and limitations |
| * under the License. |
| */ |
| |
| // This script can be used for testing right now but it should not be used for production because |
| // it does not work for marketing packages and more importantly, if there is a new product without any |
| // inventory items, it will not show up on the inventory report even if it had a ProductFacility record. |
| // These problems need to be addressed before this can be used in production. |
| |
| // This script counts the inventory in the facility using a complex and |
| // pretty efficient dynamic view entity. |
| // However, since the quantities are not computed using the inventory |
| // services (getInventoryAvailableByFacility and getMktgPackagesAvailable) |
| // there are some limitations: the virtual inventory of marketing packages |
| // is not computed; you can use the ViewFacilityInventoryByProduct.groovy if you |
| // need it (but it is slower than this one). |
| |
| import org.apache.ofbiz.base.util.Debug |
| import org.apache.ofbiz.entity.* |
| import org.apache.ofbiz.entity.condition.* |
| import org.apache.ofbiz.entity.transaction.* |
| import org.apache.ofbiz.entity.util.* |
| import org.apache.ofbiz.entity.model.DynamicViewEntity |
| import org.apache.ofbiz.entity.model.ModelKeyMap |
| import org.apache.ofbiz.entity.model.ModelViewEntity.ComplexAlias |
| import org.apache.ofbiz.entity.model.ModelViewEntity.ComplexAliasField |
| import org.apache.ofbiz.product.inventory.* |
| |
| action = request.getParameter("action") |
| |
| searchParameterString = "action=Y&facilityId=" + facilityId |
| |
| offsetQOH = -1 |
| offsetATP = -1 |
| hasOffsetQOH = false |
| hasOffsetATP = false |
| |
| EntityListIterator prodsEli = null |
| rows = [] as ArrayList |
| |
| if (action) { |
| // ------------------------------ |
| prodView = new DynamicViewEntity() |
| atpDiffComplexAlias = new ComplexAlias("-") |
| |
| conditionMap = [facilityId : facilityId] |
| |
| if (offsetQOHQty) { |
| try { |
| offsetQOH = Integer.parseInt(offsetQOHQty) |
| hasOffsetQOH = true |
| searchParameterString = searchParameterString + "&offsetQOHQty=" + offsetQOH |
| } catch (NumberFormatException nfe) { |
| } |
| } |
| if (offsetATPQty) { |
| try { |
| offsetATP = Integer.parseInt(offsetATPQty) |
| hasOffsetATP = true |
| searchParameterString = searchParameterString + "&offsetATPQty=" + offsetATP |
| } catch (NumberFormatException nfe) { |
| } |
| } |
| |
| prodView.addMemberEntity("PRFA", "ProductFacility") |
| prodView.addAlias("PRFA", "productId", null, null, null, Boolean.TRUE, null) |
| prodView.addAlias("PRFA", "minimumStock", null, null, null, Boolean.TRUE, null) |
| prodView.addAlias("PRFA", "reorderQuantity", null, null, null, Boolean.TRUE, null) |
| prodView.addAlias("PRFA", "daysToShip", null, null, null, Boolean.TRUE, null) |
| prodView.addAlias("PRFA", "facilityId", null, null, null, Boolean.TRUE, null) |
| |
| prodView.addMemberEntity("PROD", "Product") |
| prodView.addViewLink("PROD", "PRFA", Boolean.FALSE, ModelKeyMap.makeKeyMapList("productId")) |
| prodView.addAlias("PROD", "internalName", null, null, null, Boolean.TRUE, null) |
| prodView.addAlias("PROD", "isVirtual", null, null, null, Boolean.TRUE, null) |
| prodView.addAlias("PROD", "salesDiscontinuationDate", null, null, null, Boolean.TRUE, null) |
| if (productTypeId) { |
| prodView.addAlias("PROD", "productTypeId", null, null, null, Boolean.TRUE, null) |
| conditionMap.productTypeId = productTypeId |
| searchParameterString = searchParameterString + "&productTypeId=" + productTypeId |
| } |
| |
| prodView.addMemberEntity("IITE", "InventoryItem") |
| prodView.addViewLink("PRFA", "IITE", Boolean.FALSE, ModelKeyMap.makeKeyMapList("productId", "productId", "facilityId", "facilityId")) |
| prodView.addAlias("IITE", "totalQuantityOnHandTotal", "quantityOnHandTotal", null, null, null, "sum") |
| prodView.addAlias("IITE", "totalAvailableToPromiseTotal", "availableToPromiseTotal", null, null, null, "sum") |
| qohDiffComplexAlias = new ComplexAlias("-") |
| qohDiffComplexAlias.addComplexAliasMember(new ComplexAliasField("IITE", "quantityOnHandTotal", null, "sum")) |
| qohDiffComplexAlias.addComplexAliasMember(new ComplexAliasField("PRFA", "minimumStock", null, null)) |
| prodView.addAlias(null, "offsetQOHQtyAvailable", null, null, null, null, null, qohDiffComplexAlias) |
| atpDiffComplexAlias = new ComplexAlias("-") |
| atpDiffComplexAlias.addComplexAliasMember(new ComplexAliasField("IITE", "availableToPromiseTotal", null, "sum")) |
| atpDiffComplexAlias.addComplexAliasMember(new ComplexAliasField("PRFA", "minimumStock", null, null)) |
| prodView.addAlias(null, "offsetATPQtyAvailable", null, null, null, null, null, atpDiffComplexAlias) |
| |
| if (searchInProductCategoryId) { |
| prodView.addMemberEntity("PRCA", "ProductCategoryMember") |
| prodView.addViewLink("PRFA", "PRCA", Boolean.FALSE, ModelKeyMap.makeKeyMapList("productId")) |
| prodView.addAlias("PRCA", "productCategoryId", null, null, null, Boolean.TRUE, null) |
| conditionMap.productCategoryId = searchInProductCategoryId |
| searchParameterString = searchParameterString + "&searchInProductCategoryId=" + searchInProductCategoryId |
| } |
| |
| if (productSupplierId) { |
| prodView.addMemberEntity("SPPR", "SupplierProduct") |
| prodView.addViewLink("PRFA", "SPPR", Boolean.FALSE, ModelKeyMap.makeKeyMapList("productId")) |
| prodView.addAlias("SPPR", "partyId", null, null, null, Boolean.TRUE, null) |
| conditionMap.partyId = productSupplierId |
| searchParameterString = searchParameterString + "&productSupplierId=" + productSupplierId |
| } |
| |
| // set distinct on so we only get one row per product |
| searchCondition = EntityCondition.makeCondition(conditionMap, EntityOperator.AND) |
| notVirtualCondition = EntityCondition.makeCondition(EntityCondition.makeCondition("isVirtual", EntityOperator.EQUALS, null), |
| EntityOperator.OR, |
| EntityCondition.makeCondition("isVirtual", EntityOperator.NOT_EQUAL, "Y")) |
| |
| whereConditionsList = [searchCondition, notVirtualCondition] |
| // add the discontinuation date condition |
| if (productsSoldThruTimestamp) { |
| discontinuationDateCondition = EntityCondition.makeCondition( |
| [ |
| EntityCondition.makeCondition("salesDiscontinuationDate", EntityOperator.EQUALS, null), |
| EntityCondition.makeCondition("salesDiscontinuationDate", EntityOperator.GREATER_THAN,productsSoldThruTimestamp) |
| ], |
| EntityOperator.OR) |
| whereConditionsList.add(discontinuationDateCondition) |
| searchParameterString = searchParameterString + "&productsSoldThruTimestamp=" + productsSoldThruTimestamp |
| } |
| |
| // add search on internal name |
| if (internalName) { |
| whereConditionsList.add(EntityCondition.makeCondition("internalName", EntityOperator.LIKE, "%" + internalName + "%")) |
| searchParameterString = searchParameterString + "&internalName=" + internalName |
| } |
| |
| // add search on productId |
| if (productId) { |
| whereConditionsList.add(EntityCondition.makeCondition("productId", EntityOperator.LIKE, productId + "%")) |
| searchParameterString = searchParameterString + "&productId=" + productId |
| } |
| |
| orderBy = [] |
| if (hasOffsetATP) { |
| orderBy.add("offsetATPQtyAvailable") |
| } |
| if (hasOffsetQOH) { |
| orderBy.add("offsetQOHQtyAvailable") |
| } |
| orderBy.add("productId") |
| |
| // If the user has specified a number of months over which to sum usage quantities, define the correct timestamp |
| checkTime = null |
| monthsInPastLimitStr = request.getParameter("monthsInPastLimit") |
| if (monthsInPastLimitStr) { |
| try { |
| monthsInPastLimit = Integer.parseInt(monthsInPastLimitStr) |
| cal = UtilDateTime.toCalendar(null) |
| cal.add(Calendar.MONTH, 0 - monthsInPastLimit) |
| checkTime = UtilDateTime.toTimestamp(cal.getTime()) |
| searchParameterString += "&monthsInPastLimit=" + monthsInPastLimitStr |
| } catch (Exception e) { |
| // Ignore |
| } |
| } |
| |
| if (checkTime) { |
| |
| // Construct a dynamic view entity to search against for sales usage quantities |
| salesUsageViewEntity = new DynamicViewEntity() |
| salesUsageViewEntity.addMemberEntity("OI", "OrderItem") |
| salesUsageViewEntity.addMemberEntity("OH", "OrderHeader") |
| salesUsageViewEntity.addMemberEntity("ItIss", "ItemIssuance") |
| salesUsageViewEntity.addMemberEntity("InvIt", "InventoryItem") |
| salesUsageViewEntity.addViewLink("OI", "OH", false, ModelKeyMap.makeKeyMapList("orderId")) |
| salesUsageViewEntity.addViewLink("OI", "ItIss", false, ModelKeyMap.makeKeyMapList("orderId", "orderId", "orderItemSeqId", "orderItemSeqId")) |
| salesUsageViewEntity.addViewLink("ItIss", "InvIt", false, ModelKeyMap.makeKeyMapList("inventoryItemId")) |
| salesUsageViewEntity.addAlias("OI", "productId") |
| salesUsageViewEntity.addAlias("OH", "statusId") |
| salesUsageViewEntity.addAlias("OH", "orderTypeId") |
| salesUsageViewEntity.addAlias("OH", "orderDate") |
| salesUsageViewEntity.addAlias("ItIss", "inventoryItemId") |
| salesUsageViewEntity.addAlias("ItIss", "quantity") |
| salesUsageViewEntity.addAlias("InvIt", "facilityId") |
| |
| // Construct a dynamic view entity to search against for production usage quantities |
| productionUsageViewEntity = new DynamicViewEntity() |
| productionUsageViewEntity.addMemberEntity("WEIA", "WorkEffortInventoryAssign") |
| productionUsageViewEntity.addMemberEntity("WE", "WorkEffort") |
| productionUsageViewEntity.addMemberEntity("II", "InventoryItem") |
| productionUsageViewEntity.addViewLink("WEIA", "WE", false, ModelKeyMap.makeKeyMapList("workEffortId")) |
| productionUsageViewEntity.addViewLink("WEIA", "II", false, ModelKeyMap.makeKeyMapList("inventoryItemId")) |
| productionUsageViewEntity.addAlias("WEIA", "quantity") |
| productionUsageViewEntity.addAlias("WE", "actualCompletionDate") |
| productionUsageViewEntity.addAlias("WE", "workEffortTypeId") |
| productionUsageViewEntity.addAlias("II", "facilityId") |
| productionUsageViewEntity.addAlias("II", "productId") |
| } |
| |
| whereCondition = EntityCondition.makeCondition(whereConditionsList, EntityOperator.AND) |
| |
| beganTransaction = false |
| List prods = null |
| try { |
| beganTransaction = TransactionUtil.begin() |
| |
| // get the indexes for the partial list |
| lowIndex = ((viewIndex.intValue() * viewSize.intValue()) + 1) |
| highIndex = (viewIndex.intValue() + 1) * viewSize.intValue() |
| prodsEli = from(prodView).where(whereCondition).orderBy(orderBy).cursorScrollInsensitive().maxRows(highIndex).distinct().queryIterator() |
| |
| // get the partial list for this page |
| prods = prodsEli.getPartialList(lowIndex, highIndex) |
| prodsIt = prods.iterator() |
| while (prodsIt) { |
| oneProd = prodsIt.next() |
| offsetQOHQtyAvailable = oneProd.getBigDecimal("offsetQOHQtyAvailable") |
| offsetATPQtyAvailable = oneProd.getBigDecimal("offsetATPQtyAvailable") |
| if (hasOffsetATP) { |
| if (offsetATPQtyAvailable && offsetATPQtyAvailable.doubleValue() > offsetATP) { |
| break |
| } |
| } |
| if (hasOffsetQOH) { |
| if (offsetQOHQtyAvailable && offsetQOHQtyAvailable.doubleValue() > offsetQOH) { |
| break |
| } |
| } |
| |
| oneInventory = [:] |
| oneInventory.productId = oneProd.productId |
| oneInventory.minimumStock = oneProd.getBigDecimal("minimumStock") |
| oneInventory.reorderQuantity = oneProd.getBigDecimal("reorderQuantity") |
| oneInventory.daysToShip = oneProd.getString("daysToShip") |
| oneInventory.totalQuantityOnHand = oneProd.totalQuantityOnHandTotal |
| oneInventory.totalAvailableToPromise = oneProd.totalAvailableToPromiseTotal |
| oneInventory.offsetQOHQtyAvailable = offsetQOHQtyAvailable |
| oneInventory.offsetATPQtyAvailable = offsetATPQtyAvailable |
| oneInventory.quantityOnOrder = InventoryWorker.getOutstandingPurchasedQuantity(oneProd.productId, delegator) |
| |
| |
| if (checkTime) { |
| |
| // Make a query against the sales usage view entity |
| salesUsageIt = from(salesUsageViewEntity) |
| .where(EntityCondition.makeCondition("facilityId", EntityOperator.EQUALS, facilityId), |
| EntityCondition.makeCondition("productId", EntityOperator.EQUALS, oneProd.productId), |
| EntityCondition.makeCondition("statusId", EntityOperator.IN, ['ORDER_COMPLETED', 'ORDER_APPROVED', 'ORDER_HELD']), |
| EntityCondition.makeCondition("orderTypeId", EntityOperator.EQUALS, "SALES_ORDER"), |
| EntityCondition.makeCondition("orderDate", EntityOperator.GREATER_THAN_EQUAL_TO, checkTime)) |
| .queryIterator() |
| |
| // Sum the sales usage quantities found |
| salesUsageQuantity = 0 |
| salesUsageIt.each { salesUsageItem -> |
| if (salesUsageItem.quantity) { |
| try { |
| salesUsageQuantity += salesUsageItem.getDouble("quantity").doubleValue() |
| } catch (Exception e) { |
| // Ignore |
| } |
| } |
| } |
| salesUsageIt.close() |
| |
| // Make a query against the production usage view entity |
| productionUsageIt = from(productionUsageViewEntity) |
| .where(EntityCondition.makeCondition("facilityId", EntityOperator.EQUALS, facilityId), |
| EntityCondition.makeCondition("productId", EntityOperator.EQUALS, oneProd.productId), |
| EntityCondition.makeCondition("workEffortTypeId", EntityOperator.EQUALS, "PROD_ORDER_TASK"), |
| EntityCondition.makeCondition("actualCompletionDate", EntityOperator.GREATER_THAN_EQUAL_TO, checkTime)) |
| .queryIterator() |
| |
| // Sum the production usage quantities found |
| productionUsageQuantity = 0 |
| productionUsageIt.each { productionUsageItem -> |
| if (productionUsageItem.quantity) { |
| try { |
| productionUsageQuantity += productionUsageItem.getDouble("quantity").doubleValue() |
| } catch (Exception e) { |
| // Ignore |
| } |
| } |
| } |
| productionUsageIt.close() |
| oneInventory.usageQuantity = salesUsageQuantity + productionUsageQuantity |
| } |
| rows.add(oneInventory) |
| } |
| if (rows.size() < viewSize.intValue()) { |
| productListSize = lowIndex + rows.size() - 1 |
| } else { |
| // attempt to get the full size |
| if (hasOffsetQOH || hasOffsetATP) { |
| rowProcessed = 0 |
| while (nextValue = prodsEli.next()) { |
| offsetQOHQtyAvailable = nextValue.getDouble("offsetQOHQtyAvailable") |
| offsetATPQtyAvailable = nextValue.getDouble("offsetATPQtyAvailable") |
| if (hasOffsetATP) { |
| if (offsetATPQtyAvailable && offsetATPQtyAvailable.doubleValue() > offsetATP) { |
| break |
| } |
| } |
| if (hasOffsetQOH) { |
| if (offsetQOHQtyAvailable && offsetQOHQtyAvailable.doubleValue() > offsetQOH) { |
| break |
| } |
| } |
| rowProcessed++ |
| } |
| productListSize = lowIndex + rows.size() + rowProcessed - 1 |
| } else { |
| productListSize = prodsEli.getResultsSizeAfterPartialList() |
| } |
| } |
| prodsEli.close() |
| if (highIndex > productListSize) { |
| highIndex = productListSize |
| } |
| context.overrideListSize = productListSize |
| context.highIndex = highIndex |
| context.lowIndex = lowIndex |
| |
| } catch (GenericEntityException e) { |
| errMsg = "Failure in operation, rolling back transaction" |
| Debug.logError(e, errMsg, "ViewFacilityInventoryByProduct") |
| try { |
| // only rollback the transaction if we started one... |
| TransactionUtil.rollback(beganTransaction, errMsg, e) |
| } catch (GenericEntityException e2) { |
| Debug.logError(e2, "Could not rollback transaction: " + e2.toString(), "ViewFacilityInventoryByProduct") |
| } |
| // after rolling back, rethrow the exception |
| throw e |
| } finally { |
| if (prodsEli != null) { |
| try { |
| prodsEli.close() |
| } catch (Exception exc) {} |
| } |
| // only commit the transaction if we started one... this will throw an exception if it fails |
| TransactionUtil.commit(beganTransaction) |
| } |
| } |
| context.inventoryByProduct = rows |
| context.searchParameterString = searchParameterString |
