DossierResults.java

Go to the documentation of this file.

/*
 * Turró i Cutiller Foundation. License notice.
 * Copyright (C) 2011 Lluis Turró Cutiller <http://www.turro.org/>
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program 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 Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
package org.turro.dossier.search;
 
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Set;
import org.turro.string.Strings;
import org.turro.auth.Authentication;
import org.turro.contacts.Contact;
import org.turro.dossier.db.DossierPU;
import org.turro.dossier.dossier.DossierSet;
import org.turro.dossier.entity.Category;
import org.turro.dossier.entity.Dossier;
import org.turro.dossier.entity.DossierStatus;
import org.turro.dossier.entity.DossierType;
import org.turro.dossier.entity.ParticipantRole;
import org.turro.elephant.context.Application;
import org.turro.elephant.db.WhereClause;
import org.turro.jpa.Dao;
import org.turro.plugin.contacts.IContact;
import org.turro.util.Chars;
 
public class DossierResults {
 
  public static final int CATEGORY_SQL_ITERATIONS = 5;
 
  private final Application app = Application.getApplication();
 
  private String dossierValue = "*";
  private boolean ckOthers = false;
  private Set<ParticipantRole> roles = new HashSet<>();
  private Set<DossierStatus> status = new HashSet<>();
  private Set<DossierType> type = new HashSet<>();
  private Category category;
  private IContact byParticipant;
  private Contact subject = null;
  private String orderBy = null;
  
  public DossierResults() {
    roles.add(ParticipantRole.PARTICIPANT_OWNER);
    roles.add(ParticipantRole.PARTICIPANT_ASSISTANT);
 
    status.add(DossierStatus.DOSSIER_OPENED);
    
    type.add(DossierType.TYPE_DOSSIER);
    type.add(DossierType.TYPE_ELECTION);
    type.add(DossierType.TYPE_PROJECT);
    type.add(DossierType.TYPE_PROPOSAL);
    type.add(DossierType.TYPE_SECRET_ELECTION);
    type.add(DossierType.TYPE_SECRET_PROPOSAL);
  }
  
  public void markAll() {
 
    roles.add(ParticipantRole.PARTICIPANT_OWNER);
    roles.add(ParticipantRole.PARTICIPANT_ASSISTANT);
 
    ckOthers = app.isInRole("dossier:all");
 
    status.add(DossierStatus.DOSSIER_OPENED);
    status.add(DossierStatus.DOSSIER_CLOSED);
    status.add(DossierStatus.DOSSIER_FROZEN);
 
    type.add(DossierType.TYPE_DOSSIER);
    type.add(DossierType.TYPE_ELECTION);
    type.add(DossierType.TYPE_PROJECT);
    type.add(DossierType.TYPE_PROPOSAL);
    type.add(DossierType.TYPE_SECRET_ELECTION);
    type.add(DossierType.TYPE_SECRET_PROPOSAL);
  }
 
  public long getDossierCount() {
    Dao dao = new DossierPU();
    WhereClause wc = new WhereClause();
    wc.addClause("select count(distinct dossier)");
    wc.addClause("from Dossier as dossier");
    wc.addClause("left outer join dossier.participants participant");
    wc.addClause("where 1=1");
    addCriteria(wc);
    return (Long) dao.getSingleResult(wc);
  }
 
  public java.util.List<Dossier> getDossierList() {
    Dao dao = new DossierPU();
    WhereClause wc = new WhereClause();
    wc.addClause("select distinct dossier");
    wc.addClause("from Dossier as dossier");
    wc.addClause("left outer join dossier.participants participant");
    wc.addClause("where 1=1");
    addCriteria(wc);
    return new ArrayList(new DossierSet(dao.getResultList(wc)));
  }
 
  public java.util.List<DossierIds> getDossierIdList() {
    Dao dao = new DossierPU();
    WhereClause wc = new WhereClause();
    wc.addClause("select distinct new org.turro.dossier.search.DossierIds(dossier.id, dossier.category.id, dossier.category.fullDescription)");
    wc.addClause("from Dossier as dossier");
    wc.addClause("left outer join dossier.participants participant");
    wc.addClause("where 1=1");
    addCriteria(wc);
    return dao.getResultList(wc);
  }
 
  public Category getCategory() {
    return category;
  }
 
  public void setCategory(Category category) {
    this.category = category;
  }
 
  public Set<ParticipantRole> getRoles() {
    return roles;
  }
 
  public void setRoles(Set<ParticipantRole> roles) {
    this.roles = roles;
  }
 
  public Set<DossierStatus> getStatus() {
    return status;
  }
 
  public void setStatus(Set<DossierStatus> status) {
    this.status = status;
  }
 
  public Set<DossierType> getType() {
    return type;
  }
 
  public void setType(Set<DossierType> type) {
    this.type = type;
  }
 
  public void setOrderBy(String orderBy) {
    this.orderBy = orderBy;
  }
 
  public void addCriteria(WhereClause wc) {
    boolean asParticipant =
            roles.contains(ParticipantRole.PARTICIPANT_OWNER) ||
            roles.contains(ParticipantRole.PARTICIPANT_ASSISTANT);
    
    if(Strings.isEmpty(dossierValue) || (!asParticipant && !app.isInRole("dossier:all"))) {
      wc.addClause("and 0=1");
      return;
    }
    
    if(byParticipant == null) {
      byParticipant = Authentication.getIContact();
    }
 
    wc.addClause("and ((");
    wc.setPrefix("");
    wc.addLikeFields(new String[] {
              "dossier.description"
              }, (dossierValue == null ? "" : dossierValue.replaceAll("\\*", "%")));
    wc.setPrefix(null);
    wc.addClause("or exists (select fieldValue from dossier.fieldValues fieldValue where 1=1");
    wc.addLikeFields(new String[] {
              "fieldValue.value"
              }, (dossierValue == null ? "" : dossierValue.replaceAll("\\*", "%")));
    wc.setPrefix("");
    wc.addClause("or (fieldValue.value = 'true' and");
    wc.addLikeFields(new String[] {
              "fieldValue.fieldDef.labelKey"
              }, (dossierValue == null ? "" : dossierValue.replaceAll("\\*", "%")));
    wc.setPrefix(null);
    wc.addClause("))");
    wc.addClause("or exists (select parsub from dossier.participants parsub where parsub.role = :subrole");
    wc.addNamedValue("subrole", ParticipantRole.PARTICIPANT_SUBJECT);
    wc.addLikeFields(new String[] {
              "parsub.name"
              }, (dossierValue == null ? "" : dossierValue.replaceAll("\\*", "%")));
    wc.addClause("))");
 
//    wc = SQLHelper.getWhereClause(new String[] {
//              "dossier.description",
//              "fieldValue.value"
//              }, (dossierValue == null ? "" : dossierValue.replaceAll("\\*", "%")));
 
    long id;
    try {
      id = Long.valueOf(dossierValue);
      if(id > 0) {
        wc.addClause("or dossier.id = :id");
        wc.addNamedValue("id", id);
      }
    } catch(Exception ex) {}
    
    wc.addClause(")");
 
    if(asParticipant && !(ckOthers && app.isInRole("dossier:all"))) {
      wc.addClause("and (");
      wc.addClause("(");
      wc.addClause("participant.idContact = :idContact");
      wc.addNamedValue("idContact", byParticipant.getId());
      wc.addClause("and (participant.role = :cpdrole1 or participant.role = :cpdrole2)");
      wc.addNamedValue("cpdrole1", ParticipantRole.PARTICIPANT_OWNER);
      wc.addNamedValue("cpdrole2", ParticipantRole.PARTICIPANT_ASSISTANT);
      wc.addClause(")");
      CategoryResults.addParticipantAffiliance(wc, "or", byParticipant.getId(), "dossier");
      //addCategoryConditionals(wc, sep, byParticipant.getId(), ParticipantRole.PARTICIPANT_OWNER, "=");
      wc.addClause(")");
    }
      
    String sep = "";
    wc.addClause("and (");
    if(status.contains(DossierStatus.DOSSIER_OPENED)) {
      wc.addClause(sep);
      sep = "or";
      wc.addClause("dossier.status = :statusOpened");
      wc.addNamedValue("statusOpened", DossierStatus.DOSSIER_OPENED);
    }
    if(status.contains(DossierStatus.DOSSIER_CLOSED)) {
      wc.addClause(sep);
      sep = "or";
      wc.addClause("dossier.status = :statusClosed");
      wc.addNamedValue("statusClosed", DossierStatus.DOSSIER_CLOSED);
    }
    if(status.contains(DossierStatus.DOSSIER_FROZEN)) {
      wc.addClause(sep);
      sep = "or";
      wc.addClause("dossier.status = :statusFrozen");
      wc.addNamedValue("statusFrozen", DossierStatus.DOSSIER_FROZEN);
    }
    wc.addClause(")");
 
    sep = "";
    wc.addClause("and (");
    if(type.contains(DossierType.TYPE_DOSSIER)) {
      wc.addClause(sep);
      sep = "or";
      wc.addClause("dossier.type = :typeDossier");
      wc.addNamedValue("typeDossier", DossierType.TYPE_DOSSIER);
    }
    if(type.contains(DossierType.TYPE_ELECTION)) {
      wc.addClause(sep);
      sep = "or";
      wc.addClause("dossier.type = :typeElection");
      wc.addNamedValue("typeElection", DossierType.TYPE_ELECTION);
    }
    if(type.contains(DossierType.TYPE_PROJECT)) {
      wc.addClause(sep);
      sep = "or";
      wc.addClause("dossier.type = :typeProject");
      wc.addNamedValue("typeProject", DossierType.TYPE_PROJECT);
    }
    if(type.contains(DossierType.TYPE_PROPOSAL)) {
      wc.addClause(sep);
      sep = "or";
      wc.addClause("dossier.type = :typeProposal");
      wc.addNamedValue("typeProposal", DossierType.TYPE_PROPOSAL);
    }
    if(type.contains(DossierType.TYPE_SECRET_ELECTION)) {
      wc.addClause(sep);
      sep = "or";
      wc.addClause("dossier.type = :typeSecElection");
      wc.addNamedValue("typeSecElection", DossierType.TYPE_SECRET_ELECTION);
    }
    if(type.contains(DossierType.TYPE_SECRET_PROPOSAL)) {
      wc.addClause(sep);
      sep = "or";
      wc.addClause("dossier.type = :typeSecProposal");
      wc.addNamedValue("typeSecProposal", DossierType.TYPE_SECRET_PROPOSAL);
    }
    wc.addClause(")");
 
    if(subject != null) {
      wc.addClause("and ((");
      wc.addClause("exists ( select subject from Participant as subject");
      wc.addClause("where subject.dossier = dossier");
      wc.addClause("and subject.idContact = :idSubject");
      wc.addNamedValue("idSubject", subject.getId());
      wc.addClause("and subject.role = :roleSubject )");
      wc.addNamedValue("roleSubject", ParticipantRole.PARTICIPANT_SUBJECT);
      wc.addClause(") or (");
      wc.addClause("exists ( select csubject from CategoryParticipant as csubject");
      wc.addClause("where csubject.category = dossier.category");
      wc.addClause("and csubject.idContact = :cidSubject");
      wc.addNamedValue("cidSubject", subject.getId());
      wc.addClause("and csubject.role = :croleSubject )");
      wc.addNamedValue("croleSubject", ParticipantRole.PARTICIPANT_SUBJECT);
      wc.addClause("))");
    }
 
    if(category != null) {
      wc.addClause("and (dossier.category.fullDescription = :decat");
      wc.addNamedValue("decat", category.getFullDescription());
      wc.addClause("or dossier.category.fullDescription like :dlcat)");
      wc.addNamedValue("dlcat", category.getFullDescription() + Chars.backward().spaced() + "%");
//      wc.addClause("and (");
//      addCategoryAffiliance(wc, category, "dossier.category");
//      wc.addClause(")");
    }
    
    if(!Strings.isBlank(orderBy)) {
      wc.addClause(orderBy);
    }
  }
 
  public boolean isCkOthers() {
    return ckOthers;
  }
 
  public void setCkOthers(boolean ckOthers) {
    this.ckOthers = ckOthers;
  }
 
  public String getDossierValue() {
    return dossierValue;
  }
 
  public void setDossierValue(String dossierValue) {
    this.dossierValue = dossierValue;
  }
 
  public IContact getByParticipant() {
    return byParticipant;
  }
 
  public void setByParticipant(IContact byParticipant) {
    this.byParticipant = byParticipant;
  }
 
  public Contact getSubject() {
    return subject;
  }
 
  public void setSubject(Contact subject) {
    this.subject = subject;
  }
  
  @Deprecated
  public static void addSubjectAffiliance(WhereClause wc,
          String sep, String id, String dossier) {
    wc.addClause(sep + " exists (");
    wc.addClause("select cp from CategoryParticipant as cp");
    wc.addClause("where cp.idContact = :idContact");
    wc.addNamedValue("idContact", id);
    wc.addClause("and cp.role = :cprole");
    wc.addNamedValue("cprole", ParticipantRole.PARTICIPANT_SUBJECT);
    wc.addClause("and (");
    wc.addClause("cp.category.fullDescription = " + dossier + ".category.fullDescription");
    wc.addClause("or " + dossier + ".category.fullDescription like concat(cp.category.fullDescription, '" + Chars.backward().spaced() + "%')");
    wc.addClause("))");
  }
  
  @Deprecated
  public static String getCategoryJoins() {
    StringBuilder sb = new StringBuilder(
            " left outer join dossier.category.participants cparticipant");
    for(int i = 0; i < CATEGORY_SQL_ITERATIONS; i++) {
      sb.append(" left outer join dossier.category");
      for(int c = 0; c < i; c++) {
        sb.append(".parent");
      }
      sb.append(".participants cparticipant" + i);
    }
    return sb.toString();
  }
 
  @Deprecated
  public static void addCategoryConditionals(WhereClause wc,
          String sep, String id, ParticipantRole participantRole, String operator) {
    for(int i = 0; i < CATEGORY_SQL_ITERATIONS; i++) {
      wc.addClause(sep);
      wc.addClause("(");
      wc.addClause("cparticipant" + i + ".idContact = :idContact" + participantRole.name() + i);
      wc.addNamedValue("idContact" + participantRole.name() + i, id);
      wc.addClause("and cparticipant" + i + ".role " + operator + " :" + participantRole.name() + i);
      wc.addNamedValue(participantRole.name() + i, participantRole);
      wc.addClause(")");
    }
  }
 
  @Deprecated
  public static void addCategoryAffiliance(WhereClause wc, Category category, String field) {
    for(int i = 0; i < CATEGORY_SQL_ITERATIONS; i++) {
      StringBuilder sb = new StringBuilder(field);
      for(int ci = 0; ci < i; ci++) {
        sb.append(".parent");
      }
      wc.addClause(sb.toString() + " = :category" + i);
      wc.addNamedValue("category" + i, category);
      if(i < CATEGORY_SQL_ITERATIONS - 1) wc.addClause("or");
    }
  }
 
}