public ActionResult Bands(int itemCount, int bands, int industryId, Core.DataLayer.Granularity granularity, long?regionId, long?stateId)
{
BestPlacesFilters filters = BuildFilters();
using (var context = ContextFactory.SizeUpContext)
{
var output = Core.DataLayer.BestPlaces.Bands(context, industryId, itemCount, bands, regionId, stateId, filters, granularity);
return(Json(output, JsonRequestBehavior.AllowGet));
}
}
public ActionResult Index(int industryId, Core.DataLayer.Granularity granularity, long?regionId, long?stateId, int itemCount = 25)
{
int maxResults = int.Parse(ConfigurationManager.AppSettings["Data.BestPlaces.MaxResults"]);
itemCount = Math.Min(maxResults, itemCount);
BestPlacesFilters filters = BuildFilters();
using (var context = ContextFactory.SizeUpContext)
{
var output = Core.DataLayer.BestPlaces.Get(context, industryId, regionId, stateId, filters, granularity).Take(itemCount).ToList();
if (granularity == Core.DataLayer.Granularity.City)
{
var cityIds = output.Select(i => i.City.Id).ToList();
var counties = context.Cities.Where(c => cityIds.Contains(c.Id)).SelectMany(i => i.Counties, (i, o) => new { i.Id, o.Name }).ToList();
output.ForEach(i => i.City.Counties = counties.Where(c => c.Id == i.City.Id).Select(c => new Core.DataLayer.Models.County {
Name = c.Name
}).ToList());
}
return(Json(output, JsonRequestBehavior.AllowGet));
}
}
//
// GET: /Api/TopPlaces/
private BestPlacesFilters BuildFilters()
{
BestPlacesFilters f = new BestPlacesFilters();
f.AverageRevenue = ParseQueryString("averageRevenue");
f.TotalRevenue = ParseQueryString("totalRevenue");
f.TotalEmployees = ParseQueryString("totalEmployees");
f.RevenuePerCapita = ParseQueryString("revenuePerCapita");
f.HouseholdIncome = ParseQueryString("householdIncome");
f.HouseholdExpenditures = ParseQueryString("householdExpenditures");
f.MedianAge = ParseQueryString("medianAge");
f.BachelorOrHigher = QueryString.IntValue("bachelorsDegreeOrHigher");
f.HighSchoolOrHigher = QueryString.IntValue("highSchoolOrHigher");
f.WhiteCollarWorkers = QueryString.IntValue("whiteCollarWorkers");
f.BlueCollarWorkers = QueryString.IntValue("blueCollarWorkers");
f.AirportsNearby = QueryString.IntValue("airportsNearby");
f.YoungEducated = QueryString.IntValue("youngEducated");
f.UniversitiesNearby = QueryString.IntValue("universitiesNearby");
f.CommuteTime = QueryString.IntValue("commuteTime");
f.Attribute = QueryString.StringValue("attribute");
return(f);
}
public static List <Models.Band <double> > Bands(SizeUpContext context, long industryId, int itemCount, int bands, long?regionId, long?stateId, BestPlacesFilters filters, Granularity granularity)
{
var data = Get(context, industryId, regionId, stateId, filters, granularity)
.Take(itemCount)
.ToList();
List <Models.Band <double> > output = new List <Models.Band <double> >();
switch (filters.Attribute)
{
case "totalRevenue":
output = data.NTileDescending(i => i.TotalRevenue.Max, bands)
.Select(b => new Models.Band <double>()
{
Min = b.Min(i => i.TotalRevenue.Min), Max = b.Max(i => i.TotalRevenue.Max)
})
.ToList();
break;
case "averageRevenue":
output = data.NTileDescending(i => i.AverageRevenue.Max, bands)
.Select(b => new Models.Band <double>()
{
Min = b.Min(i => i.AverageRevenue.Min), Max = b.Max(i => i.AverageRevenue.Max)
})
.ToList();
break;
case "revenuePerCapita":
output = data.NTileDescending(i => i.RevenuePerCapita.Max, bands)
.Select(b => new Models.Band <double>()
{
Min = b.Min(i => i.RevenuePerCapita.Min), Max = b.Max(i => i.RevenuePerCapita.Max)
})
.ToList();
break;
case "underservedMarkets":
output = data.NTile(i => i.RevenuePerCapita.Max, bands)
.Select(b => new Models.Band <double>()
{
Min = b.Min(i => i.RevenuePerCapita.Min), Max = b.Max(i => i.RevenuePerCapita.Max)
})
.ToList();
break;
case "totalEmployees":
output = data.NTileDescending(i => i.TotalEmployees.Max, bands)
.Select(b => new Models.Band <double>()
{
Min = b.Min(i => i.TotalEmployees.Min), Max = b.Max(i => i.TotalEmployees.Max)
})
.ToList();
break;
case "averageEmployees":
output = data.NTileDescending(i => i.AverageEmployees.Max, bands)
.Select(b => new Models.Band <double>()
{
Min = b.Min(i => i.AverageEmployees.Min), Max = b.Max(i => i.AverageEmployees.Max)
})
.ToList();
break;
case "employeesPerCapita":
output = data.NTileDescending(i => i.EmployeesPerCapita.Max, bands)
.Select(b => new Models.Band <double>()
{
Min = b.Min(i => i.EmployeesPerCapita.Min), Max = b.Max(i => i.EmployeesPerCapita.Max)
})
.ToList();
break;
}
if (filters.Attribute == "underservedMarkets")
{
output.Format();
}
else
{
output.FormatDescending();
}
return(output);
}
public static IQueryable <Models.BestPlaces> Get(SizeUpContext context, long industryId, long?regionId, long?stateId, BestPlacesFilters filters, Granularity granularity)
{
var gran = Enum.GetName(typeof(Granularity), granularity);
var data = context.GeographicLocations
.SelectMany(i => i.Demographics, (i, o) => new { Place = i, Demographics = o })
.SelectMany(i => i.Place.IndustryDatas, (i, o) => new { i.Place, i.Demographics, IndustryData = o, Industry = o.Industry })
.SelectMany(i => i.Place.Geographies, (i, o) => new { i.Place, i.IndustryData, i.Demographics, i.Industry, Geography = o })
.Where(i => i.IndustryData.Year == CommonFilters.TimeSlice.Industry.Year && i.IndustryData.Quarter == CommonFilters.TimeSlice.Industry.Quarter)
.Where(i => i.Demographics.Year == CommonFilters.TimeSlice.Demographics.Year && i.Demographics.Quarter == CommonFilters.TimeSlice.Demographics.Quarter)
.Where(i => i.Geography.GeographyClass.Name == Geo.GeographyClass.Calculation)
.Where(i => i.IndustryData.IndustryId == industryId && i.IndustryData.BusinessCount > CommonFilters.MinimumBusinessCount)
.Where(i => i.Place.Granularity.Name == gran)
.Select(i => new Wrapper
{
Place = i.Place,
Industry = i.Industry,
AirportsNearby = i.Demographics.AirPortsWithinHalfMile,
BachelorsDegreeOrHigher = i.Demographics.BachelorsOrHigherPercentage,
BlueCollarWorkers = i.Demographics.BlueCollarWorkersPercentage,
CommuteTime = i.Demographics.CommuteTime,
HighSchoolOrHigher = i.Demographics.HighSchoolOrHigherPercentage,
HouseholdExpenditures = i.Demographics.AverageHouseholdExpenditure,
HouseholdIncome = i.Demographics.MedianHouseholdIncome,
MedianAge = i.Demographics.MedianAge,
Population = i.Demographics.TotalPopulation,
UniversitiesNearby = i.Demographics.UniversitiesWithinHalfMile,
WhiteCollarWorkers = i.Demographics.WhiteCollarWorkersPercentage,
YoungEducated = i.Demographics.YoungEducatedPercentage,
AverageEmployees = i.IndustryData.AverageEmployees,
AverageRevenue = i.IndustryData.AverageRevenue,
RevenuePerCapita = i.IndustryData.RevenuePerCapita,
TotalEmployees = i.IndustryData.TotalEmployees,
TotalRevenue = i.IndustryData.TotalRevenue,
EmployeesPerCapita = i.IndustryData.EmployeesPerCapita,
Centroid = new Geo.LatLng
{
Lat = i.Geography.CenterLat.Value,
Lng = i.Geography.CenterLong.Value
},
BoundingBox = new Geo.BoundingBox
{
NorthEast = new Geo.LatLng
{
Lat = i.Geography.North,
Lng = i.Geography.East
},
SouthWest = new Geo.LatLng
{
Lat = i.Geography.South,
Lng = i.Geography.West
}
},
AverageEmployeesBand = i.IndustryData.Bands.Where(b => b.Attribute.Name == IndustryAttribute.AverageEmployees).Select(b => new Band <double> {
Min = (double)b.Min.Value, Max = (double)b.Max.Value
}).FirstOrDefault(),
TotalEmployeesBand = i.IndustryData.Bands.Where(b => b.Attribute.Name == IndustryAttribute.TotalEmployees).Select(b => new Band <double> {
Min = (double)b.Min.Value, Max = (double)b.Max.Value
}).FirstOrDefault(),
EmployeesPerCapitaBand = i.IndustryData.Bands.Where(b => b.Attribute.Name == IndustryAttribute.EmployeesPerCapita).Select(b => new Band <double> {
Min = (double)b.Min.Value, Max = (double)b.Max.Value
}).FirstOrDefault(),
AverageRevenueBand = i.IndustryData.Bands.Where(b => b.Attribute.Name == IndustryAttribute.AverageRevenue).Select(b => new Band <double> {
Min = (double)b.Min.Value, Max = (double)b.Max.Value
}).FirstOrDefault(),
TotalRevenueBand = i.IndustryData.Bands.Where(b => b.Attribute.Name == IndustryAttribute.TotalRevenue).Select(b => new Band <double> {
Min = (double)b.Min.Value, Max = (double)b.Max.Value
}).FirstOrDefault(),
RevenuePerCapitaBand = i.IndustryData.Bands.Where(b => b.Attribute.Name == IndustryAttribute.RevenuePerCapita).Select(b => new Band <double> {
Min = (double)b.Min.Value, Max = (double)b.Max.Value
}).FirstOrDefault()
});
if (regionId != null)
{
data = data.Where(i => i.Place.IntersectedGeographicLocations.Any(global => global.Id == regionId));
}
if (stateId != null)
{
data = data.Where(i => i.Place.IntersectedGeographicLocations.Any(global => global.Id == stateId));
}
data = FilterQuery(data, filters);
IQueryable <Models.BestPlaces> output = new List <Models.BestPlaces>().AsQueryable();
if (granularity == Granularity.City)
{
output = data
.Where(i => i.Place.City.IsActive && i.Place.City.CityType.IsActive)
.Select(i => new Models.BestPlaces
{
State = new Models.State
{
Id = i.Place.City.State.Id,
Abbreviation = i.Place.City.State.Abbreviation,
Name = i.Place.City.State.Name,
SEOKey = i.Place.City.State.SEOKey,
LongName = i.Place.City.State.GeographicLocation.LongName,
ShortName = i.Place.City.State.GeographicLocation.ShortName
},
County = i.Place.City.Counties.Select(co => new Models.County
{
Id = co.Id,
Name = co.Name,
SEOKey = co.SEOKey,
LongName = co.GeographicLocation.LongName,
ShortName = co.GeographicLocation.ShortName
}).FirstOrDefault(),
City = new Models.City
{
Id = i.Place.City.Id,
Name = i.Place.City.Name,
SEOKey = i.Place.City.SEOKey,
TypeName = i.Place.City.CityType.Name,
ShortName = i.Place.City.GeographicLocation.ShortName,
LongName = i.Place.City.GeographicLocation.LongName
},
Centroid = i.Centroid,
BoundingBox = i.BoundingBox,
TotalEmployees = i.TotalEmployeesBand,
AverageEmployees = i.AverageEmployeesBand,
EmployeesPerCapita = i.EmployeesPerCapitaBand,
TotalRevenue = i.TotalRevenueBand,
AverageRevenue = i.AverageRevenueBand,
RevenuePerCapita = i.RevenuePerCapitaBand
});
}
else if (granularity == Granularity.County)
{
output = data.Select(i => new Models.BestPlaces
{
State = new Models.State
{
Id = i.Place.County.State.Id,
Abbreviation = i.Place.County.State.Abbreviation,
Name = i.Place.County.State.Name,
SEOKey = i.Place.County.State.SEOKey,
LongName = i.Place.County.State.GeographicLocation.LongName,
ShortName = i.Place.County.State.GeographicLocation.ShortName
},
County = new Models.County
{
Id = i.Place.County.Id,
Name = i.Place.County.Name,
SEOKey = i.Place.County.SEOKey,
LongName = i.Place.County.GeographicLocation.LongName,
ShortName = i.Place.County.GeographicLocation.ShortName
},
Centroid = i.Centroid,
BoundingBox = i.BoundingBox,
TotalEmployees = i.TotalEmployeesBand,
AverageEmployees = i.AverageEmployeesBand,
EmployeesPerCapita = i.EmployeesPerCapitaBand,
TotalRevenue = i.TotalRevenueBand,
AverageRevenue = i.AverageRevenueBand,
RevenuePerCapita = i.RevenuePerCapitaBand
});
}
else if (granularity == Granularity.Metro)
{
output = data.Select(i => new Models.BestPlaces
{
Metro = new Models.Metro
{
Id = i.Place.Metro.Id,
Name = i.Place.Metro.Name,
SEOKey = i.Place.Metro.SEOKey,
LongName = i.Place.Metro.GeographicLocation.LongName,
ShortName = i.Place.Metro.GeographicLocation.ShortName
},
Centroid = i.Centroid,
BoundingBox = i.BoundingBox,
TotalEmployees = i.TotalEmployeesBand,
AverageEmployees = i.AverageEmployeesBand,
EmployeesPerCapita = i.EmployeesPerCapitaBand,
TotalRevenue = i.TotalRevenueBand,
AverageRevenue = i.AverageRevenueBand,
RevenuePerCapita = i.RevenuePerCapitaBand
});
}
else if (granularity == Granularity.State)
{
output = data.Select(i => new Models.BestPlaces
{
State = new Models.State
{
Id = i.Place.State.Id,
Abbreviation = i.Place.State.Abbreviation,
Name = i.Place.State.Name,
SEOKey = i.Place.State.SEOKey,
LongName = i.Place.State.GeographicLocation.LongName,
ShortName = i.Place.State.GeographicLocation.ShortName
},
Centroid = i.Centroid,
BoundingBox = i.BoundingBox,
TotalEmployees = i.TotalEmployeesBand,
AverageEmployees = i.AverageEmployeesBand,
EmployeesPerCapita = i.EmployeesPerCapitaBand,
TotalRevenue = i.TotalRevenueBand,
AverageRevenue = i.AverageRevenueBand,
RevenuePerCapita = i.RevenuePerCapitaBand
});
}
return(output);
}
protected static IQueryable <Wrapper> FilterQuery(IQueryable <Wrapper> data, BestPlacesFilters filters)
{
if (filters.AverageRevenue != null)
{
if (filters.AverageRevenue.Min.HasValue)
{
data = data.Where(i => i.AverageRevenue >= filters.AverageRevenue.Min);
}
if (filters.AverageRevenue.Max.HasValue)
{
data = data.Where(i => i.AverageRevenue <= filters.AverageRevenue.Max);
}
}
if (filters.TotalRevenue != null)
{
if (filters.TotalRevenue.Min.HasValue)
{
data = data.Where(i => i.TotalRevenue >= filters.TotalRevenue.Min);
}
if (filters.TotalRevenue.Max.HasValue)
{
data = data.Where(i => i.TotalRevenue <= filters.TotalRevenue.Max);
}
}
if (filters.TotalEmployees != null)
{
if (filters.TotalEmployees.Min.HasValue)
{
data = data.Where(i => i.TotalEmployees >= filters.TotalEmployees.Min);
}
if (filters.TotalEmployees.Max.HasValue)
{
data = data.Where(i => i.TotalEmployees <= filters.TotalEmployees.Max);
}
}
if (filters.AverageEmployees != null)
{
if (filters.AverageEmployees.Min.HasValue)
{
data = data.Where(i => i.AverageEmployees >= filters.AverageEmployees.Min);
}
if (filters.TotalEmployees.Max.HasValue)
{
data = data.Where(i => i.AverageEmployees <= filters.AverageEmployees.Max);
}
}
if (filters.RevenuePerCapita != null)
{
if (filters.RevenuePerCapita.Min.HasValue)
{
data = data.Where(i => i.RevenuePerCapita >= filters.RevenuePerCapita.Min);
}
if (filters.RevenuePerCapita.Max.HasValue)
{
data = data.Where(i => i.RevenuePerCapita <= filters.RevenuePerCapita.Max);
}
}
if (filters.HouseholdIncome != null)
{
if (filters.HouseholdIncome.Min.HasValue)
{
data = data.Where(i => i.HouseholdIncome >= filters.HouseholdIncome.Min);
}
if (filters.HouseholdIncome.Max.HasValue)
{
data = data.Where(i => i.HouseholdIncome <= filters.HouseholdIncome.Max);
}
}
if (filters.HouseholdExpenditures != null)
{
if (filters.HouseholdExpenditures.Min.HasValue)
{
data = data.Where(i => i.HouseholdExpenditures >= filters.HouseholdExpenditures.Min);
}
if (filters.HouseholdExpenditures.Max.HasValue)
{
data = data.Where(i => i.HouseholdExpenditures <= filters.HouseholdExpenditures.Max);
}
}
if (filters.MedianAge != null)
{
if (filters.MedianAge.Min.HasValue)
{
data = data.Where(i => i.MedianAge >= filters.MedianAge.Min);
}
if (filters.MedianAge.Max.HasValue)
{
data = data.Where(i => i.MedianAge <= filters.MedianAge.Max);
}
}
if (filters.BachelorOrHigher != null)
{
var v = filters.BachelorOrHigher / 100.0d;
data = data.Where(i => i.BachelorsDegreeOrHigher >= v);
}
if (filters.HighSchoolOrHigher != null)
{
var v = filters.HighSchoolOrHigher / 100.0d;
data = data.Where(i => i.HighSchoolOrHigher >= v);
}
if (filters.WhiteCollarWorkers != null)
{
var v = filters.WhiteCollarWorkers / 100.0d;
data = data.Where(i => i.WhiteCollarWorkers >= v);
}
if (filters.BlueCollarWorkers != null)
{
var v = filters.BlueCollarWorkers / 100.0d;
data = data.Where(i => i.BlueCollarWorkers >= v);
}
if (filters.YoungEducated != null)
{
var v = filters.YoungEducated / 100.0d;
data = data.Where(i => i.YoungEducated >= v);
}
if (filters.AirportsNearby != null)
{
data = data.Where(i => i.AirportsNearby >= filters.AirportsNearby);
}
if (filters.UniversitiesNearby != null)
{
data = data.Where(i => i.UniversitiesNearby >= filters.UniversitiesNearby);
}
if (filters.CommuteTime != null)
{
data = data.Where(i => i.CommuteTime <= filters.CommuteTime);
}
if (filters.Attribute == "totalRevenue")
{
data = data.Where(i => i.TotalRevenue != null && i.TotalRevenue > 0);
}
else if (filters.Attribute == "averageRevenue")
{
data = data.Where(i => i.AverageRevenue != null && i.AverageRevenue > 0);
}
else if (filters.Attribute == "revenuePerCapita")
{
data = data.Where(i => i.RevenuePerCapita != null && i.RevenuePerCapita > 0);
}
else if (filters.Attribute == "underservedMarkets")
{
data = data.Where(i => i.RevenuePerCapita != null && i.RevenuePerCapita > 0);
}
else if (filters.Attribute == "employeesPerCapita")
{
data = data.Where(i => i.EmployeesPerCapita != null && i.EmployeesPerCapita > 0);
}
else if (filters.Attribute == "householdIncome")
{
data = data.Where(i => i.HouseholdIncome != null && i.HouseholdIncome > 0);
}
else if (filters.Attribute == "whiteCollarWorkers")
{
data = data.Where(i => i.WhiteCollarWorkers != null && i.WhiteCollarWorkers > 0);
}
else if (filters.Attribute == "totalEmployees")
{
data = data.Where(i => i.TotalEmployees != null && i.TotalEmployees > 0);
}
else if (filters.Attribute == "averageEmployees")
{
data = data.Where(i => i.AverageEmployees != null && i.AverageEmployees > 0);
}
else if (filters.Attribute == "householdExpenditures")
{
data = data.Where(i => i.HouseholdExpenditures != null && i.HouseholdExpenditures > 0);
}
else if (filters.Attribute == "medianAge")
{
data = data.Where(i => i.MedianAge != null && i.MedianAge > 0);
}
else if (filters.Attribute == "bachelorsDegreeOrHigher")
{
data = data.Where(i => i.BachelorsDegreeOrHigher != null && i.BachelorsDegreeOrHigher > 0);
}
else if (filters.Attribute == "highSchoolOrHigher")
{
data = data.Where(i => i.HighSchoolOrHigher != null && i.HighSchoolOrHigher > 0);
}
else if (filters.Attribute == "blueCollarWorkers")
{
data = data.Where(i => i.BlueCollarWorkers != null && i.BlueCollarWorkers > 0);
}
else if (filters.Attribute == "airportsNearby")
{
data = data.Where(i => i.AirportsNearby != null && i.AirportsNearby > 0);
}
else if (filters.Attribute == "youngEducated")
{
data = data.Where(i => i.YoungEducated != null && i.YoungEducated > 0);
}
else if (filters.Attribute == "universitiesNearby")
{
data = data.Where(i => i.UniversitiesNearby != null && i.UniversitiesNearby > 0);
}
else if (filters.Attribute == "commuteTime")
{
data = data.Where(i => i.CommuteTime != null && i.CommuteTime > 0);
}
data = data.Where(i => i.Population >= POPULATION_MIN);
switch (filters.Attribute)
{
case "totalRevenue":
data = data.OrderByDescending(i => i.TotalRevenue);
break;
case "averageRevenue":
data = data.OrderByDescending(i => i.AverageRevenue);
break;
case "revenuePerCapita":
data = data.OrderByDescending(i => i.RevenuePerCapita);
break;
case "underservedMarkets":
data = data.OrderBy(i => i.RevenuePerCapita);
break;
case "employeesPerCapita":
data = data.OrderByDescending(i => i.EmployeesPerCapita);
break;
case "householdIncome":
data = data.OrderByDescending(i => i.HouseholdIncome);
break;
case "whiteCollarWorkers":
data = data.OrderByDescending(i => i.WhiteCollarWorkers);
break;
case "totalEmployees":
data = data.OrderByDescending(i => i.TotalEmployees);
break;
case "averageEmployees":
data = data.OrderByDescending(i => i.AverageEmployees);
break;
case "householdExpenditures":
data = data.OrderByDescending(i => i.HouseholdExpenditures);
break;
case "medianAge":
data = data.OrderByDescending(i => i.MedianAge);
break;
case "bachelorsDegreeOrHigher":
data = data.OrderByDescending(i => i.BachelorsDegreeOrHigher);
break;
case "highSchoolOrHigher":
data = data.OrderByDescending(i => i.HighSchoolOrHigher);
break;
case "blueCollarWorkers":
data = data.OrderByDescending(i => i.BlueCollarWorkers);
break;
case "airportsNearby":
data = data.OrderByDescending(i => i.AirportsNearby);
break;
case "youngEducated":
data = data.OrderByDescending(i => i.YoungEducated);
break;
case "universitiesNearby":
data = data.OrderByDescending(i => i.UniversitiesNearby);
break;
case "commuteTime":
data = data.OrderByDescending(i => i.CommuteTime);
break;
}
return(data);
}