/**
* @param Carbon $start
* @param Carbon $end
*
* @return Builder
*/
protected function queryJournalsWithTransactions(Carbon $start, Carbon $end)
{
$query = TransactionJournal::leftJoin('transactions as t_from', function (JoinClause $join) {
$join->on('t_from.transaction_journal_id', '=', 'transaction_journals.id')->where('t_from.amount', '<', 0);
})->leftJoin('accounts as ac_from', 't_from.account_id', '=', 'ac_from.id')->leftJoin('account_meta as acm_from', function (JoinClause $join) {
$join->on('ac_from.id', '=', 'acm_from.account_id')->where('acm_from.name', '=', 'accountRole');
})->leftJoin('transactions as t_to', function (JoinClause $join) {
$join->on('t_to.transaction_journal_id', '=', 'transaction_journals.id')->where('t_to.amount', '>', 0);
})->leftJoin('accounts as ac_to', 't_to.account_id', '=', 'ac_to.id')->leftJoin('account_meta as acm_to', function (JoinClause $join) {
$join->on('ac_to.id', '=', 'acm_to.account_id')->where('acm_to.name', '=', 'accountRole');
})->leftJoin('transaction_types', 'transaction_types.id', '=', 'transaction_journals.transaction_type_id');
$query->before($end)->after($start)->where('transaction_journals.user_id', Auth::user()->id);
return $query;
}
/**
* Returns the date of the very first transaction in this account.
*
* @param Account $account
*
* @return Carbon
*/
public function oldestJournalDate(Account $account) : Carbon
{
/** @var TransactionJournal $journal */
$journal = TransactionJournal::leftJoin('transactions', 'transactions.transaction_journal_id', '=', 'transaction_journals.id')->where('transactions.account_id', $account->id)->orderBy('transaction_journals.date', 'ASC')->orderBy('transaction_journals.order', 'DESC')->orderBy('transaction_journals.id', 'ÅSC')->first(['transaction_journals.*']);
if (is_null($journal)) {
return new Carbon('1900-01-01');
}
return $journal->date;
}
/**
*
*/
private function reportNoTransactions()
{
$set = TransactionJournal::leftJoin('transactions', 'transactions.transaction_journal_id', '=', 'transaction_journals.id')->groupBy('transaction_journals.id')->whereNull('transactions.transaction_journal_id')->get(['transaction_journals.id']);
foreach ($set as $entry) {
$this->error('Error: Journal #' . $entry->id . ' has zero transactions. Open table `transaction_journals` and delete the entry with id #' . $entry->id);
}
}
