bank-statement-api / core /helpers.py
3v324v23's picture
Feat: Added New column in xlsx
fbb917f
Raw History Blame Contribute Delete
13.2 kB
import re
import logging
import json
from datetime import date, datetime
from decimal import Decimal
from typing import List, Dict, Any, Optional
from core.state import statements, _uploads_dir
from core.logging_utils import _swap_logger
from bank_enrichment import enrich_transactions, confidence_summary
logger = logging.getLogger("befree.helpers")
def _normalize_description(desc: str) -> str:
"""Lowercase + collapse whitespace for description matching."""
return re.sub(r'\s+', ' ', (desc or '').strip().lower())
def _filter_transactions_by_last_n_months(transactions: List[dict], n: int = 3) -> List[dict]:
"""Return only the latest n calendar months from a transaction list."""
tx_months = set()
for tx in transactions:
d_raw = tx.get('date')
if d_raw:
try:
d = datetime.fromisoformat(str(d_raw).split('T')[0]).date()
tx_months.add((d.year, d.month))
except Exception:
pass
if not tx_months:
return transactions.copy()
sorted_months = sorted(tx_months)
keep_months = set(sorted_months[-n:])
filtered = []
for tx in transactions:
d_raw = tx.get('date')
if d_raw:
try:
d = datetime.fromisoformat(str(d_raw).split('T')[0]).date()
if (d.year, d.month) in keep_months:
filtered.append(tx)
except Exception:
filtered.append(tx)
else:
filtered.append(tx)
return filtered
def clean_audit_flags(flags: Any) -> List[str]:
if not flags:
return []
if isinstance(flags, str):
if flags.startswith("[") and flags.endswith("]"):
try:
import ast
parsed = ast.literal_eval(flags)
if isinstance(parsed, list):
return [str(x) for x in parsed]
except Exception:
pass
return [flags]
if isinstance(flags, list):
return [str(x) for x in flags]
try:
return list(flags)
except Exception:
return []
def _tx_sort_key(tx: dict) -> tuple:
"""Build a sort key that uses numeric page/row from the tx_id."""
page = tx.get("pageNumber", 1)
tx_id = tx.get("id", "")
try:
parts = tx_id.split("_")
if len(parts) >= 3:
row_num = int(parts[2])
else:
row_num = 0
except (ValueError, IndexError):
row_num = 0
return (page, row_num, tx_id)
def _to_transaction_row_global(tx: Any, statement_id: str) -> Dict[str, Any]:
amount = float(tx.amount) if tx.amount is not None else None
confidence = float(getattr(tx, "confidence", 0.0) or 0.0)
calc_bal = getattr(tx, "calculated_balance", None)
extr_bal = getattr(tx, "balance_after", None)
audit_flags_from_extractor = list(getattr(tx, "audit_flags", []) or [])
auto_swapped = any("Auto-corrected: debit/credit swapped" in f for f in audit_flags_from_extractor)
if auto_swapped and amount is not None:
amount = -amount
_swap_logger.debug(
f" [UNDO extractor swap] tx={tx.tx_id} | "
f"extractor gave +{-amount}, reversed to {amount}",
statement_id=statement_id
)
audit_flags_from_extractor = [
f for f in audit_flags_from_extractor
if "Auto-corrected: debit/credit swapped" not in f
]
debit = abs(amount) if amount is not None and amount < 0 else None
credit = amount if amount is not None and amount > 0 else None
if calc_bal is not None and extr_bal is not None:
from decimal import Decimal as _D
from shared.constants import BALANCE_TOLERANCE
balance_mismatch = abs(_D(str(calc_bal)) - _D(str(extr_bal))) > BALANCE_TOLERANCE
else:
balance_mismatch = False
status = "flagged" if balance_mismatch else "valid"
raw_page_num = getattr(tx, "page_num", None)
try:
page_number = int(raw_page_num) + 1 if raw_page_num is not None else 1
except Exception:
page_number = 1
stated_bal = float(extr_bal) if extr_bal is not None else None
if stated_bal is not None and amount is not None:
if abs(stated_bal - abs(amount)) < 0.01:
if calc_bal is not None and abs(float(calc_bal) - stated_bal) > 1.0:
stated_bal = None
return {
"id": tx.tx_id or f"tx_{tx.page_num}_{tx.row_num}",
"pageNumber": page_number,
"date": tx.date.isoformat() if getattr(tx, "date", None) else (tx.date_raw or ""),
"description": tx.description or tx.desc_raw or "",
"debit": debit,
"credit": credit,
"balance": float(calc_bal) if calc_bal is not None else None,
"calculatedBalance": stated_bal,
"confidence": confidence,
"status": status,
"suggestedCorrections": None,
"category": None,
"third_party": None,
"categoryConfidence": None,
"categorySource": None,
"auditFlags": audit_flags_from_extractor if audit_flags_from_extractor else
(["Balance mismatch"] if balance_mismatch else []),
"source_extractor": getattr(tx, "source_extractor", "coordinate_locked") or "coordinate_locked",
}
def _heal_negated_cr_balances(statement_id: str) -> int:
"""Fix legacy cached data where CR-suffix balances were incorrectly negated."""
return 0
def _sort_dicts_chronologically(transactions: list[dict]) -> list[dict]:
if not transactions:
return transactions
pages = {}
for tx in transactions:
p = tx.get("pageNumber", 1)
pages.setdefault(p, []).append(tx)
sorted_txs = []
def get_row_num(t):
tx_id = t.get("id", "")
try:
parts = tx_id.split("_")
if len(parts) >= 3:
return int(parts[2])
except (ValueError, IndexError):
pass
return 0
page_dates = {}
for p_num, p_txs in pages.items():
dates = []
for t in p_txs:
d_str = t.get("date")
if d_str:
try:
dates.append(datetime.fromisoformat(d_str).date())
except Exception:
pass
if dates:
page_dates[p_num] = sum((d - date(1970, 1, 1)).days for d in dates) / len(dates)
is_pages_desc = True
if len(page_dates) >= 2:
sorted_p_nums = sorted(page_dates.keys())
desc_p_count = sum(1 for i in range(len(sorted_p_nums)-1) if page_dates[sorted_p_nums[i]] > page_dates[sorted_p_nums[i+1]])
asc_p_count = sum(1 for i in range(len(sorted_p_nums)-1) if page_dates[sorted_p_nums[i]] < page_dates[sorted_p_nums[i+1]])
if asc_p_count > desc_p_count:
is_pages_desc = False
sorted_page_nums = sorted(pages.keys(), reverse=is_pages_desc)
for p_num in sorted_page_nums:
p_txs = pages[p_num]
raw_sorted = sorted(p_txs, key=get_row_num)
dates = []
for t in raw_sorted:
d_str = t.get("date")
if d_str:
try:
dates.append(datetime.fromisoformat(d_str).date())
except Exception:
pass
is_page_desc = True
if len(dates) >= 2:
desc_count = sum(1 for i in range(len(dates)-1) if dates[i] > dates[i+1])
asc_count = sum(1 for i in range(len(dates)-1) if dates[i] < dates[i+1])
if asc_count > desc_count:
is_page_desc = False
else:
balances = []
for t in raw_sorted:
b = t.get("calculatedBalance")
if b is not None:
try:
balances.append(float(b))
except Exception:
pass
if len(balances) >= 2:
inc_count = sum(1 for i in range(len(balances)-1) if balances[i] < balances[i+1])
dec_count = sum(1 for i in range(len(balances)-1) if balances[i] > balances[i+1])
if inc_count > dec_count:
is_page_desc = False
if is_page_desc:
sorted_txs.extend(sorted(p_txs, key=lambda x: -get_row_num(x)))
else:
sorted_txs.extend(sorted(p_txs, key=get_row_num))
return sorted_txs
def update_statement_confidence(statement_id: str):
if statement_id not in statements:
return
stmt = statements[statement_id]
transactions = stmt.transactions
if not transactions:
stmt.accuracy = 0.0
return
_swap_logger.debug(f"\n{'─'*80}", statement_id=statement_id)
_swap_logger.debug(f"[update_confidence] START for {statement_id}", statement_id=statement_id)
_swap_logger.debug(f"{'─'*80}", statement_id=statement_id)
sorted_txs = _sort_dicts_chronologically(transactions)
raw_list = []
for tx in sorted_txs:
r_tx = tx.copy()
r_tx['page_num'] = r_tx.pop('pageNumber', 1)
r_tx['tx_id'] = r_tx['id']
debit = r_tx.get('debit')
credit = r_tx.get('credit')
if debit is None and credit is None:
r_tx['amount'] = None
else:
r_tx['amount'] = (credit or 0.0) - (debit or 0.0)
if 'calculatedBalance' in r_tx:
r_tx['balance_after'] = r_tx['calculatedBalance']
elif 'balance' in r_tx:
r_tx['balance_after'] = r_tx['balance']
raw_list.append(r_tx)
enriched_list = enrich_transactions(raw_list)
csm = confidence_summary(enriched_list)
enriched_map = {t['tx_id']: t for t in enriched_list if 'tx_id' in t}
for tx in transactions:
enr = enriched_map.get(tx['id'])
if enr:
tx['confidence'] = enr.get('confidence_score', 1.0)
if tx.get('status') not in ['verified', 'corrected']:
old_bal = tx.get('balance')
old_calc = tx.get('calculatedBalance')
new_bal = enr.get('calculated_balance')
new_calc = enr.get('balance_after')
new_status = 'valid' if enr.get('is_balanced', True) else 'flagged'
_swap_logger.debug(
f" [enrich] tx={tx['id']} | "
f"balance: {old_bal} → {new_bal} | "
f"calcBal: {old_calc} → {new_calc} | "
f"status: {tx.get('status')} → {new_status} | "
f"is_balanced={enr.get('is_balanced')} bal_err={enr.get('balance_error')}",
statement_id=statement_id
)
tx['balance'] = new_bal
tx['calculatedBalance'] = new_calc
if tx.get('balanceOverride') is not None:
tx['balance'] = tx['balanceOverride']
tx['status'] = new_status
flags = list(enr.get('validation_errors', []))
if not enr.get('is_balanced', True):
err_val = enr.get('balance_error')
flags.append(f"Balance mismatch: {err_val}" if err_val else "Balance mismatch")
if enr.get('gap_detected'):
flags.append("Date gap detected")
tx['auditFlags'] = flags if flags else None
else:
_swap_logger.debug(
f" [enrich] tx={tx['id']} | PROTECTED ({tx.get('status')}) — "
f"keeping balance={tx.get('balance')} calcBal={tx.get('calculatedBalance')} | "
f"enrichment would have set: bal={enr.get('calculated_balance')} calcBal={enr.get('balance_after')}"
)
active_txs = [tx for tx in transactions if tx.get('status') != 'deleted']
total_active = len(active_txs)
accurate_count = sum(1 for tx in active_txs if tx.get('status') in ['valid', 'corrected', 'verified'])
stmt.accuracy = round((accurate_count / total_active) * 100, 1) if total_active > 0 else 0.0
_swap_logger.debug(f"[update_confidence] DONE: accuracy={stmt.accuracy}% ({accurate_count}/{total_active})\n", statement_id=statement_id)
return csm
def _get_source_file_name(stmt, tx: dict) -> str:
"""Return the human-readable filename of the bank statement that produced this transaction."""
file_index = tx.get("file_index", 0)
source_file_names = getattr(stmt, "source_file_names", [])
if source_file_names and file_index < len(source_file_names):
name = source_file_names[file_index]
if name.lower().endswith(".pdf"):
name = name[:-4]
return name
pdf_paths = getattr(stmt, "pdf_paths", [])
if pdf_paths and file_index < len(pdf_paths):
name = os.path.basename(pdf_paths[file_index])
if name.lower().endswith(".pdf"):
name = name[:-4]
return name
return stmt.file_name if stmt.file_name else "Unknown"