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

class Classroom_model extends CI_Model {

    public function __construct() {
        parent::__construct();
        $this->load->model('url_model');
        $this->load->model('super_admin_feature_model');
    }

    public function check_all_active_classroom() {
        $now = strtotime(date('H:i:s'));
        $posible_active_time_limit = date("H:i:s", $now - 100); // 5 minute past time from now in microtime like 3256481156.15646
        $today = date("Y-m-d");

        $sql = "SELECT * "
                . " FROM "
                . "     tbl_classroom as cr "
                . " WHERE "
                . "     cr.classroom_status = 1 "
                . "     AND ("
                . "             (cr.class_date = '$today' OR cr.class_date < '$today' AND cr.class_end_time < '$posible_active_time_limit')"
                . "             OR (cr.classroom_status = 1 AND cr.class_date < '$today') "
                . "             OR (cr.classroom_status = 1 AND cr.class_date = '$today' AND cr.class_end_time < '$posible_active_time_limit')"
                . "         )"
        ;

        $result = $this->db->query($sql)->result();
        foreach ($result as $active_class) {
            $class_end_time = $active_class->class_end_time;
            $class_date = $active_class->class_date;
            if ($class_end_time < $posible_active_time_limit && $class_date <= $today) {
                $this->db->set('classroom_status', 3);
                $this->db->where('classroom_id', $active_class->classroom_id);
                $this->db->update('tbl_classroom');
            }
        }
    }

    public function check_all_active_classroom_student() {
        $now = strtotime(date('H:i:s'));
        $posible_active_time_limit = date("H:i:s", $now - 100); // 5 minute past time from now in microtime like 3256481156.15646
        $today = date("Y-m-d");

        $sql = "SELECT * "
                . " FROM "
                . "     tbl_classroom as cr, "
                . "     tbl_classroom_student as crs "
                . " WHERE "
                . "     crs.classroom_student_join_status = 1 "
                . "     AND ("
                . "             (cr.class_date = '$today' OR cr.class_date < '$today' AND crs.class_leave_time < '$posible_active_time_limit')"
                . "             OR (crs.classroom_student_join_status = 1 AND cr.class_date < '$today') "
                . "             OR (crs.classroom_student_join_status = 1 AND cr.class_date = '$today' AND cr.class_end_time < '$posible_active_time_limit')"
                . "         )"
        ;

        $result = $this->db->query($sql)->result();
        foreach ($result as $active_class) {
            $class_end_time = $active_class->class_end_time;
            $class_date = $active_class->class_date;
            if ($class_end_time < $posible_active_time_limit && $class_date <= $today) {
                $this->db->set('classroom_student_join_status', 3);
                $this->db->where('classroom_student_id', $active_class->classroom_id);
                $this->db->update('tbl_classroom_student');
            }
        }
    }

    public function check_for_not_held_class() {
        $person_id = $this->session->userdata('user_id');
        $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.person_id = '$person_id'"
        ;
        $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');
        }
    }

    public function check_for_current_class() {

        $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_start_time  <= '$now' "
                . "     AND cr.class_end_time  >= '$now' "
                . "     AND cr.classroom_status = 0 "
        ;
        $result = $this->db->query($sql)->result();
        foreach ($result as $active_class) {
            $this->db->set('classroom_status', 1);
            $this->db->where('classroom_id', $active_class->classroom_id);
            $this->db->update('tbl_classroom');
        }
    }

    public function check_for_finished_class() {

        $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 = 1 "
        ;

        $result = $this->db->query($sql)->result();
        foreach ($result as $active_class) {

            $sql = "SELECT * "
                    . " FROM "
                    . "     tbl_classroom as cr, tbl_classroom_student as crs "
                    . "WHERE "
                    . "         cr.classroom_id = '$active_class->classroom_id'"
                    . "     AND cr.classroom_id = crs.classroom_id "
                    . "     AND (   crs.classroom_student_join_status = 1 "
                    . "         OR  crs.classroom_student_join_status = 3 "
                    . "         OR  crs.classroom_student_join_status = 4 "
                    . "     ) "
                    . "     AND cr.classroom_status = 1 "
            ;
            $classroom_joined_student = $this->db->query($sql)->result();
            foreach ($classroom_joined_student as $v_student) {
                $this->db->set('classroom_student_join_status', 2);
                $this->db->where('classroom_student_id', $v_student->classroom_student_id);
                $this->db->update('tbl_classroom_student');
            }

            $this->db->set('classroom_status', 2);
            $this->db->where('classroom_id', $active_class->classroom_id);
            $this->db->update('tbl_classroom');
        }
    }

    public function select_class_room_batch_student($person_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_course_batch_student as cbs,"
                . "     tbl_course_batch as cb,"
                . "     tbl_course_category as cc, "
                . "     tbl_course as c, "
                . "     tbl_person as p"
                . " WHERE "
                . "         cb.resource_person_id = '$person_id'"
                . "     AND cbs.batch_id = cb.course_batch_id"
                . "     AND cbs.student_id = p.person_id"
                . " GROUP BY cbs.batch_id";
        $result = $this->db->query($sql)->result();
        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       :   classroom -> classroom_selected_batch_info();
     *
     * */
    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;
    }

    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            :   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       :   classroom -> check_feature_for_schedule_class_validation()
     *                          
     * */
    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 "
                . "     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_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       :   classroom -> check_feature_for_schedule_class_validation()
     *                          
     * */
    public function select_tt_resource_person_courses($resource_person_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_course as c, "
                . "     tbl_url as u "
                . " WHERE "
                . "         ("
                . "             (c.course_status = 1 AND c.resource_person_id = '$resource_person_id') "
                . "         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            :   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       :   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;
    }

    public function select_course_resource_person($course_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_resource_person as rp, "
                . "     tbl_course as c,"
                . "     tbl_person as p "
                . " WHERE "
                . "         c.course_id = '$course_id' "
                . "     AND c.resource_person_id = rp.resource_person_id"
                . "     AND rp.person_id = p.person_id "
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    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;
    }

    public function select_classroom_info_by_url($url) {
        $sql = "SELECT cr.*, u.*, c.course_name, cr.course_id "
                . " FROM "
                . "     tbl_classroom as cr, "
                . "     tbl_url as u, "
                . "     tbl_course as c "
                . " WHERE "
                . "         u.url = '$url' "
                . "     AND u.url_id = cr.url_id"
                . "     AND cr.course_id = c.course_id "
                . "     AND (cr.classroom_status = 0 OR cr.classroom_status = 1 OR cr.classroom_status = 3 OR cr.classroom_status = 7)"
                . " GROUP BY cr.classroom_id "
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_classroom_info_for_all_status($url) {
        $sql = "SELECT cr.*, u.*, c.course_name, cr.course_id "
                . " FROM "
                . "     tbl_classroom as cr, "
                . "     tbl_url as u, "
                . "     tbl_course as c "
                . " WHERE "
                . "         u.url = '$url' "
                . "     AND u.url_id = cr.url_id"
                . "     AND cr.course_id = c.course_id "
                . " GROUP BY cr.classroom_id "
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_classroom_info_by_course_id($course_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_classroom as cr, "
                . "     tbl_course as c "
                . " WHERE "
                . "         cr.course_id = '$course_id' "
                . "     AND cr.course_id = c.course_id "
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_recent_classroom_info($course_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_classroom as cr, "
                . "     tbl_course as c "
                . " WHERE "
                . "         cr.course_id = '$course_id' "
                . "     AND cr.course_id = c.course_id "
                . "     AND (cr.classroom_status = 1 OR cr.classroom_status = 3)"
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

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

    public function select_classroom_info_by_person_id($person_id, $class_time_in_second = null) {

        $sql = "SELECT * "
                . " FROM "
                . "     tbl_classroom as cr, "
                . "     tbl_course as c "
                . " WHERE "
                . "         cr.person_id = '$person_id' "
                . "     AND cr.course_id = c.course_id "
                . "     AND cr.classroom_status = 3 "
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_created_classroom_by_person_id($person_id) {

        $sql = "SELECT * "
                . " FROM "
                . "     tbl_classroom as cr, "
                . "     tbl_course as c "
                . " WHERE "
                . "         cr.person_id = '$person_id' "
                . "     AND cr.course_id = c.course_id "
                . "     AND (   cr.classroom_status = 0 "
                . "             OR  cr.classroom_status = 1 "
                . "         )"
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_current_classroom($person_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_classroom as cr, "
                . "     tbl_course as c,"
                . "     tbl_person as p "
                . " WHERE "
                . "         cr.person_id = '$person_id' "
                . "     AND p.person_id = cr.person_id "
                . "     AND cr.course_id = c.course_id "
                . "     AND (cr.classroom_status = 1 OR cr.classroom_status = 3)" //Running=1, End=2, Unexpected End = 3;
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_person_active_classroom_by_person_id($person_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_classroom as cr, "
                . "     tbl_course as c,"
                . "     tbl_person as p, "
                . "     tbl_url as u "
                . " WHERE "
                . "         cr.person_id = '$person_id' "
                . "     AND p.person_id = cr.person_id "
                . "     AND cr.course_id = c.course_id "
                . "     AND (cr.classroom_status = 1 OR cr.classroom_status = 3) " //Running=1, End=2, Unexpected End = 3;
                . "     AND u.url_id = cr.url_id "
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_current_classroom_for_paid($pdata) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_classroom as cr, "
                . "     tbl_course as c,"
                . "     tbl_person as p "
                . " WHERE ";
        $sql .= "           p.person_id = cr.person_id "
                . "     AND cr.course_id = c.course_id "
                . "     AND (cr.classroom_status = 1 OR cr.classroom_status = 3)" //Running=1, End=2, Unexpected End = 3;
        ;

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

    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 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;
    }

    public function select_classroom_student($classroom_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_classroom as cr, "
                . "     tbl_classroom_student as crs, "
                . "     tbl_student as s, "
                . "     tbl_person as p "
                . " WHERE "
                . "         cr.classroom_id = '$classroom_id' "
                . "     AND crs.classroom_id = cr.classroom_id "
                . "     AND crs.student_id = s.student_id "
                . "     AND p.person_id = s.person_id "
                . "     AND (crs.classroom_student_join_status = 1 "
                . "         OR crs.classroom_student_join_status = 2 "
                . "         OR crs.classroom_student_join_status = 3 )"
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    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;
    }

    public function all_assigned_student($classroom_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_classroom_student as crs "
                . " WHERE "
                . "         crs.classroom_id = '$classroom_id' "
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_attended_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 = 1"
                . "     OR  crs.classroom_student_join_status = 2"
                . "     OR  crs.classroom_student_join_status = 3"
                . "     OR  crs.classroom_student_join_status = 4)"
                . "     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;
    }

    public function select_active_student($classroom_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_classroom_student as crs "
                . " WHERE "
                . "         crs.classroom_id = '$classroom_id' "
                . "     AND crs.classroom_student_join_status = 1"
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_inactive_student($classroom_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_classroom_student as crs "
                . " WHERE "
                . "         crs.classroom_id = '$classroom_id' "
                . "     AND crs.classroom_student_join_status = 2"
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_unexpectedly_leave_student($classroom_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_classroom_student as crs "
                . " WHERE "
                . "         crs.classroom_id = '$classroom_id' "
                . "     AND crs.classroom_student_join_status = 3"
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_assigned_student_by_student_id($student_id, $classroom_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_classroom_student as crs "
                . " WHERE "
                . "         crs.student_id = '$student_id' "
                . "     AND crs.classroom_id = '$classroom_id' "
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function organization_feature_info($feature_price_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_feature_price as fp"
                . " WHERE "
                . "         fp.feature_price_id = '$feature_price_id' "
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_virtual_classroom_feature_info_by_id($feature_id) {
        $sql = "SELECT * "
                . " FROM"
                . "      tbl_feature_price as fp "
                . " WHERE "
                . "     fp.feature_price_id = '$feature_id'";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_class_room_student($course_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_student as s, "
                . "     tbl_course as c, "
                . "     tbl_person as p  "
                . " WHERE "
                . "         c.course_id = '$course_id' "
                . "     AND s.course_id = c.course_id "
                . "     AND s.person_id = p.person_id "
                . " GROUP BY s.student_id "
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_person_info($person_id) {
        $this->db->select('*');
        $this->db->from('tbl_person');
        $this->db->where('person_id', "$person_id");
        $this->db->where('person_status', 1);
        $query_result = $this->db->get();
        $result = $query_result->result();
        return $result;
    }

    public function select_student_info_by_person_id($person_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_student as s, "
                . "     tbl_course as c, "
                . "     tbl_person as p  "
                . " WHERE "
                . "         p.person_id = '$person_id' "
                . "     AND s.person_id = p.person_id "
                . " GROUP BY s.student_id "
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_student_info_by_course_id($person_id, $course_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_student as s, "
                . "     tbl_course as c, "
                . "     tbl_person as p,"
                . "     tbl_url as u "
                . " WHERE "
                . "         p.person_id = '$person_id' AND s.course_id = $course_id "
                . "     AND s.person_id = p.person_id "
                . "     AND p.url_id = u.url_id "
                . " GROUP BY s.student_id "
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_student_info_by_student_id($student_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_student as s, "
                . "     tbl_person as p,"
                . "     tbl_url as u "
                . " WHERE "
                . "         s.student_id = '$student_id'"
                . "     AND s.person_id = p.person_id "
                . "     AND p.url_id = u.url_id "
                . " GROUP BY s.student_id "
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    /**
     * @Function            :   select_student_info_by_student_id_list
     * @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_by_student_id_list($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_student_assigned_batch($student_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_course_batch "
                . " WHERE "
                . "         (FIND_IN_SET('',student_id) > 0 "
                . "     OR  FIND_IN_SET('$student_id',student_id) > 0) "
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_classroom_student_information($classroom_id, $student_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_classroom_student as cs, "
                . "     tbl_course as c, "
                . "     tbl_person as p  "
                . " WHERE "
                . "         cs.student_id = '$student_id' "
                . "     AND cs.classroom_id = '$classroom_id' "
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_classroom_student_info($classroom_student_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_classroom_student as cs, "
                . "     tbl_course as c, "
                . "     tbl_person as p  "
                . " WHERE "
                . "         cs.classroom_student_id = '$classroom_student_id' "
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function set_classroom_student_visited($classroom_student_id) {
        $this->db->set('classroom_student_join_status', 5);
        $this->db->where('classroom_student_id', $classroom_student_id);
        $this->db->update('tbl_classroom_student');
    }

    public function activate_classroom_student_again($classroom_student_id) {
        $update_time = round(microtime(true) * 1000);
        $this->db->set('classroom_student_join_status', 7);
        $this->db->set('classroom_student_update_time', $update_time);
        $this->db->where('classroom_student_id', $classroom_student_id);
        $this->db->update('tbl_classroom_student');
    }

    public function select_class_room_attended_student($student_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_student as s, "
                . "     tbl_classroom as cr,"
                . "     tbl_classroom_student as crs "
                . " WHERE "
                . "         s.student_id = '$student_id' "
                . "     AND crs.student_id = s.student_id "
                . "     AND crs.classroom_student_join_status = 1"
                . " GROUP BY s.student_id"
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function save_started_classroom_information($person_id, $course_id, $organization_id = null) {
        $sql = "SELECT * FROM tbl_classroom as cr WHERE cr.course_id = '$course_id' AND cr.person_id = '$person_id' ORDER BY cr.class_session_number DESC";
        $result = $this->db->query($sql)->row();
        $data = array();
        $data['person_id'] = $person_id;
        $data['course_id'] = $course_id;
        if ($organization_id) {
            $data['organization_id'] = $organization_id;
        }

        if ($result->class_session_number != '') {
            $data['class_session_number'] = $result->class_session_number + 1;
        } else {
            $data['class_session_number'] = 1;
        }
        $data['class_start_time'] = date('Y-m-d') . ' ' . date('H:i:s');
        $data['class_end_time'] = date('H:i:s');
        $data['classroom_status'] = 1;  //1 for running
        $this->db->insert('tbl_classroom', $data);
        return $this->db->insert_id();
    }

    public function save_active_classroom_last_time($last_active_time, $classroom_id) {
        $this->db->set('class_end_time', $last_active_time);
        $this->db->where('classroom_id', $classroom_id);
        $this->db->update('tbl_classroom');
    }

    public function save_active_classroom_student_leave_time($class_leave_time, $classroom_student_id) {
        $this->db->set('class_leave_time', $class_leave_time);
        $this->db->where('classroom_student_id', $classroom_student_id);
        $this->db->update('tbl_classroom_student');
    }

    public function activate_classroom($classroom_id) {
        $update_time = round(microtime(true) * 1000);
        $this->db->set('classroom_teacher_update_time', $update_time);
        $this->db->set('classroom_teacher_join_status', 7);
        $this->db->where('classroom_id', $classroom_id);
        $this->db->update('tbl_classroom');
    }

    public function set_classroom_image_upload_table($classroom_id) {
        $sql = "SELECT * FROM tbl_classroom_image_upload as ciu where ciu.classroom_id = '$classroom_id'";
        $result = $this->db->query($sql)->row();
        if (!$result) {
            $ciu_data = array();
            $ciu_data['classroom_id'] = $classroom_id;
            $this->db->insert('tbl_classroom_image_upload', $ciu_data);
        }
    }

    public function classroom_teacher_status_update_to_visit($classroom_id) {
        $this->db->set('classroom_teacher_join_status', 5);
        $this->db->where('classroom_id', $classroom_id);
        $this->db->update('tbl_classroom');
    }

    public function inactivate_classroom($classroom_id) {
        $this->db->set('classroom_status', 0);
        $this->db->where('classroom_id', $classroom_id);
        $this->db->update('tbl_classroom');
    }

    public function save_classroom_student($crs_data) {
        $student_id = $crs_data['student_id'];
        $sql = "SELECT * FROM tbl_classroom_student as crs WHERE student_id = '$student_id' AND crs.classroom_student_join_status =2";
        $result = $this->db->query($sql)->row();
        if ($result) {
            $this->db->set('classroom_student_join_status', 1);
            $this->db->where('classroom_student_id', $result->classroom_student_id);
            $this->db->update('tbl_classroom_student');
            return $result->classroom_student_id;
        }
        $this->db->insert('tbl_classroom_student', $crs_data);
        return $this->db->insert_id();
    }

    public function end_session($classroom_id) {
        $this->db->set('classroom_status', 2);
        $this->db->set('class_end_time', date("H:i:s"));
        $this->db->where('classroom_id', $classroom_id);
        $this->db->update('tbl_classroom');
    }

    public function leave_classroom($classroom_student_id) {
        $this->db->set('classroom_student_join_status', 2)
                ->where('classroom_student_id', $classroom_student_id)
                ->update('tbl_classroom_student');
    }

    public function update_classroom_status($classroom_id) {
        $this->db->set('classroom_status', 2);
        $this->db->where('classroom_id', $classroom_id);
        $this->db->update('tbl_classroom');
    }

    /**
     * @Function            : select_organization_feture_detail
     * @Developed By        : Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> | 
     * @DevelopeDateTime    : 14-07-2015 05:00 PM   | 
     * @Updated Date Time   : dd-mm-yyyy hh:mm AM   |
     * @Updated By          : Name                  |
     * @parameter           : $person_id
     * @return              : single row 
     * @redirect            : no
     * */
    public function select_feture_detail($feature_price_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_feature_price as fp "
                . " WHERE "
                . "         fp.feature_price_id = '$feature_price_id'";

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

    public function select_active_feature_by_feature_id($feature_price_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_feature_price as fp "
                . " WHERE "
                . "         fp.feature_price_id = '$feature_price_id' "
                . "     AND (fp.feature_price_status = 1 "
                . "     OR fp.feature_price_status = 3 )";

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

    public function select_last_feature_payment_info_by_id($person_id) {
        $sql = "SELECT * "
                . " FROM tbl_payment as p"
                . " WHERE "
                . "         (p.feature_price_payment_status = 1 OR  p.feature_price_payment_status = 2)"
                . "     AND p.person_id = '$person_id' "
                . " ORDER BY p.payment_id DESC";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_student_payment_status($payment_id) {
        $sql = "SELECT * "
                . " FROM tbl_payment as p "
                . " WHERE "
                . "         p.payment_id = '$payment_id'";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function get_timezone_offset($remote_tz, $origin_tz = null) {
        date_default_timezone_set('America/New_York');
        if ($origin_tz === null) {
            if (!is_string($origin_tz = date_default_timezone_get())) {
                return false; // A UTC timestamp was returned -- bail out!
            }
        }
        $origin_dtz = new DateTimeZone($origin_tz);
        $remote_dtz = new DateTimeZone($remote_tz);
        $origin_dt = new DateTime("now", $origin_dtz);
        $remote_dt = new DateTime("now", $remote_dtz);
        $offset = $origin_dtz->getOffset($origin_dt) - $remote_dtz->getOffset($remote_dt);
        return $offset;
    }

    /**
     * @Function            : save_class_schedule_information
     * @Developed By        : Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> | 
     * @DevelopeDateTime    : 14-07-2015 05:00 PM   | 03-02-2016 (class_session_number of $course_id)
     * @Updated Date Time   :                       |
     * @Updated By          : Name                  |
     * @parameter           : NONE
     * @return              : $this->db->insert_id() (classroom_id)
     * @redirect            : no
     * */
    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('user_id');
        $classroom_data['course_id'] = $course_id = $this->input->post('selected_course_id', true);
        $course_batch_id = $this->input->post('course_batch_id', TRUE);
        if ($course_batch_id) {
            $classroom_data['course_batch_id'] = implode(',', $course_batch_id);
        }
        $classroom_data['organization_id'] = $this->session->userdata('organization_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';
        $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       :   classroom -> schedule_classroom()
     *
     * */
    public function assign_class_student($classroom_id) {
        $course_batch_id = $this->input->post('course_batch_id', TRUE);
        if ($course_batch_id) {
            foreach ($course_batch_id as $value) {
                $batch_info = $this->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    :   2016-02-24 04:35 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   Assign classroom student from selected batches
     *
     * @parameter           :   $classroom_info
     * @Return              :   no
     * @Redirect            :   no
     *
     * @External call       :   classroom -> edit_scheduled_class()
     *
     * */
    public function update_assigned_class_student($data) {
        $classroom_id = $data['classroom_info']->classroom_id;
        $course_batch_id = $data['classroom_info']->course_batch_id;

        $course_new_batch_id = $this->input->post('course_batch_id', TRUE);
        if ($course_new_batch_id) {
            $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            :   make_batch_student_list
     * @Developed By        :   Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> |
     * @DevelopeDateTime    :   2016-02-24 04:35 PM   |
     * @Updated Date Time   :
     * @Updated By          :
     *
     * @Works               :   make_batch_student_list
     *
     * @parameter           :   $classroom_id
     * @Return              :   no
     * @Redirect            :   no
     *
     * @External call       :   classroom_model -> update_assigned_class_student()
     *
     * */
    public function make_batch_student_list($batch_id_list) {
        $course_batches = $this->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;
    }

    /**
     * @Function            :   select_course_batch_info
     * @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       :  
     * @External call       :   
     * */
    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;
    }

    public function cancel_schedule_class($classroom_id) {
        $this->db->set('classroom_status', '5'); // classroom Status for Archived
        $this->db->where('classroom_id', $classroom_id);
        $this->db->update('tbl_classroom');
    }

    public function delete_schedule_class($classroom_id) {
        $this->db->set('classroom_status', '6'); // classroom Status for Archived
        $this->db->where('classroom_id', $classroom_id);
        $this->db->update('tbl_classroom');
    }

    public function reschedule_schedule_class($classroom_id) {
        $this->db->set('classroom_status', '0'); // classroom Status for Archived
        $this->db->where('classroom_id', $classroom_id);
        $this->db->update('tbl_classroom');
    }

    public function select_all_available_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_category_course
     * @Developed By        : Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> | 
     * @DevelopeDateTime    : 14-07-2015 05:00 PM   | 
     * @Updated Date Time   : dd-mm-yyyy hh:mm AM   |
     * @Updated By          : Name                  |
     * @parameter           : $category_id
     * @return              : $result array
     * @redirect            : no
     * */
    public function save_course_info($data) {
        $this->db->insert('tbl_course', $data);
        return $this->db->insert_id();
    }

    /**
     * @Function            : select_category_course
     * @Developed By        : Mahabub-A-Mawla <mahabub.cgbd@yahoo.com> <+8801925821707> | 
     * @DevelopeDateTime    : 14-07-2015 05:00 PM   | 
     * @Updated Date Time   : dd-mm-yyyy hh:mm AM   |
     * @Updated By          : Name                  |
     * @parameter           : $category_id
     * @return              : $result array
     * @redirect            : no
     * */
    public function select_category_course($category_id, $pending, $aproved) {
        $sql = "SELECT * "
                . "FROM tbl_course as c "
                . "WHERE "
                . "         c.course_category_id = '$category_id'"
                . "     AND (c.course_status = 1 "
                . "     OR c.course_status = 3 "
                . "     OR c.course_status = 4 )"
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_categories_publish_approved_and_recent_course($category_id, $recently_created_course_id) {
        $sql = "SELECT * "
                . "FROM tbl_course as c "
                . "WHERE "
                . "         c.course_category_id = '$category_id'"
                . "     AND (c.course_status = 1 "
                . "     OR c.course_status = 4 "
                . "     OR c.course_id IN ($recently_created_course_id))"
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_others_category_course($category_id) {
        $person_id = $this->session->userdata('user_id');
        $sql = "SELECT * "
                . "FROM tbl_course as c "
                . "WHERE "
                . "         c.course_category_id = '$category_id'"
                . "     AND ( c.course_status = 1 OR c.course_status = 3 OR c.course_status = 4 AND c.person_id = '$person_id')"
                . "     AND c.person_id = '$person_id'";

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

    //TalhaTraining Course
    public function select_course_by_person_and_category_id($category_id, $person_id, $resource_person, $recently_created_course_id) {
        $sql = "SELECT * "
                . " FROM tbl_course as c "
                . " WHERE "
                . "         c.course_category_id = '$category_id'"
                . "     AND (c.course_status = 1 "
                . "     OR c.course_status = 4 ";
        if ($recently_created_course_id) {
            $sql .= "     OR c.course_id IN ($recently_created_course_id)";
        }
        $sql .= ")";

        $sql .= "     AND ((c.person_id = '$person_id' OR c.resource_person_id = '')";
        if ($resource_person) {
            foreach ($resource_person as $v_person) {
                $sql .= " OR (  c.person_id = $v_person->person_id";
                $sql .= "       OR c.resource_person_id = '$v_person->resource_person_id')";
            }
        }
        $sql .= " )";

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

    public function select_course_by_person_and_category_id_as_teacher($category_id, $person_id) {
        $sql = "SELECT * "
                . "FROM tbl_course as c "
                . "WHERE "
                . "         c.course_category_id = '$category_id'"
                . "     AND (c.course_status = 1 "
                . "     OR c.course_status = 3 "
                . "     OR c.course_status = 4 )"
                . "     AND c.resource_person_id = '$person_id'"
        ;
        $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       :   classroom -> select_category_course()
     *
     * */
    public function select_talhatraining_category_course_by_category_id($data) {
        $category_id = $data['category_id'];
        $person_id = $data['person_info']->person_id;
        $resource_person_id = $data['person_info']->resource_person_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 AND c.resource_person_id = '$resource_person_id') "
                . "         OR (c.person_id = '$person_id' AND c.course_status = 4)) "
                . " GROUP BY c.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       :   classroom -> select_category_course()
     *                          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;
    }

    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;
    }

    public function select_course_category_info($category_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_course_category as cc, "
                . "     tbl_url as u"
                . " WHERE "
                . "         cc.course_category_id = '$category_id' "
                . "     AND cc.url_id = u.url_id "
        ;

        $result = $this->db->query($sql)->row();
        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       :   classroom -> select_category_course()
     *
     * */
    public function select_class_session_number($person_id, $course_id) {
        $sql = "SELECT * FROM tbl_classroom as c WHERE c.person_id = '$person_id' AND c.course_id = '$course_id' ORDER BY c.classroom_id DESC";
        $result = $this->db->query($sql)->row();
        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;
    }
    //MY CLASS MODEL//

    public function select_student_upcoming_Classes($person_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_student as s, "
                . "     tbl_course as c, "
                . "     tbl_resource_person as rp, "
                . "     tbl_classroom as cr, "
                . "     tbl_person as p "
                . " WHERE "
                // for Student
                . "         (s.person_id = '$person_id' "
                . "     AND c.course_id =  s.course_id "
                . "     AND c.resource_person_id  = rp.resource_person_id "
                . "     AND c.course_id = cr.course_id "
                . "     AND cr.person_id = p.person_id ) "
                . " GROUP BY c.course_id "
                . " ORDER BY s.course_id DESC ";


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

    public function select_learner_course_schedule($course_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_course as c, "
                . "     tbl_classroom as cr,"
                . "     tbl_person as p, "
                . "     tbl_url as u "
                . " WHERE "
                // for Student
                . "         c.course_id = '$course_id' "
                . "     AND c.course_id = cr.course_id "
                . "     AND p.person_id = c.person_id "
                . "     AND p.url_id = u.url_id ";


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

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

    public function select_learner_upcoming_classes($course_id, $start = NULL, $limit = NULL) {
        $person_id = $this->session->userdata('user_id');
        $sql = "SELECT * "
                . " FROM tbl_person where person_id = '$person_id'";
        $person = $this->db->query($sql)->row();
       
        $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 = '$course_id'"
                . "     AND u.url_id = cr.url_id "
                . "     AND (cr.class_start_time  > '$now' AND cr.classroom_status = 0)"
                . "     AND c.course_id = cr.course_id "
                . "     AND p.person_id = cr.person_id "
                . " GROUP BY cr.classroom_id "
        ;
        if ($limit != null) {
            $sql .= " LIMIT $start, $limit ";
        }

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

    public function select_learner_current_classes($course_id, $start = NULL, $limit = NULL) {
        $person_id = $this->session->userdata('user_id');
        $now = gmdate('Y-m-d H:i:s');
        $sql = "SELECT * "
                . " FROM tbl_person where person_id = '$person_id'";
        $person = $this->db->query($sql)->row();

        $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 = '$course_id'"
                . "     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;
    }

    public function select_learner_previous_classes($course_id, $start = NULL, $limit = NULL) {
        $person_id = $this->session->userdata('user_id');
        $sql = "SELECT * "
                . " FROM tbl_person where person_id = '$person_id'";
        $person = $this->db->query($sql)->row();

        $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_course as c, "
                . "     tbl_resource_person as rp, "
                . "     tbl_classroom as cr, "
                . "     tbl_person as p, "
                . "     tbl_url as u "
                . " WHERE "
                . "         cr.course_id = '$course_id'"
                . "     AND u.url_id = cr.url_id "
                . "     AND c.course_id =  cr.course_id "
                . "     AND cr.person_id  = rp.person_id "
                . "     AND cr.person_id = p.person_id "
                . "     AND ((cr.class_end_time  < '$now' "
                . "     AND     (       cr.classroom_status = 2 "
                . "                 OR  cr.classroom_status = 3 "
                . "                 OR  cr.classroom_status = 4 "
                . "                 OR  cr.classroom_status = 5 "
                . "             )"
                . "           )"
                . "         ) "
                . " GROUP BY cr.classroom_id ";

        if ($limit != null) {
            $sql .= " LIMIT $start, $limit ";
        }

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

    public function select_teacher_upcoming_classes($courses, $start = NULL, $limit = NULL) {
        $person_id = $this->session->userdata('user_id');
        $sql = "SELECT * "
                . " FROM tbl_person where person_id = '$person_id'";
        $person = $this->db->query($sql)->row();

        $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 = ''";

        foreach ($courses as $value) {
            $sql .= "       OR cr.course_id = $value->course_id";
        }
        $sql .= " )";
        $sql .= "       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 "
        ;
        if ($limit != null) {
            $sql .= " LIMIT $start, $limit ";
        }

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

    public function select_teacher_current_classes($courses, $start = NULL, $limit = NULL) {
        $person_id = $this->session->userdata('user_id');
        $sql = "SELECT * "
                . " FROM tbl_person where person_id = '$person_id'";
        $person = $this->db->query($sql)->row();

        $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 = ''";

        foreach ($courses as $value) {
            $sql .= "       OR cr.course_id = $value->course_id";
        }
        $sql .= " )";

        $sql .= "       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;
    }

    public function select_teacher_previous_Classes($courses, $start = NULL, $limit = NULL) {
        $person_id = $this->session->userdata('user_id');
        $sql = "SELECT * "
                . " FROM tbl_person where person_id = '$person_id'";
        $person = $this->db->query($sql)->row();

        $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_course as c, "
                . "     tbl_resource_person as rp, "
                . "     tbl_classroom as cr, "
                . "     tbl_person as p "
                . " WHERE (cr.course_id = ''";

        foreach ($courses as $value) {
            $sql .= "       OR cr.course_id = $value->course_id";
        }
        $sql .= " )";

        $sql .= "       AND c.course_id =  cr.course_id "
                . "     AND cr.person_id  = rp.person_id "
                . "     AND cr.person_id = p.person_id "
                . "     AND ((cr.class_end_time  < '$now' "
                . "     AND     (       cr.classroom_status = 2 "
                . "                 OR  cr.classroom_status = 3 "
                . "                 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;
    }

    public function select_organization_upcoming_classes($person_list, $start = NULL, $limit = NULL) {
        $person_id = $this->session->userdata('user_id');
        $sql = "SELECT * "
                . " FROM tbl_person where person_id = '$person_id'";
        $person = $this->db->query($sql)->row();

        $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.person_id = '$person_id' "
        ;
        foreach ($person_list as $value) {
            $sql .= " OR  cr.person_id = '$value->person_id' ";
        }
        $sql .= "         )"
                . "     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;
    }

    public function select_organization_current_classes($person_list, $start = NULL, $limit = NULL) {
        $person_id = $this->session->userdata('user_id');
        $sql = "SELECT * "
                . " FROM tbl_person where person_id = '$person_id'";
        $person = $this->db->query($sql)->row();

        $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.person_id = '' "
        ;
        foreach ($person_list as $value) {
            $sql .= " OR  cr.person_id = '$value->person_id' ";
        }
        $sql .= "         )"
                . "     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;
    }

    public function select_organization_previous_classes($person_id, $start = NULL, $limit = NULL) {
        $person_id = $this->session->userdata('user_id');
        $sql = "SELECT * "
                . " FROM tbl_person where person_id = '$person_id'";
        $person = $this->db->query($sql)->row();

        $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.person_id = '$person_id' "
                . "     AND u.url_id = cr.url_id "
                . "     AND ((cr.class_end_time  < '$now' "
                . "     AND     (       cr.classroom_status = 2 "
                . "                 OR  cr.classroom_status = 3 "
                . "                 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;
    }

    public function select_organization_previous_classes2($person_id, $start = NULL, $limit = NULL) {

        $today = date('Y-m-d');

        $sql = "SELECT * "
                . " FROM "
                . "     tbl_organization as o, "
                . "     tbl_url as u, "
                . "     tbl_course as c, "
                . "     tbl_resource_person as rp, "
                . "     tbl_classroom as cr, "
                . "     tbl_person as p "
                . " WHERE "
                . "         o.person_id = '$person_id' "
                . "     AND o.organization_id = cr.organization_id"
                . "     AND c.course_id =  cr.course_id "
                . "     AND cr.person_id = p.person_id "
                . "     AND u.url_id = cr.url_id "
                . "     AND (cr.classroom_status = 2 OR cr.classroom_status = 4) "
                . " GROUP BY cr.classroom_id ";

        if ($limit != null) {
            $sql .= " LIMIT $start, $limit ";
        }

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

    public function select_organization_resource_person_by_org_id($org_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_person as p, "
                . "     tbl_resource_person as rp "
                . " WHERE "
                . "         p.person_id= rp.person_id "
                . "     AND rp.organization_id = '$org_id'"
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_organization_active_resource_person_by_org_id($org_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_person as p, "
                . "     tbl_resource_person as rp "
                . " WHERE "
                . "         p.person_id= rp.person_id "
                . "     AND p.person_status = 1 "
                . "     AND rp.organization_id = '$org_id'"
        ;
        $result = $this->db->query($sql)->result();
      return $result;
    }

    public function resource_person_total_schedule($data) {
        $classroom_id = $data['classroom_id'];
        $schedule_start_date_time = $data['schedule_start_date_time'];
        $schedule_end_date_time = $data['schedule_end_date_time'];

        $total_scheduled_class = 0;
        if ($data['person_info']->resource_person_type == 3) {
            $organization_id = $data['person_info']->organization_id;
            $data['org_resource_person'] = $this->classroom_model->select_organization_resource_person_by_org_id($organization_id);
            foreach ($data['org_resource_person'] as $value) {
                $result = $this->cr_model->person_class_schedule($value->person_id);
                foreach ($result as $value) {
                    $scheduled_class = $this->cr_model->person_class_schedule_by_date_time($value->classroom_id, $schedule_start_date_time, $schedule_end_date_time);
                    if ($scheduled_class && $value->classroom_id != $classroom_id) {
                        $total_scheduled_class++;
                    }
                }
            }
            $course_id_list = '';
            $data['courses'] = $this->classroom_model->select_resource_person_courses($data['person_info']->resource_person_id);
            foreach ($data['courses'] as $key => $value) {
                $course_id_list .= $value->course_id . ',';
            }
        } else if ($data['person_info']->resource_person_type == 2) {
            $course_id_list = '';
            $data['courses'] = $this->classroom_model->select_resource_person_courses($data['person_info']->resource_person_id);
            foreach ($data['courses'] as $key => $value) {
                $course_id_list .= $value->course_id . ',';
            }
            $scheduled_class = $this->classroom_model->person_class_schedule_by_course_list_and_date_time($classroom_id, $course_id_list, $schedule_start_date_time, $schedule_end_date_time);
            $total_scheduled_class = count($scheduled_class);
        } else if ($data['person_info']->resource_person_type == 1) {
            $course_id_list = '';
            $data['courses'] = $this->classroom_model->select_tt_resource_person_courses($data['person_info']->resource_person_id);
            foreach ($data['courses'] as $key => $value) {
                $course_id_list .= $value->course_id . ',';
            }
            $scheduled_class = $this->classroom_model->person_class_schedule_by_course_list_and_date_time($classroom_id, $course_id_list, $schedule_start_date_time, $schedule_end_date_time);
            $total_scheduled_class = count($scheduled_class);
        }
        $return_data = array();
        $return_data['total_scheduled_class'] = $total_scheduled_class;
        $return_data['course_id_list'] = $course_id_list;
        return $return_data;
    }

    public function person_feature_info($data) {
        $data['status'] = NULL;
        $data['feature_info'] = $this->super_admin_feature_model->check_course_feature_information($data['person_info']->person_id);
        if ($data['feature_info']) {
            $data['virtual_classroom_number'] = $data['feature_info']->virtual_classroom_number;
            $data['virtual_classroom_per_day'] = $data['feature_info']->virtual_classroom_per_day;
            $data['virtual_classroom_attendees_number'] = $data['feature_info']->virtual_classroom_attendees_number;
            $data['virtual_classroom_type'] = $data['feature_info']->virtual_classroom_type;
        } else {
            $data['status'] = 0;
        }
        return $data;
    }

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

    /**
     * @Function            : save_classroom_information 
     * @Used IN             : @function-'schedule_class.php' 
     * @Description         : save classroom information in datavase
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 12-03-2016 08:44 AM    
     * @Status              : available      
     * */
    function save_classroom_information() {
        $cData['person_id'] = $this->session->userdata('user_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_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('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            : update_class_schedule_information 
     * @Used IN             : @function-'edit-schedule_class.php' 
     * @Description         : update classroom information in database
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 14-03-2016 02:37 AM    
     * @Status              : available      
     * */
    public function update_class_schedule_information($classroom_info) {
        $cData['person_id'] = $this->session->userdata('user_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_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('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            : select_classroom_info_by_classroom_id 
     * @Used IN             : @function-'schedule_class'                   Classroom[@Controller]
     * @                    : @function-'schedule_review_and_publish'      Classroom[@Controller]
     * @                    : @function-'update_classroom_url_form'        Classroom[@Controller]
     * @                    : @function-'save_update_classroom_url'        Classroom[@Controller]
     * @                    : @function-'edit_scheduled_class'             Classroom[@Controller]
     * @                    : @function-'change_student_permission'        Classroom[@Controller]
     * @Description         : get classroom informatiom using classroom ID
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 13-03-2016 02:15 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-'schedule_review_and_publish'    Classroom[@Controller]
     * @                    : @function-'edit_scheduled_class'           Classroom[@Controller]
     * @Description         : get classroom informatiom using classroom URL
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 13-03-2016 02:25 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            : save_classroom_information 
     * @Used IN             : @function-'save_classroom_information'    this[@Model]
     * @Description         : get classroom session nmber
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 13-03-2016 12:44 AM    
     * @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            : check_classroom_schedule 
     * @Used IN             : @function-'check_date_time_feature_status'    classroom[@Controller]
     * @                    : @function-'check_class_duration'              classroom[@Controller]
     * @Description         : check class room schedult
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 13-03-2016 02:29 PM    
     * @Status              : available      
     * */
    function check_classroom_schedule($class_start_time, $class_end_time, $scheduled_person_id, $classroom_id) {
        $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.person_id IN ($scheduled_person_id)
                    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_date 
     * @Used IN             : @function-'check_classroom_start_date_with_feature'    classroom[@Controller]
     * @                    : @function-'check_class_start_date'                     classroom[@Controller]
     * @Description         : check class date with feature
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 13-03-2016 03:01 PM    
     * @Status              : available      
     * */
    function check_classroom_schedule_date($start_date, $person_id, $classroom_id) {
        $sql = "SELECT * 
                FROM 
                    tbl_classroom AS cls
                WHERE 
                    DATE(cls.class_start_time) = '$start_date'
                AND cls.person_id IN ($person_id)
                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            : get_upcomming_classroom_by_person_id 
     * @Used IN             : @function-'organization_upcoming_class'    classroom[@Controller]
     * @                    : @function-'teacher_upcoming_class'         classroom[@Controller]
     * @Description         : get upcoming class using person ID
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 13-03-2016 03:01 PM    
     * @Status              : available      
     * */
    function get_upcomming_classroom_by_person_id($person_id_array, $todays_date, $resource_person_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";
        if ($resource_person_id == '') {
            $sql.=" cls.person_id IN ($person_id_array)";
        } else {
            $sql.=" (cls.person_id IN ($person_id_array) OR c.resource_person_id = '$resource_person_id')";
        }

        $sql.=" AND 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_learner_upcomming_classroom_by_person_id 
     * @Used IN             : @function-'learner_upcoming_class'         classroom[@Controller]
     * @Description         : get learner upcoming class using person ID
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 18-03-2016 05:46 PM    
     * @Status              : available      
     * */
    function get_learner_upcomming_classroom_by_person_id($person_id, $todays_date) {
        $sql = "SELECT *,cls.url_id AS classroom_url_id 
                FROM 
                    tbl_student AS s,
                    tbl_classroom_student AS cs,
                    tbl_classroom AS cls,
                    tbl_url AS u
                WHERE 
                    s.person_id = '$person_id'
                AND s.student_type = '1'
                AND s.student_id = cs.student_id
                AND cs.classroom_student_join_status !='2'
                AND cs.classroom_id = cls.classroom_id
                AND cls.url_id = u.url_id
                AND u.url_type = '11'
                AND cls.class_start_time > '$todays_date'
                AND cls.classroom_status IN (0,7)
                ORDER BY cls.class_start_time ASC";
        return $this->db->query($sql)->result();
    }

    /**
     * @Function            : get_current_classroom_by_person_id 
     * @Used IN             : @function-'teacher_current_class'          classroom[@Controller]
     * @                    : @function-'organization_current_class'     classroom[@Controller]
     * @Description         : get current class using person ID
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 14-03-2016 04:44 PM    
     * @Status              : available      
     * */
    function get_current_classroom_by_person_id($person_id_array, $todays_date, $resource_person_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 ";
        if ($resource_person_id == '') {
            $sql.=" cls.person_id IN ($person_id_array)";
        } else {
            $sql.=" (cls.person_id IN ($person_id_array) OR c.resource_person_id = '$resource_person_id')";
        }
        $sql.=" AND (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_learner_current_classroom_by_person_id 
     * @Used IN             : @function-'learner_upcoming_class'         classroom[@Controller]
     * @Description         : get learner current class using person ID
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 18-03-2016 08:01 PM    
     * @Status              : available      
     * */
    function get_learner_current_classroom_by_person_id($person_id, $todays_date) {
        $sql = "SELECT *,cls.url_id AS classroom_url_id 
                FROM 
                    tbl_student AS s,
                    tbl_classroom_student AS cs,
                    tbl_classroom AS cls,
                    tbl_url AS u
                WHERE 
                    s.person_id = '$person_id'
                AND s.student_type = '1'
                AND s.student_id = cs.student_id
                AND cs.classroom_student_join_status !='2'
                AND cs.classroom_id = cls.classroom_id
                AND cls.url_id = u.url_id
                AND u.url_type = '11'
                AND (cls.class_start_time <= '$todays_date' AND cls.class_end_time >= '$todays_date')
                AND cls.classroom_status IN (0,1,3)
                ORDER BY cls.class_start_time ASC";
        return $this->db->query($sql)->result();
    }

    /**
     * @Function            : get_previous_classroom_by_person_id 
     * @Used IN             : @function-'teacher_previoius_class'          classroom[@Controller]
     * @                    : @function-'organization_previoius_class'     classroom[@Controller]
     * @Description         : get current class using person ID
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 14-03-2016 04:44 PM    
     * @Status              : available      
     * */
    function get_previous_classroom_by_person_id($person_id_array, $todays_date, $resource_person_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";
        if ($resource_person_id == '') {
            $sql.=" cls.person_id IN ($person_id_array)";
        } else {
            $sql.=" (cls.person_id IN ($person_id_array) OR c.resource_person_id = '$resource_person_id')";
        }
        $sql.=" 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 (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_learner_previous_classroom_by_person_id 
     * @Used IN             : @function-'learner_current_class'         classroom[@Controller]
     * @Description         : get learner current class using person ID
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 18-03-2016 08:18 PM    
     * @Status              : available      
     * */
    function get_learner_previous_classroom_by_person_id($person_id, $todays_date) {
        $sql = "SELECT *,cls.url_id AS classroom_url_id 
                FROM 
                    tbl_student AS s,
                    tbl_classroom_student AS cs,
                    tbl_classroom AS cls,
                    tbl_url AS u
                WHERE 
                    s.person_id = '$person_id'
                AND s.student_type = '1'
                AND s.student_id = cs.student_id
                AND cs.classroom_student_join_status !='2'
                AND cs.classroom_id = cls.classroom_id
                AND cls.url_id = u.url_id
                AND u.url_type = '11'
                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        : Ripon Jaherul
     * @Start DateTime      : 16-03-2016 07:27 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        : Ripon Jaherul
     * @Start DateTime      : 16-03-2016 08:01 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            : get_student_information_student_id 
     * @Used IN             : @function-'change_student_permission'          classroom[@Controller]
     * @Description         : student information  studentID
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 17-03-2016 01:04 PM    
     * @Status              : available      
     * */
    function get_student_information_student_id($student_id) {
        $sql = "SELECT * 
                FROM 
                    tbl_student AS s
                WHERE 
                    s.student_id = '$student_id'";
        return $this->db->query($sql)->row();
    }

    /**
     * @Function            : get_all_student_id_by_person_id 
     * @Used IN             : @function-'change_student_permission'          classroom[@Controller]
     * @Description         : all student ID by person ID
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 17-03-2016 01:04 PM    
     * @Status              : available      
     * */
    function get_all_student_id_by_person_id($person_id) {
        $sql = "SELECT group_concat(s.student_id) as student_id_string 
                FROM 
                    tbl_student AS s
                WHERE s.person_id = '$person_id'";
        return $this->db->query($sql)->row();
    }

    /**
     * @Function            : get_all_classroom_id_by_multiple_student_id 
     * @Used IN             : @function-'change_student_permission'          classroom[@Controller]
     * @Description         : all classroom id by multiple student id
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 17-03-2016 01:04 PM    
     * @Status              : available      
     * */
    function get_all_classroom_id_by_multiple_student_id($student_id_string) {
        $sql = "SELECT group_concat(cs.classroom_id) as classroom_id_string
                FROM 
                    tbl_classroom_student AS cs
                WHERE 
                    cs.student_id IN ($student_id_string)
                AND cs.classroom_student_join_status != '2'";
        return $this->db->query($sql)->row();
    }

    /**
     * @Function            : get_remaining_classroom_by_classroom_id_with_date 
     * @Used IN             : @function-'change_student_permission'          classroom[@Controller]
     * @Description         : get all classroom by classroom id and classroom_start_date,classroom_end_date 
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 17-03-2016 01:30 PM    
     * @Status              : available      
     * */
    function get_remaining_classroom_by_classroom_id_with_date($classroom_id_string, $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.classroom_id IN ($classroom_id_string)
                    AND cls.classroom_status IN (0,1,3)";
        return $this->db->query($sql)->result();
    }

    /**
     * @Function            : get_all_assign_student_by_classroom_id 
     * @Used IN             : @function-'change_student_permission'          classroom[@Controller]
     * @Description         : get all assign student in classroom id 
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 17-03-2016 03:57 PM    
     * @Status              : available      
     * */
    function get_all_assigned_student_by_classroom_id($classroom_id) {
        $sql = "SELECT * 
                FROM 
                    tbl_classroom_student AS cs
                WHERE 
                    cs.classroom_id = '$classroom_id'
                AND cs.classroom_student_join_status != '2'";
        return $this->db->query($sql)->result();
    }

    /**
     * @Function            : remove_assigned_student 
     * @Used IN             : @function-'change_student_permission'          classroom[@Controller]
     * @Description         : Delete assigned student usind classroom ID and studentID
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 17-03-2016 04:51 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);
    }

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