save_contribution.php

<?php
include 'db_connect.php';

if (isset($_POST['member_id'], $_POST['contribution_amount'], $_POST['month'], $_POST['contribution_date'])) {
    $member_id = $_POST['member_id'];
    $contribution_amount = $_POST['contribution_amount'];
    $month = $_POST['month'];
    $contribution_date = $_POST['contribution_date'];
    $salary_account_number = $_POST['salary_account_number'];
    $description = $_POST['description'] ?? ''; // Optional

    // Start a transaction to ensure data integrity
    $conn->begin_transaction();

    try {
        // Insert the contribution into the contributions table
        $sql = "INSERT INTO contributions (member_id, contribution_amount, month, contribution_date, description, salary_account_number) 
                VALUES (?, ?, ?, ?, ?, ?)";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("idssss", $member_id, $contribution_amount, $month, $contribution_date, $description, $salary_account_number);
        
        if (!$stmt->execute()) {
            throw new Exception("Failed to insert contribution: " . $stmt->error);
        }

        // Check if the salary account number exists in member_balances
        $check_balance_sql = "SELECT balance FROM member_balances WHERE salary_account_number = ?";
        $stmt = $conn->prepare($check_balance_sql);
        $stmt->bind_param("s", $salary_account_number);
        $stmt->execute();
        $result = $stmt->get_result();

        if ($result->num_rows > 0) {
            // If account exists, fetch the current balance
            $row = $result->fetch_assoc();
            $previous_balance = $row['balance'];
            $new_balance = $previous_balance + $contribution_amount;

            // Update the existing balance
            $update_balance_sql = "UPDATE member_balances 
                                   SET balance = ?, date_updated = NOW() 
                                   WHERE salary_account_number = ?";
            $stmt = $conn->prepare($update_balance_sql);
            $stmt->bind_param("ds", $new_balance, $salary_account_number);
        } else {
            // If account does not exist, create a new record with the contribution amount
            $new_balance = $contribution_amount;
            $insert_balance_sql = "INSERT INTO member_balances (salary_account_number, balance, date_updated) 
                                   VALUES (?, ?, NOW())";
            $stmt = $conn->prepare($insert_balance_sql);
            $stmt->bind_param("sd", $salary_account_number, $new_balance);
        }

        if (!$stmt->execute()) {
            throw new Exception("Failed to update balance: " . $stmt->error);
        }

        // Commit the transaction
        $conn->commit();
        echo 1; // Success
    } catch (Exception $e) {
        $conn->rollback();
        echo "Error: " . $e->getMessage(); // Debugging message
    }
}
?>