I am trying to make a report using multiple inputs to filter the table, but only if they're set. I've managed to transpose my data from the example page (https://www.koolreport.com/examples/reports/advanced/multiple_data_filters/)
However, for the Tribe filter, I need to show data from in between the two dates, set in the start and end date filters above it. I'm trying to understand how to add multiple parameters to this bit of code, but I'm not sure which bit to duplicate.
")->params(
$this->params["years"]!=array()?
array(":years"=>$this->params["years"]):
array()
I'm also not sure that the colon array() bit on the end does, so I don't know which part I should duplicate for the other input parameters.
array(":years"=>$this->params["years"]):
array()
What does the :array() do?
Here us the tribe input code at the moment:
<?php
Select2::create(array(
"name"=>"select",
// "dataStore"=>$this->dataStore("sales"),
"dataSource"=>$this->src("automaker")->query("
Select distinct
TribeName AS "Tribe Name"
From tbl_dealhack
left join tbl_dealhack_attachments on tbl_dealhack_attachments.dealhack_id = tbl_dealhack.id
left join tbl_profile on tbl_profile.user_id = tbl_dealhack.user_id
Left join tbl_attachment_type on tbl_attachment_type.id = tbl_dealhack_attachments.attachment_type_id
left join tbl_companytribe on tbl_companytribe.id = tbl_profile.TribeID
WHERE created_date BETWEEN "2020-06-04 12:47:00" AND "2021-12-21 12:00:00"
AND attachment_type_id = 4
//I'm trying to put created_date BETWEEN :start AND :end into the below code, so it adds the between condition if they're set
".( $this->params["years"]!=array()?"where YEAR(orderDate) in (:years)":"")."
group by customerName
")->params(
$this->params["years"]!=array()?
array(":years"=>$this->params["years"]):
array()
),
"dataBind"=>"Tribe Name",
"attributes"=>array(
"class"=>"form-control",
)
));
Here is the MyReport.php code
<?php
//Step 1: Load KoolReport
require_once "../../load.koolreport.php";
//Step 2: Creating Report class
class MyReport extends \koolreport\KoolReport
{
use \koolreport\inputs\Bindable;
use \koolreport\inputs\POSTBinding;
protected function defaultParamValues()
{
$date=date_create("2021-07-16");
date_add($date,date_interval_create_from_date_string("-42 days"));
$sd = date_format($date,"Y-m-d");
return array(
"startDatePicker"=>date($sd . " 00:00:00"),
"endDatePicker"=>date("Y-m-d 23:59:59"),
"select"=>"",
"locationSelector"=>"",
);
// return array(
// "years"=>array(2003),
// "customerNames"=>array(),
// "productLines"=>array(),
// );
}
protected function bindParamsToInputs()
{
return array(
"startDatePicker",
"endDatePicker",
"select",
"locationSelector",
);
// return array(
// "years",
// "customerNames",
// "productLines"
// );
}
protected function settings()
{
$config = include "../../config.php";
return array(
"dataSources"=>$config
);
// return array(
// "dataSources"=>array(
// "automaker"=>array(
// "connectionString"=>"mysql:host=localhost;dbname=automaker",
// "username"=>"root",
// "password"=>"password",
// "charset"=>"utf8"
// ),
// )
// );
}
protected function setup()
{
$query_params = array();
if($this->params["startDatePicker"]!=array())
{
$query_params[":start"] = $this->params["startDatePicker"];
}
if($this->params["endDatePicker"]!=array())
{
$query_params[":end"] = $this->params["endDatePicker"];
}
if($this->params["select"]!=array())
{
$query_params[":tribe"] = $this->params["select"];
}
// if($this->params["locationSelector"]!=array())
// {
// $query_params[":location"] = $this->params["locationSelector"];
// }
$this->src('dealhackdb')
->query("Select
tbl_dealhack.id,
tbl_profile.user_id AS UserID,
hack_description AS Decription,
created_date AS 'Created',
attachment_location AS 'Attachment Location',
uploaded_date AS Uploaded ,
Company,
TribeName AS 'Tribe Name'
From tbl_dealhack
left join tbl_dealhack_attachments on tbl_dealhack_attachments.dealhack_id = tbl_dealhack.id
left join tbl_profile on tbl_profile.user_id = tbl_dealhack.user_id
Left join tbl_attachment_type on tbl_attachment_type.id = tbl_dealhack_attachments.attachment_type_id
left join tbl_companytribe on tbl_companytribe.id = tbl_profile.TribeID
WHERE 1=1
AND attachment_type_id = 4
"
.(($this->params["startDatePicker"]!=array())?"AND created_date BETWEEN (:start) ":"").""
.(($this->params["endDatePicker"]!=array())?" AND (:end) ":"").""
.(($this->params["select"]!=array())?"AND TribeName LIKE (:tribe) ":"").
""
)
->params($query_params)
->pipe($this->dataStore("sales"));
// GROUP BY year, productLine, customerName
// $this->src('automaker')->query("
// select
// customerName,
// productLine,
// YEAR(orderDate) as year,
// sum(quantityOrdered*priceEach) as amount
// from orders
// join customers
// on
// customers.customerNumber = orders.customerNumber
// join orderdetails
// on orders.orderNumber = orderdetails.orderNumber
// join products
// on products.productCode = orderdetails.productCode
// where 1=1
// ".(($this->params["years"]!=array())?"and YEAR(orderDate) in (:years)":"")."
// ".(($this->params["customerNames"]!=array())?"and customerName in (:customerNames)":"")."
// ".(($this->params["productLines"]!=array())?"and productLine in (:productLines)":"")."
// GROUP BY year, productLine, customerName
// ")
// ->params($query_params)
// ->pipe($this->dataStore("orders"));
}
}
