<?php defined('BASEPATH')or exit('NO direct Access is Allowed');

class Coursemodel extends CI_Model{

  public function __construct(){
    parent::__construct();
      $this->class_status      = array('create','modify');
  }
  public function course_list_running(){
    $query  = $this->db->select(['course.id','course.course_name'])->from('course')
     //->join('course','course.id=course_class_list.course_id','left')
    // ->where('course_class_list.course_id')
    // ->where_in('course_class_list.method',array('create','modify','cancel'))
  //   ->where_in('course_class_list.class_status',array('create','modify','cancel'))
  // ->group_by('course.id')
     //->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->result();
  }
  public function total_course_classes_($courseid){
    $query  = $this->db->select(['course_class_list.id'])->from('course_class_list')
     ->where('course_class_list.course_id',$courseid)
     ->where_in('course_class_list.method',array('create','modify','cancel'))
     ->where_in('course_class_list.class_status',array('create','modify','cancel'))
     ->get();
     //echo $this->db->last_query();die;
       return $query->num_rows();
   }
  public function total_course_classes_over_till($courseid){
    $query  = $this->db->select(['course_class_list.id'])->from('course_class_list')
    ->join('course','course.id=course_class_list.course_id','left')
    ->where('course_class_list.course_id',$courseid)
    ->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();
    return $query->num_rows();
  }
  public function total_course_classes_remain_till($courseid){
    $query  = $this->db->select(['course_class_list.id'])->from('course_class_list')
    ->join('course','course.id=course_class_list.course_id','left')
    ->where('course_class_list.course_id',$courseid)
    ->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")
    ->get();
    return $query->num_rows();
  }
  public function total_course_classes_runn_till($courseid){
    $query  = $this->db->select(['course_class_list.id'])->from('course_class_list')
    ->join('course','course.id=course_class_list.course_id','left')
    ->where('course_class_list.course_id',$courseid)
    ->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_start_time))<=timestamp(NOW())')
    ->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();
    return $query->num_rows();
  }

  public function total_course_classes_cancel_till($courseid){
    $query  = $this->db->select(['course_class_list.id'])->from('course_class_list')
    ->join('course','course.id=course_class_list.course_id','left')
    ->where('course_class_list.course_id',$courseid)
    ->group_start()
    ->or_where_in('course_class_list.method',array('cancel'))
    ->or_where_in('course_class_list.class_status',array('cancel'))
    ->group_end()
  //  ->where('timestamp(concat_ws(" ",course_class_list.class_date,course_class_list.class_start_time))<=timestamp(NOW())')
    //->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();
    return $query->num_rows();
  }


  public function find_usercategory_userid($id){
    $query = $this->db->select('userid')->from('master_user')->where('id',$id)->get();
    return $query->row();
  }
  public function teacher_schedule_by_id($teacherid,$limit, $offset){
    $offset = ($offset-1)*$limit;

		$query  = $this->db->select(['course_class_list.*','course.course_name',
    '(select CONCAT(first_name, " ", last_name) from v2_faculty  join master_user on master_user.id=v2_faculty.teacher_id where course_class_list.faculty_id=v2_faculty.id) as faculty',
    '(select session_tag_id from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as session_name',
    '(select total_attendee from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as total_attendee',
    '(select attendee_limit from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as attendee_limit',
    '(select category from category where category.id=course.category)as category_id',
    '(select subject from subject where subject.id=course.subject)as subject_id','course_class_list.teacher_cls_url','course_class_list.teacher_rec_url',
    '(select class from class where class.id=course.class)as class_id','(select count(id) from course_vise_student where course_id=course.id and status=1)as course_student',
    '(select count(id) from course_vise_student where course_id=course.id and status=0)as inactive_student','(select master_user.userid
    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 userid'])->from('course_class_list')
    ->join('course','course.id=course_class_list.course_id','left')
    ->join('v2_faculty','v2_faculty.id=course_class_list.faculty_id')
    ->where('v2_faculty.teacher_id',$teacherid)
    ->order_by("TIMESTAMP(CONCAT_WS(' ',course_class_list.class_date,course_class_list.class_start_time)) desc")
   // ->where("TIMESTAMP(CONCAT_WS(' ',v2_course_class_detail.class_date,v2_course_class_detail.class_start_time))>CURRENT_TIMESTAMP()")
    	->limit($limit, $offset)
		  ->get();
		//echo $this->db->last_query();die;
		return $query->result();
  }
  public function teacher_schedule_by_id_num_rows($teacherid){
    $query  = $this->db->select(['course_class_list.*','course.course_name',
    '(select CONCAT(first_name, " ", last_name) from v2_faculty  join master_user on master_user.id=v2_faculty.teacher_id where course_class_list.faculty_id=v2_faculty.id) as faculty',
    '(select session_tag_id from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as session_name',
    '(select total_attendee from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as total_attendee',
    '(select attendee_limit from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as attendee_limit',
    '(select category from category where category.id=course.category)as category_id',
    '(select subject from subject where subject.id=course.subject)as subject_id','course_class_list.teacher_cls_url','course_class_list.teacher_rec_url',
    '(select class from class where class.id=course.class)as class_id','(select count(id) from course_vise_student where course_id=course.id and status=1)as course_student',
    '(select count(id) from course_vise_student where course_id=course.id and status=0)as inactive_student','(select master_user.userid
    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 userid'])->from('course_class_list')
    ->join('course','course.id=course_class_list.course_id','left')
      ->join('v2_faculty','v2_faculty.id=course_class_list.faculty_id')
      ->where('v2_faculty.teacher_id',$teacherid)
    //->where('course_class_list.faculty_id',$facultyid)
    ->order_by("TIMESTAMP(CONCAT_WS(' ',course_class_list.class_date,course_class_list.class_start_time)) desc")
		  ->get();
      return $query->num_rows();
  }
  public function teacher_schedule_by_id_count_class($teacherid){
    $query  = $this->db->select(['count(course_class_list.id) as class_count','group_concat(course_id) as course_count','group_concat(course_name) as course_names'])->from('course_class_list')
          ->join('v2_faculty','v2_faculty.id=course_class_list.faculty_id')
          ->join('course','course.id=course_class_list.course_id','left')
    ->where('v2_faculty.teacher_id',$teacherid)->group_by('v2_faculty.teacher_id')
      ->get();
      //echo $this->db->last_query();die;
      return $query->row();
  }
  public function teacher_schedule_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(['*','count(class_name)as class_count','v2_faculty.teacher_id',
    'GROUP_CONCAT(course_id)as course_count',
    '(select concat_ws(" ",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 master_user.userid
    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 userid'])
    ->from('course_class_list')
    ->join('v2_faculty','v2_faculty.id=course_class_list.faculty_id')
    ->group_by('v2_faculty.teacher_id')
      	->limit($limit, $offset)
    ->get();
  //  echo $this->db->last_query();die;
    return $query->result();
  }
  public function teacher_schedule_list_num_rows(){
	  $this->db->simple_query('SET sql_mode=(SELECT REPLACE(@@sql_mode,"ONLY_FULL_GROUP_BY",""))');
    $query = $this->db->select(['*','count(class_name)as class_count','v2_faculty.teacher_id',
    'GROUP_CONCAT(course_id)as course_count',
    '(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'])
    ->from('course_class_list')
    ->join('v2_faculty','v2_faculty.id=course_class_list.faculty_id')
    ->group_by('v2_faculty.teacher_id')->get();
  //  echo $this->db->last_query();
    return $query->num_rows();
  }

  public function studentscheduleid_count_class($student_id){
    $query  = $this->db->select(['count(course_class_list.id)as class_count',
    'group_concat(course_class_list.course_id)as course_count'])
    ->from('course_class_student')->join('course_class_list','course_class_list.id=course_class_student.class_id','right')
    ->where('student_id',$student_id)
    ->get();
    return $query->row();
  }
  public function studentscheduleid($student_id,$limit, $offset){
    $offset = ($offset-1)*$limit;
    //print_r(array($limit,$offset));die;
    $query  = $this->db->select(['*','course_class_list.class_date','course_class_list.class_start_time',
    'course_class_list.course_id',
    // 'count(course_class_list.id)as class_count',
    // 'group_concat(course_class_list.course_id)as course_count',
    '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.first_name,master_user.last_name) from master_user
    where master_user.id=course_class_student.student_id) as student_name',
    '(select  master_user.userid from master_user
    where master_user.id=course_class_student.student_id) as  userid'])
    ->from('course_class_student')->join('course_class_list','course_class_list.id=course_class_student.class_id','right')
    ->where('student_id',$student_id)
    ->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 studentscheduleid_num_rows($student_id){
    $query  = $this->db->select(['*'])->from('course_class_student')
    ->where('student_id',$student_id)
    ->get();
    return $query->num_rows();
  }
  public function studentschedulelist_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(['id','COUNT(class_id) as claas_count','student_id',
    '(select concat_ws(" ",master_user.first_name,master_user.last_name) from master_user
    where master_user.id=course_class_student.student_id) as student_name',
    '(select  master_user.userid from master_user
    where master_user.id=course_class_student.student_id) as  userid'
    ,'group_concat(course_id SEPARATOR ",") AS course_count'])
    ->from('course_class_student')->group_by('student_id')
    ->limit($limit, $offset)
    ->get();
    //echo $this->db->last_query();die;
    return $query->result();
  }
  public function studentschedulelist_list_num_rows(){
       $this->db->simple_query('SET sql_mode=(SELECT REPLACE(@@sql_mode,"ONLY_FULL_GROUP_BY",""))');
    $query  = $this->db->select(['id','COUNT(class_id) as claas_count','student_id',
    '(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','group_concat(course_id SEPARATOR ",") AS course_count'])
    ->from('course_class_student')->group_by('student_id')
    ->get();
    return $query->num_rows();
  }
  public function course_list($limit, $offset){
		$offset = ($offset-1)*$limit;
		$query  = $this->db->select(['*','(select CONCAT(userid," | ",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 session_tag_id from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as session_name',
    '(select total_attendee from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as total_attendee',
    '(select attendee_limit from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as attendee_limit',
    // '(select count_month from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as parallel_session_tag',
    '(select sum(course_class_student.class_amount) from course_class_student where course_class_student.course_id=course.id)as earning_from_class',
    '(select sum(course_class_list.class_duration_min) from course_class_list where course_class_list.course_id=course.id and method in("create","modify") and class_status in("create","modify"))as sum_duration',
    '(select sum(course_class_list.class_extended_min) from course_class_list where course_class_list.course_id=course.id and method in("create","modify") and class_status in("create","modify"))as sum_duration_extend',
    '(select count(id) from course_class_list where course_class_list.course_id=course.id and method in("create","modify","cancel") and class_status in("create","modify","cancel"))as course_class_count',
	'(select count(id) from course_class_list where course_class_list.course_id=course.id and method in("create","modify") and class_status in("create","modify") and concat_ws(" ",class_date,class_end_time) < now() )as course_over_count',
	'(select count(id) from course_class_list where course_class_list.course_id=course.id and method in("create","modify") and class_status in("create","modify") and concat_ws(" ",class_date,class_end_time) > now() )as course_left_count',
	'(select count(id) from course_class_list where course_class_list.course_id=course.id and method in("create","modify") and class_status in("create","modify") /* and (concat_ws(" ",class_date,class_start_time) > now() or concat_ws(" ",class_date,class_end_time) < now()) */ and date(class_date)=date(now()) )as course_running_count',
	'(select count(id) from course_class_list where course_class_list.course_id=course.id and (method ="modify" or class_status="modify"))as course_reschedule_count',
    '(select count(id) from course_class_list where course_class_list.course_id=course.id and method in("cancel") and class_status in("cancel"))as course_class_cancel_count',
    '(select category from category where category.id=course.category)as category_id',
    '(select subject from subject where subject.id=course.subject)as subject_id',
    '(select class from class where class.id=course.class)as class_id','(select count(id) from course_vise_student where course_id=course.id and status=1)as course_student',
    '(select count(id) from course_vise_student where course_id=course.id and status=0)as inactive_student'])->from('course')
    	->limit($limit, $offset)
		  ->get();
		//echo $this->db->last_query();die;
		return $query->result();
	}
  public function course_list_num_rows(){
    $query  = $this->db->select(['*','(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 session_tag_id from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as session_name',
    '(select total_attendee from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as total_attendee',
    '(select attendee_limit from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as attendee_limit',
    // '(select count_month from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as parallel_session_tag',
    '(select category from category where category.id=course.category)as category_id',
    '(select subject from subject where subject.id=course.subject)as subject_id',
    '(select class from class where class.id=course.class)as class_id','(select count(id) from course_vise_student where course_id=course.id and status=1)as course_student',
    '(select count(id) from course_vise_student where course_id=course.id and status=0)as inactive_student'])->from('course')
      ->get();
		//echo $this->db->last_query();die;
		return $query->num_rows();
	}
  public function calendar_class_list($courseid)
  {
    $query  = $this->db->select(['course_class_list.*','course.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 session_tag_id from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as session_name',
    '(select total_attendee from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as total_attendee',
    '(select attendee_limit from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as attendee_limit',
    '(select category from category where category.id=course.category)as category_id',
    '(select subject from subject where subject.id=course.subject)as subject_id',
    '(select class from class where class.id=course.class)as class_id','(select count(id) from course_vise_student where course_id=course.id and status=1)as course_student',
    '(select count(id) from course_vise_student where course_id=course.id and status=0)as inactive_student'])->from('course_class_list')
    ->join('course','course.id=course_class_list.course_id','left')->where('course_class_list.course_id',$courseid)
    ->get();
    //echo $this->db->last_query();die;
    return $query->result();
  }
  public function course_class_list($limit, $offset,$courseid){
		$offset = ($offset-1)*$limit;
		$query  = $this->db->select(['course_class_list.*','course.course_name',
    '(select userid from v2_faculty  join master_user on master_user.id=v2_faculty.teacher_id where course_class_list.faculty_id=v2_faculty.id) as facultyuserid',
    '(select CONCAT(first_name, " ", last_name) from v2_faculty  join master_user on master_user.id=v2_faculty.teacher_id where course_class_list.faculty_id=v2_faculty.id) as faculty',
    '(select session_tag_id from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as session_name',
    '(select total_attendee from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as total_attendee',
    '(select attendee_limit from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as attendee_limit',
    '(select category from category where category.id=course.category)as category_id',
    '(select subject from subject where subject.id=course.subject)as subject_id',
    '(select sum(course_class_student.class_amount) from course_class_student where course_class_student.class_id=course_class_list.id)as earning_from_class',
    '(select count(id) from course_class_student where course_class_student.class_id=course_class_list.id group by course_class_student.class_id )as current_class_strength',
    '(select class from class where class.id=course.class)as class_id','(select count(id) from course_vise_student where course_id=course.id and status=1)as course_student',
    '(select count(id) from course_vise_student where course_id=course.id and status=0)as inactive_student'])->from('course_class_list')
    ->join('course','course.id=course_class_list.course_id','left')
    ->where('course_class_list.course_id',$courseid)
    ->order_by("TIMESTAMP(CONCAT_WS(' ',course_class_list.class_date,course_class_list.class_start_time)) desc")
   // ->where("TIMESTAMP(CONCAT_WS(' ',v2_course_class_detail.class_date,v2_course_class_detail.class_start_time))>CURRENT_TIMESTAMP()")
    	->limit($limit, $offset)
		  ->get();
		//echo $this->db->last_query();die;
		return $query->result();
	}
  public function course_class_list_num_rows($courseid){
    $query  = $this->db->select(['course_class_list.*','course.course_name',
    '(select userid from v2_faculty  join master_user on master_user.id=v2_faculty.teacher_id where course_class_list.faculty_id=v2_faculty.id) as facultyuserid',
    '(select CONCAT(first_name, " ", last_name) from v2_faculty  join master_user on master_user.id=v2_faculty.teacher_id where course_class_list.faculty_id=v2_faculty.id) as faculty',
    '(select session_tag_id from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as session_name',
    '(select total_attendee from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as total_attendee',
    '(select attendee_limit from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as attendee_limit',
    '(select category from category where category.id=course.category)as category_id',
    '(select subject from subject where subject.id=course.subject)as subject_id',
    '(select sum(course_class_student.class_amount) from course_class_student where course_class_student.class_id=course_class_list.id)as earning_from_class',
    '(select class from class where class.id=course.class)as class_id','(select count(id) from course_vise_student where course_id=course.id and status=1)as course_student',
    '(select count(id) from course_vise_student where course_id=course.id and status=0)as inactive_student'])->from('course_class_list')
    ->join('course','course.id=course_class_list.course_id','left')
    ->where('course_class_list.course_id',$courseid)
    ->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 course_student_limit($course_id){
    $query  = $this->db->select('id')->from('course_vise_student')->where('course_id',$course_id)->where('status','1')->get();
    return $query->num_rows();
  }
  public function total_course_student_list($course_id){
    $query  = $this->db->select(['*','(select course_name from course where course.id=course_vise_student.id)as course_name',
    '(select student_current_wallet.wallet_amount from student_current_wallet where student_current_wallet.student_id=course_vise_student.student_id)as student_wallet',
    '(select concat_ws(" ",master_user.userid,master_user.first_name,master_user.last_name) from master_user where master_user.id=course_vise_student.student_id)as student_name'])
    ->from('course_vise_student')->where('course_id',$course_id)->get();
    return $query->result();
  }
  public function total_course_active_student_list($course_id){
    $query  = $this->db->select(['*','(select course_name from course where course.id=course_vise_student.id)as course_name',
    '(select student_current_wallet.wallet_amount from student_current_wallet where student_current_wallet.student_id=course_vise_student.student_id)as student_wallet',
    '(select concat_ws(" ",master_user.userid,master_user.first_name,master_user.last_name) from master_user where master_user.id=course_vise_student.student_id)as student_name'])
    ->from('course_vise_student')->where('course_id',$course_id)->where('status',1)->get();
    return $query->result();
  }
  public function total_course_active_student_list_drop($course_id){
    $query  = $this->db->select(['*','(select course_name from course where course.id=course_vise_student.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=course_vise_student.student_id)as student_name'])
    ->from('course_vise_student')->where('course_id',$course_id)->where('status',1)->get();
    //echo $this->db->last_query();die;
    //$query		=	  $this->db->select(['id','first_name','last_name','gender','email','userid'])->from('master_user')->where('category',2)->order_by("id", "desc")->get();
    $return 	= 	array();
    if($query->num_rows() > 0) {
        $return[''] = ('Select Student');
        foreach($query->result_array() as $row) {
            $return[$row['student_id']] = strtoupper($row['student_name']);
        }
    }
    return $return;
  }
  public function class_id_student_per_($classid){
    $query  = $this->db->select(['student_id','(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'])
    ->from('course_class_student')->where('class_id',$classid)->get();
    $res =  $query->result();
    //$array = array();
    $return 	= 	array();
    if($query->num_rows() > 0) {
        foreach($query->result_array() as $row) {
            $return[$row['student_id']] = strtoupper($row['student_name']);
        }
    }
    return $return;
  }
  public function add_course($data){
      $this->db->insert('course', $data);
      $insertid      =   $this->db->insert_id();
      return $insertid;
  }
  public function update_course_name($tag,$list_id){
      $this->db->where('id',$list_id);
      $this->db->set('course_name',$tag);
      $this->db->update('course');
      return true;
  }
  public function update_course_code($course_code,$category){
      $this->db->where('category',$category);
      $this->db->set('course_code',$course_code);
      $this->db->update('course_name');
      return true;
  }
  public function fetch_course_code($cat){
    $query  = $this->db->select(['course_code','(select category from category where category.id=course_name.category)as category'])->from('course_name')
    ->where('course_name.category',$cat)
    ->get();
    return $query->row();
  }
  public function update_userid_masteruser($id,$userid){
    $this->db->set('userid',$userid);
    $this->db->where('id',$id);
    $this->db->update('master_user');
  }
  public function update_code_usercategory($code,$usertype){
    $this->db->set('code',$code);
    $this->db->where('category',$usertype);
    $this->db->update('user_category');
  }
  public function fetch_usercategory_code($cat){
    $query  = $this->db->select(['code','category','main_code'])->from('user_category')
    ->where('user_category.category',$cat)
    ->get();
    return $query->row();
  }
  public function student_lists(){
      $query		=	  $this->db->select(['id','first_name','last_name','gender','email','userid'])->from('master_user')->where('category',2)->order_by("id", "desc")->get();
      $return 	= 	array();
      if($query->num_rows() > 0) {
          $return[''] = ('Select Student');
          foreach($query->result_array() as $row) {
              $return[$row['id']] = strtoupper($row['userid'])." | ".strtoupper($row['first_name'])." ".strtoupper($row['last_name']);
          }
      }
      return $return;
  }
  public function add_course_vise_student($data){
      $this->db->insert('course_vise_student', $data);
      $insertid      =   $this->db->insert_id();
      return $insertid;
  }
  public function update_course_vise_student($courseid,$studentid,$data_array){
      $this->db->where('course_id',$courseid);
      $this->db->where('student_id',$studentid);
      $this->db->update('course_vise_student',$data_array);
      return true;
  }

  public function count_duplicate_student_course($courseid,$studnetid){
    $query  = $this->db->select(['id'])->from('course_vise_student')
    ->where('course_id',$courseid)
    ->where('student_id',$studnetid)
    ->get();
    return $query->num_rows();
  }

  public function course_wise_faculty_list($course_id){
    $query  = $this->db->select(['v2_faculty.id','(select userid from master_user where master_user.id=v2_faculty.teacher_id)as userid',
    '(select first_name from master_user where master_user.id=v2_faculty.teacher_id)as first_name',
    '(select last_name from master_user where master_user.id=v2_faculty.teacher_id)as last_name'])->from('course')
    ->join('v2_faculty'  ,'course.subject=v2_faculty.subject_id')
    ->where('course.id'       ,$course_id)
    ->get();
    //echo $this->db->last_query();die;
    //return $query->row();
    $return 	= 	array();
    if($query->num_rows() > 0) {
        $return[''] = ('Select Faculty');
        foreach($query->result_array() as $row) {
            $return[$row['id']] = strtoupper($row['userid'])." | ".strtoupper($row['first_name'])." ".strtoupper($row['last_name']);
        }
    }
    return $return;
  }
  public function course_id_name($course_id){
    $query  = $this->db->select(['course.*','id','course_name','course_duration_hours','class_unit_price',
    '(select attendee_limit from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as attendee_limit',
    'course_start_from','week_days','(select count(id) from course_vise_student where course_id=course.id)as course_student',
    '(select concat_ws(" ",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.faculty)) as faculty',
     '(select master_user.userid from master_user where
      master_user.id=(select v2_faculty.teacher_id from v2_faculty where v2_faculty.id=course.faculty)) as facultyuserid',
     '(course.faculty) as faculty_id'
])
    ->from('course')->where('course.id',$course_id)->get();
    return $query->row();
  }
  public function subject_name_from_course($course_id){
    $query  = $this->db->select(['(select category from category where category.id=course.category)as category_id',
    '(select subject from subject where subject.id=course.subject)as subject_id',
    '(select class from class where class.id=course.class)as class_id',
      'id','course_name','course_duration_hours','class_unit_price','category','class','subject',
    '(select attendee_limit from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as attendee_limit',
    'course_start_from','week_days','(select count(id) from course_vise_student where course_id=course.id)as course_student',
    '(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.faculty)) as faculty'])
    ->from('course')->where('course.id',$course_id)->get();
    return $query->row();
  }
  public function view_faculty($facultyid){
    $query  = $this->db->select(['(select master_user.last_name from master_user where master_user.id=v2_faculty.teacher_id) as last_name',
    '(select master_user.first_name from master_user where master_user.id=v2_faculty.teacher_id) as first_name',
    '(select master_user.userid from master_user where master_user.id=v2_faculty.teacher_id) as userid'])
    ->from('v2_faculty')->where('v2_faculty.id',$facultyid)->get();
    return $query->row();
  }
  public function add_course_class_live_created($data){
      $this->db->insert('course_class_list', $data);
      $insertid      =   $this->db->insert_id();
      return $insertid;
  }
  public function student_name($list_id){
      $query  = $this->db->select(['*'])->from('master_user')->where('id',$list_id)->where('category','2')->get();
      return $query->row();
  }
  public function insert_course_class_student($data){
    $this->db->insert('course_class_student', $data);
    $insertid      =   $this->db->insert_id();
    return $insertid;
  }
  public function class_detail($classid){
        //$query  = $this->db->select(['*',
        //'(select concat_ws(" ",master_user.userid,master_user.first_name,master_user.last_name) from v2_faculty  join master_user on master_user.id=v2_faculty.teacher_id where course_class_list.faculty_id=v2_faculty.id) as faculty',
        //"(select count(*) from course_class_student where course_class_student.class_id={$classid} group by class_id)as student_inside_class"])
        //->from('course_class_list')->where('course_class_list.id',$classid)->get();
        //echo $this->db->last_query();die;

        $query  = $this->db->select(['course_class_list.*','course.course_name',
        '(select userid from v2_faculty  join master_user on master_user.id=v2_faculty.teacher_id where course_class_list.faculty_id=v2_faculty.id) as facultyuserid',
        '(select CONCAT(first_name, " ", last_name) from v2_faculty  join master_user on master_user.id=v2_faculty.teacher_id where course_class_list.faculty_id=v2_faculty.id) as faculty',
        '(select session_tag_id from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as session_name',
        '(select total_attendee from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as total_attendee',
        '(select attendee_limit from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as attendee_limit',
        '(select category from category where category.id=course.category)as category_id',
        '(select subject from subject where subject.id=course.subject)as subject_id',
        '(select sum(course_class_student.class_amount) from course_class_student where course_class_student.class_id=course_class_list.id)as earning_from_class',
        '(select count(id) from course_class_student where course_class_student.class_id=course_class_list.id group by course_class_student.class_id )as current_class_strength',
        '(select class from class where class.id=course.class)as class_id','(select count(id) from course_vise_student where course_id=course.id and status=1)as course_student',
        '(select count(id) from course_vise_student where course_id=course.id and status=0)as inactive_student'])->from('course_class_list')
        ->join('course','course.id=course_class_list.course_id','left')
        ->where('course_class_list.id',$classid)
        ->order_by("TIMESTAMP(CONCAT_WS(' ',course_class_list.class_date,course_class_list.class_start_time)) desc")
        // ->where("TIMESTAMP(CONCAT_WS(' ',v2_course_class_detail.class_date,v2_course_class_detail.class_start_time))>CURRENT_TIMESTAMP()")
        //->limit($limit, $offset)
        ->get();
      return $query->row();
  }
  public function course_class_cancel_update($method,$remarks,$classid){
    $this->db->set('method',$method);
    $this->db->set('class_status',$method);
    $this->db->set('remarks',$remarks);
    $this->db->where('id',$classid);
    $this->db->update('course_class_list');

  }
  public function modify_course_class_update($data_class_info,$classid){
    $this->db->where('id',$classid);
    $this->db->update('course_class_list',$data_class_info);
  }
  public function course_class_student_list($class_id){
    $query  = $this->db->select(['*',
    '(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'
      ])->from('course_class_student')
    ->where('course_class_student.class_id',$class_id)
    ->get();
    //echo $this->db->last_query();die;
  //  return $query->result();
  $return 	= 	array();
  if($query->num_rows() > 0) {
      //$return[''] = ('Select Faculty');
      foreach($query->result_array() as $row) {
          $return[$row['student_id']] = strtoupper($row['student_name']);
      }
  }
  return $return;
  }
  public function course_student_wallet($studnetid){
      $query = $this->db->select(['wallet_amount'])->from('student_current_wallet')->where('student_id',$studnetid)->get();
      return $query->row();
  }
  public function course_hour_unit_price($courseid){
      $query = $this->db->select(['class_unit_price'])->from('course')->where('id',$courseid)->get();
      return $query->row();
  }
  public function update_current_amount_student($amount,$studentid){
      $this->db->set('wallet_amount',$amount);
      $this->db->where('student_id',$studentid);
      $this->db->update('student_current_wallet');
      return true;
  }
  public function course_class_list_weekly($limit, $offset){
    $offset = ($offset-1)*$limit;
    $query  = $this->db->select(['*','(select CONCAT(userid," | ",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 session_tag_id from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as session_name',
    '(select total_attendee from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as total_attendee',
    '(select attendee_limit from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as attendee_limit',
    '(select count(id) from course_class_student  where course_class_student.class_id=course_class_list.id group by class_id) as today_strength_class',
    '(select category from category where category.id=course.category)as category_id',
    '(select subject from subject where subject.id=course.subject)as subject_id',
    '(select class from class where class.id=course.class)as class_id','(select count(id) from course_vise_student where course_id=course.id and status=1)as course_student',
    '(select count(id) from course_vise_student where course_id=course.id and status=0)as inactive_student'])
    ->from('course_class_list')->join('course','course.id=course_class_list.course_id')
    ->where('date(course_class_list.class_date)>=DATE_SUB(NOW(), INTERVAL 1 WEEK)')
    ->order_by('TIMESTAMP(concat_ws(" ",class_date,class_start_time)) asc')
      ->limit($limit, $offset)
      ->get();
    //echo $this->db->last_query();die;
    return $query->result();
  }
  public function course_class_num_rows_weekly(){
    $offset = ($offset-1)*$limit;
    $query  = $this->db->select(['*','(select CONCAT(userid," | ",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 session_tag_id from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as session_name',
    '(select total_attendee from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as total_attendee',
    '(select attendee_limit from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as attendee_limit',
    '(select count(id) from course_class_student  where course_class_student.class_id=course_class_list.id group by class_id) as today_strength_class',
    '(select category from category where category.id=course.category)as category_id',
    '(select subject from subject where subject.id=course.subject)as subject_id',
    '(select class from class where class.id=course.class)as class_id','(select count(id) from course_vise_student where course_id=course.id and status=1)as course_student',
    '(select count(id) from course_vise_student where course_id=course.id and status=0)as inactive_student'])
    ->from('course_class_list')->join('course','course.id=course_class_list.course_id')
    ->where('date(course_class_list.class_date)>=DATE_SUB(NOW(), INTERVAL 1 WEEK)')
      ->get();
    //echo $this->db->last_query();die;
    return $query->num_rows();
  }

  public function course_class_list_month_calender(){
    $offset = ($offset-1)*$limit;
    $query  = $this->db->select(['*','(select CONCAT(userid," | ",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 session_tag_id from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as session_name',
    '(select total_attendee from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as total_attendee',
    '(select attendee_limit from parallel_session_purchased where parallel_session_purchased.id=course.parallel_session_purchased)as attendee_limit',
    '(select count(id) from course_class_student  where course_class_student.class_id=course_class_list.id group by class_id) as today_strength_class',
    '(select category from category where category.id=course.category)as category_id',
    '(select subject from subject where subject.id=course.subject)as subject_id',
    '(select class from class where class.id=course.class)as class_id','(select count(id) from course_vise_student where course_id=course.id and status=1)as course_student',
    '(select count(id) from course_vise_student where course_id=course.id and status=0)as inactive_student'])
    ->from('course_class_list')->join('course','course.id=course_class_list.course_id')
    ->where('date(course_class_list.class_date)>=DATE_SUB(NOW(), INTERVAL 1 MONTH)')
    ->order_by('TIMESTAMP(concat_ws(" ",class_date,class_start_time)) asc')

      ->get();
    //echo $this->db->last_query();die;
    return $query->result();
  }


}
