/**
* 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;
}
