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