382 lines
13 KiB
Python
382 lines
13 KiB
Python
 
|
"""Turns one recipe sheet of the workbook template into a Recipe Bundle recipe."""
|
|||
|
|
|
|||
|
|
from __future__ import annotations
|
|||
|
|
|
|||
|
|
import hashlib
|
|||
|
|
import re
|
|||
|
|
import uuid
|
|||
|
|
from dataclasses import dataclass, field
|
|||
|
|
|
|||
|
|
from openpyxl.utils.cell import coordinate_from_string, column_index_from_string
|
|||
|
|
|
|||
|
|
from . import parse
|
|||
|
|
from .workbook import Sheet
|
|||
|
|
|
|||
|
|
SKIPPED_SHEETS = {"Leeres Rezept", "Spinat-Feta-Pizza"} # the template and its example recipe
|
|||
|
|
|
|||
|
|
# Stable IDs: re-running the converter yields the same Recipe IDs, so a re-import skips them.
|
|||
|
|
ID_NAMESPACE = uuid.UUID("6f1d3c3e-2b7a-4c55-9a51-0c6b8f2e9d10")
|
|||
|
|
|
|||
|
|
# Cells the template itself fills or that this module reads explicitly.
|
|||
|
|
TEMPLATE_LABELS = {"A1", "C1", "F1", "G1", "C2", "D2", "E2", "F2", "G2", "B5", "G6", "B8", "G10", "B11"}
|
|||
|
|
B12_BOILERPLATE = "Löschen Sie dieses Rezeptarbeitsblatt nicht."
|
|||
|
|
PLACEHOLDER_PREFIXES = ("Geben Sie", "Kopieren Sie dieses Arbeitsblatt", "Stichwort 1")
|
|||
|
|
NO_COURSE = {"Gang"}
|
|||
|
|
NUTRITION_LABELS = {"fett", "fette", "kohlenhydrate", "eiweiß", "ballaststoffe", "kalorien/ 100g", "kalorien gesamt"}
|
|||
|
|
FIRST_INGREDIENT_ROW = 3
|
|||
|
|
|
|||
|
|
|
|||
|
|
class Reason:
|
|||
|
|
"""Review Flag reasons, shown to the cook in the app."""
|
|||
|
|
|
|||
|
|
STRAY_TEXT = "Zusätzlicher Text aus der Tabelle wurde in die Notizen übernommen."
|
|||
|
|
NO_SERVINGS = "Portionen fehlen."
|
|||
|
|
UNKNOWN_UNIT = "Einheit nicht erkannt und in den Zutatennamen übernommen: {unit}"
|
|||
|
|
UNKNOWN_AMOUNT = "Menge nicht erkannt und als Hinweis übernommen: {amount}"
|
|||
|
|
MERGED_INSTRUCTIONS = "Anleitung war auf mehrere Zellen verteilt und wurde zusammengeführt."
|
|||
|
|
LONG_STEP = "Sehr langer Schritt – eventuell aufteilen."
|
|||
|
|
NAMELESS_LINES = "Zutatenzeilen ohne Zutat wurden verworfen: {values}"
|
|||
|
|
DUPLICATE_LIST = "Doppelte Zutatenliste wurde verworfen."
|
|||
|
|
ALTERNATIVE = "„ODER“-Alternative wurde als eigene Zutatengruppe übernommen."
|
|||
|
|
UNREADABLE_TIME = "Zeitangabe nicht erkannt und in die Notizen übernommen."
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
def is_recipe_sheet(sheet: Sheet) -> bool:
|
|||
|
|
return (
|
|||
|
|
sheet.title not in SKIPPED_SHEETS
|
|||
|
|
and parse.clean(sheet.cells.get("C2")) == "Menge"
|
|||
|
|
and parse.clean(sheet.cells.get("E2")) == "Zutat"
|
|||
|
|
)
|
|||
|
|
|
|||
|
|
|
|||
|
|
TEMPLATE_SHEET = "Leeres Rezept"
|
|||
|
|
|
|||
|
|
|
|||
|
|
def template_photo_hashes(sheets: list[Sheet]) -> set[str]:
|
|||
|
|
"""The template's stock picture, which was copied onto every sheet made from it."""
|
|||
|
|
return {hashlib.md5(p).hexdigest() for s in sheets if s.title == TEMPLATE_SHEET for p in s.photos}
|
|||
|
|
|
|||
|
|
|
|||
|
|
def real_photos(sheet: Sheet, placeholders: set[str]) -> list[bytes]:
|
|||
|
|
return [p for p in sheet.photos if hashlib.md5(p).hexdigest() not in placeholders]
|
|||
|
|
|
|||
|
|
|
|||
|
|
def recipe_id(sheet_title: str) -> str:
|
|||
|
|
return str(uuid.uuid5(ID_NAMESPACE, sheet_title))
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _rc(coord: str) -> tuple[int, int]:
|
|||
|
|
col, row = coordinate_from_string(coord)
|
|||
|
|
return row, column_index_from_string(col)
|
|||
|
|
|
|||
|
|
|
|||
|
|
@dataclass
|
|||
|
|
class _Builder:
|
|||
|
|
sheet: Sheet
|
|||
|
|
notes: list[str] = field(default_factory=list)
|
|||
|
|
sources: list[str] = field(default_factory=list)
|
|||
|
|
reasons: list[str] = field(default_factory=list)
|
|||
|
|
used: set[str] = field(default_factory=set)
|
|||
|
|
|
|||
|
|
def get(self, coord: str):
|
|||
|
|
self.used.add(coord)
|
|||
|
|
return self.sheet.cells.get(coord)
|
|||
|
|
|
|||
|
|
def text(self, coord: str) -> str | None:
|
|||
|
|
value = parse.clean(self.get(coord))
|
|||
|
|
if value and value.startswith(PLACEHOLDER_PREFIXES):
|
|||
|
|
return None
|
|||
|
|
return value
|
|||
|
|
|
|||
|
|
def flag(self, reason: str) -> None:
|
|||
|
|
if reason not in self.reasons:
|
|||
|
|
self.reasons.append(reason)
|
|||
|
|
|
|||
|
|
def add_note_text(self, text: str, stray: bool) -> None:
|
|||
|
|
urls, rest = parse.extract_urls(text)
|
|||
|
|
for url in urls:
|
|||
|
|
if url not in self.sources:
|
|||
|
|
self.sources.append(url)
|
|||
|
|
if rest:
|
|||
|
|
self.notes.append(rest)
|
|||
|
|
if stray:
|
|||
|
|
self.flag(Reason.STRAY_TEXT)
|
|||
|
|
|
|||
|
|
|
|||
|
|
def build_recipe(sheet: Sheet) -> dict:
|
|||
|
|
b = _Builder(sheet)
|
|||
|
|
name = _name(b.text("C1"), sheet.title)
|
|||
|
|
course = b.text("F1")
|
|||
|
|
if course in NO_COURSE:
|
|||
|
|
course = None
|
|||
|
|
|
|||
|
|
comment_b12 = b.text("B12")
|
|||
|
|
if comment_b12 and comment_b12 != B12_BOILERPLATE:
|
|||
|
|
b.add_note_text(comment_b12, stray=False)
|
|||
|
|
|
|||
|
|
tags = _tags(b.text("B9"))
|
|||
|
|
prep = _minutes(b, "G3", "Vorbereitungszeit")
|
|||
|
|
cook = _minutes(b, "G7", "Kochzeit")
|
|||
|
|
servings = _servings(b)
|
|||
|
|
calories = _calories(b)
|
|||
|
|
groups = _ingredient_groups(b)
|
|||
|
|
sections = _steps(b)
|
|||
|
|
_stray_cells(b)
|
|||
|
|
_stray_comments(b)
|
|||
|
|
|
|||
|
|
return {
|
|||
|
|
"id": recipe_id(sheet.title),
|
|||
|
|
"name": name,
|
|||
|
|
"course": course,
|
|||
|
|
"tags": tags,
|
|||
|
|
"servings": servings,
|
|||
|
|
"prepMinutes": prep,
|
|||
|
|
"cookMinutes": cook,
|
|||
|
|
"calories": calories,
|
|||
|
|
"ingredientGroups": groups,
|
|||
|
|
"stepSections": sections,
|
|||
|
|
"photos": [],
|
|||
|
|
"sources": b.sources,
|
|||
|
|
"notes": "\n\n".join(b.notes) or None,
|
|||
|
|
"favourite": False,
|
|||
|
|
"reviewReasons": b.reasons,
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _name(title_cell: str | None, sheet_title: str) -> str:
|
|||
|
|
"""Sheet tabs are cut at 31 characters, title cells are sometimes abbreviated: keep the longer one."""
|
|||
|
|
if not title_cell or title_cell in SKIPPED_SHEETS:
|
|||
|
|
return sheet_title.strip()
|
|||
|
|
return max(title_cell, sheet_title.strip(), key=len)
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _tags(text: str | None) -> list[str]:
|
|||
|
|
tags, seen = [], set()
|
|||
|
|
for tag in (text or "").replace(";", ",").split(","):
|
|||
|
|
tag = tag.strip()
|
|||
|
|
if tag and tag.casefold() not in seen:
|
|||
|
|
seen.add(tag.casefold())
|
|||
|
|
tags.append(tag)
|
|||
|
|
return tags
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _minutes(b: _Builder, coord: str, label: str) -> int | None:
|
|||
|
|
value = parse.parse_minutes(b.get(coord))
|
|||
|
|
if isinstance(value, str):
|
|||
|
|
if not value.startswith(PLACEHOLDER_PREFIXES):
|
|||
|
|
b.notes.append(f"{label}: {value}")
|
|||
|
|
b.flag(Reason.UNREADABLE_TIME)
|
|||
|
|
return None
|
|||
|
|
return value
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _servings(b: _Builder):
|
|||
|
|
raw = b.get("G11")
|
|||
|
|
if parse.clean(raw) and str(raw).startswith(PLACEHOLDER_PREFIXES):
|
|||
|
|
raw = None
|
|||
|
|
servings = parse.parse_servings(raw)
|
|||
|
|
if servings.remark:
|
|||
|
|
b.add_note_text(f"Ergebnis: {servings.remark}", stray=True)
|
|||
|
|
if servings.value is None:
|
|||
|
|
b.flag(Reason.NO_SERVINGS)
|
|||
|
|
return servings.value
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _calories(b: _Builder) -> dict | None:
|
|||
|
|
raw = b.get("B6")
|
|||
|
|
comment = b.sheet.comments.get("B6")
|
|||
|
|
b.used.add("comment:B6")
|
|||
|
|
if not parse.is_number(raw):
|
|||
|
|
text = parse.clean(raw)
|
|||
|
|
if text and not text.startswith(PLACEHOLDER_PREFIXES):
|
|||
|
|
b.add_note_text(f"Kalorien: {text}", stray=True)
|
|||
|
|
return None
|
|||
|
|
kcal = float(str(raw).replace(",", "."))
|
|||
|
|
if kcal <= 0:
|
|||
|
|
return None
|
|||
|
|
if comment and "pro portion" in comment.lower():
|
|||
|
|
basis = "perServing"
|
|||
|
|
else:
|
|||
|
|
# Guessed, but not flagged: the threshold matched every value in the workbook on review.
|
|||
|
|
basis = parse.calories_basis(kcal)
|
|||
|
|
if comment:
|
|||
|
|
b.add_note_text(f"Kalorien: {comment}", stray=True)
|
|||
|
|
return {"kcal": int(round(kcal)), "basis": basis}
|
|||
|
|
|
|||
|
|
|
|||
|
|
# --- Ingredients -------------------------------------------------------------
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _row_cells(b: _Builder, row: int):
|
|||
|
|
return b.sheet.cells.get(f"C{row}"), b.sheet.cells.get(f"D{row}"), b.sheet.cells.get(f"E{row}")
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _ingredient_rows(b: _Builder) -> list[int]:
|
|||
|
|
rows = {
|
|||
|
|
_rc(coord)[0]
|
|||
|
|
for coord in b.sheet.cells
|
|||
|
|
if coord[0] in "CDE" and _rc(coord)[0] >= FIRST_INGREDIENT_ROW and _rc(coord)[1] in (3, 4, 5)
|
|||
|
|
}
|
|||
|
|
return sorted(rows)
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _blocks(rows: list[int]) -> list[list[int]]:
|
|||
|
|
"""Runs of consecutive rows; a blank row in the sheet separates Ingredient Groups."""
|
|||
|
|
blocks: list[list[int]] = []
|
|||
|
|
for row in rows:
|
|||
|
|
if blocks and row == blocks[-1][-1] + 1:
|
|||
|
|
blocks[-1].append(row)
|
|||
|
|
else:
|
|||
|
|
blocks.append([row])
|
|||
|
|
return blocks
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _group_marker(comment: str | None) -> str | None:
|
|||
|
|
"""A cell comment such as "Ab hier: Zutaten für die Sauce" names the group starting at that row."""
|
|||
|
|
if not comment or "zutaten" not in comment.lower():
|
|||
|
|
return None
|
|||
|
|
m = re.search(r"zutaten\s+für\s+(?:die|den|das)?\s*(.+)", comment, re.I | re.S)
|
|||
|
|
name = (m.group(1) if m else comment).strip().rstrip(":").strip()
|
|||
|
|
return name[:1].upper() + name[1:]
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _ingredient_groups(b: _Builder) -> list[dict]:
|
|||
|
|
groups: list[dict] = []
|
|||
|
|
seen_names: set[str] = set()
|
|||
|
|
pending_name: str | None = None
|
|||
|
|
dropped: list[str] = []
|
|||
|
|
|
|||
|
|
for block in _blocks(_ingredient_rows(b)):
|
|||
|
|
for row in block:
|
|||
|
|
b.used.update({f"C{row}", f"D{row}", f"E{row}"})
|
|||
|
|
|
|||
|
|
block_rows = [(row, *_row_cells(b, row)) for row in block]
|
|||
|
|
|
|||
|
|
# Nutrition tables pasted into the ingredient columns belong in the Notes.
|
|||
|
|
if any((parse.clean(d) or "").lower() in NUTRITION_LABELS for _, _, d, _ in block_rows):
|
|||
|
|
lines = [" ".join(str(v).strip() for v in (c, d, e) if parse.clean(v)) for _, c, d, e in block_rows]
|
|||
|
|
b.add_note_text("\n".join(lines), stray=True)
|
|||
|
|
continue
|
|||
|
|
|
|||
|
|
named = [parse.clean(e).casefold() for _, _, _, e in block_rows if parse.clean(e)]
|
|||
|
|
if len(named) >= 3 and sum(n in seen_names for n in named) / len(named) >= 0.8:
|
|||
|
|
b.flag(Reason.DUPLICATE_LIST)
|
|||
|
|
continue
|
|||
|
|
|
|||
|
|
current: dict | None = None
|
|||
|
|
for row, c, d, e in block_rows:
|
|||
|
|
if any((parse.clean(v) or "").upper() == "ODER" for v in (c, e)) and not parse.clean(d):
|
|||
|
|
pending_name = "Alternative"
|
|||
|
|
b.flag(Reason.ALTERNATIVE)
|
|||
|
|
current = None
|
|||
|
|
continue
|
|||
|
|
if not parse.clean(e):
|
|||
|
|
if all(v is None or parse.is_number(v) for v in (c, d)):
|
|||
|
|
continue # leftover numbers from rescaling a recipe
|
|||
|
|
if parse.clean(c) or parse.clean(d):
|
|||
|
|
dropped.append(" ".join(str(v).strip() for v in (c, d) if parse.clean(v)))
|
|||
|
|
continue
|
|||
|
|
|
|||
|
|
marker = None
|
|||
|
|
for col in "CDE":
|
|||
|
|
marker = marker or _group_marker(b.sheet.comments.get(f"{col}{row}"))
|
|||
|
|
if marker:
|
|||
|
|
pending_name = marker
|
|||
|
|
current = None
|
|||
|
|
|
|||
|
|
if current is None:
|
|||
|
|
current = {"name": pending_name, "lines": []}
|
|||
|
|
pending_name = None
|
|||
|
|
groups.append(current)
|
|||
|
|
current["lines"].append(_line(b, row, c, d, e))
|
|||
|
|
seen_names.add(parse.clean(e).casefold())
|
|||
|
|
|
|||
|
|
if dropped:
|
|||
|
|
b.flag(Reason.NAMELESS_LINES.format(values=", ".join(dropped)))
|
|||
|
|
return [g for g in groups if g["lines"]]
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _line(b: _Builder, row: int, c, d, e) -> dict:
|
|||
|
|
name = parse.clean(e)
|
|||
|
|
remarks: list[str] = []
|
|||
|
|
amount_text = parse.clean(c)
|
|||
|
|
if amount_text and amount_text.startswith("(") and name.endswith(")"):
|
|||
|
|
# "(2 | Teelöffel | Oregano)": the whole line is in parentheses, i.e. optional.
|
|||
|
|
c, name = amount_text[1:], name[:-1].strip()
|
|||
|
|
remarks.append("optional")
|
|||
|
|
amount = parse.parse_amount(c)
|
|||
|
|
unit = parse.parse_unit(d)
|
|||
|
|
unit_text = parse.clean(d)
|
|||
|
|
|
|||
|
|
if amount is None:
|
|||
|
|
b.flag(Reason.UNKNOWN_AMOUNT.format(amount=parse.clean(c)))
|
|||
|
|
remarks.append(parse.clean(c))
|
|||
|
|
amount = (None, None)
|
|||
|
|
|
|||
|
|
if unit_text and unit is None:
|
|||
|
|
combined = parse.split_amount_unit(unit_text)
|
|||
|
|
if combined and amount == (None, None):
|
|||
|
|
amount, unit = (combined[0], None), combined[1]
|
|||
|
|
else:
|
|||
|
|
b.flag(Reason.UNKNOWN_UNIT.format(unit=unit_text))
|
|||
|
|
name = f"{unit_text} {name}"
|
|||
|
|
|
|||
|
|
for col in "CDE":
|
|||
|
|
comment = b.sheet.comments.get(f"{col}{row}")
|
|||
|
|
b.used.add(f"comment:{col}{row}")
|
|||
|
|
if comment and not _group_marker(comment):
|
|||
|
|
remarks.append(comment)
|
|||
|
|
|
|||
|
|
return {
|
|||
|
|
"amount": amount[0],
|
|||
|
|
"amountMax": amount[1],
|
|||
|
|
"unit": unit,
|
|||
|
|
"name": name,
|
|||
|
|
"remark": "; ".join(remarks) or None,
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
# --- Steps -------------------------------------------------------------------
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _steps(b: _Builder) -> list[dict]:
|
|||
|
|
instruction_cells = sorted((c for c in b.sheet.cells if c.startswith("F") and c not in ("F1", "F2")), key=_rc)
|
|||
|
|
texts = []
|
|||
|
|
for coord in instruction_cells:
|
|||
|
|
text = b.text(coord)
|
|||
|
|
if text:
|
|||
|
|
texts.append(text)
|
|||
|
|
b.used.update(instruction_cells)
|
|||
|
|
if len(texts) > 1:
|
|||
|
|
b.flag(Reason.MERGED_INSTRUCTIONS)
|
|||
|
|
|
|||
|
|
sections = parse.split_steps("\n\n".join(texts))
|
|||
|
|
if any(len(step) > parse.LONG_STEP_CHARS for s in sections for step in s.steps):
|
|||
|
|
b.flag(Reason.LONG_STEP)
|
|||
|
|
return [{"title": s.title, "steps": s.steps} for s in sections]
|
|||
|
|
|
|||
|
|
|
|||
|
|
# --- Everything else -----------------------------------------------------------
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _stray_cells(b: _Builder) -> None:
|
|||
|
|
rows: dict[int, list[tuple[int, str]]] = {}
|
|||
|
|
for coord, value in b.sheet.cells.items():
|
|||
|
|
if coord in b.used or coord in TEMPLATE_LABELS:
|
|||
|
|
continue
|
|||
|
|
text = parse.clean(value)
|
|||
|
|
if not text or text == B12_BOILERPLATE or text.startswith(PLACEHOLDER_PREFIXES):
|
|||
|
|
continue
|
|||
|
|
row, col = _rc(coord)
|
|||
|
|
rows.setdefault(row, []).append((col, text))
|
|||
|
|
for row in sorted(rows):
|
|||
|
|
line = " ".join(text for _, text in sorted(rows[row]))
|
|||
|
|
b.add_note_text(line, stray=True)
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _stray_comments(b: _Builder) -> None:
|
|||
|
|
for coord, comment in b.sheet.comments.items():
|
|||
|
|
if f"comment:{coord}" in b.used or _group_marker(comment):
|
|||
|
|
continue
|
|||
|
|
b.add_note_text(comment, stray=True)
|