public ActionResult Bands(
long industryId, long boundingGeographicLocationId, int bands,
Core.DataLayer.Granularity granularity, string contentType = "*/*"
)
{
using (var context = ContextFactory.SizeUpContext)
{
if ("text/html".Equals(contentType))
{
Expression <Func <SizeUp.Data.IndustryData, bool> > filter = i => i.TotalRevenue != null;
Expression <Func <SizeUp.Data.IndustryData, Kpi.LabeledValue> > selector;
selector = i => new Kpi.LabeledValue
{
Label = i.GeographicLocation.LongName,
Value = i.TotalRevenue
};
Kpi.GetKpiModel(
ViewBag, context,
industryId, boundingGeographicLocationId, granularity,
filter, selector, "Total Revenue", "${0}", bands
);
return(View("Heatmap"));
}
else
{
var data = Core.DataLayer.TotalRevenue.Bands(context, industryId, boundingGeographicLocationId, bands, granularity);
return(Json(data, JsonRequestBehavior.AllowGet));
}
}
}
public static void GetKpiModel(
dynamic ViewBag, SizeUpContext context,
long industryId, long locationId, Core.DataLayer.Granularity granularity,
Expression <Func <IndustryData, bool> > filter,
Expression <Func <IndustryData, LabeledValue> > selector,
string kpiName, string formatString, int numBands
)
{
var gran = Enum.GetName(typeof(Core.DataLayer.Granularity), granularity);
var data = Kpi.GetKpiBands(context, industryId, locationId, gran, filter, selector, formatString, numBands);
// probably obsolete
ViewBag.BoundingEntity = context.GeographicLocations
.Where(i => i.Id == locationId)
.Select(i => i.LongName)
.FirstOrDefault();
// current
ViewBag.Area = ViewBag.BoundingEntity;
ViewBag.Bands = Kpi.FormatBands(data);
ViewBag.Industry = context.Industries
.Where(i => i.Id == industryId)
.Select(i => i.Name)
.FirstOrDefault();
ViewBag.Kpi = kpiName;
ViewBag.LevelOfDetail = Kpi.TranslateGranularity(granularity);
ViewBag.Q = numBands.ToString();
ViewBag.ThemeUrl = System.Configuration.ConfigurationManager.AppSettings["Theme.Url"];
ViewBag.Query = string.Format(
"Rank {0} in {1}, by {2} of {3} businesses, in {4} quantiles",
ViewBag.LevelOfDetail, ViewBag.Area, ViewBag.Kpi, ViewBag.Industry, ViewBag.Q
);
}
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));
}
}
/**
* Also lifted from AccessibilityController code. Not to endorse how this works, just
* relocating the code verbatim for now.
*/
public static string TranslateGranularity(Core.DataLayer.Granularity granularity)
{
if (granularity == Core.DataLayer.Granularity.ZipCode)
{
return("Zip Codes");
}
else if (granularity == Core.DataLayer.Granularity.County)
{
return("Counties");
}
else if (granularity == Core.DataLayer.Granularity.State)
{
return("States");
}
else
{
throw new KeyNotFoundException("Could not resolve granularity: " + granularity.ToString());
}
}
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));
}
}
public ActionResult TotalRevenue(int bands, int industryId, long boundingGeographicLocationId, Core.DataLayer.Granularity granularity)
{
ViewBag.Header = new Models.Header()
{
HideNavigation = true
};
var gran = Enum.GetName(typeof(Core.DataLayer.Granularity), granularity);
using (var context = ContextFactory.SizeUpContext)
{
var data = Core.DataLayer.IndustryData.Get(context)
.Where(i => i.GeographicLocation.GeographicLocations.Any(gl => gl.Id == boundingGeographicLocationId))
.Where(i => i.IndustryId == industryId)
.Where(i => i.GeographicLocation.Granularity.Name == gran)
.Select(i => new
{
Label = i.GeographicLocation.LongName,
Value = i.TotalRevenue
})
.ToList()
.NTileDescending(i => i.Value, bands)
.Select(i => new Band
{
Min = string.Format("${0}", Format(i.Min(v => v.Value.Value))),
Max = string.Format("${0}", Format(i.Max(v => v.Value.Value))),
Items = i.Select(v => v.Label).ToList()
})
.ToList();
if (granularity == Core.DataLayer.Granularity.ZipCode)
{
ViewBag.LevelOfDetail = "Zip Code";
}
else if (granularity == Core.DataLayer.Granularity.County)
{
ViewBag.LevelOfDetail = "County";
}
else if (granularity == Core.DataLayer.Granularity.State)
{
ViewBag.LevelOfDetail = "State";
}
ViewBag.Bands = FormatBands(data);
ViewBag.BoundingEntity = context.GeographicLocations.Where(i => i.Id == boundingGeographicLocationId).Select(i => i.LongName).FirstOrDefault();
ViewBag.Attribute = "Total Revenue";
return(View("Heatmap"));
}
}
public ActionResult Competition(long variableId, long boundingGeographicLocationId, int bands, Core.DataLayer.Granularity granularity)
{
ViewBag.Header = new Models.Header()
{
HideNavigation = true
};
using (var context = ContextFactory.SizeUpContext)
{
var variable = Variables(context).Where(i => i.Id == variableId).Select(i => i.Variable).FirstOrDefault();
var gran = Enum.GetName(typeof(Core.DataLayer.Granularity), granularity);
int granularityId = 0;
if (granularity == Core.DataLayer.Granularity.ZipCode)
{
granularityId = 1;
}
else if (granularity == Core.DataLayer.Granularity.City)
{
granularityId = 2;
}
else if (granularity == Core.DataLayer.Granularity.County)
{
granularityId = 3;
}
else if (granularity == Core.DataLayer.Granularity.Place)
{
granularityId = 4;
}
else if (granularity == Core.DataLayer.Granularity.Metro)
{
granularityId = 5;
}
else if (granularity == Core.DataLayer.Granularity.State)
{
granularityId = 6;
}
else if (granularity == Core.DataLayer.Granularity.Nation)
{
granularityId = 7;
}
var data = Get(context)
.Where(i => i.GeographicLocation.Granularity.Name == gran)
.Where(i => i.GeographicLocation.GeographicLocations.Any(g => g.Id == boundingGeographicLocationId));
int count = data.ToList().Count();
string queryString = string.Format("select LongName, {0} " +
"from dbo.ConsumerExpenditures as ce " +
"join dbo.GeographicLocation as gl on ce.GeographicLocationId = gl.Id " +
"where ce.GeographicLocationId in ( " +
"SELECT gl.GeographicLocationId " +
"FROM dbo.GeographicLocation as g " +
"join dbo.GeographicLocationGeographicLocation as gl on g.Id = gl.GeographicLocationId " +
"where GranularityId={1} and gl.IntersectedGeographicLocationId = {2})", variable, granularityId, boundingGeographicLocationId);
var conn = new EntityConnection(ConfigurationManager.ConnectionStrings["SizeUpContext"].ConnectionString);
List <Payload> t = new List <Payload>();
using (SqlConnection connection = new SqlConnection(conn.StoreConnection.ConnectionString))
{
SqlCommand command =
new SqlCommand(queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
// Call Read before accessing data.
while (reader.Read())
{
t.Add(new Payload()
{
Name = reader[0].ToString(), Value = Convert.ToInt64(reader[1])
});
}
// Call Close when done reading.
reader.Close();
}
var output = t
.NTileDescending(i => i.Value, bands)
.Select(i => new Band
{
Min = string.Format("${0}", Format(i.Min(v => v.Value))),
Max = string.Format("${0}", Format(i.Max(v => v.Value))),
Items = i.Select(v => v.Name).ToList()
})
.ToList();
if (granularity == Core.DataLayer.Granularity.ZipCode)
{
ViewBag.LevelOfDetail = "Zip Code";
}
else if (granularity == Core.DataLayer.Granularity.County)
{
ViewBag.LevelOfDetail = "County";
}
else if (granularity == Core.DataLayer.Granularity.State)
{
ViewBag.LevelOfDetail = "State";
}
ViewBag.Bands = FormatBands(output);
ViewBag.BoundingEntity = context.GeographicLocations.Where(i => i.Id == boundingGeographicLocationId).Select(i => i.LongName).FirstOrDefault();
ViewBag.Attribute = "Competition";
return(View("Heatmap"));
}
}
//
// GET: /Tiles/Revenue/
public ActionResult Index(int x, int y, int zoom, long industryId, long boundingGeographicLocationId, string startColor, string endColor, int bands, Core.DataLayer.Granularity granularity, int width = 256, int height = 256)
{
using (var context = ContextFactory.SizeUpContext)
{
Heatmap tile = new Heatmap(width, height, x, y, zoom);
BoundingBox boundingBox = tile.GetBoundingBox(TileBuffer);
double tolerance = GetPolygonTolerance(zoom);
var boundingGeo = boundingBox.GetDbGeography();
var gran = Enum.GetName(typeof(Core.DataLayer.Granularity), granularity);
var geos = Core.DataLayer.GeographicLocation.Get(context)
.Where(i => i.Granularity.Name == gran)
.Where(i => i.GeographicLocations.Any(g => g.Id == boundingGeographicLocationId));
var data = Core.DataLayer.IndustryData.Get(context).Where(i => i.IndustryId == industryId);
var list = geos
.GroupJoin(data, i => i.Id, o => o.GeographicLocationId, (i, o) => new { IndustryData = o, GeographicLocation = i })
.Select(i => new KeyValue <DbGeography, Band <double> >
{
Key = i.GeographicLocation.Geographies.Where(g => g.GeographyClass.Name == Core.Geo.GeographyClass.Display)
.Select(g => SqlSpatialFunctions.Reduce(g.Polygon, tolerance).Intersection(boundingGeo)).FirstOrDefault(),
Value = i.IndustryData.Select(d => d.Bands.Where(b => b.Attribute.Name == IndustryAttribute.CostEffectiveness).Select(b => new Band <double> {
Min = (double)b.Min.Value, Max = (double)b.Max.Value
}).FirstOrDefault()).FirstOrDefault()
}).ToList();
var quantiles = list
.Where(i => i.Value != null)
.NTileDescending(i => i.Value.Max, bands);
ColorBands colorBands = new Core.Tiles.ColorBands(System.Drawing.ColorTranslator.FromHtml("#" + startColor), System.Drawing.ColorTranslator.FromHtml("#" + endColor), quantiles.Count());
string[] bandList = colorBands.GetColorBands().ToArray();
var validValues = quantiles
.Select((i, index) => i.Where(g => g.Key != null).Select(g => new GeographyEntity()
{
Geography = SqlGeography.Parse(g.Key.AsText()), Color = bandList[index]
}))
.SelectMany(i => i)
.ToList();
var invalidValues = list
.Where(i => i.Value == null)
.Where(i => i.Key != null)
.Select(g => new GeographyEntity()
{
Geography = SqlGeography.Parse(g.Key.AsText())
})
.ToList();
var output = validValues.Union(invalidValues).ToList();
tile.Draw(output);
var stream = new System.IO.MemoryStream();
tile.Bitmap.Save(stream, System.Drawing.Imaging.ImageFormat.Png);
return(File(stream.GetBuffer(), "image/png"));
}
}
//
// GET: /Tiles/Revenue/
public ActionResult Index(int x, int y, int zoom, long variableId, long boundingGeographicLocationId, string startColor, string endColor, int bands, Core.DataLayer.Granularity granularity = Core.DataLayer.Granularity.State, int width = 256, int height = 256)
{
using (var context = ContextFactory.SizeUpContext)
{
Heatmap tile = new Heatmap(width, height, x, y, zoom);
BoundingBox boundingBox = tile.GetBoundingBox(TileBuffer);
double tolerance = GetPolygonTolerance(zoom);
var boundingGeo = boundingBox.GetDbGeography();
var variable = Core.DataLayer.ConsumerExpenditures.Variables(context).Where(i => i.Id == variableId).Select(i => i.Variable).FirstOrDefault();
var gran = Enum.GetName(typeof(Core.DataLayer.Granularity), granularity);
var geos = Core.DataLayer.GeographicLocation.Get(context)
.Where(i => i.Granularity.Name == gran)
.Where(i => i.GeographicLocations.Any(g => g.Id == boundingGeographicLocationId));
var data = Core.DataLayer.ConsumerExpenditures.Get(context);
//.Where(i => i.GeographicLocation.Granularity.Name == gran)
//.Where(i => i.GeographicLocation.GeographicLocations.Any(g => g.Id == boundingGeographicLocationId));
ConstantExpression constant = Expression.Constant(data); //empty set
IQueryProvider provider = data.Provider;
Type dataType = typeof(ConsumerExpenditure);
var param = Expression.Parameter(dataType, "c");
var varSelector = Expression.Convert(Expression.Property(param, variable), typeof(long?)) as Expression;
var idSelector = Expression.Property(param, "GeographicLocationId") as Expression;
var transType = typeof(KeyValue <long, long?>);
var constructor = transType.GetConstructor(new Type[] { typeof(long), typeof(long?) });
var selector = Expression.New(constructor, new Expression[] { idSelector, varSelector }.AsEnumerable(), new System.Reflection.MemberInfo[] { transType.GetProperty("Key"), transType.GetProperty("Value") });
var pred = Expression.Lambda(selector, param) as Expression;
var expression = Expression.Call(typeof(Queryable), "Select", new Type[] { dataType, transType }, constant, pred);
var transformedData = data.Provider.CreateQuery <KeyValue <long, long?> >(expression);
var list = geos.GroupJoin(transformedData, i => i.Id, o => o.Key, (i, o) => new { Data = o, GeographicLocation = i })
.Select(i => new KeyValue <DbGeography, long?>
{
Key = i.GeographicLocation.Geographies.Where(g => g.GeographyClass.Name == Core.Geo.GeographyClass.Display)
.Select(g => SqlSpatialFunctions.Reduce(g.Polygon, tolerance).Intersection(boundingGeo)).FirstOrDefault(),
Value = i.Data.Select(d => d.Value).FirstOrDefault()
}).ToList();
var quantiles = list
.Where(i => i.Value != null)
.NTileDescending(i => i.Value, bands);
ColorBands colorBands = new Core.Tiles.ColorBands(System.Drawing.ColorTranslator.FromHtml("#" + startColor), System.Drawing.ColorTranslator.FromHtml("#" + endColor), quantiles.Count());
string[] bandList = colorBands.GetColorBands().ToArray();
var validValues = quantiles
.Select((i, index) => i.Where(g => g.Key != null).Select(g => new GeographyEntity()
{
Geography = SqlGeography.Parse(g.Key.AsText()), Color = bandList[index]
}))
.SelectMany(i => i)
.ToList();
var invalidValues = list
.Where(i => i.Value == null || i.Value <= 0)
.Where(i => i.Key != null)
.Select(g => new GeographyEntity()
{
Geography = SqlGeography.Parse(g.Key.AsText())
})
.ToList();
var output = validValues.Union(invalidValues).ToList();
tile.Draw(output);
var stream = new System.IO.MemoryStream();
tile.Bitmap.Save(stream, System.Drawing.Imaging.ImageFormat.Png);
return(File(stream.GetBuffer(), "image/png"));
}
}
public ActionResult Bands(long industryId, long boundingGeographicLocationId, int bands, Core.DataLayer.Granularity granularity, string contentType = "*/*")
{
// contentType arg should also be checked for application/json and application/javascript,
// but those seem to be inferred by other code like APIContext.IsJsonp, alluded to in
// Controller.Json, and etc. For now we'll leave those alone and just check for:
if ("text/html".Equals(contentType))
{
using (var context = ContextFactory.SizeUpContext)
{
Expression <Func <SizeUp.Data.IndustryData, bool> > filter = i => i.AverageRevenue != null;
Expression <Func <SizeUp.Data.IndustryData, Kpi.LabeledValue> > selector;
selector = i => new Kpi.LabeledValue
{
Label = i.GeographicLocation.LongName,
Value = i.AverageRevenue
};
Kpi.GetKpiModel(
ViewBag, context,
industryId, boundingGeographicLocationId, granularity,
filter, selector, "Average Annual Revenue", "${0}", bands
);
return(View("Heatmap"));
}
}
else
{
using (var context = ContextFactory.SizeUpContext)
{
var data = Core.DataLayer.AverageRevenue.Bands(context, industryId, boundingGeographicLocationId, bands, granularity);
return(Json(data, JsonRequestBehavior.AllowGet));
}
}
}
public ActionResult Bands(long industryId, long boundingGeographicLocationId, int bands, Core.DataLayer.Granularity granularity)
{
using (var context = ContextFactory.SizeUpContext)
{
var data = Core.DataLayer.TotalEmployees.Bands(context, industryId, boundingGeographicLocationId, bands, granularity);
return(Json(data, JsonRequestBehavior.AllowGet));
}
}
public ActionResult Bands(
int variableId, long boundingGeographicLocationId, int bands,
Core.DataLayer.Granularity granularity, string contentType = "*/*")
{
using (var context = ContextFactory.SizeUpContext)
{
if ("text/html".Equals(contentType))
{
var variable = context.ConsumerExpenditureVariables
.Where(i => i.Id == variableId)
.FirstOrDefault();
var gran = Enum.GetName(typeof(Core.DataLayer.Granularity), granularity);
int granularityId = 0;
if (granularity == Core.DataLayer.Granularity.ZipCode)
{
granularityId = 1;
}
else if (granularity == Core.DataLayer.Granularity.City)
{
granularityId = 2;
}
else if (granularity == Core.DataLayer.Granularity.County)
{
granularityId = 3;
}
else if (granularity == Core.DataLayer.Granularity.Place)
{
granularityId = 4;
}
else if (granularity == Core.DataLayer.Granularity.Metro)
{
granularityId = 5;
}
else if (granularity == Core.DataLayer.Granularity.State)
{
granularityId = 6;
}
else if (granularity == Core.DataLayer.Granularity.Nation)
{
granularityId = 7;
}
var data = context.ConsumerExpenditures
.Where(i => i.Year == CommonFilters.TimeSlice.ConsumerExpenditures.Year && i.Quarter == CommonFilters.TimeSlice.ConsumerExpenditures.Quarter)
.Where(i => i.GeographicLocation.Granularity.Name == gran)
.Where(i => i.GeographicLocation.GeographicLocations.Any(g => g.Id == boundingGeographicLocationId));
int count = data.ToList().Count();
var output = GetPayloads(variable.Variable, granularityId, boundingGeographicLocationId)
.NTileDescending(i => i.Value, bands)
.Select(i => new Kpi.Band
{
Min = string.Format("${0}", Kpi.Format(i.Min(v => v.Value))),
Max = string.Format("${0}", Kpi.Format(i.Max(v => v.Value))),
Items = i.Select(v => v.Name).ToList()
})
.ToList();
ViewBag.Area = context.GeographicLocations
.Where(i => i.Id == boundingGeographicLocationId)
.Select(i => i.LongName)
.FirstOrDefault();
ViewBag.Bands = Kpi.FormatBands(output);
ViewBag.Expenditure = variable;
ViewBag.LevelOfDetail = Kpi.TranslateGranularity(granularity);
ViewBag.Q = bands.ToString();
ViewBag.ThemeUrl = System.Configuration.ConfigurationManager.AppSettings["Theme.Url"];
ViewBag.Query = string.Format(
"Rank {0} in {1}, by {2} Consumer Spending on {3}, in {4} quantiles",
ViewBag.LevelOfDetail, ViewBag.Area,
variable.Variable.StartsWith("X") ? "Average" : "Total",
variable.Description, ViewBag.Q
);
return(View("Heatmap"));
}
else
{
var output = Core.DataLayer.ConsumerExpenditures.Bands(context, variableId, boundingGeographicLocationId, bands, granularity);
return(Json(output, JsonRequestBehavior.AllowGet));
}
}
}
