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"
