internal string CreateDemoDatabase(string newDatabaseFullName, Class Class1, Class Class2)
{
DbCommand cmd;
File.Copy(Commons.PathAndFileDatabase, newDatabaseFullName);
// local instance of a DataLayer to operate on a second database
DataLayer newDatabaseDl = new DataLayer(newDatabaseFullName);
// erase all the data of the students of other classes
using (DbConnection conn = newDatabaseDl.Connect()) // connect to the new database, just copied
{
cmd = conn.CreateCommand();
// erase all the other classes
cmd.CommandText = "DELETE FROM Classes" +
" WHERE idClass<>" + Class1.IdClass +
" AND idClass<>" + Class2.IdClass + ";";
cmd.ExecuteNonQuery();
// erase all the lessons of other classes
cmd.CommandText = "DELETE FROM Lessons" +
" WHERE idClass<>" + Class1.IdClass +
" AND idClass<>" + Class2.IdClass + ";";
cmd.ExecuteNonQuery();
// erase all the students of other classes from the link table
cmd.CommandText = "DELETE FROM Classes_Students" +
" WHERE idClass<>" + Class1.IdClass +
" AND idClass<>" + Class2.IdClass + ";";
cmd.ExecuteNonQuery();
// erase all the students of other classes
cmd.CommandText = "DELETE FROM Students" +
" WHERE idStudent NOT IN" +
" (SELECT idStudent FROM Classes_Students" +
" WHERE idClass<>" + Class1.IdClass +
" OR idClass<>" + Class2.IdClass +
");";
cmd.ExecuteNonQuery();
// erase all the annotation, of all classes
cmd.CommandText = "DELETE FROM StudentsAnnotations" +
";";
cmd.ExecuteNonQuery();
// erase all the StartLinks of other classes
cmd.CommandText = "DELETE FROM Classes_StartLinks" +
" WHERE idClass<>" + Class1.IdClass +
" AND idClass<>" + Class2.IdClass +
";";
cmd.ExecuteNonQuery();
// erase all the grades of other classes' students
cmd.CommandText = "DELETE FROM Grades" +
" WHERE idStudent NOT IN" +
" (SELECT idStudent FROM Classes_Students" +
" WHERE idClass<>" + Class1.IdClass +
" OR idClass<>" + Class2.IdClass +
");";
cmd.ExecuteNonQuery();
// erase all the links to photos of other classes' students
cmd.CommandText = "DELETE FROM StudentsPhotos_Students" +
" WHERE idStudent NOT IN" +
" (SELECT idStudent FROM Classes_Students" +
" WHERE idClass<>" + Class1.IdClass +
" OR idClass<>" + Class2.IdClass +
");";
cmd.ExecuteNonQuery();
// erase all the photos of other classes' students
cmd.CommandText = "DELETE FROM StudentsPhotos WHERE StudentsPhotos.idStudentsPhoto NOT IN" +
"(SELECT StudentsPhotos_Students.idStudentsPhoto" +
" FROM StudentsPhotos, StudentsPhotos_Students, Classes_Students" +
" WHERE StudentsPhotos_Students.idStudent = Classes_Students.idStudent" +
" AND StudentsPhotos.idStudentsPhoto = StudentsPhotos_Students.idStudentsPhoto" +
" AND (Classes_Students.idClass=" + Class1.IdClass +
" OR Classes_Students.idClass=" + Class2.IdClass + ")" +
");";
cmd.ExecuteNonQuery();
// erase all the images of other classes
cmd.CommandText = "DELETE FROM Images WHERE Images.idImage NOT IN" +
"(SELECT DISTINCT Lessons_Images.idImage" +
" FROM Images, Lessons_Images, Lessons" +
" WHERE Lessons_Images.idImage = Images.idImage" +
" AND Lessons_Images.idLesson = Lessons.idLesson" +
" AND (Lessons.idClass=" + Class1.IdClass +
" OR Lessons.idClass=" + Class2.IdClass + ")" +
");";
cmd.ExecuteNonQuery();
//erase all links to the images of other classes
cmd.CommandText = "DELETE FROM Lessons_Images WHERE Lessons_Images.idImage NOT IN" +
"(SELECT DISTINCT Lessons_Images.idImage" +
" FROM Images, Lessons_Images, Lessons" +
" WHERE Lessons_Images.idImage = Images.idImage" +
" AND Lessons_Images.idLesson = Lessons.idLesson" +
" AND (Lessons.idClass=" + Class1.IdClass +
" OR Lessons.idClass=" + Class2.IdClass + ")" +
");";
cmd.ExecuteNonQuery();
// erase all the questions of the students of the other classes
// !! StudentsQuestions currently not used !!
cmd.CommandText = "DELETE FROM StudentsQuestions" +
" WHERE idStudent NOT IN" +
" (SELECT DISTINCT idStudent FROM Classes_Students" +
" WHERE idClass=" + Class1.IdClass +
" OR idClass=" + Class2.IdClass +
");";
cmd.ExecuteNonQuery();
// erase all the answers of the students of the other classes
// !! StudentsAnswers currently not used !!
cmd.CommandText = "DELETE FROM StudentsAnswers" +
" WHERE idStudent NOT IN" +
" (SELECT idStudent FROM Classes_Students" +
" WHERE idClass=" + Class1.IdClass +
" OR idClass=" + Class2.IdClass + ");";
cmd.ExecuteNonQuery();
// erase all the tests of students of the other classes
// !! StudentsTests currently not used !!
cmd.CommandText = "DELETE FROM StudentsTests" +
" WHERE idStudent NOT IN" +
" (SELECT idStudent FROM Classes_Students" +
" WHERE idClass=" + Class1.IdClass +
" OR idClass=" + Class2.IdClass +
");";
cmd.ExecuteNonQuery();
// erase all the topics of other classes' lessons
cmd.CommandText = "DELETE FROM Lessons_Topics" +
" WHERE idLesson NOT IN" +
" (SELECT idLesson from Lessons" +
" WHERE idClass=" + Class1.IdClass +
" OR idClass=" + Class2.IdClass +
");";
cmd.ExecuteNonQuery();
// change the data of the classes
Class1.Abbreviation = "DEMO1";
Class1.Description = "SchoolGrades demo class 1";
// Class1.IdSchool = ""; // left the existing code
Class1.PathRestrictedApplication = Commons.PathExe + "\\demo1";
// Class1.SchoolYear = // !!!! shift the data to the destination school year, to be done when year's shifting will be managed!!!!
Class1.IdSchool = Commons.IdSchool;
Class1.UriWebApp = ""; // ???? decide what to put here ????
// SaveClass Class1;
string query = "UPDATE Classes" +
" SET" +
" idClass=" + Class1.IdClass + "" +
",idSchoolYear=" + SqlString(Class1.SchoolYear) + "" +
",idSchool=" + SqlString(Class1.IdSchool) + "" +
",abbreviation=" + SqlString(Class1.Abbreviation) + "" +
",desc=" + SqlString(Class1.Description) + "" +
",uriWebApp=" + Class1.UriWebApp + "" +
",pathRestrictedApplication=" + SqlString(Class1.PathRestrictedApplication) + "" +
" WHERE idClass=" + Class1.IdClass +
";";
cmd.CommandText = query;
cmd.ExecuteNonQuery();
Class2.Abbreviation = "DEMO2";
Class2.Description = "SchoolGrades demo class 2";
Class2.PathRestrictedApplication = Commons.PathExe + "\\demo2";
// Class2.SchoolYear = !!!! shift the data to the destination school year !!!!
Class2.IdSchool = Commons.IdSchool;
Class2.UriWebApp = ""; // ???? decide what to put here ????
// SaveClass Class2;
query = "UPDATE Classes" +
" SET" +
" idClass=" + Class2.IdClass + "" +
",idSchoolYear=" + SqlString(Class2.SchoolYear) + "" +
",idSchool=" + SqlString(Class2.IdSchool) + "" +
",abbreviation=" + SqlString(Class2.Abbreviation) + "" +
",desc=" + SqlString(Class2.Description) + "" +
",uriWebApp=" + Class2.UriWebApp + "" +
",pathRestrictedApplication=" + SqlString(Class2.PathRestrictedApplication) + "" +
" WHERE idClass=" + Class2.IdClass +
";";
cmd.CommandText = query;
cmd.ExecuteNonQuery();
// erase all the users
cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM Users" +
";";
cmd.ExecuteNonQuery();
// rename every student left in the database according to the names found in the pictures' filenames
RenameStudentsNamesFromPictures(Class1, conn);
RenameStudentsNamesFromPictures(Class2, conn);
// change the paths of the images
ChangeImagesPath(Class1, conn);
ChangeImagesPath(Class2, conn);
// randomly change all grades
RandomizeGrades(conn);
// change the lesson dates to this school year (when we implement year shift!)
// !!!! TODO !!!!
// change the school year in StudentsPhotos_Students (when we implement year shift!)
// !!!! TODO !!!!
// compact the database
cmd.CommandText = "VACUUM;";
cmd.ExecuteNonQuery();
cmd.Dispose();
}
return(newDatabaseFullName);
}
internal string CreateOneClassOnlyDatabase(Class Class)
{
string newDatabasePathName = Class.PathRestrictedApplication + "\\SchoolGrades\\Data\\";
if (!Directory.Exists(newDatabasePathName))
{
Directory.CreateDirectory(newDatabasePathName);
}
string newDatabaseFullName = newDatabasePathName +
System.DateTime.Now.ToString("yyyy-MM-dd_HH.mm.ss") +
"_" + Class.Abbreviation + "_" + Class.SchoolYear + "_" +
Commons.FileDatabase;
File.Copy(Commons.PathAndFileDatabase, newDatabaseFullName);
// open a local connection to database
DataLayer newDatabaseDl = new DataLayer(newDatabaseFullName);
// erase all the data of the students of other classes
using (DbConnection conn = newDatabaseDl.Connect())
{
DbCommand cmd = conn.CreateCommand();
// erase all the other classes
cmd.CommandText = "DELETE FROM Classes" +
" WHERE idClass<>" + Class.IdClass + ";";
cmd.ExecuteNonQuery();
// erase all the lessons of other classes
cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM Lessons" +
" WHERE idClass<>" + Class.IdClass + ";";
cmd.ExecuteNonQuery();
// erase all the students of other classes from the link table
cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM Classes_Students" +
" WHERE idClass<>" + Class.IdClass + ";";
cmd.ExecuteNonQuery();
// erase all the students of other classes
cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM Students" +
" WHERE idStudent NOT IN" +
" (SELECT idStudent FROM Classes_Students);";
cmd.ExecuteNonQuery();
// erase all the StartLinks of other classes
cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM Classes_StartLinks" +
" WHERE idClass<>" + Class.IdClass + ";";
cmd.ExecuteNonQuery();
// erase all the grades of other classes' students
cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM Grades" +
" WHERE idStudent NOT IN" +
" (SELECT idStudent FROM Classes_Students);";
cmd.ExecuteNonQuery();
// erase all the links to photos of other classes' students
// !! retains previous year's photos of this classes students !!
cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM StudentsPhotos_Students" +
" WHERE idStudent NOT IN" +
" (SELECT idStudent FROM Classes_Students);";
cmd.ExecuteNonQuery();
// erase all the annotations of other classes
cmd.CommandText = "DELETE FROM StudentsAnnotations" +
" WHERE idStudent NOT IN" +
" (SELECT idStudent FROM Classes_Students)" +
";";
cmd.ExecuteNonQuery();
// erase all the photos of other classes' students
// !! retains previous year's photos of this classes students !!
cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM StudentsPhotos" +
" WHERE idStudentsPhoto NOT IN" +
" (SELECT idStudentsPhoto FROM StudentsPhotos_Students);";
cmd.ExecuteNonQuery();
// erase all the questions of the students of the other classes
// !! StudentsQuestions currently not used !!
cmd.CommandText = "DELETE FROM StudentsQuestions" +
" WHERE idStudent NOT IN" +
" (SELECT idStudent FROM Classes_Students);";
cmd.ExecuteNonQuery();
// erase all the answers of the students of the other classes
// !! StudentsAnswers currently not used !!
cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM StudentsAnswers" +
" WHERE idStudent NOT IN" +
" (SELECT idStudent FROM Classes_Students);";
cmd.ExecuteNonQuery();
// erase all the tests of students of the other classes
// !! StudentsTests currently not used !!
cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM StudentsTests" +
" WHERE idStudent NOT IN" +
" (SELECT idStudent FROM Classes_Students);";
cmd.ExecuteNonQuery();
// erase all the images of other classes' lessons
cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM Lessons_Images" +
" WHERE idLesson NOT IN" +
" (SELECT idLesson from Lessons);";
cmd.ExecuteNonQuery();
// erase all the topics of other classes' lessons
cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM Lessons_Topics" +
" WHERE idLesson NOT IN" +
" (SELECT idLesson from Lessons);";
cmd.ExecuteNonQuery();
// erase all the users
cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM Users" +
";";
cmd.ExecuteNonQuery();
// copy all the students' photo files that aren't already there or that have a newer date
string query = "SELECT StudentsPhotos.photoPath" +
" FROM StudentsPhotos" +
" JOIN StudentsPhotos_Students ON StudentsPhotos_Students.idStudentsPhoto = StudentsPhotos.idStudentsPhoto" +
" JOIN Classes_Students ON StudentsPhotos_Students.idStudent = Classes_Students.idStudent" +
" WHERE Classes_Students.idClass = " + Class.IdClass + "; ";
cmd = new SQLiteCommand(query);
cmd.Connection = conn;
DbDataReader dReader = cmd.ExecuteReader();
while (dReader.Read())
{
string destinationFile = Class.PathRestrictedApplication + "\\SchoolGrades\\Images\\" + (string)dReader["photoPath"];
if (!Directory.Exists(Path.GetDirectoryName(destinationFile)))
{
Directory.CreateDirectory(Path.GetDirectoryName(destinationFile));
}
if (!File.Exists(destinationFile) ||
File.GetLastWriteTime(destinationFile)
< File.GetLastWriteTime(Commons.PathImages + "\\" + (string)dReader["photoPath"]))
{
try
{
// destination file not existing or older
File.Copy(Commons.PathImages + "\\" + (string)dReader["photoPath"],
destinationFile);
}
catch { }
}
}
// copy all the picture's files that aren't already there or that have a newer date
query = "SELECT Images.imagePath, Classes.pathRestrictedApplication" +
" FROM Images" +
" JOIN Lessons_Images ON Lessons_Images.idImage=Images.idImage" +
" JOIN Lessons ON Lessons_Images.idLesson=Lessons.idLesson" +
" JOIN Classes ON Classes.idClass=Lessons.idClass" +
" WHERE Lessons.idClass=" + Class.IdClass +
";";
cmd = new SQLiteCommand(query);
cmd.Connection = conn;
dReader = cmd.ExecuteReader();
while (dReader.Read())
{
if (dReader["pathRestrictedApplication"] is DBNull)
{
Console.Beep();
break;
}
if (dReader["imagePath"] is DBNull)
{
Console.Beep();
break;
}
string destinationFile = (string)dReader["pathRestrictedApplication"] +
"\\SchoolGrades\\" + "Images" + "\\" + (string)dReader["imagePath"];
if (!Directory.Exists(Path.GetDirectoryName(destinationFile)))
{
Directory.CreateDirectory(Path.GetDirectoryName(destinationFile));
}
if (!File.Exists(destinationFile) ||
File.GetLastWriteTime(destinationFile)
< File.GetLastWriteTime(Commons.PathImages + "\\" + (string)dReader["imagePath"]))
{
// destination file not existing or older
try
{
File.Copy(Commons.PathImages + "\\" + (string)dReader["imagePath"],
destinationFile);
}
catch { }
}
}
dReader.Dispose();
// compact the database
cmd.CommandText = "VACUUM;";
cmd.ExecuteNonQuery();
cmd.Dispose();
}
return(Class.PathRestrictedApplication);
}