soil-report-ai/lib/soil_calculations.php

<?php
/**
 * lib/soil_calculations.php
 *
 * Functions for soil analysis calculations and display
 */

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

/**
 * Calculate and display soil program for a specific element
 *
 * @param string $symbol Element symbol (e.g., 'Ca', 'Mg', 'K')
 * @param string $element Database column name for the element
 * @param string $min Min value column (empty string uses element column)
 * @param string $max Max value column (empty string uses element column)
 * @param string $nutrient Full nutrient name
 * @param string $type Measurement type (e.g., 'kg', 'ppm', '%')
 * @param int $record_id Soil record ID
 * @param float $rand_id Random ID for verification
 * @return string HTML output for the program row
 */
function soilProgramCalcs($symbol, $element, $min, $max, $nutrient, $type, $record_id, $rand_id) {
    try {
        $pdo = getDBConnection();

        // Determine which table to use for min/max values
        if (empty($min)) {
            $element_min = $element;
            $dbtable = "soil_specifications.";
        } else {
            $element_min = $min;
            $dbtable = "soil_records.";
        }

        if (empty($max)) {
            $element_max = $element;
            $dbtable = "soil_specifications.";
        } else {
            $element_max = $max;
            $dbtable = "soil_records.";
        }

        // Prepare and execute query
        $stmt = $pdo->prepare("
            SELECT soil_records.{$element},
                   {$dbtable}{$element_max} AS soilMax,
                   {$dbtable}{$element_min} AS soilMin
            FROM soil_records
            INNER JOIN soil_specifications ON soil_records.soil_type = soil_specifications.soil_type
            WHERE soil_records.id = ? AND soil_records.rand = ?
        ");

        $stmt->execute([$record_id, $rand_id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$row) {
            return "<div class='row'>
                <div class='col-1 border-bottom border-left'>1</div>
                <div class='col border-bottom border-left'>{$nutrient}</div>
                <div class='col border-bottom border-left'>@</div>
                <div class='col border-bottom border-left'>N/A</div>
                <div class='col border-bottom border-left'>kg/Ha</div>
            </div>";
        }

        $value = (float) $row[$element];
        $max_val = (float) $row['soilMax'];

        $measurement = empty($type) ? "" : $type;
        $value_p = floatval($value);

        // Calculate recommended amount (uses max instead of median like soilAnalysisReportCalcs)
        $recommended = $max_val - $value_p;

        // Convert acres to hectares (kg/Ac to kg/ha)
        $acHa = 2.4710559990832394739; // Acres to Hectares
        $value_converted = ($recommended * $acHa);

        // Show 0 if value is negative, otherwise round to 2 decimal places
        if ($value_converted < 0) {
            $value_converted = 0;
        } else {
            $value_converted = round($value_converted, 2);
        }

        $result = $value_converted . " " . $measurement;

        // Return HTML table row
        return "<div class='row'>
            <div class='col-1 border-bottom border-left'>1</div>
            <div class='col border-bottom border-left'>{$nutrient}</div>
            <div class='col border-bottom border-left'>@</div>
            <div class='col border-bottom border-left'>{$result}</div>
            <div class='col border-bottom border-left'>kg/Ha</div>
        </div>";

    } catch (PDOException $e) {
        error_log("Database error in soilProgramCalcs: " . $e->getMessage());
    }
}

/**
 * Calculate and display soil analysis report for a specific element
 *
 * @param string $symbol Element symbol (e.g., 'Ca', 'Mg', 'K')
 * @param string $element Database column name for the element
 * @param string $min Min value column (empty string uses element column)
 * @param string $max Max value column (empty string uses element column)
 * @param string $nutrient Full nutrient name
 * @param string $type Measurement type (e.g., 'kg', 'ppm', '%')
 * @param string $class CSS class for styling
 * @param int $record_id Soil record ID
 * @param float $rand_id Random ID for verification
 * @return string HTML output for the analysis row
 */
function soilAnalysisReportCalcs($symbol, $element, $min, $max, $nutrient, $type, $class, $record_id, $rand_id) {
    try {
        $pdo = getDBConnection();

        // Determine which table to use for min/max values
        if (empty($min)) {
            $element_min = $element;
            $dbtable_min = "soil_specifications.";
        } else {
            $element_min = $min;
            $dbtable_min = "soil_records.";
        }

        if (empty($max)) {
            $element_max = $element;
            $dbtable_max = "soil_specifications.";
        } else {
            $element_max = $max;
            $dbtable_max = "soil_records.";
        }

        // Prepare and execute query
        $stmt = $pdo->prepare("
            SELECT soil_records.{$element},
                   {$dbtable_min}{$element_min} AS soilMin,
                   {$dbtable_max}{$element_max} AS soilMax
            FROM soil_records
            INNER JOIN soil_specifications ON soil_records.soil_type = soil_specifications.soil_type
            WHERE soil_records.id = ? AND soil_records.rand = ?
        ");

        $stmt->execute([$record_id, $rand_id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$row) {
            return "<div class='{$class}'>{$nutrient}: N/A</div>";
        }

        $value = (float) $row[$element];
        $min_val = (float) $row['soilMin'];
        $max_val = (float) $row['soilMax'];

        $measurement = empty($type) ? "" : $type;
        $value_p = floatval($value);

        // Calculate recommended amount (median between min and max)
        $recommended = ($min_val + $max_val) / 2;

        // Convert acres to hectares (kg/Ac to kg/ha)
        $acHa = 2.4710559990832394739; // Acres to Hectares
        $value_converted = ($recommended - $value_p) * $acHa;

        // Show 0 if value is negative, otherwise round to 2 decimal places
        if ($value_converted < 0) {
            $value_converted = 0;
        } else {
            $value_converted = round($value_converted, 2);
        }

        $result = $value_converted . " " . $measurement;

        // Return HTML div
        return "<div class='{$class}'>{$nutrient}:<br>{$result}</div>";

    } catch (PDOException $e) {
        error_log("Database error in soilAnalysisReportCalcs: " . $e->getMessage());
        return "<div class='{$class}'>{$nutrient}: Error</div>";
    }
}

/**
 * Renders a <tr> for a single nutrient with three progress-bar columns.
 *
 * Parameters ($p keys):
 *   element   string  Column name in soil_records
 *   sbl       string  Chemical symbol (may contain HTML, e.g. "NO<sub>3</sub>-N")
 *   nutrient  string  Display name (may contain HTML)
 *   min       string  Column in soil_records, numeric literal, or '' for no bar
 *   max       string  Column in soil_records, 'soil_type' (special), numeric literal, or ''
 *   type      string  Unit label (ppm, %, mS/cm …)
 *   text      string  Value cell alignment: c|r|l
 *   rec_text  string  Recommended cell alignment: c|r|l
 *   recV      string  Recommended display: 'n'=none, 'ph'='6.4', 'max'=max only, else=min–max
 *   decimal   int     Decimal places for value
 *   graph     string  CSS class for progress-bar colour
 */
function soilRow(array $row, array $spec, array $p): void
{
    $element  = $p['element']   ?? '';
    $sbl      = $p['sbl']       ?? '';
    $nutrient = $p['nutrient']  ?? '';
    $minParam = $p['min']       ?? '';
    $maxParam = $p['max']       ?? '';
    $type     = $p['type']      ?? '';
    $text     = $p['text']      ?? 'c';
    $recText  = $p['rec_text']  ?? 'c';
    $recV     = $p['recV']      ?? '';
    $decimal  = (int)($p['decimal'] ?? 2);
    $graph    = $p['graph']     ?? '';

    $label  = ($sbl !== '') ? $sbl . ' - ' . $nutrient : $nutrient;
    $rawVal = $row[$element] ?? null;
    $value  = ($rawVal !== null && $rawVal !== '') ? (float)$rawVal : 0.0;
    $valueFmt = number_format($value, $decimal, '.', '') . ($type !== '' ? ' ' . $type : '');

    // Resolve min
    $min = 0.0;
    if ($minParam !== '') {
        if (is_numeric($minParam)) {
            $min = (float)$minParam;
        } elseif (isset($row[$minParam]) && $row[$minParam] !== '') {
            $min = (float)$row[$minParam];
        } elseif (isset($spec[$minParam]) && $spec[$minParam] !== '') {
            $min = (float)$spec[$minParam];
        }
    } elseif (isset($spec[$element]) && $spec[$element] !== '') {
        $min = (float)$spec[$element] / 2;  // fallback: half the spec average
    }

    // Resolve max + recommended display label
    $max = 0.0;
    $maxLabel = '';
    if ($maxParam === 'soil_type') {
        $st = strtolower($row['soil_type'] ?? '');
        $maxLabel = match($st) {
            'light'  => 'Light Soil',
            'medium' => 'Medium Soil',
            'heavy'  => 'Heavy Soil',
            default  => htmlspecialchars($row['soil_type'] ?? '', ENT_QUOTES, 'UTF-8'),
        };
    } elseif ($maxParam !== '') {
        if (is_numeric($maxParam)) {
            $max = (float)$maxParam;
        } elseif (isset($row[$maxParam]) && $row[$maxParam] !== '') {
            $max = (float)$row[$maxParam];
        } elseif (isset($spec[$maxParam]) && $spec[$maxParam] !== '') {
            $max = (float)$spec[$maxParam];
        }
    } elseif (isset($spec[$element]) && $spec[$element] !== '') {
        $max = (float)$spec[$element] * 2;  // fallback: double the spec average
    }

    // Recommended cell text
    $measurement = ($type !== '') ? ' ' . $type : '';
    if ($maxParam === 'soil_type') {
        $recommended = $maxLabel;
    } elseif ($recV === 'n') {
        $recommended = '';
    } elseif ($recV === 'ph') {
        $recommended = '6.4';
    } elseif ($recV === 'max') {
        $recommended = number_format($max, $decimal, '.', '') . $measurement;
    } else {
        $recommended = number_format($min, $decimal, '.', '') . ' - ' . number_format($max, $decimal, '.', '') . $measurement;
    }

    $alignVal = match($text)    { 'r' => 'text-right', 'l' => 'text-left', default => 'text-center' };
    $alignRec = match($recText) { 'r' => 'text-right', 'l' => 'text-left', default => 'text-center' };

    // Bar calculations (replicates original int-cast logic)
    $c_min = $min - ($max - $min);
    $c_max = $max + ($max - $min);
    $hasValue = ($rawVal !== null && $rawVal !== '' && $rawVal !== '0');

    // First bar (deficit zone: c_min → min)
    if (!$hasValue || (int)($c_min - $min) == 0) {
        $fb = 0;
    } else {
        $fb = (int)($c_min - $value) / (int)($c_min - $min) * 100;
    }
    $fb = !$hasValue ? 0 : ($fb > 100 ? 100 : ($fb < 0 ? 2 : $fb));

    // Second bar (ideal zone: min → max)
    if (!$hasValue || (int)($min - $max) == 0) {
        $sb = 0;
    } else {
        $sb = (int)($min - $value) / (int)($min - $max) * 100;
    }
    $sbp = ($fb < 100) ? 0 : ($sb < 0 ? 0 : ($sb > 101 ? 100 : $sb));

    // Third bar (excess zone: max → c_max)
    if (!$hasValue || (int)($max - $c_max) == 0) {
        $tb = 0;
    } else {
        $tb = (int)($max - $value) / (int)($max - $c_max) * 100;
    }
    $tbp = ($sb < 100) ? 0 : ($tb < 0 ? 0 : ($tb > 101 ? 100 : $tb));

    echo "<tr class='sub-chart'>\n";
    echo "  <td class='text-left border-left text-capitalize pl-2'>{$label}</td>\n";
    echo "  <td class='{$alignRec} border-left px-3'>{$recommended}</td>\n";
    echo "  <td class='{$alignVal} border-left nutrient-balance px-3'>{$valueFmt}</td>\n";
    echo "  <td class='text-center border-left graph-border'><div class='progress'><div class='progress-bar {$graph}' style='width:{$fb}%'></div></div></td>\n";
    echo "  <td class='text-center border-left graph-border'><div class='progress'><div class='progress-bar {$graph}' style='width:{$sbp}%'></div></div></td>\n";
    echo "  <td class='text-center border-left border-right graph-border'><div class='progress'><div class='progress-bar {$graph}' style='width:{$tbp}%'></div></div></td>\n";
    echo "</tr>\n";
}

/**
 * Renders a <tr> for a calculated ratio (element ÷ elementTwo).
 *
 * Parameters ($p keys):
 *   element    string  Numerator column in soil_records
 *   elementTwo string  Denominator column in soil_records
 *   sbl        string  Chemical symbol
 *   nutrient   string  Display name
 *   rec        string  Column in soil_specifications for the recommended ratio
 *   type       string  Unit suffix (e.g. ':1')
 *   rec_text   string  Recommended alignment: c|r|l
 *   decimal    int     Decimal places
 *   graph      string  CSS class for progress-bar colour
 */
function soilRatio(array $row, array $spec, array $p): void
{
    $element  = $p['element']    ?? '';
    $element2 = $p['elementTwo'] ?? '';
    $sbl      = $p['sbl']        ?? '';
    $nutrient = $p['nutrient']   ?? '';
    $rec      = $p['rec']        ?? '';
    $type     = $p['type']       ?? '';
    $recText  = $p['rec_text']   ?? 'c';
    $decimal  = (int)($p['decimal'] ?? 1);
    $graph    = $p['graph']      ?? '';

    $label = ($sbl !== '') ? $sbl . ' - ' . $nutrient : $nutrient;
    $val1  = isset($row[$element])  && $row[$element]  !== '' ? (float)$row[$element]  : 0.0;
    $val2  = isset($row[$element2]) && $row[$element2] !== '' ? (float)$row[$element2] : 0.0;
    $ratio = ($val2 != 0) ? $val1 / $val2 : 0.0;

    $valueFmt    = number_format($ratio, $decimal, '.', '') . ($type !== '' ? ' ' . $type : '');
    $recommended = (isset($spec[$rec]) && $spec[$rec] !== '') ? htmlspecialchars($spec[$rec], ENT_QUOTES, 'UTF-8') : '';
    $alignRec    = match($recText) { 'r' => 'text-right', 'l' => 'text-left', default => 'text-center' };

    echo "<tr class='sub-chart'>\n";
    echo "  <td class='text-left border-left text-capitalize pl-2'>{$label}</td>\n";
    echo "  <td class='{$alignRec} border-left px-3'>{$recommended}</td>\n";
    echo "  <td class='text-center border-left nutrient-balance px-3'>{$valueFmt}</td>\n";
    echo "  <td class='text-center border-left graph-border'><div class='progress'><div class='progress-bar {$graph}' style='width:0%'></div></div></td>\n";
    echo "  <td class='text-center border-left graph-border'><div class='progress'><div class='progress-bar {$graph}' style='width:0%'></div></div></td>\n";
    echo "  <td class='text-center border-left border-right graph-border'><div class='progress'><div class='progress-bar {$graph}' style='width:0%'></div></div></td>\n";
    echo "</tr>\n";
}
?>