KoolReport's Forum

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

Unable to load the data when multiple options selected #1969

Closed Abhishek opened this topic on on Mar 14, 2021 - 11 comments

Abhishek commented on Mar 14, 2021

Dear Team, In my report I am able to display the data with with single option when selected but the thing is that I am unable to load the data when multiple options are selected. Please help me.

businessreport.view.php ` <?php

use \koolreport\inputs\DateRangePicker;  
use \koolreport\widgets\koolphp\Table;
use \koolreport\inputs\MultiSelect;
use \koolreport\inputs\BSelect;

?> <style>

.calendar.left .daterangepicker_input,
.calendar.right .daterangepicker_input {
    display: none;
}

</style> <div class="report-content">

<div class="text-center">
    <h2>Business Data</h2>
    <p class="lead">
        
    </p>
</div>

<form method="post">
    <div class="row" style="text-align:center;">
        <div class="col-md-3 form-group" style="margin:auto 39%">
            <strong>Select Date</strong><br><br>
            <?php
            DateRangePicker::create(array(
                "name"=>"dateRange",
            ));
            ?>
"brnchName", "multiple"=>true, "dataStore"=>$this->dataStore("tblbranch"), "dataBind"=>"brnchName", "options"=>array( 'numberDisplayed' => 5, 'includeSelectAllOption' => true, ), )); ?>
    <div class="form-group" style="margin-top:30px;text-align:center;">
        <button class="btn btn-lg btn-primary">Submit</button>
    </div>                  
    <!-- <pre><code><?php echo json_encode($this->params,JSON_PRETTY_PRINT) ?></code></pre> -->
</form>
</div>

</div>

<?php

if($this->dataStore("result")->countData()>0)
{
    Table::create(array(
        "dataStore"=>$this->dataStore("result"),
        //"removeDuplicate"=>array("customerName","orderNumber"),
        "cssClass"=>array(
            "table"=>"table table-bordered"
        ),
        "columns"=>array(
            "brnchName"=>array(
                "label"=>"brnchName",
            ),
            "custName"=>array(
                "label"=>"Patitent Name",
            ),
			"custVisitedDate"=>array(
			      "label"=>"VisitedDate",
				  "formatValue"=>function($VisitedDate){
				   if($VisitedDate=='0')
				   {
					   return "null";
				   }
				   return $VisitedDate;
			   }
			),
			"custVistId"=>array(
			      "label"=>"VisitID",
				  "formatValue"=>function($VisitID){
				   if($VisitID=='0')
				   {
					   return "null";
				   }
				   return $VisitID;
			   }
			),
			"custRegisteredDate"=>array(
			   "label"=>"RegistratedDate",
			   "formatValue"=>function($RegistratedDate){
				   if($RegistratedDate=='0')
				   {
					   return "null";
				   }
				   return $RegistratedDate;
			   }
			),
			"custRegistrationId"=>array(
			   "label"=>"RegistrationID",
			   "formatValue"=>function($RegistrationID){
				   if($RegistrationID=='0')
				   {
					   return "null";
				   }
				   return $RegistrationID;
			   }
			),
			"custConsultedDate"=>array(
			    "label"=>"ConsultedDate",
				"formatValue"=>function($ConsultedDate){
				   if($ConsultedDate=='0')
				   {
					   return "null";
				   }
				   return $ConsultedDate;
			   }
			),
			
        ),
		 "paging"=>array(
        "pageSize"=>10
		),
    ));
}
else
{
?>
  <!--  <div class="alert alert-warning">
        <i class="glyphicon glyphicon-info-sign"></i> Sorry, we found no data
    </div> -->
<?php    
}
?>

</div> ` businessreport.php

<?php

require_once "../vendor/koolreport_pro-5.0.4/examples/load.koolreport.php";


class businessreport extends \koolreport\KoolReport
{
    use \koolreport\clients\bootstrap;
	use \koolreport\inputs\Bindable;
    use \koolreport\inputs\POSTBinding;

    protected function defaultParamValues()
    {
        return array(
            "dateRange"=>array(
			date("Y-m-d"),
			date("Y-m-d")
			),
			 "tblcustomerenquiries"=>array(),
			 "brnchName"=>null,
        );
    }

    protected function bindParamsToInputs()
    {
        return array(
            "dateRange"=>"dateRange",
			"tblcustomerenquiries"=>"tblcustomerenquiries",
			"brnchName"=>"brnchName",
        );
    }
    protected function setup()
    {
        $this->src("cloudkli_demo")->query("
            SELECT
			    tb.brnchName,
				ce.custName,
				ce.custAppointmentDate,
				ce.custVisitedDate,
				ce.custVistId,
				ce.custRegisteredDate,
				ce.custRegistrationId,
				ce.custConsultedDate
            FROM
                tblcustomerenquiries ce
				LEFT JOIN tblbranch tb ON ce.custBranchId = tb.brnchId
				WHERE
				tb.brnchName=:brnchName
				AND
				tb.brnchName=:brnchName
				AND
                custCreatedDate >= :start
                AND
                custCreatedDate <= :end
               ")
		->params(array(
            ":start"=>$this->params["dateRange"][0],
            ":end"=>$this->params["dateRange"][1],
            ":tblcustomerenquiries"=>$this->params["tblcustomerenquiries"],
			 ":brnchName"=>$this->params["brnchName"]
        ))
		
         //->pipe($this->dataStore("tblcustomerenquiries"));
		 ->pipe($this->dataStore("result"));
		 
		 $this->src("cloudkli_demo")->query("
            SELECT 
			     brnchId,
			     brnchName 
			FROM  
			     tblbranch 
			GROUP BY brnchId
        ")
        ->pipe($this->dataStore("tblbranch"));
    } 
}
Abhishek commented on Mar 14, 2021

And the error when i am selecting the multiple options

Fatal error: Uncaught Exception: Query Error >> [You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''DemoSunlightIT' AND tb.brnchName='Demo','DemoSunlightIT' AND ...' at line 14] >> SELECT tb.brnchName, ce.custName, ce.custAppointmentDate, ce.custVisitedDate, ce.custVistId, ce.custRegisteredDate, ce.custRegistrationId, ce.custConsultedDate FROM tblcustomerenquiries ce LEFT JOIN tblbranch tb ON ce.custBranchId = tb.brnchId WHERE tb.brnchName=:pdoParam0,:pdoParam1 AND tb.brnchName=:pdoParam0,:pdoParam1 AND custCreatedDate >= :start AND custCreatedDate <= :end in C:\xampp\htdocs\test\vendor\koolreport_pro-5.0.4\koolreport\core\src\datasources\PdoDataSource.php:433 Stack trace: #0 C:\xampp\htdocs\test\vendor\koolreport_pro-5.0.4\koolreport\core\ in C:\xampp\htdocs\test\vendor\koolreport_pro-5.0.4\koolreport\core\src\datasources\PdoDataSource.php on line 433

Abhishek commented on Mar 14, 2021

Dear Team, I am able to load the data by selecting a single branch but when I selecting multiple branch I am unable to load the data and getting the error as above.

Abhishek commented on Mar 15, 2021

Dear Team, I am able to load the data by selecting a single branch but when I selecting multiple branches I am unable to load the data and getting the error as above.

Abhishek commented on Mar 15, 2021

Dear Team, By using the above code I am able to retrieve the data by selecting a single branch but when I am selecting the multiple branches I am facing an error and that error has also mentioned above but could you please help me that where I have committed the mistake in the above code and I have used Bselect.

And thank you in advance.

Abhishek commented on Mar 15, 2021

Dear Team, The below error is getting trigger when I am selecting multiple option in my report. And I have copied and paste the whole code above please help me where I have committed the mistake. Fatal error: Uncaught Exception: Query Error >> [You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''DemoSunlightIT' OR tb.brnchName='Demo','DemoSunlightIT' AND ...' at line 14] >> SELECT tb.brnchName, ce.custName, ce.custAppointmentDate, ce.custVisitedDate, ce.custVistId, ce.custRegisteredDate, ce.custRegistrationId, ce.custConsultedDate FROM tblcustomerenquiries ce LEFT JOIN tblbranch tb ON ce.custBranchId = tb.brnchId WHERE tb.brnchName=:pdoParam0,:pdoParam1 OR tb.brnchName=:pdoParam0,:pdoParam1 AND ce.custCreatedDate >= :start AND ce.custCreatedDate <= :end in C:\xampp\htdocs\test\vendor\koolreport_pro-5.0.4\koolreport\core\src\datasources\PdoDataSource.php:433 Stack trace: #0 C:\xampp\htdocs\test\vendor\koolreport_pro-5.0.4\koolreport\ in C:\xampp\htdocs\test\vendor\koolreport_pro-5.0.4\koolreport\core\src\datasources\PdoDataSource.php on line 433

Abhishek commented on Mar 15, 2021

Dear Team, By using the above code I am able to retrieve the data by selecting a single branch but when I am selecting the multiple branches I am facing an error and that error has also mentioned above but could you please help me that where I have committed the mistake in the above code and I have used Bselect.

And thank you in advance.

Abhishek commented on Mar 16, 2021

Dear Team, I need help from you people that is with the above code I am able to display the data of a single branch but whereas when I selecting multiple branches I am unable to load the data and getting stuck with a syntax error.

And I know that I am committing a mistake in code but unable to rectify the bug. So could please help me that where I have done the mistake, please.

Regards

Abhishek

Abhishek commented on Mar 16, 2021

Dear Team, Please help me. Kindly consider my issue and help me please

KoolReport commented on Mar 16, 2021

It seems to me your query has problem, you can test by putting query (replace with sample parameter) into phpmyadmin. Make sure the SQL correct, it will work

KoolReport commented on Mar 16, 2021

BTW, I see again and I think that the operator for brnchName condition is not correct. You should use "in" operators if you want to get all data from multiple branch.

Abhishek commented on Mar 17, 2021

Dear Team, Thanks for the instruction and I am able to fetch the data of multiple branches now.

Really thanks.

And you can close the ticket now.

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
help needed

Inputs