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.PurapPropertyConstants;
023import org.kuali.ole.module.purap.document.InvoiceDocument;
024import org.kuali.ole.module.purap.document.dataaccess.InvoiceDao;
025import org.kuali.ole.module.purap.util.VendorGroupingHelper;
026import org.kuali.ole.sys.OLEPropertyConstants;
027import org.kuali.rice.core.api.util.type.KualiDecimal;
028import org.kuali.rice.core.framework.persistence.ojb.dao.PlatformAwareDaoBaseOjb;
029import org.springframework.transaction.annotation.Transactional;
030
031import java.sql.Date;
032import java.util.ArrayList;
033import java.util.Collection;
034import java.util.List;
035
036/**
037 * OJB Implementation of InvoiceDao.
038 */
039@Transactional
040public class InvoiceDaoOjb extends PlatformAwareDaoBaseOjb implements InvoiceDao {
041    private static Logger LOG = Logger.getLogger(InvoiceDaoOjb.class);
042
043    /**
044     * The special payments query should be this: select * from pur.ap_pmt_rqst_t where pmt_rqst_stat_cd in ('AUTO', 'DPTA') and
045     * 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
046     * 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
047     * (pmt_rqst_pay_dt) <= trunc (sysdate)) or IMD_PMT_IND = 'Y')})
048     *
049     * @see org.kuali.ole.module.purap.document.dataaccess.InvoiceDao#getInvoicesToExtract(boolean, String, Date)
050     */
051    public List<InvoiceDocument> getInvoicesToExtract(boolean onlySpecialPayments, String chartCode, Date onOrBeforeInvoicePayDate) {
052        LOG.debug("getInvoicesToExtract() started");
053
054        Criteria criteria = new Criteria();
055        if (chartCode != null) {
056            criteria.addEqualTo("processingCampusCode", chartCode);
057        }
058        //criteria.addIn(PurapPropertyConstants.STATUS_CODE, Arrays.asList(InvoiceStatuses.STATUSES_ALLOWED_FOR_EXTRACTION));
059        criteria.addIsNull("extractedTimestamp");
060        criteria.addEqualTo("holdIndicator", Boolean.FALSE);
061
062        if (onlySpecialPayments) {
063            Criteria a = new Criteria();
064
065            Criteria c1 = new Criteria();
066            c1.addNotNull("specialHandlingInstructionLine1Text");
067            Criteria c2 = new Criteria();
068            c2.addNotNull("specialHandlingInstructionLine2Text");
069            Criteria c3 = new Criteria();
070            c3.addNotNull("specialHandlingInstructionLine3Text");
071            Criteria c4 = new Criteria();
072            c4.addEqualTo("paymentAttachmentIndicator", Boolean.TRUE);
073
074            c1.addOrCriteria(c2);
075            c1.addOrCriteria(c3);
076            c1.addOrCriteria(c4);
077
078            a.addAndCriteria(c1);
079            a.addLessOrEqualThan("invoicePayDate", onOrBeforeInvoicePayDate);
080
081            Criteria c5 = new Criteria();
082            c5.addEqualTo("immediatePaymentIndicator", Boolean.TRUE);
083            c5.addOrCriteria(a);
084
085            criteria.addAndCriteria(a);
086        } else {
087            Criteria c1 = new Criteria();
088            c1.addLessOrEqualThan("invoicePayDate", onOrBeforeInvoicePayDate);
089
090            Criteria c2 = new Criteria();
091            c2.addEqualTo("immediatePaymentIndicator", Boolean.TRUE);
092
093            c1.addOrCriteria(c2);
094            criteria.addAndCriteria(c1);
095        }
096
097        return (List<InvoiceDocument>) getPersistenceBrokerTemplate().getCollectionByQuery(new QueryByCriteria(InvoiceDocument.class, criteria));
098    }
099
100    /**
101     * @see org.kuali.ole.module.purap.document.dataaccess.InvoiceDao#getImmediateInvoicesToExtract(String)
102     */
103    public List<InvoiceDocument> getImmediateInvoicesToExtract(String chartCode) {
104        LOG.debug("getImmediateInvoicesToExtract() 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(InvoiceStatuses.STATUSES_ALLOWED_FOR_EXTRACTION));
112        criteria.addIsNull("extractedTimestamp");
113        criteria.addEqualTo("immediatePaymentIndicator", Boolean.TRUE);
114
115        return (List<InvoiceDocument>) getPersistenceBrokerTemplate().getCollectionByQuery(new QueryByCriteria(InvoiceDocument.class, criteria));
116    }
117
118    /**
119     * @see org.kuali.ole.module.purap.document.dataaccess.InvoiceDao#getInvoicesToExtract(String, Integer, Integer, Integer, Integer, Date)
120     */
121    @Deprecated
122    public List<InvoiceDocument> getInvoicesToExtract(String campusCode, Integer invoiceIdentifier, Integer purchaseOrderIdentifier, Integer vendorHeaderGeneratedIdentifier, Integer vendorDetailAssignedIdentifier, Date currentSqlDateMidnight) {
123        LOG.debug("getInvoicesToExtract() started");
124
125        Criteria criteria = new Criteria();
126        criteria.addEqualTo("processingCampusCode", campusCode);
127        //criteria.addIn(PurapPropertyConstants.STATUS_CODE, statuses);
128        criteria.addIsNull("extractedTimestamp");
129        criteria.addEqualTo("holdIndicator", Boolean.FALSE);
130
131        Criteria c1 = new Criteria();
132        c1.addLessOrEqualThan("invoicePayDate", currentSqlDateMidnight);
133
134        Criteria c2 = new Criteria();
135        c2.addEqualTo("immediatePaymentIndicator", Boolean.TRUE);
136
137        c1.addOrCriteria(c2);
138        criteria.addAndCriteria(c1);
139
140        if (invoiceIdentifier != null) {
141            criteria.addEqualTo("purapDocumentIdentifier", invoiceIdentifier);
142        }
143        if (purchaseOrderIdentifier != null) {
144            criteria.addEqualTo("purchaseOrderIdentifier", purchaseOrderIdentifier);
145        }
146        criteria.addEqualTo("vendorHeaderGeneratedIdentifier", vendorHeaderGeneratedIdentifier);
147        criteria.addEqualTo("vendorDetailAssignedIdentifier", vendorDetailAssignedIdentifier);
148
149        return (List<InvoiceDocument>) getPersistenceBrokerTemplate().getIteratorByQuery(new QueryByCriteria(InvoiceDocument.class, criteria));
150    }
151
152    /**
153     * @see org.kuali.ole.module.purap.document.dataaccess.InvoiceDao#getInvoicesToExtractForVendor(String,
154     *      org.kuali.ole.module.purap.util.VendorGroupingHelper, Date)
155     */
156    public Collection<InvoiceDocument> getInvoicesToExtractForVendor(String campusCode, VendorGroupingHelper vendor, Date onOrBeforeInvoicePayDate) {
157        LOG.debug("getInvoicesToExtract() started");
158
159        Criteria criteria = new Criteria();
160        criteria.addEqualTo("processingCampusCode", campusCode);
161        //criteria.addIn(PurapPropertyConstants.STATUS_CODE, statuses);
162        criteria.addIsNull("extractedTimestamp");
163        criteria.addEqualTo("holdIndicator", Boolean.FALSE);
164
165        Criteria c1 = new Criteria();
166        c1.addLessOrEqualThan("invoicePayDate", onOrBeforeInvoicePayDate);
167
168        Criteria c2 = new Criteria();
169        c2.addEqualTo("immediatePaymentIndicator", Boolean.TRUE);
170
171        c1.addOrCriteria(c2);
172        criteria.addAndCriteria(c1);
173
174        criteria.addEqualTo("vendorHeaderGeneratedIdentifier", vendor.getVendorHeaderGeneratedIdentifier());
175        criteria.addEqualTo("vendorDetailAssignedIdentifier", vendor.getVendorDetailAssignedIdentifier());
176        criteria.addEqualTo("vendorCountryCode", vendor.getVendorCountry());
177        criteria.addLike("vendorPostalCode", vendor.getVendorPostalCode() + "%");
178
179        return getPersistenceBrokerTemplate().getCollectionByQuery(new QueryByCriteria(InvoiceDocument.class, criteria));
180    }
181
182    /**
183     * @see org.kuali.ole.module.purap.document.dataaccess.InvoiceDao#getEligibleForAutoApproval(Date)
184     */
185    public List<String> getEligibleForAutoApproval(Date todayAtMidnight) {
186
187        Criteria criteria = new Criteria();
188        criteria.addLessOrEqualThan(PurapPropertyConstants.PAYMENT_REQUEST_PAY_DATE, todayAtMidnight);
189        criteria.addNotEqualTo("holdIndicator", "Y");
190        criteria.addNotEqualTo("invoiceCancelIndicator", "Y");
191
192        List<String> returnList = getDocumentNumbersOfInvoiceByCriteria(criteria, false);
193
194        return returnList;
195    }
196
197
198    /**
199     * @see org.kuali.ole.module.purap.document.dataaccess.InvoiceDao#getDocumentNumberByInvoiceId(Integer)
200     */
201    public String getDocumentNumberByInvoiceId(Integer id) {
202        Criteria criteria = new Criteria();
203        criteria.addEqualTo(PurapPropertyConstants.PURAP_DOC_ID, id);
204        return getDocumentNumberOfInvoiceByCriteria(criteria);
205    }
206
207    /**
208     * @see org.kuali.ole.module.purap.document.dataaccess.InvoiceDao#getDocumentNumbersByPurchaseOrderId(Integer)
209     */
210    public List<String> getDocumentNumbersByPurchaseOrderId(Integer poPurApId) {
211        List<String> returnList = new ArrayList<String>();
212        Criteria criteria = new Criteria();
213        criteria.addEqualTo(PurapPropertyConstants.PURCHASE_ORDER_IDENTIFIER, poPurApId);
214        returnList = getDocumentNumbersOfInvoiceByCriteria(criteria, false);
215
216        return returnList;
217    }
218
219    /**
220     * Retrieves a document number for a invoice by user defined criteria.
221     *
222     * @param criteria - list of criteria to use in the retrieve
223     * @return - document number
224     */
225    protected String getDocumentNumberOfInvoiceByCriteria(Criteria criteria) {
226        LOG.debug("getDocumentNumberOfInvoiceByCriteria() started");
227        List<String> returnList = getDocumentNumbersOfInvoiceByCriteria(criteria, false);
228
229        if (returnList.isEmpty()) {
230            return null;
231        }
232
233        if (returnList.size() > 1) {
234            // the list should have held only a single doc id of data but it holds 2 or more
235            String errorMsg = "Expected single document number for given criteria but multiple (at least 2) were returned";
236            LOG.error(errorMsg);
237            throw new RuntimeException();
238
239        } else {
240            return (returnList.get(0));
241        }
242    }
243
244    /**
245     * Retrieves a document number for a invoice by user defined criteria and sorts the values ascending if orderByAscending
246     * parameter is true, descending otherwise.
247     *
248     * @param criteria         - list of criteria to use in the retrieve
249     * @param orderByAscending - boolean to sort results ascending if true, descending otherwise
250     * @return - Iterator of document numbers
251     */
252    protected List<String> getDocumentNumbersOfInvoiceByCriteria(Criteria criteria, boolean orderByAscending) {
253        LOG.debug("getDocumentNumberOfInvoiceByCriteria() started");
254        ReportQueryByCriteria rqbc = new ReportQueryByCriteria(InvoiceDocument.class, criteria);
255        if (orderByAscending) {
256            rqbc.addOrderByAscending(OLEPropertyConstants.DOCUMENT_NUMBER);
257        } else {
258            rqbc.addOrderByDescending(OLEPropertyConstants.DOCUMENT_NUMBER);
259        }
260
261        List<String> returnList = new ArrayList<String>();
262
263        List<InvoiceDocument> prDocs = (List<InvoiceDocument>) getPersistenceBrokerTemplate().getCollectionByQuery(rqbc);
264        for (InvoiceDocument prDoc : prDocs) {
265            returnList.add(prDoc.getDocumentNumber());
266        }
267
268        return returnList;
269    }
270
271    /**
272     * Retrieves a list of invoices by user defined criteria.
273     *
274     * @param qbc - query with criteria
275     * @return - list of invoices
276     */
277    protected List<InvoiceDocument> getInvoicesByQueryByCriteria(QueryByCriteria qbc) {
278        LOG.debug("getInvoicesByQueryByCriteria() started");
279        return (List<InvoiceDocument>) getPersistenceBrokerTemplate().getCollectionByQuery(qbc);
280    }
281
282    /**
283     * Retrieves a list of invoices with the given vendor id and invoice number.
284     *
285     * @param vendorHeaderGeneratedId - header id of the vendor id
286     * @param vendorDetailAssignedId  - detail id of the vendor id
287     * @param invoiceNumber           - invoice number as entered by AP
288     * @return - List of invoices.
289     */
290    public List<InvoiceDocument> getActiveInvoicesByVendorNumberInvoiceNumber(Integer vendorHeaderGeneratedId, Integer vendorDetailAssignedId, String invoiceNumber) {
291        LOG.debug("getActiveInvoicesByVendorNumberInvoiceNumber() started");
292        Criteria criteria = new Criteria();
293        criteria.addEqualTo("vendorHeaderGeneratedIdentifier", vendorHeaderGeneratedId);
294        criteria.addEqualTo("vendorDetailAssignedIdentifier", vendorDetailAssignedId);
295        criteria.addEqualTo("invoiceNumber", invoiceNumber);
296        QueryByCriteria qbc = new QueryByCriteria(InvoiceDocument.class, criteria);
297        return this.getInvoicesByQueryByCriteria(qbc);
298    }
299
300    /**
301     * Retrieves a list of invoices with the given vendor id and invoice number.
302     *
303     * @param vendorHeaderGeneratedId - header id of the vendor id
304     * @param vendorDetailAssignedId  - detail id of the vendor id
305     * @return - List of invoices.
306     */
307    public List<InvoiceDocument> getActiveInvoicesByVendorNumber(Integer vendorHeaderGeneratedId, Integer vendorDetailAssignedId) {
308        LOG.debug("getActiveInvoicesByVendorNumber started");
309        Criteria criteria = new Criteria();
310        criteria.addEqualTo("vendorHeaderGeneratedIdentifier", vendorHeaderGeneratedId);
311        criteria.addEqualTo("vendorDetailAssignedIdentifier", vendorDetailAssignedId);
312        QueryByCriteria qbc = new QueryByCriteria(InvoiceDocument.class, criteria);
313        return this.getInvoicesByQueryByCriteria(qbc);
314    }
315
316
317    /**
318     * @see org.kuali.ole.module.purap.document.dataaccess.InvoiceDao#getActiveInvoicesByPOIdInvoiceAmountInvoiceDate(Integer,
319     *      org.kuali.rice.core.api.util.type.KualiDecimal, java.sql.Date)
320     */
321    public List<InvoiceDocument> getActiveInvoicesByPOIdInvoiceAmountInvoiceDate(Integer poId, KualiDecimal vendorInvoiceAmount, Date invoiceDate) {
322        LOG.debug("getActiveInvoicesByVendorNumberInvoiceNumber() started");
323        Criteria criteria = new Criteria();
324        criteria.addEqualTo("purchaseOrderIdentifier", poId);
325        criteria.addEqualTo("vendorInvoiceAmount", vendorInvoiceAmount);
326        criteria.addEqualTo("invoiceDate", invoiceDate);
327        QueryByCriteria qbc = new QueryByCriteria(InvoiceDocument.class, criteria);
328        return this.getInvoicesByQueryByCriteria(qbc);
329    }
330
331    public List<String> getActiveInvoiceDocumentNumbersForPurchaseOrder(Integer purchaseOrderId) {
332        LOG.debug("getActiveInvoicesByVendorNumberInvoiceNumber() started");
333
334        List<String> returnList = new ArrayList<String>();
335        Criteria criteria = new Criteria();
336
337        criteria.addEqualTo(PurapPropertyConstants.PURCHASE_ORDER_IDENTIFIER, purchaseOrderId);
338        returnList = getDocumentNumbersOfInvoiceByCriteria(criteria, false);
339
340        return returnList;
341    }
342
343    public List<String> getInvoiceInReceivingStatus() {
344        Criteria criteria = new Criteria();
345        criteria.addNotEqualTo("holdIndicator", "Y");
346        criteria.addNotEqualTo("invoiceCancelIndicator", "Y");
347
348        List<String> returnList = new ArrayList<String>();
349        returnList = getDocumentNumbersOfInvoiceByCriteria(criteria, false);
350
351        return returnList;
352
353    }
354}
355