use of de.metas.ui.web.window.descriptor.sql.SqlSelectValue in project metasfresh-webui-api by metasfresh.
the class SqlViewSelectionQueryBuilder method buildSqlCreateSelectionFromSelectionLines.
public SqlAndParams buildSqlCreateSelectionFromSelectionLines(@NonNull final ViewEvaluationCtx viewEvalCtx, @NonNull final ViewId newViewId, @NonNull final DocumentQueryOrderByList orderBys) {
final String lineTableName = getTableName();
final String lineTableAlias = getTableAlias();
final SqlViewKeyColumnNamesMap keyColumnNamesMap = getSqlViewKeyColumnNamesMap();
final String lineKeyColumnName = keyColumnNamesMap.getSingleKeyColumnName();
final SqlOrderByBindings sqlOrderByBindings = fieldName -> {
if (keyColumnNamesMap.isKeyPartFieldName(fieldName)) {
return SqlOrderByValue.ofColumnName("sl", keyColumnNamesMap.getWebuiSelectionColumnNameForKeyColumnName(fieldName));
} else if (isGroupBy(fieldName)) {
return getFieldOrderBy(fieldName);
} else if (isAggregated(fieldName)) {
return SqlOrderByValue.builder().sqlSelectValue(getSqlAggregatedColumn(fieldName)).build();
} else {
// shall not happen
return null;
}
};
final DocumentQueryOrderByList orderBysEffective = orderBys.stream().flatMap(this::flatMapEffectiveFieldNames).filter(orderBy -> keyColumnNamesMap.isKeyPartFieldName(orderBy.getFieldName()) || isGroupBy(orderBy.getFieldName()) || isAggregated(orderBy.getFieldName())).collect(DocumentQueryOrderByList.toDocumentQueryOrderByList());
final String sqlOrderBy = SqlDocumentOrderByBuilder.newInstance(sqlOrderByBindings).joinOnTableNameOrAlias(lineTableAlias).useColumnNameAlias(false).buildSqlOrderBy(orderBysEffective).map(sqlOrderByExpr -> sqlOrderByExpr.evaluate(viewEvalCtx.toEvaluatee(), OnVariableNotFound.Fail)).orElseGet(() -> keyColumnNamesMap.getWebuiSelectionColumnNamesCommaSeparated("sl"));
final String sqlFrom = "SELECT " + "\n sl." + I_T_WEBUI_ViewSelectionLine.COLUMNNAME_UUID + "\n, " + keyColumnNamesMap.getWebuiSelectionColumnNamesCommaSeparated("sl") + "\n FROM " + I_T_WEBUI_ViewSelectionLine.Table_Name + " sl " + "\n INNER JOIN " + lineTableName + " " + lineTableAlias + " ON (" + lineTableAlias + "." + lineKeyColumnName + " = sl." + I_T_WEBUI_ViewSelectionLine.COLUMNNAME_Line_ID + // join lines
")" + "\n WHERE " + I_T_WEBUI_ViewSelectionLine.COLUMNNAME_UUID + "=?" + "\n GROUP BY " + "\n sl." + I_T_WEBUI_ViewSelectionLine.COLUMNNAME_UUID + "\n, " + keyColumnNamesMap.getWebuiSelectionColumnNamesCommaSeparated("sl") + "\n, " + getGroupByFieldNamesCommaSeparated() + "\n ORDER BY " + sqlOrderBy;
final String sqlCreateSelectionFromLines = "INSERT INTO " + I_T_WEBUI_ViewSelection.Table_Name + "(" + "\n " + I_T_WEBUI_ViewSelection.COLUMNNAME_UUID + "\n, " + keyColumnNamesMap.getWebuiSelectionColumnNamesCommaSeparated() + "\n, " + // SeqNo
I_T_WEBUI_ViewSelection.COLUMNNAME_Line + "\n)" + "\n SELECT " + "\n sl." + I_T_WEBUI_ViewSelectionLine.COLUMNNAME_UUID + "\n , " + keyColumnNamesMap.getWebuiSelectionColumnNamesCommaSeparated("sl") + // SeqNo
"\n , row_number() OVER ()" + "\n FROM (" + sqlFrom + ") sl";
final List<Object> sqlCreateSelectionFromLinesParams = Arrays.asList(newViewId.getViewId());
return SqlAndParams.of(sqlCreateSelectionFromLines, sqlCreateSelectionFromLinesParams);
}
Aggregations