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