from dataclasses import dataclass from typing import Optional import openpyxl from openpyxl.styles import Alignment, Border, Font, PatternFill, Side from openpyxl.worksheet.worksheet import Worksheet from datatypes.magicplan.domain.models import Plan from backend.app.db.connection import db_read_session from backend.app.db.functions.magic_plan_functions import get_plan_by_uploaded_file_id UPLOADED_FILE_ID: int = 7843 _HEADER_FILL = PatternFill("solid", fgColor="D9D9D9") _FLOOR_FILL = PatternFill("solid", fgColor="BDD7EE") _BOLD = Font(bold=True) _TOTAL_COLS = 25 # A–Y _BLANK_SEPARATOR_COLS = {5, 21} _MEDIUM_RIGHT = Border(right=Side(style="medium")) _MEDIUM_LEFT = Border(left=Side(style="medium")) _MEDIUM_BOTTOM = Side(style="medium") _SECTION_BOUNDARY_COLS = {4, 11, 15, 20} _SECTION_START_COLS = {6, 22} _FLOOR_MERGE_SEGMENTS = [(1, 4), (6, 20), (22, 25)] @dataclass class AuditRow: floor_level: Optional[int] = None # set only on floor-separator rows room_name: Optional[str] = None room_width_m: Optional[float] = None room_length_m: Optional[float] = None room_area_m2: Optional[float] = None window_label: Optional[str] = None window_location: Optional[str] = None window_width_m: Optional[float] = None window_height_m: Optional[float] = None window_area_m2: Optional[float] = None window_opening_type: Optional[str] = None door_location: Optional[str] = None door_width_mm: Optional[float] = None is_last_room_row: bool = False opening_count: Optional[int] = None opening_width_m: Optional[float] = None opening_height_m: Optional[float] = None opening_total_area_m2: Optional[float] = None trickle_vent_blocked: Optional[bool] = None trickle_vent_pictured: Optional[bool] = None trickle_vent_effective_area: Optional[float] = None trickle_vent_count: Optional[int] = None trickle_vent_total_effective_area: Optional[float] = None def _build_rows(plan: Plan) -> list[AuditRow]: rows: list[AuditRow] = [] window_counter = 1 for floor in plan.floors: rows.append(AuditRow(floor_level=floor.level)) for room in floor.rooms: n_rows = max(1, len(room.windows), len(room.doors)) for i in range(n_rows): window = room.windows[i] if i < len(room.windows) else None door = room.doors[i] if i < len(room.doors) else None row = AuditRow( room_name=room.name if i == 0 else None, room_width_m=room.width_m if i == 0 else None, room_length_m=room.length_m if i == 0 else None, room_area_m2=room.area_m2 if i == 0 else None, ) if window is not None: row.window_label = f"W{window_counter}" row.window_location = room.name row.window_width_m = window.width_m row.window_height_m = window.height_m row.window_area_m2 = window.area_m2 row.window_opening_type = window.opening_type window_counter += 1 if door is not None: row.door_location = room.name row.door_width_mm = door.width_mm if i == n_rows - 1: row.is_last_room_row = True rows.append(row) return rows def _apply_section_borders(ws: Worksheet, row: int) -> None: for col in _SECTION_BOUNDARY_COLS: ws.cell(row=row, column=col).border = _MEDIUM_RIGHT for col in _SECTION_START_COLS: ws.cell(row=row, column=col).border = _MEDIUM_LEFT def _apply_room_bottom_border(ws: Worksheet, row: int) -> None: medium = Side(style="medium") for col in range(1, _TOTAL_COLS + 1): if col in _SECTION_BOUNDARY_COLS: ws.cell(row=row, column=col).border = Border(right=medium, bottom=medium) elif col in _SECTION_START_COLS: ws.cell(row=row, column=col).border = Border(left=medium, bottom=medium) else: ws.cell(row=row, column=col).border = Border(bottom=medium) def _write_headers(ws: Worksheet) -> None: # Row 1: group labels (merged) _merge_bold_fill(ws, 1, 1, 1, 4, "Room") # col 5: blank separator _merge_bold_fill(ws, 1, 6, 1, 11, "Window Area") _merge_bold_fill(ws, 1, 12, 1, 15, "Openings") _merge_bold_fill(ws, 1, 16, 1, 20, "Trickle Vents") # col 21: blank separator _merge_bold_fill(ws, 1, 22, 1, 25, "Doors") # Row 2: column labels _header_cell(ws, 2, 1, "Room") _header_cell(ws, 2, 2, "Width m") _header_cell(ws, 2, 3, "Length m") _header_cell(ws, 2, 4, "Area m²") _header_cell(ws, 2, 6, "Label") _header_cell(ws, 2, 7, "Location") _header_cell(ws, 2, 8, "Width m") _header_cell(ws, 2, 9, "Height m") _header_cell(ws, 2, 10, "Area m²") _header_cell(ws, 2, 11, "Opening Type") _header_cell(ws, 2, 12, "No. of Openings") _header_cell(ws, 2, 13, "Opening Width m") _header_cell(ws, 2, 14, "Opening Height m") _header_cell(ws, 2, 15, "Total Area m²") _header_cell(ws, 2, 16, "Blocked") _header_cell(ws, 2, 17, "Pictured") _header_cell(ws, 2, 18, "Effective Area") _header_cell(ws, 2, 19, "Count") _header_cell(ws, 2, 20, "Total Effective Area") _header_cell(ws, 2, 22, "Location") _header_cell(ws, 2, 23, "Width mm") _header_cell(ws, 2, 24, "Undercut mm") _header_cell(ws, 2, 25, "Area mm²") # Apply fill to header cells in rows 1–2, skipping blank separator columns for row in range(1, 3): for col in range(1, _TOTAL_COLS + 1): if col not in _BLANK_SEPARATOR_COLS: ws.cell(row=row, column=col).fill = _HEADER_FILL _apply_section_borders(ws, row) def _write_data_rows(ws: Worksheet, rows: list[AuditRow]) -> None: xl_row = 3 for row in rows: if row.floor_level is not None: for idx, (c1, c2) in enumerate(_FLOOR_MERGE_SEGMENTS): ws.merge_cells(start_row=xl_row, start_column=c1, end_row=xl_row, end_column=c2) label = f"Floor {row.floor_level}" if idx == 0 else None cell = ws.cell(row=xl_row, column=c1, value=label) cell.fill = _FLOOR_FILL if idx == 0: cell.font = _BOLD else: ws.cell(row=xl_row, column=1, value=row.room_name) ws.cell(row=xl_row, column=2, value=row.room_width_m) ws.cell(row=xl_row, column=3, value=row.room_length_m) ws.cell(row=xl_row, column=4, value=row.room_area_m2) ws.cell(row=xl_row, column=6, value=row.window_label) ws.cell(row=xl_row, column=7, value=row.window_location) ws.cell(row=xl_row, column=8, value=row.window_width_m) ws.cell(row=xl_row, column=9, value=row.window_height_m) ws.cell(row=xl_row, column=10, value=row.window_area_m2) ws.cell(row=xl_row, column=11, value=row.window_opening_type) ws.cell(row=xl_row, column=22, value=row.door_location) ws.cell(row=xl_row, column=23, value=row.door_width_mm) _apply_section_borders(ws, xl_row) if row.is_last_room_row: _apply_room_bottom_border(ws, xl_row) xl_row += 1 def _merge_bold_fill( ws: Worksheet, r1: int, c1: int, r2: int, c2: int, value: str ) -> None: ws.merge_cells(start_row=r1, start_column=c1, end_row=r2, end_column=c2) cell = ws.cell(row=r1, column=c1, value=value) cell.font = _BOLD cell.alignment = Alignment(horizontal="center") def _header_cell(ws: Worksheet, row: int, col: int, value: str) -> None: cell = ws.cell(row=row, column=col, value=value) cell.font = _BOLD def main() -> None: with db_read_session() as session: plan: Optional[Plan] = get_plan_by_uploaded_file_id(session, UPLOADED_FILE_ID) if plan is None: raise ValueError(f"No plan found for uploaded_file_id={UPLOADED_FILE_ID}") rows = _build_rows(plan) wb = openpyxl.Workbook() ws: Worksheet = wb.active # type: ignore[assignment] ws.title = "Audit" _write_headers(ws) _write_data_rows(ws, rows) output_path = f"./magic_plan_audit_{UPLOADED_FILE_ID}.xlsx" wb.save(output_path) print(output_path) if __name__ == "__main__": main()