Source code

001/*
002 * The Kuali Financial System, a comprehensive financial management system for higher education.
003 * 
004 * Copyright 2005-2014 The Kuali Foundation
005 * 
006 * This program is free software: you can redistribute it and/or modify
007 * it under the terms of the GNU Affero General Public License as
008 * published by the Free Software Foundation, either version 3 of the
009 * License, or (at your option) any later version.
010 * 
011 * This program is distributed in the hope that it will be useful,
012 * but WITHOUT ANY WARRANTY; without even the implied warranty of
013 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
014 * GNU Affero General Public License for more details.
015 * 
016 * You should have received a copy of the GNU Affero General Public License
017 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
018 */
019package org.kuali.kfs.module.tem.dataaccess.impl;
020
021import static org.kuali.kfs.module.tem.TemPropertyConstants.TRAVEL_DOCUMENT_IDENTIFIER;
022
023import java.sql.Timestamp;
024import java.util.ArrayList;
025import java.util.Arrays;
026import java.util.Collection;
027import java.util.Iterator;
028import java.util.List;
029import java.util.Set;
030
031import org.apache.log4j.Logger;
032import org.apache.ojb.broker.query.Criteria;
033import org.apache.ojb.broker.query.Query;
034import org.apache.ojb.broker.query.QueryByCriteria;
035import org.apache.ojb.broker.query.QueryFactory;
036import org.apache.ojb.broker.query.ReportQueryByCriteria;
037import org.kuali.kfs.module.tem.TemConstants;
038import org.kuali.kfs.module.tem.TemPropertyConstants;
039import org.kuali.kfs.module.tem.businessobject.ImportedExpense;
040import org.kuali.kfs.module.tem.businessobject.PerDiem;
041import org.kuali.kfs.module.tem.businessobject.TravelAdvance;
042import org.kuali.kfs.module.tem.dataaccess.TravelDocumentDao;
043import org.kuali.kfs.module.tem.document.TEMReimbursementDocument;
044import org.kuali.kfs.module.tem.document.TravelAuthorizationDocument;
045import org.kuali.kfs.module.tem.document.TravelDocument;
046import org.kuali.kfs.module.tem.document.TravelEntertainmentDocument;
047import org.kuali.kfs.module.tem.document.TravelReimbursementDocument;
048import org.kuali.kfs.module.tem.document.TravelRelocationDocument;
049import org.kuali.kfs.sys.KFSConstants;
050import org.kuali.kfs.sys.KFSPropertyConstants;
051import org.kuali.kfs.sys.KFSConstants.DocumentStatusCodes;
052import org.kuali.kfs.sys.util.TransactionalServiceUtils;
053import org.kuali.rice.core.framework.persistence.ojb.dao.PlatformAwareDaoBaseOjb;
054import org.kuali.rice.krad.util.ObjectUtils;
055import org.kuali.rice.krad.util.OjbCollectionAware;
056
057/**
058 * This class is the OJB implementation of the DocumentDao interface.
059 */
060public class TravelDocumentDaoOjb extends PlatformAwareDaoBaseOjb implements TravelDocumentDao, OjbCollectionAware {
061
062    public static Logger LOG = Logger.getLogger(TravelDocumentDaoOjb.class);
063
064    @Override
065    public List<TravelDocument> findDocuments(final Class<?> travelDocumentClass, final String travelDocumentNumber) {
066        final Criteria c = new Criteria();
067        c.addEqualTo(TRAVEL_DOCUMENT_IDENTIFIER, travelDocumentNumber);
068
069        LOG.debug("Creating query for type "+ travelDocumentClass+ " using criteria "+ c);
070
071        final List<TravelDocument> retval = new ArrayList<TravelDocument>();
072
073        Collection<? extends TravelDocument> documents = getPersistenceBrokerTemplate().getCollectionByQuery(new QueryByCriteria(travelDocumentClass, c));
074
075        for (Iterator it = documents.iterator(); it.hasNext();) {
076            TravelDocument document = (TravelDocument) it.next();
077            retval.add(document);
078        }
079
080        return retval;
081    }
082
083        @Override
084    public List<String> findDocumentNumbers(final Class<?> travelDocumentClass, final String travelDocumentNumber) {
085        final Criteria c = new Criteria();
086        c.addEqualTo(TRAVEL_DOCUMENT_IDENTIFIER, travelDocumentNumber);
087
088        LOG.debug("Creating query for type "+ travelDocumentClass+ " using criteria "+ c);
089
090        final List<String> retval = new ArrayList<String>();
091
092        Collection<? extends TravelDocument> documents = getPersistenceBrokerTemplate().getCollectionByQuery(new QueryByCriteria(travelDocumentClass, c));
093
094        for (Iterator it = documents.iterator(); it.hasNext();) {
095            TravelDocument document = (TravelDocument) it.next();
096            retval.add(document.getDocumentNumber());
097        }
098
099        return retval;
100    }
101
102        /**
103         * @see org.kuali.kfs.module.tem.dataaccess.TravelDocumentDao#findPerDiem(int, java.sql.Date, java.sql.Date)
104         */
105        @Override
106    public List<PerDiem> findEffectivePerDiems(int primaryDestinationId, java.sql.Date effectiveDate){
107            Criteria criteria = new Criteria();
108            criteria.addEqualTo(TemPropertyConstants.PRIMARY_DESTINATION_ID, new Integer(primaryDestinationId));
109
110            //Add date criteria so the date falls in a specific range
111            //or their is no "To" date.  (Open-ended)
112        Criteria dateBetweenCriteria = new Criteria();
113        Criteria dateNullCriteria = new Criteria();
114
115        dateBetweenCriteria.addGreaterOrEqualThan(TemPropertyConstants.PER_DIEM_EFFECTIVE_TO_DATE, effectiveDate);
116        dateBetweenCriteria.addLessOrEqualThan(TemPropertyConstants.PER_DIEM_EFFECTIVE_FROM_DATE, effectiveDate);
117
118        dateNullCriteria.addIsNull(TemPropertyConstants.PER_DIEM_EFFECTIVE_TO_DATE);
119
120        dateBetweenCriteria.addOrCriteria(dateNullCriteria);
121        criteria.addAndCriteria(dateBetweenCriteria);
122            QueryByCriteria query = QueryFactory.newQuery(PerDiem.class, criteria);
123
124            List<PerDiem> possiblePerDiems = new ArrayList<PerDiem>();
125            possiblePerDiems.addAll(getPersistenceBrokerTemplate().getCollectionByQuery(query));
126            return possiblePerDiems;
127        }
128
129        /**
130         * @see org.kuali.kfs.module.tem.dataaccess.TravelDocumentDao#getOutstandingTravelAdvanceByInvoice(java.util.Set)
131         */
132    @Override
133    public List<TravelAdvance> getOutstandingTravelAdvanceByInvoice(Set<String> arInvoiceDocNumbers) {
134        if (ObjectUtils.isNull(arInvoiceDocNumbers) || arInvoiceDocNumbers.isEmpty()) {
135            return new ArrayList<TravelAdvance>();
136        }
137
138        Criteria criteria = new Criteria();
139        criteria.addIn(TemPropertyConstants.AR_INVOICE_DOC_NUMBER, arInvoiceDocNumbers);
140        criteria.addIsNull(TemPropertyConstants.TAXABLE_RAMIFICATION_NOTIFICATION_DATE);
141
142        Query query = QueryFactory.newQuery(TravelAdvance.class, criteria);
143
144        return (List<TravelAdvance>)getPersistenceBrokerTemplate().getCollectionByQuery(query);
145    }
146
147    /**
148     * @see org.kuali.kfs.module.tem.dataaccess.TravelDocumentDao#findLatestTaxableRamificationNotificationDate()
149     */
150    @Override
151    public Object[] findLatestTaxableRamificationNotificationDate() {
152        getPersistenceBrokerTemplate().clearCache();
153
154        Criteria criteria = new Criteria();
155        criteria.addNotNull(TemPropertyConstants.TAXABLE_RAMIFICATION_NOTIFICATION_DATE);
156
157        ReportQueryByCriteria query = QueryFactory.newReportQuery(TravelAdvance.class, criteria);
158
159        String selectionField = "max(" + TemPropertyConstants.TAXABLE_RAMIFICATION_NOTIFICATION_DATE + ")";
160        query.setAttributes(new String[] { selectionField });
161        query.setDistinct(true);
162
163        Iterator<Object[]> iterator = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(query);
164        return TransactionalServiceUtils.retrieveFirstAndExhaustIterator(iterator);
165
166
167    }
168
169    /**
170     * @see org.kuali.kfs.module.tem.dataaccess.TravelDocumentDao#getReimbursementDocumentsByHeaderStatus(java.lang.String, boolean)
171     */
172    @Override
173    public Collection<? extends TEMReimbursementDocument> getReimbursementDocumentsByHeaderStatus(String statusCode, boolean immediatesOnly) {
174        return (Collection<? extends TEMReimbursementDocument>)getTravelDocumentsByHeaderStatus(TravelReimbursementDocument.class, statusCode, immediatesOnly, TemPropertyConstants.TRAVEL_PAYMENT);
175    }
176
177    /**
178     * @see org.kuali.kfs.module.tem.dataaccess.TravelDocumentDao#getRelocationDocumentsByHeaderStatus(java.lang.String, boolean)
179     */
180    @Override
181    public Collection<? extends TEMReimbursementDocument> getRelocationDocumentsByHeaderStatus(String statusCode, boolean immediatesOnly) {
182        return (Collection<? extends TEMReimbursementDocument>)getTravelDocumentsByHeaderStatus(TravelRelocationDocument.class, statusCode, immediatesOnly, TemPropertyConstants.TRAVEL_PAYMENT);
183    }
184
185    /**
186     * @see org.kuali.kfs.module.tem.dataaccess.TravelDocumentDao#getEntertainmentDocumentsByHeaderStatus(java.lang.String, boolean)
187     */
188    @Override
189    public Collection<? extends TEMReimbursementDocument> getEntertainmentDocumentsByHeaderStatus(String statusCode, boolean immediatesOnly) {
190        return (Collection<? extends TEMReimbursementDocument>)getTravelDocumentsByHeaderStatus(TravelEntertainmentDocument.class, statusCode, immediatesOnly, TemPropertyConstants.TRAVEL_PAYMENT);
191    }
192
193    /**
194     * @see org.kuali.kfs.module.tem.dataaccess.TravelDocumentDao#getAuthorizationDocumentsByHeaderStatus(java.lang.String, boolean)
195     */
196    @Override
197    public Collection<? extends TravelAuthorizationDocument> getAuthorizationsAndAmendmentsByHeaderStatus(String statusCode, boolean immediatesOnly) {
198        List<TravelAuthorizationDocument> documents = new ArrayList<TravelAuthorizationDocument>();
199        documents.addAll((Collection<TravelAuthorizationDocument>)getTravelDocumentsByHeaderStatus(TravelAuthorizationDocument.class, statusCode, immediatesOnly, TemPropertyConstants.ADVANCE_TRAVEL_PAYMENT)); // extents will get travel auth amendments
200        return documents;
201    }
202
203    /**
204     * Do a lookup of reimbursable documents of the given class with the given financial system document header status code and a payment method of "check" (ie, PDP will pay out)
205     * @param documentClazz the class of the document to look up
206     * @param statusCode the status code of the documents to look up
207     * @param immediatesOnly true if only those reimbursable documents
208     * @return a Collection of qualifying documents
209     */
210    protected Collection<? extends TravelDocument> getTravelDocumentsByHeaderStatus(Class<? extends TravelDocument> documentClazz, String statusCode, boolean immediatesOnly, String travelPaymentProperty) {
211        if (LOG.isDebugEnabled()) {
212            LOG.debug("getReimbursableDocumentsByHeaderStatus() started");
213        }
214
215        Criteria criteria = new Criteria();
216        criteria.addEqualTo(KFSPropertyConstants.DOCUMENT_HEADER+"."+KFSPropertyConstants.FINANCIAL_DOCUMENT_STATUS_CODE, statusCode);
217        criteria.addEqualTo(travelPaymentProperty+".paymentMethodCode", KFSConstants.PaymentSourceConstants.PAYMENT_METHOD_CHECK);
218        if (immediatesOnly) {
219            criteria.addEqualTo(travelPaymentProperty+".immediatePaymentIndicator", Boolean.TRUE);
220        }
221
222        return getPersistenceBrokerTemplate().getCollectionByQuery(new QueryByCriteria(documentClazz, criteria));
223    }
224
225    /**
226     * Retrieves all travel reimbursement documents which have corporate card expenses to extract but which have not yet been extracted
227     * @see org.kuali.kfs.module.tem.dataaccess.TravelDocumentDao#getReimbursementDocumentsNeedingCorporateCardExtraction()
228     */
229    @Override
230    public Collection<? extends TEMReimbursementDocument> getReimbursementDocumentsNeedingCorporateCardExtraction() {
231        return (Collection<? extends TEMReimbursementDocument>)getUnExtractedCorporateCardTravelDocuments(TravelReimbursementDocument.class);
232
233    }
234
235    /**
236     * Retrieves all entertainment reimbursement documents which have corporate card expenses to extract but which have not yet been extracted
237     * @see org.kuali.kfs.module.tem.dataaccess.TravelDocumentDao#getReimbursementDocumentsNeedingCorporateCardExtraction()
238     */
239    @Override
240    public Collection<? extends TEMReimbursementDocument> getEntertainmentDocumentsNeedingCorporateCardExtraction() {
241        return (Collection<? extends TEMReimbursementDocument>)getUnExtractedCorporateCardTravelDocuments(TravelEntertainmentDocument.class);
242
243    }
244
245    /**
246     * Retrieves all moving and relocation reimbursement documents which have corporate card expenses to extract but which have not yet been extracted
247     * @see org.kuali.kfs.module.tem.dataaccess.TravelDocumentDao#getReimbursementDocumentsNeedingCorporateCardExtraction()
248     */
249    @Override
250    public Collection<? extends TEMReimbursementDocument> getRelocationDocumentsNeedingCorporateCardExtraction() {
251        return (Collection<? extends TEMReimbursementDocument>)getUnExtractedCorporateCardTravelDocuments(TravelRelocationDocument.class);
252
253    }
254
255    /**
256     * Retrieves all documents of the given TravelDocument class that a) are extracted or approved by document header, but b) have a null corporate card extraction date and c) actually have corporate card expenses
257     * @param documentClazz the class of the travel documents to retrieve
258     * @return a Collection of matching documents
259     */
260    protected Collection<? extends TravelDocument> getUnExtractedCorporateCardTravelDocuments(Class<? extends TravelDocument> documentClazz) {
261        Criteria criteria = new Criteria();
262
263        List<String> statusCodes = new ArrayList<String>();
264        statusCodes.add(KFSConstants.DocumentStatusCodes.Payments.EXTRACTED);
265        statusCodes.add(KFSConstants.DocumentStatusCodes.APPROVED);
266        criteria.addIn(KFSPropertyConstants.DOCUMENT_HEADER+"."+KFSPropertyConstants.FINANCIAL_DOCUMENT_STATUS_CODE, statusCodes);
267        criteria.addIsNull(TemPropertyConstants.CORPORATE_CARD_PAYMENT_EXTRACT_DATE);
268
269        Criteria expenseCriteria = new Criteria();
270        expenseCriteria.addEqualTo(TemPropertyConstants.CARD_TYPE, TemConstants.TRAVEL_TYPE_CORP);
271        expenseCriteria.addEqualTo(TemPropertyConstants.EXPENSE_LINE_TYPE_CODE, TemConstants.EXPENSE_IMPORTED);
272        expenseCriteria.addEqualTo(TemPropertyConstants.HISTORICAL_TRAVEL_EXPENSE+"."+TemPropertyConstants.CREDIT_CARD_AGENCY+"."+TemPropertyConstants.PAYMENT_INDICATOR, Boolean.TRUE);
273        ReportQueryByCriteria expensesSubQuery = QueryFactory.newReportQuery(ImportedExpense.class, expenseCriteria);
274        expensesSubQuery.setAttributes(new String[] {KFSPropertyConstants.DOCUMENT_NUMBER});
275        criteria.addIn(KFSPropertyConstants.DOCUMENT_NUMBER, expensesSubQuery);
276
277        return getPersistenceBrokerTemplate().getCollectionByQuery(new QueryByCriteria(documentClazz, criteria));
278    }
279
280
281    @Override
282    public Collection<? extends TEMReimbursementDocument> findMatchingTrips (Integer temProfileId ,Timestamp tripBegin, Timestamp tripEnd)  {
283
284
285        final Criteria criteria = new Criteria();
286        final Criteria orEndDateCriteria = new Criteria();
287        criteria.addEqualTo(TemPropertyConstants.TEM_PROFILE_ID, temProfileId);
288        criteria.addEqualTo(TemPropertyConstants.TRIP_BEGIN_DT, tripBegin);
289        orEndDateCriteria.addEqualTo(TemPropertyConstants.TRIP_END_DT, tripEnd);
290        criteria.addOrCriteria(orEndDateCriteria);
291        criteria.addNotIn("documentHeader.financialDocumentStatusCode", Arrays.asList(DocumentStatusCodes.INITIATED));
292
293        return  getPersistenceBrokerTemplate().getCollectionByQuery(new QueryByCriteria(TravelReimbursementDocument.class, criteria));
294
295
296    }
297}