Java tutorial
/* * 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(); } }