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.
501 lines
21 KiB
501 lines
21 KiB
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
|
|
|
|
class Event_email_recipient_model extends CI_Model {
|
|
|
|
protected $search = array();
|
|
protected $replacements = array();
|
|
|
|
var $column_order = array(null, 'u.first_name','u.last_name','u.email_address', 'email_status'); //set column field database for datatable orderable
|
|
var $column_search = array('CONCAT(u.first_name, " ", u.last_name)','u.email_address'); //set column field database for datatable searchable just firstname , lastname , address are searchable
|
|
var $order = array('reference_id' => 'desc'); // default order
|
|
|
|
public function __construct() {
|
|
parent::__construct();
|
|
$this->array = [];
|
|
}
|
|
|
|
/*
|
|
* Return main query for datatable
|
|
*/
|
|
private function _get_datatables_query($data_source, $email_type_id, $event_schedule_id){
|
|
|
|
$_search = ""; $_ordey_by = ""; $data = array();
|
|
$_query = $this->query_table($email_type_id, $event_schedule_id);
|
|
|
|
// if datatable send POST for search
|
|
if($data_source['search']['value']) {
|
|
$i = 0;
|
|
foreach ($this->column_search as $item) {// loop column
|
|
if($i===0){ // first loop
|
|
array_push($data, "%".$data_source['search']['value']."%");
|
|
$_search .= " AND ( ".$item." LIKE ? ";
|
|
} else {
|
|
array_push($data, "%".$data_source['search']['value']."%");
|
|
$_search .= " OR ".$item." LIKE ? ";
|
|
}
|
|
$i++;
|
|
}
|
|
$_search .= ")";
|
|
}
|
|
|
|
if(isset($data_source['order'])) { // here order processing
|
|
if(isset($data_source['order']['0']['dir'])) {
|
|
$_ordey_by .= " ORDER BY ".$this->column_order[$data_source['order']['0']['column']]." ".$data_source['order']['0']['dir'];
|
|
}
|
|
} else if(isset($this->order)) {
|
|
$_ordey_by .= " ORDER BY ".key($this->order)." ".$this->order[key($this->order)];
|
|
}else{
|
|
$_ordey_by="";
|
|
}
|
|
|
|
return array("query"=>$_query["query"].$_search.$_query["groupby"].$_ordey_by, "data" =>$data);
|
|
}
|
|
|
|
public function get_datatables($data_source, $email_type_id, $event_schedule_id){
|
|
|
|
$_query = $this->_get_datatables_query($data_source, $email_type_id, $event_schedule_id);
|
|
|
|
if($data_source['length'] != -1){
|
|
$_query["query"] .= " LIMIT ".((int)$data_source['start']).", ".((int)$data_source['length']);
|
|
}
|
|
$data = $this->db->query($_query["query"], $_query["data"]);
|
|
return $data->result();
|
|
}
|
|
|
|
public function count_filtered($data_source, $email_type_id, $event_schedule_id){
|
|
|
|
$_query = $this->_get_datatables_query($data_source, $email_type_id, $event_schedule_id);
|
|
return $this->db->query($_query["query"], $_query["data"])->num_rows();
|
|
}
|
|
|
|
public function count_all($data_source, $email_type_id, $event_schedule_id){
|
|
$_query = $this->_get_datatables_query($data_source, $email_type_id, $event_schedule_id);
|
|
return $this->db->query($_query["query"], $_query["data"])->num_rows();
|
|
}
|
|
|
|
public function update_recipient_email_status($data, $type){
|
|
return $this->fallback_email_status($data);
|
|
}
|
|
|
|
private function fallback_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;
|
|
}
|
|
|
|
public function get_data_resend_email_list(){
|
|
$this->load->model("event_schedule_model");
|
|
$result = $this->db->query(" SELECT
|
|
DISTINCT eer.event_schedule_id,
|
|
e.event_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,
|
|
(SELECT
|
|
GROUP_CONCAT(CONCAT(eert.email_type_id) SEPARATOR '_') as email_type_id
|
|
FROM event_email_recipient eert
|
|
LEFT JOIN event_schedule_email_status eses
|
|
ON eses.event_schedule_id = eert.event_schedule_id
|
|
AND eses.email_type_id = eert.email_type_id
|
|
AND eses.status = 1
|
|
WHERE eert.event_schedule_id = eer.event_schedule_id
|
|
AND eert.email_type_id IN(3,5,6,7,8)
|
|
AND eert.email_status IN(0,3)
|
|
AND eert.email_number_of_send_attempt <= 3
|
|
AND eert.email_tpl_id IS NULL
|
|
AND eses.email_type_id = eert.email_type_id
|
|
AND eses.status = 1
|
|
) AS email_type_id,
|
|
e.description as event_description,
|
|
es.start_date_time as event_start_date,
|
|
es.item_code,
|
|
DAYNAME(es.start_date_time) AS event_start_day_name,
|
|
DATE_FORMAT(es.start_date_time, '%e') AS event_start_day,
|
|
DATE_FORMAT(es.start_date_time, '%m') AS event_start_month,
|
|
DATE_FORMAT(es.start_date_time, '%Hh%i') AS event_start_hour,
|
|
DATE_FORMAT(es.start_date_time, '%Y') AS event_start_year,
|
|
DATE_FORMAT(es.start_date_time, '%M') AS event_start_month_name,
|
|
efa.file_name as event_picture
|
|
FROM event_email_recipient eer
|
|
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
|
|
LEFT JOIN event_type et
|
|
ON et.event_type_id = e.event_type_id
|
|
LEFT JOIN event_file_attachment efa
|
|
ON efa.event_id = e.event_id AND efa.status=1 AND efa.attachment_type =1
|
|
WHERE
|
|
eer.email_status IN(0, 3)
|
|
AND eer.email_number_of_send_attempt <= 3
|
|
AND eer.email_type_id IN(3,5,6,7,8)
|
|
AND eer.email_tpl_id IS NULL
|
|
AND 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 = eer.email_type_id
|
|
and eses.status = 1
|
|
)
|
|
AND es.back_office_status IN(2,3)
|
|
AND e.status = 1
|
|
GROUP BY eer.event_schedule_id");
|
|
if($result->num_rows() > 0){
|
|
return $result->result();
|
|
}
|
|
return false;
|
|
}
|
|
|
|
public function get_event_subscribers_list($event_schedule_id, $email_type_id, $limit){
|
|
|
|
$query = $this->select_subscribers_from($email_type_id);
|
|
|
|
if($query["join"] != ""){
|
|
|
|
$result = $this->db->query("SELECT
|
|
eer.event_schedule_id,
|
|
eer.reference_id,
|
|
eer.email_recipient_id,
|
|
eer.email_number_of_send_attempt,
|
|
".$query["select"]."
|
|
FROM
|
|
event_email_recipient eer
|
|
".$query["join"]."
|
|
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 e.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 eer.email_type_id = ?
|
|
AND es.start_date_time >= NOW()
|
|
AND es.back_office_status IN(2,3)
|
|
".$query["where"]."
|
|
GROUP BY eer.reference_id
|
|
LIMIT 0,".$limit, array($event_schedule_id, $email_type_id));
|
|
|
|
if ($result->num_rows() > 0) {
|
|
return $result->result();
|
|
}
|
|
return false;
|
|
}
|
|
return false;
|
|
}
|
|
|
|
private function select_subscribers_from($email_type_id){
|
|
/** email_type_id
|
|
* 3 = Confir registration
|
|
* 5 = Cancel registration
|
|
* 6 = Confirm waitlist registration
|
|
**/
|
|
$query = array(
|
|
"select" => "",
|
|
"join" => "",
|
|
"where" => ""
|
|
);
|
|
switch($email_type_id){
|
|
//Confirm reservation
|
|
case 3 || 8 : $query["join"] = "
|
|
LEFT JOIN event_registration er
|
|
ON er.event_schedule_id = eer.event_schedule_id
|
|
AND er.registration_id = eer.reference_id
|
|
LEFT JOIN user u
|
|
ON u.user_id = er.subscriber ";
|
|
$query["where"] = " AND er.status = 1 AND er.registration_id = eer.reference_id ";
|
|
$query["select"] = " (er.number_of_guest+1) as seats_reserved, "
|
|
." u.email_address, er.subscriber as subscriber_id, "
|
|
." CONCAT(u.first_name, ' ', u.last_name) AS subscriber, "
|
|
." u.first_name AS subs_prenom, u.last_name AS subs_nom ";
|
|
break;
|
|
//Confirm Cancellation
|
|
case 5 :
|
|
$query["join"] = "
|
|
LEFT JOIN event_deregistration edr
|
|
ON edr.deregistration_id = eer.reference_id
|
|
LEFT JOIN user u
|
|
ON u.user_id = edr.subscriber ";
|
|
$query["where"] = " AND edr.subscriber
|
|
NOT IN (SELECT er.subscriber
|
|
FROM event_registration er
|
|
WHERE er.subscriber = edr.subscriber
|
|
AND er.status = 1
|
|
AND er.event_schedule_id = eer.event_schedule_id
|
|
)";
|
|
$query["select"] = " (edr.seats_reserve+1) as seats_reserved, "
|
|
." u.email_address, edr.subscriber as subscriber_id, "
|
|
." CONCAT(u.first_name, ' ', u.last_name) AS subscriber, "
|
|
." u.first_name AS subs_prenom, u.last_name AS subs_nom ";
|
|
break;
|
|
//Confirm Waitlist Reservation
|
|
case 6 :
|
|
$query["join"] = "
|
|
LEFT JOIN event_wait_list ewl
|
|
ON ewl.wait_list_id = eer.reference_id
|
|
LEFT JOIN user u
|
|
ON u.user_id = ewl.wait_list_subscriber ";
|
|
$query["where"] = " AND ewl.status = 1 ";
|
|
$query["select"] = " ewl.number_of_places as seats_reserved, "
|
|
." u.email_address, ewl.wait_list_subscriber as subscriber_id, "
|
|
." CONCAT(u.first_name, ' ', u.last_name) AS subscriber, "
|
|
." u.first_name AS subs_prenom, u.last_name AS subs_nom ";
|
|
break;
|
|
}
|
|
|
|
return $query;
|
|
}
|
|
/**
|
|
* Check if email is enabled
|
|
*/
|
|
public function check_emailing_status($event_schedule_id, $email_type_id){
|
|
return $this->db->query("
|
|
SELECT eses.event_schedule_id
|
|
FROM event_schedule_email_status eses
|
|
WHERE eses.event_schedule_id = ?
|
|
AND eses.email_type_id = ?
|
|
AND eses.status = 1
|
|
LIMIT 1", array($event_schedule_id, $email_type_id))->num_rows();
|
|
}
|
|
|
|
/**
|
|
* resend email to event subscribers for desired category
|
|
*/
|
|
public function resend_email($email_type_id, $event_schedule_id){
|
|
|
|
$_query = $this->query_table($email_type_id , $event_schedule_id)['query'];
|
|
|
|
$subscribers = $this->db->query($_query);
|
|
|
|
$total_rows = $subscribers->num_rows();
|
|
$rows = $subscribers->result_array();
|
|
|
|
for ($i=0; $i < $total_rows; $i++) {
|
|
array_push($this->array, array(
|
|
'event_schedule_id' => $event_schedule_id,
|
|
'reference_id' => $rows[$i]['reference_id'],
|
|
'email_type_id' => $email_type_id,
|
|
'email_status' => '0'
|
|
));
|
|
}
|
|
if (count($this->array) >0) {
|
|
$this->db->insert_batch('event_email_recipient',$this->array);
|
|
}
|
|
$this->db->trans_complete();
|
|
|
|
return ($this->db->trans_status() === FALSE)? FALSE:TRUE;
|
|
}
|
|
|
|
/**
|
|
* Insert new reference ids for que
|
|
*/
|
|
public function insert_reference_ids()
|
|
{
|
|
$event_schedule_id = $this->input->post('event_schedule_id');
|
|
$email_type_id = $this->input->post('email_type_id');
|
|
$stop = 0;
|
|
$size = sizeof( $this->input->post('reference_id') );
|
|
|
|
if( is_array($this->input->post('reference_id'))
|
|
|| is_object($this->input->post('reference_id'))){
|
|
|
|
for( $i = $stop; $i < $size; $i++) {
|
|
array_push($this->array, array(
|
|
'event_schedule_id' => $event_schedule_id,
|
|
'reference_id' => $this->input->post('reference_id')[$i],
|
|
'email_type_id' => $email_type_id,
|
|
'email_status' => '0'
|
|
));
|
|
}
|
|
$this->db->insert_batch('event_email_recipient',$this->array);
|
|
$this->db->trans_complete();
|
|
return ($this->db->trans_status() === FALSE)? FALSE:TRUE;
|
|
}else{
|
|
return false;
|
|
}
|
|
}
|
|
/**
|
|
* Create email template select option
|
|
*/
|
|
public function get_email_template_types($lists)
|
|
{
|
|
$where = "" ;
|
|
|
|
if( $lists ){
|
|
$lists = implode(",", $lists);
|
|
$where = "WHERE email_type_id IN(".$lists.")" ;
|
|
}
|
|
return $this->db->query("
|
|
SELECT email_type_id, email_type, sort_order
|
|
FROM event_email_template_type
|
|
".$where."
|
|
ORDER BY sort_order
|
|
ASC
|
|
")->result_array();
|
|
}
|
|
|
|
/**
|
|
* Returns Query for following lists
|
|
*
|
|
* 1 = Event reminder
|
|
* 2 = Reinvitation Wait List
|
|
* 3 = Booking Confirmation/Reservation
|
|
* 4 = Push Event
|
|
* 5 = Booking Cancellation
|
|
* 8 = Modify Reservation
|
|
* 6 = Waitlist - Confirmation
|
|
*/
|
|
|
|
private function query_table($email_type_id , $event_schedule_id)
|
|
{
|
|
switch ($email_type_id) {
|
|
|
|
//case 1: //Event Reminder
|
|
case 3: //Booking Confirmation/Reservation
|
|
return array("query" =>"
|
|
SELECT
|
|
u.email_address as email,
|
|
er.registration_id as reference_id,
|
|
u.first_name,
|
|
u.last_name,
|
|
er.subscriber,
|
|
(
|
|
SELECT CONCAT(eer.email_status,'_',eer.email_recipient_id,'_',
|
|
(CASE WHEN eer.email_description IS NULL THEN \"\"
|
|
ELSE eer.email_description
|
|
END))
|
|
FROM event_email_recipient eer
|
|
WHERE eer.reference_id = er.registration_id
|
|
AND eer.email_type_id = ".$this->db->escape($email_type_id)."
|
|
AND eer.email_status != 5
|
|
ORDER BY eer.email_recipient_id DESC LIMIT 1
|
|
) as email_status
|
|
FROM event_registration er
|
|
LEFT JOIN user u ON u.user_id = er.subscriber
|
|
WHERE er.status = 1
|
|
AND er.event_schedule_id = ".$this->db->escape($event_schedule_id)." ",
|
|
"groupby" => " GROUP BY er.subscriber ");
|
|
|
|
break;
|
|
|
|
case 5: //Booking Cancelation
|
|
return array("query" =>"
|
|
SELECT
|
|
u.email_address as email,
|
|
ed.deregistration_id as reference_id,
|
|
u.first_name,
|
|
u.last_name,
|
|
ed.subscriber,
|
|
(
|
|
SELECT CONCAT(eer.email_status,'_',eer.email_recipient_id,'_',
|
|
(CASE WHEN eer.email_description IS NULL THEN \"\"
|
|
ELSE eer.email_description
|
|
END))
|
|
FROM event_email_recipient eer
|
|
WHERE eer.reference_id = ed.deregistration_id
|
|
AND eer.email_type_id = ".$this->db->escape($email_type_id)."
|
|
AND eer.email_status != 5
|
|
ORDER BY eer.email_recipient_id DESC LIMIT 1
|
|
) as email_status
|
|
FROM event_deregistration ed
|
|
LEFT JOIN user u ON u.user_id = ed.subscriber
|
|
WHERE ed.subscriber
|
|
NOT IN (SELECT er.subscriber
|
|
FROM event_registration er
|
|
WHERE er.subscriber = ed.subscriber
|
|
AND er.status = 1
|
|
AND er.event_schedule_id = ".$this->db->escape($event_schedule_id)."
|
|
)
|
|
AND ed.event_schedule_id =".$this->db->escape($event_schedule_id)." ",
|
|
"groupby" => " GROUP BY ed.subscriber ");
|
|
break;
|
|
|
|
//case 2: //Reinvitaion/waitlist
|
|
case 6: //Wailist Confirmation
|
|
return array("query" =>"
|
|
SELECT
|
|
u.email_address as email,
|
|
wl.wait_list_id as reference_id,
|
|
u.first_name,
|
|
u.last_name,
|
|
wl.wait_list_subscriber,
|
|
(
|
|
SELECT CONCAT(eer.email_status,'_',eer.email_recipient_id,'_',
|
|
(CASE WHEN eer.email_description IS NULL THEN \"\"
|
|
ELSE eer.email_description
|
|
END))
|
|
FROM event_email_recipient eer
|
|
WHERE eer.reference_id = wl.wait_list_id
|
|
AND eer.email_type_id = ".$this->db->escape($email_type_id)."
|
|
AND eer.email_status != 5
|
|
ORDER BY eer.email_recipient_id DESC LIMIT 1
|
|
) as email_status
|
|
FROM event_wait_list wl
|
|
LEFT JOIN user u
|
|
ON u.user_id = wl.wait_list_subscriber
|
|
WHERE wl.status = 1
|
|
AND wl.event_schedule_id = ".$this->db->escape($event_schedule_id)." ",
|
|
"groupby" => " GROUP BY wl.wait_list_subscriber ");
|
|
break;
|
|
}
|
|
}
|
|
|
|
public function add_email_logs($data){
|
|
$this->db->insert("event_email_recipient", $data);
|
|
return $this->db->insert_id();
|
|
}
|
|
|
|
public function cancel_resend_email($email_recipient_id){
|
|
|
|
$this->db->select("email_status");
|
|
$this->db->where("email_recipient_id", $email_recipient_id);
|
|
$this->db->limit(1);
|
|
$result = $this->db->get('event_email_recipient')->row();
|
|
if($result){
|
|
if($result->email_status != 0){
|
|
return false;
|
|
} else {
|
|
//delete email on queue
|
|
$this->db->where("email_recipient_id", $email_recipient_id);
|
|
$this->db->delete("event_email_recipient");
|
|
return $this->db->affected_rows();
|
|
}
|
|
}
|
|
return false;
|
|
}
|
|
|
|
public function queue_reservation_email($email_info) {
|
|
$this->db->insert('event_email_recipient', $email_info);
|
|
return $this->db->insert_id();
|
|
}
|
|
|
|
public function onqueue_cancellation_emails_upon_event_cancellation($event_schedule_id, $email_type_id) {
|
|
$this->db->query("INSERT INTO
|
|
event_email_recipient(event_schedule_id, email_type_id, reference_id)
|
|
(SELECT ?, ?, er.registration_id
|
|
FROM event_registration er
|
|
LEFT JOIN event_schedule es
|
|
ON es.event_schedule_id = er.event_schedule_id
|
|
WHERE es.back_office_status IN(2,3)
|
|
AND er.status = 1
|
|
AND es.event_status = 'CANCEL'
|
|
AND NOT EXISTS (
|
|
SELECT eer.email_recipient_id
|
|
FROM event_email_recipient eer
|
|
WHERE er.registration_id = eer.reference_id
|
|
AND eer.email_type_id = 7
|
|
)
|
|
AND er.event_schedule_id = ? )",
|
|
array($event_schedule_id,
|
|
$email_type_id,
|
|
$event_schedule_id)
|
|
);
|
|
return $this->db->affected_rows();
|
|
}
|
|
}
|
|
|