course.lib.php

完美的在线教育系统

		return true;
	}

	/**
	 * Delete a course
	 * This function deletes a whole course-area from the platform. When the
	 * given course is a virtual course, the database and directory will not be
	 * deleted.
	 * When the given course is a real course, also all virtual courses refering
	 * to the given course will be deleted.
	 * Considering the fact that we remove all traces of the course in the main
	 * database, it makes sense to remove all tracking as well (if stats databases exist)
	 * so that a new course created with this code would not use the remains of an older
	 * course.
	 * 
	 * @param string $code The code of the course to delete
	 * @todo When deleting a virtual course: unsubscribe users from that virtual
	 * course from the groups in the real course if they are not subscribed in
	 * that real course.
	 * @todo Remove globals
	 */
	function delete_course($code)
	{
		global $_configuration;
		
		$table_course = Database :: get_main_table(TABLE_MAIN_COURSE);
		$table_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
		$table_course_class = Database :: get_main_table(TABLE_MAIN_COURSE_CLASS);
		$user_role_table = Database :: get_main_table(MAIN_USER_ROLE_TABLE);
		$location_table = Database::get_main_table(MAIN_LOCATION_TABLE);
		$role_right_location_table = Database::get_main_table(MAIN_ROLE_RIGHT_LOCATION_TABLE);
		$table_session_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
		$table_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
		$table_course_survey = Database::get_main_table(TABLE_MAIN_SHARED_SURVEY);
		$table_course_survey_question = Database::get_main_table(TABLE_MAIN_SHARED_SURVEY_QUESTION);
		$table_course_survey_question_option = Database::get_main_table(TABLE_MAIN_SHARED_SURVEY_QUESTION_OPTION);
		$stats = false;
		if(Database::get_statistic_database() != ''){
			$stats = true;
			$table_stats_hotpots = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_HOTPOTATOES);
			$table_stats_attempt = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
			$table_stats_exercises = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
			$table_stats_access = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS);
			$table_stats_lastaccess = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LASTACCESS);
			$table_stats_course_access = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
			$table_stats_online = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ONLINE);
			$table_stats_default = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_DEFAULT);
			$table_stats_downloads = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_DOWNLOADS);
			$table_stats_links = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LINKS);
			$table_stats_uploads = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_UPLOADS);
		}

		$sql = "SELECT * FROM $table_course WHERE code='".$code."'";
		$res = api_sql_query($sql, __FILE__, __LINE__);
		if (Database::num_rows($res) == 0)
		{
			return;
		}
		$this_course = Database::fetch_array($res);
		$db_name = $this_course['db_name'];
		CourseManager :: create_database_dump($code);
		if (!CourseManager :: is_virtual_course_from_system_code($code))
		{
			// If this is not a virtual course, look for virtual courses that depend on this one, if any
			$virtual_courses = CourseManager :: get_virtual_courses_linked_to_real_course($code);
			foreach ($virtual_courses as $index => $virtual_course)
			{
				// Unsubscribe all classes from the virtual course
				$sql = "DELETE FROM $table_course_class WHERE course_code='".$virtual_course['code']."'";
				api_sql_query($sql, __FILE__, __LINE__);
				// Unsubscribe all users from the virtual course
				$sql = "DELETE FROM $table_course_user WHERE course_code='".$virtual_course['code']."'";
				api_sql_query($sql, __FILE__, __LINE__);
				// Delete the course from the sessions tables
				$sql = "DELETE FROM $table_session_course WHERE course_code='".$virtual_course['code']."'";
				api_sql_query($sql,__FILE__,__LINE__);
				$sql = "DELETE FROM $table_session_course_user WHERE course_code='".$virtual_course['code']."'";
				api_sql_query($sql,__FILE__,__LINE__);
				// Delete the course from the survey tables
				$sql = "DELETE FROM $table_course_survey WHERE course_code='".$virtual_course['code']."'";
				api_sql_query($sql,__FILE__,__LINE__);
				$sql = "DELETE FROM $table_course_survey_user WHERE db_name='".$virtual_course['db_name']."'";
				api_sql_query($sql,__FILE__,__LINE__);
				$sql = "DELETE FROM $table_course_survey_reminder WHERE db_name='".$virtual_course['db_name']."'";
				api_sql_query($sql,__FILE__,__LINE__);
				
				// Delete the course from the stats tables
				if($stats)
				{
					$sql = "DELETE FROM $table_stats_hotpots WHERE exe_cours_id = '".$virtual_course['code']."'";
					api_sql_query($sql,__FILE__,__LINE__);
					$sql = "DELETE FROM $table_stats_attempt WHERE course_code = '".$virtual_course['code']."'";
					api_sql_query($sql,__FILE__,__LINE__);
					$sql = "DELETE FROM $table_stats_exercises WHERE exe_cours_id = '".$virtual_course['code']."'";
					api_sql_query($sql,__FILE__,__LINE__);
					$sql = "DELETE FROM $table_stats_access WHERE access_cours_code = '".$virtual_course['code']."'";
					api_sql_query($sql,__FILE__,__LINE__);
					$sql = "DELETE FROM $table_stats_lastaccess WHERE access_cours_code = '".$virtual_course['code']."'";
					api_sql_query($sql,__FILE__,__LINE__);
					$sql = "DELETE FROM $table_stats_course_access WHERE course_code = '".$virtual_course['code']."'";
					api_sql_query($sql,__FILE__,__LINE__);
					$sql = "DELETE FROM $table_stats_online WHERE course = '".$virtual_course['code']."'";
					api_sql_query($sql,__FILE__,__LINE__);
					$sql = "DELETE FROM $table_stats_default WHERE default_cours_code = '".$virtual_course['code']."'";
					api_sql_query($sql,__FILE__,__LINE__);
					$sql = "DELETE FROM $table_stats_downloads WHERE down_cours_id = '".$virtual_course['code']."'";
					api_sql_query($sql,__FILE__,__LINE__);
					$sql = "DELETE FROM $table_stats_links WHERE links_cours_id = '".$virtual_course['code']."'";
					api_sql_query($sql,__FILE__,__LINE__);
					$sql = "DELETE FROM $table_stats_uploads WHERE upload_cours_id = '".$virtual_course['code']."'";
					api_sql_query($sql,__FILE__,__LINE__);
				}				
				
				// Delete the course from the course table
				$sql = "DELETE FROM $table_course WHERE code='".$virtual_course['code']."'";
				api_sql_query($sql, __FILE__, __LINE__);
			}
			$sql = "SELECT * FROM $table_course WHERE code='".$code."'";
			$res = api_sql_query($sql, __FILE__, __LINE__);
			$course = Database::fetch_array($res);
			if (!$_configuration['single_database'])
			{
				$sql = "DROP DATABASE IF EXISTS ".$course['db_name'];
				api_sql_query($sql, __FILE__, __LINE__);
			}
			else
			{
				//TODO Clean the following code as currently it would probably delete another course
				//similarly named, by mistake...
				$db_pattern = $_configuration['table_prefix'].$course['db_name'].$_configuration['db_glue'];
				$sql = "SHOW TABLES LIKE '$db_pattern%'";
				$result = api_sql_query($sql, __FILE__, __LINE__);
				while (list ($courseTable) = Database::fetch_array($result))
				{
					api_sql_query("DROP TABLE `$courseTable`", __FILE__, __LINE__);
				}
			}
			$course_dir = api_get_path(SYS_COURSE_PATH).$course['directory'];
			$garbage_dir = api_get_path(GARBAGE_PATH).$course['directory'].'_'.time();
			rename($course_dir, $garbage_dir);
		}

		// Unsubscribe all classes from the course
		$sql = "DELETE FROM $table_course_class WHERE course_code='".$code."'";
		api_sql_query($sql, __FILE__, __LINE__);
		// Unsubscribe all users from the course
		$sql = "DELETE FROM $table_course_user WHERE course_code='".$code."'";
		api_sql_query($sql, __FILE__, __LINE__);
		// Delete the course from the sessions tables
		$sql = "DELETE FROM $table_session_course WHERE course_code='".$code."'";
		api_sql_query($sql,__FILE__,__LINE__);
		$sql = "DELETE FROM $table_session_course_user WHERE course_code='".$code."'";
		api_sql_query($sql,__FILE__,__LINE__);
		
		$sql='SELECT survey_id FROM '.$table_course_survey.' WHERE course_code="'.$code.'"';
		$result_surveys=api_sql_query($sql);
		while($surveys=Database::fetch_array($result_surveys)){
			$survey_id=$surveys[0];
			$sql='DELETE FROM '.$table_course_survey_question.' WHERE survey_id="'.$survey_id.'"';
			api_sql_query($sql,__FILE__,__LINE__);
			$sql='DELETE FROM '.$table_course_survey_question_option.' WHERE survey_id="'.$survey_id.'"';
			api_sql_query($sql,__FILE__,__LINE__);
			$sql='DELETE FROM '.$table_course_survey.' WHERE survey_id="'.$survey_id.'"';
			api_sql_query($sql,__FILE__,__LINE__);
		}

		// Delete the course from the stats tables
		if($stats)
		{
			$sql = "DELETE FROM $table_stats_hotpots WHERE exe_cours_id = '".$code."'";
			api_sql_query($sql,__FILE__,__LINE__);
			$sql = "DELETE FROM $table_stats_attempt WHERE course_code = '".$code."'";
			api_sql_query($sql,__FILE__,__LINE__);
			$sql = "DELETE FROM $table_stats_exercises WHERE exe_cours_id = '".$code."'";
			api_sql_query($sql,__FILE__,__LINE__);
			$sql = "DELETE FROM $table_stats_access WHERE access_cours_code = '".$code."'";
			api_sql_query($sql,__FILE__,__LINE__);
			$sql = "DELETE FROM $table_stats_lastaccess WHERE access_cours_code = '".$code."'";
			api_sql_query($sql,__FILE__,__LINE__);
			$sql = "DELETE FROM $table_stats_course_access WHERE course_code = '".$code."'";
			api_sql_query($sql,__FILE__,__LINE__);
			$sql = "DELETE FROM $table_stats_online WHERE course = '".$code."'";
			api_sql_query($sql,__FILE__,__LINE__);
			$sql = "DELETE FROM $table_stats_default WHERE default_cours_code = '".$code."'";
			api_sql_query($sql,__FILE__,__LINE__);
			$sql = "DELETE FROM $table_stats_downloads WHERE down_cours_id = '".$code."'";
			api_sql_query($sql,__FILE__,__LINE__);
			$sql = "DELETE FROM $table_stats_links WHERE links_cours_id = '".$code."'";
			api_sql_query($sql,__FILE__,__LINE__);
			$sql = "DELETE FROM $table_stats_uploads WHERE upload_cours_id = '".$code."'";
			api_sql_query($sql,__FILE__,__LINE__);
		}				
		// Delete the course from the database
		$sql = "DELETE FROM $table_course WHERE code='".$code."'";
		api_sql_query($sql, __FILE__, __LINE__);
	}

	/**
	 * Creates a file called mysql_dump.sql in the course folder
	 * @param $course_code The code of the course
	 * @todo Implementation for single database
	 */
	function create_database_dump($course_code)
	{
		global $_configuration;
		
		if ($_configuration['single_database'])
		{
			return;
		}
		$sql_dump = '';
		$table_course = Database :: get_main_table(TABLE_MAIN_COURSE);
		$sql = "SELECT * FROM $table_course WHERE code = '$course_code'";
		$res = api_sql_query($sql, __FILE__, __LINE__);
		$course = Database::fetch_array($res);
		$sql = "SHOW TABLES FROM ".$course['db_name'];
		$res = api_sql_query($sql, __FILE__, __LINE__);
		while ($table = Database::fetch_array($res))
		{
			$sql = "SELECT * FROM `".$course['db_name']."`.`".$table[0]."`";
			$res3 = api_sql_query($sql, __FILE__, __LINE__);
			while ($row = Database::fetch_array($res3))
			{
				foreach ($row as $key => $value)
				{
					$row[$key] = $key."='".addslashes($row[$key])."'";
				}
				$sql_dump .= "\nINSERT INTO $table[0] SET ".implode(', ', $row).';';
			}
		}
		$file_name = api_get_path(SYS_COURSE_PATH).$course['directory'].'/mysql_dump.sql';
		$handle = fopen($file_name, 'a+');
		if($handle!==false){
			fwrite($handle, $sql_dump);
			fclose($handle);
		}else{
			//TODO trigger exception in a try-catch
		}
	}
	
	function userCourseSort($user_id,$course_code){
	
		$TABLECOURSE = Database :: get_main_table(TABLE_MAIN_COURSE);
		$TABLECOURSUSER = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
		
		$sql = 'SELECT title FROM '.$TABLECOURSE.' WHERE code="'.$course_code.'"';
		$result = api_sql_query($sql, __FILE__, __LINE__);
		$course_title = Database::result($result,0,0);
		
		$sql = 'SELECT course.code as code, course.title as title, cu.sort as sort FROM '.$TABLECOURSUSER.' as cu, '.$TABLECOURSE.' as course 
				WHERE course.code = cu.course_code 
				AND user_id = "'.$user_id.'" 
				AND user_course_cat=0 ORDER BY cu.sort';
		$result = api_sql_query($sql, __FILE__, __LINE__);
		
		$s_course_title_precedent = '';
		$counter = 0;
		$b_find_course = false;
		$i_course_sort = 1;
		
		while($courses=Database::fetch_array($result)){
			
			if($s_course_title_precedent == ''){
				$s_course_title_precedent = $courses['title'];
			}
	
			if(strcasecmp($s_course_title_precedent,$course_title)<0){
				
				$b_find_course = true;
				$i_course_sort = $courses['sort'];
	
				$s_course_code = $courses['code'];
				if($counter == 0){
					$sql = 'UPDATE '.$TABLECOURSUSER.' SET sort = sort+1 WHERE user_id= "'.$user_id.'"  AND user_course_cat="0" AND sort > "'.$i_course_sort.'"';
					$i_course_sort++;
				}
				else{
					$sql = 'UPDATE '.$TABLECOURSUSER.' SET sort = sort+1 WHERE user_id= "'.$user_id.'"  AND user_course_cat="0" AND sort >= "'.$i_course_sort.'"';
				}
	
				api_sql_query($sql, __FILE__, __LINE__);
				break;
			}
			
			else{
				$s_course_title_precedent = $courses['title'];
			}
			
			$counter++;
		}
		
		//We must register the course in the beginning of the list
		if(Database::num_rows($result)>0 && !$b_find_course)
		{
			$sql_max = 'SELECT min(sort) as min_sort FROM '.$TABLECOURSUSER.' WHERE user_id="'.$user_id.'" AND user_course_cat="0"';
			$result_min_sort=api_sql_query($sql_max, __FILE__, __LINE__);
			$i_course_sort = Database::result($result_min_sort,0,0);
			
			$sql = 'UPDATE '.$TABLECOURSUSER.' SET sort = sort+1 WHERE user_id= "'.$user_id.'"  AND user_course_cat="0"';
			api_sql_query($sql, __FILE__, __LINE__);
		}
		return $i_course_sort;
	}
	
	/**
	 * create recursively all categories as option of the select passed in paramater.
	 * 
	 * @param object $select_element the quickform select where the options will be added
	 * @param string $category_selected_code the option value to select by default (used mainly for edition of courses)
	 * @param string $parent_code the parent category of the categories added (default=null for root category)
	 * @param string $padding the indent param (you shouldn't indicate something here)
	 */
	function select_and_sort_categories($select_element, $category_selected_code="", $parent_code=null , $padding="")
	{
		$table_course_category = Database :: get_main_table(TABLE_MAIN_CATEGORY);
		$sql = "SELECT code, name, auth_course_child, auth_cat_child
				FROM ".$table_course_category." 
				WHERE parent_id ".(is_null($parent_code) ? "IS NULL" : "='".Database::escape_string($parent_code)."'")."
				ORDER BY code";
		$res = api_sql_query($sql, __FILE__, __LINE__);
		
		$new_padding = $padding.str_repeat('-',3);
		
		while ($cat = Database::fetch_array($res))
		{
			$params = $cat['auth_course_child'] == 'TRUE' ? '' : 'disabled';
			$params .= ($cat['code'] == $category_selected_code) ? ' selected' : '';
			$select_element->addOption($padding.'('.$cat['code'].') '.$cat['name'], $cat['code'], $params);
			if($cat['auth_cat_child'])
			{
				CourseManager::select_and_sort_categories($select_element, $cat['code'], $category_selected_code, $new_padding);
			}
		}
	}

	/**
	 * check if course exists
	 * @param string course_code
	 * @return true if exists, false else
	 */
	function course_exists($course_code)
	{
		$sql = 'SELECT 1 FROM '.Database :: get_main_table(TABLE_MAIN_COURSE).' WHERE code="'.Database::escape_string($course_code).'"';
		$rs = api_sql_query($sql,__FILE__,__LINE__);
		return Database::num_rows($rs);
	}	
} //end class CourseManager
?>