public bool UpdateTeam(Team team)
{
try
{
using (var conn = AzureDatabase.GetConnection())
{
string query = "UPDATE Teams " +
"SET Name=@name, MaxMembers=@maxmembers, NumOfMembers=@numofmembers, " +
"City=@city, Description=@description, Difficulty=@difficulty, TerminationDate=@terminationdate " +
"WHERE Id=@id";
SqlCommand command = new SqlCommand(query, conn);
command.Parameters.AddWithValue("id", team.Id);
command.Parameters.AddWithValue("name", team.Name);
command.Parameters.AddWithValue("maxmembers", team.MaxMembers);
command.Parameters.AddWithValue("numofmembers", team.NumOfMembers);
command.Parameters.AddWithValue("city", team.City);
command.Parameters.AddWithValue("description", team.Description);
command.Parameters.AddWithValue("Difficulty", team.Difficulty);
command.Parameters.AddWithValue("TerminationDate", team.TerminationDate);
command.ExecuteNonQuery();
return(true);
}
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(false);
}
}
public User GetUser(string id)
{
try
{
User usr;
var conn = AzureDatabase.GetConnection();
SqlCommand command = new SqlCommand("SELECT * FROM Users WHERE Id=@id", conn);
command.Parameters.Add(new SqlParameter("id", id));
using (SqlDataReader reader = command.ExecuteReader())
{
//Non esiste utente con il dato id
if (!reader.Read())
{
return(null);
}
usr = new User(reader.GetString(1), reader.GetString(2), reader.GetString(3));
usr.Id = reader.GetString(0);
}
AzureDatabase.CloseConnection(conn);
return(usr);
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(null);
}
}
public List <int> GetTeams(int CategoryID)
{
List <int> teams = new List <int>();
try
{
var conn = AzureDatabase.GetConnection();
string query = "SELECT teamID FROM TeamCategories WHERE categoryID=@catid";
SqlCommand command = new SqlCommand(query, conn);
command.Parameters.AddWithValue("catid", CategoryID);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int team = reader.GetInt32(0);
teams.Add(team);
}
}
AzureDatabase.CloseConnection(conn);
return(teams);
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(null);
}
}
public int GetRequests(string TeamID, string UserID)
{
try
{
int value = 0;
var conn = AzureDatabase.GetConnection();
SqlCommand command = new SqlCommand("SELECT * FROM TeamMembers WHERE TeamId = @teamid AND UserId = @userid ", conn);
command.Parameters.Add(new SqlParameter("teamid", TeamID));
command.Parameters.Add(new SqlParameter("userid", UserID));
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
value = reader.GetInt32(3);
}
}
AzureDatabase.CloseConnection(conn);
return(value);
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(0);
}
}
public bool IsCategory(string TeamID, int CategoryID)
{
try
{
using (var conn = AzureDatabase.GetConnection())
{
SqlCommand command = new SqlCommand(
"SELECT COUNT(1) FROM TeamCategories WHERE (teamID=@teamid AND categoryID=@catid)",
conn);
command.Parameters.AddWithValue("teamid", TeamID);
command.Parameters.AddWithValue("catid", CategoryID);
int count = (int)command.ExecuteScalar();
AzureDatabase.CloseConnection(conn);
if (count == 1)
{
return(true);
}
return(false);
}
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(false);
}
}
public IEnumerable <User> GetUsers()
{
try
{
List <User> users = new List <User>();
var conn = AzureDatabase.GetConnection();
SqlCommand command = new SqlCommand("SELECT * FROM Users ", conn);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var usr = new User(reader.GetString(1), reader.GetString(2), reader.GetString(3));
usr.Id = reader.GetString(0);
users.Add(usr);
}
}
AzureDatabase.CloseConnection(conn);
return(users);
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(null);
}
}
public string FindUserByName(string nickname)
{
try
{
string id;
var conn = AzureDatabase.GetConnection();
SqlCommand command = new SqlCommand("SELECT * FROM Users WHERE NickName=@name", conn);
command.Parameters.Add(new SqlParameter("name", nickname));
using (SqlDataReader reader = command.ExecuteReader())
{
//Non esiste utente con il nome dato
if (!reader.Read())
{
return(null);
}
id = reader.GetString(0);
}
AzureDatabase.CloseConnection(conn);
return(id);
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(null);
}
}
public Dictionary <string, Tuple <double, double> > GetDetails()
{
Dictionary <string, Tuple <double, double> > teams = new Dictionary <string, Tuple <double, double> >();
try
{
var conn = AzureDatabase.GetConnection();
SqlCommand command = new SqlCommand("SELECT * FROM LocationDetails", conn);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var c = reader.GetString(0);
Tuple <double, double> detail = new Tuple <double, double>(reader.GetDouble(1), reader.GetDouble(2));
teams.Add(c, detail);
}
}
AzureDatabase.CloseConnection(conn);
return(teams);
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(null);
}
}
public (double, double) GetLocationDetails(string city)
{
double latitude = 0, longitude = 0;
try
{
var conn = AzureDatabase.GetConnection();
SqlCommand command = new SqlCommand("SELECT * FROM LocationDetails WHERE City=@city", conn);
command.Parameters.AddWithValue("city", city);
using (SqlDataReader reader = command.ExecuteReader())
{
if (!reader.Read())
{
return(0, 0);
}
latitude = reader.GetDouble(1);
longitude = reader.GetDouble(2);
}
AzureDatabase.CloseConnection(conn);
return(latitude, longitude);
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(0, 0);
}
}
public string GetDescription(string UserId, int CategoryId)
{
try
{
string desk = null;
var conn = AzureDatabase.GetConnection();
SqlCommand command = new SqlCommand("SELECT * FROM UserCategories WHERE UserId = @user AND Category = @cat ", conn);
command.Parameters.Add(new SqlParameter("user", UserId));
command.Parameters.Add(new SqlParameter("cat", CategoryId));
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
desk = reader.GetString(2);
}
}
AzureDatabase.CloseConnection(conn);
return(desk);
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(null);
}
}
public void AddDescription(string UserId, int CategoryId, string Description)
{
try
{
var conn = AzureDatabase.GetConnection();
SqlCommand command;
if (HasDescription(UserId, CategoryId))
{
command = new SqlCommand("UPDATE UserCategories SET Description = @desk WHERE UserId = @user AND CategoryId = @cat", conn);
}
else
{
command = new SqlCommand("INSERT INTO UserCategories VALUES (@user, @cat, @desk)", conn);
}
command.Parameters.Add(new SqlParameter("user", UserId));
command.Parameters.Add(new SqlParameter("cat", CategoryId));
command.Parameters.Add(new SqlParameter("desk", Description));
command.ExecuteNonQuery();
AzureDatabase.CloseConnection(conn);
return;
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return;
}
}
public List <UserContact> GetContacts(string userId)
{
try
{
List <UserContact> cntcs = new List <UserContact>();
var conn = AzureDatabase.GetConnection();
SqlCommand command = new SqlCommand("SELECT * FROM UserContacts WHERE UserId = @userid ", conn);
command.Parameters.Add(new SqlParameter("userid", userId));
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var cntc = new UserContact(reader.GetString(0), reader.GetString(1), reader.GetString(2));
cntcs.Add(cntc);
}
}
AzureDatabase.CloseConnection(conn);
return(cntcs);
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(null);
}
}
public UserContact GetContact(string userId, string type)
{
try
{
UserContact cntc;
var conn = AzureDatabase.GetConnection();
SqlCommand command = new SqlCommand("SELECT * FROM UserContacts WHERE UserId=@id AND Type = @type", conn);
command.Parameters.Add(new SqlParameter("id", userId));
command.Parameters.Add(new SqlParameter("type", type));
using (SqlDataReader reader = command.ExecuteReader())
{
//Non esiste utente con il dato id
if (!reader.Read())
{
return(null);
}
cntc = new UserContact(reader.GetString(0), reader.GetString(1), reader.GetString(2));
}
AzureDatabase.CloseConnection(conn);
return(cntc);
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(null);
}
}
public List <User> GetMembers(string TeamID)
{
try
{
List <User> users = new List <User>();
var conn = AzureDatabase.GetConnection();
SqlCommand command = new SqlCommand("SELECT * FROM TeamMembers WHERE TeamId = @id AND IsMember = 1 ", conn);
command.Parameters.Add(new SqlParameter("id", TeamID));
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var user = US.GetUser(reader.GetString(1));
{ users.Add(user); }
}
}
AzureDatabase.CloseConnection(conn);
return(users);
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(null);
}
}
public bool ModifyContact(string userId, string type, string newUri)
{
try
{
var conn = AzureDatabase.GetConnection();
SqlCommand command;
if (GetContact(userId, type) != null)
{
command = new SqlCommand("UPDATE UserContacts SET Uri = @uri WHERE UserId = @id AND Type = @type ", conn);
}
else
{
command = new SqlCommand("INSERT INTO UserContacts (UserId, Uri, Type) VALUES (@id, @uri, @type)", conn);
}
command.Parameters.Add(new SqlParameter("id", userId));
command.Parameters.Add(new SqlParameter("uri", newUri));
command.Parameters.Add(new SqlParameter("type", type));
command.ExecuteNonQuery();
AzureDatabase.CloseConnection(conn);
return(true);
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(false);
}
}
public List <Team> GetNonAdminTeams(string UserID)
{
try
{
List <Team> teams = new List <Team>();
var conn = AzureDatabase.GetConnection();
SqlCommand command = new SqlCommand("SELECT * FROM TeamMembers WHERE UserId = @id AND IsMember = 1 ", conn);
command.Parameters.Add(new SqlParameter("id", UserID));
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var team = TS.GetTeam(reader.GetString(0));
if (team.CreatorID != UserID)
{
teams.Add(team);
}
}
}
AzureDatabase.CloseConnection(conn);
return(teams);
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(null);
}
}
public bool InsertTeam(Team team)
{
try
{
using (var conn = AzureDatabase.GetConnection())
{
string query;
if (team.City == null)
{
query = "INSERT INTO Teams(Id,CreatorID,Name,MaxMembers,NumOfMembers,Description,CreationDate,Difficulty,TerminationDate) " +
"VALUES(@Id, @CreatorID, @Name, @MaxMembers, @NumOfMembers, @Description, @CreationDate,@Difficulty,@TerminationDate)";
}
else
{
query = "INSERT INTO Teams(Id,CreatorID,Name,MaxMembers,NumOfMembers,Description,City,CreationDate,Difficulty,TerminationDate) " +
"VALUES(@Id, @CreatorID, @Name, @MaxMembers, @NumOfMembers, @Description,@City,@CreationDate,@Difficulty,@TerminationDate)";
}
SqlCommand command = new SqlCommand(query, conn);
command.Parameters.AddWithValue("Id", team.Id);
command.Parameters.AddWithValue("CreatorID", team.CreatorID);
command.Parameters.AddWithValue("Name", team.Name);
command.Parameters.AddWithValue("MaxMembers", team.MaxMembers);
command.Parameters.AddWithValue("NumOfMembers", team.NumOfMembers);
command.Parameters.AddWithValue("Description", team.Description);
command.Parameters.AddWithValue("CreationDate", team.CreationDate);
command.Parameters.AddWithValue("Difficulty", team.Difficulty);
command.Parameters.AddWithValue("TerminationDate", team.TerminationDate);
if (team.City != null)
{
command.Parameters.AddWithValue("City", team.City);
}
command.ExecuteNonQuery();
AzureDatabase.CloseConnection(conn);
}
return(true);
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(false);
}
}
public bool RemoveAllUsersFromTeam(string TeamID)
{
try
{
var conn = AzureDatabase.GetConnection();
SqlCommand command = new SqlCommand("DELETE FROM TeamMembers WHERE TeamID = @TeamID", conn);
command.Parameters.AddWithValue("TeamID", TeamID);
command.ExecuteNonQuery();
AzureDatabase.CloseConnection(conn);
return(true);
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(false);
}
}
public bool RemoveAllNotificationsForUser(string UserID)
{
try
{
var conn = AzureDatabase.GetConnection();
SqlCommand command = new SqlCommand("DELETE FROM Notifications WHERE SenderID=@UserID OR ReceiverID=@UserID", conn);
command.Parameters.AddWithValue("UserID", UserID);
command.ExecuteNonQuery();
AzureDatabase.CloseConnection(conn);
return(true);
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(false);
}
}
public List <Notification> GetNotificationsSent(string senderID)
{
try
{
List <Notification> ntfs = new List <Notification>();
var conn = AzureDatabase.GetConnection();
SqlCommand command = new SqlCommand("SELECT * FROM Notifications WHERE SenderID = @senderid ", conn);
command.Parameters.Add(new SqlParameter("senderid", senderID));
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string teamId;
string message;
try
{
teamId = reader.GetString(3);
}
catch (Exception e)
{
teamId = null;
}
try
{
message = reader.GetString(5);
}
catch (Exception e)
{
message = null;
}
var ntf = new Notification(reader.GetString(1), reader.GetString(2), teamId, reader.GetInt32(4), message);
ntf.Id = reader.GetString(0);
ntfs.Add(ntf);
}
}
AzureDatabase.CloseConnection(conn);
return(ntfs);
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(null);
}
}
public void DeleteAllContacts(string userId)
{
try
{
var conn = AzureDatabase.GetConnection();
SqlCommand command = new SqlCommand("DELETE FROM UserContacts WHERE UserId = @id", conn);
command.Parameters.Add(new SqlParameter("id", userId));
command.ExecuteNonQuery();
AzureDatabase.CloseConnection(conn);
return;
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return;
}
}
public bool DeleteUser(string id)
{
try
{
var conn = AzureDatabase.GetConnection();
SqlCommand command = new SqlCommand("DELETE FROM Users WHERE Id = @id", conn);
command.Parameters.Add(new SqlParameter("id", id));
command.ExecuteNonQuery();
AzureDatabase.CloseConnection(conn);
return(true);
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(false);
}
}
public bool RemoveLocationDetails(string city)
{
using (var conn = AzureDatabase.GetConnection())
{
try
{
SqlCommand command = new SqlCommand("DELETE FROM LocationDetails WHERE City=@city");
command.Parameters.AddWithValue("city", city);
command.ExecuteNonQuery();
AzureDatabase.CloseConnection(conn);
return(true);
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(false);
}
}
}
public bool RemoveTeam(string id)
{
try
{
using (var conn = AzureDatabase.GetConnection())
{
SqlCommand command = new SqlCommand("DELETE FROM Teams WHERE Id=@id", conn);
command.Parameters.AddWithValue("id", id);
MessagingCenter.Send(this, "CityToRemove", GetTeam(id).City);
command.ExecuteNonQuery();
return(true);
}
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(false);
}
}
public IEnumerable <Team> GetActiveTeams()
{
List <Team> teams = new List <Team>();
try
{
var conn = AzureDatabase.GetConnection();
SqlCommand command = new SqlCommand("SELECT * FROM Teams " +
"WHERE (NumOfMembers < MaxMembers) AND (TerminationDate>@today)" +
"ORDER BY CreationDate DESC", conn);
command.Parameters.AddWithValue("today", DateTime.Today);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Team team = new Team();
team.Id = reader.GetString(0);
team.CreatorID = reader.GetString(1);
team.Name = reader.GetString(2);
team.MaxMembers = reader.GetInt32(3);
team.NumOfMembers = reader.GetInt32(4);
try { team.City = reader.GetString(5); }
catch
{
team.City = "";
}
team.Description = reader.GetString(6);
team.CreationDate = reader.GetDateTime(7);
team.Difficulty = reader.GetInt32(8);
team.TerminationDate = reader.GetDateTime(9);
teams.Add(team);
}
}
AzureDatabase.CloseConnection(conn);
return(teams);
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(null);
}
}
public bool AddRequest(string TeamID, string UserID)
{
try
{
int requests = GetRequests(TeamID, UserID);
using (var conn = AzureDatabase.GetConnection())
{
SqlCommand command;
if (requests == 0)
{
requests = requests + 1;
command = new SqlCommand("INSERT INTO TeamMembers VALUES (@teamid, @userid, 0, @requests)", conn);
command.Parameters.Add(new SqlParameter("teamid", TeamID));
command.Parameters.Add(new SqlParameter("userid", UserID));
command.Parameters.Add(new SqlParameter("requests", requests));
command.ExecuteNonQuery();
return(true);
}
else
{
string query = "UPDATE TeamMembers " +
"SET Requests = Requests+1 " +
"WHERE TeamID=@teamid AND UserID=@userid";
command = new SqlCommand(query, conn);
command.Parameters.AddWithValue("teamid", TeamID);
command.Parameters.AddWithValue("userid", UserID);
command.ExecuteNonQuery();
return(true);
}
}
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(false);
}
}
public bool AddContact(string userId, string contactUri, string type)
{
try
{
var conn = AzureDatabase.GetConnection();
SqlCommand command = new SqlCommand("INSERT INTO UserContacts VALUES (@userid, @uri, @type)", conn);
command.Parameters.Add(new SqlParameter("userid", userId));
command.Parameters.Add(new SqlParameter("uri", contactUri));
command.Parameters.Add(new SqlParameter("type", type));
command.ExecuteNonQuery();
AzureDatabase.CloseConnection(conn);
return(true);
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(false);
}
}
public bool UpdateUser(User u)
{
try
{
var conn = AzureDatabase.GetConnection();
SqlCommand command = new SqlCommand("UPDATE Users SET Email = @email, Pwd = @pwd, NickName = @nickname WHERE Id = @id", conn);
command.Parameters.Add(new SqlParameter("id", u.Id));
command.Parameters.Add(new SqlParameter("email", u.Email));
command.Parameters.Add(new SqlParameter("pwd", u.Pwd));
command.Parameters.Add(new SqlParameter("nickname", u.NickName));
command.ExecuteNonQuery();
AzureDatabase.CloseConnection(conn);
return(true);
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(false);
}
}
public bool InsertUser(User u)
{
try
{
var conn = AzureDatabase.GetConnection();
SqlCommand command = new SqlCommand("INSERT INTO Users VALUES (@id, @email, @pwd, @nickname)", conn);
command.Parameters.Add(new SqlParameter("id", u.Id));
command.Parameters.Add(new SqlParameter("email", u.Email));
command.Parameters.Add(new SqlParameter("pwd", u.Pwd));
command.Parameters.Add(new SqlParameter("nickname", u.NickName));
command.ExecuteNonQuery();
AzureDatabase.CloseConnection(conn);
return(true);
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(false);
}
}
public Team GetTeam(string id)
{
Team team = new Team();
try
{
var conn = AzureDatabase.GetConnection();
SqlCommand command = new SqlCommand("SELECT * FROM Teams WHERE Id=@id", conn);
command.Parameters.Add(new SqlParameter("id", id));
using (SqlDataReader reader = command.ExecuteReader())
{
if (!reader.Read())
{
return(null);
}
team.Id = reader.GetString(0);
team.CreatorID = reader.GetString(1);
team.Name = reader.GetString(2);
team.MaxMembers = reader.GetInt32(3);
team.NumOfMembers = reader.GetInt32(4);
try { team.City = reader.GetString(5); }
catch
{
team.City = "";
}
team.Description = reader.GetString(6);
team.CreationDate = reader.GetDateTime(7);
team.Difficulty = reader.GetInt32(8);
team.TerminationDate = reader.GetDateTime(9);
}
AzureDatabase.CloseConnection(conn);
return(team);
}
catch (SqlException ex)
{
Console.WriteLine("Error accessing the database: " + ex.Message);
return(null);
}
}
