Reports Semplification Proposal

[arsonus]

Great job man.

I do really like what you have done, i was thinking, wouldn't it's usage benefit from using the full power of Laravel Eloquent?

I see you are doing loops through all accounts for calculations, Please review below and let me know your thoughts, haven't tried it, just an idea that came in mind to easily query reports with any combinations.

use IFRS\Models\Account;
use IFRS\Models\Balance;
use IFRS\Models\Ledger;
use Illuminate\Database\Query\Expression;
use Illuminate\Database\Eloquent\Builder;
use Carbon\Carbon;

    /**
    * Account::scopeClosingBalance()
    *
    * @since        0.0.1
    * @access       public
    * @author       Arsonus Digital
    */      
    public function scopeClosingBalance(Builder $builder, $startAt, $endAt = null) 
{

        $this->scopeCurrentBalance($builder, $startAt, $endAt);
        $this->scopeOpeningBalance($builder, $startAt);

        return $builder->selectRaw("opening.balance + current.balance as balance");
    }

    /**
    * Account::scopeCurrentBalance()
    *
    * @since        0.0.1
    * @access       public
    * @author       Arsonus Digital
    */      
    public function scopeCurrentBalance(Builder $builder, $startAt, $endAt = null) 
{

        $ledger = (new Ledger);
        $debitQuery = $ledger->newQuery();
        $creditQuery = $ledger->newQuery();
        $startAt = new Carbon($startAt);
        $endAt = new Carbon($endAt);

        $creditQuery->selectRaw("SUM(lc.amount) AS amount")
            ->from($ledger->getTable(), "lc")
            ->where("lc.entry_type", new Expression("'". Balance::CREDIT ."'"))
            ->whereColumn("lc.post_account", "{$builder->getModel()->getQualifiedKeyName()}")
            ->whereBetween("lc.posting_date", [$startAt->format('Y-m-d H:i:s'), $endAt->format('Y-m-d H:i:s')]);

        $debitQuery->selectRaw("SUM(ld.amount) - SUM(lcr.amount) *-1 AS balance")
            ->from($ledger->getTable(), "ld")
            ->where("ld.entry_type", new Expression("'". Balance::DEBIT ."'"))
            ->whereColumn("ld.post_account", "{$builder->getModel()->getQualifiedKeyName()}")
            ->whereBetween("ld.posting_date", [$startAt->format('Y-m-d H:i:s'), $endAt->format('Y-m-d H:i:s')])
            ->selectSub(clone $creditQuery, "lcr");

        return $builder->selectSub(clone $debitQuery, "current");
    }   

    /**
    * Account::scopeOpeningBalance()
    *
    * @since        0.0.1
    * @access       public
    * @author       Arsonus Digital
    */      
    public function scopeOpeningBalance(Builder $builder, $startAt = null) 
{

        $balance = (new Balance);
        $debitQuery = $balance->newQuery();
        $creditQuery = $balance->newQuery();
        $year = (new Carbon($startAt))->year;

        $creditQuery->selectRaw("SUM(bc.amount) AS amount")
            ->selectRaw("bc.currency_rate AS rate")
            ->from($balance->getTable(), "bc")
            ->leftJoin("reporting_periods as cp", "cp.year", "=", new Expression("'$year'"))
            ->where("bc.balance_type", new Expression("'". Balance::CREDIT ."'"))
            ->whereColumn("bc.account_id", "{$builder->getModel()->getQualifiedKeyName()}")
            ->whereColumn("bc.reporting_period_id", "cp.id");

// Might need to join exchange_rates table
        $debitQuery->selectRaw("(SUM(bd.amount) / bd.currency_rate) - (SUM(bcr.amount) / bcr.rate) *-1 AS balance")
            ->from($balance->getTable(), "bd")
            ->leftJoin("reporting_periods as dp", "dp.year", "=", new Expression("'$year'"))
            ->where("bd.balance_type", new Expression("'". Balance::DEBIT ."'"))
            ->whereColumn("bd.account_id", "{$builder->getModel()->getQualifiedKeyName()}")
            ->whereColumn("bd.reporting_period_id", "dp.id")
            ->selectSub(clone $creditQuery, "bcr");

        return $builder->selectSub(clone $debitQuery, "opening");
    }

By making use of scopes, one can then for example query Gross Profit:

$grossProfit = Account::closingBalance($startAt, $endAt)
->whereIn('account_type', ['OPERATING_REVENUES', 'OPERATING_EXPENSES'])
// Even more flexibility
//->where('accounts.entity_id', 10)
->get('balance', 'accounts.*');

[arsonus]

Update scopeClosingBalance to mimic your calculations in Account::sectionBalances()

    /**
    * Account::scopeClosingBalance()
    *
    * @since        0.0.1
    * @access       public
    * @author       Arsonus Digital
    */      
    public function scopeClosingBalance(Builder $builder, $startAt, $endAt = null) 
{
        return $builder->selectRaw("SUM(opening.amount) + SUM(current.amount) + SUM(movement.amount) as amount")
            ->openingBalance(ReportingPeriod::year($endAt))
            ->currentBalance(ReportingPeriod::periodStart($endAt), $startAt)
            ->selectSub(static::currentBalance($startAt, $endAt)->selectRaw("SUM(current.amount) as amount"), "movement");
    }

[ekmungai]

Hi arsonus,

Thanks a lot for your suggestion. I've been thinking about shifting the responsibility for aggregation of values for the reports from php (loops) to the underlying db (raw sql) I just happen to have a weakness in the latter. I'll definitely be looking into your code above to see if I can incorporate it into the next release. The performance gains should be impressive!

Thanks again, Cheers

[arsonus]

My pleasure ekmungai,

i went further during these holidays and was able to get many of the aggregations to work by using Common Table Expressions (CTE). I will share a Gist with you once i get back to it, all the changes basically happened in the Account class so it wouldn't be difficult incorporating.

Happy New Year man, Cheers

[ekmungai]

Hi arsonus,

Great to hear. I'll be looking forward to seeing the gist.

Happy 2021!

Cheers