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 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() ); } }
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; } }
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 ); } }
.removeItemProgramIndicators() .withProgramIndicator( programIndicator ) .withProgram( programIndicator.getProgram() ) .build();