131 lines
4.9 KiB
Python
131 lines
4.9 KiB
Python
"""补充更多KPI-ERP数据同步
|
|
运行: python3 scripts/extend_erp_sync.py
|
|
"""
|
|
import sys, os, logging
|
|
from os import getenv
|
|
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
|
|
from app.database import get_session_local
|
|
from sqlalchemy import text, create_engine
|
|
from datetime import datetime
|
|
|
|
logging.basicConfig(level=logging.INFO, format='%(asctime)s [%(levelname)s] %(message)s')
|
|
logger = logging.getLogger('erp_extend')
|
|
|
|
erp_user = os.getenv("ERP_DB_USER", "zxbtest")
|
|
erp_pass = os.getenv("ERP_DB_PASS")
|
|
erp_host = os.getenv("ERP_DB_HOST", "211.149.143.215")
|
|
erp_port = os.getenv("ERP_DB_PORT", "1433")
|
|
erp_db = os.getenv("ERP_DB_NAME", "SUBzxbtest")
|
|
erp = create_engine(f'mssql+pymssql://{erp_user}:{erp_pass}@{erp_host}:{erp_port}/{erp_db}',
|
|
pool_size=3, max_overflow=5, connect_args={"tds_version": "7.0"})
|
|
db = get_session_local()()
|
|
|
|
kpi_map = {}
|
|
rows = db.execute(text("SELECT id, kpi_code FROM kpi_definitions")).fetchall()
|
|
for r in rows:
|
|
kpi_map[r[1]] = r[0]
|
|
|
|
batch = f"erp_sync_{datetime.now().strftime('%Y%m%d_%H%M')}"
|
|
written = 0
|
|
|
|
def p2ym(p):
|
|
base_year, base_period = 2021, 3
|
|
diff = p - base_period
|
|
year = base_year + diff // 12
|
|
month = (diff % 12) + 3
|
|
if month > 12: month -= 12; year += 1
|
|
return f"{year}-{month:02d}"
|
|
|
|
def write_kpi(code, period, value):
|
|
global written
|
|
kid = kpi_map.get(code)
|
|
if not kid or value is None: return
|
|
try:
|
|
db.execute(text("""
|
|
INSERT INTO kpi_values (kpi_id, period, actual_value, source_type, source_batch, data_status, calculated_at)
|
|
VALUES (:kpi_id, :period, :value, 'erp', :batch, 'verified', NOW())
|
|
ON DUPLICATE KEY UPDATE actual_value=:value2, source_batch=:batch2, data_status='verified', calculated_at=NOW()
|
|
"""), {"kpi_id": kid, "period": period, "value": float(value),
|
|
"batch": batch, "value2": float(value), "batch2": batch})
|
|
db.commit()
|
|
written += 1
|
|
except Exception as e:
|
|
db.rollback()
|
|
|
|
with erp.connect() as c:
|
|
# 获取全期间销售收入
|
|
rev_rows = c.execute(text("""
|
|
SELECT Period, SUM(SumMoney) as rev, COALESCE(SUM(SumCostMoney),0) as cost
|
|
FROM MasterBill WHERE BillType=1 AND BillState>=3
|
|
GROUP BY Period ORDER BY Period
|
|
""")).fetchall()
|
|
|
|
# 库存总额
|
|
total_inv = float(c.execute(text("SELECT COALESCE(SUM(CostMoney),0) FROM Storage WHERE CostMoney > 0")).fetchone()[0])
|
|
avg_inv = max(total_inv, 1)
|
|
|
|
# 应收账款总额
|
|
total_ar = float(c.execute(text("SELECT COALESCE(SUM(AReceive),0) FROM Units WHERE AReceive > 0")).fetchone()[0])
|
|
avg_ar = max(total_ar, 1)
|
|
|
|
for row in rev_rows:
|
|
p = row[0]
|
|
if p < 41: continue
|
|
ps = p2ym(p)
|
|
rev = float(row.rev)
|
|
cost = float(row.cost)
|
|
gross = round((rev - cost) / rev * 100, 2) if rev > 0 else 0
|
|
turnover = round(rev / avg_ar, 2) if avg_ar > 0 else 0
|
|
inv_turn = round(cost / avg_inv, 2) if avg_inv > 0 else 0
|
|
inv_days = round(365 / inv_turn, 1) if inv_turn > 0 else 0
|
|
|
|
write_kpi("F_REVENUE_001", ps, rev)
|
|
write_kpi("F_PROFIT_001", ps, gross)
|
|
write_kpi("F_AR_001", ps, turnover)
|
|
write_kpi("P_INV_001", ps, inv_turn)
|
|
write_kpi("P_INV_004", ps, inv_days)
|
|
|
|
# 前5客户集中度 和 逾期应收 另查
|
|
with erp.connect() as c:
|
|
for row in rev_rows:
|
|
p = row[0]
|
|
if p < 41: continue
|
|
ps = p2ym(p)
|
|
|
|
r = c.execute(text("""
|
|
SELECT TOP 5 SUM(SumMoney) as amt FROM (
|
|
SELECT SUM(SumMoney) as SumMoney FROM MasterBill
|
|
WHERE BillType=1 AND BillState>=3 AND Period=:p GROUP BY Unit_ID
|
|
) t ORDER BY amt DESC
|
|
"""), {"p": p}).fetchall()
|
|
top5 = sum(float(r2[0]) for r2 in r)
|
|
ratio = round(top5 / float(row.rev) * 100, 2) if float(row.rev) > 0 else 0
|
|
write_kpi("C_CUST_002", ps, ratio)
|
|
|
|
r2 = c.execute(text("""
|
|
SELECT SUM(CASE WHEN DATEDIFF(day, BillDate, GETDATE()) > 30 THEN SumMoney ELSE 0 END) as overdue
|
|
FROM MasterBill WHERE BillType=1 AND BillState>=3 AND Period=:p
|
|
"""), {"p": p}).fetchone()
|
|
overdue = float(r2[0]) if r2 and r2[0] else 0
|
|
overdue_ratio = round(overdue / float(row.rev) * 100, 2) if float(row.rev) > 0 else 0
|
|
write_kpi("F_AR_002", ps, overdue_ratio)
|
|
|
|
# 费用控制率
|
|
r = c.execute(text("""
|
|
SELECT Period,
|
|
SUM(CASE WHEN BillType=1 THEN SumMoney ELSE 0 END) as rev,
|
|
SUM(CASE WHEN BillType=30 THEN SumMoney ELSE 0 END) as expense
|
|
FROM MasterBill WHERE BillState>=3 GROUP BY Period ORDER BY Period
|
|
""")).fetchall()
|
|
for row in r:
|
|
p = row[0]
|
|
if p < 41: continue
|
|
ps = p2ym(p)
|
|
exp_rev = float(row.rev)
|
|
exp_amt = float(row.expense)
|
|
exp_ratio = round(exp_amt / exp_rev * 100, 2) if exp_rev > 0 else 0
|
|
write_kpi("F_COST_001", ps, exp_ratio)
|
|
|
|
db.close()
|
|
logger.info(f"完成! 共写入 {written} 条")
|