IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'dbo.[CP_Clientes_Estadisticas_Hist]') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
   DROP PROCEDURE dbo.CP_Clientes_Estadisticas_Hist
END
GO

CREATE PROCEDURE CP_Clientes_Estadisticas_Hist @Fecha DATE
AS
DECLARE  @socios_activos	INT
		, @socios_altas		INT
		, @socios_bajas		INT
		, @Color			CHAR(20)
		, @Tendencia		CHAR(2)
		, @Valor_1			DECIMAL(16,4)
		, @Valor_2			DECIMAL(16,4)
		, @Valor_3			DECIMAL(16,4)
		, @Valor_4			DECIMAL(16,4)
		, @Valor_5			DECIMAL(16,4)
		, @Valor_6			DECIMAL(16,4)
		, @Fecha_Mas_Uno	DATE

SET NOCOUNT ON
/*******************agrego los indices*************/
IF NOT EXISTS (SELECT 1 FROM Indices WHERE Id_Indice = 1000)
	INSERT INTO Indices (ID_INDICE,DESCRIPCION,TIPO)
	VALUES (1000,'Clientes','G')


IF NOT EXISTS (SELECT 1 FROM Indices WHERE Id_Indice = 1001)
	INSERT INTO Indices (ID_INDICE,DESCRIPCION,TIPO,TENDENCIA,ID_INDICE_PADRE)
	VALUES(1001,'Clientes Activos'			,'I','+',1000)

IF NOT EXISTS (SELECT 1 FROM Indices WHERE Id_Indice = 1004)
	INSERT INTO Indices (ID_INDICE,DESCRIPCION,TIPO,TENDENCIA,ID_INDICE_PADRE)
	VALUES(1004,'No Clientes Activos'					,'I','-',1000)
---------------------------------------------------------------------------------------

IF NOT EXISTS (SELECT 1 FROM Indices WHERE Id_Indice = 1050)
	INSERT INTO Indices (ID_INDICE,DESCRIPCION,TIPO)
	VALUES (1050,'Clientes A/B','G')

IF NOT EXISTS (SELECT 1 FROM Indices WHERE Id_Indice = 1002)
	INSERT INTO Indices (ID_INDICE,DESCRIPCION,TIPO,TENDENCIA,ID_INDICE_PADRE)
	VALUES(1002,'Altas'						,'I','+',1050)

IF NOT EXISTS (SELECT 1 FROM Indices WHERE Id_Indice = 1003)
	INSERT INTO Indices (ID_INDICE,DESCRIPCION,TIPO,TENDENCIA,ID_INDICE_PADRE)
	VALUES(1003,'Bajas'						,'I','-',1050)
-----------------------------------------------------------------------------------------------------

IF NOT EXISTS (SELECT 1 FROM Indices WHERE Id_Indice = 1100)
	INSERT INTO Indices (ID_INDICE,DESCRIPCION,TIPO,TENDENCIA,ID_INDICE_PADRE)
	VALUES(1100,'Socios por Categoria'		,'I','+',NULL)

IF NOT EXISTS (SELECT 1 FROM Indices WHERE Id_Indice = 1110)
	INSERT INTO Indices (ID_INDICE,DESCRIPCION,TIPO,TENDENCIA,ID_INDICE_PADRE)
	VALUES(1110,'No Socios por Categoria'	,'I','+',NULL)

IF NOT EXISTS (SELECT 1 FROM Indices WHERE Id_Indice = 1200)
	INSERT INTO Indices (ID_INDICE,DESCRIPCION,TIPO,TENDENCIA,ID_INDICE_PADRE)
	VALUES(1200,'Socios por Actividad'		,'I','+',NULL)

IF NOT EXISTS (SELECT 1 FROM Indices WHERE Id_Indice = 1300)
	INSERT INTO Indices (ID_INDICE,DESCRIPCION,TIPO,TENDENCIA,ID_INDICE_PADRE)
	VALUES(1300,'Morosidad Clientes'		,'I','+',NULL)

IF NOT EXISTS (SELECT 1 FROM Indices WHERE Id_Indice = 1400)
	INSERT INTO Indices (ID_INDICE,DESCRIPCION,TIPO,TENDENCIA,ID_INDICE_PADRE)
	VALUES(1400,'Socios por Grupos Familiares'		,'I','+',NULL)

/*********************************************************/


IF @Fecha IS NULL RETURN
SET @Fecha_Mas_Uno = DATEADD(mm,1,@Fecha)

--Busco el ultimo movimiendo del dia
SELECT Id_Cliente,MAX(Item) AS Item INTO #CHI FROM Clientes_Hist
	WHERE Fecha < (DATEADD(mm,1,@Fecha))
	GROUP BY Id_Cliente

SELECT CH.* INTO #CH FROM Clientes_Hist CH, #CHI CHI
	WHERE CH.Id_Cliente = CHI.Id_Cliente AND CH.Item = CHI.Item

-- Socios 

SELECT @socios_activos = Count(*)  FROM clientes C, Clientes_Tipos T , #CH CH
	WHERE C.Id_Tipo_Cli = T.Id_Tipo_Cli
	AND C.Id_Cliente =  CH.Id_Cliente
	AND CH.Fecha_baja is null
	AND DATEDIFF(MM,GETDATE(),C.Ult_Cuota_Paga) > -6
	AND CH.Fecha_Alta < @Fecha_Mas_Uno
	AND T.Flag_Tipo <> 'N'


SELECT  @Tendencia = Tendencia 
       , @Valor_1  =  Valor_1
	   , @Valor_2 =  Valor_2
	   , @Valor_3 =  Valor_3
	   , @Valor_4 =  Valor_4
	   , @Valor_5 =  Valor_5
	   , @Valor_6 =  Valor_6 FROM Indices WHERE Id_Indice = 1001

IF @Tendencia = '+'
   BEGIN
      set @COLOR = 'R'
      IF @socios_activos >  @Valor_1
	     set @COLOR = 'O'
      IF @socios_activos >=  @Valor_2
	     set @COLOR = 'Y'
      IF @socios_activos >=  @Valor_3
	     set @COLOR = 'G'
    END

IF @Tendencia = '-'
   BEGIN
      set @COLOR = 'G'
      IF @socios_activos >  @Valor_1
	     set @COLOR = 'Y' 
      IF @socios_activos >=  @Valor_2
	     set @COLOR = 'O'
      IF @socios_activos >=  @Valor_3
	     set @COLOR = 'R'
    END

DELETE  Indices_Mov WHERE Id_Indice = 1001 AND FECHA = @FECHA
INSERT INTO Indices_Mov(Id_Indice,Fecha,Valor,Color) 
		   VALUES (1001,@Fecha,@socios_activos,@Color)

--- no socios

SELECT @socios_activos = Count(*)  FROM clientes C, Clientes_Tipos T , #CH CH
	WHERE C.Id_Tipo_Cli = T.Id_Tipo_Cli
	AND C.Id_Cliente =  CH.Id_Cliente
	AND CH.Fecha_baja is null
	AND CH.Fecha_Alta < @Fecha_Mas_Uno
	AND DATEDIFF(MM,GETDATE(),C.Ult_Cuota_Paga) > -6
	AND T.Flag_Tipo = 'N'


SELECT  @Tendencia = Tendencia 
       , @Valor_1  =  Valor_1
	   , @Valor_2 =  Valor_2
	   , @Valor_3 =  Valor_3
	   , @Valor_4 =  Valor_4
	   , @Valor_5 =  Valor_5
	   , @Valor_6 =  Valor_6 FROM Indices WHERE Id_Indice = 1004

If @Tendencia = '+'
   BEGIN
      set @COLOR = 'R'
      IF @socios_activos >  @Valor_1
	     set @COLOR = 'O'
      IF @socios_activos >=  @Valor_2
	     set @COLOR = 'Y'
      IF @socios_activos >=  @Valor_3
	     set @COLOR = 'G'
    END

If @Tendencia = '-'
   BEGIN
      set @COLOR = 'G'
      IF @socios_activos >  @Valor_1
	     set @COLOR = 'Y' 
      IF @socios_activos >=  @Valor_2
	     set @COLOR = 'O'
      IF @socios_activos >=  @Valor_3
	     set @COLOR = 'R'
    END

DELETE Indices_Mov WHERE Id_Indice = 1004 AND FECHA = @FECHA
INSERT INTO Indices_Mov(Id_Indice,Fecha,Valor,Color) 
		   VALUES (1004,@Fecha,@socios_activos,@Color)


--altas

SELECT @socios_altas = Count(*)  FROM clientes C, Clientes_Tipos T , #CH CH
	WHERE C.Id_Tipo_Cli = T.Id_Tipo_Cli
	AND C.Id_Cliente = CH.Id_Cliente
	AND CONVERT(DATE,CH.Fecha_Alta) = @Fecha 
	AND T.Flag_Tipo <> 'N'


SELECT  @Tendencia = Tendencia 
       , @Valor_1  =  Valor_1
	   , @Valor_2 =  Valor_2
	   , @Valor_3 =  Valor_3
	   , @Valor_4 =  Valor_4
	   , @Valor_5 =  Valor_5
	   , @Valor_6 =  Valor_6 FROM Indices WHERE Id_Indice = 1002

IF @Tendencia = '+'
   BEGIN
      set @COLOR = 'R'
      IF @socios_altas >  @Valor_1
	     set @COLOR = 'O'
      IF @socios_altas >=  @Valor_2
	     set @COLOR = 'Y'
      IF @socios_altas >=  @Valor_3
	     set @COLOR = 'G'
    END

IF @Tendencia = '-'
   BEGIN
      set @COLOR = 'G'
      IF @socios_altas >  @Valor_1
	     set @COLOR = 'Y' 
      IF @socios_altas >=  @Valor_2
	     set @COLOR = 'O'
      IF @socios_altas >=  @Valor_3
	     set @COLOR = 'R'
    END

DELETE Indices_Mov WHERE Id_Indice = 1002 AND FECHA = @FECHA
INSERT INTO Indices_Mov(Id_Indice,Fecha,Valor,Color) 
		   VALUES (1002,@Fecha,@socios_altas,@Color)

--- bajas

SELECT @socios_bajas = Count(*)  FROM clientes C, Clientes_Tipos T, #CH CH 
	WHERE C.Id_Tipo_Cli = T.Id_Tipo_Cli
	AND C.Id_Cliente = CH.Id_Cliente
	AND CONVERT(DATE,CH.Fecha_baja) = @Fecha
	AND T.Flag_Tipo <> 'N'


SELECT  @Tendencia = Tendencia 
       , @Valor_1  =  Valor_1
	   , @Valor_2 =  Valor_2
	   , @Valor_3 =  Valor_3
	   , @Valor_4 =  Valor_4
	   , @Valor_5 =  Valor_5
	   , @Valor_6 =  Valor_6 FROM Indices WHERE Id_Indice = 1003

IF @Tendencia = '+'
   BEGIN
      set @COLOR = 'R'
      IF @socios_bajas >  @Valor_1
	     set @COLOR = 'O'
      IF @socios_bajas >=  @Valor_2
	     set @COLOR = 'Y'
      IF @socios_bajas >=  @Valor_3
	     set @COLOR = 'G'
    END

IF @Tendencia = '-'
   BEGIN
      set @COLOR = 'G'
      IF @socios_bajas >  @Valor_1
	     set @COLOR = 'Y' 
      IF @socios_bajas >=  @Valor_2
	     set @COLOR = 'O'
      IF @socios_bajas >=  @Valor_3
	     set @COLOR = 'R'
    END

DELETE Indices_Mov WHERE Id_Indice = 1003 AND FECHA = @FECHA
INSERT INTO Indices_Mov(Id_Indice,Fecha,Valor,Color) 
		   VALUES (1003,@Fecha,@socios_bajas,@Color)

-------------------------
-- SOCIOS POR CATEGORIA

  SELECT ISNULL(CH.ID_TIPO_CLI,0) AS ID_TIPO_CLI,RTRIM(LTRIM(T.DESCRIPCION)) AS DESCRIPCION,COUNT(*) AS CANTIDAD 
      Into #Socios_Cat
      FROM Clientes C, Clientes_Tipos T, #CH CH 
	  WHERE C.Id_Cliente = CH.Id_Cliente
	  AND CH.Id_Tipo_Cli = T.Id_Tipo_Cli
	  AND DATEDIFF(MM,GETDATE(),C.Ult_Cuota_Paga) > -6
	  AND CH.Fecha_Baja IS NULL
	  And T.Flag_Tipo <> 'N'
	  GROUP BY CH.ID_TIPO_CLI,T.DESCRIPCION

DELETE Indices_Mov WHERE Id_Indice = 1100 AND FECHA = @FECHA
INSERT INTO Indices_Mov 
	  SELECT 1100 AS Id_Indice, @Fecha as Fecha, CANTIDAD as Valor,'G',RTRIM(LTRIM(Descripcion)) AS Descripcion
	         FROM #Socios_Cat

DROP TABLE #Socios_Cat

---- NO SOCIOS POR CATEGORIA

SELECT ISNULL(CH.ID_TIPO_CLI,0) AS ID_TIPO_CLI,RTRIM(LTRIM(T.DESCRIPCION)) AS DESCRIPCION,COUNT(*) AS CANTIDAD 
      INTO #NoSocios_Cat
      FROM Clientes C, Clientes_Tipos T, #CH CH 
	  WHERE C.Id_Cliente = CH.Id_Cliente 
	  AND CH.Id_Tipo_Cli = T.Id_Tipo_Cli
	  AND DATEDIFF(MM,GETDATE(),C.Ult_Cuota_Paga) > -6
	  AND CH.Fecha_Baja IS NULL
	  And T.Flag_Tipo = 'N'
	  GROUP BY CH.ID_TIPO_CLI,T.DESCRIPCION

DELETE Indices_Mov WHERE Id_Indice = 1110 AND FECHA = @FECHA
INSERT INTO Indices_Mov 
SELECT 1110 AS Id_Indice, @Fecha AS Fecha, CANTIDAD as Valor,'G',RTRIM(LTRIM(Descripcion)) 
	         FROM #NoSocios_Cat

DROP TABLE #NoSocios_Cat

 
---SOCIOS POR ACTIVIDADES

 DECLARE @Fam_CuotasSoc VARCHAR(30)
 DECLARE @Tipo_Cont_CS VARCHAR(30)
 DECLARE @sql  VARCHAR(8000)

 SET @Fam_CuotasSoc = '(' + dbo.CF_xParam('Fam_CuotasSoc') + ')'  
 SET @Tipo_Cont_CS = '(' + dbo.CF_xParam('Tipo_Cont_CS') + ')'  

 
 SET @sql = 'SELECT P.ID_FAMILIA,RTRIM(LTRIM(F.DESCRIPCION)) AS DESCRIPCION,COUNT(*) AS VALOR INTO Z_CONTRATOS_AD
			 FROM CONTRATOS C, CONTRATOS_PROD CP, PRODUCTOS P , PROD_FAMILIAS F
			 WHERE C.Fecha_Alta < ''' + CONVERT(VARCHAR(8),@Fecha_Mas_Uno,112) + '''
			 AND (C.Fecha_Baja >= ''' + CONVERT(VARCHAR(8),@Fecha,112) + '''
				OR C.Fecha_Baja IS NULL)
			 AND C.Fecha_Desde < ''' + CONVERT(VARCHAR(8),@Fecha_Mas_Uno,112) + '''
			 AND (C.Fecha_Hasta >= ''' + CONVERT(VARCHAR(8),@Fecha,112) + '''
				OR C.Fecha_Hasta IS NULL)
			 AND C.ID_CONTRATO = CP.ID_CONTRATO  
			 AND CP.ID_PRODUCTO = P.ID_PRODUCTO
			 AND P.ID_FAMILIA = F.ID_FAMILIA
			 AND P.ID_FAMILIA IS NOT NULL AND P.ID_FAMILIA <> 0'
IF @Tipo_Cont_CS <> '()'
	 SET @sql = @sql + '
			AND C.ID_TIPO_CON NOT IN' + @Tipo_Cont_CS 
	 
IF @Fam_CuotasSoc <> '()'	 
	 SET @sql = @sql + ' 
			AND P.Id_Familia NOT IN ' + @Fam_CuotasSoc 

SET @sql = @sql + ' 
			GROUP BY P.ID_FAMILIA,F.DESCRIPCION '

EXEC (@sql)

DELETE Indices_Mov WHERE Id_Indice = 1200 AND FECHA = @FECHA
INSERT INTO Indices_Mov 
SELECT 1200 as Id_Indice, @Fecha AS Fecha, VALOR,'G',RTRIM(LTRIM(Descripcion)) 
	FROM Z_CONTRATOS_AD

DROP TABLE Z_CONTRATOS_AD		 


---- SOCIOS POR ULTIMA CUOTA PAGA

DECLARE @Cantidad_Mes INT
SET @Cantidad_Mes = 0 

DELETE Indices_Mov WHERE Id_Indice = 1300 AND FECHA = @FECHA

SELECT DATEDIFF(MM,GETDATE(),Ult_Cuota_Paga) AS MESES, COUNT(*) AS CANTIDAD 
      INTO #UCP
      FROM Clientes C, Clientes_Tipos T 
	  WHERE T.Id_Tipo_Cli = T.Id_Tipo_Cli
	  AND C.Fecha_Baja IS NULL
	  AND C.ACTIVO = 1
	  And T.Flag_Tipo <> 'N'
	  And DATEDIFF(MM,GETDATE(),Ult_Cuota_Paga) > -12
	  GROUP BY DATEDIFF(MM,GETDATE(),Ult_Cuota_Paga) ORDER BY 1 DESC

--AL DIA

SET @Cantidad_Mes = ISNULL((SELECT SUM(CANTIDAD) FROM #UCP WHERE MESES >= 0),0)
INSERT INTO Indices_Mov 
SELECT 1300 AS Id_Indice, @Fecha AS Fecha, @Cantidad_Mes as Valor,'G','Al Día'

-- -1

SET @Cantidad_Mes = ISNULL((SELECT SUM(CANTIDAD) FROM #UCP WHERE MESES = -1),0)
INSERT INTO Indices_Mov 
SELECT 1300 AS Id_Indice, @Fecha AS Fecha, @Cantidad_Mes as Valor,'G','Debe 1 mes'

-- -2

SET @Cantidad_Mes = ISNULL((SELECT SUM(CANTIDAD) FROM #UCP WHERE MESES = -2),0)
INSERT INTO Indices_Mov 
SELECT 1300 AS Id_Indice, @Fecha AS Fecha, @Cantidad_Mes as Valor,'Y','Debe 2 meses'
-- -3

SET @Cantidad_Mes = ISNULL((SELECT SUM(CANTIDAD) FROM #UCP WHERE MESES = -3),0)
INSERT INTO Indices_Mov 
SELECT 1300 AS Id_Indice, @Fecha AS Fecha, @Cantidad_Mes as Valor,'Y','Debe 3 mes'
-- -4

SET @Cantidad_Mes = ISNULL((SELECT SUM(CANTIDAD) FROM #UCP WHERE MESES = -4),0)
INSERT INTO Indices_Mov 
SELECT 1300 AS Id_Indice, @Fecha AS Fecha, @Cantidad_Mes as Valor,'O','Debe 4 mes'
-- -5

SET @Cantidad_Mes = ISNULL((SELECT SUM(CANTIDAD) FROM #UCP WHERE MESES = -5),0)
INSERT INTO Indices_Mov 
SELECT 1300 AS Id_Indice, @Fecha AS Fecha, @Cantidad_Mes as Valor,'O','Debe 5 mes'
-- -6

SET @Cantidad_Mes = ISNULL((SELECT SUM(CANTIDAD) FROM #UCP WHERE MESES = -6),0)
INSERT INTO Indices_Mov 
SELECT 1300 AS Id_Indice, @Fecha AS Fecha, @Cantidad_Mes as Valor,'R','Debe 6 mes'
-- -7

SET @Cantidad_Mes = ISNULL((SELECT SUM(CANTIDAD) FROM #UCP WHERE ISNULL(MESES,-10) <= -7),0)
INSERT INTO Indices_Mov 
SELECT 1300 AS Id_Indice, @Fecha AS Fecha, @Cantidad_Mes as Valor,'R','de 7 a 12 meses'

DROP TABLE #UCP

-- SOCIOS POR GRUPO FAMILIAR



select id_cliente_ref,count(*) + 1 as Grupo into #socios_grupos
    from clientes 
where id_cliente_ref <> 0 and activo = 1 
    AND DATEDIFF(MM,GETDATE(),Ult_Cuota_Paga) > -6
group by id_cliente_ref 

select Grupo,count(*) as Cantidad into #Grupo_Familias 
    from  #socios_grupos group by Grupo order by Grupo

Select count(*) as Cantidad   into #Indi
    from clientes Where  (Id_Cliente_Ref = 0 or Id_Cliente_Ref is null) 
	and activo = 1
	AND DATEDIFF(MM,GETDATE(),Ult_Cuota_Paga) > -6

Insert into #Grupo_Familias  Select 0,Cantidad from #Indi

DELETE Indices_Mov WHERE Id_Indice = 1400 AND FECHA = @FECHA
INSERT INTO Indices_Mov 
	  SELECT 1400 AS Id_Indice, @Fecha as Fecha, CANTIDAD as Valor,'G','Individual' 
	         FROM #Grupo_Familias where grupo =  0
INSERT INTO Indices_Mov 
	  SELECT 1400 AS Id_Indice, @Fecha as Fecha, CANTIDAD as Valor,'G','Grupo de '+ Cast(Grupo as char(2))
	         FROM #Grupo_Familias where grupo <>  0


Drop Table #Socios_Grupos
Drop Table #Grupo_Familias
Drop Table #Indi
  
 /*

CP_Clientes_Estadisticas_Hist '20250226'


SELECT * FROM INDICES_MOV




*/