private static bool GetMarkByStudentName(SubjectForAtestat subjectForAtestat, int semestr, string studentName, Group @group,
out RecordStudmark markWithStudName)
{
markWithStudName = subjectForAtestat.Semestrs[semestr].Marks.Find(studmark =>
ComparePibs(studentName, studmark.StudentName));
if (markWithStudName != null) return false;
Student student = @group.Students.Find(student1 =>
ComparePibs(student1.Pib, studentName) || ComparePibs(student1.PibChanged, studentName));
if (student == null)
return true;
markWithStudName = subjectForAtestat.Semestrs[semestr].Marks.Find(studmark =>
ComparePibs(studmark.StudentName, student.Pib)
|| ComparePibs(studmark.StudentName, student.PibChanged));
return markWithStudName == null;
}
private static void CreatePvyForOneSubject(Worksheet sheet, string groupName, SubjectForAtestat subjectForAtestat)
{
Log.Info(LoggerConstants.ENTER);
var group = GetGroupByName(groupName);
if (group == null)
{
Log.Info(LoggerConstants.EXIT);
return;
}
byte countOfStudent = 0;
sheet.Cells[6, "B"].Value = "з дисципліни " + subjectForAtestat.SubjectName;
var groupSpexific = "Група " + groupName;
if (groupName.Split('-').Length == 3)
groupSpexific += "(" + groupName.Split('-')[1] + ")";
sheet.Cells[7, "B"].Value = groupSpexific;
sheet.Cells[8, "B"].Value = "Спеціальність: \"" + group.Speciality + "\"";
sheet.Cells[9, "B"].Value = "Викладач " + subjectForAtestat.Teacher;
foreach (var student in @group.Students)
{
sheet.Cells[12 + countOfStudent, "C"].Value = student.GetPib();
countOfStudent++;
}
subjectForAtestat.Semestrs = subjectForAtestat.Semestrs.OrderBy(semestr => semestr.Semestr).ToList();
var markPositionColumn = 'D';
byte countOfSemestrWithoutStateExame = 0;
var columnOfStateExame = -1;
for (byte i = 0; i < subjectForAtestat.Semestrs.Count; i++)
{
if (subjectForAtestat.Semestrs[i].StateExamenExist)
{
columnOfStateExame = i;
continue;
}
countOfSemestrWithoutStateExame++;
sheet.Cells[10, markPositionColumn.ToString()].Value = subjectForAtestat.Semestrs[i].CountOfHours;
sheet.Cells[11, markPositionColumn.ToString()].Value = ArabToRome(subjectForAtestat.Semestrs[i].Semestr) +
" семестр Оцінка в балах";
byte markPositionRow = 12;
for (byte i2 = 1; i2 <= countOfStudent; i2++)
{
var studentName = sheet.Cells[markPositionRow + i2 - 1, "C"].Value + "";
if (string.IsNullOrWhiteSpace(studentName))
continue;
RecordStudmark markWithStudName;
if (GetMarkByStudentName(subjectForAtestat, i, studentName, @group, out markWithStudName))
continue;
sheet.Cells[markPositionRow + i2 - 1, markPositionColumn.ToString()].Value = markWithStudName.Mark;
}
markPositionColumn++;
}
if (countOfSemestrWithoutStateExame == 0)
{
Log.Info(LoggerConstants.EXIT);
return;
}
var average = markPositionColumn;
average--;
sheet.Cells[10, markPositionColumn.ToString()].Formula = "=SUM(D10:" + average + 10 + ")";
sheet.Cells[11, markPositionColumn.ToString()].Value = "Підсумкова оцінка";
for (var i = 0; i < countOfStudent; i++)
{
var formula = "0";
switch (countOfSemestrWithoutStateExame)
{
case 1:
formula = "(D10*D" + (i + 12) + ")/" + markPositionColumn + "10";
break;
case 2:
formula = "(D10*D" + (i + 12) + "+E10*E" + (i + 12) + ")/" + markPositionColumn + "10";
break;
case 3:
formula = "(D10*D" + (i + 12) + "+E10*E" + (i + 12) + "+F10*F" + (i + 12) + ")/" + markPositionColumn + "10";
break;
case 4:
formula = "(D10*D" + (i + 12) + "+E10*E" + (i + 12) + "+F10*F" + (i + 12) + "+G10*G" + (i + 12) +
")/" + markPositionColumn + "10";
break;
}
string cellValue = sheet.Cells[12 + i, (char)(markPositionColumn - (char)1) + ""].Value + "";
double tryPasre;
sheet.Cells[12 + i, markPositionColumn.ToString()].Value =
double.TryParse(cellValue.Trim(), out tryPasre)
? "=ROUND(" + formula + ", 0)"
: cellValue;
}
// insert stateExamen
if (columnOfStateExame >= 0)
{
markPositionColumn++;
sheet.Cells[11, markPositionColumn.ToString()].Value = "Державна підсумкова атестація";
for (byte i = 0; i < subjectForAtestat.Semestrs[columnOfStateExame].Marks.Count; i++)
{
var studentName = sheet.Cells[12 + i, "C"].Value + "";
if (string.IsNullOrWhiteSpace(studentName))
break;
RecordStudmark markWithStudName;
if (GetMarkByStudentName(subjectForAtestat, columnOfStateExame, studentName, @group, out markWithStudName))
continue;
sheet.Cells[12 + i, markPositionColumn.ToString()].Value = markWithStudName.Mark;
}
}
// delete range of cells
if (countOfStudent < 30)
sheet.Range["A" + (12 + countOfStudent), "IV41"].Delete();
markPositionColumn++;
if (markPositionColumn < 'J')
sheet.Range[markPositionColumn.ToString() + 1, "I65536"].Delete();
Log.Info(LoggerConstants.EXIT);
}
