File size: 5,271 Bytes
788eab4
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
"""

Custom Company-Format XLSX & CSV Exporter.

Renders candidate shortlists and interview schedules according to company-specific templates.

"""

from __future__ import annotations

import csv
import io
from typing import Any

import openpyxl
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter

from backend.app.api.export.mapping import CompanyExportMapping
from backend.app.domain.models import Student
from backend.app.scheduler.models import ScheduledItem


def export_to_company_xlsx(

    students: list[Student],

    schedule_items: list[ScheduledItem],

    mapping: CompanyExportMapping,

) -> bytes:
    """Exports candidates to an XLSX matching the company's exact layout and column widths."""
    wb = openpyxl.Workbook()
    ws = wb.active
    assert ws is not None
    ws.title = mapping.sheet_name or "Candidates"

    # Header styling
    header_font = Font(name="Calibri", size=11, bold=True, color="000000")
    header_fill = PatternFill(start_color="E0E0E0", end_color="E0E0E0", fill_type="solid")
    center_align = Alignment(horizontal="center", vertical="center")

    headers = mapping.column_order
    ws.append(headers)

    for col_num in range(1, len(headers) + 1):
        cell = ws.cell(row=1, column=col_num)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = center_align

    # Index schedule by student_id
    sched_map: dict[str, ScheduledItem] = {}
    for item in schedule_items:
        for sid in item.student_ids:
            sched_map[sid] = item

    # Populate candidate rows
    for s in students:
        slot = sched_map.get(s.id)
        row_vals = []
        for h in headers:
            canonical_key = mapping.canonical_mapping.get(h)
            val: Any = ""
            if canonical_key == "roll_no":
                val = s.roll_no
            elif canonical_key == "name":
                val = s.name
            elif canonical_key == "email":
                val = s.email
            elif canonical_key == "branch":
                val = s.branch
            elif canonical_key == "cgpa":
                val = s.cgpa
            elif canonical_key == "tenth_percentage":
                val = s.tenth_percentage
            elif canonical_key == "twelfth_percentage":
                val = s.twelfth_percentage or ""
            elif canonical_key == "round_name":
                val = slot.round_name if slot else ""
            elif canonical_key == "slot_time":
                val = slot.start_time.strftime("%H:%M") if slot else ""
            elif canonical_key == "room_name":
                val = slot.room_name if slot else ""
            elif canonical_key == "panel_name":
                val = slot.panel_name or "" if slot else ""

            row_vals.append(val)

        ws.append(row_vals)

    # Set column widths
    for idx, h in enumerate(headers, start=1):
        col_letter = get_column_letter(idx)
        if h in mapping.column_widths:
            ws.column_dimensions[col_letter].width = mapping.column_widths[h]
        else:
            # Default auto-width
            max_len = max(len(str(h)), 12)
            ws.column_dimensions[col_letter].width = max_len + 3

    buffer = io.BytesIO()
    wb.save(buffer)
    buffer.seek(0)
    return buffer.getvalue()


def export_to_company_csv(

    students: list[Student],

    schedule_items: list[ScheduledItem],

    mapping: CompanyExportMapping,

) -> str:
    """Exports candidates to CSV matching the company's exact header ordering."""
    output = io.StringIO()
    writer = csv.writer(output)
    headers = mapping.column_order
    writer.writerow(headers)

    sched_map: dict[str, ScheduledItem] = {}
    for item in schedule_items:
        for sid in item.student_ids:
            sched_map[sid] = item

    for s in students:
        slot = sched_map.get(s.id)
        row_vals = []
        for h in headers:
            canonical_key = mapping.canonical_mapping.get(h)
            val: Any = ""
            if canonical_key == "roll_no":
                val = s.roll_no
            elif canonical_key == "name":
                val = s.name
            elif canonical_key == "email":
                val = s.email
            elif canonical_key == "branch":
                val = s.branch
            elif canonical_key == "cgpa":
                val = s.cgpa
            elif canonical_key == "tenth_percentage":
                val = s.tenth_percentage
            elif canonical_key == "twelfth_percentage":
                val = s.twelfth_percentage or ""
            elif canonical_key == "round_name":
                val = slot.round_name if slot else ""
            elif canonical_key == "slot_time":
                val = slot.start_time.strftime("%H:%M") if slot else ""
            elif canonical_key == "room_name":
                val = slot.room_name if slot else ""
            elif canonical_key == "panel_name":
                val = slot.panel_name or "" if slot else ""

            row_vals.append(val)
        writer.writerow(row_vals)

    return output.getvalue()