Author: echatellier Date: 2012-05-04 11:43:18 +0200 (Fri, 04 May 2012) New Revision: 3400 Url: http://chorem.org/repositories/revision/lima/3400 Log: Begin report service refactoring. Move query to DAO. Remove topia query. Added: trunk/lima-callao/src/main/java/org/chorem/lima/entity/EntryDAO.java Modified: trunk/lima-business-api/src/main/java/org/chorem/lima/business/api/AccountService.java trunk/lima-business-api/src/main/java/org/chorem/lima/business/api/ReportService.java trunk/lima-business-api/src/main/java/org/chorem/lima/business/api/ReportServiceLocal.java trunk/lima-business/src/main/java/org/chorem/lima/business/ejb/AccountServiceImpl.java trunk/lima-business/src/main/java/org/chorem/lima/business/ejb/ReportServiceImpl.java trunk/lima-business/src/test/java/org/chorem/lima/business/AccountServiceImplTest.java trunk/lima-callao/src/main/java/org/chorem/lima/entity/AccountDAO.java trunk/lima-callao/src/main/java/org/chorem/lima/entity/ClosedPeriodicEntryBookDAO.java trunk/lima-callao/src/main/java/org/chorem/lima/entity/EntryImpl.java trunk/lima-callao/src/main/java/org/chorem/lima/entity/FinancialPeriodDAO.java trunk/lima-callao/src/main/java/org/chorem/lima/entity/FinancialStatementImpl.java trunk/lima-callao/src/main/java/org/chorem/lima/entity/VatStatementImpl.java Modified: trunk/lima-business/src/main/java/org/chorem/lima/business/ejb/AccountServiceImpl.java =================================================================== --- trunk/lima-business/src/main/java/org/chorem/lima/business/ejb/AccountServiceImpl.java 2012-05-04 09:42:31 UTC (rev 3399) +++ trunk/lima-business/src/main/java/org/chorem/lima/business/ejb/AccountServiceImpl.java 2012-05-04 09:43:18 UTC (rev 3400) @@ -180,6 +180,24 @@ return accountsList; } + /* + * @see org.chorem.lima.business.api.AccountService#getAllSubAccounts(org.chorem.lima.entity.Account) + */ + @Override + public List<Account> getAllSubAccounts(Account account) + throws LimaException { + List<Account> accountsList = null; + + try { + AccountDAO accountDAO = LimaDaoHelper.getAccountDAO(); + accountsList = accountDAO.findAllSubAccounts(account); + } catch (Exception ex) { + throw new LimaException("Can't get all sub accounts", ex); + } + + return accountsList; + } + /** * Permet d'effacer un compte dans la base de données. * <p/> 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 2012-05-04 09:42:31 UTC (rev 3399) +++ trunk/lima-business/src/main/java/org/chorem/lima/business/ejb/ReportServiceImpl.java 2012-05-04 09:43:18 UTC (rev 3400) @@ -104,68 +104,60 @@ * - for subaccount * - for a foldaccounts, contains many accounts * - for a foldthirdparts accounts + * @throws TopiaException */ @Override public ReportsDatas generateAccountReportsWithTransaction(Account account, Boolean thirdPartAccountsMode, Date beginDate, Date endDate, - TopiaContext topiaContext) throws LimaException { + TopiaContext topiaContext) throws LimaException, TopiaException { ReportsDatas reportsDatas = new ReportsDatasImpl(); + BigDecimal credit = new BigDecimal(0); BigDecimal debit = new BigDecimal(0); BigDecimal solde = new BigDecimal(0); List<Entry> entries = new ArrayList<Entry>(); - if (account != null) { - //Get allsubaccounts and thirdParts accounts - List<Account> accounts = null; // FIXME review code (List<Account>) account.getSubAccounts(); - if (thirdPartAccountsMode) { - List<Account> thirdPartAccount = null; // FIXME review code (List<Account>) account.getSubLedgers(); - if (thirdPartAccount != null) { - ReportsDatas subReportsDatas = generateSubAccountReportsWithTransaction(account, - beginDate, endDate, topiaContext); + //Get allsubaccounts and thirdParts accounts + List<Account> accounts = accountService.getAllSubAccounts(account); - entries.addAll(subReportsDatas.getListEntry()); - debit = debit.add(subReportsDatas.getAmountDebit()); - credit = credit.add(subReportsDatas.getAmountCredit()); - accounts.addAll(thirdPartAccount); - } + // is already subaccount + // TODO echatellier 20120502 c'est vraiment voulu ? + // il ne peut pas y avoir de transaction sur des comptes non feuilles ? + // a verifier, sinon, supprimer la récursion + if (accounts.size() == 0) { + reportsDatas = generateSubAccountReportsWithTransaction(account, + beginDate, endDate, topiaContext); + } + // else is folder accounts contains many subs and thirds accounts + else { + for (Account subAccount : accounts) { + ReportsDatas subReportsDatas = + generateAccountReportsWithTransaction(subAccount, true, + beginDate, endDate, topiaContext); + debit = debit.add(subReportsDatas.getAmountDebit()); + credit = credit.add(subReportsDatas.getAmountCredit()); + entries.addAll(subReportsDatas.getListEntry()); } + //solde = debit - credit + solde = solde.add(debit); + solde = solde.subtract(credit); - // is already subaccount - if (accounts.size() == 0) { - reportsDatas = generateSubAccountReportsWithTransaction(account, - beginDate, endDate, topiaContext); + if (solde.compareTo(BigDecimal.ZERO) == 1) { + reportsDatas.setSoldeDebit(true); } - // else is folder accounts contains many subs and thirds accounts - else { - for (Account subAccount : accounts) { - ReportsDatas subReportsDatas = - generateAccountReportsWithTransaction(subAccount, true, - beginDate, endDate, topiaContext); - debit = debit.add(subReportsDatas.getAmountDebit()); - credit = credit.add(subReportsDatas.getAmountCredit()); - entries.addAll(subReportsDatas.getListEntry()); - } - //solde = debit - credit - solde = solde.add(debit); - solde = solde.subtract(credit); - - if (solde.compareTo(BigDecimal.ZERO) == 1) { - reportsDatas.setSoldeDebit(true); - } - solde = solde.abs(); - reportsDatas.setAmountCredit(credit); - reportsDatas.setAmountDebit(debit); - reportsDatas.setAmountSolde(solde); - reportsDatas.setListEntry(entries); - } + solde = solde.abs(); + reportsDatas.setAmountCredit(credit); + reportsDatas.setAmountDebit(debit); + reportsDatas.setAmountSolde(solde); + reportsDatas.setListEntry(entries); } + return reportsDatas; } - /** + /* * Query for find entries for accountsreports and balancereports * Just exact and balanced transaction are calculated. * @@ -176,12 +168,12 @@ * @param queryAlias * @return * @throws LimaException - */ + * protected TopiaQuery createEntryQuery(Account account, Date beginDate, Date endDate, EntryDAO entryDAO, - String queryAlias) throws LimaException { + String queryAlias) { TopiaQuery query = entryDAO.createQuery(queryAlias); String transactionDateProperty = TopiaQuery.getProperty( @@ -190,8 +182,7 @@ Entry.PROPERTY_FINANCIAL_TRANSACTION, FinancialTransaction.PROPERTY_ENTRY_BOOK); // echatellier, oui c'est hardcodé, mais qu'est ce que c'est illisible sans ca - query - .addWhere("(select sum(E2.amount) from " + Entry.class.getName() + " E2 where E2.debit = false and E2.financialTransaction = E.financialTransaction) = " + + query .addWhere("(select sum(E2.amount) from " + Entry.class.getName() + " E2 where E2.debit = false and E2.financialTransaction = E.financialTransaction) = " + "(select sum(E2.amount) from " + Entry.class.getName() + " E2 where E2.debit = true and E2.financialTransaction = E.financialTransaction)") .addWhere(transactionDateProperty + " BETWEEN :beginDate AND :endDate") .addNotNull(entryBookProperty) @@ -200,7 +191,7 @@ .addEquals(Entry.PROPERTY_ACCOUNT, account); return query; - } + }*/ /** * Get list entries @@ -212,68 +203,52 @@ * @param topiaContext * @return * @throws LimaException + * @throws TopiaException */ protected ReportsDatas generateSubAccountReportsWithTransaction(Account account, Date beginDate, Date endDate, - TopiaContext topiaContext) throws LimaException { + TopiaContext topiaContext) throws TopiaException { ReportsDatas reportsDatas = new ReportsDatasImpl(); BigDecimal credit = new BigDecimal(0); BigDecimal debit = new BigDecimal(0); BigDecimal solde = new BigDecimal(0); - List<Object[]> results; - String queryAlias = "E"; - 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 - String loadEntryBookProperty = - TopiaQuery.getProperty(Entry.PROPERTY_FINANCIAL_TRANSACTION, - FinancialTransaction.PROPERTY_ENTRY_BOOK); - entriesQuery.addLoad(loadEntryBookProperty); - reportsDatas.setListEntry(entryDAO.findAllByQuery(entriesQuery)); + EntryDAO entryDAO = LimaCallaoDAOHelper.getEntryDAO(topiaContext); - TopiaQuery amountsQuery = - createEntryQuery(account, beginDate, endDate, entryDAO, queryAlias); - amountsQuery.setSelect("E." + Entry.PROPERTY_DEBIT, "SUM(E." + Entry.PROPERTY_AMOUNT + ")"); - amountsQuery.addGroup("E." + Entry.PROPERTY_DEBIT); - results = amountsQuery.execute(topiaContext); - int nbAmount = results.size(); - if (nbAmount == 2) { - debit = (BigDecimal) results.get(0)[1]; - credit = (BigDecimal) results.get(1)[1]; - } - if (nbAmount == 1) { - if ((Boolean) results.get(0)[0]) { - debit = (BigDecimal) results.get(0)[1]; - } else { - credit = (BigDecimal) results.get(0)[1]; - } - } + // trouve les entrees associées à ca compte entre les date données + List<Entry> listEntries = entryDAO.findAllEntryOfEquilibredTransaction(account, beginDate, endDate); + reportsDatas.setListEntry(listEntries); - // set the amounts and solde - //solde = debit - credit - solde = solde.add(debit); - solde = solde.subtract(credit); + // recupere les totaux pour le compte + List<Object[]> results = entryDAO.getDebitCreditOfEquilibredTransaction(account, beginDate, endDate); + int nbAmount = results.size(); + if (nbAmount == 2) { + debit = (BigDecimal) results.get(0)[1]; + credit = (BigDecimal) results.get(1)[1]; + } + if (nbAmount == 1) { + if ((Boolean) results.get(0)[0]) { + debit = (BigDecimal) results.get(0)[1]; + } else { + credit = (BigDecimal) results.get(0)[1]; + } + } - if (solde.compareTo(BigDecimal.ZERO) == 1) { - reportsDatas.setSoldeDebit(true); - } - solde = solde.abs(); + // set the amounts and solde + // solde = debit - credit + solde = solde.add(debit); + solde = solde.subtract(credit); - reportsDatas.setAmountCredit(credit); - reportsDatas.setAmountDebit(debit); - reportsDatas.setAmountSolde(solde); - - //commitTransaction(topiaContext); - } catch (TopiaException ex) { - doCatch(topiaContext, ex); - } + if (solde.compareTo(BigDecimal.ZERO) == 1) { + reportsDatas.setSoldeDebit(true); } + solde = solde.abs(); + reportsDatas.setAmountCredit(credit); + reportsDatas.setAmountDebit(debit); + reportsDatas.setAmountSolde(solde); + return reportsDatas; } @@ -281,75 +256,68 @@ * Calculate all credit, debit and solde amounts for the balance * <p/> * Get all entries if true + * @throws TopiaException */ public ReportsDatas generateSubAccountBalanceWithTransaction(Account account, Date beginDate, Date endDate, Boolean getEntries, - TopiaContext topiaContext) throws LimaException { + TopiaContext topiaContext) throws LimaException, TopiaException { ReportsDatas reportsDatas = new ReportsDatasImpl(); BigDecimal credit = new BigDecimal(0); BigDecimal debit = new BigDecimal(0); BigDecimal solde = new BigDecimal(0); + //String queryAlias = "E"; + EntryDAO entryDAO = LimaCallaoDAOHelper.getEntryDAO(topiaContext); - List<Object[]> results; - String queryAlias = "E"; - if (beginDate != null && endDate != null) { - try { - EntryDAO entryDAO = LimaCallaoDAOHelper.getEntryDAO(topiaContext); + if (getEntries) { + //TopiaQuery entriesQuery = + // createEntryQuery(account, beginDate, endDate, entryDAO, queryAlias); + //IMPORTANT : LOADING ENTRIES AND IS COLUMN FOR NO LAZY EXCEPTION + //entriesQuery.addLoad(Entry.PROPERTY_FINANCIAL_TRANSACTION); + //String loadEntryBookProperty = + // TopiaQuery.getProperty(Entry.PROPERTY_FINANCIAL_TRANSACTION, + // FinancialTransaction.PROPERTY_ENTRY_BOOK); + //entriesQuery.addLoad(loadEntryBookProperty); + List<Entry> listEntries = entryDAO.findAllEntryOfEquilibredTransaction(account, beginDate, endDate); + reportsDatas.setListEntry(listEntries); + } - if (getEntries) { - TopiaQuery entriesQuery = - createEntryQuery(account, beginDate, endDate, entryDAO, queryAlias); - //IMPORTANT : LOADING ENTRIES AND IS COLUMN FOR NO LAZY EXCEPTION - entriesQuery.addLoad(Entry.PROPERTY_FINANCIAL_TRANSACTION); - String loadEntryBookProperty = - TopiaQuery.getProperty(Entry.PROPERTY_FINANCIAL_TRANSACTION, - FinancialTransaction.PROPERTY_ENTRY_BOOK); - entriesQuery.addLoad(loadEntryBookProperty); - reportsDatas.setListEntry(entryDAO.findAllByQuery(entriesQuery)); - } + //TopiaQuery amountsQuery = + // createEntryQuery(account, beginDate, endDate, entryDAO, queryAlias); + //amountsQuery.setSelect("E." + Entry.PROPERTY_DEBIT, "SUM(E." + Entry.PROPERTY_AMOUNT + ")"); + //amountsQuery.addGroup("E." + Entry.PROPERTY_DEBIT); + //results = amountsQuery.execute(topiaContext); + List<Object[]> results = entryDAO.getDebitCreditOfEquilibredTransaction(account, beginDate, endDate); + int nbAmount = results.size(); + if (nbAmount == 2) { + debit = (BigDecimal) results.get(0)[1]; + credit = (BigDecimal) results.get(1)[1]; + } + if (nbAmount == 1) { + if ((Boolean) results.get(0)[0]) { + debit = (BigDecimal) results.get(0)[1]; + } else { + credit = (BigDecimal) results.get(0)[1]; + } + } - TopiaQuery amountsQuery = - createEntryQuery(account, beginDate, endDate, entryDAO, queryAlias); - amountsQuery.setSelect("E." + Entry.PROPERTY_DEBIT, "SUM(E." + Entry.PROPERTY_AMOUNT + ")"); - amountsQuery.addGroup("E." + Entry.PROPERTY_DEBIT); - results = amountsQuery.execute(topiaContext); - int nbAmount = results.size(); - if (nbAmount == 2) { - debit = (BigDecimal) results.get(0)[1]; - credit = (BigDecimal) results.get(1)[1]; - } - if (nbAmount == 1) { - if ((Boolean) results.get(0)[0]) { - debit = (BigDecimal) results.get(0)[1]; - } else { - credit = (BigDecimal) results.get(0)[1]; - } - } + // set the amounts and solde + //solde = debit - credit + solde = solde.add(debit); + solde = solde.subtract(credit); - // set the amounts and solde - //solde = debit - credit - solde = solde.add(debit); - solde = solde.subtract(credit); + if (solde.compareTo(BigDecimal.ZERO) == 1) { + reportsDatas.setSoldeDebit(true); - if (solde.compareTo(BigDecimal.ZERO) == 1) { - reportsDatas.setSoldeDebit(true); - - } - solde = solde.abs(); - - reportsDatas.setAmountCredit(credit); - reportsDatas.setAmountDebit(debit); - reportsDatas.setAmountSolde(solde); - - //commitTransaction(topiaContext); - } catch (TopiaException ex) { - doCatch(topiaContext, ex); - } } + solde = solde.abs(); + reportsDatas.setAmountCredit(credit); + reportsDatas.setAmountDebit(debit); + reportsDatas.setAmountSolde(solde); + return reportsDatas; } Modified: trunk/lima-business/src/test/java/org/chorem/lima/business/AccountServiceImplTest.java =================================================================== --- trunk/lima-business/src/test/java/org/chorem/lima/business/AccountServiceImplTest.java 2012-05-04 09:42:31 UTC (rev 3399) +++ trunk/lima-business/src/test/java/org/chorem/lima/business/AccountServiceImplTest.java 2012-05-04 09:43:18 UTC (rev 3400) @@ -176,6 +176,22 @@ } /** + * Test get all subaccounts. + * + * @throws LimaException + */ + @Test + public void getAllSubAccountsTest() throws LimaException { + Account parent = accountService.getAccountByNumber("50"); + List<Account> listAccount = accountService.getAllSubAccounts(parent); + Assert.assertEquals(2, listAccount.size()); + + parent = accountService.getAccountByNumber("501"); + listAccount = accountService.getAllSubAccounts(parent); + Assert.assertEquals(0, listAccount.size()); + } + + /** * Permet de tester si un compte est bien effacé. * * @throws LimaException Modified: trunk/lima-business-api/src/main/java/org/chorem/lima/business/api/AccountService.java =================================================================== --- trunk/lima-business-api/src/main/java/org/chorem/lima/business/api/AccountService.java 2012-05-04 09:42:31 UTC (rev 3399) +++ trunk/lima-business-api/src/main/java/org/chorem/lima/business/api/AccountService.java 2012-05-04 09:43:18 UTC (rev 3400) @@ -75,6 +75,15 @@ List<Account> getAllLeafAccounts() throws LimaException; /** + * Return all {@code account}-s subaccounts. + * + * @param account account to get sub accounts + * @return sub accounts + * @throws LimaException + */ + List<Account> getAllSubAccounts(Account account) throws LimaException; + + /** * Create new account. If {@code masterAccount} is not null, {@code account} * is added in {@code masterAccount}'s subAccounts. * Modified: trunk/lima-business-api/src/main/java/org/chorem/lima/business/api/ReportService.java =================================================================== --- trunk/lima-business-api/src/main/java/org/chorem/lima/business/api/ReportService.java 2012-05-04 09:42:31 UTC (rev 3399) +++ trunk/lima-business-api/src/main/java/org/chorem/lima/business/api/ReportService.java 2012-05-04 09:43:18 UTC (rev 3400) @@ -103,27 +103,7 @@ Date beginDate, Date endDate) throws LimaException; - /** - * Calculate all credit, debit and solde amounts for the balance. - * <p/> - * Get all entries if true - * - * @param account - * @param beginDate - * @param endDate - * @param getEntries - * @param topiaContext - * @return - * @throws LimaException - */ - ReportsDatas generateSubAccountBalanceWithTransaction(Account account, - Date beginDate, - Date endDate, - Boolean getEntries, - TopiaContext topiaContext) throws LimaException; - - /** * Generation du rapports des journaux. * * @param entryBook Modified: trunk/lima-business-api/src/main/java/org/chorem/lima/business/api/ReportServiceLocal.java =================================================================== --- trunk/lima-business-api/src/main/java/org/chorem/lima/business/api/ReportServiceLocal.java 2012-05-04 09:42:31 UTC (rev 3399) +++ trunk/lima-business-api/src/main/java/org/chorem/lima/business/api/ReportServiceLocal.java 2012-05-04 09:43:18 UTC (rev 3400) @@ -29,6 +29,7 @@ import org.chorem.lima.business.LimaException; import org.chorem.lima.entity.Account; import org.nuiton.topia.TopiaContext; +import org.nuiton.topia.TopiaException; import java.util.Date; @@ -55,6 +56,24 @@ Boolean thirdPartAccountsMode, Date beginDate, Date endDate, - TopiaContext topiaContext) throws LimaException; + TopiaContext topiaContext) throws LimaException, TopiaException; + /** + * Calculate all credit, debit and solde amounts for the balance. + * <p/> + * Get all entries if true + * + * @param account + * @param beginDate + * @param endDate + * @param getEntries + * @param topiaContext + * @return + * @throws LimaException + */ + ReportsDatas generateSubAccountBalanceWithTransaction(Account account, + Date beginDate, + Date endDate, + Boolean getEntries, + TopiaContext topiaContext) throws LimaException, TopiaException; } Modified: trunk/lima-callao/src/main/java/org/chorem/lima/entity/AccountDAO.java =================================================================== --- trunk/lima-callao/src/main/java/org/chorem/lima/entity/AccountDAO.java 2012-05-04 09:42:31 UTC (rev 3399) +++ trunk/lima-callao/src/main/java/org/chorem/lima/entity/AccountDAO.java 2012-05-04 09:43:18 UTC (rev 3400) @@ -33,16 +33,15 @@ import java.util.StringTokenizer; import org.apache.commons.lang3.StringUtils; -import org.apache.commons.logging.Log; -import org.apache.commons.logging.LogFactory; import org.nuiton.topia.TopiaException; -public class AccountDAO extends AccountDAOAbstract<Account> { +public class AccountDAO extends AccountDAOImpl<Account> { - private static final Log log = LogFactory.getLog(AccountDAO.class); - /** * Retourne tous les comptes qui n'ont pas eux meme de sous compte. + * + * @return leaf accounts + * @throws TopiaException */ public List<Account> findAllLeafAccounts() throws TopiaException { // FIXME echatellier 20120413 la requete ne fonctionne pas @@ -94,6 +93,21 @@ } /** + * Retourne tous les comptes dont le numero commence par celui specifié. + * + * @param account parent account + * @return + * @throws TopiaException + */ + public List<Account> findAllSubAccounts(Account account) throws TopiaException { + + String query = "FROM " + Account.class.getName() + " a WHERE a.accountNumber LIKE concat(:accountNumber, '_%')"; + List<Account> accounts = context.find(query, "accountNumber", account.getAccountNumber()); + + return accounts; + } + + /** * @deprecated since 0.6, business method, need to be moved out of dao */ @Deprecated Modified: trunk/lima-callao/src/main/java/org/chorem/lima/entity/ClosedPeriodicEntryBookDAO.java =================================================================== --- trunk/lima-callao/src/main/java/org/chorem/lima/entity/ClosedPeriodicEntryBookDAO.java 2012-05-04 09:42:31 UTC (rev 3399) +++ trunk/lima-callao/src/main/java/org/chorem/lima/entity/ClosedPeriodicEntryBookDAO.java 2012-05-04 09:43:18 UTC (rev 3400) @@ -5,7 +5,7 @@ * $Id$ * $HeadURL$ * %% - * Copyright (C) 2008 - 2010 CodeLutin + * Copyright (C) 2008 - 2012 CodeLutin, Chatellier Eric * %% * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as @@ -28,16 +28,10 @@ import java.util.Date; import java.util.List; -import org.apache.commons.logging.Log; -import org.apache.commons.logging.LogFactory; import org.nuiton.topia.TopiaException; -import org.nuiton.topia.framework.TopiaQuery; -import org.nuiton.topia.framework.TopiaQuery.Op; public class ClosedPeriodicEntryBookDAO extends ClosedPeriodicEntryBookDAOImpl<ClosedPeriodicEntryBook> { - private static final Log log = LogFactory.getLog(ClosedPeriodicEntryBookDAO.class); - /** * Find all ClosedPeriodicEntryBook with common EntryBook. * @@ -49,33 +43,64 @@ return findAllByProperties(ClosedPeriodicEntryBook.PROPERTY_ENTRY_BOOK, entryBook); } - /** Return ClosedPeriodicEntryBook by EntryBook and FinancialPeriod */ + /** + * Return ClosedPeriodicEntryBook by EntryBook and FinancialPeriod. + * + * @param entryBook + * @param financialPeriod + * @return ClosedPeriodicEntryBook + * @throws TopiaException + */ public ClosedPeriodicEntryBook findByEntryBookAndFinancialPeriod( EntryBook entryBook, FinancialPeriod financialPeriod) throws TopiaException { - TopiaQuery query = createQuery(); + List<ClosedPeriodicEntryBook> closedPeriodicEntryBooks; + String query = "FROM " + ClosedPeriodicEntryBook.class.getName(); if (entryBook != null) { - query.addEquals(ClosedPeriodicEntryBook.PROPERTY_ENTRY_BOOK, entryBook); + query += " WHERE entryBook = :entryBook"; + if (financialPeriod != null) { + query += " AND financialPeriod = :financialPeriod"; + //context.find(query, 0, 0, "entryBook", entryBook, "financialPeriod", financialPeriod); + closedPeriodicEntryBooks = context.find(query, "entryBook", entryBook, "financialPeriod", financialPeriod); + } else { + //context.find(query, 0, 0, "entryBook", entryBook); + closedPeriodicEntryBooks = context.find(query, "entryBook", entryBook); + } + } else { + if (financialPeriod != null) { + query += " WHERE financialPeriod = :financialPeriod"; + } + + //context.find(query, 0, 0, "financialPeriod", financialPeriod); + closedPeriodicEntryBooks = context.find(query, "financialPeriod", financialPeriod); } - if (financialPeriod != null) { - query.addEquals(ClosedPeriodicEntryBook.PROPERTY_FINANCIAL_PERIOD, financialPeriod); + + // get only first one + ClosedPeriodicEntryBook result = null; + if (!closedPeriodicEntryBooks.isEmpty()) { + result = closedPeriodicEntryBooks.get(0); } - return findByQuery(query); + return result; } + /** + * Retourne toutes les ClosedPeriodicEntryBook par interval de date + * sur les periodes sur lequelles elles portent ordonnée par journal. + * + * @param beginDate begin date + * @param endDate end date + * @return all ClosedPeriodicEntryBook between begin and end + * @throws TopiaException + */ public List<ClosedPeriodicEntryBook> findAllByDates(Date beginDate, Date endDate) throws TopiaException { - TopiaQuery query = createQuery(); - if (beginDate != null && endDate != null) { - String beginDateProperty = TopiaQuery.getProperty(ClosedPeriodicEntryBook.PROPERTY_FINANCIAL_PERIOD, FinancialPeriod.PROPERTY_BEGIN_DATE); - String endDateProperty = TopiaQuery.getProperty(ClosedPeriodicEntryBook.PROPERTY_FINANCIAL_PERIOD, FinancialPeriod.PROPERTY_BEGIN_DATE); - String entrybookProperty = TopiaQuery.getProperty(ClosedPeriodicEntryBook.PROPERTY_ENTRY_BOOK, EntryBook.PROPERTY_CODE); - query.addWhere(beginDateProperty, Op.GE, beginDate) - .addWhere(endDateProperty, Op.LE, endDate) - .addOrder(beginDateProperty, entrybookProperty); - } - return (List<ClosedPeriodicEntryBook>) findAllByQuery(query); + + String query = "FROM " + ClosedPeriodicEntryBook.class.getName() + + " WHERE :begindate <= financialPeriod.beginDate " + + " AND financialPeriod.beginDate <= :endDate" + + " ORDER BY entryBook.code"; + return context.find(query, 0d, 0d, "beginDate", beginDate, "endDate", endDate); } } Added: trunk/lima-callao/src/main/java/org/chorem/lima/entity/EntryDAO.java =================================================================== --- trunk/lima-callao/src/main/java/org/chorem/lima/entity/EntryDAO.java (rev 0) +++ trunk/lima-callao/src/main/java/org/chorem/lima/entity/EntryDAO.java 2012-05-04 09:43:18 UTC (rev 3400) @@ -0,0 +1,97 @@ +/* + * #%L + * Lima callao + * + * $Id$ + * $HeadURL$ + * %% + * Copyright (C) 2008 - 2012 CodeLutin, Chatellier Eric + * %% + * This program 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 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 General Public License for more details. + * + * You should have received a copy of the GNU General Public + * License along with this program. If not, see + * <http://www.gnu.org/licenses/gpl-3.0.html>. + * #L% + */ + +package org.chorem.lima.entity; + +import java.util.Date; +import java.util.List; + +import org.nuiton.topia.TopiaException; + +public class EntryDAO extends EntryDAOImpl<Entry> { + + /** + * Requete generique qui recupere les entrees equilibrées portant sur un + * compte entre deux dates. + * + * @param account account + * @param beginDate begin date + * @param endDate end date + */ + protected String getEquilibredTransactionQuery(Account account, + Date beginDate, Date endDate) { + String query = "FROM " + Entry.class.getName() + " E" + + // equlibrée (somme des débit = somme des crédit) + " WHERE (select sum(E2.amount) from " + Entry.class.getName() + " E2 where E2.debit = false and E2.financialTransaction = E.financialTransaction) = " + + "(select sum(E2.amount) from " + Entry.class.getName() + " E2 where E2.debit = true and E2.financialTransaction = E.financialTransaction)" + + // entre les 2 dates + " AND :beginDate <= E.financialTransaction.transactionDate" + + " AND E.financialTransaction.transactionDate <= :endDate" + + // concerne le compte + " AND E.account = :account"; + return query; + } + + /** + * Query for find entries for accountsreports and balancereports + * Just exact and balanced transaction are calculated. + * + * @param account account + * @param beginDate begin date + * @param endDate end date + * @return entries + * @throws TopiaException + */ + public List<Entry> findAllEntryOfEquilibredTransaction(Account account, + Date beginDate, Date endDate) throws TopiaException { + + String query = getEquilibredTransactionQuery(account, beginDate, endDate); + + List<Entry> entries = context.find(query, "beginDate", beginDate, + "endDate", endDate, "account", account); + return entries; + } + + /** + * Retourne la somme des entrées des transaction equilibrées entre + * deux dates pour un compte donné. + * + * @param account le compte + * @param beginDate bebin date + * @param endDate end date + * @return list boolean,int (une ligne pour le debit true, une ligne pour le credit) + * @throws TopiaException + */ + public List<Object[]> getDebitCreditOfEquilibredTransaction(Account account, + Date beginDate, Date endDate) throws TopiaException { + String query = "SELECT E.debit, sum(E.amount) " + + getEquilibredTransactionQuery(account, beginDate, endDate) + + " GROUP BY E.debit"; + + List<Object[]> result = context.find(query, "beginDate", beginDate, + "endDate", endDate, "account", account); + return result; + } +} Property changes on: trunk/lima-callao/src/main/java/org/chorem/lima/entity/EntryDAO.java ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision HeadURL Modified: trunk/lima-callao/src/main/java/org/chorem/lima/entity/EntryImpl.java =================================================================== --- trunk/lima-callao/src/main/java/org/chorem/lima/entity/EntryImpl.java 2012-05-04 09:42:31 UTC (rev 3399) +++ trunk/lima-callao/src/main/java/org/chorem/lima/entity/EntryImpl.java 2012-05-04 09:43:18 UTC (rev 3400) @@ -5,7 +5,7 @@ * $Id$ * $HeadURL$ * %% - * Copyright (C) 2008 - 2010 CodeLutin + * Copyright (C) 2008 - 2012 CodeLutin, Chatellier Eric * %% * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as Modified: trunk/lima-callao/src/main/java/org/chorem/lima/entity/FinancialPeriodDAO.java =================================================================== --- trunk/lima-callao/src/main/java/org/chorem/lima/entity/FinancialPeriodDAO.java 2012-05-04 09:42:31 UTC (rev 3399) +++ trunk/lima-callao/src/main/java/org/chorem/lima/entity/FinancialPeriodDAO.java 2012-05-04 09:43:18 UTC (rev 3400) @@ -5,7 +5,7 @@ * $Id$ * $HeadURL$ * %% - * Copyright (C) 2008 - 2010 CodeLutin + * Copyright (C) 2008 - 2012 CodeLutin, Chatellier Eric * %% * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as @@ -25,26 +25,34 @@ package org.chorem.lima.entity; +import java.util.Date; +import java.util.List; + import org.nuiton.topia.TopiaException; -import org.nuiton.topia.framework.TopiaQuery; -import java.util.Date; - public class FinancialPeriodDAO extends FinancialPeriodDAOImpl<FinancialPeriod> { /** - * Return FinancialPeriod by Date - * Date is include between financialperiod begin and end date + * Return FinancialPeriod by Date. + * Date is include between financialperiod begin and end date. + * + * @param date period middle date + * @return FinancialPeriod for {@code date} + * @throws TopiaException */ public FinancialPeriod findByDate(Date date) throws TopiaException { - TopiaQuery query = createQuery(); - if (date != null) { - query.addWhere(FinancialPeriod.PROPERTY_BEGIN_DATE + " <= :value") - .addWhere(FinancialPeriod.PROPERTY_END_DATE + " >= :value") - .addParam("value", date); + String query = "FROM " + FinancialPeriod.class.getName() + + " WHERE beginDate <= :date" + + " AND :date <= endDate"; + + // add unique result here + List<FinancialPeriod> financialPeriod = context.find(query, "date", date); + FinancialPeriod result = null; + if (!financialPeriod.isEmpty()) { + result = financialPeriod.get(0); } - return findByQuery(query); + return result; } } Modified: trunk/lima-callao/src/main/java/org/chorem/lima/entity/FinancialStatementImpl.java =================================================================== --- trunk/lima-callao/src/main/java/org/chorem/lima/entity/FinancialStatementImpl.java 2012-05-04 09:42:31 UTC (rev 3399) +++ trunk/lima-callao/src/main/java/org/chorem/lima/entity/FinancialStatementImpl.java 2012-05-04 09:43:18 UTC (rev 3400) @@ -5,7 +5,7 @@ * $Id$ * $HeadURL$ * %% - * Copyright (C) 2008 - 2010 CodeLutin + * Copyright (C) 2008 - 2012 CodeLutin, Chatellier Eric * %% * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as Modified: trunk/lima-callao/src/main/java/org/chorem/lima/entity/VatStatementImpl.java =================================================================== --- trunk/lima-callao/src/main/java/org/chorem/lima/entity/VatStatementImpl.java 2012-05-04 09:42:31 UTC (rev 3399) +++ trunk/lima-callao/src/main/java/org/chorem/lima/entity/VatStatementImpl.java 2012-05-04 09:43:18 UTC (rev 3400) @@ -5,7 +5,7 @@ * $Id$ * $HeadURL$ * %% - * Copyright (C) 2008 - 2011 CodeLutin + * Copyright (C) 2008 - 2012 CodeLutin, Chatellier Eric * %% * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as