In this tutorial, we will show how to convert your SQL queries to reports! So, for the sake of this example, let’s assume that the SQL query we want to use for this report is the following:

                      select * from items where `Units_In_Stock` > 15

The above SQL query should return all records from an “items” table, where the value of the “units_in_stock” column is less than 15. We want to create a professional report based on this SQL query.

We will start by showing the code in a native PHP project and then in a Laravel project.

1- Case of a native PHP Project

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
In a native PHP Project, the code will be something like the following:
use SRE\Engine\CustomEngine;
use SRE\Engine\ReportOptions;
 
require_once "../sre_bootstrap.php";
$report = new ReportOptions(SRE_PUBLIC_REPORT, SRE_SQL,"SQL_01");
$report->set_sql("select * from items where `Units_In_Stock` < 15 ")
       ->set_header("Header goes here")
       ->set_footer("Footer goes here")
       ->set_grouping(array("country"))
       ->set_layout("block")
       ->sort_by("id")
       ->label("id", "Product ID")
       ->select_all_fields();
$engine = new CustomEngine($report);
$report_path = $engine->create_report();
if ($report_path) {
    echo "Report created successfully!To visit your report please <a href = '" . $report_path . "' /> click here </a> ";
}

2- Case of Laravel Project

We will put the report generation logic in a service. It will be something like the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
namespace App\Services;
 
use Sre\SmartReportingEngine\src\Engine\Constants;
use Sre\SmartReportingEngine\src\Engine\CustomEngine;
use Sre\SmartReportingEngine\src\Engine\ReportOptions;
 
class ReportService {
 
    public function generatePublicReport() {
       $report = new ReportOptions(Constants::SRE_PUBLIC_REPORT, Constants::SRE_SQL,"SQL_01");
       $report->set_sql("select * from items where `Units_In_Stock` < 15 ")
              ->set_header("Header goes here")
              ->set_footer("Footer goes here")
              ->set_grouping(array("country"))
              ->set_layout("block")
              ->sort_by("id")
              ->label("id", "Product ID")
              ->select_all_fields();
       $engine = new CustomEngine($report);
       $report_path = $engine->create_report();
       return $report_path;
    }
}

The difference between using a smart report engine in a native PHP or in a Laravel project is in the package used, paths, and autoloading.

Notes

  • If you are not using the composer for autoloading, you will have to require the “sre_bootstrap.php” file (You can find this file in Smart Report Engine downloaded package at  “/Native/sre_bootsrap.php” ).
  • We are assuming that “sre_bootstrap.php” is on a directory one level above the current directory.

Report Options

In the above code, we first set the options we want for our report using the “$report” object, which is an instance of the ReportOptions Class. Following are the report options defined in the example:

Setting the Report as a “Public_Report”

A public report can be accessed freely without any integration with a login system. To set this report as public, we pass the “SRE_PUBLIC_REPORT”  flag to the constructor of the “ReportOptions” class. Please note that all flags used in Smart Report Engine start with “SRE_”

This report should be based on a SQL query

Since we are showing how to convert queries to reports in this example, we should set “SQL Query” as the data source of this report. Therefore, we pass the “SRE_SQL” flag to the constructor of the ReportOptionsclass, as the second parameter. Then, we pass the actual SQL query to the “set_sql” method.

Report Name

The third parameter we pass to the constructor of the “ReportOptions” class is the report name “sql_01,” which should appear in the generated report’s URL. If we don’t pass this parameter, the report name will be a concatenation of the prefix “rep” and the timestamp of the report creation. For example, “rep1581195950”.

Defining a header and a footer

In the above example, we set a header and a footer for the report. We do this by passing “Header goes here” to the “set_header” method, and “Footer goes here” to the “set_footer” method.

Grouping the report by a certain column

In the above example, we group the report by the “country” column. We do this by passing an array with only the “country” column to the “set_grouping” method.

Sorting the report by a certain column

In the above example, we sort the report by the “id” column. We do this by passing “id” to the “sort_by” method.

Giving labels to columns

In the above example, we want to give the label “product id” to the column “id.” So, we pass both the column “id” and the alias “product id” to the “label” method.

Please note that we can repeat this step if we want to label more columns in the report.

Selecting a layout

Smart Report Engine supports many layouts. In this example, we choose the “Block” layout. We do this by passing “block” to the “set_layout” method.

Selecting all columns of the selected table to appear in the report

In the above example, we want to select all columns of the SQL query to appear in the report. Therefore, we call the “select_all_fields” method.

Then after defining all the options for the report using an instance of type ReportOptions, we will pass this instance to the constructor of the CustomEngine class. By doing this, we create a new object of CustomEngine, which we will use to call the “create_report” method to generate the report and return its URL.

Summery

Smart Report Engine is a powerful PHP reporting engine using which, you can easily convert your SQL queries to reports for MYSQL DB.

Display Report Next Tutorial