/**
* @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 Bill $bill
*
* @return Collection
*/
public function getPossiblyRelatedJournals(Bill $bill) : Collection
{
$set = new Collection(DB::table('transactions')->where('amount', '>', 0)->where('amount', '>=', $bill->amount_min)->where('amount', '<=', $bill->amount_max)->get(['transaction_journal_id']));
$ids = $set->pluck('transaction_journal_id')->toArray();
$journals = new Collection();
if (count($ids) > 0) {
$journals = $this->user->transactionJournals()->transactionTypes([TransactionType::WITHDRAWAL])->whereIn('transaction_journals.id', $ids)->get(['transaction_journals.*']);
}
return $journals;
}
/**
* @param Collection $accounts
* @param array $types
* @param Carbon $start
* @param Carbon $end
*
* @return string
*/
private function sumInPeriodWithoutCategory(Collection $accounts, array $types, Carbon $start, Carbon $end) : string
{
$query = $this->user->transactionJournals()->distinct()->transactionTypes($types)->leftJoin('category_transaction_journal', 'category_transaction_journal.transaction_journal_id', '=', 'transaction_journals.id')->leftJoin('transactions as t', function (JoinClause $join) {
$join->on('t.transaction_journal_id', '=', 'transaction_journals.id')->where('amount', '<', 0);
})->leftJoin('category_transaction', 't.id', '=', 'category_transaction.transaction_id')->whereNull('category_transaction_journal.id')->whereNull('category_transaction.id')->before($end)->after($start);
if ($accounts->count() > 0) {
$accountIds = $accounts->pluck('id')->toArray();
$query->whereIn('t.account_id', $accountIds);
}
$sum = strval($query->sum('t.amount'));
return $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;
}
/**
* Returns a collection of ALL journals, given a specific account and a date range.
*
* @param Collection $accounts
* @param Carbon $start
* @param Carbon $end
*
* @return Collection
*/
public function getJournalsInRange(Collection $accounts, Carbon $start, Carbon $end) : Collection
{
$query = $this->user->transactionJournals()->expanded()->sortCorrectly();
$query->where('transaction_journals.completed', 1);
$query->before($end);
$query->after($start);
if ($accounts->count() > 0) {
$ids = $accounts->pluck('id')->toArray();
// join source and destination:
$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->where(function (Builder $q) use($ids) {
$q->whereIn('destination.account_id', $ids);
$q->orWhereIn('source.account_id', $ids);
});
}
$set = $query->get(TransactionJournal::queryFields());
return $set;
}
