public ActionResult Upload(UploadedExcelSheetViewModel viewmodel) { if (ModelState.IsValid) // validate file exist { if (viewmodel.ExcelFile != null && viewmodel.ExcelFile.ContentLength > 0) { var fileName = Path.GetFileName(viewmodel.ExcelFile.FileName); var path = Path.Combine(Server.MapPath("~/Uploads/PaySources/"), DateTime.Now.GetTimeStamp() + "_" + fileName); List <PaySource> addedPaySources = new List <PaySource>(); viewmodel.ExcelFile.SaveAs(path); // save a copy of the uploaded file. // convert the uploaded file into datatable, then add/update db entities. var dtServers = ImportUtils.ImportXlsxToDataTable(viewmodel.ExcelFile.InputStream, true); int numOfPaySourcesUpdated = 0; foreach (var row in dtServers.AsEnumerable().ToList()) { var entityViewModel = new PaySourceAddViewModel() { VendorId = int.Parse(row["PaySourceId"].ToString()), // some columns does not have ',' separater. Description = row["Description"].ToString(), //Active = row["active"].ToString() == "Y" ? true : false, }; //check if paysource does not exist if (!string.IsNullOrWhiteSpace(row["PaySourceId"].ToString())) { var existedEntity = _paySourceService.GetByVendorId(entityViewModel.VendorId); if (existedEntity == null) { var entity = Mapper.Map <PaySourceAddViewModel, PaySource>(entityViewModel); addedPaySources.Add(entity); } else { Mapper.Map(entityViewModel, existedEntity); _paySourceService.UpdatePaySource(existedEntity); numOfPaySourcesUpdated++; } } } if (addedPaySources.Any()) { _paySourceService.AddPaySources(addedPaySources); } Success($"<strong>{addedPaySources.Count}</strong> PaySources have been successfully added. <br\\>" + $"<strong>{numOfPaySourcesUpdated}</strong> PaySources have been successfully updated."); } return(RedirectToAction("Index")); } return(View(viewmodel)); }
public ActionResult Upload(UploadedExcelSheetViewModel viewmodel) { if (ModelState.IsValid) // validate file exist { if (viewmodel.ExcelFile != null && viewmodel.ExcelFile.ContentLength > 0) { var fileName = Path.GetFileName(viewmodel.ExcelFile.FileName); var path = Path.Combine(Server.MapPath("~/Uploads/ServerTimeEntries/"), DateTime.Now.GetTimeStamp() + "_" + fileName); List <ServerTimeEntry> timeEntries = new List <ServerTimeEntry>(); viewmodel.ExcelFile.SaveAs(path); // save a copy of the uploaded file. // convert the uploaded file into datatable, then add/update db entities. var columnsToImport = new string[] { "Server ID", "Current Pay Source", "Begin Date", "Duration" }; var dtServers = ImportUtils.ImportXlsxToDataTable(viewmodel.ExcelFile.InputStream, true, columnsToImport); var invalidServers = new List <int>(); var invalidPaysources = new List <int>(); foreach (var row in dtServers.AsEnumerable().ToList()) { var timeEntryViewModel = new ServerTimeEntryAddViewModel() { ServerId = int.Parse(row["Server ID"].ToString()), PaySourceId = int.Parse(row["Current Pay Source"].ToString()), BeginDate = DateTime.Parse(row["Begin Date"].ToString()), Duration = TimeSpan.Parse(row["Duration"].ToString()) }; var existedServer = _serverService.GetByVendorId(timeEntryViewModel.ServerId); if (existedServer == null) { if (!invalidServers.Any(t => t == timeEntryViewModel.ServerId)) { invalidServers.Add(timeEntryViewModel.ServerId); } } var existedPaySource = _paySourceService.GetByVendorId(timeEntryViewModel.PaySourceId); if (existedPaySource == null) { if (!invalidPaysources.Any(t => t == timeEntryViewModel.PaySourceId)) { invalidPaysources.Add(timeEntryViewModel.PaySourceId); } } if (existedServer != null && existedPaySource != null) { // check if entity already exists. var entity = Mapper.Map <ServerTimeEntryAddViewModel, ServerTimeEntry>(timeEntryViewModel); entity.ServerId = existedServer.Id; entity.PaySourceId = existedPaySource.Id; entity.ProgramId = existedPaySource.Programs.Any() ? existedPaySource.Programs.ToList()[0].Id : (int?)null; if (!_serverTimeEntryService.TimeEntryExists(entity)) { timeEntries.Add(entity); } } } if (invalidServers.Any() || invalidPaysources.Any()) { invalidServers.ForEach(invalidServerId => { ModelState.AddModelError("", $"Invalid Server Id with value ={invalidServerId}"); }); invalidPaysources.ForEach(invalidPaysource => { ModelState.AddModelError("", $"Invalid PaySource Id with value ={invalidPaysource}"); }); return(View(viewmodel)); } else { _serverTimeEntryService.AddServerTimeEntries(timeEntries); } Success($"<strong>{timeEntries.Count}</strong> Time Entries have been successfully added. <br\\>" + $"<strong>{dtServers.Rows.Count - timeEntries.Count}</strong> Time Entries are duplicated and have been skipped."); } return(RedirectToAction("Index")); } return(View(viewmodel)); }
public ActionResult Upload(UploadedExcelSheetViewModel viewmodel) { if (ModelState.IsValid) // validate file exist { if (viewmodel.ExcelFile != null && viewmodel.ExcelFile.ContentLength > 0) { var fileName = Path.GetFileName(viewmodel.ExcelFile.FileName); var path = Path.Combine(Server.MapPath("~/Uploads/Servers/"), DateTime.Now.GetTimeStamp() + "_" + fileName); List <Server> addedServers = new List <Server>(); viewmodel.ExcelFile.SaveAs(path); // save a copy of the uploaded file. // convert the uploaded file into datatable, then add/update db entities. var dtServers = ImportUtils.ImportXlsxToDataTable(viewmodel.ExcelFile.InputStream, true); int numOfServersUpdated = 0; foreach (var row in dtServers.AsEnumerable().ToList()) { var entityViewModel = new ServerAddViewModel() { VendorId = int.Parse(row["Staff"].ToString()), // some columns does not have ',' separater. FirstName = row["Sort Name"].ToString().Split(new char[] { ',', ' ' }, StringSplitOptions.RemoveEmptyEntries)[1], LastName = row["Sort Name"].ToString().Split(new char[] { ',', ' ' }, StringSplitOptions.RemoveEmptyEntries)[0], GpEmpNumber = !string.IsNullOrWhiteSpace(row["Gp Emp #"].ToString()) ? row["Gp Emp #"].ToString() : null, ElementId = !string.IsNullOrWhiteSpace(row["Element"].ToString()) ? int.Parse(row["Element"].ToString()) : (int?)null, Active = row["active"].ToString() == "Y" ? true : false, CategoryId = CategoryConverter.ConvertFromCategoryNameToId(row["Category"].ToString()) }; //check if server does not exist if (entityViewModel.VendorId != 0) { if (entityViewModel.ElementId.HasValue) { var existedElement = _elementService.GetByVendorId(entityViewModel.ElementId.Value); if (existedElement == null) { Danger($"Invalid Element Id with value ={entityViewModel.ElementId.Value}"); continue; } else { entityViewModel.ElementId = existedElement.Id; } } var existedEntity = _serverService.GetByVendorId(entityViewModel.VendorId); if (existedEntity == null) { var entity = Mapper.Map <ServerAddViewModel, Server>(entityViewModel); addedServers.Add(entity); } else { Mapper.Map(entityViewModel, existedEntity); _serverService.UpdateServer(existedEntity); numOfServersUpdated++; } } } if (addedServers.Any()) { _serverService.AddServers(addedServers); } Success($"<strong>{addedServers.Count}</strong> servers have been successfully added. <br\\>" + $"<strong>{numOfServersUpdated}</strong> servers have been successfully updated."); } return(RedirectToAction("Index")); } return(View(viewmodel)); }