private void ReadStudentsData(List <int> studentIdList) { foreach (int studentId in studentIdList) { using SQLiteCommand cmd = GlobalFunction.OpenDbConnection(); //Handle classroom name List <int> classroomIdList = Database.Get.Classroom.AllIDFromStudentID(studentId); List <string> classroomNameList = new List <string>(); foreach (int classroomId in classroomIdList) { classroomNameList.Add(Database.Get.Classroom.NameFromID(classroomId)); } string classroomName = String.Join(", ", classroomNameList.ToArray()); string[] studentName = Database.Get.Student.NameFromID(studentId); //Handle homework List <HomeworkInfo> homeworkList = GetAllHomeworks(studentId); HomeworkInfo lastHomework = GetLastHomework(homeworkList); bool lastHomeworkButtonEnabled = false; string lastHomeworkStatus = GlobalVariable.specialCharacter["CheckMark"]; string lastHomeworkColor = "Green"; if (lastHomework.retrieveDate == 0 && lastHomework.creationDate != 0) { lastHomeworkButtonEnabled = true; lastHomeworkStatus = GlobalVariable.specialCharacter["Cross"]; lastHomeworkColor = "Red"; } //Handle note List <NoteInfo> notesList = GetAllNotes(studentId); NoteInfo lastNotes = GetLastNote(notesList); //Handle votes List <VotesInfo> upvotesList = Database.Get.Vote.AllFromStudentId(studentId, true); List <VotesInfo> downvotesList = Database.Get.Vote.AllFromStudentId(studentId, false); string average = ""; List <GradeInfo> gradesList = Database.Get.Grade.AllFromStudentId(studentId); if (gradesList.Count == 0) { average = "20/20"; } else { float[] gradesArray = new float[gradesList.Count]; int[] coeffArray = new int[gradesList.Count]; for (int i = 0; i < gradesList.Count; i++) { gradesArray[i] = gradesList[i].Grade; coeffArray[i] = gradesList[i].Coeff; } int coeffSum = coeffArray.Sum(); float[] gradesCoeffMultiply = new float[gradesList.Count]; for (int i = 0; i < gradesList.Count; i++) { gradesCoeffMultiply[i] = gradesArray[i] * coeffArray[i]; } float gradesCoefMultiplySum = gradesCoeffMultiply.Sum(); float averageFloat = gradesCoefMultiplySum / coeffSum; average = averageFloat.ToString() + "/20"; } StudentDisplay studentDisplay = new StudentDisplay() { ID = studentId, Name = studentName[1] + " " + studentName[0], ClassroomName = classroomName, HomeworkButtonEnabled = lastHomeworkButtonEnabled, LastHomeworkStatusText = lastHomeworkStatus, LastHomeworkStatusColor = lastHomeworkColor, LastHomeWorkId = lastHomework.homeworkId, Note = lastNotes.content ?? "Aucune note", Average = average, UpvotesCount = upvotesList.Count.ToString(), DownvotesCount = downvotesList.Count.ToString() }; studentsCollection.Add(studentDisplay); } }
//f private async void CreateDb() { await ApplicationData.Current.LocalFolder.CreateFileAsync(GlobalVariable.currentDbName, CreationCollisionOption.OpenIfExists); using SQLiteCommand cmd = GlobalFunction.OpenDbConnection(); cmd.CommandText = @"CREATE TABLE IF NOT EXISTS students(studentId INTEGER PRIMARY KEY, lastname TEXT, surname TEXT, gender BOOLEAN, board BOOLEAN, interrogation BOOLEAN, mask INTEGER); --Mask new in 1.1.0.0 CREATE TABLE IF NOT EXISTS linkStudentToClassroom(linkId INTERGER PRIMARY KEY, studentId INTEGER, classroomId INTEGER); CREATE TABLE IF NOT EXISTS homeworks(homeworkId INTEGER PRIMARY KEY, studentId INTEGER, creationDate INTEGER, endDate INTEGER, retrieveDate INTEGER, description TEXT, active BOOLEAN); CREATE TABLE IF NOT EXISTS punishments(punishmentId INTEGER PRIMARY KEY, studentId INTEGER, creationDate INTEGER, endDate INTEGER, retrieveDate INTEGER, description TEXT, active BOOLEAN); CREATE TABLE IF NOT EXISTS notes(noteId INTEGER PRIMARY KEY, studentId INTEGER, creationDate INTEGER, content TEXT, active BOOLEAN); CREATE TABLE IF NOT EXISTS grades(gradeId INTEGER PRIMARY KEY, studentId INTEGER, grade FLOAT, coeff INTEGER, creationDate INTEGER, title TEXT, active BOOLEAN); CREATE TABLE IF NOT EXISTS votes(voteId INTEGER PRIMARY KEY, studentId INTEGER, upvotes BOOLEAN, description TEXT, creationDate INTEGER, active BOOLEAN); CREATE TABLE IF NOT EXISTS rooms(roomId INTEGER PRIMARY KEY, name TEXT, rows INTEGER, columns INTEGER); CREATE TABLE IF NOT EXISTS classrooms(classroomId INTEGER PRIMARY KEY, name TEXT); CREATE TABLE IF NOT EXISTS schedules(scheduleId INTEGER PRIMARY KEY, classroomId INTEGER, roomId INTEGER, repetitivity INTEGER, nextDate INTEGER, duration INTEGER, active BOOLEAN); CREATE TABLE IF NOT EXISTS plans(planId INTEGER PRIMARY KEY, scheduleId INTEGER, roomId INTEGER, spacing TEXT, name TEXT, active BOOLEAN); CREATE TABLE IF NOT EXISTS places(placeId INTEGER PRIMARY KEY, planId INTEGER, studentId INTEGER, row INTEGER, column INTEGER); CREATE TABLE IF NOT EXISTS pairs(pairId INTEGER PRIMARY KEY, studentId1 INTEGER, studentId2 INTEGER, classroomId INTEGER); CREATE TABLE IF NOT EXISTS reminders(reminderId INTEGER PRIMARY KEY, creationDate INTEGER, reminderDate INTEGER, description TEXT, active BOOLEAN);"; //Spacing in plans is a string of comma seperated int cmd.ExecuteNonQuery(); #if DEBUG cmd.CommandText = "DELETE FROM schedules WHERE scheduleId = 1"; cmd.ExecuteNonQuery(); #endif List <string> columnList = ReadColumnName("students"); if (columnList.Contains("classroomId")) { cmd.CommandText = "SELECT * FROM students"; using SQLiteDataReader rdr = cmd.ExecuteReader(); List <StudentInfo> studentList = new List <StudentInfo>(); if (!columnList.Contains("mask")) { while (rdr.Read()) { StudentInfo student = new StudentInfo() { studentId = rdr.GetInt32(0), lastname = rdr.GetString(2), surname = rdr.GetString(3), gender = rdr.GetBoolean(4), board = rdr.GetBoolean(5), interrogation = rdr.GetBoolean(6), mask = 0 }; studentList.Add(student); } rdr.Close(); } else { while (rdr.Read()) { StudentInfo student = new StudentInfo() { studentId = rdr.GetInt32(0), lastname = rdr.GetString(2), surname = rdr.GetString(3), gender = rdr.GetBoolean(4), interrogation = rdr.GetBoolean(5), mask = rdr.GetInt32(6) }; studentList.Add(student); } rdr.Close(); } cmd.CommandText = "DROP TABLE students"; cmd.ExecuteNonQuery(); cmd.CommandText = "CREATE TABLE students(studentId INTEGER PRIMARY KEY, lastname TEXT, surname TEXT, gender BOOLEAN, board BOOLEAN, interrogation BOOLEAN, mask INTEGER);"; cmd.ExecuteNonQuery(); foreach (StudentInfo student in studentList) { cmd.CommandText = "INSERT INTO students(studentId, lastname, surname, gender, board, interrogation, mask) VALUES(@studentId, @lastname, @surname, @gender, @board, @interrogation, @mask)"; cmd.Parameters.AddWithValue("studentId", student.studentId); cmd.Parameters.AddWithValue("lastname", student.lastname); cmd.Parameters.AddWithValue("surname", student.surname); cmd.Parameters.AddWithValue("gender", student.gender); cmd.Parameters.AddWithValue("board", student.board); cmd.Parameters.AddWithValue("interrogation", student.interrogation); cmd.Parameters.AddWithValue("mask", student.mask); cmd.Prepare(); cmd.ExecuteNonQuery(); } } columnList = ReadColumnName("students"); if (!columnList.Contains("mask")) { cmd.CommandText = "ALTER TABLE students ADD mask INTEGER"; cmd.ExecuteNonQuery(); } columnList = ReadColumnName("votes"); if (!columnList.Contains("active")) { cmd.CommandText = "ALTER TABLE votes ADD active BOOLEAN"; cmd.ExecuteNonQuery(); } }