<?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;
    }
}