<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Group_Model extends CI_Model {

//    public function save_employer_information($data) {
//        $this->db->insert('tbl_employee_information', $data);
//    }

    public function save_group_information($data) {
        $this->db->insert('tbl_group', $data);
        $mdata = array();
        $mdata['person_id'] = $this->session->userdata('user_id');
        $mdata['group_id'] = $this->db->insert_id();
        $offset = $this->session->userdata('time_offset');
        $mdata['group_join_date_time'] = date('Y-m-d H:i:s', time() + $offset);
        $mdata['group_member_status'] = 1;
        $this->db->insert('tbl_group_member', $mdata);
    }

    public function select_approved_group_by_person_id($person_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_group as g, "
                . "     tbl_url as u, "
                . "     tbl_person as p "
                . " WHERE "
                . "         p.person_id = $person_id "
                . "     AND p.person_id = g.group_created_by "
                . "     AND g.url_id=u.url_id "
                . "     AND g.group_status = 1 ";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_popular_group() {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_group AS g "
                . "     INNER JOIN "
                . "         (SELECT group_id, COUNT(*) AS total_member "
                . "         FROM tbl_group_member as gm "
                . "         GROUP BY gm.group_id) as total_group_members "
                . "     ON g.group_id = total_group_members.group_id"
                . " WHERE "
                . "     g.group_permission = 1 OR g.group_permission = 2"
                . " ORDER BY total_group_members.total_member DESC "
        ;
        $result = $this->db->query($sql)->result();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

    public function group_post_save($data) {
        $this->db->insert('tbl_post', $data);
    }

    public function member_comment_save($data) {
        $this->db->insert('tbl_comment', $data);
    }

    public function comment_replay_save($data) {
        $this->db->insert('tbl_comment_replay', $data);
    }

    public function select_group_post2($group_id, $person_id) {


        $this->db->select('*');
        $this->db->from('tbl_post as p');
        $this->db->where('group_id', $group_id);
        $this->db->join('tbl_person as pi', 'pi.person_id = p.posted_by');
        $query_result = $this->db->get();
        $result = $query_result->result();
        return $result;
    }

    public function select_group_post($group_id, $person_id) {
        $sql = "SELECT * FROM tbl_post as po, tbl_person as p WHERE po.posted_by = p.person_id AND po.group_id = $group_id";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_post_comment($post_id) {
        $sql = "SELECT c.*,pe.* FROM tbl_comment as c, tbl_post as p, tbl_person as pe WHERE c.post_id= $post_id AND c.post_id = p.post_id AND c.post_comment_by = pe.person_id ";
        $query_result = $this->db->query($sql);
        $result = $query_result->result();
        return $result;
    }

    // ............................ post comment Replay here................................
    public function select_post_comment_replay($post_comment_id) {

        $sql = "SELECT cr.*,pe.* FROM tbl_comment_replay as cr, tbl_post as p,tbl_comment as c, tbl_person as pe WHERE cr.post_comment_id= $post_comment_id AND cr.post_comment_id=c.post_comment_id AND cr.post_id = p.post_id AND cr.comment_replay_by = pe.person_id ";
        $query_result = $this->db->query($sql);
        $result = $query_result->result();
//         echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

    public function update_group_information($data, $group_id) {
        $this->db->where('group_id', $group_id);
        $this->db->update('tbl_group', $data);
    }

    public function update_user_profile($data, $person_id) {
        $this->db->where('person_id', $person_id);
        $this->db->update('tbl_person_information', $data);
    }

    public function save_group_member_information($data) {

        $this->db->insert('tbl_group_member', $data);
    }

    public function leave_group_member($group_id, $person_id) {
        $sql = "DELETE FROM tbl_group_member  WHERE group_id = $group_id AND person_id = $person_id ";
        $result = $this->db->query($sql);
    }

    public function selected_group_active($group_id) {
        $this->db->set('group_status', 1);
        $this->db->where('group_id', $group_id);
        $this->db->update('tbl_group');
    }

    public function selected_group_inactive($group_id) {
        $this->db->set('group_status', 0);
        $this->db->where('group_id', $group_id);
        $this->db->update('tbl_group');
    }

    public function selected_group_archive($group_id) {
        $this->db->set('group_status', 2);
        $this->db->where('group_id', $group_id);
        $this->db->update('tbl_group');
    }

    public function count_select_group_member_list_info($group_id) {
        $sql = "SELECT *"
                . " FROM "
                . "     tbl_group_member as gm, "
                . "     tbl_person as p, "
                . "     tbl_url as u "
                . " WHERE "
                . "         gm.group_id = $group_id "
                . "     AND gm.person_id = p.person_id "
                . "     AND p.url_id = u.url_id "
                . "     AND (gm.group_member_status = 1) ";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function view_select_group_member_list_info($group_id, $number) {

        $sql = "SELECT *"
                . " FROM "
                . "     tbl_group_member as gm, "
                . "     tbl_person as p, "
                . "     tbl_url as u "
                . " WHERE "
                . "         gm.group_id = $group_id "
                . "     AND gm.person_id = p.person_id "
                . "     AND p.url_id = u.url_id "
                . "     AND (gm.group_member_status = 1) "
        ;
        if ($number) {
            $sql.= " LIMIT $number , 10";
        } else if ($number == NULL) {
            $sql.= " LIMIT  10";
        }
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_group_member_list_info($group_id) {

        $sql = "SELECT *"
                . " FROM "
                . "     tbl_group_member as gm, "
                . "     tbl_person as p, "
                . "     tbl_url as u "
                . " WHERE "
                . "         gm.group_id = $group_id "
                . "     AND gm.person_id = p.person_id "
                . "     AND p.url_id = u.url_id "
                . "     AND (gm.group_member_status = 1) "
        ;

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

    function select_group_member_info_for_public_view($group_id, $number) {
        $sql = "SELECT *"
                . " FROM "
                . "     tbl_group_member as gm, "
                . "     tbl_person as p, "
                . "     tbl_url as u "
                . " WHERE "
                . "     gm.group_id = $group_id "
                . "     AND gm.person_id = p.person_id "
                . "     AND p.url_id = u.url_id "
                . "     AND (gm.group_member_status = 1)"
                . "     LIMIT  $number ";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_group_more_members($group_id, $start, $limit) {

        $sql = "SELECT *"
                . " FROM "
                . "     tbl_group_member as gm, "
                . "     tbl_person as p, "
                . "     tbl_url as u "
                . " WHERE "
                . "         gm.group_id = $group_id "
                . "     AND gm.person_id= p.person_id "
                . "     AND p.url_id = u.url_id "
                . "     AND (gm.group_member_status = 1 OR gm.group_member_status = 2)"
                . " LIMIT $start, $limit";

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

//        echo '<pre>';
//        print_r($sql);
//        exit();
        return $result;
    }

    public function count_select_group_requested_member_list_info($group_id) {
        $sql = "SELECT *"
                . " FROM "
                . "     tbl_group_member as gm, "
                . "     tbl_person as p, "
                . "     tbl_url as u "
                . " WHERE "
                . "         gm.group_id = $group_id "
                . "     AND gm.person_id= p.person_id "
                . "     AND p.url_id = u.url_id "
                . "     AND (gm.group_member_status = 3"
                . "     OR gm.group_member_status = 4"
                . "     )";
        $result = $this->db->query($sql)->result();

        return $result;
    }

    public function select_group_requested_member_list_info($group_id) {
        $sql = "SELECT *"
                . " FROM "
                . "     tbl_group_member as gm, "
                . "     tbl_person as p, "
                . "     tbl_url as u "
                . " WHERE "
                . "         gm.group_id = $group_id "
                . "     AND gm.person_id= p.person_id "
                . "     AND p.url_id = u.url_id "
                . "     AND (gm.group_member_status = 3"
                . "     OR gm.group_member_status = 4"
                . "     )";
        $result = $this->db->query($sql)->result();

        return $result;
    }

    public function view_select_group_requested_member_list_info($group_id, $number) {
        $sql = "SELECT *"
                . " FROM "
                . "     tbl_group_member as gm, "
                . "     tbl_person as p, "
                . "     tbl_url as u "
                . " WHERE "
                . "         gm.group_id = $group_id "
                . "     AND gm.person_id= p.person_id "
                . "     AND p.url_id = u.url_id "
                . "     AND (gm.group_member_status = 3"
                . "     OR gm.group_member_status = 4"
                . "     )";
        if ($number) {
            $sql.=" LIMIT $number,10 ";
        } else if ($number == NULL) {
            $sql.=" LIMIT 10 ";
        }
        $result = $this->db->query($sql)->result();

        return $result;
    }

    public function view_select_group_requested_member_list_info_number_wise($group_id, $value, $number) {
        $sql = "SELECT *"
                . " FROM "
                . "     tbl_group_member as gm, "
                . "     tbl_person as p, "
                . "     tbl_url as u "
                . " WHERE "
                . "         gm.group_id = $group_id "
                . "     AND gm.person_id= p.person_id "
                . "     AND p.url_id = u.url_id "
                . "     AND (gm.group_member_status = 3"
                . "     OR gm.group_member_status = 4"
                . "     )";
        if ($value) {
            if ($number) {
                $sql.=" LIMIT $number,$value ";
            } else if ($number == NULL) {
                $sql.=" LIMIT $value ";
            }
        }
        $result = $this->db->query($sql)->result();

        return $result;
    }

    public function select_group_result_info($group_id) {

        $sql = "SELECT gm.*,qr.* ,p.person_first_name,p.person_last_name, q.quiz_name FROM tbl_group_member as gm, tbl_quiz_result as qr, tbl_person as p, tbl_quiz as q "
                . "WHERE gm.person_id= qr.person_id AND gm.group_id=$group_id AND gm.person_id=p.person_id AND qr.quiz_id=q.quiz_id ";
        $query_result = $this->db->query($sql);
        $result = $query_result->result();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }
    
    function all_group_type_list($group_typr_id){
        $this->db->select('*')
                ->from ('tbl_group_type')
                ->where('group_type_id', $group_typr_id);
        return $this->db->get()->row();
    }
    public function group_type_list() {
        $sql = "SELECT *"
                . " FROM "
                . "    tbl_group_type "
                . " WHERE "
                . "       group_type_publication_status = 1 "
                . "     AND ( group_type_status = 1 OR group_type_status=4 )";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function active_select_member($group_member_id) {
        $this->db->set('group_member_status', 1);
        $this->db->where('group_member_id', $group_member_id);
        $this->db->update('tbl_group_member');
    }

    public function archive_select_member($group_member_id) {
        $this->db->set('group_member_status', 2);
        $this->db->where('group_member_id', $group_member_id);
        $this->db->update('tbl_group_member');
    }

    public function pending_select_member($group_member_id) {
        $this->db->set('group_member_status', 3);
        $this->db->where('group_member_id', $group_member_id);
        $this->db->update('tbl_group_member');
    }

    public function block_select_member($group_member_id) {
        $this->db->set('group_member_status', 4);
        $this->db->where('group_member_id', $group_member_id);
        $this->db->update('tbl_group_member');
    }

    public function select_request_group_member_list_info($group_id) {
        $this->db->select('*');
        $this->db->from('tbl_group_member as gm');
        $this->db->where('group_id', $group_id);
        $this->db->where('group_member_status', 3);
        $this->db->join('tbl_person as pi', 'pi.person_id = gm.person_id');
        $query_result = $this->db->get();
        $result = $query_result->result();
        return $result;
    }

    public function confirm_member_request($group_member_id) {

        $user_id = $this->session->userdata('user_id');
        $date_time = date("Y/m/d H:i:s");

        $this->db->set('group_member_status', 1);
        $this->db->set('member_added_by', $user_id);
        $this->db->set('member_approve_by', $user_id);
        $this->db->set('group_join_date_time', $date_time);
        $this->db->where('group_member_id', $group_member_id);
        $this->db->update('tbl_group_member');


        $this->db->select('*');
        $this->db->from('tbl_group_member');
        $this->db->where('group_member_id', $group_member_id);
        $query_result = $this->db->get();
        $result = $query_result->row();

        $person_id = $result->person_id;


        $this->db->select('*');
        $this->db->from('tbl_person_link');
        $this->db->where('requested_person_id', $user_id);
        $this->db->where('person_id', $person_id);

        $this->db->or_where('requested_person_id', $person_id);
        $this->db->where('person_id', $user_id);
        $query_result = $this->db->get();
        $result_link = $query_result->row();

        if (!$result_link) {
            $link_data = array();
            $link_data['person_id'] = $user_id;
            $link_data['requested_person_id'] = $result->person_id;
            $link_data['add_link_request_status'] = 1;
            $link_data['add_link_requested_date_time'] = $date_time;

            $this->db->insert('tbl_person_link', $link_data);
        }
    }

    public function delete_member_request($group_member_id) {
        $this->db->where('group_member_id', $group_member_id);
        $this->db->delete('tbl_group_member');
    }

    public function group_total_member($group_id) {
        $sql = "SELECT count(*) as total FROM tbl_group_member as gm WHERE gm.group_id =$group_id AND gm.group_member_status=1";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function group_total_member_pending($group_id) {
        $sql = "SELECT count(*) as total_pending FROM tbl_group_member as gm WHERE gm.group_id =$group_id AND gm.group_member_status=3";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_group_quiz_list($group_id) {

//        $sql = "SELECT * "
//                . " FROM "
//                . "     tbl_quiz as q, "
//                . "     tbl_quiz_permission as gqp, "
//                . "     tbl_url as u "
//                . " WHERE "
//                . "         gqp.quiz_permission_type_id = '$group_id' "
//                . "     AND gqp.quiz_id = q.quiz_id "
//                . "     AND u.url_id = q.url_id"
//                . " AND gqp.quiz_permission_type=1 ";
//        $result = $this->db->query($sql)->result();
////        echo '<pre>';
////        print_r($result);
////        exit();
//        return $result;
    }

    public function select_group_more_quiz($group_id, $start, $limit) {

        $sql = "SELECT * "
                . " FROM "
                . "     tbl_quiz as q, "
                . "     tbl_group_quiz_permission as gqp "
                . " WHERE "
                . "         gqp.group_id = '$group_id' "
                . "     AND gqp.quiz_id = q.quiz_id "
                . " LIMIT $start, $limit ";

        $result = $this->db->query($sql)->result();
//        echo '<pre>';
//        print_r($sql);
//        exit();
        return $result;
    }

//    public function select_group_popular_quiz($group_id) {
//
//        $sql = "SELECT * "
//                . " FROM "
//                . "     tbl_quiz as q, "
//                . "     tbl_group_quiz_permission as gqp "
//                . " WHERE "
//                . "         gqp.group_id = '$group_id' "
//                . "     AND gqp.quiz_id = q.quiz_id "
//                . "     AND (q.quiz_pulication_status = 1 OR q.quiz_pulication_status = 2) "
//                . "     AND q.quiz_status = 1 "
//                . " ORDER BY q.quiz_taken_number DESC "
//        ;
//        $result = $this->db->query($sql)->result();
//        return $result;
//    }

    public function select_group_more_popular_quiz($group_id, $start, $limit) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_quiz as q "
                . " WHERE "
                . "         (q.quiz_pulication_status = 1 OR q.quiz_pulication_status = 2)"
                . "     AND q.quiz_status = 1 "
                . " ORDER BY q.quiz_taken_number DESC"
                . " LIMIT $start, $limit"
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

//    public function select_group_member_all_quiz($group_member_id) {
//
//        $sql = "SELECT * FROM tbl_quiz as q, tbl_member_quiz as mq WHERE mq.group_member_id = '$group_member_id' AND q.quiz_id = mq.quiz_id ";
//        $result = $this->db->query($sql)->result();
////        echo '<pre>';
////        print_r($result);
////        exit();
//        return $result;
//    }

    public function select_user_all_quiz($person_id) {

        $sql = "SELECT * "
                . " FROM "
                . "     tbl_quiz as q, "
                . "     tbl_group_member as gm, "
                . "     tbl_group as g "
                . " WHERE "
                . "         gm.person_id = '$person_id' "
                . "     AND g.group_id = gm.group_id "
                . "     AND q.quiz_id = gm.quiz_id "
        ;

        $result = $this->db->query($sql)->result();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

    public function select_group_all_note_post($group_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_person as pi, "
                . "     tbl_post as p,"
                . "     tbl_post_group as pg,"
                . "     tbl_url AS u"
                . " WHERE "
                . "         pg.group_id=$group_id "
                . "     AND p.post_id=pg.post_id "
                . "     AND p.posted_by = pi.person_id "
                . "     AND pi.url_id = u.url_id "
                . "     AND p.post_type = 1 "
                . "     AND (p.post_status = 0 OR p.post_status = 1)"
                . " GROUP BY p.post_id ";
        $result = $this->db->query($sql)->result();
        return $result;
    }
    
    public function select_limited_group_all_note_post($group_id, $limit_offset) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_person as pi, "
                . "     tbl_post as p,"
                . "     tbl_post_group as pg,"
                . "     tbl_url AS u"
                . " WHERE "
                . "         pg.group_id=$group_id "
                . "     AND p.post_id=pg.post_id "
                . "     AND p.posted_by = pi.person_id "
                . "     AND pi.url_id = u.url_id "
                . "     AND p.post_type = 1 "
                . "     AND (p.post_status = 0 OR p.post_status = 1)"
                . " GROUP BY p.post_id LIMIT 10 OFFSET $limit_offset";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_group_all_alert_post($group_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_person as pi, "
                . "     tbl_post as p,"
                . "     tbl_post_group as pg,"
                . "     tbl_url AS u"
                . " WHERE "
                . "         pg.group_id=$group_id "
                . "     AND p.post_id=pg.post_id "
                . "     AND p.posted_by = pi.person_id "
                . "     AND pi.url_id = u.url_id "
                . "     AND post_type = 2 "
                . "     AND (p.post_status = 0 OR p.post_status = 1)"
                . " GROUP BY p.post_id ";

        $result = $this->db->query($sql)->result();
        return $result;
    }
    
     public function select_limited_group_all_alert_post($group_id, $limit_offset) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_person as pi, "
                . "     tbl_post as p,"
                . "     tbl_post_group as pg,"
                . "     tbl_url AS u"
                . " WHERE "
                . "         pg.group_id=$group_id "
                . "     AND p.post_id=pg.post_id "
                . "     AND p.posted_by = pi.person_id "
                . "     AND pi.url_id = u.url_id "
                . "     AND post_type = 2 "
                . "     AND (p.post_status = 0 OR p.post_status = 1)"
                . " GROUP BY p.post_id LIMIT 10 OFFSET $limit_offset";

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

    public function select_group_all_assignment_post($group_id, $person_url = '') {
        $this->db->select('*')
                ->from('tbl_person as pi')
                ->join('tbl_post as p', 'p.posted_by = pi.person_id')
                ->join('tbl_post_group as pg', 'p.post_id=pg.post_id')
                ->join('tbl_url AS u', 'u.url_id=pi.url_id');
        $this->db->where('p.post_type', 3)->where('pg.group_id', $group_id)
                ->where_in('p.post_status', Array(0, 1))
                ->where('pg.post_visibility_status', 1);
        if ($person_url != '') {
            $this->db->where('u.url', $person_url);
        }
        return $this->db->group_by('p.post_id')->get()->result();
    }

    function get_all_group_post($post_type, $group_id, $person_url = '') {
        $this->db->select('*')
                ->from('tbl_person as pi')
                ->join('tbl_post as p', 'p.posted_by = pi.person_id')
                ->join('tbl_post_group as pg', 'p.post_id=pg.post_id')
                ->join('tbl_url AS u', 'u.url_id=pi.url_id');
        $this->db->where('p.post_type', $post_type)->where('pg.group_id', $group_id)
                ->where_in('p.post_status', Array(0, 1))
                ->where('pg.post_visibility_status', 1);
        if ($person_url != '') {
            $this->db->where('u.url', $person_url);
        }
        return $this->db->group_by('p.post_id')->get()->result();
    }
    
    function get_limited_all_group_post($post_type, $group_id, $limit_offset) {
        $this->db->select('*')
                ->from('tbl_person as pi')
                ->join('tbl_post as p', 'p.posted_by = pi.person_id')
                ->join('tbl_post_group as pg', 'p.post_id=pg.post_id')
                ->join('tbl_url AS u', 'u.url_id=pi.url_id');
        $this->db->where('p.post_type', $post_type)->where('pg.group_id', $group_id)
                ->where_in('p.post_status', Array(0, 1))
                ->where('pg.post_visibility_status', 1)
                ->limit(10, $limit_offset);
        
        return $this->db->group_by('p.post_id')->get()->result();
    }

    public function select_group_all_photo_post($group_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_person as pi, "
                . "     tbl_post as p,"
                . "     tbl_post_group as pg,"
                . "     tbl_url AS u "
                . " WHERE "
                . "         pg.group_id=$group_id "
                . "     AND p.post_id=pg.post_id "
                . "     AND p.posted_by = pi.person_id "
                . "     AND pi.url_id = u.url_id "
                . "     AND p.post_type = 4 "
                . "     AND (p.post_status = 0 OR p.post_status = 1)"
                . "     AND  	post_visibility_status=1"
                . " GROUP BY p.post_id ";

        $result = $this->db->query($sql)->result();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

    public function select_group_all_video_post($group_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_person as pi, "
                . "     tbl_post as p,"
                . "     tbl_post_group as pg,"
                . "     tbl_url AS u "
                . " WHERE "
                . "         pg.group_id=$group_id "
                . "     AND p.post_id=pg.post_id "
                . "     AND p.posted_by = pi.person_id "
                . "     AND pi.url_id = u.url_id "
                . "     AND p.post_type = 5 "
                . "     AND (p.post_status = 0 OR p.post_status = 1)"
                . "     AND pg.post_visibility_status=1"
                . " GROUP BY p.post_id ";

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

    public function select_group_courses($group_id) {
//        $sql = "SELECT * from  tbl_course_group_permission where (group_id LIKE '%,$group_id' OR group_id LIKE '$group_id,%' OR group_id LIKE '$group_id' OR group_id LIKE '%,$group_id,%' ) AND  course_group_type=1";
//        $result = $this->db->query($sql)->result();
//        return $result;
    }

    public function select_group_video_courses($group_id) {
        //echo $group_id;
//        $sql = "SELECT * from  tbl_course_group_permission where (group_id LIKE '%,$group_id' OR group_id LIKE '$group_id,%' OR group_id LIKE '$group_id' OR group_id LIKE '%,$group_id,%' ) AND  course_group_type=2";
//        $result = $this->db->query($sql)->result();
//        //print_r($result);
//        //exit();
//        return $result;
    }

    public function active_selected_post($post_id) {
        $this->db->set('post_status', 1);
        $this->db->where('post_id', $post_id);
        $this->db->update('tbl_post');
    }

    public function inactive_selected_post($post_id) {
        $this->db->set('post_status', 0);
        $this->db->where('post_id', $post_id);
        $this->db->update('tbl_post');
    }

    public function archive_selected_post($post_id) {
        $this->db->set('post_status', 2);
        $this->db->where('post_id', $post_id);
        $this->db->update('tbl_post');
    }

    public function select_question_by_quiz_id($quiz_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_quiz as qz, "
                . "     tbl_question as q "
                . " WHERE "
                . "         qz.quiz_id = $quiz_id "
                . "     AND q.quiz_id = qz.quiz_id ";

        $result = $this->db->query($sql)->row();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

    public function selected_quiz_total($group_id) {
        $sql = "SELECT count(*) as total_quiz FROM tbl_quiz as q WHERE q.group_id =$group_id ";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function selected_note_total($group_id) {
        $sql = "SELECT count(*) as total_note FROM tbl_post_group as pg WHERE pg.group_id =$group_id ";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function selected_group_details($group_id) {
        $sql = " SELECT g.*,gt.group_type_name , u.url"
                . " FROM "
                . "     tbl_group as g, "
                . "     tbl_group_type as gt, "
                . "     tbl_url u "
                . " WHERE "
                . "         g.group_id= $group_id "
                . "     AND g.url_id = u.url_id "
                . "     AND g.group_type_id = gt.group_type_id";
        $result = $this->db->query($sql)->result();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

    public function selected_group_details_info($group_id) {

        $sql = "SELECT g.*,u.url,u.url_id,gt.group_type_name FROM tbl_group as g, tbl_url as u, tbl_group_type as gt  WHERE g.group_id=$group_id AND g.url_id=u.url_id AND g.group_type_id=gt.group_type_id";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_group_creator_id($created_by) {
        $this->db->select('person_id');
        $this->db->from('tbl_group_member');
        $this->db->where('person_id', $created_by);
        $query_result = $this->db->get();
        $result = $query_result->row();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

    public function check_member_requst($person_id, $group_id) {

        $user_id = $this->session->userdata('user_id');
        $sql = "SELECT * FROM tbl_group_member WHERE group_id='$group_id' AND person_id='$user_id' AND (group_member_status=1 OR group_member_status=3 OR group_member_status=5 OR group_member_status=4) ";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function check_group_active_member($person_id, $group_id) {
        $sql = "SELECT  * "
                . " FROM tbl_group_member as gm "
                . " WHERE "
                . "         gm.person_id = $person_id "
                . "     AND gm.group_id = $group_id ";

        $query_result = $this->db->query($sql);
        $result = $query_result->row();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

    public function group_list_info($created_by_id) {
        $sql = "SELECT  gm.* , g.group_created_by FROM tbl_group_member as gm ,tbl_group as g  "
                . "WHERE gm.person_id=$created_by_id AND gm.group_id=g.group_id  AND gm.group_member_status=1 ";

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

        return $result;
    }

    public function count_created_group_info($user_id) {
        $sql = "SELECT * FROM tbl_group as g, tbl_url as u "
                . " WHERE g.group_created_by = $user_id AND g.url_id = u.url_id "
                . " AND(g.group_status = 0 OR g.group_status=1)";

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

        return $result;
    }

    public function count_created_group_info_for_search($user_id, $given_text) {

        $sql = "SELECT * FROM tbl_group as g "
                . " WHERE g.group_created_by = '$user_id'  "
                . " AND(g.group_status = 0 OR g.group_status=1)";
        if ($given_text != '') {
            $sql .= " AND group_name LIKE '%$given_text%'";
        }
        $result = $this->db->query($sql)->result();

        return $result;
    }

    public function created_group_info($user_id, $number) {
        $sql = "SELECT * FROM tbl_group as g, tbl_url as u "
                . " WHERE g.group_created_by = $user_id AND g.url_id = u.url_id "
                . " AND(g.group_status = 0 OR g.group_status=1)";
        if ($number) {
            $sql.=" ORDER BY g.group_id DESC LIMIT $number,10 ";
        } else if ($number == NULL) {
            $sql.=" ORDER BY g.group_id DESC LIMIT 10 ";
        }
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function created_group_info_asc($user_id, $number) {
        $sql = "SELECT * FROM tbl_group as g, tbl_url as u "
                . " WHERE g.group_created_by = $user_id AND g.url_id = u.url_id "
                . " AND(g.group_status = 0 OR g.group_status=1)";
        if ($number) {

            $sql.=" ORDER BY g.group_name ASC LIMIT $number,10 ";
        } else if ($number == NULL) {
            $sql.=" ORDER BY g.group_name ASC LIMIT 10 ";
        }
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function created_group_info_dsc($user_id, $number) {
        $sql = "SELECT * FROM tbl_group as g, tbl_url as u "
                . " WHERE g.group_created_by = $user_id AND g.url_id = u.url_id "
                . " AND(g.group_status = 0 OR g.group_status=1)";
        if ($number) {
            $sql.=" ORDER BY g.group_name DESC LIMIT $number,10 ";
        } else if ($number == NULL) {
            $sql.=" ORDER BY g.group_name DESC LIMIT 10 ";
        }
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function created_group_info_asc_member($user_id, $number) {
        $sql = " SELECT * 
               FROM tbl_group AS g,
               tbl_url AS u
               WHERE g.url_id = u.url_id
               AND g.group_created_by = '$user_id'
               AND u.url_type = '2'
            
               ORDER By (SELECT count(*)
               FROM tbl_group_member AS gm
               WHERE gm.group_id = g.group_id
               AND gm.group_member_status=1) DESC";

        if ($number) {
            $sql.=" LIMIT $number,10 ";
        } else if ($number == NULL) {
            $sql.="  LIMIT 10 ";
        }
        $result = $this->db->query($sql)->result();
        return $result;
    }

    function created_group_info_dsc_member($user_id, $number) {
        $sql = " SELECT * 
               FROM tbl_group AS g,
               tbl_url AS u
               WHERE g.url_id = u.url_id
               AND g.group_created_by = '$user_id'
               AND u.url_type = '2'
            
               ORDER By (SELECT count(*)
               FROM tbl_group_member AS gm
               WHERE gm.group_id = g.group_id
               AND gm.group_member_status=1)";

        if ($number) {
            $sql.=" LIMIT $number,10 ";
        } else if ($number == NULL) {
            $sql.="  LIMIT 10 ";
        }
        $result = $this->db->query($sql)->result();
        return $result;
    }

    function created_group_info_asc_request_member($user_id, $number) {
        $sql = " SELECT * 
               FROM tbl_group AS g,
               tbl_url AS u
               WHERE g.url_id = u.url_id
               AND g.group_created_by = '$user_id'
               AND u.url_type = '2'
            
               ORDER By (SELECT count(*)
               FROM tbl_group_member AS gm
               WHERE gm.group_id = g.group_id
               AND gm.group_member_status=3) DESC";

        if ($number) {
            $sql.=" LIMIT $number,10 ";
        } else if ($number == NULL) {
            $sql.="  LIMIT 10 ";
        }
        $result = $this->db->query($sql)->result();
        return $result;
    }

    function created_group_info_dsc_request_member($user_id, $number) {
        $sql = " SELECT * 
               FROM tbl_group AS g,
               tbl_url AS u
               WHERE g.url_id = u.url_id
               AND g.group_created_by = '$user_id'
               AND u.url_type = '2'
            
               ORDER By (SELECT count(*)
               FROM tbl_group_member AS gm
               WHERE gm.group_id = g.group_id
               AND gm.group_member_status=3) ";

        if ($number) {
            $sql.=" LIMIT $number,10 ";
        } else if ($number == NULL) {
            $sql.="  LIMIT 10 ";
        }
        $result = $this->db->query($sql)->result();
        return $result;
    }

    function created_group_info_by_searching_given_text($user_id, $number, $given_text) {
        $sql = " SELECT * 
               FROM tbl_group AS g,
               tbl_url AS u
               WHERE g.url_id = u.url_id
               AND g.group_created_by = '$user_id'
               AND (g.group_status = 1 OR g.group_status = 0)
               AND u.url_type = '2' AND group_name LIKE '%$given_text%'";

        if ($number) {
            $sql.=" LIMIT $number,10 ";
        } else if ($number == NULL) {
            $sql.="  LIMIT 10 ";
        }
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_student_connected_group($user_id, $number) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_group_member as gm, "
                . " tbl_group as g"
                . " WHERE "
                . " gm.person_id = $user_id "
                . "  AND(gm.group_member_status = 1 OR gm.group_member_status = 3)"
                . "  AND gm.group_id=g.group_id "
                . "  AND g.group_status=1"
                . "  AND  g.group_created_by!= '$user_id'  ";
        if ($number) {
            $sql.= " ORDER by g.group_id DESC LIMIT $number , 10 ";
        } else if ($number == NULL) {
            $sql.= " ORDER by g.group_id DESC LIMIT 10 ";
        }
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function count_select_student_connected_group($user_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_group_member as gm, "
                . " tbl_group as g"
                . " WHERE "
                . " gm.person_id = $user_id "
                . "  AND(gm.group_member_status = 1 OR gm.group_member_status = 3)"
                . "  AND gm.group_id=g.group_id "
                . "  AND g.group_status=1"
                . "  AND  g.group_created_by!= '$user_id'  ";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function count_select_student_connected_group_for_searching($user_id, $given_text) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_group_member as gm, "
                . " tbl_group as g"
                . " WHERE "
                . " gm.person_id = $user_id "
                . "  AND(gm.group_member_status = 1 OR gm.group_member_status = 3)"
                . "  AND gm.group_id=g.group_id "
                . "  AND g.group_status=1"
                . "  AND  g.group_created_by!= '$user_id' "
                . "  AND group_name LIKE '%$given_text%' ";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_student_connected_group_for_searching($user_id, $number, $given_text) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_group_member as gm, "
                . " tbl_group as g"
                . " WHERE "
                . " gm.person_id = $user_id "
                . "  AND(gm.group_member_status = 1 OR gm.group_member_status = 3)"
                . "  AND gm.group_id=g.group_id "
                . "  AND g.group_status=1"
                . "  AND  g.group_created_by!= '$user_id' "
                . "    AND group_name LIKE '%$given_text%'  ";
        if ($number) {
            $sql.= " ORDER by g.group_id DESC LIMIT $number , 10 ";
        } else if ($number == NULL) {
            $sql.= " ORDER by g.group_id DESC LIMIT 10 ";
        }
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function group_info_by_url($url) {
        $sql = "SELECT * FROM tbl_group as g, tbl_url as u WHERE u.url = '$url' AND g.url_id = u.url_id";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function group_info_by_public_id($public_id) {
        $sql = "SELECT * FROM tbl_group as g, tbl_url as u WHERE g.group_public_id = '$public_id' AND g.url_id = u.url_id";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function group_info($group_id) {
        $sql = "SELECT g.*,u.url,u.url_id FROM tbl_group as g, tbl_url as u WHERE g.group_id=$group_id AND g.url_id=u.url_id ";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function group_info_for_public($group_id) {
        $sql = "SELECT * 
                FROM 
                    tbl_group as g, 
                    tbl_url as u 
               WHERE 
                    g.group_id= '$group_id' 
                AND g.url_id = u.url_id 
                AND g.group_status = 1 ";

        $result = $this->db->query($sql)->row();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

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

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

    public function select_group($text) {
        $sql = "SELECT * FROM tbl_group as g WHERE g.group_name LIKE '%$text%' AND g.group_status = 1 ";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function group_member_info_by_url($person_url) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_group_member as gm, "
                . "     tbl_url as u,"
                . "     tbl_person as p "
                . " WHERE "
                . "         u.url = '$person_url' "
                . "     AND u.url_id = p.url_id "
                . "     AND gm.person_id = p.person_id "
        ;

        $result = $this->db->query($sql)->row();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

    public function select_user($text) {
        $sql = "SELECT * FROM tbl_person as pi WHERE pi.person_first_name LIKE '$text%'";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function archive_group_member($group_id, $user_id) {
        $leave_time = date("Y/m/d H:i:s");
        $this->db->set('group_member_status', 2);
        $this->db->set('group_leave_date_time', $leave_time);
        $this->db->where('person_id', $user_id);
        $this->db->where('group_id', $group_id);
        $this->db->update('tbl_group_member');
    }

    public function active_archive_member_id($user_id, $group_id) {
        $req_time = date("Y/m/d H:i:s");
        $this->db->set('group_member_status', 3);
        $this->db->set('group_join_date_time', $req_time);
        $this->db->where('person_id', $user_id);
        $this->db->where('group_id', $group_id);
        $this->db->update('tbl_group_member');
    }

    public function check_group_code($code) {
        $this->db->select('*');
        $this->db->from('tbl_group');
        $this->db->where('group_public_id', $code);
        $query_result = $this->db->get();
        $result = $query_result->row();

        return $result;
    }

//join group updated by afrin jahan noumin
    public function check_join_group_code_id($code) {
        $this->db->select('*');
        $this->db->from('tbl_group');
        $this->db->where('group_public_id', $code);
//        $this->db->where('group_status',1);
        $query_result = $this->db->get();
        $result = $query_result->row();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

    public function check_join_group_status($group_id) {
        $this->db->select('*');
        $this->db->from('tbl_group');
        $this->db->where('group_id', $group_id);
        $query_result = $this->db->get();
        $result = $query_result->row();
        return $result;
    }

    public function check_group_member_status($group_id) {
        $user_id = $this->session->userdata('user_id');

        $sql = "SELECT * FROM  tbl_group_member WHERE group_id='$group_id' AND person_id='$user_id' AND (group_member_status=1 OR group_member_status=3 OR group_member_status=5 OR group_member_status=4) ";
        $result = $this->db->query($sql)->row();

        return $result;
    }

    public function check_group_url($code) {
        $this->db->select('*');
        $this->db->from('tbl_url');
        $this->db->where('url', $code);
        $query_result = $this->db->get();
        $result = $query_result->row();
        return $result;
    }

    //group model by afrin jahan noumin
    //check group type exists or not
    public function check_whether_group_type_exists_or_not($value) {
        $sql = "SELECT * "
                . " FROM "
                . " tbl_group_type "
                . " WHERE "
                . "  group_type_name='$value' ";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function fornt_end_group_url_exists() {
        $url_status = $this->input->post('url_status', TRUE);
        $video_url = $this->input->post('group_url', TRUE);
        if ($url_status == '1') {
            echo $this->url_model->check_url_exists($video_url);
        } else {
            echo $this->url_model->check_url_exists1($video_url);
        }
    }

//download group content by afrin jahan noumin
    public function select_group_creator($group_id) {
        $sql = "SELECT * "
                . " FROM "
                . " tbl_group "
                . " WHERE "
                . " group_id='$group_id' ";
        $result = $this->db->query($sql)->row();
        return $result;
    }

//end download group content by afrin jahan noumin
//    public function update_url_by_id($udata, $url_id) {
//        $this->db->where('url_id', $url_id);
//        $this->db->update('tbl_url', $udata);
//    }
    public function select_specific_post_info($post_id) {
        $sql = "SELECT * FROM tbl_post WHERE post_id='$post_id'";
        $result = $this->db->query($sql)->row();
        return $result;
    }

//delete_group_content by afrin jahan noumin
    public function check_group_creator_id($group_id) {
        $sql = "SELECT * FROM  tbl_group WHERE  group_id='$group_id' ";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function remove_post_group_note_by_admin_or_creator($post_id, $group_id) {
        $this->db->set('post_visibility_status', 0);
        $this->db->where('group_id', $group_id);
        $this->db->where('post_id', $post_id);
        $this->db->update('tbl_post_group');
    }

    public function select_post_visibity_for_all_group($post_id) {
        $sql = "SELECT * FROM tbl_post_group WHERE post_id='$post_id'";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function check_group_visibility_for_that_post($post_id, $grp_id, $grp_id_array) {
        //  if(count($grp_id_array)>1){
        // echo $grp_id;
        $sql = "SELECT * FROM tbl_post_group WHERE group_id IN ($grp_id) AND post_id=$post_id AND post_visibility_status=1 ";
        $result = $this->db->query($sql)->result();
        //print_r($result);
        return $result;
        // }
//     else if(count($grp_id_array)==1){
//        //echo $grp_id_array;
//        $sql="SELECT * FROM tbl_post_group WHERE group_id='$grp_id_array' AND post_visibility_status=1";
//        $result = $this->db->query($sql)->result();
//        return $result;
//     }
    }

    public function change_post_status($post_id) {
        $this->db->set('post_status', 4);
        $this->db->where('post_id', $post_id);
        $this->db->update('tbl_post');
    }

    //view course list in fornt end group by afrin jahan noumin
    public function get_url_id($url_name) {
        $sql = "SELECT * FROM tbl_url WHERE  url='$url_name'";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_group_id_for_note($url_id) {
        $sql = "SELECT * FROM tbl_group WHERE url_id='$url_id'";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_courses_for_group($group_id) {
        $sql = "SELECT * FROM tbl_course_group_permission WHERE (group_id LIKE '%,$group_id' OR group_id LIKE '$group_id,%' OR group_id LIKE '$group_id' OR group_id LIKE '%,$group_id,%' ) AND course_group_type = 1";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_group_course_info_by_course_id($course_id) {
        $sql = "SELECT * FROM  tbl_course WHERE course_id= '$course_id'";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_course_url($url_id) {
        $sql = "SELECT * FROM   tbl_url WHERE  url_id= '$url_id'";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_group_course_student($course_id) {
        $sql = "SELECT * FROM   tbl_student WHERE  course_id= '$course_id' AND student_type=1";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_course_id_by_url($url_id) {
        $sql = "SELECT * FROM  tbl_course WHERE  url_id= '$url_id'";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_person_role($person_id) {
        $sql = "SELECT * FROM tbl_person WHERE  person_id= '$person_id'";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_student_for_that_course($course_id) {
        $sql = "SELECT * FROM   tbl_student WHERE  course_id= '$course_id' AND student_type=1";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_student_info_person_id($person_id) {
        $sql = "SELECT * FROM tbl_person WHERE  person_id= '$person_id'";
        $result = $this->db->query($sql)->row();
        return $result;
    }

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

    /**
     * @Function            : select_all_front_end_selected_group 
     * @Used IN             : @function-'get_front_end_group_name'       user_video[@Controller]
     * @                    : @function-'check_group_validation'         user_video[@Controller] - [215]
     * @Description         : get all front end selected group
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 19-03-2016 01:11 AM   
     * @Parameter           : $all_person_id[STRING], $selected_group_list[STRING]
     * @Return              : all_seleced_group     
     * @Status              : available      
     * */
    function select_all_front_end_selected_group($all_person_id, $selected_group_list) {
        $sql = "SELECT * 
                FROM 
                    tbl_group AS g
                WHERE 
                    g.group_created_by IN ($all_person_id)";
        if ($selected_group_list) {
            $sql.=" AND g.group_id IN ($selected_group_list)";
        }
        return $this->db->query($sql)->result();
    }

    /**
     * @Function            : get_person_id_by_person_information 
     * @Used IN             : @function-'check_date_time_feature_status'           classroom[@Controller]
     * @                    : @function-'get_selected_group_list_by_person_id'     this[@Model]
     * @Description         : get all front end group by search
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 14-03-2016 12:37 AM  
     * @Parameter           : $all_person_id[STRING], $search_group_name[STRING], $selected_group_list[STRING]
     * @Return              : all_searching_group      
     * @Status              : available      
     * */
    function select_all_front_end_group_by_search($all_person_id, $search_group_name, $selected_group_list) {
        $sql = "SELECT * 
                FROM 
                    tbl_group AS g
                WHERE 
                    g.group_created_by IN ($all_person_id)
                AND g.group_status = '1'
                AND g.group_name LIKE '%$search_group_name%'";
        if ($selected_group_list != '') {
            $sql.= " AND g.group_id NOT IN ($selected_group_list)";
        }
        return $this->db->query($sql)->result();
    }

    /**
     * @Function            : select_group_info_for_group_member 
     * @Used IN             : 
     * @Description         : Select group info for member of that group
     * @Developed By        : Afrin Jahan Noumin
     * @Start DateTime      : 08-05-2016 12:37 AM  
     * @Parameter           : $user_id
     * @Return              :       
     * @Status              : available      
     * */
    function select_group_info_for_group_member($user_id, $number) {
        $sql = "SELECT * FROM "
                . " tbl_group as g,"
                . " tbl_url as u "
                . " WHERE g.group_created_by='$user_id'"
                . " AND g.url_id= u.url_id ";
        if ($number) {
            $sql.= "  ORDER BY g.group_id DESC LIMIT $number,10";
        } else if ($number == NULL) {
            $sql.= " ORDER BY g.group_id DESC LIMIT 10 ";
        }
        $result = $this->db->query($sql)->result();
        return $result;
    }

    function select_detail_group_info_asc($user_id, $number) {
        $sql = "SELECT * FROM "
                . " tbl_group as g,"
                . " tbl_url as u "
                . " WHERE g.group_created_by='$user_id'"
                . " AND g.url_id= u.url_id ";
        if ($number) {
            $sql.= "  ORDER BY g.group_name  LIMIT $number,10";
        } else if ($number == NULL) {
            $sql.= " ORDER BY g.group_name  LIMIT 10 ";
        }
        $result = $this->db->query($sql)->result();
        return $result;
    }

    function select_detail_group_info_dsc($user_id, $number) {
        $sql = "SELECT * FROM "
                . " tbl_group as g,"
                . " tbl_url as u "
                . " WHERE g.group_created_by='$user_id'"
                . " AND g.url_id= u.url_id ";
        if ($number) {
            $sql.= "  ORDER BY g.group_name DESC LIMIT $number,10";
        } else if ($number == NULL) {
            $sql.= " ORDER BY g.group_name DESC LIMIT 10 ";
        }
        $result = $this->db->query($sql)->result();
        return $result;
    }

    function select_group_info_by_giving_search_text($user_id, $number, $given_text) {
        $sql = "SELECT * FROM"
                . " tbl_group as g,"
                . " tbl_url as u "
                . " WHERE g.group_created_by='$user_id'"
                . " AND g.url_id= u.url_id "
                . " AND group_name LIKE '%$given_text%'";
        if ($number) {
            $sql.= "  ORDER BY g.group_id DESC LIMIT $number,10";
        } else if ($number == NULL) {
            $sql.= " ORDER BY g.group_id DESC LIMIT 10 ";
        }
        $result = $this->db->query($sql)->result();
        return $result;
    }

    /**
     * @Function            : select_group_info_for_group_member 
     * @Used IN             : 
     * @Description         : count all group info for that user
     * @Developed By        : Afrin Jahan Noumin
     * @Start DateTime      : 08-05-2016 12:37 AM  
     * @Parameter           : $user_id
     * @Return              :       
     * @Status              : available      
     * */
    function select_count_group_info_for_group_member($user_id) {
        $sql = "SELECT * FROM "
                . " tbl_group as g,"
                . " tbl_url as u "
                . " WHERE g.group_created_by='$user_id'"
                . " AND g.url_id= u.url_id";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    function select_count_group_info_for_group_member_for_search($user_id, $given_text) {
        $sql = " SELECT * FROM "
                . " tbl_group as g"
                . "  WHERE g.group_created_by='$user_id' "
                . "  AND g.group_name LIKE '%$given_text%' ";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    function select_group_info_for_group_learner_member($user_id, $number) {
        $sql = "SELECT * FROM tbl_group_member as gm,"
                . " tbl_group as g,"
                . " tbl_url as u "
                . " WHERE g.group_created_by!='$user_id'"
                . " AND gm.person_id ='$user_id'"
                . " AND g.group_id= gm.group_id"
                . " AND gm.group_member_status=1"
                . " AND g.url_id= u.url_id ";
        if ($number) {
            $sql.= "  ORDER BY g.group_id DESC LIMIT $number,10";
        } else if ($number == NULL) {
            $sql.= " ORDER BY g.group_id DESC LIMIT 10 ";
        }
        $result = $this->db->query($sql)->result();
        return $result;
    }

    function select_count_group_info_for_group_learner_member($user_id) {
        $sql = "SELECT * FROM tbl_group_member as gm,"
                . " tbl_group as g,"
                . " tbl_url as u "
                . " WHERE g.group_created_by!='$user_id'"
                . " AND gm.person_id ='$user_id'"
                . " AND g.group_id= gm.group_id"
                . " AND gm.group_member_status=1"
                . " AND g.url_id= u.url_id";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    function select_detail_learner_group_info_asc($user_id, $number) {
        $sql = "SELECT * FROM tbl_group_member as gm,"
                . " tbl_group as g,"
                . " tbl_url as u "
                . " WHERE g.group_created_by!='$user_id'"
                . " AND gm.person_id ='$user_id'"
                . " AND g.group_id= gm.group_id"
                . " AND gm.group_member_status=1"
                . " AND g.url_id= u.url_id ";
        if ($number) {
            $sql.= "  ORDER BY g.group_name  LIMIT $number,10";
        } else if ($number == NULL) {
            $sql.= " ORDER BY g.group_name  LIMIT 10 ";
        }
        $result = $this->db->query($sql)->result();
        return $result;
    }

    function select_detail_learner_group_info_dsc($user_id, $number) {
        $sql = "SELECT * FROM tbl_group_member as gm,"
                . " tbl_group as g,"
                . " tbl_url as u "
                . " WHERE g.group_created_by!='$user_id'"
                . " AND gm.person_id ='$user_id'"
                . " AND g.group_id= gm.group_id"
                . " AND gm.group_member_status=1"
                . " AND g.url_id= u.url_id ";
        if ($number) {
            $sql.= "  ORDER BY g.group_name DESC LIMIT $number,10";
        } else if ($number == NULL) {
            $sql.= " ORDER BY g.group_name DESC LIMIT 10 ";
        }
        $result = $this->db->query($sql)->result();
        return $result;
    }

    function select_learner_group_info_by_giving_search_text($user_id, $number, $given_text) {
        $sql = "SELECT * FROM tbl_group_member as gm, "
                . " tbl_group as g,"
                . " tbl_url as u "
                . " WHERE g.group_created_by!='$user_id'"
                . " AND gm.person_id ='$user_id'"
                . " AND g.group_id= gm.group_id"
                . " AND gm.group_member_status=1"
                . " AND g.url_id= u.url_id "
                . " AND group_name LIKE '%$given_text%'";
        if ($number) {
            $sql.= "  ORDER BY g.group_id DESC LIMIT $number,10";
        } else if ($number == NULL) {
            $sql.= " ORDER BY g.group_id DESC LIMIT 10 ";
        }
        $result = $this->db->query($sql)->result();
        return $result;
    }

    function select_count_group_info_for_group_learner_member_for_search($user_id, $given_text) {
        $sql = "SELECT * FROM tbl_group_member as gm, "
                . " tbl_group as g"
                . " WHERE g.group_created_by!='$user_id'"
                . " AND gm.person_id ='$user_id'"
                . " AND g.group_id= gm.group_id"
                . " AND gm.group_member_status=1"
                . " AND group_name LIKE '%$given_text%'";


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

    function change_selected_group_member_status($member_id_array, $member_id, $value) {
        if (count($member_id_array) > 1) {
            if ($value == '1') {
                $sql = "UPDATE tbl_group_member "
                        . " SET group_member_status=1 "
                        . " WHERE  group_member_id IN ($member_id) ";
            } else if ($value == '2') {
                $sql = "UPDATE tbl_group_member "
                        . " SET group_member_status=2 "
                        . " WHERE  group_member_id IN ($member_id) ";
            }
        } else if (count($member_id_array) == 1) {
            if ($value == '1') {
                $sql = "UPDATE tbl_group_member "
                        . " SET group_member_status=1 "
                        . " WHERE  group_member_id = '$member_id' ";
            } else if ($value == '2') {
                $sql = "UPDATE tbl_group_member "
                        . " SET group_member_status=2 "
                        . " WHERE  group_member_id ='$member_id' ";
            }
        }
        $this->db->query($sql);
    }

    /*
      -----------@@@RIPON@ Start public group search Part----------------------------------
     */

    function get_group_value_by_search($data, $search_value, $start_limit, $end_limit) {
        $searched_group = $this->get_searched_group_information($search_value, $start_limit, $end_limit);
        if ($searched_group) {
            $data['searched_group'] = $searched_group;
            $data['total_searched_group'] = $this->get_total_searched_group_information($search_value);
        }
        return $data;
    }

    /**
     * @Function            : get_searched_group_information 
     * @Used IN             : @function-'view_searched_value'  welcome[@controller],
     * @Description         : get group information by search 
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 27-03-2016 10:24 AM   
     * @Status              : available      
     * */
    function get_searched_group_information($search_value, $start_limit, $end_limit) {
        $sql = "SELECT * 
                FROM 
                    tbl_group AS g,
                    tbl_url AS u
                WHERE 
                    g.url_id = u.url_id
                AND u.url_type = '2'
                AND g.group_status = '1'
                AND g.group_name LIKE '%$search_value%'
                ORDER BY g.group_name ASC
                LIMIT $start_limit,$end_limit";
        return $this->db->query($sql)->result();
    }

    /**
     * @Function            : get_total_searched_group_information 
     * @Used IN             : @function-'view_searched_value'  welcome[@controller],
     * @Description         : get total group information by search 
     * @Developed By        : Ripon Jaherul
     * @Start DateTime      : 27-03-2016 02:01 PM   
     * @Status              : available      
     * */
    function get_total_searched_group_information($search_value) {
        $sql = "SELECT count(*) AS total_group 
                FROM 
                    tbl_group AS g,
                    tbl_url AS u
                WHERE 
                    g.url_id = u.url_id
                AND u.url_type = '2'
                AND g.group_status = '1'
                AND g.group_name LIKE '%$search_value%'";
        $result = $this->db->query($sql)->row();
        return $result->total_group;
    }

    /*
      -----------@@@RIPON@ End public group search Part----------------------------------
     */

    /**
     * @Function            : select_group_member_id_by_group_id 
     * @Description         : select_group_member_id_by_group_id
     * @Developed By        : MD Jaherul Islam
     * @Start DateTime      : 18-07-2016 12:37 PM   
     * @Parameter           : $group_id_string[INTEGER]
     * @Return              : $group_member_id_string[STRING]    
     * @Status              : available      
     * */
    function select_group_member_id_by_group_id($group_id_string) {
        $sql = "SELECT GROUP_CONCAT(gm.person_id)  AS group_member_id_string 
                FROM 
                    tbl_group_member AS gm,
                    tbl_person AS p
                WHERE gm.group_id IN ($group_id_string)
                AND gm.group_member_status = '1'
                AND gm.person_id = p.person_id
                AND p.person_role IN (3,4,5)";
        $result = $this->db->query($sql)->row();
        return $result->group_member_id_string;
    }

    /**
     * @Function            : get_concat_multiple_group_name_by_post_id 
     * @Description         : get concat multiple group name by post ID
     * @Developed By        : MD Jaherul Islam
     * @Start DateTime      : 29-09-2016 05:23 PM   
     * @Parameter           : $post_id[INTEGER]
     * @Return              : $group_name[STRING]    
     * @Status              : available      
     * */
    function get_concat_multiple_group_name_by_post_id($post_id) {
        return $this->db->select('GROUP_CONCAT(g.group_name) AS group_name')
                        ->from('tbl_post_group AS pg')->join('tbl_group AS g', 'pg.group_id = g.group_id')
                        ->where('pg.post_id', $post_id)->get()->row()->group_name;
    }

    /**
     * @Function            : get_group_info_by_group_id 
     * @Description         : get group information by group ID
     * @Developed By        : MD Jaherul Islam
     * @Start DateTime      : 01-10-2016 12:38 PM   
     * @Parameter           : $group_id[INTEGER]
     * @Return              : $group_data[Array] 
     * @Status              : available      
     * */
    function get_group_info_by_group_id($group_id) {
        return $this->db->select('*')->from('tbl_group AS g')
                ->join('tbl_url AS u', 'g.url_id=u.url_id')
                ->where('g.group_id',$group_id)->get()->row();
    }
    
    /**
     * @Function            : selected_group_details_info_by_code 
     * @Description         : gets the group data using group url
     * @Developed By        : Unknown       
     * @Parameter           : $code[url of a group]    
     * @Status              : available      
     * */
    public function selected_group_details_info_by_code($code) {
        $sql = "SELECT g.*,u.url,u.url_id FROM tbl_group as g, tbl_url as u WHERE u.url='$code' AND g.url_id=u.url_id";
        $result = $this->db->query($sql)->row();
        return $result;
    }
    /**
     * @Function            : check_group_public_id 
     * @Description         : gets the group public id exit
     * @Developed By        : kazol            
     * */
    public function check_group_public_id($group_public_id) {
        $sql = "SELECT * FROM tbl_group as q WHERE q.group_public_id = '$group_public_id'";
        $result = $this->db->query($sql)->row();
        if ($result) {
            return 1;
        } else {
            return 2;
        }
    }

}
