Ok, posted about this before but couldn't at the time post any sample code so people couldn't understand, am now having another bash.
I'm trying to write a reporting system for a CRM package we've developed. The idea is that non-techies can write a report definition where there know field names. This report definition is saved in a database. Users can then be presented with a list of reports to run. They select the report, the input fields are automatically created and the report runs - simple I thought! I'm using the inputs package and the single page one.
So let's start with the report definition, this is created/edited on the screen below
I then have the code which runs the report as below
$query='select * from tblReport where rID="'.$_GET['id'].'"';
$result=mysqli_query($GLOBALS['link'],$query);
$nr=mysqli_num_rows($result);
if (!$nr) { header('Location:reports');}
$row=@mysqli_fetch_array($result);
if ($row['rFor']=='S' && !IsAdmin()){ header('Location:reports'); }
if ($row['rFor']=='A' && !IsAdmin() && !IsFranchiseAdmin()){ header('Location:reports'); }
$reporttitle=$row['rTitle'];
$data=json_decode(str_replace("'",'"',$row['rBody']),true);
$PageTitle=$reporttitle;
require_once "kreports/koolreport/autoload.php";
use \koolreport\widgets\koolphp\Table;
use \koolreport\inputs\Select2;
use \koolreport\inputs\DateRangePicker;
use \koolreport\instant\Widget;
class MyReport extends \koolreport\KoolReport
{
use \koolreport\instant\SinglePage;
// use \koolreport\clients\Bootstrap;
use \koolreport\inputs\Bindable;
use \koolreport\inputs\POSTBinding;
function getData(){
$query='select * from tblReport where rID="'.$_GET['id'].'"';
$result=mysqli_query($GLOBALS['link'],$query);
$row=@mysqli_fetch_array($result);
$this->arrdata=json_decode(str_replace("'",'"',$row['rBody']),true);
}
function settings()
{
return array(
"dataSources"=>array(
"tag"=>array("connectionString"=>"mysql:host=localhost;dbname=".$GLOBALS['dbname'],"username"=>$GLOBALS['dbuser'],"password"=>$GLOBALS['dbpass'],"charset"=>"utf8"),
),
);
}
function bindParamsToInputs()
{
$paramarray='';
for ($i=0; $i<count($this->arrdata); $i++){
if ($this->arrdata[$i]['fldparameter']!='No'){
$paramarray.='"p'.$i.'",';
}
}
return array(substr($paramarray,0,strlen($paramarray)-1));
}
function setup()
{
$data=$this->getData();
for ($i=0; $i<count($this->arrdata); $i++){
if ($this->arrdata[$i]['fldparameter']!='No'){
$this->src('tag')->query("SELECT distinct(".$this->arrdata[$i]['fldname'].") FROM tblInitial where ".$this->arrdata[$i]['fldname']." is not null ORDER BY ".$this->arrdata[$i]['fldname']." asc;")->pipe($this->dataStore("p".$i));
}
}
$q='';
$groupby='';
$where='';
$params=array();
for ($i=0; $i<count($this->arrdata); $i++){
$q.=$this->arrdata[$i]['fldname'].' as f'.$i.', ';
}
$q='select '.substr($q,0,strlen($q)-2).' FROM tblInitial ';
for ($i=0; $i<count($this->arrdata); $i++){
if ($this->arrdata[$i]['fldtotal']!='No'){
$groupby.='f'.$i.', ';
}
}
$groupby='group by '.substr($groupby,0,strlen($groupby)-2);
for ($i=0; $i<count($this->arrdata); $i++){
if ($this->arrdata[$i]['fldparameter']!='No'){
$where.=arrdata[$i]['fldname'].'=:p'.$i.' and ';
$params[":p".$i]=$this->params["p".$i];
}
}
$where='where '.substr($where,0,strlen($where)-4);
$this->src('tag')
->query($q.' '.$where.' '.$groupby)->pipe($this->dataStore('reportdata')); //->params($params)
}
}
$report = new MyReport();
$report->start();
include_once('header.php');
?>
<section role="main" class="content-body">
<header class="page-header">
<h2><?php echo $reporttitle;?></h2>
</header>
<form method="post">
<?php
for ($i=0; $i<count($data); $i++){
if ($data[$i]['fldparameter']!='No'){
echo "<div class='row'><div class='form-group'><label class='col-sm-2 control-label'>".$data[$i]['fldtitle']."</label><div class='col-md-6 form-group'>";
if ($data[$i]['fldparameter']=='Single'){
Select2::create(array(
"name"=>"p".$i,
"multiple"=>true,
"dataStore"=>$report->dataStore("p".$i),
"defaultOption"=>array("--"=>""),
"dataBind"=>$data[$i]['fldname'],
"attributes"=>array("class"=>"form-control",)
));
} else if ($data[$i]['fldparameter']=='Multi'){
Select2::create(array(
"name"=>"p".$i,
"dataStore"=>$report->dataStore("p".$i),
"defaultOption"=>array("--"=>""),
"dataBind"=>$data[$i]['fldname'],
"attributes"=>array("class"=>"form-control",)
));
} else if ($data[$i]['fldparameter']=='Date'){
DateRangePicker::create(array(
"name"=>"p".$i,
"format"=>"YYYY-MM-DD"
));
}
echo "</div></div></div><br>";
}
}
?>
<div class='row'>
<div class='col-sm-2'><button type="submit" class="btn btn-primary">Run Report</button></div>
</div>
</form>
<br><hr><br>
<div class='row'><div class='col-sm-12'>
<?php
$cols=array();
for ($i=0; $i<count($data); $i++){
$d=array("label"=>$data[$i]['fldtitle'],"prefix"=>$data[$i]['fldprefix'],"footer"=>$data[$i]['fldtotal'],"footerText"=>"<b>@value</b>");
$cols["f".$i]=$d;
}
Widget::create(Table::class,array("dataSource"=>$report->dataStore('reportdata'),"showFooter"=>"bottom","columns"=>($cols)),false);
?>
</div></div>
<?php $report->end(); ?>
So in theory seems simple no? There's some more complex code required to join different tables in etc based upon the fields entered but that's for another day once this works.
Next step, I go to run the report so go to the correct url for that and am presented with the screen below which has been dynamically created
You can see that the parameters have been setup correctly as per the definition and the skeleton titles for the report are there as I'd expect.
Now the problem!
I select the lender and product code (in this example) and press the Run Report button, expecting that this will then produce the data in the table below.
What actually happens is that as the Run Report button is effectively POSTing the page, the process restarts from scratch, so when the page is reloaded, it just presents me with the blank screen again ready to enter the parameters again.
What I need to happen is that the run report button does what it implies so it's an issue with the POST as far as I see.
Any ideas how to get this working as I don't believe I'm the only one trying to incorporate the product in this way and could be useful to others. Obviously once this phase works it can be extended to include charts etc.
Many thanks in advance!!
