dbo.grafo_sociedades_claudio

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
-- exec [grafo_sociedades] '30714127043', 'J'
-- exec [grafo_sociedades] '30711733473', 'J'
-- exec [grafo_sociedades] '27131209253', 'F'
-- exec [grafo_sociedades] '20076297534', 'F'

-- EXEC dbo.obtener_vinculaciones '30641368977'
CREATE OR ALTER PROCEDURE dbo.grafo_sociedades_claudio
@p_Cuit varchar(20),
@tipo char(1)
AS

BEGIN
       SET NOCOUNT ON;
DECLARE @p_5anios smalldatetime = getdate() - 1825
DECLARE @p_2anios smalldatetime = getdate() - 730
DECLARE @p_1anio  smalldatetime = getdate() - 365
declare @periodo date = (select max(fuentes_procesadas.fecha_informacion) from fuentes_procesadas where fuente = 'empleados')
declare @periodo_comparar int = substring(convert(char,@periodo),3,2) +(substring(convert(char,@periodo),6,2))

declare @ultimo_mes_empleados date
declare @mes_empleados date
declare @periodo_empleados_desde int 
declare @periodo_empleados_hasta int 
--  set @ultimo_mes_empleados = '2018-06-01'
set @ultimo_mes_empleados = (select max(fuentes_procesadas.fecha_informacion) from fuentes_procesadas where fuente = 'empleados')
set @periodo_empleados_hasta = convert(int,substring(convert(char,@ultimo_mes_empleados),3,2)+substring(convert(char,@ultimo_mes_empleados),6,2))
set @mes_empleados = DATEADD(M,-6,@ultimo_mes_empleados) --'2018-06-01'-- DATEADD(M,-6,@ultimo_mes_empleados) -- antes decia -2
set @periodo_empleados_desde = convert(int,substring(convert(char,@mes_empleados),3,2)+substring(convert(char,@mes_empleados),6,2))
 
-- 01/06/2018 se cambiaron todos los union all por union, para que no traiga duplicados
IF @tipo = 'j' 
begin
 
-- declare @p_Cuit varchar(20)= '30711733473'

 select distinct
 padrones.id_padron as idNodoGrafo,
 padrones.identificacion as cuit,
 padrones.nombre_persona as nombre_persona,
 padInicial.nombre_persona as CentroGrafo,
 padInicial.id_padron as IDCentroGrafo,
 'AUTORIDADES_IGJ' AS seccion, null as score, null as score_color
 from socios 
 inner join padrones
 on padrones.identificacion = socios.c01_cuit_soc
 inner join padrones padInicial on padInicial.identificacion = @p_Cuit
 where socios.c01_cuit = @p_Cuit and socios.visible = 1 and padrones.habilitado = 1
 /* and c04_ultimo = 'S' */
 and padInicial.habilitado = 1
 and socios.car_codigo='IGJ'
 union
 select distinct
 padrones.id_padron as idNodoGrafo,
 padrones.identificacion as cuit,
 padrones.nombre_persona as nombre_persona,
 padInicial.nombre_persona as CentroGrafo,
 padInicial.id_padron as IDCentroGrafo,
 'VINCULACIONES_OTRAS' AS seccion, null as score, null as score_color
 from socios 
 inner join padrones
 on padrones.identificacion = socios.c01_cuit_soc
 inner join padrones padInicial on padInicial.identificacion = @p_Cuit
 where socios.c01_cuit = @p_Cuit and socios.visible = 1 and padrones.habilitado = 1
 /* and c04_ultimo = 'S' */
 and padInicial.habilitado = 1
 and socios.car_codigo='RLL'
 
 and socios.c01_cuit_soc not in (select distinct
 padrones.identificacion as cuit 
 from socios 
 inner join padrones
 on padrones.identificacion = socios.c01_cuit_soc
 inner join padrones padInicial on padInicial.identificacion = @p_Cuit
 where socios.c01_cuit = @p_Cuit and socios.visible = 1 and padrones.habilitado = 1
 /* and c04_ultimo = 'S' */
 and padInicial.habilitado = 1
 and socios.car_codigo='IGJ'
 )
 
 union
 select distinct
 padrones.id_padron as idNodoGrafo,
 padrones.identificacion as cuit,
 padrones.nombre_persona as nombre_persona,
 padInicial.nombre_persona as CentroGrafo,
 padInicial.id_padron as IDCentroGrafo,
 'SOCIOS' AS seccion, null as score, null as score_color
 from socios 
 inner join padrones
 on padrones.identificacion = socios.c01_cuit_soc
 inner join padrones padInicial on padInicial.identificacion = @p_Cuit
 where socios.c01_cuit = @p_Cuit and socios.visible = 1 and c04_ultimo = 'S' and padrones.habilitado = 1
 and padInicial.habilitado = 1
 and socios.car_codigo!='RLL'
    and socios.car_codigo!='IGJ'

 UNION --ALL
  select distinct
 padrones.id_padron as idNodoGrafo,
 padrones.identificacion as cuit,
 'ex-'+padrones.nombre_persona as nombre_persona,
 padInicial.nombre_persona as CentroGrafo,
 padInicial.id_padron as IDCentroGrafo,
 'SOCIOS' AS seccion, null as score, null as score_color
 from socios 
 inner join padrones
 on padrones.identificacion = socios.c01_cuit_soc
 inner join padrones padInicial on padInicial.identificacion = @p_Cuit
 where socios.c01_cuit = @p_Cuit and socios.visible = 1 and c04_ultimo = 'N' and padrones.habilitado = 1
 and not exists(select * from socios b where b.c01_cuit = socios.c01_cuit and b.c01_cuit_soc = socios.c01_cuit_soc and b.c04_ultimo = 'S' and b.visible = 1)
 and padInicial.habilitado = 1
 and socios.car_codigo is not null
 and socios.car_codigo!='RLL'
    and socios.car_codigo!='IGJ'


 UNION --ALL
 select distinct
  padrones.id_padron as idNodoGrafo,
  cuit_sociedad as cuit,
  padrones.nombre_persona as nombre_pesona,
  padInicial.nombre_persona as CentroGrafo,
  padInicial.id_padron as IDCentroGrafo,
  'ACCIONISTA_DE' AS seccion, null as score, null as score_color
  from accionistas 
  inner join padrones
  on padrones.identificacion = accionistas.cuit_sociedad
  inner join padrones padInicial on padInicial.identificacion =  ACCIONISTAS.cuit_acc
  where ACCIONISTAS.cuit_acc = @p_Cuit
  and padrones.habilitado = 1 and padInicial.habilitado = 1  and visible = 1

 union --all


 select distinct 
 padrones.id_padron as idNodoGrafo,cuit_acc as cuit,padrones.nombre_persona,padInicial.nombre_persona as CentroGrafo,padInicial.id_padron as IDCentroGrafo, 'ACCIONISTAS' AS seccion, null as score, null as score_color from  accionistas
 inner join padrones on
 padrones.identificacion = accionistas.cuit_acc
 inner join padrones padInicial on padInicial.identificacion = @p_Cuit

 where cuit_sociedad = @p_Cuit and accionistas.visible = 1 
 and padrones.habilitado = 1 and padInicial.habilitado = 1
 union --all

 select  distinct
 padrones.id_padron as idNodoGrafo,vinculaciones.cuit_vinculada as cuit,padrones.nombre_persona,padInicial.nombre_persona as CentroGrafo,padInicial.id_padron as IDCentroGrafo, iif(nombre = 'CONTROLADA','CONTROLADAS','CONTROLANTES') AS seccion, null as score, null as score_color from  vinculaciones
  right join tipo_vinculaciones on
  id_tipo_vinculacion = cod_vinculacion 
 inner join padrones padInicial on padInicial.identificacion = @p_Cuit
  INNER JOIN PADRONES ON PADRONES.identificacion = cuit_vinculada where cuit_sociedad = @p_cuit  and visible = 1  and id_tipo_vinculacion  in (3,4) and padinicial.habilitado = 1 and PADRONES.habilitado = 1
 

 union --all

 select distinct 
 padrones.id_padron as idNodoGrafo,vinculaciones.cuit_sociedad as cuit,padrones.nombre_persona,padInicial.nombre_persona as CentroGrafo,padInicial.id_padron as IDCentroGrafo, iif(nombre = 'CONTROLADA','CONTROLANTES','CONTROLADAS')
  AS seccion, null as score, null as score_color from  vinculaciones
  right join tipo_vinculaciones on
  id_tipo_vinculacion = cod_vinculacion 
 inner join padrones padInicial on padInicial.identificacion = @p_Cuit
  INNER JOIN PADRONES ON PADRONES.identificacion = cuit_sociedad where cuit_vinculada = @p_cuit  and visible = 1 and id_tipo_vinculacion  in (3,4)  and padinicial.habilitado = 1 and PADRONES.habilitado = 1
 union --all

 select distinct 
 padrones.id_padron as idNodoGrafo,vinculaciones.cuit_sociedad as cuit,padrones.nombre_persona,padInicial.nombre_persona as CentroGrafo,padInicial.id_padron as IDCentroGrafo,
 'VINCULACIONES_OTRAS'
  AS seccion, null as score, null as score_color from  vinculaciones
  right join tipo_vinculaciones on
  id_tipo_vinculacion = cod_vinculacion 
 inner join padrones padInicial on padInicial.identificacion = @p_Cuit
  INNER JOIN PADRONES ON PADRONES.identificacion = cuit_sociedad where cuit_vinculada = @p_cuit  and visible = 1 and id_tipo_vinculacion  not in (3,4) and tipo_vinculaciones.id_tipo_vinculacion < 18
  and padinicial.habilitado = 1 and PADRONES.habilitado = 1
 union --all
 
 --VINCULACIONES OTRAS PJ B
 select distinct 
 padrones.id_padron as idNodoGrafo,
 cuitb as cuit,padrones.nombre_persona,padInicial.nombre_persona as CentroGrafo,padInicial.id_padron as IDCentroGrafo, 'VINCULACIONES_OTRAS'
  AS seccion, null as score, null as score_color from  relaciones
 inner join padrones
  on padrones.identificacion = cuitb
  inner join padrones padInicial on padInicial.identificacion = cuita
 where cuita = @p_Cuit and origen_del_dato = 'AFIP'
   and relaciones.cuitb not in(select socios.c01_cuit_soc from socios 
 where socios.c01_cuit = @p_Cuit and socios.visible = 1 and c04_ultimo = 'S' )
 
 and padrones.habilitado = 1 and padInicial.habilitado = 1
 
 union --all

 -- 01/06/2018 --> se agrego que busque en el cuitb
 select distinct 
 padrones.id_padron as idNodoGrafo,
 cuita as cuit,padrones.nombre_persona,
 padInicial.nombre_persona as CentroGrafo,padInicial.id_padron as IDCentroGrafo, 
 'VINCULACIONES_OTRAS' AS seccion, null as score, null as score_color 
 from  relaciones
 inner join padrones            on padrones.identificacion = cuita
 inner join padrones padInicial on padInicial.identificacion = cuitb
 where cuitb = @p_Cuit and origen_del_dato = 'AFIP'
   and relaciones.cuita not in(select socios.c01_cuit_soc from socios 
 where socios.c01_cuit = @p_Cuit and socios.visible = 1 and c04_ultimo = 'S' )
 and padrones.habilitado = 1 and padInicial.habilitado = 1
    --
 
 -- 01/06/2018 --> trae las relaciones de Cheques-BCRA
 union --all
 
 select distinct 
         pf.id_padron as idNodoGrafo,
   Cuita as cuit,
   pf.nombre_persona,
   pj.nombre_persona as CentroGrafo,
   pj.id_padron as IDCentroGrafo,
   'FIRMANTES' as seccion, null as score, null as score_color
  FROM Relaciones
  INNER JOIN padrones as pf ON pf.identificacion = cuita and pf.habilitado = 1 
  INNER JOIN padrones as pj ON pj.identificacion = cuitb and pj.habilitado = 1     
  
  WHERE Cuitb = @p_Cuit
    AND origen_del_dato = 'Cheques-BCRA'
   

 
 union  

 select distinct 
 pf.id_padron as idNodoGrafo,
 ChequesBCRA.cuit_firmante as cuit,
 pf.nombre_persona,pj.nombre_persona as CentroGrafo,pj.id_padron as IDCentroGrafo,
 'FIRMANTES' as seccion, null as score, null as score_color

  FROM
    ChequesBCRA
    INNER JOIN padrones as pf ON (pf.identificacion = cuit_firmante)       --info firmante
    INNER JOIN padrones as pj ON (pj.identificacion = Cuit_relacionado)    --info empresa
    WHERE 
    (Cuit_relacionado = @p_Cuit)
   
    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 >= @p_2anios)) 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_2anios)) 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) 
      )
    
     )*/
   and pf.habilitado = 1 and pj.habilitado = 1
   group by pf.nombre_persona,Cuit_relacionado,pf.id_padron,cuit_firmante,pj.nombre_persona,pj.id_padron

 end
else /* ESTO ES PERSONA FÍSICA*/
 begin
 select distinct
  padrones.id_padron as idNodoGrafo,
  padrones.identificacion as cuit,
  padrones.nombre_persona as nombre_persona,
  padInicial.nombre_persona as CentroGrafo,
  padInicial.id_padron as IDCentroGrafo,
  'AUTORIDADES_IGJ' AS seccion, null as score, null as score_color
  from socios 
  inner join padrones
  on padrones.identificacion = socios.c01_cuit
  inner join padrones padInicial on padInicial.identificacion = socios.c01_cuit_soc
 where socios.c01_cuit_soc = @p_Cuit and socios.visible = 1
 and padrones.habilitado = 1 and padInicial.habilitado = 1 
 and socios.car_codigo='IGJ'
 union

  select distinct
  padrones.id_padron as idNodoGrafo,
  padrones.identificacion as cuit,
  padrones.nombre_persona as nombre_persona,
  padInicial.nombre_persona as CentroGrafo,
  padInicial.id_padron as IDCentroGrafo,
  'VINCULACIONES_OTRAS' AS seccion, null as score, null as score_color
  from socios 
  inner join padrones
  on padrones.identificacion = socios.c01_cuit
  inner join padrones padInicial on padInicial.identificacion = socios.c01_cuit_soc
 where socios.c01_cuit_soc = @p_Cuit and socios.visible = 1
 and padrones.habilitado = 1 and padInicial.habilitado = 1 
 and socios.car_codigo='RLL'
 and socios.c01_cuit not in (

 select distinct
  padrones.identificacion as cuit 
  from socios 
  inner join padrones
  on padrones.identificacion = socios.c01_cuit
  inner join padrones padInicial on padInicial.identificacion = socios.c01_cuit_soc
 where socios.c01_cuit_soc = @p_Cuit and socios.visible = 1
 and padrones.habilitado = 1 and padInicial.habilitado = 1 
 and socios.car_codigo='IGJ'
 )
 union
 select distinct
  padrones.id_padron as idNodoGrafo,
  padrones.identificacion as cuit,
  padrones.nombre_persona as nombre_persona,
  padInicial.nombre_persona as CentroGrafo,
  padInicial.id_padron as IDCentroGrafo,
  'SOCIEDADES' AS seccion, null as score, null as score_color
  from socios 
  inner join padrones
  on padrones.identificacion = socios.c01_cuit
  inner join padrones padInicial on padInicial.identificacion = socios.c01_cuit_soc
 where socios.c01_cuit_soc = @p_Cuit and isnull(socios.visible,1) = 1 and c04_ultimo = 'S' 
 and padrones.habilitado = 1 and padInicial.habilitado = 1 
 and socios.car_codigo!='RLL'
    and socios.car_codigo!='IGJ'

 UNION --ALL
 select distinct
  padrones.id_padron as idNodoGrafo,
  padrones.identificacion as cuit,
  'ex-'+padrones.nombre_persona as nombre_persona,
  padInicial.nombre_persona as CentroGrafo,
  padInicial.id_padron as IDCentroGrafo,
  'SOCIEDADES' AS seccion, null as score, null as score_color
  from socios 
  inner join padrones
  on padrones.identificacion = socios.c01_cuit
  inner join padrones padInicial on padInicial.identificacion = socios.c01_cuit_soc

 where socios.c01_cuit_soc = @p_Cuit and isnull(socios.visible,1) = 1 and upper(c04_ultimo) = 'N' 
 and padrones.habilitado = 1 and padInicial.habilitado = 1 and socios.car_codigo!='RLL' and socios.car_codigo!='IGJ'

 UNION --ALL
 select distinct
  padrones.id_padron as idNodoGrafo,
  cuita as cuit,
  padrones.nombre_persona as nombre_pesona,
  padInicial.nombre_persona as CentroGrafo,
  padInicial.id_padron as IDCentroGrafo,
  'VINCULACIONES_OTRAS' AS seccion, null as score, null as score_color
  from relaciones 
  inner join padrones
  on padrones.identificacion = cuita
  inner join padrones padInicial on padInicial.identificacion = cuitb
  and relaciones.cuita not in(select socios.c01_cuit from socios 
 where socios.c01_cuit_soc = @p_Cuit and socios.visible = 1 and c04_ultimo = 'S' 
 and padrones.habilitado = 1 and padInicial.habilitado = 1 )
 where cuitb = @p_Cuit and origen_del_dato = 'AFIP'
 and padrones.habilitado = 1 and padInicial.habilitado = 1  
--agregar not in socios.
 UNION --ALL

 
 select distinct
  padrones.id_padron as idNodoGrafo,
  cuitb as cuit,
  padrones.nombre_persona as nombre_pesona,
  padInicial.nombre_persona as CentroGrafo,
  padInicial.id_padron as IDCentroGrafo,
  'VINCULACIONES_OTRAS' AS seccion, null as score, null as score_color
  from relaciones 
  inner join padrones
  on padrones.identificacion = cuitb
  inner join padrones padInicial on padInicial.identificacion = cuita

 where cuita = @p_Cuit and origen_del_dato = 'AFIP'
  and relaciones.cuitb not in(select socios.c01_cuit from socios 
 where socios.c01_cuit_soc = @p_Cuit and socios.visible = 1 and c04_ultimo = 'S' 
 and padrones.habilitado = 1 and padInicial.habilitado = 1 )
 and padrones.habilitado = 1 and padInicial.habilitado = 1
 UNION --ALL
 select distinct
  padrones.id_padron as idNodoGrafo,
  cuit,
  padrones.nombre_persona as nombre_persona,
  padInicial.nombre_persona as CentroGrafo,
  padInicial.id_padron as IDCentroGrafo,
  'EMPLEADO_DE' AS seccion, null as score, null as score_color
  from empleados 
  inner join padrones
  on padrones.identificacion = cuit
  inner join padrones padInicial on padInicial.identificacion = @p_Cuit
  where cuil = @p_Cuit
    and visible = '1'
  --and empleados.periodo between  '1806' and @periodo_comparar
    and empleados.periodo = (select max(periodo) from empleados b
                    where b.cuil = @p_cuit 
          and b.visible = 1 
                      and b.periodo between @periodo_empleados_desde and @periodo_empleados_hasta)

    and padrones.habilitado = 1 
    and padInicial.habilitado = 1


UNION --ALL
 select distinct
  padrones.id_padron as idNodoGrafo,
  cuit_sociedad as cuit,
  padrones.nombre_persona as nombre_pesona,
  padInicial.nombre_persona as CentroGrafo,
  padInicial.id_padron as IDCentroGrafo,
  'ACCIONISTA_DE' AS seccion, null as score, null as score_color
  from accionistas 
  inner join padrones
  on padrones.identificacion = accionistas.cuit_sociedad
  inner join padrones padInicial on padInicial.identificacion =  ACCIONISTAS.cuit_acc
  where ACCIONISTAS.cuit_acc = @p_Cuit
  and padrones.habilitado = 1 and padInicial.habilitado = 1  and visible = 1

 -- 01/06/2018 --> trae las relaciones de Cheques-BCRA
 union
 select distinct 
         pj.id_padron as idNodoGrafo,
   Cuitb as cuit,
   pj.nombre_persona,
   pf.nombre_persona as CentroGrafo,
   pf.id_padron as IDCentroGrafo,
   'FIRMANTES' as seccion, null as score, null as score_color
  FROM Relaciones
  INNER JOIN padrones as pf ON pf.identificacion = cuita and pf.habilitado = 1 
  INNER JOIN padrones as pj ON pj.identificacion = cuitb and pj.habilitado = 1     
  
  WHERE Cuita = @p_Cuit
    AND origen_del_dato = 'Cheques-BCRA'

 UNION --ALL
  select 
 pf.id_padron as idNodoGrafo,ChequesBCRA.Cuit_relacionado as cuit,pf.nombre_persona,pj.nombre_persona as CentroGrafo,pj.id_padron as IDCentroGrafo,'FIRMANTES' as seccion, null as score, null as score_color

  FROM
    ChequesBCRA
    INNER JOIN padrones as pf ON (pf.identificacion = Cuit_relacionado)    --info firmante
    INNER JOIN padrones as pj ON (pj.identificacion = cuit_firmante)    --info empresa
    WHERE 
    (cuit_firmante = @p_Cuit)
   
    AND visible = 1 
    -- 01/06/2018 --> se deja que muestre de todos los cheques, aunque sean viejos.
    /*
       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 >= @p_2anios)) 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_2anios)) 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) 
      )
    
     )
     */
   and pf.habilitado = 1 and pj.habilitado = 1
   group by pf.nombre_persona,Cuit_relacionado,pf.id_padron,cuit_firmante,pj.nombre_persona,pj.id_padron









 end






END

GO