public async Task <ResponceExcelService> ImportFromExcel(IFormFile file) { var responce = new ResponceExcelService(); //try //{ responce.IsOk = true; ICollection <ImportDataDto> result = new List <ImportDataDto>(); using (var stream = new MemoryStream()) { file.CopyTo(stream); System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance); using var reader = ExcelReaderFactory.CreateReader(stream); var subjectNames = new List <string>(); reader.Read(); reader.Read(); for (int i = _subjectIndexStart; i < reader.FieldCount; i++) { var str = reader.GetString(i); if (!string.IsNullOrEmpty(str)) { subjectNames.Add(str); } } if (subjectNames.Count > 13) { responce.IsOk = false; responce.ErrorMesage = "В файле присутствуют предметы, которых нет в списке. Скачайте шаблон и заполните его."; return(responce); } while (reader.Read()) { if ( string.IsNullOrEmpty(reader.GetString(0)?.Trim()) || string.IsNullOrEmpty(reader.GetString(1)?.Trim()) || string.IsNullOrEmpty(reader.GetString(4)?.Trim())) { continue; } var data = new ImportDataDto(); data.UniversityName = reader.GetString(0)?.Trim(); data.DivisionName = reader.GetString(1)?.Trim(); data.Level = reader.GetString(2)?.Trim(); data.Code = reader.GetString(3)?.Trim(); data.DirectionName = reader.GetString(4)?.Trim(); data.PeriodOfStudy = reader.IsDBNull(5)? 0: reader.GetDouble(5); data.Form = reader.GetString(6)?.Trim(); data.BudgetPlacesCount = reader.IsDBNull(7)? 0: reader.GetDouble(7); data.PaidPlacesCount = reader.IsDBNull(8)? 0: reader.GetDouble(8); data.Price = reader.IsDBNull(9)? 0: reader.GetDouble(9); data.Subjects = new List <SubjectImport>(); for (int i = 0; i < reader.FieldCount - _subjectIndexStart; i++) { if (!reader.IsDBNull(i + _subjectIndexStart)) { var sub = new SubjectImport { Name = subjectNames[i]?.Trim(), Score = reader.IsDBNull(i + _subjectIndexStart)? 0 : reader.GetDouble(i + _subjectIndexStart) }; data.Subjects.Add(sub); } } result.Add(data); } } responce.AddItemCount = result.Count; if (responce.IsOk) { var responceSave = await SaveToDatabase(result); if (!responceSave.IsOk) { responce.IsOk = false; responce.ErrorMesage = responceSave.ErrorMesage; } } return(responce); //} //catch (Exception ex) //{ // var errorMessage = $"SaveToDatabase.\n{ex}"; // _logger.LogWarning(errorMessage); // responce.IsOk = false; // responce.ErrorMesage = ex.Message; // return responce; //} }
private async Task <ResponceExcelService> SaveToDatabase(ICollection <ImportDataDto> dataDtos) { var responce = new ResponceExcelService(); try { var subjects = dataDtos .SelectMany(x => x.Subjects.Select(s => s.Name)) .Distinct().ToList(); var subjectsInDb = _dbContext.Subjects.Select(s => s.Name).ToList(); if (!subjects.All(s => subjectsInDb.Any(x => x == s))) { responce.IsOk = false; responce.ErrorMesage = "В файле присутствуют предметы, которых нет в списке. Скачайте шаблон и заполните его."; return(responce); } ICollection <University> universities = new List <University>(); var data = dataDtos.GroupBy(x => x.UniversityName, (u, us) => new { UniversityName = u, Divisions = us.GroupBy(d => d.DivisionName, (d, ds) => new { DivisionName = d, Directions = ds.Select(dir => new { DirectionName = dir.DirectionName, Budjet = dir.BudgetPlacesCount, Payment = dir.PaidPlacesCount, Price = dir.Price, Level = dir.Level, Form = dir.Form, Code = dir.Code, Period = dir.PeriodOfStudy, Subjects = dir.Subjects }).ToList() }).ToList() }).ToList(); var now = DateTime.Now; foreach (var item in data) { University university = await _dbContext.Universities .Include(u => u.Divisions) .FirstOrDefaultAsync(u => u.Name == item.UniversityName); if (university == null) { university = new University(); university.Name = item.UniversityName; university.CreateDateTime = university.ModifiedDateTime = now; university.Divisions = new List <EducationalDivision>(); _dbContext.Universities.Add(university); await _dbContext.SaveChangesAsync(); } foreach (var division in item.Divisions) { EducationalDivision educationalDivision = await _dbContext.Divisions .Include(x => x.EducationalDirections) .FirstOrDefaultAsync(d => d.Name == division.DivisionName && d.UniversityId == university.Id); if (educationalDivision == null) { educationalDivision = new EducationalDivision(); educationalDivision.Name = division.DivisionName; educationalDivision.CreateDateTime = educationalDivision.ModifiedDateTime = now; educationalDivision.EducationalDirections = new List <EducationalDirection>(); university.Divisions.Add(educationalDivision); await _dbContext.SaveChangesAsync(); } foreach (var direction in division.Directions) { var form = direction.Form.GetEnumFromString <EducationalFormEnum>(); var level = direction.Level.GetEnumFromString <EducationalLevelEnum>(); EducationalDirection educationalDirection = await _dbContext.Directions .Include(x => x.SubjectScores) .FirstOrDefaultAsync(x => x.Name == direction.DirectionName && x.EducationalDivisionId == educationalDivision.Id && x.EducationalForm == form && x.Level == level); if (educationalDirection == null) { educationalDirection = new EducationalDirection(); educationalDirection.Name = direction.DirectionName; educationalDirection.CreateDateTime = educationalDirection.ModifiedDateTime = now; educationalDivision.EducationalDirections.Add(educationalDirection); } else { educationalDirection.ModifiedDateTime = now; } educationalDirection.Level = level; educationalDirection.EducationalForm = form; educationalDirection.BudgetPlacesCount = (int)direction.Budjet; educationalDirection.PaidPlacesCount = (int)direction.Payment; educationalDirection.PeriodOfStudy = direction.Period; educationalDirection.Price = (int)direction.Price; educationalDirection.Code = direction.Code; if (educationalDirection.SubjectScores?.Any() == true) { educationalDirection.SubjectScores.Clear(); } else if (educationalDirection.SubjectScores == null) { educationalDirection.SubjectScores = new List <SubjectScore>(); } await _dbContext.SaveChangesAsync(); foreach (var subjectDto in direction.Subjects) { var subject = await _dbContext.Subjects.FirstOrDefaultAsync(x => x.Name == subjectDto.Name); if (subject != null) { var subjectScore = new SubjectScore(); subjectScore.MinimumScore = (int)subjectDto.Score; subjectScore.CreateDateTime = subjectScore.ModifiedDateTime = now; subjectScore.Subject = subject; educationalDirection.SubjectScores.Add(subjectScore); } } } } } await _dbContext.SaveChangesAsync(); responce.IsOk = true; return(responce); } catch (Exception ex) { var errorMessage = $"SaveToDatabase.\n{ex}"; _logger.LogWarning(errorMessage); responce.IsOk = false; responce.ErrorMesage = ex.Message; return(responce); } }