MasterClass/application/models/Event_email_template_mapping_model.php

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

class Event_email_template_mapping_model extends CI_Model {

    function __construct(){
        parent::__construct();
        $this->table_mapping = "event_email_template_mapping";
        $this->table_event = "event";
    }

   public function update_set_default_templates( $event_id )
   {
      $this->db->where("event_id", $event_id );
      $this->db->update($this->table_event, array("status" => $this->input->post('default') ));
      return $this->db->affected_rows();
   }
   public function get_set_default_templates( $event_id )
   {

         $query = $this->db->select('status')
            ->from($this->table_event)
            ->where('event_id', $event_id)
            ->get()
            ->result();

        return $query;
   }
   public function remove_template_event_schedules($email_mapping_id)
   {
      $this->db->where("email_mapping_id", $email_mapping_id);
      $this->db->update($this->table_mapping, array("email_mapping_status" => '0'));
      return $this->db->affected_rows();
   }
   public function assign_template_events()
   {
       //template-id-value=7&event-id-value=219&templates_id=Email+de+Rappel
        $client_session = $this->session->userdata('logged_in');
        $user_id = $client_session['user_id'];
        $this->db->insert($this->table_mapping,
        array(
             'event_id' => $_POST['event-id-value'],
             'email_tpl_id' => $_POST['template-id-value'],
             'use_as_default' => 1,
             'email_map_author' =>  $user_id,
             'email_map_date_created' => date("Y-m-d H:i:s", strtotime('today GMT')),
             'email_mapping_status' =>  1
            ));
        return $this->db->insert_id();

   }
   public function search_by_template_ids($email_type_id)
   {

        $result = $this->db->query("SELECT email_tpl_id, email_tpl_name FROM event_email_template WHERE email_type_id = ?
            AND email_tpl_status = 2
             AND email_tpl_creation_status != 'DEFAULT'
            ", array($email_type_id));
        $arr = [];
        if ($result->num_rows() > 0) {
             foreach ($result->result_array() as $key => $value) {
                array_push($arr, array(
                    "template_id" => $value['email_tpl_id'],
                    "template_name" => $value['email_tpl_name']
                ));
             }
        }
        return $arr;
    }
    public function get_table_event($event_id)
    {
        $result = $this->db->query("
           SELECT
            type.email_type_id,
            type.email_type as template_type,
            (SELECT
                GROUP_CONCAT(
                        CONCAT(
                            eetm.email_mapping_id,'_',
                            eetm.email_tpl_id,'_',

                            eet.email_tpl_name, '_',
                            (CASE
                                WHEN  eetm.use_as_default = 1 THEN 'OUI'
                                ELSE 'NON'
                             END)
                        )
                        ORDER BY eetm.email_tpl_id SEPARATOR ';;') as assigned_tempalate
                FROM event_email_template_mapping eetm
                LEFT JOIN event_email_template eet ON eet.email_tpl_id =  eetm.email_tpl_id
                WHERE eet.email_type_id = type.email_type_id
                AND eet.email_tpl_status = 2
                AND eetm.email_mapping_status = 1
                AND eetm.event_id = ?
                LIMIT 1
            ) AS assigned_tempalate

            FROM event_email_template_type as type
            WHERE
                type.email_type_status = 1
             ORDER BY
                type.sort_order ASC
        ", array($event_id));

        if ($result->num_rows() > 0)
        {
            $res = [];//"assigned_tempalate"
            $x = 0;
            foreach ($result->result_array() as $key => $value) {
                $splits =  explode('_',$value['assigned_tempalate'] );
                $default =  isset($splits[3]) ? explode(';;',$splits[3]) : ""  ;
                array_push($res,
                    array(
                        'no' => ++$x,
                        'email_type_id' => $value['email_type_id'],
                        'template_type' => $value['template_type'],
                        'default' => (  isset($default[0]) ? $default[0] : "" ) ,
                        'template_name' => (  isset($splits[2]) ? $splits[2] : "" ),
                        'template_id' => (  isset($splits[1]) ? $splits[1] : "" ) ,
                        'email_mapping_id' => (  isset($splits[0]) ? $splits[0] : "" )
                    )
                );
            }
            return array( 'data' => $res );
        }
    }
}