private void SetFilters() { if (this.AreaFilterQueryString == null) { return; } if (AreaTypeMappings.GetAreaTypeCategory(this.AreaTypeIdIs) != Util.AreaTypeMappings.AreaTypeCategory.AdministrativeArea) { throw new NotImplementedException( "Area filters are only available for administrative areas"); } var parser = new Parsers.AreaFilterParser(); parser.ValueHandler = delegate(object val) { return("@" + this.Parameters.AddValue(val)); }; parser.IdHandler = delegate(string name) { if (AreaTypeMappings.GetAreaTypeCategory(name) != Util.AreaTypeMappings.AreaTypeCategory.AdministrativeArea) { throw new NotImplementedException( "Area filtering can only be done with administrative areas"); } var schema = AreaTypeMappings.GetDatabaseSchema(name); string idColumn = schema["MainIdColumn"]; idColumn = SchemaDataFormat(idColumn); return(idColumn); }; parser.SpatialHandler = delegate( string geom1, string geom2, string func) { throw new NotImplementedException( "Spatial filtering not supported by area queries"); }; string whereString = parser.Parse(this.AreaFilterQueryString); this.whereList.Add(whereString); }
private void SetFilters() { Parsers.AreaFilterParserVisitor.ValueHandlerDelegate valueHandler = delegate(object val) { return("@" + this.Parameters.AddValue(val)); }; if (this.HomeFilterQueryString != null) { var parser = new Parsers.AreaFilterParser(); parser.ValueHandler = valueHandler; parser.IdHandler = delegate(string name) { this.ReduceUsableAreaTypes_Home(name); var schema = AreaTypeMappings.GetDatabaseSchema(name); return(string.Format(schema["MainIdColumn"], "A2_home", "A_home")); }; parser.SpatialHandler = this.SpatialHandler("A_home", "A2_home"); string whereString = parser.Parse(this.HomeFilterQueryString); this.whereList.Add(whereString); } if (this.WorkFilterQueryString != null) { var parser = new Parsers.AreaFilterParser(); parser.ValueHandler = valueHandler; parser.IdHandler = delegate(string name) { this.ReduceUsableAreaTypes_Work(name); var schema = AreaTypeMappings.GetDatabaseSchema(name); return(string.Format(schema["MainIdColumn"], "A2_work", "A_work")); }; parser.SpatialHandler = this.SpatialHandler("A_work", "A2_work"); string whereString = parser.Parse(this.WorkFilterQueryString); this.whereList.Add(whereString); } }
protected void SetFilters() { if (this.AreaFilterQueryString != null) { var parser = new Parsers.AreaFilterParser(); parser.ValueHandler = delegate(object val) { return("@" + this.Parameters.AddValue(val)); }; parser.IdHandler = delegate(string name) { // currently disabled areaTypes should only affect grouping, // so this block of code is disabled if (false && this.BlockedAreaTypes != null && this.BlockedAreaTypes.Contains(name)) { /* normally the user should never reach this exception, * since the available areaTypes are already listed in the * indicator */ throw new Exception(string.Format( "Specified filtering areaType \"{2}\" is disabled for statisticsId:{0} year:{1}", this.IdIs, this.YearIs, name)); } this.ReduceUsableAreaTypes(name); var schema = AreaTypeMappings.GetDatabaseSchema(name); string idColumn = schema["MainIdColumn"]; idColumn = SchemaDataFormat(idColumn); /* "Luokka" tables need to be added with these */ if (!this.filterJoins.Contains(schema["FilterJoinQuery"]) && schema["FilterJoinQuery"] != null && schema["FilterJoinQuery"].Length > 0) { this.filterJoins.Add(SchemaDataFormat( schema["FilterJoinQuery"])); } return(idColumn); }; /* We get both spatial atoms here (geom1, geom2), * one of them is the raw areaType, and another is the * SQL server geometry. At this point it is not known * which is which. */ parser.SpatialHandler = delegate( string geom1, string geom2, string func) { string areaType; Dictionary <string, string> schema; /* guess which one is geometry, so the other one * should be areaType */ /* NOTE: areaType is user input, handle carefully! */ if (geom1.StartsWith("geometry::") || geom1.StartsWith("@")) { areaType = geom2; schema = AreaTypeMappings.GetDatabaseSchema(areaType); geom2 = SchemaDataFormat(schema["GeometryColumn"]); // geom1 should be geometry } else { areaType = geom1; schema = AreaTypeMappings.GetDatabaseSchema(areaType); geom1 = SchemaDataFormat(schema["GeometryColumn"]); // geom2 should be geometry } // currently disabled areaTypes should only affect grouping, // so this block of code is disabled if (false && this.BlockedAreaTypes != null && this.BlockedAreaTypes.Contains(areaType)) { /* normally the user should never reach this exception, * since the available areaTypes are already listed in the * indicator */ throw new Exception(string.Format( "Specified filtering areaType \"{2}\" is disabled for statisticsId:{0} year:{1}", this.IdIs, this.YearIs, areaType)); } this.ReduceUsableAreaTypes(areaType); string paramName = "SpatialParam_" + (++this.GeometryParameterCount).ToString(); this.sbPreQuery.Append(string.Format( "CREATE TABLE #{0} (id INT NOT NULL)\n", paramName)); this.sbPostQuery.Append(string.Format( "DROP TABLE #{0}\n", paramName)); /* this.sbPreQuery.Append(string.Format( * "DECLARE @{0} TABLE (id INT NOT NULL)\n", * paramName)); */ int databaseAreaTypeId = AreaTypeMappings.GetPrimaryDatabaseAreaType(areaType); this.sbPreQuery.Append(string.Format( "INSERT INTO #{0} SELECT {1} FROM {2} WHERE {3} = {4} AND {5}.{6}({7}) = 1\n", paramName, SchemaDataFormat(schema["SubIdColumn"]), SchemaDataFormat(schema["SubFromString"]), SchemaDataFormat("{0}.AlueTaso_ID"), databaseAreaTypeId, geom1, func, geom2)); /* only the spatial operation here to make sure we are * using the spatial index */ /* * this.sbPreQuery.Append(string.Format( * "INSERT INTO @{0} SELECT {1} FROM {2} WHERE {3}.{4}({5}) = 1\n", * paramName, * SchemaDataFormat(schema["SubIdColumn"]), * SchemaDataFormat(schema["SubFromString"]), * geom1, * func, * geom2)); */ string expr = string.Format( "{0} IN (SELECT id FROM #{1})", SchemaDataFormat(schema["MainIdColumn"]), paramName); return(expr); }; string whereString = parser.Parse(this.AreaFilterQueryString); this.whereList.Add(whereString); } }