125 lines
4.8 KiB
Python
125 lines
4.8 KiB
Python
"""Write mileage itineraries to Excel workbooks.
|
||
|
||
This module uses :mod:`openpyxl` to construct a workbook with one sheet
|
||
per month. Each row corresponds to a single hop between recognised
|
||
sites. Columns follow the specification used by the EveryHR system:
|
||
|
||
* ``Date`` – calendar date in ISO format (YYYY-MM-DD).
|
||
* ``Purpose`` – free text summarising the journey, e.g. ``"Travel from
|
||
Home to Lingwood Primary Academy 13.2mi"``.
|
||
* ``Miles`` – numeric value rounded to one decimal place.
|
||
* ``Vehicle`` – the vehicle descriptor configured for the user.
|
||
* ``Job Role`` – the job role of the user.
|
||
* ``From`` – friendly label of the origin site.
|
||
* ``To`` – friendly label of the destination site.
|
||
* ``Notes`` – blank for manual additions.
|
||
|
||
Rows are grouped by month (YYYY-MM). Each sheet is named after the
|
||
month and contains a header row followed by one row per hop in
|
||
chronological order.
|
||
"""
|
||
|
||
from __future__ import annotations
|
||
|
||
import os
|
||
from collections import defaultdict
|
||
from datetime import date
|
||
from typing import Dict, Iterable, List, Tuple
|
||
|
||
from openpyxl import Workbook
|
||
from openpyxl.utils import get_column_letter
|
||
|
||
from ..logic.detect_itinerary import Hop, SiteConfig
|
||
|
||
|
||
def build_monthly_rows(hops: Iterable[Hop], site_config: SiteConfig, distance_resolver) -> Dict[str, List[Tuple[str, str, float, str, str, str, str, str]]]:
|
||
"""Prepare rows grouped by month for Excel output.
|
||
|
||
Parameters
|
||
----------
|
||
hops : iterable of :class:`Hop`
|
||
The hops produced by itinerary detection.
|
||
site_config : :class:`SiteConfig`
|
||
Used to look up friendly labels for canonical site names.
|
||
distance_resolver : object
|
||
An object with a ``resolve(origin_name, dest_name, origin_coords, dest_coords)``
|
||
method that returns a distance in miles. See
|
||
:class:`~mileage_logger.distance.resolve.DistanceResolver`.
|
||
|
||
Returns
|
||
-------
|
||
dict mapping str -> list of tuples
|
||
Keys are month strings in the form ``YYYY-MM``. Values are
|
||
lists of tuples containing the data for each row: (date_str,
|
||
purpose, miles, vehicle, job_role, from_label, to_label, notes).
|
||
"""
|
||
|
||
rows_by_month: Dict[str, List[Tuple[str, str, float, str, str, str, str, str]]] = defaultdict(list)
|
||
for hop in hops:
|
||
month_key = hop.date.strftime("%Y-%m") # Keep YYYY-MM for sheet naming
|
||
origin_site = site_config.by_canonical.get(hop.origin)
|
||
dest_site = site_config.by_canonical.get(hop.destination)
|
||
if origin_site is None or dest_site is None:
|
||
continue
|
||
# Resolve distance
|
||
dist = distance_resolver.resolve(
|
||
hop.origin,
|
||
hop.destination,
|
||
(origin_site.lat, origin_site.lon),
|
||
(dest_site.lat, dest_site.lon),
|
||
)
|
||
# Build purpose string
|
||
purpose = f"Travel from {origin_site.label} to {dest_site.label} {dist:.1f}mi"
|
||
rows_by_month[month_key].append(
|
||
(
|
||
hop.date.strftime("%d/%m/%Y"), # Change date format here
|
||
purpose,
|
||
dist,
|
||
distance_resolver.vehicle_label if hasattr(distance_resolver, "vehicle_label") else "SH11 DRV (Own 1.6CC Diesel Car/Van)",
|
||
distance_resolver.job_role if hasattr(distance_resolver, "job_role") else "ICT Technician",
|
||
origin_site.label,
|
||
dest_site.label,
|
||
"",
|
||
)
|
||
)
|
||
|
||
return rows_by_month
|
||
|
||
|
||
def write_monthly_workbook(rows_by_month: Dict[str, List[Tuple[str, str, float, str, str, str, str, str]]], output_path: str) -> None:
|
||
"""Write the grouped rows into an Excel workbook.
|
||
|
||
Parameters
|
||
----------
|
||
rows_by_month : dict
|
||
Mapping from month strings to lists of row tuples as returned
|
||
by :func:`build_monthly_rows`.
|
||
output_path : str
|
||
Path of the Excel workbook to write. Any existing file will be
|
||
overwritten.
|
||
"""
|
||
|
||
wb = Workbook()
|
||
# Remove the default sheet created by openpyxl
|
||
default_sheet = wb.active
|
||
wb.remove(default_sheet)
|
||
for month, rows in sorted(rows_by_month.items()):
|
||
ws = wb.create_sheet(title=month)
|
||
# Write header
|
||
header = ["Date", "Purpose", "Miles", "Vehicle", "Job Role", "From", "To", "Notes"]
|
||
ws.append(header)
|
||
for row in rows:
|
||
ws.append(list(row))
|
||
# Autosize columns (approximate)
|
||
for col_idx in range(1, len(header) + 1):
|
||
column_letter = get_column_letter(col_idx)
|
||
max_length = max(
|
||
len(str(ws.cell(row=r + 1, column=col_idx).value)) for r in range(len(rows) + 1)
|
||
)
|
||
# Add a little extra padding
|
||
ws.column_dimensions[column_letter].width = max_length + 2
|
||
# Ensure directory exists
|
||
os.makedirs(os.path.dirname(output_path), exist_ok=True)
|
||
wb.save(output_path)
|
||
|