svn commit: r519102 - in /ofbiz/trunk/applications/prod
Locked
 
|
Author: sichen
Date: Fri Mar 16 12:02:27 2007 New Revision: 519102 URL: http://svn.apache.org/viewvc?view=rev&rev=519102 Log: Provide 'Usage' column to the Facility inventory report, based on sales usage plus production usage. Sales usage is assembled via OrderItem/OrderHeader/ItemIssuance/InventoryItem, and production usage via WorkEffortInventoryAssign/WorkEffort/InventoryItem. There are two very similar bsh scripts involved with these screens at the moment, so changes were applied to both so that when one is eventually decided on, there won't be any porting necessary. Modified: ofbiz/trunk/applications/product/config/ProductUiLabels.properties ofbiz/trunk/applications/product/webapp/facility/WEB-INF/actions/facility/ViewFacilityInventoryByProduct.bsh ofbiz/trunk/applications/product/webapp/facility/WEB-INF/actions/facility/countFacilityInventoryByProduct.bsh ofbiz/trunk/applications/product/webapp/facility/facility/FacilityForms.xml Modified: ofbiz/trunk/applications/product/config/ProductUiLabels.properties URL: http://svn.apache.org/viewvc/ofbiz/trunk/applications/product/config/ProductUiLabels.properties?view=diff&rev=519102&r1=519101&r2=519102 ============================================================================== --- ofbiz/trunk/applications/product/config/ProductUiLabels.properties (original) +++ ofbiz/trunk/applications/product/config/ProductUiLabels.properties Fri Mar 16 12:02:27 2007 @@ -1342,6 +1342,7 @@ ProductUploadLinkOneImage=Upload Link One Image ProductUploadLinkTwoImage=Upload Link Two Image ProductUpSell=Try these instead of +ProductUsage=Usage ProductUseCount=Use Count ProductUseCountLimit=Use Count Limit ProductUseDays=Use Days Modified: ofbiz/trunk/applications/product/webapp/facility/WEB-INF/actions/facility/ViewFacilityInventoryByProduct.bsh URL: http://svn.apache.org/viewvc/ofbiz/trunk/applications/product/webapp/facility/WEB-INF/actions/facility/ViewFacilityInventoryByProduct.bsh?view=diff&rev=519102&r1=519101&r2=519102 ============================================================================== --- ofbiz/trunk/applications/product/webapp/facility/WEB-INF/actions/facility/ViewFacilityInventoryByProduct.bsh (original) +++ ofbiz/trunk/applications/product/webapp/facility/WEB-INF/actions/facility/ViewFacilityInventoryByProduct.bsh Fri Mar 16 12:02:27 2007 @@ -150,6 +150,55 @@ TransactionUtil.commit(beganTransaction); } + // 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 (UtilValidate.isNotEmpty(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 (! UtilValidate.isEmpty(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"); + + } + prodsIt = prods.iterator(); while (prodsIt.hasNext()) { @@ -194,6 +243,64 @@ oneInventory.put("offsetQOHQtyAvailable", offsetQOHQtyAvailable); oneInventory.put("offsetATPQtyAvailable", offsetATPQtyAvailable); oneInventory.put("quantityOnOrder", quantityOnOrder); + + if (! UtilValidate.isEmpty(checkTime)) { + + // Make a query against the sales usage view entity + salesUsageIt = delegator.findListIteratorByCondition(salesUsageViewEntity, + new EntityConditionList( + UtilMisc.toList( + new EntityExpr("facilityId", EntityOperator.EQUALS, "WebStoreWarehouse"), + new EntityExpr("productId", EntityOperator.EQUALS, oneProd.getString("productId")), + new EntityExpr("statusId", EntityOperator.IN, UtilMisc.toList("ORDER_COMPLETED", "ORDER_APPROVED", "ORDER_HELD")), + new EntityExpr("orderTypeId", EntityOperator.EQUALS, "SALES_ORDER"), + new EntityExpr("orderDate", EntityOperator.GREATER_THAN_EQUAL_TO, checkTime) + ), + EntityOperator.AND), + null, null, null, null); + + // Sum the sales usage quantities found + salesUsageQuantity = 0; + while((salesUsageItem = salesUsageIt.next()) != null) { + if (salesUsageItem.get("quantity") != null) { + 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, + new EntityConditionList( + UtilMisc.toList( + new EntityExpr("facilityId", EntityOperator.EQUALS, "WebStoreWarehouse"), + new EntityExpr("productId", EntityOperator.EQUALS, oneProd.getString("productId")), + new EntityExpr("workEffortTypeId", EntityOperator.EQUALS, "PROD_ORDER_TASK"), + new EntityExpr("actualCompletionDate", EntityOperator.GREATER_THAN_EQUAL_TO, checkTime) + ), + EntityOperator.AND), + null, null, null, null); + + // Sum the production usage quantities found + productionUsageQuantity = 0; + while((productionUsageItem = productionUsageIt.next()) != null) { + if (productionUsageItem.get("quantity") != null) { + try { + productionUsageQuantity += productionUsageItem.getDouble("quantity").doubleValue(); + } catch (Exception e) { + // Ignore + } + } + } + productionUsageIt.close(); + + oneInventory.put("usageQuantity", salesUsageQuantity + productionUsageQuantity); + + } + rows.add(oneInventory); } Modified: ofbiz/trunk/applications/product/webapp/facility/WEB-INF/actions/facility/countFacilityInventoryByProduct.bsh URL: http://svn.apache.org/viewvc/ofbiz/trunk/applications/product/webapp/facility/WEB-INF/actions/facility/countFacilityInventoryByProduct.bsh?view=diff&rev=519102&r1=519101&r2=519102 ============================================================================== --- ofbiz/trunk/applications/product/webapp/facility/WEB-INF/actions/facility/countFacilityInventoryByProduct.bsh (original) +++ ofbiz/trunk/applications/product/webapp/facility/WEB-INF/actions/facility/countFacilityInventoryByProduct.bsh Fri Mar 16 12:02:27 2007 @@ -173,6 +173,55 @@ } 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 (UtilValidate.isNotEmpty(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 (! UtilValidate.isEmpty(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"); + + } + EntityCondition whereCondition = new EntityConditionList(whereConditionsList, EntityOperator.AND); boolean beganTransaction = false; @@ -215,6 +264,64 @@ oneInventory.put("offsetQOHQtyAvailable", offsetQOHQtyAvailable); oneInventory.put("offsetATPQtyAvailable", offsetATPQtyAvailable); oneInventory.put("quantityOnOrder", InventoryWorker.getOutstandingPurchasedQuantity(oneProd.getString("productId"), delegator)); + + if (! UtilValidate.isEmpty(checkTime)) { + + // Make a query against the sales usage view entity + salesUsageIt = delegator.findListIteratorByCondition(salesUsageViewEntity, + new EntityConditionList( + UtilMisc.toList( + new EntityExpr("facilityId", EntityOperator.EQUALS, "WebStoreWarehouse"), + new EntityExpr("productId", EntityOperator.EQUALS, oneProd.getString("productId")), + new EntityExpr("statusId", EntityOperator.IN, UtilMisc.toList("ORDER_COMPLETED", "ORDER_APPROVED", "ORDER_HELD")), + new EntityExpr("orderTypeId", EntityOperator.EQUALS, "SALES_ORDER"), + new EntityExpr("orderDate", EntityOperator.GREATER_THAN_EQUAL_TO, checkTime) + ), + EntityOperator.AND), + null, null, null, null); + + // Sum the sales usage quantities found + salesUsageQuantity = 0; + while((salesUsageItem = salesUsageIt.next()) != null) { + if (salesUsageItem.get("quantity") != null) { + 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, + new EntityConditionList( + UtilMisc.toList( + new EntityExpr("facilityId", EntityOperator.EQUALS, "WebStoreWarehouse"), + new EntityExpr("productId", EntityOperator.EQUALS, oneProd.getString("productId")), + new EntityExpr("workEffortTypeId", EntityOperator.EQUALS, "PROD_ORDER_TASK"), + new EntityExpr("actualCompletionDate", EntityOperator.GREATER_THAN_EQUAL_TO, checkTime) + ), + EntityOperator.AND), + null, null, null, null); + + // Sum the production usage quantities found + productionUsageQuantity = 0; + while((productionUsageItem = productionUsageIt.next()) != null) { + if (productionUsageItem.get("quantity") != null) { + try { + productionUsageQuantity += productionUsageItem.getDouble("quantity").doubleValue(); + } catch (Exception e) { + // Ignore + } + } + } + productionUsageIt.close(); + + oneInventory.put("usageQuantity", salesUsageQuantity + productionUsageQuantity); + + } + rows.add(oneInventory); } if (rows.size() < viewSize.intValue()) { Modified: ofbiz/trunk/applications/product/webapp/facility/facility/FacilityForms.xml URL: http://svn.apache.org/viewvc/ofbiz/trunk/applications/product/webapp/facility/facility/FacilityForms.xml?view=diff&rev=519102&r1=519101&r2=519102 ============================================================================== --- ofbiz/trunk/applications/product/webapp/facility/facility/FacilityForms.xml (original) +++ ofbiz/trunk/applications/product/webapp/facility/facility/FacilityForms.xml Fri Mar 16 12:02:27 2007 @@ -100,6 +100,7 @@ <date-time default-value="${bsh: org.ofbiz.base.util.UtilDateTime.nowTimestamp()}"/> </field> <field name="VIEW_SIZE" entry-name="viewSize" title="${uiLabelMap.ProductShowProductsPerPage}" widget-style="inputBox"><text/></field> + <field name="monthsInPastLimit" entry-name="monthsInPastLimit" title="monthsInPastLimit" widget-style="inputBox"><text/></field> <field name="submitButton" title="${uiLabelMap.CommonFind}" widget-style="smallSubmit"> <submit button-type="button"/> </field> @@ -123,6 +124,7 @@ <field name="daysToShip" title="${uiLabelMap.ProductDaysToShip}" widget-area-style="tabletextright"><display/></field> <field name="offsetQOHQtyAvailable" title="${uiLabelMap.ProductQtyOffsetQOH}" widget-area-style="tabletextright"><display/></field> <field name="offsetATPQtyAvailable" title="${uiLabelMap.ProductQtyOffsetATP}" widget-area-style="tabletextright"><display/></field> + <field name="usageQuantity" title="${uiLabelMap.ProductUsage}" widget-area-style="tabletextright"><display/></field> </form> <form name="SearchInventoryItemsParams" type="single" target="SearchInventoryItems" |
«
Return to OFBiz - Commits
|
1 view|%1 views
| Free forum by Nabble | Edit this page |