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

CREATE PROCEDURE CPJ_Turnos_Borrar		@pJson VARCHAR(8000)
AS 
SET NOCOUNT ON 

/*Variables recibidas por parámetro a traves del JSON*/
DECLARE @pFlagUsarTrans			TINYINT
DECLARE @pModoDebug				TINYINT
DECLARE @pId_Usuario			SMALLINT
DECLARE @pId_Sector				SMALLINT
DECLARE @pId_Recurso			INT
DECLARE @pFecha_Desde			DATETIME
DECLARE @pFecha_Hasta			DATETIME

SET @pFlagUsarTrans			= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJson,'FlagUsarTrans')), 0)
SET @pModoDebug				= ISNULL(CONVERT(TINYINT, dbo.CF_JSON_VALUE(@pJson,'ModoDebug')), 0)
SET @pId_Usuario			= ISNULL(CONVERT(SMALLINT, dbo.CF_JSON_VALUE(@pJson,'Id_Usuario')), 0)
SET @pId_Sector				= ISNULL(CONVERT(SMALLINT, dbo.CF_JSON_VALUE(@pJson,'Id_Sector')), 0)
SET @pId_Recurso			= ISNULL(CONVERT(INT, dbo.CF_JSON_VALUE(@pJson,'Id_Recurso')), 0)
SET @pFecha_Desde			= ISNULL(CONVERT(DATETIME, dbo.CF_JSON_VALUE(@pJson,'Fecha_Desde')), '19000101')
SET @pFecha_Hasta			= ISNULL(CONVERT(DATETIME, dbo.CF_JSON_VALUE(@pJson,'Fecha_Hasta')), '19000101')
/*FIN Variables recibidas por parámetro a traves del JSON*/

/*Variables Locales*/
DECLARE @cadaDiaAGenerar		DATETIME
DECLARE @Id_Recurso				INT
DECLARE @Id_Recurso_Horario		INT
DECLARE @Dia_De_La_Semana		VARCHAR(50)
DECLARE @Hora_Inicio			DATETIME
DECLARE @Hora_Fin				DATETIME
DECLARE @Frecuencia				INT
DECLARE @cadaMinutoAGenerar		DATETIME
DECLARE @fechaCompletaAGenerar	DATETIME
DECLARE @Flag_Id_Recurso_Bloq	INT
/*FIN Variables Locales*/

BEGIN TRY
	IF @pFlagUsarTrans <> 0 
	BEGIN
		BEGIN TRAN
	END

	IF(@pModoDebug = 1)
	BEGIN
		PRINT 'Ingreso a Stored Procedure: CPJ_Turnos_Borrar'
		PRINT 'Parámetros: '+ @pJson  
	END
	/*===================================================================*/	

	IF @pFecha_Desde = 0 RAISERROR ('Parameter "Fecha_Desde" mandatory', 16,  1)
	IF @pFecha_Hasta = 0 RAISERROR ('Parameter "Fecha_Hasta" mandatory', 16,  1)
	IF @pFecha_Desde >= @pFecha_Hasta  RAISERROR ('Parameter "Fecha_Hasta" greater than or equal Parameter "Fecha_Desde"', 16,  1)
	
	IF(@pId_Sector = 0 AND @pId_Recurso = 0)
	BEGIN
		IF @pId_Sector = 0 RAISERROR ('Parameter "Id_Sector" mandatory', 16,  1)
		IF @pId_Recurso = 0 RAISERROR ('Parameter "Id_Recurso" mandatory', 16,  1)
	END		
	
	/*===================================================================*/	
	
	--POR SECTOR 
	IF(@pId_Sector <> 0)
	BEGIN
		DELETE FROM CRM_Turnos
		WHERE
		Estado = ''
		AND Id_Trans = 0
		AND Fecha <= @pFecha_Desde
		AND Fecha >= @pFecha_Hasta
		AND Id_Recurso IN (SELECT Id_Recurso FROM Empresas_Sedes_Sec_Recursos WHERE Id_Sector = @pId_Sector)
	END
	
	--POR RECURSO
	IF(@pId_Recurso <> 0)
	BEGIN
		DELETE FROM CRM_Turnos
		WHERE
		Estado = ''
		AND Id_Trans = 0
		AND Fecha <= @pFecha_Desde
		AND Fecha >= @pFecha_Hasta
		AND Id_Recurso = @pId_Recurso
	END

	/*===================================================================*/	

	IF @pFlagUsarTrans <> 0 COMMIT

	SELECT	 0				AS ErrorNumber 
			,0				AS ErrorSeverity  
			,0				AS ErrorState 
			,0				AS ErrorProcedure  
			,0				AS ErrorLine		
			,''				AS ErrorMessage

	IF(@pModoDebug = 1)
	BEGIN
		PRINT 'Fin Stored Procedure: CPJ_Turnos_Generar'
	END
	
END TRY  


BEGIN CATCH  
    EXECUTE usp_GetErrorInfo

	IF @pFlagUsarTrans <> 0  ROLLBACK

	RETURN 9999
END CATCH

RETURN 0 

--Ejemplo de uso
--EXEC CPJ_Turnos_Generar '{"FlagUsarTrans":"0", "ModoDebug":"1", "Id_Usuario":"-1", "Id_Sector":"2", "Fecha_Desde":"20200601", "Fecha_Hasta":"20200630"}'