excel-reader.php

<?php
class ExcelManager {
    private $excelFolder = 'excel/';
    private $archiveFolder = 'archives/';
    
    public function __construct() {
        // Create directories if they don't exist
        if (!is_dir($this->excelFolder)) {
            mkdir($this->excelFolder, 0777, true);
        }
        if (!is_dir($this->archiveFolder)) {
            mkdir($this->archiveFolder, 0777, true);
        }
    }
    
    /**
     * Get all Excel files in the excel folder
     */
    public function getExcelFiles() {
        $files = [];
        if (is_dir($this->excelFolder)) {
            $items = scandir($this->excelFolder);
            foreach ($items as $item) {
                if ($item !== '.' && $item !== '..') {
                    $ext = pathinfo($item, PATHINFO_EXTENSION);
                    if (in_array(strtolower($ext), ['xlsx', 'xls', 'csv'])) {
                        $files[] = $item;
                    }
                }
            }
        }
        return $files;
    }
    
    /**
     * Check for duplicate files before uploading
     */
    public function checkDuplicateFiles($uploadedFiles) {
        $existingFiles = $this->getExcelFiles();
        $duplicates = [];
        
        if (!empty($uploadedFiles['name'][0])) {
            for ($i = 0; $i < count($uploadedFiles['name']); $i++) {
                $filename = basename($uploadedFiles['name'][$i]);
                if (in_array($filename, $existingFiles)) {
                    $duplicates[] = $filename;
                }
            }
        }
        
        return $duplicates;
    }
    
    /**
     * Add Excel files to the system with duplicate checking
     */
    public function addExcelFiles($uploadedFiles, $overwrite = false) {
        $successCount = 0;
        $errorCount = 0;
        $messages = [];
        $skippedCount = 0;
        
        if (!empty($uploadedFiles['name'][0])) {
            for ($i = 0; $i < count($uploadedFiles['name']); $i++) {
                if ($uploadedFiles['error'][$i] === UPLOAD_ERR_OK) {
                    $filename = basename($uploadedFiles['name'][$i]);
                    $targetPath = $this->excelFolder . $filename;
                    
                    // Check if file is Excel or CSV
                    $ext = pathinfo($filename, PATHINFO_EXTENSION);
                    if (!in_array(strtolower($ext), ['xlsx', 'xls', 'csv'])) {
                        $messages[] = "Le fichier '$filename' n'est pas un fichier Excel valide.";
                        $errorCount++;
                        continue;
                    }
                    
                    // Check if file already exists
                    if (file_exists($targetPath)) {
                        if ($overwrite) {
                            // Remove existing file before uploading new one
                            if (!unlink($targetPath)) {
                                $messages[] = "Impossible de supprimer l'ancien fichier '$filename'.";
                                $errorCount++;
                                continue;
                            }
                        } else {
                            $messages[] = "Le fichier '$filename' existe déjà et a été ignoré.";
                            $skippedCount++;
                            continue;
                        }
                    }
                    
                    if (move_uploaded_file($uploadedFiles['tmp_name'][$i], $targetPath)) {
                        $successCount++;
                    } else {
                        $messages[] = "Erreur lors du téléchargement de '$filename'.";
                        $errorCount++;
                    }
                } else {
                    $messages[] = "Erreur avec le fichier '" . $uploadedFiles['name'][$i] . "'.";
                    $errorCount++;
                }
            }
        }
        
        $message = "$successCount fichier(s) ajouté(s) avec succès.";
        if ($skippedCount > 0) {
            $message .= " $skippedCount fichier(s) ignoré(s) (doublons).";
        }
        if ($errorCount > 0) {
            $message .= " $errorCount erreur(s). " . implode(' ', $messages);
        }
        
        return [
            'success' => $successCount > 0,
            'message' => $message,
            'added_count' => $successCount,
            'skipped_count' => $skippedCount,
            'error_count' => $errorCount
        ];
    }

    /**
     * Update an existing file with a new one
     */
    public function updateFile($filename, $newFile) {
        $filePath = $this->excelFolder . $filename;
        
        if (!file_exists($filePath)) {
            return [
                'success' => false,
                'message' => "Fichier '$filename' non trouvé."
            ];
        }
        
        if ($newFile['error'] === UPLOAD_ERR_OK) {
            $newFilename = basename($newFile['name']);
            $newFilePath = $this->excelFolder . $newFilename;
            
            // Check if file is Excel or CSV
            $ext = pathinfo($newFilename, PATHINFO_EXTENSION);
            if (!in_array(strtolower($ext), ['xlsx', 'xls', 'csv'])) {
                return [
                    'success' => false,
                    'message' => "Le fichier '$newFilename' n'est pas un fichier Excel valide."
                ];
            }
            
            // Remove old file
            if (!unlink($filePath)) {
                return [
                    'success' => false,
                    'message' => "Erreur lors de la suppression de l'ancien fichier '$filename'."
                ];
            }
            
            // Move new file
            if (move_uploaded_file($newFile['tmp_name'], $newFilePath)) {
                return [
                    'success' => true,
                    'message' => "Fichier '$filename' mis à jour avec '$newFilename' avec succès."
                ];
            } else {
                return [
                    'success' => false,
                    'message' => "Erreur lors du téléchargement du nouveau fichier '$newFilename'."
                ];
            }
        } else {
            return [
                'success' => false,
                'message' => "Erreur avec le fichier '" . $newFile['name'] . "'."
            ];
        }
    }
    
    /**
     * Read data from all Excel files
     */
    public function readAllExcelData() {
        $allData = [];
        $files = $this->getExcelFiles();
        
        foreach ($files as $file) {
            $fileData = $this->readExcelFile($file);
            if (!empty($fileData)) {
                $allData = array_merge($allData, $fileData);
            }
        }
        
        return $allData;
    }
    
    /**
     * Read data from a single Excel file
     */
    private function readExcelFile($filename) {
        $filePath = $this->excelFolder . $filename;
        $data = [];
        
        try {
            $ext = pathinfo($filename, PATHINFO_EXTENSION);
            
            if (strtolower($ext) === 'csv') {
                $data = $this->readCSVFile($filePath, $filename);
            } else {
                $data = $this->readBinaryExcelFile($filePath, $filename);
            }
            
        } catch (Exception $e) {
            // Log error but continue processing other files
            error_log("Error reading Excel file $filename: " . $e->getMessage());
        }
        
        return $data;
    }
    
    /**
     * Read data from archive files
     */
    public function readArchiveData($archiveName) {
        $archivePath = $this->archiveFolder . $archiveName . '/';
        $allData = [];
        
        if (!is_dir($archivePath)) {
            return [
                'success' => false,
                'message' => "Archive '$archiveName' non trouvée.",
                'data' => []
            ];
        }
        
        $files = scandir($archivePath);
        foreach ($files as $file) {
            if ($file !== '.' && $file !== '..') {
                $ext = pathinfo($file, PATHINFO_EXTENSION);
                if (in_array(strtolower($ext), ['xlsx', 'xls', 'csv'])) {
                    $filePath = $archivePath . $file;
                    $fileData = [];
                    
                    try {
                        if (strtolower($ext) === 'csv') {
                            $fileData = $this->readCSVFile($filePath, $file);
                        } else {
                            $fileData = $this->readBinaryExcelFile($filePath, $file);
                        }
                        
                        if (!empty($fileData)) {
                            $allData = array_merge($allData, $fileData);
                        }
                    } catch (Exception $e) {
                        error_log("Error reading archive file $file: " . $e->getMessage());
                    }
                }
            }
        }
        
        return [
            'success' => true,
            'message' => count($allData) . " enregistrement(s) trouvé(s) dans l'archive.",
            'data' => $allData,
            'archive_name' => $archiveName
        ];
    }
    
    /**
     * Search in archive data
     */
    public function searchInArchive($archiveName, $searchTerm) {
        $archiveData = $this->readArchiveData($archiveName);
        
        if (!$archiveData['success']) {
            return $archiveData;
        }
        
        $results = [];
        $searchTerm = strtolower($searchTerm);
        
        foreach ($archiveData['data'] as $row) {
            foreach ($row as $value) {
                if (strpos(strtolower($value), $searchTerm) !== false) {
                    $results[] = $row;
                    break;
                }
            }
        }
        
        return [
            'success' => true,
            'message' => count($results) . " résultat(s) trouvé(s) pour '$searchTerm' dans l'archive '$archiveName'",
            'data' => $results,
            'archive_name' => $archiveName,
            'search_term' => $searchTerm
        ];
    }
    
    /**
     * Read CSV file
     */
    private function readCSVFile($filePath, $filename) {
        $data = [];
        
        if (($handle = fopen($filePath, "r")) !== FALSE) {
            $headers = [];
            $rowIndex = 0;
            
            // Detect delimiter
            $firstLine = fgets($handle);
            $delimiter = $this->detectDelimiter($firstLine);
            fseek($handle, 0);
            
            while (($row = fgetcsv($handle, 1000, $delimiter)) !== FALSE) {
                if ($rowIndex === 0) {
                    // First row is headers
                    $headers = $this->cleanHeaders($row);
                } else {
                    // Data rows
                    $rowData = [];
                    foreach ($headers as $index => $header) {
                        $rowData[$header] = isset($row[$index]) ? $this->cleanValue($row[$index]) : 'N/A';
                    }
                    
                    // Add source file information
                    $rowData['_source_file'] = $filename;
                    $data[] = $rowData;
                }
                $rowIndex++;
            }
            fclose($handle);
        }
        
        return $data;
    }
    
    /**
     * Read binary Excel file (XLSX, XLS)
     */
    private function readBinaryExcelFile($filePath, $filename) {
        $data = [];
        
        // For binary Excel files, we'll use a simple approach to extract data
        // This is a basic implementation that might need adjustment
        
        // First, try to read as ZIP (for XLSX)
        if ($this->isZipFile($filePath)) {
            $data = $this->readXLSXFile($filePath, $filename);
        } else {
            // For XLS files or if ZIP method fails, use string extraction
            $data = $this->extractDataFromBinary($filePath, $filename);
        }
        
        return $data;
    }
    
    /**
     * Check if file is a ZIP file (XLSX format)
     */
    private function isZipFile($filePath) {
        $fileSignature = file_get_contents($filePath, false, null, 0, 4);
        return $fileSignature === "PK\x03\x04";
    }
    
    /**
     * Read XLSX file by extracting shared strings and sheet data
     */
    private function readXLSXFile($filePath, $filename) {
        $data = [];
        
        // Create a temporary directory for extraction
        $tempDir = sys_get_temp_dir() . '/excel_extract_' . uniqid();
        mkdir($tempDir, 0777, true);
        
        // Copy and extract the XLSX file
        $zip = new ZipArchive();
        if ($zip->open($filePath) === TRUE) {
            $zip->extractTo($tempDir);
            $zip->close();
            
            // Read shared strings if available
            $sharedStrings = [];
            $sharedStringsFile = $tempDir . '/xl/sharedStrings.xml';
            if (file_exists($sharedStringsFile)) {
                $sharedStrings = $this->parseSharedStrings($sharedStringsFile);
            }
            
            // Read sheet data
            $sheetFile = $tempDir . '/xl/worksheets/sheet1.xml';
            if (file_exists($sheetFile)) {
                $data = $this->parseSheetData($sheetFile, $sharedStrings, $filename);
            }
            
            // Clean up
            $this->deleteDirectory($tempDir);
        }
        
        return $data;
    }
    
    /**
     * Parse shared strings from XML
     */
    private function parseSharedStrings($xmlFile) {
        $sharedStrings = [];
        $xml = simplexml_load_file($xmlFile);
        
        if ($xml && isset($xml->si)) {
            foreach ($xml->si as $stringItem) {
                $sharedStrings[] = (string)$stringItem->t;
            }
        }
        
        return $sharedStrings;
    }
    
    /**
     * Parse sheet data from XML
     */
    private function parseSheetData($xmlFile, $sharedStrings, $filename) {
        $data = [];
        $xml = simplexml_load_file($xmlFile);
        
        if ($xml && isset($xml->sheetData)) {
            $headers = [];
            $firstRow = true;
            
            foreach ($xml->sheetData->row as $row) {
                $rowData = [];
                $colIndex = 0;
                
                foreach ($row->c as $cell) {
                    $cellValue = '';
                    $cellAttributes = $cell->attributes();
                    
                    if (isset($cell->v)) {
                        if (isset($cellAttributes['t']) && (string)$cellAttributes['t'] === 's') {
                            // Shared string
                            $stringIndex = (int)$cell->v;
                            if (isset($sharedStrings[$stringIndex])) {
                                $cellValue = $sharedStrings[$stringIndex];
                            }
                        } else {
                            // Direct value
                            $cellValue = (string)$cell->v;
                        }
                    }
                    
                    if ($firstRow) {
                        $headers[$colIndex] = $this->cleanValue($cellValue) ?: 'Colonne_' . ($colIndex + 1);
                    } else {
                        if (isset($headers[$colIndex])) {
                            $rowData[$headers[$colIndex]] = $this->cleanValue($cellValue);
                        }
                    }
                    
                    $colIndex++;
                }
                
                if ($firstRow) {
                    $firstRow = false;
                } else if (!empty($rowData)) {
                    $rowData['_source_file'] = $filename;
                    $data[] = $rowData;
                }
            }
        }
        
        return $data;
    }
    
    /**
     * Extract data from binary file using string extraction
     */
    private function extractDataFromBinary($filePath, $filename) {
        $data = [];
        
        // Read file content
        $content = file_get_contents($filePath);
        
        // Extract readable strings (minimum 3 characters)
        preg_match_all('/[\\x20-\\x7E]{4,}/', $content, $matches);
        
        if (!empty($matches[0])) {
            $headers = [];
            $currentRow = [];
            $rowIndex = 0;
            
            foreach ($matches[0] as $string) {
                $string = trim($string);
                
                // Skip very long strings (likely binary data)
                if (strlen($string) > 100) continue;
                
                // Skip common binary patterns
                if (preg_match('/^[0-9\.\-]+$/', $string)) continue;
                
                if ($rowIndex === 0) {
                    $headers[] = $string ?: 'Colonne_' . (count($headers) + 1);
                } else {
                    $headerIndex = count($currentRow);
                    if (isset($headers[$headerIndex])) {
                        $currentRow[$headers[$headerIndex]] = $string;
                    }
                    
                    // Assume 5 columns per row (adjust based on your data)
                    if (count($currentRow) >= 5) {
                        $currentRow['_source_file'] = $filename;
                        $data[] = $currentRow;
                        $currentRow = [];
                    }
                }
                
                $rowIndex++;
            }
            
            // Add the last row if not empty
            if (!empty($currentRow)) {
                $currentRow['_source_file'] = $filename;
                $data[] = $currentRow;
            }
        }
        
        return $data;
    }
    
    /**
     * Detect CSV delimiter
     */
    private function detectDelimiter($firstLine) {
        $delimiters = [',', ';', "\t", '|'];
        $counts = [];
        
        foreach ($delimiters as $delimiter) {
            $counts[$delimiter] = count(str_getcsv($firstLine, $delimiter));
        }
        
        return array_search(max($counts), $counts);
    }
    
    /**
     * Clean and standardize headers
     */
    private function cleanHeaders($headers) {
        $cleaned = [];
        foreach ($headers as $index => $header) {
            $cleanHeader = trim(preg_replace('/[^\w\s]/', ' ', $header));
            $cleaned[] = $cleanHeader ?: 'Colonne_' . ($index + 1);
        }
        return $cleaned;
    }
    
    /**
     * Clean cell values - improved to handle various data types
     */
    private function cleanValue($value) {
        if (is_null($value)) {
            return 'N/A';
        }
        
        $value = trim($value);
        
        // Remove non-printable characters but keep accented characters
        $value = preg_replace('/[^\x20-\x7E\xC0-\xFF]/u', '', $value);
        
        // Convert boolean values to readable text
        if ($value === true || $value === 'true' || $value === 'TRUE' || $value === '1') {
            return 'Oui';
        }
        if ($value === false || $value === 'false' || $value === 'FALSE' || $value === '0') {
            return 'Non';
        }
        
        return empty($value) ? 'N/A' : $value;
    }
    
    /**
     * Delete directory recursively
     */
    private function deleteDirectory($dir) {
        if (!is_dir($dir)) return;
        
        $files = array_diff(scandir($dir), ['.', '..']);
        foreach ($files as $file) {
            $path = $dir . '/' . $file;
            is_dir($path) ? $this->deleteDirectory($path) : unlink($path);
        }
        
        rmdir($dir);
    }
    
    /**
     * Search through all data
     */
    public function search($term) {
        $allData = $this->readAllExcelData();
        $results = [];
        
        if (empty($term)) {
            return [
                'data' => $allData,
                'message' => 'Affichage de toutes les données'
            ];
        }
        
        $term = strtolower($term);
        foreach ($allData as $row) {
            foreach ($row as $value) {
                if (strpos(strtolower($value), $term) !== false) {
                    $results[] = $row;
                    break;
                }
            }
        }
        
        return [
            'data' => $results,
            'message' => count($results) . ' résultat(s) trouvé(s) pour "' . $term . '"'
        ];
    }
    
    /**
     * Check for duplicates
     */
    public function checkDuplicates($type) {
        if ($type === 'files') {
            return $this->checkFileDuplicates();
        } else {
            return $this->checkIndividualDuplicates();
        }
    }
    
    /**
     * Check for duplicate files
     */
    private function checkFileDuplicates() {
        $files = $this->getExcelFiles();
        $fileHashes = [];
        $duplicates = [];
        
        foreach ($files as $file) {
            $filePath = $this->excelFolder . $file;
            $fileHash = md5_file($filePath);
            
            if (isset($fileHashes[$fileHash])) {
                $duplicates[] = [
                    'file1' => $fileHashes[$fileHash],
                    'file2' => $file
                ];
            } else {
                $fileHashes[$fileHash] = $file;
            }
        }
        
        $message = empty($duplicates) ? 
            "Aucun doublon de fichiers trouvé." : 
            count($duplicates) . " doublon(s) de fichiers détecté(s).";
            
        return [
            'data' => $duplicates,
            'message' => $message
        ];
    }
    
    /**
     * Check for duplicate individuals
     */
    private function checkIndividualDuplicates() {
        $allData = $this->readAllExcelData();
        $individuals = [];
        $duplicates = [];
        
        foreach ($allData as $row) {
            $key = $this->getIndividualKey($row);
            if (isset($individuals[$key])) {
                $duplicates[] = [
                    'individual' => $row,
                    'files' => [$individuals[$key], $row['_source_file']]
                ];
            } else {
                $individuals[$key] = $row['_source_file'];
            }
        }
        
        $message = empty($duplicates) ? 
            "Aucun doublon d'individu trouvé." : 
            count($duplicates) . " doublon(s) d'individu détecté(s).";
            
        return [
            'data' => $duplicates,
            'message' => $message
        ];
    }
    
    /**
     * Create a unique key for an individual
     */
    private function getIndividualKey($row) {
        // Try multiple possible field names for name and CIN
        $nameFields = ['Nom', 'nom', 'Name', 'name', 'NOM', 'Last Name', 'last name'];
        $firstNameFields = ['Prénom', 'prenom', 'Prenom', 'PRENOM', 'First Name', 'first name'];
        $cinFields = ['CIN', 'cin', 'ID', 'id', 'Id', 'Identifiant', 'identifiant', 'CNE', 'cne', 'Numéro CIN', 'numéro cin', 'Carte Identité'];
        
        $lastName = '';
        $firstName = '';
        $cin = '';
        
        // Find last name field
        foreach ($nameFields as $field) {
            if (isset($row[$field]) && $this->isValidValue($row[$field])) {
                $lastName = $row[$field];
                break;
            }
        }
        
        // Find first name field
        foreach ($firstNameFields as $field) {
            if (isset($row[$field]) && $this->isValidValue($row[$field])) {
                $firstName = $row[$field];
                break;
            }
        }
        
        // Find CIN field - more comprehensive search
        foreach ($cinFields as $field) {
            if (isset($row[$field]) && $this->isValidValue($row[$field])) {
                $cin = $row[$field];
                break;
            }
        }
        
        // If no CIN found, try to find any field that might contain an ID
        if (empty($cin)) {
            foreach ($row as $key => $value) {
                if ($key !== '_source_file' && $this->looksLikeId($value)) {
                    $cin = $value;
                    break;
                }
            }
        }
        
        return md5(strtolower(trim($lastName . $firstName))) . '_' . md5(strtolower(trim($cin)));
    }
    
    /**
     * Check if a value is valid (not empty, not N/A)
     */
    public function isValidValue($value) {
        if (is_null($value)) return false;
        $value = trim($value);
        return !empty($value) && $value !== 'N/A' && $value !== 'n/a';
    }
    
    /**
     * Check if a value looks like a name
     */
    public function looksLikeName($value) {
        if (!$this->isValidValue($value)) return false;
        
        $value = trim($value);
        // Names typically contain letters, spaces, and maybe hyphens or apostrophes
        return preg_match('/^[a-zA-Z\s\-\.\']+$/', $value) && strlen($value) > 1;
    }
    
    /**
     * Check if a value looks like an ID
     */
    public function looksLikeId($value) {
        if (!$this->isValidValue($value)) return false;
        
        $value = trim($value);
        // IDs can be alphanumeric and might contain special characters
        // Check if it's not obviously something else
        if ($this->looksLikeName($value)) return false;
        if (is_numeric($value) && strlen($value) > 3) return true;
        if (preg_match('/^[A-Za-z0-9]{4,20}$/', $value)) return true;
        
        return false;
    }
    
    /**
     * Get establishments statistics with improved field detection
     */
    public function getEstablishments() {
        $allData = $this->readAllExcelData();
        $establishments = [];
        
        foreach ($allData as $row) {
            // Try multiple possible field names with better detection
            $etablissement = $this->findFieldValue($row, [
                'Etablissement', 'etablissement', 'Establishment', 'establishment', 
                'ETABLISSEMENT', 'Établissement', 'établissement', 'School', 'school',
                'University', 'university', 'College', 'college', 'Institut', 'institut'
            ]);
            
            $sigle = $this->findFieldValue($row, [
                'Sigle', 'sigle', 'Code', 'code', 'SIGLE', 'CODE', 'Acronym', 'acronym',
                'Abbreviation', 'abbreviation', 'Short Name', 'short name'
            ]);
            
            $accredited = $this->findFieldValue($row, [
                'Accrédité', 'accrédité', 'Accredited', 'accredited', 'ACC REDITE', 
                'Habilitée', 'habilitée', 'Accréditation', 'accréditation', 'Status',
                'status', 'Statut', 'statut', 'Habilitation', 'habilitation'
            ], true); // Allow boolean-like values
            
            $filiere = $this->findFieldValue($row, [
                'Filière', 'filière', 'Field', 'field', 'FILIERE', 'filiere', 
                'Spécialité', 'spécialité', 'Specialite', 'specialite', 'Major',
                'major', 'Program', 'program', 'Programme', 'programme'
            ]);
            
            // If no establishment found, try to infer from other fields
            if ($etablissement === 'N/A') {
                foreach ($row as $key => $value) {
                    if ($key !== '_source_file' && $this->looksLikeEstablishment($value)) {
                        $etablissement = $value;
                        break;
                    }
                }
            }
            
            if (!isset($establishments[$etablissement])) {
                $establishments[$etablissement] = [
                    'name' => $etablissement,
                    'student_count' => 0,
                    'sigle' => $sigle,
                    'accredited' => $accredited,
                    'filières' => []
                ];
            }
            
            $establishments[$etablissement]['student_count']++;
            
            if ($filiere !== 'N/A' && !empty(trim($filiere))) {
                $establishments[$etablissement]['filières'][] = $filiere;
            }
        }
        
        return [
            'data' => array_values($establishments),
            'message' => count($establishments) . ' établissement(s) trouvé(s)'
        ];
    }
    
    /**
     * Find field value with flexible matching
     */
    private function findFieldValue($row, $possibleFields, $allowBoolean = false) {
        foreach ($possibleFields as $field) {
            if (isset($row[$field]) && $this->isValidValue($row[$field])) {
                $value = $row[$field];
                // Convert boolean-like values if allowed
                if ($allowBoolean) {
                    if ($value === true || $value === 'true' || $value === 'TRUE' || $value === '1' || $value === 'Oui' || $value === 'oui') {
                        return 'Oui';
                    }
                    if ($value === false || $value === 'false' || $value === 'FALSE' || $value === '0' || $value === 'Non' || $value === 'non') {
                        return 'Non';
                    }
                }
                return $value;
            }
        }
        return 'N/A';
    }
    
    /**
     * Check if a value looks like an establishment name
     */
    private function looksLikeEstablishment($value) {
        if (!$this->isValidValue($value)) return false;
        
        $value = trim($value);
        // Establishment names often contain words like "University", "College", "School", "Institute"
        $establishmentKeywords = [
            'university', 'college', 'school', 'institute', 'institut', 'academy', 
            'faculty', 'faculté', 'école', 'polytechnique', 'université'
        ];
        
        $lowerValue = strtolower($value);
        foreach ($establishmentKeywords as $keyword) {
            if (strpos($lowerValue, $keyword) !== false) {
                return true;
            }
        }
        
        return false;
    }
    
    /**
     * Get all data with statistics
     */
    public function getAllData() {
        $files = $this->getExcelFiles();
        if (empty($files)) {
            return [
                'data' => [],
                'total_students' => 0,
                'total_establishments' => 0,
                'total_files' => 0
            ];
        }
        
        $allData = $this->readAllExcelData();
        $establishments = $this->getEstablishments();
        
        return [
            'data' => $allData,
            'total_students' => count($allData),
            'total_establishments' => count($establishments['data']),
            'total_files' => count($files)
        ];
    }
    
    /**
     * Display data in a table format
     */
    public function displayDataTable($data) {
        if (empty($data)) {
            echo '<p class="no-data">Aucune donnée à afficher.</p>';
            return;
        }
        
        echo '<table class="data-table">';
        echo '<thead><tr>';
        
        // Get headers from first row
        $headers = array_keys($data[0]);
        foreach ($headers as $header) {
            if ($header !== '_source_file') {
                echo '<th>' . htmlspecialchars($header) . '</th>';
            }
        }
        echo '<th>Actions</th>';
        echo '</tr></thead>';
        
        echo '<tbody>';
        foreach ($data as $index => $row) {
            echo '<tr>';
            foreach ($headers as $header) {
                if ($header !== '_source_file') {
                    echo '<td>' . htmlspecialchars($row[$header] ?? 'N/A') . '</td>';
                }
            }
            echo '<td><button class="cmd-button small" onclick="showDetails(' . $index . ')">Détails</button></td>';
            echo '</tr>';
        }
        echo '</tbody>';
        echo '</table>';
        
        // Store data for details modal
        echo '<script>window.currentData = ' . json_encode($data) . ';</script>';
    }
    
    /**
     * Delete a file - FIXED VERSION
     */
    public function deleteFile($filename) {
        // Sanitize the filename to prevent directory traversal
        $filename = basename($filename);
        $filePath = $this->excelFolder . $filename;
        
        // Check if file exists and is in the correct directory
        if (file_exists($filePath) && is_file($filePath)) {
            // Double check we're in the excel folder to prevent accidental deletion
            $realFilePath = realpath($filePath);
            $realExcelFolder = realpath($this->excelFolder);
            
            if ($realFilePath && $realExcelFolder && strpos($realFilePath, $realExcelFolder) === 0) {
                if (unlink($realFilePath)) {
                    return [
                        'success' => true,
                        'message' => "Fichier '$filename' supprimé avec succès."
                    ];
                } else {
                    return [
                        'success' => false,
                        'message' => "Erreur lors de la suppression du fichier '$filename'."
                    ];
                }
            } else {
                return [
                    'success' => false,
                    'message' => "Chemin de fichier invalide."
                ];
            }
        } else {
            return [
                'success' => false,
                'message' => "Fichier '$filename' non trouvé."
            ];
        }
    }
    
    /**
     * Create archive of current files (moves files instead of copying)
     */
    public function createArchive() {
        $archiveName = 'archive_' . date('Y-m-d_H-i-s');
        $archivePath = $this->archiveFolder . $archiveName . '/';
        
        if (!is_dir($archivePath)) {
            mkdir($archivePath, 0777, true);
        }
        
        $files = $this->getExcelFiles();
        $movedCount = 0;
        $errorCount = 0;
        
        foreach ($files as $file) {
            $source = $this->excelFolder . $file;
            $destination = $archivePath . $file;
            
            if (rename($source, $destination)) {
                $movedCount++;
            } else {
                $errorCount++;
            }
        }
        
        $message = $movedCount > 0 ? 
            "Archive '$archiveName' créée avec $movedCount fichier(s) déplacé(s)." :
            "Erreur lors de la création de l'archive.";
            
        if ($errorCount > 0) {
            $message .= " $errorCount erreur(s) lors du déplacement des fichiers.";
        }
            
        return [
            'success' => $movedCount > 0,
            'message' => $message
        ];
    }
    
    /**
     * Get list of archived folders
     */
    public function getArchivedFolders() {
        $folders = [];
        if (is_dir($this->archiveFolder)) {
            $items = scandir($this->archiveFolder);
            foreach ($items as $item) {
                if ($item !== '.' && $item !== '..' && is_dir($this->archiveFolder . $item)) {
                    $folders[] = $item;
                }
            }
        }
        return $folders;
    }
    
    /**
     * Delete an archive
     */
    public function deleteArchive($archiveName) {
        $archivePath = $this->archiveFolder . $archiveName . '/';
        
        if (is_dir($archivePath)) {
            // Remove all files in the archive directory
            $files = glob($archivePath . '*');
            foreach ($files as $file) {
                if (is_file($file)) {
                    unlink($file);
                }
            }
            
            // Remove the directory
            if (rmdir($archivePath)) {
                return [
                    'success' => true,
                    'message' => "Archive '$archiveName' supprimée avec succès."
                ];
            } else {
                return [
                    'success' => false,
                    'message' => "Erreur lors de la suppression de l'archive '$archiveName'."
                ];
            }
        } else {
            return [
                'success' => false,
                'message' => "Archive '$archiveName' non trouvée."
            ];
        }
    }
}
?>