UtilCOGS


1   /*
2    * Copyright (c) 2006 - 2007 Open Source Strategies, Inc.
3    *
4    * This program is free software; you can redistribute it and/or modify
5    * it under the terms of the Honest Public License.
6    *
7    * This program is distributed in the hope that it will be useful,
8    * but WITHOUT ANY WARRANTY; without even the implied warranty of
9    * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
10   * Honest Public License for more details.
11   *
12   * You should have received a copy of the Honest Public License
13   * along with this program; if not, write to Funambol,
14   * 643 Bair Island Road, Suite 305 - Redwood City, CA 94063, USA
15   */
16  
17  package com.opensourcestrategies.financials.util;
18  
19  import java.util.*;
20  import java.sql.Timestamp  ;
21  import java.math.BigDecimal  ;
22  
23  import javolution.util.FastMap;
24  
25  import org.ofbiz.accounting.AccountingException;
26  import org.ofbiz.base.util.*;
27  import org.ofbiz.entity.*;
28  import org.ofbiz.entity.condition.*;
29  import org.ofbiz.entity.util.*;
30  import org.ofbiz.service.*;
31  
32  import com.opensourcestrategies.financials.util.UtilFinancial;
33  
34  /**
35   * UtilCOGS - Utilities for Cost of Goods Sold services and calculations
36   *
37   * @author     <a HREF="mailto:leon@opensourcestrategies.com">Leon Torres</a> 
38   * @version    $Rev: 81 $
39   * @since      2.2
40   */
41  
42  public class UtilCOGS {
43      
44      public static String   module = UtilCOGS.class.getName();
45  
46      public static int decimals = UtilNumber.getBigDecimalScale("fin_arithmetic.properties", "financial.statements.decimals");
47      public static int rounding = UtilNumber.getBigDecimalRoundingMode("fin_arithmetic.properties", "financial.statements.rounding");
48      public static final BigDecimal   ZERO = new BigDecimal  ("0");
49      
50      /**
51       * Attempts to find the average cost of a given product.  First it will look in ProductAverageCost.  If a non-zero ProductAverageCost is found, it wil return it. 
52       * Otherwise, it will call calculateProductCosts to find the average cost of the product in CostComponent.  
53       * A null will be returned if no cost was found in either ProductAverageCost or CostComponent, or if the find attempts failed for some other entity/service reason.
54       */
55      public static BigDecimal   getProductAverageCost(String   productId, String   organizationPartyId, GenericValue userLogin, GenericDelegator delegator, LocalDispatcher dispatcher) {
56          BigDecimal   cost = null;
57  
58          try {
59              // first try using the first unexpired ProductAverageCost.averageCost as the cost
60              EntityConditionList conditions = new EntityConditionList(UtilMisc.toList(
61                          EntityUtil.getFilterByDateExpr(),
62                          new EntityExpr("productId", EntityOperator.EQUALS, productId),
63                          new EntityExpr("organizationPartyId", EntityOperator.EQUALS, organizationPartyId)
64                          ), EntityOperator.AND);
65              GenericValue entry = EntityUtil.getFirst( delegator.findByCondition("ProductAverageCost", conditions, UtilMisc.toList("averageCost"), null) );
66              if (entry != null) {
67                  cost = entry.getBigDecimal("averageCost");
68              }
69  
70              // if we found a non-zero cost via ProductAverageCost, then we're done
71              if ((cost != null) && (cost.signum() != 0)) return cost;
72  
73              // otherwise we have to look up the standard costs (the CostComponents with prefix EST_STD_)  If we found a non-zero standard cost, then it becomes a cost 
74              GenericValue acctgPref = delegator.findByPrimaryKeyCache("PartyAcctgPreference", UtilMisc.toMap("partyId", organizationPartyId));
75              Map results = dispatcher.runSync("calculateProductCosts", UtilMisc.toMap("productId", productId, "currencyUomId", acctgPref.get("baseCurrencyUomId"), "costComponentTypePrefix", "EST_STD", "userLogin", userLogin));
76              if (!ServiceUtil.isError(results)) {
77                  Double   totalCost = (Double  ) results.get("totalCost");
78                  if ((totalCost != null) && (totalCost.doubleValue() != 0.0)) {
79                      cost = new BigDecimal  (totalCost.doubleValue());
80                  }
81              }
82  
83              // at this point, return whatever cost we have, be it null or zero
84              return cost;
85  
86          } catch (GenericEntityException e) {
87              Debug.logError("Failed to get product average cost for productId [" + productId + "] and organization [" + organizationPartyId + "] due to entity error.", module);
88              return null;
89          } catch (GenericServiceException e) {
90              Debug.logError("Failed to get product average cost for productId [" + productId + "] and organization [" + organizationPartyId + "] due to service error.", module);
91              return null;
92          }
93      }
94  
95  
96      /**
97       * Gets the value of a product in the inventory of an organization.
98       * The value is the sum of posted debits minus the sum of posted credits
99       * in the INVENTORY_ACCOUNT or INV_ADJ_AVG_COST GL accounts for this product.
100      *
101      * @param   productId               The product to get the value of
102      * @param   organizationPartyId     Organization of the transactions, which is required.
103      * @param   transactionDate         An optional timestamp. If specified, the sum will be taken up to this date, inclusive.
104      * @return  BigDecimal value of the product
105      */
106     public static BigDecimal   getInventoryValueForProduct(String   productId, String   organizationPartyId, Timestamp   transactionDate, GenericDelegator delegator)
107         throws GenericEntityException {
108         Map results = getNetInventoryValueHelper(productId, null, organizationPartyId, transactionDate, delegator);
109         return (BigDecimal  ) results.get(productId);
110     }
111 
112     /**
113      * Gets the value of all products in the inventory of an organization.
114      * The value is the sum of posted debits minus the sum of posted credits
115      * in the INVENTORY_ACCOUNT or INV_ADJ_AVG_COST GL accounts for each product.
116      *
117      * @param   productId               Specify this to limit the query to one product
118      * @param   organizationPartyId     Organization of the transactions, always specify this.
119      * @param   transactionDate         Specify this to sum over all transactions before the transactionDate or set to null to sum over all dates
120      * @return  Map of productIds keyed to their net (debit - credit) amounts
121      */
122     public static Map getInventoryValueForAllProducts(String   organizationPartyId, Timestamp   transactionDate, GenericDelegator delegator)
123         throws GenericEntityException {
124         return getNetInventoryValueHelper(null, null, organizationPartyId, transactionDate, delegator);
125     }
126 
127     /**
128      * Gets the value of all products in the inventory of an organization with the given condition.
129      * The value is the sum of posted debits minus the sum of posted credits
130      * in the INVENTORY_ACCOUNT or INV_ADJ_AVG_COST GL accounts for each product.
131      *
132      * @param   condition               EntityCondition to constrain the results using AcctgTransEntryProdSums
133      * @param   organizationPartyId     Organization of the transactions, always specify this.
134      * @param   transactionDate         Specify this to sum over all transactions before the transactionDate or set to null to sum over all dates
135      * @return  Map of productIds keyed to their net (debit - credit) amounts
136      */
137     public static Map getInventoryValueForProductsByCondition(EntityCondition condition, String   organizationPartyId, Timestamp   transactionDate, GenericDelegator delegator)
138         throws GenericEntityException {
139         return getNetInventoryValueHelper(null, condition, organizationPartyId, transactionDate, delegator);
140     }
141 
142     /**
143      * Helper method to get product inventory values. Use one of the other methods that implements this,
144      * getInventoryValueForProduct() or getInventoryValueForAllProducts().
145      *
146      * @param   productId               Specify this to limit the query to one product or match on productId (to get match behavior, specify condition)
147      * @param   condition               EntityCondition to constrain the results using AcctgTransEntryProdSums
148      * @param   organizationPartyId     Organization of the transactions, always specify this.
149      * @param   transactionDate         Specify this to sum over all transactions before the transactionDate or set to null to sum over all dates
150      * @return  Map of productIds keyed to their net (debit - credit) amounts
151      */
152     private static Map getNetInventoryValueHelper(String   productId, EntityCondition condition, String   organizationPartyId, Timestamp   transactionDate, GenericDelegator delegator)
153         throws GenericEntityException {
154 
155         if (organizationPartyId == null) {
156             throw new GenericEntityException("No organizationPartyId specified for getting product inventory value(s).");
157         }
158 
159         // fields to select: exclude the transaction date from select list, otherwise we can't use the special view
160         List selectFields = UtilMisc.toList("productId", "amount", "glAccountTypeId");
161 
162         // common AND conditions
163         List commonConditions = UtilMisc.toList(
164                 new EntityExpr("isPosted", EntityOperator.EQUALS, "Y"),
165                 new EntityExpr("organizationPartyId", EntityOperator.EQUALS, organizationPartyId)
166                 );
167 
168         // select all the inventory account types
169         commonConditions.add(new EntityConditionList(UtilMisc.toList(
170                     new EntityExpr("glAccountTypeId", EntityOperator.EQUALS, "INVENTORY_ACCOUNT"),
171                     new EntityExpr("glAccountTypeId", EntityOperator.EQUALS, "INV_ADJ_AVG_COST"),
172                     new EntityExpr("glAccountTypeId", EntityOperator.EQUALS, "RAWMAT_INVENTORY"),
173                     new EntityExpr("glAccountTypeId", EntityOperator.EQUALS, "WIP_INVENTORY")
174                     ), EntityOperator.OR));
175 
176         // add optional constraints to common conditions
177         if (productId != null) commonConditions.add(new EntityExpr("productId", EntityOperator.EQUALS, productId));
178         if (condition != null) commonConditions.add(condition);
179         if (transactionDate != null) commonConditions.add(new EntityExpr("transactionDate", EntityOperator.LESS_THAN_EQUAL_TO, transactionDate));
180 
181         // build condition for debits
182         List debitConditions = new ArrayList(commonConditions);
183         debitConditions.add(new EntityExpr("debitCreditFlag", EntityOperator.EQUALS, "D"));
184         EntityConditionList debitConditionList = new EntityConditionList(debitConditions, EntityOperator.AND);
185 
186         // perform the query
187         EntityListIterator listIt = delegator.findListIteratorByCondition("AcctgTransEntryProdSums", debitConditionList, null, selectFields,
188                 UtilMisc.toList("productId"), // fields to order by
189                 // the first true here is for "specifyTypeAndConcur" the second true is for a distinct select
190                 new EntityFindOptions(true, EntityFindOptions.TYPE_SCROLL_INSENSITIVE, EntityFindOptions.CONCUR_READ_ONLY, true));
191 
192         // get all values from the entity list iterator
193         List debitSums = listIt.getCompleteList();
194 
195         // build condition for credits
196         List creditConditions = new ArrayList(commonConditions);
197         creditConditions.add(new EntityExpr("debitCreditFlag", EntityOperator.EQUALS, "C"));
198         EntityConditionList creditConditionList = new EntityConditionList(creditConditions, EntityOperator.AND);
199 
200         // perform the query
201         listIt = delegator.findListIteratorByCondition("AcctgTransEntryProdSums", creditConditionList, null, selectFields,
202                 UtilMisc.toList("productId"), // fields to order by
203                 // the first true here is for "specifyTypeAndConcur" the second true is for a distinct select
204                 new EntityFindOptions(true, EntityFindOptions.TYPE_SCROLL_INSENSITIVE, EntityFindOptions.CONCUR_READ_ONLY, true));
205 
206         // get all values from the entity list iterator
207         List creditSums = listIt.getCompleteList();
208         
209         // make a map of the product Id to the (debit - credit) amount
210         Map inventoryValueByProduct = FastMap.newInstance();
211 
212         // the strategy is to store the negative of the credit amount in the map first (because debits might not exist for a particular product)
213         for (Iterator iter = creditSums.iterator(); iter.hasNext(); ) {
214             GenericValue value = (GenericValue) iter.next();
215             BigDecimal   creditAmount = (BigDecimal  ) inventoryValueByProduct.get(UtilFinancial.getProductIdOrDefault(value));            
216             if (creditAmount == null) creditAmount = ZERO;
217             inventoryValueByProduct.put(UtilFinancial.getProductIdOrDefault(value), creditAmount.subtract(value.getBigDecimal("amount")).setScale(decimals, rounding));
218         }
219 
220         // then go through debits and add the debit amounts
221         for (Iterator iter = debitSums.iterator(); iter.hasNext(); ) {
222             GenericValue value = (GenericValue) iter.next();
223             BigDecimal   debitAmount = (BigDecimal  ) inventoryValueByProduct.get(UtilFinancial.getProductIdOrDefault(value));
224             if (debitAmount == null) debitAmount = ZERO;  // if debit didn't exist, set to zero
225             BigDecimal   difference = value.getBigDecimal("amount").add(debitAmount).setScale(decimals, rounding);
226             inventoryValueByProduct.put(UtilFinancial.getProductIdOrDefault(value), difference);
227         }
228 
229         return inventoryValueByProduct;
230     }
231     
232     /**
233      * Method to get total inventory quantity on hand in all facilities for a given product and organizataiton.
234      */
235     public static BigDecimal   getInventoryQuantityForProduct(String   productId, String   organizationPartyId, GenericDelegator delegator, LocalDispatcher dispatcher) 
236         throws GenericServiceException, GenericEntityException {
237         BigDecimal   quantity = ZERO;
238 
239         // the strategy is to loop through the organization facilities and get the product inventory for each facility
240         List facilities = delegator.findByAnd("Facility", UtilMisc.toMap("ownerPartyId", organizationPartyId));
241         for (Iterator iter = facilities.iterator(); iter.hasNext(); ) {
242             String   facilityId = ((GenericValue) iter.next()).getString("facilityId");
243             Map serviceResults = dispatcher.runSync("getInventoryAvailableByFacility", UtilMisc.toMap("productId", productId, "facilityId", facilityId));
244             if (ServiceUtil.isError(serviceResults)) {
245                 throw new GenericServiceException(ServiceUtil.getErrorMessage(serviceResults));
246             }
247             BigDecimal   facilityQuantity = new BigDecimal  (((Double  ) serviceResults.get("quantityOnHandTotal")).doubleValue());
248             quantity = quantity.add(facilityQuantity).setScale(decimals, rounding); // TODO: quantity sum needs its own decimals and rounding
249         }
250         return quantity;
251     }
252     
253     /**
254      * Returns the total inventory value based on data from InventoryItem rather than accounting ledger data
255      * 
256      * @param productId
257      * @param organizationPartyId
258      * @param delegator
259      * @param dispatcher
260      * @return
261      * @throws GenericEntityException
262      * @throws GenericServiceException
263      */
264     public static BigDecimal   getInventoryValueFromItems(String   productId, String   organizationPartyId, GenericDelegator delegator, LocalDispatcher dispatcher) 
265         throws GenericEntityException, GenericServiceException {
266         List items = delegator.findByAnd("InventoryItem", UtilMisc.toMap("productId", productId, "ownerPartyId", organizationPartyId));
267         BigDecimal   total = ZERO;
268         
269         for (Iterator it = items.iterator(); it.hasNext(); ) {
270             GenericValue item = (GenericValue) it.next();
271             if ((item.get("unitCost") != null) && (item.get("quantityOnHandTotal") != null)) {
272                 BigDecimal   conversionFactor = new BigDecimal  (UtilFinancial.determineUomConversionFactor(delegator, dispatcher, organizationPartyId, item.getString("currencyUomId")));
273                 // this precision is probably OK since we're multiplying
274                 total = total.add(item.getBigDecimal("unitCost").multiply(item.getBigDecimal("quantityOnHandTotal")).multiply(conversionFactor)).setScale(decimals, rounding);
275             }
276         }
277         
278         return total;
279     }
280 }
281
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags