Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines
Mixing SQL query with parameters looks bad. In the new version 1.38.6 of KoolReport, you can do this:
function setup()
{
$this->src("automaker")
->query("
SELECT
orderNumber, orderDate
FROM
orders
WHERE
orderDate > :startDate
AND
orderDate < :endDate
")
->params(array(
":startDate"=>$this->params["startDate"],
":endDate"=>$this->params["endDate"],
))
->pipe(..)
...
}
Let's say i want to display all columns in my report from ->query($this->params['data']) and there is another additional column that i want to add in same report. This additional column i have to use CalculatedColumn, which includes value from ->query($this->params['data1']). If i use these code below, i get an error. {trans} is from ->query($this->params['data1']).
$this->src("automaker")
->query($this->params['data'])
$this->src("automaker")
->query($this->params['data1'])
->pipe(new CalculatedColumn(array(
"Avg"=>array(
"exp"=>"{totalqty}/{trans}",
"type"=>"number",
"decimals"=>1,
)
->pipe($this->dataStore("report1c"))
Two flows of data are separated. The first flow does not have the end. The second flow pipe data to CalculatedColumn and of course this flow does not have data from first flow. You may consider to use the Join process to join to flow of data into one then you do calculation.
Here is the information of Join process:
https://www.koolreport.com/data-processing#row-join
Hope that helps.
Hi guys, i just found small bug (maybe) on "->params()" function. Have you test this function to set params to inside of plain sql statement (not to comparasion statement e.g inside of 'WHERE') ?
Because when i try this code:
$sqlprodi = "
SELECT m1.k_jurusan_ppg, m1.keterangan
FROM gpodb.ajuan_ptk_ppg a1
LEFT JOIN gpodb.m_jurusan_ppg m1 ON a1.k_jurusan_ppg=m1.k_jurusan_ppg
WHERE a1.k_ajuan_ppg IN (2,3)
GROUP BY m1.k_jurusan_ppg, m1.keterangan
ORDER BY m1.keterangan
";
$this->src('gpo')
->query(
"
SELECT
CASE WHEN a.sekolah_id<>0 THEN m2.keterangan ELSE m21.keterangan END propinsi,
CASE WHEN a.sekolah_id<>0 THEN m5.keterangan ELSE m51.keterangan END kota,
m3.keterangan prodi,
COUNT(DISTINCT a.ptk_id) jml
FROM gpodb.ptk a
INNER JOIN gpodb.ajuan_ptk_ppg c ON a.ptk_id=c.ptk_id
LEFT JOIN gpodb.sekolah d ON a.sekolah_id=d.sekolah_id
LEFT JOIN (" .$sqlprodi. "LIMIT :prodilimit,10) m3 ON c.k_jurusan_ppg=m3.k_jurusan_ppg
LEFT JOIN gpodb.m_propinsi m2 ON d.k_propinsi=m2.k_propinsi
LEFT JOIN gpodb.m_kota m5 ON d.k_kota=m5.k_kota
LEFT JOIN gpodb.m_propinsi m21 ON a.k_propinsi=m21.k_propinsi
LEFT JOIN gpodb.m_kota m51 ON a.k_kota=m51.k_kota
WHERE c.k_ajuan_ppg IN (2,3) AND m3.keterangan IS NOT NULL
GROUP BY propinsi, kota, prodi
"
)->params(array(
":prodilimit"=>$this->params["prodilimit"],
))
->saveTo($prodi);
the result is the datastore has no data. But when i try this:
$this->src('gpo')
->query(
"
SELECT
CASE WHEN a.sekolah_id<>0 THEN m2.keterangan ELSE m21.keterangan END propinsi,
CASE WHEN a.sekolah_id<>0 THEN m5.keterangan ELSE m51.keterangan END kota,
m3.keterangan prodi,
COUNT(DISTINCT a.ptk_id) jml
FROM gpodb.ptk a
INNER JOIN gpodb.ajuan_ptk_ppg c ON a.ptk_id=c.ptk_id
LEFT JOIN gpodb.sekolah d ON a.sekolah_id=d.sekolah_id
LEFT JOIN (" .$sqlprodi. "LIMIT " .$this->params["prodilimit"]. ",10) m3 ON c.k_jurusan_ppg=m3.k_jurusan_ppg
LEFT JOIN gpodb.m_propinsi m2 ON d.k_propinsi=m2.k_propinsi
LEFT JOIN gpodb.m_kota m5 ON d.k_kota=m5.k_kota
LEFT JOIN gpodb.m_propinsi m21 ON a.k_propinsi=m21.k_propinsi
LEFT JOIN gpodb.m_kota m51 ON a.k_kota=m51.k_kota
WHERE c.k_ajuan_ppg IN (2,3) AND m3.keterangan IS NOT NULL
GROUP BY propinsi, kota, prodi
"
)
->saveTo($prodi);
it was successful
Could you please open the \koolreport\datasources\PdoDataSource.php and replace the old bindParams() with this new one to see if it works well.
protected function bindParams($query,$sqlParams)
{
if($sqlParams!=null)
{
foreach($sqlParams as $key=>$value)
{
if(gettype($value)==="array")
{
$value = "'".implode("','",$value)."'";
$query = str_replace($key,$value,$query);
}
else if(gettype($value)==="string")
{
$query = str_replace($key,"'$value'",$query);
}
else
{
$query = str_replace($key,$value,$query);
}
}
}
return $query;
}
i try to modify your code above, it works with this:
protected function bindParams($query,$sqlParams)
{
if($sqlParams!=null)
{
foreach($sqlParams as $key=>$value)
{
if(gettype($value)==="array")
{
$value = "'".implode("','",$value)."'";
$query = str_replace($key,$value,$query);
}
else if(gettype($value)==="string")
{
$query = str_replace($key,"$value",$query);
}
else
{
$query = str_replace($key,$value,$query);
}
}
}
return $query;
}
Let KoolReport help you to make great reports. It's free & open-source released under MIT license.
Download KoolReport View demo