public FileResult ExportServerAvailableHoursTemplate(ServerAvailableHourUploadViewModel viewmodel) { DateTime dateForAvailableHours = viewmodel.Date.HasValue ? viewmodel.Date.Value : DateTime.Now; MemoryStream stream = _exportingService.GetServerAvailableHoursTemplate(viewmodel.DefaultAvailableHours, dateForAvailableHours); return(File(stream, Constants.ExcelFilesMimeType, string.Format(Constants.ServerAvailableHoursTemplateExcelFileName , CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(dateForAvailableHours.Month) , dateForAvailableHours.Year))); }
public ActionResult Upload(ServerAvailableHourUploadViewModel 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/AvailableHours/"), DateTime.Now.GetTimeStamp() + "_" + fileName); List <ServerAvailableHour> addedEntities = new List <ServerAvailableHour>(); viewmodel.ExcelFile.SaveAs(path); // save a copy of the uploaded file. // convert the uploaded file into datatable, then add/update db entities. var dtAvailableHours = ImportUtils.ImportXlsxToDataTable(viewmodel.ExcelFile.InputStream, true); int numOfEntitiesUpdated = 0; // load existed entities from DB, aka "cache". var existedEntities = GetExistedAvailableHours(dtAvailableHours).ToList(); foreach (var row in dtAvailableHours.AsEnumerable().ToList()) { var entityViewModel = new ServerAvailableHourAddViewModel() { ServerVendorId = int.Parse(row["Server ID"].ToString()), DateRange = DateTime.Parse(row["Date"].ToString()), AvailableHours = float.Parse(row["AvailableHours"].ToString()) }; var existedServer = _serverService.GetByVendorId(entityViewModel.ServerVendorId); if (existedServer == null) { ModelState.AddModelError("", $"Invalid Server Id with value ={entityViewModel.ServerVendorId}"); } // check if entity already exists. var existedEntity = existedEntities.FirstOrDefault(t => t.Server.VendorId == entityViewModel.ServerVendorId && t.DateRange.Year == entityViewModel.DateRange.Year && t.DateRange.Month == entityViewModel.DateRange.Month); if (existedEntity == null) { var entity = Mapper.Map <ServerAvailableHourAddViewModel, ServerAvailableHour>(entityViewModel); entity.ServerId = existedServer.Id; addedEntities.Add(entity); } else { Mapper.Map(entityViewModel, existedEntity); _serverAvailableHourService.Update(existedEntity); numOfEntitiesUpdated++; } } if (addedEntities.Any()) { _serverAvailableHourService.Add(addedEntities); } Success($"<strong>{addedEntities.Count}</strong> records have been successfully added. <br\\>" + $"<strong>{numOfEntitiesUpdated}</strong> records have been successfully updated."); } } return(RedirectToAction("Index")); }