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