public Dictionary <string, object> Visit(Dictionary <string, object> row)
{
if (!HasStratifications)
{
return(row);
}
DTO.Enums.PeriodStratification stratification;
if (!Enum.TryParse <DTO.Enums.PeriodStratification>(Stratifications.First().ToString(), out stratification))
{
throw new ArgumentException("Unable to parse the specified stratification value as an PeriodStratification: " + Stratifications.First().ToString());
}
if (stratification == DTO.Enums.PeriodStratification.Monthly)
{
row["AdmittedOn"] = string.Format("{0}-{1:00}", row["AdmittedOnYear"], row["AdmittedOnMonth"]);
row.Remove("AdmittedOnMonth");
}
else
{
row["AdmittedOn"] = row["AdmittedOnYear"];
}
row.Remove("AdmittedOnYear");
return(row);
}
public Dictionary <string, object> Visit(Dictionary <string, object> row)
{
if (!HasStratifications)
{
return(row);
}
object value;
if (!row.TryGetValue("Weight", out value) || value == null)
{
return(row);
}
double weight = Convert.ToDouble(value);
DTO.Enums.WeightStratification stratification;
if (!Enum.TryParse <DTO.Enums.WeightStratification>(Stratifications.First().ToString(), out stratification))
{
throw new ArgumentException("Unable to parse the specified stratification value as an WeightStratification: " + Stratifications.First().ToString());
}
double stratificationValue = Convert.ToDouble((int)stratification);
row["Weight"] = string.Format(">={0:0.0} and <{1:0.0}", (weight * stratificationValue), (weight * stratificationValue) + stratificationValue);
return(row);
}
public override IEnumerable <System.Linq.Expressions.MemberBinding> FinalSelectBindings(Type selectType, System.Linq.Expressions.ParameterExpression sourceTypeParameter)
{
if (!HasStratifications)
{
Expression prop = Expressions.ChildPropertyExpression(sourceTypeParameter, "Key", "AdmittedOn");
return(new[] {
Expression.Bind(selectType.GetProperty("AdmittedOn"), prop)
});
}
DTO.Enums.PeriodStratification stratification;
if (!Enum.TryParse <DTO.Enums.PeriodStratification>(Stratifications.First().ToString(), out stratification))
{
throw new ArgumentException("Unable to parse the specified stratification value as an PeriodStratification: " + Stratifications.First().ToString());
}
List <MemberAssignment> bindings = new List <MemberAssignment>();
bindings.Add(Expression.Bind(selectType.GetProperty("AdmittedOnYear"), Expressions.ChildPropertyExpression(sourceTypeParameter, "Key", "AdmittedOnYear")));
if (stratification == DTO.Enums.PeriodStratification.Monthly)
{
bindings.Add(Expression.Bind(selectType.GetProperty("AdmittedOnMonth"), Expressions.ChildPropertyExpression(sourceTypeParameter, "Key", "AdmittedOnMonth")));
}
return(bindings);
}
public override Objects.Dynamic.IPropertyDefinition[] GroupKeyPropertyDefinitions()
{
//return new[]{
//new DTO.QueryComposer.QueryComposerResponsePropertyDefinitionDTO
// {
// Name = "AdmittedOn",
// Type = HasStratifications ? typeof(string).FullName : typeof(DateTime?).FullName
// }
//};
if (!HasStratifications)
{
return(new[] {
new DTO.QueryComposer.QueryComposerResponsePropertyDefinitionDTO
{
Name = "AdmittedOn",
Type = typeof(DateTime?).FullName
}
});
}
DTO.Enums.PeriodStratification stratification;
if (!Enum.TryParse <DTO.Enums.PeriodStratification>(Stratifications.First().ToString(), out stratification))
{
throw new ArgumentException("Unable to parse the specified stratification value as an PeriodStratification: " + Stratifications.First().ToString());
}
List <Objects.Dynamic.IPropertyDefinition> properties = new List <IPropertyDefinition>();
properties.Add(
new DTO.QueryComposer.QueryComposerResponsePropertyDefinitionDTO
{
Name = "AdmittedOnYear",
Type = typeof(int?).FullName
}
);
if (stratification == DTO.Enums.PeriodStratification.Monthly)
{
properties.Add(
new DTO.QueryComposer.QueryComposerResponsePropertyDefinitionDTO
{
Name = "AdmittedOnMonth",
Type = typeof(int?).FullName
}
);
}
return(properties.ToArray());
}
private string GenerateSqlQuery(Medical.Models.MedicalRequestModel m)
{
var strat = m.AgeStratification == null ? null : Stratifications.Find(m.AgeStratification.Value, "age");
if (strat == null)
{
return(null);
}
var stratValues = strat.ClassificationText.Split(',');
var codes = string.Join(",", m.Codes.Split(',').Select(c => string.Format("'{0}'", c.Trim())));
return(string.Format(@"
Select
SummaryData.*,
EnrollmentData.EnrollmentMembers as [Total Enrollment in Strata(Members)],
round(SummaryData.Members / EnrollmentData.EnrollmentMembers * 1000, 1) as [Prevalence Rate (Users per 1000 enrollees)],
round(SummaryData.Events / EnrollmentData.EnrollmentMembers * 1000, 1) as [Event Rate (Events per 1000 enrollees)],
round(SummaryData.Events/SummaryData.Members ,1) as [Events Per member]
From
(
Select
AgeGroup, gender as Sex, Period, Code as DXCode, DXName, Setting,
Sum(Event) as Events, Sum(Member) as Members
From ({0}) as OuterTable
Group by AgeGroup, gender, Period, Code, DXName,Setting
)
as SummaryData
Left JOIN
(
Select AgeGroup, gender as Sex, Year, Sum(Member) as EnrollmentMembers
From ({1}) as OuterEnrollmentTable
Group by AgeGroup, gender, Year
)
as EnrollmentData
ON
EnrollmentData.AgeGroup = SummaryData.Agegroup AND
EnrollmentData.sex = Summarydata.Sex AND
EnrollmentData.Year = Summarydata.Period
",
string.Join("\n Union All \n", stratValues.Select(v => GenerateDiagQuery(v, codes))),
string.Join("\n Union All \n", stratValues.Select(GenerateEnrollmentQuery))
));
}
public JsonResult GetCensusDataByZCTA(string locations, Stratifications stratification)
{
IEnumerable <IEnumerable <string> > locationZipCodes = Newtonsoft.Json.JsonConvert.DeserializeObject <IEnumerable <IEnumerable <string> > >(locations);
//List<IEnumerable<ZCTACensusData>> results = new List<IEnumerable<ZCTACensusData>>();
ArrayList results = new ArrayList();
foreach (var location in locationZipCodes)
{
if (!location.Any())
{
continue;
}
var locationResults = Demographics.GetCensusDataByZip(location, stratification);
results.Add(new { LocationKey = string.Join(",", location), Results = (locationResults ?? Enumerable.Empty <ZCTACensusData>()) });
}
return(Json(results, JsonRequestBehavior.AllowGet));
}
public JsonResult GetCensusDataByTown(string country, string state, string town, Stratifications stratification)
{
IEnumerable results = Demographics.GetCensusDataByTown(country, state, town, stratification);
return(Json(results, JsonRequestBehavior.AllowGet));
}
public override IEnumerable <System.Linq.Expressions.MemberBinding> InnerSelectBindings(Type selectType, System.Linq.Expressions.ParameterExpression sourceTypeParameter)
{
ParameterExpression pe_sourceQueryType = sourceTypeParameter;
ParameterExpression pe_vitalsQueryType = Expression.Parameter(typeof(PCORIQueryBuilder.Model.Vital), "v");
Expression weightSelector = Expression.Property(pe_vitalsQueryType, "Weight");
Expression weightSelector_Value = Expression.Property(Expression.Property(pe_vitalsQueryType, "Weight"), "Value");
BinaryExpression be_vitalsPatient = Expression.Equal(Expression.Property(pe_vitalsQueryType, "PatientID"), Expression.Property(pe_sourceQueryType, "ID"));
//apply all the applicable criteria to the inner value select as well.
List <Expression> predicates = new List <Expression>();
if (Criteria.Any(c => c.Terms.Any(t => t.Type == ModelTermsFactory.ObservationPeriodID)))
{
Expression prop_Encounter = Expression.Property(pe_vitalsQueryType, "Encounter");
Expression proper_Encounter_AdmittedOn = Expression.Property(prop_Encounter, "AdmittedOn");
BinaryExpression observationPeriodPredicate = null;
var observationPeriodCriteria = Criteria.SelectMany(c => c.Terms.Where(t => t.Type == ModelTermsFactory.ObservationPeriodID));
foreach (var obpTerm in observationPeriodCriteria)
{
BinaryExpression obp_predicate = null;
var obp_values = AdapterHelpers.ParseDateRangeValues(obpTerm);
if (obp_values.StartDate.HasValue && obp_values.EndDate.HasValue)
{
DateTime startDate = obp_values.StartDate.Value.Date;
DateTime endDate = obp_values.EndDate.Value.Date;
obp_predicate = Expression.AndAlso(Expression.GreaterThanOrEqual(proper_Encounter_AdmittedOn, Expression.Constant(startDate)), Expression.LessThanOrEqual(proper_Encounter_AdmittedOn, Expression.Constant(endDate)));
}
else if (obp_values.StartDate.HasValue)
{
DateTime startDate = obp_values.StartDate.Value.Date;
obp_predicate = Expression.GreaterThanOrEqual(proper_Encounter_AdmittedOn, Expression.Constant(startDate));
}
else if (obp_values.EndDate.HasValue)
{
DateTime endDate = obp_values.EndDate.Value.Date;
obp_predicate = Expression.LessThanOrEqual(proper_Encounter_AdmittedOn, Expression.Constant(endDate));
}
if (obp_predicate == null)
{
continue;
}
if (observationPeriodPredicate == null)
{
observationPeriodPredicate = Expression.AndAlso(Expression.NotEqual(Expression.Property(pe_vitalsQueryType, "EncounterID"), Expression.Constant(null)), obp_predicate);
}
else
{
observationPeriodPredicate = Expression.AndAlso(observationPeriodPredicate, obp_predicate);
}
}
if (observationPeriodPredicate != null)
{
predicates.Add(observationPeriodPredicate);
}
}
if (Criteria.Any(c => c.Terms.Any(t => t.Type == ModelTermsFactory.HeightID)))
{
Expression heightSelector = Expression.Property(pe_vitalsQueryType, "Height");
predicates.Add(Expression.Equal(Expression.Property(heightSelector, "HasValue"), Expression.Constant(true)));
Expression heightSelector_Value = Expression.Property(heightSelector, "Value");
var heightCriteria = Criteria.SelectMany(c => c.Terms.Where(t => t.Type == ModelTermsFactory.HeightID));
foreach (var heightTerm in heightCriteria)
{
var heightRange = AdapterHelpers.ParseHeightValues(heightTerm);
if (heightRange.MinHeight.HasValue)
{
double minHeight = heightRange.MinHeight.Value;
predicates.Add(
Expression.GreaterThanOrEqual(heightSelector_Value, Expression.Constant(minHeight))
);
}
if (heightRange.MaxHeight.HasValue)
{
double maxHeight = heightRange.MaxHeight.Value;
predicates.Add(
Expression.LessThanOrEqual(heightSelector_Value, Expression.Constant(maxHeight))
);
}
}
}
if (Criteria.Any(c => c.Terms.Any(t => t.Type == ModelTermsFactory.WeightID)))
{
//if the weight term is included in critieria it enforces that the weight value cannot be null
predicates.Add(Expression.Equal(Expression.Property(weightSelector, "HasValue"), Expression.Constant(true)));
var weightCriteria = Criteria.SelectMany(c => c.Terms.Where(t => t.Type == ModelTermsFactory.WeightID));
foreach (var weightTerm in weightCriteria)
{
var weightRange = AdapterHelpers.ParseWeightValues(weightTerm);
if (weightRange.MinWeight.HasValue)
{
double minWeight = weightRange.MinWeight.Value;
predicates.Add(
Expression.GreaterThanOrEqual(weightSelector_Value, Expression.Constant(minWeight))
);
}
if (weightRange.MaxWeight.HasValue)
{
double maxWeight = weightRange.MaxWeight.Value;
predicates.Add(
Expression.LessThanOrEqual(weightSelector_Value, Expression.Constant(maxWeight))
);
}
}
}
if (Criteria.Any(c => c.Terms.Any(t => t.Type == ModelTermsFactory.VitalsMeasureDateID)))
{
Expression measuredOnSelector = Expression.Property(pe_vitalsQueryType, "MeasuredOn");
var measureDateCriteria = Criteria.SelectMany(c => c.Terms.Where(t => t.Type == ModelTermsFactory.VitalsMeasureDateID));
foreach (var measureDateTerm in measureDateCriteria)
{
BinaryExpression md_predicate = null;
var md_values = AdapterHelpers.ParseDateRangeValues(measureDateTerm);
if (md_values.StartDate.HasValue && md_values.EndDate.HasValue)
{
DateTime startDate = md_values.StartDate.Value.Date;
DateTime endDate = md_values.EndDate.Value.Date;
md_predicate = Expression.AndAlso(Expression.GreaterThanOrEqual(measuredOnSelector, Expression.Constant(startDate)), Expression.LessThanOrEqual(measuredOnSelector, Expression.Constant(endDate)));
}
else if (md_values.StartDate.HasValue)
{
DateTime startDate = md_values.StartDate.Value.Date;
md_predicate = Expression.GreaterThanOrEqual(measuredOnSelector, Expression.Constant(startDate));
}
else if (md_values.EndDate.HasValue)
{
DateTime endDate = md_values.EndDate.Value.Date;
md_predicate = Expression.LessThanOrEqual(measuredOnSelector, Expression.Constant(endDate));
}
if (md_predicate != null)
{
predicates.Add(md_predicate);
}
}
}
Expression predicateExpression = null;
if (predicates.Count > 0)
{
predicateExpression = predicates[0];
if (predicates.Count > 1)
{
for (int i = 1; i < predicates.Count; i++)
{
predicateExpression = Expression.AndAlso(predicateExpression, predicates[i]);
}
}
}
Expression vitalsProp = Expressions.AsQueryable(Expression.Property(sourceTypeParameter, "Vitals"));
Expression measureOnSelector = Expression.Property(pe_vitalsQueryType, "MeasuredOn");
MethodCallExpression orderByMeasureOn;
if (predicateExpression == null)
{
orderByMeasureOn = Expressions.OrderByAscending(vitalsProp, Expression.Lambda(measureOnSelector, pe_vitalsQueryType));
}
else
{
MethodCallExpression weightWhere = Expressions.Where(vitalsProp, Expression.Lambda(predicateExpression, pe_vitalsQueryType));
orderByMeasureOn = Expressions.OrderByAscending(weightWhere, Expression.Lambda(measureOnSelector, pe_vitalsQueryType));
}
MethodCallExpression weightSelect = Expressions.Select(pe_vitalsQueryType.Type, weightSelector.Type, orderByMeasureOn, Expression.Lambda(weightSelector, pe_vitalsQueryType));
MethodCallExpression firstOrDefaultVitalWeight = Expressions.FirstOrDefault <double?>(weightSelect);
if (!HasStratifications)
{
return(new[] {
Expression.Bind(selectType.GetProperty("Weight"), firstOrDefaultVitalWeight)
});
}
// apply the modifier for stratification
DTO.Enums.WeightStratification stratification;
if (!Enum.TryParse <DTO.Enums.WeightStratification>(Stratifications.First().ToString(), out stratification))
{
throw new ArgumentException("Unable to parse the specified stratification value as an WeightStratification: " + Stratifications.First().ToString());
}
Expression stratificationExp = Expression.Condition(Expression.Equal(Expression.Property(firstOrDefaultVitalWeight, "HasValue"), Expression.Constant(true, typeof(bool))),
Expression.Convert(Expressions.MathFloor <double>(Expression.Divide(Expression.Property(firstOrDefaultVitalWeight, "Value"), Expression.Constant(Convert.ToDouble((int)stratification)))), typeof(double?)),
firstOrDefaultVitalWeight);
return(new[] {
Expression.Bind(selectType.GetProperty("Weight"), stratificationExp)
});
}
public override IEnumerable <System.Linq.Expressions.MemberBinding> InnerSelectBindings(Type selectType, System.Linq.Expressions.ParameterExpression sourceTypeParameter)
{
//the earliest encounter date that satisfies the query critieria
ParameterExpression pe_sourceQueryType = sourceTypeParameter;
Expression encountersProp = Expressions.AsQueryable(Expression.Property(pe_sourceQueryType, "Encounters"));
ParameterExpression pe_encountersQueryType = Expression.Parameter(typeof(PCORIQueryBuilder.Model.Encounter), "enc");
Expression admittedOnSelector = Expression.Property(pe_encountersQueryType, "AdmittedOn");
BinaryExpression be_encounterPatient = Expression.Equal(Expression.Property(pe_sourceQueryType, "ID"), Expression.Property(pe_encountersQueryType, "PatientID"));
BinaryExpression predicate = be_encounterPatient;
if (Criteria.Any(c => c.Terms.Any(t => t.Type == ModelTermsFactory.ObservationPeriodID)))
{
var observationPeriodCriteria = Criteria.SelectMany(c => c.Terms.Where(t => t.Type == ModelTermsFactory.ObservationPeriodID));
foreach (var obpTerm in observationPeriodCriteria)
{
DateTime dateValue;
var range = AdapterHelpers.ParseDateRangeValues(obpTerm);
if (range.StartDate.HasValue)
{
dateValue = range.StartDate.Value.DateTime.Date;
predicate = Expression.AndAlso(predicate, Expression.GreaterThanOrEqual(admittedOnSelector, Expression.Constant(dateValue)));
}
if (range.EndDate.HasValue)
{
dateValue = range.EndDate.Value.DateTime.Date;
predicate = Expression.AndAlso(predicate, Expression.LessThanOrEqual(admittedOnSelector, Expression.Constant(dateValue)));
}
}
}
MethodCallExpression admittedOnWhere = Expressions.Where(encountersProp, Expression.Lambda(predicate, pe_encountersQueryType));
MethodCallExpression orderByAdmittedOn = Expressions.OrderByAscending(admittedOnWhere, Expression.Lambda(admittedOnSelector, pe_encountersQueryType));
//need to cast the return type of the select to a nullable datetime so that the FirstOrDefault will be null as the default
MethodCallExpression admittedOnSelect = Expressions.Select(pe_encountersQueryType.Type, typeof(DateTime?), orderByAdmittedOn, Expression.Lambda(Expression.Convert(admittedOnSelector, typeof(DateTime?)), pe_encountersQueryType));
MethodCallExpression firstOrDefaultAdmittedOn = Expressions.FirstOrDefault <DateTime?>(admittedOnSelect);
if (!HasStratifications)
{
return(new[] {
Expression.Bind(selectType.GetProperty("AdmittedOn"), firstOrDefaultAdmittedOn)
});
}
//if stratified by Month -> return as string in format yyyy-MM
//if stratified by Year -> return as string in format yyyy
DTO.Enums.PeriodStratification stratification;
if (!Enum.TryParse <DTO.Enums.PeriodStratification>(Stratifications.First().ToString(), out stratification))
{
throw new ArgumentException("Unable to parse the specified stratification value as an PeriodStratification: " + Stratifications.First().ToString());
}
Expression prop = Expression.Property(firstOrDefaultAdmittedOn, "Value");
//Expression yearPartString = Expressions.CallToString<int>(Expression.Property(prop, "Year"));
//if (stratification == DTO.Enums.PeriodStratification.Monthly)
//{
// //if stratified by Month -> return as string in format yyyy-MM
// Expression monthPartString = Expressions.CallToString<int>(Expression.Property(prop, "Month"));
// prop = Expressions.ConcatStrings(yearPartString, Expression.Constant("-"), monthPartString);
//}
//else if (stratification == DTO.Enums.PeriodStratification.Yearly)
//{
// //if stratified by Year -> return as string in format yyyy
// prop = yearPartString;
//}
//else
//{
// throw new NotSupportedException("The specified period stratifcation is not currently supported; stratification value: " + Stratifications.First().ToString());
//}
//Expression stratificationModifier = Expression.Condition(Expression.NotEqual(firstOrDefaultAdmittedOn, Expression.Constant(null)), prop, Expression.Constant("", typeof(string)));
//return new[] {
// Expression.Bind(selectType.GetProperty("AdmittedOn"), stratificationModifier)
// };
Expression yearProp = Expression.Property(prop, "Year");
Expression monthProp = Expression.Property(prop, "Month");
return(new[] {
Expression.Bind(selectType.GetProperty("AdmittedOn"), firstOrDefaultAdmittedOn),
Expression.Bind(selectType.GetProperty("AdmittedOnYear"), yearProp),
Expression.Bind(selectType.GetProperty("AdmittedOnMonth"), monthProp)
});
}
public IEnumerable <ZCTACensusData> GetCensusDataByZip(IEnumerable <string> zipcodes, Stratifications stratification)
{
using (var db = new DataContext())
{
var query = from d in db.DemographicsByZCTA.AsNoTracking()
where zipcodes.Contains(d.Zip)
select d;
IEnumerable <ZCTACensusData> results;
//All
if ((stratification & Stratifications.Ethnicity) == Stratifications.Ethnicity && (stratification & Stratifications.Age) == Stratifications.Age && (stratification & Stratifications.Gender) == Stratifications.Gender)
{
results = (from d in query
group d by new { d.Zip, d.Ethnicity, d.AgeGroup, d.Sex } into g
select new ZCTACensusData
{
Zip = g.Key.Zip,
Ethnicity = g.Key.Ethnicity,
AgeGroup = g.Key.AgeGroup,
Sex = g.Key.Sex,
Count = g.Sum(s => (int?)s.Count) ?? 0
}).ToArray();
}
//Ethnicity and Age
else if ((stratification & Stratifications.Ethnicity) == Stratifications.Ethnicity && (stratification & Stratifications.Age) == Stratifications.Age)
{
results = (from d in query
group d by new { d.Zip, d.Ethnicity, d.AgeGroup } into g
select new ZCTACensusData
{
Zip = g.Key.Zip,
Ethnicity = g.Key.Ethnicity,
AgeGroup = g.Key.AgeGroup,
Sex = null,
Count = g.Sum(s => (int?)s.Count) ?? 0
}).ToArray();
}
//Ethnicity and Gender
else if ((stratification & Stratifications.Ethnicity) == Stratifications.Ethnicity && (stratification & Stratifications.Gender) == Stratifications.Gender)
{
results = (from d in query
group d by new { d.Zip, d.Ethnicity, d.Sex } into g
select new ZCTACensusData
{
Zip = g.Key.Zip,
Ethnicity = g.Key.Ethnicity,
AgeGroup = null,
Sex = g.Key.Sex,
Count = g.Sum(s => (int?)s.Count) ?? 0
}).ToArray();
}
//Age and Gender
else if ((stratification & Stratifications.Age) == Stratifications.Age && (stratification & Stratifications.Gender) == Stratifications.Gender)
{
results = (from d in query
group d by new { d.Zip, d.AgeGroup, d.Sex } into g
select new ZCTACensusData
{
Zip = g.Key.Zip,
Ethnicity = null,
AgeGroup = g.Key.AgeGroup,
Sex = g.Key.Sex,
Count = g.Sum(s => (int?)s.Count) ?? 0
}).ToArray();
}
//Ethnicity
else if ((stratification & Stratifications.Ethnicity) == Stratifications.Ethnicity)
{
results = (from d in query
group d by new { d.Zip, d.Ethnicity } into g
select new ZCTACensusData
{
Zip = g.Key.Zip,
Ethnicity = g.Key.Ethnicity,
AgeGroup = null,
Sex = null,
Count = g.Sum(s => (int?)s.Count) ?? 0
}).ToArray();
}
//Age
else if ((stratification & Stratifications.Age) == Stratifications.Age)
{
results = (from d in query
group d by new { d.Zip, d.AgeGroup } into g
select new ZCTACensusData
{
Zip = g.Key.Zip,
Ethnicity = null,
AgeGroup = g.Key.AgeGroup,
Sex = null,
Count = g.Sum(s => (int?)s.Count) ?? 0
}).ToArray();
}
//Gender
else if ((stratification & Stratifications.Gender) == Stratifications.Gender)
{
results = (from d in query
group d by new { d.Zip, d.Sex } into g
select new ZCTACensusData
{
Zip = g.Key.Zip,
Ethnicity = null,
AgeGroup = null,
Sex = g.Key.Sex,
Count = g.Sum(s => (int?)s.Count) ?? 0
}).ToArray();
}
else //None
{
results = (from d in query
group d by new { d.Zip } into g
select new ZCTACensusData
{
Zip = g.Key.Zip,
Ethnicity = null,
AgeGroup = null,
Sex = null,
Count = g.Sum(s => (int?)s.Count) ?? 0
}).ToArray();
}
//aggregate into a single result
results = results.GroupBy(k => new { k.Sex, k.AgeGroup, k.Ethnicity }).Select(k => new ZCTACensusData {
Zip = string.Join(",", zipcodes), Sex = k.Key.Sex, AgeGroup = k.Key.AgeGroup, Ethnicity = k.Key.Ethnicity, Count = k.Sum(i => i.Count)
}).ToArray();
return(results);
}
}
public IEnumerable <CensusData> GetCensusDataByTown(string country, string state, string town, Stratifications stratification)
{
using (var db = new DataContext())
{
var query = from d in db.Demographics.AsNoTracking()
where d.Country == country && d.State == state && d.Town == town
select d;
IEnumerable <CensusData> results;
//All
if ((stratification & Stratifications.Ethnicity) == Stratifications.Ethnicity && (stratification & Stratifications.Age) == Stratifications.Age && (stratification & Stratifications.Gender) == Stratifications.Gender)
{
results = (from d in query
group d by new { d.Country, d.State, d.Town, d.Ethnicity, d.AgeGroup, d.Gender } into g
select new CensusData
{
Country = g.Key.Country,
State = g.Key.State,
Location = g.Key.Town,
Ethnicity = g.Key.Ethnicity,
AgeGroup = g.Key.AgeGroup,
Gender = g.Key.Gender,
Count = g.Sum(s => (int?)s.Count) ?? 0
}).ToArray();
}
//Ethnicity and Age
else if ((stratification & Stratifications.Ethnicity) == Stratifications.Ethnicity && (stratification & Stratifications.Age) == Stratifications.Age)
{
results = (from d in query
group d by new { d.Country, d.State, d.Town, d.Ethnicity, d.AgeGroup } into g
select new CensusData
{
Country = g.Key.Country,
State = g.Key.State,
Location = g.Key.Town,
Ethnicity = g.Key.Ethnicity,
AgeGroup = g.Key.AgeGroup,
Count = g.Sum(s => (int?)s.Count) ?? 0
}).ToArray();
}
//Ethnicity and Gender
else if ((stratification & Stratifications.Ethnicity) == Stratifications.Ethnicity && (stratification & Stratifications.Gender) == Stratifications.Gender)
{
results = (from d in query
group d by new { d.Country, d.State, d.Town, d.Ethnicity, d.Gender } into g
select new CensusData
{
Country = g.Key.Country,
State = g.Key.State,
Location = g.Key.Town,
Ethnicity = g.Key.Ethnicity,
Gender = g.Key.Gender,
Count = g.Sum(s => (int?)s.Count) ?? 0
}).ToArray();
}
//Age and Gender
else if ((stratification & Stratifications.Age) == Stratifications.Age && (stratification & Stratifications.Gender) == Stratifications.Gender)
{
results = (from d in query
group d by new { d.Country, d.State, d.Town, d.AgeGroup, d.Gender } into g
select new CensusData
{
Country = g.Key.Country,
State = g.Key.State,
Location = g.Key.Town,
AgeGroup = g.Key.AgeGroup,
Gender = g.Key.Gender,
Count = g.Sum(s => (int?)s.Count) ?? 0
}).ToArray();
}
//Ethnicity
else if ((stratification & Stratifications.Ethnicity) == Stratifications.Ethnicity)
{
results = (from d in query
group d by new { d.Country, d.State, d.Town, d.Ethnicity } into g
select new CensusData
{
Country = g.Key.Country,
State = g.Key.State,
Location = g.Key.Town,
Ethnicity = g.Key.Ethnicity,
Count = g.Sum(s => (int?)s.Count) ?? 0
}).ToArray();
}
//Age
else if ((stratification & Stratifications.Age) == Stratifications.Age)
{
results = (from d in query
group d by new { d.Country, d.State, d.Town, d.AgeGroup } into g
select new CensusData
{
Country = g.Key.Country,
State = g.Key.State,
Location = g.Key.Town,
AgeGroup = g.Key.AgeGroup,
Count = g.Sum(s => (int?)s.Count) ?? 0
}).ToArray();
}
//Gender
else if ((stratification & Stratifications.Gender) == Stratifications.Gender)
{
results = (from d in query
group d by new { d.Country, d.State, d.Town, d.Gender } into g
select new CensusData
{
Country = g.Key.Country,
State = g.Key.State,
Location = g.Key.Town,
Gender = g.Key.Gender,
Count = g.Sum(s => (int?)s.Count) ?? 0
}).ToArray();
}
else //None
{
results = (from d in query
group d by new { d.Country, d.State, d.Town } into g
select new CensusData
{
Country = g.Key.Country,
State = g.Key.State,
Location = g.Key.Town,
Count = g.Sum(s => (int?)s.Count) ?? 0
}).ToArray();
}
return(results);
}
}
