Author: jpepin Date: 2010-05-26 17:14:58 +0200 (Wed, 26 May 2010) New Revision: 2909 Url: http://chorem.org/repositories/revision/lima/2909 Log: Optimisation requ?\195?\170te cacul balance et ?\195?\169ditions journaux. Modified: trunk/lima-business/src/main/java/org/chorem/lima/business/ejb/ReportServiceImpl.java trunk/lima-swing/src/main/java/org/chorem/lima/ui/accountsreports/AccountsReportsTableModel.java trunk/lima-swing/src/main/java/org/chorem/lima/ui/balance/BalanceTableModel.java trunk/lima-swing/src/main/java/org/chorem/lima/ui/balance/BalanceView.jaxx 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-26 12:59:22 UTC (rev 2908) +++ trunk/lima-business/src/main/java/org/chorem/lima/business/ejb/ReportServiceImpl.java 2010-05-26 15:14:58 UTC (rev 2909) @@ -35,7 +35,6 @@ import org.chorem.lima.business.LimaException; import org.chorem.lima.business.ReportService; import org.chorem.lima.entity.Account; -import org.chorem.lima.entity.AccountDAO; import org.chorem.lima.entity.Entry; import org.chorem.lima.entity.EntryBook; import org.chorem.lima.entity.EntryDAO; @@ -54,6 +53,9 @@ private TopiaContext rootContext; + protected AccountServiceImpl accountService = + new AccountServiceImpl(); + public ReportServiceImpl() { LimaConfig config = LimaConfig.getInstance(); try { @@ -88,6 +90,7 @@ } /** + * Recursiv * List entries for a period and an account * Calculate the amounts * Two case: @@ -128,7 +131,9 @@ return reportsDatas; } - + /** + * Query for find entries for accountsreports and balancereports + */ public TopiaQuery createEntryQuery(Account account, Date beginDate, Date endDate, EntryDAO entryDAO, String queryAlias) throws LimaException{ TopiaQuery query = entryDAO.createQuery(queryAlias); @@ -143,55 +148,115 @@ return query; } + /** + * Get list entries + * Calculate all credit, debit and solde amounts for the accounts reports + */ 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 (beginDate != null && endDate != null){ + 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(); } - if (nbAmount==1){ - if ((Boolean)results.get(0)[0]){ - debit = (Double)results.get(0)[1]; + catch (TopiaException ex) { + doCatch(topiaContext, ex, log); + } + } + + return reportsDatas; + } + + /** + * Calculate all credit, debit and solde amounts for the balance + */ + public ReportsDatas generateSubAccountBalanceWithTransaction(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 (beginDate != null && endDate != null){ + try { + EntryDAO entryDAO= LimaCallaoDAOHelper.getEntryDAO(topiaContext); + + 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]; } - else { - credit = (Double)results.get(0)[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(); } - - // set the amounts and solde - solde = debit - credit; - if (solde > 0){ - reportsDatas.setSoldeDebit(true); + catch (TopiaException ex) { + doCatch(topiaContext, ex, log); } - 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; } @@ -205,64 +270,67 @@ public ReportsDatas generateEntryBooksReports(EntryBook entryBook, Date beginDate, Date endDate) throws LimaException { ReportsDatas reportsDatas = new ReportsDatasImpl(); double credit = 0, debit = 0, solde = 0; - - // Get all entries with a topia query - TopiaContext topiaTransaction = null; - try { - topiaTransaction = rootContext.beginTransaction(); - EntryDAO entryDAO= LimaCallaoDAOHelper.getEntryDAO(topiaTransaction); - TopiaQuery query = entryDAO.createQuery(); - - if (entryBook != null) { - query - .add(Entry.ENTRY_BOOK, entryBook); - } - if (beginDate != null && endDate != null){ + List<Object[]> results = new ArrayList<Object[]>(); + if (entryBook != null && beginDate != null && endDate != null){ + // Get all entries with a topia query + TopiaContext topiaTransaction = null; + try { + topiaTransaction = rootContext.beginTransaction(); + EntryDAO entryDAO= LimaCallaoDAOHelper.getEntryDAO(topiaTransaction); + TopiaQuery query = entryDAO.createQuery("E"); String transactionDateProperty = TopiaQuery.getProperty(Entry.FINANCIAL_TRANSACTION, FinancialTransaction.TRANSACTION_DATE); - query.add(transactionDateProperty+" BETWEEN :beginDate AND :endDate") + query.add(Entry.ENTRY_BOOK, entryBook) + .add(transactionDateProperty+" BETWEEN :beginDate AND :endDate") .addParam("beginDate", beginDate) .addParam("endDate", endDate); - String orderProperty = + /*String orderProperty = TopiaQuery.getProperty(Entry.FINANCIAL_TRANSACTION, FinancialTransaction.TRANSACTION_DATE); - query.addOrder(orderProperty); - } - reportsDatas.setListEntry(entryDAO.findAllByQuery(query)); - - for (Entry entry : reportsDatas.getListEntry()) { + query.addOrder(orderProperty);*/ //IMPORTANT : LOADING ENTRIES AND IS COLUMN FOR NO LAZY EXCEPTION - entry.getAccount(); - entry.getFinancialTransaction().getTransactionDate(); - //calulate the amounts - if (entry.getDebit()) { - debit += entry.getAmount(); + query.addLoad(Entry.ACCOUNT, Entry.FINANCIAL_TRANSACTION); + reportsDatas.setListEntry(entryDAO.findAllByQuery(query)); + + query.setSelect("E."+Entry.DEBIT, "SUM(E."+Entry.AMOUNT+")"); + query.addGroup("E."+Entry.DEBIT); + results = query.execute(); + int nbAmount = results.size(); + if(nbAmount==2){ + debit = (Double)results.get(0)[1]; + credit = (Double)results.get(1)[1]; } - else { - credit += entry.getAmount(); + 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 + topiaTransaction.commitTransaction(); } - - // set the amounts and solde - solde = debit - credit; - if (solde > 0){ - reportsDatas.setSoldeDebit(true); + catch (TopiaException ex) { + doCatch(topiaTransaction, ex, log); } - solde = Math.abs(solde); - reportsDatas.setAmountCredit(credit); - reportsDatas.setAmountDebit(debit); - reportsDatas.setAmountSolde(solde); - - // commit - topiaTransaction.commitTransaction(); + finally { + doFinally(topiaTransaction, log); + } } - catch (TopiaException ex) { - doCatch(topiaTransaction, ex, log); - } - finally { - doFinally(topiaTransaction, log); - } return reportsDatas; } @@ -270,29 +338,21 @@ /** * Get balance trial * - * Calculate the amounts and the solde + * Calculate the amounts and the solde for all subaccounts */ @Override public BalanceTrial generateBalanceTrial(Date beginDate, Date endDate) throws LimaException { BalanceTrial balanceTrial = new BalanceTrialImpl(); balanceTrial.setReportsDatas(new ArrayList<ReportsDatas>()); double credit = 0, debit = 0, solde = 0; - List<Account> accountsList = new ArrayList<Account>(); TopiaContext topiaTransaction = null; try { topiaTransaction = rootContext.beginTransaction(); - //Get All account - AccountDAO accountDAO = - LimaCallaoDAOHelper.getAccountDAO(topiaTransaction); - List<Account> accounts = accountDAO.findAll(); - accountsList.addAll(accounts); - //for each account create a balance sheet with a ReportsDatas - for (Account account : accounts) { - ReportsDatas reportsDatas = - generateAccountReportsWithTransaction(account, beginDate, endDate, topiaTransaction); + for (Account account : accountService.getAllSubAccounts()) { + ReportsDatas reportsDatas = generateSubAccountBalanceWithTransaction(account, beginDate, endDate, topiaTransaction); reportsDatas.setAccount(account); // add balance sheet to balance trial balanceTrial.addReportsDatas(reportsDatas); Modified: trunk/lima-swing/src/main/java/org/chorem/lima/ui/accountsreports/AccountsReportsTableModel.java =================================================================== --- trunk/lima-swing/src/main/java/org/chorem/lima/ui/accountsreports/AccountsReportsTableModel.java 2010-05-26 12:59:22 UTC (rev 2908) +++ trunk/lima-swing/src/main/java/org/chorem/lima/ui/accountsreports/AccountsReportsTableModel.java 2010-05-26 15:14:58 UTC (rev 2909) @@ -178,17 +178,13 @@ } public void setBeginDate(Date date){ - log.debug("setBeginDate"); selectedBeginDate = date; - log.debug(selectedBeginDate); cacheDataList=getDataList(); fireTableDataChanged(); } public void setEndDate(Date date){ - log.debug("setEndDate"); selectedEndDate = date; - log.debug(selectedEndDate); cacheDataList=getDataList(); fireTableDataChanged(); } Modified: trunk/lima-swing/src/main/java/org/chorem/lima/ui/balance/BalanceTableModel.java =================================================================== --- trunk/lima-swing/src/main/java/org/chorem/lima/ui/balance/BalanceTableModel.java 2010-05-26 12:59:22 UTC (rev 2908) +++ trunk/lima-swing/src/main/java/org/chorem/lima/ui/balance/BalanceTableModel.java 2010-05-26 15:14:58 UTC (rev 2909) @@ -185,7 +185,7 @@ * @return */ public BalanceTrial getDataList(){ - BalanceTrial results = new BalanceTrialImpl(); + BalanceTrial results = null; try { results = reportService.generateBalanceTrial(selectedBeginDate, selectedEndDate); Modified: trunk/lima-swing/src/main/java/org/chorem/lima/ui/balance/BalanceView.jaxx =================================================================== --- trunk/lima-swing/src/main/java/org/chorem/lima/ui/balance/BalanceView.jaxx 2010-05-26 12:59:22 UTC (rev 2908) +++ trunk/lima-swing/src/main/java/org/chorem/lima/ui/balance/BalanceView.jaxx 2010-05-26 15:14:58 UTC (rev 2909) @@ -30,16 +30,16 @@ Calendar calendarBegin = Calendar.getInstance(); // set begindate to JAN 1 - 0:00.000 of this years Date beginDate = calendarBegin.getTime(); - beginDate = DateUtils.truncate(beginDate, Calendar.YEAR); - getModelBalanceTable().setBeginDate(beginDate); + beginDate = DateUtils.truncate(beginDate, Calendar.YEAR); // get end date Calendar calendarEnd = Calendar.getInstance(); Date endDate = calendarEnd.getTime(); - getModelBalanceTable().setEndDate(endDate); getBeginDatePicker().setDate(beginDate); getEndDatePicker().setDate(endDate); + getModelBalanceTable().setBeginDate(beginDate); + getModelBalanceTable().setEndDate(endDate); void $afterCompleteSetup() { getHandler().updateFooterLabel();