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; } }