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