76 lines
2.8 KiB
PHP
76 lines
2.8 KiB
PHP
<?php
|
|
|
|
require_once __DIR__ . '/db.php';
|
|
|
|
function recalculate_account_balance(int $accountId): void
|
|
{
|
|
$pdo = db();
|
|
|
|
$stmt = $pdo->prepare("SELECT id, account_type, opening_balance FROM accounts WHERE id = ?");
|
|
$stmt->execute([$accountId]);
|
|
$account = $stmt->fetch();
|
|
|
|
if (!$account) {
|
|
return;
|
|
}
|
|
|
|
$opening = (float)$account['opening_balance'];
|
|
$type = $account['account_type'];
|
|
$balance = $opening;
|
|
|
|
if ($type === 'bank' || $type === 'cash' || $type === 'other') {
|
|
$stmt = $pdo->prepare("
|
|
SELECT
|
|
COALESCE(SUM(CASE
|
|
WHEN transaction_type = 'income' AND account_id = :id THEN amount
|
|
ELSE 0 END), 0) AS income_sum,
|
|
|
|
COALESCE(SUM(CASE
|
|
WHEN transaction_type = 'expense' AND account_id = :id THEN amount
|
|
ELSE 0 END), 0) AS expense_sum,
|
|
|
|
COALESCE(SUM(CASE
|
|
WHEN transaction_type = 'transfer' AND account_id = :id THEN amount
|
|
ELSE 0 END), 0) AS transfer_out_sum,
|
|
|
|
COALESCE(SUM(CASE
|
|
WHEN transaction_type = 'transfer' AND related_account_id = :id THEN amount
|
|
ELSE 0 END), 0) AS transfer_in_sum,
|
|
|
|
COALESCE(SUM(CASE
|
|
WHEN transaction_type = 'card_payment' AND account_id = :id THEN amount
|
|
ELSE 0 END), 0) AS card_payment_sum
|
|
FROM transactions
|
|
WHERE account_id = :id OR related_account_id = :id
|
|
");
|
|
$stmt->execute(['id' => $accountId]);
|
|
$row = $stmt->fetch();
|
|
|
|
$balance += (float)$row['income_sum'];
|
|
$balance -= (float)$row['expense_sum'];
|
|
$balance -= (float)$row['transfer_out_sum'];
|
|
$balance += (float)$row['transfer_in_sum'];
|
|
$balance -= (float)$row['card_payment_sum'];
|
|
} elseif ($type === 'card') {
|
|
$stmt = $pdo->prepare("
|
|
SELECT
|
|
COALESCE(SUM(CASE
|
|
WHEN transaction_type = 'expense' AND account_id = :id THEN amount
|
|
ELSE 0 END), 0) AS card_use_sum,
|
|
|
|
COALESCE(SUM(CASE
|
|
WHEN transaction_type = 'card_payment' AND related_account_id = :id THEN amount
|
|
ELSE 0 END), 0) AS paid_sum
|
|
FROM transactions
|
|
WHERE account_id = :id OR related_account_id = :id
|
|
");
|
|
$stmt->execute(['id' => $accountId]);
|
|
$row = $stmt->fetch();
|
|
|
|
$balance += (float)$row['card_use_sum'];
|
|
$balance -= (float)$row['paid_sum'];
|
|
}
|
|
|
|
$stmt = $pdo->prepare("UPDATE accounts SET current_balance = ? WHERE id = ?");
|
|
$stmt->execute([$balance, $accountId]);
|
|
} |