You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
106 lines
5.2 KiB
106 lines
5.2 KiB
3 days ago
|
# -*- coding: utf-8 -*-
|
||
|
|
"""环境盘点第二轮：列名订正后补查（票 02）"""
|
||
|
|
import sys, io
|
||
|
|
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8')
|
||
|
|
import pymysql
|
||
|
|
|
||
|
|
CONF = dict(host='8.129.84.155', port=3306, user='root',
|
||
|
|
password='Itc@123456', database='crm', charset='utf8mb4',
|
||
|
|
autocommit=True, connect_timeout=10, cursorclass=pymysql.cursors.DictCursor)
|
||
|
|
|
||
|
|
def q(sql, args=None):
|
||
|
|
with pymysql.connect(**CONF) as conn, conn.cursor() as cur:
|
||
|
|
cur.execute(sql, args)
|
||
|
|
return cur.fetchall()
|
||
|
|
|
||
|
|
def cols(t):
|
||
|
|
return [r['Field'] for r in q(f'SHOW COLUMNS FROM {t}')]
|
||
|
|
|
||
|
|
# 1) 关键表列结构
|
||
|
|
for t in ['opportunity', 'sys_dept', 'sys_role', 'sys_user_role', 'dict_group', 'dict_item', 'customer_pending_notice']:
|
||
|
|
print(f'== {t} 列 ==')
|
||
|
|
print(' ', cols(t))
|
||
|
|
|
||
|
|
# 2) 用户全量
|
||
|
|
print('\n== crm_auth_user 全量 ==')
|
||
|
|
for r in q('SELECT id, username, account, dept_id, enabled, employment_status, title FROM crm_auth_user WHERE deleted=0 ORDER BY id'):
|
||
|
|
print(f" {r['id']} | {r['username']} | acct={r['account']} | dept={r['dept_id']} | enabled={r['enabled']} | emp={r['employment_status']} | title={r['title']}")
|
||
|
|
|
||
|
|
# 3) 部门树
|
||
|
|
print('\n== sys_dept 全量 ==')
|
||
|
|
dc = cols('sys_dept')
|
||
|
|
namecol = 'dept_name' if 'dept_name' in dc else ('name' if 'name' in dc else dc[1])
|
||
|
|
pcol = 'parent_id' if 'parent_id' in dc else dc[2]
|
||
|
|
print(f' (namecol={namecol} parent={pcol})')
|
||
|
|
for r in q(f'SELECT id, {namecol} nm, {pcol} pid FROM sys_dept WHERE deleted=0 ORDER BY id'):
|
||
|
|
print(f" {r['id']} | {r['nm']} | parent={r['pid']}")
|
||
|
|
|
||
|
|
# 4) debug 用户角色
|
||
|
|
print('\n== debug 用户 739564171091247104 角色 ==')
|
||
|
|
rc = cols('sys_role')
|
||
|
|
codecol = 'role_code' if 'role_code' in rc else ('code' if 'code' in rc else rc[1])
|
||
|
|
nmcol = 'role_name' if 'role_name' in rc else ('name' if 'name' in rc else rc[2])
|
||
|
|
try:
|
||
|
|
for r in q(f"SELECT ur.role_id, r.{codecol} cd, r.{nmcol} nm FROM sys_user_role ur JOIN sys_role r ON r.id=ur.role_id WHERE ur.user_id='739564171091247104'"):
|
||
|
|
print(f" role={r['role_id']} {r['cd']} {r['nm']}")
|
||
|
|
except Exception as e:
|
||
|
|
print(' ERR:', str(e)[:120])
|
||
|
|
print(' sys_role 全量:')
|
||
|
|
for r in q(f'SELECT id, {codecol} cd, {nmcol} nm FROM sys_role WHERE deleted=0'):
|
||
|
|
print(f" {r['id']} | {r['cd']} | {r['nm']}")
|
||
|
|
|
||
|
|
# 5) 字典 24 组
|
||
|
|
print('\n== dict_group 24 组 ==')
|
||
|
|
gc = cols('dict_group')
|
||
|
|
print(' 列:', gc)
|
||
|
|
gcode = 'group_code' if 'group_code' in gc else gc[1]
|
||
|
|
gname = 'group_name' if 'group_name' in gc else ('name' if 'name' in gc else gc[2])
|
||
|
|
rows = q(f'SELECT {gcode} gc, {gname} gn FROM dict_group WHERE deleted=0 ORDER BY {gcode}') if 'deleted' in gc else q(f'SELECT {gcode} gc, {gname} gn FROM dict_group ORDER BY {gcode}')
|
||
|
|
for r in rows:
|
||
|
|
print(f" {r['gc']} | {r['gn']}")
|
||
|
|
print(' dict_item 按 group 分布:')
|
||
|
|
try:
|
||
|
|
for r in q(f"SELECT {gcode if 'group_code' in cols('dict_item') else 'group_code'} g, COUNT(*) c FROM dict_item GROUP BY g ORDER BY g"):
|
||
|
|
print(f" {r['g']}: {r['c']}")
|
||
|
|
except Exception as e:
|
||
|
|
ic = cols('dict_item')
|
||
|
|
print(' item 列:', ic, 'ERR:', str(e)[:80])
|
||
|
|
|
||
|
|
# 6) customer 存量明细（seed 前基线）
|
||
|
|
print('\n== customer 存量 5 条明细 ==')
|
||
|
|
for r in q("SELECT id, customer_no, customer_name, owner_user_id, owner_user_name_snapshot, owner_dept_id, archive_status, customer_stage, deleted FROM customer ORDER BY create_time"):
|
||
|
|
print(f" {r['id']} | {r['customer_no']} | {r['customer_name']} | owner={r['owner_user_id']}/{r['owner_user_name_snapshot']} dept={r['owner_dept_id']} | arch={r['archive_status']} stage={r['customer_stage']} del={r['deleted']}")
|
||
|
|
|
||
|
|
# 7) opportunity 名称列 + e2e 残留
|
||
|
|
print('\n== opportunity 存量 ==')
|
||
|
|
oc = cols('opportunity')
|
||
|
|
print(' 列:', oc)
|
||
|
|
namecand = [c for c in oc if 'name' in c or 'title' in c]
|
||
|
|
print(' 名称候选列:', namecand)
|
||
|
|
if namecand:
|
||
|
|
n = namecand[0]
|
||
|
|
print(f" e2e-% 残留({n}):", q(f"SELECT COUNT(*) c FROM opportunity WHERE {n} LIKE 'e2e-%'")[0]['c'])
|
||
|
|
for r in q(f'SELECT id, {n} nm FROM opportunity WHERE deleted=0 LIMIT 10'):
|
||
|
|
print(f" {r['id']} | {r['nm']}")
|
||
|
|
print(' opportunity_customer 明细(前10):')
|
||
|
|
for r in q('SELECT id, opportunity_id, customer_id, customer_role, delete_key, is_primary_intended FROM opportunity_customer ORDER BY id LIMIT 10'):
|
||
|
|
print(f" {r['id']} opp={r['opportunity_id']} cust={r['customer_id']} role={r['customer_role']} dk={r['delete_key']} prim={r['is_primary_intended']}")
|
||
|
|
|
||
|
|
# 8) 其他支撑数据
|
||
|
|
print('\n== 支撑数据 ==')
|
||
|
|
print(' sys_region:', q('SELECT COUNT(*) c FROM sys_region')[0]['c'])
|
||
|
|
print(' sys_menu:', q('SELECT COUNT(*) c FROM sys_menu')[0]['c'])
|
||
|
|
print(' sys_data_scope_module:', q('SELECT COUNT(*) c FROM sys_data_scope_module')[0]['c'])
|
||
|
|
try:
|
||
|
|
for r in q('SELECT module_code, scope_types FROM sys_data_scope_module'):
|
||
|
|
print(f" {r['module_code']}: {r['scope_types']}")
|
||
|
|
except Exception as e:
|
||
|
|
dsm = cols('sys_data_scope_module')
|
||
|
|
print(' dsm 列:', dsm, 'ERR:', str(e)[:80])
|
||
|
|
print(' user_saved_view:', q('SELECT COUNT(*) c FROM user_saved_view')[0]['c'])
|
||
|
|
print(' user_column_preference:', q('SELECT COUNT(*) c FROM user_column_preference')[0]['c'])
|
||
|
|
print(' customer_follow:', q('SELECT COUNT(*) c FROM customer_follow')[0]['c'])
|
||
|
|
print(' customer_reminder_rule:', q('SELECT COUNT(*) c FROM customer_reminder_rule')[0]['c'])
|
||
|
|
|
||
|
|
print('\nDONE2')
|