1800)) {
header("Location: logout.php");
exit();
}
$_SESSION['last_activity'] = time();
require_once 'db_connection.php';
$pageTitle = "Sales";
// Handle form submission
if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['add_sale'])) {
try {
$date = $_POST['date'];
$buyer_id = intval($_POST['buyer_id']);
$weight_tilapia = floatval($_POST['weight_tilapia']);
$weight_small_fish = floatval($_POST['weight_small_fish']);
$weight_big_fish = floatval($_POST['weight_big_fish']);
$include_delivery = isset($_POST['include_delivery']) ? 1 : 0;
$final_amount = floatval($_POST['final_amount']);
$final_amount_override = floatval($_POST['final_amount_override']);
// Calculate harvesting charges for dashboard display only
$buyer_query = "SELECT harvesting_fee_tilapia, harvesting_fee_small_fish, harvesting_fee_big_fish FROM buyers WHERE id = ?";
$stmt = $conn->prepare($buyer_query);
$stmt->bind_param("i", $buyer_id);
$stmt->execute();
$buyer_result = $stmt->get_result();
$buyer = $buyer_result->fetch_assoc();
$stmt->close();
$harvesting_charges = ($weight_tilapia * $buyer['harvesting_fee_tilapia']) +
($weight_small_fish * $buyer['harvesting_fee_small_fish']) +
($weight_big_fish * $buyer['harvesting_fee_big_fish']);
$stmt = $conn->prepare("INSERT INTO sales (date, buyer_id, weight_tilapia, weight_small_fish, weight_big_fish, include_delivery, final_amount, final_amount_override, harvesting_charges) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
$stmt->bind_param("sidddiddd", $date, $buyer_id, $weight_tilapia, $weight_small_fish, $weight_big_fish, $include_delivery, $final_amount, $final_amount_override, $harvesting_charges);
$stmt->execute();
$stmt->close();
$_SESSION['success'] = "Sale added successfully!";
header("Location: sales.php");
exit();
} catch (Exception $e) {
$_SESSION['error'] = "Error adding sale.";
error_log("Sales error: " . $e->getMessage());
}
}
// Get buyers
$buyersQuery = "SELECT id, name FROM buyers ORDER BY name";
$buyers = $conn->query($buyersQuery)->fetch_all(MYSQLI_ASSOC);
// Get monthly sales by buyer
$monthlySalesByBuyerQuery = "SELECT
b.name as buyer_name,
DATE_FORMAT(s.date, '%Y-%m') as month,
DATE_FORMAT(s.date, '%M %Y') as month_display,
SUM(s.weight_tilapia) as total_tilapia,
SUM(s.weight_small_fish) as total_small_fish,
SUM(s.weight_big_fish) as total_big_fish,
SUM(s.weight_tilapia + s.weight_small_fish + s.weight_big_fish) as total_weight,
SUM(COALESCE(NULLIF(s.final_amount_override, 0), s.final_amount)) as total_sales,
COALESCE((SELECT SUM(p.amount_paid)
FROM payments p
WHERE p.buyer_id = b.id
AND DATE_FORMAT(p.date, '%Y-%m') = DATE_FORMAT(s.date, '%Y-%m')), 0) as total_payment
FROM buyers b
LEFT JOIN sales s ON b.id = s.buyer_id
WHERE s.date IS NOT NULL
GROUP BY b.id, DATE_FORMAT(s.date, '%Y-%m')
ORDER BY DATE_FORMAT(s.date, '%Y-%m') DESC, b.name";
$monthlySalesByBuyer = $conn->query($monthlySalesByBuyerQuery)->fetch_all(MYSQLI_ASSOC);
// Calculate grand totals
$grandTotalTilapia = 0;
$grandTotalSmallFish = 0;
$grandTotalBigFish = 0;
$grandTotalWeight = 0;
$grandTotalSales = 0;
$grandTotalPayment = 0;
$monthlySalesRows = '';
foreach ($monthlySalesByBuyer as $row) {
$grandTotalTilapia += $row['total_tilapia'];
$grandTotalSmallFish += $row['total_small_fish'];
$grandTotalBigFish += $row['total_big_fish'];
$grandTotalWeight += $row['total_weight'];
$grandTotalSales += $row['total_sales'];
$grandTotalPayment += $row['total_payment'];
$monthlySalesRows .= "
| " . htmlspecialchars($row['buyer_name']) . " |
" . htmlspecialchars($row['month_display']) . " |
" . number_format($row['total_tilapia'], 2) . " kg |
" . number_format($row['total_small_fish'], 2) . " kg |
" . number_format($row['total_big_fish'], 2) . " kg |
" . number_format($row['total_weight'], 2) . " kg |
₹" . number_format($row['total_sales'], 2) . " |
₹" . number_format($row['total_payment'], 2) . " |
";
}
// Add grand total row
$monthlySalesRows .= "
| GRAND TOTAL: |
" . number_format($grandTotalTilapia, 2) . " kg |
" . number_format($grandTotalSmallFish, 2) . " kg |
" . number_format($grandTotalBigFish, 2) . " kg |
" . number_format($grandTotalWeight, 2) . " kg |
₹" . number_format($grandTotalSales, 2) . " |
₹" . number_format($grandTotalPayment, 2) . " |
";
// Pagination
$page = isset($_GET['page']) ? intval($_GET['page']) : 1;
$per_page = 20;
$offset = ($page - 1) * $per_page;
// Get total sales count
$totalQuery = "SELECT COUNT(*) as total FROM sales";
$totalResult = $conn->query($totalQuery);
$totalSales = $totalResult->fetch_assoc()['total'];
$totalPages = ceil($totalSales / $per_page);
// Get sales - use final_amount_override if set, otherwise final_amount
$salesQuery = "SELECT s.*, b.name as buyer_name FROM sales s JOIN buyers b ON s.buyer_id = b.id ORDER BY s.date DESC LIMIT $offset, $per_page";
$sales = $conn->query($salesQuery)->fetch_all(MYSQLI_ASSOC);
// Generate buyer options
$buyerOptions = '';
foreach ($buyers as $buyer) {
$buyerOptions .= "";
}
// Generate sales rows
$salesRows = '';
foreach ($sales as $sale) {
// Use override amount if set, otherwise use final_amount
$display_amount = ($sale['final_amount_override'] != null && $sale['final_amount_override'] > 0) ? $sale['final_amount_override'] : $sale['final_amount'];
$salesRows .= "
| {$sale['date']} |
{$sale['buyer_name']} |
" . number_format($sale['weight_tilapia'], 2) . " kg |
" . number_format($sale['weight_small_fish'], 2) . " kg |
" . number_format($sale['weight_big_fish'], 2) . " kg |
" . ($sale['include_delivery'] ? 'Yes' : 'No') . " |
₹" . number_format($display_amount, 2) . " |
|
";
}
// Generate pagination
$pagination = '';
if ($totalPages > 1) {
$pagination = '';
}
$content = <<
| Date |
Buyer |
Tilapia |
Small Fish |
Big Fish |
Delivery |
Amount |
Actions |
$salesRows
$pagination
| Buyer |
Month |
Tilapia |
Small Fish |
Big Fish |
Total Weight |
Sales Amount |
Payment |
$monthlySalesRows
HTML;
include 'main_layout.php';
$conn->close();
?>