515 lines
17 KiB
PHP
515 lines
17 KiB
PHP
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
|
||
|
||
//require phpoffice library(Spreadsheet and Writer)
|
||
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
||
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
|
||
|
||
//require excel styles
|
||
use PhpOffice\PhpSpreadsheet\Style\Fill;
|
||
use PhpOffice\PhpSpreadsheet\Style\Border;
|
||
use PhpOffice\PhpSpreadsheet\Style\Color;
|
||
use PhpOffice\PhpSpreadsheet\Style\Alignment;
|
||
|
||
// for column index
|
||
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
|
||
|
||
class Event_info extends MY_Controller {
|
||
|
||
/**
|
||
* php excel object instance variables
|
||
*
|
||
* @var string
|
||
*/
|
||
private $spreadsheet;
|
||
|
||
/**
|
||
* no show excel file tabs
|
||
*
|
||
* @var string
|
||
*/
|
||
private $reports;
|
||
|
||
/**
|
||
* instance variable for PhpOffice writer class
|
||
*
|
||
* @var string
|
||
*/
|
||
private $writer;
|
||
|
||
/**
|
||
* instance variable for activesheet
|
||
*
|
||
* @var string
|
||
*/
|
||
private $sheets;
|
||
|
||
/**
|
||
* variable for default background color
|
||
*
|
||
* @var string
|
||
*/
|
||
private $default_background_color;
|
||
|
||
/**
|
||
* Tab title
|
||
*
|
||
* @var string
|
||
*/
|
||
private $sheetTitle;
|
||
|
||
/**
|
||
* Holds default maximum row
|
||
*
|
||
* @var int
|
||
*/
|
||
private $defaultMaximumRow;
|
||
|
||
/**
|
||
* Holds default column headers
|
||
*
|
||
* @var array
|
||
*/
|
||
private $columnHeaders;
|
||
|
||
/**
|
||
* Export File Name
|
||
*
|
||
* @var array
|
||
*/
|
||
private $fileName;
|
||
|
||
public function __construct()
|
||
{
|
||
//parent::__construct();
|
||
$this->my_parent_controller();
|
||
// $this->check_session_timed_out("bo_redirect_now");
|
||
//load language files
|
||
$this->load_language_backoffice();
|
||
$this->lang->load('backoffice/reports', 'fr');
|
||
$this->lang->load('backoffice/events', 'fr');
|
||
//load model
|
||
$this->load->model("user_model");
|
||
$this->load->model("event_model");
|
||
$this->load->model("event_schedule_model");
|
||
$this->load->model("event_registration_model");
|
||
|
||
// Create new PHPExcel object
|
||
$this->spreadsheet = new Spreadsheet();
|
||
|
||
//get active sheet
|
||
$this->sheets = $this->spreadsheet->getActiveSheet();
|
||
|
||
//create writer object
|
||
$this->writer = new Xlsx($this->spreadsheet);
|
||
}
|
||
|
||
// -----------------------------------------------------------------------------
|
||
/**
|
||
* Set execution and memory limit
|
||
*
|
||
* @return void
|
||
* access public
|
||
*/
|
||
public function setMemoryLimit()
|
||
{
|
||
ini_set('memory_limit', '1024M');
|
||
ini_set('max_execution_time', (60*3));
|
||
}
|
||
|
||
/**
|
||
* Set document properties
|
||
*
|
||
* @return void
|
||
* access public
|
||
*/
|
||
public function setDocumentProperties()
|
||
{
|
||
return $this->spreadsheet->getProperties()->setCreator("Company For Madagascar Event Management System")
|
||
->setLastModifiedBy("Company For Madagascar Event Management System")
|
||
->setTitle("Office 2007 XLSX Document")
|
||
->setSubject("Office 2007 XLSX Document")
|
||
->setDescription("Event export file")
|
||
->setKeywords("office 2007 openxml php")
|
||
->setCategory("Export file");
|
||
}
|
||
|
||
/**
|
||
* Set headers(pragma, cache control , cookies etc..)
|
||
*
|
||
* @param string $filename
|
||
* @return void
|
||
*/
|
||
public function setHeaders()
|
||
{
|
||
// Redirect output to a client’s web browser (Excel2007)
|
||
header('Content-Type: application/vnd.ms-excel; charset=utf-8');
|
||
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
|
||
header('Content-Disposition: attachment;filename="'.$this->fileName.'.xlsx"');
|
||
header('Cache-Control: max-age=0');
|
||
// header('Content-Encoding: deflate, gzip');
|
||
header('Cache-Control: private');
|
||
|
||
// If you're serving to IE 9, then the following may be needed
|
||
header('Cache-Control: max-age=1');
|
||
|
||
// If you're serving to IE over SSL, then the following may be needed
|
||
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
|
||
header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
|
||
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
|
||
header('Pragma: public'); // HTTP/1.0
|
||
|
||
$this->writer->save('php://output');
|
||
exit();
|
||
}
|
||
|
||
/**
|
||
* Auto size columns for each worksheet
|
||
*
|
||
* @return void
|
||
* access public
|
||
*/
|
||
public function setExcelAutoSize()
|
||
{
|
||
// Auto size columns for each worksheet
|
||
foreach ($this->spreadsheet->getWorksheetIterator() as $worksheet) {
|
||
|
||
$this->spreadsheet->setActiveSheetIndex($this->spreadsheet->getIndex($worksheet));
|
||
|
||
$sheet = $this->spreadsheet->getActiveSheet();
|
||
$cellIterator = $sheet->getRowIterator()->current()->getCellIterator();
|
||
$cellIterator->setIterateOnlyExistingCells(true);
|
||
/** @var PHPExcel_Cell $cell */
|
||
foreach ($cellIterator as $cell) {
|
||
$sheet->getColumnDimension($cell->getColumn())->setAutoSize(true);
|
||
}
|
||
}
|
||
}
|
||
|
||
/**
|
||
* Export no show file with title+date
|
||
*
|
||
* @param integer $eventID
|
||
* @param integer $eventScheduleID
|
||
* @return void
|
||
* access private member
|
||
*/
|
||
public function export_events_list($event_schedule_id, $event_id)
|
||
{
|
||
//call memory limit
|
||
$this->setMemoryLimit();
|
||
|
||
//call document properties
|
||
$this->setDocumentProperties();
|
||
|
||
//get data for populate
|
||
$this->reportData($event_schedule_id, $event_id);
|
||
|
||
//Call auto size function columns for active worksheet
|
||
$this->setExcelAutoSize();
|
||
|
||
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
|
||
$this->spreadsheet->setActiveSheetIndex(0);
|
||
|
||
$this->setHeaders();
|
||
}
|
||
|
||
public function reportData($event_schedule_id, $event_id)
|
||
{
|
||
$queryData = $this->input->get();
|
||
ini_set('memory_limit', '1024M');
|
||
ini_set('max_execution_time', (60*3));
|
||
|
||
$reports = array("event", "subscriber");
|
||
$event_title = ""; $start_date_filename = "";
|
||
|
||
foreach($reports as $sheet=>$value) {
|
||
$options = $this->get_report($value, $event_schedule_id, $event_id, $queryData);
|
||
$row = 1;
|
||
$row_cell = 2;
|
||
$active_sheet = null;
|
||
|
||
if ($sheet > 0) {
|
||
$this->spreadsheet->createSheet();
|
||
$this->spreadsheet->setActiveSheetIndex($sheet)->setTitle($this->lang->line("reports")[$value][$value."_file_name"]);
|
||
} else {
|
||
$this->spreadsheet->setActiveSheetIndex($sheet)->setTitle($this->lang->line("reports")[$value][$value."_file_name"]);
|
||
}
|
||
|
||
$col = 1;
|
||
|
||
// Set title header on every sheet except "event"
|
||
if($value != "event"){
|
||
$this->event_header_title($this->spreadsheet->setActiveSheetIndex($sheet), $event_title, $options["column_names"]);
|
||
$row= 2;
|
||
$row_cell = 3;
|
||
}
|
||
|
||
if (count($options["column_names"]) > 0) {
|
||
$headers = ""; $datas = "";
|
||
foreach ($options["column_names"] as $colKey => $name) {
|
||
if( $name == "total_places_avl"){
|
||
$headers = !empty($data->{"name_header_is_combine"}) ? "total_places_avl_combine" : "total_places_avl";
|
||
} else if($name == "sessions"){
|
||
$this->spreadsheet->getActiveSheet()->getStyle('K2')->getAlignment()->setWrapText(true);
|
||
$headers = $name;
|
||
} else{
|
||
$headers = $name;
|
||
}
|
||
|
||
$this->spreadsheet->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $this->lang->line($headers));
|
||
$generalStyleArray = [
|
||
'font' => [
|
||
'bold' => false,
|
||
],
|
||
'alignment' => [
|
||
'horizontal' => Alignment::HORIZONTAL_CENTER,
|
||
],
|
||
'fill' => [
|
||
'fillType' => Fill::FILL_SOLID,
|
||
'rotation' => 90,
|
||
'startColor' => [
|
||
'argb' => '428bca',
|
||
],
|
||
'endColor' => [
|
||
'argb' => '428bca',
|
||
],
|
||
],
|
||
];
|
||
|
||
$boderStyle = ['borderStyle' => Border::BORDER_THIN];
|
||
$boderStyleArray = [
|
||
'borders' => [
|
||
'top' => $boderStyle,
|
||
'right' => $boderStyle,
|
||
'left' => $boderStyle,
|
||
'bottom' => $boderStyle
|
||
]
|
||
];
|
||
|
||
// set style
|
||
$this->spreadsheet->getActiveSheet()->getStyle(Coordinate::stringFromColumnIndex($col).$row)->applyFromArray($generalStyleArray);
|
||
|
||
$col++;
|
||
}
|
||
}
|
||
|
||
$col = 1;
|
||
|
||
if (count($options["data"]) > 0) {
|
||
foreach ($options["data"] as $data) {
|
||
foreach ($options["column_names"] as $colKey => $name) {
|
||
if (trim($name) != "") {
|
||
$cell_value = $data->{$name};
|
||
if ($name == "price_rate") {
|
||
$cell_value = str_replace(",00", "", number_format($data->{$name}, 2, ",", " ")) . " MGA";
|
||
} else if($name == "event_title") {
|
||
$event_title = $data->{$name};
|
||
!$this->fileName && $this->fileName = $event_title." ".date('d-m-Y',strtotime($data->start_date));
|
||
} else if($name == "date_month") {
|
||
$cell_value = $data->{$name};
|
||
} else if($name == "hour") {
|
||
$cell_value = $data->{$name};
|
||
} else if($name == "description") {
|
||
$cell_value = strip_tags($data->{$name});
|
||
} else if($name == "sessions") {
|
||
$sessions = "";
|
||
for($i = 0; $i<count($data->{$name}); $i++) {
|
||
$sessions = $sessions . $data->{$name}[$i]->start_date_time. ' - ' . $data->{$name}[$i]->end_date_time;
|
||
if($i<count($data->{$name})-1) {
|
||
$sessions = $sessions . "\n";
|
||
}
|
||
}
|
||
$cell_value = $sessions;
|
||
} else if($name == "total_booked"){
|
||
$cell_value = $data->{"total_booked"};
|
||
if(isset($data->{"type"})) {
|
||
if($data->{"type"} == 0) {
|
||
$cell_value = $data->{"total_booked_ws"};
|
||
}
|
||
}
|
||
}
|
||
|
||
$this->spreadsheet->getActiveSheet()->setCellValueByColumnAndRow($col, $row_cell, $cell_value);
|
||
} else {
|
||
$this->spreadsheet->getActiveSheet()->setCellValueByColumnAndRow($col, $row_cell, "");
|
||
}
|
||
|
||
$col++;
|
||
}
|
||
$col = 1;
|
||
$row_cell++;
|
||
}
|
||
}
|
||
} //end of for loop
|
||
}
|
||
|
||
private function get_report($type, $event_schedule_id, $event_id=null, $queryData){
|
||
switch($type){
|
||
|
||
case "event" : return $this->event_data($event_schedule_id, $queryData);
|
||
break;
|
||
|
||
case "subscriber" : return $this->export_subscribers_merge($event_id);
|
||
break;
|
||
|
||
case "unsubscribe" : return $this->export_unsubscription($event_schedule_id);
|
||
break;
|
||
|
||
case "waitlist" : return $this->export_waitlist($event_schedule_id);
|
||
break;
|
||
|
||
default : return array();
|
||
break;
|
||
};
|
||
}
|
||
|
||
public function export_subscribers_merge($event_schedule_id)
|
||
{
|
||
/*BASIC SUBCRIBERS LIST EXPORT TEMPLATE*/
|
||
$column_names = array(
|
||
|
||
"civility",
|
||
"last_name",
|
||
"first_name",
|
||
"birth_date",
|
||
"total_booked",
|
||
"email_address",
|
||
"mobile_number",
|
||
"address_1",
|
||
"address_2",
|
||
"country",
|
||
"code_postal",
|
||
"city",
|
||
"reservation_date",
|
||
"reservation_time",
|
||
//"navigator",
|
||
"reservation_status"
|
||
);
|
||
|
||
$subscribers = $this->event_registration_model->get_event_subscribers_with_waitlist($event_schedule_id);
|
||
|
||
return array(
|
||
"column_names" => $column_names,
|
||
"data" => $subscribers
|
||
);
|
||
}
|
||
|
||
public function export_unsubscription($event_schedule_id)
|
||
{
|
||
/*BASIC SUBCRIBERS LIST EXPORT TEMPLATE*/
|
||
$column_names = array(
|
||
|
||
"civility",
|
||
"last_name",
|
||
"first_name",
|
||
"birth_date",
|
||
"total_booked",
|
||
"number_of_place_cancelled",
|
||
"email_address",
|
||
"mobile_number",
|
||
"address_1",
|
||
"address_2",
|
||
"country",
|
||
"code_postal",
|
||
"city",
|
||
"reservation_date",
|
||
"reservation_time",
|
||
"date_of_cancellation",
|
||
"time_of_cancellation",
|
||
"navigator"
|
||
|
||
);
|
||
|
||
$subscribers = $this->event_registration_model->get_event_unsubscription($event_schedule_id);
|
||
|
||
return array(
|
||
"column_names" => $column_names,
|
||
"data" => $subscribers
|
||
);
|
||
}
|
||
|
||
public function export_waitlist($event_schedule_id)
|
||
{
|
||
/*BASIC SUBCRIBERS LIST EXPORT TEMPLATE*/
|
||
$column_names = array(
|
||
|
||
"civility",
|
||
"last_name",
|
||
"first_name",
|
||
"birth_date",
|
||
"total_booked",
|
||
"email_address",
|
||
"mobile_number",
|
||
"address_1",
|
||
"address_2",
|
||
"country",
|
||
"code_postal",
|
||
"city",
|
||
"reservation_date",
|
||
"reservation_time",
|
||
"navigator"
|
||
|
||
);
|
||
|
||
$subscribers = $this->event_registration_model->get_event_waitlist($event_schedule_id);
|
||
|
||
return array(
|
||
"column_names" => $column_names,
|
||
"data" => $subscribers
|
||
);
|
||
}
|
||
|
||
private function event_header_title($objPHPExcelSheet, $event_title, $columns){
|
||
$first_letter = Coordinate::stringFromColumnIndex(1);
|
||
$last_letter = Coordinate::stringFromColumnIndex(count($columns));
|
||
$header_range = "{$first_letter}1:{$last_letter}1";
|
||
$objPHPExcelSheet->setCellValueByColumnAndRow(1, 1, mb_strtoupper($event_title));
|
||
$objPHPExcelSheet->mergeCells($header_range);
|
||
$generalStyleArray = [
|
||
'font' => [
|
||
'bold' => true,
|
||
],
|
||
'alignment' => [
|
||
'horizontal' => Alignment::HORIZONTAL_CENTER,
|
||
],
|
||
'fill' => [
|
||
'fillType' => Fill::FILL_SOLID,
|
||
'rotation' => 90,
|
||
'startColor' => [
|
||
'argb' => '428bca',
|
||
],
|
||
'endColor' => [
|
||
'argb' => '428bca',
|
||
],
|
||
],
|
||
];
|
||
|
||
$boderStyle = ['borderStyle' => Border::BORDER_THIN];
|
||
$boderStyleArray = [
|
||
'borders' => [
|
||
'top' => $boderStyle,
|
||
'right' => $boderStyle,
|
||
'left' => $boderStyle,
|
||
'bottom' => $boderStyle
|
||
]
|
||
];
|
||
// set style
|
||
$objPHPExcelSheet->getStyle($header_range)->applyFromArray($generalStyleArray);
|
||
$objPHPExcelSheet->getRowDimension('1')->setRowHeight(40);
|
||
}
|
||
|
||
public function event_data($event_schedule_id, $queryData){
|
||
/*BASIC SUBCRIBERS LIST EXPORT TEMPLATE*/
|
||
$column_names = array("event_title", "event_type", "place_town", "date_month", "hour",
|
||
"total_places_avl", "num_remaining_places", "price_rate","event_statuses","description", "sessions");
|
||
|
||
$events = $this->event_model->get_events_list_export($queryData, 1, $event_schedule_id);
|
||
$sessions = $this->event_schedule_model->get_event_schedule($events[0]->event_id);
|
||
$events[0]=(object)array_merge((array)$events[0], ["sessions" => (array)$sessions]);
|
||
return array(
|
||
"column_names" => $column_names,
|
||
"data" => $events
|
||
);
|
||
}
|
||
}
|