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