<?php

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

/**
 * Description of super_admin_group_model
 *
 * @author User
 */
class Super_admin_group_model extends CI_Model {

    //put your code here


    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 group_type_list() {
        $this->db->select('*');
        $this->db->from('tbl_group_type');
        $this->db->where('group_type_publication_status', 1);

        $query_result = $this->db->get();
        $result = $query_result->result();
        return $result;
    }

    public function save_group_information() {


        $data['group_name'] = $this->input->post('group_name', TRUE);
        $data['group_type_id'] = $this->input->post('group_type', TRUE);
        $data['group_description'] = $this->input->post('group_description', TRUE);
        $data['group_public_id'] = $this->input->post('group_public_id', TRUE);
        $data['group_created_by'] = $this->session->userdata('person_id');
        $data['group_status'] = 1;
        $data['group_permission'] = 1;
        $data['group_created_date_time'] = date('Y-m-d H:i:s');

//save other goup type
        $category_data = array();
        if ($data['group_type_id'] == 'other') {
            $type_data['group_type_name'] = $this->input->post('group_other_type', TRUE);
            $type_data['group_type_publication_status'] = 1;
            $this->db->insert('tbl_group_type', $type_data);
            $insert_id = $this->db->insert_id();
            $data['group_type_id'] = $insert_id;
        }

        $url = $this->input->post('group_url', true);
        $udata = array();
        $udata['url'] = $url;
        $udata['url_type'] = 2;
        $udata['url_status'] = 1;
        $this->db->insert('tbl_url', $udata);
        $insert_url_id = $this->db->insert_id();
        $data['url_id'] = $insert_url_id;

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

        return $group_id;
    }

    public function update_group_information($group_id) {
        $data['group_name'] = $this->input->post('group_name', TRUE);
        $data['group_type_id'] = $this->input->post('group_type', TRUE);

//save other goup type
        $category_data = array();
        if ($data['group_type_id'] == 'other') {
            $type_data['group_type_name'] = $this->input->post('group_other_type', TRUE);
            $type_data['group_type_publication_status'] = 1;
            $this->db->insert('tbl_group_type', $type_data);
            $insert_id = $this->db->insert_id();
            $data['group_type_id'] = $insert_id;
        }

        $url_id = $this->input->post('url_id', TRUE);
        $udata = array();
        $udata['url'] = $this->input->post('group_url', TRUE);
        $this->db->where('url_id', $url_id);
        $this->db->update('tbl_url', $udata);

        $data['group_description'] = $this->input->post('group_description', TRUE);
        $data['group_public_id'] = $this->input->post('group_public_id', TRUE);
        $data['group_description'] = $this->input->post('group_description', TRUE);
        $data['group_created_by'] = $this->session->userdata('user_id');
        $data['group_status'] = 1;
        $data['group_permission'] = 1;
        $data['group_created_date_time'] = date('Y-m-d H:i:s');
        $this->db->where('group_id', $group_id);
        $this->db->update('tbl_group', $data);
    }

    public function make_group_active($group_id) {
        $this->db->set('group_status', '1');
        $this->db->set('group_updated_by', $this->session->userdata('person_id'));
        $this->db->where('group_id', $group_id);
        $this->db->update('tbl_group');
    }

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

    public function archive_group($group_id) {
        $this->db->set('group_status', '2');
        $this->db->set('group_updated_by', $this->session->userdata('group_id'));
        $this->db->set('group_updated_date_time', date('Y-m-d') . time(''));
        $this->db->where('group_id', $group_id);
        $this->db->update('tbl_group');
    }

    /*
     * Group Infomration
     */

    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();
//        echo '<pre>';
//        print_r($result);
//        exit();
        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();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

//    public function group_total_quiz($group_id) {
//        $sql = "SELECT count(*) as total_quiz "
//                . " FROM "
//                . "     tbl_group_quiz_permission as gqp, "
//                . "     tbl_quiz as q "
//                . " WHERE "
//                . "         gqp.group_id = $group_id "
//                . "     AND q.quiz_id = gqp.quiz_id "
//                . "     AND q.quiz_status=1";
//        $result = $this->db->query($sql)->row();
//        echo '<pre>';
//        print_r($result);
//        exit();
//        return $result;
//    }

    public function group_total_note($group_id) {
        $sql = "SELECT count(*) as total_note FROM tbl_post as p, tbl_post_group as pg WHERE pg.group_id = $group_id AND pg.post_id = p.post_id AND p.post_type=1";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function group_total_assignment($group_id) {
        $sql = "SELECT count(*) as total_assignment FROM tbl_post as p, tbl_post_group as pg WHERE pg.group_id = $group_id AND pg.post_id = p.post_id AND p.post_type=2";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function group_total_photo($group_id) {
        $sql = "SELECT count(*) as total_photo FROM tbl_post as p, tbl_post_group as pg WHERE pg.group_id = $group_id AND pg.post_id = p.post_id AND p.post_type=3";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function group_all_active_quizes($group_id) {
        $sql = "SELECT qz.*, p.person_first_name, p.person_last_name, t.topic_name, t.topic_id "
                . " FROM "
                . "     tbl_group_quiz_permission as gqp, "
                . "     tbl_quiz as qz, "
                . "     tbl_topic as t, "
                . "     tbl_person as p "
                . " WHERE "
                . "         gqp.group_id = $group_id "
                . "     AND qz.quiz_id = gqp.quiz_id"
                . "     AND t.topic_id = qz.quiz_topics_specification "
                . "     AND qz.quiz_status=1 "
                . " GROUP BY qz.quiz_id ";
        $result = $this->db->query($sql)->result();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

    public function group_all_quiz($group_id) {
        $sql = "SELECT * FROM tbl_quiz as q, tbl_group as g WHERE q.group_id = $group_id AND g.group_id = q.group_id";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function selected_group_details($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->result();
        return $result;
    }

    public function member_information($member_id) {
        $sql = "SELECT * "
                . "FROM "
                . "     tbl_group as g, "
                . "     tbl_group_member as gm, "
                . "     tbl_person as p "
                . "WHERE "
                . "         gm.group_member_id = $member_id"
                . "     AND gm.group_id = g.group_id"
                . "     AND gm.person_id= p.person_id"
        ;

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

    public function select_member_quiz_result_info_by_grade_id($quiz_id, $person_id) {
        $sql = "SELECT * FROM tbl_quiz_result as qr, tbl_grade as g WHERE qr.quiz_id = $quiz_id AND qr.person_id = $person_id AND qr.grade_id=g.grade_id";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_member_quiz_result_info($quiz_id, $person_id) {
        $sql = "SELECT * FROM tbl_quiz_result as qr WHERE qr.quiz_id = $quiz_id AND qr.person_id = $person_id";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_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->join('tbl_person as p', 'p.person_id = gm.person_id');
        $query_result = $this->db->get();
        $result = $query_result->result();
        return $result;
    }

    /*
     * Group Member Information
     */

    public function select_group_member_quizes_by_member_id($member_id) {
        $sql = "SELECT * "
                . " FROM"
                . "     tbl_group_member as gm, "
                . "     tbl_quiz as q"
                . " WHERE "
                . "         gm.group_member_id = $member_id"
                . "     AND gm.group_id = q.group_id";
        $query_result = $this->db->query($sql);
        $result = $query_result->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();
        return $result;
    }

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

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

    public function select_group_information_by_id($group_id) {
        $sql = "SELECT * FROM tbl_group as g WHERE g.group_id = $group_id";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function group_info($group_id) {
        $sql = "SELECT * FROM tbl_group as g, tbl_url as u, tbl_person as p "
                . " WHERE g.group_id = $group_id AND g.url_id=u.url_id AND p.person_id = g.group_created_by ";
        $result = $this->db->query($sql)->row();
//        echo '<pre>';
//        print_r($sql);
//        exit();
        return $result;
    }

    public function select_group_member($group_id, $start, $limit, $status, $short = NULL) {
        $sql = "SELECT * "
                . "FROM tbl_group as g, tbl_group_member as gm, tbl_person as p, tbl_course_category as cc "
                . "WHERE g.group_id = $group_id AND gm.group_id = g.group_id AND p.person_id = gm.person_id AND cc.course_category_id = g.group_type";

        if ($status != '') {
            $sql .= " gm.group_member_status = $status ";
        }
        if ($short) {
            $sql .= " ORDER BY gm.join_date_time $short ";
        } else {
            $sql .= " ORDER BY gm.join_date_time DESC ";
        }
        if ($limit != '' && $start >= 0) {
            $sql .= " LIMIT $start, $limit ";
        }
        $result = $this->db->query($sql)->result();
        if ($result) {
            return $result;
        } else {
            return '';
        }
    }

    public function make_group_member_active($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 make_group_member_inactive($group_member_id) {
        $this->db->set('group_member_status', '0');
        $this->db->where('group_member_id', $group_member_id);
        $this->db->update('tbl_group_member');
    }

    public function archive_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 select_group_member_information_by_id($group_member_id) {
        $sql = "SELECT * FROM tbl_group as g, tbl_group_member as gm, tbl_person_information as pi WHERE g.group_id=gm.group_id AND gm.group_member_id = $group_member_id AND gm.user_id = pi.user_id";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function update_group_member_information($group_member_id) {
        $data = array();
        $data['group_name'] = $this->input->post('group_name', TRUE);
        $data['group_type'] = $this->input->post('group_type', TRUE);
        $data['group_size'] = $this->input->post('group_size', TRUE);
        $data['group_description'] = $this->input->post('group_description', TRUE);
        $data['group_status'] = $this->input->post('group_status', TRUE);
        $data['updated_by'] = $this->session->userdata('user_id');
        $data['updated_date_time'] = date('y-m-d');
        $this->db->where('group_id', $group_member_id);
        $this->db->update('tbl_group', $data);
    }

    ///backend group work by Afrin Jahan Noumen
    public function save_group_type() {
        $data['group_type_name'] = $this->input->post("group_type_name", TRUE);
        if ($this->input->post("group_type_description", TRUE)) {
            $data['group_type_description'] = $this->get_tinymce_description_value($this->input->post("group_type_description", TRUE));
        } else {
            $data['group_type_description'] = '';
        }
        $data['group_type_status'] = $this->input->post("group_type_status", TRUE);
        $this->db->insert('tbl_group_type', $data);
    }

    public function get_tinymce_description_value($description1) {
        $description = '';
        for ($i = 0; $i < count($description1); $i++) {
            if ($description1[$i] != '') {
                $description = $description1[$i];
                break;
            }
        }
        return $description;
    }

    public function save_group($url_id) {
        $person_id = $this->session->userdata('user_id');
        $group_datetime = date('Y/m/d H:i:s', (time()));
        $data['url_id'] = $url_id;
        if ($this->input->post("group_description", TRUE)) {
            $data['group_description'] = $this->get_tinymce_description_value($this->input->post("group_description", TRUE));
        } else {
            $data['group_type_description'] = '';
        }
        $group_type_name = $this->input->post("group_name", TRUE);
        $string2 = preg_replace("/[\s-]+/", " ", $group_type_name);
        $data['group_type_id'] = $this->input->post("group_type_id", TRUE);
        $data['group_public_id'] = $this->input->post("invitation_code", TRUE);
        $data['group_name'] = $string2;
        $data['group_created_by'] = $person_id;
        $data['group_created_date_time'] = $group_datetime;
        $data['group_permission'] = $this->input->post("user_permission", TRUE);
        $data['group_status'] = $this->input->post("group_status", TRUE);
        $this->db->insert('tbl_group', $data);
        return $this->db->insert_id();
    }

    public function save_group_group_memebr($grp_id) {
        $group_datetime = date('Y/m/d H:i:s', (time()));
        $person_id = $this->session->userdata('user_id');
        $data['person_id'] = $person_id;
        $data['group_id'] = $grp_id;
        $data['group_join_date_time'] = $group_datetime;
        $data['group_member_status'] = 1;
        $this->db->insert('tbl_group_member', $data);
    }

    public function select_type_value_type_id($group_type_id) {
        $sql = "SELECT * FROM tbl_group_type  where group_type_id= $group_type_id";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function update_group_type() {

        $group_type_name = $this->input->post("group_type_name", TRUE);
        $string2 = preg_replace("/[\s-]+/", " ", $group_type_name);
        if ($this->input->post("group_type_description", TRUE)) {
            $group_type_description = $this->get_tinymce_description_value($this->input->post("group_type_description", TRUE));
        } else {
            $group_type_description = '';
        }
        $group_type_status = $this->input->post("group_type_status", TRUE);
        $group_type_id = $this->input->post("group_type_id", TRUE);



        $this->db->set('group_type_name', $string2);
        $this->db->set('group_type_description', $group_type_description);
        $this->db->set('group_type_status', $group_type_status);
        $this->db->where('group_type_id', $group_type_id);
        $this->db->update('tbl_group_type');
    }

    public function change_pending_status($group_type_id) {
        $this->db->set('group_type_status', 4);
        $this->db->where('group_type_id', $group_type_id);
        $this->db->update('tbl_group_type');
    }

    public function change_approved_status($group_type_id) {
        $this->db->set('group_type_status', 3);
        $this->db->where('group_type_id', $group_type_id);
        $this->db->update('tbl_group_type');
    }

    public function change_active_status($group_type_id) {
        $this->db->set('group_type_status', 0);
        $this->db->where('group_type_id', $group_type_id);
        $this->db->update('tbl_group_type');
    }

    public function inactive_status_change($group_type_id) {
        $this->db->set('group_type_status', 1);
        $this->db->where('group_type_id', $group_type_id);
        $this->db->update('tbl_group_type');
    }

    //start delete_group_by_super_admin
    public function select_group_by_group_typ($group_type_id) {
        $sql = "SELECT * FROM tbl_group WHERE  	group_type_id='$group_type_id' ";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function delete_group_type($group_type_id) {
        $this->db->where('group_type_id', $group_type_id);
        $this->db->delete('tbl_group_type');
    }

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

    public function inactive_all_group_memeber($group_id) {
        $this->db->set('group_member_status', 5);
        $this->db->where('group_id', $group_id);
        $this->db->update('tbl_group_member');
    }

//end delete group by super_admin
    public function saved_group_name($group_type_name) {
        $sql = "SELECT * FROM tbl_group_type WHERE group_type_name='$group_type_name' ";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function all_group_type_list() {
        $sql = "SELECT * FROM tbl_group_type WHERE group_type_status=1 OR group_type_status=4 ";
        $result = $this->db->query($sql)->result();
        return $result;
    }

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

    public function select_all_group() {
        $sql = "SELECT * FROM tbl_group";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_all_group_member_for_specific_group($group_id) {

        $sql = "SELECT * FROM tbl_group_member WHERE group_id='$group_id'";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function group_type_name($group_id) {
        $sql = "SELECT * FROM tbl_group WHERE group_id='$group_id'";
        $result = $this->db->query($sql)->row();
        // return $result;
        if ($result) {
            $type_id = $result->group_type_id;
            $sql1 = "SELECT * FROM tbl_group_type WHERE group_type_id='$type_id'";
            $result1 = $this->db->query($sql1)->row();
            return $result1;
        }
    }

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

    public function change_public_status($group_id) {
        $this->db->set('group_permission', 4);
        $this->db->where('group_id', $group_id);
        $this->db->update('tbl_group');
    }

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

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

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

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

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

    //delete_group_by_super_admin_start
    public function delete_group($group_id) {

        $this->db->where('group_id', $group_id);
        $this->db->delete('tbl_group');
    }

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

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

    public function inactive_all_courses_and_video_courses_for_that_group($course_id) {
        $this->db->set('course_status', 6);
        $this->db->where('course_id', $course_id);
        $this->db->update('tbl_course');
    }

    //delete_group_by super_admin_end
    public function check_group_member($group_id) {
        $sql = "SELECT * FROM tbl_group_member WHERE group_id='$group_id'";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_url_name($url_id) {

        $sql = "SELECT * FROM tbl_url WHERE url_id='$url_id' AND url_type=2";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_group_url_id($grp_url) {
        $sql = "SELECT * FROM tbl_url WHERE  url='$grp_url' AND url_type=2";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function check_edit_url($string2, $url_id) {
        $sql = "SELECT * FROM tbl_url WHERE  url='$string2' AND url_type=2 AND url_id!=$url_id";
        $result = $this->db->query($sql)->result();
        return $result;
    }

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

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

    public function select_all_group_type_info() {
        $sql = "SELECT * FROM tbl_group_type WHERE  group_type_status=1 OR group_type_status=4 ";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function check_edit_invitation_code($string2, $group_id) {
        $sql = "SELECT * FROM tbl_group WHERE group_public_id='$string2' AND group_id!='$group_id' ";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function save_edit_group_url($group_url, $group_url_id) {
        // echo $group_url.$group_url_id;
        //exit();
        $this->db->set('url', $group_url);
        $this->db->where('url_id', $group_url_id);
        $this->db->where('url_type', 2);
        $this->db->update('tbl_url');
        return $this->db->insert_id();
    }

    public function update_group($group_id, $url_id) {
        //  echo $url_id;
        $person_id = $this->session->userdata('user_id');
        $group_datetime = date('Y/m/d H:i:s', (time()));

        if ($this->input->post("group_description", TRUE)) {
            $group_description = $this->get_tinymce_description_value($this->input->post("group_description", TRUE));
        } else {
            $group_type_description = '';
        }
        $group_name = $this->input->post("group_name", TRUE);
        // $string2 = preg_replace("/[\s-]+/", " ", $group_type_name);
        $group_type_id = $this->input->post("group_type_id", TRUE);
        $datagroup_public_id = $this->input->post("invitation_code", TRUE);

        $datagroup_created_by = $person_id;
        $datagroup_created_date_time = $group_datetime;
        $datagroup_permission = $this->input->post("user_permission", TRUE);
        $datagroup_status = $this->input->post("group_status", TRUE);

        $this->db->set('url_id', $url_id);
        $this->db->set('group_type_id', $group_type_id);
        $this->db->set('group_public_id', $datagroup_public_id);
        $this->db->set('group_name', $group_name);
        $this->db->set('group_description', $group_description);
        $this->db->set('group_created_by', $person_id);
        $this->db->set('group_permission', $datagroup_permission);
        $this->db->set('group_status', $datagroup_status);
        $this->db->where('group_id', $group_id);
        $this->db->update('tbl_group');
        return $this->db->insert_id();
    }

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

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

    public function select_all_memeber_info($group_id) {
        $sql = "SELECT * FROM  tbl_group_member WHERE group_id='$group_id' AND (group_member_status=1 OR group_member_status=2 OR group_member_status=4 OR group_member_status=5) ";
        $result = $this->db->query($sql)->result();
        return $result;
    }

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

    public function select_all_pending_memeber_info($group_id) {
        $sql = "SELECT * FROM  tbl_group_member WHERE group_id='$group_id' AND group_member_status=3 ";
        $result = $this->db->query($sql)->result();
        return $result;
    }

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

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

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

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

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

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

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

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

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

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

    public function total_group_note($group_id) {
        $person_id = $this->session->userdata('person_id');
        $check_person_status = $this->select_person_role($person_id);
        if ($check_person_status->person_role == 1) {
            $temp_note = array();
            $sql = "SELECT * FROM tbl_post_group WHERE group_id='$group_id' AND post_visibility_status =1 ";
            $result = $this->db->query($sql)->result();

            if ($result) {
                foreach ($result as $value) {
                    $temp_note[] = $value->post_id;
                }
                $temp_note_string = implode(",", $temp_note);
                return $temp_note_string;
            }
        } else if ($check_person_status->person_role == 6) {
            $temp_note = array();
            $sql = "SELECT * FROM tbl_post_group WHERE group_id='$group_id'  ";
            $result = $this->db->query($sql)->result();

            if ($result) {
                foreach ($result as $value) {
                    $temp_note[] = $value->post_id;
                }
                $temp_note_string = implode(",", $temp_note);
                return $temp_note_string;
            }
        }
    }

    public function select_note_info($post_id) {

        if ($post_id) {
            $sql = "SELECT * FROM tbl_post WHERE post_id IN( $post_id ) AND post_type = 1";
            $result = $this->db->query($sql)->result();
            return $result;
        }
    }

    public function select_alert_info($post_id) {
        if ($post_id) {
            $sql = "SELECT * FROM tbl_post WHERE post_id IN( $post_id ) AND post_type = 2";
            $result = $this->db->query($sql)->result();
            return $result;
        }
    }

    public function select_assignment_info($post_id) {
        if ($post_id) {
            $sql = "SELECT * FROM tbl_post WHERE post_id IN( $post_id ) AND post_type = 3";
            $result = $this->db->query($sql)->result();
            return $result;
        }
    }

    public function select_photo_info($post_id) {
        if ($post_id) {
            $sql = "SELECT * FROM tbl_post WHERE post_id IN( $post_id ) AND post_type = 4";
            $result = $this->db->query($sql)->result();
            return $result;
        }
    }

    public function select_video_info($post_id) {
        if ($post_id) {
            $sql = "SELECT * FROM tbl_post WHERE post_id IN( $post_id ) AND post_type = 5";
            $result = $this->db->query($sql)->result();
            return $result;
        }
    }

    public function select_total_course_info($group_id) {
//        if ($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_total_video_course_info($group_id) {
//        if ($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();
//            return $result;
//        }
    }

    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_specific_note_info($post_id) {
        $sql = "SELECT * FROM tbl_post WHERE post_id='$post_id' AND post_type=1";
        $result = $this->db->query($sql)->row();
        return $result;
    }

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

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

    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_group_note($post_id) {
        $this->db->where('post_id', $post_id);
        $this->db->delete('tbl_post');
    }

    public function remove_post_group_note($post_id) {
        $this->db->where('post_id', $post_id);
        $this->db->delete('tbl_post_group');
    }

    public function select_specific_alert_info($post_id) {
        $sql = "SELECT * FROM tbl_post WHERE post_id='$post_id' AND post_type=2";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_specific_assignment_info($post_id) {
        $sql = "SELECT * FROM tbl_post WHERE post_id='$post_id' AND post_type=3";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_specific_photo_info($post_id) {
        $sql = "SELECT * FROM tbl_post WHERE post_id='$post_id' AND post_type=4";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_specific_video_info($post_id) {
        $sql = "SELECT * FROM tbl_post WHERE post_id='$post_id' AND post_type=5";
        $result = $this->db->query($sql)->row();
        return $result;
    }

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

    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_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_url($url_id) {
        $sql = "SELECT * FROM   tbl_url WHERE  url_id= '$url_id'";
        $result = $this->db->query($sql)->row();
        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_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;
    }

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

    public function select_note_url_value($post_id) {
        $sql = "SELECT * FROM  tbl_post_comment_url WHERE  post_or_comment_id= '$post_id' AND  post_comment_url_type=1";
        $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_video_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 = 2";
        $result = $this->db->query($sql)->result();
        return $result;
    }

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

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

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

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

    public function select_student_for_course($course_id, $student_id) {
        $sql = "SELECT * FROM   tbl_student WHERE  course_id= '$course_id' AND student_id='$student_id'";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_group_member_for_group($group_id, $person_id) {
        $sql = "SELECT * FROM   tbl_group_member WHERE   group_id= '$group_id' AND person_id='$person_id'";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_student_payment_status_for_course($payment_id) {

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

    public function delete_select_student_for_course($course_student_id) {
        $this->db->where('student_id', $course_student_id);
        $this->db->delete('tbl_student');
    }

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

    //start group note remove by afrin jahan noumin 
    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');
    }

    //end group note remove by afrin jahan noumin
    public function search_post_from_tbl_post_comment_url($post_id) {
        $sql = "SELECT * FROM  tbl_post_comment_url WHERE  post_or_comment_id='$post_id' AND post_comment_url_type=1";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function delete_post_from_tbl_post_comment_url($post_comment_url_id) {
        $this->db->where('post_comment_url_id', $post_comment_url_id);
        $this->db->delete('tbl_post_comment_url');
    }

//pagination query start by afrin jahan noumin
//start group type_pagination afrin_jahan_noumin
    public function select_limited_type_value($number, $value) {
        $sql = "SELECT * FROM tbl_group_type";
        if ($value != '') {
            $sql.=" WHERE  group_type_name  LIKE '%$value%' OR  group_type_name  LIKE '%$value%'";
        }
        $sql.=" LIMIT $number,10";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_type_value() {
        $sql = "SELECT count(*) AS total_type FROM tbl_group_type ";
        $result = $this->db->query($sql)->row();
        return $result;
    }

// end group type_pagination afrin_jahan_noumin 
// start group_pagination afrin_jahan_noumin\
    public function select_group($number, $value) {
//     if($value==""){
        $sql = "SELECT * FROM tbl_group";
        if ($value != '') {
            $sql.=" WHERE  group_name  LIKE '%$value%' OR  group_name  LIKE '%$value%'";
        }
        $sql.=" LIMIT $number,10";
        $result = $this->db->query($sql)->result();
        return $result;
//      }
//      else if($value!=""){
//         $sql = "SELECT * FROM tbl_group  WHERE  group_name  LIKE '%$value%' OR  group_name  LIKE '%$value%' ";
//         $result = $this->db->query($sql)->result();
//         return $result;
//      }
    }

    public function select_total_group_count() {
        $sql = "SELECT count(*) AS total_group FROM tbl_group ";
        $result = $this->db->query($sql)->row();
        return $result;
    }

// end group_pagination afrin_jahan_noumin 
// start detail group_pagination afrin_jahan_noumin 
    public function select_all_group_list($number, $value) {
        $sql = "SELECT * FROM tbl_group";
        if ($value != '') {
            $sql.=" WHERE  group_name  LIKE '%$value%' ";
        }
        $sql.=" LIMIT $number,10";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_total_all_group_list() {
        $sql = "SELECT count(*) AS total_group FROM tbl_group ";
        $result = $this->db->query($sql)->row();
        return $result;
    }

// end detail group_pagination afrin_jahan_noumin
// start  group note pagination afrin_jahan_noumin
    public function select_note_info_for_group($group_id) {
        $person_id = $this->session->userdata('person_id');
        $check_person_status = $this->select_person_role($person_id);
        if ($check_person_status->person_role == 1) {
            $sql = "SELECT * FROM tbl_post_group WHERE  group_id='$group_id' AND post_visibility_status =1 ";
            $result = $this->db->query($sql)->result();
            return $result;
        } else if ($check_person_status->person_role == 6) {
            $sql = "SELECT * FROM tbl_post_group WHERE  group_id='$group_id'  ";
            $result = $this->db->query($sql)->result();
            return $result;
        }
    }

    public function select_only_note_info_for_group($total_post_id_array, $number, $value) {
        if ($value != NULL) {
            $sql = "SELECT * FROM tbl_post as po ,tbl_person as per  WHERE   po.post_id IN($total_post_id_array) AND  po.post_type=1";
            $sql.=" AND po.posted_by=per.person_id AND CONCAT(per.person_first_name,' ',per.person_last_name)LIKE '%$value%'  ";
            $sql.=" LIMIT $number,10";
            $result = $this->db->query($sql)->result();
            return $result;
        } else {
            $sql = "SELECT * FROM tbl_post WHERE  post_id IN($total_post_id_array) AND  post_type=1";
            $sql.=" LIMIT $number,10";
            $result = $this->db->query($sql)->result();
            return $result;
        }
    }

    public function select_total_note_count_for_that_group($total_post_id_array) {
        $sql = "SELECT count(*) AS total_note FROM tbl_post WHERE  post_id IN($total_post_id_array) AND post_type=1";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    // end group note pagination afrin_jahan_noumin
    //start group alert pagination afrin_jahan_noumin
    public function select_total_alert_count_for_that_group($total_post_id_array) {
        $sql = "SELECT count(*) AS total_alert FROM tbl_post WHERE  post_id IN($total_post_id_array) AND post_type=2";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_only_alert_info_for_group($total_post_id_array, $number, $value) {
        if ($value != NULL) {
            $sql = "SELECT * FROM tbl_post as po ,tbl_person as per  WHERE   po.post_id IN($total_post_id_array) AND  po.post_type=2";
            $sql.=" AND po.posted_by=per.person_id AND CONCAT(per.person_first_name,' ',per.person_last_name)LIKE '%$value%'  ";
            $sql.=" LIMIT $number,10";
            $result = $this->db->query($sql)->result();
            return $result;
        } else {

            $sql = "SELECT * FROM tbl_post WHERE  post_id IN($total_post_id_array) AND  post_type=2";
            $sql.=" LIMIT $number,10";
            $result = $this->db->query($sql)->result();
            return $result;
        }
    }

    //end group alert pagination afrin_jahan_noumin
    //start group assignment pagination afrin_jahan_noumin
    public function select_total_assignment_count_for_that_group($total_post_id_array) {
        $sql = "SELECT count(*) AS total_assignment FROM tbl_post WHERE  post_id IN($total_post_id_array) AND post_type=3";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_only_assignment_info_for_group($total_post_id_array, $number, $value) {
        if ($value != NULL) {
            $sql = "SELECT * FROM tbl_post as po ,tbl_person as per  WHERE   po.post_id IN($total_post_id_array) AND  po.post_type=3";
            $sql.=" AND po.posted_by=per.person_id AND CONCAT(per.person_first_name,' ',per.person_last_name)LIKE '%$value%'  ";
            $sql.=" LIMIT $number,10";
            $result = $this->db->query($sql)->result();
            return $result;
        } else {

            $sql = "SELECT * FROM tbl_post WHERE  post_id IN($total_post_id_array) AND  post_type=3";
            $sql.=" LIMIT $number,10";
            $result = $this->db->query($sql)->result();
            return $result;
        }
    }

    //end group assignment pagination afrin_jahan_noumin
    //start group photo pagination afrin_jahan_noumin

    public function select_total_photo_count_for_that_group($total_post_id_array) {
        $sql = "SELECT count(*) AS total_photo FROM tbl_post WHERE  post_id IN($total_post_id_array) AND post_type=4";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_only_photo_info_for_group($total_post_id_array, $number, $value) {
        if ($value != NULL) {
            $sql = "SELECT * FROM tbl_post as po ,tbl_person as per  WHERE   po.post_id IN($total_post_id_array) AND  po.post_type=4";
            $sql.=" AND po.posted_by=per.person_id AND CONCAT(per.person_first_name,' ',per.person_last_name)LIKE '%$value%'  ";
            $sql.=" LIMIT $number,10";
            $result = $this->db->query($sql)->result();
            return $result;
        } else {
            $sql = "SELECT * FROM tbl_post WHERE  post_id IN($total_post_id_array) AND  post_type=4";
            $sql.=" LIMIT $number,10";
            $result = $this->db->query($sql)->result();
            return $result;
        }
    }

    //end group photo pagination by afrin jahan noumin
    //start group video pagination by afrin jahan noumin
    public function select_total_video_count_for_that_group($total_post_id_array) {
        $sql = "SELECT count(*) AS total_video FROM tbl_post WHERE  post_id IN($total_post_id_array) AND post_type=5";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_only_video_info_for_group($total_post_id_array, $number, $value) {
        if ($value != NULL) {
            $sql = "SELECT * FROM tbl_post as po ,tbl_person as per  WHERE   po.post_id IN($total_post_id_array) AND  po.post_type=5";
            $sql.=" AND po.posted_by=per.person_id AND CONCAT(per.person_first_name,' ',per.person_last_name)LIKE '%$value%'  ";
            $sql.=" LIMIT $number,10";
            $result = $this->db->query($sql)->result();
            return $result;
        } else {
            $sql = "SELECT * FROM tbl_post WHERE  post_id IN($total_post_id_array) AND  post_type=5";
            $sql.=" LIMIT $number,10";
            $result = $this->db->query($sql)->result();
            return $result;
        }
    }

    //end group video pagination by afrin jahan noumin
//pagination query end by afrin jahan noumin
    //start change group_content_status By afrin Jahan noumin
    public function change_group_note_status($post_id, $post_status) {
        if ($post_status == 1) {
            $this->db->set('post_status', 2);
            $this->db->where('post_id', $post_id);
            $this->db->update('tbl_post');
            return 2;
        } else if ($post_status == 2) {
            $this->db->set('post_status', 1);
            $this->db->where('post_id', $post_id);
            $this->db->update('tbl_post');
            return 1;
        }
    }

    //remove_group_member by afrin jahan noumin
    public function check_group_member_person_id($group_member_id) {
        $sql = "SELECT * FROM   tbl_group_member WHERE  group_member_id='$group_member_id'";
        $result = $this->db->query($sql)->row();
        return $result;
    }

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

    //end remove_group_memeber
    //end change group_content_status By afrin jahan noumin
}
