Examples with AnalyticsTableUpdateParams org.hisp.dhis.analytics.AnalyticsTableUpdateParams used on opensource projects

Example 11 with AnalyticsTableUpdateParams

use of org.hisp.dhis.analytics.AnalyticsTableUpdateParams in project dhis2-core by dhis2.

the class JdbcEventAnalyticsTableManagerTest method verifyGetTableWithDataElements.

@Test
void verifyGetTableWithDataElements() {
    when(databaseInfo.isSpatialSupport()).thenReturn(true);
    Program program = createProgram('A');
    DataElement d1 = createDataElement('Z', ValueType.TEXT, AggregationType.SUM);
    DataElement d2 = createDataElement('P', ValueType.PERCENTAGE, AggregationType.SUM);
    DataElement d3 = createDataElement('Y', ValueType.BOOLEAN, AggregationType.NONE);
    DataElement d4 = createDataElement('W', ValueType.DATE, AggregationType.LAST);
    DataElement d5 = createDataElement('G', ValueType.ORGANISATION_UNIT, AggregationType.NONE);
    DataElement d6 = createDataElement('H', ValueType.INTEGER, AggregationType.SUM);
    DataElement d7 = createDataElement('U', ValueType.COORDINATE, AggregationType.NONE);
    ProgramStage ps1 = createProgramStage('A', Sets.newHashSet(d1, d2, d3, d4, d5, d6, d7));
    program.setProgramStages(Sets.newHashSet(ps1));
    when(idObjectManager.getAllNoAcl(Program.class)).thenReturn(Lists.newArrayList(program));
    String aliasD1 = "(select eventdatavalues #>> '{%s, value}' " + FROM_CLAUSE + " ) as \"%s\"";
    String aliasD2 = "(select cast(eventdatavalues #>> '{%s, value}' as " + statementBuilder.getDoubleColumnType() + ") " + FROM_CLAUSE + "  and eventdatavalues #>> '{%s,value}' " + statementBuilder.getRegexpMatch() + " '^(-?[0-9]+)(\\.[0-9]+)?$') as \"%s\"";
    String aliasD3 = "(select case when eventdatavalues #>> '{%s, value}' = 'true' then 1 when eventdatavalues #>> '{%s, value}' = 'false' then 0 else null end " + FROM_CLAUSE + " ) as \"%s\"";
    String aliasD4 = "(select cast(eventdatavalues #>> '{%s, value}' as timestamp) " + FROM_CLAUSE + "  and eventdatavalues #>> '{%s,value}' " + statementBuilder.getRegexpMatch() + " '^\\d{4}-\\d{2}-\\d{2}(\\s|T)?((\\d{2}:)(\\d{2}:)?(\\d{2}))?(|.(\\d{3})|.(\\d{3})Z)?$') as \"%s\"";
    String aliasD5 = "(select ou.uid from organisationunit ou where ou.uid = " + "(select eventdatavalues #>> '{" + d5.getUid() + ", value}' " + FROM_CLAUSE + " )) as \"" + d5.getUid() + "\"";
    String aliasD6 = "(select cast(eventdatavalues #>> '{%s, value}' as bigint) " + FROM_CLAUSE + "  and eventdatavalues #>> '{%s,value}' " + statementBuilder.getRegexpMatch() + " '^(-?[0-9]+)(\\.[0-9]+)?$') as \"%s\"";
    String aliasD7 = "(select ST_GeomFromGeoJSON('{\"type\":\"Point\", \"coordinates\":' || (eventdatavalues #>> '{%s, value}') || ', \"crs\":{\"type\":\"name\", \"properties\":{\"name\":\"EPSG:4326\"}}}') from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as \"%s\"";
    String aliasD5_geo = "(select ou.geometry from organisationunit ou where ou.uid = (select eventdatavalues #>> '{" + d5.getUid() + ", value}' " + FROM_CLAUSE + " )) as \"" + d5.getUid() + "\"";
    String aliasD5_name = "(select ou.name from organisationunit ou where ou.uid = (select eventdatavalues #>> '{" + d5.getUid() + ", value}' " + FROM_CLAUSE + " )) as \"" + d5.getUid() + "\"";
    AnalyticsTableUpdateParams params = AnalyticsTableUpdateParams.newBuilder().withLastYears(2).withStartTime(START_TIME).withToday(today).build();
    when(jdbcTemplate.queryForList(getYearQueryForCurrentYear(program, true), Integer.class)).thenReturn(Lists.newArrayList(2018, 2019));
    List<AnalyticsTable> tables = subject.getAnalyticsTables(params);
    assertThat(tables, hasSize(1));
    new AnalyticsTableAsserter.Builder(tables.get(0)).withTableName(TABLE_PREFIX + program.getUid().toLowerCase()).withTableType(AnalyticsTableType.EVENT).withColumnSize(59).addColumns(periodColumns).addColumn(d1.getUid(), TEXT, // ValueType.TEXT
    toAlias(aliasD1, d1.getUid())).addColumn(d2.getUid(), DOUBLE, // ValueType.PERCENTAGE
    toAlias(aliasD2, d2.getUid())).addColumn(d3.getUid(), INTEGER, // ValueType.BOOLEAN
    toAlias(aliasD3, d3.getUid())).addColumn(d4.getUid(), TIMESTAMP, // ValueType.DATE
    toAlias(aliasD4, d4.getUid())).addColumn(d5.getUid(), TEXT, // ValueType.ORGANISATION_UNIT
    toAlias(aliasD5, d5.getUid())).addColumn(d6.getUid(), BIGINT, // ValueType.INTEGER
    toAlias(aliasD6, d6.getUid())).addColumn(d7.getUid(), GEOMETRY_POINT, // ValueType.COORDINATES
    toAlias(aliasD7, d7.getUid())).addColumn(d5.getUid() + "_geom", GEOMETRY, toAlias(aliasD5_geo, d5.getUid()), IndexType.GIST).addColumn(d5.getUid() + "_name", TEXT, toAlias(aliasD5_name, d5.getUid() + "_name")).withDefaultColumns(subject.getFixedColumns()).build().verify();
}

Example 12 with AnalyticsTableUpdateParams

use of org.hisp.dhis.analytics.AnalyticsTableUpdateParams in project dhis2-core by dhis2.

the class JdbcEventAnalyticsTableManagerTest method verifyGetTableWithCategoryCombo.

@Test
void verifyGetTableWithCategoryCombo() {
    Program program = createProgram('A');
    Category categoryA = createCategory('A');
    categoryA.setCreated(getDate(2019, 12, 3));
    Category categoryB = createCategory('B');
    categoryA.setCreated(getDate(2018, 8, 5));
    CategoryCombo categoryCombo = createCategoryCombo('B', categoryA, categoryB);
    addCategoryCombo(program, categoryCombo);
    when(idObjectManager.getAllNoAcl(Program.class)).thenReturn(Lists.newArrayList(program));
    when(jdbcTemplate.queryForList(getYearQueryForCurrentYear(program, true), Integer.class)).thenReturn(Lists.newArrayList(2018, 2019));
    AnalyticsTableUpdateParams params = AnalyticsTableUpdateParams.newBuilder().withLastYears(2).withStartTime(START_TIME).withToday(today).build();
    List<AnalyticsTable> tables = subject.getAnalyticsTables(params);
    assertThat(tables, hasSize(1));
    new AnalyticsTableAsserter.Builder(tables.get(0)).withTableType(AnalyticsTableType.EVENT).withTableName(TABLE_PREFIX + program.getUid().toLowerCase()).withColumnSize(52).withDefaultColumns(subject.getFixedColumns()).addColumns(periodColumns).addColumn(categoryA.getUid(), CHARACTER_11, "acs.", categoryA.getCreated()).addColumn(categoryB.getUid(), CHARACTER_11, "acs.", categoryB.getCreated()).build().verify();
}

Example 13 with AnalyticsTableUpdateParams

use of org.hisp.dhis.analytics.AnalyticsTableUpdateParams in project dhis2-core by dhis2.

the class JdbcEventAnalyticsTableManagerTest method verifyTeiTypeOrgUnitFetchesOuNameWhenPopulatingEventAnalyticsTable.

@Test
void verifyTeiTypeOrgUnitFetchesOuNameWhenPopulatingEventAnalyticsTable() {
    ArgumentCaptor<String> sql = ArgumentCaptor.forClass(String.class);
    when(databaseInfo.isSpatialSupport()).thenReturn(true);
    Program programA = createProgram('A');
    TrackedEntityAttribute tea = createTrackedEntityAttribute('a', ValueType.ORGANISATION_UNIT);
    tea.setId(9999);
    ProgramTrackedEntityAttribute programTrackedEntityAttribute = createProgramTrackedEntityAttribute(programA, tea);
    programA.setProgramAttributes(Lists.newArrayList(programTrackedEntityAttribute));
    when(idObjectManager.getAllNoAcl(Program.class)).thenReturn(Lists.newArrayList(programA));
    AnalyticsTableUpdateParams params = AnalyticsTableUpdateParams.newBuilder().withLastYears(2).withStartTime(START_TIME).withToday(today).build();
    when(jdbcTemplate.queryForList(getYearQueryForCurrentYear(programA, true), Integer.class)).thenReturn(Lists.newArrayList(2018, 2019));
    subject.populateTable(params, PartitionUtils.getTablePartitions(subject.getAnalyticsTables(params)).get(0));
    verify(jdbcTemplate).execute(sql.capture());
    String ouQuery = "(select ou.%s from organisationunit ou where ou.uid = " + "(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and " + "trackedentityattributeid=9999)) as \"" + tea.getUid() + "\"";
    assertThat(sql.getValue(), containsString(String.format(ouQuery, "uid")));
    assertThat(sql.getValue(), containsString(String.format(ouQuery, "name")));
}

Example 14 with AnalyticsTableUpdateParams

use of org.hisp.dhis.analytics.AnalyticsTableUpdateParams in project dhis2-core by dhis2.

the class JdbcEventAnalyticsTableManagerTest method verifyTeaTypeOrgUnitFetchesOuNameWhenPopulatingEventAnalyticsTable.

@Test
void verifyTeaTypeOrgUnitFetchesOuNameWhenPopulatingEventAnalyticsTable() {
    ArgumentCaptor<String> sql = ArgumentCaptor.forClass(String.class);
    when(databaseInfo.isSpatialSupport()).thenReturn(true);
    Program programA = createProgram('A');
    TrackedEntityAttribute tea = createTrackedEntityAttribute('a', ValueType.ORGANISATION_UNIT);
    tea.setId(9999);
    ProgramTrackedEntityAttribute programTrackedEntityAttribute = createProgramTrackedEntityAttribute(programA, tea);
    programA.setProgramAttributes(Lists.newArrayList(programTrackedEntityAttribute));
    when(idObjectManager.getAllNoAcl(Program.class)).thenReturn(Lists.newArrayList(programA));
    when(jdbcTemplate.queryForList("select distinct(extract(year from psi.executiondate)) from programstageinstance psi inner join programinstance pi on psi.programinstanceid = pi.programinstanceid where psi.lastupdated <= '2019-08-01T00:00:00' and pi.programid = 0 and psi.executiondate is not null and psi.executiondate > '1000-01-01' and psi.deleted is false and psi.executiondate >= '2018-01-01'", Integer.class)).thenReturn(Lists.newArrayList(2018, 2019));
    AnalyticsTableUpdateParams params = AnalyticsTableUpdateParams.newBuilder().withLastYears(2).withStartTime(START_TIME).withToday(today).build();
    subject.populateTable(params, PartitionUtils.getTablePartitions(subject.getAnalyticsTables(params)).get(0));
    verify(jdbcTemplate).execute(sql.capture());
    final String ouQuery = "(select ou.%s from organisationunit ou where ou.uid = " + "(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and " + "trackedentityattributeid=9999)) as \"" + tea.getUid() + "\"";
    assertThat(sql.getValue(), containsString(String.format(ouQuery, "uid")));
    assertThat(sql.getValue(), containsString(String.format(ouQuery, "name")));
}

Example 15 with AnalyticsTableUpdateParams

use of org.hisp.dhis.analytics.AnalyticsTableUpdateParams in project dhis2-core by dhis2.

the class JdbcEventAnalyticsTableManagerTest method verifyDataElementTypeOrgUnitFetchesOuNameWhenPopulatingEventAnalyticsTable.

@Test
void verifyDataElementTypeOrgUnitFetchesOuNameWhenPopulatingEventAnalyticsTable() {
    ArgumentCaptor<String> sql = ArgumentCaptor.forClass(String.class);
    when(databaseInfo.isSpatialSupport()).thenReturn(true);
    Program programA = createProgram('A');
    DataElement d5 = createDataElement('G', ValueType.ORGANISATION_UNIT, AggregationType.NONE);
    ProgramStage ps1 = createProgramStage('A', Sets.newHashSet(d5));
    programA.setProgramStages(Sets.newHashSet(ps1));
    when(idObjectManager.getAllNoAcl(Program.class)).thenReturn(Lists.newArrayList(programA));
    AnalyticsTableUpdateParams params = AnalyticsTableUpdateParams.newBuilder().withLastYears(2).withStartTime(START_TIME).withToday(today).build();
    when(jdbcTemplate.queryForList(getYearQueryForCurrentYear(programA, true), Integer.class)).thenReturn(Lists.newArrayList(2018, 2019));
    subject.populateTable(params, PartitionUtils.getTablePartitions(subject.getAnalyticsTables(params)).get(0));
    verify(jdbcTemplate).execute(sql.capture());
    String ouQuery = "(select ou.%s from organisationunit ou where ou.uid = " + "(select eventdatavalues #>> '{" + d5.getUid() + ", value}' from programstageinstance where " + "programstageinstanceid=psi.programstageinstanceid )) as \"" + d5.getUid() + "\"";
    assertThat(sql.getValue(), containsString(String.format(ouQuery, "uid")));
    assertThat(sql.getValue(), containsString(String.format(ouQuery, "name")));
}