public Hierarchy(HierarchyDef definition, string name) : base(name) { Definition = definition; Levels = new HierarchyLevelCollection(this); // Definition == null - Json deserialization if (Definition != null) { Levels.Add(new HierarchyLevel(AllLevelName)); foreach (LevelDef level in definition.Levels) { Levels.Add(new HierarchyLevel(level.Name)); } } }
public LevelDefCollection(HierarchyDef hierarchy) : base() { Hierarchy = hierarchy; }
private string GetSql() { HierarchyDef def = Hierarchy.Definition; List <Table> tables = new List <Table>(); def.Levels.ForEach(_ => { Table t = _.Attribute.KeyColumns[0].Table; if (!tables.Contains(t)) { tables.Add(t); } }); List <Relationship> path = new List <Relationship>(); for (int i = tables.Count - 1; i > 0; i--) { Table table = tables[i]; Relationship rel = table.FindRelationshipByDestination(tables[i - 1]); if (rel != null) { path.Add(rel); } } for (int i = 0; i < tables.Count; i++) { tables[i].Alias = "t" + (i + 1).ToString(); } StringBuilder sb = new StringBuilder(); sb.Append("select distinct "); int index = 0; List <string> names = new List <string>(); foreach (LevelDef level in def.Levels) { AttributeDef attr = level.Attribute; if (index > 0) { sb.Append(","); } sb.AppendFormat("{0} as [___hq_key_{1}]", attr.KeyColumns.Expression, index); if (attr.OrderBy == AttributeOrderBy.Name) { names.Add(string.Format("{0} as [___hq_name_{1}]", attr.NameColumn.Name, index)); } index++; } names.ForEach(_ => sb.AppendFormat(",{0}", _)); sb.Append(" from "); index = 0; foreach (Table table in tables) { if (index > 0) { sb.Append(","); } sb.AppendFormat("[{0}] AS {1}", table.Name, table.Alias); index++; } if (path.Count > 0) { sb.Append(" where "); index = 0; foreach (Relationship rel in path) { if (rel.SourceColumns.Count == rel.DestinationColumns.Count) { for (int i = 0; i < rel.SourceColumns.Count; i++) { if (index > 0) { sb.Append(" AND "); } sb.AppendFormat( "{0} = {1} ", rel.SourceColumns[i].QualifiedName, rel.DestinationColumns[i].QualifiedName ); index++; } } else { throw new Exception("Inconsistent column counts in relationship."); } } } sb.Append(" order by "); for (int i = 0; i < def.Levels.Count; i++) { AttributeDef attr = def.Levels[i].Attribute; if (i > 0) { sb.Append(","); } if (attr.OrderBy == AttributeOrderBy.Key) { sb.AppendFormat("[___hq_key_{0}]", i); } else { sb.AppendFormat("[___hq_name_{0}]", i); } } return(sb.ToString()); }