usermanager.lib.php

完美的在线教育系统

				//we need to update the current record
				$rowufv = Database::fetch_array($resufv);
				if($rowufv['field_value'] != $fvalues)
				{
					$sqlu = "UPDATE $t_ufv SET field_value = '$fvalues', tms = FROM_UNIXTIME($tms) WHERE id = ".$rowufv['id'];
					//error_log('UM::update_extra_field_value: '.$sqlu);
					$resu = api_sql_query($sqlu,__FILE__,__LINE__);
					return($resu?true:false);
				}
				return true;
			}
			else
			{
				$sqli = "INSERT INTO $t_ufv (user_id,field_id,field_value,tms) " .
					"VALUES ($user_id,".$rowuf['id'].",'$fvalues',FROM_UNIXTIME($tms))";
				//error_log('UM::update_extra_field_value: '.$sqli);
				$resi = api_sql_query($sqli,__FILE__,__LINE__);
				return($resi?true:false);
			}
		}
		else
		{
			return false; //field not found
		}
	}
	/**
	 * Get an array of extra fieds with field details (type, default value and options)
	 * @param	integer	Offset (from which row)
	 * @param	integer	Number of items
	 * @param	integer	Column on which sorting is made
	 * @param	string	Sorting direction
	 * @param	boolean	Optional. Whether we get all the fields or just the visible ones
	 * @return	array	Extra fields details (e.g. $list[2]['type'], $list[4]['options'][2]['title']
	 */
	function get_extra_fields($from=0, $number_of_items=0, $column=5, $direction='ASC', $all_visibility=true)
	{
		$fields = array();
		$t_uf = Database :: get_main_table(TABLE_MAIN_USER_FIELD);
		$t_ufo = Database :: get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
		$columns = array('id','field_variable','field_type','field_display_text','field_default_value','field_order','tms');
		$sort_direction = '';
		if(in_array(strtoupper($direction),array('ASC','DESC')))
		{
			$sort_direction = strtoupper($direction);
		}
		$sqlf = "SELECT * FROM $t_uf ";
		if($all_visibility==false)
		{
			$sqlf .= " WHERE field_visible = 1 ";
		}
		$sqlf .= " ORDER BY ".$columns[$column]." $sort_direction " ;
		if($number_of_items != 0)
		{
			$sqlf .= " LIMIT ".Database::escape_string($from).','.Database::escape_string($number_of_items);
		}
		$resf = api_sql_query($sqlf,__FILE__,__LINE__);
		if(Database::num_rows($resf)>0)
		{
			while($rowf = Database::fetch_array($resf))
			{
				$fields[$rowf['id']] = array(
					0=>$rowf['id'],
					1=>$rowf['field_variable'],
					2=>$rowf['field_type'],
					//3=>(empty($rowf['field_display_text'])?'':get_lang($rowf['field_display_text'],'')),
					//temporarily removed auto-translation. Need update to get_lang() to know if translation exists (todo)
					3=>(empty($rowf['field_display_text'])?'':$rowf['field_display_text']),
					4=>$rowf['field_default_value'],
					5=>$rowf['field_order'],
					6=>$rowf['field_visible'],
					7=>$rowf['field_changeable'],
					8=>array()
				);
				$sqlo = "SELECT * FROM $t_ufo WHERE field_id = ".$rowf['id'];
				$reso = api_sql_query($sqlo,__FILE__,__LINE__);
				if(Database::num_rows($reso)>0)
				{
					while($rowo = Database::fetch_array($reso))
					{
						$fields[$rowf['id']][8][$rowo['id']] = array(
							0=>$rowo['id'],
							1=>$rowo['option_value'],
							//2=>(empty($rowo['option_display_text'])?'':get_lang($rowo['option_display_text'],'')),
							2=>(empty($rowo['option_display_text'])?'':$rowo['option_display_text']),
							3=>$rowo['option_order']
						);
					}	
				}
			}
		}
		return $fields;
	}
	/**
	 * Get the number of extra fields currently recorded
	 * @param	boolean	Optional switch. true (default) returns all fields, false returns only visible fields
	 * @return	integer	Number of fields
	 */
	function get_number_of_extra_fields($all_visibility=true)
	{
		$t_uf = Database :: get_main_table(TABLE_MAIN_USER_FIELD);
		$sqlf = "SELECT * FROM $t_uf ";
		if($all_visibility == false)
		{
			$sqlf .= " WHERE field_visible = 1 ";
		}
		$sqlf .= " ORDER BY field_order";
		$resf = api_sql_query($sqlf,__FILE__,__LINE__);
		return Database::num_rows($resf);
	}
	/**
	  * Creates a new extra field
	  * @param	string	Field's internal variable name
	  * @param	int		Field's type
	  * @param	string	Field's language var name
	  * @param	string	Field's default value
	  * @param	string	Optional comma-separated list of options to provide for select and radio
	  * @return int     new user id - if the new user creation succeeds, false otherwise
	  */
	function create_extra_field($fieldvarname, $fieldtype, $fieldtitle, $fielddefault, $fieldoptions='')
	{		
		// database table definition
		$table_field 		= Database::get_main_table(TABLE_MAIN_USER_FIELD);
		$table_field_options= Database::get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
		
		// First check wether the login already exists
		if (! UserManager::is_extra_field_available($fieldvarname))
			return api_set_failure('login-pass already taken');
		$sql = "SELECT MAX(field_order) FROM $table_field";
		$res = api_sql_query($sql,__FILE__,__LINE__);
		$order = 0;
		if(Database::num_rows($res)>0)
		{
			$row = Database::fetch_array($res);
			$order = $row[0]+1;
		}
		$time = time();
		$sql = "INSERT INTO $table_field
					                SET field_type = '".Database::escape_string($fieldtype)."',
					                field_variable = '".Database::escape_string($fieldvarname)."',
					                field_display_text = '".Database::escape_string($fieldtitle)."',
					                field_default_value = '".Database::escape_string($fielddefault)."',
					                field_order = '$order',
					                tms = FROM_UNIXTIME($time)";
		$result = api_sql_query($sql);
		if ($result)
		{
			//echo "id returned";
			$return=Database::get_last_insert_id();
		}
		else
		{
			//echo "false - failed" ;
			return false;
		}
		if(!empty($fieldoptions) && in_array($fieldtype,array(USER_FIELD_TYPE_RADIO,USER_FIELD_TYPE_SELECT,USER_FIELD_TYPE_SELECT_MULTIPLE)))
		{
			$list = split(';',$fieldoptions);
			foreach($list as $option)
			{
				$option = Database::escape_string($option);
				$sql = "SELECT * FROM $table_field_options WHERE field_id = $return AND option_value = '".$option."'";
				$res = api_sql_query($sql,__FILE__,__LINE__);
				if(Database::num_rows($res)>0)
				{
					//the option already exists, do nothing
				}
				else
				{
					$sql = "SELECT MAX(option_order) FROM $table_field_options WHERE field_id = $return";
					$res = api_sql_query($sql,__FILE__,__LINE__);
					$max = 1;
					if(Database::num_rows($res)>0)
					{
						$row = Database::fetch_array($res);
						$max = $row[0]+1;
					}
					$time = time();
					$sql = "INSERT INTO $table_field_options (field_id,option_value,option_display_text,option_order,tms) VALUES ($return,'$option','$option',$max,FROM_UNIXTIME($time))";
					$res = api_sql_query($sql,__FILE__,__LINE__);
					if($res === false)
					{
						$return = false;
					}
				}
			}
		}
		return $return;
	}
	/**
	 * Check if a field is available
	 * @param	string	the wanted username
	 * @return	boolean	true if the wanted username is available
	 */
	function is_extra_field_available($fieldname)
	{
		$t_uf = Database :: get_main_table(TABLE_MAIN_USER_FIELD);
		$sql = "SELECT * FROM $t_uf WHERE field_variable = '".Database::escape_string($fieldname)."'";
		$res = api_sql_query($sql,__FILE__,__LINE__);
		return Database::num_rows($res) <= 0;
	}
	/**
	 * Gets user extra fields data
	 * @param	integer	User ID
	 * @param	boolean	Whether to prefix the fields indexes with "extra_" (might be used by formvalidator)
	 * @param	boolean	Whether to return invisible fields as well
	 * @param	boolean	Whether to split multiple-selection fields or not
	 * @return	array	Array of fields => value for the given user
	 */
	function get_extra_user_data($user_id, $prefix=false, $all_visibility = true, $splitmultiple=false)
	{
		$extra_data = array();
		$t_uf = Database::get_main_table(TABLE_MAIN_USER_FIELD);
		$t_ufv = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
		$user_id = Database::escape_string($user_id);
		$sql = "SELECT f.id as id, f.field_variable as fvar, f.field_type as type FROM $t_uf f ";
		if($all_visibility == false)
		{
			$sql .= " WHERE f.field_visible = 1 ";
		}
		$sql .= " ORDER BY f.field_order";
		$res = api_sql_query($sql,__FILE__,__LINE__);
		if(Database::num_rows($res)>0)
		{
			while($row = Database::fetch_array($res))
			{
				$sqlu = "SELECT field_value as fval " .
						" FROM $t_ufv " .
						" WHERE field_id=".$row['id']."" .
						" AND user_id=".$user_id;
				$resu = api_sql_query($sqlu,__FILE__,__LINE__);
				$fval = '';
				if(Database::num_rows($resu)>0)
				{
					$rowu = Database::fetch_array($resu);
					$fval = $rowu['fval'];
					if($row['type'] ==  USER_FIELD_TYPE_SELECT_MULTIPLE)
					{
						$fval = split(';',$rowu['fval']);
					}
				}
				if($prefix)
				{
					$extra_data['extra_'.$row['fvar']] = $fval; 
				}
				else
				{
					$extra_data[$row['fvar']] = $fval; 
				}
			}
		}
		
		return $extra_data;
	}
	
	/**
	 * Gives a list of [session_id-course_code] => [status] for the current user.
	 * @param integer $user_id
	 * @return array  list of statuses (session_id-course_code => status)
	 */
	function get_personal_session_course_list($user_id)
	{
		// Database Table Definitions
		$tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
		$tbl_course 				= Database :: get_main_table(TABLE_MAIN_COURSE);
		$tbl_user 					= Database :: get_main_table(TABLE_MAIN_USER);
		$tbl_session 				= Database :: get_main_table(TABLE_MAIN_SESSION);
		$tbl_session_user			= Database :: get_main_table(TABLE_MAIN_SESSION_USER);
		$tbl_course_user 			= Database :: get_main_table(TABLE_MAIN_COURSE_USER);
		$tbl_session_course 		= Database :: get_main_table(TABLE_MAIN_SESSION_COURSE);
		$tbl_session_course_user 	= Database :: get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
	
		// variable initialisation
		$personal_course_list_sql = '';
		$personal_course_list = array();
	
		//Courses in which we suscribed out of any session
		/*$personal_course_list_sql = "SELECT course.code k, course.directory d, course.visual_code c, course.db_name db, course.title i,
											course.tutor_name t, course.course_language l, course_rel_user.status s, course_rel_user.sort sort,
											course_rel_user.user_course_cat user_course_cat
											FROM    ".$tbl_course."       course,".$main_course_user_table."   course_rel_user
											WHERE course.code = course_rel_user.course_code"."
											AND   course_rel_user.user_id = '".$user_id."'
											ORDER BY course_rel_user.user_course_cat, course_rel_user.sort ASC,i";*/
	
		$tbl_user_course_category    = Database :: get_user_personal_table(TABLE_USER_COURSE_CATEGORY);
		
		$personal_course_list_sql = "SELECT course.code k, course.directory d, course.visual_code c, course.db_name db, course.title i, course.tutor_name t, course.course_language l, course_rel_user.status s, course_rel_user.sort sort, course_rel_user.user_course_cat user_course_cat
										FROM    ".$tbl_course_user." course_rel_user
										LEFT JOIN ".$tbl_course." course
										ON course.code = course_rel_user.course_code
										LEFT JOIN ".$tbl_user_course_category." user_course_category
										ON course_rel_user.user_course_cat = user_course_category.id
										WHERE  course_rel_user.user_id = '".$user_id."'
										ORDER BY user_course_category.sort, course_rel_user.sort ASC, i";
		
		$course_list_sql_result = api_sql_query($personal_course_list_sql, __FILE__, __LINE__);
		
		while ($result_row = Database::fetch_array($course_list_sql_result))
		{
			$personal_course_list[] = $result_row;
		}
	
		// get the list of sessions where the user is subscribed as student
		$sessions_sql = "SELECT DISTINCT id, name, date_start, date_end
								FROM $tbl_session_user, $tbl_session
								WHERE id_session=id AND id_user=$user_id
								AND (date_start <= NOW() AND date_end >= NOW() OR date_start='0000-00-00')
								ORDER BY date_start, date_end, name";
		$result = api_sql_query($sessions_sql,__FILE__,__LINE__);
	
		$sessions=api_store_result($result);
	
		$sessions = array_merge($sessions , api_store_result($result));
	
		// get the list of sessions where the user is subscribed as coach in a course
		$sessions_sql = "SELECT DISTINCT id, name, date_start, date_end
								FROM $tbl_session as session
								INNER JOIN $tbl_session_course as session_rel_course
									ON session_rel_course.id_coach = $user_id
								AND (date_start <= NOW() AND date_end >= NOW() OR date_start='0000-00-00')
								ORDER BY date_start, date_end, name";
		$result = api_sql_query($sessions_sql,__FILE__,__LINE__);
	
		$session_is_coach = api_store_result($result);
	
		$sessions = array_merge($sessions , $session_is_coach);
	
		// get the list of sessions where the user is subscribed as coach
		$sessions_sql = "SELECT DISTINCT id, name, date_start, date_end
								FROM $tbl_session as session
								WHERE session.id_coach = $user_id
								AND (date_start <= NOW() AND date_end >= NOW() OR date_start='0000-00-00')
								ORDER BY date_start, date_end, name";
		$result = api_sql_query($sessions_sql,__FILE__,__LINE__);
	
		$sessions = array_merge($sessions , api_store_result($result));
	
	
		if(api_is_allowed_to_create_course())
		{
			foreach($sessions as $enreg)
			{
				$id_session = $enreg['id'];
				$personal_course_list_sql = "SELECT DISTINCT course.code k, course.directory d, course.visual_code c, course.db_name db, course.title i, CONCAT(user.lastname,' ',user.firstname) t, email, course.course_language l, 1 sort, category_code user_course_cat, date_start, date_end, session.id as id_session, session.name as session_name
											 FROM $tbl_session_course as session_course
											 INNER JOIN $tbl_course AS course
											 	ON course.code = session_course.course_code
											 INNER JOIN $tbl_session as session
												ON session.id = session_course.id_session
											 LEFT JOIN $tbl_user as user
												ON user.user_id = session_course.id_coach
											 WHERE session_course.id_session = $id_session
											 AND (session_course.id_coach=$user_id OR session.id_coach=$user_id)
											ORDER BY i";
	
				$course_list_sql_result = api_sql_query($personal_course_list_sql, __FILE__, __LINE__);
	
				while ($result_row = Database::fetch_array($course_list_sql_result))
				{
					$result_row['s'] = 2;
					$key = $result_row['id_session'].' - '.$result_row['k'];
					$personal_course_list[$key] = $result_row;
				}
			}
	
		}
	
		foreach($sessions as $enreg)
		{
			$id_session = $enreg['id'];
			$personal_course_list_sql = "SELECT DISTINCT course.code k, course.directory d, course.visual_code c, course.db_name db, course.title i, CONCAT(user.lastname,' ',user.firstname) t, email, course.course_language l, 1 sort, category_code user_course_cat, date_start, date_end, session.id as id_session, session.name as session_name, IF(session_course.id_coach = ".$user_id.",'2', '5')
										 FROM $tbl_session_course as session_course
										 INNER JOIN $tbl_course AS course
										 	ON course.code = session_course.course_code
										 LEFT JOIN $tbl_user as user
											ON user.user_id = session_course.id_coach
										 INNER JOIN $tbl_session_course_user
											ON $tbl_session_course_user.id_session = $id_session
											AND $tbl_session_course_user.id_user = $user_id
										INNER JOIN $tbl_session  as session
											ON session_course.id_session = session.id
										 WHERE session_course.id_session = $id_session
										 ORDER BY i";
	
			$course_list_sql_result = api_sql_query($personal_course_list_sql, __FILE__, __LINE__);
	
			while ($result_row = Database::fetch_array($course_list_sql_result))
			{
				$key = $result_row['id_session'].' - '.$result_row['k'];
				$result_row['s'] = $result_row['14'];
	
				if(!isset($personal_course_list[$key]))
				{
					$personal_course_list[$key] = $result_row;
				}
			}
		}
		//print_r($personal_course_list);
	
		return $personal_course_list;
	}
}
?>