export.py 18 KB

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