/// <summary>
/// Gets the details of a single request matching the given request ID.
/// </summary>
/// <param name="requestID">The request ID to search for.</param>
/// <returns>The details matching the request ID.</returns>
public Request GetRequest(long requestID, RequestType type)
{
Request returnVal = null;
MySqlDataReader dr = null;
String query = "";
if (type.Equals(RequestType.JOIN_TEAM)) {
query = "SELECT * FROM ";
query += AppConstants.MYSQL_TABLE_PERSON + " person, " + AppConstants.MYSQL_TABLE_TEAM + " team, " + AppConstants.MYSQL_TABLE_REQUESTS + " request ";
query += "WHERE person.personID=request.personID AND team.teamID=request.teamID AND request.requestID=" + requestID;
} else {
query = "SELECT * FROM ";
query += AppConstants.MYSQL_TABLE_PERSON + " person, " + AppConstants.MYSQL_TABLE_REQUESTS + " request ";
query += "WHERE person.personID=request.personID AND request.requestID=" + requestID;
}
try {
connection.Open();
command.CommandText = query;
dr = command.ExecuteReader();
Person requestee;
Team team;
if (dr.HasRows) {
dr.Read();
requestee = new Person(dr.GetString("firstName"), dr.GetString("lastName"));
requestee.ID = dr.GetInt64("personID");
requestee.email = dr.GetString("email");
//requestee.imageURL = dr.GetString("imageURL");
//requestee.height = dr.GetInt16("height");
//requestee.weight = dr.GetInt16("weight");
//requestee.birthday = dr.GetDateTime("birthday");
team = new Team();
if (type.Equals(RequestType.JOIN_TEAM)) {
team.ID = dr.GetInt64("teamID");
}
returnVal = new Request(type, requestee, team);
returnVal.ID = requestID;
}
else {
returnVal = null;
}
}
catch (Exception ex) {
returnVal = null;
}
finally {
connection.Close();
}
if (returnVal != null && returnVal.type.Equals(RequestType.JOIN_TEAM)) {
returnVal.team = GetTeamDetails(returnVal.team.ID);
}
return returnVal;
}
/// <summary>
/// Gets the requests from users who wish to have the coach permission.
/// </summary>
/// <returns>A list of the requests from users to have the coach permission added.</returns>
public List<Request> GetCoachRequests()
{
List<Request> returnVal = new List<Request>();
MySqlDataReader dr = null;
String query = "SELECT * FROM ";
query += AppConstants.MYSQL_TABLE_PERSON + " person, " + AppConstants.MYSQL_TABLE_REQUESTS + " request ";
query += "WHERE person.personID=request.personID AND request.type=" + (int)RequestType.COACH_PERMISSION;
try {
connection.Open();
command.CommandText = query;
dr = command.ExecuteReader();
Person requestee;
Request request;
while (dr.Read()) {
requestee = new Person(dr.GetString("firstName"), dr.GetString("lastName"));
requestee.ID = dr.GetInt64("personID");
requestee.email = dr.GetString("email");
request = new Request(RequestType.COACH_PERMISSION, requestee, new Team());
request.ID = dr.GetInt64("requestID");
returnVal.Add(request);
}
}
catch {
returnVal = null;
}
finally {
connection.Close();
}
return returnVal;
}
/// <summary>
/// Gets requests that are pending that were initiated by the person with the
/// corresponding email address.
/// </summary>
/// <param name="email">The email address to match.</param>
/// <returns>A list of requests initiated by the user with the given email.</returns>
public List<Request> GetMyOpenRequests(String email)
{
List<Request> returnVal = new List<Request>();
MySqlDataReader dr = null;
Person user = GetPersonInformation(email);
String query = "SELECT name, team.teamID, requestID, request.timestamp FROM ";
query += AppConstants.MYSQL_TABLE_TEAM + " team, " + AppConstants.MYSQL_TABLE_REQUESTS + " request ";
query += "WHERE team.teamID=request.teamID AND request.personID=" + user.ID;
try {
connection.Open();
command.CommandText = query;
dr = command.ExecuteReader();
Team team;
Request request;
while (dr.Read()) {
team = new Team();
team.name = dr.GetString("name");
team.ID = dr.GetInt64("teamID");
request = new Request(RequestType.JOIN_TEAM, user, team);
request.ID = dr.GetInt64("requestID");
request.timestamp = dr.GetDateTime("timestamp");
returnVal.Add(request);
}
}
catch {
returnVal = null;
}
finally {
connection.Close();
}
return returnVal;
}
/// <summary>
/// Gets requests tied to a specific user and team ID.
/// </summary>
/// <param name="teamID">The ID of the team.</param>
/// <param name="userID">The ID of the requestee.</param>
/// <returns>A list of requests matching the given team and requestee ID.</returns>
public List<Request> GetRequests(long teamID, long userID)
{
List<Request> returnVal = new List<Request>();
MySqlDataReader dr = null;
String query = "SELECT * FROM ";
query += AppConstants.MYSQL_TABLE_PERSON + " person, " + AppConstants.MYSQL_TABLE_TEAM + " team, " + AppConstants.MYSQL_TABLE_REQUESTS + " request ";
query += "WHERE person.personID=request.personID AND team.teamID=request.teamID AND request.teamID=" + teamID + " AND request.personID=" + userID;
try {
connection.Open();
command.CommandText = query;
dr = command.ExecuteReader();
Person requestee;
Team team;
Request request;
while (dr.Read()) {
requestee = new Person(dr.GetString("firstName"), dr.GetString("lastName"));
requestee.ID = dr.GetInt64("personID");
requestee.email = dr.GetString("email");
//requestee.setPassword(dr.GetString("password"));
//requestee.imageURL = dr.GetString("imageURL");
//requestee.birthday = dr.GetDateTime("birthday");
//requestee.height = dr.GetInt16("height");
//requestee.weight = dr.GetInt16("weight");
team = new Team();
team.name = dr.GetString("name");
request = new Request(RequestType.JOIN_TEAM, requestee, team);
request.ID = dr.GetInt64("requestID");
returnVal.Add(request);
}
}
catch {
returnVal = null;
}
finally {
connection.Close();
}
return returnVal;
}
