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

class TeacherModel extends CI_Model{
    protected $limit, $offset;
    public function __construct(){
      parent::__construct();
       //$this->db->simple_query('SET SESSION group_concat_max_len=100000');
       $this->class_status      = array('create','modify');

    }
	

    public function user_profile($ts_id){

      $id          =    substr($ts_id, 16);
      $hashtag     =    substr($ts_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)->where('category','1')->get();
      return $query->row();
      //echo "<pre>";print_r($query->row());die;
      //echo $this->db->last_query();die;
    }
    public function basic_information(){
      $query=$this->db->select(['master_user.*','mu_teacher_basic.upload_resume','mu_teacher_basic.screenshot_speed','mu_teacher_basic.dob','mu_teacher_basic.aadhar_id','mu_teacher_basic.pan_id',
      'mu_teacher_basic.zip_code','mu_teacher_basic.country_id','mu_teacher_basic.state_id','mu_teacher_basic.city_id',
      '(select name from countries where countries.id=mu_teacher_basic.country_id) as countries',
      '(select name from cities where cities.id=mu_teacher_basic.city_id) as cities',
      '(select name from states where states.id=mu_teacher_basic.state_id) as states'
      ])
      ->from('master_user')
      ->join('mu_teacher_basic','mu_teacher_basic.user_id=master_user.id')
      ->where('user_id',$this->userid)
      ->get();
      return $query->row();
    }
    public function v2_faculty_list(){
        $query        =   $this->db->select(['v2_faculty.id'])->from('v2_faculty')->where('teacher_id',$this->userid)->get();
        $res          =   $query->result_array();
      //print_r($res);die;
  	  if(count($res)>0){
            foreach($res as $bs){
                $z[] =   $bs['id'];
            }
            //print_r($z);die;
  		  return $z;
  	  }else{
  		  return null;
  	  }
    }
    public function upcoming_classes_list($limit,$offset){
      $offset = ($offset-1)*$limit;
      if(!empty($this->faculty_ids[0])){
        $query =   $this->db->select(['*',
        '(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 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')->where_in('faculty_id',$this->faculty_ids)
        ->where_in('class_status',$this->class_status)->where_in('method',$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;
        $res  =   $query->result();
        return $res;
      }
      else{
        return null;
      }
    }
    public function upcoming_classes_num_row(){
      if(!empty($this->faculty_ids[0])){
        $query = $this->db->select(['*',
        '(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 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')->where_in('faculty_id',$this->faculty_ids)
        ->where_in('class_status',$this->class_status)->where_in('method',$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();
        $res  =   $query->num_rows();
        return $res;
      }
      else{
        return null;
      }
    }

    public function past_classes_list($limit,$offset){
      $offset = ($offset-1)*$limit;
      if(!empty($this->faculty_ids[0])){
        $query =   $this->db->select(['*',
        '(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 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')->where_in('faculty_id',$this->faculty_ids)
        ->where_in('class_status',$this->class_status)->where_in('method',$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;
        $res  =   $query->result();
        return $res;
      }
      else{
        return null;
      }
    }
    public function past_classes_num_row(){
      if(!empty($this->faculty_ids[0])){
        $query = $this->db->select(['*',
        '(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 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')->where_in('faculty_id',$this->faculty_ids)
        ->where_in('class_status',$this->class_status)->where_in('method',$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();
        $res  =   $query->num_rows();
        return $res;
      }
      else{
        return null;
      }
    }

    public function teacher_crs_enrld_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 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 course_name from course where course.id=course_class_list.course_id)as course_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 concat_ws(" ",course_class_list.class_date,course_class_list.class_start_time) from course_class_list where course_class_list.course_id=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 where course_class_list.course_id=course.id order by id desc limit 1)as end'])
          ->from('course_class_list')
          ->join('course','course.id=course_class_list.course_id')
          ->where_in('course_class_list.faculty_id',$this->faculty_ids)
          ->group_by('course_id')
          ->order_by("course.id","asc")->limit($limit, $offset)->get();
         // echo $this->db->last_query();die;
          return $query->result();
    }
    public function teacher_crs_enrld_list_num_row(){
        $this->db->simple_query('SET sql_mode=(SELECT REPLACE(@@sql_mode,"ONLY_FULL_GROUP_BY",""))');
        $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 course_name from course where course.id=course_class_list.course_id)as course_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 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_list')
          ->join('course','course.id=course_class_list.course_id')
          ->where_in('course_class_list.faculty_id',$this->faculty_ids)
          ->group_by('course_id')
          ->order_by("course.id","asc")->limit($limit, $offset)->get();
         // echo $this->db->last_query();die;
          return $query->num_rows();

    }

    public function course_class_list_by_courseid($limit, $offset,$courseid){
      $offset = ($offset-1)*$limit;
      $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 course_name from course where course.id=course_class_list.course_id)as course_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',
      '(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_list')->join('course','course_class_list.course_id=course.id')
      ->where_in('faculty_id',$this->faculty_ids)
      ->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')
      //->order_by('timestamp(course_class_list.class_date)','desc')
      ->limit($limit, $offset)
      ->get();
      //echo $this->db->last_query();die;
    // echo $this->db->last_query();die;
      return ($query->result());
  	}
    public function course_class_list_by_courseid_num_rows($courseid){
      $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 course_name from course where course.id=course_class_list.course_id)as course_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',
      '(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_list')->join('course','course_class_list.course_id=course.id')
      ->where_in('faculty_id',$this->faculty_ids)
      ->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))')
      ->limit($limit, $offset)
      ->get();
  		///echo $this->db->last_query();die;
  		return $query->num_rows();
  	}

    public function subject_approved_list($limit,$offset){
        $offset = ($offset-1)*$limit;
        $query  = $this->db->select(['v2_faculty.*','master_user.first_name','master_user.last_name','category.category','category.category_code',
            'class.class','class.class_code','subject.subject','subject.subject_code'])
            ->from('v2_faculty')->join('master_user','v2_faculty.teacher_id   =   master_user.id')
            ->join('category','v2_faculty.category_id=category.id')
            ->join('class','v2_faculty.class_id=class.id')
            ->join('subject','v2_faculty.subject_id=subject.id')
            ->where('teacher_id',$this->userid)->limit($limit, $offset)->get();
            // echo $this->db->last_query();die;
            return $query->result();
    }
    public function subject_approved_list_num_row(){
        $query  = $this->db->select(['v2_faculty.*','master_user.first_name','master_user.last_name','category.category','category.category_code',
            'class.class','class.class_code','subject.subject','subject.subject_code'])
        ->from('v2_faculty')->join('master_user','v2_faculty.teacher_id   =   master_user.id')
        ->join('category','v2_faculty.category_id=category.id')
        ->join('class','v2_faculty.class_id=class.id')
        ->join('subject','v2_faculty.subject_id=subject.id')
        ->where('teacher_id',$this->userid)->get();
        // echo $this->db->last_query();die;
        return $query->num_rows();
    }

    public function teacher_qual_list(){
    $query=  $this->db->select(['*'])->from('mu_teacher_qualfication')->where('user_id',$this->userid)->get();
    return $query->result();
    }
    public function qualification_listing(){

		$query		=	  $this->db->select(['id','full_name'])->from('qualification_list')->order_by("id", "asc")->get();
		$return 	= 	array();
		if($query->num_rows() > 0) {
            $return[''] = ('Qualification List');
			foreach($query->result_array() as $row) {
				$return[$row['id']] = strtoupper($row['full_name']);
			}
		}
        return $return;
    }
    public function subjectshow_listing($header){

		$query		=	  $this->db->select(['id','full_name'])->from('subject_list_show')->order_by("id", "asc")->get();
		$return 	= 	array();
		if($query->num_rows() > 0) {
			foreach($query->result_array() as $row) {
				$return[$row['id']] = strtoupper($row['full_name']);
			}
		}
        return $return;
	}
  //mu_teacher_pf_class


  public function qualification_details(){
    $query=$this->db->select(['*'])
    ->from('mu_teacher_qualfication')
    ->where('user_id',$this->userid)
    ->get();
    return $query->result();
  }

  public function preferred_classes(){
    $query=$this->db->select(['*'])
    ->from('mu_teacher_pf_class')
    ->where('user_id',$this->userid)
    ->get();
    return $query->result();
  }
  public function bank_details(){
    $query=$this->db->select(['mu_teacher_bank_detail.bank_name','mu_teacher_bank_detail.branch_name',
    'mu_teacher_bank_detail.account_holder_name','mu_teacher_bank_detail.ifsc_code','mu_teacher_bank_detail.micr_code'
    ])
    ->from('mu_teacher_bank_detail')
    ->where('user_id',$this->userid)
    ->get();
    //echo $this->db->last_query();die;
    return $query->row();
  }

  /* Date: 21-06-18  */
  public function network_info(){
    $query=$this->db->select(['mu_teacher_extra_info.current_occupation','mu_teacher_extra_info.days_available',
    'mu_teacher_extra_info.hours_week_ends_available','mu_teacher_extra_info.available_timings','mu_teacher_extra_info.total_teaching_experience','mu_teacher_extra_info.type_of_connection',
    'mu_teacher_extra_info.down_internet_conn_speed','mu_teacher_extra_info.up_internet_conn_speed','mu_teacher_extra_info.association_with_gurukul','mu_teacher_extra_info.come_to_know_tog_status'
    ])
    ->from('mu_teacher_extra_info')
    ->where('user_id',$this->userid)
    ->limit($limit, $offset)
    ->get();
    //echo $this->db->last_query();die;
    return $query->row();
  }

  public function update_teacher_master($data_teacher_master){
    $this->db->where('id',$this->userid);
    $this->db->update('master_user', $data_teacher_master);
    //echo $this->db->last_query();die;
    return true;
  }

  public function update_teacher_basic($data_teacher_basic){
    $this->db->where('user_id',$this->userid);
    $this->db->update('mu_teacher_basic', $data_teacher_basic);
    // echo $this->db->last_query();die;
    return true;
  }
  public function num_teacher_qualfication($qualification,$university,$passing_year,$grade){
      $query  = $this->db->select(['mu_teacher_qualfication.id'])
      ->from('mu_teacher_qualfication')
      ->where('qualification',$qualification)
      ->where('university',$university)
      ->where('passing_year',$passing_year)
      ->where('grade',$grade)
      ->where('user_id',$this->userid)
      ->get();
      //echo $this->db->last_query();die;
      return $query->num_rows();
  }

  public function num_teacher_pf_class($class,$primary_sub,$secondary_sub,$other,$userid){
      $query  = $this->db->select(['mu_teacher_pf_class.id'])
      ->from('mu_teacher_pf_class')
      ->where('class',$class)
      ->where('primary_sub',$primary_sub)
      ->where('secondary_sub',$secondary_sub)
      ->where('other',$other)
      ->where('user_id',$userid)
      ->get();
      //echo $this->db->last_query();die;
      return $query->num_rows();
  }
  public function update_teacher_qualfication($data_teacher_qualfication,$id){
      $this->db->where('user_id',$this->userid);
      $this->db->where('id',$id);
      $this->db->update('mu_teacher_qualfication', $data_teacher_qualfication);
      //echo $this->db->last_query();die;
      return true;
  }
  public function update_teacher_pf_class($data_teacher_preferd,$id){
      $this->db->where('user_id',$this->userid);
      $this->db->where('id',$id);
      $this->db->update('mu_teacher_pf_class', $data_teacher_preferd);
      //echo $this->db->last_query();die;
      return true;
  }
  public function update_teacher_bank_detail($bankdata){
      $this->db->where('user_id',$this->userid);
      $this->db->update('mu_teacher_bank_detail', $bankdata);
      //echo $this->db->last_query();die;
      return true;
  }
  public function update_teacher_extra_info23($extra){
      $this->db->where('user_id',$this->userid);
      $this->db->update('mu_teacher_extra_info', $extra);
      //echo $this->db->last_query();die;
      return true;
  }
  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_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;
  }


}
