public ActionResult Upload(FundsUploadViewModel viewmodel)
{
try
{
if (ModelState.IsValid)
{
if (viewmodel.ExcelFile != null && viewmodel.ExcelFile.ContentLength > 0)
{
var fileName = Path.GetFileName(viewmodel.ExcelFile.FileName);
var path = Path.Combine(Server.MapPath("~/Uploads/Funds/"),
DateTime.Now.GetTimeStamp() + "_" + fileName);
viewmodel.ExcelFile.SaveAs(path); // save a copy of the uploaded file.
// convert the uploaded file into datatable, then add/update db entities.
var dtDistFunds = ImportUtils.ImportXlsxToDataTable(viewmodel.ExcelFile.InputStream, true, 1, true);
var dtGcFunds = ImportUtils.ImportXlsxToDataTable(viewmodel.ExcelFile.InputStream, true, 2, true);
var distFundAddEntities = dtDistFunds.AsEnumerable().Select(row => new FundAddEntity()
{
Year = int.Parse(row["Year"].ToString()),
FundNumber = row["Fund Number"].ToString(),
DisplayName = row["Display Name"].ToString(),
MCAG = row["MCAG"].ToString(),
MapTo = row["Map to"].ToString(),
IsActive = int.Parse(row["Is Active"].ToString()) == 1,
DbSource = DbSource.DIST
});
var gcFundAddEntities = dtGcFunds.AsEnumerable().Select(row => new FundAddEntity()
{
Year = int.Parse(row["Year"].ToString()),
FundNumber = row["Fund Number"].ToString(),
DisplayName = row["Display Name"].ToString(),
MCAG = row["MCAG"].ToString(),
MapTo = row["Map to"].ToString(),
IsActive = int.Parse(row["Is Active"].ToString()) == 1,
DbSource = DbSource.GC
});
List <FundAddEntity> rejectedFunds;
var uploadedFunds = distFundAddEntities.Union(gcFundAddEntities).ToList();
var validFunds = _fundService.AddUploadedFunds(viewmodel.FundsYear.Value, uploadedFunds, out rejectedFunds);
Success($"<strong>{validFunds.Count}</strong> Funds have been successfully saved.");
if (rejectedFunds.Any())
{
Danger($"<strong>{rejectedFunds.Count}</strong> Funds have been skipped. Please make sure that they exist in GP Dynamics system.");
}
}
}
return(RedirectToAction("Index", new { year = viewmodel.FundsYear.Value }));
}
catch
{
Danger("An error happened while updating Funds. Please try again.");
return(View(viewmodel));
}
}
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"));
}
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 static IEnumerable <WarrantsInterestSheetInputItem> Parse(Stream inputStream, int sheetIndex, List <MonthlyImportFundExceptionRule> exceptionRules)
{
var results = new List <WarrantsInterestSheetInputItem>();
var columnsToParse = new[] { "Fund", "Name", "Warrant Int" };
var sheetData = ImportUtils.ImportXlsxToDataTable(inputStream, sheetIndex, columnsToParse);
sheetData.AsEnumerable().ForEachWithIndex((row, index) =>
{
var fundResult = StringUtils.ApplyMonthlyImportExceptionRuleOnFund(row["Fund"].ToString(), exceptionRules);
results.Add(new WarrantsInterestSheetInputItem()
{
RowIndex = index + 2, // 2 => one for table header and one for zero-indexed loop
FundId = fundResult.Item2,
IsExceptionRuleMatched = fundResult.Item1,
Name = row["Name"].ToString(),
WarrantInterest = StringUtils.ParseNegativeValue(row["Warrant Int"].ToString()),
});
});
return(results);
}
public ActionResult MonthlyReport(MonthlyReportGenerateViewModel viewmodel)
{
if (ModelState.IsValid) // validate file exist
{
if (viewmodel.ExcelFile != null && viewmodel.ExcelFile.ContentLength > 0)
{
SaveUploadedFile(viewmodel.ExcelFile, Server.MapPath("~/Uploads/MonthlyReport/"));
// convert the uploaded file into datatable, then add/update db entities.
var dtBarsHours = ImportUtils.ImportXlsxToDataTable(viewmodel.ExcelFile.InputStream, true, 1);
var excelData = dtBarsHours.AsEnumerable().Select(row =>
{
string account = row["Account"].ToString();
if (account.StartsWith("-"))
{
account = account.Remove(0, 1);
}
return(new MonthlyReportDataViewModel()
{
AccountNumber = account,
Date = DateTime.Parse(row["Date"].ToString()),
Amount = decimal.Parse(row["Amount"].ToString()),
Description = row["Description"].ToString(),
Category = row["Category"].ToString(),
});
}).ToList();
viewmodel.Data = excelData;
ViewBag.DisplayResults = true;
}
}
else
{
ViewBag.DisplayResults = false;
}
return(View(viewmodel));
}
public ActionResult Upload(BarsUploadViewModel 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/Bars/"), DateTime.Now.GetTimeStamp() + "_" + fileName);
List <Bar> addedEntities = new List <Bar>();
viewmodel.ExcelFile.SaveAs(path); // save a copy of the uploaded file.
// convert the uploaded file into datatable, then add/update db entities.
var dtBarsHours = ImportUtils.ImportXlsxToDataTable(viewmodel.ExcelFile.InputStream, true, 1);
var excelData = dtBarsHours.AsEnumerable().Select(row => new BarUploadEntity()
{
Year = int.Parse(row["Year"].ToString()),
BarNumber = row["State BARS Number"].ToString(),
MapToBarNumber = row["Map to"].ToString(),
DisplayName = row["Display Name"].ToString(),
Period = string.IsNullOrWhiteSpace(row["Period"].ToString()) ? (int?)null : int.Parse(row["Period"].ToString()),
IsActive = string.IsNullOrWhiteSpace(row["Is Active"].ToString()) ? false : row["Is Active"].ToString() == "1" ? true : false
}).ToList();
excelData = excelData.GroupBy(x => x.BarNumber).Select(y => y.First()).ToList();
int numOfAddedEntities = 0, numOfUpdatedEntities = 0;
_barService.UploadBars(viewmodel.BarsYear.Value, excelData, out numOfAddedEntities, out numOfUpdatedEntities);
Success($"<strong>{numOfAddedEntities}</strong> records have been successfully added. <br\\>"
+ $"<strong>{numOfUpdatedEntities}</strong> records have been successfully updated.");
}
}
return(RedirectToAction("Index", new
{
year = viewmodel.BarsYear.Value
}));
}
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));
}