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

class Super_admin_classroom_model extends CI_Model {

    /**
     * @Function            :   person_info_by_person_id
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-21 08:00 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   Select person information with url 
     *
     * @parameter           :   $person_id
     * @Return              :   single row
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> schedule_classroom()
     *                          super_admin_classroom -> publish_classroom_schedule()
     *                          super_admin_classroom -> validate_classroom_record_status()
     *                          super_admin_classroom -> check_feature_for_schedule_class_validation()
     *                          super_admin_classroom -> select_feature_information()
     *                          super_admin_classroom -> upcoming_class()
     *                          super_admin_classroom -> current_class()
     *                          super_admin_classroom -> previous_class()
     * */
    public function person_info_by_person_id($person_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_person as p, "
                . "     tbl_url as u "
                . " WHERE "
                . "         p.person_id = '$person_id' "
                . "     AND u.url_id = p.url_id ";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    /**
     * @Function            :   select_talhatraining_active_courses
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-21 08:00 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   Select person information with url 
     *
     * @parameter           :   
     * @Return              :   multiple result
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> upcoming_class()
     *                          
     * */
    public function select_talhatraining_active_courses() {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_course as c, "
                . "     tbl_url as u "
                . " WHERE "
                . "        c.course_status = 1 "
                . "     AND u.url_id = c.url_id ";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_tt_resource_person_active_courses() {

        $data = $this->select_tt_resource_person();
        $resource_person_id_list = $data['resource_person_id_list'];
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_course as c, "
                . "     tbl_url as u "
                . " WHERE "
                . "        (c.course_status = 1 ";
        if ($resource_person_id_list) {
            $sql .= "     OR (c.resource_person_id IN ($resource_person_id_list) AND c.course_status = 4 )";
        }
        $sql .= " )";
        $sql .= "     AND u.url_id = c.url_id "
                . " GROUP BY c.course_id";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    /**
     * @Function            :   select_tt_resource_person
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-02-24 11:23 PM   |
     * @Updated Date Time   :
     * @Updated By          :   
     *
     * @Works               :   select_tt_resource_person
     *
     * @parameter           :   
     * @Return              :   multiple result
     * @Redirect            :   no
     *
     * @External call       :   
     *                          
     * */
    public function select_tt_resource_person() {
        $data = array();
        $resource_person_array = array();
        $data['tt_resource_person'] = $this->select_talhatraining_active_resource_person();
        foreach ($data['tt_resource_person'] as $value) {
            $resource_person_array[] = $value->resource_person_id;
        }
        $data['resource_person_id_list'] = implode(',', $resource_person_array);
        return $data;
    }

    /**
     * @Function            :   select_resource_person_courses
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-21 08:00 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   Select person information with url 
     *
     * @parameter           :   
     * @Return              :   multiple result
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> upcoming_class()
     *                          
     * */
    public function select_resource_person_courses($resource_person_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_course as c, "
                . "     tbl_url as u "
                . " WHERE "
                . "         ( c.course_status = 1 OR c.course_status = 4 )"
                . "     AND c.resource_person_id = '$resource_person_id' "
                . "     AND u.url_id = c.url_id ";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    /**
     * @Function            :   select_resource_person_information
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-21 08:00 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   Select person information with url 
     *
     * @parameter           :   $resource_person_id
     * @Return              :   single row
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> schedule_classroom()
     *                          
     * */
    public function select_resource_person_information($resource_person_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_person as p, "
                . "     tbl_resource_person as rp, "
                . "     tbl_url as u "
                . " WHERE "
                . "         rp.resource_person_id = '$resource_person_id' "
                . "     AND rp.person_id = p.person_id "
                . "     AND u.url_id = p.url_id ";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    /**
     * @Function            :   select_talhatraining_active_resource_person
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-21 08:00 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   Select talhatraining active resource person 
     *
     * @parameter           :   NONE
     * @Return              :   multiple row as a $result
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> schedule_classroom()
     * @Internal call       :   check_for_tt_class_not_held()
     *
     * */
    public function select_talhatraining_active_resource_person() {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_person as p, "
                . "     tbl_resource_person as rp,"
                . "     tbl_url as u"
                . " WHERE "
                . "         rp.resource_person_type = 1 "
                . "     AND rp.feature_price_id != ''"
                . "     AND rp.person_id = p.person_id "
                . "     AND p.url_id = u.url_id "
                . "     AND p.person_status = 1";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    /**
     * @Function            :   select_resource_person_under_talhatraining_feature
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-21 08:00 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   Select talhatraining active resource person whose are holding talhatraining feature
     *
     * @parameter           :   $tt_resource_person
     * @Return              :   $tt_resource_person
     * @Redirect            :   no
     *
     * @External call       :   
     * @Internal call       :   
     * 
     * */
    public function select_resource_person_under_talhatraining_feature($tt_resource_person) {
        foreach ($tt_resource_person as $key => $v_resource_person) {
            $feature_price_id = array();
            $feature_price_id = explode(',', $v_resource_person->feature_price_id);
            $feature_info = $this->sa_feature_model->select_active_feature_by_feature_id($feature_price_id[0]);
            if ($feature_info->feature_price_talhatraining == 2) {
                unset($tt_resource_person[$key]);
            }
        }
        return $tt_resource_person;
    }

    /**
     * @Function            :   talhatraining_active_resource_person_info
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-21 08:00 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   Select talhatraining active resource person  information by person id
     *
     * @parameter           :   NONE
     * @Return              :   single row of a person info
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> schedule_classroom()
     *
     * */
    public function talhatraining_active_resource_person_info($person_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_person as p, "
                . "     tbl_resource_person as rp,"
                . "     tbl_url as u"
                . " WHERE "
                . "         rp.resource_person_type = 1 "
                . "     AND p.person_id = '$person_id' "
                . "     AND rp.person_id = p.person_id "
                . "     AND p.url_id = u.url_id "
                . "     AND p.person_status = 1";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    /**
     * @Function            :   select_talhatraining_active_course_category
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-21 08:00 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   Select talhatraining public/active course category
     *
     * @parameter           :   NONE
     * @Return              :   multiple row as a $result
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> schedule_classroom()
     *
     * */
    public function select_talhatraining_active_course_category() {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_course_category as cc "
                . " WHERE "
                . "         cc.course_category_status = 1";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    /**
     * @Function            :   select_talhatraining_category_course_by_category_id
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-21 08:00 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   Select talhatraining published course category by category_id
     *
     * @parameter           :   $category_id
     * @Return              :   multiple row as a $result
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> select_category_course()
     *
     * */
    public function select_talhatraining_category_course_by_category_id($category_id) {
        $sql = "SELECT *, c.resource_person_id as c_resource_person_id"
                . " FROM "
                . "     tbl_course as c "
                . " WHERE "
                . "         c.course_category_id = '$category_id'"
                . "     AND (c.course_status = 1 OR c.course_status = 4) "
                . " GROUP BY c.course_id";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function check_student_in_multiple_batch($course_batch_id, $batch_id_list, $batch_student_array) {
        $result = array();
        $data['student_id'] = array();
        $data['batch_id'] = array();
        foreach ($batch_student_array as $value) {
            $sql = "SELECT * "
                    . " FROM "
                    . "     tbl_course_batch as cb "
                    . " WHERE FIND_IN_SET($value,cb.student_id) > 0"
                    . "     AND course_batch_id != $course_batch_id";
            if ($batch_id_list) {
                $sql .= "     AND course_batch_id IN ($batch_id_list)";
            } else {
                $sql .= "     AND course_batch_id IN (0)";
            }
            $result = $this->db->query($sql)->result();
            if ($result) {
                $data['student_id'][] = $value;
            }
        }
        return $data;
    }

    /**
     * @Function            :   select_talhatraining_course
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-21 08:00 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   Select talhatraining published course 
     *
     * @parameter           :   
     * @Return              :   multiple row as a $result
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> select_talhatraining_course()
     *
     * */
    public function select_talhatraining_course() {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_course as c "
                . " WHERE "
                . "         c.course_status = 1 ";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    /**
     * @Function            :   select_class_session_number
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-21 10:40 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   Select course classroom session number by course_id
     *
     * @parameter           :   $course_id
     * @Return              :   single row of course last created classroom
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> select_category_course()
     *
     * */
    public function select_class_session_number($course_id) {
        $sql = "SELECT * FROM tbl_classroom as c WHERE c.course_id = '$course_id' ORDER BY c.classroom_id DESC";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    /**
     * @Function            :   save_class_schedule_information
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-21 10:40 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   save class schedule information
     *
     * @parameter           :   
     * @Return              :   newly created classroom_id
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> schedule_classroom()
     *
     * */
    public function save_class_schedule_information() {
        $classroom_data = array();
        $classroom_data['classroom_title'] = $this->input->post('classroom_title', true);
        $classroom_data['url_id'] = $this->url_model->create_classroom_url($classroom_data['classroom_title']);
        $classroom_data['person_id'] = $this->session->userdata('person_id');
        $classroom_data['course_id'] = $course_id = $this->input->post('selected_course_id', true);
        $course_batch_id = $this->input->post('course_batch_id', TRUE);
        $classroom_data['course_batch_id'] = implode(',', $course_batch_id);
        $classroom_data['classroom_type'] = $this->input->post('classroom_type', true);
        $classroom_data['classroom_instruction'] = $this->input->post('classroom_instruction', true);
        $classroom_data['class_session_number'] = $this->input->post('class_session_number', true);
        $classroom_data['classroom_status'] = '7';

        $webcam = $this->input->post('webcam', true);
        $audio = $this->input->post('audio', true);
        if ($webcam && $audio) {
            $classroom_data['classroom_type'] = 1;
        } else if ($webcam && !$audio) {
            $classroom_data['classroom_type'] = 2;
        } else if (!$webcam && $audio) {
            $classroom_data['classroom_type'] = 3;
        } else if (!$webcam && !$audio) {
            $classroom_data['classroom_type'] = 4;
        }
        $classroom_data['classroom_record_status'] = $this->input->post('classroom_record_status', true);
        if (!$classroom_data['classroom_record_status']) {
            $classroom_data['classroom_record_status'] = 2;
        }
        $class_date = $this->input->post('class_date', true);
        $class_start_time = $class_date . ' ' . date('H:i:s', strtotime($this->input->post('class_start_time', true)));
        $classroom_data['class_duration'] = $this->input->post('duration', true);
        $class_end_time = strtotime($class_start_time) + ($classroom_data['class_duration'] * 60);

        $classroom_data['class_start_time'] = date('Y-m-d H:i:s', strtotime("$class_start_time"));
        $classroom_data['class_end_time'] = date('Y-m-d H:i:s', $class_end_time);

        $sql = "SELECT * FROM tbl_classroom as c WHERE c.course_id = '$course_id' ORDER BY c.classroom_id DESC";
        $result = $this->db->query($sql)->row();
        if ($result->class_session_number) {
            $classroom_data['class_session_number'] = $result->class_session_number + 1;
        } else {
            $classroom_data['class_session_number'] = 1;
        }
        $classroom_data['classroom_teacher_join_status'] = 6;
        $this->db->insert('tbl_classroom', $classroom_data);
        return $this->db->insert_id();
    }

    /**
     * @Function            :   assign_class_student
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   01-06-2016 09:04 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   Assign classroom student from selected batches
     *
     * @parameter           :   $classroom_id
     * @Return              :   no
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> schedule_classroom()
     *
     * */
    public function assign_class_student($classroom_id) {
        $course_batch_id = $this->input->post('course_batch_id', TRUE);
        foreach ($course_batch_id as $value) {
            $batch_info = $this->sa_class_model->select_course_batch_info($value);
            $batch_student = explode(',', $batch_info->student_id);
            foreach ($batch_student as $student_value) {
                $classroom_student_data = array();
                $classroom_student_data['classroom_id'] = $classroom_id;
                $classroom_student_data['student_id'] = $student_value;
                $classroom_student_data['classroom_student_join_status'] = 6;
                $this->db->insert('tbl_classroom_student', $classroom_student_data);
            }
        }
    }

    /**
     * @Function            :   update_assigned_class_student
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   01-06-2016 09:04 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   Assign classroom student from selected batches
     *
     * @parameter           :   $classroom_id
     * @Return              :   no
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> schedule_classroom()
     *
     * */
    public function make_batch_student_list($batch_id_list) {
        $course_batches = $this->sa_class_model->select_course_selected_batch_info($batch_id_list);
        foreach ($course_batches as $k => $v_b) {
            $student_list1[$k] = $v_b->student_id;
            $student_list2 = explode(',', $student_list1[$k]);

            foreach ($student_list2 as $k => $v) {
                $student_list[] = $v;
            }
        }
        return $student_list;
    }

    public function update_assigned_class_student($classroom_info) {
        $classroom_id = $classroom_info->classroom_id;
        $course_batch_id = $classroom_info->course_batch_id;

        $course_new_batch_id = $this->input->post('course_batch_id', TRUE);
        $course_new_batch_id = implode(',', $course_new_batch_id);

        $pre_student_list = $this->make_batch_student_list($course_batch_id);
        $new_student_list = $this->make_batch_student_list($course_new_batch_id);

        foreach ($pre_student_list as $k => $value) {
            if (!in_array($value, $new_student_list)) {
                //delete
                $sql = "DELETE FROM tbl_classroom_student WHERE student_id = '$value' AND classroom_id ='$classroom_id'";
                $this->db->query($sql);
            }
        }
        foreach ($new_student_list as $k => $value) {
            if (!in_array($value, $pre_student_list)) {
                //insert
                $classroom_student_data = array();
                $classroom_student_data['classroom_id'] = $classroom_id;
                $classroom_student_data['student_id'] = $value;
                $classroom_student_data['classroom_student_join_status'] = 6;
                $this->db->insert('tbl_classroom_student', $classroom_student_data);
            }
        }
    }

    /**
     * @Function            :   select_classroom_info_by_id
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-21 10:40 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   Select classroom information including classroom url
     *
     * @parameter           :   $classroom_id
     * @Return              :   single row of classroom info
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> cancel_class_schedule($classroom_id)
     * 
     * @Internal call       :   super_admin_classroom_model -> delete_classroom_schedule($classroom_id)
     *
     * */
    public function select_classroom_info_by_id($classroom_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_classroom as cr, "
                . "     tbl_url as u "
                . " WHERE "
                . "         cr.classroom_id = '$classroom_id' "
                . "     AND cr.url_id = u.url_id "
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    /**
     * @Function            :   select_classroom_info
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-21 10:40 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   Select classroom information including classroom url, course and resource person information
     *
     * @parameter           :   $classroom_id
     * @Return              :   single row of classroom info
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> schedule_classroom()
     *                                                   edit_schedule_classroom($classroom_url)
     *                                                   cancel_class_schedule($classroom_id)
     * 
     * @Internal call       :   
     *
     * */
    public function select_classroom_info1($classroom_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_classroom as cr, "
                . "     tbl_url as u, "
                . "     tbl_course as c, "
                . "     tbl_person as p "
                . " WHERE "
                . "         cr.classroom_id = '$classroom_id' "
                . "     AND cr.url_id = u.url_id "
                . "     AND cr.course_id = c.course_id "
                . "     AND p.person_id = cr.person_id "
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_classroom_info($classroom_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_classroom as cr, "
                . "     tbl_url as u "
                . " WHERE "
                . "         cr.classroom_id = '$classroom_id' "
                . "     AND cr.url_id = u.url_id "
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    /**
     * @Function            :   select_classroom_info_by_url
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-23 10:55 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   Select classroom information including classroom url, course and resource person information
     *
     * @parameter           :   $classroom_url
     * @Return              :   single row of classroom info
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> edit_schedule_classroom($classroom_url)
     *
     * */
    public function select_classroom_info_by_url($classroom_url) {
        $sql = "SELECT *, cr.url_id as cr_url_id"
                . " FROM "
                . "     tbl_classroom as cr, "
                . "     tbl_url as u, "
                . "     tbl_course as c, "
                . "     tbl_person as p "
                . " WHERE "
                . "         u.url = '$classroom_url' "
                . "     AND cr.url_id = u.url_id "
                . "     AND cr.course_id = c.course_id "
                . "     AND p.person_id = cr.person_id "
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_any_classroom_info_by_url($classroom_url) {
        $sql = "SELECT *, cr.url_id as cr_url_id"
                . " FROM "
                . "     tbl_classroom as cr, "
                . "     tbl_url as u, "
                . "     tbl_course as c, "
                . "     tbl_person as p "
                . " WHERE "
                . "         u.url = '$classroom_url' "
                . "     AND cr.url_id = u.url_id "
                . "     AND cr.course_id = c.course_id "
                . "     AND p.person_id = cr.person_id "
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    /**
     * @Function            :   select_talhatraining_upcoming_classes
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-21 10:40 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   Select talhatraining upcomming classroom, relative course and resource person information
     *
     * @parameter           :   $classroom_id
     * @Return              :   single row of classroom info
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> upcoming_class()
     *
     * */
    public function select_talhatraining_upcoming_classes($course_id_list, $start = NULL, $limit = NULL) {

        $now = date('Y-m-d H:i:s', strtotime(gmdate('Y-m-d H:i:s')));
        $current_date = date('Y-m-d', strtotime(gmdate('Y-m-d H:i:s'))) . ' 0:0:0';
        $next_date = date('Y-m-d', (strtotime(gmdate('Y-m-d H:i:s'))) + (60 * 60 * 24)) . ' 0:0:0';

        $sql = "SELECT *  "
                . " FROM "
                . "     tbl_url as u, "
                . "     tbl_classroom as cr, "
                . "     tbl_course as c, "
                . "     tbl_person as p "
                . " WHERE "
                . "         cr.course_id IN ($course_id_list 0) "
                . "     AND u.url_id = cr.url_id "
                . "     AND ((  cr.class_start_time > '$now' AND cr.classroom_status = 0)"
                . "             OR  cr.classroom_status = 7 ) "
                . "     AND c.course_id = cr.course_id "
                . "     AND p.person_id = cr.person_id "
                . " GROUP BY cr.classroom_id "
        ;


        $result = $this->db->query($sql)->result();
        return $result;
    }

    /**
     * @Function            :   select_talhatraining_current_classes
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-21 10:40 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   Select talhatraining current classroom, relative course and resource person information
     *
     * @parameter           :   $person_list, $start, $limit
     * @Return              :   Multiple row of classroom info
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> current_class()
     *
     * */
    public function select_talhatraining_current_classes($course_id_list, $start = NULL, $limit = NULL) {

        $now = date('Y-m-d H:i:s', strtotime(gmdate('Y-m-d H:i:s')));
        $current_date = date('Y-m-d', strtotime(gmdate('Y-m-d H:i:s'))) . ' 0:0:0';
        $next_date = date('Y-m-d', (strtotime(gmdate('Y-m-d H:i:s'))) + (60 * 60 * 24)) . ' 0:0:0';

        $sql = "SELECT * "
                . " FROM "
                . "     tbl_url as u, "
                . "     tbl_classroom as cr, "
                . "     tbl_course as c, "
                . "     tbl_person as p "
                . " WHERE "
                . "         cr.course_id IN ($course_id_list 0) "
                . "     AND u.url_id = cr.url_id "
                . "     AND (   '$now' BETWEEN cr.class_start_time  AND cr.class_end_time ) "
                . "     AND (cr.classroom_status = 1 OR cr.classroom_status = 0 OR cr.classroom_status = 3 ) "
                . "     AND c.course_id = cr.course_id "
                . "     AND p.person_id = cr.person_id "
                . " GROUP BY cr.classroom_id "
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    /**
     * @Function            :   select_talhatraining_previous_classes
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-21 10:40 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   Select talhatraining previous classroom, relative course and resource person information
     *
     * @parameter           :   $person_list, $start = NULL, $limit = NULL
     * @Return              :   single row of classroom info
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> previous_class()
     *
     * */
    public function select_talhatraining_previous_classes($course_id_list, $start = NULL, $limit = NULL) {

        $now = date('Y-m-d H:i:s', strtotime(gmdate('Y-m-d H:i:s')));
        $current_date = date('Y-m-d', strtotime(gmdate('Y-m-d H:i:s'))) . ' 0:0:0';
        $next_date = date('Y-m-d', (strtotime(gmdate('Y-m-d H:i:s'))) + (60 * 60 * 24)) . ' 0:0:0';

        $sql = "SELECT * "
                . " FROM "
                . "     tbl_url as u, "
                . "     tbl_classroom as cr, "
                . "     tbl_course as c, "
                . "     tbl_person as p "
                . " WHERE "
                . "         cr.course_id IN ($course_id_list 0) "
                . "     AND u.url_id = cr.url_id "
                . "     AND ((cr.class_end_time  < '$now' "
                . "     AND     (       cr.classroom_status = 2 "
                . "                 OR  cr.classroom_status = 4 "
                . "                 OR  cr.classroom_status = 5 "
                . "             )"
                . "           )	OR cr.classroom_status = 5"
                . "         ) "
                . "     AND c.course_id = cr.course_id "
                . "     AND p.person_id = cr.person_id "
                . " GROUP BY cr.classroom_id "
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    /**
     * @Function            :   check_for_tt_class_not_held
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-21 10:40 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   check and update to talhatraining not held class
     *
     * @parameter           :   
     * @Return              :   no
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> __construct()
     *
     * */
    public function check_for_tt_class_not_held() {
        $person_id = $this->session->userdata('person_id');
        $tt_course_list = $this->select_talhatraining_course();
        $now = date('Y-m-d H:i:s', strtotime(gmdate('Y-m-d H:i:s')) - $this->session->userdata('time_offset'));
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_classroom as cr "
                . "WHERE "
                . "         cr.class_end_time < '$now' "
                . "     AND cr.classroom_status = 0 "
                . "     AND (cr.course_id = '' ";
        foreach ($tt_course_list as $v_course) {
            $sql .= " OR cr.course_id ='$v_course->course_id'";
        }
        $sql .= " )";
        $result = $this->db->query($sql)->result();
        foreach ($result as $active_class) {
            $this->db->set('classroom_status', 4);
            $this->db->where('classroom_id', $active_class->classroom_id);
            $this->db->update('tbl_classroom');
        }
    }

    /**
     * @Function            :   delete_classroom_schedule
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-21 10:40 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   completely delete classroom by classroom_id
     *
     * @parameter           :   $classroom_id
     * @Return              :   no
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> delete_class_schedule($classroom_id)
     *
     * */
    public function delete_classroom_schedule($classroom_id) {
        $classroom_info = $this->select_classroom_info($classroom_id);
        $url_id = $classroom_info->url_id;

        $sql = "DELETE FROM tbl_classroom WHERE classroom_id = '$classroom_id'";
        $this->db->query($sql);

        $sql = "DELETE FROM tbl_url WHERE url_id = '$url_id'";
        $this->db->query($sql);

        $sql = "DELETE FROM tbl_classroom_student WHERE classroom_id = '$classroom_id'";
        $this->db->query($sql);
    }

    /**
     * @Function            :   cancel_classroom_schedule
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-21 10:40 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   completely delete classroom by classroom_id
     *
     * @parameter           :   $classroom_id
     * @Return              :   no
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> delete_class_schedule($classroom_id)
     *
     * */
    public function cancel_classroom_schedule($classroom_id) {
        $this->db->set('classroom_status', 5);
        $this->db->where('classroom_id', $classroom_id);
        $this->db->update('tbl_classroom');
    }

    /**
     * @Function            :   select_assigned_student
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-24 02:40 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   select assigned student of a classroom
     *
     * @parameter           :   $classroom_id
     * @Return              :   multiple row of classroom assigned student as $result
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> edit_schedule_classroom($classroom_url);
     *
     * */
    public function select_assigned_student($classroom_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_classroom_student as crs, tbl_person as p, tbl_student as s, tbl_url as u "
                . " WHERE "
                . "         crs.classroom_id = '$classroom_id' "
                . "     AND (crs.classroom_student_join_status = 6"
                . "     OR  crs.classroom_student_join_status = 5)"
                . "     AND s.student_id = crs.student_id "
                . "     AND p.person_id = s.person_id "
                . "     AND u.url_id = p.url_id "
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    /**
     * @Function            :   select_course_info_by_id
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-24 02:45 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   course information for classroom course
     *
     * @parameter           :   $classroom_id
     * @Return              :   multiple row of classroom assigned student as $result
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> edit_schedule_classroom($classroom_url);
     *                          super_admin_classroom -> current_class();
     *                          super_admin_classroom -> upcoming_class();
     *                          super_admin_classroom -> previous_class();
     *
     * */
    public function select_course_info_by_id($course_id) {
        $sql = "SELECT *, c.person_id "
                . " FROM "
                . "     tbl_course as c,"
                . "     tbl_url as u"
                . " WHERE "
                . "         c.course_id = '$course_id' "
                . "     AND u.url_id = c.url_id "
                . " GROUP BY c.course_id"
        ;
        $result = $this->db->query($sql)->row();
      return $result;
    }

    /**
     * @Function            :   person_class_schedule_by_date_time
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-24 02:45 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   person class schedule between $schedule_start_date_time and $schedule_end_date_time
     *
     * @parameter           :   $classroom_id, $schedule_start_date_time, $schedule_end_date_time
     * @Return              :   multiple row of classroom assigned student as $result
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> check_feature_for_schedule_class_validation();
     *
     * */
    public function person_class_schedule_by_date_time($classroom_id, $schedule_start_date_time, $schedule_end_date_time) {
        $sql = "SELECT *  "
                . " FROM "
                . "     tbl_classroom  "
                . "     WHERE  "
                . "         (   class_start_time BETWEEN '$schedule_start_date_time'  AND '$schedule_end_date_time'"
                . "         OR  class_end_time BETWEEN '$schedule_start_date_time'  AND '$schedule_end_date_time' ) "
                . "     AND classroom_id = '$classroom_id' "
                . "     AND (classroom_status = 0 OR classroom_status = 1)"
        ;

        $result = $this->db->query($sql)->row();
        return $result;
    }

    /**
     * @Function            :   person_class_schedule_by_course_list_and_date_time
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-24 02:45 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   person class schedule between $schedule_start_date_time and $schedule_end_date_time
     *
     * @parameter           :   $classroom_id, $schedule_start_date_time, $schedule_end_date_time
     * @Return              :   multiple row of classroom assigned student as $result
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> check_feature_for_schedule_class_validation();
     *
     * */
    public function person_class_schedule_by_course_list_and_date_time($classroom_id, $course_id_list, $schedule_start_date_time, $schedule_end_date_time) {
        $sql = "SELECT *  "
                . " FROM "
                . "     tbl_classroom  "
                . "     WHERE  "
                . "         (   class_start_time BETWEEN '$schedule_start_date_time'  AND '$schedule_end_date_time'"
                . "         OR  class_end_time BETWEEN '$schedule_start_date_time'  AND '$schedule_end_date_time' ) "
                . "     AND course_id IN ($course_id_list 0) "
                . "     AND classroom_id != '$classroom_id' "
                . "     AND (classroom_status = 0 OR classroom_status = 1)"
        ;

        $result = $this->db->query($sql)->result();
        return $result;
    }

    /**
     * @Function            :   person_class_schedule_by_course_list_and_date
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-24 02:45 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   person class schedule between $schedule_start_date_time and $schedule_end_date_time
     *
     * @parameter           :   $classroom_id, $course_id_list, $schedule_date
     * @Return              :   multiple row of classroom assigned student as $result
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> check_feature_for_schedule_class_validation();
     *
     * */
    public function person_class_schedule_by_course_list_and_date($classroom_id, $course_id_list, $schedule_date) {
        $schedule_start_date_time = date('Y-m-d H:i:s', strtotime($schedule_date . ' 00:00:00'));
        $schedule_end_date_time = date('Y-m-d H:i:s', strtotime($schedule_date . ' 23:59:59'));
        $sql = "SELECT *  "
                . " FROM "
                . "     tbl_classroom  "
                . "     WHERE  "
                . "         (   class_start_time BETWEEN '$schedule_start_date_time'  AND '$schedule_end_date_time'"
                . "         OR  class_end_time BETWEEN '$schedule_start_date_time'  AND '$schedule_end_date_time' ) "
                . "     AND course_id IN ($course_id_list 0) "
                . "     AND classroom_id != '$classroom_id' "
                . "     AND (classroom_status = 0 OR classroom_status = 1)"
        ;

        $result = $this->db->query($sql)->result();
        return $result;
    }

    /**
     * @Function            :   person_class_schedule_by_date_time
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-24 02:45 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   person class schedule between $schedule_start_date_time and $schedule_end_date_time
     *
     * @parameter           :   $classroom_id, $schedule_start_date_time, $schedule_end_date_time
     * @Return              :   multiple row of classroom assigned student as $result
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> check_feature_for_schedule_class_validation();
     *
     * */
    function person_class_schedule($person_id) {

        $sql = "SELECT *  "
                . " FROM "
                . "     tbl_classroom  "
                . "     WHERE  "
                . "     person_id = '$person_id' "
        ;

        $result = $this->db->query($sql)->result();
        return $result;
    }

    /**
     * @Function            :   select_course_registered_student
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-01-24 02:45 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   course registered student
     *
     * @parameter           :   $course_id
     * @Return              :   multiple row of classroom course registered student as $result
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> view_classroom_course_register_student();
     *
     * */
    public function select_course_registered_student($course_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_student as s,"
                . "     tbl_person as p, "
                . "     tbl_url as u "
                . " WHERE "
                . "         s.course_id = '$course_id'  "
                . "     AND p.person_id = s.person_id "
                . "     AND u.url_id = p.url_id "
                . " GROUP BY s.student_id "
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    /**
     * @Function            :   select_course_batches
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   01-06-2016 07:26 PM   |
     * @Updated Date Time   :
     * @Updated By          :   
     *
     * @Works               :   course registered student
     *
     * @parameter           :   $course_id
     * @Return              :   multiple row of classroom course batch as $result
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> select_classroom_course_batch();
     *
     * */
    public function select_course_batches($course_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_course_batch as cb,"
                . "     tbl_course as c "
                . " WHERE "
                . "         c.course_id = '$course_id'  "
                . "     AND c.course_id = cb.course_id "
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    /**
     * @Function            :   select_course_active_batches
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   01-06-2016 08:02 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   minimum one course batch 
     *                          course batch status need to be active or aproved(for batch created by tt teacher)
     *                          minimum one registered student in that batch
     *
     * @parameter           :   $course_id
     * @Return              :   multiple row of classroom course batch as $result
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> select_classroom_course_batch()
     *                          super_admin_classroom -> select_classroom_course_batch()
     *
     * */
    public function select_course_active_batches($course_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_course_batch as cb,"
                . "     tbl_course as c "
                . " WHERE "
                . "         c.course_id = '$course_id'  "
                . "     AND c.course_id = cb.course_id "
                . "     AND (cb.course_batch_status = 1 OR cb.course_batch_status = 4)"
                . "     AND cb.student_id != ''"
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    /**
     * @Function            :   select_course_active_batches
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   01-06-2016 08:02 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   course registered student
     *
     * @parameter           :   $course_id
     * @Return              :   multiple row of classroom course batch as $result
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> select_classroom_course_batch();
     * @External call       :   super_admin_classroom_model -> assign_class_student($classroom_id);
     *
     * */
    public function select_course_batch_info($course_batch_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_course_batch as cb "
                . " WHERE "
                . "         cb.course_batch_id = '$course_batch_id' "
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    /**
     * @Function            :   select_selected_batches_total_student
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   01-06-2016 08:02 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   course registered student
     *
     * @parameter           :   $course_batch_id_list
     * @Return              :   multiple row of classroom course batch as $result
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> select_classroom_course_batch();
     *
     * */
    public function select_selected_batches_total_student($course_batch_id_list) {
        $result = '';
        if ($course_batch_id_list) {
            $sql = "SELECT SUM(ifnull(LENGTH(student_id) - LENGTH(REPLACE(student_id, ',', '')) + 1,0)) AS total "
                    . " FROM "
                    . "     tbl_course_batch    "
                    . " WHERE "
                    . "     course_batch_id IN ($course_batch_id_list)"
            ;
            $result = $this->db->query($sql)->row();
        }
        return $result;
    }

    /**
     * @Function            :   select_selected_batches_total_student
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   01-06-2016 08:02 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   course registered student
     *
     * @parameter           :   $course_batch_id_list
     * @Return              :   multiple row of classroom course batch as $result
     * @Redirect            :   no
     *
     * @External call       :   super_admin_classroom -> select_classroom_course_batch();
     *
     * */
    public function select_student_info($student_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_student as s, "
                . "     tbl_person as p, "
                . "     tbl_url as u "
                . " WHERE "
                . "         s.student_id IN ($student_id)"
                . "     AND s.person_id = p.person_id "
                . "     AND u.url_id = p.url_id "
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_course_selected_batch_info($batch_id_list) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_course_batch as cb "
                . " WHERE ";
        if ($batch_id_list) {
            $sql .= "     course_batch_id IN ($batch_id_list)";
        } else {
            $sql .= "     course_batch_id IN (0)";
        }
        $result = $this->db->query($sql)->result();
        return $result;
    }

    /*
      -----------@@@RIPON@ Start add ClassRoom Part----------------------------------
     */

    /**
     * @Function            : save_classroom_information 
     * @Used IN             : @function-'super_Admin_classroom'     super_admin_classroom[@Controller] 
     * @Description         : save classroom information in datavase
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 15-03-2016 08:44 AM    
     * @Status              : available      
     * */
    function save_classroom_information() {
        $cData['person_id'] = $this->session->userdata('person_id');
        $cData['course_id'] = $this->input->post('hidden_course_id', TRUE);
        $cData['class_session_number'] = $this->get_class_session_number($cData['course_id']);
        $cData['classroom_title'] = preg_replace('/\s+/', ' ', trim($this->input->post('classroom_title', TRUE)));
        $cData['url_id'] = $this->url_model->create_classroom_url($cData['classroom_title']);
        $cData['classroom_instruction'] = preg_replace('/\s+/', ' ', trim($this->input->post('classroom_instruction', TRUE)));
        $class_date = $this->input->post('class_start_date', true);
        $class_start_time = $class_date . ' ' . date('H:i:s', strtotime($this->input->post('class_start_time', true)));
        $cData['class_duration'] = $this->input->post('class_duration', true);
        $class_end_time = strtotime($class_start_time) + ($cData['class_duration'] * 60);
        $cData['class_start_time'] = date('Y-m-d H:i:s', strtotime("$class_start_time"));
        $cData['class_end_time'] = date('Y-m-d H:i:s', $class_end_time);
        $cData['classroom_status'] = 0;
        return $this->user_course_model->save_information_in_database('tbl_classroom', $cData);
    }

    /**
     * @Function            : get_class_session_number 
     * @Used IN             : @function-'save_classroom_information'    this[@Model]
     * @Description         : get classroom session nmber
     * @Developed By        : Copied from 'Classroom_model'
     * @Start DateTime      : 15-03-2016 10:28 PM    
     * @Status              : available      
     * */
    function get_class_session_number($course_id) {
        $sql = "SELECT count(*) AS total_class
                FROM 
                    tbl_classroom AS cls
                WHERE cls.course_id = '$course_id'";
        $result = $this->db->query($sql)->row();
        return ++$result->total_class;
    }

    /**
     * @Function            : select_classroom_info_by_classroom_id 
     * @Used IN             : @function-'schedule_classroom'          super_admin_classroom[@Controller] 
     * @Used IN             : @function-'edit_schedule_classroom'     super_admin_classroom[@Controller] 
     * @Description         : get classroom informatiom using classroom ID
     * @Developed By        : Copied from 'Classroom-model'
     * @Start DateTime      : 15-03-2016 10:50 AM    
     * @Status              : available      
     * */
    function select_classroom_info_by_classroom_id($classroom_id) {
        $sql = "SELECT *, cls.url_id AS classroom_url_id 
                FROM 
                    tbl_classroom AS cls,
                    tbl_url AS u,
                    tbl_person AS p,    
                    tbl_course AS c
                WHERE 
                    cls.classroom_id = '$classroom_id'
                AND cls.url_id = u.url_id
                AND u.url_type = '11'
                AND cls.person_id = p.person_id
                AND cls.course_id = c.course_id";
        return $this->db->query($sql)->row();
    }

    /**
     * @Function            : select_classroom_info_by_classroom_url 
     * @Used IN             : @function-'classroom_review_and_publish'              super_admin_classroom[@Controller] 
     * @                    : @function-'edit_schedule_classroom'                   super_admin_classroom[@Controller] 
     * @                    : @function-'view_classroom_course_register_student'    super_admin_classroom[@Controller] 
     * @Description         : get classroom informatiom using classroom ID
     * @Developed By        : Copied from 'Classroom-model'
     * @Start DateTime      : 15-03-2016 10:50 AM    
     * @Status              : available      
     * */
    function select_classroom_info_by_classroom_url($classroom_url) {
        $sql = "SELECT * ,cls.url_id AS classroom_url_id,cls.person_id AS classroom_creator_id
                FROM 
                    tbl_classroom AS cls,
                    tbl_url AS u,
                    tbl_person AS p,
                    tbl_course AS c
                WHERE 
                    cls.url_id = u.url_id
                AND u.url = '$classroom_url'
                AND u.url_type = '11'
                AND cls.person_id = p.person_id
                AND cls.course_id = c.course_id";
        return $this->db->query($sql)->row();
    }

    /**
     * @Function            : update_class_schedule_information 
     * @Used IN             : @function-'edit_schedule_classroom'     super_admin_classroom[@Controller]
     * @Description         : update classroom information in database
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 16-03-2016 11:46 AM    
     * @Status              : available      
     * */
    public function update_class_schedule_information($classroom_info) {
        $cData['person_id'] = $classroom_info->person_id;
        $cData['course_id'] = $this->input->post('hidden_course_id', TRUE);
        $cData['class_session_number'] = $classroom_info->class_session_number;
        $cData['classroom_title'] = preg_replace('/\s+/', ' ', trim($this->input->post('classroom_title', TRUE)));
        $cData['url_id'] = $classroom_info->classroom_url_id;
        $cData['classroom_instruction'] = preg_replace('/\s+/', ' ', trim($this->input->post('classroom_instruction', TRUE)));
        $class_date = $this->input->post('class_start_date', true);
        $class_start_time = $class_date . ' ' . date('H:i:s', strtotime($this->input->post('class_start_time', true)));
        $cData['class_duration'] = $this->input->post('class_duration', true);
        $class_end_time = strtotime($class_start_time) + ($cData['class_duration'] * 60);
        $cData['class_start_time'] = date('Y-m-d H:i:s', strtotime("$class_start_time"));
        $cData['class_end_time'] = date('Y-m-d H:i:s', $class_end_time);
        $cData['classroom_status'] = 0;
        $this->user_course_model->update_information_in_database('tbl_classroom', 'classroom_id', $classroom_info->classroom_id, $cData);
        return $classroom_info->classroom_id;
    }

    /**
     * @Function            : get_total_classroom_in_single_date 
     * @Used IN             : @function-'check_classroom_start_date'     super_admin_classroom[@Controller]
     * @Description         : get total classroom in single date
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 16-03-2016 01:05 PM    
     * @Status              : available      
     * */
    function get_total_classroom_in_single_date($classroom_id, $class_start_date, $all_course) {
        $sql = "SELECT * 
                FROM 
                    tbl_classroom AS cls
                WHERE 
                        DATE(cls.class_start_time) = '$class_start_date'
                    AND cls.course_id IN ($all_course)
                    AND cls.classroom_status IN (0,1,3)";
        if ($classroom_id != '') {
            $sql.=" AND cls.classroom_id !='$classroom_id'";
        }
        return $this->db->query($sql)->result();
    }

    /**
     * @Function            : check_classroom_schedule 
     * @Used IN             : @function-'check_classroom_start_date'     super_admin_classroom[@Controller]
     * @Description         : get total classroom in single date
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 16-03-2016 01:30 PM    
     * @Status              : available      
     * */
    function check_classroom_schedule($classroom_id, $all_course, $class_start_time, $class_end_time) {
        $sql = "SELECT * 
                FROM 
                    tbl_classroom AS cls
                WHERE 
                    (((cls.class_start_time BETWEEN '$class_start_time' AND '$class_end_time') 
                    OR (cls.class_end_time BETWEEN '$class_start_time' AND '$class_end_time'))
                OR
                    (('$class_start_time' BETWEEN cls.class_start_time AND cls.class_end_time)
                    OR ('$class_end_time' BETWEEN cls.class_start_time AND cls.class_end_time)))
                    AND cls.course_id IN ($all_course)
                    AND cls.classroom_status IN (0,1,3)";
        if ($classroom_id != '') {
            $sql.=" AND cls.classroom_id !='$classroom_id'";
        }
        return $this->db->query($sql)->result();
    }

    /*
      -----------@@@RIPON@ End Add ClassRoom Part----------------------------------
     */

    /*
      -----------@@@RIPON@ Start view ClassRoom Part----------------------------------
     */

    /**
     * @Function            : get_upcomming_classroom 
     * @Used IN             : @function-'upcomming_class'    super_admin_classroom[@Controller]
     * @Description         : get upcomming classroom
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 16-03-2016 10:38 AM    
     * @Status              : available      
     * */
    function get_upcomming_classroom($todays_date) {
        $sql = "SELECT *, cls.url_id AS classroom_url_id
                FROM 
                    tbl_classroom AS cls,
                    tbl_url AS u,
                    tbl_person AS p,
                    tbl_course AS c
                WHERE 
                 cls.class_start_time > '$todays_date'
                AND cls.url_id = u.url_id
                AND cls.person_id = p.person_id
                AND cls.course_id = c.course_id
                AND cls.classroom_status IN (0,7)
                ORDER BY cls.class_start_time ASC";
        return $this->db->query($sql)->result();
    }

    /**
     * @Function            : get_upcomming_classroom 
     * @Used IN             : @function-'upcomming_class'    super_admin_classroom[@Controller]
     * @Description         : get upcomming classroom
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 16-03-2016 10:38 AM    
     * @Status              : available      
     * */
    function get_current_classroom($todays_date) {
        $sql = "SELECT *, cls.url_id AS classroom_url_id
                FROM 
                    tbl_classroom AS cls,
                    tbl_url AS u,
                    tbl_person AS p,
                    tbl_course AS c
                WHERE 
                (cls.class_start_time <= '$todays_date' AND cls.class_end_time >= '$todays_date')
                AND cls.url_id = u.url_id
                AND cls.person_id = p.person_id
                AND cls.course_id = c.course_id
                AND cls.classroom_status IN (0,1,3)
                ORDER BY cls.class_start_time ASC";
        return $this->db->query($sql)->result();
    }

    /**
     * @Function            : get_upcomming_classroom 
     * @Used IN             : @function-'upcomming_class'    super_admin_classroom[@Controller]
     * @Description         : get upcomming classroom
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 16-03-2016 10:38 AM    
     * @Status              : available      
     * */
    function get_previous_classroom($todays_date) {
        $sql = "SELECT *, cls.url_id AS classroom_url_id
                FROM 
                    tbl_classroom AS cls,
                    tbl_url AS u,
                    tbl_person AS p,
                    tbl_course AS c
                WHERE 
                 cls.url_id = u.url_id
                AND cls.person_id = p.person_id
                AND cls.course_id = c.course_id
                AND (cls.classroom_status IN (2,4,5)
                OR (cls.classroom_status = '3' AND cls.class_end_time<'$todays_date'))
                ORDER BY cls.class_start_time ASC";
        return $this->db->query($sql)->result();
    }

    /**
     * @Function            : get_previous_classroom_by_person_id 
     * @Used IN             : @function-'course_student_list'          classroom[@Controller]
     * @Description         : assign student information using classroomID and studentID
     * @Developed By        : Copied from 'classroom_model'
     * @Start DateTime      : 19-03-2016 01:52 PM    
     * @Status              : available      
     * */
    function assign_student_info_student_id($classroom_id, $student_id) {
        $sql = "SELECT *
                FROM 
                    tbl_classroom_student AS cs
                WHERE 
                    cs.classroom_id = '$classroom_id'
                AND cs.student_id = '$student_id'
                AND cs.classroom_student_join_status != '2'";
        return $this->db->query($sql)->row();
    }

    /**
     * @Function            : get_assigned_student_information_student_id 
     * @Used IN             : @function-'change_student_permission'          classroom[@Controller]
     * @Description         : assign student information  studentID
     * @Developed By        : Copied from - 'classroom_model'
     * @Start DateTime      : 19-03-2016 05:43 PM    
     * @Status              : available      
     * */
    function get_assigned_student_information_student_id($student_id) {
        $sql = "SELECT * 
                FROM 
                    tbl_classroom_student AS cs,
                    tbl_student AS s,
                    tbl_payment AS py
                WHERE 
                    cs.student_id = '$student_id'
                AND cs.student_id = s.student_id
                AND s.payment_id = py.payment_id
                AND cs.classroom_student_join_status != '2'";
        return $this->db->query($sql)->row();
    }

    /**
     * @Function            : remove_assigned_student 
     * @Used IN             : @function-'change_student_permission'          classroom[@Controller]
     * @Description         : Delete assigned student usind classroom ID and studentID
     * @Developed By        : Copied from - 'classroom_model'
     * @Start DateTime      : 19-03-2016 05:43 PM   
     * @Status              : available      
     * */
    function remove_assigned_student($classroom_id, $student_id) {
        $sql = "DELETE cs
                FROM 
                    tbl_classroom_student AS cs
                WHERE 
                    cs.classroom_id = '$classroom_id'
                AND cs.student_id = '$student_id'";
        $this->db->query($sql);
    }

    /**
     * @Function            : select_classroom_assigned_student_by_classroom_id 
     * @Used IN             : @function-'change_student_permission'          classroom[@Controller]
     * @Description         : Delete assigned student usind classroom ID and studentID
     * @Developed By        : Copied from - 'classroom_model'
     * @Start DateTime      : 19-03-2016 05:43 PM   
     * @Status              : available      
     * */
    function select_classroom_assigned_student_by_classroom_id($classroom_id) {
        $sql = "SELECT * 
                FROM 
                    tbl_classroom_student AS cs,
                    tbl_classroom AS c,
                    tbl_student AS s,
                    tbl_person AS p,
                    tbl_url AS u,
                    tbl_payment AS py
                WHERE 
                    cs.classroom_id = '$classroom_id'
                AND cs.classroom_student_join_status !='2'
                AND cs.classroom_id = c.classroom_id
                AND cs.student_id = s.student_id
                AND s.student_type = '1'
                AND s.person_id = p.person_id
                AND p.url_id = u.url_id
                AND u.url_type = '10'
                AND s.payment_id = py.payment_id";
        return $this->db->query($sql)->result();
    }

    /*
      -----------@@@RIPON@ End view ClassRoom Part----------------------------------
     */
}
