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(); } }