public void sortGroupMath()
{
StudentRating tmp = new StudentRating();
for (int i = 0; i < this.length; i++)
{
for (int j = i + 1; j < this.length; j++)
{
if (String.Equals(sr[i].getGroup(), sr[j].getGroup()))
{
tmp = sr[i];
sr[i] = sr[j];
sr[j] = tmp;
}
if (sr[i].getGroup() == sr[j].getGroup())
{
if (sr[i].getMath() > sr[j].getMath())
{
tmp = sr[i];
sr[i] = sr[j];
sr[j] = tmp;
}
}
}
}
}
public void std(int length)
{
for (int i = 0; i < this.length; i++)
{
sr[i] = new StudentRating("null", "null", 0, 0, 0, 0);
}
}
public void sortGradebook()
{
StudentRating tmp = new StudentRating();
for (int i = 0; i < this.length; i++)
{
for (int j = i + 1; j < this.length; j++)
{
if (sr[i].getGradebook() > sr[j].getGradebook())
{
tmp = sr[i];
sr[i] = sr[j];
sr[j] = tmp;
}
}
}
}
public ActionResult Create(StudentRatingFormViewModel viewModel)
{
if (!ModelState.IsValid)
{
viewModel.Internships = _unitOfWork.Internships.GetInternships();
viewModel.RatingTypes = _unitOfWork.RatingTypes.GetRatingTypes();
return(View(viewModel));
}
var studentRating = new StudentRating()
{
StudentInternshipId = viewModel.StudentInternship,
RatingTypeId = viewModel.RatingType,
Comment = viewModel.Comment,
Date = DateTime.Today
};
_unitOfWork.StudentRatings.Add(studentRating);
_unitOfWork.Complete();
return(RedirectToAction("Index", "StudentRatings"));
}
public void Remove(StudentRating StudentRating)
{
_context.StudentRatings.Remove(StudentRating);
}
public void Add(StudentRating StudentRating)
{
_context.StudentRatings.Add(StudentRating);
}
public async Task <IActionResult> DownloadCurrentRating(int groupId, int disciplineId)
{
var ratingControls = await _context.RatingControls
.Include(x => x.StudentRatings)
.Where(x => x.SemesterDisciplineId == disciplineId && x.GroupId == groupId)
.OrderBy(x => x.Number)
.ToListAsync();
var group = await _context.AcademicGroups
.Include(x => x.Specialty)
.Include(x => x.Students.OrderBy(y => y.FullName))
.ThenInclude(x => x.ActivityProtections
.Where(y => _context.Activities
.Where(z => z.SemesterDisciplineId == disciplineId)
.Any(h => y.ActivityId == h.Id)))
.FirstOrDefaultAsync(x => x.Id == groupId);
var semesterDiscipline = await _context.SemesterDiscipline
.Include(x => x.Discipline)
.ThenInclude(y => y.TeacherDisciplines)
.ThenInclude(z => z.Teacher)
.Include(x => x.Activities.OrderBy(x => x.Type).ThenBy(x => x.Number))
.ThenInclude(y => y.Type)
.FirstOrDefaultAsync(x => x.Id == disciplineId);
var studentsCount = group.Students.Count();
var activitiesCount = semesterDiscipline.Activities.Count();
var pointMultiplier = 1f;
var examMultiplier = 1f;
Activity exam = null;
var bodyRow = 5;
var footerRow = bodyRow + studentsCount;
var multiplierCol = 12; var multiplierRow = footerRow + 2;
if (semesterDiscipline.ControlType == ControlType.Exam)
{
pointMultiplier = ((float)(60f / semesterDiscipline.Activities.Where(x => x.Type.Name != EXAMTYPENAME).Select(x => x.MaxPoints).Sum()));
exam = semesterDiscipline.Activities.FirstOrDefault(x => x.Type.Name == EXAMTYPENAME);
examMultiplier = (float)(40f / exam.MaxPoints);
}
else
{
pointMultiplier = ((float)(100f / semesterDiscipline.Activities.Select(x => x.MaxPoints).Sum()));
}
//нерасширяемо, лучше сделать шаблоны
using (var workbook = new XLWorkbook())
{
//
// TABLE HEADER
//
var worksheet = workbook.Worksheets.Add("Текущий рейтинг контроль");
var currentRow = 1;
worksheet.Cell(currentRow, 1).Value = "гр.";
worksheet.Cell(currentRow, 2).Value = group.Name; worksheet.Cell(currentRow, 2).Style.Fill.BackgroundColor = XLColor.FromArgb(254, 0, 254);
worksheet.Cell(currentRow, 2).Style.Font.Bold = true;
worksheet.Cell(currentRow, 3).Value = "Расчетная ведомость рейтинга:";
worksheet.Range(worksheet.Cell(currentRow, 3), worksheet.Cell(currentRow, 3 + 6)).Merge();
worksheet.Range(worksheet.Cell(currentRow, 3 + 6 + 1), worksheet.Cell(currentRow, 3 + 6 + 1 + 7)).Merge();
worksheet.Range(worksheet.Cell(currentRow, 3 + 6 + 1), worksheet.Cell(currentRow, 3 + 6 + 1 + 7)).Style.Fill.BackgroundColor = XLColor.FromArgb(254, 0, 254);
worksheet.Cell(currentRow, 3 + 6 + 1).Value = semesterDiscipline.Discipline.Name;
currentRow++;
worksheet.Cell(currentRow, 1).Value = "сп.";
worksheet.Cell(currentRow, 2).Value = group.Specialty.Name; worksheet.Cell(currentRow, 2).Style.Fill.BackgroundColor = XLColor.FromArgb(254, 0, 254);
var currentCol = 3;
var r = new Random();
foreach (var activityType in semesterDiscipline.Activities.Select(x => x.Type).Distinct())
{
var length = semesterDiscipline.Activities.Where(x => x.TypeId == activityType.Id).Count() - 1;
worksheet.Range(worksheet.Cell(currentRow, currentCol), worksheet.Cell(currentRow, currentCol + length)).Merge();
worksheet.Cell(currentRow, currentCol).Value = Abbreviaturization(activityType.Name);
worksheet.Cell(currentRow, currentCol).Style.Font.Bold = true;
worksheet.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Range(worksheet.Cell(currentRow + 1, currentCol), worksheet.Cell(currentRow + 2 + group.Students.Count(), currentCol + length)).Style.Fill.BackgroundColor = XLColor.FromArgb(100 + r.Next(155), 100 + r.Next(155), 100 + r.Next(155));
currentCol += length + 1;
}
currentRow++;
currentCol = 3 + semesterDiscipline.Activities.Count();
worksheet.Cell(currentRow, currentCol).Value = "Итого";
worksheet.Cell(currentRow, currentCol).Style.Font.Bold = true;
worksheet.Cell(currentRow, currentCol).Style.Alignment.TextRotation = 90;
worksheet.Cell(currentRow, currentCol).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Cell(currentRow, currentCol).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Column(currentCol).Width = 6;
worksheet.Range(worksheet.Cell(currentRow + 2, currentCol), worksheet.Cell(currentRow + 2 + group.Students.Count(), currentCol)).Style.Fill.BackgroundColor = XLColor.FromArgb(51, 102, 255);
worksheet.Cell(currentRow + 1, currentCol).Value = 100;
worksheet.Cell(currentRow + 1, currentCol).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Cell(currentRow + 1, currentCol).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Cell(currentRow + 1, currentCol).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
currentCol++;
foreach (var item in ratingControls)
{
worksheet.Cell(currentRow - 1, currentCol).Value = item.Number + " рейтинг";
worksheet.Cell(currentRow - 1, currentCol).Style.Font.FontSize = 8;
worksheet.Cell(currentRow, currentCol).Value = item.CompletionDate.Date;
worksheet.Cell(currentRow, currentCol).Style.Alignment.TextRotation = 90;
worksheet.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(currentRow, currentCol).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Cell(currentRow, currentCol).Style.Font.FontSize = 9;
worksheet.Cell(currentRow, currentCol).Style.Fill.BackgroundColor = XLColor.FromArgb(254, 0, 254);
worksheet.Cell(currentRow + 1, currentCol).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Cell(currentRow + 1, currentCol).Value = $"#{item.Number}";
worksheet.Cell(currentRow + 1, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(currentRow + 1, currentCol).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Column(currentCol).Width = 5;
currentCol++;
}
if (exam != null)
{
worksheet.Cell(currentRow, currentCol).Value = exam.Type.Name;
worksheet.Cell(currentRow, currentCol).Style.Alignment.TextRotation = 90;
worksheet.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(currentRow, currentCol).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Cell(currentRow, currentCol).Style.Font.FontSize = 9;
worksheet.Cell(currentRow, currentCol).Style.Fill.BackgroundColor = XLColor.FromArgb(254, 0, 254);
worksheet.Cell(currentRow + 1, currentCol).Value = "ЭКЗ";
worksheet.Cell(currentRow + 1, currentCol).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
worksheet.Cell(currentRow + 1, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Cell(currentRow + 1, currentCol).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Column(currentCol).Width = 5;
currentCol++;
}
worksheet.Cell(currentRow, currentCol).Value = "Оценка";
worksheet.Cell(currentRow, currentCol).Style.Alignment.TextRotation = 90;
worksheet.Cell(currentRow, currentCol).Style.Font.Bold = true;
worksheet.Cell(currentRow, currentCol).Style.Border.LeftBorder = XLBorderStyleValues.Double;
worksheet.Cell(currentRow, currentCol).Style.Border.RightBorder = XLBorderStyleValues.Double;
if (semesterDiscipline.ControlType == ControlType.Exam)
{
worksheet.Cell(currentRow + 1, currentCol).Value = 5;
worksheet.Range(worksheet.Cell(currentRow + 2, currentCol), worksheet.Cell(currentRow + 2 + group.Students.Count(), currentCol))
.AddConditionalFormat().WhenEquals("5").Fill.SetBackgroundColor(XLColor.FromArgb(0, 176, 80));
worksheet.Range(worksheet.Cell(currentRow + 2, currentCol), worksheet.Cell(currentRow + 2 + group.Students.Count(), currentCol))
.AddConditionalFormat().WhenEquals("4").Fill.SetBackgroundColor(XLColor.FromArgb(255, 245, 60));
worksheet.Range(worksheet.Cell(currentRow + 2, currentCol), worksheet.Cell(currentRow + 2 + group.Students.Count(), currentCol))
.AddConditionalFormat().WhenEquals("3").Fill.SetBackgroundColor(XLColor.FromArgb(255, 80, 80));
}
else
{
worksheet.Cell(currentRow + 1, currentCol).Value = "Зач";
worksheet.Range(worksheet.Cell(currentRow + 2, currentCol), worksheet.Cell(currentRow + 2 + group.Students.Count(), currentCol))
.AddConditionalFormat().WhenEquals("Зач").Fill.SetBackgroundColor(XLColor.FromArgb(0, 176, 80));
}
worksheet.Cell(currentRow + 1, currentCol).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Cell(currentRow + 1, currentCol).Style.Border.LeftBorder = XLBorderStyleValues.Double;
worksheet.Cell(currentRow + 1, currentCol).Style.Border.RightBorder = XLBorderStyleValues.Double;
worksheet.Column(currentCol).Width = 5;
currentCol++;
currentCol++;
var startFactPointsCol = currentCol; currentRow--;
foreach (var activityType in semesterDiscipline.Activities.Select(x => x.Type).Distinct())
{
var length = semesterDiscipline.Activities.Where(x => x.TypeId == activityType.Id).Count() - 1;
worksheet.Range(worksheet.Cell(currentRow, currentCol), worksheet.Cell(currentRow, currentCol + length)).Merge();
worksheet.Cell(currentRow, currentCol).Value = Abbreviaturization(activityType.Name);
worksheet.Cell(currentRow, currentCol).Style.Font.Bold = true;
worksheet.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
worksheet.Range(worksheet.Cell(currentRow + 1, currentCol), worksheet.Cell(currentRow + 2 + group.Students.Count(), currentCol + length)).Style.Fill.BackgroundColor = XLColor.FromArgb(100 + r.Next(155), 100 + r.Next(155), 100 + r.Next(155));
currentCol += length + 1;
}
currentCol = 3;
foreach (var activity in semesterDiscipline.Activities)
{
worksheet.Cell(currentRow + 1, currentCol).Value = $"№{activity.Number}";
if (activity.Id == exam.Id)
{
worksheet.Cell(currentRow + 2, currentCol).FormulaA1 = $"{worksheet.Cell(currentRow + 2, startFactPointsCol + currentCol - 3).Address}*{worksheet.Cell(multiplierRow + 1, multiplierCol).Address}";
}
else
{
worksheet.Cell(currentRow + 2, currentCol).FormulaA1 = $"{worksheet.Cell(currentRow + 2, startFactPointsCol + currentCol - 3).Address}*{worksheet.Cell(multiplierRow, multiplierCol).Address}";
}
worksheet.Cell(currentRow + 2, currentCol).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Column(currentCol).Width = 4;
currentCol++;
}
currentRow++;
currentCol = startFactPointsCol;
foreach (var item in semesterDiscipline.Activities)
{
worksheet.Cell(currentRow, currentCol).Value = $"№{item.Number}";
worksheet.Cell(currentRow + 1, currentCol).Value = item.MaxPoints;
worksheet.Cell(currentRow + 1, currentCol).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Column(currentCol).Width = 4;
currentCol++;
}
worksheet.Row(currentRow).Height = 50;
currentRow++;
worksheet.Cell(currentRow, 1).Value = "№"; worksheet.Cell(currentRow, 1).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Cell(currentRow, 2).Value = "Ф.И.О. студента"; worksheet.Cell(currentRow, 2).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
worksheet.Row(currentRow).Height = 20;
//
// TABLE BODY
//
currentRow = bodyRow;
var i = 1;
var factPointsOffset = startFactPointsCol - 3;
foreach (var student in group.Students)
{
worksheet.Cell(currentRow, 1).Value = i++;
worksheet.Cell(currentRow, 2).Value = student.FullName;
worksheet.Cell(currentRow, 2).Style.Border.RightBorder = XLBorderStyleValues.Thin;
currentCol = 3;
foreach (var activity in semesterDiscipline.Activities)
{
if (activity.Type.Name == EXAMTYPENAME)
{
worksheet.Cell(currentRow, currentCol).FormulaA1 = $"{worksheet.Cell(currentRow, currentCol + factPointsOffset).Address}*{worksheet.Cell(multiplierRow + 1, multiplierCol).Address}";
}
else
{
worksheet.Cell(currentRow, currentCol).FormulaA1 = $"{worksheet.Cell(currentRow, currentCol + factPointsOffset).Address}*{worksheet.Cell(multiplierRow, multiplierCol).Address}";
}
currentCol++;
}
currentCol = 3 + semesterDiscipline.Activities.Count();
worksheet.Cell(currentRow, currentCol).FormulaA1 = $"SUM({worksheet.Cell(currentRow, currentCol - 1).Address}:{worksheet.Cell(currentRow, currentCol - semesterDiscipline.Activities.Count).Address})";
worksheet.Cell(currentRow, currentCol).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
worksheet.Cell(currentRow, currentCol).Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Cell(currentRow, currentCol).Style.Font.Bold = true;
var totalCol = currentCol;
currentCol++;
foreach (var ratingControl in ratingControls)
{
StudentRating studentRating = ratingControl.StudentRatings.FirstOrDefault(x => x.StudentId == student.GradebookNumber);
if (studentRating != null)
{
worksheet.Cell(currentRow, currentCol).Value = studentRating.Points;
worksheet.Cell(currentRow, currentCol).Style.Font.Bold = true;
}
currentCol++;
}
if (semesterDiscipline.ControlType == ControlType.Exam)
{
var examProtection = student.ActivityProtections.Where(x => x.Activity.Id == exam.Id).FirstOrDefault();
worksheet.Cell(currentRow, currentCol).FormulaA1 = $"{worksheet.Cell(currentRow, startFactPointsCol + activitiesCount - 1).Address}*{worksheet.Cell(multiplierRow + 1, multiplierCol).Address}";
worksheet.Cell(currentRow, currentCol).Style.Font.Bold = true;
currentCol++;
}
var totalCell = worksheet.Cell(currentRow, totalCol).Address;
worksheet.Cell(currentRow, currentCol).Style.Border.LeftBorder = XLBorderStyleValues.Double;
worksheet.Cell(currentRow, currentCol).Style.Border.RightBorder = XLBorderStyleValues.Double;
worksheet.Cell(currentRow, currentCol).Style.Font.Bold = true;
if (semesterDiscipline.ControlType == ControlType.Exam)
{
worksheet.Cell(currentRow, currentCol).FormulaA1 = $"IF({totalCell}>90,5,IF({totalCell}>73,4,IF({totalCell}>60,3,2)))";
}
else
{
worksheet.Cell(currentRow, currentCol).FormulaA1 = $"IF({totalCell}>60,\"Зач\",\"Нез\")";
}
currentCol++;
currentCol++;
foreach (var activity in semesterDiscipline.Activities)
{
ActivityProtection activityProtection = student.ActivityProtections.FirstOrDefault(x => x.Activity == activity);
if (activityProtection != null)
{
worksheet.Cell(currentRow, currentCol).Value = activityProtection.Points;
}
currentCol++;
}
currentRow++;
}
//
// TABLE FOOTER
//
currentRow = footerRow;
worksheet.Row(currentRow).Style.Fill.BackgroundColor = XLColor.FromArgb(51, 51, 51); currentRow++;
currentRow++;
worksheet.Cell(currentRow, 2).Value = "Преподаватель";
worksheet.Cell(currentRow, 3).Value = semesterDiscipline.Discipline.TeacherDisciplines.FirstOrDefault(x => x.DisciplineId == semesterDiscipline.DisciplineId).Teacher.FullName;
worksheet.Range(worksheet.Cell(currentRow, 3), worksheet.Cell(currentRow, 3 + 6)).Merge();
worksheet.Range(worksheet.Cell(currentRow, 3), worksheet.Cell(currentRow, 3 + 6)).Style.Fill.BackgroundColor = XLColor.FromArgb(254, 0, 254);
worksheet.Cell(multiplierRow, multiplierCol - 1).Value = "МР"; worksheet.Cell(multiplierRow, multiplierCol).Value = pointMultiplier;
if (semesterDiscipline.ControlType == ControlType.Exam)
{
worksheet.Cell(multiplierRow + 1, multiplierCol - 1).Value = "МЭ"; worksheet.Cell(multiplierRow + 1, multiplierCol).Value = examMultiplier;
}
worksheet.Column(1).AdjustToContents();
worksheet.Column(2).AdjustToContents();
using (var stream = new MemoryStream())
{
workbook.SaveAs(stream);
var content = stream.ToArray();
return(File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", group.Name + ".xlsx"));
}
}
}
public async Task <IActionResult> CreateConfirm(int?groupId, int?disciplineId)
{
if (groupId == null || disciplineId == null)
{
return(NotFound());
}
var lastNumber = 0;
var groupRatingControls = _context.RatingControls.Where(x => x.GroupId == groupId && x.SemesterDisciplineId == disciplineId);
if (groupRatingControls.Any())
{
lastNumber = groupRatingControls.OrderByDescending(x => x.Number).FirstOrDefault().Number;
}
var newRatingControl = new RatingControl();
newRatingControl.Number = lastNumber + 1;
newRatingControl.GroupId = (int)groupId;
newRatingControl.SemesterDisciplineId = (int)disciplineId;
newRatingControl.CompletionDate = DateTime.Now;
_context.Add(newRatingControl);
await _context.SaveChangesAsync();
var activities = _context.SemesterDiscipline
.Include(x => x.Activities)
.ThenInclude(y => y.Type)
.Where(x => x.Id == disciplineId)
.SelectMany(x => x.Activities)
.AsNoTracking()
.AsEnumerable();
var pointMultiplier = 1f;
if (_context.SemesterDiscipline.Find(disciplineId).ControlType == ControlType.Exam)
{
var exam = activities.FirstOrDefault(x => x.Type.Name == EXAMTYPENAME);
if (exam != null)
{
activities.ToList().Remove(exam);
}
pointMultiplier = ((float)(60f / activities.Select(x => x.MaxPoints).Sum()));
}
else
{
pointMultiplier = ((float)(100f / activities.Select(x => x.MaxPoints).Sum()));
}
var students = _context.AcademicGroups
.Include(x => x.Students)
.ThenInclude(x => x.ActivityProtections
.Where(y => activities
.Any(z => y.ActivityId == z.Id)))
.Where(x => x.Id == groupId)
.SelectMany(x => x.Students);
var studentsRating = new List <StudentRating>();
foreach (var student in students)
{
var studentRating = new StudentRating();
studentRating.RatingId = newRatingControl.Id;
studentRating.StudentId = student.GradebookNumber;
studentRating.Points = (float)(Math.Round(student.ActivityProtections
.Select(x => x.Points)
.Sum() * pointMultiplier
, 1));
studentsRating.Add(studentRating);
}
_context.AddRange(studentsRating);
await _context.SaveChangesAsync();
return(RedirectToAction(nameof(Group), new { id = groupId, disciplineId = disciplineId }));
}
