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

Example 1 with SqlSelectDisplayValue

use of de.metas.ui.web.window.descriptor.sql.SqlSelectDisplayValue 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 2 with SqlSelectDisplayValue

use of de.metas.ui.web.window.descriptor.sql.SqlSelectDisplayValue 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();
}