/// <summary> /// Add values into a dictionary /// </summary> /// <param name="result"></param> /// <param name="list"></param> /// <param name="values"></param> /// <param name="valuesToStuff">If there are hardcoded values to stuff into dictionary, add a column name/value pair here</param> private static void AddValuesToDictionary(NycResultsWithMetadata result, ICollection <CheckedDictionary <string, object> > list, IEnumerable <IList <object> > values, Dictionary <String, String> valuesToStuff) { CheckedDictionary <string, object> dic; foreach (object[] val in values) { // Loop through the attributes we have, and get the values dic = new CheckedDictionary <string, object>(); for (int i = 0; i < result.Attrs.Count; i++) { // Get name/val pairs string name = result.Attrs[i].Name; dic.Add(name, val[i]); } // If there are values to stuff into this dictionary, do it here foreach (KeyValuePair <String, String> row in valuesToStuff) { dic.Add(row.Key, row.Value); } // Add the whole dictionary to a list of dictionaries list.Add(dic); } }
/// <summary> /// Convert a Pdb Property quert result into a CSV string. /// </summary> /// <param name="result">The result to convert</param> /// <param name="indicatorId">Indicator Id that represtents this export</param> /// <returns>A string of comma seperated values, with header row and /// escaped with "" around strings</returns> public static string ResultsAsCsv(NycResultsWithMetadata result, string indicatorId) { StringWriter writer = new StringWriter(); List <ClassMapColDefinition> colMap = new List <ClassMapColDefinition>(); // Additional Header properties NycIndicator indicator = GetIndicator(indicatorId); colMap.Add(new ClassMapColDefinition(_csvHeaderText, _csvHeaderText.Replace("<%DATE%>", DateTime.Now.ToShortDateString()), null)); colMap.Add(new ClassMapColDefinition("Indicator", "Indicator", null)); colMap.Add(new ClassMapColDefinition("Description", "Description", null)); // Some hardcoded values need to be added to each row Dictionary <String, String> valuesToStuff = new Dictionary <string, string>(); valuesToStuff.Add("Indicator", result.Indicator); valuesToStuff.Add("Description", indicator.Description); // Create some column mappings by looping through the attributes foreach (AbstractNamedSortable attr in result.Attrs) { // Only add it if we've got something if (attr.Name != null) { string displayName = attr.Name == "Area" ? result.Resolution : attr.Name; colMap.Add(new ClassMapColDefinition(attr.Name, displayName, null)); } } ClassMapping map = new ClassMapping("csv", "csv", colMap, false); DictionaryDao csvDao = new DictionaryDao(new CsvDescriptor(writer, CsvQuoteLevel.QuoteAlways), map); List <CheckedDictionary <string, object> > list = new List <CheckedDictionary <string, object> >(); if (result.ContextRows != null) { // Add context rows AddValuesToDictionary(result, list, result.ContextRows, valuesToStuff); } if (result.Values != null) { // Add value rows AddValuesToDictionary(result, list, result.Values, valuesToStuff); } // If there were no results, just give them nothing if (list.Count == 0) { return(""); } // Add these values to the csv writer csvDao.Insert(list); // Return our result return(writer.ToString()); }
/// <summary> /// Queries for Nychanis data for the specified year range, resolution, and indicator. /// </summary> /// <param name="indicatorId">ID of the indicator being queried.</param> /// <param name="resolutionType">What resolution are we querying for.</param> /// <param name="timeUnitType">What type of time units should the data be in?</param> /// <param name="startYear">First year to include in the results.</param> /// <param name="endYear">Last year to include in the results.</param> /// <param name="scopeSubborough"></param> /// <param name="scopeBorough"></param> /// <param name="orderCol">The column to sort by.</param> /// <param name="orderDir">The direction to sort, ignored if order is less than zero. /// <param name="numPerPage">Number of records to be returned in the page sequence, if not less than zero</param> /// <param name="page">Which page in this page sequence is this request for. /// If null, assumed to be ascending.</param> /// <returns>The results!</returns> public static NycResultsWithMetadata Query(object indicatorId, NycResolutionType resolutionType, NycTimeframeType timeUnitType, int startYear, int endYear, object scopeBorough, object scopeSubborough, int orderCol, SortType?orderDir, int numPerPage, int page) { NycResultsWithMetadata retVal = new NycResultsWithMetadata(); // First load the indicator metadata, both because we need the display name, and because // if the query is for data that doesn't exist, we can skip a lot of work. NycIndicator indicator = GetIndicator(indicatorId); retVal.Indicator = indicator.Name; retVal.Resolution = resolutionType.ToString(); // Now verify the query against the metadata. retVal.MinYear = (startYear < indicator.MinYear) ? indicator.MinYear : startYear; retVal.MaxYear = (endYear > indicator.MaxYear) ? indicator.MaxYear : endYear; bool validRequest = (retVal.MaxYear >= retVal.MinYear); if (!validRequest) { // Return a completely blank result object. return(retVal); } // We only want to load time metadata for times this indicator actually has data for. DaoJoinCriteria joinCrit = new DaoJoinCriteria(); joinCrit.RightCriteria = new DaoCriteria(); joinCrit.RightCriteria.Expressions.Add(new EqualExpression("IndicatorId", indicatorId)); joinCrit.RightCriteria.Expressions.Add(new EqualExpression("Resolution", resolutionType)); joinCrit.Expressions.Add(new EqualJoinExpression("UID", "TimeId")); // Load the time metadata. joinCrit.LeftCriteria = new DaoCriteria(); // These are not-ed so they are <= and >=; joinCrit.LeftCriteria.Expressions.Add(new GreaterExpression("Year", retVal.MaxYear, false)); joinCrit.LeftCriteria.Expressions.Add(new LesserExpression("Year", retVal.MinYear, false)); joinCrit.LeftCriteria.Expressions.Add(new EqualExpression("Type", timeUnitType)); // Sort by value descending, so the most recent year comes first. joinCrit.Orders.Add(new JoinSortOrder("Value", SortType.Asc, true)); List <JoinResult <NycTimeframe, NycResolutionForIndicator> > timeframes = _timeDao.Get(joinCrit, _resolutionDao); // We also need to know what all possible times are though, so we can render the fancy slider with appropriate gaps. joinCrit.LeftCriteria.Orders.Add(new SortOrder("Value", SortType.Asc)); IList <NycTimeframe> allTimeframes = _timeDao.Get(joinCrit.LeftCriteria); // Use them to assemble the metadata, since one year is one column. retVal.Attrs = new List <AbstractNamedSortable>(); retVal.Attrs.Add(new NycGeogColumnMetadata("Area")); IDictionary <object, int> colsByTimeId = new CheckedDictionary <object, int>(); foreach (JoinResult <NycTimeframe, NycResolutionForIndicator> timeframe in timeframes) { colsByTimeId[timeframe.Left.UID] = retVal.Attrs.Count; retVal.Attrs.Add(new NycYearColumnMetadata(timeframe.Left, indicator.ValueType)); } NycTableResults results = QueryNychanisData(indicatorId, resolutionType, scopeBorough, scopeSubborough, colsByTimeId); retVal.TotalResults = results.Values.Count; // Don't do any further processing if there are no results if (results.Values.Count == 0) { return(retVal); } // If the user specified a sort order, use that, otherwise sort by the first column (area). List <KeyValuePair <int, SortType> > colSorts = new List <KeyValuePair <int, SortType> >(); colSorts.Add(new KeyValuePair <int, SortType>(orderCol < 0 ? 0 : orderCol, orderDir ?? SortType.Asc)); results.Values.Sort(new MultipleColumnListComparer(colSorts)); // Truncate the results by the requested paging information retVal.Values = ResultsWithMetadata <AbstractNamedSortable> .GetPagedSubset(results.Values, numPerPage, page); // Now get context rows. Always get City, unless we're querying for City. if (resolutionType != NycResolutionType.City) { List <IList <object> > contextRows = QueryNychanisData(indicatorId, NycResolutionType.City, null, null, colsByTimeId).Values; // Now, if they provided a borough scope and didn't ask for a borough resolution, // include borough. if ((resolutionType != NycResolutionType.Borough) && (scopeBorough != null)) { contextRows.AddRange(QueryNychanisData(indicatorId, NycResolutionType.Borough, scopeBorough, null, colsByTimeId).Values); // Then if not subborough but a subborough is provided, include that. if ((resolutionType != NycResolutionType.SubBorough) && (scopeSubborough != null)) { contextRows.AddRange(QueryNychanisData(indicatorId, NycResolutionType.SubBorough, scopeBorough, scopeSubborough, colsByTimeId).Values); } } retVal.ContextRows = contextRows; } // Now generate the map info for showing the results as a cloropleth layer. Config cfg = Config.GetConfig("PDP.Data"); string sldHandlerUrl = cfg.GetParameter("Mapping", "SldHandlerURL"); retVal.MapInfo = new NycMapInfo(); retVal.MapInfo.Server = cfg.GetParameter("Mapping", "MapServerURL"); retVal.MapInfo.Layers = new List <NycLayerInfo>(); string layerName = Config.GetConfig("NYC.SLD").GetParameter("LayerNames", resolutionType.ToString(), null); // City doesn't have a map layer, so don't create any OL layers for it. if (layerName != null) { int possibleTimeIndex = 0; int actualTimeIndex = 0; while (possibleTimeIndex < allTimeframes.Count) { // We need to pad out the list of layers with blanks for timeframes that lack data. NycLayerInfo layer = new NycLayerInfo(); layer.Name = allTimeframes[possibleTimeIndex].Name; // Years that actually have data go up faster than all years, so check if the current // possible year is lower than the next actual year. if (allTimeframes[possibleTimeIndex].Value < timeframes[actualTimeIndex].Left.Value) { // Need to pad with a blank, so just let the blank layer object be added. // Increment the possible time index to the next timeframe. possibleTimeIndex++; } else { NycTimeframe time = timeframes[actualTimeIndex].Left; // I think this check is no longer necessary, since we're probably // always excluding unavailable data. But if it ain't broke... if (results.DataAvailableByTime[actualTimeIndex]) { layer.Config = new Dictionary <string, object>(); layer.Config["layers"] = layerName; layer.Config["styles"] = ""; layer.Config["format"] = "image/png"; layer.Config["tiled"] = true; layer.Config["srs"] = "EPSG:4326"; layer.Config["transparent"] = true; StringBuilder sb = new StringBuilder(sldHandlerUrl); sb.Append("?indicator=").Append(indicatorId); sb.Append("&resolution=").Append(resolutionType.ToString()); sb.Append("&time=").Append(time.UID); if (scopeBorough != null) { sb.Append("&borough=").Append(scopeBorough); if (scopeSubborough != null) { sb.Append("&subborough=").Append(scopeSubborough); } } layer.Config["SLD"] = sb.ToString(); } // Increment both indexes. possibleTimeIndex++; actualTimeIndex++; } retVal.MapInfo.Layers.Add(layer); } // If we are creating layers, we must create a legend to describe them retVal.LegendInfo = GenerateLegendList(indicator, resolutionType); } return(retVal); }