/*
* Copyright (C) 2011 4th Line GmbH, Switzerland
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU Affero General Public License as
* published by the Free Software Foundation, either version 3 of the
* License, or (at your option) any later version.
*
* This program 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 Affero General Public License for more details.
*
* You should have received a copy of the GNU Affero General Public License
* along with this program. If not, see <http://www.gnu.org/licenses/>.
*/
package org.fourthline.konto.server.dao;
import org.hibernate.Query;
import org.hibernate.transform.ResultTransformer;
import org.seamless.util.time.DateRange;
import org.fourthline.konto.shared.LedgerCoordinates;
import org.fourthline.konto.shared.MonetaryAmount;
import org.fourthline.konto.shared.entity.Account;
import org.fourthline.konto.shared.entity.Entry;
import org.fourthline.konto.shared.entity.MonetaryUnit;
import org.fourthline.konto.shared.entity.Split;
import org.fourthline.konto.shared.query.EntriesQueryCriteria;
import org.fourthline.konto.shared.query.LedgerLinesQueryCriteria;
import org.fourthline.konto.shared.result.EntryReportLine;
import org.fourthline.konto.shared.result.LedgerLines;
import org.fourthline.konto.shared.result.AccountReportLine;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
/**
* @author Christian Bauer
*/
public class EntryDAO extends HibernateDAO {
public Entry getEntry(Long id) {
return (Entry) getCurrentSession().get(Entry.class, id);
}
public Entry populateSplits(final Entry entry) {
StringBuilder sb = new StringBuilder();
sb.append("select s, sa, eaUnit, saUnit from Entry e, Split s, ");
sb.append("Account ea, MonetaryUnit eaUnit, Account sa, MonetaryUnit saUnit");
sb.append(" where s.entryId = :entryId");
sb.append(" and e.id = s.entryId");
sb.append(" and ea.id = e.accountId and sa.id = s.accountId");
sb.append(" and ea.monetaryUnitId = eaUnit.id and sa.monetaryUnitId = saUnit.id");
Query q = getCurrentSession().createQuery(sb.toString());
q.setLong("entryId", entry.getId());
q.setResultTransformer(new ResultTransformer() {
@Override
public Object transformTuple(Object[] objects, String[] strings) {
Split split = (Split) objects[0];
split.setAccount((Account) objects[1]);
split.setEntryMonetaryUnit((MonetaryUnit) objects[2]);
split.setMonetaryUnit((MonetaryUnit) objects[3]);
split.setEntry(entry);
return split;
}
@Override
public List transformList(List list) {
return list;
}
});
entry.setSplits(q.list());
return entry;
}
public Split getSplit(Long id) {
StringBuilder sb = new StringBuilder();
sb.append("select s, sa, eaUnit, saUnit from Entry e, Split s, ");
sb.append("Account ea, MonetaryUnit eaUnit, Account sa, MonetaryUnit saUnit");
sb.append(" where e.id = s.entryId");
sb.append(" and ea.id = e.accountId and sa.id = s.accountId");
sb.append(" and ea.monetaryUnitId = eaUnit.id and sa.monetaryUnitId = saUnit.id");
sb.append(" and s.id = :id");
Query q = getCurrentSession().createQuery(sb.toString());
q.setLong("id", id);
q.setResultTransformer(new ResultTransformer() {
@Override
public Object transformTuple(Object[] objects, String[] strings) {
Split split = (Split) objects[0];
split.setAccount((Account) objects[1]);
split.setEntryMonetaryUnit((MonetaryUnit) objects[2]);
split.setMonetaryUnit((MonetaryUnit) objects[3]);
return split;
}
@Override
public List transformList(List list) {
return list;
}
});
return (Split) q.uniqueResult();
}
public LedgerLines getLedgerLines(Account account,
LedgerLinesQueryCriteria criteria) {
MonetaryAmount startingBalance;
if (criteria.isStringFiltered()) {
// Always start at zero balance because not all lines are shown
startingBalance = new MonetaryAmount(account.getMonetaryUnit());
} else {
// We might possibly show all lines...
if (criteria.isStartingAfterAccountEffectiveOn(account)) {
// No, we show a subset of all lines restricted by time, so calculate
// the starting balance up to our starting timepoint (minus one day)
Date oneDayBefore = criteria.getEffectiveOn().getOneDayBeforeStart();
List<AccountReportLine> balanceOfDayBefore =
getAccountReportLines(Arrays.asList(account), new DateRange(null, oneDayBefore), true);
startingBalance = balanceOfDayBefore.get(0).getAmount();
} else {
// Yes, we show all lines, so start with the account's initial balance
startingBalance = account.getInitialBalance();
}
}
return getLedgerLines(account, criteria, startingBalance);
}
protected LedgerLines getLedgerLines(Account account,
LedgerLinesQueryCriteria criteria,
MonetaryAmount startingBalance) {
List<Entry> entries = getEntries(account, criteria);
LedgerLines lines = new LedgerLines(account, criteria.getEffectiveOn(), startingBalance);
for (Entry entry : entries) {
lines.addEntry(entry);
}
// TODO: More flexible sorting and balance calculation
if (criteria.getOrderBy() == null || criteria.getOrderBy().equals(Entry.Property.effectiveOn)) {
boolean balanceAscending = criteria.getOrderBy() == null || !criteria.isSortAscending();
lines.updateBalances(balanceAscending);
}
return lines;
}
public List<Entry> getEntries(final Account account, EntriesQueryCriteria criteria) {
StringBuilder sb = new StringBuilder();
sb.append("select e, s, ea, sa, eaUnit, saUnit from Entry e, Split s, ");
sb.append("Account ea, MonetaryUnit eaUnit, Account sa, MonetaryUnit saUnit");
sb.append(" where e.id = s.entryId");
sb.append(" and ea.id = e.accountId and sa.id = s.accountId");
sb.append(" and ea.monetaryUnitId = eaUnit.id and sa.monetaryUnitId = saUnit.id");
sb.append(" and (e.accountId = :accountId or s.accountId = :accountId)");
if (!criteria.isStringFilterEmpty())
sb.append(" and (lower(e.description) like :desc or lower(s.description) like :desc)");
if (criteria.getEffectiveOn() != null && criteria.getEffectiveOn().getStart() != null)
sb.append(" and e.effectiveOn >= :effectiveOnStart");
if (criteria.getEffectiveOn() != null && criteria.getEffectiveOn().getEnd() != null)
sb.append(" and e.effectiveOn <= :effectiveOnEnd");
sb.append(" order by ");
if (criteria.getOrderBy() == null) {
sb.append("e.effectiveOn desc");
} else if (criteria.getOrderBy().equals(Entry.Property.description)) {
sb.append("e.description ").append(criteria.isSortAscending() ? "asc" : "desc");
sb.append(", s.description ").append(criteria.isSortAscending() ? "asc" : "desc");
} else if (criteria.getOrderBy().equals(Entry.Property.enteredOn)) {
sb.append("e.enteredOn ").append(criteria.isSortAscending() ? "asc" : "desc");
} else {
sb.append("e.effectiveOn ").append(criteria.isSortAscending() ? "asc" : "desc");
}
sb.append(", e.enteredOn desc, s.enteredOn desc");
Query q = getCurrentSession().createQuery(sb.toString());
q.setLong("accountId", account.getId());
if (!criteria.isStringFilterEmpty())
q.setString("desc", criteria.getStringFilterWildcards());
if (criteria.getEffectiveOn() != null && criteria.getEffectiveOn().getStart() != null)
q.setDate("effectiveOnStart", criteria.getEffectiveOn().getStart());
if (criteria.getEffectiveOn() != null && criteria.getEffectiveOn().getEnd() != null)
q.setDate("effectiveOnEnd", criteria.getEffectiveOn().getEnd());
// TODO: Pagination?
final Map<Long, Entry> entries = new LinkedHashMap();
q.setResultTransformer(new ResultTransformer() {
@Override
public Object transformTuple(Object[] objects, String[] strings) {
Entry entry = (Entry) objects[0];
Split split = (Split) objects[1];
entry.setAccount((Account) objects[2]);
split.setAccount((Account) objects[3]);
split.setEntryMonetaryUnit((MonetaryUnit) objects[4]);
split.setMonetaryUnit((MonetaryUnit) objects[5]);
Entry existingEntry = entries.get(entry.getId());
if (existingEntry != null) {
split.setEntry(existingEntry);
existingEntry.getSplits().add(split);
} else {
// Clear the splits, this entry might have passed through
// this transformer before (querying several times with the same PC)
entry.getSplits().clear();
entry.getSplits().add(split);
split.setEntry(entry);
entries.put(entry.getId(), entry);
}
return null;
}
@Override
public List transformList(List list) {
return list;
}
});
q.list();
return new ArrayList(entries.values());
}
protected List<Long> getAccountIds(List<Account> accounts) {
List<Long> ids = new ArrayList(accounts.size());
for (Account account : accounts) {
ids.add(account.getId());
}
return ids;
}
public List<AccountReportLine> getAccountReportLines(List<Account> accounts, DateRange dateRange, boolean useInitialBalance) {
List<Long> accountIds = getAccountIds(accounts);
Query q = getCurrentSession().getNamedQuery("sumOfAccounts");
q.setParameterList("ids", accountIds);
// Default to 01.01.1900 -> Today if there is no start or end in the given date range
q.setDate("rangeStart", dateRange.getStart() != null ? dateRange.getStart() : new Date(0, 0, 1));
q.setDate("rangeEnd", dateRange.getEnd() != null ? dateRange.getEnd() : new Date());
List<Object[]> result = q.list();
List<AccountReportLine> lines = new ArrayList(accounts.size());
for (Account account : accounts) {
// Start with account's initial balance or zero, then add the query result balance
MonetaryAmount amount =
useInitialBalance
? account.getInitialBalance()
: new MonetaryAmount(account.getMonetaryUnit());
for (Object[] r : result) {
if (account.getId().equals(r[0])) {
MonetaryAmount m =
new MonetaryAmount(account.getMonetaryUnit(), (BigDecimal) r[1]);
amount = amount.add(m);
break;
}
}
lines.add(new AccountReportLine(account, amount));
}
return lines;
}
public Map<Account, List<EntryReportLine>> getEntryReportLines(List<Account> accounts, DateRange dateRange) {
List<Long> accountIds = getAccountIds(accounts);
Query q = getCurrentSession().getNamedQuery("cashflowOfAccounts");
q.setParameterList("ids", accountIds);
// Default to 01.01.1900 -> Today if there is no start or end in the given date range
q.setDate("rangeStart", dateRange.getStart() != null ? dateRange.getStart() : new Date(0, 0, 1));
q.setDate("rangeEnd", dateRange.getEnd() != null ? dateRange.getEnd() : new Date());
List<Object[]> result = q.list();
Map<Account, List<EntryReportLine>> accountEntryLines = new LinkedHashMap(accounts.size());
for (Account account : accounts) {
boolean haveEntries = false;
for (Object[] r : result) {
if (account.getId().equals(r[0])) {
List<EntryReportLine> lines;
if ((lines = accountEntryLines.get(account)) == null) {
lines = new ArrayList();
accountEntryLines.put(account, lines);
}
Long entryId = (Long) r[1];
Long splitId = (Long) r[2];
Date effectiveOn = (Date) r[3];
String description = (String) r[4];
Long fromToAccountId = (Long) r[5];
String fromToAccountGroup = (String) r[6];
String fromToAccount = (String) r[7];
BigDecimal amount = (BigDecimal) r[8];
lines.add(
new EntryReportLine(
description,
new MonetaryAmount(account.getMonetaryUnit(), amount),
new LedgerCoordinates(account.getId(), entryId, splitId),
effectiveOn,
fromToAccountId,
fromToAccountGroup,
fromToAccount
)
);
haveEntries = true;
}
}
if (!haveEntries) {
accountEntryLines.put(account, new ArrayList());
}
}
return accountEntryLines;
}
public void persist(Entry entry) {
getCurrentSession().saveOrUpdate(entry);
getCurrentSession().flush();
for (Split split : entry.getSplits()) {
split.setEntryId(entry.getId());
persist(split);
}
for (Split split : entry.getOrphanedSplits()) {
delete(split);
}
}
public void delete(Entry entry) {
getCurrentSession().delete(entry);
}
public void persist(Split split) {
getCurrentSession().saveOrUpdate(split);
}
public void delete(Split split) {
getCurrentSession().delete(split);
deleteOrphanedEntries();
}
public void deleteOrphanedEntries() {
// Remove entries that now have no more splits
getCurrentSession().createQuery(
"delete from Entry e where not e.id in " +
"(select distinct(s.entryId) from Split s)"
).executeUpdate();
}
}