<?php defined("BASEPATH")or exit("NO DIRECT ACCESS IS ALLOWED"); ?>
<?php

class StudentModel extends CI_Model{
    protected $limit, $offset;
  public function __construct(){
    parent::__construct();
    date_default_timezone_set('Asia/Kolkata');
    //error_reporting(-1);
   // SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
    ///echo $this->db->last_query();die;
    // echo "<pre>";print_r($query->row());die;
    /* select DATE_FORMAT(class_date,"%d-%M-%Y") from class_mgmt where class_date > DATE_SUB( NOW( ) , INTERVAL 31 DAY ) */
    // date_default_timezone_set('Asia/Kolkata');
    //$config['time_reference'] = 'gmt';# Default should be GMT
    //$this->db->simple_query('SET SESSION group_concat_max_len=100000');
    $this->class_status      = array('create','modify');
  }
  public function selectrecordsforview(){
	$query = $this->db->get('quiz');
	return $query->result();
  }
  public function dataforgrade($idd,$answer){
	$sql = "SELECT count(*) as totalres from quiz where id in ($idd) and ans in ($answer)";
	$query = $this->db->query($sql);
	return $query->row();
  }
  public function add_question_answer($save){
	$this->db->insert('quiz',$save);
  }
  public function update_logout(){
    $this->db->set('is_logout',1);
    $this->db->where('id',$this->userid);
    $this->db->update('master_user');
  }
  public function check_password($id){
    $query  = $this->db->select(['password'])
    ->from('master_user')
    ->where('id',$id)
    ->get();
    //echo $this->db->last_query();die;
    return $query->row();
  }
  public function update_password($id,$hash){
    $this->db->set('password',$hash);
    $this->db->where('id',$id);
    $this->db->update('master_user');
    //echo $this->db->last_query();die;
    return true;
  }
  public function update_master_user($id,$array){
  //  echo $id.$path;die;
      $this->db->where('id', $id);
      $this->db->update('master_user',$array);
      return true;
  }
  public function update_profile_pic($id,$path){
  //  echo $id.$path;die;
      $this->db->set('profile_image', $path);
      $this->db->where('id', $id);
      $this->db->update('master_user');
      return true;
  }
  public function student_info($userid){
    $query=$this->db->select(['master_user.*','mus.school_name',
    'mus.school_address','mus.parent_fname','mus.parent_lname','mus.parent_email',
    'mus.parent_mobile','mus.current_class' ,'mus.dob','mus.country as country_id','mus.state as state_id',
    'mus.city as city_id','mus.pin_code',
    '(select name from countries where countries.id=mus.country) as country',
    '(select name from cities where cities.id=mus.city) as city',
    '(select name from states where states.id=mus.state) as state'
    ])
   //id	user_id	school_name	school_address	parent_fname	parent_lname	parent_email	parent_mobile	current_class	dob	country	state	city	pin_code

   ->from('master_user')
   ->join('mu_student_info as mus','master_user.id=mus.user_id','left')
   ->where('master_user.id',$userid)
   ->get();
   //echo "<pre>";echo $this->db->last_query();die;
   return $query->row();
}
  public function find_duplicate_student_info($userid){
  //  echo $id.$path;die;

    $query =  $this->db->select('*')->from('mu_student_info')->where('user_id', $userid)->get();
    return  $query->num_rows();
  }
  public function update_extra_info($id,$array){

      $this->db->where('user_id', $id);
      $this->db->update('mu_student_info',$array);
      //echo $this->db->last_query();die;
      return true;
  }
  public function insert_extra_info($array){
      $this->db->insert('mu_student_info',$array);
      return $this->db->insert_id();
  }
  public function user_profile($ss_id){
        $id          =  substr($ss_id, 16);
        $hashtag     =  substr($ss_id, 0,16);
        $query       =  $this->db->select(['first_name','last_name','email','category','info','profile_image','userid'])->from('master_user')
                        ->where('id',$id)->where('hashtag',$hashtag)->get();
        return $query->row();
  }
  public function upcoming_classes($limit, $offset){
    $offset = ($offset-1)*$limit;
    $query  = $this->db->select(['*','course_class_list.class_date','course_class_list.class_start_time',
    'course_class_list.course_id',
    'course_class_list.class_end_time','course_class_list.class_duration_min','course_class_list.class_extended_min',
    'course_class_list.id as class_id','course_class_list.class_amount',
    'course_class_list.method','course_class_list.class_status','course_class_list.faculty_id',
    'course_class_list.class_name','(select course_name from course where course.id=course_class_list.course_id)as course_name',
    '(select concat_ws(" ",master_user.userid,master_user.first_name,master_user.last_name) from master_user where master_user.id=(select v2_faculty.teacher_id from v2_faculty where v2_faculty.id=course_class_list.faculty_id)) as faculty',
    '(select concat_ws(" ",master_user.userid,master_user.first_name,master_user.last_name) from master_user
    where master_user.id=course_class_student.student_id) as student_name',
    '(select subject from subject where subject.id=(select subject from course where id=course_class_list.course_id))as subject',
    '(select class from class where class.id=(select class from course where id=course_class_list.course_id))as class',
    ])
    ->from('course_class_student')->join('course_class_list','course_class_list.id=course_class_student.class_id','right')
    ->where('student_id',$this->userid)
    ->where_in('course_class_list.method',$this->class_status)
    ->where_in('course_class_list.class_status',$this->class_status)
    ->where('timestamp(concat_ws(" ",course_class_list.class_date,course_class_list.class_end_time))>=timestamp(NOW())')
    ->order_by('timestamp(concat_ws(" ",course_class_list.class_date,course_class_list.class_start_time)) asc')
    ->limit($limit, $offset)
    ->get();
  // echo $this->db->last_query();die;
    return $query->result();
  }
  public function upcoming_classes_num_row(){
    $query  = $this->db->select(['*','course_class_list.class_date','course_class_list.class_start_time',
    'course_class_list.course_id',
    'course_class_list.class_end_time','course_class_list.class_duration_min','course_class_list.class_extended_min',
    'course_class_list.id as class_id',
    'course_class_list.method','course_class_list.class_status','course_class_list.faculty_id',
    'course_class_list.class_name','(select course_name from course where course.id=course_class_list.course_id)as course_name',
    '(select concat_ws(" ",master_user.userid,master_user.first_name,master_user.last_name) from master_user where master_user.id=(select v2_faculty.teacher_id from v2_faculty where v2_faculty.id=course_class_list.faculty_id)) as faculty',
    '(select concat_ws(" ",master_user.userid,master_user.first_name,master_user.last_name) from master_user
    where master_user.id=course_class_student.student_id) as student_name',
    '(select subject from subject where subject.id=(select subject from course where id=course_class_list.course_id))as subject',
    '(select class from class where class.id=(select class from course where id=course_class_list.course_id))as class',
  ])
    ->from('course_class_student')->join('course_class_list','course_class_list.id=course_class_student.class_id','right')
    ->where('student_id',$this->userid)
    ->where_in('course_class_list.method',$this->class_status)
    ->where_in('course_class_list.class_status',$this->class_status)
    ->where('timestamp(concat_ws(" ",course_class_list.class_date,course_class_list.class_end_time))>=timestamp(NOW())')
    ->order_by('timestamp(concat_ws(" ",course_class_list.class_date,course_class_list.class_start_time)) asc')
    ->get();
  //  echo $this->db->last_query();die;
    return $query->num_rows();
  }
  public function upcoming_classes_search($data_taskpo){
    $condition	=	$data_taskpo['seach_term'];
    $order		  =	$data_taskpo['order_by'];
    $array      = array('create','modify');
    $offset     = ($offset-1)*$limit;

    $this->db->select(['*','course_class_list.class_date','course_class_list.class_start_time',
    'course_class_list.course_id',
    'course_class_list.class_end_time','course_class_list.class_duration_min','course_class_list.class_extended_min',
    'course_class_list.id as class_id',
    'course_class_list.method','course_class_list.class_status','course_class_list.faculty_id',
    'course_class_list.class_name','(select course_name from course where course.id=course_class_list.course_id)as course_name',
    '(select concat_ws(" ",master_user.userid,master_user.first_name,master_user.last_name) from master_user where master_user.id=(select v2_faculty.teacher_id from v2_faculty where v2_faculty.id=course_class_list.faculty_id)) as faculty',
    '(select concat_ws(" ",master_user.userid,master_user.first_name,master_user.last_name) from master_user
    where master_user.id=course_class_student.student_id) as student_name',
    '(select subject from subject where subject.id=(select subject from course where id=course_class_list.course_id))as subject',
    '(select class from class where class.id=(select class from course where id=course_class_list.course_id))as class',
  ])
    ->from('course_class_student')
    ->join('course_class_list','course_class_list.id=course_class_student.class_id','right')
    ->join('course','course_class_list.course_id=course.id')
    ->where('course_class_student.student_id',$this->userid)
    ->where_in('course_class_list.method',$this->class_status)
    ->where_in('course_class_list.class_status',$this->class_status)
    ->where('timestamp(concat_ws(" ",course_class_list.class_date,course_class_list.class_end_time))>=timestamp(NOW())');
    $this->db->group_start();
    if($condition=='or' && !empty($order)){
      if(!empty($data_taskpo['subject']))		{$this->db->or_like('course.subject',$data_taskpo['subject'])->order_by('course.subject',$order);}
      if(!empty($data_taskpo['date']))			{$this->db->or_where_in('course_class_list.class_date',$data_taskpo['date'])->order_by('date(course_class_list.class_date)',$order);}
    }
    if($condition=='or' && empty($order)){
      if(!empty($data_taskpo['subject']))		{$this->db->or_like('course.subject',$data_taskpo['subject']);}
      if(!empty($data_taskpo['date']))			{$this->db->or_where_in('course_class_list.class_date',$data_taskpo['date']);}
    }
    if($condition=='and' && !empty($order)){
      if(!empty($data_taskpo['subject']))		{$this->db->like('course.subject',$data_taskpo['subject'])->order_by('course.subject',$order);}
      if(!empty($data_taskpo['date']))			{$this->db->where_in('course_class_list.class_date',$data_taskpo['date'])->order_by('date(course_class_list.class_date)',$order);}
    }
    if($condition=='and' && empty($order)){
      if(!empty($data_taskpo['subject']))		{$this->db->like('course.subject',$data_taskpo['subject']);}
      if(!empty($data_taskpo['date']))			{$this->db->where_in('course_class_list.class_date',$data_taskpo['date']);}
    }
    $this->db->group_end();
    $this->db->order_by('timestamp(concat_ws(" ",course_class_list.class_date,course_class_list.class_start_time)) asc');
    $query=$this->db->get();
   //echo $this->db->last_query();die;
    if($query->num_rows()>0){
        return $query->result();
    }
    else{
        return $query->result();
    }
  }
 ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////
 public function past_classes($limit, $offset){
   $offset = ($offset-1)*$limit;
   $query  = $this->db->select(['*','course_class_list.class_date','course_class_list.class_start_time','course_class_list.teacher_rec_url',
   'course_class_list.course_id','course_class_student.id as st_cls_id',
   'course_class_list.class_end_time','course_class_list.class_duration_min','course_class_list.class_extended_min',
   'course_class_list.id as class_id','course_class_list.class_amount',
   'course_class_list.method','course_class_list.class_status','course_class_list.faculty_id',
   'course_class_list.class_name','(select course_name from course where course.id=course_class_list.course_id)as course_name',
   '(select concat_ws(" ",master_user.userid,master_user.first_name,master_user.last_name) from master_user where master_user.id=(select v2_faculty.teacher_id from v2_faculty where v2_faculty.id=course_class_list.faculty_id)) as faculty',
   '(select concat_ws(" ",master_user.userid,master_user.first_name,master_user.last_name) from master_user
   where master_user.id=course_class_student.student_id) as student_name',
   '(select subject from subject where subject.id=(select subject from course where id=course_class_list.course_id))as subject',
   '(select class from class where class.id=(select class from course where id=course_class_list.course_id))as class',
   ])
   ->from('course_class_student')->join('course_class_list','course_class_list.id=course_class_student.class_id','right')
   ->where('student_id',$this->userid)
   ->where_in('course_class_list.method',$this->class_status)
   ->where_in('course_class_list.class_status',$this->class_status)
   ->where('timestamp(concat_ws(" ",course_class_list.class_date,course_class_list.class_end_time))<=timestamp(NOW())')
   ->order_by('timestamp(concat_ws(" ",course_class_list.class_date,course_class_list.class_start_time)) desc')
   ->limit($limit, $offset)
   ->get();
  ///echo $this->db->last_query();die;
   return $query->result();
 }
 public function past_classes_num_row(){
   $query  = $this->db->select(['*','course_class_list.class_date','course_class_list.class_start_time',
   'course_class_list.course_id','course_class_student.id as st_cls_id',
   'course_class_list.class_end_time','course_class_list.class_duration_min','course_class_list.class_extended_min',
   'course_class_list.id as class_id',
   'course_class_list.method','course_class_list.class_status','course_class_list.faculty_id',
   'course_class_list.class_name','(select course_name from course where course.id=course_class_list.course_id)as course_name',
   '(select concat_ws(" ",master_user.userid,master_user.first_name,master_user.last_name) from master_user where master_user.id=(select v2_faculty.teacher_id from v2_faculty where v2_faculty.id=course_class_list.faculty_id)) as faculty',
   '(select concat_ws(" ",master_user.userid,master_user.first_name,master_user.last_name) from master_user
   where master_user.id=course_class_student.student_id) as student_name',
   '(select subject from subject where subject.id=(select subject from course where id=course_class_list.course_id))as subject',
   '(select class from class where class.id=(select class from course where id=course_class_list.course_id))as class',
 ])
   ->from('course_class_student')->join('course_class_list','course_class_list.id=course_class_student.class_id','right')
   ->where('student_id',$this->userid)
   ->where_in('course_class_list.method',$this->class_status)
   ->where_in('course_class_list.class_status',$this->class_status)
   ->where('timestamp(concat_ws(" ",course_class_list.class_date,course_class_list.class_end_time))<=timestamp(NOW())')
   ->order_by('timestamp(concat_ws(" ",course_class_list.class_date,course_class_list.class_start_time))')
   ->get();
 //  echo $this->db->last_query();die;
   return $query->num_rows();
 }
 public function past_classes_search($data_taskpo){
   $condition	=	$data_taskpo['seach_term'];
   $order		  =	$data_taskpo['order_by'];
   $array      = array('create','modify');
   $offset     = ($offset-1)*$limit;

   $this->db->select(['*','course_class_list.class_date','course_class_list.class_start_time',
   'course_class_list.course_id','course_class_student.id as st_cls_id',
   'course_class_list.class_end_time','course_class_list.class_duration_min','course_class_list.class_extended_min',
   'course_class_list.id as class_id',
   'course_class_list.method','course_class_list.class_status','course_class_list.faculty_id',
   'course_class_list.class_name','(select course_name from course where course.id=course_class_list.course_id)as course_name',
   '(select concat_ws(" ",master_user.userid,master_user.first_name,master_user.last_name) from master_user where master_user.id=(select v2_faculty.teacher_id from v2_faculty where v2_faculty.id=course_class_list.faculty_id)) as faculty',
   '(select concat_ws(" ",master_user.userid,master_user.first_name,master_user.last_name) from master_user
   where master_user.id=course_class_student.student_id) as student_name',
   '(select subject from subject where subject.id=(select subject from course where id=course_class_list.course_id))as subject',
   '(select class from class where class.id=(select class from course where id=course_class_list.course_id))as class',
   '(select category from category where category.id=(select category from course where id=course_class_list.course_id))as category',
 ])
   ->from('course_class_student')
   ->join('course_class_list','course_class_list.id=course_class_student.class_id','right')
   ->join('course','course_class_list.course_id=course.id')
   ->where('course_class_student.student_id',$this->userid)
   ->where_in('course_class_list.method',$this->class_status)
   ->where_in('course_class_list.class_status',$this->class_status)
   ->where('timestamp(concat_ws(" ",course_class_list.class_date,course_class_list.class_end_time))<=timestamp(NOW()) desc');
   $this->db->group_start();
   if($condition=='or' && !empty($order)){
     if(!empty($data_taskpo['subject']))		{$this->db->or_like('course.subject',$data_taskpo['subject'])->order_by('course.subject',$order);}
     if(!empty($data_taskpo['date']))			{$this->db->or_where_in('course_class_list.class_date',$data_taskpo['date'])->order_by('date(course_class_list.class_date)',$order);}
   }
   if($condition=='or' && empty($order)){
     if(!empty($data_taskpo['subject']))		{$this->db->or_like('course.subject',$data_taskpo['subject']);}
     if(!empty($data_taskpo['date']))			{$this->db->or_where_in('course_class_list.class_date',$data_taskpo['date']);}
   }
   if($condition=='and' && !empty($order)){
     if(!empty($data_taskpo['subject']))		{$this->db->like('course.subject',$data_taskpo['subject'])->order_by('course.subject',$order);}
     if(!empty($data_taskpo['date']))			{$this->db->where_in('course_class_list.class_date',$data_taskpo['date'])->order_by('date(course_class_list.class_date)',$order);}
   }
   if($condition=='and' && empty($order)){
     if(!empty($data_taskpo['subject']))		{$this->db->like('course.subject',$data_taskpo['subject']);}
     if(!empty($data_taskpo['date']))			{$this->db->where_in('course_class_list.class_date',$data_taskpo['date']);}
   }
   $this->db->group_end();
   $this->db->order_by('timestamp(concat_ws(" ",course_class_list.class_date,course_class_list.class_start_time))');
   $query=$this->db->get();
  //echo $this->db->last_query();die;
   if($query->num_rows()>0){
       return $query->result();
   }
   else{
       return $query->result();
   }
 }

  function get_subject_array(){
      $response = array();
      $q=$this->db->select(['*','(select class_code from class where class.id=subject.class_id)as class_code',
      '(select category_code from category where category.id=subject.category_id)as category_code'])
      ->from('subject')->order_by('subject','asc')->get();
      $response = $q->result_array();
      return $response;
  }
  public function class_remarks_update($v2_cs_stu_id,$class_id,$student_remarks_json){
    $this->db->set('remarks_student',$student_remarks_json);
    $this->db->where('id',$v2_cs_stu_id);
    $this->db->where('class_id',$class_id);
    $this->db->update('course_class_student');
    return true;
  }
  public function course_enrolled_list($limit, $offset){
    $offset = ($offset-1)*$limit;
	$this->db->simple_query('SET sql_mode=(SELECT REPLACE(@@sql_mode,"ONLY_FULL_GROUP_BY",""))');
		$query  = $this->db->select(['*','(select course_name from course where course.id=course_class_student.course_id)as course_name',
      '(select CONCAT(first_name, " ", last_name) from v2_faculty  join master_user on master_user.id=v2_faculty.teacher_id where course.faculty=v2_faculty.id) as faculty',
      '(select subject from subject where subject.id=(select subject from course where id=course_class_student.course_id))as subject',
      '(select class from class where class.id=(select class from course where id=course_class_student.course_id))as class',
    //  '(select concat_ws(" ",course_class_list.class_date,course_class_list.class_start_time) from course_class_list join course on course.id=course_class_list.course_id order by id asc limit 1)as started',
    //  '(select concat_ws(" ",course_class_list.class_date,course_class_list.class_start_time) from course_class_list join course on course.id=course_class_list.course_id order by id desc limit 1)as end'
    '(select class_date from course_class_list where course_class_list.course_id=course.id order by id asc limit 1)as started',
       '(select class_date from course_class_list where course_class_list.course_id=course.id order by id desc limit 1)as end'

  ])
    ->from('course_class_student')
    ->join('course','course.id=course_class_student.course_id','inner')
    ->where('course_class_student.student_id',$this->userid)
    ->group_by('course_class_student.course_id')
  	->limit($limit, $offset)
	  ->get();
    return $query->result();
		//echo $this->db->last_query();die;
  //  if($query->num_rows>0){ $res =$query->result(); }else{ $res =0; } return $res;
  }

  public function course_enrolled_list_num_rows(){
	$this->db->simple_query('SET sql_mode=(SELECT REPLACE(@@sql_mode,"ONLY_FULL_GROUP_BY",""))');
    $query  = $this->db->select(['*','(select course_name from course where course.id=course_class_student.course_id)as course_name',
      '(select CONCAT(first_name, " ", last_name) from v2_faculty  join master_user on master_user.id=v2_faculty.teacher_id where course.faculty=v2_faculty.id) as faculty',
      '(select subject from subject where subject.id=(select subject from course where id=course_class_student.course_id))as subject',
      '(select class from class where class.id=(select class from course where id=course_class_student.course_id))as class',
    //  '(select concat_ws(" ",course_class_list.class_date,course_class_list.class_start_time) from course_class_list join course on course.id=course_class_list.course_id order by id asc limit 1)as started',
    //  '(select concat_ws(" ",course_class_list.class_date,course_class_list.class_start_time) from course_class_list join course on course.id=course_class_list.course_id order by id desc limit 1)as end'
    '(select class_date from course_class_list where course_class_list.course_id=course.id order by id asc limit 1)as started',
       '(select class_date from course_class_list where course_class_list.course_id=course.id order by id desc limit 1)as end'

  ])
    ->from('course_class_student')
    ->join('course','course.id=course_class_student.course_id','inner')
    ->where('course_class_student.student_id',$this->userid)
    ->group_by('course_class_student.course_id')
	  ->get();
    //echo $this->db->last_query();die;
    return $query->num_rows();
  }
  public function account_wallet($limit, $offset){
    $offset = ($offset-1)*$limit;
    $query  = $this->db->select(['*','student_current_wallet.wallet_amount as current_amount','student_wallet_amount_history.current_wallet_id',
    'student_wallet_amount_history.wallet_add_date_time','student_wallet_amount_history.wallet_amount as amount_add',
    'student_wallet_amount_history.remarks'])->from('student_current_wallet')->join('student_wallet_amount_history',
    'student_wallet_amount_history.current_wallet_id=student_current_wallet.id')->where('student_current_wallet.student_id',$this->userid)
      ->limit($limit, $offset)
      ->get();
    //  echo $this->db->last_query();die;
    return $query->result();
  }
  public function account_wallet_num_rows(){
    $offset = ($offset-1)*$limit;
    $query  = $this->db->select(['*'])->from('student_current_wallet')->join('student_wallet_amount_history',
    'student_wallet_amount_history.current_wallet_id=student_current_wallet.id')->where('student_current_wallet.student_id',$this->userid)
      ->get();
    return $query->num_rows();
  }
  public function course_class_list_by_courseid($limit, $offset,$courseid){
    $offset = ($offset-1)*$limit;
    $query  = $this->db->select(['*','course_class_list.class_date','course_class_list.class_start_time','course_class_list.teacher_rec_url',
    'course_class_list.course_id',
    'course_class_list.class_end_time','course_class_list.class_duration_min','course_class_list.class_extended_min',
    'course_class_list.id as class_id',
    'course_class_list.method','course_class_list.class_status','course_class_list.faculty_id',
    'course_class_list.class_name','(select course_name from course where course.id=course_class_list.course_id)as course_name',
    '(select concat_ws(" ",master_user.userid,master_user.first_name,master_user.last_name) from master_user where master_user.id=(select v2_faculty.teacher_id from v2_faculty where v2_faculty.id=course_class_list.faculty_id)) as faculty',
    '(select concat_ws(" ",master_user.userid,master_user.first_name,master_user.last_name) from master_user
    where master_user.id=course_class_student.student_id) as student_name',
    '(select subject from subject where subject.id=(select subject from course where id=course_class_list.course_id))as subject',
    '(select class from class where class.id=(select class from course where id=course_class_list.course_id))as class',
    '(select category from category where category.id=(select category from course where id=course_class_list.course_id))as category',
    ])
    ->from('course_class_student')->join('course_class_list','course_class_list.id=course_class_student.class_id','right')
    ->where('student_id',$this->userid)
    ->where_in('course_class_list.method',$this->class_status)
    ->where_in('course_class_list.class_status',$this->class_status)
    ->where('course_class_list.course_id',$courseid)
    //->where('timestamp(concat_ws(" ",course_class_list.class_date,course_class_list.class_end_time))>=timestamp(NOW())')
    ->order_by('timestamp(concat_ws(" ",course_class_list.class_date,course_class_list.class_start_time)) desc')
    ->limit($limit, $offset)
    ->get();
  // echo $this->db->last_query();die;
    return $query->result();
	}
  public function course_class_list_by_courseid_num_rows($courseid){
    $query  = $this->db->select(['*','course_class_list.class_date','course_class_list.class_start_time','course_class_list.teacher_rec_url',
    'course_class_list.course_id',
    'course_class_list.class_end_time','course_class_list.class_duration_min','course_class_list.class_extended_min',
    'course_class_list.id as class_id',
    'course_class_list.method','course_class_list.class_status','course_class_list.faculty_id',
    'course_class_list.class_name','(select course_name from course where course.id=course_class_list.course_id)as course_name',
    '(select concat_ws(" ",master_user.userid,master_user.first_name,master_user.last_name) from master_user where master_user.id=(select v2_faculty.teacher_id from v2_faculty where v2_faculty.id=course_class_list.faculty_id)) as faculty',
    '(select concat_ws(" ",master_user.userid,master_user.first_name,master_user.last_name) from master_user
    where master_user.id=course_class_student.student_id) as student_name',
    '(select subject from subject where subject.id=(select subject from course where id=course_class_list.course_id))as subject',
    '(select class from class where class.id=(select class from course where id=course_class_list.course_id))as class',
    '(select category from category where category.id=(select category from course where id=course_class_list.course_id))as category',
    ])
    ->from('course_class_student')->join('course_class_list','course_class_list.id=course_class_student.class_id','right')
    ->where('student_id',$this->userid)
    ->where_in('course_class_list.method',$this->class_status)
    ->where_in('course_class_list.class_status',$this->class_status)
    ->where('course_class_list.course_id',$courseid)
    //->where('timestamp(concat_ws(" ",course_class_list.class_date,course_class_list.class_end_time))>=timestamp(NOW())')
    ->order_by('timestamp(concat_ws(" ",course_class_list.class_date,course_class_list.class_start_time)) desc')
    ->get();
		//echo $this->db->last_query();die;
		return $query->num_rows();
	}

  public function v2_student_request_program_chat_list($limit, $offset){
      $offset       =   ($offset-1)*$limit;
      $query        =   $this->db->select(['pr.*',
          'class.class','category.category','subject.subject'])
          ->from('v2_student_request_program as pr')
          ->join('class','pr.class_id=class.id')
          ->join('category','pr.category_id=category.id')
          ->join('subject','pr.subject_id=subject.id')
          ->where("pr.user_id", $this->userid)
          ->limit($limit, $offset)
          ->order_by('pr.id','desc')
          ->get();
          //   echo $this->db->last_query();die;
      return $query->result();
  }
  public function v2_student_request_program_chat_num_rows(){
      $query        =   $this->db->select(['pr.*',
          'class.class','category.category','subject.subject'])
          ->from('v2_student_request_program as pr')
          ->join('class','pr.class_id=class.id')
          ->join('category','pr.category_id=category.id')
          ->join('subject','pr.subject_id=subject.id')
          ->where("pr.user_id", $this->userid)
          ->order_by('pr.id','desc')
          ->get();
      return $query->num_rows();
  }
  function getcategory(){
    $response = array();
    $this->db->select('*')->where("id", '1');
    $q = $this->db->get('category');
   //echo $this->db->last_query();die;
    $response = $q->result_array();
    return $response;
  }
  public function subject_code_lists(){
		$query		=	  $this->db->select(['subject.id','subject.subject','subject.subject_code','class.class_code',
		'category.category_code'])
		->from('subject')
		->join('category','category.id=subject.category_id')
		->join('class','class.id=subject.class_id')
		->order_by("subject.id", "asc")->get();
	  //echo "<pre>"; print_r($query->result_array());die;

		$return 	= 	array();
		if($query->num_rows() > 0) {
			$return[''] = ('Select subject code');
			foreach($query->result_array() as $row) {
				$return[$row['id']] = strtoupper($row['category_code'].$row['class_code'].$row['subject_code']);
			}
		}
		return $return;
	}
  function get_category_class($postData){
      $response = array();
      $this->db->select('*');
      $this->db->where('category_id', $postData['category']);

      $q = $this->db->get('class');
      $response = $q->result_array();
      return $response;
  }
  function get_class_subject($postData){
      $response = array();
      $this->db->select('*');
      $this->db->where('class_id', $postData['classes']);
      $q = $this->db->get('subject');
      $response = $q->result_array();
      return $response;
  }
  function get_subject2(){
      $response = array();
      $q=$this->db->select(['*','(select class_code from class where class.id=subject.class_id)as class_code',
      '(select category_code from category where category.id=subject.category_id)as category_code'])->from('subject')->get();
      $response = $q->result_array();
      return $response;
  }
  function get_subject_chapter($postData){
      $response = array();
      $this->db->select('*');
      $this->db->where('subject_id', $postData['subject']);
      $q = $this->db->get('chapter');
      $response = $q->result_array();
      return $response;
  }
  function get_chapter_topic($postData){
      $response = array();
      $this->db->select('*');
      $this->db->where('chapter_id', $postData['chapter']);
      $q = $this->db->get('topic');
      $response = $q->result_array();
      return $response;
  }
  function get_class_subject_course_maded_list($postData){
      // echo "<pre>";print_r($postData['subject']);die;
      $response     = array();
      $query  = $this->db->select(['v2_course_new.*','category.category','class.class','subject.subject'])
          ->from('v2_course_new')
          ->join('category','v2_course_new.category_id=category.id')
          ->join('class','v2_course_new.class_id=class.id')
          ->join('subject','v2_course_new.subject_id=subject.id')
          ->where('subject_id', $postData['subject'])->get();
          $response     = $query->result_array();
          return $response;
  }
  public function insert_v2_student_request_program_chat($data){
      $this->db->insert('v2_student_request_program_chat', $data);
      $data['id'] = $this->db->insert_id();
      return $data['id'];
  }
  public function insert_v2_student_request_program($data){
      $this->db->insert('v2_student_request_program', $data);
      $data['id'] = $this->db->insert_id();
      return $data['id'];
  }
  public function chat_list_subject_($user_id,$request_program_id){
     $query        =   $this->db->select(['pr.*','pr_chat.id as chat_id','pr_chat.request_program_id','pr_chat.user_message','pr_chat.admin_reply',
       'pr_chat.user_msg_date','pr_chat.admin_reply_date','pr_chat.create_at as chat_create','pr_chat.update_at as chat_update',
       'class.class','category.category','subject.subject'])
       ->from('v2_student_request_program as pr')
       ->join('v2_student_request_program_chat as pr_chat','pr_chat.request_program_id=pr.id')
       ->join('class','pr.class_id=class.id')
       ->join('category','pr.category_id=category.id')
       ->join('subject','pr.subject_id=subject.id')
       ->where("pr.user_id", $user_id)
       ->where("pr.id", $request_program_id)

       ->limit($limit, $offset)
       ->order_by('pr.id','desc')
       ->get();
       return $query->result();
       //  echo $this->db->last_query();die;
  }
  public function program_offered_list($limit, $offset){
        $offset     =   ($offset-1)*$limit;
        $query      =   $this->db->select(['class.id','class.class','(select category from category where class.category_id=category.id)as category'])
                        ->from('class')->limit($limit, $offset)->get();
        return $query->result();
  }
  public function program_offered_list_num_rows(){
      $query        =   $this->db->select(['class.id','class.class','(select category from category where class.category_id=category.id)as category'])
                        ->from('class')->get();
      return $query->num_rows();
  }

public function program_offered_subject_list_byid($program_id){
    $query        =   $this->db->select(['subject.id','subject.subject','subject.description','subject.class_id','subject.category_id',
                      'class.class','class.class_code',
                      'category.category','category.category_code','subject.subject_code'])
                      ->from('subject')->join('class','subject.class_id=class.id')->join('category','subject.category_id=category.id')
                      ->where("subject.class_id", $program_id)
                      ->get();
    return $query->result();
}

  public function program_offered_subject_list_num_rows($program_id){
      $query        =   $this->db->select(['subject.id','subject.subject','subject.description','subject.class_id','subject.category_id',
          'class.class','class.class_code',
          'category.category','category.category_code','subject.subject_code'])
                        ->from('subject')->join('class','subject.class_id=class.id')->join('category','subject.category_id=category.id')
                        ->where("subject.class_id", $program_id)->get();
      return $query->num_rows();
  }



}
