Need help to create Reports

Need Help
2

I’ve answered this type question so often in laravel I really don’t feel up to writing all cake code here, so convert the answer to cake orn or query builder.

Here are 2 answers: First you could just use cake pdo instance:

A join and group by:

Looks like this when looped over:

RlzeSEp.jpg1366×564 64.6 KB

Code:

public function monthlyReport()
    {
        $bdate = Request::input('begindate');
        $edate = Request::input('enddate');

        $sql = "select distinct `account_types`.`AccountType` AS `AccountType`,`accounts`.`AccountNumber` AS `AccountNumber`,`accounts`.`AccountName` AS `AccountName`,sum(`transactions`.`Expense`) AS `Sum_Expense`,sum(`transactions`.`Income`) AS `Sum_Income` from ((`account_types` join `accounts` on((`account_types`.`AccountTypeID` = `accounts`.`AccountTypeID`))) join `transactions` on((`accounts`.`AccountID` = `transactions`.`AccountID`))) where (`transactions`.`TransactionDate` Between '$bdate' and  '$edate') group by `account_types`.`AccountType`,`accounts`.`AccountNumber`,`accounts`.`AccountName`";
        
        $sth = DB::getPdo()->prepare($sql);
        $sth->execute();
        $quy = $sth->fetchAll(\PDO::FETCH_OBJ);
        $title = 'Monthly Report';
        return view('account.report', compact('quy', 'title'));
    }

Some logic is required during the loop. And this was a rough draft, not final version.

and orm something like:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1

I use cakephp, laravel, and yii2, just change example to cake style, and realize many complex queries require some trial and error to work out.

If you don’t know how to upload an image, learn that first, and if you get stuck ask a new question.

Note for reports I usually use the pdo instance, but use proper bindings.

I gave pdo instance example here:

There is also GitHub - PHPJasper/phpjasper: A PHP report generator

And here another link

https://laracasts.com/discuss/channels/laravel/complex-query-for-a-report

But with a little tweaking cake laravel or yii2 are all three similar.