<?php

class CourseOverview_model extends CI_Model{
  function __construct() {
    parent::__construct();
    //echo '<pre>'; print_r($this->userInfo); echo '</pre>';
  }
  
  function getMonths(){
    if ($this->userInfo['role']!='admin'){
      $this->db->where('teacher_id',$this->userInfo['user_id']);
    }
    return $this->db
      ->select('DISTINCT `year`,`month`',false)
      ->order_by('`year` DESC,`month` DESC')
      ->get('teacher_timesheet',array())->result_array();
  }
  
  function addCourseLesson($courseLessonID){
    $courseLessonID*=1;
    $oldCourseLesson=$this->db
      ->select('l.*,c.teacher_id')
      ->join('course AS c','c.course_id=l.course_id','inner')    
      ->get_where('course_lesson AS l',array('l.course_lesson_id'=>$courseLessonID),1)->row_array();
    
    if (!$oldCourseLesson['is_confirmed']){
      $this->db->where('course_lesson_id',$courseLessonID)->update('course_lesson',array('is_confirmed'=>1));
      $rawData=$this->db    
        ->where('l.course_id',$oldCourseLesson['course_id'])
        ->like('l.date',substr($oldCourseLesson['date'],0,8),'after')
        ->where('l.is_confirmed',1)
        ->get('course_lesson AS l')->result_array();
      
      $totalMinutes=0;
      foreach($rawData AS $l){
        $startTime=substr($l['start'],0,2)*60+substr($l['start'],-2);
        $endTime=substr($l['end'],0,2)*60+substr($l['end'],-2);
        $totalMinutes+=($endTime-$startTime);
      }
      
      $year=1*substr($oldCourseLesson['date'],0,4);
      $month=1*substr($oldCourseLesson['date'],5,2);
      
      $timesheetEntry=array(
        'year'=>$year
        ,'month'=>$month
        ,'teacher_id'=>$oldCourseLesson['teacher_id']
        ,'course_id'=>$oldCourseLesson['course_id']
        //,'minutes'=>$totalMinutes
      );
      
      $existingTimesheet=$this->db->get_where('teacher_timesheet',$timesheetEntry,1)->row_array();
      if (!$existingTimesheet){
        $timesheetEntry['minutes']=$totalMinutes;
        $this->db->insert('teacher_timesheet',$timesheetEntry);
      }
      else {
        $this->db->where($timesheetEntry)->update('teacher_timesheet',array('minutes'=>$totalMinutes));
      }
    }
  }
  
  function getList($filters){
   /* $this->db->where(array('tt.year'=>($filters['year']*1),'tt.month'=>($filters['month']*1)));
    if ($this->userInfo['role']!='admin'){
      $this->db->where('tt.teacher_id',$this->userInfo['user_id']);
    }*/
	
	//print_r($filters);
	$studentid=$filters[0];
 $this->db->where(array('cla.student_id'=>$filters[0]));
 
 $this->db->where("c.status_id NOT IN (8,9) ");
 
 $courseid=$filters[1];
 if( $courseid>0)	
 {
 	 $this->db->where(array('c.course_id'=>$filters[1]));
 
 }


/*  $ahwe= $this->db->select('`cla`.*',false)
      ->join('course_lesson AS cl','cl.course_lesson_id=cla.course_lesson_id','inner')
      ->join('course AS c','c.course_id=cl.course_id','inner')->select('c.name AS course_name,c.name_numeric as coursenumericname')
      ->join('student AS s','s.student_id=cla.student_id','inner')
      ->join('status AS st','st.status_id=cla.status_id','inner')->select('st.name AS status_name')
      ->join('user AS u','u.user_id=s.student_id','inner')
	   ->get('course_lesson_attendance AS cla')->result_array();*/
	   
	   
	    $ahwe= $this->db->select('`cla`.*',false)
         ->join('course AS c','c.course_id=cla.course_id','inner')->select('c.name AS course_name,c.name_numeric as coursenumericname')
      	->join('student AS s','s.student_id=cla.student_id','inner')
		
        ->join('user AS u','u.user_id=s.student_id','inner')
	   ->get('course_student AS cla')->result_array();
	   
	  
	   $i=0;
	  
	 
	 foreach($ahwe as $valuelesson)
	 {
		 $totalpurchased=0;
		 $totalfollowed=0;
		 $remainghours=0;
		$course_id= $valuelesson['course_id'];
		
					$selectcur="select sum(b.contract_hours) as totalpurchased from course_student as a ,course as b where a.course_id=b.course_id and a.course_id='$course_id' and  a.student_id='$studentid' and a.status_id=1";
					$queryset=$this->db->query($selectcur)->result_array();
					$totalpurchased=$queryset[0]['totalpurchased'];
					
				
				 $ahwe[$i]['totalincontract']=$totalpurchased;
				
				
				
				//follow

	 $selectcur="select * from course_lesson_attendance as a ,course_lesson as b where a.course_lesson_id=b.course_lesson_id and a.student_id='$studentid' and a.status_id NOT IN(3,10,11) and a.course_lesson_id in(select course_lesson_id from course_lesson where course_id='$course_id')";
	$queryset=$this->db->query($selectcur)->result_array();
	$totalfollowed=0;
	$datearray=array();
	foreach($queryset as $value)
	{
		$startdate=	strtotime($value['date']." ".$value['start']);
		$enddate=	strtotime($value['date']." ".$value['end']);
		 $seconds =$enddate -$startdate;
		 $totalfollowed=$totalfollowed+ $seconds;
		 array_push($datearray,($value['date']." ".$value['start']."-".$value['end']));

		
	}
		$totalfollwedss=$this->secToHR($totalfollowed);
			$ahwe[$i]['houralreadygiven']="$totalfollwedss";
			
			//remaining
			 $secondsget=$totalpurchased*3600;
			 $total=$secondsget-$totalfollowed; 
			 $total= str_replace('-', '',$total);
			 $remainghours= $this->secToHR($total);
			 
			 if($secondsget<$totalfollowed)
			 {
				$remainghours="-".$remainghours;	 
			 }
			 
			 $ahwe[$i]['remaining']="$remainghours";
			 
			  $ahwe[$i]['dategiven']=implode(",",$datearray);
	
				$i++;
		 
	 }
    
		return $ahwe;
	

	//$select="select * from  course_student as a,course as b  where a.course_id=b.course_id and a.student_id='' "
	  
	  
	  
 /*   return $this->db
      ->from('course_lesson_attendance AS cla')
    	   ->join('course_lesson AS cl','cl.course_lesson_id=cla.course_lesson_id','inner')
      ->join('course AS c','c.course_id=cl.course_id','inner')->select('c.name AS course_name')
      ->join('student AS s','s.student_id=cla.student_id','inner')
      ->join('status AS st','st.status_id=cla.status_id','inner')->select('st.name AS status_name')
      ->join('user AS u','u.user_id=s.student_id','inner')->result_array();*/
  }
  
  function secToHR($seconds) 
  {
		  $hours = floor($seconds / 3600);
		  $minutes = floor(($seconds / 60) % 60);
		  $seconds = $seconds % 60;
		  if($minutes>0)
		  {
		  return "$hours.$minutes";
		  }
		  else
		  {
		  return $hours.".00";  
		  }
	}
	
	
}

?>