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 "
1
{$nutrient}
@
N/A
kg/Ha
";
}
$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 "
1
{$nutrient}
@
{$result}
kg/Ha
";
} 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 "{$nutrient}: N/A
";
}
$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 "{$nutrient}: {$result}
";
} catch (PDOException $e) {
error_log("Database error in soilAnalysisReportCalcs: " . $e->getMessage());
return "{$nutrient}: Error
";
}
}
/**
* Renders a 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. "NO3-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 "
\n";
echo " | {$label} | \n";
echo " {$recommended} | \n";
echo " {$valueFmt} | \n";
echo " | \n";
echo " | \n";
echo " | \n";
echo "
\n";
}
/**
* Renders a 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 "
\n";
echo " | {$label} | \n";
echo " {$recommended} | \n";
echo " {$valueFmt} | \n";
echo " | \n";
echo " | \n";
echo " | \n";
echo "
\n";
}
?>