/** * @Author: ANH DUNG Sep 21, 2014 * @Todo: Export summary report */ public static function SummaryReport() { Yii::import('application.extensions.vendors.PHPExcel', true); $objPHPExcel = new PHPExcel(); // Set properties $objPHPExcel->getProperties()->setCreator("ANHDUNG")->setLastModifiedBy("ANHDUNG")->setTitle('Report Financial')->setSubject("Office 2007 XLSX Document")->setDescription("Summary Report Transaction")->setKeywords("office 2007 openxml php")->setCategory("Report Transaction"); $row = 1; $i = 1; $HeadTitle = 'Summary Report Transaction'; $cmsFormater = new CmsFormatter(); // 1.sheet 1 $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName('Times New Roman'); $objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(12); $objPHPExcel->getActiveSheet()->setTitle($HeadTitle); $objPHPExcel->getActiveSheet()->setCellValue("A{$row}", "Summary Report Transaction"); $objPHPExcel->getActiveSheet()->getStyle("A{$row}")->getFont()->setBold(true); $objPHPExcel->getActiveSheet()->mergeCells("A{$row}:c{$row}"); $row++; $index = 1; $beginBorder = $row; $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", 'S/N'); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", 'Invoice No'); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", 'Submitted Date'); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", 'Listing Type'); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", 'Property Address'); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", 'Type'); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", 'Price'); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", 'Client Commission'); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", 'Receivable Commission Amount'); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", 'Received Commission Amount'); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", 'Received Date'); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", 'Commission Receivable from External Cobroke agent'); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", 'Commission payable to External Cobroke agent'); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", 'External Coboke agent company'); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", 'External Coboke agent name'); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", 'Paid date'); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", 'Gross Commission to Company'); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", 'Salesperson Name'); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", 'Commission Payable to Salesperson'); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", '1st Tier Manager Name'); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", 'Overriding to 1st Tier Manager'); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", '2nd Tier Manager Name'); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", 'Overriding to 2nd Tier Manager'); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", "Telemarketer's Name"); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", 'Commission Payable to Telemarketer'); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", 'Net Commission Earned by Company'); $objPHPExcel->getActiveSheet()->getRowDimension($row)->setRowHeight(40); $index--; $objPHPExcel->getActiveSheet()->getStyle("A{$row}:" . MyFormat::columnName($index) . $row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle("A{$row}:" . MyFormat::columnName($index) . $row)->getFont()->setBold(true); $row++; foreach ($_SESSION['DATA_SUMMARY_REPORT']->data as $data) { $index = 1; $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", $i); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", $cmsFormater->formatSumReportInvoiceNo($data)); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", $cmsFormater->formatdate($data->created_date)); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", $cmsFormater->formatTransListingType($data)); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", $cmsFormater->formatTransactionPropertyName($data)); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", $cmsFormater->formatPropertyType($data->type)); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", $cmsFormater->formatTransactionPropertyPrice($data)); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", $cmsFormater->formatSumReportClientCom($data)); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", $cmsFormater->formatSumReportReceivableCommissionAmount($data)); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", $cmsFormater->formatSumReportReceivedCommissionAmount($data)); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", $cmsFormater->formatSumReportReceivedDate($data)); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", $cmsFormater->formatSumReportComExternalCobrokeAgent($data)); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", $cmsFormater->formatSumReportComPayableExternalCobrokeAgent($data)); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", $cmsFormater->formatSumReportExternalCobrokeAgentCompany($data)); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", $cmsFormater->formatSumReportExternalCobrokeAgentName($data)); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", $cmsFormater->formatSumReportDatePaidExernalCobroke($data)); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", $cmsFormater->formatSumReportGrossCommissiontoCompany($data)); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", $cmsFormater->formatSumReportSalespersonName($data)); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", $cmsFormater->formatSumReportCommissionPayabletoSalesperson($data)); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", $cmsFormater->formatSumReport1stTierName($data)); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", $cmsFormater->formatSumReport1stTierOverriding($data)); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", $cmsFormater->formatSumReport2ndTierName($data)); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", $cmsFormater->formatSumReport2ndTierOverriding($data)); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", $cmsFormater->formatSumReportTelemarketerName($data)); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", $cmsFormater->formatSumReportTelemarketerComm($data)); $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++) . "{$row}", $cmsFormater->formatSumReportNetCommissionEarnedbyCompany($data)); // $objPHPExcel->getActiveSheet()->setCellValue(MyFormat::columnName($index++)."$row", 'TEST VALUE'); $row++; $i++; } $objPHPExcel->getActiveSheet()->getStyle("B{$beginBorder}:" . MyFormat::columnName($index) . $row)->getAlignment()->setWrapText(true); $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(10); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('U')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('V')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('W')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('X')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('Y')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('Z')->setWidth(30); $row--; $index--; $objPHPExcel->getActiveSheet()->getStyle("G{$beginBorder}:G" . $row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle("H{$beginBorder}:H" . $row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle("I{$beginBorder}:I" . $row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle("K{$beginBorder}:K" . $row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle("L{$beginBorder}:L" . $row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle("M{$beginBorder}:M" . $row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle("Q{$beginBorder}:Q" . $row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle("S{$beginBorder}:S" . $row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle("U{$beginBorder}:U" . $row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle("W{$beginBorder}:W" . $row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle("Z{$beginBorder}:Z" . $row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); $objPHPExcel->getActiveSheet()->getStyle("A{$beginBorder}" . ':' . MyFormat::columnName($index) . $row)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle("A{$beginBorder}:" . MyFormat::columnName($index) . $row)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); //save file $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); for ($level = ob_get_level(); $level > 0; --$level) { @ob_end_clean(); } header('Cache-Control: must-revalidate, post-check=0, pre-check=0'); header('Pragma: public'); header('Content-type: ' . 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment; filename="' . 'Summary Report Transaction.' . 'xlsx' . '"'); header('Cache-Control: max-age=0'); $objWriter->save('php://output'); Yii::app()->end(); }