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}: {$result}</div>";

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