import pandas as pd
from sqlalchemy import create_engine
from datetime import datetime
from urllib.parse import quote_plus
from openpyxl import load_workbook
from pathlib import Path
import plotly.express as px                 # 基础图表
import plotly.graph_objects as go           # 两图叠加（柱+折线）
from dash import Dash, html, dcc, dash_table  # 看板搭建

# ================================导入数据库===================================
pwd = quote_plus('密码')             # 密码含特殊字符需转义
engine = create_engine(fr'mysql+pymysql://root:{pwd}@localhost:端口/work')

# JOIN 匹配客户代码
sql = """
 SELECT a.*, b.`客户代码`
 FROM 预收账款脱敏 a
 LEFT JOIN 客户表 b ON a.`客户名称` = b.`客户名称`
"""
raw = pd.read_sql(sql, con=engine)
output_folder = Path(r"C:\...\凭证")
# ==============================================================================

# ==================================一、数据清洗批量生成凭证========================================
# 动态计算当月摊销，取运行当月月份
month = datetime.now().month
raw["当月摊销"] = raw[f"{month}月摊销"]
# 筛选去除当月摊销没有金额的行
raw = raw[raw["当月摊销"].notna()]
# 本期减少：当月之前所有月摊销合计 [列表推导式]
cols = [f"{m}月摊销" for m in range(1, month)]
raw["本期减少"] = raw[cols].sum(axis=1)
# 期末余额 = 期初 + 本期新增 - 本期减少
raw["期初余额"] = raw["期初余额"].fillna(0)
raw["本期新增"] = raw["本期新增"].fillna(0)
raw["本期减少"] = raw["本期减少"].fillna(0)
raw["期末余额"] = raw["期初余额"] + raw["本期新增"] - raw["本期减少"]
raw["余额"] = raw["期末余额"] - raw["当月摊销"]
# 拼接文本列
raw["文本"] = raw["仪器所在医院"] + "/" + raw["仪器名称"] + "/" + raw["性质"] + "/" + raw["摊销期间"]
# 原因代码字典映射
PRODUCTION_MAPPING = {
    "租赁费": 90000038,
    "技术服务费": 90000027,
    "维修保养费": 90000024,
    "系统开通费": 90000045
}
raw["原因代码"] = raw["性质"].map(PRODUCTION_MAPPING).astype(str)
raw["医院代码"] = raw["医院代码"].astype(str)

# 整理列顺序
result = raw[["客户代码","客户名称","期末余额","当月摊销","余额",
              "医院代码","仪器所在医院","仪器名称","发票号",
              "性质","摊销期间","文本","原因代码"]]
# 写回数据库
result.to_sql("预收账款_处理结果", con=engine, if_exists="replace", index=False)
result = result.reset_index(drop=True)
result["序号"] = result.index + 1

# 清空并批量生成凭证文件
for file in output_folder.glob("*"):
    file.unlink()
for i, row in result.iterrows():
    wb = load_workbook(r"C:\...\凭证模版.xlsx")
    ws = wb.active
    # 抬头行（第3行）
    ws["B3"] = datetime.now().strftime("%Y%m%d")
    ws["C3"] = datetime.now().strftime("%Y%m%d")
    ws["E3"] = "收入摊销"
    ws["F3"] = row["客户名称"]
    ws["G3"] = "DA"
    ws["H3"] = "1234"           # 公司代码
    ws["I3"] = "CNY"
    # 行项目1（第4行）：借方科目6051541
    ws["B4"] = "50"
    ws["C4"] = "6051541"
    ws["E4"] = row["当月摊销"]
    ws["S4"] = row["文本"]
    ws["U4"] = row["客户代码"]
    ws["W4"] = row["原因代码"]
    ws["X4"] = row["医院代码"]
    # 行项目2（第5行）：客户贷方期末余额
    ws["B5"] = "19"
    ws["C5"] = row["客户代码"]
    ws["D5"] = "T"
    ws["E5"] = row["期末余额"]
    ws["S5"] = row["文本"]
    # 行项目3（第6行）：客户贷方余额
    ws["B6"] = "09"
    ws["C6"] = row["客户代码"]
    ws["D6"] = "T"
    ws["E6"] = row["余额"]
    ws["S6"] = row["客户名称"]
    filename = f"{row['序号']}_{row['客户名称']}.xls"
    wb.save(output_folder / filename)
    wb.close()
# ===============================================================================

# ======================================当月其他业务收入看板==============================
# 1. 当月摊销总计（KPI 卡片）
df_total = pd.read_sql("SELECT ROUND(SUM(当月摊销),2) AS 当月摊销合计 FROM 预收账款_处理结果", con=engine)
total = df_total["当月摊销合计"].values[0]

# 2. 费用类型饼图
df_type = pd.read_sql("""
    SELECT 性质, ROUND(SUM(当月摊销)/10000,2) AS 当月摊销合计
    FROM 预收账款_处理结果
    GROUP BY 性质 ORDER BY 当月摊销合计 DESC
""", con=engine)
fig_pie = px.pie(df_type, values="当月摊销合计", names="性质",
                 title="当月摊销按类型汇总（万元）", hole=0)
fig_pie.update_traces(textinfo="label+percent+value")

# 3. 期末余额前10大客户（横向柱状图）
df_top10 = pd.read_sql("""
    SELECT 客户名称, ROUND(SUM(期末余额)/10000,2) AS 期末余额
    FROM 预收账款_处理结果
    GROUP BY 客户名称 ORDER BY SUM(期末余额) ASC LIMIT 10
""", con=engine)
fig_bar = px.bar(df_top10, x="期末余额", y="客户名称",
                 orientation="h", title="期末余额前10大客户（万元）")

# 4. 当月到期合同提醒
df_due = pd.read_sql("""
    SELECT 客户名称, 仪器所在医院, 仪器名称, 合同编号,
           SUBSTR(摊销期间, 10, 6) AS 到期年月
    FROM 预收账款脱敏
    WHERE SUBSTR(摊销期间, 10, 6) = DATE_FORMAT(NOW(),'%%Y%%m')
""", con=engine)

# 5. 全年每月摊销趋势（柱+折线叠加）
df_month = pd.read_sql("""
    SELECT SUM(`1月摊销`) AS `1月`, SUM(`2月摊销`) AS `2月`,
           ... SUM(`12月摊销`) AS `12月`
    FROM 预收账款脱敏
""", con=engine)
df_long = df_month.melt(var_name="月份", value_name="金额")
df_long["金额"] = (df_long["金额"] / 10000).round(2)
fig_trend = go.Figure()
fig_trend.add_trace(go.Bar(x=df_long["月份"], y=df_long["金额"], name="金额"))
fig_trend.add_trace(go.Scatter(x=df_long["月份"], y=df_long["金额"],
                               mode="lines+markers", name="趋势"))

# 图表统一透明背景 / 白色字体
for fig in [fig_pie, fig_bar, fig_trend]:
    fig.update_layout(
        paper_bgcolor="rgba(0,0,0,0)",
        plot_bgcolor="rgba(0,0,0,0)",
        font_color="white",
        xaxis={"showgrid": False},
        yaxis={"showgrid": False}
    )

# Dash 看板布局
app = Dash(__name__)
app.layout = html.Div([
    html.H1(f"2026年{month}月其他业务收入分析",
            style={"textAlign":"center","color":"#ffffff",
                   "padding":"20px 0","fontSize":"48px","letterSpacing":"4px"}),
    # 第一行：饼图 + KPI卡片 + 到期提醒
    html.Div([
        html.Div(dcc.Graph(figure=fig_pie), style={"width":"40%",...}),
        html.Div([
            html.P("当月摊销金额合计"),
            html.H1(f"¥ {total:,.2f}")
        ], style={"width":"20%",...}),
        html.Div([
            html.P("⚠ 本月到期合同提醒"),
            dash_table.DataTable(data=df_due.to_dict("records"),...)
        ], style={"width":"40%",...}),
    ], style={"display":"flex"}),
    # 第二行：趋势图 + TOP10
    html.Div([
        html.Div(dcc.Graph(figure=fig_trend), style={"width":"50%"}),
        html.Div(dcc.Graph(figure=fig_bar),   style={"width":"50%"}),
    ], style={"display":"flex"}),
], style={"backgroundColor":"#0a1628","minHeight":"100vh"})

if __name__ == "__main__":
    app.run(debug=True)