IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'dbo.CFJ_Contratos_Periodo_Validar') 
                   and type = 'FN')
BEGIN
    DROP FUNCTION dbo.CFJ_Contratos_Periodo_Validar
END
GO

CREATE FUNCTION dbo.CFJ_Contratos_Periodo_Validar(@pJsonRecibido VARCHAR(8000))
RETURNS INT
AS
BEGIN
DECLARE @Id_Contrato_En_Periodo		INT

/*Variables recibidas por parametro a traves del JSON*/
DECLARE @pId_Usuario			SMALLINT
DECLARE @pId_Cliente			INT
DECLARE @pId_Producto			VARCHAR(14)
DECLARE @pFecha_Desde			DATETIME
DECLARE @pFecha_Hasta			DATETIME
DECLARE @pId_Contrato_A_Excluir INT

SET @pId_Usuario				= ISNULL(CONVERT(SMALLINT, dbo.CF_JSON_VALUE(@pJsonRecibido,'Id_Usuario')), 0)
SET @pId_Cliente				= ISNULL(CONVERT(INT, dbo.CF_JSON_VALUE(@pJsonRecibido,'Id_Cliente')), 0)
SET @pId_Producto				= ISNULL(CONVERT(VARCHAR(14), dbo.CF_JSON_VALUE(@pJsonRecibido,'Id_Producto')), '')
SET @pFecha_Desde				= ISNULL(CONVERT(DATETIME, dbo.CF_JSON_VALUE(@pJsonRecibido,'Fecha_Desde')), GETDATE())
SET @pFecha_Hasta				= ISNULL(CONVERT(DATETIME, dbo.CF_JSON_VALUE(@pJsonRecibido,'Fecha_Hasta')), '20501231')
SET @pId_Contrato_A_Excluir		= ISNULL(CONVERT(INT, dbo.CF_JSON_VALUE(@pJsonRecibido,'Id_Contrato')), 0)
/*FIN Variables recibidas por parametro a traves del JSON*/

/*Variable Locales*/

/*FIN Variable Locales*/

SET @Id_Contrato_En_Periodo = ISNULL(	(SELECT MAX(CT.Id_Contrato) 
										FROM Contratos CT, Contratos_Prod CP
										WHERE 										
										CT.Id_Contrato = CP.Id_Contrato
										AND CT.Activo = 1
										AND CT.Id_Contrato <> @pId_Contrato_A_Excluir
										AND CT.Id_Cliente = @pId_Cliente
										AND CP.Id_Producto = @pId_Producto
										AND CT.Fecha_Desde <= @pFecha_Desde
										AND ISNULL(CT.Fecha_Hasta,'20991231') >= @pFecha_Desde)
									, 0)

IF(@Id_Contrato_En_Periodo <> 0)
BEGIN
	RETURN @Id_Contrato_En_Periodo
END
ELSE
BEGIN
	SET @Id_Contrato_En_Periodo = ISNULL(	(SELECT MAX(CT.Id_Contrato) 
											FROM Contratos CT, Contratos_Prod CP
											WHERE 										
											CT.Id_Contrato = CP.Id_Contrato
											AND CT.Activo = 1
											AND CT.Id_Contrato <> @pId_Contrato_A_Excluir
											AND CT.Id_Cliente = @pId_Cliente
											AND CP.Id_Producto = @pId_Producto
											AND CT.Fecha_Desde <= @pFecha_Hasta
											AND ISNULL(CT.Fecha_Hasta,'20991231') >= @pFecha_Hasta)
										, 0)
	IF(@Id_Contrato_En_Periodo <> 0)
	BEGIN
		RETURN @Id_Contrato_En_Periodo
	END
	ELSE
	BEGIN
		SET @Id_Contrato_En_Periodo = ISNULL(	(SELECT MAX(CT.Id_Contrato) 
										FROM Contratos CT, Contratos_Prod CP
										WHERE 										
										CT.Id_Contrato = CP.Id_Contrato
										AND CT.Activo = 1
										AND CT.Id_Contrato <> @pId_Contrato_A_Excluir
										AND CT.Id_Cliente = @pId_Cliente
										AND CP.Id_Producto = @pId_Producto
										AND CT.Fecha_Desde >= @pFecha_Desde
										AND ISNULL(CT.Fecha_Hasta,'20991231') <= @pFecha_Hasta)
									, 0)
	END
END

RETURN ISNULL(@Id_Contrato_En_Periodo, 0)
END


/*
Si existe un contrato (Ej. Id_Contrato 25250) que contenga el Id_Producto: P0273 en Contratos_Prod y tiene Fecha_Desde = 25/01/2019 y Fecha_Hasta = 31/12/2021
Las respuestas de la funcion seran las siguientes para los periodos dados:
SELECT dbo.CFJ_Contratos_Periodo_Validar('{"Id_Cliente":"89115","Id_Producto":"P0273","Fecha_Desde":"20190101","Fecha_Hasta":"20190124"')	--1)	0
SELECT dbo.CFJ_Contratos_Periodo_Validar('{"Id_Cliente":"89115","Id_Producto":"P0273","Fecha_Desde":"20220101","Fecha_Hasta":"20221231"')	--2)	0
SELECT dbo.CFJ_Contratos_Periodo_Validar('{"Id_Cliente":"89115","Id_Producto":"P0273","Fecha_Desde":"20190101","Fecha_Hasta":"20190126"')	--3)	25250
SELECT dbo.CFJ_Contratos_Periodo_Validar('{"Id_Cliente":"89115","Id_Producto":"P0273","Fecha_Desde":"20211230","Fecha_Hasta":"20221231"')	--4)	25250
SELECT dbo.CFJ_Contratos_Periodo_Validar('{"Id_Cliente":"89115","Id_Producto":"P0273","Fecha_Desde":"20190125","Fecha_Hasta":"20211231"')	--5)	25250

Si existe un contrato (Ej. Id_Contrato 25250) que contenga el Id_Producto: P0273 en Contratos_Prod y tiene Fecha_Desde = 25/01/2019 y Fecha_Hasta = NULL
Las respuestas de la funcion seran las siguientes para los periodos dados:
SELECT dbo.CFJ_Contratos_Periodo_Validar('{"Id_Cliente":"89115","Id_Producto":"P0273","Fecha_Desde":"20190101","Fecha_Hasta":"20190124"')	--1)	0
SELECT dbo.CFJ_Contratos_Periodo_Validar('{"Id_Cliente":"89115","Id_Producto":"P0273","Fecha_Desde":"20220101","Fecha_Hasta":"20221231"')	--2)	25250
SELECT dbo.CFJ_Contratos_Periodo_Validar('{"Id_Cliente":"89115","Id_Producto":"P0273","Fecha_Desde":"20190101","Fecha_Hasta":"20190126"')	--3)	25250
SELECT dbo.CFJ_Contratos_Periodo_Validar('{"Id_Cliente":"89115","Id_Producto":"P0273","Fecha_Desde":"20211230","Fecha_Hasta":"20221231"')	--4)	25250
SELECT dbo.CFJ_Contratos_Periodo_Validar('{"Id_Cliente":"89115","Id_Producto":"P0273","Fecha_Desde":"20190125","Fecha_Hasta":"20211231"')	--5)	25250
*/