/**
* @param Collection $accounts
* @param Carbon $start
* @param Carbon $end
*
* @return string
*/
public function spentInPeriod(Collection $accounts, Carbon $start, Carbon $end) : string
{
/** @var HasMany $query */
$query = $this->user->transactionJournals()->expanded()->transactionTypes([TransactionType::WITHDRAWAL, TransactionType::TRANSFER]);
if ($end >= $start) {
$query->before($end)->after($start);
}
if ($accounts->count() > 0) {
$accountIds = $accounts->pluck('id')->toArray();
$query->leftJoin('transactions as source', function (JoinClause $join) {
$join->on('source.transaction_journal_id', '=', 'transaction_journals.id')->where('source.amount', '<', 0);
});
$query->leftJoin('transactions as destination', function (JoinClause $join) {
$join->on('destination.transaction_journal_id', '=', 'transaction_journals.id')->where('destination.amount', '>', 0);
});
$query->whereIn('source.account_id', $accountIds);
$query->whereNotIn('destination.account_id', $accountIds);
$query->whereNull('source.deleted_at');
$query->whereNull('destination.deleted_at');
$query->distinct();
}
// remove group by
$query->getQuery()->getQuery()->groups = null;
$ids = $query->get(['transaction_journals.id'])->pluck('id')->toArray();
$sum = $this->user->transactions()->whereIn('transaction_journal_id', $ids)->where('amount', '<', '0')->whereNull('transactions.deleted_at')->sum('amount');
return strval($sum);
}
/**
* @param Collection $accounts
* @param Carbon $start
* @param Carbon $end
*
* @return string
*/
public function spentInPeriodWithoutBudget(Collection $accounts, Carbon $start, Carbon $end) : string
{
$types = [TransactionType::WITHDRAWAL];
$query = $this->user->transactionJournals()->distinct()->transactionTypes($types)->leftJoin('budget_transaction_journal', 'budget_transaction_journal.transaction_journal_id', '=', 'transaction_journals.id')->leftJoin('transactions as source', function (JoinClause $join) {
$join->on('source.transaction_journal_id', '=', 'transaction_journals.id')->where('source.amount', '<', 0);
})->leftJoin('transactions as destination', function (JoinClause $join) {
$join->on('destination.transaction_journal_id', '=', 'transaction_journals.id')->where('destination.amount', '>', 0);
})->leftJoin('budget_transaction', 'source.id', '=', 'budget_transaction.transaction_id')->whereNull('budget_transaction_journal.id')->whereNull('budget_transaction.id')->before($end)->after($start)->whereNull('source.deleted_at')->whereNull('destination.deleted_at')->where('transaction_journals.completed', 1);
if ($accounts->count() > 0) {
$accountIds = $accounts->pluck('id')->toArray();
$set = join(', ', $accountIds);
$query->whereRaw('(source.account_id in (' . $set . ') XOR destination.account_id in (' . $set . '))');
}
$ids = $query->get(['transaction_journals.id'])->pluck('id')->toArray();
$sum = '0';
if (count($ids) > 0) {
$sum = strval($this->user->transactions()->whereIn('transaction_journal_id', $ids)->where('amount', '<', '0')->whereNull('transactions.deleted_at')->sum('amount'));
}
return $sum;
}
/**
* @param Collection $categories
* @param Collection $accounts
* @param array $types
* @param Carbon $start
* @param Carbon $end
*
* @return string
*/
private function sumInPeriod(Collection $categories, Collection $accounts, array $types, Carbon $start, Carbon $end) : string
{
// first collect actual transaction journals (fairly easy)
$query = $this->user->transactionJournals()->transactionTypes($types)->leftJoin('transactions as source', function (JoinClause $join) {
$join->on('source.transaction_journal_id', '=', 'transaction_journals.id')->where('source.amount', '<', 0);
})->leftJoin('transactions as destination', function (JoinClause $join) {
$join->on('destination.transaction_journal_id', '=', 'transaction_journals.id')->where('destination.amount', '>', 0);
});
if ($end >= $start) {
$query->before($end)->after($start);
}
if ($accounts->count() > 0) {
$accountIds = $accounts->pluck('id')->toArray();
$set = join(', ', $accountIds);
$query->whereRaw('(source.account_id in (' . $set . ') XOR destination.account_id in (' . $set . '))');
}
if ($categories->count() > 0) {
$categoryIds = $categories->pluck('id')->toArray();
$query->leftJoin('category_transaction_journal', 'category_transaction_journal.transaction_journal_id', '=', 'transaction_journals.id');
$query->whereIn('category_transaction_journal.category_id', $categoryIds);
}
// that should do it:
$first = strval($query->sum('source.amount'));
// then collection transactions (harder)
$query = $this->user->transactions()->where('transactions.amount', '<', 0)->where('transaction_journals.date', '>=', $start->format('Y-m-d 00:00:00'))->where('transaction_journals.date', '<=', $end->format('Y-m-d 23:59:59'));
if (count($types) > 0) {
$query->leftJoin('transaction_types', 'transaction_types.id', '=', 'transaction_journals.transaction_type_id');
$query->whereIn('transaction_types.type', $types);
}
if ($accounts->count() > 0) {
$accountIds = $accounts->pluck('id')->toArray();
$query->whereIn('transactions.account_id', $accountIds);
}
if ($categories->count() > 0) {
$categoryIds = $categories->pluck('id')->toArray();
$query->leftJoin('category_transaction', 'category_transaction.transaction_id', '=', 'transactions.id');
$query->whereIn('category_transaction.category_id', $categoryIds);
}
$second = strval($query->sum('transactions.amount'));
return bcadd($first, $second);
}
