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

CREATE  PROCEDURE CPJ_Clientes_Mov   @pJsonRecibido VARCHAR(8000)	 
AS

DECLARE 	@pFechaDesde			DATETIME,
			@pFechaHasta			DATETIME,
			@pFormato				VARCHAR(10),
			@pId_Tipo_Cli			INT
			 
			
			
SET @pFechaDesde				= ISNULL(CONVERT(DATETIME, dbo.CF_JSON_VALUE(@pJsonRecibido,'FechaDesde')),GETDATE())
SET @pFechaHasta				= ISNULL(CONVERT(DATETIME, dbo.CF_JSON_VALUE(@pJsonRecibido,'FechaHasta')),GETDATE())			
SET @pFormato					= ISNULL(CONVERT(VARCHAR(10), dbo.CF_JSON_VALUE(@pJsonRecibido,'Formato')),'D')			
SET @pId_Tipo_Cli				= ISNULL(CONVERT(INT, dbo.CF_JSON_VALUE(@pJsonRecibido,'Id_Tipo_Cli')),0)		


/*Activos anteriores a Fecha Desde*/

SELECT C.Id_Cliente,MAX(Item) Item INTO #Hist_Max_Ant 
	FROM Clientes C, Clientes_Hist H
	WHERE H.Fecha < @pFechaDesde
	AND C.Id_Cliente = H.Id_Cliente
	AND C.Fecha_Alta < @pFechaDesde
	AND ISNULL(C.Fecha_Baja,@pFechaDesde + 1) > @pFechaDesde
	AND ISNULL(H.Id_Tipo_Cli,0) <> 0
	AND ISNULL(Id_Contrato,0) = 0 
	AND C.Id_Cliente NOT IN (0,1)
	GROUP BY C.Id_Cliente

SELECT C.Id_Cliente,MAX(Item) Item INTO #Hist_Max_Rango_Ant 
	FROM Clientes C, Clientes_Hist H
	WHERE H.Fecha < @pFechaHasta
	AND C.Id_Cliente = H.Id_Cliente
	AND C.Fecha_Alta < @pFechaDesde
	AND ISNULL(C.Fecha_Baja,@pFechaHasta + 1) > @pFechaHasta
	AND ISNULL(H.Id_Tipo_Cli,0) <> 0
	AND ISNULL(Id_Contrato,0) = 0 
	AND C.Id_Cliente NOT IN (0,1)
	GROUP BY C.Id_Cliente



SELECT 'Anterior' AS Tipo,C.Id_Cliente,C.Id_Cliente_Externo, C.Fecha_Alta, C.Fecha_Baja, C.Razon_Social,C.Fecha_Nac
       ,Dbo.CF_Edad_Persona_A_UnaFecha(C.Fecha_Nac,@pFechaDesde -1) AS Edad_Inicio
       ,ISNULL( H.Id_Tipo_Cli,C.Id_Tipo_Cli) AS Id_Tipo_Cli ,ISNULL(T.Descripcion,CCTT.Descripcion) AS Categoria_ini,H.Fecha AS Fecha_Mov
       ,HR.Id_Tipo_Cli AS Id_Tipo_Cli_Fin ,TR.Descripcion AS Categoria_Fin,HR.Fecha AS Fecha_Mov_Fin
       ,C.Id_Motivo_Est , E.Descripcion AS Estado
    INTO #Resultado   
    FROM Clientes C
        LEFT JOIN #Hist_Max_Ant M ON C.Id_Cliente = M.Id_Cliente
        LEFT JOIN Clientes_Hist H ON C.Id_Cliente = H.Id_Cliente AND M.Item = H.Item
        LEFT JOIN Clientes_Tipos T ON H.Id_Tipo_Cli = T.Id_Tipo_Cli
        LEFT JOIN #Hist_Max_Rango_Ant MR ON C.Id_Cliente = MR.Id_Cliente
        LEFT JOIN Clientes_Hist HR ON C.Id_Cliente = HR.Id_Cliente AND MR.Item = HR.Item
        LEFT JOIN Clientes_Tipos TR ON HR.Id_Tipo_Cli = TR.Id_Tipo_Cli
        LEFT JOIN Tab_Motivos_Est E ON E.Id_Motivo_Est = C.Id_Motivo_Est
		, Clientes_Tipos CCTT
    WHERE C.Fecha_Alta < @pFechaDesde
	AND CCTT.Id_Tipo_Cli = C.Id_Tipo_Cli
    AND (C.Fecha_Baja > @pFechaDesde OR C.Fecha_Baja IS NULL)
	AND C.Id_Cliente NOT IN (0,1)

--nuevos socios entre ese rango



SELECT C.Id_Cliente,MIN(Item) Item INTO #Hist_Min_Nuevos
	FROM Clientes C, Clientes_Hist H
	WHERE H.Fecha < @pFechaHasta
	AND C.Id_Cliente = H.Id_Cliente
    AND C.Fecha_Alta >= @pFechaDesde AND C.Fecha_Alta < @pFechaHasta
    AND H.Fecha >= @pFechaDesde AND H.Fecha < @pFechaHasta
    AND (C.Fecha_Baja > @pFechaHasta OR C.Fecha_Baja IS NULL)
	AND ISNULL(H.Id_Tipo_Cli,0) <> 0
	AND ISNULL(Id_Contrato,0) = 0 
	AND C.Id_Cliente NOT IN (0,1)
	GROUP BY C.Id_Cliente


SELECT C.Id_Cliente,MAX(Item) Item INTO #Hist_Max_Nuevos
	FROM Clientes C, Clientes_Hist H
	WHERE H.Fecha < @pFechaHasta
	AND C.Id_Cliente = H.Id_Cliente
    AND C.Fecha_Alta >= @pFechaDesde AND C.Fecha_Alta < @pFechaHasta
    AND H.Fecha >= @pFechaDesde AND H.Fecha < @pFechaHasta
    AND (C.Fecha_Baja > @pFechaHasta OR C.Fecha_Baja IS NULL)
	AND ISNULL(H.Id_Tipo_Cli,0) <> 0
	AND ISNULL(Id_Contrato,0) = 0 
	AND C.Id_Cliente NOT IN (0,1)
	GROUP BY C.Id_Cliente

INSERT INTO #Resultado
SELECT 'Nuevo' AS Tipo,C.Id_Cliente,C.Id_Cliente_Externo, C.Fecha_Alta, C.Fecha_Baja, C.Razon_Social,C.Fecha_Nac
       ,Dbo.CF_Edad_Persona_A_UnaFecha(C.Fecha_Nac,@pFechaDesde -1) AS Edad_Inicio
       ,ISNULL( H.Id_Tipo_Cli,C.Id_Tipo_Cli) AS Id_Tipo_Cli ,ISNULL(T.Descripcion,CCTT.Descripcion) AS Categoria_ini,H.Fecha AS Fecha_Mov
       ,HR.Id_Tipo_Cli AS Id_Tipo_Cli_Fin ,TR.Descripcion AS Categoria_Fin,HR.Fecha AS Fecha_Mov_Fin
       ,C.Id_Motivo_Est , E.Descripcion AS Estado
    FROM Clientes C
        LEFT JOIN #Hist_Min_Nuevos M ON C.Id_Cliente = M.Id_Cliente
			LEFT JOIN Clientes_Hist H ON C.Id_Cliente = H.Id_Cliente AND M.Item = H.Item
				LEFT JOIN Clientes_Tipos T ON H.Id_Tipo_Cli = T.Id_Tipo_Cli
		LEFT JOIN #Hist_Max_Nuevos MR ON C.Id_Cliente = MR.Id_Cliente
			LEFT JOIN Clientes_Hist HR ON C.Id_Cliente = HR.Id_Cliente AND MR.Item = HR.Item
				LEFT JOIN Clientes_Tipos TR ON HR.Id_Tipo_Cli = TR.Id_Tipo_Cli
		LEFT JOIN Tab_Motivos_Est E ON E.Id_Motivo_Est = C.Id_Motivo_Est
		, Clientes_Tipos CCTT
    WHERE C.Fecha_Alta >= @pFechaDesde AND C.Fecha_Alta < @pFechaHasta
	AND CCTT.Id_Tipo_Cli = C.Id_Tipo_Cli
    AND (C.Fecha_Baja > @pFechaHasta OR C.Fecha_Baja IS NULL)
	AND C.Id_Cliente NOT IN (0,1)




			

UPDATE #Resultado SET Id_Tipo_Cli_Fin = Id_Tipo_Cli, Categoria_Fin = Categoria_ini WHERE Id_Tipo_Cli_Fin IS NULL

UPDATE #Resultado SET Fecha_Baja = NULL WHERE Fecha_Baja > @pFechaHasta


IF @pId_Tipo_Cli <> 0
	DELETE #Resultado WHERE Id_Tipo_Cli <> @pId_Tipo_Cli

IF UPPER(@pFormato) = 'C'
BEGIN
	CREATE TABLE #categ  (
			Id_Tipo_Cli INT,
			Categoria VARCHAR(100) , 
			SA DECIMAL(16,4) DEFAULT(0),
			Altas INT DEFAULT(0), 
			Bajas INT DEFAULT(0), 
			Altas_Pasaje INT DEFAULT(0), 
			Bajas_Pasaje INT DEFAULT(0), 
			SF DECIMAL(16,4) DEFAULT(0),
			Porc DECIMAL(12,2) DEFAULT(0)
			)
	
	INSERT INTO #categ (Id_Tipo_Cli,Categoria)
    SELECT Id_Tipo_Cli,Categoria_ini AS Categoria FROM #Resultado GROUP BY Id_Tipo_Cli,Categoria_ini
    UNION 
    SELECT Id_Tipo_Cli_Fin AS Id_Tipo_Cli,Categoria_Fin AS Categoria FROM #Resultado GROUP BY Id_Tipo_Cli_Fin,Categoria_Fin
    
    SELECT Id_Tipo_Cli,Categoria_ini,COUNT(*) AS Cant INTO #Categ_G FROM #Resultado WHERE Tipo = 'Anterior' GROUP BY Id_Tipo_Cli,Categoria_ini
    
    UPDATE #categ SET SA = Cant
       FROM #categ c ,#Categ_G r
       WHERE c.Categoria COLLATE Modern_Spanish_CI_AS = r.Categoria_ini COLLATE Modern_Spanish_CI_AS

    SELECT Id_Tipo_Cli,Categoria_ini,COUNT(*) AS Cant INTO #Categ_N FROM #Resultado WHERE Tipo = 'Nuevo' GROUP BY Id_Tipo_Cli,Categoria_ini

    UPDATE #categ SET Altas = Cant
       FROM #categ c ,#Categ_N R
       WHERE c.Categoria COLLATE Modern_Spanish_CI_AS = r.Categoria_ini COLLATE Modern_Spanish_CI_AS

    SELECT Id_Tipo_Cli,Categoria_ini,COUNT(*) AS Cant INTO #Categ_B FROM #Resultado WHERE Fecha_Baja IS NOT NULL GROUP BY Id_Tipo_Cli,Categoria_ini

    UPDATE #categ SET Bajas = Cant
       FROM #categ c ,#Categ_B R
       WHERE c.Categoria COLLATE Modern_Spanish_CI_AS = r.Categoria_ini COLLATE Modern_Spanish_CI_AS

    SELECT Categoria_Fin,COUNT(*) AS Cant INTO #Categ_AP FROM #Resultado 
			WHERE Fecha_Baja IS NULL AND Categoria_ini COLLATE Modern_Spanish_CI_AS <> Categoria_Fin COLLATE Modern_Spanish_CI_AS 
			GROUP BY Categoria_Fin

    UPDATE #categ SET Altas_Pasaje = Cant
       FROM #categ c ,#Categ_AP R
       WHERE c.Categoria COLLATE Modern_Spanish_CI_AS = r.Categoria_Fin COLLATE Modern_Spanish_CI_AS


    SELECT Categoria_ini,COUNT(*) AS Cant INTO #Categ_BP FROM #Resultado 
			WHERE Fecha_Baja IS NULL AND Categoria_ini COLLATE Modern_Spanish_CI_AS <> Categoria_Fin COLLATE Modern_Spanish_CI_AS 
			GROUP BY Categoria_ini
     --SELECT * FROM #Categ_BP			

    UPDATE #categ SET Bajas_Pasaje = Cant
       FROM #categ c ,#Categ_BP R
       WHERE c.Categoria COLLATE Modern_Spanish_CI_AS = r.Categoria_Ini COLLATE Modern_Spanish_CI_AS

    UPDATE #Categ SET SF = SA + Altas - Bajas + Altas_Pasaje - Bajas_Pasaje
    UPDATE #categ SET Porc = ((SF / SA) - 1) * 100 WHERE SA <> 0

    SELECT Id_Tipo_Cli,Categoria ,
			CONVERT(INT,SA) AS Saldo_Ant,
			Altas ,
			Bajas ,
			Altas_Pasaje ,
			Bajas_Pasaje ,
			CONVERT(INT,SF) Saldo_Final,
			Porc 
		 FROM #Categ   
    WHERE Categoria IS NOT NULL
    ORDER BY Categoria 
       
	
END
ELSE IF  UPPER(@pFormato) = 'CM'
BEGIN
    SELECT Categoria_Ini AS Categoria,Estado, COUNT(*) AS Cantidad FROM #Resultado 
			WHERE Fecha_Baja IS NOT NULL 
			GROUP BY Categoria_Ini,Estado
			ORDER BY Categoria_Ini,Estado


END
ELSE
	SELECT * FROM #Resultado 
		ORDER BY Fecha_Alta



/*

EXEC CPJ_Clientes_Mov '{"FechaDesde":"20210701","FechaHasta":"20220630","Formato":"D"}'

EXEC CPJ_Clientes_Mov '{"FechaDesde":"20210701","FechaHasta":"20220630","Formato":"C"}'

EXEC CPJ_Clientes_Mov '{"FechaDesde":"20210701","FechaHasta":"20220630","Formato":"CM"}'
EXEC CPJ_Clientes_Mov '{"Id_Usuario":"-1","FechaDesde":"20220401","FechaHasta":"20220502","Formato":"D","Id_Tipo_Cli":"42"}' 
EXEC CPJ_Clientes_Mov '{"Id_Usuario":"-1","FechaDesde":"20220601","FechaHasta":"20220801","Formato":"C","id_tipo_cli":"1"}' 

*/