BlackMarkandClaude Opus 5.5
45424e6fd6
A local web app for a small room-rental business in Austria: guest register, room ledger, occupancy plan, invoices, the monthly tourist-tax report and the quarterly VAT list. Application code only; it ships no data. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
310 lines
19 KiB
Python
310 lines
19 KiB
Python
"""Data-quality audit: surface what is missing or odd in the data, and suggest likely duplicates.
|
||
|
||
Two kinds of findings, kept strictly apart:
|
||
* DATA GAPS — the data itself is incomplete or odd (missing dates, departure before arrival, money-less
|
||
rows, duplicate or year-mismatched invoice numbers, recipients without an address). These are
|
||
surfaced per row, never invented away; reviewed ones can be dismissed as correct-as-is.
|
||
* SUGGESTIONS — near-duplicate companies/guests (typo variants) offered for one-click manual merge;
|
||
only exact-normalized spellings are merged automatically.
|
||
"""
|
||
import re
|
||
from difflib import SequenceMatcher
|
||
|
||
from app import db, normalize
|
||
|
||
# a company-name slice (legal form or personnel-industry activity word) has no business in a postal
|
||
# address line — its presence means part of the NAME was typed into the ADDRESS. Deliberately narrow
|
||
# (no 'OG'/'AG'/'KG' — those are Obergeschoss/real streets).
|
||
_ADDR_NAME_RE = re.compile(
|
||
r"(g\.?m\.?b\.?h|gbmh|ges\.?m\.?b\.?h|gesmbh|e\.\s*u\.|personalmanagement|personalservice|"
|
||
r"personalbereitstellung|personaldienst|zeitarbeit|handelsges)", re.I)
|
||
|
||
|
||
def _register_ledger_mismatches(conn):
|
||
"""A guest's Gästeblatt An-/Abreise vs the ledger periods billed under exactly that name: where the
|
||
same name appears in both, the covered spans must agree — a mismatch means one of the two is wrong
|
||
(Gästeblatt 22.–23., Zimmerbelegung 22.–25.). Only exact name matches are compared, no fuzzy guessing;
|
||
room changes are bridged via the shared Gästeblatt number."""
|
||
ledger = db.rows(conn, """
|
||
SELECT s.label, c.period_from, c.period_to, rm.number AS room
|
||
FROM charge c JOIN stay s ON c.stay_id=s.id LEFT JOIN room rm ON s.room_id=rm.id
|
||
WHERE c.source_sheet='ledger' AND c.period_from IS NOT NULL AND c.period_to IS NOT NULL
|
||
ORDER BY c.id""")
|
||
by_name = {} # 'muster max' -> segments (labels combine persons)
|
||
for r in ledger:
|
||
for token in (t.strip() for t in (r["label"] or "").split(",")):
|
||
if token:
|
||
by_name.setdefault(" ".join(token.lower().split()), []).append(r)
|
||
|
||
stays = db.rows(conn, """
|
||
SELECT s.guest_id, s.sheet_number, s.arrival, s.departure, rm.number AS room,
|
||
TRIM(g.last_name || ' ' || COALESCE(g.first_name, '')) AS name
|
||
FROM stay s JOIN guest g ON g.id=s.guest_id LEFT JOIN room rm ON s.room_id=rm.id
|
||
WHERE s.source_sheet='guestsheet' AND s.arrival IS NOT NULL
|
||
ORDER BY s.id""")
|
||
groups = {} # one Gästeblatt = one registration span (room changes)
|
||
for s in stays:
|
||
key = (s["guest_id"], s["sheet_number"]) if s["sheet_number"] else ("stay", id(s))
|
||
groups.setdefault(key, []).append(s)
|
||
|
||
out = []
|
||
for rows_ in groups.values():
|
||
first = min(rows_, key=lambda r: r["arrival"])
|
||
arrival = first["arrival"]
|
||
deps = [r["departure"] for r in rows_]
|
||
if any(d is None for d in deps): # still in the house — no final span to compare yet
|
||
continue
|
||
departure = max(deps)
|
||
segs = sorted((x for x in by_name.get(" ".join(first["name"].lower().split()), ())
|
||
if x["period_from"] <= departure and x["period_to"] >= arrival),
|
||
key=lambda x: x["period_from"])
|
||
if not segs: # name never billed in this window — nothing to compare
|
||
continue
|
||
led_from, led_to = min(x["period_from"] for x in segs), max(x["period_to"] for x in segs)
|
||
if (led_from, led_to) != (arrival, departure):
|
||
out.append({"sheet_number": first["sheet_number"], "room": first["room"], "name": first["name"],
|
||
"arrival": arrival, "departure": departure,
|
||
"ledger_from": led_from, "ledger_to": led_to})
|
||
out.sort(key=lambda r: r["arrival"], reverse=True)
|
||
return out
|
||
|
||
|
||
def dismissed(conn, kind):
|
||
"""Keys of findings reviewed and locked in as correct-as-is (natural keys, not row ids)."""
|
||
return {r[0] for r in db.rows(conn, "SELECT key FROM dismissed_finding WHERE kind=?", (kind,))}
|
||
|
||
|
||
def dismiss(conn, kind, key, note=None):
|
||
db.execute(conn, "INSERT INTO dismissed_finding (kind, key, note) VALUES (?,?,?) ON CONFLICT DO NOTHING",
|
||
(kind, key, note))
|
||
|
||
|
||
def _keyed(rows_, kind, keyfn, done):
|
||
"""Attach the dismiss key to each finding and hide the already-reviewed ones."""
|
||
out = []
|
||
for r in rows_:
|
||
r["_kind"], r["_key"] = kind, keyfn(r)
|
||
if r["_key"] not in done:
|
||
out.append(r)
|
||
return out
|
||
|
||
|
||
def data_gaps(conn):
|
||
"""Oddities in the data, surfaced per row (the tool never invents data)."""
|
||
def rows(sql, params=()):
|
||
return [dict(r) for r in db.rows(conn, sql, params)]
|
||
gaps = {}
|
||
gaps["register_ledger_mismatch"] = _keyed(
|
||
_register_ledger_mismatches(conn), "register_ledger",
|
||
lambda r: f"{r['sheet_number']}|{r['name']}|{r['arrival']}",
|
||
dismissed(conn, "register_ledger"))
|
||
gaps["register_no_departure"] = rows("""
|
||
SELECT s.id AS stay_id, g.last_name || ' ' || COALESCE(g.first_name,'') AS name, s.arrival
|
||
FROM stay s LEFT JOIN guest g ON g.id=s.guest_id
|
||
WHERE s.source_sheet='guestsheet' AND s.departure IS NULL ORDER BY s.arrival DESC NULLS LAST, s.id""")
|
||
# an open guest-register stay older than a year is a forgotten departure; the TVB Sammelmeldung
|
||
# page skips such rows silently, so they must stay visible here until the date is filled in
|
||
gaps["tvb_stale_open"] = rows("""
|
||
SELECT s.id AS stay_id, rm.number AS room, COALESCE(TRIM(g.last_name || ' ' ||
|
||
COALESCE(g.first_name,'')), s.label) AS name, s.arrival, s.sheet_number
|
||
FROM stay s LEFT JOIN guest g ON g.id=s.guest_id LEFT JOIN room rm ON s.room_id=rm.id
|
||
WHERE s.source_sheet='guestsheet' AND s.departure IS NULL AND s.arrival < to_char(CURRENT_DATE - interval '1 year', 'YYYY-MM-DD')
|
||
ORDER BY s.arrival NULLS FIRST, s.id""")
|
||
# ledger rows keep their period on the charge only — the backwards-period check looks at stays for the
|
||
# register and at charges for the ledger. Dismissable: Storno/Korrektur rows legitimately carry
|
||
# reversed dates.
|
||
gaps["depart_before_arrival"] = _keyed(rows("""
|
||
SELECT s.id AS stay_id, s.source_sheet, COALESCE(g.last_name || ' ' || COALESCE(g.first_name,''),
|
||
s.label) AS name, s.arrival, s.departure
|
||
FROM stay s LEFT JOIN guest g ON g.id=s.guest_id
|
||
WHERE s.arrival IS NOT NULL AND s.departure IS NOT NULL AND s.departure < s.arrival
|
||
ORDER BY s.id""") + rows("""
|
||
SELECT c.id AS stay_id, 'ledger' AS source_sheet, s.label AS name,
|
||
c.period_from AS arrival, c.period_to AS departure
|
||
FROM charge c JOIN stay s ON s.id=c.stay_id
|
||
WHERE c.source_sheet='ledger' AND c.period_from IS NOT NULL AND c.period_to IS NOT NULL
|
||
AND c.period_to < c.period_from
|
||
ORDER BY c.id"""),
|
||
"backwards_period", lambda r: f"{r['name']}|{r['arrival']}|{r['departure']}",
|
||
dismissed(conn, "backwards_period"))
|
||
# the ledger's GB column must point at a Gästeblatt that actually exists. Multi-person rows write
|
||
# consecutive sheets in shorthand — '816, 827', '277f' (and following), '300-1', '304 305', and
|
||
# concatenated pairs ('23435' = 234+235, '1992' = 199+200) — a ref only counts as unknown if NO reading
|
||
# resolves to an existing sheet
|
||
gb_known = {str(r[0]).strip() for r in db.rows(conn,
|
||
"SELECT DISTINCT sheet_number FROM stay WHERE source_sheet='guestsheet' AND sheet_number IS NOT NULL")}
|
||
|
||
def gb_resolves(token):
|
||
t = token.strip().rstrip("fF") # '277f'/'285ff' -> the base sheet starts the run
|
||
if not t or t in gb_known:
|
||
return True
|
||
if "-" in t: # '300-1' -> the range starts at 300
|
||
return t.split("-")[0].strip() in gb_known
|
||
if t.isdigit() and len(t) >= 4: # concatenated consecutives: a known 2-3 digit prefix
|
||
return any(t[:n] in gb_known and len(t) - n <= n for n in (3, 2))
|
||
return False
|
||
|
||
gaps["gb_ref_unknown"] = _keyed([
|
||
{**r, "missing": ", ".join(t for t in re.split(r"[,.\s]+", str(r["gb_ref"])) if t and not gb_resolves(t))}
|
||
for r in rows("""SELECT c.id AS charge_id, rm.number AS room, s.label AS name, c.period_from, c.gb_ref
|
||
FROM charge c JOIN stay s ON c.stay_id=s.id LEFT JOIN room rm ON s.room_id=rm.id
|
||
WHERE c.source_sheet='ledger' AND c.gb_ref IS NOT NULL ORDER BY c.id""")
|
||
if any(t and not gb_resolves(t) for t in re.split(r"[,.\s]+", str(r["gb_ref"])))],
|
||
"gb_ref_unknown", lambda r: f"{r['room']}|{r['name']}|{r['period_from']}|{r['gb_ref']}",
|
||
dismissed(conn, "gb_ref_unknown"))
|
||
# a CLOSED register stay whose guest never shows up in any ledger billing: either the stay was
|
||
# never charged (lost revenue) or the two lists spell the name differently — both worth seeing
|
||
ledger_tokens = set()
|
||
for r in db.rows(conn, "SELECT DISTINCT lower(label) l FROM stay "
|
||
"WHERE source_sheet='ledger' AND label IS NOT NULL"):
|
||
for tok in r["l"].split(","):
|
||
tok = " ".join(tok.split())
|
||
if len(tok) > 4:
|
||
ledger_tokens.add(tok)
|
||
|
||
def billed(name):
|
||
n = " ".join(name.lower().split())
|
||
if any(n in t or t in n for t in ledger_tokens):
|
||
return True
|
||
last = n.split()[0]
|
||
return len(last) > 3 and any(last in t for t in ledger_tokens)
|
||
|
||
gaps["register_never_billed"] = _keyed(
|
||
[r for r in rows("""SELECT s.id AS stay_id, s.sheet_number, rm.number AS room,
|
||
TRIM(g.last_name || ' ' || COALESCE(g.first_name,'')) AS name,
|
||
s.arrival, s.departure
|
||
FROM stay s JOIN guest g ON g.id=s.guest_id
|
||
LEFT JOIN room rm ON rm.id=s.room_id
|
||
WHERE s.source_sheet='guestsheet' AND s.departure IS NOT NULL
|
||
ORDER BY s.arrival DESC NULLS LAST, s.id""") if not billed(r["name"])],
|
||
"never_billed", lambda r: f"{r['sheet_number']}|{r['name']}|{r['arrival']}",
|
||
dismissed(conn, "never_billed"))
|
||
# Betrag matches NEITHER Anzahl x Satz NOR Anzahl x Satz x Personen: a possible typo in one of
|
||
# the three numbers (legitimate discounts exist — hence reviewable, not an error)
|
||
gaps["betrag_off_formula"] = _keyed(
|
||
[dict(r, calc=round(r["quantity"] * r["gross_per_unit"] * (r["persons"] or 1), 2))
|
||
for r in rows("""SELECT c.id AS charge_id, rm.number AS room, s.label AS name, c.period_from,
|
||
c.quantity, c.gross_per_unit, c.persons,
|
||
ROUND((c.line_net_override*1.1)::numeric, 2)::float8 AS betrag
|
||
FROM charge c JOIN stay s ON s.id=c.stay_id LEFT JOIN room rm ON rm.id=s.room_id
|
||
WHERE c.source_sheet='ledger' AND c.quantity IS NOT NULL
|
||
AND c.gross_per_unit IS NOT NULL AND c.line_net_override IS NOT NULL
|
||
ORDER BY c.period_from DESC NULLS LAST, c.id""")
|
||
if abs(r["quantity"] * r["gross_per_unit"] - r["betrag"]) > 1.0
|
||
and abs(r["quantity"] * r["gross_per_unit"] * (r["persons"] or 1) - r["betrag"]) > 1.0],
|
||
"betrag_formula", lambda r: f"{r['room']}|{r['name']}|{r['period_from']}|{r['betrag']}",
|
||
dismissed(conn, "betrag_formula"))
|
||
gaps["charges_without_money"] = rows("""
|
||
SELECT c.id AS charge_id, rm.number AS room, s.label AS name, c.period_from, c.period_to
|
||
FROM charge c JOIN stay s ON c.stay_id=s.id LEFT JOIN room rm ON s.room_id=rm.id
|
||
WHERE c.source_sheet='ledger' AND c.gross_per_unit IS NULL AND c.line_net_override IS NULL
|
||
ORDER BY c.id""")
|
||
# a company that is billed or worked for but has no postal address anywhere: an invoice to it would
|
||
# print half a recipient block (short 'Zlg. durch' spellings create such companies). Payment channels
|
||
# are not recipients and never count.
|
||
gaps["client_without_address"] = _keyed(rows("""
|
||
SELECT * FROM (
|
||
SELECT co.id AS company_id, co.name, COALESCE(paying.n, 0) AS as_payer,
|
||
(SELECT COUNT(*) FROM stay s JOIN client c2 ON c2.id=s.employer_client_id
|
||
WHERE c2.company_id=co.id) AS as_employer
|
||
FROM company co
|
||
-- payers counted once, not per company: one scan of charge instead of one per company
|
||
LEFT JOIN (SELECT lower(TRIM(payer)) AS payer, COUNT(*) AS n FROM charge GROUP BY 1) paying
|
||
ON paying.payer = lower(co.name)
|
||
WHERE NOT EXISTS (SELECT 1 FROM client cl WHERE cl.company_id=co.id
|
||
AND COALESCE(cl.address,'') <> '')
|
||
AND lower(co.name) NOT IN ('bar','bank')) counted
|
||
WHERE as_payer > 0 OR as_employer > 0
|
||
ORDER BY as_payer + as_employer DESC, company_id"""),
|
||
"client_without_address", lambda r: r["name"], dismissed(conn, "client_without_address"))
|
||
# a recipient whose ADDRESS line carries a slice of the company NAME — it would print wrong on every
|
||
# invoice to that client. Reviewable, not invented.
|
||
gaps["address_has_name"] = _keyed(
|
||
[dict(r) for r in rows("""SELECT cl.id AS client_id, co.name AS company, cl.name, cl.address,
|
||
cl.postcode, cl.city
|
||
FROM client cl LEFT JOIN company co ON cl.company_id=co.id
|
||
WHERE cl.address IS NOT NULL AND TRIM(cl.address) <> ''
|
||
ORDER BY cl.id""")
|
||
if _ADDR_NAME_RE.search(r["address"])],
|
||
"address_has_name", lambda r: f"{r['client_id']}|{r['address']}",
|
||
dismissed(conn, "address_has_name"))
|
||
# a negative or absurdly large quantity (a 'bis' before the 'von', a typo) poisons every sum in the grid
|
||
gaps["implausible_quantities"] = _keyed(rows("""
|
||
SELECT c.id AS charge_id, rm.number AS room, s.label AS name, c.period_from, c.period_to,
|
||
c.quantity, ROUND(COALESCE(c.line_net_override * 1.1,
|
||
c.quantity * c.gross_per_unit)::numeric, 2)::float8 AS amount
|
||
FROM charge c JOIN stay s ON c.stay_id=s.id LEFT JOIN room rm ON s.room_id=rm.id
|
||
WHERE c.source_sheet='ledger' AND (c.quantity < 0 OR c.quantity > 500
|
||
OR COALESCE(c.line_net_override * 1.1, c.quantity * c.gross_per_unit) < 0)
|
||
ORDER BY c.id"""),
|
||
"implausible_qty", lambda r: f"{r['room']}|{r['name']}|{r['period_from']}|{r['amount']}",
|
||
dismissed(conn, "implausible_qty"))
|
||
# an RNr typed into the ledger that no invoice answers to: a typo, or an invoice not entered yet
|
||
gaps["rnr_unlinked"] = rows("""
|
||
SELECT c.id AS charge_id, rm.number AS room, s.label AS name, c.period_from, c.rnr
|
||
FROM charge c JOIN stay s ON c.stay_id=s.id LEFT JOIN room rm ON s.room_id=rm.id
|
||
WHERE c.source_sheet='ledger' AND c.invoice_id IS NULL
|
||
AND c.rnr IS NOT NULL AND c.rnr <> '-----'
|
||
ORDER BY c.id""")
|
||
gaps["open_items"] = rows("""
|
||
SELECT c.id AS charge_id, rm.number AS room, s.label AS name, c.period_from,
|
||
ROUND((c.line_net_override * 1.1)::numeric, 2)::float8 AS amount
|
||
FROM charge c JOIN stay s ON c.stay_id=s.id LEFT JOIN room rm ON s.room_id=rm.id
|
||
WHERE c.paid_status='open' ORDER BY c.period_from NULLS FIRST, c.id""")
|
||
gaps["duplicate_invoice_numbers"] = _keyed(rows("""
|
||
SELECT u.code AS unit, i.number, COUNT(*) AS n, string_agg(i.id::text, ',' ORDER BY i.id) AS ids
|
||
FROM invoice i JOIN unit u ON u.id=i.unit_id
|
||
GROUP BY u.code, i.number HAVING COUNT(*) > 1 ORDER BY i.number NULLS FIRST, u.code"""),
|
||
"duplicate_number", lambda r: f"{r['unit']}|{r['number']}", dismissed(conn, "duplicate_number"))
|
||
# numbers can carry the year 4-digit ('7-2016'), or 2-digit-coded ('1803' = 2018 seq 03,
|
||
# '25-46') — only a number that matches NEITHER reading of its invoice date is a mismatch
|
||
gaps["number_year_mismatch"] = _keyed(rows("""
|
||
SELECT i.id, u.code AS unit, i.number, i.date FROM invoice i JOIN unit u ON u.id=i.unit_id
|
||
WHERE i.date IS NOT NULL AND i.number ~ '[0-9]{4}'
|
||
AND NOT i.number LIKE '%' || substr(i.date, 1, 4) || '%'
|
||
AND NOT i.number ~ ('^' || substr(i.date, 3, 2) || '[0-9][0-9]$')
|
||
AND NOT i.number ~ ('^' || substr(i.date, 3, 2) || '[- ]')
|
||
ORDER BY i.date, i.id"""),
|
||
"number_year", lambda r: f"{r['unit']}|{r['number']}|{r['date']}", dismissed(conn, "number_year"))
|
||
return gaps
|
||
|
||
|
||
def _fuzzy_pairs(items, threshold=0.87, prefix=4):
|
||
"""(id, key, label) triples -> near-duplicate pairs by key similarity. The shared-prefix guard keeps
|
||
the company comparison cheap (hundreds of names); tiny same-birthday guest groups compare unguarded."""
|
||
out = []
|
||
items = sorted(items, key=lambda x: x[1])
|
||
for i in range(len(items)):
|
||
for j in range(i + 1, len(items)):
|
||
a, b = items[i], items[j]
|
||
if prefix and a[1][:prefix] != b[1][:prefix]:
|
||
continue
|
||
if SequenceMatcher(None, a[1], b[1]).ratio() > threshold:
|
||
out.append({"a_id": a[0], "a": a[2], "b_id": b[0], "b": b[2]})
|
||
return out
|
||
|
||
|
||
def suggestions(conn):
|
||
comps = [(r["id"], normalize.company_key(r["name"]), r["name"])
|
||
for r in db.rows(conn, "SELECT id, name FROM company ORDER BY id")]
|
||
companies = _fuzzy_pairs([c for c in comps if c[1]])
|
||
|
||
guests = []
|
||
by_birth = {}
|
||
for r in db.rows(conn, "SELECT id, last_name, first_name, birth_date FROM guest "
|
||
"WHERE birth_date IS NOT NULL ORDER BY id"):
|
||
label = f"{r['last_name']} {r['first_name'] or ''}".strip()
|
||
by_birth.setdefault(r["birth_date"], []).append((r["id"], label.lower(), f"{label} ({r['birth_date']})"))
|
||
for birth, people in by_birth.items():
|
||
if len(people) > 1:
|
||
guests += _fuzzy_pairs(people, threshold=0.75, prefix=0)
|
||
# pairs reviewed as "genuinely different" stay dismissed (keyed by labels, not ids)
|
||
pair_key = lambda p: "|".join(sorted([p["a"].lower(), p["b"].lower()])) # noqa: E731
|
||
companies = _keyed(companies, "company_pair", pair_key, dismissed(conn, "company_pair"))
|
||
guests = _keyed(guests, "guest_pair", pair_key, dismissed(conn, "guest_pair"))
|
||
return {"companies": companies, "guests": guests}
|
||
|
||
|
||
def overview(conn):
|
||
return {"gaps": data_gaps(conn), "suggestions": suggestions(conn)}
|