---

# ==================== ЭКСПОРТ В EXCEL ====================

TEMPLATE_DIR = os.path.join(os.path.dirname(__file__), 'excel_templates')

def format_date(date_obj):
    return date_obj.strftime('%d.%m.%Y') if date_obj else ''

@app.route('/export/1106/<int:note_id>')
def export_1106(note_id):
    note = ServiceNote.query.get_or_404(note_id)
    details = SupplementDetail.query.filter_by(service_note_id=note_id).all()
    
    template_path = os.path.join(TEMPLATE_DIR, 'v-9.7_rzo.xlsx')
    wb = load_workbook(template_path)
    ws = wb.active # Или wb['СЗ'], если активный лист не тот
    
    # Заполняем шапку (примерные ячейки, уточните по файлу)
    # ws['C4'] = f"№ {note.doc_number} от {format_date(note.doc_date)}"
    
    # Данные начинаются с 24 строки (как в вашем примере)
    start_row = 24
    for idx, detail in enumerate(details):
        emp = detail.employee
        row = start_row + idx
        
        ws.cell(row=row, column=1, value=idx + 1) # № п/п
        ws.cell(row=row, column=2, value=emp.tab_no if emp else '') # Таб. номер
        ws.cell(row=row, column=3, value=emp.full_name if emp else '') # ФИО
        ws.cell(row=row, column=4, value=emp.department if emp else '') # Подразделение
        ws.cell(row=row, column=5, value=emp.position if emp else '') # Должность
        ws.cell(row=row, column=6, value=format_date(detail.start_date)) # Период с
        ws.cell(row=row, column=7, value=format_date(detail.end_date)) # Период по
        ws.cell(row=row, column=8, value=detail.amount) # Доплата
        ws.cell(row=row, column=9, value=note.payment_code) # Код ВО
        ws.cell(row=row, column=10, value=detail.work_content or '') # Основание
        ws.cell(row=row, column=11, value=detail.work_type or '') # Вид работы

    output = BytesIO()
    wb.save(output)
    output.seek(0)
    return send_file(output, download_name=f'СЗ_В-9.7_{note.doc_number}.xlsx', as_attachment=True)


@app.route('/export/1003/<int:note_id>')
def export_1003(note_id):
    note = ServiceNote.query.get_or_404(note_id)
    details = CombinationDetail.query.filter_by(service_note_id=note_id).all()
    
    template_path = os.path.join(TEMPLATE_DIR, 'v-9.4_comb.xlsx')
    wb = load_workbook(template_path)
    ws = wb.active
    
    start_row = 21 # Данные начинаются с 21 строки
    for idx, detail in enumerate(details):
        emp = detail.employee
        row = start_row + idx
        
        ws.cell(row=row, column=1, value=idx + 1)
        ws.cell(row=row, column=2, value=emp.tab_no if emp else '')
        ws.cell(row=row, column=3, value=emp.full_name if emp else '')
        ws.cell(row=row, column=4, value=detail.combined_position or '')
        ws.cell(row=row, column=5, value=detail.position_identifier or '')
        ws.cell(row=row, column=6, value=detail.department or '')
        ws.cell(row=row, column=7, value=format_date(detail.start_date))
        ws.cell(row=row, column=8, value=format_date(detail.end_date))
        ws.cell(row=row, column=9, value=f"{detail.extra_pay}%" if detail.extra_pay else '') # Доплата
        ws.cell(row=row, column=10, value=detail.payment_code or '')
        ws.cell(row=row, column=14, value=detail.reason or '') # Основание
        ws.cell(row=row, column=15, value=detail.duties or '') # Обязанности

    output = BytesIO()
    wb.save(output)
    output.seek(0)
    return send_file(output, download_name=f'СЗ_В-9.4_{note.doc_number}.xlsx', as_attachment=True)


@app.route('/export/1019/<int:note_id>')
def export_1019(note_id):
    note = ServiceNote.query.get_or_404(note_id)
    detail = SubstitutionDetail.query.filter_by(service_note_id=note_id).first()
    
    template_path = os.path.join(TEMPLATE_DIR, 'v-9.10_subst.xlsx')
    wb = load_workbook(template_path)
    ws = wb.active
    
    if detail:
        # Заполняем конкретные ячейки согласно шаблону В-9.10
        ws['C4'] = f"Заявка № {note.doc_number} от {format_date(note.doc_date)}"
        ws['C6'] = detail.absence_reason or ''
        
        # Отсутствующий работник
        if detail.absent:
            ws['C7'] = detail.absent.full_name
            ws['H7'] = detail.absent.tab_no
            ws['C9'] = f"{detail.absent.position}, {detail.absent.department}"
            
        # Замещающий работник
        if detail.substitute:
            ws['C11'] = detail.substitute.full_name
            ws['H11'] = detail.substitute.tab_no
            ws['C13'] = f"{detail.substitute.position}, {detail.substitute.department}"
            
        ws['B15'] = format_date(detail.start_date)
        ws['G15'] = format_date(detail.end_date)
        ws['G28'] = detail.extra_pay_percent # Доплата %
        # Добавьте другие поля по необходимости

    output = BytesIO()
    wb.save(output)
    output.seek(0)
    return send_file(output, download_name=f'Заявка_В-9.10_{note.doc_number}.xlsx', as_attachment=True)


@app.route('/export/9999/<int:note_id>')
def export_9999(note_id):
    note = ServiceNote.query.get_or_404(note_id)
    details = AllowanceDetail.query.filter_by(service_note_id=note_id).all()
    
    template_path = os.path.join(TEMPLATE_DIR, 'v-17.1_allow.xlsx')
    wb = load_workbook(template_path)
    ws = wb.active
    
    start_row = 21 # Данные начинаются с 21 строки
    for idx, detail in enumerate(details):
        emp = detail.employee
        row = start_row + idx
        
        ws.cell(row=row, column=1, value=idx + 1)
        ws.cell(row=row, column=2, value=emp.tab_no if emp else '')
        ws.cell(row=row, column=3, value=emp.full_name if emp else '')
        ws.cell(row=row, column=4, value=detail.allowance_name or '')
        ws.cell(row=row, column=5, value=detail.payment_code or '')
        ws.cell(row=row, column=6, value=detail.unit or '')
        ws.cell(row=row, column=7, value=detail.amount) # Размер
        ws.cell(row=row, column=8, value=detail.status or 'установить')
        ws.cell(row=row, column=9, value=format_date(detail.start_date))
        ws.cell(row=row, column=10, value=format_date(detail.end_date))

    output = BytesIO()
    wb.save(output)
    output.seek(0)
    return send_file(output, download_name=f'СЗ_В-17.1_{note.doc_number}.xlsx', as_attachment=True)