public int GetUserTeamRole(string UserID)
{
List <string[]> param = new List <string[]>()
{
new string[] { "@UserID", UserID },
};
var Role = SQLConnection.ExecuteSearchQueryParameters("SELECT Role FROM UserTeams Where UserID = @UserID", param);
return(Convert.ToInt32(Role[0][0]));
}
public TeamRoles GetRole(string userID, string teamID)
{
List <string[]> param = new List <string[]>
{
new string[] { "@UserID", userID },
new string[] { "@TeamID", teamID }
};
var result = SQLConnection.ExecuteSearchQueryParameters("SELECT Role FROM UserTeams WHERE UserID = @UserID AND TeamID = @TeamID", param);
var role = result.Count > 0 ? (TeamRoles)Convert.ToInt32(result[0]) : TeamRoles.NonMember;
return(role);
}
public TeamDTO FindTeamByName(string Name)
{
List <string[]> param = new List <string[]>()
{
new string[] { "@TeamName", Name },
};
var result = SQLConnection.ExecuteSearchQueryParameters($"SELECT * FROM Teams WHERE `TeamName` = @TeamName", param);
if (result.Count == 0)
{
return(null);
}
return(GenerateDTOFromRow(result[0]));
}
public TeamDTO FindTeamByID(string ID)
{
List <string[]> param = new List <string[]>()
{
new string[] { "@TeamID", ID },
};
var result = SQLConnection.ExecuteSearchQueryParameters($"SELECT * FROM Teams WHERE TeamID = @TeamID", param);
if (result.Count != 0)
{
return(GenerateDTOFromRow(result[0]));
}
else
{
return(null);
}
}
public TeamDTO FindTeamByUser(string userID)
{
List <string[]> param = new List <string[]>()
{
new string[] { "@UserID", userID },
};
var TeamID = SQLConnection.ExecuteSearchQueryParameters("SELECT TeamID FROM UserTeams WHERE UserID = @UserID", param);
if (TeamID.Count == 0)
{
return(null);
}
param = new List <string[]>()
{
new string[] { "@TeamID", TeamID[0][0] },
};
var Team = SQLConnection.ExecuteSearchQueryParameters("SELECT * FROM Teams Where TeamID = @TeamID", param);
return(GenerateDTOFromRow(Team[0]));
}
public List <string> GetMembers(string teamID)
{
List <string[]> param = new List <string[]>()
{
new string[] { "@TeamID", teamID },
};
List <string[]> Members = SQLConnection.ExecuteSearchQueryParameters(
@"SELECT Users.UserName
FROM UserTeams UT
INNER JOIN AspNetUsers Users
ON UT.UserID = Users.Id
WHERE UT.TeamID = @TeamID", param);
List <string> returnResult = new List <string>();
foreach (string[] row in Members)
{
returnResult.Add(row[0]);
}
return(returnResult);
}
public List <TeamMatchResultDTO> GetPreviousResults(string teamID)
{
List <string[]> param = new List <string[]>
{
new string[] { "@TeamID", teamID }
};
//SELECT T.TeamName FROM TeamMatch TM INNER JOIN Teams T ON T.TeamID = TM.TeamID WHERE MatchID IN (SELECT MatchID FROM TeamMatch TM WHERE TeamID = @TeamID) AND T.TeamID <> @TeamID
var result = SQLConnection.ExecuteSearchQueryParameters(
@"SELECT T.TeamName AS Enemy, TM.Score AS EnemyScore, (SELECT Score FROM TeamMatch WHERE TeamID = @TeamID) AS OwnScore
FROM (TeamMatch TM
INNER JOIN Teams T ON T.TeamID = TM.TeamID)
INNER JOIN `Match` M ON M.ID = TM.MatchID
WHERE MatchID IN
(
SELECT MatchID
FROM TeamMatch TM
WHERE TeamID = @TeamID
) AND T.TeamID <> @TeamID
ORDER BY M.PlayDate", param);
List <TeamMatchResultDTO> returnList = new List <TeamMatchResultDTO>();
foreach (var row in result)
{
TeamMatchResultDTO resultDTO = new TeamMatchResultDTO()
{
OpponentName = row[0],
ScoreOpponent = Convert.ToInt32(row[1]),
ScoreSelf = Convert.ToInt32(row[2]),
Won = Convert.ToInt32(row[1]) < Convert.ToInt32(row[2]),
};
resultDTO.Won = resultDTO.ScoreSelf > resultDTO.ScoreOpponent;
returnList.Add(resultDTO);
}
return(returnList);
}