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.pdp.dataaccess.impl;
017
018import java.sql.Date;
019import java.sql.Timestamp;
020import java.util.ArrayList;
021import java.util.Collection;
022import java.util.Collections;
023import java.util.HashMap;
024import java.util.Iterator;
025import java.util.List;
026import java.util.Map;
027
028import org.apache.commons.lang.builder.EqualsBuilder;
029import org.apache.commons.lang.builder.HashCodeBuilder;
030import org.apache.ojb.broker.query.Criteria;
031import org.apache.ojb.broker.query.QueryByCriteria;
032import org.apache.ojb.broker.query.QueryFactory;
033import org.kuali.ole.pdp.PdpConstants;
034import org.kuali.ole.pdp.PdpPropertyConstants;
035import org.kuali.ole.pdp.businessobject.DailyReport;
036import org.kuali.ole.pdp.businessobject.DisbursementNumberRange;
037import org.kuali.ole.pdp.businessobject.PaymentDetail;
038import org.kuali.ole.pdp.businessobject.PaymentGroup;
039import org.kuali.ole.pdp.businessobject.options.DailyReportComparator;
040import org.kuali.ole.pdp.dataaccess.PaymentDetailDao;
041import org.kuali.ole.sys.OLEPropertyConstants;
042import org.kuali.rice.core.api.util.type.KualiDecimal;
043import org.kuali.rice.core.api.util.type.KualiInteger;
044import org.kuali.rice.core.framework.persistence.ojb.dao.PlatformAwareDaoBaseOjb;
045
046public class PaymentDetailDaoOjb extends PlatformAwareDaoBaseOjb implements PaymentDetailDao {
047    private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(PaymentDetailDaoOjb.class);
048
049    public PaymentDetailDaoOjb() {
050        super();
051    }
052
053
054    /**
055     * @see org.kuali.ole.pdp.dataaccess.PaymentDetailDao#getAchPaymentsWithUnsentEmail()
056     */
057    public Iterator getAchPaymentsWithUnsentEmail() {
058        LOG.debug("getAchPaymentsWithUnsentEmail() started");
059
060        Criteria crit = new Criteria();
061        crit.addEqualTo(PdpPropertyConstants.PaymentDetail.PAYMENT_STATUS_CODE, PdpConstants.PaymentStatusCodes.EXTRACTED);
062        crit.addEqualTo(PdpPropertyConstants.PaymentDetail.PAYMENT_DISBURSEMENT_TYPE_CODE, PdpConstants.DisbursementTypeCodes.ACH);
063        crit.addIsNull(PdpPropertyConstants.PaymentDetail.PAYMENT_GROUP + "." + PdpPropertyConstants.ADVICE_EMAIL_SENT_DATE);
064
065        return getPersistenceBrokerTemplate().getIteratorByQuery(new QueryByCriteria(PaymentDetail.class, crit));
066    }
067
068    /**
069     * @see org.kuali.ole.pdp.dataaccess.PaymentDetailDao#getDailyReportData(java.sql.Date)
070     */
071    public List<DailyReport> getDailyReportData(Date currentSqlDate) {
072        LOG.debug("getDailyReportData() started");
073
074        if (LOG.isDebugEnabled()) {
075            LOG.debug("getDailyReportData() " + currentSqlDate);
076        }
077
078        Criteria criteria = new Criteria();
079        criteria.addEqualTo(PdpPropertyConstants.PaymentDetail.PAYMENT_GROUP + "." + PdpPropertyConstants.PaymentGroup.PAYMENT_GROUP_PAYMENT_STATUS_CODE, PdpConstants.PaymentStatusCodes.OPEN);
080
081        // (Payment date <= usePaydate OR immediate = TRUE)
082        Criteria criteria1 = new Criteria();
083        criteria1.addEqualTo(PdpPropertyConstants.PaymentDetail.PAYMENT_GROUP + "." + PdpPropertyConstants.PaymentGroup.PROCESS_IMMEDIATE, Boolean.TRUE);
084
085        Criteria criteria2 = new Criteria();
086        criteria2.addLessOrEqualThan(PdpPropertyConstants.PaymentDetail.PAYMENT_GROUP + "." + PdpPropertyConstants.PaymentGroup.PAYMENT_DATE, currentSqlDate);
087        criteria1.addOrCriteria(criteria2);
088
089        criteria.addAndCriteria(criteria1);
090
091        QueryByCriteria q = QueryFactory.newQuery(PaymentDetail.class, criteria);
092
093        q.addOrderByDescending(PdpPropertyConstants.PaymentDetail.PAYMENT_PROCESS_IMEDIATE);
094        q.addOrderByDescending(PdpPropertyConstants.PaymentDetail.PAYMENT_SPECIAL_HANDLING);
095        q.addOrderByDescending(PdpPropertyConstants.PaymentDetail.PAYMENT_ATTACHMENT);
096        q.addOrderByAscending(PdpPropertyConstants.PaymentDetail.PAYMENT_CHART_CODE);
097        q.addOrderByAscending(PdpPropertyConstants.PaymentDetail.PAYMENT_UNIT_CODE);
098        q.addOrderByAscending(PdpPropertyConstants.PaymentDetail.PAYMENT_SUBUNIT_CODE);
099        q.addOrderByAscending(PdpPropertyConstants.PaymentDetail.PAYMENT_GROUP + "." + PdpPropertyConstants.PaymentGroup.PAYMENT_GROUP_ID);
100
101        Map<Key, Numbers> summary = new HashMap<Key, Numbers>();
102        KualiInteger lastGroupId = null;
103        Iterator i = getPersistenceBrokerTemplate().getIteratorByQuery(q);
104        while (i.hasNext()) {
105            PaymentDetail d = (PaymentDetail) i.next();
106            Key rsk = new Key(d);
107            Numbers n = summary.get(rsk);
108            if (n == null) {
109                n = new Numbers();
110                n.amount = d.getNetPaymentAmount();
111                n.payments = 1;
112                n.payees = 1;
113                summary.put(rsk, n);
114                lastGroupId = d.getPaymentGroup().getId();
115            }
116            else {
117                n.payments++;
118                n.amount = n.amount.add(d.getNetPaymentAmount());
119                if (lastGroupId.intValue() != d.getPaymentGroup().getId().intValue()) {
120                    n.payees++;
121                    lastGroupId = d.getPaymentGroup().getId();
122                }
123            }
124        }
125        // Now take the data and put it in our result list
126        List<DailyReport> data = new ArrayList<DailyReport>();
127        for (Iterator iter = summary.keySet().iterator(); iter.hasNext();) {
128            Key e = (Key) iter.next();
129            Numbers n = summary.get(e);
130            DailyReport r = new DailyReport(e.customerShortName, n.amount, n.payments, n.payees, e.paymentGroup);
131            data.add(r);
132        }
133        Collections.sort(data, new DailyReportComparator());
134
135        return data;
136    }
137
138    class Key {
139        public Boolean pymtAttachment;
140        public Boolean pymtSpecialHandling;
141        public Boolean processImmediate;
142        public String customerShortName;
143        public PaymentGroup paymentGroup;
144
145        public Key(PaymentDetail d) {
146            this(d.getPaymentGroup().getPymtAttachment(), d.getPaymentGroup().getPymtSpecialHandling(), d.getPaymentGroup().getProcessImmediate(), d.getPaymentGroup().getBatch().getCustomerProfile().getCustomerShortName(), d.getPaymentGroup());
147        }
148
149        public Key(Boolean att, Boolean spec, Boolean immed, String c, PaymentGroup paymentGroup) {
150            pymtAttachment = att;
151            pymtSpecialHandling = spec;
152            processImmediate = immed;
153            customerShortName = c;
154            this.paymentGroup = paymentGroup;
155        }
156
157        @Override
158        public int hashCode() {
159            return new HashCodeBuilder(3, 5).append(pymtAttachment).append(pymtSpecialHandling).append(processImmediate).append(customerShortName).toHashCode();
160        }
161
162        @Override
163        public boolean equals(Object obj) {
164            if (!(obj instanceof Key)) {
165                return false;
166            }
167            Key thisobj = (Key) obj;
168            return new EqualsBuilder().append(pymtAttachment, thisobj.pymtAttachment).append(pymtSpecialHandling, thisobj.pymtSpecialHandling).append(processImmediate, thisobj.processImmediate).append(customerShortName, thisobj.customerShortName).isEquals();
169        }
170
171        @Override
172        public String toString() {
173            return pymtAttachment + " " + pymtSpecialHandling + " " + processImmediate + " " + customerShortName;
174        }
175    }
176
177    class Numbers {
178        public KualiDecimal amount = KualiDecimal.ZERO;
179        public int payments = 0;
180        public int payees = 0;
181    }
182
183    /**
184     * @see org.kuali.ole.pdp.dataaccess.PaymentDetailDao#getDetailForEpic(String, String, String, String)
185     */
186    public PaymentDetail getDetailForEpic(String custPaymentDocNbr, String fdocTypeCode, String orgCode, String subUnitCode) {
187        LOG.debug("getDetailForEpic(custPaymentDocNbr, fdocTypeCode) started");
188        List data = new ArrayList();
189
190        Criteria criteria = new Criteria();
191        criteria.addEqualTo(PdpPropertyConstants.PaymentDetail.PAYMENT_CUSTOMER_DOC_NUMBER, custPaymentDocNbr);
192        criteria.addEqualTo(PdpPropertyConstants.PaymentDetail.PAYMENT_DISBURSEMENT_FINANCIAL_DOCUMENT_TYPE_CODE, fdocTypeCode);
193
194        criteria.addEqualTo(PdpPropertyConstants.PaymentDetail.PAYMENT_UNIT_CODE, orgCode);
195        criteria.addEqualTo(PdpPropertyConstants.PaymentDetail.PAYMENT_SUBUNIT_CODE, subUnitCode);
196
197        List paymentDetails = (List) getPersistenceBrokerTemplate().getCollectionByQuery(new QueryByCriteria(PaymentDetail.class, criteria));
198        PaymentDetail cp = null;
199        for (Iterator iter = paymentDetails.iterator(); iter.hasNext();) {
200            PaymentDetail pd = (PaymentDetail) iter.next();
201            if (cp == null) {
202                cp = pd;
203            }
204            else {
205                if ((pd.getPaymentGroup().getBatch().getCustomerFileCreateTimestamp().compareTo(cp.getPaymentGroup().getBatch().getCustomerFileCreateTimestamp())) > 0) {
206                    cp = pd;
207                }
208            }
209        }
210
211        return cp;
212    }
213
214    /**
215     * @see org.kuali.ole.pdp.dataaccess.PaymentDetailDao#getDisbursementNumberRanges(java.lang.String)
216     */
217    public List<DisbursementNumberRange> getDisbursementNumberRanges(String campus) {
218        LOG.debug("getDisbursementNumberRanges() started");
219
220        java.util.Date now = new java.util.Date();
221        Timestamp nowTs = new Timestamp(now.getTime());
222
223        Criteria criteria = new Criteria();
224        criteria.addLessOrEqualThan(PdpPropertyConstants.DISBURSEMENT_NUMBER_RANGE_START_DATE, nowTs);
225        criteria.addEqualTo(PdpPropertyConstants.PHYS_CAMPUS_PROC_CODE, campus);
226        criteria.addEqualTo(OLEPropertyConstants.ACTIVE, true);
227
228        QueryByCriteria qbc = new QueryByCriteria(DisbursementNumberRange.class, criteria);
229        qbc.addOrderBy(OLEPropertyConstants.BANK_CODE, true);
230
231        return (List<DisbursementNumberRange>) getPersistenceBrokerTemplate().getCollectionByQuery(qbc);
232    }
233
234    /**
235     * @see org.kuali.ole.pdp.dataaccess.PaymentDetailDao#getUnprocessedCancelledDetails(java.lang.String, java.lang.String)
236     */
237    public Iterator getUnprocessedCancelledDetails(String organization, List<String> subUnits) {
238        LOG.debug("getUnprocessedCancelledDetails() started");
239
240        Collection codes = new ArrayList();
241        codes.add(PdpConstants.PaymentStatusCodes.CANCEL_DISBURSEMENT);
242        codes.add(PdpConstants.PaymentStatusCodes.CANCEL_PAYMENT);
243
244        Criteria criteria = new Criteria();
245        criteria.addIn(PdpPropertyConstants.PaymentDetail.PAYMENT_SUBUNIT_CODE, subUnits);
246        criteria.addEqualTo(PdpPropertyConstants.PaymentDetail.PAYMENT_UNIT_CODE, organization);
247        criteria.addIn(PdpPropertyConstants.PaymentDetail.PAYMENT_STATUS_CODE, codes);
248        criteria.addIsNull(PdpPropertyConstants.PaymentDetail.PAYMENT_EPIC_PAYMENT_CANCELLED_DATE);
249
250        return getPersistenceBrokerTemplate().getIteratorByQuery(new QueryByCriteria(PaymentDetail.class, criteria));
251    }
252
253    /**
254     * @see org.kuali.ole.pdp.dataaccess.PaymentDetailDao#getUnprocessedPaidDetails(java.lang.String, java.lang.String)
255     */
256    public Iterator getUnprocessedPaidDetails(String organization, List<String> subUnits) {
257        LOG.debug("getUnprocessedPaidDetails() started");
258
259        Criteria criteria = new Criteria();
260        criteria.addIn(PdpPropertyConstants.PaymentDetail.PAYMENT_SUBUNIT_CODE, subUnits);
261        criteria.addEqualTo(PdpPropertyConstants.PaymentDetail.PAYMENT_UNIT_CODE, organization);
262        criteria.addEqualTo(PdpPropertyConstants.PaymentDetail.PAYMENT_STATUS_CODE, PdpConstants.PaymentStatusCodes.EXTRACTED);
263        criteria.addIsNull(PdpPropertyConstants.PaymentDetail.PAYMENT_EPIC_PAYMENT_PAID_EXTRACTED_DATE);
264
265        return getPersistenceBrokerTemplate().getIteratorByQuery(new QueryByCriteria(PaymentDetail.class, criteria));
266    }
267
268}