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

using AutoMapper;
using Gestor.Common.Validation;
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.Relatorios;
using Gestor.Model.Domain.Seguros;
using NHibernate;
using NHibernate.Connection;
using NHibernate.Impl;
using NHibernate.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Runtime.CompilerServices;

namespace Gestor.Infrastructure.Repository.Logic
{
	public class SinistroRepository : GenericRepository<SinistroDb>, ISinistroRepository, IGenericRepository<SinistroDb>
	{
		private readonly GenericUnitOfWork _unitOfWork;

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

		public void Delete(long id)
		{
			base.Delete(base.FindEntityById(id));
		}

		public List<Sinistro> Find(string numero)
		{
			List<SinistroDb> list = (
				from x in base.All()
				where x.Numero.Normalized().Contains(numero.Normalized()) && numero.Normalized().Contains(x.Numero.Normalized())
				select x).ToList<SinistroDb>();
			return ApplicationMapper.Mapper.Map<List<SinistroDb>, List<Sinistro>>(list);
		}

		public List<Sinistro> FindByControleId(long id)
		{
			List<SinistroDb> list = (
				from x in base.All()
				where x.ControleSinistro.Id == id
				select x).ToList<SinistroDb>();
			List<Sinistro> sinistros = ApplicationMapper.Mapper.Map<List<SinistroDb>, List<Sinistro>>(list);
			sinistros.ForEach((Sinistro x) => {
				x.SinistroAuto = (new SinistroAutoRepository(this._unitOfWork)).FindBySinistroId(x.Id);
				x.SinistroVida = (new SinistroVidaRepository(this._unitOfWork)).FindBySinistroId(x.Id);
			});
			return sinistros;
		}

		public List<Sinistro> FindByData(Filtros filtro, bool pendentes = false)
		{
			object connection;
			string str = "CAST(cs.datasinistro AS DATE)";
			string referencia = filtro.Referencia;
			if (referencia == "DATA LIQUIDAÇÃO")
			{
				str = "CAST(s.dataliquidacao AS DATE)";
			}
			else if (referencia == "DATA RECLAMAÇÃO")
			{
				str = "CAST(s.datareclamacao AS DATE)";
			}
			List<Condicao> condicaos2 = filtro.CriarCondicao(str);
			List<Condicao> list = (
				from x in condicaos2
				where x.Campo == str
				select x).ToList<Condicao>();
			if (pendentes)
			{
				list.Add(new Condicao()
				{
					Campo = "CAST(s.dataliquidacao AS DATE)",
					Valores = null
				});
			}
			string str1 = (filtro.TipoVendedor == null || filtro.TipoVendedor.Count == 0 ? "OUTER APPLY (SELECT TOP 1 idvendedor FROM vendedorparcela vp WHERE vp.iddocumento = d.iddocumento AND vp.idtipovendedor = 1) vp" : string.Concat("INNER JOIN vendedorparcela vp ON vp.iddocumento = d.iddocumento AND vp.idtipovendedor IN (", string.Join<long>(",", 
				from v in filtro.TipoVendedor
				select v), ")"));
			SessionFactoryImpl sessionFactory = this._unitOfWork.Session.SessionFactory as SessionFactoryImpl;
			DataTable dataTable = new DataTable();
			DataTable dataTable1 = new DataTable();
			DataTable dataTable2 = new DataTable();
			DataTable dataTable3 = new DataTable();
			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.CommandTimeout = 15000;
						string str2 = "SELECT DISTINCT cs.iditem as id, cs.idcontrolesinistro, s.idsinistro, cs.datasinistro as data, s.numsinistro as numero, s.datareclamacao as reclamacao, s.itemsinistrado as item, s.motivo, s.tiposinistro, s.valor as valor, s.vlrorcado as valororcado, s.vlrliberado as valorliberado, s.vlrpago as valorpago, s.vlrfranquia as valorfranquia, s.vlrsalvado as valorsalvado, s.dataliquidacao as liquidacao, s.idstatussinistro, s.StatusPersonalizado, s.auxiliar, s.usuariocriacao, s.usuarioalteracao, s.datacriacao, s.dataalteracao FROM sinistro s INNER JOIN controlesinistro cs on cs.idcontrolesinistro = s.idcontrolesinistro WHERE ";
						dataTable = sqlCommand.Select(list.CreateParameters(0), str2, "");
						if (dataTable.Rows.Count != 0)
						{
							Auxiliar.CriarAuxiliar(sqlCommand, false);
							List<Condicao> list1 = condicaos2.Where<Condicao>((Condicao x) => {
								if (x.Campo == this.referencia)
								{
									return false;
								}
								return x.Campo != "CAST(s.dataliquidacao AS DATE)";
							}).ToList<Condicao>();
							list1.Add(new Condicao()
							{
								Campo = "d.excluido",
								Valores = null,
								Grupo = 1,
								Operacao = Operacao.Or
							});
							list1.Add(new Condicao()
							{
								Campo = "d.excluido",
								Valores = "0".CriarValor<string>(),
								Grupo = 1,
								Operacao = Operacao.Or
							});
							string str3 = string.Concat("SELECT i.iditem as id, cl.nome as cliente, cl.MalaDireta, cl.idcliente, c.idcontrole, c.idempresa, i.iddocumento, d.contrato as apolice, d.aditamento as endosso, d.vigencia1, d.vigencia2, c.idramo, c.idciaseg as idseguradora, c.idproduto, vp.idvendedor, d.idestipulante, d.datacontrole, d.idstatus, d.pasta, cl.pasta as pastacliente, cl.cgccpf as cpfcnpj FROM item 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 ", str1, " WHERE ");
							dataTable.AsEnumerable().ForEach<DataRow>((DataRow sinistro) => {
								List<Condicao> condicaos = new List<Condicao>()
								{
									new Condicao()
									{
										Campo = "i.iditem",
										Valores = sinistro.Field<long>("id").CriarValor<long>()
									}
								};
								condicaos.AddRange(list1);
								List<Condicao> condicaos1 = new List<Condicao>()
								{
									new Condicao()
									{
										Campo = "idsinistro",
										Valores = sinistro.Field<long>("idsinistro").CriarValor<long>()
									}
								};
								dataTable1.Merge(sqlCommand.Select(condicaos.CreateParameters(0), str3, ""));
								dataTable2.Merge(sqlCommand.Select(condicaos1.CreateParameters(0), "SELECT * FROM sinistroauto WHERE", ""));
								dataTable3.Merge(sqlCommand.Select(condicaos1.CreateParameters(0), "SELECT * FROM sinistrovida WHERE", ""));
							});
						}
						else
						{
							return new List<Sinistro>();
						}
					}
				}
			}
			List<long> nums = dataTable1.AsEnumerable().Select<DataRow, long>((DataRow x) => x.Field<long>("id")).ToList<long>();
			List<Sinistro> sinistros = new List<Sinistro>();
			dataTable.AsEnumerable().Where<DataRow>((DataRow x) => nums.Contains(x.Field<long>("id"))).ForEach<DataRow>((DataRow x) => {
				TipoPerda? nullable;
				object sinistroAuto;
				object sinistroVida;
				TipoPerda? nullable1;
				Parceiro parceiro;
				Parceiro parceiro1;
				TipoPerda? nullable2;
				DataRow dataRow = dataTable1.Select(string.Format("id = {0}", x.Field<long>("id"))).FirstOrDefault<DataRow>();
				if (dataRow == null)
				{
					return;
				}
				DataRow dataRow1 = dataTable2.AsEnumerable().ToList<DataRow>().FirstOrDefault<DataRow>((DataRow a) => a.Field<long>("idsinistro") == x.Field<long>("idsinistro"));
				if (dataRow1 == null)
				{
					sinistroAuto = null;
				}
				else
				{
					sinistroAuto = new SinistroAuto();
					((DomainBase)sinistroAuto).Id = dataRow1.Field<long>("idsinistroauto");
					parceiro = (dataRow1.Field<object>("idparceirofunilaria") == null ? null : Auxiliar.Parceiros.Find((Parceiro a) => a.Id == dataRow1.Field<long>("idparceirofunilaria")));
					((SinistroAuto)sinistroAuto).ParceiroFunilaria = parceiro;
					parceiro1 = (dataRow1.Field<object>("idparceiromecanica") == null ? null : Auxiliar.Parceiros.Find((Parceiro a) => a.Id == dataRow1.Field<long>("idparceiromecanica")));
					((SinistroAuto)sinistroAuto).ParceiroMecanica = parceiro1;
					if (dataRow1.Field<object>("idtipoperda") == null)
					{
						nullable = null;
						nullable2 = nullable;
					}
					else
					{
						nullable2 = new TipoPerda?((TipoPerda)int.Parse(dataRow1.Field<object>("idtipoperda").ToString()));
					}
					((SinistroAuto)sinistroAuto).TipoPerda = nullable2;
					((SinistroAuto)sinistroAuto).Email = dataRow1.Field<string>("email");
					((SinistroAuto)sinistroAuto).Telefone = string.Concat(dataRow1.Field<string>("ddd"), " ", dataRow1.Field<string>("telefone"));
				}
				SinistroAuto sinistroAuto1 = (SinistroAuto)sinistroAuto;
				DataRow dataRow2 = dataTable3.AsEnumerable().ToList<DataRow>().FirstOrDefault<DataRow>((DataRow a) => a.Field<long>("idsinistro") == x.Field<long>("idsinistro"));
				if (dataRow2 == null)
				{
					sinistroVida = null;
				}
				else
				{
					sinistroVida = new SinistroVida();
					((DomainBase)sinistroVida).Id = dataRow2.Field<long>("idsinistrovida");
					if (dataRow2.Field<object>("tipoperda") == null)
					{
						nullable = null;
						nullable1 = nullable;
					}
					else
					{
						nullable1 = new TipoPerda?((TipoPerda)int.Parse(dataRow2.Field<object>("tipoperda").ToString()));
					}
					((SinistroVida)sinistroVida).TipoPerda = nullable1;
					((SinistroVida)sinistroVida).Beneficiario = dataRow2.Field<string>("beneficiario");
				}
				SinistroVida sinistroVida1 = (SinistroVida)sinistroVida;
				Sinistro sinistro1 = new Sinistro()
				{
					Id = x.Field<long>("idsinistro"),
					Numero = x.Field<string>("numero"),
					ItemSinistrado = x.Field<string>("item"),
					Motivo = x.Field<string>("motivo"),
					TipoSinistro = new TipoSinistro?((TipoSinistro)Enum.Parse(typeof(TipoSinistro), x.Field<object>("tiposinistro").ToString())),
					DataReclamacao = new DateTime?((x.Field<object>("reclamacao") != null ? x.Field<DateTime>("reclamacao") : (x.Field<object>("data") != null ? x.Field<DateTime>("data") : DateTime.MinValue))),
					Valor = (x.Field<object>("valor") == null ? decimal.Zero : x.Field<decimal>("valor")),
					ValorOrcado = (x.Field<object>("valororcado") == null ? decimal.Zero : x.Field<decimal>("valororcado")),
					ValorLiberado = (x.Field<object>("valorliberado") == null ? decimal.Zero : x.Field<decimal>("valorliberado")),
					ValorSalvado = new decimal?((x.Field<object>("valorsalvado") == null ? decimal.Zero : x.Field<decimal>("valorsalvado"))),
					ValorFranquia = (x.Field<object>("valorfranquia") == null ? decimal.Zero : x.Field<decimal>("valorfranquia")),
					ValorPago = (x.Field<object>("valorpago") == null ? decimal.Zero : x.Field<decimal>("valorpago")),
					DataLiquidacao = x.Field<DateTime?>("liquidacao"),
					StatusSinistro = new StatusSinistro?((StatusSinistro)((x.Field<object>("idstatussinistro") == null ? 0 : (StatusSinistro)x.Field<object>("idstatussinistro")))),
					SinistroAuto = sinistroAuto1,
					SinistroVida = sinistroVida1,
					StatusPersonalizado = x.Field<string>("StatusPersonalizado"),
					Auxiliar = x.Field<string>("auxiliar"),
					IdUsuarioCriacao = (x.Field<object>("usuariocriacao") == null ? (long)0 : x.Field<long>("usuariocriacao")),
					DataCriacao = x.Field<DateTime?>("datacriacao"),
					IdUsuarioAlteracao = (x.Field<object>("usuarioalteracao") == null ? (long)0 : x.Field<long>("usuarioalteracao")),
					DataAlteracao = x.Field<DateTime?>("dataalteracao")
				};
				ControleSinistro controleSinistro = new ControleSinistro()
				{
					Id = x.Field<long>("idcontrolesinistro"),
					DataSinistro = new DateTime?((x.Field<object>("data") != null ? x.Field<DateTime>("data") : DateTime.MinValue))
				};
				Item item = new Item()
				{
					Id = x.Field<long>("id")
				};
				Documento documento = new Documento()
				{
					Id = dataRow.Field<long>("iddocumento"),
					Controle = new Controle()
					{
						Id = dataRow.Field<long>("idcontrole"),
						IdEmpresa = dataRow.Field<long>("idempresa"),
						Cliente = new Cliente()
						{
							Id = dataRow.Field<long>("idcliente"),
							Nome = dataRow.Field<string>("cliente"),
							Pasta = dataRow.Field<string>("pastacliente"),
							Documento = dataRow.Field<string>("cpfcnpj"),
							MalaDireta = new bool?(dataRow.Field<bool?>("MalaDireta").GetValueOrDefault(true))
						},
						Seguradora = Auxiliar.Seguradoras.Find((Seguradora p) => p.Id == dataRow.Field<long>("idseguradora")),
						Ramo = Auxiliar.Ramos.Find((Ramo p) => p.Id == dataRow.Field<long>("idramo")),
						Produto = (dataRow.Field<object>("idproduto") != null ? Auxiliar.Produtos.Find((Produto p) => p.Id == dataRow.Field<long>("idproduto")) : null)
					},
					Apolice = dataRow.Field<string>("apolice"),
					Endosso = dataRow.Field<string>("endosso"),
					VendedorPrincipal = (dataRow.Field<object>("idvendedor") != null ? Auxiliar.Vendedores.Find((Vendedor p) => p.Id == dataRow.Field<long>("idvendedor")) : null),
					Estipulante1 = (dataRow.Field<object>("idestipulante") != null ? Auxiliar.Estipulantes.Find((Estipulante p) => p.Id == dataRow.Field<long>("idestipulante")) : null),
					DataControle = dataRow.Field<DateTime?>("datacontrole"),
					Status = (dataRow.Field<object>("idstatus") != null ? Auxiliar.StatusApolice.Find((Status p) => p.Id == dataRow.Field<long>("idstatus")) : null)
				};
				DateTime? nullable3 = dataRow.Field<DateTime?>("vigencia1");
				documento.Vigencia1 = (nullable3.HasValue ? nullable3.GetValueOrDefault() : DateTime.MinValue);
				documento.Vigencia2 = dataRow.Field<DateTime?>("vigencia2");
				documento.Pasta = dataRow.Field<string>("Pasta");
				item.Documento = documento;
				controleSinistro.Item = item;
				sinistro1.ControleSinistro = controleSinistro;
				sinistros.Add(sinistro1);
			});
			return sinistros;
		}

		public Sinistro FindById(long id)
		{
			SinistroDb sinistroDb = base.FindEntityById(id);
			if (sinistroDb == null)
			{
				return null;
			}
			Sinistro sinistro = ApplicationMapper.Mapper.Map<SinistroDb, Sinistro>(sinistroDb);
			sinistro.SinistroAuto = (new SinistroAutoRepository(this._unitOfWork)).FindBySinistroId(sinistro.Id);
			sinistro.SinistroVida = (new SinistroVidaRepository(this._unitOfWork)).FindBySinistroId(sinistro.Id);
			return sinistro;
		}

		public List<Sinistro> FindByItemId(long id)
		{
			IEnumerable<long> nums = 
				from cs in (new ControleSinistroRepository(this._unitOfWork)).FindByIdItem(id)
				select cs.Id;
			return (
				from s in base.All().ToList<SinistroDb>()
				where nums.Contains<long>(s.ControleSinistro.Id)
				select new Sinistro()
				{
					Id = s.Id,
					Descricao = (string.IsNullOrEmpty(s.Descricao) ? "" : s.Descricao),
					DataReclamacao = s.DataReclamacao,
					Observacao = (string.IsNullOrEmpty(s.Observacao) ? "" : s.Observacao),
					Valor = s.Valor,
					ValorPago = s.ValorPago,
					Numero = s.Numero,
					ControleSinistro = new ControleSinistro()
					{
						DataSinistro = s.ControleSinistro.DataSinistro
					},
					ItemSinistrado = s.ItemSinistrado,
					SinistroAuto = (new SinistroAutoRepository(this._unitOfWork)).FindBySinistroId(s.Id),
					SinistroVida = (new SinistroVidaRepository(this._unitOfWork)).FindBySinistroId(s.Id)
				}).ToList<Sinistro>();
		}

		public List<Sinistro> FindNumeroSinistro(string numero)
		{
			return this.Select((new List<Condicao>()
			{
				new Condicao()
				{
					Campo = "s.numsinistro",
					Valores = null,
					Operador = Operador.Diferente
				},
				new Condicao()
				{
					Campo = "s.numsinistro",
					Valores = numero.CriarValor<string>()
				}
			}).CreateParameters(0));
		}

		public Sinistro Merge(Sinistro sinistro)
		{
			SinistroDb sinistroDb = ApplicationMapper.Mapper.Map<Sinistro, SinistroDb>(sinistro);
			base.Merge(sinistroDb);
			return ApplicationMapper.Mapper.Map<SinistroDb, Sinistro>(sinistroDb);
		}

		public Sinistro SaveOrUpdate(Sinistro sinistro)
		{
			SinistroDb sinistroDb = ApplicationMapper.Mapper.Map<Sinistro, SinistroDb>(sinistro);
			this.SaveOrUpdate(sinistroDb);
			return ApplicationMapper.Mapper.Map<SinistroDb, Sinistro>(sinistroDb);
		}

		private List<Sinistro> Select(SqlQueryCondition condition)
		{
			DataTable dataTable = this._unitOfWork.Select(condition, "SELECT * FROM sinistro s INNER JOIN controlesinistro cs on cs.idcontrolesinistro = s.idcontrolesinistro WHERE ", "");
			if (dataTable != null && dataTable.Rows.Count == 0)
			{
				return new List<Sinistro>();
			}
			List<long> list = (
				from x in dataTable.AsEnumerable().ToList<DataRow>()
				select x.Field<long>("iditem")).ToList<long>();
			List<Condicao> condicaos = new List<Condicao>()
			{
				new Condicao()
				{
					Campo = "i.iditem",
					Valores = list.CriarValor<long>()
				},
				new Condicao()
				{
					Campo = "d.excluido",
					Valores = null,
					Grupo = 1,
					Operacao = Operacao.Or
				},
				new Condicao()
				{
					Campo = "d.excluido",
					Valores = "0".CriarValor<string>(),
					Grupo = 1,
					Operacao = Operacao.Or
				}
			};
			DataTable dataTable1 = this._unitOfWork.Select(condicaos.CreateParameters(0), "SELECT i.iditem, d.iddocumento, ISNULL(d.contrato, '') as apolice, c.idcontrole, cl.idcliente, cl.nome FROM item 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 ", "");
			return dataTable.MapSinistro(dataTable1);
		}
	}
}