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