/**
* Creates data provider instance with search query applied
*
* @param array $params
*
* @return ActiveDataProvider
*/
public function search($params)
{
$query = CmbisKpiResult::find();
$query->innerJoin('cmbis_area_hosp', 'Hosp=hcode');
$query->innerJoin('cmbis_hospital', 'cmbis_hospital.hcode=cmbis_kpi_result_hcode.hcode');
$query->innerJoin('cmbis_pop_groups', 'pop_group_id=pop_group');
$dataProvider = new ActiveDataProvider(['query' => $query, 'sort' => ['defaultOrder' => ['kpi_percen_result' => SORT_DESC, 'kpi_score' => SORT_DESC]]]);
$this->load($params);
if (!$this->validate()) {
// uncomment the following line if you do not want to return any records when validation fails
// $query->where('0=1');
return $dataProvider;
}
$query->andFilterWhere(['kpi_id' => $this->kpi_id, 'kpi_result' => $this->kpi_result, 'kpi_target' => $this->kpi_target, 'kpi_percen_result' => $this->kpi_percen_result, 'kpi_score' => $this->kpi_score]);
$query->andFilterWhere(['like', 'kpi_id', $this->q])->andWhere('cmbis_area_hosp.Amp LIKE "' . $this->hcode . '%" ')->andWhere('pop_group LIKE "' . $this->kpi_miss . '%" ')->groupBy(['cmbis_kpi_result_hcode.hcode']);
return $dataProvider;
}
use yii\widgets\Pjax;
use app\models\CmbisKpiResult;
//use app\models\Chospital;
use app\models\Campur;
use yii\helpers\ArrayHelper;
use yii\data\SqlDataProvider;
use yii\helpers\Url;
use yii\data\ActiveDataProvider;
/* @var $this yii\web\View */
/* @var $searchModel app\models\CmbisKpiResultSearch */
/* @var $dataProvider yii\data\ActiveDataProvider */
$this->title = 'ผลงานตามตัวชี้วัด';
$this->params['breadcrumbs'][] = $this->title;
$kpi_id = $searchModel['kpi_id'];
if (!$searchModel['kpi_id']) {
$dataProvider = new ActiveDataProvider(['query' => CmbisKpiResult::find()->where(['kpi_id' => NULL]), 'sort' => ['defaultOrder' => ['kpi_id' => SORT_ASC, 'hcode' => SORT_DESC]]]);
}
/*$dataProvider = new SqlDataProvider([
'sql' => 'SELECT * , avg(r.kpi_score) as avg_score '.
'FROM cmbis_kpi_results r '.
'INNER JOIN campur a ON a.ampurcodefull=SUBSTR(r.villcode,1,4) '.
'INNER JOIN chospital c ON c.hoscode=r.hcode '.
'INNER JOIN cmbis_kpi k ON k.kpi_id=r.kpi_id '.
'WHERE r.kpi_id =:kpi_id '.
'GROUP BY hcode '.
'ORDER BY avg_score DESC ',
'pagination' => [
'pageSize' => 50,
//'page' => 'page',
//'pageSizeLimit' => [1,20],
//'pageSizeParam' => 'per_page',
/**
* Finds the CmbisKpiResult model based on its primary key value.
* If the model is not found, a 404 HTTP exception will be thrown.
* @param integer $kpi_id
* @param string $kpi_b_year
* @param string $hcode
* @param string $villcode
* @return CmbisKpiResult the loaded model
* @throws NotFoundHttpException if the model cannot be found
*/
protected function findModel($kpi_id, $kpi_b_year, $hcode, $villcode)
{
if (($model = CmbisKpiResult::findOne(['kpi_id' => $kpi_id, 'kpi_b_year' => $kpi_b_year, 'hcode' => $hcode, 'villcode' => $villcode])) !== null) {
return $model;
} else {
throw new NotFoundHttpException('The requested page does not exist.');
}
}
'query' =>$query,
]);
if (!empty($dataProvider)) {
echo "OK!!";
}
GridView::widget([
'dataProvider' => $dataProvider,
'columns' => [
'kpi_id',
'kpi_b_year',
'hcode',
'villcode',
//['class' => 'yii\grid\ActionColumn'],
],
]);*/
$result = CmbisKpiResult::find()->where(['kpi_id' => $kpi_id])->all();
?>
<table border="2" width="100%">
<tr align="center" heigth="50px"><td>รหัสตัวชี้วัด</td><td>ปี</td><td>รหัสสถานบริการ</td><td>ผลงาน</td></tr>
<?php
foreach ($result as $rows) {
echo "<tr><td>" . $rows->kpi_id . "</td>";
echo "<td>" . $rows->kpi_b_year . "</td>";
echo "<td>" . $rows->hcode . "</td>";
echo "<td>" . $rows->kpi_percen_result . "</td></tr>";
}
?>
</table>
</div>
<?php
}
public function getAvgScoreHosp()
{
return $this->hasMany(CmbisKpiResult::className(), ['hcode' => 'hcode'])->average('kpi_score');
}