cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/persistence/dao/hibernate/taxon/TaxonDaoHibernateImpl.java

   1 /**
   2  * Copyright (C) 2007 EDIT
   3  * European Distributed Institute of Taxonomy
   4  * http://www.e-taxonomy.eu
   5  *
   6  * The contents of this file are subject to the Mozilla Public License Version 1.1
   7  * See LICENSE.TXT at the top of this package for the full license terms.
   8  */
   9 package eu.etaxonomy.cdm.persistence.dao.hibernate.taxon;
  10
  11 import java.util.ArrayList;
  12 import java.util.Collection;
  13 import java.util.Collections;
  14 import java.util.Comparator;
  15 import java.util.HashSet;
  16 import java.util.Iterator;
  17 import java.util.List;
  18 import java.util.Set;
  19 import java.util.SortedSet;
  20 import java.util.TreeSet;
  21 import java.util.UUID;
  22
  23 import org.apache.commons.lang.StringUtils;
  24 import org.apache.log4j.Logger;
  25 import org.hibernate.Criteria;
  26 import org.hibernate.FetchMode;
  27 import org.hibernate.Hibernate;
  28 import org.hibernate.Query;
  29 import org.hibernate.Session;
  30 import org.hibernate.criterion.Criterion;
  31 import org.hibernate.criterion.Order;
  32 import org.hibernate.criterion.Projections;
  33 import org.hibernate.criterion.Restrictions;
  34 import org.hibernate.envers.query.AuditEntity;
  35 import org.hibernate.envers.query.AuditQuery;
  36 import org.hibernate.envers.query.criteria.internal.NotNullAuditExpression;
  37 import org.hibernate.envers.query.internal.property.EntityPropertyName;
  38 import org.hibernate.search.FullTextSession;
  39 import org.hibernate.search.Search;
  40 import org.springframework.beans.factory.annotation.Autowired;
  41 import org.springframework.beans.factory.annotation.Qualifier;
  42 import org.springframework.dao.DataAccessException;
  43 import org.springframework.stereotype.Repository;
  44
  45 import eu.etaxonomy.cdm.model.common.DefinedTerm;
  46 import eu.etaxonomy.cdm.model.common.IdentifiableEntity;
  47 import eu.etaxonomy.cdm.model.common.IdentifiableSource;
  48 import eu.etaxonomy.cdm.model.common.LSID;
  49 import eu.etaxonomy.cdm.model.common.MarkerType;
  50 import eu.etaxonomy.cdm.model.common.RelationshipBase.Direction;
  51 import eu.etaxonomy.cdm.model.location.NamedArea;
  52 import eu.etaxonomy.cdm.model.name.Rank;
  53 import eu.etaxonomy.cdm.model.name.TaxonName;
  54 import eu.etaxonomy.cdm.model.name.TaxonNameComparator;
  55 import eu.etaxonomy.cdm.model.reference.Reference;
  56 import eu.etaxonomy.cdm.model.taxon.Classification;
  57 import eu.etaxonomy.cdm.model.taxon.Synonym;
  58 import eu.etaxonomy.cdm.model.taxon.SynonymType;
  59 import eu.etaxonomy.cdm.model.taxon.Taxon;
  60 import eu.etaxonomy.cdm.model.taxon.TaxonBase;
  61 import eu.etaxonomy.cdm.model.taxon.TaxonNode;
  62 import eu.etaxonomy.cdm.model.taxon.TaxonRelationship;
  63 import eu.etaxonomy.cdm.model.taxon.TaxonRelationshipType;
  64 import eu.etaxonomy.cdm.model.taxon.UuidAndTitleCacheTaxonComparator;
  65 import eu.etaxonomy.cdm.model.view.AuditEvent;
  66 import eu.etaxonomy.cdm.persistence.dao.hibernate.common.IdentifiableDaoBase;
  67 import eu.etaxonomy.cdm.persistence.dao.name.ITaxonNameDao;
  68 import eu.etaxonomy.cdm.persistence.dao.taxon.ITaxonDao;
  69 import eu.etaxonomy.cdm.persistence.dto.UuidAndTitleCache;
  70 import eu.etaxonomy.cdm.persistence.query.MatchMode;
  71 import eu.etaxonomy.cdm.persistence.query.NameSearchOrder;
  72 import eu.etaxonomy.cdm.persistence.query.OrderHint;
  73 import eu.etaxonomy.cdm.persistence.query.TaxonTitleType;
  74
  75
  76 /**
  77  * @author a.mueller
  78  * @since 24.11.2008
  79  */
  80 @Repository
  81 @Qualifier("taxonDaoHibernateImpl")
  82 public class TaxonDaoHibernateImpl
  83               extends IdentifiableDaoBase<TaxonBase>
  84               implements ITaxonDao {
  85 //    private AlternativeSpellingSuggestionParser<TaxonBase> alternativeSpellingSuggestionParser;
  86     private static final Logger logger = Logger.getLogger(TaxonDaoHibernateImpl.class);
  87
  88     public TaxonDaoHibernateImpl() {
  89         super(TaxonBase.class);
  90         indexedClasses = new Class[2];
  91         indexedClasses[0] = Taxon.class;
  92         indexedClasses[1] = Synonym.class;
  93         super.defaultField = "name.titleCache_tokenized";
  94     }
  95
  96     @Autowired
  97     private ITaxonNameDao taxonNameDao;
  98
  99 ////    spelling support currently disabled in appcontext, see spelling.xml ... "
 100 ////    @Autowired(required = false)   //TODO switched of because it caused problems when starting CdmApplicationController
 101 //    public void setAlternativeSpellingSuggestionParser(AlternativeSpellingSuggestionParser<TaxonBase> alternativeSpellingSuggestionParser) {
 102 //        this.alternativeSpellingSuggestionParser = alternativeSpellingSuggestionParser;
 103 //    }
 104
 105     @Override
 106     public TaxonBase load(UUID uuid, List<String> propertyPaths){
 107         return load(uuid, INCLUDE_UNPUBLISHED, propertyPaths);
 108     }
 109
 110     @Override
 111     public TaxonBase load(UUID uuid, boolean includeUnpublished, List<String> propertyPaths){
 112         TaxonBase<?> result = super.load(uuid, includeUnpublished, propertyPaths);
 113         return result; //(result == null || (!result.isPublish() && !includeUnpublished))? null : result;
 114     }
 115
 116
 117     @Override
 118     public List<TaxonBase> getTaxaByName(String queryString, boolean includeUnpublished, Reference sec) {
 119
 120         return getTaxaByName(queryString, true, includeUnpublished, sec);
 121     }
 122
 123     @Override
 124     public List<TaxonBase> getTaxaByName(String queryString, Boolean accepted, boolean includeUnpublished, Reference sec) {
 125         checkNotInPriorView("TaxonDaoHibernateImpl.getTaxaByName(String name, Reference sec)");
 126
 127         Criteria criteria = null;
 128         if (accepted == true) {
 129             criteria = getSession().createCriteria(Taxon.class);
 130         } else {
 131             criteria = getSession().createCriteria(Synonym.class);
 132         }
 133
 134         criteria.setFetchMode( "name", FetchMode.JOIN );
 135         criteria.createAlias("name", "name");
 136
 137         if (!includeUnpublished){
 138             criteria.add(Restrictions.eq("publish", Boolean.TRUE ));
 139         }
 140
 141         if (sec != null && sec.getId() != 0) {
 142             criteria.add(Restrictions.eq("sec", sec ) );
 143         }
 144
 145         if (queryString != null) {
 146             criteria.add(Restrictions.ilike("name.nameCache", queryString));
 147         }
 148
 149         @SuppressWarnings({ "unchecked", "rawtypes" })
 150         List<TaxonBase> result = criteria.list();
 151         return result;
 152     }
 153
 154     //TODO needed? Currently only used by tests.
 155     public List<TaxonBase> getTaxaByName(boolean doTaxa, boolean doSynonyms, boolean includeUnpublished,
 156             String queryString, MatchMode matchMode, Integer pageSize, Integer pageNumber) {
 157         return getTaxaByName(doTaxa, doSynonyms, false, false, false,
 158                 queryString, null, null, matchMode, null, includeUnpublished, null, pageSize, pageNumber, null);
 159     }
 160
 161     @Override
 162     public List<TaxonBase> getTaxaByName(String queryString, MatchMode matchMode,
 163             Boolean accepted, boolean includeUnpublished, Integer pageSize, Integer pageNumber) {
 164
 165         boolean doTaxa = true;
 166         boolean doSynonyms = true;
 167
 168         if (accepted == true) {
 169             doSynonyms = false;
 170         } else {
 171            doTaxa = false;
 172         }
 173         return getTaxaByName(doTaxa, doSynonyms, includeUnpublished, queryString, matchMode, pageSize, pageNumber);
 174     }
 175
 176     @Override
 177     public List<TaxonBase> getTaxaByName(boolean doTaxa, boolean doSynonyms, boolean doMisappliedNames, boolean doCommonNames,
 178             boolean includeAuthors,
 179             String queryString, Classification classification, TaxonNode subtree,
 180             MatchMode matchMode, Set<NamedArea> namedAreas, boolean includeUnpublished, NameSearchOrder order,
 181             Integer pageSize, Integer pageNumber, List<String> propertyPaths) {
 182
 183         boolean doCount = false;
 184
 185         String searchField = includeAuthors ? "titleCache" : "nameCache";
 186         Query query = prepareTaxaByName(doTaxa, doSynonyms, doMisappliedNames, doCommonNames, includeUnpublished, searchField, queryString, classification, subtree, matchMode, namedAreas, order, pageSize, pageNumber, doCount);
 187
 188         if (query != null){
 189             @SuppressWarnings({ "unchecked", "rawtypes" })
 190             List<TaxonBase> results = query.list();
 191
 192             defaultBeanInitializer.initializeAll(results, propertyPaths);
 193
 194             //Collections.sort(results, comp);
 195             return results;
 196         }else{
 197             return new ArrayList<>();
 198         }
 199     }
 200
 201     //new search for the editor, for performance issues the return values are only uuid and titleCache, to avoid the initialisation of all objects
 202     @Override
 203     @SuppressWarnings("unchecked")
 204     public List<UuidAndTitleCache<? extends IdentifiableEntity>> getTaxaByNameForEditor(boolean doTaxa, boolean doSynonyms, boolean doNamesWithoutTaxa,
 205             boolean doMisappliedNames, boolean doCommonNames, boolean includeUnpublished, String queryString, Classification classification, TaxonNode subtree,
 206             MatchMode matchMode, Set<NamedArea> namedAreas, NameSearchOrder order) {
 207
 208         if (order == null){
 209             order = NameSearchOrder.ALPHA;  //TODO add to signature
 210         }
 211
 212         boolean doCount = false;
 213         boolean includeAuthors = false;
 214         @SuppressWarnings("rawtypes")
 215         List<UuidAndTitleCache<? extends IdentifiableEntity>> resultObjects = new ArrayList<>();
 216         if (doNamesWithoutTaxa){
 217                 List<TaxonName> nameResult = taxonNameDao.findByName(
 218                         includeAuthors, queryString, matchMode, null, null, null, null);
 219
 220                 for (TaxonName name: nameResult){
 221                         if (name.getTaxonBases().size() == 0){
 222                                 resultObjects.add(new UuidAndTitleCache<>(TaxonName.class, name.getUuid(),
 223                                         name.getId(), name.getTitleCache()));
 224                         }
 225                 }
 226                 if (!doSynonyms && !doTaxa && !doCommonNames){
 227                         return resultObjects;
 228                 }
 229         }
 230         Query query = prepareTaxaByNameForEditor(doTaxa, doSynonyms, doMisappliedNames, doCommonNames, includeUnpublished,
 231                 "nameCache", queryString, classification, subtree, matchMode, namedAreas, doCount, order);
 232
 233         if (query != null){
 234             List<Object[]> results = query.list();
 235
 236             Object[] result;
 237             for(int i = 0; i<results.size();i++){
 238                 result = results.get(i);
 239
 240                 //differentiate taxa and synonyms
 241                 // new Boolean(result[3].toString()) is due to the fact that result[3] could be a Boolean ora String
 242                 // see FIXME in 'prepareQuery' for more details
 243                 if (doTaxa || doSynonyms || doCommonNames){
 244                     if (result[3].equals("synonym")) {
 245                         resultObjects.add( new UuidAndTitleCache<>(Synonym.class, (UUID) result[0], (Integer) result[1], (String)result[2], new Boolean(result[4].toString()), null));
 246                     }
 247                     else {
 248                         resultObjects.add( new UuidAndTitleCache<>(Taxon.class, (UUID) result[0], (Integer) result[1], (String)result[2], new Boolean(result[4].toString()), null));
 249                     }
 250
 251                 }else if (doSynonyms){
 252                     resultObjects.add( new UuidAndTitleCache<>(Synonym.class, (UUID) result[0], (Integer) result[1], (String)result[2], new Boolean(result[4].toString()), null));
 253                 }
 254             }
 255         }
 256         return resultObjects;
 257
 258     }
 259
 260     @Override
 261     public List<Taxon> getTaxaByCommonName(String queryString, Classification classification,
 262                MatchMode matchMode, Set<NamedArea> namedAreas, Integer pageSize,
 263                Integer pageNumber, List<String> propertyPaths) {
 264         boolean doCount = false;
 265         Query query = prepareTaxaByCommonName(queryString, classification, matchMode, namedAreas, pageSize, pageNumber, doCount, false);
 266         if (query != null){
 267             @SuppressWarnings("unchecked")
 268             List<Taxon> results = query.list();
 269             defaultBeanInitializer.initializeAll(results, propertyPaths);
 270             return results;
 271         }else{
 272             return new ArrayList<>();
 273         }
 274
 275     }
 276
 277     /**
 278      * @param clazz
 279      * @param searchField the field in TaxonName to be searched through usually either <code>nameCache</code> or <code>titleCache</code>
 280      * @param queryString
 281      * @param classification TODO
 282      * @param matchMode
 283      * @param namedAreas
 284      * @param pageSize
 285      * @param pageNumber
 286      * @param doCount
 287      * @return
 288      *
 289      *
 290      */
 291     private Query prepareTaxaByNameForEditor(boolean doTaxa, boolean doSynonyms, boolean doMisappliedNames, boolean doCommonNames,
 292             boolean includeUnpublished, String searchField, String queryString, Classification classification, TaxonNode subtree,
 293             MatchMode matchMode, Set<NamedArea> namedAreas, boolean doCount, NameSearchOrder order) {
 294         return prepareByNameQuery(doTaxa, doSynonyms, doMisappliedNames, doCommonNames, includeUnpublished,
 295                 searchField, queryString,
 296                 classification, subtree, matchMode, namedAreas, order, doCount, true);
 297     }
 298
 299
 300     /**
 301      * @param doTaxa
 302      * @param doSynonyms
 303      * @param doIncludeMisappliedNames
 304      * @param doCommonNames
 305      * @param includeUnpublished
 306      * @param searchField
 307      * @param queryString
 308      * @param classification
 309      * @param matchMode
 310      * @param namedAreas
 311      * @param order
 312      * @param doCount
 313      * @param returnIdAndTitle
 314      *            If set true the seach method will not return synonym and taxon
 315      *            entities but an array containing the uuid, titleCache, and the
 316      *            DTYPE in lowercase letters.
 317      * @return
 318      */
 319     private Query prepareByNameQuery(boolean doTaxa, boolean doSynonyms, boolean doMisappliedNames,
 320                 boolean doCommonNames, boolean includeUnpublished, String searchField, String queryString,
 321                 Classification classification, TaxonNode subtree, MatchMode matchMode, Set<NamedArea> namedAreas,
 322                 NameSearchOrder order, boolean doCount, boolean returnIdAndTitle){
 323
 324             boolean doProParteSynonyms = doSynonyms;  //we may distinguish in future
 325             boolean doConceptRelations = doMisappliedNames || doProParteSynonyms;
 326
 327             if (order == null){
 328                 order = NameSearchOrder.DEFAULT();
 329             }
 330             String hqlQueryString = matchMode.queryStringFrom(queryString);
 331             String selectWhat;
 332             if (returnIdAndTitle){
 333                 selectWhat = "t.uuid, t.id, t.titleCache ";
 334             }else {
 335                 selectWhat = (doCount ? "count(t)": "t");
 336             }
 337
 338             //area filter
 339             //TODO share code with taxon node filter
 340             String hql = "";
 341             Set<NamedArea> areasExpanded = new HashSet<>();
 342             if(namedAreas != null && namedAreas.size() > 0){
 343                 // expand areas and restrict by distribution area
 344                 Query areaQuery = getSession().createQuery("SELECT childArea "
 345                         + " FROM NamedArea AS childArea LEFT JOIN childArea.partOf as parentArea "
 346                         + " WHERE parentArea = :area");
 347                 expandNamedAreas(namedAreas, areasExpanded, areaQuery);
 348             }
 349             boolean doAreaRestriction = areasExpanded.size() > 0;
 350
 351             Set<UUID> namedAreasUuids = new HashSet<>();
 352             for (NamedArea area:areasExpanded){
 353                 namedAreasUuids.add(area.getUuid());
 354             }
 355
 356             Subselects subSelects = createByNameHQLString(doConceptRelations,
 357                     includeUnpublished, classification, subtree, areasExpanded, matchMode, searchField);
 358             String taxonSubselect = subSelects.taxonSubselect;
 359             String synonymSubselect = subSelects.synonymSubselect;
 360             String conceptSelect = subSelects.conceptSelect;
 361             String commonNameSubSelect = subSelects.commonNameSubselect;
 362
 363
 364             if (logger.isDebugEnabled()) {
 365                 logger.debug("taxonSubselect: " + (taxonSubselect != null ? taxonSubselect: "NULL"));
 366                 logger.debug("synonymSubselect: " + (synonymSubselect != null ? synonymSubselect: "NULL"));
 367             }
 368
 369             List<Integer> taxonIDs = new ArrayList<>();
 370             List<Integer> synonymIDs = new ArrayList<>();
 371
 372             if(doTaxa){
 373                 // find Taxa
 374                 Query subTaxon = getSearchQueryString(hqlQueryString, taxonSubselect);
 375
 376                 addRestrictions(doAreaRestriction, classification, subtree, includeUnpublished,
 377                         namedAreasUuids, subTaxon);
 378                 taxonIDs = subTaxon.list();
 379             }
 380
 381             if(doSynonyms){
 382                 // find synonyms
 383                 Query subSynonym = getSearchQueryString(hqlQueryString, synonymSubselect);
 384                 addRestrictions(doAreaRestriction, classification, subtree, includeUnpublished, namedAreasUuids,subSynonym);
 385                 synonymIDs = subSynonym.list();
 386             }
 387             if (doConceptRelations ){
 388                 Query subMisappliedNames = getSearchQueryString(hqlQueryString, conceptSelect);
 389                 Set<TaxonRelationshipType> relTypeSet = new HashSet<>();
 390                 if (doMisappliedNames){
 391                     relTypeSet.addAll(TaxonRelationshipType.allMisappliedNameTypes());
 392                 }
 393                 if (doProParteSynonyms){
 394                     relTypeSet.addAll(TaxonRelationshipType.allSynonymTypes());
 395                 }
 396                 subMisappliedNames.setParameterList("rTypeSet", relTypeSet);
 397                 addRestrictions(doAreaRestriction, classification, subtree, includeUnpublished, namedAreasUuids, subMisappliedNames);
 398                 taxonIDs.addAll(subMisappliedNames.list());
 399             }
 400
 401             if(doCommonNames){
 402                 // find Taxa
 403                 Query subCommonNames = getSearchQueryString(hqlQueryString, commonNameSubSelect);
 404                 addRestrictions(doAreaRestriction, classification, subtree, includeUnpublished, namedAreasUuids, subCommonNames);
 405                 taxonIDs.addAll(subCommonNames.list());
 406             }
 407
 408
 409             if(synonymIDs.size()>0 && taxonIDs.size()>0){
 410                 hql = "SELECT " + selectWhat;
 411                 // in doNotReturnFullEntities mode it is nesscary to also return the type of the matching entities:
 412                 // also return the computed isOrphaned flag
 413                 if (returnIdAndTitle &&  !doCount ){
 414                     hql += ", CASE WHEN t.id in (:taxa) THEN 'taxon' ELSE 'synonym' END, " +
 415                             " CASE WHEN t.id in (:taxa) "
 416                                     + " AND t.taxonNodes IS EMPTY "
 417                                     + " AND t.relationsFromThisTaxon IS EMPTY "
 418                                     + " AND t.relationsToThisTaxon IS EMPTY "
 419                                  + " THEN true ELSE false END ";
 420                 }
 421                 hql +=  " FROM %s t " +
 422                         " WHERE (t.id in (:taxa) OR t.id IN (:synonyms)) ";
 423             }else if (synonymIDs.size()>0 ){
 424                 hql = "SELECT " + selectWhat;
 425                 // in doNotReturnFullEntities mode it is nesscary to also return the type of the matching entities:
 426                 // also return the computed isOrphaned flag
 427                 if (returnIdAndTitle &&  !doCount ){
 428                     hql += ", 'synonym', 'false' ";
 429
 430                 }
 431                 hql +=  " FROM %s t " +
 432                         " WHERE t.id in (:synonyms) ";
 433
 434             } else if (taxonIDs.size()>0 ){
 435                 hql = "SELECT " + selectWhat;
 436                 // in doNotReturnFullEntities mode it is nesscary to also return the type of the matching entities:
 437                 // also return the computed isOrphaned flag
 438                 if (returnIdAndTitle &&  !doCount ){
 439                     hql += ", 'taxon', " +
 440                             " CASE WHEN t.taxonNodes is empty "
 441                             + "  AND t.relationsFromThisTaxon is empty "
 442                             + "  AND t.relationsToThisTaxon is empty "
 443                             + "THEN true ELSE false END ";
 444                 }
 445                 hql +=  " FROM %s t " +
 446                         " WHERE t.id in (:taxa) ";
 447             } else if (StringUtils.isBlank(queryString)){
 448                 hql = "SELECT " + selectWhat + " FROM %s t";
 449             } else{
 450                 return null;
 451             }
 452
 453             String classString;
 454             if ((doTaxa || doCommonNames || doConceptRelations) && doSynonyms){
 455                 classString = "TaxonBase";
 456             } else if (doTaxa || doCommonNames){
 457                 classString = "Taxon";
 458             } else if (doSynonyms && !(doCommonNames|| doTaxa || doConceptRelations)){
 459                 classString = "Synonym";  // as long as doProParteSynonyms = doSynonyms this case should not happen
 460             } else{//only misappliedNames
 461                 classString = "Taxon";
 462             }
 463
 464             hql = String.format(hql, classString);
 465
 466             if (hql == "") {
 467                 return null;
 468             }
 469             if(!doCount){
 470                 String orderBy = " ORDER BY ";
 471                 String alphabeticBase = " t.name.genusOrUninomial, case when t.name.specificEpithet like '\"%\"' then 1 else 0 end, t.name.specificEpithet, t.name.rank desc, t.name.nameCache";
 472
 473                 if (order == NameSearchOrder.LENGTH_ALPHA_NAME){
 474                     orderBy += " length(t.name.nameCache), " + alphabeticBase;
 475                 }else if (order == NameSearchOrder.LENGTH_ALPHA_TITLE){
 476                     orderBy += " length(t.name.titleCache), " + alphabeticBase;
 477                 }else {
 478                     orderBy += alphabeticBase;
 479                 }
 480
 481                 hql += orderBy;
 482             }
 483
 484             if(logger.isDebugEnabled()){ logger.debug("hql: " + hql);}
 485             Query query = getSession().createQuery(hql);
 486
 487             // find taxa and synonyms
 488             if (taxonIDs.size()>0){
 489                 query.setParameterList("taxa", taxonIDs);
 490             }
 491             if (synonymIDs.size()>0){
 492                 query.setParameterList("synonyms",synonymIDs);
 493             }
 494             if (taxonIDs.size()== 0 && synonymIDs.size() == 0){
 495                 return null;
 496             }
 497
 498             return query;
 499     }
 500
 501     /**
 502      * @param hqlQueryString
 503      * @param synonymSubselect
 504      * @return
 505      */
 506     protected Query getSearchQueryString(String hqlQueryString, String synonymSubselect) {
 507         return getSession().createQuery(synonymSubselect).setParameter("queryString", hqlQueryString);
 508     }
 509
 510     /**
 511      * @param includeUnpublished
 512      * @param classification
 513      * @param doAreaRestriction
 514      * @param subtree
 515      * @param namedAreasUuids
 516      * @param subTaxon
 517      */
 518     protected void addRestrictions(boolean doAreaRestriction, Classification classification, TaxonNode subtree, boolean includeUnpublished,
 519             Set<UUID> namedAreasUuids, Query query) {
 520         if(doAreaRestriction){
 521             query.setParameterList("namedAreasUuids", namedAreasUuids);
 522         }
 523         if(classification != null){
 524             query.setParameter("classification", classification);
 525         }
 526         if(subtree != null){
 527             query.setParameter("treeIndexLike", subtree.treeIndex() + "%");
 528         }
 529         if(!includeUnpublished){
 530             query.setBoolean("publish", true);
 531         }
 532     }
 533
 534
 535     /**
 536      * @param searchField the field in TaxonName to be searched through usually either <code>nameCache</code> or <code>titleCache</code>
 537      * @param queryString
 538      * @param classification TODO
 539      * @param matchMode
 540      * @param namedAreas
 541      * @param pageSize
 542      * @param pageNumber
 543      * @param doCount
 544      * @param clazz
 545      * @return
 546      *
 547      * FIXME implement classification restriction & implement test: see {@link TaxonDaoHibernateImplTest#testCountTaxaByName()}
 548      */
 549     private Query prepareTaxaByName(boolean doTaxa, boolean doSynonyms, boolean doMisappliedNames,
 550             boolean doCommonNames, boolean includeUnpublished, String searchField, String queryString,
 551             Classification classification, TaxonNode subtree, MatchMode matchMode, Set<NamedArea> namedAreas, NameSearchOrder order, Integer pageSize, Integer pageNumber, boolean doCount) {
 552
 553         Query query = prepareByNameQuery(doTaxa, doSynonyms, doMisappliedNames, doCommonNames, includeUnpublished,
 554                 searchField, queryString, classification, subtree, matchMode, namedAreas, order, doCount, false);
 555
 556         if(pageSize != null && !doCount && query != null) {
 557             query.setMaxResults(pageSize);
 558             if(pageNumber != null) {
 559                 query.setFirstResult(pageNumber * pageSize);
 560             }
 561         }
 562
 563         return query;
 564     }
 565
 566     private Query prepareTaxaByCommonName(String queryString, Classification classification,
 567             MatchMode matchMode, Set<NamedArea> namedAreas, Integer pageSize, Integer pageNumber,
 568             boolean doCount, boolean returnIdAndTitle){
 569
 570         String what = "select distinct";
 571         if (returnIdAndTitle){
 572                 what += " t.uuid, t.id, t.titleCache, \'taxon\', case when t.taxonNodes is empty and t.relationsFromThisTaxon is empty and t.relationsToThisTaxon is empty then true else false end ";
 573         }else {
 574                 what += (doCount ? " count(t)": " t");
 575         }
 576         String hql= what + " from Taxon t " +
 577         "join t.descriptions d "+
 578         "join d.descriptionElements e " +
 579         "join e.feature f " +
 580         "where f.supportsCommonTaxonName = true and e.name "+matchMode.getMatchOperator()+" :queryString";//and ls.text like 'common%'";
 581
 582         Query query = getSession().createQuery(hql);
 583
 584         query.setParameter("queryString", matchMode.queryStringFrom(queryString));
 585
 586         if(pageSize != null &&  !doCount) {
 587             query.setMaxResults(pageSize);
 588             if(pageNumber != null) {
 589                 query.setFirstResult(pageNumber * pageSize);
 590             }
 591         }
 592         return query;
 593     }
 594
 595     @Override
 596     public long countTaxaByName(boolean doTaxa, boolean doSynonyms, boolean doMisappliedNames, boolean doCommonNames,
 597             boolean doIncludeAuthors, String queryString, Classification classification, TaxonNode subtree,
 598         MatchMode matchMode, Set<NamedArea> namedAreas, boolean includeUnpublished) {
 599
 600         boolean doCount = true;
 601         /*
 602         boolean doTaxa = true;
 603         boolean doSynonyms = true;
 604         if (clazz.equals(Taxon.class)){
 605             doSynonyms = false;
 606         } else if (clazz.equals(Synonym.class)){
 607             doTaxa = false;
 608         }
 609         */
 610         String searchField = doIncludeAuthors ? "titleCache": "nameCache";
 611
 612         Query query = prepareTaxaByName(doTaxa, doSynonyms, doMisappliedNames, doCommonNames, includeUnpublished,
 613                 searchField, queryString, classification, subtree, matchMode, namedAreas, null, null, null, doCount);
 614         if (query != null) {
 615             return (Long)query.uniqueResult();
 616         }else{
 617             return 0;
 618         }
 619     }
 620
 621     /**
 622      * @param namedAreas
 623      * @param areasExpanded
 624      * @param areaQuery
 625      */
 626     private void expandNamedAreas(Collection<NamedArea> namedAreas, Set<NamedArea> areasExpanded, Query areaQuery) {
 627         List<NamedArea> childAreas;
 628         for(NamedArea a : namedAreas){
 629             areasExpanded.add(a);
 630             areaQuery.setParameter("area", a);
 631             childAreas = areaQuery.list();
 632             if(childAreas.size() > 0){
 633                 areasExpanded.addAll(childAreas);
 634                 expandNamedAreas(childAreas, areasExpanded, areaQuery);
 635             }
 636         }
 637     }
 638
 639
 640     @Override
 641     public UUID delete(TaxonBase taxonBase) throws DataAccessException{
 642         if (taxonBase == null){
 643             logger.warn("TaxonBase was 'null'");
 644             return null;
 645         }
 646
 647         // Merge the object in if it is detached
 648         //
 649         // I think this is preferable to catching lazy initialization errors
 650         // as that solution only swallows and hides the exception, but doesn't
 651         // actually solve it.
 652         taxonBase = (TaxonBase)getSession().merge(taxonBase);
 653
 654         taxonBase.removeSources();
 655
 656
 657         if (taxonBase instanceof Taxon){ // is Taxon
 658             Taxon taxon = ((Taxon)taxonBase);
 659             Set<Synonym> syns = new HashSet<>(taxon.getSynonyms());
 660             for (Synonym syn: syns){
 661                 taxon.removeSynonym(syn);
 662             }
 663         }
 664
 665        return super.delete(taxonBase);
 666
 667     }
 668
 669     @Override
 670     public List<TaxonBase> findByNameTitleCache(boolean doTaxa, boolean doSynonyms, boolean includeUnpublished, String queryString, Classification classification, TaxonNode subtree, MatchMode matchMode, Set<NamedArea> namedAreas, NameSearchOrder order, Integer pageNumber, Integer pageSize, List<String> propertyPaths) {
 671
 672         boolean doCount = false;
 673         Query query = prepareTaxaByName(doTaxa, doSynonyms, false, false, includeUnpublished, "titleCache", queryString, classification, subtree, matchMode, namedAreas, order, pageSize, pageNumber, doCount);
 674         if (query != null){
 675             @SuppressWarnings({ "unchecked", "rawtypes" })
 676             List<TaxonBase> results = query.list();
 677             defaultBeanInitializer.initializeAll(results, propertyPaths);
 678             return results;
 679         }
 680         return new ArrayList<>();
 681
 682     }
 683
 684     @Override
 685     public TaxonBase findByUuid(UUID uuid, List<Criterion> criteria, List<String> propertyPaths) {
 686
 687         Criteria crit = getSession().createCriteria(type);
 688
 689         if (uuid != null) {
 690             crit.add(Restrictions.eq("uuid", uuid));
 691         } else {
 692             logger.warn("UUID is NULL");
 693             return null;
 694         }
 695         if(criteria != null){
 696             for (Criterion criterion : criteria) {
 697                 crit.add(criterion);
 698             }
 699         }
 700         crit.addOrder(Order.asc("uuid"));
 701
 702         @SuppressWarnings({ "unchecked", "rawtypes" })
 703         List<? extends TaxonBase> results = crit.list();
 704         if (results.size() == 1) {
 705             defaultBeanInitializer.initializeAll(results, propertyPaths);
 706             TaxonBase<?> taxon = results.iterator().next();
 707             return taxon;
 708         } else if (results.size() > 1) {
 709             logger.error("Multiple results for UUID: " + uuid);
 710         } else if (results.size() == 0) {
 711             logger.info("No results for UUID: " + uuid);
 712         }
 713
 714         return null;
 715     }
 716
 717     @Override
 718     public List<? extends TaxonBase> findByUuids(List<UUID> uuids, List<Criterion> criteria, List<String> propertyPaths) {
 719
 720         Criteria crit = getSession().createCriteria(type);
 721
 722         if (uuids != null) {
 723             crit.add(Restrictions.in("uuid", uuids));
 724         } else {
 725             logger.warn("List<UUID> uuids is NULL");
 726             return null;
 727         }
 728         if(criteria != null){
 729             for (Criterion criterion : criteria) {
 730                 crit.add(criterion);
 731             }
 732         }
 733         crit.addOrder(Order.asc("uuid"));
 734
 735         @SuppressWarnings({ "unchecked", "rawtypes" })
 736         List<? extends TaxonBase> results = crit.list();
 737
 738         defaultBeanInitializer.initializeAll(results, propertyPaths);
 739         return results;
 740     }
 741
 742     @Override
 743     public long countMatchesByName(String queryString, MatchMode matchMode, boolean onlyAcccepted) {
 744         checkNotInPriorView("TaxonDaoHibernateImpl.countMatchesByName(String queryString, ITitledDao.MATCH_MODE matchMode, boolean onlyAcccepted)");
 745
 746         Criteria crit = getCriteria(type);
 747         crit.add(Restrictions.ilike("titleCache", matchMode.queryStringFrom(queryString)));
 748         crit.setProjection(Projections.rowCount());
 749         return (Long)crit.uniqueResult();
 750     }
 751
 752
 753     @Override
 754     public long countMatchesByName(String queryString, MatchMode matchMode, boolean onlyAcccepted, List<Criterion> criteria) {
 755         checkNotInPriorView("TaxonDaoHibernateImpl.countMatchesByName(String queryString, ITitledDao.MATCH_MODE matchMode, boolean onlyAcccepted, List<Criterion> criteria)");
 756
 757         Criteria crit = getCriteria(type);
 758         crit.add(Restrictions.ilike("titleCache", matchMode.queryStringFrom(queryString)));
 759         if(criteria != null){
 760             for (Criterion criterion : criteria) {
 761                 crit.add(criterion);
 762             }
 763         }
 764         crit.setProjection(Projections.rowCount());
 765         return (Long)crit.uniqueResult();
 766     }
 767
 768
 769     @Override
 770     public long countSynonyms(boolean onlyAttachedToTaxon) {
 771         AuditEvent auditEvent = getAuditEventFromContext();
 772         if(auditEvent.equals(AuditEvent.CURRENT_VIEW)) {
 773             Query query = null;
 774
 775             String queryStr =
 776                     " SELECT count(syn) "
 777                   + " FROM Synonym syn";
 778             if (onlyAttachedToTaxon){
 779                 queryStr += " WHERE syn.acceptedTaxon IS NOT NULL";
 780             }
 781             query = getSession().createQuery(queryStr);
 782
 783             return (Long)query.uniqueResult();
 784         } else {
 785             AuditQuery query = getAuditReader().createQuery().forEntitiesAtRevision(Synonym.class,auditEvent.getRevisionNumber());
 786             if (onlyAttachedToTaxon){
 787                 query.add(new NotNullAuditExpression(new EntityPropertyName("acceptedTaxon")));
 788             }
 789             query.addProjection(AuditEntity.id().count());
 790
 791             return (Long)query.getSingleResult();
 792         }
 793     }
 794
 795     @Override
 796     public long countSynonyms(Taxon taxon, SynonymType type) {
 797         AuditEvent auditEvent = getAuditEventFromContext();
 798         if(auditEvent.equals(AuditEvent.CURRENT_VIEW)) {
 799             Criteria criteria = getCriteria(Synonym.class);
 800
 801             criteria.add(Restrictions.eq("acceptedTaxon", taxon));
 802             if(type != null) {
 803                 criteria.add(Restrictions.eq("type", type));
 804             }
 805             criteria.setProjection(Projections.rowCount());
 806             return (Long)criteria.uniqueResult();
 807         } else {
 808             AuditQuery query = makeAuditQuery(Synonym.class,auditEvent);
 809             query.add(AuditEntity.relatedId("acceptedTaxon").eq(taxon.getId()));
 810             query.addProjection(AuditEntity.id().count());
 811
 812             if(type != null) {
 813                 query.add(AuditEntity.relatedId("type").eq(type.getId()));
 814             }
 815
 816             return (Long)query.getSingleResult();
 817         }
 818     }
 819
 820     @Override
 821     public long countSynonyms(Synonym synonym, SynonymType type) {
 822         AuditEvent auditEvent = getAuditEventFromContext();
 823         if(auditEvent.equals(AuditEvent.CURRENT_VIEW)) {
 824             Criteria criteria = getCriteria(Synonym.class);
 825
 826             criteria.add(Restrictions.isNotNull("acceptedTaxon"));
 827             if(type != null) {
 828                 criteria.add(Restrictions.eq("type", type));
 829             }
 830
 831             criteria.setProjection(Projections.rowCount());
 832             return (Long)criteria.uniqueResult();
 833         } else {
 834             AuditQuery query = makeAuditQuery(Synonym.class,auditEvent);
 835             query.add(new NotNullAuditExpression(new EntityPropertyName("acceptedTaxon")));
 836             query.addProjection(AuditEntity.id().count());
 837
 838             if(type != null) {
 839                 query.add(AuditEntity.relatedId("type").eq(type.getId()));
 840             }
 841
 842             return (Long)query.getSingleResult();
 843         }
 844     }
 845
 846     @Override
 847     public long countTaxaByName(Class<? extends TaxonBase> clazz, String genusOrUninomial, String infraGenericEpithet, String specificEpithet,  String infraSpecificEpithet, Rank rank) {
 848         checkNotInPriorView("TaxonDaoHibernateImpl.countTaxaByName(Boolean accepted, String genusOrUninomial,   String infraGenericEpithet, String specificEpithet,     String infraSpecificEpithet, Rank rank)");
 849         Criteria criteria = null;
 850
 851         criteria = getCriteria(clazz);
 852
 853         criteria.setFetchMode( "name", FetchMode.JOIN );
 854         criteria.createAlias("name", "name");
 855
 856         if(genusOrUninomial == null) {
 857             criteria.add(Restrictions.isNull("name.genusOrUninomial"));
 858         } else if(!genusOrUninomial.equals("*")) {
 859             criteria.add(Restrictions.eq("name.genusOrUninomial", genusOrUninomial));
 860         }
 861
 862         if(infraGenericEpithet == null) {
 863             criteria.add(Restrictions.isNull("name.infraGenericEpithet"));
 864         } else if(!infraGenericEpithet.equals("*")) {
 865             criteria.add(Restrictions.eq("name.infraGenericEpithet", infraGenericEpithet));
 866         }
 867
 868         if(specificEpithet == null) {
 869             criteria.add(Restrictions.isNull("name.specificEpithet"));
 870         } else if(!specificEpithet.equals("*")) {
 871             criteria.add(Restrictions.eq("name.specificEpithet", specificEpithet));
 872
 873         }
 874
 875         if(infraSpecificEpithet == null) {
 876             criteria.add(Restrictions.isNull("name.infraSpecificEpithet"));
 877         } else if(!infraSpecificEpithet.equals("*")) {
 878             criteria.add(Restrictions.eq("name.infraSpecificEpithet", infraSpecificEpithet));
 879         }
 880
 881         if(rank != null) {
 882             criteria.add(Restrictions.eq("name.rank", rank));
 883         }
 884
 885         criteria.setProjection(Projections.projectionList().add(Projections.rowCount()));
 886
 887         return (Long)criteria.uniqueResult();
 888     }
 889
 890     @Override
 891     public List<TaxonBase> findTaxaByName(Class<? extends TaxonBase> clazz, String genusOrUninomial, String infraGenericEpithet, String specificEpithet, String infraSpecificEpithet, String authorship, Rank rank, Integer pageSize,   Integer pageNumber) {
 892         checkNotInPriorView("TaxonDaoHibernateImpl.findTaxaByName(Boolean accepted, String genusOrUninomial, String infraGenericEpithet, String specificEpithet, String infraSpecificEpithet, String authorship, Rank rank, Integer pageSize,   Integer pageNumber)");
 893         Criteria criteria = getCriteria(clazz);
 894
 895         criteria.setFetchMode( "name", FetchMode.JOIN );
 896         criteria.createAlias("name", "name");
 897
 898         if(genusOrUninomial == null) {
 899             criteria.add(Restrictions.isNull("name.genusOrUninomial"));
 900         } else if(!genusOrUninomial.equals("*")) {
 901             criteria.add(Restrictions.eq("name.genusOrUninomial", genusOrUninomial));
 902         }
 903
 904         if(infraGenericEpithet == null) {
 905             criteria.add(Restrictions.isNull("name.infraGenericEpithet"));
 906         } else if(!infraGenericEpithet.equals("*")) {
 907             criteria.add(Restrictions.eq("name.infraGenericEpithet", infraGenericEpithet));
 908         }
 909
 910         if(specificEpithet == null) {
 911             criteria.add(Restrictions.isNull("name.specificEpithet"));
 912         } else if(!specificEpithet.equals("*")) {
 913             criteria.add(Restrictions.eq("name.specificEpithet", specificEpithet));
 914         }
 915
 916         if(infraSpecificEpithet == null) {
 917             criteria.add(Restrictions.isNull("name.infraSpecificEpithet"));
 918         } else if(!infraSpecificEpithet.equals("*")) {
 919             criteria.add(Restrictions.eq("name.infraSpecificEpithet", infraSpecificEpithet));
 920         }
 921
 922         if(authorship == null) {
 923             criteria.add(Restrictions.eq("name.authorshipCache", ""));
 924         } else if(!authorship.equals("*")) {
 925             criteria.add(Restrictions.eq("name.authorshipCache", authorship));
 926         }
 927
 928         if(rank != null) {
 929             criteria.add(Restrictions.eq("name.rank", rank));
 930         }
 931
 932         if(pageSize != null) {
 933             criteria.setMaxResults(pageSize);
 934             if(pageNumber != null) {
 935                 criteria.setFirstResult(pageNumber * pageSize);
 936             } else {
 937                 criteria.setFirstResult(0);
 938             }
 939         }
 940
 941         @SuppressWarnings({ "unchecked", "rawtypes" })
 942         List<TaxonBase> result = criteria.list();
 943         return result;
 944     }
 945
 946     @Override
 947     public long countTaxonRelationships(Taxon taxon, TaxonRelationshipType type,
 948             boolean includeUnpublished, Direction direction) {
 949         Set<TaxonRelationshipType> types = null;
 950         if (type != null){
 951             types = new HashSet<>();
 952             types.add(type);
 953         }
 954         return countTaxonRelationships(taxon, types, includeUnpublished, direction);
 955     }
 956
 957     @Override
 958     public long countTaxonRelationships(Taxon taxon, Set<TaxonRelationshipType> types,
 959             boolean includeUnpublished, Direction direction) {
 960         AuditEvent auditEvent = getAuditEventFromContext();
 961         if(auditEvent.equals(AuditEvent.CURRENT_VIEW)) {
 962
 963             String queryString = prepareTaxonRelationshipQuery(types, includeUnpublished, direction, true);
 964             Query query = getSession().createQuery(queryString);
 965             query.setParameter("relatedTaxon", taxon);
 966             if(types != null) {
 967                 query.setParameterList("types",types);
 968             }
 969             if(! includeUnpublished) {
 970                 query.setBoolean("publish",Boolean.TRUE);
 971             }
 972
 973
 974 //            if(type == null) {
 975 //                query = getSession().createQuery(
 976 //                        "select count(taxonRelationship) from TaxonRelationship taxonRelationship where taxonRelationship."+direction+" = :relatedTaxon");
 977 //            } else {
 978 //                query = getSession().createQuery("select count(taxonRelationship) from TaxonRelationship taxonRelationship where taxonRelationship."+direction+" = :relatedTaxon and taxonRelationship.type = :type");
 979 //                query.setParameter("type",type);
 980 //            }
 981 //            query.setParameter("relatedTaxon", taxon);
 982
 983             return (Long)query.uniqueResult();
 984         } else {
 985           //TODO unpublished
 986
 987             AuditQuery query = makeAuditQuery(TaxonRelationship.class, auditEvent);
 988             query.add(AuditEntity.relatedId(direction.toString()).eq(taxon.getId()));
 989             query.addProjection(AuditEntity.id().count());
 990
 991             if(types != null) {
 992                 //TODO adapt to new Set semantic, was single type before
 993 //                query.add(AuditEntity.relatedId("type").eq(type.getId()));
 994             }
 995
 996             return (Long)query.getSingleResult();
 997         }
 998     }
 999
1000
1001     /**
1002      * @param type
1003      * @param includeUnpublished
1004      * @param direction
1005      * @param b
1006      * @return
1007      */
1008     private String prepareTaxonRelationshipQuery(Set<TaxonRelationshipType> types, boolean includeUnpublished,
1009             Direction direction, boolean isCount) {
1010         String selectStr = isCount? " count(rel) as n ":" rel ";
1011         String result = "SELECT " + selectStr + " FROM TaxonRelationship rel ";
1012         if(direction != null){
1013             result += " WHERE rel."+direction+" = :relatedTaxon";
1014         } else {
1015             result += " WHERE (rel.relatedFrom = :relatedTaxon OR rel.relatedTo = :relatedTaxon )";
1016         }
1017         if (types != null){
1018             result += " AND rel.type IN (:types) ";
1019         }
1020         if(! includeUnpublished) {
1021             result += " AND rel."+direction.invers()+".publish = :publish";
1022         }
1023         return result;
1024     }
1025
1026     @Override
1027     public List<TaxonRelationship> getTaxonRelationships(Taxon taxon, TaxonRelationshipType type,
1028             boolean includeUnpublished, Integer pageSize, Integer pageNumber, List<OrderHint> orderHints,
1029             List<String> propertyPaths, Direction direction) {
1030         Set<TaxonRelationshipType> types = null;
1031         if (type != null){
1032             types = new HashSet<>();
1033             types.add(type);
1034         }
1035         return getTaxonRelationships(taxon, types, includeUnpublished, pageSize, pageNumber, orderHints, propertyPaths, direction);
1036     }
1037
1038     @Override
1039     public List<TaxonRelationship> getTaxonRelationships(Taxon taxon, Set<TaxonRelationshipType> types,
1040             boolean includeUnpublished, Integer pageSize, Integer pageNumber, List<OrderHint> orderHints,
1041             List<String> propertyPaths, Direction direction) {
1042
1043         AuditEvent auditEvent = getAuditEventFromContext();
1044         if(auditEvent.equals(AuditEvent.CURRENT_VIEW)) {
1045
1046             String queryString = prepareTaxonRelationshipQuery(types, includeUnpublished, direction, false);
1047
1048             queryString += orderByClause("rel", orderHints);
1049
1050             Query query = getSession().createQuery(queryString);
1051             query.setParameter("relatedTaxon", taxon);
1052             if(types != null) {
1053                 query.setParameterList("types",types);
1054             }
1055             if(! includeUnpublished) {
1056                 query.setBoolean("publish",Boolean.TRUE);
1057             }
1058             setPagingParameter(query, pageSize, pageNumber);
1059
1060 //            Criteria criteria = getSession().createCriteria(TaxonRelationship.class);
1061 //
1062 //            if(direction != null) {
1063 //                criteria.add(Restrictions.eq(direction.name(), taxon));
1064 //            } else {
1065 //                criteria.add(Restrictions.or(
1066 //                        Restrictions.eq(Direction.relatedFrom.name(), taxon),
1067 //                        Restrictions.eq(Direction.relatedTo.name(), taxon))
1068 //                    );
1069 //            }
1070 //
1071 //            if(type != null) {
1072 //                criteria.add(Restrictions.eq("type", type));
1073 //            }
1074 //
1075 //            addOrder(criteria,orderHints);
1076 //
1077 //            if(pageSize != null) {
1078 //                criteria.setMaxResults(pageSize);
1079 //                if(pageNumber != null) {
1080 //                    criteria.setFirstResult(pageNumber * pageSize);
1081 //                } else {
1082 //                    criteria.setFirstResult(0);
1083 //                }
1084 //            }
1085
1086             @SuppressWarnings("unchecked")
1087             List<TaxonRelationship> result = query.list();
1088             defaultBeanInitializer.initializeAll(result, propertyPaths);
1089
1090             return result;
1091         } else {
1092             //TODO unpublished
1093             AuditQuery query = getAuditReader().createQuery().forEntitiesAtRevision(TaxonRelationship.class,auditEvent.getRevisionNumber());
1094             query.add(AuditEntity.relatedId("relatedTo").eq(taxon.getId()));
1095
1096             if(types != null) {
1097                 //FIXME adapt to Set (was single type before)
1098 //                query.add(AuditEntity.relatedId("type").eq(types.getId()));
1099             }
1100
1101             if(pageSize != null) {
1102                 query.setMaxResults(pageSize);
1103                 if(pageNumber != null) {
1104                     query.setFirstResult(pageNumber * pageSize);
1105                 } else {
1106                     query.setFirstResult(0);
1107                 }
1108             }
1109
1110             @SuppressWarnings("unchecked")
1111             List<TaxonRelationship> result = query.getResultList();
1112             defaultBeanInitializer.initializeAll(result, propertyPaths);
1113
1114             // Ugly, but for now, there is no way to sort on a related entity property in Envers,
1115             // and we can't live without this functionality in CATE as it screws up the whole
1116             // taxon tree thing
1117             if(orderHints != null && !orderHints.isEmpty()) {
1118                 SortedSet<TaxonRelationship> sortedList = new TreeSet<>(new TaxonRelationshipFromTaxonComparator());
1119                 sortedList.addAll(result);
1120                 return new ArrayList<>(sortedList);
1121             }
1122
1123             return result;
1124         }
1125     }
1126
1127     class TaxonRelationshipFromTaxonComparator implements Comparator<TaxonRelationship> {
1128
1129         @Override
1130         public int compare(TaxonRelationship o1, TaxonRelationship o2) {
1131             if (o1.equals(o2)){
1132                 return 0;
1133             }
1134             int result = o1.getFromTaxon().getTitleCache().compareTo(o2.getFromTaxon().getTitleCache());
1135             if (result == 0 ){
1136                 result = o1.getUuid().compareTo(o2.getUuid());
1137             }
1138             return result;
1139         }
1140
1141     }
1142
1143     @Override
1144     public List<Synonym> getSynonyms(Taxon taxon, SynonymType type, Integer pageSize, Integer pageNumber, List<OrderHint> orderHints, List<String> propertyPaths) {
1145         AuditEvent auditEvent = getAuditEventFromContext();
1146         if(auditEvent.equals(AuditEvent.CURRENT_VIEW)) {
1147             Criteria criteria = getSession().createCriteria(Synonym.class);
1148
1149             criteria.add(Restrictions.eq("acceptedTaxon", taxon));
1150             if(type != null) {
1151                 criteria.add(Restrictions.eq("type", type));
1152             }
1153
1154             addOrder(criteria,orderHints);
1155
1156             if(pageSize != null) {
1157                 criteria.setMaxResults(pageSize);
1158                 if(pageNumber != null) {
1159                     criteria.setFirstResult(pageNumber * pageSize);
1160                 } else {
1161                     criteria.setFirstResult(0);
1162                 }
1163             }
1164
1165             @SuppressWarnings("unchecked")
1166             List<Synonym> result = criteria.list();
1167             defaultBeanInitializer.initializeAll(result, propertyPaths);
1168
1169             return result;
1170         } else {
1171             AuditQuery query = getAuditReader().createQuery().forEntitiesAtRevision(Synonym.class,auditEvent.getRevisionNumber());
1172             query.add(AuditEntity.relatedId("acceptedTaxon").eq(taxon.getId()));
1173
1174             if(type != null) {
1175                 query.add(AuditEntity.relatedId("type").eq(type.getId()));
1176             }
1177
1178             if(pageSize != null) {
1179                 query.setMaxResults(pageSize);
1180                 if(pageNumber != null) {
1181                     query.setFirstResult(pageNumber * pageSize);
1182                 } else {
1183                     query.setFirstResult(0);
1184                 }
1185             }
1186
1187             @SuppressWarnings("unchecked")
1188             List<Synonym> result = query.getResultList();
1189             defaultBeanInitializer.initializeAll(result, propertyPaths);
1190
1191             return result;
1192         }
1193     }
1194
1195     @Override
1196     public void rebuildIndex() {
1197         FullTextSession fullTextSession = Search.getFullTextSession(getSession());
1198
1199         for(TaxonBase<?> taxonBase : list(null,null)) { // re-index all taxon base
1200             Hibernate.initialize(taxonBase.getName());
1201             fullTextSession.index(taxonBase);
1202         }
1203         fullTextSession.flushToIndexes();
1204     }
1205
1206     @Override
1207     public String suggestQuery(String queryString) {
1208         throw new RuntimeException("Query suggestion currently not implemented in TaxonDaoHibernateImpl");
1209 //        checkNotInPriorView("TaxonDaoHibernateImpl.suggestQuery(String queryString)");
1210 //        String alternativeQueryString = null;
1211 //        if (alternativeSpellingSuggestionParser != null) {
1212 //            try {
1213 //
1214 //                alternativeSpellingSuggestionParser.parse(queryString);
1215 //                org.apache.lucene.search.Query alternativeQuery = alternativeSpellingSuggestionParser.suggest(queryString);
1216 //                if (alternativeQuery != null) {
1217 //                    alternativeQueryString = alternativeQuery
1218 //                            .toString("name.titleCache");
1219 //                }
1220 //
1221 //            } catch (ParseException e) {
1222 //                throw new QueryParseException(e, queryString);
1223 //            }
1224 //        }
1225 //        return alternativeQueryString;
1226     }
1227
1228     @Override
1229     public Taxon acceptedTaxonFor(Synonym synonym, Classification classificationFilter, List<String> propertyPaths){
1230
1231         String hql = prepareListAcceptedTaxaFor(classificationFilter, false);
1232
1233         Query query = getSession().createQuery(hql);
1234         query.setParameter("synonym", synonym);
1235         if(classificationFilter != null){
1236             query.setParameter("classificationFilter", classificationFilter);
1237         }
1238
1239         @SuppressWarnings("unchecked")
1240         List<Taxon> result = query.list();
1241
1242         defaultBeanInitializer.initializeAll(result, propertyPaths);
1243
1244         return result.isEmpty()? null: result.get(0);
1245     }
1246
1247     @Override
1248     public long countAcceptedTaxonFor(Synonym synonym, Classification classificationFilter){
1249
1250         String hql = prepareListAcceptedTaxaFor(classificationFilter, true);
1251
1252         Query query = getSession().createQuery(hql);
1253         query.setParameter("synonym", synonym);
1254         if(classificationFilter != null){
1255             query.setParameter("classificationFilter", classificationFilter);
1256         }
1257
1258         Long count = Long.parseLong(query.uniqueResult().toString());
1259         return count;
1260
1261     }
1262
1263
1264     /**
1265      * @param classificationFilter
1266      * @param orderHints
1267      * @return
1268      */
1269     private String prepareListAcceptedTaxaFor(Classification classificationFilter, boolean doCount) {
1270
1271         String hql;
1272         String hqlSelect = "SELECT " + (doCount? "COUNT(taxon)" : "taxon") +
1273                  " FROM Synonym as syn "
1274                  + "   JOIN syn.acceptedTaxon as taxon ";
1275         String hqlWhere = " WHERE syn = :synonym";
1276
1277         if(classificationFilter != null){
1278             hqlSelect += " JOIN taxon.taxonNodes AS taxonNode";
1279             hqlWhere  += " AND taxonNode.classification = :classificationFilter";
1280         }
1281         hql = hqlSelect + hqlWhere;
1282         return hql;
1283     }
1284
1285     @Override
1286     public TaxonBase find(LSID lsid) {
1287         TaxonBase<?> taxonBase = super.find(lsid);
1288         if(taxonBase != null) {
1289             List<String> propertyPaths = new ArrayList<>();
1290             propertyPaths.add("createdBy");
1291             propertyPaths.add("updatedBy");
1292             propertyPaths.add("name");
1293             propertyPaths.add("sec");
1294             propertyPaths.add("relationsToThisTaxon");
1295             propertyPaths.add("relationsToThisTaxon.fromTaxon");
1296             propertyPaths.add("relationsToThisTaxon.toTaxon");
1297             propertyPaths.add("relationsFromThisTaxon");
1298             propertyPaths.add("relationsFromThisTaxon.toTaxon");
1299             propertyPaths.add("relationsToThisTaxon.type");
1300             propertyPaths.add("synonyms");
1301             propertyPaths.add("synonyms.type");
1302             propertyPaths.add("descriptions");
1303
1304             defaultBeanInitializer.initialize(taxonBase, propertyPaths);
1305         }
1306         return taxonBase;
1307     }
1308
1309     @Override
1310     public List<String> taxaByNameNotInDB(List<String> taxonNames){
1311         //get all taxa, already in db
1312         Query query = getSession().createQuery(
1313                  " FROM TaxonName t "
1314                 +" WHERE t.nameCache IN (:taxonList)");
1315         query.setParameterList("taxonList", taxonNames);
1316         @SuppressWarnings("unchecked")
1317         List<TaxonName> taxaInDB = query.list();
1318         //compare the original list with the result of the query
1319         for (TaxonName taxonName: taxaInDB){
1320             String nameCache = taxonName.getNameCache();
1321             if (taxonNames.contains(nameCache)){
1322                 taxonNames.remove(nameCache);
1323             }
1324         }
1325
1326         return taxonNames;
1327     }
1328
1329     //TODO: mal nur mit UUID probieren (ohne fetch all properties), vielleicht geht das schneller?
1330     @Override
1331     public List<UUID> findIdenticalTaxonNameIds(List<String> propertyPaths){
1332         Query query=getSession().createQuery(
1333                    "SELECT tmb2 "
1334                 + " FROM ZoologicalName tmb, ZoologicalName tmb2 FETCH ALL properties "
1335                 + " WHERE tmb.id != tmb2.id AND tmb.nameCache = tmb2.nameCache");
1336         @SuppressWarnings("unchecked")
1337         List<UUID> zooNames = query.list();
1338
1339         return zooNames;
1340
1341     }
1342
1343     @Override
1344     public List<TaxonName> findIdenticalTaxonNames(List<String> propertyPaths) {
1345
1346         Query query=getSession().createQuery(
1347                   " SELECT tmb2 "
1348                 + " FROM ZoologicalName tmb, ZoologicalName tmb2 FETCH ALL properties "
1349                 + " WHERE tmb.id != tmb2.id AND tmb.nameCache = tmb2.nameCache");
1350
1351         @SuppressWarnings("unchecked")
1352         List<TaxonName> zooNames = query.list();
1353
1354         TaxonNameComparator taxComp = new TaxonNameComparator();
1355         Collections.sort(zooNames, taxComp);
1356
1357         for (TaxonName taxonName: zooNames){
1358             defaultBeanInitializer.initialize(taxonName, propertyPaths);
1359         }
1360
1361         return zooNames;
1362     }
1363
1364     @Override
1365     public List<TaxonName> findIdenticalNamesNew(List<String> propertyPaths){
1366
1367         //Hole die beiden Source_ids von "Fauna Europaea" und "Erms" und in sources der names darf jeweils nur das entgegengesetzte auftreten (i member of tmb.taxonBases)
1368         Query query = getSession().createQuery("SELECT id "
1369                 + "FROM Reference "
1370                 + " WHERE titleCache LIKE 'Fauna Europaea database'");
1371         @SuppressWarnings("unchecked")
1372         List<String> secRefFauna = query.list();
1373         query = getSession().createQuery("Select id from Reference where titleCache like 'ERMS'");
1374         @SuppressWarnings("unchecked")
1375         List<String> secRefErms = query.list();
1376         //Query query = getSession().createQuery("select tmb2.nameCache from ZoologicalName tmb, TaxonBase tb1, ZoologicalName tmb2, TaxonBase tb2 where tmb.id != tmb2.id and tb1.name = tmb and tb2.name = tmb2 and tmb.nameCache = tmb2.nameCache and tb1.sec != tb2.sec");
1377         //Get all names of fauna europaea
1378         query = getSession().createQuery("select zn.nameCache from ZoologicalName zn, TaxonBase tb where tb.name = zn and tb.sec.id = :secRefFauna");
1379         query.setParameter("secRefFauna", secRefFauna.get(0));
1380         @SuppressWarnings("unchecked")
1381         List<String> namesFauna= query.list();
1382
1383         //Get all names of erms
1384
1385         query = getSession().createQuery("select zn.nameCache from ZoologicalName zn, TaxonBase tb where tb.name = zn and tb.sec.id = :secRefErms");
1386         query.setParameter("secRefErms", secRefErms.get(0));
1387
1388         @SuppressWarnings("unchecked")
1389         List<String> namesErms = query.list();
1390         /*TaxonNameComparator comp = new TaxonNameComparator();
1391         Collections.sort(namesFauna);
1392         Collections.sort(namesErms);
1393         */
1394         List <String> identicalNames = new ArrayList<>();
1395
1396         for (String nameFauna: namesFauna){
1397             if (namesErms.contains(nameFauna)){
1398                 identicalNames.add(nameFauna);
1399             }
1400         }
1401
1402         query = getSession().createQuery("from ZoologicalName zn where zn.nameCache IN (:identicalNames)");
1403         query.setParameterList("identicalNames", identicalNames);
1404         List<TaxonName> result = query.list();
1405         TaxonName tempName = result.get(0);
1406
1407         Iterator<IdentifiableSource> sources = tempName.getSources().iterator();
1408
1409         TaxonNameComparator taxComp = new TaxonNameComparator();
1410         Collections.sort(result, taxComp);
1411         defaultBeanInitializer.initializeAll(result, propertyPaths);
1412         return result;
1413
1414     }
1415
1416 //
1417 //
1418 //    @Override
1419 //    public String getPhylumName(TaxonName name){
1420 //        List results = new ArrayList();
1421 //        try{
1422 //        Query query = getSession().createSQLQuery("select getPhylum("+ name.getId()+");");
1423 //        results = query.list();
1424 //        }catch(Exception e){
1425 //            System.err.println(name.getUuid());
1426 //            return null;
1427 //        }
1428 //        System.err.println("phylum of "+ name.getTitleCache() );
1429 //        return (String)results.get(0);
1430 //    }
1431
1432
1433     @Override
1434     public long countTaxaByCommonName(String searchString,
1435             Classification classification, MatchMode matchMode,
1436             Set<NamedArea> namedAreas) {
1437         boolean doCount = true;
1438         Query query = prepareTaxaByCommonName(searchString, classification, matchMode, namedAreas, null, null, doCount, false);
1439         if (query != null && !query.list().isEmpty()) {
1440             Object o = query.uniqueResult();
1441             if(o != null) {
1442                 return (Long)o;
1443             }
1444         }
1445         return 0;
1446     }
1447
1448     private Subselects createByNameHQLString(boolean doConceptRelations,
1449                 boolean includeUnpublished, Classification classification, TaxonNode subtree,
1450                 Set<NamedArea> areasExpanded, MatchMode matchMode, String searchField){
1451
1452
1453         boolean doAreaRestriction = areasExpanded.size() > 0;
1454         boolean hasTaxonNodeFilter = classification != null || subtree != null;
1455
1456         String doAreaRestrictionSubSelect =
1457                      " SELECT %s.id "
1458                    + " FROM Distribution e "
1459                    + "    JOIN e.inDescription d "
1460                    + "    JOIN d.taxon t " +
1461                 (hasTaxonNodeFilter ? " JOIN t.taxonNodes AS tn " : " ");
1462
1463         String doAreaRestrictionConceptRelationSubSelect =
1464                    "SELECT %s.id "
1465                    + " FROM Distribution e "
1466                    + "   JOIN e.inDescription d"
1467                    + "   JOIN d.taxon t";
1468
1469         String doTaxonSubSelect =
1470                      " SELECT %s.id "
1471                    + " FROM Taxon t " + (hasTaxonNodeFilter ? " "
1472                            + " JOIN t.taxonNodes AS tn " : " ");
1473
1474         String doTaxonMisappliedNameSubSelect =
1475                      " SELECT %s.id "
1476                    + " FROM Taxon t ";
1477
1478         String doTaxonNameJoin = " JOIN t.name n ";
1479
1480         String doSynonymNameJoin =
1481                    " JOIN t.synonyms s "
1482                  + " JOIN s.name sn";
1483
1484         String doConceptRelationJoin =
1485                    " LEFT JOIN t.relationsFromThisTaxon AS rft " +
1486                    " LEFT JOIN rft.relatedTo AS rt " +
1487                       (hasTaxonNodeFilter ? " LEFT JOIN rt.taxonNodes AS tn2 " : " ") +
1488                    " LEFT JOIN rt.name AS n2" +
1489                    " LEFT JOIN rft.type as rtype";
1490
1491         String doCommonNamesJoin =
1492                    " JOIN t.descriptions AS description "+
1493                    " LEFT JOIN description.descriptionElements AS com " +
1494                    " LEFT JOIN com.feature f ";
1495
1496
1497         String doTreeWhere = classification == null ? "" : " AND tn.classification = :classification";
1498         String doTreeForConceptRelationsWhere = classification == null ? "": " AND tn2.classification = :classification";
1499
1500         String doSubtreeWhere = subtree == null? "":" AND tn.treeIndex like :treeIndexLike";
1501         String doSubtreeForConceptRelationsWhere = subtree == null? "":" AND tn2.treeIndex like :treeIndexLike";
1502
1503         String doAreaRestrictionWhere =  " e.area.uuid in (:namedAreasUuids)";
1504         String doCommonNamesRestrictionWhere = " (f.supportsCommonTaxonName = true and com.name "+matchMode.getMatchOperator()+" :queryString )";
1505
1506         String doSearchFieldWhere = "%s." + searchField + " " + matchMode.getMatchOperator() + " :queryString";
1507
1508         String doRelationshipTypeComparison = " rtype in (:rTypeSet) ";
1509
1510         String taxonSubselect = null;
1511         String synonymSubselect = null;
1512         String conceptSelect = null;
1513         String commonNameSubselect = null;
1514
1515         if(hasTaxonNodeFilter){
1516             if (!doConceptRelations){
1517                 if(doAreaRestriction){
1518                     taxonSubselect = String.format(doAreaRestrictionSubSelect, "t") + doTaxonNameJoin +
1519                             " WHERE (1=1) AND " + doAreaRestrictionWhere +
1520                                 doTreeWhere + doSubtreeWhere +
1521                             "  AND " + String.format(doSearchFieldWhere, "n");
1522                     synonymSubselect = String.format(doAreaRestrictionSubSelect, "s") + doSynonymNameJoin +
1523                             " WHERE (1=1) AND " + doAreaRestrictionWhere +
1524                                 doTreeWhere + doSubtreeWhere +
1525                             "  AND " + String.format(doSearchFieldWhere, "sn");
1526                     commonNameSubselect =  String.format(doAreaRestrictionSubSelect, "t") + doCommonNamesJoin +
1527                             " WHERE (1=1) AND " +  doAreaRestrictionWhere +
1528                                  doTreeWhere + doSubtreeWhere +
1529                             "  AND " + String.format(doSearchFieldWhere, "n") +
1530                             "  AND " + doCommonNamesRestrictionWhere;
1531                 } else {//no area restriction
1532                     taxonSubselect = String.format(doTaxonSubSelect, "t" )+ doTaxonNameJoin +
1533                             " WHERE (1=1) " + doTreeWhere + doSubtreeWhere +
1534                             "  AND " + String.format(doSearchFieldWhere, "n");
1535                     synonymSubselect = String.format(doTaxonSubSelect, "s" ) + doSynonymNameJoin +
1536                             " WHERE (1=1) " + doTreeWhere + doSubtreeWhere +
1537                             "  AND " + String.format(doSearchFieldWhere, "sn");
1538                     commonNameSubselect =String.format(doTaxonSubSelect, "t" )+ doCommonNamesJoin +
1539                             " WHERE (1=1) " + doTreeWhere + doSubtreeWhere +
1540                             "  AND " + doCommonNamesRestrictionWhere;
1541                 }
1542             }else{ //concept relations included
1543                 if(doAreaRestriction){
1544                     conceptSelect = String.format(doAreaRestrictionConceptRelationSubSelect, "t") + doTaxonNameJoin + doConceptRelationJoin  +
1545                             " WHERE " + doAreaRestrictionWhere +
1546                             "  AND " + String.format(doSearchFieldWhere, "n") +
1547                                  doTreeForConceptRelationsWhere + doSubtreeForConceptRelationsWhere +
1548                             "  AND " + doRelationshipTypeComparison;
1549                     taxonSubselect = String.format(doAreaRestrictionSubSelect, "t") + doTaxonNameJoin +
1550                             " WHERE " + doAreaRestrictionWhere +
1551                             "  AND " + String.format(doSearchFieldWhere, "n") +
1552                                 doTreeWhere + doSubtreeWhere;
1553                     synonymSubselect = String.format(doAreaRestrictionSubSelect, "s") + doSynonymNameJoin +
1554                             " WHERE " + doAreaRestrictionWhere +
1555                                 doTreeWhere + doSubtreeWhere +
1556                             "  AND " + String.format(doSearchFieldWhere, "sn");
1557                     commonNameSubselect= String.format(doAreaRestrictionSubSelect, "t")+ doCommonNamesJoin +
1558                             " WHERE " + doAreaRestrictionWhere +
1559                                 doTreeWhere + doSubtreeWhere +
1560                             "  AND " + doCommonNamesRestrictionWhere;
1561                 } else {//no area restriction
1562                     conceptSelect = String.format(doTaxonMisappliedNameSubSelect, "t" ) + doTaxonNameJoin + doConceptRelationJoin +
1563                             " WHERE " + String.format(doSearchFieldWhere, "n") +
1564                                   doTreeForConceptRelationsWhere + doSubtreeForConceptRelationsWhere +
1565                             "  AND " + doRelationshipTypeComparison;
1566                     taxonSubselect = String.format(doTaxonSubSelect, "t" ) + doTaxonNameJoin +
1567                             " WHERE " +  String.format(doSearchFieldWhere, "n") +
1568                                  doTreeWhere + doSubtreeWhere;
1569                     synonymSubselect = String.format(doTaxonSubSelect, "s" ) + doSynonymNameJoin +
1570                             " WHERE (1=1) " + doTreeWhere + doSubtreeWhere +
1571                             "  AND " +  String.format(doSearchFieldWhere, "sn");
1572                     commonNameSubselect= String.format(doTaxonSubSelect, "t")+ doCommonNamesJoin +
1573                             " WHERE (1=1) " + doTreeWhere + doSubtreeWhere +
1574                             "  AND " + doCommonNamesRestrictionWhere;
1575                 }
1576             }
1577         } else { //classification = null && subtree = null
1578             if(doAreaRestriction){
1579                 conceptSelect = String.format(doAreaRestrictionConceptRelationSubSelect, "t") + doTaxonNameJoin + doConceptRelationJoin +
1580                         " WHERE " + doAreaRestrictionWhere +
1581                         "  AND " + String.format(doSearchFieldWhere, "n")+
1582                         "  AND " + doRelationshipTypeComparison;
1583                 taxonSubselect = String.format(doAreaRestrictionSubSelect, "t") + doTaxonNameJoin +
1584                         " WHERE " + doAreaRestrictionWhere +
1585                         "  AND " + String.format(doSearchFieldWhere, "n");
1586                 synonymSubselect = String.format(doAreaRestrictionSubSelect, "s") + doSynonymNameJoin +
1587                         " WHERE " + doAreaRestrictionWhere +
1588                         "  AND " + String.format(doSearchFieldWhere, "sn");
1589                 commonNameSubselect = String.format(doAreaRestrictionSubSelect, "t")+ doCommonNamesJoin +
1590                         " WHERE " + doAreaRestrictionWhere +
1591                         "  AND " + doCommonNamesRestrictionWhere;
1592             } else { //no area restriction
1593                 conceptSelect = String.format(doTaxonMisappliedNameSubSelect, "t" ) + doTaxonNameJoin + doConceptRelationJoin +
1594                         " WHERE " +  String.format(doSearchFieldWhere, "n") +
1595                         " AND " + doRelationshipTypeComparison;
1596                 taxonSubselect = String.format(doTaxonSubSelect, "t" ) + doTaxonNameJoin +
1597                         " WHERE " +  String.format(doSearchFieldWhere, "n");
1598                 synonymSubselect = String.format(doTaxonSubSelect, "s" ) + doSynonymNameJoin +
1599                         " WHERE " +  String.format(doSearchFieldWhere, "sn");
1600                 commonNameSubselect = String.format(doTaxonSubSelect, "t" ) +doCommonNamesJoin +
1601                         " WHERE "+  doCommonNamesRestrictionWhere;
1602             }
1603         }
1604
1605         if (!includeUnpublished){
1606             taxonSubselect   += " AND t.publish = :publish ";
1607             synonymSubselect += " AND s.publish = :publish AND t.publish = :publish ";
1608             commonNameSubselect += " AND t.publish = :publish ";
1609             conceptSelect += " AND t.publish = :publish AND rt.publish = :publish ";
1610         }
1611
1612         Subselects result = new Subselects(taxonSubselect, synonymSubselect, conceptSelect, commonNameSubselect);
1613         return result;
1614     }
1615
1616     private class Subselects{
1617         String taxonSubselect;
1618         String synonymSubselect;
1619         String conceptSelect;
1620         String commonNameSubselect;
1621         private Subselects(String taxonSubselect, String synonymSubselect, String conceptSelect,
1622                 String commonNameSubselect) {
1623             this.taxonSubselect = taxonSubselect;
1624             this.synonymSubselect = synonymSubselect;
1625             this.conceptSelect = conceptSelect;
1626             this.commonNameSubselect = commonNameSubselect;
1627         }
1628     }
1629
1630         @Override
1631         public List<UuidAndTitleCache<Taxon>> getTaxaByCommonNameForEditor(
1632                         String titleSearchStringSqlized, Classification classification,
1633                         MatchMode matchMode, Set<NamedArea> namedAreas) {
1634                 Query query = prepareTaxaByCommonName(titleSearchStringSqlized, classification, matchMode, namedAreas, null, null, false, true);
1635         if (query != null){
1636             @SuppressWarnings("unchecked")
1637             List<Object> resultArray = query.list();
1638             List<UuidAndTitleCache<Taxon>> returnResult = new ArrayList<>() ;
1639             Object[] result;
1640             for(int i = 0; i<resultArray.size();i++){
1641                 result = (Object[]) resultArray.get(i);
1642                 returnResult.add(new UuidAndTitleCache<Taxon>(Taxon.class, (UUID) result[0],(Integer)result[1], (String)result[2], new Boolean(result[4].toString()), null));
1643             }
1644             return returnResult;
1645         }else{
1646             return new ArrayList<>();
1647         }
1648         }
1649
1650
1651         /**
1652          * @param
1653          * @see eu.etaxonomy.cdm.persistence.dao.taxon.ITaxonDao#countByIdentifier(java.lang.Class, java.lang.String, eu.etaxonomy.cdm.model.common.DefinedTerm, eu.etaxonomy.cdm.model.taxon.TaxonNode, eu.etaxonomy.cdm.persistence.query.MatchMode)
1654          */
1655         @Override
1656         public <S extends TaxonBase> long countByIdentifier(Class<S> clazz,
1657                         String identifier, DefinedTerm identifierType, TaxonNode subtreeFilter, MatchMode matchmode) {
1658                 if (subtreeFilter == null){
1659                         return countByIdentifier(clazz, identifier, identifierType, matchmode);
1660                 }
1661
1662                 Class<?> clazzParam = (clazz == null) ? type : clazz;
1663                 checkNotInPriorView("TaxonDaoHibernateImpl.countByIdentifier(T clazz, String identifier, DefinedTerm identifierType, TaxonNode subMatchMode matchmode)");
1664
1665                 boolean isTaxon = clazzParam == Taxon.class || clazzParam == TaxonBase.class;
1666                 boolean isSynonym = clazzParam == Synonym.class || clazzParam == TaxonBase.class;
1667
1668                 getSession().update(subtreeFilter);  //to avoid LIE when retrieving treeindex
1669                 String filterStr = "'" + subtreeFilter.treeIndex() + "%%'";
1670                 String accTreeJoin = isTaxon? " LEFT JOIN c.taxonNodes tn  " : "";
1671                 String synTreeJoin = isSynonym ? " LEFT JOIN c.acceptedTaxon as acc LEFT JOIN acc.taxonNodes synTn  " : "";
1672                 String accWhere = isTaxon ?  "tn.treeIndex like " + filterStr : "(1=0)";
1673                 String synWhere = isSynonym  ?  "synTn.treeIndex like " + filterStr : "(1=0)";
1674
1675                 String queryString = "SELECT count(*)  FROM %s as c " +
1676                 " INNER JOIN c.identifiers as ids " +
1677                 accTreeJoin +
1678                 synTreeJoin +
1679                 " WHERE (1=1) " +
1680                         "  AND ( " + accWhere + " OR " + synWhere + ")";
1681                 queryString = String.format(queryString, clazzParam.getSimpleName());
1682
1683                 if (identifier != null){
1684                         if (matchmode == null || matchmode == MatchMode.EXACT){
1685                                 queryString += " AND ids.identifier = '"  + identifier + "'";
1686                         }else {
1687                                 queryString += " AND ids.identifier LIKE '" + matchmode.queryStringFrom(identifier)  + "'";
1688                         }
1689                 }
1690                 if (identifierType != null){
1691                 queryString += " AND ids.type = :type";
1692         }
1693
1694                 Query query = getSession().createQuery(queryString);
1695         if (identifierType != null){
1696                 query.setEntity("type", identifierType);
1697         }
1698
1699                 return (Long)query.uniqueResult();
1700         }
1701
1702         @Override
1703         public <S extends TaxonBase> List<Object[]> findByIdentifier(
1704                         Class<S> clazz, String identifier, DefinedTerm identifierType, TaxonNode subtreeFilter,
1705                         MatchMode matchmode, boolean includeEntity,
1706                         Integer pageSize, Integer pageNumber, List<String> propertyPaths) {
1707
1708                 checkNotInPriorView("TaxonDaoHibernateImpl.findByIdentifier(T clazz, String identifier, DefinedTerm identifierType, MatchMode matchmode, Integer pageSize, Integer pageNumber, List<OrderHint> orderHints, List<String> propertyPaths)");
1709                 Class<?> clazzParam = clazz == null ? type : clazz;
1710
1711                 boolean isTaxon = clazzParam == Taxon.class || clazzParam == TaxonBase.class;
1712                 boolean isSynonym = clazzParam == Synonym.class || clazzParam == TaxonBase.class;
1713                 getSession().update(subtreeFilter);  //to avoid LIE when retrieving treeindex
1714                 String filterStr = "'" + subtreeFilter.treeIndex() + "%%'";
1715                 String accTreeJoin = isTaxon? " LEFT JOIN c.taxonNodes tn  " : "";
1716                 String synTreeJoin = isSynonym ? " LEFT JOIN c.acceptedTaxon as acc LEFT JOIN acc.taxonNodes synTn  " : "";
1717                 String accWhere = isTaxon ?  "tn.treeIndex like " + filterStr : "(1=0)";
1718                 String synWhere = isSynonym  ?  "synTn.treeIndex like " + filterStr : "(1=0)";
1719
1720                 String queryString = " SELECT ids.type, ids.identifier, %s " +
1721                                 " FROM %s as c " +
1722                 " INNER JOIN c.identifiers as ids " +
1723                 accTreeJoin +
1724                                 synTreeJoin +
1725                 " WHERE (1=1) " +
1726                         " AND ( " + accWhere + " OR " + synWhere + ")";
1727                 queryString = String.format(queryString, (includeEntity ? "c":"c.uuid, c.titleCache") , clazzParam.getSimpleName());
1728
1729                 //Matchmode and identifier
1730                 if (identifier != null){
1731                         if (matchmode == null || matchmode == MatchMode.EXACT){
1732                                 queryString += " AND ids.identifier = '"  + identifier + "'";
1733                         }else {
1734                                 queryString += " AND ids.identifier LIKE '" + matchmode.queryStringFrom(identifier)  + "'";
1735                         }
1736                 }
1737         if (identifierType != null){
1738                 queryString += " AND ids.type = :type";
1739         }
1740         //order
1741         queryString +=" ORDER BY ids.type.uuid, ids.identifier, c.uuid ";
1742
1743                 Query query = getSession().createQuery(queryString);
1744
1745                 //parameters
1746                 if (identifierType != null){
1747                 query.setEntity("type", identifierType);
1748         }
1749
1750         //paging
1751         setPagingParameter(query, pageSize, pageNumber);
1752
1753         List<Object[]> results = query.list();
1754         //initialize
1755         if (includeEntity){
1756                 List<S> entities = new ArrayList<S>();
1757                 for (Object[] result : results){
1758                         entities.add((S)result[2]);
1759                 }
1760                 defaultBeanInitializer.initializeAll(entities, propertyPaths);
1761         }
1762         return results;
1763         }
1764
1765     /**
1766      * {@inheritDoc}
1767      * @see #countByIdentifier(Class, String, DefinedTerm, TaxonNode, MatchMode)
1768      */
1769     @Override
1770     public <S extends TaxonBase> long countByMarker(Class<S> clazz, MarkerType markerType,
1771             Boolean markerValue, TaxonNode subtreeFilter) {
1772         if (markerType == null){
1773             return 0;
1774         }
1775
1776         if (subtreeFilter == null){
1777             return countByMarker(clazz, markerType, markerValue);
1778         }
1779
1780         Class<?> clazzParam = clazz == null ? type : clazz;
1781         checkNotInPriorView("TaxonDaoHibernateImpl.countByMarker(Class<S> clazz, DefinedTerm markerType, boolean markerValue, TaxonNode subtreeFilter)");
1782
1783         boolean isTaxon = clazzParam == Taxon.class || clazzParam == TaxonBase.class;
1784         boolean isSynonym = clazzParam == Synonym.class || clazzParam == TaxonBase.class;
1785
1786         getSession().update(subtreeFilter);  //to avoid LIE when retrieving treeindex
1787         String filterStr = "'" + subtreeFilter.treeIndex() + "%%'";
1788         String accTreeJoin = isTaxon? " LEFT JOIN c.taxonNodes tn  " : "";
1789         String synTreeJoin = isSynonym ? " LEFT JOIN c.acceptedTaxon acc LEFT JOIN acc.taxonNodes synTn  " : "";
1790         String accWhere = isTaxon ?  "tn.treeIndex like " + filterStr : "(1=0)";
1791         String synWhere = isSynonym  ?  "synTn.treeIndex like " + filterStr : "(1=0)";
1792
1793         String queryString = "SELECT count(*)  FROM %s as c " +
1794                 " INNER JOIN c.markers as mks " +
1795                 accTreeJoin +
1796                 synTreeJoin +
1797                 " WHERE (1=1) " +
1798                     "  AND ( " + accWhere + " OR " + synWhere + ")";
1799         queryString = String.format(queryString, clazzParam.getSimpleName());
1800
1801         if (markerValue != null){
1802             queryString += " AND mks.flag = :flag";
1803         }
1804         if (markerType != null){
1805             queryString += " AND mks.markerType = :type";
1806         }
1807
1808         Query query = getSession().createQuery(queryString);
1809         if (markerType != null){
1810             query.setEntity("type", markerType);
1811         }
1812         if (markerValue != null){
1813             query.setBoolean("flag", markerValue);
1814         }
1815
1816         Long c = (Long)query.uniqueResult();
1817         return c;
1818     }
1819
1820     /**
1821      * {@inheritDoc}
1822      */
1823     @Override
1824     public <S extends TaxonBase> List<Object[]> findByMarker(Class<S> clazz, MarkerType markerType,
1825             Boolean markerValue, TaxonNode subtreeFilter, boolean includeEntity,
1826             TaxonTitleType titleType, Integer pageSize, Integer pageNumber, List<String> propertyPaths) {
1827         checkNotInPriorView("TaxonDaoHibernateImpl.findByMarker(T clazz, String identifier, DefinedTerm identifierType, MatchMode matchmode, Integer pageSize, Integer pageNumber, List<OrderHint> orderHints, List<String> propertyPaths)");
1828         if (markerType == null){
1829             return new ArrayList<Object[]>();
1830         }
1831         if (titleType == null){
1832             titleType = TaxonTitleType.DEFAULT();
1833         }
1834
1835         Class<?> clazzParam = clazz == null ? type : clazz;
1836
1837         boolean isTaxon = clazzParam == Taxon.class || clazzParam == TaxonBase.class;
1838         boolean isSynonym = clazzParam == Synonym.class || clazzParam == TaxonBase.class;
1839         getSession().update(subtreeFilter);  //to avoid LIE when retrieving treeindex
1840         String filterStr = "'" + subtreeFilter.treeIndex() + "%%'";
1841         String accTreeJoin = isTaxon? " LEFT JOIN c.taxonNodes tn  " : "";
1842         String synTreeJoin = isSynonym ? " LEFT JOIN c.acceptedTaxon as acc LEFT JOIN acc.taxonNodes synTn  " : "";
1843         String accWhere = isTaxon ?  "tn.treeIndex like " + filterStr : "(1=0)";
1844         String synWhere = isSynonym  ?  "synTn.treeIndex like " + filterStr : "(1=0)";
1845         String selectParams = includeEntity ? "c" : titleType.hqlReplaceSelect("c.uuid, c.titleCache", "c.titleCache");
1846         String titleTypeJoin = includeEntity ? "" : titleType.hqlJoin();
1847
1848         String queryString = "SELECT mks.markerType, mks.flag, %s " +
1849                 " FROM %s as c " +
1850                 " INNER JOIN c.markers as mks " +
1851                 titleTypeJoin +
1852                 accTreeJoin +
1853                 synTreeJoin +
1854                 " WHERE (1=1) " +
1855                     " AND ( " + accWhere + " OR " + synWhere + ")";
1856         queryString = String.format(queryString, selectParams, clazzParam.getSimpleName());
1857
1858         //type and value
1859         if (markerValue != null){
1860             queryString += " AND mks.flag = :flag";
1861         }
1862         queryString += " AND mks.markerType = :type";
1863         //order
1864         queryString +=" ORDER BY mks.markerType.uuid, mks.flag, c.uuid ";
1865
1866         Query query = getSession().createQuery(queryString);
1867
1868         //parameters
1869         query.setEntity("type", markerType);
1870         if (markerValue != null){
1871             query.setBoolean("flag", markerValue);
1872         }
1873
1874         //paging
1875         setPagingParameter(query, pageSize, pageNumber);
1876
1877         List<Object[]> results = query.list();
1878         //initialize
1879         if (includeEntity){
1880             List<S> entities = new ArrayList<S>();
1881             for (Object[] result : results){
1882                 entities.add((S)result[2]);
1883             }
1884             defaultBeanInitializer.initializeAll(entities, propertyPaths);
1885         }
1886         return results;
1887     }
1888
1889     @Override
1890     public long countTaxonRelationships(Set<TaxonRelationshipType> types) {
1891         Criteria criteria = getSession().createCriteria(TaxonRelationship.class);
1892
1893         if (types != null) {
1894             if (types.isEmpty()){
1895                 return 0l;
1896             }else{
1897                 criteria.add(Restrictions.in("type", types) );
1898             }
1899         }
1900         //count
1901         criteria.setProjection(Projections.rowCount());
1902         long result = (Long)criteria.uniqueResult();
1903
1904         return result;
1905     }
1906
1907     @Override
1908     public List<TaxonRelationship> getTaxonRelationships(Set<TaxonRelationshipType> types,
1909             Integer pageSize, Integer pageNumber,
1910             List<OrderHint> orderHints, List<String> propertyPaths) {
1911         Criteria criteria = getCriteria(TaxonRelationship.class);
1912
1913         if (types != null) {
1914             if (types.isEmpty()){
1915                 return new ArrayList<>();
1916             }else{
1917                 criteria.add(Restrictions.in("type", types) );
1918             }
1919         }
1920         addOrder(criteria,orderHints);
1921         addPageSizeAndNumber(criteria, pageSize, pageNumber);
1922
1923         @SuppressWarnings("unchecked")
1924         List<TaxonRelationship> results = criteria.list();
1925         defaultBeanInitializer.initializeAll(results, propertyPaths);
1926
1927         return results;
1928     }
1929
1930     @Override
1931     public  List<UuidAndTitleCache<TaxonBase>> getUuidAndTitleCache(Integer limit, String pattern){
1932         Session session = getSession();
1933         Query query = null;
1934         if (pattern != null){
1935             query = session.createQuery(
1936                   " SELECT tb.uuid, tb.id, tb.titleCache, tb.name.rank "
1937                   + " FROM TaxonBase as tb "
1938                   + " WHERE tb.titleCache LIKE :pattern");
1939             pattern = pattern.replace("*", "%");
1940             pattern = pattern.replace("?", "_");
1941             pattern = pattern + "%";
1942             query.setParameter("pattern", pattern);
1943         } else {
1944             query = session.createQuery(
1945                     " SELECT tb.uuid, taxonBase.id, tb.titleCache, tb.name.rank "
1946                   + " FROM TaxonBase AS tb");
1947         }
1948         if (limit != null){
1949            query.setMaxResults(limit);
1950         }
1951
1952         return getUuidAndTitleCache(query);
1953     }
1954
1955     @Override
1956     protected List<UuidAndTitleCache<TaxonBase>> getUuidAndTitleCache(Query query){
1957         List<UuidAndTitleCache<TaxonBase>> list = new ArrayList<>();
1958
1959         @SuppressWarnings("unchecked")
1960         List<Object[]> result = query.list();
1961         if (!result.isEmpty()){
1962             if (result.iterator().next().length == 4){
1963                 Collections.sort(result, new UuidAndTitleCacheTaxonComparator());
1964             }
1965         }
1966
1967         for(Object[] object : result){
1968             list.add(new UuidAndTitleCache<TaxonBase>((UUID) object[0],(Integer) object[1], (String) object[2]));
1969         }
1970         return list;
1971     }
1972
1973
1974 }