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

/**
 * Description of quiz_model
 *
 * @author monower
 */
class F_end_model extends CI_Model {

//put your code here
//     public function __construct() {
//        parent::__construct();
//        $user_id = $this->session->userdata('user_id');
//       
//    }

    public function check_url_type($code) {
        // 1 = quiz, 2 = group, 3 = course , 4 = course_category, 5 = menu, 6 = video_course_category
        $this->db->select('*');
        $this->db->from('tbl_url');
        $this->db->where('url', $code);
        $query_result = $this->db->get();
        $result = $query_result->row();
        if ($result) {
            return $result->url_type;
        } else {
            return 0;
        }
    }

    public function check_url($code) {
        // 1 = quiz, 2 = group, 3 = course , 4 = course_category, 5 = menu, 6 = video_course_category
        $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 selected_course_category_details_info_by_code($code) {

        $sql = "SELECT * "
                . " FROM "
                . "     tbl_course_category as cc,"
                . "     tbl_url as u "
                . " WHERE "
                . "         u.url = '$code' "
                . "     AND u.url_id = cc.url_id "
        ;

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

    public function selected_course_details_info_by_code($code) {

        $sql = "SELECT * "
                . " FROM "
                . "     tbl_course as c, "
                . "     tbl_url as u "
                . " WHERE "
                . "         u.url = '$code' "
                . "     AND u.url_id = c.url_id "
        ;

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

        return $result;
    }
    
    public function selected_course_details_info_by_menu_url($url) {
        $sql = "SELECT * 
                FROM
                    tbl_menu AS m, 
                    tbl_url AS u,
                    tbl_course AS c
                WHERE 
                    m.url_id = u.url_id
                AND u.url = '$url'
                AND u.url_type = '1'
                AND m.menu_id =  c.menu_id";
        return $this->db->query($sql)->row();
    }
    
    public function get_course_url($course_id) {
        $sql = "SELECT * 
                FROM 
                    tbl_course AS c,
                    tbl_url AS u,
                    tbl_course_category AS cc,
                    tbl_resource_person AS rp,
                    tbl_person AS p
                WHERE 
                    c.course_id = '$course_id'
                AND c.url_id = u.url_id
                AND c.course_category_id = cc.course_category_id
                AND c.resource_person_id = rp.resource_person_id
                AND c.person_id = p.person_id ";
        return $this->db->query($sql)->row();
    }

    public function selected_video_course_details_info_by_code($code) {

        $sql = "SELECT * "
                . " FROM "
                . "     tbl_video_management as vm, "
                . "     tbl_url as u "
                . " WHERE "
                . "         u.url = '$code' "
                . "     AND u.url_id = vm.url_id "
        ;

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

        return $result;
    }

    public function search_course($search_text) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_course as c, "
                . "     tbl_url as u "
                . " WHERE "
                . "         c.course_name LIKE '%" . $search_text . "%' "
                . "     AND u.url_id = c.url_id "
                . "     AND (c.course_status = 1 OR c.course_status = 3 OR c.course_status = 4)"
                . " GROUP BY c.course_id";
        $result = $this->db->query($sql)->result();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

    public function search_teacher($search_text) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_person as p, "
                . "     tbl_url as u "
                . " WHERE "
                . "         (p.person_first_name LIKE '%" . $search_text . "%' "
                . "     OR  p.person_last_name LIKE '%" . $search_text . "%' "
                . "     OR  p.person_email LIKE '%" . $search_text . "%' ) "
                . "     AND u.url_id = p.url_id "
                . "     AND p.person_role = 3 "
                . " GROUP BY p.person_id "
        ;
        $result = $this->db->query($sql)->result();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

    public function search_student($search_text) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_person as p, "
                . "     tbl_url as u "
                . " WHERE "
                . "         p.person_first_name LIKE '%" . $search_text . "%' "
                . "     OR  p.person_last_name LIKE '%" . $search_text . "%' "
                . "     OR  p.person_email LIKE '%" . $search_text . "%' "
                . "     AND u.url_id = p.url_id "
                . "     AND p.person_role = 5 "
                . " GROUP BY p.person_id "
        ;
        $result = $this->db->query($sql)->result();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

    public function save_quiz_info() {


        $data = array();
        $data['quiz_topics_specification'] = $this->input->post('quiz_topic_specification', true);
        $topic_data = array();
        if ($data['quiz_topics_specification'] == 'other') {
            $topic_data['topic_name'] = $this->input->post('other_topic', TRUE);
            $topic_data['topic_status'] = '1';
            $topic_data['topic_created_by'] = $this->session->userdata('user_id');
            $this->db->insert('tbl_topic', $topic_data);
            $insert_id = $this->db->insert_id();
            $data['quiz_topics_specification'] = $insert_id;
        }

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

        $data['quiz_name'] = $this->input->post('quiz_name', true);
        $data['quiz_description'] = $this->input->post('quiz_description', true);
        $data['quiz_public_id'] = $this->input->post('quiz_public_id', true);
        $data['quiz_random_static'] = $this->input->post('quiz_random_static', true);
        if ($data['quiz_random_static'] == 1) {
            $data['total_number_of_question'] = $this->input->post('total_number_of_question', true);
        } else {
            $data['total_number_of_question'] = 0;
        }
        $data['url_id'] = $insert_url_id;
        $data['quiz_permission'] = $this->input->post('quiz_permission', true);
        $data['quiz_status'] = $this->input->post('quiz_status', true);
        $data['quiz_pulication_status'] = $this->input->post('quiz_pulication_status', true);
        $data['quiz_created_by'] = $this->session->userdata('user_id');

        $this->db->insert('tbl_quiz', $data);
        $quiz_id = $this->db->insert_id();

        $group_id = $this->input->post('group_id', true);
        $group_id = explode(",", $group_id);

        $group_permission_data = array();
        $group_permission_data['quiz_id'] = $quiz_id;
        for ($i = 0; $i < count($group_id) - 1; $i++) {
            $group_permission_data['group_id'] = $group_id[$i];
            $this->db->insert('tbl_group_quiz_permission', $group_permission_data);
        }
    }

    public function update_quiz_by_id($quiz_id) {

        $data['quiz_name'] = $this->input->post('quiz_name', true);
        $data['quiz_topics_specification'] = $this->input->post('quiz_topic_specification', true);

        if ($data['quiz_topics_specification'] == 'other') {
            /* ADD NEW TOPIC */
            $topic_data = array();
            $topic_data['topic_name'] = $this->input->post('other_topic', TRUE);
            $topic_data['topic_status'] = '1';
            $topic_data['topic_created_by'] = $this->session->userdata('user_id');
            $this->db->insert('tbl_topic', $topic_data);
            $insert_id = $this->db->insert_id();
            $data['quiz_topics_specification'] = $insert_id;
        }
        /* UPDATE URL */
        $url_id = $this->input->post('url_id', TRUE);
        $udata = array();
        $udata['url'] = $this->input->post('quiz_url', TRUE);
        $this->f_model->update_url_by_id($udata, $url_id);

        $data['quiz_description'] = $this->input->post('quiz_description', true);
        $data['quiz_public_id'] = $this->input->post('quiz_public_id', true);
        $data['total_number_of_question'] = $this->input->post('total_number_of_question', true);
        $data['quiz_permission'] = $this->input->post('quiz_pulication_status', true);
        $data['quiz_status'] = $this->input->post('quiz_status', true);
        $data['quiz_created_by'] = $this->session->userdata('user_id');
        $data['quiz_pulication_status'] = $this->input->post('quiz_pulication_status', true);

        $this->db->where('quiz_id', $quiz_id);
        $this->db->update('tbl_quiz', $data);

        /* Update Selected Group Ingormation */
        $group_id = $this->input->post('group_id', true);
        $pre_group_id = $this->input->post('pre_group_id', true);

        $group_id = explode(",", $group_id);
        $pre_group_id = explode(",", $pre_group_id);
        if ($data['quiz_pulication_status'] == 4) {
            /* ADD new GROUP */
            $group_permission_data = array();
            $group_permission_data['quiz_id'] = $quiz_id;
            for ($i = 0; $i < count($group_id) - 1; $i++) {
                if (in_array($group_id[$i], $pre_group_id) == NULL) {
                    $group_permission_data['group_id'] = $group_id[$i];
                    $this->db->insert('tbl_group_quiz_permission', $group_permission_data);
                }
            }

            /* DELETE GROUP FROM tbl_group_quiz_permission */
            for ($i = 0; $i < count($pre_group_id) - 1; $i++) {
                if (in_array($pre_group_id[$i], $group_id) == NULL) {
                    $this->db->where('group_id', $pre_group_id[$i]);
                    $this->db->where('quiz_id', $quiz_id);
                    $this->db->delete('tbl_group_quiz_permission');
                }
            }
        } else {
            /* DELETE ALL GROUP FROMPERMISSION GROUP QUIZ TABLE */
            for ($i = 0; $i < count($pre_group_id) - 1; $i++) {
                $this->db->where('group_id', $pre_group_id[$i]);
                $this->db->where('quiz_id', $quiz_id);
                $this->db->delete('tbl_group_quiz_permission');
            }
        }
    }

    public function update_url_by_id($udata, $url_id) {
        $this->db->where('url_id', $url_id);
        $this->db->update('tbl_url', $udata);
    }

    public function save_question_info() {
        $data = array();
        $data['topic_id'] = $this->input->post('topic_id', true);
        $topic_data = array();
        if ($data['topic_id'] == 'other') {
            $topic_data['topic_name'] = $this->input->post('other_topic', TRUE);
            $topic_data['topic_status'] = '1';
            $topic_data['topic_created_by'] = $this->session->userdata('user_id');
            $topic_data['topic_created_date_time'] = date('Y-m-d H:i:s');
            $this->db->insert('tbl_topic', $topic_data);
            $insert_id = $this->db->insert_id();
            $data['topic_id'] = $insert_id;
        }

        $data['question_type'] = $this->input->post('question_type', true);

        if ($data['question_type'] == 3) {

            $qdata['question_details'] = $this->input->post('question_details', true);
            $qdata['question_type'] = $this->input->post('question_type', true);
            $qdata['quiz_id'] = $this->input->post('quiz_id', true);
            $qdata['topic_id'] = $data['topic_id'];

            $qdata['points_per_answer'] = $this->input->post('points_per_answer', true);
            $qdata['time_limit'] = $this->input->post('time_limit', true);
            $qdata['question_status'] = 1;
//            $qdata['question_created_by'] = $this->session->userdata('user_id');
            $qdata['created_date_time'] = date('Y-m-d H:i:s');
            $qdata['answer'] = $this->input->post('desc_answer', true);
            $answer = $this->input->post('desc_answer', true);
            $qdata['desc_answer'] = (explode(" ", $answer));
            $this->session->set_userdata($qdata);


            redirect('f_end/select_question_answer_key');
        } elseif ($data['question_type'] == 4) {

            $fdata['question_details'] = $this->input->post('question_details', true);
            $fdata['question_type'] = $this->input->post('question_type', true);
            $fdata['quiz_id'] = $this->input->post('quiz_id', true);
            $fdata['topic_id'] = $data['topic_id'];

            $fdata['points_per_answer'] = $this->input->post('points_per_answer', true);
            $fdata['time_limit'] = $this->input->post('time_limit', true);
            $fdata['question_status'] = 1;
          
            $fdata['created_date_time'] = date('Y-m-d H:i:s');
            $fdata['question_detail_answer'] = $this->input->post('fill_answer', true);


            $this->db->insert('tbl_question', $fdata);
        } else {




            $data['question_details'] = $this->input->post('question_details', true);
//        $data['question_type'] = $this->input->post('question_type', true);
            $data['quiz_id'] = $this->input->post('quiz_id', true);

            $data['points_per_answer'] = $this->input->post('points_per_answer', true);
            $data['time_limit'] = $this->input->post('time_limit', true);
            $data['question_status'] = 1;
//            $data['question_created_by'] = $this->session->userdata('user_id');
            $data['created_date_time'] = date('Y-m-d H:i:s');
            $this->db->insert('tbl_question', $data);
            $insert_id = $this->db->insert_id();

            $odata = array();
            foreach ($_POST['option'] as $key => $v_contents) {
                $odata[$key]['question_id'] = $insert_id;
                $odata[$key]['question_option'] = $v_contents;
                if ($key == $_POST['status'][0]) {
//                $odata[$key]['status'] = $_POST['status'][0];
                    $odata[$key]['question_option_status'] = 1;
                } else {
                    $odata[$key]['question_option_status'] = 0;
                }
                $this->db->insert('tbl_question_option', $odata[$key]);
            }
        }
    }

    /*
     * author@ Monower
     * created date: 17-5-2015
     * for descriptive question save.
     */

    public function save_question_info_keyword() {

        $total_word = $this->session->userdata('total_word');
        $selected_words = count($_POST['key_answer']);
        $parcentage = ($selected_words * 100) / $total_word;

        if ($parcentage < 80) {

            $sdata = array();
            $sdata['message'] = 'You must select 80% words as keywords to continue!';
            $this->session->set_userdata($sdata);
            redirect('f_end/select_question_answer_key');
        } else {

            $data = array();
            $data['question_details'] = $this->session->userdata('question_details');
            $data['question_type'] = $this->session->userdata('question_type');
            $data['quiz_id'] = $this->session->userdata('quiz_id');
            $data['topic_id'] = $this->session->userdata('topic_id');

            $data['points_per_answer'] = $this->session->userdata('points_per_answer');
            $data['time_limit'] = $this->session->userdata('time_limit');
            $data['question_detail_answer'] = $this->session->userdata('answer');
            $data['question_status'] = 1;
//            $data['question_created_by'] = $this->session->userdata('user_id');
            $data['created_date_time'] = date('Y-m-d H:i:s');
            $this->db->insert('tbl_question', $data);
            $insert_id = $this->db->insert_id();

            $selected_ans = '';
            foreach ($_POST['key_answer'] as $v_ans) {
                $selected_ans = $selected_ans . $v_ans . ' ';
            }
            $odata = array();
            $odata['question_id'] = $insert_id;
            $odata['question_option'] = $selected_ans;
            $odata['question_option_status'] = 1;
            $this->db->insert('tbl_question_option', $odata);


            $this->session->unset_userdata('question_details');
            $this->session->unset_userdata('question_type');
            $this->session->unset_userdata('quiz_id');
            $this->session->unset_userdata('points_per_answer');
            $this->session->unset_userdata('time_limit');
            $this->session->unset_userdata('total_word');
            $this->session->unset_userdata('answer');
            $this->session->unset_userdata('desc_answer');

//        echo $selected_ans;
////        print_r($_POST);
//        exit();
        }
    }

    public function update_question_info_keyword() {

        $total_word = $this->session->userdata('total_word');
        $selected_words = count($_POST['key_answer']);
        $parcentage = ($selected_words * 100) / $total_word;

        if ($parcentage < 80) {

            $sdata = array();
            $sdata['message'] = 'You must select 80% words as keywords to continue!';
            $this->session->set_userdata($sdata);
            redirect('f_end/update_question_answer_key');
        } else {

            $data = array();
            $data['question_details'] = $this->session->userdata('question_details');
            $data['question_type'] = $this->session->userdata('question_type');
            $data['quiz_id'] = $this->session->userdata('quiz_id');
            $data['topic_id'] = $this->session->userdata('topic_id');
            $question_id = $this->session->userdata('question_id');

            $data['points_per_answer'] = $this->session->userdata('points_per_answer');
            $data['time_limit'] = $this->session->userdata('time_limit');
            $data['question_detail_answer'] = $this->session->userdata('answer');
            $data['question_status'] = 1;
//            $data['question_created_by'] = $this->session->userdata('user_id');
            $data['created_date_time'] = date('Y-m-d H:i:s');
            $this->db->where('question_id', $question_id);
            $this->db->update('tbl_question', $data);

            $selected_ans = '';
            foreach ($_POST['key_answer'] as $v_ans) {
                $selected_ans = $selected_ans . $v_ans . ' ';
            }
            $odata = array();
//        $odata['question_id'] = $insert_id;
            $odata['question_option'] = $selected_ans;
            $odata['question_option_status'] = 1;
            $this->db->where('question_id', $question_id);
            $this->db->update('tbl_question_option', $odata);


            $this->session->unset_userdata('question_details');
            $this->session->unset_userdata('question_type');
            $this->session->unset_userdata('quiz_id');
            $this->session->unset_userdata('points_per_answer');
            $this->session->unset_userdata('time_limit');

//        echo $selected_ans;
////        print_r($_POST);
//        exit();
        }
    }

    public function select_answer_words($question_id) {

        $this->db->select('question_option');
        $this->db->from('tbl_question_option');
        $this->db->where('question_id', $question_id);
        $query_result = $this->db->get();
        $result = $query_result->row();

        $word_count = str_word_count($result->question_option);

        return $word_count;
    }

    public function select_fil_answer_words($question_id) {

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

//        $word_count = str_word_count($result->question_option);

        return $result;
    }

    public function update_question_info($question_id) {
        $data = array();
        $data['topic_id'] = $this->input->post('topic_id', true);
        $topic_data = array();
        if ($data['topic_id'] == 'other') {
            $topic_data['topic_name'] = $this->input->post('other_topic', TRUE);
            $topic_data['topic_status'] = '1';
            $topic_data['created_by'] = $this->session->userdata('user_id');
            $this->db->insert('tbl_topic', $topic_data);
            $insert_id = $this->db->insert_id();
            $data['topic_id'] = $insert_id;
        }

        $data['question_type'] = $this->input->post('question_type', true);

        if ($data['question_type'] == 3) {

            $qdata['question_details'] = $this->input->post('question_details', true);
            $qdata['question_type'] = $this->input->post('question_type', true);
            $qdata['quiz_id'] = $this->input->post('quiz_id', true);
            $qdata['topic_id'] = $data['topic_id'];
            $qdata['question_id'] = $question_id;

            $qdata['points_per_answer'] = $this->input->post('points_per_answer', true);
            $qdata['time_limit'] = $this->input->post('time_limit', true);
            $qdata['question_status'] = 1;
//            $qdata['question_created_by'] = $this->session->userdata('user_id');
            $qdata['created_date_time'] = date('Y-m-d H:i:s');
            $qdata['answer'] = $this->input->post('desc_answer', true);
            $answer = $this->input->post('desc_answer', true);
            $qdata['desc_answer'] = (explode(" ", $answer));
            $this->session->set_userdata($qdata);


            redirect('f_end/update_question_answer_key');
        } elseif ($data['question_type'] == 4) {

            $fdata['question_details'] = $this->input->post('question_details', true);
            $fdata['question_type'] = $this->input->post('question_type', true);
            $fdata['quiz_id'] = $this->input->post('quiz_id', true);
            $fdata['topic_id'] = $data['topic_id'];

            $fdata['points_per_answer'] = $this->input->post('points_per_answer', true);
            $fdata['time_limit'] = $this->input->post('time_limit', true);
            $fdata['question_status'] = 1;
//            $fdata['question_created_by'] = $this->session->userdata('user_id');
            $fdata['created_date_time'] = date('Y-m-d H:i:s');
            $fdata['question_detail_answer'] = $this->input->post('fill_answer', true);


            $this->db->where('question_id', $question_id);
            $this->db->update('tbl_question', $fdata);
        } else {

            $data['question_details'] = $this->input->post('question_details', true);
            $data['question_type'] = $this->input->post('question_type', true);
            $data['quiz_id'] = $this->input->post('quiz_id', true);
//        $data['topic_id'] = $this->input->post('topic_id', true);
            $data['points_per_answer'] = $this->input->post('points_per_answer', true);
            $data['time_limit'] = $this->input->post('time_limit', true);
            $data['question_status'] = 1;
//            $data['question_created_by'] = $this->session->userdata('user_id');

            $this->db->where('question_id', $question_id);
            $this->db->update('tbl_question', $data);




            $odata = array();

            foreach ($_POST['option'] as $key => $v_contents) {
//            $odata[$key]['question_id'] = $insert_id;
                $question_option_id = $_POST['option_id'][$key];
                $odata[$key]['question_option'] = $v_contents;
                if ($key == $_POST['status'][0]) {
//                $odata[$key]['status'] = $_POST['status'][0];
                    $odata[$key]['question_option_status'] = 1;
                } else {
                    $odata[$key]['question_option_status'] = 0;
                }

                $this->db->where('question_option_id', $question_option_id);

                $this->db->update('tbl_question_option', $odata[$key]);
            }
        }
    }

    public function select_quiz($start, $limit, $status, $short = NULL) {
        $user_id = $this->session->userdata('user_id');

        $sql = "SELECT q.*,t.topic_name , p.person_last_name FROM tbl_quiz as q ,tbl_topic as t,tbl_person as p "
                . "WHERE q.quiz_topics_specification=t.topic_id AND q.quiz_created_by=p.person_id AND q.quiz_created_by=$user_id";

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

    public function select_all_quiz() {
        $user_id = $this->session->userdata('user_id');
        $this->db->select('*');
        $this->db->from('tbl_quiz');
        $this->db->where('person_id', $user_id);
        $query_result = $this->db->get();
        $result = $query_result->result();

        return $result;
    }

    public function select_tt_pending() {

        $user_id = $this->session->userdata('user_id');
        $this->db->select('*');
        $this->db->from('tbl_person_link');
        $this->db->where('person_id', $user_id);
        $this->db->where('add_link_request_status', 3);
        $query_result = $this->db->get();
        $result = $query_result->result();

        return $result;
    }

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

        $sql = "SELECT gm.* , g.group_id,g.group_created_by ,g.group_name,p.person_first_name,p.person_last_name,q.quiz_name FROM tbl_group_member as gm, tbl_person as p, tbl_group as g, tbl_quiz as q "
                . "WHERE gm.request_by_quiz = 3  AND gm.group_id = g.group_id AND g.group_created_by= $user_id AND p.person_id=gm.person_id AND gm.quiz_id=q.quiz_id GROUP BY g.group_created_by";
        $query_result = $this->db->query($sql);
        $result = $query_result->result();
//         echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

    public function active_selected_quiz($quiz_id) {
        $this->db->set('quiz_status', 1);
        $this->db->where('quiz_id', $quiz_id);
        $this->db->update('tbl_quiz');
    }

    public function inactive_selected_quiz($quiz_id) {
        $this->db->set('quiz_status', 0);
        $this->db->where('quiz_id', $quiz_id);
        $this->db->update('tbl_quiz');
    }

    public function archive_selected_quiz($quiz_id) {

        $this->db->set('quiz_status', 2);
        $this->db->where('quiz_id', $quiz_id);
        $this->db->update('tbl_quiz');
    }

    public function select_quiz_by_id($quiz_id) {

        $sql = "SELECT q.*,u.url,u.url_id FROM tbl_quiz as q, tbl_url as u WHERE q.quiz_id=$quiz_id AND q.url_id=u.url_id";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_question($start, $limit, $status, $short = NULL) {
        $user_id = $this->session->userdata('user_id');

        $sql = "SELECT q.*,t.topic_name  FROM tbl_question as q ,tbl_topic as t "
                . "WHERE  q.topic_id=t.topic_id";

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

    public function active_selected_question($question_id) {
        $this->db->set('question_status', 1);
        $this->db->where('question_id', $question_id);
        $this->db->update('tbl_question');
    }

    public function inactive_selected_question($question_id) {
        $this->db->set('question_status', 0);
        $this->db->where('question_id', $question_id);
        $this->db->update('tbl_question');
    }

    public function archive_selected_question($question_id) {

        $this->db->set('question_status', 2);
        $this->db->where('question_id', $question_id);
        $this->db->update('tbl_question');
    }

    public function select_all_course() {

        $this->db->select('*');
        $this->db->from('tbl_course_category');

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

        return $result;
    }

    public function select_all_topic() {
        $this->db->select('*');
        $this->db->from('tbl_topic');


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

        return $result;
    }

    public function select_all_group() {
        $user_id = $this->session->userdata('user_id');
        $this->db->select('*');
        $this->db->from('tbl_group');
        $this->db->where('group_created_by', $user_id);
        $query_result = $this->db->get();
        $result = $query_result->result();

//         echo '<pre>';
//         echo $user_id;
//         echo '<br/>';
//        print_r($result);
//        exit();

        return $result;
    }

    public function select_quiz_group_permission_by_quiz_id($quiz_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_group_quiz_permission as gqp, "
                . "     tbl_group as g "
                . " WHERE "
                . "         gqp.quiz_id = $quiz_id "
                . "     AND g.group_id = gqp.group_id ";

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

    public function selsect_all_group_member_pending() {
        $user_id = $this->session->userdata('user_id');
        $this->db->select('*');
        $this->db->from('tbl_group');
        $this->db->where('group_created_by', $user_id);
        $this->db->where('group_status', 1);
        $query_result = $this->db->get();
        $result = $query_result->result();
        $total = array();
        foreach ($result as $v_result) {
            $total[] = $this->select_all_pending_group_request($v_result->group_id);
        }

        $sumArray = array();

        foreach ($total as $key => $v_total) {
            foreach ($v_total as $key => $value) {
//    $sumArray[$key]+=$value;

                array_key_exists($key, $sumArray) ? $sumArray[$key] += $value : $sumArray[$key] = $value;
            }
        }




        return $sumArray;
    }

    public function select_all_pending_group_request($group_id) {


//        $this->db->select('*');
//        $this->db->from('tbl_group_member');
//        $this->db->where('group_id',$group_id);
//        $this->db->where('group_member_status',3);
// 
//        $query_result = $this->db->get();
//        $result = $query_result->result();
//        
//         


        $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_question_by_id($question_id) {

        $this->db->select('*');
        $this->db->from('tbl_question_option');
        $this->db->where('question_id', $question_id);
        $query_result = $this->db->get();
        $result = $query_result->result();
        return $result;
    }

    public function select_question_info_by_id($question_id) {

        $this->db->select('*');
        $this->db->from('tbl_question');
        $this->db->where('question_id', $question_id);
        $query_result = $this->db->get();
        $result = $query_result->row();
        return $result;
    }

    public function select_all_question_info($quiz_id) {

        $this->db->select('*');
        $this->db->from('tbl_question');
        $this->db->where('quiz_id', $quiz_id);

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

    public function select_all_question() {

        $user_id = $this->session->userdata('user_id');
        $this->db->select('*');
        $this->db->from('tbl_question');
        $this->db->where('question_created_by', $user_id);
        $query_result = $this->db->get();
        $result = $query_result->result();

        return $result;
    }

    public function select_all_active_question_info($quiz_id) {

        $this->db->select('*');
        $this->db->from('tbl_question');
        $this->db->where('question_status', 1);
        $this->db->where('quiz_id', $quiz_id);

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

    public function select_all_marks_active_question_info($quiz_id) {

        $sql = "SELECT  SUM(points_per_answer) AS total FROM tbl_question  WHERE  question_status=1 AND quiz_id=$quiz_id";
        $query_result = $this->db->query($sql);
        $result = $query_result->row();

        return $result;
    }

    public function select_option_info_by_id($question_id) {

        $this->db->select('*');
        $this->db->from('tbl_question_option');
        $this->db->where('question_id', $question_id);
        $query_result = $this->db->get();
        $result = $query_result->result();
        return $result;
    }

    /*
     * calculate the answer right or wrong.
     */

    public function confirm_answer($value) {
        $this->db->select('*');
        $this->db->from('tbl_question_option');
        $this->db->where('question_option_id', $value);
        $this->db->where('question_option_status', 1);
        $query_result = $this->db->get();
        $result = $query_result->row();
        return $result;
    }

    /*
     * end calculation.
     */

    public function select_all_group_id($user_id) {

        $sql = "SELECT group_id FROM tbl_group_member WHERE person_id=$user_id ";
        $query_result = $this->db->query($sql);
        $result = $query_result->result();

        return $result;
    }

    public function select_quiz_info_by_id($group_id) {

//        echo $group_id;
//        exit();


        $sql = "SELECT  q.*,c.course_category_name ,t.topic_name , p.person_last_name,g.group_name "
                . "FROM tbl_quiz as q ,tbl_course_category as c,tbl_topic as t,tbl_person as p ,tbl_group as g "
                . "WHERE q.quiz_category_name=c.course_category_id AND q.quiz_topics_specification=t.topic_id AND q.quiz_created_by=p.person_id AND q.quiz_status=1";


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

        return $result;


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

    public function select_quiz_quantity_by_id($quiz_id) {

        $this->db->select('*');
        $this->db->from('tbl_question');
        $this->db->where('quiz_id', $quiz_id);
        $query_result = $this->db->get();
        $result = $query_result->result();
        return $result;
    }

    public function search_user_mail($text) {
        $sql = "SELECT * FROM tbl_person as p WHERE p.person_email LIKE '$text%' AND p.person_status = 1 ";
        $result = $this->db->query($sql)->result();


        return $result;
    }

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


        return $result;
    }

    public function check_mail_list_by_id($person_id) {
        $user_id = $this->session->userdata('user_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 = $query_result->row();


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

    public function check_tt_requst($user_id, $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 = $query_result->row();
        return $result;
    }

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

    public function select_person_info_by_id($person_id) {

        $this->db->select('*');
        $this->db->from('tbl_person');
        $this->db->where('person_id', $person_id);
        $this->db->where('person_status', 1);

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

    public function selected_person_info_by_code($url) {

        $sql = "SELECT * FROM tbl_url as u, tbl_person as p WHERE u.url = '$url' AND p.url_id = u.url_id";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function confirm_tt_link_request_by_id($person_link_id) {

        $date = date("Y/m/d H:i:s");
        $this->db->set('add_link_request_status', 1);
        $this->db->set('add_link_requested_date_time', $date);
        $this->db->where('person_link_id', $person_link_id);
        $this->db->update('tbl_person_link');
    }

    public function delete_tt_link_request_by_id($person_link_id) {

        $this->db->where('person_link_id', $person_link_id);
        $this->db->delete('tbl_person_link');
    }

    public function selected_quiz_details_info($quiz_id) {

        $sql = "SELECT q.*,u.url FROM tbl_quiz as q, tbl_url as u WHERE q.quiz_id=$quiz_id AND q.url_id=u.url_id";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function selected_quiz_details_info_by_code($code) {



        $sql = "SELECT q.*,u.url FROM tbl_quiz as q, tbl_url as u WHERE u.url='$code' AND u.url_id=q.url_id";
        $result = $this->db->query($sql)->row();
        return $result;








//        $this->db->select('*');
//        $this->db->from('tbl_quiz');
//        $this->db->where('quiz_url', $code);
//        $this->db->where('quiz_permission', 1);
//        $this->db->where('quiz_status', 1);
//        $query_result = $this->db->get();
//        $result = $query_result->row();
//        return $result;
    }

    public function check_quiz_public_code_id($code) {
        $this->db->select('*');
        $this->db->from('tbl_quiz');
        $this->db->where('quiz_public_id', $code);
        $this->db->where('quiz_status', 1);
        $query_result = $this->db->get();
        $result = $query_result->row();
        return $result;
    }

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

    public function save_group_member_information($data) {

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

    public function confirm_quiz_request($group_member_id) {
        $date = date('Y-m-d H:i:s');
        $user_id = $this->session->userdata('user_id');
        $this->db->set('group_member_status', 1);
        $this->db->set('request_by_quiz', NULL);
        $this->db->set('quiz_id', NULL);
        $this->db->set('group_join_date_time', $date);
        $this->db->set('member_approve_by', $user_id);
        $this->db->where('group_member_id', $group_member_id);
        $this->db->update('tbl_group_member');
    }

    public function delete_quiz_request_by_id($group_member_id) {

        $this->db->where('group_member_id', $group_member_id);
        $this->db->delete('tbl_group_member');
    }

    public function check_quiz_join_code_id($code) {

        $this->db->select('*');
        $this->db->from('tbl_quiz');
        $this->db->where('quiz_public_id', $code);
        $this->db->where('quiz_status', 1);
        $query_result = $this->db->get();
        $result = $query_result->row();
        return $result;
    }

    public function check_url_code_id($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;
    }

    /*
     * @author : monower
     * @model name: update_quiz_permission_by_id
     * @parameter : $quiz_result_id , $premission
     * @ind. parameter: $quiz_result_id for quiz_result_id , $premission for quiz_result_permission
     * @return type: no
     * @created at: 2.5.14
     * @updated at: 
     * 
     */

    public function update_quiz_permission_by_id($quiz_result_id, $premission) {

        $this->db->set('quiz_result_permission', $premission);
        $this->db->where('quiz_result_id', $quiz_result_id);
        $this->db->update('tbl_quiz_result');
    }

    public function select_all_member_group() {
        $person_id = $this->session->userdata('user_id');

        $sql = "SELECT *  FROM tbl_group as g,tbl_group_member as gm WHERE gm.person_id=$person_id AND g.group_id=gm.group_id AND g.group_created_by!=$person_id AND gm.group_member_status=1";
        $result = $this->db->query($sql)->result();

        return $result;
    }

    /*
     * author@ MONOWER JAHAN
     * Function@ match_answer_word
     * Create date@ 17-5-15
     * for matching the student answer with taecher answer
     * 
     */

    public function match_answer_word($value) {
        $sql = "SELECT question_option FROM tbl_question_option WHERE question_option LIKE '% $value %' ";
        $result = $this->db->query($sql)->row();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

    public function match_fil_answer_word($value) {
        $sql = "SELECT question_option FROM tbl_question_option WHERE descriptive_ans_keyword LIKE '%" . $value . "%' ";
        $result = $this->db->query($sql)->row();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

    /*
     * author@ MONOWER JAHAN
     * Function@ check_url_type
     * Create date@ 21-5-15
     * for check url type.
     * 
     */

//
//    public function check_url_type($code) {
////        $this->db->select('*');
////        $this->db->from('tbl_url');
////        $this->db->where('url', $code);
////        $this->db->where('url_status', 1);
////        $query_result = $this->db->get();
////        $result = $query_result->row();
////        return $result;
//
//        $this->db->select('*');
//        $this->db->from('tbl_quiz');
//        $this->db->where('quiz_url', $code);
//        $query_result = $this->db->get();
//        $result = $query_result->row();
//        if ($result) {
//            return 1;
//        }
//
//        $this->db->select('*');
//        $this->db->from('tbl_group');
//        $this->db->where('group_public_id', $code);
//        $query_result = $this->db->get();
//        $result = $query_result->row();
//        if ($result) {
//            return 2;
//        }
//
//
//        $this->db->select('*');
//        $this->db->from('tbl_course_category');
//        $this->db->where('course_category_url', $code);
//        $this->db->where('course_category_type', '1');
//        $query_result = $this->db->get();
//        $result = $query_result->row();
//        if ($result) {
//            return 3;
//        }
//
//        $this->db->select('*');
//        $this->db->from('tbl_course_category');
//        $this->db->where('course_category_url', $code);
//        $query_result = $this->db->get();
//        $result = $query_result->row();
//        if ($result) {
//            return 4;
//        }
//        $this->db->select('*');
//        $this->db->from('tbl_course');
//        $this->db->where('course_url', $code);
//        $query_result = $this->db->get();
//        $result = $query_result->row();
//        if ($result) {
//            return 5;
//        }
//        return 0;
//    }
//
//    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;
//    }

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

    public function select_all_message($person_id) {

        $sql = "SELECT p.person_first_name, p.person_last_name, p.person_id, am.message_body FROM tbl_person as p, (SELECT msg.* FROM tbl_message as msg ,tbl_person as p 
WHERE msg.receiver_id = $person_id OR  msg.sender_id = $person_id group BY msg.message_id)  as am WHERE (am.receiver_id = p.person_id AND am.receiver_id != $person_id) OR (am.sender_id = p.person_id AND am.sender_id != $person_id)  GROUP BY p.person_id";
        $result = $this->db->query($sql)->result();
        return $result;


//        $this->db->select('*');
//        $this->db->from('tbl_message as msg');
//        $this->db->where('msg.receiver_id', $person_id);
////        $this->db->or_where('msg.sender_id', $person_id);
//        $this->db->join('tbl_person as p', 'p.person_id = msg.sender_id');
//        $this->db->group_by("sender_id", $person_id);
//
//        $query_result = $this->db->get();
//
//        $result = $query_result->result();
//
//        return $result;
    }

    public function select_user_all_message($person_id, $sender_id) {

        $this->db->select('*');
        $this->db->from('tbl_message as msg');
        $this->db->where('msg.receiver_id', $person_id);
        $this->db->where('msg.sender_id', $sender_id);
        $this->db->or_where('msg.receiver_id', $sender_id);
        $this->db->join('tbl_person as p', 'p.person_id = msg.sender_id');
        $query_result = $this->db->get();
        $result = $query_result->result();

        return $result;
    }

    public function selct_user_reply_message($person_id, $sender_id) {

        $this->db->select('*');
        $this->db->from('tbl_message as msg');
        $this->db->where('msg.sender_id', $person_id);
        $this->db->where('msg.receiver_id', $sender_id);
        $this->db->join('tbl_person as p', 'p.person_id = msg.sender_id');
        $query_result = $this->db->get();
        $result = $query_result->result();
// echo '<pre>';
// print_r($result);
// exit();
        return $result;
    }

    public function select_all_sent_message($person_id) {

        $this->db->select('*');
        $this->db->from('tbl_message as msg');
        $this->db->where('msg.sender_id', $person_id);
        $this->db->join('tbl_person as p', 'p.person_id = msg.receiver_id');
        $query_result = $this->db->get();
        $result = $query_result->result();
// echo '<pre>';
// print_r($result);
// exit();
        return $result;
    }

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

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

    public function update_quiz_taken_number($quiz_id) {
        $sql = "UPDATE tbl_quiz SET quiz_taken_number=quiz_taken_number+1 WHERE quiz_id=$quiz_id";
        $result = $this->db->query($sql);
        return;
    }

    public function match_fill_answer_word($answer, $question_id) {

        $this->db->select('*');
        $this->db->from('tbl_question');
        $this->db->where('question_id', $question_id);
        $this->db->where('question_detail_answer', $answer);
        $query_result = $this->db->get();
        $result = $query_result->row();
        return $result;
    }

    public function select_quiz_info($quiz_id) {

        $this->db->select('*');
        $this->db->from('tbl_quiz');
        $this->db->where('quiz_id', $quiz_id);
        $query_result = $this->db->get();
        $result = $query_result->row();
        return $result;
    }

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

//   
    /*
     * author@  suddam Hossain
     * Function@ organization_info_by_id
     * Create date@ 13-13-15
     * for check organization.
     * 
     */
    public function organization_info_by_id($person_id) {
        $this->db->select('*');
        $this->db->from('tbl_organization');
        $this->db->where('person_id', $person_id);
        $query_result = $this->db->get();
        $result = $query_result->row();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

    public function update_organization($organization_name, $organization_code, $organization_id) {


        $this->db->set('organization_name', $organization_name);
        $this->db->set('organization_code', $organization_code);
        $this->db->where('organization_id', $organization_id);
        $this->db->update('tbl_organization');
    }

    public function save_payment_info($pdata, $organization_id) {
        $this->db->insert('tbl_payment', $pdata);
        $payment_id = $this->db->insert_id();
        $date = date('Y-m-d');
        $data = array();
        $data['payment_id'] = $payment_id;
        $data['payment_status'] = 3;
        $data['paid_on'] = $date;
//        $data['paid_on']= $payment_id;
//        $data['paid_amount'] = $this->input->post('total_p', TRUE);
        $this->db->insert('tbl_payment_details_information', $data);
        $payment_details_information_id = $this->db->insert_id();


        $payment_id = $payment_id;
        $payment_details_information_id = $payment_details_information_id;
        $number_of_student_person = $this->input->post('attendees_number', TRUE);

        $this->db->set('payment_id', $payment_id);
        $this->db->set('payment_details_information_id', $payment_details_information_id);
        $this->db->set('number_of_student_person', $number_of_student_person);
        $this->db->where('organization_id', $organization_id);
        $this->db->update('tbl_organization');

        $this->db->select('*');
        $this->db->from('tbl_payment as p');
        $this->db->where('p.payment_id', $payment_id);
        $this->db->join('tbl_person as pi', 'pi.person_id = p.person_id');
        $this->db->join('tbl_payment_details_information as pd', 'pd.payment_id = p.payment_id');
        $query_result = $this->db->get();
        $result = $query_result->row();

        return $result;
    }

    public function save_online_exam_payment_info($pdata) {
        $this->db->insert('tbl_payment', $pdata);
        $payment_id = $this->db->insert_id();
        $date = date('Y-m-d');
        $data = array();
        $data['payment_id'] = $payment_id;
        $data['payment_status'] = 3;
        $data['paid_on'] = $date;
//        $data['paid_on']= $payment_id;
//        $data['paid_amount'] = $this->input->post('total_p', TRUE);
        $this->db->insert('tbl_payment_details_information', $data);

        $this->db->select('*');
        $this->db->from('tbl_payment as p');
        $this->db->where('p.payment_id', $payment_id);
        $this->db->join('tbl_person as pi', 'pi.person_id = p.person_id');
        $this->db->join('tbl_payment_details_information as pd', 'pd.payment_id = p.payment_id');
        $query_result = $this->db->get();
        $result = $query_result->row();

        return $result;
    }

    public function select_all_map_info() {

        $this->db->select('*');
        $this->db->from('tbl_map');

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

        return $result;
    }

    public function select_tt_link_by_requested_id() {

        $user_id = $this->session->userdata('user_id');
        $this->db->select('person_id');
        $this->db->from('tbl_person_link');
        $this->db->where('requested_person_id', $user_id);
        $this->db->where('add_link_request_status', 1);

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

    public function select_tt_link_by_person_id() {

        $user_id = $this->session->userdata('user_id');
        $this->db->select('requested_person_id as person_id');
        $this->db->from('tbl_person_link');
        $this->db->where('person_id', $user_id);
        $this->db->where('add_link_request_status', 1);

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

    public function select_map_info_by_person_id($person_id) {

        $sql = "SELECT "
                . "     ge.*,p.person_first_name,p.person_last_name "
                . " FROM "
                . "     tbl_person as p, "
                . "     tbl_geo_information as ge "
                . " WHERE "
                . "         p.person_id=$person_id"
                . "     AND ge.country_name LIKE  p.person_country "
                . "     AND ge.city_name LIKE   p.person_city";

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

    public function select_travel_info_by_person_id_id($person_id) {

        $sql = "SELECT * "
                . " FROM "
                . "     tbl_geo_information as gi, "
                . "     tbl_travel as t,"
                . "     tbl_person as p "
                . " WHERE "
                . "         t.person_id = $person_id "
                . "     AND t.location_id = gi.geo_id"
                . "     AND p.person_id = t.person_id "
                . " GROUP BY t.travel_id "
                . " ORDER BY gi.country_name ASC"
        ;
//        $sql = "SELECT *"
//                . " FROM "
//                . "     tbl_geo_information as ge, "
//                . "     tbl_travel as t,"
//                . "     tbl_person as p "
//                . " WHERE "
//                . "         t.person_id = $person_id "
//                . "     AND ge.country_name LIKE  t.travel_country_code "
//                . "     AND ge.city_name LIKE   t.travel_city "
//                . "     AND p.person_id = t.person_id "
//                . " GROUP BY t.travel_id "
//        ;
        $result = $this->db->query($sql)->result();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

    public function select_travel_country_by_person_id_($person_id) {

        $sql = "SELECT DISTINCT gi.country_name "
                . " FROM "
                . "     tbl_geo_information as gi, "
                . "     tbl_travel as t,"
                . "     tbl_person as p "
                . " WHERE "
                . "         t.person_id = $person_id "
                . "     AND t.location_id = gi.geo_id"
                . "     AND p.person_id = t.person_id "
                . " GROUP BY t.travel_id "
                . " ORDER BY gi.country_name ASC"
        ;
        $result = $this->db->query($sql)->result();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

    public function select_person_current_location($person_id) {       
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_person as p, "
                . "     tbl_geo_information as ge "
                . " WHERE "
                . "         p.person_id=$person_id "
                . "     AND ge.geo_country_name LIKE  p.person_country "
                . "     AND ge.geo_city_name LIKE   p.person_city";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_current_location_by_person_id($person_id) {

        $sql = "SELECT * "
                . " FROM "
                . "     tbl_person as p "
                . " WHERE "
                . "         p.person_id=$person_id "
//                . "     AND ge.country_name LIKE  p.person_country "
//                . "     AND ge.city_name LIKE   p.person_city"
        ;
        $result = $this->db->query($sql)->row();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

    public function select_person_travel_location($person_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_travel as t "
                . " WHERE "
                . "         t.person_id=$person_id ";
        $result = $this->db->query($sql)->result();
//        echo '<pre>';
//        print_r($result);
//        exit();
        return $result;
    }

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

    public function delete_travel_location($travel_id) {
        $this->db->where('travel_id', $travel_id);
        $this->db->delete('tbl_travel');
    }

    public function select_person_travel_location_by_country_code($country_code, $person_id) {
        $sql = " SELECT * FROM tbl_travel as t WHERE t.travel_country_code = '$country_code' AND t.person_id = $person_id";

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

    public function select_country_city($country_code) {
        $sql = " SELECT * FROM tbl_geo_information as gi WHERE gi.country_name = '$country_code'";

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

}
