1121 lines
39 KiB
PHP
1121 lines
39 KiB
PHP
<?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;
|
||
}
|
||
}
|