public void UpdateForm()
{
using (var context = new HostelModelContainer()) {
occupation = context.OccupationSet.Single(o => o.Id == occupationId);
tbxRoom.Text = occupation.Room.Hostel.Name + " (" + occupation.Room.Hostel.Address + ") комната " + occupation.Room.Number;
tbxStudent.Text = occupation.Student.Person.LastName + " " + occupation.Student.Person.FirstName + " " + occupation.Student.Person.MiddleName + " - " +
occupation.Student.Group.Faculty.Name + " " + occupation.Student.Group.StudyYear + " курс " + occupation.Student.Group.Number + " группа";
dpkFrom.SelectedDate = occupation.FromDate;
if (occupation.ToDate != null)
{
dpkTo.SelectedDate = occupation.ToDate;
}
if (occupation.Order != null)
{
tbxPrice.Text = occupation.Order.Price.ToString();
if (occupation.Order.OrderDate != null)
{
dpkOrder.SelectedDate = occupation.Order.OrderDate;
}
tbxOrderNumber.Text = occupation.Order.Number;
payments = (from p in context.PaymentSet
where p.Order.Id == occupation.Order.Id
select p.Amount).DefaultIfEmpty().Sum();
}
lblPayment.Content = payments.ToString();
}
}
// поиск комнаты по общежитию и номеру
public static Room FindRoom(int hostelId, int roomNumber)
{
using (var context = new HostelModelContainer()) {
Room room = context.RoomSet.Where(r => r.Number == roomNumber && r.Hostel.Id == hostelId).FirstOrDefault();
return(room);
}
}
private void UpdateStudent(int studentId)
{
using (var context = new HostelModelContainer()) {
// обновление информации про студента
Student student = context.StudentSet.Single(s => s.Id == studentId);
Person person = student.Person;
person.FirstName = txtFN.Text;
person.LastName = txtLN.Text;
person.MiddleName = txtMN.Text;
person.Passport = txtPasp.Text;
person.RegistrationAddress = txtAddr.Text;
Person father = person.Father;
if (father == null)
{
father = new Person();
context.PersonSet.Add(father);
person.Father = father;
}
father.FirstName = txtFFN.Text;
father.LastName = txtFLN.Text;
father.MiddleName = txtFMN.Text;
Person mother = person.Mother;
if (mother == null)
{
mother = new Person();
context.PersonSet.Add(mother);
person.Mother = mother;
}
mother.FirstName = txtMFN.Text;
mother.LastName = txtMLN.Text;
mother.MiddleName = txtMMN.Text;
context.SaveChanges();
}
}
// загрузка объекта Room и данных по Hostel
public static Room LoadRoom(int roomId)
{
using (var context = new HostelModelContainer()) {
Room room = context.RoomSet.Single(r => r.Id == roomId);
Hostel hostel = room.Hostel; // предзагрузка данных про общежитиеы
return(room);
}
}
private void DeactivateStudent(int studentId)
{
// деактивация студента
using (var context = new HostelModelContainer()) {
Student student = context.StudentSet.Single(s => s.Id == studentId);
student.Active = false;
context.SaveChanges();
}
}
// подсчитать количество занятых комнат
public static int GetOccupiedRoomsCount()
{
using (var context = new HostelModelContainer()) {
return(context.OccupationSet.Where(o =>
o.Active &&
o.Student.Active &&
o.FromDate <= DateTime.Today && (o.ToDate >= DateTime.Today || o.ToDate == null)
).Count());
}
}
// поиск студентов и извлечение информации для таблицы
public static List <StudentRecord> FindStudents(String text)
{
using (var context = new HostelModelContainer()) {
if (text != null && text.Length > 0)
{
var query = context.StudentSet.Where(s => s.Active && (
s.Person.FirstName.ToLower().StartsWith(text.ToLower()) ||
s.Person.LastName.ToLower().StartsWith(text.ToLower()))).Take(10).Select(s =>
new StudentRecord {
Id = s.Id,
LastName = s.Person.LastName,
FirstName = s.Person.FirstName,
MiddleName = s.Person.MiddleName,
FacultyName = s.Group.Faculty.Name,
StudyYear = s.Group.StudyYear,
GroupNumber = s.Group.Number,
StudentOccupation = (
from o in context.OccupationSet
where o.Student == s &&
o.Active && o.Student.Active &&
o.FromDate <= DateTime.Today && (o.ToDate >= DateTime.Today || o.ToDate == null)
select
new StudentOccupation {
Hostel = o.Room.Hostel.Name + "\n" + o.Room.Hostel.Address,
RoomNumber = o.Room.Number
}).FirstOrDefault()
});
return(query.ToList());
}
else
{
var query = context.StudentSet.Where(s => s.Active).Take(10).Select(s =>
new StudentRecord {
Id = s.Id,
LastName = s.Person.LastName,
FirstName = s.Person.FirstName,
MiddleName = s.Person.MiddleName,
FacultyName = s.Group.Faculty.Name,
StudyYear = s.Group.StudyYear,
GroupNumber = s.Group.Number,
StudentOccupation = (
from o in context.OccupationSet
where o.Student == s &&
o.Active && o.Student.Active &&
o.FromDate <= DateTime.Today && (o.ToDate >= DateTime.Today || o.ToDate == null)
select
new StudentOccupation {
Hostel = o.Room.Hostel.Name + "\n" + o.Room.Hostel.Address,
RoomNumber = o.Room.Number
}).FirstOrDefault()
});
return(query.ToList());
}
}
}
// попытка авторизации пользователя, если успекно то возврашается заполненный объект User
public static User Login(String login, String password)
{
using (var context = new HostelModelContainer()) {
User user = context.UserSet.Where(u => u.Login == login && u.Pasword == password && u.Active).FirstOrDefault();
if (user != null)
{
Person person = user.Person; // загрузить дополнительные данные в объект заранее
}
return(user);
}
}
private void InitFilters()
{
faculties.Clear();
cmbFaculty.Items.Clear();
using (var context = new HostelModelContainer()) {
faculties = context.FacultySet.ToList();
foreach (Faculty faculty in faculties)
{
cmbFaculty.Items.Add(faculty.Name);
}
}
}
private void BtnOk_Click(object sender, RoutedEventArgs e)
{
// проверки корректности
String errorText = null;
if (dpkPaymentDate.SelectedDate == null)
{
errorText = "Дата оплаты не указана";
}
double amount = 0;
if (tbxAmount.Text.Length > 0 && !Double.TryParse(tbxAmount.Text, out amount))
{
errorText = "Ввеедено некорректное значение для стоимости оплаты";
}
if (amount < 0)
{
errorText = "Стоимость оплаты не может быть отрицательной";
}
if (amount > 0 && tbxPaymentNumber.Text.Length == 0)
{
errorText = "Не задан номер счета";
}
// изменения
if (errorText == null)
{
using (var context = new HostelModelContainer()) {
context.Set(typeof(Occupation)).Attach(occupation);
Order order = occupation.Order;
Payment payment = new Payment()
{
Order = order,
Amount = amount,
PaymentDate = dpkPaymentDate.SelectedDate.Value,
Number = tbxPaymentNumber.Text
};
context.PaymentSet.Add(payment);
context.SaveChanges();
}
Close();
}
else
{
lblError.Content = errorText;
}
}
// Поиск студентов проживающих в комнате
public static List <StudentRecord> FindStudents(int roomId, bool loadOccupationInfo)
{
using (var context = new HostelModelContainer()) {
if (loadOccupationInfo)
{
return((from o in context.OccupationSet
where o.Room.Id == roomId && o.Active && o.Student.Active && o.FromDate <= DateTime.Today && (o.ToDate >= DateTime.Today || o.ToDate == null)
select new StudentRecord {
Id = o.Student.Id,
LastName = o.Student.Person.LastName,
FirstName = o.Student.Person.FirstName,
MiddleName = o.Student.Person.MiddleName,
FacultyName = o.Student.Group.Faculty.Name,
StudyYear = o.Student.Group.StudyYear,
GroupNumber = o.Student.Group.Number,
StudentOccupation = new StudentOccupation {
Id = o.Id,
From = o.FromDate,
To = o.ToDate,
Price = (from oo in context.OrderSet
where oo.Ocupation == o
select oo.Price).FirstOrDefault(),
Payed = (from p in context.PaymentSet
where p.Order.Ocupation == o
select p.Amount).DefaultIfEmpty().Sum()
}
}).ToList());
}
else
{
return((from o in context.OccupationSet
where o.Room.Id == roomId && o.Active && o.Student.Active &&
o.FromDate <= DateTime.Today && (o.ToDate >= DateTime.Today || o.ToDate == null)
select new StudentRecord {
Id = o.Student.Id,
LastName = o.Student.Person.LastName,
FirstName = o.Student.Person.FirstName,
MiddleName = o.Student.Person.MiddleName,
FacultyName = o.Student.Group.Faculty.Name,
StudyYear = o.Student.Group.StudyYear,
GroupNumber = o.Student.Group.Number
}).ToList());
}
}
}
// Поселить студента в комнату. Если студент уже где-то живет то он буоет выселен, если roomId == -1 то он будет просто выселен
public static (Occupation, Occupation) SetOccupation(int studentId, int roomId)
{
using (var context = new HostelModelContainer()) {
Occupation previousOcupation = context.OccupationSet.SingleOrDefault(o => o.Student.Id == studentId && o.Active);
if (previousOcupation != null)
{
Room room = previousOcupation.Room; // дозагрузка информации про комнату
}
if (roomId != -1)
{
if (previousOcupation == null || (previousOcupation.Room.Id != roomId))
{
if (previousOcupation != null)
{
previousOcupation.Active = false;
}
Occupation occupation = new Occupation()
{
Active = true,
Student = context.StudentSet.Single(s => s.Id == studentId),
Room = context.RoomSet.Single(r => r.Id == roomId),
FromDate = DateTime.Today
};
context.OccupationSet.Add(occupation);
context.SaveChanges();
return(previousOcupation, occupation);
}
else
{
return(previousOcupation, previousOcupation);
}
}
else
{
if (previousOcupation != null)
{
previousOcupation.Active = false;
context.SaveChanges();
}
return(previousOcupation, null);
}
}
}
// Поиск комнат
public static List <RoomRecord> GetRooms(int hostelId)
{
using (var context = new HostelModelContainer()) {
if (hostelId == -1)
{
return((from r in context.RoomSet
select new RoomRecord {
Id = r.Id,
Hostel = r.Hostel.Name + "(" + r.Hostel.Address + ")",
Floor = r.Floor,
Number = r.Number,
Capacity = r.Capacity,
Occupied = (from o in context.OccupationSet
where o.Room == r &&
o.Active &&
o.Student.Active &&
o.FromDate <= DateTime.Today && (o.ToDate >= DateTime.Today || o.ToDate == null)
select o).Count()
}).ToList());
}
else
{
return((from r in context.RoomSet
where r.Hostel.Id == hostelId
select new RoomRecord {
Id = r.Id,
Hostel = r.Hostel.Name + " (" + r.Hostel.Address + ")",
Floor = r.Floor,
Number = r.Number,
Capacity = r.Capacity,
Occupied = (from o in context.OccupationSet
where o.Room == r &&
o.Active &&
o.Student.Active &&
o.FromDate <= DateTime.Today && (o.ToDate >= DateTime.Today || o.ToDate == null)
select o).Count()
}).ToList());
}
}
}
private int CreateNewStudent()
{
using (var context = new HostelModelContainer()) {
// создание нового студента
Person person = new Person();
context.PersonSet.Add(person);
person.FirstName = txtFN.Text;
person.LastName = txtLN.Text;
person.MiddleName = txtMN.Text;
person.Passport = txtPasp.Text;
person.RegistrationAddress = txtAddr.Text;
Person father = new Person();
context.PersonSet.Add(father);
person.Father = father;
father.FirstName = txtFFN.Text;
father.LastName = txtFLN.Text;
father.MiddleName = txtFMN.Text;
Person mother = new Person();
context.PersonSet.Add(mother);
person.Mother = mother;
mother.FirstName = txtMFN.Text;
mother.LastName = txtMLN.Text;
mother.MiddleName = txtMMN.Text;
Student student = new Student();
context.StudentSet.Add(student);
student.Person = person;
int facultyId = faculties[cmbFaculty.SelectedIndex].Id;
int year = Int32.Parse(txtYear.Text);
int number = Int32.Parse(txtGrpNumber.Text);
Group group = (from g in context.GroupSet
where g.Faculty.Id == facultyId && g.StudyYear == year && g.Number == number
select g).FirstOrDefault();
student.Group = group;
student.Active = true;
context.SaveChanges();
return(student.Id);
}
}
// подсчитать количество всех комнат в системе
public static int GetRoomsCount()
{
using (var context = new HostelModelContainer()) {
return(context.RoomSet.Sum(r => r.Capacity));
}
}
// возвращает спсиок всех обежитий
public static List <Hostel> GetAllHostels()
{
using (var context = new HostelModelContainer()) {
return(context.HostelSet.ToList());
}
}
private void BtnSave_Click(object sender, RoutedEventArgs e)
{
// проверка корректности полей
lblError.Content = "";
try {
if (txtLN.Text.Length == 0)
{
throw new Exception("Фамилия студента не может быть пустой");
}
if (txtFN.Text.Length == 0)
{
throw new Exception("Имя студента не может быть пустым");
}
if (cmbFaculty.SelectedIndex == -1)
{
throw new Exception("Не задан факультет");
}
int year = 0;
if (txtYear.Text.Length == 0 || !Int32.TryParse(txtYear.Text, out year))
{
throw new Exception("Год обучения задан некорректно");
}
if (year < 1 || year > 4)
{
throw new Exception("Год обучения должен быить от 1 до 4");
}
int number = 0;
if (txtGrpNumber.Text.Length == 0 || !Int32.TryParse(txtGrpNumber.Text, out number))
{
throw new Exception("Номер группы задан некорректно");
}
int facultyId = faculties[cmbFaculty.SelectedIndex].Id;
using (var context = new HostelModelContainer()) {
Group group = (from g in context.GroupSet
where g.Faculty.Id == facultyId && g.StudyYear == year && g.Number == number
select g).FirstOrDefault();
if (group == null)
{
throw new Exception("Указанная группа не найдена");
}
}
if (currentStudentId == -1)
{
int studentId = CreateNewStudent();
SwitchToEditMode(studentId);
}
else
{
using (var context = new HostelModelContainer()) {
Student student = context.StudentSet.Single(s => s.Id == currentStudentId);
if (student.Group.Number != number || student.Group.StudyYear != year || student.Group.Faculty.Id != facultyId)
{
DeactivateStudent(student.Id);
int studentId = CreateNewStudent();
SwitchToEditMode(studentId);
}
else
{
UpdateStudent(student.Id);
SwitchToEditMode(student.Id);
}
}
}
} catch (Exception ex) {
lblError.Content = ex.Message;
}
}
// продсчитать сумму всех оплат
public static double GetPaymentsSum()
{
using (var context = new HostelModelContainer()) {
return(context.PaymentSet.Sum(p => (double?)p.Amount) ?? 0);
}
}
private void SwitchToEditMode(int studentId)
{
currentStudentId = studentId;
using (var context = new HostelModelContainer()) {
Student student = context.StudentSet.Single(s => s.Id == studentId);
lblMode.Content = student.Person.LastName + " " + student.Person.FirstName + " " + student.Person.MiddleName;
txtLN.Text = student.Person.LastName;
txtFN.Text = student.Person.FirstName;
txtMN.Text = student.Person.MiddleName;
txtPasp.Text = student.Person.Passport;
txtAddr.Text = student.Person.RegistrationAddress;
if (student.Person.Father != null)
{
txtFLN.Text = student.Person.Father.LastName;
txtFFN.Text = student.Person.Father.FirstName;
txtFMN.Text = student.Person.Father.MiddleName;
}
else
{
txtFLN.Text = "";
txtFFN.Text = "";
txtFMN.Text = "";
}
if (student.Person.Mother != null)
{
txtMLN.Text = student.Person.Mother.LastName;
txtMFN.Text = student.Person.Mother.FirstName;
txtMMN.Text = student.Person.Mother.MiddleName;
}
else
{
txtMLN.Text = "";
txtMFN.Text = "";
txtMMN.Text = "";
}
cmbFaculty.SelectedIndex = faculties.FindIndex(f => f.Id == student.Group.Faculty.Id);
txtYear.Text = student.Group.StudyYear.ToString();
txtGrpNumber.Text = student.Group.Number.ToString();
List <OccupationRecord> occupations = (from o in context.OccupationSet
where o.Student.Person.Id == student.Person.Id
select new OccupationRecord {
Id = o.Id,
Active = o.Active,
Hostel = o.Room.Hostel.Name + " (" + o.Room.Hostel.Address + ")",
RoomNumber = o.Room.Number,
FromDate = o.FromDate,
ToDate = o.ToDate,
Order = (from oo in context.OrderSet
where oo.Ocupation == o
select new OccupationOrderRecord {
Price = oo.Price,
Number = oo.Number,
PaymentAmount = (from p in context.PaymentSet
where p.Order == oo
select p.Amount).DefaultIfEmpty().Sum()
}
).FirstOrDefault()
}).ToList();
OccupationRecord currentOccupation = null;
double dept = 0;
occupations.ForEach(o => {
o.From = o.FromDate.ToString("dd.MM.yyyy");
o.To = o.ToDate?.ToString("dd.MM.yyyy");
o.ActiveText = o.Active ? "актуально" : "архив";
if (o.Active && o.FromDate <= DateTime.Today && (o.ToDate >= DateTime.Today || o.ToDate == null))
{
currentOccupation = o;
}
if (o.Order != null)
{
dept += o.Order.Price;
}
});
occupations.Sort((r1, r2) => r2.FromDate.CompareTo(r1.FromDate));
if (currentOccupation == null)
{
lblOccuoationStatus.Content = "не заселен";
}
else
{
lblOccuoationStatus.Content = "заселен - " + currentOccupation.Hostel + ", комната " + currentOccupation.RoomNumber;
}
grdOHist.ItemsSource = new List <OccupationRecord>();
grdOHist.ItemsSource = occupations;
List <PaymentRecord> payments = (from p in context.PaymentSet
where p.Order.Ocupation.Student.Person.Id == student.Person.Id
select new PaymentRecord {
Id = p.Id,
Amount = p.Amount,
Number = p.Number,
PaymentDate = p.PaymentDate,
Order = new PaymentOrderRecord {
Price = p.Order.Price,
Number = p.Order.Number,
OrderDate = p.Order.OrderDate
}
}).ToList();
payments.ForEach(p => {
p.Date = p.PaymentDate.ToString("dd.MM.yyyy");
p.Order.Date = p.Order.OrderDate.ToString("dd.MM.yyyy");
dept -= p.Amount;
});
payments.Sort((r1, r2) => r2.PaymentDate.CompareTo(r1.PaymentDate));
if (dept > 0.001)
{
lblPaymentStatus.Content = "долг студента = " + dept.ToString();
}
else if (dept < -0.001)
{
lblPaymentStatus.Content = "переплата студента = " + (-dept).ToString();
}
else
{
lblPaymentStatus.Content = "долга нет";
}
grdPHist.ItemsSource = payments;
lblError.Content = "";
}
}
private void BtnOk_Click(object sender, RoutedEventArgs e)
{
// проверки корректности
String errorText = null;
if (dpkFrom.SelectedDate == null)
{
errorText = "Дата заселения не указана";
}
if (dpkTo.SelectedDate != null && dpkFrom.SelectedDate != null && dpkFrom.SelectedDate > dpkTo.SelectedDate)
{
errorText = "Дата заселения должна быть больше даты выселения";
}
double price = 0;
if (tbxPrice.Text.Length > 0 && !Double.TryParse(tbxPrice.Text, out price))
{
errorText = "Ввеедено некорректное значение для цены";
}
if (price < 0)
{
errorText = "Цена не может быть отрицательной";
}
if (price > 0 && tbxOrderNumber.Text.Length == 0)
{
errorText = "Не задан номер счета";
}
if (price > 0 && dpkOrder.SelectedDate == null)
{
errorText = "Не задана дата платежа";
}
if (price == 0 && payments > 0)
{
errorText = "Нельзя удалить счет по которому есть платежи";
}
// изменения
if (errorText == null)
{
using (var context = new HostelModelContainer()) {
context.Set(typeof(Occupation)).Attach(occupation);
occupation.FromDate = dpkFrom.SelectedDate.Value;
occupation.ToDate = dpkTo.SelectedDate;
Order order = occupation.Order;
if (price > 0)
{
if (order == null)
{
order = new Order()
{
Ocupation = occupation
};
context.OrderSet.Add(order);
}
order.Price = price;
order.OrderDate = dpkOrder.SelectedDate.Value;
order.Number = tbxOrderNumber.Text;
}
if (order != null && price == 0)
{
context.OrderSet.Remove(order);
}
context.SaveChanges();
}
Close();
}
else
{
lblError.Content = errorText;
}
}
// продсчитать сумму всех выставленных счетов
public static double GetOrdersSum()
{
using (var context = new HostelModelContainer()) {
return(context.OrderSet.Sum(o => (double?)o.Price) ?? 0);
}
}