beginTransaction();
try {
$stmt = $pdo->prepare(
"INSERT INTO daybook_readings (date, nozzle_id, opening_reading, closing_reading, price)
VALUES (?, ?, ?, ?, ?)"
);
// Fetch fuel types for nozzles
$nozzle_fuel_types = [];
$nozzles_stmt = $pdo->query("
SELECT n.id, ft.fuel_name
FROM nozzles n
JOIN pumps p ON n.pump_id = p.id
JOIN tanks t ON p.tank_id = t.id
JOIN fuel_types ft ON t.fuel_type_id = ft.id
");
while ($row = $nozzles_stmt->fetch(PDO::FETCH_ASSOC)) {
$nozzle_fuel_types[$row['id']] = $row['fuel_name'];
}
foreach ($readings as $reading) {
$nozzle_id = $reading['nozzle_id'];
$opening = $reading['opening'];
$closing = $reading['closing'];
if ($closing < $opening) {
throw new Exception("Closing reading cannot be less than opening reading for one of the nozzles.");
}
$fuel_name = $nozzle_fuel_types[$nozzle_id] ?? null;
if (!$fuel_name || !isset($prices[$fuel_name])) {
throw new Exception("Could not determine price for nozzle ID: $nozzle_id");
}
$price = $prices[$fuel_name];
$stmt->execute([$date, $nozzle_id, $opening, $closing, $price]);
}
$pdo->commit();
$message = "Daybook readings saved successfully!";
} catch (PDOException $e) {
$pdo->rollBack();
if ($e->errorInfo[1] == 1062) { // Duplicate entry
$error = "Error: Daybook readings for this date and nozzle already exist.";
} else {
$error = "Database error: " . $e->getMessage();
}
} catch (Exception $e) {
$pdo->rollBack();
$error = "Error: " . $e->getMessage();
}
}
// Fetch previous day's closing readings to suggest as opening readings
$previous_day_readings = [];
if (isset($_GET['date'])) {
$current_date = $_GET['date'];
$previous_date = date('Y-m-d', strtotime($current_date . ' -1 day'));
$stmt = db()->prepare("SELECT nozzle_id, closing_reading FROM daybook_readings WHERE date = ?");
$stmt->execute([$previous_date]);
$readings = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($readings as $reading) {
$previous_day_readings[$reading['nozzle_id']] = $reading['closing_reading'];
}
}
// Fetch nozzles and their associated fuel types
$nozzles_stmt = db()->query("
SELECT n.id, n.nozzle_number, ft.fuel_name
FROM nozzles n
JOIN pumps p ON n.pump_id = p.id
JOIN tanks t ON p.tank_id = t.id
JOIN fuel_types ft ON t.fuel_type_id = ft.id
ORDER BY n.id
");
$nozzles = $nozzles_stmt->fetchAll(PDO::FETCH_ASSOC);
// Group nozzles by fuel type
$nozzles_by_fuel = [];
foreach ($nozzles as $nozzle) {
$nozzles_by_fuel[$nozzle['fuel_name']][] = $nozzle;
}
?>