public static List <StudentStatus> loadStatusesWithNoOutstatusForThisStudent(SqlConnection connection, Student student) { List <StudentStatus> returnMe = new List <StudentStatus>(); SqlCommand sqlCommand = new SqlCommand(); sqlCommand.Connection = connection; sqlCommand.CommandType = CommandType.Text; StringBuilder SQL = new StringBuilder(); /* Load all attendance blocks, so we can reference them */ List <AttendanceBlock> blocks = AttendanceBlock.loadAllAttendanceBlocks(connection); SQL.Append("SELECT * FROM LSKY_StudentStatuses WHERE cStudentNumber = '" + student.getStudentID() + "' AND OutStatus is null;"); sqlCommand.CommandText = SQL.ToString(); sqlCommand.Connection.Open(); SqlDataReader dataReader = sqlCommand.ExecuteReader(); if (dataReader.HasRows) { while (dataReader.Read()) { // Parse the instatus bool hasInDate = false; DateTime inDate; if (DateTime.TryParse(dataReader["dInDate"].ToString(), out inDate)) { hasInDate = true; } // Parse the outstatus bool hasOutDate = false; DateTime OutDate; if (DateTime.TryParse(dataReader["dOutDate"].ToString(), out OutDate)) { hasOutDate = true; } StudentStatus newStatus = new StudentStatus( int.Parse(dataReader["iStudentStatusID"].ToString()), dataReader["cStudentNumber"].ToString(), dataReader["SchoolName"].ToString(), int.Parse(dataReader["SchoolNumber"].ToString()), hasInDate, inDate, dataReader["InStatus"].ToString(), hasOutDate, OutDate, dataReader["OutStatus"].ToString() ); returnMe.Add(newStatus); } } sqlCommand.Connection.Close(); return(returnMe); }
public static List <Absence> loadAbsencesForThisDate(SqlConnection connection, DateTime date) { List <Absence> returnMe = new List <Absence>(); SqlCommand sqlCommand = new SqlCommand(); sqlCommand.Connection = connection; sqlCommand.CommandType = CommandType.Text; StringBuilder SQL = new StringBuilder(); /* Load all attendance blocks, so we can reference them */ List <AttendanceBlock> blocks = AttendanceBlock.loadAllAttendanceBlocks(connection); SQL.Append("SELECT * FROM LSKY_Attendance WHERE dDate='" + date.Year + "-" + date.Month + "-" + date.Day + " 00:00:00' ORDER BY dDate ASC, block ASC;"); sqlCommand.CommandText = SQL.ToString(); sqlCommand.Connection.Open(); SqlDataReader dataReader = sqlCommand.ExecuteReader(); if (dataReader.HasRows) { while (dataReader.Read()) { Absence newAbsence = new Absence( DateTime.Parse(dataReader["dDate"].ToString()), int.Parse(dataReader["iTrackID"].ToString().Trim()), dataReader["StudentNumber"].ToString().Trim(), dataReader["ClassName"].ToString().Trim(), dataReader["ClassID"].ToString().Trim(), dataReader["Status"].ToString().Trim(), dataReader["Reason"].ToString().Trim(), dataReader["Comment"].ToString().Trim(), int.Parse(dataReader["Block"].ToString()), int.Parse(dataReader["Minutes"].ToString()), parseExcused(dataReader["lExcusable"].ToString()) ); newAbsence.period = newAbsence.getBlock().ToString(); foreach (AttendanceBlock atBlock in blocks) { if (atBlock.block == newAbsence.block) { if (atBlock.track == newAbsence.track) { newAbsence.period = atBlock.name; newAbsence.attendanceBlock = atBlock; } } } returnMe.Add(newAbsence); } } sqlCommand.Connection.Close(); foreach (Absence abs in returnMe) { } return(returnMe); }