/// <summary>
/// Выводит оценки по сессии студента по заданным предметам
/// </summary>
/// <param name="userID">Идентификатор пользователя - студента</param>
/// <param name="typeSession">Тип сессии</param>
/// <returns>Частичное представление</returns>
public ActionResult SessionResultStudent(int userID, string typeSession)
{
string[] tempEl = Request.Form["subjectsID"].Split(',');
Subject[] subjects = new Subject[tempEl.Length];
string subjectsID = "(";
for (int i = 0; i < tempEl.Length; i++)
{
subjects[i] = new Subject(Convert.ToInt32(tempEl[i]));
subjectsID += subjects[i].ID + (i == tempEl.Length - 1 ? "" : ",");
subjects[i].GetInformationAboutUserFromDB();
}
subjectsID += ")";
Examination[] result = null;
DB.ResponseTable examID = (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 examination.StudentSubject_id = studentsubject.StudentSubject_id where users.User_id = {0} and studentsubject.Subject_id in {1} and examination.Exam_type = '{2}';", userID, subjectsID, typeSession));
if (examID != null)
{
result = new Examination[examID.CountRow];
for (int i = 0; i < result.Length && examID.Read(); i++)
{
result[i] = new Examination(Convert.ToInt32(examID["Examination_id"]));
result[i].GetInformationAboutUserFromDB();
}
}
ViewData["sessionResult"] = result;
return(View());
}
public ActionResult RestorePasswordUser(string idRestorePassword, string password, string replacePassword)
{
DB db = new DB();
DB.ResponseTable user = db.QueryToRespontTable(string.Format("select User_id from restorepassword where RestorePassword_id = '{0}';", idRestorePassword));
if (user == null)
{
return(Redirect("/restorepassword"));
}
Messages messages = new Messages();
if (password == "")
{
messages.Add(Messages.Message.TypeMessage.error, "Вы не ввели новый пароль");
}
else
if (password != replacePassword)
{
messages.Add(Messages.Message.TypeMessage.error, "Пароли должны совпадать");
}
else
{
user.Read();
db.QueryToRespontTable(string.Format("update users set Password = '******' where User_id = {1};" +
"delete from restorepassword where RestorePassword_id = '{2}';", password, user["User_id"], idRestorePassword));
messages.Add(Messages.Message.TypeMessage.good, string.Format("Пароль был востановлен. Авторизируйтесь в системе используя толькочто созданный пароль.<br /> <a href='http://{0}/'>Вход</a>", Request.Url.Authority));
}
ViewData["messages"] = messages;
return(View());
}
/// <summary>
/// Берет всю информацию о кафедре из БД
/// </summary>
/// <returns>true - есть, false - нету</returns>
public bool GetInformationAboutUserFromDB()
{
DB db = new DB();
string query;
if (this.departmentID != -1)
{
query = string.Format("select * from department where Department_id = {0};", this.departmentID);
}
else
if (this.name != "" && this.facultyName != "")
{
query = string.Format("select * from department where Name='{0}' and Faculty_name='{1}';", this.name, this.departmentID);
}
else
{
query = "";
}
DB.ResponseTable department = db.QueryToRespontTable(query);
if (department == null || department.CountRow <= 0)
{
return(false);
}
department.Read();
this.departmentID = (int)department["Department_id"];
this.name = (string)department["Name"];
this.facultyName = (string)department["Faculty_name"];
return(true);
}
/// <summary>
/// Выводит результат сессии по предмету определенной группы
/// </summary>
/// <param name="subjectID">Идентификатор предмета</param>
/// <param name="groupID">Идентификатор группы</param>
/// <param name="typeSession">Тип сессии</param>
/// <returns>Частичное представление</returns>
public ActionResult SessionResultLecturer(int subjectID, int groupID, string typeSession)
{
Examination[] result = null;
DB.ResponseTable examID = (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 = '{2}';", groupID, subjectID, typeSession));
if (examID != null)
{
result = new Examination[examID.CountRow];
for (int i = 0; i < result.Length && examID.Read(); i++)
{
result[i] = new Examination(Convert.ToInt32(examID["Examination_id"]));
result[i].GetInformationAboutUserFromDB();
}
}
ViewData["sessionResult"] = result;
return(View());
}
/// <summary>
/// Выводит студентов которых могут выгнать по результатам сессии
/// </summary>
/// <returns>Частичное представление</returns>
public ActionResult KickedSession()
{
Student[] students = null;
Examination[] examinations = null;
string[] tempEl = Request.Form["subjectsID"].Split(',');
Subject[] subjects = new Subject[tempEl.Length];
string subjectsID = "(";
for (int i = 0; i < tempEl.Length; i++)
{
subjects[i] = new Subject(Convert.ToInt32(tempEl[i]));
subjectsID += subjects[i].ID + (i == tempEl.Length - 1 ? "" : ",");
subjects[i].GetInformationAboutUserFromDB();
}
tempEl = Request.Form["groupsID"].Split(',');
Group[] groups = new Group[tempEl.Length];
string groupsID = "(";
for (int i = 0; i < tempEl.Length; i++)
{
groups[i] = new Group(Convert.ToInt32(tempEl[i]));
groupsID += groups[i].ID + (i == tempEl.Length - 1 ? "" : ",");
groups[i].GetInformationAboutUserFromDB();
}
groupsID += ")";
subjectsID += ")";
DB.ResponseTable examResult = (new DB()).QueryToRespontTable(string.Format("select student.Student_id, examination.Examination_id from groups inner join student inner join studentsubject inner join examination on groups.Group_id = student.Group_id and student.Student_id = studentsubject.Student_id and studentsubject.StudentSubject_id = examination.StudentSubject_id where student.Group_id in {0} and studentsubject.Subject_id in {1} and examination.Exam_type in ('іспит', 'пересдача1', 'пересдача2') and examination.Mark/examination.Min_mark < 0.6 order by groups.Name, student.Surname;", groupsID, subjectsID));
if (examResult != null)
{
students = new Student[examResult.CountRow];
examinations = new Examination[examResult.CountRow];
for (int i = 0; i < examResult.CountRow && examResult.Read(); i++)
{
students[i] = new Student(Convert.ToInt32(examResult["Student_id"]));
students[i].GetInformationAboutUserFromDB();
examinations[i] = new Examination(Convert.ToInt32(examResult["Examination_id"]));
examinations[i].GetInformationAboutUserFromDB();
}
}
ViewData["students"] = students;
ViewData["examinations"] = examinations;
ViewData["subjects"] = subjects;
ViewData["groups"] = groups;
return(View());
}
/// <summary>
/// Возвращает все оценки выставленные преподователем
/// </summary>
/// <param name="userID">Идентификатор пользователя - преподователя</param>
/// <param name="subjectsID">Идентификаторы предметов</param>
/// <param name="groupsID">Идентификаторы групп</param>
/// <param name="studentsID">Идентификаторы студентов</param>
/// <returns>Частичное представление</returns>
public ActionResult ShowMarkLecturer(int userID, string subjectsID, string groupsID, string studentsID)
{
Marks[] result = null;
DB db = new DB();
DB.ResponseTable marksTable = db.QueryToRespontTable(string.Format("select marks.Mark_id from groups inner join student inner join studentsubject inner join marks on groups.Group_id = student.Group_id and student.Student_id = studentsubject.Student_id and marks.StudentSubject_id = studentsubject.StudentSubject_id and groups.Group_id in {0} and student.Student_id in {1} and studentsubject.Subject_id in {2} order by groups.Name;", groupsID, studentsID, subjectsID));
if (marksTable != null && marksTable.CountRow != 0)
{
result = new Marks[marksTable.CountRow];
for (int i = 0, end = result.Length; i < end && marksTable.Read(); i++)
{
result[i] = new Marks(Convert.ToInt32(marksTable["Mark_id"]));
result[i].GetInformationAboutUserFromDB();
}
}
ViewData["showMark"] = result;
return(View());
}
/// <summary>
/// Проверяет сущетвует ли такая кафедра в БД
/// </summary>
/// <returns>true - есть, false - нету</returns>
public bool IsExistsInDB()
{
DB db = new DB();
string query;
if (this.departmentID != -1)
{
query = string.Format("select * from department where Department_id = {0};", this.departmentID);
}
else
if (this.name != "" && this.facultyName != "")
{
query = string.Format("select * from department where Name='{0}' and Faculty_name='{1}';", this.name, this.departmentID);
}
else
{
query = "";
}
DB.ResponseTable department = db.QueryToRespontTable(query);
return(department != null && department.CountRow == 1);
}
/// <summary>
/// Отправляет подтверждение на изменение email пользователю на новую почту email или производит изменение email если такое уже было отправленно
/// </summary>
/// <param name="idChangeEmail">Идентификатор сессии изменения</param>
/// <returns>Страница</returns>
public ActionResult ChangeEmailUser(string idChangeEmail)
{
Users user = Login();
if (user != null)
{
DB db = new DB();
DB.ResponseTable changeEmail = db.QueryToRespontTable(string.Format("select * from changeemail where ChangeEmail_id = '{0}'", idChangeEmail));
if (changeEmail == null || changeEmail.CountRow != 1)
{
return(Redirect("/ChangeEmail"));
}
Messages messages = new Messages();
ViewData["user"] = user;
changeEmail.Read();
if (Validation.VerifyMd5Hash(user.Email + user.Password + changeEmail["New_email"].ToString(), idChangeEmail))
{
db.QueryToRespontTable(string.Format("update users set Email = '{0}' where User_id = {1};delete from changeemail where ChangeEmail_id = '{2}';", changeEmail["New_email"].ToString(), user.ID, idChangeEmail));
messages.Add(Messages.Message.TypeMessage.good, "Ваш email был успешно изменен");
}
else
{
idChangeEmail = Validation.StringToMd5Hash(user.Email + user.Password + changeEmail["New_email"].ToString());
db.QueryToRespontTable(string.Format("update changeemail set ChangeEmail_id = '{0}' where ChangeEmail_id = '{1}'", idChangeEmail, changeEmail["ChangeEmail_id"].ToString()));
Mail.SendMail("smtp.gmail.com",
ConfigurationManager.AppSettings.Get("AIDemail"),
ConfigurationManager.AppSettings.Get("AIDpassword"),
changeEmail["New_email"].ToString(),
"Изменение email",
"Здравствуйте, " + user.Email +
"\n\nВы попросили измениеть свой email на сайте studentUp.com. Если вы этого не делали игнорируйте это сообщений.\n\n" +
"Для подтверждения изменения своего email перейдите по ниже указзанной ссылке:\n\n" +
"http://" + Request.Url.Authority + "/ChangeEmailUser?idChangeEmail=" + idChangeEmail);
messages.Add(Messages.Message.TypeMessage.good, "Вам на новую почту было высланно подтверждение об изменении email");
}
ViewData["messages"] = messages;
return(View());
}
return(Redirect("/"));
}
/// <summary>
/// Отправляет заново письмо по востановлению пароля пользоввтелю
/// </summary>
/// <param name="idRestorePassword">Индефикатор сессии востановления</param>
/// <returns>Сообщение об отправке письма</returns>
public ActionResult ResendEmailRestorePassword(string idRestorePassword)
{
DB.ResponseTable restoreSession = (new DB()).QueryToRespontTable(string.Format("select User_id from restorepassword where RestorePassword_id = \"{0}\";", idRestorePassword));
if ((new DB()).QueryToRespontTable(string.Format("select User_id from restorepassword where RestorePassword_id = \"{0}\";", idRestorePassword)) == null)
{
return(Redirect("/restorepassword"));
}
restoreSession.Read();
Users user = new Users((int)restoreSession["User_id"]);
user.GetInformationAboutUserFromDB();
Mail.SendMail("smtp.gmail.com",
ConfigurationManager.AppSettings.Get("AIDemail"),
ConfigurationManager.AppSettings.Get("AIDpassword"),
user.Email,
"Востановление пароля",
"Здравствуйте, " + user.Email +
"\n\nВы попросили востановить парольна сайте studentUp.com. Если вы этого не делали игнорируйте это сообщений.\n\n" +
"Для востановления пароля перейдите по ниже указзанной ссылке:\n\n" +
"http://" + Request.Url.Authority + "/RestorePasswordUser?idRestorePassword="******"idRestorePassword"] = idRestorePassword;
return(Redirect("/RestorePassword"));
}
/// <summary>
/// Выводит форму для выставления сесии
/// </summary>
/// <param name="subjectID">Идентификатор предмета</param>
/// <param name="groupID">Идентификатор группы</param>
/// <param name="typeSession">Тип сессии</param>
/// <returns>Частичное представление</returns>
public ActionResult ShowSetSession(int subjectID, int groupID, string typeSession)
{
string limit;
Student[] result = null;
switch (typeSession)
{
case "іспит":
limit = "('іспит')";
break;
case "пересдача1":
limit = "('іспит', 'пересдача1')";
break;
case "пересдача2":
limit = "('іспит', 'пересдача1', 'пересдача2')";
break;
default:
limit = "('іспит')";
break;
}
DB.ResponseTable studentsID = (new DB()).QueryToRespontTable(string.Format("select student.Student_id from student inner join studentsubject on student.Student_id = studentsubject.Student_id where student.Group_id = {0} and studentsubject.Subject_id = {1} and student.Student_id not in (select student.Student_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 in {2});", groupID, subjectID, limit));
if (studentsID != null)
{
result = new Student[studentsID.CountRow];
for (int i = 0; i < studentsID.CountRow && studentsID.Read(); i++)
{
result[i] = new Student(Convert.ToInt32(studentsID["Student_id"]));
result[i].GetInformationAboutUserFromDB();
}
}
ViewData["students"] = result;
return(View());
}
/// <summary>
/// Создает файл с атестациями и отправляет его пользователю
/// </summary>
/// <param name="numberAttestation">Номер атестации</param>
/// <returns>Имя файла для загрузки</returns>
public string Attestation(int numberAttestation)
{
string fileName = "attestation" + numberAttestation + "_" + DateTime.Now.ToString("dd_MM_yyyy") + ".xlsx";
DeleteFile(fileName);
DB db = new DB();
DB.ResponseTable groupsID = db.QueryToRespontTable("select groups.Group_id from groups order by groups.Name;");
if (groupsID != null)
{
//Приложение самого Excel
Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
ObjExcel.SheetsInNewWorkbook = groupsID.CountRow;
//Книга.
Microsoft.Office.Interop.Excel.Workbook ObjWorkBook = ObjExcel.Workbooks.Add(System.Reflection.Missing.Value);
for (int i = 0, end = groupsID.CountRow; i < end && groupsID.Read(); i++)
{
Worksheet currentWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)ObjWorkBook.Sheets[i + 1];
Group currentGroup = new Group(Convert.ToInt32(groupsID["Group_id"]));
currentGroup.GetInformationAboutUserFromDB();
currentWorksheet.Name = currentGroup.Name;
Student[] students = currentGroup.GetStudent();
Subject[] subjectsStudents = currentGroup.GetSubjects();
Microsoft.Office.Interop.Excel.Range headCell = currentWorksheet.Range["A1", (char)(65 + subjectsStudents.Length + 1) + "1"];
headCell.Merge();
headCell.ColumnWidth = (35 + 10 * subjectsStudents.Length) / subjectsStudents.Length;
headCell.RowHeight = 75;
headCell.Value2 = string.Format("Національний технічний університет України \"Київський політехнічний інститут\"\n Факультет інформатики та обчислювальної техніки \n АТЕСТАЦІЙНА ВІДОМОСТЬ Група {0} курс{1}", currentGroup.Name, 1);
currentWorksheet.Range["A3"].RowHeight = 15;
currentWorksheet.Range["A4"].RowHeight = 105;
currentWorksheet.Range["A3", "A4"].Merge();
currentWorksheet.Range["A3", "A4"].Value2 = "№\nп/п";
currentWorksheet.Range["A3", "A4"].ColumnWidth = 4;
currentWorksheet.Range["B3", "B4"].Merge();
currentWorksheet.Range["B3", "B4"].Value2 = "Прізвище та ініціали студента";
currentWorksheet.Range["B3", "B4"].ColumnWidth = 25;
for (int j = 0; j < students.Length; j++)
{
currentWorksheet.Cells[j + 5, 1] = j + 1;
currentWorksheet.Cells[j + 5, 2] = students[j].ShortName;
}
int numberOffset = 3, numberExam = subjectsStudents.Length + 2;
for (int j = 0; j < subjectsStudents.Length; j++)
{
if (subjectsStudents[j].TypeExam != Subject.ExamType.exam)
{
currentWorksheet.Range[(char)(65 + numberOffset - 1) + "4"].Value2 = subjectsStudents[j].Name;
Examination[] examinations = subjectsStudents[j].GetAttestation(1, new[] { currentGroup.ID });
if (examinations != null)
{
for (int k = 0; k < examinations.Length; k++)
{
currentWorksheet.Range[(char)(65 + numberOffset - 1) + (5 + k).ToString()].Value2 = examinations[k].Mark < examinations[k].MinMark ? "н/з" : "з";
}
}
numberOffset++;
}
else
{
currentWorksheet.Range[(char)(65 + numberExam - 1) + "4"].Value2 = subjectsStudents[j].Name;
Examination[] examinations = subjectsStudents[j].GetAttestation(numberAttestation, new[] { currentGroup.ID });
if (examinations != null)
{
for (int k = 0; k < examinations.Length; k++)
{
currentWorksheet.Range[(char)(65 + numberExam - 1) + (5 + k).ToString()].Value2 = examinations[k].Mark < examinations[k].MinMark ? "н/з" : "з";
}
}
numberExam--;
}
}
currentWorksheet.Range["C3", (char)(65 + numberOffset - 1) + "3"].Merge();
currentWorksheet.Range["C3", (char)(65 + numberOffset - 1) + "3"].Value2 = "залікові дисципліни";
currentWorksheet.Range[(char)(65 + numberExam + 1) + "3", (char)(65 + subjectsStudents.Length + 1) + "3"].Merge();
currentWorksheet.Range[(char)(65 + numberExam + 1) + "3", (char)(65 + subjectsStudents.Length + 1) + "3"].Value2 = "екзаменац.дисцип.";
}
ObjExcel.DisplayAlerts = false;
ObjWorkBook.SaveAs(Server.MapPath("~/Files") + "\\" + fileName);
ObjExcel.DisplayAlerts = true;
//Закрытие книгу Excel.
ObjWorkBook.Close();
//Закрытие приложения Excel.
ObjExcel.Quit();
return(fileName);
}
return(fileName);
}
/// <summary>
/// Выводит всех студентов соответствующийх запросу
/// </summary>
/// <param name="searchType">Тип запроса</param>
/// <returns>Частичное представление</returns>
public ActionResult SearchStudent(int searchType)
{
Student[] result = null;
string[] tempEl = Request.Form["subjectsID"].Split(',');
Subject[] subjects = new Subject[tempEl.Length];
string subjectsID = "(";
for (int i = 0; i < tempEl.Length; i++)
{
subjects[i] = new Subject(Convert.ToInt32(tempEl[i]));
subjectsID += subjects[i].ID + (i == tempEl.Length - 1 ? "" : ",");
subjects[i].GetInformationAboutUserFromDB();
}
tempEl = Request.Form["groupsID"].Split(',');
Group[] groups = new Group[tempEl.Length];
string groupsID = "(";
for (int i = 0; i < tempEl.Length; i++)
{
groups[i] = new Group(Convert.ToInt32(tempEl[i]));
groupsID += groups[i].ID + (i == tempEl.Length - 1 ? "" : ",");
groups[i].GetInformationAboutUserFromDB();
}
groupsID += ")";
subjectsID += ")";
string query = "";
switch (searchType)
{
case 1:
query = string.Format("(select student.Student_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 in {0} and studentsubject.Subject_id = {1} order by groups.Name, student.Surname limit 10000)", groupsID, subjects[0].ID);
for (int i = 1; i < subjects.Length; i++)
{
query += "union all" + string.Format("(select student.Student_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 in {0} and studentsubject.Subject_id = {1} order by groups.Name, student.Surname limit 10000)", groupsID, subjects[i].ID);
}
break;
case 2:
query = string.Format("select t1.Student_id, t1.Subject_id, t1.Examination_id from (select student.Student_id, student.Surname, studentsubject.Subject_id, examination.Examination_id, groups.Name from groups inner join student inner join studentsubject inner join examination on groups.Group_id = student.Group_id and student.Student_id = studentsubject.Student_id and studentsubject.StudentSubject_id = examination.StudentSubject_id where groups.Group_id in {0} and studentsubject.Subject_id in {1} and examination.Exam_type = 'атестація1' and examination.Mark < examination.Min_mark order by student.Student_id, studentsubject.Subject_id, examination.Examination_id) as t1 inner join (select student.Student_id, student.Surname, studentsubject.Subject_id, examination.Examination_id, groups.Name from groups inner join student inner join studentsubject inner join examination on groups.Group_id = student.Group_id and student.Student_id = studentsubject.Student_id and studentsubject.StudentSubject_id = examination.StudentSubject_id where groups.Group_id in {0} and studentsubject.Subject_id in {1} and examination.Exam_type = 'атестація2' and examination.Mark < examination.Min_mark order by student.Student_id, studentsubject.Subject_id, examination.Examination_id) as t2 on t1.Student_id = t2.Student_id and t1.Subject_id = t2.Subject_id order by t1.Name, t1.Surname;", groupsID, subjectsID);
break;
case 3:
query = string.Format("select student.Student_id from student inner join groups on student.Group_id = groups.Group_id where student.Employment_in_the_department <> '' and groups.Group_id in {0} order by groups.Name, student.Surname;", groupsID);
break;
case 4:
query = string.Format("select student.Student_id from student inner join studentsubject inner join marks on student.Student_id = studentsubject.Student_id and studentsubject.StudentSubject_id = marks.StudentSubject_id where student.Student_id not in (select studentsubject.Student_id from marks inner join studentsubject on marks.StudentSubject_id = studentsubject.StudentSubject_id where marks.Mark <> marks.Max_mark) and student.Group_id in {0};", groupsID);
break;
case 5:
KickedSession();
return(View("KickedSession"));
}
DB.ResponseTable studentsID = (new DB()).QueryToRespontTable(query);
if (searchType == 2 && studentsID != null)
{
Examination[] examinations = new Examination[studentsID.CountRow];
for (int i = 0, end = examinations.Length; i < end && studentsID.Read(); i++)
{
examinations[i] = new Examination(Convert.ToInt32(studentsID["Examination_id"]));
examinations[i].GetInformationAboutUserFromDB();
}
ViewData["examinations"] = examinations;
}
if (studentsID != null)
{
studentsID.GoToStatrTable();
result = new Student[studentsID.CountRow];
for (int i = 0, end = result.Length; i < end && studentsID.Read(); i++)
{
result[i] = new Student(Convert.ToInt32(studentsID["Student_id"]));
result[i].GetInformationAboutUserFromDB();
}
}
ViewData["searchType"] = searchType;
ViewData["students"] = result;
ViewData["subjects"] = subjects;
ViewData["groups"] = groups;
return(View());
}
