""" Excel and CSV export utilities for the General Ledger. Supports rich openpyxl styling, freeze panes, formulas, auto column width, and falls back to zipped CSVs if openpyxl is not installed. """ import os import io import csv import zipfile import sqlite3 import tempfile from datetime import datetime from decimal import Decimal try: import openpyxl from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils import get_column_letter OPENPYXL_AVAILABLE = True except ImportError: OPENPYXL_AVAILABLE = False def get_posted_ledger_rows(db_path): """Retrieve all posted transaction rows from the database sorted by account and date.""" conn = sqlite3.connect(db_path) c = conn.cursor() c.execute(""" SELECT r.date, r.account_no, r.contra_account_no, r.amount, r.reference, (SELECT description FROM coa_rows WHERE account_no = r.account_no AND upload_id IN (SELECT upload_id FROM uploads WHERE type='COA' AND status='POSTED') LIMIT 1) as account_desc FROM entry_rows r WHERE r.upload_id IN (SELECT upload_id FROM uploads WHERE type='ENTRY' AND status='POSTED') ORDER BY r.account_no ASC, r.date ASC, r.row_id ASC """) rows = c.fetchall() # Get company name c.execute("SELECT name FROM company WHERE id=1") co_row = c.fetchone() company_name = co_row[0] if co_row else "Unnamed Company" conn.close() return rows, company_name def generate_general_ledger_excel(db_path, output_stream): """Generate a beautifully formatted General Ledger Excel spreadsheet.""" rows, company_name = get_posted_ledger_rows(db_path) wb = openpyxl.Workbook() ws = wb.active ws.title = "General Ledger" ws.views.sheetView[0].showGridLines = True # Fonts title_font = Font(name="Segoe UI", size=16, bold=True, color="1F497D") meta_font = Font(name="Segoe UI", size=10, italic=True, color="595959") header_font = Font(name="Segoe UI", size=11, bold=True, color="FFFFFF") data_font = Font(name="Segoe UI", size=10) total_font = Font(name="Segoe UI", size=11, bold=True) # Fills & Borders header_fill = PatternFill(start_color="1F497D", end_color="1F497D", fill_type="solid") total_fill = PatternFill(start_color="F2F2F2", end_color="F2F2F2", fill_type="solid") thin_side = Side(border_style="thin", color="D9D9D9") thick_bottom = Side(border_style="medium", color="1F497D") double_bottom = Side(border_style="double", color="000000") top_border = Side(border_style="thin", color="000000") data_border = Border(left=thin_side, right=thin_side, top=thin_side, bottom=thin_side) total_border = Border(top=top_border, bottom=double_bottom) # Title & Metadata Info ws["A1"] = company_name ws["A1"].font = title_font ws["A2"] = f"General Ledger — Generated on {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}" ws["A2"].font = meta_font headers = [ "Date", "Account No", "Account Description", "Contra Account", "Reference", "Debit", "Credit", "Net Amount", "Running Balance" ] # Write headers at row 4 header_row = 4 for col_idx, text in enumerate(headers, 1): cell = ws.cell(row=header_row, column=col_idx, value=text) cell.font = header_font cell.fill = header_fill cell.alignment = Alignment(horizontal="center" if col_idx in [1, 2, 4] else "left" if col_idx in [3, 5] else "right") cell.border = Border(bottom=thick_bottom) # Write transaction rows current_row = 5 prev_account = None running_balance = Decimal("0") for r in rows: r_date, r_acct, r_contra, r_amt_raw, r_ref, r_desc = r r_amt = Decimal(r_amt_raw) # Reset running balance on account change if r_acct != prev_account: prev_account = r_acct running_balance = Decimal("0") running_balance += r_amt debit = r_amt if r_amt > 0 else Decimal("0") credit = abs(r_amt) if r_amt < 0 else Decimal("0") row_data = [ r_date, r_acct, r_desc or "(no description)", r_contra or "", r_ref or "", float(debit) if debit else "", float(credit) if credit else "", float(r_amt), float(running_balance) ] for col_idx, val in enumerate(row_data, 1): cell = ws.cell(row=current_row, column=col_idx, value=val) cell.font = data_font cell.border = data_border # Alignments if col_idx in [1, 2, 4]: cell.alignment = Alignment(horizontal="center") elif col_idx in [3, 5]: cell.alignment = Alignment(horizontal="left") else: cell.alignment = Alignment(horizontal="right") # Number formats if col_idx in [6, 7, 8, 9] and val != "": cell.number_format = "$#,##0.00;($#,##0.00);\"-\"" current_row += 1 # Total row at bottom if current_row > 5: # Grand Total label cell_lbl = ws.cell(row=current_row, column=3, value="Grand Total") cell_lbl.font = total_font cell_lbl.alignment = Alignment(horizontal="right") # Write sum formulas for Debit, Credit, Net Amount # Columns F, G, H correspond to indexes 6, 7, 8 for col_idx, col_letter in [(6, "F"), (7, "G"), (8, "H")]: formula = f"=SUM({col_letter}5:{col_letter}{current_row-1})" cell = ws.cell(row=current_row, column=col_idx, value=formula) cell.font = total_font cell.border = total_border cell.fill = total_fill cell.alignment = Alignment(horizontal="right") cell.number_format = "$#,##0.00;($#,##0.00);\"-\"" # Draw double lines for non-formula cells in total row for col_idx in [1, 2, 4, 5, 9]: cell = ws.cell(row=current_row, column=col_idx) cell.border = total_border cell.fill = total_fill # Auto-adjust column width for col in ws.columns: max_len = 0 col_letter = get_column_letter(col[0].column) for cell in col: # Skip title row from width calculations if cell.row in [1, 2]: continue if cell.value: # Approximate formatting expansion val_str = str(cell.value) max_len = max(max_len, len(val_str)) ws.column_dimensions[col_letter].width = max(max_len + 3, 12) # Freeze Panes below headers ws.freeze_panes = "A5" wb.save(output_stream) def generate_general_ledger_csv_zip(db_path, output_stream): """Fallback generator to zip the general ledger into flat CSV file.""" rows, company_name = get_posted_ledger_rows(db_path) csv_buffer = io.StringIO() writer = csv.writer(csv_buffer) writer.writerow([ "Date", "Account No", "Account Description", "Contra Account", "Reference", "Debit", "Credit", "Net Amount", "Running Balance" ]) prev_account = None running_balance = Decimal("0") for r in rows: r_date, r_acct, r_contra, r_amt_raw, r_ref, r_desc = r r_amt = Decimal(r_amt_raw) if r_acct != prev_account: prev_account = r_acct running_balance = Decimal("0") running_balance += r_amt debit = r_amt if r_amt > 0 else Decimal("0") credit = abs(r_amt) if r_amt < 0 else Decimal("0") writer.writerow([ r_date, r_acct, r_desc or "", r_contra or "", r_ref or "", f"{debit:.2f}" if debit else "0.00", f"{credit:.2f}" if credit else "0.00", f"{r_amt:.2f}", f"{running_balance:.2f}" ]) with zipfile.ZipFile(output_stream, "w", zipfile.ZIP_DEFLATED) as z: z.writestr("general_ledger.csv", csv_buffer.getvalue()) def export_ledger(db_path, output_stream): """Main export dispatcher.""" if OPENPYXL_AVAILABLE: generate_general_ledger_excel(db_path, output_stream) return "xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" else: generate_general_ledger_csv_zip(db_path, output_stream) return "zip", "application/zip"