from pathlib import Path
from decimal import Decimal
from collections import Counter, defaultdict
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

out = Path('/Users/qianliyun/Documents/aiwork/finance/PDF合并_发票明细及金额合计.xlsx')
out.parent.mkdir(parents=True, exist_ok=True)

buyer = '杭州万物有灵文化科技有限公司'
buyer_tax = '91330106MA2KDLRJ4J'
rows = [
    {'序号':1,'发票类型':'电子发票（普通发票）','发票号码':'26332000002924604841','开票日期':'2026-04-10','购买方':buyer,'购买方税号':buyer_tax,'销售方':'杭州麓格餐饮有限公司','销售方税号':'91330105MAEF3RCTXN','项目名称':'*餐饮服务*餐饮服务','项目大类':'餐饮服务','不含税金额':'339.62','税额':'20.38','价税合计':'360.00','备注':''},
    {'序号':2,'发票类型':'电子发票（普通发票）','发票号码':'26332000002529496711','开票日期':'2026-03-28','购买方':buyer,'购买方税号':buyer_tax,'销售方':'杭州唐乔餐饮管理有限公司','销售方税号':'91330108MA2KFAT29B','项目名称':'*餐饮服务*餐饮费','项目大类':'餐饮服务','不含税金额':'382.08','税额':'22.92','价税合计':'405.00','备注':''},
    {'序号':3,'发票类型':'电子发票（普通发票）','发票号码':'26332000003092499976','开票日期':'2026-04-15','购买方':buyer,'购买方税号':buyer_tax,'销售方':'杭州乔伟餐饮管理有限公司','销售方税号':'92330106MACDXX9H78','项目名称':'*餐饮服务*餐饮','项目大类':'餐饮服务','不含税金额':'700.00','税额':'7.00','价税合计':'707.00','备注':''},
    {'序号':4,'发票类型':'电子发票（铁路电子客票）','发票号码':'26339133005000114616','开票日期':'2026-05-03','购买方':buyer,'购买方税号':buyer_tax,'销售方':'铁路电子客票','销售方税号':'','项目名称':'*运输服务*票价','项目大类':'运输服务','不含税金额':'','税额':'','价税合计':'37.50','备注':'2026-05-03 永康南站-义乌站 G7320'},
    {'序号':5,'发票类型':'电子发票（铁路电子客票）','发票号码':'26339190041004604686','开票日期':'2026-05-03','购买方':buyer,'购买方税号':buyer_tax,'销售方':'铁路电子客票','销售方税号':'','项目名称':'*运输服务*票价','项目大类':'运输服务','不含税金额':'','税额':'','价税合计':'41.00','备注':'2026-01-18 杭州东站-义乌站 D171'},
    {'序号':6,'发票类型':'电子发票（铁路电子客票）','发票号码':'26339190041004604688','开票日期':'2026-05-03','购买方':buyer,'购买方税号':buyer_tax,'销售方':'铁路电子客票','销售方税号':'','项目名称':'*运输服务*票价','项目大类':'运输服务','不含税金额':'','税额':'','价税合计':'99.50','备注':'2026-05-02 杭州东站-永康南站 G7759'},
    {'序号':7,'发票类型':'电子发票（铁路电子客票）','发票号码':'26339133005000125018','开票日期':'2026-05-12','购买方':buyer,'购买方税号':buyer_tax,'销售方':'铁路电子客票','销售方税号':'','项目名称':'*运输服务*票价','项目大类':'运输服务','不含税金额':'','税额':'','价税合计':'9.00','备注':'2026-05-05 武义北站-永康南站 G7330'},
    {'序号':8,'发票类型':'电子发票（铁路电子客票）','发票号码':'26339132537000341324','开票日期':'2026-05-12','购买方':buyer,'购买方税号':buyer_tax,'销售方':'铁路电子客票','销售方税号':'','项目名称':'*运输服务*票价','项目大类':'运输服务','不含税金额':'','税额':'','价税合计':'39.50','备注':'2026-05-03 义乌站-永康南站 G821'},
    {'序号':9,'发票类型':'电子发票（铁路电子客票）','发票号码':'26319199022000102246','开票日期':'2026-05-12','购买方':buyer,'购买方税号':buyer_tax,'销售方':'铁路电子客票','销售方税号':'','项目名称':'*运输服务*票价','项目大类':'运输服务','不含税金额':'','税额':'','价税合计':'90.50','备注':'2026-05-05 杭州东站-武义北站 补票 G7330'},
    {'序号':10,'发票类型':'电子发票（普通发票）','发票号码':'26317000001639414282','开票日期':'2026-05-03','购买方':buyer,'购买方税号':buyer_tax,'销售方':'上海华程西南国际旅行社有限公司','销售方税号':'91310105134638405A','项目名称':'*经纪代理服务*代订机票产品','项目大类':'经纪代理服务','不含税金额':'1245.28','税额':'74.72','价税合计':'1320.00','备注':'携程订单:1128140114630126'},
    {'序号':11,'发票类型':'电子发票（普通发票）','发票号码':'26337000000369920854','开票日期':'2026-04-21','购买方':buyer,'购买方税号':buyer_tax,'销售方':'中国石化销售股份有限公司浙江杭州石油分公司','销售方税号':'91330100722781742J','项目名称':'*汽油*95号车用汽油(ⅥB)','项目大类':'汽油','不含税金额':'395.71','税额':'51.44','价税合计':'447.15','备注':'加油：2026-01-07 杭州城中东新站（新）'},
    {'序号':12,'发票类型':'电子发票（普通发票）','发票号码':'26337000000317358876','开票日期':'2026-04-21','购买方':buyer,'购买方税号':buyer_tax,'销售方':'杭州余杭交投石化能源有限公司','销售方税号':'913301105832025357','项目名称':'*汽油*95号车用汽油(ⅥB)','项目大类':'汽油','不含税金额':'438.05','税额':'56.95','价税合计':'495.00','备注':'加油：2026-02-24 杭州余二顺潭头站'},
    {'序号':13,'发票类型':'电子发票（普通发票）','发票号码':'26337000000368515061','开票日期':'2026-04-21','购买方':buyer,'购买方税号':buyer_tax,'销售方':'中国石化销售股份有限公司浙江杭州石油分公司','销售方税号':'91330100722781742J','项目名称':'*汽油*95号车用汽油(ⅥB)','项目大类':'汽油','不含税金额':'549.78','税额':'71.47','价税合计':'621.25','备注':'加油：2026-04-15 杭州城中绍兴路站'},
    {'序号':14,'发票类型':'电子发票（普通发票）','发票号码':'26317000001487294763','开票日期':'2026-05-03','购买方':buyer,'购买方税号':buyer_tax,'销售方':'上海携程国际旅行社有限公司','销售方税号':'913101107390065156','项目名称':'*旅游服务*旅游服务费','项目大类':'旅游服务','不含税金额':'937.74','税额':'56.26','价税合计':'994.00','备注':'1128146480805187'},
    {'序号':15,'发票类型':'电子发票（普通发票）','发票号码':'26337000000403647524','开票日期':'2026-05-09','购买方':buyer,'购买方税号':buyer_tax,'销售方':'杭州叮当国际旅行社有限公司','销售方税号':'913301105548831241','项目名称':'*旅游服务*代订服务费','项目大类':'旅游服务','不含税金额':'941.51','税额':'56.49','价税合计':'998.00','备注':''},
    {'序号':16,'发票类型':'电子发票（普通发票）','发票号码':'26112000001774951906','开票日期':'2026-05-05','购买方':buyer,'购买方税号':buyer_tax,'销售方':'北京帝都国际旅行社有限公司','销售方税号':'91110113MA00BMMH6R','项目名称':'*旅游服务*旅游服务费','项目大类':'旅游服务','不含税金额':'1087.02','税额':'10.87','价税合计':'1097.89','备注':''},
    {'序号':17,'发票类型':'电子发票（增值税专用发票）','发票号码':'26362000000806351071','开票日期':'2026-05-12','购买方':buyer,'购买方税号':buyer_tax,'销售方':'景德镇市瓷邑酒店有限公司','销售方税号':'91360200MA35Q3CF63','项目名称':'*住宿服务*住宿费','项目大类':'住宿服务','不含税金额':'426.94','税额':'25.62','价税合计':'452.56','备注':''},
    {'序号':18,'发票类型':'电子发票（普通发票）','发票号码':'26317000001639346853','开票日期':'2026-05-03','购买方':buyer,'购买方税号':buyer_tax,'销售方':'上海赫程国际旅行社有限公司','销售方税号':'91310110350849784X','项目名称':'*经纪代理服务*代订住宿费','项目大类':'经纪代理服务','不含税金额':'1186.32','税额':'71.18','价税合计':'1257.50','备注':''},
    {'序号':19,'发票类型':'电子发票（普通发票）','发票号码':'26327000000793224157','开票日期':'2026-05-03','购买方':buyer,'购买方税号':buyer_tax,'销售方':'上海赫程国际旅行社有限公司南通分公司','销售方税号':'91320691MA1MA9TQ5J','项目名称':'*旅游服务*代订住宿费','项目大类':'旅游服务','不含税金额':'1885.85','税额':'113.15','价税合计':'1999.00','备注':''},
    {'序号':20,'发票类型':'电子发票（普通发票）','发票号码':'26317000001639346925','开票日期':'2026-05-03','购买方':buyer,'购买方税号':buyer_tax,'销售方':'上海赫程国际旅行社有限公司','销售方税号':'91310110350849784X','项目名称':'*经纪代理服务*代订住宿费','项目大类':'经纪代理服务','不含税金额':'545.28','税额':'32.72','价税合计':'578.00','备注':''},
]

# convert numeric fields
for r in rows:
    for k in ['不含税金额','税额','价税合计']:
        if r[k] != '':
            r[k] = Decimal(r[k]).quantize(Decimal('0.01'))

invoice_nums = [r['发票号码'] for r in rows]
dup_nums = [n for n,c in Counter(invoice_nums).items() if c > 1]
total_count = len(rows)
total_inc = sum(r['价税合计'] for r in rows)
total_ex = sum((r['不含税金额'] if r['不含税金额'] != '' else Decimal('0.00')) for r in rows)
total_tax = sum((r['税额'] if r['税额'] != '' else Decimal('0.00')) for r in rows)

# category summary
cat = defaultdict(lambda: {'张数':0, '不含税金额':Decimal('0.00'), '税额':Decimal('0.00'), '价税合计':Decimal('0.00')})
for r in rows:
    d = cat[r['项目大类']]
    d['张数'] += 1
    if r['不含税金额'] != '': d['不含税金额'] += r['不含税金额']
    if r['税额'] != '': d['税额'] += r['税额']
    d['价税合计'] += r['价税合计']

wb = Workbook()
ws = wb.active
ws.title = '发票明细'
headers = ['序号','发票类型','发票号码','开票日期','购买方','购买方税号','销售方','销售方税号','项目名称','项目大类','不含税金额','税额','价税合计','备注']
ws.append(headers)
for r in rows:
    ws.append([r[h] for h in headers])

ws2 = wb.create_sheet('金额汇总')
summary_rows = [
    ['发票张数', total_count],
    ['价税合计', total_inc],
    ['不含税金额合计（铁路客票未拆分税额，故此项不含铁路）', total_ex],
    ['税额合计（铁路客票未拆分税额，故此项不含铁路）', total_tax],
    ['重复发票号码', '无' if not dup_nums else '、'.join(dup_nums)],
]
for row in summary_rows:
    ws2.append(row)
ws2.append([])
ws2.append(['项目大类','张数','不含税金额','税额','价税合计'])
for k,v in sorted(cat.items(), key=lambda kv: kv[1]['价税合计'], reverse=True):
    ws2.append([k, v['张数'], v['不含税金额'], v['税额'], v['价税合计']])
ws2.append(['合计', total_count, total_ex, total_tax, total_inc])

# style
header_fill = PatternFill('solid', fgColor='1F4E78')
header_font = Font(color='FFFFFF', bold=True)
total_fill = PatternFill('solid', fgColor='FFF2CC')
thin = Side(style='thin', color='D9E2F3')
for sh in wb.worksheets:
    sh.freeze_panes = 'A2'
    try:
        sh.auto_filter.ref = sh.dimensions
    except Exception:
        pass
    for cell in sh[1]:
        cell.fill = header_fill
        cell.font = header_font
        cell.alignment = Alignment(horizontal='center', vertical='center')
    for row in sh.iter_rows():
        for cell in row:
            cell.alignment = Alignment(vertical='top', wrap_text=True)
            cell.border = Border(left=thin, right=thin, top=thin, bottom=thin)
            if isinstance(cell.value, Decimal):
                cell.value = float(cell.value)
                cell.number_format = '#,##0.00'
    for col in sh.columns:
        letter = get_column_letter(col[0].column)
        max_len = max(len(str(c.value)) if c.value is not None else 0 for c in col)
        sh.column_dimensions[letter].width = min(max(max_len + 2, 10), 48)
# highlight final summary total row
for cell in ws2[ws2.max_row]:
    cell.fill = total_fill
    cell.font = Font(bold=True)

wb.save(out)
print('已生成:', out)
print('发票张数:', total_count)
print('价税合计:', total_inc)
print('不含税金额合计（不含铁路拆分）:', total_ex)
print('税额合计（不含铁路拆分）:', total_tax)
print('重复发票号码:', '无' if not dup_nums else dup_nums)