View Javadoc
1   /*
2    * Copyright 2007 The Kuali Foundation
3    *
4    * Licensed under the Educational Community License, Version 2.0 (the "License");
5    * you may not use this file except in compliance with the License.
6    * You may obtain a copy of the License at
7    *
8    * http://www.opensource.org/licenses/ecl2.php
9    *
10   * Unless required by applicable law or agreed to in writing, software
11   * distributed under the License is distributed on an "AS IS" BASIS,
12   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13   * See the License for the specific language governing permissions and
14   * limitations under the License.
15   */
16  package org.kuali.ole.module.purap.document.dataaccess.impl;
17  
18  import org.apache.log4j.Logger;
19  import org.apache.ojb.broker.query.Criteria;
20  import org.apache.ojb.broker.query.QueryByCriteria;
21  import org.apache.ojb.broker.query.ReportQueryByCriteria;
22  import org.kuali.ole.module.purap.PurapConstants;
23  import org.kuali.ole.module.purap.PurapPropertyConstants;
24  import org.kuali.ole.module.purap.document.PaymentRequestDocument;
25  import org.kuali.ole.module.purap.document.dataaccess.PaymentRequestDao;
26  import org.kuali.ole.module.purap.util.VendorGroupingHelper;
27  import org.kuali.ole.sys.OLEPropertyConstants;
28  import org.kuali.rice.core.api.util.type.KualiDecimal;
29  import org.kuali.rice.core.framework.persistence.ojb.dao.PlatformAwareDaoBaseOjb;
30  import org.springframework.transaction.annotation.Transactional;
31  
32  import java.sql.Date;
33  import java.util.ArrayList;
34  import java.util.Arrays;
35  import java.util.Collection;
36  import java.util.List;
37  
38  /**
39   * OJB Implementation of PaymentRequestDao.
40   */
41  @Transactional
42  public class PaymentRequestDaoOjb extends PlatformAwareDaoBaseOjb implements PaymentRequestDao {
43      private static Logger LOG = Logger.getLogger(PaymentRequestDaoOjb.class);
44  
45      /**
46       * The special payments query should be this: select * from pur.ap_pmt_rqst_t where pmt_rqst_stat_cd in ('AUTO', 'DPTA') and
47       * 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
48       * 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
49       * (pmt_rqst_pay_dt) <= trunc (sysdate)) or IMD_PMT_IND = 'Y')})
50       *
51       * @see org.kuali.ole.module.purap.document.dataaccess.PaymentRequestDao#getPaymentRequestsToExtract(boolean, java.lang.String)
52       */
53      public List<PaymentRequestDocument> getPaymentRequestsToExtract(boolean onlySpecialPayments, String chartCode, Date onOrBeforePaymentRequestPayDate) {
54          LOG.debug("getPaymentRequestsToExtract() started");
55  
56          Criteria criteria = new Criteria();
57          if (chartCode != null) {
58              criteria.addEqualTo("processingCampusCode", chartCode);
59          }
60          //criteria.addIn(PurapPropertyConstants.STATUS_CODE, Arrays.asList(PaymentRequestStatuses.STATUSES_ALLOWED_FOR_EXTRACTION));
61          criteria.addIsNull("extractedTimestamp");
62          criteria.addEqualTo("holdIndicator", Boolean.FALSE);
63  
64          if (onlySpecialPayments) {
65              Criteria a = new Criteria();
66  
67              Criteria c1 = new Criteria();
68              c1.addNotNull("specialHandlingInstructionLine1Text");
69              Criteria c2 = new Criteria();
70              c2.addNotNull("specialHandlingInstructionLine2Text");
71              Criteria c3 = new Criteria();
72              c3.addNotNull("specialHandlingInstructionLine3Text");
73              Criteria c4 = new Criteria();
74              c4.addEqualTo("paymentAttachmentIndicator", Boolean.TRUE);
75  
76              c1.addOrCriteria(c2);
77              c1.addOrCriteria(c3);
78              c1.addOrCriteria(c4);
79  
80              // Code commented to extract all Payment requests which are approved irrespective of paydate
81             /*
82              a.addAndCriteria(c1);
83              a.addLessOrEqualThan("paymentRequestPayDate", onOrBeforePaymentRequestPayDate);
84  
85              Criteria c5 = new Criteria();
86              c5.addEqualTo("immediatePaymentIndicator", Boolean.TRUE);
87              c5.addOrCriteria(a);
88  
89              criteria.addAndCriteria(a);*/
90              criteria.addAndCriteria(c1);
91          }
92          // Code commented to extract all Payment requests which are approved irrespective of paydate
93          /*else {
94              Criteria c1 = new Criteria();
95              c1.addLessOrEqualThan("paymentRequestPayDate", onOrBeforePaymentRequestPayDate);
96  
97              Criteria c2 = new Criteria();
98              c2.addEqualTo("immediatePaymentIndicator", Boolean.TRUE);
99  
100             c1.addOrCriteria(c2);
101             criteria.addAndCriteria(c1);
102         } */
103         return (List<PaymentRequestDocument>) getPersistenceBrokerTemplate().getCollectionByQuery(new QueryByCriteria(PaymentRequestDocument.class, criteria));
104     }
105 
106     /**
107      * @see org.kuali.ole.module.purap.document.dataaccess.PaymentRequestDao#getImmediatePaymentRequestsToExtract(java.lang.String)
108      */
109     public List<PaymentRequestDocument> getImmediatePaymentRequestsToExtract(String chartCode) {
110         LOG.debug("getImmediatePaymentRequestsToExtract() started");
111 
112         Criteria criteria = new Criteria();
113         if (chartCode != null) {
114             criteria.addEqualTo("processingCampusCode", chartCode);
115         }
116 
117         //criteria.addIn(PurapPropertyConstants.STATUS_CODE, Arrays.asList(PaymentRequestStatuses.STATUSES_ALLOWED_FOR_EXTRACTION));
118         criteria.addIsNull("extractedTimestamp");
119         criteria.addEqualTo("immediatePaymentIndicator", Boolean.TRUE);
120 
121         return (List<PaymentRequestDocument>) getPersistenceBrokerTemplate().getCollectionByQuery(new QueryByCriteria(PaymentRequestDocument.class, criteria));
122     }
123 
124     /**
125      * @see org.kuali.ole.module.purap.document.dataaccess.PaymentRequestDao#getPaymentRequestsToExtract(java.lang.String,
126      *      java.lang.Integer, java.lang.Integer, java.lang.Integer, java.lang.Integer)
127      */
128     @Deprecated
129     public List<PaymentRequestDocument> getPaymentRequestsToExtract(String campusCode, Integer paymentRequestIdentifier, Integer purchaseOrderIdentifier, Integer vendorHeaderGeneratedIdentifier, Integer vendorDetailAssignedIdentifier, Date currentSqlDateMidnight) {
130         LOG.debug("getPaymentRequestsToExtract() started");
131 
132         Criteria criteria = new Criteria();
133         criteria.addEqualTo("processingCampusCode", campusCode);
134         //criteria.addIn(PurapPropertyConstants.STATUS_CODE, statuses);
135         criteria.addIsNull("extractedTimestamp");
136         criteria.addEqualTo("holdIndicator", Boolean.FALSE);
137         // Code commented to extract all Payment requests which are approved irrespective of paydate
138         /* Criteria c1 = new Criteria();
139         c1.addLessOrEqualThan("paymentRequestPayDate", currentSqlDateMidnight);
140 
141         Criteria c2 = new Criteria();
142         c2.addEqualTo("immediatePaymentIndicator", Boolean.TRUE);
143 
144         c1.addOrCriteria(c2);
145         criteria.addAndCriteria(c1);
146         */
147         if (paymentRequestIdentifier != null) {
148             criteria.addEqualTo("purapDocumentIdentifier", paymentRequestIdentifier);
149         }
150         if (purchaseOrderIdentifier != null) {
151             criteria.addEqualTo("purchaseOrderIdentifier", purchaseOrderIdentifier);
152         }
153         criteria.addEqualTo("vendorHeaderGeneratedIdentifier", vendorHeaderGeneratedIdentifier);
154         criteria.addEqualTo("vendorDetailAssignedIdentifier", vendorDetailAssignedIdentifier);
155 
156         return (List<PaymentRequestDocument>) getPersistenceBrokerTemplate().getIteratorByQuery(new QueryByCriteria(PaymentRequestDocument.class, criteria));
157     }
158 
159     /**
160      * @see org.kuali.ole.module.purap.document.dataaccess.PaymentRequestDao#getPaymentRequestsToExtractForVendor(java.lang.String,
161      *      org.kuali.ole.module.purap.util.VendorGroupingHelper)
162      */
163     public Collection<PaymentRequestDocument> getPaymentRequestsToExtractForVendor(String campusCode, VendorGroupingHelper vendor, Date onOrBeforePaymentRequestPayDate) {
164         LOG.debug("getPaymentRequestsToExtract() started");
165 
166         Criteria criteria = new Criteria();
167         criteria.addEqualTo("processingCampusCode", campusCode);
168         //criteria.addIn(PurapPropertyConstants.STATUS_CODE, statuses);
169         criteria.addIsNull("extractedTimestamp");
170         criteria.addEqualTo("holdIndicator", Boolean.FALSE);
171 
172         // Code commented to extract all Payment requests which are approved irrespective of paydate
173        /* Criteria c1 = new Criteria();
174         c1.addLessOrEqualThan("paymentRequestPayDate", onOrBeforePaymentRequestPayDate);
175 
176         Criteria c2 = new Criteria();
177         c2.addEqualTo("immediatePaymentIndicator", Boolean.TRUE);
178 
179         c1.addOrCriteria(c2);
180         criteria.addAndCriteria(c1);
181         */
182         criteria.addEqualTo("vendorHeaderGeneratedIdentifier", vendor.getVendorHeaderGeneratedIdentifier());
183         criteria.addEqualTo("vendorDetailAssignedIdentifier", vendor.getVendorDetailAssignedIdentifier());
184         criteria.addEqualTo("vendorCountryCode", vendor.getVendorCountry());
185         criteria.addLike("vendorPostalCode", vendor.getVendorPostalCode() + "%");
186 
187         return getPersistenceBrokerTemplate().getCollectionByQuery(new QueryByCriteria(PaymentRequestDocument.class, criteria));
188     }
189 
190     /**
191      * @see org.kuali.ole.module.purap.document.dataaccess.PaymentRequestDao#getEligibleForAutoApproval()
192      */
193     public List<String> getEligibleForAutoApproval(Date todayAtMidnight) {
194 
195         Criteria criteria = new Criteria();
196         criteria.addLessOrEqualThan(PurapPropertyConstants.PAYMENT_REQUEST_PAY_DATE, todayAtMidnight);
197         criteria.addNotEqualTo("holdIndicator", "Y");
198         criteria.addNotEqualTo("paymentRequestedCancelIndicator", "Y");
199 
200         List<String> returnList = getDocumentNumbersOfPaymentRequestByCriteria(criteria, false);
201 
202         return returnList;
203     }
204 
205     /**
206      * @see org.kuali.ole.module.purap.document.dataaccess.PaymentRequestDao#getEligibleForAutoApproval()
207      */
208     public List<String> getEligibleForAutoApproval() {
209 
210         Criteria criteria = new Criteria();
211         criteria.addNotEqualTo("holdIndicator", "Y");
212         criteria.addNotEqualTo("paymentRequestedCancelIndicator", "Y");
213 
214         List<String> returnList = getDocumentNumbersOfPaymentRequestByCriteria(criteria, false);
215 
216         return returnList;
217     }
218 
219 
220     /**
221      * @see org.kuali.ole.module.purap.document.dataaccess.PaymentRequestDao#getDocumentNumberByPaymentRequestId(java.lang.Integer)
222      */
223     public String getDocumentNumberByPaymentRequestId(Integer id) {
224         Criteria criteria = new Criteria();
225         criteria.addEqualTo(PurapPropertyConstants.PURAP_DOC_ID, id);
226         return getDocumentNumberOfPaymentRequestByCriteria(criteria);
227     }
228 
229     /**
230      * @see org.kuali.ole.module.purap.document.dataaccess.PaymentRequestDao#getDocumentNumbersByPurchaseOrderId(java.lang.Integer)
231      */
232     public List<String> getDocumentNumbersByPurchaseOrderId(Integer poPurApId) {
233         List<String> returnList = new ArrayList<String>();
234         Criteria criteria = new Criteria();
235         criteria.addEqualTo(PurapPropertyConstants.PURCHASE_ORDER_IDENTIFIER, poPurApId);
236         returnList = getDocumentNumbersOfPaymentRequestByCriteria(criteria, false);
237 
238         return returnList;
239     }
240 
241     /**
242      * Retrieves a document number for a payment request by user defined criteria.
243      *
244      * @param criteria - list of criteria to use in the retrieve
245      * @return - document number
246      */
247     protected String getDocumentNumberOfPaymentRequestByCriteria(Criteria criteria) {
248         LOG.debug("getDocumentNumberOfPaymentRequestByCriteria() started");
249         List<String> returnList = getDocumentNumbersOfPaymentRequestByCriteria(criteria, false);
250 
251         if (returnList.isEmpty()) {
252             return null;
253         }
254 
255         if (returnList.size() > 1) {
256             // the list should have held only a single doc id of data but it holds 2 or more
257             String errorMsg = "Expected single document number for given criteria but multiple (at least 2) were returned";
258             LOG.error(errorMsg);
259             throw new RuntimeException();
260 
261         } else {
262             return (returnList.get(0));
263         }
264     }
265 
266     /**
267      * Retrieves a document number for a payment request by user defined criteria and sorts the values ascending if orderByAscending
268      * parameter is true, descending otherwise.
269      *
270      * @param criteria         - list of criteria to use in the retrieve
271      * @param orderByAscending - boolean to sort results ascending if true, descending otherwise
272      * @return - Iterator of document numbers
273      */
274     protected List<String> getDocumentNumbersOfPaymentRequestByCriteria(Criteria criteria, boolean orderByAscending) {
275         LOG.debug("getDocumentNumberOfPaymentRequestByCriteria() started");
276         ReportQueryByCriteria rqbc = new ReportQueryByCriteria(PaymentRequestDocument.class, criteria);
277         if (orderByAscending) {
278             rqbc.addOrderByAscending(OLEPropertyConstants.DOCUMENT_NUMBER);
279         } else {
280             rqbc.addOrderByDescending(OLEPropertyConstants.DOCUMENT_NUMBER);
281         }
282 
283         List<String> returnList = new ArrayList<String>();
284 
285         List<PaymentRequestDocument> prDocs = (List<PaymentRequestDocument>) getPersistenceBrokerTemplate().getCollectionByQuery(rqbc);
286         for (PaymentRequestDocument prDoc : prDocs) {
287             returnList.add(prDoc.getDocumentNumber());
288         }
289 
290         return returnList;
291     }
292 
293     /**
294      * Retrieves a list of payment requests by user defined criteria.
295      *
296      * @param qbc - query with criteria
297      * @return - list of payment requests
298      */
299     protected List<PaymentRequestDocument> getPaymentRequestsByQueryByCriteria(QueryByCriteria qbc) {
300         LOG.debug("getPaymentRequestsByQueryByCriteria() started");
301         return (List<PaymentRequestDocument>) getPersistenceBrokerTemplate().getCollectionByQuery(qbc);
302     }
303 
304     /**
305      * Retrieves a list of payment requests with the given vendor id and invoice number.
306      *
307      * @param vendorHeaderGeneratedId - header id of the vendor id
308      * @param vendorDetailAssignedId  - detail id of the vendor id
309      * @param invoiceNumber           - invoice number as entered by AP
310      * @return - List of payment requests.
311      */
312     public List<PaymentRequestDocument> getActivePaymentRequestsByVendorNumberInvoiceNumber(Integer vendorHeaderGeneratedId, Integer vendorDetailAssignedId, String invoiceNumber) {
313         LOG.debug("getActivePaymentRequestsByVendorNumberInvoiceNumber() started");
314         Criteria criteria = new Criteria();
315         criteria.addEqualTo("vendorHeaderGeneratedIdentifier", vendorHeaderGeneratedId);
316         criteria.addEqualTo("vendorDetailAssignedIdentifier", vendorDetailAssignedId);
317         criteria.addEqualTo("invoiceNumber", invoiceNumber);
318         QueryByCriteria qbc = new QueryByCriteria(PaymentRequestDocument.class, criteria);
319         return this.getPaymentRequestsByQueryByCriteria(qbc);
320     }
321 
322     /**
323      * Retrieves a list of payment requests with the given vendor id and invoice number.
324      *
325      * @param vendorHeaderGeneratedId - header id of the vendor id
326      * @param vendorDetailAssignedId  - detail id of the vendor id
327      * @param invoiceNumber           - invoice number as entered by AP
328      * @return - List of payment requests.
329      */
330     public List<PaymentRequestDocument> getActivePaymentRequestsByVendorNumber(Integer vendorHeaderGeneratedId, Integer vendorDetailAssignedId) {
331         LOG.debug("getActivePaymentRequestsByVendorNumber started");
332         Criteria criteria = new Criteria();
333         criteria.addEqualTo("vendorHeaderGeneratedIdentifier", vendorHeaderGeneratedId);
334         criteria.addEqualTo("vendorDetailAssignedIdentifier", vendorDetailAssignedId);
335         QueryByCriteria qbc = new QueryByCriteria(PaymentRequestDocument.class, criteria);
336         return this.getPaymentRequestsByQueryByCriteria(qbc);
337     }
338 
339 
340     /**
341      * @see org.kuali.ole.module.purap.document.dataaccess.PaymentRequestDao#getActivePaymentRequestsByPOIdInvoiceAmountInvoiceDate(java.lang.Integer,
342      *      org.kuali.rice.core.api.util.type.KualiDecimal, java.sql.Date)
343      */
344     public List<PaymentRequestDocument> getActivePaymentRequestsByPOIdInvoiceAmountInvoiceDate(Integer poId, KualiDecimal vendorInvoiceAmount, Date invoiceDate) {
345         LOG.debug("getActivePaymentRequestsByVendorNumberInvoiceNumber() started");
346         Criteria criteria = new Criteria();
347         criteria.addEqualTo("purchaseOrderIdentifier", poId);
348         criteria.addEqualTo("vendorInvoiceAmount", vendorInvoiceAmount);
349         criteria.addEqualTo("invoiceDate", invoiceDate);
350         QueryByCriteria qbc = new QueryByCriteria(PaymentRequestDocument.class, criteria);
351         return this.getPaymentRequestsByQueryByCriteria(qbc);
352     }
353 
354     public List<String> getActivePaymentRequestDocumentNumbersForPurchaseOrder(Integer purchaseOrderId) {
355         LOG.debug("getActivePaymentRequestsByVendorNumberInvoiceNumber() started");
356 
357         List<String> returnList = new ArrayList<String>();
358         Criteria criteria = new Criteria();
359 
360         criteria.addEqualTo(PurapPropertyConstants.PURCHASE_ORDER_IDENTIFIER, purchaseOrderId);
361         returnList = getDocumentNumbersOfPaymentRequestByCriteria(criteria, false);
362 
363         return returnList;
364     }
365 
366     public List<String> getPaymentRequestInReceivingStatus() {
367         Criteria criteria = new Criteria();
368         criteria.addNotEqualTo("holdIndicator", "Y");
369         criteria.addNotEqualTo("paymentRequestedCancelIndicator", "Y");
370 
371         List<String> returnList = new ArrayList<String>();
372         returnList = getDocumentNumbersOfPaymentRequestByCriteria(criteria, false);
373 
374         return returnList;
375 
376     }
377 }
378