# Phase Recon-2 — Python Report Generator (CloudI Service) **Status:** ⬜ Pending **Duration:** Weeks 4–5 **Depends on:** Phase Recon-1 **Goal:** `recon_report_py` CloudI service accepts match results and config, writes a formatted Excel workbook, and returns it as base64. --- ## Service Structure ``` services/recon_report_py/ ├── main.py # CloudI entry point ├── requirements.txt # openpyxl, cloudi ├── Dockerfile ├── docker-compose.yml ├── writer.py # Matched + unmatched sheet writer ├── summary.py # Location-grouped summary sheet writer └── sheet_names.py # Naming convention per recon type ``` --- ## Sheet Naming Convention (`sheet_names.py`) ```python SHEET_NAMES = { "bank_card_vs_momentspay": ("M-BankCard-Matched", "M-BankCard-Unmatched"), "bank_upi_vs_momentspay": ("M-BankUpi-Matched", "M-BankUpi-Unmatched"), "his_bank_card": ("M-HISBankcard-Matched", "M-HISBankcard-Unmatched"), "his_bank_upi": ("M-HISBankUpi-Matched", "M-HISBankUpi-Unmatched"), "his_momentspay_card": ("M-HISMPCard-Matched", "M-HISMPCard-Unmatched"), "his_momentspay_upi": ("M-HISMPUpi-Matched", "M-HISMPUpi-Unmatched"), "amex": ("M-AMEX-Matched", "M-AMEX-Unmatched"), } def get_sheet_names(recon_type: str) -> tuple: return SHEET_NAMES.get(recon_type, ("Matched", "Unmatched")) ``` --- ## Report Writer (`writer.py`) ```python """ writer.py — Writes matched and unmatched sheets into a new workbook. Applies blue header formatting consistent with existing reports. """ import io import pandas as pd import openpyxl from openpyxl import Workbook from openpyxl.styles import PatternFill, Font from openpyxl.utils.dataframe import dataframe_to_rows HEADER_FILL = PatternFill(fgColor='1274bd', fill_type='solid') HEADER_FONT = Font(color='FFFFFF', bold=True) ALT_ROW_FILL = PatternFill(fgColor='EBF3FB', fill_type='solid') def write_workbook( matched_rows: list, unmatched_rows: list, summary_data: dict, recon_type: str, location_map: dict ) -> bytes: """ Build and return a complete XLSX workbook as bytes. Sheets: 1. 2. 3. Summary """ from sheet_names import get_sheet_names from summary import write_summary_sheet matched_name, unmatched_name = get_sheet_names(recon_type) wb = Workbook() wb.remove(wb.active) # Remove default empty sheet _write_data_sheet(wb, matched_name, matched_rows, "YES") _write_data_sheet(wb, unmatched_name, unmatched_rows, "NO") write_summary_sheet(wb, summary_data, location_map, recon_type) buf = io.BytesIO() wb.save(buf) return buf.getvalue() def _write_data_sheet(wb: Workbook, sheet_name: str, rows: list, status: str): if not rows: ws = wb.create_sheet(sheet_name) ws.append(["No records"]) return df = pd.DataFrame(rows) df.insert(0, 'S.No.', range(1, len(df) + 1)) df['momentpay matched'] = status ws = wb.create_sheet(sheet_name) # Header row headers = list(df.columns) ws.append(headers) for cell in ws[1]: cell.fill = HEADER_FILL cell.font = HEADER_FONT # Data rows with alternating fill for i, row in enumerate(df.itertuples(index=False), start=2): ws.append(list(row)) if i % 2 == 0: for cell in ws[i]: cell.fill = ALT_ROW_FILL # Auto-size columns for col in ws.columns: max_len = max((len(str(cell.value or '')) for cell in col), default=10) ws.column_dimensions[col[0].column_letter].width = min(max_len + 2, 50) ``` --- ## Summary Sheet Writer (`summary.py`) ```python """ summary.py — Writes the Summary sheet. Summary sheet contains: - Overall totals: count + amount for matched and unmatched - Per-location breakdown: each Unit/Location row with total_count, total_amount, matched_count, matched_amount, unmatched_count, unmatched_amount """ import openpyxl from openpyxl.styles import PatternFill, Font, Alignment from openpyxl import Workbook HEADER_FILL = PatternFill(fgColor='1274bd', fill_type='solid') HEADER_FONT = Font(color='FFFFFF', bold=True) def write_summary_sheet(wb: Workbook, summary_data: dict, location_map: dict, recon_type: str): """ summary_data expected structure (from engine response): { "total": 120, "matched": 110, "unmatched": 10, "matched_amount": "135420.00", "unmatched_amount": "1230.00", "by_location": { "BBW": {"total": 20, "matched": 18, "unmatched": 2, "total_amount": 24000.0, "matched_amount": 21600.0, "unmatched_amount": 2400.0}, ... } } """ ws = wb.create_sheet("Summary") # ── Header ────────────────────────────────────────────────────────── headers = ['Location', 'Total Txn', 'Total Amount (₹)', 'Matched Txn', 'Matched Amount (₹)', 'Unmatched Txn', 'Unmatched Amount (₹)'] ws.append(headers) for cell in ws[1]: cell.fill = HEADER_FILL cell.font = HEADER_FONT cell.alignment = Alignment(horizontal='center') # ── Overall totals row ─────────────────────────────────────────────── ws.append([ 'TOTAL', summary_data.get('total', 0), float(summary_data.get('total_amount', 0)), summary_data.get('matched', 0), float(summary_data.get('matched_amount', 0)), summary_data.get('unmatched', 0), float(summary_data.get('unmatched_amount', 0)), ]) for cell in ws[2]: cell.font = Font(bold=True) # ── Per-location rows ──────────────────────────────────────────────── by_location = summary_data.get('by_location', {}) for loc, data in sorted(by_location.items()): ws.append([ loc, data.get('total', 0), float(data.get('total_amount', 0)), data.get('matched', 0), float(data.get('matched_amount', 0)), data.get('unmatched', 0), float(data.get('unmatched_amount', 0)), ]) # Number format for amount columns for row in ws.iter_rows(min_row=2): for cell in [row[2], row[4], row[6]]: cell.number_format = '#,##0.00' for cell in [row[1], row[3], row[5]]: cell.alignment = Alignment(horizontal='center') # Column widths ws.column_dimensions['A'].width = 35 for col_letter in ['B', 'C', 'D', 'E', 'F', 'G']: ws.column_dimensions[col_letter].width = 20 ``` --- ## CloudI Service Entry (`main.py`) ```python """ recon_report_py — Report generation CloudI service. CloudI path: /+/recon/report/ Request payload: { "recon_type": "bank_card_vs_momentspay", "matched": [ {...row...}, ... ], "unmatched": [ {...row...}, ... ], "summary": { ...summary_data... }, "location_map": { "full name": "code", ... } } Response: { "status": "ok", "report_base64": "" } """ import sys, json, logging, base64 sys.path.append('/usr/local/lib/cloudi-2.0.7/api/python/') from cloudi import API, terminate_exception from writer import write_workbook logger = logging.getLogger('recon_report_py') class ReconReportService: def __init__(self): self.__api = API(0) def run(self): try: self.__api.subscribe("+/recon/report/", self.__handle) logger.info("recon_report_py started") self.__api.poll() except terminate_exception: logger.info("recon_report_py terminated cleanly") def __handle(self, request_type, name, pattern, request_info, request, timeout, priority, trans_id, pid): try: payload = json.loads(request) recon_type = payload["recon_type"] matched = payload.get("matched", []) unmatched = payload.get("unmatched", []) summary = payload.get("summary", {}) location_map = payload.get("location_map", {}) xlsx_bytes = write_workbook(matched, unmatched, summary, recon_type, location_map) report_b64 = base64.b64encode(xlsx_bytes).decode('utf-8') return json.dumps({"status": "ok", "report_base64": report_b64}) except Exception as e: logger.error(f"Report generation failed: {e}", exc_info=True) return json.dumps({"status": "error", "reason": str(e)}) if __name__ == '__main__': assert API.thread_count() == 1 ReconReportService().run() ``` --- ## Elixir Client: `apps/mw_recon/lib/mw_recon/report_client.ex` ```elixir defmodule MwRecon.ReportClient do @moduledoc """ Calls recon_report_py CloudI service to generate the Excel report. Returns {:ok, base64_string} or {:error, reason}. """ alias AdapterCloudi.Dispatcher @service_path "/recon/report/" @timeout_ms 60_000 @spec generate(map()) :: {:ok, String.t()} | {:error, String.t()} def generate(payload) when is_map(payload) do case Dispatcher.call( "#{payload.tenant_id}#{@service_path}", Jason.encode!(payload), @timeout_ms ) do {:ok, response_json} -> case Jason.decode(response_json) do {:ok, %{"status" => "ok", "report_base64" => b64}} -> {:ok, b64} {:ok, %{"status" => "error", "reason" => r}} -> {:error, r} _ -> {:error, "Invalid response"} end {:error, reason} -> {:error, "CloudI call failed: #{inspect(reason)}"} end end @doc "Build report payload from engine match result + config." def build_payload(tenant_id, match_result, location_map) do %{ tenant_id: tenant_id, recon_type: match_result["recon_type"], matched: match_result["matched"], unmatched: match_result["unmatched"], summary: match_result["summary"], location_map: location_map } end end ``` --- ## `config/cloudi.conf` — Add Entry ```erlang %% recon_report_py {prefix, "/+/recon/report/"}, {file_path, "/usr/bin/python3"}, {args, "/app/services/recon_report_py/main.py"}, {count_process, 2}, {max_r, 5}, {max_t, 60}, {env, [{"PYTHONPATH", "/usr/local/lib/cloudi-2.0.7/api/python"}]} ``` --- ## `requirements.txt` ``` cloudi==2.0.7 pandas==2.2.2 openpyxl==3.1.2 ``` --- ## Acceptance Criteria - [ ] Calling `MwRecon.ReportClient.generate/1` in IEx returns `{:ok, ""}` with a valid XLSX - [ ] Decoded XLSX contains sheets: ``, ``, `Summary` - [ ] Matched sheet header row is blue `#1274bd` with white bold text - [ ] Summary sheet has correct per-location rows with count and formatted ₹ amounts - [ ] Sheet names follow the naming convention from `sheet_names.py` - [ ] Empty matched or unmatched list produces a sheet with "No records" (no crash)