diff options
Diffstat (limited to 'Codemerx/Gestor.Infrastructure/Gestor.Infrastructure.Repository.Logic/LancamentoRepository.cs')
| -rw-r--r-- | Codemerx/Gestor.Infrastructure/Gestor.Infrastructure.Repository.Logic/LancamentoRepository.cs | 345 |
1 files changed, 345 insertions, 0 deletions
diff --git a/Codemerx/Gestor.Infrastructure/Gestor.Infrastructure.Repository.Logic/LancamentoRepository.cs b/Codemerx/Gestor.Infrastructure/Gestor.Infrastructure.Repository.Logic/LancamentoRepository.cs new file mode 100644 index 0000000..82f67fc --- /dev/null +++ b/Codemerx/Gestor.Infrastructure/Gestor.Infrastructure.Repository.Logic/LancamentoRepository.cs @@ -0,0 +1,345 @@ +using AutoMapper;
+using Gestor.Infrastructure.Entities.Financeiro;
+using Gestor.Infrastructure.Entities.Generic;
+using Gestor.Infrastructure.Helpers;
+using Gestor.Infrastructure.Mappers;
+using Gestor.Infrastructure.Repository.Generic;
+using Gestor.Infrastructure.Repository.Interface;
+using Gestor.Infrastructure.UnitOfWork.Generic;
+using Gestor.Model.Common;
+using Gestor.Model.Domain.Financeiro;
+using Gestor.Model.Domain.Financeiro.Relatorios;
+using Gestor.Model.Domain.Generic;
+using NHibernate;
+using NHibernate.Connection;
+using NHibernate.Impl;
+using System;
+using System.Collections.Generic;
+using System.Data;
+using System.Data.Common;
+using System.Data.SqlClient;
+using System.Linq;
+using System.Linq.Expressions;
+using System.Runtime.CompilerServices;
+
+namespace Gestor.Infrastructure.Repository.Logic
+{
+ public class LancamentoRepository : GenericRepository<LancamentoDb>, ILancamentoRepository, IGenericRepository<LancamentoDb>
+ {
+ private readonly GenericUnitOfWork _unitOfWork;
+
+ public LancamentoRepository(GenericUnitOfWork unitOfWork) : base(unitOfWork.Session)
+ {
+ this._unitOfWork = unitOfWork;
+ }
+
+ public List<Lancamento> AddRange(List<Lancamento> lancamento)
+ {
+ List<LancamentoDb> lancamentoDbs = ApplicationMapper.Mapper.Map<List<Lancamento>, List<LancamentoDb>>(lancamento);
+ base.AddRange(lancamentoDbs);
+ return ApplicationMapper.Mapper.Map<List<LancamentoDb>, List<Lancamento>>(lancamentoDbs);
+ }
+
+ public bool BancosContasUtilizado(long id)
+ {
+ return base.All().Any<LancamentoDb>((LancamentoDb x) => x.Conta.Id == id);
+ }
+
+ public void Delete(long id)
+ {
+ LancamentoDb lancamentoDb = base.FindEntityById(id);
+ if (lancamentoDb == null)
+ {
+ return;
+ }
+ base.Delete(lancamentoDb);
+ }
+
+ public List<Lancamento> Fechamento(FiltroFinanceiro filtro)
+ {
+ string str = (filtro.Centro == null || filtro.Centro.Count == 0 ? "" : string.Concat(" AND c.idcentro IN (", string.Join<long>(",", filtro.Centro), ")"));
+ string str1 = (filtro.Plano == null || filtro.Plano.Count == 0 ? "" : string.Concat(" AND cp.idcplano IN (", string.Join<long>(",", filtro.Plano), ")"));
+ string str2 = (filtro.Planos == null || filtro.Planos.Count == 0 ? "" : string.Concat(" AND c.idcplanos IN (", string.Join<long>(",", filtro.Planos), ")"));
+ string str3 = (filtro.Conta == null || filtro.Conta.Count == 0 ? "" : string.Concat(" AND l.idconta IN (", string.Join<long>(",", filtro.Conta), ")"));
+ return this.Select(string.Format(" AND {0} >= '{1:yyyy-MM-dd}' AND {2} <= '{3:yyyy-MM-dd}' {4} {5} {6} {7}", new object[] { filtro.Referencia, filtro.Inicio, filtro.Referencia, filtro.Fim, str, str1, str2, str3 }));
+ }
+
+ public Saldo FecharSaldo(Saldo saldo)
+ {
+ object connection;
+ SessionFactoryImpl sessionFactory = this._unitOfWork.Session.SessionFactory as SessionFactoryImpl;
+ decimal num = new decimal();
+ if (sessionFactory != null)
+ {
+ connection = sessionFactory.ConnectionProvider.GetConnection();
+ }
+ else
+ {
+ connection = null;
+ }
+ using (SqlConnection sqlConnection = connection as SqlConnection)
+ {
+ if (sqlConnection != null)
+ {
+ using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
+ {
+ sqlCommand.CommandText = string.Format("SELECT sinal, vlrbaixa FROM lancamen WHERE idconta = {0} AND dtbaixa IS NOT NULL AND dtbaixa >= '{1:yyyy-MM-dd}' AND dtbaixa < '{2:yyyy-MM-dd}' ", saldo.Conta.Id, saldo.DataInicio, saldo.DataFinal);
+ DataTable dataTable = new DataTable();
+ using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter())
+ {
+ sqlDataAdapter.SelectCommand = sqlCommand;
+ sqlDataAdapter.Fill(dataTable);
+ }
+ num = (dataTable.Rows.Count == 0 ? decimal.Zero : dataTable.AsEnumerable().ToList<DataRow>().Sum<DataRow>((DataRow x) => {
+ decimal? nullable;
+ decimal? nullable1;
+ if (x.Field<string>("sinal") == "0")
+ {
+ nullable = x.Field<decimal?>("vlrbaixa");
+ return nullable.GetValueOrDefault();
+ }
+ decimal? nullable2 = x.Field<decimal?>("vlrbaixa");
+ if (nullable2.HasValue)
+ {
+ nullable1 = new decimal?(-nullable2.GetValueOrDefault());
+ }
+ else
+ {
+ nullable = null;
+ nullable1 = nullable;
+ }
+ nullable = nullable1;
+ return nullable.GetValueOrDefault();
+ }));
+ }
+ }
+ }
+ saldo.ValorFinal = new decimal?(saldo.ValorInicio + num);
+ return saldo;
+ }
+
+ public List<Lancamento> Find(DateTime inicio, DateTime fim, StatusLancamento status)
+ {
+ if (status == StatusLancamento.Baixados)
+ {
+ return this.Select(string.Format(" AND CAST(l.dtpre AS DATE) >= '{0:yyyy-MM-dd}' AND CAST(l.dtpre AS DATE) <= '{1:yyyy-MM-dd}' AND l.dtbaixa IS NOT NULL", inicio, fim));
+ }
+ if (status != StatusLancamento.Todos)
+ {
+ return this.Select(string.Format(" AND CAST(l.dtpre AS DATE) >= '{0:yyyy-MM-dd}' AND CAST(l.dtpre AS DATE) <= '{1:yyyy-MM-dd}' AND l.dtbaixa IS NULL", inicio, fim));
+ }
+ return this.Select(string.Format(" AND CAST(l.dtpre AS DATE) >= '{0:yyyy-MM-dd}' AND CAST(l.dtpre AS DATE) <= '{1:yyyy-MM-dd}'", inicio, fim));
+ }
+
+ public List<Lancamento> FindByBaixa(DateTime inicio, DateTime fim)
+ {
+ return this.Select(string.Format(" AND CAST(l.dtbaixa AS DATE) >= '{0:yyyy-MM-dd}' AND CAST(l.dtbaixa AS DATE) <= '{1:yyyy-MM-dd}'", inicio, fim));
+ }
+
+ public List<Lancamento> FindByCodigoBanco(List<string> codigos)
+ {
+ List<string> strs = codigos;
+ List<Lancamento> lancamentos = new List<Lancamento>();
+ while (strs.Count > 0)
+ {
+ List<string> list = strs.Take<string>(2100).ToList<string>();
+ string str = string.Concat(" AND l.CodigoBanco IN ('", string.Join(",", list), "')");
+ lancamentos.AddRange(this.Select(str));
+ strs = (list.Count < 2100 ? new List<string>() : strs.Except<string>(list).ToList<string>());
+ }
+ return lancamentos;
+ }
+
+ public List<ExtratoConta> FindByConta(DateTime inicio, DateTime fim, long id)
+ {
+ return this.Select(string.Format(" AND l.idconta = {0} AND CAST(l.dtbaixa AS DATE) >= '{1:yyyy-MM-dd}' AND CAST(l.dtbaixa AS DATE) <= '{2:yyyy-MM-dd}'", id, inicio, fim)).Select<Lancamento, ExtratoConta>((Lancamento x) => {
+ decimal? valorPago;
+ string nome;
+ decimal zero;
+ ExtratoConta extratoContum = new ExtratoConta()
+ {
+ IdLancamento = x.Id
+ };
+ DateTime? baixa = x.Baixa;
+ extratoContum.Baixa = new DateTime?((baixa.HasValue ? baixa.GetValueOrDefault() : DateTime.MinValue));
+ ControleFinanceiro controle = x.Controle;
+ if (controle != null)
+ {
+ Fornecedor fornecedor = controle.Fornecedor;
+ if (fornecedor != null)
+ {
+ nome = fornecedor.Nome;
+ }
+ else
+ {
+ nome = null;
+ }
+ }
+ else
+ {
+ nome = null;
+ }
+ extratoContum.Fornecedor = nome;
+ extratoContum.Historico = x.Historico;
+ extratoContum.TipoPagamento = new TipoPagamento?(x.TipoPagamento);
+ if (!x.ValorPago.HasValue)
+ {
+ zero = decimal.Zero;
+ }
+ else if (x.Sinal == Sinal.Credito)
+ {
+ valorPago = x.ValorPago;
+ zero = Math.Abs(valorPago.Value);
+ }
+ else
+ {
+ valorPago = x.ValorPago;
+ zero = -Math.Abs(valorPago.Value);
+ }
+ extratoContum.Valor = new decimal?(zero);
+ extratoContum.Sinal = x.Sinal;
+ return extratoContum;
+ }).ToList<ExtratoConta>();
+ }
+
+ public List<Lancamento> FindByControle(long id, StatusLancamento status)
+ {
+ if (status == StatusLancamento.Baixados)
+ {
+ return this.Select(string.Format(" AND l.idcfinan = {0} AND l.dtbaixa IS NOT NULL", id));
+ }
+ if (status != StatusLancamento.Todos)
+ {
+ return this.Select(string.Format(" AND l.idcfinan = {0} AND l.dtbaixa IS NULL", id));
+ }
+ return this.Select(string.Format(" AND l.idcfinan = {0}", id));
+ }
+
+ public Lancamento FindByControle(long id, int parcela)
+ {
+ return this.Select(string.Format(" AND l.idcfinan = {0} AND l.parcela = {1}", id, parcela)).FirstOrDefault<Lancamento>();
+ }
+
+ public List<Lancamento> FindByFornecedor(long id, StatusLancamento status)
+ {
+ if (status == StatusLancamento.Baixados)
+ {
+ return this.Select(string.Format(" AND c.idfornecedor = {0} AND l.dtbaixa IS NOT NULL", id));
+ }
+ if (status != StatusLancamento.Todos)
+ {
+ return this.Select(string.Format(" AND c.idfornecedor = {0} AND l.dtbaixa IS NULL", id));
+ }
+ return this.Select(string.Format(" AND c.idfornecedor = {0}", id));
+ }
+
+ public List<Lancamento> FindByFornecedor(long id, DateTime date, Sinal sinal)
+ {
+ return this.Select(string.Format(" AND c.idfornecedor = {0} AND l.dtpre >= '{1:yyyy-MM-dd}' AND l.dtpre <= '{2:yyyy-MM-dd}' AND l.sinal = {3}", new object[] { id, date, date.AddMonths(1), (int)sinal }));
+ }
+
+ public Lancamento FindById(long id)
+ {
+ LancamentoDb lancamentoDb = base.FindEntityById(id);
+ return ApplicationMapper.Mapper.Map<LancamentoDb, Lancamento>(lancamentoDb);
+ }
+
+ public List<Lancamento> FindByLancamento(DateTime inicio, DateTime fim, StatusLancamento status)
+ {
+ if (status == StatusLancamento.Baixados)
+ {
+ return this.Select(string.Format(" AND CAST(l.datacriacao AS DATE) >= '{0:yyyy-MM-dd}' AND CAST(l.datacriacao AS DATE) <= '{1:yyyy-MM-dd}' AND l.dtbaixa IS NOT NULL", inicio, fim));
+ }
+ if (status != StatusLancamento.Todos)
+ {
+ return this.Select(string.Format(" AND CAST(l.datacriacao AS DATE) >= '{0:yyyy-MM-dd}' AND CAST(l.datacriacao AS DATE) <= '{1:yyyy-MM-dd}' AND l.dtbaixa IS NULL", inicio, fim));
+ }
+ return this.Select(string.Format(" AND CAST(l.datacriacao AS DATE) >= '{0:yyyy-MM-dd}' AND CAST(l.datacriacao AS DATE) <= '{1:yyyy-MM-dd}'", inicio, fim));
+ }
+
+ public List<Lancamento> FindByPagamento(DateTime inicio, DateTime fim)
+ {
+ return this.Select(string.Format(" AND CAST(l.dtpagto AS DATE) >= '{0:yyyy-MM-dd}' AND CAST(l.dtpagto AS DATE) <= '{1:yyyy-MM-dd}'", inicio, fim));
+ }
+
+ public List<Lancamento> FindLancamentosByConta(DateTime inicio, DateTime fim, long id)
+ {
+ return this.Select(string.Format(" AND CAST(l.dtpre AS DATE) >= '{0:yyyy-MM-dd}' AND CAST(l.dtpre AS DATE) <= '{1:yyyy-MM-dd}' AND l.idconta = {2}", inicio, fim, id));
+ }
+
+ public List<Lancamento> FindPersonalizado(DateTime inicio, DateTime fim, StatusLancamento status, FiltroLancamentoData filtrodata)
+ {
+ string referencia = this.GetReferencia(filtrodata);
+ if (status == StatusLancamento.Baixados)
+ {
+ return this.Select(string.Format(" AND {0} >= '{1:yyyy-MM-dd}' AND {2} <= '{3:yyyy-MM-dd}' AND l.dtbaixa IS NOT NULL", new object[] { referencia, inicio, referencia, fim }));
+ }
+ if (status != StatusLancamento.Todos)
+ {
+ return this.Select(string.Format(" AND {0} >= '{1:yyyy-MM-dd}' AND {2} <= '{3:yyyy-MM-dd}' AND l.dtbaixa IS NULL", new object[] { referencia, inicio, referencia, fim }));
+ }
+ return this.Select(string.Format(" AND {0} >= '{1:yyyy-MM-dd}' AND {2} <= '{3:yyyy-MM-dd}'", new object[] { referencia, inicio, referencia, fim }));
+ }
+
+ private string GetReferencia(FiltroLancamentoData filtro)
+ {
+ if (filtro == FiltroLancamentoData.Baixa)
+ {
+ return "CAST(l.dtbaixa AS DATE)";
+ }
+ if (filtro != FiltroLancamentoData.Pagamento)
+ {
+ return "CAST(l.dtpre AS DATE)";
+ }
+ return "CAST(l.dtpagto AS DATE)";
+ }
+
+ public bool HasByFornecedor(long id)
+ {
+ return base.All().Any<LancamentoDb>((LancamentoDb x) => x.Controle.Fornecedor.Id == id);
+ }
+
+ public Lancamento Merge(Lancamento lancamento)
+ {
+ LancamentoDb lancamentoDb = ApplicationMapper.Mapper.Map<Lancamento, LancamentoDb>(lancamento);
+ base.Merge(lancamentoDb);
+ return ApplicationMapper.Mapper.Map<LancamentoDb, Lancamento>(lancamentoDb);
+ }
+
+ public Lancamento SaveOrUpdate(Lancamento lancamento)
+ {
+ LancamentoDb lancamentoDb = ApplicationMapper.Mapper.Map<Lancamento, LancamentoDb>(lancamento);
+ this.SaveOrUpdate(lancamentoDb);
+ return ApplicationMapper.Mapper.Map<LancamentoDb, Lancamento>(lancamentoDb);
+ }
+
+ private List<Lancamento> Select(string condition)
+ {
+ object connection;
+ DataTable dataTable = new DataTable();
+ SessionFactoryImpl sessionFactory = this._unitOfWork.Session.SessionFactory as SessionFactoryImpl;
+ if (sessionFactory != null)
+ {
+ connection = sessionFactory.ConnectionProvider.GetConnection();
+ }
+ else
+ {
+ connection = null;
+ }
+ using (SqlConnection sqlConnection = connection as SqlConnection)
+ {
+ using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
+ {
+ AuxiliarFinanceiro.Criar(sqlCommand);
+ sqlCommand.CommandText = string.Concat("SELECT DISTINCT c.idfornecedor, f.nome, f.Ativo, c.idfornecedor, c.idcplanos, c.idcentro, c.historico as historicocfinan, c.parcela as parcelas, cp.idcplano, l.* FROM lancamen l INNER JOIN cfinan c on c.idcfinan = l.idcfinan LEFT OUTER JOIN cplanos cp on c.idcplanos = cp.idcplanos LEFT OUTER JOIN fornecedor f on f.idfornecedor = c.idfornecedor WHERE 1=1 ", condition);
+ using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter())
+ {
+ sqlDataAdapter.SelectCommand = sqlCommand;
+ sqlDataAdapter.Fill(dataTable);
+ }
+ }
+ }
+ return CustomMap.MapLancamento(dataTable);
+ }
+ }
+}
\ No newline at end of file |