470 lines
20 KiB
Python
470 lines
20 KiB
Python
from datetime import date
|
||
from math import ceil
|
||
|
||
from fastapi import APIRouter, Depends, Query
|
||
from fastapi.responses import Response
|
||
from sqlalchemy import select
|
||
from sqlalchemy.orm import Session, selectinload
|
||
|
||
from app.database import get_db
|
||
from app.deps import require_roles
|
||
from app.models import (
|
||
Personnel,
|
||
Product,
|
||
ProductionReport,
|
||
ProductionReportAllocation,
|
||
ProductionReportItem,
|
||
ReportStatus,
|
||
Role,
|
||
WorkSession,
|
||
)
|
||
from app.schemas import DashboardRow, PageResponse
|
||
from app.services.cleaning import is_cleaning_item, is_cleaning_product
|
||
from app.services.common import as_float, round1, round2
|
||
from app.services.continuous_die import is_continuous_die_item, is_continuous_die_product
|
||
from app.services.excel_export import export_dashboard_rows
|
||
from app.services.metrics import shift_distribution_text
|
||
from app.services.misc_work import is_misc_item, is_misc_product
|
||
from app.services.multi_person import is_multi_person_item
|
||
from app.services.report_lifecycle import purge_expired_voided_reports
|
||
from app.services.serializers import report_correction_maps
|
||
|
||
router = APIRouter(prefix="/api/dashboard", tags=["dashboard"])
|
||
|
||
|
||
def _product_for_item(
|
||
db: Session,
|
||
cache: dict[tuple[str, str, str, str, str], Product | None],
|
||
item: ProductionReportItem,
|
||
) -> Product | None:
|
||
key = (
|
||
item.attendance_point_name,
|
||
item.project_no,
|
||
item.product_name,
|
||
item.process_name or "",
|
||
"",
|
||
)
|
||
if key not in cache:
|
||
with db.no_autoflush:
|
||
cache[key] = db.scalar(
|
||
select(Product).where(
|
||
Product.project_no == item.project_no,
|
||
Product.attendance_point_name == item.attendance_point_name,
|
||
Product.product_name == item.product_name,
|
||
Product.process_name == (item.process_name or ""),
|
||
)
|
||
)
|
||
return cache[key]
|
||
|
||
|
||
REPORT_CORRECTION_LABELS = {
|
||
"start_at": "上班时间",
|
||
"end_at": "下班时间",
|
||
"review_remark": "备注",
|
||
}
|
||
|
||
ITEM_CORRECTION_LABELS = {
|
||
"device_no": "设备号",
|
||
"project_no": "项目号",
|
||
"product_name": "产品名称",
|
||
"raw_material_batch_no": "材料库存批次号",
|
||
"process_name": "工序",
|
||
"stamping_method": "冲压方式",
|
||
"process_unit_price_yuan": "工序单价",
|
||
"changeover_count": "换料次数",
|
||
"remark": "杂活事项",
|
||
"good_qty": "成品数量",
|
||
"defect_qty": "不良数量",
|
||
"scrap_qty": "报废数量",
|
||
}
|
||
|
||
|
||
def _correction_value(value) -> str | float:
|
||
if value is None:
|
||
return "-"
|
||
if hasattr(value, "isoformat"):
|
||
return value.isoformat()
|
||
return value
|
||
|
||
|
||
def _append_correction_pair(group: dict, key: tuple, label: str, old_value, new_value) -> None:
|
||
if key in group["correction_pair_keys"]:
|
||
return
|
||
group["correction_pair_keys"].add(key)
|
||
group["correction_pairs"].append(
|
||
{
|
||
"key": "__".join(str(part) for part in key),
|
||
"label": label,
|
||
"old_value": _correction_value(old_value),
|
||
"new_value": _correction_value(new_value),
|
||
}
|
||
)
|
||
|
||
|
||
def _append_dashboard_corrections(
|
||
group: dict,
|
||
report: ProductionReport,
|
||
item: ProductionReportItem,
|
||
report_corrections: dict,
|
||
item_corrections: dict,
|
||
) -> None:
|
||
for field, old_value in report_corrections.items():
|
||
label = REPORT_CORRECTION_LABELS.get(field, field)
|
||
_append_correction_pair(group, (report.id, field), label, old_value, getattr(report, field))
|
||
for field, old_value in item_corrections.items():
|
||
label = ITEM_CORRECTION_LABELS.get(field, field)
|
||
_append_correction_pair(group, (report.id, item.id, field), label, old_value, getattr(item, field))
|
||
|
||
|
||
def _dashboard_rows(
|
||
db: Session,
|
||
start_date: date | None = None,
|
||
end_date: date | None = None,
|
||
include_voided: bool = False,
|
||
) -> list[DashboardRow]:
|
||
query = (
|
||
select(ProductionReportAllocation)
|
||
.join(ProductionReportAllocation.report)
|
||
.join(ProductionReportAllocation.item)
|
||
.options(
|
||
selectinload(ProductionReportAllocation.report).selectinload(ProductionReport.employee),
|
||
selectinload(ProductionReportAllocation.report).selectinload(ProductionReport.items),
|
||
selectinload(ProductionReportAllocation.report).selectinload(
|
||
ProductionReport.audit_logs
|
||
),
|
||
selectinload(ProductionReportAllocation.report)
|
||
.selectinload(ProductionReport.session)
|
||
.selectinload(WorkSession.devices),
|
||
selectinload(ProductionReportAllocation.item),
|
||
)
|
||
.where(ProductionReport.status == ReportStatus.approved)
|
||
)
|
||
if not include_voided:
|
||
query = query.where(ProductionReport.is_voided.is_(False))
|
||
if start_date:
|
||
query = query.where(ProductionReportAllocation.allocation_date >= start_date)
|
||
if end_date:
|
||
query = query.where(ProductionReportAllocation.allocation_date <= end_date)
|
||
allocations = db.scalars(query).all()
|
||
|
||
product_cache: dict[tuple[str, str, str, str, str], Product | None] = {}
|
||
for allocation in allocations:
|
||
item = allocation.item
|
||
if item is not None:
|
||
setattr(item, "_dashboard_product", _product_for_item(db, product_cache, item))
|
||
return _dashboard_rows_from_allocations(allocations)
|
||
|
||
|
||
def _dashboard_rows_from_allocations(allocations: list) -> list[DashboardRow]:
|
||
groups: dict[tuple, dict] = {}
|
||
correction_cache: dict[int, tuple[dict, dict[int, dict], dict[int, dict]]] = {}
|
||
for allocation in allocations:
|
||
report = getattr(allocation, "report", None)
|
||
item = getattr(allocation, "item", None)
|
||
if report is None or item is None:
|
||
continue
|
||
report_id = getattr(report, "id", None)
|
||
report_key = report_id if report_id is not None else id(report)
|
||
if report_key not in correction_cache:
|
||
correction_cache[report_key] = report_correction_maps(report)
|
||
report_corrections, item_corrections, _device_corrections = correction_cache[report_key]
|
||
employee_name = report.employee.name if report.employee else ""
|
||
worker_type = (
|
||
"临时工" if (report.employee and report.employee.is_temporary) else "正式工"
|
||
)
|
||
item_misc = is_misc_item(item)
|
||
process_name = (
|
||
(item.remark or item.process_name or "") if item_misc else (item.process_name or "")
|
||
)
|
||
product = getattr(item, "_dashboard_product", None)
|
||
is_cleaning = is_cleaning_item(item) or is_cleaning_product(product)
|
||
is_misc = item_misc or is_misc_product(product)
|
||
is_continuous_die = is_continuous_die_item(item) or is_continuous_die_product(product)
|
||
item_standard_beat = getattr(item, "standard_beat", 0)
|
||
standard_beat = (
|
||
0
|
||
if (is_cleaning or is_misc)
|
||
else as_float(product.standard_beat if product else item_standard_beat)
|
||
)
|
||
process_unit_price = as_float(getattr(item, "process_unit_price_yuan", 0))
|
||
stamping_method = getattr(item, "stamping_method", None) or (
|
||
product.stamping_method if product else None
|
||
)
|
||
allocation_date = getattr(allocation, "allocation_date")
|
||
attendance_point_name = getattr(
|
||
report, "attendance_point_name", getattr(allocation, "attendance_point_name", "")
|
||
)
|
||
employee_phone = getattr(
|
||
report, "employee_phone", getattr(allocation, "employee_phone", "")
|
||
)
|
||
key = (
|
||
attendance_point_name,
|
||
allocation_date,
|
||
employee_name,
|
||
employee_phone,
|
||
item.project_no,
|
||
item.product_name,
|
||
process_name,
|
||
stamping_method or "",
|
||
)
|
||
group = groups.setdefault(
|
||
key,
|
||
{
|
||
"id": "__".join(str(part) for part in key),
|
||
"attendance_point_name": attendance_point_name,
|
||
"report_date": allocation_date,
|
||
"employee_phone": employee_phone,
|
||
"employee_name": employee_name,
|
||
"worker_type": worker_type,
|
||
"project_no": item.project_no,
|
||
"product_name": item.product_name,
|
||
"process_name": process_name,
|
||
"report_ids": set(),
|
||
"source_report_dates": set(),
|
||
"effective_minutes": 0.0,
|
||
"shift_day_minutes": 0.0,
|
||
"shift_overtime_minutes": 0.0,
|
||
"shift_night_minutes": 0.0,
|
||
"total_good_qty": 0.0,
|
||
"total_defect_qty": 0.0,
|
||
"total_output_qty": 0.0,
|
||
"standard_beat": standard_beat,
|
||
"process_unit_price_yuan": process_unit_price,
|
||
"changeover_count": 0.0,
|
||
"reference_wage": 0.0,
|
||
"expected_workload": 0.0,
|
||
"profile_no": (
|
||
getattr(product, "profile_no", getattr(item, "profile_no", None))
|
||
if product
|
||
else getattr(item, "profile_no", None)
|
||
),
|
||
"material_code": (
|
||
getattr(product, "material_code", getattr(item, "material_code", None))
|
||
if product
|
||
else getattr(item, "material_code", None)
|
||
),
|
||
"material_name": (
|
||
getattr(product, "material_name", getattr(item, "material_name", None))
|
||
if product
|
||
else getattr(item, "material_name", None)
|
||
),
|
||
"raw_material_batch_numbers": set(),
|
||
"device_no": item.device_no,
|
||
"stamping_method": stamping_method,
|
||
"operator_count": (
|
||
as_float(product.operator_count)
|
||
if product
|
||
else as_float(getattr(item, "operator_count", 1))
|
||
),
|
||
"is_continuous_die": is_continuous_die,
|
||
"is_multi_person": is_multi_person_item(item),
|
||
"is_multi_person_assistant": False,
|
||
"is_misc": is_misc,
|
||
"is_system_auto_submitted": False,
|
||
"is_voided": False,
|
||
"review_remarks": [],
|
||
"review_remark_keys": set(),
|
||
"has_over_limit": False,
|
||
"over_limit_exceeded_keys": set(),
|
||
"over_limit_uncheckable_keys": set(),
|
||
"over_limit_reasons": [],
|
||
"over_limit_reason_keys": set(),
|
||
"correction_pairs": [],
|
||
"correction_pair_keys": set(),
|
||
},
|
||
)
|
||
_append_dashboard_corrections(
|
||
group,
|
||
report,
|
||
item,
|
||
report_corrections,
|
||
item_corrections.get(getattr(item, "id", None), {}),
|
||
)
|
||
good_qty = as_float(getattr(allocation, "good_qty", 0))
|
||
defect_qty = as_float(getattr(allocation, "defect_qty", 0)) + as_float(
|
||
getattr(allocation, "scrap_qty", 0)
|
||
)
|
||
output_qty = good_qty + defect_qty
|
||
effective_minutes = as_float(getattr(allocation, "effective_minutes", 0))
|
||
expected_workload = effective_minutes * 60 / standard_beat if standard_beat > 0 else 0
|
||
group["report_ids"].add(report_key)
|
||
group["source_report_dates"].add(report.report_date)
|
||
group["effective_minutes"] += effective_minutes
|
||
group["expected_workload"] += expected_workload
|
||
group["shift_day_minutes"] += as_float(getattr(allocation, "day_minutes", 0))
|
||
group["shift_overtime_minutes"] += as_float(getattr(allocation, "overtime_minutes", 0))
|
||
group["shift_night_minutes"] += as_float(getattr(allocation, "night_minutes", 0))
|
||
group["total_good_qty"] += good_qty
|
||
group["reference_wage"] += as_float(getattr(allocation, "reference_wage", 0))
|
||
group["changeover_count"] += as_float(getattr(allocation, "changeover_count", 0))
|
||
group["total_defect_qty"] += defect_qty
|
||
group["total_output_qty"] += output_qty
|
||
group["is_system_auto_submitted"] = (
|
||
group["is_system_auto_submitted"] or bool(report.is_system_auto_submitted)
|
||
)
|
||
group["is_voided"] = group["is_voided"] or bool(report.is_voided)
|
||
group["is_multi_person"] = group["is_multi_person"] or is_multi_person_item(item)
|
||
group["is_continuous_die"] = group["is_continuous_die"] or is_continuous_die
|
||
group["is_multi_person_assistant"] = (
|
||
group["is_multi_person_assistant"] or bool(report.is_multi_person_assistant)
|
||
)
|
||
over_limit_status = str(getattr(item, "over_limit_status", "") or "")
|
||
over_limit_reason = str(getattr(item, "over_limit_reason", "") or "").strip()
|
||
over_limit_item_key = getattr(item, "id", None) or id(item)
|
||
if over_limit_status == "exceeded":
|
||
group["has_over_limit"] = True
|
||
group["over_limit_exceeded_keys"].add(over_limit_item_key)
|
||
elif over_limit_status == "uncheckable":
|
||
group["over_limit_uncheckable_keys"].add(over_limit_item_key)
|
||
if over_limit_reason and over_limit_reason not in group["over_limit_reason_keys"]:
|
||
group["over_limit_reason_keys"].add(over_limit_reason)
|
||
group["over_limit_reasons"].append(over_limit_reason)
|
||
if report.review_remark:
|
||
review_remark = str(report.review_remark).strip()
|
||
if review_remark and review_remark not in group["review_remark_keys"]:
|
||
group["review_remark_keys"].add(review_remark)
|
||
group["review_remarks"].append(review_remark)
|
||
raw_material_batch_no = getattr(item, "raw_material_batch_no", None)
|
||
if raw_material_batch_no:
|
||
group["raw_material_batch_numbers"].add(raw_material_batch_no)
|
||
|
||
rows: list[DashboardRow] = []
|
||
for group in groups.values():
|
||
actual_beat = (
|
||
group["effective_minutes"] * 60 / group["total_output_qty"]
|
||
if group["total_output_qty"] > 0
|
||
else 0
|
||
)
|
||
standard_beat = group["standard_beat"]
|
||
expected_workload = group["expected_workload"]
|
||
pace_rate = (actual_beat - standard_beat) / standard_beat * 100 if standard_beat > 0 else 0
|
||
workload_rate = (
|
||
(group["total_good_qty"] - expected_workload) / expected_workload * 100
|
||
if expected_workload > 0
|
||
else 0
|
||
)
|
||
shift_minutes = {
|
||
"day": group["shift_day_minutes"],
|
||
"overtime": group["shift_overtime_minutes"],
|
||
"night": group["shift_night_minutes"],
|
||
}
|
||
source_report_dates = sorted(group["source_report_dates"])
|
||
exceeded_count = len(group["over_limit_exceeded_keys"])
|
||
uncheckable_count = len(group["over_limit_uncheckable_keys"])
|
||
if exceeded_count and uncheckable_count:
|
||
over_limit_summary = f"{exceeded_count}项超额，{uncheckable_count}项无法校验"
|
||
elif exceeded_count:
|
||
over_limit_summary = f"{exceeded_count}项超额"
|
||
elif uncheckable_count:
|
||
over_limit_summary = f"{uncheckable_count}项无法校验"
|
||
else:
|
||
over_limit_summary = None
|
||
rows.append(
|
||
DashboardRow(
|
||
id=group["id"],
|
||
attendance_point_name=group["attendance_point_name"],
|
||
report_date=group["report_date"],
|
||
source_report_date=source_report_dates[-1] if source_report_dates else group["report_date"],
|
||
source_report_dates=source_report_dates,
|
||
employee_phone=group["employee_phone"],
|
||
employee_name=group["employee_name"],
|
||
worker_type=group["worker_type"],
|
||
project_no=group["project_no"],
|
||
product_name=group["product_name"],
|
||
process_name=group["process_name"],
|
||
report_count=len(group["report_ids"]),
|
||
effective_minutes=round2(group["effective_minutes"]),
|
||
shift_distribution_text=shift_distribution_text(shift_minutes),
|
||
shift_day_minutes=round2(group["shift_day_minutes"]),
|
||
shift_overtime_minutes=round2(group["shift_overtime_minutes"]),
|
||
shift_night_minutes=round2(group["shift_night_minutes"]),
|
||
shift_other_minutes=0,
|
||
total_good_qty=round2(group["total_good_qty"]),
|
||
total_defect_qty=round2(group["total_defect_qty"]),
|
||
total_output_qty=round2(group["total_output_qty"]),
|
||
actual_beat=round2(actual_beat),
|
||
standard_beat=round2(standard_beat),
|
||
process_unit_price_yuan=round(as_float(group["process_unit_price_yuan"]), 4),
|
||
changeover_count=round2(group["changeover_count"]),
|
||
reference_wage=round2(group["reference_wage"]),
|
||
expected_workload=round1(expected_workload),
|
||
pace_rate=round1(pace_rate),
|
||
workload_rate=round1(workload_rate),
|
||
pace_text=f"{'慢' if pace_rate > 0 else '快'}{abs(round1(pace_rate))}%",
|
||
workload_text=f"{'多' if workload_rate >= 0 else '少'}{abs(round1(workload_rate))}%",
|
||
profile_no=group["profile_no"],
|
||
material_code=group["material_code"],
|
||
material_name=group["material_name"],
|
||
raw_material_batch_no="、".join(sorted(group["raw_material_batch_numbers"])) or None,
|
||
device_no=group["device_no"],
|
||
stamping_method=group["stamping_method"],
|
||
operator_count=group["operator_count"],
|
||
is_continuous_die=group["is_continuous_die"],
|
||
is_multi_person=group["is_multi_person"],
|
||
is_multi_person_assistant=group["is_multi_person_assistant"],
|
||
is_misc=group["is_misc"],
|
||
is_system_auto_submitted=group["is_system_auto_submitted"],
|
||
is_voided=group["is_voided"],
|
||
review_remark="；".join(group["review_remarks"]) or None,
|
||
has_over_limit=group["has_over_limit"],
|
||
over_limit_summary=over_limit_summary,
|
||
over_limit_reasons="；".join(group["over_limit_reasons"]) or None,
|
||
correction_pairs=group["correction_pairs"],
|
||
)
|
||
)
|
||
|
||
rows.sort(
|
||
key=lambda item: (
|
||
item.report_date,
|
||
item.attendance_point_name,
|
||
item.employee_phone,
|
||
item.project_no,
|
||
item.product_name,
|
||
item.process_name or "",
|
||
),
|
||
reverse=True,
|
||
)
|
||
return rows
|
||
|
||
|
||
@router.get("/reports/export")
|
||
def export_dashboard_report_excel(
|
||
start_date: date | None = None,
|
||
end_date: date | None = None,
|
||
include_voided: bool = False,
|
||
_: Personnel = Depends(require_roles(Role.manager)),
|
||
db: Session = Depends(get_db),
|
||
) -> Response:
|
||
purge_expired_voided_reports(db)
|
||
content = export_dashboard_rows(_dashboard_rows(db, start_date, end_date, include_voided))
|
||
return Response(
|
||
content=content,
|
||
media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
|
||
headers={"Content-Disposition": 'attachment; filename="dashboard.xlsx"'},
|
||
)
|
||
|
||
|
||
@router.get("/reports", response_model=PageResponse)
|
||
def dashboard_reports(
|
||
start_date: date | None = None,
|
||
end_date: date | None = None,
|
||
page: int = Query(1, ge=1),
|
||
page_size: int = Query(10, ge=1, le=100),
|
||
include_voided: bool = False,
|
||
_: Personnel = Depends(require_roles(Role.manager)),
|
||
db: Session = Depends(get_db),
|
||
) -> PageResponse:
|
||
purge_expired_voided_reports(db)
|
||
rows = _dashboard_rows(db, start_date, end_date, include_voided)
|
||
safe_page_size = min(100, max(1, page_size))
|
||
total = len(rows)
|
||
total_pages = max(1, ceil(total / safe_page_size))
|
||
start = (page - 1) * safe_page_size
|
||
return PageResponse(
|
||
page=page,
|
||
page_size=safe_page_size,
|
||
total=total,
|
||
total_pages=total_pages,
|
||
rows=rows[start : start + safe_page_size],
|
||
)
|