public IEnumerable <RelatorioFinanceiro> GetRelatorioDespesas(RelatorioFinanceiroSearch filters)
{
return(_relatorioFinanceiroRepositoryReadOnly.GetRelatorioDespesas(filters));
}
public IEnumerable <RelatorioFinanceiro> GetRelatorioDespesas(RelatorioFinanceiroSearch filters)
{
using (var cn = Connection)
{
var dt = new DateTime();
var query = @"
select d.*, f.Nome as Funcionario, fo.Nome as Fornecedor, t.Descricao as TipoPagamento, cf.Categoria, sc.SubCategoria, t.Descricao
from Despesas d
left join SubCategoriaFinanceira sc on d.SubCategoriaFinanceiraId = sc.SubCategoriaFinanceiraId
left join Funcionarios f on d.FuncionarioId = f.FuncionarioId
left join Fornecedores fo on d.FornecedorId = fo.FornecedorId
left join Tipopagamento t on d.TipoPagamentoId = t.TipoPagamentoId
inner join CategoriaFinanceira cf on d.CategoriaFinanceiraId = cf.CategoriaFinanceiraId
where 1 = 1";
if (filters.EmissaoInicio != dt)
{
query = query + " and d.DataEmissao >= '" + filters.EmissaoInicio.ToString("yyyy-MM-dd 00:00:00") + "' ";
}
if (filters.EmissaoFim != dt)
{
query = query + " and d.DataEmissao <= '" + filters.EmissaoFim.ToString("yyyy-MM-dd 23:59:59") + "' ";
}
if (filters.VencimentoInicio != dt)
{
query = query + " and d.DataVencimento >= '" + filters.VencimentoInicio.ToString("yyyy-MM-dd 00:00:00") + "' ";
}
if (filters.VencimentoFim != dt)
{
query = query + " and d.DataVencimento <= '" + filters.VencimentoFim.ToString("yyyy-MM-dd 23:59:59") + "' ";
}
if (filters.PagamentoInicio != dt)
{
query = query + " and d.DataPagamento >= '" + filters.PagamentoInicio.ToString("yyyy-MM-dd 00:00:00") + "' ";
}
if (filters.PagamentoFim != dt)
{
query = query + " and d.DataPagamento <= '" + filters.PagamentoFim.ToString("yyyy-MM-dd 23:59:59") + "' ";
}
if (filters.CategoriaId != null)
{
query = query + " and cf.CategoriaFinanceiraId = '" + filters.CategoriaId + "' ";
}
if (filters.SubCategoriaId != null)
{
query = query + " and sc.SubCategoriaFinanceiraId = '" + filters.SubCategoriaId + "' ";
}
if (filters.Situacao != "")
{
query = query + " and d.Situacao = '" + filters.Situacao + "' ";
}
if (filters.FornecedorId != null)
{
query = query + " and d.FornecedorId = '" + filters.FornecedorId + "' ";
}
if (filters.FuncionarioId != null)
{
query = query + " and f.FuncionarioId = '" + filters.FuncionarioId + "' ";
}
if (filters.TipoPagamentoId != null)
{
query = query + " and d.TipoPagamentoId = '" + filters.TipoPagamentoId + "' ";
}
if (filters.Documento != null)
{
query = query + " and d.Documento like '%" + filters.Documento + "%' ";
}
cn.Open();
var relatorio = cn.Query <RelatorioFinanceiro>(query);
cn.Close();
return(relatorio);
}
}
public IEnumerable <RelatorioFinanceiro> GetRelatorio(RelatorioFinanceiroSearch filters)
{
using (var cn = Connection)
{
var dt = new DateTime();
var query = @"
select
r.Documento,
r.DataVencimento,
r.DataEmissao,
r.DataPagamento,
r.Valor,
r.ValorTotal,
cf.Tipo,
r.Situacao,
cf.Categoria,
sc.SubCategoria,
tp.Descricao as TipoPagamento,
r.Observacao
from Receitas r
left join SubCategoriaFinanceira sc on r.SubCategoriaFinanceiraId = sc.SubCategoriaFinanceiraId
inner join CategoriaFinanceira cf on r.CategoriaFinanceiraId = cf.CategoriaFinanceiraId
left join TipoPagamento tp on r.TipoPagamentoId = tp.TipoPagamentoId
where 1 = 1";
if (filters.Tipo != null && filters.Tipo != "Todos")
{
query = query + " and cf.Tipo = '" + filters.Tipo + "' ";
}
if (filters.EmissaoInicio != dt)
{
query = query + " and r.DataEmissao >= '" + filters.EmissaoInicio.ToString("yyyy-MM-dd 00:00:00") + "' ";
}
if (filters.EmissaoFim != dt)
{
query = query + " and r.DataEmissao <= '" + filters.EmissaoFim.ToString("yyyy-MM-dd 23:59:59") + "' ";
}
if (filters.VencimentoInicio != dt)
{
query = query + " and r.DataVencimento >= '" + filters.VencimentoInicio.ToString("yyyy-MM-dd 00:00:00") + "' ";
}
if (filters.VencimentoFim != dt)
{
query = query + " and r.DataVencimento <= '" + filters.VencimentoFim.ToString("yyyy-MM-dd 23:59:59") + "' ";
}
if (filters.PagamentoInicio != dt)
{
query = query + " and r.DataPagamento >= '" + filters.PagamentoInicio.ToString("yyyy-MM-dd 00:00:00") + "' ";
}
if (filters.PagamentoFim != dt)
{
query = query + " and r.DataPagamento <= '" + filters.PagamentoFim.ToString("yyyy-MM-dd 23:59:59") + "' ";
}
if (filters.CategoriaId != null)
{
query = query + " and cf.CategoriaFinanceiraId = '" + filters.CategoriaId + "' ";
}
if (filters.SubCategoriaId != null)
{
query = query + " and sc.SubCategoriaFinanceiraId = '" + filters.SubCategoriaId + "' ";
}
if (filters.Situacao != "")
{
query = query + " and r.Situacao = '" + filters.Situacao + "' ";
}
if (filters.TipoPagamentoId != null)
{
query = query + " and tp.TipoPagamentoId = '" + filters.TipoPagamentoId + "' ";
}
query = query +
"union " +
" select " +
" d.Documento, " +
" d.DataVencimento, " +
" d.DataEmissao, " +
" d.DataPagamento, " +
" d.Valor, " +
" d.ValorTotal, " +
" cf.Tipo, " +
" d.Situacao, " +
" cf.Categoria, " +
" sc.SubCategoria, " +
" tp.Descricao as TipoPagamento, " +
" d.Observacao " +
" from Despesas d " +
" left join SubCategoriaFinanceira sc on d.SubCategoriaFinanceiraId = sc.SubCategoriaFinanceiraId " +
" left join TipoPagamento tp on d.TipoPagamentoId = tp.TipoPagamentoId " +
" inner join CategoriaFinanceira cf on d.CategoriaFinanceiraId = cf.CategoriaFinanceiraId " +
" where 1 = 1 ";
if (filters.Tipo != null && filters.Tipo != "Todos")
{
query = query + " and cf.Tipo = '" + filters.Tipo + "' ";
}
if (filters.EmissaoInicio != dt)
{
query = query + " and d.DataEmissao >= '" + filters.EmissaoInicio.ToString("yyyy-MM-dd 00:00:00") + "' ";
}
if (filters.EmissaoFim != dt)
{
query = query + " and d.DataEmissao <= '" + filters.EmissaoFim.ToString("yyyy-MM-dd 23:59:59") + "' ";
}
if (filters.VencimentoInicio != dt)
{
query = query + " and d.DataVencimento >= '" + filters.VencimentoInicio.ToString("yyyy-MM-dd 00:00:00") + "' ";
}
if (filters.VencimentoFim != dt)
{
query = query + " and d.DataVencimento <= '" + filters.VencimentoFim.ToString("yyyy-MM-dd 23:59:59") + "' ";
}
if (filters.PagamentoInicio != dt)
{
query = query + " and d.DataPagamento >= '" + filters.PagamentoInicio.ToString("yyyy-MM-dd 00:00:00") + "' ";
}
if (filters.PagamentoFim != dt)
{
query = query + " and d.DataPagamento <= '" + filters.PagamentoFim.ToString("yyyy-MM-dd 23:59:59") + "' ";
}
if (filters.CategoriaId != null)
{
query = query + " and cf.CategoriaFinanceiraId = '" + filters.CategoriaId + "' ";
}
if (filters.SubCategoriaId != null)
{
query = query + " and sc.SubCategoriaFinanceiraId = '" + filters.SubCategoriaId + "' ";
}
if (filters.Situacao != "")
{
query = query + " and d.Situacao = '" + filters.Situacao + "' ";
}
if (filters.TipoPagamentoId != null)
{
query = query + " and tp.TipoPagamentoId = '" + filters.TipoPagamentoId + "' ";
}
cn.Open();
var relatorio = cn.Query <RelatorioFinanceiro>(query);
cn.Close();
return(relatorio);
}
}
public IEnumerable <RelatorioFinanceiro> GetRelatorioReceitas(RelatorioFinanceiroSearch filters)
{
using (var cn = Connection)
{
var dt = new DateTime();
var query = @"
select r.*, c.Nome as Cliente, f.Nome as Funcionario, t.Descricao as TipoPagamento, cf.Categoria, sc.SubCategoria, t.Descricao from Receitas r
left join SubCategoriaFinanceira sc on r.SubCategoriaFinanceiraId = sc.SubCategoriaFinanceiraId
left join Cliente c on r.ClienteId = c.ClienteId
left join Funcionarios f on r.FuncionarioId = f.FuncionarioId
left join Tipopagamento t on r.TipoPagamentoId = t.TipoPagamentoId
inner join CategoriaFinanceira cf on r.CategoriaFinanceiraId = cf.CategoriaFinanceiraId
where 1 = 1";
if (filters.EmissaoInicio != dt)
{
query = query + " and r.DataEmissao >= '" + filters.EmissaoInicio.ToString("yyyy-MM-dd 00:00:00") + "' ";
}
if (filters.EmissaoFim != dt)
{
query = query + " and r.DataEmissao <= '" + filters.EmissaoFim.ToString("yyyy-MM-dd 23:59:59") + "' ";
}
if (filters.VencimentoInicio != dt)
{
query = query + " and r.DataVencimento >= '" + filters.VencimentoInicio.ToString("yyyy-MM-dd 00:00:00") + "' ";
}
if (filters.VencimentoFim != dt)
{
query = query + " and r.DataVencimento <= '" + filters.VencimentoFim.ToString("yyyy-MM-dd 23:59:59") + "' ";
}
if (filters.PagamentoInicio != dt)
{
query = query + " and r.DataPagamento >= '" + filters.PagamentoInicio.ToString("yyyy-MM-dd 00:00:00") + "' ";
}
if (filters.PagamentoFim != dt)
{
query = query + " and r.DataPagamento <= '" + filters.PagamentoFim.ToString("yyyy-MM-dd 23:59:59") + "' ";
}
if (filters.CategoriaId != null)
{
query = query + " and cf.CategoriaFinanceiraId = '" + filters.CategoriaId + "' ";
}
if (filters.SubCategoriaId != null)
{
query = query + " and sc.SubCategoriaFinanceiraId = '" + filters.SubCategoriaId + "' ";
}
if (filters.Situacao != "")
{
query = query + " and r.Situacao = '" + filters.Situacao + "' ";
}
if (filters.Cliente != null)
{
query = query + " and (c.Nome like '%" + filters.Cliente + "%' or r.Nome like '%" + filters.Cliente + "%')";
}
if (filters.FuncionarioId != null)
{
query = query + " and f.FuncionarioId = '" + filters.FuncionarioId + "' ";
}
if (filters.TipoPagamentoId != null)
{
query = query + " and r.TipoPagamentoId = '" + filters.TipoPagamentoId + "' ";
}
cn.Open();
var relatorio = cn.Query <RelatorioFinanceiro>(query);
cn.Close();
return(relatorio);
}
}
