141 lines
8.3 KiB
Python
141 lines
8.3 KiB
Python
"""Rule-based categorisation + seed data tuned for Dutch merchants."""
|
|
import re
|
|
from functools import lru_cache
|
|
|
|
from sqlalchemy import select
|
|
from sqlalchemy.orm import Session
|
|
|
|
from models import Category, Rule, Transaction
|
|
|
|
_PREFIXES = re.compile(
|
|
r"^(bea|gea|sepa|ccv\*|zettle_\*|sumup \*|sumup\*|pay\.nl\*|mollie\*|stichting|paypal \*|paypal\*|apple pay|"
|
|
r"google pay|ideal|betaalverzoek|tikkie)\s*[,:*]?\s*", re.I)
|
|
|
|
|
|
def normalize(text: str) -> str:
|
|
"""Stable key for a counterparty: lower-case, no digits/punctuation/payment-processor prefixes."""
|
|
s = (text or "").lower()
|
|
for _ in range(2):
|
|
s = _PREFIXES.sub("", s)
|
|
s = re.sub(r"[^a-z\s.&']", " ", s)
|
|
s = re.sub(r"\b(nl|bv|b\.v\.|n\.v\.|nv|the|via)\b", " ", s)
|
|
s = re.sub(r"\s+", " ", s).strip(" .")
|
|
return " ".join(s.split()[:4])
|
|
|
|
|
|
@lru_cache(maxsize=2048)
|
|
def _compile(pattern: str) -> re.Pattern:
|
|
if pattern.startswith("re:"):
|
|
return re.compile(pattern[3:], re.I)
|
|
return re.compile(r"(?<![a-z0-9])" + re.escape(pattern.lower()) + r"(?![a-z0-9])", re.I)
|
|
|
|
|
|
def rule_matches(rule: Rule, tx: Transaction) -> bool:
|
|
if rule.field == "iban":
|
|
return bool(tx.counter_iban) and tx.counter_iban.replace(" ", "").upper() == rule.pattern.replace(" ", "").upper()
|
|
if rule.field == "counterparty":
|
|
text = tx.counterparty or ""
|
|
elif rule.field == "description":
|
|
text = tx.description or ""
|
|
else:
|
|
text = f"{tx.counterparty} {tx.description}"
|
|
return bool(_compile(rule.pattern).search(text))
|
|
|
|
|
|
def load_rules(db: Session) -> list[Rule]:
|
|
return list(db.scalars(select(Rule).order_by(Rule.priority, Rule.id)))
|
|
|
|
|
|
def categorize(db: Session, txs: list[Transaction], own_ibans: set[str] | None = None,
|
|
rules: list[Rule] | None = None) -> int:
|
|
"""Assign categories to unlocked transactions. Returns how many got a category."""
|
|
rules = rules if rules is not None else load_rules(db)
|
|
own_ibans = own_ibans or set()
|
|
transfer = db.scalar(select(Category).where(Category.name == "Transfers"))
|
|
n = 0
|
|
for tx in txs:
|
|
if tx.category_locked:
|
|
continue
|
|
if transfer and tx.counter_iban and tx.counter_iban.replace(" ", "").upper() in own_ibans:
|
|
tx.category_id = transfer.id
|
|
n += 1
|
|
continue
|
|
for r in rules:
|
|
if rule_matches(r, tx):
|
|
tx.category_id = r.category_id
|
|
n += 1
|
|
break
|
|
return n
|
|
|
|
|
|
SEED: dict[str, tuple[str, str, list[str]]] = {
|
|
# name: (kind, color, patterns)
|
|
"Salary": ("income", "#2e7d32", ["salaris", "salary", "loon", "re:\\bsal\\.?\\b"]),
|
|
"Other income": ("income", "#66bb6a", ["belastingdienst teruggaaf", "toeslag", "zorgtoeslag", "huurtoeslag"]),
|
|
"Transfers": ("transfer", "#9e9e9e", ["top-up", "topup", "oranje spaarrekening", "spaarrekening",
|
|
"re:^to (eur|savings)", "re:revolut\\b.*top"]),
|
|
"Housing": ("expense", "#6d4c41", ["huur", "hypotheek", "vve", "woningstichting", "woonbron", "ymere",
|
|
"eigen haard", "de alliantie", "vesteda"]),
|
|
"Utilities": ("expense", "#f9a825", ["vattenfall", "eneco", "essent", "greenchoice", "budget energie",
|
|
"vandebron", "energiedirect", "pure energie", "frank energie", "tibber",
|
|
"waternet", "vitens", "evides", "brabant water", "pwn", "dunea",
|
|
"oasen", "wml"]),
|
|
"Internet & phone": ("expense", "#0288d1", ["ziggo", "kpn", "odido", "t-mobile", "vodafone", "simyo", "lebara",
|
|
"youfone", "delta fiber", "tele2", "re:\\bben\\b"]),
|
|
"Insurance": ("expense", "#5e35b1", ["zilveren kruis", "re:\\bcz\\b", "vgz", "menzis", "ohra", "centraal beheer",
|
|
"fbto", "interpolis", "nationale-nederlanden", "nationale nederlanden",
|
|
"a.s.r", "asr", "unive", "inshared", "dsw", "anderzorg", "zekur",
|
|
"promovendum", "allianz"]),
|
|
"Taxes": ("expense", "#455a64", ["belastingdienst", "gemeentelijke belastingen", "waterschap", "cjib",
|
|
"rdw", "re:^gemeente\\b", "bsgw", "gbt"]),
|
|
"Groceries": ("expense", "#43a047", ["albert heijn", "jumbo", "lidl", "aldi", "re:\\bplus\\b", "dirk",
|
|
"picnic", "ekoplaza", "vomar", "hoogvliet", "re:\\bcoop\\b", "spar",
|
|
"crisp", "flink", "dekamarkt", "nettorama", "re:\\bah\\b"]),
|
|
"Eating out": ("expense", "#ef6c00", ["thuisbezorgd", "uber eats", "deliveroo", "mcdonalds", "starbucks",
|
|
"burger king", "kfc", "dominos", "new york pizza", "febo", "restaurant",
|
|
"cafe", "eetcafe", "brasserie", "lunchroom"]),
|
|
"Transport": ("expense", "#00897b", ["ns groep", "ns reizigers", "re:\\bns\\b", "ov-chipkaart", "translink",
|
|
"gvb", "re:\\bret\\b", "htm", "arriva", "qbuzz", "connexxion", "keolis",
|
|
"uber", "bolt", "swapfiets", "felyx", "check technologies"]),
|
|
"Car": ("expense", "#546e7a", ["shell", "re:\\bbp\\b", "esso", "tinq", "tango", "totalenergies", "texaco",
|
|
"parkeren", "q-park", "anwb", "yellowbrick", "parkmobile", "easypark",
|
|
"fastned", "allego", "vandebron laad"]),
|
|
"Subscriptions": ("expense", "#d81b60", ["netflix", "spotify", "disney plus", "disneyplus", "videoland",
|
|
"apple.com/bill", "apple.com", "itunes", "google one",
|
|
"youtube premium", "amazon prime", "prime video", "hbo max",
|
|
"max.com", "chatgpt", "openai", "anthropic", "claude.ai", "icloud",
|
|
"patreon", "nlziet", "viaplay", "storytel", "audible",
|
|
"basic-fit", "basic fit", "sportcity", "trainmore"]),
|
|
"Shopping": ("expense", "#8e24aa", ["bol.com", "coolblue", "amazon", "zalando", "ikea", "action", "hema",
|
|
"kruidvat", "etos", "mediamarkt", "gamma", "praxis", "karwei",
|
|
"hornbach", "blokker", "primark", "h&m", "decathlon", "temu",
|
|
"aliexpress", "wehkamp", "intertoys"]),
|
|
"Health": ("expense", "#e53935", ["apotheek", "tandarts", "huisarts", "fysio", "mondzorg", "ziekenhuis",
|
|
"opticien", "pearle", "hans anders"]),
|
|
"Entertainment": ("expense", "#fb8c00", ["pathe", "vue", "kinepolis", "ticketmaster", "eventim",
|
|
"steam", "playstation", "nintendo", "xbox"]),
|
|
"Travel": ("expense", "#1e88e5", ["booking.com", "airbnb", "klm", "transavia", "ryanair", "easyjet",
|
|
"vueling", "schiphol", "hotel", "flixbus"]),
|
|
"Investments": ("transfer", "#3949ab", ["degiro", "trade republic", "bux", "bitvavo", "coinbase", "kraken",
|
|
"meesman", "brand new day", "peaks", "lynx", "saxo", "binance",
|
|
"goud", "holland gold", "goudwisselkantoor"]),
|
|
"Debt": ("expense", "#c62828", ["duo", "dienst uitvoering onderwijs", "aflossing", "lening"]),
|
|
"Cash": ("expense", "#757575", ["geldautomaat", "re:\\batm\\b", "cash withdrawal", "geldopname"]),
|
|
"Fees & interest": ("expense", "#9e9e9e", ["kosten oranjepakket", "kosten betaalpakket", "basic fee",
|
|
"premium plan fee", "metal plan fee", "rente", "interest"]),
|
|
"Other": ("expense", "#bdbdbd", []),
|
|
}
|
|
|
|
|
|
def seed(db: Session) -> None:
|
|
if db.scalar(select(Category.id).limit(1)) is not None:
|
|
return
|
|
for name, (kind, color, patterns) in SEED.items():
|
|
cat = Category(name=name, kind=kind, color=color)
|
|
db.add(cat)
|
|
db.flush()
|
|
prio = 50 if name in ("Transfers", "Investments", "Salary") else 100
|
|
for p in patterns:
|
|
db.add(Rule(pattern=p, field="any", category_id=cat.id, priority=prio))
|
|
db.commit()
|