public override List <DocumentoMovimentado> GetDocumentos(long IDReq, string filter, DataSet currentDataSet, IDbConnection conn)
{
List <DocumentoMovimentado> ret = new List <DocumentoMovimentado>();
using (SqlCommand command = new SqlCommand("", (SqlConnection)conn))
{
command.CommandText = "CREATE TABLE #NiveisTemp (IDNivel BIGINT); " +
"CREATE TABLE #SPParametersNiveis (IDNivel BIGINT); " +
"CREATE TABLE #SPResultsCodigos (IDNivel BIGINT, CodigoCompleto NVARCHAR(300));";
command.ExecuteNonQuery();
command.CommandText =
"INSERT INTO #NiveisTemp " +
"SELECT dr.IDNivel " +
"FROM DocumentosMovimentados dr " +
"WHERE dr.IDMovimento = @IDReq AND dr.isDeleted=@isDeleted";
command.Parameters.AddWithValue("@IDReq", IDReq);
command.Parameters.AddWithValue("@isDeleted", 0);
command.ExecuteNonQuery();
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
"INNER JOIN #NiveisTemp ON #NiveisTemp.IDNivel = Nivel.ID");
da.Fill(currentDataSet, "Nivel");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["DocumentosMovimentados"],
"WHERE IDMovimento = @IDReq");
da.Fill(currentDataSet, "DocumentosMovimentados");
}
command.CommandText = "INSERT INTO #SPParametersNiveis " +
"SELECT IDNivel " +
"FROM #NiveisTemp " +
"EXEC sp_getCodigosCompletosNiveis ";
command.ExecuteNonQuery();
command.CommandText =
"SELECT DISTINCT nt.IDNivel, nd.Designacao, tnr.Designacao, dp.InicioAno, dp.InicioMes, dp.InicioDia, dp.FimAno, dp.FimMes, dp.FimDia, codigo.CodigoCompleto " +
"FROM #NiveisTemp nt " +
"INNER JOIN NivelDesignado nd ON nt.IDNivel = nd.ID AND nd.isDeleted = @isDeleted " +
"INNER JOIN RelacaoHierarquica rh ON nt.IDNivel = rh.ID AND rh.isDeleted = @isDeleted " +
"INNER JOIN TipoNivelRelacionado tnr ON rh.IDTipoNivelRelacionado = tnr.ID AND tnr.isDeleted = @isDeleted " +
"INNER JOIN FRDBase frd ON frd.IDNivel = nt.IDNivel AND frd.isDeleted = @isDeleted " +
"INNER JOIN ( " +
"SELECT #SPResultsCodigos.IDNivel, MIN(#SPResultsCodigos.CodigoCompleto) CodigoCompleto " +
"FROM #SPResultsCodigos " +
"INNER JOIN #NiveisTemp p ON p.IDNivel = #SPResultsCodigos.IDNivel " +
"GROUP BY #SPResultsCodigos.IDNivel " +
") codigo ON codigo.IDNivel = nt.IDNivel " +
"LEFT JOIN SFRDDatasProducao dp ON dp.IDFRDBase = frd.ID AND dp.isDeleted = @isDeleted ";
if (filter != string.Empty)
{
command.Parameters.AddWithValue("@ndDesignacao", filter);
command.CommandText += "WHERE nd.Designacao=@ndDesignacao";
}
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
DocumentoMovimentado dm = new DocumentoMovimentado();
dm.IDNivel = System.Convert.ToInt64(reader.GetValue(0));
dm.Designacao = reader.GetValue(1).ToString();
dm.NivelDescricao = reader.GetValue(2).ToString();
dm.AnoInicio = reader.GetValue(3).ToString();
dm.MesInicio = reader.GetValue(4).ToString();
dm.DiaInicio = reader.GetValue(5).ToString();
dm.AnoFim = reader.GetValue(6).ToString();
dm.MesFim = reader.GetValue(7).ToString();
dm.DiaFim = reader.GetValue(8).ToString();
dm.CodigoCompleto = reader.GetValue(9).ToString();
ret.Add(dm);
}
reader.Close();
command.CommandText =
"DROP TABLE #NiveisTemp " +
"DROP TABLE #SPParametersNiveis " +
"DROP TABLE #SPResultsCodigos ";
command.ExecuteNonQuery();
}
return(ret);
}
public override List<DocumentoMovimentado> GetDocumentos(long IDReq, string filter, DataSet currentDataSet, IDbConnection conn)
{
List<DocumentoMovimentado> ret = new List<DocumentoMovimentado>();
using (SqlCommand command = new SqlCommand("", (SqlConnection)conn))
{
command.CommandText = "CREATE TABLE #NiveisTemp (IDNivel BIGINT); "+
"CREATE TABLE #SPParametersNiveis (IDNivel BIGINT); " +
"CREATE TABLE #SPResultsCodigos (IDNivel BIGINT, CodigoCompleto NVARCHAR(300));";
command.ExecuteNonQuery();
command.CommandText =
"INSERT INTO #NiveisTemp " +
"SELECT dr.IDNivel " +
"FROM DocumentosMovimentados dr " +
"WHERE dr.IDMovimento = @IDReq AND dr.isDeleted=@isDeleted";
command.Parameters.AddWithValue("@IDReq", IDReq);
command.Parameters.AddWithValue("@isDeleted", 0);
command.ExecuteNonQuery();
using(SqlDataAdapter da = new SqlDataAdapter(command))
{
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["Nivel"],
"INNER JOIN #NiveisTemp ON #NiveisTemp.IDNivel = Nivel.ID");
da.Fill(currentDataSet, "Nivel");
da.SelectCommand.CommandText = SqlSyntax.CreateSelectCommandText(currentDataSet.Tables["DocumentosMovimentados"],
"WHERE IDMovimento = @IDReq");
da.Fill(currentDataSet, "DocumentosMovimentados");
}
command.CommandText = "INSERT INTO #SPParametersNiveis " +
"SELECT IDNivel " +
"FROM #NiveisTemp " +
"EXEC sp_getCodigosCompletosNiveis ";
command.ExecuteNonQuery();
command.CommandText =
"SELECT DISTINCT nt.IDNivel, nd.Designacao, tnr.Designacao, dp.InicioAno, dp.InicioMes, dp.InicioDia, dp.FimAno, dp.FimMes, dp.FimDia, codigo.CodigoCompleto " +
"FROM #NiveisTemp nt " +
"INNER JOIN NivelDesignado nd ON nt.IDNivel = nd.ID AND nd.isDeleted = @isDeleted " +
"INNER JOIN RelacaoHierarquica rh ON nt.IDNivel = rh.ID AND rh.isDeleted = @isDeleted " +
"INNER JOIN TipoNivelRelacionado tnr ON rh.IDTipoNivelRelacionado = tnr.ID AND tnr.isDeleted = @isDeleted " +
"INNER JOIN FRDBase frd ON frd.IDNivel = nt.IDNivel AND frd.isDeleted = @isDeleted " +
"INNER JOIN ( " +
"SELECT #SPResultsCodigos.IDNivel, MIN(#SPResultsCodigos.CodigoCompleto) CodigoCompleto " +
"FROM #SPResultsCodigos " +
"INNER JOIN #NiveisTemp p ON p.IDNivel = #SPResultsCodigos.IDNivel " +
"GROUP BY #SPResultsCodigos.IDNivel " +
") codigo ON codigo.IDNivel = nt.IDNivel " +
"LEFT JOIN SFRDDatasProducao dp ON dp.IDFRDBase = frd.ID AND dp.isDeleted = @isDeleted ";
if (filter != string.Empty)
{
command.Parameters.AddWithValue("@ndDesignacao", filter);
command.CommandText += "WHERE nd.Designacao=@ndDesignacao";
}
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
DocumentoMovimentado dm = new DocumentoMovimentado();
dm.IDNivel = System.Convert.ToInt64(reader.GetValue(0));
dm.Designacao = reader.GetValue(1).ToString();
dm.NivelDescricao = reader.GetValue(2).ToString();
dm.AnoInicio = reader.GetValue(3).ToString();
dm.MesInicio = reader.GetValue(4).ToString();
dm.DiaInicio = reader.GetValue(5).ToString();
dm.AnoFim = reader.GetValue(6).ToString();
dm.MesFim = reader.GetValue(7).ToString();
dm.DiaFim = reader.GetValue(8).ToString();
dm.CodigoCompleto = reader.GetValue(9).ToString();
ret.Add(dm);
}
reader.Close();
command.CommandText =
"DROP TABLE #NiveisTemp " +
"DROP TABLE #SPParametersNiveis " +
"DROP TABLE #SPResultsCodigos ";
command.ExecuteNonQuery();
}
return ret;
}
