334 KiB
334 KiB
📦 КОМПАКТНЫЙ ETL-СЛЕПОК ИСХОДНОГО КОДА (СКУД ⟷ 1С & DB CORE)
File: ./config.py
import os
import re
from datetime import datetime, timedelta
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DATA_DIR = os.path.join(BASE_DIR, "data")
SCUD_DIR = os.path.join(DATA_DIR, "scud")
ZUP_1C_DIR = os.path.join(DATA_DIR, "1c")
OUTPUT_DIR = os.path.join(BASE_DIR, "output")
REPORTS_DIR = os.path.join(OUTPUT_DIR, "reports")
SHARE_1C_DIR = "/mnt/scud_share"
for folder in [DATA_DIR, SCUD_DIR, ZUP_1C_DIR, OUTPUT_DIR, REPORTS_DIR]:
os.makedirs(folder, exist_ok=True)
NOW = datetime.now()
DATE_TODAY = NOW.strftime("%d.%m.%Y")
if NOW.weekday() == 0:
DATE_YESTERDAY = (NOW - timedelta(days=3)).strftime("%d.%m.%Y")
else:
DATE_YESTERDAY = (NOW - timedelta(days=1)).strftime("%d.%m.%Y")
OLLAMA_URL = "http://10.121.17.227:11434/api/generate"
OLLAMA_MODEL = "qwen2.5:14b"
MODEL_NAME = OLLAMA_MODEL
KNOWLEDGE_BASE_PATH = os.path.join(DATA_DIR, "knowledge_base.json")
EXCEPTIONS_PATH = os.path.join(BASE_DIR, "exceptions.json")
ZUP_SQL_CONFIG = {
"driver": "{ODBC Driver 18 for SQL Server}",
"server": os.getenv("ZUP_SQL_SERVER", "ACCOUNT-01"),
"database": os.getenv("ZUP_SQL_DB", "ZUP30"),
"user": os.getenv("ZUP_SQL_USER", "scud_reader"),
"password": os.getenv("ZUP_SQL_PASS", "Rhfcysq90"),
"trust_server_certificate": "yes",
"encrypt": "no"
}
def find_dated_file(prefix, date_str, search_dirs=None):
if search_dirs is None:
search_dirs = [ZUP_1C_DIR, SCUD_DIR, DATA_DIR, "."]
date_dots = str(date_str).replace('_', '.')
date_underscores = date_dots.replace('.', '_')
for d in search_dirs:
if not os.path.exists(d):
continue
for f in os.listdir(d):
if f.endswith('.xlsx') or f.endswith('.csv'):
if f.lower().startswith(prefix.lower()):
if date_dots in f or date_underscores in f:
return os.path.join(d, f)
return None
def normalize_fio(fio):
if not fio or not isinstance(fio, str):
return ""
fio_clean = re.sub(r'\(.*?\)', '', fio)
fio_clean = fio_clean.replace('\xa0', ' ')
parts = fio_clean.strip().split()
return " ".join(parts).title()
def clean_scud_fio_light(fio_str):
return normalize_fio(fio_str)
def load_exceptions():
"""
Приоритетно читает исключения и белый список из SQLite таблицы exceptions_registry.
При отсутствии таблицы или пустой базе выполняет fallback на exceptions.json.
"""
try:
from services.exceptions_repo import get_all_exceptions_from_db
db_exc = get_all_exceptions_from_db()
if any(db_exc.values()):
return db_exc
except Exception:
pass
import json
if os.path.exists(EXCEPTIONS_PATH):
try:
with open(EXCEPTIONS_PATH, "r", encoding="utf-8") as f:
return json.load(f)
except Exception:
pass
return {"fio": [], "departments": [], "positions": [], "position_keywords": [], "include_fio": []}
File: ./core/connection.py
"""
===============================================================================
FILE: core/connection.py
PROJECT: SCUD Orion AI (Unified Architecture)
ROLE: Единый менеджер подключений к базе данных SQLite (WAL mode, timeouts).
===============================================================================
"""
import os
import sqlite3
from config import DATA_DIR
DB_PATH = os.path.join(DATA_DIR, "scud_orion_ai.db")
def get_connection(row_factory: bool = False) -> sqlite3.Connection:
"""
Создает оптимизированное подключение к SQLite.
row_factory=True возвращает sqlite3.Row для доступа к полям по имени.
"""
conn = sqlite3.connect(DB_PATH, timeout=30.0)
if row_factory:
conn.row_factory = sqlite3.Row
conn.execute("PRAGMA foreign_keys = ON;")
conn.execute("PRAGMA journal_mode = WAL;")
conn.execute("PRAGMA synchronous = NORMAL;")
return conn
File: ./core/database.py
"""
===============================================================================
FILE: core/database.py
ROLE: Фасад ядра базы данных с полной обратной совместимостью импортов.
===============================================================================
"""
import os
import pandas as pd
from config import OUTPUT_DIR
from core.connection import get_connection, DB_PATH
from core.schema import init_all_tables
from core.repositories.scud_repo import (
has_scud_logs_for_date,
has_yesterday_final_snapshot,
get_or_create_snapshot_id,
save_scud_to_db,
get_latest_snapshot_time,
load_scud_from_db_by_snapshot,
get_available_snapshots,
delete_snapshot_by_id,
delete_snapshots_by_date
)
from core.repositories.zup_repo import (
save_staff_to_db,
load_staff_from_db,
save_absences_to_db,
load_absences_from_db,
save_anomalies_to_db,
get_all_rules_from_db,
add_rule_to_db,
get_department_synonyms_dict,
add_department_synonym_to_db
)
init_db = init_all_tables
def dump_database_to_excel(out_filename: str = "db_dump_full.xlsx") -> str:
"""Создает полный дамп всех ключевых таблиц SQLite в многостраничный Excel."""
out_path = os.path.join(OUTPUT_DIR, out_filename)
tables = [
'scud_logs', 'scud_events_raw', 'zup_staff', 'zup_absences',
'anomalies_history', 'ai_knowledge_base', 'chat_messages',
'session_states', 'system_prompt_nodes', 'tasks',
'exceptions_registry', 'manual_absences', 'person_identity_mapping'
]
with get_connection() as conn, pd.ExcelWriter(out_path, engine='openpyxl') as writer:
for table in tables:
try:
df = pd.read_sql_query(f"SELECT * FROM {table}", conn)
df.to_excel(writer, sheet_name=table[:31], index=False)
except Exception:
pass
return out_path
File: ./core/repositories/scud_repo.py
"""
===============================================================================
FILE: core/repositories/scud_repo.py
ROLE: Репозиторий логов СКУД, сохранение и загрузка снапшотов.
===============================================================================
"""
from datetime import datetime
import pandas as pd
from core.connection import get_connection
def has_scud_logs_for_date(date_str: str) -> bool:
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("SELECT 1 FROM scud_logs WHERE log_date = ? LIMIT 1", (date_str,))
return cursor.fetchone() is not None
def has_yesterday_final_snapshot(date_str: str) -> bool:
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute(
"""
SELECT 1 FROM scud_logs
WHERE log_date = ?
AND (snapshot_id LIKE 'Y%' OR snapshot_id LIKE '%_FINAL%' OR snapshot_time LIKE '%23:59:59' OR snapshot_time LIKE '%22:00:00')
LIMIT 1
""",
(date_str,)
)
return cursor.fetchone() is not None
def get_or_create_snapshot_id(snapshot_time: str, date_str: str = None, is_yesterday: bool = False) -> str:
"""
Генерирует понятный и уникальный ID снапшота:
- Дата префикса берется строго из даты самих логов (date_str).
- Для итоговых срезов дня: YYYYYMMDD_FINAL (строго с буквой Y в начале).
- Для дневных срезов на время: YYYYMMDD_HHMM.
"""
if date_str:
try:
dt_log = datetime.strptime(date_str.replace('_', '.'), "%d.%m.%Y").date()
date_prefix = dt_log.strftime("%Y%m%d")
except Exception:
dt_log = datetime.now().date()
date_prefix = dt_log.strftime("%Y%m%d")
else:
try:
dt_snap = datetime.strptime(snapshot_time.split()[0], "%Y-%m-%d").date()
date_prefix = dt_snap.strftime("%Y%m%d")
except (ValueError, TypeError, IndexError):
date_prefix = datetime.now().strftime("%Y%m%d")
time_part = "2359"
try:
t_str = snapshot_time.split()[1] if " " in snapshot_time else snapshot_time
t_parts = t_str.split(":")
time_part = f"{t_parts[0]}{t_parts[1]}"
except Exception:
pass
with get_connection() as conn:
cursor = conn.cursor()
# Если срез с точно таким же временем и датой уже существует — возвращаем его ID
if date_str:
cursor.execute(
"SELECT snapshot_id FROM scud_logs WHERE log_date = ? AND snapshot_time = ? AND snapshot_id IS NOT NULL LIMIT 1",
(date_str, snapshot_time)
)
else:
cursor.execute(
"SELECT snapshot_id FROM scud_logs WHERE snapshot_time = ? AND snapshot_id IS NOT NULL LIMIT 1",
(snapshot_time,)
)
row = cursor.fetchone()
if row and row[0]:
return row[0]
# Финальный суточный ID: строго с префиксом Y
if is_yesterday or time_part in ["2359", "2200"]:
base_final_id = f"Y{date_prefix}_FINAL"
cursor.execute("SELECT 1 FROM scud_logs WHERE snapshot_id = ? LIMIT 1", (base_final_id,))
if not cursor.fetchone():
return base_final_id
return base_final_id
# Дневной срез на определенное время
base_id = f"{date_prefix}_{time_part}"
cursor.execute("SELECT 1 FROM scud_logs WHERE snapshot_id = ? LIMIT 1", (base_id,))
if not cursor.fetchone():
return base_id
cursor.execute("""
SELECT DISTINCT snapshot_id
FROM scud_logs
WHERE snapshot_id LIKE ?
""", (f"{base_id}-%",))
rows = cursor.fetchall()
max_seq = 1
for (s_id,) in rows:
if not s_id:
continue
try:
parts = str(s_id).split('-')
if len(parts) >= 2 and parts[-1].isdigit():
num = int(parts[-1])
if num > max_seq:
max_seq = num
except Exception:
continue
next_seq = max_seq + 1
return f"{base_id}-{next_seq:03d}"
def save_scud_to_db(df_scud: pd.DataFrame, date_str: str, snapshot_time: str = None, is_yesterday: bool = False) -> None:
if df_scud is None or df_scud.empty:
return
now_local_str = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
if not snapshot_time:
snapshot_time = now_local_str
snapshot_id = get_or_create_snapshot_id(snapshot_time, date_str=date_str, is_yesterday=is_yesterday)
data_to_insert = [
(
date_str,
r.get('Сотрудник', r.get('fio_raw', '')),
r.get('fio_clean', ''),
r.get('Подразделение', ''),
r.get('Должность', ''),
r.get('Начало_дня', 'Нет входа'),
r.get('Первая_активность', '—'),
r.get('Конец_дня', 'Нет выхода'),
r.get('Находился_в_здании', '00:00'),
1 if r.get('Пришел', False) else 0,
r.get('anomaly_flag', 'NONE'),
snapshot_time,
snapshot_id,
now_local_str # ⭐️ Передаем локальное время машины напрямую
)
for _, r in df_scud.iterrows()
]
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("DELETE FROM scud_logs WHERE log_date = ? AND snapshot_time = ?", (date_str, snapshot_time))
cursor.executemany("""
INSERT INTO scud_logs (
log_date, fio, fio_clean, department, position,
time_in, first_activity, time_out, time_in_building,
is_present, anomaly_flag, snapshot_time, snapshot_id, created_at
)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""", data_to_insert)
conn.commit()
def get_latest_snapshot_time(date_str: str = None):
with get_connection() as conn:
cursor = conn.cursor()
if date_str:
cursor.execute("SELECT snapshot_time FROM scud_logs WHERE log_date = ? AND snapshot_time IS NOT NULL ORDER BY snapshot_time DESC LIMIT 1", (date_str,))
else:
cursor.execute("SELECT snapshot_time FROM scud_logs WHERE snapshot_time IS NOT NULL ORDER BY snapshot_time DESC LIMIT 1")
row = cursor.fetchone()
return row[0] if row else None
def load_scud_from_db_by_snapshot(date_str: str, snapshot_param: str = None) -> pd.DataFrame:
with get_connection() as conn:
df = pd.DataFrame()
if snapshot_param:
df = pd.read_sql_query(
"SELECT * FROM scud_logs WHERE snapshot_id = ?",
conn, params=(str(snapshot_param),)
)
if df.empty and date_str:
cursor = conn.cursor()
cursor.execute(
"""
SELECT snapshot_id FROM scud_logs
WHERE log_date = ?
AND (snapshot_id LIKE 'Y%' OR snapshot_id LIKE '%_FINAL%')
ORDER BY snapshot_time DESC, id DESC LIMIT 1
""",
(date_str,)
)
row = cursor.fetchone()
if not row:
cursor.execute(
"SELECT snapshot_id FROM scud_logs WHERE log_date = ? ORDER BY snapshot_time DESC, id DESC LIMIT 1",
(date_str,)
)
row = cursor.fetchone()
if row and row[0]:
target_id = row[0]
df = pd.read_sql_query(
"SELECT * FROM scud_logs WHERE snapshot_id = ?",
conn, params=(target_id,)
)
if not df.empty:
rename_map = {
'department': 'department_scud',
'position': 'Должность',
'fio': 'Сотрудник',
'time_in': 'Начало_дня',
'first_activity': 'Первая_активность',
'time_out': 'Конец_дня',
'time_in_building': 'Находился_в_здании',
'is_present': 'Пришел'
}
df = df.rename(columns={k: v for k, v in rename_map.items() if k in df.columns})
if 'department_scud' in df.columns and 'Подразделение' not in df.columns:
df['Подразделение'] = df['department_scud']
for col in ['Пришел', 'Начало_дня', 'Первая_активность', 'Конец_дня', 'Находился_в_здании', 'anomaly_flag']:
if col not in df.columns:
df[col] = False if col == 'Пришел' else '—'
if 'Пришел' in df.columns:
df['Пришел'] = df['Пришел'].astype(bool)
return df
def get_available_snapshots(date_str: str = None):
with get_connection() as conn:
cursor = conn.cursor()
query = """
SELECT
snapshot_id,
log_date,
snapshot_time,
COUNT(*) as cnt,
MIN(created_at) as created_at
FROM scud_logs
WHERE snapshot_time IS NOT NULL
"""
params = []
if date_str:
query += " AND log_date = ?"
params.append(date_str)
query += " GROUP BY snapshot_id, log_date, snapshot_time ORDER BY snapshot_time DESC"
cursor.execute(query, params)
return cursor.fetchall()
def delete_snapshot_by_id(snapshot_id: str) -> int:
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("DELETE FROM scud_logs WHERE snapshot_id = ?", (snapshot_id,))
cnt = cursor.rowcount
conn.commit()
return cnt
def delete_snapshots_by_date(date_str: str) -> int:
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("DELETE FROM scud_logs WHERE log_date = ? OR snapshot_id LIKE ?", (date_str, f"%{date_str.replace('.', '')}%"))
cnt = cursor.rowcount
conn.commit()
return cnt
def save_raw_events_to_db(df_raw_events: pd.DataFrame, date_str: str) -> int:
if df_raw_events is None or df_raw_events.empty:
return 0
records = [
(
date_str,
str(r.get('TimeVal', '')),
int(r.get('HozOrgan', 0)),
str(r.get('Сотрудник', '')),
str(r.get('fio_clean', '')),
str(r.get('Подразделение', '')),
int(r.get('Event', 0)),
int(r.get('Mode', 0)),
int(r.get('DoorIndex')) if pd.notna(r.get('DoorIndex')) else None,
str(r.get('Direction', 'OTHER'))
)
for _, r in df_raw_events.iterrows()
]
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("DELETE FROM scud_events_raw WHERE log_date = ?", (date_str,))
cursor.executemany("""
INSERT INTO scud_events_raw (
log_date, time_val, hoz_organ, fio, fio_clean,
department, event_code, mode, door_index, direction
)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""", records)
conn.commit()
return len(records)
def get_building_presence(date_str: str, only_inside: bool = True) -> list[dict]:
with get_connection() as conn:
cursor = conn.cursor()
query = """
WITH RankedEvents AS (
SELECT
hoz_organ,
fio,
department,
time_val,
direction,
ROW_NUMBER() OVER (
PARTITION BY hoz_organ
ORDER BY time_val DESC, id DESC
) as rn
FROM scud_events_raw
WHERE log_date = ?
)
SELECT
hoz_organ,
fio,
department,
time_val,
direction
FROM RankedEvents
WHERE rn = 1
"""
if only_inside:
query += " AND direction = 'IN'"
query += " ORDER BY fio ASC;"
cursor.execute(query, (date_str,))
rows = cursor.fetchall()
results = [
{
"hoz_organ": r[0],
"fio": r[1],
"department": r[2],
"last_event_time": r[3],
"direction": r[4],
"status": "В здании" if r[4] == "IN" else "Вышел"
}
for r in rows
]
results.sort(key=lambda x: x["fio"].lower())
return results
File: ./core/repositories/zup_repo.py
"""
===============================================================================
FILE: core/repositories/zup_repo.py
ROLE: Репозиторий кадровых данных 1С:ЗУП, аномалий и базы знаний.
===============================================================================
"""
import pandas as pd
from typing import List, Dict, Any
from core.connection import get_connection
def save_staff_to_db(df_staff: pd.DataFrame, date_str: str) -> None:
if df_staff is None or df_staff.empty:
return
data = [
(date_str, r.get('ФИО', ''), r.get('fio_clean', ''), r.get('Подразделение', ''), r.get('Должность', ''))
for _, r in df_staff.iterrows()
]
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("DELETE FROM zup_staff WHERE snapshot_date = ?", (date_str,))
cursor.executemany("INSERT INTO zup_staff (snapshot_date, fio, fio_clean, department, position) VALUES (?, ?, ?, ?, ?)", data)
conn.commit()
def load_staff_from_db(date_str: str) -> pd.DataFrame:
with get_connection() as conn:
df = pd.read_sql_query(
"SELECT fio as 'ФИО', fio_clean, department as 'Подразделение', position as 'Должность' FROM zup_staff WHERE snapshot_date = ?",
conn, params=(date_str,)
)
return df if not df.empty else None
def save_absences_to_db(df_absent: pd.DataFrame, date_str: str) -> None:
if df_absent is None or df_absent.empty:
return
data = [
(date_str, r.get('ФИО', r.get('fio_clean', '')), r.get('fio_clean', ''), r.get('Вид_отсутствия', ''))
for _, r in df_absent.iterrows()
]
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("DELETE FROM zup_absences WHERE absence_date = ?", (date_str,))
cursor.executemany("INSERT INTO zup_absences (absence_date, fio, fio_clean, absence_type) VALUES (?, ?, ?, ?)", data)
conn.commit()
def load_absences_from_db(date_str: str) -> pd.DataFrame:
with get_connection() as conn:
df = pd.read_sql_query(
"SELECT fio as 'ФИО', fio_clean, absence_type as 'Вид_отсутствия' FROM zup_absences WHERE absence_date = ?",
conn, params=(date_str,)
)
return df if not df.empty else None
def save_anomalies_to_db(anomalies_list: List[Dict[str, Any]], date_str: str) -> None:
if not anomalies_list:
return
data = [(date_str, a.get('fio', ''), a.get('type', ''), a.get('details', '')) for a in anomalies_list]
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("DELETE FROM anomalies_history WHERE anomaly_date = ?", (date_str,))
cursor.executemany("INSERT INTO anomalies_history (anomaly_date, fio, anomaly_type, details) VALUES (?, ?, ?, ?)", data)
conn.commit()
def get_all_rules_from_db() -> List[str]:
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("SELECT rule_text FROM ai_knowledge_base")
return [r[0] for r in cursor.fetchall()]
def add_rule_to_db(rule_text: str, added_by: str = "Human") -> None:
if not rule_text or not rule_text.strip():
return
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("INSERT OR IGNORE INTO ai_knowledge_base (rule_text, added_by) VALUES (?, ?)", (rule_text.strip(), added_by))
conn.commit()
def get_department_synonyms_dict() -> Dict[str, str]:
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("SELECT LOWER(short_name), LOWER(full_name) FROM department_synonyms")
return {row[0]: row[1] for row in cursor.fetchall()}
def add_department_synonym_to_db(short_name: str, full_name: str) -> None:
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("INSERT OR REPLACE INTO department_synonyms (short_name, full_name) VALUES (?, ?)", (short_name.strip().lower(), full_name.strip().lower()))
conn.commit()
File: ./core/schema.py
"""
===============================================================================
FILE: core/schema.py
PROJECT: SCUD Orion AI (Unified Architecture)
ROLE: DDL-схемы таблиц, создание индексов и инициализация базы данных.
===============================================================================
"""
import logging
from core.connection import get_connection
logger = logging.getLogger("DB_SCHEMA")
def init_all_tables() -> None:
"""Инициализирует все таблицы и индексы системы."""
with get_connection() as conn:
cursor = conn.cursor()
# 1. Логи СКУД
cursor.execute("""
CREATE TABLE IF NOT EXISTS scud_logs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
log_date TEXT NOT NULL,
fio TEXT NOT NULL,
fio_clean TEXT NOT NULL,
department TEXT,
position TEXT,
time_in TEXT,
first_activity TEXT DEFAULT '—',
time_out TEXT,
time_in_building TEXT,
is_present INTEGER NOT NULL,
anomaly_flag TEXT DEFAULT 'NONE',
snapshot_time TEXT DEFAULT NULL,
snapshot_id TEXT DEFAULT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
# 1.1 Сырые физические события турникетов СКУД (для перекуров и оперативного статуса)
cursor.execute("""
CREATE TABLE IF NOT EXISTS scud_events_raw (
id INTEGER PRIMARY KEY AUTOINCREMENT,
log_date TEXT NOT NULL,
time_val TEXT NOT NULL,
hoz_organ INTEGER NOT NULL,
fio TEXT NOT NULL,
fio_clean TEXT NOT NULL,
department TEXT,
event_code INTEGER NOT NULL,
mode INTEGER NOT NULL,
door_index INTEGER DEFAULT 1,
direction TEXT NOT NULL, -- 'IN' или 'OUT'
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
# 2. Кадровые реестры 1С
cursor.execute("""
CREATE TABLE IF NOT EXISTS zup_staff (
id INTEGER PRIMARY KEY AUTOINCREMENT,
snapshot_date TEXT NOT NULL,
fio TEXT NOT NULL,
fio_clean TEXT NOT NULL,
department TEXT,
position TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS zup_absences (
id INTEGER PRIMARY KEY AUTOINCREMENT,
absence_date TEXT NOT NULL,
fio TEXT NOT NULL,
fio_clean TEXT NOT NULL,
absence_type TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
# 3. Аномалии, база знаний и синонимы
cursor.execute("""
CREATE TABLE IF NOT EXISTS anomalies_history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
anomaly_date TEXT NOT NULL,
fio TEXT NOT NULL,
anomaly_type TEXT NOT NULL,
details TEXT NOT NULL,
human_status TEXT DEFAULT 'Pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS ai_knowledge_base (
id INTEGER PRIMARY KEY AUTOINCREMENT,
rule_text TEXT UNIQUE NOT NULL,
added_by TEXT DEFAULT 'Human',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS department_synonyms (
id INTEGER PRIMARY KEY AUTOINCREMENT,
short_name TEXT UNIQUE NOT NULL,
full_name TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
# 4. Исключения и Кэш сопоставлений личностей (ИИ / Ручной)
cursor.execute("""
CREATE TABLE IF NOT EXISTS exceptions_registry (
id INTEGER PRIMARY KEY AUTOINCREMENT,
category TEXT NOT NULL,
value TEXT NOT NULL,
comment TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE(category, value)
);
""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS person_identity_mapping (
id INTEGER PRIMARY KEY AUTOINCREMENT,
scud_fio TEXT NOT NULL,
zup_fio TEXT NOT NULL,
scud_dept TEXT,
zup_dept TEXT,
match_source TEXT DEFAULT 'AI', -- 'AI', 'MANUAL', 'EXACT'
status TEXT DEFAULT 'ACTIVE', -- 'ACTIVE', 'PROPOSED', 'REJECTED'
confidence REAL DEFAULT 1.0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE(scud_fio, zup_fio)
);
""")
# 5. Узлы системного промпта, сессии, сообщения и задачи
cursor.execute("""
CREATE TABLE IF NOT EXISTS system_prompt_nodes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
prompt_name TEXT DEFAULT 'main_agent',
section_id INTEGER NOT NULL,
item_id INTEGER NOT NULL,
content TEXT NOT NULL,
is_active INTEGER DEFAULT 1,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE(prompt_name, section_id, item_id)
);
""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS session_states (
session_id TEXT PRIMARY KEY,
state_type TEXT NOT NULL,
pending_data TEXT,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS chat_messages (
id INTEGER PRIMARY KEY AUTOINCREMENT,
session_id TEXT NOT NULL,
role TEXT NOT NULL,
content TEXT NOT NULL,
is_ephemeral INTEGER DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS tasks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
task_id TEXT,
module TEXT DEFAULT 'general',
title TEXT NOT NULL,
priority TEXT DEFAULT 'MEDIUM',
status TEXT DEFAULT 'BACKLOG',
due_date TEXT,
user_id INTEGER DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
# 6. Индексы
cursor.execute("CREATE INDEX IF NOT EXISTS idx_scud_date ON scud_logs(log_date);")
cursor.execute("CREATE INDEX IF NOT EXISTS idx_scud_fio ON scud_logs(fio_clean);")
cursor.execute("CREATE INDEX IF NOT EXISTS idx_mapping_scud ON person_identity_mapping(scud_fio);")
cursor.execute("CREATE INDEX IF NOT EXISTS idx_raw_events_date_hoz ON scud_events_raw(log_date, hoz_organ);")
cursor.execute("CREATE INDEX IF NOT EXISTS idx_raw_events_date_time ON scud_events_raw(log_date, time_val);")
conn.commit()
File: ./docs/CHANGELOG.md
# Changelog
Все важные изменения проекта документируются в этом файле.
## [3.3.0] — 2026-09-24
### ✨ Добавлено (Added)
- **Генератор «Упрощенного отчета» (`services/reports/simplified_builder.py`):**
- Формирование суточного файла `Упрощенный отчет за ДД.ММ.ГГГГг..xlsx` с выгрузкой на сетевую шару параллельно с детальным отчетом.
- Сокращение полных ФИО до эталонного формата с инициалами (`Иванов И.И.`).
- Подстановка официальных текстовых заглушек СКУД `Нет входа (0:00)` и `Нет выхода (23:59)` при отсутствии отметок.
- Алфавитная сортировка по подразделениям и сотрудникам, форматирование сетки `Calibri 11` с числовым форматом времени `h:mm`.
- **Разделы сводки «Не приняты на работу» и «Нет пропуска»:**
- В `services/scud_etl/merger.py` и `services/reports/svodka_builder.py` выделена категория физлиц, присутствующих в СКУД, но еще не оформленных в 1С:ЗУП (`not_hired_yet`).
- Сотрудники штата без карт доступа теперь явно направляются в блок «Нет пропуска» (`no_scud_pass`).
### 🔧 Изменено (Changed)
- **Исключение не принятых на работу из табеля:**
- В `services/reports/otchet_builder.py` сотрудники без проведенного приказа о приеме в 1С исключены из таблицы детального суточного отчета за вчера.
- **Восстановление аббревиатур подразделений:**
- В `merger.py` восстановлен приоритет компактных названий отделов из базы СКУД Орион (`ОАН`, `ПУ`, `ОИЗ`, `ОА`, `ИГТЛЛ` и др.) взамен длинных кадровых формулировок 1С.
- **Инфраструктура нового сервера (Debian 12):**
- Исправлены пути виртуального окружения `/home/apushkov/projects/scud_ai/venv/bin/python` в cron-скриптах.
- Настроено автоматическое CIFS-монтирование шары отчетов в `/etc/fstab` с правами пользователя `apushkov`.
### 🛡️ Исправлено (Fixed)
- **Сбой генерации детального отчета (`TypeError: NAN/INF not supported`):**
- В `otchet_builder.py` включен параметр книги `nan_inf_to_errors: True` и внедрена санитизация пустых ячеек `NaN`/`None`, исключающая аварийное завершение `write_number()`.
- **Предупреждения Pandas в логах:**
- Устранены ошибки `Boolean Series key will be reindexed to match DataFrame index` в `merger.py` за счет изоляции расчета масок от `df_staff_only`.
## [2.5.8] - 2026-09-10
### Добавлено (Added)
- **Двухконтурная физическая модель СКУД (Правый PERCo):**
- Поддержка проходов через оба турникета (`DoorIndex IN (1, 2)`) для сотрудников дворовых служб (отделы `ЭТО`, `ЛЦ` и др.).
- Реестр доступа к правому турникету в `exceptions_registry` с категориями `turnstile_fio` и `turnstile_departments`.
- Корректная склейка ФИО (`Name + FirstName + MidName`) в динамическом SQL-фильтре MS SQL.
- **Интерактивное модальное окно исключений:**
- Замена браузерного `prompt()` на модальную форму в едином Tailwind-стиле с автоподбором сотрудников из 1С:ЗУП (`staff-autocomplete`).
- Поле примечания/комментария для фиксации оснований допуска.
- **Точное локальное время создания срезов:**
- Явная фиксация времени хоста при записи срезов в `scud_logs.created_at`, устраняющая 3-часовое смещение UTC внутри контейнеров.
- **Декларативное подтверждение удаления срезов:**
- Добавление параметра `confirmed: boolean` в схему `TOOLS_SCHEMA` инструмента `db_delete_snapshots`, исключающее зацикливание подтверждений в LLM.
### Изменено (Changed)
- **Упразднение искусственного автозакрытия смен:**
- Полное отключение механизма `calculate_autoclose_time` по согласованию с отделом кадров. При отсутствии физической отметки на турникете статус «Нет выхода» сохраняется без искажения аналитики.
- **Математический расчет временных границ срезов:**
- Перевод формирования `@EndDate` в `services/scud_export.py` на цепочку функций `DATEADD` от `@StartDate`, устраняющий зависимость от регионального формата даты (`DMY` vs `MDY`).
## [Unreleased] - 2026-09-07
### Добавлено
- Механизм Gemini-скроллинга диалогового окна: функция `scrollToUserMessageTop` в `core.js` автоматически поднимает отправленный вопрос к верхнему срезу экрана, скрывая громоздкие предыдущие ответы.
- CSS-отступ `padding-bottom: 80vh` у `#chat-messages-container`, гарантирующий свободное вертикальное пространство для прокрутки без сдвига строки ввода.
- Пункт 2.9 системного промпта, жестко обязывающий модель вызывать нативный инструмент `db_get_rules` при запросах Базы Знаний и регламентов вместо текстовой имитации.
### Запланировано
- Рефакторинг интерпретации аппаратных кодов турникетов PERCO в `services/scud_export.py`: разделение логики разрешения прохода (`Event 28`) и факта проворота створки (`Event 32`), приоритет направления по `log.Mode` (1 — вход, 2 — выход) для устранения фантомных выходов.
- Создание DDL-схемы и таблицы `scud_events_raw` в SQLite для гранулярного хранения всех внутридневных перемещений сотрудников (база для учета перекуров и инструмента «Кто в здании»).
## [0.9.5] - 2026-08-28
### Добавлено
- Механизм отказоустойчивого сохранения отчетов `safe_close_workbook` в `services/excel_exporter.py`: при блокировке файла открытым процессом Excel создается резервный файл с таймстемпом вида `..._timestamp.xlsx`.
### Изменено
- Экспорт всех Excel-документов (`services/scud_export.py`, `services/excel_exporter.py`) полностью переведен с `openpyxl` на `xlsxwriter`.
- Скорректирована верстка детального отчета: столбец «Подразделение» выровнен строго по центру, колонка «ФИО» расширена на 10% (ширина 33), а избыточная ширина столбцов «Первая активность» и «Отклонение от нормы» уменьшена до 11 пунктов для корректного размещения на экране без горизонтальной прокрутки.
- В модуле сетевой синхронизации `services/share_copier.py` вызов `shutil.copy2` заменен на `shutil.copyfile`, что устранило ошибки метаданных `[Errno 1] Operation not permitted` при монтировании SMB-шары.
### Исправлено
- Устранена системная ошибка структуры OpenXML (`errorXXXXXX_01.xml`, «Обнаружена ошибка в части содержимого книги...»), возникавшая при открытии сгенерированных отчетов в MS Excel.
- Восстановлены сворачиваемые интерактивные категории с кнопками `[+]` / `[-]` в файле ежедневной сводки (`outline_settings(symbols_below=False)`).
## [Unreleased] - 2026-08-28
### Fixed
- **Генерация Excel-книг (`services/excel_exporter.py`, `services/scud_export.py`):** полный перевод экспорта с `openpyxl` на `xlsxwriter`, что устранило хроническую ошибку структуры OpenXML («В книге обнаружено нечитаемое содержимое...»).
- **Группировки в Сводке:** восстановлено корректное отображение интерактивных кнопок `[+]`/`[-]` и сворачивание категорий по умолчанию (`outline_settings`, `level=1`, `collapsed=True`).
- **Верстка и пропорции Детального отчета:** центрирование столбца «Подразделение», калибровка ширины колонок «ФИО» (+10%), «Первая активность» и «Отклонение от нормы» для плотного размещения таблицы на одном экране без горизонтальной прокрутки.
- **Безопасное сохранение при блокировках:** внедрена функция `safe_close_workbook` для фонового сохранения резервных копий с timestamp, если файл открыт пользователем в Excel.
- **Сетевое копирование 1С (`services/share_copier.py`):** замена `shutil.copy2` на `shutil.copyfile` для устранения сбоя `[Errno 1] Operation not permitted` при работе с SMB/CIFS-шарой.
## [Unreleased] - 2026-08-28
### Fixed
- **Инкремент Snapshot ID (`core/repositories/scud_repo.py`):** заменена строковая сортировка `ORDER BY snapshot_id DESC` на целочисленный парсинг суффиксов, что устранило залипание номеров снапшотов (`-002`).
### Added
- **Режим тихих почасовых срезов (`--export-only`):** быстрый экспорт данных СКУД напрямую в SQLite без построения отчетов для интеграции в cron.
- **Интерактивный справочник CLI (`main_etl.py`):** полный баннер справки по всем аргументам (`-h`, `--help`, `help`).
- **Автоматическая ротация промежуточных срезов (`services/snapshots/retention.py`):** ночная очистка устаревших почасовых срезов старше 2 дней с сохранением полуденного (`13:00`) и итогового `Y` (`23:59:59`).
## [Unreleased] - 2026-08-28
### Added
- **Smart Snap-to-Grid Finder (`services/snapshots/finder.py`):** интеллектуальный поиск ближайшего среза СКУД в SQLite с допуском ±20 минут и поддержкой On-Demand экспорта из MS SQL при отсутствии готового среза.
- **Параметр `--time` в `main_etl.py`:** возможность формирования оперативной сводки на произвольное время суток.
- **Раздельные генераторы отчетов:**
- `services/scud_etl/svodka_generator.py` — оперативная сводка за сегодня с группировками.
- `services/scud_etl/otchet_generator.py` — суточный детальный отчет за вчера по финишному срезу `Y` (23:59:59) с учетом рабочего норматива 8.5 ч.
### Changed
- **Фиксация финишного среза `Y` на `23:59:59`:** обновлено время снапшота в `services/scud_export.py`, `core/repositories/scud_repo.py` и `services/scud_etl/pipeline.py` с сохранением обратной совместимости со срезами на `22:00:00`.
- **Путь виртуального окружения в `run_cron_etl.sh`:** скорректирован на актуальный `/home/puh/projects/scud_ai/venv/bin/python`.
- **Игнорирование временных файлов SQLite:** добавлены `*.db-shm` и `*.db-wal` в `.gitignore`.
## [3.2.0] — 2026-08-27
### 🧠 Интеллектуальный арбитраж личностей, агрегация мульти-пропусков и автозакрытие смен
#### ✨ Добавлено:
- **Кэш сопоставлений личностей (`person_identity_mapping`)**:
- Создана таблица в SQLite (`core/schema.py`) для постоянного хранения подтвержденных связок между сырыми ФИО СКУД и эталонными ФИО 1С:ЗУП.
- Поддержка мгновенного извлечения проверенных сопоставлений в `merger.py` (время выборки < 1 мс).
- **Агрегация дубликатов пропусков и мульти-учеток СКУД (`merger.py`)**:
- Функция `aggregate_scud_by_person` объединяет все карты/записи одного физлица: фиксирует самый ранний вход `min(valid_ins)`, самый поздний выход `max(valid_outs)` и итоговый статус присутствия.
- Исключено ложное попадание сотрудников с несколькими пропусками (например, Кондрашова Е.В.) в категорию «неизвестно».
- **Интеллектуальный выбор ставки совместителей 1С (`merger.py`)**:
- Функция `select_best_zup_position` сопоставляет множественные должности одного сотрудника в 1С со СКУД и подтягивает ставку того подразделения, где человек находится физически.
- **Автозакрытие незакрытых смен по Правилу 8.5ч (`excel_exporter.py`)**:
- При наличии утреннего входа и отсутствии вечернего выхода у офисных сотрудников в детальном отчете за вчера расчетный выход ставится как `Вход + 8ч 30мин`, норма 8 часов и нулевое отклонение `0:00` (вместо ложного штрафа `-8:00`).
- **Теневой ИИ-арбитраж нераспознанных ФИО (`text_reporter.py`)**:
- Модуль `find_ai_identity_suggestions` выявляет нечеткие совпадения и опечатки операторов СКУД, выводя рекомендации для кадровой службы в блоке «💡 Предложения ИИ по сопоставлению ФИО».
- **Расширение CLI-утилиты (`scripts/db_cli.py`)**:
- Добавлены команды управления связками: `python scripts/db_cli.py mapping [list|add|del]`.
#### 🔧 Изменено:
- **`services/scud_etl/anomaly_detector.py`**:
- Добавлено авто-детектирование задвоенных карточек СКУД (`DUPLICATE_SCUD_CARD`) с передачей информации в ИИ-сводку.
- **`main_etl.py`**:
- Восстановлена передача сырых датасетов (`raw_scud_df`, `raw_staff_df`, `raw_absent_df`) в ИИ-аудитор и генерация диагностического дампа `СКУД_Сырые_данные_ДД.ММ.ГГГГ.xlsx`.
---
## [3.1.0] — 2026-08-25
### 🛡️ Реляционный реестр исключений, Белый список и отказоустойчивость выгрузок
#### ✨ Добавлено:
- **Реляционный реестр исключений (`exceptions_registry`)**:
- Перенос исключений (`departments`, `positions`, `fio`, `position_keywords`) и белого списка (`include_fio`) из статического JSON в базу данных SQLite (`core/schema.py`).
- Репозиторий `services/exceptions_repo.py` с функциями CRUD и авто-миграцией из `exceptions.json`.
- **Белый список (`include_fio`)**:
- Поддержка явного включения сотрудников в расчет контроллинга в обход должностных и отдельских фильтров (Тарасенко А.А., Журиков М.Н.).
- **Управление исключениями в CLI (`scripts/db_cli.py`)**:
- Добавлены команды `exceptions list`, `exceptions add -c ... -v ...`, `exceptions del`, `exceptions sync`.
- **Резервный загрузчик кадровых отсутствий (`data_loader.py`)**:
- Реализован каскадный fallback: `MS SQL 1C:ЗУП` (таймаут 30с) $\rightarrow$ резервный парсинг скопированных Excel-файлов `Отсутствия_*.xlsx` $\rightarrow$ `static_reason_workers.csv`.
#### 🔧 Изменено:
- **Структура отчетов Excel (`excel_exporter.py`)**:
- Изменен базовый путь сохранения отчетов на `output/reports/ГОД/МЕСЯЦ/`.
- В Ежедневной сводке блок «В том числе на удаленной работе» вынесен под «Итого на работе», а блок «Исключения» размещен в самом низу с поддержкой группировки.
- Добавлена защита от блокировок Excel (`PermissionError`/`OSError`): при занятости файла создается резервная копия с временной меткой.
- **Подключение к СКУД (`services/scud_export.py`)**:
- Актуализирован адрес сервера MS SQL Орион (`172.16.200.147\SQL`).
- Добавлена защита инициализации файлового логгера от ошибок доступа прав пользователей.
---
## [3.0.0] — 2026-08-21
### 🚀 Глубокий архитектурный рефакторинг (Unified Clean Architecture)
#### Добавлено:
- **Пакет ядра `core/`:**
- `core/connection.py`: централизованный менеджер SQLite-подключений с поддержкой WAL-режима и row_factory.
- `core/schema.py`: единая точка инициализации схемы данных и индексов.
- `core/repositories/`: низкоуровневые репозитории `scud_repo.py` и `zup_repo.py`.
- **Доменные сервисы `services/`:**
- `services/tasks/`: полная изоляция логики задач (CRUD, Markdown-экспорт, генерация `ROADMAP.md`).
- `services/prompts/`: доменный сервис системного промпта, поддержка превью, генерация HTML-Diff и пакетное удаление узлов (`BATCH_DELETE`).
- `services/snapshots/`: бизнес-логика срезов СКУД с защитой финишных Y-снапшотов.
- `services/knowledge/`: сервис базы знаний правил и синонимов отделов.
- `services/scud_etl/`: модульный ETL-конвейер (`pipeline.py`, `anomaly_detector.py`, `merger.py`, `sql_queries.py`).
- **ИИ-оркестратор `modules/ai_engine/`:**
- Декомпозиция монолитного `agent.py` на узкие обработчики `handlers/` и динамический контекст-билдер `context_builder.py`.
- Двухконтурная обработка: нативный Function Calling + семантический страхующий инжектор (`tool_injector.py`).
- Интеллектуальный Topic Drift менеджер с вежливым напоминанием на 3-й шаг и автоочисткой эфемерных сообщений на 4-й.
- **Интерфейс и UI:**
- Кнопка «✏️ Редактировать» прямо в карточке просмотра системного промпта с инлайн-редактором.
#### Исправлено:
- Устранена утечка системного промпта в постоянный контекст (`chat_messages`).
- Исправлены ошибки несоответствия сигнатур (`TypeError: create_prompt_preview`, `save_full_prompt_draft`, `get_snapshots_registry`) в Fast-Path.
- Устранена галлюцинация слов-паразитов («Почемучка», «Почемучто») на уровне валидации ответа.
- Исправлено устаревшее противоречивое правило 2.3 в базе данных, блокировавшее интерактивное удаление задач.
#### Удалено:
- Устаревшие файлы-дубли: `modules/web_api/llm/db/db_tasks.py`, `db_snapshots.py`, `prompt_merger.py`.
---
## [2026-08-19] — Инлайн-редактор задач, интеллектуальный ContextManager и стабилизация Function Calling
### ✨ Добавлено
* **Инлайн-редактирование задач (`task_widget.js` / `tasks.py`)**:
- Добавлена кнопка редактирования (`✏️`) на каждой карточке задачи.
- Карточка трансформируется в инлайн-форму с тремя полями: текст задачи, HTML5-пикер календаря планируемого срока и выпадающий список важности (`LOW`, `MEDIUM`, `HIGH`, `CRITICAL`).
- Добавлен REST API эндпоинт `PATCH /api/v1/tasks/{task_id}` с поддержкой комплексного обновления полей в SQLite.
- В карточки выведено отображение даты создания задачи.
* **Интеллектуальный ContextManager (`context_manager.py`)**:
- Служебные запросы и карточки инструментов сохраняются синхронными парами (`is_ephemeral = 1`) и атомарно удаляются при завершении операций.
- Свободные диалоги оператора (включая темы Topic Drift) сохраняются в постоянную историю (`is_ephemeral = 0`) и не стираются при закрытии сессий промпта/задач.
* **Поддержка Y-снапшотов (`db_snapshots.py`)**:
- Исправлен SQL-запрос `db_get_snapshots` для точного извлечения ночных и фоновых срезов с префиксом `Y` (например, `Y20260817-003`).
### 🔧 Изменено
* **`modules/web_api/llm/agent.py`**:
- Полностью удалены эвристические текстовые фильтры и принудительные сортировки инструментов.
- Настроен нативный возврат и исполнение инструментов без зацикливания модели.
- Устранена ошибка `UnboundLocalError` при вызове справочных инструментов.
* **`modules/web_api/llm/schemas.py`**:
- Очищены описания инструментов от агрессивных директив и капса, стабилизирована селективность Function Calling для Qwen 2.5.
* **`modules/web_api/llm/core/ollama_client.py`**:
- Оптимизированы параметры генерации для Function Calling (`repeat_penalty: 1.0`, `presence_penalty: 0.0`, `temperature: 0.0`), что исключило спам несколькими вызовами инструментов на один запрос.
* **`modules/web_api/static/js/chat/task_widget.js`**:
- Фильтр по умолчанию переведен на вкладку **«В работе»** (`IN_PROGRESS`).
---
## [2026-08-18] — Двухфазный жизненный цикл сессий, двусторонний Diff и Topic Drift Guard
### ✨ Добавлено
* **Двусторонний клиентский Diff (`core.js`)**: Реализован алгоритм сравнения черновика с baseline-промптом. Удаленные строки остаются на экране с красным зачеркиванием `[УДАЛЕНИЕ]`, а добавленные/измененные строки выделяются красной рамкой с авто-отступами.
* **Topic Drift Guard (`agent.py`)**: Внедрен двухуровневый контроль смены темы:
- Мгновенный сброс стейта и зачистка эфемерных сообщений при вызове сторонних инструментов (задачи, снапшоты, база).
- Мягкий счетчик отвлечений (`idle_turns` = 3) в свободном диалоге с выводом напоминания и кнопок из `tool_action_registry`.
* **Команда `context purge` в CLI (`db_cli.py`)**: Добавлен флаг `--all` для полной очистки таблицы `chat_messages` и сброса стейтов сессий.
---
## [2026-08-17] — Внедрение CLI-инспекции контекста и переход на нативный Function Calling
### ✨ Добавлено
* **`scripts/db_cli.py`**: Добавлена команда `context` для инспекции истории сообщений `chat_messages` по сессиям с визуальным отображением флагов `[ЭФЕМЕРНОЕ]`.
* **`ROADMAP.md`**: Сформирована актуальная дорожная карта с планом отказа от регулярных выражений в пользу нативного Function Calling.
---
## [2026-08-15] — Реляционная архитектура системного промпта
### ✨ Добавлено
* **`system_prompt_nodes`**: Создана таблица реляционных узлов промпта с индексацией по разделам и пунктам (`section_id`, `item_id`).
* **`db_prompt_node_edit`**: Добавлен инструмент точечного добавления, изменения и удаления пунктов системного промпта.
* **Инлайн-редактор**: Добавлен визуальный diff-просмотр изменений с подсветкой и кнопкой `[✏️ Редактировать]`.
---
## [2026-08-10] — Механизм эфемерных сообщений и очистки диалога
### ✨ Добавлено
* **Эфемерные сообщения (`is_ephemeral`)**: Маркировка временных сервисных ответов и черновиков.
* **`db_purge_ephemeral_messages`**: Механизм физической зачистки временных записей из SQLite при завершении операций.
* **Интерактивные виджеты**: Поддержка карточек задач (`TASK_INTERACTIVE_CARD`) с кнопками быстрого действия.
File: ./docs/in_progress/ROADMAP.md
# План реализации (ROADMAP)
## 1. Очистка от регулярок и костылей (`tool_injector.py`) `[ЗАВЕРШЕНО]`
- [x] Полностью удалить принудительные перехваты текста регулярными выражениями для команд добавления, редактирования и удаления пунктов.
- [x] Оставить в модуле только базовую санитарную очистку сырых тегов (`<tool_call>`).
---
## 2. Настройка контекста и инструкций сессии (`agent.py`) `[ЗАВЕРШЕНО]`
- [x] Передать управление диалогом языковой модели через системный блок `role: "system"`.
- [x] При активном состоянии `PROMPT_PREVIEW` передавать модели инструкцию[cite: 2]:
- **Подтверждение / отмена / корректировка:** продолжать работу с превью и вызывать соответствующие инструменты[cite: 2].
- **Смена темы:** вежливо напомнить об открытом изменении и запросить решение[cite: 2].
- [x] Обеспечить видимость эфемерных сообщений (`is_ephemeral = 1`) для модели во время активной работы с превью[cite: 2].
- [x] Внедрить семантический Topic Drift Guard (`idle_turns` = 3) с вопросами и кнопками из `tool_action_registry`[cite: 2].
- [x] Реализовать детерминированный мгновенный сброс сессии и зачистку эфемерного контекста при вызове сторонних инструментов[cite: 2].
---
## 3. Очистка эфемерных сообщений при завершении (`fast_path.py` / `context_manager.py`) `[ЗАВЕРШЕНО]`
- [x] Настроить удаление временных сообщений превью (`db_purge_ephemeral_messages`) строго в момент нажатия кнопок **«Подтвердить»** или **«Отменить»**[cite: 2].
- [x] Сбрасывать состояние сессии в базе данных после фиксации решения[cite: 2].
- [x] Внедрить прямое точечное применение изменений через `db_apply_prompt_node_action`[cite: 2].
- [x] Добавить обработку фазы `PROMPT_FOLLOWUP` с кнопками завершения и очистки контекста[cite: 2].
- [x] Создать `context_manager.py` для строгого разграничения служебных tool-пар (`is_ephemeral=1`) и содержательного диалога (`is_ephemeral=0`), сохраняя беседы Topic Drift в истории[cite: 2].
---
## 4. Инлайн-редактор в окне диалога (`core.js`) `[ЗАВЕРШЕНО]`
- [x] Проверить работу блока ручного редактирования (`inline-prompt-editor-container`) с кнопками **«Сохранить правки»** и **«Свернуть»**[cite: 2].
- [x] Обеспечить сохранение черновика через API (`/api/v1/chat/draft`) и отображение обновленного текста перед подтверждением[cite: 2].
- [x] Реализовать двусторонний клиентский Diff-рендерер (одновременная подсветка добавленных строк и зачеркивание удаленных `[УДАЛЕНИЕ]`)[cite: 2].
- [x] Добавить авто-форматирование и отступы подпунктов (`X.Y.`) при ручном сохранении черновика[cite: 2].
---
## 5. Тестирование и валидация системного промпта `[ЗАВЕРШЕНО]`
- [x] **Нативные вызовы:** проверить добавление, редактирование и удаление пунктов через нативные вызовы модели (`db_prompt_node_edit`)[cite: 2].
- [x] **Контекстные сценарии:** проверить поведение модели при смене темы диалога оператором (Guardrail)[cite: 2].
- [x] **UI и очистка:** проверить ручное редактирование через кнопку в окне чата и последующую очистку контекста (`db_purge_ephemeral_messages`)[cite: 2].
- [x] **Строгий вызов Базы Знаний:** внедрено правило 2.9 в системный промпт для пресечения текстовой имитации вызова `db_get_rules`[cite: 2].
---
## 6. Распространение архитектурного паттерна на модуль задач (`tasks`) `[ЗАВЕРШЕНО]`
- [x] **Масштабирование UI задач:**
- Увеличена ширина карточки до `max-w-4xl` и динамическая высота скролла до `70vh`[cite: 2].
- Реализованы переключатели фильтрации: «В работе» (по умолчанию), «В планах», «Готово», «Все»[cite: 2].
- Разделены кнопки действия: «В работу» (для плановых) и «Готово» (для текущих)[cite: 2].
- [x] **Инлайн-редактирование карточки задачи:**
- Плавная трансформация карточки в 3-строчную форму (текст, дата со встроенным пикером, выпадающий список важности)[cite: 2].
- Отображение даты создания задачи[cite: 2].
- Сохранение через REST API эндпоинт `PATCH /api/v1/tasks/{id}`[cite: 2].
- [x] **Детерминированный Fast-Path и двухфазное удаление:**
- Мгновенная смена статусов без задержек LLM[cite: 2].
- Карточка подтверждения удаления с автоочисткой контекста[cite: 2].
- [x] **Генерация отчетов задач в Markdown:**
- Добавлен инструмент `db_export_tasks_markdown` для формирования Markdown-файла с группировкой по модулям и чекбоксами `[x]` / `[ ]`[cite: 2].
- Реализован роутер безопасной отдачи файлов с сохранением имени (`/api/v1/files/download/...`)[cite: 2].
- [x] **Доменная консолидация задач:**
- Реализован консолидированный инструмент `db_tasks_edit(action: ["ADD", "UPDATE", "DELETE", "EXPORT"], ...)`[cite: 2].
---
## 7. Распространение на модуль снапшотов (`snapshots`) `[ЗАВЕРШЕНО]`
- [x] **Поддержка Y-снапшотов:** универсальный парсинг и поиск по `snapshot_id LIKE 'Y%'` и срезам за 22:00:00 / 23:59:59[cite: 2].
- [x] **Single Active Snapshot View:** сохранение активного среза в контексте для аналитики с автоматической ротацией и зачисткой при запросе новых дат[cite: 2].
- [x] **Интерактивный UI с чекбоксами и защитой срезов:**
- Чекбоксы в строках дневных срезов и кнопка «Выбрать все» в шапке[cite: 2].
- Защита итогового вечернего среза Y (иконка замочка `🔒`, блокировка выбора)[cite: 2].
- Динамическая кнопка «Удалить выбранные (N)» в подвале карточки[cite: 2].
- [x] **Декларативное управление удалением срезов:**
- Добавлен параметр `confirmed` в схему `TOOLS_SCHEMA` для `db_delete_snapshots`, исключающий зацикливание подтверждений в LLM.
- Двухфазное подтверждение удаления (одиночное и пакетное) с корректным счётчиком количества удаляемых элементов[cite: 2].
- Корректное отображение времени создания срезов в интерфейсе (устранение смещения UTC относительно локального времени сервера).
---
## 8. Глубокий архитектурный рефакторинг ядра и сервисов `[ЗАВЕРШЕНО]`
- [x] **Выделение общего слоя ядра (`core/`):**
- Изолированный пул соединений SQLite (`core/connection.py`) с поддержкой WAL и таймаутов[cite: 2].
- DDL-схемы таблиц и индексов (`core/schema.py`)[cite: 2].
- Репозитории СКУД и 1С (`core/repositories/scud_repo.py`, `core/repositories/zup_repo.py`)[cite: 2].
- Фасад обратной совместимости (`core/database.py`)[cite: 2].
- [x] **Декомпозиция предметных доменов (`services/`):**
- Сервис задач (`services/tasks/`), системного промпта (`services/prompts/`), срезов СКУД (`services/snapshots/`), базы знаний (`services/knowledge/`)[cite: 2].
- [x] **Рефакторинг ETL-конвейера (`services/scud_etl/`):**
- Оркестратор `pipeline.py`, детектор аномалий `anomaly_detector.py`, слияние `merger.py`[cite: 2].
- [x] **Модульный ИИ-оркестратор (`modules/ai_engine/`):**
- Динамический строитель системного контекста (`context_builder.py`)[cite: 2].
- Изолированные обработчики инструментов в `handlers/`[cite: 2].
---
## 9. Реляционный реестр исключений, схлопывание мульти-пропусков и кадровый аудит `[ЗАВЕРШЕНО]`
- [x] **Реестр исключений в SQLite (`exceptions_registry`):**
- Хранение категорий `departments`, `positions`, `fio`, `position_keywords`, `include_fio` в БД[cite: 2].
- Доменный сервис `exceptions_repo.py` и полная поддержка в CLI `scripts/db_cli.py exceptions`[cite: 2].
- [x] **Агрегация мульти-пропусков физлиц в СКУД:**
- Функция `aggregate_scud_by_person` в `merger.py`: объединение событий всех пропусков одного человека (ранний вход, поздний выход, присутствие)[cite: 2].
- Автоматическая фиксация аномалий дублирования пропусков (`DUPLICATE_SCUD_CARD`)[cite: 2].
- [x] **Умный выбор ставки совместителей 1С:ЗУП:**
- Функция `select_best_zup_position` в `merger.py`: привязка ставки 1С по подразделению физического нахождения в СКУД[cite: 2].
- [x] **Отказ от искусственного автозакрытия смен:**
- Полное отключение механизма дорисовывания 8.5 часов (`calculate_autoclose_time`) по согласованию с отделом кадров.
- Сохранение честного статуса «Нет выхода» для прозрачности кадрового аудита и выявления нарушений.
- [x] **Кэш сопоставлений личностей (`person_identity_mapping`):**
- Таблица в SQLite и команды `db_cli.py mapping [list|add|del]`[cite: 2].
- Теневой режим ИИ-подсказок по нечетким ФИО в `text_reporter.py`[cite: 2].
- [x] **Каскадный fallback кадровых отсутствий:**
- `MS SQL ЗУП` $\rightarrow$ резервный парсинг `Отсутствия_*.xlsx` $\rightarrow$ `static_reason_workers.csv`[cite: 2].
---
## 10. Разделение генераторов и почасовые срезы `[ЗАВЕРШЕНО]`
- [x] Разделение логики генерации на `svodka_generator.py` и `otchet_generator.py`[cite: 2].
- [x] Перевод времени суточного среза `Y` на `23:59:59`[cite: 2].
- [x] Интеллектуальный поиск срезов `services/snapshots/finder.py` (Snap-to-Grid ±20 мин)[cite: 2].
- [x] Флаг `--time` и интерактивный help в `main_etl.py`[cite: 2].
- [x] Флаг `--export-only` для почасового крона[cite: 2].
- [x] Политика ночной ротации промежуточных срезов `services/snapshots/retention.py`[cite: 2].
- [x] Исправление целочисленного инкремента `snapshot_id`[cite: 2].
---
## 11. Стабилизация генератора отчетов и верстки `[ЗАВЕРШЕНО]`
- [x] **Миграция на `XlsxWriter`:** полный перевод экспорта книг (`scud_export.py`, `excel_exporter.py`) на чистый генератор, устранивший ошибки OpenXML и окна восстановления при открытии[cite: 2].
- [x] **Интективные группировки в Сводке:** восстановление сворачивания категорий по умолчанию (`level=1`, `collapsed=True`) и кнопок управления уровнями `[+]`/`[-]` (`outline_settings(symbols_below=False)`)[cite: 2].
- [x] **Компактная экранная верстка Детального отчета:** центрирование столбца «Подразделение», расширение столбца «ФИО» (+10%, ширина 33), оптимизация ширины колонок «Первая активность» (11) и «Отклонение от нормы» (11) для отображения без горизонтальной прокрутки[cite: 2].
- [x] **Защита от блокировок занятых файлов (Fallback Timestamp):** механизм перехвата `FileCreateError`/`OSError` в `safe_close_workbook` с сохранением копии при открытом в Excel файле[cite: 2].
- [x] **Безопасная синхронизация 1С с сетевой шары:** переход на `shutil.copyfile` в `share_copier.py` для устранения сбоев прав доступа (`Operation not permitted`) на SMB/CIFS-ресурсах[cite: 2].
---
## 12. UI/UX веб-интерфейса и управление реестрами `[ЗАВЕРШЕНО]`
- [x] **Паттерн Gemini-скролла:** реализация функции `scrollToUserMessageTop` в `core.js`, позиционирующей свежий вопрос пользователя строго по верхней кромке видимой области[cite: 2].
- [x] **Полное скрытие истории:** предыдущие объемные ответы модели гарантированно вытесняются за верхний край экрана[cite: 2].
- [x] **Стабилизация панели ввода:** настройка `padding-bottom: 80vh` для `#chat-messages-container` в `index.html`, обеспечивающая необходимый запас высоты прокрутки без сдвига строки ввода текста[cite: 2].
- [x] **Модальные формы реестров с автокомплитом 1С:**
- Полный отказ от браузерного `prompt()` при работе со списками исключений.
- Единое Tailwind-окно для добавления исключений с живым поиском сотрудников по базе 1С:ЗУП (`staff-autocomplete`) и полем для комментария/основания.
---
## 13. Архитектура обработки событий турникетов и двухконтурный учет `[ЗАВЕРШЕНО]`
- [x] **Опора на физический факт прохода (Event 32):**
- Устранение потери событий из-за рассинхронизации меток Event 28 (разрешение) и Event 32 (проход).
- Переход на плоский SQL-запрос выборки первого входа (`Mode = 1`) и последнего выхода (`Mode = 2`).
- [x] **Двухконтурная модель турникетов (Левый/Правый PERCo):**
- Разрешение использования обоих турникетов (`DoorIndex IN (1, 2)`) для сотрудников дворовых служб (отделы `ЭТО`, `ЛЦ` и др.).
- Защита основного пула офисных сотрудников от транзитных отметок на правом турникете (`DoorIndex = 1`).
- Реестр «Пр. турникет» (`turnstile_fio`, `turnstile_departments`) в базе данных и веб-панели.
- Корректное сопоставление составных ФИО (`Name + FirstName + MidName`) в запросе к `pList`.
- [x] **Таблица сырых событий (`scud_events_raw`):**
- Создание DDL-схемы таблицы и логирование всех физических проходов дня параллельно со снапшотами.
---
## 14. Миграция инфраструктуры, отказоустойчивость отчетов и Упрощенный отчет `[ЗАВЕРШЕНО]`
- [x] **Миграция на новый сервер (Debian 12):**
- Актуализация путей виртуального окружения в cron-скриптах (`scripts/cron/*.sh`).
- Восстановление прямого подключения к базе 1С:ЗУП (MS SQL Server `ACCOUNT-01`).
- Настройка автоматического CIFS-монтирования шары отчетов `//storage/SCUD/Отчеты` в `/etc/fstab` с правами пользователя.
- [x] **Разделение статусов наличия в кадровых реестрах (`merger.py`):**
- Выделение сотрудников, заведенных в СКУД, но не принятых в ЗУП (`not_hired_yet = True`), в отдельный раздел сводки **«Не приняты на работу»**.
- Полное исключение непринятых сотрудников из детального суточного табеля за прошлые смены.
- Обособление штатных сотрудников без карт СКУД (`no_scud_pass = True`) в раздел **«Нет пропуска»** с исключением их из «Неизвестных».
- Устранение предупреждений `UserWarning: Boolean Series key will be reindexed` при вычислении метрик штата.
- [x] **Стабилизация генерации Excel-книг (`otchet_builder.py`):**
- Включение защитной опции `nan_inf_to_errors: True` в `xlsxwriter.Workbook`.
- Санитизация ячеек от значений `NaN` / `None` / `pd.NA` перед записью, устранившая критический сбой `TypeError: NAN/INF not supported in write_number()`.
- [x] **Генератор «Упрощенного отчета» (`services/reports/simplified_builder.py`):**
- Создание генератора по эталонному шаблону: заголовок `Упрощенный отчет: c ДД.ММ.ГГГГ по ДД.ММ.ГГГГ`, сокращение ФИО до инициалов (`Фамилия И.О.`), отображение времени с текстовыми заглушками (`Нет входа (0:00)`, `Нет выхода (23:59)`).
- Сортировка по подразделениям и алфавиту сотрудников.
- Интеграция этапа сборки упрощенного отчета за вчера в ETL-конвейер (`main_etl.py`).
- [x] **Компактный динамический сборщик слепков (`make_etl_snapshot.py`):**
- Автоматический обход дерева (auto-discovery) с отсечением дублирующих архивных манифестов, временных файлов и пустых `__init__.py`.
---
## 15. ПРИОРИТЕТ 1: Внутридневной контроль, экран «Кто в здании» и On-Demand генерация `[В РАБОТЕ]`
- [ ] **Оперативный контроль «Кто в здании прямо сейчас» (Live Presence):**
- [ ] REST API эндпоинт `GET /api/v1/presence/live` на базе таблицы `scud_events_raw` и оконной функции последнего события прохода.
- [ ] Выделенная вкладка/панель в веб-интерфейсе:
- Метрики реального времени: *Всего по штату*, *В здании прямо сейчас*, *Вышли на перерыв/обед*, *На удаленке*, *В командировке/отпуске*, *Не пришли*.
- Быстрый поиск сотрудника и фильтрация по подразделениям.
- Детекция внутридневных аномалий (находится в здании без отметки утреннего входа, вход без выхода более 10 часов).
- [ ] **On-Demand генерация отчетов из UI и сайдбара:**
- [ ] Кнопка **«Сводка на текущий момент»** в сайдбаре: фоновый вызов `svodka_generator.py` со срезом на текущую минуту без перезагрузки страницы.
- [ ] Модальное окно **«Сформировать отчеты за дату»** с выбором даты из календаря и чекбоксами формирования документов:
- [ ] Детальный отчет (`otchet_generator.py`)
- [ ] Упрощенный отчет (`simplified_builder.py`)
- [ ] Сводка контроллинга (`svodka_generator.py`)
- [ ] **Атомарная публикация и отдача файлов (Report Delivery Engine):**
- [ ] Сборка документов во временный каталог `/tmp/scud_reports/` для исключения блокировок SMB-шары открытыми процессами Excel.
- [ ] Безопасная синхронизация готовых файлов на сетевую шару `//storage/SCUD/Отчеты/`.
- [ ] Автоматическая выдача прямой ссылки на скачивание файла (`/api/v1/files/download/...`) в интерфейс.
- [ ] **Реестр «Графики работы» (Custom Work Schedules):**
- [ ] Таблица в SQLite `work_schedules_registry` (`fio_clean`, `department`, `schedule_type`, `norm_hours`, `time_start_window`, `time_end_window`, `valid_from`, `valid_to`).
- [ ] Интеграция индивидуальной нормы часов (4, 7, 11, 24 ч) в калькулятор выработки `calculators.py` вместо константы 8 ч.
- [ ] Вкладка управления графиками в сайдбаре «Реестры» с автоподбором сотрудников из 1С.
---
## 16. ПРИОРИТЕТ 2: Кадровый арбитраж до отчетов и интеграция с MS Exchange `[В ПЛАНАХ]`
- [ ] **Почтовый агент MS Exchange (Email-Driven Controller & Human-in-the-Loop):**
- [ ] Фоновый сервис `services/mail/exchange_bot.py` для опроса служебного ящика по IMAP/SMTP (`imaplib` / `email`) или EWS (`exchangelib`).
- [ ] Белый список доверенных отправителей (адреса сотрудников ОК, СБ и руководства).
- [ ] **Генерация отчетов по письму:** распознавание запросов в теме/теле писем (*«Сводка на 14:00»*, *«Отчет за 22.09.2026»*) и отправка файла в ответном письме.
- [ ] **Разрешение кадровых коллизий через почту:** обработка ответов СБ на утреннюю рассылку аномалий (подтверждение связок в `person_identity_mapping`) и регистрация командировок/служебок в `manual_absences`.
- [ ] Двухфазный Guardrail: запрос подтверждения у кадровика по почте при низкой уверенности в сопоставлении тёзок (<90%).
- [ ] **Интеллектуальный кадровый арбитраж ДО генерации отчетов:**
- [ ] Перенос арбитража не сопоставившихся персон на этап [2.5] ДО сохранения Excel-файлов `сводка.xlsx` и `отчет.xlsx`.
- [ ] **Якорный табельный номер (TabNo Matching):** извлечение `TabNo` из MS SQL СКУД Орион и 1С:ЗУП, защита от смены фамилий и опечаток.
- [ ] 4-уровневая система предохранителей (Точный матч $\rightarrow$ Табельный номер $\rightarrow$ Кэш связок $\rightarrow$ ИИ-арбитраж).
---
## 17. ПРИОРИТЕТ 3: Векторная база знаний (RAG) и внешний контекст `[В ПЛАНАХ]`
- [ ] **Векторный поиск по регламентам компании (ChromaDB / RAG Engine):**
- [ ] Встраиваемая векторная база для семантического поиска по ПВТР, положениям о пропускном режиме и должностным инструкциям.
- [ ] Мгновенная выдача ссылок на пункты локальных нормативных актов при консультации оператора в чате.
- [ ] Семантическая классификация нестандартных формулировок причин отсутствий из писем и заявлений.
- [ ] **Внешний контекстный инструмент (Web Search Agent):**
- [ ] Нативный инструмент `web_search` для обращения к производственному календарю РФ (переносы праздников, предпраздничные сокращенные смены по ст. 95 ТК РФ).
- [ ] Справка по статьям Трудового кодекса РФ (сверхурочные, выходные дни).
---
## 18. Исследовательский трек: Изолированная песочница кода `[ОТЛОЖЕНО]`
- [ ] **Docker/gVisor контур:**
- [ ] Изолированный контейнер без доступа к внешней сети (`network: none`) с жесткими cgroups-лимитами и монтированием данных в режиме Read-Only.
- [ ] Генерация и выполнение Python/Pandas скриптов для построения сложных графиков и нестандартной статистики на лету.
File: ./exceptions.json
{
"departments": [
"ОВК"
],
"positions": [
"Уборщик производственных помещений",
"Уборщик служебных помещений"
],
"fio": [
"Таткало Валерий Валерьевич",
"Петренюк Андрей Германович",
"Михалев Сергей Геннадьевич"
],
"position_keywords": [
"уборщик",
"клинер",
"дворник",
"гардероб",
"рабочий по обслуживанию"
],
"include_fio": [
"Тарасенко Александр Александрович",
"Журиков Михаил Николаевич"
]
}
File: ./main_etl.py
"""
===============================================================================
FILE: main_etl.py
PROJECT: SCUD Orion AI (Unified Architecture)
ROLE: Главная точка входа ETL-конвейера СКУД ⟷ 1С:ЗУП.
===============================================================================
"""
import os
import sys
import argparse
import logging
import pandas as pd
from datetime import datetime, timedelta
from services.scud_etl.pipeline import load_best_snapshot_for_date, load_1c_files_for_date
from services.scud_etl.merger import merge_scud_and_1c, calculate_summary_metrics
from services.scud_etl.anomaly_detector import detect_registry_anomalies
from services.scud_etl.svodka_generator import generate_svodka_service
from services.scud_etl.otchet_generator import generate_otchet_service
from services.text_reporter import generate_markdown_report
from services.snapshots.retention import cleanup_old_intermediate_snapshots
from services.snapshots.retention import purge_day_intermediate_snapshots
from services.scud_export import run_export
from services.share_copier import copy_1c_files_from_share
from services.excel_exporter import export_raw_scud
logging.basicConfig(level=logging.INFO, format="[%(asctime)s] [%(levelname)s] %(message)s")
def print_help():
print("""
===============================================================================
🛠️ SCUD ORION AI — СИСТЕМА КОНТРОЛЛИНГА И СВОДНЫХ ОТЧЕТОВ
===============================================================================
Использование:
python main_etl.py [ОПЦИИ]
Доступные аргументы:
-h, --help, help Показать эту справку и выйти
--date ДД.ММ.ГГГГ Дата расчета (по умолчанию: текущий рабочий день)
--time ЧЧ:ММ Время среза для сводки (например: 14:30)
Ищет ближайший срез (±20 мин) или запрашивает On-Demand экспорт
--snapshot ID Точный ID снапшота для расчета (например: 20260827-002)
--skip-export Пропустить выгрузку СКУД из MS SQL (работать только с SQLite)
--export-only ТОЛЬКО сделать экспорт/снапшот СКУД в БД без построения отчетов
-d, --debug Включить режим расширенной отладки
Примеры использования:
python main_etl.py
👉 Полный суточный цикл: экспорт -> отчет за вчера -> сводка за сегодня -> ИИ.
python main_etl.py --export-only
👉 Почасовой тихий срез в БД (для cron) без генерации отчетов.
python main_etl.py --date 27.08.2026 --time 12:15 --skip-export
👉 Построить сводку за 27.08 на 12:15 без запроса к внешнему MS SQL.
python main_etl.py --snapshot 20260827-001 --skip-export
👉 Расчет отчетов строго по выбранному снапшоту из SQLite.
===============================================================================
""")
def main():
if len(sys.argv) > 1 and sys.argv[1] in ("-h", "--help", "help"):
print_help()
sys.exit(0)
parser = argparse.ArgumentParser(add_help=False)
parser.add_argument("-h", "--help", action="store_true")
parser.add_argument("-d", "--debug", action="store_true")
parser.add_argument("--skip-export", action="store_true")
parser.add_argument("--export-only", action="store_true")
parser.add_argument("--date", type=str, default=None)
parser.add_argument("--time", type=str, default=None)
parser.add_argument("--snapshot", type=str, default=None)
args = parser.parse_args()
if args.help:
print_help()
sys.exit(0)
print("=" * 60)
print(f"ЗАПУСК СИСТЕМЫ МОДУЛЬНОГО КОНТРОЛЛИНГА СКУД ⟷ 1С {'[DEBUG]' if args.debug else ''}")
print("=" * 60)
# Автоматическая ротация архивных почасовых срезов
if not args.skip_export and not args.snapshot:
deleted_count = cleanup_old_intermediate_snapshots(days_to_keep_all=2)
if deleted_count > 0:
print(f"[🧹] Ротация БД: очищено {deleted_count} строк промежуточных архивных срезов.")
now = datetime.now()
if args.date:
today_str = args.date.replace('_', '.')
dt_target = datetime.strptime(today_str, "%d.%m.%Y")
days_back = 3 if dt_target.weekday() == 0 else 1
yesterday_str = (dt_target - timedelta(days=days_back)).strftime("%d.%m.%Y")
else:
today_str = now.strftime("%d.%m.%Y")
if now.weekday() == 0:
yesterday_str = (now - timedelta(days=3)).strftime("%d.%m.%Y")
else:
yesterday_str = (now - timedelta(days=1)).strftime("%d.%m.%Y")
# [Этап 0] Выгрузка свежих данных СКУД
if not args.skip_export and not args.snapshot:
print(f"\n[0/5] Экспорт данных СКУД за {today_str} и {yesterday_str}...")
run_export(input_date=args.date, debug=args.debug, save_xlsx=True)
else:
print("\n[0/5] Пропуск прямого экспорта СКУД из MS SQL (--skip-export)...")
# Если запрошен режим тихого почасового среза — выходим без тяжелых генераций
if args.export_only:
print(f"\n[✓] Режим --export-only: срез зафиксирован в SQLite. Генерация отчетов пропущена.")
sys.exit(0)
# [Этап 0.5] Синхронизация файлов с шары 1С
if not args.snapshot and not args.skip_export:
print(f"\n[0.5/5] Проверка и копирование файлов 1С с шары...")
copy_1c_files_from_share()
else:
print("\n[0.5/5] Пропуск синхронизации с шары (чтение локальных данных)...")
# [Этап 1-2] Загрузка данных
print(f"\n[1-2/5] Загрузка срезов: Сегодня = {today_str}, Накануне = {yesterday_str}...")
df_scud_yesterday = load_best_snapshot_for_date(yesterday_str, prefer_final_y=True)
df_scud_today = load_best_snapshot_for_date(today_str, prefer_final_y=False)
df_staff_yesterday, df_abs_yesterday = load_1c_files_for_date(yesterday_str)
df_staff_today, df_abs_today = load_1c_files_for_date(today_str)
if df_scud_today is not None and not df_scud_today.empty:
export_raw_scud(df_scud_today, filename=f"СКУД_Сырые_данные_{today_str}.xlsx")
# [Этап 3] Детальный и Упрощенный отчеты за вчера
print(f"\n[3/5] Обработка и построение детального и упрощенного отчетов за ВЧЕРА ({yesterday_str})...")
res_otchet = generate_otchet_service(target_date=yesterday_str)
if res_otchet.get("status") == "success":
print(f"[✓] {res_otchet.get('message')}: {res_otchet.get('filepath')}")
# Генерация Упрощенного отчета за вчера
try:
from services.reports.simplified_builder import generate_simplified_excel
df_scud_y = load_best_snapshot_for_date(yesterday_str, prefer_final_y=True)
df_staff_y, df_abs_y = load_1c_files_for_date(yesterday_str)
df_merged_y = merge_scud_and_1c(df_scud_y, df_staff_y, df_abs_y)
simplified_path = generate_simplified_excel(df_merged_y, date_str=yesterday_str)
print(f"[✓] Упрощенный отчет за {yesterday_str} успешно сформирован: {simplified_path}")
except Exception as e:
print(f"[⚠️] Ошибка формирования упрощенного отчета: {e}")
# [Этап 4] Сводка за сегодня через svodka_generator
print(f"\n[4/5] Обработка и построение Ежедневной сводки за {today_str} {args.time or ''}...")
res_svodka = generate_svodka_service(
target_date=today_str,
target_time=args.time,
snapshot_id=args.snapshot
)
if res_svodka.get("status") == "success":
print(f"[✓] {res_svodka.get('message')}: {res_svodka.get('filepath')}")
if res_svodka.get("note"):
print(f" ℹ️ {res_svodka.get('note')}")
# Если запуск ночной (после 22:00) или закрывающий смену — зачищаем дневные срезы
if now.hour >= 22 and not args.skip_export:
purged = purge_day_intermediate_snapshots(today_str)
if purged > 0:
print(f"[🧹] Ночная очистка: удалено {purged} строк дневных срезов за {today_str}. Итоговый Y-срез сохранен.")
# [Этап 5] Формирование Markdown-сводки через ИИ-аудитора (Ollama)
print(f"\n[5/5] Формирование Markdown-сводки через ИИ-аудитора (Ollama)...")
df_merged_today = merge_scud_and_1c(df_scud_today, df_staff_today, df_abs_today)
anomalies_today = detect_registry_anomalies(df_merged_today, df_raw_scud=df_scud_today)
absent_explained = df_merged_today[
(df_merged_today['Пришел'] == False) &
(df_merged_today['Вид_отсутствия'].notna()) &
(~df_merged_today['Вид_отсутствия'].astype(str).str.startswith('Исключение'))
]
absent_unexplained = df_merged_today[
(df_merged_today['Пришел'] == False) &
(df_merged_today['Вид_отсутствия'].isna() | (df_merged_today['Вид_отсутствия'].astype(str).str.strip().isin(['', 'nan', 'None']))) &
(df_merged_today.get('is_excluded', False) == False) &
(df_merged_today.get('not_hired_yet', False) == False) &
(df_merged_today.get('no_scud_pass', False) == False)
]
summary_md = generate_markdown_report(
merged_df=df_merged_today[df_merged_today.get('is_excluded', False) == False],
absent_explained=absent_explained,
absent_unexplained=absent_unexplained,
scud_present_but_absent_in_1c=pd.DataFrame(),
anomalies_list=anomalies_today,
raw_scud_df=df_scud_today,
raw_staff_df=df_staff_today,
raw_absent_df=df_abs_today,
date_str=today_str
)
os.makedirs("output", exist_ok=True)
md_file_path = f"output/Сводка_контроллинга_{today_str}.md"
with open(md_file_path, "w", encoding="utf-8") as f:
f.write(summary_md)
print(f"[✓] Текстовый отчет сохранен в: {md_file_path}")
print("\n" + "=" * 60)
print("ГОТОВАЯ ТЕКСТОВАЯ СВОДКА ИИ-АУДИТОРА:")
print("=" * 60)
print(summary_md)
if __name__ == "__main__":
main()
File: ./scripts/add_rule_ai.py
import sys
import os
# Добавляем корень проекта в путь поиска модулей Python
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from core.database import get_connection
rule_text = (
"Сотрудники, присутствующие в 1С:ЗУП, но отсутствующие в СКУД Орион Pro, "
"являются аномалией синхронизации профилей. ИИ должен запрашивать у СБ статус выдачи пропуска."
)
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("INSERT OR IGNORE INTO ai_knowledge_base (rule_text) VALUES (?)", (rule_text,))
conn.commit()
print("✓ Правило успешно внесено в SQLite БД!")
File: ./scripts/cron/run_cron_etl.sh
#!/bin/bash
set -e
cd /home/apushkov/projects/scud_ai
mkdir -p /home/apushkov/projects/scud_ai/logs
echo "==================================================" >> /home/apushkov/projects/scud_ai/logs/cron_etl.log
echo "[CRON START] $(date '+%Y-%m-%d %H:%M:%S')" >> /home/apushkov/projects/scud_ai/logs/cron_etl.log
echo "==================================================" >> /home/apushkov/projects/scud_ai/logs/cron_etl.log
/home/apushkov/projects/scud_ai/venv/bin/python /home/apushkov/projects/scud_ai/main_etl.py >> /home/apushkov/projects/scud_ai/logs/cron_etl.log 2>&1
echo "[CRON FINISH] $(date '+%Y-%m-%d %H:%M:%S')" >> /home/apushkov/projects/scud_ai/logs/cron_etl.log
echo "" >> /home/apushkov/projects/scud_ai/logs/cron_etl.log
File: ./scripts/cron/run_hourly_snapshot.sh
#!/bin/bash
set -e
cd /home/apushkov/projects/scud_ai
mkdir -p /home/apushkov/projects/scud_ai/logs
echo "[CRON HOURLY START] $(date '+%Y-%m-%d %H:%M:%S')" >> /home/apushkov/projects/scud_ai/logs/cron_hourly.log
/home/apushkov/projects/scud_ai/venv/bin/python /home/apushkov/projects/scud_ai/services/scud_export.py >> /home/apushkov/projects/scud_ai/logs/cron_hourly.log 2>&1
echo "[CRON HOURLY FINISH] $(date '+%Y-%m-%d %H:%M:%S')" >> /home/apushkov/projects/scud_ai/logs/cron_hourly.log
File: ./scripts/cron/run_reports_only.sh
#!/bin/bash
set -e
cd /home/apushkov/projects/scud_ai
mkdir -p /home/apushkov/projects/scud_ai/logs
echo "[CRON REPORTS START] $(date '+%Y-%m-%d %H:%M:%S')" >> /home/apushkov/projects/scud_ai/logs/cron_reports.log
/home/apushkov/projects/scud_ai/venv/bin/python /home/apushkov/projects/scud_ai/main_etl.py --skip-export >> /home/apushkov/projects/scud_ai/logs/cron_reports.log 2>&1
echo "[CRON REPORTS FINISH] $(date '+%Y-%m-%d %H:%M:%S')" >> /home/apushkov/projects/scud_ai/logs/cron_reports.log
File: ./scripts/db_cli.py
import os
import sys
import argparse
import sqlite3
import pandas as pd
from datetime import datetime
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from modules.web_api.llm.db.db_chat import db_clear_all_chat_context
from core.database import dump_database_to_excel
from core.repositories.scud_repo import get_building_presence
from config import DATA_DIR, DATE_TODAY, OUTPUT_DIR, EXCEPTIONS_PATH, normalize_fio
from core.database import (
get_connection,
get_available_snapshots,
get_all_rules_from_db,
load_scud_from_db_by_snapshot,
get_latest_snapshot_time
)
from services.exceptions_repo import (
get_all_exceptions_from_db,
add_exception_to_db,
remove_exception_from_db,
sync_json_to_db
)
DB_PATH = os.path.join(DATA_DIR, "scud_orion_ai.db")
def cmd_mapping(args_list):
"""Управление подтвержденными сопоставлениями ФИО (СКУД <-> 1С:ЗУП)."""
with get_connection() as conn:
cursor = conn.cursor()
if not args_list or args_list[0] in ["list", "show"]:
cursor.execute("SELECT id, scud_fio, zup_fio, match_source, status FROM person_identity_mapping ORDER BY id DESC")
rows = cursor.fetchall()
print("\n🔗 СОХРАНЕННЫЕ СОПОСТАВЛЕНИЯ ФИО (person_identity_mapping):")
print("=" * 80)
if rows:
for r in rows:
print(f" #{r[0]} [{r[4]}] СКУД: '{r[1]}' ⟷ 1С: '{r[2]}' ({r[3]})")
else:
print(" — сопоставлений пока нет")
print("=" * 80 + "\n")
return
subcmd = args_list[0]
if subcmd == "add":
if len(args_list) < 3:
print("Использование: python scripts/db_cli.py mapping add 'ФИО в СКУД' 'ФИО в 1С'")
return
scud_f, zup_f = args_list[1], args_list[2]
cursor.execute("""
INSERT OR REPLACE INTO person_identity_mapping (scud_fio, zup_fio, match_source, status)
VALUES (?, ?, 'MANUAL', 'ACTIVE')
""", (scud_f.strip(), zup_f.strip()))
conn.commit()
print(f"✅ Успешно добавлена связка: '{scud_f}' ⟷ '{zup_f}'")
elif subcmd in ["del", "delete", "remove"]:
if len(args_list) < 2:
print("Использование: python scripts/db_cli.py mapping del 'ФИО в СКУД'")
return
cursor.execute("DELETE FROM person_identity_mapping WHERE scud_fio = ?", (args_list[1].strip(),))
conn.commit()
print(f"✅ Связка для '{args_list[1]}' удалена.")
def print_tool_actions():
"""Выводит реестр декларативных действий инструментов и шаблоны кнопок."""
print("\n" + "=" * 110)
print("🧰 ДЕКЛАРАТИВНЫЙ РЕЕСТР ДЕЙСТВИЙ ИНСТРУМЕНТОВ (tool_action_registry):")
print("=" * 110)
with get_connection() as conn:
cursor = conn.cursor()
try:
cursor.execute("""
SELECT id, tool_name, category, bypass_llm, success_template, follow_up_question, buttons_json
FROM tool_action_registry
WHERE is_active = 1
ORDER BY id ASC
""")
rows = cursor.fetchall()
if not rows:
print("Таблица tool_action_registry пуста.")
else:
for r in rows:
print(f"ID: {r[0]} | Tool: [{r[1]}] | Категория: {r[2]} | Bypass LLM: {'ДА (0.05с)' if r[3] else 'НЕТ'}")
print(f" • Сообщение: {r[4]}")
if r[5]:
print(f" • Вопрос: {r[5]}")
print(f" • Кнопки: {r[6]}")
print("-" * 110)
except Exception as e:
print(f"Таблица tool_action_registry недоступна: {e}")
print("=" * 110 + "\n")
def print_stats():
"""Выводит общую статистику по записям в таблицах БД."""
print("\n" + "=" * 60)
print("📊 СТАТИСТИКА БАЗЫ ДАННЫХ SQLITE (scud_orion_ai.db):")
print("=" * 60)
with get_connection() as conn:
cursor = conn.cursor()
tables = [
'scud_logs', 'scud_events_raw', 'zup_staff', 'zup_absences', 'anomalies_history',
'ai_knowledge_base', 'chat_messages', 'session_states',
'system_prompt_nodes', 'tasks', 'exceptions_registry'
]
for t in tables:
try:
cursor.execute(f"SELECT COUNT(*) FROM {t}")
cnt = cursor.fetchone()[0]
print(f" • Таблица [{t:<22}]: {cnt:>6} записей")
except Exception:
pass
print("=" * 60 + "\n")
def print_snapshots_list(date_str=None):
"""Выводит реестр снапшотов с отображением даты и точного времени среза."""
rows = get_available_snapshots(date_str)
print("\n" + "=" * 105)
print(f"📸 РЕЕСТР СОХРАНЕННЫХ СНАПШОТОВ (СВЕРХУ СВЕЖИЕ) {'ЗА ЛОГИ ' + date_str if date_str else ''}:")
print("=" * 105)
header = f"{'ID снапшота':<16} | {'Дата снапшота (создания)':<24} | {'Дата и время среза':<20} | {'Записей':<8}"
print(header)
print("-" * 105)
if not rows:
print("Снапшотов пока нет.")
print("=" * 105 + "\n")
return
def snapshot_sort_key(row):
snap_id = row[0] or ""
snap_time = row[2] or ""
seq_num = 0
if "-" in snap_id:
parts = snap_id.replace("Y", "").split("-")
if len(parts) > 1 and parts[1].isdigit():
seq_num = int(parts[1])
return (snap_time, seq_num)
sorted_rows = sorted(rows, key=snapshot_sort_key, reverse=True)
for r in sorted_rows:
snap_id = r[0] if r[0] else '----------'
log_date = r[1] if r[1] else '—'
snap_time = r[2] if r[2] else '—'
count = r[3]
time_part = "—"
if snap_time and " " in snap_time:
time_part = snap_time.split(" ")[1]
slice_datetime_str = f"{log_date} {time_part}" if time_part != "—" else log_date
formatted_snap_id = f" {snap_id}" if not snap_id.startswith("Y") else snap_id
print(f"{formatted_snap_id:<16} | {snap_time:<24} | {slice_datetime_str:<20} | {count:<8}")
print("=" * 105 + "\n")
def delete_snapshot_by_id(snapshot_id: str):
"""Удаляет конкретный снапшот из таблицы scud_logs по его ID."""
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("DELETE FROM scud_logs WHERE snapshot_id = ?", (snapshot_id,))
deleted_count = cursor.rowcount
conn.commit()
print(f"\n[✓] Успешно удален снапшот [{snapshot_id}]. Удалено строк: {deleted_count}\n")
return deleted_count
def delete_snapshots_by_date(date_str: str):
"""Удаляет все снапшоты за указанную дату (например, '04.08.2026')."""
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("DELETE FROM scud_logs WHERE log_date = ? OR snapshot_id LIKE ?", (date_str, f"%{date_str.replace('.', '')}%"))
deleted_count = cursor.rowcount
conn.commit()
print(f"\n[✓] Успешно удалены все снапшоты за дату [{date_str}]. Удалено строк: {deleted_count}\n")
return deleted_count
def inspect_scud(snapshot_id=None, date_str=None, export_xlsx=None):
"""Инспектирует логи СКУД за выбранный снапшот или дату и опционально сохраняет XLSX."""
target_date = date_str if date_str else DATE_TODAY
print("\n" + "=" * 115)
if snapshot_id:
print(f"🔍 ИНСПЕКЦИЯ СКУД ПО СНАПШОТУ [{snapshot_id}] (Дата среза: {target_date}):")
else:
print(f"🔍 ИНСПЕКЦИЯ СКУД ЗА ТЕКУЩУЮ ДАТУ [{target_date}] (ПОСЛЕДНИЙ СРЕЗ):")
print("=" * 115)
df = load_scud_from_db_by_snapshot(target_date, snapshot_param=snapshot_id)
if df is None or df.empty:
print("Записи СКУД не найдены.")
print("=" * 115 + "\n")
return
fio_col = next((c for c in ['Сотрудник', 'fio', 'fio_clean'] if c in df.columns), None)
dept_col = next((c for c in ['Подразделение', 'department_scud', 'department'] if c in df.columns), None)
pos_col = next((c for c in ['Должность', 'position'] if c in df.columns), None)
in_col = next((c for c in ['Начало_дня', 'time_in'] if c in df.columns), None)
first_act_col = next((c for c in ['Первая_активность', 'first_activity'] if c in df.columns), None)
out_col = next((c for c in ['Конец_дня', 'time_out'] if c in df.columns), None)
dur_col = next((c for c in ['Находился_в_здании', 'time_in_building', 'duration'] if c in df.columns), None)
present_col = next((c for c in ['Пришел', 'is_present'] if c in df.columns), None)
anom_col = 'anomaly_flag' if 'anomaly_flag' in df.columns else None
snap_col = 'snapshot_id' if 'snapshot_id' in df.columns else None
total = len(df)
if present_col:
present_cnt = len(df[df[present_col].astype(str).str.lower().isin(['true', '1'])])
else:
present_cnt = 0
absent_cnt = total - present_cnt
print(f"Всего записей: {total} | Присутствовали: {present_cnt} | Отсутствовали: {absent_cnt}")
print("-" * 115)
display_cols = [c for c in [fio_col, dept_col, in_col, first_act_col, out_col, dur_col, present_col, anom_col, snap_col] if c]
print(df[display_cols].head(30).to_string(index=False))
if len(df) > 30:
print(f"\n... и ещё {len(df) - 30} строк.")
if export_xlsx:
out_path = export_xlsx if export_xlsx.endswith('.xlsx') else f"{export_xlsx}.xlsx"
if not os.path.isabs(out_path):
out_path = os.path.join(OUTPUT_DIR, out_path)
df.to_excel(out_path, index=False)
print("\n" + "*" * 115)
print(f"[✓] УСПЕШНЫЙ ЭКСПОРТ ДЕБАГ-ФАЙЛА В EXCEL: {out_path}")
print("*" * 115)
print("=" * 115 + "\n")
def print_absences(date_str=None):
"""Выводит список официально отсутствующих сотрудников из 1С:ЗУП за выбранный день."""
target_date = date_str if date_str else DATE_TODAY
print("\n" + "=" * 90)
print(f"📋 ОФИЦИАЛЬНЫЕ ОТСУТСТВИЯ ИЗ 1С:ЗУП ЗА ДАТУ [{target_date}]:")
print("=" * 90)
with get_connection() as conn:
df = pd.read_sql_query(
"SELECT fio as 'ФИО', absence_type as 'Причина отсутствия 1С' FROM zup_absences WHERE absence_date = ? ORDER BY absence_type, fio",
conn,
params=(target_date,)
)
if df.empty:
print(f"Записи об отсутствиях 1С за {target_date} в базе не найдены.")
else:
print(f"Всего зафиксировано документов 1С: {len(df)}")
print("-" * 90)
print(df.to_string(index=False))
print("=" * 90 + "\n")
def print_anomalies():
"""Выводит список аномалий СКУД из БД."""
print("\n" + "=" * 80)
print("🚨 ИСТОРИЯ НАЙДЕННЫХ АНОМАЛИЙ СКУД ⟷ 1С:")
print("=" * 80)
with get_connection() as conn:
df = pd.read_sql_query("SELECT anomaly_date, fio, anomaly_type, details FROM anomalies_history ORDER BY id DESC LIMIT 50", conn)
if df.empty:
print("Аномалии не найдены.")
else:
print(df.to_string(index=False))
print("=" * 80 + "\n")
def print_rules():
"""Выводит правила базы знаний ИИ."""
rules = get_all_rules_from_db()
print("\n" + "=" * 80)
print("🧠 ПРАВИЛА БАЗЫ ЗНАНИЙ ИИ:")
print("=" * 80)
if not rules:
print("База знаний пуста.")
else:
for idx, r in enumerate(rules, 1):
print(f" {idx}. {r}")
print("=" * 80 + "\n")
def dump_all_to_excel(out_filename="db_dump_full.xlsx"):
"""Дампит всю базу SQLite во многостраничный Excel."""
print(f"\n[🔄] Создание полного дампа БД в файл: {out_filename} ...")
out_path = dump_database_to_excel(out_filename)
print(f"[✓] Дамп успешно сохранен: {out_path}\n")
def print_system_prompts():
"""Выводит все системные промпты из базы данных."""
print("\n" + "=" * 80)
print("📝 СИСТЕМНЫЕ ПРОМПТЫ (system_prompt_nodes):")
print("=" * 80)
with get_connection() as conn:
cursor = conn.cursor()
try:
cursor.execute("SELECT section_id, item_id, content, is_active, updated_at FROM system_prompt_nodes ORDER BY section_id ASC, item_id ASC")
rows = cursor.fetchall()
except Exception:
rows = []
if not rows:
print("Таблица system_prompt_nodes пуста.")
else:
for r in rows:
print(f"Раздел {r[0]}.{r[1]} | Active: {r[3]} | Updated: {r[4]}")
print(f" {r[2]}")
print("-" * 80)
print("=" * 80 + "\n")
def print_session_states():
"""Выводит текущие активные сессии и превью (session_states)."""
print("\n" + "=" * 80)
print("🔄 АКТИВНЫЕ СЕССИИ И ПРЕВЬЮ (session_states):")
print("=" * 80)
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("SELECT session_id, state_type, updated_at, pending_data FROM session_states")
rows = cursor.fetchall()
if not rows:
print("Таблица session_states пуста (нет активных превью).")
else:
for r in rows:
print(f"Session: {r[0]} | Type: {r[1]} | Updated: {r[2]}")
print("-" * 80)
print(f"Pending Data:\n{r[3]}\n")
print("=" * 80 + "\n")
def print_chat_messages(session_id=None, limit=50):
"""Выводит таблицу истории сообщений чата (контекст)."""
print("\n" + "=" * 105)
print(f"💬 ИСТОРИЯ СООБЩЕНИЙ ЧАТА (chat_messages) {f'для сессии: {session_id}' if session_id else 'все сессии'}:")
print("=" * 105)
with get_connection() as conn:
cursor = conn.cursor()
if session_id:
cursor.execute("""
SELECT id, session_id, role, content, is_ephemeral, created_at
FROM chat_messages
WHERE session_id = ?
ORDER BY id ASC
LIMIT ?
""", (session_id, limit))
else:
cursor.execute("""
SELECT id, session_id, role, content, is_ephemeral, created_at
FROM chat_messages
ORDER BY id DESC
LIMIT ?
""", (limit,))
rows = cursor.fetchall()
if not rows:
print("Таблица chat_messages пуста.")
else:
if not session_id:
rows = list(reversed(rows))
for r in rows:
msg_id, sess, role, content, ephemeral, created = r
eph_marker = " [ЭФЕМЕРНОЕ]" if ephemeral else ""
print(f"[{msg_id}] {created} | Сессия: {sess} | Роль: {role.upper()}{eph_marker}")
print("-" * 105)
content_preview = content if content else ""
print(f"{content_preview}")
print("=" * 105)
print("\n")
def purge_chat_context(session_id=None, purge_all=False):
"""Очистка контекста сообщений через репозиторий чата."""
deleted_msgs = db_clear_all_chat_context(session_id=session_id, purge_all=purge_all)
mode = "Полная" if purge_all else "Умная"
print(f"\n[✓] {mode} зачистка контекста выполнена! Удалено сообщений: {deleted_msgs}\n")
def print_exceptions():
"""Выводит реестр исключений и белый список сотрудников из базы SQLite."""
exc = get_all_exceptions_from_db()
print("\n" + "=" * 80)
print("📋 РЕЕСТР ИСКЛЮЧЕНИЙ И БЕЛЫЙ СПИСОК (exceptions_registry):")
print("=" * 80)
for cat, items in exc.items():
print(f"[{cat.upper()}] ({len(items)} шт.):")
if items:
for it in items:
print(f" • {it}")
else:
print(" — пусто")
print("-" * 80)
print("=" * 80 + "\n")
def print_building_presence(date_str: str, all_statuses: bool = False):
"""Выводит оперативный список сотрудников, находящихся в здании."""
records = get_building_presence(date_str, only_inside=not all_statuses)
title = f"КТО СЕЙЧАС В ЗДАНИИ [{date_str}]" if not all_statuses else f"ОПЕРАТИВНЫЙ СТАТУС СОТРУДНИКОВ [{date_str}]"
print("\n" + "=" * 80)
print(f"🏢 {title} (Всего: {len(records)})")
print("=" * 80)
if not records:
print(" Нет данных о проходах за указанную дату.")
else:
print(f"{'ФИО':<35} | {'Подразделение':<15} | {'Время':<10} | {'Статус':<8}")
print("-" * 80)
for r in records:
time_short = r['last_event_time'].split()[-1][:8] if ' ' in r['last_event_time'] else r['last_event_time'][:8]
print(f"{r['fio']:<35} | {r['department'][:15]:<15} | {time_short:<10} | {r['status']:<8}")
print("=" * 80 + "\n")
HELP_TEXT = """
CLI-утилита инспекции и управления SQLite базой данных СКУД (scud_orion_ai.db)
ДОСТУПНЫЕ КОМАНДЫ:
stats -- Общая статистика строк по всем таблицам БД
snapshots [ДД.ММ.ГГГГ] -- Посмотреть реестр снапшотов (опционально за конкретную дату)
scud [ДД.ММ.ГГГГ] [--snapshot ID] [--export-xlsx NAME] -- Инспекция логов СКУД по дате/снапшоту и экспорт в Excel
in_building [ДД.ММ.ГГГГ] [--all] -- Оперативный статус: кто сейчас в здании (или все статусы с флагом --all)
absences [ДД.ММ.ГГГГ] -- Посмотреть список официально отсутствующих из 1С:ЗУП
anomalies -- Посмотреть историю найденных аномалий СКУД ⟷ 1С
rules -- Посмотреть правила Базы Знаний ИИ из SQLite
prompts -- Посмотреть узлы системного промпта (system_prompt_nodes)
tools -- Посмотреть реестр действий инструментов и кнопок (tool_action_registry)
sessions -- Посмотреть активные сессии и превью (session_states)
context [session_id] [--limit N] -- Посмотреть таблицу контекста сообщений чата (chat_messages)
context purge [session_id] [--all] -- Очистить контекст (умная зачистка или полная с флагом --all)
dump [output.xlsx] -- Полный дамп всех таблиц БД в многостраничный Excel
snapshot del [ID] или [--day ДД.ММ.ГГГГ] -- Удаление снапшота по ID или всех за выбранный день
exceptions [list] -- Посмотреть реестр исключений и белый список (SQLite)
exceptions add -c CATEGORY -v VALUE [-m COMMENT] -- Добавить исключение (fio, include_fio, departments, positions, position_keywords)
exceptions del -c CATEGORY -v VALUE -- Удалить исключение из БД
exceptions sync -- Синхронизировать exceptions.json -> SQLite
ПРИМЕРЫ ЗАПУСКА:
python scripts/db_cli.py stats
python scripts/db_cli.py snapshots 06.08.2026
python scripts/db_cli.py scud 06.08.2026 --export-xlsx срез_четверг
python scripts/db_cli.py in_building 07.09.2026
python scripts/db_cli.py in_building 07.09.2026 --all
python scripts/db_cli.py absences 07.08.2026
python scripts/db_cli.py prompts
python scripts/db_cli.py tools
python scripts/db_cli.py sessions
python scripts/db_cli.py context web_session_main --limit 20
python scripts/db_cli.py context purge
python scripts/db_cli.py context purge --all
python scripts/db_cli.py context purge web_session_main --all
python scripts/db_cli.py snapshot del Y20260805-007
python scripts/db_cli.py dump my_dump.xlsx
python scripts/db_cli.py exceptions
python scripts/db_cli.py exceptions add -c include_fio -v "Тарасенко Александр Александрович"
python scripts/db_cli.py exceptions add -c fio -v "Михалев Сергей Геннадьевич" -m "Уборщик"
python scripts/db_cli.py exceptions del -c fio -v "Михалев Сергей Геннадьевич"
python scripts/db_cli.py exceptions sync
"""
def main():
if any(arg in sys.argv for arg in ['-h', '--help']):
print(HELP_TEXT)
sys.exit(0)
parser = argparse.ArgumentParser(
description=HELP_TEXT,
formatter_class=argparse.RawDescriptionHelpFormatter,
add_help=False
)
parser.add_argument('command', nargs='?', default=None, choices=[
'stats', 'snapshots', 'scud', 'absences', 'anomalies',
'rules', 'prompts', 'sessions', 'dump', 'snapshot',
'tools', 'context', 'in_building', 'exceptions'
], help="Основная команда")
parser.add_argument('action', nargs='?', default=None, help="Действие ('del', 'purge', 'add', 'sync') или дата/сессия")
parser.add_argument('param', nargs='?', default=None, help="Параметр (дата, ID снапшота, session_id, имя файла)")
parser.add_argument('-c', '--category', type=str, default=None, choices=[
'departments', 'positions', 'fio', 'position_keywords',
'include_fio', 'turnstile_fio', 'turnstile_departments',
'fligel_fio', 'fligel_departments'
], help="Категория исключения")
parser.add_argument('-v', '--value', type=str, default=None, help="Значение исключения (ФИО, отдел, должность)")
parser.add_argument('-m', '--comment', type=str, default="", help="Комментарий к исключению")
parser.add_argument('--snapshot', type=str, default=None, help="ID конкретного снапшота для инспекции")
parser.add_argument('--export-xlsx', type=str, default=None, help="Экспорт среза СКУД в Excel-файл")
parser.add_argument('--day', type=str, default=None, help="Удалить снапшоты за конкретный день (ДД.ММ.ГГГГ)")
parser.add_argument('--limit', type=int, default=50, help="Лимит выводимых сообщений чата (для команды context)")
parser.add_argument('--all', action='store_true', help="Полная очистка всех сообщений (для команды context purge)")
if len(sys.argv) == 1:
print_stats()
return
args = parser.parse_args()
if args.command == 'stats':
print_stats()
elif args.command == 'snapshots':
date_val = args.action or args.param
print_snapshots_list(date_str=date_val)
elif args.command == 'scud':
date_val = args.action or args.param
inspect_scud(snapshot_id=args.snapshot, date_str=date_val, export_xlsx=args.export_xlsx)
elif args.command == 'absences':
date_val = args.action or args.param
print_absences(date_str=date_val)
elif args.command == 'anomalies':
print_anomalies()
elif args.command == 'rules':
print_rules()
elif args.command == 'prompts':
print_system_prompts()
elif args.command == 'sessions':
print_session_states()
elif args.command == 'tools':
print_tool_actions()
elif args.command in ('in_building', 'presence'):
target_date = args.action if args.action else datetime.now().strftime("%d.%m.%Y")
print_building_presence(target_date, all_statuses=args.all)
elif args.command == 'context':
if args.action in ['purge', 'clear']:
is_all = args.all or (args.param == '--all')
sess_id = None if (args.param == '--all' or not args.param) else args.param
purge_chat_context(session_id=sess_id, purge_all=is_all)
else:
sess_id = args.action if args.action else None
print_chat_messages(session_id=sess_id, limit=args.limit)
elif args.command == 'dump':
filename = args.action or args.param or "db_dump_full.xlsx"
dump_all_to_excel(filename)
elif args.command == 'snapshot':
if args.action == 'del':
if args.day:
delete_snapshots_by_date(args.day)
elif args.param:
delete_snapshot_by_id(args.param)
else:
print("\n[❌] Ошибка: Не указан ID снапшота или параметр --day для удаления.")
print("Пример: python scripts/db_cli.py snapshot del Y20260805-007\n")
else:
print(f"\n[❌] Ошибка: Неизвестное действие '{args.action}' для команды snapshot.")
print("Используйте: python scripts/db_cli.py snapshot del [ID или --day 'ДД.ММ.ГГГГ']\n")
elif args.command == 'exceptions':
if args.action == 'add':
if not args.category or not args.value:
print("\n[❌] Ошибка: Для добавления исключения укажите флаги -c/--category и -v/--value")
print("Пример: python scripts/db_cli.py exceptions add -c include_fio -v \"Тарасенко Александр Александрович\"\n")
return
if add_exception_to_db(args.category, args.value, args.comment):
print(f"\n[✓] Успешно добавлено исключение: [{args.category}] {args.value}\n")
else:
print(f"\n[❌] Ошибка добавления исключения [{args.category}] {args.value}\n")
elif args.action in ['del', 'delete', 'remove']:
if not args.category or not args.value:
print("\n[❌] Ошибка: Для удаления исключения укажите флаги -c/--category и -v/--value")
print("Пример: python scripts/db_cli.py exceptions del -c fio -v \"Михалев Сергей Геннадьевич\"\n")
return
if remove_exception_from_db(args.category, args.value):
print(f"\n[✓] Успешно удалено исключение: [{args.category}] {args.value}\n")
else:
print(f"\n[⚠️] Запись не найдена в базе: [{args.category}] {args.value}\n")
elif args.action == 'sync':
sync_json_to_db()
print("\n[✓] Синхронизация exceptions.json -> SQLite успешно завершена.\n")
else:
print_exceptions()
else:
print("\n[❌] Ошибка: Неизвестная команда.")
print(HELP_TEXT)
if __name__ == "__main__":
main()
File: ./scripts/fix_snapshots.py
import sqlite3
import os
from datetime import datetime
DB_PATH = 'data/scud_orion_ai.db'
def parse_to_date(date_str):
"""Надежное преобразование даты логов и времени снапшота в объект date."""
if not date_str:
return None
date_str = str(date_str).split(" ")[0].strip()
if "." in date_str:
try:
return datetime.strptime(date_str, "%d.%m.%Y").date()
except ValueError:
return None
elif "-" in date_str:
try:
return datetime.strptime(date_str, "%Y-%m-%d").date()
except ValueError:
return None
return None
def fix_snapshots():
"""
Переиндексирует снапшоты в таблице scud_logs.
Нумерация веников (001, 002, 003...) сквозная внутри каждого ДНЯ СОЗДАНИЯ.
Префикс Y выставляется только если дата логов < даты создания.
"""
if not os.path.exists(DB_PATH):
print(f"База данных {DB_PATH} не найдена.")
return
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
# Группируем по уникальным выгрузкам
cursor.execute("""
SELECT snapshot_time, log_date
FROM scud_logs
WHERE snapshot_time IS NOT NULL
GROUP BY snapshot_time, log_date
ORDER BY snapshot_time ASC, log_date ASC
""")
rows = cursor.fetchall()
if not rows:
print("Снапшоты не найдены.")
conn.close()
return
print("=== ИСПРАВЛЕНИЕ СКВОЗНОЙ НУМЕРАЦИИ ВНУТРИ ДНЯ СОЗДАНИЯ ===")
# Счетчик текущего порядкового номера strictly за ДЕНЬ СОЗДАНИЯ (YYYYMMDD)
seq_counters = {}
for snap_time_raw, log_date_raw in rows:
d_snap = parse_to_date(snap_time_raw)
d_log = parse_to_date(log_date_raw)
if not d_snap or not d_log:
continue
date_prefix = d_snap.strftime("%Y%m%d")
# Если дата логов строго раньше даты создания снапшота — ставим Y
is_yesterday = (d_log < d_snap)
prefix = "Y" if is_yesterday else ""
# Приращиваем сквозной счетчик за ЭТОТ ДЕНЬ СОЗДАНИЯ
seq_counters[date_prefix] = seq_counters.get(date_prefix, 0) + 1
seq_num = seq_counters[date_prefix]
new_id = f"{prefix}{date_prefix}-{seq_num:03d}"
cursor.execute(
"UPDATE scud_logs SET snapshot_id = ? WHERE snapshot_time = ? AND log_date = ?",
(new_id, snap_time_raw, log_date_raw)
)
print(f" • Срез создания: {snap_time_raw} | Логи за: {log_date_raw} ===> Назначен ID: [{new_id}]")
conn.commit()
conn.close()
print("\n[✓] Переиндексация внутри дней создания успешно выполнена!")
if __name__ == "__main__":
fix_snapshots()
File: ./scripts/init_tool_registry.py
"""
Скрипт инициализации таблицы tool_action_registry в SQLite БД.
"""
import os
import sys
import json
import sqlite3
BASE_DIR = os.path.abspath(os.path.join(os.path.dirname(__file__), ".."))
DB_PATH = os.path.join(BASE_DIR, "data", "scud_orion_ai.db")
def init_tool_action_registry():
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
# 1. Создаем таблицу
cursor.execute("""
CREATE TABLE IF NOT EXISTS tool_action_registry (
id INTEGER PRIMARY KEY AUTOINCREMENT,
tool_name TEXT UNIQUE NOT NULL,
category TEXT DEFAULT 'general',
bypass_llm INTEGER DEFAULT 1,
success_template TEXT NOT NULL,
follow_up_question TEXT,
action_type TEXT DEFAULT 'BUTTONS',
buttons_json TEXT,
is_active INTEGER DEFAULT 1,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
# 2. Начальное наполнение базовыми сценариями
seed_tools = [
(
"db_confirm_prompt_preview",
"prompt",
1,
"✅ Системный промпт успешно сохранен и применен в базе данных.",
"Желаете продолжить работу с системным промптом?",
"FOLLOW_UP_ACTION",
json.dumps([
{"label": "Показать промпт", "value": "покажи системный промпт"},
{"label": "Готово", "value": "нет, спасибо"}
], ensure_ascii=False)
),
(
"db_cancel_prompt_preview",
"prompt",
1,
"❌ Изменения системного промпта отменены.",
"Желаете продолжить работу с системным промптом?",
"FOLLOW_UP_ACTION",
json.dumps([
{"label": "Показать промпт", "value": "покажи системный промпт"},
{"label": "Готово", "value": "нет, спасибо"}
], ensure_ascii=False)
),
(
"db_delete_task",
"tasks",
1,
"🗑 Задача успешно удалена из вашего реестра.",
"Хотите посмотреть обновленный список задач?",
"FOLLOW_UP_ACTION",
json.dumps([
{"label": "Показать задачи", "value": "покажи список задач"},
{"label": "Готово", "value": "нет"}
], ensure_ascii=False)
),
(
"db_delete_snapshots",
"snapshots",
1,
"🗑 Выбранные снапшоты успешно удалены из базы данных.",
"Вывести актуальный список снапшотов?",
"FOLLOW_UP_ACTION",
json.dumps([
{"label": "Показать снапшоты", "value": "покажи список снапшотов"},
{"label": "Готово", "value": "нет"}
], ensure_ascii=False)
)
]
for item in seed_tools:
cursor.execute("""
INSERT INTO tool_action_registry (
tool_name, category, bypass_llm, success_template, follow_up_question, action_type, buttons_json
) VALUES (?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(tool_name) DO UPDATE SET
category = excluded.category,
bypass_llm = excluded.bypass_llm,
success_template = excluded.success_template,
follow_up_question = excluded.follow_up_question,
action_type = excluded.action_type,
buttons_json = excluded.buttons_json,
updated_at = CURRENT_TIMESTAMP
""", item)
conn.commit()
conn.close()
print("[✓] Таблица tool_action_registry успешно создана и наполнена!")
if __name__ == "__main__":
init_tool_action_registry()
File: ./services/ai_verifier.py
import json
import re
import requests
import pandas as pd
from config import OLLAMA_URL, OLLAMA_MODEL
from core.database import get_all_rules_from_db
from core.database import get_department_synonyms_dict, add_department_synonym_to_db
def resolve_department_exception_ai(dept_1c, dept_scud, exception_departments):
"""
Универсальный сопоставитель отделов.
Проверяет 1С, СКУД, локальную базу синонимов SQLite и задействует ИИ для сложных случайных аббревиатур.
"""
if not exception_departments:
return False
d_1c = str(dept_1c).strip().lower() if dept_1c else ""
d_scud = str(dept_scud).strip().lower() if dept_scud else ""
exc_list = [d.strip().lower() for d in exception_departments]
# 1. Прямая проверка: если точное имя или подстрока уже совпали в 1С или СКУД
for exc in exc_list:
if exc and (exc in d_1c or exc in d_scud or d_1c in exc or d_scud in exc):
return True
# 2. Проверка по сохраненной Базе Знаний синонимов из SQLite
synonyms = get_department_synonyms_dict()
for exc in exc_list:
# Если в БД зафиксировано: 'овк' -> 'отдел внутреннего контроля'
full_from_db = synonyms.get(exc, "")
if full_from_db and (full_from_db in d_1c or full_from_db in d_scud):
return True
# 3. Умный ИИ-арбитраж (если отдел спорный и еще не сохранен в БД)
if d_1c or d_scud:
dept_to_check = d_1c if d_1c else d_scud
prompt = f"""
Ты — кадровый аналитик.
Проверь, является ли отдел сотрудника "{dept_to_check}" тем же самым подразделением, что и один из отделов-исключений: {exception_departments}?
Примеры:
- "Отдел внутреннего контроля" — это "ОВК" (Да)
- "Отдел технического обеспечения" — это "ОТО" (Да)
Ответь СТРОГО в формате JSON:
{{
"is_match": true/false,
"matched_exception": "Название из списка исключений",
"explanation": "краткое объяснение"
}}
"""
try:
raw_res = ask_ollama(prompt, system_prompt="Отвечай только валидным JSON.")
match = re.search(r'\{.*\}', raw_res, re.DOTALL)
if match:
data = json.loads(match.group(0))
if data.get("is_match"):
matched_exc = data.get("matched_exception", "").lower()
# Запоминаем открытую ИИ связь в SQLite навсегда!
add_department_synonym_to_db(matched_exc, dept_to_check)
return True
except Exception as e:
print(f"[⚠️] Ошибка ИИ-арбитража отделов: {e}")
return False
def ask_ollama(prompt, system_prompt=None):
"""
Универсальная функция отправки запросов к локальной модели Ollama (Qwen 2.5).
"""
payload = {
"model": OLLAMA_MODEL,
"prompt": f"{system_prompt}\n\n{prompt}" if system_prompt else prompt,
"stream": False
}
try:
response = requests.post(OLLAMA_URL, json=payload, timeout=120)
if response.status_code == 200:
data = response.json()
if "response" in data:
return data["response"].strip()
elif "message" in data and "content" in data["message"]:
return data["message"]["content"].strip()
else:
return f"⚠️ Неизвестная структура ответа Ollama: {data}"
else:
return f"⚠️ Ошибка Ollama (Код {response.status_code}): {response.text}"
except requests.exceptions.ConnectionError:
return "⚠️ Не удалось подключиться к Ollama. Проверьте, запущен ли сервис (ollama run qwen2.5:14b)."
except Exception as e:
return f"⚠️ Ошибка при обращении к Ollama: {e}"
def get_system_rules_context():
"""
Загружает динамические системные правила компании напрямую из Базы Данных SQLite.
"""
rules = get_all_rules_from_db()
if not rules:
return ""
rules_text = "\n".join([f"- {r}" for r in rules])
return f"\nОБЯЗАТЕЛЬНЫЕ ГЛОБАЛЬНЫЕ ПРАВИЛА И ПРИОРИТЕТЫ КОМПАНИИ (ИЗ SQLITE БД):\n{rules_text}\n"
def analyze_scud_mass_failure_ai(df_scud):
"""
Оценивает процент аномалий 'ANOMALY_NO_IN_HAS_ACTIVITY' в выгрузке.
Если процент аномалий превышает 5% от смены или 10 человек, вызывает ИИ для генерации критического алерта.
"""
if df_scud is None or df_scud.empty:
return None
total_records = len(df_scud)
anomaly_rows = df_scud[df_scud.get('anomaly_flag', 'NONE') == 'ANOMALY_NO_IN_HAS_ACTIVITY']
anomaly_count = len(anomaly_rows)
if total_records == 0:
return None
anomaly_percent = round((anomaly_count / total_records) * 100, 1)
if anomaly_percent > 5.0 or anomaly_count >= 10:
rules_context = get_system_rules_context()
prompt = f"""
{rules_context}
ВНИМАНИЕ! Проведён анализ смены СКУД:
- Всего записей за смену: {total_records}
- Выявлено сотрудников без утреннего входа, но с зафиксированной дневной активностью: {anomaly_count} ({anomaly_percent}% от смены)
Сформируй понятное предупреждение для Администратора СКУД и Руководителя.
Объясни, что это критический массовый сбой турникетов/контроллеров входа на КПП, и порекомендуй действия.
"""
alert_text = ask_ollama(prompt, system_prompt="Ты — ИИ-аналитик контроллинга СКУД. Отвечай кратко и строго по делу.")
return {
"is_mass_failure": True,
"anomaly_count": anomaly_count,
"anomaly_percent": anomaly_percent,
"alert_text": alert_text
}
return {
"is_mass_failure": False,
"anomaly_count": anomaly_count,
"anomaly_percent": anomaly_percent,
"alert_text": "Массовых сбоев оборудования не зафиксировано."
}
def ai_verify_scud_against_staff(unrecognized_scud_fios, staff_fios):
if not unrecognized_scud_fios or not staff_fios:
return {}
# Исключаем точные совпадения
staff_fios_set = set(staff_fios)
real_unrecognized = [f for f in unrecognized_scud_fios if f not in staff_fios_set]
if not real_unrecognized:
return {}
rules_context = get_system_rules_context()
prompt = f"""
Ты — кадровый аудитор безопасности СКУД.
{rules_context}
В СКУД записаны неопознанные ФИО: {json.dumps(real_unrecognized, ensure_ascii=False)}
В официальном Штатном расписании 1С записаны ЭТАЛОНЫ: {json.dumps(staff_fios, ensure_ascii=False)}
СТРОГИЕ ПРАВИЛА:
1. Запись из Штат 1С — это 100% ПРАВИЛЬНЫЙ эталон.
2. В СКУД допущена опечатка.
3. Любое присутствие сотрудника по СКУД при наличии в 1С документа отсутствия (кроме командировок) является гарантированной аномалией.
4. В поле "warning" опиши обнаруженную опечатку.
5. Запрещено выдумывать опечатки и объединять разных людей/однофамильцев!
ОТВЕЧАЙ ТОЛЬКО ИСКЛЮЧИТЕЛЬНО В ФОРМАТЕ ВАЛИДНОГО JSON:
{{
"verified_matches": [
{{
"scud_fio": "ФИО из СКУД",
"staff_fio": "эталон ФИО из Штат 1С",
"warning": "Описание опечатки в СКУД"
}}
]
}}
"""
raw_response = ask_ollama(
prompt,
system_prompt="Ты — строгий JSON API генератор. Отвечай только валидным JSON объектом без пояснительного текста."
)
mapping = {}
if not raw_response:
return mapping
# Фаза 1: Попытка прямого разбора с санитарной очисткой
try:
match = re.search(r'\{.*\}', raw_response, re.DOTALL)
if match:
json_str = match.group(0)
# Убираем висячие запятые: {"a": 1,} -> {"a": 1}
json_str = re.sub(r',\s*([\}\]])', r'\1', json_str)
# Заменяем одинарные кавычки в ключах/значениях на двойные при необходимости
json_str = re.sub(r"(?<=\{|\,)\s*'([^']+)'\s*:", r'"\1":', json_str)
data = json.loads(json_str)
for item in data.get("verified_matches", []):
scud_f = item.get("scud_fio")
staff_f = item.get("staff_fio")
warn = item.get("warning", "Точное совпадение (без опечаток)")
if scud_f and staff_f and staff_f in staff_fios_set:
mapping[scud_f] = {"staff_fio": staff_f, "warning": warn}
return mapping
except Exception:
pass
# Фаза 2: Резервный Regex-парсер (если JSON синтаксически сломан, но пары ключ-значение есть)
try:
pattern = r'["\']scud_fio["\']\s*:\s*["\']([^"\']+)["\'].*?["\']staff_fio["\']\s*:\s*["\']([^"\']+)["\']'
matches = re.findall(pattern, raw_response, re.DOTALL)
for scud_f, staff_f in matches:
scud_clean = scud_f.strip()
staff_clean = staff_f.strip()
if staff_clean in staff_fios_set:
mapping[scud_clean] = {"staff_fio": staff_clean, "warning": "Восстановлено парсером опечаток"}
except Exception as e:
print(f"[!] Ошибка резервного парсинга опечаток: {e}")
return mapping
def ai_verify_department_exceptions(unexplained_df, exception_departments):
"""
Локальный ИИ проверяет, не являются ли неотмеченные отделы синонимами отделов-исключений.
"""
if unexplained_df.empty or not exception_departments:
return []
dept_list = unexplained_df['Подразделение'].dropna().unique().tolist()
rules_context = get_system_rules_context()
prompt = f"""
{rules_context}
Ты — кадровый аудитор.
Список отделов-исключений компании: {json.dumps(exception_departments, ensure_ascii=False)}
Список отделов сотрудников, попавших в неизвестные: {json.dumps(dept_list, ensure_ascii=False)}
Определи, какие из отделов сотрудников являются ПОЛНЫМИ НАЗВАНИЯМИ или СИНУНИМАМИ отделов-исключений (например: 'Отдел внутреннего контроля' — это 'ОВК').
Выдай ответ строго в формате JSON:
{{
"matched_departments": ["Название отдела 1", "Название отдела 2"]
}}
"""
raw_response = ask_ollama(prompt, system_prompt="Выдавай только валидный JSON.")
try:
match = re.search(r'\{.*\}', raw_response, re.DOTALL)
if match:
data = json.loads(match.group(0))
return data.get("matched_departments", [])
except Exception as e:
print(f"[⚠️] Ошибка ИИ-арбитража отделов: {e}")
return []
File: ./services/data_loader.py
"""
===============================================================================
FILE: services/data_loader.py
ROLE: Надежная загрузка штата и отсутствий (MS SQL ЗУП -> Резервный Excel).
===============================================================================
"""
import os
import warnings
from datetime import datetime # <-- ДОБАВИТЬ ЭТУ СТРОКУ
import pandas as pd
from config import (
normalize_fio, clean_scud_fio_light, load_exceptions,
DATE_TODAY, DATE_YESTERDAY, find_dated_file, ZUP_1C_DIR, SCUD_DIR, DATA_DIR
)
from services.zup_extractor import fetch_zup_absences_from_sql
from core.database import (
load_scud_from_db_by_snapshot,
load_staff_from_db,
load_absences_from_db
)
warnings.filterwarnings('ignore', category=UserWarning, module='pandas')
def load_staff_data(date_str):
"""Загружает файл Штатного расписания 1С."""
filepath = find_dated_file("Штат", date_str)
if not filepath:
fallback_path = os.path.join(ZUP_1C_DIR, "штат.xlsx")
if os.path.exists(fallback_path):
filepath = fallback_path
else:
return None
try:
df_raw = pd.read_excel(filepath, skiprows=8)
df_staff = df_raw.iloc[:, [1, 5, 12]].copy()
df_staff.columns = ['ФИО', 'Подразделение', 'Должность']
df_staff = df_staff.dropna(subset=['ФИО']).reset_index(drop=True)
df_staff = df_staff[~df_staff['ФИО'].astype(str).str.contains('Всего|Организация|Сотрудник|ФИО', case=False, na=False)]
df_staff['fio_clean'] = df_staff['ФИО'].apply(normalize_fio)
return df_staff
except Exception as e:
print(f"[❌] Ошибка загрузки штата из {filepath}: {e}")
return None
def load_absent_from_excel(date_str):
"""Резервное чтение файла Отсутствия_ДД_ММ_ГГГГ.xlsx."""
filepath = find_dated_file("Отсутствия", date_str)
if not filepath:
return None
try:
for skip in [0, 1, 2, 3, 4, 5, 8]:
df_try = pd.read_excel(filepath, skiprows=skip)
fio_col = None
reason_col = None
for col in df_try.columns:
c_str = str(col).lower()
if ('фио' in c_str or 'сотрудник' in c_str) and fio_col is None:
fio_col = col
if ('вид' in c_str or 'причина' in c_str or 'отсутств' in c_str) and reason_col is None:
reason_col = col
if fio_col and reason_col:
df_res = df_try[[fio_col, reason_col]].copy()
df_res.columns = ['ФИО', 'Вид_отсутствия']
df_res = df_res.dropna(subset=['ФИО', 'Вид_отсутствия'])
df_res = df_res[~df_res['ФИО'].astype(str).str.contains('Всего|Организация|Сотрудник|ФИО|ЛЕНМОРНИИПРОЕКТ', case=False, na=False)]
df_res['fio_clean'] = df_res['ФИО'].apply(normalize_fio)
print(f" [✓] Резервный Excel: загружено {len(df_res)} записей отсутствий из {os.path.basename(filepath)}")
return df_res[['fio_clean', 'Вид_отсутствия']].dropna(subset=['fio_clean'])
except Exception as e:
print(f" [⚠️] Ошибка чтения резервного Excel отсутствий {filepath}: {e}")
return None
def load_absent_data(date_str):
"""
Загружает отсутствия из MS SQL 1С:ЗУП, а при сбое — из локального Excel.
"""
df_absent = None
try:
df_absent = fetch_zup_absences_from_sql(date_str)
if df_absent is not None and not df_absent.empty:
print(f" [✓] MS SQL ЗУП: получено {len(df_absent)} записей отсутствий за {date_str}")
df_absent = df_absent.dropna(subset=['ФИО', 'Вид_отсутствия'])
df_absent = df_absent[~df_absent['ФИО'].astype(str).str.contains('АО "ЛЕНМОРНИИПРОЕКТ"|Сотрудник', case=False, na=False)]
df_absent['fio_clean'] = df_absent['ФИО'].apply(normalize_fio)
df_absent = df_absent[['fio_clean', 'Вид_отсутствия']].dropna(subset=['fio_clean'])
except Exception as e:
print(f" [⚠️] Ошибка подключения к MS SQL ЗУП за {date_str}: {e}")
# Резервный источник: Excel файл из 1С с сетевой шары
if df_absent is None or df_absent.empty:
df_absent = load_absent_from_excel(date_str)
if df_absent is None:
df_absent = pd.DataFrame(columns=['fio_clean', 'Вид_отсутствия'])
# Обогащение удаленщиками из static_reason_workers.csv с ротацией просроченных записей
try:
static_path = os.path.join(DATA_DIR, "static_reason_workers.csv")
if os.path.exists(static_path):
df_static = pd.read_csv(static_path, dtype=str, on_bad_lines='skip').fillna("")
for col in ['fio', 'reason', 'department', 'date_from', 'date_to']:
if col not in df_static.columns:
df_static[col] = ""
today_date = datetime.now().date()
# Разбор целевой даты отчета
clean_target_str = str(date_str).replace('_', '.')
try:
clean_target_date = datetime.strptime(clean_target_str, "%d.%m.%Y").date()
except ValueError:
clean_target_date = today_date
def parse_date_safe(d_val):
if not d_val or str(d_val).lower() in ['nan', 'none', '', 'nat']:
return None
s = str(d_val).strip().replace('_', '.')
for fmt in ("%d.%m.%Y", "%Y-%m-%d"):
try:
return datetime.strptime(s, fmt).date()
except ValueError:
pass
return None
active_for_file = []
new_rows_for_report = []
file_changed = False
existing_fios = set(df_absent['fio_clean'].dropna().tolist()) if not df_absent.empty else set()
for _, s_row in df_static.iterrows():
fio_raw = str(s_row.get('fio', '')).strip()
if not fio_raw:
continue
fio_c = normalize_fio(fio_raw)
reason = str(s_row.get('reason', '')).strip() or "Дистанционная работа"
d_from = parse_date_safe(s_row.get('date_from'))
d_to = parse_date_safe(s_row.get('date_to'))
# 1. Физическая ротация просроченных: только если текущий реальный день (today) строго больше date_to
if d_to is not None and today_date > d_to:
print(f" [🧹] Удаленка истекла: {fio_raw} (до {d_to.strftime('%d.%m.%Y')}). Удалена из CSV.")
file_changed = True
continue
active_for_file.append(s_row.to_dict())
# 2. Проверка действия удаленки на дату формируемого отчета:
# Если date_from не указана — действует всегда до date_to
is_after_start = (d_from is None) or (clean_target_date >= d_from)
is_before_end = (d_to is None) or (clean_target_date <= d_to)
if is_after_start and is_before_end:
if fio_c not in existing_fios:
new_rows_for_report.append({
'fio_clean': fio_c,
'Вид_отсутствия': reason
})
existing_fios.add(fio_c)
# Перезаписываем CSV только если реально были удалены просроченные сотрудники
if file_changed:
pd.DataFrame(active_for_file).to_csv(static_path, index=False, encoding='utf-8')
print(" [✓] Файл static_reason_workers.csv синхронизирован без просроченных записей.")
if new_rows_for_report:
df_absent = pd.concat([df_absent, pd.DataFrame(new_rows_for_report)], ignore_index=True)
print(f" [✓] Реестр удаленщиков: добавлено {len(new_rows_for_report)} чел. в отчет за {date_str}")
except Exception as e:
print(f" [⚠️] Ошибка обработки static_reason_workers.csv: {e}")
# Обогащение реестрами "Мест. командир." и "Иное"
try:
from services.manual_absences_repo import get_active_manual_absences_for_date
manual_records = get_active_manual_absences_for_date(date_str)
if manual_records:
existing_fios = set(df_absent['fio_clean'].dropna().tolist()) if not df_absent.empty else set()
manual_rows = []
for r in manual_records:
fc = r['fio_clean']
if fc not in existing_fios:
label = "Мест. командир." if r['absence_type'] == 'LOCAL_TRIP' else "Иное"
manual_rows.append({
'fio_clean': fc,
'Вид_отсутствия': label
})
existing_fios.add(fc)
if manual_rows:
df_absent = pd.concat([df_absent, pd.DataFrame(manual_rows)], ignore_index=True)
print(f" [✓] Реестры 'Мест. командир.' / 'Иное': добавлено {len(manual_rows)} чел. в отчет за {date_str}")
except Exception as e:
print(f" [⚠️] Ошибка применения manual_absences: {e}")
return df_absent if not df_absent.empty else None
def load_1c_data_smart(date_str, use_db=False):
df_staff = None
df_absent = None
if use_db:
df_staff = load_staff_from_db(date_str)
df_absent = load_absences_from_db(date_str)
if df_staff is None:
df_staff = load_staff_data(date_str)
if df_absent is None:
df_absent = load_absent_data(date_str)
return df_staff, df_absent
File: ./services/data_validator.py
import os
import time
from datetime import datetime
from config import DATE_TODAY, DATE_YESTERDAY, find_dated_file, ZUP_1C_DIR, SCUD_DIR
def check_file_freshness(max_age_hours=24):
"""
Проверяет существование входных файлов 1С и СКУД.
Возвращает флаги доступности файлов за Сегодня и Вчера.
"""
warnings = []
errors = []
# 1. Проверка файлов 1С за СЕГОДНЯ
staff_today = find_dated_file("Штат", DATE_TODAY)
absent_today = find_dated_file("Отсутствия", DATE_TODAY)
has_today_1c = bool(staff_today and absent_today)
if not has_today_1c:
warnings.append(
f"Файлы 1С за СЕГОДНЯ ({DATE_TODAY}) еще не выложены на сетевую шару. "
f"Формирование Ежедневной сводки за сегодня будет временно пропущено."
)
# 2. Проверка файлов 1С за ВЧЕРА (критически важны для детального отчета)
staff_yesterday = find_dated_file("Штат", DATE_YESTERDAY)
absent_yesterday = find_dated_file("Отсутствия", DATE_YESTERDAY)
if not staff_yesterday or not absent_yesterday:
errors.append(
f"Отсутствуют файлы 1С за ВЧЕРА ({DATE_YESTERDAY}) в папке {ZUP_1C_DIR}. "
f"Невозможно построить детальный отчет за прошлую смену."
)
# 3. Проверка файлов СКУД
scud_today = find_dated_file("Сотрудники", DATE_TODAY, search_dirs=[SCUD_DIR])
if not scud_today:
warnings.append(f"Сегодняшний файл СКУД за {DATE_TODAY} еще не сформирован в {SCUD_DIR}")
is_valid = len(errors) == 0
return is_valid, warnings, errors, has_today_1c
File: ./services/excel_exporter.py
"""
===============================================================================
FILE: services/excel_exporter.py
ROLE: Единый фасад генераторов Excel-отчетов (обратная совместимость).
===============================================================================
"""
from services.reports.styles import (
format_date_ru,
get_dated_reports_dir,
safe_close_workbook,
create_xlsx_format
)
from services.reports.calculators import calculate_deviation
# Реэкспорт функций генерации
from services.reports.svodka_builder import generate_summary_excel
from services.reports.otchet_builder import generate_detailed_excel
from services.reports.raw_scud_builder import export_raw_scud
File: ./services/exceptions_repo.py
"""
===============================================================================
FILE: services/exceptions_repo.py
ROLE: Управление исключениями в SQLite с синхронизацией с exceptions.json.
===============================================================================
"""
from typing import Dict, List, Any
import json
import os
from config import EXCEPTIONS_PATH, normalize_fio
from core.connection import get_connection
def init_exceptions_table():
with get_connection() as conn:
conn.execute("""
CREATE TABLE IF NOT EXISTS exceptions_registry (
id INTEGER PRIMARY KEY AUTOINCREMENT,
category TEXT NOT NULL,
value TEXT NOT NULL,
comment TEXT DEFAULT '',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE(category, value)
);
""")
conn.commit()
def get_all_exceptions_from_db() -> Dict[str, List[str]]:
init_exceptions_table()
cfg = {
"departments": [],
"positions": [],
"fio": [],
"position_keywords": [],
"include_fio": [],
"turnstile_fio": [],
"turnstile_departments": [],
"fligel_fio": [], # ⭐️ Сотрудники с доступом через Флигель (DoorIndex = 23)
"fligel_departments": [] # ⭐️ Отделы с доступом через Флигель
}
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("SELECT category, value FROM exceptions_registry")
rows = cursor.fetchall()
if not rows and os.path.exists(EXCEPTIONS_PATH):
sync_json_to_db()
return get_all_exceptions_from_db()
for cat, val in rows:
if cat in cfg:
cfg[cat].append(val)
return cfg
def add_exception_to_db(category: str, value: str, comment: str = "") -> bool:
init_exceptions_table()
# Нормализуем ФИО в том числе для реестра флигеля
val_clean = normalize_fio(value) if category in ["fio", "include_fio", "turnstile_fio", "fligel_fio"] else value.strip()
if not val_clean:
return False
with get_connection() as conn:
conn.execute(
"INSERT OR REPLACE INTO exceptions_registry (category, value, comment) VALUES (?, ?, ?)",
(category, val_clean, comment)
)
conn.commit()
return True
def remove_exception_from_db(category: str, value: str) -> bool:
init_exceptions_table()
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("DELETE FROM exceptions_registry WHERE category = ? AND value = ?", (category, value.strip()))
conn.commit()
return cursor.rowcount > 0
def sync_json_to_db():
"""Переносит данные из exceptions.json в SQLite."""
if not os.path.exists(EXCEPTIONS_PATH):
return
try:
with open(EXCEPTIONS_PATH, "r", encoding="utf-8") as f:
data = json.load(f)
for cat, items in data.items():
for item in items:
add_exception_to_db(cat, item, comment="Импорт из JSON")
except Exception as e:
print(f"[⚠️] Ошибка синхронизации JSON -> DB: {e}")
File: ./services/feedback_loop.py
import os
import json
from core.database import add_rule_to_db
try:
from services.knowledge_base import save_rule
except ImportError:
try:
from services.knowledge_base import add_rule as save_rule
except ImportError:
save_rule = None
def review_ai_decisions(report_text, suspicious_cases):
"""
Интерактивный консольный модуль обучения (Human-in-the-Loop).
Запрашивает подтверждение оператора ТОЛЬКО по нетипичным/сложным случаям.
"""
if not suspicious_cases:
print("\n[✓] Все аномалии валидированы автоматически. Спорных вопросов для оператора нет.")
return
print("\n" + "=" * 60)
print("РЕЖИМ ОБУЧЕНИЯ И ПРОВЕРКИ РЕШЕНИЙ ИИ (HUMAN-IN-THE-LOOP)")
print("=" * 60)
for idx, case in enumerate(suspicious_cases, 1):
fio_target = case.get('fio_target', 'Неизвестно')
reason = case.get('reason', 'Нет описания')
print(f"\n[?] Спорный случай {idx}/{len(suspicious_cases)}: {fio_target}")
print(f" Детали: {reason}")
user_input = input("Подтверждаете аномалию? (д / да / н / нет / или введите текст правила): ").strip()
user_input_clean = user_input.lower()
# Расширенный список вариантов "ДА"
if user_input_clean in ['д', 'да', 'da', 'yes', 'y', 'ок', 'ага', '+']:
print(" [✓] Решение подтверждено оператором.")
elif user_input_clean in ['н', 'нет', 'no', 'n', '-']:
print(" [ℹ️] Решение отклонено оператором.")
elif user_input:
# Если оператор обучает систему синониму отделов
if "отдел" in user_input_clean or "овк" in user_input_clean:
from core.database import add_department_synonym_to_db
add_department_synonym_to_db(user_input.strip(), "отдел внутреннего контроля")
new_rule = f"Правило: {user_input}"
add_rule_to_db(new_rule, added_by="Human_Admin")
print(f" [✓] Новое правило записано в Базу Знаний SQLite: {new_rule}")
else:
print(" [ℹ️] Запись пропущена.")
print("\n[✓] ЭТАП ОБУЧЕНИЯ ЗАВЕРШЕН.")
File: ./services/knowledge/service.py
"""
===============================================================================
FILE: services/knowledge/service.py
PROJECT: SCUD Orion AI (Unified Architecture)
MODULE: services / knowledge
ROLE: Доменный сервис базы знаний, правил компании и синонимов подразделений.
===============================================================================
"""
from typing import List, Dict, Any
from core.repositories.zup_repo import (
get_all_rules_from_db,
add_rule_to_db,
get_department_synonyms_dict,
add_department_synonym_to_db
)
def get_rules() -> List[Dict[str, Any]]:
"""Получить все правила базы знаний в виде списка словарей."""
raw_rules = get_all_rules_from_db()
return [{"id": idx, "rule_text": r} for idx, r in enumerate(raw_rules, 1)]
def add_rule(rule_text: str, added_by: str = "Human") -> None:
"""Добавить новое правило в базу знаний."""
add_rule_to_db(rule_text, added_by=added_by)
def get_synonyms() -> Dict[str, str]:
"""Получить словарь синонимов отделов."""
return get_department_synonyms_dict()
def register_department_synonym(short_name: str, full_name: str) -> None:
"""Сохранить новую пару синонимов подразделения."""
add_department_synonym_to_db(short_name, full_name)
File: ./services/knowledge_base.py
import os
from core.database import get_all_rules_from_db, add_rule_to_db
def load_knowledge_base():
"""
Загружает актуальные правила Базы Знаний компании напрямую из базы данных SQLite.
"""
rules = get_all_rules_from_db()
return {
"rules": rules if rules else [],
"fio_corrections": {}
}
def add_rule_to_kb(new_rule):
"""
Добавляет новое принятое человеком правило в SQLite таблицу ai_knowledge_base.
"""
if not new_rule or not new_rule.strip():
return
add_rule_to_db(new_rule.strip(), added_by="Human")
print(f"[✓] База знаний SQLite успешно обновлена! Новое правило: {new_rule.strip()}")
File: ./services/manual_absences_repo.py
"""
===============================================================================
FILE: services/manual_absences_repo.py
ROLE: Репозиторий ручных реестров ("Мест. командир.", "Иное") и поиск по штату 1С.
===============================================================================
"""
import os
import csv
from datetime import datetime
from typing import List, Dict, Any, Optional
from core.connection import get_connection
from config import normalize_fio, DATA_DIR
REASONS_CSV_PATH = os.path.join(DATA_DIR, "static_reason_absence.csv")
def init_manual_absences_table() -> None:
with get_connection() as conn:
conn.execute("""
CREATE TABLE IF NOT EXISTS manual_absences (
id INTEGER PRIMARY KEY AUTOINCREMENT,
absence_type TEXT NOT NULL, -- 'LOCAL_TRIP' или 'OTHER'
fio TEXT NOT NULL,
fio_clean TEXT NOT NULL,
department TEXT DEFAULT '',
position TEXT DEFAULT '',
date_start TEXT, -- 'YYYY-MM-DD'
date_end TEXT, -- 'YYYY-MM-DD'
reason TEXT NOT NULL,
comment TEXT DEFAULT '',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
conn.execute("CREATE INDEX IF NOT EXISTS idx_manual_abs_dates ON manual_absences(date_start, date_end);")
conn.execute("CREATE INDEX IF NOT EXISTS idx_manual_abs_fio ON manual_absences(fio_clean);")
conn.commit()
def get_static_reasons() -> List[str]:
"""Возвращает список причин из data/static_reason_absence.csv."""
if not os.path.exists(REASONS_CSV_PATH):
# Если файл еще не создан, создаем базовый набор причин
os.makedirs(os.path.dirname(REASONS_CSV_PATH), exist_ok=True)
default_reasons = ["По семейным обстоятельствам", "Медосмотр", "Сдача крови", "Учебный отпуск", "Административный отпуск"]
with open(REASONS_CSV_PATH, "w", encoding="utf-8", newline="") as f:
writer = csv.writer(f)
writer.writerow(["reason"])
for r in default_reasons:
writer.writerow([r])
return default_reasons
reasons = []
try:
with open(REASONS_CSV_PATH, "r", encoding="utf-8") as f:
reader = csv.reader(f)
for row in reader:
if row and row[0].strip() and row[0].strip().lower() != "reason":
reasons.append(row[0].strip())
except Exception:
pass
return reasons
def search_staff_suggestions(query: str, limit: int = 15) -> List[Dict[str, str]]:
"""Живой поиск сотрудников по zup_staff для автокомплита."""
q = (query or "").strip()
if not q or len(q) < 2:
return []
# Приводим к разным регистрам для гарантированного поиска кириллицы в SQLite
q_lower = q.lower()
q_title = q.capitalize()
with get_connection(row_factory=True) as conn:
cursor = conn.cursor()
# 1. Находим действительно самый свежий срез штата (по created_at или по структуре даты ГГГГ-ММ-ДД)
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()
latest_date = row[0] if row else None
if not latest_date:
return []
# 2. Поиск с сортировкой: сначала те, у кого фамилия НАЧИНАЕТСЯ с запроса
sql = """
SELECT DISTINCT fio, fio_clean, department, position
FROM zup_staff
WHERE snapshot_date = ?
AND (
fio LIKE ? OR fio LIKE ? OR fio_clean LIKE ? OR fio_clean LIKE ?
OR fio LIKE ? OR fio_clean LIKE ?
)
ORDER BY
CASE
WHEN fio LIKE ? OR fio_clean LIKE ? THEN 0
ELSE 1
END,
fio ASC
LIMIT ?
"""
prefix_pattern_title = f"{q_title}%"
prefix_pattern_lower = f"{q_lower}%"
any_pattern_title = f"%{q_title}%"
any_pattern_lower = f"%{q_lower}%"
cursor.execute(sql, (
latest_date,
prefix_pattern_title, prefix_pattern_lower, prefix_pattern_title, prefix_pattern_lower,
any_pattern_title, any_pattern_lower,
prefix_pattern_title, prefix_pattern_title,
limit
))
rows = cursor.fetchall()
return [
{
"fio": r["fio"],
"fio_clean": r["fio_clean"],
"department": r["department"] or "—",
"position": r["position"] or "—"
}
for r in rows
]
def add_manual_absence(
absence_type: str,
fio: str,
reason: str,
department: str = "",
position: str = "",
date_start: Optional[str] = None,
date_end: Optional[str] = None,
comment: str = ""
) -> int:
init_manual_absences_table()
clean_fio = normalize_fio(fio)
if not clean_fio:
return 0
today_str = datetime.now().strftime("%Y-%m-%d")
d_start = date_start.strip() if date_start and date_start.strip() else today_str
d_end = date_end.strip() if date_end and date_end.strip() else today_str
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("""
INSERT INTO manual_absences (
absence_type, fio, fio_clean, department, position,
date_start, date_end, reason, comment
)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
""", (absence_type.upper(), fio.strip(), clean_fio, department.strip(), position.strip(), d_start, d_end, reason.strip(), comment.strip()))
conn.commit()
return cursor.lastrowid
def delete_manual_absence(item_id: int) -> bool:
init_manual_absences_table()
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("DELETE FROM manual_absences WHERE id = ?", (item_id,))
conn.commit()
return cursor.rowcount > 0
def get_manual_absences_list(absence_type: Optional[str] = None) -> List[Dict[str, Any]]:
today_str = datetime.now().strftime("%Y-%m-%d")
# ⭐️ Обязательно row_factory=True для преобразования строк SQLite в dict
with get_connection(row_factory=True) as conn:
cursor = conn.cursor()
# 1. Автоочистка просроченных командировок и отсутствий
cursor.execute("""
DELETE FROM manual_absences
WHERE date_end IS NOT NULL
AND date_end != ''
AND date_end < ?
""", (today_str,))
conn.commit()
# 2. Выборка активных записей
query = "SELECT id, absence_type, fio, department, position, reason, date_start, date_end, comment FROM manual_absences"
params = []
if absence_type:
query += " WHERE absence_type = ?"
params.append(absence_type)
query += " ORDER BY id DESC"
cursor.execute(query, params)
rows = cursor.fetchall()
return [dict(r) for r in rows]
def get_active_manual_absences_for_date(date_str: str) -> List[Dict[str, Any]]:
"""Выбирает записи, активные на дату отчета (формат даты ДД.ММ.ГГГГ)."""
init_manual_absences_table()
try:
dt_target = datetime.strptime(date_str.replace('_', '.'), "%d.%m.%Y").strftime("%Y-%m-%d")
except Exception:
dt_target = datetime.now().strftime("%Y-%m-%d")
with get_connection(row_factory=True) as conn:
cursor = conn.cursor()
cursor.execute("""
SELECT id, absence_type, fio, fio_clean, department, position, date_start, date_end, reason
FROM manual_absences
WHERE (date_start IS NULL OR date_start <= ?)
AND (date_end IS NULL OR date_end >= ?)
""", (dt_target, dt_target))
return [dict(r) for r in cursor.fetchall()]
File: ./services/presence_service.py
"""
===============================================================================
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
}
File: ./services/prompts/diff_engine.py
"""
===============================================================================
FILE: services/prompts/diff_engine.py
PROJECT: SCUD Orion AI (Unified Architecture)
MODULE: services / prompts
ROLE: Формирование превью изменений промпта и генерация визуального HTML-Diff.
AI-CONTEXT-ANCHORS:
- ANCHOR[PROMPT_DIFF_BUILDER]: Генерация черновика и HTML-разметки изменений.
===============================================================================
"""
from typing import Tuple, Dict, List, Set
from core.connection import get_connection
def build_prompt_diff(
action: str,
section_id: int = None,
item_id: int = None,
content: str = "",
delete_nodes: List[Tuple[int, int]] = None,
prompt_name: str = "main_agent"
) -> Tuple[str, str]:
act = (action or "ADD").upper()
nodes_to_delete: Set[Tuple[int, int]] = set()
if delete_nodes:
nodes_to_delete.update(delete_nodes)
elif act == "DELETE" and section_id is not None and item_id is not None:
nodes_to_delete.add((section_id, item_id))
with get_connection(row_factory=True) as conn:
cursor = conn.cursor()
cursor.execute("""
SELECT section_id, item_id, content
FROM system_prompt_nodes
WHERE prompt_name = ? AND is_active = 1
ORDER BY section_id, item_id
""", (prompt_name,))
existing_nodes = cursor.fetchall()
nodes_dict = {(r["section_id"], r["item_id"]): r["content"] for r in existing_nodes}
nodes_dict_for_draft = {k: v for k, v in nodes_dict.items() if k not in nodes_to_delete}
if act not in ["DELETE", "BATCH_DELETE"] and section_id is not None and item_id is not None:
nodes_dict_for_draft[(section_id, item_id)] = content
draft_lines = []
curr_sec = None
for (s_id, i_id), txt in sorted(nodes_dict_for_draft.items()):
if i_id == 0:
if curr_sec is not None: draft_lines.append("")
draft_lines.append(f"{s_id}. {txt}")
curr_sec = s_id
else:
draft_lines.append(f" {s_id}.{i_id}. {txt}")
merged_prompt = "\n".join(draft_lines)
diff_lines = []
curr_sec = None
display_nodes = dict(nodes_dict)
if act not in ["DELETE", "BATCH_DELETE"] and section_id is not None and item_id is not None:
display_nodes[(section_id, item_id)] = content
for (s_id, i_id), txt in sorted(display_nodes.items()):
if i_id == 0:
if curr_sec is not None: diff_lines.append("")
diff_lines.append(f"{s_id}. {txt}")
curr_sec = s_id
else:
if (s_id, i_id) in nodes_to_delete:
line_str = f' <span class="line-through text-rose-600 font-bold bg-rose-50 px-1.5 py-0.5 rounded border border-rose-300 opacity-80">{s_id}.{i_id}. {txt} [УДАЛЕНИЕ]</span>'
elif act == "ADD" and s_id == section_id and i_id == item_id:
line_str = f' <span class="text-rose-600 font-bold bg-rose-50 px-1.5 py-0.5 rounded border border-rose-300">{s_id}.{i_id}. {txt}</span>'
else:
line_str = f" {s_id}.{i_id}. {txt}"
diff_lines.append(line_str)
return merged_prompt, "\n".join(diff_lines)
File: ./services/prompts/repository.py
"""
===============================================================================
FILE: services/prompts/repository.py
PROJECT: SCUD Orion AI (Unified Architecture)
MODULE: services / prompts
ROLE: Реляционное хранилище узлов системного промпта (таблица system_prompt_nodes).
AI-CONTEXT-ANCHORS:
- ANCHOR[PROMPT_REPO_GET_ACTIVE]: Сборка активного промпта из узлов БД.
- ANCHOR[PROMPT_REPO_APPLY_ACTION]: Точечная вставка / изменение / удаление узла.
===============================================================================
"""
import re
import logging
from typing import List, Tuple, Dict, Any
from core.connection import get_connection
logger = logging.getLogger("PROMPT_REPO")
# ANCHOR[PROMPT_REPO_GET_ACTIVE]
def repo_get_active_prompt(prompt_name: str = "main_agent") -> str:
"""Собирает структурированный текст системного промпта из активных узлов."""
with get_connection(row_factory=True) as conn:
cursor = conn.cursor()
cursor.execute("""
SELECT section_id, item_id, content
FROM system_prompt_nodes
WHERE prompt_name = ? AND is_active = 1
ORDER BY section_id ASC, item_id ASC
""", (prompt_name,))
rows = cursor.fetchall()
if not rows:
return "Ты — ИИ-ассистент SCUD Orion AI."
lines = []
current_section = None
for r in rows:
sec_id = r["section_id"]
itm_id = r["item_id"]
content = r["content"]
if itm_id == 0:
if current_section is not None:
lines.append("")
lines.append(f"{sec_id}. {content}")
current_section = sec_id
else:
lines.append(f" {sec_id}.{itm_id}. {content}")
return "\n".join(lines)
# ANCHOR[PROMPT_REPO_APPLY_ACTION]
def repo_apply_prompt_action(action: str, section_id: int, item_id: int, content: str = "", prompt_name: str = "main_agent") -> None:
"""Точечно применяет действие (ADD / UPDATE / DELETE) над узлом промпта."""
with get_connection() as conn:
cursor = conn.cursor()
action_clean = (action or "").upper()
if action_clean in ["ADD", "UPDATE", "EDIT"]:
cursor.execute("""
INSERT INTO system_prompt_nodes (prompt_name, section_id, item_id, content, is_active, updated_at)
VALUES (?, ?, ?, ?, 1, CURRENT_TIMESTAMP)
ON CONFLICT(prompt_name, section_id, item_id) DO UPDATE SET
content = excluded.content,
is_active = 1,
updated_at = CURRENT_TIMESTAMP
""", (prompt_name, section_id, item_id, content))
elif action_clean == "DELETE":
cursor.execute("""
DELETE FROM system_prompt_nodes
WHERE prompt_name = ? AND section_id = ? AND item_id = ?
""", (prompt_name, section_id, item_id))
conn.commit()
def repo_save_full_prompt(prompt_text: str, prompt_name: str = "main_agent") -> None:
"""Парсит и полностью перезаписывает все узлы промпта из сырого текста."""
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("DELETE FROM system_prompt_nodes WHERE prompt_name = ?", (prompt_name,))
current_sec = 1
current_itm = 0
for raw_line in prompt_text.splitlines():
clean_line = re.sub(r'<[^>]+>', '', raw_line).strip()
if not clean_line:
continue
sub_match = re.match(r'^(\d+)[\.\s]+(\d+)[\.\s\:\-]+(.*)$', clean_line)
sec_match = re.match(r'^(\d+)[\.\s\:\-]+(.*)$', clean_line)
if sub_match:
current_sec = int(sub_match.group(1))
current_itm = int(sub_match.group(2))
content = sub_match.group(3).strip()
elif sec_match and not any(c.islower() for c in sec_match.group(2)[:15]):
current_sec = int(sec_match.group(1))
current_itm = 0
content = sec_match.group(2).strip()
else:
current_itm += 1
content = clean_line
cursor.execute("""
INSERT OR REPLACE INTO system_prompt_nodes (prompt_name, section_id, item_id, content, is_active)
VALUES (?, ?, ?, ?, 1)
""", (prompt_name, current_sec, current_itm, content))
conn.commit()
File: ./services/prompts/service.py
"""
===============================================================================
FILE: services/prompts/service.py
PROJECT: SCUD Orion AI (Unified Architecture)
MODULE: services / prompts
ROLE: Единый доменный сервис управления системным промптом.
===============================================================================
"""
from typing import Tuple, Dict, Any, List, Optional
from .repository import repo_get_active_prompt, repo_apply_prompt_action, repo_save_full_prompt
from .diff_engine import build_prompt_diff
def get_active_system_prompt() -> str:
"""Получить текущий активный системный промпт."""
return repo_get_active_prompt()
def apply_prompt_action(action: str, section_id: int, item_id: int, content: str = "") -> None:
"""Применить точечное изменение к узлу промпта."""
repo_apply_prompt_action(action, section_id, item_id, content)
def save_full_prompt_draft(draft_text: str, prompt_name: str = "main_agent") -> None:
"""Сохранить полный черновик промпта."""
repo_save_full_prompt(draft_text, prompt_name=prompt_name)
def create_prompt_preview(
action: str,
section_id: Optional[int] = None,
item_id: Optional[int] = None,
content: str = "",
delete_nodes: Optional[List[Tuple[int, int]]] = None
) -> Tuple[str, str, str]:
"""
Формирует черновик и diff.
Возвращает (merged_draft, diff_html, baseline_prompt).
"""
baseline = repo_get_active_prompt()
draft, diff_html = build_prompt_diff(
action=action,
section_id=section_id,
item_id=item_id,
content=content,
delete_nodes=delete_nodes
)
return draft, diff_html, baseline
File: ./services/reports/calculators.py
"""
===============================================================================
FILE: services/reports/calculators.py
ROLE: Расчет баланса рабочего времени, обеденного перерыва и отклонений от нормы.
===============================================================================
"""
import pandas as pd
def calculate_deviation(time_in_building_str, reason="", norm_hours=8, lunch_minutes=30):
"""
Расчет отклонения от нормы.
Для удаленщиков при наличии физического времени в здании вычисляется реальное отклонение.
"""
reason_clean = str(reason).strip().lower() if pd.notna(reason) else ""
is_remote = "удален" in reason_clean or "дистанцион" in reason_clean
has_building_time = isinstance(time_in_building_str, str) and time_in_building_str not in ['00:00', '0', '', 'None', 'nan', 'NaN']
# Если есть уважительная причина (больничный, отпуск, командировка и т.д.) не удаленка
if reason_clean != "" and not is_remote:
return "0:00"
# Если удаленщик работал исключительно из дома (00:00 в здании)
if is_remote and not has_building_time:
return "0:00"
# Если сотрудника не было в здании и нет уважительной причины
if not has_building_time:
return f"-{norm_hours}:00"
try:
parts = time_in_building_str.strip().split(':')
hh = int(parts[0])
mm = int(parts[1]) if len(parts) > 1 else 0
total_in_building_minutes = hh * 60 + mm
if total_in_building_minutes == 0:
return "0:00" if is_remote else f"-{norm_hours}:00"
work_minutes = max(0, total_in_building_minutes - lunch_minutes)
norm_minutes = norm_hours * 60
diff = work_minutes - norm_minutes
if diff == 0:
return "0:00"
sign = "-" if diff < 0 else ""
abs_diff = abs(diff)
res_hh = abs_diff // 60
res_mm = abs_diff % 60
return f"{sign}{res_hh}:{res_mm:02d}"
except Exception:
return "0:00" if is_remote else f"-{norm_hours}:00"
File: ./services/reports/otchet_builder.py
"""
===============================================================================
FILE: services/reports/otchet_builder.py
ROLE: Генератор книги Детального суточного отчета со сверкой 1С:ЗУП.
===============================================================================
"""
import os
import math
import pandas as pd
import xlsxwriter
from services.reports.styles import get_dated_reports_dir, format_date_ru, safe_close_workbook, create_xlsx_format
from services.reports.calculators import calculate_deviation
def generate_detailed_excel(merged_df, date_str="20.08.2026", filename=None):
date_clean = str(date_str).replace('_', '.')
if not filename:
filename = f"{format_date_ru(date_clean)} отчет.xlsx"
is_exc = merged_df.get('is_excluded', False) == True
is_not_hired = merged_df.get('not_hired_yet', False) == True
# Исключаем из отчета за вчера сотрудников-исключений И тех, кто еще не принят на работу
df_export = merged_df[(~is_exc) & (~is_not_hired)].copy() if merged_df is not None and not merged_df.empty else pd.DataFrame()
target_dir = get_dated_reports_dir(date_clean)
output_path = os.path.join(target_dir, filename)
# Включаем опцию защиты nan_inf_to_errors
wb = xlsxwriter.Workbook(output_path, {'nan_inf_to_errors': True})
ws = wb.add_worksheet("Детальный_отчет")
fmt_date_lbl = wb.add_format({'font_name': 'Arial', 'font_size': 10, 'bold': True})
ws.write(1, 1, "Дата:", fmt_date_lbl)
ws.write(1, 3, date_clean, fmt_date_lbl)
headers = [
"№", "ФИО", "Подразделение", "время входа", "первая активность", "время выхода",
"находился в здании", "причина отсутствия", "норма", "отклонение от нормы"
]
fmt_hdr = create_xlsx_format(wb, font_name='Arial', font_size=10, bg_color='#D9E1F2', bold=True, align="center", wrap=True)
ws.set_row(3, 26)
for col_idx, h_text in enumerate(headers):
ws.write(3, col_idx, h_text, fmt_hdr)
start_col = 'Начало дня' if 'Начало дня' in df_export.columns else 'Начало_дня'
end_col = 'Конец дня' if 'Конец дня' in df_export.columns else 'Конец_дня'
hours_col = 'Часы' if 'Часы' in df_export.columns else 'Находился_в_здании'
chars_per_line_h = 24
for idx, row in df_export.reset_index(drop=True).iterrows():
row_num = 4 + idx
is_present = bool(row.get('Пришел', False))
absence_reason = row.get('Вид_отсутствия', '')
has_reason = pd.notna(absence_reason) and str(absence_reason).strip() not in ['', 'nan', 'None']
in_val = str(row.get(start_col, 'Нет входа')).strip()
if in_val.lower() in ['nan', 'none']: in_val = 'Нет входа'
out_val = str(row.get(end_col, 'Нет выхода')).strip()
if out_val.lower() in ['nan', 'none']: out_val = 'Нет выхода'
in_building_str = str(row.get(hours_col, '00:00')).strip()
if in_building_str.lower() in ['nan', 'none']: in_building_str = '00:00'
first_act_val = str(row.get('Первая_активность', '—')).strip()
if first_act_val.lower() in ['nan', 'none']: first_act_val = '—'
has_first_act = first_act_val not in ['—', '', 'None', 'nan']
deviation_val = calculate_deviation(in_building_str, reason=absence_reason if has_reason else "", norm_hours=8, lunch_minutes=30)
dept_raw = row.get('department_scud', row.get('department', row.get('Подразделение', '')))
dept_scud_val = str(dept_raw).strip() if pd.notna(dept_raw) else '—'
if dept_scud_val.lower() in ['nan', 'none']: dept_scud_val = '—'
fio_raw = row.get('Сотрудник', '')
fio_val = str(fio_raw).strip() if pd.notna(fio_raw) else ''
if fio_val.lower() in ['nan', 'none']: fio_val = ''
row_color = None
if is_present and has_reason:
row_color = '#E2EFDA'
elif not is_present and has_reason:
row_color = '#FFF2CC'
elif not is_present and not has_reason and not has_first_act:
row_color = '#FCE4D6'
val_h_str = str(absence_reason).strip() if has_reason else ""
if val_h_str.lower() in ['nan', 'none']: val_h_str = ""
lines_count = math.ceil(len(val_h_str) / chars_per_line_h) if len(val_h_str) > chars_per_line_h else 1
ws.set_row(row_num, max(lines_count * 18, 20))
values = [
(idx + 1, 'center', False),
(fio_val, 'left', False),
(dept_scud_val, 'center', False),
(in_val, 'center', False),
(first_act_val, 'center', False),
(out_val, 'center', False),
(in_building_str, 'center', False),
(val_h_str, 'left', True),
(8, 'center', False),
(deviation_val, 'center', False)
]
for col_idx, (val, align_type, is_wrap) in enumerate(values):
# Санитизация для исключения передачи float('nan') в _write_number
if pd.isna(val) or val is None or str(val).strip().lower() == 'nan':
val = ""
fmt = create_xlsx_format(wb, font_name='Arial', font_size=10, bg_color=row_color, align=align_type, wrap=is_wrap)
ws.write(row_num, col_idx, val, fmt)
col_widths = {0: 4, 1: 33, 2: 13, 3: 11, 4: 11, 5: 11, 6: 12, 7: 24, 8: 6, 9: 11}
for col_idx, width in col_widths.items():
ws.set_column(col_idx, col_idx, width)
safe_close_workbook(wb, output_path, target_dir, filename)
File: ./services/reports/raw_scud_builder.py
"""
===============================================================================
FILE: services/reports/raw_scud_builder.py
ROLE: Генерация Excel-файла сырых данных СКУД.
===============================================================================
"""
import os
import pandas as pd
import xlsxwriter
from config import REPORTS_DIR
from services.reports.styles import safe_close_workbook, create_xlsx_format
def export_raw_scud(df_scud, filename="СКУД_Сырые_данные.xlsx"):
output_path = os.path.join(REPORTS_DIR, filename)
target_dir = os.path.dirname(output_path)
wb = xlsxwriter.Workbook(output_path)
ws = wb.add_worksheet("Сырые_данные")
fmt_hdr = create_xlsx_format(wb, bold=True, bg_color='#D9E1F2', align='center')
fmt_cell = create_xlsx_format(wb, align='left')
headers = list(df_scud.columns)
ws.set_row(0, 28)
for col_idx, header in enumerate(headers):
ws.write(0, col_idx, str(header), fmt_hdr)
col_widths = [len(str(h)) for h in headers]
for row_idx, row_values in enumerate(df_scud.values, start=1):
ws.set_row(row_idx, 19)
for col_idx, val in enumerate(row_values):
val_str = "" if (pd.isna(val) or val is None) else ("Да" if isinstance(val, bool) and val else ("Нет" if isinstance(val, bool) else str(val)))
ws.write(row_idx, col_idx, val_str, fmt_cell)
if len(val_str) > col_widths[col_idx]:
col_widths[col_idx] = len(val_str)
for col_idx, width in enumerate(col_widths):
ws.set_column(col_idx, col_idx, min(max(width + 3, 10), 45))
safe_close_workbook(wb, output_path, target_dir, filename)
File: ./services/reports/simplified_builder.py
"""
===============================================================================
FILE: services/reports/simplified_builder.py
ROLE: Генератор книги "Упрощенный отчет за ДД.ММ.ГГГГг..xlsx"
на основе агрегированных данных СКУД и 1С.
===============================================================================
"""
import os
import openpyxl
from openpyxl.styles import Font, Alignment, Border, Side
from datetime import datetime, time
import pandas as pd
from config import REPORTS_DIR
from services.reports.styles import get_dated_reports_dir
def format_fio_initials(full_fio: str) -> str:
"""Преобразует 'Иванов Иван Иванович' в 'Иванов И.И.'"""
if not full_fio or pd.isna(full_fio):
return ""
parts = str(full_fio).strip().split()
if len(parts) >= 3:
return f"{parts[0]} {parts[1][0]}.{parts[2][0]}."
elif len(parts) == 2:
return f"{parts[0]} {parts[1][0]}."
return str(full_fio).strip()
def parse_time_cell(val, default_empty="Нет входа (0:00)"):
"""Преобразует строку времени СКУД в time(hh, mm) либо оставляет текстовую заглушку."""
if not val or pd.isna(val):
return default_empty
s = str(val).strip()
if s in ["Нет входа", "—", "", "nan", "None", "00:00:00", "00:00"]:
return default_empty
if s in ["Нет выхода"]:
return "Нет выхода (23:59)"
# Если уже пришёл time
if isinstance(val, time):
return val
try:
parts = s.split(":")
h = int(parts[0])
m = int(parts[1]) if len(parts) > 1 else 0
return time(h, m)
except Exception:
return s
def generate_simplified_excel(df_merged: pd.DataFrame, date_str: str, filename: str = None) -> str:
"""
Генерирует Excel-файл упрощенного отчета точно по образцу Ориона.
Структура:
A: № | B: Подразделение | C: Сотрудник | D: Должность | E: Начало дня | F: Конец дня
"""
clean_date = str(date_str).replace('_', '.')
if not filename:
filename = f"Упрощенный отчет за {clean_date}г..xlsx"
target_dir = get_dated_reports_dir(clean_date)
output_path = os.path.join(target_dir, filename)
# Исключаем тех, кто не принят на работу и исключения
is_exc = df_merged.get('is_excluded', False) == True
is_not_hired = df_merged.get('not_hired_yet', False) == True
df_src = df_merged[(~is_exc) & (~is_not_hired)].copy()
# Подготовка данных
rows_to_sort = []
for _, r in df_src.iterrows():
fio_raw = r.get('Сотрудник', r.get('ФИО', ''))
dept = str(r.get('Подразделение', '—')).strip()
pos = str(r.get('Должность', '—')).strip()
if pos.lower() in ['nan', 'none', '']: pos = '—'
if dept.lower() in ['nan', 'none', '']: dept = '—'
fio_short = format_fio_initials(fio_raw)
t_in = parse_time_cell(r.get('Начало_дня'), default_empty="Нет входа (0:00)")
t_out = parse_time_cell(r.get('Конец_дня'), default_empty="Нет выхода (23:59)")
rows_to_sort.append({
'dept': dept,
'fio_short': fio_short,
'pos': pos,
't_in': t_in,
't_out': t_out
})
# Сортировка: Подразделение (А-Я), затем Сотрудник (А-Я)
rows_sorted = sorted(rows_to_sort, key=lambda x: (x['dept'].lower(), x['fio_short'].lower()))
# Создание книги openpyxl для точного соблюдения структуры образца
wb = openpyxl.Workbook()
ws = wb.active
ws.title = "Лист1"
# Стили по образцу
font_main = Font(name="Calibri", size=11, bold=False)
align_center = Alignment(horizontal="center", vertical="center")
border_thin = Border(
left=Side(style="thin"),
right=Side(style="thin"),
top=Side(style="thin"),
bottom=Side(style="thin")
)
# Заголовок
ws["B1"] = f"Упрощенный отчет: c {clean_date} по {clean_date}"
ws["B1"].font = font_main
# Шапка таблицы (Строка 3)
headers = [None, "Подразделение", "Сотрудник", "Должность", "Начало дня", "Конец дня"]
for col_idx, h_text in enumerate(headers, start=1):
cell = ws.cell(row=3, column=col_idx, value=h_text)
cell.font = font_main
cell.alignment = align_center
cell.border = border_thin
# Заполнение строк данных (с 4 строки)
for idx, item in enumerate(rows_sorted, start=1):
row_num = 3 + idx
vals = [idx, item['dept'], item['fio_short'], item['pos'], item['t_in'], item['t_out']]
for col_idx, val in enumerate(vals, start=1):
cell = ws.cell(row=row_num, column=col_idx, value=val)
cell.font = font_main
cell.alignment = align_center
cell.border = border_thin
# Числовой формат времени h:mm
if isinstance(val, time):
cell.number_format = "h:mm"
# Настройка ширины колонок по образцу
widths = {'A': 13.0, 'B': 25.0, 'C': 27.0, 'D': 80.0, 'E': 21.5, 'F': 23.3}
for col_letter, w in widths.items():
ws.column_dimensions[col_letter].width = w
# Создание пустых Лист2 и Лист3 как в оригинальном шаблоне
wb.create_sheet("Лист2")
wb.create_sheet("Лист3")
wb.save(output_path)
return output_path
File: ./services/reports/styles.py
"""
===============================================================================
FILE: services/reports/styles.py
ROLE: Стили, палитры цветов, форматирование дат и защита от блокировок Excel.
===============================================================================
"""
import os
import time
from datetime import datetime
from xlsxwriter.exceptions import FileCreateError
from config import REPORTS_DIR
MONTHS_RU_GENITIVE = {
1: "января", 2: "февраля", 3: "марта", 4: "апреля",
5: "мая", 6: "июня", 7: "июля", 8: "августа",
9: "сентября", 10: "октября", 11: "ноября", 12: "декабря"
}
MONTHS_RU_NOMINATIVE = {
1: "январь", 2: "февраль", 3: "март", 4: "апрель",
5: "май", 6: "июнь", 7: "июль", 8: "август",
9: "сентябрь", 10: "октябрь", 11: "ноябрь", 12: "декабрь"
}
def format_date_ru(date_str):
date_clean = str(date_str).replace('_', '.')
try:
dt = datetime.strptime(date_clean, "%d.%m.%Y")
return f"{dt.day} {MONTHS_RU_GENITIVE[dt.month]} {dt.year}"
except Exception:
return date_str
def get_dated_reports_dir(date_str):
date_clean = str(date_str).replace('_', '.')
try:
dt = datetime.strptime(date_clean, "%d.%m.%Y")
year_str = str(dt.year)
month_name = MONTHS_RU_NOMINATIVE[dt.month]
except Exception:
now = datetime.now()
year_str = str(now.year)
month_name = MONTHS_RU_NOMINATIVE[now.month]
target_dir = os.path.join(REPORTS_DIR, year_str, month_name)
os.makedirs(target_dir, exist_ok=True)
return target_dir
def safe_close_workbook(wb, output_path, target_dir, filename):
try:
wb.close()
return output_path
except (FileCreateError, OSError, PermissionError):
alt_filename = filename.replace(".xlsx", f"_{int(time.time())}.xlsx")
alt_path = os.path.join(target_dir, alt_filename)
try:
wb.filename = alt_path
wb._store_workbook()
return alt_path
except Exception:
return output_path
def create_xlsx_format(workbook, font_name="Calibri", font_size=11, bg_color=None, bold=False, align="left", wrap=False):
fmt_dict = {
'font_name': font_name,
'font_size': font_size,
'bold': bold,
'align': align,
'valign': 'vcenter',
'border': 1,
'border_color': '#D3D3D3',
'text_wrap': wrap
}
if bg_color:
fmt_dict['bg_color'] = bg_color
return workbook.add_format(fmt_dict)
File: ./services/reports/svodka_builder.py
"""
===============================================================================
FILE: services/reports/svodka_builder.py
ROLE: Генератор книги Ежедневной сводки (иерархические группировки XlsxWriter).
===============================================================================
"""
import os
import math
import pandas as pd
import xlsxwriter
from services.reports.styles import get_dated_reports_dir, format_date_ru, safe_close_workbook, create_xlsx_format
def generate_summary_excel(merged_df, date_str="21.08.2026", filename=None):
date_clean = str(date_str).replace('_', '.')
if not filename:
filename = f"{format_date_ru(date_clean)} сводка.xlsx"
target_dir = get_dated_reports_dir(date_clean)
output_path = os.path.join(target_dir, filename)
wb = xlsxwriter.Workbook(output_path, {'nan_inf_to_errors': True})
ws = wb.add_worksheet("Лист_1")
ws.outline_settings(visible=True, symbols_below=False, symbols_right=False, auto_style=False)
fmt_hdr_l = create_xlsx_format(wb, bg_color='#D9E1F2', bold=True, align="left")
fmt_hdr_r = create_xlsx_format(wb, bg_color='#D9E1F2', bold=True, align="right")
fmt_tot_l = create_xlsx_format(wb, bg_color='#F2F2F2', bold=True, align="left")
fmt_tot_r = create_xlsx_format(wb, bg_color='#F2F2F2', bold=True, align="right")
fmt_empty = create_xlsx_format(wb)
ws.set_row(0, 20)
ws.write(0, 0, "Сводка на", fmt_hdr_l)
ws.write(0, 1, date_clean, fmt_hdr_r)
ws.set_row(1, 20)
ws.write(1, 0, "", fmt_empty)
ws.write(1, 1, "", fmt_empty)
is_not_hired = merged_df.get('not_hired_yet', False) == True
is_no_pass = merged_df.get('no_scud_pass', False) == True
is_exc = merged_df.get('is_excluded', False) == True
# "По списку" строго по официальному штату 1С
staff_total = len(merged_df[~is_not_hired])
ws.set_row(2, 20)
ws.write(2, 0, "По списку", fmt_tot_l)
ws.write(2, 1, staff_total, fmt_tot_r)
current_row = 3
# 1. Неизвестно
unexplained = merged_df[
(~is_not_hired) &
(merged_df['Пришел'] == False) &
(merged_df['Вид_отсутствия'].isna() | (merged_df['Вид_отсутствия'].astype(str).str.strip().isin(['', 'nan', 'None']))) &
(~is_no_pass) & (~is_exc)
]
fmt_unexp_hl = create_xlsx_format(wb, bg_color='#FCE4D6', bold=True, align="left")
fmt_unexp_hr = create_xlsx_format(wb, bg_color='#FCE4D6', bold=True, align="right")
fmt_unexp_rl = create_xlsx_format(wb, bg_color='#FCE4D6', bold=False, align="left")
fmt_unexp_rr = create_xlsx_format(wb, bg_color='#FCE4D6', bold=False, align="right")
ws.set_row(current_row, 20)
ws.write(current_row, 0, "неизвестно", fmt_unexp_hl)
ws.write(current_row, 1, len(unexplained), fmt_unexp_hr)
current_row += 1
for fio in sorted(unexplained['Сотрудник'].dropna().unique()):
ws.set_row(current_row, 20, None, {'level': 1, 'hidden': False})
ws.write(current_row, 0, str(fio), fmt_unexp_rl)
ws.write(current_row, 1, "", fmt_unexp_rr)
current_row += 1
# 2. Нет пропуска (в штате 1С есть, но карты СКУД нет)
no_pass_df = merged_df[is_no_pass & (~is_exc) & (~is_not_hired)]
fmt_np_hl = create_xlsx_format(wb, bg_color='#E1F5FE', bold=True, align="left")
fmt_np_hr = create_xlsx_format(wb, bg_color='#E1F5FE', bold=True, align="right")
fmt_np_rl = create_xlsx_format(wb, bg_color='#E1F5FE', bold=False, align="left")
fmt_np_rr = create_xlsx_format(wb, bg_color='#E1F5FE', bold=False, align="right")
ws.set_row(current_row, 20)
ws.write(current_row, 0, "Нет в СКУД", fmt_np_hl)
ws.write(current_row, 1, len(no_pass_df), fmt_np_hr)
current_row += 1
if not no_pass_df.empty:
for fio in sorted(no_pass_df['Сотрудник'].dropna().unique()):
ws.set_row(current_row, 20, None, {'level': 1, 'hidden': False})
ws.write(current_row, 0, str(fio), fmt_np_rl)
ws.write(current_row, 1, "", fmt_np_rr)
current_row += 1
# 3. Не приняты на работу (в СКУД есть, но в 1С приказа еще нет)
not_hired_df = merged_df[is_not_hired & (~is_exc)]
fmt_nh_hl = create_xlsx_format(wb, bg_color='#FFF0F5', bold=True, align="left")
fmt_nh_hr = create_xlsx_format(wb, bg_color='#FFF0F5', bold=True, align="right")
fmt_nh_rl = create_xlsx_format(wb, bg_color='#FFF0F5', bold=False, align="left")
fmt_nh_rr = create_xlsx_format(wb, bg_color='#FFF0F5', bold=False, align="right")
ws.set_row(current_row, 20)
ws.write(current_row, 0, "Нет в ЗУП", fmt_nh_hl)
ws.write(current_row, 1, len(not_hired_df), fmt_nh_hr)
current_row += 1
if not not_hired_df.empty:
for fio in sorted(not_hired_df['Сотрудник'].dropna().unique()):
ws.set_row(current_row, 20, None, {'level': 1, 'hidden': False})
ws.write(current_row, 0, str(fio), fmt_nh_rl)
ws.write(current_row, 1, "", fmt_nh_rr)
current_row += 1
# 4. Официальные отсутствия
reason_clean = merged_df['Вид_отсутствия'].astype(str).str.lower()
is_remote_reason = reason_clean.str.contains('удален|дистанцион', regex=True, na=False)
absent_only = merged_df[
(~is_not_hired) &
(merged_df['Пришел'] == False) &
(merged_df['Вид_отсутствия'].notna()) &
(~merged_df['Вид_отсутствия'].astype(str).str.startswith('Исключение')) &
(~is_remote_reason)
]
absent_groups = absent_only.groupby('Вид_отсутствия')
pastels = ['#FFF2CC', '#E1D5E7', '#E1F5FE', '#FFF0F5', '#FCF3CF']
for idx_cat, (cat_name, group) in enumerate(absent_groups):
hex_c = pastels[idx_cat % len(pastels)]
fmt_cat_hl = create_xlsx_format(wb, bg_color=hex_c, bold=True, align="left")
fmt_cat_hr = create_xlsx_format(wb, bg_color=hex_c, bold=True, align="right")
fmt_cat_rl = create_xlsx_format(wb, bg_color=hex_c, bold=False, align="left")
fmt_cat_rr = create_xlsx_format(wb, bg_color=hex_c, bold=False, align="right")
ws.set_row(current_row, 20)
ws.write(current_row, 0, str(cat_name), fmt_cat_hl)
ws.write(current_row, 1, len(group), fmt_cat_hr)
current_row += 1
is_other_category = (str(cat_name).strip().lower() == "иное")
for _, row in group.sort_values(by='Сотрудник').iterrows():
fio = row.get('Сотрудник', '')
detail_val = row.get('detailed_reason', row.get('comment', '')) if is_other_category else ""
if pd.isna(detail_val):
detail_val = ""
ws.set_row(current_row, 20, None, {'level': 1, 'hidden': True, 'collapsed': True})
ws.write(current_row, 0, str(fio), fmt_cat_rl)
ws.write(current_row, 1, str(detail_val), fmt_cat_rr)
current_row += 1
# 5. Итого на работе (только принятые)
exc_without_doc = merged_df[(~is_not_hired) & is_exc & (merged_df['Вид_отсутствия'].isna() | (merged_df['Вид_отсутствия'].astype(str).str.strip().isin(['', 'nan', 'Исключение'])))]
present_scud = merged_df[(~is_not_hired) & (merged_df['Пришел'] == True) & (~is_exc)]
total_present_count = len(present_scud) + len(exc_without_doc)
fmt_pres_hl = create_xlsx_format(wb, bg_color='#E2EFDA', bold=True, align="left")
fmt_pres_hr = create_xlsx_format(wb, bg_color='#E2EFDA', bold=True, align="right")
ws.set_row(current_row, 20)
ws.write(current_row, 0, "Итого на работе", fmt_pres_hl)
ws.write(current_row, 1, total_present_count, fmt_pres_hr)
current_row += 1
# 6. Удаленная работа
remote_home = merged_df[(~is_not_hired) & (merged_df['Пришел'] == False) & is_remote_reason & (~is_exc)]
fmt_rem_hl = create_xlsx_format(wb, bg_color='#E8F8F5', bold=True, align="left")
fmt_rem_hr = create_xlsx_format(wb, bg_color='#E8F8F5', bold=True, align="right")
fmt_rem_rl = create_xlsx_format(wb, bg_color='#E8F8F5', bold=False, align="left")
fmt_rem_rr = create_xlsx_format(wb, bg_color='#E8F8F5', bold=False, align="right")
ws.set_row(current_row, 20)
ws.write(current_row, 0, "В том числе на удаленной работе", fmt_rem_hl)
ws.write(current_row, 1, len(remote_home), fmt_rem_hr)
current_row += 1
if not remote_home.empty:
for fio in sorted(remote_home['Сотрудник'].dropna().unique()):
ws.set_row(current_row, 20, None, {'level': 1, 'hidden': True, 'collapsed': True})
ws.write(current_row, 0, str(fio), fmt_rem_rl)
ws.write(current_row, 1, "", fmt_rem_rr)
current_row += 1
# 7. Аномалии СКУД и 1С
anomalies = merged_df[
(~is_not_hired) & (~is_exc) & (
((merged_df['Пришел'] == True) & (merged_df['Вид_отсутствия'].notna()) &
(~merged_df['Вид_отсутствия'].astype(str).str.startswith('Исключение')) &
(~is_remote_reason) &
(~reason_clean.str.contains('командировк', na=False))) |
(merged_df.get('anomaly_flag', 'NONE') == 'ANOMALY_NO_IN_HAS_ACTIVITY')
)
]
fmt_anom_hl = create_xlsx_format(wb, bg_color='#FCE4D6', bold=True, align="left")
fmt_anom_hr = create_xlsx_format(wb, bg_color='#FCE4D6', bold=True, align="right")
fmt_anom_rl = create_xlsx_format(wb, bg_color='#FCE4D6', bold=False, align="left")
fmt_anom_rr = create_xlsx_format(wb, bg_color='#FCE4D6', bold=False, align="left", wrap=True)
ws.set_row(current_row, 20)
ws.write(current_row, 0, "Аномалии СКУД и 1С", fmt_anom_hl)
ws.write(current_row, 1, len(anomalies), fmt_anom_hr)
current_row += 1
chars_per_line_b = 30
if not anomalies.empty:
for _, row in anomalies.iterrows():
fio = row.get('Сотрудник', '')
anom_flag = row.get('anomaly_flag', 'NONE')
reason_text = f"🚨 АНОМАЛИЯ СКУД: Нет входа (первая активность: {row.get('Первая_активность', '—')})" if anom_flag == 'ANOMALY_NO_IN_HAS_ACTIVITY' else f"В 1С: {row.get('Вид_отсутствия', '')}"
lines_count = math.ceil(len(reason_text) / chars_per_line_b) if len(reason_text) > chars_per_line_b else 1
ws.set_row(current_row, max(lines_count * 18, 20), None, {'level': 1, 'hidden': True, 'collapsed': True})
ws.write(current_row, 0, str(fio), fmt_anom_rl)
ws.write(current_row, 1, str(reason_text), fmt_anom_rr)
current_row += 1
ws.set_column(0, 0, 45)
ws.set_column(1, 1, 38)
safe_close_workbook(wb, output_path, target_dir, filename)
File: ./services/scud_etl/anomaly_detector.py
"""
===============================================================================
FILE: services/scud_etl/anomaly_detector.py
ROLE: Детектирование аномалий СКУД, дубликатов пропусков и несоответствий с 1С.
===============================================================================
"""
import logging
from typing import List, Dict, Any
import pandas as pd
from config import normalize_fio
logger = logging.getLogger("SCUD_ANOMALY")
def detect_registry_anomalies(df_merged: pd.DataFrame, df_raw_scud: pd.DataFrame = None) -> List[Dict[str, Any]]:
anomalies = []
if df_merged is None or df_merged.empty:
return anomalies
# 1. ⭐️ Поиск дубликатов учеток/пропусков в сыром СКУД
if df_raw_scud is not None and not df_raw_scud.empty:
df_raw = df_raw_scud.copy()
if 'fio_clean' not in df_raw.columns:
f_col = 'Сотрудник' if 'Сотрудник' in df_raw.columns else 'ФИО'
df_raw['fio_clean'] = df_raw[f_col].apply(normalize_fio)
counts = df_raw['fio_clean'].value_counts()
duplicate_fios = counts[counts > 1].index.tolist()
for dup_fio in duplicate_fios:
sub = df_raw[df_raw['fio_clean'] == dup_fio]
departments = ", ".join(sub['Подразделение'].astype(str).unique())
anomalies.append({
"fio": dup_fio,
"type": "DUPLICATE_SCUD_CARD",
"description": f"Сотрудник заведен в СКУД {len(sub)} раза (отделы: {departments}). События входа и выхода объединены автоматически."
})
# 2. Поиск кадровых аномалий и сбоев оборудования
for _, row in df_merged.iterrows():
if row.get("is_excluded", False):
continue
fio = row.get("fio_clean") or row.get("Сотрудник", "")
start_day = str(row.get("Начало_дня", "")).strip()
end_day = str(row.get("Конец_дня", "")).strip()
reason = str(row.get("причина отсутствия", row.get("Вид_отсутствия", ""))).strip()
reason_lower = reason.lower()
if "исключен" in reason_lower or "овк" in reason_lower:
continue
# Приход в офис во время отпуска/больничного (удаленка и командировки разрешены)
if start_day not in ["Нет входа", "—", "", "nan", "None"] and reason and reason != "nan":
if not ("удален" in reason_lower or "дистанцион" in reason_lower or "командировк" in reason_lower or "поездк" in reason_lower):
anomalies.append({
"fio": fio,
"type": "PHYSICAL_PRESENCE_DURING_ABSENCE",
"description": f"Присутствовал в здании ({start_day}), но в 1С числится документ: '{reason}'."
})
# Ошибка считывателя (есть выход без входа)
if start_day in ["Нет входа", "—", ""] and end_day not in ["Нет выхода", "—", "", "nan", "None"]:
anomalies.append({
"fio": fio,
"type": "SCUD_EQUIPMENT_ANOMALY",
"description": f"Зафиксирован выход ({end_day}) при отсутствии отметки утреннего входа."
})
return anomalies
File: ./services/scud_etl/merger.py
"""
===============================================================================
FILE: services/scud_etl/merger.py
ROLE: Агрегация реестров, выбор совместителей 1С по отделу СКУД и авто-связки.
Распределение исключений в общий рабочий пул при отсутствии справок.
===============================================================================
"""
import logging
from typing import Dict, Any, List
import pandas as pd
from services.knowledge.service import get_department_synonyms_dict
from config import normalize_fio, load_exceptions
from core.connection import get_connection
logger = logging.getLogger("SCUD_MERGER")
def load_identity_mappings() -> Dict[str, str]:
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS person_identity_mapping (
id INTEGER PRIMARY KEY AUTOINCREMENT,
scud_fio TEXT NOT NULL,
zup_fio TEXT NOT NULL,
scud_dept TEXT,
zup_dept TEXT,
match_source TEXT DEFAULT 'AI',
status TEXT DEFAULT 'ACTIVE',
confidence REAL DEFAULT 1.0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE(scud_fio, zup_fio)
);
""")
cursor.execute("""
SELECT scud_fio, zup_fio
FROM person_identity_mapping
WHERE status = 'ACTIVE'
""")
return {r[0]: r[1] for r in cursor.fetchall()}
def aggregate_scud_by_person(df_scud: pd.DataFrame) -> pd.DataFrame:
if df_scud is None or df_scud.empty:
return df_scud
df = df_scud.copy()
if 'fio_clean' not in df.columns:
fio_col = 'Сотрудник' if 'Сотрудник' in df.columns else 'ФИО'
df['fio_clean'] = df[fio_col].apply(normalize_fio)
mapping_dict = load_identity_mappings()
if mapping_dict:
df['fio_clean'] = df['fio_clean'].apply(lambda f: mapping_dict.get(f, f))
aggregated_rows = []
for fio_clean, group in df.groupby('fio_clean'):
if len(group) == 1:
aggregated_rows.append(group.iloc[0].to_dict())
continue
base_row = group.sort_values(by='Пришел', ascending=False).iloc[0].to_dict()
valid_ins = [
str(t).strip() for t in group['Начало_дня']
if str(t).strip() not in ['Нет входа', '—', '', 'nan', 'None', '00:00:00', '00:00']
]
base_row['Начало_дня'] = min(valid_ins) if valid_ins else 'Нет входа'
valid_outs = [
str(t).strip() for t in group['Конец_дня']
if str(t).strip() not in ['Нет выхода', '—', '', 'nan', 'None', '00:00:00', '00:00']
]
base_row['Конец_дня'] = max(valid_outs) if valid_outs else 'Нет выхода'
valid_acts = [
str(t).strip() for t in group['Первая_активность']
if str(t).strip() not in ['—', '', 'nan', 'None', '00:00:00']
]
base_row['Первая_активность'] = min(valid_acts) if valid_acts else '—'
base_row['Пришел'] = any(group['Пришел'] == True) or (base_row['Начало_дня'] != 'Нет входа')
durations = [str(d) for d in group['Находился_в_здании'] if str(d) not in ['00:00', '', 'nan']]
if durations:
base_row['Находился_в_здании'] = max(durations)
aggregated_rows.append(base_row)
return pd.DataFrame(aggregated_rows)
def select_best_zup_position(df_staff_1c: pd.DataFrame, df_scud_agg: pd.DataFrame) -> pd.DataFrame:
if df_staff_1c is None or df_staff_1c.empty:
return pd.DataFrame()
df_staff = df_staff_1c.copy()
if 'fio_clean' not in df_staff.columns:
f_col = 'ФИО' if 'ФИО' in df_staff.columns else 'Сотрудник'
df_staff['fio_clean'] = df_staff[f_col].apply(normalize_fio)
scud_dept_map = {}
if df_scud_agg is not None and not df_scud_agg.empty:
for _, r in df_scud_agg.iterrows():
scud_dept_map[r.get('fio_clean', '')] = str(r.get('Подразделение', '')).strip().upper()
best_rows = []
for fio, group in df_staff.groupby('fio_clean'):
if len(group) == 1:
best_rows.append(group.iloc[0].to_dict())
continue
target_scud_dept = scud_dept_map.get(fio, "")
matched_row = None
if target_scud_dept:
for _, r in group.iterrows():
dept_1c = str(r.get('Подразделение', '')).strip().upper()
if dept_1c == target_scud_dept or target_scud_dept in dept_1c or dept_1c in target_scud_dept:
matched_row = r.to_dict()
break
if not matched_row:
matched_row = group.iloc[0].to_dict()
best_rows.append(matched_row)
return pd.DataFrame(best_rows)
def merge_scud_and_1c(
df_scud: pd.DataFrame,
df_staff_1c: pd.DataFrame,
df_absences_1c: pd.DataFrame
) -> pd.DataFrame:
if (df_scud is None or df_scud.empty) and (df_staff_1c is None or df_staff_1c.empty):
return pd.DataFrame(columns=[
'Сотрудник', 'fio_clean', 'Подразделение', 'Должность',
'Начало_дня', 'Первая_активность', 'Конец_дня', 'Находился_в_здании',
'Пришел', 'anomaly_flag', 'причина отсутствия', 'Вид_отсутствия',
'is_excluded', 'not_hired_yet', 'no_scud_pass'
])
synonyms = get_department_synonyms_dict()
exceptions_cfg = load_exceptions()
df_scud_agg = aggregate_scud_by_person(df_scud)
df_staff_agg = select_best_zup_position(df_staff_1c, df_scud_agg)
staff_fios = set(df_staff_agg['fio_clean'].dropna().tolist()) if df_staff_agg is not None and not df_staff_agg.empty else set()
scud_dict = df_scud_agg.set_index('fio_clean').to_dict('index') if df_scud_agg is not None and not df_scud_agg.empty else {}
merged_rows = []
# 1. Формируем строки по официальному штату 1С
if df_staff_agg is not None and not df_staff_agg.empty:
for _, s_row in df_staff_agg.iterrows():
fio_c = s_row.get('fio_clean', '')
r = dict(s_row)
if 'Сотрудник' not in r or pd.isna(r['Сотрудник']) or str(r['Сотрудник']).strip() == '':
r['Сотрудник'] = r.get('ФИО', fio_c)
if fio_c in scud_dict:
# Сотрудник есть в СКУД — берем короткую аббревиатуру отдела из СКУД
scud_data = scud_dict[fio_c]
dept_scud = str(scud_data.get('Подразделение', '')).strip()
if dept_scud and dept_scud.lower() not in ['nan', 'none', '—', 'без подразделения']:
r['Подразделение'] = dept_scud
r['Начало_дня'] = scud_data.get('Начало_дня', 'Нет входа')
r['Первая_активность'] = scud_data.get('Первая_активность', '—')
r['Конец_дня'] = scud_data.get('Конец_дня', 'Нет выхода')
r['Находился_в_здании'] = scud_data.get('Находился_в_здании', '00:00')
r['Пришел'] = bool(scud_data.get('Пришел', False))
r['anomaly_flag'] = scud_data.get('anomaly_flag', 'NONE')
r['no_scud_pass'] = False
r['not_hired_yet'] = False
else:
# Сотрудника нет в СКУД (Нет пропуска)
r['Начало_дня'] = 'Нет входа'
r['Первая_активность'] = '—'
r['Конец_дня'] = 'Нет выхода'
r['Находился_в_здании'] = '00:00'
r['Пришел'] = False
r['anomaly_flag'] = 'NONE'
r['no_scud_pass'] = True
r['not_hired_yet'] = False
merged_rows.append(r)
# 2. Сотрудники из СКУД, которых еще нет в 1С (Не приняты на работу)
if df_scud_agg is not None and not df_scud_agg.empty:
for _, scud_row in df_scud_agg.iterrows():
fio_c = scud_row.get('fio_clean', '')
if fio_c not in staff_fios:
r = dict(scud_row)
r['not_hired_yet'] = True
r['no_scud_pass'] = False
if 'Должность' not in r or pd.isna(r['Должность']):
r['Должность'] = '—'
merged_rows.append(r)
df_res = pd.DataFrame(merged_rows)
if "Сотрудник" not in df_res.columns and "ФИО" in df_res.columns:
df_res["Сотрудник"] = df_res["ФИО"]
for col, default_val in [
('Начало_дня', 'Нет входа'),
('Первая_активность', '—'),
('Конец_дня', 'Нет выхода'),
('Находился_в_здании', '00:00'),
('Пришел', False),
('anomaly_flag', 'NONE'),
('not_hired_yet', False),
('no_scud_pass', False)
]:
if col not in df_res.columns:
df_res[col] = default_val
# Словарь синонимов и принудительное сокращение длинных отделов 1С до аббревиатур
reverse_synonyms = {v.lower(): k.upper() for k, v in synonyms.items()}
direct_synonyms = {k.lower(): k.upper() for k in synonyms.keys()}
all_dept_map = {
**reverse_synonyms,
**direct_synonyms,
"отдел внутреннего контроля": "ОВК",
"отдел вневедомственного контроля": "ОВК",
"отдел авторского надзора и технического аудита": "ОАН",
"отдел инженерных изысканий": "ОИЗ",
"правовое управление": "ПУ",
"макетная мастерская": "ММ",
"отдел автоматизации": "ОА",
"испытательная геотехническая лаборатория лабораторного центра": "ИГТЛЛ",
"отдел экономики, смет и организации строительства": "ОЭС",
"отдел электротехники, связи и пожарной автоматики": "ОЭСС",
"бетонная лаборатория": "БЛ",
"управление главных инженеров проектов №1": "УГИП №1",
"управление главных инженеров проектов №2": "УГИП №2",
"строительный отдел": "СО",
"гидротехническая экспедиция": "ГЭ",
"планово-экономический отдел": "ПЭО",
"конструкторский отдел": "КО",
"отдел тепловодоснабжения и канализации": "ОТВК",
"электротехнический отдел": "ЭТО"
}
if "Подразделение" in df_res.columns:
df_res["Подразделение"] = df_res["Подразделение"].apply(
lambda d: all_dept_map.get(str(d).strip().lower(), str(d).strip()) if pd.notna(d) else "—"
)
# Привязка кадровых документов 1С
absences_map = {}
if df_absences_1c is not None and not df_absences_1c.empty:
fio_col = next((c for c in ["fio_clean", "ФИО", "Сотрудник"] if c in df_absences_1c.columns), None)
reason_col = next((c for c in ["Вид_отсутствия", "Причина", "причина отсутствия"] if c in df_absences_1c.columns), None)
if fio_col and reason_col:
for _, row in df_absences_1c.iterrows():
fio = normalize_fio(str(row[fio_col]))
reason = str(row[reason_col]).strip()
if reason and reason.lower() not in ["nan", "none"]:
absences_map[fio] = reason
manual_reasons_map = {}
try:
from services.manual_absences_repo import get_active_manual_absences_for_date
target_date_val = df_res.get('Дата', pd.Series()).iloc[0] if 'Дата' in df_res.columns and not df_res.empty else None
if target_date_val:
m_records = get_active_manual_absences_for_date(str(target_date_val))
for mr in m_records:
manual_reasons_map[mr['fio_clean']] = mr['reason']
except Exception:
pass
df_res["detailed_reason"] = df_res["fio_clean"].map(manual_reasons_map).fillna("")
df_res["причина отсутствия"] = df_res["fio_clean"].map(absences_map)
df_res["Вид_отсутствия"] = df_res["причина отсутствия"]
# Исключения и белый список
exc_fios = [normalize_fio(f) for f in exceptions_cfg.get("fio", []) if f]
exc_depts = [d.strip().upper() for d in exceptions_cfg.get("departments", []) if d]
exc_pos = [p.strip().lower() for p in exceptions_cfg.get("positions", []) if p]
pos_kw = [k.strip().lower() for k in exceptions_cfg.get("position_keywords", []) if k]
whitelist_fios = [normalize_fio(f) for f in exceptions_cfg.get("include_fio", []) if f]
df_res["is_excluded"] = False
for idx, row in df_res.iterrows():
fio = row.get("fio_clean", "")
has_official_absence = pd.notna(row.get("Вид_отсутствия")) and str(row.get("Вид_отсутствия")).strip() not in ["", "nan", "None", "Исключение"]
if fio in whitelist_fios:
df_res.at[idx, "is_excluded"] = False
continue
dep = str(row.get("Подразделение", "")).upper()
pos = str(row.get("Должность", "")).lower()
is_match_exc = (fio in exc_fios or dep in exc_depts or any(d in dep for d in exc_depts) or pos in exc_pos or any(k in pos for k in pos_kw))
if is_match_exc:
df_res.at[idx, "is_excluded"] = not has_official_absence
mask_exc = (df_res["is_excluded"] == True) & (df_res["Вид_отсутствия"].isna() | (df_res["Вид_отсутствия"] == ""))
df_res.loc[mask_exc, "Вид_отсутствия"] = "Исключение"
df_res.loc[mask_exc, "причина отсутствия"] = "Исключение"
return df_res
def calculate_summary_metrics(df_merged: pd.DataFrame) -> Dict[str, Any]:
# Создаем независимую копию среза штата с собственным непрерывным индексом
df_staff_only = df_merged[~df_merged.get('not_hired_yet', False)].copy().reset_index(drop=True)
total_staff = len(df_staff_only)
is_exc_staff = df_staff_only.get('is_excluded', False) == True
is_no_pass_staff = df_staff_only.get('no_scud_pass', False) == True
came_to_office_mask = (df_staff_only["Начало_дня"].astype(str).str.strip().ne("Нет входа")) & (~is_exc_staff)
exc_without_doc_mask = (is_exc_staff) & (
df_staff_only["Вид_отсутствия"].isna() |
df_staff_only["Вид_отсутствия"].astype(str).str.strip().isin(["", "nan", "Исключение"])
)
working_in_office_count = int((came_to_office_mask | exc_without_doc_mask).sum())
df_not_working = df_staff_only[~came_to_office_mask & ~exc_without_doc_mask].copy().reset_index(drop=True)
reason_series = df_not_working["причина отсутствия"].astype(str).str.lower()
is_remote_mask = reason_series.str.contains("удален|дистанцион", regex=True, na=False)
remote_home_count = int(is_remote_mask.sum())
df_remaining_absent = df_not_working[~is_remote_mask].copy().reset_index(drop=True)
has_doc_mask = (
df_remaining_absent["причина отсутствия"].notna() &
df_remaining_absent["причина отсутствия"].ne("") &
df_remaining_absent["причина отсутствия"].ne("nan") &
(~df_remaining_absent["причина отсутствия"].astype(str).str.startswith("Исключение"))
)
official_absent_count = int(has_doc_mask.sum())
# Неизвестно: среди тех, у кого нет официального документа и кто имеет пропуск
is_no_pass_remaining = df_remaining_absent.get('no_scud_pass', False) == True
unknown = df_remaining_absent[~has_doc_mask & ~is_no_pass_remaining]
unknown_count = len(unknown)
no_pass_count = int((is_no_pass_staff & ~is_exc_staff).sum())
is_not_hired_all = df_merged.get('not_hired_yet', False) == True
is_exc_all = df_merged.get('is_excluded', False) == True
not_hired_count = int((is_not_hired_all & ~is_exc_all).sum())
return {
"total_staff": total_staff,
"working_in_office_count": working_in_office_count,
"remote_home_count": remote_home_count,
"official_absent_count": official_absent_count,
"no_pass_count": no_pass_count,
"not_hired_count": not_hired_count,
"unknown_count": unknown_count,
"unknown_list": unknown[["fio_clean", "Подразделение", "Должность"]].to_dict(orient="records") if not unknown.empty else []
}
File: ./services/scud_etl/otchet_generator.py
"""
===============================================================================
FILE: services/scud_etl/otchet_generator.py
ROLE: Генератор Детального Отчета за прошлые смены (строго по итоговому Y-снапшоту).
===============================================================================
"""
import os
import logging
from typing import Dict, Any, Optional
import pandas as pd
from config import DATE_YESTERDAY
from core.database import load_scud_from_db_by_snapshot
from services.scud_etl.pipeline import load_1c_files_for_date
from services.scud_etl.merger import merge_scud_and_1c
from services.excel_exporter import generate_detailed_excel, get_dated_reports_dir, format_date_ru
logger = logging.getLogger("OTCHET_GENERATOR")
def generate_otchet_service(
target_date: Optional[str] = None,
snapshot_id: Optional[str] = None
) -> Dict[str, Any]:
"""
Формирует детальный отчет за прошедшую смену:
- target_date: дата отчета (по умолчанию вчерашний рабочий день).
- snapshot_id: опциональный ID (по умолчанию выбирается итоговый вечерний срез Y).
"""
date_clean = str(target_date or DATE_YESTERDAY).replace('_', '.')
df_scud = load_scud_from_db_by_snapshot(date_clean, snapshot_param=snapshot_id)
if df_scud is None or df_scud.empty:
return {
"status": "error",
"message": f"Итоговый срез СКУД (Y) за {date_clean} не найден в базе данных."
}
df_staff, df_abs = load_1c_files_for_date(date_clean)
df_merged = merge_scud_and_1c(df_scud, df_staff, df_abs)
filename = f"{format_date_ru(date_clean)} отчет.xlsx"
generate_detailed_excel(df_merged, date_str=date_clean, filename=filename)
target_dir = get_dated_reports_dir(date_clean)
full_filepath = os.path.join(target_dir, filename)
return {
"status": "success",
"report_type": "OTCHET",
"date": date_clean,
"snapshot_id": snapshot_id or "AUTO_Y_FINAL",
"filename": filename,
"filepath": full_filepath,
"download_url": f"/api/v1/files/download/reports/{os.path.basename(full_filepath)}",
"total_rows": len(df_merged),
"message": f"Детальный отчет за {date_clean} успешно сформирован."
}
File: ./services/scud_etl/pipeline.py
"""
===============================================================================
FILE: services/scud_etl/pipeline.py
===============================================================================
"""
import os
import logging
from typing import Optional, Dict, Any, Tuple
import pandas as pd
from core.connection import get_connection
from core.database import load_scud_from_db_by_snapshot
from config import DATA_DIR
from services.data_loader import load_staff_data, load_absent_data
logger = logging.getLogger("SCUD_PIPELINE")
def load_best_snapshot_for_date(date_str: str, prefer_final_y: bool = False) -> Optional[pd.DataFrame]:
"""
Загружает наилучший срез СКУД за дату.
Если prefer_final_y=True — отдает предпочтение финишному Y (23:59:59).
"""
with get_connection() as conn:
cursor = conn.cursor()
target_snap_id = None
if prefer_final_y:
cursor.execute("""
SELECT snapshot_id
FROM scud_logs
WHERE log_date = ?
AND (snapshot_id LIKE 'Y%' OR snapshot_time LIKE '%23:59:59' OR snapshot_time LIKE '%22:00:00')
ORDER BY id DESC LIMIT 1
""", (date_str,))
row = cursor.fetchone()
if row:
target_snap_id = row[0]
if not target_snap_id:
cursor.execute("""
SELECT snapshot_id
FROM scud_logs
WHERE log_date = ?
ORDER BY id DESC LIMIT 1
""", (date_str,))
row = cursor.fetchone()
if row:
target_snap_id = row[0]
if not target_snap_id:
return None
return load_scud_from_db_by_snapshot(date_str, snapshot_param=target_snap_id)
def load_1c_files_for_date(date_str: str) -> Tuple[Optional[pd.DataFrame], Optional[pd.DataFrame]]:
"""
Загружает реестры штата и отсутствий 1С на указанную дату через data_loader.
"""
df_staff = load_staff_data(date_str)
df_abs = load_absent_data(date_str)
return df_staff, df_abs
File: ./services/scud_etl/sql_queries.py
"""
===============================================================================
FILE: services/scud_etl/sql_queries.py
ROLE: Изолированные SQL-шаблоны для MS SQL Server (СКУД Орион Pro).
===============================================================================
"""
# 1. Основной срез СКУД с учетом турникетов и флигеля
SQL_QUERY_TEMPLATE = r"""
DECLARE @InputDate DATE = '{target_date}';
DECLARE @TargetDate DATE = @InputDate;
DECLARE @StartDate DATETIME = CAST(@TargetDate AS DATETIME);
DECLARE @EndDate DATETIME = {end_datetime_sql};
WITH PercoPassages AS (
-- Физические факты прохода (Event = 32)
SELECT
log.HozOrgan AS EmployeeID,
log.TimeVal,
CASE
WHEN log.Mode = 1 THEN 'IN'
WHEN log.Mode = 2 THEN 'OUT'
ELSE 'OTHER'
END AS Direction
FROM pLogData log WITH (NOLOCK)
INNER JOIN pList p WITH (NOLOCK) ON log.HozOrgan = p.ID
LEFT JOIN PDivision div WITH (NOLOCK) ON p.Section = div.ID
WHERE log.TimeVal BETWEEN @StartDate AND @EndDate
AND log.HozOrgan IS NOT NULL
AND log.HozOrgan > 0
AND log.Event = 32
AND log.Mode IN (1, 2)
AND (
-- Контур 1: Левый турникет открыт для всех
log.DoorIndex = 1
OR
-- Контур 2: Правый турникет разрешен только для реестра двора
(
log.DoorIndex = 2
AND ({turnstile_filter_sql})
)
OR
-- Контур 3: Флигель 1 эт. разрешен только для реестра флигеля
(
log.DoorIndex = 23
AND ({fligel_filter_sql})
)
)
),
Passages AS (
SELECT
EmployeeID,
MIN(TimeVal) AS FirstRawEvent,
MAX(TimeVal) AS LastRawEvent,
MIN(CASE WHEN Direction = 'IN' THEN TimeVal END) AS FirstIn,
MAX(CASE WHEN Direction = 'OUT' THEN TimeVal END) AS FinalOut
FROM PercoPassages
GROUP BY EmployeeID
),
EvaluatedPassages AS (
SELECT
p.*,
CASE
WHEN p.FinalOut IS NOT NULL
AND p.FirstIn IS NOT NULL
AND p.FinalOut > DATEADD(MINUTE, 5, p.FirstIn)
THEN p.FinalOut
ELSE NULL
END AS FilteredLastOut
FROM Passages p
)
SELECT
N'ЛЕНМОРНИИПРОЕКТ' AS [Фирма],
ISNULL(CAST(div.Name AS NVARCHAR(255)), N'Без подразделения') AS [Подразделение],
LTRIM(RTRIM(
ISNULL(CAST(p.Name AS NVARCHAR(255)), N'') +
CASE WHEN p.FirstName IS NOT NULL AND CAST(p.FirstName AS NVARCHAR(255)) <> ''
THEN N' ' + CAST(p.FirstName AS NVARCHAR(255)) ELSE N'' END +
CASE WHEN p.MidName IS NOT NULL AND CAST(p.MidName AS NVARCHAR(255)) <> ''
THEN N' ' + CAST(p.MidName AS NVARCHAR(255)) ELSE N'' END
)) AS [Сотрудник],
ISNULL(CAST(post.Name AS NVARCHAR(255)), N'—') AS [Должность],
ISNULL(CAST(p.TabNumber AS NVARCHAR(50)), N'—') AS [Таб_№],
CONVERT(VARCHAR(10), @TargetDate, 104) AS [Дата],
ISNULL(CAST(CONVERT(VARCHAR(8), pass.FirstIn, 108) AS NVARCHAR(20)), N'Нет входа') AS [Начало_дня],
CASE
WHEN pass.FirstIn IS NULL AND pass.FirstRawEvent IS NOT NULL
THEN CAST(CONVERT(VARCHAR(8), pass.FirstRawEvent, 108) AS NVARCHAR(20))
ELSE N'—'
END AS [Первая_активность],
CASE
WHEN pass.FilteredLastOut IS NOT NULL
THEN CAST(CONVERT(VARCHAR(8), pass.FilteredLastOut, 108) AS NVARCHAR(20))
ELSE N'Нет выхода'
END AS [Конец_дня],
CASE
WHEN pass.EmployeeID IS NOT NULL AND (pass.FirstIn IS NOT NULL OR pass.FirstRawEvent IS NOT NULL) THEN
RIGHT('0' + CAST(DATEDIFF(MINUTE,
ISNULL(pass.FirstIn, pass.FirstRawEvent),
CASE
WHEN pass.FilteredLastOut IS NOT NULL THEN pass.FilteredLastOut
ELSE @EndDate
END) / 60 AS VARCHAR), 2) + ':' +
RIGHT('0' + CAST(DATEDIFF(MINUTE,
ISNULL(pass.FirstIn, pass.FirstRawEvent),
CASE
WHEN pass.FilteredLastOut IS NOT NULL THEN pass.FilteredLastOut
ELSE @EndDate
END) % 60 AS VARCHAR), 2)
ELSE N'00:00'
END AS [Находился_в_здании],
CASE
WHEN pass.EmployeeID IS NOT NULL THEN N'Присутствовал'
ELSE N'Отсутствовал (Нет событий)'
END AS [Статус]
FROM pList p WITH (NOLOCK)
LEFT JOIN PDivision div WITH (NOLOCK) ON p.Section = div.ID
LEFT JOIN PPost post WITH (NOLOCK) ON p.Post = post.ID
LEFT JOIN EvaluatedPassages pass ON p.ID = pass.EmployeeID
WHERE
ISNULL(p.StatusRecord, 0) = 0
AND p.DateTimeInArchive IS NULL
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT LIKE N'Аренд%'
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT IN (N'Без подразделения', N'')
AND p.Name NOT LIKE N'бр.%'
AND p.Name NOT LIKE N'Гость%'
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT IN (N'БГИ', N'КНР')
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT LIKE N'Рабоч%'
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT LIKE N'Врем%'
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT LIKE N'Практика%'
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT LIKE N'тест%'
AND ISNULL(CAST(post.Name AS NVARCHAR(255)), N'') NOT LIKE N'Практикант%'
ORDER BY p.Name ASC;
"""
# 2. Сырые события физических проходов турникетов
SQL_RAW_EVENTS_QUERY = r"""
DECLARE @InputDate DATE = '{target_date}';
DECLARE @StartDate DATETIME = CAST(@InputDate AS DATETIME);
DECLARE @EndDate DATETIME = {end_datetime_sql};
SELECT
log.TimeVal,
log.HozOrgan,
LTRIM(RTRIM(
ISNULL(CAST(p.Name AS NVARCHAR(255)), N'') +
CASE WHEN p.FirstName IS NOT NULL AND CAST(p.FirstName AS NVARCHAR(255)) <> ''
THEN N' ' + CAST(p.FirstName AS NVARCHAR(255)) ELSE N'' END +
CASE WHEN p.MidName IS NOT NULL AND CAST(p.MidName AS NVARCHAR(255)) <> ''
THEN N' ' + CAST(p.MidName AS NVARCHAR(255)) ELSE N'' END
)) AS [Сотрудник],
ISNULL(CAST(div.Name AS NVARCHAR(255)), N'Без подразделения') AS [Подразделение],
log.Event,
log.Mode,
log.DoorIndex,
CASE
WHEN log.Mode = 2 THEN 'OUT'
WHEN log.Mode = 1 THEN 'IN'
WHEN log.Event IN (2, 27, 29, 33, 55, 65) THEN 'OUT'
WHEN log.Event IN (1, 21, 26, 54, 64) THEN 'IN'
ELSE 'OTHER'
END AS Direction
FROM pLogData log WITH (NOLOCK)
INNER JOIN pList p WITH (NOLOCK) ON log.HozOrgan = p.ID
LEFT JOIN PDivision div WITH (NOLOCK) ON p.Section = div.ID
LEFT JOIN PPost post WITH (NOLOCK) ON p.Post = post.ID
WHERE log.TimeVal BETWEEN @StartDate AND @EndDate
AND log.HozOrgan IS NOT NULL
AND log.HozOrgan > 0
AND log.Event IN (28, 32)
-- ⭐️ Фильтрация сотрудников Ленморниипроект (исключение арендаторов и гостей)
AND ISNULL(p.StatusRecord, 0) = 0
AND p.DateTimeInArchive IS NULL
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT LIKE N'Аренд%'
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT IN (N'Без подразделения', N'')
AND p.Name NOT LIKE N'бр.%'
AND p.Name NOT LIKE N'Гость%'
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT IN (N'БГИ', N'КНР')
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT LIKE N'Рабоч%'
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT LIKE N'Врем%'
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT LIKE N'Практика%'
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT LIKE N'тест%'
AND ISNULL(CAST(post.Name AS NVARCHAR(255)), N'') NOT LIKE N'Практикант%'
ORDER BY log.TimeVal ASC;
"""
File: ./services/scud_etl/svodka_generator.py
"""
===============================================================================
FILE: services/scud_etl/svodka_generator.py
ROLE: Генератор Ежедневной Сводки (оперативный контроль, текущий срез).
===============================================================================
"""
import os
import logging
from typing import Dict, Any, Optional
import pandas as pd
from config import DATE_TODAY
from core.database import load_scud_from_db_by_snapshot
from services.scud_etl.pipeline import load_1c_files_for_date
from services.scud_etl.merger import merge_scud_and_1c, calculate_summary_metrics
from services.scud_etl.anomaly_detector import detect_registry_anomalies
from services.snapshots.finder import find_or_create_snapshot_for_time
from services.excel_exporter import generate_summary_excel, get_dated_reports_dir, format_date_ru
logger = logging.getLogger("SVODKA_GENERATOR")
def generate_svodka_service(
target_date: Optional[str] = None,
target_time: Optional[str] = None,
snapshot_id: Optional[str] = None
) -> Dict[str, Any]:
"""
Формирует оперативную сводку на указанную дату / время:
- target_date: дата сводки (по умолчанию сегодня).
- target_time: время среза (например '14:30').
- snapshot_id: точный ID среза.
"""
date_clean = str(target_date or DATE_TODAY).replace('_', '.')
applied_note = ""
# Если передано время, но не указан конкретный snapshot_id — ищем ближайший или запрашиваем экспорт
if target_time and not snapshot_id:
found_id, note = find_or_create_snapshot_for_time(date_clean, target_time, allow_ondemand_export=True)
snapshot_id = found_id
applied_note = note
if note:
logger.info(note)
df_scud = load_scud_from_db_by_snapshot(date_clean, snapshot_param=snapshot_id)
if df_scud is None or df_scud.empty:
return {
"status": "error",
"message": f"Срез СКУД за {date_clean} ({applied_note or snapshot_id or 'последний доступный'}) не найден в базе."
}
df_staff, df_abs = load_1c_files_for_date(date_clean)
df_merged = merge_scud_and_1c(df_scud, df_staff, df_abs)
metrics = calculate_summary_metrics(df_merged)
anomalies = detect_registry_anomalies(df_merged, df_raw_scud=df_scud)
# Добавляем суффикс времени в имя файла, если сводка строилась на точный срез
time_suffix = f" на {target_time.replace(':', '-')}" if target_time else ""
filename = f"{format_date_ru(date_clean)} сводка{time_suffix}.xlsx"
generate_summary_excel(df_merged, date_str=date_clean, filename=filename)
target_dir = get_dated_reports_dir(date_clean)
full_filepath = os.path.join(target_dir, filename)
return {
"status": "success",
"report_type": "SVODKA",
"date": date_clean,
"target_time": target_time,
"snapshot_id": snapshot_id or "AUTO_LATEST",
"filename": filename,
"filepath": full_filepath,
"download_url": f"/api/v1/files/download/reports/{os.path.basename(full_filepath)}",
"metrics": metrics,
"anomalies_count": len(anomalies),
"note": applied_note,
"message": f"Ежедневная сводка на {date_clean} {target_time or ''} успешно сформирована."
}
File: ./services/scud_export.py
"""
===============================================================================
FILE: services/scud_export.py
ROLE: Прямой экспорт данных СКУД Орион (MS SQL) в SQLite и чистый Excel (XlsxWriter).
Корректная фильтрация транзитных проходов турникетов парковки и двора.
Учет только левого PERCo (DoorIndex = 1), правого PERCo (DoorIndex = 2)
и входа через Флигель (DoorIndex = 23).
===============================================================================
"""
import argparse
import logging
import os
import sys
import warnings
from datetime import datetime, timedelta
CURRENT_DIR = os.path.dirname(os.path.abspath(__file__))
ROOT_DIR = os.path.abspath(os.path.join(CURRENT_DIR, ".."))
if ROOT_DIR not in sys.path:
sys.path.insert(0, ROOT_DIR)
import pandas as pd
import pyodbc
import xlsxwriter
from services.scud_etl.sql_queries import SQL_QUERY_TEMPLATE, SQL_RAW_EVENTS_QUERY
from config import SCUD_DIR, clean_scud_fio_light, load_exceptions
from core.database import save_scud_to_db, has_scud_logs_for_date, has_yesterday_final_snapshot
from core.repositories.scud_repo import save_raw_events_to_db
warnings.filterwarnings("ignore", message="pandas only supports SQLAlchemy connectable")
SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
LOG_DIR = os.path.join(SCRIPT_DIR, "..", "logs")
os.makedirs(LOG_DIR, exist_ok=True)
TODAY_DATE_STR = datetime.now().strftime("%d.%m.%Y")
LOG_FILE = os.path.join(LOG_DIR, f"export_{TODAY_DATE_STR}.log")
logger = logging.getLogger("scud_export")
logger.setLevel(logging.INFO)
logger.propagate = False
if not logger.handlers:
try:
_file_handler = logging.FileHandler(LOG_FILE, encoding="utf-8")
_formatter = logging.Formatter("[%(asctime)s] [%(levelname)s] %(message)s", datefmt="%Y-%m-%d %H:%M:%S")
_file_handler.setFormatter(_formatter)
logger.addHandler(_file_handler)
except (PermissionError, OSError) as e:
sys.stderr.write(f"Предупреждение: невозможно создать лог-файл {LOG_FILE}: {e}\n")
_console_handler = logging.StreamHandler(sys.stdout)
_formatter = logging.Formatter("[%(asctime)s] [%(levelname)s] %(message)s", datefmt="%Y-%m-%d %H:%M:%S")
_console_handler.setFormatter(_formatter)
logger.addHandler(_console_handler)
def log(message: str, level: str = "INFO"):
level_map = {
"INFO": logging.INFO,
"ERROR": logging.ERROR,
"WARNING": logging.WARNING,
"SUCCESS": logging.INFO,
}
if level == "SUCCESS":
logger.info(f"[SUCCESS] {message}")
else:
logger.log(level_map.get(level, logging.INFO), message)
SERVER_NAME = r"172.16.200.147\SQL"
DATABASE_NAME = "Orion-14.01.21-1"
SQL_USER = "sa"
SQL_PASSWORD = "123456"
ODBC_DRIVER = "ODBC Driver 18 for SQL Server"
SQL_QUERY_TEMPLATE = r"""
DECLARE @InputDate DATE = '{target_date}';
DECLARE @TargetDate DATE = @InputDate;
DECLARE @StartDate DATETIME = CAST(@TargetDate AS DATETIME);
DECLARE @EndDate DATETIME = {end_datetime_sql};
WITH PercoPassages AS (
-- Физические факты прохода (Event = 32)
SELECT
log.HozOrgan AS EmployeeID,
log.TimeVal,
CASE
WHEN log.Mode = 1 THEN 'IN'
WHEN log.Mode = 2 THEN 'OUT'
ELSE 'OTHER'
END AS Direction
FROM pLogData log WITH (NOLOCK)
INNER JOIN pList p WITH (NOLOCK) ON log.HozOrgan = p.ID
LEFT JOIN PDivision div WITH (NOLOCK) ON p.Section = div.ID
WHERE log.TimeVal BETWEEN @StartDate AND @EndDate
AND log.HozOrgan IS NOT NULL
AND log.HozOrgan > 0
AND log.Event = 32
AND log.Mode IN (1, 2)
AND (
-- Контур 1: Левый турникет открыт для всех
log.DoorIndex = 1
OR
-- Контур 2: Правый турникет разрешен только для реестра двора
(
log.DoorIndex = 2
AND ({turnstile_filter_sql})
)
OR
-- Контур 3: Флигель 1 эт. разрешен только для реестра флигеля
(
log.DoorIndex = 23
AND ({fligel_filter_sql})
)
)
),
Passages AS (
SELECT
EmployeeID,
MIN(TimeVal) AS FirstRawEvent,
MAX(TimeVal) AS LastRawEvent,
MIN(CASE WHEN Direction = 'IN' THEN TimeVal END) AS FirstIn,
MAX(CASE WHEN Direction = 'OUT' THEN TimeVal END) AS FinalOut
FROM PercoPassages
GROUP BY EmployeeID
),
EvaluatedPassages AS (
SELECT
p.*,
CASE
WHEN p.FinalOut IS NOT NULL
AND p.FirstIn IS NOT NULL
AND p.FinalOut > DATEADD(MINUTE, 5, p.FirstIn)
THEN p.FinalOut
ELSE NULL
END AS FilteredLastOut
FROM Passages p
)
SELECT
N'ЛЕНМОРНИИПРОЕКТ' AS [Фирма],
ISNULL(CAST(div.Name AS NVARCHAR(255)), N'Без подразделения') AS [Подразделение],
LTRIM(RTRIM(
ISNULL(CAST(p.Name AS NVARCHAR(255)), N'') +
CASE WHEN p.FirstName IS NOT NULL AND CAST(p.FirstName AS NVARCHAR(255)) <> ''
THEN N' ' + CAST(p.FirstName AS NVARCHAR(255)) ELSE N'' END +
CASE WHEN p.MidName IS NOT NULL AND CAST(p.MidName AS NVARCHAR(255)) <> ''
THEN N' ' + CAST(p.MidName AS NVARCHAR(255)) ELSE N'' END
)) AS [Сотрудник],
ISNULL(CAST(post.Name AS NVARCHAR(255)), N'—') AS [Должность],
ISNULL(CAST(p.TabNumber AS NVARCHAR(50)), N'—') AS [Таб_№],
CONVERT(VARCHAR(10), @TargetDate, 104) AS [Дата],
ISNULL(CAST(CONVERT(VARCHAR(8), pass.FirstIn, 108) AS NVARCHAR(20)), N'Нет входа') AS [Начало_дня],
CASE
WHEN pass.FirstIn IS NULL AND pass.FirstRawEvent IS NOT NULL
THEN CAST(CONVERT(VARCHAR(8), pass.FirstRawEvent, 108) AS NVARCHAR(20))
ELSE N'—'
END AS [Первая_активность],
CASE
WHEN pass.FilteredLastOut IS NOT NULL
THEN CAST(CONVERT(VARCHAR(8), pass.FilteredLastOut, 108) AS NVARCHAR(20))
ELSE N'Нет выхода'
END AS [Конец_дня],
CASE
WHEN pass.EmployeeID IS NOT NULL AND (pass.FirstIn IS NOT NULL OR pass.FirstRawEvent IS NOT NULL) THEN
RIGHT('0' + CAST(DATEDIFF(MINUTE,
ISNULL(pass.FirstIn, pass.FirstRawEvent),
CASE
WHEN pass.FilteredLastOut IS NOT NULL THEN pass.FilteredLastOut
ELSE @EndDate
END) / 60 AS VARCHAR), 2) + ':' +
RIGHT('0' + CAST(DATEDIFF(MINUTE,
ISNULL(pass.FirstIn, pass.FirstRawEvent),
CASE
WHEN pass.FilteredLastOut IS NOT NULL THEN pass.FilteredLastOut
ELSE @EndDate
END) % 60 AS VARCHAR), 2)
ELSE N'00:00'
END AS [Находился_в_здании],
CASE
WHEN pass.EmployeeID IS NOT NULL THEN N'Присутствовал'
ELSE N'Отсутствовал (Нет событий)'
END AS [Статус]
FROM pList p WITH (NOLOCK)
LEFT JOIN PDivision div WITH (NOLOCK) ON p.Section = div.ID
LEFT JOIN PPost post WITH (NOLOCK) ON p.Post = post.ID
LEFT JOIN EvaluatedPassages pass ON p.ID = pass.EmployeeID
WHERE
ISNULL(p.StatusRecord, 0) = 0
AND p.DateTimeInArchive IS NULL
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT LIKE N'Аренд%'
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT IN (N'Без подразделения', N'')
AND p.Name NOT LIKE N'бр.%'
AND p.Name NOT LIKE N'Гость%'
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT IN (N'БГИ', N'КНР')
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT LIKE N'Рабоч%'
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT LIKE N'Врем%'
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT LIKE N'Практика%'
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT LIKE N'тест%'
AND ISNULL(CAST(post.Name AS NVARCHAR(255)), N'') NOT LIKE N'Практикант%'
ORDER BY p.Name ASC;
"""
SQL_RAW_EVENTS_QUERY = r"""
DECLARE @InputDate DATE = '{target_date}';
DECLARE @StartDate DATETIME = CAST(@InputDate AS DATETIME);
DECLARE @EndDate DATETIME = {end_datetime_sql};
SELECT
log.TimeVal,
log.HozOrgan,
LTRIM(RTRIM(
ISNULL(CAST(p.Name AS NVARCHAR(255)), N'') +
CASE WHEN p.FirstName IS NOT NULL AND CAST(p.FirstName AS NVARCHAR(255)) <> ''
THEN N' ' + CAST(p.FirstName AS NVARCHAR(255)) ELSE N'' END +
CASE WHEN p.MidName IS NOT NULL AND CAST(p.MidName AS NVARCHAR(255)) <> ''
THEN N' ' + CAST(p.MidName AS NVARCHAR(255)) ELSE N'' END
)) AS [Сотрудник],
ISNULL(CAST(div.Name AS NVARCHAR(255)), N'Без подразделения') AS [Подразделение],
log.Event,
log.Mode,
log.DoorIndex,
CASE
WHEN log.Mode = 2 THEN 'OUT'
WHEN log.Mode = 1 THEN 'IN'
WHEN log.Event IN (2, 27, 29, 33, 55, 65) THEN 'OUT'
WHEN log.Event IN (1, 21, 26, 54, 64) THEN 'IN'
ELSE 'OTHER'
END AS Direction
FROM pLogData log WITH (NOLOCK)
INNER JOIN pList p WITH (NOLOCK) ON log.HozOrgan = p.ID
LEFT JOIN PDivision div WITH (NOLOCK) ON p.Section = div.ID
LEFT JOIN PPost post WITH (NOLOCK) ON p.Post = post.ID
WHERE log.TimeVal BETWEEN @StartDate AND @EndDate
AND log.HozOrgan IS NOT NULL
AND log.HozOrgan > 0
AND log.Event IN (28, 32)
-- ⭐️ Фильтрация сотрудников Ленморниипроект (исключение арендаторов и гостей)
AND ISNULL(p.StatusRecord, 0) = 0
AND p.DateTimeInArchive IS NULL
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT LIKE N'Аренд%'
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT IN (N'Без подразделения', N'')
AND p.Name NOT LIKE N'бр.%'
AND p.Name NOT LIKE N'Гость%'
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT IN (N'БГИ', N'КНР')
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT LIKE N'Рабоч%'
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT LIKE N'Врем%'
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT LIKE N'Практика%'
AND ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') NOT LIKE N'тест%'
AND ISNULL(CAST(post.Name AS NVARCHAR(255)), N'') NOT LIKE N'Практикант%'
ORDER BY log.TimeVal ASC;
"""
def save_df_to_clean_excel(df: pd.DataFrame, file_path: str, sheet_name: str = "Отчет"):
workbook = xlsxwriter.Workbook(file_path, {'constant_memory': False})
worksheet = workbook.add_worksheet(sheet_name)
fmt_header = workbook.add_format({
'bold': True,
'bg_color': '#D9E1F2',
'border': 1,
'border_color': '#D3D3D3',
'align': 'center',
'valign': 'vcenter',
'font_name': 'Calibri',
'font_size': 11
})
fmt_cell = workbook.add_format({
'border': 1,
'border_color': '#D3D3D3',
'valign': 'vcenter',
'align': 'left',
'font_name': 'Calibri',
'font_size': 11
})
headers = list(df.columns)
col_widths = [len(str(h)) for h in headers]
for col_idx, header in enumerate(headers):
worksheet.write(0, col_idx, str(header), fmt_header)
for row_idx, row_values in enumerate(df.values, start=1):
for col_idx, val in enumerate(row_values):
if pd.isna(val) or val is None:
val_str = ""
elif isinstance(val, bool):
val_str = "Да" if val else "Нет"
else:
val_str = str(val)
worksheet.write(row_idx, col_idx, val_str, fmt_cell)
if len(val_str) > col_widths[col_idx]:
col_widths[col_idx] = len(val_str)
for col_idx, width in enumerate(col_widths):
worksheet.set_column(col_idx, col_idx, min(max(width + 3, 10), 45))
workbook.close()
def get_targets(input_date: str | None, input_time: str | None = None):
targets = []
if input_date:
try:
parsed = datetime.strptime(input_date.replace('_', '.'), "%d.%m.%Y").date()
targets.append({"name": "Указанная дата", "date": parsed, "target_time": input_time})
except ValueError:
log(f"ОШИБКА: Неверный формат даты '{input_date}'. Используйте ДД.ММ.ГГГГ", "ERROR")
sys.exit(1)
else:
now = datetime.now()
yesterday = (now - timedelta(days=3 if now.weekday() == 0 else 1)).date()
today = now.date()
targets.append({"name": "Вчера", "date": yesterday, "target_time": None})
targets.append({"name": "Сегодня", "date": today, "target_time": None})
return targets
def run_export(input_date: str | None = None, input_time: str | None = None, save_xlsx: bool = True, debug: bool = False):
if debug:
logger.setLevel(logging.DEBUG)
log("=== ВКЛЮЧЕН РЕЖИМ ОТЛАДКИ (DEBUG MODE) ===", "WARNING")
log("=== [ЭТАП 0] Выгрузка свежих данных СКУД напрямую из БД Орион ===")
os.makedirs(SCUD_DIR, exist_ok=True)
targets = get_targets(input_date, input_time)
conn_str = (
f"DRIVER={{{ODBC_DRIVER}}};"
f"SERVER={SERVER_NAME};"
f"DATABASE={DATABASE_NAME};"
f"UID={SQL_USER};"
f"PWD={SQL_PASSWORD};"
f"TrustServerCertificate=yes;"
f"Encrypt=no;"
)
success = True
for target in targets:
processing_date = target["date"]
processing_date_str = processing_date.strftime("%d.%m.%Y")
period_label = target["name"]
target_time = target["target_time"]
today_date = datetime.now().date()
is_past_day = (processing_date < today_date) or (period_label == "Вчера")
if is_past_day and not target_time and has_yesterday_final_snapshot(processing_date_str):
log(f"[ℹ️] День ({processing_date_str}) уже зафиксирован финишным снапшотом _FINAL. Пропускаем.")
continue
exc_data = load_exceptions()
# 1. Формируем фильтр правого турникета (DoorIndex = 2)
t_fios = [f.replace("'", "''") for f in exc_data.get('turnstile_fio', []) if f]
t_depts = [d.replace("'", "''") for d in exc_data.get('turnstile_departments', []) if d]
full_fio_sql = (
"LTRIM(RTRIM("
"ISNULL(CAST(p.Name AS NVARCHAR(255)), N'') + "
"CASE WHEN p.FirstName IS NOT NULL AND CAST(p.FirstName AS NVARCHAR(255)) <> '' THEN N' ' + CAST(p.FirstName AS NVARCHAR(255)) ELSE N'' END + "
"CASE WHEN p.MidName IS NOT NULL AND CAST(p.MidName AS NVARCHAR(255)) <> '' THEN N' ' + CAST(p.MidName AS NVARCHAR(255)) ELSE N'' END"
"))"
)
conditions = []
if t_fios:
fio_in = ", ".join([f"N'{f}'" for f in t_fios])
conditions.append(f"{full_fio_sql} IN ({fio_in})")
if t_depts:
dept_in = ", ".join([f"N'{d}'" for d in t_depts])
conditions.append(f"ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') IN ({dept_in})")
turnstile_filter_sql = " OR ".join(conditions) if conditions else "1 = 0"
# 1.1 Формируем фильтр Флигеля (DoorIndex = 23)
fl_fios = [f.replace("'", "''") for f in exc_data.get('fligel_fio', []) if f]
fl_depts = [d.replace("'", "''") for d in exc_data.get('fligel_departments', []) if d]
fl_conditions = []
if fl_fios:
fl_fio_in = ", ".join([f"N'{f}'" for f in fl_fios])
fl_conditions.append(f"{full_fio_sql} IN ({fl_fio_in})")
if fl_depts:
fl_dept_in = ", ".join([f"N'{d}'" for d in fl_depts])
fl_conditions.append(f"ISNULL(CAST(div.Name AS NVARCHAR(255)), N'') IN ({fl_dept_in})")
fligel_filter_sql = " OR ".join(fl_conditions) if fl_conditions else "1 = 0"
# 2. Безопасное математическое определение @EndDate через DATEADD
if target_time:
t_clean = target_time.strip()
t_parts = t_clean.split(":")
h = int(t_parts[0])
m = int(t_parts[1]) if len(t_parts) > 1 else 0
s = int(t_parts[2]) if len(t_parts) > 2 else 0
snapshot_time = f"{processing_date.strftime('%Y-%m-%d')} {h:02d}:{m:02d}:{s:02d}"
end_datetime_sql = f"DATEADD(SECOND, {s}, DATEADD(MINUTE, {m}, DATEADD(HOUR, {h}, @StartDate)))"
is_final = False
elif is_past_day:
snapshot_time = f"{processing_date.strftime('%Y-%m-%d')} 23:59:59"
end_datetime_sql = "DATEADD(SECOND, -1, DATEADD(DAY, 1, @StartDate))"
is_final = True
else:
snapshot_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
end_datetime_sql = "GETDATE()"
is_final = False
log(f"--- Обработка периода: {period_label} ({processing_date_str}) --- [Срез: {snapshot_time}]")
sql_query = SQL_QUERY_TEMPLATE.format(
target_date=processing_date.strftime("%Y-%m-%d"),
end_datetime_sql=end_datetime_sql,
turnstile_filter_sql=turnstile_filter_sql,
fligel_filter_sql=fligel_filter_sql
)
connection = None
try:
connection = pyodbc.connect(conn_str, timeout=120)
df = pd.read_sql(sql_query, connection)
log(f"База вернула {len(df)} строк за {processing_date_str}")
if len(df) > 0:
df['fio_clean'] = df['Сотрудник'].apply(clean_scud_fio_light)
df['anomaly_flag'] = 'NONE'
mask_anomaly = (df['Начало_дня'] == 'Нет входа') & (df['Первая_активность'] != '—')
df.loc[mask_anomaly, 'anomaly_flag'] = 'ANOMALY_NO_IN_HAS_ACTIVITY'
df['Пришел'] = df['Статус'].str.contains('Присутствовал', case=False, na=False) & (~mask_anomaly)
save_scud_to_db(df, processing_date_str, snapshot_time=snapshot_time, is_yesterday=is_final)
raw_sql = SQL_RAW_EVENTS_QUERY.format(
target_date=processing_date.strftime("%Y-%m-%d"),
end_datetime_sql=end_datetime_sql
)
df_raw = pd.read_sql(raw_sql, connection)
if len(df_raw) > 0:
df_raw['fio_clean'] = df_raw['Сотрудник'].apply(clean_scud_fio_light)
inserted_count = save_raw_events_to_db(df_raw, processing_date_str)
log(f"[✓] В scud_events_raw сохранено {inserted_count} сырых событий за {processing_date_str}!", "SUCCESS")
log(f"[✓] Записи за {processing_date_str} успешно сохранены в SQLite!", "SUCCESS")
if save_xlsx:
file_name = f"Сотрудники_{processing_date_str}.xlsx"
file_path = os.path.join(SCUD_DIR, file_name)
if os.path.exists(file_path):
try:
os.remove(file_path)
except OSError as e:
log(f"ОШИБКА при удалении старого файла {file_name}: {e}", "ERROR")
save_df_to_clean_excel(df, file_path, sheet_name="Отчет")
log(f"[✓] Успешно экспортирован файл: data/scud/{file_name}", "SUCCESS")
else:
log(f"Запрос за {processing_date_str} вернул 0 строк.", "WARNING")
except Exception as e:
log(f"🛑 ОШИБКА выгрузки СКУД за {processing_date_str}: {e}", "ERROR")
success = False
finally:
if connection is not None:
connection.close()
log("=== Выгрузка СКУД завершена ===\n")
return success
if __name__ == "__main__":
parser = argparse.ArgumentParser()
parser.add_argument("--date", dest="input_date", default=None, help="Дата среза (ДД.ММ.ГГГГ)")
parser.add_argument("--time", dest="input_time", default=None, help="Время среза (ЧЧ:ММ)")
parser.add_argument("-d", "--debug", action="store_true")
parser.add_argument("--no-xlsx", dest="save_xlsx", action="store_false", default=True)
args = parser.parse_args()
run_export(args.input_date, input_time=args.input_time, save_xlsx=args.save_xlsx, debug=args.debug)
File: ./services/share_copier.py
"""
===============================================================================
FILE: services/share_copier.py
ROLE: Синхронизация файлов 1С (Штат и Отсутствия) с сетевой шары в data/1c/.
===============================================================================
"""
import os
import shutil
from config import DATE_TODAY, DATE_YESTERDAY, ZUP_1C_DIR, SHARE_1C_DIR
def find_file_strictly_on_share(prefix, date_str):
"""
Ищет файл со строгой привязкой ТОЛЬКО к сетевой шаре SHARE_1C_DIR,
не обращаясь к локальным папкам.
"""
if not os.path.exists(SHARE_1C_DIR):
return None
date_dots = date_str
date_underscores = date_str.replace('.', '_')
try:
for f in os.listdir(SHARE_1C_DIR):
if f.endswith('.xlsx') or f.endswith('.csv'):
if f.lower().startswith(prefix.lower()):
if date_dots in f or date_underscores in f:
return os.path.join(SHARE_1C_DIR, f)
except Exception as e:
print(f" [⚠️] Ошибка чтения сетевой шары: {e}")
return None
def copy_1c_files_from_share():
r"""
Копирует свежие файлы 1С (Штат и Отсутствия) за Сегодня и Вчера
из сетевой шары \\storage\SCUD\Обмен\Штат в локальную папку data/1c/
"""
print(f"[0.5/5] Проверка и копирование файлов 1С с шары: {SHARE_1C_DIR}...")
if not os.path.exists(SHARE_1C_DIR):
print(f"[⚠️] Сетевой шар недоступен или путь не найден: {SHARE_1C_DIR}")
print(" Используем ранее сохраненные локальные файлы из data/1c/\n")
return False
dates_to_copy = [DATE_TODAY, DATE_YESTERDAY]
prefixes = ["Штат", "Отсутствия"]
copied_count = 0
os.makedirs(ZUP_1C_DIR, exist_ok=True)
for d_str in dates_to_copy:
for prefix in prefixes:
remote_file = find_file_strictly_on_share(prefix, d_str)
if remote_file and os.path.exists(remote_file):
filename = os.path.basename(remote_file)
local_target_path = os.path.join(ZUP_1C_DIR, filename)
try:
# shutil.copyfile копирует только содержимое потока байтов
# без попыток изменить POSIX-права/атрибуты (chmod) на CIFS/SMB шаре
shutil.copyfile(remote_file, local_target_path)
print(f" [✓] Успешно скопирован с шары: {filename} -> data/1c/")
copied_count += 1
except Exception as e:
print(f" [⚠️] Ошибка копирования {filename}: {e}")
else:
d_fmt = d_str.replace('.', '_')
print(f" [ℹ️] На сетевой шаре отсутствует {prefix} за {d_str} ({prefix}_{d_fmt}.xlsx)")
if copied_count > 0:
print(f"[✓] Успешно скопировано файлов с сетевой шары: {copied_count} шт.\n")
else:
print("[ℹ️] Новых файлов за указанные даты на сетевой шаре не обнаружено.\n")
return True
File: ./services/snapshots/finder.py
"""
===============================================================================
FILE: services/snapshots/finder.py
ROLE: Поиск ближайшего снапшота в SQLite (Smart Snap-to-Grid) и On-Demand экспорт.
===============================================================================
"""
import logging
from datetime import datetime
from typing import Optional, Tuple
from core.connection import get_connection
from services.scud_export import run_export
logger = logging.getLogger("SNAPSHOT_FINDER")
def find_or_create_snapshot_for_time(
target_date_str: str,
target_time_str: str,
tolerance_minutes: int = 20,
allow_ondemand_export: bool = True
) -> Tuple[Optional[str], str]:
"""
Ищет ближайший срез за указанную дату и время (±tolerance_minutes).
Если не найден и allow_ondemand_export=True — запрашивает выгрузку из MS SQL на это время.
Возвращает: (snapshot_id, human_message)
"""
date_clean = target_date_str.replace('_', '.')
dt_target = datetime.strptime(f"{date_clean} {target_time_str}", "%d.%m.%Y %H:%M")
# 1. Поиск существующих снапшотов за эту дату в SQLite
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("""
SELECT DISTINCT snapshot_id, snapshot_time
FROM scud_logs
WHERE log_date = ? AND snapshot_time IS NOT NULL
""", (date_clean,))
rows = cursor.fetchall()
best_snapshot = None
min_diff_seconds = float('inf')
for snap_id, snap_time_str in rows:
try:
# Формат в базе: YYYY-MM-DD HH:MM:SS или DD.MM.YYYY HH:MM:SS
raw_time = str(snap_time_str).strip()
if '.' in raw_time.split()[0]:
dt_snap = datetime.strptime(raw_time, "%d.%m.%Y %H:%M:%S")
else:
dt_snap = datetime.strptime(raw_time, "%Y-%m-%d %H:%M:%S")
diff = abs((dt_snap - dt_target).total_seconds())
if diff < min_diff_seconds:
min_diff_seconds = diff
best_snapshot = (snap_id, snap_time_str, dt_snap)
except Exception:
continue
# Если найден срез в пределах допуска (по умолчанию 20 минут)
if best_snapshot and min_diff_seconds <= (tolerance_minutes * 60):
snap_id, snap_time, dt_s = best_snapshot
diff_mins = round(min_diff_seconds / 60)
return snap_id, f"Использован готовый срез {snap_id} за {dt_s.strftime('%H:%M')} (разница {diff_mins} мин)."
# 2. Если срез не найден и разрешен On-Demand экспорт из MS SQL Орион
if allow_ondemand_export:
logger.info(f"Снапшот на {date_clean} {target_time_str} не найден в SQLite. Запуск прямого среза из MS SQL...")
run_export(
input_date=date_clean,
debug=False,
save_xlsx=True
)
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("""
SELECT snapshot_id
FROM scud_logs
WHERE log_date = ?
ORDER BY id DESC LIMIT 1
""", (date_clean,))
row = cursor.fetchone()
if row:
return row[0], f"Создан новый срез {row[0]} из MS SQL на {target_time_str}."
return None, f"Срез на {date_clean} {target_time_str} не найден."
File: ./services/snapshots/retention.py
"""
===============================================================================
FILE: services/snapshots/retention.py
ROLE: Политика ротации и очистки промежуточных почасовых срезов в SQLite.
===============================================================================
"""
import logging
from datetime import datetime, timedelta
from typing import List, Tuple
from core.connection import get_connection
logger = logging.getLogger("SNAPSHOT_RETENTION")
def cleanup_old_intermediate_snapshots(days_to_keep_all: int = 2) -> int:
"""
Очищает промежуточные дневные срезы старше days_to_keep_all дней.
Правило сохранения для архивных дней:
- Сохраняется срез Y (23:59:59).
- Сохраняется полуденный срез (ближайший к 13:00).
- Все остальные промежуточные снапшоты удаляются из scud_logs.
Возвращает количество удаленных записей.
"""
cutoff_date = datetime.now() - timedelta(days=days_to_keep_all)
cutoff_str = cutoff_date.strftime("%Y-%m-%d")
deleted_rows_count = 0
with get_connection() as conn:
cursor = conn.cursor()
# 1. Получаем список всех уникальных дат старше cutoff_date
cursor.execute("""
SELECT DISTINCT log_date
FROM scud_logs
WHERE snapshot_time IS NOT NULL
""")
all_dates = [row[0] for row in cursor.fetchall()]
for raw_date in all_dates:
try:
# Преобразуем формат ДД.ММ.ГГГГ в объект даты
d_clean = raw_date.replace('_', '.')
dt_obj = datetime.strptime(d_clean, "%d.%m.%Y")
if dt_obj >= cutoff_date:
continue # Свежие дни не трогаем — там хранятся все срезы
# 2. Для архивной даты ищем все снапшоты
cursor.execute("""
SELECT DISTINCT snapshot_id, snapshot_time
FROM scud_logs
WHERE log_date = ?
""", (raw_date,))
snapshots = cursor.fetchall()
if len(snapshots) <= 2:
continue # Если и так 1-2 среза, ничего чистить не нужно
keep_ids = set()
# Ищем финальный срез Y / 23:59:59 / 22:00:00
for s_id, s_time in snapshots:
s_id_str = str(s_id)
s_time_str = str(s_time)
if s_id_str.startswith('Y') or '23:59:59' in s_time_str or '22:00:00' in s_time_str:
keep_ids.add(s_id)
break
# Ищем полуденный срез (ближайший к 13:00)
noon_target = datetime.strptime(f"{d_clean} 13:00:00", "%d.%m.%Y %H:%M:%S")
best_noon_id = None
min_noon_diff = float('inf')
for s_id, s_time in snapshots:
if s_id in keep_ids:
continue
try:
raw_t = str(s_time).strip()
if '.' in raw_t.split()[0]:
dt_s = datetime.strptime(raw_t, "%d.%m.%Y %H:%M:%S")
else:
dt_s = datetime.strptime(raw_t, "%Y-%m-%d %H:%M:%S")
diff = abs((dt_s - noon_target).total_seconds())
if diff < min_noon_diff:
min_noon_diff = diff
best_noon_id = s_id
except Exception:
continue
if best_noon_id:
keep_ids.add(best_noon_id)
# 3. Все снапшоты, не попавшие в keep_ids, удаляем
to_delete_ids = [s_id for s_id, _ in snapshots if s_id not in keep_ids]
for del_id in to_delete_ids:
cursor.execute("DELETE FROM scud_logs WHERE snapshot_id = ?", (del_id,))
deleted_rows_count += cursor.rowcount
logger.info(f"Удален промежуточный архивный срез {del_id} за {raw_date}")
except Exception as e:
logger.warning(f"Ошибка при обработке ротации за дату {raw_date}: {e}")
conn.commit()
if deleted_rows_count > 0:
logger.info(f"Ротация завершена: удалено {deleted_rows_count} строк промежуточных срезов.")
return deleted_rows_count
def purge_day_intermediate_snapshots(date_str: str) -> int:
"""
Удаляет все промежуточные дневные срезы за указанную дату,
оставляя нетронутым итоговый вечерний срез Y..._FINAL.
"""
clean_date = date_str.replace('_', '.')
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("""
DELETE FROM scud_logs
WHERE log_date = ?
AND snapshot_id NOT LIKE 'Y%'
AND snapshot_id NOT LIKE '%_FINAL%'
""", (clean_date,))
deleted = cursor.rowcount
conn.commit()
if deleted > 0:
logger.info(f"[Retention] Удалено {deleted} строк промежуточных срезов за {clean_date}. Сохранен только финишный Y.")
return deleted
File: ./services/snapshots/service.py
"""
===============================================================================
FILE: services/snapshots/service.py
PROJECT: SCUD Orion AI (Unified Architecture)
MODULE: services / snapshots
ROLE: Бизнес-логика срезов СКУД (выборка, отображение времени, удаление).
AI-CONTEXT-ANCHORS:
- ANCHOR[SNAPSHOT_GET_REGISTRY]: Выборка срезов с разметкой защищенных Y-снапшотов.
- ANCHOR[SNAPSHOT_DELETE_SAFE]: Безопасное удаление дневных срезов с защитой итоговых.
===============================================================================
"""
from typing import Dict, Any, Optional, List
from core.connection import get_connection
from core.repositories.scud_repo import get_available_snapshots, delete_snapshot_by_id
# ANCHOR[SNAPSHOT_GET_REGISTRY]
def get_snapshots_registry(date_str: Optional[str] = None) -> Dict[str, Any]:
"""
Возвращает реестр снапшотов.
В поле snapshot_time объединяет время среза и фактическое время создания снапшота.
"""
clean_date = date_str.strip() if date_str else ""
rows = get_available_snapshots(date_str=clean_date if clean_date else None)
snapshots = []
for r in rows:
snap_id = r[0]
log_date = r[1]
snap_time = r[2]
rec_count = r[3]
created_at = r[4] if len(r) > 4 else None
slice_time_str = snap_time.split()[1] if snap_time and " " in snap_time else snap_time
created_str = ""
if created_at and " " in str(created_at):
c_date, c_time = str(created_at).split()[:2]
c_parts = c_date.split("-")
c_date_fmt = f"{c_parts[2]}.{c_parts[1]}" if len(c_parts) == 3 else c_date
created_str = f" · создан {c_date_fmt} {c_time[:5]}"
display_label = f"Срез {slice_time_str}{created_str}"
snapshots.append({
"snapshot_id": snap_id,
"log_date": log_date,
"snapshot_time": display_label,
"record_count": rec_count,
"is_final": str(snap_id).startswith("Y") or "_FINAL" in str(snap_id)
})
return {
"query_date": clean_date or "все",
"snapshots_count": len(snapshots),
"snapshots": snapshots
}
# ANCHOR[SNAPSHOT_DELETE_SAFE]
def delete_snapshots_safely(snapshot_ids: List[str]) -> Dict[str, Any]:
"""
Удаляет выбранные дневные снапшоты.
Итоговые вечерние срезы с префиксом 'Y' гарантированно защищены от удаления.
"""
if not snapshot_ids:
return {"status": "error", "message": "Не указаны ID снапшотов для удаления."}
safe_ids = [str(s).strip() for s in snapshot_ids if s and not str(s).strip().startswith("Y")]
if not safe_ids:
return {"status": "error", "message": "⚠️ Итоговый срез Y защищен от удаления. Выберите дневные снапшоты."}
with get_connection() as conn:
cursor = conn.cursor()
placeholders = ",".join(["?"] * len(safe_ids))
cursor.execute(f"DELETE FROM scud_logs WHERE snapshot_id IN ({placeholders})", safe_ids)
deleted_count = cursor.rowcount
conn.commit()
return {
"status": "success",
"deleted_count": deleted_count,
"deleted_ids": safe_ids,
"message": f"Успешно удалено снапшотов: {len(safe_ids)} шт."
}
File: ./services/tasks/exporter.py
"""
===============================================================================
FILE: services/tasks/exporter.py
PROJECT: SCUD Orion AI (Unified Architecture)
MODULE: services / tasks
ROLE: Экспорт задач в чистый Markdown и генерация прямой ссылки на скачивание.
===============================================================================
"""
import os
import uuid
from typing import Dict, Any, Optional
from config import BASE_DIR
from .repository import repo_get_tasks
WEB_OUTPUT_DIR = os.path.join(BASE_DIR, "output", "web", "db_export_tasks_markdown")
def export_tasks_to_markdown(
user_id: int,
filename: Optional[str] = "ROADMAP.md",
status_filter: Optional[str] = None
) -> Dict[str, Any]:
tasks = repo_get_tasks(user_id=user_id, status=status_filter)
safe_filename = os.path.basename(filename or "ROADMAP.md")
if not safe_filename.endswith(".md"):
safe_filename += ".md"
session_uuid = str(uuid.uuid4())[:8]
target_dir = os.path.join(WEB_OUTPUT_DIR, session_uuid)
os.makedirs(target_dir, exist_ok=True)
target_filepath = os.path.join(target_dir, safe_filename)
lines = [
f"# 📋 Реестр задач проекта ({safe_filename})",
f"**Всего задач:** {len(tasks)} ",
f"**Пользователь ID:** {user_id} ",
"",
"| ID | Статус | Приоритет | Модуль | Срок | Задача |",
"| :--- | :--- | :--- | :--- | :--- | :--- |"
]
status_icons = {
"IN_PROGRESS": "⚙️ В работе",
"COMPLETED": "✓ Завершено",
"BACKLOG": "📋 Бэклог"
}
for t in tasks:
t_id = t.get("task_id") or f"#{t.get('id')}"
t_status = status_icons.get(t.get("status"), t.get("status", "BACKLOG"))
t_prio = t.get("priority", "MEDIUM")
t_mod = t.get("module", "general")
t_due = t.get("due_date") or "—"
t_title = str(t.get("title", "")).replace("|", "\\|").strip()
lines.append(f"| `{t_id}` | {t_status} | {t_prio} | `{t_mod}` | {t_due} | {t_title} |")
lines.append("")
content = "\n".join(lines)
with open(target_filepath, "w", encoding="utf-8") as f:
f.write(content)
download_url = f"/api/v1/files/download/db_export_tasks_markdown/{session_uuid}/{safe_filename}"
return {
"status": "success",
"message": f"Отчет успешно сформирован в файл `{safe_filename}` (всего задач: {len(tasks)}).",
"filename": safe_filename,
"download_url": download_url,
"tasks_count": len(tasks)
}
File: ./services/tasks/repository.py
"""
===============================================================================
FILE: services/tasks/repository.py
===============================================================================
"""
import re
from typing import List, Dict, Any, Optional
from core.connection import get_connection
def normalize_task_id(task_id_input: str) -> str:
if not task_id_input:
return ""
clean_id = str(task_id_input).strip().upper().replace("TASK-", "").replace("TASK", "").replace("#", "")
if clean_id.isdigit():
num = int(clean_id)
return f"TASK-{num:02d}" if num < 100 else f"TASK-{num:03d}"
return f"TASK-{clean_id}"
def repo_get_tasks(user_id: int, status: Optional[str] = None) -> List[Dict[str, Any]]:
with get_connection(row_factory=True) as conn:
cursor = conn.cursor()
if status and status.upper() != "ALL":
target_status = status.upper()
if target_status in ["PROGRESS", "В РАБОТЕ"]:
target_status = "IN_PROGRESS"
elif target_status in ["DONE", "ГОТОВО"]:
target_status = "COMPLETED"
elif target_status in ["PLANNED", "ПЛАНЫ"]:
target_status = "BACKLOG"
cursor.execute("""
SELECT id, task_id, module, title, priority, status, due_date, created_at
FROM tasks
WHERE user_id = ? AND (status = ? OR (status = 'BACKLOG' AND ? = 'PLANNED'))
ORDER BY id DESC
""", (user_id, target_status, target_status))
else:
cursor.execute("""
SELECT id, task_id, module, title, priority, status, due_date, created_at
FROM tasks
WHERE user_id = ?
ORDER BY id DESC
""", (user_id,))
rows = cursor.fetchall()
return [dict(r) for r in rows]
def repo_add_task(
user_id: int,
module: str,
title: str,
priority: str = "MEDIUM",
due_date: Optional[str] = None,
status: str = "BACKLOG"
) -> Dict[str, Any]:
target_status = status.upper() if status else "BACKLOG"
if target_status in ["PROGRESS", "В РАБОТЕ"]:
target_status = "IN_PROGRESS"
elif target_status in ["DONE", "ГОТОВО"]:
target_status = "COMPLETED"
elif target_status in ["PLANNED", "ПЛАНЫ", "BACKLOG"]:
target_status = "BACKLOG"
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("SELECT MAX(id) FROM tasks")
max_id = cursor.fetchone()[0] or 0
new_task_id = f"TASK-{(max_id + 1):02d}"
cursor.execute("""
INSERT INTO tasks (task_id, module, title, priority, status, due_date, user_id)
VALUES (?, ?, ?, ?, ?, ?, ?)
""", (new_task_id, module or "general", title.strip(), priority.upper(), target_status, due_date, user_id))
conn.commit()
return {"status": "success", "task_id": new_task_id, "id": max_id + 1}
def repo_update_task(
user_id: int,
task_id: str,
title: Optional[str] = None,
priority: Optional[str] = None,
status: Optional[str] = None,
due_date: Optional[str] = None
) -> Dict[str, Any]:
clean_num = re.sub(r'\D', '', str(task_id))
formatted_id = normalize_task_id(task_id)
updates = []
params = []
if title is not None and title.strip():
updates.append("title = ?")
params.append(title.strip())
if priority is not None and priority.strip():
updates.append("priority = ?")
params.append(priority.strip().upper())
if status is not None and status.strip():
target_status = status.strip().upper()
if target_status in ["PROGRESS", "В РАБОТЕ"]:
target_status = "IN_PROGRESS"
elif target_status in ["DONE", "ГОТОВО"]:
target_status = "COMPLETED"
elif target_status in ["PLANNED", "ПЛАНЫ"]:
target_status = "BACKLOG"
updates.append("status = ?")
params.append(target_status)
if due_date is not None:
updates.append("due_date = ?")
params.append(due_date.strip() if due_date.strip() else None)
if not updates:
return {"status": "success", "message": "Нет данных для обновления"}
params.extend([clean_num, formatted_id, f"%{task_id.strip()}", user_id])
sql = f"""
UPDATE tasks
SET {', '.join(updates)}
WHERE (id = ? OR UPPER(task_id) = ? OR task_id LIKE ?) AND user_id = ?
"""
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute(sql, params)
rows_affected = cursor.rowcount
conn.commit()
if rows_affected == 0:
return {"error": f"Задача {task_id} не найдена или принадлежит другому пользователю"}
return {"status": "success", "message": f"Задача #{task_id} успешно обновлена"}
def repo_delete_task(user_id: int, task_id: str) -> Dict[str, Any]:
clean_num = re.sub(r'\D', '', str(task_id))
formatted_id = normalize_task_id(task_id)
with get_connection() as conn:
cursor = conn.cursor()
cursor.execute("""
DELETE FROM tasks
WHERE (id = ? OR UPPER(task_id) = ? OR task_id LIKE ?) AND user_id = ?
""", (clean_num, formatted_id, f"%{task_id.strip()}", user_id))
deleted = cursor.rowcount
conn.commit()
if deleted == 0:
return {"error": f"Задача {task_id} не найдена"}
return {"status": "success", "message": f"Задача #{task_id} удалена"}
File: ./services/tasks/service.py
"""
===============================================================================
FILE: services/tasks/service.py
PROJECT: SCUD Orion AI (Unified Architecture)
MODULE: services / tasks
ROLE: Единый доменный сервис задач (бизнес-логика и диспетчер операций).
AI-CONTEXT-ANCHORS:
- ANCHOR[TASK_SERVICE_DISPATCHER]: Маршрутизация действий ADD/UPDATE/DELETE/EXPORT.
===============================================================================
"""
from typing import Dict, Any, Optional, List
from .repository import repo_get_tasks, repo_add_task, repo_update_task, repo_delete_task
from .exporter import export_tasks_to_markdown
def get_tasks(user_id: int, status: Optional[str] = None) -> List[Dict[str, Any]]:
"""Получить список задач."""
return repo_get_tasks(user_id, status)
def add_task(user_id: int, module: str, title: str, priority: str = "MEDIUM", due_date: Optional[str] = None, status: str = "BACKLOG") -> Dict[str, Any]:
"""Создать задачу."""
res = repo_add_task(user_id, module, title, priority, due_date, status)
return {"status": "success", "task_id": res["task_id"], "message": f"Задача #{res['id']} создана и добавлена в планы"}
def update_task_details(user_id: int, task_id: str, title: Optional[str] = None, priority: Optional[str] = None, status: Optional[str] = None, due_date: Optional[str] = None) -> Dict[str, Any]:
"""Обновить задачу."""
return repo_update_task(user_id, task_id, title, priority, status, due_date)
def delete_task(user_id: int, task_id: str) -> Dict[str, Any]:
"""Удалить задачу."""
return repo_delete_task(user_id, task_id)
# ANCHOR[TASK_SERVICE_DISPATCHER]
def execute_task_action(
user_id: int,
action: str,
task_id: Optional[str] = None,
title: Optional[str] = None,
priority: Optional[str] = "MEDIUM",
status: Optional[str] = None,
module: Optional[str] = "general",
due_date: Optional[str] = None,
filename: Optional[str] = "ROADMAP.md"
) -> Dict[str, Any]:
"""Консолидированный диспетчер операций над задачами."""
act = (action or "").strip().upper()
if act == "ADD":
if not title:
return {"status": "error", "message": "Для создания задачи требуется указать title"}
return add_task(user_id, module or "general", title, priority or "MEDIUM", due_date, status or "BACKLOG")
elif act == "UPDATE":
if not task_id:
return {"status": "error", "message": "Для обновления требуется указать task_id"}
return update_task_details(user_id, str(task_id), title, priority, status, due_date)
elif act == "DELETE":
if not task_id:
return {"status": "error", "message": "Для удаления требуется указать task_id"}
return delete_task(user_id, str(task_id))
elif act == "EXPORT":
return export_tasks_to_markdown(user_id, filename=filename or "ROADMAP.md", status_filter=status)
return {"status": "error", "message": f"Неизвестное действие action='{action}'"}
File: ./services/text_reporter.py
import json
import difflib
import re
from services.ai_verifier import ask_ollama
from services.knowledge_base import load_knowledge_base
def find_ai_identity_suggestions(unexplained_df, raw_staff_df):
"""
⭐️ Теневой ИИ-арбитраж: ищет потенциальные опечатки операторов СКУД
среди нераспознанных сотрудников и формирует рекомендации для администратора.
"""
if unexplained_df.empty or raw_staff_df is None or raw_staff_df.empty:
return []
staff_fios = raw_staff_df['fio_clean'].dropna().unique().tolist() if 'fio_clean' in raw_staff_df.columns else []
unexp_fios = unexplained_df['fio_clean'].dropna().unique().tolist() if 'fio_clean' in unexplained_df.columns else []
suggestions = []
for scud_fio in unexp_fios:
f_parts = scud_fio.split()
if not f_parts:
continue
surname = f_parts[0].lower()
for staff_fio in staff_fios:
if staff_fio.lower().startswith(surname[:4]):
ratio = difflib.SequenceMatcher(None, scud_fio.lower(), staff_fio.lower()).ratio()
if 0.75 <= ratio < 1.0:
suggestions.append({
"scud_fio": scud_fio,
"suggested_zup_fio": staff_fio,
"confidence": round(ratio, 2)
})
break
return suggestions
def generate_markdown_report(merged_df, absent_explained, absent_unexplained, scud_present_but_absent_in_1c, anomalies_list=None, raw_scud_df=None, raw_staff_df=None, raw_absent_df=None, date_str="05.08.2026"):
kb = load_knowledge_base()
custom_rules = kb.get("rules", [])
custom_rules_str = "\n".join([f"- {r}" for r in custom_rules]) if custom_rules else "Специфических правил компании пока нет."
total_staff = len(merged_df)
present_office_cnt = len(merged_df[merged_df['Пришел'] == True])
reason_series = merged_df['Вид_отсутствия'].astype(str).str.lower()
is_remote_reason = reason_series.str.contains('удален|дистанцион', regex=True, na=False)
remote_home_cnt = len(merged_df[(merged_df['Пришел'] == False) & is_remote_reason])
explained_cnt = len(merged_df[(merged_df['Пришел'] == False) & (merged_df['Вид_отсутствия'].notna()) & (~is_remote_reason) & (~merged_df['Вид_отсутствия'].astype(str).str.startswith('Исключение'))])
unexplained_cnt = len(absent_unexplained)
anomalies_cnt = len(anomalies_list) if anomalies_list else 0
# ⭐️ Теневые подсказки ИИ
ai_suggestions = find_ai_identity_suggestions(absent_unexplained, raw_staff_df)
anomalies_formatted = []
if anomalies_list:
for idx, a in enumerate(anomalies_list, 1):
anomalies_formatted.append(f"{idx}. {a.get('fio', 'Сотрудник')}: {a.get('description', '')}")
anomalies_text_block = "\n".join(anomalies_formatted) if anomalies_formatted else "Аномалий не обнаружено."
unexplained_list = []
if not absent_unexplained.empty:
for idx, r in absent_unexplained.iterrows():
fio = r.get('Сотрудник', r.get('fio_clean', ''))
dept_scud = str(r.get('department_scud', '')).strip()
dept_1c = str(r.get('Подразделение', '')).strip()
dept = dept_scud if (dept_scud and dept_scud.lower() != 'nan' and dept_scud != '—') else dept_1c
if not dept:
dept = '—'
pos = r.get('Должность', '—')
unexplained_list.append(f"{fio} — {dept}, {pos}")
prompt = f"""
Ты — старший аудитор кадровой безопасности и контроллинга СКУД.
Сформируй итоговую сводку кадрового контроля на {date_str}.
📌 БАЗА ЗНАНИЙ И ПРАВИЛА ПРЕДПРИЯТИЯ:
{custom_rules_str}
🚨 ВХОДНЫЕ МЕТРИКИ:
- Всего сотрудников: {total_staff}
- Итого на работе (в офисе): {present_office_cnt}
- В том числе на удаленной работе: {remote_home_cnt}
- Официально отсутствуют: {explained_cnt}
- Неизвестно (истинно неотмеченные): {unexplained_cnt}
- Выявлено аномалий/конфликтов реестров: {anomalies_cnt}
---
🚨 ПОДТВЕРЖДЁННЫЕ АНОМАЛИИ ({anomalies_cnt} шт):
{anomalies_text_block}
---
💡 ПРЕДЛОЖЕНИЯ ИИ ПО СОПОСТАВЛЕНИЮ ФИО ({len(ai_suggestions)} шт):
{json.dumps(ai_suggestions, ensure_ascii=False, indent=2)}
---
📊 СПИСОК НЕИЗВЕСТНЫХ СЛУЧАЕВ ({unexplained_cnt} чел):
{json.dumps(unexplained_list, ensure_ascii=False, indent=2)}
СТРОГИЕ ИНСТРУКЦИИ:
1. КАТЕГОРИЧЕСКИ ЗАПРЕЩЕНО выводить JSON! В блоке "Выявленные ИИ аномалии" выведи читаемый нумерованный список строк ровно так, как передано в разделе "ПОДТВЕРЖДЁННЫЕ АНОМАЛИИ".
2. В разделе "Подозрения на ошибки сопоставления ФИО" выведи рекомендации по сопоставлению из массива "ПРЕДЛОЖЕНИЯ ИИ". Если массив пуст — напиши "Ошибок сопоставления ФИО не обнаружено."
3. В разделе "Неизвестные случаи" выведи нумерованный список всех {unexplained_cnt} человек.
4. В разделе "Рекомендации" дай 2-3 системные рекомендации.
СТРОГИЙ ШАБЛОН ОТВЕТА:
**Сводка контроллинга СКУД и 1С:ЗУП на {date_str}**
- Всего офисных сотрудников: **{total_staff}**
- Итого на работе (в офисе): **{present_office_cnt}**
- В том числе на удаленной работе: **{remote_home_cnt}**
- Официально отсутствуют: **{explained_cnt}**
- Неизвестно (истинно неотмеченные): **{unexplained_cnt}** чел.
- Выявлено аномалий/конфликтов реестров: **{anomalies_cnt}** шт.
#### 🚨 Выявленные ИИ аномалии и конфликты источников ({anomalies_cnt}):
(Список аномалий или 'Аномалий не обнаружено.')
#### 💡 Подозрения на ошибки сопоставления ФИО и предложения ИИ:
(Список предложений сопоставления или 'Ошибок сопоставления ФИО не обнаружено.')
#### Неизвестные случаи: {unexplained_cnt}
(Нумерованный список всех {unexplained_cnt} человек)
### Точечные рекомендации:
(2-3 системные рекомендации)
"""
sys_prompt = "Ты — русскоязычный кадровый аудитор. Пиши СТРОГО на русском языке форматированным текстом. Запрещено выводить фигурные скобки JSON."
return ask_ollama(prompt, system_prompt=sys_prompt)
File: ./services/zup_extractor.py
import os
import logging
from datetime import date, datetime
import pyodbc
import pandas as pd
from config import DATA_DIR, normalize_fio, ZUP_SQL_CONFIG
logger = logging.getLogger(__name__)
def get_zup_connection_string() -> str:
"""Формирует строку подключения pyodbc к MS SQL Server из config.py."""
return (
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={ZUP_SQL_CONFIG.get('trust_server_certificate', 'yes')};"
f"Encrypt={ZUP_SQL_CONFIG.get('encrypt', 'no')};"
)
def fetch_zup_absences_from_sql(target_date) -> pd.DataFrame:
"""
Извлекает оперативные отсутствия и действующие декреты из MS SQL 1С:ЗУП 3.1
на указанную дату (принимает как datetime.date, так и строку 'DD.MM.YYYY' / 'DD_MM_YYYY').
"""
if isinstance(target_date, str):
clean_date_str = target_date.replace('_', '.')
try:
target_date = datetime.strptime(clean_date_str, "%d.%m.%Y").date()
except ValueError:
logger.error(f"[❌] Неверный формат даты для SQL-запроса: {target_date}. Ожидался DD.MM.YYYY")
return pd.DataFrame()
query = """
DECLARE @TargetDate DATE = ?;
-- 1. Оперативные отсутствия (Отпуска, Командировки, Больничные, Отгулы)
SELECT
LTRIM(RTRIM(ref_emp._Description)) AS [ФИО],
CASE state._Fld16925RRef
WHEN 0x9C10B2452D414FDF4A90E2B2AB81D3F7 THEN N'Отпуск основной'
WHEN 0xBA63FCF94B4AD0664ED369D2E6505D67 THEN N'Командировка'
WHEN 0x8C3B61F23954155A40EB0108FC0932DB THEN N'Болезнь'
WHEN 0x853001C18D0965EE4B2702405C94054A THEN N'Отпуск неоплачиваемый по разрешению работодателя'
WHEN 0xB7335AEFD8708C3E462861FC59489A38 THEN N'Отпуск по беременности и родам'
ELSE N'Другое отсутствие'
END AS [Вид_отсутствия]
FROM dbo._InfoRg16921 state WITH (NOLOCK)
INNER JOIN dbo._Reference299 ref_emp WITH (NOLOCK)
ON state._Fld16922RRef = ref_emp._IDRRef
WHERE @TargetDate BETWEEN CAST(CASE WHEN YEAR(state._Fld16926) > 3000 THEN DATEADD(YEAR, -2000, state._Fld16926) ELSE state._Fld16926 END AS DATE)
AND CAST(CASE WHEN YEAR(state._Fld16927) > 3000 THEN DATEADD(YEAR, -2000, state._Fld16927) ELSE state._Fld16927 END AS DATE)
UNION ALL
-- 2. Динамический выбор ДЕЙСТВУЮЩИХ декретниц по уходу за ребенком
SELECT
active_state.fio AS [ФИО],
N'Отпуск по уходу за ребенком' AS [Вид_отсутствия]
FROM (
SELECT
LTRIM(RTRIM(ref_emp._Description)) AS fio,
all_states._Fld16925RRef AS state_guid,
CAST(CASE WHEN YEAR(all_states._Fld16926) > 3000 THEN DATEADD(YEAR, -2000, all_states._Fld16926) ELSE all_states._Fld16926 END AS DATE) AS date_start,
ROW_NUMBER() OVER (
PARTITION BY all_states._Fld16922RRef
ORDER BY all_states._Fld16926 DESC
) AS rn
FROM dbo._InfoRg16921 all_states WITH (NOLOCK)
INNER JOIN dbo._Reference299 ref_emp WITH (NOLOCK)
ON all_states._Fld16922RRef = ref_emp._IDRRef
WHERE CAST(CASE WHEN YEAR(all_states._Fld16926) > 3000 THEN DATEADD(YEAR, -2000, all_states._Fld16926) ELSE all_states._Fld16926 END AS DATE) <= @TargetDate
) active_state
WHERE active_state.rn = 1
AND active_state.state_guid = 0xA4FBA038663B3C2A48DA151C262855E1
AND active_state.date_start >= DATEADD(YEAR, -3, @TargetDate)
ORDER BY [ФИО] ASC;
"""
try:
conn_str = get_zup_connection_string()
with pyodbc.connect(conn_str, timeout=30) as conn:
df = pd.read_sql(query, conn, params=[target_date])
if not df.empty:
df['fio_clean'] = df['ФИО'].apply(normalize_fio)
return df
except Exception as e:
logger.error(f"[❌] Ошибка SQL-выгрузки отсутствий за {target_date}: {e}")
return pd.DataFrame()
def fetch_zup_staff_from_sql() -> pd.DataFrame:
"""Резервная выгрузка штата из MS SQL."""
query = """
SELECT DISTINCT
LTRIM(RTRIM(ref_emp._Description)) AS [ФИО],
N'Организация' AS [Подразделение],
N'Сотрудник' AS [Должность]
FROM dbo._Reference299 ref_emp WITH (NOLOCK)
WHERE ref_emp._Description <> ''
AND ref_emp._Marked = 0x00
ORDER BY [ФИО] ASC;
"""
try:
conn_str = get_zup_connection_string()
with pyodbc.connect(conn_str, timeout=5) as conn:
df = pd.read_sql(query, conn)
if not df.empty:
df['fio_clean'] = df['ФИО'].apply(normalize_fio)
return df
except Exception as e:
logger.error(f"[❌] Ошибка выгрузки штата из MS SQL: {e}")
return pd.DataFrame()
def sync_zup_to_excel(target_date) -> bool:
"""Создает дамп в Excel при необходимости."""
try:
if isinstance(target_date, str):
clean_date_str = target_date.replace('_', '.')
target_date_obj = datetime.strptime(clean_date_str, "%d.%m.%Y").date()
else:
target_date_obj = target_date
date_str_file = target_date_obj.strftime("%d_%m_%Y")
c_1c_dir = os.path.join(DATA_DIR, "1c")
os.makedirs(c_1c_dir, exist_ok=True)
df_staff = fetch_zup_staff_from_sql()
df_absences = fetch_zup_absences_from_sql(target_date_obj)
staff_excel_path = os.path.join(c_1c_dir, f"Штат_{date_str_file}.xlsx")
absences_excel_path = os.path.join(c_1c_dir, f"Отсутствия_{date_str_file}.xlsx")
with pd.ExcelWriter(staff_excel_path, engine='openpyxl') as writer:
dummy_headers = pd.DataFrame([[""] * 13] * 8)
dummy_headers.to_excel(writer, index=False, header=False)
df_staff[['ФИО', 'Подразделение', 'Должность']].to_excel(writer, startrow=8, index=False)
with pd.ExcelWriter(absences_excel_path, engine='openpyxl') as writer:
dummy_headers = pd.DataFrame([[""] * 3] * 3)
dummy_headers.to_excel(writer, index=False, header=False)
df_absences[['ФИО', 'Вид_отсутствия']].to_excel(writer, startrow=3, index=False)
return True
except Exception as e:
logger.error(f"[❌] Ошибка прямого импорта из MS SQL 1С: {e}")
return False