internal List <SchoolPeriod> GetSchoolPeriods(string IdSchoolYear)
{
List <SchoolPeriod> l = new List <SchoolPeriod>();
using (DbConnection conn = Connect())
{
DbDataReader dRead;
DbCommand cmd = conn.CreateCommand();
string query = "SELECT * " +
"FROM SchoolPeriods";
if (IdSchoolYear != null)
{
query +=
" WHERE idSchoolYear=" + IdSchoolYear +
" OR IdSchoolYear IS null OR IdSchoolYear=''" +
";";
}
cmd.CommandText = query;
dRead = cmd.ExecuteReader();
while (dRead.Read())
{
SchoolPeriod p = GetOneSchoolPeriodFromRow(dRead);
l.Add(p);
}
}
return(l);
}
public void Initialize(SchoolPeriod schoolPeriod, IEnumerable<GradingTerm> gradingTerms, IEnumerable<GradingStandard> gradingStandards, byte[] templateData)
{
Template = templateData;
Period = schoolPeriod;
Terms = gradingTerms.ToList();
Standards = gradingStandards.ToList();
}
private void cmbSchoolPeriod_SelectedIndexChanged(object sender, EventArgs e)
{
currentSchoolPeriod = (SchoolPeriod)(cmbSchoolPeriod.SelectedValue);
if (currentSchoolPeriod.IdSchoolPeriodType != "N")
{
dtpStartPeriod.Value = (DateTime)currentSchoolPeriod.DateStart;
dtpEndPeriod.Value = (DateTime)currentSchoolPeriod.DateFinish;
}
else if (currentSchoolPeriod.IdSchoolPeriod == "month")
{
dtpStartPeriod.Value = DateTime.Now.AddMonths(-1);
dtpEndPeriod.Value = DateTime.Now;
}
else if (currentSchoolPeriod.IdSchoolPeriod == "week")
{
dtpStartPeriod.Value = DateTime.Now.AddDays(-7);
dtpEndPeriod.Value = DateTime.Now;
}
else if (currentSchoolPeriod.IdSchoolPeriod == "year")
{
dtpStartPeriod.Value = DateTime.Now.AddYears(-1);
dtpEndPeriod.Value = DateTime.Now;
}
RetrieveData(FindCheckedRadioButton());
}
private static MemoryStream ProcessReportCard(SchoolPeriod schoolPeriod, IEnumerable<GradingStandard> gradingStandards, ReportCardTemplate template, IEnumerable<ClassEnrollment> enrollments)
{
IReportCardParser parser = GetParser(template.FileType);
parser.Initialize(schoolPeriod, schoolPeriod.GradingTerms, gradingStandards, template.TemplateData);
return parser.Process(enrollments);
}
private static MemoryStream ProcessReportCard(SchoolPeriod schoolPeriod, IEnumerable<GradingStandard> gradingStandards, ReportCardTemplate template, ClassEnrollment enrollment)
{
var parser = GetParser(template.FileType);
parser.Initialize(schoolPeriod, schoolPeriod.GradingTerms, gradingStandards, template.TemplateData);
return parser.Process(enrollment, enrollment.Student.ClassEnrollments.SelectMany(p => p.StudentGrades));
}
public void Initialize(SchoolPeriod schoolPeriod, IEnumerable<GradingTerm> gradingTerms, IEnumerable<GradingStandard> gradingStandards, byte[] templateData)
{
this.Period = schoolPeriod;
this.Terms = gradingTerms;
this.Standards = gradingStandards;
this.TemplateStream = new MemoryStream(templateData);
}
internal void SaveSchoolPeriod(SchoolPeriod SchoolPeriod)
{
if (FindIfIdIsAlreadyExisting(SchoolPeriod.IdSchoolPeriod))
{
UpdateSchoolPeriod(SchoolPeriod);
}
else
{
CreateSchoolPeriod(SchoolPeriod);
}
}
public void Add_Click(object sender, RoutedEventArgs e)
{
if (!IsWrongValue())
{
var OwnerWindowInstance = (PeriodOfStudyWindow)this.Owner;
var period = new SchoolPeriod(NameTextBox.Text, DatePicker_1.SelectedDate.Value, DatePicker_2.SelectedDate.Value);
OwnerWindowInstance.StudyPeriodDict.dictionaryList.Add(period);
OwnerWindowInstance.PeriodsList.Items.Add(period);
ElementsModification.ResetControlText <TextBox>(this);
}
}
/// <summary>
/// Ajouter Nouvelle Periode Pour Une Annee Scolaire, Return True if Successful
/// </summary>
/// <param name="newSchoolPeriod"> Object PeriodeScolaire</param>
private static bool AddPeriodeScolaire(SchoolPeriod newSchoolPeriod)
{
if (newSchoolPeriod.SchoolPeriodGuid == Guid.Empty)
{
newSchoolPeriod.SchoolPeriodGuid = Guid.NewGuid();
}
using (var db = new SchoolContext())
{
db.SchoolPeriods.Add(newSchoolPeriod);
return(db.SaveChanges() > 0);
}
}
internal void CreateNewTrimesterPeriods(string SchoolYear)
{
{
if (dl.FindIfPeriodsAreAlreadyExisting(SchoolYear))
{
throw new Exception("Period already present");
return;
}
SchoolPeriod newSp = new SchoolPeriod();
int startingYear = Convert.ToInt32("20" + SchoolYear.Substring(0, 2));
// whole year period
newSp.IdSchoolPeriod = SchoolYear;
newSp.IdSchoolPeriodType = "Y";
newSp.IdSchoolYear = SchoolYear;
newSp.DateStart = new DateTime(startingYear, 9, 1);
newSp.DateFinish = new DateTime(startingYear + 1, 6, 15);
newSp.Name = "Anno " + SchoolYear;
newSp.Desc = "Anno scolastico " + startingYear + "-" + (startingYear + 1);
dl.SaveSchoolPeriod(newSp);
// first period
newSp.IdSchoolPeriod = SchoolYear + "1P";
newSp.IdSchoolPeriodType = "P";
newSp.IdSchoolYear = SchoolYear;
newSp.DateStart = new DateTime(startingYear, 9, 1);
newSp.DateFinish = new DateTime(startingYear, 11, 30);
newSp.Name = "1 Per." + startingYear.ToString().Substring(2) + "-" + (startingYear + 1).ToString().Substring(2);
newSp.Desc = "Primo periodo A.S. " + startingYear + "-" + (startingYear + 1);
dl.SaveSchoolPeriod(newSp);
// second period
newSp.IdSchoolPeriod = SchoolYear + "2P";
newSp.IdSchoolPeriodType = "P";
newSp.IdSchoolYear = SchoolYear;
newSp.DateStart = new DateTime(startingYear, 11, 30);
newSp.DateFinish = new DateTime(startingYear + 1, 3, 31);
newSp.Name = "2 Per." + startingYear.ToString().Substring(2) + "-" + (startingYear + 1).ToString().Substring(2);
newSp.Desc = "Secondo periodo A.S. " + startingYear + "-" + (startingYear + 1);
dl.SaveSchoolPeriod(newSp);
// third period
newSp.IdSchoolPeriod = SchoolYear + "3P";
newSp.IdSchoolPeriodType = "P";
newSp.IdSchoolYear = SchoolYear;
newSp.DateStart = new DateTime(startingYear + 1, 3, 31);
newSp.DateFinish = new DateTime(startingYear + 1, 6, 15);
newSp.Name = "3 Per." + startingYear.ToString().Substring(2) + "-" + (startingYear + 1).ToString().Substring(2);
newSp.Desc = "Terzo periodo A.S. " + startingYear + "-" + (startingYear + 1);
dl.SaveSchoolPeriod(newSp);
}
}
internal PeriodeTranscript(Guid studentGuid, SchoolPeriod schoolPeriod)
{
//var studentGrades = StudentsGradesManager.StaticGetStudentGrades(studentGuid, periodeScolaire.StartDate, periodeScolaire.EndDate);
//foreach(var grade in studentGrades)
// GradesListCards.Add(new GradeCard(grade));
//PeriodeName=periodeScolaire.Name.ToUpper();
//PeriodeTime=" ("+periodeScolaire.StartDate.GetValueOrDefault().ToShortDateString()+" -> "+
// periodeScolaire.EndDate.GetValueOrDefault().ToShortDateString()+")";
//Average = studentGrades.Sum(g => g.GradeCoeff).ToString("0.##" + CultureInfo.CurrentCulture) + "/20";
}
private void ContextMenuCopyButton_Click(object sender, RoutedEventArgs e)
{
if (PeriodsList.SelectedItems != null)
{
foreach (SchoolPeriod item in PeriodsList.SelectedItems)
{
var period = new SchoolPeriod(item.Name, item.Start, item.End);
StudyPeriodDict.dictionaryList.Add(period);
PeriodsList.Items.Add(period);
}
}
else
{
MessageBox.Show("Choose period(-s)");
}
}
internal SchoolPeriod GetOneSchoolPeriodFromRow(DbDataReader Row)
{
SchoolPeriod p = new SchoolPeriod();
p.IdSchoolPeriodType = SafeDb.SafeString(Row["idSchoolPeriodType"]);
if (p.IdSchoolPeriodType != "N")
{
p.DateFinish = SafeDb.SafeDateTime(Row["dateFinish"]);
p.DateStart = SafeDb.SafeDateTime(Row["dateStart"]);
}
p.Name = SafeDb.SafeString(Row["name"]);
p.Desc = SafeDb.SafeString(Row["desc"]);
p.IdSchoolPeriod = SafeDb.SafeString(Row["idSchoolPeriod"]);
p.IdSchoolYear = SafeDb.SafeString(Row["idSchoolYear"]);
return(p);
}
/// <summary>
/// Le Rapport d'Assudite Par Periode
/// </summary>
public PeriodeAttendance(Guid personGuid, SchoolPeriod schoolPeriod)
{
using (var db = new SchoolContext())
{
var personTickets = db.AbsenceTickets.Where(tk =>
tk.PersonGuid == personGuid &&
(
tk.CoursDate >= schoolPeriod.StartDate &&
tk.CoursDate <= schoolPeriod.EndDate
)
).OrderBy(tk => tk.CoursDate).ToList();
foreach (var personTicket in personTickets)
{
TicketsCardList.Add(new AssiduiteCard(personTicket));
}
PeriodeName = schoolPeriod.Name.ToUpper();
PeriodeTime = " (" + schoolPeriod.StartDate.GetValueOrDefault().ToShortDateString() + " -> " +
schoolPeriod.EndDate.GetValueOrDefault().ToShortDateString() + ")";
PersonFullName = db.Students.Find(personGuid)?.Person.FullName ?? db.Staffs.Find(personGuid)?.Person.FullName;
foreach (var ticket in personTickets)
{
if (!ticket.IsPresent)
{
TotalAbsences = TotalAbsences + 1;
}
else
{
TotalRetards = TotalRetards + 1;
TotalRatardTime = TotalRatardTime.Add(ticket.RetardTime);
}
}
TrimestreTotal = TotalAbsences > 0 ? "Absences " + TotalAbsences + " fois, " : "Aucune Absence, ";
TrimestreTotal = TotalRetards > 0
? TrimestreTotal + "Retards " + TotalRetards + " fois en "
+ TotalRatardTime.ProperTimeSpan() + " mins"
: TrimestreTotal + "Aucun Retard";
}
}
public frmGroups(List<Student> GroupsList, Class Class, SchoolSubject subject, GradeType grade)
{
InitializeComponent();
listGroups = GroupsList;
schoolClass = Class;
schoolSubject = subject;
schoolGrade = grade;
db = new DbAndBusiness(Commons.PathAndFileDatabase);
List<SchoolPeriod> listPeriods = db.GetSchoolPeriods(Class.SchoolYear);
foreach (SchoolPeriod sp in listPeriods)
{
if (sp.DateFinish > DateTime.Now && sp.DateStart < DateTime.Now
&& sp.IdSchoolPeriodType == "P")
{
schoolPeriod = sp;
}
}
}
private void ImportExcel_Click(object sender, RoutedEventArgs e)
{
string[,] data = ExcelFileTools.UploadExcelData();
// HACK: Change Length Checker
if (data?.GetLength(1) > ElementsModification.FindVisualChildren <GridViewColumnHeader>(this).Count() - 2)
{
for (int i = 0; i < data.GetLength(0); i++)
{
DateTime.TryParse(data[i, 1], out DateTime start);
DateTime.TryParse(data[i, 2], out DateTime end);
var period = new SchoolPeriod(data[i, 0], start, end);
StudyPeriodDict.dictionaryList.Add(period);
PeriodsList.Items.Add(period);
}
}
else if (data?.GetLength(1) < ElementsModification.FindVisualChildren <GridViewColumnHeader>(this).Count() - 2)
{
MessageBox.Show("Wrong Columns Format");
}
}
internal void UpdateSchoolPeriod(SchoolPeriod SchoolPeriod)
{
using (DbConnection conn = Connect())
{
DbCommand cmd = conn.CreateCommand();
string query = "UPDATE SchoolPeriods" +
" SET" +
//" idSchoolPeriod=" + SqlString(SchoolPeriod.IdSchoolPeriod) + "," +
" idSchoolPeriodType=" + SqlString(SchoolPeriod.IdSchoolPeriodType) + "," +
" dateFinish=" + SqlDate(SchoolPeriod.DateFinish) + "," +
" dateStart=" + SqlDate(SchoolPeriod.DateStart) + "," +
" name=" + SqlString(SchoolPeriod.Name) + "," +
" desc=" + SqlString(SchoolPeriod.Desc) + "," +
" idSchoolYear=" + SqlString(SchoolPeriod.IdSchoolYear) +
" WHERE idSchoolPeriod=" + SqlString(SchoolPeriod.IdSchoolPeriod) +
";";
cmd.CommandText = query;
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
internal List <SchoolPeriod> GetSchoolPeriodsOfDate(DateTime Date)
{
List <SchoolPeriod> l = new List <SchoolPeriod>();
using (DbConnection conn = dl.Connect())
{
DbDataReader dRead;
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT *" +
" FROM SchoolPeriods" +
" WHERE " + SqlVal.SqlDate(Date) +
" BETWEEN dateStart and dateFinish" +
";";
dRead = cmd.ExecuteReader();
while (dRead.Read())
{
SchoolPeriod p = GetOneSchoolPeriodFromRow(dRead);
l.Add(p);
}
}
return(l);
}
internal void CreateSchoolPeriod(SchoolPeriod SchoolPeriod)
{
using (DbConnection conn = Connect())
{
DbCommand cmd = conn.CreateCommand();
string query = "INSERT INTO SchoolPeriods" +
"(idSchoolPeriod, idSchoolPeriodType, dateStart, dateFinish, " +
" name, desc, idSchoolYear)";
query += " Values(";
query += "" + SqlString(SchoolPeriod.IdSchoolPeriod);
query += "," + SqlString(SchoolPeriod.IdSchoolPeriodType);
query += "," + SqlDate(SchoolPeriod.DateStart);
query += "," + SqlDate(SchoolPeriod.DateFinish);
query += "," + SqlString(SchoolPeriod.Name);
query += "," + SqlString(SchoolPeriod.Desc);
query += "," + SqlString(SchoolPeriod.IdSchoolYear);
query += ");";
cmd.CommandText = query;
cmd.ExecuteNonQuery();
cmd.Dispose();
}
return;
}
public void SetValues(SchoolPeriod schoolPeriod, ReportingPeriod reportingPeriod, School school, SchoolDistrict district)
{
SetValue(CommonPlacholder.DistrictName, district.Name);
SetValue(CommonPlacholder.PeriodName, reportingPeriod.Name);
SetValue(CommonPlacholder.SchoolName, school.Name);
}
public StudentImportResult ImportStudents(int districtID, int reportingPeriodID, IEnumerable<StudentImportRecord> importRecords, params StudentImportOptions[] importOptions)
{
//Create the result object, and mark the start time
var result = new StudentImportResult();
result.MarkStart();
//Get the list of schools for the School District, along with each schools list of teachers and school periods
var schools = _repo.GetSchools(districtID, SchoolInclude.Teachers, SchoolInclude.SchoolPeriods).ToList();
//Get a list of the class sessions in this school district, that are in the school period specifieid, and include the Class Type for each class
var existingClasses = _repo.GetClasses(ClassInclude.ClassType, ClassInclude.ClassEnrollments).Where(p => p.SchoolPeriod.ReportingPeriodID == reportingPeriodID).ToList();
//Also get a dedicated list of class types for the district (including the ones that are unique to specific schools in this district)
var classTypes = _repo.GetClassTypes(districtID, false).ToList();
//Get a list of all the students in this district
var students = _repo.GetStudents(districtID).ToList();
//Loop through each reacord in the imported data set
foreach (var record in importRecords)
{
result.NumRecordsProcessed++;
//Create a new student record
//If we find an existing student later, we will use this object to update the existing student
var newStudent = new Student
{
ImportID = record.StudentID,
FirstName = record.FirstName,
MiddleName = record.MiddleName,
LastName = record.LastName,
IDNumber = record.IDNumber,
GradeLevel = (byte)record.GradeLevel
};
//Skip if there is no school to link the imported record to
if (String.IsNullOrWhiteSpace(record.SchoolID))
{
result.SkippedRecords.Add(newStudent, ImportRecordSkipReason.NoSystemSchoolIdAvailable);
continue;
}
//Skip if there is no teacher to link the imported record to
if (String.IsNullOrWhiteSpace(record.TeacherID))
{
result.SkippedRecords.Add(newStudent, ImportRecordSkipReason.NoSystemTeacherIDAvailable);
continue;
}
//Skip if the grade level is out of range
if (record.GradeLevel < 0 || record.GradeLevel > 12)
{
result.SkippedRecords.Add(newStudent, ImportRecordSkipReason.GradeLevelOutOfRange);
continue;
}
//Find school the student belongs to
var school = schools.FirstOrDefault(p => p.ImportID == record.SchoolID);
//Skip if the school could not be found
if (school == null)
{
result.SkippedRecords.Add(newStudent, ImportRecordSkipReason.SchoolNotFound);
continue;
}
//Find the school period
var schoolPeriod = school.SchoolPeriods.FirstOrDefault(p => p.ReportingPeriodID == reportingPeriodID);
//Create the school period if necessary
if (schoolPeriod == null && importOptions.Contains(StudentImportOptions.AutoCreateSchoolPeriods))
{
//TODO: Somehow determine how many terms we should create
schoolPeriod = new SchoolPeriod { NumTerms = 3, School = school, ReportingPeriodID = reportingPeriodID };
//link to school
foreach (int i in Enumerable.Range(1, 3))
{
var term = new GradingTerm { TermNum = (byte)i, GradingOpen = false };
schoolPeriod.GradingTerms.Add(term);
}
//Add to repo for persistance
//(Linking to school should do this anyway, but just in case)
_repo.AddSchoolPeriod(schoolPeriod, false);
//Add new school period to result object
result.SchoolPeriodsCreated.Add(schoolPeriod);
}
else if (schoolPeriod == null)
{
//Skip if the school period couldn't be found, and we aren't suppose to auto create it
result.SkippedRecords.Add(newStudent, ImportRecordSkipReason.NoSchoolPeriodAvailable);
continue;
}
//Try and find an existing student
var existingStudent = students.FirstOrDefault(p => p.ImportID == record.StudentID && p.SchoolID == school.SchoolID);
//Alternatively, if selected, try to find the student by their ID number
if (existingStudent == null && importOptions.Contains(StudentImportOptions.AllowStudentMatchOnIdNumber) && !String.IsNullOrWhiteSpace(record.IDNumber) && record.IDNumber.Length > 1)
{
existingStudent = students.FirstOrDefault(p => p.IDNumber == record.IDNumber && p.SchoolID == school.SchoolID);
}
if (existingStudent != null)
{
//Update existing student
existingStudent.ImportID = newStudent.ImportID;
existingStudent.FirstName = newStudent.FirstName;
existingStudent.MiddleName = newStudent.MiddleName;
existingStudent.LastName = newStudent.LastName;
existingStudent.IDNumber = newStudent.IDNumber;
existingStudent.GradeLevel = newStudent.GradeLevel;
result.UpdatedRecords.Add(existingStudent);
}
else
{
//Link student to school
newStudent.School = school;
//Add to repo for persistance
//(which would probably happen anyway now that they are linked to a school)
_repo.AddStudent(newStudent, false);
result.InsertedRecords.Add(newStudent);
}
//Use either the existing student, or new student, depending on
var student = existingStudent ?? newStudent;
//Get the teacher that is teaching the enrolled class
var teacher = school.Teachers.FirstOrDefault(p => p.ImportID == record.TeacherID);
//Skip if teacher was not found
if (teacher == null)
{
result.SkippedRecords.Add(newStudent, ImportRecordSkipReason.TeacherNotFound);
continue;
}
//Find or create the class the student would be enrolled in
Class enrolledClass = existingClasses.FirstOrDefault(p => (p.TeacherID == teacher.TeacherID || p.Teacher == teacher) && p.ClassType.GradeLevel == record.GradeLevel);
if (enrolledClass == null && importOptions.Contains(StudentImportOptions.AutoCreateClasses))
{
ClassType classType = classTypes.FirstOrDefault(p => p.GradeLevel == record.GradeLevel && (p.SchoolID == null || p.SchoolID == school.SchoolID));
//Create the class type if necessary as well
if (classType == null)
{
classType = new ClassType();
classType.GradeLevel = (byte)record.GradeLevel;
classType.Name = classType.GradeLevelLong;
classType.SchoolDistrictID = school.SchoolDistrictID; //link to district
classType.SchoolID = null; //make available district wide
//Add class type creation to result object
result.ClassTypesCreated.Add(classType);
//persist the new class type
_repo.AddClassType(classType, false);
//Add the new class type to the local collection for future use
classTypes.Add(classType);
}
enrolledClass = new Class();
enrolledClass.ClassType = classType;
enrolledClass.Name = String.Format("{0}'s {1}", teacher.FullName, student.GradeLevelLong);
enrolledClass.Teacher = teacher;
enrolledClass.SchoolPeriod = schoolPeriod;
//add new class to result object
result.ClassesCreated.Add(enrolledClass);
//persist new class
_repo.AddClass(enrolledClass, false);
//add new class to local collection for future use
existingClasses.Add(enrolledClass);
}
else if (enrolledClass == null)
{
result.SkippedRecords.Add(newStudent, ImportRecordSkipReason.ClassNotAvailable);
continue;
}
//Find existing enrollment
var enrollment = enrolledClass.ClassEnrollments.FirstOrDefault(p => p.Student == student);
//Create the class enrollment if necessary
if (enrollment == null)
{
enrollment = new ClassEnrollment();
enrollment.Student = student;
enrollment.Class = enrolledClass;
//persist
_repo.AddClassEnrollment(enrollment, false);
result.EnrollmentsCreated.Add(enrollment);
}
}
//Save changes
_repo.Save();
result.MarkEnd();
return result;
}
internal void SaveSchoolPeriod(SchoolPeriod SchoolPeriod)
{
dl.SaveSchoolPeriod(SchoolPeriod);
}
