コード例 #1
0
ファイル: boffice.php プロジェクト: sirfsonuji/SAM-API
 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');
 }