<?php

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

class Super_admin_batch_model extends CI_Model {
    
    /**
     * @Function            : select_all_active_courses 
     * @Modified By         : Md. Delwar Hossain 
     * @DateTime            : 30-5-2017 2:30 PM
     * @Parameter           : $person_role
     * @Status              : Available        
     * */
    function select_all_active_courses() {
//        $sql = "SELECT * FROM tbl_course as c
//            WHERE c.course_status = '1'
//            OR c.course_status = '4'";
//        return $this->db->query($sql)->result();
        $this->db->select('*')
                ->from('tbl_course')
                ->where('course_status','1')
                ->or_where('course_status','4');
        return $this->db->get()->result();
    }
    
    /**
     * @Function            : select_active_courses 
     * @Description         : used in search section of admin batch ajax call
     * @Modified By         : Md. Delwar Hossain 
     * @DateTime            : 29-5-2017 3:00 PM
     * @Parameter           : $person_role
     * @Status              : Available        
     * */
    function select_active_courses($person_role) {
//        $sql = "SELECT * FROM 
//	tbl_course as c,
//	tbl_person as p
//	WHERE c.person_id = p.person_id	
//        AND p.person_role = '$person_role'
//	AND c.course_status = '1'
//	OR c.course_status = '4'";
//        return $this->db->query($sql)->result();
        
        $this->db->select('*')
                ->from('tbl_person AS p')
                ->join('tbl_course AS c','p.person_id = c.person_id')
                ->where('p.person_role', $person_role)
                ->where('c.course_status','1')
                ->or_where('c.course_status','4');
        return $this->db->get()->result();
    }
    
    /**
     * @Function            : select_active_courses_search 
     * @Description         : used in search section of admin batch ajax call
     * @Modified By         : Md. Delwar Hossain 
     * @DateTime            : 30-5-2017 3:10 PM
     * @Parameter           : $person_role, $person_name
     * @Status              : Available        
     * */
    function select_active_courses_search($person_role, $person_name){
//     $sql = "SELECT * FROM 
//	tbl_course as c,
//	tbl_person as p
//	WHERE c.person_id = p.person_id	
//        AND p.person_role = '$person_role'
//        AND p.person_first_name LIKE '%$person_name%'
//	AND c.course_status = '1'
//	OR c.course_status = '4' " ;
//        return $this->db->query($sql)->result();
        
        $this->db->select('*')
                ->from('tbl_person AS p')
                ->join('tbl_course AS c','p.person_id = c.person_id')
                ->like('p.person_first_name', $person_name, 'both')
                ->where('p.person_role', $person_role)
                ->where('c.course_status','1')
                ->or_where('c.course_status','4');
        return $this->db->get()->result();
    }
    
    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() {
        $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($course_batch_status){
       $sql = "SELECT    
	count(*) AS total_batch
        FROM tbl_course_batch as b
	WHERE b.course_batch_status = '$course_batch_status'";
       return $this->db->query($sql)->row();
   }
   
   function get_batch_info($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.url_id= u.url_id
	AND b.course_batch_status = '$type' ORDER BY b.course_batch_id DESC, c.course_id DESC LIMIT 5";    //limit dite hobe
        return $this->db->query($sql)->result();
   }
   
   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_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 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 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 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 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();
    }
    
    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 get_creator_info_by_course_id($course_id){
     $sql = "SELECT * FROM
	tbl_course as c,
	tbl_person as p
	WHERE c.course_id = '$course_id'
	AND c.person_id = p.person_id"; 
     return $this->db->query($sql)->row();
    }
    
    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 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_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 get_course_information_with_url_by_course_url($course_url) {
        $sql = "SELECT *, c.person_id AS course_creator_id 
                FROM tbl_course AS c,
                     tbl_url AS u
                WHERE c.url_id = u.url_id
                AND u.url = '$course_url'";
        return $this->db->query($sql)->row();
    }
    
     function view_course_register_student_info($data, $date_data, $page_number) {
        $limit = ($page_number - 1) * 10;
        $data['course_register_student'] = $this->get_all_student_list_by_course_id(1, $data['course_info']->course_id, $date_data, $limit);
        $data['total_course_register_student'] = $this->get_total_student_by_course_id(1, $data['course_info']->course_id, $date_data);

        $params = array('url' => '', 'div_id' => 'registerCourseStudentPagination', 'total_row' => $data['total_course_register_student'], 'per_page' => 10, 'current_page' => $page_number, 'total_link' => 10);
        $data['register_student_paginzation_admin_link'] = $this->tt_pagination->view_pagination_link($params);
        return $data;
    }
    
    function get_all_student_list_by_course_id($student_type, $student_type_id, $date, $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', 1)
                ->where(Array('s.student_type' => $student_type, 's.student_type_id' => $student_type_id));
        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_total_student_by_course_id($student_type, $student_type_id, $date) {
        $this->db->select('*')
                ->from('tbl_student')
                ->where('student_eligibility', 1)
                ->where(Array('student_type' => $student_type, 'student_type_id' => $student_type_id));
        if ($date) {
            $this->db->where("DATE(register_date_time) BETWEEN '$date[sess_start_date]' AND '$date[sess_end_date]'");
        }
        return $this->db->count_all_results();
    }
    
    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            : batch_pagination 
     * @Description         : Used in batch_pagination() function
     * @Updated By          : Md Mashqur Ul Alam [09-02-17] 
     * @Parameter           : Null
     * @Return              : NULL    
     * @Status              : Available        
     * */
    function get_batch_info_admin_pagination($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.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";    //limit dite hobe
        return $this->db->query($sql)->result();
   }
   
        /**
     * @Function            : get_total_batch_by_course 
     * @Description         : view_batch_by_course [super_admin_batch - controller]
     * @Updated By          : Md Mashqur Ul Alam [09-02-17] 
     * @Parameter           : $course_id, $course_batch_status
     * @Return              : NULL    
     * @Status              : Available        
     * */
   public function get_total_batch_by_course($course_id, $batch_status){
        $result = $this->db->where('course_batch_status', $batch_status)
                           ->where('course_id', $course_id)
                           ->count_all_results('tbl_course_batch');
        return $result;
   }
   
       /**
     * @Function            : get_batch_info_by_course 
     * @Description         : view_batch_by_course [super_admin_batch - controller]
     * @Updated By          : Md Mashqur Ul Alam [09-02-17] 
     * @Parameter           : $course_id, $course_batch_status
     * @Return              : NULL    
     * @Status              : Available        
     * */
   public function get_batch_info_by_course($course_id, $course_batch_status){
       $result = $this->db->select('*')
                          ->from('tbl_course_batch AS b')
                          ->join('tbl_course AS c', 'c.course_id = b.course_id')
                          ->join('tbl_url AS u', 'u.url_id = c.url_id')
                          ->where('b.course_id', $course_id)
                          ->where('b.course_batch_status', $course_batch_status)
                          ->order_by('b.course_batch_id', 'DESC')
                          ->limit(5)
                          ->get()->result();
       return $result;
   }
   
   
   public function pagination_get_batch_info_by_course($limit, $course_id, $course_batch_status){
       $result = $this->db->select('*')
                          ->from('tbl_course_batch AS b')
                          ->join('tbl_course AS c', 'c.course_id = b.course_id')
                          ->join('tbl_url AS u', 'u.url_id = c.url_id')
                          ->where('b.course_id', $course_id)
                          ->where('b.course_batch_status', $course_batch_status)
                          ->order_by('b.course_batch_id', 'DESC')
                          ->limit($limit, 5)
                          ->get()->result();
       return $result;
   }
}
