Author: jpepin Date: 2010-05-26 14:59:22 +0200 (Wed, 26 May 2010) New Revision: 2908 Url: http://chorem.org/repositories/revision/lima/2908 Log: Optimisation req?\195?\187ete calcul montant et soldes. Calcul des montants et affichage des entr?\195?\169es d'un regrouprement de compte Modified: trunk/lima-business/src/main/java/org/chorem/lima/business/ejb/ReportServiceImpl.java Modified: trunk/lima-business/src/main/java/org/chorem/lima/business/ejb/ReportServiceImpl.java =================================================================== --- trunk/lima-business/src/main/java/org/chorem/lima/business/ejb/ReportServiceImpl.java 2010-05-25 16:24:52 UTC (rev 2907) +++ trunk/lima-business/src/main/java/org/chorem/lima/business/ejb/ReportServiceImpl.java 2010-05-26 12:59:22 UTC (rev 2908) @@ -87,69 +87,44 @@ return reportsDatas; } - + /** + * List entries for a period and an account + * Calculate the amounts + * Two case: + * - for subaccount + * - for a foldaccounts, contains many accounts + */ public ReportsDatas generateAccountReportsWithTransaction (Account account, Date beginDate, Date endDate, TopiaContext topiaContext) throws LimaException{ ReportsDatas reportsDatas = new ReportsDatasImpl(); double credit = 0, debit = 0, solde = 0; + List<Object[]> results = new ArrayList<Object[]>(); String queryAlias = "E"; if (account != null){ - try { - EntryDAO entryDAO= LimaCallaoDAOHelper.getEntryDAO(topiaContext); - reportsDatas.setListEntry(entryDAO.findAllByQuery( - createEntryQuery(account, beginDate, endDate, entryDAO, queryAlias))); - /* - TopiaQuery debitQuery = createEntryQuery(account, beginDate, endDate, entryDAO, queryAlias); - debitQuery.add(Entry.DEBIT, Boolean.TRUE).setSelect(Entry.AMOUNT); - //List<Object[]> debitObject = debitQuery.execute(); - //log.debug(debitObject); - - TopiaQuery creditQuery = createEntryQuery(account, beginDate, endDate, entryDAO, queryAlias); - creditQuery.add(Entry.DEBIT, Boolean.FALSE).setSelect(Entry.AMOUNT); - //List<Object[]> creditObject = creditQuery.execute(); - //log.debug(creditObject); - - TopiaQuery soldeQuery = createEntryQuery(account, beginDate, endDate, entryDAO, queryAlias); - soldeQuery.setSelect("SUM(" + debitQuery.fullQuery() + ") as debit") - .addParams(debitQuery.getParams()) - .addSelect("SUM(" + creditQuery.fullQuery() + ") as credit") - .addParams(creditQuery.getParams()) - .addSelect("(debit - credit) as solde"); - log.debug(soldeQuery); - List<Object[]> soldeObject = soldeQuery.execute(); - log.debug(soldeObject); - - */ - - /*query.setSelect("E."+Entry.DEBIT, "SUM(E."+Entry.AMOUNT+")"); - query.addGroup("E."+Entry.DEBIT); - List<Object[]> results = query.execute(); - for (Object[] result : results) { - if ((Boolean)result[0]){ - debit += (Double)result[1]; - } - else { - credit += (Double)result[1]; - } - } - // set the amounts and solde - solde = debit - credit; - if (solde > 0){ - reportsDatas.setSoldeDebit(true); - } - solde = Math.abs(solde);*/ - + // First subaccount + List<Account> accounts = account.getSubAccounts(); + if (accounts.size() == 0){ + reportsDatas = generateSubAccountReportsWithTransaction(account, beginDate, endDate, topiaContext); + } + //Second many account + else { + List<Entry> entries = new ArrayList<Entry>(); + for (Account subAccount : accounts) { + ReportsDatas subReportsDatas = generateAccountReportsWithTransaction(subAccount, beginDate, endDate, topiaContext); + debit += subReportsDatas.getAmountDebit(); + credit += subReportsDatas.getAmountCredit(); + entries.addAll(subReportsDatas.getListEntry()); + } + solde = debit - credit; + if (solde > 0){ + reportsDatas.setSoldeDebit(true); + } + solde = Math.abs(solde); reportsDatas.setAmountCredit(credit); reportsDatas.setAmountDebit(debit); reportsDatas.setAmountSolde(solde); - - // commit - topiaContext.commitTransaction(); + reportsDatas.setListEntry(entries); } - catch (TopiaException ex) { - doCatch(topiaContext, ex, log); - } } - return reportsDatas; } @@ -163,13 +138,63 @@ query.add(transactionDateProperty+" BETWEEN :beginDate AND :endDate") .addParam("beginDate", beginDate) .addParam("endDate", endDate) - .add(Entry.ACCOUNT, account) - //IMPORTANT : LOADING ENTRIES AND IS COLUMN FOR NO LAZY EXCEPTION - .addLoad(Entry.ENTRY_BOOK, Entry.FINANCIAL_TRANSACTION); + .add(Entry.ACCOUNT, account); return query; } + public ReportsDatas generateSubAccountReportsWithTransaction(Account account, Date beginDate, Date endDate, TopiaContext topiaContext) throws LimaException { + ReportsDatas reportsDatas = new ReportsDatasImpl(); + double credit = 0, debit = 0, solde = 0; + List<Object[]> results = new ArrayList<Object[]>(); + String queryAlias = "E"; + try { + EntryDAO entryDAO= LimaCallaoDAOHelper.getEntryDAO(topiaContext); + TopiaQuery entriesQuery = + createEntryQuery(account, beginDate, endDate, entryDAO, queryAlias); + //IMPORTANT : LOADING ENTRIES AND IS COLUMN FOR NO LAZY EXCEPTION + entriesQuery.addLoad(Entry.ENTRY_BOOK, Entry.FINANCIAL_TRANSACTION); + reportsDatas.setListEntry(entryDAO.findAllByQuery(entriesQuery)); + + TopiaQuery amountsQuery = + createEntryQuery(account, beginDate, endDate, entryDAO, queryAlias); + amountsQuery.setSelect("E."+Entry.DEBIT, "SUM(E."+Entry.AMOUNT+")"); + amountsQuery.addGroup("E."+Entry.DEBIT); + results = amountsQuery.execute(); + int nbAmount = results.size(); + if(nbAmount==2){ + debit = (Double)results.get(0)[1]; + credit = (Double)results.get(1)[1]; + } + if (nbAmount==1){ + if ((Boolean)results.get(0)[0]){ + debit = (Double)results.get(0)[1]; + } + else { + credit = (Double)results.get(0)[1]; + } + } + + // set the amounts and solde + solde = debit - credit; + if (solde > 0){ + reportsDatas.setSoldeDebit(true); + } + solde = Math.abs(solde); + + reportsDatas.setAmountCredit(credit); + reportsDatas.setAmountDebit(debit); + reportsDatas.setAmountSolde(solde); + + // commit + topiaContext.commitTransaction(); + } + catch (TopiaException ex) { + doCatch(topiaContext, ex, log); + } + return reportsDatas; + } + /** * Get all entry from an entrybook and a begin-end dates order by financial transaction date *