@Override public String getCriteriaSqlClause(Map<String, String> criteria, SqlHelper sqlHelper ) { String sql = StringUtils.EMPTY; if ( criteria != null && !criteria.isEmpty() ) { sqlHelper = ObjectUtils.firstNonNull( sqlHelper, new SqlHelper() ); for ( String filter : criteria.keySet() ) { sql += sqlHelper.whereAnd() + " " + statementBuilder.columnQuote( filter ) + "='" + criteria.get( filter ) + "' "; } } return sql; }
/** * Returns a having clause restricting the result based on the measure criteria. */ private String getMeasureCriteriaSql( DataQueryParams params ) { SqlHelper sqlHelper = new SqlHelper(); String sql = " "; for ( MeasureFilter filter : params.getMeasureCriteria().keySet() ) { Double criterion = params.getMeasureCriteria().get( filter ); sql += sqlHelper.havingAnd() + " " + getNumericValueColumn( params ) + " " + OPERATOR_SQL_MAP.get( filter ) + " " + criterion + " "; } return sql; }
private String buildProgramInstanceHql( ProgramInstanceQueryParams params ) SqlHelper hlp = new SqlHelper( true ); hql += hlp.whereAnd() + "pi.lastUpdated >= '" + getMediumDateString( params.getLastUpdated() ) + "'"; hql += hlp.whereAnd() + "pi.entityInstance.uid = '" + params.getTrackedEntityInstance().getUid() + "'"; hql += hlp.whereAnd() + "pi.entityInstance.trackedEntityType.uid = '" + params.getTrackedEntityType().getUid() + "'"; SqlHelper orHlp = new SqlHelper( true ); ouClause += orHlp.or() + "pi.organisationUnit.path LIKE '" + organisationUnit.getPath() + "%'"; hql += hlp.whereAnd() + ouClause; hql += hlp.whereAnd() + "pi.organisationUnit.uid in (" + getQuotedCommaDelimitedString( getUids( params.getOrganisationUnits() ) ) + ")"; hql += hlp.whereAnd() + "pi.program.uid = '" + params.getProgram().getUid() + "'"; hql += hlp.whereAnd() + "pi.status = '" + params.getProgramStatus() + "'"; hql += hlp.whereAnd() + "pi.followup = " + params.getFollowUp(); hql += hlp.whereAnd() + "pi.enrollmentDate >= '" + getMediumDateString( params.getProgramStartDate() ) + "'"; hql += hlp.whereAnd() + "pi.enrollmentDate <= '" + getMediumDateString( params.getProgramEndDate() ) + "'"; hql += hlp.whereAnd() + " pi.deleted is false ";
@Override public int getTrackedEntityInstanceCount( TrackedEntityInstanceQueryParams params ) { SqlHelper hlp = new SqlHelper(); // --------------------------------------------------------------------- // Select clause // --------------------------------------------------------------------- String sql = "select count(tei.uid) as " + TRACKED_ENTITY_INSTANCE_ID + " "; // --------------------------------------------------------------------- // From and where clause // --------------------------------------------------------------------- sql += getFromWhereClause( params, hlp ); // --------------------------------------------------------------------- // Query // --------------------------------------------------------------------- Integer count = jdbcTemplate.queryForObject( sql, Integer.class ); log.debug( "Tracked entity instance count SQL: " + sql ); return count; }
private String getFilterQuery( SqlHelper sqlHelper, String columnName, String operator, String value ) { String query = StringUtils.EMPTY; query += sqlHelper.whereAnd() + " " + columnName + " " + QueryUtils.parseFilterOperator( operator, value ); return query; }
SqlHelper hlp = new SqlHelper( true ); hql += hlp.whereAnd() + " pi.program.uid = '" + params.getProgram().getUid() + "'"; hql += hlp.whereAnd() + "pi.status = '" + params.getProgramStatus() + "'"; hql += hlp.whereAnd() + "pi.followup = " + params.getFollowUp(); hql += hlp.whereAnd() + "pi.enrollmentDate >= '" + getMediumDateString( params.getProgramEnrollmentStartDate() ) + "'"; hql += hlp.whereAnd() + "pi.enrollmentDate < '" + getMediumDateString( params.getProgramEnrollmentEndDate() ) + "'"; hql += hlp.whereAnd() + "pi.incidentDate >= '" + getMediumDateString( params.getProgramIncidentStartDate() ) + "'"; hql += hlp.whereAnd() + "pi.incidentDate < '" + getMediumDateString( params.getProgramIncidentEndDate() ) + "'"; hql += hlp.whereAnd() + "pi.deleted is false "; hql += hlp.whereAnd() + " attr.skipSynchronization = false"; hql += hlp.whereAnd() + "tei.trackedEntityType.uid='" + params.getTrackedEntityType().getUid() + "'"; hql += hlp.whereAnd() + "tei.lastUpdated >= '" + getMediumDateString( params.getLastUpdatedStartDate() ) + "'"; hql += hlp.whereAnd() + "tei.lastUpdated < '" + getMediumDateString( getDateAfterAddition( params.getLastUpdatedEndDate(), 1 ) ) + "'"; hql += hlp.whereAnd() + "tei.lastUpdated > tei.lastSynchronized"; SqlHelper orHlp = new SqlHelper( true );
private String getSqlForView( Grid grid, SqlView sqlView, Map<String, String> criteria, List<String> filters, List<String> fields ) { String sql = "select " + QueryUtils.parseSelectFields( fields ) + " from " + statementBuilder.columnQuote( sqlView.getViewName() ) + " "; boolean hasCriteria = criteria != null && !criteria.isEmpty(); boolean hasFilter = filters != null && !filters.isEmpty(); if ( hasCriteria || hasFilter ) { SqlHelper sqlHelper = new SqlHelper(); if ( hasCriteria ) { sql += getCriteriaSqlClause( criteria, sqlHelper ); } if ( hasFilter ) { sql += parseFilters( filters, sqlHelper ); } } return sql; }
public String getBoundaryCondition( ProgramIndicator programIndicator, Date reportingStartDate, Date reportingEndDate, SqlHelper sqlHelper ) { String sql = ""; for ( AnalyticsPeriodBoundary boundary : programIndicator.getAnalyticsPeriodBoundaries() ) { if ( boundary.isCohortDateBoundary() && !boundary.isEnrollmentHavingEventDateCohortBoundary() ) { sql += sqlHelper.whereAnd() + " " + getBoundaryCondition( boundary, programIndicator, reportingStartDate, reportingEndDate ); } } if ( programIndicator.hasEventDateCohortBoundary() ) { sql += sqlHelper.whereAnd() + " " + getProgramIndicatorEventInProgramStageSql( programIndicator, reportingStartDate, reportingEndDate ); } return sql; }
/** * Generates a sub query which provides a filtered view of the data according * to the criteria. If not, returns the full view of the partition. */ private String getPreMeasureCriteriaSubquerySql( DataQueryParams params ) { SqlHelper sqlHelper = new SqlHelper(); String fromSourceClause = getFromSourceClause( params ) + " as " + ANALYTICS_TBL_ALIAS; String sql = "(select * from " + fromSourceClause + " "; for ( MeasureFilter filter : params.getPreAggregateMeasureCriteria().keySet() ) { Double criterion = params.getPreAggregateMeasureCriteria().get( filter ); sql += sqlHelper.whereAnd() + " value " + OPERATOR_SQL_MAP.get( filter ) + " " + criterion + " "; } sql += ")"; return sql; }
sql += hlp.whereAnd() + " tei.trackedentitytypeid = " + params.getTrackedEntityType().getId() + " "; SqlHelper orHlp = new SqlHelper( true ); ouClause += orHlp.or() + "ou.path like '" + organisationUnit.getPath() + "%'"; sql += hlp.whereAnd() + ouClause; sql += hlp.whereAnd() + " tei.organisationunitid in (" + getCommaDelimitedString( getIdentifiers( params.getOrganisationUnits() ) ) + ") "; final String end = params.getQuery().isOperator( QueryOperator.LIKE ) ? anyChar : wordEnd; sql += hlp.whereAnd() + " ("; sql += hlp.whereAnd() + " tei.deleted is false ";
private String getSqlForQuery( Grid grid, SqlView sqlView, Map<String, String> criteria, Map<String, String> variables, List<String> filters, List<String> fields ) { boolean hasCriteria = criteria != null && !criteria.isEmpty(); boolean hasFilter = filters != null && !filters.isEmpty(); String sql = SqlViewUtils.substituteSqlVariables( sqlView.getSqlQuery(), variables ); if ( hasCriteria || hasFilter ) { sql = SqlViewUtils.removeQuerySeparator( sql ); String outerSql = "select " + QueryUtils.parseSelectFields( fields ) + " from " + "(" + sql + ") as qry "; SqlHelper sqlHelper = new SqlHelper(); if ( hasCriteria ) { outerSql += getCriteriaSqlClause( criteria, sqlHelper ); } if ( hasFilter ) { outerSql += parseFilters( filters, sqlHelper ); } sql = outerSql; } return sql; }
sql += hlp.whereAnd() + " psi.organisationunitid in (" + getCommaDelimitedString( getIdentifiers( organisationUnits ) ) + ") "; sql += hlp.whereAnd() + " ps.programstageid = " + params.getProgramStage().getId() + " "; sql += hlp.whereAnd() + " psi.attributeoptioncomboid = " + params.getCategoryOptionCombo().getId() + " "; sql += hlp.whereAnd() + " (psi.executiondate >= '" + getMediumDateString( params.getStartDate() ) + "' " + "or (psi.executiondate is null and psi.duedate >= '" + getMediumDateString( params.getStartDate() ) + "')) "; sql += hlp.whereAnd() + " (psi.executiondate <= '" + getMediumDateString( params.getEndDate() ) + "' " + "or (psi.executiondate is null and psi.duedate <= '" + getMediumDateString( params.getEndDate() ) + "')) "; sql += hlp.whereAnd() + " psi.lastupdated >= '" + DateUtils.getLongDateString( params.getLastUpdatedStartDate() ) + "' "; sql += hlp.whereAnd() + " psi.lastupdated <= '" + DateUtils.getLongDateString( params.getLastUpdatedEndDate() ) + "' "; sql += hlp.whereAnd() + " psi.duedate is not null and psi.duedate >= '" + DateUtils.getLongDateString( params.getDueDateStart() ) + "' "; sql += hlp.whereAnd() + " psi.duedate is not null and psi.duedate <= '" + DateUtils.getLongDateString( params.getDueDateEnd() ) + "' "; sql += hlp.whereAnd() + " psi.deleted is false ";
@Override public Map<String, Set<String>> getOrganisationUnitDataSetAssocationMap( Collection<OrganisationUnit> organisationUnits, Collection<DataSet> dataSets ) SqlHelper hlp = new SqlHelper(); sql += hlp.whereAnd() + " ("; sql += hlp.whereAnd() + " ds.datasetid in (" + StringUtils.join( IdentifiableObjectUtils.getIdentifiers( dataSets ), "," ) + ") ";
@Override public List<Map<String, String>> getTrackedEntityInstancesGrid( TrackedEntityInstanceQueryParams params ) SqlHelper hlp = new SqlHelper();
.collect( Collectors.toList() ); SqlHelper sqlHelper = new SqlHelper(); sql += sqlHelper.whereAnd() + " ("; sql += sqlHelper.whereAnd() + " " + col + " in (" + getQuotedCommaDelimitedString( getUids( dim.getItems() ) ) + ") "; sql += sqlHelper.whereAnd() + " " + "ps.startdate >= '" + DateUtils.getMediumDateString( params.getStartDate() ) + "' and " + "ps.enddate <= '" + DateUtils.getMediumDateString( params.getEndDate() ) + "' ";
private String buildGridSql( EventSearchParams params, List<OrganisationUnit> organisationUnits ) SqlHelper hlp = new SqlHelper();
@Override public List<DataApprovalAudit> getDataApprovalAudits( DataApprovalAuditQueryParams params ) SqlHelper hlp = new SqlHelper(); hql += hlp.whereAnd() + " a.workflow.uid in (" + getQuotedCommaDelimitedString( getUids( params.getWorkflows() ) ) + ") "; hql += hlp.whereAnd() + " a.level.uid in (" + getQuotedCommaDelimitedString( getUids( params.getLevels() ) ) + ") "; hql += hlp.whereAnd() + " a.organisationUnit.uid in (" + getQuotedCommaDelimitedString( getUids( params.getOrganisationUnits() ) ) + ") "; hql += hlp.whereAnd() + " a.attributeOptionCombo.uid in (" + getQuotedCommaDelimitedString( getUids( params.getAttributeOptionCombos() ) ) + ") "; hql += hlp.whereAnd() + " a.period.startDate >= '" + getMediumDateString( params.getStartDate() ) + "' "; hql += hlp.whereAnd() + " a.period.endDate <= '" + getMediumDateString( params.getEndDate() ) + "' "; hql += hlp.whereAnd() + " (";
private Query<ProgramMessage> getHqlQuery( ProgramMessageQueryParams params ) SqlHelper helper = new SqlHelper( true ); hql += helper.whereAnd() + "pm.programInstance = :programInstance"; hql += helper.whereAnd() + "pm.programStageInstance = :programStageInstance"; ? helper.whereAnd() + "pm.messageStatus = :messageStatus" : ""; hql += params.getAfterDate() != null ? helper.whereAnd() + "pm.processeddate > :processeddate" : "" ; ? helper.whereAnd() + "pm.processeddate < :processeddate" : "";
@Override public List<DeflatedDataValue> getDeflatedDataValues( DataExportParams params ) SqlHelper sqlHelper = new SqlHelper( true ); where += sqlHelper.whereAnd() + "dv.dataelementid in (" + dataElementIdList + ")"; where += sqlHelper.whereAnd() + "dv.periodid in (" + periodIdList + ")"; where += sqlHelper.whereAnd() + "pt.periodtypeid in (" + periodTypeIdList + ")"; where += sqlHelper.whereAnd() + "p.startdate >= '" + DateUtils.getMediumDateString( params.getStartDate() ) + "'" + " and p.enddate <= '" + DateUtils.getMediumDateString( params.getStartDate() ) + "'"; where += sqlHelper.whereAnd() + "p.startdate <= '" + DateUtils.getMediumDateString( params.getIncludedDate() ) + "'" + " and p.enddate >= '" + DateUtils.getMediumDateString( params.getIncludedDate() ) + "'"; where += sqlHelper.whereAnd() + "dv.sourceid in (" + orgUnitIdList + ")"; where += sqlHelper.whereAnd() + "dv.attributeoptioncomboid in (" + aocIdList + ")"; where += sqlHelper.whereAnd() + "cc.categoryoptionid in (" + coDimConstraintsList + ") "; where += sqlHelper.whereAnd() + "cogm.categoryoptiongroupid in (" + cogDimConstraintsList + ")"; where += sqlHelper.whereAnd() + "dv.lastupdated >= " + DateUtils.getMediumDateString( params.getLastUpdated() ); where += sqlHelper.whereAnd() + "dv.deleted is false";
SqlHelper hlp = new SqlHelper(); hql += hlp.whereAnd() + " ("; hql += hlp.whereAnd() + " ou.id in (:ouIds) "; hql += hlp.whereAnd() + " uc.disabled = :disabled "; hql += hlp.whereAnd() + " uc.secret is null "; hql += hlp.whereAnd() + " (" + "lower(u.firstName) like :key " + "or lower(u.email) like :key " + hql += hlp.whereAnd() + " u.phoneNumber = :phoneNumber "; hql += hlp.whereAnd() + " g.id in (:ids) "; hql += hlp.whereAnd() + " not exists (" + "select uc2 from UserCredentials uc2 " + "inner join uc2.userAuthorityGroups ag2 " + hql += hlp.whereAnd() + " not exists (" + "select uc3 from UserCredentials uc3 " + "inner join uc3.userAuthorityGroups ag3 " + hql += hlp.whereAnd() + " uc.lastLogin >= :lastLogin "; hql += hlp.whereAnd() + " uc.lastLogin < :inactiveSince ";