public static List <TimeTableEntry> loadStudentTimeTable(SqlConnection connection, Student student, Term term)
{
List <TimeTableEntry> returnMe = new List <TimeTableEntry>();
// get student enrolled classes for this term
List <SchoolClass> studentEnrolledCoursesThisTerm = SchoolClass.loadStudentEnrolledClassesForThisTerm(connection, student, term);
// Get schedule for this school, term and class
foreach (SchoolClass thisclass in studentEnrolledCoursesThisTerm)
{
returnMe.AddRange(loadTimeTableEntries(connection, term.ID, thisclass));
}
return(returnMe);
}
public int CompareTo(object obj)
{
if (obj == null)
{
return(1);
}
SchoolClass obj2 = obj as SchoolClass;
if (obj2 != null)
{
return(this.name.CompareTo(obj2.name));
}
else
{
throw new ArgumentException("Object is not a SchoolClass");
}
}
public static List <TimeTableEntry> loadTimeTableEntries(SqlConnection connection, int termID, SchoolClass schoolClass)
{
List <TimeTableEntry> returnMe = new List <TimeTableEntry>();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = connection;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = "SELECT * FROM LSKY_ClassSchedule WHERE iTermID=@TERMID AND iClassID=@CLASSID ORDER BY iBlockNumber ASC, cName ASC;";
sqlCommand.Parameters.AddWithValue("@TERMID", termID);
sqlCommand.Parameters.AddWithValue("@CLASSID", schoolClass.classid);
sqlCommand.Connection.Open();
SqlDataReader dataReader = sqlCommand.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
TimeTableEntry newTimeTableEntry = new TimeTableEntry(
int.Parse(dataReader["iDayNumber"].ToString().Trim()),
int.Parse(dataReader["iBlockNumber"].ToString().Trim()),
int.Parse(dataReader["iSchoolID"].ToString().Trim()),
int.Parse(dataReader["itermID"].ToString().Trim()),
dataReader["Room"].ToString().Trim(),
schoolClass
);
returnMe.Add(newTimeTableEntry);
}
}
sqlCommand.Connection.Close();
return(returnMe);
}
public TimeTableEntry(int dayNumber, int blockNumber, int schoolID, int TermID, string room, SchoolClass schoolClass)
{
this.dayNum = dayNumber;
this.blockNum = blockNumber;
this.schoolClass = schoolClass;
this.termID = termID;
this.schoolID = schoolID;
this.roomName = room;
}
public static List <TimeTableEntry> loadTimeTableEntries(SqlConnection connection, Term term, SchoolClass sclass)
{
return(loadTimeTableEntries(connection, term.ID, sclass));
}
public static List <Outcome> loadObjectivesForThisCourseByCategoryName(SqlConnection connection, SchoolClass course, string categoryName)
{
List <Outcome> returnMe = new List <Outcome>();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = connection;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = "SELECT * FROM LSKY_CourseObjectives WHERE iCourseID=@COURSEID AND ObjectiveCategory=@CATNAME";
sqlCommand.Parameters.AddWithValue("@COURSEID", course.courseid);
sqlCommand.Parameters.AddWithValue("@CATNAME", categoryName);
sqlCommand.Connection.Open();
SqlDataReader dataReader = sqlCommand.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
returnMe.Add(new Outcome(
int.Parse(dataReader["iCourseObjectiveID"].ToString().Trim()),
int.Parse(dataReader["iCourseID"].ToString().Trim()),
dataReader["cSubject"].ToString().Trim(),
dataReader["mNotes"].ToString().Trim(),
dataReader["ObjectiveCategory"].ToString().Trim(),
dataReader["CourseName"].ToString().Trim(),
dataReader["cCourseCode"].ToString().Trim()
));
}
}
sqlCommand.Connection.Close();
return(returnMe);
}
/// <summary>
/// Loads mark data for a student for report cards. Loads terms, report periods, enrolled classes, class marks, outcomes, outcome marks, and attendance
/// </summary>
/// <param name="connection">Database connection</param>
/// <param name="thisStudent">The student to load data for</param>
/// <param name="theseReportPeriods">Report periods to load data for</param>
/// <returns>Student object loaded with terms, report periods, enrolled classes, class marks, outcomes, outcome marks, and attendance</returns>
public static Student loadStudentMarkData(SqlConnection connection, Student thisStudent, List <ReportPeriod> theseReportPeriods)
{
Student returnedStudent = thisStudent;
// **************************************************************
// * Report Period Comments
// **************************************************************
// Load report period comments for the selected report periods
List <ReportPeriodComment> allRPComments = ReportPeriodComment.loadRPCommentsForStudent(connection, thisStudent.getStudentID());
List <ReportPeriodComment> filteredRPComments = ReportPeriodComment.getCommentsForTheseReportPeriods(allRPComments, theseReportPeriods);
returnedStudent.ReportPeriodComments = filteredRPComments;
// **************************************************************
// * Report Periods
// **************************************************************
// Find the earliest report period and the last report period, for attendance dates
DateTime earliestDate = DateTime.MaxValue;
DateTime lastDate = DateTime.MinValue;
List <int> detectedTermIDs = new List <int>();
List <int> selectedReportPeriodIDs = new List <int>();
List <ReportPeriod> reportPeriods = new List <ReportPeriod>();
// Validate report periods so we aren't working with nulls
foreach (ReportPeriod rp in theseReportPeriods)
{
if (rp != null)
{
reportPeriods.Add(rp);
}
}
foreach (ReportPeriod rp in reportPeriods)
{
// Create a list of loaded report period IDs for later use
if (!selectedReportPeriodIDs.Contains(rp.ID))
{
selectedReportPeriodIDs.Add(rp.ID);
}
// Find the earliest report period and the last report period, for attendance dates
if (rp.startDate < earliestDate)
{
earliestDate = rp.startDate;
}
if (rp.endDate > lastDate)
{
lastDate = rp.endDate;
}
// Derive some terms from the given report periods while we are cycling through them
if (!detectedTermIDs.Contains(rp.termID))
{
detectedTermIDs.Add(rp.termID);
}
}
// **************************************************************
// * Terms
// **************************************************************
// Derive some terms from the given report periods while we are cycling through them
List <Term> detectedTerms = new List <Term>();
foreach (int termid in detectedTermIDs)
{
detectedTerms.Add(Term.loadThisTerm(connection, termid));
}
foreach (Term term in detectedTerms)
{
foreach (ReportPeriod rp in reportPeriods)
{
// Put report periods into their respective terms
if (rp.termID == term.ID)
{
term.ReportPeriods.Add(rp);
}
// Determine the final report period in a term, and assign it appropriately if it was loaded
if (
(rp.endDate.Year == term.endDate.Year) &&
(rp.endDate.Month == term.endDate.Month) &&
(rp.endDate.Day == term.endDate.Day)
)
{
term.FinalReportPeriod = rp;
}
}
}
if (returnedStudent != null)
{
// **************************************************************
// * Load student data
// **************************************************************
// Load all outcome marks (which will also load the outcomes themselves). We will filter this list by class later
List <OutcomeMark> studentOutcomeMarks_All = OutcomeMark.loadOutcomeMarksForThisStudent(connection, reportPeriods, returnedStudent);
returnedStudent.school = School.loadThisSchool(connection, thisStudent.getSchoolIDAsInt());
returnedStudent.track = Track.loadThisTrack(connection, returnedStudent.getTrackID());
returnedStudent.track.terms = detectedTerms;
// Marks
List <Mark> studentMarks_All = Mark.loadMarksFromTheseReportPeriods(connection, reportPeriods, returnedStudent);
// **************************************************************
// * Attendance
// **************************************************************
// Load this student's attendance
returnedStudent.absences = Absence.loadAbsencesForThisStudentAndTimePeriod(connection, thisStudent, earliestDate, lastDate);
foreach (Term thisTerm in returnedStudent.track.terms)
{
// **************************************************************
// * Enrolled Classes
// **************************************************************
// Load classes this student is enrolled in
thisTerm.Courses = SchoolClass.loadStudentEnrolledClassesForThisTerm(connection, returnedStudent, thisTerm);
foreach (SchoolClass thisClass in thisTerm.Courses)
{
thisClass.term = thisTerm;
// Put list of report periods into each class so we can easily reference it later
thisClass.ReportPeriods = reportPeriods;
// **************************************************************
// * Outcomes and Life Skills (or SLBs / Successful Learner Behaviors)
// **************************************************************
string lifeSkillsCategoryName = "Successful Learner Behaviours";
// From the main list of this student's outcome marks, select just the ones for this course
//List<OutcomeMark> classOutcomeMarks_All = OutcomeMark.loadOutcomeMarksForThisCourse(connection, thisTerm, returnedStudent, thisClass);
List <OutcomeMark> classOutcomeMarks_All = new List <OutcomeMark>();
foreach (OutcomeMark om in studentOutcomeMarks_All)
{
if (om.courseID == thisClass.courseid)
{
classOutcomeMarks_All.Add(om);
}
}
// Split marks into life skills and not life skills
List <Outcome> classNormalOutcomes = new List <Outcome>();
List <Outcome> classLifeSkills = new List <Outcome>();
List <OutcomeMark> classMarks_LifeSkills = new List <OutcomeMark>();
List <OutcomeMark> classMarks_Outcomes = new List <OutcomeMark>();
foreach (OutcomeMark om in classOutcomeMarks_All)
{
// Associate some of the loaded report period objects with the outcome marks
foreach (ReportPeriod rp in reportPeriods)
{
if (rp.ID == om.reportPeriodID)
{
om.reportPeriod = rp;
}
}
// Split marks into life skills and not life skills lists
if (om.outcome.category == lifeSkillsCategoryName)
{
classMarks_LifeSkills.Add(om);
classLifeSkills.Add(om.outcome);
}
else
{
classMarks_Outcomes.Add(om);
// While we are iterating through the list, derive a list of outcomes from the outcome marks, since we didn't load one earlier
// Check to see if this outcome exists already
bool outcomeExists = false;
foreach (Outcome o in classNormalOutcomes)
{
if (o.id == om.outcome.id)
{
outcomeExists = true;
o.marks.Add(om);
}
}
if (!outcomeExists)
{
classNormalOutcomes.Add(om.outcome);
}
}
}
// Put the outcomes and outcome marks into the course object
thisClass.Outcomes = classNormalOutcomes;
thisClass.LifeSkills = classLifeSkills;
thisClass.OutcomeMarks = classMarks_Outcomes;
thisClass.LifeSkillMarks = classMarks_LifeSkills;
}
// **************************************************************
// * Class marks and comments
// **************************************************************
foreach (Mark m in studentMarks_All)
{
foreach (SchoolClass c in thisTerm.Courses)
{
if (m.classID == c.classid)
{
c.Marks.Add(m);
}
}
}
}
}
return(returnedStudent);
}
public static List <OutcomeMark> loadOutcomeMarksForThisCourse(SqlConnection connection, Term term, Student student, SchoolClass course)
{
List <OutcomeMark> returnMe = new List <OutcomeMark>();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = connection;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = "SELECT * FROM LSKY_ObjectiveMarks WHERE cStudentNumber=@StudentNum AND iTermID=@TermID AND (ObjectiveCourseID=@CourseID OR MarkCourseID=@CourseID)";
sqlCommand.Parameters.AddWithValue("@TermID", term.ID);
sqlCommand.Parameters.AddWithValue("@StudentNum", student.getStudentID());
sqlCommand.Parameters.AddWithValue("@CourseID", course.courseid);
sqlCommand.Connection.Open();
SqlDataReader dataReader = sqlCommand.ExecuteReader();
if (dataReader.HasRows)
{
while (dataReader.Read())
{
float nMark = -1;
float.TryParse(dataReader["nMark"].ToString().Trim(), out nMark);
string cMark = dataReader["cMark"].ToString().Trim();
// Only load the outcome mark if it is nonzero/nonnull, since SchoolLogic neve deletes "blanked out" marks
if ((nMark > 0) || (!string.IsNullOrEmpty(cMark)))
{
returnMe.Add(new OutcomeMark(
int.Parse(dataReader["iStudentCourseObjectiveID"].ToString().Trim()),
int.Parse(dataReader["cStudentNumber"].ToString().Trim()),
int.Parse(dataReader["iCourseObjectiveID"].ToString().Trim()),
int.Parse(dataReader["iReportPeriodID"].ToString().Trim()),
int.Parse(dataReader["MarkCourseID"].ToString().Trim()),
cMark,
(float)Math.Round(nMark, 1),
new Outcome(
int.Parse(dataReader["iCourseObjectiveID"].ToString().Trim()),
int.Parse(dataReader["ObjectiveCourseID"].ToString().Trim()),
dataReader["cSubject"].ToString().Trim(),
dataReader["mNotes"].ToString().Trim(),
dataReader["OutcomeCategory"].ToString().Trim(),
dataReader["CourseName"].ToString().Trim(),
dataReader["cCourseCode"].ToString().Trim()
)
));
}
}
}
dataReader.Close();
sqlCommand.Connection.Close();
return(returnMe);
}
public static List <Teacher> loadTeachersForThisClass(SqlConnection connection, SchoolClass thisclass)
{
return(loadTeachersForThisClass(connection, thisclass.classid));
}
