cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/persistence/dao/hibernate/taxon/TaxonNodeDaoHibernateImpl.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
  10 package eu.etaxonomy.cdm.persistence.dao.hibernate.taxon;
  11
  12 import java.math.BigInteger;
  13 import java.util.ArrayList;
  14 import java.util.Collection;
  15 import java.util.Collections;
  16 import java.util.HashMap;
  17 import java.util.HashSet;
  18 import java.util.Iterator;
  19 import java.util.List;
  20 import java.util.Map;
  21 import java.util.Set;
  22 import java.util.UUID;
  23
  24 import org.apache.log4j.Logger;
  25 import org.hibernate.Criteria;
  26 import org.hibernate.Hibernate;
  27 import org.hibernate.Query;
  28 import org.hibernate.criterion.ProjectionList;
  29 import org.hibernate.criterion.Projections;
  30 import org.hibernate.criterion.Restrictions;
  31 import org.springframework.beans.factory.annotation.Autowired;
  32 import org.springframework.beans.factory.annotation.Qualifier;
  33 import org.springframework.stereotype.Repository;
  34
  35 import eu.etaxonomy.cdm.common.CdmUtils;
  36 import eu.etaxonomy.cdm.common.monitor.IProgressMonitor;
  37 import eu.etaxonomy.cdm.hibernate.HibernateProxyHelper;
  38 import eu.etaxonomy.cdm.model.common.CdmBase;
  39 import eu.etaxonomy.cdm.model.common.TreeIndex;
  40 import eu.etaxonomy.cdm.model.name.Rank;
  41 import eu.etaxonomy.cdm.model.name.TaxonName;
  42 import eu.etaxonomy.cdm.model.reference.Reference;
  43 import eu.etaxonomy.cdm.model.taxon.Classification;
  44 import eu.etaxonomy.cdm.model.taxon.Synonym;
  45 import eu.etaxonomy.cdm.model.taxon.Taxon;
  46 import eu.etaxonomy.cdm.model.taxon.TaxonBase;
  47 import eu.etaxonomy.cdm.model.taxon.TaxonNode;
  48 import eu.etaxonomy.cdm.model.taxon.TaxonNodeAgentRelation;
  49 import eu.etaxonomy.cdm.model.taxon.UuidAndTitleCacheTaxonComparator;
  50 import eu.etaxonomy.cdm.persistence.dao.common.Restriction;
  51 import eu.etaxonomy.cdm.persistence.dao.hibernate.common.AnnotatableDaoImpl;
  52 import eu.etaxonomy.cdm.persistence.dao.taxon.IClassificationDao;
  53 import eu.etaxonomy.cdm.persistence.dao.taxon.ITaxonDao;
  54 import eu.etaxonomy.cdm.persistence.dao.taxon.ITaxonNodeDao;
  55 import eu.etaxonomy.cdm.persistence.dto.TaxonNodeDto;
  56 import eu.etaxonomy.cdm.persistence.dto.UuidAndTitleCache;
  57 import eu.etaxonomy.cdm.persistence.query.OrderHint;
  58
  59 /**
  60  * @author a.mueller
  61  * @since 16.06.2009
  62  */
  63 @Repository
  64 @Qualifier("taxonNodeDaoHibernateImpl")
  65 public class TaxonNodeDaoHibernateImpl extends AnnotatableDaoImpl<TaxonNode>
  66                 implements ITaxonNodeDao {
  67         @SuppressWarnings("unused")
  68         private static final Logger logger = Logger.getLogger(TaxonNodeDaoHibernateImpl.class);
  69
  70         @Autowired
  71         private ITaxonDao taxonDao;
  72         @Autowired
  73         private IClassificationDao classificationDao;
  74
  75         public TaxonNodeDaoHibernateImpl() {
  76                 super(TaxonNode.class);
  77         }
  78         @Override
  79         public UUID delete(TaxonNode persistentObject, boolean deleteChildren){
  80                 Taxon taxon = persistentObject.getTaxon();
  81                 taxon = HibernateProxyHelper.deproxy(taxon);
  82
  83                 /*Session session = this.getSession();
  84                 Query query = session.createQuery("from TaxonNode t where t.taxon = :taxon");
  85                 query.setParameter("taxon", taxon);
  86                 List result = query.list();*/
  87                 if (taxon != null){
  88                     Hibernate.initialize(taxon);
  89                     Hibernate.initialize(taxon.getTaxonNodes());
  90                         Set<TaxonNode> nodes = taxon.getTaxonNodes();
  91                         //Hibernate.initialize(taxon.getTaxonNodes());
  92                         for (TaxonNode node:nodes) {
  93                             node = HibernateProxyHelper.deproxy(node);
  94
  95                             if (node.equals(persistentObject)){
  96                                 if (node.hasChildNodes()){
  97                                     Iterator<TaxonNode> childNodes = node.getChildNodes().iterator();
  98                                     TaxonNode childNode;
  99                                     List<TaxonNode> listForDeletion = new ArrayList<>();
 100                             while (childNodes.hasNext()){
 101                                 childNode = childNodes.next();
 102                                 listForDeletion.add(childNode);
 103                                 childNodes.remove();
 104
 105                             }
 106                             for (TaxonNode deleteNode:listForDeletion){
 107                                 delete(deleteNode, deleteChildren);
 108                             }
 109                         }
 110
 111                                 taxon.removeTaxonNode(node, deleteChildren);
 112                                 taxonDao.saveOrUpdate(taxon);
 113                                 taxon = HibernateProxyHelper.deproxy(taxonDao.findByUuid(taxon.getUuid()), Taxon.class);
 114                                 taxonDao.delete(taxon);
 115
 116                             }
 117                         }
 118                 }
 119
 120                 UUID result = super.delete(persistentObject);
 121
 122                 return result;
 123         }
 124
 125         @Override
 126         public List<TaxonNode> getTaxonOfAcceptedTaxaByClassification(Classification classification, Integer start, Integer end) {
 127                 int classificationId = classification.getId();
 128                 String limit = "";
 129                 if(start !=null && end != null){
 130                     limit = "LIMIT "+start+"," +end;
 131                 }
 132                 //FIXME write test
 133         String queryString = "SELECT DISTINCT nodes.*,taxa.titleCache "
 134                 + " FROM TaxonNode AS nodes "
 135                 + "    LEFT JOIN TaxonBase AS taxa ON nodes.taxon_id = taxa.id "
 136                 + " WHERE taxa.DTYPE = 'Taxon' "
 137                 + "    AND nodes.classification_id = " + classificationId +
 138                   " ORDER BY taxa.titleCache " + limit;
 139         @SuppressWarnings("unchecked")
 140         List<TaxonNode> result  = getSession().createSQLQuery(queryString).addEntity(TaxonNode.class).list();
 141
 142        return result;
 143         }
 144
 145     @Override
 146     public int countTaxonOfAcceptedTaxaByClassification(Classification classification){
 147         int classificationId = classification.getId();
 148         //FIXME write test
 149         String queryString = ""
 150                 + " SELECT DISTINCT COUNT('nodes.*') "
 151                 + " FROM TaxonNode AS nodes "
 152                 + "   LEFT JOIN TaxonBase AS taxa ON nodes.taxon_id = taxa.id "
 153                 + " WHERE taxa.DTYPE = 'Taxon' AND nodes.classification_id = " + classificationId;
 154          @SuppressWarnings("unchecked")
 155         List<BigInteger> result = getSession().createSQLQuery(queryString).list();
 156          return result.get(0).intValue ();
 157     }
 158
 159     @Override
 160     public List<UuidAndTitleCache<TaxonNode>> listChildNodesAsUuidAndTitleCache(UuidAndTitleCache<TaxonNode> parent) {
 161         String queryString =
 162                   " SELECT tn.uuid, tn.id, t.titleCache "
 163                 + " FROM TaxonNode tn "
 164                 + "    INNER JOIN tn.taxon AS t "
 165                 + " WHERE tn.parent.uuid = :parentId";
 166
 167         Query query =  getSession().createQuery(queryString);
 168         query.setParameter("parentId", parent.getUuid());
 169
 170         @SuppressWarnings("unchecked")
 171         List<Object[]> result = query.list();
 172
 173         List<UuidAndTitleCache<TaxonNode>> list = new ArrayList<>();
 174         for(Object[] object : result){
 175             list.add(new UuidAndTitleCache<>((UUID) object[0],(Integer) object[1], (String) object[2]));
 176         }
 177         return list;
 178     }
 179
 180     @Override
 181     public List<TaxonNodeDto> listChildNodesAsTaxonNodeDto(UuidAndTitleCache<TaxonNode> parent) {
 182         String queryString =
 183                  " SELECT tn "
 184                + " FROM TaxonNode tn "
 185                + "    INNER JOIN tn.taxon AS t "
 186                + " WHERE tn.parent.uuid = :parentId";
 187         Query query =  getSession().createQuery(queryString);
 188         query.setParameter("parentId", parent.getUuid());
 189
 190         @SuppressWarnings("unchecked")
 191         List<TaxonNode> result = query.list();
 192
 193         List<TaxonNodeDto> list = new ArrayList<>();
 194         for(TaxonNode object : result){
 195             list.add(new TaxonNodeDto(object));
 196         }
 197         return list;
 198     }
 199
 200     @Override
 201     public List<UuidAndTitleCache<TaxonNode>> getUuidAndTitleCache(Integer limit, String pattern, UUID classificationUuid) {
 202         String queryString = "SELECT tn.uuid, tn.id, t.titleCache, t.name.rank "
 203                 + " FROM TaxonNode tn "
 204                         + "   INNER JOIN tn.taxon AS t "
 205                         + "   INNER JOIN tn.classification AS cls "
 206                         + "WHERE t.titleCache LIKE :pattern ";
 207         if(classificationUuid != null){
 208                 queryString += "AND cls.uuid = :classificationUuid";
 209         }
 210         Query query =  getSession().createQuery(queryString);
 211
 212         query.setParameter("pattern", pattern.toLowerCase()+"%");
 213         query.setParameter("classificationUuid", classificationUuid);
 214
 215
 216         @SuppressWarnings("unchecked")
 217         List<Object[]> result = query.list();
 218         Collections.sort(result, new UuidAndTitleCacheTaxonComparator());
 219
 220         List<UuidAndTitleCache<TaxonNode>> list = new ArrayList<>();
 221         for(Object[] object : result){
 222             list.add(new UuidAndTitleCache<>((UUID) object[0],(Integer) object[1], (String) object[2]));
 223         }
 224         return list;
 225     }
 226
 227     /**
 228      * {@inheritDoc}
 229      */
 230     @Override
 231     public UuidAndTitleCache<TaxonNode> getParentUuidAndTitleCache(UuidAndTitleCache<TaxonNode> child) {
 232         String queryString = ""
 233                 + " SELECT tn.parent.uuid, tn.parent.id, tn.parent.taxon.titleCache, "
 234                 + "                  tn.parent.classification.titleCache "
 235                 + " FROM TaxonNode tn"
 236                 + "    LEFT OUTER JOIN tn.parent.taxon"
 237                 + " WHERE tn.id = :childId";
 238         Query query =  getSession().createQuery(queryString);
 239         query.setParameter("childId", child.getId());
 240         List<UuidAndTitleCache<TaxonNode>> list = new ArrayList<>();
 241
 242         @SuppressWarnings("unchecked")
 243         List<Object[]> result = query.list();
 244
 245         for(Object[] object : result){
 246             UUID uuid = (UUID) object[0];
 247             Integer id = (Integer) object[1];
 248             String taxonTitleCache = (String) object[2];
 249             String classificationTitleCache = (String) object[3];
 250             if(taxonTitleCache!=null){
 251                 list.add(new UuidAndTitleCache<>(uuid,id, taxonTitleCache));
 252             }
 253             else{
 254                 list.add(new UuidAndTitleCache<>(uuid,id, classificationTitleCache));
 255             }
 256         }
 257         if(list.size()==1){
 258             return list.iterator().next();
 259         }
 260         return null;
 261     }
 262
 263     @Override
 264     public List<TaxonNode> listChildrenOf(TaxonNode node, Integer pageSize, Integer pageIndex,
 265             boolean recursive, boolean includeUnpublished, List<String> propertyPaths){
 266
 267         if (recursive == true){
 268                 Criteria crit = childrenOfCriteria(node, includeUnpublished);
 269
 270                 if(pageSize != null) {
 271                 crit.setMaxResults(pageSize);
 272                 if(pageIndex != null) {
 273                     crit.setFirstResult(pageIndex * pageSize);
 274                 } else {
 275                     crit.setFirstResult(0);
 276                 }
 277             }
 278                 @SuppressWarnings("unchecked")
 279             List<TaxonNode> results = crit.list();
 280                 results.remove(node);
 281                 defaultBeanInitializer.initializeAll(results, propertyPaths);
 282                 return results;
 283         }else{
 284                 return classificationDao.listChildrenOf(node.getTaxon(), node.getClassification(), null,
 285                        includeUnpublished, pageSize, pageIndex, propertyPaths);
 286         }
 287
 288     }
 289
 290     @Override
 291         public Long countChildrenOf(TaxonNode node, Classification classification,
 292                         boolean recursive, boolean includeUnpublished) {
 293
 294                 if (recursive == true){
 295                         Criteria crit = childrenOfCriteria(node, includeUnpublished);
 296                 crit.setProjection(Projections.rowCount());
 297                 return ((Integer)crit.uniqueResult().hashCode()).longValue();
 298                 }else{
 299                         return classificationDao.countChildrenOf(
 300                                 node.getTaxon(), classification, null, includeUnpublished);
 301                 }
 302         }
 303     /**
 304      * @param node
 305      * @param includeUnpublished
 306      * @return
 307      */
 308     private Criteria childrenOfCriteria(TaxonNode node, boolean includeUnpublished) {
 309         Criteria crit = getSession().createCriteria(TaxonNode.class);
 310         crit.add( Restrictions.like("treeIndex", node.treeIndex()+ "%") );
 311         if (!includeUnpublished){
 312             crit.createCriteria("taxon").add( Restrictions.eq("publish", Boolean.TRUE));
 313         }
 314         return crit;
 315     }
 316     /**
 317      * {@inheritDoc}
 318      */
 319     @Override
 320     public List<TaxonNodeAgentRelation> listTaxonNodeAgentRelations(UUID taxonUuid, UUID classificationUuid,
 321             UUID agentUuid, UUID rankUuid, UUID relTypeUuid, Integer start, Integer limit,
 322             List<String> propertyPaths) {
 323
 324         StringBuilder hql = prepareListTaxonNodeAgentRelations(taxonUuid, classificationUuid,
 325                 agentUuid, rankUuid, relTypeUuid, false);
 326
 327         Query query =  getSession().createQuery(hql.toString());
 328
 329         if(limit != null) {
 330             query.setMaxResults(limit);
 331             if(start != null) {
 332                 query.setFirstResult(start);
 333             }
 334         }
 335
 336         setParamsForListTaxonNodeAgentRelations(taxonUuid, classificationUuid, agentUuid, rankUuid, relTypeUuid, query);
 337
 338         @SuppressWarnings("unchecked")
 339         List<TaxonNodeAgentRelation> records = query.list();
 340
 341         if(propertyPaths != null) {
 342             defaultBeanInitializer.initializeAll(records, propertyPaths);
 343         }
 344         return records;
 345     }
 346
 347     @Override
 348     public <S extends TaxonNode> List<S> list(Class<S> type, List<Restriction<?>> restrictions, Integer limit,
 349             Integer start, List<OrderHint> orderHints, List<String> propertyPaths) {
 350         // TODO Auto-generated method stub
 351         return list(type, restrictions, limit, start, orderHints, propertyPaths, INCLUDE_UNPUBLISHED);
 352     }
 353
 354     @Override
 355     public <S extends TaxonNode> List<S> list(Class<S> type, List<Restriction<?>> restrictions, Integer limit,
 356             Integer start, List<OrderHint> orderHints, List<String> propertyPaths, boolean includePublished) {
 357
 358         Criteria criteria = createCriteria(type, restrictions, false);
 359
 360         if(!includePublished){
 361             criteria.add(Restrictions.eq("taxon.publish", true));
 362         }
 363
 364         addLimitAndStart(criteria, limit, start);
 365         addOrder(criteria, orderHints);
 366
 367         @SuppressWarnings("unchecked")
 368         List<S> result = criteria.list();
 369         defaultBeanInitializer.initializeAll(result, propertyPaths);
 370         return result;
 371     }
 372
 373
 374     @Override
 375     public long count(Class<? extends TaxonNode> type, List<Restriction<?>> restrictions) {
 376         return count(type, restrictions, INCLUDE_UNPUBLISHED);
 377     }
 378
 379
 380     @Override
 381     public long count(Class<? extends TaxonNode> type, List<Restriction<?>> restrictions, boolean includePublished) {
 382
 383         Criteria criteria = createCriteria(type, restrictions, false);
 384         if(!includePublished){
 385             criteria.add(Restrictions.eq("taxon.publish", true));
 386         }
 387         criteria.setProjection(Projections.projectionList().add(Projections.rowCount()));
 388         return (Long) criteria.uniqueResult();
 389     }
 390
 391
 392     /**
 393      * {@inheritDoc}
 394      */
 395     @Override
 396     public long countTaxonNodeAgentRelations(UUID taxonUuid, UUID classificationUuid, UUID agentUuid, UUID rankUuid, UUID relTypeUuid) {
 397
 398         StringBuilder hql = prepareListTaxonNodeAgentRelations(taxonUuid, classificationUuid, agentUuid, rankUuid, relTypeUuid, true);
 399         Query query =  getSession().createQuery(hql.toString());
 400
 401         setParamsForListTaxonNodeAgentRelations(taxonUuid, classificationUuid, agentUuid, rankUuid, relTypeUuid, query);
 402
 403         Long count = Long.parseLong(query.uniqueResult().toString());
 404
 405         return count;
 406     }
 407     /**
 408      * @param taxonUuid
 409      * @param classificationUuid
 410      * @param agentUuid
 411      * @param relTypeUuid TODO
 412      * @param doCount TODO
 413      * @param rankId
 414      *     limit to taxa having this rank, only applies if <code>taxonUuid = null</code>
 415      * @return
 416      */
 417     private StringBuilder prepareListTaxonNodeAgentRelations(UUID taxonUuid, UUID classificationUuid, UUID agentUuid, UUID rankUuid, UUID relTypeUuid, boolean doCount) {
 418
 419         StringBuilder hql = new StringBuilder();
 420
 421         String join_fetch_mode = doCount ? "JOIN" : "JOIN FETCH";
 422
 423         if(doCount) {
 424             hql.append("SELECT COUNT(tnar)");
 425         } else {
 426             hql.append("SELECT tnar");
 427         }
 428
 429         hql.append(" FROM TaxonNodeAgentRelation AS tnar ");
 430         if(taxonUuid != null) {
 431             // taxonUuid is search filter, do not fetch it
 432             hql.append(" JOIN tnar.taxonNode AS tn "
 433                     + "  JOIN tn.taxon AS t ");
 434         } else {
 435             hql.append(join_fetch_mode)
 436                 .append(" tnar.taxonNode AS tn ")
 437                 .append(join_fetch_mode).append(" tn.taxon AS t ");
 438             if(rankUuid != null) {
 439                 hql.append(" join t.name as n ");
 440             }
 441         }
 442         hql.append(" JOIN tn.classification AS c ");
 443         if(agentUuid != null) {
 444             // agentUuid is search filter, do not fetch it
 445 //            hql.append(" join tnar.agent as a ");
 446             hql.append(join_fetch_mode).append(" tnar.agent AS a ");
 447         } else {
 448             hql.append(join_fetch_mode).append(" tnar.agent AS a ");
 449         }
 450
 451         hql.append(" WHERE (1 = 1) ");
 452
 453         if(relTypeUuid != null) {
 454             hql.append(" AND tnar.type.uuid = :relTypeUuid ");
 455         }
 456
 457         if(taxonUuid != null) {
 458             hql.append(" AND t.uuid = :taxonUuid ");
 459         } else {
 460             if(rankUuid != null) {
 461                 hql.append(" AND n.rank.uuid = :rankUuid ");
 462             }
 463         }
 464         if(classificationUuid != null) {
 465             hql.append(" AND c.uuid = :classificationUuid ");
 466         }
 467         if(agentUuid != null) {
 468             hql.append(" AND a.uuid = :agentUuid ");
 469         }
 470
 471         hql.append(" ORDER BY a.titleCache");
 472         return hql;
 473     }
 474     /**
 475      * @param taxonUuid
 476      * @param classificationUuid
 477      * @param agentUuid
 478      * @param relTypeUuid TODO
 479      * @param query
 480      * @param rankId TODO
 481      */
 482     private void setParamsForListTaxonNodeAgentRelations(UUID taxonUuid, UUID classificationUuid, UUID agentUuid,
 483             UUID rankUuid, UUID relTypeUuid, Query query) {
 484
 485         if(taxonUuid != null) {
 486             query.setParameter("taxonUuid", taxonUuid);
 487         } else {
 488             if(rankUuid != null) {
 489                 query.setParameter("rankUuid", rankUuid);
 490             }
 491         }
 492         if(classificationUuid != null) {
 493             query.setParameter("classificationUuid", classificationUuid);
 494         }
 495         if(agentUuid != null) {
 496             query.setParameter("agentUuid", agentUuid);
 497         }
 498         if(relTypeUuid != null) {
 499             query.setParameter("relTypeUuid", relTypeUuid);
 500         }
 501     }
 502
 503     @Override
 504     public Map<TreeIndex, Integer> rankOrderIndexForTreeIndex(List<TreeIndex> treeIndexes,
 505             Integer minRankOrderIndex,
 506             Integer maxRankOrderIndex) {
 507
 508         Map<TreeIndex, Integer> result = new HashMap<>();
 509         if (treeIndexes == null || treeIndexes.isEmpty()){
 510             return result;
 511         }
 512
 513         String hql = " SELECT tn.treeIndex, r.orderIndex "
 514                 + " FROM TaxonNode tn "
 515                 + "     JOIN tn.taxon t "
 516                 + "     JOIN t.name n "
 517                 + "      JOIN n.rank r "
 518                 + " WHERE tn.treeIndex IN (:treeIndexes) ";
 519         if (minRankOrderIndex != null){
 520             hql += " AND r.orderIndex <= :minOrderIndex";
 521         }
 522         if (maxRankOrderIndex != null){
 523             hql += " AND r.orderIndex >= :maxOrderIndex";
 524         }
 525
 526         Query query =  getSession().createQuery(hql);
 527         query.setParameterList("treeIndexes", TreeIndex.toString(treeIndexes));
 528         if (minRankOrderIndex != null){
 529             query.setParameter("minOrderIndex", minRankOrderIndex);
 530         }
 531         if (maxRankOrderIndex != null){
 532             query.setParameter("maxOrderIndex", maxRankOrderIndex);
 533         }
 534
 535         @SuppressWarnings("unchecked")
 536         List<Object[]> list = query.list();
 537         for (Object[] o : list){
 538             result.put(TreeIndex.NewInstance((String)o[0]), (Integer)o[1]);
 539         }
 540         return result;
 541     }
 542
 543     @Override
 544     public Map<TreeIndex, UuidAndTitleCache<?>> taxonUuidsForTreeIndexes(Collection<TreeIndex> treeIndexes) {
 545         Map<TreeIndex, UuidAndTitleCache<?>> result = new HashMap<>();
 546         if (treeIndexes == null || treeIndexes.isEmpty()){
 547             return result;
 548         }
 549
 550         String hql =
 551                   " SELECT tn.treeIndex, t.uuid, tnb.titleCache "
 552                 + " FROM TaxonNode tn JOIN tn.taxon t Join t.name tnb "
 553                 + " WHERE tn.treeIndex IN (:treeIndexes) ";
 554         Query query =  getSession().createQuery(hql);
 555         query.setParameterList("treeIndexes", TreeIndex.toString(treeIndexes));
 556
 557         @SuppressWarnings("unchecked")
 558         List<Object[]> list = query.list();
 559         for (Object[] o : list){
 560             result.put(TreeIndex.NewInstance((String)o[0]), new UuidAndTitleCache<>((UUID)o[1], null, (String)o[2]));
 561         }
 562         return result;
 563     }
 564
 565     @Override
 566     public TaxonNodeDto getParentTaxonNodeDtoForRank(Classification classification, Rank rank, TaxonName name) {
 567
 568         Set<TaxonBase> taxa = name.getTaxonBases();
 569         TaxonNode node = null;
 570         String treeIndex = null;
 571         Taxon taxon = null;
 572         for (TaxonBase taxonBase:taxa) {
 573                 if (taxonBase instanceof Taxon) {
 574                         taxon = HibernateProxyHelper.deproxy(taxonBase, Taxon.class);
 575                 }else {
 576                         taxon = HibernateProxyHelper.deproxy(((Synonym)taxonBase).getAcceptedTaxon(), Taxon.class);
 577                 }
 578                 if (taxon != null) {
 579                         node = taxon.getTaxonNode(classification);
 580                         if (node != null) {
 581                                 break;
 582                         }
 583                 }
 584         }
 585         if (node != null) {
 586                 treeIndex = node.treeIndex();
 587         }
 588         Criteria nodeCrit = getSession().createCriteria(TaxonNode.class);
 589         Criteria taxonCrit = nodeCrit.createCriteria("taxon");
 590         Criteria nameCrit = taxonCrit.createCriteria("name");
 591         nodeCrit.add(Restrictions.eq("classification", classification));
 592         nameCrit.add(Restrictions.eq("rank", rank));
 593
 594          ProjectionList projList = Projections.projectionList();
 595          projList.add(Projections.property("treeIndex"));
 596          projList.add(Projections.property("uuid"));
 597          nodeCrit.setProjection(projList);
 598         @SuppressWarnings("unchecked")
 599         List<Object[]> list = nodeCrit.list();
 600         UUID uuid = null;
 601         if (list.size() > 0) {
 602                 for (Object o: list) {
 603                          Object[] objectArray = (Object[]) o;
 604                  uuid = (UUID)objectArray[1];
 605                  String treeIndexParent = (String) objectArray[0];
 606                  try {
 607                  if (treeIndex.startsWith(treeIndexParent)) {
 608                          node = load(uuid);
 609                          break;
 610                  }
 611                  }catch(NullPointerException e) {
 612                          System.err.println("Parent: " + treeIndexParent + " treeIndex: " + treeIndex + " taxon: " + taxon != null?taxon.getTitleCache(): "");
 613                  }
 614                 }
 615                 if (node != null) {
 616                         TaxonNodeDto dto = new TaxonNodeDto(node);
 617                         return dto;
 618                 }
 619
 620         }
 621
 622         return null;
 623     }
 624
 625
 626
 627     /**
 628      * {@inheritDoc}
 629      */
 630     @Override
 631     public int countSecundumForSubtreeAcceptedTaxa(TreeIndex subTreeIndex, Reference newSec,
 632             boolean overwriteExisting, boolean includeSharedTaxa, boolean emptySecundumDetail) {
 633         String queryStr = forSubtreeAcceptedQueryStr(includeSharedTaxa, subTreeIndex, false, SelectMode.COUNT);
 634         if (!overwriteExisting){
 635             queryStr += " AND t.sec IS NULL ";
 636         }
 637         return countResult(queryStr);
 638     }
 639     /**
 640      * @param queryStr
 641      * @return
 642      */
 643     private int countResult(String queryStr) {
 644         Query query = getSession().createQuery(queryStr);
 645         return ((Long)query.uniqueResult()).intValue();
 646     }
 647     /**
 648      * {@inheritDoc}
 649      */
 650     @Override
 651     public int countSecundumForSubtreeSynonyms(TreeIndex subTreeIndex, Reference newSec,
 652             boolean overwriteExisting, boolean includeSharedTaxa, boolean emptySecundumDetail) {
 653         String queryStr = forSubtreeSynonymQueryStr(includeSharedTaxa, subTreeIndex, false, SelectMode.COUNT);
 654         if (!overwriteExisting){
 655             queryStr += " AND syn.sec IS NULL ";
 656         }
 657         return countResult(queryStr);
 658     }
 659
 660
 661     /**
 662      * {@inheritDoc}
 663      */
 664     //#3465
 665     @Override
 666     public Set<TaxonBase> setSecundumForSubtreeAcceptedTaxa(TreeIndex subTreeIndex, Reference newSec,
 667             boolean overwriteExisting, boolean includeSharedTaxa, boolean emptyDetail, IProgressMonitor monitor) {
 668         //for some reason this does not work, maybe because the listeners are not activated,
 669         //but also the first taxon for some reason does not get updated in terms of secundum, but only by the update listener
 670 //        String where = "SELECT t.id FROM TaxonNode tn JOIN tn.taxon t " +
 671 //                " WHERE tn.treeIndex like '%s%%' ORDER BY t.id";
 672 //        where = String.format(where, subTreeIndex.toString());
 673 //        Query query1 = getSession().createQuery(where);
 674 //        List l = query1.list();
 675 //
 676 //        String hql = "UPDATE Taxon SET sec = :newSec, publish=false WHERE id IN (" + where + ")";
 677 //        Query query = getSession().createQuery(hql);
 678 //        query.setParameter("newSec", newSec);
 679 //        int n = query.executeUpdate();
 680
 681         String queryStr = forSubtreeAcceptedQueryStr(includeSharedTaxa, subTreeIndex, false, SelectMode.ID);
 682         if (!overwriteExisting){
 683             queryStr += " AND t.sec IS NULL ";
 684         }
 685         return setSecundum(newSec, emptyDetail, queryStr, monitor);
 686
 687     }
 688
 689     @Override
 690     public Set<TaxonBase> setSecundumForSubtreeSynonyms(TreeIndex subTreeIndex, Reference newSec,
 691             boolean overwriteExisting, boolean includeSharedTaxa, boolean emptyDetail, IProgressMonitor monitor) {
 692
 693         String queryStr = forSubtreeSynonymQueryStr(includeSharedTaxa, subTreeIndex, false, SelectMode.ID);
 694         if (!overwriteExisting){
 695             queryStr += " AND syn.sec IS NULL ";
 696         }
 697         return setSecundum(newSec, emptyDetail, queryStr, monitor);
 698     }
 699     /**
 700      * @param newSec
 701      * @param emptyDetail
 702      * @param queryStr
 703      * @param monitor
 704      * @return
 705      */
 706     @SuppressWarnings("unchecked")
 707     private <T extends TaxonBase<?>> Set<T> setSecundum(Reference newSec, boolean emptyDetail, String queryStr, IProgressMonitor monitor) {
 708         Set<T> result = new HashSet<>();
 709         Query query = getSession().createQuery(queryStr);
 710         List<List<Integer>> partitionList = splitIdList(query.list(), DEFAULT_PARTITION_SIZE);
 711         for (List<Integer> taxonIdList : partitionList){
 712             List<TaxonBase> taxonList = taxonDao.loadList(taxonIdList, null, null);
 713             for (TaxonBase taxonBase : taxonList){
 714                 if (taxonBase != null){
 715                     taxonBase = taxonDao.load(taxonBase.getUuid());
 716                     taxonBase.setSec(newSec);
 717                     if (emptyDetail){
 718                         taxonBase.setSecMicroReference(null);
 719                     }
 720                     result.add((T)CdmBase.deproxy(taxonBase));
 721                     monitor.worked(1);
 722                     if (monitor.isCanceled()){
 723                         return result;
 724                     }
 725                 }
 726             }
 727         }
 728         return result;
 729     }
 730
 731     private List<List<Integer>> splitIdList(List<Integer> idList, Integer size){
 732         List<List<Integer>> result = new ArrayList<>();
 733         for (int i = 0; (i*size)<idList.size(); i++) {
 734             int upper = Math.min((i+1)*size, idList.size());
 735             result.add(idList.subList(i*size, upper));
 736         }
 737         return result;
 738     }
 739
 740     @Override
 741     public int countPublishForSubtreeAcceptedTaxa(TreeIndex subTreeIndex, boolean publish, boolean includeSharedTaxa, boolean includeHybrids) {
 742         String queryStr = forSubtreeAcceptedQueryStr(includeSharedTaxa, subTreeIndex, !includeHybrids, SelectMode.COUNT);
 743         queryStr += " AND t.publish != :publish ";
 744         System.out.println(queryStr);
 745         Query query = getSession().createQuery(queryStr);
 746         query.setBoolean("publish", publish);
 747         return ((Long)query.uniqueResult()).intValue();
 748     }
 749
 750     @Override
 751     public int countPublishForSubtreeSynonyms(TreeIndex subTreeIndex, boolean publish, boolean includeSharedTaxa, boolean includeHybrids) {
 752         String queryStr = forSubtreeSynonymQueryStr(includeSharedTaxa, subTreeIndex, !includeHybrids, SelectMode.COUNT);
 753         queryStr += " AND syn.publish != :publish ";
 754         Query query = getSession().createQuery(queryStr);
 755         query.setBoolean("publish", publish);
 756         return ((Long)query.uniqueResult()).intValue();
 757     }
 758
 759     @Override
 760     public Set<TaxonBase> setPublishForSubtreeAcceptedTaxa(TreeIndex subTreeIndex, boolean publish,
 761             boolean includeSharedTaxa, boolean includeHybrids, IProgressMonitor monitor) {
 762         String queryStr = forSubtreeAcceptedQueryStr(includeSharedTaxa, subTreeIndex, !includeHybrids, SelectMode.ID);
 763         queryStr += " AND t.publish != :publish ";
 764         return setPublish(publish, queryStr, null, monitor);
 765     }
 766
 767     @Override
 768     public Set<TaxonBase> setPublishForSubtreeSynonyms(TreeIndex subTreeIndex, boolean publish,
 769             boolean includeSharedTaxa, boolean includeHybrids, IProgressMonitor monitor) {
 770         String queryStr = forSubtreeSynonymQueryStr(includeSharedTaxa, subTreeIndex, !includeHybrids, SelectMode.ID);
 771         queryStr += " AND syn.publish != :publish ";
 772         return setPublish(publish, queryStr, null, monitor);
 773     }
 774     @Override
 775     public int countPublishForSubtreeRelatedTaxa(TreeIndex subTreeIndex, boolean publish, boolean includeSharedTaxa, boolean includeHybrids) {
 776         String queryStr = forSubtreeRelatedTaxaQueryStr(includeSharedTaxa, subTreeIndex, !includeHybrids, SelectMode.COUNT);
 777         queryStr += " AND relTax.publish != :publish ";
 778         Query query = getSession().createQuery(queryStr);
 779         query.setBoolean("publish", publish);
 780         return ((Long)query.uniqueResult()).intValue();
 781     }
 782
 783     @Override
 784     public Set<TaxonBase> setPublishForSubtreeRelatedTaxa(TreeIndex subTreeIndex, boolean publish,
 785             Set<UUID> relationTypes, boolean includeSharedTaxa, boolean includeHybrids,
 786             IProgressMonitor monitor) {
 787         String queryStr = forSubtreeRelatedTaxaQueryStr(includeSharedTaxa, subTreeIndex, !includeHybrids, SelectMode.ID);
 788         queryStr += " AND relTax.publish != :publish ";
 789         queryStr += " AND rel.type.uuid IN (:relTypeUuid)";
 790         return setPublish(publish, queryStr, relationTypes, monitor);
 791     }
 792
 793     private static final int DEFAULT_PARTITION_SIZE = 100;
 794     /**
 795      * @param publish
 796      * @param queryStr
 797      * @return
 798      */
 799     private <T extends TaxonBase<?>> Set<T> setPublish(boolean publish, String queryStr, Set<UUID> relTypeUuids, IProgressMonitor monitor) {
 800         Set<T> result = new HashSet<>();
 801         Query query = getSession().createQuery(queryStr);
 802         query.setBoolean("publish", publish);
 803         if (relTypeUuids != null && !relTypeUuids.isEmpty()){
 804             query.setParameterList("relTypeUuid", relTypeUuids);
 805         }
 806         @SuppressWarnings("unchecked")
 807         List<List<Integer>> partitionList = splitIdList(query.list(), DEFAULT_PARTITION_SIZE);
 808         for (List<Integer> taxonIdList : partitionList){
 809             List<TaxonBase> taxonList = taxonDao.loadList(taxonIdList, null, null);
 810             for (TaxonBase taxonBase : taxonList){
 811                 if (taxonBase != null){
 812                     taxonBase.setPublish(publish);
 813                     result.add((T)CdmBase.deproxy(taxonBase));
 814                     monitor.worked(1);
 815                     if (monitor.isCanceled()){
 816                         return result;
 817                     }
 818                 }
 819             }
 820         }
 821         return result;
 822     }
 823
 824
 825     /**
 826      * @param includeSharedTaxa
 827      * @param subTreeIndex
 828      * @param includeHybrids
 829      * @param includeSharedTaxa2
 830      * @return
 831      */
 832     private String forSubtreeSynonymQueryStr(boolean includeSharedTaxa, TreeIndex subTreeIndex, boolean excludeHybrids, SelectMode mode) {
 833         String queryStr = "SELECT " + mode.hql("syn")
 834                 + " FROM TaxonNode tn "
 835                 + "   JOIN tn.taxon t "
 836                 + "   JOIN t.synonyms syn  LEFT JOIN syn.name n ";
 837         String whereStr = " tn.treeIndex LIKE '%1$s%%' ";
 838         if (!includeSharedTaxa){
 839             whereStr += " AND NOT EXISTS ("
 840                     + "FROM TaxonNode tn2 WHERE tn2.taxon = t AND tn2.treeIndex not like '%1$s%%')  ";
 841         }
 842         whereStr = handleExcludeHybrids(whereStr, excludeHybrids, "syn");
 843         queryStr += " WHERE " + String.format(whereStr, subTreeIndex.toString());
 844
 845         return queryStr;
 846     }
 847
 848     /**
 849      * @param queryStr
 850      * @param excludeHybrids
 851      * @return
 852      */
 853     private String handleExcludeHybrids(String whereStr, boolean excludeHybrids, String t) {
 854         if(excludeHybrids){
 855
 856             String hybridWhere =  " AND (n is NULL OR "
 857                     + " (n.monomHybrid=0 AND n.binomHybrid=0 "
 858                     + "   AND n.trinomHybrid=0 AND n.hybridFormula=0 )) ";
 859
 860             whereStr += hybridWhere; //String.format(hybridWhere, t);
 861         }
 862         return whereStr;
 863     }
 864
 865     private String forSubtreeRelatedTaxaQueryStr(boolean includeSharedTaxa, TreeIndex subTreeIndex,
 866             boolean excludeHybrids, SelectMode mode) {
 867         String queryStr = "SELECT " + mode.hql("relTax")
 868                 + " FROM TaxonNode tn "
 869                 + "   JOIN tn.taxon t "
 870                 + "   JOIN t.relationsToThisTaxon rel"
 871                 + "   JOIN rel.relatedFrom relTax  LEFT JOIN relTax.name n ";
 872         String whereStr =" tn.treeIndex LIKE '%1$s%%' ";
 873         if (!includeSharedTaxa){
 874             //toTaxon should only be used in the given subtree
 875             whereStr += " AND NOT EXISTS ("
 876                     + "FROM TaxonNode tn2 WHERE tn2.taxon = t AND tn2.treeIndex not like '%1$s%%')  ";
 877             //from taxon should not be used in another classification
 878             whereStr += " AND NOT EXISTS ("
 879                     + "FROM TaxonNode tn3 WHERE tn3.taxon = relTax AND tn3.treeIndex not like '%1$s%%')  ";
 880             //fromTaxon should not be related as e.g. pro parte synonym or misapplication to
 881             //another taxon which is not part of the subtree
 882             //TODO and has not the publish state
 883             whereStr += " AND NOT EXISTS ("
 884                     + "FROM TaxonNode tn4 JOIN tn4.taxon t2 JOIN t2.relationsToThisTaxon rel2  "
 885                     + "   WHERE rel2.relatedFrom = relTax AND tn4.treeIndex not like '%1$s%%' "
 886                     + "         AND tn4.taxon.publish != :publish ) ";
 887         }
 888         whereStr = handleExcludeHybrids(whereStr, excludeHybrids, "relTax");
 889         queryStr += " WHERE " + String.format(whereStr, subTreeIndex.toString());
 890
 891         return queryStr;
 892     }
 893
 894     private enum SelectMode{
 895         COUNT(" count(*) "),
 896         ID ("id "),
 897         UUID("uuid "),
 898         FULL("");
 899         private String hql;
 900         SelectMode(String hql){
 901             this.hql = hql;
 902         }
 903         public String hql(String prefix){
 904             switch (this){
 905             case ID:
 906             case UUID:
 907                 return CdmUtils.Nz(prefix)+"." + hql;
 908             case FULL:
 909                 return CdmUtils.Nz(prefix) + hql;
 910             case COUNT:
 911             default: return hql;
 912             }
 913
 914         }
 915     }
 916
 917     private String forSubtreeAcceptedQueryStr(boolean includeSharedTaxa, TreeIndex subTreeIndex, boolean excludeHybrids, SelectMode mode) {
 918         String queryStr = "SELECT " + mode.hql("t")
 919                 + " FROM TaxonNode tn JOIN tn.taxon t LEFT JOIN t.name n ";
 920         String whereStr = " tn.treeIndex like '%1$s%%' ";
 921         if (!includeSharedTaxa){
 922             whereStr += " AND NOT EXISTS ("
 923                     + "FROM TaxonNode tn2 WHERE tn2.taxon = t AND tn2.treeIndex not like '%1$s%%')  ";
 924         }
 925         whereStr = handleExcludeHybrids(whereStr, excludeHybrids, "t");
 926         queryStr += " WHERE " + String.format(whereStr, subTreeIndex.toString());
 927
 928         return queryStr;
 929     }
 930
 931     @Override
 932     public List<UuidAndTitleCache<TaxonNode>> getTaxonNodeUuidAndTitleCacheOfAcceptedTaxaByClassification(Classification classification, Integer limit, String pattern, boolean searchForClassifications) {
 933         int classificationId = classification.getId();
 934
 935          String queryString =
 936                    " SELECT nodes.uuid, nodes.id,  taxon.titleCache, taxon.name.rank " +
 937                    " FROM TaxonNode AS nodes "
 938                    + "    JOIN nodes.taxon as taxon " +
 939                    " WHERE nodes.classification.id = " + classificationId ;
 940          if (pattern != null){
 941              if (pattern.equals("?")){
 942                  limit = null;
 943              } else{
 944                  if (!pattern.endsWith("*")){
 945                      pattern += "%";
 946                  }
 947                  pattern = pattern.replace("*", "%");
 948                  pattern = pattern.replace("?", "%");
 949                  queryString = queryString + " AND taxon.titleCache LIKE (:pattern) " ;
 950              }
 951          }
 952
 953          Query query = getSession().createQuery(queryString);
 954
 955          if (limit != null){
 956              query.setMaxResults(limit);
 957          }
 958
 959          if (pattern != null && !pattern.equals("?")){
 960              query.setParameter("pattern", pattern);
 961          }
 962          @SuppressWarnings("unchecked")
 963          List<Object[]> result = query.list();
 964
 965          if (searchForClassifications){
 966              queryString = ""
 967                      + " SELECT nodes.uuid, nodes.id,  nodes.classification.titleCache, NULLIF(1,1) "
 968                      + " FROM TaxonNode AS nodes "
 969                      + " WHERE nodes.classification.id = " + classificationId +
 970                           " AND nodes.taxon IS NULL";
 971              if (pattern != null){
 972                  if (pattern.equals("?")){
 973                      limit = null;
 974                  } else{
 975                      if (!pattern.endsWith("*")){
 976                          pattern += "%";
 977                      }
 978                      pattern = pattern.replace("*", "%");
 979                      pattern = pattern.replace("?", "%");
 980                      queryString = queryString + " AND nodes.classification.titleCache LIKE (:pattern) " ;
 981                  }
 982              }
 983              query = getSession().createQuery(queryString);
 984
 985              if (limit != null){
 986                  query.setMaxResults(limit);
 987              }
 988
 989              if (pattern != null && !pattern.equals("?")){
 990                  query.setParameter("pattern", pattern);
 991              }
 992              @SuppressWarnings("unchecked")
 993              List<Object[]> resultClassifications = query.list();
 994
 995              result.addAll(resultClassifications);
 996          }
 997
 998          if(result.size() == 0){
 999              return null;
1000          }else{
1001              List<UuidAndTitleCache<TaxonNode>> list = new ArrayList<>(result.size());
1002              if (result.iterator().next().length == 4){
1003                  Collections.sort(result, new UuidAndTitleCacheTaxonComparator());
1004              }
1005              for (Object object : result){
1006                  Object[] objectArray = (Object[]) object;
1007                  UUID uuid = (UUID)objectArray[0];
1008                  Integer id = (Integer) objectArray[1];
1009                  String titleCache = (String) objectArray[2];
1010
1011                  list.add(new UuidAndTitleCache<>(TaxonNode.class, uuid, id, titleCache));
1012              }
1013
1014              return list;
1015          }
1016     }
1017
1018
1019 }