public override List <PermissoesRule.ObjDig> LoadDataObjDigital(DataSet CurrentDataSet, long IDNivel, long IDTrustee, long IDLoginTrustee, out Dictionary <long, Dictionary <long, byte> > permsImpl, IDbConnection conn)
{
var res = new List <PermissoesRule.ObjDig>();
permsImpl = new Dictionary <long, Dictionary <long, byte> >();
using (var command = new SqlCommand("", (SqlConnection)conn))
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
command.CommandText = "CREATE TABLE #temp(ID BIGINT PRIMARY KEY); CREATE TABLE #odsTemp(ID BIGINT PRIMARY KEY, pid NVARCHAR(20), titulo NVARCHAR(768));";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO #temp VALUES (@IDNivel)";
command.Parameters.AddWithValue("@IDNivel", IDNivel);
command.ExecuteNonQuery();
command.CommandText = @"
WITH Temp (ID, IDUpper)
AS (
SELECT rh.ID, rh.IDUpper
FROM RelacaoHierarquica rh
WHERE rh.IDUpper = @IDNivel AND rh.isDeleted = @isDeleted
UNION ALL
SELECT rh.ID, rh.IDUpper
FROM RelacaoHierarquica rh
INNER JOIN Temp ON Temp.ID = rh.IDUpper
WHERE rh.isDeleted = @isDeleted
)
INSERT INTO #temp
SELECT Temp.ID
FROM Temp";
command.Parameters.AddWithValue("@isDeleted", 0);
command.ExecuteNonQuery();
PermissoesRule.Current.GetEffectiveReadPermissions(" FROM #temp ", IDLoginTrustee, conn);
command.CommandText = "DELETE FROM #temp WHERE ID IN (SELECT IDNivel FROM #effective WHERE Ler = @Ler OR Ler IS NULL)";
command.Parameters.AddWithValue("@Ler", 0);
command.ExecuteNonQuery();
PermissoesRule.Current.DropEffectivePermissionsTempTable(conn);
command.CommandText = @"
INSERT INTO #odsTemp
SELECT ID, pid, Titulo
FROM (
SELECT od.ID, od.pid, od.Titulo
FROM #temp T
INNER JOIN FRDBase frd ON frd.IDNivel = T.ID AND frd.IDTipoFRDBase = @IDTipoFRDBase AND frd.isDeleted = @isDeleted
INNER JOIN SFRDImagem img ON img.IDFRDBase = frd.ID AND img.Tipo = @imgTipo AND img.isDeleted = @isDeleted
INNER JOIN SFRDImagemObjetoDigital imgOD ON imgOD.IDFRDBase = img.IDFRDBase AND imgOD.idx = img.idx AND imgOD.isDeleted = @isDeleted
INNER JOIN ObjetoDigital od ON od.ID = imgOD.IDObjetoDigital AND od.isDeleted = @isDeleted
UNION ALL
SELECT odSimples.ID, odSimples.pid, odSimples.Titulo
FROM #temp T
INNER JOIN FRDBase frd ON frd.IDNivel = T.ID AND frd.IDTipoFRDBase = @IDTipoFRDBase AND frd.isDeleted = @isDeleted
INNER JOIN SFRDImagem img ON img.IDFRDBase = frd.ID AND img.Tipo = @imgTipo AND img.isDeleted = @isDeleted
INNER JOIN SFRDImagemObjetoDigital imgOD ON imgOD.IDFRDBase = img.IDFRDBase AND imgOD.idx = img.idx AND imgOD.isDeleted = @isDeleted
INNER JOIN ObjetoDigital od ON od.ID = imgOD.IDObjetoDigital AND od.isDeleted = @isDeleted
INNER JOIN ObjetoDigitalRelacaoHierarquica odrh ON odrh.IDUpper = od.ID AND odrh.isDeleted = @isDeleted
INNER JOIN ObjetoDigital odSimples ON odSimples.ID = odrh.ID AND odSimples.isDeleted = @isDeleted
) ods";
command.Parameters.AddWithValue("@IDTipoFRDBase", 1);
command.Parameters.AddWithValue("@imgTipo", "Fedora");
command.ExecuteNonQuery();
PermissoesRule.Current.GetODEffectivePermissions(" FROM #odsTemp ", IDLoginTrustee, conn);
command.CommandText = "DELETE FROM #odsTemp WHERE ID IN (SELECT DISTINCT ID FROM #effective WHERE IsGrant = @IsGrant OR IsGrant IS NULL)";
command.Parameters.AddWithValue("@IsGrant", 0);
command.ExecuteNonQuery();
PermissoesRule.Current.DropEffectivePermissionsTempTable(conn);
command.CommandText = "SELECT * FROM #odsTemp";
var reader = command.ExecuteReader();
var od = new PermissoesRule.ObjDig();
while (reader.Read())
{
od = new PermissoesRule.ObjDig();
od.ID = reader.GetInt64(0);
od.pid = reader.GetString(1);
od.titulo = reader.GetString(2);
res.Add(od);
}
reader.Close();
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["ObjetoDigital"],
"INNER JOIN #odsTemp ON #odsTemp.ID = ObjetoDigital.ID ");
da.Fill(CurrentDataSet, "ObjetoDigital");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TrusteeUser"]);
da.Fill(CurrentDataSet, "TrusteeUser");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TrusteeGroup"]);
da.Fill(CurrentDataSet, "TrusteeGroup");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["UserGroups"]);
da.Fill(CurrentDataSet, "UserGroups");
// carregar permissões
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TrusteeObjetoDigitalPrivilege"],
"INNER JOIN #odsTemp ON #odsTemp.ID = TrusteeObjetoDigitalPrivilege.IDObjetoDigital " +
"WHERE TrusteeObjetoDigitalPrivilege.IDTrustee = " + IDTrustee);
da.Fill(CurrentDataSet, "TrusteeObjetoDigitalPrivilege");
command.CommandText = "DROP TABLE #temp; DROP TABLE #odsTemp; ";
command.ExecuteNonQuery();
}
return(res);
}
public override List<PermissoesRule.ObjDig> LoadDataObjDigital(DataSet CurrentDataSet, long IDNivel, long IDTrustee, long IDLoginTrustee, out Dictionary<long, Dictionary<long, byte>> permsImpl, IDbConnection conn)
{
var res = new List<PermissoesRule.ObjDig>();
permsImpl = new Dictionary<long, Dictionary<long, byte>>();
using (var command = new SqlCommand("", (SqlConnection)conn))
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
command.CommandText = "CREATE TABLE #temp(ID BIGINT PRIMARY KEY); CREATE TABLE #odsTemp(ID BIGINT PRIMARY KEY, pid NVARCHAR(20), titulo NVARCHAR(768));";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO #temp VALUES (@IDNivel)";
command.Parameters.AddWithValue("@IDNivel", IDNivel);
command.ExecuteNonQuery();
command.CommandText = @"
WITH Temp (ID, IDUpper)
AS (
SELECT rh.ID, rh.IDUpper
FROM RelacaoHierarquica rh
WHERE rh.IDUpper = @IDNivel AND rh.isDeleted = @isDeleted
UNION ALL
SELECT rh.ID, rh.IDUpper
FROM RelacaoHierarquica rh
INNER JOIN Temp ON Temp.ID = rh.IDUpper
WHERE rh.isDeleted = @isDeleted
)
INSERT INTO #temp
SELECT Temp.ID
FROM Temp";
command.Parameters.AddWithValue("@isDeleted", 0);
command.ExecuteNonQuery();
PermissoesRule.Current.GetEffectiveReadPermissions(" FROM #temp ", IDLoginTrustee, conn);
command.CommandText = "DELETE FROM #temp WHERE ID IN (SELECT IDNivel FROM #effective WHERE Ler = @Ler OR Ler IS NULL)";
command.Parameters.AddWithValue("@Ler", 0);
command.ExecuteNonQuery();
PermissoesRule.Current.DropEffectivePermissionsTempTable(conn);
command.CommandText = @"
INSERT INTO #odsTemp
SELECT ID, pid, Titulo
FROM (
SELECT od.ID, od.pid, od.Titulo
FROM #temp T
INNER JOIN FRDBase frd ON frd.IDNivel = T.ID AND frd.IDTipoFRDBase = @IDTipoFRDBase AND frd.isDeleted = @isDeleted
INNER JOIN SFRDImagem img ON img.IDFRDBase = frd.ID AND img.Tipo = @imgTipo AND img.isDeleted = @isDeleted
INNER JOIN SFRDImagemObjetoDigital imgOD ON imgOD.IDFRDBase = img.IDFRDBase AND imgOD.idx = img.idx AND imgOD.isDeleted = @isDeleted
INNER JOIN ObjetoDigital od ON od.ID = imgOD.IDObjetoDigital AND od.isDeleted = @isDeleted
UNION ALL
SELECT odSimples.ID, odSimples.pid, odSimples.Titulo
FROM #temp T
INNER JOIN FRDBase frd ON frd.IDNivel = T.ID AND frd.IDTipoFRDBase = @IDTipoFRDBase AND frd.isDeleted = @isDeleted
INNER JOIN SFRDImagem img ON img.IDFRDBase = frd.ID AND img.Tipo = @imgTipo AND img.isDeleted = @isDeleted
INNER JOIN SFRDImagemObjetoDigital imgOD ON imgOD.IDFRDBase = img.IDFRDBase AND imgOD.idx = img.idx AND imgOD.isDeleted = @isDeleted
INNER JOIN ObjetoDigital od ON od.ID = imgOD.IDObjetoDigital AND od.isDeleted = @isDeleted
INNER JOIN ObjetoDigitalRelacaoHierarquica odrh ON odrh.IDUpper = od.ID AND odrh.isDeleted = @isDeleted
INNER JOIN ObjetoDigital odSimples ON odSimples.ID = odrh.ID AND odSimples.isDeleted = @isDeleted
) ods";
command.Parameters.AddWithValue("@IDTipoFRDBase", 1);
command.Parameters.AddWithValue("@imgTipo", "Fedora");
command.ExecuteNonQuery();
PermissoesRule.Current.GetODEffectivePermissions(" FROM #odsTemp ", IDLoginTrustee, conn);
command.CommandText = "DELETE FROM #odsTemp WHERE ID IN (SELECT DISTINCT ID FROM #effective WHERE IsGrant = @IsGrant OR IsGrant IS NULL)";
command.Parameters.AddWithValue("@IsGrant", 0);
command.ExecuteNonQuery();
PermissoesRule.Current.DropEffectivePermissionsTempTable(conn);
command.CommandText = "SELECT * FROM #odsTemp";
var reader = command.ExecuteReader();
var od = new PermissoesRule.ObjDig();
while (reader.Read())
{
od = new PermissoesRule.ObjDig();
od.ID = reader.GetInt64(0);
od.pid = reader.GetString(1);
od.titulo = reader.GetString(2);
res.Add(od);
}
reader.Close();
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["ObjetoDigital"],
"INNER JOIN #odsTemp ON #odsTemp.ID = ObjetoDigital.ID ");
da.Fill(CurrentDataSet, "ObjetoDigital");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TrusteeUser"]);
da.Fill(CurrentDataSet, "TrusteeUser");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TrusteeGroup"]);
da.Fill(CurrentDataSet, "TrusteeGroup");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["UserGroups"]);
da.Fill(CurrentDataSet, "UserGroups");
// carregar permissões
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["TrusteeObjetoDigitalPrivilege"],
"INNER JOIN #odsTemp ON #odsTemp.ID = TrusteeObjetoDigitalPrivilege.IDObjetoDigital " +
"WHERE TrusteeObjetoDigitalPrivilege.IDTrustee = " + IDTrustee);
da.Fill(CurrentDataSet, "TrusteeObjetoDigitalPrivilege");
command.CommandText = "DROP TABLE #temp; DROP TABLE #odsTemp; ";
command.ExecuteNonQuery();
}
return res;
}
public override ArrayList GetODItems(DataSet currentDataSet, int pageNr, int itemsPerPage, long IDTrustee, IDbConnection conn)
{
var res = new ArrayList();
using (SqlCommand command = new SqlCommand("", (SqlConnection)conn))
{
command.CommandText = "CREATE TABLE #ItemsID (ID BIGINT, pid NVARCHAR(20), Titulo NVARCHAR(768))";
command.ExecuteNonQuery();
command.CommandText =
"INSERT INTO #ItemsID " +
"SELECT od.ID, od.pid, od.Titulo " +
"FROM #OrderedItems " +
"INNER JOIN ObjetoDigital od ON od.ID = #OrderedItems.ID AND od.isDeleted = @isDeleted " +
"WHERE seq_id >= @seq_id1 AND seq_id <= @seq_id2 ORDER BY seq_id";
command.Parameters.AddWithValue("@isDeleted", 0);
command.Parameters.AddWithValue("@seq_id1", (pageNr - 1) * itemsPerPage + 1);
command.Parameters.AddWithValue("@seq_id2", pageNr * itemsPerPage);
command.ExecuteNonQuery();
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["ObjetoDigital"],
"INNER JOIN #ItemsID ON #ItemsID.ID = ObjetoDigital.ID ");
da.Fill(currentDataSet, "ObjetoDigital");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["TrusteeUser"]);
da.Fill(currentDataSet, "TrusteeUser");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["TrusteeGroup"]);
da.Fill(currentDataSet, "TrusteeGroup");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["UserGroups"]);
da.Fill(currentDataSet, "UserGroups");
// carregar permissões
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["TrusteeObjetoDigitalPrivilege"],
"INNER JOIN #ItemsID ON #ItemsID.ID = TrusteeObjetoDigitalPrivilege.IDObjetoDigital " +
"WHERE TrusteeObjetoDigitalPrivilege.IDTrustee = " + IDTrustee);
da.Fill(currentDataSet, "TrusteeObjetoDigitalPrivilege");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["TrusteeObjetoDigitalPrivilege"],
"INNER JOIN #ItemsID ON #ItemsID.ID = TrusteeObjetoDigitalPrivilege.IDObjetoDigital " +
"WHERE TrusteeObjetoDigitalPrivilege.IDTrustee IN (SELECT IDGroup FROM UserGroups WHERE IDUser = "******")");
da.Fill(currentDataSet, "TrusteeObjetoDigitalPrivilege");
}
PermissoesRule.Current.GetODEffectivePermissions(" FROM #ItemsID ", IDTrustee, conn);
command.CommandText = "SELECT I.ID, I.pid, I.Titulo, E.IDTipoOperation, E.IsGrant FROM #ItemsID I INNER JOIN #effective E ON E.ID = I.ID";
var reader = command.ExecuteReader();
var od = new PermissoesRule.ObjDig();
var readed_rows = new Dictionary<long, PermissoesRule.ObjDig>();
long ID = 0;
while (reader.Read())
{
ID = reader.GetInt64(0);
if (readed_rows.ContainsKey(ID))
{
od = readed_rows[ID];
if (!reader.IsDBNull(4)) od.Permissoes.Add(reader.GetInt64(3), reader.GetByte(4));
}
else
{
od = new PermissoesRule.ObjDig();
od.ID = reader.GetInt64(0);
od.pid = reader.GetString(1);
od.titulo = reader.GetString(2);
od.Permissoes = new Dictionary<long, byte>();
if (!reader.IsDBNull(4)) od.Permissoes.Add(reader.GetInt64(3), reader.GetByte(4));
readed_rows.Add(ID, od);
res.Add(od);
}
}
reader.Close();
PermissoesRule.Current.DropEffectivePermissionsTempTable(conn);
}
return res;
}