course.lib.php

完美的在线教育系统

					FROM $tbl_sessions AS session
					WHERE session.id='".$_SESSION['id_session']."'
					AND id_coach='$user_id'";

			$result = api_sql_query($sql,__FILE__,__LINE__);
			if(Database::num_rows($result))
				return true;

			return false;

		}
	}
	
	/**
	*	Return user info array of all users registered in the specified real or virtual course
	*	This only returns the users that are registered in this actual course, not linked courses.
	*
	*	@param string $course_code
	*	@return array with user info
	*/
	function get_user_list_from_course_code($course_code, $with_session=true, $session_id=0, $limit='', $order_by='')
	{		
		$session_id = intval($session_id);
		$a_users = array();		
		$table_users = Database :: get_main_table(TABLE_MAIN_USER);
		
		$where = array();
		
		$sql = 'SELECT DISTINCT user.user_id ';
        if ( $session_id == 0 ) {
        	$sql .= ', course_rel_user.role, course_rel_user.tutor_id ';
        }		
        $sql .= ' FROM '.$table_users.' as user ';

		if(api_get_setting('use_session_mode')=='true' && $with_session)
		{
			$table_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
			$sql .= ' LEFT JOIN '.$table_session_course_user.' as session_course_user
	        			 ON user.user_id = session_course_user.id_user
						 AND session_course_user.course_code="'.Database::escape_string($course_code).'"';
			if($session_id!=0) 
			{
				$sql .= ' AND session_course_user.id_session = '.$session_id;
			}
			$where[] = ' session_course_user.course_code IS NOT NULL ';
		}
		
		if($session_id == 0)
		{
			$table_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
			$sql .= ' LEFT JOIN '.$table_course_user.' as course_rel_user
				        ON user.user_id = course_rel_user.user_id
						AND course_rel_user.course_code="'.Database::escape_string($course_code).'"';
			$where[] = ' course_rel_user.course_code IS NOT NULL ';
		}		
		
		$sql .= ' WHERE '.implode(' OR ',$where);
				  
		$sql .= ' '.$order_by;
		$sql .= ' '.$limit;

		$rs = api_sql_query($sql, __FILE__, __LINE__);
		
		while($user = Database::fetch_array($rs))
		{
			$user_infos = Database :: get_user_info_from_id($user['user_id']);
			//$user_infos['status'] = $user['status'];
            if ( isset($user['role']) ) {
                $user_infos['role'] = $user['role'];
            }
            if ( isset($user['tutor_id']) ) {
                $user_infos['tutor_id'] = $user['tutor_id'];
            }                
			$a_users[$user['user_id']] = $user_infos; 
		}	
		
		return $a_users;
		
	}
	
	
	function get_coach_list_from_course_code($course_code,$session_id){
		
		$table_session_course = Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
		$table_session = Database :: get_main_table(TABLE_MAIN_SESSION);
		
		$a_users=array();
		
		//We get the coach for the given course in a given session
		$sql = 'SELECT id_coach FROM '.$table_session_course.' WHERE id_session="'.$session_id.'" AND course_code="'.$course_code.'"';
		$rs = api_sql_query($sql, __FILE__, __LINE__);
		while($user = Database::fetch_array($rs))
		{
			$user_infos = Database :: get_user_info_from_id($user['id_coach']);
			$user_infos["status"] = $user["status"];
			$user_infos["role"] = $user["role"];
			$user_infos["tutor_id"] = $user["tutor_id"];
			$user_infos['email'] = $user['email'];
			$a_users[$user['id_coach']] = $user_infos;
		}
		
		//We get the session coach
		$sql = 'SELECT id_coach FROM '.$table_session.' WHERE id="'.$session_id.'"';
		$rs = api_sql_query($sql, __FILE__, __LINE__);
		$user_infos=array();
		$session_id_coach = Database::result($rs,0,'id_coach');
		$user_infos = Database :: get_user_info_from_id($session_id_coach);
		$user_infos["status"] = $user["status"];
		$user_infos["role"] = $user["role"];
		$user_infos["tutor_id"] = $user["tutor_id"];
		$user_infos['email'] = $user['email'];
		$a_users[$session_id_coach] = $user_infos;
		
		return $a_users;
		
	}
	
	
	/**
	*	Return user info array of all users registered in the specified real or virtual course
	*	This only returns the users that are registered in this actual course, not linked courses.
	*
	*	@param string $course_code
	*	@param boolean $full list to true if we want sessions students too
	*	@return array with user id
	*/
	function get_student_list_from_course_code($course_code, $with_session=false, $session_id=0)
	{
		$a_students = array();
		
		$session_id = intval($session_id);
		
		if($session_id == 0)
		{
			// students directly subscribed to the course
			$table = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
			$sql_query = "SELECT * FROM $table WHERE `course_code` = '$course_code' AND `status` = 5";
			$rs = api_sql_query($sql_query, __FILE__, __LINE__);
			while($student = Database::fetch_array($rs))
			{
				$a_students[$student['user_id']] = $student; 
			}
		}
		
		// students subscribed to the course through a session
		
		if(api_get_setting('use_session_mode')=='true' && $with_session)
		{
			$table = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
			$sql_query = "SELECT * FROM $table WHERE `course_code` = '$course_code'";
			if($session_id!=0)
				$sql_query .= ' AND id_session = '.$session_id;
			$rs = api_sql_query($sql_query, __FILE__, __LINE__);
			while($student = Database::fetch_array($rs))
			{
				$a_students[$student['id_user']] = $student; 
			}
		}
		
		return $a_students;
	}
	/**
	*	Return user info array of all teacher-users registered in the specified real or virtual course
	*	This only returns the users that are registered in this actual course, not linked courses.
	*
	*	@param string $course_code
	*	@return array with user id
	*/
	function get_teacher_list_from_course_code($course_code)
	{
		$a_teachers = array();
		// teachers directly subscribed to the course
		$t_cu = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
		$t_u = Database :: get_main_table(TABLE_MAIN_USER);
		$sql_query = "SELECT u.user_id, u.lastname, u.firstname, u.email, u.username, u.status " .
				"FROM $t_cu cu, $t_u u " .
				"WHERE cu.course_code = '$course_code' " .
				"AND cu.status = 1 " .
				"AND cu.user_id = u.user_id";
		$rs = api_sql_query($sql_query, __FILE__, __LINE__);
		while($teacher = Database::fetch_array($rs))
		{
			$a_students[$teacher['user_id']] = $teacher; 
		}
		return $a_students;
	}

	/**
	*	Return user info array of all users registered in the specified course
	*	this includes the users of the course itsel and the users of all linked courses.
	*
	*	@param array $course_info
	*	@return array with user info
	*/
	function get_real_and_linked_user_list($course_code, $with_sessions = true, $session_id=0)
	{
		//get list of virtual courses
		$virtual_course_list = CourseManager :: get_virtual_courses_linked_to_real_course($course_code);

		//get users from real course
		$user_list = CourseManager :: get_user_list_from_course_code($course_code, $with_sessions, $session_id);
		foreach ($user_list as $this_user)
		{
			$complete_user_list[] = $this_user;
		}

		//get users from linked courses
		foreach ($virtual_course_list as $this_course)
		{
			$course_code = $this_course["code"];
			$user_list = CourseManager :: get_user_list_from_course_code($course_code, $with_sessions, $session_id);
			foreach ($user_list as $this_user)
			{
				$complete_user_list[] = $this_user;
			}
		}

		return $complete_user_list;
	}

	/**
	*	Return an array of arrays, listing course info of all courses in the list
	*	linked to the real course $real_course_code, to which the user $user_id is subscribed.
	*
	*	@param $user_id, the id (int) of the user
	*	@param $real_course_code, the id (char) of the real course
	*
	*	@return array of course info arrays
	*/
	function get_list_of_virtual_courses_for_specific_user_and_real_course($user_id, $real_course_code)
	{
		$result_array = array();
		$course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
		$course_user_table = Database :: get_main_table(TABLE_MAIN_COURSE_USER);


		$sql_query = "	SELECT *
							FROM $course_table course
							LEFT JOIN $course_user_table course_user
							ON course.`code` = course_user.`course_code`
							WHERE course.`target_course_code` = '$real_course_code' AND course_user.`user_id` = '$user_id'";
		
		$sql_result = api_sql_query($sql_query, __FILE__, __LINE__);

		while ($result = Database::fetch_array($sql_result))
		{
			$result_array[] = $result;
		}

		return $result_array;
	}

	/*
	==============================================================================
		GROUP FUNCTIONS
	==============================================================================
	*/

	function get_group_list_of_course($course_code)
	{
		$course_info = Database :: get_course_info($course_code);
		$database_name = $course_info['db_name'];
		$group_table = Database :: get_course_table(TABLE_GROUP, $database_name);
		$group_user_table = Database :: get_course_table(TABLE_GROUP_USER, $database_name);

		$sql = "SELECT g.id, g.name, COUNT(gu.id) userNb
								FROM $group_table AS g LEFT JOIN $group_user_table gu
								ON g.id = gu.group_id
								GROUP BY g.id
								ORDER BY g.name";

		$result = api_sql_query($sql, __FILE__, __LINE__) or die(mysql_error());
		while ($group_data = Database::fetch_array($result))
		{
			$group_list[$group_data['id']] = $group_data;
		}
		return $group_list;
	}

	/**
	*	Checks all parameters needed to create a virtual course.
	*	If they are all set, the virtual course creation procedure is called.
	*
	*	Call this function instead of create_virtual_course
	*/
	function attempt_create_virtual_course($real_course_code, $course_title, $wanted_course_code, $course_language, $course_category)
	{
		//better: create parameter list, check the entire list, when false display errormessage
		CourseManager :: check_parameter_or_fail($real_course_code, "Unspecified parameter: real course id.");
		CourseManager :: check_parameter_or_fail($course_title, "Unspecified parameter: course title.");
		CourseManager :: check_parameter_or_fail($wanted_course_code, "Unspecified parameter: wanted course code.");
		CourseManager :: check_parameter_or_fail($course_language, "Unspecified parameter: course language.");
		CourseManager :: check_parameter_or_fail($course_category, "Unspecified parameter: course category.");

		$creation_success = CourseManager :: create_virtual_course($real_course_code, $course_title, $wanted_course_code, $course_language, $course_category);

		return $creation_success;
	}

	/**
	*	This function creates a virtual course.
	*	It assumes all parameters have been checked and are not empty.
	*	It checks wether a course with the $wanted_course_code already exists.
	*
	*	Users of this library should consider this function private,
	*	please call attempt_create_virtual_course instead of this one.
	*
	*	NOTE:
	*	The virtual course 'owner' id (the first course admin) is set to the CURRENT user id.
	*	@return true if the course creation succeeded, false otherwise
	*	@todo research: expiration date of a course
	*/
	function create_virtual_course($real_course_code, $course_title, $wanted_course_code, $course_language, $course_category)
	{
		global $firstExpirationDelay;
		$course_table = Database :: get_main_table(TABLE_MAIN_COURSE);
		$user_id = api_get_user_id();
		$real_course_info = Database :: get_course_info($real_course_code);
		$real_course_code = $real_course_info["system_code"];

		//check: virtual course creation fails if another course has the same
		//code, real or fake.
		if (CourseManager :: is_existing_course_code($wanted_course_code))
		{
			Display :: display_error_message($wanted_course_code." - ".get_lang("CourseCodeAlreadyExists"));
			return false;
		}

		//add data to course table, course_rel_user
		$course_sys_code = $wanted_course_code;
		$course_screen_code = $wanted_course_code;
		$course_repository = $real_course_info["directory"];
		$course_db_name = $real_course_info["db_name"];
		$responsible_teacher = $real_course_info["tutor_name"];
		$faculty_shortname = $course_category;
		// $course_title = $course_title;
		// $course_language = $course_language;
		$teacher_id = $user_id;

		//HACK ----------------------------------------------------------------
		$expiration_date = time() + $firstExpirationDelay;
		//END HACK ------------------------------------------------------------

		register_course($course_sys_code, $course_screen_code, $course_repository, $course_db_name, $responsible_teacher, $faculty_shortname, $course_title, $course_language, $teacher_id, $expiration_date);

		//above was the normal course creation table update call,
		//now one more thing: fill in the target_course_code field

		$sql_query = "UPDATE $course_table SET `target_course_code` = '$real_course_code' WHERE `code` = '$course_sys_code' LIMIT 1 ";
		api_sql_query($sql_query, __FILE__, __LINE__);