public List <ContaOperacaoModel> Listar(ContaOperacaoModel obj)
{
try
{
return(Selecionar(obj));
}
catch (Exception e)
{
throw e;
}
}
public ContaOperacaoModel Consultar(ContaOperacaoModel obj)
{
try
{
List <ContaOperacaoModel> lst = Selecionar(obj);
return(lst.Count > 0 ? lst.FirstOrDefault() : null);
}
catch (Exception e)
{
throw e;
}
}
public ContaOperacaoModel Consultar(ContaOperacaoModel obj = null)
{
ContaOperacaoDAO contaOperacaoDAO = null;
try
{
contaOperacaoDAO = new ContaOperacaoDAO(GetSqlCommand());
return(contaOperacaoDAO.Consultar(obj));
}
catch (Exception ex)
{
throw ex;
}
}
public ContaOperacaoModel Inserir(ContaOperacaoModel obj)
{
ContaOperacaoDAO contaOperacaoDAO = null;
try
{
contaOperacaoDAO = new ContaOperacaoDAO(GetSqlCommand());
BeginTransaction();
contaOperacaoDAO.Inserir(obj);
Commit();
return(obj);
}
catch (Exception ex)
{
Rollback();
return(obj);
}
}
public int Inserir(ContaOperacaoModel obj)
{
int id = 0;
try
{
StringBuilder strBuilder = new StringBuilder();
GetSqlCommand().Parameters.Clear();
strBuilder.AppendLine(@"INSERT INTO dbo.ContaOperacao
(
IdConta,
IdOperacao,
DataOperacao
)
VALUES
(
@IdConta,
@IdOperacao,
@DataOperacao
)");
GetSqlCommand().CommandText = "";
GetSqlCommand().CommandText = strBuilder.ToString();
GetSqlCommand().Parameters.AddWithValue("IdConta", obj.Conta.Id);
GetSqlCommand().Parameters.AddWithValue("IdOperacao", obj.Operacao.Id);
GetSqlCommand().Parameters.AddWithValue("DataOperacao", obj.DataOperacao);
GetSqlCommand().ExecuteNonQuery();
id = UltimoIdInseridoIdentity("ContaOperacao");
return(id);
}
catch (Exception ex)
{
throw ex;
}
}
private List <ContaOperacaoModel> Selecionar(ContaOperacaoModel obj)
{
List <ContaOperacaoModel> lst = null;
try
{
objSbSelect = new StringBuilder();
objSbSelect.AppendLine(@"
SELECT ContaOperacao.IdContaOperacao
, ContaOperacao.DataOperacao
, Conta.IdConta
, Conta.Numero
, Conta.Agencia
, ContaTipo.IdContaTipo
, ContaTipo.Descricao AS ContaTipo
, Operacao.IdOperacao
, Operacao.Descricao AS Operacao
, Operacao.Custo
FROM ContaOperacao
INNER JOIN Conta
ON ContaOperacao.IdConta = Conta.IdConta
INNER JOIN ContaTipo
ON Conta.IdContaTipo = ContaTipo.IdContaTipo
INNER JOIN Operacao
ON ContaOperacao.IdOperacao = Operacao.IdOperacao
WHERE 1 = 1 ");
if (obj != null)
{
GetSqlCommand().Parameters.Clear();
if (obj.Id > 0)
{
objSbSelect.AppendLine(@" AND ContaOperacao.IdContaOperacao = @IdContaOperacao");
GetSqlCommand().Parameters.Add("IdContaOperacao", SqlDbType.Int).Value = obj.Id;
}
if (obj.Ano > 0 && obj.Mes > 0)
{
objSbSelect.AppendLine(@" AND YEAR(ContaOperacao.DataOperacao) = @Ano");
objSbSelect.AppendLine(@" AND MONTH(ContaOperacao.DataOperacao) = @Mes");
GetSqlCommand().Parameters.Add("Ano", SqlDbType.Int).Value = obj.Ano;
GetSqlCommand().Parameters.Add("Mes", SqlDbType.Int).Value = obj.Mes;
}
if (obj.Operacao.Id > 0)
{
objSbSelect.AppendLine(@" AND Operacao.IdOperacao = @IdOperacao");
GetSqlCommand().Parameters.Add("IdOperacao", SqlDbType.Int).Value = obj.Operacao.Id;
}
if (obj.Conta.Id > 0)
{
objSbSelect.AppendLine(@" AND Conta.IdConta = @IdConta");
GetSqlCommand().Parameters.Add("IdConta", SqlDbType.Int).Value = obj.Conta.Id;
}
if (!string.IsNullOrEmpty(obj.Conta.Numero))
{
objSbSelect.AppendLine(@" AND Conta.Numero LIKE '@Numero'");
GetSqlCommand().Parameters.Add("Numero", SqlDbType.VarChar).Value = obj.Conta.Numero;
}
if (!string.IsNullOrEmpty(obj.Conta.Agencia))
{
objSbSelect.AppendLine(@" AND Conta.Agencia LIKE '@Agencia'");
GetSqlCommand().Parameters.Add("Agencia", SqlDbType.VarChar).Value = obj.Conta.Agencia;
}
}
GetSqlCommand().CommandText = "";
GetSqlCommand().CommandText = objSbSelect.ToString();
lst = new List <ContaOperacaoModel>();
while (GetSqlDataReader().Read())
{
ContaOperacaoModel item = new ContaOperacaoModel();
if (!(GetSqlDataReader().IsDBNull(GetSqlDataReader().GetOrdinal("IdContaOperacao"))))
{
item.Id = Convert.ToInt32(GetSqlDataReader()["IdContaOperacao"]);
}
if (!(GetSqlDataReader().IsDBNull(GetSqlDataReader().GetOrdinal("DataOperacao"))))
{
item.DataOperacao = Convert.ToDateTime(GetSqlDataReader()["DataOperacao"]);
}
if (!(GetSqlDataReader().IsDBNull(GetSqlDataReader().GetOrdinal("IdConta"))))
{
item.Conta.Id = Convert.ToInt32(GetSqlDataReader()["IdConta"]);
}
if (!(GetSqlDataReader().IsDBNull(GetSqlDataReader().GetOrdinal("IdContaTipo"))))
{
item.Conta.ContaTipo.Id = Convert.ToInt32(GetSqlDataReader()["IdContaTipo"]);
}
if (!(GetSqlDataReader().IsDBNull(GetSqlDataReader().GetOrdinal("ContaTipo"))))
{
item.Conta.ContaTipo.Descricao = Convert.ToString(GetSqlDataReader()["ContaTipo"]);
}
if (!(GetSqlDataReader().IsDBNull(GetSqlDataReader().GetOrdinal("Numero"))))
{
item.Conta.Numero = Convert.ToString(GetSqlDataReader()["Numero"]);
}
if (!(GetSqlDataReader().IsDBNull(GetSqlDataReader().GetOrdinal("Agencia"))))
{
item.Conta.Agencia = Convert.ToString(GetSqlDataReader()["Agencia"]);
}
if (!(GetSqlDataReader().IsDBNull(GetSqlDataReader().GetOrdinal("IdOperacao"))))
{
item.Operacao.Id = Convert.ToInt32(GetSqlDataReader()["IdOperacao"]);
}
if (!(GetSqlDataReader().IsDBNull(GetSqlDataReader().GetOrdinal("Operacao"))))
{
item.Operacao.Descricao = Convert.ToString(GetSqlDataReader()["Operacao"]);
}
if (!(GetSqlDataReader().IsDBNull(GetSqlDataReader().GetOrdinal("Custo"))))
{
item.Operacao.Custo = Convert.ToDecimal(GetSqlDataReader()["Custo"]);
}
lst.Add(item);
}
return(lst);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (objSbSelect != null)
{
objSbSelect = null;
}
Close();
}
}
