<?php defined("BASEPATH")or exit("NO DIRECT ACCESS IS ALLOWED"); ?>
<?php
class AdminlteModel extends CI_Model{
    protected $limit, $offset;
    
      public function __construct(){
        parent::__construct();
        
        /*SELECT CONCAT_WS(' ',v2_course_class_detail.class_date,v2_course_class_detail.class_end_time) FROM `v2_course_class_detail` WHERE TIMESTAMP(CONCAT_WS(' ',v2_course_class_detail.class_date,v2_course_class_detail.class_end_time))>CURRENT_TIMESTAMP()  */
      }
      
      
      public function attendee_list(){
          $query=$this->db->select(['id','email','mobile','first_name','last_name','profile_image','gender','is_confirmed',
          'DATE_FORMAT(created_at,"%e-%M-%Y")as created_at'])->from('master_user')
          ->where('category','2')
          ->where('YEAR(created_at) =YEAR(CURRENT_DATE)')
          ->where('MONTH(created_at) =MONTH(CURRENT_DATE)')
          ->get();
          //echo $this->db->last_query();die;
          return $query->result();
          /*  
           * current month record
           * SELECT * FROM master_user
            WHERE YEAR(created_at) = YEAR(CURRENT_DATE)
            AND MONTH(created_at) = MONTH(CURRENT_DATE)
            */
          /*
           * last month record
           * SELECT * FROM master_user
            WHERE YEAR(created_at) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
            AND MONTH(created_at) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH) */
          /* SELECT * FROM jokes WHERE date > DATE_SUB(NOW(), INTERVAL 1 DAY) ORDER BY score DESC;        
SELECT * FROM jokes WHERE date > DATE_SUB(NOW(), INTERVAL 1 WEEK) ORDER BY score DESC;
SELECT * FROM jokes WHERE date > DATE_SUB(NOW(), INTERVAL 1 MONTH) ORDER BY score DESC;
This week:

SELECT * FROM jokes WHERE WEEKOFYEAR(date)=WEEKOFYEAR(NOW());
Last week:

SELECT * FROM jokes WHERE WEEKOFYEAR(date)=WEEKOFYEAR(NOW())-1;

down vote
Current month:

SELECT * FROM jokes WHERE YEAR(date) = YEAR(NOW()) AND MONTH(date)=MONTH(NOW());
Current week:

SELECT * FROM jokes WHERE WEEKOFYEAR(date) = WEEKOFYEAR(NOW());
Current day:

SELECT * FROM jokes WHERE YEAR(date) = YEAR(NOW()) AND MONTH(date) = MONTH(NOW()) AND DAY(date) = DAY(NOW());
 */
      }
      public function attendee_list_num_rows(){
          $query=$this->db->select(['id'])->from('master_user')->where('category','2')->get();
          return $query->num_rows();
      }
      public function attendee_num_confirmed(){
          $query=$this->db->select(['id'])->from('master_user')->where('category','2')->where('is_confirmed','1')->get();
          return $query->num_rows();
      }
      public function presenter_list(){
          $query=$this->db->select(['id','email','mobile','first_name','last_name','profile_image','gender','is_confirmed',
              'admin_approve','DATE_FORMAT(created_at,"%e-%M-%Y")as created_at'])->from('master_user')
          ->where('category','1')
          ->where('YEAR(created_at) =YEAR(CURRENT_DATE)')
          ->where('MONTH(created_at) =MONTH(CURRENT_DATE)')
          ->get();
          return $query->result();
      }
      public function presenter_list_num_rows(){
          $query=$this->db->select(['id'])->from('master_user')->where('category','1')
          ->get();
          return $query->num_rows();
      }
      public function presenter_num_confirmed(){
          $query=$this->db->select(['id'])->from('master_user')->where('category','1')->where('is_confirmed','1')->get();
          return $query->num_rows();
      }
      public function presenter_num_confirmed_approved(){
          $query=$this->db->select(['id'])->from('master_user')->where('category','1')->where('is_confirmed','1')->where('admin_approve','1')->get();
          return $query->num_rows();
      }
      public function course_num_rows_running(){
          $query  = $this->db->select(['id'])->from('v2_course_new')
          ->where("DATE(course_start_date)<=DATE(NOW())")
          ->where("DATE(course_end_date)>=DATE(NOW())")
          ->get();
          //echo $this->db->last_query();die;
          return $query->num_rows();
      }
      public function no_parallel_session_purchased(){
          $query  = $this->db->select(['id'])->from('parallel_session_purchased')
          ->get();
          return $query->num_rows();
      }
      public function student_request_for_course(){
          $query  = $this->db->select(['id'])->from('v2_student_request_program')
          ->where('YEAR(create_at) =YEAR(CURRENT_DATE)')
          ->where('MONTH(create_at) =MONTH(CURRENT_DATE)')
          ->get();
          return $query->num_rows();
      }
      public function student_course_enrolled(){
          $query  = $this->db->select(['id'])->from('v2_payment_course_student')
          ->get();
          return $query->num_rows();
      }
      public function course_list_running(){
          $query  = $this->db->select(['id','course_id_tag','course_start_date','course_end_date',
          '(select count(*) from v2_course_class_detail where v2_course_class_detail.course_id=v2_course_new.id)as total_classes',
          ]) ////and TIME(v2_course_class_detail.class_end_time)<CURRENT_TIME()
          ->from('v2_course_new')
          ->where("DATE(course_start_date)<=DATE(NOW())")
          ->where("DATE(course_end_date)>=DATE(NOW())")
          ->order_by('DATE(course_start_date)','asc')
          ->get();
         // echo $this->db->last_query();die;
          return $query->result();
      }
      
      public function classes_num_rows_over_course($course_id){
          $query  = $this->db->select(['v2_course_class_detail.id'])
          ->from('v2_course_class_detail')
          //->where("DATE(v2_course_class_detail.class_date)<=CURRENT_TIME()")
          //->where("TIME(v2_course_class_detail.class_end_time)<CURRENT_TIME()")
          ->where("DATE(v2_course_class_detail.class_date)<=CURDATE()")
          ->where("v2_course_class_detail.course_id",$course_id)
          ->get();
         // echo $this->db->last_query();die;
          return $query->num_rows();
      }
      
      public function classes_num_rows_remaining_course($course_id){
          $query  = $this->db->select(['v2_course_class_detail.id'])
          ->from('v2_course_class_detail')
          //->where("DATE(v2_course_class_detail.class_date)<=CURRENT_TIME()")
          ///->where("TIME(v2_course_class_detail.class_end_time) < CURRENT_TIME()")
          ->where("DATE(v2_course_class_detail.class_date)>=CURRENT_TIME()")
          ->where("v2_course_class_detail.course_id",$course_id)
          ->get();
          //echo $this->db->last_query();die;
          return $query->num_rows();
      }
      
      public function classes_num_rows_running_today_now_course($course_id){
          $query  = $this->db->select(['v2_course_class_detail.id'])
          ->from('v2_course_class_detail')
          ->where("TIME(v2_course_class_detail.class_start_time)<=CURRENT_TIME()")
          ->where("TIME(v2_course_class_detail.class_end_time)>=CURRENT_TIME()")
          ->where("DATE(v2_course_class_detail.class_date)=CURDATE()")
          ->where("v2_course_class_detail.course_id",$course_id)
          ->get();
         // echo $this->db->last_query();die;
          return $query->num_rows();
      }
      
      
      public function course_num_rows_completed(){
          $query  = $this->db->select(['id'])->from('v2_course_new')
          ->where("DATE(course_start_date)< DATE(NOW())")
          ->where("DATE(course_end_date)< DATE(NOW())")
          ->get();
          //echo $this->db->last_query();die;
          return $query->num_rows();
      }
      public function course_num_rows_remaning(){
          $query  = $this->db->select(['id'])->from('v2_course_new')
          ->where("DATE(course_start_date) > DATE(NOW())")
          ->get();
          //echo $this->db->last_query();die;
          return $query->num_rows();
      }
      
      public function classes_num_rows_(){
          $query  = $this->db->select(['v2_course_class_detail.id'])
          ->from('v2_course_class_detail')
          ->get();
         // echo $this->db->last_query();die;
          return $query->num_rows();
      }
      public function classes_num_rows_over(){
          $query  = $this->db->select(['v2_course_class_detail.id'])
          ->from('v2_course_class_detail')
          //->where("DATE(v2_course_class_detail.class_date)<=CURRENT_TIME()")
          ->where("TIME(v2_course_class_detail.class_end_time)<CURRENT_TIME()")
          ->where("DATE(v2_course_class_detail.class_date)<=CURDATE()")
          ->get();
          //echo $this->db->last_query();die;
          return $query->num_rows();
      }
      public function classes_num_rows_remaining(){
          $query  = $this->db->select(['v2_course_class_detail.id'])
          ->from('v2_course_class_detail')
          //->where("DATE(v2_course_class_detail.class_date)<=CURRENT_TIME()")
          //->where("TIME(v2_course_class_detail.class_end_time)>=CURRENT_TIME()")
          ->where("DATE(v2_course_class_detail.class_date)>=CURDATE()")
          ->get();
          //echo $this->db->last_query();die;
          return $query->num_rows();
      }
      
      public function classes_num_rows_running_today_now(){
          $query  = $this->db->select(['v2_course_class_detail.id'])
          ->from('v2_course_class_detail')
          ->where("TIME(v2_course_class_detail.class_start_time)<=CURRENT_TIME()")
          ->where("TIME(v2_course_class_detail.class_end_time)>=CURRENT_TIME()")
          ->where("DATE(v2_course_class_detail.class_date)=CURDATE()")
          ->get();
         // echo $this->db->last_query();die;
          return $query->num_rows();
      }
      
      public function classes_num_rows_today(){
          $query  = $this->db->select(['v2_course_class_detail.id'])
          ->from('v2_course_class_detail')
          ->where("DATE(v2_course_class_detail.class_date)=CURDATE()")
          ->get();
          //echo $this->db->last_query();die;
          return $query->num_rows();
      }
      public function classes_today_report_upcomming_classes(){
         $query  = $this->db->select(['v2_course_class_detail.*',"(select course_id_tag from v2_course_new where v2_course_class_detail.course_id=v2_course_new.id)as course_id_tag",
          "GROUP_CONCAT(v2_course_student_data.attendee_id SEPARATOR ',')as attendee",
              '(select CONCAT(first_name, " ", last_name) from v2_faculty  join master_user on master_user.id=v2_faculty.teacher_id
                where v2_course_presenter_data.class_presenter=v2_faculty.id) as faculty',
             '(select category from category where category.id=v2_course_class_detail.category_id)as category',
             '(select class from class where class.id=v2_course_class_detail.class_id)as class',
             '(select subject from subject where subject.id=v2_course_class_detail.subject_id)as subject',
             "(select course_start_date from v2_course_new where v2_course_class_detail.course_id=v2_course_new.id)as course_start_date",
             "(select course_end_date from v2_course_new where v2_course_class_detail.course_id=v2_course_new.id)as course_end_date",
          ])
          ->from('v2_course_class_detail')
          ->group_by('v2_course_student_data.class_id')
          ->join('v2_course_student_data','v2_course_student_data.class_id=v2_course_class_detail.id')
          ->join('v2_course_presenter_data','v2_course_presenter_data.class_id=v2_course_class_detail.id')
          ->where("DATE(v2_course_class_detail.class_date)=CURDATE()")
          //->where("TIMESTAMP(CONCAT_WS(' ',v2_course_class_detail.class_date,v2_course_class_detail.class_start_time))>CURRENT_TIMESTAMP()") 
          
          ->get();
           // echo $this->db->last_query();die;
           
          return $query->result();
      }
      public function classes_today_report_upcomming_classes_num_row(){
          $query  = $this->db->select(['v2_course_class_detail.*',"(select course_id_tag from v2_course_new where v2_course_class_detail.course_id=v2_course_new.id)as course_id_tag",
              "GROUP_CONCAT(v2_course_student_data.attendee_id SEPARATOR ',')as attendee",
              '(select CONCAT(first_name, " ", last_name) from v2_faculty  join master_user on master_user.id=v2_faculty.teacher_id
                where v2_course_presenter_data.class_presenter=v2_faculty.id) as faculty',
              '(select category from category where category.id=v2_course_class_detail.category_id)as category',
              '(select class from class where class.id=v2_course_class_detail.class_id)as class',
              '(select subject from subject where subject.id=v2_course_class_detail.subject_id)as subject',
              "(select course_start_date from v2_course_new where v2_course_class_detail.course_id=v2_course_new.id)as course_start_date",
              "(select course_end_date from v2_course_new where v2_course_class_detail.course_id=v2_course_new.id)as course_end_date",
          ])
          ->from('v2_course_class_detail')
          ->group_by('v2_course_student_data.class_id')
          ->join('v2_course_student_data','v2_course_student_data.class_id=v2_course_class_detail.id')
          ->join('v2_course_presenter_data','v2_course_presenter_data.class_id=v2_course_class_detail.id')
          ->where("DATE(v2_course_class_detail.class_date)=CURDATE()")
          //->where("TIMESTAMP(CONCAT_WS(' ',v2_course_class_detail.class_date,v2_course_class_detail.class_start_time))>CURRENT_TIMESTAMP()"
          ->get();
                //  echo $this->db->last_query();die;
                
                return $query->num_rows();
      }
      public function classes_num_rows_today_over(){
          $query  = $this->db->select(['v2_course_class_detail.id'])
          ->from('v2_course_class_detail')
          ->where("TIME(v2_course_class_detail.class_end_time)<CURRENT_TIME()")
          ->where("TIMESTAMP(CONCAT_WS(' ',v2_course_class_detail.class_date,v2_course_class_detail.class_start_time))<CURRENT_TIMESTAMP()") 
          ->where("DATE(v2_course_class_detail.class_date)=CURDATE()")
          ->get();
          //echo $this->db->last_query();die;
          return $query->num_rows();
      }
      public function classes_num_rows_today_remaining(){
          $query  = $this->db->select(['v2_course_class_detail.id'])
          ->from('v2_course_class_detail')
          ->where("TIMESTAMP(CONCAT_WS(' ',v2_course_class_detail.class_date,v2_course_class_detail.class_start_time))>CURRENT_TIMESTAMP()") 
          ->where("DATE(v2_course_class_detail.class_date)=CURDATE()")
          ->get();
          //echo $this->db->last_query();die;
          return $query->num_rows();
      }
      public function classes_num_rows_this_week(){
         /*  $query  = $this->db->select(['v2_course_class_detail.id'])
          ->from('v2_course_class_detail')
          ->where("DATE(v2_course_class_detail.class_date)> DATE_SUB(NOW(), INTERVAL 1 WEEK);")
          ->get(); */
          $query  = $this->db->select(['v2_course_class_detail.id'])
          ->from('v2_course_class_detail')
          ->where("WEEKOFYEAR(v2_course_class_detail.class_date)= WEEKOFYEAR(NOW())")
          ->get();
         // echo $this->db->last_query();die;
          return $query->num_rows();
      }
      public function classes_num_rows_last_week(){
          $query  = $this->db->select(['v2_course_class_detail.id'])
          ->from('v2_course_class_detail')
          ->where("WEEKOFYEAR(v2_course_class_detail.class_date)= WEEKOFYEAR(NOW())-1")
          ->get();
          //echo $this->db->last_query();die;
          return $query->num_rows();
      }
      public function classes_num_rows_next_week(){
          $query  = $this->db->select(['v2_course_class_detail.id'])
          ->from('v2_course_class_detail')
          ->where("WEEKOFYEAR(v2_course_class_detail.class_date)= WEEKOFYEAR(NOW())+1")
          ->get();
          //echo $this->db->last_query();die;
          return $query->num_rows();
      }
      public function classes_num_rows_this_month(){
          /* $query  = $this->db->select(['v2_course_class_detail.id'])
          ->from('v2_course_class_detail')
          ->where('YEAR(v2_course_class_detail.class_date) =YEAR(CURRENT_DATE)')
          ->where('MONTH(v2_course_class_detail.class_date) =MONTH(CURRENT_DATE)')
          ->get(); */
          $query  = $this->db->select(['v2_course_class_detail.id'])
          ->from('v2_course_class_detail')
          ->where("MONTH(v2_course_class_detail.class_date)= MONTH(NOW())")
          ->get();
          //echo $this->db->last_query();die;
          return $query->num_rows();
      }
      public function classes_num_rows_last_month(){
          $query  = $this->db->select(['v2_course_class_detail.id'])
          ->from('v2_course_class_detail')
          ->where("MONTH(v2_course_class_detail.class_date)= MONTH(NOW())-1")
          ->get();
          //echo $this->db->last_query();die;
          return $query->num_rows();
      }
      public function classes_num_rows_next_month(){
          $query  = $this->db->select(['v2_course_class_detail.id'])
          ->from('v2_course_class_detail')
          ->where("MONTH(v2_course_class_detail.class_date)= MONTH(NOW())+1")
          ->get();
          //echo $this->db->last_query();die;
          return $query->num_rows();
      }
      public function classes_num_rows_this_year(){
          $query  = $this->db->select(['v2_course_class_detail.id'])
          ->from('v2_course_class_detail')
          ->where("YEAR(v2_course_class_detail.class_date)= YEAR(NOW())")
          ->get();
          //echo $this->db->last_query();die;
          return $query->num_rows();
      }
      public function classes_num_rows_last_year(){
          $query  = $this->db->select(['v2_course_class_detail.id'])
          ->from('v2_course_class_detail')
          ->where("YEAR(v2_course_class_detail.class_date)= YEAR(NOW())-1")
          ->get();
          //echo $this->db->last_query();die;
          return $query->num_rows();
      }
      public function classes_num_rows_next_year(){
          $query  = $this->db->select(['v2_course_class_detail.id'])
          ->from('v2_course_class_detail')
          ->where("YEAR(v2_course_class_detail.class_date)= YEAR(NOW())+1")
          ->get();
          //echo $this->db->last_query();die;
          return $query->num_rows();
      }
      public function classes_num_rows_cancel_modify_create_this_month($method){
          $query  = $this->db->select(['v2_course_class_detail.id'])
          ->from('v2_course_class_detail')
          ->where("MONTH(v2_course_class_detail.class_date)= MONTH(NOW())")
          ->where("v2_course_class_detail.method",$method)
          ->get();
          //echo $this->db->last_query();die;
          return $query->num_rows();
      }
      public function classes_num_rows_cancel_modify_create_this_year($method){
          $query  = $this->db->select(['v2_course_class_detail.id'])
          ->from('v2_course_class_detail')
          ->where("YEAR(v2_course_class_detail.class_date)= YEAR(NOW())")
          ->where("v2_course_class_detail.method",$method)
          ->get();
          //echo $this->db->last_query();die;
          return $query->num_rows();
      }
     
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      public function login_valid($email){
          $query  =   $this->db->select(['id','email','password'])->where('email',$email)->from('admin')->get();
          if($query->num_rows()>0){
              $row = $query->row();
              return $row;
          }
          else{
              return false;
          }
      }
      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('admin');
          return true;
      }
      public function user_profile($id){
          //echo $id."<br>";//echo $hashtag."<br>";//echo $ss_id."<br>";die;
          $query       =  $this->db->select(['first_name','last_name','email','profile_image','designation'])->from('admin')
          ->where('id',$id)->get();
          return $query->row();
          //echo "<pre>";print_r($query->row());die;
      }
      public function coupon_lists(){
          $query		=	  $this->db->select(['id','coupon_category','percent','amount','status'])->from('coupon_category')
          ->where('status','1')->order_by("id", "desc")->get();
          $return 	= 	array();
          if($query->num_rows() > 0) {
              $return[''] = ('Select Coupon');
              foreach($query->result_array() as $row) {
                  $return[$row['id']] = ucwords($row['coupon_category'])." | ".strtolower($row['percent']." %"." | ".$row['amount']." Rs.");
              }
          }
          return $return;
      }
      public function student_lists(){
          $query		=	  $this->db->select(['id','email','first_name','last_name','mobile','gender'])->from('master_user')
          ->where('master_user.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']] = ucwords($row['first_name']." ".$row['last_name']).
                  " | ".strtolower($row['email']." | ".$row['mobile']." | ".$row['gender']);
              }
          }
          return $return;
      }
      public function category_lists(){
          $query		=	  $this->db->select(['id','category'])->from('category')->order_by("id", "desc")->get();
          $return 	= 	array();
          if($query->num_rows() > 0) {
              $return[''] = ('Select category');
              foreach($query->result_array() as $row) {
                  $return[$row['id']] = strtoupper($row['category']);
              }
          }
          return $return;
      }
      public function class_lists(){
          $query		=	  $this->db->select(['id','class'])->from('class')->order_by("id", "asc")->get();
          $return 	= 	array();
          if($query->num_rows() > 0) {
              $return[''] = ('Select class');
              foreach($query->result_array() as $row) {
                  $return[$row['id']] = strtoupper($row['class']);
              }
          }
          return $return;
      }
	   public function class_code_lists(){
          $query		=	  $this->db->select(['class.id','class.class','class.class_code','category.category',
		  'category.category_code'])
		  ->from('class')
		  ->join('category','category.id=class.category_id')->order_by("class.id", "asc")->get();
		 //echo "<pre>"; print_r($query->result_array());die;
		  
          $return 	= 	array();
          if($query->num_rows() > 0) {
              $return[''] = ('Select class code');
              foreach($query->result_array() as $row) {
                  $return[$row['category_code'].$row['class_code']] = strtoupper($row['category_code'].$row['class_code']);
              }
          }
          return $return;
      }
      public function subject_lists(){
          $query		=	  $this->db->select(['id','subject'])->from('subject')->order_by("id", "asc")->get();
          $return 	= 	array();
          if($query->num_rows() > 0) {
              $return[''] = ('Select subject');
              foreach($query->result_array() as $row) {
                  $return[$row['id']] = strtoupper($row['subject']);
              }
          }
          return $return;
      }
	  
	  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['category_code'].$row['class_code'].$row['subject_code']] = strtoupper($row['category_code'].$row['class_code'].$row['subject_code']);
              }
          }
          return $return;
      }
	  
	   public function chapter_lists(){
          $query		=	  $this->db->select(['id','chapter'])->from('chapter')->order_by("id", "asc")->get();
          $return 	= 	array();
          if($query->num_rows() > 0) {
              $return[''] = ('Select chapter');
              foreach($query->result_array() as $row) {
                  $return[$row['id']] = strtoupper($row['chapter']);
              }
          }
          return $return;
      }
	  

	   public function chapter_code_lists(){
          $query		=	  $this->db->select(['chapter.id','chapter.chapter','chapter.chapter_code','category.category','category.category_code','class.class','class.class_code',
		'subject.subject','subject.subject_code'])
		  ->from('chapter')
		  ->join('category','chapter.category_id=category.id')
		  ->join('class','chapter.class_id=class.id')
		  ->join('subject','chapter.subject_id=subject.id')
		  ->order_by("chapter.id", "asc")->get();
		//echo "<pre>"; print_r($query->result_array());die;
		  
          $return 	= 	array();
          if($query->num_rows() > 0) {
              $return[''] = ('Select chapter code');
              foreach($query->result_array() as $row) {
                  $return[$row['category_code'].$row['class_code'].$row['subject_code'].$row['chapter_code']] = strtoupper($row['category_code'].$row['class_code'].$row['subject_code'].$row['chapter_code']);
              }
          }
          return $return;
      }
	  
	   public function topic_lists(){
          $query		=	  $this->db->select(['id','topic'])->from('topic')->order_by("id", "asc")->get();
          $return 	= 	array();
          if($query->num_rows() > 0) {
              $return[''] = ('Select topic');
              foreach($query->result_array() as $row) {
                  $return[$row['id']] = strtoupper($row['topic']);
              }
          }
          return $return;
      }
	  
	    public function topic_code_lists(){
          $query		=	  $this->db->select(['topic.id','topic.topic','topic.topic_code','category.category','category.category_code','class.class','class.class_code',
		'subject.subject','subject.subject_code','chapter.chapter'])
		  ->from('topic')
		  ->join('category','topic.category_id=category.id')
		  ->join('class','topic.class_id=class.id')
		  ->join('subject','topic.subject_id=subject.id')
		  ->join('chapter','topic.chapter_id=chapter.id')
		  ->order_by("topic.id", "asc")->get();
		//echo "<pre>"; print_r($query->result_array());die;
		  
          $return 	= 	array();
          if($query->num_rows() > 0) {
              $return[''] = ('Select Topic code');
              foreach($query->result_array() as $row) {
                  $return[$row['category_code'].$row['class_code'].$row['subject_code'].$row['topic_code']] = strtoupper($row['category_code'].$row['class_code'].$row['subject_code'].$row['topic_code']);
              }
          }
          return $return;
      }
	  
	  
	   public function faculty_lists(){
          $query  =	 $this->db->select(['category.category','category.category_code','class.class','class.class_code',
		    'subject.subject','subject.subject_code'])
		  ->from('v2_faculty')
		  ->join('category','v2_faculty.category_id=category.id')
		  ->join('class','v2_faculty.class_id=class.id')
		  ->join('subject','v2_faculty.subject_id=subject.id')
		  ->order_by('v2_faculty.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['category_code'].$row['class_code'].$row['subject_code']] = strtoupper($row['category_code'].$row['class_code'].$row['subject_code']);
              }
          }
          return $return;
      } 
      function getcategory(){
          $response = array();
          $this->db->select('*');
          $q = $this->db->get('category');
          $response = $q->result_array();
          return $response;
      }
      
      function getclass(){
          $response = array();
          $this->db->select('*');
          $q = $this->db->get('class');
          $response = $q->result_array();
          return $response;
      }
      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;
      }
      // Get City departments
      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;
      }
      
      // Get Department user
      function get_class_subject_faculty($postData){
         // echo "<pre>";print_r($postData['subject']);die;
          $response     = array();
          $query  = $this->db->select(['v2_faculty.*','category.category','category.category_code','class.class','class.class_code',
              'subject.subject','subject.subject_code','master_user.first_name','master_user.last_name','master_user.email'])
              ->from('v2_faculty')
              ->join('category','v2_faculty.category_id=category.id')
              ->join('class','v2_faculty.class_id=class.id')
              ->join('subject','v2_faculty.subject_id=subject.id')
              ->join('master_user','v2_faculty.teacher_id=master_user.id')
              ->where('subject_id', $postData['subject'])->get();
         // $this->db->select('*');
         // $this->db->where('subject_id', $postData['subject']);
          //$q            = $this->db->get('v2_faculty');
              $response     = $query->result_array();
          return $response;
      }
      // Get Department user
      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();
              // $this->db->select('*');
              // $this->db->where('subject_id', $postData['subject']);
              //$q            = $this->db->get('v2_faculty');
              $response     = $query->result_array();
              return $response;
      }
      function get_class_subject_course_classid_list($postData){
          // echo "<pre>";print_r($postData['subject']);die;
          $response     = array();
          $query  = $this->db->select(['v2_course_class_detail.*','category.category','class.class','subject.subject',
              '(select CONCAT(first_name, " ", last_name) from v2_faculty  join master_user on master_user.id=v2_faculty.teacher_id 
                where v2_course_presenter_data.class_presenter=v2_faculty.id) as faculty',
              '(select DATE_FORMAT(v2_course_class_detail.class_date,"%W | %D - %M - %Y")) as class_date2',
              '(select TIME_FORMAT(v2_course_class_detail.class_start_time,"%h : %i %p")) as class_start_time2',
              '(select TIME_FORMAT(v2_course_class_detail.class_end_time,"%h : %i %p")) as class_end_time2'
          ])
          ->from('v2_course_class_detail')
          ->join('v2_course_presenter_data','v2_course_presenter_data.class_id=v2_course_class_detail.id')
          
          ->join('category','v2_course_class_detail.category_id=category.id')
          ->join('class','v2_course_class_detail.class_id=class.id')
          ->join('subject','v2_course_class_detail.subject_id=subject.id')
          ->where("TIMESTAMP(CONCAT_WS(' ',v2_course_class_detail.class_date,v2_course_class_detail.class_start_time))>CURRENT_TIMESTAMP()") 
          ->where('course_id', $postData['courseid'])->get();
          // $this->db->select('*');
          // $this->db->where('subject_id', $postData['subject']);
          //$q            = $this->db->get('v2_faculty');
          $response     = $query->result_array();
          return $response;
      }
      
       function get_class_amount_info($postData){
          $response     = array();
          $query  = $this->db->select(['v2_n.id','v2_n.course_id_tag','v2_n.per_class_amount',
              "(select count(id) from v2_course_class_detail where v2_course_class_detail.course_id= {$postData['course_id']})as total_classes",
              "(select count(id) * v2_n.per_class_amount from v2_course_class_detail where v2_course_class_detail.course_id= {$postData['course_id']})as total_amount"
              ])
          ->from('v2_course_new as v2_n')
          ->where('v2_n.id', $postData['course_id'])->get();
          $response     = $query->row();
          return $response;
      } 
      function get_class_amount_info_after_over_class($postData){
          $response     = array();
          $query  = $this->db->select(['id','course_id',"(select per_class_amount from v2_course_new where v2_course_new.id={$postData['course_id']})as per_class_amount",
              "(select count(*) from v2_course_class_detail where TIMESTAMP(CONCAT_WS(' ',class_date,class_start_time))>CURRENT_TIMESTAMP() and course_id={$postData['course_id']})as total_classes_remain",
              "(select count(*) * v2_course_new.per_class_amount from v2_course_class_detail join v2_course_new on v2_course_new.id=v2_course_class_detail.course_id where TIMESTAMP(CONCAT_WS(' ',v2_course_class_detail.class_date,v2_course_class_detail.class_start_time))>CURRENT_TIMESTAMP() and course_id={$postData['course_id']})as current_course_amount",
              ])
              ->from('v2_course_class_detail')
              ->group_by('course_id')
              ->where('id', $postData['course_id'])
              //->where("TIME(v2_course_class_detail.class_end_time)<CURRENT_TIME()")
              //->where("TIMESTAMP(CONCAT_WS(' ',v2_n.class_date,v2_n.class_end_time))>CURRENT_TIMESTAMP()") 
          ->get();
          //echo $this->db->last_query();die;
          $response     = $query->row();
              return $response;
      } 
     
      function get_class_subject_course_maded_info($postData){
          // echo "<pre>";print_r($postData['subject']);die;
          $response     = array();
          $query  = $this->db->select(['v2_n.id','v2_n.course_id_tag',
              'DATE_FORMAT(v2_n.course_start_date,"%e-%M-%Y")as course_start_date',
              'DATE_FORMAT(v2_n.course_end_date,"%e-%M-%Y")as course_end_date','v2_n.subject_code',
              'v2_n.class_count','v2_n.per_class_amount',
              //'v2_n.faculty_code',
              '(select CONCAT(first_name, " ", last_name) from v2_faculty  join master_user on master_user.id=v2_faculty.teacher_id where v2_n.faculty_code=v2_faculty.id) as faculty_code',
               'category.category','class.class','subject.subject',
               'v2.class_tag_id','v2.class_strength','DATE_FORMAT(v2.class_date, "%e-%M-%Y %W")as class_date',
               'TIME_FORMAT(v2.class_start_time,"%h:%i %p")as class_start_time','TIME_FORMAT(v2.class_end_time,"%h:%i %p")as class_end_time',
               'v2.class_duration_min','v2.class_extend_time_min','v2.method'
              //,'(select v2_n.per_class_amount * v2_n.class_count  from v2_n)as total_amount_course'
              
              
           ])
          ->from('v2_course_new as v2_n')
          ->join('v2_course_class_detail as v2','v2.course_id=v2_n.id')
          ->join('category','v2_n.category_id=category.id')
          ->join('class','v2_n.class_id=class.id')
          ->join('subject','v2_n.subject_id=subject.id')
          ->where('v2_n.id', $postData['course_id'])->get(); 
           
          $response     = $query->result_array();
          return $response;
      }
      
      
      public function insert_v2_course_student_data($data){
          $this->db->insert('v2_course_student_data', $data);
          $insertid      =   $this->db->insert_id();
          return $insertid;
      }
      public function update_v2_course_class_detail_34($classid,$class_strength){
          $this->db->where('id',$classid);
          $this->db->set('class_strength',$class_strength);
          $this->db->update('v2_course_class_detail');
          return true;
      }
      public function fetch_class_strength_detail_respect_to_class($classid){
          
          $query  = $this->db->select(['class_tag_id','course_id','class_strength'])->from('v2_course_class_detail')
          ->where('v2_course_class_detail.id',$classid)
          ->get();
          return $query->row();
      }
      
      public function insert_v2_course_new($data){
          $this->db->insert('v2_course_new', $data);
          $insertid      =   $this->db->insert_id();
          return $insertid;
      }
      public function insert_v2_course_new_furthur_extended($data){
          $this->db->insert('v2_course_new_furthur_extended', $data);
          $insertid      =   $this->db->insert_id();
          return $insertid;
      }
      public function update_v2_course_new_field_value($field,$value,$list_id){
          $this->db->where('id',$list_id);
          $this->db->set($field,$value);
          $this->db->update('v2_course_new');
          return true;
      }
      public function update_v2_course_new_furthur_extended_field_value($field,$value,$list_id){
          $this->db->where('main_course_id',$list_id);
          $this->db->set($field,$value);
          $this->db->update('v2_course_new_furthur_extended');
          return true;
      }
      
      public function update_v2_course_new_coursetag($tag,$list_id){
          $this->db->where('id',$list_id);
          $this->db->set('course_id_tag',$tag);
          $this->db->update('v2_course_new');
          return true;
      }
      
      public function subject_name($list_id){
          $query  = $this->db->select(['subject','class'])->from('subject')->join('class','class.id=subject.class_id')->where('subject.id',$list_id)->get();
          return $query->row();
      }
      public function insert_v2_course_class_detail($data){
          $this->db->insert('v2_course_class_detail', $data);
          $courseid      =   $this->db->insert_id();
          return $courseid;
      }
      public function insert_v2_course_presenter_data($data){
          $this->db->insert('v2_course_presenter_data', $data);
          $courseid      =   $this->db->insert_id();
          return $courseid;
      }
      public function update_v2_course_presenter_data($class_id,$faculty){
          $this->db->set('class_presenter',$faculty);
          $this->db->where('class_id',$class_id);
          $this->db->update('v2_course_presenter_data');
          return true;
      }
      public function update_v2_course_new_class_count($count,$list_id){
          $this->db->where('id',$list_id);
          $this->db->set('class_count',$count);
          $this->db->update('v2_course_new');
          return true;
      }
      public function update_v2_course_new_class_method($method,$method_info,$class_tag_id){
          $this->db->where('class_tag_id',$class_tag_id);
          $this->db->set('method',$method);
          $this->db->set('method_info',$method_info);
          $this->db->update('v2_course_class_detail');
          return true;
      }
      
      public function course_list($limit, $offset){
          $offset = ($offset-1)*$limit;
          $query  = $this->db->select(['id','course_id_tag','course_id_tag',
              'course_duration','course_start_date','course_end_date',
              '(select CONCAT(first_name, " ", last_name) from v2_faculty  join master_user on master_user.id=v2_faculty.teacher_id where v2_course_new.faculty_code=v2_faculty.id) as faculty',
              '(select category from category where category.id=v2_course_new.category_id)as category_id',
              '(select subject from subject where subject.id=v2_course_new.subject_id)as subject_id',
              '(select class from class where class.id=v2_course_new.class_id)as class_id','subject_code','class_start_time','class_end_time','class_duration_min',
              'class_extend_time_min','faculty_code','per_class_amount','class_count','course_custom_date',
              'course_week_days','time_zone'])->from('v2_course_new')->order_by("id","desc")->limit($limit, $offset)->get();
              //echo $this->db->last_query();die;
              return $query->result();
      }
      // Count Num ROWS
      public function course_num_rows(){
          $query  = $this->db->select(['id','course_id_tag','course_id_tag',
              'course_duration','course_start_date','course_end_date',
              '(select CONCAT(first_name, " ", last_name) from v2_faculty  join master_user on master_user.id=v2_faculty.teacher_id where v2_course_new.faculty_code=v2_faculty.id) as faculty',
              '(select category from category where category.id=v2_course_new.category_id)as category_id',
              '(select subject from subject where subject.id=v2_course_new.subject_id)as subject_id',
              '(select class from class where class.id=v2_course_new.class_id)as class_id','subject_code','class_start_time','class_end_time','class_duration_min',
              'class_extend_time_min','faculty_code','per_class_amount','class_count','course_custom_date',
              'course_week_days','time_zone'])->from('v2_course_new')->order_by("id","desc")->get();
              return $query->num_rows();
      }
     
      
      
      public function course_view($courseid){
          $query  = $this->db->select(['id','course_id_tag','course_id_tag',
              'course_duration','course_start_date','course_end_date',
              '(select CONCAT(first_name, " ", last_name) from v2_faculty  join master_user on master_user.id=v2_faculty.teacher_id where v2_course_new.faculty_code=v2_faculty.id) as faculty',
              '(select category from category where category.id=v2_course_new.category_id)as category',
              '(select subject from subject where subject.id=v2_course_new.subject_id)as subject',
              '(select class from class where class.id=v2_course_new.class_id)as class',
              'category_id','class_id','subject_id',
              'subject_code','class_start_time','class_end_time','class_duration_min',
              'class_extend_time_min','faculty_code','per_class_amount','class_count','course_custom_date',
              'course_week_days','time_zone'])->from('v2_course_new')->where('v2_course_new.id',$courseid)
                ->order_by("id","desc")->get();
          //echo $this->db->last_query();die;
          return $query->row();
      }
      public function course_class_list($limit, $offset,$courseid){
          $offset = ($offset-1)*$limit;
          $query  = $this->db->select(['v2_course_class_detail.id','course_id','class_tag_id','class_date',
              '(select course_start_date from v2_course_new where v2_course_class_detail.course_id=v2_course_new.id) as course_start_date',
              '(select course_end_date from v2_course_new where v2_course_class_detail.course_id=v2_course_new.id) as course_end_date',
              '(select course_id_tag from v2_course_new where v2_course_class_detail.course_id=v2_course_new.id) as course_id_tag',
              'class_strength','recording_url','method', 
              '(select CONCAT(first_name, " ", last_name) from v2_faculty  join master_user on master_user.id=v2_faculty.teacher_id where v2_course_presenter_data.class_presenter=v2_faculty.id) as faculty',
              '(select category from category where category.id=v2_course_class_detail.category_id)as category_id',
              '(select subject from subject where subject.id=v2_course_class_detail.subject_id)as subject_id',
              '(select class from class where class.id=v2_course_class_detail.class_id)as class_id','subject_code',
              'class_start_time','class_end_time','class_duration_min','class_extend_time_min','time_zone','presenter_url'])
              ->from('v2_course_class_detail')
          ->join('v2_course_presenter_data','v2_course_presenter_data.class_id=v2_course_class_detail.id')
          ->where('v2_course_class_detail.course_id',$courseid)->order_by("v2_course_class_detail.id","desc")->limit($limit, $offset)->get();
          //echo $this->db->last_query();die;
          return $query->result();
      }
      // Count Num ROWS
      public function course_class_num_rows($courseid){
          $query  = $this->db->select(['v2_course_class_detail.id','course_id','class_tag_id','class_date',
              '(select course_start_date from v2_course_new where v2_course_class_detail.course_id=v2_course_new.id) as course_start_date',
              '(select course_end_date from v2_course_new where v2_course_class_detail.course_id=v2_course_new.id) as course_end_date',
              '(select course_id_tag from v2_course_new where v2_course_class_detail.course_id=v2_course_new.id) as course_id_tag',
              'class_strength','recording_url','method',
              '(select CONCAT(first_name, " ", last_name) from v2_faculty  join master_user on master_user.id=v2_faculty.teacher_id where v2_course_presenter_data.class_presenter=v2_faculty.id) as faculty',
              '(select category from category where category.id=v2_course_class_detail.category_id)as category_id',
              '(select subject from subject where subject.id=v2_course_class_detail.subject_id)as subject_id',
              '(select class from class where class.id=v2_course_class_detail.class_id)as class_id','subject_code',
              'class_start_time','class_end_time','class_duration_min','class_extend_time_min','time_zone','presenter_url'])
              ->from('v2_course_class_detail')
              ->join('v2_course_presenter_data','v2_course_presenter_data.class_id=v2_course_class_detail.id')
              ->where('v2_course_class_detail.course_id',$courseid)->order_by("v2_course_class_detail.id","desc")->get();
          return $query->num_rows();
      }
      
      
      
      public function class_id_view($class_id){
          $query  = $this->db->select(['v2_course_class_detail.*',
              '(select category from category where category.id=category_id)as category',
              '(select subject from subject where subject.id=subject_id)as subject',
              '(select class from class where class.id=v2_course_class_detail.class_id)as class',
              'class_presenter',
              '(select CONCAT(first_name, " ", last_name) from v2_faculty  join master_user on master_user.id=v2_faculty.teacher_id where v2_course_presenter_data.class_presenter=v2_faculty.id) as faculty',
                ])
              ->from('v2_course_class_detail')
              ->join('v2_course_presenter_data','v2_course_presenter_data.class_id=v2_course_class_detail.id')
          ->where('v2_course_class_detail.class_tag_id',$class_id)
          ->order_by("v2_course_class_detail.id","desc")->get();
              return $query->row();
      }
      
      public function faculty_list_course($category_id,$class_id,$subject_id){
              $query  = $this->db->select(['v2_faculty.*','category.category','category.category_code','class.class','class.class_code',
                  'subject.subject','subject.subject_code','master_user.first_name','master_user.last_name','master_user.email'])
                  ->from('v2_faculty')
                  ->join('category','v2_faculty.category_id=category.id')
                  ->join('class','v2_faculty.class_id=class.id')
                  ->join('subject','v2_faculty.subject_id=subject.id')
                  ->join('master_user','v2_faculty.teacher_id=master_user.id')
                  ->where('v2_faculty.category_id',$category_id)
                  ->where('v2_faculty.class_id',$class_id)
                  ->where('v2_faculty.subject_id',$subject_id)->order_by("id", "asc")->get();
              $return 	= 	array();
              if($query->num_rows() > 0) {
                  $return[''] = ('Select Faculty');
                  foreach($query->result_array() as $row) {
                      $return[$row['id']] = strtoupper($row['category_code'].$row['class_code'].$row['subject_code'].
                          " | ".$row['first_name']." ".$row['last_name']." | ".$row['email']);
                  }
              }
              return $return;
          
      }
      
      public function update_class_content_modified($data,$class){
          $this->db->where('class_tag_id',$class);
          $this->db->update('v2_course_class_detail',$data);
          return true;
      }
      
      public function course_class_list_all($class_date){
          $query  = $this->db->select(['v2_course_class_detail.id','class_tag_id','class_date',
              '(select course_start_date from v2_course_new where v2_course_class_detail.course_id=v2_course_new.id) as course_start_date',
              '(select course_end_date from v2_course_new where v2_course_class_detail.course_id=v2_course_new.id) as course_end_date',
              '(select course_id_tag from v2_course_new where v2_course_class_detail.course_id=v2_course_new.id) as course_id_tag',
            
              'class_start_time','class_end_time','class_duration_min','class_extend_time_min'])
              ->from('v2_course_class_detail')
              ->join('v2_course_presenter_data','v2_course_presenter_data.class_id=v2_course_class_detail.id')
              ->order_by("v2_course_class_detail.id","asc")->where('class_date',$class_date)->get();
              //echo $this->db->last_query();die;
            ///  echo "<pre>";print_r($query->result);die;
              return $query->result();
      }
      public function  payment_done_st($courseid){
          
              $query		=	  $this->db->select(['pay2.id','(select CONCAT(first_name, " ", last_name) from master_user  where pay2.student_id=master_user.id) as student',
                  '(select master_user.id from master_user  where pay2.student_id=master_user.id) as student_id',
                  'sum(no_of_class_alot) as no_of_class_alot'])
                  ->from('v2_payment_course_student as pay2')
                  ->join('v2_payment_course_student_detail as pay2_det','pay2_det.pay_id=pay2.id')
                  ->where('pay2_det.approved','1')
                  ->where('pay2.course_id',$courseid)
                  ->group_by('pay2.course_id')
                  ->group_by('pay2.student_id')
                  ->group_by('pay2.category_id')
                  ->group_by('pay2.class_id')
                  ->group_by('pay2.subject_id')
                  //->group_by('pay2.course_id',$courseid)
              ->get();
              $return 	= 	array();
              if($query->num_rows() > 0) {
                  $return[''] = ('Select Student');
                  foreach($query->result_array() as $row) {
                      $return[$row['student_id']] = strtoupper($row['no_of_class_alot']." : ".$row['student']);
                  }
              }
              return $return;
      }
      public function  payment_done_st_id_count($courseid,$stid){
          
          $query		=	  $this->db->select(['pay2.id','(select CONCAT(first_name, " ", last_name) from master_user  where pay2.student_id=master_user.id) as student',
              '(select master_user.id from master_user  where pay2.student_id=master_user.id) as student_id',
              'sum(no_of_class_alot) as no_of_class_alot'])
              ->from('v2_payment_course_student as pay2')
              ->join('v2_payment_course_student_detail as pay2_det','pay2_det.pay_id=pay2.id')
              ->where('pay2_det.approved','1')
              ->where('pay2.course_id',$courseid)
              ->where('pay2.student_id',$stid)
              ->group_by('pay2.course_id')
              ->group_by('pay2.student_id')
              ->group_by('pay2.category_id')
              ->group_by('pay2.class_id')
              ->group_by('pay2.subject_id')
              //->group_by('pay2.course_id',$courseid)
          ->get();
          return $query->row();
      }
      
      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 class_list_for_courseid($courseid){
          $query  = $this->db->select(['class_tag_id','id','class_date','class_start_time','class_end_time'])
          ->from('v2_course_class_detail')
          ->where('course_id',$courseid)
          //->where('DATE(class_date) >','DATE(NOW())')
          ->where("TIMESTAMP(CONCAT_WS(' ',class_date,class_start_time))>CURRENT_TIMESTAMP()") 
          ->get();
          return $query->result();
      }
 
}
