public List <OmadaSurveysAverage> GetSurveysAverages(OmadaTeam team) { List <OmadaSurveysAverage> averageWeeks = new List <OmadaSurveysAverage>(); using (SqlConnection connection = DatabaseConnector.CreateConnection()) { using (SqlCommand command = connection.CreateCommand()) { command.CommandText = @"SELECT DATEPART(ww, SurveyDate) AS SURVEYS_WEEK, ROUND(AVG(CAST(FirstAnswer AS FLOAT)), 2) AS SURVEYS_AVG FROM Surveys WHERE USERID IN (SELECT UserID FROM Users_Teams WHERE TeamId = @TeamId AND YEAR(SurveyDate) = @SurveysYear) GROUP BY DATEPART(ww, SurveyDate) ORDER BY SURVEYS_WEEK"; command.Parameters.AddWithValue("@TeamId", team.Id); command.Parameters.AddWithValue("@SurveysYear", DateTime.UtcNow.Year); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { OmadaSurveysAverage averageWeek = new OmadaSurveysAverage(); averageWeek.Week = reader.GetInt32(0); averageWeek.Average = reader.GetDouble(1); averageWeeks.Add(averageWeek); } } } return(averageWeeks); } }
private void SetUsers(int?teamId) { if (!teamId.HasValue) { TeamExists = false; Team = new OmadaTeam(); NotTeamMembers = new List <NotTeamMember>(); foreach (var user in userManager.Users.Where(u => u.Id != userManager.GetUserId(User))) { NotTeamMembers.Add(new NotTeamMember() { User = user, IsSelected = false }); } } else { TeamExists = true; Team = teamData.GetTeamById(teamId.Value); NotTeamMembers = teamData.UsersNotInTeam(Team.Id); TeamMembers = new List <TeamMember>(); foreach (var member in teamData.GetTeamUsers(Team.Id)) { TeamMembers.Add(new TeamMember() { User = member, IsLeader = teamData.GetTeamLeaders(Team).Where(l => l.Id == member.Id).Any() ? true : false, Remove = false }); } } }
public List <OmadaSurvey> GetOpinionsFromCurrentWeek(OmadaTeam team, int week) { List <OmadaSurvey> opinions = new List <OmadaSurvey>(); using (SqlConnection connection = DatabaseConnector.CreateConnection()) { using (SqlCommand command = connection.CreateCommand()) { command.CommandText = @"SELECT SecondAnswer, ThirdAnswer FROM Surveys WHERE UserId IN (SELECT UserID FROM Users_Teams WHERE TeamId = @TeamId AND YEAR(SurveyDate) = @SurveysYear) AND DATEPART(ww, SurveyDate) = @Week"; command.Parameters.AddWithValue("@TeamId", team.Id); command.Parameters.AddWithValue("@SurveysYear", DateTime.UtcNow.Year); command.Parameters.AddWithValue("@Week", week); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { OmadaSurvey opinion = new OmadaSurvey(); opinion.SecondAnswer = reader.GetString(0); opinion.ThirdAnswer = reader.GetString(1); opinions.Add(opinion); } } } return(opinions); } }
public OmadaTeam Add(OmadaTeam team, string userId) { using (SqlConnection connection = DatabaseConnector.CreateConnection()) { SqlCommand command = new SqlCommand(); using (command = connection.CreateCommand()) { command.CommandText = @"INSERT INTO dbo.Teams VALUES(@Name, @IsPublic, @OpinionsVisible)"; command.Parameters.AddWithValue("@Name", team.Name); command.Parameters.AddWithValue("@IsPublic", team.IsPublic); command.Parameters.AddWithValue("@OpinionsVisible", team.OpinionsVisible); command.ExecuteNonQuery(); } using (command = connection.CreateCommand()) { command.CommandText = @"SELECT Id FROM dbo.Teams WHERE Name = @Name"; command.Parameters.AddWithValue("@Name", team.Name); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { team.Id = reader.GetInt32(0); } } } AddUserToTeam(userId, team.Id, 1); } return(team); }
public OmadaTeam GetTeamById(int teamId) { OmadaTeam team = new OmadaTeam(); using (SqlConnection connection = DatabaseConnector.CreateConnection()) { using (SqlCommand command = connection.CreateCommand()) { command.CommandText = @"SELECT * FROM dbo.Teams WHERE Id = @teamId"; command.Parameters.AddWithValue("@teamId", teamId); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { team.Id = reader.GetInt32(0); team.Name = reader.GetString(1); team.IsPublic = reader.GetBoolean(2); team.OpinionsVisible = reader.GetBoolean(3); } } } } return(team); }
public List <OmadaUser> GetTeamLeaders(OmadaTeam team) { List <OmadaUser> teamUsers = new List <OmadaUser>(); using (SqlConnection connection = DatabaseConnector.CreateConnection()) { using (SqlCommand command = connection.CreateCommand()) { command.CommandText = @"SELECT dbo.AspNetUsers.Id, dbo.AspNetUsers.UserName, dbo.AspNetUsers.Email FROM dbo.Users_Teams JOIN DBO.AspNetUsers ON Dbo.AspNetUsers.Id = dbo.Users_Teams.UserId AND dbo.Users_Teams.TeamId = @teamId AND dbo.Users_Teams.IsLeader = 1"; command.Parameters.AddWithValue("@teamId", team.Id); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { OmadaUser user = new OmadaUser(); user.Id = reader.GetString(0); user.UserName = reader.GetString(1); user.Email = reader.GetString(2); teamUsers.Add(user); } } } } return(teamUsers); }
public List <OmadaTeam> GetTeamsWhereUserNotMember(string userId) { List <OmadaTeam> teams = new List <OmadaTeam>(); using (SqlConnection connection = DatabaseConnector.CreateConnection()) { using (SqlCommand command = connection.CreateCommand()) { command.CommandText = @"SELECT DISTINCT dbo.Teams.Id, dbo.Teams.Name, dbo.Teams.IsPublic, dbo.Teams.OpinionsVisible FROM dbo.Users_Teams JOIN dbo.Teams ON dbo.Users_Teams.TeamId = dbo.Teams.Id AND dbo.Users_Teams.TeamId NOT IN (SELECT TeamId FROM dbo.Users_Teams WHERE UserId = @userId)"; command.Parameters.AddWithValue("@userId", userId); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { OmadaTeam team = new OmadaTeam(); team.Id = reader.GetInt32(0); team.Name = reader.GetString(1); team.IsPublic = reader.GetBoolean(2); team.OpinionsVisible = reader.GetBoolean(3); teams.Add(team); } } } } return(teams); }
public List <OmadaTeam> GetAllTeams() { List <OmadaTeam> teams = new List <OmadaTeam>(); using (SqlConnection connection = DatabaseConnector.CreateConnection()) { using (SqlCommand command = connection.CreateCommand()) { command.CommandText = @"SELECT * FROM dbo.Teams"; using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { OmadaTeam team = new OmadaTeam(); team.Id = reader.GetInt32(0); team.Name = reader.GetString(1); team.IsPublic = reader.GetBoolean(2); team.OpinionsVisible = reader.GetBoolean(3); teams.Add(team); } } } return(teams); } }
private void AddNewUsersToTeam(OmadaTeam team) { var selectedUsers = NotTeamMembers.Where(u => u.IsSelected == true).Select(u => u.User.Id).ToList(); foreach (var user in selectedUsers) { teamData.AddUserToTeam(user, team.Id, 0); } }
public IActionResult OnGet(int teamId) { Team = teamData.GetTeamById(teamId); if (Team.Name == null) { return(RedirectToPage("./NotFound")); } return(Page()); }
private void UpdateLeaders(OmadaTeam team) { List <OmadaUser> updatedLeaders = TeamMembers.Where(m => m.IsLeader == true).Select(m => m.User).ToList(); teamData.SetNoLeaders(team); foreach (var member in updatedLeaders) { teamData.UpdateLeaderStatus(member.Id, team); } }
public void SetNoLeaders(OmadaTeam team) { using (SqlConnection connection = DatabaseConnector.CreateConnection()) { using (SqlCommand command = connection.CreateCommand()) { command.CommandText = @"UPDATE dbo.Users_Teams SET IsLeader = 0 WHERE teamId = @teamId"; command.Parameters.AddWithValue("@teamId", team.Id); command.ExecuteNonQuery(); } } }
public void UpdateLeaderStatus(string userId, OmadaTeam team) { using (SqlConnection connection = DatabaseConnector.CreateConnection()) { using (SqlCommand command = connection.CreateCommand()) { command.CommandText = @"UPDATE dbo.Users_Teams SET IsLeader = 1 WHERE teamId = @teamId AND UserId = @userId"; command.Parameters.AddWithValue("@teamId", team.Id); command.Parameters.AddWithValue("@userId", userId); command.ExecuteNonQuery(); } } }
public OmadaTeam Update(OmadaTeam team) { using (SqlConnection connection = DatabaseConnector.CreateConnection()) { using (SqlCommand command = connection.CreateCommand()) { command.CommandText = @"UPDATE dbo.Teams SET Name = @Name, IsPublic = @IsPublic, OpinionsVisible = @OpinionsVisible WHERE Id = @Id"; command.Parameters.AddWithValue("@Id", team.Id); command.Parameters.AddWithValue("@Name", team.Name); command.Parameters.AddWithValue("@IsPublic", team.IsPublic); command.Parameters.AddWithValue("@OpinionsVisible", team.OpinionsVisible); command.ExecuteNonQuery(); } } return(team); }
public async Task <IActionResult> OnPostFinalAsync() { if (!ModelState.IsValid) { SetUsers(Team.Id); return(Page()); } if (Team.Id > 0) { OmadaTeam team = teamData.Update(Team); AddNewUsersToTeam(team); UpdateLeaders(team); } else { OmadaTeam team = teamData.Add(Team, userManager.GetUserId(User)); } await HttpContext.RefreshLoginAsync(); return(RedirectToPage("./TeamsList")); }