soil-report-ai/controllers/soilTestSubmit.php

<?php
/**
 * controllers/soilTestSubmit.php
 *
 * Secure soil test form submission handler.
 * Replaces modX [[!soilformSubmit]] snippet.
 */

// Start session if not already started
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

// Include configuration and dependencies
require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../lib/auth.php';
require_once __DIR__ . '/../lib/validation.php';
require_once __DIR__ . '/../lib/csrf.php';

// Check authentication
if (!isLoggedIn()) {
    http_response_code(403);
    die('Access denied: User not authenticated');
}

// Check CSRF token
if (!verifyCsrfToken($_POST['csrf_token'] ?? '')) {
    http_response_code(403);
    die('CSRF token validation failed');
}

// Only process POST requests
if ($_SERVER['REQUEST_METHOD'] !== 'POST' || !isset($_POST['SoilcsvForm'])) {
    http_response_code(405);
    die('Method not allowed');
}

try {
    // Validate and sanitize input data
    $input = validateSoilTestData($_POST);

    // Perform soil analysis calculations
    $calculations = calculateSoilAnalysis($input);

    // Generate unique identifier for this record
    $rand = mt_rand(10000, 99999);

    // Insert data into database
    $recordId = insertSoilRecord($input, $calculations, $rand);

    // Log successful submission
    error_log("Soil test record created: ID {$recordId}, User: {$_SESSION['user_id']}");

    // Redirect to results page
    $redirectUrl = "/dashboard/crop-analysis/soil-analysis.php?rand={$rand}&cid={$input['sample_id']}&rid={$recordId}&stid=" . urlencode($input['crop_type']);
    header("Location: {$redirectUrl}");
    exit;

} catch (ValidationException $e) {
    http_response_code(400);
    die('Validation error: ' . htmlspecialchars($e->getMessage()));
} catch (PDOException $e) {
    error_log("Database error in soil test submission: " . $e->getMessage());
    http_response_code(500);
    die('Database error occurred. Please try again later.');
} catch (Exception $e) {
    error_log("Unexpected error in soil test submission: " . $e->getMessage());
    http_response_code(500);
    die('An unexpected error occurred. Please try again later.');
}

/**
 * Validate and sanitize soil test form data
 */
function validateSoilTestData(array $post): array
{
    $validated = [];

    // Client information
    $validated['client_id'] = filter_var($post['client_id'] ?? '', FILTER_VALIDATE_INT);
    if ($validated['client_id'] === false) {
        throw new ValidationException('Invalid client ID');
    }

    $validated['name'] = sanitizeString($post['name'] ?? '', 100);
    $validated['company'] = sanitizeString($post['company'] ?? '', 100);
    $validated['email'] = filter_var($post['email'] ?? '', FILTER_VALIDATE_EMAIL);
    if ($validated['email'] === false) {
        throw new ValidationException('Invalid email address');
    }

    $validated['site_address'] = sanitizeString($post['site_address'] ?? '', 255);
    $validated['state_postcode'] = sanitizeString($post['state_postcode'] ?? '', 100);

    // Analysis details
    $validated['lab_no'] = sanitizeString($post['lab_no'] ?? '', 50);
    $validated['batch_no'] = sanitizeString($post['batch_no'] ?? '', 50);
    $validated['sample_id'] = sanitizeString($post['sample_id'] ?? '', 50);
    $validated['site_id'] = sanitizeString($post['site_id'] ?? '', 50);
    $validated['crop_type'] = sanitizeString($post['crop_type'] ?? '', 50);
    $validated['soil_type'] = sanitizeString($post['soil_type'] ?? '', 50);

    $validated['date_sampled'] = $post['date_sampled'] ?? '';
    if (!empty($validated['date_sampled'])) {
        $date = DateTime::createFromFormat('Y-m-d', $validated['date_sampled']);
        if (!$date) {
            throw new ValidationException('Invalid date sampled format');
        }
        $validated['date_sampled'] = $date->format('Y-m-d');
    }

    // Physical properties
    $validated['texture'] = sanitizeString($post['texture'] ?? '', 50);
    $validated['gravel'] = validateNumeric($post['gravel'] ?? '', 0, 100);
    $validated['colour'] = sanitizeString($post['colour'] ?? '', 50);
    $validated['ocarbon'] = validateNumeric($post['ocarbon'] ?? '', 0, 100);
    $validated['omatter'] = validateNumeric($post['omatter'] ?? '', 0, 100);

    // Chemical properties
    $validated['ph_cacl2'] = validateNumeric($post['ph_cacl2'] ?? '', 0, 14);
    $validated['ph_h2o'] = validateNumeric($post['ph_h2o'] ?? '', 0, 14);
    $validated['paramag'] = validateNumeric($post['paramag'] ?? '');
    $validated['ec'] = validateNumeric($post['ec'] ?? '', 0);

    // Nutrient analysis
    $nutrientFields = [
        'NO3_N', 'NH3_N', 'p_mehlick', 'p_bray2', 'p_morgan', 'k_morgan',
        'ca_morgan', 'mg_morgan', 'na_morgan', 'ch_h2o', 'fe', 's_morgan',
        'b_cacl2', 'mn_dtpa', 'zn_dtpa', 'fe_dtpa', 'cu_dtpa', 'al',
        'sl_cacl2', 'm_dtpa', 'co_dtpa', 'se'
    ];

    foreach ($nutrientFields as $field) {
        $validated[$field] = validateNumeric($post[$field] ?? '', 0);
    }

    // Base saturation
    $validated['tec'] = validateNumeric($post['tec'] ?? '');
    $validated['cec'] = validateNumeric($post['cec'] ?? '');
    $validated['ca_mehlick3'] = validateNumeric($post['ca_mehlick3'] ?? '');
    $validated['mg_mehlick3'] = validateNumeric($post['mg_mehlick3'] ?? '');
    $validated['k_mehlick3'] = validateNumeric($post['k_mehlick3'] ?? '');
    $validated['na_mehlick3'] = validateNumeric($post['na_mehlick3'] ?? '');
    $validated['al_mehlick3'] = validateNumeric($post['al_mehlick3'] ?? '');

    // Additional calculations
    $validated['c_total'] = validateNumeric($post['c_total'] ?? '');
    $validated['n_total'] = validateNumeric($post['n_total'] ?? '');

    return $validated;
}

/**
 * Perform soil analysis calculations
 */
function calculateSoilAnalysis(array $data): array
{
    $calculations = [];

    // pH lookup table
    $phRange = [
        30 => [75.0, 11.4], 31 => [74.0, 11.2], 32 => [73.0, 11.0], 33 => [72.0, 10.8],
        34 => [71.0, 10.6], 35 => [70.0, 10.4], 36 => [69.0, 10.2], 37 => [68.0, 10.0],
        38 => [67.0, 9.8], 39 => [66.0, 9.6], 40 => [65.0, 9.4], 41 => [63.0, 9.2],
        42 => [61.0, 9.0], 43 => [59.0, 8.8], 44 => [57.0, 8.6], 45 => [55.0, 8.4],
        46 => [53.0, 8.2], 47 => [51.0, 8.0], 48 => [49.0, 7.8], 49 => [47.0, 7.6],
        50 => [45.0, 7.4], 51 => [42.0, 7.2], 52 => [39.0, 7.0], 53 => [36.0, 6.8],
        54 => [33.0, 6.6], 55 => [30.0, 6.4], 56 => [27.0, 6.2], 57 => [24.0, 6.0],
        58 => [21.0, 5.8], 59 => [18.0, 5.6], 60 => [15.0, 5.4], 61 => [13.5, 5.3],
        62 => [12.0, 5.2], 63 => [10.5, 5.1], 64 => [9.0, 5.0], 65 => [7.5, 4.9],
        66 => [6.0, 4.8], 67 => [4.5, 4.7], 68 => [3.0, 4.6], 69 => [1.5, 4.5],
        70 => [0.0, 4.4], 71 => [0.0, 4.3], 72 => [0.0, 4.2], 73 => [0.0, 4.1],
        74 => [0.0, 4.0], 75 => [0.0, 3.9], 76 => [0.0, 3.8], 77 => [0.0, 3.7],
        78 => [0.0, 3.6], 79 => [0.0, 3.5], 80 => [0.0, 3.4], 81 => [0.0, 3.3],
        82 => [0.0, 3.2], 83 => [0.0, 3.1], 84 => [0.0, 3.0], 85 => [0.0, 2.9],
        86 => [0.0, 2.8], 87 => [0.0, 2.7], 88 => [0.0, 2.6], 89 => [0.0, 2.5],
        90 => [0.0, 2.4], 91 => [0.0, 2.3], 92 => [0.0, 2.2], 93 => [0.0, 2.1],
        94 => [0.0, 2.0], 95 => [0.0, 1.9], 96 => [0.0, 1.8], 97 => [0.0, 1.7],
        98 => [0.0, 1.6], 99 => [0.0, 1.5], 100 => [0.0, 1.4],
    ];

    // Base saturation calculations
    $ph = $data['ph_h2o'];
    $aluminium = $data['al_mehlick3'];

    $phLookup = round($ph * 10);
    $hydrogen = $phRange[$phLookup][0] ?? 0;
    $otherbases = $phRange[$phLookup][1] ?? 0;

    $calculations['h_rec'] = round($hydrogen, 2);
    $calculations['ob_rec'] = round($otherbases, 2);

    if ($aluminium < 0) {
        $otherbases = 0;
    }

    // Calculate hydrogen and other bases results
    $obresult = 0;
    $hresult = 0;

    if ($otherbases > 0) {
        while ((($obresult * 100) / ($data['cec'] + $obresult + $hresult)) <= $otherbases) {
            $obresult += 0.001;
            $hresult = ($obresult * $hydrogen) / $otherbases;
        }
        $obresult -= 0.001;
        if ($hresult != 0) {
            $hresult -= 0.001;
        }
    } else {
        while ((($hresult * 100) / $data['tec']) <= $hydrogen) {
            $hresult += 0.001;
        }
        $hresult -= 0.001;
    }

    $tecTemp = $data['cec'] + $obresult + $hresult;
    $calculations['tec'] = round($tecTemp, 2);
    $calculations['cec'] = round($data['cec'], 2);

    // Base saturation percentages and recommendations
    $tec = $calculations['tec'];

    if ($tec >= 1 && $tec <= 3) {
        $calculations['cabs_max'] = 60.00; $calculations['mgbs_max'] = 20.00; $calculations['kbs'] = 5.00; $calculations['kbs_max'] = 7.00; $calculations['nabs_max'] = 1.50;
    } elseif ($tec > 3 && $tec <= 5) {
        $calculations['cabs_max'] = 62.00; $calculations['mgbs_max'] = 18.00; $calculations['kbs'] = 5.00; $calculations['kbs_max'] = 7.00; $calculations['nabs_max'] = 1.50;
    } elseif ($tec > 5 && $tec <= 7) {
        $calculations['cabs_max'] = 64.00; $calculations['mgbs_max'] = 16.00; $calculations['kbs'] = 4.00; $calculations['kbs_max'] = 7.00; $calculations['nabs_max'] = 1.50;
    } elseif ($tec > 7 && $tec <= 9) {
        $calculations['cabs_max'] = 65.00; $calculations['mgbs_max'] = 15.00; $calculations['kbs'] = 4.00; $calculations['kbs_max'] = 7.00; $calculations['nabs_max'] = 1.50;
    } elseif ($tec > 9 && $tec <= 11) {
        $calculations['cabs_max'] = 67.00; $calculations['mgbs_max'] = 13.00; $calculations['kbs'] = 4.00; $calculations['kbs_max'] = 7.00; $calculations['nabs_max'] = 1.50;
    } elseif ($tec > 11 && $tec <= 30) {
        $calculations['cabs_max'] = 68.00; $calculations['mgbs_max'] = 12.00; $calculations['kbs'] = 4.00; $calculations['kbs_max'] = 7.00; $calculations['nabs_max'] = 1.50;
    } else {
        $calculations['cabs_max'] = 70.00; $calculations['mgbs_max'] = 10.00; $calculations['kbs'] = 3.00; $calculations['kbs_max'] = 6.00; $calculations['nabs_max'] = 1.50;
    }

    // Calculate percentages and PPM values
    $calculations['cabs_tec'] = round(($data['ca_mehlick3'] / $tec) * 100, 2);
    $calculations['mgbs_tec'] = round(($data['mg_mehlick3'] / $tec) * 100, 2);
    $calculations['kbs_tec'] = round(($data['k_mehlick3'] / $tec) * 100, 2);
    $calculations['nabs_tec'] = round(($data['na_mehlick3'] / $tec) * 100, 2);
    $calculations['albs_tec'] = round(($data['al_mehlick3'] / $tec) * 100, 2);

    $calculations['BS_ca_ppm'] = $data['ca_mehlick3'] * 200;
    $calculations['BS_mg_ppm'] = $data['mg_mehlick3'] * 120;
    $calculations['BS_k_ppm'] = $data['k_mehlick3'] * 390;
    $calculations['BS_na_ppm'] = $data['na_mehlick3'] * 230;
    $calculations['BS_al_ppm'] = $data['al_mehlick3'] * 90;

    // Calculate ratios
    $calculations['ca_mg_ratio'] = $data['mg_mehlick3'] > 0 ? round($data['ca_mehlick3'] / $data['mg_mehlick3'], 2) : 0;
    $calculations['c_n_ratio'] = $data['n_total'] > 0 ? round($data['c_total'] / $data['n_total'], 2) : 0;

    return $calculations;
}

/**
 * Insert soil record into database
 */
function insertSoilRecord(array $data, array $calculations, int $rand): int
{
    global $pdo;

    $sql = "INSERT INTO soil_records (
        client_records_id, modx_user_id, date, email, client_name, site_address, state_postcode,
        analysis_type, lab_no, batch_no, sample_id, site_id, crop_type, soil_type, date_sampled,
        tec, cec, texture, gravel, colour, NO3_N, NH3_N, p_mehlick, p_bray2, p_morgan,
        k_morgan, ca_morgan, mg_morgan, na_morgan, ch_h2o, ocarbon, omatter, fe, ec,
        ph_cacl2, ph_h2o, paramag, s_morgan, b_cacl2, mn_dtpa, zn_dtpa, fe_dtpa, cu_dtpa,
        al, sl_cacl2, m_dtpa, co_dtpa, se, ca_mehlick3, BS_ca_ppm, mg_mehlick3, BS_mg_ppm,
        k_mehlick3, BS_k_ppm, na_mehlick3, BS_na_ppm, al_mehlick3, BS_al_ppm,
        BS_ca2, BS_mg2, BS_k, BS_na, BS_al3, BS_ob, BS_h,
        cabs_min, ca_ppm_min, cabs_max, ca_ppm_max, mgbs_min, mg_ppm_min, mgbs_max, mg_ppm_max,
        kbs_min, k_ppm_min, kbs_max, k_ppm_max, nabs_min, na_ppm_min, nabs_max, na_ppm_max,
        albs_min, al_ppm_min, albs_max, al_ppm_max, ob_rec, h_rec, ca_mg_ratio, rand
    ) VALUES (
        :client_id, :modx_user_id, NOW(), :email, :client_name, :site_address, :state_postcode,
        'Soil Test', :lab_no, :batch_no, :sample_id, :site_id, :crop_type, :soil_type, :date_sampled,
        :tec, :cec, :texture, :gravel, :colour, :NO3_N, :NH3_N, :p_mehlick, :p_bray2, :p_morgan,
        :k_morgan, :ca_morgan, :mg_morgan, :na_morgan, :ch_h2o, :ocarbon, :omatter, :fe, :ec,
        :ph_cacl2, :ph_h2o, :paramag, :s_morgan, :b_cacl2, :mn_dtpa, :zn_dtpa, :fe_dtpa, :cu_dtpa,
        :al, :sl_cacl2, :m_dtpa, :co_dtpa, :se, :ca_mehlick3, :BS_ca_ppm, :mg_mehlick3, :BS_mg_ppm,
        :k_mehlick3, :BS_k_ppm, :na_mehlick3, :BS_na_ppm, :al_mehlick3, :BS_al_ppm,
        :cabs_tec, :mgbs_tec, :kbs_tec, :nabs_tec, :al_mehlick3, :ob_rec, :h_rec,
        0, 0, :cabs_max, :ca_ppm_max, 0, 0, :mgbs_max, :mg_ppm_max,
        :kbs, :k_ppm_min, :kbs_max, :k_ppm_max, 0.50, :na_ppm_min, :nabs_max, :na_ppm_max,
        0, 0, 0.5, :al_ppm_max, :ob_rec, :h_rec, :ca_mg_ratio, :rand
    )";

    $stmt = $pdo->prepare($sql);
    $stmt->execute([
        'client_id' => $data['client_id'],
        'modx_user_id' => $_SESSION['user_id'],
        'email' => $data['email'],
        'client_name' => $data['name'],
        'site_address' => $data['site_address'],
        'state_postcode' => $data['state_postcode'],
        'lab_no' => $data['lab_no'],
        'batch_no' => $data['batch_no'],
        'sample_id' => $data['sample_id'],
        'site_id' => $data['site_id'],
        'crop_type' => $data['crop_type'],
        'soil_type' => $data['soil_type'],
        'date_sampled' => $data['date_sampled'],
        'tec' => $calculations['tec'],
        'cec' => $calculations['cec'],
        'texture' => $data['texture'] ?: null,
        'gravel' => $data['gravel'],
        'colour' => $data['colour'] ?: null,
        'NO3_N' => $data['NO3_N'],
        'NH3_N' => $data['NH3_N'],
        'p_mehlick' => $data['p_mehlick'],
        'p_bray2' => $data['p_bray2'],
        'p_morgan' => $data['p_morgan'],
        'k_morgan' => $data['k_morgan'],
        'ca_morgan' => $data['ca_morgan'],
        'mg_morgan' => $data['mg_morgan'],
        'na_morgan' => $data['na_morgan'],
        'ch_h2o' => $data['ch_h2o'],
        'ocarbon' => $data['ocarbon'],
        'omatter' => $data['omatter'],
        'fe' => $data['fe'],
        'ec' => $data['ec'],
        'ph_cacl2' => $data['ph_cacl2'],
        'ph_h2o' => $data['ph_h2o'],
        'paramag' => $data['paramag'] ?: null,
        's_morgan' => $data['s_morgan'],
        'b_cacl2' => $data['b_cacl2'],
        'mn_dtpa' => $data['mn_dtpa'],
        'zn_dtpa' => $data['zn_dtpa'],
        'fe_dtpa' => $data['fe_dtpa'],
        'cu_dtpa' => $data['cu_dtpa'],
        'al' => $data['al'],
        'sl_cacl2' => $data['sl_cacl2'],
        'm_dtpa' => $data['m_dtpa'],
        'co_dtpa' => $data['co_dtpa'],
        'se' => $data['se'],
        'ca_mehlick3' => $data['ca_mehlick3'],
        'BS_ca_ppm' => $calculations['BS_ca_ppm'],
        'mg_mehlick3' => $data['mg_mehlick3'],
        'BS_mg_ppm' => $calculations['BS_mg_ppm'],
        'k_mehlick3' => $data['k_mehlick3'],
        'BS_k_ppm' => $calculations['BS_k_ppm'],
        'na_mehlick3' => $data['na_mehlick3'],
        'BS_na_ppm' => $calculations['BS_na_ppm'],
        'al_mehlick3' => $data['al_mehlick3'],
        'BS_al_ppm' => $calculations['BS_al_ppm'],
        'cabs_tec' => $calculations['cabs_tec'],
        'mgbs_tec' => $calculations['mgbs_tec'],
        'kbs_tec' => $calculations['kbs_tec'],
        'nabs_tec' => $calculations['nabs_tec'],
        'cabs_max' => $calculations['cabs_max'],
        'ca_ppm_max' => $calculations['tec'] * $calculations['cabs_max'] * 2,
        'mgbs_max' => $calculations['mgbs_max'],
        'mg_ppm_max' => $calculations['tec'] * $calculations['mgbs_max'] * 1.2,
        'kbs' => $calculations['kbs'],
        'k_ppm_min' => $calculations['tec'] * $calculations['kbs'] * 3.9,
        'kbs_max' => $calculations['kbs_max'],
        'k_ppm_max' => $calculations['tec'] * $calculations['kbs_max'] * 3.9,
        'na_ppm_min' => $calculations['tec'] * 0.5 * 2.3,
        'nabs_max' => $calculations['nabs_max'],
        'na_ppm_max' => $calculations['tec'] * $calculations['nabs_max'] * 2.3,
        'al_ppm_max' => $calculations['tec'] * 0.5 * 0.9,
        'ob_rec' => $calculations['ob_rec'],
        'h_rec' => $calculations['h_rec'],
        'ca_mg_ratio' => $calculations['ca_mg_ratio'],
        'rand' => $rand
    ]);

    return $pdo->lastInsertId();
}

/**
 * Custom exception for validation errors
 */
class ValidationException extends Exception {}

/**
 * Sanitize string input
 */
function sanitizeString(?string $value, int $maxLength = 255): string
{
    if ($value === null) return '';
    $sanitized = trim($value);
    $sanitized = filter_var($sanitized, FILTER_SANITIZE_STRING, FILTER_FLAG_NO_ENCODE_QUOTES);
    return substr($sanitized, 0, $maxLength);
}

/**
 * Validate numeric input
 */
function validateNumeric(?string $value, float $min = null, float $max = null): ?float
{
    if ($value === '' || $value === null) return null;

    $numeric = filter_var($value, FILTER_VALIDATE_FLOAT);
    if ($numeric === false) {
        throw new ValidationException('Invalid numeric value: ' . $value);
    }

    if ($min !== null && $numeric < $min) {
        throw new ValidationException('Value below minimum: ' . $numeric);
    }

    if ($max !== null && $numeric > $max) {
        throw new ValidationException('Value above maximum: ' . $numeric);
    }

    return $numeric;
}
?>