public static List<AttendedEvent> GetAll(string staffUsername, DateTime? fromDate, DateTime? toDate) { List<AttendedEvent> output = new List<AttendedEvent>(); using (SqlConnection conn = UtilityDb.GetConnectionESS()) { string sql = "SELECT * FROM REQ_AttendedEventsHistory WHERE UPPER(StaffUsername)='" + staffUsername.ToUpper() + "'"; SqlDataReader dr = UtilityDb.GetDataReader(sql, conn); while (dr.Read()) { AttendedEvent ae = new AttendedEvent(); ae.LoadFromReader(dr); ae.LoadDataObjects(); if (ae.FitsDates(fromDate, toDate)) output.Add(ae); } } // order by start date output.Sort(CompareByStartDate); return output; }
private static int CompareByStartDate(AttendedEvent x, AttendedEvent y) { if (x.AttendedEventDate == null) x.LoadDataObjects(); if (y.AttendedEventDate == null) y.LoadDataObjects(); return x.AttendedEventDate.StartDate.CompareTo(y.AttendedEventDate.StartDate); }
//24/6/2012 added to use in Staff Listing page public static AttendedEvent GetByEventDateIDAndUsername(int EventDateID, string StaffUsername) { AttendedEvent output = new AttendedEvent(); using (SqlConnection conn = UtilityDb.GetConnectionESS()) { string sql = string.Format("SELECT * FROM REQ_AttendedEvents WHERE UPPER(StaffUsername)='{0}' AND EventDateID={1}", StaffUsername.ToUpper(), EventDateID); SqlDataReader dr = UtilityDb.GetDataReader(sql, conn); while (dr.Read()) { output.LoadFromReader(dr); output.LoadDataObjects(); } } return output; }
/// <summary> /// Get all events whose feedbacks have not been submitted /// </summary> /// <param name="p"></param> /// <param name="username"></param> /// <returns></returns> public static List<AttendedEvent> GetUnsubmitted(string moduleName, string staffUsername) { List<AttendedEvent> output = new List<AttendedEvent>(); using (SqlConnection conn = UtilityDb.GetConnectionESS()) { string sql = "SELECT * FROM REQ_AttendedEvents AS A WHERE UPPER(A.StaffUsername)='" + staffUsername.ToUpper() + "' AND NOT EXISTS " + "(SELECT * FROM ASM_Submitted" + moduleName + " AS S WHERE S.StaffUserName=A.StaffUserName AND S.EventDateID=A.EventDateID)"; SqlDataReader dr = UtilityDb.GetDataReader(sql, conn); while (dr.Read()) { AttendedEvent ae = new AttendedEvent(); ae.LoadFromReader(dr); ae.LoadDataObjects(); output.Add(ae); } } return output; }
public static List<AttendedEvent> GetAllByUsername(string staffUsername) { List<AttendedEvent> output = new List<AttendedEvent>(); using (SqlConnection conn = UtilityDb.GetConnectionESS()) { string sql = "SELECT * FROM REQ_AttendedEvents WHERE UPPER(StaffUsername)='" + staffUsername.ToUpper() + "'"; SqlDataReader dr = UtilityDb.GetDataReader(sql, conn); while (dr.Read()) { AttendedEvent ae = new AttendedEvent(); ae.LoadFromReader(dr); ae.LoadDataObjects(); output.Add(ae); } } return output; }