public function actionGetSaldo()
{
if (!Yii::app()->request->isAjaxRequest) {
return;
}
if (isset($_POST) && !empty($_POST)) {
$bulan = $_POST['bulan'];
$tahun = $_POST['tahun'];
if ($bulan === '' || $tahun === '') {
echo CJSON::encode(array('success' => false, 'msg' => 'Bulan atau Tahun periode tidak boleh kosong.'));
Yii::app()->end();
}
// $bank_act = PePrefs::BankOnHand();
$periode = period2date($bulan, $tahun);
$start_date = $periode['start'];
$end_date = $periode['end'];
$sisa_anggaran = Pe::get_balance_before_for_bank_account($tahun . "-" . $bulan . "-1");
$total_saldo = Pe::get_total_pendapatan($start_date, $end_date);
$saldo_skrg = $total_saldo + $sisa_anggaran;
echo CJSON::encode(array('success' => true, 'sisa' => $sisa_anggaran, 'current' => $total_saldo, 'total' => $saldo_skrg));
Yii::app()->end();
}
}
public function actionAnggaranRealisasi()
{
if (Yii::app()->request->isAjaxRequest) {
return;
}
if (isset($_POST) && !empty($_POST)) {
$format = $_POST['format'];
$bulan = $_POST['periode_bulan'];
$tahun = $_POST['periode_tahun'];
$periode = period2date($bulan, $tahun);
$start_date = $periode['start'];
$end_date = $periode['end'];
$start = 1;
$file_name = 'AnggaranRealisasi';
$worksheet_name = 'AnggaranRealisasi';
$objPHPExcel = new PHPExcel();
$this->header($objPHPExcel, $start, $worksheet_name, "LAPORAN ANGGARAN VERSUS REALISASI");
$objPHPExcel->setActiveSheetIndex(0)->mergeCells("A{$start}:G{$start}")->setCellValue("A{$start}", "PERIODE: " . date2longperiode($start_date, 'MMMM yyyy'))->getStyle("A{$start}")->getFont()->setSize(12)->setBold(true);
$objPHPExcel->getActiveSheet()->setTitle($worksheet_name);
$start++;
$start++;
$start_body = $start;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("A{$start}", "Nama Rekening")->setCellValue("B{$start}", "Anggaran")->setCellValue("C{$start}", "Realisasi")->setCellValue("D{$start}", "%")->setCellValue("E{$start}", "Surplus(Defisit)")->getStyle("A{$start}:E{$start}")->getFont()->setBold(true);
$start++;
$rows = Pah::get_chart_master_beban();
$total_anggaran = 0;
$total_realisasi = 0;
foreach ($rows as $row) {
$anggaran = Pah::get_anggaran_by_code($bulan, $tahun, $row['account_code']);
$realisasi = Pah::get_realisasi_by_code($start_date, $end_date, $row['account_code']);
if ($anggaran == 0 && $realisasi == 0) {
continue;
}
$total_anggaran += $anggaran;
$total_realisasi += $realisasi;
$selisih = $anggaran - $realisasi;
if ($anggaran != 0) {
$persen = $realisasi / $anggaran;
}
$anggaran_format = $format == 'excel' ? $anggaran : acc_format($anggaran);
$realisasi_format = $format == 'excel' ? $realisasi : acc_format($realisasi);
$selisih_format = $format == 'excel' ? $selisih : acc_format($selisih);
if ($anggaran != 0) {
$persen_format = $format == 'excel' ? $persen : percent_format($persen, 2);
} else {
$persen_format = '#DEF';
}
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("A{$start}", $row['account_name'])->setCellValue("B{$start}", $anggaran_format)->setCellValue("C{$start}", $realisasi_format)->setCellValue("D{$start}", $persen_format)->setCellValue("E{$start}", $selisih_format);
$start++;
}
$total_selisih = $total_anggaran - $total_realisasi;
$total_persen = $total_realisasi / $total_anggaran;
$total_anggaran_format = $format == 'excel' ? $total_anggaran : acc_format($total_anggaran);
$total_realisasi_format = $format == 'excel' ? $total_realisasi : acc_format($total_realisasi);
$total_selisih_format = $format == 'excel' ? $total_selisih : acc_format($total_selisih);
$total_persen_format = $format == 'excel' ? $total_persen : percent_format($total_persen, 2);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("A{$start}", 'Total')->setCellValue("B{$start}", $total_anggaran_format)->setCellValue("C{$start}", $total_realisasi_format)->setCellValue("D{$start}", $total_persen_format)->setCellValue("E{$start}", $total_selisih_format);
$start++;
$end_body = $start - 1;
$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
$objPHPExcel->setActiveSheetIndex(0)->getStyle("A{$start_body}:E{$end_body}")->applyFromArray($styleArray);
$start_row = $start_body + 1;
if ($format == 'excel') {
$objPHPExcel->setActiveSheetIndex(0)->getStyle("B{$start_row}:E{$end_body}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_ACCOUNTING);
$objPHPExcel->setActiveSheetIndex(0)->getStyle("D{$start_row}:D{$end_body}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00);
} else {
$objPHPExcel->setActiveSheetIndex(0)->getStyle("B{$start_body}:E{$end_body}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
}
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("A")->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("B")->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("C")->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("D")->setAutoSize(true);
$objPHPExcel->setActiveSheetIndex(0)->getColumnDimension("E")->setAutoSize(true);
$this->footer($objPHPExcel, $start, $file_name, $format, "Laporan Beban Aktivitas per Anak");
Yii::app()->end();
}
}
