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

class Batch_Model extends CI_Model {
    function __construct() {
        parent::__construct();
    }

    /* ------------------------------------------ add batch tania --------------------------------------------------- */
    /*
     * Function         : select_all_active_courses
     * Used IN          : function [create_batch] controller[batch], function[edit_batch] controller [batch]
     * Description      : used to select all active courses
     * Parameter        : $data (person info and courses)
     * return           : $result (course info)
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 14-06-2016
     */

    function select_all_active_courses($data) {
        if ($data->person_role == 4) {      // organization
            $sql = "SELECT * FROM tbl_course WHERE person_id= '$data->person_id' AND course_status IN (1,4)"; //1= published, 4= approved
            $result = $this->db->query($sql)->result();
            return $result;
        } else if ($data->person_role == 3) {   //teacher
            $sql = "  SELECT * FROM 
	    tbl_person as p,
	    tbl_course as c,
	    tbl_resource_person as rp
	    WHERE p.person_id='$data->person_id'
	    AND rp.person_id = p.person_id
	    AND c.resource_person_id = rp.resource_person_id
            AND c.course_status IN(1,4)";           //1= published, 4= approved
            $result = $this->db->query($sql)->result();
            return $result;
        }
    }

    /*
     * Function         : select_course_info_by_course_id
     * Used IN          : function[batch_course_info] controller[batch]
     * Description      : used to select course info by course id
     * Parameter        : $course_id
     * return           : $result( course info)
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 14-06-2016
     */

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

    /*
     * Function         : save_course_batch_information
     * Used IN          : function[create_batch] controller[batch]
     * Description      : used to save course batch information 
     * Parameter        : none
     * return           : course_batch_id
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 14-06-2016
     */

    function save_course_batch_information() {
        $course_id = $this->input->post('course', TRUE);
        $course_batch_start_date = $this->input->post('course_batch_start_date', TRUE);
        $course_batch_finish_date = $this->input->post('course_batch_finish_date', TRUE);
        $course_batch_name = $this->input->post('course_batch_name', TRUE);
        $batch_info['course_id'] = $course_id;
        $batch_info['course_batch_name'] = $course_batch_name;
        $batch_info['course_batch_start_date'] = $course_batch_start_date;
        $batch_info['course_batch_finish_date'] = $course_batch_finish_date;
        $batch_info['course_batch_status'] = 4; // 4=create
        $this->db->insert('tbl_course_batch', $batch_info);
        return $this->db->insert_id();
    }

    /*
     * Function         : get_total_batch_status
     * Used IN          : function[manage_organization_batch] controller[batch]
     *                    function[view_created_batch_for_ajax_view]
     *                    function[view_running_batch_for_ajax_view]
     *                    function[view_finished_batch_for_ajax_view]
     *                    function[view_cancel_batch_for_ajax_view]
     *                    function[view_search_batch]
     *                    function[search_view_created_batch_for_ajax_view]
     *                    function[search_view_running_batch_for_ajax_view]
     *                    function[search_view_finished_batch_for_ajax_view]
     *                    function[search_view_cancel_batch_for_ajax_view]
     * 
     * Description      : used to get total number of batches
     * Parameter        : $person_id, $type( 1=running, 2=finished, 3=cancel, 4=created)
     * return           : total number of batch 
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 14-06-2016
     */

    function get_total_batch_status($person_id, $type) {
        $sql = "SELECT    
	count(*) AS total_batch
        FROM tbl_course_batch as b,
        tbl_course as c
	WHERE b.course_id = c.course_id
	AND c.person_id = '$person_id'
        AND course_batch_status= '$type'";
        return $this->db->query($sql)->row();
    }

    /*
     * Function         : get_batch_info
     * Used IN          : function[manage_organization_batch] controller[]                    
     * Description      : used to get batch info
     * Parameter        : $person_id, $type( 1=running, 2=finished, 3=cancel, 4=created)
     * return           : batch info
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 15-06-2016
     */

    function get_batch_info($person_id, $type) {
        $sql = "SELECT * FROM 
	tbl_course_batch as b,
	tbl_course as c,
        tbl_url as u
	WHERE b.course_id = c.course_id
	AND c.person_id = '$person_id'
        AND c.url_id= u.url_id
	AND b.course_batch_status = '$type' ORDER BY b.course_batch_id DESC, c.course_id DESC LIMIT 5";
        return $this->db->query($sql)->result();
    }

    /*
     * Function         : get_total_teacher_batch_status
     * Used IN          : function[manage_teacher_batch] 
     * Description      : used to get total number of batches of teacher
     * Parameter        : $resource_person_id, $type( 1=running, 2=finished, 3=cancel, 4=created)
     * return           : total number of batches
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 16-06-2016
     */

    function get_total_teacher_batch_status($resource_person_id, $type) {
        $sql = "SELECT count(*) AS total_batch
        FROM tbl_course_batch as b,
	tbl_course as c
	WHERE b.course_id = c.course_id
	AND c.resource_person_id = '$resource_person_id'
	AND b.course_batch_status = '$type'";
        return $this->db->query($sql)->row();
    }

    /*
     * Function         : get_resource_person_info
     * Used IN          : function[manage_organization_batch] controller[]
     *                    function[manage_teacher_batch] controller[] 
     *                    function[view_created_batch_for_ajax_view] controller[] 
     *                    function[view_running_batch_for_ajax_view] controller[]
     *                    function[view_finished_batch_for_ajax_view] controller[]
     *                    function[view_cancel_batch_for_ajax_view] controller[] 
     *                    function[view_search_batch] controller[] 
     *                    function[search_view_created_batch_for_ajax_view] controller[]
     *                    function[search_view_running_batch_for_ajax_view] controller[]
     *                    function[search_view_finished_batch_for_ajax_view] controller[] 
     *                    function[search_view_cancel_batch_for_ajax_view] controller[] 
     *                    function[] controller[]
     * Description      : used to get resource person info
     * Parameter        : $resource_person_id
     * return           : resource person info
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 18-06-2016
     */

    function get_resource_person_info($resource_person_id) {
        $sql = " SELECT * FROM 
	tbl_resource_person as rp,
	tbl_person as p,
	tbl_url as u
	WHERE rp.resource_person_id= '$resource_person_id'	
	AND rp.person_id = p.person_id
	AND p.url_id = u.url_id";
        return $this->db->query($sql)->row();
    }

    /*
     * Function         : get_teacher_batch_info
     * Used IN          : function[manage_teacher_batch] contoller[batch]
     * Description      : used to get teacher batch info
     * Parameter        : $resource_person_id, $type( 1=running, 2=finished, 3=cancel, 4=created)
     * return           : batch info
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 18-06-2016
     */

    function get_teacher_batch_info($resource_person_id, $type) {
        $sql = "SELECT * FROM 
	tbl_course_batch as b,
	tbl_course as c,
        tbl_url as u
	WHERE b.course_id = c.course_id
	AND c.resource_person_id = '$resource_person_id'
       	AND c.url_id = u.url_id
	AND b.course_batch_status = '$type' ORDER BY c.course_id DESC, b.course_batch_id DESC LIMIT 5";
        return $this->db->query($sql)->result();
    }

    /*
     * Function         : select_batch_for_student
     * Used IN          : function[assign_studen_for_batch] controller[]
     * Description      : used to select batch for student
     * Parameter        : $student_id
     * return           : student info
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 19-06-2016
     */

    function select_batch_for_student($student_id) {
        $sql = "SELECT * FROM 
	tbl_student as s,
	tbl_course as c,
	tbl_course_batch as b
	WHERE s.student_id = '$student_id'
	AND s.student_type_id = c.course_id
	AND c.course_id = b.course_id
       AND b.course_batch_status IN(1,4)";      // 1= running, 4=create
        return $this->db->query($sql)->result();
    }

    /*
     * Function         : update_assign_student_for_batch
     * Used IN          : function[update_student_in_tbl_course_batch]
     * Description      : used to update student in batch
     * Parameter        : none
     * return           : none
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 20-06-2016
     */

    function update_assign_student_for_batch() {
        $student_id = $this->input->post('studentID', TRUE);
        $course_batch_id = $this->input->post('batchID', TRUE);
        $result = $this->select_assign_student($course_batch_id);
        $student_id_array = explode(',', $result->student_id);
        if ($result->student_id != NULL) {
            if (in_array($student_id, $student_id_array)) {
                $data['student_id'] = $result;
            } else {
                $data['student_id'] = $result->student_id . ',' . $this->input->post('studentID', TRUE);
            }
        } else {
            $data['student_id'] = $this->input->post('studentID', TRUE);
        }
        $this->db->where('course_batch_id', $course_batch_id);
        $this->db->update('tbl_course_batch', $data);
    }

    /*
     * Function         : select_assign_student
     * Used IN          : function[update_assign_student_for_batch] model[batch.model]
     * Description      : used to select student from batch
     * Parameter        : $course_batch_id
     * return           : student info
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 21-06-2016
     */

    function select_assign_student($course_batch_id) {
        $sql = "SELECT student_id FROM tbl_course_batch WHERE course_batch_id='$course_batch_id'";
        return $this->db->query($sql)->row();
    }

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

    function select_checked_batch_info($student_id) {
        $sql = "SELECT * FROM tbl_course_batch
	WHERE FIND_IN_SET('$student_id', student_id)";
        return $this->db->query($sql)->row();
    }

    /*
     * Function         : select_assigned_batch_for_view
     * Used IN          : function[view_assigned_batch] controller[batch]
     * Description      : used to batch for batch
     * Parameter        : $student_id, $course_id
     * return           : batch info
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 22-06-2016
     */

    function select_assigned_batch_for_view($student_id, $course_id) {
        $sql = "SELECT * FROM tbl_course_batch 
	WHERE FIND_IN_SET('$student_id', student_id)
	AND course_id ='$course_id'";
        return $this->db->query($sql)->result();
    }

    /*
     * Function         : remove_student_from_assigned_batch 
     * Used IN          : function[remove_student]
     * Description      : used to remove student from batch
     * Parameter        : none
     * return           : none
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 23-06-2016 
     */

    function remove_student_from_assigned_batch() {
        $student_id = $this->input->post('studentID', TRUE);
        $batch_id = $this->input->post('batchID', TRUE);
        $sql = "UPDATE tbl_course_batch
        SET student_id = TRIM(BOTH ',' FROM REPLACE(CONCAT(',', `student_id`), ',$student_id', ''))
        WHERE FIND_IN_SET('$student_id', `student_id`)
        AND course_batch_id= '$batch_id'";
        $this->db->query($sql);
    }

    /*
     * Function         : get_total_checked
     * Used IN          : function[registered_student_list] controllers[user_course]
     * Description      : used to get total assigned batch
     * Parameter        : $student_id
     * return           : total number of assigned batch
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 24-06-2016
     */

    function get_total_checked($student_id) {
        $sql = "SELECT count(*) AS total_checked
	FROM tbl_course_batch
	WHERE FIND_IN_SET('$student_id', student_id)";
        return $this->db->query($sql)->row();
    }

    /*
     * Function         : select_batch_info
     * Used IN          : function[auto_update_course_batch_type]
     * Description      : used to get batch info 
     * Parameter        : none
     * return           : batch info
     * redirect         : none
     * Developed By     : Tania Akter
     * Updated By       : Md Mashqur Ul Alam [18-02-17]
     * Date             : 25-06-2016
     */

    function select_batch_info() {        
        $restricted_batch_status = array(3,5);
        $result = $this->db->select('*')->from('tbl_course_batch')
                            ->where_not_in('course_batch_status', $restricted_batch_status)
                            ->get()->result();        
        return $result;
    }

    /*
     * Function         : update_batch_type
     * Used IN          : function[auto_update_course_batch_type]
     * Description      : used to update batch type
     * Parameter        : $course_batch_id, $type( 1=running, 2=finished, 3=cancel, 4=created)
     * return           : none
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 25-06-2016
     */

    function update_batch_type($course_batch_id, $type) {
        $data['course_batch_status'] = $type;
        $this->db->where('course_batch_id', $course_batch_id);
        $this->db->update('tbl_course_batch', $data);
    }

    /*
     * Function         : count_number_of_student_in_batch
     * Used IN          : function[manage_organization_batch]
     *                    function[manage_teacher_batch] controller[]
     *                    function[view_created_batch_for_ajax_view] controller[]
     *                    function[view_running_batch_for_ajax_view] controller[]
     *                    function[view_finished_batch_for_ajax_view] controller[]
     *                    function[search_view_cancel_batch_for_ajax_view] controller[]
     *                    function[] controller[]
     *                    function[] controller[]
     *                    function[] controller[]
     *                    function[] controller[]
     *                    function[] controller[]
     *                    function[] controller[]
     *                    function[] controller[]
     *                    function[] controller[]
     *                    function[] controller[]
     *                    function[] controller[]
     *                    function[] controller[]

     * Description      : count number of student in batch
     * Parameter        : $course_batch_id
     * return           : none
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 26-06-2016
     */

    function count_number_of_student_in_batch($course_batch_id) {
        $sql = "SELECT student_id FROM tbl_course_batch WHERE course_batch_id= '$course_batch_id'";
        return $this->db->query($sql)->row();
    }

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

//    function count_batch_by_course_id($course_id) {
//        $sql = "SELECT count(*) AS course_count
//	FROM tbl_course_batch
//	WHERE course_id= '$course_id'";
//        return $this->db->query($sql)->row();
//    }

    /*
     * Function         : select_batch_by_course_id
     * Used IN          : function[show_batch_no] model[batch_model]
     * Description      : used to select batch by course id
     * Parameter        : $course_id
     * return           : none
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 26-06-2916
     */

    function select_batch_by_course_id($course_id) {
        $sql = "	SELECT *
	FROM tbl_course_batch
	WHERE course_id= '$course_id' ORDER BY course_batch_id ASC ";
        return $this->db->query($sql)->result();
    }

    /*
     * Function         : show_batch_no 
     * Used IN          : user\batch\ajax_page\search_created_batch_view.php , 
     *                    user\batch\ajax_page\view_cancel_batch.php
      user\batch\ajax_page\view_created_batch.php
      user\batch\ajax_page\view_finished_batch.php
      user\batch\ajax_page\view_running_batch.php
      user\batch\view_course_batch.php
     * Description      : show batch no
     * Parameter        : $course_id, $course_batch_id
     * return           : batch no
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 25-06-2016
     */

    function show_batch_no($course_id, $course_batch_id) {
        $data['batch_info'] = $this->select_batch_by_course_id($course_id);
        $batch_id = array();
        foreach ($data['batch_info'] as $batches) {
            $batch_id[] = $batches->course_batch_id;
        }
        return (array_search($course_batch_id, $batch_id) + 1);
    }

    /*
     * Function         : cancel_batch_by_course_batch_id
     * Used IN          : function[cancel_batch]
     * Description      : used to cancek batch
     * Parameter        : $course_batch_id
     * return           : none
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 27-06-2016s
     */

    function cancel_batch_by_course_batch_id($course_batch_id) {
        $data['course_batch_status'] = 3;                          //3= cancel batch
        $this->db->where('course_batch_id', $course_batch_id);
        $this->db->update('tbl_course_batch', $data);
    }

    /*
     * Function         : get_batch_information 
     * Used IN          : function[edit_batch] 
     *                    function[view_assigned_student]
     * Description      : used to get batch info
     * Parameter        : $course_batch_id
     * return           : none
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 28-06-2016
     */

    function get_batch_information($course_batch_id) {
        $sql = "SELECT * FROM tbl_course_batch WHERE course_batch_id = '$course_batch_id' ";
        return $this->db->query($sql)->row();
    }
    
    //tania date:30-09-2016
    function get_resourse_info_by_course_id($course_id){
        $sql = "SELECT * FROM
	tbl_course as c,
	tbl_resource_person as r,
	tbl_person as p
	WHERE c.course_id = '$course_id'
	AND c.resource_person_id = r.resource_person_id
	AND r.person_id = p.person_id";
        return $this->db->query($sql)->row();
    }

    /*
     * Function         : update_batch_information 
     * Used IN          : function[edit_batch] controller[batch]
     * Description      : used to update batch
     * Parameter        : $course_batch_id, $course_batch_status
     * return           : none
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 27-06-2016
     */

    function update_batch_information($course_batch_id, $course_batch_status) {
        $batch_info['course_id'] = $this->input->post('course', TRUE);
        $batch_info['course_batch_name'] = $this->input->post('course_batch_name', TRUE);
        $batch_info['course_batch_start_date'] = $this->input->post('course_batch_start_date', TRUE);
        $batch_info['course_batch_finish_date'] = $this->input->post('course_batch_finish_date', TRUE);
        $batch_info['course_batch_status'] = $course_batch_status;
        $this->db->where('course_batch_id', $course_batch_id);
        $this->db->update('tbl_course_batch', $batch_info);
    }

    /*
     * Function         : delete_batch_by_course_batch_id
     * Used IN          : function[delete_batch]
     * Description      : used to delete batch
     * Parameter        : $course_batch_id
     * return           : none
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 28-06-2016
     */

    function delete_batch_by_course_batch_id($course_batch_id) {
        $this->db->where('course_batch_id', $course_batch_id);
        $this->db->delete('tbl_course_batch');
    }

    /*
     * Function         : get_course_registarted_student
     * Used IN          : function[manage_organization_batch] controller[batch]
     *                    function[manage_teacher_batch] controller
     *                    function[view_created_batch_for_ajax_view]
     *                    function[view_running_batch_for_ajax_view]
     *                    function[view_search_batch]
     * 
     * Description      : get student info who are registrated in a course
     * Parameter        : $course_id
     * return           : none
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 29-06-2016
     */

    function get_course_registarted_student($course_id) {
        $sql = "SELECT * FROM tbl_student WHERE student_type_id='$course_id'";
        return $this->db->query($sql)->result();
    }

    /*
     * Function         : get_batch_assigned_student_list
     * Used IN          : function[view_assigned_student] controller[]
     * Description      : used to get student info who are assigned in batch
     * Parameter        : $student_id
     * return           : student info
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 28-06-2016
     */

    function get_batch_assigned_student_list($student_id) {
        $sql = "SELECT * 
                FROM tbl_student AS s,
                tbl_person AS p
                WHERE s.student_id IN ($student_id)
                AND s.person_id = p.person_id	";
        return $this->db->query($sql)->result();
    }

    /*
     * Function         : get_batch_info_pagination 
     * Used IN          : function[view_created_batch_for_ajax_view] controller[]
      function[view_running_batch_for_ajax_view] controller[]
      function[view_finished_batch_for_ajax_view] controller[]
      function[view_cancel_batch_for_ajax_view] controller[]

     * Description      : used to get batch by pagination
     * Parameter        : $person_id, $type, $limit
     * return           : BATCH INFO
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 29-06-2016
     */

    function get_batch_info_pagination($person_id, $type, $limit) {
        $sql = "SELECT * FROM 
	tbl_course_batch as b,
	tbl_course as c,
        tbl_url as u
	WHERE b.course_id = c.course_id
	AND c.person_id = '$person_id'
        AND c.url_id= u.url_id
	AND b.course_batch_status = '$type' ORDER BY b.course_batch_id DESC, c.course_id DESC LIMIT $limit, 5";
        return $this->db->query($sql)->result();
    }

    /*
     * Function         : get_teacher_batch_info_pagination
     * Used IN          : function[view_created_batch_for_ajax_view] controller[]
     *                    function[view_running_batch_for_ajax_view] controller[]  
     *                    function[view_finished_batch_for_ajax_view] controller[] 
     *                    function[view_cancel_batch_for_ajax_view] controller[] 
     * 
     * Description      : used to get batch by pagination
     * Parameter        : $resource_person_id, $type, $limit
     * return           : batch info
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 29-06-2016
     */

    function get_teacher_batch_info_pagination($resource_person_id, $type, $limit) {
        $sql = "SELECT * FROM 
	tbl_course_batch as b,
	tbl_course as c,
        tbl_url as u
	WHERE b.course_id = c.course_id
	AND c.resource_person_id = '$resource_person_id'
       	AND c.url_id = u.url_id
	AND b.course_batch_status = '$type' ORDER BY c.course_id DESC, b.course_batch_id DESC LIMIT $limit, 5";
        return $this->db->query($sql)->result();
    }

    /*
     * Function         : get_searched_batch
     * Used IN          : function[view_search_batch] controller[]
      function[search_view_created_batch_for_ajax_view] controller[]
      function[search_view_running_batch_for_ajax_view]
      function[search_view_finished_batch_for_ajax_view]
      function[search_view_cancel_batch_for_ajax_view]
     * Description      : used to get batch by search
     * Parameter        : $person_id, $type, $search_values
     * return           : none
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 29-06-2016
     */

    function get_searched_batch($person_id, $type, $search_value) {
        $sql = "SELECT * FROM 
	tbl_course_batch as b,
	tbl_course as c,
        tbl_url as u
	WHERE b.course_id = c.course_id
	AND c.person_id = '$person_id'
	AND c.url_id= u.url_id
	AND b.course_batch_status = '$type'
	AND (b.course_batch_name LIKE '%$search_value%' OR c.course_name LIKE '%$search_value%')
        ORDER BY c.course_id DESC, b.course_batch_id DESC LIMIT 5";
        return $this->db->query($sql)->result();
    }

    function get_total_batch_by_search_value($person_id, $type, $search_value) {
        $sql = "SELECT count(*) as total_batch
        FROM 
	tbl_course_batch as b,
	tbl_course as c,
        tbl_url as u
	WHERE b.course_id = c.course_id
	AND c.person_id = '$person_id'
	AND c.url_id= u.url_id
	AND b.course_batch_status = '$type'
	AND (b.course_batch_name LIKE '%$search_value%' 
	OR c.course_name LIKE '%$search_value%')";
        return $this->db->query($sql)->row();
    }

    /*
     * Function         : get_searched_teacher_batch 
     * Used IN          : function[view_search_batch] controller[batch]
     *                    function[search_view_created_batch_for_ajax_view] controller[batch]
     *                    function[search_view_running_batch_for_ajax_view] controller[batch]
     *                    function[search_view_finished_batch_for_ajax_view] controller[batch]
     *                    function[search_view_cancel_batch_for_ajax_view] controller[batch]
     * Description      : used to get batch by search
     * Parameter        : $resource_person_id, 
     *                    $type, 
     *                    $search_value
     * return           : batch info
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 29-06-2016
     */

    function get_searched_teacher_batch($resource_person_id, $type, $search_value) {
        $sql = "SELECT * FROM 
	tbl_course_batch as b,
	tbl_course as c,
        tbl_url as u
	WHERE b.course_id = c.course_id
	AND c.resource_person_id = '$resource_person_id'
       	AND c.url_id = u.url_id
	AND b.course_batch_status = '$type' AND (b.course_batch_name LIKE '%$search_value%' OR c.course_name LIKE '%$search_value%')
        ORDER BY c.course_id DESC, b.course_batch_id DESC LIMIT 5";
        return $this->db->query($sql)->result();
    }

    function get_total_teacher_batch_by_search_value($resource_person_id, $type, $search_value) {
        $sql = "SELECT count(*) as total_batch
        FROM 
	tbl_course_batch as b,
	tbl_course as c,
        tbl_url as u
	WHERE b.course_id = c.course_id
	AND c.resource_person_id = '$resource_person_id'
       	AND c.url_id = u.url_id
	AND b.course_batch_status = '$type' AND (b.course_batch_name LIKE '%$search_value%' OR c.course_name LIKE '%$search_value%')";
        return $this->db->query($sql)->row();
    }

    /* --------------------- start 06-09-2016--------------------- */

    /*
     * Function         : get_total_batch_status_course_wise 
     * Used IN          : function[view_organization_batch_status] controller[batch]
     *                    
     * Description      : used to get total course wise batch type
     * Parameter        : $person_id, $type, $course_id 
     * return           : batch info
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 06-09-2016
     */

    function get_total_batch_status_course_wise($person_id, $type, $course_id) {
        $sql = "SELECT count(*) AS total_batch
        FROM tbl_course_batch as b,
        tbl_course as c
	WHERE b.course_id = c.course_id
	AND c.person_id = '$person_id'
        AND b.course_batch_status= '$type'
        AND b.course_id = '$course_id'";
        return $this->db->query($sql)->row();
    }

    function get_batch_info_course_wise($person_id, $type, $course_id) {
        $sql = "SELECT * FROM 
	tbl_course_batch as b,
	tbl_course as c,
        tbl_url as u
	WHERE b.course_id = c.course_id
        AND b.course_id = '$course_id'
	AND c.person_id = '$person_id'
        AND c.url_id= u.url_id
	AND b.course_batch_status = '$type' ORDER BY c.course_id DESC, b.course_batch_id DESC LIMIT 5";
        return $this->db->query($sql)->result();
    }

    /*
     * Function         : get_total_teacher_batch_status_course_wise
     * Used IN          : function[manage_teacher_batch] 
     * Description      : used to get total number of batches of teacher
     * Parameter        : $resource_person_id, $type( 1=running, 2=finished, 3=cancel, 4=created)
     * return           : total number of batches
     * redirect         : none
     * Developed By     : Tania Akter
     * Date             : 16-06-2016
     */

    function get_total_teacher_batch_status_course_wise($resource_person_id, $type, $course_id) {
        $sql = "SELECT count(*) AS total_batch
        FROM tbl_course_batch as b,
	tbl_course as c
	WHERE b.course_id = c.course_id
	AND c.resource_person_id = '$resource_person_id'
	AND b.course_batch_status = '$type'
        AND b.course_id = '$course_id'";
        return $this->db->query($sql)->row();
    }

    function get_teacher_batch_info_course_wise($resource_person_id, $type, $course_id) {
        $sql = "SELECT * FROM 
	tbl_course_batch as b,
	tbl_course as c,
        tbl_url as u
	WHERE b.course_id = c.course_id
	AND c.resource_person_id = '$resource_person_id'
       	AND c.url_id = u.url_id
        AND b.course_id='$course_id'
	AND b.course_batch_status = '$type' ORDER BY c.course_id DESC, b.course_batch_id DESC LIMIT 5";
        return $this->db->query($sql)->result();
    }

    function get_batch_info_pagination_course($person_id, $type, $limit, $course_id) {
        $sql = "SELECT * FROM 
	tbl_course_batch as b,
	tbl_course as c,
        tbl_url as u
	WHERE b.course_id = c.course_id
	AND c.person_id = '$person_id'
        AND c.url_id= u.url_id
        AND b.course_id = '$course_id'
	AND b.course_batch_status = '$type' ORDER BY c.course_id DESC, b.course_batch_id DESC LIMIT $limit, 5";
        return $this->db->query($sql)->result();
    }

    function get_total_batch_status_course($person_id, $type, $course_id) {
        $sql = "SELECT    
	count(*) AS total_batch
        FROM tbl_course_batch as b,
        tbl_course as c
	WHERE b.course_id = c.course_id
	AND c.person_id = '$person_id'
        AND b.course_id = '$course_id'
        AND course_batch_status= '$type'";
        return $this->db->query($sql)->row();
    }

    function get_total_teacher_batch_status_course($resource_person_id, $type, $course_id) {
        $sql = "SELECT count(*) AS total_batch
        FROM tbl_course_batch as b,
	tbl_course as c
	WHERE b.course_id = c.course_id
	AND c.resource_person_id = '$resource_person_id'
        AND b.course_id = '$course_id'
	AND b.course_batch_status = '$type'";
        return $this->db->query($sql)->row();
    }

    function get_teacher_batch_info_pagination_course($resource_person_id, $type, $limit, $course_id) {
        $sql = "SELECT * FROM 
	tbl_course_batch as b,
	tbl_course as c,
        tbl_url as u
	WHERE b.course_id = c.course_id
	AND c.resource_person_id = '$resource_person_id'
       	AND c.url_id = u.url_id
        AND b.course_id = '$course_id'
	AND b.course_batch_status = '$type' ORDER BY c.course_id DESC, b.course_batch_id DESC LIMIT $limit, 5";
        return $this->db->query($sql)->result();
    }

    function get_batch_info_course_id($course_id) {
        $sql = "SELECT * FROM 
	tbl_course_batch as b
	WHERE b.course_id = '$course_id'";
        return $this->db->query($sql)->row();
    }

    function remove_assigned_student_from_batch() {
        //$student_id = $this->input->post('student_id', TRUE);
        $student_id = $this->input->post('studentID', TRUE);
        $batch_id = $this->input->post('batchID', TRUE);
        $sql = "UPDATE tbl_course_batch
        SET student_id = TRIM(BOTH ',' FROM REPLACE(CONCAT(',', `student_id`), ',$student_id', ''))
        WHERE FIND_IN_SET('$student_id', `student_id`)
        AND course_batch_id= '$batch_id'";
        $this->db->query($sql);
    }

    function get_student_count_number($course_batch_id) {
        $sql = "SELECT * FROM tbl_course_batch
	WHERE course_batch_id= '$course_batch_id'";
        return $this->db->query($sql)->row;
    }

    function get_course_url($course_batch_id) {
        $sql = "SELECT * FROM 
	tbl_course_batch as b,
	tbl_course as c,
	tbl_url as u
	WHERE b.course_batch_id = '$course_batch_id'
	AND b.course_id = c.course_id
	AND c.url_id = u.url_id";
        return $this->db->query($sql)->row();
    }

    function get_unassigned_student($student_id) {
        $sql = "SELECT * FROM tbl_course_batch
	WHERE ! FIND_IN_SET('$student_id', student_id)";
        return $this->db->query($sql)->row();
    }

    function get_assigned_student($course_id) {
        $sql = "SELECT GROUP_CONCAT(b.student_id) AS student_id
        FROM tbl_course_batch AS b
	WHERE  b.course_id ='$course_id'
	AND b.student_id !=''";
        return $this->db->query($sql)->row()->student_id;
    }

    function get_register_student($course_id) {
        $sql = "SELECT GROUP_CONCAT(s.student_id) AS register_student_id 
        FROM 
	tbl_student as s
	WHERE s.student_type_id = '$course_id'";
        return $this->db->query($sql)->row()->register_student_id;
    }
/*------------------------- start unassigned student------------------*/
//21-09-2016 tania
    function get_unassigned_students($data, $date, $page_number, $total_count) {
        //$data= array();
        $data['unassigned_student_info'] = $this->all_unassigned_student_list($data['unasigned_student'], $date, $page_number);
        $params = array('url' => '', 'div_id' => 'unassignStudentPagination', 'total_row' => $total_count, 'per_page' => 10, 'current_page' => $page_number, 'total_link' => 10);
        $data['unassigned_student_paginzation_link'] = $this->tt_pagination->view_pagination_link($params);
        return $data;
    }
    function all_unassigned_student_list($unasigned_student, $date, $page_number) {
        $limit = ($page_number - 1) * 10;
        $this->db->select('*')
                ->from('tbl_student as s')
                ->join('tbl_person AS p', 's.person_id = p.person_id')
                ->join('tbl_url AS u', 'p.url_id = u.url_id')
                ->where_in('s.student_id', $unasigned_student);
        if ($date) {
            $this->db->where("DATE(s.register_date_time) BETWEEN '$date[sess_start_date]' AND '$date[sess_end_date]'");
        }
        return $this->db->limit(10, $limit)->get()->result();
    }

    function get_unassigned_student_list($unasigned_student) {
        return $this->db->select('*')
                        ->from('tbl_student as s')
                        ->join('tbl_person AS p', 's.person_id = p.person_id')
                        ->join('tbl_url AS u', 'p.url_id = u.url_id')
                        ->where_in('s.student_id', $unasigned_student)->get()->result();
    }

    function get_total_unassigned_student_by_search($unasigned_student, $date) {
        $this->db->select('*')
                ->from('tbl_student as s')
                ->join('tbl_person AS p', 's.person_id = p.person_id')
                ->join('tbl_url AS u', 'p.url_id = u.url_id')
                ->where_in('s.student_id', $unasigned_student);
        if ($date) {
            $this->db->where("DATE(s.register_date_time) BETWEEN '$date[sess_start_date]' AND '$date[sess_end_date]'");
        }
        return $this->db->get()->result();
    }
/*------------------------- end  unassigned student 24-09-2016------------------*/
    function make_student_not_eligible($course_id, $student_id){
        $data['student_eligibility'] = 2;
        $this->db->where('student_type_id', $course_id);
        $this->db->where('student_id', $student_id);
        $this->db->update('tbl_student', $data);
    }
    function make_student_eligible($course_id, $student_id){
        $data['student_eligibility'] = 1;
        $this->db->where('student_type_id', $course_id);
        $this->db->where('student_id', $student_id);
        $this->db->update('tbl_student', $data);
    }
    
    function get_all_course_not_eligible_student($data, $page_number) {
        $limit = ($page_number - 1) * 10;
        $data['not_eligible_student'] = $this->get_all_not_eligible_student_list(1, $data['course_info']->course_id, $limit);
        $data['total_not_eligible_student'] = $this->get_total_not_eligible_student(1, $data['course_info']->course_id);
        $params = array('url' => '', 'div_id' => 'notEligiblePagination', 'total_row' => $data['total_not_eligible_student'], 'per_page' => 10, 'current_page' => $page_number, 'total_link' => 10);
        $data['not_eligible_student_paginzation_link'] = $this->tt_pagination->view_pagination_link($params);
        return $data;
    }
    
    function get_all_not_eligible_student_list($student_type, $student_type_id, $limit) {
        $this->db->select('*')
                ->from('tbl_student AS s')
                ->join('tbl_person AS p', 's.person_id = p.person_id')
                ->join('tbl_url AS u', 'p.url_id = u.url_id')
                ->where('s.student_eligibility', 2)
                ->where(Array('s.student_type' => $student_type, 's.student_type_id' => $student_type_id));
        return $this->db->limit(10, $limit)->get()->result();
    }
    
    function get_total_not_eligible_student($student_type, $student_type_id){
        $this->db->select('*')
                ->from('tbl_student')
                ->where('student_eligibility', 2)
                ->where(Array('student_type' => $student_type, 'student_type_id' => $student_type_id));
        return $this->db->count_all_results();   
    }
    
    
    function get_all_not_eligible_student_by_search($student_name, $data_info, $page_number){
    $limit = ($page_number - 1) * 10;
        $data['not_eligible_student'] = $this->get_all_not_eligible_student_list_search(1, $data_info->course_id, $limit, $student_name);
        $data['total_not_eligible_student'] = $this->get_total_not_eligible_student_search(1, $data_info->course_id, $student_name);
        $params = array('url' => '', 'div_id' => 'notEligiblePagination', 'total_row' => $data['total_not_eligible_student'], 'per_page' => 10, 'current_page' => $page_number, 'total_link' => 10);
        $data['not_eligible_student_paginzation_link'] = $this->tt_pagination->view_pagination_link($params);
        return $data;    
    }
    
    function get_all_not_eligible_student_list_search($student_type, $student_type_id, $limit, $student_name){
     $sql = "SELECT * FROM tbl_student as s,
	tbl_person as p,
	tbl_url as u
	WHERE s.student_type ='$student_type'
	AND s.student_eligibility = '2'
	AND s.student_type_id = '$student_type_id'
	AND s.person_id = p.person_id
	AND p.person_first_name LIKE '%$student_name%'
	AND p.url_id = u.url_id LIMIT $limit,5";
    return $this->db->query($sql)->result();      
    }
    
    function get_total_not_eligible_student_search($student_type, $student_type_id, $student_name){
     $sql = "SELECT count(*) as total_not_eligible_student
        FROM tbl_student  as s,
	tbl_person as p,
	tbl_url as u
	WHERE s.student_type ='$student_type'
	AND s.student_eligibility = '2'
	AND s.student_type_id = '$student_type_id'
	AND s.person_id = p.person_id
	AND p.person_first_name LIKE '%$student_name%'
	AND p.url_id = u.url_id";  
     return $this->db->query($sql)->row()->total_not_eligible_student;
    }
    
    
    /**
     * @Function            : get_student_detail_by_person_id
     * @Used IN             : get_batch_data
     * @Description         : get student details from database
     * @Developed By        : MD Mashqur Ul Alam
     * @Start DateTime      : 20-02-17
     * @Parameter           : $person_id
     * @Status              : Available       
     * */
    function get_student_detail_by_person_id($person_id){
        $student_detail = $this->db->select('student_type_id AS course_id, student_id')
                                   ->from('tbl_student')
                                   ->where('person_id', $person_id)
                                   ->where('student_eligibility', 1)
                                   ->get()->result();
        return $student_detail;
    }    
    
    /**
     * @Function            : get_learner_batch_detail
     * @Used IN             : get_batch_data
     * @Description         : get student details from database
     * @Developed By        : MD Mashqur Ul Alam
     * @Start DateTime      : 20-02-17
     * @Parameter           : $person_id
     * @Status              : Available       
     * */
    function get_learner_batch_detail($course_id, $student_id){        
        $all_batch = $this->db->select('course_batch_id, course_batch_name, course_batch_status')
                           ->from('tbl_course_batch')
                           ->where('course_id', $course_id)
                           ->where("FIND_IN_SET('".$student_id."', student_id)")
                           ->get()->result();
        return $all_batch;
    }    
    
     /*
     * @Function            : get_learner_course_detail
     * @Used IN             : get_batch_data
     * @Description         : get course details from database
     * @Developed By        : MD Mashqur Ul Alam
     * @Start DateTime      : 20-02-17
     * @Parameter           : $course_id
     * @Status              : Available       
     */
    function get_learner_course_detail($course_id){
        $course_detail = $this->db->select('tc.course_name, tc.person_id AS course_by_id, tc.resource_person_id, tu.url')
                                  ->from('tbl_course AS tc')
                                  ->join('tbl_url AS tu', 'tu.url_id = tc.url_id')
                                  ->where('course_id', $course_id)
                                  ->get()->row();
        return $course_detail;
        
    }    
    
    /*
     * @Function            : get_resource_person_detail_by_id
     * @Used IN             : get_batch_data
     * @Description         : get resource person details from database
     * @Developed By        : MD Mashqur Ul Alam
     * @Start DateTime      : 20-02-17
     * @Parameter           : $resource_person_id
     * @Status              : Available       
     */
    function get_resource_person_detail_by_id($resource_person_id){
        $name_dtl = $this->db->select('tp.person_first_name, tp.person_last_name, tu.url')
                             ->from('tbl_person AS tp')
                             ->join('tbl_resource_person AS trp','trp.person_id = tp.person_id' )
                             ->join('tbl_url AS tu', 'tu.url_id = tp.url_id')
                             ->where('resource_person_id', $resource_person_id)
                             ->get()->row();
        return $name_dtl;
    }
    
     /*
     * @Function            : get_course_by_detail
     * @Used IN             : get_batch_data
     * @Description         : get resource course details from database
     * @Developed By        : MD Mashqur Ul Alam
     * @Start DateTime      : 20-02-17
     * @Parameter           : $person_id
     * @Status              : Available       
     */
    function get_course_by_detail($person_id){
        $name_dtl = $this->db->select('tp.person_first_name, tp.person_last_name, tu.url')
                             ->from('tbl_person AS tp')
                             ->join('tbl_url AS tu', 'tu.url_id = tp.url_id')
                             ->where('person_id', $person_id)
                             ->get()->row();
        return $name_dtl;
    }    
    
    /*
     * @Function            : get_course_registered_student
     * @Used IN             : get_batch_data
     * @Description         : counts the number of registered student in a course
     * @Developed By        : MD Mashqur Ul Alam
     * @Start DateTime      : 20-02-17
     * @Parameter           : $course_id
     * @Status              : Available       
     */
    function get_course_registered_student($course_id){
        $student_count = $this->db->where('student_type_id', $course_id)
                                  ->count_all_results('tbl_student');
        return $student_count;
    }
    
    
    /*
     * @Function            : get_certificate_student_roll
     * @Used IN             : get_batch_data
     * @Description         : Get the student certificate data
     * @Developed By        : MD Mashqur Ul Alam
     * @Start DateTime      : 01-03-17
     * @Parameter           : $person_id, $course_batch_id
     * @Status              : Available       
     */
    function get_certificate_student_roll($person_id, $course_batch_id){
        $result = $this->db->select('student_roll_number')->from('tbl_student_certificate')
                           ->where('person_id', $person_id)->where('course_batch_id', $course_batch_id)
                           ->get()->row();
        return $result;
    }

}
