#!/usr/bin/env python3 from __future__ import annotations import csv import json import sys from collections import defaultdict from datetime import date, datetime from decimal import Decimal from pathlib import Path from urllib.parse import quote ROOT = Path(__file__).resolve().parent SNAPSHOT_AT = "2026-07-16T16:50:00+08:00" SNAPSHOT_LABEL = "2026-07-16 16:50（北京时间）" GENERATED_AT = datetime.now().astimezone().isoformat(timespec="seconds") BASE_URL = ( "https://zhctpmt.yyangpt.cn/work_store/data-queries/published/" "2026-07-16-all-project-stats/" ) RAW_URL = ( "https://zhctpmt.yyangpt.cn/raw-files/zhctprompt/work_store/data-queries/" "published/2026-07-16-all-project-stats/" ) HTML_PATH = ROOT / "sales-project-operations-dashboard.html" def read_csv(name: str) -> list[dict[str, str]]: with (ROOT / name).open(encoding="utf-8-sig", newline="") as handle: return list(csv.DictReader(handle)) def integer(value: str | None) -> int: return int(Decimal(value or "0")) def number(value: str | None) -> float: return float(Decimal(value or "0")) def chart_source(label: str, tables: str, scope: str) -> str: return f"数据来源：{label}｜数据表：{tables}｜口径：{scope}｜快照：{SNAPSHOT_LABEL}" def build_artifact() -> None: summary_rows = read_csv("项目汇总.csv") daily_rows = read_csv("每日餐段订单.csv") quality_rows = read_csv("数据质量检查.csv") snapshot_date = date.fromisoformat("2026-07-16") latest_verified_order_date: dict[str, date] = {} for daily in daily_rows: meal_date = date.fromisoformat(daily["日期"]) if meal_date <= snapshot_date and integer(daily["订单数"]) > 0: code = daily["配置目录"] latest_verified_order_date[code] = max(meal_date, latest_verified_order_date.get(code, meal_date)) connected = [row for row in summary_rows if row["查询状态"] == "成功"] operational = [row for row in connected if integer(row["订单数"]) > 0] connected_no_orders = [row for row in connected if integer(row["订单数"]) == 0] unavailable = [row for row in summary_rows if row["查询状态"] != "成功"] total_users = sum(integer(row["用户数"]) for row in connected) total_orders = sum(integer(row["订单数"]) for row in connected) total_amount = sum(Decimal(row["订单金额(元)"] or "0") for row in connected) total_dish_rows = sum(integer(row["订单菜品明细行数"]) for row in connected) reconciled_projects = sum(row["回算是否一致"] == "是" for row in connected) project_rows: list[dict[str, object]] = [] for row in summary_rows: is_connected = row["查询状态"] == "成功" orders = integer(row["订单数"]) if is_connected else None if not is_connected: operating_status = "暂未接入" elif (orders or 0) > 0: operating_status = "已有订单" else: operating_status = "已接入无订单" detail_rows = integer(row["订单菜品明细行数"]) if is_connected else None unmatched = integer(row["无法匹配菜品档案的明细行数"]) if is_connected else None coverage = ((detail_rows - unmatched) / detail_rows) if detail_rows else (0 if is_connected else None) latest_verified = latest_verified_order_date.get(row["配置目录"]) days_since_last_order = (snapshot_date - latest_verified).days if latest_verified else None if days_since_last_order is None: activity_score, activity_label = 0, "暂无历史订单" if is_connected else "数据库暂未接入" elif days_since_last_order <= 7: activity_score, activity_label = 1, "近 7 天有订单" elif days_since_last_order <= 30: activity_score, activity_label = 0.7, "近 30 天有订单" elif days_since_last_order <= 90: activity_score, activity_label = 0.4, "近 90 天有订单" else: activity_score, activity_label = 0.1, "超过 90 天未见订单" project_rows.append({ "code": row["配置目录"], "project": row["项目名称"], "database": row["数据库名称"], "operating_status": operating_status, "connected": is_connected, "users": integer(row["用户数"]) if is_connected else None, "orders": orders, "transaction_amount_yuan": number(row["订单金额(元)"]) if is_connected else None, "transaction_amount_wan": round(number(row["订单金额(元)"]) / 10_000, 2) if is_connected else None, "order_share": (orders / total_orders) if is_connected and total_orders else None, "amount_share": float(Decimal(row["订单金额(元)"] or "0") / total_amount) if is_connected and total_amount else None, "first_order_date": row["最早订单日期"] or "—", "latest_order_date": row["最晚订单日期"] or "—", "latest_verified_order_date": latest_verified.isoformat() if latest_verified else "—", "days_since_last_order": days_since_last_order, "activity_score": activity_score, "activity_label": activity_label, "dish_detail_rows": detail_rows, "dish_quantity": number(row["订单菜品数量"]) if is_connected else None, "dish_weight_g": number(row["订单菜品重量(g)"]) if is_connected else None, "dish_types": integer(row["菜品种类数"]) if is_connected else None, "energy_kcal": number(row["能量(kcal)"]) if is_connected else None, "carbohydrate_g": number(row["碳水(g)"]) if is_connected else None, "protein_g": number(row["蛋白质(g)"]) if is_connected else None, "fat_g": number(row["脂肪(g)"]) if is_connected else None, "nutrition_coverage": coverage, "connected_score": 1 if is_connected else 0, "orders_score": 1 if (orders or 0) > 0 else 0, "dish_score": 1 if (detail_rows or 0) > 0 else 0, "nutrition_score": coverage or 0, "数据接入": 1 if is_connected else 0, "已有订单": 1 if (orders or 0) > 0 else 0, "菜品明细": 1 if (detail_rows or 0) > 0 else 0, "近期活跃": activity_score, "营养匹配": coverage or 0, }) status_by_code = {row["code"]: row["operating_status"] for row in project_rows} def portfolio_row(status: str, rows: list[dict[str, object]]) -> dict[str, object]: known = [row for row in rows if row["connected"]] has_orders = [row for row in rows if (row["orders"] or 0) > 0] unavailable_group = bool(rows) and not known amount_yuan = sum(Decimal(str(row["transaction_amount_yuan"])) for row in known) return { "operating_status": status, "project_total": len(rows), "connected_projects": len(known), "connected_rate": len(known) / len(rows) if rows else 0, "operational_projects": len(has_orders), "operational_rate": len(has_orders) / len(rows) if rows else 0, # Unavailable projects are unknown, not zero. Null keeps the cards honest # when the viewer filters to the unavailable group. "registered_users": None if unavailable_group else sum(int(row["users"] or 0) for row in known), "orders": None if unavailable_group else sum(int(row["orders"] or 0) for row in known), "transaction_amount_yuan": None if unavailable_group else float(amount_yuan), "transaction_amount_yi": None if unavailable_group else round(float(amount_yuan) / 100_000_000, 4), "dish_detail_rows": None if unavailable_group else sum(int(row["dish_detail_rows"] or 0) for row in known), "reconciled_projects": None if unavailable_group else sum( summary_rows_by_code[row["code"]]["回算是否一致"] == "是" for row in known ), "reconciled_rate": None if unavailable_group else ( sum(summary_rows_by_code[row["code"]]["回算是否一致"] == "是" for row in known) / len(known) if known else 0 ), } summary_rows_by_code = {row["配置目录"]: row for row in summary_rows} portfolio = [portfolio_row("all", project_rows)] for status in ("已有订单", "已接入无订单", "暂未接入"): portfolio.append(portfolio_row(status, [row for row in project_rows if row["operating_status"] == status])) status_mix = [ {"operating_status": "已有订单", "project_count": len(operational), "project_share": len(operational) / len(summary_rows)}, {"operating_status": "已接入无订单", "project_count": len(connected_no_orders), "project_share": len(connected_no_orders) / len(summary_rows)}, {"operating_status": "暂未接入", "project_count": len(unavailable), "project_share": len(unavailable) / len(summary_rows)}, ] ranked_projects = sorted( [row for row in project_rows if (row["orders"] or 0) > 0], key=lambda row: row["orders"] or 0, reverse=True, ) order_ranking = ranked_projects amount_ranking = sorted(ranked_projects, key=lambda row: row["transaction_amount_yuan"] or 0, reverse=True) user_ranking = sorted( [row for row in project_rows if (row["users"] or 0) > 0], key=lambda row: row["users"] or 0, reverse=True, )[:10] matrix_rows = sorted( project_rows, key=lambda row: (row["connected_score"], row["orders_score"], row["orders"] or 0), reverse=True, ) monthly: dict[tuple[str, str], dict[str, object]] = defaultdict(lambda: { "orders": 0, "transaction_amount": Decimal("0"), "projects": set(), }) segment: dict[tuple[str, str], dict[str, object]] = defaultdict(lambda: { "orders": 0, "transaction_amount": Decimal("0"), "projects": set(), }) for row in daily_rows: if row["日期"] > "2026-07-16": continue month = row["日期"][:7] status = status_by_code[row["配置目录"]] for filter_status in ("all", status): monthly_key = (filter_status, month) monthly[monthly_key]["orders"] += integer(row["订单数"]) monthly[monthly_key]["transaction_amount"] += Decimal(row["订单金额(元)"] or "0") monthly[monthly_key]["projects"].add(row["配置目录"]) segment_key = (filter_status, row["餐段"]) segment[segment_key]["orders"] += integer(row["订单数"]) segment[segment_key]["transaction_amount"] += Decimal(row["订单金额(元)"] or "0") segment[segment_key]["projects"].add(row["配置目录"]) months = sorted(month for filter_status, month in monthly if filter_status == "all")[-12:] monthly_rows = [] for filter_status in ("all", "已有订单", "已接入无订单", "暂未接入"): for month in months: item = monthly.get((filter_status, month)) if not item: continue monthly_rows.append({ "operating_status": filter_status, "month": month, "orders": item["orders"], "transaction_amount_yuan": float(item["transaction_amount"]), "transaction_amount_wan": round(float(item["transaction_amount"]) / 10_000, 2), "active_projects": len(item["projects"]), }) segment_order = {"早餐": 1, "午餐": 2, "晚餐": 3, "夜宵": 4} segment_rows = [] for filter_status in ("all", "已有订单", "已接入无订单", "暂未接入"): items = [(label, item) for (status, label), item in segment.items() if status == filter_status] status_total = sum(item["orders"] for _, item in items) for label, item in sorted(items, key=lambda pair: segment_order.get(pair[0], 99)): segment_rows.append({ "operating_status": filter_status, "meal_segment": label, "orders": item["orders"], "transaction_amount_yuan": float(item["transaction_amount"]), "transaction_amount_wan": round(float(item["transaction_amount"]) / 10_000, 2), "order_share": item["orders"] / status_total if status_total else 0, "active_projects": len(item["projects"]), }) nutrition_rows = sorted( [row for row in project_rows if (row["dish_detail_rows"] or 0) > 0], key=lambda row: row["nutrition_coverage"] or 0, reverse=True, ) # Separate read-only production queries can change by a few rows while the # collection is running. Disclose those tiny timing deltas rather than hide # them or overwrite either source. quality_by_code = {row["配置目录"]: row for row in quality_rows} separate_read_differences = sum( quality_by_code[code]["明细行数"] != summary_rows_by_code[code]["订单菜品明细行数"] for code in quality_by_code ) future_orders = sum(integer(row["未来日期订单数"]) for row in quality_rows) severe_outliers = sum( bool(row["number最大值"]) and Decimal(row["number最大值"]) > Decimal("1000000") for row in quality_rows ) audit_base = { "config_unique": len({row["配置目录"] for row in summary_rows}), "config_total": len(summary_rows), "order_reconciled": sum(row["订单数"] == row["回算订单数"] for row in connected), "amount_reconciled": sum( Decimal(row["订单金额(元)"]) == Decimal(row["回算订单金额(元)"]) for row in connected ), "connected_total": len(connected), "daily_rows": len(daily_rows), "future_orders": future_orders, "severe_outliers": severe_outliers, "separate_read_differences": separate_read_differences, } quality_audit = [dict(audit_base, operating_status=status) for status in ("all", "已有订单", "已接入无订单", "暂未接入")] summary_sql = """SELECT COUNT(*) AS user_num FROM ydy_staff; SELECT COUNT(*) AS order_num, COALESCE(SUM(total_price), 0) AS total_price, MIN(meal_date) AS first_meal_date, MAX(meal_date) AS last_meal_date FROM ydy_meal_order;""" daily_sql = """SELECT meal_date, meal_times, CASE meal_times WHEN 1 THEN '早餐' WHEN 2 THEN '午餐' WHEN 3 THEN '晚餐' WHEN 4 THEN '夜宵' ELSE CONCAT('其他(', meal_times, ')') END AS meal_segment, COUNT(*) AS order_num, COALESCE(SUM(total_price), 0) AS total_price FROM ydy_meal_order GROUP BY meal_date, meal_times ORDER BY meal_date, meal_times;""" dish_sql = """SELECT COUNT(*) AS order_dish_row_num, COALESCE(SUM(CAST(im.number AS DECIMAL(20,4))), 0) AS dish_quantity, COALESCE(SUM(CAST(im.weight AS DECIMAL(20,4))), 0) AS dish_weight_g, COUNT(DISTINCT NULLIF(im.dishes_uuid, '')) AS dish_type_num, COALESCE(SUM(COALESCE(d.energy, 0) * CAST(im.number AS DECIMAL(20,4))), 0) AS energy_kcal, COALESCE(SUM(COALESCE(d.carbohydrate, 0) * CAST(im.number AS DECIMAL(20,4))), 0) AS carbohydrate_g, COALESCE(SUM(COALESCE(d.protein, 0) * CAST(im.number AS DECIMAL(20,4))), 0) AS protein_g, COALESCE(SUM(COALESCE(d.fat, 0) * CAST(im.number AS DECIMAL(20,4))), 0) AS fat_g, SUM(CASE WHEN d.uuid IS NULL THEN 1 ELSE 0 END) AS unmatched_dish_row_num FROM ydy_initial_menu AS im INNER JOIN ydy_meal_order AS mo ON mo.id = im.meal_order_id LEFT JOIN ( SELECT uuid, MAX(energy) AS energy, MAX(carbohydrate) AS carbohydrate, MAX(protein) AS protein, MAX(fat) AS fat FROM ydy_dishes GROUP BY uuid ) AS d ON d.uuid = im.dishes_uuid;""" sources = [ { "id": "project-summary-source", "label": "生产配置与项目生产库汇总", "href": BASE_URL + quote("项目汇总.csv"), "query": { "engine": "MySQL", "sql": summary_sql, "description": "项目名称和数据库信息读取自 zhctproject/store/application/config/prod；指标逐项目查询主业务生产库后汇总。", "executed_at": SNAPSHOT_AT, "language": "sql", "tables_used": ["ydy_staff", "ydy_meal_order"], "filters": ["17 个 prod 配置项目", "只查询主业务库，不查询 aizhct_*", "全量历史，不限制订单或支付状态", "海开 39.155.143.199 / hysoonai3 已按用户要求排除"], "metric_definitions": [ "项目总数=prod 配置目录数量", "数据已接入=本轮只读查询成功的项目", "已有订单项目=ydy_meal_order 至少有 1 行的项目", "累计用户=各项目 ydy_staff 行数之和，跨项目可能重复", "累计订单=各项目 ydy_meal_order 行数之和", "人民币交易金额=各项目 ydy_meal_order.total_price 合计；不是合同额、回款或公司营收", ], }, }, { "id": "daily-orders-source", "label": "每日餐段订单明细", "href": BASE_URL + quote("每日餐段订单.csv"), "query": { "engine": "MySQL", "sql": daily_sql, "description": "逐项目按 meal_date 与 meal_times 汇总订单数量和人民币订单金额。", "executed_at": SNAPSHOT_AT, "language": "sql", "tables_used": ["ydy_meal_order"], "filters": ["趋势排除 2026-07-16 之后的未来用餐日期", "meal_times 1/2/3/4 映射为早/午/晚/夜宵"], "metric_definitions": ["月订单=当月各项目各餐段订单数合计", "月交易金额=当月 total_price 合计，人民币", "餐段占比=该餐段订单数/全部可查询订单数"], }, }, { "id": "dish-quality-source", "label": "订单菜品与营养数据质量", "href": BASE_URL + quote("数据质量检查.csv"), "query": { "engine": "MySQL", "sql": dish_sql, "description": "从订单菜品明细关联当前菜品档案，统计数量、重量、种类、能量、碳水、蛋白质、脂肪及未匹配行。", "executed_at": SNAPSHOT_AT, "language": "sql", "tables_used": ["ydy_initial_menu", "ydy_meal_order", "ydy_dishes"], "filters": ["只统计能关联到订单的 ydy_initial_menu", "营养值=当前菜品档案单份营养值×订单明细 number", "西康宾馆异常行保留在原始汇总并单独披露"], "metric_definitions": ["营养档案匹配率=(菜品明细行-无法匹配菜品档案行)/菜品明细行", "订单菜品明细行=ydy_initial_menu 关联到订单的记录数"], }, }, { "id": "project-operating-matrix-source", "label": "项目运营状态与营养质量联合快照", "href": BASE_URL + quote("项目汇总.csv"), "query": { "engine": "MySQL", "sql": summary_sql + "\n\n" + dish_sql, "description": "项目配置、数据库连通结果、订单状态、订单菜品明细和营养档案匹配率的联合展示。", "executed_at": SNAPSHOT_AT, "language": "sql", "tables_used": ["ydy_staff", "ydy_meal_order", "ydy_initial_menu", "ydy_dishes"], "filters": ["17 个 prod 配置项目", "主业务库只读查询", "不可达项目不填成 0", "营养匹配率按当前菜品档案计算"], "metric_definitions": ["数据接入=数据库查询成功", "已有订单=订单数大于 0", "菜品明细=可关联订单的菜品明细行大于 0", "近期活跃=截至快照日距离最近一笔非未来日期订单的分档得分", "营养匹配=营养档案匹配率"], }, }, { "id": "validation-source", "label": "生产快照一致性校验", "href": BASE_URL + quote("统计报告.md"), "query": { "engine": "Python + Decimal 精确回算", "sql": """SELECT COUNT(*) AS config_total, COUNT(DISTINCT 配置目录) AS config_unique FROM 项目汇总; SELECT SUM(CASE WHEN 订单数 = 回算订单数 THEN 1 ELSE 0 END) AS order_reconciled, SUM(CASE WHEN 订单金额 = 回算订单金额 THEN 1 ELSE 0 END) AS amount_reconciled FROM 项目汇总 WHERE 查询状态 = '成功'; SELECT COUNT(*) AS daily_rows FROM 每日餐段订单; SELECT SUM(未来日期订单数) AS future_orders, SUM(CASE WHEN number最大值 > 1000000 THEN 1 ELSE 0 END) AS severe_outliers FROM 数据质量检查;""", "description": "对本次生产快照进行结构、汇总回算和异常检查；不以页面展示值反向证明数据正确。", "executed_at": SNAPSHOT_AT, "language": "sql", "tables_used": ["项目汇总.csv", "每日餐段订单.csv", "数据质量检查.csv"], "filters": ["金额使用 Decimal 精确比较", "每日粒度唯一键=配置目录+日期+餐段编码", "未来日期相对 2026-07-16 判断", "number>1,000,000 记为严重极值"], "metric_definitions": ["订单回算一致=项目订单数等于每日餐段订单数之和", "金额回算一致=项目金额等于每日餐段金额之和", "分次读取差异=独立生产查询之间明细行数发生微小变化"], }, }, {"id": "sql-source", "label": "完整可复现查询 SQL", "href": RAW_URL + quote("统计查询.sql")}, ] cards = [ {"id": "card-projects", "description": "来源：prod 配置目录；共 17 个项目。", "dataset": "portfolio", "sourceId": "project-summary-source", "metrics": [{"label": "项目总数", "field": "project_total", "format": "number"}]}, {"id": "card-connected", "description": "来源：生产库只读连接结果。", "dataset": "portfolio", "sourceId": "project-summary-source", "metrics": [{"label": "数据已接入", "field": "connected_projects", "format": "number"}, {"label": "接入率", "field": "connected_rate", "format": "percent"}]}, {"id": "card-operational", "description": "来源：ydy_meal_order；订单数大于 0。", "dataset": "portfolio", "sourceId": "project-summary-source", "metrics": [{"label": "已有订单项目", "field": "operational_projects", "format": "number"}, {"label": "项目占比", "field": "operational_rate", "format": "percent"}]}, {"id": "card-users", "description": "来源：各项目 ydy_staff 行数合计；跨项目可能重复。", "dataset": "portfolio", "sourceId": "project-summary-source", "metrics": [{"label": "累计用户记录", "field": "registered_users", "format": "compact"}]}, {"id": "card-orders", "description": "来源：各项目 ydy_meal_order 全量历史行数。", "dataset": "portfolio", "sourceId": "project-summary-source", "metrics": [{"label": "累计订单", "field": "orders", "format": "compact"}]}, {"id": "card-amount", "description": "来源：ydy_meal_order.total_price 合计；人民币，非公司营收。", "dataset": "portfolio", "sourceId": "project-summary-source", "metrics": [{"label": "交易金额（人民币亿元）", "field": "transaction_amount_yi", "format": "number"}]}, {"id": "audit-config", "description": "来源：prod 配置目录；配置目录键无重复。", "dataset": "quality_audit", "sourceId": "validation-source", "metrics": [{"label": "项目目录唯一", "field": "config_unique", "format": "number"}, {"label": "应有目录", "field": "config_total", "format": "number"}]}, {"id": "audit-reconcile", "description": "来源：项目汇总与每日餐段明细；订单数和人民币金额均精确回算。", "dataset": "quality_audit", "sourceId": "validation-source", "metrics": [{"label": "订单回算一致", "field": "order_reconciled", "format": "number"}, {"label": "金额回算一致", "field": "amount_reconciled", "format": "number"}]}, {"id": "audit-future", "description": "来源：数据质量检查；相对快照日的未来用餐日期订单。", "dataset": "quality_audit", "sourceId": "validation-source", "metrics": [{"label": "未来日期订单", "field": "future_orders", "format": "number"}, {"label": "每日餐段明细", "field": "daily_rows", "format": "compact"}]}, {"id": "audit-anomaly", "description": "来源：数据质量检查；严重极值与分次查询微小差异均已披露。", "dataset": "quality_audit", "sourceId": "validation-source", "metrics": [{"label": "严重菜品极值", "field": "severe_outliers", "format": "number"}, {"label": "分次读取差异项目", "field": "separate_read_differences", "format": "number"}]}, ] source_project = chart_source("项目汇总.csv", "ydy_staff、ydy_meal_order", "全量历史；人民币；主业务库") source_daily = chart_source("每日餐段订单.csv", "ydy_meal_order", "排除快照日之后的未来用餐日期") source_dish = chart_source("数据质量检查.csv", "ydy_initial_menu、ydy_meal_order、ydy_dishes", "营养按当前菜品档案匹配") charts = [ { "id": "chart-project-value", "title": "项目价值全景｜用户 × 订单 × 人民币交易金额", "subtitle": source_project + "；圆点越大代表累计人民币交易金额越高", "intent": "relationship", "question": "哪些项目同时具备用户沉淀、真实使用和交易规模？", "rationale": "一个气泡图合并用户榜、订单榜和金额规模，避免重复展示同一批项目。", "type": "scatter", "dataset": "project_value", "sourceId": "project-summary-source", "encodings": {"x": {"field": "users", "type": "quantitative", "format": "compact", "label": "累计用户记录"}, "y": {"field": "orders", "type": "quantitative", "format": "compact", "label": "累计订单"}, "size": {"field": "transaction_amount_wan", "type": "quantitative", "label": "人民币万元"}, "color": {"field": "operating_status", "type": "nominal", "label": "运营状态"}, "label": {"field": "project", "type": "nominal", "label": "项目"}, "tooltip": [{"field": "project", "type": "text", "label": "项目"}, {"field": "transaction_amount_wan", "type": "quantitative", "format": "number", "unit": "万元", "label": "人民币交易金额"}, {"field": "latest_verified_order_date", "type": "text", "label": "最近有效订单日"}, {"field": "activity_label", "type": "text", "label": "近期活跃"}]}, "valueFormat": "compact", "layout": "full", "palette": {"kind": "categorical"}, "surface": {"surface": "card", "viewMode": "visualization"}, }, { "id": "chart-amount-rank", "title": "项目成果榜｜累计人民币交易金额（万元）", "subtitle": source_project + "；订单金额不等同合同额、回款或公司营收", "intent": "comparison", "question": "哪些项目已形成更高的真实交易承载规模？", "rationale": "销售成果榜只保留统一人民币金额口径，订单和用户通过价值全景查看。", "type": "horizontalBar", "dataset": "amount_ranking", "sourceId": "project-summary-source", "encodings": {"x": {"field": "project", "type": "nominal", "label": "项目"}, "y": {"field": "transaction_amount_wan", "type": "quantitative", "format": "number", "unit": "万元", "label": "人民币万元"}, "tooltip": [{"field": "orders", "type": "quantitative", "format": "compact", "label": "累计订单"}, {"field": "users", "type": "quantitative", "format": "compact", "label": "累计用户记录"}, {"field": "amount_share", "type": "quantitative", "format": "percent", "label": "金额占比"}]}, "valueFormat": "number", "unit": "万元", "layout": "half", "palette": {"kind": "sequential"}, "settings": {"sort": "descending", "showValues": True}, "surface": {"surface": "card", "viewMode": "visualization"}, }, { "id": "chart-operating-matrix", "title": "项目运营雷达｜接入、订单、菜品、活跃、营养", "subtitle": source_project + "；近期活跃按快照日前最近有效订单日期分档", "intent": "relationship", "question": "每个项目当前的运营成熟度和销售跟进重点是什么？", "rationale": "五个互补信号放在同一矩阵中，销售可快速识别标杆、待激活和待接入项目。", "type": "heatmap", "dataset": "project_matrix", "sourceId": "project-operating-matrix-source", "encodings": {"x": {"field": "project", "type": "nominal", "label": "项目"}, "y": {"fields": ["数据接入", "已有订单", "菜品明细", "近期活跃", "营养匹配"], "type": "quantitative", "format": "percent", "label": "成熟度信号"}}, "valueFormat": "percent", "layout": "full", "palette": {"kind": "sequential"}, "surface": {"surface": "card", "viewMode": "visualization"}, }, { "id": "chart-monthly-amount", "title": "近 12 个月交易趋势｜人民币万元", "subtitle": source_daily + "；金额为 total_price 合计；未来用餐日期已排除", "intent": "trend", "question": "近期真实交易承载规模如何变化？", "rationale": "销售趋势只保留金额主轴，同时在提示中提供订单数和活跃项目数。", "type": "area", "dataset": "monthly_orders", "sourceId": "daily-orders-source", "encodings": {"x": {"field": "month", "type": "temporal", "label": "月份"}, "y": {"field": "transaction_amount_wan", "type": "quantitative", "format": "number", "unit": "万元", "label": "人民币万元"}, "tooltip": [{"field": "orders", "type": "quantitative", "format": "compact", "label": "订单数"}, {"field": "active_projects", "type": "quantitative", "label": "有订单项目"}]}, "valueFormat": "number", "unit": "万元", "layout": "half", "palette": {"kind": "sequential"}, "settings": {"showPoints": "always", "showLatestValue": True}, "surface": {"surface": "card", "viewMode": "visualization"}, }, { "id": "chart-meal-segment", "title": "真实使用场景｜早午晚夜订单结构", "subtitle": source_daily, "intent": "composition", "question": "客户主要在哪些餐段真实使用系统？", "rationale": "餐段订单量直接反映项目落地场景和使用强度。", "type": "horizontalBar", "dataset": "meal_segments", "sourceId": "daily-orders-source", "encodings": {"x": {"field": "meal_segment", "type": "nominal", "label": "餐段"}, "y": {"field": "orders", "type": "quantitative", "format": "compact", "label": "订单数"}, "tooltip": [{"field": "order_share", "type": "quantitative", "format": "percent", "label": "订单占比"}, {"field": "transaction_amount_wan", "type": "quantitative", "format": "number", "unit": "万元", "label": "人民币万元"}, {"field": "active_projects", "type": "quantitative", "label": "涉及项目"}]}, "valueFormat": "compact", "layout": "half", "palette": {"kind": "sequential"}, "settings": {"showValues": True}, "surface": {"surface": "card", "viewMode": "visualization"}, }, { "id": "chart-nutrition-coverage", "title": "营养数据可用性｜订单菜品档案匹配率", "subtitle": source_dish + "；仅显示有菜品明细的项目", "intent": "comparison", "question": "哪些项目的营养成果可以优先用于销售案例？", "rationale": "匹配率用于区分可直接引用、需补档和不可引用的营养数据。", "type": "horizontalBar", "dataset": "nutrition_coverage", "sourceId": "dish-quality-source", "encodings": {"x": {"field": "project", "type": "nominal", "label": "项目"}, "y": {"field": "nutrition_coverage", "type": "quantitative", "format": "percent", "label": "匹配率"}, "tooltip": [{"field": "dish_detail_rows", "type": "quantitative", "format": "compact", "label": "菜品明细行"}, {"field": "dish_types", "type": "quantitative", "label": "菜品种类"}, {"field": "activity_label", "type": "text", "label": "近期活跃"}]}, "valueFormat": "percent", "layout": "half", "palette": {"kind": "sequential"}, "settings": {"sort": "descending", "showValues": True}, "surface": {"surface": "card", "viewMode": "visualization"}, }, ] # Keep provenance visible on the big-screen canvas, not only inside the # source dialog. Every chart subtitle contains source, tables, scope and # snapshot time. for chart in charts: chart["showDescription"] = True blocks = [ {"id": "intro", "type": "markdown", "body": "# 智慧营养健康餐厅 · 全国项目销售运营大屏\n\n用一屏回答销售最关心的五个问题：**覆盖了多少项目、哪些项目已形成规模、近期是否活跃、客户如何真实使用、哪些成果可以放心对外引用。** 生产数据快照：2026-07-16 16:50（北京时间）。", "layout": "full"}, {"id": "interaction-guide", "type": "markdown", "body": "**交互方式：** 点击页面上方“项目运营状态”可筛选已有订单、已接入无订单或暂未接入项目；悬停图形查看项目详情；点击每个图表右上角 `···` 可全屏查看并核验数据来源。", "layout": "full"}, {"id": "headline-metrics", "type": "metric-strip", "cardIds": ["card-projects", "card-connected", "card-operational", "card-users", "card-orders", "card-amount"], "layout": "full"}, {"id": "headline-source", "type": "markdown", "body": "**本栏数据来源：** 项目名称与数据库信息读取自 `zhctproject/store/application/config/prod`；用户来自 `ydy_staff`；订单及人民币金额来自 `ydy_meal_order`；菜品明细来自 `ydy_initial_menu`。统计为全量历史，只读查询，快照时间 2026-07-16 16:50。", "layout": "full", "sourceId": "project-summary-source"}, {"id": "value-section", "type": "markdown", "body": "## 销售成果一眼看懂\n\n价值全景把用户、订单和人民币交易金额合在一张图中；成果榜只保留金额口径，避免三个排行榜重复讲同一件事。", "layout": "full"}, {"id": "project-value-chart", "type": "chart", "chartId": "chart-project-value", "layout": "full"}, {"id": "amount-chart", "type": "chart", "chartId": "chart-amount-rank", "layout": "half"}, {"id": "trend-chart", "type": "chart", "chartId": "chart-monthly-amount", "layout": "half"}, {"id": "portfolio-section", "type": "markdown", "body": "## 项目成熟度与跟进机会\n\n矩阵从接入、订单、菜品、近期活跃和营养匹配五个互补维度扫描项目。颜色越亮，代表对应信号越成熟；不把不可达项目误写为 0。", "layout": "full"}, {"id": "matrix-chart", "type": "chart", "chartId": "chart-operating-matrix", "layout": "full"}, {"id": "structure-section", "type": "markdown", "body": "## 客户真实使用与可销售成果\n\n餐段结构回答客户在什么场景使用；营养匹配率回答哪些项目成果适合对外引用。", "layout": "full"}, {"id": "meal-chart", "type": "chart", "chartId": "chart-meal-segment", "layout": "half"}, {"id": "quality-chart", "type": "chart", "chartId": "chart-nutrition-coverage", "layout": "half"}, {"id": "trust-section", "type": "markdown", "body": "## 数据可信度审计\n\n核心用户、订单和人民币金额可用于销售汇报；营养总量必须结合匹配率和异常说明使用。以下校验来自原始 CSV 独立回算，不是页面自证。", "layout": "full", "sourceId": "validation-source"}, {"id": "trust-metrics", "type": "metric-strip", "cardIds": ["audit-config", "audit-reconcile", "audit-future", "audit-anomaly"], "layout": "full"}, {"id": "caveats", "type": "markdown", "body": "## 数据边界与真实性说明\n\n- 5 个内网项目本轮不可达：滨州健康科技职业学院、城市副中心、江西206、莱蒂森、无锡新吴区；页面不把它们填成 0，而是标记为暂未接入。\n- 机场、首通智城、山西焦煤使用虚拟菜品，营养数据不可用；金斯瑞和赛迪只有部分订单菜品能匹配当前营养档案。\n- 西康宾馆存在一条已核实的异常菜品明细，导致原始菜品数量、重量、能量、碳水、蛋白质和脂肪总量严重放大；本大屏不把这些失真总量作为成果指标，但原始数据仍保留在来源文件。\n- 产业园和赛迪存在未来用餐日期订单；月度趋势已排除 2026-07-16 之后的日期。\n- 页面是生产库只读查询快照，不是浏览器直连数据库；数据来源、SQL、过滤条件和指标定义均可从各模块右上角来源入口核验。", "layout": "full", "sourceId": "dish-quality-source"}, ] manifest = { "version": 1, "surface": "dashboard", "title": "智慧营养健康餐厅｜全国项目运营数据大屏", "description": "公司管理层与销售团队使用的真实生产数据科技大屏。", "generatedAt": GENERATED_AT, "filters": [ { "id": "filter-operating-status", "label": "项目运营状态", "dataset": "filter_options", "field": "value", "defaultValue": "all", "includeAll": True, "targets": [ {"dataset": "portfolio", "field": "operating_status"}, {"dataset": "project_value", "field": "operating_status"}, {"dataset": "project_matrix", "field": "operating_status"}, {"dataset": "amount_ranking", "field": "operating_status"}, {"dataset": "monthly_orders", "field": "operating_status"}, {"dataset": "meal_segments", "field": "operating_status"}, {"dataset": "nutrition_coverage", "field": "operating_status"}, {"dataset": "quality_audit", "field": "operating_status"}, ], } ], "cards": cards, "charts": charts, "sources": sources, "blocks": blocks, } snapshot = { "version": 1, "generatedAt": SNAPSHOT_AT, "status": "ready", "datasets": { "portfolio": portfolio, "projects": project_rows, "filter_options": [{"value": status} for status in ("已有订单", "已接入无订单", "暂未接入")], "project_value": [row for row in project_rows if row["connected"]], "status_mix": status_mix, "project_matrix": matrix_rows, "order_ranking": order_ranking, "amount_ranking": amount_ranking, "user_ranking": user_ranking, "monthly_orders": monthly_rows, "meal_segments": segment_rows, "nutrition_coverage": nutrition_rows, "quality_audit": quality_audit, }, } artifact = {"surface": "dashboard", "manifest": manifest, "snapshot": snapshot, "sources": sources} output = ROOT / "sales-project-operations-dashboard.artifact.json" output.write_text(json.dumps(artifact, ensure_ascii=False, indent=2), encoding="utf-8") print(output) TECH_STYLE = r""" """ def apply_tech_theme() -> None: html = HTML_PATH.read_text(encoding="utf-8") html = html.replace('', '', 1) html = html.replace('', '', 1) if 'id="sales-tech-screen-theme"' not in html: html = html.replace("", TECH_STYLE + "\n", 1) HTML_PATH.write_text(html, encoding="utf-8") print(HTML_PATH) if __name__ == "__main__": # Compatibility entrypoint: the published page now uses the fixed 16:9 # command-center layout requested for the large display. Keep this older # filename callable so existing publishing jobs do not overwrite it with # the previous scrolling report layout. from build_command_center import build build()