import pandas as pd
import numpy as np

out = "/Users/sylvawong/Desktop/CURSOR/ONE-OS/驾驶员提成与运营数据表_含排序.xlsx"

s1 = pd.read_excel(out, sheet_name="Sheet1")
sheet2 = pd.read_excel(out, sheet_name="Sheet2")

df = sheet2.copy()
df["日期"] = pd.to_datetime(df["日期"])
df["月份"] = df["日期"].dt.to_period("M").astype(str)

detail = (
    df.groupby(["月份", "驾驶员", "车牌号码", "运营项目", "归属项目"], as_index=False)
    .agg(总运费=("运费", "sum"), 提成金额=("提成金额", "sum"))
)
detail[["总运费", "提成金额"]] = detail[["总运费", "提成金额"]].round(2)

summary = (
    detail.groupby(["月份", "驾驶员"], as_index=False)
    .agg(总运费=("总运费", "sum"), 同月提成合计=("提成金额", "sum"))
)
summary[["总运费", "同月提成合计"]] = summary[["总运费", "同月提成合计"]].round(2)

detail["同月提成合计"] = np.nan

summary_rows = summary.copy()
summary_rows["车牌号码"] = ""
summary_rows["运营项目"] = "【同月合计】"
summary_rows["归属项目"] = ""
summary_rows["提成金额"] = summary_rows["同月提成合计"]

cols = ["月份", "驾驶员", "车牌号码", "运营项目", "归属项目", "总运费", "提成金额", "同月提成合计"]
detail = detail[cols]
summary_rows = summary_rows[cols]

detail["_sort"] = 0
summary_rows["_sort"] = 1
sheet3 = pd.concat([detail, summary_rows], ignore_index=True)
sheet3 = sheet3.sort_values(["月份", "驾驶员", "_sort", "车牌号码", "运营项目"], kind="stable").drop(columns="_sort")

with pd.ExcelWriter(out, engine="openpyxl") as writer:
    s1.to_excel(writer, sheet_name="Sheet1", index=False)
    sheet2.to_excel(writer, sheet_name="Sheet2", index=False)
    sheet3.to_excel(writer, sheet_name="Sheet3", index=False)

print(f"updated {out}, rows={len(sheet3)}")