public DataSet ApplyPopulationProjection(DataTable baseResults, IEnumerable <PredefinedLocationItem> locations, ESPCensusDataSelection censusParams, string exportFormat) { _stratifyByAgeGroup = (censusParams.Stratification & DTO.Enums.Stratifications.Age) == DTO.Enums.Stratifications.Age; _stratifyBySex = (censusParams.Stratification & DTO.Enums.Stratifications.Gender) == DTO.Enums.Stratifications.Gender; _stratifyByEthnicity = (censusParams.Stratification & DTO.Enums.Stratifications.Ethnicity) == DTO.Enums.Stratifications.Ethnicity; baseResults = MergeOverEightyResults(baseResults); var locationObservedPopulations = baseResults.AsEnumerable().GroupBy(k => k["Location"], k => (k["Population_Count"] ?? 0).ToDouble(), (key, g) => new { Location = key, Count = g.Sum() }).ToDictionary(k => k.Location, k => k.Count); DataTable tbl = new DataTable("Projected"); tbl.Columns.Add(LocationColumnName, typeof(string)); tbl.Columns.Add(AgeGroupColumnName, typeof(string)); tbl.Columns.Add(SexColumnName, typeof(string)); tbl.Columns.Add(RaceEthnicityColumnName, typeof(string)); tbl.Columns.Add(ObservedPatientsColumnName, typeof(int)); tbl.Columns.Add(ObservedPopulationColumnName, typeof(int)); tbl.Columns.Add(ObservedPopulationPercentColumnName, typeof(string)); tbl.Columns.Add(CensusPopulationColumnName, typeof(int)); tbl.Columns.Add(CensusPopulationPercentColumnName, typeof(string)); tbl.Columns.Add(AdjustedObservedPatientsColumnName, typeof(int)); tbl.Columns.Add(ProjectedPatientsColumnName, typeof(int)); tbl.BeginLoadData(); foreach (DataRow outer in baseResults.Rows) { double observedPatients = outer["Patients"].ToDouble(); double observedPopulation = outer["Population_Count"].ToDouble(); double totalObservedPopulation = locationObservedPopulations[outer[LocationColumnName].ToStringEx()]; double popPct = Math.Round(observedPopulation / totalObservedPopulation * 10000) / 100; if (double.IsNaN(popPct)) { popPct = 0; } var location = locations.FirstOrDefault(l => l.ToString() == outer[LocationColumnName].ToString()); var censusData = Demographics.GetCensusDataByZip(location.PostalCodes, censusParams.Stratification); double totalCensusPopulation = censusData.Sum(d => d.Count); double stratifiedCensusPopulation = 0d; DataRow row = tbl.NewRow(); row[LocationColumnName] = outer[LocationColumnName]; if (_stratifyByAgeGroup && _stratifyBySex && _stratifyByEthnicity) { stratifiedCensusPopulation = censusData.Where(c => string.Equals(c.Sex, outer[SexColumnName].ToStringEx().Substring(0, 1), StringComparison.OrdinalIgnoreCase) && AgeGroupAsCensusValue(outer[AgeGroupColumnName]) == c.AgeGroup && EthnicityAsCensusValue(outer["Ethnicity"]) == c.Ethnicity) .Sum(c => (double)c.Count); row[AgeGroupColumnName] = outer[AgeGroupColumnName]; row[SexColumnName] = outer[SexColumnName]; row[RaceEthnicityColumnName] = outer["Ethnicity"]; } else if (_stratifyBySex && _stratifyByEthnicity) { stratifiedCensusPopulation = censusData.Where(c => string.Equals(c.Sex, outer[SexColumnName].ToStringEx().Substring(0, 1), StringComparison.OrdinalIgnoreCase) && EthnicityAsCensusValue(outer["Ethnicity"]) == c.Ethnicity) .Sum(c => (double)c.Count); row[SexColumnName] = outer[SexColumnName]; row[RaceEthnicityColumnName] = outer["Ethnicity"]; } else if (_stratifyByAgeGroup && _stratifyBySex) { stratifiedCensusPopulation = censusData.Where(c => string.Equals(c.Sex, outer[SexColumnName].ToStringEx().Substring(0, 1), StringComparison.OrdinalIgnoreCase) && AgeGroupAsCensusValue(outer[AgeGroupColumnName]) == c.AgeGroup) .Sum(c => (double)c.Count); row[AgeGroupColumnName] = outer[AgeGroupColumnName]; row[SexColumnName] = outer[SexColumnName]; } else if (_stratifyByAgeGroup && _stratifyByEthnicity) { stratifiedCensusPopulation = censusData.Where(c => AgeGroupAsCensusValue(outer[AgeGroupColumnName]) == c.AgeGroup && EthnicityAsCensusValue(outer["Ethnicity"]) == c.Ethnicity) .Sum(c => (double)c.Count); row[AgeGroupColumnName] = outer[AgeGroupColumnName]; row[RaceEthnicityColumnName] = outer["Ethnicity"]; } else if (_stratifyByAgeGroup) { stratifiedCensusPopulation = censusData.Where(c => AgeGroupAsCensusValue(outer[AgeGroupColumnName]) == c.AgeGroup) .Sum(c => (double)c.Count); row[AgeGroupColumnName] = outer[AgeGroupColumnName]; } else if (_stratifyBySex) { stratifiedCensusPopulation = censusData.Where(c => string.Equals(c.Sex, outer[SexColumnName].ToStringEx().Substring(0, 1), StringComparison.OrdinalIgnoreCase)) .Sum(c => (double)c.Count); row[SexColumnName] = outer[SexColumnName]; } else if (_stratifyByEthnicity) { stratifiedCensusPopulation = censusData.Where(c => EthnicityAsCensusValue(outer["Ethnicity"]) == c.Ethnicity) .Sum(c => (double)c.Count); row[RaceEthnicityColumnName] = outer["Ethnicity"]; } else { stratifiedCensusPopulation = censusData.Sum(c => (double)c.Count); } row[ObservedPatientsColumnName] = observedPatients; row[ObservedPopulationColumnName] = observedPopulation; row[ObservedPopulationPercentColumnName] = popPct + "%"; row[CensusPopulationColumnName] = stratifiedCensusPopulation; row[CensusPopulationPercentColumnName] = (totalCensusPopulation <= 0 || stratifiedCensusPopulation <= 0) ? "0%" : (Math.Round(stratifiedCensusPopulation / totalCensusPopulation * 10000) / 100) + "%"; row[AdjustedObservedPatientsColumnName] = ComputePopulationAdjustment(observedPatients, observedPopulation, totalObservedPopulation, stratifiedCensusPopulation, totalCensusPopulation).ToInt32(); row[ProjectedPatientsColumnName] = (stratifiedCensusPopulation <= 0 || observedPopulation <= 0) ? 0 : (observedPatients * (stratifiedCensusPopulation / observedPopulation)).ToInt32(); tbl.Rows.Add(row); } tbl.EndLoadData(); if (_stratifyByAgeGroup == false) { tbl.Columns.Remove(AgeGroupColumnName); } if (_stratifyByEthnicity == false) { tbl.Columns.Remove(RaceEthnicityColumnName); } if (_stratifyBySex == false) { tbl.Columns.Remove(SexColumnName); } tbl.AcceptChanges(); DataSet ds = new DataSet(); if (string.Equals("xls", exportFormat, StringComparison.OrdinalIgnoreCase)) { //each location needs to be on a separate tab => a separate table in the dataset with the table name the location var locationNames = tbl.AsEnumerable().GroupBy(r => r[LocationColumnName]).Select(r => r.Key.ToStringEx()); if (locationNames.Count() == 1) { tbl.TableName = locationNames.First(); ds.Tables.Add(tbl); } else { foreach (var location in locationNames) { tbl.DefaultView.RowFilter = LocationColumnName + " = '" + location + "'"; var table = tbl.DefaultView.ToTable(); table.TableName = location; ds.Tables.Add(table); } } } else { //single table in the dataset ds.Tables.Add(tbl); } ds.AcceptChanges(); return(ds); }