159 lines
7.0 KiB
Python
159 lines
7.0 KiB
Python
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=5) 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 |