220 lines
8.6 KiB
Python
220 lines
8.6 KiB
Python
"""
|
||
生成模板 file_id 和关联关系的详细报告
|
||
重点检查每个模板的 file_id 是否正确，以及 f_polic_file_field 表的关联关系
|
||
"""
|
||
import sys
|
||
import pymysql
|
||
from pathlib import Path
|
||
from typing import Dict, List
|
||
from collections import defaultdict
|
||
|
||
# 设置控制台编码为UTF-8（Windows兼容）
|
||
if sys.platform == 'win32':
|
||
try:
|
||
sys.stdout.reconfigure(encoding='utf-8')
|
||
sys.stderr.reconfigure(encoding='utf-8')
|
||
except:
|
||
pass
|
||
|
||
# 数据库连接配置
|
||
DB_CONFIG = {
|
||
'host': '152.136.177.240',
|
||
'port': 5012,
|
||
'user': 'finyx',
|
||
'password': '6QsGK6MpePZDE57Z',
|
||
'database': 'finyx',
|
||
'charset': 'utf8mb4'
|
||
}
|
||
|
||
TENANT_ID = 615873064429507639
|
||
|
||
|
||
def generate_detailed_report():
|
||
"""生成详细的 file_id 和关联关系报告"""
|
||
print("="*80)
|
||
print("模板 file_id 和关联关系详细报告")
|
||
print("="*80)
|
||
|
||
# 连接数据库
|
||
try:
|
||
conn = pymysql.connect(**DB_CONFIG)
|
||
print("\n[OK] 数据库连接成功\n")
|
||
except Exception as e:
|
||
print(f"\n[ERROR] 数据库连接失败: {e}")
|
||
return
|
||
|
||
cursor = conn.cursor(pymysql.cursors.DictCursor)
|
||
|
||
try:
|
||
# 1. 查询所有有 file_path 的模板（实际模板文件，不是目录节点）
|
||
cursor.execute("""
|
||
SELECT id, name, template_code, file_path, state, parent_id
|
||
FROM f_polic_file_config
|
||
WHERE tenant_id = %s AND file_path IS NOT NULL AND file_path != ''
|
||
ORDER BY name, id
|
||
""", (TENANT_ID,))
|
||
|
||
all_templates = cursor.fetchall()
|
||
|
||
print(f"总模板数（有 file_path）: {len(all_templates)}\n")
|
||
|
||
# 2. 查询每个模板的关联字段
|
||
template_field_map = defaultdict(list)
|
||
|
||
cursor.execute("""
|
||
SELECT
|
||
fff.file_id,
|
||
fff.filed_id,
|
||
fff.state as relation_state,
|
||
fc.name as template_name,
|
||
fc.template_code,
|
||
f.name as field_name,
|
||
f.filed_code,
|
||
f.field_type,
|
||
CASE
|
||
WHEN f.field_type = 1 THEN '输入字段'
|
||
WHEN f.field_type = 2 THEN '输出字段'
|
||
ELSE '未知'
|
||
END as field_type_name
|
||
FROM f_polic_file_field fff
|
||
INNER JOIN f_polic_file_config fc ON fff.file_id = fc.id AND fff.tenant_id = fc.tenant_id
|
||
INNER JOIN f_polic_field f ON fff.filed_id = f.id AND fff.tenant_id = f.tenant_id
|
||
WHERE fff.tenant_id = %s
|
||
ORDER BY fff.file_id, f.field_type, f.name
|
||
""", (TENANT_ID,))
|
||
|
||
all_relations = cursor.fetchall()
|
||
|
||
for rel in all_relations:
|
||
template_field_map[rel['file_id']].append(rel)
|
||
|
||
# 3. 按模板分组显示
|
||
print("="*80)
|
||
print("每个模板的 file_id 和关联字段详情")
|
||
print("="*80)
|
||
|
||
# 按名称分组，显示重复的模板
|
||
templates_by_name = defaultdict(list)
|
||
for template in all_templates:
|
||
templates_by_name[template['name']].append(template)
|
||
|
||
duplicate_templates = {name: tmpls for name, tmpls in templates_by_name.items() if len(tmpls) > 1}
|
||
|
||
if duplicate_templates:
|
||
print("\n[WARN] 发现重复名称的模板:\n")
|
||
for name, tmpls in duplicate_templates.items():
|
||
print(f" 模板名称: {name}")
|
||
for tmpl in tmpls:
|
||
field_count = len(template_field_map.get(tmpl['id'], []))
|
||
input_count = sum(1 for f in template_field_map.get(tmpl['id'], []) if f['field_type'] == 1)
|
||
output_count = sum(1 for f in template_field_map.get(tmpl['id'], []) if f['field_type'] == 2)
|
||
print(f" - file_id: {tmpl['id']}")
|
||
print(f" template_code: {tmpl.get('template_code', 'N/A')}")
|
||
print(f" file_path: {tmpl.get('file_path', 'N/A')}")
|
||
print(f" 关联字段: 总计 {field_count} 个 (输入 {input_count}, 输出 {output_count})")
|
||
print()
|
||
|
||
# 4. 显示每个模板的详细信息
|
||
print("\n" + "="*80)
|
||
print("所有模板的 file_id 和关联字段统计")
|
||
print("="*80)
|
||
|
||
for template in all_templates:
|
||
file_id = template['id']
|
||
name = template['name']
|
||
template_code = template.get('template_code', 'N/A')
|
||
file_path = template.get('file_path', 'N/A')
|
||
|
||
fields = template_field_map.get(file_id, [])
|
||
input_fields = [f for f in fields if f['field_type'] == 1]
|
||
output_fields = [f for f in fields if f['field_type'] == 2]
|
||
|
||
print(f"\n模板: {name}")
|
||
print(f" file_id: {file_id}")
|
||
print(f" template_code: {template_code}")
|
||
print(f" file_path: {file_path}")
|
||
print(f" 关联字段: 总计 {len(fields)} 个")
|
||
print(f" - 输入字段 (field_type=1): {len(input_fields)} 个")
|
||
print(f" - 输出字段 (field_type=2): {len(output_fields)} 个")
|
||
|
||
if len(fields) == 0:
|
||
print(f" [WARN] 该模板没有关联任何字段")
|
||
|
||
# 5. 检查关联关系的完整性
|
||
print("\n" + "="*80)
|
||
print("关联关系完整性检查")
|
||
print("="*80)
|
||
|
||
# 检查是否有 file_id 在 f_polic_file_field 中但没有对应的文件配置
|
||
cursor.execute("""
|
||
SELECT DISTINCT fff.file_id
|
||
FROM f_polic_file_field fff
|
||
LEFT JOIN f_polic_file_config fc ON fff.file_id = fc.id AND fff.tenant_id = fc.tenant_id
|
||
WHERE fff.tenant_id = %s AND fc.id IS NULL
|
||
""", (TENANT_ID,))
|
||
orphan_file_ids = cursor.fetchall()
|
||
|
||
if orphan_file_ids:
|
||
print(f"\n[ERROR] 发现孤立的 file_id（在 f_polic_file_field 中但不在 f_polic_file_config 中）:")
|
||
for item in orphan_file_ids:
|
||
print(f" - file_id: {item['file_id']}")
|
||
else:
|
||
print("\n[OK] 所有关联关系的 file_id 都有效")
|
||
|
||
# 检查是否有 filed_id 在 f_polic_file_field 中但没有对应的字段
|
||
cursor.execute("""
|
||
SELECT DISTINCT fff.filed_id
|
||
FROM f_polic_file_field fff
|
||
LEFT JOIN f_polic_field f ON fff.filed_id = f.id AND fff.tenant_id = f.tenant_id
|
||
WHERE fff.tenant_id = %s AND f.id IS NULL
|
||
""", (TENANT_ID,))
|
||
orphan_field_ids = cursor.fetchall()
|
||
|
||
if orphan_field_ids:
|
||
print(f"\n[ERROR] 发现孤立的 filed_id（在 f_polic_file_field 中但不在 f_polic_field 中）:")
|
||
for item in orphan_field_ids:
|
||
print(f" - filed_id: {item['filed_id']}")
|
||
else:
|
||
print("\n[OK] 所有关联关系的 filed_id 都有效")
|
||
|
||
# 6. 统计汇总
|
||
print("\n" + "="*80)
|
||
print("统计汇总")
|
||
print("="*80)
|
||
|
||
total_templates = len(all_templates)
|
||
templates_with_fields = len([t for t in all_templates if len(template_field_map.get(t['id'], [])) > 0])
|
||
templates_without_fields = total_templates - templates_with_fields
|
||
|
||
total_relations = len(all_relations)
|
||
total_input_relations = sum(1 for r in all_relations if r['field_type'] == 1)
|
||
total_output_relations = sum(1 for r in all_relations if r['field_type'] == 2)
|
||
|
||
print(f"\n模板统计:")
|
||
print(f" 总模板数: {total_templates}")
|
||
print(f" 有关联字段的模板: {templates_with_fields}")
|
||
print(f" 无关联字段的模板: {templates_without_fields}")
|
||
|
||
print(f"\n关联关系统计:")
|
||
print(f" 总关联关系数: {total_relations}")
|
||
print(f" 输入字段关联: {total_input_relations}")
|
||
print(f" 输出字段关联: {total_output_relations}")
|
||
|
||
if duplicate_templates:
|
||
print(f"\n[WARN] 发现 {len(duplicate_templates)} 个模板名称有重复记录")
|
||
print(" 建议: 确认每个模板应该使用哪个 file_id，并清理重复记录")
|
||
|
||
if templates_without_fields:
|
||
print(f"\n[WARN] 发现 {templates_without_fields} 个模板没有关联任何字段")
|
||
print(" 建议: 检查这些模板是否需要关联字段")
|
||
|
||
finally:
|
||
cursor.close()
|
||
conn.close()
|
||
print("\n数据库连接已关闭")
|
||
|
||
|
||
if __name__ == '__main__':
|
||
generate_detailed_report()
|
||
|