213 lines
4.9 KiB
Python
213 lines
4.9 KiB
Python
import re
|
|
from datetime import datetime
|
|
|
|
import pymssql
|
|
from ldap3 import Server, Connection, ALL
|
|
from ldap3.utils.conv import escape_filter_chars
|
|
|
|
|
|
# =========================================================
|
|
# НАСТРОЙКИ
|
|
# =========================================================
|
|
|
|
TEST_USER = "man.bogov@tcs.sibcem.ru"
|
|
|
|
AD_SERVER = "ldap://172.16.20.20"
|
|
AD_BASE = "OU=-Пользователи,DC=sibcem,DC=ru"
|
|
|
|
AD_USER = "sdesk-mail"
|
|
AD_PASSWORD = "fne?e!q.m8phcrGVAcqr"
|
|
|
|
SQL_SERVER = "SRVKEM-MOBILEIN.sibcem.ru"
|
|
SQL_DB_NAME = "BossCopy"
|
|
|
|
DB_USER = "SIBCEM\bot_tcs"
|
|
DB_PASSWORD = "Bav:fX#UwH8%atv4"
|
|
|
|
|
|
# =========================================================
|
|
# AD
|
|
# =========================================================
|
|
|
|
def get_phone_from_ad(user_id: str) -> str:
|
|
|
|
server = Server(AD_SERVER, get_info=ALL)
|
|
|
|
conn = Connection(
|
|
server,
|
|
user=AD_USER,
|
|
password=AD_PASSWORD,
|
|
auto_bind=True
|
|
)
|
|
|
|
safe_user_id = escape_filter_chars(user_id)
|
|
|
|
short_username = user_id.split("@")[0]
|
|
|
|
search_filter = (
|
|
f"(&(objectClass=user)"
|
|
f"(|(mail={safe_user_id})"
|
|
f"(userPrincipalName={safe_user_id})"
|
|
f"(userPrincipalName={escape_filter_chars(f'{short_username}@sibcem.ru')})"
|
|
f"(sAMAccountName={escape_filter_chars(short_username)})))"
|
|
)
|
|
|
|
conn.search(
|
|
AD_BASE,
|
|
search_filter,
|
|
attributes=['mobile', 'telephoneNumber']
|
|
)
|
|
|
|
if not conn.entries:
|
|
raise Exception("Пользователь не найден в AD")
|
|
|
|
entry = conn.entries[0]
|
|
|
|
raw_phone = (
|
|
str(entry.mobile.value)
|
|
if 'mobile' in entry and entry.mobile.value
|
|
else str(entry.telephoneNumber.value)
|
|
if 'telephoneNumber' in entry and entry.telephoneNumber.value
|
|
else None
|
|
)
|
|
|
|
if not raw_phone:
|
|
raise Exception("Телефон не найден")
|
|
|
|
clean_phone = re.sub(r'\D', '', raw_phone)
|
|
|
|
if clean_phone.startswith("8") and len(clean_phone) == 11:
|
|
return "7" + clean_phone[1:]
|
|
|
|
if len(clean_phone) == 10:
|
|
return "7" + clean_phone
|
|
|
|
return clean_phone
|
|
|
|
|
|
# =========================================================
|
|
# SQL
|
|
# =========================================================
|
|
|
|
def get_db_connection():
|
|
|
|
return pymssql.connect(
|
|
server=SQL_SERVER,
|
|
user=DB_USER,
|
|
password=DB_PASSWORD,
|
|
database=SQL_DB_NAME,
|
|
charset='cp1251'
|
|
)
|
|
|
|
|
|
# =========================================================
|
|
# MAIN
|
|
# =========================================================
|
|
|
|
def main():
|
|
|
|
print("=" * 80)
|
|
print("ТЕСТ РАСЧЕТНЫХ ЛИСТКОВ")
|
|
print("=" * 80)
|
|
|
|
date = datetime.now().date()
|
|
|
|
cmonth = date.year * 12 + date.month
|
|
|
|
print(f"Дата: {date}")
|
|
print(f"CMONTH: {cmonth}")
|
|
|
|
# -----------------------------------------------------
|
|
|
|
print("\n[1] Получение телефона из AD")
|
|
|
|
phone_full = get_phone_from_ad(TEST_USER)
|
|
|
|
print(f"Телефон из AD: {phone_full}")
|
|
|
|
phone = phone_full[1:].strip()
|
|
|
|
print(f"Телефон для SQL: {phone}")
|
|
|
|
# -----------------------------------------------------
|
|
|
|
print("\n[2] Подключение к SQL")
|
|
|
|
conn = get_db_connection()
|
|
|
|
cursor = conn.cursor()
|
|
|
|
# -----------------------------------------------------
|
|
|
|
print("\n[3] Поиск PR_CARD")
|
|
|
|
query = """
|
|
SELECT
|
|
ID,
|
|
PHONE
|
|
FROM UOV_SELFSERVICE_PR_CARD
|
|
WHERE PHONE = %s
|
|
"""
|
|
|
|
print(query)
|
|
|
|
cursor.execute(query, (phone,))
|
|
|
|
rows = cursor.fetchall()
|
|
|
|
print(f"\nНайдено записей: {len(rows)}")
|
|
|
|
if rows:
|
|
|
|
for row in rows:
|
|
|
|
print("-" * 60)
|
|
print(f"ID: {row[0]}")
|
|
print(f"PHONE: {row[1]}")
|
|
|
|
else:
|
|
|
|
print("\n❌ Запись не найдена")
|
|
|
|
print("\n[4] Поиск похожих телефонов")
|
|
|
|
query2 = """
|
|
SELECT TOP 20
|
|
ID,
|
|
PHONE
|
|
FROM UOV_SELFSERVICE_PR_CARD
|
|
WHERE
|
|
PHONE LIKE %s
|
|
OR RIGHT(RTRIM(PHONE), 10) = %s
|
|
"""
|
|
|
|
phone_tail = phone[-10:]
|
|
|
|
cursor.execute(
|
|
query2,
|
|
(
|
|
f"%{phone_tail}%",
|
|
phone_tail
|
|
)
|
|
)
|
|
|
|
rows2 = cursor.fetchall()
|
|
|
|
print(f"\nПохожих записей: {len(rows2)}")
|
|
|
|
for row in rows2:
|
|
|
|
print("-" * 60)
|
|
print(f"ID: {row[0]}")
|
|
print(f"PHONE: {row[1]}")
|
|
|
|
conn.close()
|
|
|
|
print("\n")
|
|
print("=" * 80)
|
|
print("ГОТОВО")
|
|
print("=" * 80)
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main() |