Sarobidy22
/
MasterClass
1
0
Fork 0
Code Issues Pull Requests Projects Releases Wiki Activity
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
1 Commit
1 Branch
0 Tags
117 MiB
 
 
 
 
 
 
Branch: master
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'master'
${ noResults }
MasterClass/application/controllers/dashboard/Dashboard_export_controller...

2440 lines
116 KiB
Raw Permalink Blame History

<?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})");
}
}