dbo.LN_Reporte_Novedades_Monitoreo

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

/*

exec LN_Reporte_Novedades_Monitoreo 2046807993

*/

CREATE OR ALTER PROCEDUREdbo.LN_Reporte_Novedades_Monitoreo 
@cliente int
AS
BEGIN
    SET NOCOUNT ON;
declare @dias int = 31
declare @diasBancos int = 31

declare @cuit varchar(11)='30606517706'
set @cuit ='30552847764'
set @cuit = '30708076895'
set @cuit='30502459305'


DECLARE @p_5anios smalldatetime = getdate() - 1825
DECLARE @p_2anios smalldatetime = getdate() - 730
DECLARE @p_1anio  smalldatetime = getdate() - 365

select  monitoreos_resultados.cuit, padrones.nombre_persona,tipo_novedad,tipos_causas.nombre_causa, 
            /* max(monitoreos_resultados.fecha) as ultima_novedad */
            convert(varchar(10),monitoreos_resultados.fecha,103) as fecha_novedad,
            /* monitoreos_resultados.*, */ 
            null as nro_cheque,null as importe_cheque,null as fecha_rechazo, 
   null as fecha_pago_multa, 
   null as fecha_pago_cheque, 
   null as banco, 
            null as situacion,null as importe,null as periodo,null as acumulado_cheques
   from monitoreos_resultados
   inner join padrones ON monitoreos_resultados.cuit = padrones.identificacion 
   inner join LN_contrapartes on padrones.id_padron = LN_contrapartes.id_padron
   inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
   inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
   inner join LN_familias on LN_familias.familia = LN_contraparte_familias.familia
            left join juicios on juicios.id_juicio=monitoreos_resultados.id_tabla
            left join tipos_causas on tipos_causas.id_tipo_causa = juicios.id_tipo_causa
   where LN_contrapartes.cliente = @cliente
   and LN_contrapartes.activo = 1
   and LN_familias.visible = 1 
   and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
   and LN_contrapartes.activo = 1
   and LN_contrapartes.subcontratista = 0
   and LN_contraparte_familias.activo = 1 
   and monitoreos_resultados.fecha > getdate() - @diasBancos
   and monitoreos_resultados.seccion_mantenimiento <> 7
   --         and ln_contrapartes.id_padron in (select id_padron from padrones where identificacion = @cuit)
   
            and monitoreos_resultados.tipo_novedad='Fiscales_arba'


union 



select  monitoreos_resultados.cuit, padrones.nombre_persona,tipo_novedad,tipos_causas.nombre_causa, 
            /* max(monitoreos_resultados.fecha) as ultima_novedad */
            convert(varchar(10),monitoreos_resultados.fecha,103) as fecha_novedad,
            /* monitoreos_resultados.*, */ 
            null as nro_cheque,null as importe_cheque,null as fecha_rechazo, 
      null as fecha_pago_multa, 
   null as fecha_pago_cheque, 

   null as banco, 
            null as situacion,null as importe,null as periodo,null as acumulado_cheques 
   from monitoreos_resultados
   inner join padrones ON monitoreos_resultados.cuit = padrones.identificacion 
   inner join LN_contrapartes on padrones.id_padron = LN_contrapartes.id_padron
   inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
   inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
   inner join LN_familias on LN_familias.familia = LN_contraparte_familias.familia
            inner join juicios on juicios.id_juicio=monitoreos_resultados.id_tabla
            inner join tipos_causas on tipos_causas.id_tipo_causa = juicios.id_tipo_causa
   where LN_contrapartes.cliente = @cliente
   and LN_contrapartes.activo = 1
   and LN_familias.visible = 1 
   and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
   and LN_contrapartes.activo = 1
   and LN_contrapartes.subcontratista = 0
   and LN_contraparte_familias.activo = 1 
   and monitoreos_resultados.fecha > getdate() - @diasBancos
   and monitoreos_resultados.seccion_mantenimiento <> 7
--          and ln_contrapartes.id_padron in (select id_padron from padrones where identificacion = @cuit)
   
            and monitoreos_resultados.tipo_novedad='Legales'


union 

/*
select  monitoreos_resultados.cuit, padrones.nombre_persona,tipo_novedad, null as nombre_causa,
            /* max(monitoreos_resultados.fecha) as ultima_novedad */
            convert(varchar(10),monitoreos_resultados.fecha,103) as fecha_novedad,
            /* monitoreos_resultados.*, */ 
            null as nro_cheque,null as importe_cheque,null as fecha_rechazo, 
null as fecha_pago_multa, 
null as fecha_pago_cheque, 

EntidadesBancarias.nombre_corto as banco, 
            cendeu_temp.situacion as situacion,cendeu_temp.prestamos as importe,cendeu_temp.fec_inf as periodo 
from monitoreos_resultados
inner join padrones ON monitoreos_resultados.cuit = padrones.identificacion 
inner join LN_contrapartes on padrones.id_padron = LN_contrapartes.id_padron
inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
inner join LN_familias on LN_familias.familia = LN_contraparte_familias.familia
            left join cendeu_temp on cendeu_temp.id_cendeu=monitoreos_resultados.id_tabla
            inner join EntidadesBancarias on cendeu_temp.codigo_ent = EntidadesBancarias.cod_banco
where LN_contrapartes.cliente = @cliente
and LN_contrapartes.activo = 1
and LN_familias.visible = 1 
and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
and LN_contrapartes.activo = 1
and LN_contrapartes.subcontratista = 0
and LN_contraparte_familias.activo = 1 
and monitoreos_resultados.fecha > getdate() - @diasBancos
and monitoreos_resultados.seccion_mantenimiento <> 7
            and cendeu_temp.situacion > 1
            and ln_contrapartes.id_padron in (select id_padron from padrones where identificacion = @cuit)

            and monitoreos_resultados.tipo_novedad='Bancos'
*/

   select  cendeu_temp.iden as cuit, padrones.nombre_persona,'Bancos' as tipo_novedad,  null as nombre_causa,
            convert(varchar(10),cendeu_temp.created,103) as fecha_novedad,
            /* monitoreos_resultados.*, */ 
            null as nro_cheque,null as importe_cheque,null as fecha_rechazo, 
      null as fecha_pago_multa, 
   null as fecha_pago_cheque, 

   EntidadesBancarias.nombre_corto as banco, 
            cendeu_temp.situacion as situacion, ((cendeu_temp.prestamos+cendeu_temp.particip+cendeu_temp.garotor+cendeu_temp.o_cptos+cendeu_temp.deuda_total)) as importe,cendeu_temp.fec_inf as periodo ,null as acumulado_cheques
   from padrones inner join cendeu_temp ON cendeu_temp.iden = padrones.identificacion 
   inner join LN_contrapartes on padrones.id_padron = LN_contrapartes.id_padron
   inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
   inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
   inner join LN_familias on LN_familias.familia = LN_contraparte_familias.familia
            inner join EntidadesBancarias on cendeu_temp.codigo_ent = EntidadesBancarias.id_banco
   where LN_contrapartes.cliente = @cliente
   and LN_contrapartes.activo = 1
   and LN_familias.visible = 1 
   and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
   and LN_contrapartes.activo = 1
   and LN_contrapartes.subcontratista = 0
   and LN_contraparte_familias.activo = 1 
   and cendeu_temp.created > getdate() - @diasBancos
            and cendeu_temp.situacion > 1
--            and ln_contrapartes.id_padron in (select id_padron from padrones where identificacion = @cuit)
   


union 

/*
select  monitoreos_resultados.cuit, padrones.nombre_persona,tipo_novedad,  null as nombre_causa,
            /* max(monitoreos_resultados.fecha) as ultima_novedad */
            convert(varchar(10),monitoreos_resultados.fecha,103) as fecha_novedad,
            /* monitoreos_resultados.*, */ 
            null as nro_cheque,null as importe_cheque,null as fecha_rechazo, 
null as fecha_pago_multa, 
null as fecha_pago_cheque, 

EntidadesBancarias.nombre_corto as banco, 
            cendeu_temp.situacion as situacion,cendeu_temp.prestamos as importe,cendeu_temp.fec_inf as periodo 
from monitoreos_resultados
inner join padrones ON monitoreos_resultados.cuit = padrones.identificacion 
inner join LN_contrapartes on padrones.id_padron = LN_contrapartes.id_padron
inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
inner join LN_familias on LN_familias.familia = LN_contraparte_familias.familia
            left join cendeu as cendeu_temp on cendeu_temp.id_cendeu=monitoreos_resultados.id_tabla
            inner join EntidadesBancarias on cendeu_temp.codigo_ent = EntidadesBancarias.cod_banco
where LN_contrapartes.cliente = @cliente
and LN_contrapartes.activo = 1
and LN_familias.visible = 1 
and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
and LN_contrapartes.activo = 1
and LN_contrapartes.subcontratista = 0
and LN_contraparte_familias.activo = 1 
and monitoreos_resultados.fecha > getdate() - @diasBancos
and monitoreos_resultados.seccion_mantenimiento <> 7
            and cendeu_temp.situacion > 1
            and ln_contrapartes.id_padron in (select id_padron from padrones where identificacion = @cuit)

            and monitoreos_resultados.tipo_novedad='Bancos'
*/


   select  cendeu_temp.iden as cuit, padrones.nombre_persona,'Bancos' as tipo_novedad,  null as nombre_causa,
            convert(varchar(10),cendeu_temp.created,103) as fecha_novedad,
            /* monitoreos_resultados.*, */ 
            null as nro_cheque,null as importe_cheque,null as fecha_rechazo, 
      null as fecha_pago_multa, 
   null as fecha_pago_cheque, 

   EntidadesBancarias.nombre_corto as banco, 
            cendeu_temp.situacion as situacion,((cendeu_temp.prestamos+cendeu_temp.particip+cendeu_temp.garotor+cendeu_temp.o_cptos+cendeu_temp.deuda_total)) as importe,cendeu_temp.fec_inf as periodo ,null as acumulado_cheques
   from padrones inner join cendeu cendeu_temp ON cendeu_temp.iden = padrones.identificacion 
   inner join LN_contrapartes on padrones.id_padron = LN_contrapartes.id_padron
   inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
   inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
   inner join LN_familias on LN_familias.familia = LN_contraparte_familias.familia
            inner join EntidadesBancarias on cendeu_temp.codigo_ent = EntidadesBancarias.id_banco
   where LN_contrapartes.cliente = @cliente
   and LN_contrapartes.activo = 1
   and LN_familias.visible = 1 
   and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
   and LN_contrapartes.activo = 1
   and LN_contrapartes.subcontratista = 0
   and LN_contraparte_familias.activo = 1 
   and cendeu_temp.created > getdate() - @diasBancos
            and cendeu_temp.situacion > 1
--            and ln_contrapartes.id_padron in (select id_padron from padrones where identificacion = @cuit)
   


union 

select monitoreos_resultados.cuit, padrones.nombre_persona,tipo_novedad,  iif(chequesbcra.id_causa=1,'FORMALES','SIN FONDO') as nombre_causa,
            /* max(monitoreos_resultados.fecha) as ultima_novedad */
            convert(varchar(10),monitoreos_resultados.fecha,103) as fecha_novedad,
            /* monitoreos_resultados.*, */ 
            chequesbcra.nro_cheque,chequesbcra.importe as importe_cheque,
            convert(varchar(10),ChequesBCRA.fecha_rechazo,103) as fecha_rechazo, 
   convert(varchar(10),ChequesBCRA.fecha_pago,103) as fecha_pago_multa, 
   convert(varchar(10),ChequesBCRA.fecha_levantamiento,103) as fecha_pago_cheque, 
   null as banco, null as situacion,null as importe,null as periodo,
   acumulado_cheques = ((select count(nro_cheque) from ChequesBCRA
       where (cuit_firmante = monitoreos_resultados.cuit) AND id_causa=2
         AND id_cheque = (select max(id_cheque) from chequesBCRA b 
       where b.cuit_firmante = chequesBCRA.cuit_firmante 
         and b.nro_cheque = chequesBCRA.nro_cheque 
         and b.visible = 1) 
      AND visible = 1
        AND 
       (

        (
         (
          (
        (fecha_levantamiento IS NULL) 
        AND
        (fecha_rechazo >= @p_5anios)
          ) 
          OR 
          (
        (
         (fecha_levantamiento >= @p_5anios)
          AND ((datediff(day,fecha_rechazo,fecha_levantamiento)) <= 15 ) 
        ) 
        AND 
        (
         ((fecha_pago IS NOT NULL) AND (fecha_levantamiento >= (GETDATE() - 2))) 
         OR
         (((fecha_pago IS NULL) AND (fecha_levantamiento >= (GETDATE() - 2))) AND (fecha_rechazo >= @p_5anios)) 
        ) 
          ) 
         ) 
         OR 
         (
          ((fecha_levantamiento >= @p_5anios) 
          AND (((datediff(day,fecha_rechazo,fecha_levantamiento)) >= 16 ) AND ((datediff(day,fecha_rechazo,fecha_levantamiento)) <= 90)) 
          ) 
          AND 
          (((fecha_pago IS NOT NULL) AND (fecha_levantamiento >= @p_1anio))
        OR (((fecha_pago IS NULL) AND (fecha_levantamiento >= @p_1anio)) AND (fecha_rechazo >= @p_5anios))
          ) 
         ) 
        )

        OR 
 
        (
         (((fecha_levantamiento >= @p_5anios) AND
        ((datediff(day,fecha_rechazo,fecha_levantamiento)) > 90)) AND (fecha_levantamiento >= @p_2anios) 
         ) 
         AND (fecha_rechazo >= @p_5anios) 
        )

       )
       )) 
   from monitoreos_resultados
   inner join padrones ON monitoreos_resultados.cuit = padrones.identificacion 
   inner join LN_contrapartes on padrones.id_padron = LN_contrapartes.id_padron
   inner join LN_gestiones on LN_contrapartes.contraparte = LN_gestiones.contraparte
   inner join LN_contraparte_familias on LN_contrapartes.contraparte = LN_contraparte_familias.contraparte
   inner join LN_familias on LN_familias.familia = LN_contraparte_familias.familia
            inner join chequesbcra on ChequesBCRA.id_cheque=monitoreos_resultados.id_tabla
   where LN_contrapartes.cliente = @cliente
   and LN_contrapartes.activo = 1
   and LN_familias.visible = 1 
   and LN_gestiones.desde <= getdate() and LN_gestiones.hasta >= getdate()
   and LN_contrapartes.activo = 1
   and LN_contrapartes.subcontratista = 0
   and LN_contraparte_familias.activo = 1 
   and monitoreos_resultados.fecha > getdate() - @dias
   and monitoreos_resultados.seccion_mantenimiento <> 7
 --          and ln_contrapartes.id_padron in (select id_padron from padrones where identificacion = @cuit)
   and monitoreos_resultados.tipo_novedad='Cheques'



END 
GO