Java Code Examples for org.hibernate.Query#setDate()

/**
 * Update the api_status/k8s_api_status table with latest number of apis
 * @param envId
 * @param compName
 * @param numOfApi
 * @param platform
 */
public static void updateApiDetails(final int envId, String compName, int numOfApi, String platform){
	final int compId = getComponentIdFromCompName(compName);
	final java.sql.Date todayDate = new java.sql.Date(Calendar.getInstance().getTimeInMillis());;
	if(compId == 0){
		logger.info("Component Name = " + compName + "; is not available in the DB");
		return;
	}
	String updateApiStsQry = null;
	if(platform.equalsIgnoreCase("Mesos")){
		updateApiStsQry = HQLConstants.UPDATE_API_DETAILS;
	}else if(platform.equalsIgnoreCase("K8s")){
		updateApiStsQry = HQLConstants.UPDATE_K8S_API_DETAILS;
	}
	Session session = HibernateConfig.getSessionFactory().getCurrentSession();
	Transaction txn = session.beginTransaction();
	Query query = session.createQuery(updateApiStsQry);
	query.setInteger("numOfApi", numOfApi);
	query.setLong("compId", compId);
	query.setLong("environmentId", envId);
	query.setDate("stsDate", todayDate);
	query.executeUpdate();
	txn.commit();
}
 

/**
 * Get all locations of the user's time sheet (not deleted ones) with modification date within last year.
 * @param searchString
 */
@SuppressWarnings("unchecked")
public List<String> getLocationAutocompletion(final String searchString)
{
  checkLoggedInUserSelectAccess();
  if (StringUtils.isBlank(searchString) == true) {
    return null;
  }
  final String s = "select distinct location from "
      + clazz.getSimpleName()
      + " t where deleted=false and t.user.id = ? and lastUpdate > ? and lower(t.location) like ?) order by t.location";
  final Query query = getSession().createQuery(s);
  query.setInteger(0, PFUserContext.getUser().getId());
  final DateHolder dh = new DateHolder();
  dh.add(Calendar.YEAR, -1);
  query.setDate(1, dh.getDate());
  query.setString(2, "%" + StringUtils.lowerCase(searchString) + "%");
  final List<String> list = query.list();
  return list;
}
 

public Set<Long> findClassLocationTable(int startSlot, int length, Vector<Date> dates) {
	String datesStr = "";
	for (int i=0; i<dates.size(); i++) {
		if (i>0) datesStr += ", ";
		datesStr += ":date"+i;
	}
	Query q = LocationDAO.getInstance().getSession()
	    .createQuery("select distinct e.clazz.uniqueId from " +
	    		"ClassEvent e inner join e.meetings m where " +
        		"m.locationPermanentId=:permanentId and " +
        		"m.stopPeriod>:startSlot and :endSlot>m.startPeriod and " + // meeting time within given time period
        		"m.meetingDate in ("+datesStr+")") // and date
        .setLong("permanentId",getPermanentId())
        .setInteger("startSlot", startSlot)
        .setInteger("endSlot", startSlot + length);
	for (int i=0; i<dates.size(); i++) {
		q.setDate("date"+i, dates.elementAt(i));
	}
	return new HashSet<Long>(q.setCacheable(true).list());
}
 

/**
 * Update the tps_latency_history/k8s_tps_latency_history table with tps & Latency values. 
 * 
 */
public static void updateTpsLatHistory(final int envId, int componentId, float tpsVaule, float latencyValue, String platform){
	final java.sql.Date todayDate = new java.sql.Date(Calendar.getInstance().getTimeInMillis());
	
	String queryStr = null;
	if(platform.equals("K8s")){
		queryStr = HQLConstants.UPDATE_K8S_TPS_LAT_HISTORY;
	}else if(platform.equals("Mesos")){
		queryStr = HQLConstants.UPDATE_MESOS_TPS_LAT_HISTORY;
	}else{
		return;
	}
	Session session = HibernateConfig.getSessionFactory().getCurrentSession();
	Transaction txn = session.beginTransaction();
	Query query = session.createQuery(queryStr);
	query.setFloat("tpsVaule", tpsVaule);
	query.setFloat("latencyValue", latencyValue);
	query.setLong("compId", componentId);
	query.setLong("environmentId", envId);
	query.setDate("statusDate", todayDate);
	query.executeUpdate();
	txn.commit();
}
 

/**
 * Update the Pods&Containers of the object other than deployment. 
 * @param environmentId
 * @param objName
 * @param podContList
 */
public static void updateObjPodss(int environmentId, String objName, ArrayList<Integer> podContList){
	final java.sql.Date todayDate = new java.sql.Date(Calendar.getInstance().getTimeInMillis());;
	
	Session session = HibernateConfig.getSessionFactory().getCurrentSession();
	Transaction txn = session.beginTransaction();
	String updateApiStsQry = null;
	updateApiStsQry = HQLConstants.UPDATE_K8S_OBJECT_PODS;
	
	Query query = session.createQuery(updateApiStsQry);
	query.setInteger("pods", podContList.get(0));
	query.setInteger("containers", podContList.get(1));
	query.setString("objName", objName);
	query.setLong("environmentId", environmentId);
	query.setDate("stsDate", todayDate);
	query.executeUpdate();
	txn.commit();
}
 

/**
 * Get all locations of the user's calendar events (not deleted ones) with modification date within last year.
 * @param searchString
 */
@SuppressWarnings("unchecked")
public List<String> getLocationAutocompletion(final String searchString, final TeamCalDO[] calendars)
{
  if (calendars == null || calendars.length == 0) {
    return null;
  }
  if (StringUtils.isBlank(searchString) == true) {
    return null;
  }
  checkLoggedInUserSelectAccess();
  final String s = "select distinct location from "
      + clazz.getSimpleName()
      + " t where deleted=false and t.calendar in :cals and lastUpdate > :lastUpdate and lower(t.location) like :location) order by t.location";
  final Query query = getSession().createQuery(s);
  query.setParameterList("cals", calendars);
  final DateHolder dh = new DateHolder();
  dh.add(Calendar.YEAR, -1);
  query.setDate("lastUpdate", dh.getDate());
  query.setString("location", "%" + StringUtils.lowerCase(searchString) + "%");
  final List<String> list = query.list();
  return list;
}
 

/**
 * Returns the list of all the child component_id for the given parent_component_id.
 * 
 * @param componentId
 * @return
 */
@SuppressWarnings("unchecked")
private Set<Integer> getAllChildWithStatusChange(int componentId, int regionId, int environmentId, Calendar histDate) {
    Session session = sessionFactory.openSession();
    Query query = session.createQuery(HQLConstants.QUERY_GET_CHILDREN_WITH_ERROR).setInteger("envId", environmentId)
            .setInteger("regId", regionId).setInteger("parentCompId", componentId);
    if(histDate == null){
    	query.setDate("statusChangeTime", getPreviousDayDate());
    }else{
    	query.setDate("statusChangeTime", histDate.getTime());
    }

    Set<Integer> childrenList = new HashSet<Integer>();
    for (ComponentEntity compObj : (List<ComponentEntity>) query.list()) {
        childrenList.add(compObj.getComponentId());
    }
    session.close();
    return childrenList;
}
 

/**
 * Only generic check access will be done. The matching entries will not be checked!
 * @param property Property of the data base entity.
 * @param searchString String the user has typed in.
 * @return All matching entries (like search) for the given property modified or updated in the last 2 years.
 */
@SuppressWarnings("unchecked")
public List<String> getAutocompletion(final String property, final String searchString)
{
  checkLoggedInUserSelectAccess();
  if (StringUtils.isBlank(searchString) == true) {
    return null;
  }
  final String hql = "select distinct "
      + property
      + " from "
      + clazz.getSimpleName()
      + " t where deleted=false and lastUpdate > ? and lower(t."
      + property
      + ") like ?) order by t."
      + property;
  final Query query = getSession().createQuery(hql);
  final DateHolder dh = new DateHolder();
  dh.add(Calendar.YEAR, -2); // Search only for entries of the last 2 years.
  query.setDate(0, dh.getDate());
  query.setString(1, "%" + StringUtils.lowerCase(searchString) + "%");
  final List<String> list = query.list();
  return list;
}
 

@Override
public void removeTemplates(JSONArray documents) throws EMFInternalError, JSONException {

	for (int i = 0; i < documents.length(); i++) {
		Session aSession = null;
		Transaction tx = null;
		try {
			aSession = getSession();
			tx = aSession.beginTransaction();
			// String hql = "select max(sot.prog) as maxprog from SbiObjTemplates sot where sot.sbiObject.biobjId="+biobjId;
			String hql = "delete from SbiObjTemplates where active=false and sbiObject.biobjId=? and creationDate<?";
			Query query = aSession.createQuery(hql);
			query.setInteger(0, documents.getJSONObject(i).getInt("id"));
			SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
			Date creationDate = formatter.parse(documents.getJSONObject(i).getString("data"));
			query.setDate(1, creationDate);
			query.executeUpdate();
			tx.commit();
		} catch (HibernateException | ParseException he) {
			logException(he);
			if (tx != null)
				tx.rollback();
			throw new EMFInternalError(EMFErrorSeverity.ERROR, "100");
		} finally {
			if (aSession != null) {
				if (aSession.isOpen())
					aSession.close();
			}
		}
	}
}
 

public static Hashtable<Long,Set<Long>> findConflictingStudents(Long classId, int startSlot, int length, List<Date> dates) {
	Hashtable<Long,Set<Long>> table = new Hashtable();
	if (dates.isEmpty()) return table;
	String datesStr = "";
	for (int i=0; i<dates.size(); i++) {
		if (i>0) datesStr += ", ";
		datesStr += ":date"+i;
	}
	Query q = LocationDAO.getInstance().getSession()
	    .createQuery("select distinct e.clazz.uniqueId, e.student.uniqueId "+
	        	"from StudentClassEnrollment e, ClassEvent c inner join c.meetings m, StudentClassEnrollment x "+
	        	"where x.clazz.uniqueId=:classId and x.student=e.student and " + // only look among students of the given class 
	        	"e.clazz=c.clazz and " + // link ClassEvent c with StudentClassEnrollment e
        		"m.stopPeriod>:startSlot and :endSlot>m.startPeriod and " + // meeting time within given time period
        		"m.meetingDate in ("+datesStr+") and m.approvalStatus = 1")
        .setLong("classId",classId)
        .setInteger("startSlot", startSlot)
        .setInteger("endSlot", startSlot + length);
	for (int i=0; i<dates.size(); i++) {
		q.setDate("date"+i, dates.get(i));
	}
    for (Iterator i = q.setCacheable(true).list().iterator();i.hasNext();) {
        Object[] o = (Object[])i.next();
        Set<Long> set = table.get((Long)o[0]);
        if (set==null) {
        	set = new HashSet<Long>();
        	table.put((Long)o[0], set);
        }
        set.add((Long)o[1]);
    }
    return table;
}
 

/**
 * Get all locations of the user's time sheet (not deleted ones) with modification date within last year.
 * @param maxResults Limit the result to the recent locations.
 * @return result as Json object.
 */
@SuppressWarnings("unchecked")
public Collection<String> getRecentLocation(final int maxResults)
{
  checkLoggedInUserSelectAccess();
  log.info("Get recent locations from the database.");
  final String s = "select location from "
      + (clazz.getSimpleName() + " t where deleted=false and t.user.id = ? and lastUpdate > ? and t.location != null and t.location != '' order by t.lastUpdate desc");
  final Query query = getSession().createQuery(s);
  query.setInteger(0, PFUserContext.getUser().getId());
  final DateHolder dh = new DateHolder();
  dh.add(Calendar.YEAR, -1);
  query.setDate(1, dh.getDate());
  final List<Object> list = query.list();
  int counter = 0;
  final List<String> res = new ArrayList<String>();
  for (final Object loc : list) {
    if (res.contains(loc) == true) {
      continue;
    }
    res.add((String) loc);
    if (++counter >= maxResults) {
      break;
    }
  }
  return res;
}
 

/**
 * Update the container_status table with latest number of apis
 * 
 */
public static void updateContainerDetails(final int envId, int compId, int numOfcont){
	final java.sql.Date todayDate = new java.sql.Date(Calendar.getInstance().getTimeInMillis());;

	Session session = HibernateConfig.getSessionFactory().getCurrentSession();
	Transaction txn = session.beginTransaction();
	Query query = session.createQuery(HQLConstants.UPDATE_CONTAINER_DETAILS);
	query.setInteger("numOfContainers", numOfcont);
	query.setLong("compId", compId);
	query.setLong("environmentId", envId);
	query.setDate("stsDate", todayDate);
	query.executeUpdate();
	txn.commit();
}
 

public int getResourceStatsRowCount(
		final String siteId,
		final String resourceAction, final List<String> resourceIds,
		final Date iDate, final Date fDate,
		final List<String> userIds,
		final boolean inverseUserSelection,
		final List<String> totalsBy) {

	StatsSqlBuilder sqlBuilder = new StatsSqlBuilder(getDbVendor(),
			Q_TYPE_RESOURCE, totalsBy, 
			siteId, (Set<String>)null, null, showAnonymousAccessEvents, resourceAction, resourceIds, 
			iDate, fDate, userIds, inverseUserSelection, null, true);
	final String hql = sqlBuilder.getHQL();

	HibernateCallback<Integer> hcb = session -> {
           Query q = session.createQuery(hql);
           if(siteId != null){
               q.setString("siteid", siteId);
           }
           if(userIds != null && !userIds.isEmpty()) {
               if(userIds.size() <= 1000) {
                   q.setParameterList("users", userIds);
               }else{
                   int nUsers = userIds.size();
                   int blockId = 0, startIndex = 0;
                   int blocks = (int) (nUsers / 1000);
                   blocks = (blocks*1000 == nUsers) ? blocks : blocks+1;
                   for(int i=0; i<blocks-1; i++) {
                       q.setParameterList("users"+blockId, userIds.subList(startIndex, startIndex+1000));
                       blockId++;
                       startIndex += 1000;
                   }
                   q.setParameterList("users"+blockId, userIds.subList(startIndex, nUsers));
               }
           }
           if(resourceAction != null)
               q.setString("action", resourceAction);
           if(resourceIds != null && !resourceIds.isEmpty())
               q.setParameterList("resources", resourceIds);
           if(iDate != null)
               q.setDate("idate", iDate);
           if(fDate != null){
               // adjust final date
               Calendar c = Calendar.getInstance();
               c.setTime(fDate);
               c.add(Calendar.DAY_OF_YEAR, 1);
               Date fDate2 = c.getTime();
               q.setDate("fdate", fDate2);
           }
           log.debug("getEventStatsRowCount(): " + q.getQueryString());
           Integer rowCount = q.list().size();
           if(!inverseUserSelection){
               return rowCount;
           }else{
               return getSiteUsers(siteId).size() - rowCount;
           }
       };
	return getHibernateTemplate().execute(hcb);
}
 

public void set(Query query, int pos, HashMap<NamedParameterValues, Object> namedParameterValuesCache) throws Exception {
	switch (type) {
		case CRITERION_VALUE:
			QueryUtil.setQueryParameterFromCriterion(propertyName, query, valueType, pos, criterion);
			break;
		case FILTER_VALUE:
			QueryUtil.setQueryParameterFromString(query, propertyClass, pos, value);
			break;
		case STATIC_TERM_VALUE:
			QueryUtil.setQueryParameterFromCriterion(null, query, valueType, pos, criterion);
			break;
		case TODAY:
			query.setDate(pos, getCachedTime(namedParameterValuesCache));
			break;
		case NOW:
			query.setTimestamp(pos, CommonUtil.dateToTimestamp(getCachedTime(namedParameterValuesCache)));
			break;
		case TODAY_PLUS_VARIABLE_PERIOD:
			if (VariablePeriod.EXPLICIT.equals(period)) {
				throw new IllegalArgumentException(L10nUtil.getMessage(MessageCodes.NAMED_PARAMETER_EXPLICIT_VARIABLE_PERIOD,
						DefaultMessages.NAMED_PARAMETER_EXPLICIT_VARIABLE_PERIOD));
			}
			query.setDate(pos, DateCalc.addInterval(getCachedTime(namedParameterValuesCache), period, null));
			break;
		case TODAY_MINUS_VARIABLE_PERIOD:
			if (VariablePeriod.EXPLICIT.equals(period)) {
				throw new IllegalArgumentException(L10nUtil.getMessage(MessageCodes.NAMED_PARAMETER_EXPLICIT_VARIABLE_PERIOD,
						DefaultMessages.NAMED_PARAMETER_EXPLICIT_VARIABLE_PERIOD));
			}
			query.setDate(pos, DateCalc.subInterval(getCachedTime(namedParameterValuesCache), period, null));
			break;
		case NOW_PLUS_VARIABLE_PERIOD:
			if (VariablePeriod.EXPLICIT.equals(period)) {
				throw new IllegalArgumentException(L10nUtil.getMessage(MessageCodes.NAMED_PARAMETER_EXPLICIT_VARIABLE_PERIOD,
						DefaultMessages.NAMED_PARAMETER_EXPLICIT_VARIABLE_PERIOD));
			}
			query.setTimestamp(pos, CommonUtil.dateToTimestamp(DateCalc.addInterval(getCachedTime(namedParameterValuesCache), period, null)));
			break;
		case NOW_MINUS_VARIABLE_PERIOD:
			if (VariablePeriod.EXPLICIT.equals(period)) {
				throw new IllegalArgumentException(L10nUtil.getMessage(MessageCodes.NAMED_PARAMETER_EXPLICIT_VARIABLE_PERIOD,
						DefaultMessages.NAMED_PARAMETER_EXPLICIT_VARIABLE_PERIOD));
			}
			query.setTimestamp(pos, CommonUtil.dateToTimestamp(DateCalc.subInterval(getCachedTime(namedParameterValuesCache), period, null)));
			break;
		case CONTEXT_USER_ID:
			query.setBigInteger(pos, new BigInteger(getCachedUser(namedParameterValuesCache).getId().toString()));
			break;
		case CONTEXT_USER_DEPARTMENT_ID:
			query.setBigInteger(pos, new BigInteger(getCachedUserDepartment(namedParameterValuesCache).getId().toString()));
			break;
		case CONTEXT_IDENTITY_ID:
			query.setBigInteger(pos, new BigInteger(getCachedIdentity(namedParameterValuesCache).getId().toString()));
			break;
		case CONTEXT_IDENTITY_DEPARTMENT_ID:
			query.setBigInteger(pos, new BigInteger(getCachedIdentityDepartment(namedParameterValuesCache).getId().toString()));
			break;
		default:
	}
}
 

public long getTotalSiteActivity(final String siteId, final List<String> events, final Date iDate, final Date fDate) {
	if(siteId == null){
		throw new IllegalArgumentException("Null siteId");
	}else{
		String iDateStr = "";
		String fDateStr = "";
		if(iDate != null)
			iDateStr = "and ss.date >= :idate ";
		if(fDate != null)
			fDateStr = "and ss.date < :fdate ";
		final String hql = "select sum(ss.count) " +
				"from SiteActivityImpl as ss " +
				"where ss.eventId in (:eventlist) " +
				"and ss.siteId = :siteid " +
				iDateStr + fDateStr +
				"group by ss.siteId";
		
		HibernateCallback<Long> hcb = session -> {
               Query q = session.createQuery(hql);
               q.setString("siteid", siteId);
               if(events != null && events.size() > 0)
                   q.setParameterList("eventlist", events);
               else
                   q.setParameterList("eventlist", eventRegistryService.getEventIds());
               if(iDate != null)
                   q.setDate("idate", iDate);
               if(fDate != null){
                   // adjust final date
                   Calendar c = Calendar.getInstance();
                   c.setTime(fDate);
                   c.add(Calendar.DAY_OF_YEAR, 1);
                   Date fDate2 = c.getTime();
                   q.setDate("fdate", fDate2);
               }
               List<Long> res = q.list();
               if(res.size() > 0) return res.get(0);
               else return 0L;
           };
		return getHibernateTemplate().execute(hcb);
	}
}
 

public static void setQueryParameterFromString(Query query, Class propertyClass, int pos, String value) throws Exception {
	if (propertyClass.equals(String.class)) {
		query.setString(pos, value);
	} else if (propertyClass.equals(Long.class)) {
		query.setBigInteger(pos, new BigInteger(value));
	} else if (propertyClass.equals(java.lang.Long.TYPE)) {
		query.setBigInteger(pos, new BigInteger(value));
	} else if (propertyClass.equals(Integer.class)) {
		query.setInteger(pos, new Integer(value));
	} else if (propertyClass.equals(java.lang.Integer.TYPE)) {
		query.setInteger(pos, new Integer(value));
	} else if (propertyClass.equals(Boolean.class)) {
		query.setBoolean(pos, new Boolean(value));
	} else if (propertyClass.equals(java.lang.Boolean.TYPE)) {
		query.setBoolean(pos, new Boolean(value));
	} else if (propertyClass.equals(Float.class)) {
		query.setFloat(pos, CommonUtil.parseFloat(value, CoreUtil.getUserContext().getDecimalSeparator()));
	} else if (propertyClass.equals(java.lang.Float.TYPE)) {
		query.setFloat(pos, CommonUtil.parseFloat(value, CoreUtil.getUserContext().getDecimalSeparator()));
	} else if (propertyClass.equals(Double.class)) {
		query.setDouble(pos, CommonUtil.parseDouble(value, CoreUtil.getUserContext().getDecimalSeparator()));
	} else if (propertyClass.equals(java.lang.Double.TYPE)) {
		query.setDouble(pos, CommonUtil.parseDouble(value, CoreUtil.getUserContext().getDecimalSeparator()));
	} else if (propertyClass.equals(Date.class)) {
		query.setDate(pos, CommonUtil.parseDate(value, CommonUtil.getInputDatePattern(CoreUtil.getUserContext().getDateFormat())));
	} else if (propertyClass.equals(Timestamp.class)) {
		query.setTimestamp(pos, CommonUtil.dateToTimestamp(CommonUtil.parseDate(value, CommonUtil.getInputDateTimePattern(CoreUtil.getUserContext().getDateFormat()))));
	} else if (propertyClass.equals(VariablePeriod.class)) {
		query.setString(pos, VariablePeriod.fromString(value).name());
	} else if (propertyClass.equals(AuthenticationType.class)) {
		query.setString(pos, AuthenticationType.fromString(value).name());
	} else if (propertyClass.equals(Sex.class)) {
		query.setString(pos, Sex.fromString(value).name());
	} else if (propertyClass.equals(RandomizationMode.class)) {
		query.setString(pos, RandomizationMode.fromString(value).name());
	} else if (propertyClass.equals(DBModule.class)) {
		query.setString(pos, DBModule.fromString(value).name());
	} else if (propertyClass.equals(HyperlinkModule.class)) {
		query.setString(pos, HyperlinkModule.fromString(value).name());
	} else if (propertyClass.equals(JournalModule.class)) {
		query.setString(pos, JournalModule.fromString(value).name());
	} else if (propertyClass.equals(FileModule.class)) {
		query.setString(pos, FileModule.fromString(value).name());
	} else if (propertyClass.equals(Color.class)) {
		query.setString(pos, Color.fromString(value).name());
	} else if (propertyClass.equals(InputFieldType.class)) {
		query.setString(pos, InputFieldType.fromString(value).name());
	} else if (propertyClass.equals(EventImportance.class)) {
		query.setString(pos, EventImportance.fromString(value).name());
	} else if (propertyClass.equals(JobStatus.class)) {
		query.setString(pos, JobStatus.fromString(value).name());
	} else if (propertyClass.isArray() && propertyClass.getComponentType().equals(java.lang.Byte.TYPE)) { // only string hashes supported, no boolean, float, etc...
		query.setBinary(pos, CryptoUtil.hashForSearch(value));
	} else {
		// illegal type...
		throw new IllegalArgumentException(MessageFormat.format(CommonUtil.INPUT_TYPE_NOT_SUPPORTED, propertyClass.toString()));
	}
}
 

public int getEventStatsRowCount(
		final String siteId,
		final List<String> events,
		final Date iDate, final Date fDate,
		final List<String> userIds,
		final boolean inverseUserSelection,
		final List<String> totalsBy) {
	
	final Set<String> anonymousEvents = eventRegistryService.getAnonymousEventIds();
	StatsSqlBuilder sqlBuilder = new StatsSqlBuilder(getDbVendor(),
			Q_TYPE_EVENT, totalsBy,
			siteId, events, anonymousEvents, showAnonymousAccessEvents, null, null, 
			iDate, fDate, userIds, inverseUserSelection, null, true);
	final String hql = sqlBuilder.getHQL();
	final Map<Integer,Integer> columnMap = sqlBuilder.getHQLColumnMap();

	// DO IT!
	HibernateCallback<Integer> hcb = session -> {
           Query q = session.createQuery(hql);
           if(siteId != null){
               q.setString("siteid", siteId);
           }
           if(events != null && !events.isEmpty()){
               q.setParameterList("events", events);
           }
           if(userIds != null && !userIds.isEmpty()) {
               if(userIds.size() <= 1000) {
                   q.setParameterList("users", userIds);
               }else{
                   int nUsers = userIds.size();
                   int blockId = 0, startIndex = 0;
                   int blocks = (int) (nUsers / 1000);
                   blocks = (blocks*1000 == nUsers) ? blocks : blocks+1;
                   for(int i=0; i<blocks-1; i++) {
                       q.setParameterList("users"+blockId, userIds.subList(startIndex, startIndex+1000));
                       blockId++;
                       startIndex += 1000;
                   }
                   q.setParameterList("users"+blockId, userIds.subList(startIndex, nUsers));
               }
           }
           if(iDate != null)
               q.setDate("idate", iDate);
           if(fDate != null){
               // adjust final date
               Calendar c = Calendar.getInstance();
               c.setTime(fDate);
               c.add(Calendar.DAY_OF_YEAR, 1);
               Date fDate2 = c.getTime();
               q.setDate("fdate", fDate2);
           }
           if(columnMap.containsKey(StatsSqlBuilder.C_USER) && anonymousEvents != null && anonymousEvents.size() > 0){
               q.setParameterList("anonymousEvents", anonymousEvents);
           }
           log.debug("getEventStatsRowCount(): " + q.getQueryString());
           Integer rowCount = q.list().size();
           if(!inverseUserSelection){
               return rowCount;
           }else{
               return getSiteUsers(siteId).size() - rowCount;
           }
       };
	return getHibernateTemplate().execute(hcb);
}
 

public int getEventStatsRowCount(
		final String siteId,
		final List<String> events,
		final Date iDate, final Date fDate,
		final List<String> userIds,
		final boolean inverseUserSelection,
		final List<String> totalsBy) {
	
	final Set<String> anonymousEvents = eventRegistryService.getAnonymousEventIds();
	StatsSqlBuilder sqlBuilder = new StatsSqlBuilder(getDbVendor(),
			Q_TYPE_EVENT, totalsBy,
			siteId, events, anonymousEvents, showAnonymousAccessEvents, null, null, 
			iDate, fDate, userIds, inverseUserSelection, null, true);
	final String hql = sqlBuilder.getHQL();
	final Map<Integer,Integer> columnMap = sqlBuilder.getHQLColumnMap();

	// DO IT!
	HibernateCallback<Integer> hcb = session -> {
           Query q = session.createQuery(hql);
           if(siteId != null){
               q.setString("siteid", siteId);
           }
           if(events != null && !events.isEmpty()){
               q.setParameterList("events", events);
           }
           if(userIds != null && !userIds.isEmpty()) {
               if(userIds.size() <= 1000) {
                   q.setParameterList("users", userIds);
               }else{
                   int nUsers = userIds.size();
                   int blockId = 0, startIndex = 0;
                   int blocks = (int) (nUsers / 1000);
                   blocks = (blocks*1000 == nUsers) ? blocks : blocks+1;
                   for(int i=0; i<blocks-1; i++) {
                       q.setParameterList("users"+blockId, userIds.subList(startIndex, startIndex+1000));
                       blockId++;
                       startIndex += 1000;
                   }
                   q.setParameterList("users"+blockId, userIds.subList(startIndex, nUsers));
               }
           }
           if(iDate != null)
               q.setDate("idate", iDate);
           if(fDate != null){
               // adjust final date
               Calendar c = Calendar.getInstance();
               c.setTime(fDate);
               c.add(Calendar.DAY_OF_YEAR, 1);
               Date fDate2 = c.getTime();
               q.setDate("fdate", fDate2);
           }
           if(columnMap.containsKey(StatsSqlBuilder.C_USER) && anonymousEvents != null && anonymousEvents.size() > 0){
               q.setParameterList("anonymousEvents", anonymousEvents);
           }
           log.debug("getEventStatsRowCount(): " + q.getQueryString());
           Integer rowCount = q.list().size();
           if(!inverseUserSelection){
               return rowCount;
           }else{
               return getSiteUsers(siteId).size() - rowCount;
           }
       };
	return getHibernateTemplate().execute(hcb);
}
 

public int getPresenceStatsRowCount(
		final String siteId,
		final Date iDate, final Date fDate,
		final List<String> userIds,
		final boolean inverseUserSelection,
		final List<String> totalsBy) {
	
	StatsSqlBuilder sqlBuilder = new StatsSqlBuilder(getDbVendor(),
			Q_TYPE_PRESENCE, totalsBy,
			null, (Set<String>)null, null, showAnonymousAccessEvents, null, null, 
			iDate, fDate, userIds, inverseUserSelection, null, true);
	final String hql = sqlBuilder.getHQL();

	// DO IT!
	HibernateCallback<Integer> hcb = session -> {
           Query q = session.createQuery(hql);
           if(siteId != null){
               q.setString("siteid", siteId);
           }
           if(userIds != null && !userIds.isEmpty()) {
               if(userIds.size() <= 1000) {
                   q.setParameterList("users", userIds);
               }else{
                   int nUsers = userIds.size();
                   int blockId = 0, startIndex = 0;
                   int blocks = (int) (nUsers / 1000);
                   blocks = (blocks*1000 == nUsers) ? blocks : blocks+1;
                   for(int i=0; i<blocks-1; i++) {
                       q.setParameterList("users"+blockId, userIds.subList(startIndex, startIndex+1000));
                       blockId++;
                       startIndex += 1000;
                   }
                   q.setParameterList("users"+blockId, userIds.subList(startIndex, nUsers));
               }
           }
           if(iDate != null)
               q.setDate("idate", iDate);
           if(fDate != null){
               // adjust final date
               Calendar c = Calendar.getInstance();
               c.setTime(fDate);
               c.add(Calendar.DAY_OF_YEAR, 1);
               Date fDate2 = c.getTime();
               q.setDate("fdate", fDate2);
           }
           log.debug("getPresenceStatsRowCount(): " + q.getQueryString());
           Integer rowCount = q.list().size();
           if(!inverseUserSelection){
               return rowCount;
           }else{
               return getSiteUsers(siteId).size() - rowCount;
           }
       };
	return getHibernateTemplate().execute(hcb);
}
 

public int getResourceStatsRowCount(
		final String siteId,
		final String resourceAction, final List<String> resourceIds,
		final Date iDate, final Date fDate,
		final List<String> userIds,
		final boolean inverseUserSelection,
		final List<String> totalsBy) {

	StatsSqlBuilder sqlBuilder = new StatsSqlBuilder(getDbVendor(),
			Q_TYPE_RESOURCE, totalsBy, 
			siteId, (Set<String>)null, null, showAnonymousAccessEvents, resourceAction, resourceIds, 
			iDate, fDate, userIds, inverseUserSelection, null, true);
	final String hql = sqlBuilder.getHQL();

	HibernateCallback<Integer> hcb = session -> {
           Query q = session.createQuery(hql);
           if(siteId != null){
               q.setString("siteid", siteId);
           }
           if(userIds != null && !userIds.isEmpty()) {
               if(userIds.size() <= 1000) {
                   q.setParameterList("users", userIds);
               }else{
                   int nUsers = userIds.size();
                   int blockId = 0, startIndex = 0;
                   int blocks = (int) (nUsers / 1000);
                   blocks = (blocks*1000 == nUsers) ? blocks : blocks+1;
                   for(int i=0; i<blocks-1; i++) {
                       q.setParameterList("users"+blockId, userIds.subList(startIndex, startIndex+1000));
                       blockId++;
                       startIndex += 1000;
                   }
                   q.setParameterList("users"+blockId, userIds.subList(startIndex, nUsers));
               }
           }
           if(resourceAction != null)
               q.setString("action", resourceAction);
           if(resourceIds != null && !resourceIds.isEmpty())
               q.setParameterList("resources", resourceIds);
           if(iDate != null)
               q.setDate("idate", iDate);
           if(fDate != null){
               // adjust final date
               Calendar c = Calendar.getInstance();
               c.setTime(fDate);
               c.add(Calendar.DAY_OF_YEAR, 1);
               Date fDate2 = c.getTime();
               q.setDate("fdate", fDate2);
           }
           log.debug("getEventStatsRowCount(): " + q.getQueryString());
           Integer rowCount = q.list().size();
           if(!inverseUserSelection){
               return rowCount;
           }else{
               return getSiteUsers(siteId).size() - rowCount;
           }
       };
	return getHibernateTemplate().execute(hcb);
}