trueconf_bot/lk/test_lk_payslip.py

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_BASES = (
    "OU=-Пользователи,DC=sibcem,DC=ru",
    "OU=Планшеты,OU=enabled,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 = (
        "(&(objectClass=user)"
        "(|(mail={})"
        "(userPrincipalName={})"
        "(userPrincipalName={}))"
        "(sAMAccountName={}))".format(
            safe_user_id,
            safe_user_id,
            escape_filter_chars(short_username) + "@sibcem.ru",
            escape_filter_chars(short_username),
        )
    )

    for base in AD_BASES:
        conn.search(
            base,
            search_filter,
            attributes=['mobile', 'telephoneNumber']
        )
        if conn.entries:
            break

    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()