001 /*
002 * Copyright 2011 The Kuali Foundation.
003 *
004 * Licensed under the Educational Community License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 *
008 * http://www.opensource.org/licenses/ecl2.php
009 *
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 */
016 package org.kuali.kfs.module.ar.dataaccess.impl;
017
018 import java.util.Arrays;
019 import java.util.HashMap;
020 import java.util.Iterator;
021
022 import org.apache.ojb.broker.query.Criteria;
023 import org.apache.ojb.broker.query.ReportQueryByCriteria;
024 import org.kuali.kfs.module.ar.businessobject.CustomerInvoiceDetail;
025 import org.kuali.kfs.module.ar.businessobject.InvoicePaidApplied;
026 import org.kuali.kfs.module.ar.dataaccess.CustomerAgingReportDao;
027 import org.kuali.kfs.sys.KFSConstants;
028 import org.kuali.rice.kns.dao.impl.PlatformAwareDaoBaseOjb;
029 import org.kuali.rice.kns.util.KualiDecimal;
030
031 public class CustomerAgingReportDaoOjb extends PlatformAwareDaoBaseOjb implements CustomerAgingReportDao {
032
033 /**
034 *
035 * @see org.kuali.kfs.module.ar.dataaccess.CustomerAgingReportDao#findInvoiceAmountByProcessingChartAndOrg(java.lang.String, java.lang.String, java.sql.Date, java.sql.Date)
036 */
037 public HashMap<String, KualiDecimal> findInvoiceAmountByProcessingChartAndOrg(String chart, String org, java.sql.Date begin, java.sql.Date end) {
038 HashMap<String, KualiDecimal> map = new HashMap<String, KualiDecimal>();
039 Criteria criteria = new Criteria();
040 if (begin != null) {
041 criteria.addGreaterOrEqualThan("customerInvoiceDocument.billingDate", begin);
042 }
043 if (end != null) {
044 criteria.addLessOrEqualThan("customerInvoiceDocument.billingDate", end);
045 }
046 criteria.addEqualTo("customerInvoiceDocument.documentHeader.financialDocumentStatusCode", KFSConstants.DocumentStatusCodes.APPROVED);
047 criteria.addEqualTo("customerInvoiceDocument.accountsReceivableDocumentHeader.processingChartOfAccountCode", chart);
048 criteria.addEqualTo("customerInvoiceDocument.accountsReceivableDocumentHeader.processingOrganizationCode", org);
049 criteria.addEqualTo("customerInvoiceDocument.openInvoiceIndicator", true);
050 ReportQueryByCriteria reportByCriteria = new ReportQueryByCriteria(CustomerInvoiceDetail.class, new String[] { "customerInvoiceDocument.accountsReceivableDocumentHeader.customerNumber", "customerInvoiceDocument.accountsReceivableDocumentHeader.customer.customerName", "sum(amount)" }, criteria);
051 reportByCriteria.addGroupBy("customerInvoiceDocument.accountsReceivableDocumentHeader.customerNumber");
052 reportByCriteria.addGroupBy("customerInvoiceDocument.accountsReceivableDocumentHeader.customer.customerName");
053 Iterator<?> iterator = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(reportByCriteria);
054
055 while (iterator != null && iterator.hasNext()) {
056 Object[] data = (Object[]) iterator.next();
057 map.put((String) data[0] + "-" + data[1], (KualiDecimal) data[2]);
058 }
059 return map;
060 }
061
062 /**
063 *
064 * @see org.kuali.kfs.module.ar.dataaccess.CustomerAgingReportDao#findAppliedAmountByProcessingChartAndOrg(java.lang.String, java.lang.String, java.sql.Date, java.sql.Date)
065 */
066 public HashMap<String, KualiDecimal> findAppliedAmountByProcessingChartAndOrg(String chart, String org, java.sql.Date begin, java.sql.Date end) {
067 HashMap<String, KualiDecimal> map = new HashMap<String, KualiDecimal>();
068 Criteria criteria = new Criteria();
069 if (begin != null) {
070 criteria.addGreaterOrEqualThan("customerInvoiceDocument.billingDate", begin);
071 }
072 if (end != null) {
073 criteria.addLessOrEqualThan("customerInvoiceDocument.billingDate", end);
074 }
075 criteria.addEqualTo("customerInvoiceDocument.documentHeader.financialDocumentStatusCode", KFSConstants.DocumentStatusCodes.APPROVED);
076 criteria.addEqualTo("customerInvoiceDocument.accountsReceivableDocumentHeader.processingChartOfAccountCode", chart);
077 criteria.addEqualTo("customerInvoiceDocument.accountsReceivableDocumentHeader.processingOrganizationCode", org);
078 criteria.addEqualTo("customerInvoiceDocument.openInvoiceIndicator", true);
079 ReportQueryByCriteria reportByCriteria = new ReportQueryByCriteria(InvoicePaidApplied.class, new String[] { "customerInvoiceDocument.accountsReceivableDocumentHeader.customerNumber", "customerInvoiceDocument.accountsReceivableDocumentHeader.customer.customerName", "sum(invoiceItemAppliedAmount)" }, criteria);
080 reportByCriteria.addGroupBy("customerInvoiceDocument.accountsReceivableDocumentHeader.customerNumber");
081 reportByCriteria.addGroupBy("customerInvoiceDocument.accountsReceivableDocumentHeader.customer.customerName");
082 Iterator<?> iterator = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(reportByCriteria);
083
084 while (iterator != null && iterator.hasNext()) {
085 Object[] data = (Object[]) iterator.next();
086 map.put((String) data[0] + "-" + data[1], (KualiDecimal) data[2]);
087 }
088 return map;
089 }
090
091 /**
092 *
093 * @see org.kuali.kfs.module.ar.dataaccess.CustomerAgingReportDao#findDiscountAmountByProcessingChartAndOrg(java.lang.String, java.lang.String, java.sql.Date, java.sql.Date)
094 */
095 public HashMap<String, KualiDecimal> findDiscountAmountByProcessingChartAndOrg(String chart, String org, java.sql.Date begin, java.sql.Date end) {
096
097 HashMap<String, KualiDecimal> map = new HashMap<String, KualiDecimal>();
098 Criteria subCriteria = new Criteria();
099 if (begin != null) {
100 subCriteria.addGreaterOrEqualThan("customerInvoiceDocument.billingDate", begin);
101 }
102 if (end != null) {
103 subCriteria.addLessOrEqualThan("customerInvoiceDocument.billingDate", end);
104 }
105 subCriteria.addEqualTo("customerInvoiceDocument.documentHeader.financialDocumentStatusCode", KFSConstants.DocumentStatusCodes.APPROVED);
106 subCriteria.addEqualTo("customerInvoiceDocument.accountsReceivableDocumentHeader.processingChartOfAccountCode", chart);
107 subCriteria.addEqualTo("customerInvoiceDocument.accountsReceivableDocumentHeader.processingOrganizationCode", org);
108 subCriteria.addEqualTo("customerInvoiceDocument.openInvoiceIndicator", true);
109 subCriteria.addEqualToField("documentNumber", Criteria.PARENT_QUERY_PREFIX + "documentNumber");
110
111 ReportQueryByCriteria subReportQuery = new ReportQueryByCriteria(CustomerInvoiceDetail.class, new String[] { "invoiceItemDiscountLineNumber" }, subCriteria);
112
113 Criteria criteria = new Criteria();
114 criteria.addIn("sequenceNumber", subReportQuery);
115 ReportQueryByCriteria reportByCriteria = new ReportQueryByCriteria(CustomerInvoiceDetail.class, new String[] { "customerInvoiceDocument.accountsReceivableDocumentHeader.customerNumber", "customerInvoiceDocument.accountsReceivableDocumentHeader.customer.customerName", "sum(amount)" }, criteria);
116 reportByCriteria.addGroupBy("customerInvoiceDocument.accountsReceivableDocumentHeader.customerNumber");
117 reportByCriteria.addGroupBy("customerInvoiceDocument.accountsReceivableDocumentHeader.customer.customerName");
118 Iterator<?> iterator = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(reportByCriteria);
119
120 while (iterator != null && iterator.hasNext()) {
121 Object[] data = (Object[]) iterator.next();
122 map.put((String) data[0] + "-" + data[1], (KualiDecimal) data[2]);
123 }
124 return map;
125
126 }
127
128 /**
129 *
130 * @see org.kuali.kfs.module.ar.dataaccess.CustomerAgingReportDao#findInvoiceAmountByBillingChartAndOrg(java.lang.String, java.lang.String, java.sql.Date, java.sql.Date)
131 */
132 public HashMap<String, KualiDecimal> findInvoiceAmountByBillingChartAndOrg(String chart, String org, java.sql.Date begin, java.sql.Date end) {
133 HashMap<String, KualiDecimal> map = new HashMap<String, KualiDecimal>();
134 Criteria criteria = new Criteria();
135 if (begin != null) {
136 criteria.addGreaterOrEqualThan("customerInvoiceDocument.billingDate", begin);
137 }
138 if (end != null) {
139 criteria.addLessOrEqualThan("customerInvoiceDocument.billingDate", end);
140 }
141 criteria.addEqualTo("customerInvoiceDocument.documentHeader.financialDocumentStatusCode", KFSConstants.DocumentStatusCodes.APPROVED);
142 criteria.addEqualTo("customerInvoiceDocument.billByChartOfAccountCode", chart);
143 criteria.addEqualTo("customerInvoiceDocument.billedByOrganizationCode", org);
144 criteria.addEqualTo("customerInvoiceDocument.openInvoiceIndicator", true);
145 ReportQueryByCriteria reportByCriteria = new ReportQueryByCriteria(CustomerInvoiceDetail.class, new String[] { "customerInvoiceDocument.accountsReceivableDocumentHeader.customerNumber", "customerInvoiceDocument.accountsReceivableDocumentHeader.customer.customerName", "sum(amount)" }, criteria);
146 reportByCriteria.addGroupBy("customerInvoiceDocument.accountsReceivableDocumentHeader.customerNumber");
147 reportByCriteria.addGroupBy("customerInvoiceDocument.accountsReceivableDocumentHeader.customer.customerName");
148 Iterator<?> iterator = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(reportByCriteria);
149
150 while (iterator != null && iterator.hasNext()) {
151 Object[] data = (Object[]) iterator.next();
152 map.put((String) data[0] + "-" + data[1], (KualiDecimal) data[2]);
153 }
154 return map;
155 }
156
157 /**
158 *
159 * @see org.kuali.kfs.module.ar.dataaccess.CustomerAgingReportDao#findAppliedAmountByBillingChartAndOrg(java.lang.String, java.lang.String, java.sql.Date, java.sql.Date)
160 */
161 public HashMap<String, KualiDecimal> findAppliedAmountByBillingChartAndOrg(String chart, String org, java.sql.Date begin, java.sql.Date end) {
162 HashMap<String, KualiDecimal> map = new HashMap<String, KualiDecimal>();
163 Criteria criteria = new Criteria();
164 if (begin != null) {
165 criteria.addGreaterOrEqualThan("customerInvoiceDocument.billingDate", begin);
166 }
167 if (end != null) {
168 criteria.addLessOrEqualThan("customerInvoiceDocument.billingDate", end);
169 }
170 criteria.addEqualTo("customerInvoiceDocument.documentHeader.financialDocumentStatusCode", KFSConstants.DocumentStatusCodes.APPROVED);
171 criteria.addEqualTo("customerInvoiceDocument.billByChartOfAccountCode", chart);
172 criteria.addEqualTo("customerInvoiceDocument.billedByOrganizationCode", org);
173 criteria.addEqualTo("customerInvoiceDocument.openInvoiceIndicator", true);
174 ReportQueryByCriteria reportByCriteria = new ReportQueryByCriteria(InvoicePaidApplied.class, new String[] { "customerInvoiceDocument.accountsReceivableDocumentHeader.customerNumber", "customerInvoiceDocument.accountsReceivableDocumentHeader.customer.customerName", "sum(invoiceItemAppliedAmount)" }, criteria);
175 reportByCriteria.addGroupBy("customerInvoiceDocument.accountsReceivableDocumentHeader.customerNumber");
176 reportByCriteria.addGroupBy("customerInvoiceDocument.accountsReceivableDocumentHeader.customer.customerName");
177 Iterator<?> iterator = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(reportByCriteria);
178
179 while (iterator != null && iterator.hasNext()) {
180 Object[] data = (Object[]) iterator.next();
181 map.put((String) data[0] + "-" + data[1], (KualiDecimal) data[2]);
182 }
183 return map;
184 }
185
186 /**
187 *
188 * @see org.kuali.kfs.module.ar.dataaccess.CustomerAgingReportDao#findDiscountAmountByBillingChartAndOrg(java.lang.String, java.lang.String, java.sql.Date, java.sql.Date)
189 */
190 public HashMap<String, KualiDecimal> findDiscountAmountByBillingChartAndOrg(String chart, String org, java.sql.Date begin, java.sql.Date end) {
191
192 HashMap<String, KualiDecimal> map = new HashMap<String, KualiDecimal>();
193 Criteria subCriteria = new Criteria();
194 if (begin != null) {
195 subCriteria.addGreaterOrEqualThan("customerInvoiceDocument.billingDate", begin);
196 }
197 if (end != null) {
198 subCriteria.addLessOrEqualThan("customerInvoiceDocument.billingDate", end);
199 }
200 subCriteria.addEqualTo("customerInvoiceDocument.documentHeader.financialDocumentStatusCode", KFSConstants.DocumentStatusCodes.APPROVED);
201 subCriteria.addEqualTo("customerInvoiceDocument.billByChartOfAccountCode", chart);
202 subCriteria.addEqualTo("customerInvoiceDocument.billedByOrganizationCode", org);
203 subCriteria.addEqualTo("customerInvoiceDocument.openInvoiceIndicator", true);
204 subCriteria.addEqualToField("documentNumber", Criteria.PARENT_QUERY_PREFIX + "documentNumber");
205
206 ReportQueryByCriteria subReportQuery = new ReportQueryByCriteria(CustomerInvoiceDetail.class, new String[] { "invoiceItemDiscountLineNumber" }, subCriteria);
207
208 Criteria criteria = new Criteria();
209 criteria.addIn("sequenceNumber", subReportQuery);
210 ReportQueryByCriteria reportByCriteria = new ReportQueryByCriteria(CustomerInvoiceDetail.class, new String[] { "customerInvoiceDocument.accountsReceivableDocumentHeader.customerNumber", "customerInvoiceDocument.accountsReceivableDocumentHeader.customer.customerName", "sum(amount)" }, criteria);
211 reportByCriteria.addGroupBy("customerInvoiceDocument.accountsReceivableDocumentHeader.customerNumber");
212 reportByCriteria.addGroupBy("customerInvoiceDocument.accountsReceivableDocumentHeader.customer.customerName");
213 Iterator<?> iterator = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(reportByCriteria);
214
215 while (iterator != null && iterator.hasNext()) {
216 Object[] data = (Object[]) iterator.next();
217 map.put((String) data[0] + "-" + data[1], (KualiDecimal) data[2]);
218 }
219 return map;
220
221 }
222
223 /**
224 *
225 * @see org.kuali.kfs.module.ar.dataaccess.CustomerAgingReportDao#findInvoiceAmountByAccount(java.lang.String, java.lang.String, java.sql.Date, java.sql.Date)
226 */
227 public HashMap<String, KualiDecimal> findInvoiceAmountByAccount(String chart, String account, java.sql.Date begin, java.sql.Date end) {
228 HashMap<String, KualiDecimal> map = new HashMap<String, KualiDecimal>();
229 Criteria criteria = new Criteria();
230 if (begin != null) {
231 criteria.addGreaterOrEqualThan("customerInvoiceDocument.billingDate", begin);
232 }
233 if (end != null) {
234 criteria.addLessOrEqualThan("customerInvoiceDocument.billingDate", end);
235 }
236 criteria.addEqualTo("customerInvoiceDocument.documentHeader.financialDocumentStatusCode", KFSConstants.DocumentStatusCodes.APPROVED);
237 criteria.addEqualTo("customerInvoiceDocument.openInvoiceIndicator", true);
238 criteria.addEqualTo("chartOfAccountsCode", chart);
239 criteria.addEqualTo("accountNumber", account);
240 ReportQueryByCriteria reportByCriteria = new ReportQueryByCriteria(CustomerInvoiceDetail.class, new String[] { "customerInvoiceDocument.accountsReceivableDocumentHeader.customerNumber", "customerInvoiceDocument.accountsReceivableDocumentHeader.customer.customerName", "sum(amount)" }, criteria);
241 reportByCriteria.addGroupBy("customerInvoiceDocument.accountsReceivableDocumentHeader.customerNumber");
242 reportByCriteria.addGroupBy("customerInvoiceDocument.accountsReceivableDocumentHeader.customer.customerName");
243 Iterator<?> iterator = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(reportByCriteria);
244
245 while (iterator != null && iterator.hasNext()) {
246 Object[] data = (Object[]) iterator.next();
247 map.put((String) data[0] + "-" + data[1], (KualiDecimal) data[2]);
248 }
249 return map;
250 }
251
252 /**
253 *
254 * @see org.kuali.kfs.module.ar.dataaccess.CustomerAgingReportDao#findAppliedAmountByAccount(java.lang.String, java.lang.String, java.sql.Date, java.sql.Date)
255 */
256 public HashMap<String, KualiDecimal> findAppliedAmountByAccount(String chart, String account, java.sql.Date begin, java.sql.Date end) {
257 HashMap<String, KualiDecimal> map = new HashMap<String, KualiDecimal>();
258 Criteria criteria = new Criteria();
259 if (begin != null) {
260 criteria.addGreaterOrEqualThan("customerInvoiceDocument.billingDate", begin);
261 }
262 if (end != null) {
263 criteria.addLessOrEqualThan("customerInvoiceDocument.billingDate", end);
264 }
265 criteria.addEqualTo("customerInvoiceDocument.documentHeader.financialDocumentStatusCode", KFSConstants.DocumentStatusCodes.APPROVED);
266 criteria.addEqualTo("customerInvoiceDocument.openInvoiceIndicator", true);
267 criteria.addEqualTo("invoiceDetail.chartOfAccountsCode", chart);
268 criteria.addEqualTo("invoiceDetail.accountNumber", account);
269 ReportQueryByCriteria reportByCriteria = new ReportQueryByCriteria(InvoicePaidApplied.class, new String[] { "customerInvoiceDocument.accountsReceivableDocumentHeader.customerNumber", "customerInvoiceDocument.accountsReceivableDocumentHeader.customer.customerName", "sum(invoiceItemAppliedAmount)" }, criteria);
270 reportByCriteria.addGroupBy("customerInvoiceDocument.accountsReceivableDocumentHeader.customerNumber");
271 reportByCriteria.addGroupBy("customerInvoiceDocument.accountsReceivableDocumentHeader.customer.customerName");
272 Iterator<?> iterator = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(reportByCriteria);
273
274 while (iterator != null && iterator.hasNext()) {
275 Object[] data = (Object[]) iterator.next();
276 map.put((String) data[0] + "-" + data[1], (KualiDecimal) data[2]);
277 }
278 return map;
279 }
280
281 /**
282 *
283 * @see org.kuali.kfs.module.ar.dataaccess.CustomerAgingReportDao#findDiscountAmountByAccount(java.lang.String, java.lang.String, java.sql.Date, java.sql.Date)
284 */
285 public HashMap<String, KualiDecimal> findDiscountAmountByAccount(String chart, String account, java.sql.Date begin, java.sql.Date end) {
286
287 HashMap<String, KualiDecimal> map = new HashMap<String, KualiDecimal>();
288 Criteria subCriteria = new Criteria();
289 if (begin != null) {
290 subCriteria.addGreaterOrEqualThan("customerInvoiceDocument.billingDate", begin);
291 }
292 if (end != null) {
293 subCriteria.addLessOrEqualThan("customerInvoiceDocument.billingDate", end);
294 }
295 subCriteria.addEqualTo("customerInvoiceDocument.documentHeader.financialDocumentStatusCode", KFSConstants.DocumentStatusCodes.APPROVED);
296 subCriteria.addEqualTo("customerInvoiceDocument.openInvoiceIndicator", true);
297 subCriteria.addEqualTo("chartOfAccountsCode", chart);
298 subCriteria.addEqualTo("accountNumber", account);
299 subCriteria.addEqualToField("documentNumber", Criteria.PARENT_QUERY_PREFIX + "documentNumber");
300
301 ReportQueryByCriteria subReportQuery = new ReportQueryByCriteria(CustomerInvoiceDetail.class, new String[] { "invoiceItemDiscountLineNumber" }, subCriteria);
302
303 Criteria criteria = new Criteria();
304 criteria.addIn("sequenceNumber", subReportQuery);
305 ReportQueryByCriteria reportByCriteria = new ReportQueryByCriteria(CustomerInvoiceDetail.class, new String[] { "customerInvoiceDocument.accountsReceivableDocumentHeader.customerNumber", "customerInvoiceDocument.accountsReceivableDocumentHeader.customer.customerName", "sum(amount)" }, criteria);
306 reportByCriteria.addGroupBy("customerInvoiceDocument.accountsReceivableDocumentHeader.customerNumber");
307 reportByCriteria.addGroupBy("customerInvoiceDocument.accountsReceivableDocumentHeader.customer.customerName");
308 Iterator<?> iterator = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(reportByCriteria);
309
310 while (iterator != null && iterator.hasNext()) {
311 Object[] data = (Object[]) iterator.next();
312 map.put((String) data[0] + "-" + data[1], (KualiDecimal) data[2]);
313 }
314 return map;
315
316 }
317 }