private void btOK_Click(object sender, EventArgs e)
{
try
{
if (tbLastname.Text != "" && tbFirstname.Text != "" && tbMiddlename.Text != "" && tbLogin.Text != "" && cbRoles.Text != "" && cbDepartment.Text != "")
{
if (edit)
{
if (worker.Id == 0 && tbPassword.Text == "")
{
MessageBox.Show("При добавлении сотрудника, необходимо обязательно указать пароль!", "Внимание", MessageBoxButtons.OK);
return;
}
if (cbPosts.SelectedIndex == -1)
{
var dr = MessageBox.Show("Не указана должность сотрудника. Вы хотите продолжить сохранение?", "Внимание", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dr == DialogResult.No)
{
return;
}
}
worker.Lastname = tbLastname.Text;
worker.Firstname = tbFirstname.Text;
worker.Middlename = tbMiddlename.Text;
worker.LastnameEn = (tbLastnameEn.Text != "") ? tbLastnameEn.Text : null;
worker.FirstnameEn = (tbFirstnameEn.Text != "") ? tbFirstnameEn.Text : null;
worker.MiddlenameEn = (tbMiddlenameEn.Text != "") ? tbMiddlenameEn.Text : null;
worker.RoleId = Convert.ToInt32(cbRoles.SelectedValue);
worker.Login = tbLogin.Text;
worker.DepartmentId = Convert.ToInt32(cbDepartment.SelectedValue);
worker.AllAccessGroups = chbAllAccessGroups.Checked;
if (cbPosts.SelectedIndex != -1)
{
worker.PostId = Convert.ToInt32(cbPosts.SelectedValue);
}
else
{
worker.PostId = null;
}
if (tbPassword.Text != "")
{
worker.Password = tbPassword.Text;
}
try
{
if (worker.Id == 0)
{
db.Workers.Add(worker);
}
else
{
db.Entry(worker).State = EntityState.Modified;
}
db.SaveChanges();
}
catch (Exception ex)
{
MessageBox.Show("Ошибка сохранения данных сотрудника \r\n" + ex.Message, "Ошибка", MessageBoxButtons.OK);
}
finally
{
edit = false;
Close();
}
}
}
else
{
MessageBox.Show("Не все поля заполнены!", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
catch (Exception ex)
{
var m = new System.Diagnostics.StackTrace(false).GetFrame(0).GetMethod();
string methodName = m.DeclaringType.ToString() + ";" + m.Name;
CurrentSession.ReportError(methodName, ex.Message);
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void ListGroupsForm_Load(object sender, EventArgs e)
{
try
{
//загрузка фильтра направлений
var direction = db.Directions.Where(a => a.IsRemoved == false).ToList();
cbDirectionOfTraining.DataSource = direction;
cbDirectionOfTraining.DisplayMember = "Name";
cbDirectionOfTraining.ValueMember = "Id";
cbDirectionOfTraining.SelectedIndex = -1;
//загрузка фильтра преподавателей
var roleTeacher = db.Roles.Where(a => a.IsRemoved == false).FirstOrDefault(a => a.Name == "Преподаватель");
if (roleTeacher != null)
{
var teachers = from teach in db.Workers
where teach.RoleId == roleTeacher.Id && teach.IsRemoved == false
select new
{
Id = teach.Id,
Teacher = teach.Lastname + " "
+ teach.Firstname.Substring(0, 1) + "."
+ teach.Middlename.Substring(0, 1) + ".",
RoleId = teach.RoleId
};
cbTeachers.DataSource = teachers.OrderBy(a => a.Teacher).ToList();
cbTeachers.DisplayMember = "Teacher";
cbTeachers.ValueMember = "Id";
cbTeachers.SelectedIndex = -1;
}
else
{
MessageBox.Show("Не удалось получить список преподавателей", "Ошибка", MessageBoxButtons.OK);
}
//загрузка фильтра тип обучения
var type = db.Years.Where(a => a.IsRemoved == false).OrderBy(a => a.SortIndex).ToList();
cbTypeGroup.DataSource = type;
cbTypeGroup.DisplayMember = "Name";
cbTypeGroup.ValueMember = "Id";
cbTypeGroup.SelectedIndex = -1;
//загрузка статусов групп
cbActivity.DataSource = db.ActivityGroups.Where(a => a.IsRemoved == false).ToList();
cbActivity.DisplayMember = "Name";
cbActivity.ValueMember = "Id";
//выбор активного статуса
var currentActivity = db.ActivityGroups.FirstOrDefault(a => a.Name == "Активные" && a.IsRemoved == false);
cbActivity.SelectedValue = currentActivity.Id;
//добавление программной сортировки
foreach (DataGridViewColumn col in dgvListGroups.Columns)
{
col.SortMode = DataGridViewColumnSortMode.Programmatic;
}
//загрузка списка слушателей
Filter(null, null, null, null, cbActivity.Text, sortColumn, sortOrder);
//оформление таблицы
dgvListGroups.Columns["GroupId"].Visible = dgvListGroups.Columns["DirectionId"].Visible =
dgvListGroups.Columns["TeacherId"].Visible = dgvListGroups.Columns["ActivityId"].Visible =
dgvListGroups.Columns["Activity"].Visible = dgvListGroups.Columns["YearId"].Visible =
dgvListGroups.Columns["Type"].Visible = false;
dgvListGroups.Columns["Direction"].HeaderText = "Направление";
dgvListGroups.Columns["Name"].HeaderText = "Группа";
dgvListGroups.Columns["Branch"].HeaderText = "Корпус";
dgvListGroups.Columns["Class"].HeaderText = "Класс";
dgvListGroups.Columns["Days"].HeaderText = "Дни занятий";
dgvListGroups.Columns["Begin"].HeaderText = "Начало";
dgvListGroups.Columns["DurationLesson"].HeaderText = "Продолж.";
dgvListGroups.Columns["Year"].HeaderText = "Уч. год";
dgvListGroups.Columns["Teacher"].HeaderText = "Преподаватель";
dgvListGroups.Columns["TwoTeachers"].HeaderText = "Микс";
dgvListGroups.Columns["TwoTeachers"].SortMode = DataGridViewColumnSortMode.Programmatic;
dgvListGroups.Columns["Note"].HeaderText = "Примечание";
dgvListGroups.Columns["Students"].HeaderText = "Количество";
dgvListGroups.Columns["Note"].Width = 200;
dgvListGroups.Columns["Begin"].DefaultCellStyle.Format = "HH:mm";
dgvListGroups.Focus(); dgvListGroups.Select();
}
catch (Exception ex)
{
var m = new System.Diagnostics.StackTrace(false).GetFrame(0).GetMethod();
string methodName = m.DeclaringType.ToString() + ";" + m.Name;
CurrentSession.ReportError(methodName, ex.Message);
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void подробныйСписокToolStripMenuItem_Click(object sender, EventArgs e)
{
if (dgvListGroups.Rows.Count != 0)
{
try
{
var total = (from groups in db.Groups
join courses in db.Courses on groups.CourseId equals courses.Id
join directions in db.Directions on courses.DirectionId equals directions.Id
join teachers in db.Workers on groups.TeacherId equals teachers.Id into outerTeacher
from teachers in outerTeacher.DefaultIfEmpty()
join activity in db.ActivityGroups on groups.ActivityId equals activity.Id
join classes in db.Classes on groups.ClassId equals classes.Id into outerClass
from classes in outerClass.DefaultIfEmpty()
join branches in db.Housings on classes.HousingId equals branches.Id into outerBranch
from branches in outerBranch.DefaultIfEmpty()
join years in db.Years on groups.YearId equals years.Id into g
from years in g.DefaultIfEmpty()
where (activity.Name == "Текущие" || activity.Name == "В наборе")
select new ListGroupFull
{
Id = groups.Id,
Direction = directions.Name,
Course = courses.Name,
Group = groups.Name,
StatusGroup = activity.Name,
Branch = branches.Name,
Class = classes.Name,
Days = groups.Days,
Begin = groups.Begin,
DurationLesson = groups.DurationLesson,
DurationCourse = groups.DurationCourse,
CountLesson = (groups.DurationLesson != 0) ? (int)(groups.DurationCourse / groups.DurationLesson) : 0,
PassedLesson = groups.Lessons.Count,
Teacher = teachers.Lastname + " " + teachers.Firstname.Substring(0, 1) + "." + teachers.Middlename.Substring(0, 1) + ".",
TwoTeachers = groups.TwoTeachers,
Students = groups.Enrollments.Where(a => a.GroupId == groups.Id && a.DateExclusion == null).Count(),
Year = years.Name,
SchedSumNow = (from scheds in db.Schedules
join enrolls in db.Enrollments on scheds.EnrollmentId equals enrolls.Id
where scheds.Source == 2 && scheds.EnrollmentId != null && scheds.GroupId == groups.Id && enrolls.DateExclusion == null &&
scheds.DateBegin <= DateTime.Now
select new { SchedSum = scheds.Value - scheds.Discount }).Sum(s => (double?)(s.SchedSum)) ?? 0,
PaysSumNow = (from pays in db.Payments
join enrolls in db.Enrollments on pays.EnrollmentId equals enrolls.Id
where enrolls.GroupId == groups.Id && enrolls.DateExclusion == null && pays.IsDeleted == false && pays.AdditionalPay == false
select new { PaySum = pays.ValuePayment }).Sum(s => (double?)(s.PaySum)) ?? 0,
SaldoNow = String.Empty,
}).ToList();
var exportGroupFull = new Report();
exportGroupFull.ExportExcelGroupFull(total);
}
catch (Exception ex)
{
var m = new System.Diagnostics.StackTrace(false).GetFrame(0).GetMethod();
string methodName = m.DeclaringType.ToString() + ";" + m.Name;
CurrentSession.ReportError(methodName, ex.Message);
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public void StudentsForm_Load(object sender, EventArgs e)
{
try
{
//загрузка фильтра групп
var actGroups = db.Groups.Where(a => a.Activity.Name != "Закрытые" && a.Individual == false).OrderBy(a => a.Name).ToList();
cbGroups.DataSource = actGroups;
cbGroups.DisplayMember = "Name";
cbGroups.ValueMember = "Id";
cbGroups.SelectedIndex = -1;
//загрузка фильтра оснований скидок
var privileges = db.Privileges.ToList();
cbPrivileges.DataSource = privileges;
cbPrivileges.DisplayMember = "Name";
cbPrivileges.ValueMember = "Id";
cbPrivileges.SelectedIndex = -1;
//загрузка фильтра тип обучения
var type = db.Years.Where(a => a.IsRemoved == false).OrderBy(a => a.SortIndex).ToList();
cbTypeGroup.DataSource = type;
cbTypeGroup.DisplayMember = "Name";
cbTypeGroup.ValueMember = "Id";
cbTypeGroup.SelectedIndex = -1;
//заполнение статусов групп
cbActivityGroup.DataSource = db.ActivityGroups.Where(a => a.IsRemoved == false).ToList();
cbActivityGroup.DisplayMember = "Name";
cbActivityGroup.ValueMember = "Id";
//выбор активного статуса
var currentActivity = db.ActivityGroups.FirstOrDefault(a => a.Name == "Активные");
cbActivityGroup.SelectedValue = currentActivity.Id;
//установка дат
var date = dtpStart.Value = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
date = date.AddMonths(1).AddDays(-1);
dtpEnd.Value = new DateTime(DateTime.Now.Year, DateTime.Now.Month, date.Day);
//добавление программной сортировки
foreach (DataGridViewColumn col in dgvStudents.Columns)
{
col.SortMode = DataGridViewColumnSortMode.Programmatic;
}
//загрузка списка слушателей
Filter(null, null, null, cbActivityGroup.Text, null, dtpStart.Value, dtpEnd.Value, sortColumn, sortOrder);
//оформление таблицы
dgvStudents.Columns["StudentId"].Visible = dgvStudents.Columns["GroupId"].Visible =
dgvStudents.Columns["SchoolId"].Visible = dgvStudents.Columns["EnrollId"].Visible =
dgvStudents.Columns["StatusId"].Visible = dgvStudents.Columns["ActivityId"].Visible =
dgvStudents.Columns["Activity"].Visible = dgvStudents.Columns["YearId"].Visible =
dgvStudents.Columns["PrivilegeId"].Visible = false;
dgvStudents.Columns["Lastname"].HeaderText = "Фамилия";
dgvStudents.Columns["Firstname"].HeaderText = "Имя";
dgvStudents.Columns["Middlename"].HeaderText = "Отчество";
dgvStudents.Columns["Status"].HeaderText = "Статус";
dgvStudents.Columns["NameGroup"].HeaderText = "Группа";
dgvStudents.Columns["Privilege"].HeaderText = "Льгота";
dgvStudents.Columns["School"].HeaderText = "Школа";
dgvStudents.Columns["Class"].HeaderText = "Класс";
dgvStudents.Columns["Payment"].HeaderText = "Платежи";
dgvStudents.Columns["AccrualDiscount"].HeaderText = "Начислено со скидкой";
dgvStudents.Columns["Sex"].HeaderText = "Пол";
dgvStudents.Columns["Accrual"].HeaderText = "Начислено";
dgvStudents.Columns["Saldo"].HeaderText = "Остаток";
dgvStudents.Columns["Year"].HeaderText = "Форма\\Год";
dgvStudents.Columns["Sex"].Width = dgvStudents.Columns["Class"].Width = 50;
dgvStudents.Columns["School"].Width = 80;
}
catch (Exception ex)
{
var m = new System.Diagnostics.StackTrace(false).GetFrame(0).GetMethod();
string methodName = m.DeclaringType.ToString() + ";" + m.Name;
CurrentSession.ReportError(methodName, ex.Message);
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void Filter(int?directionId, string name, int?typeId, int?teacherId, string activeGroupsName, string column, SortOrder?sortOrder)
{
try
{
var listGroups = from groups in db.Groups
join courses in db.Courses on groups.CourseId equals courses.Id
join directions in db.Directions on courses.DirectionId equals directions.Id
join teachers in db.Workers on groups.TeacherId equals teachers.Id into outerTeacher
from teachers in outerTeacher.DefaultIfEmpty()
join activities in db.ActivityGroups on groups.ActivityId equals activities.Id
join classes in db.Classes on groups.ClassId equals classes.Id into outerClass
from classes in outerClass.DefaultIfEmpty()
join branches in db.Housings on classes.HousingId equals branches.Id into outerBranch
from branches in outerBranch.DefaultIfEmpty()
join years in db.Years on groups.YearId equals years.Id into outer
from years in outer.DefaultIfEmpty()
select new ListGroups
{
GroupId = groups.Id,
DirectionId = directions.Id,
Direction = directions.Name,
TeacherId = teachers.Id,
ActivityId = activities.Id,
YearId = years.Id,
Name = groups.Name,
Branch = branches.Name,
Class = classes.Name,
Days = groups.Days,
Begin = groups.Begin,
DurationLesson = groups.DurationLesson,
Year = years.Name,
Teacher = teachers.Lastname + " " + teachers.Firstname.Substring(0, 1) + "." + teachers.Middlename.Substring(0, 1) + ".",
TwoTeachers = groups.TwoTeachers,
Students = db.Enrollments.Where(a => a.GroupId == groups.Id && a.DateExclusion == null).Count(),
Note = groups.Note,
Activity = activities.Name
};
if (directionId != null)
{
listGroups = listGroups.Where(d => d.DirectionId == directionId);
}
if (teacherId != null)
{
listGroups = listGroups.Where(d => d.TeacherId == teacherId);
}
if (name != null && name != "")
{
listGroups = listGroups.Where(e => e.Name.StartsWith(name));
}
if (typeId != null)
{
listGroups = listGroups.Where(d => d.YearId == typeId);
}
if (activeGroupsName == "Активные")
{
listGroups = listGroups.Where(a => a.Activity != "Закрытые");
}
else
{
listGroups = listGroups.Where(a => a.Activity == activeGroupsName);
}
//фильтрация по праву доступа к группам
if (!CurrentSession.CurrentUser.AllAccessGroups)
{
var listOpenGroups = db.AccessGroups.Where(a => a.WorkerId == CurrentSession.CurrentUser.Id).Select(a => a.GroupId).ToList();
listGroups = listGroups.Where(a => listOpenGroups.Contains(a.GroupId));
}
//сортировка
if (column != null)
{
switch (column)
{
case "Direction":
{
if (sortOrder == SortOrder.Ascending)
{
listGroups = listGroups.OrderBy(x => x.Direction);
}
else
{
listGroups = listGroups.OrderByDescending(x => x.Direction);
}
break;
}
case "Name":
{
if (sortOrder == SortOrder.Ascending)
{
listGroups = listGroups.OrderBy(x => x.Name);
}
else
{
listGroups = listGroups.OrderByDescending(x => x.Name);
}
break;
}
case "Days":
{
if (sortOrder == SortOrder.Ascending)
{
listGroups = listGroups.OrderBy(x => x.Days);
}
else
{
listGroups = listGroups.OrderByDescending(x => x.Days);
}
break;
}
case "DurationLesson":
{
if (sortOrder == SortOrder.Ascending)
{
listGroups = listGroups.OrderBy(x => x.DurationLesson);
}
else
{
listGroups = listGroups.OrderByDescending(x => x.DurationLesson);
}
break;
}
case "Year":
{
if (sortOrder == SortOrder.Ascending)
{
listGroups = listGroups.OrderBy(x => x.Year);
}
else
{
listGroups = listGroups.OrderByDescending(x => x.Year);
}
break;
}
case "Teacher":
{
if (sortOrder == SortOrder.Ascending)
{
listGroups = listGroups.OrderBy(x => x.Teacher);
}
else
{
listGroups = listGroups.OrderByDescending(x => x.Teacher);
}
break;
}
case "TwoTeachers":
{
if (sortOrder == SortOrder.Ascending)
{
listGroups = listGroups.OrderBy(x => x.TwoTeachers);
}
else
{
listGroups = listGroups.OrderByDescending(x => x.TwoTeachers);
}
break;
}
case "Note":
{
if (sortOrder == SortOrder.Ascending)
{
listGroups = listGroups.OrderBy(x => x.Note);
}
else
{
listGroups = listGroups.OrderByDescending(x => x.Note);
}
break;
}
case "Students":
{
if (sortOrder == SortOrder.Ascending)
{
listGroups = listGroups.OrderBy(x => x.Students);
}
else
{
listGroups = listGroups.OrderByDescending(x => x.Students);
}
break;
}
}
}
else
{
listGroups = listGroups.OrderBy(f => f.Direction);
}
dgvListGroups.DataSource = listGroups.ToList();
if (sortOrder != SortOrder.None)
{
dgvListGroups.Columns[sortColumn].HeaderCell.SortGlyphDirection = (SortOrder)sortOrder;
}
}
catch (Exception ex)
{
var m = new System.Diagnostics.StackTrace(false).GetFrame(0).GetMethod();
string methodName = m.DeclaringType.ToString() + ";" + m.Name;
CurrentSession.ReportError(methodName, ex.Message);
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void Filter(int?idGroup, int?idYear, string lastname, string activeGroups, int?privilege, DateTime dtStart, DateTime dtEnd, string column, SortOrder?sortOrder)
{
try
{
listStudents = from enroll in db.Enrollments
join privileges in db.Privileges on enroll.PrivilegeId equals privileges.Id into g
from privileges in g.DefaultIfEmpty()
join groups in db.Groups on enroll.GroupId equals groups.Id
join activity in db.ActivityGroups on groups.ActivityId equals activity.Id
join students in db.Students on enroll.StudentId equals students.Id
join statuses in db.Statuses on students.StatusId equals statuses.Id
join years in db.Years on groups.YearId equals years.Id
where enroll.DateExclusion == null && enroll.ExclusionId == null
select new Finance
{
StudentId = students.Id,
GroupId = groups.Id,
SchoolId = students.SchoolId,
EnrollId = enroll.Id,
PrivilegeId = enroll.PrivilegeId,
ActivityId = activity.Id,
YearId = years.Id,
Activity = activity.Name,
StatusId = students.StatusId,
Lastname = students.Lastname,
Firstname = students.Firstname,
Middlename = students.Middlename,
Sex = students.Sex,
NameGroup = groups.Name,
Year = years.Name,
Status = statuses.Name,
School = students.School.Name,
Class = students.Class,
Privilege = privileges.Name,
Accrual = (from scheds in db.Schedules
where scheds.Source == 2 && scheds.EnrollmentId == enroll.Id && scheds.DateBegin >= dtpStart.Value && scheds.DateBegin <= dtpEnd.Value
select new { SchedsSum = scheds.Value }).Sum(s => (double?)(s.SchedsSum)) ?? 0,
AccrualDiscount = (from scheds in db.Schedules
where scheds.Source == 2 && scheds.EnrollmentId == enroll.Id && scheds.DateBegin >= dtpStart.Value && scheds.DateBegin <= dtpEnd.Value
select new { SchedSum = scheds.Value - scheds.Discount }).Sum(s => (double?)(s.SchedSum)) ?? 0,
Payment = (from pays in db.Payments
where pays.EnrollmentId == enroll.Id && pays.DatePayment >= dtpStart.Value &&
pays.DatePayment <= dtpEnd.Value && pays.IsDeleted == false && pays.AdditionalPay == false
select new { PaySum = pays.ValuePayment }).Sum(s => (double?)(s.PaySum)) ?? 0,
};
if (idGroup != null)
{
listStudents = listStudents.Where(d => d.GroupId == idGroup);
}
if (lastname != null && lastname != "")
{
listStudents = listStudents.Where(e => e.Lastname.StartsWith(lastname));
}
if (privilege != null)
{
listStudents = listStudents.Where(e => e.PrivilegeId == privilege);
}
if (idYear != null)
{
listStudents = listStudents.Where(e => e.YearId == idYear);
}
if (activeGroups == "Активные")
{
listStudents = listStudents.Where(a => a.Activity != "Закрытые");
}
else
{
listStudents = listStudents.Where(a => a.Activity == activeGroups);
}
//сортировка
if (column != null)
{
switch (column)
{
case "Lastname":
{
if (sortOrder == SortOrder.Ascending)
{
listStudents = listStudents.OrderBy(x => x.Lastname);
}
else
{
listStudents = listStudents.OrderByDescending(x => x.Lastname);
}
break;
}
case "Firstname":
{
if (sortOrder == SortOrder.Ascending)
{
listStudents = listStudents.OrderBy(x => x.Firstname);
}
else
{
listStudents = listStudents.OrderByDescending(x => x.Firstname);
}
break;
}
case "Middlename":
{
if (sortOrder == SortOrder.Ascending)
{
listStudents = listStudents.OrderBy(x => x.Middlename);
}
else
{
listStudents = listStudents.OrderByDescending(x => x.Middlename);
}
break;
}
case "Status":
{
if (sortOrder == SortOrder.Ascending)
{
listStudents = listStudents.OrderBy(x => x.Status);
}
else
{
listStudents = listStudents.OrderByDescending(x => x.Status);
}
break;
}
case "NameGroup":
{
if (sortOrder == SortOrder.Ascending)
{
listStudents = listStudents.OrderBy(x => x.NameGroup);
}
else
{
listStudents = listStudents.OrderByDescending(x => x.NameGroup);
}
break;
}
case "Privilege":
{
if (sortOrder == SortOrder.Ascending)
{
listStudents = listStudents.OrderBy(x => x.Privilege);
}
else
{
listStudents = listStudents.OrderByDescending(x => x.Privilege);
}
break;
}
case "School":
{
if (sortOrder == SortOrder.Ascending)
{
listStudents = listStudents.OrderBy(x => x.School);
}
else
{
listStudents = listStudents.OrderByDescending(x => x.School);
}
break;
}
case "Class":
{
if (sortOrder == SortOrder.Ascending)
{
listStudents = listStudents.OrderBy(x => x.Class);
}
else
{
listStudents = listStudents.OrderByDescending(x => x.Class);
}
break;
}
case "Sex":
{
if (sortOrder == SortOrder.Ascending)
{
listStudents = listStudents.OrderBy(x => x.Sex);
}
else
{
listStudents = listStudents.OrderByDescending(x => x.Sex);
}
break;
}
case "Payment":
{
if (sortOrder == SortOrder.Ascending)
{
listStudents = listStudents.OrderBy(x => x.Payment);
}
else
{
listStudents = listStudents.OrderByDescending(x => x.Payment);
}
break;
}
case "AccrualDiscount":
{
if (sortOrder == SortOrder.Ascending)
{
listStudents = listStudents.OrderBy(x => x.AccrualDiscount);
}
else
{
listStudents = listStudents.OrderByDescending(x => x.AccrualDiscount);
}
break;
}
case "Accrual":
{
if (sortOrder == SortOrder.Ascending)
{
listStudents = listStudents.OrderBy(x => x.Accrual);
}
else
{
listStudents = listStudents.OrderByDescending(x => x.Accrual);
}
break;
}
}
}
else
{
listStudents = listStudents.OrderBy(f => f.Lastname);
}
dgvStudents.DataSource = listStudents.ToList();
///////////////////////расчет итогов/////////////////////////////
//количество групп
toolStripStatusLabel2.Text = dgvStudents.Rows.Count.ToString();
double summScheds = 0;
double summPays = 0;
double summDebt = 0;
double summPrepayment = 0;
foreach (DataGridViewRow row in dgvStudents.Rows)
{
//итого начислено со скидкой
summScheds += (double)row.Cells["AccrualDiscount"].Value;
//итого платежей
summPays += (double)row.Cells["Payment"].Value;
//итого долг
summDebt += ((double)row.Cells["Saldo"].Value > 0) ? (double)row.Cells["Saldo"].Value : 0;
//итого аванс
summPrepayment += ((double)row.Cells["Saldo"].Value < 0) ? (double)row.Cells["Saldo"].Value : 0;
}
if (sortOrder != SortOrder.None)
{
dgvStudents.Columns[sortColumn].HeaderCell.SortGlyphDirection = (SortOrder)sortOrder;
}
toolStripStatusLabel5.Text = summScheds.ToString("C", CultureInfo.CurrentCulture);
toolStripStatusLabel8.Text = summPays.ToString("C", CultureInfo.CurrentCulture);
toolStripStatusLabel9.Text = summDebt.ToString("C", CultureInfo.CurrentCulture);
toolStripStatusLabel11.Text = (-summPrepayment).ToString("C", CultureInfo.CurrentCulture);
}
catch (Exception ex)
{
var m = new System.Diagnostics.StackTrace(false).GetFrame(0).GetMethod();
string methodName = m.DeclaringType.ToString() + ";" + m.Name;
CurrentSession.ReportError(methodName, ex.Message);
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void Filter(int?directionId, string name, int?type, string activeGroupsName, string column, DateTime dtStart, DateTime dtEnd, SortOrder?sortOrder)
{
try
{
var listGroups = from groups in db.Groups
join courses in db.Courses on groups.CourseId equals courses.Id
join directions in db.Directions on courses.DirectionId equals directions.Id
join teachers in db.Workers on groups.TeacherId equals teachers.Id into outerTeacher
from teachers in outerTeacher.DefaultIfEmpty()
join activities in db.ActivityGroups on groups.ActivityId equals activities.Id
join years in db.Years on groups.YearId equals years.Id into outer
from years in outer.DefaultIfEmpty()
select new FinanceGroups
{
GroupId = groups.Id,
DirectionId = directions.Id,
Direction = directions.Name,
ActivityId = activities.Id,
YearId = years.Id,
CourseName = courses.Name,
Name = groups.Name,
Year = years.Name,
StudentsP = groups.PlanEnroll,
StudentsF = db.Enrollments.Where(a => a.GroupId == groups.Id && a.DateExclusion == null).Count(),
Activity = activities.Name,
Price = db.Schedules.Where(a => a.GroupId == groups.Id && a.Source == 2 && a.EnrollmentId == null && a.DateBegin >= dtpStart.Value.Date &&
a.DateBegin <= dtpEnd.Value.Date).Select(a => new { a.Value }).Sum(s => (double?)(s.Value)) ?? 0,
Accrual = (from scheds in db.Schedules
join enrolls in db.Enrollments on scheds.EnrollmentId equals enrolls.Id
where scheds.Source == 2 && scheds.EnrollmentId != null && scheds.GroupId == groups.Id && enrolls.DateExclusion == null &&
scheds.DateBegin >= dtpStart.Value.Date && scheds.DateBegin <= dtpEnd.Value.Date
select new { SchedSum = scheds.Value }).Sum(s => (double?)(s.SchedSum)) ?? 0,
AccrualDiscount = (Double?)(from scheds in db.Schedules
join enrolls in db.Enrollments on scheds.EnrollmentId equals enrolls.Id
where scheds.Source == 2 && scheds.EnrollmentId != null && scheds.GroupId == groups.Id && enrolls.DateExclusion == null &&
scheds.DateBegin >= dtpStart.Value.Date && scheds.DateBegin <= dtpEnd.Value.Date
select new { SchedSum = scheds.Value - scheds.Discount }).Sum(s => (double?)(s.SchedSum)) ?? 0,
Payment = (Double?)(from pays in db.Payments
join enrolls in db.Enrollments on pays.EnrollmentId equals enrolls.Id
where enrolls.GroupId == groups.Id && enrolls.DateExclusion == null && pays.IsDeleted == false && pays.AdditionalPay == false &&
pays.DatePayment >= dtpStart.Value && pays.DatePayment <= dtpEnd.Value
select new { PaySum = pays.ValuePayment }).Sum(s => (double?)(s.PaySum)) ?? 0,
};
if (directionId != null)
{
listGroups = listGroups.Where(d => d.DirectionId == directionId);
}
if (name != null && name != "")
{
listGroups = listGroups.Where(e => e.Name.StartsWith(name));
}
if (type != null)
{
listGroups = listGroups.Where(d => d.YearId == type);
}
if (activeGroupsName == "Активные")
{
listGroups = listGroups.Where(a => a.Activity != "Закрытые");
}
else
{
listGroups = listGroups.Where(a => a.Activity == activeGroupsName);
}
//сортировка
if (column != null)
{
switch (column)
{
case "Direction":
{
if (sortOrder == SortOrder.Ascending)
{
listGroups = listGroups.OrderBy(x => x.Direction);
}
else
{
listGroups = listGroups.OrderByDescending(x => x.Direction);
}
break;
}
case "Name":
{
if (sortOrder == SortOrder.Ascending)
{
listGroups = listGroups.OrderBy(x => x.Name);
}
else
{
listGroups = listGroups.OrderByDescending(x => x.Name);
}
break;
}
case "Year":
{
if (sortOrder == SortOrder.Ascending)
{
listGroups = listGroups.OrderBy(x => x.Year);
}
else
{
listGroups = listGroups.OrderByDescending(x => x.Year);
}
break;
}
case "StudentsF":
{
if (sortOrder == SortOrder.Ascending)
{
listGroups = listGroups.OrderBy(x => x.StudentsF);
}
else
{
listGroups = listGroups.OrderByDescending(x => x.StudentsF);
}
break;
}
}
}
else
{
listGroups = listGroups.OrderBy(f => f.Direction);
}
dgvListGroups.DataSource = listGroups.ToList();
///////////////////////расчет итогов/////////////////////////////
//количество групп
toolStripStatusLabel2.Text = dgvListGroups.Rows.Count.ToString();
double summScheds = 0;
double summPays = 0;
double summDebt = 0;
double summPrepayment = 0;
foreach (DataGridViewRow row in dgvListGroups.Rows)
{
//итого начислено со скидкой
summScheds += (double)row.Cells["AccrualDiscount"].Value;
//итого платежей
summPays += (double)row.Cells["Payment"].Value;
//итого долг
summDebt += ((double)row.Cells["Saldo"].Value > 0) ? (double)row.Cells["Saldo"].Value : 0;
//итого аванс
summPrepayment += ((double)row.Cells["Saldo"].Value < 0) ? (double)row.Cells["Saldo"].Value : 0;
}
if (sortOrder != SortOrder.None)
{
dgvListGroups.Columns[sortColumn].HeaderCell.SortGlyphDirection = (SortOrder)sortOrder;
}
toolStripStatusLabel4.Text = summScheds.ToString("C", CultureInfo.CurrentCulture);
toolStripStatusLabel6.Text = summPays.ToString("C", CultureInfo.CurrentCulture);
toolStripStatusLabel9.Text = summDebt.ToString("C", CultureInfo.CurrentCulture);
toolStripStatusLabel11.Text = (-summPrepayment).ToString("C", CultureInfo.CurrentCulture);
}
catch (Exception ex)
{
var m = new System.Diagnostics.StackTrace(false).GetFrame(0).GetMethod();
string methodName = m.DeclaringType.ToString() + ";" + m.Name;
CurrentSession.ReportError(methodName, ex.Message);
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void FinancesGroupsForm_Load(object sender, EventArgs e)
{
try
{
//загрузка фильтра направлений
var direction = db.Directions.Where(a => a.IsRemoved == false).ToList();
cbDirectionOfTraining.DataSource = direction;
cbDirectionOfTraining.DisplayMember = "Name";
cbDirectionOfTraining.ValueMember = "Id";
cbDirectionOfTraining.SelectedIndex = -1;
//загрузка фильтра тип обучения
var type = db.Years.Where(a => a.IsRemoved == false).OrderBy(a => a.SortIndex).ToList();
cbTypeGroup.DataSource = type;
cbTypeGroup.DisplayMember = "Name";
cbTypeGroup.ValueMember = "Id";
cbTypeGroup.SelectedIndex = -1;
//загрузка статусов групп
cbActivity.DataSource = db.ActivityGroups.Where(a => a.IsRemoved == false).ToList();
cbActivity.DisplayMember = "Name";
cbActivity.ValueMember = "Id";
//выбор активного статуса
var currentActivity = db.ActivityGroups.FirstOrDefault(a => a.Name == "Активные" && a.IsRemoved == false);
cbActivity.SelectedValue = currentActivity.Id;
//добавление программной сортировки
foreach (DataGridViewColumn col in dgvListGroups.Columns)
{
col.SortMode = DataGridViewColumnSortMode.Programmatic;
}
//загрузка списка слушателей
Filter(null, null, (int?)cbTypeGroup.SelectedValue, cbActivity.Text, sortColumn, dtpStart.Value, dtpEnd.Value, sortOrder);
//установка дат
var date = dtpStart.Value = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
date = date.AddMonths(1).AddDays(-1);
dtpEnd.Value = new DateTime(DateTime.Now.Year, DateTime.Now.Month, date.Day);
//оформление таблицы
dgvListGroups.Columns["GroupId"].Visible = dgvListGroups.Columns["DirectionId"].Visible =
dgvListGroups.Columns["ActivityId"].Visible =
dgvListGroups.Columns["Activity"].Visible = false;
dgvListGroups.Columns["Direction"].HeaderText = "Направление";
dgvListGroups.Columns["Name"].HeaderText = "Группа";
dgvListGroups.Columns["CourseName"].HeaderText = "Курс";
dgvListGroups.Columns["Year"].HeaderText = "Уч. год";
dgvListGroups.Columns["Price"].HeaderText = "Цена";
dgvListGroups.Columns["StudentsP"].HeaderText = "Учащ. План";
dgvListGroups.Columns["StudentsF"].HeaderText = "Учащ. Факт";
dgvListGroups.Columns["Accrual"].HeaderText = "Начислено";
dgvListGroups.Columns["AccrualDiscount"].HeaderText = "Начислено со скидкой";
dgvListGroups.Columns["Payment"].HeaderText = "Платежи";
dgvListGroups.Columns["PlanAccrual"].HeaderText = "План выручки";
dgvListGroups.Columns["Saldo"].HeaderText = "Остаток";
dgvListGroups.Focus(); dgvListGroups.Select();
}
catch (Exception ex)
{
var m = new System.Diagnostics.StackTrace(false).GetFrame(0).GetMethod();
string methodName = m.DeclaringType.ToString() + ";" + m.Name;
CurrentSession.ReportError(methodName, ex.Message);
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void DiscountForm_Load(object sender, EventArgs e)
{
try
{
//проверяем есть ли индивидуальной график платежей
var listSchedule = (from scheds in db.Schedules
where scheds.EnrollmentId == enroll.Id && scheds.Source == 2
orderby scheds.DateBegin
select new ListSchedules
{
Id = scheds.Id,
Discount = scheds.Discount,
Date = scheds.DateBegin,
Val = scheds.Value,
Note = scheds.Note
}).ToList();
//если индивидуального нет, то загружаем график группы
if (listSchedule.Count == 0)
{
listSchedule = (from scheds in db.Schedules
where scheds.GroupId == enroll.GroupId && scheds.Source == 2 && scheds.EnrollmentId == null
orderby scheds.DateBegin
select new ListSchedules
{
Id = scheds.Id,
Discount = scheds.Discount,
Date = scheds.DateBegin,
Val = scheds.Value,
Note = scheds.Note
}).ToList();
}
double summPay = 0, summDiscount = 0;
if (listSchedule.Count > 0)
{
foreach (var sch in listSchedule)
{
dgvSchedules.Rows.Add(sch.Id, false, sch.Date, sch.Val, sch.Discount, sch.Note);
summPay += sch.Val;
summDiscount += sch.Discount;
}
dgvSchedules.Columns["Date"].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
dgvSchedules.Columns["Date"].ReadOnly = true;
dgvSchedules.Columns["Check"].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
dgvSchedules.Columns["Val"].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
dgvSchedules.Columns["Val"].ReadOnly = true;
dgvSchedules.Columns["Discount"].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
dgvSchedules.Columns["Note"].ReadOnly = true;
label2.Text = summPay.ToString("C", CultureInfo.CurrentCulture);
label3.Text = summDiscount.ToString("C", CultureInfo.CurrentCulture);
}
if (enroll.PrivilegeId != null)
{
cbPrivilege.SelectedValue = enroll.PrivilegeId;
}
else
{
cbPrivilege.SelectedIndex = -1;
}
}
catch (Exception ex)
{
var m = new System.Diagnostics.StackTrace(false).GetFrame(0).GetMethod();
string methodName = m.DeclaringType.ToString() + ";" + m.Name;
CurrentSession.ReportError(methodName, ex.Message);
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btOk_Click(object sender, EventArgs e)
{
try
{
//сохранение скидки
if (ValidationDiscount() && cbPrivilege.Text == "")
{
MessageBox.Show("Не выбрано основание для предоставления скидки!", "Внимание", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
//сохранение основания предоставления скидки
Privilege currentPriviledge = new Privilege();
if (cbPrivilege.SelectedIndex == -1 && cbPrivilege.Text != String.Empty)
{
currentPriviledge.Name = cbPrivilege.Text;
db.Privileges.Add(currentPriviledge);
db.SaveChanges();
enroll.PrivilegeId = currentPriviledge.Id;
}
else
{
enroll.PrivilegeId = Convert.ToInt32(cbPrivilege.SelectedValue);
}
//сохранение начисленной скидки
List <Schedule> list = new List <Schedule>();
list = db.Schedules.Where(a => a.EnrollmentId == enroll.Id && a.Source == 2).
OrderBy(a => a.DateBegin).ToList();
//если есть индивидуaльный график платежей, то сохраняем в него
if (list.Count > 0)
{
for (int i = 0; i < dgvSchedules.RowCount; i++)
{
list[i].Discount = Convert.ToDouble(dgvSchedules.Rows[i].Cells["Discount"].Value);
list[i].Note = (dgvSchedules.Rows[i].Cells["Note"].Value != null)?dgvSchedules.Rows[i].Cells["Note"].Value.ToString():null;
db.Entry(list[i]).State = System.Data.Entity.EntityState.Modified;
}
db.SaveChanges();
}
else
{
for (int i = 0; i < dgvSchedules.RowCount; i++)
{
list.Add(new Schedule
{
Discount = Convert.ToDouble(dgvSchedules.Rows[i].Cells["Discount"].Value),
Value = Convert.ToDouble(dgvSchedules.Rows[i].Cells["Val"].Value),
Source = 2,
DateBegin = Convert.ToDateTime(dgvSchedules.Rows[i].Cells["Date"].Value),
EnrollmentId = enroll.Id,
WorkerId = CurrentSession.CurrentUser.Id,
GroupId = enroll.GroupId,
Note = (dgvSchedules.Rows[i].Cells["Note"].Value != null) ? dgvSchedules.Rows[i].Cells["Note"].Value.ToString() : null
});
}
db.Schedules.AddRange(list);
db.SaveChanges();
}
Close();
}
}
catch (Exception ex)
{
var m = new System.Diagnostics.StackTrace(false).GetFrame(0).GetMethod();
string methodName = m.DeclaringType.ToString() + ";" + m.Name;
CurrentSession.ReportError(methodName, ex.Message);
MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}