001/*
002 * Copyright 2005-2006 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.coa.dataaccess.impl;
017
018import java.sql.Date;
019import java.util.ArrayList;
020import java.util.Collection;
021import java.util.Iterator;
022import java.util.List;
023
024import org.apache.ojb.broker.query.Criteria;
025import org.apache.ojb.broker.query.QueryFactory;
026import org.apache.ojb.broker.query.ReportQueryByCriteria;
027import org.kuali.ole.coa.businessobject.Account;
028import org.kuali.ole.coa.businessobject.AccountDelegate;
029import org.kuali.ole.coa.dataaccess.AccountDao;
030import org.kuali.ole.sys.OLEConstants;
031import org.kuali.ole.sys.OLEPropertyConstants;
032import org.kuali.ole.sys.businessobject.AccountResponsibility;
033import org.kuali.rice.core.api.util.type.KualiDecimal;
034import org.kuali.rice.core.framework.persistence.ojb.dao.PlatformAwareDaoBaseOjb;
035import org.kuali.rice.kim.api.identity.Person;
036import org.kuali.rice.krad.util.ObjectUtils;
037
038/**
039 * This class is the OJB implementation of the AccountDao interface.
040 */
041public class AccountDaoOjb extends PlatformAwareDaoBaseOjb implements AccountDao {
042    private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(AccountDaoOjb.class);
043
044    /**
045     * Retrieves account business object by primary key
046     * 
047     * @param chartOfAccountsCode - the FIN_COA_CD of the Chart Code that is part of the composite key of Account
048     * @param accountNumber - the ACCOUNT_NBR part of the composite key of Accont
049     * @return Account
050     * @see AccountDao
051     */
052    public Account getByPrimaryId(String chartOfAccountsCode, String accountNumber) {
053        LOG.debug("getByPrimaryId() started");
054
055        Criteria criteria = new Criteria();
056        criteria.addEqualTo("chartOfAccountsCode", chartOfAccountsCode);
057        criteria.addEqualTo("accountNumber", accountNumber);
058
059        return (Account) getPersistenceBrokerTemplate().getObjectByQuery(QueryFactory.newQuery(Account.class, criteria));
060    }
061
062    /**
063     * fetch the accounts that the user is either the fiscal officer or a delegate of the fiscal officer
064     * 
065     * @param kualiUser
066     * @return a list of Accounts that the user has responsibility for
067     */
068    public List getAccountsThatUserIsResponsibleFor(Person person, java.util.Date currentDate) {
069        LOG.debug("getAccountsThatUserIsResponsibleFor() started");
070
071        List accountResponsibilities = new ArrayList();
072        accountResponsibilities.addAll(getFiscalOfficerResponsibilities(person));
073        accountResponsibilities.addAll(getDelegatedResponsibilities(person, currentDate));
074        return accountResponsibilities;
075    }
076
077    /**
078     * @see org.kuali.ole.coa.dataaccess.AccountDao#determineUserResponsibilityOnAccount(org.kuali.rice.kim.api.identity.Person,
079     *      org.kuali.ole.coa.businessobject.Account, java.sql.Date)
080     */
081    public boolean determineUserResponsibilityOnAccount(Person person, Account account, Date currentSqlDate) {
082        boolean result = hasFiscalOfficerResponsibility(person, account);
083        if (!result) {
084            result = hasDelegatedResponsibility(person, account, currentSqlDate);
085        }
086        return result;
087    }
088
089    /**
090     * Resolves the Primary Delegate for the given delegate example. If the primary delegate exists for a specific Document Type
091     * Code and for a Document Type Code of "OLE", the delegate for the specific document type code is returned;
092     * 
093     * @see org.kuali.ole.coa.dataaccess.AccountDao#getPrimaryDelegationByExample(org.kuali.ole.coa.businessobject.AccountDelegate,
094     *      java.lang.String)
095     */
096    public List getPrimaryDelegationByExample(AccountDelegate delegateExample, Date currentSqlDate, String totalDollarAmount) {
097        return new ArrayList(getPersistenceBrokerTemplate().getCollectionByQuery(QueryFactory.newQuery(AccountDelegate.class, getDelegateByExampleCriteria(delegateExample, currentSqlDate, totalDollarAmount, "Y"))));
098    }
099
100    /**
101     * @see org.kuali.ole.coa.dataaccess.AccountDao#getSecondaryDelegationsByExample(org.kuali.ole.coa.businessobject.AccountDelegate,
102     *      java.lang.String)
103     */
104    public List getSecondaryDelegationsByExample(AccountDelegate delegateExample, Date currentSqlDate, String totalDollarAmount) {
105        return new ArrayList(getPersistenceBrokerTemplate().getCollectionByQuery(QueryFactory.newQuery(AccountDelegate.class, getDelegateByExampleCriteria(delegateExample, currentSqlDate, totalDollarAmount, "N"))));
106    }
107
108    /**
109     * This method creates a {@link Criteria} based on {@link Delegate}, dollar amount and whether or not it is the primary delegate
110     * 
111     * @param delegateExample
112     * @param totalDollarAmount
113     * @param accountsDelegatePrmrtIndicator
114     * @return example {@link Delegate} {@link Criteria}
115     */
116    protected Criteria getDelegateByExampleCriteria(AccountDelegate delegateExample, Date currentSqlDate, String totalDollarAmount, String accountsDelegatePrmrtIndicator) {
117        Criteria criteria = new Criteria();
118        criteria.addEqualTo(OLEConstants.CHART_OF_ACCOUNTS_CODE_PROPERTY_NAME, delegateExample.getChartOfAccountsCode());
119        criteria.addEqualTo(OLEConstants.ACCOUNT_NUMBER_PROPERTY_NAME, delegateExample.getAccountNumber());
120        criteria.addEqualTo("active", "Y");
121        criteria.addLessOrEqualThan("accountDelegateStartDate", currentSqlDate);
122        criteria.addEqualTo("accountsDelegatePrmrtIndicator", accountsDelegatePrmrtIndicator);
123        if (totalDollarAmount != null) {
124            // (toAmt is nullish and (fromAmt is nullish or fromAmt <= total)) or (fromAmt is nullish and (toAmt is nullish or toAmt
125            // >= total)) or (fromAmt <= total and toAmount >= total)
126
127            /* to not active clause: (toAmt is nullish and (fromAmt is nullish or fromAmt <= total)) */
128            Criteria toAmountIsNullish = new Criteria();
129            toAmountIsNullish.addIsNull(OLEPropertyConstants.FIN_DOC_APPROVAL_TO_THIS_AMOUNT);
130            Criteria toAmountIsZero1 = new Criteria();
131            toAmountIsZero1.addEqualTo(OLEPropertyConstants.FIN_DOC_APPROVAL_TO_THIS_AMOUNT, "0");
132            toAmountIsNullish.addOrCriteria(toAmountIsZero1);
133
134            Criteria fromMatchesClause = new Criteria();
135            fromMatchesClause.addIsNull(OLEPropertyConstants.FIN_DOC_APPROVAL_FROM_THIS_AMT);
136            Criteria fromAmountIsLessThanTotal = new Criteria();
137            fromAmountIsLessThanTotal.addLessOrEqualThan(OLEPropertyConstants.FIN_DOC_APPROVAL_FROM_THIS_AMT, totalDollarAmount);
138            fromMatchesClause.addOrCriteria(fromAmountIsLessThanTotal);
139
140            Criteria toNotActiveClause = new Criteria();
141            toNotActiveClause.addAndCriteria(toAmountIsNullish);
142            toNotActiveClause.addAndCriteria(fromMatchesClause);
143
144            /* from not active clause: (fromAmt is nullish and (toAmt is nullish or toAmt >= total)) */
145            Criteria toMatchesClause = new Criteria();
146            toMatchesClause.addIsNull(OLEPropertyConstants.FIN_DOC_APPROVAL_TO_THIS_AMOUNT);
147            Criteria toAmountIsZero2 = new Criteria();
148            toAmountIsZero2.addEqualTo(OLEPropertyConstants.FIN_DOC_APPROVAL_TO_THIS_AMOUNT, "0");
149            toMatchesClause.addOrCriteria(toAmountIsZero2);
150            Criteria toAmountIsGreaterThanTotal = new Criteria();
151            toAmountIsGreaterThanTotal.addGreaterOrEqualThan(OLEPropertyConstants.FIN_DOC_APPROVAL_TO_THIS_AMOUNT, totalDollarAmount);
152            toMatchesClause.addOrCriteria(toAmountIsGreaterThanTotal);
153
154            Criteria fromIsNullClause = new Criteria();
155            fromIsNullClause.addIsNull(OLEPropertyConstants.FIN_DOC_APPROVAL_FROM_THIS_AMT);
156            Criteria fromIsZeroClause = new Criteria();
157            fromIsZeroClause.addEqualTo(OLEPropertyConstants.FIN_DOC_APPROVAL_FROM_THIS_AMT, "0");
158            Criteria fromIsNullishClause = new Criteria();
159            fromIsNullishClause.addOrCriteria(fromIsNullClause);
160            fromIsNullishClause.addOrCriteria(fromIsZeroClause);
161            Criteria fromNotActiveClause = new Criteria();
162            fromNotActiveClause.addAndCriteria(fromIsNullishClause);
163            fromNotActiveClause.addAndCriteria(toMatchesClause);
164
165            Criteria bothActive = new Criteria();
166            bothActive.addLessOrEqualThan(OLEPropertyConstants.FIN_DOC_APPROVAL_FROM_THIS_AMT, totalDollarAmount);
167            bothActive.addGreaterOrEqualThan(OLEPropertyConstants.FIN_DOC_APPROVAL_TO_THIS_AMOUNT, totalDollarAmount);
168
169            Criteria totalDollarAmountCriteria = new Criteria();
170            totalDollarAmountCriteria.addOrCriteria(toNotActiveClause);
171            totalDollarAmountCriteria.addOrCriteria(fromNotActiveClause);
172            totalDollarAmountCriteria.addOrCriteria(bothActive);
173
174            criteria.addAndCriteria(totalDollarAmountCriteria);
175        }
176        return criteria;
177    }
178
179    /**
180     * method to get the fo responsibilities for the account
181     * 
182     * @param person - fiscal officer to check for
183     * @return list of {@link AccountResponsibility} for this fiscal officer
184     */
185    protected List getFiscalOfficerResponsibilities(Person person) {
186        List fiscalOfficerResponsibilities = new ArrayList();
187        Criteria criteria = new Criteria();
188        criteria.addEqualTo("accountFiscalOfficerSystemIdentifier", person.getPrincipalId());
189        Collection accounts = getPersistenceBrokerTemplate().getCollectionByQuery(QueryFactory.newQuery(Account.class, criteria));
190        for (Iterator iter = accounts.iterator(); iter.hasNext();) {
191            Account account = (Account) iter.next();
192            AccountResponsibility accountResponsibility = new AccountResponsibility(AccountResponsibility.FISCAL_OFFICER_RESPONSIBILITY, KualiDecimal.ZERO, KualiDecimal.ZERO, "", account);
193            fiscalOfficerResponsibilities.add(accountResponsibility);
194        }
195        return fiscalOfficerResponsibilities;
196    }
197
198    /**
199     * This method determines if a given user has fiscal officer responsiblity on a given account.
200     * 
201     * @param person the user to check responsibilities for
202     * @param account the account to check responsibilities on
203     * @return true if user does have fiscal officer responsibility on account, false if otherwise
204     */
205    protected boolean hasFiscalOfficerResponsibility(Person person, Account account) {
206        boolean hasFiscalOfficerResponsibility = false;
207        Criteria criteria = new Criteria();
208        criteria.addEqualTo("accountFiscalOfficerSystemIdentifier", person.getPrincipalId());
209        criteria.addEqualTo("chartOfAccountsCode", account.getChartOfAccountsCode());
210        criteria.addEqualTo("accountNumber", account.getAccountNumber());
211        Collection accounts = getPersistenceBrokerTemplate().getCollectionByQuery(QueryFactory.newQuery(Account.class, criteria));
212        if (accounts != null && accounts.size() > 0) {
213            Account retrievedAccount = (Account) accounts.iterator().next();
214            if (ObjectUtils.isNotNull(retrievedAccount)) {
215                hasFiscalOfficerResponsibility = true;
216            }
217        }
218        return hasFiscalOfficerResponsibility;
219    }
220
221    /**
222     * method to get the fo delegated responsibilities for the account
223     * 
224     * @param person - user to check against
225     * @return a list of {@link AccountResponsibility} objects for a delegate
226     */
227    protected List getDelegatedResponsibilities(Person person, java.util.Date currentDate) {
228        List delegatedResponsibilities = new ArrayList();
229        Criteria criteria = new Criteria();
230        criteria.addEqualTo("accountDelegateSystemId", person.getPrincipalId());
231        Collection accountDelegates = getPersistenceBrokerTemplate().getCollectionByQuery(QueryFactory.newQuery(AccountDelegate.class, criteria));
232        for (Iterator iter = accountDelegates.iterator(); iter.hasNext();) {
233            AccountDelegate accountDelegate = (AccountDelegate) iter.next();
234            if (accountDelegate.isActive()) {
235                // the start_date should never be null in the real world, but
236                // there is some test data that
237                // contains null startDates, therefore this check.
238                if (ObjectUtils.isNotNull(accountDelegate.getAccountDelegateStartDate())) {
239                    if (!accountDelegate.getAccountDelegateStartDate().after(currentDate)) {
240                        Account account = getByPrimaryId(accountDelegate.getChartOfAccountsCode(), accountDelegate.getAccount().getAccountNumber());
241                        AccountResponsibility accountResponsibility = new AccountResponsibility(AccountResponsibility.DELEGATED_RESPONSIBILITY, accountDelegate.getFinDocApprovalFromThisAmt(), accountDelegate.getFinDocApprovalToThisAmount(), accountDelegate.getFinancialDocumentTypeCode(), account);
242                        delegatedResponsibilities.add(accountResponsibility);
243                    }
244                }
245            }
246        }
247        return delegatedResponsibilities;
248    }
249
250    /**
251     * This method determines if a user has delegated responsibilities on a given account.
252     * 
253     * @param person the user to check responsibilities for
254     * @param account the account to check responsibilities on
255     * @return true if user has delegated responsibilities
256     */
257    protected boolean hasDelegatedResponsibility(Person person, Account account, java.util.Date currentDate) {
258        boolean hasResponsibility = false;
259        Criteria criteria = new Criteria();
260        criteria.addEqualTo("accountDelegateSystemId", person.getPrincipalId());
261        criteria.addEqualTo("chartOfAccountsCode", account.getChartOfAccountsCode());
262        criteria.addEqualTo("accountNumber", account.getAccountNumber());
263        Collection accountDelegates = getPersistenceBrokerTemplate().getCollectionByQuery(QueryFactory.newQuery(AccountDelegate.class, criteria));
264        for (Iterator iter = accountDelegates.iterator(); iter.hasNext() && !hasResponsibility;) {
265            AccountDelegate accountDelegate = (AccountDelegate) iter.next();
266            if (accountDelegate.isActive()) {
267                // the start_date should never be null in the real world, but
268                // there is some test data that
269                // contains null startDates, therefore this check.
270                if (ObjectUtils.isNotNull(accountDelegate.getAccountDelegateStartDate())) {
271                    if (!accountDelegate.getAccountDelegateStartDate().after(currentDate)) {
272                        hasResponsibility = true;
273                    }
274                }
275            }
276        }
277        return hasResponsibility;
278    }
279
280    /**
281     * @see org.kuali.ole.coa.dataaccess.AccountDao#getAllAccounts()
282     * @return an iterator for all accounts
283     */
284    public Iterator getAllAccounts() {
285        LOG.debug("getAllAccounts() started");
286
287        Criteria criteria = new Criteria();
288        return getPersistenceBrokerTemplate().getIteratorByQuery(QueryFactory.newQuery(Account.class, criteria));
289    }
290
291    /**
292     * @see org.kuali.ole.coa.dataaccess.AccountDao#getActiveAccountsForAccountSupervisor(java.lang.String, java.sql.Date)
293     */
294    public Iterator<Account> getActiveAccountsForAccountSupervisor(String principalId, Date currentSqlDate) {
295        Criteria criteria = new Criteria();
296        criteria.addEqualTo("accountsSupervisorySystemsIdentifier", principalId);
297        criteria.addEqualTo("active", true);
298        criteria.addAndCriteria(getAccountNotExpiredCriteria(currentSqlDate));
299        return (Iterator<Account>) getPersistenceBrokerTemplate().getIteratorByQuery(QueryFactory.newQuery(Account.class, criteria));
300    }
301
302    /**
303     * @see org.kuali.ole.coa.dataaccess.AccountDao#getActiveAccountsForFiscalOfficer(java.lang.String)
304     */
305    public Iterator<Account> getActiveAccountsForFiscalOfficer(String principalId, Date currentSqlDate) {
306        Criteria criteria = new Criteria();
307        criteria.addEqualTo("accountFiscalOfficerSystemIdentifier", principalId);
308        criteria.addEqualTo("active", true);
309        criteria.addAndCriteria(getAccountNotExpiredCriteria(currentSqlDate));
310        return (Iterator<Account>) getPersistenceBrokerTemplate().getIteratorByQuery(QueryFactory.newQuery(Account.class, criteria));
311    }
312
313    /**
314     * @see org.kuali.ole.coa.dataaccess.AccountDao#getExpiredAccountsForAccountSupervisor(java.lang.String)
315     */
316    public Iterator<Account> getExpiredAccountsForAccountSupervisor(String principalId, Date currentSqlDate) {
317        Criteria criteria = new Criteria();
318        criteria.addEqualTo("accountsSupervisorySystemsIdentifier", principalId);
319        criteria.addEqualTo("active", true);
320        criteria.addAndCriteria(getAccountExpiredCriteria(currentSqlDate));
321        return (Iterator<Account>) getPersistenceBrokerTemplate().getIteratorByQuery(QueryFactory.newQuery(Account.class, criteria));
322    }
323
324    /**
325     * @see org.kuali.ole.coa.dataaccess.AccountDao#getExpiredAccountsForFiscalOfficer(java.lang.String)
326     */
327    public Iterator<Account> getExpiredAccountsForFiscalOfficer(String principalId, Date currentSqlDate) {
328        Criteria criteria = new Criteria();
329        criteria.addEqualTo("accountFiscalOfficerSystemIdentifier", principalId);
330        criteria.addEqualTo("active", true);
331        criteria.addAndCriteria(getAccountExpiredCriteria(currentSqlDate));
332        return (Iterator<Account>) getPersistenceBrokerTemplate().getIteratorByQuery(QueryFactory.newQuery(Account.class, criteria));
333    }
334
335    /**
336     * Builds a criteria to find expired accounts
337     * 
338     * @return a Criteria for expired accounts
339     */
340    protected Criteria getAccountExpiredCriteria(Date currentSqlDate) {
341        Criteria criteria = new Criteria();
342        criteria.addNotNull("accountExpirationDate");
343        criteria.addLessOrEqualThan("accountExpirationDate", currentSqlDate);
344        return criteria;
345    }
346
347    /**
348     * Builds a criteria to find non-expired accounts
349     * 
350     * @return a Criteria for non-expired accounts
351     */
352    protected Criteria getAccountNotExpiredCriteria(Date currentSqlDate) {
353        Criteria criteria = new Criteria();
354        criteria.addIsNull("accountExpirationDate");
355
356        Criteria notYetExpiredCriteria = new Criteria();
357        notYetExpiredCriteria.addGreaterThan("accountExpirationDate", currentSqlDate);
358
359        criteria.addOrCriteria(notYetExpiredCriteria);
360        return criteria;
361    }
362
363    /**
364     * @see org.kuali.ole.coa.dataaccess.AccountDao#isPrincipalInAnyWayShapeOrFormAccountManager(java.lang.String)
365     */
366    public boolean isPrincipalInAnyWayShapeOrFormAccountManager(String principalId) {
367        return queryPrincipalHasAccountRole(principalId, "accountManagerSystemIdentifier");
368    }
369
370    /**
371     * Determines if any non-closed accounts exist where the principal id is in the role of the role name
372     * 
373     * @param principalId the principal id to check
374     * @param principalRoleName the name of the field on account to check for the principal id in
375     * @return true if the principal has that account role, false otherwise
376     */
377    protected boolean queryPrincipalHasAccountRole(String principalId, String principalRoleName) {
378        Criteria criteria = new Criteria();
379        criteria.addEqualTo(principalRoleName, principalId);
380        criteria.addEqualTo("active", "Y");
381
382        ReportQueryByCriteria reportQuery = QueryFactory.newReportQuery(Account.class, criteria);
383        reportQuery.setAttributes(new String[] { "count(*)" });
384
385        int resultCount = 0;
386        Iterator iter = getPersistenceBrokerTemplate().getReportQueryIteratorByQuery(reportQuery);
387        while (iter.hasNext()) {
388            final Object[] results = (Object[]) iter.next();
389            resultCount = (results[0] instanceof Number) ? ((Number) results[0]).intValue() : new Integer(results[0].toString()).intValue();
390        }
391        return resultCount > 0;
392    }
393
394
395    /**
396     * @see org.kuali.ole.coa.dataaccess.AccountDao#isPrincipalInAnyWayShapeOrFormAccountSupervisor(java.lang.String)
397     */
398    public boolean isPrincipalInAnyWayShapeOrFormAccountSupervisor(String principalId) {
399        return queryPrincipalHasAccountRole(principalId, "accountsSupervisorySystemsIdentifier");
400    }
401
402    /**
403     * @see org.kuali.ole.coa.dataaccess.AccountDao#isPrincipalInAnyWayShapeOrFormFiscalOfficer(java.lang.String)
404     */
405    public boolean isPrincipalInAnyWayShapeOrFormFiscalOfficer(String principalId) {
406        return queryPrincipalHasAccountRole(principalId, "accountFiscalOfficerSystemIdentifier");
407    }
408
409    /**
410     * @see org.kuali.ole.coa.dataaccess.AccountDao#getAccountsForAccountNumber(java.lang.String)
411     */
412    public Collection<Account> getAccountsForAccountNumber(String accountNumber) {
413        Criteria criteria = new Criteria();
414        criteria.addEqualTo(OLEPropertyConstants.ACCOUNT_NUMBER, accountNumber);
415
416        return getPersistenceBrokerTemplate().getCollectionByQuery(QueryFactory.newQuery(Account.class, criteria));
417    }
418}