194 lines
6.3 KiB
Python
194 lines
6.3 KiB
Python
"""业务种子数据：经费标准库、税号白名单、节假日。
|
||
|
||
init-db 幂等写入（存在即跳过，不覆盖管理员修改）。
|
||
标准数值为示例种子，实施时以学校现行《差旅费管理办法》等文件为准，
|
||
可在数据管理中直接编辑；节假日以国务院办公厅当年通知为准。
|
||
"""
|
||
|
||
from __future__ import annotations
|
||
|
||
import sqlite3
|
||
|
||
from util.timeutil import now_unix
|
||
|
||
# (category, city_tier, city_example, staff_title, max_amount, unit, basis_doc, clause)
|
||
_SEED_STANDARDS: tuple[tuple[str, str, str, str, float, str, str, str], ...] = (
|
||
(
|
||
"住宿费",
|
||
"一线城市",
|
||
"北京,上海,广州,深圳",
|
||
"副高及以上",
|
||
550.0,
|
||
"元/人/天",
|
||
"《差旅费管理办法》",
|
||
"第十条",
|
||
),
|
||
(
|
||
"住宿费",
|
||
"一线城市",
|
||
"北京,上海,广州,深圳",
|
||
"中级及以下",
|
||
480.0,
|
||
"元/人/天",
|
||
"《差旅费管理办法》",
|
||
"第十条",
|
||
),
|
||
(
|
||
"住宿费",
|
||
"省会及计划单列市",
|
||
"哈尔滨,长春,沈阳,石家庄,太原,西安,济南,杭州,郑州,合肥,南昌,福州,长沙,武汉,海口,贵阳,成都,昆明,西宁,兰州,台北,呼和浩特,南宁,拉萨,银川,乌鲁木齐,大连,青岛,宁波,厦门",
|
||
"副高及以上",
|
||
480.0,
|
||
"元/人/天",
|
||
"《差旅费管理办法》",
|
||
"第十条",
|
||
),
|
||
(
|
||
"住宿费",
|
||
"省会及计划单列市",
|
||
"哈尔滨,长春,沈阳,石家庄,太原,西安,济南,杭州,郑州,合肥,南昌,福州,长沙,武汉,海口,贵阳,成都,昆明,西宁,兰州,台北,呼和浩特,南宁,拉萨,银川,乌鲁木齐,大连,青岛,宁波,厦门",
|
||
"中级及以下",
|
||
400.0,
|
||
"元/人/天",
|
||
"《差旅费管理办法》",
|
||
"第十条",
|
||
),
|
||
(
|
||
"住宿费",
|
||
"其他地区",
|
||
"",
|
||
"副高及以上",
|
||
400.0,
|
||
"元/人/天",
|
||
"《差旅费管理办法》",
|
||
"第十条",
|
||
),
|
||
(
|
||
"住宿费",
|
||
"其他地区",
|
||
"",
|
||
"中级及以下",
|
||
350.0,
|
||
"元/人/天",
|
||
"《差旅费管理办法》",
|
||
"第十条",
|
||
),
|
||
(
|
||
"交通费",
|
||
"通用",
|
||
"",
|
||
"副高及以上",
|
||
2.0,
|
||
"席位等级代码（1=二等座/硬席/经济舱，2=一等座/软卧/公务舱，3=商务座/头等舱）",
|
||
"《差旅费管理办法》",
|
||
"第十二条",
|
||
),
|
||
(
|
||
"交通费",
|
||
"通用",
|
||
"",
|
||
"中级及以下",
|
||
1.0,
|
||
"席位等级代码（1=二等座/硬席/经济舱，2=一等座/软卧/公务舱，3=商务座/头等舱）",
|
||
"《差旅费管理办法》",
|
||
"第十二条",
|
||
),
|
||
)
|
||
|
||
# 校方购买方税号白名单种子（示例，实施时由财务处维护）
|
||
_SEED_WHITELIST: tuple[tuple[str, str, str], ...] = (
|
||
(
|
||
"91440101MA9XXYYY0A",
|
||
"广州华商学院",
|
||
"示例种子税号；上线前请由财务处核准并替换为真实统一社会信用代码",
|
||
),
|
||
)
|
||
|
||
# 节假日种子（主体日期；连休范围请管理员按当年国务院办公厅通知补全）
|
||
_SEED_HOLIDAYS: tuple[tuple[str, str], ...] = (
|
||
("2026-01-01", "元旦"),
|
||
("2026-02-16", "春节（除夕）"),
|
||
("2026-02-17", "春节"),
|
||
("2026-02-18", "春节"),
|
||
("2026-02-19", "春节"),
|
||
("2026-04-05", "清明节"),
|
||
("2026-05-01", "劳动节"),
|
||
("2026-06-19", "端午节"),
|
||
("2026-09-25", "中秋节"),
|
||
("2026-10-01", "国庆节"),
|
||
("2026-10-02", "国庆节"),
|
||
("2026-10-03", "国庆节"),
|
||
)
|
||
|
||
|
||
def seed_expense_standards(cursor: sqlite3.Cursor) -> int:
|
||
"""幂等写入经费标准种子，返回新增条数。"""
|
||
inserted = 0
|
||
for row in _SEED_STANDARDS:
|
||
category, city_tier, city_example, staff_title, max_amount, unit, basis_doc, clause = row
|
||
cursor.execute(
|
||
"""
|
||
SELECT COUNT(*) FROM expense_standards
|
||
WHERE category = ? AND city_tier = ? AND staff_title = ?
|
||
""",
|
||
(category, city_tier, staff_title),
|
||
)
|
||
if int(cursor.fetchone()[0]) > 0:
|
||
continue
|
||
cursor.execute(
|
||
"""
|
||
INSERT INTO expense_standards
|
||
(category, city_tier, city_example, staff_title, max_amount, unit,
|
||
basis_doc, clause, effective_date, created_at, updated_at)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, NULL, ?, ?)
|
||
""",
|
||
(category, city_tier, city_example, staff_title, max_amount, unit, basis_doc, clause, now_unix(), now_unix()),
|
||
)
|
||
inserted += 1
|
||
return inserted
|
||
|
||
|
||
def seed_taxid_whitelist(cursor: sqlite3.Cursor) -> int:
|
||
"""幂等写入税号白名单种子，返回新增条数。"""
|
||
inserted = 0
|
||
for tax_no, org_name, note in _SEED_WHITELIST:
|
||
cursor.execute("SELECT COUNT(*) FROM taxid_whitelist WHERE tax_no = ?", (tax_no,))
|
||
if int(cursor.fetchone()[0]) > 0:
|
||
continue
|
||
cursor.execute(
|
||
"""
|
||
INSERT INTO taxid_whitelist (tax_no, org_name, note, created_at, updated_at)
|
||
VALUES (?, ?, ?, ?, ?)
|
||
""",
|
||
(tax_no, org_name, note, now_unix(), now_unix()),
|
||
)
|
||
inserted += 1
|
||
return inserted
|
||
|
||
|
||
def seed_holidays(cursor: sqlite3.Cursor) -> int:
|
||
"""幂等写入节假日种子，返回新增条数。"""
|
||
inserted = 0
|
||
for holiday_date, holiday_name in _SEED_HOLIDAYS:
|
||
cursor.execute("SELECT COUNT(*) FROM holiday_calendar WHERE holiday_date = ?", (holiday_date,))
|
||
if int(cursor.fetchone()[0]) > 0:
|
||
continue
|
||
cursor.execute(
|
||
"""
|
||
INSERT INTO holiday_calendar (holiday_date, holiday_name, created_at, updated_at)
|
||
VALUES (?, ?, ?, ?)
|
||
""",
|
||
(holiday_date, holiday_name, now_unix(), now_unix()),
|
||
)
|
||
inserted += 1
|
||
return inserted
|
||
|
||
|
||
def seed_all(cursor: sqlite3.Cursor) -> dict[str, int]:
|
||
"""写入全部业务种子，返回各类新增条数（供 init-db 输出与测试断言）。"""
|
||
return {
|
||
"expense_standards": seed_expense_standards(cursor),
|
||
"taxid_whitelist": seed_taxid_whitelist(cursor),
|
||
"holiday_calendar": seed_holidays(cursor),
|
||
}
|