spool_csv.py 28 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695
  1. """CSV import/export for the spool inventory (#1576).
  2. One module owns the round-trip: the same fixed column schema is used to
  3. serialise existing spools out and to parse + validate a user-supplied CSV
  4. back in. Validation reuses the `SpoolCreate` Pydantic model so the CSV path
  5. and the form path share a single source of truth — anything the form rejects,
  6. the import rejects too, with the same rules.
  7. The import flow is two-phase by design: `parse_and_validate()` never writes.
  8. The route calls it once for the dry-run preview (so the user sees per-row
  9. valid/error/skipped before committing) and again on confirm, then persists
  10. only the rows that came back `valid`.
  11. """
  12. import csv
  13. import io
  14. from datetime import datetime, timezone
  15. from pydantic import BaseModel, ValidationError
  16. from sqlalchemy import func, select, text
  17. from sqlalchemy.ext.asyncio import AsyncSession
  18. from backend.app.core.database import is_sqlite
  19. from backend.app.models.color_catalog import ColorCatalogEntry
  20. from backend.app.models.spool import Spool
  21. from backend.app.models.supplier import Supplier, supplier_name_key
  22. from backend.app.schemas.spool import SpoolCreate
  23. # Fixed CSV header, in output order. Round-trips cleanly: export writes these
  24. # columns, import expects them. `material` is the only required field; the rest
  25. # are optional. Keep aligned with the SpoolCreate fields referenced below.
  26. #
  27. # `remaining` is a derived, export-only column (= label_weight - weight_used).
  28. # It's written out for human readability and round-trip clarity, but ignored on
  29. # import — `weight_used` is the source of truth, and accepting both would let
  30. # them contradict. `last_used` is a timestamp the model carries but SpoolCreate
  31. # does not, so import applies it to the ORM object directly (see persist path).
  32. # `storage_location`, `category`, `low_stock_threshold_pct` and
  33. # `material_number` (#2870) are SpoolCreate fields included so a round-trip
  34. # preserves them (they'd otherwise be lost).
  35. CSV_COLUMNS = [
  36. # The spool's id (#3520): a printed label's QR code carries it, so moving
  37. # the inventory to another install must not renumber the spools. Import
  38. # keeps it when this install never handed it out (see `_decide_ids`), and
  39. # gives the spool a new one otherwise; an empty cell is a new spool.
  40. "id",
  41. "material",
  42. "brand",
  43. "subtype",
  44. "color_name",
  45. "rgba",
  46. "extra_colors",
  47. "effect_type",
  48. "label_weight",
  49. "weight_used",
  50. "remaining",
  51. "cost_per_kg",
  52. "nozzle_temp_min",
  53. "nozzle_temp_max",
  54. "last_used",
  55. "note",
  56. "storage_location",
  57. "category",
  58. "low_stock_threshold_pct",
  59. "material_number",
  60. # Supplier assignments (#2988): `suppliers` is the "; "-joined names of
  61. # all assigned suppliers, `purchase_supplier` the one this spool was
  62. # actually bought from (or empty). Import matches names against the
  63. # existing supplier list — trimmed, case-insensitive — and NEVER creates
  64. # suppliers; an unknown name is a dry-run warning, not a row error, and
  65. # that assignment is dropped. Article number and quoted price stay out of
  66. # the CSV: they belong to the assignment, not the spool, and would break
  67. # the round-trip.
  68. "suppliers",
  69. "purchase_supplier",
  70. ]
  71. # Upload ceiling for the import endpoint. A spool inventory CSV is a few KB
  72. # even with thousands of rows; 5 MB is a generous cap that still refuses an
  73. # OOM-sized body before it's read into memory.
  74. MAX_CSV_IMPORT_BYTES = 5 * 1024 * 1024
  75. # Spreadsheet formula-injection guard. A cell whose first character is one of
  76. # these is treated as a formula by Excel / LibreOffice / Sheets; we prefix it
  77. # with a single quote on export so the value renders as literal text.
  78. _FORMULA_INJECTION_PREFIXES = ("=", "+", "-", "@", "\t", "\r")
  79. # Columns whose CSV cell must be coerced to a number before SpoolCreate sees it.
  80. # DictReader hands us strings; SpoolCreate wants int/float. Empty cell → omit
  81. # the field (falls back to the schema default / None).
  82. _INT_COLUMNS = {"label_weight", "nozzle_temp_min", "nozzle_temp_max", "low_stock_threshold_pct"}
  83. _FLOAT_COLUMNS = {"cost_per_kg", "weight_used"}
  84. # label_weight default, pulled from the schema so the weight_used bounds check
  85. # stays in sync if the schema default ever changes.
  86. _DEFAULT_LABEL_WEIGHT = SpoolCreate.model_fields["label_weight"].default
  87. class ImportRowResult(BaseModel):
  88. """Per-row outcome of a parse+validate pass.
  89. `spool` carries the validated, SpoolCreate-shaped dict for `valid` rows so
  90. the route can persist without re-parsing. `resolved_color` flags rows whose
  91. rgba/extra_colors/effect_type were filled in from the Color Catalog rather
  92. than supplied in the CSV — surfaced in the preview so the user knows a
  93. colour was inferred.
  94. """
  95. row_number: int # 1-based data row (header is not counted)
  96. status: str # "valid" | "error" | "skipped"
  97. # The id the spool is created with (#3520); None gets the next free one.
  98. keeps_id: int | None = None
  99. reason: str | None = None
  100. material: str | None = None
  101. brand: str | None = None
  102. color_name: str | None = None
  103. rgba: str | None = None
  104. resolved_color: bool = False
  105. # True when the colour was resolved from a catalog entry of a DIFFERENT
  106. # material (no exact material match existed). Surfaced so the preview can
  107. # warn the user the colour came from another material's variant.
  108. cross_material_color: bool = False
  109. # True when an active spool with the same material+brand+color_name already
  110. # exists. Informational only — the import still creates the row (there's no
  111. # unique constraint); the preview warns so a double-click / re-upload of the
  112. # same CSV doesn't silently duplicate the inventory.
  113. duplicate_of_existing: bool = False
  114. spool: dict | None = None
  115. # Resolved supplier assignments (#2988): ids matched by name from the
  116. # `suppliers` / `purchase_supplier` columns. Unknown names are dropped
  117. # with a preview warning — the import never creates suppliers.
  118. supplier_ids: list[int] = []
  119. purchase_supplier_id: int | None = None
  120. class ImportPreview(BaseModel):
  121. """Result of a dry-run (or the pre-write pass of a real import)."""
  122. columns: list[str]
  123. total: int
  124. valid_count: int
  125. error_count: int
  126. skipped_count: int
  127. rows: list[ImportRowResult]
  128. warnings: list[str] = []
  129. class ImportResult(BaseModel):
  130. """Summary returned after a real (non-dry-run) import."""
  131. created: int
  132. skipped: int
  133. errors: int
  134. error_rows: list[ImportRowResult] = []
  135. def _normalize_header(name: str) -> str:
  136. """Map a CSV header cell to a canonical field name.
  137. Case- and space-tolerant: "Color Name", "color-name", " COLOR_NAME "
  138. all collapse to "color_name".
  139. """
  140. return name.strip().lower().replace(" ", "_").replace("-", "_")
  141. def _normalize_rgba(value: str) -> str | None:
  142. """Coerce a user-supplied colour cell to 8-char RRGGBBAA hex, or None.
  143. Accepts an optional leading `#` and a 6-char RRGGBB form (alpha defaults to
  144. `ff`). Returns None if the value isn't valid hex of length 6 or 8 — the
  145. caller turns that into a row error so it isn't silently dropped.
  146. """
  147. raw = value.strip().lstrip("#")
  148. if len(raw) not in (6, 8):
  149. return None
  150. try:
  151. int(raw, 16)
  152. except ValueError:
  153. return None
  154. if len(raw) == 6:
  155. raw += "ff"
  156. return raw.lower()
  157. def _parse_datetime(value: str) -> datetime | None:
  158. """Parse an ISO-8601 timestamp, or None if it isn't valid.
  159. Accepts what `datetime.isoformat()` produces (what export writes) plus a
  160. trailing 'Z' for UTC, which `fromisoformat` rejects before Python 3.11.
  161. Returned as naive UTC, as the column holds it: SQLite would keep the wall
  162. time of a value with an offset and drop the offset. A value without one is
  163. taken as UTC, which is how export writes it.
  164. """
  165. raw = value.strip()
  166. if not raw:
  167. return None
  168. if raw.endswith("Z"):
  169. raw = raw[:-1] + "+00:00"
  170. try:
  171. parsed = datetime.fromisoformat(raw)
  172. except ValueError:
  173. return None
  174. if parsed.tzinfo is not None:
  175. parsed = parsed.astimezone(timezone.utc).replace(tzinfo=None)
  176. return parsed
  177. async def _load_color_catalog(db: AsyncSession) -> list[ColorCatalogEntry]:
  178. """Load the whole Color Catalog once so per-row resolution is in-memory.
  179. A CSV can hold hundreds of rows; resolving each with its own SELECT would
  180. be an N+1 against a small, rarely-changing table. We pull it once here and
  181. let `_resolve_color` match against the list.
  182. """
  183. result = await db.execute(select(ColorCatalogEntry))
  184. return list(result.scalars().all())
  185. def _spool_key(material: str | None, brand: str | None, color_name: str | None) -> tuple[str, str, str]:
  186. """Case/space-insensitive identity used for the duplicate soft-warn."""
  187. return (
  188. (material or "").strip().lower(),
  189. (brand or "").strip().lower(),
  190. (color_name or "").strip().lower(),
  191. )
  192. async def _load_supplier_map(db: AsyncSession) -> dict[str, int]:
  193. """Existing suppliers keyed by ``Supplier.name_key`` for CSV matching.
  194. Import resolves the `suppliers` / `purchase_supplier` columns against
  195. this map and never creates suppliers — the master list is curated in the
  196. UI, and a typo in a CSV must not silently mint a new supplier. The key is
  197. unambiguous because it is the same column the unique index is on, so two
  198. names that land on one entry here cannot both exist as rows.
  199. """
  200. result = await db.execute(select(Supplier.id, Supplier.name_key))
  201. return {name_key: supplier_id for supplier_id, name_key in result.all()}
  202. async def _load_existing_spool_keys(db: AsyncSession) -> set[tuple[str, str, str]]:
  203. """Load material+brand+color_name keys of active spools for the dup warning.
  204. Spool has no unique constraint, so a double-click or re-upload of the same
  205. CSV would silently duplicate the inventory. We pull the active spools' keys
  206. once and let the preview flag matching rows — informational only, the import
  207. still creates them.
  208. """
  209. result = await db.execute(select(Spool.material, Spool.brand, Spool.color_name).where(Spool.archived_at.is_(None)))
  210. return {_spool_key(m, b, c) for m, b, c in result.all()}
  211. def _resolve_color(
  212. catalog: list[ColorCatalogEntry], brand: str | None, color_name: str | None, material: str | None
  213. ) -> tuple[str, str | None, str | None, bool] | None:
  214. """Match brand + color_name against the preloaded catalog (case-insensitive).
  215. Returns (rgba, extra_colors, effect_type, cross_material) on a match, else
  216. None. Prefers an entry whose material matches the row; a catalog entry with
  217. a NULL material is the project's "matches any material" convention and counts
  218. as an exact match too. Only when neither exists does it fall back to another
  219. material's entry and set cross_material=True so the caller can warn that the
  220. colour came from a different material's variant.
  221. """
  222. if not brand or not color_name:
  223. return None
  224. brand_l = brand.strip().lower()
  225. name_l = color_name.strip().lower()
  226. material_l = material.strip().lower() if material else None
  227. matches = [
  228. entry
  229. for entry in catalog
  230. if entry.hex_color and entry.manufacturer.lower() == brand_l and entry.color_name.lower() == name_l
  231. ]
  232. if not matches:
  233. return None
  234. exact = next(
  235. (e for e in matches if e.material is None or (material_l and e.material.lower() == material_l)),
  236. None,
  237. )
  238. row = exact or matches[0]
  239. cross_material = exact is None
  240. rgba = _normalize_rgba(row.hex_color)
  241. if rgba is None:
  242. return None
  243. return rgba, row.extra_colors, row.effect_type, cross_material
  244. def _readable_validation_error(exc: ValidationError) -> str:
  245. """Flatten a Pydantic ValidationError into one short, user-facing line."""
  246. parts = []
  247. for err in exc.errors():
  248. loc = ".".join(str(p) for p in err.get("loc", ())) or "value"
  249. parts.append(f"{loc}: {err.get('msg', 'invalid')}")
  250. return "; ".join(parts)
  251. def _empty_preview(warnings: list[str]) -> ImportPreview:
  252. """A preview with no rows — used for the early-exit cases (bad/empty file)."""
  253. return ImportPreview(
  254. columns=CSV_COLUMNS,
  255. total=0,
  256. valid_count=0,
  257. error_count=0,
  258. skipped_count=0,
  259. rows=[],
  260. warnings=warnings,
  261. )
  262. async def parse_and_validate(raw_bytes: bytes, db: AsyncSession) -> ImportPreview:
  263. """Parse a CSV blob, validate + colour-resolve each row. Never writes.
  264. Decodes UTF-8 (BOM tolerant), reads with DictReader against the fixed
  265. schema, and classifies each row as valid / error / skipped. Valid rows
  266. carry a SpoolCreate-shaped `spool` dict ready to persist.
  267. """
  268. warnings: list[str] = []
  269. try:
  270. text = raw_bytes.decode("utf-8-sig")
  271. except UnicodeDecodeError:
  272. return _empty_preview(["File is not valid UTF-8 text."])
  273. reader = csv.reader(io.StringIO(text))
  274. try:
  275. header = next(reader)
  276. except StopIteration:
  277. return _empty_preview(["CSV is empty."])
  278. norm_header = [_normalize_header(h) for h in header]
  279. known = set(CSV_COLUMNS)
  280. unknown = [h for h in norm_header if h and h not in known]
  281. if unknown:
  282. warnings.append(f"Ignoring unknown columns: {', '.join(unknown)}")
  283. # Map canonical field name → column index in this file (first occurrence).
  284. col_index: dict[str, int] = {}
  285. for idx, h in enumerate(norm_header):
  286. if h in known and h not in col_index:
  287. col_index[h] = idx
  288. if "material" not in col_index:
  289. return _empty_preview(warnings + ["Required column 'material' is missing from the header."])
  290. # Pull the catalog and the existing-spool keys once; per-row colour
  291. # resolution and the duplicate soft-warn both match in memory rather than
  292. # issuing a SELECT per row.
  293. catalog = await _load_color_catalog(db)
  294. existing_keys = await _load_existing_spool_keys(db)
  295. supplier_map = await _load_supplier_map(db)
  296. unknown_suppliers: set[str] = set()
  297. def cell(row: list[str], field: str) -> str:
  298. idx = col_index.get(field)
  299. if idx is None or idx >= len(row):
  300. return ""
  301. # Strip whitespace, then undo any export-side formula-injection quoting
  302. # so export → import round-trips without accumulating a leading quote.
  303. return _desanitize_cell(row[idx].strip())
  304. rows: list[ImportRowResult] = []
  305. valid = error = skipped = 0
  306. for row_number, raw_row in enumerate(reader, start=1):
  307. # Fully blank row (no non-empty cell) → skip silently.
  308. if not any(c.strip() for c in raw_row):
  309. rows.append(ImportRowResult(row_number=row_number, status="skipped", reason="Empty row"))
  310. skipped += 1
  311. continue
  312. material = cell(raw_row, "material")
  313. brand = cell(raw_row, "brand") or None
  314. color_name = cell(raw_row, "color_name") or None
  315. if not material:
  316. rows.append(
  317. ImportRowResult(
  318. row_number=row_number,
  319. status="error",
  320. reason="material is required",
  321. brand=brand,
  322. color_name=color_name,
  323. )
  324. )
  325. error += 1
  326. continue
  327. data: dict = {"material": material}
  328. if brand:
  329. data["brand"] = brand
  330. if color_name:
  331. data["color_name"] = color_name
  332. row_error: str | None = None
  333. requested_id: int | None = None
  334. id_cell = cell(raw_row, "id")
  335. if id_cell:
  336. try:
  337. requested_id = int(id_cell)
  338. except ValueError:
  339. requested_id = 0
  340. if requested_id < 1:
  341. row_error = f"id must be a positive whole number (got '{id_cell}')"
  342. # Plain text passthrough columns.
  343. for field in (
  344. "subtype",
  345. "effect_type",
  346. "extra_colors",
  347. "note",
  348. "storage_location",
  349. "category",
  350. "material_number",
  351. ):
  352. value = cell(raw_row, field)
  353. if value:
  354. data[field] = value
  355. # Numeric columns: parse only if present, else leave to schema defaults.
  356. for field in _INT_COLUMNS:
  357. value = cell(raw_row, field)
  358. if value:
  359. try:
  360. data[field] = int(value)
  361. except ValueError:
  362. row_error = f"{field} must be a whole number (got '{value}')"
  363. break
  364. if row_error is None:
  365. for field in _FLOAT_COLUMNS:
  366. value = cell(raw_row, field)
  367. if value:
  368. try:
  369. data[field] = float(value)
  370. except ValueError:
  371. row_error = f"{field} must be a number (got '{value}')"
  372. break
  373. # Bounds check: weight_used must be within [0, label_weight]. The schema
  374. # accepts any float, so a negative or over-full value would otherwise be
  375. # imported silently. label_weight falls back to the schema default when
  376. # the CSV omits it.
  377. if row_error is None and "weight_used" in data:
  378. used = data["weight_used"]
  379. label = data.get("label_weight", _DEFAULT_LABEL_WEIGHT)
  380. if used < 0:
  381. row_error = f"weight_used cannot be negative (got {used})"
  382. elif used > label:
  383. row_error = f"weight_used ({used}) exceeds label_weight ({label})"
  384. # `last_used` is an ORM-only timestamp (not on SpoolCreate); parse it
  385. # here and apply it to the validated dict after the SpoolCreate gate.
  386. last_used: datetime | None = None
  387. if row_error is None:
  388. last_used_cell = cell(raw_row, "last_used")
  389. if last_used_cell:
  390. last_used = _parse_datetime(last_used_cell)
  391. if last_used is None:
  392. row_error = f"last_used must be an ISO date/time (got '{last_used_cell}')"
  393. resolved_color = False
  394. cross_material_color = False
  395. if row_error is None:
  396. # Colour precedence: explicit rgba wins; else resolve brand+name
  397. # from the catalog; else leave blank.
  398. rgba_cell = cell(raw_row, "rgba")
  399. if rgba_cell:
  400. normalized = _normalize_rgba(rgba_cell)
  401. if normalized is None:
  402. row_error = f"rgba must be 6- or 8-char hex (got '{rgba_cell}')"
  403. else:
  404. data["rgba"] = normalized
  405. else:
  406. resolved = _resolve_color(catalog, brand, color_name, material)
  407. if resolved is not None:
  408. rgba_val, extra_val, effect_val, cross_material_color = resolved
  409. data["rgba"] = rgba_val
  410. # CSV-supplied extra_colors/effect_type take precedence over
  411. # the catalog's; only fill from catalog when absent.
  412. if extra_val and "extra_colors" not in data:
  413. data["extra_colors"] = extra_val
  414. if effect_val and "effect_type" not in data:
  415. data["effect_type"] = effect_val
  416. resolved_color = True
  417. if row_error is not None:
  418. rows.append(
  419. ImportRowResult(
  420. row_number=row_number,
  421. status="error",
  422. reason=row_error,
  423. material=material,
  424. brand=brand,
  425. color_name=color_name,
  426. )
  427. )
  428. error += 1
  429. continue
  430. # Final gate: SpoolCreate runs the same validators the form uses
  431. # (rgba pattern, extra_colors/effect_type normalisation, bounds).
  432. try:
  433. spool = SpoolCreate(**data)
  434. except ValidationError as exc:
  435. rows.append(
  436. ImportRowResult(
  437. row_number=row_number,
  438. status="error",
  439. reason=_readable_validation_error(exc),
  440. material=material,
  441. brand=brand,
  442. color_name=color_name,
  443. )
  444. )
  445. error += 1
  446. continue
  447. spool_data = spool.model_dump()
  448. if last_used is not None:
  449. # last_used isn't a SpoolCreate field; graft it onto the persisted
  450. # dict so the ORM object carries it.
  451. spool_data["last_used"] = last_used
  452. # Supplier assignments (#2988): match names against the existing list,
  453. # never create. Unknown names are warnings, not row errors — the row
  454. # imports and the unmatched assignment is dropped. The purchase source
  455. # counts as an assignment even when the `suppliers` cell omits it.
  456. supplier_ids: list[int] = []
  457. purchase_supplier_id: int | None = None
  458. names = [n.strip() for n in cell(raw_row, "suppliers").split(";") if n.strip()]
  459. purchase_name = cell(raw_row, "purchase_supplier").strip()
  460. if purchase_name and supplier_name_key(purchase_name) not in {supplier_name_key(n) for n in names}:
  461. names.append(purchase_name)
  462. for name in names:
  463. key = supplier_name_key(name)
  464. supplier_id = supplier_map.get(key)
  465. if supplier_id is None:
  466. # Once per name, not once per row: a 500-row export against an
  467. # empty supplier list is one missing supplier, not 500 problems.
  468. if key not in unknown_suppliers:
  469. unknown_suppliers.add(key)
  470. warnings.append(f"Unknown supplier '{name}' — assignments dropped")
  471. elif supplier_id not in supplier_ids:
  472. supplier_ids.append(supplier_id)
  473. if purchase_name:
  474. purchase_supplier_id = supplier_map.get(supplier_name_key(purchase_name))
  475. rows.append(
  476. ImportRowResult(
  477. row_number=row_number,
  478. status="valid",
  479. material=material,
  480. brand=brand,
  481. color_name=color_name,
  482. rgba=spool.rgba,
  483. resolved_color=resolved_color,
  484. cross_material_color=cross_material_color,
  485. duplicate_of_existing=_spool_key(material, brand, color_name) in existing_keys,
  486. keeps_id=requested_id,
  487. spool=spool_data,
  488. supplier_ids=supplier_ids,
  489. purchase_supplier_id=purchase_supplier_id,
  490. )
  491. )
  492. valid += 1
  493. await _decide_ids(db, rows, warnings)
  494. return ImportPreview(
  495. columns=CSV_COLUMNS,
  496. total=valid + error + skipped,
  497. valid_count=valid,
  498. error_count=error,
  499. skipped_count=skipped,
  500. rows=rows,
  501. warnings=warnings,
  502. )
  503. async def highest_spool_id_given_out(db: AsyncSession) -> int:
  504. """The highest spool id this install has ever handed out, deleted ones included.
  505. Spool ids are never reused (old print logs and usage records may still
  506. name one), so only an id above this is free to give an imported spool.
  507. """
  508. highest = (await db.execute(select(func.max(Spool.id)))).scalar() or 0
  509. if is_sqlite():
  510. seq = (await db.execute(text("SELECT seq FROM sqlite_sequence WHERE name = 'spool'"))).scalar()
  511. return max(highest, seq or 0)
  512. sequence = (await db.execute(text("SELECT pg_get_serial_sequence('spool', 'id')"))).scalar()
  513. if sequence:
  514. state = (await db.execute(text(f"SELECT last_value, is_called FROM {sequence}"))).first() # noqa: S608 # nosec B608 -- name from pg_get_serial_sequence
  515. if state is not None:
  516. last_value, is_called = state
  517. highest = max(highest, last_value if is_called else last_value - 1)
  518. return highest
  519. async def _decide_ids(db: AsyncSession, rows: list[ImportRowResult], warnings: list[str]) -> None:
  520. """Keep each valid row's id when this install never handed it out (#3520).
  521. On a fresh install every id qualifies. An id already given out here, or
  522. one the file names twice, would merge two spools' records, so that row
  523. gets a new id instead: one warning for all of them, not one per row.
  524. """
  525. highest = await highest_spool_id_given_out(db)
  526. taken: set[int] = set()
  527. renumbered = 0
  528. for row in rows:
  529. if row.keeps_id is None:
  530. continue
  531. if row.status != "valid" or row.keeps_id <= highest or row.keeps_id in taken:
  532. if row.status == "valid":
  533. renumbered += 1
  534. row.keeps_id = None
  535. continue
  536. taken.add(row.keeps_id)
  537. if renumbered:
  538. warnings.append(
  539. f"{renumbered} spool(s) get a new id: their id is already used on this install or appears twice in the file"
  540. )
  541. def serialize(spools: list[Spool]) -> bytes:
  542. """Render spools to CSV bytes using the fixed schema (export side).
  543. rgba is written without a leading `#`, matching the import-side
  544. normalisation, so export → import round-trips without transformation.
  545. `remaining` is derived (label_weight - weight_used) and `last_used` is
  546. written as ISO-8601; empty/None fields become empty cells.
  547. """
  548. output = io.StringIO()
  549. writer = csv.writer(output)
  550. writer.writerow(CSV_COLUMNS)
  551. for spool in spools:
  552. writer.writerow([_sanitize_cell(_cell_value(spool, col)) for col in CSV_COLUMNS])
  553. return output.getvalue().encode("utf-8")
  554. def _sanitize_cell(value: str) -> str:
  555. """Neutralise spreadsheet formula injection.
  556. A free-text field (note, color_name) starting with =, +, -, @, tab, or CR
  557. is evaluated as a formula by Excel/Sheets/LibreOffice when the CSV is
  558. opened. Prefixing with a single quote forces it to render as literal text.
  559. `_desanitize_cell` is the exact inverse, applied on import.
  560. """
  561. if value and value[0] in _FORMULA_INJECTION_PREFIXES:
  562. return "'" + value
  563. return value
  564. def _desanitize_cell(value: str) -> str:
  565. """Undo `_sanitize_cell` on import so the round-trip is lossless.
  566. Export prefixes formula-looking cells with a single quote; strip exactly
  567. that quote back off when the next character is one of the guarded prefixes,
  568. so `'=SUM(A1)` reads back as `=SUM(A1)` and the value doesn't accumulate a
  569. leading quote on every export→import cycle. A quote followed by anything
  570. else is left untouched — only the prefix `_sanitize_cell` could have added
  571. is removed.
  572. """
  573. if len(value) >= 2 and value[0] == "'" and value[1] in _FORMULA_INJECTION_PREFIXES:
  574. return value[1:]
  575. return value
  576. def _cell_value(spool: Spool, col: str) -> str:
  577. """Render one spool field for export. Handles the derived `remaining`
  578. column and ISO-formats `last_used`; everything else is str() of the value."""
  579. if col == "remaining":
  580. # Derived for display: label_weight - weight_used, clamped at 0.
  581. return str(max(0, round((spool.label_weight or 0) - (spool.weight_used or 0))))
  582. if col == "suppliers":
  583. # Derived (#2988): "; "-joined supplier names, no decoration — the
  584. # purchase source has its own column so import can match plain names.
  585. # The export query loads supplier_links explicitly.
  586. return "; ".join(link.supplier_name for link in spool.supplier_links)
  587. if col == "purchase_supplier":
  588. return next((link.supplier_name for link in spool.supplier_links if link.is_purchase_source), "")
  589. value = getattr(spool, col, None)
  590. if value is None:
  591. return ""
  592. if isinstance(value, datetime):
  593. return value.isoformat()
  594. # Whole-number floats (weight_used, cost_per_kg) export as ints — "300",
  595. # not "300.0" — for a cleaner, human-friendly CSV. import re-parses fine.
  596. if isinstance(value, float) and value.is_integer():
  597. return str(int(value))
  598. return str(value)