Java tutorial
/* This file is part of Cyclos (www.cyclos.org). A project of the Social Trade Organisation (www.socialtrade.org). Cyclos 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 2 of the License, or (at your option) any later version. Cyclos 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 Cyclos; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ package nl.strohalm.cyclos.dao.members; import java.sql.SQLException; import java.util.ArrayList; import java.util.Arrays; import java.util.Calendar; import java.util.Collection; import java.util.Collections; import java.util.HashMap; import java.util.HashSet; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Set; import nl.strohalm.cyclos.dao.IndexedDAOImpl; import nl.strohalm.cyclos.dao.JDBCCallback; import nl.strohalm.cyclos.entities.Relationship; import nl.strohalm.cyclos.entities.access.Channel; import nl.strohalm.cyclos.entities.accounts.Account; import nl.strohalm.cyclos.entities.customization.fields.CustomFieldValue; import nl.strohalm.cyclos.entities.customization.fields.MemberCustomField; import nl.strohalm.cyclos.entities.exceptions.EntityNotFoundException; import nl.strohalm.cyclos.entities.exceptions.QueryParseException; import nl.strohalm.cyclos.entities.groups.BrokerGroup; import nl.strohalm.cyclos.entities.groups.Group; import nl.strohalm.cyclos.entities.groups.MemberGroup; import nl.strohalm.cyclos.entities.members.AdminQuery; import nl.strohalm.cyclos.entities.members.Administrator; import nl.strohalm.cyclos.entities.members.Element; import nl.strohalm.cyclos.entities.members.Element.Nature; import nl.strohalm.cyclos.entities.members.ElementQuery; import nl.strohalm.cyclos.entities.members.FullTextAdminQuery; import nl.strohalm.cyclos.entities.members.FullTextElementQuery; import nl.strohalm.cyclos.entities.members.FullTextMemberQuery; import nl.strohalm.cyclos.entities.members.FullTextOperatorQuery; import nl.strohalm.cyclos.entities.members.Member; import nl.strohalm.cyclos.entities.members.MemberQuery; import nl.strohalm.cyclos.entities.members.Operator; import nl.strohalm.cyclos.entities.members.OperatorQuery; import nl.strohalm.cyclos.entities.members.RegistrationAgreement; import nl.strohalm.cyclos.entities.settings.LocalSettings; import nl.strohalm.cyclos.entities.settings.LocalSettings.MemberResultDisplay; import nl.strohalm.cyclos.entities.settings.LocalSettings.SortOrder; import nl.strohalm.cyclos.services.elements.BrokerQuery; import nl.strohalm.cyclos.services.settings.SettingsServiceLocal; import nl.strohalm.cyclos.utils.EntityHelper; import nl.strohalm.cyclos.utils.JDBCWrapper; import nl.strohalm.cyclos.utils.Period; import nl.strohalm.cyclos.utils.hibernate.HibernateCustomFieldHandler; import nl.strohalm.cyclos.utils.hibernate.HibernateHelper; import nl.strohalm.cyclos.utils.lucene.Filters; import nl.strohalm.cyclos.utils.lucene.LuceneUtils; import nl.strohalm.cyclos.utils.query.PageParameters; import nl.strohalm.cyclos.utils.query.QueryParameters.ResultType; import org.apache.commons.collections.CollectionUtils; import org.apache.commons.lang.StringUtils; import org.apache.lucene.analysis.Analyzer; import org.apache.lucene.queryParser.MultiFieldQueryParser; import org.apache.lucene.queryParser.ParseException; import org.apache.lucene.search.MatchAllDocsQuery; import org.apache.lucene.search.Query; import org.apache.lucene.search.Sort; import org.apache.lucene.search.SortField; import org.hibernate.SQLQuery; /** * Implementation class for element DAO * @author rafael * @author luis */ public class ElementDAOImpl extends IndexedDAOImpl<Element> implements ElementDAO { private static final String[] FIELDS_FULL_TEXT = { "name", "username", "email", "customValues" }; private HibernateCustomFieldHandler hibernateCustomFieldHandler; private SettingsServiceLocal settingsService; public ElementDAOImpl() { super(Element.class); } public void activateMembersOfGroup(final MemberGroup group) { final Map<String, Object> namedParameters = new HashMap<String, Object>(); namedParameters.put("date", Calendar.getInstance()); namedParameters.put("group", group); bulkUpdate("update Member set activationDate = :date where group = :group and activationDate is null", namedParameters); } public void createAgreementForAllMembers(final RegistrationAgreement registrationAgreement, final MemberGroup group) { runNative(new JDBCCallback() { public void execute(final JDBCWrapper jdbc) throws SQLException { final String insert = "insert into registration_agreement_logs (member_id, registration_agreement_id, date) select id, ?, ? from members where group_id = ?"; jdbc.execute(insert, registrationAgreement.getId(), Calendar.getInstance(), group.getId()); } }); } public List<? extends Element> fullTextSearch(final FullTextElementQuery elementQuery) { final String keywords = StringUtils.trimToNull(elementQuery.getKeywords()); final Nature nature = elementQuery.getNature(); // We cannot search on a null nature if (nature == null) { return Collections.emptyList(); } // When searching by keywords, use the full-text query Analyzer analyzer = elementQuery.getAnalyzer(); Query query; Sort sort = null; if (keywords == null) { query = new MatchAllDocsQuery(); sort = new Sort(new SortField("creationDate", SortField.STRING, true)); } else { try { query = keywords == null ? new MatchAllDocsQuery() : getQueryParser(analyzer).parse(keywords); } catch (final ParseException e) { throw new QueryParseException(e); } } final Filters filters = new Filters(); filters.addTerms("active", elementQuery.getEnabled()); filters.addTerms("group", elementQuery.getGroups()); final Collection<? extends CustomFieldValue> customValues = elementQuery.getCustomValues(); if (CollectionUtils.isNotEmpty(customValues)) { for (final CustomFieldValue fieldValue : customValues) { addCustomField(filters, analyzer, fieldValue); } } if (CollectionUtils.isNotEmpty(elementQuery.getExcludeElements())) { Collection<Long> excludeIds = EntityHelper.toIdsAsList(elementQuery.getExcludeElements()); filters.add(Filters.andNot(Filters.terms("id", excludeIds))); } if (elementQuery instanceof FullTextMemberQuery) { final FullTextMemberQuery memberQuery = (FullTextMemberQuery) elementQuery; filters.addPeriod("activationDate", memberQuery.getActivationPeriod()); filters.addTerms("broker", memberQuery.getBroker()); if (memberQuery.isWithImagesOnly()) { filters.addTerms("hasImages", true); } sort = decideSorting(memberQuery); } else if (elementQuery instanceof FullTextOperatorQuery) { final FullTextOperatorQuery operatorQuery = (FullTextOperatorQuery) elementQuery; final Member member = operatorQuery.getMember(); if (member == null) { // Cannot search operators without a member return Collections.emptyList(); } filters.addTerms("member", member); } else if (elementQuery instanceof FullTextAdminQuery) { sort = decideSorting(elementQuery); } return list(nature.getElementClass(), elementQuery, query, filters, sort); } public Map<Long, Integer> getCountPerGroup(final Collection<MemberGroup> groups) { Map<String, ?> params = Collections.singletonMap("groups", groups); StringBuilder hql = new StringBuilder(); hql.append(" select g.id, count(m.id) "); hql.append(" from Member m join m.group g "); hql.append(" where g in (:groups) "); hql.append(" group by g.id "); return this.<Long, Integer>map(hql.toString(), params); } public Map<Long, Integer> getCountPerGroup(final Collection<MemberGroup> groups, final Calendar timePoint) { Map<String, Object> params = new HashMap<String, Object>(); params.put("groups", groups); params.put("timePoint", timePoint); StringBuilder hql = new StringBuilder(); hql.append(" select g.id, count(m.id) "); hql.append(" from GroupHistoryLog l join l.element m join l.group g "); hql.append(" where g in (:groups) "); hql.append(" and l.period.begin <= :timePoint "); hql.append(" and (l.period.end is null or l.period.end > :timePoint)"); hql.append(" group by g.id"); return this.<Long, Integer>map(hql.toString(), params); } public Calendar getFirstMemberActivationDate() { final String hql = "select min(activationDate) from Member"; return uniqueResult(hql, new HashMap<String, Object>()); } public HibernateCustomFieldHandler getHibernateCustomFieldHandler() { return hibernateCustomFieldHandler; } public List<Number[]> getNewMembersCountThroughTheTime(final Collection<? extends Group> groups, final Period period) { final StringBuilder hql = new StringBuilder( "select month(m.creationDate), year(m.creationDate), count(m.id) "); hql.append(" from Member m "); hql.append(" where 1=1 "); final Map<String, Object> namedParameters = new HashMap<String, Object>(); if (groups != null && !groups.isEmpty()) { HibernateHelper.addInParameterToQuery(hql, namedParameters, "m.group", groups); } if (period != null) { HibernateHelper.addPeriodParameterToQuery(hql, namedParameters, "m.creationDate", period); } hql.append(" group by month(m.creationDate), year(m.creationDate) "); hql.append(" order by year(m.creationDate), month(m.creationDate) "); final List<Object[]> results = list(hql.toString(), namedParameters); final Iterator<Object[]> i = results.iterator(); final List<Number[]> numberList = new ArrayList<Number[]>(); while (i.hasNext()) { final Object[] row = i.next(); final Number[] intRow = new Integer[3]; intRow[0] = (Integer) row[0]; intRow[1] = (Integer) row[1]; intRow[2] = (Integer) row[2]; numberList.add(intRow); } return numberList; } /** * gets the number of members which were in the specified group at any moment during the specified period. Used by Activity Stats: gross Product, * number of transactions and % not trading for compare periods, Histogram, Single Period, and by Key Dev Stats number of members * * @param groups the set of groups in which the members must be counted. * @param period the period in which they should be part of any of the groups * @return an int indicating the number of members during that period in that set of groups * */ public int getNumberOfMembersInGroupsInPeriod(final Collection<? extends Group> groups, final Period period) { final StringBuilder hql = new StringBuilder(" select count(m.id) from Member m where 1=1 "); final Map<String, Object> namedParameters = new HashMap<String, Object>(); if (CollectionUtils.isEmpty(groups) && period != null && period.getEnd() != null) { namedParameters.put("endDate", period.getEnd()); hql.append(" and m.creationDate <= :endDate "); } else if (!CollectionUtils.isEmpty(groups) && period != null && period.getEnd() != null && period.getBegin() != null) { namedParameters.put("beginDate", period.getBegin()); namedParameters.put("endDate", period.getEnd()); namedParameters.put("groups", groups); // First condition: it has been in one of the selected groups hql.append(" and ( (m.group in (:groups) and m.creationDate < :endDate and not exists "); hql.append(" (select gr1.id from GroupRemark gr1 where gr1.subject = m)) or "); // Second: Changed the member's group inside the period. hql.append( " (m.creationDate < :endDate and exists (select gr.id from GroupRemark gr where gr.subject=m and (gr.oldGroup in "); hql.append( " (:groups) or gr.newGroup in (:groups)) and gr.date > :beginDate and gr.date <= :endDate)) or "); // Third condition: the group remark right before the period put the member in one // of the selected groups hql.append(" exists (select gr2.id from GroupRemark gr2 where gr2.subject=m and "); hql.append(" gr2.newGroup in (:groups) and gr2.date=(select max(gr3.date) from GroupRemark "); hql.append(" gr3 where gr3.subject=m and gr3.date < :beginDate)) or "); // Fourth condition: the group remark right after the begin period: the member was created // then the group was changed in the period, we must use oldGroup. hql.append(" (m.creationDate <= :endDate and exists (select gr2.id from GroupRemark gr2 where "); hql.append(" gr2.subject=m and gr2.oldGroup in (:groups) and gr2.date = (select min(gr3.date) "); hql.append(" from GroupRemark gr3 where gr3.subject=m and gr3.date > :endDate))) "); hql.append(" ) "); } else if (!CollectionUtils.isEmpty(groups)) { hql.append(" and m.group in (:groups) "); namedParameters.put("groups", groups); } final Number count = uniqueResult(hql.toString(), namedParameters); return count.intValue(); } public List<Number[]> getRemovedMembersCountThroughTheTime(final Collection<? extends Group> groups, final Period period) { final Map<String, Object> namedParameters = new HashMap<String, Object>(); final StringBuilder hql = new StringBuilder("select month(gr.date), year(gr.date), count(gr.id) "); hql.append(" from GroupRemark gr "); hql.append(" where 1=1 "); hql.append(" and exists ( "); hql.append(" select gr.id "); hql.append(" from GroupRemark gr "); hql.append(" where "); hql.append(" gr.subject = e "); hql.append(" and gr.newGroup.status = :removed "); namedParameters.put("removed", Group.Status.REMOVED); // Deactivation period if (period != null) { HibernateHelper.addPeriodParameterToQuery(hql, namedParameters, "gr.date", period); } if (groups != null && !groups.isEmpty()) { hql.append(" and gr.oldGroup in (:groups) "); namedParameters.put("groups", groups); } else { return new ArrayList<Number[]>(); } hql.append(" )"); hql.append(" and gr.newGroup.status = :removed "); hql.append(" group by month(gr.date), year(gr.date) "); hql.append(" order by year(gr.date), month(gr.date) "); namedParameters.put("removed", Group.Status.REMOVED); // Map<Integer, Integer> removedMembersCountThroughTheYears = new HashMap<Integer, Integer>(); final List<Object[]> results = list(hql.toString(), namedParameters); final Iterator<Object[]> i = results.iterator(); final List<Number[]> numberList = new ArrayList<Number[]>(); while (i.hasNext()) { final Object[] row = i.next(); final Number[] intRow = new Integer[3]; intRow[0] = (Integer) row[0]; intRow[1] = (Integer) row[1]; intRow[2] = (Integer) row[2]; numberList.add(intRow); } return numberList; } public boolean hasValueForField(final Member member, final MemberCustomField field) { final Map<String, Object> namedParameters = new HashMap<String, Object>(); namedParameters.put("member", member); namedParameters.put("field", field); final StringBuilder hql = new StringBuilder(); hql.append(" select 1"); hql.append(" from MemberCustomFieldValue fv"); hql.append(" where fv.member = :member"); hql.append(" and fv.field = :field"); hql.append( " and (fv.possibleValue is not null or (fv.stringValue is not null and length(fv.stringValue) > 0))"); final List<?> list = list(ResultType.LIST, hql.toString(), namedParameters, PageParameters.max(1)); return !list.isEmpty(); } public Iterator<Member> iterateMembers(final boolean ordered, final MemberGroup... groups) { if (groups == null || groups.length == 0) { return Collections.<Member>emptyList().iterator(); } final Map<String, List<MemberGroup>> parameters = Collections.singletonMap("groups", Arrays.asList(groups)); return iterate("from Member m left join fetch m.user where m.group in (:groups) " + (ordered ? "order by m.name, m.user.username" : ""), parameters); } public List<Member> listMembersRegisteredBeforeOnGroup(final Calendar date, final MemberGroup group) { final StringBuilder hql = new StringBuilder(); hql.append(" select m"); hql.append(" from GroupHistoryLog log, Member m left join fetch m.user "); hql.append(" where log.element = m "); hql.append(" and log.element.group = :group "); hql.append(" and log.group = :group "); hql.append(" and log.period.end is null "); hql.append(" and log.period.begin < :date"); final Map<String, Object> namedParameters = new HashMap<String, Object>(); namedParameters.put("date", date); namedParameters.put("group", group); return list(ResultType.ITERATOR, hql.toString(), namedParameters, null, Element.Relationships.USER, Element.Relationships.GROUP); } public Member loadByCustomField(final MemberCustomField customField, final String value, final Relationship[] fetch) { final Map<String, Object> namedParameters = new HashMap<String, Object>(); final StringBuilder hql = new StringBuilder(); hql.append(" select m"); hql.append( " from MemberCustomFieldValue fv inner join fv.member m inner join m.user u inner join fetch m.group g"); hql.append(" where g.status <> :removed"); hql.append(" and fv.field = :field"); hql.append(" and fv.stringValue = :value"); namedParameters.put("removed", Group.Status.REMOVED); namedParameters.put("field", customField); namedParameters.put("value", value); final Member member = uniqueResult(hql.toString(), namedParameters); if (member == null) { throw new EntityNotFoundException(Member.class, null, String.format("Custom field used to load: <%1$s, %2$s>", customField.getInternalName(), value)); } return member; } public Element loadByEmail(final String email, final Relationship... fetch) throws EntityNotFoundException { final Map<String, Object> namedParameters = new HashMap<String, Object>(); final StringBuilder hql = HibernateHelper.getInitialQuery(Member.class, "m", Arrays.asList(fetch)); hql.append(" and m.group.status <> :removed"); hql.append(" and m.email = :email"); namedParameters.put("removed", Group.Status.REMOVED); namedParameters.put("email", email); final Element element = uniqueResult(hql.toString(), namedParameters); if (element == null) { throw new EntityNotFoundException(Element.class); } return element; } public void removeChannelsFromMembers(final MemberGroup group, final Collection<Channel> channels) { if (CollectionUtils.isNotEmpty(channels)) { final Map<String, Object> parameters = new HashMap<String, Object>(); final Set<Long> channelIds = new HashSet<Long>(); CollectionUtils.addAll(channelIds, EntityHelper.toIds(channels)); parameters.put("channelIds", channelIds); parameters.put("groupId", group.getId()); final String statement = " delete from members_channels " + " where channel_id in (:channelIds) " + " and member_id in (select id from members where group_id = :groupId) "; final SQLQuery query = getSession().createSQLQuery(statement); getHibernateQueryHandler().setQueryParameters(query, parameters); query.executeUpdate(); } } public List<Element> search(final ElementQuery query) { Class<? extends Element> entityType; if (query instanceof AdminQuery) { entityType = Administrator.class; } else if (query instanceof MemberQuery) { entityType = Member.class; } else if (query instanceof OperatorQuery) { entityType = Operator.class; } else { throw new IllegalArgumentException("Invalid query parameters: " + query); } final Map<String, Object> namedParameters = new HashMap<String, Object>(); final Set<Relationship> fetch = query.getFetch(); final StringBuilder hql = new StringBuilder(); if (query instanceof MemberQuery && ((MemberQuery) query).isHasAds()) { hql.append(" select distinct e"); hql.append(" from ").append(entityType.getName()).append(" e inner join e.ads ad "); } else { hql.append(" select e"); hql.append(" from ").append(entityType.getName()).append(" e "); } hibernateCustomFieldHandler.appendJoins(hql, "e.customValues", query.getCustomValues()); HibernateHelper.appendJoinFetch(hql, entityType, "e", fetch); hql.append(" where 1=1 "); if (query instanceof BrokerQuery) { hql.append(" and exists (select 1 from " + BrokerGroup.class.getName() + " bg where bg = e.group) "); } if (query.getExcludeElements() != null && !query.getExcludeElements().isEmpty()) { hql.append(" and e not in (:excludeElements) "); namedParameters.put("excludeElements", query.getExcludeElements()); } if (query.isExcludeRemoved()) { hql.append(" and e.group.status <> :removedStatus"); namedParameters.put("removedStatus", Group.Status.REMOVED); } HibernateHelper.addRightLikeParameterToQuery(hql, namedParameters, "e.user.username", query.getUsername()); HibernateHelper.addLikeParameterToQuery(hql, namedParameters, "e.name", query.getName()); HibernateHelper.addRightLikeParameterToQuery(hql, namedParameters, "e.email", query.getEmail()); // Group filters are handled at service level if (query.getGroups() != null && !query.getGroups().isEmpty()) { HibernateHelper.addInParameterToQuery(hql, namedParameters, "e.group", query.getGroups()); } HibernateHelper.addPeriodParameterToQuery(hql, namedParameters, "e.creationDate", query.getCreationPeriod()); final Boolean enabled = query.getEnabled(); // Specific tests for admins and members if (query instanceof AdminQuery) { if (enabled != null) { // If searching for admins, enabled means normal groups, while disabled means removed admins final Group.Status groupStatus = enabled ? Group.Status.NORMAL : Group.Status.REMOVED; HibernateHelper.addParameterToQuery(hql, namedParameters, "e.group.status", groupStatus); } } else if (query instanceof MemberQuery) { final MemberQuery memberQuery = (MemberQuery) query; HibernateHelper.addPeriodParameterToQuery(hql, namedParameters, "e.activationDate", memberQuery.getActivationPeriod()); if (enabled != null) { // For members enabled means the activationData must be not null / null for Enable / Disabled hql.append(" and e.activationDate is " + (enabled ? "not" : "") + " null "); if (enabled) { // Enabled also has a normal group HibernateHelper.addParameterToQuery(hql, namedParameters, "e.group.status", Group.Status.NORMAL); } } // With images only if (memberQuery.isWithImagesOnly()) { hql.append(" and exists (select mi.id from MemberImage mi where mi.member=e)"); } // Deactivation period final Period deactivationPeriod = memberQuery.getDeactivationPeriod(); if (deactivationPeriod != null) { hql.append(" and exists ( "); hql.append(" select gr.id "); hql.append(" from GroupRemark gr "); hql.append(" where "); hql.append(" gr.subject = e "); hql.append(" and gr.newGroup.status = :removed "); HibernateHelper.addPeriodParameterToQuery(hql, namedParameters, "gr.date", deactivationPeriod); hql.append(" )"); namedParameters.put("removed", Group.Status.REMOVED); } // Has ads final boolean hasAds = memberQuery.isHasAds(); if (hasAds) { hql.append(" and ad.permanent=true or ( "); hql.append(" ad.publicationPeriod.begin <= current_date() and "); hql.append(" ad.publicationPeriod.end >= current_date() "); hql.append(" ) "); } // Broker if (memberQuery.getBroker() != null) { HibernateHelper.addParameterToQuery(hql, namedParameters, "e.broker", memberQuery.getBroker()); } // Group filters if (CollectionUtils.isNotEmpty(memberQuery.getGroupFilters())) { hql.append( " and exists (select gf.id from GroupFilter gf where gf in (:groupFilters) and e.group in elements(gf.groups))"); namedParameters.put("groupFilters", memberQuery.getGroupFilters()); } } else if (query instanceof OperatorQuery) { final OperatorQuery operatorQuery = (OperatorQuery) query; hql.append(" and exists ("); hql.append(" select o.id from Operator o where o = e and o.member = :member"); hql.append(" )"); namedParameters.put("member", operatorQuery.getMember()); } if (query.getViewableGroup() != null) { hql.append(" and :mg in elements(e.group.canViewProfileOfGroups)"); namedParameters.put("mg", query.getViewableGroup()); } hibernateCustomFieldHandler.appendConditions(hql, namedParameters, query.getCustomValues()); if (query.isRandomOrder()) { HibernateHelper.appendOrder(hql, "rand()"); } else if (query.getOrder() != null) { switch (query.getOrder()) { case USERNAME: HibernateHelper.appendOrder(hql, "e.user.username"); break; case NAME: HibernateHelper.appendOrder(hql, "e.name", "e.id"); break; } } return list(query, hql.toString(), namedParameters); } @SuppressWarnings("unchecked") public Iterator<Member> searchActiveMembers(final Collection<Group> toSearch) { return getHibernateTemplate() .iterate(" from " + Member.class.getName() + " m where m.group in (?) and exists (select 1 from " + Account.class.getName() + " a where a.member = m) ", toSearch); } public List<Element> searchAtDate(final MemberQuery query, final Calendar date) { final StringBuilder hql = HibernateHelper.getInitialQuery(Member.class, "m", query.getFetch()); final Map<String, Object> namedParameters = new HashMap<String, Object>(); if (query.getBroker() != null) { hql.append(" and m.broker = :broker "); namedParameters.put("broker", query.getBroker()); } if (date == null) { hql.append(" and m.group in (:groups) "); namedParameters.put("groups", query.getGroups()); } else { if (!CollectionUtils.isEmpty(query.getGroups())) { hql.append(" and ( m.creationDate <= :date "); hql.append(" and (m.group in (:groups) and not exists "); hql.append(" (select gr1.id from GroupRemark gr1 where gr1.subject=m)) "); hql.append(" or exists (select gr2.id from GroupRemark gr2 where gr2.subject=m and "); hql.append(" gr2.newGroup in (:groups) and gr2.date= "); hql.append(" (select max(gr3.date) from GroupRemark gr3 "); hql.append(" where gr3.subject=m and gr3.date <= :date)) "); hql.append(" or (m.creationDate <= :date and exists (select gr2.id from "); hql.append(" GroupRemark gr2 where gr2.subject=m and gr2.oldGroup in (:groups) "); hql.append(" and gr2.date = (select min(gr3.date) from GroupRemark gr3 "); hql.append(" where gr3.subject=m and gr3.date > :date)) ))"); namedParameters.put("groups", query.getGroups()); } else { hql.append(" and m.creationDate <= :date "); } namedParameters.put("date", date); } return list(ResultType.ITERATOR, hql.toString(), namedParameters, query.getPageParameters()); } // Used by Stats Key Dev > number new members public List<Element> searchHistoryNew(final ElementQuery query) { Class<? extends Element> entityType; if (query instanceof AdminQuery) { entityType = Administrator.class; } else { entityType = Member.class; } final Map<String, Object> namedParameters = new HashMap<String, Object>(); final Set<Relationship> fetch = query.getFetch(); final StringBuilder hql = new StringBuilder(); hql.append(" select e"); hql.append(" from ").append(entityType.getName()).append(" e "); hibernateCustomFieldHandler.appendJoins(hql, "e.customValues", query.getCustomValues()); HibernateHelper.appendJoinFetch(hql, entityType, "e", fetch); hql.append(" where 1=1"); HibernateHelper.addRightLikeParameterToQuery(hql, namedParameters, "e.user.username", query.getUsername()); HibernateHelper.addLikeParameterToQuery(hql, namedParameters, "e.name", query.getName()); final Collection<? extends Group> groups = query.getGroups(); final Boolean enabled = query.getEnabled(); // Specific tests for admins and members if (query instanceof AdminQuery) { HibernateHelper.addPeriodParameterToQuery(hql, namedParameters, "e.creationDate", query.getCreationPeriod()); if (groups != null && !groups.isEmpty()) { HibernateHelper.addInParameterToQuery(hql, namedParameters, "e.group", groups); } if (enabled != null) { // If searching for admins, enabled means normal groups, while disabled means removed admins final Group.Status groupStatus = enabled ? Group.Status.NORMAL : Group.Status.REMOVED; HibernateHelper.addParameterToQuery(hql, namedParameters, "e.group.status", groupStatus); } } else { if (groups != null && !groups.isEmpty()) { hql.append(" and ( ( 1 = 1"); final Period creationPeriod = query.getCreationPeriod(); if (creationPeriod != null) { HibernateHelper.addPeriodParameterToQuery(hql, namedParameters, "e.creationDate", creationPeriod); } hql.append(" and ((not exists "); hql.append(" (select gr.id from GroupRemark gr where gr.subject = e) "); hql.append(" and e.group in (:groups) )"); namedParameters.put("groups", groups); hql.append(" or exists ( "); hql.append(" select gr.id "); hql.append(" from GroupRemark gr "); hql.append(" where "); hql.append(" gr.subject = e "); if (groups != null && !groups.isEmpty()) { hql.append(" and gr.oldGroup in (:groups) ) "); } hql.append(")) or "); hql.append(" exists ( "); hql.append(" select gr.id "); hql.append(" from GroupRemark gr "); hql.append(" where "); hql.append(" gr.subject = e "); // using 'creation period' if (creationPeriod != null) { HibernateHelper.addPeriodParameterToQuery(hql, namedParameters, "gr.date", creationPeriod); } if (groups != null && !groups.isEmpty()) { // hql.append(" and (gr.newGroup in (:groups) "); hql.append(" and gr.newGroup in (:groups) and gr.oldGroup not in (:groups) "); namedParameters.put("groups", groups); } hql.append(" )"); hql.append(" ) "); } else { final Period creationPeriod = query.getCreationPeriod(); if (creationPeriod != null) { HibernateHelper.addPeriodParameterToQuery(hql, namedParameters, "e.creationDate", creationPeriod); } } } // Custom Values hibernateCustomFieldHandler.appendConditions(hql, namedParameters, query.getCustomValues()); HibernateHelper.appendOrder(hql, "e.user.username"); return list(query, hql.toString(), namedParameters); } // Used by Stats Key Developments > number of (disappeared) members public List<Element> searchHistoryRemoved(final ElementQuery query) { Class<? extends Element> entityType; if (query instanceof AdminQuery) { entityType = Administrator.class; } else { entityType = Member.class; } final Map<String, Object> namedParameters = new HashMap<String, Object>(); final Set<Relationship> fetch = query.getFetch(); final StringBuilder hql = new StringBuilder(); hql.append(" select e"); hql.append(" from ").append(entityType.getName()).append(" e "); hibernateCustomFieldHandler.appendJoins(hql, "e.customValues", query.getCustomValues()); HibernateHelper.appendJoinFetch(hql, entityType, "e", fetch); hql.append(" where 1=1"); if (query instanceof BrokerQuery) { hql.append(" and exists (select 1 from " + BrokerGroup.class.getName() + " bg where bg = e.group) "); } HibernateHelper.addRightLikeParameterToQuery(hql, namedParameters, "e.user.username", query.getUsername()); HibernateHelper.addLikeParameterToQuery(hql, namedParameters, "e.name", query.getName()); final MemberQuery memberQuery = (MemberQuery) query; final Boolean enabled = query.getEnabled(); // Specific tests for admins and members if (query instanceof AdminQuery) { if (enabled != null) { // If searching for admins, enabled means normal groups, while disabled means removed admins final Group.Status groupStatus = enabled ? Group.Status.NORMAL : Group.Status.REMOVED; HibernateHelper.addParameterToQuery(hql, namedParameters, "e.group.status", groupStatus); } } else { hql.append(" and exists ( "); hql.append(" select gr.id "); hql.append(" from GroupRemark gr "); hql.append(" where "); hql.append(" gr.subject = e "); // Deactivation period final Period deactivationPeriod = memberQuery.getDeactivationPeriod(); if (deactivationPeriod != null) { HibernateHelper.addPeriodParameterToQuery(hql, namedParameters, "gr.date", deactivationPeriod); } // if at least one group was chosen. // if more than one group is selected, it should not count moving // the members from and to those groups, because they are the 'same group' in this case. final Collection<? extends Group> groups = query.getGroups(); if (groups != null && !groups.isEmpty()) { hql.append(" and gr.oldGroup in (:groups) and gr.newGroup not in (:groups) "); namedParameters.put("groups", groups); } // no group was chosen, no disappears members is returned; else { return new ArrayList<Element>(); } hql.append(" )"); } // Custom Values hibernateCustomFieldHandler.appendConditions(hql, namedParameters, query.getCustomValues()); HibernateHelper.appendOrder(hql, "e.user.username"); return list(query, hql.toString(), namedParameters); } public void setHibernateCustomFieldHandler(final HibernateCustomFieldHandler hibernateCustomFieldHandler) { this.hibernateCustomFieldHandler = hibernateCustomFieldHandler; } public void setSettingsServiceLocal(final SettingsServiceLocal settingsService) { this.settingsService = settingsService; } private Sort decideSorting(final FullTextElementQuery elementQuery) { Sort sort; // sorting LocalSettings localSettings = settingsService.getLocalSettings(); SortOrder memberSortOrder = localSettings.getMemberSortOrder(); if (elementQuery instanceof FullTextMemberQuery) { FullTextMemberQuery memberQuery = (FullTextMemberQuery) elementQuery; if (memberQuery.getMemberSortOrder() != null) { memberSortOrder = memberQuery.getMemberSortOrder(); } } if (memberSortOrder == SortOrder.CHRONOLOGICAL) { sort = new Sort(new SortField("creationDate", SortField.STRING, true)); } else { if (elementQuery.getNameDisplay() == MemberResultDisplay.NAME) { sort = new Sort(new SortField("nameForSort", SortField.STRING)); } else { sort = new Sort(new SortField("usernameForSort", SortField.STRING)); } } return sort; } private MultiFieldQueryParser getQueryParser(final Analyzer analyzer) { final Map<String, Float> boosts = new HashMap<String, Float>(); boosts.put("name", 2.0F); boosts.put("username", 1.5F); return new MultiFieldQueryParser(LuceneUtils.LUCENE_VERSION, FIELDS_FULL_TEXT, analyzer, boosts); } }