public function actionHighChart()
{
// $dataKegiatan = Kegiatan::model()->find('id=1');
// $dataLayanan = Layanan::model()->find('id=1');
$dataLayanan = "";
$dataKegiatan = "";
$dataProgram = $dataTargetProgram = $dataSeluruh = "";
for ($i = 1; $i < 13; $i++) {
// array_push($dataLayanan, array(date('M',mktime(0,0,0,$i+1,0,0)) => DatabaseUmum::getRealisasiOnMonthFromLayanan('1',$i)));
// $dataLayanan[date('M',mktime(0,0,0,$i+1,0,0))] = DatabaseUmum::getRealisasiOnMonthFromLayanan('1',$i);
$dataKegiatan = $dataKegiatan . DatabaseUmum::getRealisasi('1', $i);
$dataLayanan = $dataLayanan . DatabaseUmum::getRealisasiOnMonthFromLayanan('1', $i);
$dataProgram = $dataProgram . DatabaseUmum::getRealisasiOnMonthFromProgram('1', $i);
$dataSeluruh = $dataSeluruh . DatabaseUmum::getRealisasiOnMonth($i);
$dataTargetProgram = $dataTargetProgram . DatabaseUmum::getTargetOnMonthFromProgram('1', $i);
if ($i + 1 != 13) {
$dataLayanan = $dataLayanan . ",";
$dataKegiatan = $dataKegiatan . ",";
$dataProgram = $dataProgram . ",";
$dataSeluruh = $dataSeluruh . ",";
$dataTargetProgram = $dataTargetProgram . ",";
}
}
// print_r($dataProgram);
// echo DatabaseUmum::getRealisasiOnMonthFromLayanan('1','1');
//
// $bulan;
// $dataKegiatan = "";
// $i = 1;
// foreach ($dataKegiatan->realisasi as $key) {
// $dataKegiatan = $dataKegiatan.$key->nominal;
// if($i+1!=13) $dataLayanan = $dataLayanan.",";
// }
// $dataLayanan = "1,2,3,4,5,6,7,8,9,0,11,12";
$this->render('HighChart', array('data' => $dataLayanan, 'layanan' => 'layanan ID 1', 'dataKegiatan' => $dataKegiatan, 'dataProgram' => $dataProgram, 'dataSeluruh' => $dataSeluruh, 'dataTargetProgram' => $dataTargetProgram));
}
public function actionDownloadV1()
{
if (isset($_POST['tahun_anggaran'])) {
$tahun_anggaran = $_POST['tahun_anggaran'];
} else {
$tahun_anggaran = '2015';
}
$dataProgram = Program::model()->findAll('status=:status AND tahun_anggaran=:tahun_anggaran', array(':status' => '1', ':tahun_anggaran' => $tahun_anggaran));
Yii::import('ext.PHPExcel.Classes.PHPExcel');
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("PPS Universitas Negeri Semarang")->setLastModifiedBy("TIM Web E-POK PPS UNNES")->setTitle("Rekap Kegiatan PPS UNNES Tahun {$tahun_anggaran}");
// SET THE HEADER DOCUMENT
for ($m = 0; $m < 12; $m++) {
// SET THE HEADER DOCUMENT
$month = date('Y');
$objPHPExcel->createSheet($m);
$objPHPExcel->setActiveSheetIndex($m)->setTitle(date('M', mktime(0, 0, 0, $m + 2, 0, 0)));
// Header Document
$objPHPExcel->setActiveSheetIndex($m)->mergeCells('A1:L1')->mergeCells('A2:L2')->mergeCells('A3:L3')->mergeCells('A4:L4')->setCellValue('A1', 'LAPORAN REALISASI ALOKASI PENGGUNAAN ANGGARAN')->setCellValue('A2', 'PROGRAM PASCASARJANA UNNES')->setCellValue('A3', 'TAHUN ANGGARAN 2015')->setCellValue('D2', 'world!');
$objPHPExcel->getActiveSheet()->getStyle('A1:L6')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A1:L6')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A1:L6')->getAlignment()->setWrapText(true);
//Header Table
$objPHPExcel->setActiveSheetIndex($m)->mergeCells('A6:A7')->mergeCells('B6:B7')->mergeCells('C6:G6')->mergeCells('H6:H7')->mergeCells('I6:I7')->mergeCells('J6:J7')->mergeCells('K6:K7')->mergeCells('L6:L7')->setCellValue('A6', 'Kode')->setCellValue('B6', 'Uraian Unit/Program/Kegiatan/Output/Akun Belanja/Detil Belanja')->setCellValue('C6', 'TA 2015')->setCellValue('C7', 'Volume')->setCellValue('D7', 'Satuan')->setCellValue('E7', 'Harga Satuan')->setCellValue('F7', 'Target')->setCellValue('G7', 'SD')->setCellValue('H6', 'Bulan')->setCellValue('I6', 'Realisasi s.d Bulan Ini')->setCellValue('J6', 'Saldo')->setCellValue('K6', '%')->setCellValue('L6', 'Penanggung Jawab');
$i = 9;
foreach ($dataProgram as $key) {
$realisasiProgram = DatabaseUmum::getRealisasiOnMonthFromProgram($key->id, $m);
$realisasiProgramUntilMonth = DatabaseUmum::getRealisasiUntilMonthFromProgram($key->id, $m);
$objPHPExcel->getActiveSheet()->setCellValue("A{$i}", $key->kode_program)->setCellValue("B{$i}", $key->nama_program)->setCellValue("C{$i}", "-")->setCellValue("D{$i}", "-")->setCellValue("E{$i}", "-")->setCellValue("F{$i}", $key->target)->setCellValue("G{$i}", "-")->setCellValue("H{$i}", $realisasiProgram)->setCellValue("I{$i}", $realisasiProgramUntilMonth)->setCellValue("J{$i}", "=F{$i}-I{$i}")->setCellValue("K{$i}", "=I{$i}/F{$i} * 100");
$i++;
foreach ($key->layanan as $key2) {
$realisasiLayanan = DatabaseUmum::getRealisasiOnMonthFromLayanan($key->id, $m);
$realisasiLayananUntilMonth = DatabaseUmum::getRealisasiUntilMonthFromLayanan($key->id, $m);
$objPHPExcel->getActiveSheet()->setCellValue("A{$i}", $key2->kode_layanan)->setCellValue("B{$i}", $key2->nama_layanan)->setCellValue("C{$i}", "-")->setCellValue("D{$i}", "-")->setCellValue("E{$i}", "-")->setCellValue("F{$i}", $key2->target)->setCellValue("G{$i}", "-")->setCellValue("H{$i}", $realisasiLayanan)->setCellValue("I{$i}", $realisasiLayananUntilMonth)->setCellValue("K{$i}", "%")->setCellValue("J{$i}", "=F{$i}-I{$i}")->setCellValue("K{$i}", "=I{$i}/F{$i} * 100");
$i++;
foreach ($key2->kegiatan as $key3) {
$realisasi = DatabaseUmum::getRealisasi($key3->id, $m);
$realisasiSampaiBulan = DatabaseUmum::getSumOfRealisasi($key3->id, $m);
$objPHPExcel->getActiveSheet()->setCellValue("A{$i}", $key3->kode_kegiatan)->setCellValue("B{$i}", $key3->nama_kegiatan)->setCellValue("C{$i}", $key3->volume)->setCellValue("D{$i}", $key3->satuan)->setCellValue("E{$i}", $key3->harga_satuan)->setCellValue("F{$i}", $key3->target)->setCellValue("G{$i}", $key3->SumberDana->nama)->setCellValue("H{$i}", $realisasi)->setCellValue("I{$i}", $realisasiSampaiBulan)->setCellValue("J{$i}", "=F{$i}-I{$i}")->setCellValue("K{$i}", "=I{$i}/F{$i} * 100")->setCellValue("L{$i}", $key3->PenanggungJawab->nama);
// $objPHPExcel->getActiveSheet()->getStyle("E$i")->getNumberFormat()->setFormatCode('_-Rp* #.##0_-;-Rp* #.##0_-;_-Rp* "-"_-;_-@_-');
$i++;
}
}
}
$objPHPExcel->getActiveSheet()->getStyle('A6:L7')->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '236A04')), 'font' => array('bold' => true, 'color' => array('rgb' => 'FFFFFF'))));
// normalize all column width
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth("9.86");
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth("53.57");
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth("12.75");
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth("7.14");
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth("9.86");
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth("12.71");
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth("14.71");
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth("16");
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth("14.71");
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth("8.43");
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth("24.29");
}
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="test.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
}