143 lines
6.3 KiB
Python
143 lines
6.3 KiB
Python
"""
|
|
方案C：重置KPI字典 — 使用原生SQL以绕过ORM外键约束
|
|
"""
|
|
import sys
|
|
import 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
|
|
|
|
engine = get_engine()
|
|
|
|
with engine.connect() as conn:
|
|
print("=" * 60)
|
|
print("方案C：重置KPI字典")
|
|
print("=" * 60)
|
|
|
|
# 步骤1：先禁用外键检查，干净删除
|
|
print("\n[1/4] 删除旧数据...")
|
|
conn.execute(text("SET FOREIGN_KEY_CHECKS = 0"))
|
|
|
|
# 清理顺序：依赖链最深的先删
|
|
for tbl in [
|
|
"notification_logs",
|
|
"kpi_alerts",
|
|
"action_plans",
|
|
"kpi_values",
|
|
"operation_logs",
|
|
"kpi_definitions",
|
|
]:
|
|
r = conn.execute(text(f"DELETE FROM {tbl}"))
|
|
print(f" 已清空 {tbl}: {r.rowcount} 条")
|
|
|
|
conn.execute(text("SET FOREIGN_KEY_CHECKS = 1"))
|
|
conn.commit()
|
|
|
|
# 步骤2：从模板实例化标准KPI
|
|
print("\n[2/4] 从模板实例化标准KPI...")
|
|
|
|
rows = conn.execute(text(
|
|
"SELECT id, kpi_code, kpi_name, dimension, category, formula, formula_desc, "
|
|
"unit, target_value, description FROM kpi_templates WHERE is_system=1 ORDER BY kpi_code"
|
|
)).fetchall()
|
|
print(f" 共 {len(rows)} 个系统模板")
|
|
|
|
created = 0
|
|
for r in rows:
|
|
conn.execute(text(
|
|
"INSERT INTO kpi_definitions (template_id, is_system, kpi_code, kpi_name, "
|
|
"dimension, category, formula, formula_desc, unit, target_value, "
|
|
"data_source_type, frequency, status) "
|
|
"VALUES (:tid, 1, :code, :name, :dim, :cat, :formula, :fdesc, :unit, :target, 'manual', 'monthly', 'active')"
|
|
), {
|
|
"tid": r[0], "code": r[1], "name": r[2], "dim": r[3], "cat": r[4],
|
|
"formula": r[5], "fdesc": r[6], "unit": r[7] or "%", "target": r[8]
|
|
})
|
|
# 更新usage_count
|
|
conn.execute(text("UPDATE kpi_templates SET usage_count = IFNULL(usage_count,0)+1 WHERE id=:id"), {"id": r[0]})
|
|
created += 1
|
|
|
|
# 步骤3：补充额外KPI
|
|
print("\n[3/4] 补充额外KPI...")
|
|
|
|
extra_kpis = [
|
|
("F_REVENUE_GROWTH", "收入增长率", "finance", "revenue_growth", "(本期收入-上期收入)/上期收入*100", "%", 15.0),
|
|
("F_ROE", "净资产收益率(ROE)", "finance", "profitability", "净利润/净资产*100", "%", 12.0),
|
|
("F_AR_TURNOVER", "应收账款周转率", "finance", "asset_efficiency", "营业收入/平均应收账款", "次", 6.0),
|
|
("F_DEBT_RATIO", "资产负债率", "finance", "cash_risk", "总负债/总资产*100", "%", 50.0),
|
|
|
|
("C_MARKET_SHARE", "市场份额", "customer", "customer_scale", "本公司销售额/行业总销售额*100", "%", None),
|
|
("C_CAC", "新客户获取成本(CAC)", "customer", "customer_scale", "销售费用/新客户数", "元", None),
|
|
("C_CLV", "客户生命周期价值(CLV)", "customer", "customer_scale", "平均客单价*复购次数*毛利率", "元", None),
|
|
("C_RETENTION", "客户留存率", "customer", "customer_scale", "期末客户数/期初客户数*100", "%", 85.0),
|
|
("C_NPS", "净推荐值(NPS)", "customer", "customer_satisfaction", "推荐者占比-贬损者占比", "分", 50.0),
|
|
|
|
("P_CAPACITY", "产能利用率", "process", "supply_chain", "实际产量/设计产能*100", "%", 85.0),
|
|
("P_OEE", "设备综合效率(OEE)", "process", "supply_chain", "可用率*表现率*质量率*100", "%", 75.0),
|
|
("P_INV_TURNOVER", "存货周转率", "process", "supply_chain", "营业成本/平均存货", "次", 8.0),
|
|
("P_FIRST_PASS", "产品一次合格率", "process", "delivery_quality", "一次合格数/总检验数*100", "%", 98.0),
|
|
|
|
("L_PER_CAPITA", "人均产值", "learning", "employee_engagement", "营业收入/员工总数", "万元", None),
|
|
("L_HR_SATISFACTION", "员工满意度指数", "learning", "employee_engagement", "满意度调查得分", "分", 85.0),
|
|
("L_SYSTEM_COVERAGE", "信息系统覆盖率", "learning", "innovation", "已系统化业务流程数/总业务流程数*100", "%", 70.0),
|
|
]
|
|
|
|
ek_created = 0
|
|
for code, name, dim, cat, formula, unit, target in extra_kpis:
|
|
existing = conn.execute(text("SELECT COUNT(*) FROM kpi_definitions WHERE kpi_code=:code"), {"code": code}).scalar()
|
|
if existing > 0:
|
|
continue
|
|
conn.execute(text(
|
|
"INSERT INTO kpi_definitions (is_system, kpi_code, kpi_name, dimension, category, "
|
|
"formula, unit, target_value, data_source_type, frequency, status) "
|
|
"VALUES (1, :code, :name, :dim, :cat, :formula, :unit, :target, 'manual', 'monthly', 'active')"
|
|
), {"code": code, "name": name, "dim": dim, "cat": cat,
|
|
"formula": formula, "unit": unit, "target": target})
|
|
ek_created += 1
|
|
|
|
print(f" 补充了 {ek_created} 条额外KPI")
|
|
|
|
# 步骤4：标记ERP数据源
|
|
print("\n[4/4] 标记ERP数据源...")
|
|
erp_codes = [
|
|
"F_REVENUE", "C_CUSTOMER_COUNT", "F_PROFIT_RATE", "F_NET_PROFIT_RATE",
|
|
"C_CUSTOMER_CONCENTRATION", "F_CASH_FLOW", "C_CAC",
|
|
"F_REVENUE_GROWTH", "F_AR_TURNOVER", "P_INV_TURNOVER", "F_ROE",
|
|
]
|
|
for code in erp_codes:
|
|
r = conn.execute(text(
|
|
"UPDATE kpi_definitions SET data_source_type='erp' WHERE kpi_code=:code"
|
|
), {"code": code})
|
|
if r.rowcount > 0:
|
|
print(f" {code}: ERP")
|
|
|
|
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()
|
|
|
|
print(f"\n{'=' * 60}")
|
|
print(f"完成！KPI字典共 {total} 条")
|
|
for d, c in by_dim:
|
|
print(f" {d}: {c} 条")
|
|
|
|
# 打印所有KPI
|
|
print(f"\n{'=' * 60}")
|
|
print("新KPI字典清单:")
|
|
print(f"{'=' * 60}")
|
|
all_kpis = conn.execute(text(
|
|
"SELECT id, kpi_code, kpi_name, dimension, data_source_type FROM kpi_definitions WHERE status='active' ORDER BY kpi_code"
|
|
)).fetchall()
|
|
for r in all_kpis:
|
|
src = "ERP" if r[4] == "erp" else "手动"
|
|
print(f" [{r[0]:2d}] {r[1]:30s} {r[2]:20s} {r[3]:12s} [{src}]")
|
|
|
|
print("\n✅ 重置完成")
|