Author: Bavencoff Date: 2013-06-27 15:12:22 +0200 (Thu, 27 Jun 2013) New Revision: 3688 Url: http://chorem.org/projects/lima/repository/revisions/3688 Log: Modification dans les service du syst?\195?\168me de recherche avec filtre Added: trunk/lima-callao/src/main/java/org/chorem/lima/entity/GeneratorHQuery.java Modified: trunk/lima-business-api/src/main/java/org/chorem/lima/business/api/FinancialTransactionService.java trunk/lima-business/src/main/java/org/chorem/lima/business/ejb/FinancialTransactionServiceImpl.java trunk/lima-callao/src/main/java/org/chorem/lima/entity/FinancialTransactionDAOImpl.java Modified: trunk/lima-business/src/main/java/org/chorem/lima/business/ejb/FinancialTransactionServiceImpl.java =================================================================== --- trunk/lima-business/src/main/java/org/chorem/lima/business/ejb/FinancialTransactionServiceImpl.java 2013-06-27 13:08:13 UTC (rev 3687) +++ trunk/lima-business/src/main/java/org/chorem/lima/business/ejb/FinancialTransactionServiceImpl.java 2013-06-27 13:12:22 UTC (rev 3688) @@ -27,7 +27,9 @@ import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; -import org.chorem.lima.beans.FinancialTransactionSearch; +import org.chorem.lima.Filter.FilterGenerator; +import org.chorem.lima.Filter.FinancialTransactionFilter; +import org.chorem.lima.beans.FinancialTransactionCondition; import org.chorem.lima.beans.LetteringFilter; import org.chorem.lima.business.AccountingRules; import org.chorem.lima.business.LimaConfig; @@ -40,7 +42,6 @@ import org.chorem.lima.business.api.ReportService; import org.chorem.lima.business.utils.LetteringComparator; 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; @@ -614,22 +615,18 @@ } @Override - public List<FinancialTransaction> searchFinancialTransaction(FinancialTransactionSearch financialTransactionSearch) throws LimaException { + public List<FinancialTransaction> searchFinancialTransaction(FinancialTransactionCondition financialTransactionCondition) throws LimaException { List<FinancialTransaction> result; - try { - // first search on account dao - String accountsList = financialTransactionSearch.getAccountList(); - List<Account> accounts = null; - if (accountsList != null) { - AccountDAO accountDAO = getDaoHelper().getAccountDAO(); - accounts = accountDAO.stringToListAccounts(accountsList, false); - } + FilterGenerator generator = new FilterGenerator(); + financialTransactionCondition.accept(generator); + FinancialTransactionFilter filter = generator.getFilter(); + + try { // next on financial transaction dao FinancialTransactionDAO financialTransactionDAO = getDaoHelper().getFinancialTransactionDAO(); - result = financialTransactionDAO.searchFinancialTransaction( - financialTransactionSearch, accounts); + result = financialTransactionDAO.searchFinancialTransaction(filter); } catch (Exception ex) { Modified: trunk/lima-business-api/src/main/java/org/chorem/lima/business/api/FinancialTransactionService.java =================================================================== --- trunk/lima-business-api/src/main/java/org/chorem/lima/business/api/FinancialTransactionService.java 2013-06-27 13:08:13 UTC (rev 3687) +++ trunk/lima-business-api/src/main/java/org/chorem/lima/business/api/FinancialTransactionService.java 2013-06-27 13:12:22 UTC (rev 3688) @@ -25,7 +25,7 @@ package org.chorem.lima.business.api; -import org.chorem.lima.beans.FinancialTransactionSearch; +import org.chorem.lima.beans.FinancialTransactionCondition; import org.chorem.lima.beans.LetteringFilter; import org.chorem.lima.business.LimaException; import org.chorem.lima.entity.Entry; @@ -68,7 +68,7 @@ List<FinancialTransaction> getAllFinancialTransactions(FiscalPeriod period) throws LimaException; - List<FinancialTransaction> searchFinancialTransaction(FinancialTransactionSearch financialTransactionSearch) throws LimaException; + List<FinancialTransaction> searchFinancialTransaction(FinancialTransactionCondition financialTransactionFilter) throws LimaException; Entry createEntry(Entry entry) throws LimaException; Modified: trunk/lima-callao/src/main/java/org/chorem/lima/entity/FinancialTransactionDAOImpl.java =================================================================== --- trunk/lima-callao/src/main/java/org/chorem/lima/entity/FinancialTransactionDAOImpl.java 2013-06-27 13:08:13 UTC (rev 3687) +++ trunk/lima-callao/src/main/java/org/chorem/lima/entity/FinancialTransactionDAOImpl.java 2013-06-27 13:12:22 UTC (rev 3688) @@ -25,18 +25,12 @@ package org.chorem.lima.entity; -import org.apache.commons.collections.CollectionUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; -import org.chorem.lima.FinancialStatementWayEnum; -import org.chorem.lima.beans.FinancialTransactionSearch; +import org.chorem.lima.Filter.Filter; import org.hibernate.HibernateException; import org.nuiton.topia.TopiaException; -import java.math.BigDecimal; -import java.text.DecimalFormat; -import java.text.ParsePosition; -import java.util.ArrayList; import java.util.Date; import java.util.List; @@ -271,145 +265,31 @@ /** * Search financial transaction. * - * @param financialTransactionSearch search parameters - * @param accounts search accounts + * + * @param filter filter parameters * @return financial transaction * @throws TopiaException */ - public List<FinancialTransaction> searchFinancialTransaction(FinancialTransactionSearch financialTransactionSearch, - List<Account> accounts) throws TopiaException { - String query = "SELECT distinct T FROM " + FinancialTransaction.class.getName() + " T"+ - " JOIN T.entry AS E" + - " WHERE 1=1 "; // to not manage first where in all condition + public List<FinancialTransaction> searchFinancialTransaction(Filter filter) throws TopiaException { - List<Object> args = new ArrayList<Object>(); - - // dates - Date beginDate = financialTransactionSearch.getBeginDate(); - Date endDate = financialTransactionSearch.getEndDate(); - if (beginDate != null && endDate != null) { - query += " AND (:beginDate <= T.transactionDate" + - " AND T.transactionDate <= :endDate)"; - args.add("beginDate"); - args.add(beginDate); - args.add("endDate"); - args.add(endDate); + GeneratorHQuery generator = new GeneratorHQuery(); + filter.accept(generator); - if (log.isDebugEnabled()) { - log.debug("Begin date :" + beginDate); - log.debug("End date :" + endDate); - } - } else if (beginDate != null) { - query += " AND :beginDate = T.transactionDate"; - args.add(beginDate); - args.add("beginDate"); - } + String query = generator.getHQuery(); + List<Object> arguments = generator.getArguments(); - // voucher - String voucher = financialTransactionSearch.getVoucher(); - if (voucher != null) { - query += " AND E.voucher LIKE :voucher"; - args.add("voucher"); - args.add("%" + voucher + "%"); - } - - // description - String description = financialTransactionSearch.getDescription(); - if (description != null) { - query += " AND E.description LIKE :description"; - args.add("description"); - args.add("%" + description + "%"); - } - - // letter - String lettering = financialTransactionSearch.getLettering(); - if (lettering != null) { - query += " AND E.lettering = :lettering"; - args.add("lettering"); - args.add(lettering); - if (log.isDebugEnabled()) { - log.debug("Lettering filter : -" + lettering + "-"); - } - } - - // accounts - Account account = financialTransactionSearch.getAccount(); - //String accountsList = financialTransactionSearch.getAccountList(); - if (account != null) { - query += " AND E.account = :account"; - args.add("account"); - args.add(account); - } else if (CollectionUtils.isNotEmpty(accounts)) { - StringBuffer accountNumbers = new StringBuffer(); - for (int i = 0; i < accounts.size(); i++) { - accountNumbers.append(accounts.get(i - 1).getAccountNumber()); - if (i < accounts.size() - 1) { - accountNumbers.append(","); - } - } - if (accounts.size() > 0) { - query += " AND E.accountNumber in (" + accountNumbers + ")"; - } - - } - - // entry book - EntryBook entryBook = financialTransactionSearch.getEntryBook(); - if (entryBook != null) { - query += " AND T." + FinancialTransaction.PROPERTY_ENTRY_BOOK + " = :entryBook"; - args.add("entryBook"); - args.add(entryBook); - } - - // amounts - String amount = financialTransactionSearch.getAmount(); - String amount2 = financialTransactionSearch.getAmount2(); - String operator = financialTransactionSearch.getOperator(); - FinancialStatementWayEnum fStWayEnum = financialTransactionSearch.getAmountWayEnum(); - if (amount != null) { - DecimalFormat decimalFormat = new DecimalFormat(); - BigDecimal amountBigDecimal = new BigDecimal(decimalFormat.parse(amount, new ParsePosition(0)).toString()); - if (log.isDebugEnabled()) { - log.debug("Value of first amount : " + amount); - log.debug("Value of first amount - BigDecimal : " + amountBigDecimal); - } - if (amount2 != null) { - BigDecimal secondAmountBigDecimal = new BigDecimal(decimalFormat.parse(amount2, new ParsePosition(0)).toString()); - if (log.isDebugEnabled()) { - log.debug("Value of second amount : " + secondAmountBigDecimal); - } - query += " AND :amount <= E.amount AND E.amount <= :amount2"; - args.add("amount"); - args.add(amountBigDecimal); - args.add("amount2"); - args.add(secondAmountBigDecimal); - } else if (operator != null) { - if (log.isDebugEnabled()) { - log.debug("Value of first amount - BigDecimal, where operator not null : " + amountBigDecimal); - } - query += " AND E.amount " + operator + " :amount"; - args.add("amount"); - args.add(amountBigDecimal); - } - switch (fStWayEnum) { - case CREDIT: - query += " AND E.debit = false"; - break; - case DEBIT: - query += " AND E.debit = true"; - break; - } - } - - query += " order by T.transactionDate desc"; - if (log.isDebugEnabled()) { - log.debug("Generated query : " + query); + log.debug("Query : \n" + query + "\nArguments: " + arguments); } // perform query - List<FinancialTransaction> result = context.findAll(query, args.toArray()); + List<FinancialTransaction> result; + if (arguments.isEmpty()) { + result = context.findAll(query); + } else { + result = context.findAll(query, arguments.toArray()); + } if (log.isDebugEnabled()) { log.debug("Number of founded transactions : " + result.size()); Added: trunk/lima-callao/src/main/java/org/chorem/lima/entity/GeneratorHQuery.java =================================================================== --- trunk/lima-callao/src/main/java/org/chorem/lima/entity/GeneratorHQuery.java (rev 0) +++ trunk/lima-callao/src/main/java/org/chorem/lima/entity/GeneratorHQuery.java 2013-06-27 13:12:22 UTC (rev 3688) @@ -0,0 +1,333 @@ +package org.chorem.lima.entity; + +import org.chorem.lima.Clause.AndClause; +import org.chorem.lima.Clause.BooleanClause; +import org.chorem.lima.Clause.Clause; +import org.chorem.lima.Clause.DateClause; +import org.chorem.lima.Clause.NotClause; +import org.chorem.lima.Clause.NumberClause; +import org.chorem.lima.Clause.OrClause; +import org.chorem.lima.Clause.SetClause; +import org.chorem.lima.Clause.StringClause; +import org.chorem.lima.Clause.SubFilterClause; +import org.chorem.lima.Clause.VisitorClause; +import org.chorem.lima.Filter.AccountFilter; +import org.chorem.lima.Filter.EntryBookFilter; +import org.chorem.lima.Filter.EntryFilter; +import org.chorem.lima.Filter.Filter; +import org.chorem.lima.Filter.FinancialTransactionFilter; +import org.chorem.lima.Filter.VisitorFilter; + +import java.util.ArrayList; +import java.util.Deque; +import java.util.HashMap; +import java.util.LinkedList; +import java.util.List; +import java.util.Map; + +/** + * @author Sylvain Bavencoff <bavencoff@codelutin.com> + */ +public class GeneratorHQuery implements VisitorFilter, VisitorClause { + + protected String fromClause; + + protected String whereClause; + + protected Map<String, Object> parameters; + + protected Map<String, Integer> parametersCount; + + protected Map<Filter, String> aliasMap; + + protected Map<String, Integer> aliasCount; + + protected Deque<String> fromClauseStack; + protected Deque<String> whereClauseStak; + + protected Deque<Filter> filterStack; + + public GeneratorHQuery() { + fromClause = ""; + whereClause = ""; + parameters = new HashMap<String, Object>(); + parametersCount = new HashMap<String, Integer>(); + aliasMap = new HashMap<Filter, String>(); + aliasCount = new HashMap<String, Integer>(); + filterStack = new LinkedList<Filter>(); + fromClauseStack = new LinkedList<String>(); + whereClauseStak = new LinkedList<String>(); + } + + protected String addParameter(Object value) { + String type = value.getClass().getSimpleName(); + Integer count; + if (parametersCount.containsKey(type)) { + count = parametersCount.get(type); + count++; + } else { + count = 1; + } + parametersCount.put(type, count); + String parameter = type + "_" + count; + parameters.put(parameter, value); + return type + "_" + count; + } + + protected String newAlias(Filter filter) { + String name = filter.getEntityClass().getSimpleName(); + Integer count; + if (aliasCount.containsKey(name)) { + count = aliasCount.get(name); + count++; + } else { + count = 1; + } + aliasCount.put(name, count); + String alias = name + count; + aliasMap.put(filter, alias); + return alias; + } + + public List<Object> getArguments() { + List<Object> result = new ArrayList<Object>(); + for (Map.Entry<String, Object> entry : parameters.entrySet()) { + result.add(entry.getKey()); + result.add(entry.getValue()); + } + return result; + } + + public String getHQuery() { + return fromClause + whereClause; + } + + protected void visitFilter(Filter filter) { + String alias = newAlias(filter); + if (fromClause.isEmpty()) { + fromClause = "SELECT DISTINCT " + alias; + fromClause += "\nFROM " + filter.getEntityClass().getName() + " AS " + alias; + if (filter.getClause() != null) { + whereClause = "\nWHERE "; + } + } else { + fromClause += " AS " + alias; + } + if (filter.getClause() != null) { + filterStack.addFirst(filter); + filter.getClause().accept(this); + filterStack.removeFirst(); + } + } + + @Override + public void visitAccountFilter(AccountFilter accountFilter) { + visitFilter(accountFilter); + } + + @Override + public void visitEntryBookFilter(EntryBookFilter entryBookFilter) { + visitFilter(entryBookFilter); + } + + @Override + public void visitEntryFilter(EntryFilter entryFilter) { + visitFilter(entryFilter); + } + + @Override + public void visitFinancialTransactionFilter(FinancialTransactionFilter financialTransactionFilter) { + visitFilter(financialTransactionFilter); + } + + @Override + public void visitDateClause(DateClause dateClause) { + String parameter = addParameter(dateClause.getValue()); + Filter filter = filterStack.peek(); + String alias = aliasMap.get(filter); + String operand = " = " ; + switch (dateClause.getOperand()) { + case SAME: + operand = " = "; + break; + case PREVIOUS: + operand = " <= "; + break; + case AFTER: + operand = " >= "; + break; + case DIFFERENT: + operand = " != "; + break; + } + whereClause += alias + "." + dateClause.getProperty() + operand + ":" + parameter; + } + + @Override + public void visitAndClause(AndClause andClause) { + boolean first = true; + whereClause += "("; + for (Clause clause: andClause) { + if (first) { + first = false; + } else { + whereClause += "\nAND "; + } + clause.accept(this); + } + whereClause += ")"; + } + + @Override + public void visitBooleanClause(BooleanClause booleanClause) { + String parameter = addParameter(true); + Filter filter = filterStack.peek(); + String alias = aliasMap.get(filter); + whereClause += alias + "." + booleanClause.getProperty() + " = :" + parameter; + } + + @Override + public void visitNumberClause(NumberClause numberClause) { + String parameter = addParameter(numberClause.getValue()); + Filter filter = filterStack.peek(); + String alias = aliasMap.get(filter); + String operand = " = " ; + switch (numberClause.getOperand()) { + case EQUAL: + operand = " = "; + break; + case NOT_EQUAL: + operand = " != "; + break; + case LOWER: + operand = " < "; + break; + case LOWER_OR_EQUAL: + operand = " <= "; + break; + case UPPER: + operand = " > "; + break; + case UPPER_OR_EQUAL: + operand = " >= "; + break; + } + whereClause += alias + "." + numberClause.getProperty() + operand + ":" + parameter; + } + + @Override + public void visitOrClause(OrClause orClause) { + boolean first = true; + whereClause += "("; + for (Clause clause: orClause) { + if (first) { + first = false; + } else { + whereClause += "\nOR "; + } + clause.accept(this); + } + whereClause += ")"; + } + + @Override + public void visitNotClause(NotClause notClause) { + whereClause += "NOT "; + Clause clause = notClause.getClause(); + clause.accept(this); + } + + @Override + public void visitSetClause(SetClause setClause) { + Filter filter = filterStack.peek(); + String alias = aliasMap.get(filter); + String operand = " = " ; + switch (setClause.getOperand()) { + case IN: + operand = " IN "; + break; + case NOT_IN: + operand = " NOT IN "; + break; + } + whereClause += alias + "." + setClause.getProperty() + operand + "("; + + boolean first = true; + for (Object object : setClause) { + if (first) { + first = false; + } else { + whereClause += ", "; + } + String parameter = addParameter(object); + whereClause += ":" + parameter; + } + whereClause += ")"; + } + + @Override + public void visitStringClause(StringClause stringClause) { + Filter filter = filterStack.peek(); + String alias = aliasMap.get(filter); + String operand = " = " ; + String value = stringClause.getValue(); + switch (stringClause.getOperand()) { + case EQUAL: + operand = " = "; + value = stringClause.getValue(); + break; + case NOT_EQUAL: + operand = " != "; + value = stringClause.getValue(); + break; + case BEGIN: + operand = " LIKE "; + value = stringClause.getValue() + "%"; + break; + case ENDING: + operand = " LIKE "; + value = "%" + stringClause.getValue(); + break; + case CONTAIN: + operand = " LIKE "; + value = "%" + stringClause.getValue()+ "%"; + break; + } + if (stringClause.isSensitiveCase()) { + whereClause += alias + "." + stringClause.getProperty() + operand + "'" + value + "'"; + } else { + whereClause += "lower(" + alias + "." + stringClause.getProperty() + ")" + operand + "'" + value.toLowerCase() + "'"; + } + } + + @Override + public void visitSubFilterClause(SubFilterClause subFilterClause) { + Filter filter = filterStack.peek(); + String alias = aliasMap.get(filter); + + Filter subFilter; + switch (subFilterClause.getMultiplicity()) { + case ONE: + fromClauseStack.addFirst(fromClause); + fromClause = ""; + whereClauseStak.addFirst(whereClause); + whereClause = ""; + + subFilter = subFilterClause.getSubFilter(); + subFilter.accept(this); + + String clause = alias + "." + subFilterClause.getProperty() + + " IN (" + fromClause + whereClause + ")"; + + fromClause = fromClauseStack.removeFirst(); + whereClause = whereClauseStak.removeFirst() + clause; + break; + case MANY: + fromClause += "\nLEFT JOIN " + alias + "." + subFilterClause.getProperty(); + subFilter = subFilterClause.getSubFilter(); + subFilter.accept(this); + break; + } + } + +}