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