function report()
{
//load our new PHPExcel library
$this->load->library('excel');
$this->load->library('Sanitizer');
$EmployeeMeta = new Sanitizer($EmpId);
$employee = $this->uri->segment(3);
$from = date('Y-m-d', strtotime($this->input->post('FromDate')));
$to = date('Y-m-d', strtotime($this->input->post('endDate')));
$this->db->select('*');
$this->db->from('ecc_calls_call');
//$this->db->join('ecc_employees_emp', 'idemp_call = id_emp', 'inner');
$this->db->join('ecc_company_co', 'idco_call = id_co', 'inner');
$this->db->where('status_call', 'active');
$this->db->where('callStatus_call', 'done');
$this->db->where('idemp_call', $employee);
$this->db->where('inDate_call >=', $from);
$this->db->where('inDate_call <=', $to);
$getCalls = $this->db->get();
$cell = 9;
$meta = $EmployeeMeta->EmployeeMeta($employee);
$callNumber = 1;
foreach ($getCalls->result() as $row) {
$cell = $cell + 4;
$this->db->select('*');
$this->db->from('ecc_companyCategory_cocat');
$getCat = $this->db->get();
$this->db->select('*');
$this->db->from('ecc_feedback_feed');
$this->db->where('idcall_feed', $row->id_call);
$getFeed = $this->db->get();
//call meta
$this->db->SELECT('*');
$this->db->FROM('ecc_call_cmeta');
$this->db->WHERE('idcall_cmeta', $row->id_call);
$metaCall = $this->db->GET();
$thisMetaCall = array();
foreach ($metaCall->result() as $row2) {
$thisMetaCall[$row2->metaKey_cmeta] = $row2->metaValue_cmeta;
}
//meta
$MetaCall = array('reason' => $thisMetaCall['reason'], 'contact' => $thisMetaCall['contact']);
//call meta
$this->db->SELECT('*');
$this->db->FROM('ecc_call_cmeta');
$this->db->WHERE('idcall_cmeta', $row->id_call);
$this->db->WHERE('metaKey_cmeta', 'contact');
$metaContact = $this->db->GET();
$name = $meta['first_name'] . " " . $meta['last_name'];
$position = $meta['position'];
$totalCalls = $getCalls->num_rows();
$company = $row->name_co;
$date = $row->date_call;
$doneDate = $row->inDate_call;
$objective = $MetaCall['reason'];
//$feedback = $MetaCall['feedback'];
$status = $row->callStatus_call;
//$logo = imagecreatefromjpeg(base_url() . "assets/bofficr/images/ecc_report_logo.png");
$author = $this->name;
//date
$format = 'DATE_RFC1036';
$time = time();
$creationDate = standard_date($format, $time);
//activate worksheet number 1
$this->excel->setActiveSheetIndex(0);
//name the worksheet
$this->excel->getActiveSheet()->setTitle($name . ' Calls Report');
$this->excel->getActiveSheet()->setCellValue('A1', $name);
$this->excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
$this->excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$this->excel->getActiveSheet()->mergeCells('A1:G1');
$this->excel->getActiveSheet()->setCellValue('A2', $position);
$this->excel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
$this->excel->getActiveSheet()->mergeCells('A2:G2');
$this->excel->getActiveSheet()->setCellValue('A4', 'Total Calls');
$this->excel->getActiveSheet()->getStyle('A4')->getFont()->setSize(16);
$this->excel->getActiveSheet()->getStyle('A4')->getFont()->setBold(true);
$this->excel->getActiveSheet()->mergeCells('A4:C4');
$this->excel->getActiveSheet()->setCellValue('E4', 'From');
$this->excel->getActiveSheet()->getStyle('E4')->getFont()->setSize(16);
$this->excel->getActiveSheet()->getStyle('E4')->getFont()->setBold(true);
$this->excel->getActiveSheet()->setCellValue('H4', 'To');
$this->excel->getActiveSheet()->getStyle('H4')->getFont()->setSize(16);
$this->excel->getActiveSheet()->getStyle('H4')->getFont()->setBold(true);
//$this->excel->getActiveSheet()->setCellValue('A5', 'Companies');
$this->excel->getActiveSheet()->getStyle('A5')->getFont()->setSize(16);
$this->excel->getActiveSheet()->getStyle('A5')->getFont()->setBold(true);
$this->excel->getActiveSheet()->mergeCells('A5:C5');
$this->excel->getActiveSheet()->setCellValue('A7', 'Company to call');
$this->excel->getActiveSheet()->getStyle('A7')->getFont()->setSize(16);
$this->excel->getActiveSheet()->getStyle('A7')->getFont()->setBold(true);
$this->excel->getActiveSheet()->mergeCells('A7:D11');
$this->excel->getActiveSheet()->setCellValue('E7', 'Created in');
$this->excel->getActiveSheet()->getStyle('E7')->getFont()->setSize(16);
$this->excel->getActiveSheet()->getStyle('E7')->getFont()->setBold(true);
$this->excel->getActiveSheet()->mergeCells('E7:G11');
$this->excel->getActiveSheet()->setCellValue('H7', 'Done in date');
$this->excel->getActiveSheet()->getStyle('H7')->getFont()->setSize(16);
$this->excel->getActiveSheet()->getStyle('H7')->getFont()->setBold(true);
$this->excel->getActiveSheet()->mergeCells('H7:I11');
$this->excel->getActiveSheet()->setCellValue('J7', 'Call objective');
$this->excel->getActiveSheet()->getStyle('J7')->getFont()->setSize(16);
$this->excel->getActiveSheet()->getStyle('J7')->getFont()->setBold(true);
$this->excel->getActiveSheet()->mergeCells('J7:K11');
$this->excel->getActiveSheet()->setCellValue('D4', $totalCalls);
$this->excel->getActiveSheet()->getStyle('D4')->getFont()->setSize(12);
//$this->excel->getActiveSheet()->mergeCells('C4:D4');
$this->excel->getActiveSheet()->setCellValue('F4', $from);
$this->excel->getActiveSheet()->getStyle('F4')->getFont()->setSize(12);
$this->excel->getActiveSheet()->mergeCells('F4:G4');
$this->excel->getActiveSheet()->setCellValue('I4', $to);
$this->excel->getActiveSheet()->getStyle('I4')->getFont()->setSize(12);
$this->excel->getActiveSheet()->getStyle('A7:V11')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FF555555');
$this->excel->getActiveSheet()->getStyle('A7:V11')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$this->excel->getActiveSheet()->getStyle('A7:V11')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$this->excel->getActiveSheet()->getStyle('A7:V8')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$this->excel->getActiveSheet()->getStyle('A7:V11')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
/*
$this->excel->getActiveSheet()->setCellValue('B6', '0');
$this->excel->getActiveSheet()->setCellValue('D6', '0');
$this->excel->getActiveSheet()->setCellValue('F6', '0');
$this->excel->getActiveSheet()->setCellValue('H6', '0');
$this->excel->getActiveSheet()->setCellValue('J6', '0');
$catCell = 1;
foreach($getCat->result() as $rowCat){
if($catCell == 1){
$this->excel->getActiveSheet()->setCellValue('A6', $rowCat->name_cocat);
$this->excel->getActiveSheet()->getStyle('A6')->getFont()->setSize(12);
$catCell = $catCell+1;
}elseif($catCell == 2){
$cat{$catCell} = 1;
$this->excel->getActiveSheet()->setCellValue('C6', $rowCat->name_cocat);
$this->excel->getActiveSheet()->getStyle('C6')->getFont()->setSize(12);
$catCell = $catCell+1;
}elseif($catCell == 3){
$cat{$catCell} = 1;
$this->excel->getActiveSheet()->setCellValue('E6', $rowCat->name_cocat);
$this->excel->getActiveSheet()->getStyle('E6')->getFont()->setSize(12);
$catCell = $catCell+1;
}elseif($catCell == 4){
$cat{$catCell} = 1;
$this->excel->getActiveSheet()->setCellValue('G6', $rowCat->name_cocat);
$this->excel->getActiveSheet()->getStyle('G6')->getFont()->setSize(12);
$catCell = $catCell+1;
}elseif($catCell == 5){
$cat{$catCell} = 1;
$this->excel->getActiveSheet()->setCellValue('I6', $rowCat->name_cocat);
$this->excel->getActiveSheet()->getStyle('I6')->getFont()->setSize(12);
$catCell = $catCell+1;
}elseif($catCell == 6){
$cat{$catCell} = 1;
$this->excel->getActiveSheet()->setCellValue('K6', $rowCat->name_cocat);
$this->excel->getActiveSheet()->getStyle('K6')->getFont()->setSize(12);
$catCell = $catCell+1;
}elseif($catCell == 7){
$this->excel->getActiveSheet()->setCellValue('M6', $rowCat->name_cocat);
$this->excel->getActiveSheet()->getStyle('M6')->getFont()->setSize(12);
$catCell = $catCell+1;
}
};
$callCat = 1;
$callCat2 = 1;
if($row->idcocat_co == 1){
$this->excel->getActiveSheet()->setCellValue('B6', $callCat);
$callCat++;
}elseif($row->idcocat_co == 2){
$this->excel->getActiveSheet()->setCellValue('D6', +1);
}elseif($row->idcocat_co == 3){
$this->excel->getActiveSheet()->setCellValue('F6', +1);
}elseif($row->idcocat_co == 4){
$this->excel->getActiveSheet()->setCellValue('H6', +1);
$callCat2++;
};
*/
//echo $row->idcocat_co ."<br />";
$this->excel->getActiveSheet()->getStyle('A' . $cell . ':V' . $cell)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFdcdcdc');
$this->excel->getActiveSheet()->getStyle('A' . $cell . ':V' . $cell)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$this->excel->getActiveSheet()->setCellValue('A' . $cell, $callNumber);
$this->excel->getActiveSheet()->getStyle('A' . $cell)->getFont()->setSize(20);
$this->excel->getActiveSheet()->getStyle('A' . $cell)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->excel->getActiveSheet()->getStyle('A' . $cell)->getFont()->setBold(true);
$this->excel->getActiveSheet()->getStyle('B' . ($cell + 1))->getFont()->setBold(true);
$this->excel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
$this->excel->getActiveSheet()->setCellValue('B' . $cell, $company);
$this->excel->getActiveSheet()->getStyle('B' . $cell)->getFont()->setSize(14);
$this->excel->getActiveSheet()->mergeCells('B' . $cell . ':D' . $cell);
$this->excel->getActiveSheet()->getStyle('B' . $cell)->getFont()->setBold(true);
$this->excel->getActiveSheet()->getRowDimension($cell)->setRowHeight(40);
$this->excel->getActiveSheet()->getStyle('A' . $cell . ':V' . $cell)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$this->excel->getActiveSheet()->setCellValue('E' . $cell, $date);
$this->excel->getActiveSheet()->getStyle('E' . $cell)->getFont()->setSize(12);
$this->excel->getActiveSheet()->mergeCells('E' . $cell . ':F' . $cell);
$this->excel->getActiveSheet()->setCellValue('H' . $cell, $doneDate);
$this->excel->getActiveSheet()->getStyle('H' . $cell)->getFont()->setSize(12);
$this->excel->getActiveSheet()->mergeCells('H' . $cell . ':I' . $cell);
$this->excel->getActiveSheet()->setCellValue('J' . $cell, $objective);
$this->excel->getActiveSheet()->getStyle('J' . $cell)->getFont()->setSize(12);
$this->excel->getActiveSheet()->mergeCells('J' . $cell . ':V' . $cell);
$this->excel->getActiveSheet()->setCellValue('B' . ($cell + 1), 'Feedbacks');
$this->excel->getActiveSheet()->getStyle('B' . ($cell + 1))->getFont()->setSize(14);
$this->excel->getActiveSheet()->getStyle('B' . ($cell + 1))->getFont()->setBold(true);
$this->excel->getActiveSheet()->mergeCells('B' . ($cell + 1) . ':V' . ($cell + 1));
$this->excel->getActiveSheet()->getRowDimension($cell + 1)->setRowHeight(30);
$this->excel->getActiveSheet()->getStyle('B' . ($cell + 1) . ':V' . ($cell + 1))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$this->excel->getActiveSheet()->getStyle('B' . ($cell + 1) . ':V' . ($cell + 1))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFf0f0f0');
$this->excel->getActiveSheet()->getStyle('B' . ($cell + 1) . ':V' . ($cell + 1))->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$this->excel->getActiveSheet()->mergeCells('J7:V8');
$this->excel->getActiveSheet()->mergeCells('C7:D7');
$this->excel->getActiveSheet()->mergeCells('C8:D8');
$this->excel->getActiveSheet()->mergeCells('A1:V1');
$this->excel->getActiveSheet()->mergeCells('A2:V2');
$this->excel->getActiveSheet()->mergeCells('A3:V3');
$this->excel->getActiveSheet()->mergeCells('A6:V6');
//$this->excel->getActiveSheet()->mergeCells('A9:V11');
$cellFeed = $cell + 2;
if ($getFeed->num_rows() == 0) {
$this->excel->getActiveSheet()->setCellValue('B' . $cellFeed, 'No Feedbacks avalable');
$this->excel->getActiveSheet()->mergeCells('B' . $cellFeed . ':V' . $cellFeed);
$this->excel->getActiveSheet()->getRowDimension($cellFeed)->setRowHeight(30);
$this->excel->getActiveSheet()->getStyle('B' . $cellFeed . ':V' . $cellFeed)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$this->excel->getActiveSheet()->getStyle('B' . $cellFeed . ':V' . $cellFeed)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFf0f0f0');
$this->excel->getActiveSheet()->getStyle('B' . $cellFeed . ':V' . $cellFeed)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
} else {
foreach ($getFeed->result() as $rowFeed) {
$metaFeed = $EmployeeMeta->EmployeeMeta($rowFeed->idemp_feed);
$this->excel->getActiveSheet()->getRowDimension($cellFeed)->setRowHeight(25);
$this->excel->getActiveSheet()->getStyle('B' . $cellFeed . ':V' . $cellFeed)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$this->excel->getActiveSheet()->setCellValue('B' . $cellFeed, $metaFeed['first_name'] . " " . $metaFeed['last_name']);
$this->excel->getActiveSheet()->getStyle('B' . $cellFeed)->getFont()->setSize(12);
$this->excel->getActiveSheet()->mergeCells('B' . $cellFeed . ':D' . $cellFeed);
$this->excel->getActiveSheet()->setCellValue('E' . $cellFeed, '(' . $rowFeed->date_feed . ') ' . $rowFeed->feedback_feed);
$this->excel->getActiveSheet()->getStyle('E' . $cellFeed)->getFont()->setSize(12);
$this->excel->getActiveSheet()->mergeCells('E' . $cellFeed . ':V' . $cellFeed);
$this->excel->getActiveSheet()->getStyle('B' . $cellFeed . ':V' . $cellFeed)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFf0f0f0');
$this->excel->getActiveSheet()->getStyle('B' . $cellFeed . ':V' . $cellFeed)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$cellFeed++;
}
}
$this->excel->getActiveSheet()->setCellValue('B' . ($cellFeed + 1), 'In Contact List');
$this->excel->getActiveSheet()->getStyle('B' . ($cellFeed + 1))->getFont()->setSize(14);
$this->excel->getActiveSheet()->getStyle('B' . ($cellFeed + 1))->getFont()->setBold(true);
$this->excel->getActiveSheet()->mergeCells('B' . ($cellFeed + 1) . ':V' . ($cellFeed + 1));
$this->excel->getActiveSheet()->getRowDimension($cellFeed + 1)->setRowHeight(30);
$this->excel->getActiveSheet()->getStyle('B' . ($cellFeed + 1) . ':V' . ($cellFeed + 1))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$this->excel->getActiveSheet()->getStyle('B' . ($cellFeed + 1) . ':V' . ($cellFeed + 1))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFf0f0f0');
$this->excel->getActiveSheet()->getStyle('B' . ($cellFeed + 1) . ':V' . ($cellFeed + 1))->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//$i = 1;
$contactList = array();
$cellContact = $cellFeed;
if ($metaContact->num_rows() == '0') {
$this->excel->getActiveSheet()->setCellValue('B' . ($cellContact + 2), 'No contact asigned to this call');
$this->excel->getActiveSheet()->mergeCells('B' . ($cellContact + 2) . ':V' . ($cellContact + 2));
} else {
foreach ($metaContact->result() as $rowContact) {
$this->db->select('*');
$this->db->from('ecc_companyContact_cocontact');
$this->db->where('id_cocontact', $rowContact->metaValue_cmeta);
$contactName = $this->db->get();
foreach ($contactName->result() as $rowContactName) {
$this->excel->getActiveSheet()->getRowDimension($cellContact + 2)->setRowHeight(25);
$this->excel->getActiveSheet()->getStyle('B' . ($cellContact + 2) . ':V' . ($cellContact + 2))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$this->excel->getActiveSheet()->setCellValue('B' . ($cellContact + 2), $rowContactName->name_cocontact);
$this->excel->getActiveSheet()->setCellValue('E' . ($cellContact + 2), $rowContactName->email_cocontact);
$this->excel->getActiveSheet()->setCellValue('J' . ($cellContact + 2), '(' . $rowContactName->mobile_cocontact . ')');
$this->excel->getActiveSheet()->getStyle('J' . ($cellContact + 2))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$this->excel->getActiveSheet()->getStyle('B' . ($cellContact + 2))->getFont()->setSize(12);
$this->excel->getActiveSheet()->mergeCells('B' . ($cellContact + 2) . ':D' . ($cellContact + 2));
$this->excel->getActiveSheet()->mergeCells('E' . ($cellContact + 2) . ':I' . ($cellContact + 2));
$this->excel->getActiveSheet()->mergeCells('J' . ($cellContact + 2) . ':V' . ($cellContact + 2));
$this->excel->getActiveSheet()->getStyle('B' . ($cellContact + 2) . ':V' . ($cellContact + 2))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFf0f0f0');
$this->excel->getActiveSheet()->getStyle('B' . ($cellContact + 2) . ':V' . ($cellContact + 2))->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
}
$i++;
$cellContact++;
}
}
$cell = $cellContact;
//set aligment to center for that merged cell (A1 to D1)
$this->excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$this->excel->getActiveSheet()->getStyle('D4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$filename = $name . " (from " . $from . ' to ' . $to . ').xls';
//save our workbook as this file name
header('Content-Type: application/vnd.ms-excel');
//mime type
header('Content-Disposition: attachment;filename="' . $filename . '"');
//tell browser what's the file name
header('Cache-Control: max-age=0');
//no cache
//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
//if you want to save it as .XLSX Excel 2007 format
$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
//force user to download the Excel file without writing it to server's HD
$callNumber++;
}
//foreach
/*
$this->excel->getActiveSheet()->setCellValue('A'.($cell+3), 'this report was generated by the ECC Sales Quality System in ' . $creationDate .', by the request of '. $author);
$this->excel->getActiveSheet()->getStyle('A'.($cell+3))->getFont()->setSize(12);
$this->excel->getActiveSheet()->mergeCells('A'.($cell+3).':Q'.($cell+3));
*/
$objWriter->save('php://output');
}
