KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > opensourcestrategies > financials > accounts > AccountsHelper


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.accounts;
18
19 import java.math.BigDecimal JavaDoc;
20 import java.sql.Timestamp JavaDoc;
21 import java.util.Iterator JavaDoc;
22 import java.util.LinkedList JavaDoc;
23 import java.util.List JavaDoc;
24 import java.util.Map JavaDoc;
25
26 import javolution.util.FastMap;
27
28 import org.ofbiz.accounting.invoice.InvoiceWorker;
29 import org.ofbiz.base.util.Debug;
30 import org.ofbiz.base.util.UtilDateTime;
31 import org.ofbiz.base.util.UtilMisc;
32 import org.ofbiz.base.util.UtilNumber;
33 import org.ofbiz.entity.GenericDelegator;
34 import org.ofbiz.entity.GenericEntityException;
35 import org.ofbiz.entity.GenericValue;
36 import org.ofbiz.entity.condition.EntityConditionList;
37 import org.ofbiz.entity.condition.EntityExpr;
38 import org.ofbiz.entity.condition.EntityOperator;
39 import com.opensourcestrategies.financials.invoice.InvoiceWithOutstandingBalance;
40
41 public class AccountsHelper {
42
43     public static String JavaDoc module = AccountsHelper.class.getName();
44     
45     public static int decimals = UtilNumber.getBigDecimalScale("fin_arithmetic.properties", "financial.statements.decimals");
46     public static int rounding = UtilNumber.getBigDecimalRoundingMode("fin_arithmetic.properties", "financial.statements.rounding");
47     protected static final BigDecimal JavaDoc ZERO = new BigDecimal JavaDoc("0"); // TODO: this will soon be UtilNumber.BD_ZERO
48 protected static final BigDecimal JavaDoc MILLISECONDS_PER_DAY = new BigDecimal JavaDoc("86400000");
49
50     /**
51      * Find the sum of transaction entry amounts by partyId for the given parameters
52      *
53      * @param organizationPartyId
54      * @param glAccountId
55      * @param glAccountTypeId
56      * @param glFiscalTypeId
57      * @param debitCreditFlag
58      * @param partyId
59      * @param roleTypeId
60      * @param asOfDate
61      * @param delegator
62      * @return
63      * @throws GenericEntityException
64      */
65     public static List JavaDoc getAcctgTransPartySums(String JavaDoc organizationPartyId, String JavaDoc glAccountTypeId, String JavaDoc glFiscalTypeId, String JavaDoc debitCreditFlag,
66             String JavaDoc partyId, String JavaDoc roleTypeId, Timestamp JavaDoc asOfDate, GenericDelegator delegator) throws GenericEntityException {
67     
68         List JavaDoc conditions = UtilMisc.toList(
69                 new EntityExpr("organizationPartyId", EntityOperator.EQUALS, organizationPartyId),
70                 new EntityExpr("debitCreditFlag", EntityOperator.EQUALS, debitCreditFlag),
71                 new EntityExpr("isPosted", EntityOperator.EQUALS, "Y"),
72                 new EntityExpr("glAccountTypeId", EntityOperator.EQUALS, glAccountTypeId),
73                 new EntityExpr("glFiscalTypeId", EntityOperator.EQUALS, glFiscalTypeId),
74                 new EntityExpr("transactionDate", EntityOperator.LESS_THAN_EQUAL_TO, asOfDate));
75         if (partyId != null) {
76             conditions.add(new EntityExpr("partyId", EntityOperator.EQUALS, partyId));
77         }
78         if (roleTypeId != null) {
79             conditions.add(new EntityExpr("roleTypeId", EntityOperator.EQUALS, roleTypeId));
80         }
81         
82         EntityConditionList findConditions = new EntityConditionList(conditions, EntityOperator.AND);
83
84         List JavaDoc fieldsToGet = UtilMisc.toList("partyId", "amount");
85         
86         List JavaDoc transactionEntries = delegator.findByCondition("AcctgTransEntryPartySum", findConditions,
87                 fieldsToGet, // get these fields
88 UtilMisc.toList("partyId")); // order by these fields
89
90         return transactionEntries;
91     }
92
93     /**
94      * Canonical method to get transaction balances by glAccountTypeid.
95      * Use one of the helper methods defined after this method.
96      *
97      * @param glAccountTypeId Account type to sum over. this determines the sign of the resulting balance.
98      * For instance, in the case of receivables (incoming), sum of all debit - sum of all credit.
99      * In the case of payables (outgoing), sum of all credit - sum of all debit.
100      * @param partyId If specified, limit the search result to the given partyId
101      * @param glFiscalTypeId
102      * @param asOfDateTime Timestamp to sum up to. TODO: investigate the boundary conditions
103      */
104     public static Map JavaDoc getBalancesHelper(String JavaDoc glAccountTypeId, String JavaDoc organizationPartyId, String JavaDoc partyId, String JavaDoc glFiscalTypeId,
105             Timestamp JavaDoc asOfDateTime, GenericDelegator delegator) throws GenericEntityException {
106
107         // set up a convenience boolean for testing receivables/payables
108 boolean isReceivable = (glAccountTypeId.equals("ACCOUNTS_RECEIVABLE") ? true : false);
109         String JavaDoc roleTypeId = (isReceivable ? "BILL_TO_CUSTOMER" : "BILL_FROM_VENDOR");
110         
111         // query for debit and credit balances
112 List JavaDoc debitBalances = getAcctgTransPartySums(organizationPartyId, glAccountTypeId, glFiscalTypeId, "D", partyId, roleTypeId,
113                 asOfDateTime, delegator);
114         List JavaDoc creditBalances = getAcctgTransPartySums(organizationPartyId, glAccountTypeId, glFiscalTypeId, "C", partyId, roleTypeId,
115                 asOfDateTime, delegator);
116
117         // return map has key partyId to value balance
118 Map JavaDoc balances = FastMap.newInstance();
119
120         // go through debits and put either the (debitBalance) for receivables or (ZERO - debitBalance) for payables
121 for (Iterator JavaDoc iter = debitBalances.iterator(); iter.hasNext(); ) {
122             GenericValue balance = (GenericValue) iter.next();
123             BigDecimal JavaDoc balanceAmount = balance.getBigDecimal("amount").setScale(decimals, rounding);
124             if (!isReceivable) balanceAmount = ZERO.subtract(balanceAmount);
125             balances.put(balance.get("partyId"), balanceAmount);
126         }
127
128         // now go through credits and add to debitBalance (default ZERO) the following: (creditBalance) for payables or (ZERO - creditBalance) for receivables
129 for (Iterator JavaDoc iter = creditBalances.iterator(); iter.hasNext(); ) {
130             GenericValue balance = (GenericValue) iter.next();
131             BigDecimal JavaDoc balanceAmount = balance.getBigDecimal("amount").setScale(decimals, rounding);
132             if (isReceivable) balanceAmount = ZERO.subtract(balanceAmount);
133
134             // see if a debitBalance exists, otherwise default to ZERO
135 BigDecimal JavaDoc debitBalance = (BigDecimal JavaDoc) balances.get(balance.get("partyId"));
136             if (debitBalance == null) debitBalance = ZERO;
137
138             // add them together and put in balance map
139 balanceAmount = balanceAmount.add(debitBalance);
140             balances.put(balance.get("partyId"), balanceAmount);
141         }
142
143         return balances;
144     }
145
146     /** Gets ACCOUNTS_RECEIVABLE balances for all customers in an organizatoin up to the asOfDateTime. Returns a Map of partyId keys to BigDecimal balance values */
147     public static Map JavaDoc getBalancesForAllCustomers(String JavaDoc organizationPartyId, String JavaDoc glFiscalTypeId, Timestamp JavaDoc asOfDateTime, GenericDelegator delegator) throws GenericEntityException {
148         return getBalancesHelper("ACCOUNTS_RECEIVABLE", organizationPartyId, null, glFiscalTypeId, asOfDateTime, delegator);
149     }
150
151     /** Gets ACCOUNTS_PAYABLE balances for all vendors in an organizatoin up to the asOfDateTime. Returns a Map of partyId keys to BigDecimal balance values */
152     public static Map JavaDoc getBalancesForAllVendors(String JavaDoc organizationPartyId, String JavaDoc glFiscalTypeId, Timestamp JavaDoc asOfDateTime, GenericDelegator delegator) throws GenericEntityException {
153         return getBalancesHelper("ACCOUNTS_PAYABLE", organizationPartyId, null, glFiscalTypeId, asOfDateTime, delegator);
154     }
155
156     /** Gets ACCOUNTS_RECEIVABLE balance for a given customer in an organizatoin up to the asOfDateTime. Returns the balance as a BigDecimal */
157     public static BigDecimal JavaDoc getBalanceForCustomerPartyId(String JavaDoc customerPartyId, String JavaDoc organizationPartyId, String JavaDoc glFiscalTypeId, Timestamp JavaDoc asOfDateTime, GenericDelegator delegator)
158         throws GenericEntityException {
159         return (BigDecimal JavaDoc) getBalancesHelper("ACCOUNTS_RECEIVABLE", organizationPartyId, customerPartyId, glFiscalTypeId, asOfDateTime, delegator).get(customerPartyId);
160     }
161
162     /** Gets ACCOUNTS_PAYABLE balance for a given vendor in an organizatoin up to the asOfDateTime. Returns the balance as a BigDecimal */
163     public static BigDecimal JavaDoc getBalanceForVendorPartyId(String JavaDoc vendorPartyId, String JavaDoc organizationPartyId, String JavaDoc glFiscalTypeId, Timestamp JavaDoc asOfDateTime, GenericDelegator delegator)
164         throws GenericEntityException {
165         return (BigDecimal JavaDoc) getBalancesHelper("ACCOUNTS_PAYABLE", organizationPartyId, vendorPartyId, glFiscalTypeId, asOfDateTime, delegator).get(vendorPartyId);
166     }
167     
168     /**
169      * Gets unpaid invoice balances for customer (SALES_INVOICE)
170      * See getUnpaidInvoicesHelper for parameter information
171      */
172     public static Map JavaDoc getUnpaidInvoicesForCustomers(String JavaDoc organizationPartyId, List JavaDoc daysOutstandingPoints, Timestamp JavaDoc asOfDateTime, GenericDelegator delegator)
173         throws GenericEntityException {
174         return getUnpaidInvoicesHelper(organizationPartyId, "SALES_INVOICE", daysOutstandingPoints, asOfDateTime, delegator);
175     }
176
177     /**
178      * Gets unpaid invoice balances for vendor (PURCHASE_INVOICE)
179      * See getUnpaidInvoicesHelper for parameter information
180      */
181     public static Map JavaDoc getUnpaidInvoicesForVendors(String JavaDoc organizationPartyId, List JavaDoc daysOutstandingPoints, Timestamp JavaDoc asOfDateTime, GenericDelegator delegator)
182         throws GenericEntityException {
183         return getUnpaidInvoicesHelper(organizationPartyId, "PURCHASE_INVOICE", daysOutstandingPoints, asOfDateTime, delegator);
184     }
185         
186     /**
187      * Returns a Map of Integer (not int) days outstanding breakpoints and List of InvoiceWithOutstandingBalance objects for invoices whose days outstanding
188      * is just less than the breakpoint days, but greater than the preceding (smaller) days outstanding breakpoint.
189      *
190      * @param organizationPartyId
191      * @param invoiceTypeId
192      * @param daysOutstandingPoints List of Integer (not int) days outstanding breakpoints, ie: UtilMisc.toList(new Integer(0), new Integer(30), new Integer(60), new Integer(90)
193      * @param asOfDateTime
194      * @param delegator
195      * @return
196      * @throws GenericEntityException
197      */
198     public static Map JavaDoc getUnpaidInvoicesHelper(String JavaDoc organizationPartyId, String JavaDoc invoiceTypeId, List JavaDoc daysOutstandingPoints,
199             Timestamp JavaDoc asOfDateTime, GenericDelegator delegator) throws GenericEntityException {
200         
201         // future asOfDateTime results are correct but unintuitive, so limit time range from anywhere in past to now
202 Timestamp JavaDoc now = UtilDateTime.getDayEnd( UtilDateTime.nowTimestamp() );
203         if (asOfDateTime.after(now)) asOfDateTime = now;
204         
205         // which field is equal to the organizationPartyId? Depends on the invoice type
206 String JavaDoc organizationInvoiceField = null;
207         if (invoiceTypeId.equals("PURCHASE_INVOICE")) {
208             organizationInvoiceField = "partyId";
209         } else {
210             organizationInvoiceField = "partyIdFrom";
211         }
212         
213         // used to select invoices which are not paid by the as of date time but which are created before the as of date
214 // TODO: some kind of invoice status condition as well, to filter out the READY invoice status?
215 EntityConditionList invoiceDateConditions = new EntityConditionList(UtilMisc.toList(
216                 new EntityExpr("paidDate", EntityOperator.GREATER_THAN, asOfDateTime),
217                 new EntityExpr("paidDate", EntityOperator.EQUALS, null)),
218                 EntityOperator.OR);
219         
220         EntityConditionList conditions = new EntityConditionList(UtilMisc.toList(
221                 new EntityExpr("invoiceTypeId", EntityOperator.EQUALS, invoiceTypeId),
222                 new EntityExpr("invoiceDate", EntityOperator.LESS_THAN_EQUAL_TO, asOfDateTime),
223                 new EntityExpr(organizationInvoiceField, EntityOperator.EQUALS, organizationPartyId),
224                 invoiceDateConditions),
225                 EntityOperator.AND);
226
227         // search and sort results on invoiceDate ascending
228 List JavaDoc invoices = delegator.findByCondition("Invoice", conditions, null, UtilMisc.toList("invoiceDate"));
229         
230         // Create outstandingInvoices map, populate with empty buckets, and store the maximum days outstanding of all the break points
231 Map JavaDoc outstandingInvoicesByAge = FastMap.newInstance();
232         int maxDaysOutstanding = 0;
233         for (Iterator JavaDoc it = daysOutstandingPoints.iterator(); it.hasNext(); ) {
234             Integer JavaDoc nextDaysOutstanding = (Integer JavaDoc) it.next();
235             outstandingInvoicesByAge.put(nextDaysOutstanding, new LinkedList JavaDoc());
236             if (nextDaysOutstanding.intValue() > maxDaysOutstanding) {
237                 maxDaysOutstanding = nextDaysOutstanding.intValue();
238             }
239         }
240
241         // loop through invoices and put it into the right date bucket
242 Iterator JavaDoc iter = invoices.iterator();
243         while (iter.hasNext()) {
244             GenericValue invoice = (GenericValue) iter.next();
245             BigDecimal JavaDoc balance = InvoiceWorker.getInvoiceNotApplied(invoice, asOfDateTime);
246             
247             // skip zero balance invoices
248 if (balance.compareTo(ZERO) == 0) continue;
249
250             // Calculate number of days elapsed, rounded as a BigDecimal
251 BigDecimal JavaDoc numberOfDays = new BigDecimal JavaDoc(UtilDateTime.getInterval(invoice.getTimestamp("invoiceDate"), asOfDateTime)).divide(MILLISECONDS_PER_DAY, decimals, rounding);
252
253             // create object of invoice with outstanding balance and whether it's past due or not
254 boolean isPastDue = false;
255             if ((invoice.getTimestamp("dueDate") != null) && (invoice.getTimestamp("dueDate").before(asOfDateTime))) {
256                 isPastDue = true;
257             }
258             InvoiceWithOutstandingBalance invoiceWithBalance = new InvoiceWithOutstandingBalance(invoice, balance, isPastDue);
259
260             // Put this invoice into the List at the first days outstanding (DSO) break point which is greater than the current one, or
261 // the last DSO break point in the list of DSO break points, whichever one comes first
262 boolean foundDaysOutstandingPoint = false;
263             Iterator JavaDoc it = daysOutstandingPoints.iterator();
264             while ((it.hasNext()) && (!foundDaysOutstandingPoint) ) {
265                 Integer JavaDoc daysOutstandingPoint = (Integer JavaDoc) it.next();
266                 if (numberOfDays.compareTo(new BigDecimal JavaDoc(daysOutstandingPoint.intValue())) == -1) { // -1 is less than
267 List JavaDoc invoicesByDaysOutstanding = (LinkedList JavaDoc) outstandingInvoicesByAge.get(daysOutstandingPoint);
268                     invoicesByDaysOutstanding.add(invoiceWithBalance);
269                     foundDaysOutstandingPoint = true;
270                 }
271             }
272             if (!foundDaysOutstandingPoint) {
273                 List JavaDoc invoicesByDaysOutstanding = (LinkedList JavaDoc) outstandingInvoicesByAge.get(new Integer JavaDoc(maxDaysOutstanding));
274                 invoicesByDaysOutstanding.add(invoiceWithBalance);
275             }
276         }
277         return outstandingInvoicesByAge;
278     }
279 }
280
Popular Tags