public static List<TrainingEvent> getAllTrainingTitle() { List<TrainingEvent> output = new List<TrainingEvent>(); using (SqlConnection conn = UtilityDb.GetConnectionESS()) { string sql = string.Format("SELECT DISTINCT Title,ID FROM REQ_Events"); SqlDataReader dr = UtilityDb.GetDataReader(sql, conn); while (dr.Read()) { TrainingEvent te = new TrainingEvent(); te.Title = dr["Title"].ToString(); te.ID = Convert.ToInt32(dr["ID"].ToString()); if (te != null) output.Add(te); } } return output; }
internal static string GetRequestedUsername(TrainingEvent toSubmit) { string Username = ""; string sql = string.Format("SELECT RequesterUsername FROM REQ_PrebookedEvents Where EventID={0}", toSubmit.Id); using (SqlConnection conn = UtilityDb.GetConnectionESS()) { SqlDataReader dr = UtilityDb.GetDataReader(sql, conn); while (dr.Read()) { Username = dr["RequesterUsername"].ToString(); } } return Username; }
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; }
private void LoadDataObjects() { TrainingEvent te = new TrainingEvent(); te = TrainingEvent.GetById(EventID); te.EventDates = EventDate.GetAll(EventID); PrebookedEvent = te; PrebookedStaff = Staff.GetFromUsername(StaffUsername); }
/// <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 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; }