internal void LoadAndShowStaff(EventDate eventDate)
{
if (eventDate == null)
{
gvStaff.DataSource = new List<Staff>();
gvStaff.DataBind();
return;
}
CurrentEventDate = eventDate;
int stage = 2;
List<Staff> lstStaff = BookedEvent.GetAllStaff(eventDate, stage);
List<Staff> BookedStaff = new List<Staff>();
if (lstStaff != null)
{
// filter the staff to only the admin's subsidiaries
//List<string> subsidiaries = Staff.GetAdminSubsidiaries(Page.User.Identity.Name);
foreach (Staff staff in lstStaff)
{
//bool isInSubsidiary = false;
//foreach (string subsidiary in subsidiaries)
//{
// if (subsidiary == staff.Subsidiary)
// {
// isInSubsidiary = true;
// break;
// }
//}
//if (isInSubsidiary)
BookedStaff.Add(staff);
}
}
gvStaff.DataSource = BookedStaff;
gvStaff.DataBind();
// show questions for ratings
pnlButtons.Visible = true;
}
internal static EventDate GetById(int eventDateId)
{
string sql = "SELECT * FROM REQ_EventDates WHERE ID = " + eventDateId;
using (SqlConnection conn = UtilityDb.GetConnectionESS())
{
SqlDataReader dr = UtilityDb.GetDataReader(sql, conn);
while (dr.Read())
{
EventDate obj = new EventDate();
obj.LoadFromReader(dr);
return obj;
}
return null;
}
}
internal static List<EventDate> GetAll(int eventId)
{
List<EventDate> output = new List<EventDate>();
using (SqlConnection conn = UtilityDb.GetConnectionESS())
{
string sql = "SELECT * FROM REQ_EventDates WHERE EventID=" + eventId.ToString();
SqlDataReader dr = UtilityDb.GetDataReader(sql, conn);
while (dr.Read())
{
EventDate ed = new EventDate();
ed.LoadFromReader(dr);
output.Add(ed);
}
}
return output;
}
public void Copy(EventDate source)
{
EventId = source.EventId;
StartDate = source.StartDate;
EndDate = source.EndDate;
base.Copy(source);
}
public static EventDate GetFromId(int id)
{
using (SqlConnection conn = UtilityDb.GetConnectionESS())
{
string sql = "SELECT * FROM REQ_EventDates WHERE ID=" + id.ToString();
SqlDataReader dr = UtilityDb.GetDataReader(sql, conn);
while (dr.Read())
{
EventDate ed = new EventDate();
ed.LoadFromReader(dr);
return ed;
}
}
return null;
}
public void AttachEventDates(DateTime? fromDate, DateTime? toDate, int? eventDateId, bool bookedEventsOnly, bool unbookedEventsOnly, string bookingStaffUsername)
{
using (SqlConnection conn = UtilityDb.GetConnectionESS())
{
string sql = "SELECT * FROM REQ_EventDates AS ED WHERE ED.EventId=" + Id;
string subsql = "SELECT * FROM REQ_BookedEvents AS BE WHERE BE.EventDateID=ED.ID AND BE.StaffUsername='******'";
if (bookedEventsOnly)
sql += " AND EXISTS(" + subsql + ")";
if (unbookedEventsOnly)
sql += " AND NOT EXISTS(" + subsql + ")";
SqlDataReader dr = UtilityDb.GetDataReader(sql, conn);
while (dr.Read())
{
EventDate date = new EventDate();
date.LoadFromReader(dr);
bool attach = false;
{
}
// if dates are unspecified automatically include all dates
if (!fromDate.HasValue && !toDate.HasValue && !eventDateId.HasValue)
attach = true;
// select by date ID
else if (eventDateId.HasValue && date.Id == eventDateId)
attach = true;
// dates are specified, so do the filtration
else if (date.StartDate.CompareTo(fromDate) >= 0 &&
date.StartDate.CompareTo(toDate) <= 0)
attach = true;
if (attach)
{
// attach extra info from the event
date.SetEventInfo(DisplayName);
EventDates.Add(date);
}
}
}
}
internal static string getRequestedUsername(EventDate toSubmit)
{
string Username = "";
using (SqlConnection conn = UtilityDb.GetConnectionESS())
{
string sql = string.Format("SELECT RequesterUsername FROM REQ_BookedEvents WHERE EventDateID={0}", toSubmit.Id);
SqlDataReader dr = UtilityDb.GetDataReader(sql, conn);
while (dr.Read())
{
Username = dr["RequesterUsername"].ToString();
}
}
return Username;
}
internal static List<Staff> GetAllStaff(EventDate eventDate, int stage)
{
List<Staff> output = new List<Staff>();
using (SqlConnection conn = UtilityDb.GetConnectionESS())
{
string sql = "SELECT StaffUsername FROM REQ_BookedEvents WHERE EventDateId=" + eventDate.Id.ToString() + " AND Stage=" + stage;
SqlDataReader dr = UtilityDb.GetDataReader(sql, conn);
while (dr.Read())
{
try
{
Staff staff = Staff.GetFromUsername(dr["StaffUsername"].ToString());
output.Add(staff);
}
catch (Exception ex)
{
string Error = ex.Message;
}
}
}
return output;
}
public static List<EventDate> GetSubmissionList(int currentStage, string staffUsername)
{
List<EventDate> output = new List<EventDate>();
using (SqlConnection conn = UtilityDb.GetConnectionESS())
{
string whereClause = string.Format(" Stage={0} AND UPPER(StaffUsername)='{1}' AND P.EventDateId=E.ID",
currentStage, staffUsername.ToUpper());
// obtain a list of events to be approved, so we can return it
string sql = "SELECT * FROM REQ_EventDates AS E WHERE Exists (SELECT * FROM REQ_BookedEvents as P WHERE " +
whereClause + ")";
SqlDataReader dr = UtilityDb.GetDataReader(sql, conn);
while (dr.Read())
{
EventDate pb = new EventDate();
pb.LoadFromReader(dr);
output.Add(pb);
}
}
return output;
}
public static List<EventDate> GetAllEventDates(int stage)
{
List<EventDate> output = new List<EventDate>();
using (SqlConnection conn = UtilityDb.GetConnectionESS())
{
string sql = "SELECT * FROM REQ_EventDates AS ED WHERE EXISTS (SELECT * FROM REQ_BookedEvents AS BE WHERE BE.EventDateID=ED.ID AND Stage=" + stage + ")";
SqlDataReader dr = UtilityDb.GetDataReader(sql, conn);
while (dr.Read())
{
EventDate ed = new EventDate();
ed.LoadFromReader(dr);
output.Add(ed);
}
}
return output;
}