Tengo que mostrar los datos de una búsqueda y algunas estadísticas en un grafico de Google (Google Charts)
Esta es la estructura:
Model: "Results" and "ResultsSearch"
Controller: "ResultsController"
Action: "actionConsulta"
View: "consulta" and the search form "_buscador"
La consulta devuelve la lista de "Results" y lo muestro en un GridView con los siguientes campos:
Code - Type - Date - Status.
Adicionalmente debo mostrar las estadísticas de "Results" con Status=1 (exito) y con Status=0 (Fallo) y también debo mostrar las estadísticas separadas por mes:
Example 1: Search "Results" of "Type = A" from "Start Date = January 1" to "End Date = March 1"
Total: 12 Success - 10 Failure
January: 1 Success - 5 Failure
February: 3 Success - 1 Failure
March 8 Success - 4 Failure
Example 2: Search for "Results" of "Type = A" from "Start Date = February 20" to "End Date= March 15"
Total: 15 Success - 10 Failure
February: 3 Success - 1 Failure
March 12 Success - 9 Failure
etc....
He implementado una busqueda global para mostrar los datos en el GridView:
Example 1
Code - Type - Date - Status.
01 - A - 01-01-2015 - Success
02 - A - 02-01-2015 - Failure
03 - A - 03-01-2015 - Failure
04 - A - 04-01-2015 - Failure
05 - A - 05-01-2015 - Failure
06 - A - 06-01-2015 - Failure
07 - A - 01-02-2015 - Success
08 - A - 02-02-2015 - Success
09 - A - 02-02-2015 - Success
10 - A - 03-02-2015 - Failure
etc....
Para mostrar las estadísticas he implementado la misma consulta para cada estado (Status Success and [size="2"]Status[/size][size="2"] Failure) y despues uso "[/size][size="2"]->[/size]getTotalCount". Debido a que las consultas son similares he decidido usar una funcion para la consulta global y otra para el dataProvider global, después filtro cada consulta con "->andFilterWhere" según el "Status"
class ResultsSearch extends Results
{
public function consultaQuery($params)
{
$query = Resultados::find();
$this->load($params);
if (!$this->validate()) {
$query->where('0=1');
return $query;
}
$query->andFilterWhere([
'type' => $this->type,
'date' => $this->date,
]);
$query->andFilterWhere(['between', 'results.date', $this->dateStart, $this->dateEnd]);
return $query;
}
public function consultaDataProvider($query)
{
$dataProvider = new ActiveDataProvider([
'query' => $query,
]);
$dataProvider->setSort([
'attributes' => [
'id',
'type',
'date',
'status',
]
]);
return $dataProvider;
}
public function consulta($params)
{
$query = $this->consultaQuery($params);
$dataProvider = $this->consultaDataProvider($query);
$queryAptos = $this->consultaQuery($params);
$queryAptos->andFilterWhere(['status' => 1]);
$dataProviderAptos = $this->consultaDataProvider($queryAptos);
$queryNoAptos = $this->consultaQuery($params);
$queryNoAptos->andFilterWhere(['status' => 0]);
$dataProviderNoAptos = $this->consultaDataProvider($queryNoAptos);
$dataResult['dataProvider']=$dataProvider;
$dataResult['aptos']=$dataProviderAptos->getTotalCount();
$dataResult['noAptos']=$dataProviderNoAptos->getTotalCount();
return $dataResult;
}
}
Para mostrar las estadísticas por mes he intentado realizar la misma consulta para cada "Status" y para cada mes: (Solo muestro el mes de enero para que el post no sea tan largo)
[size="2"]
public function consulta($params)
{
$query = $this->consultaQuery($params);
$dataProvider = $this->consultaDataProvider($query);
$queryAptos = $this->consultaQuery($params);
$queryAptos->andFilterWhere(['status' => 1]);
$dataProviderAptos = $this->consultaDataProvider($queryAptos);
$queryNoAptos = $this->consultaQuery($params);
$queryNoAptos->andFilterWhere(['status' => 0]);
$dataProviderNoAptos = $this->consultaDataProvider($queryNoAptos);
$dataResult['dataProvider']=$dataProvider;
$dataResult['aptos']=$dataProviderAptos->getTotalCount();
$dataResult['noAptos']=$dataProviderNoAptos->getTotalCount();
//Estadísticas de enero
$queryAptosEne = $this->consultaQuery($params);
$queryAptosEne->andFilterWhere(['status' => 1]);
$queryAptosEne->andFilterWhere(['between', 'results.date', date("2015-01-01"), date("2015-01-31")]);
$dataProviderAptosEne = $this->consultaDataProvider($queryAptos);
$queryNoAptosEne = $this->consultaQuery($params);
$queryNoAptosEne->andFilterWhere(['status' => 0]);
$queryNoAptosEne->andFilterWhere(['between', 'results.date', date("2015-01-01"), date("2015-01-31")]);
$dataProviderNoAptosEne = $this->consultaDataProvider($queryNoAptos);
$ene['aptos']=$dataProviderAptosEne->getTotalCount();
$ene['noAptos']=$dataProviderNoAptosEne->getTotalCount();
$months['ene']=$ene;
//Fin de Estadísticas de enero
$dataResult['months']=$months;
return $dataResult;
}
[/size]
Esta es la vista "consulta"
<div id="results-lista">
//Show total stats
<?=Html::encode('Total: '$results['dataProvider']->getTotalCount().' - Aptos: '.$results['aptos'].' - No Aptos: '.$results['noAptos'])?>
//Show January stats
<?=Html::encode('Enero - Aptos: '.$results['month']['ene']['aptos'].' - No Aptos: '.$results['month']['ene']['noAptos'])?>
//Search Form
<?php echo $this->render('_buscador', ['model' => $resultsSearchModel]); ?>
//GridView
<?= GridView::widget([
'dataProvider' => $resultsDataProvider,
'filterModel' => $resultsSearchModel,
'columns' => [
'id',
'type',
'date',
'status',
],
]);
?>
</div>
No sé si esta es la forma adecuada de resolver este problema o si hay otra forma mejor.
Con la siguiente consulta puedo obtener las estadísticas de los meses y los totales, pero no sé como incluirla en las consultas y en los dataProvider ni de como usarlas en la vista
select code, type, date, status,
sum(case when (status = '1' AND orders.date_order BETWEEN '2015-01-01 00:00:00' AND '2015-01-31 00:00:00') then 1 else 0 end) AptoEne,
sum(case when (status = '1' AND orders.date_order BETWEEN '2015-02-01 00:00:00' AND '2015-02-29 00:00:00') then 1 else 0 end) AptoFeb,
sum(case when (status = '1' AND orders.date_order BETWEEN '2015-03-01 00:00:00' AND '2015-03-31 00:00:00') then 1 else 0 end) AptoMar,
sum(case when (status = '1' AND orders.date_order BETWEEN '2015-04-01 00:00:00' AND '2015-04-30 00:00:00') then 1 else 0 end) AptoAbr,
sum(case when (status = '1' AND orders.date_order BETWEEN '2015-05-01 00:00:00' AND '2015-05-31 00:00:00') then 1 else 0 end) AptoMay,
sum(case when (status = '1' AND orders.date_order BETWEEN '2015-06-01 00:00:00' AND '2015-06-30 00:00:00') then 1 else 0 end) AptoJun,
sum(case when (status = '1' AND orders.date_order BETWEEN '2015-07-01 00:00:00' AND '2015-07-31 00:00:00') then 1 else 0 end) AptoJul,
sum(case when (status = '1' AND orders.date_order BETWEEN '2015-08-01 00:00:00' AND '2015-08-31 00:00:00') then 1 else 0 end) AptoAgo,
sum(case when (status = '1' AND orders.date_order BETWEEN '2015-09-01 00:00:00' AND '2015-09-30 00:00:00') then 1 else 0 end) AptoSep,
sum(case when (status = '1' AND orders.date_order BETWEEN '2015-10-01 00:00:00' AND '2015-10-31 00:00:00') then 1 else 0 end) AptoOct,
sum(case when (status = '1' AND orders.date_order BETWEEN '2015-11-01 00:00:00' AND '2015-11-30 00:00:00') then 1 else 0 end) AptoNov,
sum(case when (status = '1' AND orders.date_order BETWEEN '2015-12-01 00:00:00' AND '2015-12-31 00:00:00') then 1 else 0 end) AptoDic,
sum(case when (status = '0' AND orders.date_order BETWEEN '2015-01-01 00:00:00' AND '2015-01-31 00:00:00') then 1 else 0 end) NoAptoEne,
sum(case when (status = '0' AND orders.date_order BETWEEN '2015-02-01 00:00:00' AND '2015-02-29 00:00:00') then 1 else 0 end) NoAptoFeb,
sum(case when (status = '0' AND orders.date_order BETWEEN '2015-03-01 00:00:00' AND '2015-03-31 00:00:00') then 1 else 0 end) NoAptoMar,
sum(case when (status = '0' AND orders.date_order BETWEEN '2015-04-01 00:00:00' AND '2015-04-30 00:00:00') then 1 else 0 end) NoAptoAbr,
sum(case when (status = '0' AND orders.date_order BETWEEN '2015-05-01 00:00:00' AND '2015-05-31 00:00:00') then 1 else 0 end) NoAptoMay,
sum(case when (status = '0' AND orders.date_order BETWEEN '2015-06-01 00:00:00' AND '2015-06-30 00:00:00') then 1 else 0 end) NoAptoJun,
sum(case when (status = '0' AND orders.date_order BETWEEN '2015-07-01 00:00:00' AND '2015-07-31 00:00:00') then 1 else 0 end) NoAptoJul,
sum(case when (status = '0' AND orders.date_order BETWEEN '2015-08-01 00:00:00' AND '2015-08-31 00:00:00') then 1 else 0 end) NoAptoAgo,
sum(case when (status = '0' AND orders.date_order BETWEEN '2015-09-01 00:00:00' AND '2015-09-30 00:00:00') then 1 else 0 end) NoAptoSep,
sum(case when (status = '0' AND orders.date_order BETWEEN '2015-10-01 00:00:00' AND '2015-10-31 00:00:00') then 1 else 0 end) NoAptoOct,
sum(case when (status = '0' AND orders.date_order BETWEEN '2015-11-01 00:00:00' AND '2015-11-30 00:00:00') then 1 else 0 end) NoAptoNov,
sum(case when (status = '0' AND orders.date_order BETWEEN '2015-12-01 00:00:00' AND '2015-12-31 00:00:00') then 1 else 0 end) NoAptoDic,
count(*) total,
sum(case when status = '1' then 1 else 0 end) Apto,
sum(case when status = '0' then 1 else 0 end) NoApto
from results
where results.cliente=28
Un saludo!