/** * Returns an array with the following key:value pairs: * * yyyy-mm-dd:<amount> * * Where yyyy-mm-dd is the date and <amount> is the money spent using DEPOSITS in the $category * from all the users accounts. * * @param Category $category * @param Carbon $start * @param Carbon $end * * @return array */ public function spentPerDay(Category $category, Carbon $start, Carbon $end) { /** @var Collection $query */ $query = $category->transactionJournals()->transactionTypes([TransactionType::WITHDRAWAL])->leftJoin('transactions', 'transactions.transaction_journal_id', '=', 'transaction_journals.id')->where('transactions.amount', '<', 0)->before($end)->after($start)->groupBy('date')->get(['transaction_journals.date as dateFormatted', DB::Raw('SUM(`transactions`.`amount`) AS `sum`')]); $return = []; foreach ($query->toArray() as $entry) { $return[$entry['dateFormatted']] = $entry['sum']; } return $return; }
/** * @param Category $category * * @return int */ public function countJournalsInRange(Category $category, Carbon $start, Carbon $end) { return $category->transactionJournals()->before($end)->after($start)->count(); }
/** * @param Category $category * @param int $page * * @return Collection */ public function getJournals(Category $category, $page) { $offset = $page > 0 ? $page * 50 : 0; return $category->transactionJournals()->withRelevantData()->take(50)->offset($offset)->orderBy('transaction_journals.date', 'DESC')->orderBy('transaction_journals.order', 'ASC')->orderBy('transaction_journals.id', 'DESC')->get(['transaction_journals.*']); }
/** * This method returns the sum of the journals in the category, optionally * limited by a start or end date. * * @param Category $category * @param Carbon $start * @param Carbon $end * * @return string */ public function journalsSum(Category $category, Carbon $start = null, Carbon $end = null) { $query = $category->transactionJournals()->orderBy('transaction_journals.date', 'DESC')->orderBy('transaction_journals.order', 'ASC')->orderBy('transaction_journals.id', 'DESC'); if (!is_null($start)) { $query->after($start); } if (!is_null($end)) { $query->before($end); } return $query->get(['transaction_journals.*'])->sum('correct_amount'); }
/** * @param Category $category * @param Collection $accounts * * @return Carbon */ public function lastUseDate(Category $category, Collection $accounts) : Carbon { $last = null; /** @var TransactionJournal $first */ $lastJournalQuery = $category->transactionJournals()->orderBy('date', 'DESC'); if ($accounts->count() > 0) { // filter journals: $ids = $accounts->pluck('id')->toArray(); $lastJournalQuery->leftJoin('transactions as t', 't.transaction_journal_id', '=', 'transaction_journals.id'); $lastJournalQuery->whereIn('t.account_id', $ids); } $lastJournal = $lastJournalQuery->first(['transaction_journals.*']); if ($lastJournal) { $last = $lastJournal->date; } // check transactions: $lastTransactionQuery = $category->transactions()->leftJoin('transaction_journals', 'transaction_journals.id', '=', 'transactions.transaction_journal_id')->orderBy('transaction_journals.date', 'DESC'); if ($accounts->count() > 0) { // filter journals: $ids = $accounts->pluck('id')->toArray(); $lastTransactionQuery->whereIn('transactions.account_id', $ids); } $lastTransaction = $lastTransactionQuery->first(['transaction_journals.*']); if (!is_null($lastTransaction) && (!is_null($last) && $lastTransaction->date < $last || is_null($last))) { $last = new Carbon($lastTransaction->date); } if (is_null($last)) { return new Carbon('1900-01-01'); } return $last; }