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.
806 lines
38 KiB
806 lines
38 KiB
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
|
|
|
|
class Dashboard_statistics_model extends CI_Model{
|
|
|
|
public function __construct() {
|
|
parent::__construct();
|
|
}
|
|
|
|
public function get_refresh_token(){
|
|
$this->db->select('oauth_code_id, refresh_token, access_token, access_token_expiration');
|
|
$this->db->where('status', 1);
|
|
$result = $this->db->get('event_dashboard_gaoauth_code');
|
|
if($result->num_rows() > 0){
|
|
return (array) $result->row();
|
|
}
|
|
return false;
|
|
}
|
|
|
|
public function update_access_token($oauth_code_id, $data){
|
|
$this->db->where('oauth_code_id', $oauth_code_id);
|
|
$this->db->update('event_dashboard_gaoauth_code', $data);
|
|
return $this->db->affected_rows();
|
|
}
|
|
|
|
public function save_new_oauth_code($data){
|
|
//set previuos oauth code to obsolete
|
|
$this->db->where('status', 1);
|
|
$this->db->update('event_dashboard_gaoauth_code', array('status' => 0));
|
|
|
|
//and save the new oauth code
|
|
$this->db->insert('event_dashboard_gaoauth_code', $data);
|
|
return $this->db->insert_id();
|
|
}
|
|
|
|
public function gsignout_by_user($user_id){
|
|
$this->db->where('user_id', $user_id);
|
|
$this->db->where('status', 1);
|
|
$this->db->update('event_dashboard_gaoauth_code', array("status" => 0));
|
|
return $this->db->affected_rows();
|
|
}
|
|
|
|
|
|
private function check_ga_date($date){
|
|
$d = DateTime::createFromFormat('d-m-Y', $date);
|
|
$is_date = $d && $d->format('d-m-Y') === $date;
|
|
if (!$is_date) {
|
|
$d2 = DateTime::createFromFormat('Y-m-d', $date);
|
|
$is_date = $d && $d->format('Y-m-d') === $date;
|
|
}
|
|
|
|
$days = '-0';
|
|
|
|
if ($is_date) {
|
|
return date('Y-m-d', strtotime($date));
|
|
}
|
|
if (!$is_date) {
|
|
$n_date = strtolower($date);
|
|
if ($n_date == 'start_date') {
|
|
return date('2015-01-01');
|
|
}
|
|
if ($n_date == 'end_date') {
|
|
return date('Y-m-d');
|
|
}
|
|
switch ($n_date) {
|
|
case 'today':
|
|
$days = '-0';
|
|
break;
|
|
case 'yesterday':
|
|
$days = '-1';
|
|
break;
|
|
case '7daysago':
|
|
$days = '-7';
|
|
break;
|
|
case '30daysago':
|
|
$days = '-30';
|
|
break;
|
|
}
|
|
return date('Y-m-d', strtotime("$days days"));
|
|
}
|
|
}
|
|
|
|
public function get_dashboard_table_data($category, $start_date, $end_date){
|
|
|
|
// check start date
|
|
$start_date = $this->check_ga_date($start_date);
|
|
// // check end date
|
|
$end_date = $this->check_ga_date($end_date);
|
|
|
|
/**
|
|
* y0 = number of registrations yearly
|
|
* y1 = number of events yearly
|
|
* y2 = number of reservations done per subscriber
|
|
* y3 = number of reservations
|
|
* y4 = total number of places reserved
|
|
* y5 = total number of places cancelled
|
|
*/
|
|
// $start_year
|
|
$cur_date = date("Y-m-d");
|
|
$cur_year = date("Y");
|
|
$prev_year = date("Y",strtotime("-1 year"));
|
|
|
|
// compute date differences
|
|
$date1 = new DateTime($start_date);
|
|
$date2 = new DateTime($end_date);
|
|
|
|
$date_1 = $date1->format('Y-m-d');
|
|
$date_2 = $date2->format('Y-m-d');
|
|
|
|
$y_date_1 = $date1->format('Y');
|
|
$y_date_2 = $date2->format('Y');
|
|
|
|
$interval = ($date1->diff($date2))->format('%a Derniers Jours');
|
|
if ($cur_date == $end_date && $interval <= 30) {
|
|
$diff = $interval;
|
|
} else {
|
|
$diff = ($date1)->format('M d').' - '.($date2)->format('M d');
|
|
}
|
|
// if (condition) {
|
|
// // code...
|
|
// }
|
|
|
|
switch ($category) {
|
|
case 'filtered_browsers':
|
|
$query = "SELECT DATE_FORMAT(vis_rdate, '%Y') year, SUM(vis_rdata) vis_rdata, br_rtype rtype
|
|
FROM ga_visitors
|
|
WHERE br_rtype != 'UNDEFINED' AND
|
|
DATE_FORMAT(vis_rdate, '%Y-%m-%d') >= '$start_date' AND
|
|
DATE_FORMAT(vis_rdate, '%Y-%m-%d') <= '$end_date'
|
|
GROUP BY br_rtype
|
|
ORDER BY SUM(vis_rdata) DESC, vis_rdate DESC";
|
|
break;
|
|
case 'filtered_devices':
|
|
$query = "SELECT DATE_FORMAT(vis_rdate, '%Y') year, SUM(vis_rdata) vis_rdata, dv_rtype rtype
|
|
FROM ga_visitors
|
|
WHERE dv_rtype != 'UNDEFINED' AND
|
|
DATE_FORMAT(vis_rdate, '%Y-%m-%d') >= '$start_date' AND
|
|
DATE_FORMAT(vis_rdate, '%Y-%m-%d') <= '$end_date'
|
|
GROUP BY dv_rtype
|
|
ORDER BY SUM(vis_rdata) DESC, vis_rdate DESC";
|
|
break;
|
|
case 'filtered_url_clicks':
|
|
$query = "SELECT SUM(gev_rdata) gev_rdata, gev_raction , DATE_FORMAT(gev_rdate, '%Y') year
|
|
FROM ga_events
|
|
WHERE gev_rcategory = 'click' AND gev_raction NOT LIKE '%javascript:%' AND
|
|
DATE_FORMAT(gev_rdate, '%Y-%m-%d') >= '$start_date' AND
|
|
DATE_FORMAT(gev_rdate, '%Y-%m-%d') <= '$end_date'
|
|
GROUP BY gev_raction
|
|
ORDER BY gev_rdata DESC";
|
|
break;
|
|
|
|
case 'filtered_contact_cat':
|
|
$query = "SELECT COUNT(c_id) clicks, c_category, DATE_FORMAT(c_date_sent, '%Y') year
|
|
FROM contact_email
|
|
WHERE
|
|
DATE_FORMAT(c_date_sent, '%Y-%m-%d') >= '$start_date' AND
|
|
DATE_FORMAT(c_date_sent, '%Y-%m-%d') <= '$end_date'
|
|
GROUP BY c_category
|
|
ORDER BY COUNT(c_id) DESC, DATE_FORMAT(c_date_sent, '%Y') DESC";
|
|
break;
|
|
|
|
case 'yearly_browsers':
|
|
$query = "SELECT DATE_FORMAT(vis_rdate, '%Y') year, SUM(vis_rdata) vis_rdata, br_rtype rtype
|
|
FROM ga_visitors
|
|
WHERE br_rtype != 'UNDEFINED'
|
|
GROUP BY DATE_FORMAT(vis_rdate, '%Y'), br_rtype
|
|
ORDER BY SUM(vis_rdata) DESC, vis_rdate DESC";
|
|
break;
|
|
case 'yearly_devices':
|
|
$query = "SELECT DATE_FORMAT(vis_rdate, '%Y') year, SUM(vis_rdata) vis_rdata, dv_rtype rtype
|
|
FROM ga_visitors
|
|
WHERE dv_rtype != 'UNDEFINED'
|
|
GROUP BY DATE_FORMAT(vis_rdate, '%Y'), dv_rtype
|
|
ORDER BY SUM(vis_rdata) DESC, vis_rdate DESC";
|
|
break;
|
|
case 'yearly_url_clicks':
|
|
$query = "SELECT SUM(gev_rdata) gev_rdata, gev_raction , DATE_FORMAT(gev_rdate, '%Y') year
|
|
FROM ga_events
|
|
WHERE gev_rcategory = 'click' AND gev_raction NOT LIKE '%javascript:%'
|
|
GROUP BY gev_raction, DATE_FORMAT(gev_rdate, '%Y')
|
|
ORDER BY gev_rdata DESC";
|
|
break;
|
|
|
|
case 'yearly_contact_cat':
|
|
$query = "SELECT COUNT(c_id) clicks, c_category, DATE_FORMAT(c_date_sent, '%Y') year
|
|
FROM contact_email
|
|
GROUP BY c_category, DATE_FORMAT(c_date_sent, '%Y')
|
|
ORDER BY DATE_FORMAT(c_date_sent, '%Y') DESC";
|
|
break;
|
|
case 'yearly_fevents':
|
|
$query = "SELECT DATE_FORMAT(e.date_created, '%Y') data_year, COUNT(e.event_id) value,
|
|
(CASE
|
|
-- coming soon
|
|
WHEN e.back_office_status = 1 THEN 'Prochainement'
|
|
-- being created
|
|
WHEN e.back_office_status = 0 THEN 'En Cours'
|
|
-- complete
|
|
WHEN ((e.back_office_status = 2 OR e.back_office_status = 3) AND e.event_status ='FULL'
|
|
AND (CASE
|
|
WHEN e.remaining_combined_seat >=0 )
|
|
THEN 'Complets'
|
|
-- terminee
|
|
WHEN e.back_office_status = 7 THEN 'Terminés'
|
|
|
|
-- passe
|
|
WHEN e.back_office_status = 4 THEN 'Passés'
|
|
-- archived
|
|
WHEN e.back_office_status = 5 THEN 'Archivés'
|
|
END) bostat
|
|
FROM event e
|
|
WHERE DATE_FORMAT(e.date_created, '%Y') != 0
|
|
GROUP BY e.back_office_status, DATE_FORMAT(e.date_created, '%Y')
|
|
ORDER BY e.date_created DESC, e.back_office_status DESC";
|
|
break;
|
|
case 'f_fevents':
|
|
$query = "SELECT DATE_FORMAT(e.date_created, '%Y') data_year, COUNT(e.event_id) value,
|
|
(CASE
|
|
-- coming soon
|
|
WHEN e.back_office_status = 1 THEN 'Prochainement'
|
|
-- being created
|
|
WHEN e.back_office_status = 0 THEN 'En Cours'
|
|
-- complete
|
|
WHEN ((e.back_office_status = 2 OR e.back_office_status = 3) AND e.event_status ='FULL'
|
|
AND e.remaining_combined_seat >=0 )
|
|
THEN 'Complets'
|
|
-- terminee
|
|
WHEN e.back_office_status = 7 THEN 'Terminés'
|
|
|
|
-- passe
|
|
WHEN e.back_office_status = 4 THEN 'Passés'
|
|
-- archived
|
|
WHEN e.back_office_status = 5 THEN 'Archivés'
|
|
END) bostat
|
|
FROM event e
|
|
WHERE DATE_FORMAT(e.date_created, '%Y') != 0 AND
|
|
DATE_FORMAT(e.date_created, '%Y-%m-%d') >= '$start_date' AND
|
|
DATE_FORMAT(e.date_created, '%Y-%m-%d') <= '$end_date'
|
|
GROUP BY e.back_office_status
|
|
ORDER BY e.date_created DESC, e.back_office_status DESC";
|
|
break;
|
|
// Recherches Internes
|
|
case 'yearly_s_evtype':
|
|
$query = "SELECT SUM(gev_rdata) gev_rdata, gev_raction, gev_rdatalabel,
|
|
(SELECT event_type FROM event_type WHERE event_type_id = gev_raction) label
|
|
, DATE_FORMAT(gev_rdate, '%Y') year
|
|
FROM ga_events
|
|
WHERE gev_rcategory = 'fo_filter' AND gev_rdatalabel = 'filter-type'
|
|
GROUP BY gev_raction, gev_rdatalabel, DATE_FORMAT(gev_rdate, '%Y')
|
|
ORDER BY DATE_FORMAT(gev_rdate, '%Y') DESC, SUM(gev_rdata) DESC";
|
|
break;
|
|
case 'f_s_evtype':
|
|
$query = "SELECT SUM(gev_rdata) gev_rdata, gev_raction, gev_rdatalabel,
|
|
(SELECT event_type FROM event_type WHERE event_type_id = gev_raction) label
|
|
, DATE_FORMAT(gev_rdate, '%Y') year
|
|
FROM ga_events
|
|
WHERE gev_rcategory = 'fo_filter' AND gev_rdatalabel = 'filter-type' AND
|
|
DATE_FORMAT(gev_rdate, '%Y-%m-%d') >= '$start_date' AND
|
|
DATE_FORMAT(gev_rdate, '%Y-%m-%d') <= '$end_date'
|
|
GROUP BY gev_raction, gev_rdatalabel
|
|
ORDER BY SUM(gev_rdata) DESC";
|
|
break;
|
|
case 'yearly_s_month':
|
|
$query = "SELECT SUM(gev_rdata) gev_rdata, gev_raction, gev_rdatalabel, DATE_FORMAT(gev_rdate, '%Y') year
|
|
FROM ga_events
|
|
WHERE gev_rcategory = 'fo_filter' AND gev_rdatalabel = 'filter-month'
|
|
GROUP BY gev_raction, gev_rdatalabel, DATE_FORMAT(gev_rdate, '%Y')
|
|
ORDER BY DATE_FORMAT(gev_rdate, '%Y') DESC, SUM(gev_rdata) DESC";
|
|
break;
|
|
case 'f_s_month':
|
|
$query = "SELECT SUM(gev_rdata) gev_rdata, gev_raction, gev_rdatalabel, DATE_FORMAT(gev_rdate, '%Y') year
|
|
FROM ga_events
|
|
WHERE gev_rcategory = 'fo_filter' AND gev_rdatalabel = 'filter-month' AND
|
|
DATE_FORMAT(gev_rdate, '%Y-%m-%d') >= '$start_date' AND
|
|
DATE_FORMAT(gev_rdate, '%Y-%m-%d') <= '$end_date'
|
|
GROUP BY gev_raction, gev_rdatalabel
|
|
ORDER BY SUM(gev_rdata) DESC";
|
|
break;
|
|
case 'yearly_s_city':
|
|
$query = "SELECT SUM(gev_rdata) gev_rdata, gev_raction, gev_rdatalabel,
|
|
(SELECT city FROM event_city_location WHERE city_location_id = gev_raction) label
|
|
, DATE_FORMAT(gev_rdate, '%Y') year
|
|
FROM ga_events
|
|
WHERE gev_rcategory = 'fo_filter' AND gev_rdatalabel = 'filter-city'
|
|
GROUP BY gev_raction, gev_rdatalabel, DATE_FORMAT(gev_rdate, '%Y')
|
|
ORDER BY DATE_FORMAT(gev_rdate, '%Y') DESC, SUM(gev_rdata) DESC";
|
|
break;
|
|
case 'f_s_city':
|
|
$query = "SELECT SUM(gev_rdata) gev_rdata, gev_raction, gev_rdatalabel,
|
|
(SELECT city FROM event_city_location WHERE city_location_id = gev_raction) label
|
|
, DATE_FORMAT(gev_rdate, '%Y') year
|
|
FROM ga_events
|
|
WHERE gev_rcategory = 'fo_filter' AND gev_rdatalabel = 'filter-city' AND
|
|
DATE_FORMAT(gev_rdate, '%Y-%m-%d') >= '$start_date' AND
|
|
DATE_FORMAT(gev_rdate, '%Y-%m-%d') <= '$end_date'
|
|
GROUP BY gev_raction, gev_rdatalabel
|
|
ORDER BY DATE_FORMAT(gev_rdate, '%Y') DESC, SUM(gev_rdata) DESC";
|
|
break;
|
|
|
|
case 'yearly_faq_search':
|
|
$query = "SELECT SUM(gev_rdata) gev_rdata, gev_raction,
|
|
DATE_FORMAT(gev_rdate, '%Y') year
|
|
FROM ga_events
|
|
WHERE gev_rcategory = 'faq_search'
|
|
AND
|
|
gev_raction !='undefined'
|
|
GROUP BY gev_raction, DATE_FORMAT(gev_rdate, '%Y')
|
|
ORDER BY DATE_FORMAT(gev_rdate, '%Y') DESC, SUM(gev_rdata) DESC";
|
|
break;
|
|
case 'f_faq_search':
|
|
$query = "SELECT SUM(gev_rdata) gev_rdata, gev_raction,
|
|
DATE_FORMAT(gev_rdate, '%Y') year
|
|
FROM ga_events
|
|
WHERE gev_rcategory = 'faq_search'
|
|
AND
|
|
gev_raction !='undefined' AND
|
|
DATE_FORMAT(gev_rdate, '%Y-%m-%d') >= '$start_date' AND
|
|
DATE_FORMAT(gev_rdate, '%Y-%m-%d') <= '$end_date'
|
|
GROUP BY gev_raction
|
|
ORDER BY DATE_FORMAT(gev_rdate, '%Y') DESC, SUM(gev_rdata) DESC";
|
|
break;
|
|
|
|
case 'yearly_source_mediums':
|
|
$query = "SELECT SUM(gsm_rdata) gsm_rdata, gsm_rsource, gsm_rsourcem, gsm_rchannelgroup, DATE_FORMAT(gsm_rdate, '%Y') year
|
|
FROM ga_source_mediums
|
|
GROUP BY gsm_rsource, gsm_rsourcem, gsm_rchannelgroup, DATE_FORMAT(gsm_rdate, '%Y')
|
|
ORDER BY DATE_FORMAT(gsm_rdate, '%Y') DESC, SUM(gsm_rdata) DESC";
|
|
break;
|
|
case 'f_source_mediums':
|
|
$query = "SELECT SUM(gsm_rdata) gsm_rdata, gsm_rsource, gsm_rsourcem, gsm_rchannelgroup, DATE_FORMAT(gsm_rdate, '%Y') year
|
|
FROM ga_source_mediums
|
|
WHERE
|
|
DATE_FORMAT(gsm_rdate, '%Y-%m-%d') >= '$start_date' AND
|
|
DATE_FORMAT(gsm_rdate, '%Y-%m-%d') <= '$end_date'
|
|
GROUP BY gsm_rsource, gsm_rsourcem, gsm_rchannelgroup
|
|
ORDER BY DATE_FORMAT(gsm_rdate, '%Y') DESC, SUM(gsm_rdata) DESC";
|
|
break;
|
|
|
|
case 'url_clicks':
|
|
$query = "SELECT SUM(gev_rdata) gev_rdata, gev_raction ,gev_rdate
|
|
FROM ga_events
|
|
WHERE gev_rcategory = 'click' AND gev_raction NOT LIKE '%javascript:%' AND gev_raction != 'undefined' AND
|
|
DATE_FORMAT(gev_rdate, '%Y-%m-%d') >= '$start_date' AND
|
|
DATE_FORMAT(gev_rdate, '%Y-%m-%d') <= '$end_date'
|
|
GROUP BY gev_raction
|
|
ORDER BY SUM(gev_rdata) DESC LIMIT 12";
|
|
break;
|
|
|
|
case 'y_contact':
|
|
$query = "SELECT COUNT(c_id) value, DATE_FORMAT(c_date_sent, '%Y') data_year
|
|
FROM contact_email
|
|
GROUP BY DATE_FORMAT(c_date_sent, '%Y')
|
|
ORDER BY data_year DESC";
|
|
break;
|
|
|
|
case 'contact_cat':
|
|
$query = "SELECT COUNT(c_id) value, c_category
|
|
FROM contact_email
|
|
WHERE
|
|
DATE_FORMAT(c_date_sent, '%Y-%m-%d') >= '$start_date' AND
|
|
DATE_FORMAT(c_date_sent, '%Y-%m-%d') <= '$end_date'
|
|
GROUP BY c_category
|
|
ORDER BY DATE_FORMAT(c_date_sent, '%Y') DESC";
|
|
break;
|
|
|
|
// Nombre d'inscriptions (Nouveau qui a confirmé son formulaire de première inscription)
|
|
case 'y0':
|
|
$query = "SELECT DATE_FORMAT(subscription_date, '%Y') data_year, COUNT(subscriber_id) value
|
|
FROM user_subscriber
|
|
WHERE DATE_FORMAT(subscription_date, '%Y') != 0
|
|
GROUP BY DATE_FORMAT(subscription_date, '%Y')
|
|
ORDER BY DATE_FORMAT(subscription_date, '%Y') DESC";
|
|
break;
|
|
case 'f_y0':
|
|
$query = "SELECT DATE_FORMAT(subscription_date, '%Y') data_year, COUNT(subscriber_id) value
|
|
FROM user_subscriber
|
|
WHERE DATE_FORMAT(subscription_date, '%Y') != 0 AND
|
|
DATE_FORMAT(subscription_date, '%Y-%m-%d') >= '$start_date' AND
|
|
DATE_FORMAT(subscription_date, '%Y-%m-%d') <= '$end_date'";
|
|
break;
|
|
case 'y1':
|
|
$query = "SELECT DATE_FORMAT(date_created, '%Y') data_year, COUNT(event_id) value
|
|
FROM event
|
|
WHERE DATE_FORMAT(date_created, '%Y') != 0 AND back_office_status != 6
|
|
GROUP BY DATE_FORMAT(date_created, '%Y')
|
|
ORDER BY DATE_FORMAT(date_created, '%Y') DESC";
|
|
break;
|
|
case 'f_y1':
|
|
$query = "SELECT 'Total Période' data_year, COUNT(event_id) value
|
|
FROM event
|
|
WHERE DATE_FORMAT(date_created, '%Y') != 0 AND
|
|
DATE_FORMAT(date_created, '%Y-%m-%d') >= '$start_date' AND
|
|
DATE_FORMAT(date_created, '%Y-%m-%d') <= '$end_date'
|
|
AND back_office_status != 6";
|
|
break;
|
|
|
|
// Nombre de réservations effectuées (par abonnés)
|
|
case 'y2':
|
|
$query = "SELECT DATE_FORMAT(date_time, '%Y') data_year,
|
|
COUNT(subscriber) subs_count,
|
|
((SUM(number_of_guest)+1)/COUNT(subscriber)) value
|
|
FROM event_registration
|
|
WHERE status = 1 AND DATE_FORMAT(date_time, '%Y') != 0
|
|
GROUP BY DATE_FORMAT(date_time, '%Y')
|
|
ORDER BY DATE_FORMAT(date_time, '%Y') DESC";
|
|
break;
|
|
case 'f_y2':
|
|
$query = "SELECT 'Total Période' data_year,
|
|
COUNT(subscriber) subs_count,
|
|
((SUM(number_of_guest)+1)/COUNT(subscriber)) value
|
|
FROM event_registration
|
|
WHERE status = 1 AND DATE_FORMAT(date_time, '%Y') != 0 AND
|
|
DATE_FORMAT(date_time, '%Y-%m-%d') >= '$start_date' AND
|
|
DATE_FORMAT(date_time, '%Y-%m-%d') <= '$end_date'";
|
|
break;
|
|
|
|
// Nombre de places réservées
|
|
case 'y3':
|
|
$query = "SELECT DATE_FORMAT(date_time, '%Y') data_year, (SUM(number_of_guest) + COUNT(subscriber)) value
|
|
FROM event_registration
|
|
WHERE status = 1 AND DATE_FORMAT(date_time, '%Y') != 0
|
|
GROUP BY DATE_FORMAT(date_time, '%Y')
|
|
ORDER BY DATE_FORMAT(date_time, '%Y') DESC";
|
|
break;
|
|
case 'f_y3':
|
|
$query = "SELECT '30 Derniers Jours' data_year, (SUM(number_of_guest) + COUNT(subscriber)) value
|
|
FROM event_registration
|
|
WHERE status = 1 AND DATE_FORMAT(date_time, '%Y') != 0 AND
|
|
DATE_FORMAT(date_time, '%Y-%m-%d') >= '$start_date' AND
|
|
DATE_FORMAT(date_time, '%Y-%m-%d') <= '$end_date'";
|
|
break;
|
|
|
|
//Nombre d'annulations effectuées (annulation totale de réservation)
|
|
case 'y4':
|
|
$query = "SELECT DATE_FORMAT(date_time, '%Y') data_year, (SUM(number_of_guest) + COUNT(subscriber)) value
|
|
FROM event_registration
|
|
WHERE status = 0 AND DATE_FORMAT(date_time, '%Y') != 0
|
|
GROUP BY DATE_FORMAT(date_time, '%Y')
|
|
ORDER BY DATE_FORMAT(date_time, '%Y') DESC";
|
|
break;
|
|
case 'f_y4':
|
|
$query = "SELECT 'Total Période' dinterval, (SUM(number_of_guest) + COUNT(subscriber)) value
|
|
FROM event_registration
|
|
WHERE status = 0 AND DATE_FORMAT(date_time, '%Y') != 0 AND
|
|
DATE_FORMAT(date_time, '%Y-%m-%d') >= '$start_date' AND
|
|
DATE_FORMAT(date_time, '%Y-%m-%d') <= '$end_date'";
|
|
break;
|
|
|
|
case 'fsubs':
|
|
$query = "SELECT 'Total Période' dinterval, COUNT(subscriber_id) value
|
|
FROM user_subscriber
|
|
WHERE DATE_FORMAT(subscription_date, '%Y') != 0 AND
|
|
DATE_FORMAT(subscription_date, '%Y-%m-%d') >= '$start_date' AND
|
|
DATE_FORMAT(subscription_date, '%Y-%m-%d') <= '$end_date'";
|
|
break;
|
|
case 'fevents':
|
|
$query = "SELECT DATE_FORMAT(e.date_created, '%Y') ddate, COUNT(e.event_id) value,
|
|
(CASE
|
|
-- coming soon
|
|
WHEN e.back_office_status = 1 THEN 'proc'
|
|
-- being created
|
|
WHEN e.back_office_status = 0 THEN 'en'
|
|
-- complete
|
|
WHEN ((e.back_office_status = 2 OR e.back_office_status = 3) AND e.event_status ='FULL'
|
|
AND e.remaining_combined_seat >=0 )
|
|
THEN 'com'
|
|
-- terminee
|
|
WHEN e.back_office_status = 7 THEN 'ter'
|
|
|
|
-- passe
|
|
WHEN e.back_office_status = 4 THEN 'pas'
|
|
-- archived
|
|
WHEN e.back_office_status = 5 THEN 'arc'
|
|
END) bostat
|
|
FROM event e
|
|
WHERE DATE_FORMAT(e.date_created, '%Y') != 0 AND
|
|
DATE_FORMAT(e.date_created, '%Y') = '$cur_year'
|
|
GROUP BY e.back_office_status
|
|
ORDER BY e.back_office_status DESC";
|
|
break;
|
|
|
|
// Recherches Internes
|
|
case 's':
|
|
$query = "SELECT SUM(gev_rdata) gev_rdata, gev_raction, gev_rdatalabel,
|
|
(CASE
|
|
WHEN gev_rdatalabel = 'filter-type' THEN (SELECT event_type FROM event_type WHERE event_type_id = gev_raction)
|
|
WHEN gev_rdatalabel = 'filter-city' THEN (SELECT city FROM event_city_location WHERE city_location_id = gev_raction)
|
|
END) label
|
|
,gev_rdate
|
|
FROM ga_events
|
|
WHERE gev_rcategory = 'fo_filter'
|
|
AND
|
|
DATE_FORMAT(gev_rdate, '%Y-%m-%d') >= '$start_date' AND
|
|
DATE_FORMAT(gev_rdate, '%Y-%m-%d') <= '$end_date'
|
|
|
|
GROUP BY gev_raction, gev_rdatalabel
|
|
ORDER BY SUM(gev_rdata) DESC";
|
|
break;
|
|
case 'faq_search':
|
|
$query = "SELECT SUM(gev_rdata) gev_rdata, gev_raction FROM ga_events
|
|
WHERE gev_rcategory = 'faq_search'
|
|
AND
|
|
DATE_FORMAT(gev_rdate, '%Y-%m-%d') >= '$start_date' AND
|
|
DATE_FORMAT(gev_rdate, '%Y-%m-%d') <= '$end_date' AND
|
|
gev_raction !='undefined'
|
|
GROUP BY gev_raction
|
|
ORDER BY gev_rdata DESC LIMIT 12";
|
|
break;
|
|
case 'source_mediums':
|
|
$query = "SELECT SUM(gsm_rdata) gsm_rdata, gsm_rchannelgroup,
|
|
(SUM(gsm_rdata)/(SELECT SUM(gsm_rdata) FROM ga_source_mediums
|
|
WHERE DATE_FORMAT(gsm_rdate, '%Y') = '$cur_year')*100) percentage
|
|
|
|
FROM ga_source_mediums
|
|
WHERE
|
|
DATE_FORMAT(gsm_rdate, '%Y-%m-%d') >= '$start_date' AND
|
|
DATE_FORMAT(gsm_rdate, '%Y-%m-%d') <= '$end_date' AND
|
|
GROUP BY gsm_rchannelgroup
|
|
ORDER BY SUM(gsm_rdata) DESC";
|
|
break;
|
|
case 'faq_stat':
|
|
$query = "SELECT
|
|
ffs.subject,
|
|
ffs.subject_id,
|
|
ffq.question,
|
|
@helpful := (SELECT count(ffhc.id) FROM `frontoffice_faq_helpful_counter` as `ffhc` where ffhc.question_id = ffq.question_id and `ffhc`.`faq_helpful_status` = 1 ) as helpful,
|
|
@not_helpful := (SELECT count(ffhc.id) FROM `frontoffice_faq_helpful_counter` as `ffhc` where ffhc.question_id = ffq.question_id and `ffhc`.`faq_helpful_status` = 0 ) as not_helpful,
|
|
ROUND(((@helpful / (@helpful + @not_helpful)) * 100)) as vol_helpful,
|
|
ROUND(((@not_helpful / (@helpful + @not_helpful)) * 100)) as vol_not_helpful
|
|
FROM
|
|
`frontoffice_faq_questions` as `ffq`,
|
|
`frontoffice_faq_subject` as `ffs`
|
|
where
|
|
ffs.subject_id = ffq.subject_id
|
|
and
|
|
ffq.status = 1
|
|
order by
|
|
FIELD(ffq.subject_id, 1, 2, 3, 6, 4, 5),
|
|
ffq.question_id ASC";
|
|
break;
|
|
|
|
// case 's':
|
|
// $query = "SELECT SUM(gev_rdata) gev_rdata, gev_raction, gev_rdatalabel,
|
|
// (CASE
|
|
// WHEN gev_rdatalabel = 'filter-type' THEN (SELECT event_type FROM event_type WHERE event_type_id = gev_raction)
|
|
// WHEN gev_rdatalabel = 'filter-city' THEN (SELECT city FROM event_city_location WHERE city_location_id = gev_raction)
|
|
// END) label
|
|
// ,gev_rdate
|
|
// FROM ga_events
|
|
// WHERE gev_rcategory = 'fo_filter'
|
|
// AND
|
|
// DATE_FORMAT(gev_rdate, '%Y') = '$cur_year'
|
|
// GROUP BY gev_raction, gev_rdatalabel
|
|
// ORDER BY SUM(gev_rdata) DESC";
|
|
// break;
|
|
// case 'faq_search':
|
|
// $query = "SELECT SUM(gev_rdata) gev_rdata, gev_raction FROM ga_events
|
|
// WHERE gev_rcategory = 'faq_search'
|
|
// AND
|
|
// DATE_FORMAT(gev_rdate, '%Y') = '$cur_year' AND
|
|
// gev_raction !='undefined'
|
|
// GROUP BY gev_raction
|
|
// ORDER BY gev_rdata DESC LIMIT 12";
|
|
// break;
|
|
// case 'source_mediums':
|
|
// $query = "SELECT SUM(gsm_rdata) gsm_rdata, gsm_rchannelgroup,
|
|
// (SUM(gsm_rdata)/(SELECT SUM(gsm_rdata) FROM ga_source_mediums
|
|
// WHERE DATE_FORMAT(gsm_rdate, '%Y') = '$cur_year')*100) percentage
|
|
//
|
|
// FROM ga_source_mediums
|
|
// WHERE DATE_FORMAT(gsm_rdate, '%Y') = '$cur_year'
|
|
// GROUP BY gsm_rchannelgroup
|
|
// ORDER BY SUM(gsm_rdata) DESC";
|
|
// break;
|
|
default:
|
|
// code...
|
|
break;
|
|
}
|
|
|
|
$result = $result = $this->db->query($query);
|
|
return $result->result_array();
|
|
}
|
|
|
|
// GA data stored locally
|
|
public function get_ga_data($category = '', $col_abbrev, $user_type, $start, $end, $order = 'ASC'){
|
|
// check start date
|
|
$start = $this->check_ga_date($start);
|
|
// check end date
|
|
$end = $this->check_ga_date($end);
|
|
|
|
$ga_data = array();
|
|
$cur_date = date("Y-m-d");
|
|
$cur_year = date("Y");
|
|
$prev_year = date("Y",strtotime("-1 year"));
|
|
|
|
$time_diff = abs(strtotime($end) - strtotime($start));
|
|
$days_count = $time_diff/86400; // 86400 seconds in 1 day
|
|
|
|
$user_types = explode('_', $user_type);
|
|
|
|
// for ($i=1; $i < 4 ; $i++) {
|
|
// }
|
|
// $this->db->or_where("user_type", 2);
|
|
// $this->db->or_where("user_type", 3);
|
|
if ($user_type != 'none') {
|
|
switch ($category) {
|
|
case 'visitors':
|
|
$table_name = 'ga_visitors';
|
|
$table_name_alias = 'gv';
|
|
break;
|
|
case 'sessions':
|
|
$table_name = 'ga_sessions';
|
|
$table_name_alias = 'sess';
|
|
$rtype = 0;
|
|
break;
|
|
|
|
case 'session_duration':
|
|
$table_name = 'ga_sessions';
|
|
$table_name_alias = 'sess';
|
|
$rtype = 1;
|
|
break;
|
|
|
|
case 'pageviews':
|
|
$table_name = 'ga_pageviews';
|
|
$table_name_alias = 'pv';
|
|
$rtype = 0;
|
|
break;
|
|
|
|
case 'unique_pageviews':
|
|
$table_name = 'ga_pageviews';
|
|
$table_name_alias = 'pv';
|
|
$rtype = 1;
|
|
break;
|
|
|
|
case 'event_views':
|
|
$table_name = 'ga_events_view';
|
|
$table_name_alias = 'ev';
|
|
$rtype = 0;
|
|
break;
|
|
|
|
case 'bounceRate':
|
|
$table_name = 'ga_bounce_rate';
|
|
$table_name_alias = 'bcr';
|
|
break;
|
|
|
|
default:
|
|
// code...
|
|
break;
|
|
}
|
|
$_select_part = '';
|
|
|
|
switch ($category) {
|
|
case 'session_duration':
|
|
$_select_part = "ROUND((({$col_abbrev}_rdata))/(SELECT (sess_rdata) FROM ga_sessions WHERE sess_rdate = sess.sess_rdate AND sess_rtype = 0 AND user_type = sess.user_type)) {$col_abbrev}_rdata,
|
|
DATE_FORMAT({$col_abbrev}_rdate, '%d/%m/%Y') {$col_abbrev}_rdate";
|
|
break;
|
|
case 'bounceRate':
|
|
$this->db->join("ga_sessions gas", "$table_name_alias.user_type = gas.user_type AND $table_name_alias.bcr_rdate = gas.sess_rdate");
|
|
$_select_part = "({$col_abbrev}_rdata) {$col_abbrev}_rdata, DATE_FORMAT({$col_abbrev}_rdate, '%d/%m/%Y') {$col_abbrev}_rdate, gas.sess_rdata, ROUND(gas.sess_rdata * ($table_name_alias.bcr_rdata/100)) sess_br";
|
|
break;
|
|
|
|
default:
|
|
$_select_part = "SUM({$col_abbrev}_rdata) {$col_abbrev}_rdata, DATE_FORMAT({$col_abbrev}_rdate, '%d/%m/%Y') {$col_abbrev}_rdate";
|
|
break;
|
|
}
|
|
|
|
$this->db->select(
|
|
"$_select_part,
|
|
(CASE
|
|
WHEN $table_name_alias.user_type = 0 THEN 'All'
|
|
WHEN $table_name_alias.user_type = 1 THEN 'Nouveaux'
|
|
WHEN $table_name_alias.user_type = 2 THEN 'Connus'
|
|
WHEN $table_name_alias.user_type = 3 THEN 'Connectés'
|
|
END )AS user_type"
|
|
);
|
|
$this->db->from("$table_name $table_name_alias");
|
|
if (isset($rtype)) {
|
|
$this->db->where("$table_name_alias.{$col_abbrev}_rtype", $rtype);
|
|
// $this->db->group_by("DATE_FORMAT($table_name_alias.{$col_abbrev}_rdate, '%Y-%m-%d'), $table_name_alias.user_type");
|
|
$this->db->group_by(array(
|
|
"DATE_FORMAT({$col_abbrev}_rdate, '%Y-%m-%d')",
|
|
"$table_name_alias.user_type",
|
|
"$table_name_alias.{$col_abbrev}_rtype"
|
|
)
|
|
);
|
|
} else {
|
|
$this->db->group_by(array("DATE_FORMAT($table_name_alias.{$col_abbrev}_rdate, '%Y-%m-%d')", "$table_name_alias.user_type"));
|
|
}
|
|
$this->db->where("DATE_FORMAT($table_name_alias.{$col_abbrev}_rdate, '%Y-%m-%d') >=", $start);
|
|
$this->db->where("DATE_FORMAT($table_name_alias.{$col_abbrev}_rdate, '%Y-%m-%d') <=", $end);
|
|
|
|
$this->db->where_in("$table_name_alias.user_type", $user_types);
|
|
$this->db->order_by("$table_name_alias.{$col_abbrev}_rdate", $order);
|
|
$this->db->order_by("$table_name_alias.user_type", 'ASC');
|
|
} else {
|
|
|
|
switch ($category) {
|
|
case 'users_browsers':
|
|
$table_name = 'ga_visitors';
|
|
$table_name_alias = 'gv';
|
|
$col_prefix = 'vis';
|
|
$abbrev = 'br';
|
|
break;
|
|
case 'users_and_device_category':
|
|
$table_name = 'ga_visitors';
|
|
$table_name_alias = 'gv';
|
|
$col_prefix = 'vis';
|
|
$abbrev = 'dv';
|
|
break;
|
|
case 'source_mediums_graph':
|
|
$table_name = 'ga_source_mediums';
|
|
$table_name_alias = 'gsm';
|
|
$col_prefix = 'gsm';
|
|
$abbrev = 'gsm';
|
|
break;
|
|
default:
|
|
// code...
|
|
break;
|
|
}
|
|
if ($category == 'source_mediums_graph') {
|
|
// get total of all row data
|
|
$d = array(
|
|
$cur_year
|
|
);
|
|
$q = "SELECT SUM(gsm_rdata) total_data FROM (SELECT SUM(gsm_rdata) gsm_rdata FROM ga_source_mediums $table_name_alias
|
|
WHERE DATE_FORMAT($table_name_alias.gsm_rdate, '%Y') = ?
|
|
GROUP BY $table_name_alias.gsm_rchannelgroup
|
|
ORDER BY SUM($table_name_alias.gsm_rdata) DESC) total_data";
|
|
|
|
$total_data = (($this->db->query($q, $d))->result_array())[0]['total_data'];
|
|
if (!is_numeric($total_data)) {
|
|
return false;
|
|
}
|
|
$this->db->select(
|
|
"SUM(gsm_rdata) vis_rdata, gsm_rchannelgroup rtype, ".
|
|
"(SUM(gsm_rdata)/$total_data*100) percentage "
|
|
);
|
|
$this->db->from("$table_name $table_name_alias");
|
|
// $this->db->where("DATE_FORMAT(gsm_rdate, '%Y')=", "$cur_year");
|
|
$this->db->where("DATE_FORMAT(gsm_rdate, '%Y-%m-%d') >=", $start);
|
|
$this->db->where("DATE_FORMAT(gsm_rdate, '%Y-%m-%d') <=", $end);
|
|
|
|
$this->db->group_by(array("$table_name_alias.gsm_rchannelgroup"));
|
|
$this->db->order_by("SUM(gsm_rdata)", 'DESC');
|
|
} else {
|
|
// get total of all row data
|
|
$d = array(
|
|
$start,
|
|
$end,
|
|
);
|
|
$q = "SELECT SUM(vis_rdata) total_vis FROM (SELECT SUM(vis_rdata) vis_rdata FROM ga_visitors gv
|
|
WHERE {$abbrev}_rtype!= 'UNDEFINED' AND
|
|
DATE_FORMAT(gv.vis_rdate, '%Y-%m-%d') >= ? AND
|
|
DATE_FORMAT(gv.vis_rdate, '%Y-%m-%d') <= ?
|
|
GROUP BY {$abbrev}_rtype
|
|
ORDER BY SUM(gv.vis_rdata) DESC
|
|
LIMIT 12) total_vis";
|
|
|
|
$total_vis = (($this->db->query($q, $d))->result_array())[0]['total_vis'];
|
|
if (!is_numeric($total_vis)) {
|
|
return false;
|
|
}
|
|
$this->db->select(
|
|
"SUM(vis_rdata) vis_rdata, {$abbrev}_rtype rtype, ".
|
|
"(SUM(vis_rdata)/$total_vis*100) percentage "
|
|
);
|
|
$this->db->from("$table_name $table_name_alias");
|
|
$this->db->where("$table_name_alias.{$abbrev}_rtype !=", "UNDEFINED");
|
|
$this->db->where("DATE_FORMAT($table_name_alias.vis_rdate, '%Y-%m-%d') >=", $start);
|
|
$this->db->where("DATE_FORMAT($table_name_alias.vis_rdate, '%Y-%m-%d') <=", $end);
|
|
|
|
$this->db->group_by(array("$table_name_alias.{$abbrev}_rtype"));
|
|
$this->db->order_by("SUM(vis_rdata)", 'DESC');
|
|
}
|
|
// $this->db->order_by("$table_name_alias.{$abbrev}_rtype", 'ASC');
|
|
$this->db->limit(12);
|
|
}
|
|
|
|
$result = $this->db->get();
|
|
if($result->num_rows() > 0){
|
|
return $result->result_array();
|
|
// $data = $result->result_array();
|
|
// foreach ($data as $key => $value) {
|
|
// // array_push(&$stack, $vars...);
|
|
// if (!isset($ga_data[$value['user_type']])) {
|
|
// $ga_data[$value['user_type']] = array();
|
|
// // $ga_data[$value['user_type']]['t'] = '';
|
|
// // $ga_data[$value['user_type']]['y'] = '';
|
|
// // array_push($ga_data[$value['user_type']], '{"t":"'.($value[$abbrev.'_rdate']).'", "y":"'.($value[$abbrev.'_rdata']).'"}');
|
|
// array_push($ga_data[$value['user_type']], array(
|
|
// "t" => $value[$abbrev.'_rdate'],
|
|
// "y" => $value[$abbrev.'_rdata']
|
|
// ));
|
|
// } else {
|
|
// array_push($ga_data[$value['user_type']], array(
|
|
// "t" => $value[$abbrev.'_rdate'],
|
|
// "y" => $value[$abbrev.'_rdata']
|
|
// ));
|
|
// // array_push($ga_data[$value['user_type']], '{"t":"'.($value[$abbrev.'_rdate']).'", "y":"'.($value[$abbrev.'_rdata']).'"}');
|
|
// }
|
|
// }
|
|
// return $ga_data;
|
|
}
|
|
return false;
|
|
}
|
|
}
|
|
|
|
?>
|
|
|