Hi Sebastian! I changed my database structure by combining all the three tables into one table. All the data from the three tables are captured in a single table. The items under the mineral rights are accessed in a drop down menu under table column MineralRights.
MinRightsReceived, MinRightsProcesses and MineralRightsGranted are the column names in that single table.
It works perfectly. The only problem is that when I set Multiple to False, anytime I load the page for the first time I get some error message in the Region and Year parameter text fields. However if I click on the submit button the message is cleared. Any solution to that please. These are my files and the screenshot.
After clicking on submit I get this:
MyReport.php
<?php
//Step 1: Load KoolReport
require_once "../load.koolreport.php";
use \koolreport\clients\Bootstrap;
use \koolreport\processes\ColumnMeta;
use \koolreport\processes\Transpose;
use \koolreport\processes\ColumnRename;
use \koolreport\core\src\core\Utility;
use \koolreport\cube\processes\Cube;
//Step 2: Creating Report class
class MyReport extends \koolreport\KoolReport
{
use \koolreport\export\Exportable;
use \koolreport\inputs\Bindable;
use \koolreport\inputs\POSTBinding;
use \koolreport\clients\Bootstrap;
protected function defaultParamValues()
{
return array(
"years"=>array(2019),
"Years"=>array(),
"Region"=>array(),
);
}
protected function bindParamsToInputs()
{
return array(
"Years",
"Region",
);
}
protected function settings()
{
return array(
"dataSources"=>array(
"data"=>array(
"connectionString"=>"mysql:host=localhost;dbname=mlnrgh",
"username"=>"root",
"password"=>"",
"charset"=>"utf8"
),
)
);
}
protected function setup()
{
$query_params = array();
if($this->params["Years"]!=array())
{
$query_params[":Years"] = $this->params["Years"];
}
if($this->params["Region"]!=array())
{
$query_params[":Region"] = $this->params["Region"];
$this->src('data')->query("
select
MineralRights,
sum(ApplicationsRcvd) as 'ApplicationsReceived',
sum(ApplicationsProc) as 'ApplicationsProcessed',
sum(ApplicationGranted) as 'ApplicationsGranted',
YEAR(RecDate) as 'Year'
from mineralsmgt
where 1=1
".(($this->params["Years"]!=array())?"and YEAR(RecDate) in (:Years)":"")."
".(($this->params["Region"]!=array())?"and Region in (:Region)":"")."
GROUP BY Year, Region, MineralRights
")->params($query_params)
//$node->pipe(new Cube(array(
// "row" => "MineralRights",
// )))
// ->saveTo($source);
//Save orginal data
// $source->pipe($this->dataStore("origin"));
//Pipe through process to get result
// ->pipe(new Transpose())
/* ->pipe(new ColumnRename(array(
"c0"=>"Mineral Rights",
"c1"=>"Number of Applications Received",
))) */
->pipe($this->dataStore("result"));
}
}
}
MyReport.view.php
<?php
use \koolreport\widgets\koolphp\Table;
use \koolreport\inputs\Select2;
use \koolreport\widgets\google;
use \koolreport\widgets\google\PieChart;
use \koolreport\clients\bootstrap;
use \koolreport\widgets\google\ColumnChart;
use \koolreport\widgets\google\BarChart;
?>
<div class="report-content">
<div class="text-center">
<h1>Minerals Management</h1>
<p class="lead">Mineral Rights / Licenses</p>
</div>
<form method="post">
<div class="row">
<div class="col-md-6">
<div class="form-group">
<b>Select Year</b>
<?php
Select2::create(array(
"multiple"=>false,
"name"=>"Years",
"dataSource"=>$this->src("data")->query("
select YEAR(RecDate) as Year
from mineralsmgt
group by Year
"),
"attributes"=>array(
"class"=>"form-control"
//"size"=>1
)
));
?>
</div>
<div class="form-group">
<b>Select Region</b>
<?php
Select2::create(array(
"multiple"=>false,
"name"=>"Region",
"dataSource"=>$this->src("data")->query("
select Region
from mineralsmgt
group by Region
")->params(
$this->params["Years"]!=array()?
array(":Years"=>$this->params["Years"]):
array()
),
"attributes"=>array(
"class"=>"form-control"
//"size"=>1
)
));
?>
</div>
<div class="form-group">
<button class="btn btn-primary">Submit</button>
<button formaction="exportLicenses.php" class="btn btn-primary">Download PDF</button>
</div>
</div>
</div>
</form>
<?php
Table::create(array(
"dataSource"=>$this->dataStore("result"),
"columns"=>array(
"MineralRights"=>array(
"label"=>"Mineral Rights",
"type"=>"string",
),
//"Region",
"ApplicationsReceived"=>array(
"label"=>"Number of Applications Received",
"type"=>"number",
),
"ApplicationsProcessed"=>array(
"label"=>"Number of Applications Processed",
"type"=>"number",
),
"ApplicationsGranted"=>array(
"label"=>"Number of Licenses/Leases Granted",
"type"=>"number",
),
// "amount"=>array("prefix"=>"$"),
// "Year"=>array("format"=>false)
),
"grouping"=>array(
"Year"=>array(
"calculate"=>array(
"{ApplicationsReceived}"=>array("sum","ApplicationsReceived"),
"{ApplicationsProcessed}"=>array("sum","ApplicationsProcessed"),
"{ApplicationsGranted}"=>array("sum","ApplicationsGranted"),
),
"top"=>"<b>Year {Year}</b>",
"bottom"=>"<td><b>Total of year {Year}</b></td>
<td><b>{ApplicationsReceived}</b></td>
<td><b>{ApplicationsProcessed}</b></td>
<td><b>{ApplicationsGranted}</b></td>
",
),
),
"paging"=>array(
"pageSize"=>25
),
"cssClass"=>array(
"table"=>"table-bordered table-striped table-hover",
)
) )
?>
<i class="fa fa-arrow-down" style="font-size:24px;"></i>
<pre style="font-weight:bold"><code>
</code></pre>
<i class="fa fa-arrow-down" style="font-size:24px;"></i>
<div style="margin-top:20px;">
<div class="row">
<div class="col-md-8">
<?php
BarChart::create(array(
"dataStore"=>$this->dataStore('result'),
"options"=>array(
'title' => 'Barchart Showing No. of Applications Received, Processed and Granted',
'isStacked' => false
),
"columns"=>array(
"MineralRights"=>array(
"label"=>"Mineral Rights",
"type"=>"string",
),
"ApplicationsReceived"=>array(
"label"=>"Number of Applications Received",
"type"=>"number",
),
"ApplicationsProcessed"=>array(
"label"=>"Number of Applications Processed",
"type"=>"number",
),
"ApplicationsGranted"=>array(
"label"=>"Number of Licenses/Leases Granted",
"type"=>"number",
),
),
"width"=>"100%",
));
?>
</div>
<div class="col-md-8">
<?php
PieChart::create(array(
"dataSource"=>$this->dataStore("result"),
"options"=>array(
'title' => 'Piechart Showing Revenue by Regions',
),
"columns"=>array(
"MineralRights"=>array(
"label"=>"Mineral Rights",
"type"=>"string",
),
"ApplicationsReceived"=>array(
"label"=>"Number of Applications Received",
"type"=>"number",
),
"ApplicationsProcessed"=>array(
"label"=>"Number of Applications Processed",
"type"=>"number",
),
"ApplicationsGranted"=>array(
"label"=>"Number of Applications Granted",
"type"=>"number",
),
),
"options"=>array(
"legend"=>array(
"position"=>"right"
),
)
));
?>
</div>
</div>
</div>
</div>
MyReportPdf.view.php
<?php
use \koolreport\widgets\koolphp\Table;
use \koolreport\inputs\Select2;
use \koolreport\widgets\google;
use \koolreport\widgets\google\PieChart;
use \koolreport\clients\bootstrap;
use \koolreport\widgets\google\ColumnChart;
use \koolreport\widgets\google\BarChart;
?>
<html>
<body style="margin:0.5in 1in 0.5in 1in">
<link rel="stylesheet" href="../../../assets/bs3/bootstrap.min.css" />
<link rel="stylesheet" href="../../../assets/bs3/bootstrap-theme.min.css" />
<div class="page-header" style="text-align:right"><i></i></div>
<div class="page-footer" style="text-align:right">{pageNum}</div>
<div class="text-center">
<div class="report-content">
Minerals Management
Mineral Rights / Licenses for
$this->dataStore("result"),
"columns"=>array(
"MineralRights"=>array(
"label"=>"Mineral Rights",
"type"=>"string",
),
//"Region",
"ApplicationsReceived"=>array(
"label"=>"Number of Applications Received",
"type"=>"number",
),
"ApplicationsProcessed"=>array(
"label"=>"Number of Applications Processed",
"type"=>"number",
),
"ApplicationsGranted"=>array(
"label"=>"Number of Licenses/Leases Granted",
"type"=>"number",
),
),
"grouping"=>array(
"Year"=>array(
"calculate"=>array(
"{ApplicationsReceived}"=>array("sum","ApplicationsReceived"),
"{ApplicationsProcessed}"=>array("sum","ApplicationsProcessed"),
"{ApplicationsGranted}"=>array("sum","ApplicationsGranted"),
),
"top"=>"
Year {Year}",
"bottom"=>"
Total of year {Year} |
{ApplicationsReceived} |
{ApplicationsProcessed} |
{ApplicationsGranted} |
",
),
),
"paging"=>array(
"pageSize"=>25
),
"cssClass"=>array(
"table"=>"table-bordered table-striped table-hover",
)
) )
?>
$this->dataStore('result'),
"options"=>array(
'title' => 'Barchart Showing No. of Applications Received, Processed and Granted',
'isStacked' => false
),
"columns"=>array(
"MineralRights"=>array(
"label"=>"Mineral Rights",
"type"=>"string",
),
"ApplicationsReceived"=>array(
"label"=>"Number of Applications Received",
"type"=>"number",
),
"ApplicationsProcessed"=>array(
"label"=>"Number of Applications Processed",
"type"=>"number",
),
"ApplicationsGranted"=>array(
"label"=>"Number of Licenses/Leases Granted",
"type"=>"number",
),
),
"width"=>"100%",
));
?>
$this->dataStore("result"),
"options"=>array(
'title' => 'Piechart Showing Number of Applications Received',
),
"columns"=>array(
"MineralRights"=>array(
"label"=>"Mineral Rights",
"type"=>"string",
),
"ApplicationsReceived"=>array(
"label"=>"Number of Applications Received",
"type"=>"number",
),
"ApplicationsProcessed"=>array(
"label"=>"Number of Applications Processed",
"type"=>"number",
),
"ApplicationsGranted"=>array(
"label"=>"Number of Applications Granted",
"type"=>"number",
"emphasis"=>true
),
),
"options"=>array(
"legend"=>array(
"position"=>"right"
),
"width"=>'100%',
)
));
?>
</html>
