/**
* Returns the amount in the account before the specified transaction took place.
*
* @param Transaction $transaction
*
* @return string
*/
public function balanceBeforeTransaction(Transaction $transaction) : string
{
// some dates from journal
$journal = $transaction->transactionJournal;
$query = Transaction::leftJoin('transaction_journals', 'transactions.transaction_journal_id', '=', 'transaction_journals.id')->where('transactions.account_id', $transaction->account_id)->where('transaction_journals.user_id', $this->user->id)->where(function (Builder $q) use($journal) {
$q->where('transaction_journals.date', '<', $journal->date->format('Y-m-d'));
$q->orWhere(function (Builder $qq) use($journal) {
$qq->where('transaction_journals.date', '=', $journal->date->format('Y-m-d'));
$qq->where('transaction_journals.order', '>', $journal->order);
});
})->where('transactions.id', '!=', $transaction->id)->whereNull('transactions.deleted_at')->whereNull('transaction_journals.deleted_at')->groupBy('transaction_journals.id');
$sum = $query->sum('transactions.amount');
return strval($sum);
}
/**
*
* @param array $ids
* @param \Carbon\Carbon $date
*
* @return float
*/
public function balancesById(array $ids, Carbon $date)
{
// abuse chart properties:
$cache = new CacheProperties();
$cache->addProperty($ids);
$cache->addProperty('balances');
$cache->addProperty($date);
if ($cache->has()) {
return $cache->get();
// @codeCoverageIgnore
}
bcscale(2);
$balances = Transaction::leftJoin('transaction_journals', 'transaction_journals.id', '=', 'transactions.transaction_journal_id')->where('transaction_journals.date', '<=', $date->format('Y-m-d'))->groupBy('transactions.account_id')->whereIn('transactions.account_id', $ids)->get(['transactions.account_id', DB::Raw('sum(`transactions`.`amount`) as aggregate')]);
$result = [];
foreach ($balances as $entry) {
$accountId = intval($entry->account_id);
$balance = round($entry->aggregate, 2);
$result[$accountId] = $balance;
}
$cache->store($result);
return $result;
}
/**
* Reports on deleted transactions that are connected to a not deleted journal.
*/
private function reportTransactions()
{
$set = Transaction::leftJoin('transaction_journals', 'transactions.transaction_journal_id', '=', 'transaction_journals.id')->whereNotNull('transactions.deleted_at')->whereNull('transaction_journals.deleted_at')->get(['transactions.id as transaction_id', 'transactions.deleted_at as transaction_deleted', 'transaction_journals.id as journal_id', 'transaction_journals.deleted_at']);
/** @var stdClass $entry */
foreach ($set as $entry) {
$this->error('Error: Transaction journal #' . $entry->journal_id . ' should have been deleted, but has not.' . ' Find it in the table called `transaction_journals` and change the `deleted_at` field to: "' . $entry->transaction_deleted . '"');
}
}