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