public String getProgramIndicatorColumnSelectSql( String programStageUid, String columnName, Date reportingStartDate, Date reportingEndDate, ProgramIndicator programIndicator ) { if ( programIndicator.getAnalyticsType().equals( AnalyticsType.ENROLLMENT ) ) { String eventTableName = "analytics_event_" + programIndicator.getProgram().getUid(); return "(select " + columnName + " from " + eventTableName + " where " + eventTableName + ".pi = " + ANALYTICS_TBL_ALIAS + ".pi and " + columnName + " is not null " + ( programIndicator.getEndEventBoundary() != null ? ("and " + getBoundaryCondition( programIndicator.getEndEventBoundary(), programIndicator, reportingStartDate, reportingEndDate ) + " ") : "" ) + ( programIndicator.getStartEventBoundary() != null ? ( "and " + getBoundaryCondition( programIndicator.getStartEventBoundary(), programIndicator, reportingStartDate, reportingEndDate ) + " ") : "" ) + "and ps = '" + programStageUid + "' " + "order by executiondate " + "desc limit 1 )"; } else { return columnName; } }
public String countWhereCondition( ProgramIndicator programIndicator, StatementBuilder sb, Date reportingStartDate, Date reportingEndDate, String element, String condition ) { Matcher matcher = COHORT_HAVING_DATA_ELEMENT_PATTERN.matcher( element ); if ( matcher.find() ) { String ps = matcher.group( PROGRAM_STAGE_REGEX_GROUP ); String de = matcher.group( DATA_ELEMENT_REGEX_GROUP ); String eventTableName = "analytics_event_" + programIndicator.getProgram().getUid(); String columnName = "\"" + de + "\""; return "(select count(" + columnName + ") from " + eventTableName + " where " + eventTableName + ".pi = " + StatementBuilder.ANALYTICS_TBL_ALIAS + ".pi and " + columnName + " is not null " + " and " + columnName + condition + " " + (programIndicator.getEndEventBoundary() != null ? ("and " + sb.getBoundaryCondition( programIndicator.getEndEventBoundary(), programIndicator, reportingStartDate, reportingEndDate ) + " ") : "") + (programIndicator.getStartEventBoundary() != null ? ("and " + sb.getBoundaryCondition( programIndicator.getStartEventBoundary(), programIndicator, reportingStartDate, reportingEndDate ) + " ") : "") + "and ps = '" + ps + "')"; } else { throw new IllegalArgumentException( "No data element found in argument 1:" + element + " in " + BaseCountIfProgramIndicatorFunction.KEY + " for program indciator:" + programIndicator.getUid() ); } }
@Override public Map<ProgramIndicator, String> getInvalidProgramIndicatorExpressions() { Map<ProgramIndicator, String> invalidExpressions = new HashMap<>(); invalidExpressions = programIndicatorService.getAllProgramIndicators().stream() .filter( pi -> ! ProgramIndicator.VALID.equals( programIndicatorService.expressionIsValid( pi.getExpression() ) ) ) .collect( Collectors.toMap( pi -> pi, pi -> pi.getExpression() ) ); return invalidExpressions; }
/** * Indicates whether the program indicator includes event boundaries, to be applied if the program indicator queries event data. */ public Boolean hasEventBoundary() { return getEndEventBoundary() != null || getStartEventBoundary() != null; }
@Override public Map<ProgramIndicator, String> getInvalidProgramIndicatorFilters() { Map<ProgramIndicator, String> invalidFilters = new HashMap<>(); invalidFilters = programIndicatorService.getAllProgramIndicators().stream() .filter( pi -> ( ! ( pi.hasFilter() ? ProgramIndicator.VALID.equals( programIndicatorService.filterIsValid( pi.getFilter() ) ) : true ) ) ) .collect( Collectors.toMap( pi -> pi, pi -> pi.getFilter() ) ); return invalidFilters; } }
protected String getBoundedDataValueSelectSql( String programStageUid, String dataElementUid, Date reportingStartDate, Date reportingEndDate, ProgramIndicator programIndicator ) { if ( programIndicator.hasNonDefaultBoundaries() && programIndicator.hasEventBoundary() ) { String eventTableName = "analytics_event_" + programIndicator.getProgram().getUid(); String columnName = "\"" + dataElementUid + "\""; return "(select " + columnName + " from " + eventTableName + " where " + eventTableName + ".pi = enrollmenttable.pi and " + columnName + " is not null " + ( programIndicator.getEndEventBoundary() != null ? ( "and " + statementBuilder.getBoundaryCondition( programIndicator.getEndEventBoundary(), programIndicator, reportingStartDate, reportingEndDate ) + " ") : "" ) + (programIndicator.getStartEventBoundary() != null ? ("and " + statementBuilder.getBoundaryCondition( programIndicator.getStartEventBoundary(), programIndicator, reportingStartDate, reportingEndDate ) + " ") : "" ) + "and ps = '" + programStageUid + "' " + "order by executiondate " + "desc limit 1 )"; } else { return statementBuilder.columnQuote( programStageUid + ProgramIndicator.DB_SEPARATOR_ID + dataElementUid ); } }
for ( AnalyticsPeriodBoundary boundary : params.getProgramIndicator().getAnalyticsPeriodBoundaries() ) if ( params.hasProgramIndicatorDimension() && params.getProgramIndicator().hasFilter() ) String filter = programIndicatorService.getAnalyticsSQl( params.getProgramIndicator().getFilter(), params.getProgramIndicator(), false, params.getEarliestStartDate(), params.getLatestEndDate() ); String anyValueFilter = programIndicatorService.getAnyValueExistsClauseAnalyticsSql( params.getProgramIndicator().getExpression(), params.getProgramIndicator().getAnalyticsType() );
private String getProgramIndicatorEventInProgramStageSql(ProgramIndicator programIndicator, Date reportingStartDate, Date reportingEndDate ) { Assert.isTrue( programIndicator.hasEventDateCohortBoundary(), "Can not get event date cohort boundaries for program indicator:" + programIndicator.getUid() ); Map<String, Set<AnalyticsPeriodBoundary>> map = programIndicator.getEventDateCohortBoundaryByProgramStage(); final SimpleDateFormat format = new SimpleDateFormat(); format.applyPattern( Period.DEFAULT_DATE_FORMAT ); String sql = ""; for ( String programStage : map.keySet() ) { Set<AnalyticsPeriodBoundary> boundaries = map.get( programStage ); String eventTableName = "analytics_event_" + programIndicator.getProgram().getUid(); sql += " (select count(*) from " + eventTableName + " where " + eventTableName + ".pi = " + ANALYTICS_TBL_ALIAS + ".pi and executiondate is not null "; for ( AnalyticsPeriodBoundary boundary : boundaries ) { sql += " and executiondate " + ( boundary.getAnalyticsPeriodBoundaryType().isStartBoundary() ? ">" : "<" ) + " cast( '" + format.format( boundary.getBoundaryDate( reportingStartDate, reportingEndDate ) ) + "' as date )"; } sql += ") > 0"; } return sql; }
public boolean hasEventProgramIndicatorDimension() { return programIndicator != null && AnalyticsType.EVENT.equals( programIndicator.getAnalyticsType() ); }
String asClause = " as " + quote( in.getUid() ); columns.add( "(" + programIndicatorService.getAnalyticsSQl( in.getExpression(), in, params.getEarliestStartDate(), params.getLatestEndDate() ) + ")" + asClause );
public String getUid() { return programIndicator != null ? programIndicator.getUid() : null; }
grid.addValue( params.getProgramIndicator().getUid() ); grid.addValue( AnalyticsUtils.getRoundedValue( params, indicator.getDecimals(), value ) );
public void addProgramIndicator( ProgramIndicator programIndicator ) { members.add( programIndicator ); programIndicator.getGroups().add( this ); }
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; }
String variableColumnName = ProgramIndicator.getVariableColumnName( var ); for ( String uid : ProgramIndicator.getDataElementAndAttributeIdentifiers( expression, analyticsType ) ) for ( String uid : ProgramIndicator.getDataElementAndAttributeIdentifiers( expression, analyticsType ) )
.removeItemProgramIndicators() .withProgramIndicator( programIndicator ) .withProgram( programIndicator.getProgram() ) .build();
String function = params.getProgramIndicator().getAggregationTypeFallback().getValue(); String expression = programIndicatorService.getAnalyticsSQl( params.getProgramIndicator().getExpression(), params.getProgramIndicator(), params.getEarliestStartDate(), params.getLatestEndDate() );
/** * Returns a set of all analytics columns required for the variables used in the given expression * * @param expression the program indicator expression. * @return a set of column names */ public static Set<String> getVariableColumnNames( String expression ) { Set<String> requiredColumns = new HashSet<String>(); Set<String> variables = RegexUtils.getMatches( VARIABLE_PATTERN, expression, 1 ); for ( String variable : variables ) { String columnName = getVariableColumnName( variable ); if ( null != columnName ) { requiredColumns.add( columnName ); } } return requiredColumns; }
public boolean hasEnrollmentProgramIndicatorDimension() { return programIndicator != null && AnalyticsType.ENROLLMENT.equals( programIndicator.getAnalyticsType() ); }