private List<HierarchyMember> GetHierarchyFromHierarchyRowList(HierarchyMember parent) { List<HierarchyMember> res = new List<HierarchyMember>(); List<string> children; //Select roots if (string.IsNullOrEmpty(parent.PathFromStart)) { children = rows .Where(r => r.PathMod.Split(new[] { '|' }).Length > 1) .Select(r => r.PathMod.Substring(0, r.PathMod.IndexOf('|') )).Distinct().ToList(); } else { children = rows .Where(r => r.PathMod.StartsWith(parent.PathFromStart) && r.PathMod != parent.PathFromStart) .Select(r => r.PathMod.Substring(parent.PathFromStart.Length + 1) ) .Select(path=>path.Contains("|") ? path.Substring(0, path.IndexOf('|')) : path) .Where(path=>!string.IsNullOrEmpty(path)) .Distinct() .ToList(); } foreach (var child in children) { string parentPath = parent.PathFromStart + (string.IsNullOrEmpty(parent.PathFromStart) ? "" : "|"); int allocCount = rows.Where(r => r.PathMod.StartsWith(parentPath + child)).Sum(r => r.Nb); HierarchyMember member = new HierarchyMember() { AllocataireCount = allocCount, NodeName = child, PathFromStart = parentPath + child, Rate = allocCount * 100 / parent.AllocataireCount, Depth = parent.Depth+1 }; if (member.Depth <= 10) member.Children = GetHierarchyFromHierarchyRowList(member); else member.Children = new List<HierarchyMember>(); res.Add(member); } return res; }
private HierarchyMember GetTreeFromDb() { using (var conn = new SqlConnection(connectionString)) { SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT [Nb] ,LEN([PathMod]) -LEN(REPLACE([PathMod], '|', '')) as nbSep ,[PathMod] FROM [Hackathon].[dbo].[FAB2] f"; conn.Open(); var reader = cmd.ExecuteReader(); while(reader.Read()) { rows.Add( new HierarchyRow() { Nb = reader.GetInt32(0), NbSep = (int)reader.GetInt64(1), PathMod = reader.GetString(2) }); } reader.Close(); conn.Close(); } HierarchyMember hierarchy = new HierarchyMember() { NodeName = "Tous les alocataires", PathFromStart = string.Empty, AllocataireCount = rows.Sum(c => c.Nb), Depth = 0 }; hierarchy.Children = GetHierarchyFromHierarchyRowList(hierarchy); return hierarchy; }