256 lines
12 KiB
Python
256 lines
12 KiB
Python
"""
|
||
===============================================================================
|
||
FILE: services/presence_service.py
|
||
ROLE: Сервис оперативного мониторинга («Кто в здании прямо сейчас»).
|
||
1. Строгое соответствие общему штату 1С (286 чел.).
|
||
2. Служебный персонал (уборщики, контролеры) маркируется как EXCLUDED
|
||
и не искажает вкладки "Не пришли" и "В здании".
|
||
3. Сотрудники флигеля/двора (Чупряев, Пухаренко, БЛ) при выходе через
|
||
турникет во двор остаются со статусом "В здании (Флигель/Двор)".
|
||
===============================================================================
|
||
"""
|
||
|
||
import logging
|
||
from datetime import datetime
|
||
from typing import Dict, Any, List, Optional
|
||
import pandas as pd
|
||
|
||
from core.connection import get_connection
|
||
from config import DATE_TODAY, normalize_fio
|
||
from services.exceptions_repo import get_all_exceptions_from_db
|
||
from services.data_loader import load_1c_data_smart
|
||
from services.scud_export import run_export
|
||
|
||
logger = logging.getLogger("PRESENCE_SERVICE")
|
||
|
||
|
||
def get_latest_zup_staff() -> Optional[pd.DataFrame]:
|
||
"""Загружает самый свежий доступный срез штата из zup_staff."""
|
||
with get_connection(row_factory=True) as conn:
|
||
cursor = conn.cursor()
|
||
cursor.execute("""
|
||
SELECT snapshot_date
|
||
FROM zup_staff
|
||
ORDER BY
|
||
SUBSTR(snapshot_date, 7, 4) DESC,
|
||
SUBSTR(snapshot_date, 4, 2) DESC,
|
||
SUBSTR(snapshot_date, 1, 2) DESC,
|
||
id DESC
|
||
LIMIT 1
|
||
""")
|
||
row = cursor.fetchone()
|
||
if not row:
|
||
return None
|
||
|
||
latest_date = row[0]
|
||
df = pd.read_sql_query(
|
||
"SELECT fio as 'ФИО', fio_clean, department as 'Подразделение', position as 'Должность' FROM zup_staff WHERE snapshot_date = ?",
|
||
conn, params=(latest_date,)
|
||
)
|
||
return df if not df.empty else None
|
||
|
||
|
||
def get_live_presence(date_str: Optional[str] = None, force_refresh: bool = False) -> Dict[str, Any]:
|
||
clean_date = (date_str or DATE_TODAY).replace('_', '.')
|
||
data_source = "LOCAL_SQLITE"
|
||
|
||
# 1. Принудительный опрос MS SQL при запросе
|
||
if force_refresh:
|
||
logger.info(f"[Presence] Прямой опрос MS SQL Орион за {clean_date}...")
|
||
try:
|
||
run_export(input_date=clean_date, save_xlsx=False, debug=False)
|
||
data_source = "LIVE_MSSQL"
|
||
except Exception as e:
|
||
logger.error(f"[Presence] Ошибка при обращении к MS SQL: {e}")
|
||
|
||
# 2. Выборка последних событий по каждому сотруднику за сегодня
|
||
events_raw = []
|
||
with get_connection(row_factory=True) as conn:
|
||
cursor = conn.cursor()
|
||
query = """
|
||
WITH RankedEvents AS (
|
||
SELECT
|
||
hoz_organ,
|
||
fio,
|
||
fio_clean,
|
||
department,
|
||
time_val,
|
||
direction,
|
||
ROW_NUMBER() OVER (
|
||
PARTITION BY fio_clean
|
||
ORDER BY time_val DESC, id DESC
|
||
) as rn
|
||
FROM scud_events_raw
|
||
WHERE log_date = ?
|
||
)
|
||
SELECT hoz_organ, fio, fio_clean, department, time_val, direction
|
||
FROM RankedEvents
|
||
WHERE rn = 1;
|
||
"""
|
||
cursor.execute(query, (clean_date,))
|
||
events_raw = [dict(r) for r in cursor.fetchall()]
|
||
|
||
last_events_map = {r['fio_clean']: r for r in events_raw}
|
||
|
||
# 3. Штат 1С и кадровые отклонения
|
||
df_staff, df_abs = load_1c_data_smart(clean_date, use_db=True)
|
||
if df_staff is None or df_staff.empty:
|
||
df_staff = get_latest_zup_staff()
|
||
|
||
absences_map = {}
|
||
if df_abs is not None and not df_abs.empty:
|
||
for _, row in df_abs.iterrows():
|
||
fc = row.get('fio_clean')
|
||
reason = str(row.get('Вид_отсутствия', '')).strip()
|
||
if fc and reason:
|
||
absences_map[fc] = reason
|
||
|
||
# 4. Исключения и реестр флигеля из базы данных
|
||
try:
|
||
exceptions_cfg = get_all_exceptions_from_db()
|
||
except Exception:
|
||
exceptions_cfg = {}
|
||
|
||
exc_fios = set([normalize_fio(f) for f in exceptions_cfg.get("fio", []) if f])
|
||
exc_depts = set([str(d).strip().lower() for d in exceptions_cfg.get("departments", []) if d])
|
||
exc_positions = set([str(p).strip().lower() for p in exceptions_cfg.get("positions", []) if p])
|
||
|
||
# Реестр флигеля / двора (ФИО и отделы)
|
||
fligel_fios = set([normalize_fio(f) for f in exceptions_cfg.get("fligel_fio", []) if f])
|
||
fligel_depts = set([str(d).strip().lower() for d in exceptions_cfg.get("fligel_departments", []) if d])
|
||
# Авто-добавление ключевых сотрудников и подразделений лаборатории БЛ
|
||
fligel_depts.update(["бл", "бетонная лаборатория", "испытательная геотехническая лаборатория"])
|
||
fligel_fios.update([normalize_fio("Чупряев Антон Михайлович"), normalize_fio("Пухаренко Ольга Юрьевна")])
|
||
|
||
# Ключевые слова технического персонала (клининг, контролеры КПП)
|
||
default_exc_keywords = ["уборщ", "дворник", "контролер", "внутреннего контроля", "клининг"]
|
||
|
||
def check_is_excluded(fio_c: str, dept: str, pos: str) -> bool:
|
||
if fio_c in exc_fios:
|
||
return True
|
||
d_lower = str(dept).strip().lower()
|
||
if d_lower in exc_depts or any(k in d_lower for k in ["контрол", "клининг"]):
|
||
return True
|
||
p_lower = str(pos).strip().lower()
|
||
if p_lower in exc_positions or any(k in p_lower for k in default_exc_keywords):
|
||
return True
|
||
return False
|
||
|
||
def check_is_fligel(fio_c: str, dept: str) -> bool:
|
||
if fio_c in fligel_fios:
|
||
return True
|
||
d_lower = str(dept).strip().lower()
|
||
return d_lower in fligel_depts or any(k in d_lower for k in ["бетонная лаб", "геотехническая лаб"])
|
||
|
||
# 5. Формирование полного списка сотрудников
|
||
staff_items = []
|
||
seen_fios = set()
|
||
|
||
# 5.1. Обработка всех сотрудников из официального штата 1С
|
||
if df_staff is not None and not df_staff.empty:
|
||
for _, s_row in df_staff.iterrows():
|
||
fio_raw = s_row.get('ФИО', '')
|
||
fio_c = s_row.get('fio_clean', normalize_fio(fio_raw))
|
||
if not fio_c or fio_c in seen_fios:
|
||
continue
|
||
|
||
seen_fios.add(fio_c)
|
||
dept_1c = str(s_row.get('Подразделение', '—')).strip()
|
||
pos = str(s_row.get('Должность', '—')).strip()
|
||
|
||
is_excluded = check_is_excluded(fio_c, dept_1c, pos)
|
||
is_fligel = check_is_fligel(fio_c, dept_1c)
|
||
|
||
absence_reason = absences_map.get(fio_c, "")
|
||
event = last_events_map.get(fio_c)
|
||
has_events_today = event is not None
|
||
last_time = event['time_val'].split()[-1][:5] if event and ' ' in event['time_val'] else (event['time_val'][:5] if event else '—')
|
||
direction = str(event.get('direction', '')).upper() if event else ""
|
||
|
||
dept_val = str(event['department']).strip() if (event and event.get('department') and str(event['department']).strip() not in ['—', 'Без подразделения', '']) else dept_1c
|
||
|
||
is_remote = "удален" in absence_reason.lower() or "дистанцион" in absence_reason.lower()
|
||
is_trip_or_leave = bool(absence_reason) and not is_remote
|
||
|
||
# ⭐️ ЛОГИКА ОПРЕДЕЛЕНИЯ СТАТУСА:
|
||
if is_excluded:
|
||
# Сотрудник входит в штат 1С, но имеет служебный статус исключения
|
||
status = "EXCLUDED"
|
||
status_label = "Исключение (Служебный)"
|
||
elif is_fligel and has_events_today:
|
||
# ⭐️ Сотрудник флигеля/двора: выход через турникет = внутридневной выход во двор на рабочее место
|
||
status = "INSIDE"
|
||
status_label = "В здании (Флигель/Двор)"
|
||
elif direction == "OUT":
|
||
status = "OUTSIDE"
|
||
status_label = "Вышел"
|
||
elif direction == "IN" or has_events_today:
|
||
status = "INSIDE"
|
||
status_label = "В здании"
|
||
elif is_remote:
|
||
status = "REMOTE"
|
||
status_label = "Удаленная работа"
|
||
elif is_trip_or_leave:
|
||
status = "OFFICIAL_ABSENCE"
|
||
status_label = absence_reason
|
||
else:
|
||
status = "NOT_ENTERED"
|
||
status_label = "Не пришел"
|
||
|
||
staff_items.append({
|
||
"fio": fio_raw,
|
||
"fio_clean": fio_c,
|
||
"department": dept_val,
|
||
"position": pos,
|
||
"status": status,
|
||
"status_label": status_label,
|
||
"last_time": last_time,
|
||
"last_direction": direction or "NONE",
|
||
"absence_reason": absence_reason,
|
||
"is_excluded": is_excluded,
|
||
"is_fligel": is_fligel
|
||
})
|
||
|
||
# Сортировка: В здании -> Вышли -> Удаленка -> Отсутствуют -> Не пришли -> Исключения
|
||
status_order = {
|
||
"INSIDE": 0,
|
||
"OUTSIDE": 1,
|
||
"REMOTE": 2,
|
||
"OFFICIAL_ABSENCE": 3,
|
||
"NOT_ENTERED": 4,
|
||
"EXCLUDED": 5
|
||
}
|
||
staff_items.sort(key=lambda x: (status_order.get(x["status"], 6), x["fio"].lower()))
|
||
|
||
# 6. Метрики
|
||
total_staff = len(staff_items)
|
||
inside_count = sum(1 for x in staff_items if x["status"] == "INSIDE")
|
||
outside_count = sum(1 for x in staff_items if x["status"] == "OUTSIDE")
|
||
remote_count = sum(1 for x in staff_items if x["status"] == "REMOTE")
|
||
absence_count = sum(1 for x in staff_items if x["status"] == "OFFICIAL_ABSENCE")
|
||
not_entered_count = sum(1 for x in staff_items if x["status"] == "NOT_ENTERED")
|
||
excluded_count = sum(1 for x in staff_items if x["status"] == "EXCLUDED")
|
||
|
||
latest_event_time = "—"
|
||
if events_raw:
|
||
times = [r['time_val'] for r in events_raw if r.get('time_val')]
|
||
if times:
|
||
max_t = max(times)
|
||
latest_event_time = max_t.split()[-1][:5] if ' ' in max_t else max_t[:5]
|
||
|
||
return {
|
||
"date": clean_date,
|
||
"data_source": data_source,
|
||
"latest_event_time": latest_event_time,
|
||
"timestamp": datetime.now().strftime("%H:%M:%S"),
|
||
"metrics": {
|
||
"total_staff": total_staff,
|
||
"inside": inside_count,
|
||
"outside": outside_count,
|
||
"remote": remote_count,
|
||
"official_absence": absence_count,
|
||
"not_entered": not_entered_count,
|
||
"excluded": excluded_count
|
||
},
|
||
"records": staff_items
|
||
} |