63 lines
2.9 KiB
Python
63 lines
2.9 KiB
Python
import pyodbc
|
|
from config import ZUP_SQL_CONFIG
|
|
|
|
print("="*60)
|
|
print("🕵️♂️ ZUP DETECTIVE: ПОИСК СТАТУСА 'УВОЛЕН'")
|
|
print("="*60)
|
|
|
|
conn_str = (
|
|
f"DRIVER={ZUP_SQL_CONFIG['driver']};"
|
|
f"SERVER={ZUP_SQL_CONFIG['server']};"
|
|
f"DATABASE={ZUP_SQL_CONFIG['database']};"
|
|
f"UID={ZUP_SQL_CONFIG['user']};"
|
|
f"PWD={ZUP_SQL_CONFIG['password']};"
|
|
f"TrustServerCertificate=yes;"
|
|
)
|
|
|
|
with pyodbc.connect(conn_str) as conn:
|
|
cursor = conn.cursor()
|
|
|
|
# 1. Получаем ID наших подопытных
|
|
cursor.execute("SELECT TOP 1 _IDRRef FROM dbo._Reference299 WHERE _Description LIKE '%Шумский Алексей Анатольевич%'")
|
|
id_work = cursor.fetchone()[0]
|
|
|
|
cursor.execute("SELECT TOP 1 _IDRRef FROM dbo._Reference299 WHERE _Description LIKE '%Щелканов Дмитрий Викторович%'")
|
|
id_fired = cursor.fetchone()[0]
|
|
|
|
# 2. Сканируем два самых больших кадровых регистра, которые нашел наш сканер
|
|
for table in ['_InfoRg16684', '_InfoRg32796']:
|
|
print(f"\n🔍 АНАЛИЗ РЕГИСТРА: {table}")
|
|
|
|
# Ищем все колонки-ссылки
|
|
cursor.execute(f"SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('{table}') AND system_type_id = 173 AND max_length = 16")
|
|
cols_bin = [r[0] for r in cursor.fetchall()]
|
|
|
|
found = False
|
|
for col in cols_bin:
|
|
cursor.execute(f"SELECT COUNT(*) FROM dbo.{table} WITH (NOLOCK) WHERE {col} = ?", (id_work,))
|
|
if cursor.fetchone()[0] > 0:
|
|
print(f"✅ Найдена колонка связи с Сотрудником: {col}")
|
|
|
|
# Забираем полные строки обоих людей
|
|
cursor.execute(f"SELECT * FROM dbo.{table} WITH (NOLOCK) WHERE {col} = ?", (id_work,))
|
|
data_work = cursor.fetchone()
|
|
all_cols = [c[0] for c in cursor.description]
|
|
|
|
cursor.execute(f"SELECT * FROM dbo.{table} WITH (NOLOCK) WHERE {col} = ?", (id_fired,))
|
|
data_fired = cursor.fetchone()
|
|
|
|
if data_work and data_fired:
|
|
print("\n⚖️ ОТЛИЧИЯ В ДАННЫХ (Работает vs Уволен):")
|
|
for i, c in enumerate(all_cols):
|
|
# Выводим только те поля, где есть отличия
|
|
if data_work[i] != data_fired[i]:
|
|
print(f"Поле [{c}]:")
|
|
print(f" 🟢 Шумский: {data_work[i]}")
|
|
print(f" 🔴 Щелканов: {data_fired[i]}\n")
|
|
found = True
|
|
break
|
|
|
|
if found:
|
|
break
|
|
|
|
print("✅ Расследование завершено!") |