') { return ''; } preg_match('/mailto:([^"]+)/', $email, $matches); return !empty($matches[1]) ? $matches[1] : $email; } // Function to fix encoding issues function fixEncoding($text) { if (empty($text)) return $text; $replacements = [ 'é' => 'é', 'è' => 'è', 'ê' => 'ê', 'ë' => 'ë', 'î' => 'î', 'ï' => 'ï', 'ô' => 'ô', 'ö' => 'ö', 'ù' => 'ù', 'û' => 'û', 'ü' => 'ü', 'ç' => 'ç', 'À' => 'À', 'â' => 'â', 'Ä' => 'Ä', 'È' => 'È', 'Ê' => 'Ê', 'Ì' => 'Ì', 'Î' => 'Î', 'ñ' => 'ñ', 'ó' => 'ó', 'õ' => 'õ', 'ø' => 'ø', 'ú' => 'ú', 'Ü' => 'Ü', 'ۏ' => 'Û', 'é' => 'é', 'è' => 'è', 'ê' => 'ê', 'ë' => 'ë', 'î' => 'î', 'ï' => 'ï', 'ô' => 'ô', 'ö' => 'ö', 'ù' => 'ù', 'û' => 'û', 'ü' => 'ü', 'ç' => 'ç', 'À' => 'À', 'â' => 'â', 'Ä' => 'Ä', 'È' => 'È', 'Ê' => 'Ê', 'ÃŒ' => 'Ì', 'ÃŽ' => 'Î', 'ñ' => 'ñ', 'ó' => 'ó', 'õ' => 'õ', 'ø' => 'ø', 'ú' => 'ú', 'Ãœ' => 'Ü', 'É' => 'É', '’' => '\'', 'Â' => 'Â', 'Ô' => 'Ô', 'Ï' => 'Ï' ]; return str_replace(array_keys($replacements), array_values($replacements), $text); } // Process each JSON entry $debugProcessed = []; foreach ($data as $entry) { $row = $entry['v']; // Fix encoding and convert Name to proper case $name = ucwords(strtolower(fixEncoding($row[0]))); // Name $address = fixEncoding($row[1]); // Address $city = fixEncoding($row[2]); // City (not in final CSV) $type = fixEncoding($row[3]); // Type (for type_prefix and Construction) $capacity = $row[4]; // Capacity $phone = $row[5]; // Phone $email = cleanEmail($row[6]); // Email $subsidized = $row[7] === 'Oui' ? '1' : '0'; // Subsidized $url = $row[8]; // URL (for Compo) // Parse Capacity and Poupons $newCapacity = ''; $poupons = '0'; if (preg_match('/(\d+)\s*places\s*\((\d+)\s*poupons\)/i', $capacity, $matches)) { $newCapacity = $matches[1]; // e.g., "80" $poupons = $matches[2]; // e.g., "20" } elseif (preg_match('/(\d+)\s*places/i', $capacity, $matches)) { $newCapacity = $matches[1]; // e.g., "62" } // Pouponnière $pouponniere = $poupons > 0 ? '1' : '0'; // Extract Compo from URL and determine type_prefix $compo = ''; $garderie_id = ''; $type_prefix = ''; if (preg_match('/Compo=(\d+)/', $url, $matches)) { $compo = $matches[1]; // e.g., "30058833" if ($type === 'Garderie subventionnée' || $type === 'Garderie non subventionnée') { $garderie_id = 'GARD' . $compo; // e.g., "GARD30058833" $type_prefix = 'GARD'; } elseif ($type === 'Centre petite enfance') { $garderie_id = 'CPE' . $compo; // e.g., "CPE30058833" $type_prefix = 'CPE'; } } else { if ($type === 'Garderie subventionnée' || $type === 'Garderie non subventionnée' || $type === 'Garderie subventionnée en cours de réalisation' || $type === 'Garderie non subventionnée en cours de réalisation') { $type_prefix = 'GARD'; } elseif ($type === 'Centre petite enfance' || $type === 'Centre petite enfance en cours de réalisation') { $type_prefix = 'CPE'; } } // Set Construction flag $construction = stripos($type, 'en cours de réalisation') !== false ? '1' : '0'; // Create new row in requested order $newRow = [ $name, // Name $construction, // Construction $type_prefix, // Type (GARD or CPE) $subsidized, // Subsidized (1 or 0) $pouponniere, // Pouponnière (1 or 0) $newCapacity, // Capacity $address, // Address $phone, // Phone $email // Email ]; $csvData[] = $newRow; // Debug: Log first 5 processed rows (Name, Construction, Type_prefix, Subsidized, Pouponnière) if (count($debugProcessed) < 5) { $debugProcessed[] = implode(',', [$name, $construction, $type_prefix, $subsidized, $pouponniere]); } } file_put_contents('debug_processed.txt', implode("\n", $debugProcessed)); // Sort by Name (first column) usort($csvData, function($a, $b) use ($headers) { if ($a[0] === $headers[0]) return -1; // Keep header first if ($b[0] === $headers[0]) return 1; return strcmp($a[0], $b[0]); }); // Write to CSV with UTF-8 BOM for Excel compatibility $fp = fopen('data.csv', 'w'); // Add UTF-8 BOM fwrite($fp, "\xEF\xBB\xBF"); foreach ($csvData as $row) { fputcsv($fp, $row); } fclose($fp); echo "CSV file 'data.csv' has been generated successfully.\n"; ?>