Examples with ANALYTICS_TBL_ALIAS org.hisp.dhis.analytics.util.AnalyticsSqlUtils.ANALYTICS_TBL_ALIAS used on opensource projects

Example 1 with ANALYTICS_TBL_ALIAS

use of org.hisp.dhis.analytics.util.AnalyticsSqlUtils.ANALYTICS_TBL_ALIAS in project dhis2-core by dhis2.

the class JdbcRawAnalyticsManager method getSelectStatement.

// -------------------------------------------------------------------------
// Supportive methods
// -------------------------------------------------------------------------
/**
 * Returns a SQL select statement.
 *
 * @param params the data query parameters.
 * @param dimensions the list of dimensions.
 * @return a SQL select statement.
 */
private String getSelectStatement(DataQueryParams params, List<DimensionalObject> dimensions) {
    String idScheme = ObjectUtils.firstNonNull(params.getOutputIdScheme(), IdScheme.UID).getIdentifiableString().toLowerCase();
    List<String> dimensionColumns = dimensions.stream().map(d -> asColumnSelect(d, idScheme)).collect(Collectors.toList());
    SqlHelper sqlHelper = new SqlHelper();
    String sql = "select " + StringUtils.join(dimensionColumns, ", ") + ", " + DIM_NAME_OU + ", value " + "from " + params.getTableName() + " as " + ANALYTICS_TBL_ALIAS + " " + "inner join organisationunit ou on ax.ou = ou.uid " + "inner join _orgunitstructure ous on ax.ou = ous.organisationunituid " + "inner join _periodstructure ps on ax.pe = ps.iso ";
    for (DimensionalObject dim : dimensions) {
        if (!dim.getItems().isEmpty() && !dim.isFixed()) {
            String col = quote(dim.getDimensionName());
            if (DimensionalObject.ORGUNIT_DIM_ID.equals(dim.getDimension())) {
                sql += sqlHelper.whereAnd() + " (";
                for (DimensionalItemObject item : dim.getItems()) {
                    OrganisationUnit unit = (OrganisationUnit) item;
                    sql += DIM_NAME_OU + " like '" + unit.getPath() + "%' or ";
                }
                sql = TextUtils.removeLastOr(sql) + ") ";
            } else {
                sql += sqlHelper.whereAnd() + " " + col + " in (" + getQuotedCommaDelimitedString(getUids(dim.getItems())) + ") ";
            }
        }
    }
    sql += sqlHelper.whereAnd() + " " + "ps.startdate >= '" + DateUtils.getMediumDateString(params.getStartDate()) + "' and " + "ps.enddate <= '" + DateUtils.getMediumDateString(params.getEndDate()) + "' ";
    return sql;
}