public function actionScoreampur()
{
$query = CmbisKpiResultAmp::find()->groupBy(['amp'])->orderBy('avg(kpi_score) DESC');
$dataProvider = new ActiveDataProvider(['query' => $query, 'pagination' => ['pageSize' => 25]]);
$dataProvider->setSort(['attributes' => ['avgScore' => ['asc' => ['avg(kpi_score)' => SORT_ASC], 'desc' => ['avg(kpi_score)' => SORT_DESC], 'default' => SORT_ASC, 'label' => 'avg_score']]]);
return $this->render('scoreampur', ['dataProvider' => $dataProvider]);
}
/**
* Creates data provider instance with search query applied
*
* @param array $params
*
* @return ActiveDataProvider
*/
public function search($params)
{
$query = CmbisKpiResultAmp::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]], 'pagination' => ['pageSize' => 25]]);
$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]);
// andFilterWhere(['like', 'kpi_b_year', $this->kpi_b_year])
// ->andFilterWhere(['like', 'hcode', $this->hcode])
//->andFilterWhere(['like', 'villcode', $this->villcode])
// ->andWhere('cmbis_area_hosp.Amp LIKE "' . $this->hcode . '%" ')
// ->andWhere('pop_group LIKE "' . $this->kpi_miss . '%" ')
//->groupBy(['amp',])
// ->orderBy('avg(kpi_score) DESC');
return $dataProvider;
}
public function getAvgScore()
{
return $this->hasMany(CmbisKpiResultAmp::className(), ['amp' => 'amp'])->average('kpi_score');
}
</div><!-- /.info-box-content -->
</div><!-- /.info-box -->
<!-- <div class="jumbotron list-group-item list-group-item-success">
<h1>ยินดีต้อนรับ</h1>
<p class="lead">ระบบสารสนเทศเปรียบเทียบเพื่อการประเมินประสิทธิผลการปฏิบัติงาน</p>
<p><?php
echo $this->title;
?>
</p>
</div> -->
</div>
<?php
$dataProvider = new ActiveDataProvider(['query' => CmbisKpiResultAmp::find()->select('*,avg(kpi_score) as avgScore')->groupBy('amp')->orderBy('avgScore DESC'), 'sort' => ['defaultOrder' => ['kpi_score' => SORT_DESC]], 'pagination' => ['pageSize' => 5]]);
?>
<?php
$sql = "SELECT *, round(avg(kpi_score),2) as avgScore FROM cmbis_kpi_result_amp INNER JOIN cmbis_area_hosp ON cmbis_area_hosp.AMP=cmbis_kpi_result_amp.amp GROUP BY cmbis_kpi_result_amp.amp ORDER BY avgScore DESC LIMIT 5";
$rawData = Yii::$app->db->createCommand($sql)->queryAll();
$main_data = [];
$main_name = [];
foreach ($rawData as $data) {
$main_name[] = $data['Amp_Des'];
$main_data[] = [$data['avgScore'] * 1];
}
$score = json_encode($main_data);
$name = json_encode($main_name);
//echo $score."<br/>";
//echo $name;
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;
?>
<?php
yii\widgets\Pjax::begin(['id' => 'grid-kpiresult-pjax', 'timeout' => 5000]);
?>
<?php
echo $this->render('_searchkpiampur', ['model' => $searchModel]);
?>
<br>
<?php
if (!$searchModel['kpi_id']) {
$dataProvider = new ActiveDataProvider(['query' => CmbisKpiResultAmp::find()->where(['kpi_id' => NULL]), 'sort' => ['defaultOrder' => []]]);
}
//print_r($dataProvider);
?>
<?php
echo GridView::widget(['id' => 'grid-kpiresult', 'dataProvider' => $dataProvider, 'tableOptions' => ['class' => 'table table-bordered table-striped table-hover'], 'panel' => ['type' => GridView::TYPE_SUCCESS, 'heading' => 'เปรียบเทียบระดับอำเภอ'], 'columns' => [['class' => 'kartik\\grid\\SerialColumn'], 'amp', 'amphur.Amp_Des', 'kpi_target', 'kpi_result', 'kpi_percen_result', 'kpi_score'], 'pager' => ['firstPageLabel' => 'หน้าแรก', 'lastPageLabel' => 'หน้าสุดท้าย']]);
yii\widgets\Pjax::end();
?>
