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