3v324v23's picture
Feat: Added New column in xlsx
fbb917f
Raw History Blame Contribute Delete
56 kB
import os import io import csv import logging import openpyxl import fitz # PyMuPDF from datetime import datetime from typing import Optional, List, Dict, Any from fastapi import APIRouter, Depends, HTTPException, Request from fastapi.responses import StreamingResponse from auth.models import User from auth.service import get_current_user from models.export_models import ExportRequest, ExportResponse, ExportSummary from core.state import statements, settings from core.helpers import _get_source_file_name, _filter_transactions_by_last_n_months, _tx_sort_key from core.statement_io import _persist_swap_to_xlsx logger = logging.getLogger("befree.export") router = APIRouter(prefix="/api/v1/statements", tags=["export"]) @router.get("/{statement_id}/export/summary", response_model=ExportSummary) async def get_export_summary(statement_id: str, current_user: User = Depends(get_current_user)): """Get export summary.""" if statement_id not in statements: raise HTTPException(status_code=404, detail="Statement not found") stmt = statements[statement_id] use_3_months = getattr(stmt, "extract_last_3_months", None) if use_3_months is None: use_3_months = settings.extractLast3Months raw_txs = stmt.transactions if use_3_months: raw_txs = _filter_transactions_by_last_n_months(raw_txs) unverified_count = 0 for tx in raw_txs: src = tx.get('categorySource') or tx.get('category_source') or '' verified = bool(tx.get('verified', False)) if (src.startswith('ml') or src.startswith('llm')) and not verified: unverified_count += 1 import calendar as _cal_export tx_months: set = set() for tx in raw_txs: 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 months_available = len(tx_months) if tx_months: sorted_months = sorted(tx_months) start_label = f"{_cal_export.month_name[sorted_months[0][1]]} {sorted_months[0][0]}" end_label = f"{_cal_export.month_name[sorted_months[-1][1]]} {sorted_months[-1][0]}" month_range_label = start_label if sorted_months[0] == sorted_months[-1] else f"{start_label} – {end_label}" else: month_range_label = "" return ExportSummary( statementId=statement_id, fileName=stmt.file_name, totalRows=len(raw_txs), correctedRows=0, deletedRows=0, accuracy=stmt.accuracy or 0, exportedAt=None, unverifiedCount=unverified_count, monthsAvailable=months_available, monthsUsedForAverage=3, monthRangeLabel=month_range_label, ) @router.post("/{statement_id}/export", response_model=ExportResponse) async def export_statement(statement_id: str, payload: ExportRequest, request: Request, current_user: User = Depends(get_current_user)): """Export statement.""" if statement_id not in statements: raise HTTPException(status_code=404, detail="Statement not found") stmt = statements[statement_id] download_url = f"{request.base_url}api/v1/statements/{statement_id}/download?format={payload.format}" auth_header = request.headers.get("authorization") if auth_header and auth_header.lower().startswith("bearer "): token = auth_header[7:] download_url += f"&token={token}" return ExportResponse( downloadUrl=download_url, fileName=f"{stmt.file_name.replace('.pdf', '')}.{payload.format}", ) @router.get("/{statement_id}/download") async def download_statement(statement_id: str, format: str = "xlsx", current_user: User = Depends(get_current_user)): """Download the processed statement in the requested format.""" if statement_id not in statements: raise HTTPException(status_code=404, detail="Statement not found") stmt = statements[statement_id] def is_zero(val): if val is None: return True try: return float(val) == 0 except (ValueError, TypeError): return True def _is_credit_tx(tx: dict) -> bool: return not is_zero(tx.get("credit")) and is_zero(tx.get("debit")) def _is_additional_expenses_tx(tx: dict) -> bool: return str(tx.get("category") or "").strip() == "Additional Expenses" use_3_months = getattr(stmt, "extract_last_3_months", None) if use_3_months is None: use_3_months = settings.extractLast3Months raw_txs = stmt.transactions other_txs = [] if use_3_months: raw_txs = _filter_transactions_by_last_n_months(raw_txs) filtered_ids = {tx.get("id") for tx in raw_txs} other_txs = [ tx for tx in stmt.transactions if tx.get("id") not in filtered_ids and not (is_zero(tx.get("debit")) and is_zero(tx.get("credit"))) ] other_txs = sorted(other_txs, key=_tx_sort_key) transactions = [ tx for tx in raw_txs if not (is_zero(tx.get("debit")) and is_zero(tx.get("credit"))) ] main_transactions = [tx for tx in transactions if not _is_additional_expenses_tx(tx)] additional_expense_transactions = [tx for tx in transactions if _is_additional_expenses_tx(tx)] try: if format == "xlsx_audit": xlsx_path = getattr(stmt, "xlsx_path", None) if not xlsx_path or not os.path.exists(xlsx_path): _persist_swap_to_xlsx(statement_id) xlsx_path = getattr(stmt, "xlsx_path", None) if xlsx_path and os.path.exists(xlsx_path): safe_name = stmt.file_name.replace('.pdf', '').replace('"', '') + '_reconciliation_report.xlsx' with open(xlsx_path, 'rb') as fh: raw = fh.read() return StreamingResponse( io.BytesIO(raw), media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", headers={"Content-Disposition": f'attachment; filename="{safe_name}"'} ) raise HTTPException(status_code=404, detail="Audit spreadsheet not found") elif format == "csv": output = io.StringIO() writer = csv.DictWriter(output, fieldnames=["date", "description", "debit", "credit", "balance", "category", "third_party", "status", "auditFlags"]) writer.writeheader() for tx in transactions: row = { "date": tx.get("date"), "description": tx.get("description"), "debit": tx.get("debit"), "credit": tx.get("credit"), "balance": tx.get("balance"), "category": tx.get("category", ""), "third_party": tx.get("third_party", ""), "status": tx.get("status"), "auditFlags": ", ".join(tx.get("auditFlags", [])) if tx.get("auditFlags") else "" } writer.writerow(row) output.seek(0) safe_name = stmt.file_name.replace('.pdf', '').replace('"', '') + '.csv' return StreamingResponse( io.BytesIO(output.getvalue().encode()), media_type="text/csv", headers={"Content-Disposition": f'attachment; filename="{safe_name}"'} ) elif format == "xlsx": wb = openpyxl.Workbook() # Shared styles tx_hdr_fill = openpyxl.styles.PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid") tx_hdr_font = openpyxl.styles.Font(bold=True, color="FFFFFF", size=11) tx_section_font = openpyxl.styles.Font(bold=True, size=12, color="FFFFFF") tx_section_fill_dark = openpyxl.styles.PatternFill(start_color="1F3864", end_color="1F3864", fill_type="solid") tx_avg_fill = openpyxl.styles.PatternFill(start_color="D6E4F7", end_color="D6E4F7", fill_type="solid") tx_avg_font = openpyxl.styles.Font(bold=True, size=11) # ── Temp/review column styles ────────────────────────────────────── temp_hdr_fill = openpyxl.styles.PatternFill(start_color="ED7D31", end_color="ED7D31", fill_type="solid") temp_hdr_font = openpyxl.styles.Font(bold=True, color="FFFFFF", size=11) def _get_temp_cols(tx: dict): """Return (method_label, confidence_label, reason) for temp columns.""" src = (tx.get("categorySource") or tx.get("category_source") or "").strip() reason = tx.get("classification_reason") or tx.get("llm_reasoning") or "—" # category_source format: "rule:RULE_NAME", "llm:0.90", "ml:0.85", # "manual", "fallback:heuristic", "unknown" src_lower = src.lower() if src_lower.startswith("rule"): method = "Rule" # Confidence is always 100% for rule matches conf_label = "100%" # Show the rule name from the source string if reason is empty if reason == "—" and ":" in src: reason = src.split(":", 1)[1] elif src_lower.startswith("ml"): method = "AI Suggestion" # Parse confidence from "ml:0.85" if ":" in src: try: conf_label = f"{float(src.split(':', 1)[1]):.0%}" except ValueError: conf_label = "—" else: conf = tx.get("categoryConfidence") or tx.get("category_confidence") conf_label = f"{float(conf):.0%}" if conf is not None else "—" elif src_lower.startswith("llm"): method = "AI Suggestion" # Parse confidence from "llm:0.90" if ":" in src: try: conf_label = f"{float(src.split(':', 1)[1]):.0%}" except ValueError: conf_label = "—" else: conf = tx.get("categoryConfidence") or tx.get("category_confidence") conf_label = f"{float(conf):.0%}" if conf is not None else "—" elif src_lower == "manual": method = "Manual" conf_label = "100%" elif src_lower.startswith("fallback"): method = "Fallback" conf = tx.get("categoryConfidence") or tx.get("category_confidence") conf_label = f"{float(conf):.0%}" if conf is not None else "—" else: method = src if src else "—" conf = tx.get("categoryConfidence") or tx.get("category_confidence") conf_label = f"{float(conf):.0%}" if conf is not None else "—" return method, conf_label, reason or "—" def _temp_col_fill(method: str): return None # no fill — white cells matching rest of sheet # Sheet 1: Transactions ws = wb.active ws.title = "Transactions" tx_headers = [ "Date", "Description", "Debit", "Credit", "Balance", "Category", "Bank Statement", "Status", "Issues", # ── TEMP review columns ── "⚙ Classification Method", "⚙ Confidence", "⚙ Reason / Rule", ] ws.append(tx_headers) for i, cell in enumerate(ws[1]): if i < 9: cell.font = tx_hdr_font cell.fill = tx_hdr_fill else: cell.font = temp_hdr_font cell.fill = temp_hdr_fill for tx in main_transactions: method, conf_label, reason = _get_temp_cols(tx) ws.append([ tx.get("date"), tx.get("description"), tx.get("debit"), tx.get("credit"), tx.get("balance"), tx.get("category", ""), _get_source_file_name(stmt, tx), tx.get("status"), ", ".join(tx.get("auditFlags", [])) if tx.get("auditFlags") else "", method, conf_label, reason, ]) # Style the temp columns per row — plain white, normal font for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=10, max_col=12): for cell in row: cell.font = openpyxl.styles.Font(size=11) ws.column_dimensions['A'].width = 15 ws.column_dimensions['B'].width = 40 ws.column_dimensions['C'].width = 12 ws.column_dimensions['D'].width = 12 ws.column_dimensions['E'].width = 12 ws.column_dimensions['F'].width = 20 ws.column_dimensions['G'].width = 30 ws.column_dimensions['H'].width = 12 ws.column_dimensions['I'].width = 30 ws.column_dimensions['J'].width = 22 ws.column_dimensions['K'].width = 13 ws.column_dimensions['L'].width = 55 ws.freeze_panes = "A2" for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=1, max_col=12): for cell in row: cell.alignment = openpyxl.styles.Alignment(wrap_text=True, vertical="top") if use_3_months and other_txs: ws_other = wb.create_sheet(title="Other Transactions") ws_other.append(tx_headers) for i, cell in enumerate(ws_other[1]): if i < 9: cell.font = tx_hdr_font cell.fill = tx_hdr_fill else: cell.font = temp_hdr_font cell.fill = temp_hdr_fill for tx in other_txs: method, conf_label, reason = _get_temp_cols(tx) ws_other.append([ tx.get("date"), tx.get("description"), tx.get("debit"), tx.get("credit"), tx.get("balance"), tx.get("category", ""), _get_source_file_name(stmt, tx), tx.get("status"), ", ".join(tx.get("auditFlags", [])) if tx.get("auditFlags") else "", method, conf_label, reason, ]) for row in ws_other.iter_rows(min_row=2, max_row=ws_other.max_row, min_col=10, max_col=12): for cell in row: cell.font = openpyxl.styles.Font(size=11) ws_other.column_dimensions['A'].width = 15 ws_other.column_dimensions['B'].width = 40 ws_other.column_dimensions['C'].width = 12 ws_other.column_dimensions['D'].width = 12 ws_other.column_dimensions['E'].width = 12 ws_other.column_dimensions['F'].width = 20 ws_other.column_dimensions['G'].width = 30 ws_other.column_dimensions['H'].width = 12 ws_other.column_dimensions['I'].width = 30 ws_other.column_dimensions['J'].width = 22 ws_other.column_dimensions['K'].width = 13 ws_other.column_dimensions['L'].width = 55 ws_other.freeze_panes = "A2" for row in ws_other.iter_rows(min_row=2, max_row=ws_other.max_row, min_col=1, max_col=12): for cell in row: cell.alignment = openpyxl.styles.Alignment(wrap_text=True, vertical="top") # Sheet 2: Summary ws_txsum = wb.create_sheet(title="Summary") tx_month_data: Dict[tuple, Dict[str, Any]] = {} total_debit_all = 0.0 total_credit_all = 0.0 total_count_all = 0 for tx in main_transactions: d_raw = tx.get("date") ym = None if d_raw: try: d = datetime.fromisoformat(str(d_raw).split("T")[0]).date() ym = (d.year, d.month) except Exception: pass if ym is None: ym = (0, 0) if ym not in tx_month_data: tx_month_data[ym] = {"debit": 0.0, "credit": 0.0, "count": 0} dv = float(tx.get("debit") or 0) cv = float(tx.get("credit") or 0) tx_month_data[ym]["debit"] += dv tx_month_data[ym]["credit"] += cv tx_month_data[ym]["count"] += 1 total_debit_all += dv total_credit_all += cv total_count_all += 1 unique_sources: list[str] = [] seen_sources: set[str] = set() for tx in main_transactions: src = _get_source_file_name(stmt, tx) if src not in seen_sources: unique_sources.append(src) seen_sources.add(src) source_totals: dict[str, dict] = { src: {"debit": 0.0, "credit": 0.0, "count": 0} for src in unique_sources } for tx in main_transactions: src = _get_source_file_name(stmt, tx) if src in source_totals: source_totals[src]["debit"] += float(tx.get("debit") or 0) source_totals[src]["credit"] += float(tx.get("credit") or 0) source_totals[src]["count"] += 1 real_months_tx = sorted(ym for ym in tx_month_data if ym != (0, 0)) num_months_tx = max(len(real_months_tx), 1) divisor_tx = 3 def _txsum_section(ws, text, ncols): ws.append([text]) r = ws.max_row ws.cell(row=r, column=1).font = tx_section_font ws.cell(row=r, column=1).fill = tx_section_fill_dark ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=ncols) def _txsum_header(ws, cols): ws.append(cols) r = ws.max_row for c in range(1, len(cols) + 1): ws.cell(row=r, column=c).font = tx_hdr_font ws.cell(row=r, column=c).fill = tx_hdr_fill _txsum_section(ws_txsum, "Per-Bank-Statement Summary", ncols=5) _txsum_header(ws_txsum, ["Bank Statement", "Transaction Count", "Total Debit", "Total Credit", "Net (Credit - Debit)"]) for src in unique_sources: sd = source_totals[src] ws_txsum.append([ src, sd["count"], round(sd["debit"], 2), round(sd["credit"], 2), round(sd["credit"] - sd["debit"], 2) ]) ws_txsum.append([]) _txsum_section(ws_txsum, "Per-Bank-Statement Monthly Averages", ncols=5) _txsum_header(ws_txsum, ["Bank Statement", "Avg Transactions/Month", "Avg Monthly Debit", "Avg Monthly Credit", "Avg Monthly Net"]) for src in unique_sources: sd = source_totals[src] ws_txsum.append([ src, round(sd["count"] / divisor_tx, 1), round(sd["debit"] / divisor_tx, 2), round(sd["credit"] / divisor_tx, 2), round((sd["credit"] - sd["debit"]) / divisor_tx, 2) ]) ws_txsum.append([]) ws_txsum.append([]) _txsum_section(ws_txsum, "Combined Total Summary", ncols=2) _txsum_header(ws_txsum, ["Metric", "Value"]) ws_txsum.append(["Total Transactions", total_count_all]) ws_txsum.append(["Total Debit Amount", round(total_debit_all, 2)]) ws_txsum.append(["Total Credit Amount", round(total_credit_all, 2)]) ws_txsum.append(["Net Amount (Credit - Debit)", round(total_credit_all - total_debit_all, 2)]) ws_txsum.append([]) _txsum_section(ws_txsum, "Monthly Averages", ncols=2) _txsum_header(ws_txsum, ["Metric", "Monthly Average"]) if num_months_tx < 3: _note_text = ( f"Note: Only {num_months_tx} month(s) of data uploaded. " f"Monthly averages use a 3-month divisor; missing months counted as $0." ) _note_font = openpyxl.styles.Font(italic=True, size=9, bold=True, color="7D6608") _note_fill = openpyxl.styles.PatternFill(start_color="FFF2CC", end_color="FFF2CC", fill_type="solid") else: _note_text = ( f"Note: Data covers {num_months_tx} month(s). " f"Monthly averages calculated on a 3-month basis." ) _note_font = openpyxl.styles.Font(italic=True, size=9, color="595959") _note_fill = None ws_txsum.append([_note_text]) _note_row = ws_txsum.max_row ws_txsum.cell(row=_note_row, column=1).font = _note_font if _note_fill: ws_txsum.cell(row=_note_row, column=1).fill = _note_fill ws_txsum.merge_cells(start_row=_note_row, start_column=1, end_row=_note_row, end_column=2) ws_txsum.append(["Avg Transactions per Month", round(total_count_all / divisor_tx, 1)]) ws_txsum.append(["Avg Monthly Debit", round(total_debit_all / divisor_tx, 2)]) ws_txsum.append(["Avg Monthly Credit", round(total_credit_all / divisor_tx, 2)]) ws_txsum.append(["Avg Monthly Net (Credit - Debit)", round((total_credit_all - total_debit_all) / divisor_tx, 2)]) ws_txsum.append([]) ws_txsum.append([]) _txsum_section(ws_txsum, "Month-by-Month Breakdown", ncols=5) _txsum_header(ws_txsum, ["Month", "Transaction Count", "Total Debit", "Total Credit", "Net (Credit - Debit)"]) import calendar as _cal2 for ym in sorted(tx_month_data.keys()): year, month = ym label = "No Date" if year == 0 else f"{_cal2.month_name[month]} {year}" mdat = tx_month_data[ym] ws_txsum.append([label, mdat["count"], round(mdat["debit"], 2), round(mdat["credit"], 2), round(mdat["credit"] - mdat["debit"], 2)]) ws_txsum.append([]) ws_txsum.append(["Monthly Average", round(total_count_all / divisor_tx, 1), round(total_debit_all / divisor_tx, 2), round(total_credit_all / divisor_tx, 2), round((total_credit_all - total_debit_all) / divisor_tx, 2)]) r = ws_txsum.max_row for c in range(1, 6): ws_txsum.cell(row=r, column=c).font = tx_avg_font ws_txsum.cell(row=r, column=c).fill = tx_avg_fill ws_txsum.column_dimensions['A'].width = 30 ws_txsum.column_dimensions['B'].width = 22 ws_txsum.column_dimensions['C'].width = 18 ws_txsum.column_dimensions['D'].width = 18 ws_txsum.column_dimensions['E'].width = 22 if additional_expense_transactions: ws_ae = wb.create_sheet(title="Additional Expenses") ws_ae.append([ "Date", "Description", "Debit", "Credit", "Balance", "Category", "Bank Statement", "Status", "Issues", "⚙ Classification Method", "⚙ Confidence", "⚙ Reason / Rule", ]) for i, cell in enumerate(ws_ae[1]): if i < 9: cell.font = tx_hdr_font cell.fill = tx_hdr_fill else: cell.font = temp_hdr_font cell.fill = temp_hdr_fill for tx in additional_expense_transactions: method, conf_label, reason = _get_temp_cols(tx) ws_ae.append([ tx.get("date"), tx.get("description"), tx.get("debit"), tx.get("credit"), tx.get("balance"), tx.get("category", ""), _get_source_file_name(stmt, tx), tx.get("status"), ", ".join(tx.get("auditFlags", [])) if tx.get("auditFlags") else "", method, conf_label, reason, ]) for row in ws_ae.iter_rows(min_row=2, max_row=ws_ae.max_row, min_col=10, max_col=12): for cell in row: cell.font = openpyxl.styles.Font(size=11) ws_ae.column_dimensions['A'].width = 15 ws_ae.column_dimensions['B'].width = 40 ws_ae.column_dimensions['C'].width = 12 ws_ae.column_dimensions['D'].width = 12 ws_ae.column_dimensions['E'].width = 12 ws_ae.column_dimensions['F'].width = 20 ws_ae.column_dimensions['G'].width = 30 ws_ae.column_dimensions['H'].width = 12 ws_ae.column_dimensions['I'].width = 30 ws_ae.column_dimensions['J'].width = 22 ws_ae.column_dimensions['K'].width = 13 ws_ae.column_dimensions['L'].width = 55 ws_ae.freeze_panes = "A2" for row in ws_ae.iter_rows(min_row=2, max_row=ws_ae.max_row, min_col=1, max_col=12): for cell in row: cell.alignment = openpyxl.styles.Alignment(wrap_text=True, vertical="top") output = io.BytesIO() wb.save(output) output.seek(0) safe_name = stmt.file_name.replace('.pdf', '').replace('"', '') + '.xlsx' return StreamingResponse( output, media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", headers={"Content-Disposition": f'attachment; filename="{safe_name}"'} ) elif format == "xlsx_report": try: from bank_classifier.rules.rule_schema import CATEGORY_TO_SHEET, EXPENSE_SHEETS except ImportError: CATEGORY_TO_SHEET = {} EXPENSE_SHEETS = [] wb = openpyxl.Workbook() header_font = openpyxl.styles.Font(bold=True, size=11) header_fill = openpyxl.styles.PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid") header_font_white = openpyxl.styles.Font(bold=True, size=11, color="FFFFFF") ws_sum = wb.active ws_sum.title = "Summary" section_heading_font = openpyxl.styles.Font(bold=True, size=13, color="FFFFFF") section_heading_fill = openpyxl.styles.PatternFill(start_color="1F3864", end_color="1F3864", fill_type="solid") month_heading_font = openpyxl.styles.Font(bold=True, size=12, color="FFFFFF") month_heading_fill = openpyxl.styles.PatternFill(start_color="2F5496", end_color="2F5496", fill_type="solid") def _write_sum_header(ws, row_values): ws.append(row_values) r = ws.max_row for c in range(1, len(row_values) + 1): ws.cell(row=r, column=c).font = header_font_white ws.cell(row=r, column=c).fill = header_fill def _write_section_heading(ws, text, ncols=3): ws.append([text]) r = ws.max_row ws.cell(row=r, column=1).font = section_heading_font ws.cell(row=r, column=1).fill = section_heading_fill ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=ncols) def _write_month_heading(ws, text, ncols=3): ws.append([text]) r = ws.max_row ws.cell(row=r, column=1).font = month_heading_font ws.cell(row=r, column=1).fill = month_heading_fill ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=ncols) expense_transactions = [tx for tx in transactions if not _is_credit_tx(tx)] credit_transactions = [tx for tx in transactions if _is_credit_tx(tx)] cat_totals: Dict[str, Dict[str, Any]] = {} for tx in expense_transactions: cat = tx.get("category") or "" if cat not in cat_totals: cat_totals[cat] = {"count": 0, "debit": 0.0, "credit": 0.0} cat_totals[cat]["count"] += 1 debit = float(tx.get("debit") or 0) credit = float(tx.get("credit") or 0) cat_totals[cat]["debit"] += debit cat_totals[cat]["credit"] += credit import calendar as _calendar month_cat_data: Dict[tuple, Dict[str, Dict[str, Any]]] = {} for tx in expense_transactions: d_raw = tx.get("date") ym = None if d_raw: try: d = datetime.fromisoformat(str(d_raw).split("T")[0]).date() ym = (d.year, d.month) except Exception: pass if ym is None: ym = (0, 0) cat = tx.get("category") or "" if ym not in month_cat_data: month_cat_data[ym] = {} if cat not in month_cat_data[ym]: month_cat_data[ym][cat] = {"count": 0, "debit": 0.0, "credit": 0.0} month_cat_data[ym][cat]["count"] += 1 debit = float(tx.get("debit") or 0) credit = float(tx.get("credit") or 0) month_cat_data[ym][cat]["debit"] += debit month_cat_data[ym][cat]["credit"] += credit real_months = sorted(ym for ym in month_cat_data if ym != (0, 0)) num_months = max(len(real_months), 1) divisor = 3 _credit_note = ( " All credit/income transactions have been separated into the " "'Credit Transactions' sheet and are not included in any averages or totals here." ) if num_months < 3: _ann_text = ( f"⚠ Note: Only {num_months} month(s) of data available. " f"Monthly averages assume a 3-month window (missing months treated as $0)." + _credit_note ) _ann_font = openpyxl.styles.Font(italic=True, size=9, bold=True, color="7D6608") _ann_fill = openpyxl.styles.PatternFill(start_color="FFF2CC", end_color="FFF2CC", fill_type="solid") else: _ann_text = ( f"Data covers {num_months} month(s). Monthly averages calculated on a 3-month basis." + _credit_note ) _ann_font = openpyxl.styles.Font(italic=True, size=9, color="595959") _ann_fill = None _ann_cell = ws_sum.cell(row=1, column=1, value=_ann_text) _ann_cell.font = _ann_font _ann_cell.alignment = openpyxl.styles.Alignment(wrap_text=True, vertical="center") if _ann_fill: _ann_cell.fill = _ann_fill ws_sum.merge_cells(start_row=1, start_column=1, end_row=1, end_column=7) ws_sum.row_dimensions[1].height = 30 ws_sum.cell(row=2, column=1, value="Combined Category Summary").font = section_heading_font ws_sum.cell(row=2, column=1).fill = section_heading_fill ws_sum.merge_cells(start_row=2, start_column=1, end_row=2, end_column=3) heading_text = f"Monthly Average per Category (3-month basis · {num_months} month(s) of data)" ws_sum.cell(row=2, column=5, value=heading_text).font = section_heading_font ws_sum.cell(row=2, column=5).fill = section_heading_fill ws_sum.merge_cells(start_row=2, start_column=5, end_row=2, end_column=7) headers_left = ["Category", "Transaction Count", "Total Debit"] for idx, h in enumerate(headers_left): cell = ws_sum.cell(row=3, column=1 + idx, value=h) cell.font = header_font_white cell.fill = header_fill headers_right = ["Category", "Avg Monthly Transactions", "Avg Monthly Debit"] for idx, h in enumerate(headers_right): cell = ws_sum.cell(row=3, column=5 + idx, value=h) cell.font = header_font_white cell.fill = header_fill current_row = 4 for cat, data in sorted(cat_totals.items()): ws_sum.cell(row=current_row, column=1, value=cat) ws_sum.cell(row=current_row, column=2, value=data["count"]) ws_sum.cell(row=current_row, column=3, value=round(data["debit"], 2)) ws_sum.cell(row=current_row, column=5, value=cat) ws_sum.cell(row=current_row, column=6, value=round(data["count"] / divisor, 1)) ws_sum.cell(row=current_row, column=7, value=round(data["debit"] / divisor, 2)) current_row += 1 current_row += 2 def _write_section_heading_coords(ws, text, start_row, ncols=5): cell = ws.cell(row=start_row, column=1, value=text) cell.font = section_heading_font cell.fill = section_heading_fill ws.merge_cells(start_row=start_row, start_column=1, end_row=start_row, end_column=ncols) return start_row + 1 def _write_month_heading_coords(ws, text, start_row, ncols=5): cell = ws.cell(row=start_row, column=1, value=text) cell.font = month_heading_font cell.fill = month_heading_fill ws.merge_cells(start_row=start_row, start_column=1, end_row=start_row, end_column=ncols) return start_row + 1 def _write_sum_header_coords(ws, row_values, start_row): for idx, val in enumerate(row_values): cell = ws.cell(row=start_row, column=idx + 1, value=val) cell.font = header_font_white cell.fill = header_fill return start_row + 1 current_row = _write_section_heading_coords(ws_sum, "Monthly Breakdown", current_row, ncols=3) for ym in sorted(month_cat_data.keys()): year, month = ym month_label = "No Date" if year == 0 else f"{_calendar.month_name[month]} {year}" current_row = _write_month_heading_coords(ws_sum, month_label, current_row, ncols=3) current_row = _write_sum_header_coords(ws_sum, ["Category", "Transaction Count", "Total Debit"], current_row) for cat, mdata in sorted(month_cat_data[ym].items()): ws_sum.cell(row=current_row, column=1, value=cat) ws_sum.cell(row=current_row, column=2, value=mdata["count"]) ws_sum.cell(row=current_row, column=3, value=round(mdata["debit"], 2)) current_row += 1 current_row += 1 src_cat_data: dict[str, dict[str, dict]] = {} for tx in expense_transactions: src = _get_source_file_name(stmt, tx) cat = tx.get("category") or "" if src not in src_cat_data: src_cat_data[src] = {} if cat not in src_cat_data[src]: src_cat_data[src][cat] = {"count": 0, "debit": 0.0, "credit": 0.0} src_cat_data[src][cat]["count"] += 1 src_cat_data[src][cat]["debit"] += float(tx.get("debit") or 0) src_cat_data[src][cat]["credit"] += float(tx.get("credit") or 0) unique_sources_report: list[str] = [] seen_sources_report: set[str] = set() for tx in expense_transactions: src = _get_source_file_name(stmt, tx) if src not in seen_sources_report: unique_sources_report.append(src) seen_sources_report.add(src) current_row += 2 for src in unique_sources_report: current_row = _write_section_heading_coords( ws_sum, f"Bank Statement: {src} — Category Summary", current_row, ncols=11 ) ws_sum.cell(row=current_row, column=1, value="Category Summary").font = section_heading_font ws_sum.cell(row=current_row, column=1).fill = openpyxl.styles.PatternFill(start_color="2F5496", end_color="2F5496", fill_type="solid") ws_sum.merge_cells(start_row=current_row, start_column=1, end_row=current_row, end_column=3) ws_sum.cell(row=current_row, column=5, value="Monthly Averages").font = section_heading_font ws_sum.cell(row=current_row, column=5).fill = openpyxl.styles.PatternFill(start_color="2F5496", end_color="2F5496", fill_type="solid") ws_sum.merge_cells(start_row=current_row, start_column=5, end_row=current_row, end_column=7) current_row += 1 left_headers = ["Category", "Transaction Count", "Total Debit"] for idx, h in enumerate(left_headers): cell = ws_sum.cell(row=current_row, column=1 + idx, value=h) cell.font = header_font_white cell.fill = header_fill right_headers = ["Category", "Avg Monthly Transactions", "Avg Monthly Debit"] for idx, h in enumerate(right_headers): cell = ws_sum.cell(row=current_row, column=5 + idx, value=h) cell.font = header_font_white cell.fill = header_fill current_row += 1 src_cats = src_cat_data.get(src, {}) for cat, data in sorted(src_cats.items()): ws_sum.cell(row=current_row, column=1, value=cat) ws_sum.cell(row=current_row, column=2, value=data["count"]) ws_sum.cell(row=current_row, column=3, value=round(data["debit"], 2)) ws_sum.cell(row=current_row, column=5, value=cat) ws_sum.cell(row=current_row, column=6, value=round(data["count"] / divisor, 1)) ws_sum.cell(row=current_row, column=7, value=round(data["debit"] / divisor, 2)) current_row += 1 current_row += 2 ws_sum.column_dimensions['A'].width = 35 ws_sum.column_dimensions['B'].width = 22 ws_sum.column_dimensions['C'].width = 18 ws_sum.column_dimensions['D'].width = 3 ws_sum.column_dimensions['E'].width = 35 ws_sum.column_dimensions['F'].width = 22 ws_sum.column_dimensions['G'].width = 18 sheet_names = [s for s in EXPENSE_SHEETS if s != "Miscellaneous"] + ["Additional Expenses"] sheet_headers = [ "Date", "Description", "Category", "Sub-category", "Type", "Amount", "Bank Statement", "⚙ Classification Method", "⚙ Confidence", "⚙ Reason / Rule", ] # ── Temp/review column styles for xlsx_report ──────────────────── rpt_temp_hdr_fill = openpyxl.styles.PatternFill(start_color="ED7D31", end_color="ED7D31", fill_type="solid") rpt_temp_hdr_font = openpyxl.styles.Font(bold=True, color="FFFFFF", size=11) def _rpt_temp_fill(method: str): return None # no fill — white cells matching rest of sheet def _rpt_get_temp_cols(tx: dict): src = (tx.get("categorySource") or tx.get("category_source") or "").strip() reason = tx.get("classification_reason") or tx.get("llm_reasoning") or "—" src_lower = src.lower() if src_lower.startswith("rule"): method, conf_label = "Rule", "100%" if reason == "—" and ":" in src: reason = src.split(":", 1)[1] elif src_lower.startswith("ml") or src_lower.startswith("llm"): method = "AI Suggestion" if ":" in src: try: conf_label = f"{float(src.split(':', 1)[1]):.0%}" except ValueError: conf_label = "—" else: conf = tx.get("categoryConfidence") or tx.get("category_confidence") conf_label = f"{float(conf):.0%}" if conf is not None else "—" elif src_lower == "manual": method, conf_label = "Manual", "100%" elif src_lower.startswith("fallback"): method = "Fallback" conf = tx.get("categoryConfidence") or tx.get("category_confidence") conf_label = f"{float(conf):.0%}" if conf is not None else "—" else: method = src if src else "—" conf = tx.get("categoryConfidence") or tx.get("category_confidence") conf_label = f"{float(conf):.0%}" if conf is not None else "—" return method, conf_label, reason or "—" def sanitize_sheet_title(name: str) -> str: for char in ['\\\\', '/', '?', '*', ':', '[', ']']: name = name.replace(char, ' ') return " ".join(name.split())[:31] active_sheet_names = set() for tx in expense_transactions: cat = tx.get("category") or "" if cat in sheet_names: active_sheet_names.add(cat) sheets: Dict[str, Any] = {} for sn in sheet_names: if sn not in active_sheet_names: continue ws_title = sanitize_sheet_title(sn) ws = wb.create_sheet(title=ws_title) ws.append(sheet_headers) for i, cell in enumerate(ws[1]): if i < 7: cell.font = header_font_white cell.fill = header_fill else: cell.font = rpt_temp_hdr_font cell.fill = rpt_temp_hdr_fill sheets[sn] = ws for tx in expense_transactions: cat = tx.get("category") or "" sub_cat = tx.get("sub_category") or "" debit = float(tx.get("debit") or 0) credit = float(tx.get("credit") or 0) amount = debit if debit else credit tx_type = "Debit" if debit else ("Credit" if credit else "") method, conf_label, reason = _rpt_get_temp_cols(tx) if cat in sheets: ws = sheets[cat] ws.append([ tx.get("date"), tx.get("description"), cat, sub_cat, tx_type, amount, _get_source_file_name(stmt, tx), method, conf_label, reason, ]) for ws in sheets.values(): ws.column_dimensions['A'].width = 15 ws.column_dimensions['B'].width = 40 ws.column_dimensions['C'].width = 25 ws.column_dimensions['D'].width = 25 ws.column_dimensions['E'].width = 12 ws.column_dimensions['F'].width = 15 ws.column_dimensions['G'].width = 30 ws.column_dimensions['H'].width = 22 ws.column_dimensions['I'].width = 13 ws.column_dimensions['J'].width = 55 for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=1, max_col=10): for i, cell in enumerate(row): cell.alignment = openpyxl.styles.Alignment(wrap_text=True, vertical="top") if credit_transactions: from collections import defaultdict as _defaultdict ws_credit = wb.create_sheet(title="Credit Transactions") ws_credit.sheet_view.showGridLines = False credit_banner_fill = openpyxl.styles.PatternFill(start_color="1F3864", end_color="1F3864", fill_type="solid") credit_banner_font = openpyxl.styles.Font(bold=True, size=12, color="FFFFFF") credit_hdr_fill = openpyxl.styles.PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid") credit_hdr_font = openpyxl.styles.Font(bold=True, size=11, color="FFFFFF") credit_amt_fmt = "#,##0.00" credit_by_cat: dict = _defaultdict(list) for tx in credit_transactions: cat = (tx.get("category") or "Uncategorised").strip() credit_by_cat[cat].append(tx) cr_row = 1 for cat_name in sorted(credit_by_cat.keys()): cat_txs = sorted( credit_by_cat[cat_name], key=lambda t: str(t.get("date") or "") ) ws_credit.merge_cells( start_row=cr_row, start_column=1, end_row=cr_row, end_column=5 ) banner_cell = ws_credit.cell(row=cr_row, column=1, value=cat_name) banner_cell.font = credit_banner_font banner_cell.fill = credit_banner_fill banner_cell.alignment = openpyxl.styles.Alignment(horizontal="center", vertical="center") ws_credit.row_dimensions[cr_row].height = 22 cr_row += 1 col_headers = ["Date", "Description", "Category", "Amount", "Bank Statement"] for ci, h in enumerate(col_headers, 1): c = ws_credit.cell(row=cr_row, column=ci, value=h) c.font = credit_hdr_font c.fill = credit_hdr_fill c.alignment = openpyxl.styles.Alignment(horizontal="center", vertical="center") ws_credit.row_dimensions[cr_row].height = 18 cr_row += 1 for tx in cat_txs: credit_amt = float(tx.get("credit") or 0) row_vals = [ tx.get("date"), tx.get("description"), tx.get("category") or "", credit_amt, _get_source_file_name(stmt, tx), ] for ci, val in enumerate(row_vals, 1): c = ws_credit.cell(row=cr_row, column=ci, value=val) c.font = openpyxl.styles.Font(size=9) c.alignment = openpyxl.styles.Alignment(vertical="top", wrap_text=(ci == 2)) if ci == 4: c.number_format = credit_amt_fmt ws_credit.row_dimensions[cr_row].height = 14 cr_row += 1 cr_row += 1 ws_credit.column_dimensions['A'].width = 15 ws_credit.column_dimensions['B'].width = 40 ws_credit.column_dimensions['C'].width = 30 ws_credit.column_dimensions['D'].width = 15 ws_credit.column_dimensions['E'].width = 35 misc_expense_txs = [ tx for tx in expense_transactions if (tx.get("category") or "").strip() == "Miscellaneous" ] if misc_expense_txs: ws_misc = wb.create_sheet(title="Miscellaneous") ws_misc.append(sheet_headers) for i, cell in enumerate(ws_misc[1]): if i < 7: cell.font = header_font_white cell.fill = header_fill else: cell.font = rpt_temp_hdr_font cell.fill = rpt_temp_hdr_fill for tx in misc_expense_txs: debit = float(tx.get("debit") or 0) credit = float(tx.get("credit") or 0) amount = debit if debit else credit tx_type = "Debit" if debit else ("Credit" if credit else "") method, conf_label, reason = _rpt_get_temp_cols(tx) ws_misc.append([ tx.get("date"), tx.get("description"), tx.get("category") or "", tx.get("sub_category") or "", tx_type, amount, _get_source_file_name(stmt, tx), method, conf_label, reason, ]) ws_misc.column_dimensions['A'].width = 15 ws_misc.column_dimensions['B'].width = 40 ws_misc.column_dimensions['C'].width = 25 ws_misc.column_dimensions['D'].width = 20 ws_misc.column_dimensions['E'].width = 10 ws_misc.column_dimensions['F'].width = 15 ws_misc.column_dimensions['G'].width = 35 ws_misc.column_dimensions['H'].width = 22 ws_misc.column_dimensions['I'].width = 13 ws_misc.column_dimensions['J'].width = 55 ws_misc.freeze_panes = "A2" for row in ws_misc.iter_rows(min_row=2, max_row=ws_misc.max_row, min_col=1, max_col=10): for cell in row: cell.alignment = openpyxl.styles.Alignment(wrap_text=True, vertical="top") output = io.BytesIO() wb.save(output) output.seek(0) safe_name = stmt.file_name.replace('.pdf', '').replace('"', '') + '_expense_report.xlsx' return StreamingResponse( output, media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", headers={"Content-Disposition": f'attachment; filename="{safe_name}"'} ) elif format == "pdf_audit": doc = fitz.open() def start_page(): p = doc.new_page() p.insert_text((50, 40), f"BEFREE AUDIT REPORT - {stmt.file_name}", fontsize=8, color=(0.5, 0.5, 0.5)) p.draw_line((50, 45), (550, 45), color=(0.8, 0.8, 0.8), width=0.5) return p, 70 page, y = start_page() page.insert_text((50, y), "Audit Summary", fontsize=18, fontname="hebo") y += 30 summary_data = [ ("Statement ID", statement_id), ("Filename", stmt.file_name), ("Date Processed", datetime.now().strftime("%Y-%m-%d %H:%M:%S")), ("Total Transactions", str(len(transactions))), ("Overall Accuracy", f"{stmt.accuracy or 0:.2f}%") ] for label, value in summary_data: page.insert_text((50, y), label + ":", fontsize=10, fontname="hebo") page.insert_text((180, y), str(value), fontsize=10) y += 15 y += 30 page.insert_text((50, y), "Transaction Detail", fontsize=14, fontname="hebo") y += 20 headers = ["Date", "Description", "Amount", "Balance", "Status"] x_offsets = [50, 110, 320, 400, 480] for i, h in enumerate(headers): page.insert_text((x_offsets[i], y), h, fontsize=9, fontname="hebo") y += 10 page.draw_line((50, y), (550, y), width=1) y += 15 for tx in transactions: if y > 780: page, y = start_page() for i, h in enumerate(headers): page.insert_text((x_offsets[i], y), h, fontsize=9, fontname="hebo") y += 10 page.draw_line((50, y), (550, y), width=0.5) y += 15 page.insert_text((x_offsets[0], y), str(tx.get("date") or ""), fontsize=8) desc = str(tx.get("description") or "") if len(desc) > 55: desc = desc[:52] + "..." page.insert_text((x_offsets[1], y), desc, fontsize=8) amt = float(tx.get("credit") or 0) - float(tx.get("debit") or 0) page.insert_text((x_offsets[2], y), f"{amt:,.2f}", fontsize=8) bal = tx.get("balance") page.insert_text((x_offsets[3], y), f"{float(bal):,.2f}" if (bal is not None and bal != "") else "-", fontsize=8) status = tx.get("status") or "uploaded" color = (0, 0.5, 0) if status in ["verified", "corrected"] else (0.8, 0, 0) page.insert_text((x_offsets[4], y), status.upper(), fontsize=7, fontname="hebo", color=color) y += 12 pdf_bytes = doc.tobytes() doc.close() safe_name = stmt.file_name.replace('.pdf', '').replace('"', '') + '_audit.pdf' return StreamingResponse( io.BytesIO(pdf_bytes), media_type="application/pdf", headers={"Content-Disposition": f'attachment; filename="{safe_name}"'} ) else: raise HTTPException(status_code=400, detail="Unsupported format") except Exception as e: logger.error(f"Download failed for {statement_id} (format={format}): {e}", exc_info=True) raise HTTPException(status_code=500, detail="Export generation failed")
import os
import io
import csv
import logging
import openpyxl
import fitz # PyMuPDF
from datetime import datetime
from typing import Optional, List, Dict, Any
from fastapi import APIRouter, Depends, HTTPException, Request
from fastapi.responses import StreamingResponse
from auth.models import User
from auth.service import get_current_user
from models.export_models import ExportRequest, ExportResponse, ExportSummary
from core.state import statements, settings
from core.helpers import _get_source_file_name, _filter_transactions_by_last_n_months, _tx_sort_key
from core.statement_io import _persist_swap_to_xlsx
logger = logging.getLogger("befree.export")
router = APIRouter(prefix="/api/v1/statements", tags=["export"])
@router.get("/{statement_id}/export/summary", response_model=ExportSummary)
async def get_export_summary(statement_id: str, current_user: User = Depends(get_current_user)):
"""Get export summary."""
if statement_id not in statements:
raise HTTPException(status_code=404, detail="Statement not found")
stmt = statements[statement_id]
use_3_months = getattr(stmt, "extract_last_3_months", None)
if use_3_months is None:
use_3_months = settings.extractLast3Months
raw_txs = stmt.transactions
if use_3_months:
raw_txs = _filter_transactions_by_last_n_months(raw_txs)
unverified_count = 0
for tx in raw_txs:
src = tx.get('categorySource') or tx.get('category_source') or ''
verified = bool(tx.get('verified', False))
if (src.startswith('ml') or src.startswith('llm')) and not verified:
unverified_count += 1
import calendar as _cal_export
tx_months: set = set()
for tx in raw_txs:
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
months_available = len(tx_months)
if tx_months:
sorted_months = sorted(tx_months)
start_label = f"{_cal_export.month_name[sorted_months[0][1]]} {sorted_months[0][0]}"
end_label = f"{_cal_export.month_name[sorted_months[-1][1]]} {sorted_months[-1][0]}"
month_range_label = start_label if sorted_months[0] == sorted_months[-1] else f"{start_label} – {end_label}"
else:
month_range_label = ""
return ExportSummary(
statementId=statement_id,
fileName=stmt.file_name,
totalRows=len(raw_txs),
correctedRows=0,
deletedRows=0,
accuracy=stmt.accuracy or 0,
exportedAt=None,
unverifiedCount=unverified_count,
monthsAvailable=months_available,
monthsUsedForAverage=3,
monthRangeLabel=month_range_label,
)
@router.post("/{statement_id}/export", response_model=ExportResponse)
async def export_statement(statement_id: str, payload: ExportRequest, request: Request, current_user: User = Depends(get_current_user)):
"""Export statement."""
if statement_id not in statements:
raise HTTPException(status_code=404, detail="Statement not found")
stmt = statements[statement_id]
download_url = f"{request.base_url}api/v1/statements/{statement_id}/download?format={payload.format}"
auth_header = request.headers.get("authorization")
if auth_header and auth_header.lower().startswith("bearer "):
token = auth_header[7:]
download_url += f"&token={token}"
return ExportResponse(
downloadUrl=download_url,
fileName=f"{stmt.file_name.replace('.pdf', '')}.{payload.format}",
)