using AutoMapper;
using Gestor.Infrastructure.Entities.Generic;
using Gestor.Infrastructure.Entities.Seguros;
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.Generic;
using Gestor.Model.Domain.Seguros;
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 CriticaApoliceRepository : GenericRepository<CriticaApoliceDb>, ICriticaApoliceRepository, IGenericRepository<CriticaApoliceDb>
	{
		private readonly GenericUnitOfWork _unitOfWork;

		private const string _commandoCritica = "SELECT i.idimportacao as id, i.iddocumento, i.idusuario, i.critica, i.dataimportacao, i.tipo, d.contrato as apolice, d.proposta, c.idcontrole, c.idciaseg, c.idramo, cl.idcliente, cl.nome as cliente, cl.documentoprincipal, cl.cgccpf as documento, cl.rne, cl.cei, d.vigencia1, d.vigencia2, i.critica, i.idusuariocritica, i.datacritica FROM importacao i INNER JOIN documento d on d.iddocumento = i.iddocumento INNER JOIN controle c on c.idcontrole = d.idcontrole INNER JOIN cliente cl on cl.idcliente = c.idcliente WHERE";

		private const string _commandoCriticaVinculo = "SELECT DISTINCT i.idimportacao as id, i.iddocumento, i.idusuario, i.critica, i.dataimportacao, i.tipo, d.contrato as apolice, d.proposta, c.idcontrole, c.idciaseg, c.idramo, cl.idcliente, cl.nome as cliente, cl.documentoprincipal, cl.cgccpf as documento, cl.rne, cl.cei, d.vigencia1, d.vigencia2, i.critica, i.idusuariocritica, i.datacritica FROM importacao i INNER JOIN documento d on d.iddocumento = i.iddocumento INNER JOIN controle c on c.idcontrole = d.idcontrole INNER JOIN cliente cl on cl.idcliente = c.idcliente LEFT OUTER JOIN vendedorparcela vp on vp.iddocumento = d.iddocumento WHERE";

		public CriticaApoliceRepository(GenericUnitOfWork unitOfWork) : base(unitOfWork.Session)
		{
			this._unitOfWork = unitOfWork;
		}

		public List<Usuario> BuscarUsuarioCritica(DateTime inicio, DateTime fim, bool criticado = false, long idempresa = 0L)
		{
			List<Usuario> usuarios;
			object connection;
			string str = (criticado ? " AND i.critica = '1'" : " AND i.critica = '0'");
			string str1 = (idempresa == 0 ? "" : string.Format(" AND u.idempresa = {0}", idempresa));
			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())
				{
					Auxiliar.CriarAuxiliarUsuario(sqlCommand);
					sqlCommand.CommandText = string.Concat("SELECT DISTINCT  i.idusuario FROM importacao i INNER JOIN documento d on d.iddocumento = i.iddocumento INNER JOIN usuario u on u.idusuario = i.idusuario ", string.Format("WHERE CAST(i.dataimportacao AS DATE) >= '{0:yyyy-MM-dd}' AND CAST(i.dataimportacao AS DATE) <= '{1:yyyy-MM-dd}' AND (d.excluido IS NULL OR d.excluido != '1') {2} {3}", new object[] { inicio, fim, str1, str }));
					using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter())
					{
						sqlDataAdapter.SelectCommand = sqlCommand;
						sqlDataAdapter.Fill(dataTable);
					}
					if (dataTable.Rows.Count != 0)
					{
						goto Label0;
					}
					else
					{
						usuarios = new List<Usuario>();
					}
				}
			}
			return usuarios;
			List<long> list = dataTable.AsEnumerable().Select<DataRow, long>((DataRow x) => x.Field<long>("idusuario")).ToList<long>();
			return (
				from x in Auxiliar.Usuarios
				where list.Contains(x.Id)
				select x).ToList<Usuario>();
		}

		public void Delete(long id)
		{
			CriticaApoliceDb criticaApoliceDb = base.All().FirstOrDefault<CriticaApoliceDb>((CriticaApoliceDb x) => x.Id == id);
			if (criticaApoliceDb == null)
			{
				return;
			}
			base.Delete(criticaApoliceDb);
		}

		public List<CriticaApolice> Find(long usuario, DateTime inicio, DateTime fim, long vendedorCorretoraId, bool criticado = false, long idempresa = 0L, List<VendedorUsuario> vinculos = null, bool vendedorEmBranco = false, bool vinculoVendedorCritica = false)
		{
			Condicao condicao = new Condicao()
			{
				Campo = "i.idusuario",
				Valores = usuario.CriarValor<long>()
			};
			Condicao condicao1 = new Condicao()
			{
				Campo = "i.critica",
				Valores = (criticado ? "1".CriarValor<string>() : "0".CriarValor<string>())
			};
			Condicao condicao2 = new Condicao()
			{
				Campo = "c.idempresa",
				Valores = idempresa.CriarValor<long>()
			};
			List<Condicao> condicaos = new List<Condicao>()
			{
				new Condicao()
				{
					Campo = "CAST(i.dataimportacao AS DATE)",
					Valores = inicio.CriarValor<DateTime>(),
					Operador = Operador.MaiorEIgual
				},
				new Condicao()
				{
					Campo = "CAST(i.dataimportacao AS DATE)",
					Valores = fim.CriarValor<DateTime>(),
					Operador = Operador.MenorEIgual
				},
				new Condicao()
				{
					Campo = " d.excluido",
					Valores = null,
					Grupo = 1,
					Operacao = Operacao.Or
				},
				new Condicao()
				{
					Campo = " d.excluido",
					Valores = "1".CriarValor<string>(),
					Grupo = 1,
					Operador = Operador.Diferente,
					Operacao = Operacao.Or
				}
			};
			string str = (vinculos == null || vinculos.Count <= 0 ? "SELECT i.idimportacao as id, i.iddocumento, i.idusuario, i.critica, i.dataimportacao, i.tipo, d.contrato as apolice, d.proposta, c.idcontrole, c.idciaseg, c.idramo, cl.idcliente, cl.nome as cliente, cl.documentoprincipal, cl.cgccpf as documento, cl.rne, cl.cei, d.vigencia1, d.vigencia2, i.critica, i.idusuariocritica, i.datacritica FROM importacao i INNER JOIN documento d on d.iddocumento = i.iddocumento INNER JOIN controle c on c.idcontrole = d.idcontrole INNER JOIN cliente cl on cl.idcliente = c.idcliente WHERE" : "SELECT DISTINCT i.idimportacao as id, i.iddocumento, i.idusuario, i.critica, i.dataimportacao, i.tipo, d.contrato as apolice, d.proposta, c.idcontrole, c.idciaseg, c.idramo, cl.idcliente, cl.nome as cliente, cl.documentoprincipal, cl.cgccpf as documento, cl.rne, cl.cei, d.vigencia1, d.vigencia2, i.critica, i.idusuariocritica, i.datacritica FROM importacao i INNER JOIN documento d on d.iddocumento = i.iddocumento INNER JOIN controle c on c.idcontrole = d.idcontrole INNER JOIN cliente cl on cl.idcliente = c.idcliente LEFT OUTER JOIN vendedorparcela vp on vp.iddocumento = d.iddocumento WHERE");
			if (vinculos != null && vinculos.Count > 0)
			{
				if (vendedorEmBranco)
				{
					condicaos.Add(new Condicao()
					{
						Campo = "vp.idvendedor",
						Valores = null,
						Operacao = Operacao.Or,
						Grupo = 3
					});
				}
				condicaos.Add(new Condicao()
				{
					Campo = "vp.idvendedor",
					Valores = (vinculoVendedorCritica & vendedorEmBranco ? vendedorCorretoraId.CriarValor<long>() : (
						from x in vinculos
						select x.Vendedor.Id).ToList<long>().CriarValor<long>()),
					Grupo = 3
				});
			}
			if (usuario > (long)0)
			{
				condicaos.Add(condicao);
			}
			if (idempresa > (long)0)
			{
				condicaos.Add(condicao2);
			}
			condicaos.Add(condicao1);
			return this.Select(condicaos.CreateParameters(0), str);
		}

		public List<CriticaApolice> Find(DateTime inicio, DateTime fim, long idempresa = 0L)
		{
			Condicao condicao = new Condicao()
			{
				Campo = "c.idempresa",
				Valores = idempresa.CriarValor<long>()
			};
			List<Condicao> condicaos = new List<Condicao>()
			{
				new Condicao()
				{
					Campo = "CAST(i.dataimportacao AS DATE)",
					Valores = inicio.CriarValor<DateTime>(),
					Operador = Operador.MaiorEIgual
				},
				new Condicao()
				{
					Campo = "CAST(i.dataimportacao AS DATE)",
					Valores = fim.CriarValor<DateTime>(),
					Operador = Operador.MenorEIgual
				},
				new Condicao()
				{
					Campo = " d.excluido",
					Valores = null,
					Grupo = 1,
					Operacao = Operacao.Or
				},
				new Condicao()
				{
					Campo = " d.excluido",
					Valores = "1".CriarValor<string>(),
					Grupo = 1,
					Operador = Operador.Diferente,
					Operacao = Operacao.Or
				}
			};
			if (idempresa > (long)0)
			{
				condicaos.Add(condicao);
			}
			return this.Select(condicaos.CreateParameters(0), "SELECT i.idimportacao as id, i.iddocumento, i.idusuario, i.critica, i.dataimportacao, i.tipo, d.contrato as apolice, d.proposta, c.idcontrole, c.idciaseg, c.idramo, cl.idcliente, cl.nome as cliente, cl.documentoprincipal, cl.cgccpf as documento, cl.rne, cl.cei, d.vigencia1, d.vigencia2, i.critica, i.idusuariocritica, i.datacritica FROM importacao i INNER JOIN documento d on d.iddocumento = i.iddocumento INNER JOIN controle c on c.idcontrole = d.idcontrole INNER JOIN cliente cl on cl.idcliente = c.idcliente WHERE");
		}

		public List<CriticaApolice> Find(long id)
		{
			List<CriticaApoliceDb> list = this._unitOfWork.Session.CreateQuery(string.Format("from CriticaApoliceDb WHERE iddocumento = {0} AND (Critica IS NULL OR Critica != '1')", id)).List<CriticaApoliceDb>().ToList<CriticaApoliceDb>();
			return ApplicationMapper.Mapper.Map<List<CriticaApoliceDb>, List<CriticaApolice>>(list);
		}

		public CriticaApolice FindByApolice(long id)
		{
			CriticaApoliceDb criticaApoliceDb = base.All().FirstOrDefault<CriticaApoliceDb>((CriticaApoliceDb x) => x.Documento.Id == id);
			return ApplicationMapper.Mapper.Map<CriticaApoliceDb, CriticaApolice>(criticaApoliceDb);
		}

		public CriticaApolice FindById(long id)
		{
			CriticaApoliceDb criticaApoliceDb = base.FindEntityById(id);
			return ApplicationMapper.Mapper.Map<CriticaApoliceDb, CriticaApolice>(criticaApoliceDb);
		}

		public List<CriticaApolice> FindDuo(long id, DateTime vigencia)
		{
			return this.Select((new List<Condicao>()
			{
				new Condicao()
				{
					Campo = " c.idcliente",
					Valores = id.CriarValor<long>()
				},
				new Condicao()
				{
					Campo = " CAST(d.vigencia1 AS DATE)",
					Valores = vigencia.CriarValor<DateTime>(),
					Operador = Operador.Igual
				},
				new Condicao()
				{
					Campo = " i.critica",
					Valores = 1.CriarValor<int>(),
					Operador = Operador.Diferente
				},
				new Condicao()
				{
					Campo = " d.excluido",
					Valores = null,
					Grupo = 1,
					Operacao = Operacao.Or
				},
				new Condicao()
				{
					Campo = " d.excluido",
					Valores = "1".CriarValor<string>(),
					Grupo = 1,
					Operador = Operador.Diferente,
					Operacao = Operacao.Or
				},
				new Condicao()
				{
					Campo = " c.idramo",
					Valores = 5.CriarValor<int>(),
					Grupo = 2,
					Operacao = Operacao.Or
				},
				new Condicao()
				{
					Campo = " c.idramo",
					Valores = 1.CriarValor<int>(),
					Grupo = 2,
					Operacao = Operacao.Or
				}
			}).CreateParameters(0), "SELECT i.idimportacao as id, i.iddocumento, i.idusuario, i.critica, i.dataimportacao, i.tipo, d.contrato as apolice, d.proposta, c.idcontrole, c.idciaseg, c.idramo, cl.idcliente, cl.nome as cliente, cl.documentoprincipal, cl.cgccpf as documento, cl.rne, cl.cei, d.vigencia1, d.vigencia2, i.critica, i.idusuariocritica, i.datacritica FROM importacao i INNER JOIN documento d on d.iddocumento = i.iddocumento INNER JOIN controle c on c.idcontrole = d.idcontrole INNER JOIN cliente cl on cl.idcliente = c.idcliente WHERE");
		}

		public List<CriticaApolice> FindImport(long id)
		{
			List<CriticaApoliceDb> list = this._unitOfWork.Session.CreateQuery(string.Format("from CriticaApoliceDb WHERE iddocumento = {0}", id)).List<CriticaApoliceDb>().ToList<CriticaApoliceDb>();
			return ApplicationMapper.Mapper.Map<List<CriticaApoliceDb>, List<CriticaApolice>>(list);
		}

		public CriticaApolice Merge(CriticaApolice documento)
		{
			CriticaApoliceDb criticaApoliceDb = ApplicationMapper.Mapper.Map<CriticaApolice, CriticaApoliceDb>(documento);
			base.Merge(criticaApoliceDb);
			return ApplicationMapper.Mapper.Map<CriticaApoliceDb, CriticaApolice>(criticaApoliceDb);
		}

		public CriticaApolice SaveOrUpdate(CriticaApolice documento)
		{
			CriticaApoliceDb criticaApoliceDb = ApplicationMapper.Mapper.Map<CriticaApolice, CriticaApoliceDb>(documento);
			this.SaveOrUpdate(criticaApoliceDb);
			return ApplicationMapper.Mapper.Map<CriticaApoliceDb, CriticaApolice>(criticaApoliceDb);
		}

		private List<CriticaApolice> Select(SqlQueryCondition condition, string command)
		{
			return this._unitOfWork.Select(condition, command, "").MapImportacao();
		}
	}
}