<?php
require_once dirname(dirname(dirname(dirname(dirname(__FILE__))))) . '/config.php';
class analytics_model{

	private $current_record = 0;
	
	/*
	 *
	 */
	function __construct(){
		global $CFG;
		
		if(!empty($CFG->block_analytics_current_record)){
			$this->current_record = $CFG->block_analytics_current_record;
		} else {
			$sql = "SELECT MIN(id) 'min' FROM {$CFG->prefix}log";
			$min_record = array_pop(get_records_sql($sql));
			$this->current_record = $min_record->min;

		}
	}
	
	/*
	 *
	 */
	function process($offset=null, $limit=10000){
		global $CFG;
	
		// This may take a long time depending on the data processing limit
		// So increase the memory and remove script timeout
		set_time_limit(0);
		raise_memory_limit('512M');
		
		
		if(empty($offset)){
			$offset = $this->current_record;
		}
		
		$upper_limit = $offset + $limit;
		
		$sql = "SELECT id, WEEK(FROM_UNIXTIME(time)) as week, course, userid user, COUNT(id) as count 
				FROM (SELECT * FROM {$CFG->prefix}log WHERE id > $offset AND id <= $upper_limit) as log 
				GROUP BY userid, course, week
				ORDER BY userid, course, week";
		$activity_counts = get_records_sql($sql);
		if(!empty($activity_counts)){
			foreach ($activity_counts as $activity_count){
				if($record = get_record('block_analytics_user', 'course', $activity_count->course, 'week', $activity_count->week, 'user', $activity_count->user)){
					$record->count += $activity_count->count;
					update_record('block_analytics_user', $record);
				} else {
					insert_record('block_analytics_user', $activity_count);
				}
			}
			
			$max_sql = "SELECT MAX(id) max FROM (SELECT * FROM {$CFG->prefix}log WHERE id > $offset AND id <= $upper_limit) as log";
			$max_record = array_pop(get_records_sql($max_sql));
			$max = $max_record->max;
			
			$this->update_current_record($max);
		}
	}
	
	/*
	 *
	 */
	function get_top_courses($limit = 10, $week = null, $min_activity=1){
		global $CFG;
		$courses_sql = "SELECT course, SUM(count) as total ";
		$courses_sql .=	"FROM {$CFG->prefix}block_analytics_user bac ";
		$courses_sql .=	"WHERE course > 1 ";
		$courses_sql .= (!empty($week))? "AND week=$week ": "";
		$courses_sql .=	"GROUP BY course ";
		$courses_sql .=	"ORDER BY total DESC";
		
		$sql = "SELECT c.fullname, top_c.total FROM {$CFG->prefix}course c
				INNER JOIN ($courses_sql) as top_c ON top_c.course = c.id
				WHERE top_c.total >= $min_activity 
				LIMIT $limit ";
				
		$top_courses = get_records_sql($sql);
		
		return $top_courses;		
	}
	
	/*
	 *
	 */
	function get_num_active_courses($min_activity=1){
		global $CFG;
		$sql="SELECT COUNT(course) 'count'
				FROM (SELECT course, SUM(count) as 'count' 
						FROM {$CFG->prefix}block_analytics_user 
						GROUP BY course) as courses 
				WHERE count >= $min_activity";
				
		$num_courses_record = get_record_sql($sql);
		
		return $num_courses_record->count;
	}
	
	/*
	 *
	 */
	// If roleid is null, returns num of active users in a course
	// If roleid is specified, returns num of active users of that role in a course
	function get_num_active_users($min_activity=1, $roleid=null){
		global $CFG;
		
		// Get users by roleid, if specified
		$user_sql = "SELECT bau.user, SUM(bau.count) total ";
		$user_sql .= "FROM {$CFG->prefix}block_analytics_user bau ";
		$user_sql .= "INNER JOIN {$CFG->prefix}role_assignments ra ON bau.user = ra.userid ";
		$user_sql .= "INNER JOIN {$CFG->prefix}context ctxt ON ra.contextid=ctxt.id ";
		$user_sql .= "WHERE ctxt.contextlevel=". CONTEXT_COURSE ." AND ctxt.instanceid = bau.course ";
		$user_sql .= (!empty($roleid))? "AND ra.roleid=$roleid " : "";
		$user_sql .= "GROUP BY bau.user ";
		$user_sql .= "ORDER BY bau.user ";
		
		//Count number of users
		$sql="SELECT COUNT(users.user) as count FROM ($user_sql) AS users WHERE users.total >= $min_activity";	
		$num_users_record = get_record_sql($sql);
		
		return $num_users_record->count;
	}
	
	/*
	 *
	 */
	function get_num_active_instructors($min_activity=1){
		global $CFG;
		$sql="SELECT COUNT(user) 'count'
				FROM (SELECT user, SUM(count) as 'count' 
						FROM {$CFG->prefix}block_analytics_user 
						GROUP BY user) as users 
				WHERE count >= $min_activity";
				
		$num_users_record = get_record_sql($sql);
		
		return $num_users_record->count;
	}
	
	/*
	 *
	 */
	function get_current_record(){
		global $CFG;
		//$sql = "SELECT i FROM {$CFG->prefix}log WHERE id=$CFG->block_analytics_current_record}";
		$current_record = get_record('log', 'id', $CFG->block_analytics_current_record);
		
		return $current_record;
	}
	
	/*
	 *
	 */
	function update_current_record($id){
		if(!empty($id)){
			set_config('block_analytics_current_record', $id);
		}
	}
	
	/*
	 *
	 */
	function reset(){
		global $CFG;
		delete_records('block_analytics_user');
		 $sql = "SELECT MIN(id) 'min' FROM {$CFG->prefix}log";
			$min_record = array_pop(get_records_sql($sql));
			$min = $min_record->min;

		set_config('block_analytics_current_record', $min);
	}
    
    /*
     *
     */
     function get_activity_info($category_id = null){
        global $CFG;
        
        $activity_info = array();
        
        //Assignment
        $assignment_info = new stdClass;
        $assignment_info->name = 'Assignment';
       
        $sql = "SELECT COUNT(a.id) count FROM {$CFG->prefix}assignment a ";
        $sql .= "INNER JOIN {$CFG->prefix}course c ON a.course = c.id ";
        $sql .= (!empty($category_id)) ? "WHERE c.category=$category_id" : "";
        $assignment_info->num_placed  = number_format(array_pop(get_records_sql($sql))->count);
        
        $sql = "SELECT COUNT(assignments.id) as count FROM ";
        $sql .= "(SELECT a.id FROM {$CFG->prefix}assignment a ";
        $sql .= "INNER JOIN {$CFG->prefix}assignment_submissions asub ON a.id = asub.assignment ";
        $sql .= "INNER JOIN {$CFG->prefix}course c ON a.course = c.id ";
        $sql .= (!empty($category_id)) ? "WHERE c.category=$category_id " : "";
        $sql .= "GROUP BY a.id) as assignments";
        $assignment_info->num_used  = number_format(array_pop(get_records_sql($sql))->count);
        
        $sql = "SELECT COUNT(assignment_submissions.id) as count FROM ";
        $sql .= "(SELECT asub.id FROM {$CFG->prefix}assignment a ";
        $sql .= "INNER JOIN {$CFG->prefix}assignment_submissions asub ON a.id = asub.assignment ";
        $sql .= "INNER JOIN {$CFG->prefix}course c ON a.course = c.id ";
        $sql .= (!empty($category_id)) ? "WHERE c.category=$category_id " : "";
        $sql .= ") as assignment_submissions";
        $assignment_info->usage  = number_format(array_pop(get_records_sql($sql))->count) . " submissions";

        $activity_info[] = $assignment_info;
        
        
        //Forums
        $forum_info = new stdClass;
        $forum_info->name = 'Forum';
       
        $sql = "SELECT COUNT(f.id) count FROM {$CFG->prefix}forum f ";
        $sql .= "INNER JOIN {$CFG->prefix}course c ON f.course = c.id ";
        $sql .= (!empty($category_id)) ? "WHERE c.category=$category_id" : "";
        $forum_info->num_placed  = number_format(array_pop(get_records_sql($sql))->count);
        
        $sql = "SELECT COUNT(forums.id) as count FROM ";
        $sql .= "(SELECT f.id FROM {$CFG->prefix}forum f ";
        $sql .= "INNER JOIN {$CFG->prefix}forum_discussions fd ON f.id = fd.forum ";
        $sql .= "INNER JOIN {$CFG->prefix}forum_posts fp ON fp.discussion = fd.id ";
        $sql .= "INNER JOIN {$CFG->prefix}course c ON f.course = c.id ";
        $sql .= (!empty($category_id)) ? "WHERE c.category=$category_id " : "";
        $sql .= "GROUP BY f.id) as forums";
        $forum_info->num_used  = number_format(array_pop(get_records_sql($sql))->count);
        
        $sql = "SELECT COUNT(forum_posts.id) as count FROM ";
        $sql .= "(SELECT fp.id FROM {$CFG->prefix}forum f ";
        $sql .= "INNER JOIN {$CFG->prefix}forum_discussions fd ON f.id = fd.forum ";
        $sql .= "INNER JOIN {$CFG->prefix}forum_posts fp ON fp.discussion = fd.id ";
        $sql .= "INNER JOIN {$CFG->prefix}course c ON f.course = c.id ";
        $sql .= (!empty($category_id)) ? "WHERE c.category=$category_id " : "";
        $sql .= ") as forum_posts";
        $forum_info->usage  = number_format(array_pop(get_records_sql($sql))->count) . " posts";

        $activity_info[] = $forum_info;
        
         //Quiz
        $quiz_info = new stdClass;
        $quiz_info->name = 'Quiz';
       
        $sql = "SELECT COUNT(q.id) count FROM {$CFG->prefix}quiz q ";
        $sql .= "INNER JOIN {$CFG->prefix}course c ON q.course = c.id ";
        $sql .= (!empty($category_id)) ? "WHERE c.category=$category_id" : "";
        $quiz_info->num_placed  = number_format(array_pop(get_records_sql($sql))->count);
        
        $sql = "SELECT COUNT(quizzes.id) as count FROM ";
        $sql .= "(SELECT q.id FROM {$CFG->prefix}quiz q ";
        $sql .= "INNER JOIN {$CFG->prefix}quiz_attempts qa ON q.id = qa.quiz ";
        $sql .= "INNER JOIN {$CFG->prefix}course c ON q.course = c.id ";
        $sql .= (!empty($category_id)) ? "WHERE c.category=$category_id " : "";
        $sql .= "GROUP BY q.id) as quizzes";
        $quiz_info->num_used  = number_format(array_pop(get_records_sql($sql))->count);
 
        $sql = "SELECT COUNT(quiz_attempts.id) as count FROM ";
        $sql .= "(SELECT qa.id FROM {$CFG->prefix}quiz q ";
        $sql .= "INNER JOIN {$CFG->prefix}quiz_attempts qa ON q.id = qa.quiz ";
        $sql .= "INNER JOIN {$CFG->prefix}course c ON q.course = c.id ";
        $sql .= (!empty($category_id)) ? "WHERE c.category=$category_id " : "";
        $sql .= ") as quiz_attempts";
        $quiz_info->usage  = number_format(array_pop(get_records_sql($sql))->count) . " attempts";

        $activity_info[] = $quiz_info;
        
        return $activity_info;
     }
     
     function get_user_info($week = null, $page = 1, $limit = 20){
        $user_info = array();
        
        $offset = ($page - 1) * $limit;
        
        $sql = "SELECT u.firstname, u.lastname, bau.user, SUM(bau.count) count ";
        $sql .= "FROM mdl_block_analytics_user bau ";
        $sql .= "INNER JOIN mdl_user u ON u.id = bau.user ";
        $sql .= "WHERE bau.course != 1 ";
        $sql .= "GROUP BY user ";
        $sql .= "ORDER BY count DESC ";
        $sql .= "LIMIT $limit ";
        $sql .= "OFFSET $offset ";

        $user_info = get_records_sql($sql);
        return $user_info;
     }
     
     function get_course_info($week = null, $page = 1, $limit = 20){
        $course_info = array();
        
        $offset = ($page - 1) * $limit;
        
        $sql = "SELECT c.fullname, bau.course, SUM(bau.count) count ";
        $sql .= "FROM mdl_block_analytics_user bau ";
        $sql .= "INNER JOIN mdl_course c ON c.id = bau.course ";
        $sql .= "WHERE bau.course != 1 ";
        $sql .= "GROUP BY course ";
        $sql .= "ORDER BY count DESC ";
        $sql .= "LIMIT $limit ";
        $sql .= "OFFSET $offset ";

        $course_info = get_records_sql($sql);
        return $course_info;
     }
	
}