Spreadsheet reader library not found.

'); } require_once $readerFile; require_once $spreadFile; if (!isset($_POST['Submit'])) { exit; } $allowedMimes = [ 'application/vnd.ms-excel', 'text/xls', 'text/xlsx', 'text/csv', 'application/vnd.oasis.opendocument.spreadsheet', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', ]; if (!in_array($_FILES['file']['type'] ?? '', $allowedMimes, true)) { exit('

Invalid file type. Please upload an Excel or CSV file.

'); } $uploadDir = __DIR__ . '/../../../client-assets/uploads/'; $uploadPath = $uploadDir . basename($_FILES['file']['name']); if (!move_uploaded_file($_FILES['file']['tmp_name'], $uploadPath)) { exit('

Failed to upload file.

'); } $reader = new SpreadsheetReader($uploadPath); $totalSheet = count($reader->sheets()); echo '

File uploaded — ' . (int) $totalSheet . ' sheet(s) found.

'; $pdo = getDBConnection(); $userId = getCurrentUserId(); /** * Map spreadsheet column indices to soil_records column names. * Water test data is stored in soil_records pending a dedicated water_records table. * Adjust indices to match the lab's spreadsheet layout. */ $colMap = [ 0 => 'analysis_type', 1 => 'lab_no', 2 => 'batch_no', 3 => 'sample_id', 4 => 'site_id', 5 => 'crop', 6 => 'date_sampled', 7 => 'tec', 8 => 'cec', 9 => 'texture', 10 => 'gravel', 11 => 'colour', 12 => 'NO3_N', 13 => 'NH3_N', 14 => 'p_mehlick', 15 => 'p_bray2', 16 => 'p_morgan', 17 => 'k_morgan', 18 => 'ca_morgan', 19 => 'mg_morgan', 20 => 'na_morgan', 21 => 'ch_h2o', 22 => 'ocarbon', 23 => 'omatter', 24 => 'fe', 25 => 'ec', 26 => 'ph_cacl2', 27 => 'ph_h2o', 28 => 'paramag', 29 => 's_morgan', 30 => 'b_cacl2', 31 => 'mn_dtpa', 32 => 'zn_dtpa', 33 => 'fe_dtpa', 34 => 'cu_dtpa', 35 => 'al', 36 => 'sl_cacl2', 37 => 'm_dtpa', 38 => 'co_dtpa', 39 => 'se', 40 => 'ca_mehlick3', 41 => 'mg_mehlick3', 42 => 'k_mehlick3', 43 => 'na_mehlick3', 44 => 'al_mehlick3', ]; $columns = array_values($colMap); $placeholders = implode(', ', array_fill(0, count($columns) + 2, '?')); $colList = 'modx_user_id, rand, ' . implode(', ', array_map(fn($c) => "`$c`", $columns)); $stmt = $pdo->prepare( "INSERT INTO soil_records ($colList) VALUES ($placeholders)" ); $inserted = 0; $skipped = 0; for ($i = 0; $i < $totalSheet; $i++) { $reader->ChangeSheet($i); $firstRow = true; foreach ($reader as $row) { if ($firstRow) { $firstRow = false; continue; } // skip header row $rand = mt_rand(10000, 99999); $values = [$userId, $rand]; foreach ($colMap as $idx => $colName) { $val = isset($row[$idx]) ? trim((string) $row[$idx]) : null; $values[] = ($val === '') ? null : $val; } try { $stmt->execute($values); $inserted++; } catch (\PDOException $e) { $skipped++; } } } @unlink($uploadPath); echo '

Import complete: ' . (int) $inserted . ' record(s) inserted, ' . (int) $skipped . ' skipped.

';