public static User GetUserFromUserId(int userId)
{
User toReturn;
using (SqlConnection connection = ConnectionManager.GetConnection())
{
var data = new ActivEarthDataProvidersDataContext(connection);
toReturn = (from u in data.UserDataProviders
join p in data.ProfileDataProviders on u.id equals p.user_id
where u.id == userId
select
new User
{
UserName = u.user_name,
UserID = u.id,
Email = p.email,
FirstName = p.first_name,
LastName = p.last_name,
City = p.city,
State = p.state,
Gender = p.gender,
ProfileID = p.id,
Age = p.age,
Weight = p.weight,
Height = p.height
}).FirstOrDefault();
}
if (toReturn != null)
{
toReturn.userPrivacySettings = PrivacySettingDAO.GetPrivacySettingFromUserId(toReturn.UserID);
}
return toReturn;
}
/// <summary>
/// Saves a Team as a new entry in the DB.
/// </summary>
/// <param name="team">Team object to add to the DB.</param>
/// <returns>ID of the created team on success, 0 on failure.</returns>
public static int CreateNewTeam(ContestTeam team)
{
try
{
int id;
using (SqlConnection connection = ConnectionManager.GetConnection())
{
var data = new ActivEarthDataProvidersDataContext(connection);
var teamData = new TeamDataProvider
{
name = team.Name,
score = team.Score,
contest_id = team.ContestId,
locked = team.IsLocked,
group_id = team.GroupId,
bracket = team.Bracket
};
data.TeamDataProviders.InsertOnSubmit(teamData);
data.SubmitChanges();
id = teamData.id;
}
TeamDAO.UpdateTeamMembers(team);
return id;
}
catch (Exception)
{
return 0;
}
}
/// <summary>
/// Retrieves the collection of all recycling centers submitted to the server.
/// </summary>
/// <returns>Recycle Centers submitted to the server.</returns>
public static List<RecycleCenter> GetRecyclingCenters()
{
using (SqlConnection connection = ConnectionManager.GetConnection())
{
var data = new ActivEarthDataProvidersDataContext(connection);
return (from c in data.RecyclingCenterDataProviders
select
new RecycleCenter
{
UserId = c.user_id,
Location = c.location,
Comments = c.comments,
Automotive = c.automotive,
Electronics = c.electronics,
Construction = c.construction,
Batteries = c.batteries,
Garden = c.garden,
Glass = c.glass,
Hazardous = c.hazardous,
Household = c.household,
Metal = c.metal,
Paint = c.paint,
Paper = c.paper,
Plastic = c.plastic
}).ToList();
}
}
/// <summary>
/// Creates a new DB entry for a team member.
/// </summary>
/// <param name="teamMember">Member to be added to the DB.</param>
/// <param name="teamId">Team ID that the member should be added to.</param>
/// <returns>ID of the newly added Team Member entry on success, 0 on failure.</returns>
public static int CreateNewTeamMember(ContestTeamMember teamMember, int teamId)
{
try
{
int contestId = TeamDAO.GetContestIdFromTeamId(teamId);
using (SqlConnection connection = ConnectionManager.GetConnection())
{
if (TeamDAO.UserCompetingInContest(teamMember.UserId, contestId)) { throw new Exception("User is already competing in the contest"); }
var data = new ActivEarthDataProvidersDataContext(connection);
var userData = new TeamMemberDataProvider
{
contest_id = contestId,
team_id = teamId,
user_id = teamMember.UserId,
score = teamMember.Score,
initialized = teamMember.Initialized,
initial_score = teamMember.InitialScore
};
data.TeamMemberDataProviders.InsertOnSubmit(userData);
data.SubmitChanges();
return userData.id;
}
}
catch (Exception)
{
return 0;
}
}
/// <summary>
/// Saves a user statistic as a new entry in the DB.
/// </summary>
/// <param name="userId">User to create stat for.</param>
/// <param name="statType">Statistic type to create.</param>
/// <param name="val">Value of the statistic.</param>
/// <returns>ID of the created statistic on success, 0 on failure.</returns>
public static int CreateNewStatisticForUser(int userId, Statistic statType, float val)
{
try
{
int id;
using (SqlConnection connection = ConnectionManager.GetConnection())
{
var data = new ActivEarthDataProvidersDataContext(connection);
var statisticData = new UserStatisticDataProvider
{
user_id = userId,
value = val,
statistic_type = (byte)statType
};
data.UserStatisticDataProviders.InsertOnSubmit(statisticData);
data.SubmitChanges();
id = statisticData.id;
}
return id;
}
catch (Exception)
{
return 0;
}
}
/// <summary>
/// Adds a Group's the list of Recent Acitivity Messages stored in the Database to the Group's Wall.
/// </summary>
/// <param name="group">The Group for which to retrieve Recent Activity Messages</param>
public static void GetGroupRecentActivity(Group group)
{
int groupId = group.ID;
try
{
using (SqlConnection connection = ConnectionManager.GetConnection())
{
var data = new ActivEarthDataProvidersDataContext(connection);
List<Message> messages = (from m in data.MessageDataProviders
where m.group_id == groupId
select
new Message(m.title, m.message, UserDAO.GetUserFromUserId(m.user_id), m.date, m.time)
).ToList();
foreach (Message message in messages)
{
group.Post(message);
}
}
}
catch (Exception)
{
}
}
/// <summary>
/// Adds a new Carpool to the database.
/// </summary>
/// <param name="carpool">Carpool to add to the database.</param>
/// <returns>Primary Key (ID) of the newly added Carpool.</returns>
public static int AddCarpool(Carpool carpool, out string errorMessage)
{
try
{
using (SqlConnection connection = ConnectionManager.GetConnection())
{
errorMessage = String.Empty;
var data = new ActivEarthDataProvidersDataContext(connection);
var carpoolData = new CarpoolDataProvider
{
start = carpool.Start,
destination = carpool.Destination,
time = carpool.Time,
seats_available = carpool.SeatsAvailable,
comments = carpool.Comments,
user_id = carpool.UserId
};
data.CarpoolDataProviders.InsertOnSubmit(carpoolData);
data.SubmitChanges();
return carpoolData.id;
}
}
catch (Exception e)
{
errorMessage = e.Message;
return 0;
}
}
/// <summary>
/// Saves a Team as a new entry in the DB.
/// </summary>
/// <param name="team">Team object to add to the DB.</param>
/// <returns>ID of the created team on success, 0 on failure.</returns>
public static int CreateNewTeam(Team team, int contestId)
{
try
{
int id;
using (SqlConnection connection = ConnectionManager.GetConnection())
{
var data = new ActivEarthDataProvidersDataContext(connection);
var teamData = new TeamDataProvider
{
name = team.Name,
score = team.Score,
contest_id = contestId
};
data.TeamDataProviders.InsertOnSubmit(teamData);
data.SubmitChanges();
id = teamData.id;
}
UpdateTeamMembers(team);
return id;
}
catch (Exception)
{
return 0;
}
}
/// <summary>
/// Retrieves a PrivacySetting from the DB based on its ID.
/// </summary>
/// <param name="privacySettingId">Identifier of the privacy setting to retrieve.</param>
/// <returns>Privacy setting specified by the provided ID.</returns>
public static PrivacySetting GetPrivacySettingFromPrivacySettingId(int privacySettingId)
{
PrivacySetting toReturn;
using (SqlConnection connection = ConnectionManager.GetConnection())
{
var data = new ActivEarthDataProvidersDataContext(connection);
toReturn = (from p in data.PrivacySettingDataProviders
where p.id == privacySettingId
select
new PrivacySetting
{
ID = p.id,
Email = p.email,
Gender = p.gender,
Age = p.age,
Height = p.height,
Weight = p.weight,
Group = p.groups,
ProfileVisibility = (ProfileVisibility)p.profile_visibility,
UserID = p.user_id
}).FirstOrDefault();
}
if (toReturn != null)
{
toReturn.User = UserDAO.GetUserFromUserId(toReturn.UserID);
}
return toReturn;
}
/// <summary>
/// Saves a privacy setting as a new entry in the DB.
/// </summary>
/// <param name="privacySetting">Privacy setting object to add to the DB.</param>
/// <returns>ID of the created privacy setting on success, 0 on failure.</returns>
public static int CreateNewPrivacySetting(PrivacySetting privacySetting)
{
try
{
int id;
using (SqlConnection connection = ConnectionManager.GetConnection())
{
var data = new ActivEarthDataProvidersDataContext(connection);
var privacySettingData = new PrivacySettingDataProvider
{
id = privacySetting.ID,
email = privacySetting.Email,
gender = privacySetting.Gender,
age = privacySetting.Age,
height = privacySetting.Height,
weight = privacySetting.Weight,
profile_visibility = (Byte)privacySetting.ProfileVisibility,
user_id = privacySetting.UserID
};
data.PrivacySettingDataProviders.InsertOnSubmit(privacySettingData);
data.SubmitChanges();
id = privacySettingData.id;
}
return id;
}
catch (Exception)
{
return 0;
}
}
/// <summary>
/// Saves a challenge as a new entry in the DB.
/// </summary>
/// <param name="challenge">Challenge object to add to the DB.</param>
/// <returns>ID of the created challenge on success, 0 on failure.</returns>
public static int CreateNewChallenge(Challenge challenge)
{
try
{
using (SqlConnection connection = ConnectionManager.GetConnection())
{
var data = new ActivEarthDataProvidersDataContext(connection);
var challengeData = new ChallengeDataProvider
{
name = challenge.Name,
description = challenge.Description,
reward = challenge.Reward,
requirement = challenge.Requirement,
persistent = challenge.IsPersistent,
end_time = challenge.EndTime,
duration_days = challenge.Duration.Days,
statistic = (byte)challenge.StatisticBinding,
active = challenge.IsActive,
image_path = challenge.ImagePath
};
data.ChallengeDataProviders.InsertOnSubmit(challengeData);
data.SubmitChanges();
return challengeData.id;
}
}
catch (Exception)
{
return 0;
}
}
/// <summary>
/// Retrieves the collection of routes submitted by a given user.
/// </summary>
/// <param name="userId">Identifier of the user.</param>
/// <returns>Routes belonging to the user specified by the provided ID.</returns>
public static List<Route> GetRoutesFromUserId(int userId)
{
using (SqlConnection connection = ConnectionManager.GetConnection())
{
var data = new ActivEarthDataProvidersDataContext(connection);
return (from c in data.ActiveRouteDataProviders
where c.user_id == userId
select
new Route
{
GMTOffset = c.gmt_offset,
Distance = c.distance,
EndLatitude = c.end_latitude,
EndLongitude = c.end_longitude,
EndTime = c.end_time,
Mode = c.mode,
Points = c.points,
StartLatitude = c.start_latitude,
StartLongitude = c.start_longitude,
StartTime = c.start_time,
Steps = c.steps,
Type = c.type,
UserId = c.user_id
}).ToList();
}
}
public static User GetUserFromUserNameAndPassword(string userName, string password)
{
using (SqlConnection connection = ConnectionManager.GetConnection())
{
var data = new ActivEarthDataProvidersDataContext(connection);
return
(from u in data.UserDataProviders
join p in data.ProfileDataProviders on u.id equals p.user_id
where u.user_name == userName && u.password == password
select
new User
{
UserName = u.user_name,
UserID = u.id,
Email = p.email,
FirstName = p.first_name,
LastName = p.last_name,
City = p.city,
State = p.state,
Gender = p.gender,
ProfileID = p.id,
Age = p.age,
Weight = p.weight,
Height = p.height
}).FirstOrDefault();
}
}
public static UserDataProvider GetUserFromUserName(string userName)
{
using (SqlConnection connection = ConnectionManager.GetConnection())
{
var data = new ActivEarthDataProvidersDataContext(connection);
return data.UserDataProviders.FirstOrDefault(u => u.user_name == userName);
}
}
/// <summary>
/// Retrieves the reward for a particular badge.
/// </summary>
/// <param name="stat">Statistic for which the badge is awarded.</param>
/// <param name="badgeLevel">Desired level of the badge.</param>
/// <returns>Reward for the given badge.</returns>
public static float GetBadgeReward(Statistic stat, int badgeLevel)
{
using (SqlConnection connection = ConnectionManager.GetConnection())
{
var data = new ActivEarthDataProvidersDataContext(connection);
return (from c in data.BadgeConstantsDataProviders
where c.statistic == (byte)stat && c.level == badgeLevel
select (float)c.reward).FirstOrDefault();
}
}
/// <summary>
/// Retrieves the statistic requirements for all levels of a particular badge.
/// </summary>
/// <param name="stat">Statistic for which the badge is awarded.</param>
/// <returns>Statistic requirement for the given badge.</returns>
public static float[] GetBadgeRequirementArray(Statistic stat)
{
using (SqlConnection connection = ConnectionManager.GetConnection())
{
var data = new ActivEarthDataProvidersDataContext(connection);
return (from c in data.BadgeConstantsDataProviders
where c.statistic == (byte)stat
select (float)c.requirement).ToArray();
}
}
/// <summary>
/// Retrieves the image path for a particular badge.
/// </summary>
/// <param name="stat">Statistic for which the badge is awarded.</param>
/// <param name="badgeLevel">Desired level of the badge.</param>
/// <returns>Reward for the given badge.</returns>
public static string GetBadgeImagePath(Statistic stat, int badgeLevel)
{
using (SqlConnection connection = ConnectionManager.GetConnection())
{
var data = new ActivEarthDataProvidersDataContext(connection);
return (from c in data.BadgeConstantsDataProviders
where c.statistic == (byte)stat && c.level == badgeLevel
select c.image_path).FirstOrDefault();
}
}
/// <summary>
/// Retrieves the statistic requirement for a particular badge.
/// </summary>
/// <param name="stat">Statistic for which the badge is awarded.</param>
/// <param name="badgeLevel">Desired level of the badge.</param>
/// <returns>Statistic requirement for the given badge.</returns>
public static float GetBadgeRequirement(Statistic stat, int badgeLevel)
{
using (SqlConnection connection = ConnectionManager.GetConnection())
{
var data = new ActivEarthDataProvidersDataContext(connection);
float foundEntry = (from c in data.BadgeConstantsDataProviders
where c.statistic == (byte)stat && c.level == badgeLevel
select (float)c.requirement).FirstOrDefault();
return (foundEntry >= 0 ? (float)foundEntry : float.PositiveInfinity);
}
}
/// <summary>
/// Retrieves the name for a particular statistic.
/// </summary>
/// <param name="stat">Statistic to query.</param>
/// <returns>Name to be used for the statistic.</returns>
public static string GetStatisticName(Statistic stat)
{
using (SqlConnection connection = ConnectionManager.GetConnection())
{
var data = new ActivEarthDataProvidersDataContext(connection);
string toReturn = (from c in data.StatisticConstantsDataProviders
where c.statistic_id == (byte)stat
select c.name).FirstOrDefault();
return (toReturn != null ? toReturn.Trim() : String.Empty);
}
}
/// <summary>
/// Creates a Group as a new entry in the DB.
/// </summary>
/// <param name="group">Group object to add to the DB.</param>
/// <returns>ID of the created Group on success, 0 on failure.</returns>
public static int CreateNewGroup(Group group)
{
try
{
using (SqlConnection connection = ConnectionManager.GetConnection())
{
var data = new ActivEarthDataProvidersDataContext(connection);
var groupData = new GroupDataProvider
{
name = group.Name,
description = group.Description,
owner_id = group.Owner.UserID,
green_score = group.GreenScore,
badge_score = group.ActivityScore.BadgeScore,
challenge_score = group.ActivityScore.ChallengeScore,
contest_score = group.ActivityScore.ContestScore,
};
data.GroupDataProviders.InsertOnSubmit(groupData);
data.SubmitChanges();
foreach (User member in group.Members) {
GroupMemberDataProvider memberData = new GroupMemberDataProvider
{
user_id = member.UserID,
group_id = groupData.id
};
data.GroupMemberDataProviders.InsertOnSubmit(memberData);
}
foreach (string tag in group.HashTags)
{
GroupHashtagDataProvider hashtagData = new GroupHashtagDataProvider
{
hashtag = tag,
group_id = groupData.id
};
data.GroupHashtagDataProviders.InsertOnSubmit(hashtagData);
}
data.SubmitChanges();
data.Connection.Close();
return groupData.id;
}
}
catch (Exception)
{
return 0;
}
}
/// <summary>
/// Retrieve's a UserStatistic from the DB based on the statistic's ID.
/// </summary>
/// <param name="statisticId">ID of statistic to fetch.</param>
/// <returns>The UserStatistic with the specified ID.</returns>
public static UserStatistic GetStatisticFromStatisticId(int statisticId)
{
using (SqlConnection connection = ConnectionManager.GetConnection())
{
var data = new ActivEarthDataProvidersDataContext(connection);
return (from c in data.UserStatisticDataProviders
where c.id == statisticId
select
new UserStatistic((Statistic)c.statistic_type, (float)c.value)
{
UserStatisticID = c.id,
UserID = c.user_id
}).FirstOrDefault();
}
}
/// <summary>
/// Retrieves all of a User's Statistics from the DB.
/// </summary>
/// <param name="userId">ID of user to fetch statistic info for.</param>
/// <returns>Statistic values for the user.</returns>
public static List<UserStatistic> GetAllStatisticsByUserId(int userId)
{
var returnDict = new Dictionary<Statistic, float>();
using (SqlConnection connection = ConnectionManager.GetConnection())
{
var data = new ActivEarthDataProvidersDataContext(connection);
return (from p in data.UserStatisticDataProviders
where p.user_id == userId
select new UserStatistic((Statistic)p.statistic_type, (float)p.value)
{
UserStatisticID = p.id,
UserID = p.user_id
}).ToList();
}
}
public static bool ConfirmPassword(string password, int userId)
{
try
{
using (SqlConnection connection = ConnectionManager.GetConnection())
{
var data = new ActivEarthDataProvidersDataContext(connection);
string oldPassword = (from u in data.UserDataProviders
where u.id == userId
select u.password).FirstOrDefault();
return oldPassword != null && oldPassword == password;
}
}
catch (Exception)
{
return false;
}
}
/// <summary>
/// Adds a new Route to the database.
/// </summary>
/// <param name="route">Route to add to the database.</param>
/// <returns>Primary Key (ID) of the newly added Route.</returns>
public static int AddNewRoute(Route route, out string errorMessage)
{
try
{
using (SqlConnection connection = ConnectionManager.GetConnection())
{
errorMessage = String.Empty;
var data = new ActivEarthDataProvidersDataContext(connection);
var routeData = new ActiveRouteDataProvider
{
gmt_offset = route.GMTOffset,
distance = route.Distance,
end_latitude = route.EndLatitude,
end_longitude = route.EndLongitude,
end_time = route.EndTime,
mode = route.Mode,
points = route.Points,
start_latitude = route.StartLatitude,
start_longitude = route.StartLongitude,
start_time = route.StartTime,
steps = route.Steps,
type = route.Type,
user_id = route.UserId
};
data.ActiveRouteDataProviders.InsertOnSubmit(routeData);
data.SubmitChanges();
if (routeData.id > 0)
{
ActiveRouteDAO.ProcessRoute(route);
}
return routeData.id;
}
}
catch (Exception e)
{
errorMessage = e.Message;
return 0;
}
}
/// <summary>
/// Retrieves the collection of all carpools submitted to the server.
/// </summary>
/// <returns>Carpools submitted to the server.</returns>
public static List<Carpool> GetCarpools()
{
using (SqlConnection connection = ConnectionManager.GetConnection())
{
var data = new ActivEarthDataProvidersDataContext(connection);
return (from c in data.CarpoolDataProviders
select
new Carpool
{
ID = c.id,
UserId = c.user_id,
Comments = c.comments,
Start = c.start,
Destination = c.destination,
Time = c.time,
SeatsAvailable = c.seats_available,
}).ToList();
}
}
public static bool CreateNewUser(User user, string password)
{
try
{
using (SqlConnection connection = ConnectionManager.GetConnection())
{
var data = new ActivEarthDataProvidersDataContext(connection);
var userData = new UserDataProvider {password = password, user_name = user.UserName,};
data.UserDataProviders.InsertOnSubmit(userData);
data.SubmitChanges();
return true;
}
}
catch (Exception)
{
return false;
}
}
public static IEnumerable<PrivacySetting> GetAllPrivacySettings()
{
using (SqlConnection connection = ConnectionManager.GetConnection())
{
var data = new ActivEarthDataProvidersDataContext(connection);
return (from p in data.PrivacySettingDataProviders
select
new PrivacySetting
{
ID = p.id,
Email = p.email,
Gender = p.gender,
Age = p.age,
Height = p.height,
Weight = p.weight,
Group = p.groups,
ProfileVisibility = p.profile_visibility,
UserID = p.user_id
});
}
}
/// <summary>
/// Adds a new Recycle Center to the database.
/// </summary>
/// <param name="center">Recycle Center to add to the database.</param>
/// <returns>Primary Key (ID) of the newly added Recycle Center.</returns>
public static int AddRecycleCenter(RecycleCenter center, out string errorMessage)
{
try
{
using (SqlConnection connection = ConnectionManager.GetConnection())
{
errorMessage = String.Empty;
var data = new ActivEarthDataProvidersDataContext(connection);
var centerData = new RecyclingCenterDataProvider
{
location = center.Location,
comments = center.Comments,
automotive = center.Automotive,
electronics = center.Electronics,
construction = center.Construction,
batteries = center.Batteries,
garden = center.Garden,
glass = center.Glass,
hazardous = center.Hazardous,
household = center.Household,
metal = center.Metal,
paint = center.Paint,
paper = center.Paper,
plastic = center.Plastic,
user_id = center.UserId
};
data.RecyclingCenterDataProviders.InsertOnSubmit(centerData);
data.SubmitChanges();
return centerData.id;
}
}
catch (Exception e)
{
errorMessage = e.Message;
return 0;
}
}
/// <summary>
/// Saves a contest as a new entry in the DB.
/// </summary>
/// <param name="contest">Contest object to add to the DB.</param>
/// <returns>ID of the created contest on success, 0 on failure.</returns>
public static int CreateNewContest(Contest contest)
{
try
{
int id;
using (SqlConnection connection = ConnectionManager.GetConnection())
{
var data = new ActivEarthDataProvidersDataContext(connection);
var contestData = new ContestDataProvider
{
name = contest.Name,
description = contest.Description,
points = contest.Points,
end_mode = (byte)contest.Mode,
end_goal = contest.EndCondition.EndValue,
end_time = contest.EndCondition.EndTime,
start = contest.StartTime,
type = (byte)contest.Type,
statistic = (byte)contest.StatisticBinding,
};
data.ContestDataProviders.InsertOnSubmit(contestData);
data.SubmitChanges();
id = contestData.id;
}
foreach (Team team in contest.Teams)
{
//TeamDAO.CreateNewTeam(team, contest.ID);
}
return id;
}
catch (Exception)
{
return 0;
}
}
/// <summary>
/// Retrieves a badge matching a provided ID.
/// </summary>
/// <param name="badgeId">Identifier of the badge.</param>
/// <returns>Badge matching the provided ID.</returns>
public static Badge GetBadgeFromBadgeId(int badgeId)
{
using (SqlConnection connection = ConnectionManager.GetConnection())
{
var data = new ActivEarthDataProvidersDataContext(connection);
Badge badge = (from c in data.BadgeDataProviders
where c.id == badgeId
select
new Badge
{
ID = c.id,
UserID = c.user_id,
StatisticBinding = (Statistic)c.statistic,
Level = c.badge_level,
Progress = c.progress
}).FirstOrDefault();
LoadExternalBadgeData(badge);
return badge;
}
}