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.
1120 lines
39 KiB
1120 lines
39 KiB
<?php
|
|
if (! defined('BASEPATH')) {
|
|
exit('No direct script access allowed');
|
|
}
|
|
|
|
class Event_moderation_model extends CI_Model
|
|
{
|
|
/**
|
|
* Actual settings for moderation (without condition)
|
|
* Used in BO moderation settings
|
|
*/
|
|
protected const MOD_TBL = 'global_moderation';
|
|
/**
|
|
* Settings for moderation (with validation)
|
|
* Used in conditions/validations
|
|
*/
|
|
protected const MOD_VIEW_TBL = 'gm_settings_view';
|
|
/**
|
|
* Subettings for moderation (without validation)
|
|
* Used in BO moderation settings
|
|
*/
|
|
protected const MOD_SUB_VIEW_BO_TBL = 'gm_raw_subsettings_view_for_bo';
|
|
/**
|
|
* Subettings for moderation (with validation)
|
|
* Used in conditions/validations
|
|
*/
|
|
protected const MOD_SUB_VIEW_TBL = 'gm_subsettings_view';
|
|
|
|
/**
|
|
* Table that contains the setting per event schedule
|
|
* @var string
|
|
*/
|
|
protected const EVSCHED_MOD_TBL = 'gm_evsched_setting';
|
|
/**
|
|
* Table that contains events affected by current moderation settings
|
|
* @var string
|
|
*/
|
|
protected const EVSCHED_AFFECTED_BY_CURMOD = 'gm_evscheds_affected_by_latest_mod_settings_view';
|
|
/**
|
|
* Table for the sub settings
|
|
* @var string
|
|
*/
|
|
protected const GM_NSHOW_PSUBS_TBL = 'gm_nshow_psubs';
|
|
protected const GM_RES_PSUBS_TBL = 'gm_res_psubs';
|
|
protected const GM_EV_CAT_TBL = 'gm_event_cat';
|
|
protected const GM_EV_LOC_TBL = 'gm_event_loc';
|
|
protected const GM_EV_MON_TBL = 'gm_event_month';
|
|
|
|
protected const GM_FLEX_MOD_TBL = 'gm_flex_mod';
|
|
|
|
/**
|
|
* Initialize this Class; call Parent Class construct
|
|
* @return void
|
|
*
|
|
* access public member
|
|
*/
|
|
public function __construct()
|
|
{
|
|
parent::__construct();
|
|
}
|
|
|
|
/**
|
|
*
|
|
* get old settings
|
|
* @access public
|
|
* @param int used(1) | unused(0) | either (3)
|
|
* @param int 1:get actual settings (without validation) or 0: not
|
|
* @return array
|
|
*
|
|
*/
|
|
public function getLatestModSettings(int $is_used = 3, $get_actual = 1)
|
|
{
|
|
// insert visitors report data
|
|
try {
|
|
$data = array(
|
|
$is_used
|
|
);
|
|
if ($is_used == 3) {
|
|
$_where = '';
|
|
} else {
|
|
$_where = 'WHERE is_used = ?';
|
|
}
|
|
if ($get_actual) {
|
|
$query = "SELECT *,
|
|
CASE WHEN gm_id IN((
|
|
SELECT evmod.gm_id
|
|
FROM gm_evsched_setting evmod
|
|
WHERE (evmod.gm_id = gm.gm_id))) THEN 1
|
|
ELSE 0 END AS is_used
|
|
FROM ".(self::MOD_TBL)." gm ORDER BY gm_id DESC LIMIT 1";
|
|
} else {
|
|
$query = "SELECT * FROM ".(self::MOD_VIEW_TBL)." $_where ORDER BY gm_id DESC LIMIT 1";
|
|
}
|
|
|
|
$settings_data = ($this->db->query($query, $data));
|
|
if ($settings_data->num_rows() > 0) {
|
|
return $settings_data->row_array();
|
|
} else {
|
|
return array();
|
|
}
|
|
} catch (\Exception $e) {
|
|
throw new Exception('Cannot get current settings');
|
|
}
|
|
}
|
|
/**
|
|
*
|
|
* get old sub settings
|
|
* @access public
|
|
* @param int id of the current moderation setting
|
|
* @param int 1:get actual settings (without validation) or 0: not
|
|
* @return array
|
|
*
|
|
*/
|
|
public function getSubModSettings(int $gm_id, $get_actual = 1)
|
|
{
|
|
try {
|
|
$data = array(
|
|
$gm_id
|
|
);
|
|
/*
|
|
* actual settings are used in BO while the other is used for validation
|
|
* since there are conditions applied in the query
|
|
*/
|
|
if ($get_actual) {
|
|
$query = "SELECT * FROM ".(self::MOD_SUB_VIEW_BO_TBL)." WHERE gm_id = ?";
|
|
} else {
|
|
$query = "SELECT * FROM ".(self::MOD_SUB_VIEW_TBL)." WHERE gm_id = ?";
|
|
}
|
|
|
|
$settings_data = ($this->db->query($query, $data));
|
|
if ($settings_data->num_rows() > 0) {
|
|
return $settings_data->row_array();
|
|
} else {
|
|
return array();
|
|
}
|
|
} catch (\Exception $e) {
|
|
throw new Exception('Cannot get current sub settings');
|
|
}
|
|
}
|
|
/**
|
|
*
|
|
* save settings
|
|
* @access public
|
|
* @param int data settings data
|
|
* @param int action add | update
|
|
* @return array
|
|
*
|
|
*/
|
|
public function saveSettings($data, $action = 'add')
|
|
{
|
|
try {
|
|
switch ($action) {
|
|
case 'add':
|
|
/*
|
|
* remove gm_id before inserting
|
|
*/
|
|
unset($data['gm_id']);
|
|
/*
|
|
* Insert new settings
|
|
*/
|
|
$this->db->insert(self::MOD_TBL, $data);
|
|
return $this->db->insert_id();
|
|
|
|
break;
|
|
|
|
default:
|
|
/*
|
|
* get gm_id and remove it from $data variable then update
|
|
*/
|
|
$gm_id = $data['gm_id'];
|
|
unset($data['gm_id']);
|
|
|
|
/*
|
|
* deactivate all moderation settings if one is disabled
|
|
*/
|
|
if ($data['gm_mod_stat'] == 0) {
|
|
$d_query = "UPDATE global_moderation SET gm_mod_stat = 0";
|
|
$this->db->query($d_query);
|
|
}
|
|
if ($data['gm_mod_stat'] == 1) {
|
|
$d_query = "UPDATE global_moderation SET gm_mod_stat = 1";
|
|
$this->db->query($d_query);
|
|
}
|
|
|
|
|
|
$this->db->where('gm_id', $gm_id);
|
|
return $this->db->update(self::MOD_TBL, $data);
|
|
break;
|
|
}
|
|
// return $this->db->last_query();
|
|
} catch (\Exception $e) {
|
|
throw new Exception('Cannot save settings');
|
|
}
|
|
}
|
|
/**
|
|
*
|
|
* save sub settings
|
|
* @access public
|
|
* @param int data sub settings data
|
|
* @param int action add | update
|
|
* @return array
|
|
*
|
|
*/
|
|
public function saveSubSettings($sub_data, $action = 'add')
|
|
{
|
|
try {
|
|
switch ($action) {
|
|
case 'add':
|
|
switch ($sub_data['type']) {
|
|
case 'noshow_psub_sub':
|
|
$table = self::GM_NSHOW_PSUBS_TBL;
|
|
break;
|
|
case 'reservation_psub':
|
|
$table = self::GM_RES_PSUBS_TBL;
|
|
break;
|
|
case 'event_category':
|
|
$table = self::GM_EV_CAT_TBL;
|
|
$main_data_col = "event_cat";
|
|
break;
|
|
case 'event_location':
|
|
$table = self::GM_EV_LOC_TBL;
|
|
$main_data_col = "event_location";
|
|
break;
|
|
case 'event_month':
|
|
$table = self::GM_EV_MON_TBL;
|
|
$main_data_col = "event_month";
|
|
break;
|
|
case 'flex_moderation':
|
|
$table = self::GM_FLEX_MOD_TBL;
|
|
break;
|
|
|
|
default:
|
|
$table = '';
|
|
break;
|
|
}
|
|
$gm_id = $sub_data['gm_id'];
|
|
/*
|
|
* save data in array form
|
|
*/
|
|
if (empty($table)) {
|
|
return false;
|
|
}
|
|
switch ($sub_data['type']) {
|
|
case 'event_category':
|
|
case 'event_location':
|
|
case 'event_month':
|
|
/*
|
|
* Delete all current data
|
|
*/
|
|
$delete = $data_exists = $this->db->query(
|
|
"DELETE FROM $table WHERE gm_id = ?",
|
|
array($gm_id)
|
|
);
|
|
/*
|
|
* insertt!
|
|
*/
|
|
$data_list = explode(',', $sub_data[$main_data_col]);
|
|
foreach ($data_list as $key => $value) {
|
|
if (!empty($value)) {
|
|
$sub_d = array(
|
|
"gm_id" => $sub_data['gm_id'],
|
|
$main_data_col => $value
|
|
);
|
|
$this->db->insert($table, $sub_d);
|
|
}
|
|
}
|
|
return true;
|
|
break;
|
|
|
|
default:
|
|
unset($sub_data['type']);
|
|
/*
|
|
* Insert new settings
|
|
*/
|
|
if (!empty($table)) {
|
|
$this->db->insert($table, $sub_data);
|
|
return true;
|
|
} else {
|
|
return false;
|
|
}
|
|
break;
|
|
}
|
|
break;
|
|
|
|
default:
|
|
switch ($sub_data['type']) {
|
|
case 'noshow_psub_sub':
|
|
$table = self::GM_NSHOW_PSUBS_TBL;
|
|
break;
|
|
break;
|
|
case 'reservation_psub':
|
|
$table = self::GM_RES_PSUBS_TBL;
|
|
break;
|
|
case 'event_category':
|
|
$table = self::GM_EV_CAT_TBL;
|
|
$main_data_col = "event_cat";
|
|
break;
|
|
case 'event_location':
|
|
$table = self::GM_EV_LOC_TBL;
|
|
$main_data_col = "event_location";
|
|
break;
|
|
case 'event_month':
|
|
$table = self::GM_EV_MON_TBL;
|
|
$main_data_col = "event_month";
|
|
break;
|
|
case 'flex_moderation':
|
|
$table = self::GM_FLEX_MOD_TBL;
|
|
break;
|
|
default:
|
|
$table = '';
|
|
break;
|
|
}
|
|
$gm_id = $sub_data['gm_id'];
|
|
|
|
/*
|
|
* save data in array form
|
|
*/
|
|
if (empty($table)) {
|
|
return false;
|
|
}
|
|
switch ($sub_data['type']) {
|
|
case 'event_category':
|
|
case 'event_location':
|
|
case 'event_month':
|
|
/*
|
|
* Delete all current data
|
|
*/
|
|
$delete = $data_exists = $this->db->query(
|
|
"DELETE FROM $table WHERE gm_id = ?",
|
|
array($gm_id)
|
|
);
|
|
/*
|
|
* insertt!
|
|
*/
|
|
$data_list = explode(',', $sub_data[$main_data_col]);
|
|
foreach ($data_list as $key => $value) {
|
|
if (!empty($value)) {
|
|
$sub_d = array(
|
|
"gm_id" => $sub_data['gm_id'],
|
|
$main_data_col => $value
|
|
);
|
|
$this->db->insert($table, $sub_d);
|
|
}
|
|
}
|
|
return true;
|
|
break;
|
|
|
|
default:
|
|
/*
|
|
* check if there is current data
|
|
*/
|
|
$data_exists = $this->db->query(
|
|
"SELECT gm_id FROM $table WHERE gm_id = ?",
|
|
array($gm_id)
|
|
);
|
|
/*
|
|
* update if data exists, insert if no settings are present
|
|
*/
|
|
unset($sub_data['type']);
|
|
if ($data_exists->num_rows() >0) {
|
|
unset($sub_data['gm_id']);
|
|
$this->db->where('gm_id', $gm_id);
|
|
$this->db->update($table, $sub_data);
|
|
return true;
|
|
} else {
|
|
$this->db->insert($table, $sub_data);
|
|
return true;
|
|
}
|
|
break;
|
|
}
|
|
|
|
break;
|
|
}
|
|
// return $this->db->last_query();
|
|
} catch (\Exception $e) {
|
|
throw new Exception('Cannot save settings');
|
|
}
|
|
}
|
|
|
|
/**
|
|
*
|
|
* @method evsched mod settings
|
|
* @access public
|
|
* @param int event_schedule_id
|
|
* @return array
|
|
*
|
|
*/
|
|
public function getEvSchedModSettings(int $event_schedule_id)
|
|
{
|
|
try {
|
|
$settings = $this->db->query(
|
|
"SELECT gmevsched.*,
|
|
gsv.gm_mod_stat, gsv.noshow_psub_stat, gsv.res_psub_stat,
|
|
gsv.event_cat_stat, gsv.event_loc_stat, gsv.event_month_stat, gsv.flex_mod_stat gm_flex_mod_stat,
|
|
gsv.date_created gm_date_created, gsv.is_used,
|
|
gsubv.max_noshow, gsubv.nshow_period,
|
|
gsubv.max_res, gsubv.res_period_type FROM ".(self::EVSCHED_MOD_TBL)." gmevsched
|
|
INNER JOIN gm_settings_view gsv USING (gm_id)
|
|
INNER JOIN gm_subsettings_view gsubv USING (gm_id)
|
|
WHERE event_schedule_id = ?",
|
|
array(
|
|
$event_schedule_id
|
|
)
|
|
);
|
|
|
|
if ($settings->num_rows() > 0) {
|
|
return $settings->row_array();
|
|
}
|
|
return array();
|
|
} catch (\Exception $e) {
|
|
throw new Exception('No settings detected');
|
|
}
|
|
}
|
|
/**
|
|
*
|
|
* @method check if event schedule qualifies in event moderation
|
|
* @access public
|
|
* @param int event_schedule_id
|
|
* @return array
|
|
*
|
|
*/
|
|
public function checkIfEventQualifiesInModeration(int $event_schedule_id)
|
|
{
|
|
try {
|
|
$settings = $this->db->query(
|
|
"SELECT * FROM ".(self::EVSCHED_AFFECTED_BY_CURMOD)." WHERE event_schedule_id = ?",
|
|
array(
|
|
$event_schedule_id
|
|
)
|
|
);
|
|
|
|
if ($settings->num_rows() > 0) {
|
|
return $settings->row_array();
|
|
}
|
|
return array();
|
|
} catch (\Exception $e) {
|
|
throw new Exception('No settings detected');
|
|
}
|
|
}
|
|
|
|
/**
|
|
*
|
|
* @method Save moderation settings per event schedule
|
|
* @access public
|
|
*
|
|
* @param int event schedule_id
|
|
* @param array evsched moderation settings
|
|
* @return array
|
|
*
|
|
*/
|
|
public function saveEvSchedModSettings($event_schedule_id, $data)
|
|
{
|
|
try {
|
|
$cur_sett = $this->getLatestModSettings(3, 0);
|
|
|
|
// do not save moderation setting if moderation is turned off
|
|
if ( !count($cur_sett) || $cur_sett['gm_mod_stat'] == 0) {
|
|
return false;
|
|
}
|
|
/*
|
|
* check if there is current data
|
|
*/
|
|
$old_data = $this->getEvSchedModSettings($event_schedule_id);
|
|
/*
|
|
* update if event schedule id is currently linked to a mod setting
|
|
* insert new setting otherwise using the latest gm_id
|
|
*/
|
|
|
|
// TODO: add option to whether adapt current moderation settings when saving
|
|
// $data['adapt_cur_mod_settings'];
|
|
|
|
$evsched_data = array(
|
|
"event_schedule_id" => $event_schedule_id,
|
|
"gm_id" => $cur_sett['gm_id'],
|
|
"mod_stat" => ($data['mod_stat']) ?? 0,
|
|
"flex_mod_stat" => ($data['flex_mod_stat']) ?? 0,
|
|
"flex_mod_applied" => ($data['flex_mod_applied']) ?? 0,
|
|
"hrs_bef_event_closes" => ($data['hrs_bef_event_closes']) ?? 0,
|
|
"places_volume" => ($data['places_volume']) ?? 0,
|
|
);
|
|
if (count($old_data) > 0) {
|
|
unset($evsched_data['gm_id']);
|
|
$gm_id = $old_data['gm_id'];
|
|
$this->db->where('gm_id', $gm_id);
|
|
$this->db->where('event_schedule_id', $event_schedule_id);
|
|
$this->db->update(self::EVSCHED_MOD_TBL, $evsched_data);
|
|
return true;
|
|
} else {
|
|
$this->db->insert(self::EVSCHED_MOD_TBL, $evsched_data);
|
|
return true;
|
|
}
|
|
} catch (\Exception $e) {
|
|
throw new Exception('Cannot update settings');
|
|
output_to_json($this, array(
|
|
'mtype' => "error",
|
|
'msg' => $e->getMessage(),
|
|
));
|
|
}
|
|
}
|
|
/**
|
|
*
|
|
* @method Get number of noshow of the subscriber in a certain noshow_period
|
|
* @access public
|
|
*
|
|
* @param int noshow period in days
|
|
* @param array user id
|
|
* @return array
|
|
*
|
|
*/
|
|
public function getNumberOfNoshow($nshow_period, $user_id)
|
|
{
|
|
try {
|
|
/*
|
|
* Get the date "nshow_period" ago
|
|
*/
|
|
$nshow_days_ago_date = date("Y-m-d", strtotime("-$nshow_period day"));
|
|
|
|
$query = "SELECT SUM(full_noshow) totalNoshow FROM (SELECT
|
|
ean.user_id, DATE_FORMAT(es.start_date_time, '%Y-%m-%d') start_date_time, ean.event_id, ean.event_schedule_id,
|
|
es.no_show_stat,
|
|
SUM(booking_expected) as booking_expected,
|
|
SUM(attendance) total_attendance,
|
|
|
|
CASE WHEN es.no_show_stat = 0 THEN 0 ELSE
|
|
SUM(
|
|
CASE WHEN attendance = 0 THEN 1 ELSE 0 END
|
|
) END full_noshow
|
|
|
|
FROM event_attendance_noshow ean
|
|
INNER JOIN event_schedule es USING (event_schedule_id)
|
|
INNER JOIN event ev ON (ev.event_id = es.event_id)
|
|
WHERE ean.user_id = ? AND
|
|
(
|
|
DATE_FORMAT(es.start_date_time, '%Y-%m-%d') BETWEEN ? AND DATE_FORMAT(NOW(), '%Y-%m-%d')
|
|
)
|
|
AND (
|
|
ev.status = 1 AND es.back_office_status NOT IN(6)
|
|
)
|
|
AND es.event_status != 'CANCEL'
|
|
AND booking_expected > 0
|
|
GROUP BY event_schedule_id
|
|
ORDER BY start_date_time DESC) subscriber_noshows";
|
|
|
|
$noshow_info = $this->db->query(
|
|
$query,
|
|
array($user_id, $nshow_days_ago_date)
|
|
);
|
|
|
|
if ($noshow_info->num_rows() > 0) {
|
|
return $noshow_info->row_array()['totalNoshow'];
|
|
}
|
|
return 0;
|
|
} catch (\Exception $e) {
|
|
throw new Exception('Cannot get noshow value');
|
|
output_to_json($this, array(
|
|
'mtype' => "error",
|
|
'msg' => $e->getMessage(),
|
|
));
|
|
}
|
|
}
|
|
|
|
/**
|
|
*
|
|
* @method Get number of reservation of the subscriber in a certain res_period_type
|
|
* @access public
|
|
*
|
|
* @param int reservation period
|
|
* @param array user id
|
|
* @return array
|
|
*
|
|
*/
|
|
public function getNumberOfReservation(
|
|
$res_period_type,
|
|
$user_id,
|
|
$includeEventNotInModeration=false,
|
|
$type = 1,
|
|
$checkForFutureReservation = 0,
|
|
$dateCovered = [],
|
|
$includeYear = false,
|
|
$includePaidEvents = false
|
|
)
|
|
{
|
|
try {
|
|
/*
|
|
* Get the month range for the current quarter covered
|
|
*/
|
|
|
|
$cur_year = (int) date("Y", time());
|
|
$cur_month = (int) date("n", time());
|
|
|
|
if(!empty($dateCovered) && !is_null($dateCovered)) {
|
|
$cur_year = (int) $dateCovered['cur_year'];
|
|
$cur_month = (int) $dateCovered['cur_month'];
|
|
}
|
|
|
|
$cur_quarter = ceil($cur_month/3);
|
|
$half_yearly = ceil($cur_month/6);
|
|
|
|
if ($checkForFutureReservation) {
|
|
$cur_quarter += $checkForFutureReservation;
|
|
$half_yearly += $checkForFutureReservation;
|
|
}
|
|
|
|
switch ($res_period_type) {
|
|
case 1: // quaterly
|
|
/*
|
|
* Get the months under the current quarter
|
|
*/
|
|
switch ($cur_quarter) {
|
|
case 2:
|
|
$min_month = 4;
|
|
$max_month = 6;
|
|
break;
|
|
case 3:
|
|
$min_month = 7;
|
|
$max_month = 9;
|
|
break;
|
|
case 4:
|
|
$min_month = 10;
|
|
$max_month = 12;
|
|
break;
|
|
case 5:
|
|
$min_month = 1;
|
|
$max_month = 3;
|
|
$cur_year = $cur_year + 1;
|
|
break;
|
|
|
|
default:
|
|
$min_month = 1;
|
|
$max_month = 3;
|
|
break;
|
|
}
|
|
$_where = " MONTH(es.start_date_time) >= $min_month && MONTH(es.start_date_time) <= $max_month ";
|
|
if ($includeYear) {
|
|
$_where .= " AND YEAR(es.start_date_time) = {$cur_year} ";
|
|
}
|
|
break;
|
|
case 2: // half year
|
|
switch ($half_yearly) {
|
|
case 2:
|
|
$min_month = 7;
|
|
$max_month = 12;
|
|
break;
|
|
case 3:
|
|
$min_month = 1;
|
|
$max_month = 6;
|
|
$cur_year = $cur_year + 1;
|
|
break;
|
|
default:
|
|
$min_month = 1;
|
|
$max_month = 6;
|
|
break;
|
|
}
|
|
$_where = " MONTH(es.start_date_time) >= $min_month && MONTH(es.start_date_time) <= $max_month ";
|
|
if ($includeYear) {
|
|
$_where .= " AND YEAR(es.start_date_time) = {$cur_year} ";
|
|
}
|
|
break;
|
|
case 3:
|
|
$_where = " MONTH(es.start_date_time) >= 1 && MONTH(es.start_date_time) <= 12 ";
|
|
if ($checkForFutureReservation == 1 || $includeYear) {
|
|
$cur_year += $checkForFutureReservation;
|
|
$_where .= " AND YEAR(es.start_date_time) = {$cur_year} ";
|
|
}
|
|
break;
|
|
default:
|
|
/*
|
|
* Use current month
|
|
*/
|
|
if ($checkForFutureReservation) {
|
|
if ( $cur_month == 12 ) {
|
|
$cur_year +=1;
|
|
}
|
|
$cur_month = date("n", strtotime("next month"));
|
|
}
|
|
|
|
$_where = " MONTH(es.start_date_time) = $cur_month ";
|
|
|
|
if ($includeYear) {
|
|
$_where .= " AND YEAR(es.start_date_time) = {$cur_year} ";
|
|
}
|
|
|
|
break;
|
|
}
|
|
|
|
$query = $this->reservationQuery($type, $includeEventNotInModeration, $includePaidEvents).$_where;
|
|
|
|
$reservation_info = $this->db->query(
|
|
$query,
|
|
array($user_id)
|
|
);
|
|
|
|
if ($reservation_info->num_rows() > 0) {
|
|
return $reservation_info->row_array()['totalReservations'];
|
|
}
|
|
return 0;
|
|
} catch (\Exception $e) {
|
|
throw new Exception('Cannot get noshow value');
|
|
output_to_json($this, array(
|
|
'mtype' => "error",
|
|
'msg' => $e->getMessage(),
|
|
));
|
|
}
|
|
}
|
|
|
|
private function reservationQuery($type = 1, $includeEventNotInModeration, $includePaidEvents = false) {
|
|
$addPaidEvents = " AND ev.event_category IN ('REGULAR_EVENT') ";
|
|
if ($includePaidEvents) {
|
|
$addPaidEvents = "AND ev.event_category IN ('REGULAR_EVENT', 'PAID_EVENT') ";
|
|
}
|
|
if( $type ) { // Normal reservation
|
|
return "SELECT COUNT(er.registration_id) totalReservations
|
|
FROM event_registration er
|
|
INNER JOIN event_schedule es USING(event_schedule_id)
|
|
INNER JOIN event ev ON (ev.event_id = es.event_id)
|
|
".(($includeEventNotInModeration)
|
|
? " WHERE er.subscriber = ? AND er.status = 1 ".$addPaidEvents
|
|
:" INNER JOIN gm_evsched_setting gems USING(event_schedule_id)
|
|
WHERE er.subscriber = ? AND er.status = 1 AND gems.mod_stat = 1")."
|
|
AND (ev.status = 1 AND es.back_office_status NOT IN(0,5,6,4))
|
|
AND (es.event_status != 'CANCEL') ".$addPaidEvents."AND";
|
|
|
|
// Removed : AND (es.event_status != 'CANCEL')
|
|
} else {
|
|
return "SELECT COUNT(ew.wait_list_id) totalReservations
|
|
FROM event_wait_list ew
|
|
INNER JOIN event_schedule es USING(event_schedule_id)
|
|
INNER JOIN event ev ON (ev.event_id = es.event_id)
|
|
".(($includeEventNotInModeration)
|
|
? " WHERE ew.wait_list_subscriber = ? AND ew.status = 1 ".$addPaidEvents
|
|
:" INNER JOIN gm_evsched_setting gems USING(event_schedule_id)
|
|
WHERE ew.wait_list_subscriber = ? AND ew.status = 1 AND gems.mod_stat = 1")."
|
|
AND (ev.status = 1 AND es.back_office_status NOT IN(0,5,6,4))
|
|
AND (es.event_status != 'CANCEL') ".$addPaidEvents." AND";
|
|
|
|
// Removed : AND (es.event_status != 'CANCEL')
|
|
}
|
|
}
|
|
/**
|
|
*
|
|
* @method Get number of CANCELLED reservation of the subscriber in a certain res_period_type
|
|
* @access public
|
|
*
|
|
* @param int reservation period
|
|
* @param array user id
|
|
* @return array
|
|
*
|
|
*/
|
|
public function getNumberOfCancelledReservation(
|
|
$res_period_type,
|
|
$user_id,
|
|
$includeEventNotInModeration=false,
|
|
$type = 1,
|
|
$checkForFutureReservation = 0,
|
|
$dateCovered = [],
|
|
$includeYear = false,
|
|
$addPaidEvents = false
|
|
)
|
|
{
|
|
try {
|
|
/*
|
|
* Get the month range for the current quarter covered
|
|
*/
|
|
|
|
$cur_year = (int) date("Y", time());
|
|
$cur_month = (int) date("n", time());
|
|
|
|
if(!empty($dateCovered) && !is_null($dateCovered)) {
|
|
$cur_year = (int) $dateCovered['cur_year'];
|
|
$cur_month = (int) $dateCovered['cur_month'];
|
|
}
|
|
|
|
$cur_quarter = ceil($cur_month/3);
|
|
$half_yearly = ceil($cur_month/6);
|
|
|
|
if ($checkForFutureReservation) {
|
|
$cur_quarter += $checkForFutureReservation;
|
|
$half_yearly += $checkForFutureReservation;
|
|
}
|
|
|
|
switch ($res_period_type) {
|
|
case 1: // quaterly
|
|
/*
|
|
* Get the months under the current quarter
|
|
*/
|
|
switch ($cur_quarter) {
|
|
case 2:
|
|
$min_month = 4;
|
|
$max_month = 6;
|
|
break;
|
|
case 3:
|
|
$min_month = 7;
|
|
$max_month = 9;
|
|
break;
|
|
case 4:
|
|
$min_month = 10;
|
|
$max_month = 12;
|
|
break;
|
|
case 5:
|
|
$min_month = 1;
|
|
$max_month = 3;
|
|
$cur_year = $cur_year + 1;
|
|
break;
|
|
|
|
default:
|
|
$min_month = 1;
|
|
$max_month = 3;
|
|
break;
|
|
}
|
|
$_where = " MONTH(es.start_date_time) >= $min_month && MONTH(es.start_date_time) <= $max_month ";
|
|
if ($includeYear) {
|
|
$_where .= " AND YEAR(es.start_date_time) = {$cur_year} ";
|
|
}
|
|
break;
|
|
case 2: // half year
|
|
switch ($half_yearly) {
|
|
case 2:
|
|
$min_month = 7;
|
|
$max_month = 12;
|
|
break;
|
|
case 3:
|
|
$min_month = 1;
|
|
$max_month = 6;
|
|
$cur_year = $cur_year + 1;
|
|
break;
|
|
default:
|
|
$min_month = 1;
|
|
$max_month = 6;
|
|
break;
|
|
}
|
|
$_where = " MONTH(es.start_date_time) >= $min_month && MONTH(es.start_date_time) <= $max_month ";
|
|
if ($includeYear) {
|
|
$_where .= " AND YEAR(es.start_date_time) = {$cur_year} ";
|
|
}
|
|
break;
|
|
case 3:
|
|
$_where = " MONTH(es.start_date_time) >= 1 && MONTH(es.start_date_time) <= 12 ";
|
|
if ($checkForFutureReservation == 1 || $includeYear) {
|
|
$cur_year += $checkForFutureReservation;
|
|
$_where .= " AND YEAR(es.start_date_time) = {$cur_year} ";
|
|
}
|
|
break;
|
|
default:
|
|
/*
|
|
* Use current month
|
|
*/
|
|
if ($checkForFutureReservation) {
|
|
if ( $cur_month == 12 ) {
|
|
$cur_year +=1;
|
|
}
|
|
$cur_month = date("n", strtotime("next month"));
|
|
}
|
|
|
|
$_where = " MONTH(es.start_date_time) = $cur_month ";
|
|
|
|
if ($includeYear) {
|
|
$_where .= " AND YEAR(es.start_date_time) = {$cur_year} ";
|
|
}
|
|
|
|
break;
|
|
}
|
|
|
|
$query = $this->cancelledreservationQuery($type, $includeEventNotInModeration, $addPaidEvents).$_where;
|
|
|
|
$reservation_info = $this->db->query(
|
|
$query,
|
|
array($user_id)
|
|
);
|
|
|
|
|
|
if ($reservation_info->num_rows() > 0) {
|
|
return $reservation_info->row_array()['totalCancelledReservations'];
|
|
}
|
|
return 0;
|
|
} catch (\Exception $e) {
|
|
throw new Exception('Cannot get noshow value');
|
|
output_to_json($this, array(
|
|
'mtype' => "error",
|
|
'msg' => $e->getMessage(),
|
|
));
|
|
}
|
|
}
|
|
|
|
private function cancelledreservationQuery($type = 1, $includeEventNotInModeration, $includePaidEvents = false) {
|
|
$addPaidEvents = " AND ev.event_category IN ('REGULAR_EVENT') ";
|
|
if ($includePaidEvents) {
|
|
$addPaidEvents = "AND ev.event_category IN ('REGULAR_EVENT', 'PAID_EVENT') ";
|
|
}
|
|
if( $type ) { // Normal reservation
|
|
return "SELECT COUNT(er.registration_id) totalCancelledReservations
|
|
FROM event_registration er
|
|
INNER JOIN event_schedule es USING(event_schedule_id)
|
|
INNER JOIN event ev ON (ev.event_id = es.event_id)
|
|
".(($includeEventNotInModeration)
|
|
? " WHERE er.subscriber = ? AND er.status = 1 "
|
|
:" INNER JOIN gm_evsched_setting gems USING(event_schedule_id)
|
|
WHERE er.subscriber = ? AND er.status = 1 AND gems.mod_stat = 1")."
|
|
AND (ev.status = 1 AND es.back_office_status NOT IN(0,5,6,4))
|
|
AND (es.event_status = 'CANCEL') ".$addPaidEvents."AND ";
|
|
|
|
// Removed : AND (es.event_status != 'CANCEL')
|
|
} else {
|
|
return "SELECT COUNT(ew.wait_list_id) totalCancelledReservations
|
|
FROM event_wait_list ew
|
|
INNER JOIN event_schedule es USING(event_schedule_id)
|
|
INNER JOIN event ev ON (ev.event_id = es.event_id)
|
|
".(($includeEventNotInModeration)
|
|
? " WHERE ew.wait_list_subscriber = ? AND ew.status = 1 "
|
|
:" INNER JOIN gm_evsched_setting gems USING(event_schedule_id)
|
|
WHERE ew.wait_list_subscriber = ? AND ew.status = 1 AND gems.mod_stat = 1")."
|
|
AND (ev.status = 1 AND es.back_office_status NOT IN(0,5,6,4))
|
|
AND (es.event_status = 'CANCEL') ".$addPaidEvents."AND";
|
|
|
|
// Removed : AND (es.event_status != 'CANCEL')
|
|
}
|
|
}
|
|
|
|
/**
|
|
* DB Query to Check if Subscriber is already registered in Waiting list
|
|
*
|
|
* @param integer $eventScheduleId
|
|
* @param integer $subscriber
|
|
* @return Array
|
|
*
|
|
* access public member
|
|
*/
|
|
public function queryCheckSubscriberInWaitingList(int $eventScheduleId, int $subscriber) : array
|
|
{
|
|
$result = array("reservedInWL" => 0, "data"=>[]);
|
|
|
|
$this->db->select('ewl.*, es.seats_per_subscriber, es.quota_waiting_list_seat');
|
|
$this->db->from("event_wait_list ewl");
|
|
$this->db->join("event_schedule es", "event_schedule_id");
|
|
$this->db->where('ewl.event_schedule_id', $eventScheduleId);
|
|
$this->db->where('ewl.wait_list_subscriber', $subscriber);
|
|
$this->db->where('ewl.status', 1);
|
|
$this->db->where('es.back_office_status', 2);
|
|
$this->db->order_by('ewl.date_time', 'desc');
|
|
$this->db->limit(1);
|
|
$waitlist = $this->db->get();
|
|
|
|
if ($waitlist->num_rows()) {
|
|
$row = $waitlist->row();
|
|
$result["reservedInWL"] = $row->isModeratedButAllowedInWL ? 2 : 1;
|
|
$result["data"] = $row;
|
|
}
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* DB Query to set event schedule flexible mode to true depending on its current settings
|
|
*
|
|
* @return void
|
|
*
|
|
* access public member
|
|
*/
|
|
public function querySetPerEventFlexibleModerationToActive() : void
|
|
{
|
|
/**
|
|
* Formula
|
|
*
|
|
* Total seats = 100
|
|
* Remaining = 40
|
|
* A = 100 * .50
|
|
* B = 100 – A,
|
|
* B > Remaining , enable flexible
|
|
* B < Remaining, disable flexible
|
|
* t 20 r 16
|
|
* flexibleModeHours
|
|
*/
|
|
$cur_sett = $this->getLatestModSettings(3, 0);
|
|
/*
|
|
* If global moderation and flexible moderation is turned on
|
|
* Update all events that qualify for flexible moderation
|
|
*
|
|
* Events are qualified IF
|
|
* (remaining seats >= (total seats * places volume)) AND
|
|
* reservationn start/end date || event start date (whichever is present) <= current date and time
|
|
*/
|
|
if ($cur_sett['gm_mod_stat'] && $cur_sett['flex_mod_stat']) {
|
|
$this->db->query(
|
|
"UPDATE gm_evsched_setting gmes
|
|
LEFT JOIN event_schedule es USING(event_schedule_id)
|
|
LEFT JOIN global_moderation gm USING(gm_id)
|
|
LEFT JOIN event e ON( e.event_id = es.event_id)
|
|
SET gmes.flex_mod_applied = 1
|
|
WHERE gmes.flex_mod_stat = 1
|
|
AND gmes.mod_stat = 1
|
|
AND gmes.flex_mod_applied = 0
|
|
AND es.back_office_status = 2
|
|
AND gm.flex_mod_stat = 1
|
|
AND e.event_category IN ('REGULAR_EVENT')
|
|
AND
|
|
(DATE_FORMAT(
|
|
DATE_SUB(
|
|
(CASE
|
|
WHEN ISNULL(es.reservation_end_date) THEN
|
|
CASE
|
|
WHEN ISNULL(es.reservation_end_date) THEN es.start_date_time
|
|
ELSE es.reservation_end_date
|
|
END
|
|
ELSE es.reservation_end_date
|
|
END), INTERVAL gmes.hrs_bef_event_closes HOUR
|
|
), '%Y-%m-%d %H:%i'
|
|
) <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i'))
|
|
AND
|
|
(
|
|
(CASE WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
|
|
ELSE es.remaining_seat
|
|
END) >=
|
|
(FLOOR(
|
|
(CASE WHEN e.seat_feature = 2 THEN e.total_combined_seat
|
|
ELSE es.total_available_seat END) * (gmes.places_volume/100)))
|
|
)
|
|
");
|
|
}
|
|
|
|
/*Query that gets all events qualified for flexible moderation
|
|
|
|
SELECT * FROM gm_evsched_setting gmes
|
|
LEFT JOIN event_schedule es USING(event_schedule_id)
|
|
LEFT JOIN global_moderation gm USING(gm_id)
|
|
LEFT JOIN event e ON( e.event_id = es.event_id)
|
|
WHERE gmes.flex_mod_stat = 1
|
|
AND gmes.mod_stat = 1
|
|
AND gmes.flex_mod_applied = 0
|
|
AND es.back_office_status = 2
|
|
AND gm.flex_mod_stat = 1
|
|
AND
|
|
--check if the event is qualified for flexible moderation based on the remaining seats volume
|
|
(DATE_FORMAT(
|
|
DATE_SUB(
|
|
(CASE
|
|
WHEN ISNULL(es.reservation_end_date) THEN
|
|
CASE
|
|
WHEN ISNULL(es.reservation_end_date) THEN es.start_date_time
|
|
ELSE es.reservation_end_date
|
|
END
|
|
ELSE es.reservation_end_date
|
|
END), INTERVAL gmes.hrs_bef_event_closes HOUR
|
|
), '%Y-%m-%d %H:%i'
|
|
) <= DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i'))
|
|
AND
|
|
--check if the event is qualified for flexible moderation based on the remaining seats volume
|
|
(
|
|
(CASE WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
|
|
ELSE es.remaining_seat
|
|
END) >=
|
|
(FLOOR(
|
|
(CASE WHEN e.seat_feature = 2 THEN e.total_combined_seat
|
|
ELSE es.total_available_seat END) * (gmes.places_volume/100)))
|
|
)
|
|
|
|
*/
|
|
}
|
|
|
|
/**
|
|
* Get the date when the reservation will be available for this subscriber
|
|
* @method noShowDateWhenToResumeReservation
|
|
* @param int $subscriber
|
|
* @param int $noShowLimit
|
|
* @param int $noShowDuration
|
|
* @return string
|
|
*/
|
|
public function noShowDateWhenToResumeReservation(int $subscriber, int $noShowLimit, int $noShowDuration) :array {
|
|
|
|
$nshow_days_ago_date = date("Y-m-d", strtotime("-$noShowDuration day"));
|
|
$noShowLimit = ($noShowLimit <=1)?$noShowLimit:($noShowLimit-1);
|
|
$noShowDuration++; //add plus one to advance the date
|
|
|
|
$this->db->select("@reservation_date := DATE_FORMAT(DATE_ADD(es.start_date_time, INTERVAL ".$noShowDuration." DAY), '%Y-%m-%d H:i:s') as resumeReservationOn,
|
|
DATE_FORMAT(@reservation_date, '%M') AS event_start_month_name");
|
|
$this->db->where("ean.user_id", $subscriber);
|
|
$this->db->where("ean.attendance = 0");
|
|
$this->db->where("es.item_code IS NULL");
|
|
$this->db->where("es.event_url IS NULL");
|
|
$this->db->where("DATE_FORMAT(es.start_date_time, '%Y-%m-%d') BETWEEN '".$nshow_days_ago_date."' AND DATE_FORMAT(NOW(), '%Y-%m-%d')");
|
|
$this->db->limit($noShowLimit);
|
|
$this->db->from("event_attendance_noshow ean");
|
|
$this->db->join("event_schedule es", "event_schedule_id");
|
|
$this->db->order_by('es.start_date_time', 'DESC');
|
|
$result = $this->db->get();
|
|
$totalRows = $result->num_rows();
|
|
$index = ($totalRows <=1)?0:($totalRows-1);
|
|
$resultArray = $result->result_array();
|
|
return $resultArray[$index];
|
|
}
|
|
|
|
/**
|
|
* Get event start date
|
|
*
|
|
* @param int $eventScheduleId event schedule id
|
|
* @return string
|
|
*/
|
|
public function getEventStartDate($eventScheduleId) {
|
|
if ( $eventScheduleId ) {
|
|
$row = $this->db->query("SELECT start_date_time as eventStartDate FROM event_schedule WHERE event_schedule_id = ?", array($eventScheduleId))->row();
|
|
if ( $row ) {
|
|
return $row->eventStartDate;
|
|
}
|
|
}
|
|
|
|
return '';
|
|
}
|
|
|
|
/**
|
|
* Check if event schedule is waitlist or not
|
|
*
|
|
* @param int $eventScheduleId event schedule id
|
|
* @return string
|
|
*/
|
|
public function getEventScheduleStatus($eventScheduleId) {
|
|
if ( $eventScheduleId ) {
|
|
$row = $this->db->query("SELECT remaining_seat, event_status, back_office_status FROM event_schedule WHERE event_schedule_id = ?", array($eventScheduleId))->row();
|
|
return $row ?? false;
|
|
}
|
|
|
|
return false;
|
|
}
|
|
}
|
|
|