265 lines
10 KiB
Python
Executable File
265 lines
10 KiB
Python
Executable File
"""定开项目财务口径:时间归属 + 季度 / 年度统计 + 年度计收目标。
|
||
|
||
历史数据有三个坑,统一在这里处理,前端只消费结果:
|
||
· `accept_date` 只写季度(Q1~Q4)没有年份,跨年就混在一起(2025Q3 和 2026Q3
|
||
都算「Q3」)→ 新增 `accept_year`,启动时按真实验收日期回填一次;
|
||
· `accept_date_real` 是当年导入脚本按「季度末」补的占位日期,不能当统计依据 → 停用;
|
||
· `sign_date` 存过自由文本(「2024年3月前」)→ 能识别出年份的才进年/季度口径。
|
||
|
||
统计周期以顶栏选中的周期为准:`period_id`(周)或 `year+month`(月),
|
||
由后端换算成所属「年份 + 季度」,切到历史周期看到的就还是那一年的数。
|
||
"""
|
||
from __future__ import annotations
|
||
|
||
import json
|
||
from datetime import date
|
||
|
||
from sqlalchemy.orm import Session
|
||
|
||
from .constants import (
|
||
CUSTOM_ANNUAL_TARGETS_DEFAULT,
|
||
CUSTOM_PLACED_ORIGINS,
|
||
derive_stage,
|
||
parse_quarter,
|
||
parse_year_month,
|
||
quarter_end,
|
||
quarter_of_month,
|
||
)
|
||
from .models import CustomProject, Period, Setting, now
|
||
|
||
__all__ = [
|
||
"period_context", "accept_period", "sign_period", "accept_label", "is_placed",
|
||
"summary", "read_annual_targets", "write_annual_targets", "backfill",
|
||
]
|
||
|
||
|
||
# ---------------------------------------------------------------- 统计上下文
|
||
def period_context(db: Session, period_id: int | None = None,
|
||
year: int | None = None, month: int | None = None) -> dict:
|
||
"""把顶栏选中的周期换算成「年份 + 季度」,没选就取今天所在的年月。"""
|
||
label = ""
|
||
if period_id:
|
||
per = db.get(Period, period_id)
|
||
if per is not None:
|
||
year, month = per.year, per.month
|
||
label = per.full_label
|
||
if not year or not month:
|
||
today = date.today()
|
||
year = int(year) if year else today.year
|
||
month = int(month) if month else today.month
|
||
month = min(12, max(1, int(month)))
|
||
year = int(year)
|
||
q = quarter_of_month(month)
|
||
return {
|
||
"period_id": period_id,
|
||
"year": year,
|
||
"month": month,
|
||
"quarter": q,
|
||
"label": label or f"{year}年{month}月",
|
||
"quarter_label": f"{year}年Q{q}",
|
||
"quarter_start": f"{year}-{(q - 1) * 3 + 1:02d}-01",
|
||
"quarter_end": quarter_end(year, q),
|
||
}
|
||
|
||
|
||
# ---------------------------------------------------------------- 时间归属
|
||
def is_placed(cp: CustomProject) -> bool:
|
||
"""已下单 = 项目归属属于「华智下单 / 存量项目 / 汇智直签」。"""
|
||
return (cp.origin or "").strip() in CUSTOM_PLACED_ORIGINS
|
||
|
||
|
||
def sign_period(cp: CustomProject) -> tuple[int | None, int | None]:
|
||
"""下单时间 → (年份, 季度);文本认不出年份时返回 (None, None)。"""
|
||
ym = parse_year_month(cp.sign_date)
|
||
if not ym:
|
||
return None, None
|
||
year, month = ym
|
||
return year, quarter_of_month(month) if month else None
|
||
|
||
|
||
def accept_period(cp: CustomProject) -> tuple[int | None, int | None]:
|
||
"""验收时间 → (年份, 季度)。年份只认 `accept_year`(缺年度就按「未定」不计入任何年份)。
|
||
|
||
刻意不看 `accept_date_real`:那是导入脚本按季度末补的占位日期,
|
||
用它兜底会把「没填验收时间」的项目算进某一年,清掉年度也清不掉。
|
||
老数据的一次性补齐在 `backfill()` 里做。
|
||
"""
|
||
return (cp.accept_year or None), parse_quarter(cp.accept_date)
|
||
|
||
|
||
def accept_label(cp: CustomProject) -> str:
|
||
"""界面上的验收时间:「2026Q3」;缺什么显示什么,全空返回空串。"""
|
||
year, q = accept_period(cp)
|
||
if year and q:
|
||
return f"{year}Q{q}"
|
||
if q:
|
||
return f"Q{q}"
|
||
return str(year) if year else ""
|
||
|
||
|
||
# ---------------------------------------------------------------- 年度计收目标
|
||
ANNUAL_TARGETS_PREFIX = "custom_annual_targets"
|
||
|
||
|
||
def _targets_key(year: int) -> str:
|
||
return f"{ANNUAL_TARGETS_PREFIX}:{year}"
|
||
|
||
|
||
def read_annual_targets(db: Session, year: int) -> dict[str, float]:
|
||
"""按年取四个季度的计收目标(万元);没配过的年份用默认值。"""
|
||
row = db.get(Setting, _targets_key(year))
|
||
# 没配过的年份用默认值;配过就以库里为准——在界面上清空某一季,读回来就得是 0
|
||
saved = None
|
||
if row and row.value:
|
||
try:
|
||
parsed = json.loads(row.value)
|
||
saved = parsed if isinstance(parsed, dict) else None
|
||
except (TypeError, ValueError):
|
||
saved = None
|
||
if saved is None:
|
||
saved = {str(q): v for q, v in CUSTOM_ANNUAL_TARGETS_DEFAULT.items()}
|
||
targets: dict[str, float] = {}
|
||
for q in (1, 2, 3, 4):
|
||
v = saved.get(str(q))
|
||
try:
|
||
targets[str(q)] = 0.0 if v in (None, "") else round(float(v), 2)
|
||
except (TypeError, ValueError):
|
||
targets[str(q)] = 0.0
|
||
return targets
|
||
|
||
|
||
def write_annual_targets(db: Session, year: int, targets: dict) -> dict[str, float]:
|
||
clean: dict[str, float] = {}
|
||
for q in (1, 2, 3, 4):
|
||
v = (targets or {}).get(str(q))
|
||
if v is None or v == "":
|
||
clean[str(q)] = 0.0
|
||
continue
|
||
try:
|
||
clean[str(q)] = round(float(v), 2)
|
||
except (TypeError, ValueError):
|
||
raise ValueError(f"Q{q} 目标值不是数字")
|
||
value = json.dumps(clean, ensure_ascii=False)
|
||
row = db.get(Setting, _targets_key(year))
|
||
if row:
|
||
row.value = value
|
||
else:
|
||
db.add(Setting(key=_targets_key(year), value=value))
|
||
db.commit()
|
||
return clean
|
||
|
||
|
||
# ---------------------------------------------------------------- 统计
|
||
def _sum(rows) -> float:
|
||
return round(sum(float(r.amount or 0) for r in rows), 2)
|
||
|
||
|
||
def summary(db: Session, ctx: dict) -> dict:
|
||
"""定开财务汇总:KPI(当前季度 / 年度)+ 年度任务表(该年四个季度 + 未分季)。
|
||
|
||
金额单位一律「元」,展示层再折算万元;目标值是「万元」。
|
||
「未分季」= 认得出年份、认不出季度(`sign_date` 只有「2024/」这类残缺值、
|
||
或只有 `accept_year` 没有 `accept_date`)的项目:它们计入年度,
|
||
但不属于任何一季度,所以单列一行,保证「四季度 + 未分季 = 合计 = KPI 年度」。
|
||
"""
|
||
year, q = ctx["year"], ctx["quarter"]
|
||
rows = db.query(CustomProject).filter(CustomProject.archived.is_(False)).all()
|
||
placed = [r for r in rows if is_placed(r)]
|
||
|
||
def signed(yy, qq):
|
||
return [r for r in placed
|
||
if (sy := sign_period(r))[0] == yy and (qq is None or sy[1] == qq)]
|
||
|
||
def accepted(yy, qq):
|
||
return [r for r in rows
|
||
if (ap := accept_period(r))[0] == yy and (qq is None or ap[1] == qq)]
|
||
|
||
def kpi(items):
|
||
return {"count": len(items), "amount": _sum(items)}
|
||
|
||
sign_q, sign_y = signed(year, q), signed(year, None)
|
||
acc_q, acc_y = accepted(year, q), accepted(year, None)
|
||
# 数据质量:识别不出下单年份 / 缺验收年份的项目,页面上给计数,不静默漏算
|
||
sign_unknown = [r for r in placed if sign_period(r)[0] is None]
|
||
acc_no_year = [r for r in rows if accept_period(r)[1] and not accept_period(r)[0]]
|
||
# 有年份、无季度:计入年度,不进任何一季度
|
||
sign_no_q = [r for r in placed if (sy := sign_period(r))[0] == year and sy[1] is None]
|
||
acc_no_q = [r for r in rows if (ap := accept_period(r))[0] == year and ap[1] is None]
|
||
|
||
targets = read_annual_targets(db, year)
|
||
quarters = []
|
||
for i in (1, 2, 3, 4):
|
||
acc, sgn = accepted(year, i), signed(year, i)
|
||
target = targets[str(i)]
|
||
amount = _sum(acc)
|
||
quarters.append({
|
||
"q": i,
|
||
"target": target, # 万元
|
||
"acc_count": len(acc), "acc_amount": amount, # 元
|
||
"sign_count": len(sgn), "sign_amount": _sum(sgn),
|
||
"rate": round(amount / 10000 / target, 4) if target else None,
|
||
"current": i == q,
|
||
})
|
||
return {
|
||
"context": ctx,
|
||
"kpi": {
|
||
"placed": kpi(placed),
|
||
"sign_quarter": kpi(sign_q),
|
||
"sign_year": kpi(sign_y),
|
||
"accept_quarter": kpi(acc_q),
|
||
"accept_year": kpi(acc_y),
|
||
"sign_unknown": len(sign_unknown),
|
||
"accept_no_year": len(acc_no_year),
|
||
"sign_no_quarter": len(sign_no_q),
|
||
"accept_no_quarter": len(acc_no_q),
|
||
},
|
||
"annual": {
|
||
"year": year,
|
||
"targets": targets,
|
||
"quarters": quarters,
|
||
"unscheduled": {
|
||
"acc_count": len(acc_no_q), "acc_amount": _sum(acc_no_q),
|
||
"sign_count": len(sign_no_q), "sign_amount": _sum(sign_no_q),
|
||
},
|
||
"total": {
|
||
"target": round(sum(t["target"] for t in quarters), 2),
|
||
"acc_count": sum(t["acc_count"] for t in quarters) + len(acc_no_q),
|
||
"acc_amount": round(sum(t["acc_amount"] for t in quarters) + _sum(acc_no_q), 2),
|
||
"sign_count": sum(t["sign_count"] for t in quarters) + len(sign_no_q),
|
||
"sign_amount": round(sum(t["sign_amount"] for t in quarters) + _sum(sign_no_q), 2),
|
||
},
|
||
},
|
||
"generated_at": now().isoformat(timespec="seconds"),
|
||
}
|
||
|
||
|
||
# ---------------------------------------------------------------- 老库回填
|
||
def backfill(db: Session) -> list[str]:
|
||
"""幂等回填:验收年份 + 由项目状态推导商务阶段。只在缺值 / 不一致时写。"""
|
||
notes: list[str] = []
|
||
rows = db.query(CustomProject).all()
|
||
# 台账按年维护,缺年份的老行按库里出现过的最晚验收年份补(不随「今天」漂移)
|
||
known = [ym[0] for r in rows
|
||
if (ym := parse_year_month(r.accept_date_real)) and parse_quarter(r.accept_date)]
|
||
fallback = max(known) if known else None
|
||
years = stages = 0
|
||
for r in rows:
|
||
if r.accept_year is None and parse_quarter(r.accept_date):
|
||
ym = parse_year_month(r.accept_date_real)
|
||
r.accept_year = ym[0] if ym else fallback
|
||
if r.accept_year:
|
||
years += 1
|
||
status = (r.project_status or "").strip()
|
||
if status:
|
||
want = derive_stage(status, r.stage)
|
||
if r.stage != want:
|
||
r.stage = want
|
||
stages += 1
|
||
if years or stages:
|
||
db.commit()
|
||
if years:
|
||
notes.append(f"已补齐定制项目验收年份 {years} 条")
|
||
if stages:
|
||
notes.append(f"已按项目状态推导商务阶段 {stages} 条")
|
||
return notes
|