/**
* @param $value
* @param $route
*
* @return mixed
*/
public static function routeBinder($value, $route) : Collection
{
if (auth()->check()) {
$ids = explode(',', $value);
/** @var \Illuminate\Support\Collection $object */
$object = TransactionJournal::whereIn('transaction_journals.id', $ids)->expanded()->where('transaction_journals.user_id', auth()->user()->id)->get(TransactionJournal::queryFields());
if ($object->count() > 0) {
return $object;
}
}
throw new NotFoundHttpException();
}
/**
* @param array $words
*
* @return Collection
*/
public function searchTransactions(array $words) : Collection
{
// decrypted transaction journals:
$decrypted = auth()->user()->transactionJournals()->expanded()->where('transaction_journals.encrypted', 0)->where(function (EloquentBuilder $q) use($words) {
foreach ($words as $word) {
$q->orWhere('transaction_journals.description', 'LIKE', '%' . e($word) . '%');
}
})->get(TransactionJournal::queryFields());
// encrypted
$all = auth()->user()->transactionJournals()->expanded()->where('transaction_journals.encrypted', 1)->get(TransactionJournal::queryFields());
$set = $all->filter(function (TransactionJournal $journal) use($words) {
foreach ($words as $word) {
$haystack = strtolower($journal->description);
$word = strtolower($word);
if (!(strpos($haystack, $word) === false)) {
return $journal;
}
}
return null;
});
$filtered = $set->merge($decrypted);
$filtered = $filtered->sortBy(function (TransactionJournal $journal) {
return intval($journal->date->format('U'));
});
$filtered = $filtered->reverse();
return $filtered;
}
/**
* @param Collection $accounts
* @param array $types
* @param Carbon $start
* @param Carbon $end
*
* @return Collection
*/
public function journalsInPeriod(Collection $accounts, array $types, Carbon $start, Carbon $end) : Collection
{
// first collect actual transaction journals (fairly easy)
$query = $this->user->transactionJournals()->expanded()->sortCorrectly();
if ($end >= $start) {
$query->before($end)->after($start);
}
if (count($types) > 0) {
$query->transactionTypes($types);
}
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);
});
$set = join(', ', $accountIds);
$query->whereRaw('(source.account_id in (' . $set . ') XOR destination.account_id in (' . $set . '))');
}
// that should do it:
$fields = TransactionJournal::queryFields();
$complete = $query->get($fields);
return $complete;
}
/**
* @param Collection $accounts
* @param Carbon $start
* @param Carbon $end
*
* @return Collection
*/
public function journalsInPeriodWithoutBudget(Collection $accounts, Carbon $start, Carbon $end) : Collection
{
$accountIds = [];
if ($accounts->count() > 0) {
$accountIds = $accounts->pluck('id')->toArray();
}
/** @var Collection $set */
$query = $this->user->transactionJournals()->expanded()->sortCorrectly()->transactionTypes([TransactionType::WITHDRAWAL])->leftJoin('budget_transaction_journal', 'budget_transaction_journal.transaction_journal_id', '=', 'transaction_journals.id')->whereNull('budget_transaction_journal.id')->leftJoin('transactions as source', function (JoinClause $join) {
$join->on('source.transaction_journal_id', '=', 'transaction_journals.id')->where('source.amount', '<', '0');
})->before($end)->after($start)->with(['transactions' => function (HasMany $query) {
$query->where('transactions.amount', '<', 0);
}, 'transactions.budgets']);
// add account id's, if relevant:
if (count($accountIds) > 0) {
$query->whereIn('source.account_id', $accountIds);
}
$set = $query->get(TransactionJournal::queryFields());
$set = $set->filter(function (TransactionJournal $journal) {
foreach ($journal->transactions as $t) {
if ($t->budgets->count() === 0) {
return true;
}
}
return false;
});
return $set;
}
/**
* 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;
}
/**
* This method also returns the amount of the journal in "journalAmount"
* for easy access.
*
* @param Bill $bill
*
* @param int $page
* @param int $pageSize
*
* @return LengthAwarePaginator|Collection
*/
public function getJournals(Bill $bill, int $page, int $pageSize = 50) : LengthAwarePaginator
{
$offset = ($page - 1) * $pageSize;
$query = $bill->transactionJournals()->expanded()->sortCorrectly();
$count = $query->count();
$set = $query->take($pageSize)->offset($offset)->get(TransactionJournal::queryFields());
$paginator = new LengthAwarePaginator($set, $count, $pageSize, $page);
return $paginator;
}
/**
* @param Tag $tag
*
* @return Collection
*/
public function getJournals(Tag $tag) : Collection
{
/** @var Collection $journals */
$journals = $tag->transactionJournals()->sortCorrectly()->expanded()->groupBy(['tag_transaction_journal.tag_id', 'tag_transaction_journal.transaction_journal_id'])->get(TransactionJournal::queryFields());
return $journals;
}
/**
* @param Collection $accounts
* @param array $types
* @param Carbon $start
* @param Carbon $end
*
* @return Collection
*/
public function journalsInPeriodWithoutCategory(Collection $accounts, array $types, Carbon $start, Carbon $end) : Collection
{
/** @var Collection $set */
$query = $this->user->transactionJournals();
if (count($types) > 0) {
$query->transactionTypes($types);
}
$query->leftJoin('category_transaction_journal', 'category_transaction_journal.transaction_journal_id', '=', 'transaction_journals.id')->whereNull('category_transaction_journal.id')->before($end)->after($start);
if ($accounts->count() > 0) {
$accountIds = $accounts->pluck('id')->toArray();
$query->leftJoin('transactions as t', 't.transaction_journal_id', '=', 'transaction_journals.id');
$query->whereIn('t.account_id', $accountIds);
}
$set = $query->get(['transaction_journals.*']);
if ($set->count() == 0) {
return new Collection();
}
// grab all the transactions from this set.
// take only the journals with transactions that all have no category.
// select transactions left join journals where id in this set
// and left join transaction-category where null category
$journalIds = $set->pluck('id')->toArray();
$secondQuery = $this->user->transactions()->leftJoin('category_transaction', 'category_transaction.transaction_id', '=', 'transactions.id')->whereNull('category_transaction.id')->whereIn('transaction_journals.id', $journalIds);
if ($accounts->count() > 0) {
$accountIds = $accounts->pluck('id')->toArray();
$secondQuery->whereIn('transactions.account_id', $accountIds);
}
// this second set REALLY doesn't have any categories.
$secondSet = $secondQuery->get(['transactions.transaction_journal_id']);
$allIds = $secondSet->pluck('transaction_journal_id')->toArray();
$return = $this->user->transactionJournals()->sortCorrectly()->expanded()->whereIn('transaction_journals.id', $allIds)->get(TransactionJournal::queryFields());
return $return;
}
