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.
411 lines
17 KiB
411 lines
17 KiB
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
|
|
|
|
class Event_email_template_model extends CI_Model {
|
|
|
|
var $column_order = array(null, 'eet.email_tpl_name', 'eett.email_type','eet.email_tpl_creation_status','eet.email_tpl_status', 'u.first_name', null, 'eet.email_tpl_date_created'); //set column field database for datatable orderable
|
|
var $column_search = array('CONCAT(u.first_name, " ", u.last_name)','eet.email_tpl_name'); //set column field database for datatable searchable just firstname , lastname , address are searchable
|
|
// var $order = array('eet.email_tpl_date_created' => 'desc'); // default order
|
|
|
|
public function __construct() {
|
|
parent::__construct();
|
|
}
|
|
|
|
private function _get_datatables_query($data_source){
|
|
|
|
$_query = ""; $_search = ""; $_ordey_by = ""; $data = array(); $select = "";
|
|
|
|
$_query ="SELECT
|
|
eet.email_tpl_id,
|
|
eett.email_type,
|
|
eett.email_type_subject,
|
|
eet.email_type_id,
|
|
eet.email_tpl_name,
|
|
eet.email_tpl_subject,
|
|
eet.email_tpl_surheader,
|
|
eet.email_tpl_creation_status AS email_tpl_creation_status,
|
|
eet.email_tpl_status AS email_tpl_status_,
|
|
(CASE
|
|
WHEN eet.email_tpl_status = 1 THEN 'En cours de création'
|
|
WHEN eet.email_tpl_status = 2 THEN 'Publié'
|
|
ELSE 'Email n\'est pas encore créé'
|
|
END) AS email_tpl_status,
|
|
(CASE
|
|
WHEN eet.email_tpl_status = 2 AND eett.sort_order=1 AND eet.email_tpl_creation_status = 'DEFAULT' THEN CONCAT('j - ',eet.email_tpl_date_created)
|
|
WHEN eet.email_tpl_status = 2 AND eett.sort_order=2 AND eet.email_tpl_creation_status = 'DEFAULT' THEN CONCAT('i - ',eet.email_tpl_date_created)
|
|
WHEN eet.email_tpl_status = 2 AND eett.sort_order=3 AND eet.email_tpl_creation_status = 'DEFAULT' THEN CONCAT('h - ',eet.email_tpl_date_created)
|
|
WHEN eet.email_tpl_status = 2 AND eett.sort_order=4 AND eet.email_tpl_creation_status = 'DEFAULT' THEN CONCAT('g - ',eet.email_tpl_date_created)
|
|
WHEN eet.email_tpl_status = 2 AND eett.sort_order=5 AND eet.email_tpl_creation_status = 'DEFAULT' THEN CONCAT('f - ',eet.email_tpl_date_created)
|
|
WHEN eet.email_tpl_status = 2 AND eett.sort_order=6 AND eet.email_tpl_creation_status = 'DEFAULT' THEN CONCAT('e - ',eet.email_tpl_date_created)
|
|
WHEN eet.email_tpl_status = 2 AND eett.sort_order=7 AND eet.email_tpl_creation_status = 'DEFAULT' THEN CONCAT('c - ',eet.email_tpl_date_created)
|
|
WHEN eet.email_tpl_status = 2 AND eett.sort_order=8 AND eet.email_tpl_creation_status = 'DEFAULT' THEN CONCAT('b - ',eet.email_tpl_date_created)
|
|
ELSE CONCAT('a - ',eet.email_tpl_date_created)
|
|
END) AS custom_order,
|
|
CONCAT(u.first_name, ' ', u.last_name) as email_tpl_author,
|
|
DATE_FORMAT(eet.email_tpl_date_created, '%d/%m/%Y %Hh%i') AS email_tpl_date_created
|
|
FROM event_email_template eet
|
|
LEFT JOIN event_email_template_type eett
|
|
ON eett.email_type_id = eet.email_type_id
|
|
LEFT JOIN user u
|
|
ON u.user_id = eet.email_tpl_author
|
|
WHERE
|
|
eet.email_tpl_status IN(1,2) ";
|
|
|
|
if(isset($data_source['order']) && !empty($data_source['order'])) { // here order processing
|
|
if(isset($data_source['order']['0']['dir']) && !empty($data_source['order']['0']['dir'])) {
|
|
if(isset($this->column_order[$data_source['order']['0']['column']]))
|
|
$_ordey_by .= " ORDER BY ".$this->column_order[$data_source['order']['0']['column']]." ".$data_source['order']['0']['dir'];
|
|
} else {
|
|
$_ordey_by .= " ORDER BY ".key($this->order)." ".$this->order[key($this->order)];
|
|
}
|
|
} else if(isset($this->order) && !empty($this->order)) {
|
|
$_ordey_by .= " ORDER BY ".key($this->order)." ".$this->order[key($this->order)];
|
|
}
|
|
|
|
$_ordey_by .= ($_ordey_by!="")?", ":" ORDER BY ";
|
|
$_ordey_by .= " custom_order DESC";
|
|
|
|
return array("query"=>$_query.$_search.$_ordey_by, "data" =>$data);
|
|
}
|
|
|
|
public function get_datatables($data_source){
|
|
$_query = $this->_get_datatables_query($data_source);
|
|
if($data_source['length'] != -1){
|
|
$_query["query"] .= " LIMIT ".$data_source['start'].", ".$data_source['length'];
|
|
}
|
|
return $this->db->query($_query["query"], $_query["data"])->result();
|
|
}
|
|
|
|
public function count_filtered($data_source){
|
|
$_query = $this->_get_datatables_query($data_source);
|
|
return $this->db->query($_query["query"], $_query["data"])->num_rows();
|
|
}
|
|
|
|
public function count_all($data_source){
|
|
$_query = $this->_get_datatables_query($data_source);
|
|
return $this->db->query($_query["query"], $_query["data"])->num_rows();
|
|
}
|
|
|
|
public function create_email_template($data){
|
|
//unset default template if the template to be added is also a default one
|
|
if($data["email_tpl_creation_status"] === "DEFAULT" && $data["email_tpl_status"] == 2) {
|
|
$this->db->where("email_tpl_creation_status", $data["email_tpl_creation_status"]);
|
|
$this->db->where("email_tpl_status", $data["email_tpl_status"]);
|
|
$this->db->where("email_type_id", $data["email_type_id"]);
|
|
$this->db->update("event_email_template", array("email_tpl_creation_status" => "CUSTOMIZED"));
|
|
}
|
|
$this->db->insert("event_email_template", $data);
|
|
if($this->db->affected_rows()){
|
|
return $this->db->insert_id();
|
|
}
|
|
return false;
|
|
}
|
|
|
|
public function update_email_template($email_tpl_id, $data){
|
|
//unset default template if the template to be replaced is also a default one
|
|
if($data["email_tpl_creation_status"] === "DEFAULT" && $data["email_tpl_status"] == 2) {
|
|
$this->db->where("email_tpl_creation_status", $data["email_tpl_creation_status"]);
|
|
$this->db->where("email_tpl_status", $data["email_tpl_status"]);
|
|
$this->db->where("email_type_id", $data["email_type_id"]);
|
|
$this->db->update("event_email_template", array("email_tpl_creation_status" => "CUSTOMIZED"));
|
|
}
|
|
$this->db->where("email_tpl_id", $email_tpl_id);
|
|
$update = $this->db->update("event_email_template", $data);
|
|
if($update){
|
|
return true;
|
|
}
|
|
return false;
|
|
}
|
|
|
|
public function check_template_dependencies($email_tpl_id){
|
|
// //check dependencies
|
|
// $this->db->select("email_tpl_id");
|
|
// $this->db->where("email_tpl_id", $email_tpl_id);
|
|
// $this->db->where("email_map_status", 1);
|
|
// $check = $this->db->get("event_email_template_map")->num_rows();
|
|
// if($check){
|
|
// return true;
|
|
// }
|
|
// return false;
|
|
}
|
|
|
|
public function delete_email_tempalate($email_tpl_id){
|
|
$this->db->where("email_tpl_id", $email_tpl_id);
|
|
$this->db->update("event_email_template", array("email_tpl_status" => 0));
|
|
if($this->db->affected_rows()){
|
|
return true;
|
|
}
|
|
return false;
|
|
}
|
|
|
|
public function get_email_template_content($email_tpl_id){
|
|
$this->db->select("email_tpl_detail,email_tpl_subject");
|
|
$this->db->where("email_tpl_id",$email_tpl_id);
|
|
$result = $this->db->get("event_email_template")->result();
|
|
if($result){
|
|
return $result;
|
|
}
|
|
return false;
|
|
}
|
|
|
|
public function check_duplicate_template_name($email_tpl_id="", $email_tpl_name){
|
|
$this->db->select("email_tpl_id");
|
|
if($email_tpl_id !="") {
|
|
$this->db->where("email_tpl_id !=", $email_tpl_id);
|
|
}
|
|
$this->db->where("email_tpl_name", $email_tpl_name);
|
|
$this->db->where("email_tpl_status !=", 0);
|
|
$result = $this->db->get("event_email_template")->num_rows();
|
|
|
|
if($result > 0){
|
|
return true;
|
|
}
|
|
return false;
|
|
}
|
|
|
|
public function get_subscribers_data($subscriber){
|
|
$result = $this->db->query("SELECT
|
|
u.user_id as subscriber_id,
|
|
u.email_address,
|
|
CONCAT(u.first_name, ' ', u.last_name) AS subscriber,
|
|
u.first_name AS subs_prenom,
|
|
u.last_name AS subs_nom
|
|
FROM
|
|
user u
|
|
WHERE
|
|
user_id = ?
|
|
LIMIT 1", array($subscriber));
|
|
|
|
if ($result->num_rows() > 0) {
|
|
return $result->result_array()[0];
|
|
}
|
|
return false;
|
|
}
|
|
|
|
public function list_email_type(){
|
|
$result = $this->db->select("email_type_id, email_type")
|
|
->where("email_type_status",1)
|
|
->order_by("sort_order", "asc")
|
|
->get("event_email_template_type")->result();
|
|
return $result;
|
|
}
|
|
|
|
public function list_email_type_with_default_setting(){
|
|
$result = $this->db->select("email_type_id, email_type")
|
|
->where("email_type_status",1)
|
|
->where_not_in("email_type_id", array(1,2,4, 5, 6 ,7, 8))
|
|
->get("event_email_template_type")->result();
|
|
return $result;
|
|
}
|
|
|
|
public function typeahead_email_template($data){
|
|
$this->db->select("email_tpl_id, email_tpl_name AS name");
|
|
$this->db->where("email_tpl_status", 2);
|
|
$this->db->where("email_type_id", $data["email_type_id"]);
|
|
$this->db->like("email_tpl_name", $data["search"]);
|
|
$this->db->limit(10);
|
|
$this->db->order_by("email_tpl_id", "desc");
|
|
return $this->db->get("event_email_template")->result();
|
|
}
|
|
|
|
public function typeahead_event($data){
|
|
$where = (isset($data["events_ids"]) && !empty($data["events_ids"]))?" AND eetmp.event_id != ".$this->db->escape($data["events_ids"]):"";
|
|
return $this->db->query("
|
|
SELECT event_id, title AS name,
|
|
DATE_FORMAT(reservation_start_date, '%d/%m/%Y %Hh%m') AS reservation
|
|
FROM
|
|
event
|
|
WHERE
|
|
back_office_status IN(1,2,3)
|
|
AND
|
|
title LIKE '%".$this->db->escape_like_str($data["search"])."%' ESCAPE '!'
|
|
AND
|
|
event_id NOT IN(
|
|
SELECT eetmp.event_id
|
|
FROM event_email_template_mapping eetmp, event_email_template eet
|
|
WHERE eet.email_type_id = '".$this->db->escape_str($data["email_type_id"])."' AND eetmp.email_mapping_status = 1 ".$where."
|
|
)
|
|
ORDER BY event_id desc
|
|
LIMIT 10
|
|
")->result();
|
|
}
|
|
|
|
public function check_reset_time($email_schedule_id){
|
|
return $this->db->query("SELECT ees.email_schedule_id
|
|
FROM event_email_schedule ees
|
|
LEFT JOIN event_schedule es ON es.event_schedule_id = ees.event_schedule_id
|
|
WHERE ees.email_schedule_date <= NOW()
|
|
AND es.start_date_time >= NOW()
|
|
AND ees.email_schedule_status = 1
|
|
AND ees.email_schedule_id = ?
|
|
AND es.back_office_status IN(2,3)
|
|
ORDER BY ees.email_schedule_id DESC
|
|
LIMIT 1
|
|
", array($email_schedule_id))->num_rows();
|
|
}
|
|
|
|
public function get_current_event_email_template($event_id, $email_type_id, $withcontent=false, $custom_tpl_id = null){
|
|
|
|
//check if email is disabled
|
|
$not_disabled = $this->db->query("
|
|
select status
|
|
from event_schedule_email_status
|
|
where event_id = ?
|
|
and email_type_id = ?
|
|
and status = 1
|
|
limit 1
|
|
", array($event_id, $email_type_id))->num_rows();
|
|
|
|
if ($custom_tpl_id !== null) {
|
|
$email_type_id = $custom_tpl_id;
|
|
}
|
|
|
|
if($not_disabled > 0){
|
|
|
|
$select = "";
|
|
if($withcontent){
|
|
$select = ",
|
|
(CASE
|
|
WHEN (eet.email_tpl_surheader IS NULL OR eet.email_tpl_surheader = '') THEN eet.email_tpl_detail
|
|
ELSE CONCAT(eet.email_tpl_surheader,' ', eet.email_tpl_detail)
|
|
END) AS email_tpl_detail, eet.email_tpl_subject ";
|
|
}
|
|
//check if there is an assigned template to this event
|
|
$eventTPL = $this->db->query("
|
|
SELECT
|
|
eet.email_tpl_id,
|
|
eet.email_type_id
|
|
".$select."
|
|
FROM event_email_template_mapping_by_event_schedule emap
|
|
LEFT JOIN event_email_template eet
|
|
ON emap.email_tpl_id = eet.email_tpl_id
|
|
WHERE
|
|
emap.event_id = ?
|
|
AND emap.event_id IN(
|
|
SELECT
|
|
DISTINCT eses.event_id
|
|
FROM event_schedule_email_status eses
|
|
WHERE eses.event_id = ?
|
|
AND eses.status = 1
|
|
AND eses.email_type_id = ?
|
|
)
|
|
AND emap.is_active = 1
|
|
AND eet.email_type_id = ?
|
|
AND eet.email_tpl_status = 2
|
|
LIMIT 1
|
|
", array($event_id, $event_id, $email_type_id, $email_type_id));
|
|
|
|
if ($eventTPL->num_rows()) {
|
|
return $eventTPL->row();
|
|
}
|
|
|
|
/*Check if there is an assigned email template for the event schedule*/
|
|
$eventSchedTPL = $this->db->query("
|
|
SELECT
|
|
eet.email_tpl_id,
|
|
eet.email_type_id
|
|
".$select."
|
|
FROM event_email_template eet
|
|
WHERE
|
|
eet.email_tpl_status = 2
|
|
AND eet.email_type_id = ?
|
|
AND eet.email_type_id IN(
|
|
SELECT
|
|
DISTINCT eses.email_type_id
|
|
FROM event_schedule_email_status eses
|
|
WHERE eses.event_id = ?
|
|
AND eses.status = 1
|
|
AND eses.email_type_id = ?
|
|
)
|
|
AND eet.email_tpl_creation_status = 'DEFAULT'
|
|
LIMIT 1
|
|
", array($email_type_id, $event_id, $email_type_id));
|
|
if ($eventSchedTPL->num_rows()) {
|
|
return $eventSchedTPL->row();
|
|
}
|
|
/*Get globally set default email template for the email type*/
|
|
$defaultTPL = $this->db->query("
|
|
SELECT
|
|
eet.email_tpl_id,
|
|
eet.email_type_id
|
|
".$select."
|
|
FROM event_email_template eet
|
|
WHERE
|
|
eet.email_tpl_status = 2
|
|
AND eet.email_type_id = ?
|
|
AND eet.email_tpl_creation_status = 'DEFAULT'
|
|
LIMIT 1
|
|
", array($email_type_id));
|
|
|
|
if ($defaultTPL->num_rows()) {
|
|
return $defaultTPL->row();
|
|
}
|
|
}
|
|
return array();
|
|
}
|
|
|
|
public function get_default_template($email_template_id){
|
|
$default_template = $this->db->select("email_type_subject, email_type_template")
|
|
->from('event_email_template_type')
|
|
->where('email_type_id', $email_template_id)
|
|
->where('email_type_status', 1)
|
|
->limit(1)
|
|
->get()
|
|
->row();
|
|
|
|
if($default_template){
|
|
return $default_template;
|
|
} else return false;
|
|
}
|
|
|
|
public function get_default_template_by_email_type_id($email_type_id){
|
|
$default_template = $this->db->select("*")
|
|
->from('event_email_template')
|
|
->where('email_type_id', $email_type_id)
|
|
->where('email_tpl_creation_status', 'DEFAULT')
|
|
->where('email_tpl_status', 2)
|
|
->limit(1)
|
|
->get()
|
|
->row();
|
|
|
|
if($default_template){
|
|
return $default_template;
|
|
} else return false;
|
|
}
|
|
|
|
public function get_customized_template_by_email_type_id($email_type_id){
|
|
$default_template = $this->db->select("*")
|
|
->from('event_email_template')
|
|
->where('email_type_id', $email_type_id)
|
|
->where('email_tpl_creation_status', 'CUSTOMIZED')
|
|
->where('email_tpl_status', 2)
|
|
->limit(1)
|
|
->get()
|
|
->row();
|
|
|
|
if($default_template){
|
|
return $default_template;
|
|
} else return false;
|
|
}
|
|
|
|
public function get_default_tmplt($email_template_id){
|
|
$default_template = $this->db->select("email_tpl_subject, email_tpl_detail")
|
|
->from('event_email_template')
|
|
->where('email_type_id', $email_template_id)
|
|
->where('email_tpl_status', 2)
|
|
->where('email_tpl_creation_status', "DEFAULT")
|
|
->limit(1)
|
|
->get()
|
|
->row();
|
|
|
|
if($default_template){
|
|
return $default_template;
|
|
} else return false;
|
|
}
|
|
|
|
public function upload_default_email_template($email_template){
|
|
$this->db->where('email_type_status',1);
|
|
$this->db->update_batch('event_email_template_type', $email_template, 'email_type_id');
|
|
$this->db->trans_complete();
|
|
return ($this->db->trans_status() === FALSE)? FALSE:TRUE;
|
|
}
|
|
}
|
|
|