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