94 lines
3.7 KiB
PHP
94 lines
3.7 KiB
PHP
<?php
|
|
|
|
namespace Modules\Accounting\Services;
|
|
|
|
use Illuminate\Support\Facades\DB;
|
|
use Modules\Accounting\Entities\AccountingAccount;
|
|
use Modules\Accounting\Entities\AccountingBankReconciliation;
|
|
use Modules\Accounting\Entities\AccountingBankReconciliationItem;
|
|
use Modules\Accounting\Utils\AccountingUtil;
|
|
|
|
class BankReconciliationService
|
|
{
|
|
public function __construct(protected AccountingUtil $accountingUtil) {}
|
|
|
|
public function create(int $businessId, array $data, int $userId): AccountingBankReconciliation
|
|
{
|
|
$bookBalance = $this->getBookBalance(
|
|
$businessId,
|
|
(int) $data['accounting_account_id'],
|
|
$data['statement_date']
|
|
);
|
|
|
|
$reconciliation = AccountingBankReconciliation::create([
|
|
'business_id' => $businessId,
|
|
'accounting_account_id' => $data['accounting_account_id'],
|
|
'payment_account_id' => $data['payment_account_id'] ?? null,
|
|
'statement_date' => $data['statement_date'],
|
|
'statement_balance' => $data['statement_balance'],
|
|
'book_balance' => $bookBalance,
|
|
'adjusted_balance' => $bookBalance,
|
|
'status' => 'draft',
|
|
'note' => $data['note'] ?? null,
|
|
'created_by' => $userId,
|
|
]);
|
|
|
|
$transactions = DB::table('accounting_accounts_transactions as aat')
|
|
->join('accounting_accounts as aa', 'aa.id', '=', 'aat.accounting_account_id')
|
|
->where('aa.business_id', $businessId)
|
|
->where('aat.accounting_account_id', $data['accounting_account_id'])
|
|
->whereDate('aat.operation_date', '<=', $data['statement_date'])
|
|
->select('aat.id', 'aat.amount', 'aat.type', 'aat.note', 'aat.operation_date')
|
|
->orderBy('aat.operation_date')
|
|
->get();
|
|
|
|
foreach ($transactions as $tx) {
|
|
AccountingBankReconciliationItem::create([
|
|
'reconciliation_id' => $reconciliation->id,
|
|
'aat_id' => $tx->id,
|
|
'amount' => $tx->type === 'debit' ? $tx->amount : -1 * $tx->amount,
|
|
'is_matched' => false,
|
|
'description' => $tx->note,
|
|
]);
|
|
}
|
|
|
|
return $reconciliation->load('items');
|
|
}
|
|
|
|
public function complete(AccountingBankReconciliation $reconciliation, array $matchedItemIds): AccountingBankReconciliation
|
|
{
|
|
AccountingBankReconciliationItem::where('reconciliation_id', $reconciliation->id)
|
|
->update(['is_matched' => false]);
|
|
|
|
if (! empty($matchedItemIds)) {
|
|
AccountingBankReconciliationItem::where('reconciliation_id', $reconciliation->id)
|
|
->whereIn('id', $matchedItemIds)
|
|
->update(['is_matched' => true]);
|
|
}
|
|
|
|
$matchedSum = AccountingBankReconciliationItem::where('reconciliation_id', $reconciliation->id)
|
|
->where('is_matched', true)
|
|
->sum('amount');
|
|
|
|
$reconciliation->adjusted_balance = (float) $reconciliation->book_balance + (float) $matchedSum;
|
|
$reconciliation->status = 'completed';
|
|
$reconciliation->save();
|
|
|
|
return $reconciliation;
|
|
}
|
|
|
|
protected function getBookBalance(int $businessId, int $accountId, string $asOfDate): float
|
|
{
|
|
$balanceFormula = $this->accountingUtil->balanceFormula();
|
|
|
|
$result = AccountingAccount::join('accounting_accounts_transactions as AAT', 'AAT.accounting_account_id', '=', 'accounting_accounts.id')
|
|
->where('accounting_accounts.business_id', $businessId)
|
|
->where('accounting_accounts.id', $accountId)
|
|
->whereDate('AAT.operation_date', '<=', $asOfDate)
|
|
->select(DB::raw($balanceFormula))
|
|
->first();
|
|
|
|
return (float) ($result->balance ?? 0);
|
|
}
|
|
}
|