573 lines
23 KiB
Python
573 lines
23 KiB
Python
"""Python/openpyxl monthly workbook building and recoverable publication."""
|
|
|
|
from __future__ import annotations
|
|
|
|
import hashlib
|
|
import json
|
|
import os
|
|
import shutil
|
|
import tempfile
|
|
import uuid
|
|
from dataclasses import dataclass
|
|
from datetime import date, datetime
|
|
from decimal import Decimal, InvalidOperation
|
|
from pathlib import Path
|
|
from typing import Any, Dict, Mapping, Optional
|
|
|
|
from openpyxl import Workbook, load_workbook
|
|
from openpyxl.styles import Alignment, Font, PatternFill
|
|
from openpyxl.utils import get_column_letter
|
|
|
|
from arr_storage.store import ManagedObjectStore
|
|
from monthly_reports.contracts import KB_HEADER, RESULT_SCHEMA_VERSION, XLSX_MIME, ErrorCode, MonthlyReport
|
|
from monthly_reports.repository import (
|
|
FileMetadata,
|
|
ReportRepository,
|
|
RepositoryError,
|
|
ReservedReport,
|
|
)
|
|
|
|
|
|
class BuildError(RuntimeError):
|
|
def __init__(self, code: str, safe_message: str):
|
|
super().__init__(safe_message)
|
|
self.code = code
|
|
self.safe_message = safe_message
|
|
|
|
|
|
class PublicationError(RuntimeError):
|
|
def __init__(self, code: str, safe_message: str):
|
|
super().__init__(safe_message)
|
|
self.code = code
|
|
self.safe_message = safe_message
|
|
|
|
|
|
@dataclass(frozen=True)
|
|
class BuiltWorkbook:
|
|
path: Path
|
|
sha256: str
|
|
byte_size: int
|
|
summary: Mapping[str, Any]
|
|
|
|
|
|
@dataclass(frozen=True)
|
|
class PublicationOutcome:
|
|
latest_path: Path
|
|
latest_result_path: Path
|
|
archive_path: Path
|
|
result_path: Path
|
|
artifact: FileMetadata
|
|
result_json: FileMetadata
|
|
|
|
|
|
def sha256_file(path: Path) -> str:
|
|
digest = hashlib.sha256()
|
|
with path.open("rb") as handle:
|
|
for chunk in iter(lambda: handle.read(1024 * 1024), b""):
|
|
digest.update(chunk)
|
|
return digest.hexdigest()
|
|
|
|
|
|
def _private_json(path: Path, payload: Mapping[str, Any]) -> None:
|
|
path.parent.mkdir(parents=True, exist_ok=True, mode=0o700)
|
|
descriptor = os.open(path, os.O_WRONLY | os.O_CREAT | os.O_TRUNC, 0o600)
|
|
try:
|
|
with os.fdopen(descriptor, "w", encoding="utf-8") as handle:
|
|
json.dump(payload, handle, ensure_ascii=False, sort_keys=True, indent=2)
|
|
handle.write("\n")
|
|
handle.flush()
|
|
os.fsync(handle.fileno())
|
|
except Exception:
|
|
try:
|
|
os.close(descriptor)
|
|
except OSError:
|
|
pass
|
|
raise
|
|
|
|
|
|
class OpenpyxlWorkbookBuilder:
|
|
"""Build and re-open a validated monthly workbook without external runtimes."""
|
|
|
|
_INTEGER_HEADERS = frozenset({"NIGHTS", "ADULTS", "CHILDREN", "NO_OF_ROOMS"})
|
|
_DECIMAL_HEADERS = frozenset({"RATE_AMOUNT", "REAL PRICE", "TOTAL PRICE", KB_HEADER})
|
|
_DECIMAL_TOLERANCE = Decimal("0.000001")
|
|
|
|
def __init__(self, *_legacy_args: Any, **_legacy_options: Any) -> None:
|
|
# The ignored arguments keep older local wrappers import-compatible while
|
|
# making the production builder entirely Python/openpyxl based.
|
|
_ = (_legacy_args, _legacy_options)
|
|
|
|
@staticmethod
|
|
def _safe_text(value: Any) -> str:
|
|
text = "" if value is None else str(value)
|
|
return "'" + text if text[:1] in {"=", "+", "-", "@"} else text
|
|
|
|
@staticmethod
|
|
def _date_key(value: Any) -> str:
|
|
if isinstance(value, datetime):
|
|
return value.date().isoformat()
|
|
if isinstance(value, date):
|
|
return value.isoformat()
|
|
if isinstance(value, str) and len(value) >= 10:
|
|
return value[:10]
|
|
return ""
|
|
|
|
@staticmethod
|
|
def _semantic_sha256(payload: Mapping[str, Any]) -> str:
|
|
encoded = json.dumps(
|
|
{"channels": payload["channels"]},
|
|
ensure_ascii=False,
|
|
separators=(",", ":"),
|
|
).encode("utf-8")
|
|
return hashlib.sha256(encoded).hexdigest()
|
|
|
|
@classmethod
|
|
def _cell_value(cls, header: str, value: Any) -> Any:
|
|
if header in {"ARRIVAL", "DEPARTURE"}:
|
|
return date.fromisoformat(str(value))
|
|
if header in cls._INTEGER_HEADERS:
|
|
return int(value)
|
|
if header in cls._DECIMAL_HEADERS:
|
|
return float(Decimal(str(value)))
|
|
return cls._safe_text(value)
|
|
|
|
@classmethod
|
|
def _decimal_matches(cls, actual: Any, expected: Any) -> bool:
|
|
try:
|
|
actual_decimal = Decimal(str(actual))
|
|
expected_decimal = Decimal(str(expected))
|
|
except (InvalidOperation, TypeError, ValueError):
|
|
return False
|
|
if not actual_decimal.is_finite() or not expected_decimal.is_finite():
|
|
return False
|
|
return abs(actual_decimal - expected_decimal) <= cls._DECIMAL_TOLERANCE
|
|
|
|
@staticmethod
|
|
def _style_sheet(worksheet: Any, headers: list[str]) -> None:
|
|
worksheet.freeze_panes = "A2"
|
|
worksheet.auto_filter.ref = f"A1:{get_column_letter(len(headers))}{max(1, worksheet.max_row)}"
|
|
for column, header in enumerate(headers, start=1):
|
|
cell = worksheet.cell(1, column)
|
|
cell.font = Font(name="Arial", size=10, bold=True, color="000000")
|
|
cell.fill = PatternFill("solid", fgColor="D9EAF7")
|
|
cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
|
|
worksheet.column_dimensions[get_column_letter(column)].width = min(
|
|
34, max(12, len(header) + 2)
|
|
)
|
|
worksheet.row_dimensions[1].height = 28
|
|
for row in worksheet.iter_rows(min_row=2):
|
|
for cell in row:
|
|
cell.font = Font(name="Arial", size=10, color="222222")
|
|
cell.alignment = Alignment(vertical="center", wrap_text=True)
|
|
worksheet.row_dimensions[row[0].row].height = 24
|
|
for column in ("A", "B"):
|
|
for cell in worksheet[column][1:]:
|
|
cell.number_format = "yyyy-mm-dd"
|
|
|
|
@classmethod
|
|
def _write_sheet(cls, worksheet: Any, channel: Mapping[str, Any]) -> int:
|
|
headers = [str(header) for header in channel["headers"]]
|
|
worksheet.append(headers)
|
|
total_column = headers.index("TOTAL PRICE") + 1
|
|
for row in channel["rows"]:
|
|
row_index = worksheet.max_row + 1
|
|
values = [
|
|
cls._cell_value(header, row.get(header, ""))
|
|
for header in headers
|
|
]
|
|
values[total_column - 1] = f"=R{row_index}*C{row_index}*G{row_index}"
|
|
worksheet.append(values)
|
|
cls._style_sheet(worksheet, headers)
|
|
return len(channel["rows"])
|
|
|
|
@classmethod
|
|
def _validate_workbook(
|
|
cls,
|
|
output_path: Path,
|
|
report: MonthlyReport,
|
|
payload: Mapping[str, Any],
|
|
) -> int:
|
|
workbook = load_workbook(output_path, data_only=False, read_only=True)
|
|
try:
|
|
expected_names = [channel.worksheet for channel in report.channels]
|
|
expected_rows = [len(channel.rows) for channel in report.channels]
|
|
if workbook.sheetnames != expected_names:
|
|
raise ValueError("worksheet names do not match")
|
|
formula_count = 0
|
|
for channel, expected_row_count in zip(report.channels, expected_rows):
|
|
worksheet = workbook[channel.worksheet]
|
|
headers = list(channel.headers)
|
|
row_iterator = worksheet.iter_rows(values_only=False)
|
|
header_cells = next(row_iterator, None)
|
|
if (
|
|
header_cells is None
|
|
or len(header_cells) != len(headers)
|
|
or [cell.value for cell in header_cells] != headers
|
|
):
|
|
raise ValueError("worksheet headers do not match")
|
|
for row_index, expected_row in enumerate(channel.rows, start=2):
|
|
cells = next(row_iterator, None)
|
|
if cells is None or len(cells) != len(headers):
|
|
raise ValueError("worksheet dimensions do not match")
|
|
payload_row = expected_row.to_payload(channel.worksheet == "DY-AI-Easy-KB")
|
|
for header, cell in zip(headers, cells):
|
|
if header == "TOTAL PRICE":
|
|
expected_formula = f"=R{row_index}*C{row_index}*G{row_index}"
|
|
if cell.value != expected_formula or cell.data_type != "f":
|
|
raise ValueError("total price formula does not match")
|
|
formula_count += 1
|
|
continue
|
|
actual = cell.value
|
|
expected = payload_row[header]
|
|
if header in {"ARRIVAL", "DEPARTURE"}:
|
|
matches = cls._date_key(actual) == str(expected)
|
|
elif header in cls._INTEGER_HEADERS:
|
|
matches = actual == int(expected)
|
|
elif header in cls._DECIMAL_HEADERS:
|
|
matches = cls._decimal_matches(actual, expected)
|
|
else:
|
|
matches = ("" if actual is None else actual) == cls._safe_text(expected)
|
|
if not matches:
|
|
raise ValueError("worksheet values do not match")
|
|
if next(row_iterator, None) is not None:
|
|
raise ValueError("worksheet dimensions do not match")
|
|
if formula_count != report.row_count:
|
|
raise ValueError("formula count does not match")
|
|
if cls._semantic_sha256(payload) != cls._semantic_sha256(report.to_workbook_payload()):
|
|
raise ValueError("semantic source differs")
|
|
return formula_count
|
|
finally:
|
|
workbook.close()
|
|
|
|
def build(self, report: MonthlyReport, work_dir: Path) -> BuiltWorkbook:
|
|
work_dir.mkdir(parents=True, exist_ok=True, mode=0o700)
|
|
os.chmod(work_dir, 0o700)
|
|
output_path = work_dir / report.filename
|
|
summary_path = work_dir / "workbook-summary.json"
|
|
payload = report.to_workbook_payload()
|
|
try:
|
|
workbook = Workbook()
|
|
for index, channel in enumerate(payload["channels"]):
|
|
worksheet = workbook.active if index == 0 else workbook.create_sheet()
|
|
worksheet.title = str(channel["worksheet"])
|
|
self._write_sheet(worksheet, channel)
|
|
workbook.save(output_path)
|
|
workbook.close()
|
|
os.chmod(output_path, 0o600)
|
|
formula_count = self._validate_workbook(output_path, report, payload)
|
|
expected_names = [channel.worksheet for channel in report.channels]
|
|
expected_rows = [len(channel.rows) for channel in report.channels]
|
|
summary = {
|
|
"status": "success",
|
|
"schema_version": payload["schema_version"],
|
|
"report_year": report.report_year,
|
|
"report_month": report.report_month,
|
|
"as_of_date": report.as_of_date.isoformat(),
|
|
"filename": report.filename,
|
|
"sheet_names": expected_names,
|
|
"row_counts": expected_rows,
|
|
"formula_count": formula_count,
|
|
"semantic_sha256": self._semantic_sha256(payload),
|
|
"preview_count": len(expected_names),
|
|
"byte_size": output_path.stat().st_size,
|
|
}
|
|
_private_json(summary_path, summary)
|
|
except (OSError, ValueError, TypeError, InvalidOperation, KeyError):
|
|
raise BuildError(
|
|
ErrorCode.OUTPUT_VALIDATION_FAILED,
|
|
"the monthly XLSX builder result could not be validated",
|
|
) from None
|
|
return BuiltWorkbook(
|
|
path=output_path,
|
|
sha256=sha256_file(output_path),
|
|
byte_size=output_path.stat().st_size,
|
|
summary=summary,
|
|
)
|
|
|
|
class AtomicReportPublisher:
|
|
def __init__(
|
|
self,
|
|
project_root: Path,
|
|
output_root: Path,
|
|
*,
|
|
object_store: Optional[ManagedObjectStore] = None,
|
|
bucket_alias: str = "arr-private",
|
|
) -> None:
|
|
self._project_root = project_root.resolve()
|
|
self._output_root = output_root.resolve()
|
|
self._object_store = object_store
|
|
self._bucket_alias = bucket_alias
|
|
try:
|
|
self._output_root.relative_to(self._project_root)
|
|
except ValueError:
|
|
raise ValueError("output_root must be inside project_root") from None
|
|
|
|
@staticmethod
|
|
def _fsync_parent(path: Path) -> None:
|
|
descriptor = os.open(path.parent, os.O_RDONLY)
|
|
try:
|
|
os.fsync(descriptor)
|
|
finally:
|
|
os.close(descriptor)
|
|
|
|
@classmethod
|
|
def _atomic_copy(cls, source: Path, target: Path) -> None:
|
|
target.parent.mkdir(parents=True, exist_ok=True, mode=0o700)
|
|
temporary = target.parent / f".{target.name}.tmp-{uuid.uuid4().hex}"
|
|
try:
|
|
shutil.copyfile(source, temporary)
|
|
os.chmod(temporary, 0o600)
|
|
with temporary.open("rb") as handle:
|
|
os.fsync(handle.fileno())
|
|
os.replace(temporary, target)
|
|
cls._fsync_parent(target)
|
|
finally:
|
|
temporary.unlink(missing_ok=True)
|
|
|
|
@classmethod
|
|
def _atomic_json(cls, target: Path, payload: Mapping[str, Any]) -> None:
|
|
target.parent.mkdir(parents=True, exist_ok=True, mode=0o700)
|
|
temporary = target.parent / f".{target.name}.tmp-{uuid.uuid4().hex}"
|
|
try:
|
|
_private_json(temporary, payload)
|
|
os.replace(temporary, target)
|
|
cls._fsync_parent(target)
|
|
finally:
|
|
temporary.unlink(missing_ok=True)
|
|
|
|
@classmethod
|
|
def _install_once(cls, source: Path, target: Path, expected_sha256: str) -> bool:
|
|
if target.exists():
|
|
if sha256_file(target) != expected_sha256:
|
|
raise PublicationError(
|
|
ErrorCode.PUBLISH_FAILED,
|
|
"an immutable monthly archive path contains different content",
|
|
)
|
|
return False
|
|
cls._atomic_copy(source, target)
|
|
return True
|
|
|
|
def _storage_key(self, path: Path) -> str:
|
|
try:
|
|
return path.resolve().relative_to(self._project_root).as_posix()
|
|
except ValueError:
|
|
raise PublicationError(
|
|
ErrorCode.PUBLISH_FAILED,
|
|
"a monthly artifact escaped the controlled project root",
|
|
) from None
|
|
|
|
@staticmethod
|
|
def _success_result(
|
|
report: MonthlyReport,
|
|
reservation: ReservedReport,
|
|
artifact_storage_key: str,
|
|
artifact_sha256: str,
|
|
semantic_sha256: str,
|
|
storage_provider: str,
|
|
bucket_alias: str,
|
|
) -> Dict[str, Any]:
|
|
return {
|
|
"schema_version": RESULT_SCHEMA_VERSION,
|
|
"status": "success",
|
|
"report_year": report.report_year,
|
|
"report_month": report.report_month,
|
|
"as_of_date": report.as_of_date.isoformat(),
|
|
"report_version_id": reservation.report_version_id,
|
|
"version_no": reservation.version_no,
|
|
"artifact": {
|
|
"filename": report.filename,
|
|
"storage_key": artifact_storage_key,
|
|
"sha256": artifact_sha256,
|
|
"semantic_sha256": semantic_sha256,
|
|
"storage_provider": storage_provider,
|
|
"bucket_alias": bucket_alias,
|
|
},
|
|
"row_count": report.row_count,
|
|
"channel_manifest": [
|
|
{
|
|
"worksheet": worksheet,
|
|
"worksheet_order": order,
|
|
"row_count": row_count,
|
|
}
|
|
for worksheet, order, row_count in report.channel_manifest
|
|
],
|
|
"daily_version_count": len(report.daily_versions),
|
|
"warnings": [],
|
|
"errors": [],
|
|
}
|
|
|
|
def publish(
|
|
self,
|
|
report: MonthlyReport,
|
|
reservation: ReservedReport,
|
|
built: BuiltWorkbook,
|
|
repository: ReportRepository,
|
|
work_dir: Path,
|
|
) -> PublicationOutcome:
|
|
month_root = self._output_root / f"{report.report_year:04d}" / f"{report.report_month:02d}"
|
|
version_root = month_root / "archive" / f"v{reservation.version_no:04d}"
|
|
archive_path = version_root / report.filename
|
|
result_path = version_root / "result.json"
|
|
latest_path = month_root / "latest.xlsx"
|
|
latest_result_path = month_root / "latest.result.json"
|
|
workbook_backup = work_dir / "previous-latest.xlsx"
|
|
result_backup = work_dir / "previous-latest.result.json"
|
|
latest_existed = latest_path.is_file()
|
|
latest_result_existed = latest_result_path.is_file()
|
|
archive_created = False
|
|
result_created = False
|
|
latest_replaced = False
|
|
latest_result_replaced = False
|
|
|
|
try:
|
|
month_root.mkdir(parents=True, exist_ok=True, mode=0o700)
|
|
if latest_existed:
|
|
shutil.copyfile(latest_path, workbook_backup)
|
|
os.chmod(workbook_backup, 0o600)
|
|
if latest_result_existed:
|
|
shutil.copyfile(latest_result_path, result_backup)
|
|
os.chmod(result_backup, 0o600)
|
|
|
|
archive_created = self._install_once(built.path, archive_path, built.sha256)
|
|
storage_provider = "local"
|
|
bucket_alias = "arr-project-root"
|
|
artifact_storage_key = self._storage_key(archive_path)
|
|
if self._object_store is not None:
|
|
uploaded_artifact = self._object_store.upload_committed(
|
|
job_id=f"monthly-report-{reservation.report_version_id}",
|
|
attempt_no=1,
|
|
role="monthly_report",
|
|
source=archive_path,
|
|
original_filename=report.filename,
|
|
expected_sha256=built.sha256,
|
|
expected_byte_size=built.byte_size,
|
|
)
|
|
storage_provider = "oss"
|
|
bucket_alias = self._bucket_alias
|
|
artifact_storage_key = uploaded_artifact.object_key
|
|
result_payload = self._success_result(
|
|
report,
|
|
reservation,
|
|
artifact_storage_key,
|
|
built.sha256,
|
|
str(built.summary.get("semantic_sha256", "")),
|
|
storage_provider,
|
|
bucket_alias,
|
|
)
|
|
if result_path.exists():
|
|
expected = json.dumps(
|
|
result_payload,
|
|
ensure_ascii=False,
|
|
sort_keys=True,
|
|
indent=2,
|
|
) + "\n"
|
|
if result_path.read_text(encoding="utf-8") != expected:
|
|
raise PublicationError(
|
|
ErrorCode.PUBLISH_FAILED,
|
|
"an immutable monthly result path contains different content",
|
|
)
|
|
else:
|
|
self._atomic_json(result_path, result_payload)
|
|
result_created = True
|
|
|
|
self._atomic_copy(built.path, latest_path)
|
|
latest_replaced = True
|
|
self._atomic_json(latest_result_path, result_payload)
|
|
latest_result_replaced = True
|
|
artifact = FileMetadata(
|
|
file_kind="monthly_xlsx",
|
|
original_filename=report.filename,
|
|
storage_key=artifact_storage_key,
|
|
sha256=built.sha256,
|
|
byte_size=built.byte_size,
|
|
mime_type=XLSX_MIME,
|
|
)
|
|
result_json = FileMetadata(
|
|
file_kind="result_json",
|
|
original_filename=result_path.name,
|
|
storage_key=self._storage_key(result_path),
|
|
sha256=sha256_file(result_path),
|
|
byte_size=result_path.stat().st_size,
|
|
mime_type="application/json",
|
|
storage_provider=storage_provider,
|
|
bucket_alias=bucket_alias,
|
|
)
|
|
if self._object_store is not None:
|
|
uploaded_result = self._object_store.upload_committed(
|
|
job_id=f"monthly-report-{reservation.report_version_id}",
|
|
attempt_no=1,
|
|
role="result_json",
|
|
source=result_path,
|
|
original_filename=result_path.name,
|
|
expected_sha256=result_json.sha256,
|
|
expected_byte_size=result_json.byte_size,
|
|
)
|
|
result_json = FileMetadata(
|
|
file_kind="result_json",
|
|
original_filename=result_path.name,
|
|
storage_key=uploaded_result.object_key,
|
|
sha256=uploaded_result.sha256,
|
|
byte_size=uploaded_result.byte_size,
|
|
mime_type=uploaded_result.mime_type,
|
|
storage_provider=storage_provider,
|
|
bucket_alias=bucket_alias,
|
|
)
|
|
artifact = FileMetadata(
|
|
file_kind="monthly_xlsx",
|
|
original_filename=report.filename,
|
|
storage_key=artifact_storage_key,
|
|
sha256=built.sha256,
|
|
byte_size=built.byte_size,
|
|
mime_type=XLSX_MIME,
|
|
storage_provider=storage_provider,
|
|
bucket_alias=bucket_alias,
|
|
)
|
|
repository.activate_report(
|
|
reservation,
|
|
artifact,
|
|
result_json,
|
|
str(built.summary.get("semantic_sha256", "")),
|
|
)
|
|
return PublicationOutcome(
|
|
latest_path=latest_path,
|
|
latest_result_path=latest_result_path,
|
|
archive_path=archive_path,
|
|
result_path=result_path,
|
|
artifact=artifact,
|
|
result_json=result_json,
|
|
)
|
|
except Exception as error:
|
|
if latest_result_replaced:
|
|
if latest_result_existed and result_backup.is_file():
|
|
self._atomic_copy(result_backup, latest_result_path)
|
|
else:
|
|
latest_result_path.unlink(missing_ok=True)
|
|
if latest_replaced:
|
|
if latest_existed and workbook_backup.is_file():
|
|
self._atomic_copy(workbook_backup, latest_path)
|
|
else:
|
|
latest_path.unlink(missing_ok=True)
|
|
if result_created:
|
|
result_path.unlink(missing_ok=True)
|
|
if archive_created:
|
|
archive_path.unlink(missing_ok=True)
|
|
code = getattr(error, "code", ErrorCode.PUBLISH_FAILED)
|
|
safe_message = getattr(error, "safe_message", "monthly report publication failed")
|
|
try:
|
|
repository.mark_failed(reservation, code, safe_message)
|
|
except Exception:
|
|
pass
|
|
if isinstance(error, PublicationError):
|
|
raise
|
|
if isinstance(error, RepositoryError):
|
|
raise PublicationError(error.code, error.safe_message) from None
|
|
raise PublicationError(
|
|
ErrorCode.PUBLISH_FAILED,
|
|
"monthly report publication failed",
|
|
) from None
|
|
|
|
|
|
def private_staging_directory(parent: Path):
|
|
parent.mkdir(parents=True, exist_ok=True, mode=0o700)
|
|
return tempfile.TemporaryDirectory(prefix="monthly-report-", dir=parent)
|