#!/usr/bin/env python3
"""生成三系统月度汇总Excel + 柱状图（仅2025-01到2026-05，不含总计列）"""
import openpyxl, json
from datetime import datetime
from collections import defaultdict

fp = '/mnt/c/Users/kingw/Desktop/自助机项目成立至2026年5月31日完全统计表.xlsx'
wb_src = openpyxl.load_workbook(fp, data_only=True)

# ── Aggregation (verified: matches source 总计 column) ──
ice = defaultdict(lambda: {'total': 0.0, 'count': 0})
bev = defaultdict(lambda: {'total': 0.0, 'count': 0})

sheets_cfg = [
    ('2.0系统冰激淋', 2, 3, 10, 'ice'),    # name_col, first_month_col, total_col, cat
    ('2.0系统 饮料机', 2, 3, 10, 'bev'),
    ('1.0系统冰激淋', 2, 3, 19, 'ice'),
    ('1.0系统饮料数据', 1, 2, 7, 'bev'),
    ('海逸冰激淋', 3, 4, 28, 'ice'),
]

for sname, name_col, fm_col, total_col, cat in sheets_cfg:
    ws = wb_src[sname]
    for row in range(2, ws.max_row + 1):
        for col in range(fm_col, total_col):  # excludes 总计 column
            h = ws.cell(row=1, column=col).value
            if not isinstance(h, datetime):
                continue
            if h < datetime(2025, 1, 1) or h > datetime(2026, 5, 31):
                continue
            val = ws.cell(row=row, column=col).value
            if val and val != '-' and val != 0:
                try:
                    amt = float(val)
                except:
                    continue
                if amt > 0:
                    m = h.strftime('%Y-%m')
                    target = ice if cat == 'ice' else bev
                    target[m]['total'] += amt
                    target[m]['count'] += 1

# Build combined
months_17 = []
for y in [2025, 2026]:
    end = 13 if y == 2025 else 6
    for m in range(1, end):
        months_17.append(f'{y}-{m:02d}')

def build_series(data):
    rows = []
    for m in months_17:
        d = data.get(m, {'total': 0.0, 'count': 0})
        rows.append({
            'month': m, 
            'total': round(d['total'], 2), 
            'count': d['count'],
            'avg': round(d['total'] / d['count'], 2) if d['count'] > 0 else 0
        })
    return rows

ice_series = build_series(ice)
bev_series = build_series(bev)
comb_series = []
for i, m in enumerate(months_17):
    comb_series.append({
        'month': m,
        'total': ice_series[i]['total'] + bev_series[i]['total'],
        'count': ice_series[i]['count'] + bev_series[i]['count'],
        'avg': 0
    })
    if comb_series[-1]['count'] > 0:
        comb_series[-1]['avg'] = round(comb_series[-1]['total'] / comb_series[-1]['count'], 2)

# ── Create output workbook ──
wb = openpyxl.Workbook()

header_fill = openpyxl.styles.PatternFill(start_color='2F5496', end_color='2F5496', fill_type='solid')
header_font = openpyxl.styles.Font(bold=True, color='FFFFFF', size=10)
thin_border = openpyxl.styles.Border(
    left=openpyxl.styles.Side(style='thin'),
    right=openpyxl.styles.Side(style='thin'),
    top=openpyxl.styles.Side(style='thin'),
    bottom=openpyxl.styles.Side(style='thin')
)
center_align = openpyxl.styles.Alignment(horizontal='center')

# ── Summary sheet ──
ws = wb.active
ws.title = '月度汇总'

headers = ['月份', '🍦冰激淋销售额', '冰机器数', '冰单机月均',
           '🥤饮料销售额', '饮机器数', '饮单机月均',
           '📊合计销售额', '合机器数', '合单机月均']
for c, h in enumerate(headers, 1):
    cell = ws.cell(row=1, column=c, value=h)
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = center_align
    cell.border = thin_border

for i in range(17):
    row = i + 2
    ice_r = ice_series[i]
    bev_r = bev_series[i]
    comb_r = comb_series[i]
    vals = [comb_r['month'],
            ice_r['total'], ice_r['count'], ice_r['avg'],
            bev_r['total'], bev_r['count'], bev_r['avg'],
            comb_r['total'], comb_r['count'], comb_r['avg']]
    for c, v in enumerate(vals, 1):
        cell = ws.cell(row=row, column=c, value=v)
        cell.border = thin_border
        cell.alignment = center_align
        if c in (2, 5, 8):
            cell.number_format = '#,##0'
        elif c in (4, 7, 10):
            cell.number_format = '#,##0'

for col in range(1, 11):
    ws.column_dimensions[openpyxl.utils.get_column_letter(col)].width = 14
ws.column_dimensions['A'].width = 10

# ── Chart sheets ──
from openpyxl.chart import BarChart, LineChart, Reference
from openpyxl.chart.label import DataLabelList

def make_chart_sheet(wb, name, series, bar_color, title):
    ws = wb.create_sheet(title=name)
    
    # Data table
    for c, h in enumerate(['月份', '销售额', '机器数', '单机月均'], 1):
        cell = ws.cell(row=1, column=c, value=h)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = center_align
        cell.border = thin_border
    
    for i, r in enumerate(series):
        row = i + 2
        ws.cell(row=row, column=1, value=r['month']).border = thin_border
        ws.cell(row=row, column=1).alignment = center_align
        ws.cell(row=row, column=2, value=r['total']).border = thin_border
        ws.cell(row=row, column=2).number_format = '#,##0'
        ws.cell(row=row, column=3, value=r['count']).border = thin_border
        ws.cell(row=row, column=3).alignment = center_align
        ws.cell(row=row, column=4, value=r['avg']).border = thin_border
        ws.cell(row=row, column=4).number_format = '#,##0'
    
    ws.column_dimensions['A'].width = 10
    ws.column_dimensions['B'].width = 14
    ws.column_dimensions['C'].width = 10
    ws.column_dimensions['D'].width = 14
    
    n = len(series)
    
    # Bar chart
    chart = BarChart()
    chart.type = 'col'
    chart.style = 10
    chart.title = title
    chart.y_axis.title = '销售额 (¥)'
    chart.width = 26
    chart.height = 15
    
    cats = Reference(ws, min_col=1, min_row=2, max_row=n+1)
    data_ref = Reference(ws, min_col=2, min_row=1, max_row=n+1)
    chart.add_data(data_ref, titles_from_data=True)
    chart.set_categories(cats)
    chart.series[0].graphicalProperties.solidFill = bar_color
    
    # Line: machine count
    line1 = LineChart()
    line1.add_data(Reference(ws, min_col=3, min_row=1, max_row=n+1), titles_from_data=True)
    line1.y_axis.axId = 200
    line1.y_axis.title = '机器数'
    line1.y_axis.crosses = 'max'
    line1.series[0].graphicalProperties.line.solidFill = 'FF0000'
    line1.series[0].marker.symbol = 'circle'
    line1.series[0].marker.size = 5
    chart += line1
    
    # Line: per-machine avg
    line2 = LineChart()
    line2.add_data(Reference(ws, min_col=4, min_row=1, max_row=n+1), titles_from_data=True)
    line2.y_axis.axId = 300
    line2.y_axis.title = '单机月均 (¥)'
    line2.y_axis.crosses = 'max'
    line2.series[0].graphicalProperties.line.solidFill = '70AD47'
    line2.series[0].marker.symbol = 'diamond'
    line2.series[0].marker.size = 5
    chart += line2
    
    ws.add_chart(chart, f'A{n+4}')
    return ws

make_chart_sheet(wb, '🍦冰激淋趋势', ice_series, '4472C4', '冰激淋月度趋势（2025.01-2026.05）')
make_chart_sheet(wb, '🥤饮料趋势', bev_series, 'ED7D31', '饮料月度趋势（2025.01-2026.05）')
make_chart_sheet(wb, '📊合计趋势', comb_series, '7030A0', '全品类合计月度趋势（2025.01-2026.05）')

# ── Move summary to first position ──
wb.move_sheet('月度汇总', offset=-3)

filepath = '/mnt/c/Users/kingw/Desktop/三系统月度销售汇总_2025.01-2026.05.xlsx'
wb.save(filepath)

# Print verification
total_ice = sum(r['total'] for r in ice_series)
total_bev = sum(r['total'] for r in bev_series)
print(f'✅ {filepath}')
print(f'冰激淋累计: ¥{total_ice:,.2f} | 饮料累计: ¥{total_bev:,.2f} | 合计: ¥{total_ice+total_bev:,.2f}')
print(f'数据范围: 2025-01 ~ 2026-05（17个月），不含2024年及总计列')
PYEOF