Hermes CI Fix
451c994a86
Task: finance-20261003-cpa-customer-profitability-mvp-fullstack (P0-a / P0-b) - backend/app/utils/cpa_caliber.py (new): single source of truth for customer-level revenue aggregation from zxt_masterbill. Reused by alert_rules._customer_revenue_shares (dedupe: no second implementation). - backend/scripts/migrate_cpa_snapshot_20261004.py (new): idempotent DDL for customer_profitability_snapshot (UNIQUE entity_id+period+unit_id), --check / --apply / --rollback / --status / --show-create + journal. - backend/scripts/kpi_customer_profitability_deriver.py (new): monthly snapshot + kpi_values upsert (single transaction, idempotent). fail-closed: cogs_alloc / chan_sub / contrib / contrib_rate stay NULL and are never written as 0 or as an apportioned value. - backend/scripts/sql/cpa_kpi_defs_20261004.sql (new): C_CUST_REVENUE and C_CUST_COUNT active; 5 P1 codes registered inactive with fail-closed reason. - backend/app/api/customer_dashboard.py: new read-only endpoint GET /api/cma/customer-dashboard/standard-indicators returning the 9-card tri-state (value / no_data / undefined). Reads kpi_definitions via raw SQL to stay decoupled from the in-flight KPI direction DDL. - frontend/src/views/CustomerDashboard.vue: 9-card panel rendering the three states (never renders an empty value as 0). Anchor check 2026-01..09 (offline sqlite rehearsal, ALL_PASS=true): 73 customers / 4883.52 wan / top1 25.3835 / top2 23.4905 / top4 63.3249 / top10 81.7675 - all six match the task acceptance anchors. No production write and no deploy in this commit; release is gated.
124 lines
8.2 KiB
SQL
124 lines
8.2 KiB
SQL
-- CMA 客户获利能力（CPA / Customer Profitability）KPI 定义 — 2026-10-04
|
||
-- 任务: finance-20261003-cpa-customer-profitability-mvp-fullstack（P0-a；派单方 财务Bot，放行 任总 2026-10-03 18:17 CST DM「立」）
|
||
-- 主体范围: 陕西酣客 entity_id=1（zxt_masterbill 唯一有数主体）
|
||
-- 口径裁定与失败原因: 见 backend/app/utils/cpa_caliber.py 与
|
||
-- backend/scripts/kpi_customer_profitability_deriver.py 头部文档
|
||
--
|
||
-- 动作:
|
||
-- ① 新增 C_CUST_REVENUE 客户收入（万元） status=active ← 派生器当即可供值
|
||
-- ② 新增 C_CUST_COUNT 客户数（个） status=active ← 派生器当即可供值
|
||
-- ③ 新增 5 条 P1 定义（fail-closed，**不供值**）status=inactive ← 口径已登记，待数据/裁定
|
||
-- C_CUST_COGS_ALLOC / C_CUST_CHAN_SUB / C_CUST_CONTRIB / C_CUST_CONTRIB_RATE / C_CUST_AVG_CONTRIB
|
||
-- （inactive 而非 active：避免「有定义无值」在看板上伪装成「无数据」，P0-b 三态按「未定义·待建」显示）
|
||
-- * C_CUST_CONCENTRATION 复用既有 kpi_id=23（formula 前5大客户收入/总收入×100%），本 SQL 不新增/不改名；
|
||
-- 其 2026-01~09 九期现网值（source_batch finance-20261002-kpi6, verified）**原样保留**，派生器只补缺口期次。
|
||
--
|
||
-- 幂等: NOT EXISTS 守卫，可安全重跑（重跑=零副作用）
|
||
-- 注意: (entity_id, kpi_code) 唯一键 uk_entity_kpi
|
||
|
||
-- ① 客户收入（万元）
|
||
INSERT INTO kpi_definitions
|
||
(entity_id, kpi_code, kpi_name, dimension, category, formula, formula_desc,
|
||
data_source_type, data_source, data_owner, frequency, unit, target_calc_type,
|
||
kpi_level, status, epic, bot_source, data_source_config)
|
||
SELECT 1, 'C_CUST_REVENUE', '客户收入', 'customer', 'customer_profitability',
|
||
'Σ 客户销售额（万元）',
|
||
'期内客户收入合计 = Σ zxt_masterbill.sum_money（entity_id=1, unit_id>0, sum_money>0, bill_date 期内）÷10000；'
|
||
'口径单一事实源 app/utils/cpa_caliber.py（与客户集中度预警同源）',
|
||
'erp', '智享通销售单据 zxt_masterbill（unit_id=往来单位/客户）', '财务部', 'monthly', '万元', 'amount',
|
||
'strategic', 'active', 'Epic2', 'fullstack-bot',
|
||
'{"source_ids": [], "deriver": "scripts/kpi_customer_profitability_deriver.py", "basis": "zxt_masterbill.sum_money(含税原值)"}'
|
||
FROM DUAL
|
||
WHERE NOT EXISTS (SELECT 1 FROM (SELECT id FROM kpi_definitions
|
||
WHERE entity_id = 1 AND kpi_code = 'C_CUST_REVENUE') AS t);
|
||
|
||
-- ② 客户数（个）
|
||
INSERT INTO kpi_definitions
|
||
(entity_id, kpi_code, kpi_name, dimension, category, formula, formula_desc,
|
||
data_source_type, data_source, data_owner, frequency, unit, target_calc_type,
|
||
kpi_level, status, epic, bot_source, data_source_config)
|
||
SELECT 1, 'C_CUST_COUNT', '客户数', 'customer', 'customer_profitability',
|
||
'期内活跃客户数（个）',
|
||
'期内 zxt_masterbill 活跃往来单位数（unit_id 去重；含极小金额客户，与客户收入聚合同过滤）',
|
||
'erp', '智享通销售单据 zxt_masterbill', '财务部', 'monthly', '个', 'number',
|
||
'strategic', 'active', 'Epic2', 'fullstack-bot',
|
||
'{"source_ids": [], "deriver": "scripts/kpi_customer_profitability_deriver.py"}'
|
||
FROM DUAL
|
||
WHERE NOT EXISTS (SELECT 1 FROM (SELECT id FROM kpi_definitions
|
||
WHERE entity_id = 1 AND kpi_code = 'C_CUST_COUNT') AS t);
|
||
|
||
-- ③ P1 fail-closed 定义（口径已登记，供值待裁定/待打标；status=inactive）
|
||
INSERT INTO kpi_definitions
|
||
(entity_id, kpi_code, kpi_name, dimension, category, formula, formula_desc,
|
||
data_source_type, data_source, data_owner, frequency, unit, target_calc_type,
|
||
kpi_level, status, epic, bot_source, data_source_config)
|
||
SELECT 1, 'C_CUST_COGS_ALLOC', '客户销货成本(分摊)', 'customer', 'customer_profitability',
|
||
'客户收入 × 公司整体销货成本率(5401÷5001)',
|
||
'【P1 fail-closed 未供值】基数口径未决：客户收入基座 zxt_masterbill 含税单据流（2026-01~09=4,883.52万）'
|
||
'与凭证账 5401/5001（收入量级~900万）相差约5.4×，直接相乘无经济意义；且须与「统一后的 CVP 变动成本口径」'
|
||
'（finance-20261003-cvp-fix-and-bohai-coverage 第⑤项）一致 ⇒ 待财务裁定基数后落值。禁写 0/分摊值。',
|
||
'derived', '待建（scripts/kpi_customer_profitability_deriver.py 已留位）', '财务部', 'monthly', '万元', 'amount',
|
||
'strategic', 'inactive', 'Epic2', 'fullstack-bot',
|
||
'{"fail_closed": true, "reason_code": "cogs_base_undecided", "pending_on": "finance-20261003-cvp-fix-and-bohai-coverage#5"}'
|
||
FROM DUAL
|
||
WHERE NOT EXISTS (SELECT 1 FROM (SELECT id FROM kpi_definitions
|
||
WHERE entity_id = 1 AND kpi_code = 'C_CUST_COGS_ALLOC') AS t);
|
||
|
||
INSERT INTO kpi_definitions
|
||
(entity_id, kpi_code, kpi_name, dimension, category, formula, formula_desc,
|
||
data_source_type, data_source, data_owner, frequency, unit, target_calc_type,
|
||
kpi_level, status, epic, bot_source, data_source_config)
|
||
SELECT 1, 'C_CUST_CHAN_SUB', '客户渠道补贴', 'customer', 'customer_profitability',
|
||
'Σ 客户专属渠道补贴',
|
||
'【P1 fail-closed 未供值】571103 渠道补贴目前仅有总额、不可按客户/渠道拆分；须先落「渠道补贴台账」'
|
||
'（人员/渠道/客户 × 期间 × 金额）再取值。禁按收入占比硬摊。',
|
||
'derived', '待建（渠道补贴打标台账）', '财务部', 'monthly', '万元', 'amount',
|
||
'strategic', 'inactive', 'Epic2', 'fullstack-bot',
|
||
'{"fail_closed": true, "reason_code": "channel_subsidy_untagged"}'
|
||
FROM DUAL
|
||
WHERE NOT EXISTS (SELECT 1 FROM (SELECT id FROM kpi_definitions
|
||
WHERE entity_id = 1 AND kpi_code = 'C_CUST_CHAN_SUB') AS t);
|
||
|
||
INSERT INTO kpi_definitions
|
||
(entity_id, kpi_code, kpi_name, dimension, category, formula, formula_desc,
|
||
data_source_type, data_source, data_owner, frequency, unit, target_calc_type,
|
||
kpi_level, status, epic, bot_source, data_source_config)
|
||
SELECT 1, 'C_CUST_CONTRIB', '客户贡献毛利', 'customer', 'customer_profitability',
|
||
'客户收入 − 客户销货成本(分摊) − 客户专属渠道补贴',
|
||
'【P1 fail-closed 未供值】依赖 C_CUST_COGS_ALLOC 与 C_CUST_CHAN_SUB，二者皆未落值 ⇒ 不计算、不写 0。',
|
||
'derived', '待建', '财务部', 'monthly', '万元', 'amount',
|
||
'strategic', 'inactive', 'Epic2', 'fullstack-bot',
|
||
'{"fail_closed": true, "reason_code": "depends_on_cogs_alloc_and_chan_sub"}'
|
||
FROM DUAL
|
||
WHERE NOT EXISTS (SELECT 1 FROM (SELECT id FROM kpi_definitions
|
||
WHERE entity_id = 1 AND kpi_code = 'C_CUST_CONTRIB') AS t);
|
||
|
||
INSERT INTO kpi_definitions
|
||
(entity_id, kpi_code, kpi_name, dimension, category, formula, formula_desc,
|
||
data_source_type, data_source, data_owner, frequency, unit, target_calc_type,
|
||
kpi_level, status, epic, bot_source, data_source_config)
|
||
SELECT 1, 'C_CUST_CONTRIB_RATE', '客户贡献毛利率', 'customer', 'customer_profitability',
|
||
'客户贡献毛利 ÷ 客户收入 × 100%',
|
||
'【P1 fail-closed 未供值】依赖 C_CUST_CONTRIB ⇒ 不计算。',
|
||
'derived', '待建', '财务部', 'monthly', '%', 'ratio',
|
||
'strategic', 'inactive', 'Epic2', 'fullstack-bot',
|
||
'{"fail_closed": true, "reason_code": "depends_on_contrib"}'
|
||
FROM DUAL
|
||
WHERE NOT EXISTS (SELECT 1 FROM (SELECT id FROM kpi_definitions
|
||
WHERE entity_id = 1 AND kpi_code = 'C_CUST_CONTRIB_RATE') AS t);
|
||
|
||
INSERT INTO kpi_definitions
|
||
(entity_id, kpi_code, kpi_name, dimension, category, formula, formula_desc,
|
||
data_source_type, data_source, data_owner, frequency, unit, target_calc_type,
|
||
kpi_level, status, epic, bot_source, data_source_config)
|
||
SELECT 1, 'C_CUST_AVG_CONTRIB', '客户平均贡献值', 'customer', 'customer_profitability',
|
||
'期内客户贡献毛利合计 ÷ 期内活跃客户数',
|
||
'【P1 fail-closed 未供值】依赖 C_CUST_CONTRIB ⇒ 不计算。页面「客户平均贡献值」卡即此码；'
|
||
'P0 阶段按「未定义·待建」显示（定义已登记但 inactive）。',
|
||
'derived', '待建', '财务部', 'monthly', '万元', 'amount',
|
||
'strategic', 'inactive', 'Epic2', 'fullstack-bot',
|
||
'{"fail_closed": true, "reason_code": "depends_on_contrib"}'
|
||
FROM DUAL
|
||
WHERE NOT EXISTS (SELECT 1 FROM (SELECT id FROM kpi_definitions
|
||
WHERE entity_id = 1 AND kpi_code = 'C_CUST_AVG_CONTRIB') AS t);
|