public Long maxGameNum() {
   return (Long)
       getSession()
           .createCriteria(GameEntity.class)
           .setProjection(Projections.max("gameNum"))
           .uniqueResult();
 }
Example #2
0
  @Override
  public int fetchMaxBatchNumber() {
    int maxBatchNumber = 0;
    Session session = null;
    List entities = null;
    try {
      session = sessionFactory.openSession();
      Criteria maxBatchNumberCri = session.createCriteria(CampaignImportModel.class);
      ProjectionList maxBatchNumberProj = Projections.projectionList();
      maxBatchNumberProj.add(Projections.max("batchFileNo"));
      maxBatchNumberCri.setProjection(maxBatchNumberProj);
      entities = maxBatchNumberCri.list();
      maxBatchNumber =
          (null != entities && !entities.isEmpty() && null != entities.get(0))
              ? Integer.valueOf(entities.get(0).toString())
              : 0;
    } catch (HibernateException e) {
      LOGGER.error(" HibernateException fetching max batch number : " + e.getMessage());
    } finally {
      session.close();
    }

    LOGGER.info(" Max Batch Number: " + maxBatchNumber);
    return maxBatchNumber;
  }
 /** {@inheritDoc} */
 @Override
 @Transactional(readOnly = true)
 public Long getSequenceId(
     final TableDefinition tableDefinition, final ColumnDefinition definition) {
   Long id = 0L;
   synchronized (cache) {
     String cacheKey = tableDefinition.getDestinationTable() + "." + definition.getDestination();
     if (cache.containsKey(cacheKey)) {
       id = cache.get(cacheKey);
     } else {
       // check from DB.
       String sql_queryForMax =
           "SELECT MAX("
               + definition.getDestination()
               + ") AS ID FROM "
               + tableDefinition.getDestinationTable();
       Criteria criteria = getSession().createCriteria(tableDefinition.getDestinationTable());
       Integer max =
           (Integer)
               criteria.setProjection(Projections.max(definition.getDestination())).uniqueResult();
       if (max == null) {
         return 1L;
       } else {
         return max.longValue() + 1;
       }
     }
     id++;
     cache.put(cacheKey, id);
   }
   return id;
 }
 /*
  * (non-Javadoc)
  * @see com.csit.dao.CompetitionPhotoDAO#getMaxArray(com.csit.model.Competition)
  */
 @Override
 public Integer getMaxArray(Competition competition) {
   Criteria criteria = getCurrentSession().createCriteria(CompetitionPhoto.class);
   criteria.add(Restrictions.eq("competition", competition));
   criteria.setProjection(Projections.max("array"));
   Object obj = criteria.uniqueResult();
   return obj == null ? 0 : (Integer) obj;
 }
 @Override
 public LocalDate getLatestDate() {
   Criteria criteria =
       getSession()
           .createCriteria(ReadingEnergy.class)
           .setProjection(Projections.max("readingDate"));
   return (LocalDate) criteria.uniqueResult();
 }
Example #6
0
 public Date getUpdatedFromDB() {
   return (Date)
       this.currentSession()
           .createCriteria(Rules.class)
           .setCacheable(true)
           .setCacheRegion(CandlepinCacheRegions.FIVE_SECONDS_QUERY_CACHE)
           .setProjection(Projections.projectionList().add(Projections.max("updated")))
           .uniqueResult();
 }
Example #7
0
  @Override
  public Integer MaxId(Integer potentialId) {

    Criteria criteria = getCurrentSession().createCriteria(Reply.class);
    criteria.add(Restrictions.eq("potential.potentialId", potentialId));
    Integer maxReplyId =
        (Integer) criteria.setProjection(Projections.max("replyId")).uniqueResult();
    return maxReplyId;
  }
 @Transactional
 public Long transactionMaxIdForAccount(OandaAccount account) {
   Criteria criteria =
       getSession()
           .createCriteria(OandaTransaction.class)
           .setProjection(Projections.projectionList().add(Projections.max("transactionId")))
           .add(Restrictions.eq("account", account));
   return (Long) criteria.uniqueResult();
 }
 public int getLastDvtId() {
   session.beginTransaction();
   Criteria cr =
       session
           .createCriteria(DonViTinh.class)
           .setProjection(Projections.max("dvtId")); // max("ctvtId"));
   Integer id = (Integer) cr.list().get(0);
   session.getTransaction().commit();
   return id;
 }
 @Override
 public Object findMax(String max, Map<String, Object> Map) {
   Criteria criteria = createCriteria();
   for (Iterator<String> i = Map.keySet().iterator(); i.hasNext(); ) {
     String name = i.next();
     if (filterProperty(name)) {
       criteria.add(Restrictions.eq(name, Map.get(name)));
     } else {
       log.error("Could not resolve property:" + name);
     }
   }
   return criteria.setProjection(Projections.max(max)).uniqueResult();
 }
Example #11
0
  public Object getAggregateFunctionResult() {
    Session hSession = (Session) entityManager.getDelegate();
    Criteria criteria = hSession.createCriteria(Book.class);
    Object returnObj = 0;

    //		criteria.setProjection(Projections.avg("price"));
    criteria.setProjection(Projections.max("price"));
    List<Book> books = criteria.list();
    //		criteria.setProjection((Projection)Property.forName("name")).
    //		setProjection((Projection)Property.forName("publishedDate"));
    System.out.println("[Criteria] = " + criteria.toString());
    returnObj = books;
    return returnObj;
  }
  public List<ProcessDefinition> findProcessDefinitions(
      String name, boolean latest, int start, int end, OrderByComparator orderByComparator) {

    try {
      Criteria criteria = _session.createCriteria(ProcessDefinition.class);

      if (latest) {
        ProjectionList projectionList = Projections.projectionList();

        projectionList.add(Projections.groupProperty("name"));
        projectionList.add(Projections.max("version"));

        criteria.setProjection(projectionList);

        addOrder(criteria, orderByComparator, "version");
      }

      if (name != null) {
        criteria.add(Restrictions.eq("name", name));

        addOrder(criteria, orderByComparator, "name");
      }

      if (latest == false && name == null) {
        addOrder(criteria, orderByComparator);
      }

      addPagination(criteria, start, end);

      if (latest) {
        List<Object[]> list = criteria.list();

        List<String> names = new ArrayList<String>(list.size());

        for (Object[] array : list) {
          names.add((String) array[0]);
        }

        return findProcessDefinitions(names);
      } else {
        return criteria.list();
      }
    } catch (Exception e) {
      throw new JbpmException(e);
    }
  }
 @Transactional
 public Map<OandaAccount, Long> transactionsMaxIdByAccount() {
   final String property = "account";
   Criteria criteria =
       getSession()
           .createCriteria(OandaTransaction.class)
           .setProjection(
               Projections.projectionList()
                   .add(Projections.groupProperty(property))
                   .add(Projections.max("transactionId")));
   @SuppressWarnings("rawtypes")
   List results = criteria.list();
   Map<OandaAccount, Long> accountCountMap = Maps.newLinkedHashMap();
   for (Object row : results) {
     Object[] rowData = (Object[]) row;
     accountCountMap.put((OandaAccount) rowData[0], (Long) rowData[1]);
   }
   return accountCountMap;
 }
  @Override
  public Projection buildProjection(
      String projectionField, ProjectionType projectionType, Iterator argsIterator) {
    Projection projection = new Projection();

    projection.setField(projectionField);

    switch (projectionType) {
      case RowCount:
        projection.setDetails(Projections.rowCount());
        break;

      case Count:
        projection.setDetails(Projections.count(projectionField));
        break;

      case CountDistinct:
        projection.setDetails(Projections.countDistinct(projectionField));
        break;

      case Maximum:
        projection.setDetails(Projections.max(projectionField));
        break;

      case Minimum:
        projection.setDetails(Projections.min(projectionField));
        break;

      case Average:
        projection.setDetails(Projections.avg(projectionField));
        break;

      case Sum:
        projection.setDetails(Projections.sum(projectionField));
        break;

      default:
        throw new RuntimeException("Unexpected projection type: " + projectionType);
    }

    return projection;
  }
Example #15
0
  @Override
  public String obterUltimoCodigo() {

    Criteria crit = getSession().createCriteria(Cliente.class);
    crit.setProjection(Projections.max("codigo"));
    String result = (String) crit.uniqueResult();
    String max = "";
    if (result == null) {
      max = "00001";
    } else {
      max = Integer.parseInt(result) + 1 + "";
    }

    int j = 5 - max.length();

    for (int i = 0; i < j; i++) {
      max = "0".concat(max);
    }
    return max;
  }
Example #16
0
 @Transactional(
     propagation = Propagation.REQUIRES_NEW,
     readOnly = true,
     rollbackFor = Throwable.class)
 public Calendar lastUpdated() {
   Calendar res = Calendar.getInstance();
   try {
     Session currentSession = getSession();
     currentSession.clear();
     Criteria criteria =
         currentSession.createCriteria(Flota.class).setProjection(Projections.max("updatedAt"));
     res.setTime((Date) criteria.uniqueResult());
   } catch (NullPointerException t) {
     log.error("No hay datos en la tabla.");
     return null;
   } catch (Throwable t) {
     log.error(t, t);
     return null;
   }
   return res;
 }
Example #17
0
  public static void main(String... args) {
    final String[] columns = {"max", "min", "sum", "avg"};
    try {
      Session session = HibernateUtil.getSession();

      Transaction tx = session.beginTransaction();
      Criteria criteria = session.createCriteria(Product.class);
      ProjectionList projList = Projections.projectionList();
      projList.add(Projections.max("price"));
      projList.add(Projections.min("price"));
      projList.add(Projections.sum("price"));
      projList.add(Projections.avg("price"));
      criteria.setProjection(projList);
      Object[] results = (Object[]) criteria.uniqueResult();
      tx.commit();

      for (int i = 0, n = results.length; i < n; i++) {
        Object res = results[i];
        System.out.printf("%s = %s%n", columns[i], res);
      }
    } finally {
      HibernateUtil.cleanUp();
    }
  }
Example #18
0
  public static void main(String[] args) {

    SessionFactory sessionFactory = HibernateUtil.getSessionFactroy();
    // We can obtain a sessionFactroy using openSession()
    Session session = sessionFactory.openSession();

    // Using Aggregate functions in Criteria
    Criteria criteria = session.createCriteria(Product.class);

    ProjectionList projectionList = Projections.projectionList();

    projectionList.add(Projections.max("price"));

    criteria.setProjection(projectionList);

    float maxPrice = (float) criteria.uniqueResult();

    System.out.println("Max Price:" + maxPrice);

    /*
    //Partial Records are called Projections
    //Fetch only Product name and price
    Criteria criteria=session.createCriteria(Product.class);

    Criterion criterion =Restrictions.gt("id", 3);
    Criterion criterion2 = Restrictions.like("name","G%" );

    LogicalExpression logicalExpression= Restrictions.and(criterion,criterion2);

    criteria.add(logicalExpression);
    //using projectionlist() in Projection,we can filter the records in Criteria
    ProjectionList projectionList = Projections.projectionList();

    projectionList.add(Projections.property("name"));
      	projectionList.add(Projections.property("price"));


      	criteria.setProjection(projectionList);


      	List<Object[]> objectArrayList = criteria.list();

      	for (Object[] objects : objectArrayList) {


      		System.out.println("Product Name :"+objects[0]);
      		System.out.println("Product price :"+objects[1]);

    }*/

    /*		//Using methods we create Queries using Criteria
    		Criteria criteria=session.createCriteria(Product.class);


    		//Criterians are the conditions used for the record

    		Criterion criterion =Restrictions.gt("id", 3);
    		Criterion criterion2 = Restrictions.like("name","G%" );

    //		criteria.add(criterion);
    //		criteria.add(criterion2);
    		//It returns a object called as Logical Expression
    		//using Restrictions.or() ,we get all records which satisfy anyone conditions
    		//using Restrictions.and() ,we get all records which satisfy all conditions
    		LogicalExpression logicalExpression= Restrictions.and(criterion,criterion2);

    		criteria.add(logicalExpression);

    		List<Product> productList = criteria.list();

    		for (Product product : productList) {
    			System.out.println("Id :"+product.getId());
    			System.out.println("Name :"+product.getName());
    			System.out.println("Description :"+product.getDescription());
    			System.out.println("Price :"+product.getPrice());
    			System.out.println();
    		}
    		*/
    /*//using Joins(inner-Join)
    //Query query = session.createQuery("SELECT p,s FROM Product p inner join p.supplier s");

    //using Joins(left-Outer-Join)
    Query query = session.createQuery("SELECT p,s FROM Product p left outer join p.supplier s");

    List<Object[]> lisfOfArrays=query.list();

    for (Object[] objects : lisfOfArrays) {


    	Product product = (Product) objects[0];
    	Supplier supplier = (Supplier) objects[1];


    	System.out.println("Id :"+product.getId());
    	System.out.println("Name :"+product.getName());
    	System.out.println("Description :"+product.getDescription());
    	System.out.println("Price :"+product.getPrice());
    	System.out.println();
    	System.out.println("Supplier Id :"+supplier.getId());
    	System.out.println("Supplier Name :"+supplier.getName());
    	System.out.println();
    	System.out.println();
    }
    */

    //		//Using Aggrigate Functions---AVG,MIN etc
    //
    //
    //		Query query =session.createQuery("select max(b.bidAmount) from Bid b");
    //
    //		float maxPrice = (float) query.uniqueResult();
    //
    //		System.out.println("Maximim Price is::"+maxPrice);

    /*	//If we want only two rows of the table
    //This type of queries are called Projections
    Query query =session.createQuery("select c.name,c.countrycode from City c where c.id> :cid and c.name like :cname");

    query.setInteger("cid", 4079);
    query.setString("cname", "L%");

    List<Object[]> objectArrayList = query.list();

    for (Object[] objects : objectArrayList) {

    	System.out.println("Name :"+objects[0]);
    	System.out.println("CountryCode :"+objects[1]);
    	System.out.println();

    }*/

    /*// HQL Query
    //All city Records from City Table Using City class
    //Method -1
    //Query query =session.createQuery("from City");
    //method-2(c refers to City class type)(Best-Way)
    //It is hard-coded with values
    //Query query =session.createQuery("select c from City c where c.id>4079 and c.name like 'L%'");


    //Reusable Query (using Positional Binding parameters)
    																//0					//1
    //Query query =session.createQuery("select c from City c where c.id> ? and c.name like ?");


    //query.setInteger(0, 4079);
    //query.setString(1, "L%");

    //Reusable Query (using named Binding parameters)
    																//cid and cname are Named Bind Parameters
    Query query =session.createQuery("select c from City c where c.id> :cid and c.name like :cname");

    query.setInteger("cid", 4079);
    query.setString("cname", "L%");
    //It will list all records of city table
    List<City> city = query.list();

    for (City cities : city) {

    	//System.out.println("ID         :"+cities.getId());
    	System.out.println("Name       :"+cities.getName());
    	System.out.println("CountryCode:"+cities.getCountrycode());
    	//System.out.println("District   :"+cities.getDistrict());
    	//System.out.println("Population :"+cities.getPopulation());
    	System.out.println();
    }*/

    /*//Many To many Relation(Item-Category)
    Item item = (Item) session.get(Item.class, 108);
    System.out.println("Item ID:" + item.getId());
    System.out.println("Item Name:" + item.getName());

    Set<Category> categories = item.getCategories();

    for (Category category : categories) {
    	System.out.println("Category ID:" +category.getId());
    	System.out.println("Category Name:" +category.getName());
    	System.out.println();
    }*/

    //		Item item = (Item) session.get(Item.class, 101);
    //
    //		System.out.println("Item ID:" + item.getId());
    //		System.out.println("Item Name" + item.getName());
    //
    //		// Getting Associated Bid Records with the ID
    //
    //		Set<Bid> bids = item.getBids();
    //
    //		for (Bid bid : bids) {
    //
    //			System.out.println("BidId :"+bid.getId());
    //			System.out.println("Bid Amount:"+bid.getBidAmount());
    //			System.out.println();
    //		}

    /*
     * //Add a City Record City city = new
     * City(4080,"London","UK","LDN",435678);
     *
     * //We need a Transaction to use Insert,Update Delete Transaction
     * transaction = session.getTransaction(); transaction.begin();
     *
     * session.save(city);
     *
     * transaction.commit();
     *
     * session.close();
     */

    // //getting record by ID
    // City city =(City)session.get(City.class, 102);
    //
    // System.out.println("ID         :"+city.getId());
    // System.out.println("Name       :"+city.getName());
    // System.out.println("CountryCode:"+city.getCountrycode());
    // System.out.println("District   :"+city.getDistrict());
    // System.out.println("Population :"+city.getPopulation());
  }
  public List<RecepcionMx> getRecepcionesByFiltro(FiltroMx filtro) {
    Session session = sessionFactory.getCurrentSession();
    Soundex varSoundex = new Soundex();
    Criteria crit = session.createCriteria(RecepcionMx.class, "recepcion");
    crit.createAlias("recepcion.tomaMx", "tomaMx");
    crit.createAlias("tomaMx.estadoMx", "estado");
    // crit.createAlias("orden.idTomaMx", "tomaMx");
    crit.createAlias("tomaMx.idNotificacion", "notifi");
    // siempre se tomam las muestras que no estan anuladas
    crit.add(
        Restrictions.and(
            Restrictions.eq("tomaMx.anulada", false))); // y las ordenes en estado según filtro
    if (filtro.getCodEstado() != null) {
      if (filtro.getIncluirTraslados()) {
        crit.add(
            Restrictions.or(Restrictions.eq("estado.codigo", filtro.getCodEstado()).ignoreCase())
                .add(Restrictions.or(Restrictions.eq("estado.codigo", "ESTDMX|TRAS"))));
      } else {
        crit.add(
            Restrictions.and(
                Restrictions.eq("estado.codigo", filtro.getCodEstado().toLowerCase())
                    .ignoreCase()));
      }
    }

    // se filtra por nombre y apellido persona
    if (filtro.getNombreApellido() != null) {
      // crit.createAlias("notifi.persona", "person");
      String[] partes = filtro.getNombreApellido().split(" ");
      String[] partesSnd = filtro.getNombreApellido().split(" ");
      for (int i = 0; i < partes.length; i++) {
        try {
          partesSnd[i] = varSoundex.encode(partes[i]);
        } catch (IllegalArgumentException e) {
          partesSnd[i] = "0000";
          e.printStackTrace();
        }
      }
      for (int i = 0; i < partes.length; i++) {
        Junction conditGroup = Restrictions.disjunction();
        conditGroup
            .add(
                Subqueries.propertyIn(
                    "notifi.persona.personaId",
                    DetachedCriteria.forClass(SisPersona.class, "person")
                        .add(
                            Restrictions.or(
                                    Restrictions.ilike(
                                        "person.primerNombre", "%" + partes[i] + "%"))
                                .add(
                                    Restrictions.or(
                                            Restrictions.ilike(
                                                "person.primerApellido", "%" + partes[i] + "%"))
                                        .add(
                                            Restrictions.or(
                                                    Restrictions.ilike(
                                                        "person.segundoNombre",
                                                        "%" + partes[i] + "%"))
                                                .add(
                                                    Restrictions.or(
                                                            Restrictions.ilike(
                                                                "person.segundoApellido",
                                                                "%" + partes[i] + "%"))
                                                        .add(
                                                            Restrictions.or(
                                                                Restrictions.ilike(
                                                                    "person.sndNombre",
                                                                    "%" + partesSnd[i] + "%")))))))
                        .setProjection(Property.forName("personaId"))))
            .add(
                Subqueries.propertyIn(
                    "notifi.solicitante.idSolicitante",
                    DetachedCriteria.forClass(Solicitante.class, "solicitante")
                        .add(Restrictions.ilike("solicitante.nombre", "%" + partes[i] + "%"))
                        .setProjection(Property.forName("idSolicitante"))));

        crit.add(conditGroup);
      }
    }
    // se filtra por SILAIS
    if (filtro.getCodSilais() != null) {
      crit.createAlias("notifi.codSilaisAtencion", "silais");
      crit.add(
          Restrictions.and(Restrictions.eq("silais.codigo", Long.valueOf(filtro.getCodSilais()))));
    }
    // se filtra por unidad de salud
    if (filtro.getCodUnidadSalud() != null) {
      crit.createAlias("notifi.codUnidadAtencion", "unidadS");
      crit.add(
          Restrictions.and(
              Restrictions.eq("unidadS.codigo", Long.valueOf(filtro.getCodUnidadSalud()))));
    }
    // Se filtra por rango de fecha de toma de muestra
    if (filtro.getFechaInicioTomaMx() != null && filtro.getFechaFinTomaMx() != null) {
      crit.add(
          Restrictions.and(
              Restrictions.between(
                  "tomaMx.fechaHTomaMx",
                  filtro.getFechaInicioTomaMx(),
                  filtro.getFechaFinTomaMx())));
    }
    // Se filtra por rango de fecha de recepción
    if (filtro.getFechaInicioRecep() != null && filtro.getFechaFinRecep() != null) {
      crit.add(
          Restrictions.and(
              Restrictions.between(
                  "recepcion.fechaHoraRecepcion",
                  filtro.getFechaInicioRecep(),
                  filtro.getFechaFinRecep())));
    }
    // se filtra por tipo de muestra
    if (filtro.getCodTipoMx() != null) {
      crit.add(
          Restrictions.and(
              Restrictions.eq(
                  "tomaMx.codTipoMx.idTipoMx", Integer.valueOf(filtro.getCodTipoMx()))));
    }
    // se filtra por area que procesa
    /*if (filtro.getIdAreaProcesa()!=null){
        crit.createAlias("orden.codExamen", "examen");
        crit.add( Restrictions.and(
                        Restrictions.eq("examen.area.idArea", Integer.valueOf(filtro.getIdAreaProcesa())))
        );
    }*/

    // Se filtra por rango de fecha de recepcion en laboratorio
    if (filtro.getFechaInicioRecepLab() != null && filtro.getFechaFinRecepLab() != null) {
      crit.add(
          Subqueries.propertyIn(
              "recepcion.idRecepcion",
              DetachedCriteria.forClass(RecepcionMxLab.class)
                  .createAlias("recepcionMx", "recepcionGral")
                  .add(
                      Restrictions.between(
                          "fechaHoraRecepcion",
                          filtro.getFechaInicioRecepLab(),
                          filtro.getFechaFinRecepLab()))
                  .setProjection(Property.forName("recepcionGral.idRecepcion"))));
    }

    if (filtro.getIncluirMxInadecuada() != null && filtro.getIncluirMxInadecuada()) {
      crit.add(
          Restrictions.or(Restrictions.isNull("recepcion.calidadMx.codigo"))
              .add(Restrictions.or(Restrictions.ne("recepcion.calidadMx.codigo", "CALIDMX|IDC"))));
    }
    if (filtro.getCodigoUnicoMx() != null) {
      crit.add(
          Restrictions.or(Restrictions.eq("tomaMx.codigoUnicoMx", filtro.getCodigoUnicoMx()))
              .add(
                  Restrictions.or(Restrictions.eq("tomaMx.codigoLab", filtro.getCodigoUnicoMx()))));
    }

    // se filtra por tipo de solicitud
    if (filtro.getCodTipoSolicitud() != null) {
      if (filtro.getCodTipoSolicitud().equals("Estudio")) {
        crit.add(
            Subqueries.propertyIn(
                "tomaMx.idTomaMx",
                DetachedCriteria.forClass(DaSolicitudEstudio.class)
                    .createAlias("idTomaMx", "toma")
                    .setProjection(Property.forName("toma.idTomaMx"))));
      } else {
        crit.add(
            Subqueries.propertyIn(
                "tomaMx.idTomaMx",
                DetachedCriteria.forClass(DaSolicitudDx.class)
                    .createAlias("idTomaMx", "toma")
                    .add(
                        Subqueries.propertyIn(
                            "labProcesa.codigo",
                            DetachedCriteria.forClass(AutoridadLaboratorio.class)
                                .createAlias("laboratorio", "labautorizado")
                                .createAlias("user", "usuario")
                                .add(
                                    Restrictions.eq(
                                        "pasivo", false)) // autoridad laboratorio activa
                                .add(
                                    Restrictions.and(
                                        Restrictions.eq(
                                            "usuario.username",
                                            filtro.getNombreUsuario()))) // usuario
                                .setProjection(Property.forName("labautorizado.codigo"))))
                    .setProjection(Property.forName("toma.idTomaMx"))));
      }
    }

    // nombre solicitud
    if (filtro.getNombreSolicitud() != null) {
      if (filtro.getCodTipoSolicitud() != null) {
        if (filtro.getCodTipoSolicitud().equals("Estudio")) {
          crit.add(
              Subqueries.propertyIn(
                  "solicitudtomaMx.idTomaMx",
                  DetachedCriteria.forClass(DaSolicitudEstudio.class)
                      .createAlias("tipoEstudio", "estudio")
                      .add(
                          Restrictions.ilike(
                              "estudio.nombre", "%" + filtro.getNombreSolicitud() + "%"))
                      .createAlias("idTomaMx", "toma")
                      .setProjection(Property.forName("toma.idTomaMx"))));
        } else {
          crit.add(
              Subqueries.propertyIn(
                  "tomaMx.idTomaMx",
                  DetachedCriteria.forClass(DaSolicitudDx.class)
                      .createAlias("codDx", "dx")
                      .add(Restrictions.ilike("dx.nombre", "%" + filtro.getNombreSolicitud() + "%"))
                      .createAlias("idTomaMx", "toma")
                      .setProjection(Property.forName("toma.idTomaMx"))));
        }
      } else {

        Junction conditGroup = Restrictions.disjunction();
        conditGroup
            .add(
                Subqueries.propertyIn(
                    "tomaMx.idTomaMx",
                    DetachedCriteria.forClass(DaSolicitudEstudio.class)
                        .createAlias("tipoEstudio", "estudio")
                        .add(
                            Restrictions.ilike(
                                "estudio.nombre", "%" + filtro.getNombreSolicitud() + "%"))
                        .createAlias("idTomaMx", "toma")
                        .setProjection(Property.forName("toma.idTomaMx"))))
            .add(
                Subqueries.propertyIn(
                    "tomaMx.idTomaMx",
                    DetachedCriteria.forClass(DaSolicitudDx.class)
                        .createAlias("codDx", "dx")
                        .add(
                            Restrictions.ilike(
                                "dx.nombre", "%" + filtro.getNombreSolicitud() + "%"))
                        .createAlias("idTomaMx", "toma")
                        .setProjection(Property.forName("toma.idTomaMx"))));

        crit.add(conditGroup);
      }
    }
    // se filtra que usuario tenga autorizado laboratorio al que se envio la muestra desde ALERTA
    /*if (filtro.getNombreUsuario()!=null) {
        crit.createAlias("tomaMx.envio","envioMx");
        crit.add(Subqueries.propertyIn("envioMx.laboratorioDestino.codigo", DetachedCriteria.forClass(AutoridadLaboratorio.class)
                .createAlias("laboratorio", "labautorizado")
                .createAlias("user", "usuario")
                .add(Restrictions.eq("pasivo",false)) //autoridad laboratorio activa
                .add(Restrictions.and(Restrictions.eq("usuario.username",filtro.getNombreUsuario()))) //usuario
                .setProjection(Property.forName("labautorizado.codigo"))));

    }*/
    if (filtro.getCodEstado() != null) {
      if (filtro
          .getCodEstado()
          .equalsIgnoreCase("ESTDMX|EPLAB")) { // significa que es recepción en laboratorio
        // Se filtra que el área a la que pertenece la solicitud este asociada al usuario
        // autenticado
        Junction conditGroup = Restrictions.disjunction();

        conditGroup
            .add(
                Subqueries.propertyIn(
                    "tomaMx.idTomaMx",
                    DetachedCriteria.forClass(DaSolicitudEstudio.class)
                        .createAlias("tipoEstudio", "estudio")
                        .createAlias("estudio.area", "area")
                        .add(
                            Subqueries.propertyIn(
                                "area.idArea",
                                DetachedCriteria.forClass(AutoridadArea.class)
                                    .add(Restrictions.eq("pasivo", false)) // autoridad area activa
                                    .add(
                                        Restrictions.and(
                                            Restrictions.eq(
                                                "user.username",
                                                filtro.getNombreUsuario()))) // usuario
                                    .setProjection(Property.forName("area.idArea"))))
                        .createAlias("idTomaMx", "toma")
                        .setProjection(Property.forName("idTomaMx.idTomaMx"))))
            .add(
                Subqueries.propertyIn(
                    "tomaMx.idTomaMx",
                    DetachedCriteria.forClass(DaSolicitudDx.class)
                        .createAlias("codDx", "dx")
                        .createAlias("dx.area", "area")
                        .add(
                            Subqueries.propertyIn(
                                "area.idArea",
                                DetachedCriteria.forClass(AutoridadArea.class)
                                    .add(Restrictions.eq("pasivo", false)) // autoridad area activa
                                    .add(
                                        Restrictions.and(
                                            Restrictions.eq(
                                                "user.username",
                                                filtro.getNombreUsuario()))) // usuario
                                    .setProjection(Property.forName("area.idArea"))))
                        .createAlias("idTomaMx", "toma")
                        .setProjection(Property.forName("toma.idTomaMx"))));

        crit.add(conditGroup);
      }
    }

    // filtro que las rutinas pertenezcan al laboratorio del usuario que consulta
    crit.createAlias("recepcion.labRecepcion", "labRecep");
    crit.add(
        Subqueries.propertyIn(
            "labRecep.codigo",
            DetachedCriteria.forClass(AutoridadLaboratorio.class)
                .createAlias("laboratorio", "labautorizado")
                .createAlias("user", "usuario")
                .add(Restrictions.eq("pasivo", false)) // autoridad laboratorio activa
                .add(
                    Restrictions.and(
                        Restrictions.eq("usuario.username", filtro.getNombreUsuario()))) // usuario
                .setProjection(Property.forName("labautorizado.codigo"))));

    // sólo la última recepción de cada muestra

    DetachedCriteria maxDateQuery = DetachedCriteria.forClass(RecepcionMx.class);
    maxDateQuery.createAlias("tomaMx", "mx");
    maxDateQuery.add(Restrictions.eqProperty("mx.idTomaMx", "tomaMx.idTomaMx"));
    maxDateQuery.setProjection(Projections.max("fechaHoraRecepcion"));
    crit.add(Property.forName("fechaHoraRecepcion").eq(maxDateQuery));

    // filtro sólo control calidad en el laboratio del usuario
    if (filtro.getControlCalidad() != null) {
      if (filtro
          .getControlCalidad()) { // si hay filtro por control de calidad y es "Si", sólo incluir
                                  // rutinas
        crit.add(
            Subqueries.propertyIn(
                "tomaMx.idTomaMx",
                DetachedCriteria.forClass(DaSolicitudDx.class)
                    .add(Restrictions.eq("controlCalidad", filtro.getControlCalidad()))
                    .createAlias("idTomaMx", "toma")
                    .add(
                        Subqueries.propertyIn(
                            "labProcesa.codigo",
                            DetachedCriteria.forClass(AutoridadLaboratorio.class)
                                .createAlias("laboratorio", "labautorizado")
                                .createAlias("user", "usuario")
                                .add(
                                    Restrictions.eq(
                                        "pasivo", false)) // autoridad laboratorio activa
                                .add(
                                    Restrictions.and(
                                        Restrictions.eq(
                                            "usuario.username",
                                            filtro.getNombreUsuario()))) // usuario
                                .setProjection(Property.forName("labautorizado.codigo"))))
                    .setProjection(Property.forName("toma.idTomaMx"))));
      } else { // si hay filtro por control de calidad y es "No", siempre incluir los estudios
        Junction conditGroup = Restrictions.disjunction();
        conditGroup
            .add(
                Subqueries.propertyIn(
                    "tomaMx.idTomaMx",
                    DetachedCriteria.forClass(DaSolicitudDx.class)
                        .add(Restrictions.eq("controlCalidad", filtro.getControlCalidad()))
                        .createAlias("idTomaMx", "toma")
                        .add(
                            Subqueries.propertyIn(
                                "labProcesa.codigo",
                                DetachedCriteria.forClass(AutoridadLaboratorio.class)
                                    .createAlias("laboratorio", "labautorizado")
                                    .createAlias("user", "usuario")
                                    .add(
                                        Restrictions.eq(
                                            "pasivo", false)) // autoridad laboratorio activa
                                    .add(
                                        Restrictions.and(
                                            Restrictions.eq(
                                                "usuario.username",
                                                filtro.getNombreUsuario()))) // usuario
                                    .setProjection(Property.forName("labautorizado.codigo"))))
                        .setProjection(Property.forName("toma.idTomaMx"))))
            .add(
                Restrictions.or(
                    Subqueries.propertyIn(
                        "tomaMx.idTomaMx",
                        DetachedCriteria.forClass(DaSolicitudEstudio.class)
                            .createAlias("idTomaMx", "idTomaMx")
                            .setProjection(Property.forName("idTomaMx.idTomaMx")))));
        crit.add(conditGroup);
      }
    }
    // filtro para sólo solicitudes aprobadas
    if (filtro.getSolicitudAprobada() != null) {
      Junction conditGroup = Restrictions.disjunction();
      conditGroup
          .add(
              Subqueries.propertyIn(
                  "tomaMx.idTomaMx",
                  DetachedCriteria.forClass(DaSolicitudEstudio.class)
                      .add(Restrictions.eq("aprobada", filtro.getSolicitudAprobada()))
                      .createAlias("idTomaMx", "toma")
                      .setProjection(Property.forName("toma.idTomaMx"))))
          .add(
              Subqueries.propertyIn(
                  "tomaMx.idTomaMx",
                  DetachedCriteria.forClass(DaSolicitudDx.class)
                      .add(Restrictions.eq("aprobada", filtro.getSolicitudAprobada()))
                      // .add(Restrictions.eq("controlCalidad",false)) ¿¿¿¿¿¿¿¿¿?????????????
                      .createAlias("idTomaMx", "toma")
                      .setProjection(Property.forName("toma.idTomaMx"))));

      crit.add(conditGroup);
    }

    return crit.list();
  }