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.purap.document.dataaccess.impl;
017
018 import java.sql.Date;
019 import java.util.ArrayList;
020 import java.util.Arrays;
021 import java.util.Collection;
022 import java.util.Iterator;
023 import java.util.List;
024
025 import org.apache.log4j.Logger;
026 import org.apache.ojb.broker.query.Criteria;
027 import org.apache.ojb.broker.query.Query;
028 import org.apache.ojb.broker.query.QueryByCriteria;
029 import org.apache.ojb.broker.query.ReportQueryByCriteria;
030 import org.kuali.kfs.module.purap.PurapConstants;
031 import org.kuali.kfs.module.purap.PurapPropertyConstants;
032 import org.kuali.kfs.module.purap.PurapConstants.PaymentRequestStatuses;
033 import org.kuali.kfs.module.purap.batch.service.PurapRunDateService;
034 import org.kuali.kfs.module.purap.document.PaymentRequestDocument;
035 import org.kuali.kfs.module.purap.document.dataaccess.NegativePaymentRequestApprovalLimitDao;
036 import org.kuali.kfs.module.purap.document.dataaccess.PaymentRequestDao;
037 import org.kuali.kfs.module.purap.service.PurapAccountingService;
038 import org.kuali.kfs.module.purap.util.VendorGroupingHelper;
039 import org.kuali.kfs.sys.KFSPropertyConstants;
040 import org.kuali.kfs.sys.context.SpringContext;
041 import org.kuali.rice.kew.exception.WorkflowException;
042 import org.kuali.rice.kns.dao.impl.PlatformAwareDaoBaseOjb;
043 import org.kuali.rice.kns.exception.InfrastructureException;
044 import org.kuali.rice.kns.service.DateTimeService;
045 import org.kuali.rice.kns.service.DocumentService;
046 import org.kuali.rice.kns.service.KualiConfigurationService;
047 import org.kuali.rice.kns.util.KualiDecimal;
048 import org.kuali.rice.kns.util.TransactionalServiceUtils;
049
050 /**
051 * OJB Implementation of PaymentRequestDao.
052 */
053 public class PaymentRequestDaoOjb extends PlatformAwareDaoBaseOjb implements PaymentRequestDao {
054 private static Logger LOG = Logger.getLogger(PaymentRequestDaoOjb.class);
055
056 private NegativePaymentRequestApprovalLimitDao negativePaymentRequestApprovalLimitDao;
057 private DateTimeService dateTimeService;
058 private PurapAccountingService purapAccountingService;
059 private KualiConfigurationService kualiConfigurationService;
060 private PurapRunDateService purapRunDateService;
061
062 public void setNegativePaymentRequestApprovalLimitDao(NegativePaymentRequestApprovalLimitDao negativePaymentRequestApprovalLimitDao) {
063 this.negativePaymentRequestApprovalLimitDao = negativePaymentRequestApprovalLimitDao;
064 }
065
066 public void setDateTimeService(DateTimeService dateTimeService) {
067 this.dateTimeService = dateTimeService;
068 }
069
070 public void setPurapAccountingService(PurapAccountingService purapAccountingService) {
071 this.purapAccountingService = purapAccountingService;
072 }
073
074 public void setKualiConfigurationService(KualiConfigurationService kualiConfigurationService) {
075 this.kualiConfigurationService = kualiConfigurationService;
076 }
077
078 public void setPurapRunDateService(PurapRunDateService purapRunDateService) {
079 this.purapRunDateService = purapRunDateService;
080 }
081
082
083 /**
084 * The special payments query should be this: select * from pur.ap_pmt_rqst_t where pmt_rqst_stat_cd in ('AUTO', 'DPTA') and
085 * prcs_cmp_cd = ? and pmt_extrt_ts is NULL and pmt_hld_ind = 'N' and ( ( ( pmt_spcl_handlg_instrc_ln1_txt is not NULL or
086 * pmt_spcl_handlg_instrc_ln2_txt is not NULL or pmt_spcl_handlg_instrc_ln3_txt is not NULL or pmt_att_ind = 'Y') and trunc
087 * (pmt_rqst_pay_dt) <= trunc (sysdate)) or IMD_PMT_IND = 'Y')})
088 *
089 * @see org.kuali.kfs.module.purap.document.dataaccess.PaymentRequestDao#getPaymentRequestsToExtract(boolean, java.lang.String)
090 */
091 public Collection<PaymentRequestDocument> getPaymentRequestsToExtract(boolean onlySpecialPayments, String chartCode,
092 Date onOrBeforePaymentRequestPayDate) {
093 LOG.debug("getPaymentRequestsToExtract() started");
094
095 Criteria criteria = new Criteria();
096 if (chartCode != null) {
097 criteria.addEqualTo("processingCampusCode", chartCode);
098 }
099 criteria.addIn("statusCode", Arrays.asList(PaymentRequestStatuses.STATUSES_ALLOWED_FOR_EXTRACTION));
100 criteria.addIsNull("extractedTimestamp");
101 criteria.addEqualTo("holdIndicator", Boolean.FALSE);
102
103 if (onlySpecialPayments) {
104 Criteria a = new Criteria();
105
106 Criteria c1 = new Criteria();
107 c1.addNotNull("specialHandlingInstructionLine1Text");
108 Criteria c2 = new Criteria();
109 c2.addNotNull("specialHandlingInstructionLine2Text");
110 Criteria c3 = new Criteria();
111 c3.addNotNull("specialHandlingInstructionLine3Text");
112 Criteria c4 = new Criteria();
113 c4.addEqualTo("paymentAttachmentIndicator", Boolean.TRUE);
114
115 c1.addOrCriteria(c2);
116 c1.addOrCriteria(c3);
117 c1.addOrCriteria(c4);
118
119 a.addAndCriteria(c1);
120 a.addLessOrEqualThan("paymentRequestPayDate", onOrBeforePaymentRequestPayDate);
121
122 Criteria c5 = new Criteria();
123 c5.addEqualTo("immediatePaymentIndicator", Boolean.TRUE);
124 c5.addOrCriteria(a);
125
126 criteria.addAndCriteria(a);
127 }
128 else {
129 Criteria c1 = new Criteria();
130 c1.addLessOrEqualThan("paymentRequestPayDate", onOrBeforePaymentRequestPayDate);
131
132 Criteria c2 = new Criteria();
133 c2.addEqualTo("immediatePaymentIndicator", Boolean.TRUE);
134
135 c1.addOrCriteria(c2);
136 criteria.addAndCriteria(c1);
137 }
138
139 return getPersistenceBrokerTemplate().getCollectionByQuery(new QueryByCriteria(PaymentRequestDocument.class, criteria));
140 }
141
142 /**
143 * @see org.kuali.kfs.module.purap.document.dataaccess.PaymentRequestDao#getImmediatePaymentRequestsToExtract(java.lang.String)
144 */
145 public Collection<PaymentRequestDocument> getImmediatePaymentRequestsToExtract(String chartCode) {
146 LOG.debug("getImmediatePaymentRequestsToExtract() started");
147
148 Criteria criteria = new Criteria();
149 if (chartCode != null) {
150 criteria.addEqualTo("processingCampusCode", chartCode);
151 }
152
153 criteria.addIn("statusCode", Arrays.asList(PaymentRequestStatuses.STATUSES_ALLOWED_FOR_EXTRACTION));
154 criteria.addIsNull("extractedTimestamp");
155 criteria.addEqualTo("immediatePaymentIndicator", Boolean.TRUE);
156
157 return getPersistenceBrokerTemplate().getCollectionByQuery(new QueryByCriteria(PaymentRequestDocument.class, criteria));
158 }
159
160 /**
161 * @see org.kuali.kfs.module.purap.document.dataaccess.PaymentRequestDao#getPaymentRequestsToExtract(java.lang.String, java.lang.Integer,
162 * java.lang.Integer, java.lang.Integer, java.lang.Integer)
163 */
164 public Iterator<PaymentRequestDocument> getPaymentRequestsToExtract(String campusCode, Integer paymentRequestIdentifier, Integer purchaseOrderIdentifier, Integer vendorHeaderGeneratedIdentifier, Integer vendorDetailAssignedIdentifier) {
165 LOG.debug("getPaymentRequestsToExtract() started");
166
167 List statuses = new ArrayList();
168 statuses.add(PurapConstants.PaymentRequestStatuses.AUTO_APPROVED);
169 statuses.add(PurapConstants.PaymentRequestStatuses.DEPARTMENT_APPROVED);
170
171 Criteria criteria = new Criteria();
172 criteria.addEqualTo("processingCampusCode", campusCode);
173 criteria.addIn("statusCode", statuses);
174 criteria.addIsNull("extractedTimestamp");
175 criteria.addEqualTo("holdIndicator", Boolean.FALSE);
176
177 Criteria c1 = new Criteria();
178 c1.addLessOrEqualThan("paymentRequestPayDate", dateTimeService.getCurrentSqlDateMidnight());
179
180 Criteria c2 = new Criteria();
181 c2.addEqualTo("immediatePaymentIndicator", Boolean.TRUE);
182
183 c1.addOrCriteria(c2);
184 criteria.addAndCriteria(c1);
185
186 if (paymentRequestIdentifier != null) {
187 criteria.addEqualTo("purapDocumentIdentifier", paymentRequestIdentifier);
188 }
189 if (purchaseOrderIdentifier != null) {
190 criteria.addEqualTo("purchaseOrderIdentifier", purchaseOrderIdentifier);
191 }
192 criteria.addEqualTo("vendorHeaderGeneratedIdentifier", vendorHeaderGeneratedIdentifier);
193 criteria.addEqualTo("vendorDetailAssignedIdentifier", vendorDetailAssignedIdentifier);
194
195 return getPersistenceBrokerTemplate().getIteratorByQuery(new QueryByCriteria(PaymentRequestDocument.class, criteria));
196 }
197
198 /**
199 *
200 * @see org.kuali.kfs.module.purap.document.dataaccess.PaymentRequestDao#getPaymentRequestsToExtractForVendor(java.lang.String, org.kuali.kfs.module.purap.util.VendorGroupingHelper)
201 */
202 public Collection<PaymentRequestDocument> getPaymentRequestsToExtractForVendor(String campusCode, VendorGroupingHelper vendor, Date onOrBeforePaymentRequestPayDate) {
203 LOG.debug("getPaymentRequestsToExtract() started");
204
205 List statuses = new ArrayList();
206 statuses.add(PurapConstants.PaymentRequestStatuses.AUTO_APPROVED);
207 statuses.add(PurapConstants.PaymentRequestStatuses.DEPARTMENT_APPROVED);
208
209 Criteria criteria = new Criteria();
210 criteria.addEqualTo("processingCampusCode", campusCode);
211 criteria.addIn("statusCode", statuses);
212 criteria.addIsNull("extractedTimestamp");
213 criteria.addEqualTo("holdIndicator", Boolean.FALSE);
214
215 Criteria c1 = new Criteria();
216 c1.addLessOrEqualThan("paymentRequestPayDate", onOrBeforePaymentRequestPayDate);
217
218 Criteria c2 = new Criteria();
219 c2.addEqualTo("immediatePaymentIndicator", Boolean.TRUE);
220
221 c1.addOrCriteria(c2);
222 criteria.addAndCriteria(c1);
223
224 criteria.addEqualTo( "vendorHeaderGeneratedIdentifier", vendor.getVendorHeaderGeneratedIdentifier() );
225 criteria.addEqualTo( "vendorDetailAssignedIdentifier", vendor.getVendorDetailAssignedIdentifier() );
226 criteria.addEqualTo( "vendorCountryCode", vendor.getVendorCountry() );
227 criteria.addLike( "vendorPostalCode", vendor.getVendorPostalCode() + "%" );
228
229 return getPersistenceBrokerTemplate().getCollectionByQuery(new QueryByCriteria(PaymentRequestDocument.class, criteria));
230 }
231
232 /**
233 * @see org.kuali.kfs.module.purap.document.dataaccess.PaymentRequestDao#getEligibleForAutoApproval()
234 */
235 public List<PaymentRequestDocument> getEligibleForAutoApproval() {
236 Date todayAtMidnight = dateTimeService.getCurrentSqlDateMidnight();
237 Criteria criteria = new Criteria();
238 criteria.addLessOrEqualThan(PurapPropertyConstants.PAYMENT_REQUEST_PAY_DATE, todayAtMidnight);
239 criteria.addNotEqualTo("holdIndicator", "Y");
240 criteria.addNotEqualTo("paymentRequestedCancelIndicator", "Y");
241 criteria.addIn("status", Arrays.asList(PurapConstants.PaymentRequestStatuses.PREQ_STATUSES_FOR_AUTO_APPROVE));
242
243 Query query = new QueryByCriteria(PaymentRequestDocument.class, criteria);
244 Iterator<PaymentRequestDocument> documents = (Iterator<PaymentRequestDocument>) getPersistenceBrokerTemplate().getIteratorByQuery(query);
245 ArrayList<String> documentHeaderIds = new ArrayList<String>();
246 while (documents.hasNext()) {
247 PaymentRequestDocument document = (PaymentRequestDocument) documents.next();
248 documentHeaderIds.add(document.getDocumentNumber());
249 }
250
251 if (documentHeaderIds.size() > 0) {
252 try {
253 return SpringContext.getBean(DocumentService.class).getDocumentsByListOfDocumentHeaderIds(PaymentRequestDocument.class, documentHeaderIds);
254 }
255 catch (WorkflowException e) {
256 throw new InfrastructureException("unable to retrieve paymentRequestDocuments", e);
257 }
258 }
259 else {
260 return null;
261 }
262
263 }
264
265 /**
266 * @see org.kuali.kfs.module.purap.document.dataaccess.PaymentRequestDao#getDocumentNumberByPaymentRequestId(java.lang.Integer)
267 */
268 public String getDocumentNumberByPaymentRequestId(Integer id) {
269 Criteria criteria = new Criteria();
270 criteria.addEqualTo(PurapPropertyConstants.PURAP_DOC_ID, id);
271 return getDocumentNumberOfPaymentRequestByCriteria(criteria);
272 }
273
274 /**
275 * @see org.kuali.kfs.module.purap.document.dataaccess.PaymentRequestDao#getDocumentNumbersByPurchaseOrderId(java.lang.Integer)
276 */
277 public List<String> getDocumentNumbersByPurchaseOrderId(Integer poPurApId) {
278 List<String> returnList = new ArrayList<String>();
279 Criteria criteria = new Criteria();
280 criteria.addEqualTo(PurapPropertyConstants.PURCHASE_ORDER_IDENTIFIER, poPurApId);
281 Iterator<Object[]> iter = getDocumentNumbersOfPaymentRequestByCriteria(criteria, false);
282 while (iter.hasNext()) {
283 Object[] cols = (Object[]) iter.next();
284 returnList.add((String) cols[0]);
285 }
286 return returnList;
287 }
288
289 /**
290 * Retrieves a document number for a payment request by user defined criteria.
291 *
292 * @param criteria - list of criteria to use in the retrieve
293 * @return - document number
294 */
295 protected String getDocumentNumberOfPaymentRequestByCriteria(Criteria criteria) {
296 LOG.debug("getDocumentNumberOfPaymentRequestByCriteria() started");
297 Iterator<Object[]> iter = getDocumentNumbersOfPaymentRequestByCriteria(criteria, false);
298 if (iter.hasNext()) {
299 Object[] cols = (Object[]) iter.next();
300 if (iter.hasNext()) {
301 // the iterator should have held only a single doc id of data but it holds 2 or more
302 String errorMsg = "Expected single document number for given criteria but multiple (at least 2) were returned";
303 LOG.error(errorMsg);
304 TransactionalServiceUtils.exhaustIterator(iter);
305 throw new RuntimeException();
306 }
307 // at this part of the code, we know there's no more elements in iterator
308 return (String) cols[0];
309 }
310 return null;
311 }
312
313 /**
314 * Retrieves a document number for a payment request by user defined criteria and sorts the values ascending if orderByAscending
315 * parameter is true, descending otherwise.
316 *
317 * @param criteria - list of criteria to use in the retrieve
318 * @param orderByAscending - boolean to sort results ascending if true, descending otherwise
319 * @return - Iterator of document numbers
320 */
321 protected Iterator<Object[]> getDocumentNumbersOfPaymentRequestByCriteria(Criteria criteria, boolean orderByAscending) {
322 LOG.debug("getDocumentNumberOfPaymentRequestByCriteria() started");
323 ReportQueryByCriteria rqbc = new ReportQueryByCriteria(PaymentRequestDocument.class, criteria);
324 rqbc.setAttributes(new String[] { KFSPropertyConstants.DOCUMENT_NUMBER });
325 if (orderByAscending) {
326 rqbc.addOrderByAscending(KFSPropertyConstants.DOCUMENT_NUMBER);
327 }
328 else {
329 rqbc.addOrderByDescending(KFSPropertyConstants.DOCUMENT_NUMBER);
330 }
331 return getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(rqbc);
332 }
333
334 /**
335 * Retrieves a list of payment requests by user defined criteria.
336 *
337 * @param qbc - query with criteria
338 * @return - list of payment requests
339 */
340 protected List<PaymentRequestDocument> getPaymentRequestsByQueryByCriteria(QueryByCriteria qbc) {
341 LOG.debug("getPaymentRequestsByQueryByCriteria() started");
342 List l = (List) getPersistenceBrokerTemplate().getCollectionByQuery(qbc);
343 return l;
344 }
345
346 /**
347 * Retrieves a list of payment requests with the given vendor id and invoice number.
348 *
349 * @param vendorHeaderGeneratedId - header id of the vendor id
350 * @param vendorDetailAssignedId - detail id of the vendor id
351 * @param invoiceNumber - invoice number as entered by AP
352 * @return - List of payment requests.
353 */
354 public List<PaymentRequestDocument> getActivePaymentRequestsByVendorNumberInvoiceNumber(Integer vendorHeaderGeneratedId, Integer vendorDetailAssignedId, String invoiceNumber) {
355 LOG.debug("getActivePaymentRequestsByVendorNumberInvoiceNumber() started");
356 Criteria criteria = new Criteria();
357 criteria.addEqualTo("vendorHeaderGeneratedIdentifier", vendorHeaderGeneratedId);
358 criteria.addEqualTo("vendorDetailAssignedIdentifier", vendorDetailAssignedId);
359 criteria.addEqualTo("invoiceNumber", invoiceNumber);
360 QueryByCriteria qbc = new QueryByCriteria(PaymentRequestDocument.class, criteria);
361 return this.getPaymentRequestsByQueryByCriteria(qbc);
362 }
363
364 /**
365 * Retrieves a list of payment requests with the given vendor id and invoice number.
366 *
367 * @param vendorHeaderGeneratedId - header id of the vendor id
368 * @param vendorDetailAssignedId - detail id of the vendor id
369 * @param invoiceNumber - invoice number as entered by AP
370 * @return - List of payment requests.
371 */
372 public List<PaymentRequestDocument> getActivePaymentRequestsByVendorNumber(Integer vendorHeaderGeneratedId, Integer vendorDetailAssignedId) {
373 LOG.debug("getActivePaymentRequestsByVendorNumber started");
374 Criteria criteria = new Criteria();
375 criteria.addEqualTo("vendorHeaderGeneratedIdentifier", vendorHeaderGeneratedId);
376 criteria.addEqualTo("vendorDetailAssignedIdentifier", vendorDetailAssignedId);
377 QueryByCriteria qbc = new QueryByCriteria(PaymentRequestDocument.class, criteria);
378 return this.getPaymentRequestsByQueryByCriteria(qbc);
379 }
380
381
382 /**
383 * @see org.kuali.kfs.module.purap.document.dataaccess.PaymentRequestDao#getActivePaymentRequestsByPOIdInvoiceAmountInvoiceDate(java.lang.Integer,
384 * org.kuali.rice.kns.util.KualiDecimal, java.sql.Date)
385 */
386 public List<PaymentRequestDocument> getActivePaymentRequestsByPOIdInvoiceAmountInvoiceDate(Integer poId, KualiDecimal vendorInvoiceAmount, Date invoiceDate) {
387 LOG.debug("getActivePaymentRequestsByVendorNumberInvoiceNumber() started");
388 Criteria criteria = new Criteria();
389 criteria.addEqualTo("purchaseOrderIdentifier", poId);
390 criteria.addEqualTo("vendorInvoiceAmount", vendorInvoiceAmount);
391 criteria.addEqualTo("invoiceDate", invoiceDate);
392 QueryByCriteria qbc = new QueryByCriteria(PaymentRequestDocument.class, criteria);
393 return this.getPaymentRequestsByQueryByCriteria(qbc);
394 }
395
396 public List<String> getActivePaymentRequestDocumentNumbersForPurchaseOrder(Integer purchaseOrderId){
397 LOG.debug("getActivePaymentRequestsByVendorNumberInvoiceNumber() started");
398
399 List<String> returnList = new ArrayList<String>();
400 Criteria criteria = new Criteria();
401
402 criteria.addEqualTo(PurapPropertyConstants.PURCHASE_ORDER_IDENTIFIER, purchaseOrderId);
403 criteria.addIn(PurapPropertyConstants.STATUS_CODE, Arrays.asList(PaymentRequestStatuses.STATUSES_POTENTIALLY_ACTIVE));
404 QueryByCriteria qbc = new QueryByCriteria(PaymentRequestDocument.class, criteria);
405
406 Iterator<Object[]> iter = getDocumentNumbersOfPaymentRequestByCriteria(criteria, false);
407 while (iter.hasNext()) {
408 Object[] cols = (Object[]) iter.next();
409 returnList.add((String) cols[0]);
410 }
411 return returnList;
412 }
413
414 public List<PaymentRequestDocument> getPaymentRequestInReceivingStatus() {
415 Criteria criteria = new Criteria();
416 criteria.addNotEqualTo("holdIndicator", "Y");
417 criteria.addNotEqualTo("paymentRequestedCancelIndicator", "Y");
418 criteria.addEqualTo("statusCode", PurapConstants.PaymentRequestStatuses.AWAITING_RECEIVING_REVIEW);
419
420 Query query = new QueryByCriteria(PaymentRequestDocument.class, criteria);
421 Iterator<PaymentRequestDocument> documents = (Iterator<PaymentRequestDocument>) getPersistenceBrokerTemplate().getIteratorByQuery(query);
422 ArrayList<String> documentHeaderIds = new ArrayList<String>();
423 while (documents.hasNext()) {
424 PaymentRequestDocument document = (PaymentRequestDocument) documents.next();
425 documentHeaderIds.add(document.getDocumentNumber());
426 }
427
428 if (documentHeaderIds.size() > 0) {
429 try {
430 return SpringContext.getBean(DocumentService.class).getDocumentsByListOfDocumentHeaderIds(PaymentRequestDocument.class, documentHeaderIds);
431 }
432 catch (WorkflowException e) {
433 throw new InfrastructureException("unable to retrieve paymentRequestDocuments", e);
434 }
435 }
436 else {
437 return null;
438 }
439
440 }
441 }