forderung_data.py 2.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051
  1. """One authorized projection for list results and dashboard aggregates."""
  2. from sqlalchemy import text
  3. from .jinja_templates import templates
  4. from .query_filter import EMPTY_FILTER_VALUE, EMPTY_FILTER_LABEL
  5. def read_filter_suggestions(db, username, field, clauses, values, search="", *, selected=None):
  6. # Reuse the authorized projection, including local changes to its WHERE clause.
  7. query = templates.env.get_template('forderungen/liste/queries/forderungen_liste.sql').render()
  8. query = query.rsplit('ORDER BY', 1)[0]
  9. raw_key, raw_label, sort = (f"F.[{column}]" for column in (field.key_column, field.value_column, field.sort_column))
  10. key = f"COALESCE(NULLIF(CAST({raw_key} AS nvarchar(4000)), ''), '{EMPTY_FILTER_VALUE}')"
  11. label = (f"CASE WHEN NULLIF(CAST({raw_key} AS nvarchar(4000)), '') IS NULL THEN '{EMPTY_FILTER_LABEL}' "
  12. f"ELSE CAST({raw_label} AS nvarchar(4000)) END")
  13. limit = 'TOP 51 ' if selected is None else ''
  14. query = query.replace('SELECT F.*', f'SELECT {limit}{key} AS value, MIN({label}) AS label, MIN({sort}) AS sort_value', 1)
  15. predicates = list(clauses)
  16. values = dict(values, auth_username=username)
  17. if selected is not None:
  18. placeholders = []
  19. for index, value in enumerate(selected):
  20. parameter = f'selected_{index}'
  21. placeholders.append(':' + parameter)
  22. values[parameter] = value
  23. if not placeholders:
  24. return []
  25. predicates.append(f"{key} IN ({', '.join(placeholders)})")
  26. if search:
  27. predicates.append(f"(CAST({key} AS nvarchar(max)) LIKE :suggest_search OR CAST({label} AS nvarchar(max)) LIKE :suggest_search)")
  28. values['suggest_search'] = f'%{search}%'
  29. if predicates:
  30. query = query.replace('AND 1 = 1', 'AND 1 = 1 AND ' + ' AND '.join(predicates))
  31. query += f' GROUP BY {key} ORDER BY MIN({sort}), {key}'
  32. rows = db.execute(text(query), values).fetchall()
  33. return [{"value": str(row[0]), "label": str(row[1] if row[1] is not None else row[0])} for row in rows]
  34. def read_invoices(db, username, clauses=(), values=None):
  35. query = templates.env.get_template('forderungen/liste/queries/forderungen_liste.sql').render()
  36. if clauses:
  37. query = query.replace('AND 1 = 1', 'AND 1 = 1 AND ' + ' AND '.join(clauses))
  38. result = db.execute(text(query).execution_options(query_name='forderungen/liste/queries/forderungen_liste.sql'), dict(values or {}, auth_username=username))
  39. columns = list(result.keys())
  40. tenant, number = columns.index('Client_DB'), columns.index('Beleg_Nr')
  41. seen, rows = set(), []
  42. for row in result.fetchall():
  43. identity = (str(row[tenant]), str(row[number]))
  44. if identity not in seen:
  45. rows.append(row)
  46. seen.add(identity)
  47. return columns, rows