Examples with TextUtils.getQuotedCommaDelimitedString org.hisp.dhis.commons.util.TextUtils.getQuotedCommaDelimitedString used on opensource projects

Example 1 with TextUtils.getQuotedCommaDelimitedString

use of org.hisp.dhis.commons.util.TextUtils.getQuotedCommaDelimitedString in project dhis2-core by dhis2.

the class JdbcAnalyticsTableManager method populateTable.

/**
     * Populates the given analytics table.
     *
     * @param table               analytics table to populate.
     * @param valueExpression     numeric value expression.
     * @param textValueExpression textual value expression.
     * @param valueTypes          data element value types to include data for.
     * @param whereClause         where clause to constrain data query.
     */
private void populateTable(AnalyticsTable table, String valueExpression, String textValueExpression, Set<ValueType> valueTypes, String whereClause, String approvalClause) {
    final String start = DateUtils.getMediumDateString(table.getPeriod().getStartDate());
    final String end = DateUtils.getMediumDateString(table.getPeriod().getEndDate());
    final String tableName = table.getTempTableName();
    final String valTypes = TextUtils.getQuotedCommaDelimitedString(ObjectUtils.asStringList(valueTypes));
    final boolean respectStartEndDates = (Boolean) systemSettingManager.getSystemSetting(SettingKey.RESPECT_META_DATA_START_END_DATES_IN_ANALYTICS_TABLE_EXPORT);
    String sql = "insert into " + table.getTempTableName() + " (";
    List<AnalyticsTableColumn> columns = getDimensionColumns(table);
    validateDimensionColumns(columns);
    for (AnalyticsTableColumn col : columns) {
        sql += col.getName() + ",";
    }
    sql += "daysxvalue, daysno, value, textvalue) select ";
    for (AnalyticsTableColumn col : columns) {
        sql += col.getAlias() + ",";
    }
    sql += valueExpression + " * ps.daysno as daysxvalue, " + "ps.daysno as daysno, " + valueExpression + " as value, " + textValueExpression + " as textvalue " + "from datavalue dv " + "inner join _dataelementgroupsetstructure degs on dv.dataelementid=degs.dataelementid " + "inner join _organisationunitgroupsetstructure ougs on dv.sourceid=ougs.organisationunitid " + "inner join _categorystructure dcs on dv.categoryoptioncomboid=dcs.categoryoptioncomboid " + "inner join _categorystructure acs on dv.attributeoptioncomboid=acs.categoryoptioncomboid " + "left join _orgunitstructure ous on dv.sourceid=ous.organisationunitid " + "inner join _dataelementstructure des on dv.dataelementid = des.dataelementid " + "inner join dataelement de on dv.dataelementid=de.dataelementid " + "inner join categoryoptioncombo co on dv.categoryoptioncomboid=co.categoryoptioncomboid " + "inner join categoryoptioncombo ao on dv.attributeoptioncomboid=ao.categoryoptioncomboid " + "inner join period pe on dv.periodid=pe.periodid " + "inner join _periodstructure ps on dv.periodid=ps.periodid " + "inner join organisationunit ou on dv.sourceid=ou.organisationunitid " + "inner join _categoryoptioncomboname aon on dv.attributeoptioncomboid=aon.categoryoptioncomboid " + "inner join _categoryoptioncomboname con on dv.categoryoptioncomboid=con.categoryoptioncomboid " + approvalClause + "where de.valuetype in (" + valTypes + ") " + "and de.domaintype = 'AGGREGATE' " + "and pe.startdate >= '" + start + "' " + "and pe.startdate <= '" + end + "' " + "and dv.value is not null " + "and dv.deleted is false ";
    if (respectStartEndDates) {
        sql += "and (aon.startdate is null or aon.startdate <= pe.startdate) " + "and (aon.enddate is null or aon.enddate >= pe.enddate) " + "and (con.startdate is null or con.startdate <= pe.startdate) " + "and (con.enddate is null or con.enddate >= pe.enddate) ";
    }
    if (whereClause != null) {
        sql += "and " + whereClause;
    }
    populateAndLog(sql, tableName + ", " + valueTypes);
}

Example 2 with TextUtils.getQuotedCommaDelimitedString

use of org.hisp.dhis.commons.util.TextUtils.getQuotedCommaDelimitedString in project dhis2-core by dhis2.

the class JdbcAnalyticsTableManager method populateTable.

/**
 * Populates the given analytics table.
 *
 * @param valueExpression numeric value expression.
 * @param textValueExpression textual value expression.
 * @param valueTypes data element value types to include data for.
 * @param whereClause where clause to constrain data query.
 */
private void populateTable(AnalyticsTableUpdateParams params, AnalyticsTablePartition partition, String valueExpression, String textValueExpression, Set<ValueType> valueTypes, String whereClause) {
    final String tableName = partition.getTempTableName();
    final String valTypes = TextUtils.getQuotedCommaDelimitedString(ObjectUtils.asStringList(valueTypes));
    final boolean respectStartEndDates = systemSettingManager.getBoolSetting(SettingKey.RESPECT_META_DATA_START_END_DATES_IN_ANALYTICS_TABLE_EXPORT);
    final String approvalClause = getApprovalJoinClause(partition.getYear());
    final String partitionClause = partition.isLatestPartition() ? "and dv.lastupdated >= '" + getLongDateString(partition.getStartDate()) + "' " : "and ps.year = " + partition.getYear() + " ";
    String sql = "insert into " + partition.getTempTableName() + " (";
    List<AnalyticsTableColumn> columns = getDimensionColumns(partition.getYear());
    List<AnalyticsTableColumn> values = partition.getMasterTable().getValueColumns();
    validateDimensionColumns(columns);
    for (AnalyticsTableColumn col : ListUtils.union(columns, values)) {
        sql += col.getName() + ",";
    }
    sql = TextUtils.removeLastComma(sql) + ") select ";
    for (AnalyticsTableColumn col : columns) {
        sql += col.getAlias() + ",";
    }
    sql += valueExpression + " * ps.daysno as daysxvalue, " + "ps.daysno as daysno, " + valueExpression + " as value, " + textValueExpression + " as textvalue " + "from datavalue dv " + "inner join period pe on dv.periodid=pe.periodid " + "inner join _periodstructure ps on dv.periodid=ps.periodid " + "inner join dataelement de on dv.dataelementid=de.dataelementid " + "inner join _dataelementstructure des on dv.dataelementid = des.dataelementid " + "inner join _dataelementgroupsetstructure degs on dv.dataelementid=degs.dataelementid " + "inner join organisationunit ou on dv.sourceid=ou.organisationunitid " + "left join _orgunitstructure ous on dv.sourceid=ous.organisationunitid " + "inner join _organisationunitgroupsetstructure ougs on dv.sourceid=ougs.organisationunitid " + "and (cast(date_trunc('month', pe.startdate) as date)=ougs.startdate or ougs.startdate is null) " + "inner join categoryoptioncombo co on dv.categoryoptioncomboid=co.categoryoptioncomboid " + "inner join categoryoptioncombo ao on dv.attributeoptioncomboid=ao.categoryoptioncomboid " + "inner join _categorystructure dcs on dv.categoryoptioncomboid=dcs.categoryoptioncomboid " + "inner join _categorystructure acs on dv.attributeoptioncomboid=acs.categoryoptioncomboid " + "inner join _categoryoptioncomboname aon on dv.attributeoptioncomboid=aon.categoryoptioncomboid " + "inner join _categoryoptioncomboname con on dv.categoryoptioncomboid=con.categoryoptioncomboid " + approvalClause + "where de.valuetype in (" + valTypes + ") " + "and de.domaintype = 'AGGREGATE' " + partitionClause + "and dv.lastupdated < '" + getLongDateString(params.getStartTime()) + "' " + "and dv.value is not null " + "and dv.deleted is false ";
    if (respectStartEndDates) {
        sql += "and (aon.startdate is null or aon.startdate <= pe.startdate) " + "and (aon.enddate is null or aon.enddate >= pe.enddate) " + "and (con.startdate is null or con.startdate <= pe.startdate) " + "and (con.enddate is null or con.enddate >= pe.enddate) ";
    }
    if (whereClause != null) {
        sql += "and " + whereClause;
    }
    invokeTimeAndLog(sql, String.format("Populate %s %s", tableName, valueTypes));
}

Example 3 with TextUtils.getQuotedCommaDelimitedString

use of org.hisp.dhis.commons.util.TextUtils.getQuotedCommaDelimitedString in project dhis2-core by dhis2.

the class JdbcEventAnalyticsManager method getWhereClause.

/**
 * Returns a from and where SQL clause. If this is a program indicator with
 * non-default boundaries, the relationship with the reporting period is
 * specified with where conditions on the enrollment or incident dates. If
 * the default boundaries is used, or the query does not include program
 * indicators, the periods are joined in from the analytics tables the
 * normal way. A where clause can never have a mix of indicators with
 * non-default boundaries and regular analytics table periods.
 * <p>
 * If the query has a non-default time field specified, the query will use
 * the period type columns from the {@code date period structure} resource
 * table through an alias to reflect the period aggregation.
 *
 * @param params the {@link EventQueryParams}.
 */
@Override
protected String getWhereClause(EventQueryParams params) {
    String sql = "";
    SqlHelper hlp = new SqlHelper();
    // ---------------------------------------------------------------------
    // Periods
    // ---------------------------------------------------------------------
    sql += hlp.whereAnd() + " " + timeFieldSqlRenderer.renderTimeFieldSql(params);
    if (params.isOrganisationUnitMode(OrganisationUnitSelectionMode.SELECTED)) {
        String orgUnitCol = quoteAlias(params.getOrgUnitFieldFallback());
        sql += hlp.whereAnd() + " " + orgUnitCol + OPEN_IN + getQuotedCommaDelimitedString(getUids(params.getDimensionOrFilterItems(ORGUNIT_DIM_ID))) + ") ";
    } else if (params.isOrganisationUnitMode(OrganisationUnitSelectionMode.CHILDREN)) {
        String orgUnitCol = quoteAlias(params.getOrgUnitFieldFallback());
        sql += hlp.whereAnd() + " " + orgUnitCol + OPEN_IN + getQuotedCommaDelimitedString(getUids(params.getOrganisationUnitChildren())) + ") ";
    } else // Descendants
    {
        String orgUnitAlias = getOrgUnitAlias(params);
        String sqlSnippet = getOrgDescendantsSqlSnippet(orgUnitAlias, params.getDimensionOrFilterItems(ORGUNIT_DIM_ID));
        if (sqlSnippet != null && !sqlSnippet.trim().isEmpty()) {
            sql += hlp.whereAnd() + " " + sqlSnippet;
        }
    }
    // ---------------------------------------------------------------------
    // Organisation unit group sets
    // ---------------------------------------------------------------------
    List<DimensionalObject> dynamicDimensions = params.getDimensionsAndFilters(Sets.newHashSet(DimensionType.ORGANISATION_UNIT_GROUP_SET, DimensionType.CATEGORY));
    for (DimensionalObject dim : dynamicDimensions) {
        String col = quoteAlias(dim.getDimensionName());
        sql += hlp.whereAnd() + " " + col + OPEN_IN + getQuotedCommaDelimitedString(getUids(dim.getItems())) + ") ";
    }
    if (params.hasProgramStage()) {
        sql += hlp.whereAnd() + " " + quoteAlias("ps") + " = '" + params.getProgramStage().getUid() + "' ";
    }
    for (QueryItem item : params.getItems()) {
        if (item.hasFilter()) {
            for (QueryFilter filter : item.getFilters()) {
                String field = getSelectSql(filter, item, params.getEarliestStartDate(), params.getLatestEndDate());
                if (IN.equals(filter.getOperator())) {
                    InQueryFilter inQueryFilter = new InQueryFilter(field, statementBuilder.encode(filter.getFilter(), false), item.isText());
                    sql += hlp.whereAnd() + " " + inQueryFilter.getSqlFilter();
                } else {
                    sql += hlp.whereAnd() + " " + field + " " + filter.getSqlOperator() + " " + getSqlFilter(filter, item) + " ";
                }
            }
        }
    }
    for (QueryItem item : params.getItemFilters()) {
        if (item.hasFilter()) {
            for (QueryFilter filter : item.getFilters()) {
                sql += hlp.whereAnd() + " " + getSelectSql(filter, item, params.getEarliestStartDate(), params.getLatestEndDate()) + " " + filter.getSqlOperator() + " " + getSqlFilter(filter, item) + " ";
            }
        }
    }
    if (params.hasProgramIndicatorDimension() && params.getProgramIndicator().hasFilter()) {
        String filter = programIndicatorService.getAnalyticsSql(params.getProgramIndicator().getFilter(), params.getProgramIndicator(), params.getEarliestStartDate(), params.getLatestEndDate());
        String sqlFilter = ExpressionUtils.asSql(filter);
        sql += hlp.whereAnd() + " (" + sqlFilter + ") ";
    }
    if (params.hasProgramIndicatorDimension()) {
        String anyValueFilter = programIndicatorService.getAnyValueExistsClauseAnalyticsSql(params.getProgramIndicator().getExpression(), params.getProgramIndicator().getAnalyticsType());
        if (anyValueFilter != null) {
            sql += hlp.whereAnd() + " (" + anyValueFilter + ") ";
        }
    }
    if (params.hasProgramStatus()) {
        sql += hlp.whereAnd() + " pistatus in (" + params.getProgramStatus().stream().map(p -> encode(p.name(), true)).collect(joining(",")) + ") ";
    }
    if (params.hasEventStatus()) {
        sql += hlp.whereAnd() + " psistatus in (" + params.getEventStatus().stream().map(e -> encode(e.name(), true)).collect(joining(",")) + ") ";
    }
    if (params.isCoordinatesOnly() || params.isGeometryOnly()) {
        if (params.isCoordinateOuFallback()) {
            sql += hlp.whereAnd() + " (" + quoteAlias(resolveCoordinateFieldColumnName(params.getCoordinateField(), params)) + " is not null or " + quoteAlias(resolveCoordinateFieldColumnName(params.getFallbackCoordinateField(), params)) + " is not null )";
        } else {
            sql += hlp.whereAnd() + " " + quoteAlias(resolveCoordinateFieldColumnName(params.getCoordinateField(), params)) + " is not null ";
        }
    }
    if (params.isCompletedOnly()) {
        sql += hlp.whereAnd() + " completeddate is not null ";
    }
    if (params.hasBbox()) {
        sql += hlp.whereAnd() + " " + quoteAlias(params.getCoordinateField()) + " && ST_MakeEnvelope(" + params.getBbox() + ",4326) ";
    }
    if (!params.isSkipPartitioning() && params.hasPartitions() && !params.hasNonDefaultBoundaries() && !params.hasTimeField()) {
        sql += hlp.whereAnd() + " " + quoteAlias("yearly") + OPEN_IN + TextUtils.getQuotedCommaDelimitedString(params.getPartitions().getPartitions()) + ") ";
    }
    if (params.getAggregationTypeFallback().isFirstOrLastPeriodAggregationType()) {
        sql += hlp.whereAnd() + " " + quoteAlias("pe_rank") + " = 1 ";
    }
    return sql;
}