public function actionLabaRugiMahkotrans()
{
if (Yii::app()->request->isAjaxRequest) {
return;
}
if (isset($_POST) && !empty($_POST)) {
$format = $_POST['format'];
$is_excel = $format == 'excel';
$start_date = $_POST['trans_date_mulai'];
$end_date = $_POST['trans_date_sampai'];
$start = 1;
$file_name = 'LabaRugiMahkotrans';
$worksheet_name = 'Laba Rugi Mahkotrans';
$objPHPExcel = new PHPExcel();
$this->header($objPHPExcel, $start, $worksheet_name, "LABA RUGI");
$objPHPExcel->setActiveSheetIndex(0)->mergeCells("A{$start}:C{$start}")->setCellValue("A{$start}", "Dari tanggal " . sql2long_date($start_date) . " sampai tanggal " . sql2long_date($end_date))->getStyle("A{$start}")->getFont()->setSize(12)->setBold(true);
$objPHPExcel->getActiveSheet()->setTitle($worksheet_name);
$start++;
$start++;
$start_body = $start;
// 4100 Pendapatan Sewa
$pen_sewa = $this->loadModel('4100', 'MtChartMaster');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("A{$start}", $pen_sewa->account_name);
$tot_pen_sewa = -Mt::get_sum_account_trans($pen_sewa->account_code, NULL, $start_date, $end_date);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("B{$start}", $is_excel ? $tot_pen_sewa : number_format($tot_pen_sewa));
$start++;
// 4200 Potongan atau Diskon
$pen_diskon = $this->loadModel('4200', 'MtChartMaster');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("A{$start}", $pen_diskon->account_name);
$tot_pen_diskon = -Mt::get_sum_account_trans($pen_diskon->account_code, NULL, $start_date, $end_date);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("B{$start}", $is_excel ? $tot_pen_diskon : number_format($tot_pen_diskon));
$start++;
// Netto
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("A{$start}", $pen_sewa->account_name . ' (Netto)');
$tot_pen_sewa_netto = $tot_pen_sewa - $tot_pen_diskon;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("C{$start}", $is_excel ? $tot_pen_sewa_netto : number_format($tot_pen_sewa_netto));
$start++;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("A{$start}", ' ');
$start++;
// 4300 Pendapatan Denda
$pen_denda = $this->loadModel('4300', 'MtChartMaster');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("A{$start}", $pen_denda->account_name);
$tot_pen_denda = -Mt::get_sum_account_trans($pen_denda->account_code, NULL, $start_date, $end_date);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("C{$start}", $is_excel ? $tot_pen_denda : number_format($tot_pen_denda));
$start++;
// 4400 Pendapatan Lain-lain
$pen_lain = $this->loadModel('4400', 'MtChartMaster');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("A{$start}", $pen_lain->account_name);
$tot_pen_lain = -Mt::get_sum_account_trans($pen_lain->account_code, NULL, $start_date, $end_date);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("C{$start}", $is_excel ? $tot_pen_lain : number_format($tot_pen_lain));
$start++;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("A{$start}", 'Beban-beban Terkait');
$start++;
$acc_beban = Mt::get_beban();
$total_beban_terkait = 0;
foreach ($acc_beban as $row) {
$beban = $this->loadModel($row['account_code'], 'MtChartMaster');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("A{$start}", ' --- ' . $beban->account_name);
$tot_beban = -Mt::get_sum_account_trans($beban->account_code, NULL, $start_date, $end_date);
$total_beban_terkait += $tot_beban;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("B{$start}", $is_excel ? $tot_beban : number_format($tot_beban));
$start++;
}
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("A{$start}", ' --- Total')->setCellValue("C{$start}", $is_excel ? $total_beban_terkait : number_format($total_beban_terkait));
$start++;
$total = $tot_pen_sewa_netto + $tot_pen_denda + $tot_pen_lain - $total_beban_terkait;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue("A{$start}", 'Laba')->setCellValue("C{$start}", $is_excel ? $total : number_format($total));
$start++;
$end_body = $start - 1;
$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
$objPHPExcel->setActiveSheetIndex(0)->getStyle("A{$start_body}:C{$end_body}")->applyFromArray($styleArray);
$start_row = $start_body + 1;
if ($is_excel) {
$objPHPExcel->setActiveSheetIndex(0)->getStyle("B{$start_body}:C{$end_body}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_ACCOUNTING);
} else {
$objPHPExcel->setActiveSheetIndex(0)->getStyle("B{$start_body}:C{$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);
$this->footer($objPHPExcel, $start, $file_name, $format, $worksheet_name);
Yii::app()->end();
}
}
public function actionPengeluaranPerKodeRekeningDetil()
{
if (Yii::app()->request->isAjaxRequest) {
return;
}
if (isset($_POST) && !empty($_POST)) {
$format = $_POST['format'];
$start_date = $_POST['trans_date_mulai'];
$end_date = $_POST['trans_date_sampai'];
$kode = $_POST['account_code'];
$daftar_code = Pah::get_arr_kode_rekening_pengeluaran($kode);
$index = 0;
$objPHPExcel = new PHPExcel();
$objPHPExcel->getDefaultStyle()->getFont()->setSize(10);
$file_name = 'PengeluaranPerKodeRekeningDetil';
foreach ($daftar_code as $key => $codename) {
$start = 1;
$worksheet_name = "{$key} - {$codename}";
if ($index > 0) {
$objPHPExcel->createSheet($index);
}
$objPHPExcel->setActiveSheetIndex($index);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
$objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.1 / 2.54);
$objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.1 / 2.54);
$objPHPExcel->getActiveSheet()->mergeCells("A{$start}:G{$start}")->setCellValue("A{$start}", "LAPORAN DETIL PENGELUARAN " . strtoupper($codename))->getStyle("A{$start}")->getFont()->setSize(18)->setBold(true);
$start++;
$objPHPExcel->getActiveSheet()->mergeCells("A{$start}:G{$start}")->setCellValue("A{$start}", "PONDOK ASUH HARAPAN")->getStyle("A{$start}")->getFont()->setSize(14)->setBold(true);
$start++;
$objPHPExcel->getActiveSheet()->mergeCells("A{$start}:G{$start}")->setCellValue("A{$start}", "PERIODE: " . sql2long_date($start_date) . " - " . sql2long_date($end_date))->getStyle("A{$start}")->getFont()->setSize(12)->setBold(true);
$objPHPExcel->getActiveSheet()->setTitle($worksheet_name);
$start++;
$start++;
$start_body = $start;
$objPHPExcel->getActiveSheet()->setCellValue("A{$start}", "Tgl")->setCellValue("B{$start}", "Keterangan")->setCellValue("C{$start}", "Debit")->setCellValue("D{$start}", "Kredit")->getStyle("A{$start}:D{$start}")->getFont()->setBold(true);
$start++;
$rows = Pah::get_pengeluaran_detil_kode_rekening($start_date, $end_date, $key);
$total = Pah::get_total_pengeluaran($start_date, $end_date, $key);
$total_persen = 0;
foreach ($rows as $row) {
// $total_persen += $row['total_beban'] / $total;
// $total_beban = $row['total_beban'];
// $persen = $row['total_beban'] / $total;
$objPHPExcel->getActiveSheet()->setCellValue("A{$start}", $row['tran_date'])->setCellValue("B{$start}", $row['memo_'])->setCellValue("C{$start}", $row['debit'])->setCellValue("D{$start}", $row['kredit']);
$start++;
}
//$total_format = $format == 'excel' ? $total : acc_format($total);
$objPHPExcel->getActiveSheet()->setCellValue("A{$start}", 'Total')->setCellValue($total > 0 ? "C{$start}" : "D{$start}", $total);
// ->setCellValue("C$start", $format == 'excel' ? $total_persen : percent_format($total_persen, 2));
$start++;
$end_body = $start - 1;
$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
$objPHPExcel->getActiveSheet()->getStyle("A{$start_body}:D{$end_body}")->applyFromArray($styleArray);
$start_row = $start_body + 1;
$objPHPExcel->getActiveSheet()->getStyle("C{$start_row}:D{$end_body}")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_ACCOUNTING);
// $objPHPExcel->getActiveSheet()->getStyle("C$start_row:C$end_body")->getNumberFormat()
// ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00);
$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);
$start++;
$jemaat = get_jemaat_from_user_id(Yii::app()->user->getId());
$objPHPExcel->getActiveSheet()->mergeCells("A{$start}:G{$start}")->setCellValue("A{$start}", "Dicetak oleh: " . $jemaat->real_name);
$start++;
$objPHPExcel->getActiveSheet()->mergeCells("A{$start}:G{$start}")->setCellValue("A{$start}", "Pada tanggal " . get_date_today('dd/MM/yyyy') . " jam " . get_time_now());
$index++;
}
ob_end_clean();
ob_start();
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename={$file_name}.xls");
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
Yii::app()->end();
}
}
