// resources/assets/ts/ContactsPage/utils/csvImportUtils.ts /** * CSV Import Utilities * Helper functions for CSV parsing, validation, and template generation * used for ContactsAddMutiple.tsx */ import { i18n } from '@lingui/core'; export interface ParsedContact { firmenname: string; abkuerzung: string; schlagwoerter: string; email: string; fax: string; website_link_1: string; website_link_2: string; telefon_1: string; telefon_2: string; telefon_3: string; telefon_4: string; strasse: string; plz: string; ort: string; lieferadresse_strasse: string; lieferadresse_plz: string; lieferadresse_ort: string; kategorie_id: string; prioritaet_id: string; branche_id: string; //verantwortlicher: string; use ersteller as default for now, will think of krzl and user validation later bemerkung: string; } export interface ValidationResult { valid: boolean; errors: string[]; } export interface ContactPreview extends ParsedContact { rowNumber: number; validation: ValidationResult; isDuplicate: boolean; duplicateReason?: string; } // ─── Localized column config ────────────────────────────────────────────── // Single source of truth: the template is generated from `labels[locale]`, and // parseCSV normalizes ANY locale's header back to the canonical key. This means // a template downloaded in one language always re-imports correctly. type CsvLocale = 'de' | 'en' | 'fr' | 'es' | 'it'; const CSV_LOCALES: CsvLocale[] = ['de', 'en', 'fr', 'es', 'it']; const activeCsvLocale = (): CsvLocale => { // 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 (CSV_LOCALES as readonly string[]).includes(base) ? (base as CsvLocale) : 'de'; }; interface ContactColumn { key: keyof ParsedContact; required?: boolean; labels: Record; example: Record; } const CONTACT_COLUMNS: ContactColumn[] = [ { key: 'firmenname', required: true, labels: { de: 'firmenname', en: 'company_name', fr: 'nom_entreprise', es: 'nombre_empresa', it: 'nome_azienda' }, example: { de: 'Beispiel GmbH', en: 'Example Ltd', fr: 'Exemple SARL', es: 'Empresa Ejemplo S.L.', it: 'Azienda Esempio Srl' } }, { key: 'abkuerzung', labels: { de: 'abkuerzung', en: 'abbreviation', fr: 'abreviation', es: 'abreviatura', it: 'abbreviazione' }, example: { de: 'BSP', en: 'EX', fr: 'EX', es: 'EJ', it: 'ES' } }, { key: 'schlagwoerter', labels: { de: 'schlagwoerter', en: 'keywords', fr: 'mots_cles', es: 'palabras_clave', it: 'parole_chiave' }, example: { de: 'Schwimbäder, Wellness', en: 'Swimming pools, wellness', fr: 'Piscines, bien-être', es: 'Piscinas, bienestar', it: 'Piscine, benessere' } }, { key: 'email', labels: { de: 'email', en: 'email', fr: 'email', es: 'correo', it: 'email' }, example: { de: 'info@beispiel.de', en: 'info@example.com', fr: 'info@exemple.fr', es: 'info@ejemplo.es', it: 'info@esempio.it' } }, { key: 'fax', labels: { de: 'fax', en: 'fax', fr: 'fax', es: 'fax', it: 'fax' }, example: { de: '+49 123 456789', en: '+49 123 456789', fr: '+49 123 456789', es: '+49 123 456789', it: '+49 123 456789' } }, { key: 'website_link_1', labels: { de: 'website_link_1', en: 'website_link_1', fr: 'lien_site_1', es: 'enlace_web_1', it: 'link_sito_1' }, example: { de: 'https://beispiel.de', en: 'https://example.com', fr: 'https://exemple.fr', es: 'https://ejemplo.es', it: 'https://esempio.it' } }, { key: 'website_link_2', labels: { de: 'website_link_2', en: 'website_link_2', fr: 'lien_site_2', es: 'enlace_web_2', it: 'link_sito_2' }, example: { de: '', en: '', fr: '', es: '', it: '' } }, { key: 'telefon_1', labels: { de: 'telefon_1', en: 'phone_1', fr: 'telephone_1', es: 'telefono_1', it: 'telefono_1' }, example: { de: '+49 123 111111', en: '+49 123 111111', fr: '+49 123 111111', es: '+49 123 111111', it: '+49 123 111111' } }, { key: 'telefon_2', labels: { de: 'telefon_2', en: 'phone_2', fr: 'telephone_2', es: 'telefono_2', it: 'telefono_2' }, example: { de: '+49 123 222222', en: '+49 123 222222', fr: '+49 123 222222', es: '+49 123 222222', it: '+49 123 222222' } }, { key: 'telefon_3', labels: { de: 'telefon_3', en: 'phone_3', fr: 'telephone_3', es: 'telefono_3', it: 'telefono_3' }, example: { de: '', en: '', fr: '', es: '', it: '' } }, { key: 'telefon_4', labels: { de: 'telefon_4', en: 'phone_4', fr: 'telephone_4', es: 'telefono_4', it: 'telefono_4' }, example: { de: '', en: '', fr: '', es: '', it: '' } }, { key: 'strasse', labels: { de: 'strasse', en: 'street', fr: 'rue', es: 'calle', it: 'via' }, example: { de: 'Musterstraße 123', en: 'Example Street 123', fr: 'Rue Exemple 123', es: 'Calle Ejemplo 123', it: 'Via Esempio 123' } }, { key: 'plz', labels: { de: 'plz', en: 'postal_code', fr: 'code_postal', es: 'codigo_postal', it: 'cap' }, example: { de: '12345', en: '12345', fr: '12345', es: '12345', it: '12345' } }, { key: 'ort', labels: { de: 'ort', en: 'city', fr: 'ville', es: 'ciudad', it: 'citta' }, example: { de: 'Musterstadt', en: 'Example City', fr: 'Ville Exemple', es: 'Ciudad Ejemplo', it: 'Città Esempio' } }, { key: 'lieferadresse_strasse', labels: { de: 'lieferadresse_strasse', en: 'delivery_street', fr: 'rue_livraison', es: 'calle_entrega', it: 'via_consegna' }, example: { de: '', en: '', fr: '', es: '', it: '' } }, { key: 'lieferadresse_plz', labels: { de: 'lieferadresse_plz', en: 'delivery_postal_code', fr: 'code_postal_livraison', es: 'codigo_postal_entrega', it: 'cap_consegna' }, example: { de: '', en: '', fr: '', es: '', it: '' } }, { key: 'lieferadresse_ort', labels: { de: 'lieferadresse_ort', en: 'delivery_city', fr: 'ville_livraison', es: 'ciudad_entrega', it: 'citta_consegna' }, example: { de: '', en: '', fr: '', es: '', it: '' } }, { key: 'kategorie_id', required: true, labels: { de: 'kategorie_id', en: 'category_id', fr: 'categorie_id', es: 'categoria_id', it: 'categoria_id' }, example: { de: '1', en: '1', fr: '1', es: '1', it: '1' } }, { key: 'prioritaet_id', required: true, labels: { de: 'prioritaet_id', en: 'priority_id', fr: 'priorite_id', es: 'prioridad_id', it: 'priorita_id' }, example: { de: '1', en: '1', fr: '1', es: '1', it: '1' } }, { key: 'branche_id', required: true, labels: { de: 'branche_id', en: 'industry_id', fr: 'secteur_id', es: 'sector_id', it: 'settore_id' }, example: { de: '1', en: '1', fr: '1', es: '1', it: '1' } }, { key: 'bemerkung', labels: { de: 'bemerkung', en: 'note', fr: 'remarque', es: 'nota', it: 'nota' }, example: { de: 'Wichtiger Kunde', en: 'Important customer', fr: 'Client important', es: 'Cliente importante', it: 'Cliente importante' } }, ]; const TEMPLATE_FILENAME: Record = { de: 'kontakte_vorlage.csv', en: 'contacts_template.csv', fr: 'modele_contacts.csv', es: 'plantilla_contactos.csv', it: 'modello_contatti.csv', }; // Reverse lookup: any localized header (lowercased, *-stripped) → canonical key const CONTACT_HEADER_ALIASES: Record = (() => { const map: Record = {}; for (const col of CONTACT_COLUMNS) { map[col.key.toLowerCase()] = col.key; for (const loc of CSV_LOCALES) map[col.labels[loc].toLowerCase()] = col.key; } return map; })(); const normalizeContactHeader = (header: string): string => { const clean = header.replace(/\*/g, '').trim().toLowerCase(); return CONTACT_HEADER_ALIASES[clean] || clean; // unknown headers pass through (ignored downstream) }; /** Canonical column keys in template order — for building export rows. */ export const CONTACT_CSV_KEYS: (keyof ParsedContact)[] = CONTACT_COLUMNS.map(c => c.key); /** Localized CSV headers in the active UI language (identical to the import template). */ export const getContactCsvHeaders = (): string[] => CONTACT_COLUMNS.map(c => c.labels[activeCsvLocale()] + (c.required ? '*' : '')); /** Localized fallback base name for an exported contact file (when the company has no name). */ const CONTACT_EXPORT_BASENAME: Record = { de: 'kontakt', en: 'contact', fr: 'contact', es: 'contacto', it: 'contatto', }; export const contactExportFallbackName = (): string => CONTACT_EXPORT_BASENAME[activeCsvLocale()]; /** * Escape CSV field (wrap in quotes if contains special characters) */ export const escapeCSVField = (field: string): string => { if (!field) return ''; if (field.includes(',') || field.includes('"') || field.includes('\n')) { return `"${field.replace(/"/g, '""')}"`; } return field; }; /** * Parse CSV line handling quoted fields properly (RFC 4180) */ export const parseCSVLine = (line: string): string[] => { const result: string[] = []; let current = ''; let inQuotes = false; for (let i = 0; i < line.length; i++) { const char = line[i]; const nextChar = line[i + 1]; if (char === '"') { if (inQuotes && nextChar === '"') { current += '"'; i++; } else { inQuotes = !inQuotes; } } else if (char === ',' && !inQuotes) { result.push(current.trim()); current = ''; } else { current += char; } } result.push(current.trim()); return result; }; /** * Parse CSV text into array of contact objects */ export const parseCSV = (text: string): ParsedContact[] => { const lines = text.split('\n').filter(line => line.trim()); if (lines.length < 2) return []; //if no rows after header or empty // Normalize headers across all supported languages → canonical keys, // so a template downloaded in any language re-imports correctly. const headers = parseCSVLine(lines[0]).map(normalizeContactHeader); const rows: ParsedContact[] = []; for (let i = 1; i < lines.length; i++) { const values = parseCSVLine(lines[i]); const row: any = {}; headers.forEach((header, index) => { row[header] = values[index]?.trim() || ''; // Also trim values }); rows.push(row); } return rows; }; /** * Validate a single contact row */ export const validateContact = (contact: ParsedContact): ValidationResult => { const errors: string[] = []; // Required fields if (!contact.firmenname?.trim()) { errors.push('Firmenname ist erforderlich'); } if (!contact.kategorie_id?.trim()) { errors.push('Kategorie ID ist erforderlich'); } if (!contact.prioritaet_id?.trim()) { errors.push('Priorität ID ist erforderlich'); } if (!contact.branche_id?.trim()) { errors.push('Branche ID ist erforderlich'); } // if (!contact.verantwortlicher?.trim()) { // errors.push('Verantwortlicher ist erforderlich'); // } // Email validation if (contact.email) { const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/; if (!emailRegex.test(contact.email)) { errors.push('Ungültige E-Mail-Adresse'); } } // PLZ validation if (contact.plz) { const plzRegex = /^[0-9]{3,10}$/; if (!plzRegex.test(contact.plz)) { errors.push('Ungültige PLZ'); } } // Numeric ID validation if (contact.kategorie_id && isNaN(Number(contact.kategorie_id))) { errors.push('Kategorie ID muss eine Zahl sein'); } if (contact.prioritaet_id && isNaN(Number(contact.prioritaet_id))) { errors.push('Priorität ID muss eine Zahl sein'); } if (contact.branche_id && isNaN(Number(contact.branche_id))) { errors.push('Branche ID muss eine Zahl sein'); } return { valid: errors.length === 0, errors, }; }; /** * Check if contact is duplicate */ export const checkDuplicate = async ( firmenname: string, email: string ): Promise<{ isDuplicate: boolean; reason?: string }> => { try { const csrfToken = document.querySelector('meta[name="csrf-token"]')?.getAttribute('content') || ''; const response = await fetch('/contacts/check-duplicate', { method: 'POST', headers: { 'Content-Type': 'application/json', 'Accept': 'application/json', 'X-CSRF-TOKEN': csrfToken, }, credentials: 'include', body: JSON.stringify({ firmenname: firmenname, email: email || null, }), }); if (response.ok) { const result = await response.json(); return { isDuplicate: result.isDuplicate, reason: result.reason, }; } } catch (error) { console.error('Duplicate check error:', error); } return { isDuplicate: false }; }; /** * Import a single contact to the backend */ export const importContact = async (contact: ParsedContact): Promise => { try { const csrfToken = document.querySelector('meta[name="csrf-token"]')?.getAttribute('content') || ''; const websiteLinks = [contact.website_link_1, contact.website_link_2] .filter(link => link) .join(', '); const response = await fetch('/contacts/saveAdr', { method: 'POST', headers: { 'Content-Type': 'application/json', 'Accept': 'application/json', 'X-CSRF-TOKEN': csrfToken, }, credentials: 'include', body: JSON.stringify({ id: 0, firmenname: contact.firmenname, kurzname: contact.abkuerzung || '', firmenname2: contact.schlagwoerter || '', email: contact.email || '', fax1: contact.fax || '', Web: websiteLinks || '', telefon1: contact.telefon_1 || '', telefon2: contact.telefon_2 || '', telefon3: contact.telefon_3 || '', strasse: contact.strasse || '', plz: contact.plz || '', ort: contact.ort || '', liefer_strasse: contact.lieferadresse_strasse || contact.strasse || '', liefer_plz: contact.lieferadresse_plz || contact.plz || '', liefer_ort: contact.lieferadresse_ort || contact.ort || '', adr_kunde: parseInt(contact.kategorie_id) || 0, adr_potiential: parseInt(contact.prioritaet_id) || 0, adr_branche: parseInt(contact.branche_id) || 0, //Bearbeiter: contact.verantwortlicher, Bemerkung: contact.bemerkung || '', }), }); return response.ok; } catch (error) { console.error('Import error:', error); return false; } }; /** * Generate and download CSV template */ export const generateCSVTemplate = (): void => { const loc = activeCsvLocale(); const headers = CONTACT_COLUMNS.map(c => c.labels[loc] + (c.required ? '*' : '')); const exampleRow = CONTACT_COLUMNS.map(c => c.example[loc]); const csvContent = [ headers.join(','), exampleRow.map(escapeCSVField).join(','), ].join('\n'); const blob = new Blob(['\ufeff' + csvContent], { type: 'text/csv;charset=utf-8;' }); const link = document.createElement('a'); link.href = URL.createObjectURL(blob); link.download = TEMPLATE_FILENAME[loc]; link.click(); URL.revokeObjectURL(link.href); };