<?php

class Quiz_model extends CI_Model {
    
    /**
     * @Function            : change_quiz_result_status_model 
     * @Description         : Change the status of quiz result
     * @Developed By        : Md Mashqur Ul Alam
     * @Start DateTime      : 18-01-17
     * @Parameter           : $data[array]
     * @Return              : NULL    
     * @Status              : Available       
     * */
    public function change_quiz_result_status_model($data){
        $this->db->set('quiz_result_status', $data['status'])
                 ->where('quiz_result_id', $data['quiz_result_id'])
                 ->update('tbl_quiz_result');
    }
    
    
    /**
     * @Function            : delete_quiz_result_model 
     * @Description         : permanently deletes a quiz result
     * @Developed By        : Md Mashqur Ul Alam
     * @Start DateTime      : 19-01-17
     * @Parameter           : $result_id
     * @Return              : NULL    
     * @Status              : Available       
     * */
    public function delete_quiz_result_model($result_id){
        $this->db->where('quiz_result_id', $result_id);
        $this->db->delete('tbl_quiz_result');
    }

//put your code here
    public function admin_login_check($data) {
        $this->db->select('*');
        $this->db->from('tbl_person_information pi');
        $this->db->where('email', $data['user_email']);
        $this->db->where('password', md5($data['user_password']));
        $query_result = $this->db->get();
        $result = $query_result->row();
        return $result;
    }

//20-08-2016
    function person_info_by_person_idfor_quiz_topic($person_id) {
        $sql = " SELECT * FROM  tbl_person as p,  tbl_url as u "
                . " WHERE p.person_id = '$person_id'"
                . " AND p.url_id =u.url_id ";
        return $result = $this->db->query($sql)->row();
    }

    public function check_quiz_public_id($public_id) {
        $sql = "SELECT * FROM tbl_quiz as q WHERE q.quiz_public_id = '$public_id'";
        $result = $this->db->query($sql)->row();
        if ($result) {
            return 1;
        } else {
            return 2;
        }
    }

//05-09-2016
    function get_question_info($key) {
        $sql = "SELECT * FROM 
        tbl_question as qu,
        tbl_quiz as q,
        tbl_url as u
        WHERE qu.question_id = '$key'
        AND qu.quiz_id = q.quiz_id
        AND q.url_id = u.url_id";
        return $result = $this->db->query($sql)->row();
    }

    //tania 05-09-2016    
    function get_option_info($value) {
        $sql = " SELECT * FROM 
    tbl_question_option as qo,
    tbl_question as qu,
    tbl_quiz as q,
    tbl_url as u
    WHERE qo.question_option_id ='$value'
    AND qo.question_id = qu.question_id
    AND qu.quiz_id =q.quiz_id
    AND q.url_id = u.url_id";
        return $result = $this->db->query($sql)->row();
    }

    function question_option($value) {
        $sql = "SELECT * FROM 
    tbl_question_option as qo
    WHERE qo.question_option_id ='$value'";
        return $result = $this->db->query($sql)->row();
    }

    public function check_quiz_topic($topic_name) {
        $sql = "SELECT * FROM tbl_topic as t WHERE t.topic_name = '$topic_name'";
        $result = $this->db->query($sql)->row();

        if ($result) {
            return 1;
        } else {
            return 2;
        }
    }

    public function save_unique_url($url, $url_type, $url_status) {
        $url = preg_replace('/[^A-Za-z0-9 -]/', '', $url);
        $url = str_replace('.', '-', $url);
        $url = str_replace(' ', '-', $url);
        $url = $temp_url = strtolower($url);
        $i = 1;
        do {
            $sql = "SELECT * FROM tbl_url as u WHERE u.url = '$temp_url'";
            $result = $this->db->query($sql)->row();
            if ($result) {
                $temp_url = $url;
                $temp_url .=$i;
                $i++;
            }
        } while ($result);

        $url = $temp_url;
        $url_data['url'] = $url;
        $url_data['url_type'] = $url_type;
        $this->db->insert('tbl_url', $url_data);
        return $this->db->insert_id();
    }

    /*
     * Function         : save_quiz_info
     * Used IN          : 
     * Description      : 
     * Parameter        : none
     * return           : none
     * redirect         : none
     * Developed By     : Unknown
     * Update By        : Tania Akter
     * Date             : 16-07-2016
     */

    public function save_quiz_info() {
        $person_id = $this->session->userdata('user_id');
        $data = array();
        $data['quiz_name'] = $this->input->post('quiz_name', true);
        $data['quiz_topics_specification'] = $this->input->post('quiz_topics_specification', true);
        $url = $this->input->post('quiz_url', true);
        $data['url_id'] = $quiz_url_id = $this->url_model->create_quiz_url($url);
        $data['quiz_public_id'] = strtolower($this->input->post('quiz_public_id', true));
        $data['quiz_random_static'] = $this->input->post('quiz_random_static', true);
        $data['quiz_pulication_status'] = $quiz_pulication_status = $this->input->post('quiz_pulication_status', true);
        $data['quiz_created_by'] = $this->session->userdata('user_id');
        $data['quiz_created_date_time'] = date('Y-m-d H:i:s', time() - $this->session->userdata('time_offset'));
        $data['quiz_status'] = $this->input->post('quiz_status', true);

        $this->db->insert('tbl_quiz', $data);
        $quiz_id = $this->db->insert_id();
        $this->save_quiz_all_tinymce_information($person_id, $quiz_id, $quiz_url_id);   //used to save tinymce information
        return $quiz_id;
    }

    /* ----------------------------  start add tinymce information    --------------------------------- */
    /*
     * Function         : save_quiz_all_tinymce_information
     * Used IN          : 
     * Description      : 
     * Parameter        : none
     * return           : none
     * redirect         : none
     * Developed By     : Unknown
     * Update By        : Tania Akter
     * Date             : 16-07-2016
     */

    function save_quiz_all_tinymce_information($person_id, $quiz_id, $quiz_url_id) {
        $quiz_description = $this->input->post('quiz_description', FALSE);
        if ($quiz_description) {
            $quiz_description_update = $this->save_quiz_tinymce_information($person_id, $quiz_id, $quiz_url_id, $quiz_description);
            $data['quiz_description'] = $this->utility_model->changed_special_character_to_text($quiz_description_update);
        } else {
            $data['quiz_description'] = '';
        }
        $this->utility_model->update_information_in_database('tbl_quiz', 'quiz_id', $quiz_id, $data);
        $person_url = $this->person_model->get_profile_url($person_id);
        $directory = 'source/' . $person_url . '/';
        $this->utility_model->remove_directory_without_root($directory);
    }

    /*
     * Function         : save_quiz_tinymce_information
     * Used IN          : 
     * Description      : 
     * Parameter        : none
     * return           : none
     * redirect         : none
     * Developed By     : Unknown
     * Update By        : Tania Akter
     * Date             : 16-07-2016
     */

    function save_quiz_tinymce_information($person_id, $quiz_id, $quiz_url_id, $quiz_description) {
        $person_url = $this->person_model->get_profile_url($person_id);
        $destination_path = 'content-library/' . $person_url . '/my-work-file';
        $quiz_url_info = $this->url_model->get_url_by_url_id($quiz_url_id, 4); //0=invalid_url_no_url;1=menu;2=group;3=course_category;4=quiz;5=course;8=video_course;10=Person_Public_Profile;11=classroom; 13=quiz_topic
        $new_directory = $destination_path . '/' . $quiz_url_info->url;
        return $this->utility_model->get_tinymce_description($quiz_id, $new_directory, $quiz_description, 4);
    }

// tania 29-08-2016
    function select_all_quiz_topic_wise_pagination($topic_id_array, $topic_id, $limit) {
        if (count($topic_id_array) == 1) {
            $sql = "SELECT * FROM tbl_quiz as q,tbl_url as u WHERE 
                    q.quiz_topics_specification = '$topic_id' 
                    AND q.quiz_status=1 AND  
                    u.url_type=4 AND q.url_id=u.url_id 
                    ORDER BY q.quiz_taken_number DESC LIMIT $limit, 10";
            $result = $this->db->query($sql)->result();
            return $result;
        } else if (count($topic_id_array) > 1) {
            $sql = "SELECT * FROM tbl_quiz as q,tbl_url as u 
                WHERE q.quiz_topics_specification IN ($topic_id) 
                AND q.quiz_status=1 
                AND q.url_id=u.url_id 
                AND url_type=4
                ORDER BY q.quiz_taken_number DESC LIMIT $limit, 10";
            $result = $this->db->query($sql)->result();
            return $result;
        }
    }

// tania 29-08-2016   
    function select_latest_quiz_topic_wise_pagination($topic_id_array, $topic_id, $limit) {
        if (count($topic_id_array) == 1) {
            $sql = "SELECT * FROM tbl_quiz as q,tbl_url as u WHERE "
                    . " q.quiz_topics_specification = '$topic_id' "
                    . " AND q.quiz_status=1 AND  u.url_type=4 AND q.url_id=u.url_id "
                    . "  ORDER BY q.quiz_id DESC LIMIT $limit, 10";
            $result = $this->db->query($sql)->result();
            return $result;
        } else if (count($topic_id_array) > 1) {
            $sql = "SELECT * FROM tbl_quiz as q,tbl_url as u "
                    . "  WHERE  q.quiz_topics_specification IN ($topic_id) "
                    . "  AND q.quiz_status=1 AND q.url_id=u.url_id AND  url_type=4"
                    . "   ORDER BY q.quiz_id DESC LIMIT $limit, 10";
            $result = $this->db->query($sql)->result();
            return $result;
        }
    }

    /* ----------------------------   end add tinymce information    --------------------------------- */

//tania 27-07-2016
    public function update_quiz_info($data) {
        $quiz_id = $data['quiz_info']->quiz_id;
        $person_url = $this->person_model->get_profile_url($data['quiz_info']->quiz_created_by);

        if ($this->input->post('quiz_description', FALSE)) {
            $quiz_description_update = $this->utility_model->update_tinymce_information($quiz_id, $person_url, $data['quiz_url'], $this->input->post('quiz_description', FALSE), $data['quiz_info']->quiz_description);
            $qdata['quiz_description'] = $this->utility_model->changed_special_character_to_text($quiz_description_update);
        }

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

        $url_id = $this->input->post('url_id', true);
        $url = strtolower($this->input->post('quiz_url', true));
        $this->url_model->update_quiz_url($url_id, $url);

        $qdata['quiz_public_id'] = strtolower($this->input->post('quiz_public_id', true));
        $qdata['quiz_random_static'] = $quiz_random_static = $this->input->post('quiz_random_static', true);
        $qdata['quiz_pulication_status'] = $quiz_pulication_status = $this->input->post('quiz_pulication_status', true);
        $qdata['quiz_created_by'] = $this->session->userdata('user_id');
        $qdata['quiz_updated_date_time'] = date('Y-m-d H:i:s', time() - $this->session->userdata('time_offset'));
        $qdata['quiz_status'] = $this->input->post('quiz_status', true);

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

    /* ---------------------------------------- edit tinymec ------------------------------------- */

    public function select_quiz_group_permission($quiz_public_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_quiz as q, "
                . "     tbl_group_quiz_permission as gqp "
                . " WHERE "
                . "         q.quiz_public_id = '$quiz_public_id' "
                . "     AND q.quiz_id = gqp.quiz_id ";

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

    public function select_quiz_course_permission_by_course_id($course_id) {
//        $sql = "SELECT * "
//                . " FROM "
//                . "     tbl_course_quiz_permission as cqp "
//                . " WHERE "
//                . "         cqp.course_id = '$course_id' ";
//
//        $result = $this->db->query($sql)->row();
//        return $result;
    }

    public function select_quiz_video_course_permission_by_course_id($video_management_id) {
//        $sql = "SELECT * "
//                . " FROM "
//                . "     tbl_video_course_quiz_permission as vcqp "
//                . " WHERE "
//                . "         vcqp.video_management_id = '$video_management_id' ";
//
//        $result = $this->db->query($sql)->row();
//        return $result;
    }

    public function select_quiz_course_permission($quiz_public_id) {
//        $sql = "SELECT * "
//                . " FROM "
//                . "     tbl_quiz as q, "
//                . "     tbl_course_quiz_permission as cqp "
//                . " WHERE "
//                . "         q.quiz_public_id = '$quiz_public_id' "
//                . "     AND q.quiz_id = cqp.quiz_id ";
//
//        $result = $this->db->query($sql)->row();
//        return $result;
    }

    public function select_quiz_video_course_permission($quiz_public_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_quiz as q, "
                . "     tbl_video_course_quiz_permission as vcqp "
                . " WHERE "
                . "         q.quiz_public_id = '$quiz_public_id' "
                . "     AND q.quiz_id = vcqp.quiz_id ";

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

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

    public function select_video_course_quiz_permission($video_management_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_video_management as vm, "
                . "     tbl_video_course_quiz_permission as vcqp "
                . " WHERE "
                . "         vm.video_management_id = '$video_management_id' "
                . "     AND vm.video_management_id = vcqp.video_management_id ";

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

    public function save_question_info() {
        $data = array();
        $person_id = $this->session->userdata('user_id');
        $quiz_url = $this->input->post('quiz_id', true);
        $quiz_info = $this->select_quiz_by_url($quiz_url);
        $data['quiz_id'] = $quiz_id = $quiz_info->quiz_id;
        $data['points_per_answer'] = $this->input->post('points_per_answer');
        $data['time_limit'] = $this->input->post('time_limit', true);
        $data['question_type'] = $question_type = $this->input->post('question_type', true);
        $data['question_status'] = 1;
        $data['question_created_by'] = $this->session->userdata('user_id');
        $this->db->insert('tbl_question', $data);
        $insert_id = $question_id = $this->db->insert_id();
        $this->save_quiz_all_tinymce_information_add_question($person_id, $quiz_id, $quiz_info->url_id, $question_id);

        if ($question_type == 3) {
            $question = $this->input->post('question_details', true);
            $desc_answer = $this->input->post('desc_answer', true);
            $desc_answer = (explode(" ", $desc_answer));
            $words = array(".", "?", ":", ",", ";", "(", ")", "=", "+", "!");
            $desc_answer = str_replace($words, "", $desc_answer);
            $words = array("am", "is", "are", "was", "were", "has", "have", "had", "can", "could", "can", "may", "might", "shall", "will", "should", "would", "a", "an", "the", "of", "in", "to", "with", "as", "at", "for", "on", "by", "from", "not", "no");
            foreach ($desc_answer as $v_answer) {
                if (!in_array($v_answer, $words)) {
                    $desc_answer_keys[] = $v_answer;
                }
            }
            if ($desc_answer_keys) {
                $unique_ans_keys = array_unique($desc_answer_keys);
            } else {
                $unique_ans_keys = '';
            }
            $descript_ans_keys = (implode(", ", $unique_ans_keys));

            $odata = array();
            $odata['question_id'] = $insert_id;
            $odata['question_option'] = $this->input->post('desc_answer', true);
            $odata['descriptive_ans_keyword'] = $descript_ans_keys;
            $odata['question_option_status'] = 1;
            $this->db->insert('tbl_question_option', $odata);
        } else if ($question_type == 4) {
            $question_status = $this->input->post('status', TRUE);
            $datafill['question_id'] = $insert_id;
            $datafill['question_option_status'] = 1;
            $this->db->insert('tbl_question_option', $datafill);
            $question_option_id = $this->db->insert_id();
            $this->save_quiz_all_tinymce_information_question_option_fill_in_blanks($person_id, $quiz_info->url_id, $question_option_id);
        } else {
            $question_option = $this->input->post('option', FALSE);
            $question_status = $this->input->post('status', TRUE);

            for ($i = 0; $i < count($question_option); $i++) {
                $mcqdata['question_id'] = $question_id;
                if ($i == $question_status) {
                    $mcqdata['question_option_status'] = 1;
                } else {
                    $mcqdata['question_option_status'] = 0;
                }
                $this->db->insert('tbl_question_option', $mcqdata);
                $question_option_id = $this->db->insert_id();
                $this->save_quiz_all_tinymce_information_question_option($person_id, $quiz_info->url_id, $question_option_id, $question_option[$i]);
            }
            $this->remove_tinymec_directory($person_id);
        }
    }

    /* ------------------------------start  add tinymec question --------------------------------------- */

//tania 23-07-2016
    /*
     * Function         : 
     * Used IN          : 
     * Description      : 
     * Parameter        : 
     * return           : 
     * redirect         : 
     * Developed By     : Tania Akter
     * Date             : 
     */
    function save_quiz_all_tinymce_information_add_question($person_id, $quiz_id, $quiz_url_id, $question_id) {
        $question_details = $this->input->post('question_details', FALSE);
        if ($question_details) {
            $question_details_update = $this->save_quiz_tinymce_information_add_question($person_id, $quiz_id, $quiz_url_id, $question_details);
            $data['question_details'] = $this->utility_model->changed_special_character_to_text($question_details_update);
        } else {
            $data['question_details'] = '';
        }
        $this->utility_model->update_information_in_database('tbl_question', 'question_id', $question_id, $data);
    }

    /*
     * Function         : 
     * Used IN          : 
     * Description      : 
     * Parameter        : 
     * return           : 
     * redirect         : 
     * Developed By     : Tania Akter
     * Date             : 
     */

    function save_quiz_tinymce_information_add_question($person_id, $quiz_id, $quiz_url_id, $question_details) {
        $person_url = $this->person_model->get_profile_url($person_id);
        $destination_path = 'content-library/' . $person_url . '/my-work-file';
        $quiz_url_info = $this->url_model->get_url_by_url_id($quiz_url_id, 4); //0=invalid_url_no_url;1=menu;2=group;3=course_category;4=quiz;5=course;8=video_course;10=Person_Public_Profile;11=classroom; 13=quiz_topic
        $new_directory = $destination_path . '/' . $quiz_url_info->url;
        return $this->utility_model->get_tinymce_description($quiz_id, $new_directory, $question_details, 4);
    }

    /* ------------------------------------ end add tinymec question --------------------------------------------- */

    /* ------------------------------------ start question option tinymec ---------------------------------------------- */
    /*
     * Function         : 
     * Used IN          : 
     * Description      : 
     * Parameter        : 
     * return           : 
     * redirect         : 
     * Developed By     : Tania Akter
     * Date             : 
     */

    function save_quiz_all_tinymce_information_question_option($person_id, $quiz_url_id, $question_option_id, $question_option) {
        if ($question_option) {
            $question_option_update = $this->save_quiz_tinymce_information_question_option($person_id, $quiz_url_id, $question_option);
            $data['question_option'] = $this->utility_model->changed_special_character_to_text($question_option_update);
        } else {
            $data['question_option'] = '';
        }
        $this->utility_model->update_information_in_database('tbl_question_option', 'question_option_id', $question_option_id, $data);
    }

    /*
     * Function         : 
     * Used IN          : 
     * Description      : 
     * Parameter        : 
     * return           : 
     * redirect         : 
     * Developed By     : Tania Akter
     * Date             : 
     */

    function remove_tinymec_directory($person_id) {
        $person_url = $this->person_model->get_profile_url($person_id);
        $directory = 'source/' . $person_url . '/';
        $this->utility_model->remove_directory_without_root($directory);
    }

    /*
     * Function         : 
     * Used IN          : 
     * Description      : 
     * Parameter        : 
     * return           : 
     * redirect         : 
     * Developed By     : Tania Akter
     * Date             : 
     */

    function save_quiz_tinymce_information_question_option($person_id, $quiz_url_id, $question_option) {
        $person_url = $this->person_model->get_profile_url($person_id);
        $destination_path = 'content-library/' . $person_url . '/my-work-file';
        $quiz_url_info = $this->url_model->get_url_by_url_id($quiz_url_id, 4); //0=invalid_url_no_url;1=menu;2=group;3=course_category;4=quiz;5=course;8=video_course;10=Person_Public_Profile;11=classroom; 13=quiz_topic
        $new_directory = $destination_path . '/' . $quiz_url_info->url;
        return $this->utility_model->get_tinymce_description_question_option($person_id, $new_directory, $question_option, 4);
    }

    /* ----------------------------- end  question option tinymec ------------------------------------- */
    /* ------------------------ start question option fill in the blanks tinymec ------------------------------------------ */
    /*
     * Function         : 
     * Used IN          : 
     * Description      : 
     * Parameter        : 
     * return           : 
     * redirect         : 
     * Developed By     : Tania Akter
     * Date             : 
     */

    function save_quiz_all_tinymce_information_question_option_fill_in_blanks($person_id, $quiz_url_id, $question_option_id) {
        $question_option = $this->input->post('option', FALSE);
        if ($question_option) {
            $question_option_update = $this->save_quiz_tinymce_information_question_option($person_id, $quiz_url_id, $question_option);
            $data['question_option'] = $this->utility_model->changed_special_character_to_text($question_option_update);
        } else {
            $data['question_option'] = '';
        }
        $this->utility_model->update_information_in_database('tbl_question_option', 'question_option_id', $question_option_id, $data);
        $person_url = $this->person_model->get_profile_url($person_id);
        $directory = 'source/' . $person_url . '/';
        $this->utility_model->remove_directory_without_root($directory);
    }

    /* ----------------------------- end  question option fill in the blanks tinymec ------------------------------------- */

    public function select_question_options_by_question_id($question_id) {
//        $sql = "SELECT * "
//                . " FROM "
//                . "     tbl_question_option as qo"
//                . " WHERE "
//                . "         qo.question_id = $question_id "
//        ;
//        $result = $this->db->query($sql)->result();
//        return $result;
        
        $this->db->select('*')
                ->from('tbl_question_option')
                ->where('question_id', $question_id)
                ->order_by('question_option_id','asc');
        return $this->db->get()->result();
    }

    public function save_grade_info($quiz_id) {
        $data = array();
        $data['grade_total_number'] = $this->input->post('grade_total_number');
        $data['grade_name'] = $this->input->post('grade_name');
        $data['grade_number'] = $this->input->post('grade_number');

        for ($i = 0; $i < count($data['grade_name']); $i++) {
            $gdata['quiz_id'] = $quiz_id;
            $gdata['grade_total_number'] = $data['grade_total_number'];
            $gdata['grade_name'] = $data['grade_name'][$i];
            $gdata['grade_number'] = $data['grade_number'][$i];
            $gdata['grade_status'] = 1;
            $this->db->insert('tbl_grade', $gdata);
        }
    }

    public function select_quiz_grade($quiz_id) {
        $sql = "SELECT * FROM tbl_grade  WHERE quiz_id= $quiz_id  ";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_grade_by_id($grade_id) {
        $sql = "SELECT * FROM tbl_grade  WHERE grade_id= $grade_id  ";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function update_grade_info($quiz_id) {
        $data = array();
        $grade_id = $this->input->post('grade_id');
        $data['grade_total_number'] = $this->input->post('grade_total_number');
        $data['grade_name'] = $this->input->post('grade_name');
        $data['grade_number'] = $this->input->post('grade_number');

        for ($i = 0; $i < count($data['grade_name']); $i++) {
            $gdata = array();
            $gdata['quiz_id'] = $quiz_id;
            $gdata['grade_total_number'] = $data['grade_total_number'];
            $gdata['grade_name'] = $data['grade_name'][$i];
            $gdata['grade_number'] = $data['grade_number'][$i];
            $gdata['grade_status'] = 1;

            if ($grade_id[$i]) {
                $this->db->where('grade_id', $grade_id[$i]);
                $this->db->update('tbl_grade', $gdata);
            } else {
                $this->db->insert('tbl_grade', $gdata);
            }
        }
    }

    public function select_quiz_all_grade($quiz_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_quiz as q, tbl_grade as g"
                . " WHERE "
                . "         q.quiz_id = '$quiz_id' "
                . "     AND q.quiz_id = g.quiz_id "
                . " ORDER BY g.grade_number DESC"
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_quiz_active_grade($quiz_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_quiz as q, tbl_grade as g"
                . " WHERE "
                . "         q.quiz_id = '$quiz_id' "
                . "     AND q.quiz_id = g.quiz_id "
                . "     AND g.grade_status = 1"
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_quiz_inactive_grade($quiz_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_quiz as q, tbl_grade as g"
                . " WHERE "
                . "         q.quiz_id = '$quiz_id' "
                . "     AND q.quiz_id = g.quiz_id "
                . "     AND g.grade_status = 0"
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_quiz_archive_grade($quiz_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_quiz as q, tbl_grade as g"
                . " WHERE "
                . "         q.quiz_id = '$quiz_id' "
                . "     AND q.quiz_id = g.quiz_id "
                . "     AND g.grade_status = 2"
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function active_selected_grade($grade_id) {
        $this->db->set('grade_status', 1);
        $this->db->where('grade_id', $grade_id);
        $this->db->update('tbl_grade');
    }

    public function inactive_selected_grade($grade_id) {
        $this->db->set('grade_status', 0);
        $this->db->where('grade_id', $grade_id);
        $this->db->update('tbl_grade');
    }

    public function archive_selected_grade($grade_id) {
        $this->db->set('grade_status', 2);
        $this->db->where('grade_id', $grade_id);
        $this->db->update('tbl_grade');
    }

    public function save_certificate_information() {
        $cdata = array();
        $cdata['certificate_created_by'] = $this->session->userdata('user_id');
        $cdata['certificate_name'] = $this->input->post('certificate_name', TRUE);
        $cdata['certificate_public_id'] = str_replace(' ', '-', $cdata['certificate_name']);
        $cdata['certificate_share_status'] = $this->input->post('certificate_share_status', TRUE);
        $cdata['certificate_status'] = $this->input->post('certificate_status', TRUE);
        $cdata['certificate_public_id'] .= rand(111, 999);

        $certificate_description = $this->input->post('certificate_description');

        for ($i = 0; $i < 4; $i++) {
            if ($certificate_description[$i]) {
                $cdata['certificate_description'] = $certificate_description[$i];
            }
        }
        /*
         * Start Image Upload
         */
        if ($_FILES['company_image']['name']) {
            $config['upload_path'] = 'site/images/quiz/certificate/original/';
            $config['allowed_types'] = 'gif|jpg|png';
            $config['max_size'] = '300';
            $config['max_width'] = '2000';
            $config['max_height'] = '2000';
            $error = '';
            $fdata = array();
            $this->load->library('upload', $config);

            if (!$this->upload->do_upload('company_image')) {
                $error = $this->upload->display_errors();
                echo $error;
                exit();
            } else {
                $fdata = $this->upload->data();
                $cdata['certificate_image'] = $config['upload_path'] . $fdata['file_name'];
            }
        }
// END Create Thumbnail
        /*
         * End Image Ipload
         */
        $this->db->insert('tbl_certificate', $cdata);
        $certicate_id = $this->db->insert_id();

        return $certicate_id;
    }

    public function update_quiz_certificate($certificate_id) {
        $previous_quizes = $this->input->post('previous_quizes', TRUE);
        $previous_quizes = explode(',', $previous_quizes);

        $quiz_id = $this->input->post('to_address', TRUE);
        $quiz_id = explode(',', $quiz_id);

        for ($i = 0; $i < count($quiz_id); $i++) {
            if (!in_array($quiz_id[$i], $previous_quizes)) {

                $this->db->set('certificate_id', $certificate_id);
                $this->db->where('quiz_id', $quiz_id[$i]);
                $this->db->update('tbl_quiz');
            }
        }
        for ($i = 0; $i < count($previous_quizes); $i++) {
            if (!in_array($previous_quizes[$i], $quiz_id)) {
                $this->db->set('certificate_id', NULL);
                $this->db->where('quiz_id', $previous_quizes[$i]);
                $this->db->update('tbl_quiz');
                echo 'remove certificate';
            }
        }
        redirect('quizes/certificate');
    }

    public function update_certificate_information($certificate_id) {

        $cdata = array();
        $cdata['certificate_name'] = $this->input->post('certificate_name', TRUE);

        $certificate_description = $this->input->post('certificate_description');
        for ($i = 0; $i < 4; $i++) {
            if ($certificate_description[$i]) {
                $cdata['certificate_description'] = $certificate_description[$i];
            }
        }

        $cdata['certificate_share_status'] = $this->input->post('certificate_share_status', TRUE);
        $cdata['certificate_status'] = $this->input->post('certificate_status', TRUE);

        /*
         * Start Image Upload
         */
        if ($_FILES['company_new_image']['name']) {
            $config['upload_path'] = 'site/images/quiz/certificate/original/';
            $config['allowed_types'] = 'gif|jpg|png';
            $config['max_size'] = '300';
            $config['max_width'] = '2000';
            $config['max_height'] = '2000';
            $error = '';
            $fdata = array();
            $this->load->library('upload', $config);

            if (!$this->upload->do_upload('company_new_image')) {
                $error = $this->upload->display_errors();
                echo $error;
                exit();
            } else {
                $fdata = $this->upload->data();
                $cdata['certificate_image'] = $config['upload_path'] . $fdata['file_name'];
            }
        }
// END Image Upload
        /*
         * End Image Ipload
         */

        $this->db->where('certificate_id', $certificate_id);
        $this->db->update('tbl_certificate', $cdata);
        $certicate_id = $this->db->insert_id();

        return $certicate_id;
    }

    public function select_quiz_certificate($quiz_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_quiz as q, tbl_certificate as c"
                . " WHERE "
                . "         q.quiz_id = $quiz_id "
                . "     AND c.quiz_id = q.quiz_id "
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

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

    public function select_all_quiz_by_certificate($certificate_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_certificate as c, "
                . "     tbl_quiz q "
                . " WHERE "
                . "         c.certificate_id = $certificate_id "
                . "     AND c.certificate_id = q.certificate_id "
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_certificate_by_id($certificate_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_certificate"
                . " WHERE "
                . "         certificate_id = $certificate_id"
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_active_certificate_by_public_id($certicate_public_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_certificate"
                . " WHERE "
                . "         certificate_public_id = '$certicate_public_id'"
                . "     AND certificate_status = 1"
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_certificate_by_public_id($certicate_public_id) {
        $person_id = $this->session->userdata('user_id');
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_certificate"
                . " WHERE "
                . "             certificate_created_by = $person_id"
                . "         AND certificate_public_id = '$certicate_public_id'"
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function active_selected_certificate($certificate_id) {
        $this->db->set('certificate_status', 1);
        $this->db->where('certificate_id', $certificate_id);
        $this->db->update('tbl_certificate');
    }

    public function inactive_selected_certificate($certificate_id) {
        $this->db->set('certificate_status', 0);
        $this->db->where('certificate_id', $certificate_id);
        $this->db->update('tbl_certificate');
    }

    public function archive_selected_certificate($certificate_id) {
        $this->db->set('certificate_status', 2);
        $this->db->where('certificate_id', $certificate_id);
        $this->db->update('tbl_certificate');
    }

    public function update_question_info($question_id, $question_details) {
        $data = array();
        $quiz_url = $this->input->post('quiz_id', true);
        $quiz_info = $this->select_quiz_by_url($quiz_url);
        $first_question = $this->select_quiz_first_question($quiz_info->quiz_id);

        $data['quiz_id'] = $quiz_id = $quiz_info->quiz_id;
        $quiz_creator_info = $this->super_admin_quiz_model->get_quiz_creator_info_by_quiz_id($quiz_id);

        if ($this->input->post('question_details', FALSE)) {  //tania
            $question_details_update = $this->utility_model->update_tinymce_information($quiz_id, $quiz_creator_info->url, $quiz_url, $this->input->post('question_details', FALSE), $question_details);
            $data['question_details'] = $this->utility_model->changed_special_character_to_text($question_details_update);
            $this->remove_tinymec_directory($quiz_creator_info->person_id);
        }
        $data['points_per_answer'] = $this->input->post('points_per_answer', TRUE);
        $data['time_limit'] = $this->input->post('time_limit', TRUE);
        $data['question_type'] = $question_type = $this->input->post('question_type', 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);

        if ($question_type == 1) {           //tania
            $question_option = $this->input->post('option', FALSE);
            $question_status = $this->input->post('status', TRUE);
            $question_options_old = $this->select_question_options_by_question_id($question_id);

            for ($i = 0; $i < count($question_option); $i++) {
                if ($i == $question_status) {
                    $mcqdata['question_option_status'] = 1;
                } else {
                    $mcqdata['question_option_status'] = 0;
                }
                $question_option_update = $this->utility_model->update_tinymce_information_question_option($quiz_id, $quiz_creator_info->url, $quiz_url, $question_option[$i], $question_options_old[$i]->question_option);
                $mcqdata['question_option'] = $this->utility_model->changed_special_character_to_text($question_option_update);
                $this->db->where('question_option_id', $question_options_old[$i]->question_option_id);
                $this->db->update('tbl_question_option', $mcqdata);
            }
        } else if ($question_type == 2) {
            $true_false_option_info = $this->select_question_options_by_question_id($question_id);
            $question_status = $this->input->post('status', TRUE);
            foreach ($true_false_option_info as $value) {
                if ($value->question_option_id == $question_status) {
                    $truedata['question_option_status'] = 1;
                } else {
                    $truedata['question_option_status'] = 0;
                }
                $this->db->where('question_option_id', $value->question_option_id);
                $this->db->update('tbl_question_option', $truedata);
            }
        } else if ($question_type == 4) {
            $question_options_id = $this->super_admin_quiz_model->select_fill_in_the_blanks_options_by_question_id($question_id);
            $filldata['question_option'] = $this->input->post('option', FALSE);
            $this->db->where('question_option_id', $question_options_id->question_option_id);
            $this->db->update('tbl_question_option', $filldata);
        }
    }

//tania 30-08-2016
    function get_total_quiz_topic($quiz_topic_id) {
        $sql = "SELECT count(*) as total_quiz FROM tbl_quiz AS q 
                WHERE q.quiz_topics_specification IN ($quiz_topic_id)";
        return $this->db->query($sql)->row()->total_quiz;
    }

//tania 30-08-2016    
    function get_others_topic_info() {
        $sql = "SELECT * FROM 
	tbl_topic as t,
	tbl_quiz as q
	WHERE t.topic_status = '3'
	AND t.topic_id = q.quiz_topics_specification
	AND q.quiz_status = '1'";
        return $this->db->query($sql)->result();
    }

    function get_other_topic_count() {
        $sql = "SELECT count(*) as other_topic_count
        FROM 
	tbl_topic as t,
	tbl_quiz as q
	WHERE t.topic_status = '3'
	AND t.topic_id = q.quiz_topics_specification
	AND q.quiz_status = '1'";
        return $this->db->query($sql)->row()->other_topic_count;
    }

    function get_other_topic_id($quiz_topic_id) {
        $sql = "SELECT 
        GROUP_CONCAT(t.topic_id) as other_topic_id
	FROM tbl_topic as t
	WHERE 
	t.topic_id in ($quiz_topic_id)
	AND t.topic_status ='3'";
        return $result = $this->db->query($sql)->row()->other_topic_id;
    }

//20-08-2016    
    function select_latest_quiz_topic_wise($topic_id_array, $topic_id) {
        if (count($topic_id_array) == 1) {
            $sql = "SELECT * FROM tbl_quiz as q,tbl_url as u WHERE "
                    . " q.quiz_topics_specification = '$topic_id' "
                    . " AND q.quiz_status=1 AND  u.url_type=4 AND q.url_id=u.url_id "
                    . "  ORDER BY q.quiz_id DESC LIMIT 10";
            $result = $this->db->query($sql)->result();
            return $result;
        } else if (count($topic_id_array) > 1) {
            $sql = "SELECT * FROM tbl_quiz as q,tbl_url as u "
                    . "  WHERE  q.quiz_topics_specification IN ($topic_id) "
                    . "  AND q.quiz_status=1 AND q.url_id=u.url_id AND  url_type=4"
                    . "   ORDER BY q.quiz_id DESC LIMIT 10";
            $result = $this->db->query($sql)->result();
            return $result;
        }
    }

    //tania 19-08-2016
    function quiz_url_by_quiz_id($quiz_id) {
        $sql = "SELECT * 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 update_question_info_my() {
        $data = array();

        $quiz_url = $this->input->post('quiz_id', true);
        $quiz_info = $this->select_quiz_by_url($quiz_url);
        $first_question = $this->select_quiz_first_question($quiz_info->quiz_id);

        $data['quiz_id'] = $quiz_info->quiz_id;
        $data['question_details'] = $this->input->post('question_details', true);
        $data['points_per_answer'] = $this->input->post('points_per_answer', true);
        $data['time_limit'] = $this->input->post('time_limit', true);

        $data['question_type'] = $question_type = $this->input->post('question_type', true);
        $data['question_status'] = 1;
        $data['question_created_by'] = $this->session->userdata('user_id');
        $this->db->insert('tbl_question', $data);

        $insert_id = $this->db->insert_id();

        if ($question_type == 3) {
            $question = $this->input->post('question_details', true);
            $desc_answer = $this->input->post('desc_answer', true);
            $desc_answer = (explode(" ", $desc_answer));
            $words = array(".", "?", ":", ",", ";", "(", ")", "=", "+", "!");
            $desc_answer = str_replace($words, "", $desc_answer);
            $words = array("am", "is", "are", "was", "were", "has", "have", "had", "can", "could", "can", "may", "might", "shall", "will", "should", "would", "a", "an", "the", "of", "in", "to", "with", "as", "at", "for", "on", "by", "from", "not", "no");
            foreach ($desc_answer as $v_answer) {
                if (!in_array($v_answer, $words)) {
                    $desc_answer_keys[] = $v_answer;
                }
            }
            if ($desc_answer_keys) {
                $unique_ans_keys = array_unique($desc_answer_keys);
            } else {
                $unique_ans_keys = '';
            }
            $descript_ans_keys = (implode(", ", $unique_ans_keys));

            $odata = array();
            $odata['question_id'] = $insert_id;
            $odata['descriptive_ans_keyword'] = $descript_ans_keys;
            $odata['question_option_status'] = 1;
            $this->db->insert('tbl_question_option', $odata);
        } else {
            $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]['question_option_status'] = 1;
                } else {
                    $odata[$key]['question_option_status'] = 0;
                }
                $this->db->insert('tbl_question_option', $odata[$key]);
            }
        }




//UPDATE POINTS PER ANSWER 
        if ($data['points_per_answer'] != $first_question->points_per_answer) {
            $all_question = $this->select_all_question_by_quiz_id($quiz_info->quiz_id);
            foreach ($all_question as $v_question) {
                $this->db->set('points_per_answer', $data['points_per_answer']);
                $this->db->set('time_limit', $data['time_limit']);
                $this->db->where('question_id', $v_question->question_id);
                $this->db->update('tbl_question');
            }
        }
    }

    public function select_quiz($start = NULL, $limit = NULL, $status = NULL, $short = NULL) {
        $sql = "SELECT "
                . "     q.*, t.topic_name, p.person_first_name, u.url "
                . " FROM "
                . "     tbl_quiz as q, "
                . "     tbl_topic as t, "
                . "     tbl_person as p, "
                . "     tbl_url as u "
                . " WHERE "
                . "         q.quiz_topics_specification = t.topic_id "
                . "     AND q.quiz_created_by = p.person_id "
                . "     AND u.url_id = q.url_id ";

        if ($status != '') {
            $sql .= "WHERE q.quiz_status = $status ";
        } else {
            $sql .= " AND q.quiz_status = 1 ";
        }
        $sql .= " AND (q.quiz_pulication_status = 1 OR q.quiz_pulication_status = 2 OR q.quiz_pulication_status = 5 OR q.quiz_pulication_status = 6 )"
                . " ORDER BY q.quiz_id DESC ";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_topic_specific_latest_quiz($topic_url, $start = NULL, $limit = NULL, $status = NULL, $short = NULL) {
        $sql = "SELECT "
                . "     q.*, t.topic_name, p.person_first_name, u.url "
                . " FROM "
                . "     tbl_quiz as q, "
                . "     tbl_topic as t, "
                . "     tbl_person as p, "
                . "     tbl_url as u "
                . " WHERE "
                . "         u.url = '$topic_url'"
                . "     AND t.url_id = u.url_id "
                . "     AND q.quiz_topics_specification = t.topic_id "
                . "     AND q.quiz_created_by = p.person_id ";

        $sql .= " AND ( q.quiz_pulication_status = 1 OR q.quiz_pulication_status = 2 OR q.quiz_pulication_status = 5 OR q.quiz_pulication_status = 6 )"
                . " ORDER BY q.quiz_id DESC";

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

    public function select_topic_specific_popular_quiz($topic_url, $start = NULL, $limit = NULL, $status = NULL, $short = NULL) {
        $sql = "SELECT "
                . "     q.*, t.topic_name, p.person_first_name, u.url "
                . " FROM "
                . "     tbl_quiz as q, "
                . "     tbl_topic as t, "
                . "     tbl_person as p, "
                . "     tbl_url as u "
                . " WHERE "
                . "         u.url = '$topic_url'"
                . "     AND t.url_id = u.url_id "
                . "     AND q.quiz_topics_specification = t.topic_id "
                . "     AND q.quiz_created_by = p.person_id ";

        $sql .= " AND ( q.quiz_pulication_status = 1 OR q.quiz_pulication_status = 2 OR q.quiz_pulication_status = 5 OR q.quiz_pulication_status = 6 )"
                . " ORDER BY q.quiz_taken_number DESC";

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

        return $result;
    }

    public function select_all_quiz() {
        $this->db->select('*');
        $this->db->from('tbl_quiz');
        $query_result = $this->db->get();
        $result = $query_result->result();
        return $result;
    }

    public function select_all_quiz_by_person_id($person_id) {
        $tt = array(0,1);
        $this->db->select('*')
               ->from('tbl_quiz as q')
                ->join('tbl_url as u', 'q.url_id = u.url_id')
                ->where('q.quiz_created_by',$person_id)
                ->where_in(' q.quiz_status',$tt);
//        $sql = "SELECT * "
//                . " FROM "
//                . "     tbl_quiz as q, tbl_url as u "
//                . " WHERE "
//                . "         q.quiz_created_by = '$person_id' "
//                . "     AND (q.quiz_status = 0 OR q.quiz_status = 1)"
//                . "     AND q.url_id = u.url_id"
//        ;
        $result = $this->db->get()->result();

        return $result;
    }
    public function select_limit_quiz_by_person_id($person_id, $limit_offset) {
        $tt = array(0,1);
        $this->db->select('*')
                ->from('tbl_quiz as q')
                ->join('tbl_url as u', 'q.url_id = u.url_id')
                ->where('q.quiz_created_by',$person_id)
                ->where_in(' q.quiz_status',$tt)
                ->limit(10,$limit_offset);
//        $sql = "SELECT * "
//                . " FROM "
//                . "     tbl_quiz as q, tbl_url as u "
//                . " WHERE "
//                . "         q.quiz_created_by = '$person_id' "
//                . "     AND (q.quiz_status = 0 OR q.quiz_status = 1)"
//                . "     AND q.url_id = u.url_id "
//                . "LIMIT 10 OFFSET $limit_offset";
        $result = $this->db->get()->result();
        //$result = $this->db->limit(10,$query);

        return $result;
    }
    
    /**
     * @Function            : select_learner_all_quiz_by_person_id
     * @Used IN             : @function-'view_quiz_result_for_profile'
     * @Description         : 
     * @Developed By        : Tania Aktar
     * @Start DateTime      : 29-08-2016
     * @Updated By          : Md Mashqur Ul Alam
     * @Update Date         : 18-01-16
     * @Parameter           : $person_id[INTEGER]
     * @Return              : $result   
     * @Status              : Available       
     * */

    // tania update 29-08-2016
    function select_learner_limit_quiz_by_person_id($person_id, $limit_offset, $limit_data = NULL) {
//        $sql = "SELECT DISTINCT q.*, u.url, t.* "
//                . " FROM "
//                . "     tbl_quiz_result as qr, "
//                . "     tbl_topic as t,"
//                . "     tbl_quiz as q, "
//                . "     tbl_url as u "
//                . " WHERE "
//                . "     qr.person_id = '$person_id' "
//                . "     AND qr.quiz_id = q.quiz_id "
//                . "     AND q.url_id = u.url_id "
//                //. "     AND q.quiz_topics_specification = t.topic_id"
//                . "     AND q.quiz_status = '1'";
//        $result = $this->db->query($sql)->result();
        $limit_data = isset($limit_data) ? $limit_data : 10;
        $result = $this->db->select('*')
                           ->from('tbl_quiz_result AS qr')
                           ->join('tbl_quiz AS q', 'q.quiz_id = qr.quiz_id')
                           ->join('tbl_url AS u', 'u.url_id = q.url_id')
                           ->join('tbl_topic AS t','t.topic_id = q.quiz_topics_specification')
                           ->where('qr.person_id', $person_id)
                           ->limit($limit_data, $limit_offset)
                           ->get()->result();       
        
        return $result;
    }
    
    function select_learner_all_quiz_by_person_id($person_id) {
//        $sql = "SELECT DISTINCT q.*, u.url, t.* "
//                . " FROM "
//                . "     tbl_quiz_result as qr, "
//                . "     tbl_topic as t,"
//                . "     tbl_quiz as q, "
//                . "     tbl_url as u "
//                . " WHERE "
//                . "     qr.person_id = '$person_id' "
//                . "     AND qr.quiz_id = q.quiz_id "
//                . "     AND q.url_id = u.url_id "
//                //. "     AND q.quiz_topics_specification = t.topic_id"
//                . "     AND q.quiz_status = '1'";
//        $result = $this->db->query($sql)->result();
        
        $result = $this->db->select('*')
                           ->from('tbl_quiz_result AS qr')
                           ->join('tbl_quiz AS q', 'q.quiz_id = qr.quiz_id')
                           ->join('tbl_url AS u', 'u.url_id = q.url_id')
                           ->join('tbl_topic AS t','t.topic_id = q.quiz_topics_specification')
                           ->where('qr.person_id', $person_id)                        
                           ->get()->result();       
        
        return $result;
    }

    public function select_active_quiz_by_person_id($person_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_quiz as q, tbl_url as u "
                . " WHERE "
                . "         q.quiz_created_by = '$person_id'"
                . "     AND q.quiz_status = 1 "
                . "     AND q.url_id = u.url_id "
        ;
        $result = $this->db->query($sql)->result();

        return $result;
    }

    public function select_inactive_quiz_by_person_id($person_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_quiz as q, tbl_url as u "
                . " WHERE "
                . "         q.quiz_created_by = '$person_id'"
                . "     AND q.quiz_status = 0 "
                . "     AND q.url_id = u.url_id "
        ;
        $result = $this->db->query($sql)->result();

        return $result;
    }

    public function select_archived_quiz_by_person_id($person_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_quiz as q, tbl_url as u "
                . " WHERE "
                . "         q.quiz_created_by = '$person_id'"
                . "     AND q.quiz_status = 2 "
                . "     AND q.url_id = u.url_id "
        ;
        $result = $this->db->query($sql)->result();

        return $result;
    }

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

    public function select_active_quiz_by_id($quiz_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_quiz as q, tbl_url as u "
                . " WHERE "
                . "         q.quiz_id = '$quiz_id' "
                . "     AND u.url_id = q.url_id "
                . "     AND q.quiz_status = 1 "
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_quiz_all_status_by_url($quiz_url) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_quiz as q, tbl_url as u, tbl_topic as t "
                . " WHERE "
                . "         u.url = '$quiz_url' "
                . "     AND u.url_id = q.url_id "
                . "     AND t.topic_id = q.quiz_topics_specification "
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_quiz_all_status_by_public_id($quiz_public_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_quiz as q, tbl_url as u, tbl_topic as t "
                . " WHERE "
                . "         q.quiz_public_id = '$quiz_public_id' "
                . "     AND u.url_id = q.url_id "
                . "     AND t.topic_id = q.quiz_topics_specification "
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_quiz_by_public_id($quiz_public_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_quiz as q, tbl_url as u, tbl_topic as t "
                . " WHERE "
                . "         q.quiz_public_id = '$quiz_public_id' "
                . "     AND u.url_id = q.url_id "
                . "     AND q.quiz_status = 1 "
                . "     AND t.topic_id = q.quiz_topics_specification "
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

    public function select_quiz_by_url($url) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_quiz as q, tbl_url as u "
                . " WHERE "
                . "         u.url = '$url' "
                . "     AND u.url_id = q.url_id "
                . "     AND (q.quiz_status = 0 OR q.quiz_status = 1 )"
        ;
        $result = $this->db->query($sql)->row();
        return $result;
    }

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

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

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

    public function select_all_question_by_quiz_id($quiz_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_question as q "
                . " WHERE "
                . "         q.quiz_id = '$quiz_id' "
                . "     AND q.question_status = 1 "
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    /*
     * @Function            : select_active_question_by_quiz_id 
     * @Used IN             : @function-'view_quiz_result_for_profile'
     * @Description         : Select questions of a quiz
     * @Developed By        : Unknown
     * @Updated By          : Md Mashqur Ul Alam
     * @Update DateTime     : 18-01-17
     * @Parameter           : $quiz_id      
     * @Status              : Available       
     */
    
    public function select_active_question_by_quiz_id($quiz_id) {
//        $sql = "SELECT * "
//                . " FROM "
//                . "     tbl_question as q "
//                . " WHERE "
//                . "         q.quiz_id = '$quiz_id' "
//                . "     AND q.question_status = 1 "
//        ;
//        $result = $this->db->query($sql)->result();
        
        $result = $this->db->select('*')->from('tbl_question')
                           ->where('quiz_id', $quiz_id)
                           ->where('question_status', 1)
                           ->get()->result();
        return $result;
    }

    /*
     * @Function            : select_person_rank 
     * @Used IN             : @function-'view_quiz_result_for_profile'
     * @Description         : Select rank of a person of quiz
     * @Developed By        : Unknown
     * @Updated By          : Md Mashqur Ul Alam
     * @Update DateTime     : 18-01-17
     * @Parameter           : $quiz_id      
     * @Status              : Available       
     */
    public function select_person_rank($quiz_id) {
//        $sql = "SELECT DISTINCT qr.person_id  "
//                . " FROM "
//                . "     tbl_quiz_result as qr "
//                . " WHERE "
//                . "         qr.quiz_id = '$quiz_id' "
//                . "     AND qr.quiz_result_status = 1 "
//                . " ORDER BY qr.quiz_receive_marks DESC"
//        ;
//
//        $result = $this->db->query($sql)->result();
        
        $result = $this->db->select('*')->distinct()->from('tbl_quiz_result')
                           ->where('quiz_id', $quiz_id)
                           ->where('quiz_result_status', 1)
                           ->order_by('quiz_receive_marks', 'DESC')
                           ->get()->result();
        return $result;
    }

    public function select_inactive_question_by_quiz_id($quiz_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_question as q "
                . " WHERE "
                . "         q.quiz_id = '$quiz_id' "
                . "     AND q.question_status = 0 "
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_archived_question_by_quiz_id($quiz_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_question as q "
                . " WHERE "
                . "         q.quiz_id = '$quiz_id' "
                . "     AND q.question_status = 2 "
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_sugested_quiz() {

        $sql = "SELECT * "
                . " FROM "
                . "     tbl_quiz as q "
                . " WHERE "
                . "         (q.quiz_pulication_status = 1 OR q.quiz_pulication_status = 2)"
                . "     AND q.quiz_status = 1 "
                . " ORDER BY q.quiz_taken_number DESC"
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_popular_quiz($start, $limit, $status, $short = NULL) {

        $sql = "SELECT * "
                . " FROM "
                . "     tbl_quiz as q "
                . " WHERE "
                . "         (q.quiz_pulication_status = 1 OR q.quiz_pulication_status = 2)"
                . "     AND q.quiz_status = 1 "
                . " ORDER BY q.quiz_taken_number DESC"
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_active_popular_quiz($start, $limit, $status, $short = NULL) {

        $sql = "SELECT q.*, u.url "
                . " FROM "
                . "     tbl_quiz as q, tbl_url as u  "
                . " WHERE "
                . "         q.quiz_status = 1 "
                . "     AND u.url_id = q.url_id "
                . " ORDER BY q.quiz_taken_number DESC "
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    //tania 19-08-2016
    function select_latest_quiz_topic($number) {
        $result=array();
        $sql = "SELECT * FROM 	
	tbl_topic as t,
	tbl_quiz as q
	WHERE q.quiz_topics_specification = t.topic_id
	AND t.topic_status in(1,4)
	AND q.quiz_status = '1' 
	GROUP BY q.quiz_topics_specification
        ORDER BY t.topic_name ASC
	LIMIT $number";
        if($result){
            $result = $this->db->query($sql)->result();        
            return $result;
        }       
    }

    //select latest quiz from quiz table for public show by afrin jahan noumin
    public function select_latest_quize($number) {
        if ($number == 0) {
            $sql = "SELECT * from tbl_quiz as q , tbl_url as u where q.quiz_status=1 AND q.url_id= u.url_id  ORDER BY  q.quiz_id DESC LIMIT 3";
            $result = $this->db->query($sql)->result();
            return $result;
        } else {
            $sql = "SELECT * from tbl_quiz as q , tbl_url as u where q.quiz_status=1 AND q.url_id= u.url_id  ORDER BY  q.quiz_id  DESC LIMIT $number ";
            $result = $this->db->query($sql)->result();
            return $result;
        }
    }

    public function select_popular_quize($number) {
        $sql = "SELECT * from tbl_quiz where quiz_status=1 ORDER BY quiz_taken_number DESC LIMIT $number";
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_popular_quiz_topic($number) {

        $sql = "SELECT DISTINCT q.quiz_topics_specification, t.*, u.url "
                . " FROM "
                . "     tbl_quiz as q, tbl_topic as t, tbl_url as u  "
                . " WHERE "
                . "         t.topic_id = q.quiz_topics_specification "
                . "     AND (t.topic_status = 1 OR t.topic_status = 4 or t.topic_status = 3)"
                . "     AND q.quiz_status = 1  "
                . "     AND u.url_id = t.url_id "
                . " ORDER BY q.quiz_taken_number DESC"
                . " LIMIT $number "
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    //20-8-2016
    public function select_all_quiz_topic_wise($topic_id_array, $topic_id) {
        if (count($topic_id_array) == 1) {
            $sql = "SELECT * FROM tbl_quiz as q,tbl_url as u WHERE 
                    q.quiz_topics_specification = '$topic_id' 
                    AND q.quiz_status=1 AND  
                    u.url_type=4 AND q.url_id=u.url_id 
                    ORDER BY q.quiz_taken_number DESC LIMIT 10";
            $result = $this->db->query($sql)->result();
            return $result;
        } else if (count($topic_id_array) > 1) {
            $sql = "SELECT * FROM tbl_quiz as q,tbl_url as u 
                WHERE q.quiz_topics_specification IN ($topic_id) 
                AND q.quiz_status=1 
                AND q.url_id=u.url_id 
                AND url_type=4
                ORDER BY q.quiz_taken_number DESC LIMIT 10";
            $result = $this->db->query($sql)->result();
            return $result;
        }
    }

//update
    function get_total_quiz() {
        $sql = "SELECT count(*) as total_quiz
	FROM tbl_quiz as q,
	tbl_topic as t
	WHERE q.quiz_topics_specification = t.topic_id
	AND q.quiz_status ='1' ";
        return $result = $this->db->query($sql)->row()->total_quiz;
    }

//tania 30-08-2016
    function get_all_quiz_topic_id() {
        $sql = "SELECT GROUP_CONCAT(DISTINCT(q.quiz_topics_specification)) AS quiz_topic_id FROM tbl_quiz AS q WHERE q.quiz_status = '1'";
        return $this->db->query($sql)->row()->quiz_topic_id;
    }

    function select_all_popular_quiz_pagination($limit) {
        $sql = "SELECT * from 
        tbl_quiz as q,
	tbl_topic as t,
	tbl_url as u,
	tbl_person as p
	where q.quiz_status = '1'
	AND q.quiz_topics_specification = t.topic_id
	AND q.quiz_created_by = p.person_id
	AND p.url_id = u.url_id
	ORDER BY q.quiz_taken_number DESC LIMIT $limit, 10";
        $result = $this->db->query($sql)->result();
        return $result;
    }

//update  31-08-2016  
    function select_all_popular_quiz() {
        $sql = "SELECT * from 
        tbl_quiz as q,
	tbl_topic as t,
	tbl_url as u,
	tbl_person as p
	where q.quiz_status = '1'
	AND q.quiz_topics_specification = t.topic_id
	AND q.quiz_created_by = p.person_id
	AND p.url_id = u.url_id
        AND t.topic_status in (1,3,4)
	ORDER BY q.quiz_taken_number DESC LIMIT 10";
        $result = $this->db->query($sql)->result();
        return $result;
    }

//tania 24-08-2016    
    function select_all_latest_quiz_pagination($limit) {
        $sql = "SELECT * from 
        tbl_quiz as q,
	tbl_topic as t,
	tbl_url as u,
	tbl_person as p
	where q.quiz_status = '1'
	AND q.quiz_topics_specification = t.topic_id
	AND q.quiz_created_by = p.person_id
	AND p.url_id = u.url_id
	ORDER BY q.quiz_id DESC LIMIT $limit, 10";
        $result = $this->db->query($sql)->result();
        return $result;
    }

//tania 24-08-2016    
    function topic_wise_total_quiz($topic_id_array, $topic_id) {
        if (count($topic_id_array) == 1) {
            $sql = "SELECT count(*) as total_rows
                    FROM tbl_quiz as q,
                    tbl_url as u 
                    WHERE 
                    q.quiz_topics_specification = '$topic_id' 
                    AND q.quiz_status=1 
                    AND u.url_type=4 AND q.url_id=u.url_id";
            return $result = $this->db->query($sql)->row()->total_rows;
        } else if (count($topic_id_array) > 1) {
            $sql = "SELECT count(*) as total_rows 
                FROM tbl_quiz as q,tbl_url as u 
                WHERE q.quiz_topics_specification IN ($topic_id) 
                AND q.quiz_status=1 
                AND q.url_id=u.url_id 
                AND url_type=4";
            return $result = $this->db->query($sql)->row()->total_rows;
        }
    }

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

    public function update_quiz_by_id($data, $quiz_id) {

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

    public function select_question($start, $limit, $status, $short = NULL) {
        $sql = "SELECT * "
                . "FROM tbl_question";

        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 select_all_active_question($quiz_id) {
        $sql = "SELECT * "
                . " FROM tbl_question as q, tbl_quiz as qz "
                . " WHERE "
                . "         q.question_status = 1 "
                . "     AND q.quiz_id = qz.quiz_id ";

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

    function select_all_active_question_by_quiz_id($quiz_id) {
        $sql = "SELECT * "
                . " FROM tbl_question as q, tbl_quiz as qz "
                . " WHERE "
                . "         q.quiz_id = '$quiz_id'"
                . "     AND q.question_status = 1 "
                . "     AND q.quiz_id = qz.quiz_id ";

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

//update 31-08-2016
    function select_all_latest_quiz() {
        $sql = "SELECT * from 
        tbl_quiz as q,
	tbl_topic as t,
	tbl_url as u,
	tbl_person as p
	where q.quiz_status = '1'
	AND q.quiz_topics_specification = t.topic_id
	AND q.quiz_created_by = p.person_id
	AND p.url_id = u.url_id
	AND t.topic_status in (1, 3, 4)
	ORDER BY q.quiz_id DESC LIMIT 10";
        $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_multiple_choice_question($quiz_id) {
        $sql = "SELECT * "
                . "FROM tbl_question as q, tbl_quiz as qz"
                . " WHERE"
                . "         qz.quiz_id = '$quiz_id' "
                . "     AND qz.quiz_id = q.quiz_id "
                . "     AND q.question_type = 1"
                . "     AND (q.question_status = 0 OR q.question_status = 1)";

        $result = $this->db->query($sql)->result();
        return $result;
    }
    
    public function select_limited_multiple_choice_question($quiz_id, $limit_offset) {
        $sql = "SELECT * "
                . "FROM tbl_question as q, tbl_quiz as qz"
                . " WHERE"
                . "         qz.quiz_id = '$quiz_id' "
                . "     AND qz.quiz_id = q.quiz_id "
                . "     AND q.question_type = 1"
                . "     AND (q.question_status = 0 OR q.question_status = 1) LIMIT 10 OFFSET $limit_offset";

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

    public function select_currect_ans($question_id) {
        $sql = "SELECT * "
                . "FROM tbl_question as q, tbl_question_option as qo"
                . " WHERE"
                . "         q.question_id = '$question_id' "
                . "     AND qo.question_id = q.question_id "
                . "     AND qo.question_option_status = 1";

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

    public function select_mcq_currect_ans($question_id) {
        $sql = "SELECT * "
                . "FROM tbl_question as q, tbl_question_option as qo"
                . " WHERE"
                . "         q.question_id = '$question_id' "
                . "     AND qo.question_id = q.question_id "
                . "     AND qo.question_option_status = 1";

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

    public function select_true_false_currect_ans($question_id) {
        $sql = "SELECT * "
                . "FROM tbl_question as q, tbl_question_option as qo"
                . " WHERE"
                . "         q.question_id = '$question_id' "
                . "     AND qo.question_id = q.question_id "
                . "     AND qo.question_option_status = 1";

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

    public function select_true_false_question($quiz_id) {
        $sql = "SELECT * "
                . "FROM tbl_question as q, tbl_quiz as qz"
                . " WHERE"
                . "         qz.quiz_id = '$quiz_id' "
                . "     AND qz.quiz_id = q.quiz_id "
                . "     AND q.question_type = 2"
                . "     AND (q.question_status = 0 OR q.question_status = 1)";

        $result = $this->db->query($sql)->result();
        return $result;
    }
    
    public function select_limited_true_false_question($quiz_id, $limit_offset) {
        $sql = "SELECT * "
                . "FROM tbl_question as q, tbl_quiz as qz"
                . " WHERE"
                . "         qz.quiz_id = '$quiz_id' "
                . "     AND qz.quiz_id = q.quiz_id "
                . "     AND q.question_type = 2"
                . "     AND (q.question_status = 0 OR q.question_status = 1) LIMIT 10 OFFSET $limit_offset";

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

    public function select_descriptive_question_currect_ans($question_id) {
        $sql = "SELECT * "
                . "FROM tbl_question as q, tbl_question_option as qo"
                . " WHERE"
                . "         q.question_id = '$question_id' "
                . "     AND qo.question_id = q.question_id "
                . "     AND qo.question_option_status = 1";

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

    public function select_fill_blank_question_currect_ans($question_id) {
        $sql = "SELECT * "
                . "FROM tbl_question as q, tbl_question_option as qo"
                . " WHERE"
                . "         q.question_id = '$question_id' "
                . "     AND qo.question_id = q.question_id "
                . "     AND qo.question_option_status = 1";

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

    public function select_descriptive_question_question($quiz_id) {
        $sql = "SELECT * "
                . "FROM tbl_question as q, tbl_quiz as qz"
                . " WHERE"
                . "         qz.quiz_id = '$quiz_id' "
                . "     AND qz.quiz_id = q.quiz_id "
                . "     AND q.question_type = 3"
                . "     AND (q.question_status = 0 OR q.question_status = 1)";

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

    public function select_fill_blank_question_question($quiz_id) {
        $sql = "SELECT * "
                . "FROM tbl_question as q, tbl_quiz as qz"
                . " WHERE"
                . "         qz.quiz_id = '$quiz_id' "
                . "     AND qz.quiz_id = q.quiz_id "
                . "     AND q.question_type = 4"
                . "     AND (q.question_status = 0 OR q.question_status = 1)";

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

    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_quiz_active_topic() {
        $this->db->select('*');
        $this->db->select('topic_status', 1);
        $this->db->from('tbl_topic');
        $query_result = $this->db->get();
        $result = $query_result->result();
        return $result;
    }

    public function select_quiz_topic($person_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_topic as t, "
                . "     tbl_url as u "
                . " WHERE "
                . "         t.url_id=u.url_id "
                . "     AND (t.topic_status = 1 OR ( t.topic_status = 3 AND t.topic_created_by = '$person_id') OR ( t.topic_status = 4 ))"
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_quiz_topic_by_topic_id($topic_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_topic as t, "
                . "     tbl_url as u "
                . " WHERE "
                . "         t.topic_id = '$topic_id' "
                . "     AND t.url_id = u.url_id "
        ;
        $result = $this->db->query($sql)->row();

        return $result;
    }

    public function select_all_active_group($person_id) {
        $this->db->select('*');
        $this->db->select('group_status', 1);
        $this->db->select('group_created_by', $person_id);
        $this->db->from('tbl_group');
        $query_result = $this->db->get();
        $result = $query_result->result();

        return $result;
    }

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

    public function select_course_by_person_id($person_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_course as c "
                . " WHERE "
                . "         c.person_id = '$person_id' "
                . "     AND c.course_status = 4 "
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_public_course_by_person_id($person_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_course as c "
                . " WHERE "
                . "         c.person_id = '$person_id' "
                . "     AND c.course_access_level = 1 "
                . "     AND (c.course_status = 1 OR c.course_status = 4)  "
        ;
        $result = $this->db->query($sql)->result();
        return $result;
    }

    public function select_video_course_by_person_id($person_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_video_management as vm "
                . " WHERE "
                . "         vm.person_id = '$person_id' "
                . "     AND vm.video_management_status = 4 "
                . "     AND (vm.video_management_status = 1 OR vm.video_management_status = 4) "
        ;
        $result = $this->db->query($sql)->result();
        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->row();
        return $result;
    }

    public function select_quiz_first_question($quiz_id) {
        $sql = "SELECT  * FROM tbl_question as q WHERE q.quiz_id = '$quiz_id'";
        $query_result = $this->db->query($sql);
        $result = $query_result->row();
        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() {

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

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

    public function select_all_active_question_info() {

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

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

    public function select_all_marks_active_question_info() {

        $sql = "SELECT  SUM(points_per_answer) AS total FROM tbl_question  WHERE  question_status=1";
        $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('status', 1);
        $query_result = $this->db->get();
        $result = $query_result->row();
        return $result;
    }

    /*
     * end calculation.
     */

    public function calculate_quiz_total_time($data) {
        $quiz_total_time = 0;
        if ($data['quiz_info']->quiz_random_static == 1) {
            $quiz_total_time = ($data['quiz_questions'][0]->time_limit * 60) * $data['quiz_info']->total_number_of_question;
        } else {
            foreach ($data['quiz_questions'] as $value) {
                $quiz_total_time += $value->time_limit;
            }
            $quiz_total_time = $quiz_total_time * 60;
        }
        return $quiz_total_time;
    }

    public function calculate_quiz_total_question($data) {
        $quiz_total_question = 0;
        if ($data['quiz_info']->quiz_random_static == 1) {
            $quiz_total_question = $data['quiz_info']->total_number_of_question;
        } else {
            foreach ($data['quiz_questions'] as $value) {
                $quiz_total_question++;
            }
        }
        return $quiz_total_question;
    }

    public function calculate_quiz_total_mark($data) {
        $quiz_total_marks = 0;
        if ($data['quiz_info']->quiz_random_static == 1) {
            $quiz_total_marks = $data['quiz_questions'][0]->points_per_answer * $data['quiz_info']->total_number_of_question;
        } else {
            foreach ($data['quiz_questions'] as $value) {
                $quiz_total_marks += $value->points_per_answer;
            }
        }
        return $quiz_total_marks;
    }

    public function save_quiz_result_info($data) {
        $user_id = $this->session->userdata('user_id');
        $quiz_id = $data['quiz_info']->quiz_id;

        $correct_ans = $this->session->userdata('correct_ans');
        $wrong_ans = $this->session->userdata('wrong_ans');
        $receive_marks = $this->session->userdata('receive_marks');

        if (!$correct_ans) {
            $correct_ans = 0;
        }
        if (!$wrong_ans) {
            $wrong_ans = 0;
        }
        if (!$receive_marks) {
            $receive_marks = 0;
        }

        $total_question = $this->calculate_quiz_total_question($data);
        $total_marks = $this->calculate_quiz_total_mark($data);
        if ($total_marks) {
            $marks_percentage = ($receive_marks * 100) / $total_marks;
        } else {
            $marks_percentage = 0;
        }

        $qdata = array();
        $qdata['quiz_id'] = $quiz_id;
        $qdata['resource_person_id'] = $data['quiz_info']->quiz_created_by;
        //$qdata['grade_id'] = $this->search_quiz_grade($quiz_id);
        $qdata['quiz_total_question'] = $total_question;
        $qdata['quiz_correct_answer'] = $correct_ans;
        $qdata['quiz_wrong_answer'] = $wrong_ans;
        $qdata['quiz_total_marks'] = $total_marks;
        $qdata['quiz_receive_marks'] = $receive_marks;
        $qdata['quiz_receive_marks_percentage'] = $marks_percentage;
        $qdata['quiz_result_permission'] = 1; // public
        $qdata['quiz_result_status'] = 0; // unpublish
        $qdata['person_id'] = $user_id;
        $qdata['quiz_finish_time'] = $this->session->userdata('taken_time');

        $this->db->insert('tbl_quiz_result', $qdata);
        $insert_id = $this->db->insert_id();

        return $insert_id;
    }

//    public function search_quiz_grade($quiz_id) {
//        $grade_id = NULL;
//        $sql = "SELECT * FROM tbl_grade  WHERE quiz_id= '$quiz_id'  ";
//        $result = $this->db->query($sql)->result();
//        $grade = $result;
//        if ($grade) {
//            $temp_total = 0;
//            foreach ($grade as $v_grade) {
//                if ($receive_marks >= $v_grade->grade_number && $v_grade->grade_number > $temp_total) {
//                    $temp_total = $v_grade->grade_number;
//                    $grade_id = $v_grade->grade_id;
//                }
//            }
//        }
//        return $grade_id;
//    }

    public function update_quiz_result_info($data) {
        $user_id = $this->session->userdata('user_id');
        $quiz_id = $data['quiz_info']->quiz_id;
        $correct_ans = $this->session->userdata('correct_ans');
        $wrong_ans = $this->session->userdata('wrong_ans');
        $total_question = $this->session->userdata('total_question');
        $receive_marks = $this->session->userdata('receive_marks');

        if (!$correct_ans) {
            $correct_ans = 0;
        }
        if (!$wrong_ans) {
            $wrong_ans = 0;
        }
        if (!$receive_marks) {
            $receive_marks = 0;
        }

        $total_marks = $this->calculate_quiz_total_mark($data);
        if ($total_marks) {
            $marks_percentage = ($receive_marks * 100) / $total_marks;
        } else {
            $marks_percentage = 0;
        }
//        $temp_total = 0;
//        $grade_id = 0;
//
//        $sql = "SELECT * FROM tbl_grade  WHERE quiz_id=$quiz_id  ";
//        $result = $this->db->query($sql)->result();
//        $grade = $result;
//        if ($grade) {
//            foreach ($grade as $v_grade) {
//                if ($receive_marks >= $v_grade->grade_number && $v_grade->grade_number > $temp_total) {
//                    $temp_total = $v_grade->grade_number;
//                    $grade_id = $v_grade->grade_id;
//                }
//            }
//        }

        $qdata = array();
        $qdata['quiz_id'] = $quiz_id;
        $qdata['resource_person_id'] = $data['quiz_info']->quiz_created_by;
        $qdata['quiz_total_question'] = $total_question;
        $qdata['quiz_correct_answer'] = $correct_ans;
        $qdata['quiz_wrong_answer'] = $wrong_ans;
        $qdata['quiz_total_marks'] = $total_marks;
        $qdata['quiz_receive_marks'] = $receive_marks;
        $qdata['quiz_receive_marks_percentage'] = $marks_percentage;
        $qdata['quiz_result_permission'] = 1; // public
        $qdata['quiz_result_status'] = 0; // unpublish
        //$qdata['grade_id'] = $grade_id;
        $qdata['person_id'] = $user_id;
        $qdata['quiz_finish_time'] = $this->session->userdata('taken_time');

        $this->db->where('quiz_result_id', $data['person_quiz_result']->quiz_result_id);
        $this->db->update('tbl_quiz_result', $qdata);
    }

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

    public function select_quiz_result_by_quiz_id($quiz_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_quiz_result as qr, "
                . "     tbl_quiz as q "
                . " WHERE "
                . "         q.quiz_id = '$quiz_id' "
                . "     AND qr.quiz_result_permission = 1 "
                . "     AND qr.quiz_result_status = 1";
        $result = $this->db->query($sql)->row();

        return $result;
    }

    public function select_Quiz_result_by_result_id($quiz_result_id) {
        $sql = "SELECT * "
                . " FROM "
                . "     tbl_quiz_result as qr, "
                . "     tbl_quiz as q "
                . " WHERE "
                . "         qr.quiz_result_id = '$quiz_result_id' "
                . "     AND qr.quiz_result_permission = 1 "
                . "     AND qr.quiz_result_status = 1"
                . "     AND q.quiz_id = qr.quiz_id ";
        $result = $this->db->query($sql)->row();

        return $result;
    }

    public function publish_quiz_result($result_id) {
        $this->db->set('quiz_result_permission', 1);
        $this->db->set('quiz_result_status', 1);
        $this->db->where('quiz_result_id', $result_id);
        $this->db->update('tbl_quiz_result');
    }

    public function change_quiz_result_status($result_id, $status) {
        $this->db->set('quiz_result_status', $status);
        $this->db->where('quiz_result_id', $result_id);
        $this->db->update('tbl_quiz_result');
    }

    public function change_quiz_result_permission($result_id, $permission) {
        $this->db->set('quiz_result_permission', $permission);
        $this->db->where('quiz_result_id', $result_id);
        $this->db->update('tbl_quiz_result');
    }

    /**
     * @Function            : select_quiz_information_by_video_course_id 
     * @Used IN             : @function-'view_video_course'    Welcome[Controller]
     * @Description         : Select quiz information bu video course ID
     * @Developed By        : Mohammad Jaherul Islam
     * @Start DateTime      : 13-04-2016 05:33 PM  
     * @Parameter           : $video_course_id[INTEGER]
     * @Return              : quiz_information    
     * @Status              : Available        
     * */
    function select_quiz_information_by_video_course_id($video_course_id) {
        $sql = "SELECT *
                FROM 
                    tbl_video_course_quiz_permission AS vcqp,
                    tbl_quiz AS q
                WHERE 
                    vcqp.video_management_id = '$video_course_id'
                AND vcqp.quiz_id = q.quiz_id";
        return $this->db->query($sql)->row();
    }

    //updated by afrin jahan noumin
    /*
      -----------@@@RIPON@ Start public quiz search Part----------------------------------
     */
    function get_quiz_value_by_search($data, $search_value, $start_limit, $end_limit) {
        $searched_quiz = $this->get_searched_quiz_information($search_value, $start_limit, $end_limit);
        if ($searched_quiz) {
            $data['searched_quiz'] = $searched_quiz;
            $data['total_searched_quiz'] = $this->get_total_searched_quiz_information($search_value);
        }
        return $data;
    }

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

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

    function get_tinymce_description_question_option($id, $upload_dir, $description, $type) {
        $href_match = Array();
        $src_match = Array();
        preg_match_all('@href="([^"]+)"@', $description, $href_match);
        preg_match_all('@src="([^"]+)"@', $description, $src_match);
        if ($src_match[1]) {
            if (!is_dir($upload_dir)) {
                mkdir($upload_dir);
            }

            for ($i = 0; $i < count($src_match[1]); $i++) {
                $main_full_path = urldecode($src_match[1][$i]);
                $main_full_path_array = explode('?', $main_full_path);
                $cut_string_array = explode('source', $main_full_path_array[0]);
                $main_file_path = 'source' . $cut_string_array[1];
                if (is_file($main_file_path)) {
                    $description = $this->upload_tinymc_file_question_option($id, $description, $main_full_path, $main_file_path, $upload_dir, $type);
                }
            }
        }

        if ($href_match[1]) {
            if (!is_dir($upload_dir)) {
                mkdir($upload_dir);
            }

            for ($i = 0; $i < count($href_match[1]); $i++) {
                $main_full_path = urldecode($href_match[1][$i]);
                $cut_string_array = explode('source', $main_full_path);
                $main_file_path = 'source' . $cut_string_array[1];
                if (is_file($main_file_path)) {
                    $description = $this->upload_tinymc_file_question_option($id, $description, $main_full_path, $main_file_path, $upload_dir, $type);
                }
            }
        }
        return $description;
    }

    function get_video_url_quiz_id($quiz_id) {
        $sql = "SELECT * FROM 
	tbl_quiz as q,
	tbl_video_course_quiz_permission as vp,
	tbl_video_management as vm,
	tbl_url as u
	WHERE q.quiz_id = '$quiz_id'
	AND q.quiz_id = vp.quiz_id
	AND vp.video_management_id = vm.video_management_id
	AND vm.url_id = u.url_id";
        $result = $this->db->query($sql)->row();
        return $result;
    }

    function get_course_url_by_quiz_id($quiz_id) {
        $sql = "SELECT * FROM 
	tbl_quiz as q,
	tbl_course_quiz_permission as cp,
	tbl_course as c,
	tbl_url as u
	WHERE q.quiz_id = '$quiz_id'
	AND q.quiz_id = cp.quiz_id
	AND cp.course_id = c.course_id
	AND c.url_id = u.url_id";
        $result = $this->db->query($sql)->row();
        return $result;
    }

// 02-09-2016
    function select_correct_option($question_id) {
        $sql = "SELECT * FROM tbl_question_option as qo
	WHERE qo.question_id ='$question_id'
	AND qo.question_option_status = '1' ";
        return $result = $this->db->query($sql)->row();
    }

    /*
      -----------@@@RIPON@ End public quiz search Part----------------------------------
     */
    
        /**
     * @Function            : view_quiz_result_for_profile 
     * @Used IN             : @function-'user_profile'    Application[@controller],
     * @Description         : view quiz result for user profile
     * @Developed By        : MD Jaherul Islam
     * @Start DateTime      : 01-08-2016 06:25 PM
     * @Parameter           : $person_id[INTEGER],$data[ARRAY]
     * @Return              : $data[ARRAY]    
     * @Status              : Available       
     * */
    function view_quiz_result_for_profile($person_id, $data, $limit_offset = NULL) {
        $page_number = 1;
        $limit_offset = isset($limit_offset) ? $limit_offset : 0;
        $data['all_quiz'] = $this->select_learner_limit_quiz_by_person_id($person_id, $limit_offset);
        //$this->cf->dd($limit_offset);
        $data['all_row_quiz'] = count($this->select_learner_all_quiz_by_person_id($person_id));
        foreach ($data['all_quiz'] as $v_quiz) {
            $data['quiz_result'][$v_quiz->quiz_id] = $this->select_quiz_result($person_id, $v_quiz->quiz_id);
            
            $data['quiz_questions'][$v_quiz->quiz_id] = $this->select_active_question_by_quiz_id($v_quiz->quiz_id);
            $data['quiz_total_participant'] = $this->select_person_rank($v_quiz->quiz_id);
            $rank = 1;
            foreach ($data['quiz_total_participant'] as $v_rank) {
                if ($person_id == $v_rank->person_id) {
                    break;
                }
                $rank++;
                if ($rank >= 25) {
                    break;
                }
            }
            $data['rank'][$v_quiz->quiz_id] = $rank;
        }
        $params = array('url' => '', 'div_id' => 'learner_quiz_pagination', 'total_row' => $data['all_row_quiz'], 'per_page' => 10, 'current_page' => $page_number, 'total_link' => 10);
        $data['pagination_link'] = $this->tt_pagination->view_pagination_link($params);

        return $data;
    }
    
}
