soil-report-ai/dashboard/crop-analysis/water-test-data/water-uploadsubmit.php

<?php
/**
 * dashboard/crop-analysis/water-test-data/water-uploadsubmit.php
 *
 * Spreadsheet bulk-upload handler for water test records.
 * Requires the SpreadsheetReader library.
 *
 * NOTE: Column index → DB field mapping below must match the actual
 * spreadsheet format provided by the lab. Update $colMap as needed.
 */

if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

require_once __DIR__ . '/../../../config/database.php';
require_once __DIR__ . '/../../../lib/auth.php';

requireLogin();

$readerBase = __DIR__ . '/../../../client-assets/php/spreadsheet';
$readerFile = $readerBase . '/php-excel-reader/excel_reader2.php';
$spreadFile = $readerBase . '/SpreadsheetReader.php';

if (!file_exists($readerFile) || !file_exists($spreadFile)) {
    http_response_code(500);
    exit('<p class="text-danger">Spreadsheet reader library not found.</p>');
}

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('<p class="text-danger">Invalid file type. Please upload an Excel or CSV file.</p>');
}

$uploadDir  = __DIR__ . '/../../../client-assets/uploads/';
$uploadPath = $uploadDir . basename($_FILES['file']['name']);

if (!move_uploaded_file($_FILES['file']['tmp_name'], $uploadPath)) {
    exit('<p class="text-danger">Failed to upload file.</p>');
}

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

echo '<p>File uploaded — ' . (int) $totalSheet . ' sheet(s) found.</p>';

$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 '<p class="text-success">Import complete: '
   . (int) $inserted . ' record(s) inserted, '
   . (int) $skipped  . ' skipped.</p>';