internal void CreateUser(User User)
{
using (DbConnection conn = Connect())
{
// check if username is existing
DbCommand cmd = conn.CreateCommand();
// !!!! TODO !!!!
// create row in table
string?now = SqlVal.SqlDate(DateTime.Now);
cmd.CommandText = "INSERT INTO Users " +
"(username, lastName, firstName, email," +
"password,creationTime,lastChange,lastPasswordChange,salt,idUserCategory,isEnabled)" +
"Values " +
"('" + SqlVal.SqlString(User.Username) + "','" +
SqlVal.SqlString(User.LastName) + "','" +
SqlVal.SqlString(User.FirstName) + "','" +
SqlVal.SqlString(User.Email) + "','" +
SqlVal.SqlString(User.Password) + "'," +
now + "," + now + "," + now + ",'" +
SqlVal.SqlString(User.Salt) + "','" +
SqlVal.SqlString(User.IdUserCategory.ToString()) + "', TRUE" +
");";
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
internal void ChangePassword(User User)
{
using (DbConnection conn = Connect())
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "UPDATE Users" +
" Set" +
" password='******'," +
" lastPasswordChange=" + SqlVal.SqlDate(DateTime.Now) + "," +
" salt='" + SqlVal.SqlString(User.Salt) + "'" +
" WHERE username='******'" +
";";
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 UpdateUser(User User)
{
using (DbConnection conn = Connect())
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "UPDATE Users" +
" Set" +
" description='" + SqlVal.SqlString(User.Description) + "'," +
" lastName='" + SqlVal.SqlString(User.LastName) + "'," +
" firstName='" + SqlVal.SqlString(User.FirstName) + "'," +
" email='" + SqlVal.SqlString(User.Email) + "'," +
//" password="******"'," +
" lastChange=" + SqlVal.SqlDate(DateTime.Now) + "," +
//" lastPasswordChange=" + SqlVal.SqlDate(DateTime.Now) + "," +
//" creationTime=" + SqlVal.SqlDate(User.CreationTime) + "," +
" salt='" + SqlVal.SqlString(User.Salt) + "'," +
" isEnabled=" + SqlVal.SqlBool(User.IsEnabled) +
" idUserCategory=" + SqlVal.SqlInt(User.IdUserCategory) +
" WHERE username='******'" +
";";
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
internal void UpdateUserOverride(string username, string lastname, string firstname, string password, string email, string description, DateTime last, DateTime lastpassw, DateTime creation, string salt, bool isenabled, int idusercateogry)
{
using (DbConnection conn = Connect())
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "UPDATE Users" +
" Set" +
" description='" + SqlVal.SqlString(description) + "'," +
" lastName='" + SqlVal.SqlString(lastname) + "'," +
" firstName='" + SqlVal.SqlString(firstname) + "'," +
" email='" + SqlVal.SqlString(email) + "'," +
" password="******"'," +
" lastChange=" + SqlVal.SqlDate(last) + "," +
" lastPasswordChange=" + SqlVal.SqlDate(lastpassw) + "," +
" creationTime=" + SqlVal.SqlDate(creation) + "," +
" salt='" + SqlVal.SqlString(salt) + "'," +
" isEnabled=" + SqlVal.SqlBool(isenabled) +
" idUserCategory=" + SqlVal.SqlInt(idusercateogry) +
" WHERE username='******'" +
";";
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
internal int CreateClassAndStudents(string[,] StudentsData, string ClassAbbreviation,
string ClassDescription, string SchoolYear, string OfficialSchoolAbbreviation,
bool LinkPhoto)
{
// creation of a new class in the Classes table
// finds a key for the new class
int idClass = NextKey("Classes", "idClass");
using (DbConnection conn = dl.Connect())
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO Classes " +
"(idClass, Desc, idSchoolYear, idSchool, abbreviation) " +
"Values (" + idClass + ",'" + SqlVal.SqlString(ClassDescription) + "','" +
SqlVal.SqlString(SchoolYear) + "','" + SqlVal.SqlString(OfficialSchoolAbbreviation) + "','" +
SqlVal.SqlString(ClassAbbreviation) + "'" +
");";
cmd.ExecuteNonQuery();
// find the key for next student
int idNextStudent = NextKey("Students", "idStudent");
// find the key for next picture
int idNextPhoto = NextKey("StudentsPhotos", "idStudentsPhoto");
// add the student to the students' table
// start from the second row of the file, first row is descriptions
for (int riga = 1; riga < StudentsData.GetLength(0); riga++)
{
int rigap1 = riga + 1;
// create new student
cmd.CommandText = "INSERT INTO Students " +
"(idStudent, lastName, firstName, residence, origin, email, birthDate, birthPlace) " +
"Values (" +
"'" + idNextStudent + "','" +
SqlVal.SqlString(StudentsData[riga, 1]) + "','" +
SqlVal.SqlString(StudentsData[riga, 2]) + "','" +
SqlVal.SqlString(StudentsData[riga, 3]) + "','" +
SqlVal.SqlString(StudentsData[riga, 4]) + "','" +
SqlVal.SqlString(StudentsData[riga, 5]) + "'," +
SqlVal.SqlDate(StudentsData[riga, 6]) + ",'" +
SqlVal.SqlString(StudentsData[riga, 7]) + "'" +
");";
cmd.ExecuteNonQuery();
// aggiunge lo studente alla classe
cmd.CommandText = "INSERT INTO Classes_Students " +
"(idClass, idStudent, registerNumber) " +
"Values ('" + idClass + "','" + idNextStudent + "','" + rigap1.ToString() + "'" +
");";
cmd.ExecuteNonQuery();
if (LinkPhoto)
{
// aggiunge la foto alle foto
cmd.CommandText = "INSERT INTO StudentsPhotos " +
"(idStudentsPhoto, photoPath)" +
"Values " +
"('" + idNextPhoto + "','" + SqlVal.SqlString(SchoolYear) +
SqlVal.SqlString(ClassAbbreviation) + "\\" + SqlVal.SqlString(StudentsData[riga, 1]) + "_" +
SqlVal.SqlString(StudentsData[riga, 2]) + "_" + SqlVal.SqlString(ClassAbbreviation) +
SqlVal.SqlString(SchoolYear) + ".jpg" + // TODO mettere l'estensione del file che c'è effettivamente
"');"; // relative path. Home path will be added at visualization time
cmd.ExecuteNonQuery();
// add the picture to the link table
cmd.CommandText = "INSERT INTO StudentsPhotos_Students " +
"(idStudentsPhoto, idStudent, idSchoolYear) " +
"Values (" + idNextPhoto + "," + idNextStudent + ",'" + SqlVal.SqlString(SchoolYear) +
"');";
cmd.ExecuteNonQuery();
idNextPhoto++;
}
idNextStudent++;
}
cmd.Dispose();
}
return(idClass);
}