private static WorkingCirriculumViewModel AnalizeWorkingCirriculumFile(DBContext context, Stream stream)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, false))
{
WorkingCirriculumViewModel data = AnalizeTitleSheet(context, document);
data.AcademicDisciplines = AnalizePlanSheet(context, document);
return(data);
}
}
private static WorkingCirriculumViewModel AnalizeTitleSheet(DBContext context, SpreadsheetDocument document)
{
string sheetName = "Титул";
WorkbookPart workbookPart = document.WorkbookPart;
Sheet sheet = workbookPart.Workbook.Descendants <Sheet>().FirstOrDefault(s => s.Name == sheetName);
WorksheetPart worksheetPart = (WorksheetPart)(workbookPart.GetPartById(sheet.Id));
SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First();
WorkingCirriculumViewModel workingCirriculum = new WorkingCirriculumViewModel();
AreaOfTrainingViewModel areaOfTraining = new AreaOfTrainingViewModel();
string typeOfEducationSearch = "форма обучения";
string trainingPeriodSearch = "срок обучения";
string startPeriodSearch = "год начала подготовки";
string educationalStandartSearch = "образовательный стандарт";
string departmentWord = "кафедра";
string levelOfHigherEducationWord = "квалификация";
int areaOfTrainingRowIndex = 0;
bool needToStopForStartPeriod = false;
bool needToStopForEducationalStandart = false;
bool needToSearchDepartmentId = false;
bool needToStopForAreaOfTrainingCode = true;
bool needSearchAreaOfTraining = false;
foreach (Row row in sheetData.Elements <Row>())
{
foreach (Cell cell in row.Elements <Cell>())
{
string value = GetCellValue(workbookPart, cell);
if (value.ToLower().Contains(typeOfEducationSearch))
{
workingCirriculum.TypeOfEducationName = GetTypeOfEducationName(value);
}
else if (value.ToLower().Contains(trainingPeriodSearch))
{
workingCirriculum.TrainingPeriod = GetTrainingPeriod(value);
}
else if (value.ToLower().Contains(startPeriodSearch))
{
needToStopForStartPeriod = true;
}
else if (int.TryParse(value, out int tempStartYear) && needToStopForStartPeriod)
{
workingCirriculum.StartTraining = tempStartYear;
needToStopForStartPeriod = false;
}
else if (value.ToLower().Contains(educationalStandartSearch))
{
needToStopForEducationalStandart = true;
}
else if (!string.IsNullOrEmpty(value) && needToStopForEducationalStandart)
{
workingCirriculum.EducationalStandart = value;
needToStopForEducationalStandart = false;
}
else if (needToStopForAreaOfTrainingCode && value.Split('.').Length > 1 && value.Length <= 8)
{
if (context.AreasOfTraining.Where(o => o.Code == value).AsNoTracking().Count() > 0)
{
areaOfTraining = context.AreasOfTraining.Where(o => o.Code == value).AsNoTracking().Select(o => new AreaOfTrainingViewModel
{
Id = o.Id,
Code = o.Code,
Directionaly = o.Directionaly,
LevelOfHigherEducationName = o.LevelOfHigherEducationName,
Name = o.Name,
ShortName = o.ShortName
})
.FirstOrDefault();
needSearchAreaOfTraining = false;
}
else
{
areaOfTraining.Code = value;
needSearchAreaOfTraining = true;
}
needToStopForAreaOfTrainingCode = false;
areaOfTrainingRowIndex = int.Parse(row.RowIndex.ToString());
}
else if (needSearchAreaOfTraining && (int.Parse(row.RowIndex) - areaOfTrainingRowIndex == 1) && !string.IsNullOrEmpty(value))
{
areaOfTraining.Name = GetAreaOfTrainingName(value);
areaOfTraining.ShortName = GetAreaOfTrainingShortName(areaOfTraining.Name);
}
else if (needSearchAreaOfTraining &&
(int.Parse(row.RowIndex) - areaOfTrainingRowIndex == 2) &&
!string.IsNullOrEmpty(value))
{
areaOfTraining.Directionaly = GetAreaOfTrainingDirectionaly(value);
}
else if (needSearchAreaOfTraining && value.ToLower().Contains(levelOfHigherEducationWord))
{
string levelOfHigherEducationName = GetLevelOfHigherEducationName(value);
areaOfTraining.LevelOfHigherEducationName = context.LevelsOfHigherEducation.Where(o =>
o.Name.ToLower().Contains(levelOfHigherEducationName.ToLower())).AsNoTracking().FirstOrDefault().Name;
}
else if (value.ToLower().Contains(departmentWord))
{
needToSearchDepartmentId = true;
}
else if (needToSearchDepartmentId && !string.IsNullOrEmpty(value))
{
var tempDepartment = context.Departments.Where(o => o.Name.ToLower().Contains(value.ToLower())).Select(o => new { o.Id, o.Name }).FirstOrDefault();
workingCirriculum.DepartmentId = tempDepartment.Id;
workingCirriculum.DepartmentName = tempDepartment.Name;
areaOfTraining.DepartmentId = tempDepartment.Id;
areaOfTraining.DepartmentName = tempDepartment.Name;
needToSearchDepartmentId = false;
}
}
}
workingCirriculum.AreaOfTraining = areaOfTraining;
return(workingCirriculum);
}
