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

CREATE PROCEDURE CP_Job_Cargar @pDB SYSNAME, @pQuery NVARCHAR(4000), @pFecha DATETIME, @pNombre VARCHAR(100)
AS

DECLARE @jn NVARCHAR(200)
		,@descrip			NVARCHAR(100)
		,@fecha				INT
		,@hora				INT
		,@fecha_iso			NVARCHAR(24)
		,@rtaMsjStored		VARCHAR(4000)
		,@productversion	VARCHAR(50)
		,@productlevel		VARCHAR(50)
		,@edition			VARCHAR(50)

SET @fecha_iso = CONVERT(NVARCHAR(100),@pFecha,126) 

SET @jn		=  N'CJ_'+ CAST(@pDB as NVARCHAR(100)) + N'_'+ CAST(@pNombre as NVARCHAR(100)) + N'_'+ @fecha_iso
SET @fecha	= CONVERT(INT, LEFT( CONVERT(NVARCHAR(100),@pFecha,112) ,8) )
SET @hora	= CONVERT(INT, SUBSTRING(@fecha_iso,12,2) +  SUBSTRING(@fecha_iso,15,2) + SUBSTRING(@fecha_iso,18,2) ) 


BEGIN TRY
	BEGIN TRAN

	SELECT	  @productversion = CONVERT(VARCHAR(50),SERVERPROPERTY('productversion'))
			, @productlevel = CONVERT(VARCHAR(50),SERVERPROPERTY ('productlevel'))
			, @edition = CONVERT(VARCHAR(50),SERVERPROPERTY ('edition'))

	IF LEFT(UPPER(@edition),7) = 'EXPRESS'
		RAISERROR ('Jobs cannot be scheduled on EXPRESS database engine versions. Please upgrade DB server.', 16,  1 )
	
	IF EXISTS (SELECT * FROM Cbtes_Lotes_Trabajos WHERE Lote = @pNombre)
	    RAISERROR ('There is already a Job programmed with this ID', 16,  1 ) 

	IF DATEADD(minute,5, GETDATE()) > @pFecha
		 RAISERROR ('The programming date cannot be less than the current day/time plus 5 minutes.', 16,  1 ) 


	IF EXISTS ( SELECT * FROM msdb.dbo.sysjobs WHERE name = @jn)
	BEGIN
		EXEC msdb.dbo.sp_delete_job @job_name = @jn  , @delete_history = 1 , @delete_unused_schedule = 1  
	END
   
	SET @descrip = N'Ejecuta ' + @jn
	EXEC msdb.dbo.sp_add_job  
		@job_name = @jn,
		@description = @descrip;  
  
	EXEC msdb.dbo.sp_add_jobstep  
		@job_name =  @jn,  
		@step_name = @descrip,  
		@subsystem = N'TSQL',  
		@database_name = @pDB,
		@command = @pQuery,   
		@retry_attempts = 1,  
		@retry_interval = 1 ;  


	IF NOT EXISTS ( SELECT * FROM msdb.dbo.sysschedules WHERE name = @jn ) 
	BEGIN
		EXEC msdb.dbo.sp_add_schedule  
			@schedule_name = @jn ,  
			@active_start_date  = @fecha,
			@freq_type = 1,  
			@freq_interval = 1,
			@freq_subday_type  = 0x1,
			@active_start_time = @hora


	END
 

	EXEC msdb.dbo.sp_attach_schedule  
	   @job_name = @jn,   
	   @schedule_name = @jn;  

	EXEC msdb.dbo.sp_add_jobserver  
		@job_name = @jn 


    SELECT TOP 1 @rtaMsjStored = job_id FROM msdb.dbo.sysjobs WHERE name = @jn

    INSERT INTO Cbtes_Lotes_Trabajos (Lote, job_id) VALUES (@pNombre,@rtaMsjStored)		 
	COMMIT
	
								
	SELECT	 0				AS ErrorNumber 
			,0				AS ErrorSeverity  
			,0				AS ErrorState 
			,0				AS ErrorProcedure  
			,0				AS ErrorLine		
			,@rtaMsjStored	AS ErrorMessage


END TRY  


BEGIN CATCH  
    EXECUTE usp_GetErrorInfo

	ROLLBACK

	RETURN 9999
END CATCH

RETURN 0
/*

EXEC CP_Job_Cargar 'Clever_Macabi'
        , ' EXEC [dbo].[CPJ_Cbtes_Lotes_Facturar] ''{"Lote":"SOP-2021-12/6","Fecha":"20211201","Descripcion":"SOP-2021-12/6","Id_Lote_Tipo":"1","Fecha_Desde":"20211201","Fecha_Hasta":"20211231","Fecha_Rem_Desde":"        ","Fecha_Rem_Hasta":"        ","Id_Tipo_Cont":"1","Id_Usuario":"-1","FiltrarTarjeta":"0","FiltrarCliente":"0","FiltrarCondPago":"0","FiltrarTipoCbte":"0","FiltrarCantCont":"0","Refacturar":"1","Incl_Cbtes_Cero":"0","FlagUsarTrans":"0","ModoDebug":"0","Omitir_Resultado_OK":"0"}'' '
		,'20220419 10:27'
		, 'Prueba5'

select * from cbtes_lotes_trabajos

*/


