Author: fdesbois Date: 2009-12-21 19:07:23 +0000 (Mon, 21 Dec 2009) New Revision: 142 Added: trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/TopiaQuery.java Modified: trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/entity/BoatInfosImpl.java trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/impl/ActivityCalendarImport.java trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/impl/ServiceBoatImpl.java trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/impl/ServiceSamplingImpl.java trunk/suiviobsmer-business/src/main/xmi/suiviobsmer.zargo trunk/suiviobsmer-business/src/test/java/fr/ifremer/suiviobsmer/impl/ServiceBoatImplTest.java trunk/suiviobsmer-ui/src/main/webapp/Boats.tml Log: - Add Query managment - Improve query for Boat filters Added: trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/TopiaQuery.java =================================================================== --- trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/TopiaQuery.java (rev 0) +++ trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/TopiaQuery.java 2009-12-21 19:07:23 UTC (rev 142) @@ -0,0 +1,204 @@ + +package fr.ifremer.suiviobsmer; + +import java.util.ArrayList; +import java.util.Arrays; +import java.util.List; +import org.nuiton.topia.TopiaContext; +import org.nuiton.topia.TopiaException; +import org.nuiton.topia.persistence.TopiaEntity; +import org.slf4j.Logger; +import org.slf4j.LoggerFactory; + +/** + * TopiaQuery + * + * Created: 21 déc. 2009 + * + * @author fdesbois + * @version $Revision$ + * + * Mise a jour: $Date$ + * par : $Author$ + */ +public class TopiaQuery { + + private static final Logger log = LoggerFactory.getLogger(TopiaQuery.class); + + protected List<Object> params; + + protected String select; + + protected String from; + + protected String where; + + protected String orderBy; + + protected boolean parentheses; + + public static enum Op { + EQ("="), + GT(">"), + GE(">="), + LIKE("LIKE"), + LT("<"), + LE("<="), + NOT_NULL("IS NOT NULL"); + + protected String value; + + Op(String value) { + this.value = value; + } + + @Override + public String toString() { + return this.value; + } + } + + public TopiaQuery(Class<? extends TopiaEntity> entityClass) { + this(entityClass.getName()); + } + + public TopiaQuery(String from) { + this.from = " FROM " + from; + parentheses = true; + } + + @Override + public String toString() { + return fullQuery(); + } + + public String fullQuery() { + String result = ""; + if (select != null) { + result = select; + } + result += from; + if (where != null) { + result += where; + } + if (orderBy != null) { + result += orderBy; + } + return result.trim(); + } + + public TopiaQuery addParam(String id, Object e) { + if (params == null) { + params = new ArrayList<Object>(); + } + params.add(id); + params.add(e); + return this; + } + + public TopiaQuery add(String where) { + if (this.where == null) { + this.where = " WHERE "; + } else { + this.where += " AND "; + } + if (parentheses) { + this.where += "("; + } + this.where += where; + if (parentheses) { + this.where += ")"; + } + parentheses = true; + return this; + } + + public TopiaQuery add(String paramName, Op constraint, Object paramValue) { + int dot = paramName.lastIndexOf("."); + String valueName = paramName; + if (dot != -1) { + valueName = paramName.substring(dot+1); + } + parentheses = false; + return add(paramName + " " + constraint + " :" + valueName).addParam(valueName, paramValue); + } + + public TopiaQuery addNotNull(String paramName) { + return add(paramName + " " + Op.NOT_NULL); + } + + public TopiaQuery add(String paramName, Object paramValue) { + return add(paramName, Op.EQ, paramValue); + } + + public TopiaQuery addFrom(String str) { + from += ", " + str; + return this; + } + + public TopiaQuery addSelect(String select) { + if (this.select == null) { + this.select = "SELECT "; + } else { + this.select += ", "; + } + this.select += select; + return this; + } + + public TopiaQuery addOrder(String order) { + if (orderBy == null) { + orderBy = " ORDER BY "; + } else { + orderBy += ", "; + } + orderBy += order; + return this; + } + + public List execute(TopiaContext transaction) throws TopiaException { + if (log.isTraceEnabled()) { + log.trace("# QUERY : " + fullQuery()); + if (params != null) { + log.trace("# PARAMS : " + Arrays.toString(params.toArray())); + } + } + if (params == null) { + return transaction.find(fullQuery()); + } + return transaction.find(fullQuery(), params.toArray()); + } + + public <T extends TopiaEntity> List<T> executeToEntityList(TopiaContext transaction, Class<T> entityClass) throws TopiaException { + List res = execute(transaction); + List<T> results = new ArrayList<T>(); + for (Object o : res) { + T entity = (T)o; + results.add(entity); + } + return results; + } + + public <T extends TopiaEntity> T executeToEntity(TopiaContext transaction, Class<T> entityClass) throws TopiaException { + List<T> results = executeToEntityList(transaction, entityClass); + return !results.isEmpty() ? results.get(0) : null; + } + + public int executeToInteger(TopiaContext transaction, String select) throws TopiaException { + String oldValue = this.select; + this.select = "SELECT " + select; + int result = 0; + List results = execute(transaction); + if (!results.isEmpty()) { + Long tmp = (Long)results.get(0); + result = tmp.intValue(); + } + this.select = oldValue; + return result; + } + + public int executeCount(TopiaContext transaction) throws TopiaException { + return executeToInteger(transaction, "COUNT(*)"); + } + +} Property changes on: trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/TopiaQuery.java ___________________________________________________________________ Added: svn:keywords + "Author Date Id Revision HeadURL" Modified: trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/entity/BoatInfosImpl.java =================================================================== --- trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/entity/BoatInfosImpl.java 2009-12-21 14:22:59 UTC (rev 141) +++ trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/entity/BoatInfosImpl.java 2009-12-21 19:07:23 UTC (rev 142) @@ -23,15 +23,14 @@ import fr.ifremer.suiviobsmer.SuiviObsmerContext; import fr.ifremer.suiviobsmer.SuiviObsmerException; +import fr.ifremer.suiviobsmer.TopiaQuery; import fr.ifremer.suiviobsmer.bean.ContactState; import java.io.Serializable; -import java.util.ArrayList; -import java.util.Arrays; -import java.util.Collections; import org.nuiton.topia.TopiaContext; import java.util.Date; -import java.util.List; -import org.nuiton.topia.TopiaException; +import org.slf4j.Logger; +import org.slf4j.LoggerFactory; +import static fr.ifremer.suiviobsmer.TopiaQuery.Op; /** * BoatInfosImpl @@ -45,6 +44,8 @@ * par : $Author$ */ public class BoatInfosImpl extends BoatInfosAbstract implements Serializable, BoatInfos { + + private static final Logger log = LoggerFactory.getLogger(BoatInfosImpl.class); private static final long serialVersionUID = 1L; @@ -66,7 +67,8 @@ try { transaction = SuiviObsmerContext.getTopiaRootContext().beginTransaction(); - result = countDoneContactsFromDate(transaction, fromDate, false); + TopiaQuery query = createQueryDoneContactsFromDate(fromDate); + result = query.executeCount(transaction); transaction.closeContext(); } catch (Exception eee) { @@ -93,8 +95,11 @@ try { transaction = SuiviObsmerContext.getTopiaRootContext().beginTransaction(); - result = countDoneContactsFromDate(transaction, fromDate, true); + TopiaQuery query = createQueryDoneContactsFromDate(fromDate); + query.add("user.company = :company").addParam("company", getCompany()); + result = query.executeCount(transaction); + transaction.closeContext(); } catch (Exception eee) { SuiviObsmerContext.serviceException(transaction, @@ -106,6 +111,29 @@ } /** + * Create the query for contacts with BoardingDone state since the fromDate. + * This query is used to get all boarding dones on this boat for all companies. + * Only the validate contacts by company will be count. The param fromDate can be null, + * in this case, all contacts will be given (no matter which date then started). + * + * @param fromDate the date to start + * @return the TopiaQuery created + */ + protected TopiaQuery createQueryDoneContactsFromDate(Date fromDate) { + TopiaQuery query = new TopiaQuery(Contact.class). + add("boat", getBoat()). + add("state", ContactState.BOARDING_DONE.toString()). + add("validationCompany", Boolean.TRUE). + add("validationProgram IS NULL OR validationProgram = :booleanTrue"). + addParam("booleanTrue", Boolean.TRUE); + + if (fromDate != null) { + query.add("tideBeginDate", Op.GE, fromDate); + } + return query; + } + + /** * Execute the HQL query for counting contact with BoardingDone state since the fromDate. * This query is used to get all boarding dones on this boat for all companies or only for the * current one. Only the validate contacts by company will be count. The param fromDate can be null, @@ -117,40 +145,40 @@ * @return the number of boarding done since fromDate * @throws TopiaException */ - protected int countDoneContactsFromDate(TopiaContext transaction, Date fromDate, boolean companyOnly) throws TopiaException { - int result = 0; - - List<Object> params = new ArrayList<Object>(); - String contactClassName = Contact.class.getName(); - String query = "SELECT COUNT(*)" + - " FROM " + contactClassName + - " WHERE boat = :boat AND state = :boardingDone" + - " AND validationCompany = :booleanTrue" + - " AND (validationProgram IS NULL OR validationProgram = :booleanTrue)"; - - params.add("boat"); params.add(getBoat()); - params.add("boardingDone"); params.add(ContactState.BOARDING_DONE.toString()); - params.add("booleanTrue"); params.add(Boolean.TRUE); - - if (fromDate != null) { - query += " AND tideBeginDate >= :fromDate"; - params.add("fromDate"); - params.add(fromDate); - } - - if (companyOnly) { - query+= " AND user.company = :company"; - params.add("company"); - params.add(getCompany()); - } - - List results = transaction.find(query, params.toArray()); - - if (!results.isEmpty()) { - Long tmp = (Long) results.get(0); - result += tmp.intValue(); - } - - return result; - } +// protected int countDoneContactsFromDate(TopiaContext transaction, Date fromDate, boolean companyOnly) throws TopiaException { +// int result = 0; +// +// List<Object> params = new ArrayList<Object>(); +// String contactClassName = Contact.class.getName(); +// String query = "SELECT COUNT(*)" + +// " FROM " + contactClassName + +// " WHERE boat = :boat AND state = :boardingDone" + +// " AND validationCompany = :booleanTrue" + +// " AND (validationProgram IS NULL OR validationProgram = :booleanTrue)"; +// +// params.add("boat"); params.add(getBoat()); +// params.add("boardingDone"); params.add(ContactState.BOARDING_DONE.toString()); +// params.add("booleanTrue"); params.add(Boolean.TRUE); +// +// if (fromDate != null) { +// query += " AND tideBeginDate >= :fromDate"; +// params.add("fromDate"); +// params.add(fromDate); +// } +// +// if (companyOnly) { +// query+= " AND user.company = :company"; +// params.add("company"); +// params.add(getCompany()); +// } +// +// List results = transaction.find(query, params.toArray()); +// +// if (!results.isEmpty()) { +// Long tmp = (Long) results.get(0); +// result += tmp.intValue(); +// } +// +// return result; +// } } Modified: trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/impl/ActivityCalendarImport.java =================================================================== --- trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/impl/ActivityCalendarImport.java 2009-12-21 14:22:59 UTC (rev 141) +++ trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/impl/ActivityCalendarImport.java 2009-12-21 19:07:23 UTC (rev 142) @@ -252,10 +252,10 @@ if (result%500 == 0 && log.isInfoEnabled()) { log.info("RUNNING... Import calendrier d'activité ligne " + currRow); } + + transaction.commitTransaction(); } - transaction.commitTransaction(); - long stopTime = System.currentTimeMillis(); String execTime = DurationFormatUtils.formatDurationHMS(stopTime - startTime); Modified: trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/impl/ServiceBoatImpl.java =================================================================== --- trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/impl/ServiceBoatImpl.java 2009-12-21 14:22:59 UTC (rev 141) +++ trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/impl/ServiceBoatImpl.java 2009-12-21 19:07:23 UTC (rev 142) @@ -26,6 +26,8 @@ import fr.ifremer.suiviobsmer.SuiviObsmerContext; import fr.ifremer.suiviobsmer.SuiviObsmerException; import fr.ifremer.suiviobsmer.SuiviObsmerModelDAOHelper; +import fr.ifremer.suiviobsmer.TopiaQuery; +import fr.ifremer.suiviobsmer.TopiaQuery.Op; import fr.ifremer.suiviobsmer.bean.BoatFilter; import fr.ifremer.suiviobsmer.bean.CompanyBoatInfos; import fr.ifremer.suiviobsmer.bean.CompanyBoatInfosImpl; @@ -45,7 +47,6 @@ import java.util.Map; import java.util.regex.Matcher; import java.util.regex.Pattern; -import org.apache.commons.lang.BooleanUtils; import org.apache.commons.lang.StringUtils; import org.nuiton.topia.TopiaContext; import org.slf4j.Logger; @@ -79,137 +80,269 @@ try { transaction = rootContext.beginTransaction(); - BoatDAO dao = SuiviObsmerModelDAOHelper.getBoatDAO(transaction); + TopiaQuery query = new TopiaQuery(Boat.class.getName() + " B").addSelect("B"); - boolean alreadyFiltered = false; - - /******************** FILTER ON BOATS *****************************/ - - // IMMATRICULATION : full + // Immatriculation if (filter.getBoatImmatriculation() != null) { - if (log.isDebugEnabled()) { - log.debug("Filter by immatriculation : " + filter.getBoatImmatriculation()); - } - Boat boat = dao.findByImmatriculation(filter.getBoatImmatriculation()); - results.put(boat.getImmatriculation(), boat); - return results; + query.add("B.immatriculation", filter.getBoatImmatriculation()); } - // NAME : startWith + + // Name if (!StringUtils.isEmpty(filter.getBoatName())) { - if (log.isDebugEnabled()) { - log.debug("Filter by boatName : " + filter.getBoatName()); - } - List list = transaction.find("FROM BoatImpl WHERE name LIKE :name", - "name", filter.getBoatName() + "%"); - for (Object o : list) { - Boat boat = (Boat)o; - results.put(boat.getImmatriculation(), boat); - } - alreadyFiltered = true; + query.add("B.name", Op.LIKE, filter.getBoatName() + "%"); } - // DISTRICT CODE : full + + // DistrictCode if (!StringUtils.isEmpty(filter.getBoatDistrictCode())) { - if (log.isDebugEnabled()) { - log.debug("Filter by boatDistrictCode : " + filter.getBoatDistrictCode()); - } - // boats not filtered yet - if (!alreadyFiltered) { - List<Boat> boats = dao.findAllByDistrictCode(filter.getBoatDistrictCode()); - for (Boat boat : boats) { - results.put(boat.getImmatriculation(), boat); - } - // results may exist with some boats from previous query (boatName) - } else { - for (Integer key : results.keySet()) { - Boat boat = results.get(key); - if (!boat.getDistrictCode().equals(filter.getBoatDistrictCode())) { - results.remove(key); - } - } - } + query.add("B.districtCode", filter.getBoatDistrictCode()); } - /******************** FILTER ON SAMPLE ROWS ***********************/ - - // COMPANY FILTERED ? - boolean companyFiltered = filter.getCompany() != null; + boolean sampleRowCodeFiltered = !StringUtils.isEmpty(filter.getSampleRowCode()); boolean professionFiltered = filter.getProfession() != null; boolean fishingZoneFiltered = filter.getFishingZone() != null; + boolean sampleRowFiltered = sampleRowCodeFiltered || professionFiltered || fishingZoneFiltered; - SampleRowDAO rowDAO = SuiviObsmerModelDAOHelper.getSampleRowDAO(transaction); + // Add join for ElligibleBoat + if (sampleRowFiltered) { + String elligibleBoatClassName = ElligibleBoat.class.getName(); + query.addFrom(elligibleBoatClassName + " E").add("E.boat = B"); + } + // SampleRowCode + if (sampleRowCodeFiltered) { + query.add("E.sampleRow.code", filter.getSampleRowCode()); + } + // Profession + if (professionFiltered) { + query.add("E.sampleRow.profession", filter.getProfession()); + } - // CODE - Map<String, Object> properties = new HashMap<String, Object>(); - if (!StringUtils.isEmpty(filter.getSampleRowCode())) { - if (log.isDebugEnabled()) { - log.debug("Filter by rowCode : " + filter.getSampleRowCode()); - } - properties.put(SampleRow.CODE, filter.getSampleRowCode()); - } else { - // COMPANY : only if an other filter on SampleRow is set - if (companyFiltered && (professionFiltered || fishingZoneFiltered)) { - if (log.isDebugEnabled()) { - log.debug("Filter by company : " + filter.getCompany().getName()); - } - // TODO add filter for non closed sampleRow, i.e. sampleRow.program.periodEnd > currentTime - properties.put(SampleRow.COMPANY, filter.getCompany()); - } - // PROFESSION - if (professionFiltered) { - if (log.isDebugEnabled()) { - log.debug("Filter by profession : " + filter.getProfession().getCode()); - } - properties.put(SampleRow.PROFESSION, filter.getProfession()); - } + // TODO FishingZone + + // Company : only if an other filter is set for the sampleRow + if (filter.getCompany() != null && sampleRowFiltered) { + query.add("E.sampleRow.company", filter.getCompany()); + query.add("E.companyActive = :booleanTrue OR (E.companyActive IS NULL AND E.globalActive = :booleanTrue)"). + addParam("booleanTrue", Boolean.TRUE); } - List<SampleRow> rows = new ArrayList<SampleRow>(); - if (!properties.isEmpty()) { - rows = rowDAO.findAllByProperties(properties); - alreadyFiltered = true; - } else if (fishingZoneFiltered) { - rows = rowDAO.findAllContainsFishingZone(filter.getFishingZone()); - alreadyFiltered = true; + List<Boat> boats = query.executeToEntityList(transaction, Boat.class); + for (Boat boat : boats) { + results.put(boat.getImmatriculation(), boat); } - /******************** CHECK ELLIGIBLE BOATS FOR EACH ROW **********/ +// String boatClassName = Boat.class.getName(); +// String queryHead = "SELECT B FROM " + boatClassName + " B"; +// String query = ""; +// boolean alreadyFiltered = false; +// List<Object> params = new ArrayList<Object>(); +// +// // Immatriculation +// if (filter.getBoatImmatriculation() != null) { +// query += " WHERE B.immatriculation = :immatriculation"; +// params.add("immatriculation"); +// params.add(filter.getBoatImmatriculation()); +// alreadyFiltered = true; +// } +// // Name +// if (!StringUtils.isEmpty(filter.getBoatName())) { +// query += alreadyFiltered ? " AND " : " WHERE "; +// query += "B.name LIKE :name"; +// params.add("name"); +// params.add(filter.getBoatName() + "%"); +// alreadyFiltered = true; +// } +// // DistrictCode +// if (!StringUtils.isEmpty(filter.getBoatDistrictCode())) { +// query += alreadyFiltered ? " AND " : " WHERE "; +// query += "B.districtCode = :districtCode"; +// params.add("districtCode"); +// params.add(filter.getBoatDistrictCode()); +// alreadyFiltered = true; +// } +// +// boolean sampleRowCodeFiltered = !StringUtils.isEmpty(filter.getSampleRowCode()); +// boolean professionFiltered = filter.getProfession() != null; +// boolean fishingZoneFiltered = filter.getFishingZone() != null; +// boolean sampleRowFiltered = sampleRowCodeFiltered || professionFiltered || fishingZoneFiltered; +// +// // Add join for ElligibleBoat +// if (sampleRowFiltered) { +// String elligibleBoatClassName = ElligibleBoat.class.getName(); +// queryHead += ", " + elligibleBoatClassName + " E"; +// query += alreadyFiltered ? " AND " : " WHERE "; +// query += "E.boat = B"; +// } +// // SampleRowCode +// if (sampleRowCodeFiltered) { +// query += " AND E.sampleRow.code = :sampleRowCode"; +// params.add("sampleRowCode"); +// params.add(filter.getSampleRowCode()); +// } +// // Profession +// if (professionFiltered) { +// query += " AND E.sampleRow.profession = :profession"; +// params.add("profession"); +// params.add(filter.getProfession()); +// } +// // FishingZone +//// if (fishingZoneFiltered) { +//// String fishingZoneClassName = FishingZone.class.getName(); +//// queryHead += ", " + fishingZoneClassName + " F"; +//// query += " AND F.sampleRow = " + +//// query += " AND E.sampleRow.fishingZone = :fishingZone"; +//// params.add("profession"); +//// params.add(filter.getFishingZone()); +//// } +// // Company : only if an other filter is set for the sampleRow +// if (filter.getCompany() != null && sampleRowFiltered) { +// query += " AND E.sampleRow.company = :company"; +// query += " AND (E.companyActive = :booleanTrue "; +// query += " OR (E.companyActive IS NULL AND E.globalActive = :booleanTrue))"; +// params.add("company"); +// params.add(filter.getCompany()); +// params.add("booleanTrue"); +// params.add(Boolean.TRUE); +// } +// +// if (log.isDebugEnabled()) { +// log.debug("QUERY : " + queryHead + query); +// } +// +// List boats = transaction.find(queryHead + query, params.toArray()); +// for (Object o : boats) { +// Boat boat = (Boat)o; +// results.put(boat.getImmatriculation(), boat); +// } - for (SampleRow row : rows) { - List<FishingZone> zones = row.getFishingZone(); - // FILTER BY FISHING ZONE - if (fishingZoneFiltered && !zones.contains(filter.getFishingZone())) { - break; - } - for (ElligibleBoat elligible : row.getElligibleBoat()) { - int immatriculation = elligible.getBoat().getImmatriculation(); - // For company - boolean condition1 = companyFiltered && - BooleanUtils.isNotFalse(elligible.getCompanyActive()); + /******************** FILTER ON BOATS *****************************/ - // For admin - boolean condition2 = !companyFiltered && elligible.getGlobalActive(); +// // IMMATRICULATION : full +// if (filter.getBoatImmatriculation() != null) { +// if (log.isDebugEnabled()) { +// log.debug("Filter by immatriculation : " + filter.getBoatImmatriculation()); +// } +// Boat boat = dao.findByImmatriculation(filter.getBoatImmatriculation()); +// results.put(boat.getImmatriculation(), boat); +// return results; +// } +// // NAME : startWith +// if (!StringUtils.isEmpty(filter.getBoatName())) { +// if (log.isDebugEnabled()) { +// log.debug("Filter by boatName : " + filter.getBoatName()); +// } +// List list = transaction.find("FROM BoatImpl WHERE name LIKE :name", +// "name", filter.getBoatName() + "%"); +// for (Object o : list) { +// Boat boat = (Boat)o; +// results.put(boat.getImmatriculation(), boat); +// } +// alreadyFiltered = true; +// } +// // DISTRICT CODE : full +// if (!StringUtils.isEmpty(filter.getBoatDistrictCode())) { +// if (log.isDebugEnabled()) { +// log.debug("Filter by boatDistrictCode : " + filter.getBoatDistrictCode()); +// } +// // boats not filtered yet +// if (!alreadyFiltered) { +// List<Boat> boats = dao.findAllByDistrictCode(filter.getBoatDistrictCode()); +// for (Boat boat : boats) { +// results.put(boat.getImmatriculation(), boat); +// } +// // results may exist with some boats from previous query (boatName) +// } else { +// for (Integer key : results.keySet()) { +// Boat boat = results.get(key); +// if (!boat.getDistrictCode().equals(filter.getBoatDistrictCode())) { +// results.remove(key); +// } +// } +// } +// } +// +// /******************** FILTER ON SAMPLE ROWS ***********************/ +// +// // COMPANY FILTERED ? +// boolean companyFiltered = filter.getCompany() != null; +// boolean professionFiltered = filter.getProfession() != null; +// boolean fishingZoneFiltered = filter.getFishingZone() != null; +// +// SampleRowDAO rowDAO = SuiviObsmerModelDAOHelper.getSampleRowDAO(transaction); +// +// // CODE +// Map<String, Object> properties = new HashMap<String, Object>(); +// if (!StringUtils.isEmpty(filter.getSampleRowCode())) { +// if (log.isDebugEnabled()) { +// log.debug("Filter by rowCode : " + filter.getSampleRowCode()); +// } +// properties.put(SampleRow.CODE, filter.getSampleRowCode()); +// } else { +// // COMPANY : only if an other filter on SampleRow is set +// if (companyFiltered && (professionFiltered || fishingZoneFiltered)) { +// if (log.isDebugEnabled()) { +// log.debug("Filter by company : " + filter.getCompany().getName()); +// } +// // TODO add filter for non closed sampleRow, i.e. sampleRow.program.periodEnd > currentTime +// properties.put(SampleRow.COMPANY, filter.getCompany()); +// } +// // PROFESSION +// if (professionFiltered) { +// if (log.isDebugEnabled()) { +// log.debug("Filter by profession : " + filter.getProfession().getCode()); +// } +// properties.put(SampleRow.PROFESSION, filter.getProfession()); +// } +// } +// +// List<SampleRow> rows = new ArrayList<SampleRow>(); +// if (!properties.isEmpty()) { +// rows = rowDAO.findAllByProperties(properties); +// alreadyFiltered = true; +// } else if (fishingZoneFiltered) { +// rows = rowDAO.findAllContainsFishingZone(filter.getFishingZone()); +// alreadyFiltered = true; +// } +// +// /******************** CHECK ELLIGIBLE BOATS FOR EACH ROW **********/ +// +// for (SampleRow row : rows) { +// List<FishingZone> zones = row.getFishingZone(); +// // FILTER BY FISHING ZONE +// if (fishingZoneFiltered && !zones.contains(filter.getFishingZone())) { +// break; +// } +// +// for (ElligibleBoat elligible : row.getElligibleBoat()) { +// int immatriculation = elligible.getBoat().getImmatriculation(); +// +// // For company +// boolean condition1 = companyFiltered && +// BooleanUtils.isNotFalse(elligible.getCompanyActive()); +// +// // For admin +// boolean condition2 = !companyFiltered && elligible.getGlobalActive(); +// +// if (condition1 || condition2) { +// if (log.isDebugEnabled()) { +// log.debug("Add elligible boat : " + +// immatriculation + " - " + +// elligible.getBoat().getName()); +// } +// results.put(immatriculation, elligible.getBoat()); +// } +// +// } +// } +// +// // No filter set +// if (!alreadyFiltered) { +// for (Boat boat : dao.findAll()) { +// results.put(boat.getImmatriculation(), boat); +// } +// } - if (condition1 || condition2) { - if (log.isDebugEnabled()) { - log.debug("Add elligible boat : " + - immatriculation + " - " + - elligible.getBoat().getName()); - } - results.put(immatriculation, elligible.getBoat()); - } - - } - } - - // No filter set - if (!alreadyFiltered) { - for (Boat boat : dao.findAll()) { - results.put(boat.getImmatriculation(), boat); - } - } - transaction.closeContext(); } catch (Exception eee) { SuiviObsmerContext.serviceException(transaction, "Impossible de filtrer la liste des navires", eee); @@ -275,19 +408,21 @@ } } } + // Last contact String contactClassName = Contact.class.getName(); - List results = transaction.find( - "FROM " + contactClassName + " C1" + - " WHERE C1.user.company = :company AND C1.boat = :boat " + - " AND C1.topiaCreateDate = " + - " (SELECT MAX(C2.topiaCreateDate) FROM " + contactClassName + " C2" + - " WHERE C2.user.company = :company AND C2.boat = C1.boat)", - "company", company, - "boat", boat); + TopiaQuery query = new TopiaQuery(contactClassName + " C1"); + query.add("C1.user.company = :company").addParam("company", company); + query.add("C1.boat", boat); - if (!results.isEmpty()) { - Contact contact = (Contact)results.get(0); + TopiaQuery subquery = new TopiaQuery(contactClassName + " C2").addSelect("MAX(C2.topiaCreateDate)"); + subquery.add("C2.boat = C1.boat").add("C2.user.company = :company"); + + query.add("C1.topiaCreateDate = (" + subquery.fullQuery() + ")"); + + Contact contact = query.executeToEntity(transaction, Contact.class); + + if (contact != null) { DataLoader.loadContactForBoatInfos(contact); result.setLastContact(contact); } Modified: trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/impl/ServiceSamplingImpl.java =================================================================== --- trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/impl/ServiceSamplingImpl.java 2009-12-21 14:22:59 UTC (rev 141) +++ trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/impl/ServiceSamplingImpl.java 2009-12-21 19:07:23 UTC (rev 142) @@ -25,6 +25,7 @@ import fr.ifremer.suiviobsmer.SuiviObsmerException; import fr.ifremer.suiviobsmer.SuiviObsmerModelDAOHelper; import fr.ifremer.suiviobsmer.SuiviObsmerContext; +import fr.ifremer.suiviobsmer.TopiaQuery; import fr.ifremer.suiviobsmer.dto.SamplingHistoricRow; import fr.ifremer.suiviobsmer.entity.*; import fr.ifremer.suiviobsmer.entity.SampleRow; @@ -47,6 +48,7 @@ import org.apache.commons.lang.StringUtils; import org.nuiton.topia.TopiaContext; import org.nuiton.topia.TopiaException; +import org.nuiton.util.DateUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; @@ -179,33 +181,25 @@ FishingZone.FACADE_NAME, FishingZone.SECTOR_NAME, FishingZone.DISTRICT_CODE); - /*List list; - if (company != null) { - list = transaction.find( - "SELECT S FROM SampleRowImpl S, FishingZoneImpl F, FishingZoneToSampleRowImpl FS, ProgramImpl P" + - " WHERE FS.fishingZone = F AND FS.sampleRow = S AND S.company = :company" + - " AND (P.periodBegin <= :begin OR P.periodEnd >= :end)" + - " ORDER BY facadeName, sectorName, districtCode", - "company", company, "begin", periodBegin, "end", periodEnd); - } else { - list = transaction.find( - "SELECT S FROM SampleRowImpl S, FishingZoneImpl F, FishingZoneToSampleRowImpl FS, ProgramImpl P" + - " WHERE FS.fishingZone = F AND FS.sampleRow = S" + - " AND (P.periodBegin <= :begin OR P.periodEnd >= :end)" + - " ORDER BY facadeName, sectorName, districtCode", - "begin", periodBegin, "end", periodEnd); - } - for (Object o : list) { - SampleRow row = (SampleRow)o; - // load data - row.getProfession(); - row.getProgram(); - row.getCompany(); - row.getAllFishingZones(); - results.add(row); - }*/ +// TopiaQuery query = new TopiaQuery(SampleRow.class.getName() + " S").addSelect("S"); +// query.addFrom(SampleRowZone.class.getName() + " Z").add("Z.sampleRow = S"); +// query.addFrom(FishingZone.class.getName() + " F").add("Z.fishingZone = F"); +// +// if (company != null) { +// query.add("S.company = :company").addParam("company", company); +// } +// +// // Prepare period dates +// periodBegin = DateUtils.setFirstDayOfMonth(periodBegin); +// periodEnd = DateUtils.setLastDayOfMonth(periodEnd); +// query.add("S.program.periodBegin < :end AND S.program.periodEnd > :begin"). +// addParam("begin", periodBegin).addParam("end", periodEnd); +// +// query.addOrder("facadeName, sectorName, districtCode"); + + for (FishingZone zone : list) { if (log.isTraceEnabled()) { log.trace("Zone : " + zone); Modified: trunk/suiviobsmer-business/src/main/xmi/suiviobsmer.zargo =================================================================== (Binary files differ) Modified: trunk/suiviobsmer-business/src/test/java/fr/ifremer/suiviobsmer/impl/ServiceBoatImplTest.java =================================================================== --- trunk/suiviobsmer-business/src/test/java/fr/ifremer/suiviobsmer/impl/ServiceBoatImplTest.java 2009-12-21 14:22:59 UTC (rev 141) +++ trunk/suiviobsmer-business/src/test/java/fr/ifremer/suiviobsmer/impl/ServiceBoatImplTest.java 2009-12-21 19:07:23 UTC (rev 142) @@ -111,13 +111,49 @@ InputStream input = getClass().getResourceAsStream("/import/navires.csv"); service.importBoatCsv(input); + ServiceUser serviceUser = new ServiceUserImpl(); + Company company = new CompanyImpl(); + company.setName("TARTANPION"); + serviceUser.createUpdateCompany(company); + User user = new UserImpl(); + user.setFirstName("Jean"); + user.setLastName("Michmuche"); + user.setCompany(company); + serviceUser.createUpdateUser(user, true); + List<SampleRow> rows = prepareSampleRows(company); + BoatFilter filter = new BoatFilterImpl(); + // Exec with no filter Map<Integer, Boat> boats = service.getBoatsByFilter(filter); assertEquals(6, boats.size()); // first boat of the file Boat boat = boats.get(273129); assertNotNull(boat); + + // Exec with filter on Immatriculation + filter.setBoatImmatriculation(273129); + boats = service.getBoatsByFilter(filter); + assertEquals(1, boats.size()); + + // Exec with filter on Name : start with "M" + filter = new BoatFilterImpl(); + filter.setBoatName("M"); + boats = service.getBoatsByFilter(filter); + assertEquals(2, boats.size()); + + // Exec with filter on DistrictCode + filter = new BoatFilterImpl(); + filter.setBoatDistrictCode("UN"); + boats = service.getBoatsByFilter(filter); + assertEquals(4, boats.size()); + + // Exec with filter on SampleRowCode and company + filter = new BoatFilterImpl(); + filter.setSampleRowCode("2009_3"); + filter.setCompany(company); + boats = service.getBoatsByFilter(filter); + assertEquals(2, boats.size()); } @Test Modified: trunk/suiviobsmer-ui/src/main/webapp/Boats.tml =================================================================== --- trunk/suiviobsmer-ui/src/main/webapp/Boats.tml 2009-12-21 14:22:59 UTC (rev 141) +++ trunk/suiviobsmer-ui/src/main/webapp/Boats.tml 2009-12-21 19:07:23 UTC (rev 142) @@ -125,47 +125,50 @@ <!-- BOATINFOS:: Informations du navire sélectionné --> <t:if t:test="boatSelectedImmatriculation"> - <t:zone t:id="boatInfosZone" t:update="show" class="fleft" id="so-boats-boat-infos"> - <!-- HEADER For admin --> - <t:if t:test="user.admin"> - <h2>Informations sur <!--${companyBoatInfos.boatInfos.boat.name}-->${boatName}</h2> - <form t:type="form" t:id="companySelectForm" t:zone="so-boats-boat-infos"> - <t:label t:for="company" /> : - <input t:type="select" t:id="company" t:model="companySelectModel" t:value="companyId" />  - <input t:type="submit" class="ico search" t:id="searchCompany" value="Search" /> - </form> - </t:if> - <!-- BODY - delegator --> - <t:if t:test="canShowActiveBoatInfos()"> - <t:delegate to="activeBoatInfosBlock" /> - </t:if> - <!-- BODY - activity calendar --> - <fieldset id="so-boats-boat-infos-calendar"> - <legend>Calendrier d'activité</legend> - <t:unless t:test="activityCalendarImportRun"> - <p> - <t:if t:test="hasActivityCalendar()"> - <a t:type="actionlink" t:id="showLastActivityCalendar"> - <img src="${asset:context:}/img/calendar.png" title="Calendrier d'activité"/> - </a> - <p:else> - <img src="${asset:context:}/img/calendar-unavailable.png" title="Calendrier d'activité indisponible"/> - </p:else> - </t:if> - </p> - <p:else> - <div class="fb-info"> - Le chargement des calendriers d'activité dans l'application est actuellement en cours d'exécution... - </div> - </p:else> + <t:zone t:id="boatInfosZone" t:update="show" class="fleft" id="so-boats-boat-infos"> + <t:unless t:test="activityCalendarImportRun"> + <!-- HEADER For admin --> + <t:if t:test="user.admin"> + <h2>Informations sur <!--${companyBoatInfos.boatInfos.boat.name}-->${boatName}</h2> + <form t:type="form" t:id="companySelectForm" t:zone="so-boats-boat-infos"> + <t:label t:for="company" /> : + <input t:type="select" t:id="company" t:model="companySelectModel" t:value="companyId" />  + <input t:type="submit" class="ico search" t:id="searchCompany" value="Search" /> + </form> + </t:if> + <!-- BODY - delegator --> + <t:if t:test="canShowActiveBoatInfos()"> + <t:delegate to="activeBoatInfosBlock" /> + </t:if> + <!-- BODY - activity calendar --> + <fieldset id="so-boats-boat-infos-calendar"> + <legend>Calendrier d'activité</legend> + <p> + <t:if t:test="hasActivityCalendar()"> + <a t:type="actionlink" t:id="showLastActivityCalendar"> + <img src="${asset:context:}/img/calendar.png" title="Calendrier d'activité"/> + </a> + <p:else> + <img src="${asset:context:}/img/calendar-unavailable.png" title="Calendrier d'activité indisponible"/> + </p:else> + </t:if> + </p> + </fieldset> + <!-- FOOTER For user --> + <t:unless t:test="user.admin"> + <div id="so-boats-boat-infos-message"> + Les informations saisies sont confidentielles et propriété de la société. <br /> + Elles sont néanmoins consultables par un administrateur + </div> </t:unless> - </fieldset> - <!-- FOOTER For user --> - <t:unless t:test="user.admin"> - <div id="so-boats-boat-infos-message"> - Les informations saisies sont confidentielles et propriété de la société. <br /> - Elles sont néanmoins consultables par un administrateur - </div> + <p:else> + <div class="fb-info"> + Le chargement des calendriers d'activité est en cours. <br /> + Vous ne pouvez pas accéder aux informations du navire pour l'instant. <br /> + Veuillez réessayer ultérieurement. + + </div> + </p:else> </t:unless> </t:zone>