MasterClass/application/models/Event_wait_list_model.php

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Event_wait_list_model extends CI_Model {

  	public function __construct() {
          parent::__construct();
          $this->load->model("user_activity_log_model");
      }

    public function check_subscription($user_id, $event_schedule_id)
    {
    	$query = $this->db->select('ewl.status, ewl.number_of_places')
          ->from('event_wait_list ewl')
          ->join('event_schedule es', 'es.event_schedule_id = ewl.event_schedule_id', 'left')
          ->join('event e', 'e.event_id = es.event_id', 'left')
          ->where('es.event_status !=', 'CANCEL')
          ->where('ewl.event_schedule_id', $event_schedule_id)
          ->where('ewl.wait_list_subscriber', $user_id)
          ->where('(CASE
              WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
              ELSE es.remaining_seat
            END) >= 0')
          ->where('es.event_status IN (\'FULL\', \'AVAILABLE\')')
          ->where('ewl.status !=', 0)
          ->order_by('ewl.date_time', 'DESC')
          ->limit(1)
          ->get()
          ->row();
      if(!$query){
        return false;
      } else {
          if($query->status == 0){
              return false;
          }else{
              return $query->number_of_places;
         }
      }
    }

    public function count_all_waitlist_subscription($user_id, $event_schedule_id, $event_id, $page=2, $seats_reserved=0)
    {
        if($page == 2){

          $query = $this->db->query("
            SELECT ewl.status, ewl.number_of_places
            FROM event_wait_list ewl
            LEFT JOIN event_schedule es ON es.event_schedule_id = ewl.event_schedule_id
                  AND es.event_schedule_id = ?
            LEFT JOIN event e ON e.event_id = es.event_id
            WHERE ewl.event_schedule_id = ?
            AND es.event_schedule_id = ?
            AND ewl.wait_list_subscriber = ?
            AND es.event_status != 'CANCEL'
            AND (CASE
                  WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
                  ELSE es.remaining_seat
                END) >= 0
            AND es.event_status IN ('FULL', 'AVAILABLE')
            AND ewl.status != 0
            ".(($seats_reserved > 0)? "AND ewl.number_of_places = '".$this->db->escape_str($seats_reserved)."'":"")."
            ORDER BY ewl.date_time DESC
            LIMIT 1
          ", array($event_schedule_id, $event_schedule_id, $event_schedule_id, $user_id))->row();

        } else{

          $query = $this->db->query("SELECT SUM(ewl.number_of_places) as number_of_places
              FROM event_wait_list ewl
              LEFT JOIN event_schedule es ON es.event_schedule_id = ewl.event_schedule_id
              LEFT JOIN event e ON e.event_id = es.event_id
              WHERE ewl.wait_list_subscriber = ".$this->db->escape($user_id)."
               AND (CASE
                  WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
                  ELSE es.remaining_seat
                END) >= 0
              and es.event_status IN ('FULL', 'AVAILABLE')
              AND es.event_status != 'CANCEL'
              and es.event_id = ".$this->db->escape($event_id)."
              and es.back_office_status IN(1,2,3)
              and ewl.status = 1")->row();
        }

        if(!$query){
          return false;
        } else {
          return $query->number_of_places;
        }
    }
    public function count_all_waitlist_subscription_for_myres_history($user_id, $event_schedule_id, $event_id)
    {
        $query = $this->db->query("SELECT SUM(ewl.number_of_places) as number_of_places
            FROM event_wait_list ewl
            LEFT JOIN event_schedule es ON es.event_schedule_id = ewl.event_schedule_id
            LEFT JOIN event e ON e.event_id = es.event_id
            WHERE ewl.wait_list_subscriber = ?
             AND (CASE
                WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
                ELSE es.remaining_seat
              END) >= 0
            AND es.event_id = ?
            AND ewl.status = 1", [$user_id, $event_id])->row();

        if(!$query){
          return false;
        } else {
          return $query->number_of_places;
        }
    }

    public function count_wl_registration_by_event_schedule($event_schedule_id){
        $query = $this->db->query("SELECT SUM(ewl.number_of_places) as number_of_places
          FROM event_wait_list ewl
          LEFT JOIN event_schedule es ON es.event_schedule_id = ewl.event_schedule_id
          WHERE es.event_schedule_id = ".$this->db->escape($event_schedule_id)."
          and es.back_office_status NOT IN(6)
          and ewl.status = 1")->row();

        return ($query->number_of_places)?$query->number_of_places:0;
    }

    public function register_waitlist($registration) {

        $registeredSubcriberWL = false;

    	// Remaining waiting list seats
    	$remaining_seats = $this->db->select('quota_waiting_list_seat')
			->from('event_schedule')
			->where('event_schedule_id', $registration['event_schedule_id'])
            ->limit(1)
			->get()
			->row();

		if($remaining_seats->quota_waiting_list_seat < 1) { return false; }
		else{

        if($registration["action"] == "register") {
                // Register Client and get get the registration id
            $registeredSubcriberWL = $this->add_reserved_seats($registration);

        } else if($registration["action"] == "modify") {
            // Update registration
            $registeredSubcriberWL = $this->update_reserved_seats($registration);
        }

        // Subtract from quota_waiting_list_seat seats
        $this->db->query("
          UPDATE event_schedule
           SET quota_waiting_list_seat = (CASE
                  WHEN (quota_waiting_list_seat - ?) <= 0 THEN 0
                  ELSE (quota_waiting_list_seat- ?)
                  END)
         WHERE event_schedule_id = ? ", array($registration['seats_reserved'], $registration['seats_reserved'], $registration['event_schedule_id']));
	    }

  		// Update event_status and back_office_status
  		$check_seats = $this->db->select('quota_waiting_list_seat, remaining_seat, event_status, back_office_status')
			->from('event_schedule')
			->where('event_schedule_id', $registration['event_schedule_id'])
            ->limit(1)
			->get()
			->row();

  		if($check_seats->quota_waiting_list_seat <= 0 && ($check_seats->remaining_seat >=0 && $check_seats->event_status!="AVAILABLE")){
  			$this->db->where('event_schedule_id', $registration['event_schedule_id']);
  			$this->db->update('event_schedule', array('back_office_status' => 3, 'event_status' => "FULL"));

            $event_schedule_id = $registration['event_schedule_id'];
            $log_desc = "No more available waitlist seats. Event schedule is now locked for any registrations. Backoffice status changed from Open to Locked.";
            $act_log = $this->user_activity_log_model->add_activity_log(array(
                "description" => $log_desc." - event_schedule_id : ".$this->db->escape($event_schedule_id),
                "user_id"   =>  $_SESSION["logged_in"]["user_id"],
                "action"    =>  "EDIT",
                "table_origin" => "event_schedule",
                "reference_id" => $event_schedule_id
            ));
  		}

      // Update event concurrent process
      $this->db->set('process_status', 0);
      $this->db->where('login_id', $registration['login_id']);
      $this->db->where('process_type', $registration['process_type']);
      $this->db->update('event_concurrent_process');

      return $registeredSubcriberWL;
    }

    private function add_reserved_seats($registration){

        $this->db->insert('event_wait_list',
            array(
                'event_schedule_id' => $registration['event_schedule_id'],
                'wait_list_subscriber' => $registration['user_id'],
                'number_of_places' => $registration['seats_reserved'],
                'user_agent' => $registration['user_agent'],
                'isModeratedButAllowedInWL' => $registration['isModeratedButAllowedInWL']?? 0,
                'is_user_moderated' => $registration['is_user_moderated']?? 1
            )
        );
        //get the registration id
        return $this->db->insert_id();

    }

    private function update_reserved_seats($registration){
        $row = $this->db->select("wait_list_id")
            ->from("event_wait_list")
            ->where("event_schedule_id", $registration['event_schedule_id'])
            ->where("wait_list_subscriber", $registration['user_id'])
            ->where("status", 1)
            ->get()
            ->row();
        if($row){
            $this->db->query("
              UPDATE event_wait_list
              SET number_of_places = number_of_places + ?
               WHERE event_schedule_id =?
               AND wait_list_subscriber = ?
               AND wait_list_id = ?
               AND status =1", array($registration['seats_reserved'], $registration['event_schedule_id'], $registration['user_id'], $row->wait_list_id));

            if($this->db->affected_rows()){
                return $row->wait_list_id;
            }
        }
        return false;
    }

    public function get_waitlist_registration_details($user_id, $event_schedule_id){
        return $this->db->query("select ew.*,
            (CASE
              WHEN ew.number_of_places = 0 THEN 1
              ELSE ew.number_of_places
            END) as seats_reserved
          from event_wait_list ew
          INNER JOIN event_schedule es USING(event_schedule_id)
          where ew.wait_list_subscriber = ?
          and ew.event_schedule_id = ?
          AND es.event_status != 'CANCEL'
          and ew.status = ?
          limit 1
          ", array($user_id, $event_schedule_id, 1))->row();
    }

    public function seats_reserved($user_id, $event_schedule_id){
		$query = $this->db->select('ew.number_of_places')
			->from('event_wait_list ew')
            ->join('event_schedule es', 'ew.event_schedule_id = es.event_schedule_id')
            ->where('es.event_status !=', 'CANCEL')
			->where('ew.wait_list_subscriber', $user_id)
			->where('ew.event_schedule_id', $event_schedule_id)
			->where('ew.status', 1)
			->order_by('ew.date_time', 'DESC')
			->limit(1)
			->get();
		$guests = $query->row();
		return $guests->number_of_places;
	}

  public function prepare_list_for_reinvitation_email($email_schedule_id, $event_schedule_id, $limit){
        /*
          Email key will be used to get the group of recipient ids in one single query
          which will also be used to insert into the table "event_email_recipient_other_detail"
          to get the exact id and exact connection.
        */
        $email_key = "2E".generate_random_keys(8);
        
        /* Set moderated users */

        $this->db->query("INSERT INTO
        event_email_recipient(event_schedule_id, email_type_id, email_key, reference_id)
        (SELECT ?, ?, ?, ewl.wait_list_id
        FROM event_wait_list ewl
        LEFT JOIN event_schedule es
              ON es.event_schedule_id = ewl.event_schedule_id
        LEFT JOIN event e
              ON e.event_id = es.event_id
        WHERE es.back_office_status IN(2,3)
        AND e.status = 1
        AND (CASE
              WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
              ELSE es.remaining_seat
          END) >= ewl.number_of_places
        AND ewl.wait_list_id NOT IN(SELECT eer.reference_id
                FROM event_email_recipient eer
                WHERE eer.email_status IN(1, 0, 5)
                AND eer.event_schedule_id = ?
                AND eer.email_type_id = 2
                GROUP BY eer.reference_id)
        AND ewl.status = 1
        AND ewl.event_schedule_id= ?
        ORDER BY is_user_moderated ASC, ewl.wait_list_id ASC
        LIMIT 0, ".((int)($limit))." )", array(
            $event_schedule_id,
            2,
            $email_key,
            $event_schedule_id,
            $event_schedule_id
        ));

        //insert into other details table
        $this->db->query("INSERT INTO event_email_recipient_other_detail(email_sched_reference_id, email_sched_reference, email_recipient_id)
          (SELECT ?, ?, email_recipient_id
          FROM event_email_recipient eer
          WHERE eer.email_type_id = ?
          AND eer.email_key = ?
          AND eer.event_schedule_id= ? )",
        array($email_schedule_id,
          1,
          2, //email_type_id for reinvitation waitlist
          $email_key,
          $event_schedule_id)
        );

        return $this->db->affected_rows();
    }

    public function get_data_waitlists(){
      $this->load->model("event_schedule_model");
       $result = $this->db->select("
                e.event_id,
                es.event_schedule_id,
                e.title as event_title,
                et.event_type,
                es.event_status,
                ".$this->event_schedule_model->use_diffent_address()."
                (CASE WHEN e.rate > 0 THEN e.rate ELSE \"\" END) as event_rate,
                efa.file_name as event_picture,
                e.description as event_description,
                es.start_date_time as event_start_date")
            ->select("DAYNAME(es.start_date_time) AS event_start_day_name", FALSE)
            ->select("DATE_FORMAT(es.start_date_time, '%e') AS event_start_day", FALSE)
            ->select("DATE_FORMAT(es.start_date_time, '%m') AS event_start_month", FALSE)
            ->select("DATE_FORMAT(es.start_date_time, '%M') AS event_start_month_name", FALSE)
            ->select("DATE_FORMAT(es.start_date_time, '%Hh%i') AS event_start_hour", FALSE)
            ->select("DATE_FORMAT(es.start_date_time, '%Y') AS event_start_year", FALSE)
            ->from(" event_email_recipient eer")
            ->join("event_schedule es", "es.event_schedule_id = eer.event_schedule_id", "left")
            ->join("event e", "e.event_id = es.event_id", "left")
            ->join('event_type et', 'et.event_type_id = e.event_type_id',"left")
            ->join('event_wait_list ewl', 'ewl.event_schedule_id = eer.event_schedule_id AND ewl.wait_list_id = eer.reference_id AND ewl.status=1',"left")
            ->join('event_file_attachment efa', 'efa.event_id = e.event_id AND efa.status=1 AND efa.attachment_type =1', "left")
            ->where("(eer.email_status = 0 OR (eer.email_status = 3 AND eer.email_number_of_send_attempt <=3))")
            ->where("e.status",1)
            ->where("ewl.event_schedule_id = eer.event_schedule_id ")
            ->where("eer.email_type_id", 2)
            ->where("eer.event_schedule_id IN(
                    select eses.event_schedule_id
                    from event_schedule_email_status eses
                    where eses.event_schedule_id = eer.event_schedule_id
                    and eses.email_type_id = 2
                    and eses.status = 1
                )")
            // ->where("(CASE
            //             WHEN eer.email_sched_reference = 1
            //             THEN
            //                 (SELECT eeds.email_type_id
            //                 FROM event_email_schedule ees
            //                 LEFT JOIN `event_email_default_setting` eeds
            //                   ON `eeds`.`email_tpl_setting_id` = `ees`.`reference`
            //                   AND eeds.email_type_id = 2
            //                 WHERE ees.email_schedule_id = eer.email_sched_reference_id
            //                   AND ees.event_schedule_id = eer.event_schedule_id
            //                   /*AND ees.email_schedule_status= 1*/
            //                 LIMIT 1)
            //             WHEN eer.email_sched_reference = 2
            //             THEN
            //                 (SELECT eeds.email_type_id
            //                 FROM event_email_default_setting eeds
            //                 WHERE eeds.email_tpl_setting_id = eer.email_sched_reference_id
            //                   /*AND eeds.email_tpl_setting_status= 1*/
            //                   AND eeds.email_type_id = 2
            //                 LIMIT 1)
            //         END) = 2")
           // ->where("eer.reference_id IN(
           //     SELECT wait_list_id
           //     FROM event_wait_list
           //     WHERE wait_list_id = eer.reference_id
           //     AND event_id = eer.event_id
           //     AND status = 1
           // )")
            ->where("ewl.wait_list_id = eer.reference_id")
            ->where("ewl.status",1)
            ->where_in("es.back_office_status", array(2,3))
            ->where("es.start_date_time > NOW()")
            ->where("(CASE
                    WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
                    ELSE es.remaining_seat
                END) >", 0)
            ->where("es.event_status", "AVAILABLE")
            ->group_by("eer.event_schedule_id")
            ->get();

        if ($result->num_rows() > 0) {
            return $result->result();
        }
        return false;
    }

    public function get_waitlists_subscribers($event_schedule_id, $limit){
        $result = $this->db->query("SELECT
            ewl.wait_list_subscriber as subscriber_id,
            eer.reference_id,
            u.email_address,
            ewl.number_of_places as seats_reserved,
            CONCAT(u.first_name, ' ', u.last_name) AS subscriber,
            u.first_name subs_prenom,
            u.last_name subs_nom,
            eer.email_recipient_id,
            eer.email_number_of_send_attempt
        FROM
            event_email_recipient eer
        LEFT JOIN event_wait_list ewl
            ON ewl.event_schedule_id = eer.event_schedule_id
            AND ewl.wait_list_id = eer.reference_id
            AND ewl.status=1
        LEFT JOIN user u
            ON u.user_id = ewl.wait_list_subscriber
        LEFT JOIN event_schedule es
            ON es.event_schedule_id = eer.event_schedule_id
        LEFT JOIN event e
            ON e.event_id = es.event_id
        WHERE ewl.status=1
            AND eer.reference_id = ewl.wait_list_id
            AND e.status = 1
            AND eer.email_type_id = 2
            AND (CASE
                  WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
                  ELSE es.remaining_seat
                END) >= ewl.number_of_places
            /*AND
                (CASE
                    WHEN eer.email_sched_reference = 1
                    THEN
                        (SELECT eeds.email_type_id
                        FROM event_email_schedule ees
                        LEFT JOIN `event_email_default_setting` eeds
                          ON `eeds`.`email_tpl_setting_id` = `ees`.`reference`
                          AND eeds.email_type_id = 2
                        WHERE ees.email_schedule_id = eer.email_sched_reference_id
                          AND ees.event_schedule_id = eer.event_schedule_id
                          AND ees.email_schedule_status= 1
                        LIMIT 1)
                    WHEN eer.email_sched_reference = 2
                    THEN
                        (SELECT eeds.email_type_id
                        FROM event_email_default_setting eeds
                        WHERE eeds.email_tpl_setting_id = eer.email_sched_reference_id
                        AND eeds.email_tpl_setting_status= 1
                          AND eeds.email_type_id = 2
                        LIMIT 1)
                END) = 2*/
            AND ewl.wait_list_subscriber NOT IN(
                  SELECT er.subscriber
                  FROM event_registration er
                  WHERE er.event_schedule_id = eer.event_schedule_id
                  AND er.subscriber = ewl.wait_list_subscriber
                  AND er.status = 1
              )
            AND (eer.email_status = 0 OR (eer.email_status = 3 AND eer.email_number_of_send_attempt <=3))
            AND eer.event_schedule_id = ?
            AND es.start_date_time >= NOW()
            AND (CASE
                  WHEN e.seat_feature = 2 THEN e.remaining_combined_seat
                  ELSE es.remaining_seat
                END) > 0
            AND es.event_status = 'AVAILABLE'
            GROUP BY eer.reference_id
            LIMIT 0, ".((int)($limit)), array($event_schedule_id));

        if ($result->num_rows() > 0) {
            return $result->result();
        }
        return false;
    }

    public function get_waitlist_reinvitation_email_data($email_recipient_id, $subscriber){
      $this->load->model("event_schedule_model");
      $result = $this->db->select("
                e.event_id,
                es.event_schedule_id,
                e.title as event_title,
                es.event_status,
                et.event_type,
                u.email_address,
                ".$this->event_schedule_model->use_diffent_address()."
                (CASE WHEN e.rate > 0 THEN e.rate ELSE \"\" END) as event_rate,
                ewl.number_of_places as seats_reserved,
                efa.file_name as event_picture,
                eet.email_tpl_detail,
                e.description as event_description,
                es.start_date_time as event_start_date")
            ->select("DAYNAME(es.start_date_time) AS event_start_day_name", FALSE)
            ->select("DATE_FORMAT(es.start_date_time, '%e') AS event_start_day", FALSE)
            ->select("DATE_FORMAT(es.start_date_time, '%m') AS event_start_month", FALSE)
            ->select("DATE_FORMAT(es.start_date_time, '%M') AS event_start_month_name", FALSE)
            ->select("DATE_FORMAT(es.start_date_time, '%Hh%i') AS event_start_hour", FALSE)
            ->select("DATE_FORMAT(es.start_date_time, '%Y') AS event_start_year", FALSE)
            ->select("CONCAT(u.first_name, ' ', u.last_name) AS subscriber", FALSE)
            ->select("u.first_name AS subs_prenom", FALSE)
            ->select("u.last_name AS subs_nom", FALSE)
            // ->select("CONCAT('(', u.email_address, ')') AS email_address", FALSE)
            ->from(" event_email_recipient eer")
            ->join("event_schedule es", "es.event_schedule_id = eer.event_schedule_id", "left")
            ->join("event e", "e.event_id = es.event_id", "left")
            ->join("event_wait_list ewl", "ewl.wait_list_id = eer.reference_id", "left")
            ->join("user u", "u.user_id = ewl.wait_list_subscriber", "left")
            ->join('event_type et', 'et.event_type_id = e.event_type_id',"left")
            ->join('event_email_template eet', 'eet.email_tpl_id = eer.email_tpl_id',"left")
            ->join('event_file_attachment efa', 'efa.event_id = e.event_id AND efa.status=1 AND efa.attachment_type =1', "left")
            ->where("eer.email_recipient_id", $email_recipient_id)
            ->group_by("eer.event_schedule_id")
            ->get();
        if($result->num_rows() > 0){
            return $result->row();
        }
        return false;
    }

    /* Check if can still reserve more than 1 date per event schedule? */
    public function count_all_waitlist_user_registration_per_event($event_id, $user_id){

      $result = $this->db->select('COUNT(ewl.status) as total_registration', FALSE)
              ->from('event_wait_list ewl')
              ->join('event_schedule es', 'es.event_schedule_id = ewl.event_schedule_id', 'left')
              ->where('es.event_id', $event_id)
              ->where('es.event_status !=', 'CANCEL')
              ->where_in('es.back_office_status', array(2,3))
              ->where('ewl.wait_list_subscriber', $user_id)
              ->where('ewl.status !=', 0)
              ->order_by('ewl.date_time', 'DESC')
              ->get()
              ->row();

      if($result){
        return $result->total_registration;
      }
      return 0;
    }

    /**
     * Reset the moderation of the subscribers in the WL so that once the event
     * becomes available again the subscriber who are already
     * in the WL can now reserve in the event regardless of the moderation
     * @method resetModeratedReservationsFronWaitingListByEventSchedule
     * @param  int  $eventScheduleId
     * @return void
     */
    public function resetModeratedReservationsFronWaitingListByEventSchedule(int $eventScheduleId) :void {
      $this->db->where('event_schedule_id', $eventScheduleId);
      $this->db->where('status', 1);
      $this->db->update('event_wait_list', array('isModeratedButAllowedInWL' => 0));
    }

    /**
     * Reset the moderation of the subscribers in the WL so that once the event
     * becomes available again the subscriber who are already
     * in the WL can now reserve in the event regardless of the moderation
     * @method resetModeratedReservationsFronWaitingListByEvent
     * @param  int  $event_id
     * @return void
     */
    public function resetModeratedReservationsFronWaitingListByEvent(int $event_id) :void {

      $this->db->query(
          "UPDATE event_wait_list ewl
          LEFT JOIN event_schedule es USING(event_schedule_id)
          SET ewl.isModeratedButAllowedInWL = 0
          WHERE ewl.isModeratedButAllowedInWL = 1
          AND ewl.status = 1
          AND es.event_id = ?", $event_id);
    }
}