204 lines
11 KiB
Python
204 lines
11 KiB
Python
"""P2阶段: 预设KPI因果链（基于CMA四层因果链模型）
|
|
学习成长→内部流程→客户→财务
|
|
"""
|
|
import sys, os
|
|
sys.path.insert(0, os.path.dirname(os.path.dirname(__file__)))
|
|
|
|
from app.database import get_engine
|
|
from sqlalchemy import text
|
|
|
|
# 基于CMA四层因果链模型的预设因果关系
|
|
PRESET_CAUSALITIES = [
|
|
# ===== 学习成长 → 内部流程 =====
|
|
# 培训完成率 → 过程合格率
|
|
{"source": "L_TRAINING", "target": "P_PASS_RATE", "strength": 0.6, "lag": 2, "direction": "positive", "formula": "培训提升技能→过程质量提升"},
|
|
# 人均培训时长 → 缺陷率（负相关）
|
|
{"source": "L_TRAINING_HOURS", "target": "P_BUG_RATE", "strength": 0.5, "lag": 2, "direction": "negative", "formula": "更多培训→更少缺陷"},
|
|
# 员工满意度 → 返工率（负相关）
|
|
{"source": "L_EMPLOYEE_SAT", "target": "P_REWORK_RATE", "strength": 0.4, "lag": 1, "direction": "negative", "formula": "员工满意→更少返工"},
|
|
# 关键技术掌握率 → 交付及时率
|
|
{"source": "L_TECH_COVERAGE", "target": "P_DELIVERY", "strength": 0.7, "lag": 1, "direction": "positive", "formula": "技术掌握→交付提升"},
|
|
# 关键岗位胜任度 → 过程合格率
|
|
{"source": "L_COMPETENCY", "target": "P_PASS_RATE", "strength": 0.6, "lag": 1, "direction": "positive", "formula": "胜任度→质量提升"},
|
|
# 创新提案数量 → 新产品收入占比
|
|
{"source": "L_INNOVATION_COUNT", "target": "P_NEW_PROD_RATIO", "strength": 0.5, "lag": 3, "direction": "positive", "formula": "创新提案→新产品上市"},
|
|
# 数据自动化率 → 交付及时率
|
|
{"source": "L_DATA_AUTO_RATE", "target": "P_DELIVERY", "strength": 0.3, "lag": 1, "direction": "positive", "formula": "自动化→效率提升"},
|
|
# 系统覆盖率 → 供应链响应周期
|
|
{"source": "L_SYS_COVERAGE", "target": "P_SUPPLY_CYCLE", "strength": 0.4, "lag": 3, "direction": "negative", "formula": "系统覆盖→周期缩短"},
|
|
# 战略认知度 → 新产品收入占比
|
|
{"source": "L_STRATEGY_AWARE", "target": "P_NEW_PROD_RATIO", "strength": 0.3, "lag": 2, "direction": "positive", "formula": "战略理解→创新聚焦"},
|
|
|
|
# ===== 内部流程 → 客户 =====
|
|
# 过程合格率 → 客户满意度
|
|
{"source": "P_PASS_RATE", "target": "C_SATISFACTION", "strength": 0.7, "lag": 1, "direction": "positive", "formula": "质量提升→客户满意"},
|
|
# 交付及时率 → 客户满意度
|
|
{"source": "P_DELIVERY", "target": "C_SATISFACTION", "strength": 0.6, "lag": 0, "direction": "positive", "formula": "及时交付→客户满意"},
|
|
# 缺陷率 → 客户满意度（负相关）
|
|
{"source": "P_BUG_RATE", "target": "C_SATISFACTION", "strength": 0.5, "lag": 1, "direction": "negative", "formula": "缺陷多→不满意"},
|
|
# 返工率 → 客户满意度（负相关）
|
|
{"source": "P_REWORK_RATE", "target": "C_SATISFACTION", "strength": 0.3, "lag": 1, "direction": "negative", "formula": "返工多→不满意"},
|
|
# 供应链响应周期 → 交付及时率（负相关）
|
|
{"source": "P_SUPPLY_CYCLE", "target": "P_DELIVERY", "strength": 0.5, "lag": 1, "direction": "negative", "formula": "周期长→交付慢"},
|
|
# 产能利用率 → 交付及时率
|
|
{"source": "P_CAPACITY_UTIL", "target": "P_DELIVERY", "strength": 0.4, "lag": 0, "direction": "positive", "formula": "产能足→交付快"},
|
|
# 新产品收入占比 → 市场份额
|
|
{"source": "P_NEW_PROD_RATIO", "target": "C_MARKET_SHARE", "strength": 0.5, "lag": 3, "direction": "positive", "formula": "创新产品→市场占有率提升"},
|
|
|
|
# ===== 客户 → 财务 =====
|
|
# 客户满意度 → 客户保留率
|
|
{"source": "C_SATISFACTION", "target": "C_RETENTION_RATE", "strength": 0.8, "lag": 1, "direction": "positive", "formula": "满意→留存"},
|
|
# 客户满意度 → 营业收入
|
|
{"source": "C_SATISFACTION", "target": "F_REVENUE", "strength": 0.5, "lag": 2, "direction": "positive", "formula": "满意客户→复购增加"},
|
|
# 客户保留率 → 营业收入
|
|
{"source": "C_RETENTION_RATE", "target": "F_REVENUE", "strength": 0.6, "lag": 1, "direction": "positive", "formula": "老客户留存→稳定收入"},
|
|
# 客户保留率 → 客户生命周期价值
|
|
{"source": "C_RETENTION_RATE", "target": "C_LTV", "strength": 0.7, "lag": 2, "direction": "positive", "formula": "高留存→生命周期延长"},
|
|
# 新客户数 → 营业收入
|
|
{"source": "C_NEW_CLIENTS", "target": "F_REVENUE", "strength": 0.4, "lag": 1, "direction": "positive", "formula": "新客户→收入增长"},
|
|
# 市场份额 → 营业收入
|
|
{"source": "C_MARKET_SHARE", "target": "F_REVENUE", "strength": 0.5, "lag": 1, "direction": "positive", "formula": "市场扩大→收入增加"},
|
|
# 获客成本 → 净利润（负相关）
|
|
{"source": "C_ACQUISITION_COST", "target": "F_NET_PROFIT", "strength": 0.3, "lag": 1, "direction": "negative", "formula": "获客成本高→利润减少"},
|
|
# 客户集中度 → 净利润（负相关）
|
|
{"source": "C_CUST_CONCENTRATION", "target": "F_NET_PROFIT", "strength": 0.3, "lag": 1, "direction": "negative", "formula": "集中度高→风险增大"},
|
|
|
|
# ===== 财务内部因果 =====
|
|
# 营业收入 → 净利润
|
|
{"source": "F_REVENUE", "target": "F_NET_PROFIT", "strength": 0.7, "lag": 0, "direction": "positive", "formula": "收入增长→利润增加"},
|
|
# 毛利率 → 净利润
|
|
{"source": "F_GROSS_MARGIN", "target": "F_NET_PROFIT", "strength": 0.6, "lag": 0, "direction": "positive", "formula": "毛利提升→利润增加"},
|
|
# 费用率 → 净利润（负相关）
|
|
{"source": "F_COST_RATIO", "target": "F_NET_PROFIT", "strength": 0.5, "lag": 0, "direction": "negative", "formula": "费用高→利润减少"},
|
|
# 收入增长率 → 营业收入
|
|
{"source": "F_REVENUE_GROWTH", "target": "F_REVENUE", "strength": 0.8, "lag": 1, "direction": "positive", "formula": "增长加速→收入提升"},
|
|
# 净利润 → 经济增加值
|
|
{"source": "F_NET_PROFIT", "target": "F_EVA", "strength": 0.9, "lag": 0, "direction": "positive", "formula": "净利润→经济增加值"},
|
|
# 经营性现金流 → 净利润（滞后反馈）
|
|
{"source": "F_OP_CFLOW", "target": "F_NET_PROFIT", "strength": 0.4, "lag": 1, "direction": "positive", "formula": "现金充裕→运营改善"},
|
|
# 产品合格率 → 返工率（负相关）
|
|
{"source": "F_QUALITY_RATE", "target": "F_REWORK_RATE", "strength": 0.6, "lag": 1, "direction": "negative", "formula": "合格率高→返工少"},
|
|
# 应收账款周转天数 → 经营性现金流（负相关）
|
|
{"source": "F_AR_DAYS", "target": "F_OP_CFLOW", "strength": 0.5, "lag": 1, "direction": "negative", "formula": "回款慢→现金流紧张"},
|
|
# 流动比率 → 资产负债率
|
|
{"source": "F_CURRENT_RATIO", "target": "F_DEBT_RATIO", "strength": 0.3, "lag": 1, "direction": "negative", "formula": "流动性强→负债率低"},
|
|
|
|
# ===== 客户生命周期价值 =====
|
|
# 净推荐值(NPS) → 客户保留率
|
|
{"source": "C_NPS", "target": "C_RETENTION_RATE", "strength": 0.6, "lag": 1, "direction": "positive", "formula": "NPS高→留存好"},
|
|
# 客户生命周期价值 → 净利润
|
|
{"source": "C_LTV", "target": "F_NET_PROFIT", "strength": 0.5, "lag": 1, "direction": "positive", "formula": "客户终身价值→长期利润"},
|
|
]
|
|
|
|
|
|
def seed_causalities():
|
|
engine = get_engine()
|
|
with engine.connect() as conn:
|
|
# 先构建KPI代码→ID映射
|
|
kpis = conn.execute(
|
|
text("SELECT id, kpi_code FROM kpi_definitions WHERE status='active'")
|
|
).fetchall()
|
|
code_to_id = {r.kpi_code: r.id for r in kpis}
|
|
|
|
inserted = 0
|
|
skipped = 0
|
|
for item in PRESET_CAUSALITIES:
|
|
src_id = code_to_id.get(item["source"])
|
|
tgt_id = code_to_id.get(item["target"])
|
|
if not src_id:
|
|
print(f" ⚠️ 源KPI不存在: {item['source']}")
|
|
skipped += 1
|
|
continue
|
|
if not tgt_id:
|
|
print(f" ⚠️ 目标KPI不存在: {item['target']}")
|
|
skipped += 1
|
|
continue
|
|
|
|
# 检查是否已存在
|
|
existing = conn.execute(
|
|
text("SELECT id FROM kpi_causality WHERE source_kpi_id=:src AND target_kpi_id=:tgt"),
|
|
{"src": src_id, "tgt": tgt_id},
|
|
).fetchone()
|
|
if existing:
|
|
skipped += 1
|
|
continue
|
|
|
|
conn.execute(
|
|
text("""INSERT INTO kpi_causality
|
|
(source_kpi_id, target_kpi_id, strength, lag_months, formula, direction)
|
|
VALUES (:src, :tgt, :strength, :lag, :formula, :dir)"""),
|
|
{
|
|
"src": src_id, "tgt": tgt_id,
|
|
"strength": item["strength"],
|
|
"lag": item["lag"],
|
|
"formula": item["formula"],
|
|
"dir": item["direction"],
|
|
},
|
|
)
|
|
inserted += 1
|
|
|
|
conn.commit()
|
|
print(f"因果链: 新增{inserted}, 跳过{skipped}")
|
|
return inserted
|
|
|
|
|
|
def seed_bi_templates():
|
|
engine = get_engine()
|
|
PRESET_TEMPLATES = [
|
|
{
|
|
"name": "四层指标总览",
|
|
"report_type": "overview",
|
|
"is_system": 1,
|
|
"config": '{"description":"展示财务/客户/流程/学习四层维度KPI概览","layout":"grid","dimensions":["finance","customer","process","learning"],"metrics":["count","avg_value","alert_count"],"chart_type":"gauge_card"}',
|
|
},
|
|
{
|
|
"name": "同比趋势分析",
|
|
"report_type": "trend",
|
|
"is_system": 1,
|
|
"config": '{"description":"各KPI近12个月趋势对比","period":"monthly","window_months":12,"chart_type":"line","show_compare":true}',
|
|
},
|
|
{
|
|
"name": "实际vs预算对比",
|
|
"report_type": "comparison",
|
|
"is_system": 1,
|
|
"config": '{"description":"KPI实际值vs目标值偏差分析","chart_type":"bar","show_deviation":true,"group_by":"dimension"}',
|
|
},
|
|
{
|
|
"name": "TOP N异常KPI",
|
|
"report_type": "topn",
|
|
"is_system": 1,
|
|
"config": '{"description":"排名前N的异常KPI红黄灯","top_n":10,"sort_by":"deviation","chart_type":"horizontal_bar","show_threshold":true}',
|
|
},
|
|
{
|
|
"name": "因果链推演",
|
|
"report_type": "causality",
|
|
"is_system": 1,
|
|
"config": '{"description":"基于KPI因果链推演分析","chart_type":"force_graph","max_depth":3,"min_strength":0.3}',
|
|
},
|
|
]
|
|
|
|
with engine.connect() as conn:
|
|
inserted = 0
|
|
for tpl in PRESET_TEMPLATES:
|
|
existing = conn.execute(
|
|
text("SELECT id FROM bi_report_templates WHERE name=:name AND is_system=1"),
|
|
{"name": tpl["name"]},
|
|
).fetchone()
|
|
if existing:
|
|
continue
|
|
conn.execute(
|
|
text("INSERT INTO bi_report_templates (name, report_type, config, is_system) VALUES (:name, :type, :cfg, 1)"),
|
|
{"name": tpl["name"], "type": tpl["report_type"], "cfg": tpl["config"]},
|
|
)
|
|
inserted += 1
|
|
conn.commit()
|
|
print(f"报表模板: 新增{inserted}")
|
|
return inserted
|
|
|
|
|
|
if __name__ == "__main__":
|
|
print("=== 种子数据初始化 ===\n")
|
|
seed_causalities()
|
|
seed_bi_templates()
|
|
print("\n完成!")
|