KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Codeigniter 4 CI4 integration with KoolReport #1881

Open Keith Burke opened this topic on on Jan 30, 2021 - 41 comments

Keith Burke commented on Jan 30, 2021

So I'm working on a new project in Codeigniter 4. Having never integrated KoolReport into it [or CI3] before, my first step was to search these forums, then KoolReport git, then the CodeIgniter forums. There was little information. Most information related to integrating with CI3 which is totally different as CI4 is not backword compatible. So after about 3 hours of frustrating work, I got it working. Hopefully this save someone some frustration.

I'll include snips from KoolReports integration steps from CI3 as well as some info found in these forums from [https://www.koolreport.com/forum/topics/1529](pargibay topic 1529). as well as a small bug [maybe] in the KoolReport Autoload.php.

Note : Composer was NOT used.

  1. I downloaded CI 4.04 and unzipped it so that I had a folder structure like :
    app
    public
    system
    writeable
    
  2. I downloaded KoolReport 5.0.1 free and unzipped it. I navigated to koolreport/core/src and copied the src folder to CI4 app/Libraries folder then renamed src to koolreport. My app/Libraries now looks like
    apps
     libraries
         koolreports
             clients
             core
             datastructures
             processes
             widgets
             KoolReport.php   <-- file
             debug.view.php   <-- file
    
  3. I downloaded the KoolReport codeigniter [1.8.0] plugin and unzipped it. I created a codeigniter folder in the koolreports folder above and placed all files in it. You really just need the two php files.
  4. This is where I used the info from [https://www.koolreport.com/forum/topics/1529](topic 1529). Note that the CI4 part of this just bridges to the default database. If you have more connections, just look at the CI3 part and modify the CI4 part accordingly. Full code is
<?php
namespace koolreport\codeigniter;

use \koolreport\core\Utility;

trait Friendship
{
    public function __constructFriendship()
    {

        //assets folder
        $assets = Utility::get($this->reportSettings, "assets");
        if ($assets == null) {
            $document_root = Utility::getDocumentRoot();
            $script_folder = str_replace("\\", "/", realpath(dirname($_SERVER["SCRIPT_FILENAME"])));
            $asset_path = $script_folder . "/assets";
            $asset_url = Utility::strReplaceFirst($document_root, "", $script_folder) . "/assets";
            if (!is_dir($asset_path . "/koolreport_assets")) {
                if (!is_dir($asset_path)) {
                    mkdir($asset_path, 0755);
                }
                mkdir($asset_path . "/koolreport_assets", 0755);
            }

            $assets = array(
                "url" => $asset_url . "/koolreport_assets",
                "path" => $asset_path . "/koolreport_assets",
            );
            $this->reportSettings["assets"] = $assets;
        }
        
      //If codeigniter 4 then load default database <====
        if(file_exists(APPPATH . 'Config/App.php')){
                $db = \Config\Database::connect();
                $dataSources = array(
                   "default"=>array(
                       "connectionString"=>"mysql:host=$db->hostname;dbname=$db->database",
                       "username"=>$db->username,
                       "password"=>$db->password,
                        "charset"=>$db->charset
                      ),
                  );
                $this->reportSettings["dataSources"] = $dataSources; 
        }
        return;        
////////////////////////////////////end CI4\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
        
      //If codeigniter 3 then load databases <====
        
        if (!file_exists($file_path = APPPATH . 'config/' . ENVIRONMENT . '/database.php')
            && !file_exists($file_path = APPPATH . 'config/database.php')) {
            return;
        }

        include $file_path;
        $dbSources = array();
        foreach ($db as $name => $dbconfig) {
            $dbSources[$name] = array(
                "class" => CIDataSource::class,
                "name" => $name,
            );
        }
        $dataSources = Utility::get($this->reportSettings, "dataSources", array());
        $this->reportSettings["dataSources"] = array_merge($dbSources, $dataSources);
////////////////////////////////////end CI3\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

    }
}
  1. I had forgotten about the KoolReport autoload.php so copied it from the core folder of the zip file and placed it in app/Libraries/koolreport. The same folder that contains KoolReport.php. This one took a while to get working as there is an assumption in the autoload.php. Maybe it's because I didn't use Composer. I had to remove the packages subfolder reference in line 39 as it doesn't exist. This is when stuff really started to work.
    $filePath = $dir."/".str_replace("koolreport/", "/", $classname).".php";
    
  2. Now that we are fully configured, we have to set up one test report. For good segregation, create an app/Reports. This will be where you place all of your reports. Create a test report and view here. Don't be tempted to use your Views folder just yet.

testReport.php

<?php
namespace App\Reports;

require APPPATH."Libraries/koolreport/autoload.php";

class TestReport extends \koolreport\KoolReport
{
    use \koolreport\codeigniter\Friendship;
    function setup()
    {
        $this->src("default")
        ->query("SELECT * FROM users WHERE id in (14)")
        ->pipe($this->dataStore("users"));          
    }
}

testReport.view.php

<?php
    use \koolreport\widgets\koolphp\Table;

        Table::create(array(
            "dataStore"=>$this->dataStore("users"),
            "class"=>array(
                "table"=>"table table-hover"
            )
        ));
?>

Create a Reports Controller in app/Controllers. Note that we don't run $report->run()->render(); just $report->run();. We will render it in the CI4 view.

<?php namespace App\Controllers;

use CodeIgniter\Controller;

require APPPATH."Reports/TestReport.php";

class Reports extends BaseController
{
    function __construct()
    {
        $this->session = session();
    }
    
    public function index()
    {
    }
    
    public function testReport(){
        $report = new \App\Reports\TestReport();
        $report->run();
        $data['report'] = $report;
        
        echo view('page_shell/header');    
        echo view('page_shell/profile');
        echo view('reports/showReport',$data);
        echo view('page_shell/footer');
    }
}

Create a showReport view in app/Views. It will just include the below code. This allows you to frame your Kool Report within the framework of your CI4 site. You only need this one view so it can be reused through all of your reports in app/Reports.

<?php $report->render(); ?>

That's it, you're done, just call your controller/method [Reports/testReport]. Enjoy. thanks to the CI and KR team for two fantastic projects.

Keith Burke commented on Jan 30, 2021

So the numbering is messed up. Just pretend that there are no number 1's and 2's :)

pargibay commented on Jan 31, 2021

Hi Keith,

Great job. It must be included in a new version of codeigniter plugin.

Finally, I install koolreport with composer and I took the following solution, in the report. Its simple and I can use composer update without risk.

<?php

// Require autoload.php from koolreport library
require APPPATH."../vendor/autoload.php";

//Define the class
class Frozen extends \koolreport\KoolReport
{
    use \koolreport\inputs\Bindable;
    use \koolreport\inputs\POSTBinding;
    
    public function settings()
    {
        $db=config('Database')->default;
        return [
            "dataSources"=>[
                "default"=>[
                    'host' => $db['hostname'],
                    'username' => $db['username'],
                    'password' => $db['password'],
                    'dbname' => $db['database'],
                    'charset' => 'utf8',  
                    'class' => "\koolreport\datasources\MySQLDataSource"  
                ],
            ]
        ];
    }
...

Keith Burke commented on Feb 1, 2021

Nice. I'll give that a try.

And no, they didn't patch the plugin. I had to add your code to access default database. But hey, once it works.

I just tried it with KoolReport Pro. The only changes that I had to make from above is that the codeigniter plugin, KoolRereport.php and Autoload.php is in app/Libaries/koolreport/core/src and then change the testReport to include the correct autoload.php.

KoolReport commented on Feb 3, 2021

Thank you @Keith for sharing, it is very beneficial with real experience.

Murugappan Ramanathan commented on Nov 17, 2021

@keith ,

I was excited with the new release and especially the dashboard feature. Great job. I tried the code you provided above but it failed. I am using CI 4.1.5. Its giving me the following error.

The directory structure is:

I am trying my best to use KoolReport for the company but each time i try the sample given (like the one here) it does not work. Then i just throw on the "shelf" and move on. Appreciate if you could post the autoload.php file as well.

Appreciate all the help to make a good decision. Disappointing to note that forum does not allow/support file upload. I could zip and upload the instance.

Sebastian Morales commented on Nov 18, 2021

Murugappan, if you install KoolReport by downloading our zip file pls use this require path instead:

require $pathTo . "/koolreport/core/autoload.php"; // use koolreport/core/autoload.php instead of koolreport/autoload.php

Beside using our forum, you can always send mail and attach files to our support emails: support@koolphp.net and support@koolreport.com. Let us know if you have any question. Tks,

Murugappan Ramanathan commented on Nov 18, 2021

Hi Sebastian,

Thank you for your assistance. I tried the change and it still did not work. I have sent an email to support@koolreport.com attaching the instance for your review. Appreciate all the help.

Thank you

Murugappan Ramanathan commented on Nov 18, 2021

Hi Sebastian,

After some ding-dong emails with support. I got Keith Burke's solution working. This is what i did:

(1) Downloaded the koolreport manually and unzipped the contents into a "temp" folder in my C: drive (2) Copied the entire koolreport folder, as is, into the CI4->Library folder (3) Downloaded the Codeigniter plugin and unzipped in c:\temp folder (4) Modified the Friendship.php (changed part in bold) as below:

<?php

namespace koolreport\codeigniter;

use \koolreport\core\Utility;

trait Friendship
{
    public function __constructFriendship()
    {

        //assets folder
        $assets = Utility::get($this->reportSettings, "assets");
        if ($assets == null) {
            $document_root = Utility::getDocumentRoot();
            $script_folder = str_replace("\\", "/", realpath(dirname($_SERVER["SCRIPT_FILENAME"])));
            $asset_path = $script_folder . "/assets";
            $asset_url = Utility::strReplaceFirst($document_root, "", $script_folder) . "/assets";
            if (!is_dir($asset_path . "/koolreport_assets")) {
                if (!is_dir($asset_path)) {
                    mkdir($asset_path, 0755);
                }
                mkdir($asset_path . "/koolreport_assets", 0755);
            }

            $assets = array(
                "url" => $asset_url . "/koolreport_assets",
                "path" => $asset_path . "/koolreport_assets",
            );
            $this->reportSettings["assets"] = $assets;
        }

       __ if (file_exists(APPPATH . 'Config/App.php')) {
            $db = \Config\Database::connect();
            $dataSources = array(
                "default" => array(
                    "connectionString" => "mysql:host=$db->hostname;dbname=$db->database",
                    "username" => $db->username,
                    "password" => $db->password,
                    "charset" => $db->charset,
                ),
            );
            $this->reportSettings["dataSources"] = $dataSources;
        }
        return;

    }
}__

(5) Copied the entire Codeigniter folder from the unzipped plugin folder into CI4->Libraries->koolreport folder

(6) The sample reports files testReport.php, testReport_view.php (in Reports folder) and showReport.php (in C4->views folder) files exactly as stated by Keith (7) Modified Keith's Reports controller and removed all the "page_shell" views (i do not have them):

<?php namespace App\Controllers;

require APPPATH . "Reports/TestReport.php";

class Reports extends BaseController
{
    public function __construct()
    {
        $this->session = session();
    }

    public function index()
    {
    }

    public function testReport()
    {
        $report = new \App\Reports\TestReport();
        $report->run();
        $data['report'] = $report;

        __// echo view('page_shell/header');
        // echo view('page_shell/profile');
        echo view('showReport', $data);
        // echo view('page_shell/footer');__
    }
}

Finally, it worked with the output like this

Great guys, now i can get dowm to analyze and test further. Thank you so much.

One small request to @pargibay, please put the full solution. Its great to create a better solution but its useless if you put in morse code. Hehehe...

pargibay commented on Nov 19, 2021

Hi Murugappan,

the second way is smarter.

You can access to the database, keeping koolreport (to update without risk).

You just have to use: $db=config('Database')->default; to get parameters to access database (user, password,...) and you complete config function settings (as below)

By this way you don't need codeigniter friendship

Regards

<?php

// Require autoload.php from koolreport library
require APPPATH."../vendor/autoload.php";

//Define the class
class Report extends \koolreport\KoolReport
{
    use \koolreport\inputs\Bindable;
    use \koolreport\inputs\POSTBinding;
    
    public function settings()
    {
        $db=config('Database')->default; // <= get codeigniter default database config
        return [
            "dataSources"=>[
                "default"=>[
                    'host' => $db['hostname'],           // <= hostname
                    'username' => $db['username'],    // <= username
                    'password' => $db['password'],     // <= password
                    'dbname' => $db['database'],       // <= database is default
                    'charset' => 'utf8',  
                    'class' => "\koolreport\datasources\MySQLDataSource"  
                ],
            ]
        ];
    }
....................
Murugappan Ramanathan commented on Nov 20, 2021

Hi @pargibay

I could not figure out your solution. But i managed to create the solution i (clone from Keith) gave earlier in my post installing the KoolReport with composer and adding the Codeigniter plugin into the vendor->koolreport folder. Also change the require in "testReport.php" to

require APPPATH . "../vendor/autoload.php";

Like said before, please put the full solution. Its great to create a better solution but its useless if you put in morse code. There are others who may have struggled, lost and abandoned KoolReport altogether..

Keith Burke commented on Nov 20, 2021

@pargibay, ok, fantastic solution for not using Friendship. I'll try it out soon.

@Murugappan, Sorry, I didn't have much time to look into your problem, life has me very busy at the moment. I've now migrated to using Composer for both KoolReport and CI4 in all of my future projects but I've not touched my project that uses both of them yet. I'd like to revisit it and implement the friendless DB connection but I'm not sure I will have time this year. If I get it don't, I'll post another topic with as much detail as possible.

Murugappan Ramanathan commented on Nov 20, 2021

@Keith Burke,

Thank you so much. I have done one with composer using your earlier code as a base but was not complete as i had problem getting some of the other views in the Report.php controller were not available. I could send you a zipped file of what i have done and you could use that a base. The one i did was using composer all the way. If i could have your email i could send it to you.

Keith Burke commented on Nov 20, 2021

@Murugappan I'd rather stay private, thanks anyway :)

Murugappan Ramanathan commented on Nov 24, 2021

Hi @Keith Burke

Any update on the project? Looking forward to it. In the meantime, I am learning all about Koolreport using the tutorials emailed to me.

Keith Burke commented on Nov 25, 2021

@Murugappan. Not yet. I won't be getting back to it until, maybe, February. Much too busy with other stuff.

Murugappan Ramanathan commented on Nov 25, 2021

@Keith Burke ,

I managed to get it working using the sample report provided in Koolreport Tutorial 1. The app does not use the codeigniter 1.8.0 at the moment.

AhmedHaroon commented on Jan 6, 2023

hi all, a newbie is here, need your help. i knew it is now 2023 and last post was made in Nov 2021. may be some things modified / upgraded. i realized all seniors are busy people and spare their valuable time to guide us to teach us. i am thankful for their efforts and interest to "actually" help us.

i am requesting to please create steps from scratch with above mentioned problems and/or with new changes (if any) like @Murugappan Ramanathan mentioned here " I managed to get it working using the sample report provided in Koolreport Tutorial 1. The app does not use the codeigniter 1.8.0 at the moment.". it means NOW that plugin is not required to install? if it is not require anymore, so the steps should be without it.

regards.

Keith Burke commented on Jan 6, 2023

So, you have it working? What do you need?

AhmedHaroon commented on Jan 6, 2023

@Keith Burke, thanks ... it is not working, may be due to my lack of knowledge, follow your OP (with plugin) but failed ( undefined variable PathTo in APPPATH\Reports\TestReport.php at line 5). please help me for success. regards

Murugappan Ramanathan commented on Jan 6, 2023

Hi All,

I stopped working on Koolreport as it was getting a bit complicated for my needs, especially for Codeigniter 4 (documentation not helping). I was looking for something like Reportico from Reporticp.org. Reportico provided a full UI interface in Joomla CMS, and non-Joomla environment, for developing all reports and works wonderfully. Unfortunately, Peter Reed, the originator, decided to stop work on the product and the current version does not work with PHP 8.x. Seems to be refocusing elsewhere.

With Koolreport, I found it has too much code. The interesting part is the new dashboard feature/component. I am yet to try this as this may provide the option to embed the dashboard into Joomla. However, the excessive code to develop the report is still required.

Keith Burke commented on Jan 6, 2023

Ok,

Installing both with composer. I got it working and uploaded it to github. Clone my repo and read the README. That should get you working with the latest version of CI and KR.

https://github.com/FLYEfit-Keith/ci4-koolreports-integration.

Any problems, post here.

AhmedHaroon commented on Jan 6, 2023

@Keith Burke... extremely helpful !!! i will see it and wish i have a fully working Koolreport. for my knowledge, please guide when i am using .env file for database connection, all the things work as is? may be the stupid question but i don't know anything how it works, what are the requirements? etc. etc. hope i will become a Koolreport developer too with CI4. regards

Keith Burke commented on Jan 6, 2023

Ahmed, no problem.

Firstly, are you familiar with developing with CI4? The .env file allows you to put all/most of your CI4 config entries in there. The idea being, you don't normally include your .env file in your git commits, therefore not divulging any secret passwords etc.

So, in your .env file, edit the following lines so that your database parameters are correct, host, database, user and password etc database.default.hostname = localhost database.default.database = ci4 database.default.username = root database.default.password = database.default.DBDriver = MySQLi database.default.DBPrefix = database.default.port = 3306 Doing it this way means that you don't have to put these entries into app/config/database.php as this file will be committed to git, therefore hiding your database credentials.

CodeIgniter environment variables : https://codeigniter.com/user_guide/general/configuration.html?highlight=env

AhmedHaroon commented on Jan 6, 2023

@Keith Burke

please check the below steps and advise, is there anything i have done wrong or missing or misunderstood.

cloned your repo from github (https://github.com/FLYEfit-Keith/ci4-koolreports-integration), then in the root folder modified composer.json and remove below line as i am currently using free / community ver.

"koolreport/pro":"*"

then modified .env file as instructed

then in cmd of the root folder:

1) composer require koolreport/core

2) composer require koolreport/codeigniter

Friendship.php is already in your given path: /vendor/koolreport/codeigniter/Friendship.php , may i have to update it with your code here in OP?

first tried to check i run in cmd window of project root : php spark serve

it is throwing error: Could not open input file: spark

in other apps folder it is executing and working fine.

please help.

Keith Burke commented on Jan 6, 2023

spark is used by CI4. I've never used it so can't comment.

You need to OVERWRITE /vendor/koolreport/codeigniter/Friendship.php with my one from the repo root. The one that comes with KR only works with CI3. My one references CI4 in the comments of that file.

Make that one change and test again by running it in Apache from a browser. XAMPP is your friend.

AhmedHaroon commented on Jan 6, 2023

@Keith Burke thanks and appreciate your effort to help me.

i overwrite Friendship.php and tried to run with Apache but it is showing error:

Class 'koolreport\datagrid\DataTables' not found

and showing below:

APPPATH\Reports\TestReport\TestReport.view.php at line 4

i checked 'koolreport\datagrid\DataTables' it is not exists. how i can include it?

regards.

Keith Burke commented on Jan 6, 2023

So DataTables is a feature of KoolReports Pro. You need to use Tables for the free/Core version.

This is good news. At least the site is working, now you just need to do get your first report running. My suggestions are:

  1. Edit app/Reports/TestReport/TestReport.php. Change the query string to be something that your database will return. You only need a small table, 3 or 4 records, no more. Keep it simple.
  2. Again, make sure your database configuration in the .env file is correct. Server, Database, User, Password
  3. Change TestReport.view.php from
<?php
    use \koolreport\datagrid\DataTables;
 
    DataTables::create(array(
        "dataStore"=>$this->dataStore('testReport')
        ,"cssClass"=>array(
            "table"=>"table table-hover table-bordered",
            "th"=>"cssHeader",
            "tr"=>"cssItem"
            )
        ,"options"=>array(
                    "paging"=>true,
                    "searching"=>true,
                    "colReorder"=>true,
                    "fixedHeader"=>true,
                    "select"=>true,
                    "info"=>false,
            )
    ));
?>

To [Table widget is included with KoolReport Free/Core]

<?php
    use \koolreport\widgets\koolphp\Table;
 
    Table::create(array(
        "dataStore"=>$this->dataStore('testReport')
    ));
?>
AhmedHaroon commented on Jan 6, 2023

@Keith Burke

great help man !!! at last i run the very first KoolReport integrated with CI4 with your help/guidance.

really grateful to you.

kind regards.

note: will come again when i will try another level reports to get help from you when require.

Keith Burke commented on Jan 6, 2023

Fantastic, You have it working. The ONLY thing you need to do now is add new KR reports to the Reports folder. Everything else is just vanilla CI4.

AhmedHaroon commented on Jan 9, 2023

@Keith Burke

We have KoolReport Pro 6.0.6, downloaded .zip file has been given to me now to integrate and create the reports with datatable etc. ( previously i used composer to install free version and now it is .zip file and pro version )

we have to integrate in an existing CI 4 app.

in .zip file there are 2 folders : (1) examples (2) koolreports

how i can integrate it as i am confused with paths which should have proper place to access and work normally.

is there i need to create a new topic for this question ?

regards

Keith Burke commented on Jan 9, 2023

Ahmed,

It's a little bit more difficult not to use composer but all details are above in my original post. If possible, get your composer auth token from your license page.

AhmedHaroon commented on Jan 9, 2023

thanks @Keith Burke

i have managed to get working sample report. it is showing data in DataTable now, but i can't get the data in Table widget, it is showing a simple page with data.

how i can use Table instead of DataTable? as showing in example report Sales by Country. below code is from sample file ( SalesByCountry.view.php ) which is using Table. i copied it in my TestReport.view.php file.

<?php
use \koolreport\widgets\koolphp\Table;
use \koolreport\widgets\google\GeoChart;
?>

regards

Keith Burke commented on Jan 9, 2023

Can you post a screenshot?

AhmedHaroon commented on Jan 9, 2023

@Keith Burke

i am trying to explore examples, will follow those and if have any problem i will contact you.

thanks for your passions.

regards

Keith Burke commented on Jan 9, 2023

And this screenshot is just showing the data, not the table layout? I've seen this before and it means something.I'll have to dig into my notes but it's a fallback that KoolReports uses when it can't find something.

Keith Burke commented on Jan 9, 2023

Can you post your TestReport.php and TestReport.view.php, please? I can't find the problem in the forum ut I know I reported it at least once.

AhmedHaroon commented on Jan 9, 2023

yeah sure, it is here... i modified to use Table widget.

TestReport.php

<?php
namespace App\Reports;

require APPPATH."Libraries/koolreport/autoload.php";

class TestReport extends \koolreport\KoolReport
{
    use \koolreport\codeigniter\Friendship;
    function setup()
    {
        $this->src("default")
        ->query("SELECT * FROM zones")
        ->pipe($this->dataStore("zones"));          
    }
}

TestReport.view.php

<?php
use \koolreport\widgets\koolphp\Table;
?>
<div class='report-content'>
    <div class="text-center">
        <h1>Zones List</h1>
        <p class="lead">The report show Zones</p>
    </div>

    <?php
    Table::create(array(
        "dataStore"=>$this->dataStore("zones")->sort(array("id"=>"desc")),
        "columns"=>array(
            "id"=>array(
                "label"=>"Zone ID",
                "type"=>"number",
                "prefix"=>"$",
            ),
            "zone_name"=>array(
                "label"=>"Zone Name"
            ),
        ),
        "paging"=>array(
            "pageSize"=>10,
        ),
        "cssClass"=>array(
            "table"=>"table table-bordered table-striped"
        )
    ));
    ?>
</div>

used prefix in id column intentionally just to check it.

regards

Keith Burke commented on Jan 9, 2023

Your TestReport.php is wrong. This appears to be another view creating a Table. This file should be your SQL stuff.

AhmedHaroon commented on Jan 9, 2023

@Keith Burke sorry for inconvenience, i updated my code of TestReport.php above.

Keith Burke commented on Jan 9, 2023

Weird, everything looks fine. So, it works with DataTable but not Table? I would suggest you open a new ticket on the forum. Maybe KoolReport staff can help.

Not using Composer may introduce issues that are very hard to track down. Can you try with Composer first [you will need your auth token from your license section]? That should work immediately.

AhmedHaroon commented on Jan 9, 2023

@Keith Burke

yes, it works with DataTable.

i will create new thread / new ticket on forum.

i will ask to use Composer and can use if management permit.

thank you for trying hard to help us.

regards

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
wiki

CodeIgniter