public async Task <IActionResult> ImportFullSeason(IFormFile theExcel) { ExcelPackage excel; int seasonID = 0; using (var memoryStream = new MemoryStream()) { await theExcel.CopyToAsync(memoryStream); excel = new ExcelPackage(memoryStream); } var workSheet = excel.Workbook.Worksheets[0]; var start = workSheet.Dimension.Start; var end = workSheet.Dimension.End; for (int row = start.Row; row <= end.Row; row++) { if (workSheet.Cells[row, 1].Text == "SEASON") { var checkExistSeason = _context.Seasons.FirstOrDefault(d => d.Name == workSheet.Cells[row, 2].Text); if (checkExistSeason == null) { Season season = new Season { Name = workSheet.Cells[row, 2].Text, StartDate = Convert.ToDateTime(workSheet.Cells[row, 4].Value), EndDate = Convert.ToDateTime(workSheet.Cells[row, 6].Value) }; _context.Seasons.Add(season); _context.SaveChanges(); seasonID = _context.Seasons.FirstOrDefault(d => d.Name == workSheet.Cells[row, 2].Text).ID; } } if (workSheet.Cells[row, 1].Text == "DIVISION") { var checkExistDivison = _context.Divisions.FirstOrDefault(d => d.Name == workSheet.Cells[row, 2].Text); if (checkExistDivison == null) { Division division = new Division { Name = workSheet.Cells[row, 2].Text, PositionNo = Convert.ToInt16(workSheet.Cells[row, 4].Text) }; _context.Divisions.Add(division); _context.SaveChanges(); } } if (workSheet.Cells[row, 1].Text == "TEAM") { var checkExistVenue = _context.Venues.FirstOrDefault(d => d.Name == workSheet.Cells[row, 4].Text); if (checkExistVenue == null) { Venue venue = new Venue { Name = workSheet.Cells[row, 4].Text }; _context.Venues.Add(venue); _context.SaveChanges(); } int venueID = _context.Venues.FirstOrDefault(d => d.Name == workSheet.Cells[row, 4].Text).ID; var checkExistTeam = _context.Teams.FirstOrDefault(d => d.Name == workSheet.Cells[row, 2].Text); if (checkExistTeam == null) { Team team = new Team { Name = workSheet.Cells[row, 2].Text, VenueID = venueID }; _context.Teams.Add(team); _context.SaveChanges(); } int teamID = _context.Teams.FirstOrDefault(d => d.Name == workSheet.Cells[row, 2].Text).ID; int divisionID = _context.Divisions.FirstOrDefault(d => d.Name == workSheet.Cells[row, 6].Text).ID; var checkExistSeasonDivisionTeam = _context.SeasonDivisionTeams.FirstOrDefault(d => d.SeasonID == seasonID && d.DivisionID == divisionID && d.TeamID == teamID); if (checkExistSeasonDivisionTeam == null) { SeasonDivisionTeam seasonDivisionTeam = new SeasonDivisionTeam { SeasonID = seasonID, DivisionID = divisionID, TeamID = teamID }; _context.SeasonDivisionTeams.Add(seasonDivisionTeam); _context.SaveChanges(); } } if (workSheet.Cells[row, 1].Text == "CAPTAIN") { var checkExistCaptain = _context.Players.FirstOrDefault(d => d.Email == workSheet.Cells[row, 5].Text); if (checkExistCaptain == null) { Player captain = new Player { FirstName = workSheet.Cells[row, 2].Text, LastName = workSheet.Cells[row, 3].Text, DOB = Convert.ToDateTime(workSheet.Cells[row, 4].Value), Email = workSheet.Cells[row, 5].Text, //PositionID = _context.Positions.FirstOrDefault(d => d.Name == workSheet.Cells[row, 6].Text).ID, PositionID = _context.Positions.FirstOrDefault(d => d.Name.Contains(workSheet.Cells[row, 6].Text)).ID, TeamID = _context.Teams.FirstOrDefault(d => d.Name == workSheet.Cells[row, 8].Text).ID }; IdentityUser newUser = new IdentityUser { UserName = captain.Email, Email = captain.Email }; IdentityResult result = _userManager.CreateAsync(newUser, "password").Result; if (result.Succeeded) { _userManager.AddToRoleAsync(newUser, "User").Wait(); } //var user = await _userManager.FindByEmailAsync(captain.Email); var newRoleToCaptain = _userManager.AddToRoleAsync(newUser, "Captain"); _context.Players.Add(captain); _context.SaveChanges(); if (workSheet.Cells[row, 8].Text == "FALSE") { captain.IsEnabled = false; await _userManager.SetLockoutEndDateAsync(newUser, DateTimeOffset.MaxValue); } Team teamToUpdate = _context.Teams.FirstOrDefault(d => d.Name == workSheet.Cells[row, 8].Text); int captainID = _context.Players.FirstOrDefault(d => d.Email == captain.Email).ID; teamToUpdate.CaptainID = captainID; _context.Teams.Update(teamToUpdate); } } if (workSheet.Cells[row, 1].Text == "PLAYER") { var checkExistPlayer = _context.Players.FirstOrDefault(d => d.Email == workSheet.Cells[row, 5].Text); if (checkExistPlayer == null) { Player player = new Player { FirstName = workSheet.Cells[row, 2].Text, LastName = workSheet.Cells[row, 3].Text, DOB = Convert.ToDateTime(workSheet.Cells[row, 4].Value), Email = workSheet.Cells[row, 5].Text, PositionID = _context.Positions.FirstOrDefault(d => d.Name.Contains(workSheet.Cells[row, 6].Text)).ID, TeamID = _context.Teams.FirstOrDefault(d => d.Name == workSheet.Cells[row, 8].Text).ID }; IdentityUser newUser = new IdentityUser { UserName = player.Email, Email = player.Email }; IdentityResult result = _userManager.CreateAsync(newUser, "password").Result; if (result.Succeeded) { _userManager.AddToRoleAsync(newUser, "User").Wait(); } if (workSheet.Cells[row, 8].Text == "FALSE") { player.IsEnabled = false; await _userManager.SetLockoutEndDateAsync(newUser, DateTimeOffset.MaxValue); } _context.Players.Add(player); _context.SaveChanges(); } } //Fixture fixture = new Fixture //{ // SeasonID = _context.Seasons.FirstOrDefault(d => d.Name == workSheet.Cells[row, 1].Text).ID, // DivisionID = _context.Divisions.FirstOrDefault(d => d.Name == workSheet.Cells[row, 2].Text).ID, // HomeTeamID = _context.Teams.FirstOrDefault(d => d.Name == workSheet.Cells[row, 3].Text).ID, // AwayTeamID = _context.Teams.FirstOrDefault(d => d.Name == workSheet.Cells[row, 4].Text).ID, // Date = Convert.ToDateTime(workSheet.Cells[row, 5].Value), // Time = Convert.ToDateTime(workSheet.Cells[row, 6].Text), // VenueID = _context.Venues.FirstOrDefault(d => d.Name == workSheet.Cells[row, 7].Text).ID //}; //_context.Fixtures.Add(fixture); } ; _context.SaveChanges(); return(RedirectToAction(nameof(Index))); }
public async Task <IActionResult> Create([Bind("ID,Name, CaptainID, VenueID, Profile")] Team team, IFormFile thePicture, int SeasonID, int DivisionID) { bool error = false; if (SeasonID == 0) { if (DivisionID == 0) { ModelState.AddModelError("", "Please, select a Season and Division"); error = true; } else { ModelState.AddModelError("", "Please, select a Season"); error = true; } } else if (SeasonID == 0) { ModelState.AddModelError("", "Please, select a Division"); error = true; } if (error == true) { PopulateDropDownListCaptain(); PopulateDropDownListVenue(); PopulateDropDownListSeason(); PopulateDropDownListDivision(); return(View()); } try { if (ModelState.IsValid) { if (thePicture != null) { string mimeType = thePicture.ContentType; long fileLength = thePicture.Length; if (!(mimeType == "" || fileLength == 0)) { if (mimeType.Contains("image")) { using (var memoryStream = new MemoryStream()) { await thePicture.CopyToAsync(memoryStream); team.imageContent = memoryStream.ToArray(); } team.imageMimeType = mimeType; team.imageFileName = thePicture.FileName; } } } _context.Add(team); await _context.SaveChangesAsync(); int teamID = _context.Teams.FirstOrDefault(d => d.Name == team.Name).ID; var checkExistSeasonDivisionTeam = _context.SeasonDivisionTeams.FirstOrDefault(d => d.SeasonID == SeasonID && d.DivisionID == DivisionID && d.TeamID == teamID); if (checkExistSeasonDivisionTeam == null) { SeasonDivisionTeam seasonDivisionTeam = new SeasonDivisionTeam { SeasonID = SeasonID, DivisionID = DivisionID, TeamID = teamID }; _context.SeasonDivisionTeams.Add(seasonDivisionTeam); _context.SaveChanges(); } return(RedirectToAction(nameof(Index))); } } catch (Exception err) { ModelState.AddModelError("", "Unable to save changes. Try again, and if the problem persists see your system administrator." + err.ToString()); } //ViewData["CaptainID"] = new SelectList(_context.Players, "ID", "Email", team.CaptainID); PopulateDropDownListCaptain(team); PopulateDropDownListVenue(team); //ViewData["VenueID"] = new SelectList(_context.Venues, "ID", "Name", team.VenueID); return(View(team)); }