public override List<NiveisDescendentes> get_NiveisDescendentes(long IDNivel, long IDTrustee, IDbConnection conn) { List<NiveisDescendentes> result = new List<NiveisDescendentes>(); try { string query = string.Format(" FROM RelacaoHierarquica WHERE IDUpper = {0} AND isDeleted = 0 ", IDNivel); PermissoesRule.Current.GetEffectiveReadPermissions(query, IDTrustee, conn); query = string.Format(@" SELECT nfilho.ID, nfilho.IDTipoNivel, tnr.Codigo AS Codigo_TipoNivelRelacionado, tnr.Designacao AS Designacao_TipoNivelRelacionado, d.Termo Termo_Estrutural_Filho FROM Nivel n INNER JOIN #effective E ON e.IDNivel = n.ID INNER JOIN RelacaoHierarquica rh ON rh.IDUpper = n.ID AND rh.isDeleted = 0 INNER JOIN Nivel nfilho ON nfilho.ID = rh.ID AND nfilho.isDeleted = 0 LEFT JOIN NivelDesignado ndf ON ndf.ID = nfilho.ID AND ndf.isDeleted = 0 INNER JOIN TipoNivelRelacionado tnr ON tnr.ID = rh.IDTipoNivelRelacionado AND tnr.isDeleted = 0 LEFT JOIN NivelControloAut nca ON nca.ID = nfilho.ID AND nca.isDeleted = 0 LEFT JOIN ControloAutDicionario cad ON cad.IDControloAut = nca.IDControloAut AND cad.IDTipoControloAutForma = 1 AND cad.isDeleted = 0 LEFT JOIN Dicionario d ON d.ID = cad.IDDicionario AND d.isDeleted = 0 WHERE n.ID = {0} AND n.isDeleted = 0 AND E.Ler = 1 ", IDNivel, IDTrustee); SqlCommand command = new SqlCommand(query, (SqlConnection)conn); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { NiveisDescendentes ndesc = new NiveisDescendentes(); ndesc.IDNivelPai = IDNivel; ndesc.IDNivel = reader.GetInt64(0); ndesc.TipoNivel = reader.GetInt64(1); if (!reader.IsDBNull(4)) ndesc.Termo_Estrutural_Filho = reader.GetString(4); ndesc.geracao = 0; result.Add(ndesc); } reader.Close(); PermissoesRule.Current.DropEffectivePermissionsTempTable(conn); } catch (Exception ex) { Trace.WriteLine(ex); throw; } return result; }
/* * Fecho transitivo (para descendentes): */ public override List<NiveisDescendentes> get_All_NiveisDescendentes(long IDNivelTopo, long IDTrustee, IDbConnection conn) { List<NiveisDescendentes> result = new List<NiveisDescendentes>(); try { string query = string.Format(@" WITH Descs (ID, IDUpper, gen) AS ( SELECT ID, IDUpper, 0 AS gen FROM RelacaoHierarquica WHERE IDUpper = {0} AND isDeleted = 0 UNION ALL SELECT rh.ID, rh.IDUpper, gen + 1 FROM RelacaoHierarquica rh INNER JOIN Descs d ON d.ID = rh.IDUpper WHERE rh.isDeleted = 0 ) SELECT DISTINCT ID, IDUpper, gen INTO #Descs FROM Descs", IDNivelTopo); SqlCommand command = new SqlCommand(query, (SqlConnection)conn); command.ExecuteNonQuery(); PermissoesRule.Current.GetEffectiveReadPermissions(" FROM #Descs ", IDTrustee, conn); query = @" SELECT DISTINCT d.ID, d.IDUpper, n.IDTipoNivel, tnr.Codigo AS Codigo_TipoNivelRelacionado, tnr.Designacao AS Designacao_TipoNivelRelacionado, dict.Termo Termo_Estrutural_Filho, d.gen FROM #Descs d INNER JOIN #effective E ON E.IDNivel = d.ID INNER JOIN RelacaoHierarquica rh ON rh.ID = d.ID AND rh.isDeleted = 0 INNER JOIN Nivel n ON n.ID = rh.ID AND n.isDeleted = 0 INNER JOIN TipoNivelRelacionado tnr ON tnr.ID = rh.IDTipoNivelRelacionado AND tnr.isDeleted = 0 LEFT JOIN NivelControloAut nca ON nca.ID = d.ID AND nca.isDeleted = 0 LEFT JOIN ControloAutDicionario cad ON cad.IDControloAut = nca.IDControloAut AND cad.IDTipoControloAutForma = 1 AND cad.isDeleted = 0 LEFT JOIN Dicionario dict ON dict.ID = cad.IDDicionario AND dict.isDeleted = 0 WHERE E.Ler = 1 ORDER BY d.IDUpper, d.ID"; command.CommandText = query; SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { NiveisDescendentes ndesc = new NiveisDescendentes(); ndesc.IDNivelPai = reader.GetInt64(1); ndesc.IDNivel = reader.GetInt64(0); ndesc.TipoNivel = reader.GetInt64(2); if (!reader.IsDBNull(4)) ndesc.Termo_Estrutural_Filho = reader.GetString(3); ndesc.geracao = reader.GetInt32(6); result.Add(ndesc); } reader.Close(); PermissoesRule.Current.DropEffectivePermissionsTempTable(conn); command.CommandText = "DROP TABLE #Descs"; command.ExecuteNonQuery(); } catch (Exception ex) { Trace.WriteLine(ex); throw; } return result; }