path: root/Codemerx/Gestor.Infrastructure/Gestor.Infrastructure.Repository.Logic/LancamentoRepository.cs

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);
		}
	}
}