/**
* 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']);
}
