public override ArrayList GetItemsNav(DataSet currentDataSet, int pageNr, int itemsPerPage, IDbConnection conn) { ArrayList rows = new ArrayList(); using (SqlCommand command = new SqlCommand("", (SqlConnection)conn)) { command.CommandText = "CREATE TABLE #ItemsID (ID BIGINT, seq_id BIGINT)"; command.ExecuteNonQuery(); command.CommandText = "INSERT INTO #ItemsID SELECT ID,seq_id FROM #OrderedItems WHERE seq_id >= @seq_id1 AND seq_id <= @seq_id2"; command.Parameters.AddWithValue("@seq_id1", (pageNr - 1) * itemsPerPage + 1); command.Parameters.AddWithValue("@seq_id2", pageNr * itemsPerPage); command.ExecuteNonQuery(); // Carregamento de informação dos IDs calculados e obtidos anteriormente using (SqlDataAdapter da = new SqlDataAdapter(command)) { command.Parameters.AddWithValue("@isDeleted", 0); da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"], "INNER JOIN #ItemsID ON #ItemsID.ID = Nivel.ID"); da.Fill(currentDataSet, "Nivel"); da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["NivelDesignado"], "INNER JOIN #ItemsID ON #ItemsID.ID = NivelDesignado.ID"); da.Fill(currentDataSet, "NivelDesignado"); //da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["NivelDocumentoSimples"], // "INNER JOIN #ItemsID ON #ItemsID.ID = NivelDocumentoSimples.ID"); //da.Fill(currentDataSet, "NivelDocumentoSimples"); da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["RelacaoHierarquica"], "INNER JOIN #ItemsID ON #ItemsID.ID = RelacaoHierarquica.ID"); da.Fill(currentDataSet, "RelacaoHierarquica"); // carregar niveis dos parents para o caso do filtro "esconder niveis não directos" nao estar activo da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"], "INNER JOIN RelacaoHierarquica rh ON rh.IDUpper = Nivel.ID " + "INNER JOIN #ItemsID ON #ItemsID.ID = rh.ID"); da.Fill(currentDataSet, "Nivel"); da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["FRDBase"], "INNER JOIN #ItemsID ON #ItemsID.ID = FRDBase.IDNivel"); da.Fill(currentDataSet, "FRDBase"); //necessário apenas para a integração //constraint = // "INNER JOIN FRDBase frd ON frd.ID = SFRDDatasProducao.IDFRDBase " + // "INNER JOIN #ItemsID ON #ItemsID.ID = frd.IDNivel"; //da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["SFRDDatasProducao"], constraint); //da.Fill(currentDataSet, "SFRDDatasProducao"); } command.CommandText = "SELECT n.ID, nd.Designacao, rh.IDTipoNivelRelacionado, " + // 0; 1; 2 "Requisitado = " + // 3 " CASE " + " WHEN Mov.Data_Req IS NULL OR (Mov.Data_Dev IS NOT NULL AND Mov.Data_Req < Mov.Data_Dev) THEN 'Não' " + " ELSE 'Sim' " + " END, " + "tnr.GUIOrder, " + // 4 "dp.FimAno, " + "dp.FimMes, " + "dp.FimDia, " + "dp.InicioAno, " + "dp.InicioMes, " + "dp.InicioDia, " + "dp.FimAtribuida, " + "dp.InicioAtribuida, " + "agr.Agrupador " + "FROM Nivel n " + "LEFT JOIN ( " + " SELECT n1.ID IDNivel, MAX(req.Data) Data_Req, MAX(dev.Data) Data_Dev " + " FROM Nivel n1 " + " LEFT JOIN DocumentosMovimentados dm ON dm.IDNivel = n1.ID AND dm.isDeleted = @isDeleted " + " LEFT JOIN Movimento req ON req.ID = dm.IDMovimento and req.CatCode = 'REQ' AND req.isDeleted = @isDeleted " + " LEFT JOIN Movimento dev ON dev.ID = dm.IDMovimento AND dev.CatCode = 'DEV' AND dev.isDeleted = @isDeleted " + " GROUP BY n1.ID " + ") Mov ON Mov.IDNivel = n.ID " + "INNER JOIN #ItemsID ON #ItemsID.ID = n.ID " + "INNER JOIN (" + "SELECT DISTINCT rh.ID, rh.IDTipoNivelRelacionado " + "FROM #ItemsID " + "INNER JOIN RelacaoHierarquica rh ON rh.ID = #ItemsID.ID AND rh.isDeleted = @isDeleted " + ") rh ON rh.ID = n.ID " + "INNER JOIN NivelDesignado nd ON n.ID = nd.ID " + "INNER JOIN TipoNivelRelacionado tnr ON tnr.ID = rh.IDTipoNivelRelacionado " + "INNER JOIN FRDBase frd ON frd.IDNivel = n.ID AND frd.isDeleted = @isDeleted " + "LEFT JOIN SFRDDatasProducao dp ON dp.IDFRDBase = frd.ID " + "LEFT JOIN SFRDAgrupador agr ON agr.IDFRDBase = frd.ID " + " WHERE " + " n.isDeleted = @isDeleted " + " AND (dp.IDFRDBase IS NULL OR dp.isDeleted = @isDeleted) " + " AND n.IDTipoNivel = @IDTipoNivel " + "ORDER BY #ItemsID.seq_id "; command.Parameters.AddWithValue("@IDTipoNivel", 3); SqlDataReader reader = command.ExecuteReader(); NivelDocumentalListItem row; while (reader.Read()) { row = new NivelDocumentalListItem(); row.IDNivel = System.Convert.ToInt64(reader.GetValue(0)); row.Designacao = reader.GetString(1); row.IDTipoNivelRelacionado = System.Convert.ToInt64(reader.GetValue(2)); row.Requisitado = reader.GetString(3); row.GUIOrder = System.Convert.ToInt32(reader.GetValue(4)); row.FimAno = reader.GetValue(5).ToString(); row.FimMes = reader.GetValue(6).ToString(); row.FimDia = reader.GetValue(7).ToString(); row.InicioAno = reader.GetValue(8).ToString(); row.InicioMes = reader.GetValue(9).ToString(); row.InicioDia = reader.GetValue(10).ToString(); if (reader.GetValue(11) != null && reader.GetValue(11) != DBNull.Value) row.FimAtribuida = Convert.ToBoolean(reader.GetValue(11)); if (reader.GetValue(12) != null && reader.GetValue(12) != DBNull.Value) row.InicioAtribuida = Convert.ToBoolean(reader.GetValue(12)); row.Agrupador = !reader.IsDBNull(13) ? reader.GetString(13) : ""; rows.Add(row); } reader.Close(); } return rows; }
public override List<NivelDocumentalListItem> GetItemsGA(DataSet currentDataSet, int pageNr, int itemsPerPage, IDbConnection conn) { List<NivelDocumentalListItem> rows = new List<NivelDocumentalListItem>(); using (SqlCommand command = new SqlCommand("", (SqlConnection)conn)) { command.CommandText = "CREATE TABLE #ItemsID (ID BIGINT, seq_id INT)"; command.ExecuteNonQuery(); command.CommandText = "INSERT INTO #ItemsID SELECT ID,seq_id FROM #OrderedItems WHERE seq_id >= @seq_id1 AND seq_id <= @seq_id2"; command.Parameters.AddWithValue("@seq_id1", (pageNr - 1) * itemsPerPage + 1); command.Parameters.AddWithValue("@seq_id2", pageNr * itemsPerPage); command.ExecuteNonQuery(); // Carregamento de informação dos IDs calculados e obtidos anteriormente using (SqlDataAdapter da = new SqlDataAdapter(command)) { command.Parameters.AddWithValue("@isDeleted", 0); da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"], "INNER JOIN #ItemsID ON #ItemsID.ID = Nivel.ID "); da.Fill(currentDataSet, "Nivel"); da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["NivelDesignado"], "INNER JOIN #ItemsID ON #ItemsID.ID = NivelDesignado.ID "); da.Fill(currentDataSet, "NivelDesignado"); } command.CommandText = "SELECT n.ID, nd.Designacao, tnr.ID, " + "tnr.GUIOrder " + "FROM Nivel n " + "INNER JOIN #ItemsID ON #ItemsID.ID = n.ID " + "INNER JOIN (" + "SELECT rh.ID, MIN(rh.IDTipoNivelRelacionado) IDTipoNivelRelacionado " + "FROM #ItemsID " + "INNER JOIN RelacaoHierarquica rh ON rh.ID = #ItemsID.ID AND rh.isDeleted = @isDeleted " + "GROUP BY rh.ID " + ") TipoNivelRelacionado ON TipoNivelRelacionado.ID = n.ID " + "INNER JOIN TipoNivelRelacionado tnr ON tnr.ID = TipoNivelRelacionado.IDTipoNivelRelacionado AND tnr.isDeleted = @isDeleted " + "INNER JOIN NivelDesignado nd ON nd.ID = n.ID AND nd.isDeleted = @isDeleted " + "WHERE n.isDeleted = @isDeleted AND n.IDTipoNivel = @IDTipoNivel " + "ORDER BY seq_id"; command.Parameters.AddWithValue("@IDTipoNivel", 1); SqlDataReader reader = command.ExecuteReader(); NivelDocumentalListItem row; while (reader.Read()) { row = new NivelDocumentalListItem(); row.IDNivel = reader.GetInt64(0); row.Designacao = reader.GetString(1); row.IDTipoNivelRelacionado = reader.GetInt64(2); row.GUIOrder = System.Convert.ToInt32(reader.GetValue(3)); rows.Add(row); } reader.Close(); } return rows; }
//public override List<NivelDocumentalListItem> GetItems(DataSet currentDataSet, long parentNivelID, int pageNr, long exceptTipoNivel, int itemsPerPage, IDbConnection conn) { public override ArrayList GetItems(DataSet currentDataSet, int pageNr, long exceptTipoNivel, int itemsPerPage, IDbConnection conn) { ArrayList rows = new ArrayList(); using (SqlCommand command = new SqlCommand("", (SqlConnection)conn)) { command.CommandText = "CREATE TABLE #ItemsID (ID BIGINT, seq_id INT)"; command.ExecuteNonQuery(); command.CommandText = "INSERT INTO #ItemsID SELECT ID,seq_id FROM #OrderedItems WHERE seq_id >= @seq_id1 AND seq_id <= @seq_id2"; command.Parameters.AddWithValue("@seq_id1", (pageNr - 1) * itemsPerPage + 1); command.Parameters.AddWithValue("@seq_id2", pageNr * itemsPerPage); command.ExecuteNonQuery(); // Carregamento de informação dos IDs calculados e obtidos anteriormente using (SqlDataAdapter da = new SqlDataAdapter(command)) { command.Parameters.AddWithValue("@isDeleted", 0); da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"], "INNER JOIN #ItemsID ON #ItemsID.ID = Nivel.ID "); da.Fill(currentDataSet, "Nivel"); } command.CommandText = "SELECT n.ID, nd.Designacao, tnr.ID, " + // 0; 1; 2 "dp.FimAno, " + "dp.FimMes, " + "dp.FimDia, " + "dp.InicioAno, " + "dp.InicioMes, " + "dp.InicioDia, " + "dp.FimAtribuida, " + "dp.InicioAtribuida, " + "tnr.GUIOrder " + // 4 "FROM Nivel n " + "INNER JOIN #ItemsID ON #ItemsID.ID = n.ID " + "INNER JOIN (" + "SELECT rh.ID, MIN(rh.IDTipoNivelRelacionado) IDTipoNivelRelacionado " + "FROM #ItemsID " + "INNER JOIN RelacaoHierarquica rh ON rh.ID = #ItemsID.ID AND rh.isDeleted = @isDeleted " + "GROUP BY rh.ID " + ") TipoNivelRelacionado ON TipoNivelRelacionado.ID = n.ID " + "INNER JOIN NivelDesignado nd ON n.ID = nd.ID " + "INNER JOIN TipoNivelRelacionado tnr ON tnr.ID = TipoNivelRelacionado.IDTipoNivelRelacionado " + "LEFT JOIN FRDBase frd ON frd.IDNivel = n.ID " + "LEFT JOIN SFRDDatasProducao dp ON dp.IDFRDBase = frd.ID " + "WHERE n.isDeleted = 0 AND nd.isDeleted = @isDeleted " + "AND (frd.IDTipoFRDBase IS NULL OR frd.IDTipoFRDBase = @IDTipoFRDBase) " + "AND (dp.IDFRDBase IS NULL OR dp.isDeleted = @isDeleted) " + "ORDER BY seq_id"; command.Parameters.AddWithValue("@IDTipoFRDBase", 1); SqlDataReader reader = command.ExecuteReader(); NivelDocumentalListItem row; while (reader.Read()) { row = new NivelDocumentalListItem(); row.IDNivel = System.Convert.ToInt64(reader.GetValue(0)); row.Designacao = reader.GetString(1); row.IDTipoNivelRelacionado = System.Convert.ToInt64(reader.GetValue(2)); row.FimAno = reader.GetValue(3).ToString(); row.FimMes = reader.GetValue(4).ToString(); row.FimDia = reader.GetValue(5).ToString(); row.InicioAno = reader.GetValue(6).ToString(); row.InicioMes = reader.GetValue(7).ToString(); row.InicioDia = reader.GetValue(8).ToString(); if (reader.GetValue(9) != null && reader.GetValue(9) != DBNull.Value) row.FimAtribuida = Convert.ToBoolean(reader.GetValue(9)); if (reader.GetValue(10) != null && reader.GetValue(10) != DBNull.Value) row.InicioAtribuida = Convert.ToBoolean(reader.GetValue(10)); row.GUIOrder = System.Convert.ToInt32(reader.GetValue(11)); rows.Add(row); } reader.Close(); } return rows; }
public override List<NivelDocumentalListItem> GetItemsEstrutural(DataSet currentDataSet, int pageNr, int itemsPerPage, IDbConnection conn) { List<NivelDocumentalListItem> rows = new List<NivelDocumentalListItem>(); using (SqlCommand command = new SqlCommand("", (SqlConnection)conn)) { command.CommandText = "CREATE TABLE #ItemsID (ID BIGINT, seq_id INT)"; command.ExecuteNonQuery(); command.CommandText = "INSERT INTO #ItemsID SELECT ID,seq_id FROM #OrderedItems WHERE seq_id >= @seq_id1 AND seq_id <= @seq_id2"; command.Parameters.AddWithValue("@seq_id1", (pageNr - 1) * itemsPerPage + 1); command.Parameters.AddWithValue("@seq_id2", pageNr * itemsPerPage); command.ExecuteNonQuery(); // Carregamento de informação dos IDs calculados e obtidos anteriormente using (SqlDataAdapter da = new SqlDataAdapter(command)) { command.Parameters.AddWithValue("@isDeleted", 0); da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"], "INNER JOIN #ItemsID ON #ItemsID.ID = Nivel.ID "); da.Fill(currentDataSet, "Nivel"); da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["RelacaoHierarquica"], "INNER JOIN #ItemsID ON #ItemsID.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 #ItemsID ON #ItemsID.ID = RelacaoHierarquica.ID "); da.Fill(currentDataSet, "Nivel"); } command.CommandText = "SELECT n.ID, d.Termo, tnr.ID, " + "dp.FimAno, " + "dp.FimMes, " + "dp.FimDia, " + "dp.InicioAno, " + "dp.InicioMes, " + "dp.InicioDia, " + "dp.FimAtribuida, " + "dp.InicioAtribuida, " + "tnr.GUIOrder " + "FROM Nivel n " + "INNER JOIN #ItemsID ON #ItemsID.ID = n.ID " + "INNER JOIN (" + "SELECT rh.ID, MIN(rh.IDTipoNivelRelacionado) IDTipoNivelRelacionado " + "FROM #ItemsID " + "INNER JOIN RelacaoHierarquica rh ON rh.ID = #ItemsID.ID AND rh.isDeleted = @isDeleted " + "GROUP BY rh.ID " + ") TipoNivelRelacionado ON TipoNivelRelacionado.ID = n.ID " + "INNER JOIN TipoNivelRelacionado tnr ON tnr.ID = TipoNivelRelacionado.IDTipoNivelRelacionado AND tnr.isDeleted = @isDeleted " + "INNER JOIN NivelControloAut nca ON nca.ID = n.ID AND nca.isDeleted = @isDeleted " + "INNER JOIN ControloAut ca ON ca.ID = nca.IDControloAut AND ca.IDTipoNoticiaAut = @IDTipoNoticiaAut AND ca.isDeleted = @isDeleted " + "INNER JOIN ControloAutDicionario cad ON cad.IDControloAut = ca.ID AND cad.IDTipoControloAutForma = @IDTipoControloAutForma AND cad.isDeleted = @isDeleted " + "INNER JOIN Dicionario d ON d.ID = cad.IDDicionario AND d.isDeleted = @isDeleted " + "INNER JOIN FRDBase frd ON frd.IDNivel = n.ID AND frd.IDTipoFRDBase = @IDTipoFRDBase AND frd.isDeleted = @isDeleted " + "LEFT JOIN SFRDDatasProducao dp ON dp.IDFRDBase = frd.ID AND dp.isDeleted = @isDeleted " + "WHERE n.isDeleted = @isDeleted AND n.IDTipoNivel = @IDTipoNivel " + "ORDER BY seq_id"; command.Parameters.AddWithValue("@IDTipoNivel", 2); command.Parameters.AddWithValue("@IDTipoFRDBase", 1); command.Parameters.AddWithValue("@IDTipoNoticiaAut", 4); command.Parameters.AddWithValue("@IDTipoControloAutForma", 1); SqlDataReader reader = command.ExecuteReader(); NivelDocumentalListItem row; while (reader.Read()) { row = new NivelDocumentalListItem(); row.IDNivel = reader.GetInt64(0); row.Designacao = reader.GetString(1); row.IDTipoNivelRelacionado = reader.GetInt64(2); row.FimAno = reader.GetValue(3).ToString(); row.FimMes = reader.GetValue(4).ToString(); row.FimDia = reader.GetValue(5).ToString(); row.InicioAno = reader.GetValue(6).ToString(); row.InicioMes = reader.GetValue(7).ToString(); row.InicioDia = reader.GetValue(8).ToString(); if (!reader.IsDBNull(9)) row.FimAtribuida = reader.GetBoolean(9); if (!reader.IsDBNull(10)) row.InicioAtribuida = reader.GetBoolean(10); row.GUIOrder = System.Convert.ToInt32(reader.GetValue(11)); rows.Add(row); } reader.Close(); } return rows; }