| /* |
| * 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.ofbiz.base.util.Debug |
| import org.ofbiz.base.util.ObjectType |
| import org.ofbiz.entity.* |
| import org.ofbiz.entity.condition.* |
| import org.ofbiz.entity.transaction.* |
| import org.ofbiz.entity.util.* |
| import org.ofbiz.entity.model.DynamicViewEntity |
| import org.ofbiz.entity.model.ModelKeyMap |
| import org.ofbiz.entity.model.ModelViewEntity.ComplexAlias |
| import org.ofbiz.entity.model.ModelViewEntity.ComplexAliasField |
| import org.ofbiz.entity.model.ModelViewEntity.ComplexAliasMember |
| import org.ofbiz.product.inventory.* |
| |
| action = request.getParameter("action"); |
| |
| searchParameterString = ""; |
| 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 |
| findOpts = new EntityFindOptions(true, EntityFindOptions.TYPE_SCROLL_INSENSITIVE, EntityFindOptions.CONCUR_READ_ONLY, true); |
| 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(); |
| findOpts.setMaxRows(highIndex); |
| prodsEli = delegator.findListIteratorByCondition(prodView, whereCondition, null, null, orderBy, findOpts); |
| |
| // 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 = delegator.findListIteratorByCondition(salesUsageViewEntity, |
| EntityCondition.makeCondition( |
| [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) |
| ], |
| EntityOperator.AND), null, null, null, null); |
| |
| // 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 = delegator.findListIteratorByCondition(productionUsageViewEntity, |
| EntityCondition.makeCondition( |
| [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) |
| ], |
| EntityOperator.AND), null, null, null, null); |
| |
| // 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; |
