dbo.LN_contrapartes_lista_email

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
/*
select * from clientes where cli_codigo = 6143
exec LN_contrapartes_lista_email 7500717,47,2

*/


CREATE OR ALTER PROCEDURE dbo.LN_contrapartes_lista_email 
@p_cliente int,
@p_servicio_contratado int, 
@especial int = 0
AS
BEGIN
 SET NOCOUNT ON;
if @especial = 0 
 select  LN_contrapartes.contraparte, padrones.nombre_persona,
   padrones.identificador, padrones.numero, padrones.identificacion,
   identificadores.abreviatura, identificadores.country_code,
   country.country_name,
   LN_contrapartes.nombre_contacto, LN_contrapartes.email_contacto,
   LN_contrapartes.telefono_contacto,
   convert(varchar, LN_contrapartes.clave_cambio, 103) as clave_cambio,
   convert(varchar, LN_contrapartes.clave_expira, 103) as clave_expira,
   convert(varchar, LN_contrapartes.last_login, 103) as last_login,
   convert(varchar, LN_gestiones.desde, 121) AS desde,
   convert(varchar, LN_gestiones.hasta, 121) AS hasta, nombre_familia as familia
   from LN_contrapartes 
   inner join padrones on LN_contrapartes.id_padron = padrones.id_padron
   inner join identificadores on padrones.identificador = identificadores.identificador
   inner join country on identificadores.country_code = country.country_code
   inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
   inner join ln_contraparte_familias on (LN_contrapartes.contraparte = ln_contraparte_familias.contraparte and ln_contraparte_familias.activo = 1) 
   inner join ln_familias on ln_familias.familia = LN_contraparte_familias.familia

   where LN_contrapartes.cliente = @p_cliente
   and LN_contrapartes.activo = 1
   and LN_contrapartes.subcontratista = 0
   and LN_contrapartes.email_contacto IS NOT NULL
   and LN_gestiones.desde <= GETDATE()
   and LN_gestiones.hasta >= GETDATE() 
   and ln_gestiones.vigente = 1 
   and LN_contrapartes.servicio_contratado = @p_servicio_contratado
   order by padrones.nombre_persona
if @especial = 1
 select  LN_contrapartes.contraparte, padrones.nombre_persona,
  padrones.identificador, padrones.numero, padrones.identificacion,
  identificadores.abreviatura, identificadores.country_code,
  country.country_name,
  LN_contrapartes.nombre_contacto, LN_contrapartes.email_contacto,
  LN_contrapartes.telefono_contacto,
  convert(varchar, LN_contrapartes.clave_cambio, 103) as clave_cambio,
  convert(varchar, LN_contrapartes.clave_expira, 103) as clave_expira,
  convert(varchar, LN_contrapartes.last_login, 103) as last_login,
  convert(varchar, LN_gestiones.desde, 121) AS desde,
  convert(varchar, LN_gestiones.hasta, 121) AS hasta, nombre_familia as familia
  from LN_contrapartes 
  inner join padrones on LN_contrapartes.id_padron = padrones.id_padron
  inner join identificadores on padrones.identificador = identificadores.identificador
  inner join country on identificadores.country_code = country.country_code
  inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
  inner join ln_contraparte_familias on (LN_contrapartes.contraparte = ln_contraparte_familias.contraparte and ln_contraparte_familias.activo = 1) 
  inner join ln_familias on ln_familias.familia = LN_contraparte_familias.familia

  where LN_contrapartes.cliente = @p_cliente
  and LN_contrapartes.activo = 1
  and LN_contrapartes.subcontratista = 0
  and LN_contrapartes.email_contacto IS NOT NULL
  and LN_gestiones.desde <= GETDATE()
  and LN_gestiones.hasta >= GETDATE() 
  and ln_gestiones.vigente = 1 
  and LN_contrapartes.servicio_contratado = @p_servicio_contratado
  and clave_temporal = 1
  and ln_familias.nombre_familia like 'VRM-%'
  order by padrones.nombre_persona

if @especial = 2
 select  LN_contrapartes.contraparte, padrones.nombre_persona,
  padrones.identificador, padrones.numero, padrones.identificacion,
  identificadores.abreviatura, identificadores.country_code,
  country.country_name,
  LN_contrapartes.nombre_contacto, LN_contrapartes.email_contacto,
  LN_contrapartes.telefono_contacto,
  convert(varchar, LN_contrapartes.clave_cambio, 103) as clave_cambio,
  convert(varchar, LN_contrapartes.clave_expira, 103) as clave_expira,
  convert(varchar, LN_contrapartes.last_login, 103) as last_login,
  convert(varchar, LN_gestiones.desde, 121) AS desde,
  convert(varchar, LN_gestiones.hasta, 121) AS hasta, nombre_familia as familia, 
  (SELECT COUNT(*) 
  FROM LN_docs
        INNER JOIN LN_contraparte_familias ON LN_docs.contraparte = LN_contraparte_familias.contraparte
        INNER JOIN LN_familias ON LN_contraparte_familias.familia = LN_familias.familia
        INNER JOIN LN_set_tipo_doc ON (LN_familias.set_doc = LN_set_tipo_doc.set_doc
                                        AND LN_docs.tipo_doc = LN_set_tipo_doc.tipo_doc
                                        AND LN_set_tipo_doc.activo = 1)   
        WHERE LN_docs.contraparte = LN_contrapartes.contraparte
  AND LN_docs.estado_doc <> 'X'
  AND LN_docs.gestion = LN_gestiones.gestion 
  AND LN_docs.aplica = 1) as total_documentos, 

  (SELECT COUNT(*) 
   FROM LN_docs
   INNER JOIN LN_contraparte_familias ON LN_docs.contraparte = LN_contraparte_familias.contraparte
   INNER JOIN LN_familias ON LN_contraparte_familias.familia = LN_familias.familia
   INNER JOIN LN_set_tipo_doc ON (LN_familias.set_doc = LN_set_tipo_doc.set_doc
           AND LN_docs.tipo_doc = LN_set_tipo_doc.tipo_doc
           AND LN_set_tipo_doc.activo = 1)   
   WHERE LN_docs.contraparte = LN_contrapartes.contraparte
   AND LN_docs.estado_doc in ('I','V')
   AND LN_docs.gestion = LN_gestiones.gestion 
   AND LN_docs.aplica = 1) as total_cumplidos 


  from LN_contrapartes 
  inner join padrones on LN_contrapartes.id_padron = padrones.id_padron
  inner join identificadores on padrones.identificador = identificadores.identificador
  inner join country on identificadores.country_code = country.country_code
  inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
  inner join ln_contraparte_familias on (LN_contrapartes.contraparte = ln_contraparte_familias.contraparte and ln_contraparte_familias.activo = 1) 
  inner join ln_familias on ln_familias.familia = LN_contraparte_familias.familia

  where LN_contrapartes.cliente = @p_cliente
  and LN_contrapartes.activo = 1
  and LN_contrapartes.subcontratista = 0
  and LN_contrapartes.email_contacto IS NOT NULL
  and LN_gestiones.desde <= GETDATE()
  and LN_gestiones.hasta >= GETDATE() 
  and ln_gestiones.vigente = 1 
  and LN_contrapartes.servicio_contratado = @p_servicio_contratado
  order by padrones.nombre_persona
END 
GO