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; }
</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(); ?>