106 lines
3.5 KiB
Python
106 lines
3.5 KiB
Python
"""Excel: отчёт «Потребление корма» (3 листа)."""
|
|
|
|
from __future__ import annotations
|
|
|
|
import io
|
|
|
|
from flask import Flask
|
|
from openpyxl import Workbook
|
|
from openpyxl.styles import Font
|
|
from openpyxl.utils import get_column_letter
|
|
|
|
from app.services.feed_accounting.consumption_report_builder import build_consumption_report
|
|
|
|
|
|
def _header_font() -> Font:
|
|
return Font(bold=True)
|
|
|
|
|
|
def build_consumption_xlsx(app: Flask, date_from: str, date_to: str) -> bytes:
|
|
report = build_consumption_report(app, date_from, date_to)
|
|
wb = Workbook()
|
|
|
|
ws_sum = wb.active
|
|
ws_sum.title = "Сводка"
|
|
ws_sum.append([f"Потребление корма · {date_from} — {date_to}"])
|
|
ws_sum.append([])
|
|
sum_headers = [
|
|
"Компонент",
|
|
"Осталось, кг",
|
|
"Расход за период (факт), кг",
|
|
"Расход /24ч (план), кг",
|
|
"Осталось дней",
|
|
"Поступление, кг",
|
|
"Всего на складе, кг",
|
|
]
|
|
ws_sum.append(sum_headers)
|
|
header_row = ws_sum.max_row
|
|
for col in range(1, len(sum_headers) + 1):
|
|
ws_sum.cell(row=header_row, column=col).font = _header_font()
|
|
for row in report.summary:
|
|
ws_sum.append(
|
|
[
|
|
row["name"],
|
|
row["remaining_kg"],
|
|
row["consumed_kg"],
|
|
row["planned_per_day_kg"] or "",
|
|
row["days_left"] if row["days_left"] is not None else "",
|
|
row["inflow_kg"],
|
|
row["total_kg"],
|
|
]
|
|
)
|
|
for col in range(1, len(sum_headers) + 1):
|
|
ws_sum.column_dimensions[get_column_letter(col)].width = 18
|
|
|
|
ws_det = wb.create_sheet("Детализация")
|
|
det_headers = [
|
|
"Дата/время начала",
|
|
"Дата/время окончания",
|
|
"Рецепт",
|
|
"Кормораздатчик",
|
|
"Компонент",
|
|
"Целевой вес, кг",
|
|
"Фактический вес, кг",
|
|
"Перегруз, кг",
|
|
"Порядок загрузки",
|
|
]
|
|
ws_det.append(det_headers)
|
|
for col in range(1, len(det_headers) + 1):
|
|
ws_det.cell(row=1, column=col).font = _header_font()
|
|
for row in report.detail:
|
|
ws_det.append(
|
|
[
|
|
row["start"],
|
|
row["end"],
|
|
row["recipe"],
|
|
row["dispenser"],
|
|
row["component"],
|
|
row["target_kg"],
|
|
row["actual_kg"],
|
|
row["overload_kg"],
|
|
row["loading_order"],
|
|
]
|
|
)
|
|
for col in range(1, len(det_headers) + 1):
|
|
ws_det.column_dimensions[get_column_letter(col)].width = 16
|
|
|
|
ws_daily = wb.create_sheet("Расход по дням")
|
|
day_headers = ["Компонент"] + [d.strftime("%d.%m.%Y") for d in report.days] + ["Итого за период"]
|
|
ws_daily.append(day_headers)
|
|
for col in range(1, len(day_headers) + 1):
|
|
ws_daily.cell(row=1, column=col).font = _header_font()
|
|
for row in report.daily:
|
|
values = [row["name"]]
|
|
for d in report.days:
|
|
v = row["days"].get(d, 0)
|
|
values.append(v if v else 0)
|
|
values.append(row["total_kg"])
|
|
ws_daily.append(values)
|
|
ws_daily.column_dimensions["A"].width = 22
|
|
for col in range(2, len(day_headers) + 1):
|
|
ws_daily.column_dimensions[get_column_letter(col)].width = 11
|
|
|
|
buf = io.BytesIO()
|
|
wb.save(buf)
|
|
return buf.getvalue()
|