Author: fdesbois Date: 2009-12-21 22:47:09 +0000 (Mon, 21 Dec 2009) New Revision: 143 Added: trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/QueryBuilder.java Modified: trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/TopiaQuery.java trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/entity/BoatImpl.java trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/entity/BoatInfosImpl.java trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/impl/ServiceBoatImpl.java Log: - Suppress validation test for canCreateContact method - Improve queries Added: trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/QueryBuilder.java =================================================================== --- trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/QueryBuilder.java (rev 0) +++ trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/QueryBuilder.java 2009-12-21 22:47:09 UTC (rev 143) @@ -0,0 +1,71 @@ + +package fr.ifremer.suiviobsmer; + +import fr.ifremer.suiviobsmer.TopiaQuery.Op; +import fr.ifremer.suiviobsmer.bean.ContactState; +import fr.ifremer.suiviobsmer.entity.Boat; +import fr.ifremer.suiviobsmer.entity.Company; +import fr.ifremer.suiviobsmer.entity.Contact; +import java.util.Date; + +/** + * QueryBuilder + * + * Created: 21 déc. 2009 + * + * @author fdesbois + * @version $Revision$ + * + * Mise a jour: $Date$ + * par : $Author$ + */ +public class QueryBuilder { + + /** + * Create the query for the last contact of the boat and company. + * + * @param boat the boat concerned by the contact + * @param company the company concerned by the user who create the contact + * @return the TopiaQuery created + */ + public static TopiaQuery createQueryLastContactForBoat(Boat boat, Company company) { + String contactClassName = Contact.class.getName(); + TopiaQuery subquery = new TopiaQuery(contactClassName + " C2"). + addSelect("MAX(C2.topiaCreateDate)"). + add("C2.boat = C1.boat"). + add("C2.user.company = :company"); + + TopiaQuery query = new TopiaQuery(contactClassName + " C1"). + add("C1.boat", boat). + add("C1.user.company = :company"). + addParam("company", company). + add("C1.topiaCreateDate = (" + subquery.fullQuery() + ")"); + + return query; + } + + /** + * Create the query for contacts linked with a boat with BoardingDone state since the fromDate. + * This query is used to get all boarding dones with the 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 boat the boat concerned by the contact + * @param fromDate the date to start + * @return the TopiaQuery created + */ + public static TopiaQuery createQueryDoneContactsFromDate(Boat boat, Date fromDate) { + TopiaQuery query = new TopiaQuery(Contact.class). + add("boat", boat). + 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; + } + +} Property changes on: trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/QueryBuilder.java ___________________________________________________________________ Added: svn:keywords + "Author Date Id Revision HeadURL" Modified: trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/TopiaQuery.java =================================================================== --- trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/TopiaQuery.java 2009-12-21 19:07:23 UTC (rev 142) +++ trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/TopiaQuery.java 2009-12-21 22:47:09 UTC (rev 143) @@ -35,6 +35,8 @@ protected String orderBy; + protected String groupBy; + protected boolean parentheses; public static enum Op { @@ -81,6 +83,9 @@ if (where != null) { result += where; } + if (groupBy != null) { + result += groupBy; + } if (orderBy != null) { result += orderBy; } @@ -156,6 +161,16 @@ return this; } + public TopiaQuery addGroup(String group) { + if (groupBy == null) { + groupBy = " GROUP BY "; + } else { + groupBy += ", "; + } + groupBy += group; + return this; + } + public List execute(TopiaContext transaction) throws TopiaException { if (log.isTraceEnabled()) { log.trace("# QUERY : " + fullQuery()); Modified: trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/entity/BoatImpl.java =================================================================== --- trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/entity/BoatImpl.java 2009-12-21 19:07:23 UTC (rev 142) +++ trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/entity/BoatImpl.java 2009-12-21 22:47:09 UTC (rev 143) @@ -21,14 +21,12 @@ package fr.ifremer.suiviobsmer.entity; -import fr.ifremer.suiviobsmer.DataLoader; +import fr.ifremer.suiviobsmer.QueryBuilder; import fr.ifremer.suiviobsmer.SuiviObsmerException; -import fr.ifremer.suiviobsmer.SuiviObsmerModelDAOHelper; import fr.ifremer.suiviobsmer.SuiviObsmerContext; +import fr.ifremer.suiviobsmer.TopiaQuery; import fr.ifremer.suiviobsmer.bean.ContactState; import java.io.Serializable; -import java.util.List; -import org.apache.commons.lang.BooleanUtils; import org.nuiton.topia.TopiaContext; import org.slf4j.Logger; import org.slf4j.LoggerFactory; @@ -65,23 +63,24 @@ try { transaction = SuiviObsmerContext.getTopiaRootContext().beginTransaction(); - String contactClassName = Contact.class.getName(); - // Return the last contact for this boat and company - 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", this); +// String contactClassName = Contact.class.getName(); +// // Return the last contact for this boat and company +// 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", this); - if (!results.isEmpty()) { - Contact contact = (Contact)results.get(0); - boolean validation = contact.getValidationProgram() != null || BooleanUtils.isFalse(contact.getValidationCompany()); + TopiaQuery query = QueryBuilder.createQueryLastContactForBoat(this, company); + Contact contact = query.executeToEntity(transaction, Contact.class); + if (contact != null) { + //boolean validation = contact.getValidationProgram() != null || BooleanUtils.isFalse(contact.getValidationCompany()); ContactState state = ContactState.createContactStateEnum(contact.getState()); - result = validation/* && !state.equals(ContactState.BOAT_DEFINITIVE_REFUSED)*/; + result = state.isFinalState(); /*validation && !state.equals(ContactState.BOAT_DEFINITIVE_REFUSED)*/; } transaction.closeContext(); 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 19:07:23 UTC (rev 142) +++ trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/entity/BoatInfosImpl.java 2009-12-21 22:47:09 UTC (rev 143) @@ -21,16 +21,15 @@ package fr.ifremer.suiviobsmer.entity; +import fr.ifremer.suiviobsmer.QueryBuilder; 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 org.nuiton.topia.TopiaContext; import java.util.Date; import org.slf4j.Logger; import org.slf4j.LoggerFactory; -import static fr.ifremer.suiviobsmer.TopiaQuery.Op; /** * BoatInfosImpl @@ -67,7 +66,7 @@ try { transaction = SuiviObsmerContext.getTopiaRootContext().beginTransaction(); - TopiaQuery query = createQueryDoneContactsFromDate(fromDate); + TopiaQuery query = QueryBuilder.createQueryDoneContactsFromDate(getBoat(), fromDate); result = query.executeCount(transaction); transaction.closeContext(); @@ -95,7 +94,7 @@ try { transaction = SuiviObsmerContext.getTopiaRootContext().beginTransaction(); - TopiaQuery query = createQueryDoneContactsFromDate(fromDate); + TopiaQuery query = QueryBuilder.createQueryDoneContactsFromDate(getBoat(), fromDate); query.add("user.company = :company").addParam("company", getCompany()); result = query.executeCount(transaction); @@ -111,29 +110,6 @@ } /** - * 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, 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 19:07:23 UTC (rev 142) +++ trunk/suiviobsmer-business/src/main/java/fr/ifremer/suiviobsmer/impl/ServiceBoatImpl.java 2009-12-21 22:47:09 UTC (rev 143) @@ -23,6 +23,7 @@ import com.csvreader.CsvReader; import fr.ifremer.suiviobsmer.DataLoader; +import fr.ifremer.suiviobsmer.QueryBuilder; import fr.ifremer.suiviobsmer.SuiviObsmerContext; import fr.ifremer.suiviobsmer.SuiviObsmerException; import fr.ifremer.suiviobsmer.SuiviObsmerModelDAOHelper; @@ -410,16 +411,7 @@ } // Last contact - String contactClassName = Contact.class.getName(); - TopiaQuery query = new TopiaQuery(contactClassName + " C1"); - query.add("C1.user.company = :company").addParam("company", company); - query.add("C1.boat", boat); - - 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() + ")"); - + TopiaQuery query = QueryBuilder.createQueryLastContactForBoat(boat, company); Contact contact = query.executeToEntity(transaction, Contact.class); if (contact != null) { @@ -637,29 +629,29 @@ try { transaction = rootContext.beginTransaction(); -// BoatDAO boatDAO = SuiviObsmerModelDAOHelper.getBoatDAO(transaction); -// Boat boat = boatDAO.findByTopiaId(boatId); -// if (boat == null) { -// throw new IllegalArgumentException( -// "Le navire avec l'identifiant '" + boatId + "' n'existe pas"); -// } +// String activityCalendarClassName = ActivityCalendar.class.getName(); +// List results = transaction.find( +// "FROM " + activityCalendarClassName + " A WHERE A.boat = :boat AND A.year = " + +// "(SELECT MAX(B.year) FROM " + activityCalendarClassName + " B " + +// " WHERE B.boat = A.boat GROUP BY B.boat)", +// "boat", boat); +// +// result = (ActivityCalendar) results.get(0); String activityCalendarClassName = ActivityCalendar.class.getName(); - List results = transaction.find( - "FROM " + activityCalendarClassName + " A WHERE A.boat = :boat AND A.year = " + - "(SELECT MAX(B.year) FROM " + activityCalendarClassName + " B " + - " WHERE B.boat = A.boat GROUP BY B.boat)", - "boat", boat); + TopiaQuery query = new TopiaQuery(activityCalendarClassName + " A"). + add("A.boat", boat); - result = (ActivityCalendar) results.get(0); - DataLoader.loadActivityCalendar(result); + TopiaQuery subquery = new TopiaQuery(activityCalendarClassName + " B"). + addSelect("MAX(B.year)"). + add("B.boat = A.boat"). + addGroup("B.boat"); -// ActivityCalendarDAO dao = SuiviObsmerModelDAOHelper.getActivityCalendarDAO(transaction); -// -// result = dao.findByProperties( -// ActivityCalendar.BOAT, boat, -// ActivityCalendar.YEAR, year); + query.add("A.year = (" + subquery.fullQuery() + ")"); + result = query.executeToEntity(transaction, ActivityCalendar.class); + DataLoader.loadActivityCalendar(result); + transaction.closeContext(); } catch (Exception eee) { SuiviObsmerContext.serviceException(transaction,