Hello, Im having trouble using a input on a SQL query.
MyReport.php:
protected function defaultParamValues()
{
return array(
"dateRange"=>array(
date("Y-m-d", strtotime('-7 days')) . " 00:00:00",
date("Y-m-d", strtotime('-0 days')) . " 23:59:59"
),
"customers"=>array(),
);
}
protected function bindParamsToInputs()
{
return array(
"dateRange"=>"dateRange",
"customers"=>"customers",
);
}
....
public function setup()
{
$this->src('automaker')
->query("SELECT * FROM (
SELECT
c.nombre AS conductor,
(SELECT descripzam FROM (SELECT
conhistid,
h.conductorid,
h.sucursalid,
c.nombre,
s.descripzam,
fecha
FROM tbl_cond_suc_historial h
INNER JOIN tbl_conductor c ON c.conductorid = h.conductorid
INNER JOIN cat_sucursales s ON s.sucursalid = h.sucursalid
WHERE c.sucursalid IN (18, 19, 23, 24)
AND c.estatus =1
UNION ALL
SELECT 0, c.conductorid, c.sucursalid, c.nombre, (SELECT s.descripzam from cat_sucursales s WHERE s.sucursalid = c.sucursalid), fecha_ingreso
FROM tbl_conductor c
WHERE c.sucursalid IN (18, 19, 23, 24)
AND c.estatus =1) t WHERE t.conductorid = g.conductor AND t.fecha < g.eventtimemx ORDER BY fecha DESC LIMIT 1) AS sucursal,
SUM(CASE
WHEN confirmacion_clasificacion = 'conducción distraída' THEN 1
ELSE 0 END
) AS 'conducción distraída',
SUM(CASE
WHEN confirmacion_clasificacion = 'conducción distraída de notificación obligatoria' THEN 1
ELSE 0 END
) AS 'conducción distraída de notificación obligatoria',
SUM(CASE
WHEN confirmacion_clasificacion = 'uso del celular' THEN 1
ELSE 0 END
) AS 'uso del celular',
SUM(CASE
WHEN confirmacion_clasificacion = 'somnolencia' THEN 1
ELSE 0 END
) AS 'somnolencia',
SUM(CASE
WHEN confirmacion_clasificacion = 'bostezar' THEN 1
ELSE 0 END
) AS 'bostezar',
SUM(CASE
WHEN confirmacion_clasificacion IN ('conducción distraída', 'conducción distraída de notificación obligatoria', 'uso del celular', 'somnolencia', 'bostezar') THEN 1
ELSE 0 END
) AS 'total'
FROM tbl_guardian g
INNER JOIN tbl_conductor c ON c.conductorid = g.conductor
WHERE g.eventtimemx > :start
AND g.eventtimemx < :end
AND confirmacion_clasificacion NOT IN ('Conducción normal', 'cámara desalineada', 'sensores cubiertos')
AND c.estatus = 1
AND g.cla_fp <> 1
AND g.conductor = :customers
GROUP BY conductor, sucursal
ORDER BY conductor, sucursal
) a WHERE a.sucursal = 'TSP - LINDE TEPEJI'")
->params(array(
":start"=>$this->params["dateRange"][0],
":end"=>$this->params["dateRange"][1],
":customers"=>$this->params["customers"]
))
->pipe($this->dataStore('purchase_summary'));
$this->src("automaker")->query("
SELECT conductorid, nombre FROM tbl_conductor c
WHERE c.estatus = 1
AND c.sucursalid IN (18, 19, 23, 24)
AND conductorid <> 22")
->pipe($this->dataStore("customers"));
}
MyReport.view.php
<form method="post">
<div class="row">
<div class="col-md-8 offset-md-2">
<?php
Select2::create(array(
"name"=>"customers",
"dataStore"=>$this->dataStore("customers"),
"dataBind"=>array(
"text"=>"nombre",
"value"=>"conductorid",
),
"attributes"=>array(
"class"=>"form-control",
)
));
?>
<br>
<?php
DateRangePicker::create(array(
"name"=>"dateRange"
))
?>
</div>
</div>
<div class="form-group text-center">
<button class="btn btn-success"><i class="glyphicon glyphicon-refresh"></i>Cargar</button>
</div>
<pre><code><?php echo json_encode($this->params,JSON_PRETTY_PRINT) ?></code></pre>
</form>
