/**
*
* @param unknown_type $from
* @param unknown_type $to
* @param unknown_type $numberImputations
* @param unknown_type $totalDuration
*/
private function _exportDetailedValuesByCountryAndCity($from, $to, $numberImputations, $totalTime)
{
$begin_row = $this->_row;
$active_sheet = $this->getDocument()->getActiveSheet();
/* --------------- EXPORTATION OF THE DETAILED STATS BY AGE RANGE -----------------*/
$countries = array();
$cities = array();
$citiesTable = array();
$imputations = Doctrine_Query::create()->select('i.*')->from('ImputationArchive i')->where('imputation_date BETWEEN ? AND ?', array($from, $to))->execute();
foreach ($imputations as $imputation) {
$country = $imputation->getUserArchive()->getCountry();
$city = $imputation->getUserArchive()->getCityName();
if (!in_array($country, $countries)) {
$countries[] = $country;
}
if (!in_array($city, $citiesTable)) {
$citiesTable[] = $city;
$cities[$country][] = $city;
}
}
$countryCityStats = Statistics::getDetailedValuesByCountryAndCity($from, $to, $numberImputations);
/*------------------------------ TITLE -----------------------------*/
$active_sheet->setCellValueByColumnAndRow(0, $this->_row, $this->_translate(self::BY_COUNTRY_CITY));
$active_sheet->mergeCells('A' . $this->_row . ':D' . $this->_row);
$active_sheet->getStyle('A' . $this->_row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$active_sheet->getStyle('A' . $this->_row)->getFont()->setBold(true);
$active_sheet->getStyle('A' . $this->_row)->getFill()->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => '808080'), 'endcolor' => array('rgb' => '808080')));
/*----------------------------- END TITLE ---------------------------*/
$this->nextLine();
/*------------------------------ HEADER -----------------------------*/
$active_sheet->setCellValueByColumnAndRow(0, $this->_row, $this->_translate('Country/city'));
$active_sheet->setCellValueByColumnAndRow(1, $this->_row, $this->_translate('Total of uses (with a duration or not)'));
$active_sheet->setCellValueByColumnAndRow(2, $this->_row, $this->_translate('Percentage'));
$active_sheet->setCellValueByColumnAndRow(3, $this->_row, $this->_translate('Time'));
$active_sheet->getStyle('A' . $this->_row . ':D' . $this->_row)->getFont()->setBold(true);
$active_sheet->getStyle('B' . $this->_row . ':D' . $this->_row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
/*----------------------------- END HEADER ---------------------------*/
$this->nextLine();
/*------------------------------ BODY -----------------------------*/
foreach ($countries as $country) {
$active_sheet->setCellValueByColumnAndRow(0, $this->_row, $this->_translate($country));
$active_sheet->setCellValueByColumnAndRow(1, $this->_row, $countryCityStats[$country][0]);
$active_sheet->setCellValueByColumnAndRow(2, $this->_row, $countryCityStats[$country][1] . ' %');
$active_sheet->setCellValueByColumnAndRow(3, $this->_row, gmdate('G', $countryCityStats[$country][2]) . 'h' . gmdate('i', $countryCityStats[$country][2]));
$active_sheet->getStyle('B' . $this->_row . ':D' . $this->_row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$active_sheet->getStyle('A' . $this->_row . ':D' . $this->_row)->getFill()->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => 'DDDDDD'), 'endcolor' => array('rgb' => 'DDDDDD')));
$this->nextLine();
foreach ($cities[$country] as $city) {
$active_sheet->setCellValueByColumnAndRow(0, $this->_row, $city);
$active_sheet->setCellValueByColumnAndRow(1, $this->_row, $countryCityStats[$country][$city][0]);
$active_sheet->setCellValueByColumnAndRow(2, $this->_row, $countryCityStats[$country][$city][1] . ' %');
$active_sheet->setCellValueByColumnAndRow(3, $this->_row, gmdate('G', $countryCityStats[$country][$city][2]) . 'h' . gmdate('i', $countryCityStats[$country][$city][2]));
$active_sheet->getStyle('A' . $this->_row)->getAlignment()->setIndent(1);
$active_sheet->getStyle('B' . $this->_row . ':D' . $this->_row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$this->nextLine();
}
}
/*----------------------------- END BODY ---------------------------*/
/*------------------------------ FOOTER -----------------------------*/
$active_sheet->setCellValueByColumnAndRow(0, $this->_row, $this->_translate('Total'));
$active_sheet->setCellValueByColumnAndRow(1, $this->_row, $numberImputations);
$active_sheet->setCellValueByColumnAndRow(2, $this->_row, '100%');
$active_sheet->setCellValueByColumnAndRow(3, $this->_row, gmdate('G', $totalTime) . 'h' . gmdate('i', $totalTime));
$active_sheet->getStyle('A' . $this->_row . ':D' . $this->_row)->getFont()->setBold(true);
$active_sheet->getStyle('B' . $this->_row . ':D' . $this->_row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$active_sheet->getStyle('A' . $this->_row . ':D' . $this->_row)->getFill()->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => 'C0C0C0'), 'endcolor' => array('rgb' => 'C0C0C0')));
/*----------------------------- END FOOTER ---------------------------*/
$active_sheet->getStyle('A' . ($begin_row + 1) . ':D' . $this->_row)->applyFromArray(array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN, 'color' => array('argb' => PHPExcel_Style_Color::COLOR_BLACK)))));
}
public function executeAjaxDetailedStat(sfWebRequest $request, $xhr = true)
{
$begin_date = $request->getParameter('from');
$end_date = $request->getParameter('to');
$this->from = $begin_date;
$this->to = $end_date;
$this->xhr = $xhr;
$this->userCulture = $this->getUser()->getCulture();
$countries = array();
$cities = array();
$citiesTable = array();
$imputationsGlobal = Doctrine_Query::create()->select('COUNT(*) as number, SUM(TIME_TO_SEC(i.duration)) as duration')->from('ImputationArchive i')->where('i.imputation_date BETWEEN ? AND ?', array($begin_date, $end_date))->fetchOne();
$imputations = Doctrine_Query::create()->select('i.*')->from('ImputationArchive i')->where('imputation_date BETWEEN ? AND ?', array($begin_date, $end_date))->execute();
$numberImputations = $imputationsGlobal->getNumber();
$totalDuration = $imputationsGlobal->getDuration();
/*Global statistics*/
$this->numberImputations = $numberImputations;
$this->totalTime = $totalDuration;
if ($numberImputations != 0) {
/*Gender Stats*/
$this->genderStats = Statistics::getDetailedValuesByGender($begin_date, $end_date, $numberImputations);
/*Age Range Stats*/
$this->ageRangeStats = Statistics::getDetailedValuesByAgeRange($begin_date, $end_date, $numberImputations);
/*Countries/Cities Stats*/
foreach ($imputations as $imputation) {
$country = $imputation->getUserArchive()->getCountry();
$city = $imputation->getUserArchive()->getCityName();
if (!in_array($country, $countries)) {
$countries[] = $country;
}
if (!in_array($city, $citiesTable)) {
$citiesTable[] = $city;
$cities[$country][] = $city;
}
}
$this->countries = $countries;
$this->cities = $cities;
$this->countryCityStats = Statistics::getDetailedValuesByCountryAndCity($begin_date, $end_date, $numberImputations);
/*By day/time slot Stats*/
$this->days = array('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday');
$dayStats = Statistics::getDetailedValuesByDay($begin_date, $end_date, $numberImputations);
$this->dayStats = $dayStats;
$timeslots = array('8h-12h', '12h-16h', '16h-20h', '20h-24h');
// KYXAR 0010 : Changement du nom de la fonction getDetailedValuesByTimeSlot -> getDetailedValuesByDaysAndTimeSlot
//$this->dayAndTimeSlotStats = Statistics::getDetailedValuesByTimeSlot($begin_date,$end_date);
$this->dayAndTimeSlotStats = Statistics::getDetailedValuesByDaysAndTimeSlot($begin_date, $end_date);
// KYXAR 0010 : Appel de la nouvelle fonction getDetailedValuesByTimeSlot
$this->TimeSlotStats = Statistics::getDetailedValuesByTimeSlot($begin_date, $end_date);
$hours[$timeslots[0]] = array('8h-9h', '9h-10h', '10h-11h', '11h-12h');
$hours[$timeslots[1]] = array('12h-13h', '13h-14h', '14h-15h', '15h-16h');
$hours[$timeslots[2]] = array('16h-17h', '17h-18h', '18h-19h', '19h-20h');
$hours[$timeslots[3]] = array('20h-21h', '21h-22h', '22h-23h', '23h-24h');
$this->timeslots = $timeslots;
$this->hours = $hours;
$this->hourStats = Statistics::getDetailedValuesByHour($begin_date, $end_date, $dayStats);
/*By public categories*/
$this->publicCategoryStats = Statistics::getDetailedValuesByPublicCategory($begin_date, $end_date, $numberImputations);
/*By act*/
$this->actStats = Statistics::getDetailedValuesByAct($begin_date, $end_date, $numberImputations);
/*By SEG*/
$this->segStats = Statistics::getDetailedValuesBySeg($begin_date, $end_date, $numberImputations);
/*By building/room/computer*/
$this->buildingStats = Statistics::getDetailedValuesByBuilding($begin_date, $end_date, $numberImputations);
$this->roomStats = Statistics::getDetailedValuesByRoom($begin_date, $end_date);
$this->computerStats = Statistics::getDetailedValuesByComputer($begin_date, $end_date);
/*By type of connection*/
$this->typeOfConnectionStats = Statistics::getDetailedValuesByTypeOfConnection($begin_date, $end_date);
/*By way of awareness*/
$this->awarenessStats = Statistics::getDetailedValuesByAwareness($begin_date, $end_date, $numberImputations);
} else {
$this->getUser()->setFlash('notice', 'No uses found between these two dates.');
$this->setTemplate('index', 'stat');
}
$this->setTemplate('detailedStat', 'stat');
}
