#!/usr/bin/env python3 """Build the bounded Data Analytics artifact payload for this analysis.""" from __future__ import annotations import csv import json from pathlib import Path HERE = Path(__file__).resolve().parent REPO = HERE.parents[1] GENERATED_AT = "2026-08-04T12:00:00+08:00" TITLE = "智慧团餐项目软件与硬件总体画像" def read_csv(name: str) -> list[dict]: with (HERE / name).open(encoding="utf-8-sig", newline="") as handle: rows = list(csv.DictReader(handle)) numeric_fields = { "project_coverage_count", "project_coverage_pct", "evidence_item_count", "explicit_evidence_rows", "hardware_implied_rows", "category_count", "source_line_count", "observed_quantity", "missing_quantity_lines", } for row in rows: for key in numeric_fields & row.keys(): value = row[key] if value == "": row[key] = None else: number = float(value) row[key] = int(number) if number.is_integer() else number return rows def repo_path(path: Path) -> str: return path.relative_to(REPO).as_posix() def source(source_id: str, label: str, sql: str, description: str, tables: list[str], metrics: list[str]) -> dict: return { "id": source_id, "label": label, "path": repo_path(HERE / "analysis_queries.sql"), "query": { "engine": "sqlite", "language": "sql", "sql": sql.strip(), "description": description, "tables_used": tables, "metric_definitions": metrics, "executed_at": GENERATED_AT, }, } def main() -> None: software = read_csv("software_modules.csv") hardware_groups = read_csv("hardware_groups.csv") hardware_categories = read_csv("hardware_categories.csv") bulk = read_csv("bulk_quantities.csv") quality = read_csv("data-quality-findings.csv") headline = [{ "project_contexts": 10, "software_modules": 5, "observed_hardware": 648, "unfilled_slots": 4, }] profile_source = source( "src_profile", "项目分析概况", """ SELECT 10 AS project_contexts, 5 AS software_modules, 648 AS observed_hardware, 4 AS unfilled_slots """, "汇总当前可审计项目场景、软件模块、可计数硬件观察值和待补项目槽位。", ["line_items", "software_module_evidence", "project_product_evidence_db"], [ "项目场景数：将同一客户项目的安装费拆行并回主项目后去重。", "可计数硬件：仅汇总台、套、个、块等耐用品；排除软件、服务、餐具、线缆长度和 LED 面积。", ], ) software_source = source( "src_software", "软件模块覆盖 SQL", """ SELECT module_id, module_name, COUNT(DISTINCT project_context_id) AS project_coverage_count, ROUND(100.0 * COUNT(DISTINCT project_context_id) / 10, 1) AS project_coverage_pct FROM software_module_evidence GROUP BY module_id, module_name ORDER BY project_coverage_count DESC, module_id """, "按项目场景去重，统计五类软件模块的证据覆盖。", ["software_module_evidence"], ["覆盖率 = 含该模块证据的项目场景数 / 10；硬件隐含证据和显式软件证据均保留标记。"], ) hardware_source = source( "src_hardware", "硬件分类与数量 SQL", """ SELECT hardware_group, hardware_category, COUNT(DISTINCT project_context_id) AS project_coverage_count, COUNT(*) AS source_line_count, ROUND(SUM(quantity), 2) AS observed_quantity, SUM(CASE WHEN quantity IS NULL THEN 1 ELSE 0 END) AS missing_quantity_lines FROM line_items WHERE record_type = 'hardware' GROUP BY hardware_group, hardware_category ORDER BY observed_quantity DESC """, "汇总耐用品硬件类别、观察数量和缺数量明细。", ["line_items"], ["观察数量为源表已填写数量之和；缺数量可能低估，重复版本可能高估，因此 648 不是最终净数量。"], ) bulk_source = source( "src_bulk", "批量材料分量纲 SQL", """ SELECT hardware_category AS bulk_category, unit, COUNT(DISTINCT project_context_id) AS project_coverage_count, ROUND(SUM(quantity), 2) AS observed_quantity, SUM(CASE WHEN quantity IS NULL THEN 1 ELSE 0 END) AS missing_quantity_lines FROM line_items WHERE record_type = 'bulk' GROUP BY hardware_category, unit ORDER BY hardware_category, unit """, "按类别和单位分开汇总餐具、线缆、LED 面积与耗材。", ["line_items"], ["不同单位不可相加；LED 屏体、镀膜、配件和结构的相同平方米数是计价基数，不是四倍物理面积。"], ) quality_source = source( "src_quality", "数据质量审计", "SELECT finding_id, severity, finding, impact, recommendation FROM data_quality_findings ORDER BY finding_id", "列出覆盖范围、金额勾稽、单位和数量缺口。", ["data_quality_findings"], ["严重级别由对总体结论的影响确定；high 表示可改变全量性或数量结论。"], ) sources = [profile_source, software_source, hardware_source, bulk_source, quality_source] manifest = { "version": 1, "surface": "report", "title": TITLE, "description": "基于 2025-2026 可审计项目清单形成的软件模块覆盖、硬件类别数量和数据质量总体画像。", "generatedAt": GENERATED_AT, "cards": [ {"id": "card_projects", "description": "合并同一客户项目的安装费拆行后。", "dataset": "headline", "sourceId": "src_profile", "metrics": [{"label": "客户项目场景", "field": "project_contexts", "format": "number"}]}, {"id": "card_modules", "description": "从显式软件和可追溯设备能力证据归类。", "dataset": "headline", "sourceId": "src_profile", "metrics": [{"label": "软件能力模块", "field": "software_modules", "format": "number"}]}, {"id": "card_hardware", "description": "排除软件、服务、餐具、线缆长度和 LED 面积后的观察值；尚未消除缺数量与重复版本影响。", "dataset": "headline", "sourceId": "src_profile", "metrics": [{"label": "可计数硬件观察值", "field": "observed_hardware", "format": "number"}]}, {"id": "card_gaps", "description": "既有 15 项目证据库尚未补齐。", "dataset": "headline", "sourceId": "src_profile", "metrics": [{"label": "待补项目槽位", "field": "unfilled_slots", "format": "number"}]}, ], "charts": [ { "id": "software_coverage", "title": "软件模块项目覆盖", "subtitle": "交易结算覆盖 7/10 个项目场景；营养健康与系统集成各覆盖 4/10。", "type": "bar", "dataset": "software", "sourceId": "src_software", "options": {"orientation": "horizontal", "grouping": "single"}, "encodings": { "x": {"field": "module_name", "type": "nominal", "label": "软件模块"}, "y": {"field": "project_coverage_count", "type": "quantitative", "label": "覆盖项目场景数"}, "tooltip": [ {"field": "project_coverage_pct", "type": "quantitative", "label": "覆盖率", "format": "number"}, {"field": "evidence_item_count", "type": "quantitative", "label": "证据物料数"}, ], }, "valueFormat": "number", "layout": "full", }, { "id": "hardware_group_quantity", "title": "硬件大类观察数量", "subtitle": "前厅交易与身份、计算网络存储、营养展示三类合计 415 台/件，占观察值约 64%。", "type": "bar", "dataset": "hardware_groups", "sourceId": "src_hardware", "options": {"orientation": "horizontal", "grouping": "single"}, "encodings": { "x": {"field": "hardware_group", "type": "nominal", "label": "硬件大类"}, "y": {"field": "observed_quantity", "type": "quantitative", "label": "观察数量"}, "tooltip": [ {"field": "project_coverage_count", "type": "quantitative", "label": "覆盖项目场景"}, {"field": "missing_quantity_lines", "type": "quantitative", "label": "缺数量明细"}, ], }, "valueFormat": "number", "layout": "full", }, ], "tables": [ { "id": "hardware_categories", "title": "硬件细分类数量", "subtitle": "观察数量不等于最终签收数量；单位缺失和重复版本仍需回读。", "dataset": "hardware_categories", "sourceId": "src_hardware", "defaultSort": {"field": "observed_quantity", "direction": "desc"}, "density": "dense", "layout": "full", "columns": [ {"field": "hardware_group", "label": "硬件大类", "type": "text"}, {"field": "hardware_category", "label": "硬件细类", "type": "text"}, {"field": "observed_quantity", "label": "观察数量", "format": "number"}, {"field": "project_coverage_count", "label": "覆盖项目场景", "format": "number"}, {"field": "missing_quantity_lines", "label": "缺数量明细", "format": "number"}, {"field": "units", "label": "源表单位", "type": "text"}, ], }, { "id": "bulk_quantities", "title": "批量材料与餐具（按单位分开）", "subtitle": "餐具个数、线缆米数、网线箱数与 LED 平方米不能相加。", "dataset": "bulk", "sourceId": "src_bulk", "defaultSort": {"field": "observed_quantity", "direction": "desc"}, "density": "dense", "layout": "full", "columns": [ {"field": "bulk_category", "label": "类别", "type": "text"}, {"field": "unit", "label": "单位", "type": "text"}, {"field": "observed_quantity", "label": "观察数量", "format": "number"}, {"field": "project_coverage_count", "label": "覆盖项目场景", "format": "number"}, {"field": "missing_quantity_lines", "label": "缺数量明细", "format": "number"}, ], }, { "id": "quality_findings", "title": "数据质量与结论边界", "subtitle": "先解决高优先级问题，再把结果升级为历史全量项目画像。", "dataset": "quality", "sourceId": "src_quality", "defaultSort": {"field": "finding_id", "direction": "asc"}, "density": "dense", "layout": "full", "columns": [ {"field": "finding_id", "label": "编号", "type": "text"}, {"field": "severity", "label": "严重级别", "type": "text"}, {"field": "finding", "label": "问题", "type": "text"}, {"field": "impact", "label": "影响", "type": "text"}, {"field": "recommendation", "label": "建议", "type": "text"}, ], }, ], "sources": [{"id": item["id"], "label": item["label"], "path": item["path"]} for item in sources], "blocks": [ {"id": "title", "type": "markdown", "body": f"# {TITLE}"}, {"id": "executive_summary", "type": "markdown", "body": "## Executive Summary\n\n当前可审计结构化数据覆盖 **10 个客户项目场景**（11 条商务记录），形成 **5 类软件能力模块**，可计数硬件观察值为 **648 台/件**。交易结算是最稳定的软件底座；硬件集中在前厅交易身份、计算网络存储、营养展示和视频 AI。这个结果是 2025-2026 样本画像，不是公司自开展智慧团餐以来的历史全量；缺数量与重复版本会让最终净数量上调或下调。"}, {"id": "metrics", "type": "metric-strip", "cardIds": ["card_projects", "card_modules", "card_hardware", "card_gaps"], "layout": "full"}, {"id": "software_section", "type": "markdown", "body": "## 软件功能模块\n\n能力骨架已从单点收银扩展为 **交易结算 + 食安进销存 + 营养健康 + 移动入口 + 系统集成**。覆盖率描述样本内项目证据，不代表模块活跃度、使用深度或收入贡献。"}, {"id": "software_chart", "type": "chart", "chartId": "software_coverage", "layout": "full"}, {"id": "hardware_section", "type": "markdown", "body": "## 硬件类别与数量\n\n可计数硬件观察值为 **648 台/件**。其中称重/收银/消费终端 160、电子价签/显示终端 114、摄像/视频采集 98、机柜配线电源附件 68、保温餐炉 64。7 条硬件/材料明细缺数量可能造成低估，江西 206 重复版本风险可能造成高估，因此 648 不是最终签收净数量。"}, {"id": "hardware_chart", "type": "chart", "chartId": "hardware_group_quantity", "layout": "full"}, {"id": "hardware_table", "type": "table", "tableId": "hardware_categories", "layout": "full"}, {"id": "bulk_section", "type": "markdown", "body": "## 批量材料必须分量纲查看\n\n另有托盘与餐具 1,600 个/原表计数、线缆与光纤 1,200 米、网线 23 箱、LED 屏体 28.27 平方米。GOB 镀膜、配件包、结构装饰分别复用 28.27 平方米计价基数，不能重复解释成物理屏体面积。"}, {"id": "bulk_table", "type": "table", "tableId": "bulk_quantities", "layout": "full"}, {"id": "recommendation", "type": "markdown", "body": "## 建议\n\n先补齐统一项目台账和最终交付 BOM：项目 ID、商务/交付/验收状态、合同版本、软件标准 SKU、硬件物料编码、品牌型号、单位、数量、是否捆绑、部署位置和签收状态。补齐 15 项目库的 4 个空槽，再回填 2022-2024 已交付项目，之后才适合计算历史全量、标准复用率和软硬件毛利结构。"}, {"id": "quality_table", "type": "table", "tableId": "quality_findings", "layout": "full"}, {"id": "further_questions", "type": "markdown", "body": "## Further Questions\n\n1. 哪些微盘项目已经签约并完成验收，哪些只是售前或方案材料？\n2. 江西 206 项目的 399,820 元清单为何在明细中正好出现两倍金额？\n3. 软件模块是否有标准 SKU、版本、启用状态与实际活跃度记录？\n4. 能否补齐软件、硬件、实施的收入、成本、毛利、交付工时和续费？"}, {"id": "caveats", "type": "markdown", "body": "## Caveats and Assumptions\n\n- 分析窗口为 2025-01-22 至 2026-02-02，不是历史全量。\n- 11 条商务记录合并为 10 个项目场景；同客户安装费拆行不重复算项目。\n- 648 排除软件、服务、餐具、长度和面积，且有缺数量明细。\n- 本任务不是实验分析：没有随机分组、样本量、主指标和显著性，因此不调用 `/analyze-test` 输出 ship / extend / stop。"}, ], } snapshot = { "version": 1, "generatedAt": GENERATED_AT, "status": "ready", "datasets": { "headline": headline, "software": software, "hardware_groups": hardware_groups, "hardware_categories": hardware_categories, "bulk": bulk, "quality": quality, }, } artifact = { "surface": "report", "manifest": manifest, "snapshot": snapshot, "sources": sources, "package_info": { "originUrl": "artifact://smart-canteen-project-portfolio-analysis", "controls": {"edit": True, "refresh": True}, }, } (HERE / "artifact.json").write_text(json.dumps(artifact, ensure_ascii=False, indent=2) + "\n", encoding="utf-8") print(json.dumps({"artifact": repo_path(HERE / "artifact.json"), "datasets": {key: len(value) for key, value in snapshot["datasets"].items()}}, ensure_ascii=False, indent=2)) if __name__ == "__main__": main()