public async Task Create(GroupDisciplineLoad model) { try { _logger.LogInformation("Trying to execute sql create group discipline load query"); model.Id = await QuerySingleOrDefaultAsync <int>(@" insert into GroupDisciplineLoad ( DepartmentLoadId, DisciplineTitleId, StudentGroupId, FacultyName, SemesterNumber, StudyWeeksCount, Amount ) values ( @DepartmentLoadId, @DisciplineTitleId, @StudentGroupId, @FacultyName, @SemesterNumber, @StudyWeeksCount, @Amount ); select SCOPE_IDENTITY(); ", model); _logger.LogInformation("Sql create group discipline load query successfully executed"); } catch (Exception exception) { _logger.LogError(exception.Message); throw exception; } }
public async Task Update(GroupDisciplineLoad model) { try { _logger.LogInformation("Trying to execute sql update group discipline load query"); await ExecuteAsync(@" update GroupDisciplineLoad set DepartmentLoadId = @DepartmentLoadId, DisciplineTitleId = @DisciplineTitleId, StudentGroupId = @StudentGroupId, FacultyName = @FacultyName, SemesterNumber = @SemesterNumber, StudyWeeksCount = @StudyWeeksCount, Amount = @Amount from GroupDisciplineLoad where Id = @Id ", model); _logger.LogInformation("Sql update group discipline load query successfully executed"); } catch (Exception exception) { _logger.LogError(exception.Message); throw exception; } }
public async Task <GroupDisciplineLoad> Update(GroupDisciplineLoad model) { await _groupDisciplineLoadDao.Update(model); await UpdateStudyLoad(model.Id, model.StudyLoad); return(model); }
private void GetLoadFromSheet( DepartmentLoad departmentLoad, ISheet loadSheet, List <StudyDirection> studyDirections, List <Department> faculties, List <DisciplineTitle> disciplinesTitles, List <StudentGroup> studentGroups ) { #region Define columns numbers const int disciplineTitleColumn = 1; const int semesterColumn = 2; const int studyDirectionColumn = 3; const int facultyColumn = 4; const int courseColumn = 5; const int studentGroupColumn = 6; const int studentsCountColumn = 7; const int groupsInStreamColumn = 8; const int studyWeeksColumn = 9; const int lectionsColumn = 10; const int practicalLessonsColumn = 11; const int laboratoryLessonsColumn = 12; const int thematicalDiscussionsColumn = 13; const int consultasionsColumn = 14; const int examsColumn = 15; const int offsetsColumn = 16; const int otherColumn = 17; const int abstractsColumn = 18; const int esTestPapersColumn = 19; const int stateExamsColumn = 20; const int postgraduateExamsColumn = 21; const int practicesColumn = 22; const int departmentManagementColumn = 23; const int studentReserachWorkColumn = 24; const int courseWorksColumn = 25; const int graduationQualificationManagementColumn = 26; const int masterProgramManagementColumn = 27; const int postgraduateProgramManagementColumn = 28; const int amountColumn = 29; #endregion int?firstRowIndex = GetFirstRowIndex(loadSheet); if (!firstRowIndex.HasValue) { throw new ApplicationException("Ошибка формата файла."); } int?lastRowIndex = GetLastRowIndex(loadSheet, firstRowIndex.Value); if (!lastRowIndex.HasValue) { throw new ApplicationException("Ошибка формата файла."); } int firstRow = firstRowIndex.Value + 1; for (int i = firstRow; i < lastRowIndex; i++) { var row = loadSheet.GetRow(i); CheckRow(row); #region Define required cells var disciplineTitleCell = row.GetCell(disciplineTitleColumn); var semesterCell = row.GetCell(semesterColumn); var studyDirectionCell = row.GetCell(studyDirectionColumn); var facultyCell = row.GetCell(facultyColumn); var courseCell = row.GetCell(courseColumn); var studentGroupCell = row.GetCell(studentGroupColumn); var studentsCountCell = row.GetCell(studentsCountColumn); var groupsInStreamCell = row.GetCell(groupsInStreamColumn); var studyWeeksCell = row.GetCell(studyWeeksColumn); var lectionsCell = row.GetCell(lectionsColumn); var practicalLessonsCell = row.GetCell(practicalLessonsColumn); var laboratoryLessonsCell = row.GetCell(laboratoryLessonsColumn); var thematicalDiscussionsCell = row.GetCell(thematicalDiscussionsColumn); var consultasionsCell = row.GetCell(consultasionsColumn); var examsCell = row.GetCell(examsColumn); var offsetsCell = row.GetCell(offsetsColumn); var otherCell = row.GetCell(otherColumn); var abstractCell = row.GetCell(abstractsColumn); var esTestPapersCell = row.GetCell(esTestPapersColumn); var stateExamsCell = row.GetCell(stateExamsColumn); var postgraduateExamsCell = row.GetCell(postgraduateExamsColumn); var practicesCell = row.GetCell(practicesColumn); var departmentManagementCell = row.GetCell(departmentManagementColumn); var studentReserachWorkCell = row.GetCell(studentReserachWorkColumn); var courseWorksCell = row.GetCell(courseWorksColumn); var graduationQualificationManagementCell = row.GetCell(graduationQualificationManagementColumn); var masterProgramManagementCell = row.GetCell(masterProgramManagementColumn); var postgraduateProgramManagementCell = row.GetCell(postgraduateProgramManagementColumn); var amountCell = row.GetCell(amountColumn); #endregion #region Check required cells CheckCell(disciplineTitleCell); CheckCell(semesterCell); CheckCell(studyDirectionCell); CheckCell(facultyCell); CheckCell(courseCell); CheckCell(studentGroupCell); CheckCell(studentsCountCell); CheckCell(groupsInStreamCell); CheckCell(studyWeeksCell); #endregion #region Get values from cells string tempStartYear = Regex.Match(studentGroupCell.StringCellValue, @"[а-я,А-Я]\d{2}").Value; int startYear = int.Parse(Regex.Match(tempStartYear, @"\d{2}").Value); string directionCode = Regex.Match(studyDirectionCell.StringCellValue, @"\d{2}.\d{2}.\d{2}").Value; var studyDirection = studyDirections.Find(o => directionCode == o.Code); var title = new DisciplineTitle { DepartmentId = departmentLoad.DepartmentId, Name = disciplineTitleCell.StringCellValue, Shortname = disciplineTitleCell.StringCellValue.GetShortening() }; if (!disciplinesTitles.Any(o => o.Name == title.Name)) { disciplinesTitles.Add(title); } if (studyDirection != null) { var group = new StudentGroup { CurrentCourse = Convert.ToInt32(courseCell.NumericCellValue), Name = studentGroupCell.StringCellValue, StartYear = startYear, StudentsCount = Convert.ToInt32(studentsCountCell.NumericCellValue), StudyDirection = studyDirection, StudyDirectionId = studyDirection.Id }; if (!studentGroups.Any(o => o.Name == group.Name)) { studentGroups.Add(group); } int semesterNumber = Convert.ToInt32(semesterCell.NumericCellValue); int studyWeeksCount = Convert.ToInt32(studyWeeksCell.NumericCellValue); double amount = amountCell.NumericCellValue; var groupDisciplineLoad = new GroupDisciplineLoad { DisciplineTitle = title, DisciplineTitleId = title.Id, FacultyName = facultyCell.StringCellValue, SemesterNumber = semesterNumber, StudentGroup = group, StudentGroupId = group.Id, StudyWeeksCount = studyWeeksCount, StudyLoad = new List <StudyLoad>(), Amount = amount }; if (lectionsCell != null) { groupDisciplineLoad.StudyLoad.Add(new StudyLoad { Value = GetValueFromCell(lectionsCell), ShownValue = GetShownValueFromCell(lectionsCell), ProjectType = ProjectType.Lection }); } if (practicalLessonsCell != null) { groupDisciplineLoad.StudyLoad.Add(new StudyLoad { Value = GetValueFromCell(practicalLessonsCell), ShownValue = GetShownValueFromCell(practicalLessonsCell), ProjectType = ProjectType.PracticalLesson }); } if (laboratoryLessonsCell != null) { groupDisciplineLoad.StudyLoad.Add(new StudyLoad { Value = GetValueFromCell(laboratoryLessonsCell), ShownValue = GetShownValueFromCell(laboratoryLessonsCell), ProjectType = ProjectType.LaboratoryLesson }); } if (thematicalDiscussionsCell != null) { groupDisciplineLoad.StudyLoad.Add(new StudyLoad { Value = GetValueFromCell(thematicalDiscussionsCell), ShownValue = GetShownValueFromCell(thematicalDiscussionsCell), ProjectType = ProjectType.ThematicalDiscussion }); } if (consultasionsCell != null) { groupDisciplineLoad.StudyLoad.Add(new StudyLoad { Value = GetValueFromCell(consultasionsCell), ShownValue = GetShownValueFromCell(consultasionsCell), ProjectType = ProjectType.Consultation }); } if (examsCell != null) { groupDisciplineLoad.StudyLoad.Add(new StudyLoad { Value = GetValueFromCell(examsCell), ShownValue = GetShownValueFromCell(examsCell), ProjectType = ProjectType.Exam }); } if (offsetsCell != null) { groupDisciplineLoad.StudyLoad.Add(new StudyLoad { Value = GetValueFromCell(offsetsCell), ShownValue = GetShownValueFromCell(offsetsCell), ProjectType = ProjectType.Offest }); } if (otherCell != null) { groupDisciplineLoad.StudyLoad.Add(new StudyLoad { Value = GetValueFromCell(otherCell), ShownValue = GetShownValueFromCell(otherCell), ProjectType = ProjectType.Other }); } if (abstractCell != null) { groupDisciplineLoad.StudyLoad.Add(new StudyLoad { Value = GetValueFromCell(abstractCell), ShownValue = GetShownValueFromCell(abstractCell), ProjectType = ProjectType.Abstract }); } if (esTestPapersCell != null) { groupDisciplineLoad.StudyLoad.Add(new StudyLoad { Value = GetValueFromCell(esTestPapersCell), ShownValue = GetShownValueFromCell(esTestPapersCell), ProjectType = ProjectType.EsTestPapers }); } if (stateExamsCell != null) { groupDisciplineLoad.StudyLoad.Add(new StudyLoad { Value = GetValueFromCell(stateExamsCell), ShownValue = GetShownValueFromCell(stateExamsCell), ProjectType = ProjectType.StateExam }); } if (postgraduateExamsCell != null) { groupDisciplineLoad.StudyLoad.Add(new StudyLoad { Value = GetValueFromCell(postgraduateExamsCell), ShownValue = GetShownValueFromCell(postgraduateExamsCell), ProjectType = ProjectType.PostgraduateEntranceExam }); } if (practicesCell != null) { groupDisciplineLoad.StudyLoad.Add(new StudyLoad { Value = GetValueFromCell(practicesCell), ShownValue = GetShownValueFromCell(practicesCell), ProjectType = ProjectType.Practice }); } if (departmentManagementCell != null) { groupDisciplineLoad.StudyLoad.Add(new StudyLoad { Value = GetValueFromCell(departmentManagementCell), ShownValue = GetShownValueFromCell(departmentManagementCell), ProjectType = ProjectType.DepartmentManagement }); } if (studentReserachWorkCell != null) { groupDisciplineLoad.StudyLoad.Add(new StudyLoad { Value = GetValueFromCell(studentReserachWorkCell), ShownValue = GetShownValueFromCell(studentReserachWorkCell), ProjectType = ProjectType.StudentResearchWork }); } if (courseWorksCell != null) { groupDisciplineLoad.StudyLoad.Add(new StudyLoad { Value = GetValueFromCell(courseWorksCell), ShownValue = GetShownValueFromCell(courseWorksCell), ProjectType = ProjectType.CourseWork }); } if (graduationQualificationManagementCell != null) { groupDisciplineLoad.StudyLoad.Add(new StudyLoad { Value = GetValueFromCell(graduationQualificationManagementCell), ShownValue = GetShownValueFromCell(graduationQualificationManagementCell), ProjectType = ProjectType.GraduationQualificationManagement }); } if (masterProgramManagementCell != null) { groupDisciplineLoad.StudyLoad.Add(new StudyLoad { Value = GetValueFromCell(masterProgramManagementCell), ShownValue = GetShownValueFromCell(masterProgramManagementCell), ProjectType = ProjectType.MasterProgramManagement }); } if (postgraduateProgramManagementCell != null) { groupDisciplineLoad.StudyLoad.Add(new StudyLoad { Value = GetValueFromCell(postgraduateProgramManagementCell), ShownValue = GetShownValueFromCell(postgraduateProgramManagementCell), ProjectType = ProjectType.PostgraduateProgramManagement }); } departmentLoad.GroupDisciplineLoad.Add(groupDisciplineLoad); } #endregion } }