Source code

001/*
002 * Copyright 2007 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 */
016package org.kuali.ole.module.purap.document.dataaccess.impl;
017
018import org.apache.log4j.Logger;
019import org.apache.ojb.broker.query.Criteria;
020import org.apache.ojb.broker.query.QueryByCriteria;
021import org.apache.ojb.broker.query.ReportQueryByCriteria;
022import org.kuali.ole.module.purap.PurapConstants;
023import org.kuali.ole.module.purap.PurapPropertyConstants;
024import org.kuali.ole.module.purap.document.PaymentRequestDocument;
025import org.kuali.ole.module.purap.document.dataaccess.PaymentRequestDao;
026import org.kuali.ole.module.purap.util.VendorGroupingHelper;
027import org.kuali.ole.sys.OLEPropertyConstants;
028import org.kuali.rice.core.api.util.type.KualiDecimal;
029import org.kuali.rice.core.framework.persistence.ojb.dao.PlatformAwareDaoBaseOjb;
030import org.springframework.transaction.annotation.Transactional;
031
032import java.sql.Date;
033import java.util.ArrayList;
034import java.util.Arrays;
035import java.util.Collection;
036import java.util.List;
037
038/**
039 * OJB Implementation of PaymentRequestDao.
040 */
041@Transactional
042public class PaymentRequestDaoOjb extends PlatformAwareDaoBaseOjb implements PaymentRequestDao {
043    private static Logger LOG = Logger.getLogger(PaymentRequestDaoOjb.class);
044
045    /**
046     * The special payments query should be this: select * from pur.ap_pmt_rqst_t where pmt_rqst_stat_cd in ('AUTO', 'DPTA') and
047     * 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
048     * 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
049     * (pmt_rqst_pay_dt) <= trunc (sysdate)) or IMD_PMT_IND = 'Y')})
050     *
051     * @see org.kuali.ole.module.purap.document.dataaccess.PaymentRequestDao#getPaymentRequestsToExtract(boolean, java.lang.String)
052     */
053    public List<PaymentRequestDocument> getPaymentRequestsToExtract(boolean onlySpecialPayments, String chartCode, Date onOrBeforePaymentRequestPayDate) {
054        LOG.debug("getPaymentRequestsToExtract() started");
055
056        Criteria criteria = new Criteria();
057        if (chartCode != null) {
058            criteria.addEqualTo("processingCampusCode", chartCode);
059        }
060        //criteria.addIn(PurapPropertyConstants.STATUS_CODE, Arrays.asList(PaymentRequestStatuses.STATUSES_ALLOWED_FOR_EXTRACTION));
061        criteria.addIsNull("extractedTimestamp");
062        criteria.addEqualTo("holdIndicator", Boolean.FALSE);
063
064        if (onlySpecialPayments) {
065            Criteria a = new Criteria();
066
067            Criteria c1 = new Criteria();
068            c1.addNotNull("specialHandlingInstructionLine1Text");
069            Criteria c2 = new Criteria();
070            c2.addNotNull("specialHandlingInstructionLine2Text");
071            Criteria c3 = new Criteria();
072            c3.addNotNull("specialHandlingInstructionLine3Text");
073            Criteria c4 = new Criteria();
074            c4.addEqualTo("paymentAttachmentIndicator", Boolean.TRUE);
075
076            c1.addOrCriteria(c2);
077            c1.addOrCriteria(c3);
078            c1.addOrCriteria(c4);
079
080           // Code commented to extract all Payment requests which are approved irrespective of paydate
081           /*
082            a.addAndCriteria(c1);
083            a.addLessOrEqualThan("paymentRequestPayDate", onOrBeforePaymentRequestPayDate);
084
085            Criteria c5 = new Criteria();
086            c5.addEqualTo("immediatePaymentIndicator", Boolean.TRUE);
087            c5.addOrCriteria(a);
088
089            criteria.addAndCriteria(a);*/
090            criteria.addAndCriteria(c1);
091        }
092        // Code commented to extract all Payment requests which are approved irrespective of paydate
093        /*else {
094            Criteria c1 = new Criteria();
095            c1.addLessOrEqualThan("paymentRequestPayDate", onOrBeforePaymentRequestPayDate);
096
097            Criteria c2 = new Criteria();
098            c2.addEqualTo("immediatePaymentIndicator", Boolean.TRUE);
099
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