| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051 |
- """One authorized projection for list results and dashboard aggregates."""
- from sqlalchemy import text
- from .jinja_templates import templates
- from .query_filter import EMPTY_FILTER_VALUE, EMPTY_FILTER_LABEL
- def read_filter_suggestions(db, username, field, clauses, values, search="", *, selected=None):
- # Reuse the authorized projection, including local changes to its WHERE clause.
- query = templates.env.get_template('forderungen/liste/queries/forderungen_liste.sql').render()
- query = query.rsplit('ORDER BY', 1)[0]
- raw_key, raw_label, sort = (f"F.[{column}]" for column in (field.key_column, field.value_column, field.sort_column))
- key = f"COALESCE(NULLIF(CAST({raw_key} AS nvarchar(4000)), ''), '{EMPTY_FILTER_VALUE}')"
- label = (f"CASE WHEN NULLIF(CAST({raw_key} AS nvarchar(4000)), '') IS NULL THEN '{EMPTY_FILTER_LABEL}' "
- f"ELSE CAST({raw_label} AS nvarchar(4000)) END")
- limit = 'TOP 51 ' if selected is None else ''
- query = query.replace('SELECT F.*', f'SELECT {limit}{key} AS value, MIN({label}) AS label, MIN({sort}) AS sort_value', 1)
- predicates = list(clauses)
- values = dict(values, auth_username=username)
- if selected is not None:
- placeholders = []
- for index, value in enumerate(selected):
- parameter = f'selected_{index}'
- placeholders.append(':' + parameter)
- values[parameter] = value
- if not placeholders:
- return []
- predicates.append(f"{key} IN ({', '.join(placeholders)})")
- if search:
- predicates.append(f"(CAST({key} AS nvarchar(max)) LIKE :suggest_search OR CAST({label} AS nvarchar(max)) LIKE :suggest_search)")
- values['suggest_search'] = f'%{search}%'
- if predicates:
- query = query.replace('AND 1 = 1', 'AND 1 = 1 AND ' + ' AND '.join(predicates))
- query += f' GROUP BY {key} ORDER BY MIN({sort}), {key}'
- rows = db.execute(text(query), values).fetchall()
- return [{"value": str(row[0]), "label": str(row[1] if row[1] is not None else row[0])} for row in rows]
- def read_invoices(db, username, clauses=(), values=None):
- query = templates.env.get_template('forderungen/liste/queries/forderungen_liste.sql').render()
- if clauses:
- query = query.replace('AND 1 = 1', 'AND 1 = 1 AND ' + ' AND '.join(clauses))
- result = db.execute(text(query).execution_options(query_name='forderungen/liste/queries/forderungen_liste.sql'), dict(values or {}, auth_username=username))
- columns = list(result.keys())
- tenant, number = columns.index('Client_DB'), columns.index('Beleg_Nr')
- seen, rows = set(), []
- for row in result.fetchall():
- identity = (str(row[tenant]), str(row[number]))
- if identity not in seen:
- rows.append(row)
- seen.add(identity)
- return columns, rows
|