/** * Creates data provider instance with search query applied * * @param array $params * * @return ActiveDataProvider */ public function search($params) { $query = FarmerEducation::find(); // add conditions that should always apply here $dataProvider = new ActiveDataProvider(['query' => $query]); $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; } // grid filtering conditions $query->andFilterWhere(['id' => $this->id, 'education_id' => $this->education_id, 'state_id' => $this->state_id, 'year' => $this->year, 'quarter' => $this->quarter, 'param' => $this->param, 'created_at' => $this->created_at, 'created_by' => $this->created_by, 'updated_at' => $this->updated_at, 'updated_by' => $this->updated_by]); $query->andFilterWhere(['like', 'note', $this->note]); return $dataProvider; }
<?php foreach ($educations as $education) { echo "<tr>"; echo "<td>" . ++$idx_education . "</td>"; echo "<td>" . $education->name . "</td>"; $where['education_id'] = $education->id; if (!empty($years)) { $where['year'] = $years; } if (!empty($quarters)) { $where['quarter'] = $quarters; } if (!empty($stateIds)) { $where['state_id'] = $stateIds; } $farmerEducation = \app\models\FarmerEducation::find()->select("\n sum(param) as sparam,\n group_concat(note) as gcnote,\n count(state_id) as cstate,\n ")->where($where)->asArray()->one(); echo "<td>"; echo $farmerEducation['sparam']; echo "</td>"; echo "<td>" . $education->unit . "</td>"; echo "<td>"; echo $farmerEducation['gcnote']; echo "</td>"; echo "<td>"; $data = number_format($farmerEducation['cstate'] / $stateCount * 100, 2); echo $data . '%'; echo "</td>"; echo "</tr>"; } ?> </table>
/** * Finds the FarmerEducation model based on its primary key value. * If the model is not found, a 404 HTTP exception will be thrown. * @param integer $id * @return FarmerEducation the loaded model * @throws NotFoundHttpException if the model cannot be found */ protected function findModel($id) { if (($model = FarmerEducation::findOne($id)) !== null) { return $model; } else { throw new NotFoundHttpException('The requested page does not exist.'); } }
public function actionExportExcel() { //$searchModel = new SecuritasSearch(); //$dataProvider = $searchModel->search(Yii::$app->request->queryParams); $session = Yii::$app->session; $renderFarmerEducationReport = $session->get('renderFarmerEducationReport'); $objReader = \PHPExcel_IOFactory::createReader('Excel2007'); $template = Yii::getAlias('@app/views/' . $this->id) . '/_export.xlsx'; $objPHPExcel = $objReader->load($template); $activeSheet = $objPHPExcel->getActiveSheet(); $border_style = ['borders' => ['allborders' => ['style' => \PHPExcel_Style_Border::BORDER_THIN, 'color' => ['argb' => '000000']]]]; $text_center = ['horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER]; $font_bold9 = ['font' => ['bold' => true, 'color' => ['rgb' => '000000'], 'size' => 9, 'name' => 'Calibri']]; $font_bold8 = ['font' => ['bold' => true, 'color' => ['rgb' => '000000'], 'size' => 8, 'name' => 'Calibri']]; $bg_gray = ['fill' => ['type' => \PHPExcel_Style_Fill::FILL_SOLID, 'color' => ['rgb' => 'dddddd']]]; $activeSheet->setCellValue('G2', 'print at ' . date('d-M-Y H:i:s')); $years = 'Semua'; if (!empty($renderFarmerEducationReport['years'])) { $years = implode(', ', $renderFarmerEducationReport['years']); } $activeSheet->setCellValue('C3', $years); $quarters = 'Semua'; if (!empty($renderFarmerEducationReport['quarters'])) { $quarters = implode(', ', $renderFarmerEducationReport['quarters']); } $activeSheet->setCellValue('C4', $quarters); $states = 'Semua'; if (!empty($renderFarmerEducationReport['states'])) { $states = implode(', ', $renderFarmerEducationReport['states']); } $activeSheet->setCellValue('C5', $states); $stateCount = $renderFarmerEducationReport['stateCount']; $startRow = 9; //$year = $educations = \app\models\Education::find()->all(); $idx_education = 0; $row = $startRow; $starRowTable = $row; $activeSheet->setCellValue('B' . $row, 'NO'); $activeSheet->setCellValue('C' . $row, 'LEVEL'); $activeSheet->setCellValue('D' . $row, 'JUMLAH'); $activeSheet->setCellValue('E' . $row, 'SATUAN'); $activeSheet->setCellValue('F' . $row, 'KETERANGAN'); $activeSheet->setCellValue('G' . $row, 'DATA MASUK'); $activeSheet->getStyle('B' . $row . ':G' . $row)->applyFromArray($bg_gray); foreach ($educations as $education) { $row++; $activeSheet->setCellValue('B' . $row, ++$idx_education); $activeSheet->setCellValue('C' . $row, $education->name); $where['education_id'] = $education->id; if (!empty($renderFarmerEducationReport['years'])) { $where['year'] = $renderFarmerEducationReport['years']; } if (!empty($renderFarmerEducationReport['quarters'])) { $where['quarter'] = $renderFarmerEducationReport['quarters']; } if (!empty($renderFarmerEducationReport['stateIds'])) { $where['state_id'] = $renderFarmerEducationReport['stateIds']; } $farmerEducation = \app\models\FarmerEducation::find()->select("\n sum(param) as sparam,\n group_concat(note) as gcnote,\n count(state_id) as cstate,\n ")->where($where)->asArray()->one(); $activeSheet->setCellValue('D' . $row, $farmerEducation['sparam']); $activeSheet->setCellValue('E' . $row, $education->unit); $activeSheet->setCellValue('F' . $row, $farmerEducation['gcnote']); $data = number_format($farmerEducation['cstate'] / $stateCount * 100, 2); $activeSheet->setCellValue('G' . $row, $data . '%'); } $activeSheet->getStyle("B" . $starRowTable . ":G" . $row)->applyFromArray($border_style); $activeSheet->getStyle("B" . $starRowTable . ":G" . $row)->getAlignment()->applyFromArray($text_center); $activeSheet->getStyle("F" . ($starRowTable + 1) . ":F" . $row)->getAlignment()->setWrapText(true); header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="' . $this->id . '_' . date('YmdHis') . '.xlsx"'); header('Cache-Control: max-age=0'); $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007"); $objWriter->save('php://output'); exit; }
/** * @return \yii\db\ActiveQuery */ public function getFarmerEducations() { return $this->hasMany(FarmerEducation::className(), ['education_id' => 'id']); }