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.document.dataaccess.impl;
017
018 import java.util.ArrayList;
019 import java.util.Collection;
020 import java.util.Iterator;
021 import java.util.List;
022
023 import org.apache.commons.lang.StringUtils;
024 import org.apache.ojb.broker.query.Criteria;
025 import org.apache.ojb.broker.query.QueryByCriteria;
026 import org.apache.ojb.broker.query.QueryFactory;
027 import org.apache.ojb.broker.query.ReportQueryByCriteria;
028 import org.kuali.kfs.module.ar.ArConstants;
029 import org.kuali.kfs.module.ar.document.CustomerInvoiceDocument;
030 import org.kuali.kfs.module.ar.document.dataaccess.CustomerInvoiceDocumentDao;
031 import org.kuali.kfs.sys.KFSConstants;
032 import org.kuali.rice.kns.dao.impl.PlatformAwareDaoBaseOjb;
033
034 public class CustomerInvoiceDocumentDaoOjb extends PlatformAwareDaoBaseOjb implements CustomerInvoiceDocumentDao {
035
036 private static org.apache.log4j.Logger LOG =
037 org.apache.log4j.Logger.getLogger(CustomerInvoiceDocumentDaoOjb.class);
038
039 public List<String> getPrintableCustomerInvoiceDocumentNumbersFromUserQueue() {
040
041 Criteria criteria = new Criteria();
042 criteria.addEqualTo("printInvoiceIndicator", ArConstants.PrintInvoiceOptions.PRINT_BY_USER);
043 criteria.addIsNull("printDate");
044 criteria.addEqualTo("documentHeader.financialDocumentStatusCode", KFSConstants.DocumentStatusCodes.APPROVED);
045
046 // Why use the OJB reports approach here, rather than a list of CustomerInvoiceDocuments?
047 //
048 // This was done because even if we had the invoice documents, we then need to do a proper document load
049 // via the documentService, which loads up the workflow information as well and properly prepares the document.
050 //
051 // Therefore, at this stage, there's no reason to load entire documents, all we need are document numbers. And with
052 // OJB, this is how you get just a collection of a single column's value out. Given the performance issues associated
053 // with live reporting like this, the attempt was made to minimize the resource usage.
054
055 ReportQueryByCriteria rqbc = QueryFactory.newReportQuery(CustomerInvoiceDocument.class, new String[] { "documentNumber" }, criteria, false);
056
057 Iterator<Object[]> iter = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(rqbc);
058 List<String> invoiceNumbers = new ArrayList<String>();
059 while (iter.hasNext()) {
060 invoiceNumbers.add((String)iter.next()[0]);
061 }
062 return invoiceNumbers;
063 }
064
065 public List<String> getPrintableCustomerInvoiceDocumentNumbersByProcessingChartAndOrg(String chartOfAccountsCode, String organizationCode) {
066 if (StringUtils.isBlank(chartOfAccountsCode)) {
067 throw new IllegalArgumentException("The method was called with a Null or Blank chartOfAccountsCode parameter.");
068 }
069 if (StringUtils.isBlank(organizationCode)) {
070 throw new IllegalArgumentException("The method was called with a Null or Blank organizationCode parameter.");
071 }
072
073 // Why use the OJB reports approach here, rather than a list of CustomerInvoiceDocuments?
074 //
075 // This was done because even if we had the invoice documents, we then need to do a proper document load
076 // via the documentService, which loads up the workflow information as well and properly prepares the document.
077 //
078 // Therefore, at this stage, there's no reason to load entire documents, all we need are document numbers. And with
079 // OJB, this is how you get just a collection of a single column's value out. Given the performance issues associated
080 // with live reporting like this, the attempt was made to minimize the resource usage.
081
082 // select i.fdoc_nbr
083 // from ar_doc_hdr_t h inner join ar_inv_doc_t i
084 // on h.fdoc_nbr = i.fdoc_nbr
085 // where h.prcs_fin_coa_cd = ? and h.prcs_org_cd = ?
086
087 Criteria criteria = new Criteria();
088 criteria.addEqualTo("accountsReceivableDocumentHeader.processingChartOfAccountCode", chartOfAccountsCode);
089 criteria.addEqualTo("accountsReceivableDocumentHeader.processingOrganizationCode", organizationCode);
090 criteria.addEqualTo("printInvoiceIndicator", ArConstants.PrintInvoiceOptions.PRINT_BY_PROCESSING_ORG);
091 criteria.addIsNull("printDate");
092 criteria.addEqualTo("documentHeader.financialDocumentStatusCode", KFSConstants.DocumentStatusCodes.APPROVED);
093
094 ReportQueryByCriteria rqbc = QueryFactory.newReportQuery(CustomerInvoiceDocument.class, new String[] { "documentNumber" }, criteria, false);
095
096 Iterator<Object[]> iter = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(rqbc);
097 List<String> invoiceNumbers = new ArrayList<String>();
098 while (iter.hasNext()) {
099 invoiceNumbers.add((String)iter.next()[0]);
100 }
101 return new ArrayList<String>(invoiceNumbers);
102 }
103
104 public List<String> getPrintableCustomerInvoiceDocumentNumbersByBillingChartAndOrg(String chartOfAccountsCode, String organizationCode) {
105 if (StringUtils.isBlank(chartOfAccountsCode)) {
106 throw new IllegalArgumentException("The method was called with a Null or Blank chartOfAccountsCode parameter.");
107 }
108 if (StringUtils.isBlank(organizationCode)) {
109 throw new IllegalArgumentException("The method was called with a Null or Blank organizationCode parameter.");
110 }
111
112 // Why use the OJB reports approach here, rather than a list of CustomerInvoiceDocuments?
113 //
114 // This was done because even if we had the invoice documents, we then need to do a proper document load
115 // via the documentService, which loads up the workflow information as well and properly prepares the document.
116 //
117 // Therefore, at this stage, there's no reason to load entire documents, all we need are document numbers. And with
118 // OJB, this is how you get just a collection of a single column's value out. Given the performance issues associated
119 // with live reporting like this, the attempt was made to minimize the resource usage.
120
121 Criteria criteria = new Criteria();
122 criteria.addEqualTo("billByChartOfAccountCode", chartOfAccountsCode);
123 criteria.addEqualTo("billedByOrganizationCode", organizationCode);
124 criteria.addEqualTo("printInvoiceIndicator", ArConstants.PrintInvoiceOptions.PRINT_BY_BILLING_ORG);
125 criteria.addIsNull("printDate");
126 criteria.addEqualTo("documentHeader.financialDocumentStatusCode", KFSConstants.DocumentStatusCodes.APPROVED);
127
128 ReportQueryByCriteria rqbc = QueryFactory.newReportQuery(CustomerInvoiceDocument.class, new String[] { "documentNumber" }, criteria, false);
129
130 Iterator<Object[]> iter = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(rqbc);
131 List<String> invoiceNumbers = new ArrayList<String>();
132 while (iter.hasNext()) {
133 invoiceNumbers.add((String)iter.next()[0]);
134 }
135 return new ArrayList<String>(invoiceNumbers);
136 }
137
138 /**
139 * Very similar to above except lacks check for print invoice indicator and print date.
140 *
141 * @see org.kuali.kfs.module.ar.document.dataaccess.CustomerInvoiceDocumentDao#getPrintableCustomerInvoiceDocumentNumbersForBillingStatementByBillingChartAndOrg(java.lang.String, java.lang.String)
142 */
143 public List<String> getPrintableCustomerInvoiceDocumentNumbersForBillingStatementByBillingChartAndOrg(String chartOfAccountsCode, String organizationCode) {
144 if (StringUtils.isBlank(chartOfAccountsCode)) {
145 throw new IllegalArgumentException("The method was called with a Null or Blank chartOfAccountsCode parameter.");
146 }
147 if (StringUtils.isBlank(organizationCode)) {
148 throw new IllegalArgumentException("The method was called with a Null or Blank organizationCode parameter.");
149 }
150
151 // Why use the OJB reports approach here, rather than a list of CustomerInvoiceDocuments?
152 //
153 // This was done because even if we had the invoice documents, we then need to do a proper document load
154 // via the documentService, which loads up the workflow information as well and properly prepares the document.
155 //
156 // Therefore, at this stage, there's no reason to load entire documents, all we need are document numbers. And with
157 // OJB, this is how you get just a collection of a single column's value out. Given the performance issues associated
158 // with live reporting like this, the attempt was made to minimize the resource usage.
159
160 Criteria criteria = new Criteria();
161 criteria.addEqualTo("billByChartOfAccountCode", chartOfAccountsCode);
162 criteria.addEqualTo("billedByOrganizationCode", organizationCode);
163 criteria.addEqualTo("documentHeader.financialDocumentStatusCode", KFSConstants.DocumentStatusCodes.APPROVED);
164
165 ReportQueryByCriteria rqbc = QueryFactory.newReportQuery(CustomerInvoiceDocument.class, new String[] { "documentNumber" }, criteria, false);
166
167 Iterator<Object[]> iter = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(rqbc);
168 List<String> invoiceNumbers = new ArrayList<String>();
169 while (iter.hasNext()) {
170 invoiceNumbers.add((String)iter.next()[0]);
171 }
172 return new ArrayList<String>(invoiceNumbers);
173 }
174
175 public List<String> getCustomerInvoiceDocumentNumbersByProcessingChartAndOrg(String chartOfAccountsCode, String organizationCode) {
176 if (StringUtils.isBlank(chartOfAccountsCode)) {
177 throw new IllegalArgumentException("The method was called with a Null or Blank chartOfAccountsCode parameter.");
178 }
179 if (StringUtils.isBlank(organizationCode)) {
180 throw new IllegalArgumentException("The method was called with a Null or Blank organizationCode parameter.");
181 }
182
183 // Why use the OJB reports approach here, rather than a list of CustomerInvoiceDocuments?
184 //
185 // This was done because even if we had the invoice documents, we then need to do a proper document load
186 // via the documentService, which loads up the workflow information as well and properly prepares the document.
187 //
188 // Therefore, at this stage, there's no reason to load entire documents, all we need are document numbers. And with
189 // OJB, this is how you get just a collection of a single column's value out. Given the performance issues associated
190 // with live reporting like this, the attempt was made to minimize the resource usage.
191
192 // select i.fdoc_nbr
193 // from ar_doc_hdr_t h inner join ar_inv_doc_t i
194 // on h.fdoc_nbr = i.fdoc_nbr
195 // where h.prcs_fin_coa_cd = ? and h.prcs_org_cd = ?
196
197 Criteria criteria = new Criteria();
198 criteria.addEqualTo("accountsReceivableDocumentHeader.processingChartOfAccountCode", chartOfAccountsCode);
199 criteria.addEqualTo("accountsReceivableDocumentHeader.processingOrganizationCode", organizationCode);
200
201 ReportQueryByCriteria rqbc = QueryFactory.newReportQuery(CustomerInvoiceDocument.class, new String[] { "documentNumber" }, criteria, false);
202
203 Iterator<Object[]> iter = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(rqbc);
204 List<String> invoiceNumbers = new ArrayList<String>();
205 while (iter.hasNext()) {
206 invoiceNumbers.add((String)iter.next()[0]);
207 }
208 return new ArrayList<String>(invoiceNumbers);
209 }
210
211 public List<String> getCustomerInvoiceDocumentNumbersByBillingChartAndOrg(String chartOfAccountsCode, String organizationCode) {
212 if (StringUtils.isBlank(chartOfAccountsCode)) {
213 throw new IllegalArgumentException("The method was called with a Null or Blank chartOfAccountsCode parameter.");
214 }
215 if (StringUtils.isBlank(organizationCode)) {
216 throw new IllegalArgumentException("The method was called with a Null or Blank organizationCode parameter.");
217 }
218
219 // Why use the OJB reports approach here, rather than a list of CustomerInvoiceDocuments?
220 //
221 // This was done because even if we had the invoice documents, we then need to do a proper document load
222 // via the documentService, which loads up the workflow information as well and properly prepares the document.
223 //
224 // Therefore, at this stage, there's no reason to load entire documents, all we need are document numbers. And with
225 // OJB, this is how you get just a collection of a single column's value out. Given the performance issues associated
226 // with live reporting like this, the attempt was made to minimize the resource usage.
227
228 Criteria criteria = new Criteria();
229 criteria.addEqualTo("billByChartOfAccountCode", chartOfAccountsCode);
230 criteria.addEqualTo("billedByOrganizationCode", organizationCode);
231
232 ReportQueryByCriteria rqbc = QueryFactory.newReportQuery(CustomerInvoiceDocument.class, new String[] { "documentNumber" }, criteria, false);
233
234 Iterator<Object[]> iter = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(rqbc);
235 List<String> invoiceNumbers = new ArrayList<String>();
236 while (iter.hasNext()) {
237 invoiceNumbers.add((String)iter.next()[0]);
238 }
239 return new ArrayList<String>(invoiceNumbers);
240 }
241
242 public Collection getAllOpen() {
243 Criteria criteria = new Criteria();
244 criteria.addEqualTo("openInvoiceIndicator", true);
245 criteria.addEqualTo("documentHeader.financialDocumentStatusCode", KFSConstants.DocumentStatusCodes.APPROVED);
246
247 QueryByCriteria qbc = QueryFactory.newQuery(CustomerInvoiceDocument.class, criteria);
248
249 Collection customerinvoicedocuments = getPersistenceBrokerTemplate().getCollectionByQuery(qbc);
250 List invoiceList = new ArrayList(customerinvoicedocuments);
251 return invoiceList;
252 }
253
254 public Collection getOpenByCustomerNumber(String customerNumber) {
255 // select i.*
256 // from ar_doc_hdr_t h inner join ar_inv_doc_t i
257 // on h.fdoc_nbr = i.fdoc_nbr
258 // where h.cust_nbr = ?
259
260 // OJB deals with the inner join automatically, because we have it setup with
261 // accountsReceivableDocumentHeader as a ReferenceDescriptor to Invoice.
262 Criteria criteria = new Criteria();
263 criteria.addEqualTo("accountsReceivableDocumentHeader.customerNumber", customerNumber==null?customerNumber:customerNumber.toUpperCase());
264 criteria.addEqualTo("openInvoiceIndicator", "true");
265 criteria.addEqualTo("documentHeader.financialDocumentStatusCode", KFSConstants.DocumentStatusCodes.APPROVED);
266
267
268 QueryByCriteria qbc = QueryFactory.newQuery(CustomerInvoiceDocument.class, criteria);
269
270 Collection customerinvoicedocuments = getPersistenceBrokerTemplate().getCollectionByQuery(qbc);
271 List invoiceList = new ArrayList(customerinvoicedocuments);
272 return invoiceList;
273 }
274
275 public Collection getOpenByCustomerNameByCustomerType(String customerName, String customerTypeCode) {
276 // select i.*
277 // from ar_doc_hdr_t h inner join ar_inv_doc_t i
278 // on h.fdoc_nbr = i.fdoc_nbr
279 // inner join ar_cust_t c
280 // on h.cust_nbr = c.cust_nbr
281 // where c.cust_nm like ? and c.cust_typ_cd = ?
282
283 Criteria criteria = new Criteria();
284 criteria.addLike("accountsReceivableDocumentHeader.customer.customerName", customerName);
285 criteria.addEqualTo("accountsReceivableDocumentHeader.customer.customerTypeCode", customerTypeCode);
286 criteria.addEqualTo("openInvoiceIndicator", "true");
287 criteria.addEqualTo("documentHeader.financialDocumentStatusCode", KFSConstants.DocumentStatusCodes.APPROVED);
288
289
290 QueryByCriteria qbc = QueryFactory.newQuery(CustomerInvoiceDocument.class, criteria);
291
292 Collection customerinvoicedocuments = getPersistenceBrokerTemplate().getCollectionByQuery(qbc);
293 List invoiceList = new ArrayList(customerinvoicedocuments);
294 return invoiceList;
295 }
296
297 public Collection getOpenByCustomerName(String customerName) {
298 // select i.*
299 // from ar_doc_hdr_t h inner join ar_inv_doc_t i
300 // on h.fdoc_nbr = i.fdoc_nbr
301 // inner join ar_cust_t c
302 // on h.cust_nbr = c.cust_nbr
303 // where c.cust_nm like ?
304
305 Criteria criteria = new Criteria();
306 criteria.addLike("accountsReceivableDocumentHeader.customer.customerName", customerName);
307 criteria.addEqualTo("openInvoiceIndicator", "true");
308 criteria.addEqualTo("documentHeader.financialDocumentStatusCode", KFSConstants.DocumentStatusCodes.APPROVED);
309
310
311 QueryByCriteria qbc = QueryFactory.newQuery(CustomerInvoiceDocument.class, criteria);
312
313 Collection customerinvoicedocuments = getPersistenceBrokerTemplate().getCollectionByQuery(qbc);
314 List invoiceList = new ArrayList(customerinvoicedocuments);
315 return invoiceList;
316 }
317
318 public Collection getOpenByCustomerType(String customerTypeCode) {
319 // select i.*
320 // from ar_doc_hdr_t h inner join ar_inv_doc_t i
321 // on h.fdoc_nbr = i.fdoc_nbr
322 // inner join ar_cust_t c
323 // on h.cust_nbr = c.cust_nbr
324 // where c.cust_typ_cd = ?
325
326 // OJB deals with the inner join automatically, because we have it setup with
327 // accountsReceivableDocumentHeader as a ReferenceDescriptor to Invoice, and Customer
328 // as a referencedescriptor to accountsReceivableDocumentHeader.
329 Criteria criteria = new Criteria();
330 criteria.addEqualTo("accountsReceivableDocumentHeader.customer.customerTypeCode", customerTypeCode);
331 criteria.addEqualTo("openInvoiceIndicator", "true");
332 criteria.addEqualTo("documentHeader.financialDocumentStatusCode", KFSConstants.DocumentStatusCodes.APPROVED);
333
334
335 QueryByCriteria qbc = QueryFactory.newQuery(CustomerInvoiceDocument.class, criteria);
336
337 Collection customerinvoicedocuments = getPersistenceBrokerTemplate().getCollectionByQuery(qbc);
338 List invoiceList = new ArrayList(customerinvoicedocuments);
339 return invoiceList;
340 }
341
342 /**
343 * @see org.kuali.kfs.module.ar.document.dataaccess.CustomerInvoiceDocumentDao#getInvoiceByOrganizationInvoiceNumber(java.lang.String)
344 */
345 public CustomerInvoiceDocument getInvoiceByOrganizationInvoiceNumber(String organizationInvoiceNumber) {
346 Criteria criteria = new Criteria();
347 criteria.addEqualTo("organizationInvoiceNumber", organizationInvoiceNumber);
348
349 return (CustomerInvoiceDocument) getPersistenceBrokerTemplate().getObjectByQuery(QueryFactory.newQuery(CustomerInvoiceDocument.class, criteria));
350
351 }
352
353 /**
354 * @see org.kuali.kfs.module.ar.document.dataaccess.CustomerInvoiceDocumentDao#getInvoiceByInvoiceDocumentNumber(java.lang.String)
355 */
356 public CustomerInvoiceDocument getInvoiceByInvoiceDocumentNumber(String documentNumber) {
357 Criteria criteria = new Criteria();
358 criteria.addEqualTo("documentNumber", documentNumber);
359 return (CustomerInvoiceDocument) getPersistenceBrokerTemplate().getObjectByQuery(QueryFactory.newQuery(CustomerInvoiceDocument.class, criteria));
360 }
361
362 }