103 lines
3.1 KiB
Python
103 lines
3.1 KiB
Python
#!/usr/bin/env python3
|
||
"""读取腾讯文档「工资表V2」指定月份的数据，输出 data.js 给 salary_slips.html 使用。
|
||
|
||
用法:
|
||
python3 fetch_salary.py 202604 # 拉 4 月
|
||
python3 fetch_salary.py 202605 # 拉 5 月
|
||
python3 fetch_salary.py # 默认本月（YYYYMM）
|
||
|
||
输出: ./data.js（与本脚本同目录）
|
||
|
||
依赖: mcporter（系统命令）+ tencent-docs mcp 已配置
|
||
"""
|
||
import csv
|
||
import io
|
||
import json
|
||
import os
|
||
import subprocess
|
||
import sys
|
||
from datetime import date
|
||
|
||
FILE_ID = "VLSAvSvqvYzU" # 工资表V2
|
||
SHEET_ID = "BB08J2" # 员工档案
|
||
END_ROW = 80 # 足够覆盖所有月份
|
||
END_COL = 42
|
||
|
||
OUT_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), "data.js")
|
||
|
||
|
||
def fetch_csv(file_id: str, sheet_id: str) -> list:
|
||
args = {
|
||
"file_id": file_id,
|
||
"sheet_id": sheet_id,
|
||
"start_row": 0,
|
||
"end_row": END_ROW,
|
||
"start_col": 0,
|
||
"end_col": END_COL,
|
||
"return_csv": True,
|
||
}
|
||
res = subprocess.run(
|
||
["mcporter", "call", "tencent-docs", "sheet.get_cell_data",
|
||
"--args", json.dumps(args)],
|
||
capture_output=True, text=True, check=True,
|
||
)
|
||
data = json.loads(res.stdout)
|
||
if data.get("error"):
|
||
raise RuntimeError(f"API error: {data['error']}")
|
||
return list(csv.reader(io.StringIO(data["csv_data"])))
|
||
|
||
|
||
def default_month() -> str:
|
||
"""Return YYYYMM for current month."""
|
||
today = date.today()
|
||
return f"{today.year}{today.month:02d}"
|
||
|
||
|
||
def main():
|
||
month = sys.argv[1] if len(sys.argv) > 1 else default_month()
|
||
print(f"拉取月份: {month}")
|
||
|
||
rows = fetch_csv(FILE_ID, SHEET_ID)
|
||
if not rows:
|
||
sys.exit("空数据")
|
||
header = rows[0]
|
||
|
||
def month_records(m):
|
||
out = []
|
||
for r in rows[1:]:
|
||
if not r or not r[0].strip() or r[0] != m:
|
||
continue
|
||
out.append({header[i]: (r[i] if i < len(r) else "") for i in range(len(header))})
|
||
return out
|
||
|
||
records = month_records(month)
|
||
if not records:
|
||
sys.exit(f"未找到 {month} 月份的记录")
|
||
|
||
# 上月业绩(同店逐人按姓名匹配)，供工资单展示3种业绩环比涨跌
|
||
y, mm = int(month[:4]), int(month[4:6])
|
||
prev = f"{y-1}12" if mm == 1 else f"{y}{mm-1:02d}"
|
||
prev_by_name = {}
|
||
for r in month_records(prev):
|
||
prev_by_name[r.get("姓名", "")] = {
|
||
"部门业绩": r.get("部门业绩", ""),
|
||
"班次业绩": r.get("班次业绩", ""),
|
||
"部门x班次业绩": r.get("部门x班次业绩", ""),
|
||
}
|
||
for rec in records:
|
||
rec["_prev"] = prev_by_name.get(rec.get("姓名", ""), None)
|
||
|
||
payload = {"month": month, "prev_month": prev, "header": header, "records": records}
|
||
with open(OUT_PATH, "w", encoding="utf-8") as f:
|
||
f.write("window.SALARY_DATA = ")
|
||
json.dump(payload, f, ensure_ascii=False, indent=2)
|
||
f.write(";\n")
|
||
|
||
print(f"写入 {len(records)} 条记录到 {OUT_PATH}")
|
||
for r in records:
|
||
print(f" - {r.get('姓名','?')} ({r.get('归属','')} {r.get('部门','')} {r.get('岗位','')})")
|
||
|
||
|
||
if __name__ == "__main__":
|
||
main()
|