Examples with SqlSelectValue de.metas.ui.web.window.descriptor.sql.SqlSelectValue used on opensource projects

Example 1 with SqlSelectValue

use of de.metas.ui.web.window.descriptor.sql.SqlSelectValue in project metasfresh-webui-api by metasfresh.

the class HUEditorViewFactoryTemplate method createSqlViewBinding.

private SqlViewBinding createSqlViewBinding() {
    // Get HU's standard entity descriptor. We will needed all over.
    final DocumentEntityDescriptor huEntityDescriptor = getHUEntityDescriptor();
    // 
    // Static where clause
    final StringBuilder sqlWhereClause = new StringBuilder();
    {
        // top level
        sqlWhereClause.append(I_M_HU.COLUMNNAME_M_HU_Item_Parent_ID + " is null");
        // Consider window tab's where clause if any
        final I_AD_Tab huTab = Services.get(IADWindowDAO.class).retrieveFirstTab(WEBUI_HU_Constants.WEBUI_HU_Window_ID.toAdWindowId());
        if (!Check.isEmpty(huTab.getWhereClause(), true)) {
            sqlWhereClause.append("\n AND (").append(huTab.getWhereClause()).append(")");
        }
    }
    // 
    // Start preparing the sqlViewBinding builder
    final List<String> displayFieldNames = ImmutableList.of(I_M_HU.COLUMNNAME_M_HU_ID);
    final SqlViewBinding.Builder sqlViewBinding = SqlViewBinding.builder().tableName(I_M_HU.Table_Name).displayFieldNames(displayFieldNames).sqlWhereClause(sqlWhereClause.toString()).rowIdsConverter(HUSqlViewRowIdsConverter.instance);
    // 
    // View fields: from M_HU's entity descriptor
    {
        // NOTE: we need to add all HU's standard fields because those might be needed for some of the standard filters defined
        final SqlDocumentEntityDataBindingDescriptor huEntityBindings = SqlDocumentEntityDataBindingDescriptor.cast(huEntityDescriptor.getDataBinding());
        huEntityBindings.getFields().stream().map(huField -> SqlViewBindingFactory.createViewFieldBinding(huField, displayFieldNames)).forEach(sqlViewBinding::field);
    }
    // 
    // View field: BestBeforeDate
    {
        final String sqlBestBeforeDate = HUAttributeConstants.sqlBestBeforeDate(sqlViewBinding.getTableAlias() + "." + I_M_HU.COLUMNNAME_M_HU_ID);
        sqlViewBinding.field(SqlViewRowFieldBinding.builder().fieldName(HUEditorRow.FIELDNAME_BestBeforeDate).widgetType(DocumentFieldWidgetType.LocalDate).sqlSelectValue(SqlSelectValue.builder().virtualColumnSql(sqlBestBeforeDate).columnNameAlias(HUEditorRow.FIELDNAME_BestBeforeDate).build()).fieldLoader((rs, adLanguage) -> rs.getTimestamp(HUEditorRow.FIELDNAME_BestBeforeDate)).build());
    }
    // 
    // View filters and converters
    {
        sqlViewBinding.filterDescriptors(createFilterDescriptorsProvider()).filterConverter(HUBarcodeSqlDocumentFilterConverter.instance).filterConverter(HUIdsFilterHelper.SQL_DOCUMENT_FILTER_CONVERTER).filterConverters(createFilterConverters());
    }
    // 
    return sqlViewBinding.build();
}

Example 2 with SqlSelectValue

use of de.metas.ui.web.window.descriptor.sql.SqlSelectValue in project metasfresh-webui-api by metasfresh.

the class SqlDefaultDocumentFilterConverter method replaceTableNameWithTableAliasIfNeeded.

@VisibleForTesting
SqlSelectValue replaceTableNameWithTableAliasIfNeeded(@NonNull final SqlSelectValue columnSql, @NonNull final SqlOptions sqlOpts) {
    if (sqlOpts.isUseTableAlias()) {
        SqlSelectValue columnSqlEffective = columnSql;
        columnSqlEffective = columnSqlEffective.withJoinOnTableNameOrAlias(sqlOpts.getTableAlias());
        if (columnSqlEffective.isVirtualColumn()) {
            final String virtualColumnSql = replaceTableNameWithTableAlias(columnSqlEffective.getVirtualColumnSql(), sqlOpts.getTableAlias());
            columnSqlEffective = columnSqlEffective.withVirtualColumnSql(virtualColumnSql);
        }
        return columnSqlEffective;
    } else {
        return columnSql;
    }
}

Example 3 with SqlSelectValue

use of de.metas.ui.web.window.descriptor.sql.SqlSelectValue in project metasfresh-webui-api by metasfresh.

the class SqlViewSelectData method selectFieldValues.

public SqlAndParams selectFieldValues(@NonNull final ViewEvaluationCtx viewEvalCtx, @NonNull final String selectionId, @NonNull final String fieldName, final int limit) {
    Check.assumeGreaterThanZero(limit, "limit");
    final SqlViewRowFieldBinding field = fieldsByFieldName.get(fieldName);
    if (field == null) {
        throw new AdempiereException("Field `" + fieldName + "` not found. Available fields are: " + fieldsByFieldName.keySet());
    }
    final SqlSelectValue sqlValue = field.getSqlSelectValue();
    final SqlSelectDisplayValue sqlDisplayValue;
    if (field.getSqlSelectDisplayValue() != null && displayFieldNames.contains(fieldName)) {
        sqlDisplayValue = field.getSqlSelectDisplayValue();
    } else {
        sqlDisplayValue = null;
    }
    final CompositeStringExpression.Builder sqlExpression = IStringExpression.composer().append("SELECT DISTINCT ").append(sqlValue.getColumnNameAlias());
    if (sqlDisplayValue != null) {
        sqlExpression.append(", ").append(sqlDisplayValue.getColumnNameAlias());
    }
    sqlExpression.append("\n FROM (").append("\n SELECT ").append("\n ").append(sqlValue.withJoinOnTableNameOrAlias(sqlTableName).toSqlStringWithColumnNameAlias());
    if (sqlDisplayValue != null) {
        sqlExpression.append("\n, ").append(sqlDisplayValue.withJoinOnTableNameOrAlias(sqlTableName).toStringExpressionWithColumnNameAlias());
    }
    sqlExpression.append("\n FROM " + I_T_WEBUI_ViewSelection.Table_Name + " sel").append("\n INNER JOIN " + sqlTableName + " ON (" + keyColumnNamesMap.getSqlJoinCondition(sqlTableName, "sel") + ")").append("\n WHERE sel." + I_T_WEBUI_ViewSelection.COLUMNNAME_UUID + "=?").append("\n ORDER BY sel." + I_T_WEBUI_ViewSelection.COLUMNNAME_Line).append("\n) t").append("\n LIMIT ?");
    final String sql = sqlExpression.build().evaluate(viewEvalCtx.toEvaluatee(), OnVariableNotFound.Fail);
    return SqlAndParams.of(sql, selectionId, limit);
}

Example 4 with SqlSelectValue

use of de.metas.ui.web.window.descriptor.sql.SqlSelectValue in project metasfresh-webui-api by metasfresh.

the class SqlDefaultDocumentFilterConverter method buildSqlWhereClause_StandardWidget.

private String buildSqlWhereClause_StandardWidget(final SqlParamsCollector sqlParams, final DocumentFilterParam filterParam, final SqlOptions sqlOpts) {
    final SqlEntityFieldBinding paramBinding = getParameterBinding(filterParam.getFieldName());
    final DocumentFieldWidgetType widgetType = paramBinding.getWidgetType();
    final String columnSqlString;
    {
        final IQueryFilterModifier fieldModifier = extractFieldModifier(widgetType);
        final SqlSelectValue columnSqlEffective = replaceTableNameWithTableAliasIfNeeded(paramBinding.getSqlSelectValue(), sqlOpts);
        columnSqlString = fieldModifier.getColumnSql(columnSqlEffective.toSqlString());
    }
    final IQueryFilterModifier valueModifier = extractValueModifier(widgetType);
    final Operator operator = filterParam.getOperator();
    switch(operator) {
        case EQUAL:
            {
                final Object sqlValue = convertToSqlValue(filterParam.getValue(), paramBinding, valueModifier);
                final boolean negate = false;
                return buildSqlWhereClause_Equals(columnSqlString, sqlValue, negate, sqlParams);
            }
        case NOT_EQUAL:
            {
                final Object sqlValue = convertToSqlValue(filterParam.getValue(), paramBinding, valueModifier);
                final boolean negate = true;
                return buildSqlWhereClause_Equals(columnSqlString, sqlValue, negate, sqlParams);
            }
        case IN_ARRAY:
            {
                final List<Object> sqlValuesList = filterParam.getValueAsList(itemObj -> convertToSqlValue(itemObj, paramBinding, valueModifier));
                return buildSqlWhereClause_InArray(columnSqlString, sqlValuesList, sqlParams);
            }
        case GREATER:
            {
                final Object sqlValue = convertToSqlValue(filterParam.getValue(), paramBinding, valueModifier);
                return buildSqlWhereClause_Compare(columnSqlString, ">", sqlValue, sqlParams);
            }
        case GREATER_OR_EQUAL:
            {
                final Object sqlValue = convertToSqlValue(filterParam.getValue(), paramBinding, valueModifier);
                return buildSqlWhereClause_Compare(columnSqlString, ">=", sqlValue, sqlParams);
            }
        case LESS:
            {
                final Object sqlValue = convertToSqlValue(filterParam.getValue(), paramBinding, valueModifier);
                return buildSqlWhereClause_Compare(columnSqlString, "<", sqlValue, sqlParams);
            }
        case LESS_OR_EQUAL:
            {
                final Object sqlValue = convertToSqlValue(filterParam.getValue(), paramBinding, valueModifier);
                return buildSqlWhereClause_Compare(columnSqlString, "<=", sqlValue, sqlParams);
            }
        case LIKE:
            {
                final Object sqlValue = convertToSqlValue(filterParam.getValue(), paramBinding, valueModifier);
                final boolean negate = false;
                final boolean ignoreCase = false;
                return buildSqlWhereClause_Like(columnSqlString, negate, ignoreCase, sqlValue, sqlParams);
            }
        case NOT_LIKE:
            {
                final Object sqlValue = convertToSqlValue(filterParam.getValue(), paramBinding, valueModifier);
                final boolean negate = true;
                final boolean ignoreCase = false;
                return buildSqlWhereClause_Like(columnSqlString, negate, ignoreCase, sqlValue, sqlParams);
            }
        case LIKE_I:
            {
                final Object sqlValue = convertToSqlValue(filterParam.getValue(), paramBinding, valueModifier);
                final boolean negate = false;
                final boolean ignoreCase = true;
                return buildSqlWhereClause_Like(columnSqlString, negate, ignoreCase, sqlValue, sqlParams);
            }
        case NOT_LIKE_I:
            {
                final Object sqlValue = convertToSqlValue(filterParam.getValue(), paramBinding, valueModifier);
                final boolean negate = true;
                final boolean ignoreCase = true;
                return buildSqlWhereClause_Like(columnSqlString, negate, ignoreCase, sqlValue, sqlParams);
            }
        case BETWEEN:
            {
                final Object sqlValue = convertToSqlValue(filterParam.getValue(), paramBinding, valueModifier);
                final Object sqlValueTo = convertToSqlValue(filterParam.getValueTo(), paramBinding, valueModifier);
                return buildSqlWhereClause_Between(columnSqlString, sqlValue, sqlValueTo, sqlParams);
            }
        default:
            {
                throw new IllegalArgumentException("Operator not supported: " + operator);
            }
    }
}

Example 5 with SqlSelectValue

use of de.metas.ui.web.window.descriptor.sql.SqlSelectValue in project metasfresh-webui-api by metasfresh.

the class SqlViewSelectionQueryBuilder method buildSqlCreateSelectionFromSelection.

/**
 * @return
 *
 *         <pre>
 * INSERT INTO T_WEBUI_ViewSelection (UUID, Line, keys)
 * SELECT ... FROM T_WEBUI_ViewSelection sel INNER JOIN ourTable WHERE sel.UUID=[fromUUID]
 *         </pre>
 */
public SqlAndParams buildSqlCreateSelectionFromSelection(@NonNull final ViewEvaluationCtx viewEvalCtx, @NonNull final ViewId newViewId, @NonNull final String fromSelectionId, @NonNull final DocumentFilterList filters, @NonNull final DocumentQueryOrderByList orderBys, @NonNull final SqlDocumentFilterConverterContext filterConverterCtx) {
    final String sqlTableAlias = getTableAlias();
    final SqlViewKeyColumnNamesMap keyColumnNamesMap = getSqlViewKeyColumnNamesMap();
    final DocumentQueryOrderByList orderBysEffective = orderBys.stream().flatMap(this::flatMapEffectiveFieldNames).collect(DocumentQueryOrderByList.toDocumentQueryOrderByList());
    // 
    // Build the table we will join.
    final SqlAndParams sqlSourceTable;
    {
        final Set<String> addedFieldNames = new HashSet<>();
        final StringBuilder sqlKeyColumnNames;
        {
            sqlKeyColumnNames = new StringBuilder();
            for (final String keyColumnName : keyColumnNamesMap.getKeyColumnNames()) {
                if (!addedFieldNames.add(keyColumnName)) {
                    continue;
                }
                if (sqlKeyColumnNames.length() > 0) {
                    sqlKeyColumnNames.append("\n, ");
                }
                sqlKeyColumnNames.append(getSqlSelectValue(keyColumnName).withColumnNameAlias(keyColumnName).toSqlStringWithColumnNameAlias());
            }
        }
        final SqlAndParams.Builder sqlSourceTableBuilder = SqlAndParams.builder();
        sqlSourceTableBuilder.append("(SELECT ").append(sqlKeyColumnNames);
        for (final DocumentQueryOrderBy orderBy : orderBysEffective.toList()) {
            final String fieldName = orderBy.getFieldName();
            final SqlSelectDisplayValue sqlSelectDisplayValue = getSqlSelectDisplayValue(fieldName);
            if (sqlSelectDisplayValue != null && addedFieldNames.add(sqlSelectDisplayValue.getColumnNameAlias())) {
                sqlSourceTableBuilder.append("\n, ").append(sqlSelectDisplayValue.withJoinOnTableNameOrAlias(getTableName()).toSqlStringWithColumnNameAlias(viewEvalCtx.toEvaluatee()));
            }
            final SqlSelectValue sqlSelectValue = getSqlSelectValue(fieldName);
            if (sqlSelectValue != null && addedFieldNames.add(sqlSelectValue.getColumnNameAlias())) {
                sqlSourceTableBuilder.append("\n, ").append(sqlSelectValue.withJoinOnTableNameOrAlias(getTableName()).toSqlStringWithColumnNameAlias());
            }
        }
        sqlSourceTableBuilder.append("\n FROM ").append(getTableName());
        final SqlAndParams sqlFilters = buildSqlFiltersOrNull(filters, filterConverterCtx, SqlOptions.usingTableName(getTableName()));
        if (sqlFilters != null) {
            sqlSourceTableBuilder.append("\n WHERE ").append(sqlFilters);
        }
        sqlSourceTableBuilder.append(")");
        sqlSourceTable = sqlSourceTableBuilder.build();
    }
    // 
    // Order BY
    final String sqlOrderBys = SqlDocumentOrderByBuilder.newInstance(this::getFieldOrderBy).joinOnTableNameOrAlias(sqlTableAlias).useColumnNameAlias(true).buildSqlOrderBy(orderBysEffective).map(sqlOrderBysExpr -> sqlOrderBysExpr.evaluate(viewEvalCtx.toEvaluatee(), OnVariableNotFound.Fail)).map(sql -> _viewBinding.replaceTableNameWithTableAlias(sql, sqlTableAlias)).orElse(null);
    // 
    final String sqlJoinCondition = keyColumnNamesMap.getSqlJoinCondition(sqlTableAlias, "sel");
    // 
    return SqlAndParams.builder().append("INSERT INTO " + I_T_WEBUI_ViewSelection.Table_Name + " (" + " " + I_T_WEBUI_ViewSelection.COLUMNNAME_UUID + ", " + I_T_WEBUI_ViewSelection.COLUMNNAME_Line + ", " + keyColumnNamesMap.getWebuiSelectionColumnNamesCommaSeparated() + ")").append("\n SELECT ").append("\n  ?", // newUUID
    newViewId.getViewId()).append("\n, ").append("row_number() OVER (").append(sqlOrderBys != null ? "ORDER BY " + sqlOrderBys : "").append(// Line
    ")").append("\n, ").append(// keys
    keyColumnNamesMap.getKeyColumnNamesCommaSeparated()).append("\n FROM ").append(I_T_WEBUI_ViewSelection.Table_Name).append(" sel").append("\n INNER JOIN ").append(sqlSourceTable).append(" ").append(sqlTableAlias).append(" ON (").append(sqlJoinCondition).append(")").append("\n WHERE sel.").append(I_T_WEBUI_ViewSelection.COLUMNNAME_UUID).append("=?", // fromUUID
    fromSelectionId).build();
}