public static List<Grade> GetAllGrades()
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
return context.Grades.ToList();
}
}
public static List<Subject> GetAllSubjects()
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
return context.Subjects.ToList();
}
}
public static void PlaceMark(int lessonID, int studentID, decimal? markValue, bool isPresent = true)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
try
{
//context.placeMark(lessonID, studentID, markValue, isPresent);
int markID = -1;
if (markValue.HasValue) //check for correct value
{
var value = from mark in context.Marks
where mark.Value == markValue
select mark;
if (value.Count() == 0)
throw new ArgumentOutOfRangeException(string.Format("Mark value '{0}' is incorrect!", markValue));
markID = value.First().MarkID;
}
var less_student = from record in context.lesson_student
where record.LessonID == lessonID &&
record.StudentID == studentID
select record;
if (markID != -1)
less_student.First().MarkID = markID;
less_student.First().IsPresent = isPresent;
context.SaveChanges();
}
catch (Exception ex)
{
throw new Exception("PlaceMark failed!"+ex.Message+"\n"+ex.InnerException.Message);
}
}
}
private void AddStudentToGroupDialog_Load(object sender, EventArgs e)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
var studentsInGroup = from grp in context.Groups
from student in context.Students
where grp.students.Contains(student) &&
(grp.grade.GradeNo + grp.grade.Section).Equals(GradeSection)
select student.StudentID;
var studentNames = from student in context.Students
where !studentsInGroup.Contains(student.StudentID)
&& (student.grade.GradeNo + student.grade.Section).Equals(GradeSection)
select student.user.UserID + " " + student.user.LastName + " " + student.user.FirstName;
foreach (var sName in studentNames)
{
this.comboBoxNames.Items.Add(sName.ToString());
}
}
if (comboBoxNames.Items.Count > 0)
comboBoxNames.SelectedItem = comboBoxNames.Items[0];
else
{
comboBoxNames.Visible = false;
lblError.Visible = true;
}
}
public static int NewGroup(int teacherID, string subject, string gradeSection, string description)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
//get gradeID using gradeSection
int gradeID = (from grade in context.Grades where grade.GradeNo + grade.Section == gradeSection select grade.GradeID).FirstOrDefault();
if (gradeID == 0)
throw new ArgumentOutOfRangeException("Grade name is invalid!");
//get subjectID
int subjID = (from subj in context.Subjects where subj.Title == subject select subj.SubjectID).FirstOrDefault();
if (subjID == 0)
throw new ArgumentOutOfRangeException("Subject title is invalid!");
Group newGroup = new Group
{
TeacherID = teacherID,
SubjectID = subjID,
GradeID = gradeID,
Description = description
};
context.Groups.Add(newGroup);
context.SaveChanges();
return newGroup.GroupID;
}
}
public static List<string> GetStudyYears(int teacherID, int gradeNo, int yearEndMonth)
{
SchoolJournalEntities context = new SchoolJournalEntities();
HashSet<string> studyYears = new HashSet<string>();
var records = from j in context.attendanceJournal
where j.TeacherID == teacherID && j.Grade == gradeNo
select j;
var dates = (from rec in records select rec.Date).Distinct();
foreach (var date in dates)
{
//semester begining - 01.09; semester ending - 31.07;
if (date.Month < yearEndMonth) //period from New Year to summer - semester beginning - the previous year
{
string studyYear = string.Format("{0}-{1}", date.Year - 1, date.Year);
studyYears.Add(studyYear);
}
else
{
string studyYear = string.Format("{0}-{1}", date.Year, date.Year+1);
studyYears.Add(studyYear);
}
}
context.Dispose();
return studyYears.ToList();
}
public static TeacherInfo GetTeacher(int teacherID)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
var teachers = from t in context.Teachers where t.TeacherID == teacherID select t;
return new TeacherInfo(teachers.First());
}
}
public static AdminInfo GetAdmin(int adminID)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
var admins = from adm in context.Admins where adm.AdminID == adminID select adm;
return new AdminInfo(admins.First());
}
}
public static void RemoveStudentFromGroup(int groupID, int studentID)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
context.Database.ExecuteSqlCommand("DELETE FROM student_group WHERE group_id=@group_id AND student_id=@student_id",
new SqlParameter("@group_id", groupID), new SqlParameter("@student_id", studentID));
context.SaveChanges();
}
}
public static void AddStudentToGroup(int groupID, int studentID)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
context.Database.ExecuteSqlCommand(
string.Format("INSERT INTO student_group(group_id,student_id) VALUES({0},{1})",groupID,studentID));
context.SaveChanges();
}
}
public static void DeleteParent(int parentID)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
Parent parentToDelete = context.Set<Parent>().Find(parentID);
context.Parents.Remove(parentToDelete);
context.SaveChanges();
}
}
public static void DeleteParent(int parentID)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
Parent parentToDelete = context.Set <Parent>().Find(parentID);
context.Parents.Remove(parentToDelete);
context.SaveChanges();
}
}
public static void AddParent(int studentID, int parentID)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
context.Database.ExecuteSqlCommand("INSERT INTO parent_student VALUES (@student_id, @parent_id)",
new SqlParameter("@student_id", studentID),
new SqlParameter("@parent_id", parentID));
context.SaveChanges();
}
}
public static int AddNewParent(Parent newParent)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
if (!Util.IsValidEmail(newParent.user.Email))
throw new ArgumentException("Email string is not a valid email!");
context.Parents.Add(newParent);
context.SaveChanges();
return newParent.ParentID;
}
}
public static void UpdateGroup(int groupID, string description)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
Group groupToUpdate = context.Set <Group>().Find(groupID);
groupToUpdate.GroupID = groupID;
groupToUpdate.Description = description;
context.SaveChanges();
}
}
public static double GetAvgMark(int studentID)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
ObjectParameter output = new ObjectParameter("avg_mark", typeof(int));
context.getAvgMark(studentID, output);
return((double)output.Value);
}
}
public static List <string> GetParentNames()
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
var parents = from parent in context.parentsList
select parent.Parent_ID + " " +
parent.Last_Name + " " +
parent.First_Name + " " +
parent.Patronymic;
return(parents.ToList());
}
}
public static List<Subject> GetGradeSubjects(int gradeID)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
var subjts = from subj in context.Subjects
from lesson in context.Lessons
where subj.SubjectID == lesson.SubjectID &&
lesson.GradeID == gradeID
select subj;
return subjts.ToList();
}
}
public static List <Subject> GetGradeSubjects(int gradeID)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
var subjts = from subj in context.Subjects
from lesson in context.Lessons
where subj.SubjectID == lesson.SubjectID &&
lesson.GradeID == gradeID
select subj;
return(subjts.ToList());
}
}
public static List<string> GetParentNames()
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
var parents = from parent in context.parentsList
select parent.Parent_ID + " " +
parent.Last_Name + " " +
parent.First_Name + " " +
parent.Patronymic;
return parents.ToList();
}
}
public static int GetStudentCountByGradeName(string gradeName)
{
gradeName = gradeName.Replace(" ", string.Empty).ToUpper();
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
var students = from student in context.Students
where student.GradeID == (from grade in context.Grades
where (grade.GradeNo + grade.Section).Equals(gradeName)
select grade.GradeID).FirstOrDefault()
select student;
return(students.Count());
}
}
public static int AddNewParent(Parent newParent)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
if (!Util.IsValidEmail(newParent.user.Email))
{
throw new ArgumentException("Email string is not a valid email!");
}
context.Parents.Add(newParent);
context.SaveChanges();
return(newParent.ParentID);
}
}
public static ILessonInfo StartNewLesson(string subj_title, string theme, int numberOfHours, string gradeName, string homeTask, int teacherID)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
ObjectParameter output = new ObjectParameter("addedLesson_id", typeof(int));
context.startNewLesson(subj_title, theme, numberOfHours, gradeName, homeTask, teacherID, output);
context.SaveChanges();
int lessonID = (int)output.Value;
var lsn = (from lesson in context.Lessons where lesson.LessonID == lessonID select lesson).FirstOrDefault();
return(new LessonInfo(lsn));
}
}
public static ILessonInfo StartNewLesson(string subj_title, string theme, int numberOfHours, string gradeName, string homeTask, int teacherID)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
ObjectParameter output = new ObjectParameter("addedLesson_id", typeof(int));
context.startNewLesson(subj_title, theme, numberOfHours, gradeName, homeTask, teacherID, output);
context.SaveChanges();
int lessonID = (int)output.Value;
var lsn = (from lesson in context.Lessons where lesson.LessonID==lessonID select lesson).FirstOrDefault();
return new LessonInfo(lsn);
}
}
public static void UpdateTeacher(TeacherInfo t)
{
if (!Util.IsValidEmail(t.Email))
throw new ArgumentException("Email string is not a valid email!");
UsersDAL.UpdateUser(t);
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
Teacher teacherToUpdate = context.Set<Teacher>().Find(t.TeacherID);
teacherToUpdate.Specialization = t.Specialization;
teacherToUpdate.Category = t.Category;
context.SaveChanges();
}
}
public static DataTable BuildDataTable(int teacherID, int gradeNo, string section, string subjectTitle,
DateTime studyYearStart, DateTime studyYearEnd)
{
SchoolJournalEntities context = new SchoolJournalEntities();
DataTable dt = new DataTable();
var records = from j in context.attendanceJournal
where j.TeacherID == teacherID && j.Grade == gradeNo && j.Section==section
&& j.Date <= studyYearEnd && j.Date >= studyYearStart
&& j.Subject.Equals(subjectTitle)
select j;
var dates = (from rec in records select rec.Date).Distinct();
dt.Columns.Add(new DataColumn("ID", typeof(int)));
dt.Columns.Add(new DataColumn("Last Name, First Name", typeof(string)));
dt.PrimaryKey = new DataColumn[] {dt.Columns[0]};
foreach (var date in dates)
{
//string dateString = date.ToString("dd.MM.yy");
string dateString = string.Format("{0}\n{1}", date.ToString("dd.MM.yy"), date.ToShortTimeString());
if (dt.Columns.Contains(dateString))
continue;
DataColumn dc = new DataColumn(dateString, typeof(string));
dt.Columns.Add(dc);
}
foreach (var rec in records)
{
DataRow dr;
string nameSurname = string.Format("{0} {1}", rec.Last_Name, rec.First_Name);
if (!dt.Rows.Contains(rec.StudentID))
{
dr = dt.NewRow();
dr[0] = rec.StudentID;
dr[1] = string.Format("{0} {1}", rec.Last_Name, rec.First_Name);
}
else dr = dt.Rows.Find(rec.StudentID);
string dateString = string.Format("{0}\n{1}", rec.Date.ToString("dd.MM.yy"), rec.Date.ToShortTimeString());
dr[dateString] = rec.Mark == null ? (rec.IsPresent.GetValueOrDefault() ? "." : "a") : rec.Mark.ToString();
if(!dt.Rows.Contains(rec.StudentID))
dt.Rows.Add(dr);
}
context.Dispose();
return dt;
}
public static ParentInfo GetParentInfo(int parentID)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
Parent p = context.Set<Parent>().Find(parentID);
if (p != null)
{
ParentInfo info = new ParentInfo(p);
return info;
}
else throw new ArgumentOutOfRangeException("Parent ID was not found in the DB!");
}
}
public static List <Grade> GetTeacherGrades(int teacherID)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
var grades = from lesson in context.Lessons
from teacher in context.Teachers
from grade in context.Grades
where teacher.lessons.Contains(lesson) &&
teacher.TeacherID == teacherID &&
lesson.GradeID == grade.GradeID
select grade;
return(grades.Distinct().ToList());
}
}
public static void DeleteUser(int userID)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
User userToDelete = (User)context.Set<User>().Find(userID);
if (userToDelete != null)
{
context.Users.Remove(userToDelete);
context.SaveChanges();
}
else throw new ArgumentOutOfRangeException("Invalid userID!");
}
}
public static void UpdateTeacher(TeacherInfo t)
{
if (!Util.IsValidEmail(t.Email))
{
throw new ArgumentException("Email string is not a valid email!");
}
UsersDAL.UpdateUser(t);
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
Teacher teacherToUpdate = context.Set <Teacher>().Find(t.TeacherID);
teacherToUpdate.Specialization = t.Specialization;
teacherToUpdate.Category = t.Category;
context.SaveChanges();
}
}
public static int GetStudentGradeID(int studentID)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
Student s = context.Set <Student>().Find(studentID);
if (s != null)
{
return(s.GradeID);
}
else
{
throw new ArgumentOutOfRangeException("No such studentID=" + studentID);
}
}
}
public static StudentInfo GetStudentInfo(int studentID)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
Student p = context.Set <Student>().Find(studentID);
if (p != null)
{
StudentInfo info = new StudentInfo(p);
return(info);
}
else
{
throw new ArgumentOutOfRangeException("Student ID was not found in the DB!");
}
}
}
public static void DeleteUser(int userID)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
User userToDelete = (User)context.Set <User>().Find(userID);
if (userToDelete != null)
{
context.Users.Remove(userToDelete);
context.SaveChanges();
}
else
{
throw new ArgumentOutOfRangeException("Invalid userID!");
}
}
}
public static List <Subject> GetTeacherSubjects(int teacherID)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
var subjectIDs = from lesson in context.Lessons
from teacher in context.Teachers
where teacher.TeacherID == teacherID &&
teacher.lessons.Contains(lesson)
select lesson.SubjectID;
var subjects = from subject in context.Subjects
where subjectIDs.Contains(subject.SubjectID)
select subject;
return(subjects.ToList());
}
}
//password - not hashed password
public static User GetUserByCredentials(string email, string plainPassword)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
string cryptedPassword = Crypter.Blowfish.Crypt(plainPassword);
var user = (from usr in context.Users
where usr.Email == email
select usr).ToList();
foreach (User usr in user)
{
if (Crypter.CheckPassword(plainPassword, usr.Password))
{
return(usr);
}
}
return(null);
}
}
//returns new UserID
public static int AddNewUser(UserInfo u, System.Data.Common.DbTransaction transaction = null)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
if (transaction != null)
{
context.Database.UseTransaction(transaction);
}
User newUser = u.GetUser();
if (!Util.IsValidEmail(u.Email))
throw new ArgumentException("Email string is not a valid email!");
newUser.Password = Crypter.Blowfish.Crypt(newUser.Password); //newUser entity contains uncrypted password!
context.Users.Add(newUser);
context.SaveChanges();
return newUser.UserID;
}
}
public static void ShiftAllStudentsToNextGrade()
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
using (var dbContextTransaction = context.Database.BeginTransaction())
{
try
{
foreach (Student s in context.Students)
{
//if the current grade is the last one, the student is deleted!
int nextGradeID = GetNextGradeID(s.GradeID);
if (nextGradeID == -1) //delete student from school
{
UsersDAL.DeleteUser(s.StudentID);
}
else
{
s.GradeID = nextGradeID;
}
}
foreach (Group g in context.Groups)
{
int nextGradeID = GetNextGradeID(g.GradeID);
if (nextGradeID == -1) //delete student from school
{
context.Groups.Remove(g);
}
else
{
g.GradeID = nextGradeID;
}
}
context.SaveChanges();
dbContextTransaction.Commit();
}
catch (Exception ex)
{
dbContextTransaction.Rollback();
throw ex;
}
}
}
}
/// <summary>
/// Changes user password
/// </summary>
/// <param name="UserID">UserID</param>
/// <param name="oldPass">old plain text password</param>
/// <param name="newPass">new plain text password</param>
public static void ChangeUserPassword(int UserID, string oldPass, string newPass)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
if (newPass.Length > 0)
{
User userToUpdate = (User)context.Set<User>().Find(UserID);
//if (userToUpdate.Password.Equals(oldPass))
if(Crypter.CheckPassword(oldPass, userToUpdate.Password))
{
//userToUpdate.Password = newPass;
userToUpdate.Password = Crypter.Blowfish.Crypt(newPass);
context.SaveChanges();
}
else throw new ArgumentException("Old password is incorrect!");
}
else throw new ArgumentOutOfRangeException("New password is empty!");
}
}
public static List <HomeTask> GetStudentHomeTaskByDate(int studentID, DateTime intervalStart, DateTime intervalEnd)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
var tasks = (from lesson in context.Lessons
from lstud in context.lesson_student
where lesson.LessonID == lstud.LessonID &&
lstud.StudentID == studentID &&
lesson.Date <= intervalEnd && lesson.Date >= intervalStart
orderby lesson.Date
select new HomeTask
{
Subject = lesson.subject.Title,
Task = lesson.HomeTask,
Date = lesson.Date
}).Distinct();
return(tasks.ToList());
}
}
public static int AddNewUser(UserInfo u, System.Data.Common.DbTransaction transaction = null) //returns new UserID
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
if (transaction != null)
{
context.Database.UseTransaction(transaction);
}
User newUser = u.GetUser();
if (!Util.IsValidEmail(u.Email))
{
throw new ArgumentException("Email string is not a valid email!");
}
newUser.Password = Crypter.Blowfish.Crypt(newUser.Password); //newUser entity contains uncrypted password!
context.Users.Add(newUser);
context.SaveChanges();
return(newUser.UserID);
}
}
public static int AddNewStudent(string firstName, string lastName, string patronymic, DateTime?dateOfBirth,
string email, string password, string phone, string gradeName)
{
string cpassword = Crypter.Blowfish.Crypt(password);
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
if (!Util.IsValidEmail(email))
{
throw new ArgumentException("Email string is not a valid email!");
}
context.addNewStudent(firstName,
lastName,
patronymic,
dateOfBirth,
email,
cpassword,
phone,
gradeName);
context.SaveChanges();
var users = (from user in context.Users
where user.FirstName == firstName &&
user.LastName == lastName &&
user.Patronymic == patronymic &&
user.DateOfBirth == dateOfBirth &&
user.Email == email &&
user.Phone == phone
select user).ToList();
foreach (User user in users)
{
if (user.Password == cpassword)
{
return(user.UserID);
}
}
return(-1);
}
}
public static List <StudentInfo> GetAllStudentsOnTheLesson(int lessonID)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
var studentIDs = from lessonStudent in context.lesson_student
where lessonStudent.LessonID == lessonID
select lessonStudent.StudentID;
var students = from student in context.Students
where studentIDs.Contains(student.StudentID)
orderby student.user.LastName, student.user.FirstName
select student;
List <StudentInfo> stds = new List <StudentInfo>();
foreach (Student s in students)
{
stds.Add(new StudentInfo(s));
}
return(stds);
}
}
public static UserCategory GetUserCategory(int userID)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
bool isParent = (from usr in context.Parents
where usr.ParentID == userID
select usr).Count() > 0;
bool isTeacher = (from usr in context.Teachers
where usr.TeacherID == userID
select usr).Count() > 0;
bool isAdmin = (from usr in context.Admins
where usr.AdminID == userID
select usr).Count() > 0;
bool isStudent = (from usr in context.Students
where usr.StudentID == userID
select usr).Count() > 0;
if (isParent)
{
return(UserCategory.PARENT);
}
else if (isTeacher)
{
return(UserCategory.TEACHER);
}
else if (isAdmin)
{
return(UserCategory.ADMIN);
}
else if (isStudent)
{
return(UserCategory.STUDENT);
}
else
{
return(UserCategory.NONE);
}
}
}
public static void PlaceMark(int lessonID, int studentID, decimal?markValue, bool isPresent = true)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
try
{
//context.placeMark(lessonID, studentID, markValue, isPresent);
int markID = -1;
if (markValue.HasValue) //check for correct value
{
var value = from mark in context.Marks
where mark.Value == markValue
select mark;
if (value.Count() == 0)
{
throw new ArgumentOutOfRangeException(string.Format("Mark value '{0}' is incorrect!", markValue));
}
markID = value.First().MarkID;
}
var less_student = from record in context.lesson_student
where record.LessonID == lessonID &&
record.StudentID == studentID
select record;
if (markID != -1)
{
less_student.First().MarkID = markID;
}
less_student.First().IsPresent = isPresent;
context.SaveChanges();
}
catch (Exception ex)
{
throw new Exception("PlaceMark failed!" + ex.Message + "\n" + ex.InnerException.Message);
}
}
}
public static void UpdateGroup(int groupID, int teacherID, string subject, string gradeSection, string description)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
//get gradeID using gradeSection
int gradeID = (from grade in context.Grades where grade.GradeNo + grade.Section == gradeSection select grade.GradeID).FirstOrDefault();
if (gradeID == 0)
throw new ArgumentOutOfRangeException("Grade name is invalid!");
//get subjectID
int subjID = (from subj in context.Subjects where subj.Title == subject select subj.SubjectID).FirstOrDefault();
if (subjID == 0)
throw new ArgumentOutOfRangeException("Subject title is invalid!");
Group groupToUpdate = context.Set<Group>().Find(groupID);
groupToUpdate.GroupID = groupID;
groupToUpdate.GradeID = gradeID;
groupToUpdate.SubjectID = subjID;
groupToUpdate.TeacherID = teacherID;
groupToUpdate.Description = description;
context.SaveChanges();
}
}
/// <summary>
/// Adds new teacher to the database.
/// </summary>
/// <param name="t"></param>
/// <returns>ID of newly added teacher.</returns>
public static int AddNewTeacher(TeacherInfo t)
{
int userID;
if (!Util.IsValidEmail(t.Email))
throw new ArgumentException("Email string is not a valid email!");
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
using (var dbContextTransaction = context.Database.BeginTransaction())
{
userID = UsersDAL.AddNewUser(t, dbContextTransaction.UnderlyingTransaction);
context.Teachers.Add(new Teacher
{
TeacherID = userID,
Category = t.Category,
Specialization = t.Specialization
});
context.SaveChanges();
dbContextTransaction.Commit();
}
}
return userID;
}
public static int AddNewStudent(string firstName, string lastName, string patronymic, DateTime? dateOfBirth,
string email, string password, string phone, string gradeName)
{
string cpassword = Crypter.Blowfish.Crypt(password);
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
if (!Util.IsValidEmail(email))
throw new ArgumentException("Email string is not a valid email!");
context.addNewStudent(firstName,
lastName,
patronymic,
dateOfBirth,
email,
cpassword,
phone,
gradeName);
context.SaveChanges();
var users = (from user in context.Users
where user.FirstName == firstName &&
user.LastName == lastName &&
user.Patronymic == patronymic &&
user.DateOfBirth == dateOfBirth &&
user.Email == email &&
user.Phone == phone
select user).ToList();
foreach (User user in users)
{
if (user.Password==cpassword)
return user.UserID;
}
return -1;
}
}
static int GetNextGradeID(int currentGradeID)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
Grade currentGrade = (from grade in context.Grades where grade.GradeID == currentGradeID select grade).FirstOrDefault();
string section = currentGrade.Section;
//check if current grade is last one, if yes, return -1
int lastGradeNo = (from grade in context.Grades select grade.GradeNo).Max();
if (currentGrade.GradeNo == lastGradeNo)
return -1;
int nextGradeID = (from grade in context.Grades where grade.GradeNo == currentGrade.GradeNo + 1
&& (grade.Section==currentGrade.Section ||
grade.Section==null)
select grade.GradeID).FirstOrDefault();
if (nextGradeID != 0)
return nextGradeID;
else throw new Exception("GetNextGradeID() failed!");
}
}
public static List<Grade> GetTeacherGrades(int teacherID)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
var grades = from lesson in context.Lessons
from teacher in context.Teachers
from grade in context.Grades
where teacher.lessons.Contains(lesson) &&
teacher.TeacherID == teacherID &&
lesson.GradeID == grade.GradeID
select grade;
return grades.Distinct().ToList();
}
}
public static List<Subject> GetTeacherSubjects(int teacherID)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
var subjectIDs = from lesson in context.Lessons
from teacher in context.Teachers
where teacher.TeacherID == teacherID &&
teacher.lessons.Contains(lesson)
select lesson.SubjectID;
var subjects = from subject in context.Subjects
where subjectIDs.Contains(subject.SubjectID)
select subject;
return subjects.ToList();
}
}
private void toolStripButtonSaveChanges_Click(object sender, EventArgs e)
{
SchoolJournalEntities context = null;
try
{
context = new SchoolJournalEntities();
var teacherIDs = from teacher in context.Teachers select teacher.TeacherID;
foreach (DataGridViewRow r in teachersListDataGridView.Rows)
{
if (r.IsNewRow)
continue;
int teacherID = -1;
try
{
teacherID = int.Parse(teachersListDataGridView["teacherID", r.Index].Value.ToString());
}
catch (ArgumentException)
{
MessageBox.Show(
string.Format("Incorrect teacherID in row {0}!\nMaybe you've created empty rows?",r.Index),
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
continue;
}
if (teacherIDs.Contains(teacherID)) //update record
{
DateTime? dateOfBirth;
try
{
dateOfBirth = DateTime.Parse(teachersListDataGridView["dateOfBirth", r.Index].Value.ToString());
}
catch
{
dateOfBirth = null;
}
TeacherDAL.UpdateTeacher(new TeacherInfo(
teacherID,
teachersListDataGridView["firstName", r.Index].Value.ToString(),
teachersListDataGridView["lastName", r.Index].Value.ToString(),
teachersListDataGridView["patronymic", r.Index].Value.ToString(),
dateOfBirth,
teachersListDataGridView["email", r.Index].Value.ToString(),
teachersListDataGridView["password", r.Index].Value.ToString(),
teachersListDataGridView["phone", r.Index].Value.ToString(),
teachersListDataGridView["specialization", r.Index].Value.ToString(),
teachersListDataGridView["category", r.Index].Value.ToString())
);
}
else //add new record
{
DateTime? dateOfBirth;
try
{
dateOfBirth = DateTime.Parse(teachersListDataGridView["dateOfBirth", r.Index].Value.ToString());
}
catch
{
dateOfBirth = null;
}
teachersListDataGridView["teacherID", r.Index].Value =
TeacherDAL.AddNewTeacher(new TeacherInfo(
-1, //заглушка
teachersListDataGridView["firstName", r.Index].Value.ToString(),
teachersListDataGridView["lastName", r.Index].Value.ToString(),
teachersListDataGridView["patronymic", r.Index].Value.ToString(),
dateOfBirth,
teachersListDataGridView["email", r.Index].Value.ToString(),
teachersListDataGridView["password", r.Index].Value.ToString(),
teachersListDataGridView["phone", r.Index].Value.ToString(),
teachersListDataGridView["specialization", r.Index].Value.ToString(),
teachersListDataGridView["category", r.Index].Value.ToString())
);
}
}
this.toolStripSavedStatus.Text = "Changes saved!";
MessageBox.Show("Changes successfully changed!", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
string innerException = string.Empty;
if (ex.InnerException != null)
innerException = ex.InnerException.Message;
MessageBox.Show(ex.Message+"\n"+innerException, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if(context!=null)
context.Dispose();
}
}
private void toolStripButtonSave_Click(object sender, EventArgs e)
{
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
var IDs = from user in context.Users select user.UserID;
for (int i = 0; i < dataGridStudents.Rows.Count-1; i++)
{
if (!IDs.Contains(int.Parse(dataGridStudents["_StudentID", i].Value.ToString())))
{
try
{
AddNewStudent(i);
}
catch (Exception ex)
{
MessageBox.Show("Error while updating student!\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
try
{
DateTime dateOfJoin;
DateTime? parsedDateOfJoin = null;
bool success = DateTime.TryParse(dataGridStudents["_Date_of_join", i].Value.ToString(), out dateOfJoin);
if (success)
parsedDateOfJoin = dateOfJoin;
//check all fields and save
StudentDAL.UpdateStudent(new StudentInfo(
int.Parse(dataGridStudents["_StudentID", i].Value.ToString()),
dataGridStudents["_First_name", i].Value.ToString(),
dataGridStudents["_Last_name", i].Value.ToString(),
dataGridStudents["_Patronymic", i].Value.ToString(),
null,
dataGridStudents["_Email", i].Value.ToString(),
dataGridStudents["_Password", i].Value.ToString(),
null,
0, //заглушка для gradeID
dataGridStudents["_Grade", i].EditedFormattedValue.ToString(),
parsedDateOfJoin));
}
catch (Exception ex)
{
MessageBox.Show("Error while updating student!\n"+ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
UpdateDataGridViews();
}
}
private void dataGridStudents_UserAddedRow(object sender, DataGridViewRowEventArgs e)
{
int rowIndex = e.Row.Index-1;
using (SchoolJournalEntities context = new SchoolJournalEntities())
{
int lastID = 0;
try
{
lastID = Math.Max(
int.Parse(dataGridStudents["_StudentID", dataGridStudents.Rows.Count - 3].Value.ToString()),
(int)(from u in context.Users select u.UserID).Max());
}
catch
{ }
dataGridStudents["_StudentID", rowIndex].Value = lastID + 1;
}
}