public override void GetPidsPorNvl(List <string> IDNiveis, IDbConnection conn, out List <string> pids)
{
GisaDataSetHelperRule.ImportIDs(IDNiveis.Select(id => System.Convert.ToInt64(id)).ToArray(), conn);
pids = new List <string>();
var cmd = @"
SELECT od.pid
FROM (
SELECT img.IDFRDBase
FROM #temp
INNER JOIN FRDBase frd ON frd.IDNivel = #temp.ID AND frd.isDeleted = 0
INNER JOIN SFRDImagem img ON img.IDFRDBase = frd.ID AND img.Tipo = 'Fedora' AND img.isDeleted = 0
GROUP BY img.IDFRDBase
HAVING COUNT(img.IDFRDBase) = 1
) imgs
INNER JOIN SFRDImagem img ON img.IDFRDBase = imgs.IDFRDBase AND img.Tipo = 'Fedora' AND img.isDeleted = 0
INNER JOIN SFRDImagemObjetoDigital imgOD ON imgOD.IDFRDBase = img.IDFRDBase AND imgOD.idx = img.idx AND imgOD.isDeleted = 0
INNER JOIN ObjetoDigital od ON od.ID = imgOD.IDObjetoDigital AND od.isDeleted = 0" ;
var command = new SqlCommand(cmd, (SqlConnection)conn);
command.CommandText = cmd;
var reader = command.ExecuteReader();
while (reader.Read())
{
pids.Add(reader.GetValue(0).ToString());
}
reader.Close();
}
public override Dictionary <string, string> GetGisaIDs(List <string> pids, IDbConnection conn)
{
var gisa_ids = new Dictionary <string, string>();
GisaDataSetHelperRule.ImportDesignacoes(pids.Distinct().ToArray(), conn);
var command = new SqlCommand(string.Empty, (SqlConnection)conn);
command.CommandType = CommandType.Text;
command.CommandTimeout = 500;
command.CommandText = @"
create table #ODTemp (ID bigint)
insert into #ODTemp
select od.ID
from ObjetoDigital od
inner join #temp on #temp.Designacao COLLATE LATIN1_GENERAL_CS_AS = od.pid COLLATE LATIN1_GENERAL_CS_AS
where od.isDeleted = 0";
command.ExecuteNonQuery();
command.CommandText = @"
select od.pid, frd.IDNivel
from ObjetoDigital od
inner join #ODTemp T on T.ID = od.ID
inner join SFRDImagemObjetoDigital imgOD on imgOD.IDObjetoDigital = od.ID and imgOD.isDeleted = 0
inner join SFRDImagem img on img.IDFRDBase = imgOD.IDFRDBase and img.isDeleted = 0
inner join FRDBase frd on frd.ID = img.IDFRDBase and frd.isDeleted = 0
where od.isDeleted = 0
select od.pid, frd.IDNivel
from ObjetoDigital od
inner join #ODTemp T on T.ID = od.ID
inner join ObjetoDigitalRelacaoHierarquica odrh on odrh.ID = od.ID and odrh.isDeleted = 0
inner join ObjetoDigital odComp on odComp.ID = odrh.IDUpper and odComp.isDeleted = 0
inner join SFRDImagemObjetoDigital imgOD on imgOD.IDObjetoDigital = odComp.ID and imgOD.isDeleted = 0
inner join SFRDImagem img on img.IDFRDBase = imgOD.IDFRDBase and img.isDeleted = 0
inner join FRDBase frd on frd.ID = img.IDFRDBase and frd.isDeleted = 0
where od.isDeleted = 0
";
var reader = command.ExecuteReader();
while (reader.Read())
{
gisa_ids[reader.GetString(0)] = reader.GetInt64(1).ToString();
}
reader.NextResult();
while (reader.Read())
{
gisa_ids[reader.GetString(0)] = reader.GetInt64(1).ToString();
}
reader.Close();
return(gisa_ids);
}
public override void LoadControloAuts(DataSet currentDataSet, string[] designacoes, IDbConnection conn)
{
GisaDataSetHelperRule.ImportDesignacoes(designacoes, conn);
using (SqlCommand command = new SqlCommand(string.Empty, (SqlConnection)conn))
{
command.CommandText = "CREATE TABLE #aux (ID BIGINT, Termo NVARCHAR(768));";
command.ExecuteNonQuery();
command.Parameters.AddWithValue("@isDeleted", 0);
command.CommandText =
"INSERT INTO #aux " +
"SELECT ID, Termo " +
"FROM Dicionario " +
"INNER JOIN #temp ON #temp.Designacao COLLATE LATIN1_GENERAL_CS_AS LIKE Termo COLLATE LATIN1_GENERAL_CS_AS " +
"WHERE isDeleted = @isDeleted";
command.ExecuteNonQuery();
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Dicionario"],
"INNER JOIN #aux ON #aux.ID = Dicionario.ID ");
da.Fill(currentDataSet, "Dicionario");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["ControloAut"],
"INNER JOIN ControloAutDicionario cad ON cad.IDControloAut = ControloAut.ID AND cad.isDeleted = @isDeleted " +
"INNER JOIN Dicionario d ON d.ID = cad.IDDicionario AND d.isDeleted = @isDeleted " +
"INNER JOIN #aux ON #aux.ID = d.ID ");
da.Fill(currentDataSet, "ControloAut");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["ControloAutDicionario"],
"INNER JOIN Dicionario d ON d.ID = ControloAutDicionario.IDDicionario AND d.isDeleted = @isDeleted " +
"INNER JOIN #aux ON #aux.ID = d.ID ");
da.Fill(currentDataSet, "ControloAutDicionario");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
"INNER JOIN NivelControloAut nca ON nca.ID = Nivel.ID AND nca.isDeleted = @isDeleted " +
"INNER JOIN ControloAut ca ON ca.ID = nca.IDControloAut AND ca.isDeleted = @isDeleted " +
"INNER JOIN ControloAutDicionario cad ON cad.IDControloAut = ca.ID AND cad.isDeleted = @isDeleted " +
"INNER JOIN Dicionario d ON d.ID = cad.IDDicionario AND d.isDeleted = @isDeleted " +
"INNER JOIN #aux ON #aux.ID = d.ID ");
da.Fill(currentDataSet, "Nivel");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["NivelControloAut"],
"INNER JOIN ControloAut ca ON ca.ID = NivelControloAut.IDControloAut AND ca.isDeleted = @isDeleted " +
"INNER JOIN ControloAutDicionario cad ON cad.IDControloAut = ca.ID AND cad.isDeleted = @isDeleted " +
"INNER JOIN Dicionario d ON d.ID = cad.IDDicionario AND d.isDeleted = @isDeleted " +
"INNER JOIN #aux ON #aux.ID = d.ID ");
da.Fill(currentDataSet, "NivelControloAut");
}
command.CommandText = "DROP TABLE #aux";
command.ExecuteNonQuery();
}
}
public override void LoadUnidadesFisicas(DataSet currentDataSet, string[] codigos, IDbConnection conn)
{
GisaDataSetHelperRule.ImportDesignacoes(codigos, conn);
using (SqlCommand command = new SqlCommand(string.Empty, (SqlConnection)conn))
{
command.CommandText = "CREATE TABLE #aux (ID BIGINT, Termo NVARCHAR(50))";
command.ExecuteNonQuery();
command.Parameters.AddWithValue("@isDeleted", 0);
command.CommandText = @"
INSERT INTO #aux
SELECT n.ID, n.Codigo
FROM Nivel n
INNER JOIN #temp ON #temp.Designacao COLLATE LATIN1_GENERAL_CS_AS LIKE n.Codigo COLLATE LATIN1_GENERAL_CS_AS
WHERE n.isDeleted = @isDeleted";
command.ExecuteNonQuery();
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
"INNER JOIN #aux ON #aux.ID = Nivel.ID ");
da.Fill(currentDataSet, "Nivel");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["NivelDesignado"],
"INNER JOIN #aux ON #aux.ID = NivelDesignado.ID ");
da.Fill(currentDataSet, "NivelDesignado");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["FRDBase"],
"INNER JOIN #aux ON #aux.ID = FRDBase.IDNivel ");
da.Fill(currentDataSet, "FRDBase");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["RelacaoHierarquica"],
"INNER JOIN #aux ON #aux.ID = RelacaoHierarquica.ID ");
da.Fill(currentDataSet, "RelacaoHierarquica");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
"INNER JOIN RelacaoHierarquica ON RelacaoHierarquica.IDUpper = Nivel.ID " +
"INNER JOIN #aux ON #aux.ID = RelacaoHierarquica.ID ");
da.Fill(currentDataSet, "Nivel");
}
command.CommandText = "DROP TABLE #aux";
command.ExecuteNonQuery();
}
}
public override void LoadDocumentos(DataSet currentDataSet, string[] designacoes, IDbConnection conn)
{
GisaDataSetHelperRule.ImportDesignacoes(designacoes, conn);
using (SqlCommand command = new SqlCommand(string.Empty, (SqlConnection)conn))
{
command.CommandText = "CREATE TABLE #aux (ID BIGINT, Termo NVARCHAR(768))";
command.ExecuteNonQuery();
command.Parameters.AddWithValue("@isDeleted", 0);
command.CommandText = @"
INSERT INTO #aux
SELECT n.ID, nd.Designacao
FROM Nivel n
INNER JOIN #temp ON #temp.Designacao = n.ID
INNER JOIN NivelDesignado nd ON nd.ID = n.ID AND nd.isDeleted = @isDeleted
WHERE n.isDeleted = @isDeleted";
command.ExecuteNonQuery();
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
"INNER JOIN #aux ON #aux.ID = Nivel.ID ");
da.Fill(currentDataSet, "Nivel");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["NivelDesignado"],
"INNER JOIN #aux ON #aux.ID = NivelDesignado.ID ");
da.Fill(currentDataSet, "NivelDesignado");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["RelacaoHierarquica"],
"INNER JOIN #aux ON #aux.ID = RelacaoHierarquica.ID ");
da.Fill(currentDataSet, "RelacaoHierarquica");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
"INNER JOIN RelacaoHierarquica ON RelacaoHierarquica.IDUpper = Nivel.ID " +
"INNER JOIN #aux ON #aux.ID = RelacaoHierarquica.ID ");
da.Fill(currentDataSet, "Nivel");
}
command.CommandText = "DROP TABLE #aux";
command.ExecuteNonQuery();
}
}
public override decimal LoadDescricaoFisicaAndGetSomatorioLargura(DataSet currentDataSet, long[] ufIDs, IDbConnection conn)
{
GisaDataSetHelperRule.ImportIDs(ufIDs, conn);
decimal total = 0;
var cmd = @"
SELECT COUNT(df.MedidaLargura)
FROM FRDBase frd
INNER JOIN #temp ON #temp.ID = frd.IDNivel
LEFT JOIN SFRDUFDescricaoFisica df ON df.IDFRDBase = frd.ID AND df.isDeleted = @isDeleted
WHERE frd.isDeleted = @isDeleted";
using (var command = new SqlCommand(cmd, (SqlConnection)conn))
using (var da = new SqlDataAdapter(command))
{
command.Parameters.AddWithValue("@isDeleted", 0);
total = System.Convert.ToDecimal(command.ExecuteScalar());
// carregar informação dos niveis documentais associados às ufs
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
"INNER JOIN FRDBase ON FRDBase.IDNivel = Nivel.ID " +
"INNER JOIN SFRDUnidadeFisica ON SFRDUnidadeFisica.IDFRDBase = FRDBase.ID " +
"INNER JOIN #temp ON #temp.ID = SFRDUnidadeFisica.IDNivel");
da.Fill(currentDataSet, "Nivel");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["FRDBase"],
"INNER JOIN SFRDUnidadeFisica ON SFRDUnidadeFisica.IDFRDBase = FRDBase.ID " +
"INNER JOIN #temp ON #temp.ID = SFRDUnidadeFisica.IDNivel");
da.Fill(currentDataSet, "FRDBase");
// carregar informação das ufs
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
"INNER JOIN #temp ON #temp.ID = Nivel.ID");
da.Fill(currentDataSet, "Nivel");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["SFRDUnidadeFisica"],
"INNER JOIN #temp ON #temp.ID = SFRDUnidadeFisica.IDNivel");
da.Fill(currentDataSet, "SFRDUnidadeFisica");
}
return(total);
}
public override Dictionary <long, Dictionary <string, byte> > CalculateEffectivePermissions(List <long> IDNiveis, long IDTrustee, IDbConnection conn)
{
GisaDataSetHelperRule.ImportIDs(IDNiveis.ToArray(), conn);
var query = "FROM #temp";
PermissoesRule.Current.GetEffectivePermissions(query, IDTrustee, conn);
SqlCommand command = new SqlCommand(string.Empty, (SqlConnection)conn);
command.CommandText = "SELECT * FROM #effective";
var reader = command.ExecuteReader();
var nivelPermissoes = GetEffectivePermissions(reader);
PermissoesRule.Current.DropEffectivePermissionsTempTable(conn);
return(nivelPermissoes);
}
public override void LoadDataCIPermissoes(DataSet CurrentDataSet, List <long> lstIDNivel, IDbConnection conn)
{
GisaDataSetHelperRule.ImportIDs(lstIDNivel.ToArray(), conn);
using (SqlCommand command = SqlSyntax.CreateSelectCommandWithNoDeletedRowsParam((SqlConnection)conn))
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(CurrentDataSet.Tables["Trustee"]);
da.Fill(CurrentDataSet, "Trustee");
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["TrusteeNivelPrivilege"],
"INNER JOIN #temp T ON T.ID = TrusteeNivelPrivilege.IDNivel ");
da.Fill(CurrentDataSet, "TrusteeNivelPrivilege");
}
}