public override Dictionary<string, DocGisaInfo> LoadDocsCorrespondenciasNovas(DataSet currentDataSet, List<string> idsExternos, long IDTipoNivelRelacionado, IDbConnection conn)
{
DataTable t = new DataTable();
DataColumn c1 = new DataColumn("IDExterno", typeof(string));
DataColumn c2 = new DataColumn("IDSistema", typeof(int));
c2.AllowDBNull = true;
t.Columns.Add(c1);
t.Columns.Add(c2);
//System.Diagnostics.Trace.WriteLine("LoadDocsCorrespondenciasNovas");
foreach (string idexterno in idsExternos)
{
DataRow dr = t.NewRow();
dr[0] = idexterno;
dr[1] = DBNull.Value;
t.Rows.Add(dr);
//System.Diagnostics.Trace.WriteLine("INSERT INTO #temp VALUES (" + idexterno + ")");
}
Dictionary<string, DocGisaInfo> result = new Dictionary<string, DocGisaInfo>();
using (SqlCommand command = new SqlCommand(string.Empty, (SqlConnection)conn))
{
command.CommandText = "CREATE TABLE #temp(IDExterno NVARCHAR(200) COLLATE Latin1_General_CS_AS NOT NULL); CREATE TABLE #NiveisSugest(IDExterno NVARCHAR(200), IDNivel BIGINT);";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO #temp SELECT IDExterno FROM @Integ_DocExterno";
SqlParameter paramIds = command.Parameters.AddWithValue("@Integ_DocExterno", t);
paramIds.SqlDbType = SqlDbType.Structured;
paramIds.TypeName = "Integ_DocExterno";
command.ExecuteNonQuery();
command.Parameters.Clear();
command.CommandText = @"
INSERT INTO #NiveisSugest
SELECT #temp.IDExterno, n.ID
FROM Nivel n
INNER JOIN #temp ON #temp.IDExterno COLLATE Latin1_General_CS_AS = n.Codigo COLLATE Latin1_General_CS_AS
INNER JOIN RelacaoHierarquica rh ON rh.ID = n.ID AND rh.IDTipoNivelRelacionado = @IDTipoNivelRelacionado
WHERE n.isDeleted = @isDeleted";
command.Parameters.AddWithValue("@IDTipoNivelRelacionado", IDTipoNivelRelacionado);
command.Parameters.AddWithValue("@isDeleted", 0);
command.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter(command);
{
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
"INNER JOIN #NiveisSugest ON #NiveisSugest.IDNivel = Nivel.ID");
da.Fill(currentDataSet, "Nivel");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["NivelDesignado"],
"INNER JOIN #NiveisSugest ON #NiveisSugest.IDNivel = NivelDesignado.ID");
da.Fill(currentDataSet, "NivelDesignado");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
"INNER JOIN RelacaoHierarquica ON RelacaoHierarquica.IDUpper = Nivel.ID " +
"INNER JOIN #NiveisSugest ON #NiveisSugest.IDNivel = RelacaoHierarquica.ID ");
da.Fill(currentDataSet, "Nivel");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["NivelDesignado"],
"INNER JOIN RelacaoHierarquica ON RelacaoHierarquica.IDUpper = NivelDesignado.ID " +
"INNER JOIN #NiveisSugest ON #NiveisSugest.IDNivel = RelacaoHierarquica.ID ");
da.Fill(currentDataSet, "NivelDesignado");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["RelacaoHierarquica"],
"INNER JOIN #NiveisSugest ON #NiveisSugest.IDNivel = RelacaoHierarquica.ID");
da.Fill(currentDataSet, "RelacaoHierarquica");
StringBuilder innerQuery = new StringBuilder("INNER JOIN #NiveisSugest ON #NiveisSugest.IDNivel = FRDBase.IDNivel ");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["FRDBase"],
innerQuery.ToString());
da.Fill(currentDataSet, "FRDBase");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["SFRDDatasProducao"],
"INNER JOIN FRDBase ON FRDBase.ID = SFRDDatasProducao.IDFRDBase " +
innerQuery.ToString());
da.Fill(currentDataSet, "SFRDDatasProducao");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Codigo"],
"INNER JOIN FRDBase ON FRDBase.ID = Codigo.IDFRDBase " +
innerQuery.ToString());
da.Fill(currentDataSet, "Codigo");
// carregar FRDBase do processo do documento (se for caso disso)
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["FRDBase"],
"INNER JOIN RelacaoHierarquica ON RelacaoHierarquica.IDUpper = FRDBase.IDNivel " +
"INNER JOIN #NiveisSugest ON #NiveisSugest.IDNivel = RelacaoHierarquica.ID ");
da.Fill(currentDataSet, "FRDBase");
// carregar informação referente aos registos de autoridade associados
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["IndexFRDCA"],
innerQuery.Insert(0, "INNER JOIN FRDBase ON FRDBase.ID = IndexFRDCA.IDFRDBase ").ToString());
da.Fill(currentDataSet, "IndexFRDCA");
var temp = innerQuery.ToString();
innerQuery = new StringBuilder(string.Format("INNER JOIN (SELECT DISTINCT IndexFRDCA.IDControloAut FROM IndexFRDCA {0}) IndexFRDCA ON IndexFRDCA.IDControloAut = ControloAut.ID ", temp));
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["ControloAut"],
innerQuery.ToString());
da.Fill(currentDataSet, "ControloAut");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["ControloAutDicionario"],
innerQuery.Insert(0, "INNER JOIN ControloAut ON ControloAut.ID = ControloAutDicionario.IDControloAut AND ControloAutDicionario.IDTipoControloAutForma = 1 ").ToString());
da.Fill(currentDataSet, "ControloAutDicionario");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Dicionario"],
innerQuery.Insert(0, "INNER JOIN ControloAutDicionario ON ControloAutDicionario.IDDicionario = Dicionario.ID ").ToString());
da.Fill(currentDataSet, "Dicionario");
// carregar informação referente aos produtores
command.Parameters.Clear();
command.CommandText = "CREATE TABLE #NvlIDs (IDNivelDoc BIGINT, IDNivelProdutor BIGINT)";
command.ExecuteNonQuery();
command.CommandText = @"
WITH Temp (IDStart, ID, IDUpper, IDTipoNivelRelacionado)
AS
(
SELECT rh.ID, rh.ID, rh.IDUpper, rh.IDTipoNivelRelacionado
FROM RelacaoHierarquica rh
INNER JOIN #NiveisSugest ON #NiveisSugest.IDNivel = rh.ID
WHERE rh.isDeleted = @isDeleted
UNION ALL
SELECT Temp.IDStart, RelacaoHierarquica.ID, RelacaoHierarquica.IDUpper, RelacaoHierarquica.IDTipoNivelRelacionado
FROM RelacaoHierarquica
INNER JOIN Temp ON Temp.IDUpper = RelacaoHierarquica.ID
WHERE RelacaoHierarquica.IDTipoNivelRelacionado > @IDTipoNivelRelacionado6
)
INSERT INTO #NvlIDs
SELECT IDStart, IDUpper
FROM Temp
WHERE IDTipoNivelRelacionado > @IDTipoNivelRelacionado6";
command.Parameters.AddWithValue("@IDTipoNivelRelacionado6", 6);
command.Parameters.AddWithValue("@isDeleted", 0);
command.ExecuteNonQuery();
innerQuery = new StringBuilder("INNER JOIN (SELECT DISTINCT IDNivelProdutor FROM #NvlIDs) NvlIDs ON NvlIDs.IDNivelProdutor = Nivel.ID ");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
innerQuery.ToString());
da.Fill(currentDataSet, "Nivel");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
"INNER JOIN (" +
"SELECT DISTINCT IDUpper " +
"FROM RelacaoHierarquica rh " +
"INNER JOIN Nivel ON Nivel.ID = rh.ID " +
innerQuery.ToString() +
")ids ON ids.IDUpper = Nivel.ID");
da.Fill(currentDataSet, "Nivel");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["RelacaoHierarquica"],
"INNER JOIN Nivel ON Nivel.ID = RelacaoHierarquica.ID " +
innerQuery.ToString());
da.Fill(currentDataSet, "RelacaoHierarquica");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["NivelControloAut"],
innerQuery.Insert(0, "INNER JOIN Nivel ON Nivel.ID = NivelControloAut.ID ").ToString());
da.Fill(currentDataSet, "NivelControloAut");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["ControloAut"],
innerQuery.Insert(0, "INNER JOIN NivelControloAut ON NivelControloAut.IDControloAut = ControloAut.ID ").ToString());
da.Fill(currentDataSet, "ControloAut");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["ControloAutDicionario"],
innerQuery.Insert(0, "INNER JOIN ControloAut ON ControloAut.ID = ControloAutDicionario.IDControloAut AND ControloAutDicionario.IDTipoControloAutForma = 1 ").ToString());
da.Fill(currentDataSet, "ControloAutDicionario");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Dicionario"],
innerQuery.Insert(0, "INNER JOIN ControloAutDicionario ON ControloAutDicionario.IDDicionario = Dicionario.ID ").ToString());
da.Fill(currentDataSet, "Dicionario");
}
var res = new Dictionary<string, long>();
command.CommandText = @"
SELECT #NiveisSugest.IDExterno, #NvlIDs.IDNivelDoc, #NvlIDs.IDNivelProdutor
FROM #NiveisSugest
INNER JOIN #NvlIDs ON #NvlIDs.IDNivelDoc = #NiveisSugest.IDNivel
ORDER BY #NvlIDs.IDNivelDoc";
SqlDataReader reader = command.ExecuteReader();
long currentID = -1;
long id = 0;
DocGisaInfo dgInfo = new DocGisaInfo();
while (reader.Read())
{
id = reader.GetInt64(1);
if (currentID != id)
{
currentID = id;
dgInfo = new DocGisaInfo();
dgInfo.IDNivelProdutores = new List<long>();
dgInfo.IDNivel = currentID;
result[reader.GetString(0)] = dgInfo;
}
dgInfo.IDNivelProdutores.Add(reader.GetInt64(2));
}
reader.Close();
command.CommandText = "DROP TABLE #temp; DROP TABLE #NvlIDs; DROP TABLE #NiveisSugest";
command.ExecuteNonQuery();
}
return result;
}
public override List<DocGisaInfo> LoadDocsCorrespondenciasAnteriores(DataSet currentDataSet, List<EntidadeExterna> docsExternos, int IDTipoEntidade, IDbConnection conn)
{
DataTable t = new DataTable();
DataColumn c1 = new DataColumn("IDExterno", typeof(string));
DataColumn c2= new DataColumn("IDSistema", typeof(int));
t.Columns.Add(c1);
t.Columns.Add(c2);
//var a = new StringBuilder();
foreach (var doc in docsExternos)
{
DataRow dr = t.NewRow();
dr[0] = doc.IDExterno;
dr[1] = doc.Sistema;
t.Rows.Add(dr);
//a.AppendLine("INSERT INTO #temp VALUES ('" + doc.IDExterno + "', " + doc.Sistema + ");");
}
List<DocGisaInfo> result = new List<DocGisaInfo>();
using (SqlCommand command = new SqlCommand(string.Empty, (SqlConnection)conn))
{
command.CommandText =
"CREATE TABLE #Relacoes(IDEntidadeExterna BIGINT, IDNivel BIGINT);" +
"CREATE TABLE #temp(IDExterno NVARCHAR(200) COLLATE Latin1_General_CS_AS NOT NULL, IDSistema INT);";
command.ExecuteNonQuery();
command.CommandText = string.Format(@"
SELECT * INTO #temp FROM @Integ_DocExterno;
INSERT INTO #Relacoes
SELECT re.IDEntidadeExterna, re.IDNivel
FROM #temp
INNER JOIN Integ_EntidadeExterna ee ON ee.IDExterno COLLATE Latin1_General_CS_AS = #temp.IDExterno COLLATE Latin1_General_CS_AS
AND ee.IDSistema = #temp.IDSistema
AND ee.IDTipoEntidade = {0}
INNER JOIN Integ_RelacaoExternaNivel re ON re.IDEntidadeExterna = ee.ID
GROUP BY re.IDEntidadeExterna, re.IDNivel", IDTipoEntidade);
SqlParameter paramIds = command.Parameters.AddWithValue("@Integ_DocExterno", t);
paramIds.SqlDbType = SqlDbType.Structured;
paramIds.TypeName = "Integ_DocExterno";
command.ExecuteNonQuery();
command.Parameters.Clear();
string innerJoinEntExt =
"INNER JOIN (SELECT DISTINCT IDEntidadeExterna FROM #Relacoes) ee ON ee.IDEntidadeExterna = Integ_EntidadeExterna.ID ";
string innerJoinNivelDoc =
"INNER JOIN (SELECT DISTINCT IDNivel FROM #Relacoes) ee ON ee.IDNivel = {0}.{1} ";
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
command.Parameters.AddWithValue("@isDeleted", 0);
command.CommandType = CommandType.Text;
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Integ_EntidadeExterna"],
innerJoinEntExt);
da.Fill(currentDataSet, "Integ_EntidadeExterna");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
string.Format(innerJoinNivelDoc, "Nivel", "ID"));
da.Fill(currentDataSet, "Nivel");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Integ_RelacaoExternaNivel"],
"INNER JOIN #Relacoes ON #Relacoes.IDEntidadeExterna = Integ_RelacaoExternaNivel.IDEntidadeExterna AND #Relacoes.IDNivel = Integ_RelacaoExternaNivel.IDNivel ");
da.Fill(currentDataSet, "Integ_RelacaoExternaNivel");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["NivelDesignado"],
string.Format(innerJoinNivelDoc, "NivelDesignado", "ID"));
da.Fill(currentDataSet, "NivelDesignado");
if (IDTipoEntidade >= 6)
{
// nivel upper do nivel documental
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
"INNER JOIN RelacaoHierarquica ON RelacaoHierarquica.IDUpper = Nivel.ID " +
string.Format(innerJoinNivelDoc, "RelacaoHierarquica", "ID"));
da.Fill(currentDataSet, "Nivel");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["NivelDesignado"],
"INNER JOIN RelacaoHierarquica ON RelacaoHierarquica.IDUpper = NivelDesignado.ID " +
string.Format(innerJoinNivelDoc, "RelacaoHierarquica", "ID"));
da.Fill(currentDataSet, "NivelDesignado");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["RelacaoHierarquica"],
string.Format(innerJoinNivelDoc, "RelacaoHierarquica", "ID"));
da.Fill(currentDataSet, "RelacaoHierarquica");
// nuvem FRDBase do nivel documental
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["FRDBase"],
string.Format(innerJoinNivelDoc, "FRDBase", "IDNivel"));
da.Fill(currentDataSet, "FRDBase");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["SFRDDatasProducao"],
"INNER JOIN FRDBase ON FRDBase.ID = SFRDDatasProducao.IDFRDBase " +
string.Format(innerJoinNivelDoc, "FRDBase", "IDNivel"));
da.Fill(currentDataSet, "SFRDDatasProducao");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["SFRDConteudoEEstrutura"],
"INNER JOIN FRDBase ON FRDBase.ID = SFRDConteudoEEstrutura.IDFRDBase " +
string.Format(innerJoinNivelDoc, "FRDBase", "IDNivel"));
da.Fill(currentDataSet, "SFRDConteudoEEstrutura");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["SFRDCondicaoDeAcesso"],
"INNER JOIN FRDBase ON FRDBase.ID = SFRDCondicaoDeAcesso.IDFRDBase " +
string.Format(innerJoinNivelDoc, "FRDBase", "IDNivel"));
da.Fill(currentDataSet, "SFRDCondicaoDeAcesso");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Codigo"],
"INNER JOIN FRDBase ON FRDBase.ID = Codigo.IDFRDBase " +
string.Format(innerJoinNivelDoc, "FRDBase", "IDNivel"));
da.Fill(currentDataSet, "Codigo");
// carregar informação referente aos controlos de autoridade associados
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["ControloAut"],
@"INNER JOIN (
SELECT DISTINCT IndexFRDCA.IDControloAut
FROM IndexFRDCA
INNER JOIN FRDBase ON FRDBase.ID = IndexFRDCA.IDFRDBase " +
string.Format(innerJoinNivelDoc, "FRDBase", "IDNivel") +
") IndexFRDCA ON IndexFRDCA.IDControloAut = ControloAut.ID ");
da.Fill(currentDataSet, "ControloAut");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["IndexFRDCA"],
"INNER JOIN FRDBase ON FRDBase.ID = IndexFRDCA.IDFRDBase " +
string.Format(innerJoinNivelDoc, "FRDBase", "IDNivel"));
da.Fill(currentDataSet, "IndexFRDCA");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Dicionario"],
"INNER JOIN (" +
"SELECT DISTINCT cad.IDDicionario " +
"FROM ControloAutDicionario cad " +
"INNER JOIN ControloAut ON ControloAut.ID = cad.IDControloAut " +
@"INNER JOIN (
SELECT DISTINCT IndexFRDCA.IDControloAut
FROM IndexFRDCA
INNER JOIN FRDBase ON FRDBase.ID = IndexFRDCA.IDFRDBase " +
string.Format(innerJoinNivelDoc, "FRDBase", "IDNivel") +
") IndexFRDCA ON IndexFRDCA.IDControloAut = ControloAut.ID " +
")cads ON cads.IDDicionario = Dicionario.ID");
da.Fill(currentDataSet, "Dicionario");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["ControloAutDicionario"],
"INNER JOIN ControloAut ON ControloAut.ID = ControloAutDicionario.IDControloAut " +
@"INNER JOIN (
SELECT DISTINCT IndexFRDCA.IDControloAut
FROM IndexFRDCA
INNER JOIN FRDBase ON FRDBase.ID = IndexFRDCA.IDFRDBase " +
string.Format(innerJoinNivelDoc, "FRDBase", "IDNivel") +
") IndexFRDCA ON IndexFRDCA.IDControloAut = ControloAut.ID ");
da.Fill(currentDataSet, "ControloAutDicionario");
}
// carregar informação referente aos produtores
command.Parameters.Clear();
command.CommandText = "CREATE TABLE #NvlIDs (IDNivelDoc BIGINT, IDNivelProdutor BIGINT); " +
"CREATE TABLE #Prods (IDNivelProdutor BIGINT); ";
command.ExecuteNonQuery();
string query = @"
WITH Temp (IDStart, ID, IDUpper, IDTipoNivelRelacionado)
AS
(
SELECT rh.ID, rh.ID, rh.IDUpper, rh.IDTipoNivelRelacionado
FROM RelacaoHierarquica rh " +
string.Format(innerJoinNivelDoc, "rh", "ID") +
@"WHERE rh.isDeleted = @isDeleted
UNION ALL
SELECT Temp.IDStart, RelacaoHierarquica.ID, RelacaoHierarquica.IDUpper, RelacaoHierarquica.IDTipoNivelRelacionado
FROM RelacaoHierarquica
INNER JOIN Temp ON Temp.IDUpper = RelacaoHierarquica.ID
WHERE RelacaoHierarquica.IDTipoNivelRelacionado > @IDTipoNivelRelacionado6
)
INSERT INTO #NvlIDs
SELECT IDStart, IDUpper
FROM Temp
WHERE IDTipoNivelRelacionado > @IDTipoNivelRelacionado6";
command.CommandText = query;
command.Parameters.AddWithValue("@isDeleted", 0);
command.Parameters.AddWithValue("@IDTipoNivelRelacionado6", 6);
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO #Prods SELECT DISTINCT IDNivelProdutor FROM #NvlIDs";
command.ExecuteNonQuery();
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
"INNER JOIN #Prods ON #Prods.IDNivelProdutor = Nivel.ID ");
da.Fill(currentDataSet, "Nivel");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["ControloAut"],
"INNER JOIN NivelControloAut ON NivelControloAut.IDControloAut = ControloAut.ID " +
"INNER JOIN Nivel ON Nivel.ID = NivelControloAut.ID " +
"INNER JOIN #Prods ON #Prods.IDNivelProdutor = Nivel.ID ");
da.Fill(currentDataSet, "ControloAut");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["NivelControloAut"],
"INNER JOIN #Prods ON #Prods.IDNivelProdutor = NivelControloAut.ID ");
da.Fill(currentDataSet, "NivelControloAut");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Dicionario"],
"INNER JOIN ControloAutDicionario ON ControloAutDicionario.IDDicionario = Dicionario.ID " +
"INNER JOIN ControloAut ON ControloAut.ID = ControloAutDicionario.IDControloAut " +
"INNER JOIN NivelControloAut ON NivelControloAut.IDControloAut = ControloAut.ID " +
"INNER JOIN Nivel ON Nivel.ID = NivelControloAut.ID " +
"INNER JOIN #Prods ON #Prods.IDNivelProdutor = Nivel.ID ");
da.Fill(currentDataSet, "Dicionario");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["ControloAutDicionario"],
"INNER JOIN ControloAut ON ControloAut.ID = ControloAutDicionario.IDControloAut " +
"INNER JOIN NivelControloAut ON NivelControloAut.IDControloAut = ControloAut.ID " +
"INNER JOIN Nivel ON Nivel.ID = NivelControloAut.ID " +
"INNER JOIN #Prods ON #Prods.IDNivelProdutor = Nivel.ID ");
da.Fill(currentDataSet, "ControloAutDicionario");
}
command.Parameters.Clear();
command.CommandText = "SELECT * FROM #NvlIDs ORDER BY IDNivelDoc";
SqlDataReader reader = command.ExecuteReader();
long currentID = -1;
long id = 0;
List<long> idProd = new List<long>();
DocGisaInfo dgInfo = new DocGisaInfo();
while (reader.Read())
{
id = reader.GetInt64(0);
if (currentID != id)
{
currentID = id;
dgInfo = new DocGisaInfo();
dgInfo.IDNivelProdutores = new List<long>();
dgInfo.IDNivel = currentID;
result.Add(dgInfo);
}
dgInfo.IDNivelProdutores.Add(reader.GetInt64(1));
}
reader.Close();
command.CommandText = "DROP TABLE #temp; DROP TABLE #Relacoes; DROP TABLE #NvlIDs; DROP TABLE #Prods";
command.ExecuteNonQuery();
}
return result;
}