private string NewStudentVisitsDBRequest(StudentVisit studentVisit)
{
string request;
var date = studentVisit.DateTime.ToString("yyyy-MM-dd");
var selectIdStudent = $"select idstudent from vcsdb.students where firstname = '{studentVisit.FirstName}' and lastname = '{studentVisit.LastName}' and pastname = '{studentVisit.PastName}'";
var selectIdSubject = $"select idsubject from vcsdb.subjects where subject = '{studentVisit.Subject}'";
var selectIdClassroom = $"select idclassroom from vcsdb.classrooms where classroom = '{studentVisit.Classroom}'";
var selectIdStudentGroup = $"select idstudentgroup from vcsdb.studentgroups where studentgroup = '{studentVisit.Group}'";
var selectIdClass = $"select idclass from vcsdb.shedule where idsubject = ({selectIdSubject}) and idclassroom = ({selectIdClassroom}) and idstudentgroup = ({selectIdStudentGroup})";
request = $"insert into vcsdb.visits (idstudent, date, idclass, presense) " +
$"value(({selectIdStudent})" +
$", '{date}'" +
$", ({selectIdClass})" +
$", '0');";
//request = $"UPDATE vcsdb.visits " +
// $"SET firstname = '{studentVisit.FirstName}' " +
// $", lastname = '{studentVisit.LastName}' " +
// $", pastname = '{studentVisit.PastName}' " +
// $", date = '{date}' " +
// $", classroom = '{studentVisit.Classroom}' " +
// $", studentgroup = '{studentVisit.Group}' " +
// $", subject = '{studentVisit.Subject}' " +
// $", presense = '0' " +
// $"WHERE idpresense = '{studentVisit.Id}';";
return(request);
}
public IActionResult EditStudentVisitView(StudentVisit model, int subjectId, int groupId)
{
var visit = _db.StudentVisit.Find(model.Id);
visit.TypeVisit = model.TypeVisit;
_db.SaveChanges();
var visits = _db.GroupVisit
.Include(x => x.StudentVisits)
.ThenInclude(x => x.Student)
.Include(x => x.Subject)
.Include(x => x.Group)
.Include(x => x.ThemeVisits)
.Where(x => ((x.Subject.Id == subjectId) &&
(x.StudentVisits.FirstOrDefault().Student.Group.Id == groupId)))
.ToList();
var output = new GroupVisitView()
{
GroupId = groupId,
SubjectId = subjectId,
Visits = visits,
Filter = new Filter()
{
From = visits.Min(x => x.Date), Before = visits.Max(x => x.Date)
}
};
return(View("VisitTableView", output));
}
/// <summary>
/// Метод, который устанавливает значение присутствия в новое, которое нужно ему задавать в StudentVisit.Presense по id
/// </summary>
/// <param name="studentVisit"></param>
/// <returns></returns>
public bool SetStudentVisit(StudentVisit studentVisit)
{
bool success = true;
conn.Open();
try
{
var cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = SetStudentVisitPresenseDBRequest(studentVisit);
var qr = cmd.ExecuteNonQuery();
if (qr < 1)
{
throw new Exception("Не удалось отметить посещение");
}
}
catch (Exception)
{
success = false;
throw;
}
finally
{
conn.Close();
conn.Dispose();
}
return(success);
}
/// <summary>
/// Добавление посещений
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public IActionResult AddVisit(VisitViewModel model)
{
var date = model.Date;
var saved = HttpContext.Session.GetString("SessionStorageThemes");
var storage = new List <SessionStorageThemes>();
if (storage != null)
{
storage = JsonConvert.DeserializeObject <List <SessionStorageThemes> >(saved);
}
var themeVisits = new List <ThemeVisit>();
foreach (var savedTheme in storage)
{
var theme = _db.Theme.Include(x => x.ThemeGroup).Include(x => x.Subject).ThenInclude(x => x.Semester).Where(x => x.Id == savedTheme.ThemeId).FirstOrDefault();
theme.ThemeGroup.UsedHours = savedTheme.ThemeGroup.UsedHours;
var hoursPerVisit = savedTheme.Reserved;
var typeSubject = theme.Type;
var themeVisit = new ThemeVisit()
{
HoursPerVisit = hoursPerVisit, Theme = theme, TypeSubject = typeSubject
};
themeVisits.Add(themeVisit);
}
var visit = new GroupVisit()
{
Date = date,
ThemeVisits = themeVisits,
Subject = _db.Subject.Find(model.SubjectId),
Group = _db.Group.Find(model.GroupId),
Semester = _db.Subject.Include(x => x.Semester).Where(x => x.Id == model.SubjectId).FirstOrDefault().Semester,
Theme = themeVisits.FirstOrDefault().Theme
};
_db.GroupVisit.Add(visit);
foreach (var visitModify in model.Output.VisitsModify)
{
var studentVisit = new StudentVisit()
{
TypeVisit = visitModify.TypeVisit,
Student = _db.Student.Find(visitModify.StudentId),
GroupVisit = visit,
Subject = _db.Subject.Find(model.SubjectId),
Theme = visit.ThemeVisits.FirstOrDefault().Theme
};
_db.StudentVisit.Add(studentVisit);
visit.StudentVisits.Add(studentVisit);
visit.Theme.StudentVisits.Add(studentVisit);
}
_db.SaveChanges();
return(View("VisitAddedView"));
}
public void ChangedVisitInfo(StudentVisit studentVisit)
{
MySql mySql = new MySql();
var result = mySql.SetStudentVisit(studentVisit);
if (!result)
{
MessageBox.Show("Ошибка при изменении значения!", "Ошибка!", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
studentVisits[visitId] = studentVisit;
grdPresenseInfoTable.ItemsSource = null;
grdPresenseInfoTable.ItemsSource = studentVisits;
}
public EditVisitPage(StudentVisit studentVisit, ChangedPresense changedPresense)
{
InitializeComponent();
if (studentVisit.Presense)
{
cmbPresense.SelectedIndex = 0;
}
else
{
cmbPresense.SelectedIndex = 1;
}
visit = studentVisit;
this.changedPresense = changedPresense;
}
private string SetStudentVisitPresenseDBRequest(StudentVisit studentVisit)
{
string request;
var presense = studentVisit.Presense ? 1 : 0;
var date = studentVisit.DateTime.ToString("yyyy-MM-dd");
var selectIdStudent = $"select idstudent from vcsdb.students where firstname = '{studentVisit.FirstName}' and lastname = '{studentVisit.LastName}' and pastname = '{studentVisit.PastName}'";
var selectIdSubject = $"select idsubject from vcsdb.subjects where subject = '{studentVisit.Subject}'";
var selectIdClassroom = $"select idclassroom from vcsdb.classrooms where classroom = '{studentVisit.Classroom}'";
var selectIdStudentGroup = $"select idstudentgroup from vcsdb.studentgroups where studentgroup = '{studentVisit.Group}'";
var selectIdClass = $"select idclass from vcsdb.shedule where idsubject = ({selectIdSubject}) and idclassroom = ({selectIdClassroom}) and idstudentgroup = ({selectIdStudentGroup})";
request = $"UPDATE vcsdb.visits " +
$"SET presense = '{presense}' " +
$"WHERE idpresense = '{studentVisit.Id}' " +
$"AND idclass = ({selectIdClass});";
return(request);
}
private static void SeedLessons(Repository repository)
{
int numberOfLessons = 5;
Random random = new Random(DateTime.Now.Millisecond);
foreach (var group in repository.Students.Select(s => s.Group).Distinct())
{
foreach (var subject in repository.Subjects)
{
for (int i = 0; i < numberOfLessons; i++)
{
Lesson lesson = new Lesson
{
StartTime = DateTime.Today.AddDays(-i).AddHours(9 + i),
EndTime = DateTime.Today.AddDays(-i).AddHours(9 + i + 1),
Subject = subject,
Room = repository.Rooms[random.Next(repository.Rooms.Count)],
Teacher = repository.Teachers[random.Next(repository.Teachers.Count)],
};
repository.Lessons.Add(lesson);
foreach (var student in repository.Students)
{
if (random.Next(5) != 0)
{
StudentVisit visit = new StudentVisit {
Lesson = lesson, Student = student
};
if (random.Next(3) == 0)
{
visit.Mark = (byte)random.Next(3, 10);
}
repository.Visits.Add(visit);
}
}
}
}
}
}
// TODO: ЗАМЕНИТЬ НА СОЗДАНИЕ НОВОГО ПОСЕЩЕНИЯ с 0
/// <summary>
/// Метод, который создает новое посещение с переменной Присутствие=0
/// </summary>
/// <param name="studentVisit"></param>
/// <returns></returns>
public bool NewStudentVisit(StudentVisit studentVisit)
{
bool success = true;
conn.Open();
try
{
var cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = NewStudentVisitsDBRequest(studentVisit);
var reader = cmd.ExecuteReader();
}
catch (NullReferenceException)
{
success = false;
throw;
}
finally
{
conn.Close();
conn.Dispose();
}
return(success);
}
