foodie-server/specs/010-order-invoice/gen_love_org_sql.py

# -*- coding: utf-8 -*-
"""把 specs/010-order-invoice/xca.csv(财政部爱心码清单)转成 updatesql/pos_love_org.sql。
CSV 用 CR(\r) 分隔记录、捐赠码含前导零、简称/县市可空、>7位码 ezPay 不支持置 enabled=0。
重新生成(CSV 更新后)直接重跑本脚本即可。"""
import os

ROOT = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
src = os.path.join(ROOT, 'specs', '010-order-invoice', 'xca.csv')
out = os.path.join(ROOT, 'updatesql', 'pos_love_org.sql')

with open(src, 'r', encoding='utf-8-sig') as f:
    raw = f.read()
raw = raw.replace('\r\n', '\n').replace('\r', '\n')
lines = [l for l in raw.split('\n') if l.strip()]


def sq(s):
    s = (s or '').strip()
    if s == '':
        return 'NULL'
    s = s.replace('\\', '\\\\').replace("'", "''")
    return "'" + s + "'"


rows = []
seen = set()
dups = 0
over7 = 0
bad = 0
bad_samples = []
for line in lines[1:]:
    parts = line.split(',')
    if len(parts) != 6:
        bad += 1
        if len(bad_samples) < 5:
            bad_samples.append(line[:100])
        continue
    seq, name, love, short, ubn, city = parts
    love = love.strip()
    if not love or not love.isdigit():
        bad += 1
        if len(bad_samples) < 5:
            bad_samples.append(line[:100])
        continue
    if love in seen:
        dups += 1
        continue
    seen.add(love)
    enabled = 0 if len(love) > 7 else 1
    if enabled == 0:
        over7 += 1
    rows.append((love, name.strip(), short.strip(), ubn.strip(), city.strip(), enabled))

with open(out, 'w', encoding='utf-8', newline='\n') as f:
    f.write("-- ============================================================\n")
    f.write("-- 捐赠机构字典 pos_love_org (财政部电子发票爱心码清单)\n")
    f.write("-- 来源: 财政部电子发票整合服务平台 https://www.einvoice.nat.gov.tw/portal/btc/btc603w\n")
    f.write("-- 源 CSV: specs/010-order-invoice/xca.csv (由 gen_love_org_sql.py 生成)\n")
    f.write("-- 说明: love_code 含前导零以 VARCHAR 存储; >7 位码 ezPay 不支持, enabled=0\n")
    f.write("-- 用法: 直接在 MySQL 执行(DROP+CREATE+INSERT 全量刷新, 可重复执行)\n")
    f.write("-- ============================================================\n\n")
    f.write("DROP TABLE IF EXISTS `pos_love_org`;\n")
    f.write("CREATE TABLE `pos_love_org` (\n")
    f.write("  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',\n")
    f.write("  `love_code` VARCHAR(10) NOT NULL COMMENT '捐赠码(爱心码,保留前导零)',\n")
    f.write("  `org_name` VARCHAR(255) NOT NULL COMMENT '受捐赠机关或团体名称',\n")
    f.write("  `org_short_name` VARCHAR(64) DEFAULT NULL COMMENT '机构简称(可空)',\n")
    f.write("  `ubn` VARCHAR(16) DEFAULT NULL COMMENT '统编(保留前导零)',\n")
    f.write("  `city` VARCHAR(32) DEFAULT NULL COMMENT '县市(可空)',\n")
    f.write("  `enabled` TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用:1启用 0停用(>7位码ezPay不支持)',\n")
    f.write("  `sync_time` DATETIME DEFAULT NULL COMMENT '财政部清单同步时间',\n")
    f.write("  `create_time` DATETIME DEFAULT NULL COMMENT '创建时间',\n")
    f.write("  PRIMARY KEY (`id`),\n")
    f.write("  UNIQUE KEY `uk_love_code` (`love_code`)\n")
    f.write(") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='捐赠机构字典(财政部电子发票爱心码清单)';\n\n")
    cols = "(`love_code`,`org_name`,`org_short_name`,`ubn`,`city`,`enabled`,`sync_time`,`create_time`)"
    BATCH = 100
    for i in range(0, len(rows), BATCH):
        chunk = rows[i:i + BATCH]
        vals = []
        for love, name, short, ubn, city, enabled in chunk:
            vals.append(f"({sq(love)},{sq(name)},{sq(short)},{sq(ubn)},{sq(city)},{enabled},NOW(),NOW())")
        f.write("INSERT INTO `pos_love_org` " + cols + " VALUES\n")
        f.write(",\n".join(vals))
        f.write(";\n\n")

print(f"imported={len(rows)} dups_skipped={dups} over7_disabled={over7} bad={bad}")
for s in bad_samples:
    print("BAD:", s)
print(f"output: {out}")