private List <string> ReadColumnName(string table)
{
using SQLiteCommand cmd = GlobalFunction.OpenDbConnection();
cmd.CommandText = $"PRAGMA table_info({table})";
using SQLiteDataReader rdr = cmd.ExecuteReader();
List <string> columnList = new List <string>();
while (rdr.Read())
{
columnList.Add(rdr.GetString(1));
}
return(columnList);
}
//private readonly int int32id;
public studentsPage()
{
InitializeComponent();
studentsCollection = new ObservableCollection <StudentDisplay>();
studentList.ItemsSource = studentsCollection;
using SQLiteCommand cmd = GlobalFunction.OpenDbConnection();
cmd.CommandText = "SELECT studentId FROM students";
using SQLiteDataReader rdr = cmd.ExecuteReader();
List <int> studentIdList = new List <int>();
while (rdr.Read())
{
studentIdList.Add((int)rdr.GetInt64(0));
}
ReadStudentsData(studentIdList);
}
private void RetrieveLastHomeworkButton_Click(object sender, RoutedEventArgs e)
{
StudentDisplay currentStudent = (StudentDisplay)((FrameworkElement)sender).DataContext;
using SQLiteCommand cmd = GlobalFunction.OpenDbConnection();
cmd.CommandText = "UPDATE homeworks SET retrieveDate = @retrieveDate WHERE homeworkId = @id";
cmd.Parameters.AddWithValue("id", currentStudent.LastHomeWorkId);
int currentTimestamp = (int)new DateTimeOffset(DateTime.Now).ToUnixTimeSeconds();
cmd.Parameters.AddWithValue("retrieveDate", currentTimestamp);
cmd.Prepare();
cmd.ExecuteNonQuery();
currentStudent.LastHomeworkStatusColor = "Green";
currentStudent.LastHomeworkStatusText = GlobalVariable.specialCharacter["CheckMark"];
currentStudent.HomeworkButtonEnabled = false;
}
public studentsPage(int classroomId)
{
InitializeComponent();
studentsCollection = new ObservableCollection <StudentDisplay>();
studentList.ItemsSource = studentsCollection;
using SQLiteCommand cmd = GlobalFunction.OpenDbConnection();
cmd.CommandText = "SELECT studentId FROM linkStudentToClassroom WHERE classroomId = @classroomId";
cmd.Parameters.AddWithValue("classroomId", classroomId);
cmd.Prepare();
List <int> studentIdList = new List <int>();
using SQLiteDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
studentIdList.Add(rdr.GetInt32(0));
}
ReadStudentsData(studentIdList);
}
/// <summary>
/// Get all the notes from a given student
/// </summary>
/// <param name="studentId"></param>
/// <returns></returns>
public static List <NoteInfo> GetAllNotes(int studentId)
{
using SQLiteCommand cmd = GlobalFunction.OpenDbConnection();
cmd.CommandText = $"SELECT * FROM notes WHERE studentId = {studentId}";
using SQLiteDataReader rdr = cmd.ExecuteReader();
List <NoteInfo> notesList = new List <NoteInfo>();
while (rdr.Read())
{
NoteInfo noteInfo = new NoteInfo()
{
noteId = rdr.GetInt32(0),
creationDate = rdr.GetInt32(2),
content = rdr.GetString(3)
};
notesList.Add(noteInfo);
}
rdr.Close();
return(notesList);
}
/// <summary>
/// Get all homeworks from a specific studentId
/// </summary>
/// <param name="studentId"></param>
/// <returns></returns>
private static List <HomeworkInfo> GetAllHomeworks(int studentId)
{
using SQLiteCommand cmd = GlobalFunction.OpenDbConnection();
cmd.CommandText = $"SELECT * FROM homeworks WHERE studentId = {studentId}";
using SQLiteDataReader rdr = cmd.ExecuteReader();
List <HomeworkInfo> homeworksList = new List <HomeworkInfo>();
while (rdr.Read())
{
HomeworkInfo homeworkInfo = new HomeworkInfo()
{
homeworkId = rdr.GetInt32(0),
creationDate = rdr.GetInt32(2),
endDate = rdr.GetInt32(3),
retrieveDate = rdr.GetInt32(4),
description = rdr.GetString(5)
};
homeworksList.Add(homeworkInfo);
}
rdr.Close();
return(homeworksList);
}
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();
}
}