forderung_dashboard.py 6.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174
  1. """Aggregate authorized invoices by rubric and selectable stack dimension."""
  2. import hashlib
  3. from collections import Counter
  4. from datetime import datetime
  5. from decimal import Decimal
  6. from zoneinfo import ZoneInfo
  7. from .dashboard_segment import DASHBOARD_VIEWS, pack_segment
  8. from .forderung_data import read_invoices
  9. from .jinja_templates import number_format
  10. from .navigation import list_url
  11. from .query_filter import EMPTY_FILTER_VALUE, encode_filter_values
  12. BERLIN = ZoneInfo("Europe/Berlin")
  13. STACKS = ("abteilung", "wiedervorlage")
  14. def _key(value):
  15. return None if value is None or str(value) == "" else str(value)
  16. def color(department):
  17. hue = int.from_bytes(hashlib.sha256((department or "").encode()).digest()[:2], "big") % 360
  18. return f"hsl({hue}, 23%, 70%)"
  19. FOLLOW_UP_COLORS = {
  20. "überfällig": "#d4aaaa",
  21. "in 3 tagen fällig": "#e0cd9c",
  22. "noch nicht fällig": "#b4c5b5",
  23. "zurückgestellt": "#b2bccd",
  24. }
  25. def _url(view, key, segment=..., only_invoices=False, stack="abteilung"):
  26. filters = {}
  27. def select(name, value):
  28. filters[name] = encode_filter_values([EMPTY_FILTER_VALUE if value is None or value == "" else value])
  29. if view == "Standort":
  30. select("Mandant", key["client"])
  31. select("Filiale", key["site"])
  32. elif view == "Altersstaffel":
  33. select("Staffel", key)
  34. elif view == "Verursacher":
  35. select("Verursacher", key["text"])
  36. if segment is not ... and stack == "abteilung":
  37. select("Abteilung", segment)
  38. if segment is not ... and stack == "wiedervorlage":
  39. select("WVStaffel", segment)
  40. if only_invoices:
  41. select("RechnungGutschrift", "Rechnung")
  42. return list_url(filters)
  43. def build_dashboard(db, username, now=None, only_invoices=False, stack="abteilung"):
  44. if stack not in STACKS:
  45. raise ValueError("Ungültige Stapelung")
  46. now = now or datetime.now(BERLIN)
  47. if only_invoices:
  48. columns, source = read_invoices(
  49. db, username, ["F.[Rechnung_Gutschrift] = :dashboard_invoice_type"], {"dashboard_invoice_type": "Rechnung"}
  50. )
  51. else:
  52. columns, source = read_invoices(db, username)
  53. groups = {view: {} for view in DASHBOARD_VIEWS}
  54. total, departments, stages = Decimal(0), set(), {}
  55. for values in source:
  56. row = dict(zip(columns, values))
  57. department = _key(row.get("Abteilung"))
  58. departments.add(department)
  59. if stack == "abteilung":
  60. segment_id = department
  61. else:
  62. # The shared list projection owns the classification, including the
  63. # 21-day deferred boundary. Use its key for an exact WVStaffel link.
  64. segment_id = _key(row["Wiedervorlage_Staffel_Nr"])
  65. label = _key(row["Wiedervorlage_Staffel_Text"])
  66. stages.setdefault(segment_id, dict(
  67. id=segment_id,
  68. label=label or (f"WV-Staffel {segment_id}" if segment_id is not None else "Ohne WV-Staffel"),
  69. color=FOLLOW_UP_COLORS.get((label or "").casefold(), color(segment_id)) if segment_id is not None else "#c5c5c5",
  70. key=segment_id,
  71. ))
  72. amount = Decimal(str(row["Beleg_Offen"] or 0))
  73. total += amount
  74. age = _key(row.get("Staffel_Text"))
  75. site = {"client": str(row["Client_DB"]), "site": _key(row.get("Standort_ID"))}
  76. person_name = _key(row.get("Verursacher_Text"))
  77. person = {"text": person_name}
  78. definitions = [
  79. (
  80. "Altersstaffel",
  81. age,
  82. age or "Ohne Altersstaffel",
  83. int(row["Beleg_Tage_offen"]) if age and row.get("Beleg_Tage_offen") is not None else 10**12,
  84. ),
  85. (
  86. "Standort",
  87. site,
  88. f"{row.get('Standort_Name') or site['site'] or 'Ohne Standort'} ({row.get('Hauptbetrieb_Name') or 'Mandant ' + site['client']})",
  89. 0,
  90. ),
  91. ("Verursacher", person, person_name or "Ohne Verursacher", 0),
  92. ]
  93. for view, key, label, order in definitions:
  94. identity = pack_segment(key)
  95. group = groups[view].setdefault(
  96. identity, dict(key=key, label=label, order=order, count=0, amount=Decimal(0), segments={})
  97. )
  98. group["order"] = min(group["order"], order)
  99. group["count"] += 1
  100. group["amount"] += amount
  101. segment = group["segments"].setdefault(segment_id, dict(count=0, amount=Decimal(0)))
  102. segment["count"] += 1
  103. segment["amount"] += amount
  104. items = (
  105. [
  106. dict(id=dep, label=dep or "Ohne Abteilung", color=color(dep), key=dep)
  107. for dep in sorted(departments, key=lambda value: (value is None, value or ""))
  108. ]
  109. if stack == "abteilung"
  110. else sorted(stages.values(), key=lambda item: (item["key"] is None, Decimal(item["key"] or "0")))
  111. )
  112. def serialized(amount):
  113. return str(amount.quantize(Decimal("0.01")))
  114. output = {}
  115. for view, mapping in groups.items():
  116. result = []
  117. labels = Counter(group["label"].casefold() for group in mapping.values())
  118. for group in mapping.values():
  119. label = group["label"]
  120. if labels[label.casefold()] > 1:
  121. if view == "Standort":
  122. label += " · Standort-ID " + (group["key"]["site"] or "ohne ID")
  123. segments = [
  124. dict(
  125. id=item["id"],
  126. department=item["id"] if stack == "abteilung" else None,
  127. label=item["label"],
  128. count=group["segments"][item["id"]]["count"],
  129. amount=serialized(group["segments"][item["id"]]["amount"]),
  130. color=item["color"],
  131. url=_url(view, group["key"], item["key"], only_invoices, stack),
  132. )
  133. for item in items
  134. if item["id"] in group["segments"]
  135. ]
  136. result.append(
  137. dict(
  138. label=label,
  139. order=group["order"],
  140. count=group["count"],
  141. amount=serialized(group["amount"]),
  142. segments=segments,
  143. url=_url(view, group["key"], only_invoices=only_invoices),
  144. )
  145. )
  146. output[view] = result
  147. return dict(
  148. count=len(source),
  149. amount=serialized(total),
  150. amount_display=number_format(total),
  151. views=output,
  152. only_invoices=only_invoices,
  153. stack=stack,
  154. stack_label="Abteilung" if stack == "abteilung" else "Wiedervorlage",
  155. stack_items=[{name: value for name, value in item.items() if name != "key"} for item in items],
  156. generated=now.astimezone(BERLIN).strftime("%d.%m.%Y %H:%M"),
  157. )