Examples with IQuerySort com.servoy.j2db.query.IQuerySort used on opensource projects

Example 6 with IQuerySort

use of com.servoy.j2db.query.IQuerySort in project servoy-client by Servoy.

the class PartNode method process.

public List<DataRendererDefinition> process(FormPreviewPanel fpp, FoundSet fs, Table table, QuerySelect sqlString) throws Exception {
    // Selection model must be in print mode to be able to set the selection to -1  . Otherwise is not allowed by the selectionModel
    ((ISwingFoundSet) fs).getSelectionModel().hideSelectionForPrinting();
    // this is needed because we must keep sql the same in foundset during printing
    FoundSet rootSet = (FoundSet) fs.copy(false);
    foundSets.add(rootSet);
    IApplication app = fpp.getApplication();
    // retval
    List<DataRendererDefinition> list = new ArrayList<DataRendererDefinition>();
    if (part != null && (part.getPartType() == Part.LEADING_SUBSUMMARY || part.getPartType() == Part.TRAILING_SUBSUMMARY || isLeadingAndTrailingSubsummary)) {
        QuerySelect newSQLString = AbstractBaseQuery.deepClone(sqlString);
        IDataServer server = app.getDataServer();
        // build the sql parts  based on sort columns
        ArrayList<IQuerySelectValue> selectCols = new ArrayList<IQuerySelectValue>();
        ArrayList<QueryColumn> groupbyCols = new ArrayList<QueryColumn>();
        ArrayList<QuerySort> sortbyCols = new ArrayList<QuerySort>();
        for (SortColumn element : sortColumns) {
            BaseQueryTable queryTable = sqlString.getTable();
            Relation[] relations = element.getRelations();
            if (relations != null) {
                for (Relation relation : relations) {
                    ISQLTableJoin join = (ISQLTableJoin) sqlString.getJoin(queryTable, relation.getName());
                    if (join == null) {
                        // $NON-NLS-1$ //$NON-NLS-2$
                        Debug.log("Missing relation " + relation.getName() + " in join condition for form on table " + table.getName());
                    } else {
                        queryTable = join.getForeignTable();
                    }
                }
            }
            Column column = (Column) element.getColumn();
            QueryColumn queryColumn = column.queryColumn(queryTable);
            selectCols.add(queryColumn);
            groupbyCols.add(queryColumn);
            sortbyCols.add(new QuerySort(queryColumn, element.getSortOrder() == SortColumn.ASCENDING, fs.getFoundSetManager().getSortOptions(column)));
        }
        // make sql
        for (AggregateVariable ag : allAggregates) {
            selectCols.add(new QueryAggregate(ag.getType(), new QueryColumn(newSQLString.getTable(), -1, ag.getColumnNameToAggregate(), ag.getDataProviderType(), ag.getLength(), 0, null, ag.getFlags()), ag.getName()));
        }
        newSQLString.setColumns(selectCols);
        newSQLString.setGroupBy(groupbyCols);
        ArrayList<IQuerySort> oldSort = newSQLString.getSorts();
        // fix the sort (if columns not are selected of used in groupby they cannot be used in sort)
        newSQLString.setSorts(sortbyCols);
        FoundSetManager foundSetManager = ((FoundSetManager) app.getFoundSetManager());
        String transaction_id = foundSetManager.getTransactionID(table.getServerName());
        IDataSet data = server.performQuery(app.getClientID(), table.getServerName(), transaction_id, newSQLString, null, foundSetManager.getTableFilterParams(table.getServerName(), newSQLString), false, 0, foundSetManager.config.pkChunkSize() * 4, IDataServer.PRINT_QUERY);
        // create a new FoundSet with 'data' and with right 'table', 'where','whereArgs'
        SubSummaryFoundSet newSet = new SubSummaryFoundSet(app.getFoundSetManager(), rootSet, sortColumns, allAggregates, data, table);
        // restore the sort for child body parts
        newSQLString.setSorts(oldSort);
        // make new where for use in sub queries
        for (QuerySort sortbyCol : sortbyCols) {
            QueryColumn sc = (QueryColumn) (sortbyCol).getColumn();
            newSQLString.addCondition(SQLGenerator.CONDITION_SEARCH, new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, sc, new Placeholder(new TablePlaceholderKey(sc.getTable(), '#' + sc.getName()))));
        }
        int count = newSet.getSize();
        for (int ii = 0; ii < count; ii++) {
            // make copy for setting sort column
            QuerySelect newSQLStringCopy = AbstractBaseQuery.deepClone(newSQLString);
            // handle the child first, this puts the rootset in the right state! for use of related(!) fields in the subsums
            // THIS is EXTREMELY important for correct printing, see also SubSummaryFoundSet.queryForRelatedFoundSet
            List<DataRendererDefinition> childRetval = null;
            IFoundSetInternal curLeafFoundSet = null;
            if (child != null) {
                for (int i = 0; i < sortbyCols.size(); i++) {
                    QueryColumn sc = (QueryColumn) (sortbyCols.get(i)).getColumn();
                    TablePlaceholderKey placeholderKey = new TablePlaceholderKey(sc.getTable(), '#' + sc.getName());
                    if (!newSQLStringCopy.setPlaceholderValue(placeholderKey, data.getRow(ii)[i])) {
                        Debug.error(// $NON-NLS-1$//$NON-NLS-2$ //$NON-NLS-3$
                        new RuntimeException("Could not set placeholder " + placeholderKey + " in query " + newSQLStringCopy + "-- continuing"));
                    }
                }
                childRetval = child.process(fpp, rootSet, table, newSQLStringCopy);
                curLeafFoundSet = child.getCurrentLeafFoundSet();
            }
            SubSummaryFoundSet.PrintState state = (SubSummaryFoundSet.PrintState) newSet.getRecord(ii);
            state.setDelegate(curLeafFoundSet);
            if (part.getPartType() == Part.LEADING_SUBSUMMARY) {
                state.doAggregatesLookup();
                list.add(new DataRendererDefinition(fpp, renderParent, part, renderer, state));
            }
            if (childRetval != null) {
                list.addAll(childRetval);
            }
            if (isLeadingAndTrailingSubsummary) {
                state.doAggregatesLookup();
                list.add(new DataRendererDefinition(fpp, renderParent, second_part, second_renderer, state));
            } else if (part.getPartType() == Part.TRAILING_SUBSUMMARY) {
                state.doAggregatesLookup();
                list.add(new DataRendererDefinition(fpp, renderParent, part, renderer, state));
            }
        }
    } else // for handeling (virtual) body part
    {
        rootSet.browseAll(sqlString);
        int count = app.getFoundSetManager().getFoundSetCount(rootSet);
        for (int ii = 0; ii < count; ii++) {
            currentLeafFoundSet = rootSet;
            list.add(new DataRendererDefinition(fpp, renderParent, part, renderer, rootSet, ii));
        }
    }
    return list;
}

Example 7 with IQuerySort

use of com.servoy.j2db.query.IQuerySort in project servoy-client by Servoy.

the class LookupListModel method fillDBValueListValues.

/**
 * @param txt
 * @throws Exception
 * @throws RepositoryException
 * @throws RemoteException
 */
private void fillDBValueListValues(String filter) throws ServoyException {
    ValueList valueList = ((LookupValueList) lookup).getValueList();
    QuerySelect sqlParts = AbstractBaseQuery.deepClone(creationSQLParts);
    if (!generateWherePart(filter, valueList, sqlParts, sqlParts.getTable())) {
        ArrayList<IQuerySort> sorts = getSortColumnsForQuery(sqlParts);
        if (sorts != null)
            sqlParts.setSorts(sorts);
    }
    try {
        FoundSetManager foundSetManager = ((FoundSetManager) application.getFoundSetManager());
        String transaction_id = foundSetManager.getTransactionID(table.getServerName());
        ArrayList<TableFilter> tableFilterParams = foundSetManager.getTableFilterParams(table.getServerName(), sqlParts);
        if (// apply name as filter on column valuelist_name in creationSQLParts
        nameFilter != null) {
            if (tableFilterParams == null) {
                tableFilterParams = new ArrayList<TableFilter>();
            }
            tableFilterParams.add(nameFilter);
        }
        SQLStatement trackingInfo = null;
        if (foundSetManager.getEditRecordList().hasAccess(table, IRepository.TRACKING_VIEWS)) {
            trackingInfo = new SQLStatement(ISQLActionTypes.SELECT_ACTION, table.getServerName(), table.getName(), null, null);
            trackingInfo.setTrackingData(sqlParts.getColumnNames(), new Object[][] {}, new Object[][] {}, application.getUserUID(), foundSetManager.getTrackingInfo(), application.getClientID());
        }
        IDataSet set = application.getDataServer().performQuery(application.getClientID(), table.getServerName(), transaction_id, sqlParts, null, tableFilterParams, !sqlParts.isUnique(), 0, 100, IDataServer.VALUELIST_QUERY, trackingInfo);
        String[] displayFormat = (lookup instanceof LookupValueList) ? ((LookupValueList) lookup).getDisplayFormat() : null;
        for (int i = 0; i < set.getRowCount(); i++) {
            Object[] row = processRow(set.getRow(i));
            DisplayString display = CustomValueList.handleDisplayData(valueList, displayFormat, concatShowValues, showValues, row, application);
            if (display != null) {
                alDisplay.add(display);
                alReal.add(CustomValueList.handleRowData(valueList, concatReturnValues, returnValues, row, application));
            }
        }
        hadMoreRows = set.hadMoreRows();
    } catch (RemoteException e) {
        throw new RepositoryException(e);
    }
}

Example 8 with IQuerySort

use of com.servoy.j2db.query.IQuerySort in project servoy-client by Servoy.

the class DBValueList method createValuelistQuery.

public static QuerySelect createValuelistQuery(IServiceProvider application, ValueList valueList, ITable table) {
    if (table == null)
        return null;
    FoundSetManager foundSetManager = ((FoundSetManager) application.getFoundSetManager());
    // do not add the default pk-sort, only add real configured sort columns on value list
    List<SortColumn> sortColumns = valueList.getSortOptions() == null ? null : foundSetManager.getSortColumns(table, valueList.getSortOptions());
    int showValues = valueList.getShowDataProviders();
    int returnValues = valueList.getReturnDataProviders();
    int total = (showValues | returnValues);
    QuerySelect select = new QuerySelect(new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema()));
    ArrayList<IQuerySort> orderColumns = new ArrayList<IQuerySort>();
    ArrayList<IQuerySelectValue> columns = new ArrayList<IQuerySelectValue>();
    boolean useDefinedSort = sortColumns != null && sortColumns.size() > 0;
    if (useDefinedSort) {
        for (SortColumn sc : sortColumns) {
            SortOptions sortoptions = application.getFoundSetManager().getSortOptions(sc.getColumn());
            orderColumns.add(new QuerySort(getQuerySelectValue(table, select.getTable(), sc.getDataProviderID()), sc.getSortOrder() == SortColumn.ASCENDING, sortoptions));
        }
    }
    if ((total & 1) != 0) {
        IQuerySelectValue cSQLName = getQuerySelectValue(table, select.getTable(), valueList.getDataProviderID1());
        columns.add(cSQLName);
        if ((showValues & 1) != 0 && !useDefinedSort) {
            SortOptions sortoptions = application.getFoundSetManager().getSortOptions(table.getColumn(valueList.getDataProviderID1()));
            orderColumns.add(new QuerySort(cSQLName, true, sortoptions));
        }
    }
    if ((total & 2) != 0) {
        IQuerySelectValue cSQLName = getQuerySelectValue(table, select.getTable(), valueList.getDataProviderID2());
        columns.add(cSQLName);
        if ((showValues & 2) != 0 && !useDefinedSort) {
            SortOptions sortoptions = application.getFoundSetManager().getSortOptions(table.getColumn(valueList.getDataProviderID2()));
            orderColumns.add(new QuerySort(cSQLName, true, sortoptions));
        }
    }
    if ((total & 4) != 0) {
        IQuerySelectValue cSQLName = getQuerySelectValue(table, select.getTable(), valueList.getDataProviderID3());
        columns.add(cSQLName);
        if ((showValues & 4) != 0 && !useDefinedSort) {
            SortOptions sortoptions = application.getFoundSetManager().getSortOptions(table.getColumn(valueList.getDataProviderID3()));
            orderColumns.add(new QuerySort(cSQLName, true, sortoptions));
        }
    }
    // check if we can still use distinct
    select.setDistinct(SQLGenerator.isDistinctAllowed(columns, orderColumns));
    select.setColumns(columns);
    select.setSorts(orderColumns);
    return select;
}