378 lines
18 KiB
Python
378 lines
18 KiB
Python
# /opt/trueconf_bot/lk/info_service.py
|
||
import re
|
||
import logging
|
||
import calendar
|
||
import pymssql
|
||
from datetime import datetime
|
||
|
||
import config.config as config
|
||
|
||
logger = logging.getLogger(__name__)
|
||
|
||
def get_db_connection():
|
||
if not config.DB_USER or not config.DB_PASSWORD:
|
||
raise Exception("Пароли БД не загружены из Passwork.")
|
||
return pymssql.connect(
|
||
server=config.SQL_SERVER,
|
||
user=config.DB_USER,
|
||
password=config.DB_PASSWORD,
|
||
database=config.SQL_DB_NAME,
|
||
charset='cp1251'
|
||
)
|
||
|
||
def get_lk_profile_by_card_id(card_id: str) -> dict:
|
||
"""Запрос 1: Получение информации о подразделении, должности и стаже по CARD_ID"""
|
||
try:
|
||
current_date = datetime.now().strftime('%Y-%m-%d')
|
||
conn = get_db_connection()
|
||
cursor = conn.cursor()
|
||
query = """
|
||
select f.SNAME [Компания], d.NAME [Отдел], appoint.NAME [Должность], rtrim(ltrim(e.STAGO)) [Страховой стаж], rtrim(ltrim(e.STAGP)) [Стаж на предприятии]
|
||
from (select e.ID, e.FIRM_ID, st.STAGO, st.STAGP
|
||
from UOV_SELFSERVICE_PR_EMP e
|
||
join UOV_SELFSERVICE_PR_CARD c on c.ID=e.CARD_ID
|
||
join UOV_SELFSERVICE_PR_STAG_CUR st on st.CARD_ID=c.ID
|
||
where c.ID=%s
|
||
group by e.ID , e.FIRM_ID, st.STAGO, st.STAGP) as e
|
||
join UOV_SELFSERVICE_PR_TRANS t on t.EMP_ID = e.ID
|
||
join UOV_SELFSERVICE_HR_FIRM f on f.ID = e.FIRM_ID
|
||
join UOV_SELFSERVICE_HR_DEPT d on d.ID = t.DEPT_ID
|
||
join UOV_SELFSERVICE_ST_APPOINT appoint on appoint.ID = t.APPOINT_ID
|
||
where (t.D_FROM <= cast(%s as date)
|
||
and (t.D_TO >= cast(%s as date)
|
||
or t.D_TO = cast('1999-12-31' as date)))
|
||
group by f.SNAME, d.NAME, appoint.NAME, rtrim(ltrim(e.STAGO)), rtrim(ltrim(e.STAGP))
|
||
"""
|
||
cursor.execute(query, (card_id, current_date, current_date))
|
||
row = cursor.fetchone()
|
||
conn.close()
|
||
if not row: raise Exception(f"Запись сотрудника по CARD_ID {card_id} не найдена в БД.")
|
||
return {
|
||
'company': str(row[0]).strip() if row[0] else "",
|
||
'dept': str(row[1]).strip() if row[1] else "",
|
||
'job': str(row[2]).strip() if row[2] else "",
|
||
'stag_o': str(row[3]).strip() if row[3] else "0",
|
||
'stag_p': str(row[4]).strip() if row[4] else "0"
|
||
}
|
||
except Exception as e: raise Exception(f"SQL Profile Error: {e}")
|
||
|
||
def get_vacation_records(card_id: str) -> tuple:
|
||
"""Возвращает статический заголовок (баланс + планы) и список строк истории отпусков"""
|
||
try:
|
||
conn = get_db_connection()
|
||
cursor = conn.cursor()
|
||
|
||
cursor.execute("SELECT TOP 1 e.ID FROM UOV_SELFSERVICE_PR_EMP e WHERE e.CARD_ID = %s", (card_id,))
|
||
row = cursor.fetchone()
|
||
if not row:
|
||
conn.close()
|
||
return "🌴 <b>Информация об отпусках</b>\n\nСотрудник не найден в базе данных.", []
|
||
emp_id = row[0]
|
||
|
||
# Балансы
|
||
q_balance = """
|
||
SELECT comp.LEAVE_NAME, comp.DAYS FROM UO_SELFSERVICE_LEAVE_COMP as comp
|
||
INNER JOIN UOV_SELFSERVICE_PR_TRANS as trans ON trans.EMP_ID = comp.EMP_ID
|
||
WHERE GETDATE() BETWEEN trans.D_FROM and trans.D_TO AND comp.EMP_ID = %s
|
||
"""
|
||
cursor.execute(q_balance, (emp_id,))
|
||
balances = cursor.fetchall()
|
||
|
||
# Планы
|
||
q_plans = """
|
||
SELECT e.D_FROM, e.DAYS
|
||
FROM UOV_SELFSERVICE_PR_LEAVE_PLAN e
|
||
WHERE e.EMP_ID = %s AND e.D_FROM >= CAST(GETDATE() AS DATE)
|
||
ORDER BY e.D_FROM ASC
|
||
"""
|
||
cursor.execute(q_plans, (emp_id,))
|
||
plans = cursor.fetchall()
|
||
|
||
# Архив всей истории
|
||
q_history = """
|
||
SELECT e.D_FROM, e.D_TO, e.DAYS, CAST(e.NAME AS VARCHAR(250)), e.ADDITIONAL
|
||
FROM UOV_SELFSERVICE_PR_LEAVE_ALL e
|
||
WHERE e.EMP_ID = %s AND e.D_FROM < CAST(GETDATE() AS DATE)
|
||
ORDER BY e.D_FROM DESC
|
||
"""
|
||
cursor.execute(q_history, (emp_id,))
|
||
history = cursor.fetchall()
|
||
conn.close()
|
||
|
||
# Формируем неизменяемую верхнюю часть (шапку) справки
|
||
static_header = "🌴 <b>Информация об отпусках</b>\n\n🟢 <b>Доступный остаток:</b>\n"
|
||
if balances:
|
||
for b in balances:
|
||
b_name = str(b[0]).strip() if b[0] else "Ежегодный оплачиваемый отпуск"
|
||
static_header += f"• <b>{int(b[1]) if b[1] else 0} дн.</b> <i>({b_name})</i>\n"
|
||
else:
|
||
static_header += "• <b>0 дн.</b>\n"
|
||
|
||
static_header += "\n⏳ <b>Предстоящие отпуска:</b>\n"
|
||
if plans:
|
||
for p in plans:
|
||
d_from = p[0].strftime('%d.%m.%Y') if hasattr(p[0], 'strftime') else str(p[0])[:10]
|
||
static_header += f"• <b>{d_from}</b> — <b>{int(p[1]) if p[1] else 0} дн.</b>\n"
|
||
else:
|
||
static_header += "• <i>Нет запланированных отпусков</i>\n"
|
||
|
||
# Нарезаем строки истории в плоский массив для пагинатора
|
||
history_lines = []
|
||
if history:
|
||
for h in history:
|
||
h_from = h[0].strftime('%d.%m.%Y') if hasattr(h[0], 'strftime') else str(h[0])[:10]
|
||
h_to = h[1].strftime('%d.%m.%Y') if hasattr(h[1], 'strftime') else str(h[1])[:10]
|
||
h_days = int(h[2]) if h[2] else 0
|
||
|
||
if h[3] and str(h[3]).strip():
|
||
type_str = str(h[3]).strip()
|
||
else:
|
||
type_str = "Дополнительный" if h[4] == 1 else "Основной"
|
||
|
||
history_lines.append(f"• {h_from} — {h_to} : 🔹 <b>{h_days} дн.</b> <i>({type_str})</i>")
|
||
|
||
return static_header, history_lines
|
||
except Exception as e:
|
||
raise Exception(f"SQL Vacation Records Error: {e}")
|
||
|
||
def get_sick_leaves_text(card_id: str) -> str:
|
||
"""Запрашивает историю больничных листов строго по CARD_ID"""
|
||
try:
|
||
conn = get_db_connection()
|
||
cursor = conn.cursor()
|
||
cursor.execute("SELECT e.ID FROM UOV_SELFSERVICE_PR_EMP e WHERE e.CARD_ID = %s", (card_id,))
|
||
row = cursor.fetchone()
|
||
if not row:
|
||
conn.close()
|
||
return "📑 <b>Ваши больничные листы:</b>\n\nСотрудник не найден в базе данных."
|
||
emp_id = row[0]
|
||
|
||
query = """
|
||
SELECT e.D_FROM, e.D_TO
|
||
FROM UOV_SELFSERVICE_PR_ILL e
|
||
WHERE e.EMP_ID = %s
|
||
ORDER BY e.D_FROM DESC
|
||
"""
|
||
cursor.execute(query, (emp_id,))
|
||
results = cursor.fetchall()
|
||
conn.close()
|
||
|
||
if not results:
|
||
return "📑 <b>Ваши больничные листы:</b>\n\nНичего не найдено."
|
||
|
||
response = "📑 <b>Ваши больничные листы:</b>\n\n"
|
||
for r in results:
|
||
d_from = r[0].strftime('%d.%m.%Y') if hasattr(r[0], 'strftime') else str(r[0])[:10]
|
||
d_to = r[1].strftime('%d.%m.%Y') if hasattr(r[1], 'strftime') else str(r[1])[:10]
|
||
|
||
days_count = ""
|
||
if hasattr(r[0], 'strftime') and hasattr(r[1], 'strftime'):
|
||
delta_days = (r[1] - r[0]).days + 1
|
||
days_count = f" — <b>{delta_days} дн.</b>"
|
||
|
||
response += f"🩺 <code>{d_from}</code> по <code>{d_to}</code>{days_count}\n"
|
||
return response
|
||
except Exception as e:
|
||
raise Exception(f"SQL Sick Leaves Error: {e}")
|
||
|
||
def get_missions_records(card_id: str) -> list:
|
||
"""Возвращает структурированный список pre-formatted текстовых блоков командировок"""
|
||
try:
|
||
conn = get_db_connection()
|
||
cursor = conn.cursor()
|
||
query = """
|
||
SELECT mp.D_FROM, mp.D_TO, CAST(mp.NAME AS VARCHAR(250)), CAST(mp.CITY AS VARCHAR(250))
|
||
FROM UOV_SELFSERVICE_PR_EMP e
|
||
JOIN UOV_SELFSERVICE_PR_MISSION m ON e.ID = m.EMP_ID
|
||
JOIN UOV_SELFSERVICE_PR_MISSION_P mp ON mp.MISSION_ID = m.ID
|
||
WHERE e.CARD_ID = %s
|
||
ORDER BY mp.D_FROM DESC
|
||
"""
|
||
cursor.execute(query, (card_id,))
|
||
results = cursor.fetchall()
|
||
conn.close()
|
||
|
||
mission_blocks = []
|
||
for r in results:
|
||
d_from = r[0].strftime('%d.%m.%Y') if hasattr(r[0], 'strftime') else str(r[0])[:10]
|
||
d_to = r[1].strftime('%d.%m.%Y') if hasattr(r[1], 'strftime') else str(r[1])[:10]
|
||
firm_name = str(r[2]).strip() if r[2] else "Организация не указана"
|
||
city = str(r[3]).strip() if r[3] else "Город не указан"
|
||
|
||
block = f"• 📅 <code>{d_from}</code> — <code>{d_to}</code>\n 📍 г. {city}, {firm_name}"
|
||
mission_blocks.append(block)
|
||
|
||
return mission_blocks
|
||
except Exception as e:
|
||
raise Exception(f"SQL Missions Records Error: {e}")
|
||
|
||
def get_transfers_records(card_id: str) -> list:
|
||
"""Возвращает структурированный список кадровых переходов сотрудника по его CARD_ID"""
|
||
try:
|
||
conn = get_db_connection()
|
||
cursor = conn.cursor()
|
||
query = """
|
||
SELECT
|
||
tr.D_FROM,
|
||
CASE
|
||
WHEN tr.D_TO = CAST('1999-12-31' AS DATE) THEN CAST('2099-12-31' AS DATE)
|
||
ELSE tr.D_TO
|
||
END as D_TO,
|
||
CAST(d.NAME AS VARCHAR(250)) as DEPT,
|
||
CAST(a.NAME AS VARCHAR(250)) as APPOINT
|
||
FROM UOV_SELFSERVICE_PR_EMP e
|
||
JOIN UOV_SELFSERVICE_PR_TRANS tr ON tr.EMP_ID = e.ID
|
||
JOIN UOV_SELFSERVICE_HR_DEPT d ON d.ID = tr.DEPT_ID
|
||
JOIN UOV_SELFSERVICE_ST_APPOINT a ON a.ID = tr.APPOINT_ID
|
||
WHERE e.CARD_ID = %s
|
||
ORDER BY tr.D_FROM DESC
|
||
"""
|
||
cursor.execute(query, (card_id,))
|
||
results = cursor.fetchall()
|
||
conn.close()
|
||
|
||
transfer_blocks = []
|
||
for r in results:
|
||
d_from = r[0].strftime('%d.%m.%Y') if hasattr(r[0], 'strftime') else str(r[0])[:10]
|
||
if r[1] and (r[1].year == 2099 or r[1].year == 1999):
|
||
d_to = "наст. время"
|
||
else:
|
||
d_to = r[1].strftime('%d.%m.%Y') if hasattr(r[1], 'strftime') else str(r[1])[:10]
|
||
|
||
dept = str(r[2]).strip() if r[2] else "Не указан"
|
||
appoint = str(r[3]).strip() if r[3] else "Не указана"
|
||
|
||
block = f"• ⏳ <b>{d_from} — {d_to}</b>\n 💼 <b>Должность:</b> {appoint}\n 📂 <b>Отдел:</b> {dept}"
|
||
transfer_blocks.append(block)
|
||
|
||
return transfer_blocks
|
||
except Exception as e:
|
||
raise Exception(f"SQL Transfers Records Error: {e}")
|
||
|
||
def get_rewards_text(card_id: str) -> str:
|
||
"""Запрашивает историю наград по CARD_ID"""
|
||
try:
|
||
conn = get_db_connection()
|
||
cursor = conn.cursor()
|
||
cursor.execute("""
|
||
SELECT e.D, CAST(e.NAME AS VARCHAR(1000))
|
||
FROM UOV_SELFSERVICE_PR_REW_ENC e
|
||
WHERE e.CARD_ID = %s
|
||
ORDER BY e.D
|
||
""", (card_id,))
|
||
results = cursor.fetchall()
|
||
conn.close()
|
||
|
||
if not results:
|
||
return "🏆 <b>Ваши награды и поощрения:</b>\n\nНичего не найдено."
|
||
|
||
response = "🏆 <b>Ваши награды и поощрения:</b>\n\n"
|
||
for r in results:
|
||
date_str = r[0].strftime('%d.%m.%Y') if hasattr(r[0], 'strftime') else str(r[0])[:10]
|
||
response += f"🏅 <b>{date_str}</b> — {str(r[1]).strip()}\n"
|
||
return response
|
||
except Exception as e:
|
||
raise Exception(f"SQL Rewards Error: {e}")
|
||
|
||
def get_tabel_report_text(card_id: str, year: int, month: int) -> str:
|
||
"""Формирует отчет по табелю из UOV_SELFSERVICE_TB_TABEL на основе CARD_ID"""
|
||
try:
|
||
conn = get_db_connection()
|
||
cursor = conn.cursor()
|
||
|
||
cursor.execute("SELECT e.ID FROM UOV_SELFSERVICE_PR_EMP e WHERE e.CARD_ID = %s", (card_id,))
|
||
row = cursor.fetchone()
|
||
if not row:
|
||
conn.close()
|
||
return "📋 <b>Рабочий табель:</b>\n\nСотрудник не найден в базе данных."
|
||
emp_id = row[0]
|
||
|
||
query = """
|
||
SELECT e.D, e.TDAY_ID, e.H
|
||
FROM UOV_SELFSERVICE_TB_TABEL e
|
||
WHERE e.EMP_ID = %s AND YEAR(e.D) = %s AND MONTH(e.D) = %s
|
||
ORDER BY e.D ASC
|
||
"""
|
||
cursor.execute(query, (emp_id, year, month))
|
||
records = cursor.fetchall()
|
||
conn.close()
|
||
|
||
if not records:
|
||
return f"📋 <b>Рабочий табель за {month:02d}.{year} г.</b>\n\nДанные отсутствуют."
|
||
|
||
tabel_map = {r[0].day if hasattr(r[0], 'day') else int(str(r[0])[-2:]): (str(r[1]).strip(), float(r[2]) if r[2] is not None else 0.0) for r in records}
|
||
|
||
total_days = 0
|
||
total_hours = 0.0
|
||
for code, hours in tabel_map.values():
|
||
if code == 'Я':
|
||
total_days += 1
|
||
total_hours += hours
|
||
|
||
months_names = {1: "Январь", 2: "Февраль", 3: "Март", 4: "Апрель", 5: "Май", 6: "Июнь", 7: "Июль", 8: "Август", 9: "Сентябрь", 10: "Октябрь", 11: "Ноябрь", 12: "Декабрь"}
|
||
|
||
report = (
|
||
f"📅 <b>Рабочий табель за {months_names.get(month, 'Указанный месяц')} {year} г.</b>\n\n"
|
||
f"📊 <b>Итого за месяц:</b>\n"
|
||
f"• Отработано дней: <b>{total_days}</b>\n"
|
||
f"• Всего часов: <b>{total_hours:.1f} ч.</b>\n"
|
||
f"• Переработки / Недоработки: <b>0 ч.</b>\n"
|
||
)
|
||
|
||
days_in_month = calendar.monthrange(year, month)[1]
|
||
weekday_ru = ["Пн", "Вт", "Ср", "Чт", "Пт", "Сб", "Вс"]
|
||
current_week_days = []
|
||
week_counter = 1
|
||
|
||
for day in range(1, days_in_month + 1):
|
||
cur_date = datetime(year, month, day)
|
||
wday_idx = cur_date.weekday()
|
||
|
||
code, hours = tabel_map.get(day, ('В', 0.0))
|
||
current_week_days.append((day, weekday_ru[wday_idx], code, hours))
|
||
|
||
if wday_idx == 6 or day == days_in_month:
|
||
w_start = f"{current_week_days[0][0]:02d}.{month:02d}"
|
||
w_end = f"{current_week_days[-1][0]:02d}.{month:02d}"
|
||
|
||
report += f"\n🔹 <b>Неделя {week_counter} ({w_start} — {w_end}):</b>\n"
|
||
|
||
idx = 0
|
||
while idx < len(current_week_days):
|
||
d_num, d_name, c_code, c_hours = current_week_days[idx]
|
||
|
||
if c_code == 'Я' and c_hours == 8.2:
|
||
run_start = d_name
|
||
run_end = d_name
|
||
count = 1
|
||
while idx + 1 < len(current_week_days) and current_week_days[idx+1][2] == 'Я' and current_week_days[idx+1][3] == 8.2:
|
||
idx += 1
|
||
run_end = current_week_days[idx][1]
|
||
count += 1
|
||
range_str = f"{run_start} - {run_end}" if count > 1 else f"{run_start}"
|
||
report += f"• {range_str}: 🛠 <b>Явка</b> ({count} дн. по 8.2 ч.)\n"
|
||
|
||
elif c_code == 'В' and c_hours == 0.0:
|
||
run_start = d_name
|
||
run_end = d_name
|
||
count = 1
|
||
while idx + 1 < len(current_week_days) and current_week_days[idx+1][2] == 'В' and current_week_days[idx+1][3] == 0.0:
|
||
idx += 1
|
||
run_end = current_week_days[idx][1]
|
||
count += 1
|
||
range_str = f"{run_start} - {run_end}" if count > 1 else f"{run_start}"
|
||
report += f"• {range_str}: 🏖 <b>Выходные</b> (0 ч.)\n"
|
||
|
||
else:
|
||
emoji = "🛠" if c_code == 'Я' else "⏱" if c_code in ['Б', 'БЛ'] else "🏖"
|
||
label = "Явка" if c_code == 'Я' else "Больничный" if c_code in ['Б', 'БЛ'] else "Отпуск" if c_code == 'ОТ' else "Выходной"
|
||
report += f"• {d_name}: {emoji} <b>{label}</b> ({c_hours} ч.)\n"
|
||
|
||
idx += 1
|
||
|
||
current_week_days = []
|
||
week_counter += 1
|
||
|
||
return report
|
||
except Exception as e:
|
||
raise Exception(f"SQL Tabel Parser Error: {e}") |