323 lines
10 KiB
Python
323 lines
10 KiB
Python
"""
|
||
初步核实审批表字段数据初始化脚本
|
||
根据Excel数据字段汇总表和Word模板分析，创建相关字段配置
|
||
"""
|
||
import pymysql
|
||
from datetime import datetime
|
||
import uuid
|
||
|
||
# 数据库连接配置
|
||
DB_CONFIG = {
|
||
'host': '152.136.177.240',
|
||
'port': 5012,
|
||
'user': 'finyx',
|
||
'password': '6QsGK6MpePZDE57Z',
|
||
'database': 'finyx',
|
||
'charset': 'utf8mb4'
|
||
}
|
||
|
||
# 固定值
|
||
TENANT_ID = 615873064429507639 # 从现有数据中获取
|
||
CREATED_BY = 655162080928945152 # 从现有数据中获取
|
||
CURRENT_TIME = datetime.now()
|
||
|
||
# 根据Excel数据设计的字段配置
|
||
# 字段类型：1-输入字段，2-输出字段
|
||
FIELDS = [
|
||
{
|
||
'name': '被核查人姓名',
|
||
'field_code': 'target_name',
|
||
'field_type': 2, # 输出字段
|
||
'description': '被核查人姓名'
|
||
},
|
||
{
|
||
'name': '被核查人员单位及职务',
|
||
'field_code': 'target_organization_and_position',
|
||
'field_type': 2, # 输出字段
|
||
'description': '被核查人员单位及职务（包括兼职）'
|
||
},
|
||
{
|
||
'name': '被核查人员单位',
|
||
'field_code': 'target_organization',
|
||
'field_type': 2, # 输出字段
|
||
'description': '被核查人员单位'
|
||
},
|
||
{
|
||
'name': '被核查人员职务',
|
||
'field_code': 'target_position',
|
||
'field_type': 2, # 输出字段
|
||
'description': '被核查人员职务'
|
||
},
|
||
{
|
||
'name': '被核查人员性别',
|
||
'field_code': 'target_gender',
|
||
'field_type': 2, # 输出字段
|
||
'description': '被核查人员性别（男/女，不用男性和女性）'
|
||
},
|
||
{
|
||
'name': '被核查人员出生年月',
|
||
'field_code': 'target_date_of_birth',
|
||
'field_type': 2, # 输出字段
|
||
'description': '被核查人员出生年月（YYYYMM格式，不需要日）'
|
||
},
|
||
{
|
||
'name': '被核查人员年龄',
|
||
'field_code': 'target_age',
|
||
'field_type': 2, # 输出字段
|
||
'description': '被核查人员年龄（数字，单位：岁）'
|
||
},
|
||
{
|
||
'name': '被核查人员文化程度',
|
||
'field_code': 'target_education_level',
|
||
'field_type': 2, # 输出字段
|
||
'description': '被核查人员文化程度（如：本科、大专、高中等）'
|
||
},
|
||
{
|
||
'name': '被核查人员政治面貌',
|
||
'field_code': 'target_political_status',
|
||
'field_type': 2, # 输出字段
|
||
'description': '被核查人员政治面貌（中共党员、群众等）'
|
||
},
|
||
{
|
||
'name': '被核查人员职级',
|
||
'field_code': 'target_professional_rank',
|
||
'field_type': 2, # 输出字段
|
||
'description': '被核查人员职级（如：正处级）'
|
||
},
|
||
{
|
||
'name': '线索来源',
|
||
'field_code': 'clue_source',
|
||
'field_type': 2, # 输出字段
|
||
'description': '线索来源'
|
||
},
|
||
{
|
||
'name': '主要问题线索',
|
||
'field_code': 'target_issue_description',
|
||
'field_type': 2, # 输出字段
|
||
'description': '主要问题线索描述'
|
||
},
|
||
{
|
||
'name': '初步核实审批表承办部门意见',
|
||
'field_code': 'department_opinion',
|
||
'field_type': 2, # 输出字段
|
||
'description': '初步核实审批表承办部门意见'
|
||
},
|
||
{
|
||
'name': '初步核实审批表填表人',
|
||
'field_code': 'filler_name',
|
||
'field_type': 2, # 输出字段
|
||
'description': '初步核实审批表填表人'
|
||
},
|
||
{
|
||
'name': '线索信息',
|
||
'field_code': 'clue_info',
|
||
'field_type': 1, # 输入字段
|
||
'description': '线索信息（用于AI解析）'
|
||
},
|
||
{
|
||
'name': '被核查人员工作基本情况线索',
|
||
'field_code': 'target_basic_info_clue',
|
||
'field_type': 1, # 输入字段
|
||
'description': '被核查人员工作基本情况线索（用于AI解析）'
|
||
}
|
||
]
|
||
|
||
# 文件配置
|
||
FILE_CONFIG = {
|
||
'name': '初步核实审批表',
|
||
'template_code': 'PRELIMINARY_VERIFICATION_APPROVAL',
|
||
'file_path': '/templates/初步核实审批表模板.docx', # MinIO相对路径
|
||
'business_type': 'INVESTIGATION', # 调查核实
|
||
'parent_id': None # 顶级分类，可以根据实际情况设置
|
||
}
|
||
|
||
|
||
def generate_id():
|
||
"""生成ID（使用时间戳+随机数的方式，模拟雪花算法）"""
|
||
import time
|
||
import random
|
||
timestamp = int(time.time() * 1000)
|
||
random_part = random.randint(100000, 999999)
|
||
return timestamp * 1000 + random_part
|
||
|
||
|
||
def init_fields(conn):
|
||
"""初始化字段数据"""
|
||
cursor = conn.cursor()
|
||
field_ids = {}
|
||
|
||
print("="*60)
|
||
print("开始初始化字段数据...")
|
||
print("="*60)
|
||
|
||
for field in FIELDS:
|
||
# 检查字段是否已存在
|
||
check_sql = """
|
||
SELECT id FROM f_polic_field
|
||
WHERE tenant_id = %s AND filed_code = %s
|
||
"""
|
||
cursor.execute(check_sql, (TENANT_ID, field['field_code']))
|
||
existing = cursor.fetchone()
|
||
|
||
if existing:
|
||
field_id = existing[0]
|
||
print(f"字段 '{field['name']}' (code: {field['field_code']}) 已存在，ID: {field_id}")
|
||
else:
|
||
field_id = generate_id()
|
||
insert_sql = """
|
||
INSERT INTO f_polic_field
|
||
(id, tenant_id, name, filed_code, field_type, created_time, created_by, updated_time, updated_by, state)
|
||
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
|
||
"""
|
||
cursor.execute(insert_sql, (
|
||
field_id,
|
||
TENANT_ID,
|
||
field['name'],
|
||
field['field_code'],
|
||
field['field_type'],
|
||
CURRENT_TIME,
|
||
CREATED_BY,
|
||
CURRENT_TIME,
|
||
CREATED_BY,
|
||
0 # state: 0表示未启用，1表示启用
|
||
))
|
||
print(f"✓ 创建字段: {field['name']} (code: {field['field_code']}), ID: {field_id}")
|
||
|
||
field_ids[field['field_code']] = field_id
|
||
|
||
conn.commit()
|
||
return field_ids
|
||
|
||
|
||
def init_file_config(conn):
|
||
"""初始化文件配置"""
|
||
cursor = conn.cursor()
|
||
|
||
print("\n" + "="*60)
|
||
print("开始初始化文件配置...")
|
||
print("="*60)
|
||
|
||
# 检查文件配置是否已存在
|
||
check_sql = """
|
||
SELECT id FROM f_polic_file_config
|
||
WHERE tenant_id = %s AND name = %s
|
||
"""
|
||
cursor.execute(check_sql, (TENANT_ID, FILE_CONFIG['name']))
|
||
existing = cursor.fetchone()
|
||
|
||
if existing:
|
||
file_config_id = existing[0]
|
||
print(f"文件配置 '{FILE_CONFIG['name']}' 已存在，ID: {file_config_id}")
|
||
else:
|
||
file_config_id = generate_id()
|
||
insert_sql = """
|
||
INSERT INTO f_polic_file_config
|
||
(id, tenant_id, parent_id, name, input_data, file_path, created_time, created_by, updated_time, updated_by, state)
|
||
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
|
||
"""
|
||
# input_data字段存储模板编码和业务类型（JSON格式）
|
||
import json
|
||
input_data = json.dumps({
|
||
'template_code': FILE_CONFIG['template_code'],
|
||
'business_type': FILE_CONFIG['business_type']
|
||
}, ensure_ascii=False)
|
||
|
||
cursor.execute(insert_sql, (
|
||
file_config_id,
|
||
TENANT_ID,
|
||
FILE_CONFIG['parent_id'],
|
||
FILE_CONFIG['name'],
|
||
input_data,
|
||
FILE_CONFIG['file_path'],
|
||
CURRENT_TIME,
|
||
CREATED_BY,
|
||
CURRENT_TIME,
|
||
CREATED_BY,
|
||
1 # state: 1表示启用
|
||
))
|
||
print(f"✓ 创建文件配置: {FILE_CONFIG['name']}, ID: {file_config_id}")
|
||
|
||
conn.commit()
|
||
return file_config_id
|
||
|
||
|
||
def init_file_field_relations(conn, file_config_id, field_ids):
|
||
"""初始化文件和字段的关联关系"""
|
||
cursor = conn.cursor()
|
||
|
||
print("\n" + "="*60)
|
||
print("开始建立文件和字段的关联关系...")
|
||
print("="*60)
|
||
|
||
# 只关联输出字段（field_type=2）
|
||
output_fields = {k: v for k, v in field_ids.items()
|
||
if any(f['field_code'] == k and f['field_type'] == 2 for f in FIELDS)}
|
||
|
||
for field_code, field_id in output_fields.items():
|
||
# 检查关联关系是否已存在
|
||
check_sql = """
|
||
SELECT id FROM f_polic_file_field
|
||
WHERE tenant_id = %s AND filed_id = %s AND file_id = %s
|
||
"""
|
||
cursor.execute(check_sql, (TENANT_ID, field_id, file_config_id))
|
||
existing = cursor.fetchone()
|
||
|
||
if existing:
|
||
print(f"关联关系已存在: 文件ID {file_config_id} <-> 字段ID {field_id} ({field_code})")
|
||
else:
|
||
insert_sql = """
|
||
INSERT INTO f_polic_file_field
|
||
(tenant_id, filed_id, file_id, created_time, created_by, updated_time, updated_by, state)
|
||
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
|
||
"""
|
||
cursor.execute(insert_sql, (
|
||
TENANT_ID,
|
||
field_id,
|
||
file_config_id,
|
||
CURRENT_TIME,
|
||
CREATED_BY,
|
||
CURRENT_TIME,
|
||
CREATED_BY,
|
||
0 # state: 0表示未启用，1表示启用
|
||
))
|
||
field_name = next(f['name'] for f in FIELDS if f['field_code'] == field_code)
|
||
print(f"✓ 建立关联: {field_name} ({field_code})")
|
||
|
||
conn.commit()
|
||
|
||
|
||
def main():
|
||
"""主函数"""
|
||
try:
|
||
# 连接数据库
|
||
conn = pymysql.connect(**DB_CONFIG)
|
||
print("数据库连接成功！\n")
|
||
|
||
# 初始化字段
|
||
field_ids = init_fields(conn)
|
||
|
||
# 初始化文件配置
|
||
file_config_id = init_file_config(conn)
|
||
|
||
# 建立关联关系
|
||
init_file_field_relations(conn, file_config_id, field_ids)
|
||
|
||
print("\n" + "="*60)
|
||
print("初始化完成！")
|
||
print("="*60)
|
||
print(f"\n文件配置ID: {file_config_id}")
|
||
print(f"创建的字段数量: {len(field_ids)}")
|
||
print(f"建立的关联关系数量: {len([f for f in FIELDS if f['field_type'] == 2])}")
|
||
|
||
conn.close()
|
||
|
||
except Exception as e:
|
||
print(f"\n错误: {e}")
|
||
import traceback
|
||
traceback.print_exc()
|
||
|
||
|
||
if __name__ == '__main__':
|
||
main()
|
||
|