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

Example 6 with AnalyticsTableColumn

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

the class JdbcCompletenessTargetTableManager method createTable.

@Override
public void createTable(AnalyticsTable table) {
    final String tableName = table.getTempTableName();
    final String sqlDrop = "drop table " + tableName;
    executeSilently(sqlDrop);
    String sqlCreate = "create table " + tableName + " (";
    List<AnalyticsTableColumn> columns = getDimensionColumns(table);
    validateDimensionColumns(columns);
    for (AnalyticsTableColumn col : columns) {
        sqlCreate += col.getName() + " " + col.getDataType() + ",";
    }
    sqlCreate += "value double precision)";
    log.info("Creating table: " + tableName + ", columns: " + columns.size());
    log.debug("Create SQL: " + sqlCreate);
    jdbcTemplate.execute(sqlCreate);
}

Example 7 with AnalyticsTableColumn

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

the class JdbcEnrollmentAnalyticsTableManager method populateTable.

@Override
protected void populateTable(AnalyticsTable table) {
    final String tableName = table.getTempTableName();
    final String piEnrollmentDate = statementBuilder.getCastToDate("pi.enrollmentdate");
    String sql = "insert into " + table.getTempTableName() + " (";
    List<AnalyticsTableColumn> columns = getDimensionColumns(table);
    validateDimensionColumns(columns);
    for (AnalyticsTableColumn col : columns) {
        sql += col.getName() + ",";
    }
    sql = removeLast(sql, 1) + ") select ";
    for (AnalyticsTableColumn col : columns) {
        sql += col.getAlias() + ",";
    }
    sql = removeLast(sql, 1) + " ";
    sql += "from programinstance pi " + "inner join program pr on pi.programid=pr.programid " + "left join trackedentityinstance tei on pi.trackedentityinstanceid=tei.trackedentityinstanceid and tei.deleted is false " + "inner join organisationunit ou on pi.organisationunitid=ou.organisationunitid " + "left join _orgunitstructure ous on pi.organisationunitid=ous.organisationunitid " + "left join _organisationunitgroupsetstructure ougs on pi.organisationunitid=ougs.organisationunitid " + "left join _dateperiodstructure dps on " + piEnrollmentDate + "=dps.dateperiod " + "where pr.programid=" + table.getProgram().getId() + " " + "and pi.organisationunitid is not null " + "and pi.incidentdate is not null " + "and pi.deleted is false ";
    populateAndLog(sql, tableName);
}

Example 8 with AnalyticsTableColumn

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

the class JdbcCompletenessTableManager method populateTable.

@Override
protected void populateTable(AnalyticsTable table) {
    final String start = DateUtils.getMediumDateString(table.getPeriod().getStartDate());
    final String end = DateUtils.getMediumDateString(table.getPeriod().getEndDate());
    final String tableName = table.getTempTableName();
    String insert = "insert into " + table.getTempTableName() + " (";
    List<AnalyticsTableColumn> columns = getDimensionColumns(table);
    validateDimensionColumns(columns);
    for (AnalyticsTableColumn col : columns) {
        insert += col.getName() + ",";
    }
    insert += "value) ";
    String select = "select ";
    for (AnalyticsTableColumn col : columns) {
        select += col.getAlias() + ",";
    }
    // Legacy fix
    select = select.replace("organisationunitid", "sourceid");
    select += "cdr.date as value " + "from completedatasetregistration cdr " + "inner join dataset ds on cdr.datasetid=ds.datasetid " + "inner join _organisationunitgroupsetstructure ougs on cdr.sourceid=ougs.organisationunitid " + "left join _orgunitstructure ous on cdr.sourceid=ous.organisationunitid " + "inner join _categorystructure acs on cdr.attributeoptioncomboid=acs.categoryoptioncomboid " + "inner join period pe on cdr.periodid=pe.periodid " + "inner join _periodstructure ps on cdr.periodid=ps.periodid " + "where pe.startdate >= '" + start + "' " + "and pe.startdate <= '" + end + "' " + "and cdr.date is not null";
    final String sql = insert + select;
    populateAndLog(sql, tableName);
}

Example 9 with AnalyticsTableColumn

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

the class JdbcCompletenessTargetTableManager method getDimensionColumns.

@Override
public List<AnalyticsTableColumn> getDimensionColumns(AnalyticsTable table) {
    List<AnalyticsTableColumn> columns = new ArrayList<>();
    List<OrganisationUnitGroupSet> orgUnitGroupSets = idObjectManager.getDataDimensionsNoAcl(OrganisationUnitGroupSet.class);
    List<OrganisationUnitLevel> levels = organisationUnitService.getFilledOrganisationUnitLevels();
    List<CategoryOptionGroupSet> attributeCategoryOptionGroupSets = categoryService.getAttributeCategoryOptionGroupSetsNoAcl();
    List<DataElementCategory> attributeCategories = categoryService.getAttributeDataDimensionCategoriesNoAcl();
    for (OrganisationUnitGroupSet groupSet : orgUnitGroupSets) {
        columns.add(new AnalyticsTableColumn(quote(groupSet.getUid()), "character(11)", "ougs." + quote(groupSet.getUid()), groupSet.getCreated()));
    }
    for (OrganisationUnitLevel level : levels) {
        String column = quote(PREFIX_ORGUNITLEVEL + level.getLevel());
        columns.add(new AnalyticsTableColumn(column, "character(11)", "ous." + column, level.getCreated()));
    }
    for (CategoryOptionGroupSet groupSet : attributeCategoryOptionGroupSets) {
        columns.add(new AnalyticsTableColumn(quote(groupSet.getUid()), "character(11)", "acs." + quote(groupSet.getUid()), groupSet.getCreated()));
    }
    for (DataElementCategory category : attributeCategories) {
        columns.add(new AnalyticsTableColumn(quote(category.getUid()), "character(11)", "acs." + quote(category.getUid()), category.getCreated()));
    }
    AnalyticsTableColumn ouOpening = new AnalyticsTableColumn(quote("ouopeningdate"), "date", "ou.openingdate");
    AnalyticsTableColumn ouClosed = new AnalyticsTableColumn(quote("oucloseddate"), "date", "ou.closeddate");
    AnalyticsTableColumn coStart = new AnalyticsTableColumn(quote("costartdate"), "date", "doc.costartdate");
    AnalyticsTableColumn coEnd = new AnalyticsTableColumn(quote("coenddate"), "date", "doc.coenddate");
    AnalyticsTableColumn ds = new AnalyticsTableColumn(quote("dx"), "character(11) not null", "ds.uid");
    AnalyticsTableColumn ao = new AnalyticsTableColumn(quote("ao"), "character(11) not null", "ao.uid");
    columns.addAll(Lists.newArrayList(ouOpening, ouClosed, coStart, coEnd, ds, ao));
    return filterDimensionColumns(columns);
}

Example 10 with AnalyticsTableColumn

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

the class JdbcCompletenessTargetTableManager method populateTable.

@Override
protected void populateTable(AnalyticsTable table) {
    final String tableName = table.getTempTableName();
    String sql = "insert into " + table.getTempTableName() + " (";
    List<AnalyticsTableColumn> columns = getDimensionColumns(table);
    validateDimensionColumns(columns);
    for (AnalyticsTableColumn col : columns) {
        sql += col.getName() + ",";
    }
    sql += "value) select ";
    for (AnalyticsTableColumn col : columns) {
        sql += col.getAlias() + ",";
    }
    sql += "1 as value " + "from _datasetorganisationunitcategory doc " + "inner join dataset ds on doc.datasetid=ds.datasetid " + "inner join organisationunit ou on doc.organisationunitid=ou.organisationunitid " + "left join _orgunitstructure ous on doc.organisationunitid=ous.organisationunitid " + "left join _organisationunitgroupsetstructure ougs on doc.organisationunitid=ougs.organisationunitid " + "left join categoryoptioncombo ao on doc.attributeoptioncomboid=ao.categoryoptioncomboid " + "left join _categorystructure acs on doc.attributeoptioncomboid=acs.categoryoptioncomboid ";
    populateAndLog(sql, tableName);
}