Пример #1
0
 public function actionExcel()
 {
     //Validasi Raport gan!
     $bulan = date('m');
     if ($bulan < 7) {
         $smt = 2;
         //jika th 2(1-6) maka tahun-1/tahun-2 2012/2013
         $th_ajar = date('Y') - 1 . '/' . date('Y');
     } elseif ($bulan < 13 && $bulan > 6) {
         $smt = 1;
         //jika th 1(7-12) maka tahun/tahun+1 2012/2013
         $th_ajar = date('Y') . '/' . (date('Y') + 1);
     }
     Yii::import('ext.phpexcel.Classes.PHPExcel', true);
     $objPHPExcel = new PHPExcel();
     // Set document properties
     $objPHPExcel->getProperties()->setCreator("Nur Rochim")->setLastModifiedBy("Nur Rochim")->setTitle("Raport")->setSubject("Raport I-SIMS Penilaian")->setDescription("Raport I-SIMS Penilaian")->setKeywords("Raport I-SIMS Penilaian")->setCategory("Raport I-SIMS Penilaian");
     $styleBold = array('font' => array('bold' => true), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => 'FF9900')));
     $styleBackground = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => 'FF9900')), 'font' => array('name' => 'Arial', 'size' => 12, 'bold' => true));
     $styleArray = array('font' => array('bold' => true), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT, 'wrap' => PHPExcel_Style_Alignment::VERTICAL_TOP), 'borders' => array('horizontal' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
     $tengah = array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY, 'wrap' => PHPExcel_Style_Alignment::VERTICAL_CENTER), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
     $tebalgaristengah = array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'wrap' => PHPExcel_Style_Alignment::VERTICAL_CENTER), 'borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)), 'font' => array('bold' => true), 'fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => 'FF9900')));
     $tebalgariskanan = array('alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT, 'wrap' => PHPExcel_Style_Alignment::VERTICAL_CENTER), 'font' => array('bold' => true));
     //Di aliaskan
     $ocim = $objPHPExcel->setActiveSheetIndex();
     $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
     $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
     //Set Judul di merge
     $ocim->setCellValue("A1", 'LAPORAN HASIL BELAJAR SISWA')->getStyle("A1:E1")->applyFromArray($styleBackground);
     $ocim->mergeCells("A1:E1");
     $ocim->getStyle("A1:E1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
     $ocim->getRowDimension(1)->setRowHeight(22);
     $ocim->getRowDimension(2)->setRowHeight(20);
     //Set Widthnya gan!
     /*$ocim->getColumnDimension("A")->setWidth(5);
     		$ocim->getColumnDimension("B")->setWidth(40);
     		$ocim->getColumnDimension("C")->setWidth(10);
     		$ocim->getColumnDimension("D")->setWidth(10);
     		$ocim->getColumnDimension("E")->setWidth(10);*/
     $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
     $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
     $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
     $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
     $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
     //Nilai Raport Mulai
     $nilai = Nilai::model()->findAll(array('condition' => "nis='" . $_GET['id'] . "' and th_ajar='" . $th_ajar . "' and semester='" . $smt . "'", 'order' => 'kode_mapel ASC'));
     $arraynilai = array();
     foreach ($nilai as $nil) {
         $arraynilai[] = array('nama' => $nil->nis0->nama_lengkap, 'kode_mapel' => $nil->kodeMapel->kodeMapel->mapel, 'kkm' => $nil->kodeMapel->kodeMapel->kkm, 'na' => $nil->na);
     }
     //Header info siswa mulai
     $objPHPExcel->getActiveSheet()->getStyle('A3:E6')->applyFromArray($styleArray);
     $ocim->setCellValue("A3", "Nama : " . $nil->nis0->nama_lengkap);
     $ocim->mergeCells("A3:B3");
     $ocim->setCellValue("A4", "Nomor Induk :" . $nil->nis);
     $ocim->mergeCells("A4:B4");
     $ocim->setCellValue("A5", "Nama Sekolah : " . Yii::app()->params['title']);
     $ocim->mergeCells("A5:B5");
     $ocim->setCellValue("A6", "Alamat Sekolah : " . Yii::app()->params['subtitle']);
     $ocim->mergeCells("A6:E7");
     $ocim->getStyle("A6:E7")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
     $ocim->getStyle("A6:E7")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
     $ocim->setCellValue("D3", "Kelas : " . $nil->kelas . " " . $nil->lokal);
     $ocim->mergeCells("D3:E3");
     $ocim->setCellValue("D4", "Semester : " . $nil->semester);
     $ocim->mergeCells("D4:E4");
     $ocim->setCellValue("D5", "Tahun Pelajaran : " . $nil->th_ajar);
     $ocim->mergeCells("D5:E5");
     //Header info siswa selesai
     $ocim->setCellValue("A9", "No")->setCellValue("B9", "Mata Pelajaran")->setCellValue("C9", "KKM*")->setCellValue("D9", "Nilai")->setCellValue("E9", "NRK**")->getStyle("A9:E9")->applyFromArray($styleBold);
     foreach ($arraynilai as $k => $u) {
         $ocim->setCellValue('A' . ($k + 10), $k + 1)->setCellValue('B' . ($k + 10), $u['kode_mapel'])->setCellValue('C' . ($k + 10), $u['kkm'])->setCellValue('D' . ($k + 10), $u['na'])->setCellValue('E' . ($k + 10), $u['kkm']);
         //cetak kkm
     }
     $akhir = Yii::app()->db->createCommand("select count(nis) from nilai where nis='" . $_GET['id'] . "' and th_ajar='" . $th_ajar . "' and semester='" . $smt . "';")->queryScalar();
     //Jumlah nilai akhir
     $ocim->setCellValue('A' . ($akhir + 12), "Jumlah Nilai Prestasi Hasil Belajar ")->mergeCells("A" . ($akhir + 12) . ":B" . ($akhir + 12));
     $objPHPExcel->getActiveSheet()->getCell('C' . ($akhir + 12))->setValue('=SUM(D10:D23)');
     //Jumlah Rata-rata Hasil Belajar:
     $ocim->setCellValue('A' . ($akhir + 13), "Rata-rata Hasil Belajar ")->mergeCells("A" . ($akhir + 13) . ":B" . ($akhir + 13));
     $objPHPExcel->getActiveSheet()->getCell('C' . ($akhir + 13))->setValue("=SUM(D10:D23)/" . $akhir . "");
     foreach ($nilai as $i => $ii) {
         $model = new Nilai();
         $rata2 = Yii::app()->db->createCommand("select sum(na)/count(na) from nilai where kode_mapel='" . $ii['kode_mapel'] . "' and th_ajar='" . $th_ajar . "' and semester='" . $smt . "';")->queryScalar();
         echo $rata2 . "<br>";
         $ocim->setCellValue('E' . ($i + 10), $rata2);
     }
     //Nilai Raport Selesai
     //Berikan Border All
     $semua = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
     $ocim->getStyle('A9:E' . ($akhir + 10))->applyFromArray($semua);
     //Ekstra Mulai
     $ekstra = Trekstra::model()->findAll(array('condition' => "nis='" . $_GET['id'] . "' and th_ajar='" . $th_ajar . "' and semester='" . $smt . "'"));
     $arrayekstra = array();
     foreach ($ekstra as $eks) {
         $arrayekstra[] = array('nama_ekstra' => $eks->idEkstra->nama_ekstra, 'nilai_ekstra' => $eks->nilai_ekstra);
     }
     $ocim->setCellValue("A" . ($akhir + 15), "No")->setCellValue("B" . ($akhir + 15), "KEGIATAN EKSTRAKURIKULER")->setCellValue("E" . ($akhir + 15), "NILAI")->getStyle("A" . ($akhir + 15) . ":E" . ($akhir + 15))->applyFromArray($styleBold);
     $ocim->mergeCells("B" . ($akhir + 15) . ":D" . ($akhir + 15));
     foreach ($arrayekstra as $oc => $im) {
         $ocim->setCellValue('A' . ($akhir + 16 + $oc), $oc + 1)->setCellValue('B' . ($akhir + 16 + $oc), $im['nama_ekstra'])->setCellValue('E' . ($akhir + 16 + $oc), $im['nilai_ekstra'])->mergeCells("B" . ($akhir + 16 + $oc) . ":D" . ($akhir + 16 + $oc));
     }
     $ocim->getStyle('A' . ($akhir + 15) . ':E' . ($akhir + 16 + $oc))->applyFromArray($semua);
     //Ekstra Selesai
     //Pribadi Mulai
     $akhir2 = Yii::app()->db->createCommand("select count(nis) from tr_ekstra where nis='" . $_GET['id'] . "' and th_ajar='" . $th_ajar . "' and semester='" . $smt . "';")->queryScalar();
     $pribadi = Trpribadi::model()->findAll(array('condition' => "nis='" . $_GET['id'] . "' and th_ajar='" . $th_ajar . "' and semester='" . $smt . "' and id_pribadi not in (4,5)"));
     $arraypribadi = array();
     foreach ($pribadi as $pri) {
         $arraypribadi[] = array('nama_pribadi' => $pri->idPribadi->nama_pribadi, 'nilai_pribadi' => $pri->nilai_pribadi, 'catatan' => $pri->catatan);
     }
     $ocim->setCellValue("A" . ($akhir + 17 + $akhir2), "No")->setCellValue("B" . ($akhir + 17 + $akhir2), "KEPRIBADIAN")->setCellValue("C" . ($akhir + 17 + $akhir2), "NILAI")->getStyle("A" . ($akhir + 17 + $akhir2) . ":C" . ($akhir + 17 + $akhir2))->applyFromArray($styleBold);
     foreach ($arraypribadi as $oc => $im) {
         $ocim->setCellValue('A' . ($akhir + 18 + $akhir2 + $oc), $oc + 1)->setCellValue('B' . ($akhir + 18 + $akhir2 + $oc), $im['nama_pribadi'])->setCellValue('C' . ($akhir + 18 + $akhir2 + $oc), $im['nilai_pribadi']);
         //cetak kkm
     }
     $ocim->getStyle('A' . ($akhir + 17 + $akhir2) . ':E' . ($akhir + 18 + $akhir2 + $oc))->applyFromArray($semua);
     //Pribadi Selesai
     //Keterangan KKM dan NRK
     $ocim->setCellValue('A' . ($akhir + 20 + $akhir2 + $oc), "*) Kriteria Ketuntasan Minimal")->setCellValue('A' . ($akhir + 21 + $akhir2 + $oc), "**) Nilai Rata-Rata Kelas")->mergeCells("A" . ($akhir + 20 + $akhir2 + $oc) . ":E" . ($akhir + 20 + $akhir2 + $oc))->mergeCells("A" . ($akhir + 21 + $akhir2 + $oc) . ":E" . ($akhir + 21 + $akhir2 + $oc));
     $ocim->getStyle("A" . ($akhir + 20 + $akhir2 + $oc) . ":E" . ($akhir + 21 + $akhir2 + $oc))->getFont()->setSize(8);
     //Presensi Mulai
     $akhir2 = Yii::app()->db->createCommand("select count(nis) from tr_ekstra where nis='" . $_GET['id'] . "' and th_ajar='" . $th_ajar . "' and semester='" . $smt . "';")->queryScalar();
     $pribadi = Trpribadi::model()->findAll(array('condition' => "nis='" . $_GET['id'] . "' and th_ajar='" . $th_ajar . "' and semester='" . $smt . "' and id_pribadi not in (4,5)"));
     $arraypribadi = array();
     foreach ($pribadi as $pri) {
         $arraypribadi[] = array('nama_pribadi' => $pri->idPribadi->nama_pribadi, 'nilai_pribadi' => $pri->nilai_pribadi, 'catatan' => $pri->catatan);
     }
     $ocim->setCellValue("D" . ($akhir + 17 + $akhir2), "KETIDAKHADIRAN")->setCellValue("E" . ($akhir + 17 + $akhir2), "HARI")->getStyle("D" . ($akhir + 17 + $akhir2) . ":E" . ($akhir + 17 + $akhir2))->applyFromArray($styleBold);
     $sakit = Presensi::model()->countByAttributes(array('nis' => $_GET['id'], 'status' => 'S', 'th_ajar' => $th_ajar, 'semester' => (string) $smt));
     $izin = Presensi::model()->countByAttributes(array('nis' => $_GET['id'], 'status' => 'I', 'th_ajar' => $th_ajar, 'semester' => (string) $smt));
     $alpha = Presensi::model()->countByAttributes(array('nis' => $_GET['id'], 'status' => 'A', 'th_ajar' => $th_ajar, 'semester' => (string) $smt));
     $ocim->setCellValue("D" . ($akhir + 18 + $akhir2), "Sakit")->setCellValue("D" . ($akhir + 19 + $akhir2), "Izin")->setCellValue("D" . ($akhir + 20 + $akhir2), "Alpha")->setCellValue("E" . ($akhir + 18 + $akhir2), $sakit)->setCellValue("E" . ($akhir + 19 + $akhir2), $izin)->setCellValue("E" . ($akhir + 20 + $akhir2), $alpha);
     //Presensi Selesai
     //Tentang Mulai
     $objPHPExcel->getActiveSheet()->getStyle('G4:L9')->applyFromArray($tengah);
     $ocim->setCellValue("G3", 'CATATAN TENTANG PENGEMBANGAN DIRI')->mergeCells("G3:L3")->getStyle("G3:L3")->applyFromArray($tebalgaristengah);
     $pribadi2 = Trpribadi::model()->findAll(array('condition' => "nis='" . $_GET['id'] . "' and th_ajar='" . $th_ajar . "' and semester='" . $smt . "' and id_pribadi=5"));
     $arraypribadi2 = array();
     foreach ($pribadi2 as $pri2) {
         $arraypribadi2[] = array($ocim->setCellValue("G4", $pri2->catatan), $ocim->mergeCells("G4:L9"));
     }
     //Tentang selesai
     //Catatan
     $objPHPExcel->getActiveSheet()->getStyle('G12:L17')->applyFromArray($tengah);
     $ocim->setCellValue("G11", 'CATATAN')->mergeCells("G11:L11")->getStyle("G11:L11")->applyFromArray($tebalgaristengah);
     $pribadi2 = Trpribadi::model()->findAll(array('condition' => "nis='" . $_GET['id'] . "' and th_ajar='" . $th_ajar . "' and semester='" . $smt . "' and id_pribadi=4"));
     $arraypribadi2 = array();
     foreach ($pribadi2 as $pri2) {
         $arraypribadi2[] = array($ocim->setCellValue("G12", $pri2->catatan), $ocim->mergeCells("G12:L17"));
     }
     //Catatan selesai
     //Tanggal
     $tanggalbawah = date('d F Y');
     $ocim->setCellValue("G19", 'Karanganyar, ' . $tanggalbawah)->mergeCells("G19:L19")->getStyle("G19:L19")->applyFromArray($tebalgariskanan);
     //TTD
     $raport = Nilai::model()->find(array('condition' => "nis='" . $_GET['id'] . "'"));
     $ocim->setCellValue("K20", 'Wali Kelas')->mergeCells("K20:L20")->getStyle("G20:L20")->applyFromArray($tebalgariskanan);
     $ocim->setCellValue("G20", 'Orang Tua/Wali')->mergeCells("G20:H20")->getStyle("G20:H20")->applyFromArray($tebalgariskanan);
     $ocim->setCellValue("K24", $raport->kodeGuru->nama_guru)->mergeCells("K24:L24")->getStyle("G24:L24")->applyFromArray($tebalgariskanan);
     //NIP
     $ocim->setCellValue("K25", "NIP." . $raport->kodeGuru->nip)->mergeCells("K25:L25")->getStyle("G25:L25")->applyFromArray($tebalgariskanan);
     $ocim->setCellValue("G24", '(_____________)')->mergeCells("G24:H24")->getStyle("G24:H24")->applyFromArray($tebalgariskanan);
     $objPHPExcel->setActiveSheetIndex(0);
     ob_end_clean();
     ob_start();
     // Redirect output to a client’s web browser (Excel2007)
     header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
     header('Content-Disposition: attachment;filename="ocim.xlsx"');
     header('Cache-Control: max-age=0');
     $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
     $objWriter->save('php://output');
 }
Пример #2
0
 /**
  * Returns the data model based on the primary key given in the GET variable.
  * If the data model is not found, an HTTP exception will be raised.
  * @param integer the ID of the model to be loaded
  */
 public function loadModel($id)
 {
     $model = Trekstra::model()->findByPk($id);
     if ($model === null) {
         throw new CHttpException(404, 'The requested page does not exist.');
     }
     return $model;
 }