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