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
                    }));
                }
            }
        }