/// <summary>
/// Gets the last time a pushpin was added to the corkboard.
/// </summary>
/// <param name="owner">Owner of the corkboard.</param>
/// <param name="title">Title of the corkboard.</param>
/// <returns>Returns the last time a pushpin was added to the corkboard, null otherwise.</returns>
public static DateTime GetLatestCorkboardUpdate(this Models.Corkboard corkboard)
{
var results = DatabaseHelper.ExecuteQuery($"SELECT date_time FROM pushpin WHERE owner_email = '{corkboard.Owner.Email}' AND title = '{corkboard.Title}' ORDER BY date_time DESC LIMIT 1");
var update = (results.Rows.Count > 0) ? Convert.ToDateTime(results.GetValueInTable("date_time")) : DateTime.MinValue;
return(update);
}
/// <summary>
/// Adds a corkboard for a user.
/// </summary>
/// <param name="owner">User to add the corkboard for.</param>
/// <param name="corkboard">Corkboard to add.</param>
public static void AddCorkboard(Models.User owner, Models.Corkboard corkboard)
{
DatabaseHelper.ExecuteQuery($"INSERT INTO corkboard ( title, visibility, owner_email, category_type) VALUES ('{corkboard.Title}', {GetCorkboardVisibility(corkboard.IsPrivate)}, '{owner.Email}', '{corkboard.Category}');");
// TODO create private corkboard if private, needs password to be passed
if (GetCorkboardVisibility(corkboard.IsPrivate) == 1)
{
AddPrivateCorkboard(owner, corkboard);
}
}
public static List <Models.User> GetCorkboardWatchers(Models.Corkboard corkboard)
{
var watchers = DatabaseHelper.ExecuteQuery($"Select * from Corkboard NATURAL JOIN Watch WHERE owner_email='{corkboard.Owner.Email}' AND title='{corkboard.Title}'");
var watchersList = new List <Models.User>();
foreach (DataRow row in watchers.Rows)
{
watchersList.Add(UserHelper.GetUserByEmail(row.GetValueInRow("email")));
}
return(watchersList);
}
public static Models.Corkboard CreateCorkboardFromDataRow(DataRow row)
{
var corkboard = new Models.Corkboard();
corkboard.Category = row.GetValueInRow("category_type");
corkboard.IsPrivate = GetCorkboardVisibility(row.GetValueInRow("visibility"));
corkboard.Title = row.GetValueInRow("title");
corkboard.Owner = UserHelper.GetUserByEmail(row.GetValueInRow("owner_email"));
corkboard.Pushpins = PushpinHelper.GetPushpinsForCorkboard(corkboard);
corkboard.LastUpdate = Convert.ToDateTime(corkboard.GetLatestCorkboardUpdate());
corkboard.Watchers = GetCorkboardWatchers(corkboard);
return(corkboard);
}
public static List <Models.Pushpin> GetPushpinsForCorkboard(Models.Corkboard corkboard)
{
var pushpinRows = DatabaseHelper.ExecuteQuery($"Select * From pushpin where owner_email = '{corkboard.Owner.Email}' AND title = '{corkboard.Title}'");
return(CreatePushpinsFromDataRows(pushpinRows.Rows));
}
/// <summary>
/// Adds a private corkboard for a user.
/// </summary>
/// <param name="owner">User to add the corkboard for.</param>
/// <param name="corkboard">Corkboard to add.</param>
public static void AddPrivateCorkboard(Models.User owner, Models.Corkboard corkboard)
{
DatabaseHelper.ExecuteQuery($"INSERT INTO private_corkboard ( title, owner_email, password) VALUES ('{corkboard.Title}', '{owner.Email}', '{corkboard.Password}');");
}
