forderung_dashboard.py 6.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119
  1. """Aggregate authorized invoices by rubric and selectable stack dimension."""
  2. from datetime import date, datetime, timedelta
  3. from decimal import Decimal
  4. from collections import Counter
  5. import hashlib
  6. from urllib.parse import urlencode
  7. from zoneinfo import ZoneInfo
  8. from .dashboard_segment import pack_segment, DASHBOARD_VIEWS
  9. from .forderung_data import read_invoices
  10. from .jinja_templates import number_format
  11. BERLIN = ZoneInfo('Europe/Berlin')
  12. STACKS = ('abteilung', 'wiedervorlage')
  13. def _key(value):
  14. return None if value is None or str(value) == '' else str(value)
  15. def _date(value):
  16. return value.date() if isinstance(value, datetime) else value if isinstance(value, date) else date.fromisoformat(str(value)[:10]) if value else None
  17. def color(department):
  18. hue = int.from_bytes(hashlib.sha256((department or '').encode()).digest()[:2], 'big') % 360
  19. return f'hsl({hue}, 23%, 70%)'
  20. def follow_up_bands(today):
  21. return [
  22. dict(id='overdue', label='Überfällig', color='#d4aaaa', key={'from': None, 'to': (today-timedelta(days=1)).isoformat()}),
  23. dict(id='soon', label='In 3 Tagen fällig', color='#e0cd9c', key={'from': today.isoformat(), 'to': (today+timedelta(days=3)).isoformat()}),
  24. dict(id='not_due', label='Noch nicht fällig', color='#b4c5b5', key={'from': (today+timedelta(days=4)).isoformat(), 'to': (today+timedelta(days=14)).isoformat()}),
  25. dict(id='deferred', label='zurückgestellt', color='#b2bccd', key={'from': (today+timedelta(days=15)).isoformat(), 'to': None}),
  26. dict(id='undated', label='Ohne Datum', color='#c5c5c5', key=None),
  27. ]
  28. def _url(view, key, segment=..., only_invoices=False, stack='abteilung'):
  29. selection = {'view': view, 'key': key}
  30. if segment is not ...:
  31. selection['department' if stack == 'abteilung' else 'follow_up'] = segment
  32. if only_invoices:
  33. selection['only_invoices'] = True
  34. return '/app/forderungen/liste?' + urlencode({'dashboard_segment': pack_segment(selection)})
  35. def build_dashboard(db, username, now=None, only_invoices=False, stack='abteilung'):
  36. if stack not in STACKS:
  37. raise ValueError('Ungültige Stapelung')
  38. now = now or datetime.now(BERLIN)
  39. today = now.astimezone(BERLIN).date()
  40. if only_invoices:
  41. columns, source = read_invoices(db, username, ['F.[Rechnung_Gutschrift] = :dashboard_invoice_type'], {'dashboard_invoice_type': 'Rechnung'})
  42. else:
  43. columns, source = read_invoices(db, username)
  44. groups = {view: {} for view in DASHBOARD_VIEWS}
  45. total, departments, observed = Decimal(0), set(), set()
  46. bands = follow_up_bands(today)
  47. for values in source:
  48. row = dict(zip(columns, values))
  49. department = _key(row.get('Abteilung'))
  50. departments.add(department)
  51. if stack == 'abteilung':
  52. segment_id = department
  53. else:
  54. when = _date(row.get('Wiedervorlage') or row.get('Beleg_Faellig_Datum'))
  55. bucket = 4 if when is None else 0 if when < today else 1 if when <= today+timedelta(days=3) else 2 if when <= today+timedelta(days=14) else 3
  56. segment_id = bands[bucket]['id']
  57. observed.add(segment_id)
  58. amount = Decimal(str(row['Beleg_offen'] or 0))
  59. total += amount
  60. age = _key(row.get('Staffel_Text'))
  61. site = {'client': str(row['Client_DB']), 'site': _key(row.get('Standort_ID'))}
  62. person_id = _key(row.get('Verursacher_Benutzer_ID'))
  63. person = {'id': person_id} if person_id else {'text': _key(row.get('Verursacher_Text'))}
  64. definitions = [
  65. ('Altersstaffel', age, age or 'Ohne Altersstaffel', int(row['Beleg_Tage_offen']) if age and row.get('Beleg_Tage_offen') is not None else 10**12),
  66. ('Standort', site, f"{row.get('Standort_Name') or site['site'] or 'Ohne Standort'} ({row.get('Hauptbetrieb_Name') or 'Mandant ' + site['client']})", 0),
  67. ('Verursacher', person, row.get('Verursacher_Text') or person_id or 'Ohne Verursacher', 0),
  68. ]
  69. for view, key, label, order in definitions:
  70. identity = pack_segment(key)
  71. group = groups[view].setdefault(identity, dict(key=key, label=label, order=order, count=0, amount=Decimal(0), segments={}))
  72. group['order'] = min(group['order'], order)
  73. group['count'] += 1
  74. group['amount'] += amount
  75. segment = group['segments'].setdefault(segment_id, dict(count=0, amount=Decimal(0)))
  76. segment['count'] += 1
  77. segment['amount'] += amount
  78. items = [dict(id=dep, label=dep or 'Ohne Abteilung', color=color(dep), key=dep)
  79. for dep in sorted(departments, key=lambda value: (value is None, value or ''))] if stack == 'abteilung' else [band for band in bands if band['id'] != 'undated' or 'undated' in observed]
  80. def serialized(amount):
  81. return str(amount.quantize(Decimal('0.01')))
  82. output = {}
  83. for view, mapping in groups.items():
  84. result = []
  85. labels = Counter(group['label'].casefold() for group in mapping.values())
  86. for group in mapping.values():
  87. label = group['label']
  88. if labels[label.casefold()] > 1:
  89. if view == 'Verursacher':
  90. label += ' (' + (group['key'].get('id') or 'ohne Benutzer-ID') + ')'
  91. elif view == 'Standort':
  92. label += ' · Standort-ID ' + (group['key']['site'] or 'ohne ID')
  93. segments = [dict(id=item['id'], department=item['id'] if stack == 'abteilung' else None,
  94. label=item['label'], count=group['segments'][item['id']]['count'],
  95. amount=serialized(group['segments'][item['id']]['amount']), color=item['color'],
  96. url=_url(view, group['key'], item['key'], only_invoices, stack))
  97. for item in items if item['id'] in group['segments']]
  98. result.append(dict(label=label, order=group['order'], count=group['count'], amount=serialized(group['amount']),
  99. segments=segments, url=_url(view, group['key'], only_invoices=only_invoices)))
  100. output[view] = result
  101. return dict(count=len(source), amount=serialized(total), amount_display=number_format(total), views=output,
  102. only_invoices=only_invoices, stack=stack, stack_label='Abteilung' if stack == 'abteilung' else 'Wiedervorlage',
  103. stack_items=[{name: value for name, value in item.items() if name != 'key'} for item in items],
  104. generated=now.astimezone(BERLIN).strftime('%d.%m.%Y %H:%M'))