Пример #1
0
        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;
        }
Пример #2
0
        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;
        }