export.py 20 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523
  1. import csv
  2. import io
  3. import re
  4. from collections.abc import Sequence
  5. from datetime import datetime, timezone
  6. from typing import TYPE_CHECKING, Any
  7. from sqlalchemy import select
  8. from sqlalchemy.ext.asyncio import AsyncSession
  9. from sqlalchemy.orm import selectinload
  10. from backend.app.core.printer_scope import PrinterScope
  11. from backend.app.models.archive import PrintArchive
  12. from backend.app.models.print_log import INCLUDE_OPEN_RUNS, PrintLogEntry
  13. if TYPE_CHECKING:
  14. from backend.app.models.maintenance import MaintenanceLogEntry
  15. # A cell starting with one of these is read as a formula by spreadsheet apps,
  16. # including the full-width forms (= + - @) some normalise to ASCII.
  17. _FORMULA_PREFIXES = ("=", "+", "-", "@", "=", "+", "-", "@")
  18. def _spreadsheet_text(value: Any) -> Any:
  19. """A CSV cell as typed: text that starts like a formula gets a leading quote, so it stays text.
  20. Leading whitespace is looked past (spreadsheets trim it on import), and a
  21. value starting with a tab or line break is quoted too. Anything that isn't
  22. text is left alone.
  23. """
  24. if not isinstance(value, str) or not value:
  25. return value
  26. if value[0] in "\t\r\n" or value.lstrip().startswith(_FORMULA_PREFIXES):
  27. return f"'{value}"
  28. return value
  29. def _xlsx_cell(ws: Any, row: int, column: int, value: Any) -> Any:
  30. """Write a worksheet cell; text that starts with "=" is stored as text, not as a formula."""
  31. cell = ws.cell(row=row, column=column, value=value)
  32. if isinstance(value, str) and cell.data_type == "f":
  33. cell.data_type = "s"
  34. return cell
  35. # What one print of an archive has of its own (archive redesign): in the
  36. # export, its run's. Everything else is the file's, the archive's.
  37. _PER_PRINT_FIELDS = frozenset(
  38. {
  39. "status",
  40. "quantity",
  41. "printer_id",
  42. "printer_name",
  43. "plate_id",
  44. "filament_type",
  45. "filament_used_grams",
  46. "cost",
  47. "energy_kwh",
  48. "energy_cost",
  49. "wear_cost",
  50. "failure_reason",
  51. "user_verdict",
  52. "duration_seconds",
  53. "started_at",
  54. "completed_at",
  55. }
  56. )
  57. def _utc_iso(value: datetime | None) -> str:
  58. """A stored (naive UTC) time as ISO 8601 with its zone."""
  59. if value is None:
  60. return ""
  61. if value.tzinfo is not None:
  62. value = value.astimezone(timezone.utc).replace(tzinfo=None)
  63. return value.replace(microsecond=0).isoformat() + "Z"
  64. class ExportService:
  65. """Service for exporting archive data to CSV/Excel formats."""
  66. # Default fields to export
  67. DEFAULT_FIELDS = [
  68. "id",
  69. "run_id",
  70. "print_name",
  71. "filename",
  72. "status",
  73. "quantity",
  74. "printer_id",
  75. "printer_name",
  76. "plate_id",
  77. "project_name",
  78. "filament_type",
  79. "filament_used_grams",
  80. "print_time_seconds",
  81. "layer_height",
  82. "nozzle_diameter",
  83. "bed_temperature",
  84. "nozzle_temperature",
  85. "total_layers",
  86. "cost",
  87. "designer",
  88. "tags",
  89. "notes",
  90. "failure_reason",
  91. "user_verdict",
  92. "duration_seconds",
  93. "started_at",
  94. "completed_at",
  95. "created_at",
  96. ]
  97. # Field labels for headers
  98. FIELD_LABELS = {
  99. "id": "ID",
  100. "run_id": "Print ID",
  101. "printer_name": "Printer",
  102. "plate_id": "Plate",
  103. "user_verdict": "Outcome",
  104. "duration_seconds": "Duration (s)",
  105. "energy_kwh": "Energy (kWh)",
  106. "print_name": "Print Name",
  107. "filename": "Filename",
  108. "status": "Status",
  109. "quantity": "Items Printed",
  110. "printer_id": "Printer ID",
  111. "project_name": "Project",
  112. "filament_type": "Filament Type",
  113. "filament_used_grams": "Filament (g)",
  114. "print_time_seconds": "Print Time (s)",
  115. "layer_height": "Layer Height (mm)",
  116. "nozzle_diameter": "Nozzle (mm)",
  117. "bed_temperature": "Bed Temp (°C)",
  118. "nozzle_temperature": "Nozzle Temp (°C)",
  119. "total_layers": "Total Layers",
  120. "cost": "Cost",
  121. "energy_cost": "Energy Cost",
  122. "wear_cost": "Wear Cost",
  123. "designer": "Designer",
  124. "tags": "Tags",
  125. "notes": "Notes",
  126. "failure_reason": "Failure Reason",
  127. "started_at": "Started At",
  128. "completed_at": "Completed At",
  129. "created_at": "Created At",
  130. }
  131. @classmethod
  132. def unknown_fields(cls, fields: list[str]) -> list[str]:
  133. """The requested columns the export does not have: only its own
  134. columns are exported, never other attributes of an archive (its
  135. one-tap confirm token is a credential)."""
  136. return [f for f in fields if f not in cls.FIELD_LABELS]
  137. def __init__(self, db: AsyncSession):
  138. self.db = db
  139. async def export_archives(
  140. self,
  141. format: str = "csv",
  142. fields: list[str] | None = None,
  143. printer_id: int | None = None,
  144. project_id: int | None = None,
  145. status: str | None = None,
  146. date_from: datetime | None = None,
  147. date_to: datetime | None = None,
  148. search: str | None = None,
  149. visible_to_user_id: int | None = None,
  150. printer_scope: PrinterScope | None = None,
  151. ) -> tuple[bytes, str, str]:
  152. """Export archives to CSV or Excel format, one row per print.
  153. An archive is a file, each print of it a run (archive redesign): a row
  154. has the archive's file columns and one run's own (``_PER_PRINT_FIELDS``).
  155. An archive printed before runs were logged is one row of its own
  156. fields; one never printed, one row with empty print columns.
  157. ``printer_id``, ``status`` and the dates pick prints (a run's start,
  158. an archive without runs by when it was created); ``project_id``,
  159. ``search`` and ``visible_to_user_id`` pick archives. A caller limited
  160. to some printers gets only the prints on theirs.
  161. Returns:
  162. Tuple of (file_bytes, filename, content_type)
  163. """
  164. # Soft-deleted archives (#1343) are excluded: this export is the list
  165. # the user is looking at, saved to a file, and that list hides them —
  166. # an export that silently contains rows the UI says are gone is worse
  167. # than useless for reconciling anything (#2731).
  168. query = (
  169. select(PrintArchive)
  170. .options(selectinload(PrintArchive.project), selectinload(PrintArchive.printer))
  171. .where(PrintArchive.deleted_at.is_(None))
  172. .order_by(PrintArchive.created_at.desc())
  173. )
  174. if project_id:
  175. query = query.where(PrintArchive.project_id == project_id)
  176. if visible_to_user_id is not None:
  177. query = query.where(PrintArchive.created_by_id == visible_to_user_id)
  178. from backend.app.core.row_visibility import archive_scope_clause
  179. if printer_scope is not None and (clause := archive_scope_clause(printer_scope)) is not None:
  180. query = query.where(clause)
  181. if search:
  182. like_pattern = f"%{search}%"
  183. query = query.where(
  184. (PrintArchive.print_name.ilike(like_pattern))
  185. | (PrintArchive.filename.ilike(like_pattern))
  186. | (PrintArchive.tags.ilike(like_pattern))
  187. | (PrintArchive.notes.ilike(like_pattern))
  188. | (PrintArchive.designer.ilike(like_pattern))
  189. )
  190. archives = list((await self.db.execute(query)).scalars().all())
  191. # Every archive's runs, a print running now included; the caller's
  192. # only, when limited. By the archive query itself, not a list of its
  193. # ids: a farm's archives outnumber what one statement may bind.
  194. printed: set[int] = set()
  195. runs: dict[int, list[PrintLogEntry]] = {}
  196. if archives:
  197. archive_ids = query.with_only_columns(PrintArchive.id).order_by(None)
  198. all_runs = (
  199. await self.db.scalars(
  200. select(PrintLogEntry)
  201. .where(PrintLogEntry.archive_id.in_(archive_ids))
  202. .order_by(PrintLogEntry.started_at.desc().nulls_last(), PrintLogEntry.id.desc())
  203. .execution_options(**{INCLUDE_OPEN_RUNS: True})
  204. )
  205. ).all()
  206. for run in all_runs:
  207. if run.archive_id is None:
  208. continue
  209. printed.add(run.archive_id)
  210. if printer_scope is None or printer_scope.allows(run.printer_id):
  211. runs.setdefault(run.archive_id, []).append(run)
  212. statuses = {s.strip() for s in status.split(",") if s.strip()} if status else set()
  213. def picked(printer: int | None, row_status: str | None, when: datetime | None) -> bool:
  214. if printer_id and printer != printer_id:
  215. return False
  216. if statuses and row_status not in statuses:
  217. return False
  218. if date_from and (when is None or when < date_from):
  219. return False
  220. return not (date_to and (when is None or when > date_to))
  221. export_fields = fields if fields else self.DEFAULT_FIELDS
  222. rows = []
  223. for archive in archives:
  224. if archive.id in runs:
  225. for run in runs[archive.id]:
  226. if picked(run.printer_id, run.status, run.started_at or run.created_at):
  227. rows.append(self._archive_to_row(archive, export_fields, run))
  228. elif archive.id not in printed:
  229. # Printed before runs were logged, or never: the archive's own.
  230. if picked(archive.printer_id, archive.status, archive.created_at):
  231. rows.append(self._archive_to_row(archive, export_fields))
  232. elif not (printer_id or status or date_from or date_to):
  233. # Printed only on printers outside the caller's scope: the file.
  234. rows.append(self._archive_to_row(archive, export_fields, hide_prints=True))
  235. # Generate headers
  236. headers = [self.FIELD_LABELS.get(f, f) for f in export_fields]
  237. # Generate file
  238. timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
  239. if format == "xlsx":
  240. file_bytes = self._generate_xlsx(headers, rows, export_fields)
  241. filename = f"archives_export_{timestamp}.xlsx"
  242. content_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
  243. else:
  244. file_bytes = self._generate_csv(headers, rows)
  245. filename = f"archives_export_{timestamp}.csv"
  246. content_type = "text/csv"
  247. return file_bytes, filename, content_type
  248. async def export_stats(
  249. self,
  250. format: str = "csv",
  251. days: int = 30,
  252. printer_id: int | None = None,
  253. project_id: int | None = None,
  254. created_by_id: int | None = None,
  255. printer_scope: PrinterScope | None = None,
  256. ) -> tuple[bytes, str, str]:
  257. """Export statistics summary to CSV or Excel format.
  258. Args:
  259. format: Export format ('csv' or 'xlsx')
  260. days: Number of days to include in stats
  261. printer_id: Filter by printer
  262. project_id: Filter by project
  263. created_by_id: Filter by user who created the print (-1 for no user)
  264. Returns:
  265. Tuple of (file_bytes, filename, content_type)
  266. """
  267. from backend.app.services.failure_analysis import FailureAnalysisService
  268. # Get failure analysis data (includes stats)
  269. analysis_service = FailureAnalysisService(self.db)
  270. analysis = await analysis_service.analyze_failures(
  271. days=days,
  272. printer_id=printer_id,
  273. project_id=project_id,
  274. created_by_id=created_by_id,
  275. printer_scope=printer_scope,
  276. )
  277. # Build stats rows
  278. rows = [
  279. ["Metric", "Value"],
  280. ["Period (days)", analysis["period_days"]],
  281. ["Total Prints", analysis["total_prints"]],
  282. ["Failed Prints", analysis["failed_prints"]],
  283. ["Failure Rate (%)", analysis["failure_rate"]],
  284. [""],
  285. ["Failures by Reason", ""],
  286. ]
  287. for reason, count in analysis["failures_by_reason"].items():
  288. rows.append([reason, count])
  289. rows.append([""])
  290. rows.append(["Failures by Filament", ""])
  291. for filament, count in analysis["failures_by_filament"].items():
  292. rows.append([filament, count])
  293. rows.append([""])
  294. rows.append(["Failures by Printer", ""])
  295. for printer, count in analysis["failures_by_printer"].items():
  296. rows.append([printer, count])
  297. rows.append([""])
  298. rows.append(["Weekly Trend", ""])
  299. rows.append(["Week", "Total", "Failed", "Rate (%)"])
  300. for week in analysis["trend"]:
  301. rows.append(
  302. [
  303. week["week_start"],
  304. week["total_prints"],
  305. week["failed_prints"],
  306. week["failure_rate"],
  307. ]
  308. )
  309. timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
  310. if format == "xlsx":
  311. file_bytes = self._generate_xlsx_simple(rows)
  312. filename = f"stats_export_{timestamp}.xlsx"
  313. content_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
  314. else:
  315. file_bytes = self._generate_csv_simple(rows)
  316. filename = f"stats_export_{timestamp}.csv"
  317. content_type = "text/csv"
  318. return file_bytes, filename, content_type
  319. def _archive_to_row(
  320. self,
  321. archive: PrintArchive,
  322. fields: list[str],
  323. run: PrintLogEntry | None = None,
  324. *,
  325. hide_prints: bool = False,
  326. ) -> list[Any]:
  327. """One row: the archive's file columns, and ``run``'s own (without a
  328. run, the archive's, as it kept its print before runs; empty with
  329. ``hide_prints``)."""
  330. row = []
  331. for field in fields:
  332. if field == "project_name":
  333. value = archive.project.name if archive.project else None
  334. elif field == "run_id":
  335. value = run.id if run is not None else None
  336. elif field in _PER_PRINT_FIELDS and hide_prints:
  337. value = None
  338. elif field in _PER_PRINT_FIELDS and run is not None:
  339. value = getattr(run, field, None)
  340. # Runs logged before they kept these share their archive's.
  341. if field in ("filament_type", "quantity") and value is None:
  342. value = getattr(archive, field)
  343. elif field == "printer_name":
  344. value = archive.printer.name if archive.printer is not None else None
  345. elif field == "duration_seconds":
  346. started, completed = archive.started_at, archive.completed_at
  347. value = (
  348. int((completed - started).total_seconds())
  349. if started and completed and completed > started
  350. else None
  351. )
  352. else:
  353. value = getattr(archive, field, None)
  354. if isinstance(value, datetime):
  355. value = value.isoformat()
  356. row.append(value)
  357. return row
  358. MAINTENANCE_LOG_HEADERS = ["Performed at (UTC)", "Printer", "Task", "Performed by", "Print hours", "Comment"]
  359. @classmethod
  360. def export_maintenance_log(cls, entries: Sequence["MaintenanceLogEntry"], scope_label: str) -> tuple[bytes, str]:
  361. """The maintenance log as CSV, and its file name.
  362. ``scope_label`` names what the file holds (a printer's name, or
  363. "all") in the file name. Text that people typed is written so that a
  364. spreadsheet shows it as text, never runs it as a formula; the BOM makes
  365. Excel read the file as UTF-8.
  366. """
  367. rows = [
  368. [
  369. _utc_iso(entry.performed_at),
  370. _spreadsheet_text(entry.printer_name),
  371. _spreadsheet_text(entry.maintenance_type_name),
  372. _spreadsheet_text(entry.performed_by_username),
  373. round(entry.hours_at_maintenance or 0.0, 1),
  374. _spreadsheet_text(entry.notes),
  375. ]
  376. for entry in entries
  377. ]
  378. output = io.StringIO()
  379. output.write("\ufeff")
  380. writer = csv.writer(output)
  381. writer.writerow(cls.MAINTENANCE_LOG_HEADERS)
  382. writer.writerows(rows)
  383. slug = re.sub(r"[^\w-]+", "_", scope_label, flags=re.UNICODE).strip("_") or "printer"
  384. timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
  385. return output.getvalue().encode("utf-8"), f"maintenance_log_{slug}_{timestamp}.csv"
  386. def _generate_csv(self, headers: list[str], rows: list[list]) -> bytes:
  387. """Generate CSV file content."""
  388. output = io.StringIO()
  389. writer = csv.writer(output)
  390. writer.writerow(headers)
  391. writer.writerows([[_spreadsheet_text(value) for value in row] for row in rows])
  392. return output.getvalue().encode("utf-8")
  393. def _generate_csv_simple(self, rows: list[list]) -> bytes:
  394. """Generate CSV file content from simple rows (no separate headers)."""
  395. output = io.StringIO()
  396. writer = csv.writer(output)
  397. writer.writerows([[_spreadsheet_text(value) for value in row] for row in rows])
  398. return output.getvalue().encode("utf-8")
  399. def _generate_xlsx(self, headers: list[str], rows: list[list], fields: list[str]) -> bytes:
  400. """Generate Excel file content."""
  401. try:
  402. from openpyxl import Workbook
  403. from openpyxl.styles import Alignment, Font, PatternFill
  404. from openpyxl.utils import get_column_letter
  405. except ImportError:
  406. raise ImportError("openpyxl is required for Excel export. Install with: pip install openpyxl")
  407. wb = Workbook()
  408. ws = wb.active
  409. ws.title = "Archives"
  410. # Header style
  411. header_font = Font(bold=True, color="FFFFFF")
  412. header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
  413. header_alignment = Alignment(horizontal="center")
  414. # Write headers
  415. for col, header in enumerate(headers, 1):
  416. cell = ws.cell(row=1, column=col, value=header)
  417. cell.font = header_font
  418. cell.fill = header_fill
  419. cell.alignment = header_alignment
  420. # Write data
  421. for row_idx, row in enumerate(rows, 2):
  422. for col_idx, value in enumerate(row, 1):
  423. _xlsx_cell(ws, row_idx, col_idx, value)
  424. # Auto-adjust column widths
  425. for col_idx, _field in enumerate(fields, 1):
  426. column_letter = get_column_letter(col_idx)
  427. max_length = len(headers[col_idx - 1])
  428. for row in rows:
  429. cell_value = row[col_idx - 1]
  430. if cell_value is not None:
  431. max_length = max(max_length, len(str(cell_value)))
  432. ws.column_dimensions[column_letter].width = min(max_length + 2, 50)
  433. # Freeze header row
  434. ws.freeze_panes = "A2"
  435. output = io.BytesIO()
  436. wb.save(output)
  437. return output.getvalue()
  438. def _generate_xlsx_simple(self, rows: list[list]) -> bytes:
  439. """Generate Excel file content from simple rows."""
  440. try:
  441. from openpyxl import Workbook
  442. from openpyxl.styles import Font
  443. except ImportError:
  444. raise ImportError("openpyxl is required for Excel export. Install with: pip install openpyxl")
  445. wb = Workbook()
  446. ws = wb.active
  447. ws.title = "Statistics"
  448. bold_font = Font(bold=True)
  449. for row_idx, row in enumerate(rows, 1):
  450. for col_idx, value in enumerate(row, 1):
  451. cell = _xlsx_cell(ws, row_idx, col_idx, value)
  452. # Bold section headers
  453. if col_idx == 1 and value and isinstance(value, str) and value.endswith(":"):
  454. cell.font = bold_font
  455. output = io.BytesIO()
  456. wb.save(output)
  457. return output.getvalue()