/// <summary>
/// Get all available events in a eventObj group.
/// </summary>
/// <param name="eventGroup"></param>
/// <returns></returns>
public static List<TrainingEvent> GetAvailableEvents(EventGroup eventGroup,
string staffUsername, string searchTitle, bool checkAgainstPrebookingTable)
{
List<int> eventGroupIds = new List<int>();
CollectTreeIds(eventGroupIds, eventGroup);
string sql = "SELECT * FROM REQ_Events AS C WHERE EXISTS " +
"(SELECT * FROM REQ_EventsInGroups AS CG WHERE CG.EventId = C.ID ";
// add in clause for the event groups
if (eventGroupIds.Count > 0)
{
string inClause = "";
int i = 0;
foreach (int groupId in eventGroupIds)
{
if (i > 0)
inClause += ",";
inClause += groupId.ToString();
i++;
}
inClause = "(" + inClause + ")";
sql += " AND CG.GroupId IN " + inClause;
}
sql += ") ";
// if search term is specified
if (!String.IsNullOrEmpty(searchTitle))
{
sql += " AND C.Title LIKE '%" + searchTitle + "%' ";
}
// filter out events that have been pre-booked by this staff
if (checkAgainstPrebookingTable)
{
string bookingTable = "REQ_PrebookedEvents";
if (!string.IsNullOrEmpty(staffUsername))
{
sql += string.Format(" AND NOT EXISTS(SELECT * FROM {0} AS PB WHERE PB.StaffUsername='******' AND PB.EventID=C.ID) ",
bookingTable, staffUsername);
}
sql += " ORDER BY C.Title";
}
// fetch output from database from the sql generated above.
List<TrainingEvent> output = new List<TrainingEvent>();
using (SqlConnection conn = UtilityDb.GetConnectionESS())
{
SqlDataReader dr = UtilityDb.GetDataReader(sql, conn);
while (dr.Read())
{
TrainingEvent eventObj = new TrainingEvent();
eventObj.LoadFromReader(dr);
output.Add(eventObj);
}
}
return output;
}
public static List<TrainingEvent> GetSubmissionList(int currentStage, string staffUsername, bool isAdHoc)
{
List<TrainingEvent> output = new List<TrainingEvent>();
using (SqlConnection conn = UtilityDb.GetConnectionESS())
{
string whereClause = string.Format(" Stage={0} AND StaffUsername='******' AND IsAdHoc='{2}' AND P.EventId=E.ID",
currentStage, staffUsername, isAdHoc);
// obtain a list of events to be approved, so we can return it
string sql = "SELECT * FROM REQ_Events AS E WHERE Exists (SELECT * FROM REQ_PrebookedEvents as P WHERE " +
whereClause + ") ORDER BY E.Title";
SqlDataReader dr = UtilityDb.GetDataReader(sql, conn);
while (dr.Read())
{
TrainingEvent pb = new TrainingEvent();
pb.LoadFromReader(dr);
output.Add(pb);
}
}
return output;
}
public static TrainingEvent GetById(int EventId)
{
using (SqlConnection conn = UtilityDb.GetConnectionESS())
{
string sql = string.Format("SELECT * FROM REQ_Events WHERE ID={0}", EventId);
SqlDataReader dr = UtilityDb.GetDataReader(sql, conn);
while (dr.Read())
{
TrainingEvent output = new TrainingEvent();
output.LoadFromReader(dr);
return output;
}
}
return null;
}