TransferDAOImpl.java

/*
    This file is part of Cyclos (www.cyclos.org).
    A project of the Social Trade Organisation (www.socialtrade.org).

    Cyclos is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation; either version 2 of the License, or
    (at your option) any later version.

    Cyclos is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with Cyclos; if not, write to the Free Software
    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA

 */
package nl.strohalm.cyclos.dao.accounts.transactions;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import nl.strohalm.cyclos.dao.BaseDAOImpl;
import nl.strohalm.cyclos.dao.accounts.AccountDAO;
import nl.strohalm.cyclos.entities.Relationship;
import nl.strohalm.cyclos.entities.accounts.Account;
import nl.strohalm.cyclos.entities.accounts.AccountQuery;
import nl.strohalm.cyclos.entities.accounts.Currency;
import nl.strohalm.cyclos.entities.accounts.Rated;
import nl.strohalm.cyclos.entities.accounts.external.ExternalTransfer;
import nl.strohalm.cyclos.entities.accounts.transactions.AuthorizationLevel;
import nl.strohalm.cyclos.entities.accounts.transactions.AuthorizationLevel.Authorizer;
import nl.strohalm.cyclos.entities.accounts.transactions.Payment;
import nl.strohalm.cyclos.entities.accounts.transactions.PaymentFilter;
import nl.strohalm.cyclos.entities.accounts.transactions.Transfer;
import nl.strohalm.cyclos.entities.accounts.transactions.TransferQuery;
import nl.strohalm.cyclos.entities.accounts.transactions.TransferType;
import nl.strohalm.cyclos.entities.accounts.transactions.TransfersAwaitingAuthorizationQuery;
import nl.strohalm.cyclos.entities.exceptions.DaoException;
import nl.strohalm.cyclos.entities.groups.GroupFilter;
import nl.strohalm.cyclos.entities.groups.MemberGroup;
import nl.strohalm.cyclos.entities.members.Administrator;
import nl.strohalm.cyclos.entities.members.Element;
import nl.strohalm.cyclos.entities.members.Member;
import nl.strohalm.cyclos.entities.members.Operator;
import nl.strohalm.cyclos.entities.reports.StatisticalDTO;
import nl.strohalm.cyclos.services.stats.general.KeyDevelopmentsStatsPerMonthVO;
import nl.strohalm.cyclos.utils.BigDecimalHelper;
import nl.strohalm.cyclos.utils.Pair;
import nl.strohalm.cyclos.utils.Period;
import nl.strohalm.cyclos.utils.hibernate.HibernateCustomFieldHandler;
import nl.strohalm.cyclos.utils.hibernate.HibernateHelper;
import nl.strohalm.cyclos.utils.query.PageParameters;
import nl.strohalm.cyclos.utils.query.QueryParameters.ResultType;
import nl.strohalm.cyclos.utils.statistics.ListOperations;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.ArrayUtils;

/**
 * Implementation class for transfer DAO
 * @author rafael, Jefferson Magno, rinke
 */
public class TransferDAOImpl extends BaseDAOImpl<Transfer> implements TransferDAO {

    private AccountDAO                  accountDao;
    private HibernateCustomFieldHandler hibernateCustomFieldHandler;

    public TransferDAOImpl() {
        super(Transfer.class);
    }

    @Override
    public BigDecimal balanceDiff(final Account account, final Period period) {
        if (account == null) {
            return BigDecimal.ZERO;
        }
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("account", account.getId());
        StringBuilder hql = new StringBuilder();
        hql.append(" select sum( ");
        hql.append("     case when t.chargebackOf.id is null then ");
        hql.append("         case when t.from.id = :account then -t.amount else t.amount end ");
        hql.append("     else ");
        hql.append("         case when t.to.id = :account then t.amount else -t.amount end ");
        hql.append("     end)");
        hql.append(" from Transfer t ");
        hql.append(" where (t.from.id = :account or t.to.id = :account) ");
        hql.append("   and t.processDate is not null ");
        HibernateHelper.addPeriodParameterToQuery(hql, params, "t.processDate", period);
        BigDecimal diff = (BigDecimal) uniqueResult(hql.toString(), params);
        return BigDecimalHelper.nvl(diff);
    }

    @Override
    public BigDecimal balanceDiff(final Account account, Period period, final Transfer transfer) {
        if (account == null) {
            return BigDecimal.ZERO;
        }
        period = period.clone();
        period.setEnd(null);
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("account", account.getId());
        StringBuilder hql = new StringBuilder();
        hql.append(" select sum( ");
        hql.append("     case when t.chargebackOf.id is null then ");
        hql.append("         case when t.from.id = :account then -t.amount else t.amount end ");
        hql.append("     else ");
        hql.append("         case when t.to.id = :account then t.amount else -t.amount end ");
        hql.append("     end)");
        hql.append(" from Transfer t ");
        hql.append(" where (t.from.id = :account or t.to.id = :account) ");
        hql.append("   and t.processDate is not null ");
        if (period != null && period.getBegin() != null) {
            hql.append("   and (t.processDate >");
            if (period.isInclusiveBegin()) {
                hql.append("=");
            }
            hql.append(" :beginDate ) ");
            params.put("beginDate", period.getBegin());
        }
        params.put("endDate", transfer.getProcessDate());
        params.put("transferId", transfer.getId());
        hql.append("   and (t.processDate < :endDate or (t.processDate = :endDate and t.id <");
        if (period != null && period.isInclusiveEnd()) {
            hql.append("=");
        }
        hql.append(" :transferId) ) ");
        BigDecimal diff = (BigDecimal) uniqueResult(hql.toString().trim(), params);
        return BigDecimalHelper.nvl(diff);
    }

    @Override
    public BigDecimal getChargebackBalance(final Account account, final Period period) {
        if (account == null) {
            return BigDecimal.ZERO;
        }
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("account", account.getId());
        StringBuilder hql = new StringBuilder();
        hql.append(" select sum(case when t.from.id = :account then t.amount else -t.amount end) ");
        hql.append(" from Transfer t ");
        hql.append(" where (t.from.id = :account or t.to.id = :account) ");
        hql.append(" and (t.chargedBackBy is not null or t.chargebackOf is not null) ");
        hql.append("   and t.processDate is not null ");
        HibernateHelper.addPeriodParameterToQuery(hql, params, "t.processDate", period);
        BigDecimal diff = (BigDecimal) uniqueResult(hql.toString(), params);
        return BigDecimalHelper.nvl(diff);
    }

    @Override
    public BigDecimal getChargebackBalance(final Account account, final Transfer transfer, final boolean inclusive) {
        if (account == null) {
            return BigDecimal.ZERO;
        }
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("account", account.getId());
        StringBuilder hql = new StringBuilder();
        hql.append(" select sum(case when t.from.id = :account then t.amount else -t.amount end) ");
        hql.append(" from Transfer t ");
        hql.append(" where (t.from.id = :account or t.to.id = :account) ");
        hql.append(" and (t.chargedBackBy is not null or t.chargebackOf is not null) ");
        hql.append("   and t.processDate is not null ");
        params.put("date", transfer.getProcessDate());
        params.put("transferId", transfer.getId());
        hql.append("   and (t.processDate < :date or (t.processDate = :date and t.id <");
        if (inclusive) {
            hql.append("=");
        }
        hql.append(" :transferId) ) ");
        BigDecimal diff = (BigDecimal) uniqueResult(hql.toString(), params);
        return BigDecimalHelper.nvl(diff);
    }

    // Used by Activity: all using gross product.
    // TODO statistics: all statistics queries must included processDate is not null
    @Override
    public List<Pair<Member, BigDecimal>> getGrossProductPerMember(final StatisticalDTO dto) {
        final Map<String, Object> namedParameters = new HashMap<String, Object>();
        final StringBuilder hql = new StringBuilder("select new " + Pair.class.getName());
        hql.append("(m, sum(t.amount)) from Transfer t, Member m where 1=1 ");
        hql.append(" and t.chargedBackBy is null and t.chargebackOf is null ");
        hql.append(" and exists (select ma.id from MemberAccount ma where t.to = ma and m = ma.member) ");
        appendGroupAndPaymentFilterAndPeriod(hql, namedParameters, dto);
        // Group by the member that received the transfer
        hql.append(" group by m ");

        // Order by the sum of amounts
        hql.append(" order by sum(t.amount) desc ");

        return list(hql.toString(), namedParameters);
    }

    // performance tested 2010: 24 secs
    @Override
    public List<KeyDevelopmentsStatsPerMonthVO> getGrossProductPerMonth(final StatisticalDTO dto) {
        final Map<String, Object> namedParameters = new HashMap<String, Object>();
        final StringBuilder hql = new StringBuilder();
        hql.append(" select new " + KeyDevelopmentsStatsPerMonthVO.class.getName());
        hql.append(" (sum(t.amount), month(t.processDate), year(t.processDate)) ");
        hql.append(" from Transfer t, Member m where 1=1 ");
        hql.append(" and t.chargedBackBy is null and t.chargebackOf is null ");
        hql.append(" and exists (select ma.id from MemberAccount ma where t.to = ma and m = ma.member) ");
        appendGroupAndPaymentFilterAndPeriod(hql, namedParameters, dto);
        hql.append(" group by month(t.processDate), year(t.processDate) ");
        hql.append(" order by year(t.processDate), month(t.processDate) ");

        final List<KeyDevelopmentsStatsPerMonthVO> list = list(hql.toString(), namedParameters);
        return list;
    }

    // Used by Activity Stats > all using number of trans, % not trading
    @Override
    public List<Pair<Member, Integer>> getNumberOfTransactionsPerMember(final StatisticalDTO dto) {
        final Map<String, Object> namedParameters = new HashMap<String, Object>();
        final StringBuilder hql = new StringBuilder();
        hql.append("select new " + Pair.class.getName() + "(m, count(t.id))");
        hql.append(" from Transfer t, Member m where 1=1 ");
        hql.append(" and t.chargedBackBy is null and t.chargebackOf is null ");
        // transaction to
        hql.append(" and (exists (select ma.id from MemberAccount ma where t.to = ma and m = ma.member) ");
        // transaction from (added by Rinke, because eventually this seemed more logical)
        hql.append(" or exists (select ma.id from MemberAccount ma where t.from = ma and m = ma.member)) ");
        appendGroupAndPaymentFilterAndPeriod(hql, namedParameters, dto);

        // Group by the member that received the transfer
        hql.append(" group by m ");

        // Order by the sum of amounts
        hql.append(" order by count(t.id) desc ");

        return list(hql.toString(), namedParameters);
    }

    @Override
    public List<KeyDevelopmentsStatsPerMonthVO> getNumberOfTransactionsPerMonth(final StatisticalDTO dto) {
        final Map<String, Object> namedParameters = new HashMap<String, Object>();
        final StringBuilder hql = new StringBuilder();
        hql.append(" select new " + KeyDevelopmentsStatsPerMonthVO.class.getName());
        hql.append(" (count(distinct t.id), month(t.processDate), year(t.processDate)) ");
        hql.append(" from Transfer t, Member m where 1=1 ");
        hql.append(" and t.chargedBackBy is null and t.chargebackOf is null ");
        hql.append(" and (exists (select ma.id from MemberAccount ma where t.to = ma and m = ma.member) ");
        hql.append(" or exists (select ma.id from MemberAccount ma where t.from = ma and m = ma.member)) ");
        appendGroupAndPaymentFilterAndPeriod(hql, namedParameters, dto);
        hql.append(" group by month(t.processDate), year(t.processDate) ");
        hql.append(" order by year(t.processDate), month(t.processDate) ");

        final List<KeyDevelopmentsStatsPerMonthVO> list = list(hql.toString(), namedParameters);
        return list;
    }

    @Override
    public Calendar getOldestTransfer(final Currency currency, final Account account, final Period period, final boolean excludeChargebacks) {
        final StringBuilder hql = new StringBuilder();
        final Map<String, Object> namedParameters = new HashMap<String, Object>();
        hql.append(" select min(t.processDate) from Transfer t  ");
        hql.append(" where 1=1 ");
        if (currency != null) {
            namedParameters.put("currency", currency);
            hql.append(" and t.to.type.currency = :currency ");
        }
        if (account != null) {
            namedParameters.put("account", account);
            hql.append(" and (t.from = :account or t.to = :account) ");
        }
        if (excludeChargebacks) {
            hql.append(" and t.chargedBackBy is null and t.chargebackOf is null ");
        }
        HibernateHelper.addPeriodParameterToQuery(hql, namedParameters, "t.processDate", period);
        hql.append(" order by t.processDate, t.id");
        return (Calendar) uniqueResult(hql.toString(), namedParameters);
    }

    @Override
    public List<Pair<Member, BigDecimal>> getPaymentsPerMember(final StatisticalDTO dto) throws DaoException {
        // change later on to its own implementation (it should sum outgoing transfers, not incoming as in gross product)
        return getGrossProductPerMember(dto);
    }

    @Override
    public BigDecimal getSumOfTransactions(final StatisticalDTO dto) {
        final Map<String, Object> namedParameters = new HashMap<String, Object>();
        // the query uses the same as getGrossProductPerMember, because mysql appears to be much faster with this query than with a simple select
        // sum(t.amount) without "group by".
        final StringBuilder hql = new StringBuilder("select new " + Pair.class.getName());
        hql.append("(m, sum(t.amount)) from Transfer t, Member m where 1=1 ");
        hql.append(" and t.chargedBackBy is null and t.chargebackOf is null ");
        hql.append(" and exists (select ma.id from MemberAccount ma where t.to = ma and m = ma.member) ");
        appendGroupAndPaymentFilterAndPeriod(hql, namedParameters, dto);
        // TransferType
        if (dto.getTransferType() != null) {
            hql.append(" and t.type = :transferType ");
            namedParameters.put("transferType", dto.getTransferType());
        }
        hql.append(" group by m ");

        final List<Pair<Member, BigDecimal>> sums = list(hql.toString(), namedParameters);
        BigDecimal sumOfTransactions = BigDecimal.ZERO;
        for (final Pair<Member, BigDecimal> item : sums) {
            sumOfTransactions = sumOfTransactions.add(item.getSecond());
        }
        return sumOfTransactions;
    }

    // TEST PERFORMANCE
    @Override
    public BigDecimal getSumOfTransactionsRest(final TransferQuery query) {
        final StringBuilder hql = new StringBuilder("select sum(t.amount) from Transfer t where 1=1 ");
        hql.append(" and t.chargedBackBy is null and t.chargebackOf is null ");

        final Map<String, Object> namedParameters = new HashMap<String, Object>();

        // Period
        HibernateHelper.addPeriodParameterToQuery(hql, namedParameters, "t.processDate", query.getPeriod());

        // From AccountType
        if (query.getFromAccountType() != null) {
            hql.append(" and t.from.type = :fromAccountType");
            namedParameters.put("fromAccountType", query.getFromAccountType());
        }

        // To AccountType
        if (query.getToAccountType() != null) {
            hql.append(" and t.to.type = :toAccountType");
            namedParameters.put("toAccountType", query.getToAccountType());
        }

        // TransferType not in the collection of payment filters
        // (that is the meaning of the word 'rest' on the name of the method)
        final Collection<PaymentFilter> paymentFilters = query.getPaymentFilters();
        if (paymentFilters != null && !CollectionUtils.isEmpty(paymentFilters)) {
            final Set<TransferType> transferTypesSet = new HashSet<TransferType>();
            for (final PaymentFilter paymentFilter : paymentFilters) {
                transferTypesSet.addAll(paymentFilter.getTransferTypes());
            }
            hql.append(" and t.type not in (:transferTypes) ");
            namedParameters.put("transferTypes", transferTypesSet);
        }

        final BigDecimal sumOfTransactions = uniqueResult(hql.toString(), namedParameters);
        if (sumOfTransactions == null) {
            return BigDecimal.ZERO;
        } else {
            return sumOfTransactions;
        }
    }

    // used by key dev: transaction amounts and number of transactions.
    @Override
    public List<Number> getTransactionAmounts(final StatisticalDTO dto) {
        final Map<String, Object> namedParameters = new HashMap<String, Object>();
        final StringBuilder hql = new StringBuilder();
        hql.append("select new " + Pair.class.getName() + "(t.id, t.amount)");
        hql.append(" from Transfer t, Member m where 1=1 ");
        hql.append(" and t.chargedBackBy is null and t.chargebackOf is null ");
        hql.append(" and (exists (select ma.id from MemberAccount ma where t.to = ma and m = ma.member) ");
        hql.append(" or exists (select ma.id from MemberAccount ma where t.from = ma and m = ma.member)) ");
        appendGroupAndPaymentFilterAndPeriod(hql, namedParameters, dto);
        final List<Pair<Long, BigDecimal>> pairList = list(hql.toString(), namedParameters);
        // because using to and from's, duplicate id's may appear. Transferring to Set solves this.
        final Set<Pair<Long, BigDecimal>> pairSet = new HashSet<Pair<Long, BigDecimal>>(pairList);
        return ListOperations.getSecondNumberFromPairCollection(pairSet);
    }

    @Override
    public BigDecimal getTransactionedAmountAt(final Calendar date, final Account account, final TransferType transferType) {
        return getTransactionedAmountAt(date, null, account, transferType);
    }

    @Override
    public BigDecimal getTransactionedAmountAt(Calendar date, final Operator operator, final Account account, final TransferType transferType) {
        if (date == null) {
            date = Calendar.getInstance();
        }
        final Map<String, Object> namedParameters = new HashMap<String, Object>();
        StringBuilder hql = new StringBuilder("select sum(t.amount) from Transfer t where 1=1 ");
        HibernateHelper.addInParameterToQuery(hql, namedParameters, "t.status", Payment.Status.PROCESSED, Payment.Status.PENDING, Payment.Status.SCHEDULED);
        HibernateHelper.addParameterToQuery(hql, namedParameters, "t.from", account);
        HibernateHelper.addParameterToQuery(hql, namedParameters, "t.type", transferType);
        HibernateHelper.addParameterToQuery(hql, namedParameters, "t.by", operator);
        HibernateHelper.addPeriodParameterToQuery(hql, namedParameters, "ifnull(t.processDate, t.date)", Period.day(date));
        BigDecimal sum = uniqueResult(hql.toString(), namedParameters);
        return BigDecimalHelper.nvl(sum);
    }

    @Override
    public List<Transfer> getTransfers(final Currency currency, final Account account, final Period period, final Transfer sinceTransfer, final boolean includeChargebacks, final Integer maxResults) {
        final StringBuilder hql = new StringBuilder();
        if (sinceTransfer != null && sinceTransfer.getProcessDate() == null) {
            throw new IllegalArgumentException("transfer must be processed");
        }
        final Map<String, Object> namedParameters = new HashMap<String, Object>();
        hql.append(" from Transfer t  ");
        hql.append(" where 1=1 ");
        hql.append(" and t.processDate is not null ");
        if (currency != null) {
            namedParameters.put("currency", currency);
            hql.append(" and t.to.type.currency = :currency ");
        }
        if (account != null) {
            namedParameters.put("account", account);
            hql.append(" and (t.from = :account or t.to = :account) ");
        }
        HibernateHelper.addPeriodParameterToQuery(hql, namedParameters, "t.processDate", period);
        if (sinceTransfer != null) {
            namedParameters.put("id", sinceTransfer.getId());
            namedParameters.put("startDate", sinceTransfer.getProcessDate());
            hql.append(" and ( (t.processDate > :startDate) or ( (t.processDate = :startDate) and (t.id > :id) ) ) ");
        }
        if (!includeChargebacks) {
            hql.append(" and t.chargedBackBy is null and t.chargebackOf is null ");
        }
        hql.append(" order by t.processDate, t.id");

        List<Transfer> transfers;
        if (maxResults != null) {
            transfers = list(ResultType.LIST, hql.toString(), namedParameters, PageParameters.max(maxResults));
        } else {
            transfers = list(hql.toString(), namedParameters);
        }

        return transfers;
    }

    @Override
    public boolean hasTransfers(final Account account) {
        Map<String, Account> params = Collections.singletonMap("account", account);
        PageParameters pageParameters = PageParameters.max(1);
        String hql = "select t.id from Transfer t where t.from = :account or t.to = :account";
        List<?> list = list(ResultType.LIST, hql, params, pageParameters);
        return !list.isEmpty();
    }

    @Override
    public Transfer loadTransferByTraceNumber(final String traceNumber, final Long clientId, final Relationship... fetch) {
        final Map<String, Object> namedParameters = new HashMap<String, Object>();

        List<Relationship> toFetch = ArrayUtils.isEmpty(fetch) ? null : Arrays.asList(fetch);
        final StringBuilder hql = HibernateHelper.getInitialQuery(getEntityType(), "t", toFetch);
        HibernateHelper.addParameterToQuery(hql, namedParameters, "traceNumber", traceNumber);
        HibernateHelper.addParameterToQuery(hql, namedParameters, "clientId", clientId);
        return uniqueResult(hql.toString(), namedParameters);
    }

    @Override
    public List<SimpleTransferVO> paymentVOs(final Account account, final Period period) throws DaoException {
        final StringBuilder hql = new StringBuilder();
        final Map<String, Object> namedParameters = new HashMap<String, Object>();
        namedParameters.put("account", account);
        hql.append("select new ").append(SimpleTransferVO.class.getName()).append("(t.date, case t.from when :account then -t.amount else t.amount end)");
        hql.append(" from ").append(getEntityType().getName()).append(" t");
        hql.append(" where (t.from = :account or t.to = :account) ");
        HibernateHelper.addPeriodParameterToQuery(hql, namedParameters, "t.date", period);
        hql.append(" order by t.date");

        return list(hql.toString(), namedParameters);
    }

    @Override
    public List<Transfer> search(final TransferQuery query) {
        final Map<String, Object> namedParameters = new HashMap<String, Object>();
        final StringBuilder hql = new StringBuilder();
        hql.append(" select t");
        hql.append(" from Loan l right join l.transfer t ");
        hibernateCustomFieldHandler.appendJoins(hql, "t.customValues", query.getCustomValues());
        HibernateHelper.appendJoinFetch(hql, Transfer.class, "t", query.getFetch());
        hql.append(" where 1=1");

        if (!buildSearchQuery(query, hql, namedParameters)) {
            return Collections.emptyList();
        }

        return list(query, hql.toString(), namedParameters);
    }

    @Override
    public List<Transfer> searchTransfersAwaitingAuthorization(final TransfersAwaitingAuthorizationQuery query) {
        final Map<String, Object> namedParameters = new HashMap<String, Object>();
        final StringBuilder hql = new StringBuilder();
        hql.append(" select t from Transfer t join t.nextAuthorizationLevel l ");
        HibernateHelper.appendJoinFetch(hql, getEntityType(), "t", query.getFetch());
        hql.append(" where 1=1");

        Element authorizer = query.getAuthorizer();
        if (authorizer == null) {
            return Collections.emptyList();
        }

        // Set common parameters
        authorizer = getFetchDao().fetch(authorizer, Element.Relationships.GROUP);
        for (final Authorizer auth : Authorizer.values()) {
            namedParameters.put(auth.name(), auth);
        }
        namedParameters.put("authorizer", authorizer);

        // The payment must be top-level
        hql.append(" and t.parent is null");

        // Status is PENDING and process date is null
        hql.append(" and t.processDate is null and t.status = :status ");
        namedParameters.put("status", Payment.Status.PENDING);

        // Filter by authorizer
        if (authorizer instanceof Administrator) {
            final Administrator administrator = (Administrator) authorizer;
            hql.append(" and l.authorizer in (:ADMIN, :BROKER)");
            hql.append(" and :adminGroup in elements(l.adminGroups)");
            namedParameters.put("adminGroup", administrator.getAdminGroup());
        } else if (authorizer instanceof Operator) {
            hql.append(" and ((l.authorizer = :RECEIVER and exists (");
            hql.append(" select ma.id from MemberAccount ma, Operator o where ma = t.to and o.member = ma.member and o = :authorizer");
            hql.append(" )) or (l.authorizer = :PAYER and exists (");
            hql.append(" select ma.id from MemberAccount ma, Operator o where ma = t.from and o.member = ma.member and o = :authorizer");
            hql.append(" ))) ");
        } else {
            hql.append(" and ((l.authorizer = :BROKER and exists(");
            hql.append(" select ma.id from MemberAccount ma where ma = t.from and ma.member.broker = :authorizer");
            hql.append(" )) or (l.authorizer = :RECEIVER and exists (");
            hql.append(" select ma.id from MemberAccount ma where ma = t.to and ma.member = :authorizer");
            hql.append(" )) or (l.authorizer = :PAYER and exists (");
            hql.append(" select ma.id from MemberAccount ma where ma = t.from and ma.member = :authorizer");
            hql.append(")))");
        }

        // Ensures that when the authorizer has already authorized once, the same transfer is not returned
        hql.append(" and not exists (select a.id from TransferAuthorization a where a.transfer = t and a.by = :authorizer)");

        // Add the from member
        final Member member = query.getMember();
        if (member != null) {
            hql.append(" and exists (select ma.id from MemberAccount ma where ma.member = :member and (ma = t.from or ma = t.to))");
            namedParameters.put("member", member);
        }

        // Add the payment filter
        PaymentFilter paymentFilter = query.getPaymentFilter();
        if (paymentFilter != null) {
            paymentFilter = getFetchDao().fetch(paymentFilter, PaymentFilter.Relationships.TRANSFER_TYPES);
            HibernateHelper.addInParameterToQuery(hql, namedParameters, "t.type", paymentFilter.getTransferTypes());
        }

        // Add the other filters
        HibernateHelper.addPeriodParameterToQuery(hql, namedParameters, "t.date", query.getPeriod());
        HibernateHelper.addParameterToQuery(hql, namedParameters, "t.type", query.getTransferType());
        HibernateHelper.addLikeParameterToQuery(hql, namedParameters, "t.transactionNumber", query.getTransactionNumber());
        HibernateHelper.appendOrder(hql, "t.date desc");

        return list(query, hql.toString(), namedParameters);
    }

    public void setAccountDao(final AccountDAO accountDao) {
        this.accountDao = accountDao;
    }

    public void setHibernateCustomFieldHandler(final HibernateCustomFieldHandler hibernateCustomFieldHandler) {
        this.hibernateCustomFieldHandler = hibernateCustomFieldHandler;
    }

    @Override
    public Transfer updateAuthorizationData(final Long id, final Transfer.Status status, final AuthorizationLevel nextAuthorizationLevel, final Calendar processDate, final Rated rates) {
        final Transfer transfer = load(id);
        transfer.setStatus(status);
        transfer.setNextAuthorizationLevel(nextAuthorizationLevel);
        transfer.setProcessDate(processDate);
        if (rates != null) {
            // if rates are set, the processDate may not be null
            if ((rates.getEmissionDate() != null || rates.getExpirationDate() != null) && transfer.getProcessDate() == null) {
                throw new IllegalArgumentException("rates can only be set if processDate on the transfer is NOT null. ");
            }
            transfer.setEmissionDate(rates.getEmissionDate());
            transfer.setExpirationDate(rates.getExpirationDate());
            transfer.setiRate(rates.getiRate());
        }
        return update(transfer);
    }

    @Override
    public Transfer updateChargeBack(final Transfer transfer, final Transfer chargeback) {
        transfer.setChargedBackBy(chargeback);
        return update(transfer);
    }

    @Override
    public Transfer updateExternalTransfer(final Long id, final ExternalTransfer externalTransfer) {
        final Transfer transfer = load(id);
        transfer.setExternalTransfer(externalTransfer);
        return update(transfer);
    }

    @Override
    public Transfer updateStatus(final Long id, final Payment.Status status) {
        final Transfer transfer = load(id);
        transfer.setStatus(status);
        if (status != Payment.Status.PROCESSED) {
            transfer.setProcessDate(null);
        }
        return update(transfer);
    }

    @Override
    public Transfer updateTransactionNumber(final Long id, final String transactionNumber) {
        final Transfer transfer = load(id);
        transfer.setTransactionNumber(transactionNumber);
        return update(transfer);
    }

    /**
     * convenience method for a very often repeated block of hql query append statements. It appends the group filter, payment filter and period to a
     * hql StringBuilder object for a query.
     * 
     * @param hql
     * @param namedParameters
     * @param dto
     */
    private void appendGroupAndPaymentFilterAndPeriod(final StringBuilder hql, final Map<String, Object> namedParameters, final StatisticalDTO dto) {
        // Period
        HibernateHelper.addPeriodParameterToQuery(hql, namedParameters, "t.processDate", dto.getPeriod());
        // PaymentFilter
        if (dto.getPaymentFilter() != null) {
            hql.append(" and exists (select 1 from " + PaymentFilter.class.getName() + " pf where pf = :filter and t.type in elements(pf.transferTypes)) ");
            namedParameters.put("filter", dto.getPaymentFilter());
        }
        // Members groups
        if (!CollectionUtils.isEmpty(dto.getGroups())) {
            hql.append(" and exists ");
            hql.append("    ( select ghl.id ");
            hql.append("      from GroupHistoryLog ghl ");
            hql.append("      where ghl.element = m ");
            hql.append("      and ghl.group in (:groups) ");
            hql.append("      and ghl.period.begin < :end ");
            hql.append("      and (ghl.period.end is null or ghl.period.end >= :begin) ");
            hql.append("      and t.processDate between ghl.period.begin and ifnull(ghl.period.end, t.processDate) ");
            hql.append("    ) ");
            namedParameters.put("groups", dto.getGroups());
            namedParameters.put("begin", dto.getPeriod().getBegin());
            namedParameters.put("end", dto.getPeriod().getEnd());
        }
    }

    @SuppressWarnings("unchecked")
    private boolean buildSearchQuery(final TransferQuery query, final StringBuilder hql, final Map<String, Object> namedParameters) {
        // hql.append(" and not exists (select pas.id from PendingAccountStatus pas where pas.transfer = t)");
        HibernateHelper.addParameterToQuery(hql, namedParameters, "t.type.requiresAuthorization", query.getRequiresAuthorization());
        HibernateHelper.addParameterToQuery(hql, namedParameters, "t.status", query.getStatus());
        HibernateHelper.addLikeParameterToQuery(hql, namedParameters, "t.description", query.getDescription());
        HibernateHelper.addLikeParameterToQuery(hql, namedParameters, "t.transactionNumber", query.getTransactionNumber());
        HibernateHelper.addParameterToQuery(hql, namedParameters, "t.loanPayment", query.getLoanPayment());
        HibernateHelper.addParameterToQuery(hql, namedParameters, "t.parent", query.getParent());
        HibernateHelper.addParameterToQuery(hql, namedParameters, "t.type", query.getTransferType());
        HibernateHelper.addPeriodParameterToQuery(hql, namedParameters, "ifnull(t.processDate, t.date)", query.getPeriod());
        if (query.isRootOnly()) {
            hql.append(" and t.parent is null");
        }
        if (query.getLoanTransfer() != null) {
            if (query.getLoanTransfer()) {
                hql.append(" and l is not null");
            } else {
                hql.append(" and l is null");
            }
        }

        // By conciliation status
        if (query.getConciliated() != null) {
            hql.append(" and t.externalTransfer is " + (query.getConciliated() ? "not" : "") + " null");
        }

        // By owner
        if (query.getOwner() != null) {
            // Load the account
            Collection<Account> accounts;
            if (query.getType() == null) {
                AccountQuery aq = new AccountQuery();
                aq.setOwner(query.getOwner());
                accounts = (Collection<Account>) accountDao.search(aq);
            } else {
                final Account account = accountDao.load(query.getOwner(), query.getType());
                accounts = Collections.singleton(account);
            }
            namedParameters.put("accounts", accounts);

            if (query.getMember() != null) {
                // Load the related member accounts
                final AccountQuery otherAccountsQuery = new AccountQuery();
                otherAccountsQuery.setOwner(query.getMember());
                final List<? extends Account> otherAccounts = accountDao.search(otherAccountsQuery);
                if (otherAccounts.isEmpty()) {
                    // No accounts - ensure nothing will be returned
                    return false;
                } else {
                    hql.append(" and ((t.from in (:accounts) and t.to in (:relatedAccounts)) or (t.to in (:accounts) and t.from in (:relatedAccounts)))");
                    namedParameters.put("relatedAccounts", otherAccounts);
                }
            } else {
                hql.append(" and (t.from in (:accounts) or t.to in (:accounts))");
            }

            // Use the groups / group filters
            Collection<MemberGroup> groups = new HashSet<MemberGroup>();
            if (CollectionUtils.isNotEmpty(query.getGroupFilters())) {
                // Get the groups from group filters
                for (GroupFilter groupFilter : query.getGroupFilters()) {
                    if (groupFilter != null && groupFilter.isPersistent()) {
                        groupFilter = getFetchDao().fetch(groupFilter, GroupFilter.Relationships.GROUPS);
                        groups.addAll(groupFilter.getGroups());
                    }
                }
            }
            if (CollectionUtils.isNotEmpty(query.getGroups())) {
                // Specific groups
                if (!groups.isEmpty()) {
                    // No group filters: use group alone
                    groups.retainAll(query.getGroups());
                } else {
                    // Filter the groups from group filters with the specified groups
                    groups = query.getGroups();
                }
            }

            if (!groups.isEmpty()) {
                hql.append(" and ((t.to in (:accounts) and exists (select ma.id from MemberAccount ma where ma = t.from and ma.member.group in (:groups)))");
                hql.append("   or (t.from in (:accounts) and exists (select ma.id from MemberAccount ma where ma = t.to and ma.member.group in (:groups))))");
                namedParameters.put("groups", groups);
            }
        }

        // From account owner
        if (query.getFromAccountOwner() != null) {
            final AccountQuery accountQuery = new AccountQuery();
            accountQuery.setOwner(query.getFromAccountOwner());
            final List<? extends Account> fromAccounts = accountDao.search(accountQuery);
            hql.append(" and t.from in (:fromAccounts) ");
            namedParameters.put("fromAccounts", fromAccounts);
        }

        // To account owner
        if (query.getToAccountOwner() != null) {
            final AccountQuery accountQuery = new AccountQuery();
            accountQuery.setOwner(query.getToAccountOwner());
            final List<? extends Account> toAccounts = accountDao.search(accountQuery);
            hql.append(" and t.to in (:toAccounts) ");
            namedParameters.put("toAccounts", toAccounts);
        }

        // PaymentFilter
        final Collection<PaymentFilter> paymentFilters = query.getPaymentFilters();
        if (CollectionUtils.isNotEmpty(paymentFilters)) {
            // Get all TTs from all those payment filters
            final String ttHql = "from TransferType tt where exists ("
                    + " select 1"
                    + " from PaymentFilter pf"
                    + " where pf in (:pfs)"
                    + " and tt in elements(pf.transferTypes))";
            final List<TransferType> transferTypes = list(ttHql, Collections.singletonMap("pfs", paymentFilters));

            HibernateHelper.addInParameterToQuery(hql, namedParameters, "t.type", transferTypes);
        }

        if (query.getExcludeTransferType() != null) {
            hql.append(" and t.type != :excludeTransferType ");
            namedParameters.put("excludeTransferType", query.getExcludeTransferType());
        }

        // Operated by
        if (query.getBy() != null) {
            hql.append(" and (t.by = :by or t.receiver = :by)");
            namedParameters.put("by", query.getBy());
        }

        // Custom fields
        hibernateCustomFieldHandler.appendConditions(hql, namedParameters, query.getCustomValues());

        // Set the order
        if (!query.isUnordered()) {
            final List<String> orders = new ArrayList<String>();

            // Order by date ...
            String order = "ifnull(t.processDate, t.date)";
            if (query.isReverseOrder()) {
                order += " desc";
            }
            orders.add(order);

            // ... then by id, to ensure that payments in the same second are ordered correctly
            order = "t.id";
            if (query.isReverseOrder()) {
                order += " desc";
            }
            orders.add(order);

            HibernateHelper.appendOrder(hql, orders);
        }

        return true;
    }

}