query($query);
return $result->fetch_all(MYSQLI_ASSOC);
}
function getPayments($conn) {
$query = "SELECT p.*, b.name as buyer_name
FROM payments p
JOIN buyers b ON p.buyer_id = b.id
ORDER BY p.date DESC";
$result = $conn->query($query);
return $result->fetch_all(MYSQLI_ASSOC);
}
function calculateBalance($conn, $buyerId, $date) {
$salesQuery = "SELECT SUM(final_amount) as total_sales
FROM sales
WHERE buyer_id = ? AND date <= ?";
$salesStmt = $conn->prepare($salesQuery);
$salesStmt->bind_param("is", $buyerId, $date);
$salesStmt->execute();
$salesResult = $salesStmt->get_result();
$totalSales = $salesResult->fetch_assoc()['total_sales'] ?? 0;
$paymentsQuery = "SELECT SUM(amount_paid) as total_payments
FROM payments
WHERE buyer_id = ? AND date <= ?";
$paymentsStmt = $conn->prepare($paymentsQuery);
$paymentsStmt->bind_param("is", $buyerId, $date);
$paymentsStmt->execute();
$paymentsResult = $paymentsStmt->get_result();
$totalPayments = $paymentsResult->fetch_assoc()['total_payments'] ?? 0;
return $totalSales - $totalPayments;
}
if ($_SERVER["REQUEST_METHOD"] == "POST") {
if (isset($_POST["add_payment"])) {
$query = "INSERT INTO payments (date, buyer_id, amount_paid) VALUES (?, ?, ?)";
$stmt = $conn->prepare($query);
$stmt->bind_param("sid", $_POST["date"], $_POST["buyer_id"], $_POST["amount_paid"]);
$stmt->execute();
$balance = calculateBalance($conn, $_POST["buyer_id"], $_POST["date"]);
$updateQuery = "UPDATE payments SET balance = ? WHERE id = LAST_INSERT_ID()";
$updateStmt = $conn->prepare($updateQuery);
$updateStmt->bind_param("d", $balance);
$updateStmt->execute();
$_SESSION['flash_message'] = "Payment added successfully";
header("Location: payments.php");
exit();
}
}
$buyers = getBuyers($conn);
$payments = getPayments($conn);
function generateBuyerOptions($buyers) {
$options = '';
foreach ($buyers as $buyer) {
$options .= "";
}
return $options;
}
function generatePaymentRows($payments) {
$rows = '';
foreach ($payments as $payment) {
$rows .= "
| {$payment['date']} |
{$payment['buyer_name']} |
₹{$payment['amount_paid']} |
₹{$payment['balance']} |
|
";
}
return $rows;
}
$buyerOptions = generateBuyerOptions($buyers);
$paymentRows = generatePaymentRows($payments);
$content = <<
Payments Management
| Date |
Buyer |
Amount Paid |
Balance |
Actions |
$paymentRows
HTML;
include 'main_layout.php';
?>