public async Task <IActionResult> ImportHospitalizationByCounty()
{
var path = Path.Combine(
_env.ContentRootPath,
String.Format("Data/Source/Covid/Hospitalizations/TexasCOVID19HospitalizationsbyTSA624.xlsx"));
using (var stream = new FileStream(
path,
FileMode.Open,
FileAccess.Read))
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var ep = new ExcelPackage(stream))
{
// get the first worksheet
var ws = ep.Workbook.Worksheets[0];
var lstHostDates = _context.CountyHospitalizations.ToList();
// Initialize the record counters
var nCounty = 0;
// iterate through all rows, skipping the first one
for (int nRow = 2; nRow <= ws.Dimension.End.Row; nRow++)
{
var row = ws.Cells[nRow, 1, nRow, ws.Dimension.End.Column];
var dateId = row[nRow, 4].GetValue <DateTime>();
if (lstHostDates.Where(d => d.Date == dateId).Count() == 0)
{
var hosp = new HospByCounty();
hosp.Date = row[nRow, 4].GetValue <DateTime>();
hosp.County = row[nRow, 1].GetValue <string>();
hosp.Hospitalizations = row[nRow, 3].GetValue <int>();
// create the hospbycounty entity and fill it with xlsx data
// save the hospbycounty to the db
_context.CountyHospitalizations.Add(hosp);
await _context.SaveChangesAsync();
//increment the counter
nCounty++;
}
}
return(new JsonResult(new
{
HospByCounty = nCounty
}));
}
}
}
public async Task <IActionResult> FormatHospitalizationsByCron()
{
var path = Path.Combine(
_env.ContentRootPath,
String.Format("Data/Source/Trials/TexasHospitalizations.xlsx"));
using (var stream = new FileStream(
path,
FileMode.Open,
FileAccess.Read))
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var ep = new ExcelPackage(stream))
{
// get the first worksheet
var ws = ep.Workbook.Worksheets[0];
var lstCounties = _context.CountyHospitalizations.Select(s => s.County).Distinct().ToList();
var lstHostDates = _context.CountyHospitalizations.ToList();
int nums = lstCounties.Count;
int rowCnt = ws.Dimension.End.Row;
int colCnt = ws.Dimension.End.Column + 1;
DateTime startingDate = new DateTime(2020, 04, 05);
List <HospByCounty> CasesYall = new List <HospByCounty>();
var nCounty = 0;
for (var i = 0; i < lstCounties.Count; i++)
{
for (var j = 4; j < 27; j++)
{
if (lstCounties[i] == ws.Cells[j, 2].Value.ToString())
{
for (var k = 3; k < colCnt; k++)
{
var dateId = startingDate.AddDays(k).Date;
if (lstHostDates.Where(d => d.Date == dateId).Count() == 0)
{
var hosp = new HospByCounty();
hosp.Date = startingDate.AddDays(k).Date;
hosp.County = lstCounties[i];
hosp.Hospitalizations = ws.Cells[j, k].GetValue <int>();
CasesYall.Add(hosp);
// create the hospbycounty entity and fill it with xlsx data
// save the hospbycounty to the db
_context.CountyHospitalizations.Add(hosp);
await _context.SaveChangesAsync();
//increment the counter
nCounty++;
}
}
}
}
}
return(new JsonResult(new
{
HospByCounty = nCounty
}));
}
}
}