// resources/assets/ts/ContactsPage/utils/employeeCsvImportUtils.ts /** * Employee CSV Import Utilities * * Key features: * - Duplicates are WARNINGS, not blockers (users can override) * - Supports multi-company import via optional adressid column * - Uses same /contacts/saveEmp endpoint as MitarbeiterTab */ import { i18n } from '@lingui/core'; export interface ParsedEmployee { adressid?: number; // Optional: Company ID (for multi-company import) firmenname?: string; // Optional: For display purposes MW: string; // Salutation (Herr/Frau) Name: string; // Last name (required) vorname: string; // First name email: string; dw1: string; // Phone extension 1 dw2: string; // Phone extension 2 dw3: string; // Phone extension 3 dw4: string; // Phone extension 4 fax: string; bemerkung: string; // Remarks kma_brf: number; // Flag (0/1) } export interface ValidationResult { valid: boolean; errors: string[]; warnings: string[]; } export interface DuplicateCheckResult { isDuplicate: boolean; reason: string; matchType: 'name' | 'email' | 'both' | null; existingEmployeeId?: number; } export interface EmployeePreview extends ParsedEmployee { rowNumber: number; validation: ValidationResult; duplicateCheck: DuplicateCheckResult; importAnyway: boolean; // User can override duplicate warning } // ─── Localized column config ────────────────────────────────────────────── // Single source of truth: the template is generated from `labels[locale]`, and // parsing accepts every locale's header (+ legacy aliases) for the same field, // so a template downloaded in one language always re-imports correctly. type EmpLocale = 'de' | 'en' | 'fr' | 'es' | 'it'; const EMP_LOCALES: EmpLocale[] = ['de', 'en', 'fr', 'es', 'it']; const empActiveLocale = (): EmpLocale => { // Use the same authoritative source as setupI18n (server-set ), // falling back to the Lingui locale; normalize "fr-FR"/"fr_FR" → "fr". const raw = (typeof document !== 'undefined' && document.documentElement.lang) || i18n.locale || 'de'; const base = String(raw).toLowerCase().split(/[-_]/)[0]; return (EMP_LOCALES as readonly string[]).includes(base) ? (base as EmpLocale) : 'de'; }; interface EmpColumn { key: 'adressid' | 'MW' | 'Name' | 'vorname' | 'email' | 'dw1' | 'dw2' | 'dw3' | 'dw4' | 'fax' | 'bemerkung'; required?: boolean; labels: Record; example: Record; extra?: string[]; // legacy / alternate header aliases also accepted on import } const EMP_COLUMNS: EmpColumn[] = [ { key: 'adressid', required: true, labels: { de: 'adressid', en: 'company_id', fr: 'id_entreprise', es: 'id_empresa', it: 'id_azienda' }, example: { de: '1', en: '1', fr: '1', es: '1', it: '1' }, extra: ['firmen_id', 'firma_id', 'kontakt_id'] }, { key: 'MW', labels: { de: 'MW', en: 'salutation', fr: 'civilite', es: 'tratamiento', it: 'titolo' }, example: { de: 'Herr', en: 'Herr', fr: 'Herr', es: 'Herr', it: 'Herr' }, // stored value stays German extra: ['anrede'] }, { key: 'Name', required: true, labels: { de: 'Name', en: 'lastname', fr: 'nom', es: 'apellido', it: 'cognome' }, example: { de: 'Mustermann', en: 'Doe', fr: 'Dupont', es: 'García', it: 'Rossi' }, extra: ['nachname', 'last_name'] }, { key: 'vorname', labels: { de: 'vorname', en: 'firstname', fr: 'prenom', es: 'nombre', it: 'nome' }, example: { de: 'Max', en: 'John', fr: 'Jean', es: 'Juan', it: 'Mario' }, extra: ['first_name'] }, { key: 'email', labels: { de: 'email', en: 'email', fr: 'email', es: 'correo', it: 'email' }, example: { de: 'max.mustermann@example.com', en: 'john.doe@example.com', fr: 'jean.dupont@exemple.fr', es: 'juan.garcia@ejemplo.es', it: 'mario.rossi@esempio.it' }, extra: ['e-mail', 'mail'] }, { key: 'dw1', labels: { de: 'dw1', en: 'phone_1', fr: 'telephone_1', es: 'telefono_1', it: 'telefono_1' }, example: { de: '+49 123 456789', en: '+49 123 456789', fr: '+49 123 456789', es: '+49 123 456789', it: '+49 123 456789' }, extra: ['telefon', 'phone', 'telefon1', 'phone1'] }, { key: 'dw2', labels: { de: 'dw2', en: 'phone_2', fr: 'telephone_2', es: 'telefono_2', it: 'telefono_2' }, example: { de: '', en: '', fr: '', es: '', it: '' }, extra: ['telefon2', 'phone2'] }, { key: 'dw3', labels: { de: 'dw3', en: 'phone_3', fr: 'telephone_3', es: 'telefono_3', it: 'telefono_3' }, example: { de: '', en: '', fr: '', es: '', it: '' }, extra: ['telefon3', 'phone3'] }, { key: 'dw4', labels: { de: 'dw4', en: 'phone_4', fr: 'telephone_4', es: 'telefono_4', it: 'telefono_4' }, example: { de: '', en: '', fr: '', es: '', it: '' }, extra: ['telefon4', 'phone4'] }, { key: 'fax', labels: { de: 'fax', en: 'fax', fr: 'fax', es: 'fax', it: 'fax' }, example: { de: '', en: '', fr: '', es: '', it: '' } }, { key: 'bemerkung', labels: { de: 'bemerkung', en: 'notes', fr: 'remarque', es: 'nota', it: 'nota' }, example: { de: 'Abteilungsleiter', en: 'Department head', fr: 'Chef de service', es: 'Jefe de departamento', it: 'Capo reparto' }, extra: ['remarks', 'notizen'] }, ]; const EMP_TEMPLATE_FILENAME: Record = { de: { single: 'mitarbeiter_import_vorlage.csv', multi: 'mitarbeiter_import_mehrere_firmen_vorlage.csv' }, en: { single: 'employees_import_template.csv', multi: 'employees_import_multiple_companies_template.csv' }, fr: { single: 'modele_import_employes.csv', multi: 'modele_import_employes_plusieurs_entreprises.csv' }, es: { single: 'plantilla_importacion_empleados.csv', multi: 'plantilla_importacion_empleados_varias_empresas.csv' }, it: { single: 'modello_importazione_dipendenti.csv', multi: 'modello_importazione_dipendenti_piu_aziende.csv' }, }; // All accepted header aliases for a field (canonical key + every locale label + legacy extras) const empAliases = (key: EmpColumn['key']): string[] => { const col = EMP_COLUMNS.find(c => c.key === key)!; return [col.key.toLowerCase(), ...EMP_LOCALES.map(l => col.labels[l].toLowerCase()), ...(col.extra || [])]; }; /** Canonical employee column keys for the active export columns (in order). */ export const getEmployeeCsvKeys = (includeAdressid: boolean): EmpColumn['key'][] => EMP_COLUMNS.filter(c => includeAdressid || c.key !== 'adressid').map(c => c.key); /** Localized employee CSV headers in the active UI language (identical to the import template). */ export const getEmployeeCsvHeaders = (includeAdressid: boolean): string[] => { const loc = empActiveLocale(); return EMP_COLUMNS .filter(c => includeAdressid || c.key !== 'adressid') .map(c => c.labels[loc] + (c.required ? '*' : '')); }; /** Localized filename (with timestamp) for an exported employee list. */ const EMP_EXPORT_BASENAME: Record = { de: 'mitarbeiter_export', en: 'employees_export', fr: 'export_employes', es: 'exportacion_empleados', it: 'esportazione_dipendenti', }; export const employeeExportFilename = (): string => `${EMP_EXPORT_BASENAME[empActiveLocale()]}_${Date.now()}.csv`; /** * Parse CSV text into employee objects */ export function parseCSV(csvText: string): ParsedEmployee[] { const lines = csvText.trim().split('\n'); if (lines.length < 2) { return []; } // Parse header row - handle both comma and semicolon delimiters const delimiter = lines[0].includes(';') ? ';' : ','; // Clean headers: lowercase, trim, and remove asterisks/special chars const headers = parseCSVLine(lines[0], delimiter).map(h => h.trim().toLowerCase().replace(/[*]/g, '') ); const employees: ParsedEmployee[] = []; for (let i = 1; i < lines.length; i++) { const line = lines[i].trim(); if (!line) continue; const values = parseCSVLine(line, delimiter); // Parse adressid if present (for multi-company import) const adressidStr = getValueByHeader(headers, values, empAliases('adressid')); const adressid = adressidStr ? parseInt(adressidStr, 10) : undefined; const employee: ParsedEmployee = { adressid: adressid && !isNaN(adressid) ? adressid : undefined, firmenname: getValueByHeader(headers, values, ['firmenname', 'firma', 'company', 'company_name']) || '', MW: getValueByHeader(headers, values, empAliases('MW')) || '', Name: getValueByHeader(headers, values, empAliases('Name')) || '', vorname: getValueByHeader(headers, values, empAliases('vorname')) || '', email: getValueByHeader(headers, values, empAliases('email')) || '', dw1: getValueByHeader(headers, values, empAliases('dw1')) || '', dw2: getValueByHeader(headers, values, empAliases('dw2')) || '', dw3: getValueByHeader(headers, values, empAliases('dw3')) || '', dw4: getValueByHeader(headers, values, empAliases('dw4')) || '', fax: getValueByHeader(headers, values, empAliases('fax')) || '', bemerkung: getValueByHeader(headers, values, empAliases('bemerkung')) || '', kma_brf: parseInt(getValueByHeader(headers, values, ['kma_brf', 'briefversand']) || '0') || 0, }; employees.push(employee); } return employees; } /** * Parse a single CSV line, handling quoted values */ function parseCSVLine(line: string, delimiter: string): string[] { const result: string[] = []; let current = ''; let inQuotes = false; for (let i = 0; i < line.length; i++) { const char = line[i]; if (char === '"') { if (inQuotes && line[i + 1] === '"') { current += '"'; i++; } else { inQuotes = !inQuotes; } } else if (char === delimiter && !inQuotes) { result.push(current.trim()); current = ''; } else { current += char; } } result.push(current.trim()); return result; } /** * Get value from parsed line by checking multiple possible header names */ function getValueByHeader(headers: string[], values: string[], possibleNames: string[]): string { for (const name of possibleNames) { const index = headers.indexOf(name.toLowerCase()); if (index !== -1 && values[index] !== undefined) { return values[index].trim(); } } return ''; } /** * Validate a single employee record * * @param employee - The employee to validate * @param requireAdressid - If true, adressid is required (for multi-company mode) */ export function validateEmployee(employee: ParsedEmployee, requireAdressid: boolean = false): ValidationResult { const errors: string[] = []; const warnings: string[] = []; // Required field: Name (last name) if (!employee.Name || employee.Name.trim() === '') { errors.push('Nachname ist erforderlich'); } // Required field: adressid (only in multi-company mode) if (requireAdressid && !employee.adressid) { errors.push('Firma-ID (adressid) ist erforderlich'); } // Validate email format if provided if (employee.email && employee.email.trim() !== '') { const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/; if (!emailRegex.test(employee.email)) { errors.push('Ungültiges E-Mail-Format'); } } // Validate MW (salutation) if provided if (employee.MW && !['', 'Herr', 'Frau'].includes(employee.MW)) { warnings.push('Anrede sollte "Herr" oder "Frau" sein'); } // Warning if no first name if (!employee.vorname || employee.vorname.trim() === '') { warnings.push('Vorname fehlt'); } return { valid: errors.length === 0, errors, warnings, }; } /** * Check for duplicate employees in the database * Returns a warning, not a blocker! * * @param employee - The employee to check * @param contactId - The contact ID to check within (uses employee.adressid if not provided) */ export async function checkDuplicate( employee: ParsedEmployee, contactId?: number ): Promise { const targetContactId = employee.adressid || contactId; if (!targetContactId) { return { isDuplicate: false, reason: '', matchType: null }; } try { const response = await fetch(`/api/contacts/${targetContactId}/employees/check-duplicate`, { method: 'POST', headers: { 'Content-Type': 'application/json', 'X-CSRF-TOKEN': getCSRFToken(), 'Accept': 'application/json', }, body: JSON.stringify({ name: employee.Name, vorname: employee.vorname, email: employee.email, }), }); if (!response.ok) { console.warn('Duplicate check failed, proceeding without check'); return { isDuplicate: false, reason: '', matchType: null }; } const data = await response.json(); return { isDuplicate: data.isDuplicate || false, reason: data.reason || '', matchType: data.matchType || null, existingEmployeeId: data.existingEmployeeId, }; } catch (error) { console.error('Error checking duplicate:', error); return { isDuplicate: false, reason: '', matchType: null }; } } /** * Import a single employee * Uses the same endpoint as MitarbeiterTab: /contacts/saveEmp * * @param employee - The employee data to import * @param contactId - Fallback contact ID (used if employee.adressid is not set) */ export async function importEmployee( employee: ParsedEmployee, contactId?: number ): Promise<{ success: boolean; employeeId?: number; error?: string }> { // Use employee's adressid if provided, otherwise use passed contactId const targetContactId = employee.adressid || contactId; if (!targetContactId) { return { success: false, error: 'Keine Firma-ID angegeben (adressid fehlt)', }; } try { const formData = new FormData(); formData.append('id', targetContactId.toString()); formData.append('ma_MW', employee.MW); formData.append('ma_name', employee.Name); formData.append('ma_vorname', employee.vorname); formData.append('ma_email', employee.email); formData.append('ma_dw1', employee.dw1); formData.append('ma_dw2', employee.dw2 || ''); formData.append('ma_dw3', employee.dw3 || ''); formData.append('ma_dw4', employee.dw4 || ''); formData.append('ma_fax', employee.fax); formData.append('ma_bemerkung', employee.bemerkung); formData.append('_token', getCSRFToken()); if (employee.kma_brf === 1) { formData.append('ma_wahl', 'on'); } const response = await fetch('/contacts/saveEmp', { method: 'POST', body: formData, credentials: 'include', }); if (!response.ok) { const errorData = await response.json().catch(() => ({})); return { success: false, error: errorData.message || 'Import fehlgeschlagen' }; } const data = await response.json(); return { success: true, employeeId: data.ma_id }; } catch (error) { console.error('Error importing employee:', error); return { success: false, error: error instanceof Error ? error.message : 'Unbekannter Fehler' }; } } /** * Generate and download CSV template for employees * * @param includeAdressid - If true, includes adressid column for multi-company import */ export function generateCSVTemplate(includeAdressid: boolean = false): void { const loc = empActiveLocale(); const cols = EMP_COLUMNS.filter(c => includeAdressid || c.key !== 'adressid'); const headers = cols.map(c => c.labels[loc] + (c.required ? '*' : '')); const exampleRow = cols.map(c => c.example[loc]); const csvContent = [ headers.join(';'), exampleRow.join(';'), ].join('\n'); const filename = includeAdressid ? EMP_TEMPLATE_FILENAME[loc].multi : EMP_TEMPLATE_FILENAME[loc].single; const blob = new Blob(['\ufeff' + csvContent], { type: 'text/csv;charset=utf-8;' }); const url = URL.createObjectURL(blob); const link = document.createElement('a'); link.href = url; link.download = filename; document.body.appendChild(link); link.click(); document.body.removeChild(link); URL.revokeObjectURL(url); } /** * Get CSRF token from meta tag */ function getCSRFToken(): string { const tokenElement = document.querySelector('meta[name="csrf-token"]'); return tokenElement ? tokenElement.getAttribute('content') || '' : ''; } /** * Bulk import employees with progress callback * * @param employees - Array of employees to import * @param contactId - Optional fallback contact ID (used if employee.adressid is not set) * @param onProgress - Optional progress callback */ export async function bulkImportEmployees( employees: EmployeePreview[], contactId?: number, onProgress?: (current: number, total: number) => void ): Promise<{ success: boolean; imported: number; skipped: number; errors: number; details: Array<{ rowNumber: number; status: 'success' | 'skipped' | 'error'; message: string; employeeId?: number; firmenname?: string; adressid?: number; }>; }> { const result = { success: true, imported: 0, skipped: 0, errors: 0, details: [] as Array<{ rowNumber: number; status: 'success' | 'skipped' | 'error'; message: string; employeeId?: number; firmenname?: string; adressid?: number; }>, }; // Filter: only import valid employees that are either: // - Not duplicates, OR // - Duplicates with importAnyway = true const toImport = employees.filter(e => e.validation.valid && (!e.duplicateCheck.isDuplicate || e.importAnyway) ); const toSkip = employees.filter(e => e.validation.valid && e.duplicateCheck.isDuplicate && !e.importAnyway ); const invalid = employees.filter(e => !e.validation.valid); // Process imports for (let i = 0; i < toImport.length; i++) { const employee = toImport[i]; onProgress?.(i + 1, toImport.length); const importResult = await importEmployee(employee, contactId); if (importResult.success) { result.imported++; result.details.push({ rowNumber: employee.rowNumber, status: 'success', message: employee.duplicateCheck.isDuplicate ? 'Importiert (Duplikat-Warnung überschrieben)' : 'Erfolgreich importiert', employeeId: importResult.employeeId, firmenname: employee.firmenname, adressid: employee.adressid, }); } else { result.errors++; result.details.push({ rowNumber: employee.rowNumber, status: 'error', message: importResult.error || 'Import fehlgeschlagen', firmenname: employee.firmenname, adressid: employee.adressid, }); } } // Add skipped duplicates for (const employee of toSkip) { result.skipped++; result.details.push({ rowNumber: employee.rowNumber, status: 'skipped', message: `Duplikat übersprungen: ${employee.duplicateCheck.reason}`, firmenname: employee.firmenname, adressid: employee.adressid, }); } // Add validation errors for (const employee of invalid) { result.errors++; result.details.push({ rowNumber: employee.rowNumber, status: 'error', message: employee.validation.errors.join('; '), firmenname: employee.firmenname, adressid: employee.adressid, }); } // Sort details by row number result.details.sort((a, b) => a.rowNumber - b.rowNumber); result.success = result.imported > 0 || (result.errors === 0 && result.skipped > 0); return result; } /** * Check if CSV has adressid column (multi-company mode) */ export function detectMultiCompanyMode(csvText: string): boolean { const firstLine = csvText.trim().split('\n')[0] || ''; const delimiter = firstLine.includes(';') ? ';' : ','; const headers = firstLine.toLowerCase().split(delimiter).map(h => h.trim().replace(/[*]/g, '')); return headers.some(h => empAliases('adressid').includes(h)); }