KoolReport's Forum

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

Dynamic reporting with report writer - Help please! #567

Open Dan Smith opened this topic on on Dec 20, 2018 - 32 comments

Dan Smith commented on Dec 20, 2018

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!!

KoolReport commented on Dec 21, 2018

The problem line on your bindParamsToInputs(). What return from this function is:

array(" 'p0','p1','p2' ")

But it expected

array('p0','p1','p2')

That's reason why your inputs does not know the param to connect to.

Please use the array_push() to push each name of inputs to the array.

Dan Smith commented on Dec 22, 2018

thanks, changed code to below but still does nothing.

    function bindParamsToInputs()
    {
		$paramarray=array();
		for ($i=0; $i<count($this->arrdata); $i++){
			if ($this->arrdata[$i]['fldparameter']!='No'){
				array_push($paramarray,'p'.$i);
			}
		}
		return $paramarray;
    }

If I run a report with no parameters it runs perfectly but in that scenario the run report button is never pressed as it generates the report straight away

KoolReport commented on Dec 23, 2018

Is there any error showing?

Dan Smith commented on Dec 23, 2018

hmm, deleted koolreport folder, setup again with the stock download and instant and inputs packages put in the packages folder. and is now giving

testreport?id=2:374 Uncaught ReferenceError: KoolReport is not defined

on the line which shows in the code as below so not sure what I've done!?

<script type="text/javascript">
KoolReport.widget.init({"js":["koolreport_assets\/2538894251\/jquery.min.js",["koolreport_assets\/4229711927\/select2\/js\/select2.full.min.js"]],"css":["koolreport_assets\/4229711927\/select2\/css\/select2.min.css"]},function(){
    p0 = $('#p0');
    let name = p0;
    name.select2([]);
        name.defaultValue = name.val();
    name.reset = function() {
       name.val(p0.defaultValue).trigger('change');
    }
    });    
</script>
KoolReport commented on Dec 23, 2018

Please look into the console to see if any KoolReport.js is not loaded. Try Ctrl+F5 to see how.

Dan Smith commented on Dec 23, 2018

yep, is there

KoolReport commented on Dec 23, 2018

Thats strange if KoolReport is not defined. Is your page is online? I would like to see if possible?

Dan Smith commented on Dec 23, 2018

https://online.adviser-suite.co.uk/testreport?id=2

KoolReport commented on Dec 23, 2018

I am out of office so I will come back and check your url in few hours

Dan Smith commented on Dec 23, 2018

thanks

KoolReport commented on Dec 23, 2018

Please remove the use \koolreport\clients\Bootstrap;will solve the issue. It seems that your page has bootstrap already, you do not need to add again in the report.

Dan Smith commented on Dec 23, 2018

er, done and no difference?

KoolReport commented on Dec 23, 2018

It is quite mystery as rendering report will render KoolReport.js as well on top. Anyway, here is the quick fix, add following KoolReport script like this:

<script src="assets/vendor/jquery-serialize.js"></script>
<script type='text/javascript' src='/koolreport/src/clients/core/KoolReport.js'></script>

After the jquery-serialize.js you add the KoolReport.js

Dan Smith commented on Dec 23, 2018

ok so that fixed that issue, though same issue when you press run report, nothing happens, just refreshes the page


$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 "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\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);
		$this->rwhere=stripslashes($row['rWhere']);
	}
	
    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=array();
		for ($i=0; $i<count($this->arrdata); $i++){
			if ($this->arrdata[$i]['fldparameter']!='No'){
				array_push($paramarray, 'p'.$i); 
			}
		}
        return $paramarray;
    }
    function setup()
    {
		$data=$this->getData();
		
		for ($i=0; $i<count($this->arrdata); $i++){
			if ($this->arrdata[$i]['fldparameter']!='No'){
				$fld=$this->arrdata[$i]['fldname'];
				$this->src('tag')->query("SELECT distinct( ".$fld.") FROM tblInitial where ".$fld." is not null and amAmount>100000 ORDER BY ".$fld." 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]['fldgroup']!='No'){
				$groupby.=$this->arrdata[$i]['fldname'].', ';
			}
		}				
		$groupby='group by '.substr($groupby,0,strlen($groupby)-2);

		for ($l=1; $l<50; $l++){
			for ($i=0; $i<count($this->arrdata); $i++){
				if ($this->arrdata[$i]['fldsort']==$l){
					$orderby.=$this->arrdata[$i]['fldname'].', ';
				}
			}				
		}
		if ($orderby!=''){
			$orderby='order by '.substr($orderby,0,strlen($orderby)-2);
		}
		
		for ($i=0; $i<count($this->arrdata); $i++){
			if ($this->arrdata[$i]['fldparameter']!='No'){
				$where.=$this->arrdata[$i]['fldname'].'=:p'.$i.' and ';
				$params[":p".$i]=$this->params["p".$i];
			}
		}				
		if ($where!=''){
			$where='where '.substr($where,0,strlen($where)-4);
		}
		if ($this->rwhere!=''){
			if ($where!=''){
				$where.=' and '.$this->rwhere.' ';
			} else {
				$where='where '.$this->rwhere.' ';
			}
		}
		
		$this->src('tag')->query($q.' '.$where.' '.$groupby.' '.$orderby)->pipe($this->dataStore('reportdata')); //->params($params)
		LogIt($q.' '.$where.' '.$groupby.' '.$orderby);
    }
}

$report = new MyReport();
$report->start();


?>

	<section role="main" class="content-body">


		<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(); ?>
KoolReport commented on Dec 23, 2018

I notice the $data variable in the view. I did see it has any definition. I see the $data in the setup() method of MyReport however, it is local variable and will not be available at the view.

Dan Smith commented on Dec 23, 2018

understand, but doesn't make too much sense as if you take a look at https://online.adviser-suite.co.uk/testreport?id=3 which is the same report but without any parameters, it shows the resulting data correctly so must be working through?

then again, that report runs without and parameters hence nothing is being POSTed.

I'm logging the SQL generated and it's the same first display and after posting, the only bit which isn't working is the parameter bit - I'd expect the sql to be updated to show the selected option rather than

select amLender as f0, amAmount as f1, (amFees_Proc+amFees_Adviser) as f2, iIntroducerFee as f3, (amFees_Proc+amFees_Adviser-iIntroducerFee) as f4 FROM tblInitial  where amLender=:p0  group by amLender order by amLender

what do you think I'd need to do to get it working?

KoolReport commented on Dec 23, 2018

In the bindInputsToParams, please try to var_dump()

    function bindParamsToInputs()
    {
		$paramarray=array();
		for ($i=0; $i<count($this->arrdata); $i++){
			if ($this->arrdata[$i]['fldparameter']!='No'){
				array_push($paramarray,'p'.$i);
			}
		}
                var_dump($paramarray);
		return $paramarray;
    }

In the setup() method, please try to var_dump the $_POST and report params

function setup()
{

echo "[POST]";
var_dump($_POST);
echo "[Params]";
var_dump($this->params);

Let test if everything correct.

Dan Smith commented on Dec 23, 2018

ok so on first load (before post)

and once button pressed (ie after POST)

Dan Smith commented on Dec 26, 2018

made one change to bindParamsToInputs as param was eg p0 but wasn't mapped to a field. Didn't make any change though so somewhere the params are not being passed through anywhere?

    function bindParamsToInputs()
    {
		$paramarray=array();
		for ($i=0; $i<count($this->arrdata); $i++){
			if ($this->arrdata[$i]['fldparameter']!='No'){
				array_push($paramarray, array($this->arrdata[$i]['fldname'] => 'p'.$i)); 
			}
		}
		var_dump($paramarray);
        return $paramarray;
    }
KoolReport commented on Dec 26, 2018

I have asked dev.team to check on your case. Your image is very helpful.

Dan Smith commented on Dec 26, 2018

thanks, very much appreciated

Dan Smith commented on Dec 28, 2018

hi, have the developers got any update please?

Dan Smith commented on Dec 31, 2018

Morning, is there a way to escalate this, I've just purchased the pro version so I can integrate the full product as soon as this issue is resolved.

KoolReport commented on Dec 31, 2018

Thank you very much for purchasing KoolReport Pro, very appreciated!

I suspected that the Inputs package does not work well with instant SinglePage since I see that the post value does not go to the report's params. So I told dev.team to make test on that. I have run the test by myself and seems they are working well. In my test, there are textbox and select2. So the issue must be other.

One thing I notice is the $this->arrdata. This is not initiated if you dont call the getData() methods. And you only call this method inside the setup(). This means that when the report initiates its service ( which happens before the setup()), the bindParamsToInputs() will not get the correct arrdata ( actually is will be undefined). That will cause issue of parameters is not recognized. That why your inputs will be blank.

Dan Smith commented on Dec 31, 2018

hmm

Dan Smith commented on Dec 31, 2018

I changed the bindParamsToInputs to

    function bindParamsToInputs()
    {
		$query='select * from tblReport where rID="'.$_GET['id'].'"';	
		$result=mysqli_query($GLOBALS['link'],$query);	
		$row=@mysqli_fetch_array($result);
		$arrdata=json_decode(str_replace("'",'"',$row['rBody']),true);		
		
		$paramarray=array();
		for ($i=0; $i<count($arrdata); $i++){
			if ($arrdata[$i]['fldparameter']!='No'){
				$toadd=array($arrdata[$i]['fldname'] => 'p'.$i);
				echo "Param: ".$arrdata[$i]['fldname'];
				array_push($paramarray, array($arrdata[$i]['fldname'] => 'p'.$i)); 
			}
		}
		var_dump($paramarray);
        return $paramarray;
    }

to ensure that the values are there.

here's the screenshot before I select parameter and click run report

and here's the after

Am I better off ditching the instant package and running as separate or is there some deeper issue with this approach?

Dan Smith commented on Jan 4, 2019

ok, so I split into regular files as below to try and get this to work

testreport.php

<?php
require_once('reportview.php');

$report=new MyReport;
$report->run()->render();

?>

reportview.php

<?php
require_once('fn.php');

?>
<!doctype html>

<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no" />
<link href="https://fonts.googleapis.com/css?family=Open+Sans:300,400,600,700,800|Shadows+Into+Light" rel="stylesheet" type="text/css">
<link rel="stylesheet" href="assets/vendor/bootstrap/css/bootstrap.min.css" />
<link rel="stylesheet" href="assets/stylesheets/theme.css" />
<link rel="stylesheet" href="assets/stylesheets/skins/default.css">

<script src="assets/vendor/jquery/jquery.js"></script>		
<script src="assets/vendor/jquery-browser-mobile/jquery.browser.mobile.js"></script>		
<script src="assets/vendor/jquery-cookie/jquery-cookie.js"></script>		
<script src="assets/vendor/bootstrap/js/bootstrap.js"></script>		
<script src="assets/vendor/modernizr/modernizr.js"></script>		
<script type='text/javascript' src='koolreport/src/clients/core/KoolReport.js'></script>
</head>
<body>
	<h1>Report</h1>
<?php

require_once "koolreport/autoload.php";

class MyReport extends \koolreport\KoolReport
{
	use \koolreport\inputs\Bindable;
	use \koolreport\inputs\POSTBinding;
	
	protected 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);
				
		for ($i=0; $i<count($this->arrdata); $i++){			
			$d=array("label"=>$this->arrdata[$i]['fldtitle'],"prefix"=>$this->arrdata[$i]['fldprefix'],"footer"=>$this->arrdata[$i]['fldtotal'],"footerText"=>"<b>@value</b>");
			$cols["f".$i]=$d;
		}
		$this->cols=$cols;	
		
	}
	
    protected function settings()
    {
        return array(
            "dataSources"=>array(				"tag"=>array("connectionString"=>"mysql:host=localhost;dbname=".$GLOBALS['dbname'],"username"=>$GLOBALS['dbuser'],"password"=>$GLOBALS['dbpass'],"charset"=>"utf8"),  
            ),
        );
    }
    protected function bindParamsToInputs()
    {
		$query='select * from tblReport where rID="'.$_GET['id'].'"';	
		$result=mysqli_query($GLOBALS['link'],$query);	
		$row=@mysqli_fetch_array($result);
		$arrdata=json_decode(str_replace("'",'"',$row['rBody']),true);		
		
		$paramarray=array();
		for ($i=0; $i<count($arrdata); $i++){
			if ($arrdata[$i]['fldparameter']!='No'){
				$paramarray[$arrdata[$i]['fldname']]="p".$i;
			}
		}
        return $paramarray;
		
		
    }
    protected function setup()
    {
		$data=$this->getData();
		
		for ($i=0; $i<count($this->arrdata); $i++){
			if ($this->arrdata[$i]['fldparameter']!='No'){
				$fld=$this->arrdata[$i]['fldname'];
				echo "Setup: ".$fld.' => p'.$i.'<br>';
				$this->src('tag')->query("SELECT distinct( ".$fld.") FROM tblInitial where ".$fld." is not null ORDER BY ".$fld." asc;")->pipe($this->dataStore("p".$i));
			}
		}
		
		$q='';
		$groupby='';
		$where='';
		$params=array();
		
		for ($i=0; $i<count($this->arrdata); $i++){
			if ($this->arrdata[$i]['fldshow']=='Yes'){
				$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]['fldgroup']!='No'){
				$groupby.=$this->arrdata[$i]['fldname'].', ';
			}
		}				
		$groupby='group by '.substr($groupby,0,strlen($groupby)-2);

		for ($l=1; $l<50; $l++){
			for ($i=0; $i<count($this->arrdata); $i++){
				if ($this->arrdata[$i]['fldsort']==$l){
					$orderby.=$this->arrdata[$i]['fldname'].', ';
				}
			}				
		}
		if ($orderby!=''){
			$orderby='order by '.substr($orderby,0,strlen($orderby)-2);
		}
		
		for ($i=0; $i<count($this->arrdata); $i++){
			if ($this->arrdata[$i]['fldparameter']!='No'){
				$where.=$this->arrdata[$i]['fldname'].'=:p'.$i.' and ';
				$params[":p".$i]=$this->params["p".$i];
			}
		}		

		if ($where!=''){
			$where='where '.substr($where,0,strlen($where)-4);
		}
		if ($this->rwhere!=''){
			if ($where!=''){
				$where.=' and '.$this->rwhere.' ';
			} else {
				$where='where '.$this->rwhere.' ';
			}
		}
		echo $q.' '.$where.' '.$groupby.' '.$orderby;
		$this->src('tag')->query($q.' '.$where.' '.$groupby.' '.$orderby)->params($params)->pipe($this->dataStore('reportdata')); //
    }
}
?>

reportview.view.php

<?php
use \koolreport\widgets\koolphp\Table;
use \koolreport\inputs\DateRangePicker;
use \koolreport\inputs\TextBox;
use \koolreport\inputs\Select2;
?>
<form method="post">	
	<?php
	for ($i=0; $i<count($this->arrdata); $i++){
		if ($this->arrdata[$i]['fldparameter']!='No'){
			echo "<div class='row'><div class='form-group'><label class='col-sm-2 control-label'>".$this->arrdata[$i]['fldtitle']."</label><div class='col-md-6 form-group'>";

			if ($this->arrdata[$i]['fldparameter']=='Single'){
				Select2::create(array(
					"name"=>$this->arrdata[$i]['fldname'],
					"multiple"=>true,
					"dataStore"=>$this->dataStore($this->arrdata[$i]['fldname']),
					"defaultOption"=>array("--"=>""),
					"dataBind"=>$this->arrdata[$i]['fldname'],
					"attributes"=>array("class"=>"form-control",)
				));													
			} else if ($this->arrdata[$i]['fldparameter']=='Multi'){
				Select2::create(array(
					"name"=>$this->arrdata[$i]['fldname'],
					"dataStore"=>$this->dataStore($this->arrdata[$i]['fldname']),
					"defaultOption"=>array("--"=>""),
					"dataBind"=>$this->arrdata[$i]['fldname'],
					"attributes"=>array("class"=>"form-control",)
				));							
			} else if ($this->arrdata[$i]['fldparameter']=='Date'){
				DateRangePicker::create(array(
					"name"=>$this->arrdata[$i]['fldname'],
					"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>	
	<?php

Table::create(array(
	"dataSource"=>$this->dataStore('reportdata'),
	"showFooter"=>"bottom",
	"columns"=>($this->cols),
	"cssClass"=>array("table"=>"table table-hover table-bordered")
),false);	
?>

you can see the result at https://online.adviser-suite.co.uk/testreport?id=2 but 1. it seems to show the parameter options in a table rather than the select2 it should be using 2. the submit button isn't showing to run the report 3. it doesn't carry through the cols array from reportview.php 4. there are strange <h1> tags in the result which I don't know where they come from

I don't really understand what's going on here or why. I even tried hardcoding the array in bindParamsToInputs using return array("amLender"=>"p0") to try and bypass that code though did nothing different - still no parameters correctly

Is there anyone I can call to discuss this with to try and get to the bottom of it and work out what the issue is?

KoolReport commented on Jan 4, 2019

I've visited your page and it seems showing the debug view. Could you please turn on the view of report.

Dan Smith commented on Jan 4, 2019

sorry, I've read the docs which say if you don't have the .view.php file it goes into debug mode, though I can't see what I'm doing wrong here from the code above?

Dan Smith commented on Jan 4, 2019

right found the issue, the class name needs to match the filenames etc. have fixed that and is partially running so will continue my tests - thanks!

Dan Smith commented on Jan 4, 2019

ok, have gone back to basics to try and get inputs working hence have hardcoded as follows

reportview.php

<?php
require_once('fn.php');

require_once "koolreport/autoload.php";

class reportview extends \koolreport\KoolReport
{
	use \koolreport\inputs\Bindable;
	use \koolreport\inputs\POSTBinding;
	
    protected function defaultParamValues()
    {
        return array(
            "amLender"=>"NatWest"
        );
    }
    protected function settings()
    {
        return array(
            "dataSources"=>array(				"tag"=>array("connectionString"=>"mysql:host=localhost;dbname=".$GLOBALS['dbname'],"username"=>$GLOBALS['dbuser'],"password"=>$GLOBALS['dbpass'],"charset"=>"utf8"),  
            ),
        );
    }
    protected function bindParamsToInputs()
    {
		return array("amLender"=>"lender");
		
    }
    protected function setup()
    {
	
		$this->src('tag')->query("SELECT distinct(amLender) as amLender FROM tblInitial where amLender is not null and amAmount>100000 ORDER BY amLender asc;")->pipe($this->dataStore("lender"));
		
		$query='select amLender, amAmount, (amFees_Proc+amFees_Adviser) as f2, iIntroducerFee, (amFees_Proc+amFees_Adviser-iIntroducerFee) as f4 FROM tblInitial where amLender=:lender group by amLender order by amLender';
		
		$this->src('tag')->query($query)->params(array("amLender"))->pipe($this->dataStore('reportview')); //
    }
}
?>

reportview.view.php

<?php
use \koolreport\widgets\koolphp\Table;
use \koolreport\inputs\DateRangePicker;
use \koolreport\inputs\TextBox;
use \koolreport\inputs\Select2;
use \koolreport\inputs\Select;
?>

<!doctype html>

<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no" />
<link href="https://fonts.googleapis.com/css?family=Open+Sans:300,400,600,700,800|Shadows+Into+Light" rel="stylesheet" type="text/css">
<link rel="stylesheet" href="assets/vendor/bootstrap/css/bootstrap.min.css" />
<link rel="stylesheet" href="assets/stylesheets/theme.css" />
<link rel="stylesheet" href="assets/stylesheets/skins/default.css">

<script src="assets/vendor/jquery/jquery.js"></script>		
<script src="assets/vendor/jquery-browser-mobile/jquery.browser.mobile.js"></script>		
<script src="assets/vendor/jquery-cookie/jquery-cookie.js"></script>		
<script src="assets/vendor/bootstrap/js/bootstrap.js"></script>		
<script src="assets/vendor/modernizr/modernizr.js"></script>		
<script type='text/javascript' src='koolreport/src/clients/core/KoolReport.js'></script>
</head>
<body>
	<h1>Report</h1>

<form method="post">	
	<?php
		Select::create(array(
					"name"=>'lender',
					"multiple"=>false,
					"dataStore"=>$this->dataStore("lender"),
					"defaultOption"=>array("--"=>""),
					"dataBind"=>'amLender',
					"attributes"=>array("class"=>"form-control",)
				));
	
	?>
	<div class='row'>
		<div class='col-sm-2'><button type="submit" class="btn btn-primary">Run Report</button></div>
	</div>	

</form>	
<div>
<h1>Report Data</h1>
	<?php

Table::create(array(
	"dataSource"=>$this->dataStore('reportview'),
	"showFooter"=>"bottom",
	"columns"=>array("amLender","amAmount","f2","iIntroducerFee","f4"),
	"cssClass"=>array("table"=>"table table-hover table-bordered")
),false);	
?>
</div>

so I'm expecting having gone through the docs and examples that this should work. It should create a select box with lenders listed and then use that parameter for the query to produce the results.

see https://online.adviser-suite.co.uk/testreport?id=2

it still doesn't show any data when the parameter is selected and the report is run

to my mind it's the where amLender=:lender bit in the query. If I remove the where clause it produces the data correctly though obviously with no parameter.

Think this is nearly there but obviously one little bit is missing or wrong?

KoolReport commented on Jan 4, 2019

Hi,

You need to bind the :lender to the query like this:

$this->src('tag')->query($query)
->params(array(
    ":lender"=>$this->params["amLender"]
))
...

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
solved

Inputs