/** * 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 . '"'); } }