public Announcement(DatabaseManager manager, SqlDataReader r)
{
_manager = manager;
_id = (int)r["Id"];
_title = (string)r["Title"];
_body = (string)r["Body"];
_importance = (int)r["Importance"];
_startDate = (DateTime)r["StartDate"];
_endDate = (DateTime)r["EndDate"];
_createTime = (DateTime)r["CreateTime"];
_createUser = (int)r["CreateUser"];
_editTime = (DateTime)r["EditTime"];
_editUser = (r["EditUser"] is DBNull) ? -1 : (int)r["EditUser"];
_statusTime = (DateTime)r["StatusTime"];
_statusUser = (r["StatusUser"] is DBNull) ? -1 : (int)r["StatusUser"];
_statusMessage = (r["StatusMessage"] is DBNull) ? "" : (string)r["StatusMessage"];
_status = (int)r["Status"];
_scope = (r["Scope"] is DBNull) ? 0 : (int)r["Scope"];
}
public static void OpenConnection(string connectionString, IsolationLevel isolationLevel)
{
if (Current != null)
throw new InvalidOperationException("A database connection is already open!");
Current = new DatabaseManager(connectionString, isolationLevel);
}
public Club(DatabaseManager manager, SqlDataReader r)
{
_manager = manager;
_id = (int)r["Id"];
_name = (string)r["Name"];
_description = (string)r["Description"];
_location = (string)r["Location"];
_teacher = (int)r["Teacher"];
_weekday = (int)r["Weekday"];
_afterSchool = (bool)r["AfterSchool"];
_createTime = (DateTime)r["CreateTime"];
_createUser = (int)r["CreateUser"];
_editTime = (DateTime)r["EditTime"];
_editUser = (r["EditUser"] is DBNull) ? -1 : (int)r["EditUser"];
_statusTime = (DateTime)r["StatusTime"];
_statusUser = (r["StatusUser"] is DBNull) ? -1 : (int)r["StatusUser"];
_statusMessage = (r["StatusMessage"] is DBNull) ? "" : (string)r["StatusMessage"];
_status = (int)r["Status"];
}
public UserProfile(DatabaseManager manager, SqlDataReader r)
{
_manager = manager;
_id = (int)r["Id"];
_username = (string)r["Username"];
_displayname = (string)r["DisplayName"];
}
public UserProfile(DatabaseManager manager, string username)
{
_manager = manager;
_id = -1;
_username = username;
_displayname = username.Split('\\')[1];
}
public static UserProfile FromDatabase(DatabaseManager manager, int id)
{
using (SqlCommand cmd = manager.CreateCommand("SELECT * FROM Users WHERE Id=@id"))
{
cmd.Parameters.AddWithValue("@id", id);
using (SqlDataReader r = cmd.ExecuteReader())
{
if (r.Read())
return new UserProfile(manager, r);
else
return null;
}
}
}
public static List<Scope> AllFromDatabase(DatabaseManager manager)
{
List<Scope> scopes = new List<Scope>();
scopes.Add(everybodyScope);
using (SqlCommand cmd = manager.CreateCommand("SELECT * FROM Scopes"))
{
using (SqlDataReader r = cmd.ExecuteReader())
{
while (r.Read())
scopes.Add(new Scope(r));
}
return scopes;
}
}
public static Scope FromDatabase(DatabaseManager manager, int id)
{
if (id <= 0)
return everybodyScope;
using (SqlCommand cmd = manager.CreateCommand("SELECT * FROM Scopes WHERE Id=@id"))
{
cmd.Parameters.AddWithValue("@id", id);
using (SqlDataReader r = cmd.ExecuteReader())
{
if (r.Read())
return new Scope(r);
else
return null;
}
}
}
public static CompiledSecurityInfo CompileAccessLevel(DatabaseManager manager, IPrincipal user)
{
if (user != null && user.Identity != null && user.Identity.Name != String.Empty)
{
CompiledSecurityInfo level = new CompiledSecurityInfo();
level.AddPermissions(SecurityInfo.FromDatabase(manager, user.Identity.Name));
foreach (string group in Roles.GetRolesForUser())
{
level.AddPermissions(SecurityInfo.FromDatabase(manager, group));
}
return level;
}
else
{
return new CompiledSecurityInfo();
}
}
public static UserProfile FromDatabase(DatabaseManager manager, string name, bool create)
{
using (SqlCommand cmd = manager.CreateCommand("SELECT * FROM Users WHERE Username=@username"))
{
cmd.Parameters.AddWithValue("@username", name);
using (SqlDataReader r = cmd.ExecuteReader())
{
if (r.Read())
{
return new UserProfile(manager, r);
}
else if (!create)
{
return null;
}
}
}
UserProfile u = new UserProfile(manager, name);
u.Insert();
return u;
}
public static void PopulatePageNumber(DatabaseManager manager, UserProfile settings, Label currentPageLabel, Label maxPageLabel, string mode, int currentPage, int numPerPage)
{
using (SqlCommand cmd = manager.CreateCommand())
{
if (mode == "ViewAll" || mode == "Approval")
{
cmd.CommandText = "SELECT COUNT(*) FROM Clubs WHERE Status<>3";
}
else
{
cmd.CommandText = "SELECT COUNT(*) FROM Clubs WHERE Status<>3 AND CreateUser=@createUser";
}
cmd.Parameters.AddWithValue("@createUser", settings.Id);
cmd.Parameters.AddWithValue("@today", DateTime.Today);
maxPageLabel.Text = Math.Max(1, Math.Ceiling((((int)cmd.ExecuteScalar()) / (double)numPerPage))).ToString();
currentPageLabel.Text = currentPage.ToString();
}
}
public Club(DatabaseManager manager)
{
_manager = manager;
}
public static void PopulateClubTable(DatabaseManager manager, UserProfile settings, string mode, bool showDeleted, HtmlTable table, int offset, int rows, Dictionary<int, CheckBox> checkBoxes)
{
using (SqlCommand cmd = manager.CreateCommand())
{
if (mode == "ViewAll" || mode == "Approval")
{
cmd.CommandText = "SELECT * FROM Clubs WHERE 1=1";
}
else
{
cmd.CommandText = "SELECT * FROM Clubs WHERE CreateUser=@createUser";
}
if (!showDeleted)
cmd.CommandText += " AND Status<>3";
if (mode == "Approval")
cmd.CommandText += " ORDER BY (CASE WHEN Status = 0 THEN 1 ELSE 0 END) DESC, ";
else if (mode == "Submission")
cmd.CommandText += " ORDER BY (CASE WHEN Status = 2 THEN 1 ELSE 0 END) DESC, ";
else
cmd.CommandText += " ORDER BY ";
cmd.CommandText += "Name ASC OFFSET " + offset + " ROWS FETCH NEXT " + rows + " ROWS ONLY";
cmd.Parameters.AddWithValue("@createUser", settings.Id);
cmd.Parameters.AddWithValue("@weekday", DateTime.Today.DayOfWeek);
List<Club> clubs = new List<Club>();
using (SqlDataReader r = cmd.ExecuteReader())
{
if (r.HasRows)
{
while (r.Read())
{
clubs.Add(new Club(manager, r));
}
}
else
{
HtmlTableRow row = new HtmlTableRow();
HtmlTableCell cell;
row.Cells.Add(cell = new HtmlTableCell()
{
ColSpan = 5,
InnerHtml = "<em>There are currently no clubs requiring attention</em>",
});
cell.Style.Add("padding-left", "5px");
table.Rows.Add(row);
}
}
foreach (Club c in clubs)
{
HtmlTableRow row = new HtmlTableRow();
if (c.Status == ClubStatus.Deleted)
{
row.Style.Add("background", "#f3f3f3");
}
else if (mode == "Approval" && c.Status == Club.ClubStatus.Pending)
{
row.Style.Add("background", "#ffa4a4");
}
else if (mode == "Submission" && c.Status == Club.ClubStatus.Denied)
{
row.Style.Add("background", "#ffa4a4");
}
HtmlTableCell checkCell;
CheckBox chk;
if (checkBoxes != null)
{
row.Cells.Add(checkCell = new HtmlTableCell());
checkCell.Style.Add("text-align", "center");
checkCell.Controls.Add(chk = new CheckBox()
{
ID = "chk_club_" + c.Id
});
if (checkBoxes.ContainsKey(c.Id))
checkBoxes[c.Id] = chk;
else
checkBoxes.Add(c.Id, chk);
}
row.Cells.Add(new HtmlTableCell() { InnerHtml = c.Name });
row.Cells.Add(new HtmlTableCell() { InnerHtml = c.CreatorDisplayName });
row.Cells.Add(new HtmlTableCell() { InnerHtml = weekdays[c.Weekday] });
switch (c.Status)
{
case Club.ClubStatus.Pending:
row.Cells.Add(new HtmlTableCell() { InnerHtml = "Pending" });
break;
case Club.ClubStatus.Approved:
row.Cells.Add(new HtmlTableCell() { InnerHtml = "Approved" });
break;
case Club.ClubStatus.Denied:
row.Cells.Add(new HtmlTableCell() { InnerHtml = "Denied" });
break;
case Club.ClubStatus.Deleted:
row.Cells.Add(new HtmlTableCell() { InnerHtml = "Deleted" });
break;
default:
row.Cells.Add(new HtmlTableCell() { InnerHtml = "(Unknown)" });
break;
}
row.Cells.Add(new HtmlTableCell() { InnerHtml = "<a href=\"ClubEdit.aspx?id=" + c.Id + "\" class=\"linkbutton-small\" style=\"padding-left: 8px; padding-right: 8px;\">Edit</a>" });
table.Rows.Add(row);
}
}
}
public static void PopulateClubTable(DatabaseManager manager, UserProfile settings, CompiledSecurityInfo level, HtmlTable table, int offset, int rows, Dictionary<int, CheckBox> checkBoxes)
{
string mode;
if (level["CanApproveClub"] && level["CanViewAllClub"])
mode = "Approval";
else if (level["CanViewAllClub"])
mode = "ViewAll";
else
mode = "Submission";
PopulateClubTable(manager, settings, mode, false, table, offset, rows, checkBoxes);
}
public static void PopulateAnnouncementTable(DatabaseManager manager, UserProfile settings, string mode, bool viewExpired, bool viewDeleted, HtmlTable table, int offset, int rows, Dictionary<int, CheckBox> checkBoxes)
{
SqlCommand cmd;
if (mode == "ViewAll" || mode == "Approval")
{
cmd = manager.CreateCommand("SELECT * FROM Announcements WHERE 1=1");
}
else
{
cmd = manager.CreateCommand("SELECT * FROM Announcements WHERE CreateUser=@createUser");
}
try
{
if (!viewExpired)
cmd.CommandText += " AND EndDate>=@today";
if (!viewDeleted)
cmd.CommandText += " AND Status<>3";
if (mode == "Approval")
cmd.CommandText += " ORDER BY (CASE WHEN Status = 0 THEN 1 ELSE 0 END) DESC, ";
else if (mode == "Submission")
cmd.CommandText += " ORDER BY (CASE WHEN Status = 2 THEN 1 ELSE 0 END) DESC, ";
else
cmd.CommandText += " ORDER BY ";
cmd.CommandText += "Importance DESC, StartDate DESC OFFSET " + offset + " ROWS FETCH NEXT " + rows + " ROWS ONLY";
cmd.Parameters.AddWithValue("@createUser", settings.Id);
cmd.Parameters.AddWithValue("@today", DateTime.Today);
List<Announcement> announcements = new List<Announcement>();
using (SqlDataReader r = cmd.ExecuteReader())
{
if (r.HasRows)
{
while (r.Read())
{
announcements.Add(new Announcement(manager, r));
}
}
else
{
HtmlTableRow row = new HtmlTableRow();
HtmlTableCell cell;
row.Cells.Add(cell = new HtmlTableCell()
{
ColSpan = (checkBoxes == null) ? 6 : 7,
InnerHtml = "<em>There are currently no announcements requiring attention</em>",
});
cell.Style.Add("padding-left", "5px");
table.Rows.Add(row);
return;
}
}
foreach (Announcement a in announcements)
{
HtmlTableRow row = new HtmlTableRow();
if (a.EndDate < DateTime.Today || a.Status == AnnouncementStatus.Deleted)
{
row.Style.Add("background", "#f3f3f3");
}
else if (mode == "Approval" && a.Status == Announcement.AnnouncementStatus.Pending)
{
row.Style.Add("background", "#ffa4a4");
}
else if (mode == "Submission" && a.Status == Announcement.AnnouncementStatus.Denied)
{
row.Style.Add("background", "#ffa4a4");
}
HtmlTableCell checkCell;
CheckBox chk;
if (checkBoxes != null)
{
row.Cells.Add(checkCell = new HtmlTableCell());
checkCell.Style.Add("text-align", "center");
checkCell.Controls.Add(chk = new CheckBox()
{
ID = "chk_announcement_" + a.Id
});
if (checkBoxes.ContainsKey(a.Id))
checkBoxes[a.Id] = chk;
else
checkBoxes.Add(a.Id, chk);
}
row.Cells.Add(new HtmlTableCell() { InnerHtml = a.Title });
row.Cells.Add(new HtmlTableCell() { InnerHtml = a.CreatorDisplayName });
row.Cells.Add(new HtmlTableCell() { InnerHtml = a.StartDate.ToShortDateString() + " - " + a.EndDate.ToShortDateString() });
row.Cells.Add(new HtmlTableCell() { InnerHtml = a.Scope.Name });
if (a.EndDate < DateTime.Today)
{
row.Cells.Add(new HtmlTableCell() { InnerHtml = "Expired" });
}
else
{
switch (a.Status)
{
case Announcement.AnnouncementStatus.Pending:
row.Cells.Add(new HtmlTableCell() { InnerHtml = "Pending" });
break;
case Announcement.AnnouncementStatus.Approved:
row.Cells.Add(new HtmlTableCell() { InnerHtml = "Approved" });
break;
case Announcement.AnnouncementStatus.Denied:
row.Cells.Add(new HtmlTableCell() { InnerHtml = "Denied" });
break;
case Announcement.AnnouncementStatus.Deleted:
row.Cells.Add(new HtmlTableCell() { InnerHtml = "Deleted" });
break;
default:
row.Cells.Add(new HtmlTableCell() { InnerHtml = "(Unknown)" });
break;
}
}
row.Cells.Add(new HtmlTableCell() { InnerHtml = "<a href=\"AnnouncementEdit.aspx?id=" + a.Id + "\" class=\"linkbutton-small\" style=\"padding-left: 8px; padding-right: 8px;\">Edit</a>" });
table.Rows.Add(row);
}
}
finally
{
cmd.Dispose();
}
}
public static SecurityInfo FromDatabase(DatabaseManager manager, string principalName)
{
if (principalName.Contains('\\'))
return FromDatabase(manager, principalName.Split('\\')[0], principalName.Split('\\')[1]);
else
return null;
}
public SecurityInfo(DatabaseManager manager, string domain, string name, bool isUser)
{
_manager = manager;
Domain = domain;
Name = name;
IsUser = isUser;
Permissions = new Dictionary<string, bool>();
}
public static SecurityInfo FromDatabase(DatabaseManager manager, int id)
{
using (SqlCommand cmd = manager.CreateCommand("SELECT * FROM SecurityPrincipals WHERE Id=@id"))
{
cmd.Parameters.AddWithValue("@id", id);
using (SqlDataReader r = cmd.ExecuteReader())
{
if (r.Read())
return new SecurityInfo(manager, r);
else
return null;
}
}
}
public static void PopulatePageNumber(DatabaseManager manager, UserProfile settings, Label currentPageLabel, Label maxPageLabel, string mode, bool viewExpired, int currentPage, int numPerPage)
{
SqlCommand cmd;
if (mode == "ViewAll" || mode == "Approval")
{
cmd = manager.CreateCommand("SELECT COUNT(*) FROM Announcements WHERE Status<>3");
}
else
{
cmd = manager.CreateCommand("SELECT COUNT(*) FROM Announcements WHERE Status<>3 AND CreateUser=@createUser");
}
try
{
if (!viewExpired)
cmd.CommandText += " AND EndDate>=@today";
cmd.Parameters.AddWithValue("@createUser", settings.Id);
cmd.Parameters.AddWithValue("@today", DateTime.Today);
maxPageLabel.Text = Math.Max(1, Math.Ceiling((((int)cmd.ExecuteScalar()) / (double)numPerPage))).ToString();
currentPageLabel.Text = currentPage.ToString();
}
finally
{
cmd.Dispose();
}
}
public Announcement(DatabaseManager manager, int id)
{
_manager = manager;
_id = id;
}
public Announcement(DatabaseManager manager)
{
_manager = manager;
}
public Club(DatabaseManager manager, int id)
{
_manager = manager;
_id = id;
}
public void Update(DatabaseManager manager)
{
using (SqlCommand cmd = _manager.CreateCommand("UPDATE SecurityPrincipals SET Domain=@domain, PrincipalName=@principalName, IsUser=@isUser" + UpdatePopulate1() + " WHERE Id=@id"))
{
cmd.Parameters.AddWithValue("@id", Id);
cmd.Parameters.AddWithValue("@domain", Domain);
cmd.Parameters.AddWithValue("@principalName", Name);
cmd.Parameters.AddWithValue("@isUser", IsUser);
foreach (string permission in registeredPermissions)
cmd.Parameters.AddWithValue("@" + permission, this[permission]);
cmd.ExecuteNonQuery();
}
}
public static SecurityInfo FromDatabase(DatabaseManager manager, string domain, string principal)
{
using (SqlCommand cmd = manager.CreateCommand("SELECT * FROM SecurityPrincipals WHERE Domain=@domain AND PrincipalName=@principalName"))
{
cmd.Parameters.AddWithValue("@domain", domain);
cmd.Parameters.AddWithValue("@principalName", principal);
using (SqlDataReader r = cmd.ExecuteReader())
{
if (r.Read())
return new SecurityInfo(manager, r);
else
return null;
}
}
}
public static UserProfile FromDatabase(DatabaseManager manager, string name)
{
return FromDatabase(manager, name, true);
}
public SecurityInfo(DatabaseManager manager, SqlDataReader r)
{
_manager = manager;
Id = (int)r["Id"];
Domain = (string)r["Domain"];
Name = (string)r["PrincipalName"];
IsUser = (bool)r["IsUser"];
Permissions = new Dictionary<string, bool>();
foreach (string permission in registeredPermissions)
{
Permissions.Add(permission, (bool)r[permission]);
}
}
public User(DatabaseManager manager, IPrincipal userPrincipal)
{
Profile = UserProfile.FromDatabase(manager, userPrincipal.Identity.Name);
SecurityAccess = CompiledSecurityInfo.CompileAccessLevel(manager, userPrincipal);
}