Java tutorial
/* 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; import java.io.Closeable; import java.io.IOException; import java.math.BigDecimal; import java.sql.SQLException; 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.Iterator; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; import java.util.Set; import nl.strohalm.cyclos.dao.BaseDAOImpl; import nl.strohalm.cyclos.dao.JDBCCallback; import nl.strohalm.cyclos.entities.Relationship; import nl.strohalm.cyclos.entities.accounts.Account; import nl.strohalm.cyclos.entities.accounts.AccountLock; import nl.strohalm.cyclos.entities.accounts.AccountOwner; import nl.strohalm.cyclos.entities.accounts.AccountQuery; import nl.strohalm.cyclos.entities.accounts.AccountType; import nl.strohalm.cyclos.entities.accounts.MemberAccount; import nl.strohalm.cyclos.entities.accounts.MemberAccount.Action; import nl.strohalm.cyclos.entities.accounts.MemberAccountType; import nl.strohalm.cyclos.entities.accounts.SystemAccount; import nl.strohalm.cyclos.entities.accounts.SystemAccountOwner; import nl.strohalm.cyclos.entities.accounts.fees.transaction.BrokerCommission; import nl.strohalm.cyclos.entities.accounts.loans.Loan; 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.TransferType; import nl.strohalm.cyclos.entities.exceptions.DaoException; import nl.strohalm.cyclos.entities.exceptions.EntityNotFoundException; import nl.strohalm.cyclos.entities.exceptions.UnexpectedEntityException; import nl.strohalm.cyclos.entities.groups.MemberGroup; import nl.strohalm.cyclos.entities.members.Element; import nl.strohalm.cyclos.entities.members.Member; import nl.strohalm.cyclos.entities.members.MemberTransactionDetailsReportData; import nl.strohalm.cyclos.entities.members.MemberTransactionSummaryVO; import nl.strohalm.cyclos.entities.members.MembersTransactionsReportParameters; import nl.strohalm.cyclos.entities.settings.LocalSettings.MemberResultDisplay; import nl.strohalm.cyclos.services.accounts.AccountDTO; import nl.strohalm.cyclos.services.accounts.BulkUpdateAccountDTO; import nl.strohalm.cyclos.services.accounts.GetTransactionsDTO; import nl.strohalm.cyclos.services.transactions.TransactionSummaryVO; import nl.strohalm.cyclos.utils.EntityHelper; import nl.strohalm.cyclos.utils.IteratorListImpl; import nl.strohalm.cyclos.utils.JDBCWrapper; import nl.strohalm.cyclos.utils.Period; import nl.strohalm.cyclos.utils.PropertyHelper; import nl.strohalm.cyclos.utils.ScrollableResultsIterator; import nl.strohalm.cyclos.utils.conversion.Transformer; import nl.strohalm.cyclos.utils.hibernate.HibernateHelper; import nl.strohalm.cyclos.utils.hibernate.HibernateHelper.QueryParameter; import nl.strohalm.cyclos.utils.query.IteratorList; import nl.strohalm.cyclos.utils.query.PageParameters; import nl.strohalm.cyclos.utils.query.QueryParameters.ResultType; import org.apache.commons.collections.CollectionUtils; import org.apache.commons.lang.StringUtils; import org.apache.commons.lang.WordUtils; import org.hibernate.SQLQuery; import org.hibernate.ScrollMode; import org.hibernate.ScrollableResults; import org.hibernate.type.StandardBasicTypes; import org.hibernate.type.Type; /** * Implementation DAO for accounts * @author rafael, Jefferson Magno, luis */ public class AccountDAOImpl extends BaseDAOImpl<Account> implements AccountDAO { private class DiffsIterator implements Iterator<AccountDailyDifference>, Closeable { private final ScrollableResults results; private AccountDailyDifference diff; public DiffsIterator(final ScrollableResults results) { this.results = results; advance(); } @Override public void close() throws IOException { results.close(); } @Override public boolean hasNext() { return diff != null; } @Override public AccountDailyDifference next() { AccountDailyDifference result = diff; advance(); return result; } @Override public void remove() { throw new UnsupportedOperationException(); } private void advance() { if (!results.next()) { diff = null; return; } diff = new AccountDailyDifference(); diff.setDay(results.getCalendar(1)); diff.setBalance(BigDecimal.ZERO); diff.setReserved(BigDecimal.ZERO); readAmount(); // We have to try to iterate once, as there could be 2 records by day: one for balance and other for reserved boolean shouldRewind = true; if (results.next()) { Calendar day = results.getCalendar(1); if (day.equals(diff.getDay())) { shouldRewind = false; readAmount(); } } // We've peeked the next one to get the other data, but it was another record. Rewind. if (shouldRewind) { results.previous(); } } private void readAmount() { String type = results.getString(0); BigDecimal amount = results.getBigDecimal(2); if ("R".equals(type)) { diff.setReserved(amount); } else { diff.setBalance(amount); } } } private static final char[] COLUMN_DELIMITERS = new char[] { '_' }; public AccountDAOImpl() { super(Account.class); } @Override public void bulkUpdateCreditLimites(final BulkUpdateAccountDTO dto) { final Map<String, Object> namedParameters = new HashMap<String, Object>(); StringBuilder hql = new StringBuilder(); hql.append("update MemberAccount ma set "); hql.append(" ma.creditLimit = :limit, "); namedParameters.put("limit", dto.getCreditLimit()); hql.append(" ma.upperCreditLimit = :upperLimit "); namedParameters.put("upperLimit", dto.getUpperCreditLimit()); hql.append(" where ma.type = :type "); namedParameters.put("type", dto.getType()); // because joins in bulk deletes are not supported, we must do it via this tricky subQuery hql.append(" and ma.member in "); hql.append(" (from Member m where 1 = 1 "); hql.append(" and m.group = :group ) "); namedParameters.put("group", dto.getGroup()); bulkUpdate(hql.toString(), namedParameters); } @Override public int countAccounts(final MemberGroup group, final MemberAccountType accountType, final Action action) { final Map<String, Object> params = new HashMap<String, Object>(); params.put("group", group); params.put("type", accountType); params.put("action", action); StringBuilder hql = new StringBuilder(); hql.append(" select count(*) "); hql.append(" from MemberAccount ma"); hql.append(" where ma.member.group = :group "); hql.append(" and ma.type = :type "); hql.append(" and ma.action = :action "); return this.<Integer>uniqueResult(hql.toString(), params); } @Override public int delete(final boolean flush, final Long... ids) { getSession().createQuery("delete from AccountLock l where l.id in (:ids)").setParameterList("ids", ids) .executeUpdate(); return super.delete(flush, ids); }; @Override public TransactionSummaryVO getBrokerCommissions(final GetTransactionsDTO dto) throws EntityNotFoundException, DaoException { final Account account = load(dto.getOwner(), dto.getType()); final Period period = dto.getPeriod(); final StringBuilder hql = new StringBuilder(); final Map<String, Object> namedParams = new HashMap<String, Object>(); hql.append(" select count(*), sum(t.amount)"); hql.append(" from " + Transfer.class.getName() + " t, " + BrokerCommission.class.getName() + " f"); hql.append(" where t.accountFeeLog.accountFee = f "); // Here we use just one payment filter final Collection<PaymentFilter> paymentFilters = dto.getPaymentFilters(); if (CollectionUtils.isNotEmpty(paymentFilters)) { final PaymentFilter paymentFilter = paymentFilters.iterator().next(); if (paymentFilter != null) { hql.append(" and t.type in (select pf.transferTypes from " + PaymentFilter.class.getName() + " pf where pf = :pf) "); namedParams.put("pf", paymentFilter); } } hql.append(" and t.to = :account "); namedParams.put("account", account); HibernateHelper.addPeriodParameterToQuery(hql, namedParams, "ifnull(t.processDate, t.date)", period); return buildSummary(uniqueResult(hql.toString(), namedParams)); } @Override public TransactionSummaryVO getCredits(final GetTransactionsDTO dto) { return getSummary(dto, true, Transfer.Status.PROCESSED); } @Override public TransactionSummaryVO getDebits(final GetTransactionsDTO dto) { return getSummary(dto, false, Transfer.Status.PROCESSED); } @Override public TransactionSummaryVO getLoans(final GetTransactionsDTO dto) throws EntityNotFoundException, DaoException { final Account account = load(dto.getOwner(), dto.getType()); final Period period = dto.getPeriod(); final StringBuilder hql = new StringBuilder(); final Map<String, Object> namedParams = new HashMap<String, Object>(); hql.append(" select count(*), sum(t.amount)"); hql.append(" from " + Loan.class.getName() + " l join l.transfer t"); hql.append(" where t.to = :account "); // Here we use just one payment filter final Collection<PaymentFilter> paymentFilters = dto.getPaymentFilters(); if (CollectionUtils.isNotEmpty(paymentFilters)) { final PaymentFilter paymentFilter = paymentFilters.iterator().next(); if (paymentFilter != null) { hql.append(" and t.type in (select pf.transferTypes from " + PaymentFilter.class.getName() + " pf where pf = :pf) "); namedParams.put("pf", paymentFilter); } } namedParams.put("account", account); HibernateHelper.addPeriodParameterToQuery(hql, namedParams, "ifnull(t.processDate, t.date)", period); return buildSummary(uniqueResult(hql.toString(), namedParams)); } @Override public MemberAccount getNextPendingProcessing() { final StringBuilder hql = new StringBuilder(); hql.append("from MemberAccount "); hql.append(" where action is not null"); return (MemberAccount) uniqueResult(hql.toString(), null); } @Override public TransactionSummaryVO getPendingCredits(final GetTransactionsDTO dto) throws EntityNotFoundException, DaoException { return getSummary(dto, true, Transfer.Status.PENDING); } @Override public TransactionSummaryVO getPendingDebits(final GetTransactionsDTO dto) throws EntityNotFoundException, DaoException { return getSummary(dto, false, Transfer.Status.PENDING); } @Override public <T extends Account> T insert(final T entity, final boolean flush) throws UnexpectedEntityException, DaoException { final T account = super.insert(entity, false); getSession().persist(new AccountLock(account)); if (flush) { getSession().flush(); } return account; } @Override public IteratorList<AccountDailyDifference> iterateDailyDifferences(final MemberAccount account, final Period period) { Map<String, Object> params = new HashMap<String, Object>(); params.put("accountId", account.getId()); QueryParameter beginParameter = HibernateHelper.getBeginParameter(period); QueryParameter endParameter = HibernateHelper.getEndParameter(period); if (beginParameter != null) { params.put("begin", beginParameter.getValue()); } if (endParameter != null) { params.put("end", endParameter.getValue()); } StringBuilder sql = new StringBuilder(); sql.append(" select type, date(d.date) as date, sum(amount) as amount "); sql.append(" from ( "); sql.append(" select 'B' as type, t.process_date as date, "); sql.append(" case when t.chargeback_of_id is null then "); sql.append(" case when t.from_account_id = :accountId then -t.amount else t.amount end "); sql.append(" else "); sql.append(" case when t.to_account_id = :accountId then t.amount else -t.amount end "); sql.append(" end as amount "); sql.append(" from transfers t "); sql.append(" where (t.from_account_id = :accountId or t.to_account_id = :accountId) "); sql.append(" and t.process_date is not null "); if (beginParameter != null) { sql.append(" and t.process_date " + beginParameter.getOperator() + " :begin"); } if (endParameter != null) { sql.append(" and t.process_date " + endParameter.getOperator() + " :end"); } sql.append(" union "); sql.append(" select 'R', r.date, r.amount "); sql.append(" from amount_reservations r "); sql.append(" where r.account_id = :accountId "); if (beginParameter != null) { sql.append(" and r.date " + beginParameter.getOperator() + " :begin"); } if (endParameter != null) { sql.append(" and r.date " + endParameter.getOperator() + " :end"); } sql.append(" ) d "); sql.append(" group by type, date(d.date) "); sql.append(" order by date(d.date) "); SQLQuery query = getSession().createSQLQuery(sql.toString()); query.addScalar("type", StandardBasicTypes.STRING); query.addScalar("date", StandardBasicTypes.CALENDAR_DATE); query.addScalar("amount", StandardBasicTypes.BIG_DECIMAL); getHibernateQueryHandler().setQueryParameters(query, params); ScrollableResults results = query.scroll(ScrollMode.SCROLL_INSENSITIVE); return new IteratorListImpl<AccountDailyDifference>(new DiffsIterator(results)); } @Override public IteratorList<Account> iterateUnclosedAccounts(final Calendar day, final int maxResults) { Map<String, Calendar> params = Collections.singletonMap("day", day); StringBuilder hql = new StringBuilder(); hql.append(" from Account a "); hql.append(" where (last_closing_date is null or last_closing_date < :day)"); List<Account> accounts = list(ResultType.ITERATOR, hql.toString(), params, PageParameters.max(maxResults)); return (IteratorList<Account>) accounts; } @Override public Account load(final AccountOwner owner, final AccountType type, final Relationship... fetch) throws EntityNotFoundException, DaoException { Map<String, Object> params = new HashMap<String, Object>(); params.put("type", type); String hql; if (owner instanceof SystemAccountOwner) { hql = "from SystemAccount a where a.type = :type"; } else if (owner instanceof Member) { hql = "from MemberAccount a where a.member = :member and a.type = :type"; params.put("member", owner); } else { throw new EntityNotFoundException(Account.class); } Account account = uniqueResult(hql, params); if (account == null) { throw new EntityNotFoundException(Account.class); } return getFetchDao().fetch(account, fetch); } @Override public List<Account> loadAll(final List<AccountDTO> dtos, final Relationship... fetch) throws EntityNotFoundException, DaoException { final List<Account> accounts = new ArrayList<Account>(); for (final AccountDTO dto : dtos) { accounts.add(load(dto.getOwner(), dto.getType(), fetch)); } return accounts; } @Override public void markForActivation(final BulkUpdateAccountDTO dto) { runNative(new JDBCCallback() { @Override public void execute(final JDBCWrapper jdbc) throws SQLException { final StringBuilder sql = new StringBuilder(); Calendar date = Calendar.getInstance(); Long typeId = dto.getType().getId(); BigDecimal limit = dto.getCreditLimit(); BigDecimal upperLimit = dto.getUpperCreditLimit(); Long groupId = dto.getGroup().getId(); // Fist, mark for activation all accounts which where already there but are inactive if (jdbc.isHSQLDB()) { // this is because HSQLDB (e.g.: used by Cyclos Standalone) doesn't support join in update statements sql.append("update accounts a"); sql.append(" set member_action = 'A'"); sql.append(" where a.member_status = 'I'"); sql.append(" and a.member_action is null"); sql.append(" and a.type_id = ?"); sql.append(" and exists (select 1 from members m"); sql.append(" where a.member_id = m.id and"); sql.append(" m.group_id = ?)"); jdbc.execute(sql.toString(), groupId, typeId); } else { sql.append("update accounts a inner join members m on a.member_id = m.id"); sql.append(" set member_action = 'A'"); sql.append(" where a.member_status = 'I'"); sql.append(" and a.member_action is null"); sql.append(" and m.group_id = ?"); sql.append(" and a.type_id = ?"); jdbc.execute(sql.toString(), groupId, typeId); } // Then insert the missing accounts sql.setLength(0); sql.append("insert into accounts "); sql.append("(subclass, creation_date, owner_name, type_id, credit_limit, "); sql.append(" upper_credit_limit, member_id, member_status, member_action) "); sql.append(" select "); sql.append(" 'M', ?, u.username, ?, ?, ?, m.id, 'I', 'A' "); sql.append(" from members m, users u "); sql.append(" where m.id = u.id and m.group_id = ? "); sql.append(" and not exists ("); sql.append(" select 1"); sql.append(" from accounts a"); sql.append(" where a.member_id = m.id"); sql.append(" and a.type_id = ?"); sql.append(" )"); jdbc.execute(sql.toString(), date, typeId, limit, upperLimit, groupId, typeId); } }); } @Override public void markForDeactivation(final MemberAccountType type, final MemberGroup group) { final Map<String, Object> namedParameters = new HashMap<String, Object>(); StringBuilder hql = new StringBuilder(); hql.append("update MemberAccount ma set "); hql.append(" ma.action = :action "); namedParameters.put("action", MemberAccount.Action.REMOVE); hql.append(" where ma.type = :type "); namedParameters.put("type", type); // because joins in bulk deletes are not supported, we must do it via this tricky subQuery hql.append(" and ma.member in "); hql.append(" (from Member m where 1 = 1 "); hql.append(" and m.group = :group ) "); namedParameters.put("group", group); bulkUpdate(hql.toString(), namedParameters); } @Override public Iterator<MemberTransactionDetailsReportData> membersTransactionsDetailsReport( final MembersTransactionsReportParameters params) { final StringBuilder sql = new StringBuilder(); final Map<String, Object> parameters = new HashMap<String, Object>(); // Find the transfer types ids Set<Long> ttIds = null; if (CollectionUtils.isNotEmpty(params.getPaymentFilters())) { ttIds = new HashSet<Long>(); for (PaymentFilter pf : params.getPaymentFilters()) { pf = getFetchDao().fetch(pf, PaymentFilter.Relationships.TRANSFER_TYPES); final Long[] ids = EntityHelper.toIds(pf.getTransferTypes()); CollectionUtils.addAll(ttIds, ids); } } // Get the member group ids Set<Long> groupIds = null; if (CollectionUtils.isNotEmpty(params.getMemberGroups())) { groupIds = new HashSet<Long>(); CollectionUtils.addAll(groupIds, EntityHelper.toIds(params.getMemberGroups())); } // Get the period final Period period = params.getPeriod(); final QueryParameter beginParameter = HibernateHelper.getBeginParameter(period); final QueryParameter endParameter = HibernateHelper.getEndParameter(period); // Set the parameters final boolean useTT = CollectionUtils.isNotEmpty(ttIds); if (useTT) { parameters.put("ttIds", ttIds); } if (beginParameter != null) { parameters.put("beginDate", beginParameter.getValue()); } if (endParameter != null) { parameters.put("endDate", endParameter.getValue()); } parameters.put("processed", Payment.Status.PROCESSED.getValue()); // Build the sql string sql.append( " select u.username, m.name, bu.username broker_username, b.name broker_name, h.account_type_name, h.date, h.amount, h.description, h.related_username, h.related_name, h.transfer_type_name, h.transaction_number"); sql.append( " from members m inner join users u on m.id = u.id left join members b on m.member_broker_id = b.id left join users bu on b.id = bu.id,"); sql.append(" ("); if (params.isCredits()) { appendMembersTransactionsDetailsReportSqlPart(sql, useTT, beginParameter, endParameter, true, true); sql.append(" union"); appendMembersTransactionsDetailsReportSqlPart(sql, useTT, beginParameter, endParameter, true, false); if (params.isDebits()) { sql.append(" union"); } } if (params.isDebits()) { appendMembersTransactionsDetailsReportSqlPart(sql, useTT, beginParameter, endParameter, false, true); sql.append(" union"); appendMembersTransactionsDetailsReportSqlPart(sql, useTT, beginParameter, endParameter, false, false); } sql.append(" ) h"); sql.append(" where m.id = h.member_id"); if (groupIds != null) { parameters.put("groupIds", groupIds); sql.append(" and m.group_id in (:groupIds)"); } sql.append(" order by m.name, u.username, h.account_type_name, h.date desc, h.transfer_id desc"); // Prepare the query final SQLQuery query = getSession().createSQLQuery(sql.toString()); final Map<String, Type> columns = new LinkedHashMap<String, Type>(); columns.put("username", StandardBasicTypes.STRING); columns.put("name", StandardBasicTypes.STRING); columns.put("broker_username", StandardBasicTypes.STRING); columns.put("broker_name", StandardBasicTypes.STRING); columns.put("account_type_name", StandardBasicTypes.STRING); columns.put("date", StandardBasicTypes.CALENDAR); columns.put("amount", StandardBasicTypes.BIG_DECIMAL); columns.put("description", StandardBasicTypes.STRING); columns.put("related_username", StandardBasicTypes.STRING); columns.put("related_name", StandardBasicTypes.STRING); columns.put("transfer_type_name", StandardBasicTypes.STRING); columns.put("transaction_number", StandardBasicTypes.STRING); for (final Map.Entry<String, Type> entry : columns.entrySet()) { query.addScalar(entry.getKey(), entry.getValue()); } getHibernateQueryHandler().setQueryParameters(query, parameters); // Create a transformer, which will read rows as Object[] and transform them to MemberTransactionDetailsReportData final Transformer<Object[], MemberTransactionDetailsReportData> transformer = new Transformer<Object[], MemberTransactionDetailsReportData>() { @Override public MemberTransactionDetailsReportData transform(final Object[] input) { final MemberTransactionDetailsReportData data = new MemberTransactionDetailsReportData(); int i = 0; for (final Map.Entry<String, Type> entry : columns.entrySet()) { final String columnName = entry.getKey(); // Column names are transfer_type_name, property is transferTypeName String propertyName = WordUtils.capitalize(columnName, COLUMN_DELIMITERS); propertyName = Character.toLowerCase(propertyName.charAt(0)) + propertyName.substring(1); propertyName = StringUtils.replace(propertyName, "_", ""); PropertyHelper.set(data, propertyName, input[i]); i++; } return data; } }; return new ScrollableResultsIterator<MemberTransactionDetailsReportData>(query, transformer); } @Override public Iterator<MemberTransactionSummaryVO> membersTransactionSummaryReport( final Collection<MemberGroup> memberGroups, final PaymentFilter paymentFilter, final Period period, final boolean credits, final MemberResultDisplay order) { final Map<String, Object> parameters = new HashMap<String, Object>(); final StringBuilder sql = new StringBuilder(); // Get the transfer types ids final List<Long> ttIds = paymentFilter == null ? null : Arrays.asList(EntityHelper.toIds(paymentFilter.getTransferTypes())); // Get the member group ids List<Long> groupIds = null; if (CollectionUtils.isNotEmpty(memberGroups)) { groupIds = Arrays.asList(EntityHelper.toIds(memberGroups)); } // Get the period final QueryParameter beginParameter = HibernateHelper.getBeginParameter(period); final QueryParameter endParameter = HibernateHelper.getEndParameter(period); // Set the parameters final boolean useGroups = CollectionUtils.isNotEmpty(groupIds); final boolean useTT = CollectionUtils.isNotEmpty(ttIds); if (useGroups) { parameters.put("groupIds", groupIds); } if (useTT) { parameters.put("ttIds", ttIds); } if (beginParameter != null) { parameters.put("beginDate", beginParameter.getValue()); } if (endParameter != null) { parameters.put("endDate", endParameter.getValue()); } parameters.put("processed", Payment.Status.PROCESSED.getValue()); // Create the SQL query sql.append(" select member_id, sum(count) as count, sum(amount) as amount"); sql.append(" from ("); appendMembersTransactionsSummaryReportSqlPart(sql, useGroups, useTT, beginParameter, endParameter, credits, true); sql.append(" union"); appendMembersTransactionsSummaryReportSqlPart(sql, useGroups, useTT, beginParameter, endParameter, credits, false); sql.append(" ) ts"); sql.append(" group by member_id"); sql.append(" order by ").append(order == MemberResultDisplay.NAME ? "member_name, member_id" : "username"); final SQLQuery query = getSession().createSQLQuery(sql.toString()); query.addScalar("member_id", StandardBasicTypes.LONG); query.addScalar("count", StandardBasicTypes.INTEGER); query.addScalar("amount", StandardBasicTypes.BIG_DECIMAL); getHibernateQueryHandler().setQueryParameters(query, parameters); final Transformer<Object[], MemberTransactionSummaryVO> transformer = new Transformer<Object[], MemberTransactionSummaryVO>() { @Override public MemberTransactionSummaryVO transform(final Object[] input) { final MemberTransactionSummaryVO vo = new MemberTransactionSummaryVO(); vo.setMemberId((Long) input[0]); vo.setCount((Integer) input[1]); vo.setAmount((BigDecimal) input[2]); return vo; } }; return new ScrollableResultsIterator<MemberTransactionSummaryVO>(query, transformer); } @Override public List<Account> search(final AccountQuery query) { final Map<String, Object> namedParameters = new HashMap<String, Object>(); final Set<Relationship> fetch = query.getFetch(); Class<? extends Account> entityClass = getEntityType(); if (query.getOwner() != null) { if (query.getOwner() instanceof SystemAccountOwner) { entityClass = SystemAccount.class; } else { entityClass = MemberAccount.class; } } final StringBuilder hql = HibernateHelper.getInitialQuery(entityClass, "a", fetch); HibernateHelper.addParameterToQuery(hql, namedParameters, "a.type", query.getType()); if (query.getOwner() instanceof Member) { HibernateHelper.addParameterToQuery(hql, namedParameters, "a.member", query.getOwner()); } HibernateHelper.appendOrder(hql, "a.type.name"); return list(query, hql.toString(), namedParameters); } private void appendMembersTransactionsDetailsReportSqlPart(final StringBuilder sql, final boolean useTT, final QueryParameter beginParameter, final QueryParameter endParameter, final boolean credits, final boolean notChargeBack) { final boolean flag = notChargeBack ? credits : !credits; final String account = flag ? "to_account_id" : "from_account_id"; final String related = flag ? "from_account_id" : "to_account_id"; sql.append( " select a.member_id, at.id as account_type_id, at.name account_type_name, t.id transfer_id, t.process_date date, " + (credits ? "" : "-1 * ") + "abs(t.amount) amount, t.description, ra.owner_name related_username, rm.name related_name, tt.name transfer_type_name, t.transaction_number"); sql.append(" from transfers t inner join accounts a on t.").append(account) .append(" = a.id inner join accounts ra on t.").append(related) .append(" = ra.id inner join transfer_types tt on t.type_id = tt.id inner join account_types at on a.type_id = at.id left join members rm on ra.member_id = rm.id"); sql.append(" where t.status = :processed"); sql.append(" and t.chargeback_of_id is ").append(notChargeBack ? "" : "not ").append("null"); if (useTT) { sql.append(" and t.type_id in (:ttIds)"); } if (beginParameter != null) { sql.append(" and t.process_date " + beginParameter.getOperator() + " :beginDate"); } if (endParameter != null) { sql.append(" and t.process_date " + endParameter.getOperator() + " :endDate"); } } private void appendMembersTransactionsSummaryReportSqlPart(final StringBuilder sql, final boolean useGroups, final boolean useTT, final QueryParameter beginParameter, final QueryParameter endParameter, final boolean credits, final boolean notChargeBack) { final boolean flag = notChargeBack ? credits : !credits; final String account = flag ? "to_account_id" : "from_account_id"; sql.append( " select m.id as member_id, m.name as member_name, u.username, count(t.id) as count, sum(abs(t.amount)) as amount"); sql.append(" from transfers t inner join accounts a on t.").append(account) .append(" = a.id inner join members m on a.member_id = m.id inner join users u on m.id = u.id"); sql.append(" where t.status = :processed"); sql.append(" and t.chargeback_of_id is ").append(notChargeBack ? "null" : "not null"); if (useGroups) { sql.append(" and m.group_id in (:groupIds)"); } if (useTT) { sql.append(" and t.type_id in (:ttIds)"); } if (beginParameter != null) { sql.append(" and t.process_date " + beginParameter.getOperator() + " :beginDate"); } if (endParameter != null) { sql.append(" and t.process_date " + endParameter.getOperator() + " :endDate"); } sql.append(" group by m.id, m.name, u.username"); } private TransactionSummaryVO buildSummary(final Object object) { final Object[] row = (Object[]) object; final int count = row[0] == null ? 0 : (Integer) row[0]; final BigDecimal amount = row[1] == null ? BigDecimal.ZERO : (BigDecimal) row[1]; return new TransactionSummaryVO(count, amount); } private TransactionSummaryVO getSummary(final GetTransactionsDTO dto, final boolean credits, final Transfer.Status status) { final Account account = load(dto.getOwner(), dto.getType()); final Member relatedToMember = dto.getRelatedToMember(); final Element by = dto.getBy(); final Period period = dto.getPeriod(); final Collection<PaymentFilter> paymentFilters = dto.getPaymentFilters(); final StringBuilder hql = new StringBuilder(); final Map<String, Object> namedParams = new HashMap<String, Object>(); hql.append(" select count(*), sum(abs(t.amount))"); hql.append(" from " + Transfer.class.getName() + " t"); hql.append(" where ((t.amount > 0 and t.").append(credits ? "to" : "from").append(" = :account) "); hql.append(" or (t.amount < 0 and t.").append(credits ? "from" : "to").append(" = :account)) "); namedParams.put("account", account); HibernateHelper.addParameterToQuery(hql, namedParams, "t.status", status); // Count root transfers only if (dto.isRootOnly()) { hql.append(" and t.parent is null"); } // Get only transfers related to (from or to) the specified member if (relatedToMember != null) { hql.append(" and exists ("); hql.append(" select ma.id from MemberAccount ma "); hql.append(" where ma.member = :relatedToMember "); hql.append(" and (t.from = ma or t.to = ma) "); hql.append(" )"); namedParams.put("relatedToMember", relatedToMember); } // Apply the payments filters if (CollectionUtils.isNotEmpty(paymentFilters)) { final Set<TransferType> transferTypes = new HashSet<TransferType>(); for (PaymentFilter paymentFilter : paymentFilters) { if (paymentFilter == null || paymentFilter.isTransient()) { continue; } paymentFilter = getFetchDao().fetch(paymentFilter, PaymentFilter.Relationships.TRANSFER_TYPES); if (paymentFilter.getTransferTypes() != null) { transferTypes.addAll(paymentFilter.getTransferTypes()); } } if (CollectionUtils.isNotEmpty(transferTypes)) { hql.append(" and t.type in (:transferTypes) "); namedParams.put("transferTypes", transferTypes); } } // Apply the operated by if (by != null) { hql.append(" and (t.by = :by or t.receiver = :by)"); namedParams.put("by", by); } HibernateHelper.addPeriodParameterToQuery(hql, namedParams, "ifnull(t.processDate,t.date)", period); return buildSummary(uniqueResult(hql.toString(), namedParams)); } }