1800)) {
header("Location: logout.php");
exit();
}
$_SESSION['last_activity'] = time();
require_once 'db_connection.php';
$pageTitle = "Dashboard";
// Helper function to safely format currency
function formatCurrency($value) {
if (is_numeric($value)) {
return '₹' . number_format((float)$value, 2, '.', ',');
}
return '₹0.00';
}
// Function to safely execute SQL queries
function executeQuery($conn, $query) {
$result = $conn->query($query);
if ($result === false) {
error_log("Query failed: " . $conn->error);
throw new Exception("Database query failed.");
}
return $result;
}
try {
// Get total sales value
$result = executeQuery($conn, "SELECT SUM(final_amount) as total FROM sales");
$totalSales = $result->fetch_assoc()['total'] ?? 0;
// Get total expense value
$result = executeQuery($conn, "SELECT SUM(amount) as total FROM expenses");
$totalExpenses = $result->fetch_assoc()['total'] ?? 0;
// Get total payments value
$result = executeQuery($conn, "SELECT SUM(amount_paid) as total FROM payments");
$totalPayments = $result->fetch_assoc()['total'] ?? 0;
// Get total weight harvested
$result = executeQuery($conn, "SELECT SUM(weight_tilapia + weight_small_fish + weight_big_fish) as total FROM sales");
$totalWeightHarvested = $result->fetch_assoc()['total'] ?? 0;
// Get average rate per kg
$result = executeQuery($conn, "SELECT SUM(final_amount) / NULLIF(SUM(weight_tilapia + weight_small_fish + weight_big_fish), 0) as avg_rate FROM sales");
$averageRatePerKg = $result->fetch_assoc()['avg_rate'] ?? 0;
// Get initial values
$result = executeQuery($conn, "SELECT * FROM initial_values LIMIT 1");
$initialValues = $result->fetch_assoc();
$initialCash = $initialValues['cash_in_hand'] ?? 0;
// Calculate cash in hand
$cashInHand = $initialCash + $totalPayments - $totalExpenses;
// Format values for display
$formattedTotalSales = formatCurrency($totalSales);
$formattedTotalExpenses = formatCurrency($totalExpenses);
$formattedTotalPayments = formatCurrency($totalPayments);
$formattedCashInHand = formatCurrency($cashInHand);
$formattedTotalWeightHarvested = number_format($totalWeightHarvested, 2);
$formattedAverageRatePerKg = formatCurrency($averageRatePerKg);
// Generate Fortnightly Harvest Charges rows
$fortnightlyRows = '';
$query = "SELECT
DATE_FORMAT(date, '%Y-%m') as month_year,
CASE
WHEN DAY(date) <= 15 THEN 'First Half'
ELSE 'Second Half'
END as half_month,
MIN(date) as start_date,
MAX(date) as end_date,
SUM(weight_tilapia) as total_tilapia,
SUM(weight_small_fish) as total_small_fish,
SUM(weight_big_fish) as total_big_fish,
SUM(harvesting_charges) as total_charges
FROM sales
WHERE date BETWEEN '2024-10-01' AND '2025-09-30'
GROUP BY
month_year,
half_month
HAVING total_charges > 0
ORDER BY start_date";
$result = executeQuery($conn, $query);
while ($row = $result->fetch_assoc()) {
$startDate = date('Y-m-d', strtotime($row['start_date']));
$endDate = date('Y-m-d', strtotime($row['end_date']));
if ($row['half_month'] == 'First Half') {
$fortnightName = date('M d', strtotime($startDate)) . " - " . date('M d', strtotime($row['month_year'] . '-15'));
} else {
$fortnightName = date('M d', strtotime($row['month_year'] . '-16')) . " - " . date('M d', strtotime($endDate));
}
$fortnightlyRows .= "
| " . htmlspecialchars($fortnightName) . " |
" . number_format($row['total_tilapia'], 2) . " |
" . number_format($row['total_small_fish'], 2) . " |
" . number_format($row['total_big_fish'], 2) . " |
" . formatCurrency($row['total_charges']) . " |
";
}
// Generate Buyer Data rows
$buyerRows = '';
$madhuBalance = $initialValues['madhu_balance'] ?? 0;
$rathnaBalance = $initialValues['rathna_balance'] ?? 0;
$jambukkuttiBalance = $initialValues['jambukkutti_balance'] ?? 0;
$query = "SELECT
b.name as buyer_name,
COALESCE(SUM(s.final_amount), 0) as total_sales,
COALESCE((SELECT SUM(amount_paid) FROM payments p WHERE p.buyer_id = b.id), 0) as total_payments,
CASE
WHEN b.name = 'Madhu' THEN COALESCE(SUM(s.final_amount), 0) - COALESCE((SELECT SUM(amount_paid) FROM payments p WHERE p.buyer_id = b.id), 0) + $madhuBalance
WHEN b.name = 'Rathna' THEN COALESCE(SUM(s.final_amount), 0) - COALESCE((SELECT SUM(amount_paid) FROM payments p WHERE p.buyer_id = b.id), 0) + $rathnaBalance
WHEN b.name = 'Jambukkutti' THEN COALESCE(SUM(s.final_amount), 0) - COALESCE((SELECT SUM(amount_paid) FROM payments p WHERE p.buyer_id = b.id), 0) + $jambukkuttiBalance
ELSE COALESCE(SUM(s.final_amount), 0) - COALESCE((SELECT SUM(amount_paid) FROM payments p WHERE p.buyer_id = b.id), 0)
END as balance
FROM buyers b
LEFT JOIN sales s ON b.id = s.buyer_id
GROUP BY b.id
ORDER BY b.name";
$result = executeQuery($conn, $query);
while ($row = $result->fetch_assoc()) {
$buyerRows .= "
| " . htmlspecialchars($row['buyer_name']) . " |
" . formatCurrency($row['total_sales']) . " |
" . formatCurrency($row['total_payments']) . " |
" . formatCurrency($row['balance']) . " |
";
}
// Get monthly data for the table
$query = "SELECT
DATE_FORMAT(s.date, '%Y-%m') as month,
SUM(s.final_amount) as sales_value,
(SELECT SUM(amount) FROM expenses e WHERE DATE_FORMAT(e.date, '%Y-%m') = DATE_FORMAT(s.date, '%Y-%m')) as expense_value,
(SELECT SUM(amount_paid) FROM payments p WHERE DATE_FORMAT(p.date, '%Y-%m') = DATE_FORMAT(s.date, '%Y-%m')) as payments_value,
SUM(s.weight_tilapia + s.weight_small_fish + s.weight_big_fish) as harvested_weight,
SUM(s.final_amount) / NULLIF(SUM(s.weight_tilapia + s.weight_small_fish + s.weight_big_fish), 0) as rate_per_kg
FROM sales s
WHERE s.date BETWEEN DATE_SUB(CURDATE(), INTERVAL 12 MONTH) AND CURDATE()
GROUP BY DATE_FORMAT(s.date, '%Y-%m')
ORDER BY s.date DESC";
$monthlyData = executeQuery($conn, $query);
// Generate monthly report rows
$monthlyRows = '';
while ($row = $monthlyData->fetch_assoc()) {
$monthlyRows .= "
| " . date('M Y', strtotime($row['month'] . '-01')) . " |
" . formatCurrency($row['sales_value']) . " |
" . formatCurrency($row['expense_value']) . " |
" . formatCurrency($row['payments_value']) . " |
" . number_format($row['harvested_weight'], 2) . " kg |
" . formatCurrency($row['rate_per_kg']) . " |
";
}
$content = <<
$formattedTotalWeightHarvested kg
$formattedAverageRatePerKg
| Fortnight |
Tilapia (kg) |
Small Fish (kg) |
Big Fish (kg) |
Harvest Charges |
$fortnightlyRows
| Buyer |
Total Sales |
Total Payments |
Balance |
$buyerRows
| Month |
Sales Value |
Expenses |
Payments |
Harvested Weight |
Rate/Kg |
$monthlyRows
HTML;
} catch (Exception $e) {
$content = " An error occurred while loading dashboard data.
";
error_log("Dashboard error: " . $e->getMessage());
}
include 'main_layout.php';
$conn->close();
?>