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

<?php
/**
 * dashboard/crop-analysis/soil-test-data/soil-analysis.php
 *
 * Soil Analysis Results Display Page
 */

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

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

requireLogin();

$record_id = (float)($_GET['rid']  ?? 0);
$rand_id   = (float)($_GET['rand'] ?? 0);
$client_id = (int)  ($_GET['cid']  ?? 0);

if (!$record_id || !$rand_id) {
    die('Invalid request parameters');
}

try {
    $pdo = getDBConnection();

    // Load soil record
    $stmt = $pdo->prepare("SELECT * FROM soil_records WHERE id = ? AND rand = ?");
    $stmt->execute([$record_id, $rand_id]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$row) {
        die('Soil record not found');
    }

    // Load matching specifications row for this soil type
    $spec = [];
    if (!empty($row['soil_type'])) {
        $stmtSpec = $pdo->prepare("SELECT * FROM soil_specifications WHERE soil_type = ? LIMIT 1");
        $stmtSpec->execute([$row['soil_type']]);
        $spec = $stmtSpec->fetch(PDO::FETCH_ASSOC) ?: [];
    }

} catch (PDOException $e) {
    error_log("Database error in soil-analysis.php: " . $e->getMessage());
    die('Database error occurred');
}

// ── Page setup ─────────────────────────────────────────────────────────────
$client     = htmlspecialchars($row['client_name']   ?? '', ENT_QUOTES, 'UTF-8');
$address    = htmlspecialchars($row['site_address']   ?? '', ENT_QUOTES, 'UTF-8');
$state      = htmlspecialchars($row['state_postcode'] ?? '', ENT_QUOTES, 'UTF-8');
$email      = htmlspecialchars($row['email']          ?? '', ENT_QUOTES, 'UTF-8');
$labNo      = htmlspecialchars($row['lab_no']         ?? '', ENT_QUOTES, 'UTF-8');
$sampleDate = htmlspecialchars($row['date_sampled']   ?? '', ENT_QUOTES, 'UTF-8');
$sample     = htmlspecialchars($row['site_id']        ?? '', ENT_QUOTES, 'UTF-8');
$crop       = htmlspecialchars($row['sample_id']      ?? '', ENT_QUOTES, 'UTF-8');

$today     = date('jS F Y');
$pageTitle = 'Soil Analysis Results' . ($client !== '' ? ' - ' . $client : '');

include __DIR__.'/../../../layouts/header.php';
?>

<link rel="stylesheet" href="/client-assets/home/css/graphPrint.css" media="print">
<style>
    .progress { border-radius: 0 !important; }
</style>

<div class="container" id="content">

    <div class="row mb-2">
        <div class="col-md-3">
            <img class="img-fluid" src="/client-assets/images/crop-monitor.png" alt="Crop Monitor">
        </div>
    </div>

    <table class="title">
        <tbody>
            <tr>
                <th class="col-20"></th><th class="col-20"></th><th class="col-20"></th>
                <th class="col-20"></th><th class="col-20"></th>
            </tr>
            <tr>
                <td class="right"><b>DATE:</b></td>
                <td class="left"><?= $today ?></td>
                <td></td>
                <td class="right"><b>SAMPLE ID:</b></td>
                <td class="left"><?= $sample ?></td>
            </tr>
            <tr>
                <td class="right"><b>CLIENT:</b></td>
                <td class="left"><?= $client ?></td>
                <td></td>
                <td class="right"><b>DATE SAMPLED:</b></td>
                <td class="left"><?= $sampleDate ?></td>
            </tr>
            <tr>
                <td class="right"><b>ADDRESS:</b></td>
                <td class="left"><?= $address ?></td>
                <td></td>
                <td class="right"><b>LAB NUMBER:</b></td>
                <td class="left"><?= $labNo ?></td>
            </tr>
            <tr>
                <td class="right"></td>
                <td class="left"><?= $state ?></td>
                <td></td>
                <td class="right"><b>CROP:</b></td>
                <td class="left"><?= $crop ?></td>
            </tr>
            <tr>
                <td class="right"></td>
                <td class="left"><?= $email ?></td>
                <td></td><td></td><td></td>
            </tr>
        </tbody>
    </table>

    <div class="d-print-none">
        <div class="row p-2">
            <div class="col">
                <button type="button" class="btn btn-primary" onclick="generateGraph()">
                    <i class="fas fa-chart-bar"></i> Generate Graph
                </button>
            </div>
            <div class="col">
                <div class="form-status-holder"></div>
            </div>
        </div>
    </div>

    <div class="row">
        <div class="col-md-12 text-center fw-bold h4">Soil Analysis Summary</div>
    </div>

    <table class="chart">
        <tbody>

          <!-- ── CHART HEADER ──────────────────────────────────────────── -->
          <tr class="chart-header">
            <th colspan="3" class="text-center col-md-6 border-left border-right border-top">ELEMENT</th>
            <th colspan="3" class="text-center col-md-6 border-right border-top">STATUS</th>
          </tr>
          <tr class="chart-header-sub">
            <th class="text-center col-18 border-left"></th>
            <th class="text-center col-15">DESIRED</th>
            <th class="text-center col-15">FOUND</th>
            <th class="text-center col-16 stripe-1">LIGHT</th>
            <th class="text-center col-16 stripe-1">MEDIUM</th>
            <th class="text-center col-16 border-right stripe-1">HEAVY</th>
          </tr>
          <tr>
            <td class="border-left"></td>
            <td class="border-left"></td>
            <td class="border-left nutrient-balance"></td>
            <td class="border-left"></td>
            <td class="border-left"></td>
            <td class="border-left border-right"></td>
          </tr>

          <?php
          soilRow($row, $spec, ['element'=>'cec', 'nutrient'=>'CEC', 'recV'=>'n', 'decimal'=>2, 'graph'=>'lightorangeGraph']);
          soilRow($row, $spec, ['element'=>'tec', 'nutrient'=>'TEC', 'max'=>'soil_type', 'recV'=>'max', 'rec_text'=>'c', 'decimal'=>2, 'graph'=>'lightorangeGraph']);
          ?>

          <tr class="chart-header-sub">
            <th class="text-center col-18 border-left text-dark bg-white"></th>
            <th class="text-center col-15 border-left text-dark bg-white"></th>
            <th class="text-center col-15 border-left nutrient-balance"></th>
            <th class="text-center col-16 border-left stripe-1">DEFICIT</th>
            <th class="text-center col-16 stripe-1">IDEAL</th>
            <th class="text-center col-16 border-right stripe-1">HIGH</th>
          </tr>

          <?php
          soilRow($row, $spec, ['element'=>'ph_h2o',   'nutrient'=>'pH-level (H20)',   'type'=>'pH',    'recV'=>'ph', 'decimal'=>1, 'graph'=>'lightorangeGraph']);
          soilRow($row, $spec, ['element'=>'ph_cacl2', 'nutrient'=>'pH-level (CaCl2)', 'type'=>'pH',    'recV'=>'n',  'decimal'=>1, 'graph'=>'lightorangeGraph']);
          soilRow($row, $spec, ['element'=>'ec',       'nutrient'=>'Conductivity (EC)','type'=>'mS/cm', 'decimal'=>2, 'graph'=>'lightorangeGraph']);
          soilRow($row, $spec, ['element'=>'ocarbon',  'nutrient'=>'Organic Carbon',   'type'=>'%',     'decimal'=>1, 'graph'=>'lightorangeGraph']);
          soilRow($row, $spec, ['element'=>'omatter',  'nutrient'=>'Organic Matter',   'type'=>'%',     'decimal'=>1, 'graph'=>'lightorangeGraph']);
          ?>

          <!-- ── MAJOR ELEMENTS ─────────────────────────────────────────── -->
          <tr class="chart-header-sub">
            <th colspan="3" class="col-16 border-left text-center lightgreen">MAJOR ELEMENTS</th>
            <th class="text-center col-16 stripe-1"></th>
            <th class="text-center col-16 stripe-1"></th>
            <th class="text-center col-16 border-right stripe-1"></th>
          </tr>

          <?php
          soilRow($row, $spec, ['element'=>'NO3_N',     'sbl'=>'NO<sub>3</sub>-N', 'nutrient'=>'Nitrate <small class="d-print-none">Nitrogen</small>',  'min'=>'10', 'max'=>'20', 'type'=>'ppm', 'text'=>'c', 'rec_text'=>'r', 'decimal'=>0, 'graph'=>'lightgreenGraph']);
          soilRow($row, $spec, ['element'=>'NH3_N',     'sbl'=>'NH<sub>3</sub>-N', 'nutrient'=>'Ammonium <small class="d-print-none">Nitrogen</small>', 'type'=>'ppm', 'text'=>'c', 'rec_text'=>'r', 'decimal'=>0, 'graph'=>'lightgreenGraph']);
          // p_mehlick, p_bray2, p_morgan excluded (commented in original)
          soilRow($row, $spec, ['element'=>'p_colwell',  'sbl'=>'P',  'nutrient'=>'Phosphate <small>(colwell)</small>', 'type'=>'ppm', 'text'=>'c', 'rec_text'=>'r', 'decimal'=>0, 'graph'=>'lightgreenGraph']);
          soilRow($row, $spec, ['element'=>'BS_ca_ppm',  'sbl'=>'Ca', 'nutrient'=>'Calcium',   'min'=>'ca_ppm_min', 'max'=>'ca_ppm_max', 'type'=>'ppm', 'text'=>'c', 'rec_text'=>'r', 'recV'=>'max', 'decimal'=>0, 'graph'=>'lightgreenGraph']);
          soilRow($row, $spec, ['element'=>'BS_mg_ppm',  'sbl'=>'Mg', 'nutrient'=>'Magnesium', 'min'=>'mg_ppm_min', 'max'=>'mg_ppm_max', 'type'=>'ppm', 'text'=>'c', 'rec_text'=>'r', 'recV'=>'max', 'decimal'=>0, 'graph'=>'lightgreenGraph']);
          soilRow($row, $spec, ['element'=>'BS_k_ppm',   'sbl'=>'K',  'nutrient'=>'Potassium', 'min'=>'k_ppm_min',  'max'=>'k_ppm_max',  'type'=>'ppm', 'text'=>'c', 'rec_text'=>'r', 'recV'=>'max', 'decimal'=>0, 'graph'=>'lightgreenGraph']);
          soilRow($row, $spec, ['element'=>'BS_na_ppm',  'sbl'=>'Na', 'nutrient'=>'Sodium',    'min'=>'na_ppm_min', 'max'=>'na_ppm_max', 'type'=>'ppm', 'text'=>'c', 'rec_text'=>'r', 'recV'=>'max', 'decimal'=>0, 'graph'=>'lightgreenGraph']);
          ?>

          <!-- ── TRACE ELEMENTS ─────────────────────────────────────────── -->
          <tr class="chart-header-sub">
            <th colspan="3" class="col-16 border-left text-center lightred">TRACE ELEMENTS</th>
            <th class="text-center col-16 stripe-1"></th>
            <th class="text-center col-16 stripe-1"></th>
            <th class="text-center col-16 border-right stripe-1"></th>
          </tr>

          <?php
          soilRow($row, $spec, ['element'=>'s_morgan', 'sbl'=>'S',  'nutrient'=>'Sulfur',    'type'=>'ppm', 'text'=>'c', 'rec_text'=>'r', 'decimal'=>2, 'graph'=>'lightredGraph']);
          soilRow($row, $spec, ['element'=>'b_cacl2',  'sbl'=>'B',  'nutrient'=>'Boron',     'type'=>'ppm', 'text'=>'c', 'rec_text'=>'r', 'decimal'=>2, 'graph'=>'lightredGraph']);
          soilRow($row, $spec, ['element'=>'mn_dtpa',  'sbl'=>'Mn', 'nutrient'=>'Manganese', 'type'=>'ppm', 'text'=>'c', 'rec_text'=>'r', 'decimal'=>2, 'graph'=>'lightredGraph']);
          soilRow($row, $spec, ['element'=>'cu_dtpa',  'sbl'=>'Cu', 'nutrient'=>'Copper',    'type'=>'ppm', 'text'=>'c', 'rec_text'=>'r', 'decimal'=>2, 'graph'=>'lightredGraph']);
          soilRow($row, $spec, ['element'=>'zn_dtpa',  'sbl'=>'Zn', 'nutrient'=>'Zinc',      'type'=>'ppm', 'text'=>'c', 'rec_text'=>'r', 'decimal'=>2, 'graph'=>'lightredGraph']);
          soilRow($row, $spec, ['element'=>'fe_dtpa',  'sbl'=>'Fe', 'nutrient'=>'Iron',      'type'=>'ppm', 'text'=>'c', 'rec_text'=>'r', 'decimal'=>2, 'graph'=>'lightredGraph']);
          soilRow($row, $spec, ['element'=>'al',       'sbl'=>'Al', 'nutrient'=>'Aluminium', 'type'=>'ppm', 'text'=>'c', 'rec_text'=>'r', 'decimal'=>2, 'graph'=>'lightredGraph']);
          soilRow($row, $spec, ['element'=>'sl_cacl2', 'sbl'=>'Si', 'nutrient'=>'Silicon',   'type'=>'ppm', 'text'=>'c', 'rec_text'=>'r', 'decimal'=>2, 'graph'=>'lightredGraph']);
          ?>

          <!-- ── BASE SATURATION ────────────────────────────────────────── -->
          <tr class="chart-header-sub">
            <th colspan="3" class="col-16 border-left text-center lightpurple">BASE SATURATION</th>
            <th class="text-center col-16 stripe-1"></th>
            <th class="text-center col-16 stripe-1"></th>
            <th class="text-center col-16 border-right stripe-1"></th>
          </tr>

          <?php
          soilRow($row, $spec, ['element'=>'BS_ca2', 'sbl'=>'Ca', 'nutrient'=>'Calcium',   'min'=>'cabs_min', 'max'=>'cabs_max', 'type'=>'%', 'text'=>'c', 'rec_text'=>'r', 'recV'=>'max', 'decimal'=>2, 'graph'=>'lightpurpleGraph']);
          soilRow($row, $spec, ['element'=>'BS_mg2', 'sbl'=>'Mg', 'nutrient'=>'Magnesium', 'min'=>'mgbs_min', 'max'=>'mgbs_max', 'type'=>'%', 'text'=>'c', 'rec_text'=>'r', 'recV'=>'max', 'decimal'=>2, 'graph'=>'lightpurpleGraph']);
          soilRow($row, $spec, ['element'=>'BS_k',   'sbl'=>'K',  'nutrient'=>'Potassium', 'min'=>'kbs_min',  'max'=>'kbs_max',  'type'=>'%', 'text'=>'c', 'rec_text'=>'r', 'recV'=>'max', 'decimal'=>2, 'graph'=>'lightpurpleGraph']);
          soilRow($row, $spec, ['element'=>'BS_na',  'sbl'=>'Na', 'nutrient'=>'Sodium',    'min'=>'nabs_min', 'max'=>'nabs_max', 'type'=>'%', 'text'=>'c', 'rec_text'=>'r', 'recV'=>'max', 'decimal'=>2, 'graph'=>'lightpurpleGraph']);
          soilRow($row, $spec, ['element'=>'BS_ob',  'nutrient'=>'Other Bases', 'max'=>'ob_rec', 'type'=>'%', 'text'=>'c', 'rec_text'=>'r', 'recV'=>'max', 'decimal'=>2, 'graph'=>'lightpurpleGraph']);
          soilRow($row, $spec, ['element'=>'BS_h',   'nutrient'=>'Hydrogen',    'max'=>'h_rec',  'type'=>'%', 'text'=>'c', 'rec_text'=>'r', 'recV'=>'max', 'decimal'=>2, 'graph'=>'lightpurpleGraph']);
          ?>

          <!-- ── SOLUBLE MORGAN 2 EXTRACT ───────────────────────────────── -->
          <tr class="chart-header-sub">
            <th colspan="3" class="col-16 border-left text-center lightgrey">SOLUBLE MORGAN 2 EXTRACT</th>
            <th class="text-center col-16 stripe-1"></th>
            <th class="text-center col-16 stripe-1"></th>
            <th class="text-center col-16 border-right stripe-1"></th>
          </tr>

          <?php
          soilRow($row, $spec, ['element'=>'s_morgan', 'sbl'=>'Ca', 'nutrient'=>'Calcium',   'type'=>'%', 'text'=>'c', 'rec_text'=>'r', 'decimal'=>0, 'graph'=>'lightgreyGraph']);
          soilRow($row, $spec, ['element'=>'b_cacl2',  'sbl'=>'Mg', 'nutrient'=>'Magnesium', 'type'=>'%', 'text'=>'c', 'rec_text'=>'r', 'decimal'=>0, 'graph'=>'lightgreyGraph']);
          soilRow($row, $spec, ['element'=>'mn_dtpa',  'sbl'=>'K',  'nutrient'=>'Potassium', 'type'=>'%', 'text'=>'c', 'rec_text'=>'r', 'decimal'=>0, 'graph'=>'lightgreyGraph']);
          ?>

          <!-- ── ADDITIONAL DATA ────────────────────────────────────────── -->
          <tr class="chart-header-sub">
            <th colspan="3" class="col-16 border-left text-center lightgrey">ADDITIONAL DATA</th>
            <th class="text-center col-16 stripe-1">LOW</th>
            <th class="text-center col-16 stripe-1">IDEAL</th>
            <th class="text-center col-16 border-right stripe-1">EXCELLENT</th>
          </tr>

          <?php
          soilRow($row, $spec, ['element'=>'s_morgan', 'sbl'=>'Ca', 'nutrient'=>'Calcium', 'type'=>'%', 'text'=>'c', 'rec_text'=>'r', 'decimal'=>0, 'graph'=>'lightgreyGraph']);
          ?>

          <!-- ── RATIOS ─────────────────────────────────────────────────── -->
          <tr class="chart-header-sub">
            <th colspan="3" class="col-16 border-left text-center lightblue">RATIOS</th>
            <th class="text-center col-16 stripe-1"></th>
            <th class="text-center col-16 stripe-1"></th>
            <th class="text-center col-16 border-right stripe-1"></th>
          </tr>

          <?php
          soilRatio($row, $spec, ['element'=>'ca_mehlick3', 'elementTwo'=>'mg_mehlick3', 'rec'=>'ca_mg_ratio', 'nutrient'=>'Ca:Mg Ratio',    'rec_text'=>'r', 'decimal'=>1, 'graph'=>'lightblueGraph']);
          soilRow  ($row, $spec, ['element'=>'NH3_N',  'nutrient'=>'Total Nitrogen', 'type'=>'%',  'text'=>'c', 'rec_text'=>'r', 'decimal'=>1, 'graph'=>'lightblueGraph']);
          soilRow  ($row, $spec, ['element'=>'ocarbon', 'nutrient'=>'Total Carbon',  'type'=>'%',  'text'=>'c', 'rec_text'=>'r', 'decimal'=>1, 'graph'=>'lightblueGraph']);
          soilRatio($row, $spec, ['element'=>'ocarbon', 'elementTwo'=>'NO3_N',       'rec'=>'c_n_ratio', 'nutrient'=>'C:N Ratio', 'type'=>':1', 'rec_text'=>'r', 'decimal'=>1, 'graph'=>'lightblueGraph']);
          ?>

          <tr>
            <td class="border-bottom border-left"></td>
            <td class="border-bottom border-left"></td>
            <td class="border-bottom border-left nutrient-balance"></td>
            <td class="border-bottom border-left"></td>
            <td class="border-bottom border-left"></td>
            <td class="border-bottom border-left border-right"></td>
          </tr>

        </tbody>
    </table>

    <footer class="py-4 bg-light mt-auto">
        <div class="container-fluid px-4">
            <div class="d-flex align-items-center justify-content-between small">
                <div class="text-muted">&copy; <?= date('Y') ?> Crop Management Platform. All Rights Reserved.</div>
                <div>
                    <a href="/privacy-policy">Privacy Policy</a> &middot;
                    <a href="/terms">Terms &amp; Conditions</a>
                </div>
            </div>
        </div>
    </footer>

</div><!-- /.container -->

<script>
function generateGraph() {
    alert('Graph generation functionality will be implemented here.');
}

$(document).ready(function () {
    var timeoutId;
    $('form textarea, form input').on('input propertychange change', function () {
        clearTimeout(timeoutId);
        timeoutId = setTimeout(saveToDB, 1000);
    });

    function saveToDB() {
        var form = $('.report-form');
        $.ajax({
            url:  '/controllers/save_soil_analysis.php',
            type: 'POST',
            data: form.serialize(),
            beforeSend: function () { $('.form-status-holder').html('Saving...'); },
            success: function () {
                var d = new Date();
                $('.form-status-holder').html('Saved! Last: ' + d.toLocaleTimeString());
            }
        });
    }

    $('.report-form').submit(function (e) {
        saveToDB();
        e.preventDefault();
    });
});
</script>