Examples with SqlParamsCollector de.metas.ui.web.document.filter.sql.SqlParamsCollector used on opensource projects

Example 1 with SqlParamsCollector

use of de.metas.ui.web.document.filter.sql.SqlParamsCollector in project metasfresh-webui-api by metasfresh.

the class ProductLookupDescriptor method retrieveEntities.

@Override
public LookupValuesList retrieveEntities(final LookupDataSourceContext evalCtx) {
    final SqlParamsCollector sqlParams = SqlParamsCollector.newInstance();
    final String sql = buildSql(sqlParams, evalCtx);
    if (sql == null) {
        return LookupValuesList.EMPTY;
    }
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        pstmt = DB.prepareStatement(sql, ITrx.TRXNAME_None);
        DB.setParameters(pstmt, sqlParams.toList());
        rs = pstmt.executeQuery();
        final Map<Integer, LookupValue> valuesById = new LinkedHashMap<>();
        while (rs.next()) {
            final LookupValue value = loadLookupValue(rs);
            valuesById.putIfAbsent(value.getIdAsInt(), value);
        }
        final LookupValuesList unexplodedLookupValues = LookupValuesList.fromCollection(valuesById.values());
        final Date stockdateOrNull = getEffectiveStockDateOrNull(evalCtx);
        if (stockdateOrNull == null || availableToPromiseAdapter == null) {
            return unexplodedLookupValues;
        }
        return explodeRecordsWithStockQuantities(unexplodedLookupValues, stockdateOrNull);
    } catch (final SQLException ex) {
        throw new DBException(ex, sql, sqlParams.toList());
    } finally {
        DB.close(rs, pstmt);
    }
}

Example 2 with SqlParamsCollector

use of de.metas.ui.web.document.filter.sql.SqlParamsCollector in project metasfresh-webui-api by metasfresh.

the class SqlDocumentQueryBuilder method buildSqlWhereClause.

private IPair<IStringExpression, List<Object>> buildSqlWhereClause() {
    final SqlParamsCollector sqlParams = SqlParamsCollector.newInstance();
    final CompositeStringExpression.Builder sqlWhereClauseBuilder = IStringExpression.composer();
    // 
    // Entity's WHERE clause
    {
        final IStringExpression entityWhereClauseExpression = entityBinding.getSqlWhereClause();
        if (!entityWhereClauseExpression.isNullExpression()) {
            sqlWhereClauseBuilder.appendIfNotEmpty("\n AND ");
            sqlWhereClauseBuilder.append(" /* entity where clause */ (").append(entityWhereClauseExpression).append(")");
        }
    }
    // 
    // Key column
    // FIXME: handle AD_Reference/AD_Ref_List(s). In that case the recordId will be AD_Ref_List.Value,
    // so the SQL where clause which is currently build is AD_Ref_List_ID=<the AD_Ref_List.Value>.
    // The build SQL where clause shall be something like AD_Reference_ID=<the reference, i think we shall fetch it somehow from Lookup> AND Value=<the value, which currently is the recordId>
    final DocumentId recordId = getRecordId();
    if (recordId != null) {
        final List<SqlDocumentFieldDataBindingDescriptor> keyFields = entityBinding.getKeyFields();
        if (keyFields.isEmpty()) {
            throw new AdempiereException("Failed building where clause because there is no Key Column defined in " + entityBinding);
        } else // Single primary key
        if (keyFields.size() == 1) {
            final String keyColumnName = keyFields.get(0).getColumnName();
            sqlWhereClauseBuilder.appendIfNotEmpty("\n AND ");
            sqlWhereClauseBuilder.append(" /* key */ ").append(keyColumnName).append("=").append(sqlParams.placeholder(recordId.toInt()));
        } else // Composed primary key
        {
            final Map<String, Object> keyColumnName2value = extractComposedKey(recordId, keyFields);
            keyColumnName2value.forEach((keyColumnName, value) -> {
                sqlWhereClauseBuilder.appendIfNotEmpty("\n AND ");
                sqlWhereClauseBuilder.append(" /* key */ ").append(keyColumnName).append("=").append(sqlParams.placeholder(value));
            });
        }
    }
    // 
    // Parent link where clause (if any)
    final Document parentDocument = getParentDocument();
    if (parentDocument != null) {
        final String parentLinkColumnName = entityBinding.getParentLinkColumnName();
        final String linkColumnName = entityBinding.getLinkColumnName();
        if (parentLinkColumnName != null && linkColumnName != null) {
            final IDocumentFieldView parentLinkField = parentDocument.getFieldView(parentLinkColumnName);
            final Object parentLinkValue = parentLinkField.getValue();
            final DocumentFieldWidgetType parentLinkWidgetType = parentLinkField.getWidgetType();
            final Class<?> targetClass = entityBinding.getFieldByFieldName(linkColumnName).getSqlValueClass();
            final Object sqlParentLinkValue = SqlDocumentsRepository.convertValueToPO(parentLinkValue, parentLinkColumnName, parentLinkWidgetType, targetClass);
            sqlWhereClauseBuilder.appendIfNotEmpty("\n AND ");
            sqlWhereClauseBuilder.append(" /* parent link */ ").append(linkColumnName).append("=").append(sqlParams.placeholder(sqlParentLinkValue));
        }
    }
    // 
    // Document filters
    {
        final String sqlFilters = SqlDocumentFilterConverters.createEntityBindingEffectiveConverter(entityBinding).getSql(sqlParams, getDocumentFilters(), SqlOptions.usingTableAlias(entityBinding.getTableAlias()));
        if (!Check.isEmpty(sqlFilters, true)) {
            sqlWhereClauseBuilder.appendIfNotEmpty("\n AND ");
            sqlWhereClauseBuilder.append(" /* filters */ (\n").append(sqlFilters).append(")\n");
        }
    }
    // Build the final SQL where clause
    return ImmutablePair.of(sqlWhereClauseBuilder.build(), Collections.unmodifiableList(sqlParams.toList()));
}

Example 3 with SqlParamsCollector

use of de.metas.ui.web.document.filter.sql.SqlParamsCollector in project metasfresh-webui-api by metasfresh.

the class ProductLookupDescriptor method buildSql.

private String buildSql(@NonNull final SqlParamsCollector sqlParams, @NonNull final LookupDataSourceContext evalCtx) {
    // 
    // Get language
    final String adLanguage = evalCtx.getAD_Language();
    final boolean isBaseLanguage = Language.isBaseLanguage(adLanguage);
    final String trlAlias = isBaseLanguage ? "p" : "trl";
    // 
    // Build the SQL filter
    final StringBuilder sqlWhereClause = new StringBuilder();
    final SqlParamsCollector sqlWhereClauseParams = SqlParamsCollector.newInstance();
    appendFilterByIsActive(sqlWhereClause, sqlWhereClauseParams);
    appendFilterBySearchString(sqlWhereClause, sqlWhereClauseParams, evalCtx, trlAlias);
    appendFilterById(sqlWhereClause, sqlWhereClauseParams, evalCtx);
    appendFilterByBPartner(sqlWhereClause, sqlWhereClauseParams, evalCtx);
    appendFilterByPriceList(sqlWhereClause, sqlWhereClauseParams, evalCtx);
    appendFilterByNotFreightCostProduct(sqlWhereClause, sqlWhereClauseParams, evalCtx);
    appendFilterByOrg(sqlWhereClause, sqlWhereClauseParams, evalCtx);
    // 
    // SQL: SELECT ... FROM
    final StringBuilder sql = new StringBuilder("SELECT" + "\n p." + I_M_Product_Lookup_V.COLUMNNAME_M_Product_ID + "\n, p." + I_M_Product_Lookup_V.COLUMNNAME_Value + "\n, " + trlAlias + "." + I_M_Product_Lookup_V.COLUMNNAME_Name + "\n, p." + I_M_Product_Lookup_V.COLUMNNAME_UPC + "\n, p." + I_M_Product_Lookup_V.COLUMNNAME_BPartnerProductNo + "\n, p." + I_M_Product_Lookup_V.COLUMNNAME_BPartnerProductName + "\n FROM " + I_M_Product_Lookup_V.Table_Name + " p ");
    if (!isBaseLanguage) {
        sql.append("\n INNER JOIN M_Product_Trl trl ON (trl.M_Product_ID=p.M_Product_ID AND trl.AD_Language=").append(sqlParams.placeholder(adLanguage)).append(")");
    }
    // 
    // SQL: WHERE
    sql.append("\n WHERE ").append(sqlWhereClause);
    sqlParams.collect(sqlWhereClauseParams);
    // 
    // SQL: ORDER BY
    sql.append("\n ORDER BY ").append(trlAlias + "." + I_M_Product_Lookup_V.COLUMNNAME_Name).append(", p." + I_M_Product_Lookup_V.COLUMNNAME_C_BPartner_ID + " DESC NULLS LAST");
    // SQL: LIMIT and OFFSET
    sql.append("\n LIMIT ").append(sqlParams.placeholder(evalCtx.getLimit(100)));
    sql.append("\n OFFSET ").append(sqlParams.placeholder(evalCtx.getOffset(0)));
    return sql.toString();
}