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/models/Event_model.php

3473 lines
167 KiB
Raw Permalink Blame History

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
use app\core\auth\User as UserAuth;
class Event_model extends CI_Model {
var $column_order = array(null, 'e.title','e.event_category','et.event_type', 'ecl.city', 'e.date_created', 'CONCAT(u.first_name, " ", u.last_name)', null); //set column field database for datatable orderable
var $column_search = array('CONCAT(u.first_name, " ", u.last_name)','e.title','e.date_created', 'es.event_status', 'et.event_type', 'es.event_status', 'ecl.city', 'e.event_category'); //set column field database for datatable searchable just firstname , lastname , address are searchable
// var $order = array('e.event_id' => 'desc'); // default order
public function __construct() {
parent::__construct();
$this->db->query("SET @@group_concat_max_len =30000");
}
private function _get_export_query(){
return "SELECT
es.start_date_time as start_date,
es.end_date_time as end_date,
e.location as event_venue,
e.rate as price_rate,
-- es.remaining_seat as num_remaining_places,
-- es.total_available_seat as total_places_avl,
(CASE
WHEN e.seat_feature = 2
THEN 'COMBINE'
ELSE \"\"
END) as name_header_is_combine,
(CASE
WHEN e.seat_feature = 2
THEN e.remaining_combined_seat
ELSE es.remaining_seat
END) as num_remaining_places,
(CASE
WHEN e.seat_feature = 2
THEN e.total_combined_seat
ELSE es.total_available_seat
END) as total_places_avl,
es.reservation_start_date as reservation_date,
(CASE
WHEN es.event_status = 'AVAILABLE' THEN 'Disponible'
WHEN es.event_status = 'FULL' THEN 'Complet'
ELSE 'Annulé'
END) AS event_statuses,
-- ecl.city as place_town,
(CASE
WHEN e.use_different_address_per_date = 1 THEN
(CASE
WHEN es.city_location IS NOT NULL THEN (SELECT Ecl.city FROM event_city_location Ecl WHERE Ecl.city_location_id = es.city_location AND Ecl.status =1)
WHEN es.address IS NOT NULL THEN es.address
WHEN es.city_name IS NOT NULL THEN es.city_name
ELSE \"\"
END)
ELSE
(CASE
WHEN e.city_location IS NOT NULL THEN (SELECT Ecl.city FROM event_city_location Ecl WHERE Ecl.city_location_id = e.city_location AND Ecl.status =1)
WHEN e.address IS NOT NULL THEN e.address
WHEN e.city_name IS NOT NULL THEN e.city_name
ELSE \"\"
END)
END)as place_town,
CONCAT(IFNULL(DATE_FORMAT(es.start_date_time, '%e/%m/%Y') ,'00/00/0000'),' - ', IFNULL(DATE_FORMAT(es.end_date_time, '%e/%m/%Y') ,'00/00/0000')) AS date_month,
CONCAT(IFNULL(DATE_FORMAT(es.start_date_time, '%Hh%i') ,'00h00'),' - ', IFNULL(DATE_FORMAT(es.end_date_time, '%Hh%i') ,'00h00')) AS hour ";
}
private function get_university_id_by_domain($domain) {
$query = $this->db->select('id')
->from('user_cpay')
->where('domain', $domain)
->get();
if ($query->num_rows() > 0) {
return $query->row()->id;
}
return null;
}
private function _get_event_list($where){
return "SELECT
".$this->add_to_custom_filter($where)." AS bostatus,
(SELECT
GROUP_CONCAT(CONCAT('{\"event_schedule_id\":\"',a.event_schedule_id,
'\", \"reservation_start_date\":\"',
( CASE
WHEN a.reservation_start_date IS NULL THEN \"\"
ELSE DATE_FORMAT(a.reservation_start_date, '%e/%m/%Y %Hh%i')
END),
'\", \"start_date_time\":\"',
(CASE
WHEN a.start_date_time IS NULL THEN \"\"
ELSE DATE_FORMAT(a.start_date_time, '%e/%m/%Y %Hh%i')
END),
'\", \"end_date_time\":\"',
(CASE
WHEN a.end_date_time IS NULL THEN \"\"
ELSE DATE_FORMAT(a.end_date_time, '%e/%m/%Y %Hh%i')
END),
'\", \"total_available_seat\":\"',
(CASE
WHEN a.total_available_seat <= 0 THEN \"\"
ELSE a.total_available_seat
END),
'\", \"remaining_seat\":\"',
(CASE
WHEN a.remaining_seat <=0 THEN \"\"
ELSE a.remaining_seat
END),
'\", \"event_status\":\"',a.event_status,
'\", \"event_url\":\"',(CASE
WHEN a.event_url IS NULL THEN \"\"
ELSE a.event_url
END),
'\", \"item_code\":\"',(CASE
WHEN a.item_code IS NULL THEN \"\"
ELSE a.item_code
END),
'\", \"seat_feature\":\"',e.seat_feature,
'\", \"date_feature\":\"',e.date_feature,
'\", \"back_office_status\":\"',a.back_office_status,
'\", \"avl_seats\":\"',(CASE
WHEN e.seat_feature = 2 THEN e.total_combined_seat
ELSE a.total_available_seat
END),
'\", \"rem_seats\":\"',(CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE a.remaining_seat
END),
'\", \"reservation_end_date\":\"',
(CASE
WHEN a.reservation_end_date IS NULL THEN \"\"
ELSE DATE_FORMAT(a.reservation_end_date, '%e/%m/%Y %Hh%i')
END),
'\", \"city_location\":\"',
(CASE
WHEN e.use_different_address_per_date = 1 THEN
(CASE
WHEN a.city_location IS NULL THEN \"\"
ELSE a.city_location
END)
ELSE \"\"
END),
'\", \"location\":\"',
(CASE
WHEN e.use_different_address_per_date = 1 THEN
(CASE
WHEN a.location IS NULL THEN \"\"
ELSE a.location
END)
ELSE \"\"
END),
'\", \"address\":\"',
(CASE
WHEN e.use_different_address_per_date = 1 THEN
(CASE
WHEN a.address IS NULL THEN \"\"
ELSE a.address
END)
ELSE \"\"
END),
'\", \"code_postal\":\"',
(CASE
WHEN e.use_different_address_per_date = 1 THEN
(CASE
WHEN a.code_postal IS NULL THEN \"\"
ELSE a.code_postal
END)
ELSE \"\"
END),
'\", \"city_name\":\"',
(CASE
WHEN e.use_different_address_per_date = 1 THEN
(CASE
WHEN a.city_name IS NULL THEN \"\"
ELSE a.city_name
END)
ELSE \"\"
END),
'\", \"seats_per_subscriber\":\"',
(CASE
WHEN a.seats_per_subscriber <=0 THEN \"\"
ELSE a.seats_per_subscriber
END),
'\", \"rem_wl_seats\":\"',
(CASE
WHEN a.quota_waiting_list_seat <=0 THEN \"\"
ELSE a.quota_waiting_list_seat
END),
'\", \"avl_wl_seats\":\"',
(CASE
WHEN a.total_quota_waiting_list_seat <=0 THEN \"\"
ELSE a.total_quota_waiting_list_seat
END),
'\", \"total_quota_waiting_list_seat\":\"',
(CASE
WHEN a.total_quota_waiting_list_seat <=0 THEN \"\"
ELSE a.total_quota_waiting_list_seat
END),'\"}'
) ORDER BY
(CASE
WHEN (a.back_office_status = 2 AND a.event_status ='AVAILABLE' AND
(CASE
WHEN e.seat_feature = 2 THEN
(CASE
WHEN e.remaining_combined_seat >0 && e.remaining_combined_seat IS NOT NULL THEN e.remaining_combined_seat
ELSE 0
END)
ELSE (CASE
WHEN a.remaining_seat >0 && a.remaining_seat IS NOT NULL THEN a.remaining_seat
ELSE 0
END)
END) >0)
THEN CONCAT('aa_',a.start_date_time,'_',a.event_schedule_id,'_')
WHEN ((a.back_office_status = 2 OR a.back_office_status = 3) AND (a.event_status ='FULL' OR a.event_status ='AVAILABLE')
AND (CASE
WHEN e.seat_feature = 2 THEN
(CASE
WHEN e.remaining_combined_seat >0 && e.remaining_combined_seat IS NOT NULL THEN e.remaining_combined_seat
ELSE 0
END)
ELSE (CASE
WHEN a.remaining_seat >0 && a.remaining_seat IS NOT NULL THEN a.remaining_seat
ELSE 0
END)
END)=0 AND a.quota_waiting_list_seat > 0)
THEN CONCAT('ab_',a.start_date_time,'_',a.event_schedule_id,'_')
WHEN a.back_office_status = 1 THEN CONCAT('ac_',a.start_date_time,'_',a.event_schedule_id,'_')
WHEN (a.back_office_status = 3
AND (CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE a.remaining_seat
END)=0 AND a.quota_waiting_list_seat > 0)
THEN CONCAT('ad_',a.start_date_time,'_',a.event_schedule_id,'_')
WHEN ((a.back_office_status = 2 OR a.back_office_status = 3) AND a.event_status ='FULL'
AND (CASE
WHEN e.seat_feature = 2 THEN
(CASE
WHEN e.remaining_combined_seat >0 && e.remaining_combined_seat IS NOT NULL THEN e.remaining_combined_seat
ELSE 0
END)
ELSE (CASE
WHEN a.remaining_seat >0 && a.remaining_seat IS NOT NULL THEN a.remaining_seat
ELSE 0
END)
END)=0 AND a.quota_waiting_list_seat = 0) THEN CONCAT('ae_',a.start_date_time,'_',a.event_schedule_id,'_')
WHEN a.back_office_status = 7 THEN CONCAT('b_',a.start_date_time,'_',a.event_schedule_id,'_')
WHEN a.back_office_status = 4 THEN CONCAT('c_',a.start_date_time,'_',a.event_schedule_id,'_')
ELSE CONCAT('d_',a.start_date_time,'_',a.event_schedule_id,'_')
END) ASC,
a.start_date_time ASC
SEPARATOR ',') as child_events
FROM event_schedule a
WHERE a.event_id = e.event_id
".$where."
AND a.back_office_status NOT IN (6)
) AS event_schedule,
ecl.city as city";
}
private function _get_datatables_query($data_source, $export=0, $event_schedule_id=0){
$_query = ""; $where = ""; $_search = ""; $_ordey_by = ""; $data = array(); $select = ""; $_group_by="";
if ($export) {
$_query = $this->_get_export_query();
} else {
// $domain = $_SERVER['HTTP_HOST'];
// $university_id = (int) $this->get_university_id_by_domain($domain);
$where = $this->filter_by_event_schedule($data_source, "city_location_id");
$where .= $this->filter_by_event_schedule($data_source, "back_office_status");
$where .= $this->filter_by_event_schedule($data_source, "event_status");
$where .= $this->filter_by_event_schedule($data_source, "start_date_time");
$_query = $this->_get_event_list($where);
}
$_query .=",
es.event_status,
e.event_category,
e.event_id,
e.use_different_address_per_date,
e.title as event_title,
et.event_type,
e.description,
e.workshop_session,
DATE_FORMAT(e.date_created, '%e/%m/%Y %Hh%i') AS date_created,
CONCAT(u.first_name, ' ', u.last_name) as author
FROM event e
LEFT JOIN event_schedule es ON es.event_id = e.event_id
LEFT JOIN event_type et ON et.event_type_id = e.event_type_id
LEFT JOIN event_city_location ecl ON ecl.city_location_id = e.city_location
LEFT JOIN user u ON u.user_id = e.author
WHERE e.status IN (1) ";
if($event_schedule_id > 0) {
$_query .= " AND es.back_office_status NOT IN(6) AND es.event_schedule_id = ".$this->db->escape($event_schedule_id);
}
if(isset($data_source["custom_filter_search"]) && !empty($data_source["custom_filter_search"]) && $data_source["custom_filter_search"] != "all"){
if($data_source["custom_filter_search"] == "start_date_time"){
$splitte = explode("/", $data_source['search']['value']);
array_push($data, "%".$splitte[1]."-".$splitte[0]."%");
// echo $splitte[1]."-".$splitte[0];
$_search .= " AND ".$this->add_to_custom_filter($where)." IS NOT NULL AND ( es.start_date_time LIKE ? ";
} else if($data_source["custom_filter_search"] == "back_office_status"){
array_push($data, "%".$data_source['search']['value']."%");
$_search .= " AND ( e.title LIKE ? ";
if(!empty($data_source["filter_by_back_office_status"]) && isset($data_source["filter_by_back_office_status"])) {
array_push($data, $data_source[$data_source["keyname"]]);
$_search .= ") AND ( es." . $data_source["custom_filter_search"]." = ? ";
}
} else if($data_source["custom_filter_search"] == "event_status"){
array_push($data, "%".$data_source['search']['value']."%");
$_search .= " AND ( e.title LIKE ? ";
if(!empty($data_source["filter_by_event_status"]) && isset($data_source["filter_by_event_status"])) {
array_push($data, $data_source[$data_source["keyname"]]);
$_search .= ") AND ( es." . $data_source["custom_filter_search"]." = ? ";
}
}else if($data_source["custom_filter_search"] == "city_location"){
array_push($data, "%".$data_source['search']['value']."%");
$_search .= " AND ( e.title LIKE ? ";
if(!empty($data_source["filter_by_city_location_id"]) && isset($data_source["filter_by_city_location_id"])) {
array_push($data, $data_source[$data_source["keyname"]]);
array_push($data, $data_source[$data_source["keyname"]]);
$_search .= ") AND (CASE WHEN e.use_different_address_per_date =0
THEN (e.city_location = ? OR e.city_location IS NULL OR e.city_location = 0)
ELSE (es.city_location = ? OR es.city_location IS NULL OR es.city_location =0)
END";
}
} else {
if (isset($data_source["keyname"]) && !empty($data_source["keyname"])) {
if($data_source[$data_source["keyname"]] != "") {
$_query .= " AND e.".$this->db->escape_str($data_source["custom_filter_search"])." = '".$this->db->escape_str($data_source[$data_source["keyname"]])."'";
}
array_push($data, "%".$data_source['search']['value']."%");
$_search .= " AND ( e.title LIKE ? ";
} else {
array_push($data, "%".$data_source['search']['value']."%");
$_search .= " AND ( e." . $this->db->escape_str($data_source["custom_filter_search"])." LIKE ? ";
}
}
$_search .= ")";
} else {
if($data_source['search']['value']) {
$i = 0;
foreach ($this->column_search as $item) {// loop column
if($i===0){ // first loop
array_push($data, "%".$data_source['search']['value']."%");
$_search .= " AND ( ".$item." LIKE ? ";
} else {
array_push($data, "%".$data_source['search']['value']."%");
$_search .= " OR ".$item." LIKE ? ";
}
$i++;
}
$_search .= ")";
}
}
if($export<=0) {
// group by event_id
$_group_by .=" GROUP BY e.event_id ";
// $_ordey_by .= " ORDER BY bostatus ASC ";
}
if(isset($data_source['order']) && !empty($data_source['order'])) { // here order processing
if(isset($data_source['order']['0']['dir']) && !empty($data_source['order']['0']['dir'])) {
if(isset($this->column_order[$data_source['order']['0']['column']]))
$_ordey_by .= " ORDER BY ".$this->column_order[$data_source['order']['0']['column']]." ".$data_source['order']['0']['dir'];
} else {
$_ordey_by .= " ORDER BY ".key($this->order)." ".$this->order[key($this->order)];
}
} else if(isset($this->order) && !empty($this->order)) {
$_ordey_by .= " ORDER BY ".key($this->order)." ".$this->order[key($this->order)];
}
if($export<=0) {
$_ordey_by .= ($_ordey_by!="")?", ":" ORDER BY ";
$_ordey_by .= " bostatus ASC ";
}
//echo $_query;
return array("query"=>$_query.$_search.$_group_by.$_ordey_by, "data" =>$data);
}
public function get_events_list_export($data_source, $export, $event_schedule_id){
$_query = $this->_get_datatables_query($data_source, $export, $event_schedule_id);
return $this->db->query($_query["query"], $_query["data"])->result();
}
public function get_datatables($data_source, $export, $event_schedule_id){
$_query = $this->_get_datatables_query($data_source, $export, $event_schedule_id);
//echo $_query["query"];
if($data_source['length'] != -1){
$_query["query"] .= " LIMIT ".$this->db->escape_str($data_source['start']).", ".$this->db->escape_str($data_source['length']);
}
return $this->db->query($_query["query"], $_query["data"])->result();
}
public function count_filtered($data_source, $export, $event_schedule_id){
$_query = $this->_get_datatables_query($data_source, $export, $event_schedule_id);
return $this->db->query($_query["query"], $_query["data"])->num_rows();
}
public function count_all($data_source, $export, $event_schedule_id){
$_query = $this->_get_datatables_query($data_source, $export, $event_schedule_id);
return $this->db->query($_query["query"], $_query["data"])->num_rows();
}
private function filter_by_event_schedule($data_source, $filter){
if(isset($data_source["custom_filter_search"]) && !empty($data_source["custom_filter_search"]) && $data_source["custom_filter_search"] != "all"){
switch($filter){
case "back_office_status" :
if(!empty($data_source["filter_by_".$filter]) && isset($data_source["filter_by_".$filter])) {
return "AND a.back_office_status = ".$this->db->escape($data_source[$data_source["keyname"]])." ";
}
break;
case "event_status" :
if(!empty($data_source["filter_by_".$filter]) && isset($data_source["filter_by_".$filter])) {
return "AND a.event_status = '".$this->db->escape_str($data_source[$data_source["keyname"]])."' ";
}
break;
case "city_location_id" :
if(!empty($data_source["filter_by_".$filter]) && isset($data_source["filter_by_".$filter])) {
return " AND (a.city_location = '".$this->db->escape_str($data_source[$data_source["keyname"]])."'
OR a.city_location IS NULL OR a.city_location =0) ";
}
break;
case "start_date_time" :
if(!empty($data_source["custom_filter_search"]) && isset($data_source["custom_filter_search"]) && $data_source["custom_filter_search"] == "start_date_time"){
$splitte = explode("/", $data_source['search']['value']);
return " AND a.start_date_time LIKE '%".$this->db->escape_like_str($splitte[1]."-".$splitte[0])."%' ESCAPE '!' ";
}
break;
default:
return " ";
break;
} //end of switch
return " ";
}
}
private function add_to_custom_filter($where){
return "(SELECT
GROUP_CONCAT(CONCAT(
CASE
-- Coming soon --
WHEN (a.back_office_status = 0)
THEN CONCAT('aa_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Normal Reservation --
WHEN (
a.back_office_status = 2
AND a.event_status ='AVAILABLE'
AND (
(
e.event_category = 'ONLINE_EVENT'
AND
a.start_date_time < NOW()
)
OR
(
(CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE a.remaining_seat
END) >0
)
)
)
THEN CONCAT('ab_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Waiting List --
WHEN (
a.back_office_status = 2
AND (a.event_status ='FULL' OR a.event_status ='AVAILABLE' )
AND e.event_category <> 'ONLINE_EVENT'
AND (CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE a.remaining_seat
END)>=0
AND a.quota_waiting_list_seat > 0
)
THEN CONCAT('ac_',a.start_date_time,'_',a.event_schedule_id,'_')
-- cancelled but not yet passe single dates
WHEN (a.event_status = 'CANCEL' && a.end_date_time IS NOT NULL) &&
((SELECT date_feature FROM event WHERE event_id = a.event_id) = 1) &&
a.back_office_status NOT IN (1)
THEN
CASE
WHEN a.end_date_time > NOW()
THEN CONCAT('ad1_',a.start_date_time,'_',a.event_schedule_id,'_')
ELSE CONCAT('e_',a.start_date_time,'_',a.event_schedule_id,'_')
END
WHEN (a.event_status = 'CANCEL' && a.start_date_time IS NOT NULL) &&
((SELECT date_feature FROM event WHERE event_id = a.event_id) = 1) &&
a.back_office_status NOT IN (1)
THEN
CASE
WHEN DATE_FORMAT( DATE_ADD(a.start_date_time, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') > NOW()
THEN CONCAT('ad1_',DATE_FORMAT( DATE_ADD(a.start_date_time, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00'),'_',a.event_schedule_id,'_')
ELSE CONCAT('e_',a.start_date_time,'_',a.event_schedule_id,'_')
END
-- cancelled but not yet passe multi dates
WHEN (a.event_status = 'CANCEL' && a.end_date_time IS NOT NULL) &&
((SELECT date_feature FROM event WHERE event_id = a.event_id) = 2) &&
a.back_office_status NOT IN (1)
THEN
CASE
WHEN a.end_date_time > NOW()
THEN CONCAT('ad2_',a.start_date_time,'_',a.event_schedule_id,'_')
ELSE CONCAT('e_',a.start_date_time,'_',a.event_schedule_id,'_')
END
WHEN (a.event_status = 'CANCEL' && a.start_date_time IS NOT NULL) &&
((SELECT date_feature FROM event WHERE event_id = a.event_id) = 2) &&
a.back_office_status NOT IN (1)
THEN
CASE
WHEN DATE_FORMAT( DATE_ADD(a.start_date_time, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') > NOW()
THEN CONCAT('ad2_',DATE_FORMAT( DATE_ADD(a.start_date_time, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00'),'_',a.event_schedule_id,'_')
ELSE CONCAT('e_',a.start_date_time,'_',a.event_schedule_id,'_')
END
-- Coming soon (Regular Event)--
WHEN a.back_office_status = 1 AND e.event_category <> 'ONLINE_EVENT'
THEN CONCAT('ag_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Coming soon (Online Event) --
WHEN (a.back_office_status = 1 OR a.back_office_status = 2)
AND a.start_date_time >= NOW()
AND e.event_category = 'ONLINE_EVENT'
THEN CONCAT('ag_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Coming soon --
WHEN a.back_office_status = 1
THEN CONCAT('ag_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Complete (Regular Event) --
WHEN (
(a.back_office_status = 2 OR a.back_office_status = 3)
AND a.event_status ='FULL'
AND e.event_category <> 'ONLINE_EVENT'
AND (CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE a.remaining_seat
END)>=0
AND a.quota_waiting_list_seat = 0
)
THEN CONCAT('af_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Complete (Online Event)--
WHEN (
(a.back_office_status = 2 OR a.back_office_status = 3)
AND a.event_status ='FULL'
AND e.event_category = 'ONLINE_EVENT'
)
THEN CONCAT('af_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Locked --
WHEN a.back_office_status = 3 THEN CONCAT('ae_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Terminee --
WHEN a.back_office_status = 7 THEN CONCAT('b_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Passe --
WHEN a.back_office_status = 4 THEN CONCAT('c_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Archived --
ELSE CONCAT('e_',a.start_date_time,'_',a.event_schedule_id,'_')
END
)
ORDER BY
(CASE
-- Normal Reservation --
WHEN (
a.back_office_status = 2
AND a.event_status ='AVAILABLE'
AND
(
(
e.event_category = 'ONLINE_EVENT'
AND
a.start_date_time < NOW()
)
OR
(
(CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE a.remaining_seat
END) > 0
)
)
)
THEN CONCAT('aa_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Waiting List --
WHEN (
a.back_office_status = 2
AND (a.event_status ='FULL' OR a.event_status ='AVAILABLE' )
AND e.event_category <> 'ONLINE_EVENT'
AND (CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE a.remaining_seat
END)>=0
AND a.quota_waiting_list_seat > 0
)
THEN CONCAT('ab_',a.start_date_time,'_',a.event_schedule_id,'_')
-- cancelled but not yet passe single dates
WHEN a.event_status = 'CANCEL' && a.end_date_time IS NOT NULL &&
((SELECT date_feature FROM event WHERE event_id = a.event_id) = 1) &&
a.back_office_status NOT IN (1)
THEN
CASE
WHEN a.end_date_time > NOW()
THEN CONCAT('aba1_',a.start_date_time,'_',a.event_schedule_id,'_')
ELSE CONCAT('e_',a.start_date_time,'_',a.event_schedule_id,'_')
END
WHEN a.event_status = 'CANCEL' && a.start_date_time IS NOT NULL &&
((SELECT date_feature FROM event WHERE event_id = a.event_id) = 1) &&
a.back_office_status NOT IN (1)
THEN
CASE
WHEN DATE_FORMAT( DATE_ADD(a.start_date_time, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') > NOW()
THEN CONCAT('aba1_',DATE_FORMAT( DATE_ADD(a.start_date_time, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00'),'_',a.event_schedule_id,'_')
ELSE CONCAT('e_',a.start_date_time,'_',a.event_schedule_id,'_')
END
-- cancelled but not yet passe multi dates
WHEN a.event_status = 'CANCEL' && a.end_date_time IS NOT NULL &&
((SELECT date_feature FROM event WHERE event_id = a.event_id) = 2) &&
a.back_office_status NOT IN (1)
THEN
CASE
WHEN a.end_date_time > NOW()
THEN CONCAT('aba2_',a.start_date_time,'_',a.event_schedule_id,'_')
ELSE CONCAT('e_',a.start_date_time,'_',a.event_schedule_id,'_')
END
WHEN a.event_status = 'CANCEL' && a.start_date_time IS NOT NULL &&
((SELECT date_feature FROM event WHERE event_id = a.event_id) = 2) &&
a.back_office_status NOT IN (1)
THEN
CASE
WHEN DATE_FORMAT( DATE_ADD(a.start_date_time, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') > NOW()
THEN CONCAT('aba2_',DATE_FORMAT( DATE_ADD(a.start_date_time, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00'),'_',a.event_schedule_id,'_')
ELSE CONCAT('e_',a.start_date_time,'_',a.event_schedule_id,'_')
END
-- Coming soon (Regular Event)--
WHEN a.back_office_status = 1 AND e.event_category <> 'ONLINE_EVENT'
THEN CONCAT('ae_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Coming soon (Online Event) --
WHEN (a.back_office_status = 1 OR a.back_office_status = 2)
AND a.start_date_time >= NOW()
AND e.event_category = 'ONLINE_EVENT'
THEN CONCAT('ae_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Complete --
WHEN (
(a.back_office_status = 2 OR a.back_office_status = 3)
AND a.event_status ='FULL'
AND e.event_category <> 'ONLINE_EVENT'
AND (CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE a.remaining_seat
END)>=0
AND a.quota_waiting_list_seat = 0
)
THEN CONCAT('ac_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Complete (Online Event) --
WHEN (
(a.back_office_status = 2 OR a.back_office_status = 3)
AND a.event_status ='FULL'
AND e.event_category = 'ONLINE_EVENT'
)
THEN CONCAT('ac_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Locked --
WHEN a.back_office_status = 3 THEN CONCAT('ae_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Terminee --
WHEN a.back_office_status = 7 THEN CONCAT('b_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Passe --
WHEN a.back_office_status = 4 THEN CONCAT('c_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Archived --
ELSE CONCAT('d_',a.start_date_time,'_',a.event_schedule_id,'_')
END) ASC,
a.start_date_time ASC
SEPARATOR '')
as bostatus
FROM event_schedule a
WHERE a.event_id = e.event_id
".$where."
AND a.back_office_status NOT IN (6)
)";
}
/*
* @method get total reservations, available reservations etc.
*/
private function _get_my_reservations_info($user_id, $res_type = 0, $btype = 1)
{
if ($res_type == 1) { // My Reservation In History
//0,6,3,1,2
$and_ = " AND (es.back_office_status NOT IN(0,6,3,1,2,7) AND es.event_status != 'CANCEL')
AND e.status = 1 ";
} else {
$and_ = " AND es.back_office_status NOT IN(0,5,6,4)
AND e.status = 1";
}
// booking list
switch ($btype) {
case 1: // Normal Reservation
$select = "SUM(err.number_of_guest+1) total_res, COUNT(DISTINCT es.event_schedule_id) nr_total_events";
$join = "LEFT JOIN event_registration err ON err.event_schedule_id = es.event_schedule_id";
$and_user = "AND err.subscriber = $user_id AND err.status = 1";
break;
case 2: // Waitinglist Reservation
$select = "SUM(erw.number_of_places) total_wres, COUNT(DISTINCT e.event_id) wl_total_events";
$join = "LEFT JOIN event_wait_list erw ON erw.event_schedule_id = es.event_schedule_id";
$and_user = "AND erw.wait_list_subscriber = $user_id AND erw.status = 1";
break;
default: // Historique Reservation
$select = "COUNT(DISTINCT es.event_schedule_id) mh_total_events, es.event_schedule_id as hevent";
$join = " LEFT JOIN event_registration err ON err.event_schedule_id = es.event_schedule_id";
$and_user = "AND ((err.subscriber = $user_id AND err.status = 1) )";
/*
Removed WL in My Reservation Historique
$join .= " LEFT JOIN event_wait_list erw ON erw.event_schedule_id = es.event_schedule_id";
|| (erw.wait_list_subscriber = $user_id AND erw.status = 1)
*/
break;
}
$this->load->model("event_schedule_model");
return "SELECT $select
FROM event e
LEFT JOIN event_schedule es ON es.event_id = e.event_id
$join
LEFT JOIN event_type et ON e.event_type_id = et.event_type_id
LEFT JOIN event_file_attachment efa ON e.event_id = efa.event_id
WHERE efa.attachment_type = 1
AND efa.status = 1
$and_user
$and_
";
}
private function _get_my_reservations_winfo($user_id, $res_type = 0)
{
if ($res_type) {
$and_ = " AND es.back_office_status NOT IN(0,5,6,1,2)
AND e.status = 1 ";
} else {
$and_ = " AND es.back_office_status NOT IN(0,5,6,3,7,4)
AND e.status = 1 AND es.event_status != 'CANCEL'";
}
$this->load->model("event_schedule_model");
return "SELECT SUM(err.number_of_guest)+1,
e.event_id, efa.file_name, e.title, et.event_type, e.description, es.event_status,e.date_feature, e.city_name,
(SELECT COUNT(event_schedule_id) FROM event_schedule esc WHERE event_id = es.event_id) AS event_count,
(CASE WHEN es.event_status = 'CANCEL' THEN (SELECT COUNT(event_status) FROM event_schedule es WHERE es.event_status='CANCEL' AND es.event_id = e.event_id ) ELSE \"\" END) AS cancel_count,
(CASE
WHEN e.is_favorite = 1
THEN (SELECT fl.image FROM frontoffice_logo fl WHERE fl.status =1 )
ELSE \"\"
END) as event_favorite,
(SELECT
GROUP_CONCAT(CONCAT(
CASE
".$this->sort_mres_events_by($user_id, $res_type)."
END
)
ORDER BY
(CASE
".$this->sort_mres_events_by($user_id, $res_type)."
END),
a.start_date_time ASC
SEPARATOR '#')
as bostatus
FROM event_schedule a
WHERE a.event_id = e.event_id
AND a.back_office_status NOT IN (0,5,6)
LIMIT 1
) AS bostatus
".$this->event_schedule_model->query_date()."
FROM event e
LEFT JOIN event_schedule es ON es.event_id = e.event_id
LEFT JOIN event_registration err ON err.event_schedule_id = es.event_schedule_id
LEFT JOIN event_wait_list erw ON erw.event_schedule_id = es.event_schedule_id
LEFT JOIN event_type et ON e.event_type_id = et.event_type_id
LEFT JOIN event_file_attachment efa ON e.event_id = efa.event_id
WHERE efa.attachment_type = 1
AND efa.status = 1
AND ((err.subscriber = $user_id AND err.status = 1) || (erw.wait_list_subscriber = $user_id AND erw.status = 1))
";
}
public function _get_preferences_query($user_id)
{
$subscriber_id = $this->db->select('subscriber_id')
->from('user_subscriber')
->where('subscriber', $user_id)
->get()
->row()
->subscriber_id;
$preferences = $this->db->select('event_preference')
->from('user_subscriber_event_preference')
->where('subscriber_id', $subscriber_id)
->get()
->result_array();
$prefer = array();
foreach($preferences as $preference){
array_push($prefer, $preference['event_preference']);
}
return $prefer;
}
private function sort_mres_events_by($user_id, $res_type = 0){
if(isset($user_id) && !empty($user_id)){
$q_res = "-- Normal Reservation with reservation --
WHEN (
(a.back_office_status = 2 OR a.back_office_status = 3)
AND (a.event_status ='AVAILABLE' OR a.event_status ='FULL')
AND e.event_category <> 'ONLINE_EVENT'
AND a.event_schedule_id IN(
SELECT er.event_schedule_id
FROM event_registration er
LEFT JOIN event_schedule ess ON ess.event_schedule_id = er.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE er.subscriber = '$user_id'
AND ess.event_status != 'CANCEL'
AND ets.event_id = a.event_id
AND er.status =1
)
)
THEN CONCAT('abd_',a.start_date_time,'_',a.event_schedule_id,'_')";
$q_wl = "-- Waiting list with reservation --
WHEN ((a.back_office_status = 2 OR a.back_office_status = 3)
AND (a.event_status ='FULL' OR a.event_status ='AVAILABLE')
AND e.event_category <> 'ONLINE_EVENT'
AND a.event_schedule_id IN(
SELECT ewl.event_schedule_id
FROM event_wait_list ewl
WHERE ewl.wait_list_subscriber = '$user_id'
AND ewl.event_schedule_id = a.event_schedule_id
AND ewl.status =1
)
)
THEN CONCAT('abe_',a.start_date_time,'_',a.event_schedule_id,'_')";
switch ($res_type) {
case 1:
/*GET HISTORY */
$q_part = "{$q_res} {$q_wl}";
break;
case 2:
/*GET WAITLIST*/
$q_part = "{$q_wl}";
break;
default:
/*GET RESERVATIONS*/
$q_part = "{$q_res}";
break;
}
return "-- Normal Reservation (Reopened)--
WHEN (
a.back_office_status = 2
AND a.event_status ='AVAILABLE'
AND e.event_category <> 'ONLINE_EVENT'
AND
(CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE a.remaining_seat
END) > 0
AND '$user_id' NOT IN(
SELECT er.subscriber
FROM event_registration er
LEFT JOIN event_schedule ess ON ess.event_schedule_id = er.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE er.subscriber = '$user_id'
AND ess.event_status != 'CANCEL'
AND ets.event_id = a.event_id
AND er.status =1
)
AND '$user_id' NOT IN(
SELECT ewl.wait_list_subscriber
FROM event_wait_list ewl
LEFT JOIN event_schedule ess ON ess.event_schedule_id = ewl.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE ewl.wait_list_subscriber = '$user_id'
AND ess.event_status != 'CANCEL'
AND ets.event_id = a.event_id
AND ewl.status =1
)
AND ".$this->db->escape($user_id)." NOT IN(
SELECT ewl.wait_list_subscriber
FROM event_wait_list ewl
LEFT JOIN event_schedule ess ON ess.event_schedule_id = ewl.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE ewl.wait_list_subscriber = '$user_id'
AND ess.event_status != 'CANCEL'
AND ets.event_id = a.event_id
AND ewl.status = 1
)
AND ".$this->db->escape($user_id)." NOT IN(
SELECT er.subscriber
FROM event_registration er
LEFT JOIN event_schedule ess ON ess.event_schedule_id = er.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE er.subscriber = ".$this->db->escape($user_id)."
AND ess.event_status != 'CANCEL'
AND ets.event_id = a.event_id
AND er.status = 1
)
)
THEN CONCAT('aa_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Normal Reservation --
WHEN (
a.back_office_status = 2
AND a.event_status ='AVAILABLE'
AND (CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE a.remaining_seat
END) > 0
AND e.event_category <> 'ONLINE_EVENT'
AND '$user_id' NOT IN(
SELECT er.subscriber
FROM event_registration er
LEFT JOIN event_schedule ess ON ess.event_schedule_id = er.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE er.subscriber = '$user_id'
AND ess.event_status != 'CANCEL'
AND ets.event_id = a.event_id
AND er.status =1
)
AND ".$this->db->escape($user_id)." NOT IN(
SELECT ewl.wait_list_subscriber
FROM event_wait_list ewl
LEFT JOIN event_schedule ess ON ess.event_schedule_id = ewl.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE ewl.wait_list_subscriber = ".$this->db->escape($user_id)."
AND ess.event_status != 'CANCEL'
AND ets.event_id = a.event_id
AND ewl.status = 1
)
AND ".$this->db->escape($user_id)." NOT IN(
SELECT er.subscriber
FROM event_registration er
LEFT JOIN event_schedule ess ON ess.event_schedule_id = er.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE er.subscriber = ".$this->db->escape($user_id)."
AND ess.event_status != 'CANCEL'
AND ets.event_id = a.event_id
AND er.status = 1
)
/* AND '$user_id' NOT IN(
SELECT ewl.wait_list_subscriber
FROM event_wait_list ewl
LEFT JOIN event_schedule ess ON ess.event_schedule_id = ewl.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE ewl.wait_list_subscriber = '$user_id'
AND ets.event_id = a.event_id
AND ewl.status =1
)*/
)
THEN CONCAT('ab_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Waiting list --
-- Events with remaining seats greater than zero and event status 'FULL' fall in this condition --
WHEN (
a.back_office_status = 2
AND (a.event_status ='AVAILABLE' OR a.event_status ='FULL')
AND e.event_category <> 'ONLINE_EVENT'
AND
(
(
(CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE a.remaining_seat
END) <= 0
)
OR (
(CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE a.remaining_seat
END) > 0
AND a.event_status ='FULL'
)
)
AND
a.quota_waiting_list_seat > 0
AND '$user_id' NOT IN(
SELECT ewl.wait_list_subscriber
FROM event_wait_list ewl
WHERE ewl.wait_list_subscriber = '$user_id'
AND ewl.event_schedule_id = a.event_schedule_id
AND ewl.status =1
)
AND '$user_id' NOT IN(
SELECT er.subscriber
FROM event_registration er
LEFT JOIN event_schedule ess ON ess.event_schedule_id = er.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE er.subscriber = '$user_id'
AND ess.event_status != 'CANCEL'
AND ets.event_id = a.event_id
AND er.status =1
)
AND ".$this->db->escape($user_id)." NOT IN(
SELECT ewl.wait_list_subscriber
FROM event_wait_list ewl
LEFT JOIN event_schedule ess ON ess.event_schedule_id = ewl.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE ewl.wait_list_subscriber = ".$this->db->escape($user_id)."
AND ess.event_status != 'CANCEL'
AND ets.event_id = a.event_id
AND ewl.status = 1
)
AND ".$this->db->escape($user_id)." NOT IN(
SELECT er.subscriber
FROM event_registration er
LEFT JOIN event_schedule ess ON ess.event_schedule_id = er.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE er.subscriber = ".$this->db->escape($user_id)."
AND ess.event_status != 'CANCEL'
AND ets.event_id = a.event_id
AND er.status = 1
)
)
THEN CONCAT('abc_',a.start_date_time,'_',a.event_schedule_id,'_')
{$q_part}
-- cancelled but not yet passe single dates
WHEN (a.event_status = 'CANCEL' && a.end_date_time IS NOT NULL) &&
((SELECT date_feature FROM event WHERE event_id = a.event_id) = 1)
THEN
CASE
WHEN a.end_date_time > NOW()
THEN CONCAT('abf1_',a.start_date_time,'_',a.event_schedule_id,'_')
ELSE CONCAT('d_',a.start_date_time,'_',a.event_schedule_id,'_')
END
WHEN (a.event_status = 'CANCEL' && a.start_date_time IS NOT NULL) &&
((SELECT date_feature FROM event WHERE event_id = a.event_id) = 1)
THEN
CASE
WHEN DATE_FORMAT( DATE_ADD(a.start_date_time, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') > NOW()
THEN CONCAT('abf1_',DATE_FORMAT( DATE_ADD(a.start_date_time, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00'),'_',a.event_schedule_id,'_')
ELSE CONCAT('d_',a.start_date_time,'_',a.event_schedule_id,'_')
END
-- cancelled but not yet passe multi dates
WHEN (a.event_status = 'CANCEL' && a.end_date_time IS NOT NULL) &&
((SELECT date_feature FROM event WHERE event_id = a.event_id) = 2)
THEN
CASE
WHEN a.end_date_time > NOW()
THEN CONCAT('abf2_',a.start_date_time,'_',a.event_schedule_id,'_')
ELSE CONCAT('d_',a.start_date_time,'_',a.event_schedule_id,'_')
END
WHEN (a.event_status = 'CANCEL' && a.start_date_time IS NOT NULL) &&
((SELECT date_feature FROM event WHERE event_id = a.event_id) = 2)
THEN
CASE
WHEN DATE_FORMAT( DATE_ADD(a.start_date_time, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') > NOW()
THEN CONCAT('abf2_',DATE_FORMAT( DATE_ADD(a.start_date_time, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00'),'_',a.event_schedule_id,'_')
ELSE CONCAT('d_',a.start_date_time,'_',a.event_schedule_id,'_')
END
-- Coming soon --
WHEN (
a.back_office_status = 1
AND (
e.event_category = 'ONLINE_EVENT'
OR
(
'$user_id' NOT IN(
SELECT er.subscriber
FROM event_registration er
LEFT JOIN event_schedule ess ON ess.event_schedule_id = er.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE er.subscriber = '$user_id'
AND ess.event_status != 'CANCEL'
AND ets.event_id = a.event_id
AND er.status =1
)
AND '$user_id' NOT IN(
SELECT ewl.wait_list_subscriber
FROM event_wait_list ewl
LEFT JOIN event_schedule ess ON ess.event_schedule_id = ewl.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE ewl.wait_list_subscriber = '$user_id'
AND ess.event_status != 'CANCEL'
AND ets.event_id = a.event_id
AND ewl.status =1
)
)
)
)
THEN CONCAT('ad_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Event is FULL --
WHEN (
(a.back_office_status = 2 OR a.back_office_status = 3)
AND a.event_status ='FULL'
AND e.event_category <> 'ONLINE_EVENT'
AND (CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE a.remaining_seat
END)>=0 AND a.quota_waiting_list_seat = 0
AND '$user_id' NOT IN(
SELECT er.subscriber
FROM event_registration er
LEFT JOIN event_schedule ess ON ess.event_schedule_id = er.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE er.subscriber = '$user_id'
AND ess.event_status != 'CANCEL'
AND ets.event_id = a.event_id
AND er.status =1
)
AND '$user_id' NOT IN(
SELECT ewl.wait_list_subscriber
FROM event_wait_list ewl
LEFT JOIN event_schedule ess ON ess.event_schedule_id = ewl.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE ewl.wait_list_subscriber = '$user_id'
AND ess.event_status != 'CANCEL'
AND ets.event_id = a.event_id
AND ewl.status =1
)
) THEN CONCAT('ac_',a.start_date_time,'_',a.event_schedule_id,'_')
WHEN a.back_office_status = 3 THEN CONCAT('ad_',a.start_date_time,'_',a.event_schedule_id,'_')
WHEN a.back_office_status = 7 THEN CONCAT('d_',a.start_date_time,'_',a.event_schedule_id,'_')
WHEN a.back_office_status = 4 THEN CONCAT('d_',a.start_date_time,'_',a.event_schedule_id,'_')
ELSE CONCAT('d_',a.start_date_time,'_',a.event_schedule_id,'_') ";
}
}
private function _get_filter_query_mreservations($user_id, $res_type = 0, $search = "")
{
$wsearch = "";
if (!empty($search)) {
$wsearch =" AND (e.title LIKE \"%{$search}%\" OR et.event_type LIKE \"%{$search}%\" OR (CASE
WHEN e.use_different_address_per_date = 1 THEN
(CASE
WHEN es.city_location IS NULL THEN \"\"
ELSE es.city_location
END)
ELSE (CASE
WHEN e.city_location IS NULL THEN \"\"
ELSE e.city_location
END)
END) IN (SELECT city_location_id FROM event_city_location WHERE status = 1 AND city LIKE \"%{$search}%\") )";
}
switch ($res_type) {
case 1:
/*GET HISTORY */
$and_ = " AND ((err.subscriber = {$user_id} AND err.status = 1))"
." AND (es.back_office_status NOT IN(0,6,3,1,2,7) AND es.event_status != 'CANCEL') AND e.status = 1 ";
$join_ = 'LEFT JOIN event_registration err ON err.event_schedule_id = es.event_schedule_id ';
$order_ = '';
$subQueryWhere = 'AND a.back_office_status NOT IN (0,6,3,1,2,7)';
/*
Removed WL in My Reservation Historique
$join .= " LEFT JOIN event_wait_list erw ON erw.event_schedule_id = es.event_schedule_id";
|| (erw.wait_list_subscriber = $user_id AND erw.status = 1)
*/
break;
case 2:
/*GET WAITLIST*/
$and_ = " AND (erw.wait_list_subscriber = $user_id AND erw.status = 1)"
." AND es.back_office_status NOT IN(0,5,6,4) AND e.status = 1";
$join_ = 'LEFT JOIN event_wait_list erw ON erw.event_schedule_id = es.event_schedule_id';
$subQueryWhere = 'AND a.back_office_status NOT IN (0,5,6,4)';
break;
default:
/*GET RESERVATIONS*/
$and_ = " AND (err.subscriber = $user_id AND err.status = 1)"
." AND es.back_office_status NOT IN(0,5,6,4) AND e.status = 1 ";
$join_ = 'LEFT JOIN event_registration err ON err.event_schedule_id = es.event_schedule_id ';
$subQueryWhere = 'AND a.back_office_status NOT IN (0,5,6,4)';
break;
}
$this->load->model("event_schedule_model");
return "SELECT es.event_schedule_id, e.event_id, efa.file_name, e.title, et.event_type, e.description, es.event_status,e.date_feature, e.city_name,
(SELECT COUNT(event_schedule_id) FROM event_schedule esc WHERE event_id = es.event_id) AS event_count,
(CASE WHEN es.event_status = 'CANCEL' THEN (SELECT COUNT(event_status) FROM event_schedule es WHERE es.event_status='CANCEL' AND es.event_id = e.event_id ) ELSE \"\" END) AS cancel_count,
(CASE
WHEN e.is_favorite = 1
THEN (SELECT fl.image FROM frontoffice_logo fl WHERE fl.status =1 )
ELSE \"\"
END) as event_favorite,
(SELECT
GROUP_CONCAT(CONCAT(
CASE
".$this->sort_mres_events_by($user_id, $res_type)."
END
)
ORDER BY
(CASE
".$this->sort_mres_events_by($user_id, $res_type)."
END),
a.start_date_time ASC
SEPARATOR '#')
as bostatus
FROM event_schedule a
WHERE a.event_id = e.event_id
".$subQueryWhere."
LIMIT 1
) AS bostatus
FROM event e
LEFT JOIN event_schedule es ON es.event_id = e.event_id
$join_
LEFT JOIN event_type et ON e.event_type_id = et.event_type_id
LEFT JOIN event_file_attachment efa ON e.event_id = efa.event_id
WHERE efa.attachment_type = 1
AND efa.status = 1
$and_ $wsearch
";
}
/*
* get all user reservations, limit by 6
*/
public function filter_event_reservations($filter, $page, $month, $type, $city, $user_id, $logged_in, $res_type = 0, $search = "")
{
$pages = $page*6-6;
if($logged_in) $preferences = $this->_get_preferences_query($user_id);
// // $user_id = ($logged_in)?$user_id:false;
// echo $user_id;
// Filter ------------------------------------------------------------------------
$query = $this->_get_filter_query_mreservations($user_id, $res_type, $search);
//$query .= $this->_get_filter_type($filter, $month, $type, $city);
// $query .= ' GROUP BY es.event_schedule_id ORDER BY bostatus ASC';
$query .= ' GROUP BY es.event_schedule_id ORDER BY
SUBSTRING_INDEX(bostatus,"_",1) ASC,
CASE WHEN SUBSTRING_INDEX(bostatus,"_",1) NOT IN("d") THEN SUBSTRING_INDEX(SUBSTRING_INDEX(bostatus,"_",2),"_",-1) END ASC,
CASE WHEN SUBSTRING_INDEX(bostatus,"_",1) IN("d") THEN SUBSTRING_INDEX(SUBSTRING_INDEX(bostatus,"_",2),"_",-1) END DESC';
$query .= ' LIMIT '.(($pages > 0)?$pages:0).', 6';
$events = $this->db->query($query);
// Pagination ---------------------------------------------------------------------
$query = $this->_get_filter_query_mreservations($user_id, $res_type, $search);
//$query .= $this->_get_filter_type($filter, $month, $type, $city);
$query .= ' GROUP BY es.event_schedule_id ORDER BY
SUBSTRING_INDEX(bostatus,"_",1) ASC,
CASE WHEN SUBSTRING_INDEX(bostatus,"_",1) NOT IN("d") THEN SUBSTRING_INDEX(SUBSTRING_INDEX(bostatus,"_",2),"_",-1) END ASC,
CASE WHEN SUBSTRING_INDEX(bostatus,"_",1) IN("d") THEN SUBSTRING_INDEX(SUBSTRING_INDEX(bostatus,"_",2),"_",-1) END DESC';
// if($logged_in) $this->db->where_in('e.event_type_id', $preferences);
$pagination = $this->db->query($query);
return array(
'data' => $events->result(),
'pagination' => ceil($pagination->num_rows()/6),
'result_count' => $events->num_rows(),
'logged_in' => $logged_in
);
}
/*
* get user reservations for the current even
*/
public function get_reservations_info($filter, $page, $month, $type, $city, $user_id, $logged_in, $res_type = 0)
{
$this->load->model('event_moderation_model');
if($logged_in) $preferences = $this->_get_preferences_query($user_id);
//normal reservations
$bquery = $this->_get_my_reservations_info($user_id, 0, 1);
$bquery .= $this->_get_filter_type($filter, $month, $type, $city);
$booking = $this->db->query($bquery);
//waitlist reservations
$wquery = $this->_get_my_reservations_info($user_id, 2, 2);
$wquery .= $this->_get_filter_type($filter, $month, $type, $city);
$waitlist = $this->db->query($wquery);
//number of events participated
$hquery = $this->_get_my_reservations_info($user_id, 1, 3);
$hquery .= $this->_get_filter_type($filter, $month, $type, $city);
$hev_count = $this->db->query($hquery);
$total_waitlist_active_res = $this->event_moderation_model->getNumberOfReservation(3, $user_id, true, 0, 0, [], false, true);
// Total number of cancelled moderated events that the user is reserved
$total_waitlist_active_cancel_res = $this->event_moderation_model->getNumberOfCancelledReservation(3, $user_id, true, 0, 0, [], false, true);
// Total number of moderated events that the user is reserved
$total_moderated_waitlist_res_events = $this->event_moderation_model->getNumberOfReservation(3, $user_id, false, 0);
return array(
'normal_res' => $booking->result()[0]->total_res ?? 0,
'waitlist_res' => $waitlist->result()[0]->total_wres ?? 0,
'total_waitlist_active_res' => $total_waitlist_active_res ?? 0,
'total_waitlist_cancel_res' => $total_waitlist_active_cancel_res ?? 0,
'total_moderated_waitlist_res_events' => $total_moderated_waitlist_res_events ?? 0,
'nr_total_events' => $booking->result()[0]->nr_total_events ?? 0,
'wl_total_events' => $waitlist->result()[0]->wl_total_events ?? 0,
'mh_total_events' => $hev_count->result()[0]->mh_total_events ?? 0,
'logged_in' => $logged_in
);
}
private function sort_fo_events_by($user_id){
if ( isset($user_id) && !empty($user_id) )
{
return "-- Normal Reservation (Reopened)--
WHEN (
a.back_office_status = 2
AND a.event_status ='AVAILABLE'
AND
(
(
e.event_category = 'ONLINE_EVENT'
AND
a.start_date_time < NOW()
)
OR
(
e.event_category <> 'ONLINE_EVENT'
AND (CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE a.remaining_seat
END) > 0
AND ".$this->db->escape($user_id)." NOT IN(
SELECT er.subscriber
FROM event_registration er
LEFT JOIN event_schedule ess ON ess.event_schedule_id = er.event_schedule_id
WHERE er.subscriber = ".$this->db->escape($user_id)."
AND ess.event_status != 'CANCEL'
AND er.event_schedule_id = a.event_schedule_id
AND ess.event_id = a.event_id
AND ess.back_office_status NOT IN(4, 5, 7)
AND er.status = 1
)
AND ".$this->db->escape($user_id)." NOT IN(
SELECT er.subscriber
FROM event_registration er
LEFT JOIN event_schedule ess ON ess.event_schedule_id = er.event_schedule_id
WHERE er.subscriber = ".$this->db->escape($user_id)."
AND ess.event_status != 'CANCEL'
AND ess.event_id = a.event_id
AND ess.back_office_status NOT IN(4, 5, 7)
AND er.status = 1
)
AND
(
(".$this->db->escape($user_id)." NOT IN(
SELECT ewl.wait_list_subscriber
FROM event_wait_list ewl
LEFT JOIN event_schedule ess ON ess.event_schedule_id = ewl.event_schedule_id
WHERE ewl.wait_list_subscriber = ".$this->db->escape($user_id)."
AND ess.event_status != 'CANCEL'
AND ess.back_office_status NOT IN(4, 5, 7)
AND ess.event_id = a.event_id
AND ewl.status =1)
)
OR
(".$this->db->escape($user_id)." IN(
SELECT ewl.wait_list_subscriber
FROM event_wait_list ewl
LEFT JOIN event_schedule ess ON ess.event_schedule_id = ewl.event_schedule_id
WHERE ewl.wait_list_subscriber = ".$this->db->escape($user_id)."
AND ess.event_status != 'CANCEL'
AND ess.event_id = a.event_id
AND ess.back_office_status NOT IN(4, 5, 7)
AND ess.remaining_seat > 0
AND ewl.status =1)
)
)
)
) -- ENDOR --
)
THEN CONCAT('aa_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Normal Reservation --
WHEN (
a.back_office_status = 2
AND a.event_status ='AVAILABLE'
AND e.event_category <> 'ONLINE_EVENT'
AND (CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE a.remaining_seat
END) > 0
AND ".$this->db->escape($user_id)." NOT IN(
SELECT er.subscriber
FROM event_registration er
LEFT JOIN event_schedule ess ON ess.event_schedule_id = er.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE er.subscriber = ".$this->db->escape($user_id)."
AND ess.event_status != 'CANCEL'
AND er.event_schedule_id = a.event_schedule_id
AND ess.back_office_status NOT IN(4, 5, 7)
AND ets.event_id = a.event_id
AND er.status = 1
)
AND ".$this->db->escape($user_id)." NOT IN(
SELECT er.subscriber
FROM event_registration er
LEFT JOIN event_schedule ess ON ess.event_schedule_id = er.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE er.subscriber = ".$this->db->escape($user_id)."
AND ess.event_status != 'CANCEL'
AND ess.back_office_status IN(3,4)
AND ess.back_office_status NOT IN(7, 5)
AND ets.event_id = a.event_id
AND er.status = 1
)
/*AND ".$this->db->escape($user_id)." NOT IN(
SELECT ewl.wait_list_subscriber
FROM event_wait_list ewl
LEFT JOIN event_schedule ess ON ess.event_schedule_id = ewl.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE ewl.wait_list_subscriber = ".$this->db->escape($user_id)."
AND ets.event_id = a.event_id
AND ess.back_office_status NOT IN(4, 5, 7)
AND ewl.status = 1
)*/
)
THEN CONCAT('ab_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Waiting list --
WHEN (a.back_office_status = 2
AND (a.event_status ='AVAILABLE' OR a.event_status ='FULL')
AND e.event_category <> 'ONLINE_EVENT'
AND (CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE a.remaining_seat
END) >= 0
AND a.quota_waiting_list_seat > 0
AND ".$this->db->escape($user_id)." NOT IN(
SELECT ewl.wait_list_subscriber
FROM event_wait_list ewl
WHERE ewl.wait_list_subscriber = ".$this->db->escape($user_id)."
AND ewl.event_schedule_id = a.event_schedule_id
AND ewl.status = 1
)
AND ".$this->db->escape($user_id)." NOT IN(
SELECT er.subscriber
FROM event_registration er
LEFT JOIN event_schedule ess ON ess.event_schedule_id = er.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE er.subscriber = ".$this->db->escape($user_id)."
AND ess.event_status != 'CANCEL'
AND ets.event_id = a.event_id
AND er.event_schedule_id = a.event_schedule_id
AND ess.back_office_status NOT IN(4, 5, 7)
AND er.status = 1
)
AND ".$this->db->escape($user_id)." NOT IN(
SELECT ewl.wait_list_subscriber
FROM event_wait_list ewl
LEFT JOIN event_schedule ess ON ess.event_schedule_id = ewl.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE ewl.wait_list_subscriber = ".$this->db->escape($user_id)."
AND ess.event_status != 'CANCEL'
AND ets.event_id = a.event_id
AND ess.back_office_status NOT IN(4, 5, 7)
AND ewl.status = 1
)
AND ".$this->db->escape($user_id)." NOT IN(
SELECT er.subscriber
FROM event_registration er
LEFT JOIN event_schedule ess ON ess.event_schedule_id = er.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE er.subscriber = ".$this->db->escape($user_id)."
AND ess.event_status != 'CANCEL'
AND ets.event_id = a.event_id
AND ess.back_office_status NOT IN(4, 5, 7)
AND er.status = 1
)
)
THEN CONCAT('abc_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Normal Reservation with reservation --
WHEN (
(a.back_office_status = 2 OR a.back_office_status = 3)
AND (a.event_status ='AVAILABLE' OR a.event_status ='FULL')
AND e.event_category <> 'ONLINE_EVENT'
AND a.event_schedule_id IN(
SELECT er.event_schedule_id
FROM event_registration er
LEFT JOIN event_schedule ess ON ess.event_schedule_id = er.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE er.subscriber = ".$this->db->escape($user_id)."
AND ess.event_status != 'CANCEL'
AND ets.event_id = a.event_id
AND er.event_schedule_id = a.event_schedule_id
AND er.status = 1
)
)
THEN CONCAT('abd_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Waiting list with reservation --
-- Events with remaining seats greater than zero and event status 'FULL' fall in this condition --
WHEN ((a.back_office_status = 2 OR a.back_office_status = 3)
AND (a.event_status ='FULL' OR a.event_status ='AVAILABLE')
AND e.event_category <> 'ONLINE_EVENT'
AND
(
(
(CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE a.remaining_seat
END) <= 0
)
OR (
(CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE a.remaining_seat
END) > 0
AND a.event_status ='FULL'
)
)
AND a.event_schedule_id IN(
SELECT ewl.event_schedule_id
FROM event_wait_list ewl
WHERE ewl.wait_list_subscriber = ".$this->db->escape($user_id)."
AND ewl.event_schedule_id = a.event_schedule_id
AND ewl.status = 1
)
AND ".$this->db->escape($user_id)." NOT IN(
SELECT er.subscriber
FROM event_registration er
LEFT JOIN event_schedule ess ON ess.event_schedule_id = er.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE er.subscriber = ".$this->db->escape($user_id)."
AND ess.event_status != 'CANCEL'
AND ets.event_id = a.event_id
AND er.event_schedule_id = a.event_schedule_id
AND er.status = 1
)
)
THEN CONCAT('abe_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Cancelled but not yet passe single dates
WHEN (a.event_status = 'CANCEL' && a.end_date_time IS NOT NULL) &&
((SELECT date_feature FROM event WHERE event_id = a.event_id) = 1)
THEN
CASE
WHEN a.end_date_time > NOW()
THEN CONCAT('abf1_',a.start_date_time,'_',a.event_schedule_id,'_')
ELSE CONCAT('d_',a.start_date_time,'_',a.event_schedule_id,'_')
END
WHEN (a.event_status = 'CANCEL' && a.start_date_time IS NOT NULL) &&
((SELECT date_feature FROM event WHERE event_id = a.event_id) = 1)
THEN
CASE
WHEN DATE_FORMAT( DATE_ADD(a.start_date_time, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') > NOW()
THEN CONCAT('abf1_',DATE_FORMAT( DATE_ADD(a.start_date_time, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00'),'_',a.event_schedule_id,'_')
ELSE CONCAT('d_',a.start_date_time,'_',a.event_schedule_id,'_')
END
-- Cancelled but not yet passe multi dates
WHEN (a.event_status = 'CANCEL' && a.end_date_time IS NOT NULL) &&
((SELECT date_feature FROM event WHERE event_id = a.event_id) = 2)
THEN
CASE -- This is when theres is complete event that is earlier that the cancelled event --
WHEN a.end_date_time > NOW() &&
(
SELECT COUNT(event_schedule_id) FROM event_schedule es_2021
WHERE es_2021.event_id = e.event_id
AND a.start_date_time > es_2021.start_date_time
AND
(
( es_2021.back_office_status = 2 OR es_2021.back_office_status = 3)
AND ( es_2021.event_status ='AVAILABLE' OR es_2021.event_status ='FULL')
AND (CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE es_2021.remaining_seat
END) >= 0
AND es_2021.quota_waiting_list_seat = 0
AND e.event_category <> 'ONLINE_EVENT'
AND ".$this->db->escape($user_id)." NOT IN(
SELECT er.subscriber
FROM event_registration er
LEFT JOIN event_schedule ess ON ess.event_schedule_id = er.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE er.subscriber = ".$this->db->escape($user_id)."
AND ess.event_status != 'CANCEL'
AND er.event_schedule_id = es_2021.event_schedule_id
AND ets.event_id = es_2021.event_id
AND er.status = 1
)
AND ".$this->db->escape($user_id)." NOT IN(
SELECT ewl.wait_list_subscriber
FROM event_wait_list ewl
LEFT JOIN event_schedule ess ON ess.event_schedule_id = ewl.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE ewl.wait_list_subscriber = ".$this->db->escape($user_id)."
AND ess.event_status != 'CANCEL'
AND ets.event_id = es_2021.event_id
AND ewl.event_schedule_id = es_2021.event_schedule_id
AND ewl.status = 1
)
)
OR
(
( es_2021.back_office_status = 2 OR es_2021.back_office_status = 3)
AND es_2021.event_status ='FULL'
AND e.event_category = 'ONLINE_EVENT'
)
) > 0
THEN CONCAT('ac_',a.start_date_time,'_',a.event_schedule_id,'_')
WHEN a.end_date_time > NOW()
THEN CONCAT('abf2_',a.start_date_time,'_',a.event_schedule_id,'_')
ELSE CONCAT('d_',a.start_date_time,'_',a.event_schedule_id,'_')
END
WHEN (a.event_status = 'CANCEL' && a.start_date_time IS NOT NULL) &&
((SELECT date_feature FROM event WHERE event_id = a.event_id) = 2)
THEN
CASE -- This is when theres is complete event that is earlier that the cancelled event --
WHEN DATE_FORMAT( DATE_ADD(a.start_date_time, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') > NOW()
&&
(
SELECT COUNT( es_2021.event_schedule_id) FROM event_schedule es_2021
WHERE es_2021.event_id = e.event_id
AND a.start_date_time > es_2021.start_date_time
AND
(
( es_2021.back_office_status = 2 OR es_2021.back_office_status = 3)
AND ( es_2021.event_status ='AVAILABLE' OR es_2021.event_status ='FULL')
AND (CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE es_2021.remaining_seat
END) >= 0
AND es_2021.quota_waiting_list_seat = 0
AND e.event_category <> 'ONLINE_EVENT'
AND ".$this->db->escape($user_id)." NOT IN(
SELECT er.subscriber
FROM event_registration er
LEFT JOIN event_schedule ess ON ess.event_schedule_id = er.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE er.subscriber = ".$this->db->escape($user_id)."
AND ess.event_status != 'CANCEL'
AND er.event_schedule_id = es_2021.event_schedule_id
AND ets.event_id = es_2021.event_id
AND er.status = 1
)
AND ".$this->db->escape($user_id)." NOT IN(
SELECT ewl.wait_list_subscriber
FROM event_wait_list ewl
LEFT JOIN event_schedule ess ON ess.event_schedule_id = ewl.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE ewl.wait_list_subscriber = ".$this->db->escape($user_id)."
AND ess.event_status != 'CANCEL'
AND ets.event_id = es_2021.event_id
AND ewl.event_schedule_id = es_2021.event_schedule_id
AND ewl.status = 1
)
)
OR
(
( es_2021.back_office_status = 2 OR es_2021.back_office_status = 3)
AND es_2021.event_status ='FULL'
AND e.event_category = 'ONLINE_EVENT'
)
) > 0
THEN CONCAT('ac_',a.start_date_time,'_',a.event_schedule_id,'_')
WHEN DATE_FORMAT( DATE_ADD(a.start_date_time, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') > NOW()
THEN CONCAT('abf2_',DATE_FORMAT( DATE_ADD(a.start_date_time, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00'),'_',a.event_schedule_id,'_')
ELSE CONCAT('d_',a.start_date_time,'_',a.event_schedule_id,'_')
END
-- Coming soon --
WHEN (
a.back_office_status = 1
AND
(
(
e.event_category = 'ONLINE_EVENT'
AND
a.start_date_time >= NOW()
)
OR
(
".$this->db->escape($user_id)." NOT IN(
SELECT er.subscriber
FROM event_registration er
LEFT JOIN event_schedule ess ON ess.event_schedule_id = er.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE er.subscriber = ".$this->db->escape($user_id)."
AND ess.event_status != 'CANCEL'
AND ets.event_id = a.event_id
AND er.event_schedule_id = a.event_schedule_id
AND er.status = 1
)
AND ".$this->db->escape($user_id)." NOT IN(
SELECT ewl.wait_list_subscriber
FROM event_wait_list ewl
LEFT JOIN event_schedule ess ON ess.event_schedule_id = ewl.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE ewl.wait_list_subscriber = ".$this->db->escape($user_id)."
AND ess.event_status != 'CANCEL'
AND ets.event_id = a.event_id
AND ewl.event_schedule_id = a.event_schedule_id
AND ewl.status = 1
)
)
)
)
THEN CONCAT('ad_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Event is FULL OR COMPLETE (Regular Event) --
WHEN (
(a.back_office_status = 2 OR a.back_office_status = 3)
AND (a.event_status ='AVAILABLE' OR a.event_status ='FULL')
AND (CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE a.remaining_seat
END) >= 0
AND a.quota_waiting_list_seat = 0
AND e.event_category <> 'ONLINE_EVENT'
AND ".$this->db->escape($user_id)." NOT IN(
SELECT er.subscriber
FROM event_registration er
LEFT JOIN event_schedule ess ON ess.event_schedule_id = er.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE er.subscriber = ".$this->db->escape($user_id)."
AND ess.event_status != 'CANCEL'
AND er.event_schedule_id = a.event_schedule_id
AND ets.event_id = a.event_id
AND er.status = 1
)
AND ".$this->db->escape($user_id)." NOT IN(
SELECT ewl.wait_list_subscriber
FROM event_wait_list ewl
LEFT JOIN event_schedule ess ON ess.event_schedule_id = ewl.event_schedule_id
LEFT JOIN event ets ON ets.event_id = ess.event_id
WHERE ewl.wait_list_subscriber = ".$this->db->escape($user_id)."
AND ess.event_status != 'CANCEL'
AND ets.event_id = a.event_id
AND ewl.event_schedule_id = a.event_schedule_id
AND ewl.status = 1
)
) THEN CONCAT('ac_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Event is FULL OR COMPLETE (Online Event) --
WHEN (
(a.back_office_status = 2 OR a.back_office_status = 3)
AND a.event_status ='FULL'
AND e.event_category = 'ONLINE_EVENT'
)
THEN CONCAT('ac_',a.start_date_time,'_',a.event_schedule_id,'_')
WHEN a.back_office_status = 3 THEN CONCAT('ad_',a.start_date_time,'_',a.event_schedule_id,'_')
WHEN a.back_office_status = 7 THEN CONCAT('c_',a.start_date_time,'_',a.event_schedule_id,'_')
WHEN a.back_office_status = 4 THEN CONCAT('d_',a.start_date_time,'_',a.event_schedule_id,'_')
ELSE CONCAT('d_',a.start_date_time,'_',a.event_schedule_id,'_') ";
} else {
return "-- Normal Reservation --
WHEN (
a.back_office_status = 2
AND a.event_status = 'AVAILABLE'
AND (
(
e.event_category = 'ONLINE_EVENT'
AND
a.start_date_time < NOW()
)
OR
(
e.event_category <> 'ONLINE_EVENT'
AND
(CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE a.remaining_seat
END) > 0
)
)
)
THEN CONCAT('aa_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Waiting List --
WHEN a.back_office_status = 2
AND (a.event_status ='FULL' OR a.event_status ='AVAILABLE' )
AND e.event_category <> 'ONLINE_EVENT'
AND (CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE a.remaining_seat
END) >= 0
AND a.quota_waiting_list_seat > 0
THEN CONCAT('ab_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Cancelled but not yet passe single dates --
WHEN (a.event_status = 'CANCEL' && a.end_date_time IS NOT NULL) &&
((SELECT date_feature FROM event WHERE event_id = a.event_id) = 1)
THEN
CASE
WHEN a.end_date_time > NOW()
THEN CONCAT('ac1_',a.start_date_time,'_',a.event_schedule_id,'_')
ELSE CONCAT('d_',a.start_date_time,'_',a.event_schedule_id,'_')
END
WHEN (a.event_status = 'CANCEL' && a.start_date_time IS NOT NULL) &&
((SELECT date_feature FROM event WHERE event_id = a.event_id) = 1)
THEN
CASE
WHEN DATE_FORMAT( DATE_ADD(a.start_date_time, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') > NOW()
THEN CONCAT('ac1_',DATE_FORMAT( DATE_ADD(a.start_date_time, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00'),'_',a.event_schedule_id,'_')
ELSE CONCAT('d_',a.start_date_time,'_',a.event_schedule_id,'_')
END
-- Cancelled but not yet passe multi dates --
WHEN (a.event_status = 'CANCEL' && a.end_date_time IS NOT NULL) &&
((SELECT date_feature FROM event WHERE event_id = a.event_id) = 2)
THEN
CASE -- This is when theres is complete event that is earlier that the cancelled event --
WHEN a.end_date_time > NOW() &&
(
SELECT COUNT(es_2021.event_schedule_id) FROM event_schedule es_2021
WHERE es_2021.event_id = e.event_id
AND a.start_date_time > es_2021.start_date_time
AND
(
(es_2021.back_office_status = 2 OR es_2021.back_office_status = 3)
AND (es_2021.event_status ='AVAILABLE' OR es_2021.event_status ='FULL')
AND (CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE es_2021.remaining_seat
END) >= 0
AND es_2021.quota_waiting_list_seat = 0
AND e.event_category <> 'ONLINE_EVENT'
)
OR
(
(es_2021.back_office_status = 2 OR es_2021.back_office_status = 3)
AND es_2021.event_status ='FULL'
AND e.event_category = 'ONLINE_EVENT'
)
) > 0
THEN CONCAT('ad_',a.start_date_time,'_',a.event_schedule_id,'_')
WHEN a.end_date_time > NOW()
THEN CONCAT('ac2_',a.start_date_time,'_',a.event_schedule_id,'_')
ELSE CONCAT('d_',a.start_date_time,'_',a.event_schedule_id,'_')
END
WHEN (a.event_status = 'CANCEL' && a.start_date_time IS NOT NULL) &&
((SELECT date_feature FROM event WHERE event_id = a.event_id) = 2)
THEN
CASE -- This is when theres is complete event that is earlier that the cancelled event --
WHEN DATE_FORMAT( DATE_ADD(a.start_date_time, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') > NOW() &&
(
SELECT COUNT(event_schedule_id) FROM event_schedule es_2021
WHERE es_2021.event_id = e.event_id
AND a.start_date_time > es_2021.start_date_time
AND
(
(es_2021.back_office_status = 2 OR es_2021.back_office_status = 3)
AND (es_2021.event_status ='AVAILABLE' OR es_2021.event_status ='FULL')
AND (CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE es_2021.remaining_seat
END) >= 0
AND es_2021.quota_waiting_list_seat = 0
AND e.event_category <> 'ONLINE_EVENT'
)
OR
(
(es_2021.back_office_status = 2 OR es_2021.back_office_status = 3)
AND es_2021.event_status ='FULL'
AND e.event_category = 'ONLINE_EVENT'
)
) > 0
THEN CONCAT('ad_',a.start_date_time,'_',a.event_schedule_id,'_')
WHEN DATE_FORMAT( DATE_ADD(a.start_date_time, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') > NOW()
THEN CONCAT('ac2_',DATE_FORMAT( DATE_ADD(a.start_date_time, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00'),'_',a.event_schedule_id,'_')
ELSE CONCAT('d_',a.start_date_time,'_',a.event_schedule_id,'_')
END
-- Coming soon (Regular Event)--
WHEN a.back_office_status = 1 AND e.event_category <> 'ONLINE_EVENT'
THEN CONCAT('ae_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Coming soon (Online Event) --
WHEN (a.back_office_status = 1 OR a.back_office_status = 2)
AND a.start_date_time >= NOW()
AND e.event_category = 'ONLINE_EVENT'
THEN CONCAT('ae_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Complete (Regular Event) --
WHEN (
(a.back_office_status = 2 OR a.back_office_status = 3)
AND (a.event_status ='AVAILABLE' OR a.event_status ='FULL')
AND (CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE a.remaining_seat
END) >= 0
AND a.quota_waiting_list_seat = 0
AND e.event_category <> 'ONLINE_EVENT'
)
THEN CONCAT('ad_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Complete (Online Event) --
WHEN (
(a.back_office_status = 2 OR a.back_office_status = 3)
AND a.event_status ='FULL'
AND e.event_category = 'ONLINE_EVENT'
)
THEN CONCAT('ad_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Locked --
WHEN a.back_office_status = 3 THEN CONCAT('aeee_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Terminee --
WHEN a.back_office_status = 7 THEN CONCAT('b_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Passe --
WHEN a.back_office_status = 4 THEN CONCAT('d_',a.start_date_time,'_',a.event_schedule_id,'_')
-- Archived --
ELSE CONCAT('d_',a.start_date_time,'_',a.event_schedule_id,'_') ";
}
}
private function _get_filter_query($user_id)
{
$this->load->model("event_schedule_model");
// SELECT es.event_schedule_id, e.event_id, efa.file_name,
return "SELECT es.event_schedule_id, e.event_id, e.trailer, e.workshop_session,
e.title, e.workshop_author, e.author_label, e.workshop_author_description, e.workshop_banner,
e.number_of_sessions, e.hours_per_session, e.event_status as status_of_event, e.remaining_combined_seat,
et.event_type, e.description, es.event_status,e.date_feature, e.use_different_address_per_date, e.reservation_end_date, e.night_class_prices,
COUNT(es.event_id) AS event_count,
NOW() as _now,
e.event_category,
(CASE WHEN e.event_status = 'CANCEL' THEN (SELECT COUNT(event_status) FROM event_schedule es WHERE es.event_status='CANCEL' AND es.event_id = e.event_id ) ELSE \"\" END) AS cancel_count,
(CASE
WHEN e.is_favorite = 1
THEN (SELECT fl.image FROM frontoffice_logo fl WHERE fl.status =1 )
ELSE \"\"
END) as event_favorite,
(SELECT efa.file_name FROM event_file_attachment efa WHERE e.event_id = efa.event_id AND efa.attachment_type = 1 AND efa.status = 1 LIMIT 1) AS 'file_name',
(SELECT
GROUP_CONCAT(CONCAT(
CASE
".$this->sort_fo_events_by($user_id)."
END
)
ORDER BY
(CASE
".$this->sort_fo_events_by($user_id)."
END),
a.start_date_time ASC
SEPARATOR '#')
as bostatus
FROM event_schedule a
WHERE a.event_id = e.event_id
AND a.back_office_status NOT IN (0,5,6)
LIMIT 1
) AS bostatus
FROM event e
INNER JOIN event_schedule es ON es.event_id = e.event_id
LEFT JOIN event_type et ON e.event_type_id = et.event_type_id
WHERE e.back_office_status NOT IN(0,5)
AND e.status =1
";
// LEFT JOIN event_file_attachment efa ON e.event_id = efa.event_id
// WHERE efa.attachment_type = 1
// AND efa.status = 1
}
public function filter_events($filter, $page, $month, $type, $city, $user_id, $logged_in,$workshop_session='')
{
$pages = $page*HP_EVENT_DISPLAY['perPage']-HP_EVENT_DISPLAY['perPage'];
if($logged_in) $preferences = $this->_get_preferences_query($user_id);
// // $user_id = ($logged_in)?$user_id:false;
// echo $user_id;
// Filter ------------------------------------------------------------------------
$query = $this->_get_filter_query($user_id);
$query .= $this->_get_filter_type($filter, $month, $type, $city,$workshop_session);
// $query .= ' GROUP BY e.event_id ORDER BY bostatus ASC';
// Dev #44476 - FO - Sorting out workshops by the chronological posting date order //
// $query .= ' GROUP BY e.event_id ORDER BY
// SUBSTRING_INDEX(bostatus,"_",1) ASC,
// CASE WHEN SUBSTRING_INDEX(bostatus,"_",1) NOT IN("d") THEN SUBSTRING_INDEX(SUBSTRING_INDEX(bostatus,"_",2),"_",-1) END ASC,
// CASE WHEN SUBSTRING_INDEX(bostatus,"_",1) IN("d") THEN SUBSTRING_INDEX(SUBSTRING_INDEX(bostatus,"_",2),"_",-1) END DESC';
$query .= ' GROUP BY e.event_id ORDER BY e.event_id DESC';
$query .= ' LIMIT '.(($pages > 0)?$pages:0).', '.HP_EVENT_DISPLAY['perPage'].' ';
//print $query;
$events = $this->db->query($query);
// Pagination ---------------------------------------------------------------------
$query = $this->_get_filter_query($user_id);
$query .= $this->_get_filter_type($filter, $month, $type, $city,$workshop_session);
// Dev #44476 - FO - Sorting out workshops by the chronological posting date order //
// $query .= ' GROUP BY e.event_id ORDER BY
// SUBSTRING_INDEX(bostatus,"_",1) ASC,
// CASE WHEN SUBSTRING_INDEX(bostatus,"_",1) NOT IN("d") THEN SUBSTRING_INDEX(SUBSTRING_INDEX(bostatus,"_",2),"_",-1) END ASC,
// CASE WHEN SUBSTRING_INDEX(bostatus,"_",1) IN("d") THEN SUBSTRING_INDEX(SUBSTRING_INDEX(bostatus,"_",2),"_",-1) END DESC';
// if($logged_in) $this->db->where_in('e.event_type_id', $preferences);
$query .= ' GROUP BY e.event_id ORDER BY e.event_id DESC';
$pagination = $this->db->query($query);
return array(
'data' => $events->result(),
'pagination' => ceil($pagination->num_rows()/HP_EVENT_DISPLAY['perPage']),
'result_count' => $events->num_rows(),
'logged_in' => $logged_in
);
}
private function _get_filter_type($filter, $month, $type, $city, $workshop_session = '')
{
$q_w = ' AND ';
$q = [];
if(!empty($workshop_session)){
$ww = explode(",",$workshop_session);
if(count($ww) ==1){
$q_w = " AND e.workshop_session = ".$this->db->escape($ww[0]);
if($ww[0] == "PRESENTIEL")
$q_w = " AND (e.workshop_session = ".$this->db->escape($ww[0])." OR e.workshop_session is null)";
}
else{
foreach($ww as $w){
$q[] = "e.workshop_session = ".$this->db->escape($w);
if($w == "PRESENTIEL")
$q[] .= "e.workshop_session is null";
}
$q_w .= "(".implode(" OR ",$q).")";
}
}
$q_t = ' AND ';
$t = [];
if(!empty($type)){
$tt = explode(",",$type);
if(count($tt) ==1){
$q_t = " AND e.event_type_id = ".$this->db->escape($tt[0]);
}
else{
foreach($tt as $ttt => $o){
$t[] = "e.event_type_id = ".$this->db->escape($o);
}
$q_t .= "(".implode(" OR ",$t).")";
}
}
switch($filter) {
case 'month':
return " AND month(e.start_date_time) = ".$this->db->escape($month);
case 'type':
return " AND e.event_type_id = ".$this->db->escape($type);
case 'type_multiple':
return $q_t;
case 'workshop_session':
return $q_w;
case 'workshop_session_type':
return $q_w.$q_t;
case 'city':
return " AND (CASE
WHEN e.use_different_address_per_date = 1 THEN
(CASE
WHEN es.city_location IS NULL THEN \"\"
ELSE es.city_location
END)
ELSE (CASE
WHEN e.city_location IS NULL THEN \"\"
ELSE e.city_location
END)
END) = ".$this->db->escape($city);
case 'month_city':
return " AND month(e.start_date_time) = ".$this->db->escape($month).
" AND (CASE
WHEN e.use_different_address_per_date = 1 THEN
(CASE
WHEN es.city_location IS NULL THEN \"\"
ELSE es.city_location
END)
ELSE (CASE
WHEN e.city_location IS NULL THEN \"\"
ELSE e.city_location
END)
END) = ".$this->db->escape($city);
case 'month_type':
return " AND month(e.start_date_time) = ".$this->db->escape($month).
" AND e.event_type_id = ".$this->db->escape($type);
case 'type_city':
return " AND e.event_type_id = ".$type.
" AND (CASE
WHEN e.use_different_address_per_date = 1 THEN
(CASE
WHEN es.city_location IS NULL THEN \"\"
ELSE es.city_location
END)
ELSE (CASE
WHEN e.city_location IS NULL THEN \"\"
ELSE e.city_location
END)
END) = ".$this->db->escape($city);
case 'all':
return " AND month(e.start_date_time) = ".$this->db->escape($month).
" AND e.event_type_id = ".$this->db->escape($type).
" AND (CASE
WHEN e.use_different_address_per_date = 1 THEN
(CASE
WHEN es.city_location IS NULL THEN \"\"
ELSE es.city_location
END)
ELSE (CASE
WHEN e.city_location IS NULL THEN \"\"
ELSE e.city_location
END)
END) = ".$this->db->escape($city);
default : return;
}
}
public function _get_page_number($event_id, $user_id=0)
{
$this->db->query('SET @row_num := 0');
$query = $this->db->query("
SELECT @row_num := @row_num + 1 as 'row_number', t.*
FROM (
SELECT e.event_id,
(SELECT
GROUP_CONCAT(CONCAT(
CASE
".$this->sort_fo_events_by($user_id)."
END
)
ORDER BY
(CASE
".$this->sort_fo_events_by($user_id)."
END),
a.start_date_time ASC
SEPARATOR '#')
as bostatus
FROM event_schedule a
WHERE a.event_id = e.event_id
AND a.back_office_status NOT IN (0,5,6)
LIMIT 1
) AS bostatus
FROM event e
LEFT JOIN event_schedule es ON es.event_id = e.event_id
LEFT JOIN event_type et ON e.event_type_id = et.event_type_id
LEFT JOIN event_file_attachment efa ON e.event_id = efa.event_id
WHERE efa.status = 1
AND efa.attachment_type = 1
AND es.back_office_status NOT IN(0,5,6)
AND e.status =1
GROUP BY e.event_id
ORDER BY
SUBSTRING_INDEX(bostatus,'_',1) ASC,
CASE WHEN SUBSTRING_INDEX(bostatus,'_',1) NOT IN('d') THEN SUBSTRING_INDEX(SUBSTRING_INDEX(bostatus,'_',2),'_',-1) END ASC,
CASE WHEN SUBSTRING_INDEX(bostatus,'_',1) IN('d') THEN SUBSTRING_INDEX(SUBSTRING_INDEX(bostatus,'_',2),'_',-1) END DESC
) t, (SELECT @rownum := 0) r
");
foreach($query->result() as $row){
if($row->event_id == $event_id){
return ceil($row->row_number/HP_EVENT_DISPLAY['perPage']);
}
}
}
public function event_details($event_id)
{
$query = "SELECT
e.event_id, e.title,
e.event_type_id, et.event_type,
e.description, (CASE WHEN e.rate > 0 THEN e.rate ELSE \"\" END) as event_rate,
e.seat_feature,
e.date_feature,
e.is_multiple_reservation,
e.is_multiple_waitlist_reservation,
e.use_different_address_per_date,
e.workshop_author,
e.author_label,
e.workshop_author_description,
e.workshop_banner,
e.workshop_event_type,
e.number_of_sessions,
e.hours_per_session,
e.total_available_seat,
e.reservation_start_date,
e.reservation_end_date,
e.start_date_time,
(CASE WHEN e.start_date_time THEN DATE_FORMAT(e.start_date_time, '%M') ELSE \"\" END) AS event_start_month_name,
(CASE WHEN e.start_date_time THEN DATE_FORMAT(e.start_date_time, '%e') ELSE \"\" END) AS event_start_day,
e.event_category,
e.remaining_combined_seat,
e.end_date_time,
e.back_office_status,
e.workshop_author_awards,
e.location,
e.similar_events,
e.workshop_session,
e.learning_outcome,
e.trailer,
e.discounted_price,
e.discount_apply,
e.city_name,
e.night_class_prices,
e.event_status,
e.tag,
(SELECT efa.file_name FROM event_file_attachment efa WHERE efa.event_id = e.event_id AND efa.status = 1 AND efa.attachment_type = 1 LIMIT 1) AS 'file_name',
(SELECT COUNT(esx.event_schedule_id) FROM event_schedule esx WHERE esx.event_id = ? AND esx.back_office_status IN(1,2,3,4,7,5)) as schedDatesCount,
COUNT(es.event_id) AS event_count,
(CASE WHEN es.event_status = 'CANCEL' THEN (SELECT COUNT(event_status) FROM event_schedule es WHERE es.event_status='CANCEL' AND es.event_id =? ) ELSE \"\" END) AS cancel_count,
(CASE
WHEN e.is_favorite = 1
THEN (SELECT fl.image FROM frontoffice_logo fl WHERE fl.status =1 )
ELSE \"\"
END) as event_favorite,
(CASE
WHEN e.city_location IS NULL THEN \"\"
ELSE (SELECT ecl.city FROM event_city_location ecl WHERE ecl.city_location_id = e.city_location AND ecl.status =1)
END) as event_venue,
(CASE
WHEN e.use_different_address_per_date = 0 THEN
(CASE
WHEN e.address IS NULL THEN \"\"
ELSE e.address
END)
ELSE \"\"
END) as event_address,
(CASE
WHEN e.use_different_address_per_date = 0 THEN
(CASE
WHEN e.location IS NULL THEN \"\"
ELSE e.location
END)
ELSE \"\"
END) as event_place_name,
(CASE
WHEN e.use_different_address_per_date = 0 THEN
(CASE
WHEN e.city_name IS NULL THEN \"\"
ELSE e.city_name
END)
ELSE \"\"
END) as city,
(CASE
WHEN e.use_different_address_per_date = 0 THEN
(CASE
WHEN e.code_postal IS NULL THEN \"\"
ELSE e.code_postal
END)
ELSE \"\"
END) as event_postal_code
FROM event e
LEFT JOIN event_type et ON et.event_type_id = e.event_type_id
LEFT JOIN user u ON u.user_id = e.author
LEFT JOIN event_schedule es ON es.event_id = e.event_id
WHERE e.status IN (1)
AND e.event_id IN(
SELECT event_id
FROM event_schedule
WHERE back_office_status IN(1,2,3,4,7,5)
)
AND e.event_id = ?";
return $this->db->query($query, array($event_id, $event_id, $event_id))->row();
}
public function event_details_email($event_id, $ws=false)
{
$this->load->model("event_schedule_model");
if (UserAuth::isLoggedInAsSubscriber()) {
$exclusions = [
BO_STAT['del'],
];
} else {
$exclusions = [
BO_STAT['en_c'],
BO_STAT['arc'],
BO_STAT['del'],
BO_STAT['ter']
];
}
//".$this->event_schedule_model->use_diffent_address()."
//es.event_schedule_id,
$this->db->select("e.event_id,
e.title as event_title,
e.event_status,
et.event_type,
(CASE WHEN e.rate > 0 THEN e.rate ELSE \"\" END) as event_rate,
(CASE WHEN DATE_FORMAT(e.start_date_time, '%Hh%i') = '00h00' THEN \"\" ELSE DATE_FORMAT(e.start_date_time, '%Hh%i') END) as event_start_hour,
efa.file_name as event_picture,
e.description as event_description,
e.start_date_time as event_start_date");
$this->db->select("DAYNAME(e.start_date_time) AS event_start_day_name", FALSE);
$this->db->select("DATE_FORMAT(e.start_date_time, '%e') AS event_start_day", FALSE);
$this->db->select("DATE_FORMAT(e.start_date_time, '%m') AS event_start_month", FALSE);
$this->db->select("DATE_FORMAT(e.start_date_time, '%M') AS event_start_month_name", FALSE);
$this->db->select("DATE_FORMAT(e.start_date_time, '%Y') AS event_start_year", FALSE);
$this->db->from('event e');
$this->db->join('event_type et', 'e.event_type_id=et.event_type_id');
$this->db->join('event_file_attachment efa', 'e.event_id=efa.event_id');
$this->db->where('e.event_id', $event_id);
$this->db->where('efa.status !=', 0);
$this->db->where('efa.attachment_type', 1);
$this->db->where_not_in('e.back_office_status', $exclusions);
$this->db->where('e.status !=', 0);
$this->db->limit(1);
return $this->db->get()->row();
}
public function get_event_details($event_id){
return $this->db->select('status, title, date_feature, workshop_author, trailer')
->from('event')
->where('status !=', 0)
->where('event_id', $event_id)
->limit(1)
->get()
->row();
}
public function event_details_attachment_type($event_id){
$query = $this->db->select('*')
->from('event_file_attachment')
->where('event_id', $event_id)
->where('attachment_type', 2)
->where('status !=', 0)
->get()
->row();
if($query) return 2;
else return 1;
}
public function current_event($event_id){
$query = $this->db->select('')
->from('event')
->where('status !=', 0)
->where('event_id', $event_id)
->get()
->row();
return $query;
}
public function save_link_to_cookie($event_id){
$query = $this->db->select('event_type_id')
->from('event')
->where('status !=', 0)
->where('event_id', $event_id)
->get()
->row();
return $query->event_type_id;
}
public function preview_event($event_id){
$query = "SELECT
e.event_id, e.title,
e.event_type_id, et.event_type,
e.description, (CASE WHEN e.rate > 0 THEN e.rate ELSE \"\" END) as event_rate,
efa.file_name, efa.mime_type,
e.seat_feature,
e.date_feature,
e.is_multiple_reservation,
e.is_multiple_waitlist_reservation,
e.use_different_address_per_date,
e.start_date_time,
e.end_date_time,
e.workshop_author_awards,
e.author_label,
e.workshop_author_description,
e.number_of_sessions,
e.hours_per_session
COUNT(es.event_schedule_id) as event_sched,
COUNT(efa.event_id) as attachment_event_id,
(CASE
WHEN e.is_favorite = 1
THEN (SELECT fl.image FROM frontoffice_logo fl WHERE fl.status =1 )
ELSE \"\"
END) as event_favorite,
(CASE
WHEN e.city_location IS NULL THEN \"\"
ELSE (SELECT ecl.city FROM event_city_location ecl WHERE ecl.city_location_id = e.city_location AND ecl.status =1)
END) as event_venue,
(CASE
WHEN e.use_different_address_per_date = 0 THEN
(CASE
WHEN e.address IS NULL THEN \"\"
ELSE e.address
END)
ELSE \"\"
END) as event_address,
(CASE
WHEN e.use_different_address_per_date = 0 THEN
(CASE
WHEN e.location IS NULL THEN \"\"
ELSE e.location
END)
ELSE \"\"
END) as event_place_name,
(CASE
WHEN e.use_different_address_per_date = 0 THEN
(CASE
WHEN e.city_name IS NULL THEN \"\"
ELSE e.city_name
END)
ELSE \"\"
END) as city,
(CASE
WHEN e.use_different_address_per_date = 0 THEN
(CASE
WHEN e.code_postal IS NULL THEN \"\"
ELSE e.code_postal
END)
ELSE \"\"
END) as event_postal_code
FROM event e
LEFT JOIN event_type et ON et.event_type_id = e.event_type_id
LEFT JOIN event_file_attachment efa ON efa.event_id = e.event_id
LEFT JOIN event_schedule es ON es.event_id = e.event_id
LEFT JOIN user u ON u.user_id = e.author
WHERE e.status IN (1)
AND efa.status = 1
AND efa.attachment_type = 1
AND e.event_id = ?";
$result=$this->db->query($query, array($event_id))->row();
return $result;
}
public function event_information($event_id){
$this->db->select("e.*");
$this->db->from("event e");
$this->db->where("e.status !=", 0);
$this->db->where("e.event_id", $event_id);
$this->db->limit(1);
$result = $this->db->get()->row();
return $result;
}
public function check_event_title($event_title, $event_id=""){
if($event_id != ""){
$this->db->where("event_id !=", $event_id);
}
$this->db->where("title", $event_title);
$this->db->where("status !=", 0); //not deleted ones
return $this->db->get("event")->num_rows();
}
public function add_event($user_id, $event_data){
//add remaining values
$event_data["author"] = $user_id;
if(sizeof($event_data) > 0){
$this->db->insert("event", $event_data);
$event_id = $this->db->insert_id();
if($event_id){
return $event_id;
}
}
return false;
}
public function update_event($user_id, $event_data, $event_id){
$event_data["author"] = $user_id;
if(sizeof($event_data)){
$this->db->where("event_id", $event_id);
$update = $this->db->update("event", $event_data);
if($update){
return true;
}
return false;
}
return false;
} public function delete_event($event_id){
$this->db->where("event_id", $event_id);
$update = $this->db->update("event", array("status" => 0, "back_office_status" => 6));
//update also all event schedule status to deleted
$this->db->where("event_id", $event_id);
$this->db->update("event_schedule", array("back_office_status" => 6));
return $update;
}
public function edit_display_status(){
$query = $this->db->select('*')
->from('event_schedule')
->where('back_office_status !=', 6)
->where('back_office_status !=', 7)
->where('back_office_status !=', 0)
->get();
$result = $query->result();
foreach($result as $row){
$this->db->where("event_schedule", $row->event_schedule);
if($row->back_office_status == 4) $this->db->update("event_schedule", array("display_status" => 0));
else $this->db->update("event_schedule", array("display_status" => 1));
}
}
public function check_bo_status($query_id, $page)
{
$this->load->model("event_schedule_model");
$queryData = [];
$where = " es.event_schedule_id = ".$this->db->escape($query_id);
array_push($queryData, $query_id);
$limit = " LIMIT 1 ";
if($page == 2){
$where = " es.event_id = ".$this->db->escape($query_id)." and e.event_id = ".$this->db->escape($query_id);
$limit = "";
} else {
$this->load->model('event_schedule_model');
}
$query = $this->db->query("select
es.event_id,
es.event_schedule_id,
es.seats_per_subscriber,
es.back_office_status,
es.event_status,
e.status,
es.event_url,
(CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE es.remaining_seat
END) as remaining_seat,
".$this->event_schedule_model->use_diffent_address()."
e.seat_feature,
e.date_feature,
e.event_category,
e.is_multiple_reservation,
e.is_multiple_waitlist_reservation,
e.is_favorite,
e.rate,
es.quota_waiting_list_seat,
e.use_different_address_per_date,
(
CASE
WHEN es.start_date_time >= NOW()
THEN 'coming_soon'
ELSE ''
END
) as is_coming_soon,
(
CASE
WHEN es.end_date_time IS NOT NULL
THEN NOW() >= es.end_date_time
ELSE NOW() >= es.start_date_time
END
) as is_passed,
(CASE
WHEN (es.back_office_status = 2 AND es.event_status ='AVAILABLE')
OR (es.back_office_status = 2 AND es.event_status ='FULL')
OR (es.back_office_status = 3 AND es.event_status ='FULL')
THEN CONCAT('a - ',es.start_date_time,' ')
WHEN es.back_office_status = 3 THEN CONCAT('b - ',es.start_date_time,' ')
WHEN es.back_office_status = 1 THEN CONCAT('c - ',es.start_date_time,' ')
WHEN es.back_office_status = 7 THEN CONCAT('d - ',es.start_date_time,' ')
WHEN es.back_office_status = 4 THEN CONCAT('e - ',es.start_date_time,' ')
ELSE CONCAT('f - ',es.start_date_time,' ')
END) as bostatus
".(($page==1 || $page==2)?$this->event_schedule_model->query_date():"")."
from event_schedule es
left join event e ON e.event_id = es.event_id
-- LEFT JOIN event_registration err ON err.event_schedule_id = es.event_schedule_id
-- LEFT JOIN event_wait_list erw ON erw.event_schedule_id = es.event_schedule_id
where ".$where."
ORDER BY
bostatus ASC,
es.start_date_time ASC
".$limit);
if(!$query->num_rows()){
return false;
} else {
return $query->result();
}
}
public function check_bo_status_mres($query_id, $page)
{
$this->load->model("event_schedule_model");
$where = " es.event_schedule_id = ".$query_id." ";
$limit = " LIMIT 1 ";
$this->load->model('event_schedule_model');
$query = $this->db->query("select es.event_id,
es.event_schedule_id,
e.title,
es.seats_per_subscriber,
es.back_office_status,
es.event_status,
e.status,
(CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE es.remaining_seat
END) as remaining_seat,
".$this->event_schedule_model->use_diffent_address()."
e.seat_feature,
e.date_feature,
e.event_category,
e.is_multiple_reservation,
e.is_multiple_waitlist_reservation,
e.is_favorite,
es.quota_waiting_list_seat,
(
CASE
WHEN es.start_date_time >= NOW()
THEN 'coming_soon'
ELSE ''
END
) as is_coming_soon,
(CASE
WHEN (es.back_office_status = 2 AND es.event_status ='AVAILABLE')
OR (es.back_office_status = 2 AND es.event_status ='FULL')
OR (es.back_office_status = 3 AND es.event_status ='FULL')
THEN CONCAT('a - ',es.start_date_time,' ')
WHEN es.back_office_status = 3 THEN CONCAT('b - ',es.start_date_time,' ')
WHEN es.back_office_status = 1 THEN CONCAT('c - ',es.start_date_time,' ')
WHEN es.back_office_status = 7 THEN CONCAT('d - ',es.start_date_time,' ')
WHEN es.back_office_status = 4 THEN CONCAT('e - ',es.start_date_time,' ')
ELSE CONCAT('f - ',es.start_date_time,' ')
END) as bostatus
".(($page==1 || $page==2)?$this->event_schedule_model->query_date():"").",
(CASE WHEN gsv.gm_mod_stat = 0 THEN 0 ELSE ges.mod_stat END) AS mod_stat
from event_schedule es
left join event e ON e.event_id = es.event_id
LEFT JOIN event_registration err ON err.event_schedule_id = es.event_schedule_id
LEFT JOIN event_wait_list erw ON erw.event_schedule_id = es.event_schedule_id
LEFT JOIN gm_evsched_setting ges ON (es.event_schedule_id = ges.event_schedule_id)
LEFT JOIN gm_settings_view gsv USING(gm_id)
where ".$where."
".$limit);
if(!$query->num_rows()){
return false;
} else {
return $query->result();
}
}
public function check_event_status($event_id)
{
$query = $this->db->select('status')
->from('event')
->where('event_id', $event_id)
->get()
->row();
return $query->event_status;
}
public function check_seats($event_id, $reg_type=1, $byPassRegularReservation=false, $session_type=null)
{
$query = $this->db->query("select
remaining_combined_seat as remaining_seat,
workshop_session,
night_class_prices,
event_status
from event
where back_office_status NOT IN(0,4,5,7,6)
and status = 1
and event_id=?
limit 1
", array($event_id))->row();
if(countVal($query) <=0 ){ return 0; }// Full
if($query->workshop_session == 'DISTANCE-PRESENTIEL') {
$ncp = json_decode($query->night_class_prices);
$rs = 0;
if($session_type == 'soir-presentiel')
$rs = $ncp->presentiel->total_available_seat - $this->count_total_reserved_seats($event_id, $session_type);
else if($session_type == 'soir-distance')
$rs = $ncp->distance->total_available_seat;
return $rs;
}
if($query->remaining_seat == 0 && $query->workshop_session != 'ENLIGNE'){ return 0; }// Full
else if(($query->remaining_seat > 0 && $reg_type == 1 && $query->event_status == 'AVAILABLE') || $query->workshop_session == 'ENLIGNE'){
return 1; // Book
} else {
if($reg_type == 2){
return 2; // Book in Waitlist
} else {
return ($byPassRegularReservation && $query->remaining_seat > 0)?2:0; // Full
}
}
return 0;
}
public function is_seats_available($event_schedule_id, $reg_type=1)
{
$query = $this->db->query("select
es.quota_waiting_list_seat,
es.remaining_seat
from event_schedule es
left join event e ON e.event_id = es.event_id
where es.event_schedule_id = ?
and e.status = 1
limit 1
", array($event_schedule_id))->row();
// return 1;
switch ($reg_type) {
case 1:
if ($query->remaining_seat > 0) {
return 1;
}
return 0;
break;
case 2:
if ($query->quota_waiting_list_seat > 0) {
return 1;
}
return 0;
break;
default:
break;
}
}
public function check_availability($event_id, $event_schedule_id, $seats_reserved, $reg_type, $byPassRegularReservation=false)
{
if($reg_type >= 3) {return 0;}
$row = $this->db->query("select
e.remaining_combined_seat as remaining_seat,
e.seat_feature,
e.is_multiple_reservation,.
e.is_multiple_waitlist_reservation,
e.back_office_status,
e.event_status
from event e
where e.back_office_status NOT IN(0,7,6)
and e.event_id = ?
limit 1 ", array($event_id))->row();
// Event is Closed
if(countVal($row) <= 0 || $row->back_office_status >= 4){
return 4;
}
// Event Registration disabled
else if( $row->remaining_seat <=0){
return 5;
}
// Allow Reserver for normal or regular reservation
else if($reg_type == 1 && $row->remaining_seat >= $seats_reserved && $row->remaining_seat > 0 && $seats_reserved > 0 && !$byPassRegularReservation){
return 1;
}
else if($reg_type == 1 && $row->remaining_seat <= 0 && !$byPassRegularReservation){
return 6; //register to regular/normal registration disabled : must register to waiting list
}
else if($reg_type == 2 && $row->remaining_seat > 0 && $row->event_status == 'AVAILABLE' && !$byPassRegularReservation){
return 3; //register to waiting list disabled : must register to regular/normal registration
}
// Allow Reserver or Book for waiting list
else if($reg_type == 2 && $seats_reserved > 0 && (!$byPassRegularReservation || $byPassRegularReservation)){
return 2;
} else {
return 0; //Event registration disabled
}
}
public function check_closed_event($event_schedule_id)
{
$query = $this->db->select('back_office_status')
->from('event_schedule')
->where('back_office_status !=', 6)
->where('back_office_status !=', 7)
->where('back_office_status !=', 0)
->where('event_status != \'FULL\'')
->where('event_schedule_id', $event_schedule_id)
->get()
->row();
if($query->back_office_status == 4) return 0; // Event Closed
else return 1; // Event Available
}
public function update_remaining_seats($event_schedule_id)
{
$query = $this->db->select('remaining_seat')
->from('event_schedule')
->where('back_office_status !=', 6)
->where('back_office_status !=', 7)
->where('back_office_status !=', 0)
->where('event_status != \'FULL\'')
->where('event_schedule_id', $event_schedule_id)
->get()
->row();
return $query->remaining_seat;
}
public function open_events_for_reservations(){
//select and insert to event_log table all the events which are about to be opened
$this->db->query(
"INSERT INTO user_activity_log(reference_id, description, action, table_origin)
(
SELECT e.event_id, 'Open event', 'AUTO OPEN EVENT FOR RESERVATION', 'event'
FROM event e
WHERE (CASE
WHEN e.event_category <> 'ONLINE_EVENT'
THEN e.reservation_start_date
ELSE e.start_date_time
END) <= NOW()
AND e.back_office_status IN(1)
)
");
$this->db->flush_cache();
$this->db->query("UPDATE event_schedule es
LEFT JOIN event e ON e.event_id = es.event_id
SET es.back_office_status = 2, es.event_status='AVAILABLE'
WHERE (CASE
WHEN e.event_category <> 'ONLINE_EVENT'
THEN es.reservation_start_date
ELSE es.start_date_time
END) <= NOW()
AND e.event_id = es.event_id
AND es.back_office_status IN(1)
");
$this->db->flush_cache();
$this->db->query("UPDATE event e
SET e.back_office_status = 2, e.event_status='AVAILABLE'
WHERE (CASE
WHEN e.event_category <> 'ONLINE_EVENT'
THEN e.reservation_start_date
ELSE e.start_date_time
END) <= NOW()
AND e.back_office_status IN(1)
"); // OPEN for Reservations
return $this->db->affected_rows();
}
public function terminate_events() {
//defer sending of onqueue emails
$this->defer_emails_and_sending_schedules();
//select and insert to event_log table all the events which are about to be terminated
$this->db->query(
"INSERT INTO user_activity_log(reference_id, description, action, table_origin)
(
SELECT e.event_id, 'Terminate event', 'TERMINATE EVENT', 'event'
FROM event e
WHERE (CASE
WHEN e.reservation_end_date IS NULL
THEN e.start_date_time
ELSE e.reservation_end_date
END) <= NOW()
AND e.event_category <> 'ONLINE_EVENT'
AND e.back_office_status IN(1,2,3)
)
");
$this->db->flush_cache();
$this->db->query("UPDATE event_schedule es
LEFT JOIN event e ON e.event_id = es.event_id
SET es.back_office_status = 7
WHERE (CASE
WHEN e.reservation_end_date IS NULL THEN e.start_date_time
ELSE e.reservation_end_date
END) <= NOW()
AND e.event_id = es.event_id
AND e.event_category <> 'ONLINE_EVENT'
AND es.back_office_status IN(1,2,3)"
);
$this->db->flush_cache();
//then, update their statuses
$this->db->query("UPDATE event e
SET e.back_office_status = 7
WHERE (CASE
WHEN e.reservation_end_date IS NULL THEN e.start_date_time
ELSE e.reservation_end_date
END) <= NOW()
AND e.event_category <> 'ONLINE_EVENT'
AND e.back_office_status IN(1,2,3)"
); //events terminated
return $this->db->affected_rows();
}
public function close_events() {
//defer sending of onqueue emails
$this->defer_emails_and_sending_schedules();
//select and insert to event_log table all the events which are about to be closed
$this->db->query("INSERT INTO user_activity_log(reference_id, description, action, table_origin)
SELECT event_id, 'Close event', 'AUTO CLOSE EVENT', 'event'
FROM event_schedule
WHERE (CASE
WHEN end_date_time IS NULL THEN DATE_FORMAT( DATE_ADD(start_date_time, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00')
ELSE DATE_ADD(end_date_time, INTERVAL 1 MINUTE)
END) <= NOW() AND back_office_status IN(1,2,3,7)");
$this->db->flush_cache();
$this->db->query(" UPDATE event_schedule
SET back_office_status = 4
WHERE (CASE
WHEN end_date_time IS NULL THEN DATE_FORMAT( DATE_ADD(start_date_time, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00')
ELSE DATE_ADD(end_date_time, INTERVAL 1 MINUTE)
END) <= NOW()
AND back_office_status IN(1,2,3,7)
");
$this->db->flush_cache();
//then, update their statuses
$this->db->query(" UPDATE event
SET back_office_status = 4
WHERE (CASE
WHEN end_date_time IS NULL THEN DATE_FORMAT( DATE_ADD(start_date_time, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00')
ELSE DATE_ADD(end_date_time, INTERVAL 1 MINUTE)
END) <= NOW()
AND back_office_status IN(1,2,3,7)
"); //events CLOSED
return $this->db->affected_rows();
}
private function defer_emails_and_sending_schedules(){
//defer sending of onqueue emails
$this->db->query("UPDATE event_email_recipient eer SET eer.email_status = 5, eer.email_date_time = NOW() WHERE eer.email_status IN(0,2,3)
AND eer.event_schedule_id IN(SELECT event_id FROM event WHERE start_date_time <= NOW() )");
$this->db->flush_cache();
//email sending schedules must be set to pass
$this->db->query("UPDATE event_email_schedule ees SET ees.email_schedule_status = 2, ees.email_schedule_date_added = NOW() WHERE ees.email_schedule_status IN(1)
AND ees.event_schedule_id IN(SELECT event_id FROM event
WHERE start_date_time <= NOW() )");
$this->db->flush_cache();
}
public function list_events_for_typeahead($search){
$this->db->select("event_id, title AS name")
->select("DATE_FORMAT(reservation_start_date, '%e/%m/%Y %Hh%m') AS reservation", FALSE);
$this->db->where_in("back_office_status",array(1,2,3,4,5));
$this->db->limit(10);
$this->db->like("title", $search);
$this->db->order_by("event_id", "desc");
return $this->db->get("event")->result();
}
public function reopen_event($event_schedule_id){
//insert to event log before reopening an event
$this->db->query("INSERT INTO user_activity_log(reference_id, description, action, table_origin) VALUES(?, ?, ?, ?)", array($event_schedule_id, 'Re-open event', 'RE-OPEN EVENT', 'event_schedule'));
//re-open an event
$this->db->where("event_schedule_id", $event_schedule_id);
$this->db->where("auto_event_status !=", 1);
$this->db->update("event_schedule", array("back_office_status" => 2));
}
public function get_events_for_waitinglist_email() { //get_events_with_free_places
$result = $this->db->query("SELECT
es.event_id, es.event_schedule_id, es.remaining_seat, es.start_date_time, es.end_date_time,
ewl.wait_list_id,
(
SELECT
ees.email_schedule_id
FROM event_email_schedule ees
LEFT JOIN `event_email_default_setting` eeds
ON `eeds`.`email_tpl_setting_id` = `ees`.`reference`
AND eeds.email_type_id = 2
WHERE ees.event_schedule_id = ewl.event_schedule_id
AND ees.email_schedule_status=1
AND eeds.email_tpl_setting_status= 1
ORDER BY ees.email_schedule_id DESC LIMIT 1
) AS email_schedule_id
FROM event_wait_list ewl
LEFT JOIN event_schedule es ON es.event_schedule_id = ewl.event_schedule_id
LEFT JOIN event e ON e.event_id = es.event_id
WHERE (CASE
WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
ELSE es.remaining_seat
END) > 0
AND es.event_status = 'AVAILABLE'
AND e.status = 1
AND ewl.status = 1
AND ewl.isModeratedButAllowedInWL = 0
AND es.back_office_status IN (2, 3)
AND es.start_date_time > NOW()
AND ewl.event_schedule_id IN(
select eses.event_schedule_id
from event_schedule_email_status eses
where eses.event_schedule_id = ewl.event_schedule_id
and eses.email_type_id = 2
and eses.status = 1
)
AND ewl.wait_list_id NOT IN(SELECT eer.reference_id
FROM event_email_recipient eer
WHERE
eer.event_schedule_id = ewl.event_schedule_id
AND eer.email_status != 5
AND eer.reference_id IS NOT NULL
AND eer.email_type_id = 2
GROUP BY eer.reference_id)
GROUP BY ewl.event_schedule_id
ORDER BY ewl.wait_list_id DESC
")->result();
return $result;
}
public function get_events_for_reminder_email() {
return $this->db->query("SELECT
e.event_id, e.start_date_time
FROM event_registration er
LEFT JOIN event e ON e.event_id = er.event_id AND e.status = 1
WHERE er.status = 1
AND e.back_office_status IN (2, 3)
AND e.start_date_time > NOW()
AND e.status = 1
AND er.event_id IN(
SELECT eses.event_id
FROM event_schedule_email_status eses
WHERE eses.event_id = er.event_id
AND eses.email_type_id = 3
AND eses.status = 1
)
AND e.event_status !='CANCEL'
AND er.registration_id
NOT IN(SELECT eer.reference_id
FROM event_email_recipient eer
WHERE eer.event_id = er.event_id
AND eer.email_status NOT IN (1, 5)
AND eer.reference_id IS NOT NULL
AND eer.email_type_id = 3
)
AND (
CASE
WHEN (SELECT COUNT(ees.email_schedule_id)
FROM event_email_schedule ees
LEFT JOIN event_email_default_setting eeds
ON ees.reference = eeds.email_tpl_setting_id
AND eeds.email_type_id = 3
WHERE eeds.email_type_id = 3
AND ees.reference = eeds.email_tpl_setting_id
AND ees.email_schedule_status = 1
AND ees.event_id = er.event_id ) > 0
THEN
(CASE
WHEN (
SELECT COUNT(ees.email_schedule_id)
FROM event_email_schedule ees
LEFT JOIN event_email_default_setting eeds
ON ees.reference = eeds.email_tpl_setting_id
AND eeds.email_type_id = 3
WHERE eeds.email_type_id = 3
AND ees.reference = eeds.email_tpl_setting_id
AND ees.email_schedule_status = 1
AND ees.event_id = er.event_id
AND ees.email_schedule_id NOT IN (
SELECT eerod.email_sched_reference_id
FROM event_email_recipient eer
LEFT JOIN event_email_recipient_other_detail eerod
ON eerod.email_recipient_id = eer.email_recipient_id
AND eerod.email_sched_reference = 1
WHERE eer.event_id = er.event_id
AND eer.email_type_id = 3
AND eer.email_status != 5
AND eerod.email_recipient_id = eer.email_recipient_id
AND eerod.email_sched_reference = 1
)
) >0
THEN 1
ELSE 0
END)
WHEN (
SELECT COUNT(eeds.email_tpl_setting_id)
FROM event_email_default_setting eeds
WHERE eeds.email_type_id = 3
AND eeds.email_tpl_setting_status = 1
AND eeds.email_tpl_setting_id NOT IN (
SELECT eerod.email_sched_reference_id
FROM event_email_recipient eer
LEFT JOIN event_email_recipient_other_detail eerod
ON eerod.email_recipient_id = eer.email_recipient_id
AND eerod.email_sched_reference = 2
WHERE eer.event_id = er.event_id
AND eer.email_type_id = 3
AND eer.email_status != 5
AND eerod.email_recipient_id = eer.email_recipient_id
AND eerod.email_sched_reference = 2
)
) > 0
THEN 1
ELSE 0
END
) > 0
GROUP BY er.event_id
ORDER BY er.registration_id DESC
")->result();
}
public function get_event_detail_for_test_email($event_schedule_id,$email_type_id){
$this->load->model("event_schedule_model");
$result = $this->db->select("
es.event_schedule_id,
e.event_id,
e.title as event_title,
e.workshop_author,
et.event_type,
e.event_status,
".$this->event_schedule_model->use_diffent_address()."
(CASE WHEN e.rate > 0 THEN e.rate ELSE \"\" END) as event_rate,
efa.file_name as event_picture,
e.description as event_description,
e.start_date_time as event_start_date,
(CASE WHEN DATE_FORMAT(e.start_date_time, '%Hh%i') = '00h00' THEN \"\" ELSE DATE_FORMAT(e.start_date_time, '%Hh%i') END) as event_start_hour,
(CASE WHEN eeds.email_tpl_setting_sched = 0 THEN \"\" ELSE eeds.email_tpl_setting_sched END) as email_tpl_setting_sched,
(CASE WHEN eeds.email_tpl_setting_sched_by = 0 THEN \"\" ELSE eeds.email_tpl_setting_sched_by END) as email_tpl_setting_sched_by ")
->select("DAYNAME(e.start_date_time) AS event_start_day_name", FALSE)
->select("DATE_FORMAT(e.start_date_time, '%e') AS event_start_day", FALSE)
->select("DATE_FORMAT(e.start_date_time, '%m') AS event_start_month", FALSE)
->select("DATE_FORMAT(e.start_date_time, '%Y') AS event_start_year", FALSE)
->select("DATE_FORMAT(e.start_date_time, '%M') AS event_start_month_name", FALSE)
->select("DATE_FORMAT(e.start_date_time, '%Hh%i') AS start_date_hour", FALSE)
->select("DATE_FORMAT(e.start_date_time, '%Hh%i') AS commencement_de_lheure", FALSE)
->select("e.start_date_time AS date_complete", FALSE)
->from("event_schedule es")
->join("event e", "e.event_id = es.event_id", "left")
->join('event_type et', 'et.event_type_id = e.event_type_id',"left")
->join('event_file_attachment efa', 'efa.event_id = e.event_id AND efa.status=1 AND efa.attachment_type =1', "left")
->join("event_email_default_setting eeds", "eeds.email_type_id = $email_type_id AND eeds.email_tpl_setting_status=1", "left")
->where("es.event_schedule_id", $event_schedule_id)
->get();
if($result->num_rows() > 0){
return $result->row();
}
return false;
}
public function check_all_sched($event_id, $isOpen=true) {
// Tells whether there's an event open or all are closed
$notIn = $isOpen ? '4,5,6' : '5,6';
if ( !$event_id ) {
return [];
}
return $this->db->query("SELECT es.event_id as eventID,
(SELECT @min_date := MIN(start_date_time) from event_schedule where event_id = ? AND back_office_status NOT IN (?)) AS mid_date,
(SELECT
GROUP_CONCAT(CONCAT
('{\"event_schedule_id\":\"', a.event_schedule_id,
'\", \"end_date\":\"', {$this->prepare_query_to_get_end_date("a.end_date_time", "a.start_date_time")},
'\", \"max_end_date\":\"', {$this->prepare_query_to_get_end_date("a.end_date_time", "a.start_date_time")},
'\", \"event_enddate\":\"', {$this->prepare_query_to_get_end_date("DATE(a.end_date_time)", "DATE(a.start_date_time)")},
'\", \"max_end_date_day\":\"', {$this->prepare_query_to_get_end_date("DAYNAME(a.end_date_time)", "DAYNAME(a.start_date_time)")},
'\", \"max_mdate_date\":\"', {$this->prepare_query_to_get_end_date("DAY(a.end_date_time)", "DAY(a.start_date_time)")},
'\", \"max_mdate_month\":\"', {$this->prepare_query_to_get_end_date("MONTHNAME(a.end_date_time)", "MONTHNAME(a.start_date_time)")},
'\", \"max_mdate_year\":\"', {$this->prepare_query_to_get_end_date("YEAR(a.end_date_time)", "YEAR(a.start_date_time)")},
'\", \"end_mdate_hour\":\"', (CASE
WHEN a.end_date_time IS NOT NULL
AND DATE_FORMAT(a.end_date_time, '%Hh%i') != '00h00'
AND a.end_date_time != @min_date
THEN DATE_FORMAT(a.end_date_time, 'à %Hh%i')
WHEN a.start_date_time IS NOT NULL
AND DATE_FORMAT(a.start_date_time, '%Hh%i') != '00h00'
AND a.start_date_time != @min_date
THEN DATE_FORMAT(a.start_date_time, 'à %Hh%i')
ELSE \"\"
END),
'\"}'
) ORDER BY a.start_date_time DESC, a.event_schedule_id DESC
SEPARATOR ',') as child_events
FROM event_schedule a
WHERE a.event_id = ?
AND a.back_office_status NOT IN (?)
) AS event_end_date,
MIN(es.start_date_time) as start_date,
DATE(min(es.start_date_time)) as event_startdate,
DAYNAME(min(es.start_date_time)) as min_start_day,
DAY(min(es.start_date_time)) as min_start_date,
MONTHNAME(min(es.start_date_time)) as min_start_date_month,
YEAR(min(es.start_date_time)) as min_date_year,
MAX(es.start_date_time) as end_date,
DAYNAME(max(es.start_date_time)) as end_sdate_day,
DAY(max(es.start_date_time)) as end_sdate_date,
MONTHNAME(max(es.start_date_time)) as max_sdate_month,
YEAR(max(es.start_date_time)) as max_sdate_year,
(CASE
WHEN DATE_FORMAT(min(es.start_date_time), '%Hh%i') = '00h00' THEN \"\"
ELSE DATE_FORMAT(min(es.start_date_time), 'de %Hh%i')
END) AS start_date_hour,
(CASE
WHEN DATE_FORMAT(min(es.start_date_time), '%Hh%i') = '00h00' THEN \"\"
ELSE DATE_FORMAT(min(es.start_date_time), 'à %Hh%i')
END) AS min_start_date_hour,
(CASE
WHEN DATE_FORMAT(max(es.start_date_time), '%Hh%i') = '00h00' THEN \"\"
ELSE DATE_FORMAT(max(es.start_date_time), 'à %Hh%i')
END) AS max_end_sdate_hour
FROM event_schedule es
WHERE es.event_id = ?
AND es.back_office_status NOT IN (?)
", array($event_id, $notIn, $event_id, $notIn, $event_id, $notIn))->result();
}
private function prepare_query_to_get_end_date($date1, $date2) {
return "(CASE
WHEN
(
a.end_date_time IS NOT NULL
AND DATE_FORMAT(a.end_date_time, '%Y-%m-%d') = DATE_FORMAT(@min_date, '%Y-%m-%d')
)
OR
(
a.end_date_time IS NULL
AND DATE_FORMAT(a.start_date_time, '%Y-%m-%d') = DATE_FORMAT(@min_date, '%Y-%m-%d')
)
THEN \"\"
WHEN
a.end_date_time IS NOT NULL
AND DATE_FORMAT(a.end_date_time, '%Y-%m-%d') != DATE_FORMAT(@min_date, '%Y-%m-%d')
THEN {$date1}
WHEN
a.end_date_time IS NULL
AND DATE_FORMAT(a.start_date_time, '%Y-%m-%d') != DATE_FORMAT(@min_date, '%Y-%m-%d')
THEN {$date2}
ELSE \"\"
END)";
}
public function count_workshop($filter, $type, $user_id) {
$query = $this->_get_filter_query($user_id);
$query .= $this->_get_filter_type($filter, NULL, $type, NULL);
// $query .= ' GROUP BY e.event_id ORDER BY bostatus ASC';
$query .= ' GROUP BY e.event_id ORDER BY
SUBSTRING_INDEX(bostatus,"_",1) ASC,
CASE WHEN SUBSTRING_INDEX(bostatus,"_",1) NOT IN("d") THEN SUBSTRING_INDEX(SUBSTRING_INDEX(bostatus,"_",2),"_",-1) END ASC,
CASE WHEN SUBSTRING_INDEX(bostatus,"_",1) IN("d") THEN SUBSTRING_INDEX(SUBSTRING_INDEX(bostatus,"_",2),"_",-1) END DESC';
//$query .= ' LIMIT '.(($pages > 0)?$pages:0).', '.HP_EVENT_DISPLAY['perPage'].' ';
$events = $this->db->query($query);
return $events->num_rows();
}
public function get_file_attachments($event_id) {
$this->db->select('file_name, display');
$this->db->where('status', 1);
$this->db->where('event_id', $event_id);
return $this->db->get('event_file_attachment')->result_array();
}
public function event_details_reserved($event_id, $is_video = false)
{
$this->db->select("e.event_id, e.title AS event_title,
e.description,
e.rate as event_rate,
e.seat_feature,
e.date_feature,
e.workshop_author,
e.author_label,
e.workshop_author_description,
e.hours_per_session,
e.total_available_seat,
e.reservation_start_date,
e.reservation_end_date,
e.start_date_time,
e.workshop_session,
e.event_category,
e.end_date_time,
e.event_status");
$this->db->select("DAYNAME(e.start_date_time) AS event_start_day_name", FALSE);
$this->db->select("DATE_FORMAT(e.start_date_time, '%e') AS event_start_day", FALSE);
$this->db->select("DATE_FORMAT(e.start_date_time, '%m') AS event_start_month", FALSE);
$this->db->select("DATE_FORMAT(e.start_date_time, '%M') AS event_start_month_name", FALSE);
$this->db->select("DATE_FORMAT(e.start_date_time, '%Y') AS event_start_year", FALSE);
$this->db->select("DATE_FORMAT(e.start_date_time, '%Hh%i') AS start_date_hour", FALSE);
$this->db->select("DATE_FORMAT(e.start_date_time, '%Hh%i') AS commencement_de_lheure", FALSE);
$this->db->select("e.start_date_time AS date_complete", FALSE);
$this->db->from('event e');
$this->db->where('e.event_id', $event_id);
return $this->db->get()->row();
}
public function similar_events($category = 0, $event_id = null) {
$this->db->select('event_id, title, workshop_author, status');
if($category != 0) {
$this->db->where('event_type_id', $category);
}
if($event_id != null) {
$this->db->where('event_id !=', $event_id);
}
$this->db->where_in('back_office_status', [1,2,3,4]);
$this->db->where('end_date_time >=',date('Y-m-d'));
$this->db->where('status', 1);
$this->db->where('remaining_combined_seat >', 0);
$this->db->or_where('workshop_session','ENLIGNE');
$events = $this->db->get('event')->result();
$similar_events= array();
if($events) {
foreach($events as $event) {
if($event->status == 1) {
array_push($similar_events, (array) $event);
}
}
}
return $similar_events;
}
public function get_past_events() {
$this->db->select('event_id, title, workshop_author, status');
$this->db->where_not_in('back_office_status', [0,6,7]);
$this->db->where('end_date_time <',date('Y-m-d'));
$this->db->where('status', 1);
$this->db->where_not_in('workshop_session', ['ENLIGNE']);
$events = $this->db->get('event')->result();
return $events;
}
public function get_selected_past_events() {
$this->db->select('*');
$events = $this->db->get('event_past_featured')->row();
$event_details = array();
$ids = array();
if($events && $events->events != 'null') {
$ids = json_decode($events->events);
foreach($ids as $id) {
$details = $this->event_details($id);
array_push($event_details, $details);
}
}
return $event_details;
}
public function add_selected_past_events(array $data) {
$this->db->select('*');
$past_events = $this->db->get('event_past_featured')->row();
if($past_events) {
/** update featured past events */
$event_data["events"] = $data['events'];
$this->db->where("id", $past_events->id);
return $this->db->update("event_past_featured", $event_data);
} else {
/** add featured past events */
$event = array('events' => $data['events']);
$this->db->insert("event_past_featured", $event);
return $this->db->insert_id();
}
return $past_events;
}
public function get_similar_events($events) {
$similar_events= array();
if($events) {
foreach($events as $event) {
$event_details = $this->event_details($event);
if($event_details->back_office_status != 6 && $event_details->back_office_status != null) {
$event_details->hours_per_session=$this->formatHour($event_details->hours_per_session);
array_push($similar_events, (array) $event_details);
}
}
}
return $similar_events;
}
private function formatHour($time) {
$hour = explode('.', $time);
$minute = '';
if(count($hour)>1) {
$minute = $this->formatMinute($hour[1]);
}
return $hour[0].'h'.$minute;
}
private function formatMinute($minute=null) {
$m = '';
if($minute != null) {
$minute = '.'.$minute;
$m = (float)$minute*60;
}
return $m;
}
public function get_online_video() {
$result = $this->db->select('event_id, workshop_session')
->from('event')
->where('workshop_session', 'ENLIGNE')
->where('status', 1);
return $result->get()->result();
}
public function format_night_class_prices($event) {
$ncp = json_decode($event->night_class_prices);
$presentiel_available_seats = $ncp->presentiel->total_available_seat - $this->count_total_reserved_seats($event->event_id, 'soir-presentiel');
$prices = [
"presentiel" => [
"p_rate" => $ncp->presentiel->rate,
"p_total_available_seat" => $ncp->presentiel->total_available_seat,
"p_discounted_price" => $ncp->presentiel->discounted_price,
"p_discount_apply" => $ncp->presentiel->discount_apply,
"p_remaining_combined_seat" => $presentiel_available_seats
],
"distance" => [
"d_rate" => $ncp->distance->rate,
"d_total_available_seat" => $ncp->distance->total_available_seat != '' ? $ncp->distance->total_available_seat : 0,
"d_discounted_price" => $ncp->distance->discounted_price,
"d_discount_apply" => $ncp->distance->discount_apply,
"d_remaining_combined_seat" => ($ncp->distance->total_available_seat != '' ? $ncp->distance->total_available_seat : 0) - $this->count_total_reserved_seats($event->event_id, 'soir-distance')
]
];
$event->remaining_combined_seat = $presentiel_available_seats;
return array_merge((array)$event, $prices);
}
public function count_total_reserved_seats($event_id, $registration_type) {
$this->db->select_sum('number_of_guest');
$this->db->from('event_registration');
$this->db->where('event_id', $event_id);
$this->db->where('registration_type', $registration_type);
$this->db->where('status', 1);
$this->db->count_all();
$query=$this->db->get();
if(!empty($query->row()->number_of_guest))
return $query->row()->number_of_guest;
else
return 0;
}
}