diff options
Diffstat (limited to 'Codemerx/Gestor.Infrastructure/Gestor.Infrastructure.Repository.Logic/ClienteRepository.cs')
| -rw-r--r-- | Codemerx/Gestor.Infrastructure/Gestor.Infrastructure.Repository.Logic/ClienteRepository.cs | 3102 |
1 files changed, 3102 insertions, 0 deletions
diff --git a/Codemerx/Gestor.Infrastructure/Gestor.Infrastructure.Repository.Logic/ClienteRepository.cs b/Codemerx/Gestor.Infrastructure/Gestor.Infrastructure.Repository.Logic/ClienteRepository.cs new file mode 100644 index 0000000..f07399f --- /dev/null +++ b/Codemerx/Gestor.Infrastructure/Gestor.Infrastructure.Repository.Logic/ClienteRepository.cs @@ -0,0 +1,3102 @@ +using AutoMapper;
+using Gestor.Common.Helpers;
+using Gestor.Common.Validation;
+using Gestor.Infrastructure.Entities.Ferramentas;
+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.Common;
+using Gestor.Model.Domain.Ferramentas;
+using Gestor.Model.Domain.Generic;
+using Gestor.Model.Domain.Relatorios;
+using Gestor.Model.Domain.Relatorios.Classificacao;
+using Gestor.Model.Domain.Relatorios.ClientesAtivosInativos;
+using Gestor.Model.Domain.Relatorios.Dashboard;
+using Gestor.Model.Domain.Seguros;
+using Gestor.Model.Relatorios;
+using NHibernate;
+using NHibernate.Connection;
+using NHibernate.Impl;
+using NHibernate.Util;
+using System;
+using System.Collections;
+using System.Collections.Generic;
+using System.Collections.ObjectModel;
+using System.Data;
+using System.Data.Common;
+using System.Data.SqlClient;
+using System.Diagnostics;
+using System.Linq;
+using System.Linq.Expressions;
+using System.Runtime.CompilerServices;
+using System.Text;
+using System.Text.RegularExpressions;
+using System.Threading.Tasks;
+
+namespace Gestor.Infrastructure.Repository.Logic
+{
+ public class ClienteRepository : GenericRepository<ClienteDb>, IClienteRepository, IGenericRepository<ClienteDb>
+ {
+ private readonly GenericUnitOfWork _unitOfWork;
+
+ private const string ComandoEndereco = "SELECT DISTINCT idcliend, cep, cidade, uf as estado, idcliente as id, endereco, numero, comp_end as complemento FROM cliendereco WHERE ";
+
+ private const string ComandoTelefone = "SELECT DISTINCT idclitelefone, ddd, fone, idcliente as id FROM clitelefone WHERE ";
+
+ private const string ComandoEmail = "SELECT DISTINCT idclimail, email, idcliente as id FROM climail WHERE ";
+
+ private const string ComandoCliente = "SELECT idcliente as id, idempresa, nome, cgccpf as documento, cei, rne, documentoprincipal, anotacoes, dtnasc as nascimento, idprofissao, VENCHABILI as vencimento, sexo, RendaMensal, pasta, MalaDireta, clidesde, ESTADOCIVI FROM cliente WHERE ";
+
+ private const string ComandoClienteVinculo = "SELECT DISTINCT cl.idcliente as id, cl.idempresa, cl.nome, cl.cgccpf as documento, cl.cei, cl.rne, cl.documentoprincipal, cl.anotacoes, cl.dtnasc as nascimento, cl.idprofissao,cl.VENCHABILI as vencimento, cl.sexo, cl.RendaMensal, cl.pasta, cl.MalaDireta, cl.clidesde, cl.ESTADOCIVI FROM cliente cl inner join controle co on co.idcliente = cl.idcliente inner join documento d on d.idcontrole = co.idcontrole inner join vendedorparcela vp on vp.iddocumento = d.iddocumento inner join vendedorusuario vu on vu.idvendedor = vp.idvendedor WHERE ";
+
+ public ClienteRepository(GenericUnitOfWork unitOfWork) : base(unitOfWork.Session)
+ {
+ this._unitOfWork = unitOfWork;
+ }
+
+ public void AddCentralSegurado(long id, bool update)
+ {
+ try
+ {
+ this._unitOfWork.Session.BeginTransaction();
+ this._unitOfWork.Session.CreateQuery("update ClienteDb c set c.EstaNaCentralSegurado = :update where c.Id = :updatedid").SetParameter<long>("updatedid", id).SetParameter<bool>("update", update).ExecuteUpdate();
+ this._unitOfWork.Commit();
+ }
+ catch
+ {
+ }
+ }
+
+ public async Task<IEnumerable<ClientesAtivosInativos>> BuscaClientes(bool completo, long idempresa = 0L)
+ {
+ IEnumerable<ClientesAtivosInativos> clientesAtivosInativos;
+ DateTime? nullable;
+ object connection;
+ string str;
+ bool flag;
+ DateTime? nullable1;
+ string str1;
+ DateTime? nullable2;
+ string nome;
+ string str2;
+ string str3;
+ decimal num;
+ DateTime? nullable3;
+ ClienteRepository.u003cu003ec__DisplayClass34_0 variable;
+ Func<Profissao, bool> func = null;
+ Action<Gestor.Model.Relatorios.Vendedor> action2 = null;
+ List<ClientesAtivosInativos> clientesAtivosInativos1 = new List<ClientesAtivosInativos>();
+ IEnumerable<Gestor.Model.Relatorios.Documento> documentos = await this.DocumentosPeriodo(completo);
+ IEnumerable<Gestor.Model.Relatorios.ClienteEndereco> clienteEnderecos = await this.EnderecoClientes();
+ IEnumerable<Gestor.Model.Relatorios.ClienteTelefone> clienteTelefones = await this.TelefonesClientes();
+ IEnumerable<Gestor.Model.Relatorios.ClienteEmail> clienteEmails = await this.EmailsClientes();
+ Array values = Enum.GetValues(typeof(EstadoCivil));
+ Regex regex = new Regex("[^\\d]");
+ IEnumerable<EstipulanteCliente> estipulanteClientes = await this.EstipulanteCliente();
+ SessionFactoryImpl sessionFactory = this._unitOfWork.Session.SessionFactory as SessionFactoryImpl;
+ if (sessionFactory != null)
+ {
+ connection = sessionFactory.ConnectionProvider.GetConnection();
+ }
+ else
+ {
+ connection = null;
+ }
+ using (SqlConnection sqlConnection = connection as SqlConnection)
+ {
+ if (sqlConnection != null)
+ {
+ using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
+ {
+ Auxiliar.CriarAuxiliar(sqlCommand, true);
+ SqlCommand sqlCommand1 = sqlCommand;
+ str = (idempresa > (long)0 ? "AND idempresa = @empresa" : "");
+ sqlCommand1.CommandText = string.Concat("SELECT c.idcliente,c.nome,c.maladireta,c.dtnasc,c.sexo,c.estadocivi,c.venchabili,c.idprofissao,c.pasta,c.cgccpf,c.rendamensal, c.nomesocialrg, c.clidesde, IIF((SELECT TOP 1 idvinculo FROM clivinculo cv WHERE cv.idcliente1 = c.idcliente OR cv.idcliente2 = c.idcliente) IS NOT NULL, 'SIM', 'NÃO') Parentesco FROM cliente c WHERE (excluido IS NULL OR excluido = 0) ", str);
+ sqlCommand.Parameters.Add("@empresa", SqlDbType.BigInt).Value = idempresa;
+ sqlCommand.CommandTimeout = 1000;
+ using (SqlDataReader sqlDataReader = await sqlCommand.ExecuteReaderAsync())
+ {
+ while (true)
+ {
+ if (!await sqlDataReader.ReadAsync())
+ {
+ break;
+ }
+ long item = (long)sqlDataReader["idcliente"];
+ string str4 = sqlDataReader["nome"].ToString();
+ string str5 = sqlDataReader["nomesocialrg"].ToString();
+ flag = (sqlDataReader.IsDBNull(sqlDataReader.GetOrdinal("maladireta")) ? true : (bool)sqlDataReader["maladireta"]);
+ bool flag1 = flag;
+ if (sqlDataReader.IsDBNull(sqlDataReader.GetOrdinal("dtnasc")))
+ {
+ nullable = null;
+ nullable1 = nullable;
+ }
+ else
+ {
+ nullable1 = new DateTime?((DateTime)sqlDataReader["dtnasc"]);
+ }
+ DateTime? nullable4 = nullable1;
+ str1 = (sqlDataReader.IsDBNull(sqlDataReader.GetOrdinal("sexo")) ? "" : sqlDataReader["sexo"].ToString());
+ string str6 = str1;
+ Sexo sexo = Sexo.Masculino;
+ if (!string.IsNullOrWhiteSpace(str6) && !Enum.TryParse<Sexo>(str6, out sexo))
+ {
+ str6 = "";
+ }
+ IEnumerable<Gestor.Model.Relatorios.Documento> id =
+ from d in documentos
+ where d.Controle.Cliente.Id == item
+ select d;
+ Gestor.Model.Relatorios.ClienteEndereco clienteEndereco = clienteEnderecos.FirstOrDefault<Gestor.Model.Relatorios.ClienteEndereco>((Gestor.Model.Relatorios.ClienteEndereco e) => e.Cliente.Id == item);
+ if (clienteEndereco == null)
+ {
+ clienteEndereco = new Gestor.Model.Relatorios.ClienteEndereco();
+ }
+ Gestor.Model.Relatorios.ClienteEndereco clienteEndereco1 = clienteEndereco;
+ IEnumerable<Gestor.Model.Relatorios.ClienteTelefone> id1 =
+ from f in clienteTelefones
+ where f.Cliente.Id == item
+ select f;
+ IEnumerable<Gestor.Model.Relatorios.ClienteEmail> clienteEmails1 =
+ from e in clienteEmails
+ where e.Cliente.Id == item
+ select e;
+ string str7 = "";
+ string str8 = "";
+ string str9 = "NÃO";
+ string str10 = "";
+ string description = "";
+ int? nullable5 = null;
+ string str11 = "";
+ if (nullable4.HasValue)
+ {
+ str10 = nullable4.Value.ToString("dd/MM");
+ nullable5 = new int?(this.CalculaIdade(nullable4.Value));
+ }
+ List<string> strs = new List<string>();
+ id.ForEach<Gestor.Model.Relatorios.Documento>((Gestor.Model.Relatorios.Documento d) => {
+ str9 = "SIM";
+ if (completo)
+ {
+ int? ordem = d.Ordem;
+ if (ordem.GetValueOrDefault() > 0 & ordem.HasValue)
+ {
+ return;
+ }
+ strs.Add(d.Controle.Ramo.Nome ?? "");
+ IList<Gestor.Model.Relatorios.Vendedor> vendedores = d.Vendedores;
+ if (vendedores == null)
+ {
+ return;
+ }
+ Action<Gestor.Model.Relatorios.Vendedor> u003cu003e9_10 = action2;
+ if (u003cu003e9_10 == null)
+ {
+ Action<Gestor.Model.Relatorios.Vendedor> action = (Gestor.Model.Relatorios.Vendedor v) => {
+ if (!str7.Contains(v.Nome))
+ {
+ if (str7.Length > 0)
+ {
+ str7 = string.Concat(str7, " | ");
+ }
+ str7 = string.Concat(str7, v.Nome);
+ }
+ };
+ Action<Gestor.Model.Relatorios.Vendedor> action1 = action;
+ action2 = action;
+ u003cu003e9_10 = action1;
+ }
+ vendedores.ForEach<Gestor.Model.Relatorios.Vendedor>(u003cu003e9_10);
+ }
+ });
+ List<Gestor.Model.Domain.Seguros.ClienteTelefone> clienteTelefones1 = new List<Gestor.Model.Domain.Seguros.ClienteTelefone>();
+ id1.ForEach<Gestor.Model.Relatorios.ClienteTelefone>((Gestor.Model.Relatorios.ClienteTelefone f) => clienteTelefones1.Add(new Gestor.Model.Domain.Seguros.ClienteTelefone()
+ {
+ Prefixo = f.Prefixo,
+ Numero = f.Numero
+ }));
+ if (!sqlDataReader.IsDBNull(sqlDataReader.GetOrdinal("estadocivi")))
+ {
+ foreach (object value in values)
+ {
+ if (value.GetOldValue<object>() != sqlDataReader["estadocivi"].ToString())
+ {
+ continue;
+ }
+ description = ((EstadoCivil)value).GetDescription<EstadoCivil>();
+ break;
+ }
+ }
+ if (completo && strs.Count > 0)
+ {
+ List<string> strs1 = strs;
+ IEnumerable<IGrouping<string, string>> groupings =
+ from g in strs1
+ group g by g;
+ str8 = string.Join(" | ",
+ from r in groupings
+ select string.Format("{0}: {1}", r.Key, r.Count<string>()));
+ }
+ IEnumerable<EstipulanteCliente> idCliente =
+ from x in estipulanteClientes
+ where x.IdCliente == item
+ select x;
+ str11 = string.Join(" | ",
+ from x in idCliente
+ select x.NomeEstipulante);
+ ClientesAtivosInativos clientesAtivosInativo = new ClientesAtivosInativos();
+ Gestor.Model.Domain.Seguros.Cliente cliente = new Gestor.Model.Domain.Seguros.Cliente()
+ {
+ Id = item,
+ Nome = str4,
+ NomeSocialRg = str5,
+ MalaDireta = new bool?(flag1),
+ Telefones = new ObservableCollection<Gestor.Model.Domain.Seguros.ClienteTelefone>(clienteTelefones1)
+ };
+ clientesAtivosInativo.EntidadeCliente = cliente;
+ clientesAtivosInativo.Id = item;
+ clientesAtivosInativo.Nome = str4;
+ clientesAtivosInativo.Nascimento = nullable4;
+ clientesAtivosInativo.Aniversario = str10;
+ clientesAtivosInativo.Idade = nullable5;
+ clientesAtivosInativo.Documento = sqlDataReader["cgccpf"].ToString();
+ if (sqlDataReader.IsDBNull(sqlDataReader.GetOrdinal("venchabili")))
+ {
+ nullable = null;
+ nullable2 = nullable;
+ }
+ else
+ {
+ nullable2 = new DateTime?((DateTime)sqlDataReader["venchabili"]);
+ }
+ clientesAtivosInativo.VencimentoCnh = nullable2;
+ clientesAtivosInativo.Vendedor = str7;
+ clientesAtivosInativo.Ramo = str8;
+ List<Gestor.Model.Domain.Seguros.ClienteTelefone> clienteTelefones2 = clienteTelefones1;
+ clientesAtivosInativo.Telefone = string.Join(" | ",
+ from f in clienteTelefones2
+ select string.Concat(f.Prefixo, " ", f.Numero));
+ IEnumerable<Gestor.Model.Relatorios.ClienteEmail> clienteEmails2 = clienteEmails1;
+ clientesAtivosInativo.Email = string.Join(" | ",
+ from e in clienteEmails2
+ select e.Email);
+ clientesAtivosInativo.Ativo = str9;
+ if (sqlDataReader.IsDBNull(sqlDataReader.GetOrdinal("idprofissao")))
+ {
+ nome = "";
+ }
+ else
+ {
+ List<Profissao> profissoes = Auxiliar.Profissoes;
+ Func<Profissao, bool> func1 = func;
+ if (func1 == null)
+ {
+ Func<Profissao, bool> id2 = (Profissao p) => p.Id == (long)this.rd["idprofissao"];
+ Func<Profissao, bool> func2 = id2;
+ func = id2;
+ func1 = func2;
+ }
+ Profissao profissao = profissoes.FirstOrDefault<Profissao>(func1);
+ if (profissao != null)
+ {
+ nome = profissao.Nome;
+ }
+ else
+ {
+ nome = null;
+ }
+ }
+ clientesAtivosInativo.Profissao = nome;
+ clientesAtivosInativo.Cep = clienteEndereco1.Cep;
+ clientesAtivosInativo.Endereco = clienteEndereco1.Endereco;
+ clientesAtivosInativo.Numero = clienteEndereco1.Numero;
+ clientesAtivosInativo.Complemento = clienteEndereco1.Complemento;
+ clientesAtivosInativo.Cidade = clienteEndereco1.Cidade;
+ clientesAtivosInativo.Estado = clienteEndereco1.Estado;
+ clientesAtivosInativo.Bairro = clienteEndereco1.Bairro;
+ clientesAtivosInativo.PastaCliente = sqlDataReader["pasta"].ToString();
+ clientesAtivosInativo.RecebeEmail = (flag1 ? "SIM" : "NÃO");
+ if (sqlDataReader.IsDBNull(sqlDataReader.GetOrdinal("cgccpf")))
+ {
+ str2 = "FÍSICA";
+ }
+ else
+ {
+ str2 = (regex.Replace(sqlDataReader["cgccpf"].ToString(), string.Empty).Length > 11 ? "JURÍDICA" : "FÍSICA");
+ }
+ clientesAtivosInativo.TipoPessoa = str2;
+ clientesAtivosInativo.EstadoCivil = description;
+ str3 = (string.IsNullOrWhiteSpace(str6) ? "" : sexo.GetDescription<Sexo>());
+ clientesAtivosInativo.Sexo = str3;
+ num = (sqlDataReader.IsDBNull(sqlDataReader.GetOrdinal("rendamensal")) ? decimal.Zero : (decimal)sqlDataReader["rendamensal"]);
+ clientesAtivosInativo.RendaMensal = num;
+ if (sqlDataReader.IsDBNull(sqlDataReader.GetOrdinal("clidesde")))
+ {
+ nullable = null;
+ nullable3 = nullable;
+ }
+ else
+ {
+ nullable3 = new DateTime?((DateTime)sqlDataReader["clidesde"]);
+ }
+ clientesAtivosInativo.ClienteDesde = nullable3;
+ clientesAtivosInativo.Estipulantes = str11;
+ clientesAtivosInativo.Parentesco = sqlDataReader["Parentesco"].ToString();
+ clientesAtivosInativos1.Add(clientesAtivosInativo);
+ }
+ }
+ }
+ sqlCommand = null;
+ }
+ else
+ {
+ clientesAtivosInativos = null;
+ variable = null;
+ clientesAtivosInativos1 = null;
+ documentos = null;
+ clienteEnderecos = null;
+ clienteTelefones = null;
+ clienteEmails = null;
+ values = null;
+ regex = null;
+ estipulanteClientes = null;
+ return clientesAtivosInativos;
+ }
+ }
+ sqlConnection = null;
+ clientesAtivosInativos = clientesAtivosInativos1;
+ variable = null;
+ clientesAtivosInativos1 = null;
+ documentos = null;
+ clienteEnderecos = null;
+ clienteTelefones = null;
+ clienteEmails = null;
+ values = null;
+ regex = null;
+ estipulanteClientes = null;
+ return clientesAtivosInativos;
+ }
+
+ public async Task<IEnumerable<ClientesAtivosInativos>> BuscaClientesVinculo(bool completo, List<VendedorUsuario> vinculo, long idempresa = 0L)
+ {
+ IEnumerable<ClientesAtivosInativos> clientesAtivosInativos;
+ DateTime? nullable;
+ object connection;
+ string str;
+ bool flag;
+ DateTime? nullable1;
+ string str1;
+ DateTime? nullable2;
+ string nome;
+ string str2;
+ string str3;
+ decimal num;
+ DateTime? nullable3;
+ ClienteRepository.u003cu003ec__DisplayClass43_0 variable;
+ Func<Profissao, bool> func = null;
+ Action<Gestor.Model.Relatorios.Vendedor> action2 = null;
+ List<ClientesAtivosInativos> clientesAtivosInativos1 = new List<ClientesAtivosInativos>();
+ IEnumerable<Gestor.Model.Relatorios.Documento> documentos = await this.DocumentosPeriodo(completo);
+ IEnumerable<Gestor.Model.Relatorios.ClienteEndereco> clienteEnderecos = await this.EnderecoClientes();
+ IEnumerable<Gestor.Model.Relatorios.ClienteTelefone> clienteTelefones = await this.TelefonesClientes();
+ IEnumerable<Gestor.Model.Relatorios.ClienteEmail> clienteEmails = await this.EmailsClientes();
+ Array values = Enum.GetValues(typeof(EstadoCivil));
+ Regex regex = new Regex("[^\\d]");
+ IEnumerable<EstipulanteCliente> estipulanteClientes = await this.EstipulanteCliente();
+ List<VendedorUsuario> vendedorUsuarios = vinculo;
+ List<long> list = (
+ from v in vendedorUsuarios
+ select v.Id).ToList<long>();
+ string[] array = (
+ from id in list
+ select id.ToString()).ToArray<string>();
+ string str4 = string.Join(",", array);
+ SessionFactoryImpl sessionFactory = this._unitOfWork.Session.SessionFactory as SessionFactoryImpl;
+ if (sessionFactory != null)
+ {
+ connection = sessionFactory.ConnectionProvider.GetConnection();
+ }
+ else
+ {
+ connection = null;
+ }
+ using (SqlConnection sqlConnection = connection as SqlConnection)
+ {
+ if (sqlConnection != null)
+ {
+ using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
+ {
+ Auxiliar.CriarAuxiliar(sqlCommand, true);
+ SqlCommand sqlCommand1 = sqlCommand;
+ string[] strArrays = new string[] { "SELECT DISTINCT c.idcliente,c.nome,c.maladireta,c.dtnasc,c.sexo,c.estadocivi,c.venchabili,c.idprofissao,c.pasta,c.cgccpf,c.rendamensal, c.nomesocialrg, c.clidesde, \r\n IIF((SELECT TOP 1 idvinculo FROM clivinculo cv WHERE cv.idcliente1 = c.idcliente OR cv.idcliente2 = c.idcliente) IS NOT NULL, 'SIM', 'NÃO') Parentesco FROM cliente c \r\n INNER JOIN controle co on co.idcliente = c.idcliente \r\n INNER JOIN documento d on d.idcontrole = co.idcontrole \r\n INNER JOIN vendedorparcela vp on vp.IdDocumento = d.IdDocumento \r\n INNER JOIN vendedorusuario vu on vu.idvendedor = vp.idvendedor \r\n WHERE (c.excluido IS NULL OR c.excluido = 0) ", null, null, null, null };
+ str = (idempresa > (long)0 ? " AND c.idempresa = @empresa" : "");
+ strArrays[1] = str;
+ strArrays[2] = " \r\n AND vu.idvendedorusuario in (";
+ strArrays[3] = str4;
+ strArrays[4] = ")";
+ sqlCommand1.CommandText = string.Concat(strArrays);
+ sqlCommand.Parameters.Add("@empresa", SqlDbType.BigInt).Value = idempresa;
+ sqlCommand.CommandTimeout = 1000;
+ using (SqlDataReader sqlDataReader = await sqlCommand.ExecuteReaderAsync())
+ {
+ while (true)
+ {
+ if (!await sqlDataReader.ReadAsync())
+ {
+ break;
+ }
+ long item = (long)sqlDataReader["idcliente"];
+ string str5 = sqlDataReader["nome"].ToString();
+ string str6 = sqlDataReader["nomesocialrg"].ToString();
+ flag = (sqlDataReader.IsDBNull(sqlDataReader.GetOrdinal("maladireta")) ? true : (bool)sqlDataReader["maladireta"]);
+ bool flag1 = flag;
+ if (sqlDataReader.IsDBNull(sqlDataReader.GetOrdinal("dtnasc")))
+ {
+ nullable = null;
+ nullable1 = nullable;
+ }
+ else
+ {
+ nullable1 = new DateTime?((DateTime)sqlDataReader["dtnasc"]);
+ }
+ DateTime? nullable4 = nullable1;
+ str1 = (sqlDataReader.IsDBNull(sqlDataReader.GetOrdinal("sexo")) ? "" : sqlDataReader["sexo"].ToString());
+ string str7 = str1;
+ Sexo sexo = Sexo.Masculino;
+ if (!string.IsNullOrWhiteSpace(str7) && !Enum.TryParse<Sexo>(str7, out sexo))
+ {
+ str7 = "";
+ }
+ IEnumerable<Gestor.Model.Relatorios.Documento> documentos1 =
+ from d in documentos
+ where d.Controle.Cliente.Id == item
+ select d;
+ Gestor.Model.Relatorios.ClienteEndereco clienteEndereco = clienteEnderecos.FirstOrDefault<Gestor.Model.Relatorios.ClienteEndereco>((Gestor.Model.Relatorios.ClienteEndereco e) => e.Cliente.Id == item);
+ if (clienteEndereco == null)
+ {
+ clienteEndereco = new Gestor.Model.Relatorios.ClienteEndereco();
+ }
+ Gestor.Model.Relatorios.ClienteEndereco clienteEndereco1 = clienteEndereco;
+ IEnumerable<Gestor.Model.Relatorios.ClienteTelefone> clienteTelefones1 =
+ from f in clienteTelefones
+ where f.Cliente.Id == item
+ select f;
+ IEnumerable<Gestor.Model.Relatorios.ClienteEmail> clienteEmails1 =
+ from e in clienteEmails
+ where e.Cliente.Id == item
+ select e;
+ string str8 = "";
+ string str9 = "";
+ string str10 = "NÃO";
+ string str11 = "";
+ string description = "";
+ int? nullable5 = null;
+ string str12 = "";
+ if (nullable4.HasValue)
+ {
+ str11 = nullable4.Value.ToString("dd/MM");
+ nullable5 = new int?(this.CalculaIdade(nullable4.Value));
+ }
+ List<string> strs = new List<string>();
+ documentos1.ForEach<Gestor.Model.Relatorios.Documento>((Gestor.Model.Relatorios.Documento d) => {
+ str10 = "SIM";
+ if (completo)
+ {
+ int? ordem = d.Ordem;
+ if (ordem.GetValueOrDefault() > 0 & ordem.HasValue)
+ {
+ return;
+ }
+ strs.Add(d.Controle.Ramo.Nome ?? "");
+ IList<Gestor.Model.Relatorios.Vendedor> vendedores = d.Vendedores;
+ if (vendedores == null)
+ {
+ return;
+ }
+ Action<Gestor.Model.Relatorios.Vendedor> u003cu003e9_12 = action2;
+ if (u003cu003e9_12 == null)
+ {
+ Action<Gestor.Model.Relatorios.Vendedor> action = (Gestor.Model.Relatorios.Vendedor v) => {
+ if (!str8.Contains(v.Nome))
+ {
+ if (str8.Length > 0)
+ {
+ str8 = string.Concat(str8, " | ");
+ }
+ str8 = string.Concat(str8, v.Nome);
+ }
+ };
+ Action<Gestor.Model.Relatorios.Vendedor> action1 = action;
+ action2 = action;
+ u003cu003e9_12 = action1;
+ }
+ vendedores.ForEach<Gestor.Model.Relatorios.Vendedor>(u003cu003e9_12);
+ }
+ });
+ List<Gestor.Model.Domain.Seguros.ClienteTelefone> clienteTelefones2 = new List<Gestor.Model.Domain.Seguros.ClienteTelefone>();
+ clienteTelefones1.ForEach<Gestor.Model.Relatorios.ClienteTelefone>((Gestor.Model.Relatorios.ClienteTelefone f) => clienteTelefones2.Add(new Gestor.Model.Domain.Seguros.ClienteTelefone()
+ {
+ Prefixo = f.Prefixo,
+ Numero = f.Numero
+ }));
+ if (!sqlDataReader.IsDBNull(sqlDataReader.GetOrdinal("estadocivi")))
+ {
+ foreach (object value in values)
+ {
+ if (value.GetOldValue<object>() != sqlDataReader["estadocivi"].ToString())
+ {
+ continue;
+ }
+ description = ((EstadoCivil)value).GetDescription<EstadoCivil>();
+ break;
+ }
+ }
+ if (completo && strs.Count > 0)
+ {
+ List<string> strs1 = strs;
+ IEnumerable<IGrouping<string, string>> groupings =
+ from g in strs1
+ group g by g;
+ str9 = string.Join(" | ",
+ from r in groupings
+ select string.Format("{0}: {1}", r.Key, r.Count<string>()));
+ }
+ IEnumerable<EstipulanteCliente> idCliente =
+ from x in estipulanteClientes
+ where x.IdCliente == item
+ select x;
+ str12 = string.Join(" | ",
+ from x in idCliente
+ select x.NomeEstipulante);
+ ClientesAtivosInativos clientesAtivosInativo = new ClientesAtivosInativos();
+ Gestor.Model.Domain.Seguros.Cliente cliente = new Gestor.Model.Domain.Seguros.Cliente()
+ {
+ Id = item,
+ Nome = str5,
+ NomeSocialRg = str6,
+ MalaDireta = new bool?(flag1),
+ Telefones = new ObservableCollection<Gestor.Model.Domain.Seguros.ClienteTelefone>(clienteTelefones2)
+ };
+ clientesAtivosInativo.EntidadeCliente = cliente;
+ clientesAtivosInativo.Id = item;
+ clientesAtivosInativo.Nome = str5;
+ clientesAtivosInativo.Nascimento = nullable4;
+ clientesAtivosInativo.Aniversario = str11;
+ clientesAtivosInativo.Idade = nullable5;
+ clientesAtivosInativo.Documento = sqlDataReader["cgccpf"].ToString();
+ if (sqlDataReader.IsDBNull(sqlDataReader.GetOrdinal("venchabili")))
+ {
+ nullable = null;
+ nullable2 = nullable;
+ }
+ else
+ {
+ nullable2 = new DateTime?((DateTime)sqlDataReader["venchabili"]);
+ }
+ clientesAtivosInativo.VencimentoCnh = nullable2;
+ clientesAtivosInativo.Vendedor = str8;
+ clientesAtivosInativo.Ramo = str9;
+ List<Gestor.Model.Domain.Seguros.ClienteTelefone> clienteTelefones3 = clienteTelefones2;
+ clientesAtivosInativo.Telefone = string.Join(" | ",
+ from f in clienteTelefones3
+ select string.Concat(f.Prefixo, " ", f.Numero));
+ IEnumerable<Gestor.Model.Relatorios.ClienteEmail> clienteEmails2 = clienteEmails1;
+ clientesAtivosInativo.Email = string.Join(" | ",
+ from e in clienteEmails2
+ select e.Email);
+ clientesAtivosInativo.Ativo = str10;
+ if (sqlDataReader.IsDBNull(sqlDataReader.GetOrdinal("idprofissao")))
+ {
+ nome = "";
+ }
+ else
+ {
+ List<Profissao> profissoes = Auxiliar.Profissoes;
+ Func<Profissao, bool> func1 = func;
+ if (func1 == null)
+ {
+ Func<Profissao, bool> func2 = (Profissao p) => p.Id == (long)this.rd["idprofissao"];
+ Func<Profissao, bool> func3 = func2;
+ func = func2;
+ func1 = func3;
+ }
+ Profissao profissao = profissoes.FirstOrDefault<Profissao>(func1);
+ if (profissao != null)
+ {
+ nome = profissao.Nome;
+ }
+ else
+ {
+ nome = null;
+ }
+ }
+ clientesAtivosInativo.Profissao = nome;
+ clientesAtivosInativo.Cep = clienteEndereco1.Cep;
+ clientesAtivosInativo.Endereco = clienteEndereco1.Endereco;
+ clientesAtivosInativo.Numero = clienteEndereco1.Numero;
+ clientesAtivosInativo.Complemento = clienteEndereco1.Complemento;
+ clientesAtivosInativo.Cidade = clienteEndereco1.Cidade;
+ clientesAtivosInativo.Estado = clienteEndereco1.Estado;
+ clientesAtivosInativo.Bairro = clienteEndereco1.Bairro;
+ clientesAtivosInativo.PastaCliente = sqlDataReader["pasta"].ToString();
+ clientesAtivosInativo.RecebeEmail = (flag1 ? "SIM" : "NÃO");
+ if (sqlDataReader.IsDBNull(sqlDataReader.GetOrdinal("cgccpf")))
+ {
+ str2 = "FÍSICA";
+ }
+ else
+ {
+ str2 = (regex.Replace(sqlDataReader["cgccpf"].ToString(), string.Empty).Length > 11 ? "JURÍDICA" : "FÍSICA");
+ }
+ clientesAtivosInativo.TipoPessoa = str2;
+ clientesAtivosInativo.EstadoCivil = description;
+ str3 = (string.IsNullOrWhiteSpace(str7) ? "" : sexo.GetDescription<Sexo>());
+ clientesAtivosInativo.Sexo = str3;
+ num = (sqlDataReader.IsDBNull(sqlDataReader.GetOrdinal("rendamensal")) ? decimal.Zero : (decimal)sqlDataReader["rendamensal"]);
+ clientesAtivosInativo.RendaMensal = num;
+ if (sqlDataReader.IsDBNull(sqlDataReader.GetOrdinal("clidesde")))
+ {
+ nullable = null;
+ nullable3 = nullable;
+ }
+ else
+ {
+ nullable3 = new DateTime?((DateTime)sqlDataReader["clidesde"]);
+ }
+ clientesAtivosInativo.ClienteDesde = nullable3;
+ clientesAtivosInativo.Estipulantes = str12;
+ clientesAtivosInativo.Parentesco = sqlDataReader["Parentesco"].ToString();
+ clientesAtivosInativos1.Add(clientesAtivosInativo);
+ }
+ }
+ }
+ sqlCommand = null;
+ }
+ else
+ {
+ clientesAtivosInativos = null;
+ variable = null;
+ clientesAtivosInativos1 = null;
+ documentos = null;
+ clienteEnderecos = null;
+ clienteTelefones = null;
+ clienteEmails = null;
+ values = null;
+ regex = null;
+ estipulanteClientes = null;
+ return clientesAtivosInativos;
+ }
+ }
+ sqlConnection = null;
+ clientesAtivosInativos = clientesAtivosInativos1;
+ variable = null;
+ clientesAtivosInativos1 = null;
+ documentos = null;
+ clienteEnderecos = null;
+ clienteTelefones = null;
+ clienteEmails = null;
+ values = null;
+ regex = null;
+ estipulanteClientes = null;
+ return clientesAtivosInativos;
+ }
+
+ public DateTime? BuscaNascimeto(long id)
+ {
+ DateTime? nullable;
+ DateTime dateTime;
+ object connection;
+ string str;
+ SessionFactoryImpl sessionFactory = this._unitOfWork.Session.SessionFactory as SessionFactoryImpl;
+ if (sessionFactory != null)
+ {
+ connection = sessionFactory.ConnectionProvider.GetConnection();
+ }
+ else
+ {
+ connection = null;
+ }
+ using (SqlConnection sqlConnection = connection as SqlConnection)
+ {
+ if (sqlConnection != null)
+ {
+ List<Condicao> condicaos = new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "idcliente",
+ Valores = id.CriarValor<long>()
+ }
+ };
+ using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
+ {
+ sqlCommand.CommandTimeout = 15000;
+ object obj = sqlCommand.Select(condicaos.CreateParameters(0), "SELECT c.dtnasc FROM cliente c WHERE", "").AsEnumerable().ToList<DataRow>().FirstOrDefault<DataRow>().Field<object>("dtnasc");
+ if (obj != null)
+ {
+ str = obj.ToString();
+ }
+ else
+ {
+ str = null;
+ }
+ if (!DateTime.TryParse(str, out dateTime))
+ {
+ return null;
+ }
+ else
+ {
+ nullable = new DateTime?(dateTime);
+ }
+ }
+ }
+ else
+ {
+ nullable = null;
+ nullable = nullable;
+ }
+ }
+ return nullable;
+ }
+
+ public async Task<List<Prospectar>> BuscaProspeccoes(Filtros filtro, long idEmpresa)
+ {
+ int? nullable;
+ int num;
+ object connection;
+ int? nullable1;
+ List<Prospectar> prospectars = new List<Prospectar>();
+ filtro.IdEmpresa = idEmpresa;
+ num = (filtro.FiltrarDocumento == "AMBOS" ? -1 : (int)(!(filtro.FiltrarDocumento == "CNPJ")));
+ int num1 = num;
+ List<Condicao> condicaos = new List<Condicao>();
+ Condicao condicao = new Condicao()
+ {
+ Campo = "d.excluido",
+ Valores = null,
+ Grupo = 1,
+ Operacao = Operacao.Or
+ };
+ condicaos.Add(condicao);
+ Condicao condicao1 = new Condicao()
+ {
+ Campo = "d.excluido",
+ Valores = "0".CriarValor<string>(),
+ Grupo = 1,
+ Operacao = Operacao.Or
+ };
+ condicaos.Add(condicao1);
+ Condicao condicao2 = new Condicao()
+ {
+ Campo = "d.situacao"
+ };
+ List<object> objs = new List<object>()
+ {
+ 1,
+ 2,
+ 4
+ };
+ condicao2.Valores = objs;
+ condicaos.Add(condicao2);
+ List<Condicao> condicaos1 = condicaos;
+ if (filtro.IdEmpresa > (long)0)
+ {
+ Condicao condicao3 = new Condicao()
+ {
+ Campo = "cl.idempresa",
+ Valores = filtro.IdEmpresa.CriarValor<long>()
+ };
+ condicaos1.Add(condicao3);
+ }
+ string str = "";
+ if (filtro.Vendedores != null && filtro.Vendedores.Count > 0)
+ {
+ str = "INNER JOIN vendedorparcela vp ON vp.iddocumento = d.iddocumento";
+ Condicao condicao4 = new Condicao()
+ {
+ Campo = "vp.idvendedor",
+ Valores = filtro.Vendedores.CriarValor<long>()
+ };
+ condicaos1.Add(condicao4);
+ }
+ string str1 = string.Concat("SELECT cl.idcliente,cl.nome,cl.cgccpf,c.idramo FROM cliente cl INNER JOIN controle c ON c.idcliente = cl.idcliente INNER JOIN documento d ON d.idcontrole = c.idcontrole ", str, " WHERE");
+ string referencia = filtro.Referencia;
+ if (referencia == "VIGÊNCIA INICIAL")
+ {
+ Condicao condicao5 = new Condicao()
+ {
+ Campo = "CAST(d.vigencia1 AS DATE)",
+ Valores = filtro.Inicio.CriarValor<DateTime>(),
+ Operador = Operador.MaiorEIgual
+ };
+ condicaos1.Add(condicao5);
+ Condicao condicao6 = new Condicao()
+ {
+ Campo = "CAST(d.vigencia1 AS DATE)",
+ Valores = filtro.Fim.CriarValor<DateTime>(),
+ Operador = Operador.MenorEIgual
+ };
+ condicaos1.Add(condicao6);
+ }
+ else if (referencia == "VIGÊNCIA FINAL")
+ {
+ Condicao condicao7 = new Condicao()
+ {
+ Campo = "d.vigencia2",
+ Valores = null,
+ Operador = Operador.Diferente
+ };
+ condicaos1.Add(condicao7);
+ Condicao condicao8 = new Condicao()
+ {
+ Campo = "CAST(d.vigencia2 AS DATE)",
+ Valores = filtro.Inicio.CriarValor<DateTime>(),
+ Operador = Operador.MaiorEIgual
+ };
+ condicaos1.Add(condicao8);
+ Condicao condicao9 = new Condicao()
+ {
+ Campo = "CAST(d.vigencia2 AS DATE)",
+ Valores = filtro.Fim.CriarValor<DateTime>(),
+ Operador = Operador.MenorEIgual
+ };
+ condicaos1.Add(condicao9);
+ }
+ else
+ {
+ Condicao condicao10 = new Condicao()
+ {
+ Campo = "CAST(p.vencto AS DATE)",
+ Valores = filtro.Inicio.CriarValor<DateTime>(),
+ Operador = Operador.MaiorEIgual
+ };
+ condicaos1.Add(condicao10);
+ Condicao condicao11 = new Condicao()
+ {
+ Campo = "CAST(p.vencto AS DATE)",
+ Valores = filtro.Fim.CriarValor<DateTime>(),
+ Operador = Operador.MenorEIgual
+ };
+ condicaos1.Add(condicao11);
+ str1 = string.Concat("SELECT cl.idcliente,cl.nome,cl.cgccpf,c.idramo FROM cliente cl INNER JOIN controle c ON c.idcliente = cl.idcliente INNER JOIN documento d ON d.idcontrole = c.idcontrole INNER JOIN parcela p ON p.iddocumento = d.iddocumento ", str, " WHERE CAST(d.n_parc AS INTEGER) = CAST(p.parcela AS INTEGER) AND");
+ }
+ SqlQueryCondition sqlQueryCondition = condicaos1.CreateParameters(0);
+ using (SessionFactoryImpl sessionFactory = this._unitOfWork.Session.SessionFactory as SessionFactoryImpl)
+ {
+ SessionFactoryImpl sessionFactoryImpl = sessionFactory;
+ if (sessionFactoryImpl != null)
+ {
+ connection = sessionFactoryImpl.ConnectionProvider.GetConnection();
+ }
+ else
+ {
+ connection = null;
+ }
+ using (SqlConnection sqlConnection = connection as SqlConnection)
+ {
+ using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
+ {
+ sqlCommand.CommandTimeout = 1000;
+ sqlCommand.CommandText = string.Concat(str1, " ", sqlQueryCondition.Condicao);
+ sqlCommand.Parameters.AddRange(sqlQueryCondition.Parametros.ToArray());
+ List<long> nums = new List<long>();
+ using (SqlDataReader sqlDataReader = await sqlCommand.ExecuteReaderAsync())
+ {
+ while (true)
+ {
+ if (!await sqlDataReader.ReadAsync())
+ {
+ break;
+ }
+ string str2 = sqlDataReader["cgccpf"].ToString();
+ long item = (long)sqlDataReader["idcliente"];
+ long item1 = (long)sqlDataReader["idramo"];
+ if (filtro.Ramos.Any<long>((long r) => r == item1))
+ {
+ nums.Add(item);
+ Prospectar prospectar = prospectars.FirstOrDefault<Prospectar>((Prospectar p) => p.Id == item);
+ if (prospectar != null)
+ {
+ prospectars.Remove(prospectar);
+ }
+ }
+ else if (!nums.Any<long>((long i) => i == item) && !prospectars.Any<Prospectar>((Prospectar c) => c.Id == item))
+ {
+ if (num1 > -1)
+ {
+ string str3 = str2;
+ if (str3 != null)
+ {
+ string str4 = str3.Clear();
+ if (str4 != null)
+ {
+ nullable1 = new int?(str4.Length);
+ }
+ else
+ {
+ nullable = null;
+ nullable1 = nullable;
+ }
+ }
+ else
+ {
+ nullable = null;
+ nullable1 = nullable;
+ }
+ int? nullable2 = nullable1;
+ if (num1 == 0)
+ {
+ nullable = nullable2;
+ if (nullable.GetValueOrDefault() < 12 & nullable.HasValue)
+ {
+ continue;
+ }
+ }
+ if (num1 == 1)
+ {
+ nullable = nullable2;
+ if (nullable.GetValueOrDefault() > 11 & nullable.HasValue)
+ {
+ continue;
+ }
+ }
+ }
+ if (!await this.ExisteClienteRamo(item, filtro.Ramos.FirstOrDefault<long>()))
+ {
+ string str5 = sqlDataReader["nome"].ToString();
+ List<Gestor.Model.Domain.Seguros.ClienteTelefone> clienteTelefones = await this.BuscaTelefonesCliente(item);
+ List<Prospectar> prospectars1 = prospectars;
+ Prospectar prospectar1 = new Prospectar();
+ Gestor.Model.Domain.Seguros.Cliente cliente = new Gestor.Model.Domain.Seguros.Cliente()
+ {
+ Id = item,
+ Nome = str5,
+ Telefones = new ObservableCollection<Gestor.Model.Domain.Seguros.ClienteTelefone>(clienteTelefones)
+ };
+ prospectar1.EntidadeCliente = cliente;
+ prospectar1.Id = item;
+ prospectar1.Nome = str5;
+ prospectar1.Documento = str2;
+ List<Gestor.Model.Domain.Seguros.ClienteTelefone> clienteTelefones1 = clienteTelefones;
+ prospectar1.Telefone = string.Join(" | ",
+ from t in clienteTelefones1
+ select string.Concat(t.Prefixo, " ", t.Numero));
+ prospectars1.Add(prospectar1);
+ str2 = null;
+ str5 = null;
+ }
+ }
+ }
+ }
+ sqlDataReader = null;
+ nums = null;
+ }
+ sqlCommand = null;
+ }
+ sqlConnection = null;
+ }
+ sessionFactory = null;
+ List<Prospectar> prospectars2 = prospectars;
+ List<Prospectar> list = (
+ from p in prospectars2
+ orderby p.Nome
+ select p).ToList<Prospectar>();
+ prospectars = null;
+ return list;
+ }
+
+ public List<ClientesAtivosInativos> BuscarAniversariantes(Filtros filtro, long idempresa = 0L)
+ {
+ List<Condicao> condicaos = new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "idempresa",
+ Valores = idempresa.CriarValor<long>()
+ }
+ };
+ List<Condicao> condicaos1 = new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "dtnasc",
+ Valores = null,
+ Operador = Operador.Diferente
+ },
+ new Condicao()
+ {
+ Campo = "excluido",
+ Valores = null,
+ Grupo = 1,
+ Operacao = Operacao.Or
+ },
+ new Condicao()
+ {
+ Campo = "excluido",
+ Valores = "0".CriarValor<string>(),
+ Grupo = 1,
+ Operacao = Operacao.Or
+ },
+ new Condicao()
+ {
+ Campo = "falecido",
+ Valores = null,
+ Grupo = 2,
+ Operacao = Operacao.Or
+ },
+ new Condicao()
+ {
+ Campo = "falecido",
+ Valores = "1".CriarValor<string>(),
+ Operador = Operador.Diferente,
+ Grupo = 2,
+ Operacao = Operacao.Or
+ }
+ };
+ if (idempresa > (long)0)
+ {
+ condicaos1.AddRange(condicaos);
+ }
+ string str = (filtro.Inicio.Month != 12 || filtro.Fim.Month != 1 ? string.Format("{0} FORMAT(dtnasc,'MMdd') BETWEEN {1:Mdd} AND {2:Mdd} AND", "SELECT idcliente as id, idempresa, nome, cgccpf as documento, cei, rne, documentoprincipal, anotacoes, dtnasc as nascimento, idprofissao, VENCHABILI as vencimento, sexo, RendaMensal, pasta, MalaDireta, clidesde, ESTADOCIVI FROM cliente WHERE ", filtro.Inicio, filtro.Fim) : string.Format("{0} 1 = (FLOOR(DATEDIFF(dd,DTNASC,'{1:yyyy-MM-dd}') / 365.25)) - (FLOOR(DATEDIFF(dd,DTNASC,'{2:yyyy-MM-dd}') / 365.25)) AND", "SELECT idcliente as id, idempresa, nome, cgccpf as documento, cei, rne, documentoprincipal, anotacoes, dtnasc as nascimento, idprofissao, VENCHABILI as vencimento, sexo, RendaMensal, pasta, MalaDireta, clidesde, ESTADOCIVI FROM cliente WHERE ", filtro.Fim, filtro.Inicio));
+ return this.Select(condicaos1.CreateParameters(0), str, true);
+ }
+
+ public List<ClientesAtivosInativos> BuscarAniversariantesVinculo(Filtros filtro, List<VendedorUsuario> vinculo, long idempresa = 0L)
+ {
+ List<Condicao> condicaos = new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "cl.idempresa",
+ Valores = idempresa.CriarValor<long>()
+ }
+ };
+ List<Condicao> condicaos1 = new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "cl.dtnasc",
+ Valores = null,
+ Operador = Operador.Diferente
+ },
+ new Condicao()
+ {
+ Campo = "cl.excluido",
+ Valores = null,
+ Grupo = 1,
+ Operacao = Operacao.Or
+ },
+ new Condicao()
+ {
+ Campo = "cl.excluido",
+ Valores = "0".CriarValor<string>(),
+ Grupo = 1,
+ Operacao = Operacao.Or
+ },
+ new Condicao()
+ {
+ Campo = "cl.falecido",
+ Valores = null,
+ Grupo = 2,
+ Operacao = Operacao.Or
+ },
+ new Condicao()
+ {
+ Campo = "cl.falecido",
+ Valores = "1".CriarValor<string>(),
+ Operador = Operador.Diferente,
+ Grupo = 2,
+ Operacao = Operacao.Or
+ }
+ };
+ if (idempresa > (long)0)
+ {
+ condicaos1.AddRange(condicaos);
+ }
+ string[] array = (
+ from id in (
+ from v in vinculo
+ select v.Id).ToList<long>()
+ select id.ToString()).ToArray<string>();
+ string str = string.Join(",", array);
+ string str1 = (filtro.Inicio.Month != 12 || filtro.Fim.Month != 1 ? string.Format("{0} vu.idvendedorusuario in ({1}) AND FORMAT(cl.dtnasc,'MMdd') BETWEEN {2:Mdd} AND {3:Mdd} AND", new object[] { "SELECT DISTINCT cl.idcliente as id, cl.idempresa, cl.nome, cl.cgccpf as documento, cl.cei, cl.rne, cl.documentoprincipal, cl.anotacoes, cl.dtnasc as nascimento, cl.idprofissao,cl.VENCHABILI as vencimento, cl.sexo, cl.RendaMensal, cl.pasta, cl.MalaDireta, cl.clidesde, cl.ESTADOCIVI FROM cliente cl inner join controle co on co.idcliente = cl.idcliente inner join documento d on d.idcontrole = co.idcontrole inner join vendedorparcela vp on vp.iddocumento = d.iddocumento inner join vendedorusuario vu on vu.idvendedor = vp.idvendedor WHERE ", str, filtro.Inicio, filtro.Fim }) : string.Format("{0} 1 = (FLOOR(DATEDIFF(dd,cl.DTNASC,'{1:yyyy-MM-dd}') / 365.25)) - (FLOOR(DATEDIFF(dd,cl.DTNASC,'{2:yyyy-MM-dd}') / 365.25)) AND", "SELECT DISTINCT cl.idcliente as id, cl.idempresa, cl.nome, cl.cgccpf as documento, cl.cei, cl.rne, cl.documentoprincipal, cl.anotacoes, cl.dtnasc as nascimento, cl.idprofissao,cl.VENCHABILI as vencimento, cl.sexo, cl.RendaMensal, cl.pasta, cl.MalaDireta, cl.clidesde, cl.ESTADOCIVI FROM cliente cl inner join controle co on co.idcliente = cl.idcliente inner join documento d on d.idcontrole = co.idcontrole inner join vendedorparcela vp on vp.iddocumento = d.iddocumento inner join vendedorusuario vu on vu.idvendedor = vp.idvendedor WHERE ", filtro.Fim, filtro.Inicio));
+ return this.Select(condicaos1.CreateParameters(0), str1, true);
+ }
+
+ public async Task<List<Classificacao>> BuscarClassificacoes(long idEmpresa)
+ {
+ List<Classificacao> classificacaos;
+ Func<Tuple<string, string>, bool> func;
+ object connection;
+ string str;
+ Func<Tuple<string, string>, bool> func1 = null;
+ Func<Tuple<string, string>, bool> func2 = null;
+ Func<Tuple<string, string>, bool> func3 = null;
+ List<Classificacao> classificacaos1 = new List<Classificacao>();
+ SessionFactoryImpl sessionFactory = this._unitOfWork.Session.SessionFactory as SessionFactoryImpl;
+ 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 = "WITH classificatorio ( CodigoCliente, NomeCliente, AtivoInativo, SomaPremioLiq, MediaComissao, Resultado ) AS (\r\nSELECT\r\n\tcl.idcliente AS CodigoCliente,\r\n\tcl.Nome AS NomeCliente,\r\n\t IIF(MAX(d.vigencia2) >= GETDATE(), 1, 0) AS AtivoInativo,\r\n\tSUM (d.pr_liq) AS SomaPremioLiq,\r\n\tSUM (d.com01) / (COUNT(d.com01)) AS MediaComissao,\r\n\tSUM ((d.pr_liq + (IIF(d.adinacomis = 1, d.pr_adic, 0))) * (d.com01 * 0.01)) AS Resultado\r\nFROM\r\n\tcliente cl\r\n\tLEFT JOIN controle c ON c.idcliente = cl.idcliente\r\n\tLEFT JOIN documento d ON d.idcontrole = c.idcontrole AND d.excluido != 1 \tAND d.SITUACAO not in (3,7) WHERE CL.EXCLUIDO != 1 \r\nGROUP BY\r\n\tcl.IdCliente,\r\n\tcl.nome \r\n\t) SELECT CodigoCliente,NomeCliente,\r\n\tAtivoInativo,\r\nCASE\r\n\tWHEN SomaPremioLiq >= iif(quali.liquido3 is null, 500, quali.liquido3) THEN\r\n\t3\r\n\tWHEN SomaPremioLiq >= iif(quali.liquido2 is null, 500, quali.liquido2) THEN\r\n\t2 \r\n\tWHEN SomaPremioLiq >= iif(quali.liquido1 is null, 500, quali.liquido1) THEN\r\n\t1 \r\n\tELSE 0 \r\nEND QualiPremioLiquido,\r\nCASE\r\n\tWHEN MediaComissao >= iif(quali.comissao3 is null, 20, quali.comissao3) THEN\r\n\t3\r\n\tWHEN MediaComissao >= iif(quali.comissao2 is null, 15, quali.comissao2) THEN\r\n\t2 \r\n\tWHEN MediaComissao >= iif(quali.comissao1 is null, 10, quali.comissao1) THEN\r\n\t1 \r\n\tELSE 0 \r\nEND QualiComissao,\r\nCASE\r\n\tWHEN Resultado >= iif(quali.resultado3 is null, 300, quali.resultado3) THEN\r\n\t3\r\n\tWHEN Resultado >= iif(quali.resultado2 is null, 200, quali.resultado2) THEN\r\n\t2 \r\n\tWHEN Resultado >= iif(quali.resultado1 is null, 100, quali.resultado1) THEN\r\n\t1 \r\n\tELSE 0 \r\nEND QualiResultado\r\nFROM\r\nclassificatorio OUTER APPLY (SELECT top 1 * FROM qualificacao) AS quali;";
+ sqlCommand.CommandTimeout = 1000;
+ List<Tuple<string, string>> tuples = new List<Tuple<string, string>>()
+ {
+ new Tuple<string, string>("0", "SEM CLASSIFICAÇÃO"),
+ new Tuple<string, string>("1", "BRONZE"),
+ new Tuple<string, string>("2", "PRATA"),
+ new Tuple<string, string>("3", "OURO")
+ };
+ List<Tuple<string, string>> tuples1 = tuples;
+ using (SqlDataReader sqlDataReader = await sqlCommand.ExecuteReaderAsync())
+ {
+ while (true)
+ {
+ if (!await sqlDataReader.ReadAsync())
+ {
+ break;
+ }
+ List<Tuple<string, string>> tuples2 = tuples1;
+ Func<Tuple<string, string>, bool> func4 = func1;
+ if (func4 == null)
+ {
+ Func<Tuple<string, string>, bool> func5 = (Tuple<string, string> x) => x.Item1.Equals(sqlDataReader["QualiPremioLiquido"].ToString() ?? "0");
+ func = func5;
+ func1 = func5;
+ func4 = func;
+ }
+ Tuple<string, string> tuple = tuples2.FirstOrDefault<Tuple<string, string>>(func4);
+ List<Tuple<string, string>> tuples3 = tuples1;
+ Func<Tuple<string, string>, bool> func6 = func2;
+ if (func6 == null)
+ {
+ Func<Tuple<string, string>, bool> func7 = (Tuple<string, string> x) => x.Item1.Equals(sqlDataReader["QualiResultado"].ToString() ?? "0");
+ func = func7;
+ func2 = func7;
+ func6 = func;
+ }
+ Tuple<string, string> tuple1 = tuples3.FirstOrDefault<Tuple<string, string>>(func6);
+ List<Tuple<string, string>> tuples4 = tuples1;
+ Func<Tuple<string, string>, bool> func8 = func3;
+ if (func8 == null)
+ {
+ Func<Tuple<string, string>, bool> func9 = (Tuple<string, string> x) => x.Item1.Equals(sqlDataReader["QualiComissao"].ToString() ?? "0");
+ func = func9;
+ func3 = func9;
+ func8 = func;
+ }
+ Tuple<string, string> tuple2 = tuples4.FirstOrDefault<Tuple<string, string>>(func8);
+ string item2 = tuples1.FirstOrDefault<Tuple<string, string>>((Tuple<string, string> x) => x.Item1.Equals(((int)Math.Round((double.Parse(tuple.Item1) + double.Parse(tuple1.Item1) + double.Parse(tuple2.Item1)) / 3)).ToString())).Item2;
+ str = (sqlDataReader["AtivoInativo"].ToString().Equals("1") ? "SIM" : "NÃO");
+ string str1 = str;
+ List<Classificacao> classificacaos2 = classificacaos1;
+ Classificacao classificacao = new Classificacao();
+ Gestor.Model.Domain.Seguros.Cliente cliente = new Gestor.Model.Domain.Seguros.Cliente()
+ {
+ Id = long.Parse(sqlDataReader["codigocliente"].ToString())
+ };
+ classificacao.EntidadeCliente = cliente;
+ classificacao.Nome = sqlDataReader["NomeCliente"].ToString();
+ classificacao.Premioliquido = tuple.Item2;
+ classificacao.Resultado = tuple1.Item2;
+ classificacao.Comissao = tuple2.Item2;
+ classificacao.Geral = item2;
+ classificacao.Ativo = str1;
+ classificacaos2.Add(classificacao);
+ }
+ }
+ tuples1 = null;
+ }
+ sqlCommand = null;
+ }
+ else
+ {
+ classificacaos = null;
+ classificacaos1 = null;
+ return classificacaos;
+ }
+ }
+ sqlConnection = null;
+ classificacaos = classificacaos1;
+ classificacaos1 = null;
+ return classificacaos;
+ }
+
+ public string BuscarLogAntigo(long id, string conn)
+ {
+ object connection;
+ string str = "";
+ List<Condicao> condicaos = new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "id",
+ Valores = id.CriarValor<long>()
+ },
+ new Condicao()
+ {
+ Campo = "idname",
+ Valores = "idcliente".CriarValor<string>()
+ }
+ };
+ this._unitOfWork.CriarAuxiliar();
+ SessionFactoryImpl sessionFactory = this._unitOfWork.Session.SessionFactory as SessionFactoryImpl;
+ string str1 = "oldbacklog";
+ if (sessionFactory != null)
+ {
+ connection = sessionFactory.ConnectionProvider.GetConnection();
+ }
+ else
+ {
+ connection = null;
+ }
+ using (SqlConnection sqlConnection = connection as SqlConnection)
+ {
+ using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
+ {
+ sqlCommand.CommandText = "SELECT TOP 1 * FROM controlelog";
+ SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
+ sqlDataReader.Read();
+ string str2 = sqlDataReader["bdname"].ToString();
+ str1 = sqlDataReader["tabela"].ToString();
+ sqlDataReader.Close();
+ if (sqlConnection.Database != str2)
+ {
+ conn = conn.Replace(sqlConnection.Database, str2);
+ }
+ }
+ }
+ DataTable dataTable = new DataTable();
+ using (SqlConnection sqlConnection1 = new SqlConnection(conn))
+ {
+ sqlConnection1.Open();
+ using (SqlCommand sqlCommand1 = sqlConnection1.CreateCommand())
+ {
+ sqlCommand1.CommandTimeout = 15000;
+ dataTable = sqlCommand1.Select(condicaos.CreateParameters(0), string.Concat("SELECT b.* FROM ", str1, " b WHERE"), "");
+ }
+ }
+ if (dataTable == null || dataTable.Rows.Count == 0)
+ {
+ return "";
+ }
+ if (dataTable != null)
+ {
+ dataTable.AsEnumerable().OrderBy<DataRow, long>((DataRow x) => x.Field<long>("idbacklog")).ToList<DataRow>().ForEach((DataRow x) => {
+ str = string.Concat(new string[] { str, "<br><FONT face=verdana color=#FF0000 size=2><strong>(", x.Field<object>("data").ToString(), ")</font><FONT face=verdana size=2 color=#000000> ", Auxiliar.Usuarios.Find((Usuario u) => u.Id == x.Field<long>("idusuario")).Nome, "</strong></font><br>" });
+ str = string.Concat(str, "<FONT face=verdana color=#000000 size=1>", x.Field<object>("historico").ToString().Replace(Environment.NewLine, "<br>"), "</font>");
+ });
+ }
+ return str;
+ }
+
+ public List<OrigemCliente> BuscarOrigem(long id)
+ {
+ List<OrigemClienteDb> list = (
+ from x in this._unitOfWork.Query<OrigemClienteDb>()
+ where x.Cliente.Id == id
+ select x).ToList<OrigemClienteDb>();
+ return ApplicationMapper.Mapper.Map<List<OrigemClienteDb>, List<OrigemCliente>>(list);
+ }
+
+ public List<ClientesAtivosInativos> BuscarVencimentoCnh(Filtros filtro, long idempresa = 0L)
+ {
+ List<Condicao> condicaos = new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "idempresa",
+ Valores = idempresa.CriarValor<long>()
+ }
+ };
+ List<Condicao> condicaos1 = new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "venchabili",
+ Valores = null,
+ Operador = Operador.Diferente
+ },
+ new Condicao()
+ {
+ Campo = "venchabili",
+ Valores = filtro.Inicio.CriarValor<DateTime>(),
+ Operador = Operador.MaiorEIgual
+ },
+ new Condicao()
+ {
+ Campo = "venchabili",
+ Valores = filtro.Fim.CriarValor<DateTime>(),
+ Operador = Operador.MenorEIgual
+ },
+ new Condicao()
+ {
+ Campo = "excluido",
+ Valores = null,
+ Grupo = 1,
+ Operacao = Operacao.Or
+ },
+ new Condicao()
+ {
+ Campo = "excluido",
+ Valores = "0".CriarValor<string>(),
+ Grupo = 1,
+ Operacao = Operacao.Or
+ }
+ };
+ if (idempresa > (long)0)
+ {
+ condicaos1.AddRange(condicaos);
+ }
+ return this.Select(condicaos1.CreateParameters(0), "SELECT idcliente as id, idempresa, nome, cgccpf as documento, cei, rne, documentoprincipal, anotacoes, dtnasc as nascimento, idprofissao, VENCHABILI as vencimento, sexo, RendaMensal, pasta, MalaDireta, clidesde, ESTADOCIVI FROM cliente WHERE ", true);
+ }
+
+ private async Task<List<Gestor.Model.Domain.Seguros.ClienteTelefone>> BuscaTelefonesCliente(long idCliente)
+ {
+ object connection;
+ List<Gestor.Model.Domain.Seguros.ClienteTelefone> clienteTelefones = new List<Gestor.Model.Domain.Seguros.ClienteTelefone>();
+ using (SessionFactoryImpl sessionFactory = this._unitOfWork.Session.SessionFactory as SessionFactoryImpl)
+ {
+ SessionFactoryImpl sessionFactoryImpl = sessionFactory;
+ if (sessionFactoryImpl != null)
+ {
+ connection = sessionFactoryImpl.ConnectionProvider.GetConnection();
+ }
+ else
+ {
+ connection = null;
+ }
+ using (SqlConnection sqlConnection = connection as SqlConnection)
+ {
+ using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
+ {
+ sqlCommand.CommandTimeout = 1000;
+ sqlCommand.CommandText = "SELECT ddd,fone FROM clitelefone WHERE idcliente = @cliente";
+ sqlCommand.Parameters.Add("@cliente", SqlDbType.BigInt).Value = idCliente;
+ using (SqlDataReader sqlDataReader = await sqlCommand.ExecuteReaderAsync())
+ {
+ while (true)
+ {
+ if (!await sqlDataReader.ReadAsync())
+ {
+ break;
+ }
+ List<Gestor.Model.Domain.Seguros.ClienteTelefone> clienteTelefones1 = clienteTelefones;
+ Gestor.Model.Domain.Seguros.ClienteTelefone clienteTelefone = new Gestor.Model.Domain.Seguros.ClienteTelefone()
+ {
+ Prefixo = sqlDataReader["ddd"].ToString(),
+ Numero = sqlDataReader["fone"].ToString()
+ };
+ clienteTelefones1.Add(clienteTelefone);
+ }
+ }
+ sqlDataReader = null;
+ }
+ sqlCommand = null;
+ }
+ sqlConnection = null;
+ }
+ sessionFactory = null;
+ List<Gestor.Model.Domain.Seguros.ClienteTelefone> clienteTelefones2 = clienteTelefones;
+ clienteTelefones = null;
+ return clienteTelefones2;
+ }
+
+ private int CalculaIdade(DateTime nascimento)
+ {
+ int year = DateTime.Now.Year - nascimento.Year;
+ if (DateTime.Now.DayOfYear < nascimento.DayOfYear)
+ {
+ year--;
+ }
+ return year;
+ }
+
+ private List<Condicao> CriarFiltro(string filter)
+ {
+ return new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "UPPER(NOME) COLLATE Latin1_General_CI_AI",
+ Valores = filter.CriarValor<string>(),
+ Operacao = Operacao.Or,
+ Operador = Operador.Like,
+ Grupo = 2
+ },
+ new Condicao()
+ {
+ Campo = "UPPER(CGCCPF)",
+ Valores = filter.CriarValor<string>(),
+ Operacao = Operacao.Or,
+ Operador = Operador.Like,
+ Grupo = 2
+ },
+ new Condicao()
+ {
+ Campo = "UPPER(CEI)",
+ Valores = filter.CriarValor<string>(),
+ Operacao = Operacao.Or,
+ Operador = Operador.Like,
+ Grupo = 2
+ },
+ new Condicao()
+ {
+ Campo = "UPPER(RNE)",
+ Valores = filter.CriarValor<string>(),
+ Operacao = Operacao.Or,
+ Operador = Operador.Like,
+ Grupo = 2
+ },
+ new Condicao()
+ {
+ Campo = "UPPER(CAEPF)",
+ Valores = filter.CriarValor<string>(),
+ Operacao = Operacao.Or,
+ Operador = Operador.Like,
+ Grupo = 2
+ },
+ new Condicao()
+ {
+ Campo = "UPPER(nomesocialRg) COLLATE Latin1_General_CI_AI",
+ Valores = filter.CriarValor<string>(),
+ Operacao = Operacao.Or,
+ Operador = Operador.Like,
+ Grupo = 2
+ }
+ };
+ }
+
+ public void Delete(long id)
+ {
+ base.Delete(base.FindEntityById(id));
+ }
+
+ public void DeleteOrigem(long id)
+ {
+ OrigemClienteDb origemClienteDb = this._unitOfWork.Repository<OrigemClienteDb>().FindEntityById(id);
+ if (origemClienteDb == null)
+ {
+ return;
+ }
+ this._unitOfWork.Repository<OrigemClienteDb>().Delete(origemClienteDb);
+ }
+
+ private async Task<IEnumerable<Gestor.Model.Relatorios.Documento>> DocumentosPeriodo(bool completo)
+ {
+ IEnumerable<Gestor.Model.Relatorios.Documento> documentos;
+ object connection;
+ Func<Gestor.Model.Relatorios.Vendedor, bool> func = null;
+ List<Gestor.Model.Relatorios.Documento> documentos1 = new List<Gestor.Model.Relatorios.Documento>();
+ SessionFactoryImpl sessionFactory = this._unitOfWork.Session.SessionFactory as SessionFactoryImpl;
+ if (sessionFactory != null)
+ {
+ connection = sessionFactory.ConnectionProvider.GetConnection();
+ }
+ else
+ {
+ connection = null;
+ }
+ using (SqlConnection sqlConnection = connection as SqlConnection)
+ {
+ if (sqlConnection != null)
+ {
+ using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
+ {
+ if (!completo)
+ {
+ sqlCommand.CommandText = "SELECT DISTINCT d.iddocumento,d.idcontrole,c.idcliente FROM documento d INNER JOIN controle c ON c.idcontrole = d.idcontrole WHERE c.idcliente IS NOT NULL AND (d.excluido IS NULL OR d.excluido = 0) AND (d.vigencia2 IS NULL OR d.vigencia2 > @vigenciafinal) AND d.situacao IN (1,2,4) ORDER BY d.iddocumento";
+ }
+ else
+ {
+ sqlCommand.CommandText = "SELECT DISTINCT d.iddocumento,d.idcontrole,c.idcliente,c.idramo,r.nome nomeramo,vp.idvendedor,v.nome nomevendedor, d.ordem FROM documento d INNER JOIN controle c ON c.idcontrole = d.idcontrole INNER JOIN ramo r ON r.idramo = c.idramo LEFT JOIN vendedorparcela vp ON d.iddocumento = vp.iddocumento LEFT JOIN vendedor v ON vp.idvendedor = v.idvendedor WHERE c.idcliente IS NOT NULL AND (d.excluido IS NULL OR d.excluido = 0) AND (d.vigencia2 IS NULL OR d.vigencia2 > @vigenciafinal) AND d.situacao IN (1,2,4) ORDER BY d.iddocumento";
+ }
+ SqlParameter sqlParameter = sqlCommand.Parameters.Add("@vigenciafinal", SqlDbType.DateTime);
+ sqlParameter.Value = DateTime.Now.AddDays(-5);
+ sqlCommand.CommandTimeout = 1000;
+ using (SqlDataReader sqlDataReader = await sqlCommand.ExecuteReaderAsync())
+ {
+ long id = (long)0;
+ while (true)
+ {
+ if (!await sqlDataReader.ReadAsync())
+ {
+ break;
+ }
+ Gestor.Model.Relatorios.Documento documento = new Gestor.Model.Relatorios.Documento();
+ bool flag = true;
+ if (id == (long)sqlDataReader["iddocumento"])
+ {
+ documento = documentos1.Last<Gestor.Model.Relatorios.Documento>();
+ flag = false;
+ }
+ if (!flag)
+ {
+ bool flag1 = completo;
+ if (flag1)
+ {
+ flag1 = !await sqlDataReader.IsDBNullAsync(sqlDataReader.GetOrdinal("idvendedor"));
+ }
+ if (flag1)
+ {
+ IList<Gestor.Model.Relatorios.Vendedor> vendedores = documento.Vendedores;
+ Func<Gestor.Model.Relatorios.Vendedor, bool> func1 = func;
+ if (func1 == null)
+ {
+ Func<Gestor.Model.Relatorios.Vendedor, bool> id1 = (Gestor.Model.Relatorios.Vendedor v) => v.Id == (long)sqlDataReader["idvendedor"];
+ Func<Gestor.Model.Relatorios.Vendedor, bool> func2 = id1;
+ func = id1;
+ func1 = func2;
+ }
+ if (vendedores.Where<Gestor.Model.Relatorios.Vendedor>(func1).Count<Gestor.Model.Relatorios.Vendedor>() == 0)
+ {
+ IList<Gestor.Model.Relatorios.Vendedor> vendedors = documento.Vendedores;
+ Gestor.Model.Relatorios.Vendedor vendedor = new Gestor.Model.Relatorios.Vendedor()
+ {
+ Id = (long)sqlDataReader["idvendedor"],
+ Nome = sqlDataReader["nomevendedor"].ToString()
+ };
+ vendedors.Add(vendedor);
+ }
+ }
+ }
+ else
+ {
+ documento.Id = (long)sqlDataReader["iddocumento"];
+ Gestor.Model.Relatorios.Documento documento1 = documento;
+ Gestor.Model.Relatorios.Controle controle = new Gestor.Model.Relatorios.Controle()
+ {
+ Id = (long)sqlDataReader["idcontrole"]
+ };
+ Gestor.Model.Relatorios.Cliente cliente = new Gestor.Model.Relatorios.Cliente()
+ {
+ Id = (long)sqlDataReader["idcliente"]
+ };
+ controle.Cliente = cliente;
+ documento1.Controle = controle;
+ if (completo)
+ {
+ documento.Ordem = (int?)sqlDataReader["ordem"];
+ Gestor.Model.Relatorios.Controle controle1 = documento.Controle;
+ Gestor.Model.Relatorios.Ramo ramo = new Gestor.Model.Relatorios.Ramo()
+ {
+ Id = (long)sqlDataReader["idramo"],
+ Nome = sqlDataReader["nomeramo"].ToString()
+ };
+ controle1.Ramo = ramo;
+ if (!await sqlDataReader.IsDBNullAsync(sqlDataReader.GetOrdinal("idvendedor")))
+ {
+ Gestor.Model.Relatorios.Documento documento2 = documento;
+ List<Gestor.Model.Relatorios.Vendedor> vendedors1 = new List<Gestor.Model.Relatorios.Vendedor>();
+ Gestor.Model.Relatorios.Vendedor vendedor1 = new Gestor.Model.Relatorios.Vendedor()
+ {
+ Id = (long)sqlDataReader["idvendedor"],
+ Nome = sqlDataReader["nomevendedor"].ToString()
+ };
+ vendedors1.Add(vendedor1);
+ documento2.Vendedores = vendedors1;
+ }
+ }
+ }
+ id = documento.Id;
+ if (flag)
+ {
+ documentos1.Add(documento);
+ }
+ documento = null;
+ }
+ }
+ }
+ sqlCommand = null;
+ }
+ else
+ {
+ documentos = null;
+ documentos1 = null;
+ return documentos;
+ }
+ }
+ sqlConnection = null;
+ documentos = documentos1;
+ documentos1 = null;
+ return documentos;
+ }
+
+ private async Task<IEnumerable<Gestor.Model.Relatorios.ClienteEmail>> EmailsClientes()
+ {
+ IEnumerable<Gestor.Model.Relatorios.ClienteEmail> clienteEmails;
+ object connection;
+ List<Gestor.Model.Relatorios.ClienteEmail> clienteEmails1 = new List<Gestor.Model.Relatorios.ClienteEmail>();
+ SessionFactoryImpl sessionFactory = this._unitOfWork.Session.SessionFactory as SessionFactoryImpl;
+ 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 = "SELECT idcliente,email FROM climail ORDER BY idcliente,ordem";
+ sqlCommand.CommandTimeout = 1000;
+ using (SqlDataReader sqlDataReader = await sqlCommand.ExecuteReaderAsync())
+ {
+ while (true)
+ {
+ if (!await sqlDataReader.ReadAsync())
+ {
+ break;
+ }
+ List<Gestor.Model.Relatorios.ClienteEmail> clienteEmails2 = clienteEmails1;
+ Gestor.Model.Relatorios.ClienteEmail clienteEmail = new Gestor.Model.Relatorios.ClienteEmail();
+ Gestor.Model.Relatorios.Cliente cliente = new Gestor.Model.Relatorios.Cliente()
+ {
+ Id = (long)sqlDataReader["idcliente"]
+ };
+ clienteEmail.Cliente = cliente;
+ clienteEmail.Email = sqlDataReader["email"].ToString();
+ clienteEmails2.Add(clienteEmail);
+ }
+ }
+ sqlDataReader = null;
+ }
+ sqlCommand = null;
+ }
+ else
+ {
+ clienteEmails = null;
+ clienteEmails1 = null;
+ return clienteEmails;
+ }
+ }
+ sqlConnection = null;
+ clienteEmails = clienteEmails1;
+ clienteEmails1 = null;
+ return clienteEmails;
+ }
+
+ private async Task<IEnumerable<Gestor.Model.Relatorios.ClienteEndereco>> EnderecoClientes()
+ {
+ IEnumerable<Gestor.Model.Relatorios.ClienteEndereco> clienteEnderecos;
+ object connection;
+ int num;
+ List<Gestor.Model.Relatorios.ClienteEndereco> clienteEnderecos1 = new List<Gestor.Model.Relatorios.ClienteEndereco>();
+ SessionFactoryImpl sessionFactory = this._unitOfWork.Session.SessionFactory as SessionFactoryImpl;
+ 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 = "SELECT DISTINCT idcliend, idcliente,ordem,endereco,numero,comp_end,cidade,uf,cep,bairro FROM cliendereco ORDER BY idcliente, ordem, idcliend";
+ sqlCommand.CommandTimeout = 1000;
+ using (SqlDataReader sqlDataReader = await sqlCommand.ExecuteReaderAsync())
+ {
+ while (true)
+ {
+ if (!await sqlDataReader.ReadAsync())
+ {
+ break;
+ }
+ List<Gestor.Model.Relatorios.ClienteEndereco> clienteEnderecos2 = clienteEnderecos1;
+ Gestor.Model.Relatorios.ClienteEndereco clienteEndereco = new Gestor.Model.Relatorios.ClienteEndereco()
+ {
+ IdCliEnd = (long)sqlDataReader["idcliend"]
+ };
+ Gestor.Model.Relatorios.Cliente cliente = new Gestor.Model.Relatorios.Cliente()
+ {
+ Id = (long)sqlDataReader["idcliente"]
+ };
+ clienteEndereco.Cliente = cliente;
+ clienteEndereco.Endereco = sqlDataReader["endereco"].ToString();
+ clienteEndereco.Numero = sqlDataReader["numero"].ToString();
+ clienteEndereco.Complemento = sqlDataReader["comp_end"].ToString();
+ clienteEndereco.Cidade = sqlDataReader["cidade"].ToString();
+ clienteEndereco.Estado = sqlDataReader["uf"].ToString();
+ clienteEndereco.Cep = sqlDataReader["cep"].ToString();
+ clienteEndereco.Bairro = sqlDataReader["bairro"].ToString();
+ num = (sqlDataReader.IsDBNull(sqlDataReader.GetOrdinal("ordem")) ? 0 : (int)sqlDataReader["ordem"]);
+ clienteEndereco.Ordem = num;
+ clienteEnderecos2.Add(clienteEndereco);
+ }
+ }
+ sqlDataReader = null;
+ }
+ sqlCommand = null;
+ }
+ else
+ {
+ clienteEnderecos = null;
+ clienteEnderecos1 = null;
+ return clienteEnderecos;
+ }
+ }
+ sqlConnection = null;
+ clienteEnderecos = clienteEnderecos1;
+ clienteEnderecos1 = null;
+ return clienteEnderecos;
+ }
+
+ private async Task<IEnumerable<EstipulanteCliente>> EstipulanteCliente()
+ {
+ IEnumerable<EstipulanteCliente> estipulanteClientes;
+ object connection;
+ List<EstipulanteCliente> estipulanteClientes1 = new List<EstipulanteCliente>();
+ SessionFactoryImpl sessionFactory = this._unitOfWork.Session.SessionFactory as SessionFactoryImpl;
+ 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 = "SELECT DISTINCT c.idcliente, e.idestipulante, e.nome FROM documento d INNER JOIN estipulante e on e.idestipulante = d.idestipulante INNER JOIN controle c on c.idcontrole = d.idcontrole WHERE d.idestipulante IS NOT NULL AND (d.vigencia2 > GETDATE() OR d.vigencia2 IS NULL)";
+ sqlCommand.CommandTimeout = 1000;
+ using (SqlDataReader sqlDataReader = await sqlCommand.ExecuteReaderAsync())
+ {
+ while (true)
+ {
+ if (!await sqlDataReader.ReadAsync())
+ {
+ break;
+ }
+ List<EstipulanteCliente> estipulanteClientes2 = estipulanteClientes1;
+ EstipulanteCliente estipulanteCliente = new EstipulanteCliente()
+ {
+ IdEstiputante = (long)sqlDataReader["idestipulante"],
+ IdCliente = (long)sqlDataReader["idcliente"],
+ NomeEstipulante = sqlDataReader["nome"].ToString()
+ };
+ estipulanteClientes2.Add(estipulanteCliente);
+ }
+ }
+ sqlDataReader = null;
+ }
+ sqlCommand = null;
+ }
+ else
+ {
+ estipulanteClientes = null;
+ estipulanteClientes1 = null;
+ return estipulanteClientes;
+ }
+ }
+ sqlConnection = null;
+ estipulanteClientes = estipulanteClientes1;
+ estipulanteClientes1 = null;
+ return estipulanteClientes;
+ }
+
+ private async Task<bool> ExisteClienteRamo(long idCliente, long idRamo)
+ {
+ bool flag;
+ object connection;
+ using (SessionFactoryImpl sessionFactory = this._unitOfWork.Session.SessionFactory as SessionFactoryImpl)
+ {
+ SessionFactoryImpl sessionFactoryImpl = sessionFactory;
+ if (sessionFactoryImpl != null)
+ {
+ connection = sessionFactoryImpl.ConnectionProvider.GetConnection();
+ }
+ else
+ {
+ connection = null;
+ }
+ using (SqlConnection sqlConnection = connection as SqlConnection)
+ {
+ using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
+ {
+ sqlCommand.CommandText = "SELECT COUNT(c.idcontrole) contador FROM controle c INNER JOIN documento d ON d.idcontrole = c.idcontrole WHERE d.situacao IN (1,2,4) AND (d.vigencia2 > DATEADD(day, -5, GETDATE()) OR d.vigencia2 IS NULL) AND c.idcliente = @cliente AND c.idramo = @ramo";
+ sqlCommand.Parameters.Add("@cliente", SqlDbType.BigInt).Value = idCliente;
+ sqlCommand.Parameters.Add("@ramo", SqlDbType.BigInt).Value = idRamo;
+ flag = (int)await sqlCommand.ExecuteScalarAsync() > 0;
+ }
+ }
+ }
+ return flag;
+ }
+
+ public List<Gestor.Model.Domain.Seguros.Cliente> Find(string filter, long idempresa = 0L, bool acessoAgger = false, bool assintatura = false)
+ {
+ List<Gestor.Model.Domain.Seguros.Cliente> clientes;
+ object connection;
+ DataTable dataTable = new DataTable();
+ List<long> nums = new List<long>();
+ DataTable dataTable1 = 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)
+ {
+ if (sqlConnection != null)
+ {
+ using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
+ {
+ List<Condicao> condicaos = new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "excluido",
+ Valores = null,
+ Grupo = 1,
+ Operacao = Operacao.Or
+ },
+ new Condicao()
+ {
+ Campo = "excluido",
+ Valores = "0".CriarValor<string>(),
+ Grupo = 1,
+ Operacao = Operacao.Or
+ }
+ };
+ List<Condicao> condicaos1 = this.CriarFiltro(filter);
+ List<Condicao> condicaos2 = new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "idempresa",
+ Valores = idempresa.CriarValor<long>()
+ }
+ };
+ if (!acessoAgger)
+ {
+ condicaos1.AddRange(condicaos);
+ }
+ if (idempresa > (long)0)
+ {
+ condicaos1.AddRange(condicaos2);
+ }
+ dataTable = sqlCommand.Select(condicaos1.CreateParameters(0), "SELECT TOP 200 IDEMPRESA, IDCLIENTE, NOME COLLATE Latin1_General_CI_AI AS NOME, CGCCPF, CEI, RNE, ISNULL(DOCUMENTOPRINCIPAL, 0) AS DOCUMENTOPRINCIPAL, CAEPF, EXCLUIDO, MalaDireta, nomesocialRg COLLATE Latin1_General_CI_AI as nomesocialRg FROM cliente WHERE", "");
+ if (dataTable.Rows.Count > 0)
+ {
+ List<Condicao> condicaos3 = new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "c.idcliente",
+ Valores = dataTable.AsEnumerable().Select<DataRow, long>((DataRow x) => x.Field<long>("IDCLIENTE")).ToList<long>().CriarValor<long>()
+ }
+ };
+ List<Condicao> condicaos4 = new List<Condicao>()
+ {
+ 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
+ },
+ new Condicao()
+ {
+ Campo = "d.tipo",
+ Valores = 0.CriarValor<int>()
+ },
+ new Condicao()
+ {
+ Campo = "d.vigencia2",
+ Valores = null,
+ Grupo = 2,
+ Operacao = Operacao.Or
+ }
+ };
+ Condicao condicao = new Condicao()
+ {
+ Campo = "d.vigencia2"
+ };
+ DateTime date = Funcoes.GetNetworkTime().Date;
+ condicao.Valores = date.AddDays(-5).CriarValor<DateTime>();
+ condicao.Grupo = 2;
+ condicao.Operacao = Operacao.Or;
+ condicao.Operador = Operador.Maior;
+ condicaos4.Add(condicao);
+ condicaos4.Add(new Condicao()
+ {
+ Campo = "d.situacao",
+ Valores = new List<object>()
+ {
+ 1,
+ 2,
+ 4
+ }
+ });
+ List<Condicao> condicaos5 = condicaos4;
+ condicaos5.AddRange(condicaos3);
+ DataTable dataTable2 = sqlCommand.Select(condicaos5.CreateParameters(0), "SELECT DISTINCT c.idcliente FROM documento d INNER JOIN controle c on c.idcontrole = d.idcontrole WHERE", "");
+ nums = dataTable2.AsEnumerable().Select<DataRow, long>((DataRow x) => x.Field<long>("idcliente")).ToList<long>();
+ if (assintatura)
+ {
+ dataTable1 = sqlCommand.Select(condicaos3.CreateParameters(0), "SELECT * FROM ResponsavelAssinatura c WHERE ", "");
+ }
+ }
+ goto Label0;
+ }
+ }
+ else
+ {
+ clientes = new List<Gestor.Model.Domain.Seguros.Cliente>();
+ }
+ }
+ return clientes;
+ List<ResponsavelAssinatura> list = dataTable1.AsEnumerable().Select<DataRow, ResponsavelAssinatura>((DataRow x) => new ResponsavelAssinatura()
+ {
+ Id = x.Field<long>("Id"),
+ IdCliente = x.Field<long>("IdCliente"),
+ NomeResponsavel = x.Field<string>("NomeResponsavel").RemoverAcentos(),
+ DocumentoResponsavel = x.Field<string>("DocumentoResponsavel"),
+ EmailResponsavel = x.Field<string>("EmailResponsavel")
+ }).ToList<ResponsavelAssinatura>();
+ return (
+ from x in dataTable.AsEnumerable().ToList<DataRow>()
+ select new Gestor.Model.Domain.Seguros.Cliente()
+ {
+ Id = x.Field<long>("IDCLIENTE"),
+ IdEmpresa = x.Field<long>("IDEMPRESA"),
+ Nome = x.Field<string>("NOME").RemoverAcentos(),
+ Documento = x.Field<string>("CGCCPF"),
+ Cei = x.Field<string>("CEI"),
+ Rne = x.Field<string>("RNE"),
+ Caepf = x.Field<string>("CAEPF"),
+ DocumentoPrincipal = new TipoDocumento?((TipoDocumento)Enum.Parse(typeof(TipoDocumento), x.Field<int>("DOCUMENTOPRINCIPAL").ToString())),
+ Status = (nums.Any<long>((long d) => d == x.Field<long>("IDCLIENTE")) ? "ATIVO" : "INATIVO"),
+ ResponsavelAssinatura = list.Find((ResponsavelAssinatura r) => r.IdCliente == x.Field<long>("IDCLIENTE")),
+ Excluido = (x.Field<object>("EXCLUIDO") == null ? false : x.Field<object>("EXCLUIDO").ToString() == "1"),
+ MalaDireta = new bool?(x.Field<bool?>("MalaDireta").GetValueOrDefault(true)),
+ NomeSocialRg = x.Field<string>("nomesocialRg")
+ }).ToList<Gestor.Model.Domain.Seguros.Cliente>();
+ }
+
+ public List<ClientesAtivosInativos> FindAllClientes(bool completo, long idempresa = 0L)
+ {
+ List<Condicao> condicaos = new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "idempresa",
+ Valores = idempresa.CriarValor<long>()
+ }
+ };
+ List<Condicao> condicaos1 = new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "excluido",
+ Valores = null,
+ Grupo = 1,
+ Operacao = Operacao.Or
+ },
+ new Condicao()
+ {
+ Campo = "excluido",
+ Valores = "0".CriarValor<string>(),
+ Grupo = 1,
+ Operacao = Operacao.Or
+ }
+ };
+ if (idempresa > (long)0)
+ {
+ condicaos1.AddRange(condicaos);
+ }
+ return this.Select(condicaos1.CreateParameters(0), "SELECT idcliente as id, idempresa, nome, cgccpf as documento, cei, rne, documentoprincipal, anotacoes, dtnasc as nascimento, idprofissao, VENCHABILI as vencimento, sexo, RendaMensal, pasta, MalaDireta, clidesde, ESTADOCIVI FROM cliente WHERE ", completo);
+ }
+
+ public Gestor.Model.Domain.Seguros.Cliente FindById(long id)
+ {
+ ClienteDb clienteDb = base.FindEntityById(id);
+ ResponsavelAssinaturaDb responsavelAssinaturaDb = this._unitOfWork.Query<ResponsavelAssinaturaDb>().FirstOrDefault<ResponsavelAssinaturaDb>((ResponsavelAssinaturaDb x) => x.IdCliente == id);
+ ResponsavelAssinatura responsavelAssinatura = ApplicationMapper.Mapper.Map<ResponsavelAssinaturaDb, ResponsavelAssinatura>(responsavelAssinaturaDb);
+ Gestor.Model.Domain.Seguros.Cliente cliente = ApplicationMapper.Mapper.Map<ClienteDb, Gestor.Model.Domain.Seguros.Cliente>(clienteDb);
+ cliente.ResponsavelAssinatura = responsavelAssinatura;
+ return cliente;
+ }
+
+ public List<Gestor.Model.Domain.Seguros.Cliente> FindByName(string filter, long idEmpresa = 0L, bool acessoAgger = false, bool assintatura = false, bool somenteNome = false)
+ {
+ return this.Find(filter, idEmpresa, acessoAgger, assintatura);
+ }
+
+ public List<Gestor.Model.Domain.Seguros.Cliente> FindByNameOld(string filter, long idempresa = 0L, bool acessoAgger = false, bool assintatura = false, bool somenteNome = false)
+ {
+ List<Gestor.Model.Domain.Seguros.Cliente> clientes;
+ object connection;
+ DataTable dataTable = new DataTable();
+ List<long> nums = new List<long>();
+ DataTable dataTable1 = 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)
+ {
+ if (sqlConnection != null)
+ {
+ using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
+ {
+ List<Condicao> condicaos = new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "excluido",
+ Valores = null,
+ Grupo = 1,
+ Operacao = Operacao.Or
+ },
+ new Condicao()
+ {
+ Campo = "excluido",
+ Valores = "0".CriarValor<string>(),
+ Grupo = 1,
+ Operacao = Operacao.Or
+ }
+ };
+ List<Condicao> condicaos1 = new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "UPPER(NOME) COLLATE Latin1_General_CI_AI",
+ Valores = filter.CriarValor<string>(),
+ Operador = (somenteNome ? Operador.ComecaCom : Operador.Like)
+ }
+ };
+ List<Condicao> condicaos2 = new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "idempresa",
+ Valores = idempresa.CriarValor<long>()
+ }
+ };
+ if (!acessoAgger)
+ {
+ condicaos1.AddRange(condicaos);
+ }
+ if (idempresa > (long)0)
+ {
+ condicaos1.AddRange(condicaos2);
+ }
+ dataTable = sqlCommand.Select(condicaos1.CreateParameters(0), "SELECT TOP 200 IDEMPRESA, IDCLIENTE, NOME COLLATE Latin1_General_CI_AI AS NOME, CGCCPF, CEI, RNE, ISNULL(DOCUMENTOPRINCIPAL, 0) AS DOCUMENTOPRINCIPAL, CAEPF, EXCLUIDO, MalaDireta, nomesocialRg COLLATE Latin1_General_CI_AI as nomesocialRg FROM cliente WHERE", "");
+ if (dataTable.Rows.Count > 0)
+ {
+ List<Condicao> condicaos3 = new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "c.idcliente",
+ Valores = dataTable.AsEnumerable().Select<DataRow, long>((DataRow x) => x.Field<long>("IDCLIENTE")).ToList<long>().CriarValor<long>()
+ }
+ };
+ List<Condicao> condicaos4 = new List<Condicao>()
+ {
+ 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
+ },
+ new Condicao()
+ {
+ Campo = "d.tipo",
+ Valores = 0.CriarValor<int>()
+ },
+ new Condicao()
+ {
+ Campo = "d.vigencia2",
+ Valores = null,
+ Grupo = 2,
+ Operacao = Operacao.Or
+ }
+ };
+ Condicao condicao = new Condicao()
+ {
+ Campo = "d.vigencia2"
+ };
+ DateTime date = Funcoes.GetNetworkTime().Date;
+ condicao.Valores = date.AddDays(-5).CriarValor<DateTime>();
+ condicao.Grupo = 2;
+ condicao.Operacao = Operacao.Or;
+ condicao.Operador = Operador.Maior;
+ condicaos4.Add(condicao);
+ condicaos4.Add(new Condicao()
+ {
+ Campo = "d.situacao",
+ Valores = new List<object>()
+ {
+ 1,
+ 2,
+ 4
+ }
+ });
+ List<Condicao> condicaos5 = condicaos4;
+ condicaos5.AddRange(condicaos3);
+ DataTable dataTable2 = sqlCommand.Select(condicaos5.CreateParameters(0), "SELECT DISTINCT c.idcliente FROM documento d INNER JOIN controle c on c.idcontrole = d.idcontrole WHERE", "");
+ nums = dataTable2.AsEnumerable().Select<DataRow, long>((DataRow x) => x.Field<long>("idcliente")).ToList<long>();
+ if (assintatura)
+ {
+ dataTable1 = sqlCommand.Select(condicaos3.CreateParameters(0), "SELECT * FROM ResponsavelAssinatura c WHERE ", "");
+ }
+ }
+ goto Label0;
+ }
+ }
+ else
+ {
+ clientes = new List<Gestor.Model.Domain.Seguros.Cliente>();
+ }
+ }
+ return clientes;
+ List<ResponsavelAssinatura> list = dataTable1.AsEnumerable().Select<DataRow, ResponsavelAssinatura>((DataRow x) => new ResponsavelAssinatura()
+ {
+ Id = x.Field<long>("Id"),
+ IdCliente = x.Field<long>("IdCliente"),
+ NomeResponsavel = x.Field<string>("NomeResponsavel").RemoverAcentos(),
+ DocumentoResponsavel = x.Field<string>("DocumentoResponsavel"),
+ EmailResponsavel = x.Field<string>("EmailResponsavel")
+ }).ToList<ResponsavelAssinatura>();
+ return (
+ from x in dataTable.AsEnumerable().ToList<DataRow>()
+ select new Gestor.Model.Domain.Seguros.Cliente()
+ {
+ Id = x.Field<long>("IDCLIENTE"),
+ IdEmpresa = x.Field<long>("IDEMPRESA"),
+ Nome = x.Field<string>("NOME").RemoverAcentos(),
+ Documento = x.Field<string>("CGCCPF"),
+ Cei = x.Field<string>("CEI"),
+ Rne = x.Field<string>("RNE"),
+ Caepf = x.Field<string>("CAEPF"),
+ DocumentoPrincipal = new TipoDocumento?((TipoDocumento)Enum.Parse(typeof(TipoDocumento), x.Field<int>("DOCUMENTOPRINCIPAL").ToString())),
+ Status = (nums.Any<long>((long d) => d == x.Field<long>("IDCLIENTE")) ? "ATIVO" : "INATIVO"),
+ ResponsavelAssinatura = list.Find((ResponsavelAssinatura r) => r.IdCliente == x.Field<long>("IDCLIENTE")),
+ Excluido = (x.Field<object>("EXCLUIDO") == null ? false : x.Field<object>("EXCLUIDO").ToString() == "1"),
+ MalaDireta = new bool?(x.Field<bool?>("MalaDireta").GetValueOrDefault(true)),
+ NomeSocialRg = x.Field<string>("nomesocialRg") ?? ""
+ }).ToList<Gestor.Model.Domain.Seguros.Cliente>();
+ }
+
+ public List<Gestor.Model.Domain.Seguros.Cliente> FindClienteDocumento(string documento)
+ {
+ List<Gestor.Model.Domain.Seguros.Cliente> clientes;
+ object connection;
+ List<Gestor.Model.Domain.Seguros.Cliente> clientes1 = new List<Gestor.Model.Domain.Seguros.Cliente>();
+ using (SessionFactoryImpl sessionFactory = this._unitOfWork.Session.SessionFactory as SessionFactoryImpl)
+ {
+ 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 = "SELECT c.IDEMPRESA, c.IDCLIENTE, c.NOME COLLATE Latin1_General_CI_AI AS NOME FROM cliente c WHERE (UPPER(c.CGCCPF) = @documento OR UPPER(c.CEI) = @documento OR UPPER(c.RNE) = @documento OR UPPER(c.CAEPF) = @documento) AND c.EXCLUIDO = '0'";
+ sqlCommand.Parameters.Add("@documento", SqlDbType.VarChar).Value = documento;
+ using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
+ {
+ while (sqlDataReader.Read())
+ {
+ clientes1.Add(new Gestor.Model.Domain.Seguros.Cliente()
+ {
+ Id = sqlDataReader.GetFieldValue<long>("idcliente", true, true),
+ IdEmpresa = sqlDataReader.GetFieldValue<long>("idempresa", true, true),
+ Nome = sqlDataReader.GetFieldValue<string>("nome", true, true),
+ Documento = documento
+ });
+ }
+ return clientes1;
+ }
+ }
+ }
+ else
+ {
+ clientes = clientes1;
+ }
+ }
+ }
+ return clientes;
+ }
+
+ public List<Gestor.Model.Domain.Seguros.Cliente> FindClienteVinculo(string filter, List<long> vinculos, long idempresa = 0L, bool acessoAgger = false, bool assinatura = false, TipoFiltroCliente tipoFiltroCliente = 2)
+ {
+ List<Gestor.Model.Domain.Seguros.Cliente> clientes;
+ object connection;
+ List<Gestor.Model.Domain.Seguros.Cliente> clientes1 = new List<Gestor.Model.Domain.Seguros.Cliente>();
+ using (SessionFactoryImpl sessionFactory = this._unitOfWork.Session.SessionFactory as SessionFactoryImpl)
+ {
+ if (sessionFactory != null)
+ {
+ connection = sessionFactory.ConnectionProvider.GetConnection();
+ }
+ else
+ {
+ connection = null;
+ }
+ using (SqlConnection sqlConnection = connection as SqlConnection)
+ {
+ if (sqlConnection != null)
+ {
+ using (SqlCommand item = sqlConnection.CreateCommand())
+ {
+ StringBuilder stringBuilder = new StringBuilder("SELECT TOP 25 c.IDEMPRESA, c.IDCLIENTE, c.NOME COLLATE Latin1_General_CI_AI AS NOME, c.NOMESOCIALRG COLLATE Latin1_General_CI_AI AS NOMESOCIAL, c.CGCCPF, c.CEI, c.RNE, ISNULL(c.DOCUMENTOPRINCIPAL, 0) AS DOCUMENTOPRINCIPAL, c.CAEPF, c.EXCLUIDO, c.MalaDireta, (CASE WHEN (SELECT COUNT(d.IDDOCUMENTO) FROM DOCUMENTO d INNER JOIN CONTROLE ctrl ON d.IDCONTROLE = ctrl.IDCONTROLE WHERE ctrl.IDCLIENTE = c.IDCLIENTE AND (d.EXCLUIDO IS NULL OR d.EXCLUIDO = '0') AND (d.VIGENCIA2 IS NULL OR d.VIGENCIA2 >= GETDATE() - 5) AND d.TIPO = 0 AND d.SITUACAO NOT IN(3,7)) > 0 THEN 0 ELSE 1 END) AS ATIVO FROM CLIENTE c WHERE ");
+ if (idempresa > (long)0)
+ {
+ stringBuilder.Append(" (c.IDEMPRESA = @empresa) AND ");
+ item.Parameters.Add("@empresa", SqlDbType.BigInt).Value = idempresa;
+ }
+ if (!acessoAgger)
+ {
+ stringBuilder.Append(" (c.excluido IS NULL OR c.excluido = @excluido) AND ");
+ item.Parameters.Add("@excluido", SqlDbType.VarChar, 1).Value = "0";
+ }
+ stringBuilder.Append((tipoFiltroCliente != TipoFiltroCliente.NomeDocumentos ? " (UPPER(c.NOME) COLLATE Latin1_General_CI_AI LIKE @filtro OR UPPER(c.NOMESOCIALRG) COLLATE Latin1_General_CI_AI LIKE @filtro) " : " (UPPER(c.NOME) COLLATE Latin1_General_CI_AI LIKE @filtro OR UPPER(c.NOMESOCIALRG) COLLATE Latin1_General_CI_AI LIKE @filtro OR UPPER(c.CGCCPF) LIKE @filtro OR UPPER(c.CEI) LIKE @filtro OR UPPER(c.RNE) LIKE @filtro OR UPPER(c.CAEPF) LIKE @filtro) "));
+ item.Parameters.Add("@filtro", SqlDbType.VarChar, 150).Value = string.Concat((tipoFiltroCliente == TipoFiltroCliente.Nome ? "" : "%"), filter, "%");
+ if (vinculos != null && vinculos.Count > 0)
+ {
+ stringBuilder.Append("AND ((SELECT COUNT(vp.IDVENDEDOR) FROM vendedorparcela vp INNER JOIN DOCUMENTO d ON vp.IDDOCUMENTO = d.IDDOCUMENTO INNER JOIN CONTROLE ctrl ON d.IDCONTROLE = ctrl.IDCONTROLE WHERE ctrl.IDCLIENTE = c.IDCLIENTE AND (d.EXCLUIDO IS NULL OR d.EXCLUIDO = '0') AND vp.IDVENDEDOR IN (");
+ StringBuilder stringBuilder1 = new StringBuilder();
+ for (int i = 0; i < vinculos.Count; i++)
+ {
+ if (i > 0)
+ {
+ stringBuilder1.Append(",");
+ }
+ string str = string.Format("@p{0}", i);
+ stringBuilder1.Append(str);
+ item.Parameters.Add(str, SqlDbType.BigInt).Value = vinculos[i];
+ }
+ stringBuilder1.Append(")");
+ stringBuilder.Append(stringBuilder1);
+ stringBuilder.Append(") > 0 OR ((SELECT COUNT(vp.IDVENDEDOR) FROM vendedorparcela vp INNER JOIN DOCUMENTO d ON vp.IDDOCUMENTO = d.IDDOCUMENTO INNER JOIN CONTROLE ctrl ON d.IDCONTROLE = ctrl.IDCONTROLE WHERE ctrl.IDCLIENTE = c.IDCLIENTE AND (d.EXCLUIDO IS NULL OR d.EXCLUIDO = '0') AND vp.IDVENDEDOR IN (");
+ stringBuilder.Append(stringBuilder1);
+ stringBuilder.Append(") = 0 AND (SELECT COUNT(d.IDDOCUMENTO) FROM DOCUMENTO d INNER JOIN CONTROLE ctrl ON d.IDCONTROLE = ctrl.IDCONTROLE WHERE ctrl.IDCLIENTE = c.IDCLIENTE AND (d.EXCLUIDO IS NULL OR d.EXCLUIDO = '0')) = 0))");
+ }
+ item.CommandText = stringBuilder.ToString();
+ using (SqlDataReader sqlDataReader = item.ExecuteReader())
+ {
+ while (sqlDataReader.Read())
+ {
+ clientes1.Add(new Gestor.Model.Domain.Seguros.Cliente()
+ {
+ Id = sqlDataReader.GetFieldValue<long>("idcliente", true, true),
+ IdEmpresa = sqlDataReader.GetFieldValue<long>("idempresa", true, true),
+ Nome = sqlDataReader.GetFieldValue<string>("nome", true, true),
+ Documento = sqlDataReader.GetFieldValue<string>("cgccpf", true, true),
+ Cei = sqlDataReader.GetFieldValue<string>("cei", true, true),
+ Rne = sqlDataReader.GetFieldValue<string>("rne", true, true),
+ Caepf = sqlDataReader.GetFieldValue<string>("caepf", true, true),
+ DocumentoPrincipal = new TipoDocumento?(sqlDataReader.GetFieldValue<TipoDocumento>("documentoprincipal", true, true)),
+ Status = (sqlDataReader.GetFieldValue<int>("ativo", true, true) == 0 ? "ATIVO" : "INATIVO"),
+ Excluido = sqlDataReader.GetFieldValue<int>("excluido", true, true) == 1,
+ NomeSocialRg = sqlDataReader.GetFieldValue<string>("nomesocial", true, true)
+ });
+ }
+ return clientes1;
+ }
+ }
+ }
+ else
+ {
+ clientes = clientes1;
+ }
+ }
+ }
+ return clientes;
+ }
+
+ public List<Gestor.Model.Domain.Seguros.Cliente> FindObsCliente(string obsCliente, long idempresa = 0L)
+ {
+ List<Condicao> condicaos = new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "idempresa",
+ Valores = idempresa.CriarValor<long>()
+ }
+ };
+ List<Condicao> condicaos1 = new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "anotacoes",
+ Valores = obsCliente.CriarValor<string>(),
+ Operador = Operador.Like
+ },
+ new Condicao()
+ {
+ Campo = "excluido",
+ Valores = null,
+ Grupo = 1,
+ Operacao = Operacao.Or
+ },
+ new Condicao()
+ {
+ Campo = "excluido",
+ Valores = "0".CriarValor<string>(),
+ Grupo = 1,
+ Operacao = Operacao.Or
+ }
+ };
+ if (idempresa > (long)0)
+ {
+ condicaos1.AddRange(condicaos);
+ }
+ return this._unitOfWork.Select(condicaos1.CreateParameters(0), "SELECT idcliente as id, idempresa, nome, cgccpf as documento, cei, rne, documentoprincipal, anotacoes, dtnasc as nascimento, idprofissao, VENCHABILI as vencimento, sexo, RendaMensal, pasta, MalaDireta, clidesde, ESTADOCIVI FROM cliente WHERE ", "").MapCliente();
+ }
+
+ public List<Gestor.Model.Domain.Seguros.Cliente> FindPastaCliente(string pastaCliente, bool busca = false, long idempresa = 0L)
+ {
+ List<Condicao> condicaos = new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "idempresa",
+ Valores = idempresa.CriarValor<long>()
+ }
+ };
+ List<Condicao> condicaos1 = new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "pasta",
+ Valores = pastaCliente.CriarValor<string>(),
+ Operador = (busca ? Operador.Igual : Operador.Like)
+ },
+ new Condicao()
+ {
+ Campo = "excluido",
+ Valores = null,
+ Grupo = 1,
+ Operacao = Operacao.Or
+ },
+ new Condicao()
+ {
+ Campo = "excluido",
+ Valores = "0".CriarValor<string>(),
+ Grupo = 1,
+ Operacao = Operacao.Or
+ }
+ };
+ if (idempresa > (long)0)
+ {
+ condicaos1.AddRange(condicaos);
+ }
+ return this._unitOfWork.Select(condicaos1.CreateParameters(0), "SELECT idcliente as id, idempresa, nome, cgccpf as documento, cei, rne, documentoprincipal, anotacoes, dtnasc as nascimento, idprofissao, VENCHABILI as vencimento, sexo, RendaMensal, pasta, MalaDireta, clidesde, ESTADOCIVI FROM cliente WHERE ", "").MapCliente();
+ }
+
+ public List<Gestor.Model.Domain.Seguros.Cliente> FindVinculo(string filter, long id)
+ {
+ List<Condicao> condicaos = this.CriarFiltro(filter);
+ condicaos.AddRange(new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "excluido",
+ Valores = null,
+ Grupo = 1,
+ Operacao = Operacao.Or
+ },
+ new Condicao()
+ {
+ Campo = "excluido",
+ Valores = "0".CriarValor<string>(),
+ Grupo = 1,
+ Operacao = Operacao.Or
+ },
+ new Condicao()
+ {
+ Campo = "idcliente",
+ Valores = id.CriarValor<long>(),
+ Operador = Operador.Diferente
+ }
+ });
+ DataTable dataTable = this._unitOfWork.Select(condicaos.CreateParameters(0), "SELECT TOP 200 idcliente as id, idempresa, nome, cgccpf as documento, cei, rne, documentoprincipal, anotacoes, dtnasc as nascimento, idprofissao, VENCHABILI as vencimento, sexo, RendaMensal, pasta, MalaDireta FROM cliente WHERE", "");
+ if (dataTable != null && dataTable.Rows.Count == 0)
+ {
+ return new List<Gestor.Model.Domain.Seguros.Cliente>();
+ }
+ return dataTable.MapCliente();
+ }
+
+ public Gestor.Model.Domain.Seguros.Cliente Merge(Gestor.Model.Domain.Seguros.Cliente cliente)
+ {
+ ResponsavelAssinatura responsavelAssinatura;
+ ClienteDb clienteDb = ApplicationMapper.Mapper.Map<Gestor.Model.Domain.Seguros.Cliente, ClienteDb>(cliente);
+ if (cliente.ResponsavelAssinatura == null || string.IsNullOrEmpty(cliente.ResponsavelAssinatura.NomeResponsavel))
+ {
+ responsavelAssinatura = null;
+ }
+ else
+ {
+ responsavelAssinatura = cliente.ResponsavelAssinatura;
+ }
+ ResponsavelAssinatura responsavelAssinatura1 = responsavelAssinatura;
+ if (responsavelAssinatura1 != null)
+ {
+ ResponsavelAssinaturaDb responsavelAssinaturaDb = ApplicationMapper.Mapper.Map<ResponsavelAssinatura, ResponsavelAssinaturaDb>(responsavelAssinatura1);
+ if (responsavelAssinaturaDb.Id != 0)
+ {
+ this._unitOfWork.Repository<ResponsavelAssinaturaDb>().Merge(responsavelAssinaturaDb);
+ }
+ else
+ {
+ this._unitOfWork.Repository<ResponsavelAssinaturaDb>().SaveOrUpdate(responsavelAssinaturaDb);
+ }
+ responsavelAssinatura1 = ApplicationMapper.Mapper.Map<ResponsavelAssinaturaDb, ResponsavelAssinatura>(responsavelAssinaturaDb);
+ }
+ base.Merge(clienteDb);
+ Gestor.Model.Domain.Seguros.Cliente cliente1 = ApplicationMapper.Mapper.Map<ClienteDb, Gestor.Model.Domain.Seguros.Cliente>(clienteDb);
+ cliente1.ResponsavelAssinatura = responsavelAssinatura1;
+ return cliente1;
+ }
+
+ public OrigemCliente Merge(OrigemCliente origem)
+ {
+ OrigemClienteDb origemClienteDb = ApplicationMapper.Mapper.Map<OrigemCliente, OrigemClienteDb>(origem);
+ this._unitOfWork.Repository<OrigemClienteDb>().Merge(origemClienteDb);
+ return ApplicationMapper.Mapper.Map<OrigemClienteDb, OrigemCliente>(origemClienteDb);
+ }
+
+ public Gestor.Model.Domain.Seguros.Cliente SaveOrUpdate(Gestor.Model.Domain.Seguros.Cliente cliente)
+ {
+ ResponsavelAssinatura responsavelAssinatura;
+ ClienteDb clienteDb = ApplicationMapper.Mapper.Map<Gestor.Model.Domain.Seguros.Cliente, ClienteDb>(cliente);
+ this.SaveOrUpdate(clienteDb);
+ if (cliente.ResponsavelAssinatura == null || string.IsNullOrEmpty(cliente.ResponsavelAssinatura.NomeResponsavel))
+ {
+ responsavelAssinatura = null;
+ }
+ else
+ {
+ responsavelAssinatura = cliente.ResponsavelAssinatura;
+ }
+ ResponsavelAssinatura id = responsavelAssinatura;
+ if (id != null)
+ {
+ id.IdCliente = clienteDb.Id;
+ ResponsavelAssinaturaDb responsavelAssinaturaDb = ApplicationMapper.Mapper.Map<ResponsavelAssinatura, ResponsavelAssinaturaDb>(id);
+ this._unitOfWork.Repository<ResponsavelAssinaturaDb>().SaveOrUpdate(responsavelAssinaturaDb);
+ id = ApplicationMapper.Mapper.Map<ResponsavelAssinaturaDb, ResponsavelAssinatura>(responsavelAssinaturaDb);
+ }
+ Gestor.Model.Domain.Seguros.Cliente cliente1 = ApplicationMapper.Mapper.Map<ClienteDb, Gestor.Model.Domain.Seguros.Cliente>(clienteDb);
+ cliente1.ResponsavelAssinatura = id;
+ return cliente1;
+ }
+
+ public OrigemCliente SaveOrUpdate(OrigemCliente origem)
+ {
+ OrigemClienteDb origemClienteDb = ApplicationMapper.Mapper.Map<OrigemCliente, OrigemClienteDb>(origem);
+ this._unitOfWork.Repository<OrigemClienteDb>().SaveOrUpdate(origemClienteDb);
+ return ApplicationMapper.Mapper.Map<OrigemClienteDb, OrigemCliente>(origemClienteDb);
+ }
+
+ private List<ClientesAtivosInativos> Select(SqlQueryCondition condition, string command, bool completo)
+ {
+ List<ClientesAtivosInativos> clientesAtivosInativos;
+ object connection;
+ List<long> nums3 = null;
+ List<long> nums4 = null;
+ DataTable dataTable1 = new DataTable();
+ DataTable dataTable2 = new DataTable();
+ DataTable dataTable3 = new DataTable();
+ DataTable dataTable4 = new DataTable();
+ DataTable dataTable5 = new DataTable();
+ DataTable dataTable6 = new DataTable();
+ DataTable dataTable7 = new DataTable();
+ DataTable dataTable8 = 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)
+ {
+ if (sqlConnection != null)
+ {
+ using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
+ {
+ sqlCommand.CommandTimeout = 15000;
+ if (completo)
+ {
+ Auxiliar.CriarAuxiliar(sqlCommand, false);
+ }
+ dataTable1 = sqlCommand.Select(condition, command, "");
+ if (dataTable1 == null || dataTable1.Rows.Count != 0)
+ {
+ for (List<long> i = dataTable1.AsEnumerable().Select<DataRow, long>((DataRow x) => x.Field<long>("id")).ToList<long>(); i.Count > 0; i = nums3)
+ {
+ List<long> nums5 = i.Take<long>(2000).ToList<long>();
+ List<Condicao> condicaos = new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "idcliente",
+ Valores = nums5.CriarValor<long>()
+ }
+ };
+ List<Condicao> condicaos1 = new List<Condicao>()
+ {
+ 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
+ },
+ new Condicao()
+ {
+ Campo = "d.vigencia2",
+ Valores = null,
+ Grupo = 2,
+ Operacao = Operacao.Or
+ }
+ };
+ Condicao condicao = new Condicao()
+ {
+ Campo = "d.vigencia2"
+ };
+ DateTime date = Funcoes.GetNetworkTime().Date;
+ condicao.Valores = date.AddDays(-5).CriarValor<DateTime>();
+ condicao.Grupo = 2;
+ condicao.Operacao = Operacao.Or;
+ condicao.Operador = Operador.Maior;
+ condicaos1.Add(condicao);
+ condicaos1.Add(new Condicao()
+ {
+ Campo = "d.situacao",
+ Valores = new List<object>()
+ {
+ 1,
+ 2,
+ 4
+ }
+ });
+ List<Condicao> condicaos2 = condicaos1;
+ condicaos2.AddRange(condicaos);
+ DataTable dataTable9 = sqlCommand.Select(condicaos2.CreateParameters(0), "SELECT DISTINCT c.idcliente, r.idramo, r.nome AS ramo, d.iddocumento as id, d.idcontrole FROM documento d INNER JOIN controle c ON c.idcontrole = d.idcontrole INNER JOIN ramo r ON r.idramo = c.idramo WHERE", "");
+ List<long> nums6 = dataTable9.AsEnumerable().Select<DataRow, long>((DataRow x) => x.Field<long>("id")).ToList<long>();
+ List<long> nums7 = dataTable9.AsEnumerable().Select<DataRow, long>((DataRow x) => x.Field<long>("id")).ToList<long>();
+ dataTable2.Merge(dataTable9);
+ List<Condicao> condicaos3 = new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "ORDEM",
+ Valores = "1".CriarValor<string>(),
+ Grupo = 1,
+ Operacao = Operacao.Or
+ },
+ new Condicao()
+ {
+ Campo = "PADRAO",
+ Valores = "1".CriarValor<string>(),
+ Grupo = 1,
+ Operacao = Operacao.Or
+ }
+ };
+ condicaos3.AddRange(condicaos);
+ dataTable9 = sqlCommand.Select(condicaos3.CreateParameters(0), "SELECT DISTINCT idcliend, cep, cidade, uf as estado, idcliente as id, endereco, numero, comp_end as complemento FROM cliendereco WHERE ", "");
+ dataTable3.Merge(dataTable9);
+ dataTable9 = sqlCommand.Select(condicaos.CreateParameters(0), "SELECT DISTINCT idclitelefone, ddd, fone, idcliente as id FROM clitelefone WHERE ", "");
+ dataTable5.Merge(dataTable9);
+ dataTable9 = sqlCommand.Select(condicaos.CreateParameters(0), "SELECT DISTINCT idclimail, email, idcliente as id FROM climail WHERE ", "");
+ dataTable4.Merge(dataTable9);
+ if (completo)
+ {
+ for (List<long> j = nums5; j.Count > 0; j = nums4)
+ {
+ List<long> nums8 = j.Take<long>(1000).ToList<long>();
+ List<Condicao> condicaos4 = new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "idcliente1",
+ Valores = nums8.CriarValor<long>(),
+ Grupo = 1,
+ Operacao = Operacao.Or
+ },
+ new Condicao()
+ {
+ Campo = "idcliente2",
+ Valores = nums8.CriarValor<long>(),
+ Grupo = 1,
+ Operacao = Operacao.Or
+ }
+ };
+ dataTable9 = sqlCommand.Select(condicaos4.CreateParameters(0), "SELECT * FROM clivinculo c WHERE", "");
+ dataTable7.Merge(dataTable9);
+ nums4 = (nums8.Count < 1000 ? new List<long>() : j.Except<long>(nums8).ToList<long>());
+ }
+ while (nums6.Count > 0)
+ {
+ List<long> nums9 = nums6.Take<long>(2000).ToList<long>();
+ List<Condicao> condicaos5 = new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "vp.iddocumento",
+ Valores = nums9.CriarValor<long>()
+ }
+ };
+ dataTable9 = sqlCommand.Select(condicaos5.CreateParameters(0), "SELECT DISTINCT vp.idvendedor, vp.iddocumento FROM vendedorparcela vp WHERE", "");
+ dataTable6.Merge(dataTable9);
+ nums6 = (nums9.Count < 2000 ? new List<long>() : nums6.Except<long>(nums9).ToList<long>());
+ }
+ while (nums7.Count > 0)
+ {
+ List<long> nums10 = nums7.Take<long>(2000).ToList<long>();
+ List<Condicao> condicaos6 = new List<Condicao>()
+ {
+ new Condicao()
+ {
+ Campo = "iddocumento",
+ Valores = nums10.CriarValor<long>()
+ }
+ };
+ dataTable9 = sqlCommand.Select(condicaos6.CreateParameters(0), "select IDDOCUMENTO, case when(idestipulante is null ) then 0 else idestipulante end as idestipulante from documento where", "");
+ dataTable8.Merge(dataTable9);
+ nums7 = (nums10.Count < 2000 ? new List<long>() : nums6.Except<long>(nums10).ToList<long>());
+ }
+ }
+ nums3 = (nums5.Count < 2000 ? new List<long>() : i.Except<long>(nums5).ToList<long>());
+ }
+ goto Label0;
+ }
+ else
+ {
+ clientesAtivosInativos = new List<ClientesAtivosInativos>();
+ }
+ }
+ }
+ else
+ {
+ clientesAtivosInativos = new List<ClientesAtivosInativos>();
+ }
+ }
+ return clientesAtivosInativos;
+ Label0:
+ Regex regex = new Regex("[^\\d]");
+ DateTime networkTime = Funcoes.GetNetworkTime();
+ Array values = Enum.GetValues(typeof(EstadoCivil));
+ List<ClientesAtivosInativos> clientesAtivosInativos1 = new List<ClientesAtivosInativos>();
+ dataTable1.AsEnumerable().ToList<DataRow>().ForEach((DataRow x) => {
+ DateTime? nullable;
+ DateTime dateTime;
+ List<long> list;
+ List<long> nums;
+ DateTime? nullable1;
+ string str;
+ DateTime? nullable2;
+ string nome;
+ int year;
+ int? nullable3;
+ DateTime? nullable4;
+ List<DataRow> dataRows = dataTable2.AsEnumerable().Where<DataRow>((DataRow y) => y.Field<long>("idcliente") == x.Field<long>("id")).ToList<DataRow>();
+ List<DataRow> dataRows1 = dataRows;
+ Func<DataRow, long> u003cu003e9_325 = ClienteRepository.u003cu003ec.u003cu003e9__32_5;
+ if (u003cu003e9_325 == null)
+ {
+ u003cu003e9_325 = (DataRow d) => d.Field<long>("id");
+ ClienteRepository.u003cu003ec.u003cu003e9__32_5 = u003cu003e9_325;
+ }
+ List<long> list1 = dataRows1.Select<DataRow, long>(u003cu003e9_325).ToList<long>();
+ DataRow dataRow = dataTable3.AsEnumerable().FirstOrDefault<DataRow>((DataRow t) => t.Field<long>("id") == x.Field<long>("id"));
+ EnumerableRowCollection<DataRow> dataRows2 = dataTable4.AsEnumerable().Where<DataRow>((DataRow t) => t.Field<long>("id") == x.Field<long>("id"));
+ Func<DataRow, string> u003cu003e9_328 = ClienteRepository.u003cu003ec.u003cu003e9__32_8;
+ if (u003cu003e9_328 == null)
+ {
+ u003cu003e9_328 = (DataRow t) => t.Field<string>("email");
+ ClienteRepository.u003cu003ec.u003cu003e9__32_8 = u003cu003e9_328;
+ }
+ EnumerableRowCollection<string> strs = dataRows2.Select<DataRow, string>(u003cu003e9_328);
+ if (list1.Count > 0)
+ {
+ EnumerableRowCollection<DataRow> dataRows3 = dataTable6.AsEnumerable().Where<DataRow>((DataRow y) => list1.Contains(y.Field<long>("iddocumento")));
+ Func<DataRow, long> u003cu003e9_3210 = ClienteRepository.u003cu003ec.u003cu003e9__32_10;
+ if (u003cu003e9_3210 == null)
+ {
+ u003cu003e9_3210 = (DataRow y) => y.Field<long>("idvendedor");
+ ClienteRepository.u003cu003ec.u003cu003e9__32_10 = u003cu003e9_3210;
+ }
+ list = dataRows3.Select<DataRow, long>(u003cu003e9_3210).Distinct<long>().ToList<long>();
+ }
+ else
+ {
+ list = null;
+ }
+ List<long> nums1 = list;
+ EnumerableRowCollection<DataRow> dataRows4 = dataTable5.AsEnumerable().Where<DataRow>((DataRow t) => t.Field<long>("id") == x.Field<long>("id"));
+ Func<DataRow, Gestor.Model.Domain.Seguros.ClienteTelefone> u003cu003e9_3212 = ClienteRepository.u003cu003ec.u003cu003e9__32_12;
+ if (u003cu003e9_3212 == null)
+ {
+ u003cu003e9_3212 = (DataRow t) => new Gestor.Model.Domain.Seguros.ClienteTelefone()
+ {
+ Prefixo = t.Field<string>("ddd"),
+ Numero = t.Field<string>("fone")
+ };
+ ClienteRepository.u003cu003ec.u003cu003e9__32_12 = u003cu003e9_3212;
+ }
+ List<Gestor.Model.Domain.Seguros.ClienteTelefone> clienteTelefones = dataRows4.Select<DataRow, Gestor.Model.Domain.Seguros.ClienteTelefone>(u003cu003e9_3212).ToList<Gestor.Model.Domain.Seguros.ClienteTelefone>();
+ List<Gestor.Model.Domain.Seguros.ClienteTelefone> clienteTelefones1 = clienteTelefones;
+ Func<Gestor.Model.Domain.Seguros.ClienteTelefone, string> u003cu003e9_3213 = ClienteRepository.u003cu003ec.u003cu003e9__32_13;
+ if (u003cu003e9_3213 == null)
+ {
+ u003cu003e9_3213 = (Gestor.Model.Domain.Seguros.ClienteTelefone t) => string.Concat(t.Prefixo, " ", t.Numero);
+ ClienteRepository.u003cu003ec.u003cu003e9__32_13 = u003cu003e9_3213;
+ }
+ IEnumerable<string> strs1 = clienteTelefones1.Select<Gestor.Model.Domain.Seguros.ClienteTelefone, string>(u003cu003e9_3213);
+ List<DataRow> dataRows5 = dataRows;
+ Func<DataRow, string> u003cu003e9_3214 = ClienteRepository.u003cu003ec.u003cu003e9__32_14;
+ if (u003cu003e9_3214 == null)
+ {
+ u003cu003e9_3214 = (DataRow y) => y.Field<string>("ramo");
+ ClienteRepository.u003cu003ec.u003cu003e9__32_14 = u003cu003e9_3214;
+ }
+ IEnumerable<string> strs2 = dataRows5.Select<DataRow, string>(u003cu003e9_3214);
+ Func<string, string> u003cu003e9_3215 = ClienteRepository.u003cu003ec.u003cu003e9__32_15;
+ if (u003cu003e9_3215 == null)
+ {
+ u003cu003e9_3215 = (string r) => r;
+ ClienteRepository.u003cu003ec.u003cu003e9__32_15 = u003cu003e9_3215;
+ }
+ List<string> list2 = strs2.OrderBy<string, string>(u003cu003e9_3215).Distinct<string>().ToList<string>();
+ DataRow dataRow1 = dataTable7.AsEnumerable().FirstOrDefault<DataRow>((DataRow v) => v.Field<long>("idcliente1") == x.Field<long>("id")) ?? dataTable7.AsEnumerable().FirstOrDefault<DataRow>((DataRow v) => v.Field<long>("idcliente2") == x.Field<long>("id"));
+ if (list1.Count > 0)
+ {
+ DataTable dataTable = dataTable8;
+ if (dataTable != null)
+ {
+ EnumerableRowCollection<DataRow> dataRows6 = dataTable.AsEnumerable().Where<DataRow>((DataRow y) => list1.Contains(y.Field<long>("iddocumento")));
+ Func<DataRow, long> u003cu003e9_3219 = ClienteRepository.u003cu003ec.u003cu003e9__32_19;
+ if (u003cu003e9_3219 == null)
+ {
+ u003cu003e9_3219 = (DataRow y) => y.Field<long>("idestipulante");
+ ClienteRepository.u003cu003ec.u003cu003e9__32_19 = u003cu003e9_3219;
+ }
+ nums = dataRows6.Select<DataRow, long>(u003cu003e9_3219).Distinct<long>().ToList<long>();
+ }
+ else
+ {
+ nums = null;
+ }
+ }
+ else
+ {
+ nums = null;
+ }
+ List<long> nums2 = nums;
+ List<Gestor.Model.Domain.Seguros.Vendedor> vendedors = (nums1 == null || nums1.Count <= 0 ? new List<Gestor.Model.Domain.Seguros.Vendedor>() : (
+ from v in Auxiliar.Vendedores
+ where nums1.Contains(v.Id)
+ select v).ToList<Gestor.Model.Domain.Seguros.Vendedor>());
+ List<Estipulante> estipulantes = (nums2 == null || nums2.Count <= 0 ? new List<Estipulante>() : (
+ from e in Auxiliar.Estipulantes
+ where nums2.Contains(e.Id)
+ select e).ToList<Estipulante>());
+ string description = "";
+ foreach (object obj in values)
+ {
+ if (obj.GetOldValue<object>() != x.Field<string>("EstadoCivi"))
+ {
+ continue;
+ }
+ description = ((EstadoCivil)obj).GetDescription<EstadoCivil>();
+ }
+ ClientesAtivosInativos clientesAtivosInativo = new ClientesAtivosInativos()
+ {
+ EntidadeCliente = new Gestor.Model.Domain.Seguros.Cliente()
+ {
+ Id = x.Field<long>("id"),
+ Nome = x.Field<string>("nome"),
+ Telefones = new ObservableCollection<Gestor.Model.Domain.Seguros.ClienteTelefone>(clienteTelefones),
+ MalaDireta = new bool?(x.Field<bool?>("MalaDireta").GetValueOrDefault(true))
+ },
+ Id = x.Field<long>("id"),
+ Nome = x.Field<string>("nome")
+ };
+ if (x.Field<object>("nascimento") == null)
+ {
+ nullable = null;
+ nullable1 = nullable;
+ }
+ else
+ {
+ nullable1 = new DateTime?(DateTime.Parse(x.Field<object>("nascimento").ToString()));
+ }
+ clientesAtivosInativo.Nascimento = nullable1;
+ if (x.Field<object>("nascimento") == null)
+ {
+ str = "";
+ }
+ else
+ {
+ dateTime = x.Field<DateTime>("nascimento");
+ str = dateTime.ToString("dd/MM");
+ }
+ clientesAtivosInativo.Aniversario = str;
+ clientesAtivosInativo.Documento = x.Field<string>("documento");
+ if (x.Field<object>("vencimento") == null)
+ {
+ nullable = null;
+ nullable2 = nullable;
+ }
+ else
+ {
+ nullable2 = x.Field<DateTime?>("vencimento");
+ }
+ clientesAtivosInativo.VencimentoCnh = nullable2;
+ clientesAtivosInativo.Ramo = string.Join(" | ", list2);
+ clientesAtivosInativo.Ativo = (list2.Any<string>() ? "SIM" : "NÃO");
+ if (x.Field<object>("idprofissao") != null)
+ {
+ Profissao profissao = Auxiliar.Profissoes.FirstOrDefault<Profissao>((Profissao p) => p.Id == long.Parse(x.Field<object>("idprofissao").ToString()));
+ if (profissao != null)
+ {
+ nome = profissao.Nome;
+ }
+ else
+ {
+ nome = null;
+ }
+ }
+ else
+ {
+ nome = "";
+ }
+ clientesAtivosInativo.Profissao = nome;
+ clientesAtivosInativo.Telefone = string.Join(" | ", strs1);
+ clientesAtivosInativo.Cep = (dataRow != null ? dataRow.Field<string>("cep") : null);
+ clientesAtivosInativo.Endereco = (dataRow != null ? dataRow.Field<string>("endereco") : null);
+ clientesAtivosInativo.Numero = (dataRow != null ? dataRow.Field<string>("numero") : null);
+ clientesAtivosInativo.Complemento = (dataRow != null ? dataRow.Field<string>("complemento") : null);
+ clientesAtivosInativo.Cidade = (dataRow != null ? dataRow.Field<string>("cidade") : null);
+ clientesAtivosInativo.PastaCliente = x.Field<string>("pasta");
+ clientesAtivosInativo.Estado = (dataRow != null ? dataRow.Field<string>("estado") : null);
+ clientesAtivosInativo.Email = string.Join(" | ", strs);
+ clientesAtivosInativo.RecebeEmail = (x.Field<bool?>("maladireta").GetValueOrDefault() ? "SIM" : "NÃO");
+ List<Gestor.Model.Domain.Seguros.Vendedor> vendedors1 = vendedors;
+ Func<Gestor.Model.Domain.Seguros.Vendedor, string> u003cu003e9_3223 = ClienteRepository.u003cu003ec.u003cu003e9__32_23;
+ if (u003cu003e9_3223 == null)
+ {
+ u003cu003e9_3223 = (Gestor.Model.Domain.Seguros.Vendedor v) => v.Nome;
+ ClienteRepository.u003cu003ec.u003cu003e9__32_23 = u003cu003e9_3223;
+ }
+ clientesAtivosInativo.Vendedor = string.Join(" | ", vendedors1.Select<Gestor.Model.Domain.Seguros.Vendedor, string>(u003cu003e9_3223));
+ clientesAtivosInativo.TipoPessoa = (string.IsNullOrEmpty(x.Field<string>("documento")) ? "FÍSICA" : (regex.Replace(x.Field<string>("documento"), "").Length > 11 ? "JURÍDICA" : "FÍSICA"));
+ clientesAtivosInativo.Sexo = (x.Field<object>("sexo") == null || !(x.Field<object>("sexo").ToString() != "") || string.IsNullOrWhiteSpace(x.Field<object>("sexo").ToString()) ? "" : ((Sexo)Enum.Parse(typeof(Sexo), x.Field<object>("sexo").ToString())).GetDescription<Sexo>());
+ clientesAtivosInativo.RendaMensal = (x.Field<object>("RendaMensal") == null ? decimal.Zero : x.Field<decimal>("RendaMensal"));
+ clientesAtivosInativo.Parentesco = (dataRow1 == null ? "NÃO" : "SIM");
+ if (x.Field<object>("nascimento") != null)
+ {
+ DateTime dateTime1 = DateTime.Parse(x.Field<object>("nascimento").ToString());
+ int num = networkTime.Year;
+ dateTime = DateTime.Parse(x.Field<object>("nascimento").ToString());
+ if (dateTime1 > networkTime.AddYears(-(num - dateTime.Year)))
+ {
+ int year1 = networkTime.Year;
+ dateTime = DateTime.Parse(x.Field<object>("nascimento").ToString());
+ year = year1 - dateTime.Year - 1;
+ }
+ else
+ {
+ int num1 = networkTime.Year;
+ dateTime = DateTime.Parse(x.Field<object>("nascimento").ToString());
+ year = num1 - dateTime.Year;
+ }
+ nullable3 = new int?(year);
+ }
+ else
+ {
+ nullable3 = null;
+ }
+ clientesAtivosInativo.Idade = nullable3;
+ nullable = x.Field<DateTime?>("clidesde");
+ if (!nullable.HasValue)
+ {
+ nullable = null;
+ nullable4 = nullable;
+ }
+ else
+ {
+ nullable4 = x.Field<DateTime?>("clidesde");
+ }
+ clientesAtivosInativo.ClienteDesde = nullable4;
+ clientesAtivosInativo.EstadoCivil = description;
+ List<Estipulante> estipulantes1 = estipulantes;
+ Func<Estipulante, string> u003cu003e9_3224 = ClienteRepository.u003cu003ec.u003cu003e9__32_24;
+ if (u003cu003e9_3224 == null)
+ {
+ u003cu003e9_3224 = (Estipulante v) => v.Nome;
+ ClienteRepository.u003cu003ec.u003cu003e9__32_24 = u003cu003e9_3224;
+ }
+ clientesAtivosInativo.Estipulantes = string.Join(" | ", estipulantes1.Select<Estipulante, string>(u003cu003e9_3224));
+ clientesAtivosInativos1.Add(clientesAtivosInativo);
+ });
+ return clientesAtivosInativos1;
+ }
+
+ public List<OrigemCliente> Sincronize(List<OrigemCliente> origens, long id)
+ {
+ if (origens == null)
+ {
+ return null;
+ }
+ IEnumerable<OrigemClienteDb> origemClienteDbs = (
+ from x in this._unitOfWork.Query<OrigemClienteDb>()
+ where x.Cliente.Id == id
+ select x).ToList<OrigemClienteDb>().Where<OrigemClienteDb>((OrigemClienteDb x) => {
+ List<OrigemCliente> origemClientes = origens;
+ Func<OrigemCliente, long> u003cu003e9_304 = ClienteRepository.u003cu003ec.u003cu003e9__30_4;
+ if (u003cu003e9_304 == null)
+ {
+ u003cu003e9_304 = (OrigemCliente i) => i.Id;
+ ClienteRepository.u003cu003ec.u003cu003e9__30_4 = u003cu003e9_304;
+ }
+ return !origemClientes.Select<OrigemCliente, long>(u003cu003e9_304).Contains<long>(x.Id);
+ });
+ if (origemClienteDbs != null)
+ {
+ origemClienteDbs.ForEach<OrigemClienteDb>((OrigemClienteDb x) => this._unitOfWork.Repository<OrigemClienteDb>().Delete(x));
+ }
+ else
+ {
+ }
+ origens.ForEach((OrigemCliente x) => x = (x.Id > (long)0 ? this.Merge(x) : this.SaveOrUpdate(x)));
+ return origens;
+ }
+
+ private async Task<IEnumerable<Gestor.Model.Relatorios.ClienteTelefone>> TelefonesClientes()
+ {
+ IEnumerable<Gestor.Model.Relatorios.ClienteTelefone> clienteTelefones;
+ object connection;
+ List<Gestor.Model.Relatorios.ClienteTelefone> clienteTelefones1 = new List<Gestor.Model.Relatorios.ClienteTelefone>();
+ SessionFactoryImpl sessionFactory = this._unitOfWork.Session.SessionFactory as SessionFactoryImpl;
+ 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 = "SELECT idcliente,ddd,fone FROM clitelefone ORDER BY idcliente,ordem";
+ sqlCommand.CommandTimeout = 1000;
+ using (SqlDataReader sqlDataReader = await sqlCommand.ExecuteReaderAsync())
+ {
+ while (true)
+ {
+ if (!await sqlDataReader.ReadAsync())
+ {
+ break;
+ }
+ List<Gestor.Model.Relatorios.ClienteTelefone> clienteTelefones2 = clienteTelefones1;
+ Gestor.Model.Relatorios.ClienteTelefone clienteTelefone = new Gestor.Model.Relatorios.ClienteTelefone();
+ Gestor.Model.Relatorios.Cliente cliente = new Gestor.Model.Relatorios.Cliente()
+ {
+ Id = (long)sqlDataReader["idcliente"]
+ };
+ clienteTelefone.Cliente = cliente;
+ clienteTelefone.Prefixo = sqlDataReader["ddd"].ToString();
+ clienteTelefone.Numero = sqlDataReader["fone"].ToString();
+ clienteTelefones2.Add(clienteTelefone);
+ }
+ }
+ sqlDataReader = null;
+ }
+ sqlCommand = null;
+ }
+ else
+ {
+ clienteTelefones = null;
+ clienteTelefones1 = null;
+ return clienteTelefones;
+ }
+ }
+ sqlConnection = null;
+ clienteTelefones = clienteTelefones1;
+ clienteTelefones1 = null;
+ return clienteTelefones;
+ }
+ }
+}
\ No newline at end of file |