You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
289 lines
13 KiB
289 lines
13 KiB
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
|
|
|
|
class Background_service_model extends CI_Model {
|
|
|
|
function __construct(){
|
|
parent::__construct();
|
|
}
|
|
|
|
function update_recipient_email_status($data, $type){
|
|
switch($type){
|
|
case 1 :
|
|
return $this->update_reminder_email_status($data);
|
|
break;
|
|
case 2 :
|
|
return $this->update_reinvitation_email_status($data);
|
|
break;
|
|
default :
|
|
echo "No action has been specified.";
|
|
exit(0);
|
|
break;
|
|
};
|
|
}
|
|
function get_mail_recipes($event_id, $mail_id, $limit)
|
|
{
|
|
$result = $this->db->query("SELECT ets.number_of_send_attempt, ets.subscriber, ets.event_subscriber_id, u.email_address,
|
|
CONCAT(u.first_name, ' ', u.last_name) AS sub_name
|
|
FROM event_target_subscriber ets
|
|
LEFT JOIN user u ON u.user_id = ets.subscriber
|
|
WHERE (ets.status = 0 OR (ets.status = 3 AND ets.number_of_send_attempt <=3))
|
|
AND ets.event_id = ?
|
|
AND ets.mail_id = ?
|
|
LIMIT ?", array($event_id, $mail_id, $limit));
|
|
|
|
if ($result->num_rows() > 0) {
|
|
return $result->result_array();
|
|
}
|
|
return false;
|
|
}
|
|
|
|
function get_reminder_subscribers($event_id, $limit)
|
|
{
|
|
$result = $this->db->query("SELECT
|
|
er.subscriber as subscriber_id,
|
|
er.registration_id AS reference_id,
|
|
u.email_address,
|
|
CONCAT(u.first_name, ' ', u.last_name) AS sub_name,
|
|
ees.event_email_id,
|
|
ees.number_of_send_attempt
|
|
FROM
|
|
event_registration er
|
|
LEFT JOIN user u ON u.user_id = er.subscriber
|
|
LEFT JOIN event_email_subscriber ees ON ees.event_id = er.event_id AND er.registration_id = ees.reference_id
|
|
WHERE
|
|
(er.registration_id IN(SELECT reference_id FROM event_email_subscriber WHERE event_id = er.event_id and type = 1 AND (status = 0 OR (status = 3 AND number_of_send_attempt <=3)))
|
|
OR
|
|
er.registration_id NOT IN(SELECT reference_id FROM event_email_subscriber WHERE event_id = er.event_id and type = 1 AND (status = 1 OR status=2 OR (status = 3 AND number_of_send_attempt >3)))
|
|
)
|
|
AND
|
|
er.status = 1
|
|
AND
|
|
er.event_id = ?
|
|
GROUP BY er.registration_id
|
|
LIMIT ?", array($event_id, $limit));
|
|
|
|
if ($result->num_rows() > 0) {
|
|
return $result->result_array();
|
|
}
|
|
return false;
|
|
}
|
|
|
|
public function get_data_reminders(){
|
|
$data = $this->reminder_with_template();
|
|
|
|
if($data){
|
|
return array("c_template" => true, "data" => $data);
|
|
}
|
|
// else {
|
|
// return array("c_template" => false, "data" => $this->reminder_without_template());
|
|
// }
|
|
return false;
|
|
}
|
|
|
|
private function reminder_with_template(){
|
|
$result = $this->db->select("
|
|
e.event_id,
|
|
e.title as event_title,
|
|
e.event_status,
|
|
ecl.city as event_city_location,
|
|
et.event_type,
|
|
e.location as event_venue,
|
|
e.rate as event_rate,
|
|
e.remaining_seat,
|
|
e.total_available_seat as available_seat,
|
|
e.description,
|
|
eet.description as description_of_email,
|
|
eet.email_template_id,
|
|
efa.file_name")
|
|
->select("DATE_FORMAT(e.end_date_time, '%d/%m/%Y %Hh%i') AS event_end_date", FALSE)
|
|
->select("DATE_FORMAT(e.reservation_start_date, '%d/%m/%Y %Hh%i') AS reservation_date", FALSE)
|
|
->from("event_email_template eet")
|
|
->join("event e", "e.event_id = eet.event_id", "left")
|
|
->join("event_registration er", "er.event_id = eet.event_id", "left")
|
|
->join("event_email_subscriber ees", "ees.event_id = eet.event_id", "left")
|
|
->join("user u", "u.user_id = er.subscriber", "left")
|
|
->join('event_type et', 'et.event_type_id = e.event_type_id',"left")
|
|
->join('event_city_location ecl', 'ecl.city_location_id = e.city_location', "left")
|
|
->join('event_file_attachment efa', 'efa.event_id = eet.event_id AND efa.status=1', "left")
|
|
->where("er.status", 1)
|
|
->where("eet.status", 2)
|
|
->where("(er.registration_id IN(SELECT reference_id FROM event_email_subscriber WHERE event_id = er.event_id and type = 1 AND (status = 0 OR (status = 3 AND number_of_send_attempt <=3)))
|
|
OR
|
|
er.registration_id NOT IN(SELECT reference_id FROM event_email_subscriber WHERE event_id = er.event_id and type = 1 AND (status = 1 OR status=2 OR (status = 3 AND number_of_send_attempt >3)))
|
|
)")
|
|
->where_in("e.back_office_status", array(1,2,3,4))
|
|
->where("(CURRENT_TIMESTAMP) >= eet.schedule")
|
|
->group_by("eet.event_id")
|
|
->get();
|
|
|
|
if ($result->num_rows() > 0) {
|
|
return $result->result();
|
|
}
|
|
return false;
|
|
}
|
|
|
|
private function reminder_without_template(){
|
|
$result = $this->db->select("
|
|
e.event_id,
|
|
e.title as event_title,
|
|
e.event_status,
|
|
ecl.city as event_city_location,
|
|
et.event_type,
|
|
e.location as event_venue,
|
|
e.rate as event_rate,
|
|
e.remaining_seat,
|
|
e.total_available_seat as available_seat,
|
|
e.description,
|
|
efa.file_name")
|
|
->select("DATE_FORMAT(e.end_date_time, '%d/%m/%Y %Hh%i') AS event_end_date", FALSE)
|
|
->select("DATE_FORMAT(e.reservation_start_date, '%d/%m/%Y %Hh%i') AS reservation_date", FALSE)
|
|
->from("event e")
|
|
->join("event_registration er", "er.event_id = e.event_id", "left")
|
|
->join("user u", "u.user_id = er.subscriber", "left")
|
|
->join('event_type et', 'et.event_type_id = e.event_type_id',"left")
|
|
->join('event_city_location ecl', 'ecl.city_location_id = e.city_location', "left")
|
|
->join('event_file_attachment efa', 'efa.event_id = e.event_id AND efa.status=1', "left")
|
|
->where("er.status", 1)
|
|
->where("(er.registration_id IN(SELECT reference_id FROM event_email_subscriber WHERE event_id = er.event_id and type = 1 AND (status = 0 OR (status = 3 AND number_of_send_attempt <=3)))
|
|
OR
|
|
er.registration_id NOT IN(SELECT reference_id FROM event_email_subscriber WHERE event_id = er.event_id and type = 1 AND (status = 1 OR status=2 OR (status = 3 AND number_of_send_attempt >3)))
|
|
)")
|
|
->where("e.event_id NOT IN(SELECT event_id FROM event_email_template WHERE status=2 AND schedule BETWEEN '".date('Y-m')."-01' AND '".date("Y-m-t")."')")
|
|
->where_in("e.back_office_status", array(1,2,3,4))
|
|
->where("TIMESTAMPDIFF(HOUR,CURRENT_TIMESTAMP, start_date_time) <=", 48) //by default, send email only to those who subscribed 48hrs before the event date
|
|
->group_by("e.event_id")
|
|
->get();
|
|
|
|
if ($result->num_rows() > 0) {
|
|
return $result->result();
|
|
}
|
|
return false;
|
|
}
|
|
|
|
public function get_data_waitlists(){
|
|
$result = $this->db->select("
|
|
e.event_id,
|
|
e.title as event_title,
|
|
e.event_status,
|
|
ecl.city as event_city_location,
|
|
et.event_type,
|
|
e.location as event_venue,
|
|
e.rate as event_rate,
|
|
e.remaining_seat,
|
|
e.total_available_seat as available_seat,
|
|
e.description,
|
|
efa.file_name")
|
|
->select("DATE_FORMAT(e.end_date_time, '%d/%m/%Y %Hh%i') AS event_end_date", FALSE)
|
|
->select("DATE_FORMAT(e.reservation_start_date, '%d/%m/%Y %Hh%i') AS reservation_date", FALSE)
|
|
->from("event_email_subscriber ees")
|
|
->join("event e", "e.event_id = ees.event_id", "left")
|
|
->join('event_type et', 'et.event_type_id = e.event_type_id',"left")
|
|
->join('event_city_location ecl', 'ecl.city_location_id = e.city_location', "left")
|
|
->join('event_file_attachment efa', 'efa.event_id = ees.event_id AND efa.status=1', "left")
|
|
->where("ees.type = 2 AND (ees.status = 0 OR (ees.status = 3 AND ees.number_of_send_attempt <=3))")
|
|
->where_in("e.back_office_status", array(1,2,3,4))
|
|
->group_by("ees.event_id")
|
|
->get();
|
|
|
|
if ($result->num_rows() > 0) {
|
|
return $result->result();
|
|
}
|
|
return false;
|
|
}
|
|
|
|
public function get_waitlists_subscribers($event_id, $limit){
|
|
$result = $this->db->query("SELECT
|
|
ewl.wait_list_subscriber as subscriber_id,
|
|
ees.reference_id,
|
|
u.email_address,
|
|
CONCAT(u.first_name, ' ', u.last_name) AS sub_name,
|
|
ees.event_email_id,
|
|
ees.number_of_send_attempt
|
|
FROM
|
|
event_email_subscriber ees
|
|
LEFT JOIN event_wait_list ewl ON ewl.event_id = ees.event_id AND ewl.wait_list_id = ees.reference_id
|
|
LEFT JOIN user u ON u.user_id = ewl.wait_list_subscriber
|
|
WHERE
|
|
ees.type = 2
|
|
AND
|
|
(ees.status = 0 OR (ees.status = 3 AND ees.number_of_send_attempt <=3))
|
|
AND
|
|
ees.event_id = ?
|
|
GROUP BY ees.reference_id
|
|
LIMIT ?", array($event_id, $limit));
|
|
|
|
if ($result->num_rows() > 0) {
|
|
return $result->result_array();
|
|
}
|
|
return false;
|
|
}
|
|
|
|
public function get_all_published_events($dateNow)
|
|
{
|
|
$result = $this->db->select("
|
|
e.event_id,
|
|
e.title as event_title,
|
|
e.event_status,
|
|
ecl.city as event_city_location,
|
|
et.event_type,
|
|
e.location as event_venue,
|
|
e.rate as event_rate,
|
|
e.remaining_seat,
|
|
e.total_available_seat as available_seat,
|
|
e.description,
|
|
em.mail_description as description_of_email,
|
|
em.mail_id,
|
|
efa.file_name")
|
|
->select("DATE_FORMAT(e.end_date_time, '%d/%m/%Y %Hh%i') AS event_end_date", FALSE)
|
|
->select("DATE_FORMAT(e.reservation_start_date, '%d/%m/%Y %Hh%i') AS reservation_date", FALSE)
|
|
->from("event_mail em")
|
|
->join("event e", "e.event_id = em.event_id", "left")
|
|
->join("event_target_subscriber ets", "em.event_id = ets.event_id AND ets.status = 0", "left")
|
|
->join("user u", "u.user_id = ets.subscriber", "left")
|
|
->join('event_type et', 'et.event_type_id = e.event_type_id',"left")
|
|
->join('event_city_location ecl', 'ecl.city_location_id = e.city_location', "left")
|
|
->join('event_file_attachment efa', 'efa.event_id = em.event_id AND efa.status=1', "left")
|
|
->where("ets.status", 0)
|
|
->where("em.status", 2)
|
|
->where("e.back_office_status !=", 0)
|
|
->where("DATE_FORMAT('" . $this->db->escape_str($dateNow) . "','%Y-%m-%d %H:%i:%s') < DATE_FORMAT(e.start_date_time, '%Y-%m-%d %H:%i:%s')")
|
|
// ->where("DATE_FORMAT('" . $dateNow . "','%Y-%m-%d %H:%i:%s') >= DATE_FORMAT(em.schedule_time_to_send_mail, '%Y-%m-%d %H:%i:%s')")
|
|
->group_by("emd.mail_id")
|
|
->get();
|
|
|
|
if ($result->num_rows() > 0) {
|
|
return $result->result();
|
|
}
|
|
return false;
|
|
}
|
|
|
|
public function update_reminder_email_status($data){
|
|
if(isset($data) && !empty($data)) {
|
|
|
|
$this->db->trans_start();
|
|
//update batch
|
|
if($data["for_update"])
|
|
$this->db->update_batch('event_email_subscriber', $data["for_update"], 'event_email_id');
|
|
|
|
if($data["for_insertion"])
|
|
//insert_batch
|
|
$this->db->insert_batch('event_email_subscriber', $data["for_insertion"]);
|
|
|
|
$this->db->trans_complete();
|
|
return ($this->db->trans_status() === FALSE)? FALSE:TRUE;
|
|
}
|
|
return false;
|
|
}
|
|
|
|
public function update_reinvitation_email_status($data){
|
|
if(isset($data) && !empty($data)) {
|
|
$update_emails = array_merge($data["sent_emails"], $data["failed_emails"]);
|
|
$this->db->trans_start();
|
|
$this->db->update_batch('event_email_recipient', $update_emails, 'email_recipient_id');
|
|
$this->db->trans_complete();
|
|
return ($this->db->trans_status() === FALSE)? FALSE:TRUE;
|
|
}
|
|
return false;
|
|
}
|
|
}
|
|
/*If we have a record of this email address we will have sent an email.*/
|
|
|