public override List <DocumentoRequisicaoInfo> getDocumentosNaoDevolvidos(IDbConnection conn)
{
List <DocumentoRequisicaoInfo> resultSet = new List <DocumentoRequisicaoInfo>();
DocumentoRequisicaoInfo result = null;
SqlDataReader reader;
using (SqlCommand command = new SqlCommand("", (SqlConnection)conn))
{
command.CommandText =
"CREATE TABLE #NiveisDoc (IDNivel BIGINT, Designacao NVARCHAR(768), IDMovimento BIGINT, Entidade NVARCHAR(444), Data DATETIME, Notas NTEXT) " +
"CREATE TABLE #SPParametersNiveis (IDNivel BIGINT); " +
"CREATE TABLE #SPResultsCodigos (IDNivel BIGINT,CodigoCompleto NVARCHAR(300)); ";
command.ExecuteNonQuery();
command.CommandText =
"INSERT INTO #NiveisDoc " +
"SELECT docs.ID, docs.Designacao, m.ID, me.Entidade, m.Data, m.Notas " +
"FROM (" +
"SELECT n.ID, nd.Designacao, MAX(req.Data) reqData " +
"FROM Nivel n " +
"LEFT JOIN DocumentosMovimentados dm ON dm.IDNivel = n.ID AND dm.isDeleted = @isDeleted " +
"LEFT JOIN Movimento req ON req.ID = dm.IDMovimento and req.CatCode = @reqCatCode AND req.isDeleted = @isDeleted " +
"LEFT JOIN Movimento dev ON dev.ID = dm.IDMovimento AND dev.CatCode = @devCatCode AND dev.isDeleted = @isDeleted " +
"INNER JOIN NivelDesignado nd ON nd.ID = n.ID " +
"GROUP BY n.ID, nd.Designacao " +
"HAVING NOT MAX(req.Data) IS NULL AND (MAX(dev.Data) IS NULL OR MAX(dev.Data) < MAX(req.Data))) docs, Movimento m " +
"INNER JOIN MovimentoEntidade me ON m.IDEntidade = me.ID AND me.isDeleted = @isDeleted " +
"WHERE m.CatCode = @reqCatCode AND m.Data = docs.reqData AND m.isDeleted = @isDeleted";
command.Parameters.AddWithValue("@reqCatCode", "REQ");
command.Parameters.AddWithValue("@devCatCode", "DEV");
command.Parameters.AddWithValue("@isDeleted", 0);
command.ExecuteNonQuery();
command.CommandText = " INSERT INTO #SPParametersNiveis SELECT IDNivel FROM #NiveisDoc ";
command.ExecuteNonQuery();
command.CommandText = "EXEC sp_getCodigosCompletosNiveis ";
command.ExecuteNonQuery();
command.CommandText =
// 0, 1, 2, 3,
"SELECT #NiveisDoc.IDNivel, codigo.CodigoCompleto, Designacao, IDMovimento, " +
// 4, 5, 6
"Entidade, Data, Notas " +
"FROM #NiveisDoc " +
"INNER JOIN ( " +
"SELECT #SPResultsCodigos.IDNivel, MIN(#SPResultsCodigos.CodigoCompleto) CodigoCompleto " +
"FROM #SPResultsCodigos " +
"INNER JOIN #NiveisDoc p ON p.IDNivel = #SPResultsCodigos.IDNivel " +
"GROUP BY #SPResultsCodigos.IDNivel " +
") codigo ON codigo.IDNivel = #NiveisDoc.IDNivel " +
"ORDER BY Data ";
try
{
reader = command.ExecuteReader();
while (reader.Read())
{
result = new DocumentoRequisicaoInfo();
result.idNivel = reader.GetInt64(0);
result.Codigo_Completo = reader.GetString(1);
result.ND_Designacao = reader.GetString(2);
result.idMovimento = reader.GetInt64(3);
if (!reader.IsDBNull(4))
{
result.entidade = reader.GetString(4);
}
if (!reader.IsDBNull(5))
{
result.data = reader.GetDateTime(5);
}
if (!reader.IsDBNull(6))
{
result.notas = reader.GetString(6);
}
resultSet.Add(result);
}
reader.Close();
}
catch (Exception e)
{
throw e;
}
// Remover as tabelas temps:
command.CommandText =
"DROP TABLE #NiveisDoc " +
"DROP TABLE #SPParametersNiveis " +
"DROP TABLE #SPResultsCodigos ";
command.ExecuteNonQuery();
}
return(resultSet);
}
public override List<DocumentoRequisicaoInfo> getDocumentosNaoDevolvidos(IDbConnection conn) {
List<DocumentoRequisicaoInfo> resultSet = new List<DocumentoRequisicaoInfo>();
DocumentoRequisicaoInfo result = null;
SqlDataReader reader;
using (SqlCommand command = new SqlCommand("", (SqlConnection)conn))
{
command.CommandText =
"CREATE TABLE #NiveisDoc (IDNivel BIGINT, Designacao NVARCHAR(768), IDMovimento BIGINT, Entidade NVARCHAR(444), Data DATETIME, Notas NTEXT) " +
"CREATE TABLE #SPParametersNiveis (IDNivel BIGINT); " +
"CREATE TABLE #SPResultsCodigos (IDNivel BIGINT,CodigoCompleto NVARCHAR(300)); ";
command.ExecuteNonQuery();
command.CommandText =
"INSERT INTO #NiveisDoc " +
"SELECT docs.ID, docs.Designacao, m.ID, me.Entidade, m.Data, m.Notas " +
"FROM (" +
"SELECT n.ID, nd.Designacao, MAX(req.Data) reqData " +
"FROM Nivel n " +
"LEFT JOIN DocumentosMovimentados dm ON dm.IDNivel = n.ID AND dm.isDeleted = @isDeleted " +
"LEFT JOIN Movimento req ON req.ID = dm.IDMovimento and req.CatCode = @reqCatCode AND req.isDeleted = @isDeleted " +
"LEFT JOIN Movimento dev ON dev.ID = dm.IDMovimento AND dev.CatCode = @devCatCode AND dev.isDeleted = @isDeleted " +
"INNER JOIN NivelDesignado nd ON nd.ID = n.ID " +
"GROUP BY n.ID, nd.Designacao " +
"HAVING NOT MAX(req.Data) IS NULL AND (MAX(dev.Data) IS NULL OR MAX(dev.Data) < MAX(req.Data))) docs, Movimento m " +
"INNER JOIN MovimentoEntidade me ON m.IDEntidade = me.ID AND me.isDeleted = @isDeleted " +
"WHERE m.CatCode = @reqCatCode AND m.Data = docs.reqData AND m.isDeleted = @isDeleted";
command.Parameters.AddWithValue("@reqCatCode", "REQ");
command.Parameters.AddWithValue("@devCatCode", "DEV");
command.Parameters.AddWithValue("@isDeleted", 0);
command.ExecuteNonQuery();
command.CommandText = " INSERT INTO #SPParametersNiveis SELECT IDNivel FROM #NiveisDoc ";
command.ExecuteNonQuery();
command.CommandText = "EXEC sp_getCodigosCompletosNiveis ";
command.ExecuteNonQuery();
command.CommandText =
// 0, 1, 2, 3,
"SELECT #NiveisDoc.IDNivel, codigo.CodigoCompleto, Designacao, IDMovimento, " +
// 4, 5, 6
"Entidade, Data, Notas " +
"FROM #NiveisDoc " +
"INNER JOIN ( " +
"SELECT #SPResultsCodigos.IDNivel, MIN(#SPResultsCodigos.CodigoCompleto) CodigoCompleto " +
"FROM #SPResultsCodigos " +
"INNER JOIN #NiveisDoc p ON p.IDNivel = #SPResultsCodigos.IDNivel " +
"GROUP BY #SPResultsCodigos.IDNivel " +
") codigo ON codigo.IDNivel = #NiveisDoc.IDNivel " +
"ORDER BY Data ";
try
{
reader = command.ExecuteReader();
while (reader.Read())
{
result = new DocumentoRequisicaoInfo();
result.idNivel = reader.GetInt64(0);
result.Codigo_Completo = reader.GetString(1);
result.ND_Designacao = reader.GetString(2);
result.idMovimento = reader.GetInt64(3);
if (!reader.IsDBNull(4))
result.entidade = reader.GetString(4);
if (!reader.IsDBNull(5))
result.data = reader.GetDateTime(5);
if (!reader.IsDBNull(6))
result.notas = reader.GetString(6);
resultSet.Add(result);
}
reader.Close();
}
catch (Exception e)
{
throw e;
}
// Remover as tabelas temps:
command.CommandText =
"DROP TABLE #NiveisDoc " +
"DROP TABLE #SPParametersNiveis " +
"DROP TABLE #SPResultsCodigos ";
command.ExecuteNonQuery();
}
return resultSet;
}