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); }