Author: kmorin Date: 2014-09-24 14:35:48 +0200 (Wed, 24 Sep 2014) New Revision: 640 Url: http://forge.codelutin.com/projects/faxtomail/repository/revisions/640 Log: - filtre sur les quantit?\195?\169s plut?\195?\180t que sur les gammes Modified: trunk/faxtomail-persistence/src/main/java/com/franciaflex/faxtomail/persistence/entities/EmailFilter.java trunk/faxtomail-persistence/src/main/java/com/franciaflex/faxtomail/persistence/entities/EmailTopiaDao.java trunk/faxtomail-service/src/main/java/com/franciaflex/faxtomail/services/service/EmailService.java trunk/faxtomail-service/src/main/java/com/franciaflex/faxtomail/services/service/EmailServiceImpl.java trunk/faxtomail-ui-swing/src/main/java/com/franciaflex/faxtomail/ui/swing/actions/SaveDemandeAndExitAction.java trunk/faxtomail-ui-swing/src/main/java/com/franciaflex/faxtomail/ui/swing/content/demande/DemandeListTableFilter.java Modified: trunk/faxtomail-persistence/src/main/java/com/franciaflex/faxtomail/persistence/entities/EmailFilter.java =================================================================== --- trunk/faxtomail-persistence/src/main/java/com/franciaflex/faxtomail/persistence/entities/EmailFilter.java 2014-09-24 09:23:49 UTC (rev 639) +++ trunk/faxtomail-persistence/src/main/java/com/franciaflex/faxtomail/persistence/entities/EmailFilter.java 2014-09-24 12:35:48 UTC (rev 640) @@ -48,9 +48,9 @@ public static final String PROPERTY_RECEPTION_DATES = "receptionDates"; public static final String PROPERTY_RECIPIENTS = "recipients"; public static final String PROPERTY_DEMAND_STATUS = "demandStatus"; - public static final String PROPERTY_PRODUCT_RANGES = "productsRanges"; - public static final String PROPERTY_SAV_RANGES = "savRanges"; - public static final String PROPERTY_QUOTATION_RANGES = "quotationRanges"; + public static final String PROPERTY_PRODUCT_QUANTITIES = "productsQuantities"; + public static final String PROPERTY_SAV_QUANTITIES = "savQuantities"; + public static final String PROPERTY_QUOTATION_QUANTITIES = "quotationQuantities"; public static final String PROPERTY_TAKEN_BYS = "takenBys"; public static final String PROPERTY_COMMENTS = "comments"; public static final String PROPERTY_EDI_CODE_NUMBERS = "ediCodeNumbers"; @@ -82,11 +82,11 @@ protected Set<DemandStatus> demandStatus; - protected Set<Range> productsRanges; + protected Set<Long> productsQuantities; - protected Set<Range> savRanges; + protected Set<Long> savQuantities; - protected Set<Range> quotationRanges; + protected Set<Long> quotationQuantities; protected Set<FaxToMailUser> takenBys; @@ -206,31 +206,31 @@ firePropertyChange(PROPERTY_DEMAND_STATUS, null, demandStatus); } - public Set<Range> getProductsRanges() { - return productsRanges; + public Set<Long> getProductsQuantities() { + return productsQuantities; } - public void setProductsRanges(Set<Range> productsRanges) { - this.productsRanges = productsRanges; - firePropertyChange(PROPERTY_PRODUCT_RANGES, null, productsRanges); + public void setProductsQuantities(Set<Long> productsQuantities) { + this.productsQuantities = productsQuantities; + firePropertyChange(PROPERTY_PRODUCT_QUANTITIES, null, productsQuantities); } - public Set<Range> getSavRanges() { - return savRanges; + public Set<Long> getSavQuantities() { + return savQuantities; } - public void setSavRanges(Set<Range> savRanges) { - this.savRanges = savRanges; - firePropertyChange(PROPERTY_SAV_RANGES, null, savRanges); + public void setSavQuantities(Set<Long> savQuantities) { + this.savQuantities = savQuantities; + firePropertyChange(PROPERTY_SAV_QUANTITIES, null, savQuantities); } - public Set<Range> getQuotationRanges() { - return quotationRanges; + public Set<Long> getQuotationQuantities() { + return quotationQuantities; } - public void setQuotationRanges(Set<Range> quotationRanges) { - this.quotationRanges = quotationRanges; - firePropertyChange(PROPERTY_QUOTATION_RANGES, null, quotationRanges); + public void setQuotationQuantities(Set<Long> quotationQuantities) { + this.quotationQuantities = quotationQuantities; + firePropertyChange(PROPERTY_QUOTATION_QUANTITIES, null, quotationQuantities); } public Set<FaxToMailUser> getTakenBys() { @@ -282,7 +282,7 @@ setClientCodes(null); setWaitingStates(null); setDemandStatus(null); - setQuotationRanges(null); + setQuotationQuantities(null); setPriorities(null); setClientBrands(null); setComments(null); @@ -294,10 +294,10 @@ setLocalReferences(null); setReferences(null); setProjectReferences(null); - setProductsRanges(null); + setProductsQuantities(null); setReceptionDates(null); setRecipients(null); - setSavRanges(null); + setSavQuantities(null); setSenders(null); } } Modified: trunk/faxtomail-persistence/src/main/java/com/franciaflex/faxtomail/persistence/entities/EmailTopiaDao.java =================================================================== --- trunk/faxtomail-persistence/src/main/java/com/franciaflex/faxtomail/persistence/entities/EmailTopiaDao.java 2014-09-24 09:23:49 UTC (rev 639) +++ trunk/faxtomail-persistence/src/main/java/com/franciaflex/faxtomail/persistence/entities/EmailTopiaDao.java 2014-09-24 12:35:48 UTC (rev 640) @@ -706,48 +706,36 @@ query.append(")"); } - Set<Range> productRanges = emailFilter.getProductsRanges(); - if (CollectionUtils.isNotEmpty(productRanges)) { - query.append(" AND ((EXISTS (FROM " + RangeRow.class.getName() + " RR WHERE RR in elements(E." + Email.PROPERTY_RANGE_ROW + ")"); - query.append(" AND RR." + RangeRow.PROPERTY_RANGE + " IN (:" + EmailFilter.PROPERTY_PRODUCT_RANGES + ")"); - query.append(" AND RR." + RangeRow.PROPERTY_PRODUCT_QUANTITY + " > 0 )"); + Set<Long> savQuantities = emailFilter.getSavQuantities(); + if (CollectionUtils.isNotEmpty(savQuantities)) { + query.append(" AND ((EXISTS (FROM " + RangeRow.class.getName() + " RR WHERE RR in elements(E." + Email.PROPERTY_RANGE_ROW + "))" + + " AND (select sum(RR." + RangeRow.PROPERTY_SAV_QUANTITY + ") " + + "FROM " + RangeRow.class.getName() + " RR WHERE RR in elements(E." + Email.PROPERTY_RANGE_ROW + ")"); + query.append(") IN (:" + EmailFilter.PROPERTY_SAV_QUANTITIES + ")"); - if (productRanges.contains(null)) { + if (savQuantities.contains(null) || savQuantities.contains(0L)) { query.append(" OR NOT EXISTS (FROM " + RangeRow.class.getName() + " RR WHERE RR in elements(E." + Email.PROPERTY_RANGE_ROW + "))"); } query.append("))"); - args.put(EmailFilter.PROPERTY_PRODUCT_RANGES, productRanges); + args.put(EmailFilter.PROPERTY_SAV_QUANTITIES, savQuantities); } - Set<Range> savRanges = emailFilter.getSavRanges(); - if (CollectionUtils.isNotEmpty(savRanges)) { - query.append(" AND ((EXISTS (FROM " + RangeRow.class.getName() + " RR WHERE RR in elements(E." + Email.PROPERTY_RANGE_ROW + ")"); - query.append(" AND RR." + RangeRow.PROPERTY_RANGE + " IN (:" + EmailFilter.PROPERTY_SAV_RANGES + ")"); - query.append(" AND RR." + RangeRow.PROPERTY_SAV_QUANTITY + " > 0 )"); + Set<Long> quotationQuantities = emailFilter.getQuotationQuantities(); + if (CollectionUtils.isNotEmpty(quotationQuantities)) { + query.append(" AND ((EXISTS (FROM " + RangeRow.class.getName() + " RR WHERE RR in elements(E." + Email.PROPERTY_RANGE_ROW + "))" + + " AND (select sum(RR." + RangeRow.PROPERTY_QUOTATION_QUANTITY + ") " + + "FROM " + RangeRow.class.getName() + " RR WHERE RR in elements(E." + Email.PROPERTY_RANGE_ROW + ")"); + query.append(") IN (:" + EmailFilter.PROPERTY_QUOTATION_QUANTITIES + ")"); - if (savRanges.contains(null)) { + if (quotationQuantities.contains(null) || quotationQuantities.contains(0L)) { query.append(" OR NOT EXISTS (FROM " + RangeRow.class.getName() + " RR WHERE RR in elements(E." + Email.PROPERTY_RANGE_ROW + "))"); } query.append("))"); - args.put(EmailFilter.PROPERTY_SAV_RANGES, savRanges); + args.put(EmailFilter.PROPERTY_QUOTATION_QUANTITIES, quotationQuantities); } - Set<Range> quotationRanges = emailFilter.getQuotationRanges(); - if (CollectionUtils.isNotEmpty(quotationRanges)) { - query.append(" AND ((EXISTS (FROM " + RangeRow.class.getName() + " RR WHERE RR in elements(E." + Email.PROPERTY_RANGE_ROW + ")"); - query.append(" AND RR." + RangeRow.PROPERTY_RANGE + " IN (:" + EmailFilter.PROPERTY_QUOTATION_RANGES + ")"); - query.append(" AND RR." + RangeRow.PROPERTY_QUOTATION_QUANTITY + " > 0 )"); - - if (quotationRanges.contains(null)) { - query.append(" OR NOT EXISTS (FROM " + RangeRow.class.getName() + " RR WHERE RR in elements(E." + Email.PROPERTY_RANGE_ROW + "))"); - } - query.append("))"); - - args.put(EmailFilter.PROPERTY_QUOTATION_RANGES, quotationRanges); - } - Set<String> projectReferences = emailFilter.getProjectReferences(); if (projectReferences != null) { query.append(" AND (E." + Email.PROPERTY_PROJECT_REFERENCE + " in (:" + EmailFilter.PROPERTY_PROJECT_REFERENCES + ")"); @@ -922,14 +910,19 @@ return result; } - public Set<Object> getDistinctValues(MailFolder folder, String[] properties, String condition) { + public Set<Object> getDistinctValues(MailFolder folder, String[] properties, boolean sum) { Map<String, Object> args = new HashMap<>(); String folderAndStatusCondition = " WHERE email." + Email.PROPERTY_MAIL_FOLDER + " = :folder" + " AND email." + Email.PROPERTY_DEMAND_STATUS + " != :archiveStatus"; args.put("folder", folder); args.put("archiveStatus", DemandStatus.ARCHIVED); - String propertyList = StringUtils.join(properties, ", "); + String propertyList; + if (sum) { + propertyList = "sum(" + StringUtils.join(properties, "), sum(") + ")"; + } else { + propertyList = StringUtils.join(properties, ", "); + } String query = "SELECT DISTINCT " + propertyList + " FROM " + Email.class.getName() + " AS email"; @@ -947,8 +940,8 @@ } query += folderAndStatusCondition; - if (StringUtils.isNotBlank(condition)) { - query += " AND " + condition; + if (sum) { + query += " group by email." + Email.PROPERTY_TOPIA_ID; } List<Object> queryResults = findAll(query, args); @@ -999,7 +992,11 @@ // TODO echatellier 20140918 : on ajoute null dans tous les cas car les requetes precendente // sont trop lente juste pour savoir si on a vraiment besoin de savoir si l'on doit vraiment // avoir des valeurs nulles - if (!result.contains("")) { + if (sum && result.contains(null)) { + result.remove(null); + result.add(0L); + + } else if (!result.contains("")) { result.add(null); } Modified: trunk/faxtomail-service/src/main/java/com/franciaflex/faxtomail/services/service/EmailService.java =================================================================== --- trunk/faxtomail-service/src/main/java/com/franciaflex/faxtomail/services/service/EmailService.java 2014-09-24 09:23:49 UTC (rev 639) +++ trunk/faxtomail-service/src/main/java/com/franciaflex/faxtomail/services/service/EmailService.java 2014-09-24 12:35:48 UTC (rev 640) @@ -73,7 +73,7 @@ void transmitPendingDemandsToEdi(); - Set<Object> getDistinctValues(MailFolder folder, String[] properties, String condition); + Set<Object> getDistinctValues(MailFolder folder, String[] properties, boolean sum); PaginationResult<Email> getEmailForFolder(MailFolder folder, FaxToMailUser currentUser, EmailFilter filter, PaginationParameter page); Modified: trunk/faxtomail-service/src/main/java/com/franciaflex/faxtomail/services/service/EmailServiceImpl.java =================================================================== --- trunk/faxtomail-service/src/main/java/com/franciaflex/faxtomail/services/service/EmailServiceImpl.java 2014-09-24 09:23:49 UTC (rev 639) +++ trunk/faxtomail-service/src/main/java/com/franciaflex/faxtomail/services/service/EmailServiceImpl.java 2014-09-24 12:35:48 UTC (rev 640) @@ -614,11 +614,11 @@ } @Override - public Set<Object> getDistinctValues(MailFolder folder, String[] properties, String condition) { + public Set<Object> getDistinctValues(MailFolder folder, String[] properties, boolean sum) { Set<Object> result = null; if (folder.isFolderReadable()) { EmailTopiaDao dao = getPersistenceContext().getEmailDao(); - result = dao.getDistinctValues(folder, properties, condition); + result = dao.getDistinctValues(folder, properties, sum); } return result; } Modified: trunk/faxtomail-ui-swing/src/main/java/com/franciaflex/faxtomail/ui/swing/actions/SaveDemandeAndExitAction.java =================================================================== --- trunk/faxtomail-ui-swing/src/main/java/com/franciaflex/faxtomail/ui/swing/actions/SaveDemandeAndExitAction.java 2014-09-24 09:23:49 UTC (rev 639) +++ trunk/faxtomail-ui-swing/src/main/java/com/franciaflex/faxtomail/ui/swing/actions/SaveDemandeAndExitAction.java 2014-09-24 12:35:48 UTC (rev 640) @@ -37,8 +37,8 @@ } @Override - public void doAction() throws Exception { - super.doAction(); + public void postSuccessAction() { + super.postSuccessAction(); getActionEngine().runInternalAction(getContext().getMainUI().getHandler(), GoToPreviousScreenAction.class); } } Modified: trunk/faxtomail-ui-swing/src/main/java/com/franciaflex/faxtomail/ui/swing/content/demande/DemandeListTableFilter.java =================================================================== --- trunk/faxtomail-ui-swing/src/main/java/com/franciaflex/faxtomail/ui/swing/content/demande/DemandeListTableFilter.java 2014-09-24 09:23:49 UTC (rev 639) +++ trunk/faxtomail-ui-swing/src/main/java/com/franciaflex/faxtomail/ui/swing/content/demande/DemandeListTableFilter.java 2014-09-24 12:35:48 UTC (rev 640) @@ -141,15 +141,15 @@ break; case DemandeUIModel.PROPERTY_PF_NB: - emailFilter.setProductsRanges(!filtered ? null : new HashSet<Range>((Collection) items)); + emailFilter.setProductsQuantities(!filtered ? null : new HashSet<Long>((Collection) items)); break; case DemandeUIModel.PROPERTY_SAV_NB: - emailFilter.setSavRanges(!filtered ? null : new HashSet<Range>((Collection) items)); + emailFilter.setSavQuantities(!filtered ? null : new HashSet<Long>((Collection) items)); break; case DemandeUIModel.PROPERTY_QUOTATION_NB: - emailFilter.setQuotationRanges(!filtered ? null : new HashSet<Range>((Collection) items)); + emailFilter.setQuotationQuantities(!filtered ? null : new HashSet<Long>((Collection) items)); break; case Email.PROPERTY_LAST_ATTACHMENT_OPENER: @@ -183,7 +183,7 @@ Set<Object> result = new HashSet<>(); - String condition = null; + boolean sum = false; String[] properties; switch (property) { case DemandeUIModel.PROPERTY_CLIENT_CODE: @@ -200,18 +200,18 @@ break; case DemandeUIModel.PROPERTY_PF_NB: - condition = "rangeRow." + RangeRow.PROPERTY_PRODUCT_QUANTITY + " > 0"; - properties = new String[] { "rangeRow." + RangeRow.PROPERTY_RANGE }; + properties = new String[] { "rangeRow." + RangeRow.PROPERTY_PRODUCT_QUANTITY }; + sum = true; break; case DemandeUIModel.PROPERTY_SAV_NB: - condition = "rangeRow." + RangeRow.PROPERTY_SAV_QUANTITY + " > 0"; - properties = new String[] { "rangeRow." + RangeRow.PROPERTY_RANGE }; + properties = new String[] { "rangeRow." + RangeRow.PROPERTY_SAV_QUANTITY }; + sum = true; break; case DemandeUIModel.PROPERTY_QUOTATION_NB: - condition = "rangeRow." + RangeRow.PROPERTY_QUOTATION_QUANTITY + " > 0"; - properties = new String[] { "rangeRow." + RangeRow.PROPERTY_RANGE }; + properties = new String[] { "rangeRow." + RangeRow.PROPERTY_QUOTATION_QUANTITY }; + sum = true; break; case Email.PROPERTY_ATTACHMENT: @@ -224,7 +224,7 @@ result.addAll(emailService.getDistinctValues(selectedFolder, properties, - condition)); + sum)); if (Email.PROPERTY_RECEPTION_DATE.equals(property)) { Set<Object> filteredDates = new HashSet<>();