257 lines
9.0 KiB
Python
257 lines
9.0 KiB
Python
""""
|
|
KPI字典库优化P1阶段 — Tasks 1-4: 新增9个P1级KPI
|
|
执行: cd /root/cma-management/backend && python3 scripts/kpi_p1_optimization.py
|
|
"""
|
|
import sys, os
|
|
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
|
|
|
|
from dotenv import load_dotenv
|
|
load_dotenv()
|
|
|
|
from app.database import get_engine
|
|
from sqlalchemy import text
|
|
from datetime import datetime
|
|
|
|
engine = get_engine()
|
|
|
|
def log(msg):
|
|
print(f"[{datetime.now():%H:%M:%S}] {msg}")
|
|
|
|
def insert_kpi(conn, **kw):
|
|
code = kw["kpi_code"]
|
|
exists = conn.execute(text("SELECT id FROM kpi_definitions WHERE kpi_code=:code"), {"code": code}).fetchone()
|
|
if exists:
|
|
log(f" ⏭️ 已存在: {code} (id={exists[0]})")
|
|
return exists[0], False
|
|
cols = ", ".join(kw.keys())
|
|
vals = ", ".join(f":{k}" for k in kw.keys())
|
|
conn.execute(text(f"INSERT INTO kpi_definitions ({cols}) VALUES ({vals})"), kw)
|
|
new_id = conn.execute(text("SELECT LAST_INSERT_ID()")).scalar()
|
|
log(f" ✅ 新增: {code} (id={new_id})")
|
|
return new_id, True
|
|
|
|
def activate_template(conn, template_code, target_code, target_name, threshold_green, threshold_yellow, threshold_red, data_source_type="manual", frequency="monthly"):
|
|
"""从模板激活KPI"""
|
|
t = conn.execute(text(
|
|
"SELECT id, kpi_code, kpi_name, dimension, category, formula, formula_desc, "
|
|
"unit, target_value, description FROM kpi_templates WHERE kpi_code=:code"
|
|
), {"code": template_code}).fetchone()
|
|
if not t:
|
|
log(f" ❌ 模板不存在: {template_code}")
|
|
return None, False
|
|
nid, is_new = insert_kpi(conn,
|
|
template_id=t[0],
|
|
is_system=1,
|
|
kpi_code=target_code,
|
|
kpi_name=target_name or t[2],
|
|
dimension=t[3],
|
|
category=t[4],
|
|
formula=t[5] or "",
|
|
formula_desc=t[6] or t[9] or "",
|
|
unit=t[7] or "%",
|
|
target_value=t[8],
|
|
data_source_type=data_source_type,
|
|
frequency=frequency,
|
|
status="active",
|
|
epic="Epic2",
|
|
threshold_green=threshold_green,
|
|
threshold_yellow=threshold_yellow,
|
|
threshold_red=threshold_red,
|
|
created_at=datetime.now(),
|
|
updated_at=datetime.now(),
|
|
)
|
|
if is_new:
|
|
conn.execute(text("UPDATE kpi_templates SET usage_count = IFNULL(usage_count,0)+1 WHERE id=:id"), {"id": t[0]})
|
|
return nid, is_new
|
|
|
|
with engine.connect() as conn:
|
|
print("=" * 70)
|
|
log("开始KPI字典库优化P1 — 任务1~4: 新增9个P1级KPI")
|
|
print("=" * 70)
|
|
|
|
# =========================================================
|
|
# 任务1: 新增财务P1级KPI（3个）
|
|
# =========================================================
|
|
log("\n【任务1】新增财务P1级KPI")
|
|
print("-" * 50)
|
|
|
|
finance_kpis = [
|
|
("F_DEBT_RATIO", "资产负债率", "finance", "cash_risk",
|
|
"总负债/总资产", "%", 50.0, "<50", "<70", ">=70", "erp"),
|
|
("F_INTEREST_COVER", "利息保障倍数", "finance", "profitability",
|
|
"EBIT/利息费用", "倍", 5.0, ">5", ">2", "<=2", "erp"),
|
|
("F_EVA", "经济增加值(EVA)", "finance", "profitability",
|
|
"税后净营业利润-资本成本", "元", 0.0, ">0", ">-100000", "<=-100000", "erp"),
|
|
]
|
|
|
|
for code, name, dim, cat, formula, unit, target, tg, ty, tr, src in finance_kpis:
|
|
nid, is_new = insert_kpi(conn,
|
|
is_system=0,
|
|
kpi_code=code,
|
|
kpi_name=name,
|
|
dimension=dim,
|
|
category=cat,
|
|
formula=formula,
|
|
formula_desc=f"CMA标准{name}指标",
|
|
unit=unit,
|
|
target_value=target,
|
|
threshold_green=tg,
|
|
threshold_yellow=ty,
|
|
threshold_red=tr,
|
|
data_source_type=src,
|
|
frequency="monthly",
|
|
status="active",
|
|
epic="Epic2",
|
|
created_at=datetime.now(),
|
|
updated_at=datetime.now(),
|
|
)
|
|
|
|
# =========================================================
|
|
# 任务2: 新增客户P1级KPI（2个）
|
|
# =========================================================
|
|
log("\n【任务2】新增客户P1级KPI")
|
|
print("-" * 50)
|
|
|
|
customer_kpis = [
|
|
("C_MARKET_SHARE", "市场份额", "customer", "customer_scale",
|
|
"公司收入/行业总收入", "%", 10.0, ">10", ">5", "<=5", "erp"),
|
|
("C_NPS", "净推荐值(NPS)", "customer", "customer_satisfaction",
|
|
"NPS评分(-100~100)", "分", 50.0, ">50", ">0", "<=0", "manual"),
|
|
]
|
|
|
|
for code, name, dim, cat, formula, unit, target, tg, ty, tr, src in customer_kpis:
|
|
nid, is_new = insert_kpi(conn,
|
|
is_system=0,
|
|
kpi_code=code,
|
|
kpi_name=name,
|
|
dimension=dim,
|
|
category=cat,
|
|
formula=formula,
|
|
formula_desc=f"CMA标准{name}指标",
|
|
unit=unit,
|
|
target_value=target,
|
|
threshold_green=tg,
|
|
threshold_yellow=ty,
|
|
threshold_red=tr,
|
|
data_source_type=src,
|
|
frequency="monthly",
|
|
status="active",
|
|
epic="Epic2",
|
|
created_at=datetime.now(),
|
|
updated_at=datetime.now(),
|
|
)
|
|
|
|
# =========================================================
|
|
# 任务3: 新增流程P1级KPI（3个）
|
|
# =========================================================
|
|
log("\n【任务3】新增流程P1级KPI")
|
|
print("-" * 50)
|
|
|
|
# 3.1 激活P_SUPPLY_CYCLE模板
|
|
log(" --- 激活模板: P_SUPPLY_CYCLE (供应链响应周期)")
|
|
activate_template(conn, "P_SUPPLY_CYCLE", "P_SUPPLY_CYCLE", None,
|
|
"<=7", "<=14", ">14", "erp", "monthly")
|
|
|
|
# 3.2 产能利用率
|
|
insert_kpi(conn,
|
|
is_system=0,
|
|
kpi_code="P_CAPACITY_UTIL",
|
|
kpi_name="产能利用率",
|
|
dimension="process",
|
|
category="supply_chain",
|
|
formula="实际产出/理论产能",
|
|
formula_desc="CMA标准产能利用率指标，反映生产资源利用效率",
|
|
unit="%",
|
|
target_value=85.0,
|
|
threshold_green=">85",
|
|
threshold_yellow=">70",
|
|
threshold_red="<=70",
|
|
data_source_type="erp",
|
|
frequency="monthly",
|
|
status="active",
|
|
epic="Epic2",
|
|
created_at=datetime.now(),
|
|
updated_at=datetime.now(),
|
|
)
|
|
|
|
# 3.3 研发投入占比
|
|
insert_kpi(conn,
|
|
is_system=0,
|
|
kpi_code="P_RD_RATIO",
|
|
kpi_name="研发投入占比",
|
|
dimension="process",
|
|
category="innovation",
|
|
formula="研发费用/收入",
|
|
formula_desc="CMA标准研发投入占比指标，衡量创新投入力度",
|
|
unit="%",
|
|
target_value=5.0,
|
|
threshold_green=">5",
|
|
threshold_yellow=">2",
|
|
threshold_red="<=2",
|
|
data_source_type="erp",
|
|
frequency="monthly",
|
|
status="active",
|
|
epic="Epic2",
|
|
created_at=datetime.now(),
|
|
updated_at=datetime.now(),
|
|
)
|
|
|
|
# =========================================================
|
|
# 任务4: 新增学习P1级KPI（2个）
|
|
# =========================================================
|
|
log("\n【任务4】新增学习P1级KPI")
|
|
print("-" * 50)
|
|
|
|
# 4.1 激活L_INNOVATION_COUNT模板
|
|
log(" --- 激活模板: L_INNOVATION_COUNT (创新提案数量)")
|
|
activate_template(conn, "L_INNOVATION_COUNT", "L_INNOVATION_COUNT", None,
|
|
">=12", ">=6", "<6", "manual", "monthly")
|
|
|
|
# 4.2 战略认知度
|
|
insert_kpi(conn,
|
|
is_system=0,
|
|
kpi_code="L_STRATEGY_AWARE",
|
|
kpi_name="战略认知度",
|
|
dimension="learning",
|
|
category="employee_engagement",
|
|
formula="员工战略理解度评分",
|
|
formula_desc="CMA标准战略认知度指标，通过员工调研获取",
|
|
unit="分",
|
|
target_value=80.0,
|
|
threshold_green=">80",
|
|
threshold_yellow=">60",
|
|
threshold_red="<=60",
|
|
data_source_type="manual",
|
|
frequency="quarterly",
|
|
status="active",
|
|
epic="Epic2",
|
|
created_at=datetime.now(),
|
|
updated_at=datetime.now(),
|
|
)
|
|
|
|
# =========================================================
|
|
# 提交事务
|
|
# =========================================================
|
|
conn.commit()
|
|
|
|
# 统计
|
|
total = conn.execute(text("SELECT COUNT(*) FROM kpi_definitions WHERE status='active'")).scalar()
|
|
by_dim = conn.execute(text(
|
|
"SELECT dimension, COUNT(*) FROM kpi_definitions WHERE status='active' GROUP BY dimension ORDER BY dimension"
|
|
)).fetchall()
|
|
no_threshold = conn.execute(text(
|
|
"SELECT kpi_code, kpi_name FROM kpi_definitions WHERE status='active' AND (threshold_green IS NULL OR threshold_yellow IS NULL OR threshold_red IS NULL)"
|
|
)).fetchall()
|
|
|
|
print("\n" + "=" * 70)
|
|
log(f"✅ 任务1~4完成！KPI字典共 {total} 条")
|
|
for d, c in by_dim:
|
|
print(f" {d}: {c} 条")
|
|
if no_threshold:
|
|
print(f"\n⚠️ 以下KPI仍缺阈值:")
|
|
for code, name in no_threshold:
|
|
print(f" {code}: {name}")
|
|
else:
|
|
print("\n✅ 所有KPI均有阈值配置")
|
|
print("=" * 70)
|