bank-statement-api / core /statement_io.py
3v324v23's picture
Feat: Added New column in xlsx
fbb917f
Raw History Blame Contribute Delete
15.8 kB
import io
import os
import json
import logging
import re
from pathlib import Path
from typing import Any, Optional
from decimal import Decimal
from datetime import date, datetime
import openpyxl
from core.state import statements, _output_dir, _uploads_dir
from core.logging_utils import _swap_logger
from core.helpers import _tx_sort_key, _sort_dicts_chronologically, _heal_negated_cr_balances
logger = logging.getLogger("befree.statement_io")
try:
from bank_enrichment import enrich_transactions, confidence_summary
_bank_enrichment_available = True
except ImportError:
_bank_enrichment_available = False
def enrich_transactions(txs, *a, **kw): return txs
def confidence_summary(*a, **kw): return {}
def _persist_swap_to_xlsx(statement_id: str) -> None:
"""Write the current in-memory transaction data back to the persisted XLSX."""
if statement_id not in statements:
return
stmt = statements[statement_id]
xlsx_path = getattr(stmt, "xlsx_path", None)
if not xlsx_path or not os.path.exists(xlsx_path):
return
try:
from shared.models import PageAudit, StatementAudit, NormTransaction
from shared.constants import TxType
from bank_extractor.writer import write_excel
import json
pages_dict = {}
for tx in stmt.transactions:
pages_dict.setdefault(tx.get("pageNumber", 1), []).append(tx)
pages_audit = []
for p_num, p_txs in sorted(pages_dict.items()):
p_txs_sorted = sorted(p_txs, key=_tx_sort_key)
closing = 0.0
for tx in p_txs_sorted:
if tx.get("calculatedBalance") is not None:
closing = float(tx.get("calculatedBalance"))
break
opening = closing - sum((float(tx.get("credit") or 0.0) - float(tx.get("debit") or 0.0)) for tx in p_txs)
delta = 0.0
page_flagged = sum(1 for tx in p_txs if tx.get("auditFlags") or tx.get("status") == "flagged")
if page_flagged > 0:
for tx in p_txs_sorted:
flags = tx.get("auditFlags") or []
if isinstance(flags, str):
try:
flags = json.loads(flags)
except Exception:
flags = [flags]
for f in flags:
if "balance_mismatch" in f or "Balance mismatch" in f:
m = re.search(r"delta=([\d\.]+)", f)
if m:
try:
delta = max(delta, float(m.group(1)))
except Exception:
delta = 1.0
else:
delta = 1.0
if delta == 0.0:
delta = 1.0
pages_audit.append(PageAudit(
page_num=p_num,
opening_balance=Decimal(str(opening)),
closing_balance=Decimal(str(closing)),
computed_balance=Decimal(str(closing)),
delta=Decimal(str(delta)),
status="clean" if page_flagged == 0 else "error",
issues=[]
))
aud_rep = StatementAudit(
statement_id=statement_id,
bank_id=stmt.bank_name or "generic",
pages=pages_audit,
extract_last_3_months=getattr(stmt, "extract_last_3_months", None)
)
norm_txs_for_excel = []
for tx in stmt.transactions:
debit = tx.get("debit")
credit = tx.get("credit")
amount = (credit or 0.0) - (debit or 0.0)
page_num_val = tx.get("pageNumber", 1) - 1
tx_id_val = tx.get("id", "")
parts = tx_id_val.split("_")
row_num_val = 999
if len(parts) >= 3:
try:
row_num_val = int(parts[2])
except ValueError:
pass
date_val = None
d_raw = tx.get("date")
if d_raw:
if isinstance(d_raw, date):
date_val = d_raw
elif isinstance(d_raw, datetime):
date_val = d_raw.date()
else:
try:
date_val = datetime.fromisoformat(str(d_raw).split("T")[0]).date()
except Exception:
pass
raw_flags = tx.get("auditFlags") or []
if isinstance(raw_flags, str):
try:
raw_flags = json.loads(raw_flags)
except Exception:
raw_flags = [raw_flags]
cat_val = tx.get("category") or ""
third_party_val = tx.get("thirdParty") or tx.get("third_party") or ""
confidence_val = tx.get("categoryConfidence") or tx.get("category_confidence") or 1.0
source_val = tx.get("categorySource") or tx.get("category_source") or "none"
norm = NormTransaction(
tx_id=tx_id_val,
page_num=page_num_val,
row_num=row_num_val,
date=date_val,
description=tx.get("description", ""),
desc_raw=tx.get("description", ""),
amount=Decimal(str(amount)),
tx_type=TxType.DEBIT if amount < 0 else TxType.CREDIT,
balance_after=Decimal(str(tx.get("calculatedBalance"))) if tx.get("calculatedBalance") is not None else None,
confidence=tx.get("confidence", 1.0),
audit_flags=raw_flags,
source_extractor=tx.get("source_extractor", "coordinate_locked")
)
setattr(norm, "category", cat_val)
setattr(norm, "third_party", third_party_val)
setattr(norm, "category_confidence", confidence_val)
setattr(norm, "category_source", source_val)
norm_txs_for_excel.append(norm)
write_excel(
norm_txs_for_excel,
aud_rep,
xlsx_path,
statement_id,
"",
"coordinate_locked",
stmt.bank_name or "generic",
0.0
)
logger.info(f"[PersistSwap] Successfully regenerated and wrote full premium XLSX for {statement_id}")
except Exception as e:
logger.error(f"[PersistSwap] Failed to regenerate XLSX for {statement_id}: {e}", exc_info=True)
def _try_auto_swap(
debit: Optional[float],
credit: Optional[float],
balance_before: float,
stated_balance: float,
tolerance: float = 0.01,
tx_id: str = "",
statement_id: Optional[str] = None,
) -> tuple[Optional[float], Optional[float], float, bool]:
"""Try swapping debit and credit. If swap matches stated balance, return swapped values."""
from decimal import Decimal
def calc_balance(db, cr, prev):
result = prev
if db is not None and float(db) > 0:
result -= float(db)
if cr is not None and float(cr) > 0:
result += float(cr)
return round(result, 2)
original_calc = calc_balance(debit, credit, balance_before)
trial_calc = calc_balance(credit, debit, balance_before) # swapped
original_diff = abs(Decimal(str(original_calc)) - Decimal(str(stated_balance)))
trial_diff = abs(Decimal(str(trial_calc)) - Decimal(str(stated_balance)))
will_swap = trial_diff <= Decimal(str(tolerance)) and trial_diff < original_diff
_swap_logger.debug(
f" [_try_auto_swap] tx={tx_id} | "
f"debit={debit} credit={credit} | "
f"bal_before={balance_before} stated={stated_balance} | "
f"original_calc={original_calc} (diff={original_diff}) | "
f"trial_calc={trial_calc} (diff={trial_diff}) | "
f"SWAP={will_swap}",
statement_id=statement_id
)
if will_swap:
return credit, debit, trial_calc, True
else:
return debit, credit, original_calc, False
def auto_swap_all_transactions(statement_id: str) -> int:
"""Walk every transaction and auto-swap debit/credit if it fixes balance mismatch."""
if statement_id not in statements:
return 0
stmt = statements[statement_id]
transactions = stmt.transactions
if not transactions:
return 0
sorted_txs = _sort_dicts_chronologically(transactions)
_swap_logger.debug(f"\n{'='*80}", statement_id=statement_id)
_swap_logger.debug(f"[auto_swap_all] START for {statement_id}, {len(sorted_txs)} transactions", statement_id=statement_id)
_swap_logger.debug(f"{'='*80}", statement_id=statement_id)
running_balance: float | None = None
swapped_count = 0
prev_file_idx: int | None = None
for idx, tx in enumerate(sorted_txs):
tx_id = tx.get("id", "?")
desc = (tx.get("description") or "")[:40]
status = tx.get("status")
debit = tx.get("debit")
credit = tx.get("credit")
stated = tx.get("calculatedBalance")
bal = tx.get("balance")
current_file_idx = tx.get("file_index", 0)
if prev_file_idx is not None and current_file_idx != prev_file_idx:
_swap_logger.debug(
f"[auto_swap_all] File index boundary detected ({prev_file_idx} -> {current_file_idx}). "
f"Resetting running_balance (previous: {running_balance}) to None.",
statement_id=statement_id
)
running_balance = None
prev_file_idx = current_file_idx
_swap_logger.debug(
f"[Row {idx}] tx={tx_id} | page={tx.get('pageNumber')} date={tx.get('date')} | "
f"desc=\"{desc}\" | debit={debit} credit={credit} | "
f"balance={bal} stated={stated} | status={status} | running_bal={running_balance}",
statement_id=statement_id
)
if status in ["verified", "corrected"] or tx.get("balanceOverride") is not None:
if tx.get("balanceOverride") is not None:
running_balance = float(tx["balanceOverride"])
tx["balance"] = running_balance
_swap_logger.debug(f" β†’ SKIP (override), running_bal={running_balance}", statement_id=statement_id)
elif tx.get("balance") is not None:
running_balance = float(tx["balance"])
_swap_logger.debug(f" β†’ SKIP ({status}), running_bal={running_balance}", statement_id=statement_id)
else:
if running_balance is not None:
d = tx.get("debit")
c = tx.get("credit")
if d is not None and float(d) > 0:
running_balance -= float(d)
if c is not None and float(c) > 0:
running_balance += float(c)
running_balance = round(running_balance, 2)
tx["balance"] = running_balance
_swap_logger.debug(f" β†’ SKIP ({status}, no bal), calc running_bal={running_balance}", statement_id=statement_id)
continue
if running_balance is None:
if tx.get("balance") is not None:
running_balance = float(tx["balance"])
_swap_logger.debug(f" β†’ ANCHOR from balance={running_balance}", statement_id=statement_id)
elif stated is not None:
running_balance = float(stated)
tx["balance"] = running_balance
_swap_logger.debug(f" β†’ ANCHOR from stated={running_balance}", statement_id=statement_id)
else:
_swap_logger.debug(f" β†’ NO ANCHOR, skip", statement_id=statement_id)
continue
prev_balance = running_balance
expected = prev_balance
if debit is not None and float(debit) > 0: expected -= float(debit)
if credit is not None and float(credit) > 0: expected += float(credit)
expected = round(expected, 2)
_swap_logger.debug(
f" calc: {prev_balance} + d={debit} c={credit} β†’ expected={expected} vs stated={stated}",
statement_id=statement_id
)
stated_is_unreliable = False
if stated is not None:
s = float(stated)
cr_f = float(credit) if credit is not None else None
db_f = float(debit) if debit is not None else None
if (cr_f is not None and cr_f > 0 and abs(s - cr_f) < 0.01 and
abs(s - expected) > 1.0):
stated_is_unreliable = True
_swap_logger.debug(
f" ⚠ UNRELIABLE stated={stated} == credit={credit} β€” "
f"treating as missing (using computed balance)",
statement_id=statement_id
)
elif (db_f is not None and db_f > 0 and abs(s - db_f) < 0.01 and
abs(s - expected) > 1.0):
stated_is_unreliable = True
_swap_logger.debug(
f" ⚠ UNRELIABLE stated={stated} == debit={debit} β€” "
f"treating as missing (using computed balance)",
statement_id=statement_id
)
if stated_is_unreliable:
tx["balance"] = round(expected, 2)
tx["calculatedBalance"] = round(expected, 2)
running_balance = expected
_swap_logger.debug(f" β†’ FIXED unreliable stated. balance={expected}, running_bal={running_balance}", statement_id=statement_id)
elif stated is not None and abs(float(stated) - expected) > 0.01:
_swap_logger.debug(f" MISMATCH: |{stated} - {expected}| = {abs(float(stated) - expected):.4f} > 0.01 β†’ trying swap", statement_id=statement_id)
final_debit, final_credit, final_running_balance, was_swapped = _try_auto_swap(
debit=debit,
credit=credit,
balance_before=prev_balance,
stated_balance=float(stated),
tolerance=0.01,
tx_id=tx_id,
statement_id=statement_id,
)
if was_swapped:
tx["debit"] = final_debit
tx["credit"] = final_credit
tx["balance"] = round(final_running_balance, 2)
tx["calculatedBalance"] = round(final_running_balance, 2)
tx["status"] = "corrected"
tx["auditFlags"] = ["Auto-corrected: debit/credit swapped"]
running_balance = tx["balance"]
swapped_count += 1
_swap_logger.debug(
f" β†’ SWAPPED! debit={final_debit} credit={final_credit} "
f"balance={tx['balance']} running_bal={running_balance}",
statement_id=statement_id
)
continue
tx["balance"] = round(expected, 2)
running_balance = float(stated)
_swap_logger.debug(f" β†’ NO SWAP (didn't help). Set balance={tx['balance']}, trust stated running_bal={running_balance}", statement_id=statement_id)
else:
tx["balance"] = expected
running_balance = expected
_swap_logger.debug(f" β†’ OK. balance={expected}, running_bal={running_balance}", statement_id=statement_id)
stmt.transactions = sorted(sorted_txs, key=_tx_sort_key)
_swap_logger.debug(f"[auto_swap_all] DONE: swapped {swapped_count} rows\n", statement_id=statement_id)
return swapped_count