/// <summary>
/// Возвращает предметы пользователя
/// </summary>
/// <returns>Масив предметов</returns>
public Subject[] GetMySubjects()
{
Subject[] result = null;
DB db = new DB();
string query;
if (this.userType == UserType.Student)
{
query = string.Format("select studentsubject.Subject_id from studentsubject inner join student inner join users on studentsubject.Student_id = student.Student_id and student.Student_id = users.Student_id and users.User_id = {0};", this.userID);
}
else
{
query = string.Format("select subject.Subject_id from subject inner join lecturer inner join users on subject.Lecturer_id = lecturer.Lecturer_id and lecturer.Lecturer_id = users.Lecturer_id and users.User_id = {0};", this.userID);
}
DB.ResponseTable subjectsID = db.QueryToRespontTable(query);
if (subjectsID != null)
{
result = new Subject[subjectsID.CountRow];
for (int i = 0, end = result.Length; i < end && subjectsID.Read(); i++)
{
result[i] = new Subject(Convert.ToInt32(subjectsID["Subject_id"]));
result[i].GetInformationAboutUserFromDB();
}
}
return(result);
}
/// <summary>
/// Проверяет есть ли такой предмет в БД
/// </summary>
/// <returns>true - есть, false - нету</returns>
public bool IsExistsInDB()
{
DB db = new DB();
DB.ResponseTable subject = db.QueryToRespontTable(string.Format("select * from subject where Subject_id = {0};", this.subjectID));
return(subject != null && subject.CountRow == 1);
}
/// <summary>
/// Производит вход пользователя в систему
/// </summary>
/// <returns>true - залогинен, false - не залогинен</returns>
public bool Login()
{
DB db = new DB();
DB.ResponseTable users = db.QueryToRespontTable(string.Format("select * from student inner join users on student.Student_id = users.Student_id where Email='{0}' and Password='******';", this.Email, this.Password));
if (users != null && users.CountRow == 1)
{
users.Read();
this.userID = Convert.ToInt32(users["User_id"]);
this.accessLevel = Convert.ToInt32(users["Access_level"]);
this.studentID = Convert.ToInt32(users["Student_id"]);
this.groupID = Convert.ToInt32(users["Group_id"]);
this.name = (string)users["Name"];
this.surname = (string)users["Surname"];
this.secondName = (string)users["Second_name"];
this.currentSemester = Convert.ToInt32(users["Semester"]);
this.address = (string)users["Address"];
this.telephone = (string)users["Telephone"];
this.recordBook = (string)users["Record_book"];
this.typeOfEducetion = Student.ConverStringToEnum((string)users["Type_of_education"]);
this.contactsParents = (string)users["Contacts_parents"];
this.employmentInTheDepartment = (string)users["Employment_in_the_department"];
return(true);
}
return(false);
}
/// <summary>
/// Возвращает оценки пользователя по определенному предмету
/// </summary>
/// <param name="subjectID">Идентификатор предмета</param>
/// <returns>Масив оценок</returns>
public Marks[] GetMyMarks(int subjectID)
{
Marks[] result = null;
DB db = new DB();
string query;
if (this.userType == UserType.Student)
{
query = string.Format("select marks.Mark_id from marks inner join studentsubject inner join student inner join users on marks.StudentSubject_id = studentsubject.StudentSubject_id and studentsubject.Student_id = student.Student_id and student.Student_id = users.Student_id and users.User_id = {0} and studentsubject.Subject_id = {1} order by marks.Date;", this.userID, subjectID);
}
else
{
query = string.Format("select marks.Mark_id from marks inner join studentsubject inner join subject inner join lecturer inner join users on marks.StudentSubject_id = studentsubject.StudentSubject_id and studentsubject.Subject_id = subject.Subject_id and subject.Lecturer_id = lecturer.Lecturer_id and lecturer.Lecturer_id = users.Lecturer_id and users.User_id = {0} and studentsubject.Subject_id = {1} order by marks.Date;", this.userID, subjectID);
}
DB.ResponseTable markID = db.QueryToRespontTable(query);
if (markID != null && markID.CountRow > 0)
{
result = new Marks[markID.CountRow];
for (int i = 0, end = result.Length; i < end && markID.Read(); i++)
{
result[i] = new Marks(Convert.ToInt32(markID["Mark_id"]));
result[i].GetInformationAboutUserFromDB();
}
}
return(result);
}
/// <summary>
/// Проверяет была ли выставлена такая оценка
/// </summary>
/// <returns>true - есть, false - нету</returns>
public bool IsExistsInDB()
{
DB db = new DB();
DB.ResponseTable marks = db.QueryToRespontTable(string.Format("select * from marks where Mark_id = {0}", this.markID));
return(marks != null && marks.CountRow == 1);
}
/// <summary>
/// Возвращает все группы которым преподает текущий преподователь
/// </summary>
/// <returns>Масив групп</returns>
public Group[] GetMyGroups()
{
this.GetInformationAboutUserFromDB();
Group[] result = null;
DB db = new DB();
Subject[] subjects = this.GetMySubjects();
string subjectsID = "(";
for (int i = 0, end = subjects.Length; i < end; i++)
{
subjectsID += subjects[i].ID + (i != end - 1 ? "," : "");
}
subjectsID += ")";
DB.ResponseTable groupID = db.QueryToRespontTable(string.Format("select groups.Group_id from groups inner join student inner join studentsubject on groups.Group_id = student.Group_id and student.Student_id = studentsubject.Student_id and studentsubject.Subject_id in {0} Group by groups.Group_id order by groups.Name;", subjectsID));
if (groupID != null)
{
result = new Group[groupID.CountRow];
for (int i = 0, end = result.Length; i < end && groupID.Read(); i++)
{
result[i] = new Group(Convert.ToInt32(groupID["Group_id"]));
result[i].GetInformationAboutUserFromDB();
}
}
return(result);
}
/// <summary>
/// Проверяет есть ли такой результат в БД
/// </summary>
/// <returns>true - есть, false - нету</returns>
public bool IsExistsInDB()
{
DB db = new DB();
DB.ResponseTable examination = db.QueryToRespontTable(string.Format("select * from examination where examination.Examination_id = {0};", this.examinationID));
return(examination != null && examination.CountRow == 1);
}
/// <summary>
/// Возвращает атестации по текущиму предмету
/// </summary>
/// <param name="numberAttestation">Номер атестации</param>
/// <param name="groupsID">Масив идентификаторов груп</param>
/// <returns>Масив результктов атестации</returns>
public Examination[] GetAttestation(int numberAttestation, int[] groupsID)
{
Examination[] result = null;
string groups = "(";
for (int i = 0, end = groupsID.Length; i < end; i++)
{
groups += groupsID[i] + (i != end - 1 ? "," : "");
}
if (groups.Length == 1)
{
groups += -1;
}
groups += ")";
DB db = new DB();
DB.ResponseTable attestationID = db.QueryToRespontTable(string.Format("select examination.Examination_id from examination inner join studentsubject inner join student inner join groups on examination.StudentSubject_id = studentsubject.StudentSubject_id and student.Student_id = studentsubject.Student_id and student.Group_id = groups.Group_id where studentsubject.Subject_id = {0} and examination.Exam_type = '{1}' and student.Group_id in {2} order by groups.Name, student.Surname;", this.subjectID, "атестація" + numberAttestation, groups));
if (attestationID != null)
{
result = new Examination[attestationID.CountRow];
for (int i = 0, end = result.Length; i < end && attestationID.Read(); i++)
{
result[i] = new Examination(Convert.ToInt32(attestationID["Examination_id"]));
result[i].GetInformationAboutUserFromDB();
}
}
return(result);
}
/// <summary>
/// Проверяет существует ли такая группы
/// </summary>
/// <returns>true - существует, flase - не существует</returns>
public bool IsExistsInDB()
{
DB db = new DB();
DB.ResponseTable group = null;
if (this.groupID != -1)
{
group = db.QueryToRespontTable(string.Format("select * from groups where Group_id = {0};", this.groupID));
}
return(group != null && group.CountRow == 1);
}
/// <summary>
/// Добовляет оценку в БД
/// </summary>
/// <param name="studentID">Идентификатор студента</param>
/// <param name="subjectID">Идентификатор предмета</param>
/// <param name="mark">Сама оценка</param>
/// <param name="bonusMark">Бонусные балы</param>
/// <param name="maxMark">Максимальная оценка</param>
/// <param name="typeMark">Тип оценки</param>
/// <param name="date">Дата выставления оценки</param>
/// <returns>Новыя оценка</returns>
public static Marks AddMark(int studentID, int subjectID, int mark, int bonusMark, int maxMark, Marks.TypeMarks typeMark, DateTime date)
{
DB db = new DB();
db.QueryToRespontTable(string.Format("insert into marks(Date, Mark, Bonus_mark, Max_mark, StudentSubject_id, Type_marks) value ('{0}', {1}, {2}, {3}, (select StudentSubject_id from studentsubject where Student_id = {4} and Subject_id = {5}), '{6}');", date.ToString("yyyy-MM-dd"), mark, bonusMark, maxMark, studentID, subjectID, Marks.GetEnumDescription(typeMark)));
DB.ResponseTable markID = db.QueryToRespontTable("select LAST_INSERT_ID() as id;");
markID.Read();
Marks currentMark = new Marks(Convert.ToInt32(markID["id"]));
currentMark.GetInformationAboutUserFromDB();
return(currentMark);
}
/// <summary>
/// Добавляет группу
/// </summary>
/// <param name="nameGroup">Код группы</param>
/// <param name="department">Кафедра группы</param>
/// <returns>Новая группа</returns>
public static Group AddGroup(string nameGroup, int department)
{
DB db = new DB();
db.QueryToRespontTable(string.Format("insert into groups(Department_id, Name) value ({0}, '{1}');", department, nameGroup));
DB.ResponseTable idGoup = db.QueryToRespontTable("select LAST_INSERT_ID() as id;");
idGoup.Read();
Group group = new Group(Convert.ToInt32(idGoup["id"]));
group.GetInformationAboutUserFromDB();
return(group);
}
/// <summary>
/// Добовляет нового преподователя
/// </summary>
/// <param name="name">Имя преподователя</param>
/// <param name="surname">Фамилия преподователя</param>
/// <param name="secondName">Отчество преподователя</param>
/// <param name="email">Email преподователя</param>
/// <param name="position">Должность преподователя</param>
/// <param name="telephone">Телефон преподователя</param>
/// <param name="department">Кафедра преподователя, где он будет работать</param>
/// <param name="admin">Будет ли преподователь администратором</param>
/// <returns>Новый преподователь</returns>
public static Lecturer AddLecturer(string name, string surname, string secondName, string email, string position, string telephone, int department, bool admin)
{
DB db = new DB();
db.QueryToRespontTable(
string.Format(
"insert into lecturer(Department_id, Name, Surname, Second_name, Position, Telephone) value({0}, '{1}', '{2}', '{3}', '{4}', '{5}');", department, name, surname, secondName, position, telephone));
DB.ResponseTable userIdTable = db.QueryToRespontTable("select LAST_INSERT_ID() as id;");
userIdTable.Read();
Lecturer user = new Lecturer(Users.AddLecturerUsers(email, admin ? 2 : 1, Convert.ToInt32(userIdTable["id"])));
user.GetInformationAboutUserFromDB();
return(user);
}
/// <summary>
/// Добавление студента
/// </summary>
/// <param name="name">Имя студента</param>
/// <param name="surname">Фамилия студента</param>
/// <param name="secondName">Отчество студента</param>
/// <param name="email">Email студента</param>
/// <param name="telephone">Телефон студента</param>
/// <param name="group">Группа студента</param>
/// <param name="currentSemestr">Семестр на котором учится студент</param>
/// <param name="address">Адрес студента</param>
/// <param name="recordBook">Код зачетной книги студента</param>
/// <param name="typeOfEducation">Тип обралования студента ('дена','заочна')</param>
/// <param name="contactsParents">Контакты родителей студента</param>
/// <param name="employmentInTheDepartment">Чем занимается студент на кафедре</param>
/// <param name="admin">Является ли студент администраторм системы</param>
/// <returns>Новый студент</returns>
public static Student AddStudent(string surname, string name, string secondName, string email, string telephone,
int group, int currentSemestr, string address, string recordBook, Student.TypeOfEducation typeOfEducation, string contactsParents,
string employmentInTheDepartment, bool admin)
{
DB db = new DB();
db.QueryToRespontTable(string.Format("insert into student(Group_id, Surname, Name, Second_name, Semester, Address, Telephone, Record_book, Type_of_education, Сontacts_parents, Employment_in_the_department) values ({0}, '{1}', '{2}', '{3}', {4}, '{5}', '{6}', '{7}', '{8}', '{9}', '{10}');", group, surname, name, secondName, currentSemestr, address, telephone, recordBook, Student.GetEnumDescription(typeOfEducation), contactsParents, employmentInTheDepartment));
DB.ResponseTable userIdTable = db.QueryToRespontTable("select LAST_INSERT_ID() as id;");
userIdTable.Read();
Student user = new Student(Users.AddStudentUsers(email, admin ? 2 : 0, Convert.ToInt32(userIdTable["id"])));
user.GetInformationAboutUserFromDB();
return(user);
}
/// <summary>
/// Возвращает список предметов группы
/// </summary>
/// <returns>масив предметов</returns>
public Subject[] GetSubjects()
{
Subject[] result = null;
DB.ResponseTable subjectsID = (new DB()).QueryToRespontTable(string.Format("select studentsubject.Subject_id from groups inner join student inner join studentsubject on groups.Group_id = student.Group_id and student.Student_id = studentsubject.Student_id where groups.Group_id = {0} group by studentsubject.Subject_id;", this.ID));
if (subjectsID != null)
{
result = new Subject[subjectsID.CountRow];
for (int i = 0, end = result.Length; i < end && subjectsID.Read(); i++)
{
result[i] = new Subject(Convert.ToInt32(subjectsID["Subject_id"]));
result[i].GetInformationAboutUserFromDB();
}
}
return(result);
}
/// <summary>
/// Возвращает результаты сессии по определенной группе
/// </summary>
/// <param name="groupID">дентификатор группы</param>
/// <returns>Масив оценок</returns>
public Examination[] GetSession(int groupID)
{
Examination[] result = null;
DB.ResponseTable attestationID = (new DB()).QueryToRespontTable(string.Format("select examination.Examination_id from student inner join studentsubject inner join examination on student.Student_id = studentsubject.Student_id and studentsubject.StudentSubject_id = examination.StudentSubject_id where student.Group_id = {0} and studentsubject.Subject_id = {1} and examination.Exam_type = 'іспит';", groupID, this.subjectID));
if (attestationID != null)
{
result = new Examination[attestationID.CountRow];
for (int i = 0, end = result.Length; i < end && attestationID.Read(); i++)
{
result[i] = new Examination(Convert.ToInt32(attestationID["Examination_id"]));
result[i].GetInformationAboutUserFromDB();
}
}
return(result);
}
/// <summary>
/// Берет информацию о предмете из БД
/// </summary>
/// <returns>true - есть, false - нету</returns>
public bool GetInformationAboutUserFromDB()
{
DB db = new DB();
DB.ResponseTable subject = db.QueryToRespontTable(string.Format("select * from subject where Subject_id = {0};", this.subjectID));
if (subject != null && subject.CountRow > 0)
{
subject.Read();
this.subjectID = Convert.ToInt32(subject["Subject_id"]);
this.name = (string)subject["Name"];
this.lecturerID = Convert.ToInt32(subject["Lecturer_id"]);
this.typeExam = Subject.ConverStringToEnum((string)subject["Exam_type"]);
return(true);
}
return(false);
}
/// <summary>
/// Добовляет предмет в БД
/// </summary>
/// <param name="name">Название предмета</param>
/// <param name="lecturerID">Идентификатор преподователя ведущий этот предмет</param>
/// <param name="examType">Тип сдачи предмета</param>
/// <returns>Новый предмет</returns>
public static Subject AddSubject(string name, int lecturerID, string examType)
{
DB db = new DB();
if (Subject.ConverStringToEnum(examType) == ExamType.nothing && examType != "-")
{
return(null);
}
db.QueryToRespontTable(string.Format("insert into subject(Lecturer_id, Name, Exam_type) value ({0}, '{1}', '{2}');", lecturerID, name, examType));
DB.ResponseTable idSubject = db.QueryToRespontTable("select LAST_INSERT_ID() as id;");
idSubject.Read();
Subject subject = new Subject(Convert.ToInt32(idSubject["id"]));
subject.GetInformationAboutUserFromDB();
return(subject);
}
/// <summary>
/// Считывает информацию об студенте из БД
/// </summary>
/// <returns></returns>
public override bool GetInformationAboutUserFromDB()
{
DB db = new DB();
string query;
if (this.email != string.Empty && this.passwodr != string.Empty)
{
query = string.Format("select * from student inner join users on student.Student_id = users.Student_id where users.Email='{0}' and users.Password='******';", this.Email, this.Password);
}
else
if (this.userID != -1)
{
query = string.Format("select * from student inner join Users on student.Student_id = users.Student_id where users.User_id = {0};", this.userID);
}
else
if (this.studentID != -1)
{
query = string.Format("select * from student inner join users on student.Student_id = users.Student_id where student.Student_id = {0};", this.studentID);
}
else
{
query = "";
}
DB.ResponseTable users = db.QueryToRespontTable(query);
if (users != null && users.CountRow == 1)
{
users.Read();
this.userID = Convert.ToInt32(users["User_id"]);
this.email = (string)users["Email"];
this.passwodr = (string)users["Password"];
this.accessLevel = Convert.ToInt32(users["Access_level"]);
this.studentID = Convert.ToInt32(users["Student_id"]);
this.groupID = Convert.ToInt32(users["Group_id"]);
this.name = (string)users["Name"];
this.surname = (string)users["Surname"];
this.secondName = (string)users["Second_name"];
this.currentSemester = Convert.ToInt32(users["Semester"]);
this.address = (string)users["Address"];
this.telephone = (string)users["Telephone"];
this.recordBook = (string)users["Record_book"];
this.typeOfEducetion = Student.ConverStringToEnum((string)users["Type_of_education"]);
this.contactsParents = (string)users["Сontacts_parents"];
this.employmentInTheDepartment = (string)users["Employment_in_the_department"];
return(true);
}
return(false);
}
/// <summary>
/// Возвращает список студентов которые находятся в этой группе
/// </summary>
/// <returns>Масиив студентов</returns>
public Student[] GetStudent()
{
Student[] result = null;
DB db = new DB();
DB.ResponseTable studentID = db.QueryToRespontTable(string.Format("select Student_id from student where Group_id = {0};", groupID));
if (studentID != null)
{
result = new Student[studentID.CountRow];
for (int i = 0, end = result.Length; i < end && studentID.Read(); i++)
{
result[i] = new Student(Convert.ToInt32(studentID["Student_id"]));
result[i].GetInformationAboutUserFromDB();
}
}
return(result);
}
/// <summary>
/// Возвращает всех студентов группы которые изучают этот предмет
/// </summary>
/// <param name="groupID">Идентификатор группы</param>
/// <returns>Масив студентов</returns>
public Student[] GetStudentsFromGroup(int groupID)
{
Student[] result = null;
DB db = new DB();
DB.ResponseTable studentID = db.QueryToRespontTable(string.Format("select student.Student_id from groups inner join student inner join studentSubject inner join subject on groups.Group_id = student.Group_id and groups.Group_id = {0} and student.Student_id = studentSubject.Student_id and studentSubject.Subject_id = subject.Subject_id and subject.Subject_id = {1};", groupID, this.subjectID));
if (studentID != null)
{
result = new Student[studentID.CountRow];
for (int i = 0, end = result.Length; i < end && studentID.Read(); i++)
{
result[i] = new Student(Convert.ToInt32(studentID["Student_id"]));
result[i].GetInformationAboutUserFromDB();
}
}
return(result);
}
/// <summary>
/// Возвращает все группы студентов которые изучают этот предмет
/// </summary>
/// <returns>Масив групп</returns>
public Group[] GetGroups()
{
Group[] result = null;
DB db = new DB();
DB.ResponseTable groupID = db.QueryToRespontTable(string.Format("select groups.Group_id from groups inner join student inner join studentsubject inner join subject on groups.Group_id = student.Group_id and student.Student_id = studentsubject.Student_id and studentsubject.Subject_id = subject.Subject_id and subject.Subject_id = {0} Group by groups.Group_id order by groups.Name;", this.subjectID));
if (groupID != null)
{
result = new Group[groupID.CountRow];
for (int i = 0, end = result.Length; i < end && groupID.Read(); i++)
{
result[i] = new Group(Convert.ToInt32(groupID["Group_id"]));
result[i].GetInformationAboutUserFromDB();
}
}
return(result);
}
/// <summary>
/// Берет информацию о результате из БД
/// </summary>
/// <returns>true - есть, false - нету</returns>
public bool GetInformationAboutUserFromDB()
{
DB db = new DB();
DB.ResponseTable examination = db.QueryToRespontTable(string.Format("select * from examination inner join studentsubject on examination.StudentSubject_id = studentsubject.StudentSubject_id where examination.Examination_id = {0};", this.examinationID));
if (examination != null && examination.CountRow > 0)
{
examination.Read();
this.examinationID = Convert.ToInt32(examination["Examination_id"]);
this.studentID = Convert.ToInt32(examination["Student_id"]);
this.subjectID = Convert.ToInt32(examination["Subject_id"]);
this.mark = Convert.ToInt32(examination["Mark"]);
this.minMark = Convert.ToInt32(examination["Min_mark"]);
this.examType = Examination.ConverStringToEnum(examination["Exam_type"].ToString());
return(true);
}
return(false);
}
/// <summary>
/// Считывает информацию об преподователе из БД
/// </summary>
/// <returns>Возвращает всю информацию о преподователе</returns>
public override bool GetInformationAboutUserFromDB()
{
DB db = new DB();
string query;
if (this.email != string.Empty && this.passwodr != string.Empty)
{
query = string.Format("select * from lecturer inner join users on lecturer.Lecturer_id = users.Lecturer_id where users.Email='{0}' and users.Password='******';", this.Email, this.Password);
}
else
if (this.userID != -1)
{
query = string.Format("select * from lecturer inner join users on lecturer.Lecturer_id = users.Lecturer_id where users.User_id = {0};", this.userID);
}
else
if (this.lecturerID != -1)
{
query = string.Format("select * from lecturer inner join users on lecturer.Lecturer_id = users.Lecturer_id where lecturer.Lecturer_id = {0};", this.lecturerID);
}
else
{
query = "";
}
DB.ResponseTable users = db.QueryToRespontTable(query);
if (users != null && users.CountRow == 1)
{
users.Read();
this.userID = (int)users["User_id"];
this.email = (string)users["Email"];
this.passwodr = (string)users["Password"];
this.accessLevel = (int)users["Access_level"];
this.lecturerID = (int)users["Lecturer_id"];
this.departmentID = (int)users["Department_id"];
this.name = (string)users["Name"];
this.surname = (string)users["Surname"];
this.secondName = (string)users["Second_name"];
this.telephone = (string)users["Telephone"];
this.position = (string)users["Position"];
this.userType = UserType.Lecturer;
return(true);
}
return(false);
}
/// <summary>
/// Берет всю информацию об оценке из БД
/// </summary>
/// <returns>true - есть, false - нету</returns>
public bool GetInformationAboutUserFromDB()
{
DB db = new DB();
DB.ResponseTable marks = db.QueryToRespontTable(string.Format("select * from marks inner join studentsubject on marks.StudentSubject_id = studentsubject.StudentSubject_id and marks.Mark_id = {0};", this.markID));
if (marks != null && marks.CountRow == 1)
{
marks.Read();
this.studentID = Convert.ToInt32(marks["Student_id"]);
this.subjectID = Convert.ToInt32(marks["Subject_id"]);
this.date = Convert.ToDateTime(marks["Date"]);
this.mark = Convert.ToInt32(marks["Mark"]);
this.bonusMark = Convert.ToInt32(marks["Bonus_mark"]);
this.maxMark = Convert.ToInt32(marks["Max_mark"]);
this.typeMark = Marks.ConverStringToEnum((string)marks["Type_marks"]);
return(true);
}
return(false);
}
/// <summary>
/// Берет информацию о группе из БД
/// </summary>
/// <returns>true - группа существует, flase - группы не существует</returns>
public bool GetInformationAboutUserFromDB()
{
DB db = new DB();
DB.ResponseTable group = null;
if (this.groupID != -1)
{
group = db.QueryToRespontTable(string.Format("select * from groups where Group_id = {0};", this.groupID));
}
if (group == null || group.CountRow <= 0)
{
return(false);
}
group.Read();
this.departmentID = Convert.ToInt32(group["Department_id"]);
this.name = (string)group["Name"];
this.emailGroup = (string)group["Email_group"];
this.elderID = Convert.ToInt32(group["Elder_id"]);
return(true);
}
/// <summary>
/// Производи вход пользоваетля в систему
/// </summary>
/// <returns>Зарегистрирован ли пользователь в системе</returns>
public bool Login()
{
DB db = new DB();
DB.ResponseTable users = db.QueryToRespontTable(string.Format("select * from lecturer inner join users on lecturer.Lecturer_id = users.Lecturer_id where Email='{0}' and Password='******';", this.Email, this.Password));
if (users != null && users.CountRow == 1)
{
users.Read();
this.userID = (int)users["User_id"];
this.accessLevel = (int)users["Access_level"];
this.lecturerID = (int)users["Lecturer_id"];
this.departmentID = (int)users["Department_id"];
this.name = (string)users["Name"];
this.surname = (string)users["Surname"];
this.secondName = (string)users["Second_name"];
this.position = (string)users["Position"];
this.telephone = (string)users["Telephone"];
return(true);
}
return(false);
}
/// <summary>
/// Проверяет заригестрирован ли пользователь в системе
/// Проверка происходит в трех случаях:
/// 1. Если задан идентификатор пользователя
/// 2. Если задан email пользователя
/// 3. Если задан и email и пароль пользователя
/// </summary>
/// <returns>
/// true - Зарегистрирован
/// flase - Не зарегистрирован
/// </returns>
public bool IsExistsInDB()
{
DB db = new DB();
DB.ResponseTable users = null;
if (this.email != string.Empty && this.passwodr != string.Empty)
{
users = db.QueryToRespontTable(string.Format("select * from users where Email='{0}' and Password='******';", this.Email, this.Password));
}
else
if (this.userID != -1)
{
users = db.QueryToRespontTable(string.Format("select * from users where User_id='{0}';", this.userID));
}
else
if (this.email != string.Empty)
{
users = db.QueryToRespontTable(string.Format("select * from users where Email='{0}';", this.email));
}
return(users != null && users.CountRow == 1);
}
/// <summary>
/// Возвращает все атестации студента по заданным предметам
/// </summary>
/// <param name="numberAttestation">Номер атестации</param>
/// <param name="subjectsID">Масив идентификаторов предметов</param>
/// <returns>Масив атестаций предметов</returns>
public Examination[] GetAttestation(int numberAttestation, int[] subjectsID)
{
Examination[] result = null;
string subjects = "(";
for (int i = 0; i < subjectsID.Length; i++)
{
subjects += subjectsID[i] + (i != subjectsID.Length - 1 ? "," : "");
}
subjects += ")";
DB.ResponseTable attestationID = (new DB()).QueryToRespontTable(string.Format("select examination.Examination_id from users inner join student inner join studentsubject inner join examination on users.Student_id = student.Student_id and student.Student_id = studentsubject.Student_id and studentsubject.StudentSubject_id = examination.StudentSubject_id and studentsubject.Subject_id in {0} and users.User_id = {1} and examination.Exam_type = '{2}' order by studentsubject.Subject_id;", subjects, this.userID, "атестація" + numberAttestation));
if (attestationID != null)
{
result = new Examination[attestationID.CountRow];
for (int i = 0, end = result.Length; i < end && attestationID.Read(); i++)
{
result[i] = new Examination(Convert.ToInt32(attestationID["Examination_id"]));
result[i].GetInformationAboutUserFromDB();
}
}
return(result);
}
/// <summary>
/// Получает оновную информацию про пользователя из БД
/// </summary>
/// <returns>Если пользователь не зарегистрирован возращает false, иначе true</returns>
public virtual bool GetInformationAboutUserFromDB()
{
if (!this.IsExistsInDB())
{
return(false);
}
DB db = new DB();
DB.ResponseTable users = null;
if (this.email != string.Empty && this.passwodr != string.Empty)
{
users = db.QueryToRespontTable(string.Format("select * from users where Email='{0}' and Password='******';", this.Email, this.Password));
}
else
if (this.userID != -1)
{
users = db.QueryToRespontTable(string.Format("select * from users where User_id='{0}';", this.userID));
}
else
if (this.email != "")
{
users = db.QueryToRespontTable(string.Format("select * from users where Email='{0}';", this.Email));
}
if (users == null || users.CountRow <= 0)
{
return(false);
}
users.Read();
this.userID = (int)users["User_id"];
this.studentID = users["Student_id"] == null ? -1 : Convert.ToInt32(users["Student_id"]);
this.lecturerID = users["Lecturer_id"] == null ? -1 : Convert.ToInt32(users["Lecturer_id"]);
this.email = (string)users["Email"];
this.passwodr = (string)users["Password"];
this.accessLevel = (int)users["Access_level"];
this.userType = users["Student_id"] == null ? UserType.Lecturer : UserType.Student;
return(true);
}
/// <summary>
/// Возвращает всех студентов тех групп которым перподает текущий перподаватель
/// </summary>
/// <param name="groups">Масив идентификаторов группы</param>
/// <returns>Масив студентов</returns>
public Student[] GetMyStudents(Group[] groups)
{
this.GetInformationAboutUserFromDB();
Student[] result = null;
DB db = new DB();
string groupssID = "(";
for (int i = 0, end = groups.Length; i < end; i++)
{
groupssID += groups[i].ID + (i != end - 1 ? "," : "");
}
groupssID += ")";
DB.ResponseTable studentID = db.QueryToRespontTable(string.Format("select student.Student_id from student inner join groups on student.Group_id = groups.Group_id and student.Group_id in {0} order by groups.Name;", groupssID));
if (studentID != null)
{
result = new Student[studentID.CountRow];
for (int i = 0, end = result.Length; i < end && studentID.Read(); i++)
{
result[i] = new Student(Convert.ToInt32(studentID["Student_id"]));
result[i].GetInformationAboutUserFromDB();
}
}
return(result);
}