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;
}