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; }
@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; }
/** * 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; }
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; }
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() + "'"; 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 ";
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() ) + "' ";
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() + " (";
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" : "";
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"; hql += hlp.whereAnd() + ouClause; hql += hlp.whereAnd() + "tei.organisationUnit.uid in (" + getQuotedCommaDelimitedString( getUids( params.getOrganisationUnits() ) ) + ")";
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";
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 ";
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 "; hql += hlp.whereAnd() + " uc.passwordLastUpdated < :passwordLastUpdated ";
sql += hlp.whereAnd() + " tei.trackedentityinstanceid=" + params.getTrackedEntityInstance().getId() + " "; sql += hlp.whereAnd() + " p.programid = " + params.getProgram().getId() + " "; sql += hlp.whereAnd() + " ps.programstageid = " + params.getProgramStage().getId() + " "; sql += hlp.whereAnd() + " pi.status = '" + params.getProgramStatus() + "' "; sql += hlp.whereAnd() + " pi.followup is " + (params.getFollowUp() ? "true" : "false") + " "; sql += hlp.whereAnd() + " psi.lastupdated >= '" + DateUtils.getLongDateString( params.getLastUpdatedStartDate() ) + "' "; sql += hlp.whereAnd() + " psi.lastupdated < '" + DateUtils.getLongDateString( dateAfterEndDate ) + "' "; sql += hlp.whereAnd() + " psi.attributeoptioncomboid = " + params.getCategoryOptionCombo().getId() + " "; sql += hlp.whereAnd() + " psi.organisationunitid in (" + getCommaDelimitedString( orgUnitIds ) + ") "; 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( dateAfterEndDate ) + "' " + "or (psi.executiondate is null and psi.duedate < '" + getMediumDateString( dateAfterEndDate ) + "')) "; sql += hlp.whereAnd() + " p.type = '" + params.getProgramType() + "' ";
sql += sqlHelper.whereAnd() + " " + statementBuilder.getBoundaryCondition( boundary, params.getProgramIndicator(), params.getEarliestStartDate(), params.getLatestEndDate() ) + " "; sql += sqlHelper.whereAnd() + " " + timeCol + " >= '" + getMediumDateString( params.getStartDate() ) + "' "; sql += sqlHelper.whereAnd() + " " + timeCol + " <= '" + getMediumDateString( params.getEndDate() ) + "' "; sql += sqlHelper.whereAnd() + " " + quote( alias, params.getPeriodType().toLowerCase() ) + " in (" + getQuotedCommaDelimitedString( getUids( params.getDimensionOrFilterItems( PERIOD_DIM_ID ) ) ) + ") "; sql += sqlHelper.whereAnd() + " " + orgUnitCol + " in (" + getQuotedCommaDelimitedString( getUids( params.getDimensionOrFilterItems( ORGUNIT_DIM_ID ) ) ) + ") "; sql += sqlHelper.whereAnd() + " " + orgUnitCol + " in (" + getQuotedCommaDelimitedString( getUids( params.getOrganisationUnitChildren() ) ) + ") "; sql += sqlHelper.whereAnd() + " ("; sql += sqlHelper.whereAnd() + " " + col + " in (" + getQuotedCommaDelimitedString( getUids( dim.getItems() ) ) + ") "; sql += sqlHelper.whereAnd() + " " + quoteAlias( "ps" ) + " = '" + params.getProgramStage().getUid() + "' "; sql += sqlHelper.whereAnd() + " " + getSelectSql( item, params.getEarliestStartDate(), params.getLatestEndDate() ) + " " + filter.getSqlOperator() + " " + getSqlFilter( filter, item ) + " "; sql += sqlHelper.whereAnd() + " " + getSelectSql( item, params.getEarliestStartDate(), params.getLatestEndDate() ) + " " + filter.getSqlOperator() + " " + getSqlFilter( filter, item ) + " "; sql += sqlHelper.whereAnd() + " (" + sqlFilter + ") "; sql += sqlHelper.whereAnd() + " (" + anyValueFilter + ") "; sql += sqlHelper.whereAnd() + " pistatus = '" + params.getProgramStatus().name() + "' ";
sql += sqlHelper.whereAnd() + " " + col + " in (" + getQuotedCommaDelimitedString( getUids( dim.getItems() ) ) + ") "; sql += sqlHelper.whereAnd() + " ( "; sql += sqlHelper.whereAnd() + " ( "; sql += sqlHelper.whereAnd() + " (" + "(" + quoteAlias( "ouopeningdate" ) + " <= '" + getMediumDateString( params.getStartDateRestriction() ) + "' or " + quoteAlias( "ouopeningdate" ) + " is null) and " + "(" + quoteAlias( "oucloseddate" ) + " >= '" + getMediumDateString( params.getEndDateRestriction() ) + "' or " + quoteAlias( "oucloseddate" ) + " is null)) "; sql += sqlHelper.whereAnd() + " (" + "(" + quoteAlias( "costartdate" ) + " <= '" + getMediumDateString( params.getStartDateRestriction() ) + "' or " + quoteAlias( "costartdate" ) + " is null) and " + "(" + quoteAlias( "coenddate" ) + " >= '" + getMediumDateString( params.getEndDateRestriction() ) + "' or " + quoteAlias( "coenddate" ) + " is null)) "; sql += sqlHelper.whereAnd() + " " + quoteAlias( "pestartdate" ) + " >= '" + getMediumDateString( params.getStartDate() ) + "' and " + quoteAlias( "peenddate" ) + " <= '" + getMediumDateString( params.getEndDate() ) + "' "; sql += sqlHelper.whereAnd() + " " + quoteAlias( "timely" ) + " is true "; sql += sqlHelper.whereAnd() + " " + quoteAlias( "year" ) + " in (" + TextUtils.getCommaDelimitedString( params.getPartitions().getPartitions() ) + ") "; sql += sqlHelper.whereAnd() + " " + quoteAlias( "pe_rank" ) + " = 1 ";
sql += sqlHelper.whereAnd() + " enrollmentdate >= '" + getMediumDateString( params.getStartDate() ) + "' "; sql += "and enrollmentdate <= '" + getMediumDateString( params.getEndDate() ) + "' "; sql += sqlHelper.whereAnd() + " " + quote( ANALYTICS_TBL_ALIAS, params.getPeriodType().toLowerCase() ) + " in (" + getQuotedCommaDelimitedString( getUids( params.getDimensionOrFilterItems( PERIOD_DIM_ID ) ) ) + ") "; sql += sqlHelper.whereAnd() + " ou in (" + getQuotedCommaDelimitedString( getUids( params.getDimensionOrFilterItems( ORGUNIT_DIM_ID ) ) ) + ") "; sql += sqlHelper.whereAnd() + " ou in (" + getQuotedCommaDelimitedString( getUids( params.getOrganisationUnitChildren() ) ) + ") "; sql += sqlHelper.whereAnd() + " (";
sql += hlp.whereAnd() + " tei.trackedentitytypeid = " + params.getTrackedEntityType().getId() + " "; 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 ";