public static IList <User> GetUsers(int page, int pageSize, SortOrder sortOrder) { // Get all users, within the paging restrictions, sorted by Name DaoCriteria crit = new DaoCriteria(); // Start recordset at the begining of the page crit.Start = (page * pageSize) - (pageSize); // Number of records to include crit.Limit = pageSize; // Order it by the requested column if (sortOrder != null) { crit.Orders.Add(sortOrder); } else { // Have a default sort of Name, Asc if nothing passed in crit.Orders.Add(new SortOrder("Name", SortType.Asc)); } // Execute and return the query return(_userDao.Get(crit)); }
/// <summary> /// Extracts the resolution metadata from the geograpy metadata. /// NOTE: At the moment there is no way to get resolution metadata by itself /// since it is not stored separately from geography metadata. /// </summary> /// <returns></returns> public static IList <NycResolution> GetResolutionMetadata() { // First get all the geographies, sorting as we do so. DaoCriteria crit = new DaoCriteria(); crit.Orders.Add(new SortOrder("Order")); IList <NycGeography> geographies = _geogDao.Get(crit); // Now sort 'em up into groups by resolution. IDictionary <NycResolutionType, NycResolution> foundSoFar = new Dictionary <NycResolutionType, NycResolution>(); foreach (NycGeography geog in geographies) { // Only construct a resolution object once per type found. if (!foundSoFar.ContainsKey(geog.Resolution)) { foundSoFar[geog.Resolution] = new NycResolution(geog); } else { foundSoFar[geog.Resolution].Add(geog); } } List <NycResolution> retVal = new List <NycResolution>(foundSoFar.Values); // Don't need to sort the individual geographies because the DB call did that for us. retVal.Sort(); return(retVal); }
private static List <PdbCriteriaAttributeMetadata> SimplifyAndGetValues(IList <PdbAttribute> attrs) { List <PdbCriteriaAttributeMetadata> retVal = new List <PdbCriteriaAttributeMetadata>(); DaoCriteria valCrit = new DaoCriteria(); foreach (PdbAttribute attr in attrs) { // As a performance tweak, only get values for categorical attributes, // since those are the only ones (at the moment) that have values in // the attribute_values table. List <IDictionary <string, object> > legitValues = null; if (attr.HasValueList()) { valCrit.Expressions.Clear(); valCrit.Expressions.Add(new EqualExpression("AttributeName", attr.Name)); IList <PdbAttributeValue> values = _attrValDao.Get(valCrit); legitValues = new List <IDictionary <string, object> >(); foreach (PdbAttributeValue val in values) { // Since we're encoding this as json, we want a short encoding. Dictionary <string, object> strippedValue = new Dictionary <string, object>(); strippedValue["Value"] = val.Value; strippedValue["Group"] = val.Group; legitValues.Add(strippedValue); } } // Copy the values we want to let the client know about into an object to // be returned. retVal.Add(new PdbCriteriaAttributeMetadata(attr, legitValues)); } return(retVal); }
public void TestGetPrimaryMetadataForEveryone() { // Blow away excessive nychanis data. FastDAO <NycDatum> dataDao = new FastDAO <NycDatum>("PDP.Data", "NYCHANIS"); DaoCriteria crit = new DaoCriteria(); crit.Expressions.Add(new EqualExpression("IndicatorId", 201, false)); crit.Expressions.Add(new GreaterExpression("IndicatorId", 10)); dataDao.Delete(crit); // And also excessive PDB data. PdbTwoTableHelper helper = new PdbTwoTableHelper(Config.GetConfig("PDP.Data"), "Properties", PdbEntityType.Properties); DictionaryDao primaryPdbDao = new DictionaryDao( dataDao.ConnDesc, helper.GetClassMapForPrimaryTable(new SecurityRole[] { SecurityRole.@public })); FastDAO <PdbSecondaryTableProperty> secondaryPdbDao = new FastDAO <PdbSecondaryTableProperty>( dataDao.ConnDesc, helper.GetClassMapForSecondaryTable()); crit.Expressions.Clear(); crit.Expressions.Add(new GreaterExpression("UID", 100630)); primaryPdbDao.Delete(crit); crit.Expressions.Clear(); crit.Expressions.Add(new GreaterExpression("ForeignKey", 100630)); secondaryPdbDao.Delete(crit); }
/// <summary> /// Returns the metadata about the attributes that are columns on the primary /// table. Does not include the allowed values for those columns that have them. /// </summary> /// <param name="entityType">Only Properties is supported.</param> /// <param name="userRoles">The roles the current user has. /// Only attributes they can see will be returned.</param> /// <returns>All the attribute metadata for the specified columns.</returns> public static IList <PdbAttribute> GetPrimaryTableColumns( PdbEntityType entityType, IEnumerable <SecurityRole> userRoles) { DaoCriteria crit = new DaoCriteria(); crit.Expressions.Add(new EqualExpression("EntityType", entityType)); crit.Expressions.Add(new EqualExpression("InPrimaryTable", true)); return(GetAttribRecords(crit, userRoles)); }
public static IList <T> GetRows <T>(string nlihcId, IList <SecurityRole> roles) where T : class, IDisplaySortable, new() { var dao = new FastDAO <T>(Config.GetConfig("PDP.Data"), "PDB"); var crit = new DaoCriteria(new EqualExpression("NlihcId", nlihcId)); var sortField = Activator.CreateInstance <T>().GetSortField(); crit.Orders.Add(new SortOrder(sortField, SortType.Desc)); return(DisplayForRole(ResourceMap[typeof(T)], roles) ? dao.Get(crit) : null); }
/// <summary> /// Deletes the record of the given user. /// </summary> /// <param name="userName">The user to be deleted.</param> /// <returns>The number of records deleted. Should be 1 when successful.</returns> public static int DeleteUser(string userName) { // If there is a user, return the record if (StringHelper.IsNonBlank(userName)) { DaoCriteria crit = new DaoCriteria(); crit.Expressions.Add(new EqualExpression("UserName", userName)); return(_userDao.Delete(crit)); } return(0); }
public static IList <PdbUploadRevision> GetUploadRevisions(UploadTypes type) { var crit = new DaoCriteria(); crit.Expressions.Add(new EqualExpression("Type", type.ToString())); crit.Orders.Add(new SortOrder("Date", SortType.Desc)); crit.Limit = MaxRevisionsReturned; var revisions = _urDao.Get(crit); return(revisions); }
public static IList <Comment> GetAuthorizedComments(string nlihcId, User user) { var crit = new DaoCriteria(); crit.Expressions.Add(new EqualExpression("NlihcId", nlihcId)); // Anonymous users get only public comments if (user == null) { crit.Expressions.Add(new EqualExpression("AccessLevel", CommentAccessLevel.Public)); return(_dao.Get(crit)); } // This kind of query is difficult in FastDAO, so, expecting that the number // of comments on a given property will be reasonable, prune off unauthorized // comments from the entire property comment list var comments = _dao.Get(crit); // SysAdmins can see everything if (user.IsSysAdmin()) { return(comments); } var authComments = new List <Comment>(); foreach (var comment in comments) { switch (comment.AccessLevel) { case CommentAccessLevel.Public: authComments.Add(comment); break; case CommentAccessLevel.Network: if (user.IsNetworked()) { authComments.Add(comment); } break; case CommentAccessLevel.SameOrg: if (user.Organization == comment.AssociatedOrgId) { authComments.Add(comment); } break; } } return(authComments); }
/// <summary> /// Gets all the attributes, without values, as a dictionary keyed by attribute display name. /// </summary> /// <param name="entityType">Only Properties is supported.</param> /// <param name="userRoles">The roles the current user has. /// Only attributes they can see will be returned.</param> /// <returns>All the attributes visible to someone with these roles.</returns> public static IDictionary <string, PdbAttribute> GetAttributesDictionary(PdbEntityType entityType, IEnumerable <SecurityRole> userRoles) { DaoCriteria crit = new DaoCriteria(); crit.Expressions.Add(new EqualExpression("EntityType", entityType)); IList <PdbAttribute> attrList = GetAttribRecords(crit, userRoles, true); IDictionary <string, PdbAttribute> retVal = new CheckedDictionary <string, PdbAttribute>(); foreach (PdbAttribute attr in attrList) { retVal[attr.UID] = attr; } return(retVal); }
private static IList <PdbAttribute> GetAttribRecords( DaoCriteria crit, IEnumerable <SecurityRole> userRoles, bool addExtraCols) { IList <PdbAttribute> retVal = new List <PdbAttribute>(); // We have to post-process the roles because the required role could be anything // but the user role could just be "SysAdmin" so there's no easy way to have the DB // filter that for us. IList <PdbAttribute> colsForAllRoles = _attrDao.Get(crit); foreach (PdbAttribute col in colsForAllRoles) { // null required role is never shown to anyone. if (col.RequiredRole != null) { foreach (SecurityRole role in userRoles) { if ((role & col.RequiredRole) != 0 || role == SecurityRole.SysAdmin) { // User can see this one, no need to check the other roles. retVal.Add(col); break; } } } } // Lat/Long/Id should not be in attribute metadata - you cannot search on it. if (addExtraCols) { // Lat, Long and ID need to be in every search, no matter the role. We are using their // ColMapped name, not their table name PdbAttribute extra = new PdbAttribute(); extra.Name = "Lat"; retVal.Add(extra); extra = new PdbAttribute(); extra.Name = "Lon"; retVal.Add(extra); extra = new PdbAttribute(); extra.Name = "UID"; retVal.Add(extra); } return(retVal); }
/// <summary> /// Runs a query for the data for a particular indicator/resolution/time, /// and generates an SLD to color the geographies correctly based on the values. /// </summary> /// <param name="indicatorId"></param> /// <param name="resolutionType"></param> /// <param name="timeId"></param> /// <param name="scopeBorough"></param> /// <param name="scopeSubborough"></param> /// <returns></returns> public static string GenerateSld(object indicatorId, NycResolutionType resolutionType, object timeId, object scopeBorough, object scopeSubborough) { // First load the indicator metadata, both because we need the info, and because // if the query is for data that doesn't exist, we can skip a lot of work. NycIndicator indicator = GetIndicator(indicatorId); // Don't validate time, since there's no easy way to do it other than querying and seeing // if we get anything. // Now load the configs that tell us what color to render everything as. Config cfg = Config.GetConfig("NYC.SLD"); // Acceptable values are 0 to 100. So we need a 101 length array. string[] colors = new string[101]; string color = null; IDictionary <string, IList <object> > geogIdsByColor = new CheckedDictionary <string, IList <object> >(); for (int x = 0; x < colors.Length; x++) { // If the value isn't in the config, use the same as the last value. color = cfg.GetParameter(indicator.UseAlternateColors ? "AlternateBreakpoints" : "BreakPoints", x.ToString(), color); // Make sure we have a list created for the geogs that will be this color. if ((color != null) && (!geogIdsByColor.ContainsKey(color))) { geogIdsByColor[color] = new List <object>(); } colors[x] = color; } string mapLayerName = cfg.GetParameter("LayerNames", resolutionType.ToString(), null); string layerGeogIdField = cfg.GetParameter("LayerGeographyIdFields", resolutionType.ToString(), null); string layerDisplayNameField = cfg.GetParameter("LayerDisplayNameFields", resolutionType.ToString(), null); // This includes any labeling and outlines, default background color, etc. StringBuilder sldRules = new StringBuilder(cfg.GetConfigInnerXml("DefaultSLD")); // Replace any tokens with the appropriate values for this layer. sldRules.Replace("{LayerName}", mapLayerName); sldRules.Replace("{LayerGeographyIdField}", layerGeogIdField); sldRules.Replace("{LayerDisplayNameField}", layerDisplayNameField); // If no map layer is defined (I.E. "City"), or the request is invalid in some way, // use just the default SLD. if (mapLayerName != null) { // Now query for all the data. DaoCriteria crit = new DaoCriteria(); crit.Expressions.Add(new EqualExpression("IndicatorId", indicatorId)); crit.Expressions.Add(new EqualExpression("Resolution", resolutionType)); crit.Expressions.Add(new EqualExpression("TimeId", timeId)); DaoCriteria geogCrit = null; if (scopeBorough != null) { geogCrit = new DaoCriteria(); geogCrit.Expressions.Add(new EqualExpression("Borough", scopeBorough)); if (scopeSubborough != null) { geogCrit.Expressions.Add(new EqualExpression("SubBorough", scopeSubborough)); } } if (geogCrit != null) { // Scope and/or subscope was defined. geogCrit.Expressions.Add(new EqualExpression("Resolution", resolutionType)); IList <object> geogIdsInScope = new List <object>(); foreach (NycGeography geog in _geogDao.Get(geogCrit)) { geogIdsInScope.Add(geog.UID); } crit.Expressions.Add(new PropertyInListExpression("GeographyId", geogIdsInScope)); } IList <NycDatum> data = _dataDao.Get(crit); // If there's no data, don't make any SLD rules. if ((data != null) && (data.Count > 0)) { foreach (NycDatum datum in data) { int val; switch (indicator.Breakpoint) { case NycBreakpointType.HistoricalBreakpoint: val = datum.HistoricalBreakpoint; break; case NycBreakpointType.ContemporaryBreakpoint: val = datum.ContemporaryBreakpoint; break; default: throw new ArgumentOutOfRangeException("Indicator " + indicator + " has invalid breakpoint: " + indicator.Breakpoint); } if ((val < 0) || (val > 100)) { _log.Warn("'Normalized' value " + datum + " was outside 0-100 range."); } // Figure out, based on the value, which color group it belongs to. string thisColor = colors[val]; if (thisColor != null) { // Put it in that group. geogIdsByColor[thisColor].Add(datum.GeographyId); } } // Now append the rules for those color groups. foreach (KeyValuePair <string, IList <object> > kvp in geogIdsByColor) { // Only add rules for colors that actually have values. if (kvp.Value.Count > 0) { sldRules.Append("<Rule>"); // The part that says what geography IDs get this color. sldRules.Append("<ogc:Filter>"); sldRules.Append("<ogc:Or>"); foreach (object geogId in kvp.Value) { sldRules.Append("<ogc:PropertyIsEqualTo>"); sldRules.Append("<ogc:PropertyName>").Append(layerGeogIdField).Append( "</ogc:PropertyName>"); sldRules.Append("<ogc:Literal>").Append(geogId).Append("</ogc:Literal>"); sldRules.Append("</ogc:PropertyIsEqualTo>"); } sldRules.Append("</ogc:Or>"); sldRules.Append("</ogc:Filter>"); // The part that says what color to fill with. sldRules.Append("<PolygonSymbolizer>"); sldRules.Append("<Fill>"); sldRules.Append("<CssParameter name=\"fill\">").Append(kvp.Key).Append("</CssParameter>"); sldRules.Append("<CssParameter name=\"fill-opacity\">").Append( cfg.GetParameter("Polygons", "Opacity")).Append("</CssParameter>"); sldRules.Append("</Fill>"); sldRules.Append("</PolygonSymbolizer>"); sldRules.Append("</Rule>"); } } } } // Now wrap all the rendering rules in the SLD outer tags. StringBuilder sld = new StringBuilder(); sld.Append("<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>\n") .Append("<StyledLayerDescriptor version=\"1.0.0\"") .Append(" xsi:schemaLocation=\"http://www.opengis.net/sld StyledLayerDescriptor.xsd\"") .Append(" xmlns=\"http://www.opengis.net/sld\"") .Append(" xmlns:ogc=\"http://www.opengis.net/ogc\"") .Append(" xmlns:xlink=\"http://www.w3.org/1999/xlink\"") .Append(" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">") .Append("<NamedLayer>") .Append("<Name>").Append(mapLayerName).Append("</Name>") .Append("<UserStyle>") .Append("<FeatureTypeStyle>") .Append(sldRules.ToString()) .Append("</FeatureTypeStyle>") .Append("</UserStyle>") .Append("</NamedLayer>") .Append("</StyledLayerDescriptor>"); return(sld.ToString()); }
private static NycTableResults QueryNychanisData(object indicatorId, NycResolutionType resolutionType, object scopeBorough, object scopeSubborough, IDictionary <object, int> colsByTimeId) { // Query both the geography and data tables in a join so we can get the // nice display name of the geography rather than just the ID. NOTE: This could // be done by obtaining the geographies in a single query then the data in a second // query. At the moment this seems better. DaoJoinCriteria joinCrit = new DaoJoinCriteria(); joinCrit.Expressions.Add(new EqualJoinExpression("GeographyId", "UID")); // Add filters to the data (left) side of the join. DaoCriteria dataCrit = new DaoCriteria(); joinCrit.LeftCriteria = dataCrit; // Only the indicator that was requested. dataCrit.Expressions.Add(new EqualExpression("IndicatorId", indicatorId)); // Only the resolution that was requested. dataCrit.Expressions.Add(new EqualExpression("Resolution", resolutionType)); // Only the time span that was requested. dataCrit.Expressions.Add(new PropertyInListExpression("TimeId", colsByTimeId.Keys)); // Add support for querying for a single borough or subborough, "scope" DaoCriteria scopeCrit = new DaoCriteria(); if (scopeBorough != null) { scopeCrit.Expressions.Add(new EqualExpression("Borough", scopeBorough)); if (scopeSubborough != null) { scopeCrit.Expressions.Add(new EqualExpression("SubBorough", scopeSubborough)); } } // If we added any expressions, add the criteria to the geography side of the join if (scopeCrit.Expressions.Count > 0) { joinCrit.RightCriteria = scopeCrit; } // Sort the results by geography then by time (descending). This allows us to populate // the results structure easily, even if we later resort based on the user's choice. // We sort first by geography name, which "should" be unique, but just in case we also // sort by geography ID. joinCrit.Orders.Add(new JoinSortOrder("Name", false)); joinCrit.Orders.Add(new JoinSortOrder("GeographyId", true)); joinCrit.Orders.Add(new JoinSortOrder("TimeId", SortType.Desc, true)); IList <JoinResult <NycDatum, NycGeography> > dataAndGeogs = _dataDao.Get(joinCrit, _geogDao); NycTableResults results = new NycTableResults(); List <IList <object> > values = new List <IList <object> >(); // Now, for each geography ID, add all its year values to a list and add to the values collection. if (dataAndGeogs.Count > 0) { object lastGeogId = null; IList <object> resultArray = null; IList <bool> dataAvailableByTime = new bool[colsByTimeId.Count]; foreach (JoinResult <NycDatum, NycGeography> result in dataAndGeogs) { if ((lastGeogId == null) || (!lastGeogId.Equals(result.Left.GeographyId))) { // Create a new array, at the correct length already in case there // are holes in the data. resultArray = new object[colsByTimeId.Count + 1]; resultArray[0] = result.Right.Name; lastGeogId = result.Left.GeographyId; // Add it to the list to be returned. values.Add(resultArray); } // Always populate this value in the array. resultArray[colsByTimeId[result.Left.TimeId]] = result.Left.Value; dataAvailableByTime[colsByTimeId[result.Left.TimeId] - 1] = true; } results.DataAvailableByTime = dataAvailableByTime; } results.Values = values; return(results); }
/// <summary> /// Gets all columns (visible to the user anyway) and includes the allowed /// values for columns that have entries in the Attribute_Values table. /// </summary> /// <param name="entityType">Only Properties is supported.</param> /// <param name="userRoles">The roles the current user has. /// Only attributes they can see will be returned.</param> /// <returns>All the attribute metadata for the specified columns.</returns> public static IList <PdbCategory> GetAttributesForClient( PdbEntityType entityType, IEnumerable <SecurityRole> userRoles) { IDictionary <string, IDictionary <string, IList <PdbAttribute> > > attrsByCatAndSub = new CheckedDictionary <string, IDictionary <string, IList <PdbAttribute> > >(); IDictionary <string, IList <PdbAttribute> > attrsByCat = new CheckedDictionary <string, IList <PdbAttribute> >(); DaoCriteria crit = new DaoCriteria(); crit.Expressions.Add(new EqualExpression("EntityType", entityType)); IList <PdbAttribute> attrs = GetAttribRecords(crit, userRoles); // Get all the attributes and split 'em up. Put them either into the single // or double-nested dictionary depending on if they have subcategories. foreach (PdbAttribute attr in attrs) { IDictionary <string, IList <PdbAttribute> > catSubCats; IList <PdbAttribute> catAttrs; if (attrsByCatAndSub.ContainsKey(attr.Category)) { catSubCats = attrsByCatAndSub[attr.Category]; catAttrs = attrsByCat[attr.Category]; } else { catSubCats = new CheckedDictionary <string, IList <PdbAttribute> >(); catAttrs = new List <PdbAttribute>(); attrsByCatAndSub[attr.Category] = catSubCats; attrsByCat[attr.Category] = catAttrs; } // Now either it has a subcategory, in which case it's filed there, or // it doesn't, and it's filed under the category directly. if (StringHelper.IsNonBlank(attr.SubCategory)) { IList <PdbAttribute> subcatList; if (catSubCats.ContainsKey(attr.SubCategory)) { subcatList = catSubCats[attr.SubCategory]; } else { subcatList = new List <PdbAttribute>(); catSubCats[attr.SubCategory] = subcatList; } subcatList.Add(attr); } else { catAttrs.Add(attr); } } // Now they're all split up, create the returnable object types. // Remember it is impossible for any of the collections to be empty, since we created // them all based on records we had. List <PdbCategory> retVal = new List <PdbCategory>(); foreach (KeyValuePair <string, IDictionary <string, IList <PdbAttribute> > > kvp in attrsByCatAndSub) { IDictionary <string, IList <PdbAttribute> > subCatLists = kvp.Value; List <PdbSubCategory> subCats = new List <PdbSubCategory>(); PdbAttribute anAttr = null; foreach (IList <PdbAttribute> subCatList in subCatLists.Values) { subCats.Add(new PdbSubCategory(subCatList[0].SubCategory, subCatList[0].FilterAttrOrder, SimplifyAndGetValues(subCatList))); // save one indicator for the category info. if (anAttr == null) { anAttr = subCatList[0]; } } if (anAttr == null) { // subCatList and attrsByCat can't BOTH be empty or we wouldn't have this category. anAttr = attrsByCat[kvp.Key][0]; } retVal.Add(new PdbCategory(anAttr.Category, anAttr.FilterCatOrder, SimplifyAndGetValues(attrsByCat[kvp.Key]), subCats)); } retVal.Sort(); return(retVal); }
private static IList <PdbAttribute> GetAttribRecords( DaoCriteria crit, IEnumerable <SecurityRole> userRoles) { return(GetAttribRecords(crit, userRoles, false)); }