Search in sources :

Example 6 with IdSchemes

use of org.hisp.dhis.common.IdSchemes in project dhis2-core by dhis2.

the class JdbcEventStore method getEvents.

// -------------------------------------------------------------------------
// EventStore implementation
// -------------------------------------------------------------------------
@Override
public List<Event> getEvents(EventSearchParams params, List<OrganisationUnit> organisationUnits) {
    List<Event> events = new ArrayList<>();
    String sql = buildSql(params, organisationUnits);
    SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql);
    log.debug("Event query SQL: " + sql);
    Event event = new Event();
    event.setEvent("not_valid");
    Set<String> notes = new HashSet<>();
    IdSchemes idSchemes = ObjectUtils.firstNonNull(params.getIdSchemes(), new IdSchemes());
    while (rowSet.next()) {
        if (rowSet.getString("psi_uid") == null) {
            continue;
        }
        if (event.getUid() == null || !event.getUid().equals(rowSet.getString("psi_uid"))) {
            event = new Event();
            event.setUid(rowSet.getString("psi_uid"));
            event.setEvent(IdSchemes.getValue(rowSet.getString("psi_uid"), rowSet.getString("psi_code"), idSchemes.getProgramStageInstanceIdScheme()));
            event.setTrackedEntityInstance(rowSet.getString("tei_uid"));
            event.setStatus(EventStatus.valueOf(rowSet.getString("psi_status")));
            event.setProgram(IdSchemes.getValue(rowSet.getString("p_uid"), rowSet.getString("p_code"), idSchemes.getProgramIdScheme()));
            event.setProgramStage(IdSchemes.getValue(rowSet.getString("ps_uid"), rowSet.getString("ps_code"), idSchemes.getProgramStageIdScheme()));
            event.setOrgUnit(IdSchemes.getValue(rowSet.getString("ou_uid"), rowSet.getString("ou_code"), idSchemes.getOrgUnitIdScheme()));
            event.setDeleted(rowSet.getBoolean("psi_deleted"));
            ProgramType programType = ProgramType.fromValue(rowSet.getString("p_type"));
            if (programType != ProgramType.WITHOUT_REGISTRATION) {
                event.setEnrollment(rowSet.getString("pi_uid"));
                event.setEnrollmentStatus(EnrollmentStatus.fromProgramStatus(ProgramStatus.valueOf(rowSet.getString("pi_status"))));
                event.setFollowup(rowSet.getBoolean("pi_followup"));
            }
            event.setAttributeOptionCombo(rowSet.getString("coc_categoryoptioncombouid"));
            event.setAttributeCategoryOptions(rowSet.getString("deco_uid"));
            event.setTrackedEntityInstance(rowSet.getString("tei_uid"));
            event.setStoredBy(rowSet.getString("psi_storedby"));
            event.setOrgUnitName(rowSet.getString("ou_name"));
            event.setDueDate(DateUtils.getIso8601NoTz(rowSet.getDate("psi_duedate")));
            event.setEventDate(DateUtils.getIso8601NoTz(rowSet.getDate("psi_executiondate")));
            event.setCreated(DateUtils.getIso8601NoTz(rowSet.getDate("psi_created")));
            event.setLastUpdated(DateUtils.getIso8601NoTz(rowSet.getDate("psi_lastupdated")));
            event.setCompletedBy(rowSet.getString("psi_completedby"));
            event.setCompletedDate(DateUtils.getIso8601NoTz(rowSet.getDate("psi_completeddate")));
            Double longitude = rowSet.getDouble("psi_longitude");
            Double latitude = rowSet.getDouble("psi_latitude");
            if (longitude != null && latitude != null) {
                Coordinate coordinate = new Coordinate(longitude, latitude);
                try {
                    List<Double> list = OBJECT_MAPPER.readValue(coordinate.getCoordinateString(), new TypeReference<List<Double>>() {
                    });
                    coordinate.setLongitude(list.get(0));
                    coordinate.setLatitude(list.get(1));
                } catch (IOException ignored) {
                }
                if (coordinate.isValid()) {
                    event.setCoordinate(coordinate);
                }
            }
            events.add(event);
        } else {
            String attributeCategoryCombination = event.getAttributeCategoryOptions();
            String currentAttributeCategoryCombination = rowSet.getString("deco_uid");
            if (!attributeCategoryCombination.contains(currentAttributeCategoryCombination)) {
                event.setAttributeCategoryOptions(attributeCategoryCombination + ";" + currentAttributeCategoryCombination);
            }
        }
        if (rowSet.getString("pdv_value") != null && rowSet.getString("de_uid") != null && isNewDataValue(rowSet, event.getDataValues())) {
            DataValue dataValue = new DataValue();
            dataValue.setCreated(DateUtils.getIso8601NoTz(rowSet.getDate("pdv_created")));
            dataValue.setLastUpdated(DateUtils.getIso8601NoTz(rowSet.getDate("pdv_lastupdated")));
            dataValue.setValue(rowSet.getString("pdv_value"));
            dataValue.setProvidedElsewhere(rowSet.getBoolean("pdv_providedelsewhere"));
            dataValue.setDataElement(IdSchemes.getValue(rowSet.getString("de_uid"), rowSet.getString("de_code"), idSchemes.getDataElementIdScheme()));
            dataValue.setStoredBy(rowSet.getString("pdv_storedby"));
            event.getDataValues().add(dataValue);
        }
        if (rowSet.getString("psinote_value") != null && !notes.contains(rowSet.getString("psinote_id"))) {
            Note note = new Note();
            note.setValue(rowSet.getString("psinote_value"));
            note.setStoredDate(rowSet.getString("psinote_storeddate"));
            note.setStoredBy(rowSet.getString("psinote_storedby"));
            event.getNotes().add(note);
            notes.add(rowSet.getString("psinote_id"));
        }
    }
    return events;
}
Also used : SqlRowSet(org.springframework.jdbc.support.rowset.SqlRowSet) IdSchemes(org.hisp.dhis.common.IdSchemes) ArrayList(java.util.ArrayList) DateUtils.getMediumDateString(org.hisp.dhis.system.util.DateUtils.getMediumDateString) IOException(java.io.IOException) ArrayList(java.util.ArrayList) List(java.util.List) ProgramType(org.hisp.dhis.program.ProgramType) HashSet(java.util.HashSet)

Example 7 with IdSchemes

use of org.hisp.dhis.common.IdSchemes in project dhis2-core by dhis2.

the class DefaultCompleteDataSetRegistrationExchangeService method limitToValidIdSchemes.

/*
     * Limit valid IdSchemes for export to UID, CODE, NAME
     */
private static void limitToValidIdSchemes(ExportParams params) {
    IdSchemes schemes = params.getOutputIdSchemes();
    if (!EXPORT_ID_SCHEMES.contains(schemes.getIdScheme())) {
        schemes.setIdScheme(IdScheme.UID.getIdentifiableString());
    }
    if (!EXPORT_ID_SCHEMES.contains(schemes.getDataSetIdScheme())) {
        schemes.setDataSetIdScheme(IdScheme.UID.getIdentifiableString());
    }
    if (!EXPORT_ID_SCHEMES.contains(schemes.getOrgUnitIdScheme())) {
        schemes.setOrgUnitIdScheme(IdScheme.UID.getIdentifiableString());
    }
    if (!EXPORT_ID_SCHEMES.contains(schemes.getAttributeOptionComboIdScheme())) {
        schemes.setAttributeOptionComboIdScheme(IdScheme.UID.getIdentifiableString());
    }
    params.setOutputIdSchemes(schemes);
}
Also used : IdSchemes(org.hisp.dhis.common.IdSchemes)

Example 8 with IdSchemes

use of org.hisp.dhis.common.IdSchemes in project dhis2-core by dhis2.

the class SpringDataValueSetStore method writeDataValueSet.

private void writeDataValueSet(String sql, DataExportParams params, Date completeDate, final DataValueSet dataValueSet) {
    if (params.isSingleDataValueSet()) {
        IdSchemes idScheme = params.getOutputIdSchemes() != null ? params.getOutputIdSchemes() : new IdSchemes();
        IdScheme ouScheme = idScheme.getOrgUnitIdScheme();
        IdScheme dataSetScheme = idScheme.getDataSetIdScheme();
        dataValueSet.setDataSet(params.getFirstDataSet().getPropertyValue(dataSetScheme));
        dataValueSet.setCompleteDate(getLongGmtDateString(completeDate));
        dataValueSet.setPeriod(params.getFirstPeriod().getIsoDate());
        dataValueSet.setOrgUnit(params.getFirstOrganisationUnit().getPropertyValue(ouScheme));
    }
    final Calendar calendar = PeriodType.getCalendar();
    jdbcTemplate.query(sql, new RowCallbackHandler() {

        @Override
        public void processRow(ResultSet rs) throws SQLException {
            DataValue dataValue = dataValueSet.getDataValueInstance();
            PeriodType pt = PeriodType.getPeriodTypeByName(rs.getString("ptname"));
            boolean deleted = rs.getBoolean("deleted");
            dataValue.setDataElement(rs.getString("deid"));
            dataValue.setPeriod(pt.createPeriod(rs.getDate("pestart"), calendar).getIsoDate());
            dataValue.setOrgUnit(rs.getString("ouid"));
            dataValue.setCategoryOptionCombo(rs.getString("cocid"));
            dataValue.setAttributeOptionCombo(rs.getString("aocid"));
            dataValue.setValue(rs.getString("value"));
            dataValue.setStoredBy(rs.getString("storedby"));
            dataValue.setCreated(getLongGmtDateString(rs.getTimestamp("created")));
            dataValue.setLastUpdated(getLongGmtDateString(rs.getTimestamp("lastupdated")));
            dataValue.setComment(rs.getString("comment"));
            dataValue.setFollowup(rs.getBoolean("followup"));
            if (deleted) {
                dataValue.setDeleted(deleted);
            }
            dataValue.close();
        }
    });
    dataValueSet.close();
}
Also used : PeriodType(org.hisp.dhis.period.PeriodType) IdSchemes(org.hisp.dhis.common.IdSchemes) SQLException(java.sql.SQLException) DataValue(org.hisp.dhis.dxf2.datavalue.DataValue) Calendar(org.hisp.dhis.calendar.Calendar) ResultSet(java.sql.ResultSet) IdScheme(org.hisp.dhis.common.IdScheme) RowCallbackHandler(org.springframework.jdbc.core.RowCallbackHandler)

Example 9 with IdSchemes

use of org.hisp.dhis.common.IdSchemes in project dhis2-core by dhis2.

the class SpringDataValueSetStore method getDataValueSql.

//--------------------------------------------------------------------------
// Supportive methods
//--------------------------------------------------------------------------
private String getDataValueSql(DataExportParams params) {
    IdSchemes idScheme = params.getOutputIdSchemes() != null ? params.getOutputIdSchemes() : new IdSchemes();
    String deScheme = idScheme.getDataElementIdScheme().getIdentifiableString().toLowerCase();
    String ouScheme = idScheme.getOrgUnitIdScheme().getIdentifiableString().toLowerCase();
    String cocScheme = idScheme.getCategoryOptionComboIdScheme().getIdentifiableString().toLowerCase();
    String dataElements = getCommaDelimitedString(getIdentifiers(params.getAllDataElements()));
    String orgUnits = getCommaDelimitedString(getIdentifiers(params.getOrganisationUnits()));
    String orgUnitGroups = getCommaDelimitedString(getIdentifiers(params.getOrganisationUnitGroups()));
    //----------------------------------------------------------------------
    // Identifier schemes
    //----------------------------------------------------------------------
    String deSql = idScheme.getDataElementIdScheme().isAttribute() ? "coalesce((" + "select av.value as deid from attributevalue av " + "inner join dataelementattributevalues deav on av.attributevalueid=deav.attributevalueid " + "inner join attribute at on av.attributeid=at.attributeid and at.uid='" + idScheme.getDataElementIdScheme().getAttribute() + "' " + "where dv.dataelementid=deav.dataelementid " + "limit 1), de.uid) as deid" : "de." + deScheme + " as deid";
    String ouSql = idScheme.getOrgUnitIdScheme().isAttribute() ? "coalesce((" + "select av.value as ouid from attributevalue av " + "inner join organisationunitattributevalues ouav on av.attributevalueid=ouav.attributevalueid " + "inner join attribute at on av.attributeid=at.attributeid and at.uid='" + idScheme.getOrgUnitIdScheme().getAttribute() + "' " + "where dv.sourceid=ouav.organisationunitid " + "limit 1), ou.uid) as ouid" : "ou." + ouScheme + " as ouid";
    String cocSql = idScheme.getCategoryOptionComboIdScheme().isAttribute() ? "coalesce((" + "select av.value as cocid from attributevalue av " + "inner join categoryoptioncomboattributevalues cocav on av.attributevalueid=cocav.attributevalueid " + "inner join attribute at on av.attributeid=at.attributeid and at.uid='" + idScheme.getCategoryOptionComboIdScheme().getAttribute() + "' " + "where dv.categoryoptioncomboid=cocav.categoryoptioncomboid " + "limit 1), coc.uid) as cocid" : "coc." + cocScheme + " as cocid";
    String aocSql = idScheme.getCategoryOptionComboIdScheme().isAttribute() ? "coalesce((" + "select av.value as aocid from attributevalue av " + "inner join categoryoptioncomboattributevalues cocav on av.attributevalueid=cocav.attributevalueid " + "inner join attribute at on av.attributeid=at.attributeid and at.uid='" + idScheme.getCategoryOptionComboIdScheme().getAttribute() + "' " + "where dv.attributeoptioncomboid=cocav.categoryoptioncomboid " + "limit 1), aoc.uid) as aocid" : "aoc." + cocScheme + " as aocid";
    //----------------------------------------------------------------------
    // Data values
    //----------------------------------------------------------------------
    String sql = "select " + deSql + ", pe.startdate as pestart, pt.name as ptname, " + ouSql + ", " + cocSql + ", " + aocSql + ", " + "dv.value, dv.storedby, dv.created, dv.lastupdated, dv.comment, dv.followup, dv.deleted " + "from datavalue dv " + "inner join dataelement de on (dv.dataelementid=de.dataelementid) " + "inner join period pe on (dv.periodid=pe.periodid) " + "inner join periodtype pt on (pe.periodtypeid=pt.periodtypeid) " + "inner join organisationunit ou on (dv.sourceid=ou.organisationunitid) " + "inner join categoryoptioncombo coc on (dv.categoryoptioncomboid=coc.categoryoptioncomboid) " + "inner join categoryoptioncombo aoc on (dv.attributeoptioncomboid=aoc.categoryoptioncomboid) ";
    if (params.hasOrganisationUnitGroups()) {
        sql += "left join orgunitgroupmembers ougm on (ou.organisationunitid=ougm.organisationunitid) ";
    }
    sql += "where de.dataelementid in (" + dataElements + ") ";
    if (params.isIncludeChildren()) {
        sql += "and (";
        for (OrganisationUnit parent : params.getOrganisationUnits()) {
            sql += "ou.path like '" + parent.getPath() + "%' or ";
        }
        sql = TextUtils.removeLastOr(sql) + ") ";
    } else {
        sql += "and (";
        if (params.hasOrganisationUnits()) {
            sql += "dv.sourceid in (" + orgUnits + ") ";
        }
        if (params.hasOrganisationUnits() && params.hasOrganisationUnitGroups()) {
            sql += "or ";
        }
        if (params.hasOrganisationUnitGroups()) {
            sql += "ougm.orgunitgroupid in (" + orgUnitGroups + ") ";
        }
        sql += ") ";
    }
    if (!params.isIncludeDeleted()) {
        sql += "and dv.deleted is false ";
    }
    if (params.hasStartEndDate()) {
        sql += "and (pe.startdate >= '" + getMediumDateString(params.getStartDate()) + "' and pe.enddate <= '" + getMediumDateString(params.getEndDate()) + "') ";
    } else if (params.hasPeriods()) {
        sql += "and dv.periodid in (" + getCommaDelimitedString(getIdentifiers(params.getPeriods())) + ") ";
    }
    if (params.hasAttributeOptionCombos()) {
        sql += "and dv.attributeoptioncomboid in (" + getCommaDelimitedString(getIdentifiers(params.getAttributeOptionCombos())) + ") ";
    }
    if (params.hasLastUpdated()) {
        sql += "and dv.lastupdated >= '" + getLongGmtDateString(params.getLastUpdated()) + "' ";
    } else if (params.hasLastUpdatedDuration()) {
        sql += "and dv.lastupdated >= '" + getLongGmtDateString(DateUtils.nowMinusDuration(params.getLastUpdatedDuration())) + "' ";
    }
    if (params.hasLimit()) {
        sql += "limit " + params.getLimit();
    }
    log.debug("Get data value set SQL: " + sql);
    return sql;
}
Also used : OrganisationUnit(org.hisp.dhis.organisationunit.OrganisationUnit) IdSchemes(org.hisp.dhis.common.IdSchemes) DateUtils.getMediumDateString(org.hisp.dhis.system.util.DateUtils.getMediumDateString) DateUtils.getLongGmtDateString(org.hisp.dhis.system.util.DateUtils.getLongGmtDateString) TextUtils.getCommaDelimitedString(org.hisp.dhis.commons.util.TextUtils.getCommaDelimitedString)

Example 10 with IdSchemes

use of org.hisp.dhis.common.IdSchemes in project dhis2-core by dhis2.

the class JdbcCompleteDataSetRegistrationExchangeStore method createQuery.

private static String createQuery(ExportParams params) {
    IdSchemes idSchemes = params.getOutputIdSchemes() != null ? params.getOutputIdSchemes() : new IdSchemes();
    ImmutableMap.Builder<String, String> namedParamsBuilder = ImmutableMap.<String, String>builder().put(DATA_SET_SCHEME, idSchemes.getDataSetIdScheme().getIdentifiableString().toLowerCase()).put(ORG_UNIT_SCHEME, idSchemes.getOrgUnitIdScheme().getIdentifiableString().toLowerCase()).put(ATTR_OPT_COMBO_SCHEME, idSchemes.getAttributeOptionComboIdScheme().getIdentifiableString().toLowerCase());
    String sql = "SELECT ds.${dsScheme} AS dsid, pe.startdate AS pe_start, pt.name AS ptname, ou.${ouScheme} AS ouid, " + "aoc.${aocScheme} AS aocid, cdsr.storedby AS storedby, cdsr.date AS created " + "FROM completedatasetregistration cdsr " + "INNER JOIN dataset ds ON (cdsr.datasetid=ds.datasetid) " + "INNER JOIN period pe ON (cdsr.periodid=pe.periodid) " + "INNER JOIN periodtype pt ON (pe.periodtypeid=pt.periodtypeid) " + "INNER JOIN organisationunit ou ON (cdsr.sourceid=ou.organisationunitid) " + "INNER JOIN categoryoptioncombo aoc ON (cdsr.attributeoptioncomboid = aoc.categoryoptioncomboid) ";
    sql += createOrgUnitGroupJoin(params);
    sql += createDataSetClause(params, namedParamsBuilder);
    sql += createOrgUnitClause(params, namedParamsBuilder);
    sql += createPeriodClause(params, namedParamsBuilder);
    sql += createCreatedClause(params, namedParamsBuilder);
    sql += createLimitClause(params, namedParamsBuilder);
    sql = new StrSubstitutor(namedParamsBuilder.build(), "${", "}").replace(sql);
    log.debug("CompleteDataSetRegistrations query: " + sql);
    return sql;
}
Also used : StrSubstitutor(org.apache.commons.lang3.text.StrSubstitutor) IdSchemes(org.hisp.dhis.common.IdSchemes) ImmutableMap(com.google.common.collect.ImmutableMap)

Aggregations

IdSchemes (org.hisp.dhis.common.IdSchemes)13 DhisSpringTest (org.hisp.dhis.DhisSpringTest)5 Test (org.junit.Test)5 ByteArrayOutputStream (java.io.ByteArrayOutputStream)4 Date (java.util.Date)3 HashSet (java.util.HashSet)3 DataExportParams (org.hisp.dhis.datavalue.DataExportParams)3 DateUtils.getMediumDateString (org.hisp.dhis.system.util.DateUtils.getMediumDateString)3 IOException (java.io.IOException)2 ArrayList (java.util.ArrayList)2 DataValue (org.hisp.dhis.datavalue.DataValue)2 ImportSummary (org.hisp.dhis.dxf2.importsummary.ImportSummary)2 ProgramType (org.hisp.dhis.program.ProgramType)2 SqlRowSet (org.springframework.jdbc.support.rowset.SqlRowSet)2 ImmutableMap (com.google.common.collect.ImmutableMap)1 OutputStream (java.io.OutputStream)1 OutputStreamWriter (java.io.OutputStreamWriter)1 ResultSet (java.sql.ResultSet)1 SQLException (java.sql.SQLException)1 List (java.util.List)1