119 lines
5.8 KiB
Python
119 lines
5.8 KiB
Python
"""
|
|
===============================================================================
|
|
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) |