2441 lines
116 KiB
PHP
2441 lines
116 KiB
PHP
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
|
||
|
||
use app\core\utils\Response;
|
||
use app\core\auth\Page;
|
||
|
||
//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;
|
||
|
||
//require excel protection for lock/unlock
|
||
use PhpOffice\PhpSpreadsheet\Style\Protection;
|
||
|
||
//require excel contitions
|
||
use PhpOffice\PhpSpreadsheet\Style\Conditional;
|
||
|
||
//require excel datavalidations
|
||
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
|
||
|
||
class Dashboard_export_controller 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;
|
||
|
||
/**
|
||
* variable that set iteration to row 2 which is the datasets not headers
|
||
*
|
||
* @var string
|
||
*/
|
||
private $dataset;
|
||
|
||
/**
|
||
* instance variable for activesheet
|
||
*
|
||
* @var string
|
||
*/
|
||
private $sheets;
|
||
|
||
/**
|
||
* variable default font size for excel exports
|
||
*
|
||
* @var string
|
||
*/
|
||
private $default_font;
|
||
|
||
/**
|
||
* 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;
|
||
|
||
/**
|
||
* EXCEL PASSWORD PROTECTION THIS IS CONFIDENTIAL
|
||
*/
|
||
private const PASSWORD = '1@#]00YHkBlackHatzZ';
|
||
|
||
public function __construct() {
|
||
$this->my_parent_controller();
|
||
Response::handleSessionTimeout("bo");
|
||
$this->load_language_backoffice();
|
||
Page::authorize(PAGE_CODE['dashboard'], PRIVS[PAGE_CODE['dashboard']]['view'], true);
|
||
$this->clear_cache();
|
||
|
||
$this->lang->load('backoffice/dashboard_lang', 'fr');
|
||
|
||
// 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);
|
||
|
||
//tab title
|
||
$this->sheetTitle = "dashboard_data";
|
||
|
||
//no show excel tabs pagination
|
||
$this->reports = "data_fields";
|
||
|
||
//start iteration in row 6 dataset exclude row 1 - 5(which is the excel main pane headers)
|
||
$this->dataset = 6;
|
||
|
||
//set default excel font size which is 8
|
||
$this->default_font = 8;
|
||
|
||
//Default maximum row
|
||
$this->defaultMaximumRow = 2000;
|
||
|
||
//set default background color
|
||
$this->default_background_color = '428bcb';
|
||
|
||
$this->load->model('dashboard_statistics_model');
|
||
}
|
||
/**
|
||
* 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("No show export import file")
|
||
->setKeywords("office 2007 openxml php")
|
||
->setCategory("Export file");
|
||
}
|
||
|
||
/**
|
||
* Set headers(pragma, cache control , cookies etc..)
|
||
*
|
||
* @param string $filename
|
||
* @return void
|
||
*/
|
||
public function setHeaders($filename)
|
||
{
|
||
// 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="'.$filename.'.xlsx"');
|
||
header('Cache-Control: max-age=0');
|
||
|
||
// 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()
|
||
{
|
||
foreach ($this->spreadsheet->getWorksheetIterator() as $worksheet) {
|
||
$this->spreadsheet->setActiveSheetIndex($this->spreadsheet->getIndex($worksheet));
|
||
|
||
$sheet = $this->sheets;
|
||
$cellIterator = $sheet->getRowIterator()->current()->getCellIterator();
|
||
$cellIterator->setIterateOnlyExistingCells(true);
|
||
foreach ($cellIterator as $cell) {
|
||
$sheet->getColumnDimension($cell->getColumn())->setAutoSize(true);
|
||
}
|
||
}
|
||
}
|
||
|
||
/**
|
||
* set excel main headers (first rows 1 - 5 with freeze pane)
|
||
*
|
||
* @param integer $eventID
|
||
* @param integer $eventScheduleID
|
||
* @param string $event_title
|
||
* @return void
|
||
* access private member
|
||
*/
|
||
private function setExcelHeaders()
|
||
{
|
||
/*create four tabs*/
|
||
/*
|
||
* first sheet
|
||
* headers for the graph data
|
||
*/
|
||
$this->sheets->getRowDimension(1)->setRowHeight(-1);
|
||
foreach(range('A','U') as $columnID) {
|
||
$this->sheets->getColumnDimension($columnID)->setAutoSize(true);
|
||
}
|
||
// merge and set cell values
|
||
$this->sheets->freezePane('B5')
|
||
->setTitle('F-Général (données graphiques)')
|
||
->mergeCells('B2:V2')
|
||
->setCellValue('B2', 'Catégorie')
|
||
|
||
->mergeCells('A2:A4')
|
||
->setCellValue('A2', 'Date')
|
||
|
||
->mergeCells('B3:D3')
|
||
->setCellValue('B3', 'Visiteurs')
|
||
->setCellValue('D4', $this->lang->line('statistics')['connected_users'])
|
||
->setCellValue('C4', $this->lang->line('statistics')['known_users'])
|
||
->setCellValue('B4', $this->lang->line('statistics')['new_users'])
|
||
|
||
->mergeCells('E3:G3')
|
||
->setCellValue('E3', $this->lang->line('statistics')['unique_sessions_per_subscriber'])
|
||
->setCellValue('G4', $this->lang->line('statistics')['connected_users'])
|
||
->setCellValue('F4', $this->lang->line('statistics')['known_users'])
|
||
->setCellValue('E4', $this->lang->line('statistics')['new_users'])
|
||
|
||
->mergeCells('H3:J3')
|
||
->setCellValue('H3', $this->lang->line('statistics')['number_of_sessions'])
|
||
->setCellValue('J4', $this->lang->line('statistics')['connected_users'])
|
||
->setCellValue('I4', $this->lang->line('statistics')['known_users'])
|
||
->setCellValue('H4', $this->lang->line('statistics')['new_users'])
|
||
|
||
->mergeCells('K3:M3')
|
||
->setCellValue('K3', $this->lang->line('statistics')['export']['page_view'])
|
||
->setCellValue('M4', $this->lang->line('statistics')['connected_users'])
|
||
->setCellValue('L4', $this->lang->line('statistics')['known_users'])
|
||
->setCellValue('K4', $this->lang->line('statistics')['new_users'])
|
||
|
||
->mergeCells('N3:P3')
|
||
->setCellValue('N3', $this->lang->line('statistics')['unique_page_view'])
|
||
->setCellValue('P4', $this->lang->line('statistics')['connected_users'])
|
||
->setCellValue('O4', $this->lang->line('statistics')['known_users'])
|
||
->setCellValue('N4', $this->lang->line('statistics')['new_users'])
|
||
|
||
->mergeCells('Q3:S3')
|
||
->setCellValue('Q3', $this->lang->line('statistics')['avg_events_views'])
|
||
->setCellValue('S4', $this->lang->line('statistics')['connected_users'])
|
||
->setCellValue('R4', $this->lang->line('statistics')['known_users'])
|
||
->setCellValue('Q4', $this->lang->line('statistics')['new_users'])
|
||
|
||
->mergeCells('T3:V3')
|
||
->setCellValue('T3', $this->lang->line('statistics')['bounce_rate'])
|
||
->setCellValue('V4', $this->lang->line('statistics')['connected_users'])
|
||
->setCellValue('U4', $this->lang->line('statistics')['known_users'])
|
||
->setCellValue('T4', $this->lang->line('statistics')['new_users'])
|
||
|
||
|
||
// set style
|
||
->getStyle('A2:V4')->applyFromArray([
|
||
'font' => [
|
||
'bold' => true
|
||
],
|
||
'alignment' => array(
|
||
'horizontal' => Alignment::HORIZONTAL_CENTER,
|
||
)
|
||
]);
|
||
/*
|
||
* end first sheet headers
|
||
*/
|
||
|
||
/*FILTERED DATA*/
|
||
/*
|
||
* fifth sheet
|
||
*/
|
||
|
||
// merge and set cell values
|
||
|
||
/*
|
||
* end fourth sheet headers
|
||
*/
|
||
|
||
/*
|
||
* second sheet
|
||
* headers for the graph data
|
||
*/
|
||
$objWorksheet = $this->spreadsheet->createSheet(1);
|
||
$objWorksheet->setTitle('F-Général (Web et Devices');
|
||
|
||
$objWorksheet->getRowDimension(1)->setRowHeight(-1);
|
||
foreach(range('A','V') as $columnID) {
|
||
$objWorksheet->getColumnDimension($columnID)->setAutoSize(true);
|
||
}
|
||
// merge and set cell values
|
||
$objWorksheet->freezePane('A4')
|
||
->mergeCells('A2:B2')
|
||
->setCellValue('A2', $this->lang->line('statistics')['web_browsers'])
|
||
->setCellValue('A3', $this->lang->line('statistics')['browser_type'])
|
||
->setCellValue('B3', $this->lang->line('statistics')['number_of_users'])
|
||
// ->setCellValue('C3', 'Year')
|
||
|
||
->mergeCells('E2:F2')
|
||
->setCellValue('E2', $this->lang->line('statistics')['device_type'])
|
||
->setCellValue('E3', $this->lang->line('statistics')['type'])
|
||
->setCellValue('F3', $this->lang->line('statistics')['number_of_users'])
|
||
// ->setCellValue('G3', 'Year')
|
||
|
||
->mergeCells('I2:J2')
|
||
->setCellValue('I2', $this->lang->line('statistics')['link_clicks'])
|
||
->setCellValue('I3', $this->lang->line('statistics')['link_url'])
|
||
->setCellValue('J3', $this->lang->line('statistics')['number_of_clicks'])
|
||
// ->setCellValue('K3', 'Year')
|
||
|
||
->mergeCells('M2:N2')
|
||
->setCellValue('M2', $this->lang->line('statistics')['sent_forms'])
|
||
->setCellValue('M3', $this->lang->line('statistics')['category'])
|
||
->setCellValue('N3', $this->lang->line('statistics')['number_of_clicks'])
|
||
// ->setCellValue('O3', 'Year')
|
||
;
|
||
|
||
// set style
|
||
$objWorksheet->getStyle('A2:V3')->applyFromArray([
|
||
'font' => [
|
||
'bold' => true
|
||
],
|
||
'alignment' => array(
|
||
'horizontal' => Alignment::HORIZONTAL_CENTER,
|
||
)
|
||
])->getAlignment()->setWrapText(true);
|
||
/*
|
||
* end second sheet headers
|
||
*/
|
||
|
||
/*
|
||
* third sheet
|
||
* headers for the graph data
|
||
*/
|
||
$objWorksheet = $this->spreadsheet->createSheet(2);
|
||
$objWorksheet->setTitle('F-Abonnes');
|
||
|
||
$objWorksheet->getRowDimension(2)->setRowHeight(60);
|
||
foreach(range('A','V') as $columnID) {
|
||
$objWorksheet->getColumnDimension($columnID)->setAutoSize(true);
|
||
}
|
||
// merge and set cell values
|
||
$objWorksheet->freezePane('A4')
|
||
->mergeCells('A2:B2')
|
||
->setCellValue('A2', "{$this->lang->line('statistics')['number_of_subscriptions']} ({$this->lang->line('statistics')['new_who_confirmed']}")
|
||
->setCellValue('A3', $this->lang->line('statistics')['number_of_subscriptions'])
|
||
// ->setCellValue('B3', 'Date')
|
||
|
||
->mergeCells('E2:F2')
|
||
->setCellValue('E2', $this->lang->line('statistics')['events_per_status'])
|
||
->setCellValue('E3', $this->lang->line('statistics')['status'])
|
||
->setCellValue('F3', $this->lang->line('statistics')['number_of_users'])
|
||
// ->setCellValue('G3', 'Year')
|
||
|
||
->mergeCells('I2:J2')
|
||
->setCellValue('I2', $this->lang->line('statistics')['avg_subscription_per_user'].' (par abonnés)')
|
||
->setCellValue('I3', $this->lang->line('statistics')['avg_subscription'])
|
||
// ->setCellValue('J3', 'Year')
|
||
|
||
->mergeCells('L2:M2')
|
||
->setCellValue('L2', $this->lang->line('statistics')['total_reserved_places'])
|
||
->setCellValue('L3', $this->lang->line('statistics')['total_reserved_places'])
|
||
// ->setCellValue('M3', 'Year')
|
||
|
||
->mergeCells('O2:P2')
|
||
->setCellValue('O2', $this->lang->line('statistics')['total_cancellation_of_reservation'].' (annulation totale de réservation)')
|
||
->setCellValue('O3', $this->lang->line('statistics')['total_cancelled_places'])
|
||
// ->setCellValue('P3', 'Year')
|
||
;
|
||
|
||
// set style
|
||
$objWorksheet->getStyle('A2:V2')->applyFromArray([
|
||
'font' => [
|
||
'bold' => true
|
||
],
|
||
'alignment' => array(
|
||
'horizontal' => Alignment::HORIZONTAL_CENTER,
|
||
)
|
||
])->getAlignment()->setWrapText(true);
|
||
|
||
$objWorksheet->getStyle('A2:A2')->applyFromArray([
|
||
'font' => [
|
||
'bold' => true
|
||
],
|
||
'alignment' => array(
|
||
'horizontal' => Alignment::HORIZONTAL_CENTER,
|
||
)
|
||
])->getAlignment()->setWrapText(true);
|
||
$objWorksheet->getStyle('E2:F2')->applyFromArray([
|
||
'font' => [
|
||
'bold' => true
|
||
],
|
||
'alignment' => array(
|
||
'horizontal' => Alignment::HORIZONTAL_CENTER,
|
||
)
|
||
])->getAlignment()->setWrapText(true);
|
||
$objWorksheet->getStyle('I2:I2')->applyFromArray([
|
||
'font' => [
|
||
'bold' => true
|
||
],
|
||
'alignment' => array(
|
||
'horizontal' => Alignment::HORIZONTAL_CENTER,
|
||
)
|
||
])->getAlignment()->setWrapText(true);
|
||
$objWorksheet->getStyle('L2:L2')->applyFromArray([
|
||
'font' => [
|
||
'bold' => true
|
||
],
|
||
'alignment' => array(
|
||
'horizontal' => Alignment::HORIZONTAL_CENTER,
|
||
)
|
||
])->getAlignment()->setWrapText(true);
|
||
$objWorksheet->getStyle('O2:O2')->applyFromArray([
|
||
'font' => [
|
||
'bold' => true
|
||
],
|
||
'alignment' => array(
|
||
'horizontal' => Alignment::HORIZONTAL_CENTER,
|
||
)
|
||
])->getAlignment()->setWrapText(true);
|
||
/*
|
||
* end third sheet headers
|
||
*/
|
||
|
||
/*
|
||
* fourth sheet
|
||
* headers for the graph data
|
||
*/
|
||
$objWorksheet = $this->spreadsheet->createSheet(3);
|
||
$objWorksheet->setTitle('F-Recherches');
|
||
|
||
$objWorksheet->getRowDimension(1)->setRowHeight(-1);
|
||
foreach(range('A','U') as $columnID) {
|
||
$objWorksheet->getColumnDimension($columnID)->setAutoSize(true);
|
||
}
|
||
// merge and set cell values
|
||
$objWorksheet->freezePane('A5')
|
||
->mergeCells('A2:K2')
|
||
->setCellValue('A2', $this->lang->line('statistics')['internal_research'])
|
||
|
||
->mergeCells('A3:B3')
|
||
->setCellValue('A3', $this->lang->line('statistics')['by_type_of_event'])
|
||
->setCellValue('A4', $this->lang->line('statistics')['event_type'])
|
||
->setCellValue('B4', $this->lang->line('statistics')['number'])
|
||
// ->setCellValue('C4', 'Year')
|
||
|
||
->mergeCells('E3:F3')
|
||
->setCellValue('E3', $this->lang->line('statistics')['by_month'])
|
||
->setCellValue('E4', $this->lang->line('statistics')['month'])
|
||
->setCellValue('F4', $this->lang->line('statistics')['number'])
|
||
// ->setCellValue('G4', 'Year')
|
||
|
||
->mergeCells('I3:J3')
|
||
->setCellValue('I3', $this->lang->line('statistics')['by_city'])
|
||
->setCellValue('I4', $this->lang->line('statistics')['city'])
|
||
->setCellValue('J4', $this->lang->line('statistics')['number'])
|
||
// ->setCellValue('K4', 'Year')
|
||
;
|
||
|
||
$objWorksheet->mergeCells('M2:N2')
|
||
->setCellValue('M2', $this->lang->line('statistics')['external_research'])
|
||
->mergeCells('M3:N3')
|
||
->setCellValue('M3', $this->lang->line('statistics')['search_syntax'])
|
||
->setCellValue('M4', $this->lang->line('statistics')['expressions'])
|
||
->setCellValue('N4', $this->lang->line('statistics')['number'])
|
||
// ->setCellValue('O4', 'Year')
|
||
|
||
->mergeCells('Q3:S3')
|
||
->setCellValue('Q3', $this->lang->line('statistics')['origin'])
|
||
->setCellValue('Q4', $this->lang->line('statistics')['source'])
|
||
// ->setCellValue('R4', 'Source moyenne')
|
||
->setCellValue('R4', $this->lang->line('statistics')['channel'])
|
||
->setCellValue('S4', $this->lang->line('statistics')['number_of_users'])
|
||
// ->setCellValue('U4', 'Year')
|
||
;
|
||
|
||
// set style
|
||
$objWorksheet->getStyle('A2:U4')->applyFromArray([
|
||
'font' => [
|
||
'bold' => true
|
||
],
|
||
'alignment' => array(
|
||
'horizontal' => Alignment::HORIZONTAL_CENTER,
|
||
)
|
||
])->getAlignment()->setWrapText(true);
|
||
/*
|
||
* end fourth sheet headers
|
||
*/
|
||
}
|
||
|
||
/*ALL DATA*/
|
||
// private function setExcelHeaders()
|
||
// {
|
||
// /*create four tabs*/
|
||
// /*
|
||
// * first sheet
|
||
// * headers for the graph data
|
||
// */
|
||
//
|
||
// // merge and set cell values
|
||
// $this->sheets->freezePane('B4')
|
||
// ->setTitle('General (graph data)')
|
||
// ->mergeCells('B1:V1')
|
||
// ->setCellValue('B1', 'Category')
|
||
//
|
||
// ->mergeCells('A1:A3')
|
||
// ->setCellValue('A1', 'Date')
|
||
//
|
||
// ->mergeCells('B2:D2')
|
||
// ->setCellValue('B2', 'Visiteurs')
|
||
// ->setCellValue('D3', $this->lang->line('statistics')['connected_users'])
|
||
// ->setCellValue('C3', $this->lang->line('statistics')['known_users'])
|
||
// ->setCellValue('B3', $this->lang->line('statistics')['new_users'])
|
||
//
|
||
// ->mergeCells('E2:G2')
|
||
// ->setCellValue('E2', $this->lang->line('statistics')['unique_sessions_per_subscriber'])
|
||
// ->setCellValue('G3', $this->lang->line('statistics')['connected_users'])
|
||
// ->setCellValue('F3', $this->lang->line('statistics')['known_users'])
|
||
// ->setCellValue('E3', $this->lang->line('statistics')['new_users'])
|
||
//
|
||
// ->mergeCells('H2:J2')
|
||
// ->setCellValue('H2', 'Sessions duration')
|
||
// ->setCellValue('J3', $this->lang->line('statistics')['connected_users'])
|
||
// ->setCellValue('I3', $this->lang->line('statistics')['known_users'])
|
||
// ->setCellValue('H3', $this->lang->line('statistics')['new_users'])
|
||
//
|
||
// ->mergeCells('K2:M2')
|
||
// ->setCellValue('K2', $this->lang->line('statistics')['export']['page_view'])
|
||
// ->setCellValue('M3', $this->lang->line('statistics')['connected_users'])
|
||
// ->setCellValue('L3', $this->lang->line('statistics')['known_users'])
|
||
// ->setCellValue('K3', $this->lang->line('statistics')['new_users'])
|
||
//
|
||
// ->mergeCells('N2:P2')
|
||
// ->setCellValue('N2', 'Unique pageviews')
|
||
// ->setCellValue('P3', $this->lang->line('statistics')['connected_users'])
|
||
// ->setCellValue('O3', $this->lang->line('statistics')['known_users'])
|
||
// ->setCellValue('N3', $this->lang->line('statistics')['new_users'])
|
||
//
|
||
// ->mergeCells('Q2:S2')
|
||
// ->setCellValue('Q2', 'Event views')
|
||
// ->setCellValue('S3', $this->lang->line('statistics')['connected_users'])
|
||
// ->setCellValue('R3', $this->lang->line('statistics')['known_users'])
|
||
// ->setCellValue('Q3', $this->lang->line('statistics')['new_users'])
|
||
//
|
||
// ->mergeCells('T2:V2')
|
||
// ->setCellValue('T2', 'Bounce Rate')
|
||
// ->setCellValue('V3', $this->lang->line('statistics')['connected_users'])
|
||
// ->setCellValue('U3', $this->lang->line('statistics')['known_users'])
|
||
// ->setCellValue('T3', $this->lang->line('statistics')['new_users'])
|
||
//
|
||
//
|
||
// // set style
|
||
// ->getStyle('A1:V3')->applyFromArray([
|
||
// 'font' => [
|
||
// 'bold' => true
|
||
// ],
|
||
// 'alignment' => array(
|
||
// 'horizontal' => Alignment::HORIZONTAL_CENTER,
|
||
// )
|
||
// ]);
|
||
// /*
|
||
// * end first sheet headers
|
||
// */
|
||
//
|
||
// /*
|
||
// * second sheet
|
||
// * headers for the graph data
|
||
// */
|
||
// $objWorksheet = $this->spreadsheet->createSheet(1);
|
||
// $objWorksheet->setTitle('General (Web & Device type)');
|
||
//
|
||
// $objWorksheet->getRowDimension(1)->setRowHeight(-1);
|
||
// foreach(range('A','V') as $columnID) {
|
||
// $objWorksheet->getColumnDimension($columnID)->setAutoSize(true);
|
||
// }
|
||
// // merge and set cell values
|
||
// $objWorksheet->freezePane('A3')
|
||
// ->mergeCells('A1:C1')
|
||
// ->setCellValue('A1', 'Navigateurs Web')
|
||
// ->setCellValue('A2', 'Browser Type')
|
||
// ->setCellValue('B2', 'Nbre')
|
||
// ->setCellValue('C2', 'Year')
|
||
//
|
||
// ->mergeCells('E1:G1')
|
||
// ->setCellValue('E1', 'Type de device')
|
||
// ->setCellValue('E2', 'Device type')
|
||
// ->setCellValue('F2', 'Nbre')
|
||
// ->setCellValue('G2', 'Year')
|
||
//
|
||
// ->mergeCells('I1:K1')
|
||
// ->setCellValue('I1', 'Liens Cliques')
|
||
// ->setCellValue('I2', 'Url Link')
|
||
// ->setCellValue('J2', 'Clicks')
|
||
// ->setCellValue('K2', 'Year')
|
||
//
|
||
// ->mergeCells('M1:O1')
|
||
// ->setCellValue('M1', 'Formulaires envoyés')
|
||
// ->setCellValue('M2', 'Category')
|
||
// ->setCellValue('N2', 'Clicks')
|
||
// ->setCellValue('O2', 'Year');
|
||
//
|
||
// // set style
|
||
// $objWorksheet->getStyle('A1:V2')->applyFromArray([
|
||
// 'font' => [
|
||
// 'bold' => true
|
||
// ],
|
||
// 'alignment' => array(
|
||
// 'horizontal' => Alignment::HORIZONTAL_CENTER,
|
||
// )
|
||
// ])->getAlignment()->setWrapText(true);
|
||
// /*
|
||
// * end second sheet headers
|
||
// */
|
||
//
|
||
// /*
|
||
// * third sheet
|
||
// * headers for the graph data
|
||
// */
|
||
// $objWorksheet = $this->spreadsheet->createSheet(2);
|
||
// $objWorksheet->setTitle('Abonnes');
|
||
//
|
||
// $objWorksheet->getRowDimension(1)->setRowHeight(60);
|
||
// foreach(range('A','V') as $columnID) {
|
||
// $objWorksheet->getColumnDimension($columnID)->setAutoSize(true);
|
||
// }
|
||
// // merge and set cell values
|
||
// $objWorksheet->freezePane('A3')
|
||
// ->mergeCells('A1:B1')
|
||
// ->setCellValue('A1', "Nombre d'inscriptions (Nouveau qui a confirmé son formulaire de première inscription)")
|
||
// ->setCellValue('A2', 'Nbre')
|
||
// ->setCellValue('B2', 'Year')
|
||
//
|
||
// ->mergeCells('E1:G1')
|
||
// ->setCellValue('E1', "Nombre d'événements par statuts (prochainement, en cours, complets, terminés, passés, archivés) total + /année")
|
||
// ->setCellValue('E2', 'Status')
|
||
// ->setCellValue('F2', 'Nbre')
|
||
// ->setCellValue('G2', 'Year')
|
||
//
|
||
// ->mergeCells('I1:J1')
|
||
// ->setCellValue('I1', "Nombre de réservations effectuées (par abonnés)")
|
||
// ->setCellValue('I2', 'Nbre')
|
||
// ->setCellValue('J2', 'Year')
|
||
//
|
||
// ->mergeCells('L1:M1')
|
||
// ->setCellValue('L1', "Nombre de places réservées")
|
||
// ->setCellValue('L2', 'Nbre')
|
||
// ->setCellValue('M2', 'Year')
|
||
//
|
||
// ->mergeCells('O1:P1')
|
||
// ->setCellValue('O1', "Nombre d'annulations effectuées (annulation totale de réservation)")
|
||
// ->setCellValue('O2', 'Nbre')
|
||
// ->setCellValue('P2', 'Year')
|
||
// ;
|
||
//
|
||
// // set style
|
||
// $objWorksheet->getStyle('A1:V2')->applyFromArray([
|
||
// 'font' => [
|
||
// 'bold' => true
|
||
// ],
|
||
// 'alignment' => array(
|
||
// 'horizontal' => Alignment::HORIZONTAL_CENTER,
|
||
// )
|
||
// ])->getAlignment()->setWrapText(true);
|
||
// /*
|
||
// * end third sheet headers
|
||
// */
|
||
//
|
||
// /*
|
||
// * fourth sheet
|
||
// * headers for the graph data
|
||
// */
|
||
// $objWorksheet = $this->spreadsheet->createSheet(3);
|
||
// $objWorksheet->setTitle('Recherches');
|
||
//
|
||
// $objWorksheet->getRowDimension(1)->setRowHeight(-1);
|
||
// foreach(range('A','U') as $columnID) {
|
||
// $objWorksheet->getColumnDimension($columnID)->setAutoSize(true);
|
||
// }
|
||
// // merge and set cell values
|
||
// $objWorksheet->freezePane('A4')
|
||
// ->mergeCells('A1:K1')
|
||
// ->setCellValue('A1', "Recherches Internes")
|
||
//
|
||
// ->mergeCells('A2:C2')
|
||
// ->setCellValue('A2', 'By event type')
|
||
// ->setCellValue('A3', 'Event type')
|
||
// ->setCellValue('B3', 'Nbre')
|
||
// ->setCellValue('C3', 'Year')
|
||
//
|
||
// ->mergeCells('E2:G2')
|
||
// ->setCellValue('E2', 'By month')
|
||
// ->setCellValue('E3', 'Month')
|
||
// ->setCellValue('F3', 'Nbre')
|
||
// ->setCellValue('G3', 'Year')
|
||
//
|
||
// ->mergeCells('I2:K2')
|
||
// ->setCellValue('I2', 'By location')
|
||
// ->setCellValue('I3', 'Location')
|
||
// ->setCellValue('J3', 'Nbre')
|
||
// ->setCellValue('K3', 'Year');
|
||
//
|
||
// $objWorksheet->mergeCells('M1:O1')
|
||
// ->setCellValue('M1', "Recherches Externes")
|
||
// ->mergeCells('M2:O2')
|
||
// ->setCellValue('M2', 'Syntaxe de recherche')
|
||
// ->setCellValue('M3', 'Expressions')
|
||
// ->setCellValue('N3', 'Nbre')
|
||
// ->setCellValue('O3', 'Year')
|
||
//
|
||
// ->mergeCells('Q2:U2')
|
||
// ->setCellValue('Q2', "Provenance")
|
||
// ->setCellValue('Q3', 'Source')
|
||
// ->setCellValue('R3', 'Source Medium')
|
||
// ->setCellValue('S3', 'Channel')
|
||
// ->setCellValue('T3', 'Nbre')
|
||
// ->setCellValue('U3', 'Year')
|
||
// ;
|
||
//
|
||
// // set style
|
||
// $objWorksheet->getStyle('A1:U3')->applyFromArray([
|
||
// 'font' => [
|
||
// 'bold' => true
|
||
// ],
|
||
// 'alignment' => array(
|
||
// 'horizontal' => Alignment::HORIZONTAL_CENTER,
|
||
// )
|
||
// ])->getAlignment()->setWrapText(true);
|
||
// /*
|
||
// * end fourth sheet headers
|
||
// */
|
||
//
|
||
//
|
||
// /*FILTERED DATA*/
|
||
// /*
|
||
// * fifth sheet
|
||
// */
|
||
// $objWorksheet = $this->spreadsheet->createSheet(4);
|
||
// $objWorksheet->getRowDimension(1)->setRowHeight(-1);
|
||
// foreach(range('A','U') as $columnID) {
|
||
// $objWorksheet->getColumnDimension($columnID)->setAutoSize(true);
|
||
// }
|
||
// // merge and set cell values
|
||
//
|
||
// $objWorksheet->freezePane('B5')
|
||
// ->setTitle('F-General (graph data)')
|
||
// ->mergeCells('B2:V2')
|
||
// ->setCellValue('B2', 'Category')
|
||
//
|
||
// ->mergeCells('A2:A4')
|
||
// ->setCellValue('A2', 'Date')
|
||
//
|
||
// ->mergeCells('B3:D3')
|
||
// ->setCellValue('B3', 'Visiteurs')
|
||
// ->setCellValue('D4', $this->lang->line('statistics')['connected_users'])
|
||
// ->setCellValue('C4', $this->lang->line('statistics')['known_users'])
|
||
// ->setCellValue('B4', $this->lang->line('statistics')['new_users'])
|
||
//
|
||
// ->mergeCells('E3:G3')
|
||
// ->setCellValue('E3', $this->lang->line('statistics')['unique_sessions_per_subscriber'])
|
||
// ->setCellValue('G4', $this->lang->line('statistics')['connected_users'])
|
||
// ->setCellValue('F4', $this->lang->line('statistics')['known_users'])
|
||
// ->setCellValue('E4', $this->lang->line('statistics')['new_users'])
|
||
//
|
||
// ->mergeCells('H3:J3')
|
||
// ->setCellValue('H3', 'Sessions duration')
|
||
// ->setCellValue('J4', $this->lang->line('statistics')['connected_users'])
|
||
// ->setCellValue('I4', $this->lang->line('statistics')['known_users'])
|
||
// ->setCellValue('H4', $this->lang->line('statistics')['new_users'])
|
||
//
|
||
// ->mergeCells('K3:M3')
|
||
// ->setCellValue('K3', $this->lang->line('statistics')['export']['page_view'])
|
||
// ->setCellValue('M4', $this->lang->line('statistics')['connected_users'])
|
||
// ->setCellValue('L4', $this->lang->line('statistics')['known_users'])
|
||
// ->setCellValue('K4', $this->lang->line('statistics')['new_users'])
|
||
//
|
||
// ->mergeCells('N3:P3')
|
||
// ->setCellValue('N3', 'Unique pageviews')
|
||
// ->setCellValue('P4', $this->lang->line('statistics')['connected_users'])
|
||
// ->setCellValue('O4', $this->lang->line('statistics')['known_users'])
|
||
// ->setCellValue('N4', $this->lang->line('statistics')['new_users'])
|
||
//
|
||
// ->mergeCells('Q3:S3')
|
||
// ->setCellValue('Q3', 'Event views')
|
||
// ->setCellValue('S4', $this->lang->line('statistics')['connected_users'])
|
||
// ->setCellValue('R4', $this->lang->line('statistics')['known_users'])
|
||
// ->setCellValue('Q4', $this->lang->line('statistics')['new_users'])
|
||
//
|
||
// ->mergeCells('T3:V3')
|
||
// ->setCellValue('T3', 'Bounce Rate')
|
||
// ->setCellValue('V4', $this->lang->line('statistics')['connected_users'])
|
||
// ->setCellValue('U4', $this->lang->line('statistics')['known_users'])
|
||
// ->setCellValue('T4', $this->lang->line('statistics')['new_users'])
|
||
//
|
||
//
|
||
// // set style
|
||
// ->getStyle('A2:V4')->applyFromArray([
|
||
// 'font' => [
|
||
// 'bold' => true
|
||
// ],
|
||
// 'alignment' => array(
|
||
// 'horizontal' => Alignment::HORIZONTAL_CENTER,
|
||
// )
|
||
// ]);
|
||
// /*
|
||
// * end first sheet headers
|
||
// */
|
||
// /*
|
||
// * end fourth sheet headers
|
||
// */
|
||
//
|
||
// /*
|
||
// * second sheet
|
||
// * headers for the graph data
|
||
// */
|
||
// $objWorksheet = $this->spreadsheet->createSheet(5);
|
||
// $objWorksheet->setTitle('F-General (Web & Device type)');
|
||
//
|
||
// $objWorksheet->getRowDimension(1)->setRowHeight(-1);
|
||
// foreach(range('A','V') as $columnID) {
|
||
// $objWorksheet->getColumnDimension($columnID)->setAutoSize(true);
|
||
// }
|
||
// // merge and set cell values
|
||
// $objWorksheet->freezePane('A4')
|
||
// ->mergeCells('A2:B2')
|
||
// ->setCellValue('A2', 'Navigateurs Web')
|
||
// ->setCellValue('A3', 'Browser Type')
|
||
// ->setCellValue('B3', 'Nbre')
|
||
// // ->setCellValue('C3', 'Year')
|
||
//
|
||
// ->mergeCells('E2:F2')
|
||
// ->setCellValue('E2', 'Type de device')
|
||
// ->setCellValue('E3', 'Device type')
|
||
// ->setCellValue('F3', 'Nbre')
|
||
// // ->setCellValue('G3', 'Year')
|
||
//
|
||
// ->mergeCells('I2:J2')
|
||
// ->setCellValue('I2', 'Liens Cliques')
|
||
// ->setCellValue('I3', 'Url Link')
|
||
// ->setCellValue('J3', 'Clicks')
|
||
// // ->setCellValue('K3', 'Year')
|
||
//
|
||
// ->mergeCells('M2:N2')
|
||
// ->setCellValue('M2', 'Formulaires envoyés')
|
||
// ->setCellValue('M3', 'Category')
|
||
// ->setCellValue('N3', 'Clicks')
|
||
// // ->setCellValue('O3', 'Year')
|
||
// ;
|
||
//
|
||
// // set style
|
||
// $objWorksheet->getStyle('A2:V3')->applyFromArray([
|
||
// 'font' => [
|
||
// 'bold' => true
|
||
// ],
|
||
// 'alignment' => array(
|
||
// 'horizontal' => Alignment::HORIZONTAL_CENTER,
|
||
// )
|
||
// ])->getAlignment()->setWrapText(true);
|
||
// /*
|
||
// * end second sheet headers
|
||
// */
|
||
//
|
||
// /*
|
||
// * third sheet
|
||
// * headers for the graph data
|
||
// */
|
||
// $objWorksheet = $this->spreadsheet->createSheet(6);
|
||
// $objWorksheet->setTitle('F-Abonnes');
|
||
//
|
||
// $objWorksheet->getRowDimension(2)->setRowHeight(60);
|
||
// foreach(range('A','V') as $columnID) {
|
||
// $objWorksheet->getColumnDimension($columnID)->setAutoSize(true);
|
||
// }
|
||
// // merge and set cell values
|
||
// $objWorksheet->freezePane('A4')
|
||
// ->mergeCells('A2:B2')
|
||
// ->setCellValue('A2', "Nombre d'inscriptions (Nouveau qui a confirmé son formulaire de première inscription)")
|
||
// ->setCellValue('A3', 'Nbre')
|
||
// // ->setCellValue('B3', 'Date')
|
||
//
|
||
// ->mergeCells('E2:F2')
|
||
// ->setCellValue('E2', "Nombre d'événements par statuts (prochainement, en cours, complets, terminés, passés, archivés) total + /année")
|
||
// ->setCellValue('E3', 'Status')
|
||
// ->setCellValue('F3', 'Nbre')
|
||
// // ->setCellValue('G3', 'Year')
|
||
//
|
||
// ->mergeCells('I2:J2')
|
||
// ->setCellValue('I2', "Nombre de réservations effectuées (par abonnés)")
|
||
// ->setCellValue('I3', 'Nbre')
|
||
// // ->setCellValue('J3', 'Year')
|
||
//
|
||
// ->mergeCells('L2:M2')
|
||
// ->setCellValue('L2', "Nombre de places réservées")
|
||
// ->setCellValue('L3', 'Nbre')
|
||
// // ->setCellValue('M3', 'Year')
|
||
//
|
||
// ->mergeCells('O2:P2')
|
||
// ->setCellValue('O2', "Nombre d'annulations effectuées (annulation totale de réservation)")
|
||
// ->setCellValue('O3', 'Nbre')
|
||
// // ->setCellValue('P3', 'Year')
|
||
// ;
|
||
//
|
||
// // set style
|
||
// $objWorksheet->getStyle('A2:V2')->applyFromArray([
|
||
// 'font' => [
|
||
// 'bold' => true
|
||
// ],
|
||
// 'alignment' => array(
|
||
// 'horizontal' => Alignment::HORIZONTAL_CENTER,
|
||
// )
|
||
// ])->getAlignment()->setWrapText(true);
|
||
//
|
||
// $objWorksheet->getStyle('A2:A2')->applyFromArray([
|
||
// 'font' => [
|
||
// 'bold' => true
|
||
// ],
|
||
// 'alignment' => array(
|
||
// 'horizontal' => Alignment::HORIZONTAL_CENTER,
|
||
// )
|
||
// ])->getAlignment()->setWrapText(true);
|
||
// $objWorksheet->getStyle('E2:F2')->applyFromArray([
|
||
// 'font' => [
|
||
// 'bold' => true
|
||
// ],
|
||
// 'alignment' => array(
|
||
// 'horizontal' => Alignment::HORIZONTAL_CENTER,
|
||
// )
|
||
// ])->getAlignment()->setWrapText(true);
|
||
// $objWorksheet->getStyle('I2:I2')->applyFromArray([
|
||
// 'font' => [
|
||
// 'bold' => true
|
||
// ],
|
||
// 'alignment' => array(
|
||
// 'horizontal' => Alignment::HORIZONTAL_CENTER,
|
||
// )
|
||
// ])->getAlignment()->setWrapText(true);
|
||
// $objWorksheet->getStyle('L2:L2')->applyFromArray([
|
||
// 'font' => [
|
||
// 'bold' => true
|
||
// ],
|
||
// 'alignment' => array(
|
||
// 'horizontal' => Alignment::HORIZONTAL_CENTER,
|
||
// )
|
||
// ])->getAlignment()->setWrapText(true);
|
||
// $objWorksheet->getStyle('O2:O2')->applyFromArray([
|
||
// 'font' => [
|
||
// 'bold' => true
|
||
// ],
|
||
// 'alignment' => array(
|
||
// 'horizontal' => Alignment::HORIZONTAL_CENTER,
|
||
// )
|
||
// ])->getAlignment()->setWrapText(true);
|
||
// /*
|
||
// * end third sheet headers
|
||
// */
|
||
//
|
||
// /*
|
||
// * fourth sheet
|
||
// * headers for the graph data
|
||
// */
|
||
// $objWorksheet = $this->spreadsheet->createSheet(7);
|
||
// $objWorksheet->setTitle('F-Recherches');
|
||
//
|
||
// $objWorksheet->getRowDimension(1)->setRowHeight(-1);
|
||
// foreach(range('A','U') as $columnID) {
|
||
// $objWorksheet->getColumnDimension($columnID)->setAutoSize(true);
|
||
// }
|
||
// // merge and set cell values
|
||
// $objWorksheet->freezePane('A5')
|
||
// ->mergeCells('A2:K2')
|
||
// ->setCellValue('A2', "Recherches Internes")
|
||
//
|
||
// ->mergeCells('A3:B3')
|
||
// ->setCellValue('A3', 'By event type')
|
||
// ->setCellValue('A4', 'Event type')
|
||
// ->setCellValue('B4', 'Nbre')
|
||
// // ->setCellValue('C4', 'Year')
|
||
//
|
||
// ->mergeCells('E3:F3')
|
||
// ->setCellValue('E3', 'By month')
|
||
// ->setCellValue('E4', 'Month')
|
||
// ->setCellValue('F4', 'Nbre')
|
||
// // ->setCellValue('G4', 'Year')
|
||
//
|
||
// ->mergeCells('I3:J3')
|
||
// ->setCellValue('I3', 'By location')
|
||
// ->setCellValue('I4', 'Location')
|
||
// ->setCellValue('J4', 'Nbre')
|
||
// // ->setCellValue('K4', 'Year')
|
||
// ;
|
||
//
|
||
// $objWorksheet->mergeCells('M2:N2')
|
||
// ->setCellValue('M2', "Recherches Externes")
|
||
// ->mergeCells('M3:N3')
|
||
// ->setCellValue('M3', 'Syntaxe de recherche')
|
||
// ->setCellValue('M4', 'Expressions')
|
||
// ->setCellValue('N4', 'Nbre')
|
||
// // ->setCellValue('O4', 'Year')
|
||
//
|
||
// ->mergeCells('Q3:T3')
|
||
// ->setCellValue('Q3', "Provenance")
|
||
// ->setCellValue('Q4', 'Source')
|
||
// ->setCellValue('R4', 'Source Medium')
|
||
// ->setCellValue('S4', 'Channel')
|
||
// ->setCellValue('T4', 'Nbre')
|
||
// // ->setCellValue('U4', 'Year')
|
||
// ;
|
||
//
|
||
// // set style
|
||
// $objWorksheet->getStyle('A2:U4')->applyFromArray([
|
||
// 'font' => [
|
||
// 'bold' => true
|
||
// ],
|
||
// 'alignment' => array(
|
||
// 'horizontal' => Alignment::HORIZONTAL_CENTER,
|
||
// )
|
||
// ])->getAlignment()->setWrapText(true);
|
||
// /*
|
||
// * end fourth sheet headers
|
||
// */
|
||
// }
|
||
|
||
/**
|
||
* get data for export = all dashboard data
|
||
*
|
||
* @param int $event_schedule_id
|
||
* @return array
|
||
* access public
|
||
*/
|
||
private function getExportData($sheet, $start ="start_date", $end = "end_date")
|
||
{
|
||
switch ($sheet) {
|
||
case 0:
|
||
// First sheet graph data
|
||
$visitors = $this->dashboard_statistics_model->get_ga_data('visitors', 'vis', '1_2_3', $start, $end, 'DESC');
|
||
$sessions_unique = $this->dashboard_statistics_model->get_ga_data('sessions', 'sess', '1_2_3', $start, $end, 'DESC');
|
||
$sessions_duration = $this->dashboard_statistics_model->get_ga_data('session_duration', 'sess', '1_2_3', $start, $end, 'DESC');
|
||
$pageviews = $this->dashboard_statistics_model->get_ga_data('pageviews', 'pv', '1_2_3', $start, $end, 'DESC');
|
||
$unique_pageviews = $this->dashboard_statistics_model->get_ga_data('unique_pageviews', 'pv', '1_2_3', $start, $end, 'DESC');
|
||
$event_views = $this->dashboard_statistics_model->get_ga_data('event_views', 'ev', '1_2_3', $start, $end, 'DESC');
|
||
|
||
$bounceRate = $this->dashboard_statistics_model->get_ga_data('bounceRate', 'bcr', '1_2_3', $start, $end, 'DESC');
|
||
|
||
$data = array(
|
||
'visitors_graph_data' => $visitors,
|
||
'sessions_graph_data' => $sessions_unique,
|
||
'session_duration_graph_data' => $sessions_duration,
|
||
'pageviews' => $pageviews,
|
||
'unique_pageviews' => $unique_pageviews,
|
||
'event_views' => $event_views,
|
||
'bounceRate' => $bounceRate,
|
||
);
|
||
break;
|
||
case 1:
|
||
$yearly_browsers = $this->dashboard_statistics_model->get_dashboard_table_data('yearly_browsers', $start, $end);
|
||
$yearly_devices = $this->dashboard_statistics_model->get_dashboard_table_data('yearly_devices', $start, $end);
|
||
$yearly_url_clicks = $this->dashboard_statistics_model->get_dashboard_table_data('yearly_url_clicks', $start, $end);
|
||
$yearly_contact_cat = $this->dashboard_statistics_model->get_dashboard_table_data('yearly_contact_cat', $start, $end);
|
||
|
||
$data = array(
|
||
'yearly_browsers' => $yearly_browsers,
|
||
'yearly_devices' => $yearly_devices,
|
||
'yearly_url_clicks' => $yearly_url_clicks,
|
||
'yearly_contact_cat' => $yearly_contact_cat,
|
||
);
|
||
break;
|
||
case 4:
|
||
$filtered_browsers = $this->dashboard_statistics_model->get_dashboard_table_data('filtered_browsers', $start, $end);
|
||
$filtered_devices = $this->dashboard_statistics_model->get_dashboard_table_data('filtered_devices', $start, $end);
|
||
$filtered_url_clicks = $this->dashboard_statistics_model->get_dashboard_table_data('filtered_url_clicks', $start, $end);
|
||
$filtered_contact_cat = $this->dashboard_statistics_model->get_dashboard_table_data('filtered_contact_cat', $start, $end);
|
||
|
||
$data = array(
|
||
'yearly_browsers' => $filtered_browsers,
|
||
'yearly_devices' => $filtered_devices,
|
||
'yearly_url_clicks' => $filtered_url_clicks,
|
||
'yearly_contact_cat' => $filtered_contact_cat,
|
||
);
|
||
break;
|
||
case 2:
|
||
// Nombre d'inscriptions (Nouveau qui a confirmé son formulaire de première inscription)
|
||
$y0 = $this->dashboard_statistics_model->get_dashboard_table_data('y0', $start, $end);
|
||
$yearly_fevents = $this->dashboard_statistics_model->get_dashboard_table_data('yearly_fevents', $start, $end);
|
||
$y2 = $this->dashboard_statistics_model->get_dashboard_table_data('y2', $start, $end);
|
||
$y3 = $this->dashboard_statistics_model->get_dashboard_table_data('y3', $start, $end);
|
||
$y4 = $this->dashboard_statistics_model->get_dashboard_table_data('y4', $start, $end);
|
||
|
||
$data = array(
|
||
'y0' => $y0,
|
||
'yearly_fevents' => $yearly_fevents,
|
||
'y2' => $y2,
|
||
'y3' => $y3,
|
||
'y4' => $y4,
|
||
);
|
||
break;
|
||
case 5:
|
||
// Nombre d'inscriptions (Nouveau qui a confirmé son formulaire de première inscription)
|
||
$f_y0 = $this->dashboard_statistics_model->get_dashboard_table_data('f_y0', $start, $end);
|
||
$f_fevents = $this->dashboard_statistics_model->get_dashboard_table_data('f_fevents', $start, $end);
|
||
$f_y2 = $this->dashboard_statistics_model->get_dashboard_table_data('f_y2', $start, $end);
|
||
$f_y3 = $this->dashboard_statistics_model->get_dashboard_table_data('f_y3', $start, $end);
|
||
$f_y4 = $this->dashboard_statistics_model->get_dashboard_table_data('f_y4', $start, $end);
|
||
|
||
$data = array(
|
||
'y0' => $f_y0,
|
||
'yearly_fevents' => $f_fevents,
|
||
'y2' => $f_y2,
|
||
'y3' => $f_y3,
|
||
'y4' => $f_y4,
|
||
);
|
||
break;
|
||
case 3:
|
||
$yearly_s_evtype = $this->dashboard_statistics_model->get_dashboard_table_data('yearly_s_evtype', $start, $end);
|
||
$yearly_s_month = $this->dashboard_statistics_model->get_dashboard_table_data('yearly_s_month', $start, $end);
|
||
$yearly_s_city = $this->dashboard_statistics_model->get_dashboard_table_data('yearly_s_city', $start, $end);
|
||
$yearly_faq_search = $this->dashboard_statistics_model->get_dashboard_table_data('yearly_faq_search', $start, $end);
|
||
$yearly_source_mediums = $this->dashboard_statistics_model->get_dashboard_table_data('yearly_source_mediums', $start, $end);
|
||
|
||
$data = array(
|
||
'yearly_s_evtype' => $yearly_s_evtype,
|
||
'yearly_s_month' => $yearly_s_month,
|
||
'yearly_s_city' => $yearly_s_city,
|
||
'yearly_faq_search' => $yearly_faq_search,
|
||
'yearly_source_mediums' => $yearly_source_mediums,
|
||
);
|
||
break;
|
||
case 6:
|
||
$f_s_evtype = $this->dashboard_statistics_model->get_dashboard_table_data('f_s_evtype', $start, $end);
|
||
$f_s_month = $this->dashboard_statistics_model->get_dashboard_table_data('f_s_month', $start, $end);
|
||
$f_s_city = $this->dashboard_statistics_model->get_dashboard_table_data('f_s_city', $start, $end);
|
||
$f_faq_search = $this->dashboard_statistics_model->get_dashboard_table_data('f_faq_search', $start, $end);
|
||
$f_source_mediums = $this->dashboard_statistics_model->get_dashboard_table_data('f_source_mediums', $start, $end);
|
||
|
||
$data = array(
|
||
'yearly_s_evtype' => $f_s_evtype,
|
||
'yearly_s_month' => $f_s_month,
|
||
'yearly_s_city' => $f_s_city,
|
||
'yearly_faq_search' => $f_faq_search,
|
||
'yearly_source_mediums' => $f_source_mediums,
|
||
);
|
||
break;
|
||
|
||
default:
|
||
$data = array();
|
||
// code...
|
||
break;
|
||
}
|
||
|
||
return $data;
|
||
}
|
||
|
||
/**
|
||
* Main no show excel data values
|
||
*
|
||
* @param array $options
|
||
* @return void
|
||
* access private member
|
||
*/
|
||
private function reportData($start = "", $end = "")
|
||
{
|
||
/*FILTERED DATA*/
|
||
/*
|
||
* first sheet data
|
||
*/
|
||
$start_date_string = date('d/m/Y', strtotime($start));
|
||
$end_date_string = date('d/m/Y', strtotime($end));
|
||
$datasets = $this->getExportData(0, $start, $end);
|
||
$active_sheet = $this->spreadsheet->setActiveSheetIndex(0);
|
||
$date_indexes = array();
|
||
|
||
// set interval
|
||
$active_sheet->mergeCells('A1:V1')
|
||
->setCellValue('A1', "Du {$start_date_string} au {$end_date_string}");
|
||
|
||
// start from fourth row in first sheet
|
||
$previous_date = '';
|
||
foreach ($datasets as $data_category => $category_value) {
|
||
$cell_row_index = 5;
|
||
$row_passed = 0;
|
||
switch ($data_category) {
|
||
case 'visitors_graph_data':
|
||
$user_cell = array(
|
||
'Nouveaux'=> 'B',
|
||
'Connus'=> 'C',
|
||
'Connectés' =>'D'
|
||
);
|
||
// echo var_dump($category_value[0]['vis_rdate']);
|
||
if ($category_value) {
|
||
foreach ($category_value as $index => $value) {
|
||
$date_indexes[$value['vis_rdate']] = $cell_row_index;
|
||
// array_push($unique_dates['A'.$cell_row_index], $value['vis_rdate']);
|
||
$active_sheet->setCellValue('A'.($cell_row_index), $value['vis_rdate']);
|
||
$active_sheet->setCellValue($user_cell[$value['user_type']].($cell_row_index), $value['vis_rdata']);
|
||
// $previous_date = $value['vis_rdate'];
|
||
|
||
$row_passed++;
|
||
if ($row_passed == 3) {
|
||
$cell_row_index++;
|
||
$row_passed = 0;
|
||
}
|
||
// if ($value['vis_rdate'] == $previous_date) {
|
||
// } else {
|
||
// $active_sheet->setCellValue('A'.($cell_row_index), $value['vis_rdate']);
|
||
// $active_sheet->setCellValue($user_cell[$value['vis_rdate']].($cell_row_index), $value['vis_rdate']);
|
||
// }
|
||
}
|
||
}
|
||
break;
|
||
case 'sessions_graph_data':
|
||
$user_cell = array(
|
||
'Nouveaux'=> 'E',
|
||
'Connus'=> 'F',
|
||
'Connectés' =>'G'
|
||
);
|
||
// echo var_dump($category_value[0]['sess_rdate']);
|
||
if ($category_value) {
|
||
foreach ($category_value as $index => $value) {
|
||
// array_push($unique_dates['A'.$cell_row_index], $value['sess_rdate']);
|
||
$active_sheet->setCellValue('A'.($cell_row_index), $value['sess_rdate']);
|
||
$active_sheet->setCellValue($user_cell[$value['user_type']].($cell_row_index), $value['sess_rdata']);
|
||
// $previous_date = $value['sess_rdate'];
|
||
|
||
$row_passed++;
|
||
if ($row_passed == 3) {
|
||
$cell_row_index++;
|
||
$row_passed = 0;
|
||
}
|
||
// if ($value['vis_rdate'] == $previous_date) {
|
||
// } else {
|
||
// $active_sheet->setCellValue('A'.($cell_row_index), $value['vis_rdate']);
|
||
// $active_sheet->setCellValue($user_cell[$value['vis_rdate']].($cell_row_index), $value['vis_rdate']);
|
||
// }
|
||
}
|
||
}
|
||
break;
|
||
case 'session_duration_graph_data':
|
||
$user_cell = array(
|
||
'Nouveaux'=> 'H',
|
||
'Connus'=> 'I',
|
||
'Connectés' =>'J'
|
||
);
|
||
// echo var_dump($category_value[0]['sess_rdate']);
|
||
if ($category_value) {
|
||
foreach ($category_value as $index => $value) {
|
||
// array_push($unique_dates['A'.$cell_row_index], $value['sess_rdate']);
|
||
$active_sheet->setCellValue('A'.($cell_row_index), $value['sess_rdate']);
|
||
$active_sheet->setCellValue($user_cell[$value['user_type']].($cell_row_index), $value['sess_rdata']);
|
||
// $previous_date = $value['sess_rdate'];
|
||
|
||
$row_passed++;
|
||
if ($row_passed == 3) {
|
||
$cell_row_index++;
|
||
$row_passed = 0;
|
||
}
|
||
// if ($value['vis_rdate'] == $previous_date) {
|
||
// } else {
|
||
// $active_sheet->setCellValue('A'.($cell_row_index), $value['vis_rdate']);
|
||
// $active_sheet->setCellValue($user_cell[$value['vis_rdate']].($cell_row_index), $value['vis_rdate']);
|
||
// }
|
||
}
|
||
}
|
||
break;
|
||
case 'pageviews':
|
||
$user_cell = array(
|
||
'Nouveaux'=> 'K',
|
||
'Connus'=> 'L',
|
||
'Connectés' =>'M'
|
||
);
|
||
// echo var_dump($category_value[0]['pv_rdate']);
|
||
if ($category_value) {
|
||
foreach ($category_value as $index => $value) {
|
||
// array_push($unique_dates['A'.$cell_row_index], $value['pv_rdate']);
|
||
$active_sheet->setCellValue('A'.($cell_row_index), $value['pv_rdate']);
|
||
$active_sheet->setCellValue($user_cell[$value['user_type']].($cell_row_index), $value['pv_rdata']);
|
||
// $previous_date = $value['sess_rdate'];
|
||
|
||
$row_passed++;
|
||
if ($row_passed == 3) {
|
||
$cell_row_index++;
|
||
$row_passed = 0;
|
||
}
|
||
// if ($value['vis_rdate'] == $previous_date) {
|
||
// } else {
|
||
// $active_sheet->setCellValue('A'.($cell_row_index), $value['vis_rdate']);
|
||
// $active_sheet->setCellValue($user_cell[$value['vis_rdate']].($cell_row_index), $value['vis_rdate']);
|
||
// }
|
||
}
|
||
}
|
||
break;
|
||
case 'unique_pageviews':
|
||
$user_cell = array(
|
||
'Nouveaux'=> 'N',
|
||
'Connus'=> 'O',
|
||
'Connectés' =>'P'
|
||
);
|
||
// echo var_dump($category_value[0]['pv_rdate']);
|
||
if ($category_value) {
|
||
foreach ($category_value as $index => $value) {
|
||
// array_push($unique_dates['A'.$cell_row_index], $value['pv_rdate']);
|
||
$active_sheet->setCellValue('A'.($cell_row_index), $value['pv_rdate']);
|
||
$active_sheet->setCellValue($user_cell[$value['user_type']].($cell_row_index), $value['pv_rdata']);
|
||
// $previous_date = $value['sess_rdate'];
|
||
|
||
$row_passed++;
|
||
if ($row_passed == 3) {
|
||
$cell_row_index++;
|
||
$row_passed = 0;
|
||
}
|
||
// if ($value['vis_rdate'] == $previous_date) {
|
||
// } else {
|
||
// $active_sheet->setCellValue('A'.($cell_row_index), $value['vis_rdate']);
|
||
// $active_sheet->setCellValue($user_cell[$value['vis_rdate']].($cell_row_index), $value['vis_rdate']);
|
||
// }
|
||
}
|
||
}
|
||
break;
|
||
case 'event_views':
|
||
$user_cell = array(
|
||
'Nouveaux'=> 'Q',
|
||
'Connus'=> 'R',
|
||
'Connectés' =>'S'
|
||
);
|
||
// echo var_dump($category_value[0]['ev_rdate']);
|
||
if ($category_value) {
|
||
foreach ($category_value as $index => $value) {
|
||
// array_push($unique_dates['A'.$cell_row_index], $value['ev_rdate']);
|
||
// $active_sheet->setCellValue('A'.($date_indexes[$value['ev_rdate']]), $value['ev_rdate']);
|
||
$active_sheet->setCellValue($user_cell[$value['user_type']].($date_indexes[$value['ev_rdate']]), $value['ev_rdata']);
|
||
// $previous_date = $value['sess_rdate'];
|
||
|
||
$row_passed++;
|
||
if ($row_passed == 3) {
|
||
$cell_row_index++;
|
||
$row_passed = 0;
|
||
}
|
||
// if ($value['vis_rdate'] == $previous_date) {
|
||
// } else {
|
||
// $active_sheet->setCellValue('A'.($cell_row_index), $value['vis_rdate']);
|
||
// $active_sheet->setCellValue($user_cell[$value['vis_rdate']].($cell_row_index), $value['vis_rdate']);
|
||
// }
|
||
}
|
||
}
|
||
break;
|
||
case 'bounceRate':
|
||
$user_cell = array(
|
||
'Nouveaux'=> 'T',
|
||
'Connus'=> 'U',
|
||
'Connectés' =>'V'
|
||
);
|
||
// echo var_dump($category_value[0]['bcr_rdate']);
|
||
if ($category_value) {
|
||
foreach ($category_value as $index => $value) {
|
||
// array_push($unique_dates['A'.$cell_row_index], $value['bcr_rdate']);
|
||
$active_sheet->setCellValue('A'.($cell_row_index), $value['bcr_rdate']);
|
||
$active_sheet->setCellValue($user_cell[$value['user_type']].($cell_row_index), $value['bcr_rdata']);
|
||
// $previous_date = $value['sess_rdate'];
|
||
|
||
$row_passed++;
|
||
if ($row_passed == 3) {
|
||
$cell_row_index++;
|
||
$row_passed = 0;
|
||
}
|
||
// if ($value['vis_rdate'] == $previous_date) {
|
||
// } else {
|
||
// $active_sheet->setCellValue('A'.($cell_row_index), $value['vis_rdate']);
|
||
// $active_sheet->setCellValue($user_cell[$value['vis_rdate']].($cell_row_index), $value['vis_rdate']);
|
||
// }
|
||
}
|
||
}
|
||
break;
|
||
|
||
default:
|
||
// code...
|
||
break;
|
||
}
|
||
// echo $key;
|
||
// echo var_dump($value);
|
||
}
|
||
|
||
/*
|
||
* second sheet data
|
||
*/
|
||
$datasets = $this->getExportData(4, $start, $end);
|
||
$active_sheet = $this->spreadsheet->setActiveSheetIndex(1);
|
||
$date_indexes = array();
|
||
|
||
// set interval
|
||
$active_sheet->mergeCells('A1:V1')
|
||
->setCellValue('A1', "Du {$start_date_string} au {$end_date_string}");
|
||
|
||
$previous_date = '';
|
||
foreach ($datasets as $data_category => $category_value) {
|
||
$cell_row_index = 4;
|
||
$row_passed = 0;
|
||
switch ($data_category) {
|
||
case 'yearly_browsers':
|
||
// echo var_dump($category_value[0]['vis_rdate']);
|
||
foreach ($category_value as $index => $value) {
|
||
$date_indexes[$value['year']] = $cell_row_index;
|
||
$active_sheet->setCellValue('A'.($cell_row_index), $value['rtype']);
|
||
$active_sheet->setCellValue('B'.($cell_row_index), $value['vis_rdata']);
|
||
// $active_sheet->setCellValue('C'.($cell_row_index), $value['year']);
|
||
$cell_row_index++;
|
||
}
|
||
break;
|
||
case 'yearly_devices':
|
||
// echo var_dump($category_value[0]['vis_rdate']);
|
||
foreach ($category_value as $index => $value) {
|
||
$date_indexes[$value['year']] = $cell_row_index;
|
||
$active_sheet->setCellValue('E'.($cell_row_index), $value['rtype']);
|
||
$active_sheet->setCellValue('F'.($cell_row_index), $value['vis_rdata']);
|
||
// $active_sheet->setCellValue('G'.($cell_row_index), $value['year']);
|
||
$cell_row_index++;
|
||
}
|
||
break;
|
||
case 'yearly_url_clicks':
|
||
// echo var_dump($category_value[0]['vis_rdate']);
|
||
foreach ($category_value as $index => $value) {
|
||
$date_indexes[$value['year']] = $cell_row_index;
|
||
$active_sheet->setCellValue('I'.($cell_row_index), $value['gev_raction']);
|
||
$active_sheet->setCellValue('J'.($cell_row_index), $value['gev_rdata']);
|
||
// $active_sheet->setCellValue('K'.($cell_row_index), $value['year']);
|
||
$cell_row_index++;
|
||
}
|
||
break;
|
||
case 'yearly_contact_cat':
|
||
// echo var_dump($category_value[0]['vis_rdate']);
|
||
foreach ($category_value as $index => $value) {
|
||
$date_indexes[$value['year']] = $cell_row_index;
|
||
$active_sheet->setCellValue('M'.($cell_row_index), $value['c_category']);
|
||
$active_sheet->setCellValue('N'.($cell_row_index), $value['clicks']);
|
||
// $active_sheet->setCellValue('O'.($cell_row_index), $value['year']);
|
||
$cell_row_index++;
|
||
}
|
||
break;
|
||
|
||
default:
|
||
// code...
|
||
break;
|
||
}
|
||
}
|
||
|
||
/*
|
||
* third sheet data
|
||
*/
|
||
$datasets = $this->getExportData(5, $start, $end);
|
||
$active_sheet = $this->spreadsheet->setActiveSheetIndex(2);
|
||
$date_indexes = array();
|
||
|
||
// set interval
|
||
$active_sheet->mergeCells('A1:V1')
|
||
->setCellValue('A1', "Du {$start_date_string} au {$end_date_string}");
|
||
|
||
$previous_date = '';
|
||
if ($category_value) {
|
||
foreach ($datasets as $data_category => $category_value) {
|
||
$cell_row_index = 4;
|
||
$row_passed = 0;
|
||
switch ($data_category) {
|
||
case 'y0':
|
||
// echo var_dump($category_value[0]['vis_rdate']);
|
||
foreach ($category_value as $index => $value) {
|
||
// $active_sheet->setCellValue('A'.($cell_row_index), $value['value']);
|
||
$active_sheet->setCellValue('B3', $value['value']);
|
||
// $active_sheet->setCellValue('B'.($cell_row_index), $value['data_year']);
|
||
$cell_row_index++;
|
||
}
|
||
break;
|
||
case 'yearly_fevents':
|
||
foreach ($category_value as $index => $value) {
|
||
if (!empty($value['bostat'])) {
|
||
$active_sheet->setCellValue('E'.($cell_row_index), $value['bostat']);
|
||
$active_sheet->setCellValue('F'.($cell_row_index), $value['value']);
|
||
// $active_sheet->setCellValue('G'.($cell_row_index), $value['data_year']);
|
||
$cell_row_index++;
|
||
}
|
||
}
|
||
break;
|
||
case 'y2':
|
||
foreach ($category_value as $index => $value) {
|
||
// $active_sheet->setCellValue('I'.($cell_row_index), $value['value']);
|
||
$active_sheet->setCellValue('J3', (empty($value['value'])? 0 : $value['value']));
|
||
// $active_sheet->setCellValue('J'.($cell_row_index), $value['data_year']);
|
||
$cell_row_index++;
|
||
}
|
||
break;
|
||
case 'y3':
|
||
foreach ($category_value as $index => $value) {
|
||
$active_sheet->setCellValue('M3', (empty($value['value'])? 0 : $value['value']));
|
||
// $active_sheet->setCellValue('L'.($cell_row_index), $value['value']);
|
||
// $active_sheet->setCellValue('M'.($cell_row_index), $value['data_year']);
|
||
$cell_row_index++;
|
||
}
|
||
break;
|
||
case 'y4':
|
||
foreach ($category_value as $index => $value) {
|
||
$active_sheet->setCellValue('P3', (empty($value['value'])? 0 : $value['value']));
|
||
// $active_sheet->setCellValue('O'.($cell_row_index), $value['value']);
|
||
// $active_sheet->setCellValue('P'.($cell_row_index), $value['data_year']);
|
||
$cell_row_index++;
|
||
}
|
||
break;
|
||
default:
|
||
// code...
|
||
break;
|
||
}
|
||
}
|
||
}
|
||
/*
|
||
* fourth sheet data
|
||
*/
|
||
$datasets = $this->getExportData(6, $start, $end);
|
||
$active_sheet = $this->spreadsheet->setActiveSheetIndex(3);
|
||
$date_indexes = array();
|
||
|
||
// set interval
|
||
$active_sheet->mergeCells('A1:V1')
|
||
->setCellValue('A1', "Du {$start_date_string} au {$end_date_string}");
|
||
|
||
$previous_date = '';
|
||
|
||
if ($category_value) {
|
||
foreach ($datasets as $data_category => $category_value) {
|
||
$cell_row_index = 5;
|
||
$row_passed = 0;
|
||
switch ($data_category) {
|
||
case 'yearly_s_evtype':
|
||
foreach ($category_value as $index => $value) {
|
||
if (!empty($value['label'])) {
|
||
$active_sheet->setCellValue('A'.($cell_row_index), $value['label']);
|
||
$active_sheet->setCellValue('B'.($cell_row_index), $value['gev_rdata']);
|
||
// $active_sheet->setCellValue('C'.($cell_row_index), $value['year']);
|
||
$cell_row_index++;
|
||
}
|
||
}
|
||
break;
|
||
case 'yearly_s_month':
|
||
$months = array('Tous les mois', 'Janvier', 'Février', 'Mars', 'Avril', 'Mai',
|
||
'Juin', 'Juillet', 'Août', 'Septembre', 'Octobre', 'Novembre', 'Décembre');
|
||
foreach ($category_value as $index => $value) {
|
||
if (!empty($value['gev_raction']) && $value['gev_raction'] <= 12) {
|
||
$active_sheet->setCellValue('E'.($cell_row_index), $months[$value['gev_raction']]);
|
||
$active_sheet->setCellValue('F'.($cell_row_index), $value['gev_rdata']);
|
||
// $active_sheet->setCellValue('G'.($cell_row_index), $value['year']);
|
||
$cell_row_index++;
|
||
}
|
||
}
|
||
break;
|
||
case 'yearly_s_city':
|
||
foreach ($category_value as $index => $value) {
|
||
if (!empty($value['label'])) {
|
||
$active_sheet->setCellValue('I'.($cell_row_index), $value['label']);
|
||
$active_sheet->setCellValue('J'.($cell_row_index), $value['gev_rdata']);
|
||
// $active_sheet->setCellValue('K'.($cell_row_index), $value['year']);
|
||
$cell_row_index++;
|
||
}
|
||
}
|
||
break;
|
||
case 'yearly_faq_search':
|
||
foreach ($category_value as $index => $value) {
|
||
$active_sheet->setCellValue('M'.($cell_row_index), $value['gev_raction']);
|
||
$active_sheet->setCellValue('N'.($cell_row_index), $value['gev_rdata']);
|
||
// $active_sheet->setCellValue('O'.($cell_row_index), $value['year']);
|
||
$cell_row_index++;
|
||
}
|
||
break;
|
||
|
||
case 'yearly_source_mediums':
|
||
foreach ($category_value as $index => $value) {
|
||
$active_sheet->setCellValue('Q'.($cell_row_index), $value['gsm_rsource']);
|
||
// $active_sheet->setCellValue('R'.($cell_row_index), $value['gsm_rsourcem']);
|
||
$active_sheet->setCellValue('R'.($cell_row_index), $value['gsm_rchannelgroup']);
|
||
$active_sheet->setCellValue('S'.($cell_row_index), $value['gsm_rdata']);
|
||
// $active_sheet->setCellValue('U'.($cell_row_index), $value['year']);
|
||
$cell_row_index++;
|
||
}
|
||
break;
|
||
default:
|
||
// code...
|
||
break;
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
/*ALL REPORT DATA*/
|
||
// private function reportData($start = "", $end = "")
|
||
// {
|
||
// /*
|
||
// * first sheet data
|
||
// */
|
||
// $datasets = $this->getExportData(0);
|
||
// $this->spreadsheet->setActiveSheetIndex(0);
|
||
// $date_indexes = array();
|
||
//
|
||
// // start from fourth row in first sheet
|
||
// $previous_date = '';
|
||
// foreach ($datasets as $data_category => $category_value) {
|
||
// $cell_row_index = 4;
|
||
// $row_passed = 0;
|
||
// switch ($data_category) {
|
||
// case 'visitors_graph_data':
|
||
// $user_cell = array(
|
||
// 'Nouveaux'=> 'B',
|
||
// 'Connus'=> 'C',
|
||
// 'Connectés' =>'D'
|
||
// );
|
||
// // echo var_dump($category_value[0]['vis_rdate']);
|
||
// foreach ($category_value as $index => $value) {
|
||
// $date_indexes[$value['vis_rdate']] = $cell_row_index;
|
||
// // array_push($unique_dates['A'.$cell_row_index], $value['vis_rdate']);
|
||
// $this->sheets->setCellValue('A'.($cell_row_index), $value['vis_rdate']);
|
||
// $this->sheets->setCellValue($user_cell[$value['user_type']].($cell_row_index), $value['vis_rdata']);
|
||
// // $previous_date = $value['vis_rdate'];
|
||
//
|
||
// $row_passed++;
|
||
// if ($row_passed == 3) {
|
||
// $cell_row_index++;
|
||
// $row_passed = 0;
|
||
// }
|
||
// // if ($value['vis_rdate'] == $previous_date) {
|
||
// // } else {
|
||
// // $this->sheets->setCellValue('A'.($cell_row_index), $value['vis_rdate']);
|
||
// // $this->sheets->setCellValue($user_cell[$value['vis_rdate']].($cell_row_index), $value['vis_rdate']);
|
||
// // }
|
||
// }
|
||
// break;
|
||
// case 'sessions_graph_data':
|
||
// $user_cell = array(
|
||
// 'Nouveaux'=> 'E',
|
||
// 'Connus'=> 'F',
|
||
// 'Connectés' =>'G'
|
||
// );
|
||
// // echo var_dump($category_value[0]['sess_rdate']);
|
||
// foreach ($category_value as $index => $value) {
|
||
// // array_push($unique_dates['A'.$cell_row_index], $value['sess_rdate']);
|
||
// $this->sheets->setCellValue('A'.($cell_row_index), $value['sess_rdate']);
|
||
// $this->sheets->setCellValue($user_cell[$value['user_type']].($cell_row_index), $value['sess_rdata']);
|
||
// // $previous_date = $value['sess_rdate'];
|
||
//
|
||
// $row_passed++;
|
||
// if ($row_passed == 3) {
|
||
// $cell_row_index++;
|
||
// $row_passed = 0;
|
||
// }
|
||
// // if ($value['vis_rdate'] == $previous_date) {
|
||
// // } else {
|
||
// // $this->sheets->setCellValue('A'.($cell_row_index), $value['vis_rdate']);
|
||
// // $this->sheets->setCellValue($user_cell[$value['vis_rdate']].($cell_row_index), $value['vis_rdate']);
|
||
// // }
|
||
// }
|
||
// break;
|
||
// case 'session_duration_graph_data':
|
||
// $user_cell = array(
|
||
// 'Nouveaux'=> 'H',
|
||
// 'Connus'=> 'I',
|
||
// 'Connectés' =>'J'
|
||
// );
|
||
// // echo var_dump($category_value[0]['sess_rdate']);
|
||
// foreach ($category_value as $index => $value) {
|
||
// // array_push($unique_dates['A'.$cell_row_index], $value['sess_rdate']);
|
||
// $this->sheets->setCellValue('A'.($cell_row_index), $value['sess_rdate']);
|
||
// $this->sheets->setCellValue($user_cell[$value['user_type']].($cell_row_index), $value['sess_rdata']);
|
||
// // $previous_date = $value['sess_rdate'];
|
||
//
|
||
// $row_passed++;
|
||
// if ($row_passed == 3) {
|
||
// $cell_row_index++;
|
||
// $row_passed = 0;
|
||
// }
|
||
// // if ($value['vis_rdate'] == $previous_date) {
|
||
// // } else {
|
||
// // $this->sheets->setCellValue('A'.($cell_row_index), $value['vis_rdate']);
|
||
// // $this->sheets->setCellValue($user_cell[$value['vis_rdate']].($cell_row_index), $value['vis_rdate']);
|
||
// // }
|
||
// }
|
||
// break;
|
||
// case 'pageviews':
|
||
// $user_cell = array(
|
||
// 'Nouveaux'=> 'K',
|
||
// 'Connus'=> 'L',
|
||
// 'Connectés' =>'M'
|
||
// );
|
||
// // echo var_dump($category_value[0]['pv_rdate']);
|
||
// foreach ($category_value as $index => $value) {
|
||
// // array_push($unique_dates['A'.$cell_row_index], $value['pv_rdate']);
|
||
// $this->sheets->setCellValue('A'.($cell_row_index), $value['pv_rdate']);
|
||
// $this->sheets->setCellValue($user_cell[$value['user_type']].($cell_row_index), $value['pv_rdata']);
|
||
// // $previous_date = $value['sess_rdate'];
|
||
//
|
||
// $row_passed++;
|
||
// if ($row_passed == 3) {
|
||
// $cell_row_index++;
|
||
// $row_passed = 0;
|
||
// }
|
||
// // if ($value['vis_rdate'] == $previous_date) {
|
||
// // } else {
|
||
// // $this->sheets->setCellValue('A'.($cell_row_index), $value['vis_rdate']);
|
||
// // $this->sheets->setCellValue($user_cell[$value['vis_rdate']].($cell_row_index), $value['vis_rdate']);
|
||
// // }
|
||
// }
|
||
// break;
|
||
// case 'unique_pageviews':
|
||
// $user_cell = array(
|
||
// 'Nouveaux'=> 'N',
|
||
// 'Connus'=> 'O',
|
||
// 'Connectés' =>'P'
|
||
// );
|
||
// // echo var_dump($category_value[0]['pv_rdate']);
|
||
// foreach ($category_value as $index => $value) {
|
||
// // array_push($unique_dates['A'.$cell_row_index], $value['pv_rdate']);
|
||
// $this->sheets->setCellValue('A'.($cell_row_index), $value['pv_rdate']);
|
||
// $this->sheets->setCellValue($user_cell[$value['user_type']].($cell_row_index), $value['pv_rdata']);
|
||
// // $previous_date = $value['sess_rdate'];
|
||
//
|
||
// $row_passed++;
|
||
// if ($row_passed == 3) {
|
||
// $cell_row_index++;
|
||
// $row_passed = 0;
|
||
// }
|
||
// // if ($value['vis_rdate'] == $previous_date) {
|
||
// // } else {
|
||
// // $this->sheets->setCellValue('A'.($cell_row_index), $value['vis_rdate']);
|
||
// // $this->sheets->setCellValue($user_cell[$value['vis_rdate']].($cell_row_index), $value['vis_rdate']);
|
||
// // }
|
||
// }
|
||
// break;
|
||
// case 'event_views':
|
||
// $user_cell = array(
|
||
// 'Nouveaux'=> 'Q',
|
||
// 'Connus'=> 'R',
|
||
// 'Connectés' =>'S'
|
||
// );
|
||
// // echo var_dump($category_value[0]['ev_rdate']);
|
||
// foreach ($category_value as $index => $value) {
|
||
// // array_push($unique_dates['A'.$cell_row_index], $value['ev_rdate']);
|
||
// // $this->sheets->setCellValue('A'.($date_indexes[$value['ev_rdate']]), $value['ev_rdate']);
|
||
// $this->sheets->setCellValue($user_cell[$value['user_type']].($date_indexes[$value['ev_rdate']]), $value['ev_rdata']);
|
||
// // $previous_date = $value['sess_rdate'];
|
||
//
|
||
// $row_passed++;
|
||
// if ($row_passed == 3) {
|
||
// $cell_row_index++;
|
||
// $row_passed = 0;
|
||
// }
|
||
// // if ($value['vis_rdate'] == $previous_date) {
|
||
// // } else {
|
||
// // $this->sheets->setCellValue('A'.($cell_row_index), $value['vis_rdate']);
|
||
// // $this->sheets->setCellValue($user_cell[$value['vis_rdate']].($cell_row_index), $value['vis_rdate']);
|
||
// // }
|
||
// }
|
||
// break;
|
||
// case 'bounceRate':
|
||
// $user_cell = array(
|
||
// 'Nouveaux'=> 'T',
|
||
// 'Connus'=> 'U',
|
||
// 'Connectés' =>'V'
|
||
// );
|
||
// // echo var_dump($category_value[0]['bcr_rdate']);
|
||
// foreach ($category_value as $index => $value) {
|
||
// // array_push($unique_dates['A'.$cell_row_index], $value['bcr_rdate']);
|
||
// $this->sheets->setCellValue('A'.($cell_row_index), $value['bcr_rdate']);
|
||
// $this->sheets->setCellValue($user_cell[$value['user_type']].($cell_row_index), $value['bcr_rdata']);
|
||
// // $previous_date = $value['sess_rdate'];
|
||
//
|
||
// $row_passed++;
|
||
// if ($row_passed == 3) {
|
||
// $cell_row_index++;
|
||
// $row_passed = 0;
|
||
// }
|
||
// // if ($value['vis_rdate'] == $previous_date) {
|
||
// // } else {
|
||
// // $this->sheets->setCellValue('A'.($cell_row_index), $value['vis_rdate']);
|
||
// // $this->sheets->setCellValue($user_cell[$value['vis_rdate']].($cell_row_index), $value['vis_rdate']);
|
||
// // }
|
||
// }
|
||
// break;
|
||
//
|
||
// default:
|
||
// // code...
|
||
// break;
|
||
// }
|
||
// // echo $key;
|
||
// // echo var_dump($value);
|
||
// }
|
||
//
|
||
// /*
|
||
// * second sheet data
|
||
// */
|
||
// $datasets = $this->getExportData(1);
|
||
// $active_sheet = $this->spreadsheet->setActiveSheetIndex(1);
|
||
// $date_indexes = array();
|
||
//
|
||
// $previous_date = '';
|
||
// foreach ($datasets as $data_category => $category_value) {
|
||
// $cell_row_index = 3;
|
||
// $row_passed = 0;
|
||
// switch ($data_category) {
|
||
// case 'yearly_browsers':
|
||
// // echo var_dump($category_value[0]['vis_rdate']);
|
||
// foreach ($category_value as $index => $value) {
|
||
// $date_indexes[$value['year']] = $cell_row_index;
|
||
// $active_sheet->setCellValue('A'.($cell_row_index), $value['rtype']);
|
||
// $active_sheet->setCellValue('B'.($cell_row_index), $value['vis_rdata']);
|
||
// $active_sheet->setCellValue('C'.($cell_row_index), $value['year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// break;
|
||
// case 'yearly_devices':
|
||
// // echo var_dump($category_value[0]['vis_rdate']);
|
||
// foreach ($category_value as $index => $value) {
|
||
// $date_indexes[$value['year']] = $cell_row_index;
|
||
// $active_sheet->setCellValue('E'.($cell_row_index), $value['rtype']);
|
||
// $active_sheet->setCellValue('F'.($cell_row_index), $value['vis_rdata']);
|
||
// $active_sheet->setCellValue('G'.($cell_row_index), $value['year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// break;
|
||
// case 'yearly_url_clicks':
|
||
// // echo var_dump($category_value[0]['vis_rdate']);
|
||
// foreach ($category_value as $index => $value) {
|
||
// $date_indexes[$value['year']] = $cell_row_index;
|
||
// $active_sheet->setCellValue('I'.($cell_row_index), $value['gev_raction']);
|
||
// $active_sheet->setCellValue('J'.($cell_row_index), $value['gev_rdata']);
|
||
// $active_sheet->setCellValue('K'.($cell_row_index), $value['year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// break;
|
||
// case 'yearly_contact_cat':
|
||
// // echo var_dump($category_value[0]['vis_rdate']);
|
||
// foreach ($category_value as $index => $value) {
|
||
// $date_indexes[$value['year']] = $cell_row_index;
|
||
// $active_sheet->setCellValue('M'.($cell_row_index), $value['c_category']);
|
||
// $active_sheet->setCellValue('N'.($cell_row_index), $value['clicks']);
|
||
// $active_sheet->setCellValue('O'.($cell_row_index), $value['year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// break;
|
||
//
|
||
// default:
|
||
// // code...
|
||
// break;
|
||
// }
|
||
// }
|
||
//
|
||
// /*
|
||
// * third sheet data
|
||
// */
|
||
// $datasets = $this->getExportData(2);
|
||
// $active_sheet = $this->spreadsheet->setActiveSheetIndex(2);
|
||
// $date_indexes = array();
|
||
//
|
||
// $previous_date = '';
|
||
// foreach ($datasets as $data_category => $category_value) {
|
||
// $cell_row_index = 3;
|
||
// $row_passed = 0;
|
||
// switch ($data_category) {
|
||
// case 'y0':
|
||
// // echo var_dump($category_value[0]['vis_rdate']);
|
||
// foreach ($category_value as $index => $value) {
|
||
// $active_sheet->setCellValue('A'.($cell_row_index), $value['value']);
|
||
// $active_sheet->setCellValue('B'.($cell_row_index), $value['data_year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// break;
|
||
// case 'yearly_fevents':
|
||
// foreach ($category_value as $index => $value) {
|
||
// if (!empty($value['bostat'])) {
|
||
// $active_sheet->setCellValue('E'.($cell_row_index), $value['bostat']);
|
||
// $active_sheet->setCellValue('F'.($cell_row_index), $value['value']);
|
||
// $active_sheet->setCellValue('G'.($cell_row_index), $value['data_year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// }
|
||
// break;
|
||
// case 'y2':
|
||
// foreach ($category_value as $index => $value) {
|
||
// $active_sheet->setCellValue('I'.($cell_row_index), $value['value']);
|
||
// $active_sheet->setCellValue('J'.($cell_row_index), $value['data_year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// break;
|
||
// case 'y3':
|
||
// foreach ($category_value as $index => $value) {
|
||
// $active_sheet->setCellValue('L'.($cell_row_index), $value['value']);
|
||
// $active_sheet->setCellValue('M'.($cell_row_index), $value['data_year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// break;
|
||
// case 'y4':
|
||
// foreach ($category_value as $index => $value) {
|
||
// $active_sheet->setCellValue('O'.($cell_row_index), $value['value']);
|
||
// $active_sheet->setCellValue('P'.($cell_row_index), $value['data_year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// break;
|
||
// default:
|
||
// // code...
|
||
// break;
|
||
// }
|
||
// }
|
||
//
|
||
// /*
|
||
// * fourth sheet data
|
||
// */
|
||
// $datasets = $this->getExportData(3);
|
||
// $active_sheet = $this->spreadsheet->setActiveSheetIndex(3);
|
||
// $date_indexes = array();
|
||
//
|
||
// $previous_date = '';
|
||
// foreach ($datasets as $data_category => $category_value) {
|
||
// $cell_row_index = 4;
|
||
// $row_passed = 0;
|
||
// switch ($data_category) {
|
||
// case 'yearly_s_evtype':
|
||
// foreach ($category_value as $index => $value) {
|
||
// if (!empty($value['label'])) {
|
||
// $active_sheet->setCellValue('A'.($cell_row_index), $value['label']);
|
||
// $active_sheet->setCellValue('B'.($cell_row_index), $value['gev_rdata']);
|
||
// $active_sheet->setCellValue('C'.($cell_row_index), $value['year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// }
|
||
// break;
|
||
// case 'yearly_s_month':
|
||
// $months = array('Tous les mois', 'Janvier', 'Février', 'Mars', 'Avril', 'Mai',
|
||
// 'Juin', 'Juillet', 'Août', 'Septembre', 'Octobre', 'Novembre', 'Décembre');
|
||
// foreach ($category_value as $index => $value) {
|
||
// if (!empty($value['gev_raction'])) {
|
||
// $active_sheet->setCellValue('E'.($cell_row_index), $months[$value['gev_raction']]);
|
||
// $active_sheet->setCellValue('F'.($cell_row_index), $value['gev_rdata']);
|
||
// $active_sheet->setCellValue('G'.($cell_row_index), $value['year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// }
|
||
// break;
|
||
// case 'yearly_s_city':
|
||
// foreach ($category_value as $index => $value) {
|
||
// if (!empty($value['label'])) {
|
||
// $active_sheet->setCellValue('I'.($cell_row_index), $value['label']);
|
||
// $active_sheet->setCellValue('J'.($cell_row_index), $value['gev_rdata']);
|
||
// $active_sheet->setCellValue('K'.($cell_row_index), $value['year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// }
|
||
// break;
|
||
// case 'yearly_faq_search':
|
||
// foreach ($category_value as $index => $value) {
|
||
// $active_sheet->setCellValue('M'.($cell_row_index), $value['gev_raction']);
|
||
// $active_sheet->setCellValue('N'.($cell_row_index), $value['gev_rdata']);
|
||
// $active_sheet->setCellValue('O'.($cell_row_index), $value['year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// break;
|
||
//
|
||
// case 'yearly_source_mediums':
|
||
// foreach ($category_value as $index => $value) {
|
||
// $active_sheet->setCellValue('Q'.($cell_row_index), $value['gsm_rsource']);
|
||
// $active_sheet->setCellValue('R'.($cell_row_index), $value['gsm_rsourcem']);
|
||
// $active_sheet->setCellValue('S'.($cell_row_index), $value['gsm_rchannelgroup']);
|
||
// $active_sheet->setCellValue('T'.($cell_row_index), $value['gsm_rdata']);
|
||
// $active_sheet->setCellValue('U'.($cell_row_index), $value['year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// break;
|
||
// default:
|
||
// // code...
|
||
// break;
|
||
// }
|
||
// }
|
||
//
|
||
// /*FILTERED DATA*/
|
||
// /*
|
||
// * first sheet data
|
||
// */
|
||
// $datasets = $this->getExportData(0, $start, $end);
|
||
// $active_sheet = $this->spreadsheet->setActiveSheetIndex(4);
|
||
// $date_indexes = array();
|
||
//
|
||
// // set interval
|
||
// $active_sheet->mergeCells('A1:V1')
|
||
// ->setCellValue('A1', "Du {$start_date_string} au {$end_date_string}");
|
||
//
|
||
// // start from fourth row in first sheet
|
||
// $previous_date = '';
|
||
// foreach ($datasets as $data_category => $category_value) {
|
||
// $cell_row_index = 5;
|
||
// $row_passed = 0;
|
||
// switch ($data_category) {
|
||
// case 'visitors_graph_data':
|
||
// $user_cell = array(
|
||
// 'Nouveaux'=> 'B',
|
||
// 'Connus'=> 'C',
|
||
// 'Connectés' =>'D'
|
||
// );
|
||
// // echo var_dump($category_value[0]['vis_rdate']);
|
||
// foreach ($category_value as $index => $value) {
|
||
// $date_indexes[$value['vis_rdate']] = $cell_row_index;
|
||
// // array_push($unique_dates['A'.$cell_row_index], $value['vis_rdate']);
|
||
// $active_sheet->setCellValue('A'.($cell_row_index), $value['vis_rdate']);
|
||
// $active_sheet->setCellValue($user_cell[$value['user_type']].($cell_row_index), $value['vis_rdata']);
|
||
// // $previous_date = $value['vis_rdate'];
|
||
//
|
||
// $row_passed++;
|
||
// if ($row_passed == 3) {
|
||
// $cell_row_index++;
|
||
// $row_passed = 0;
|
||
// }
|
||
// // if ($value['vis_rdate'] == $previous_date) {
|
||
// // } else {
|
||
// // $active_sheet->setCellValue('A'.($cell_row_index), $value['vis_rdate']);
|
||
// // $active_sheet->setCellValue($user_cell[$value['vis_rdate']].($cell_row_index), $value['vis_rdate']);
|
||
// // }
|
||
// }
|
||
// break;
|
||
// case 'sessions_graph_data':
|
||
// $user_cell = array(
|
||
// 'Nouveaux'=> 'E',
|
||
// 'Connus'=> 'F',
|
||
// 'Connectés' =>'G'
|
||
// );
|
||
// // echo var_dump($category_value[0]['sess_rdate']);
|
||
// foreach ($category_value as $index => $value) {
|
||
// // array_push($unique_dates['A'.$cell_row_index], $value['sess_rdate']);
|
||
// $active_sheet->setCellValue('A'.($cell_row_index), $value['sess_rdate']);
|
||
// $active_sheet->setCellValue($user_cell[$value['user_type']].($cell_row_index), $value['sess_rdata']);
|
||
// // $previous_date = $value['sess_rdate'];
|
||
//
|
||
// $row_passed++;
|
||
// if ($row_passed == 3) {
|
||
// $cell_row_index++;
|
||
// $row_passed = 0;
|
||
// }
|
||
// // if ($value['vis_rdate'] == $previous_date) {
|
||
// // } else {
|
||
// // $active_sheet->setCellValue('A'.($cell_row_index), $value['vis_rdate']);
|
||
// // $active_sheet->setCellValue($user_cell[$value['vis_rdate']].($cell_row_index), $value['vis_rdate']);
|
||
// // }
|
||
// }
|
||
// break;
|
||
// case 'session_duration_graph_data':
|
||
// $user_cell = array(
|
||
// 'Nouveaux'=> 'H',
|
||
// 'Connus'=> 'I',
|
||
// 'Connectés' =>'J'
|
||
// );
|
||
// // echo var_dump($category_value[0]['sess_rdate']);
|
||
// foreach ($category_value as $index => $value) {
|
||
// // array_push($unique_dates['A'.$cell_row_index], $value['sess_rdate']);
|
||
// $active_sheet->setCellValue('A'.($cell_row_index), $value['sess_rdate']);
|
||
// $active_sheet->setCellValue($user_cell[$value['user_type']].($cell_row_index), $value['sess_rdata']);
|
||
// // $previous_date = $value['sess_rdate'];
|
||
//
|
||
// $row_passed++;
|
||
// if ($row_passed == 3) {
|
||
// $cell_row_index++;
|
||
// $row_passed = 0;
|
||
// }
|
||
// // if ($value['vis_rdate'] == $previous_date) {
|
||
// // } else {
|
||
// // $active_sheet->setCellValue('A'.($cell_row_index), $value['vis_rdate']);
|
||
// // $active_sheet->setCellValue($user_cell[$value['vis_rdate']].($cell_row_index), $value['vis_rdate']);
|
||
// // }
|
||
// }
|
||
// break;
|
||
// case 'pageviews':
|
||
// $user_cell = array(
|
||
// 'Nouveaux'=> 'K',
|
||
// 'Connus'=> 'L',
|
||
// 'Connectés' =>'M'
|
||
// );
|
||
// // echo var_dump($category_value[0]['pv_rdate']);
|
||
// foreach ($category_value as $index => $value) {
|
||
// // array_push($unique_dates['A'.$cell_row_index], $value['pv_rdate']);
|
||
// $active_sheet->setCellValue('A'.($cell_row_index), $value['pv_rdate']);
|
||
// $active_sheet->setCellValue($user_cell[$value['user_type']].($cell_row_index), $value['pv_rdata']);
|
||
// // $previous_date = $value['sess_rdate'];
|
||
//
|
||
// $row_passed++;
|
||
// if ($row_passed == 3) {
|
||
// $cell_row_index++;
|
||
// $row_passed = 0;
|
||
// }
|
||
// // if ($value['vis_rdate'] == $previous_date) {
|
||
// // } else {
|
||
// // $active_sheet->setCellValue('A'.($cell_row_index), $value['vis_rdate']);
|
||
// // $active_sheet->setCellValue($user_cell[$value['vis_rdate']].($cell_row_index), $value['vis_rdate']);
|
||
// // }
|
||
// }
|
||
// break;
|
||
// case 'unique_pageviews':
|
||
// $user_cell = array(
|
||
// 'Nouveaux'=> 'N',
|
||
// 'Connus'=> 'O',
|
||
// 'Connectés' =>'P'
|
||
// );
|
||
// // echo var_dump($category_value[0]['pv_rdate']);
|
||
// foreach ($category_value as $index => $value) {
|
||
// // array_push($unique_dates['A'.$cell_row_index], $value['pv_rdate']);
|
||
// $active_sheet->setCellValue('A'.($cell_row_index), $value['pv_rdate']);
|
||
// $active_sheet->setCellValue($user_cell[$value['user_type']].($cell_row_index), $value['pv_rdata']);
|
||
// // $previous_date = $value['sess_rdate'];
|
||
//
|
||
// $row_passed++;
|
||
// if ($row_passed == 3) {
|
||
// $cell_row_index++;
|
||
// $row_passed = 0;
|
||
// }
|
||
// // if ($value['vis_rdate'] == $previous_date) {
|
||
// // } else {
|
||
// // $active_sheet->setCellValue('A'.($cell_row_index), $value['vis_rdate']);
|
||
// // $active_sheet->setCellValue($user_cell[$value['vis_rdate']].($cell_row_index), $value['vis_rdate']);
|
||
// // }
|
||
// }
|
||
// break;
|
||
// case 'event_views':
|
||
// $user_cell = array(
|
||
// 'Nouveaux'=> 'Q',
|
||
// 'Connus'=> 'R',
|
||
// 'Connectés' =>'S'
|
||
// );
|
||
// // echo var_dump($category_value[0]['ev_rdate']);
|
||
// foreach ($category_value as $index => $value) {
|
||
// // array_push($unique_dates['A'.$cell_row_index], $value['ev_rdate']);
|
||
// // $active_sheet->setCellValue('A'.($date_indexes[$value['ev_rdate']]), $value['ev_rdate']);
|
||
// $active_sheet->setCellValue($user_cell[$value['user_type']].($date_indexes[$value['ev_rdate']]), $value['ev_rdata']);
|
||
// // $previous_date = $value['sess_rdate'];
|
||
//
|
||
// $row_passed++;
|
||
// if ($row_passed == 3) {
|
||
// $cell_row_index++;
|
||
// $row_passed = 0;
|
||
// }
|
||
// // if ($value['vis_rdate'] == $previous_date) {
|
||
// // } else {
|
||
// // $active_sheet->setCellValue('A'.($cell_row_index), $value['vis_rdate']);
|
||
// // $active_sheet->setCellValue($user_cell[$value['vis_rdate']].($cell_row_index), $value['vis_rdate']);
|
||
// // }
|
||
// }
|
||
// break;
|
||
// case 'bounceRate':
|
||
// $user_cell = array(
|
||
// 'Nouveaux'=> 'T',
|
||
// 'Connus'=> 'U',
|
||
// 'Connectés' =>'V'
|
||
// );
|
||
// // echo var_dump($category_value[0]['bcr_rdate']);
|
||
// foreach ($category_value as $index => $value) {
|
||
// // array_push($unique_dates['A'.$cell_row_index], $value['bcr_rdate']);
|
||
// $active_sheet->setCellValue('A'.($cell_row_index), $value['bcr_rdate']);
|
||
// $active_sheet->setCellValue($user_cell[$value['user_type']].($cell_row_index), $value['bcr_rdata']);
|
||
// // $previous_date = $value['sess_rdate'];
|
||
//
|
||
// $row_passed++;
|
||
// if ($row_passed == 3) {
|
||
// $cell_row_index++;
|
||
// $row_passed = 0;
|
||
// }
|
||
// // if ($value['vis_rdate'] == $previous_date) {
|
||
// // } else {
|
||
// // $active_sheet->setCellValue('A'.($cell_row_index), $value['vis_rdate']);
|
||
// // $active_sheet->setCellValue($user_cell[$value['vis_rdate']].($cell_row_index), $value['vis_rdate']);
|
||
// // }
|
||
// }
|
||
// break;
|
||
//
|
||
// default:
|
||
// // code...
|
||
// break;
|
||
// }
|
||
// // echo $key;
|
||
// // echo var_dump($value);
|
||
// }
|
||
//
|
||
// /*
|
||
// * second sheet data
|
||
// */
|
||
// $datasets = $this->getExportData(4, $start, $end);
|
||
// $active_sheet = $this->spreadsheet->setActiveSheetIndex(5);
|
||
// $date_indexes = array();
|
||
//
|
||
// // set interval
|
||
// $active_sheet->mergeCells('A1:V1')
|
||
// ->setCellValue('A1', "Du {$start_date_string} au {$end_date_string}");
|
||
//
|
||
// $previous_date = '';
|
||
// foreach ($datasets as $data_category => $category_value) {
|
||
// $cell_row_index = 4;
|
||
// $row_passed = 0;
|
||
// switch ($data_category) {
|
||
// case 'yearly_browsers':
|
||
// // echo var_dump($category_value[0]['vis_rdate']);
|
||
// foreach ($category_value as $index => $value) {
|
||
// $date_indexes[$value['year']] = $cell_row_index;
|
||
// $active_sheet->setCellValue('A'.($cell_row_index), $value['rtype']);
|
||
// $active_sheet->setCellValue('B'.($cell_row_index), $value['vis_rdata']);
|
||
// // $active_sheet->setCellValue('C'.($cell_row_index), $value['year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// break;
|
||
// case 'yearly_devices':
|
||
// // echo var_dump($category_value[0]['vis_rdate']);
|
||
// foreach ($category_value as $index => $value) {
|
||
// $date_indexes[$value['year']] = $cell_row_index;
|
||
// $active_sheet->setCellValue('E'.($cell_row_index), $value['rtype']);
|
||
// $active_sheet->setCellValue('F'.($cell_row_index), $value['vis_rdata']);
|
||
// // $active_sheet->setCellValue('G'.($cell_row_index), $value['year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// break;
|
||
// case 'yearly_url_clicks':
|
||
// // echo var_dump($category_value[0]['vis_rdate']);
|
||
// foreach ($category_value as $index => $value) {
|
||
// $date_indexes[$value['year']] = $cell_row_index;
|
||
// $active_sheet->setCellValue('I'.($cell_row_index), $value['gev_raction']);
|
||
// $active_sheet->setCellValue('J'.($cell_row_index), $value['gev_rdata']);
|
||
// // $active_sheet->setCellValue('K'.($cell_row_index), $value['year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// break;
|
||
// case 'yearly_contact_cat':
|
||
// // echo var_dump($category_value[0]['vis_rdate']);
|
||
// foreach ($category_value as $index => $value) {
|
||
// $date_indexes[$value['year']] = $cell_row_index;
|
||
// $active_sheet->setCellValue('M'.($cell_row_index), $value['c_category']);
|
||
// $active_sheet->setCellValue('N'.($cell_row_index), $value['clicks']);
|
||
// // $active_sheet->setCellValue('O'.($cell_row_index), $value['year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// break;
|
||
//
|
||
// default:
|
||
// // code...
|
||
// break;
|
||
// }
|
||
// }
|
||
//
|
||
// /*
|
||
// * third sheet data
|
||
// */
|
||
// $datasets = $this->getExportData(5, $start, $end);
|
||
// $active_sheet = $this->spreadsheet->setActiveSheetIndex(6);
|
||
// $date_indexes = array();
|
||
//
|
||
// // set interval
|
||
// $active_sheet->mergeCells('A1:V1')
|
||
// ->setCellValue('A1', "Du {$start_date_string} au {$end_date_string}");
|
||
//
|
||
// $previous_date = '';
|
||
// foreach ($datasets as $data_category => $category_value) {
|
||
// $cell_row_index = 4;
|
||
// $row_passed = 0;
|
||
// switch ($data_category) {
|
||
// case 'y0':
|
||
// // echo var_dump($category_value[0]['vis_rdate']);
|
||
// foreach ($category_value as $index => $value) {
|
||
// // $active_sheet->setCellValue('A'.($cell_row_index), $value['value']);
|
||
// $active_sheet->setCellValue('B3', $value['value']);
|
||
// // $active_sheet->setCellValue('B'.($cell_row_index), $value['data_year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// break;
|
||
// case 'yearly_fevents':
|
||
// foreach ($category_value as $index => $value) {
|
||
// if (!empty($value['bostat'])) {
|
||
// $active_sheet->setCellValue('E'.($cell_row_index), $value['bostat']);
|
||
// $active_sheet->setCellValue('F'.($cell_row_index), $value['value']);
|
||
// // $active_sheet->setCellValue('G'.($cell_row_index), $value['data_year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// }
|
||
// break;
|
||
// case 'y2':
|
||
// foreach ($category_value as $index => $value) {
|
||
// // $active_sheet->setCellValue('I'.($cell_row_index), $value['value']);
|
||
// $active_sheet->setCellValue('J3', $value['value']);
|
||
// // $active_sheet->setCellValue('J'.($cell_row_index), $value['data_year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// break;
|
||
// case 'y3':
|
||
// foreach ($category_value as $index => $value) {
|
||
// $active_sheet->setCellValue('M3', $value['value']);
|
||
// // $active_sheet->setCellValue('L'.($cell_row_index), $value['value']);
|
||
// // $active_sheet->setCellValue('M'.($cell_row_index), $value['data_year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// break;
|
||
// case 'y4':
|
||
// foreach ($category_value as $index => $value) {
|
||
// $active_sheet->setCellValue('P3', $value['value']);
|
||
// // $active_sheet->setCellValue('O'.($cell_row_index), $value['value']);
|
||
// // $active_sheet->setCellValue('P'.($cell_row_index), $value['data_year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// break;
|
||
// default:
|
||
// // code...
|
||
// break;
|
||
// }
|
||
// }
|
||
//
|
||
// /*
|
||
// * fourth sheet data
|
||
// */
|
||
// $datasets = $this->getExportData(6, $start, $end);
|
||
// $active_sheet = $this->spreadsheet->setActiveSheetIndex(7);
|
||
// $date_indexes = array();
|
||
//
|
||
// // set interval
|
||
// $active_sheet->mergeCells('A1:V1')
|
||
// ->setCellValue('A1', "Du {$start_date_string} au {$end_date_string}");
|
||
//
|
||
// $previous_date = '';
|
||
// foreach ($datasets as $data_category => $category_value) {
|
||
// $cell_row_index = 5;
|
||
// $row_passed = 0;
|
||
// switch ($data_category) {
|
||
// case 'yearly_s_evtype':
|
||
// foreach ($category_value as $index => $value) {
|
||
// if (!empty($value['label'])) {
|
||
// $active_sheet->setCellValue('A'.($cell_row_index), $value['label']);
|
||
// $active_sheet->setCellValue('B'.($cell_row_index), $value['gev_rdata']);
|
||
// // $active_sheet->setCellValue('C'.($cell_row_index), $value['year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// }
|
||
// break;
|
||
// case 'yearly_s_month':
|
||
// $months = array('Tous les mois', 'Janvier', 'Février', 'Mars', 'Avril', 'Mai',
|
||
// 'Juin', 'Juillet', 'Août', 'Septembre', 'Octobre', 'Novembre', 'Décembre');
|
||
// foreach ($category_value as $index => $value) {
|
||
// if (!empty($value['gev_raction'])) {
|
||
// $active_sheet->setCellValue('E'.($cell_row_index), $months[$value['gev_raction']]);
|
||
// $active_sheet->setCellValue('F'.($cell_row_index), $value['gev_rdata']);
|
||
// // $active_sheet->setCellValue('G'.($cell_row_index), $value['year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// }
|
||
// break;
|
||
// case 'yearly_s_city':
|
||
// foreach ($category_value as $index => $value) {
|
||
// if (!empty($value['label'])) {
|
||
// $active_sheet->setCellValue('I'.($cell_row_index), $value['label']);
|
||
// $active_sheet->setCellValue('J'.($cell_row_index), $value['gev_rdata']);
|
||
// // $active_sheet->setCellValue('K'.($cell_row_index), $value['year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// }
|
||
// break;
|
||
// case 'yearly_faq_search':
|
||
// foreach ($category_value as $index => $value) {
|
||
// $active_sheet->setCellValue('M'.($cell_row_index), $value['gev_raction']);
|
||
// $active_sheet->setCellValue('N'.($cell_row_index), $value['gev_rdata']);
|
||
// // $active_sheet->setCellValue('O'.($cell_row_index), $value['year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// break;
|
||
//
|
||
// case 'yearly_source_mediums':
|
||
// foreach ($category_value as $index => $value) {
|
||
// $active_sheet->setCellValue('Q'.($cell_row_index), $value['gsm_rsource']);
|
||
// $active_sheet->setCellValue('R'.($cell_row_index), $value['gsm_rsourcem']);
|
||
// $active_sheet->setCellValue('S'.($cell_row_index), $value['gsm_rchannelgroup']);
|
||
// $active_sheet->setCellValue('T'.($cell_row_index), $value['gsm_rdata']);
|
||
// // $active_sheet->setCellValue('U'.($cell_row_index), $value['year']);
|
||
// $cell_row_index++;
|
||
// }
|
||
// break;
|
||
// default:
|
||
// // code...
|
||
// break;
|
||
// }
|
||
// }
|
||
// }
|
||
/**
|
||
* Export no show file with title+date
|
||
*
|
||
* @param integer $eventID
|
||
* @param integer $eventScheduleID
|
||
* @return void
|
||
* access private member
|
||
*/
|
||
public function export($start, $end)
|
||
{
|
||
//call memory limit
|
||
$this->setMemoryLimit();
|
||
|
||
//call document properties
|
||
$this->setDocumentProperties();
|
||
|
||
|
||
//set php excel first 5 row(s) with freeze header
|
||
$this->setExcelHeaders();
|
||
|
||
//get data for populate
|
||
$this->reportData($start, $end);
|
||
|
||
//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);
|
||
|
||
// protect the workbok
|
||
$this->spreadsheet->getSecurity()->setLockWindows(true);
|
||
$this->spreadsheet->getSecurity()->setLockStructure(true);
|
||
|
||
$this->spreadsheet->getSecurity()->setWorkbookPassword('secret');
|
||
|
||
//call headers and download file
|
||
$start_date_string = date('d/m/Y', strtotime($start));
|
||
$end_date_string = date('d/m/Y', strtotime($end));
|
||
|
||
$this->setHeaders('Données statistiques - '.date('Y_m_d H:i:s')." (Du {$start_date_string} au {$end_date_string})");
|
||
}
|
||
}
|