Generating reports basic

Need Help
1

I looked at the Documentation for CakePhp but I could not find documentation on how to generate a report let’s say from a mySQL database.

Is generating report available for CakePhp? If so, could anyone lead me to where I could start learning about it?

Thank you and GOD bless.

2

To generate a report in CakePHP, you add a report function to your controller, which uses the appropriate models to load all the data you need and send it to a view, which you write to output the data in the format you need. There is no higher-level functionality available for ad-hoc reporting.

3

I don’t have a cake example, this is a laravel example but you will get the idea.

You can use orm or normal sql and pdo choice is yours:

    public function veryTemp()
    {

        $page = Request::input('page', '1');
        $bdate = Request::input('begindate');
        $edate = Request::input('enddate');

        $perpage = "10";
        $offset = ($page - 1) * $perpage;

        if (!$page === '1') {
            $numrows = Session::get('numrows');
        } else {
            $krows = "SELECT COUNT(*) AS kount FROM ";
            $krows .= "(select distinct `account_types`.`AccountType` AS `AccountType`,`accounts`.`AccountNumber` ";
            $krows .= "AS `AccountNumber`,`accounts`.`AccountName` AS `AccountName`,sum(`transactions`.`Expense`) ";
            $krows .= "AS `Sum_Expense`,sum(`transactions`.`Income`) AS `Sum_Income` ";
            $krows .= "from ((`account_types` join `accounts` on((`account_types`.`AccountTypeID` = ";
            $krows .= "`accounts`.`AccountTypeID`))) join `transactions` on((`accounts`.`AccountID` = ";
            $krows .= "`transactions`.`AccountID`))) ";
            $krows .= "where (`transactions`.`TransactionDate` Between '$bdate' and  '$edate') ";
            $krows .= "group by `account_types`.`AccountType`,`accounts`.`AccountNumber`,`accounts`.`AccountName`) ";
            $krows .= "as t1";

            $sthk = DB::connection('mysqlcb')->getPdo()->prepare($krows);
            $sthk->execute();
            $quyk = $sthk->fetch(\PDO::FETCH_OBJ);
            $numrows = $quyk->kount;
            Session::put('numrows', $numrows);
        }

        $pagingQuery = "LIMIT {$offset}, {$perpage}";

        $sql = "select distinct `account_types`.`AccountType` AS `AccountType`,`accounts`.`AccountNumber` ";
        $sql .= "AS `AccountNumber`,`accounts`.`AccountName` AS `AccountName`,sum(`transactions`.`Expense`) ";
        $sql .= "AS `Sum_Expense`,sum(`transactions`.`Income`) AS `Sum_Income` ";
        $sql .= "from ((`account_types` join `accounts` on((`account_types`.`AccountTypeID` = ";
        $sql .= "`accounts`.`AccountTypeID`))) join `transactions` on((`accounts`.`AccountID` = ";
        $sql .= "`transactions`.`AccountID`))) ";
        $sql .= "where (`transactions`.`TransactionDate` Between :bdate and  :edate) ";
        $sql .= "group by `account_types`.`AccountType`,`accounts`.`AccountNumber`,`accounts`.`AccountName` ";
        $sql .= $pagingQuery;

        $sth = DB::connection('mysqlcb')->getPdo()->prepare($sql);
        $params = ['bdate' => $bdate, 'edate' => $edate];
        $sth->execute($params);
        $quy = $sth->fetchAll(\PDO::FETCH_OBJ);
        $report = new LengthAwarePaginator($quy, $numrows, $perpage);
        $pagelinks = ['bdate' => $bdate, 'edate' => $edate, 'page' => $page];

        $title = 'Monthly Report';
        return view('account.report', compact('report', 'pagelinks', 'title'));
    }

And notice you don’t need to count every time if you use session.

The key to a report

Is strategically looping over it to make it show the way you need, for example:

RlzeSEp.jpg1366×564

You have to put thought and logic while looping.

Of course convert to cakephp style code, and this is just an example to demo it does take some thought.

4

Zuhuru,

Thank you for the reply.

So, that would mean you are left in your own how to generate the report without any utilities/libraries that are specific to reporting from CakePhp.

Thanks once again and GOD bless.

5

Thank you for your reply.

Although this is not specific to CakePhp but again thank you for taking the time to answer.

GOD bless.

6

I have same success with https://www.tinybutstrong.com/opentbs.php and http://jasperstarter.cenote.de/.

7

utilities/libraries that are specific to reporting from CakePhp.

No, I mean you learn how to write the query in cakephp orm or in cakephp using pdo.

The trick to a report is also looping over the results to “display” the data the way you need it displayed.

A report is no different than any other sql. I was just showing, you still have to put the time in to learn the orm or sql to query the data. You are dealing with join, group by, order by and things like that in a report.

I have no idea what you need, but if it’s only a small report, say a few pages, you could even export to pdf, (again a learning curve here too). But you still have to have it properly formated (layout) to export it.

Doing things like this can take quite a while to learn, days, or even weeks.

And you have to remember, a complex query can take some trial and error to get right.