USE [Exago]
GO
/****** Object:  Table [dbo].[QueueApiJobs]    Script Date: 8/4/2017 2:21:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[QueueApiJobs](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[JobId] [uniqueidentifier] NULL,
	[NextExecuteDate] [datetime2](0) NULL,
	[LastExecuteDate] [datetime2](0) NULL,
	[Status] [tinyint] NULL,
	[ServiceName] [nvarchar](50) NULL,
	[DbServerIp] [nvarchar](50) NULL,
	[CompanyId] [nvarchar](128) NULL,
	[UserId] [nvarchar](128) NULL,
	[ScheduleName] [nvarchar](250) NULL,
	[ReportName] [nvarchar](500) NULL,
	[RecurrencePattern] [varchar](50) NULL,
	[ScheduleDay] [varchar](50) NULL,
	[ScheduleTime] [time](0) NULL,
	[EmailTo] [nvarchar](max) NULL,
	[EmailSubject] [nvarchar](max) NULL,
	[EmailBody] [nvarchar](max) NULL,
	[ReportId] [uniqueidentifier] NULL,
	[ReportXml] [nvarchar](max) NULL,
	[Xml] [nvarchar](max) NULL,
	[Environment] [varchar](50) NULL,
	[AttachmentFormat] [nvarchar](50) NULL,
	[AttachmentPassword] [nvarchar](50) NULL,
 CONSTRAINT [PK_QueueApiJobs] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[QueueApiLock]    Script Date: 8/4/2017 2:21:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[QueueApiLock](
	[LockHost] [nvarchar](50) NULL,
	[LockDateTime] [datetime] NULL
) ON [PRIMARY]

GO
/****** Object:  StoredProcedure [dbo].[DeleteReport]    Script Date: 8/4/2017 2:21:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************************************
-- Procedure: dbo.DeleteReport
--
-- Developer:	DKillough
-- Application:	Exago Scheduler Queue
--
-- Parameters:	JobXml
--		
-- Description:	Save the passed schedule   
-- Creation Date: 10/03/2016
-- Returns: next schedule 
-- Modification Log:
-- Date			Developer			Comments
-- -------		----------------	----------------
-- 
--
******************************************************************************************/
CREATE PROCEDURE [dbo].[DeleteReport]
    @ReportID UNIQUEIDENTIFIER 
AS
BEGIN
    SET NOCOUNT ON 
    SET XACT_ABORT ON 
    BEGIN TRANSACTION 
    UPDATE dbo.QueueApiLock SET LockHost='DeleteReport',LockDateTime=GETDATE() 
    IF @@ROWCOUNT != 1 BEGIN
        DELETE FROM dbo.QueueApiLock 
        INSERT INTO dbo.QueueApiLock (LockHost,LockDateTime) VALUES ('DeleteReport',GETDATE())
    END
	DELETE FROM dbo.QueueApiJobs WHERE ReportId = @ReportId 
    COMMIT TRANSACTION 
END 

GO
/****** Object:  StoredProcedure [dbo].[DeleteSchedule]    Script Date: 8/4/2017 2:21:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************************************
-- Procedure: dbo.DeleteSchedule
--
-- Developer:   DKillough
-- Application: Exago Scheduler Queue
--
-- Parameters:  JobId
--		
-- Description: Delete the passed schedule   
-- Creation Date: 02/15/2017
-- Returns: next schedule 
-- Modification Log:
-- Date			Developer			Comments
-- -------		----------------	----------------
-- 
--
******************************************************************************************/
CREATE PROCEDURE [dbo].[DeleteSchedule]
    @JobID UNIQUEIDENTIFIER 
AS
BEGIN
    SET NOCOUNT ON 
    SET XACT_ABORT ON 
    BEGIN TRANSACTION 
    UPDATE dbo.QueueApiLock SET LockHost='DeleteSchedule',LockDateTime=GETDATE() 
    IF @@ROWCOUNT != 1 BEGIN
        DELETE FROM dbo.QueueApiLock 
        INSERT INTO dbo.QueueApiLock (LockHost,LockDateTime) VALUES ('DeleteSchedule',GETDATE())
    END
    DELETE FROM dbo.QueueApiJobs WHERE JobId = @JobID AND ScheduleName LIKE 'DPO SCHED:%'
    COMMIT TRANSACTION 
END 



GO
/****** Object:  StoredProcedure [dbo].[Flush]    Script Date: 8/4/2017 2:21:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************************************
-- Procedure: dbo.Flush
--
-- Developer:	DKillough
-- Application:	Exago Scheduler Queue
--
-- Parameters:	JobXml
--		
-- Description:	Save the passed schedule   
-- Creation Date: 10/03/2016
-- Returns: next schedule 
-- Modification Log:
-- Date			Developer			Comments
-- -------		----------------	----------------
-- 
--
EXEC dbo.Flush 'Company','demosysgen',''
******************************************************************************************/
CREATE PROCEDURE [dbo].[Flush]
    @ViewLevel NVARCHAR(20),
    @CompanyId NVARCHAR(128),
    @UserId NVARCHAR(128)  
AS
BEGIN
    SET NOCOUNT ON 
    SET XACT_ABORT ON 
    BEGIN TRANSACTION 
    UPDATE dbo.QueueApiLock SET LockHost='GetJobList',LockDateTime=GETDATE() 
    IF @@ROWCOUNT != 1 BEGIN
        DELETE FROM dbo.QueueApiLock 
        INSERT INTO dbo.QueueApiLock (LockHost,LockDateTime) VALUES ('GetJobList',GETDATE())
    END
    IF @ViewLevel = 'company'  
		DELETE FROM dbo.QueueApiJobs    
		WHERE [Status] IN (2,4) AND CompanyId = @CompanyId 

    ELSE IF @ViewLevel = 'User'
		DELETE FROM dbo.QueueApiJobs    
        WHERE [Status] IN (2,4)  AND UserId = @UserId 
    ELSE
		DELETE FROM dbo.QueueApiJobs
		WHERE [Status] IN (2,4)
    COMMIT TRANSACTION 
END 

GO
/****** Object:  StoredProcedure [dbo].[GetJobData]    Script Date: 8/4/2017 2:21:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************************************
-- Procedure: dbo.GetJobData
--
-- Developer:	DKillough
-- Application:	Exago Scheduler Queue
--
-- Parameters:	
--		
-- Description:	Save the passed schedule   
-- Creation Date: 10/03/2016
-- Returns: next schedule 
-- Modification Log:
-- Date			Developer			Comments
-- -------		----------------	----------------
-- 
--
EXEC dbo.GetJobData 'Company','demosysgen',''
******************************************************************************************/
CREATE PROCEDURE [dbo].[GetJobData]
    @JobId UNIQUEIDENTIFIER 
AS
BEGIN
    SET NOCOUNT ON 
    BEGIN TRANSACTION 
    UPDATE dbo.QueueApiLock SET LockHost='GetJobData',LockDateTime=GETDATE() 
    IF @@ROWCOUNT != 1 BEGIN
        DELETE FROM dbo.QueueApiLock 
        INSERT INTO dbo.QueueApiLock (LockHost,LockDateTime) VALUES ('GetJobData',GETDATE())
    END
	SELECT 
		JobId,NextExecuteDate,LastExecuteDate,[Status],ServiceName,CompanyId,UserId,
		ScheduleName,ReportName,RecurrencePattern,ScheduleDay,ScheduleTime,
		EmailTo,EmailSubject,EmailBody,ReportId,ReportXml,[Xml],Environment,
		AttachmentFormat, AttachmentPassword 
	FROM dbo.QueueApiJobs    
    WHERE JobId = @JobId 
    COMMIT TRANSACTION 
END 

GO
/****** Object:  StoredProcedure [dbo].[GetJobList]    Script Date: 8/4/2017 2:21:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************************************
-- Procedure: dbo.GetJobList
--
-- Developer:    DKillough
-- Application:    Exago Scheduler Queue
--
-- Parameters:    JobXml
--        
-- Description:    Save the passed schedule   
-- Creation Date: 10/03/2016
-- Returns: next schedule 
-- Modification Log:
-- Date            Developer            Comments
-- -------        ----------------    ----------------
-- 02/02/2017     DKillough           filter out DPO Easy Report schedules
--
EXEC dbo.GetJobList 'Company','demosysgen',''
******************************************************************************************/
CREATE PROCEDURE [dbo].[GetJobList]
    @ViewLevel NVARCHAR(20),
    @CompanyId NVARCHAR(128),
    @UserId NVARCHAR(128)  
AS
BEGIN
    BEGIN TRANSACTION 
    UPDATE dbo.QueueApiLock SET LockHost='GetJobList',LockDateTime=GETDATE() 
    IF @@ROWCOUNT != 1 BEGIN
        DELETE FROM dbo.QueueApiLock 
        INSERT INTO dbo.QueueApiLock (LockHost,LockDateTime) VALUES ('GetJobList',GETDATE())
    END
    IF @ViewLevel = 'company'  
        SELECT 
            JobId,NextExecuteDate,LastExecuteDate,[Status],ServiceName,CompanyId,UserId,
            ScheduleName,ReportName,RecurrencePattern,ScheduleDay,ScheduleTime,
            EmailTo,EmailSubject,EmailBody,ReportId,ReportXml,[Xml],Environment,
            AttachmentFormat, AttachmentPassword
        FROM dbo.QueueApiJobs    
        WHERE CompanyId = @CompanyId 
        AND ScheduleName NOT LIKE 'DPO SCHED:%'

    ELSE IF @ViewLevel = 'User'
        SELECT 
            JobId,NextExecuteDate,LastExecuteDate,[Status],ServiceName,CompanyId,UserId,
            ScheduleName,ReportName,RecurrencePattern, ScheduleDay, ScheduleTime,
            EmailTo,EmailSubject,EmailBody,ReportId,ReportXml,[Xml],Environment,
            AttachmentFormat, AttachmentPassword
        FROM dbo.QueueApiJobs    
        WHERE CompanyId = @CompanyId 
	   AND ScheduleName NOT LIKE 'DPO SCHED:%'
    ELSE
        SELECT TOP 100
            JobId,NextExecuteDate,LastExecuteDate,[Status],ServiceName,CompanyId,UserId,
            ScheduleName,ReportName,RecurrencePattern, ScheduleDay, ScheduleTime,
            EmailTo,EmailSubject,EmailBody,ReportId,ReportXml,[Xml],Environment,
            AttachmentFormat, AttachmentPassword
        FROM dbo.QueueApiJobs
	   WHERE ScheduleName NOT LIKE 'DPO SCHED:%'
    COMMIT TRANSACTION         
END 

GO
/****** Object:  StoredProcedure [dbo].[GetNextExecuteJob]    Script Date: 8/4/2017 2:21:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************************************
-- Procedure: dbo.GetNextExecuteJob
--
-- Developer:	DKillough
-- Application:	Exago Scheduler Queue
--
-- Parameters:	ServiceName
--		
-- Description:	Retrieve the next schedule to run  
-- Creation Date: 10/03/2016
-- Returns: next schedule 
--

-- Modification Log:
-- Date			Developer			Comments
-- -------		----------------	----------------   
--
******************************************************************************************/
CREATE PROCEDURE [dbo].[GetNextExecuteJob]
    @ServiceName VARCHAR(200) 
AS
BEGIN
    DECLARE @JobId UNIQUEIDENTIFIER 
    
    SET NOCOUNT ON 
    SET XACT_ABORT ON 
    BEGIN TRANSACTION 
    -- Get exclusive access ... 
    UPDATE dbo.QueueApiLock SET LockHost='test',LockDateTime=GETDATE() 
    IF @@ROWCOUNT != 1 BEGIN
        DELETE FROM dbo.QueueApiLock 
        INSERT INTO dbo.QueueApiLock (LockHost,LockDateTime) VALUES (@ServiceName,GETDATE())
    END
    -- get next job 
	SELECT TOP 1 @JobId = JobId 
	FROM dbo.QueueApiJobs 
	WHERE NextExecuteDate <= GETDATE() AND Status = 0 
	AND DbServerIp NOT IN (
	    SELECT DbServerIp FROM dbo.QueueApiJobs WHERE [Status] = 1
	    GROUP BY DbServerIp HAVING COUNT(*) >= 3 
	)
	ORDER BY NextExecuteDate, CompanyId, Id    
    -- return next job 
	SELECT 
		JobId,NextExecuteDate,LastExecuteDate,[Status],ServiceName,CompanyId,UserId,
		ScheduleName,ReportName,RecurrencePattern,ScheduleDay,ScheduleTime,
		EmailTo,EmailSubject,EmailBody,ReportId,ReportXml,[Xml],Environment,
		AttachmentFormat, AttachmentPassword
	FROM dbo.QueueApiJobs 
	WHERE JobId = @JobId
	-- mark job as running 
	UPDATE dbo.QueueApiJobs SET ServiceName = @ServiceName, Status = 1 
	WHERE JobId = @JobId
	-- commit
    COMMIT TRANSACTION 
END 

GO
/****** Object:  StoredProcedure [dbo].[RenameReport]    Script Date: 8/4/2017 2:21:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************************************
-- Procedure: dbo.RenameReport
--
-- Developer:	DKillough
-- Application:	Exago Scheduler Queue
--
-- Parameters:	JobXml
--		
-- Description:	Save the passed schedule   
-- Creation Date: 10/03/2016
-- Returns: next schedule 
-- Modification Log:
-- Date			Developer			Comments
-- -------		----------------	----------------
-- 
--
EXEC dbo.DeleteReport 
******************************************************************************************/
CREATE PROCEDURE [dbo].[RenameReport]
    @ReportID UNIQUEIDENTIFIER,
    @ReportName NVARCHAR(500)
AS
BEGIN
    SET NOCOUNT ON 
    SET XACT_ABORT ON 
    BEGIN TRANSACTION 
    UPDATE dbo.QueueApiLock SET LockHost='RenameReport',LockDateTime=GETDATE() 
    IF @@ROWCOUNT != 1 BEGIN
        DELETE FROM dbo.QueueApiLock 
        INSERT INTO dbo.QueueApiLock (LockHost,LockDateTime) VALUES ('RenameReport',GETDATE())
    END
    UPDATE dbo.QueueApiJobs SET ReportName=@ReportName WHERE ReportId=@ReportId 
    COMMIT TRANSACTION 
END 

GO
/****** Object:  StoredProcedure [dbo].[SaveJob]    Script Date: 8/4/2017 2:21:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************************************
-- Procedure: dbo.SaveJob
--
-- Developer:	DKillough
-- Application:	Exago Scheduler Queue
--
-- Parameters:	JobXml
--		
-- Description:	Save the passed schedule   
-- Creation Date: 10/03/2016
-- Returns: next schedule 
-- Modification Log:
-- Date			Developer			Comments
-- -------		----------------	----------------
-- 
******************************************************************************************/
CREATE PROCEDURE [dbo].[SaveJob]
    @JobId UNIQUEIDENTIFIER,
    @NextExecuteDate DATETIME2(0),
    @LastExecuteDate DATETIME2(0),
    @Status TINYINT,
    @DbServerIp NVARCHAR(50),
    @CompanyId NVARCHAR(128),
    @UserId NVARCHAR(128),
    @ScheduleName NVARCHAR(128),
    @ReportName NVARCHAR(500),
    @RecurrencePattern VARCHAR(10),
    @ScheduleDay VARCHAR(50),
    @ScheduleTime TIME(0),
    @EmailTo NVARCHAR(MAX),
    @EmailSubject NVARCHAR(MAX),
    @EmailBody NVARCHAR(MAX),
    @ReportId UNIQUEIDENTIFIER,
    @ReportXml NVARCHAR(MAX),
    @Xml NVARCHAR(MAX),
    @Environment VARCHAR(50),
    @AttachmentFormat VARCHAR(50),
    @AttachmentPassword VARCHAR(50)   
AS
BEGIN
    SET NOCOUNT ON 
    SET XACT_ABORT ON 
    BEGIN TRANSACTION 
    -- Get exclusive access ... 
    UPDATE dbo.QueueApiLock SET LockHost='SaveJob',LockDateTime=GETDATE() 
    IF @@ROWCOUNT != 1 BEGIN
        DELETE FROM dbo.QueueApiLock 
        INSERT INTO dbo.QueueApiLock (LockHost,LockDateTime) VALUES ('SaveJob',GETDATE())
    END
    IF @Status = 8 BEGIN
        DELETE FROM dbo.QueueApiJobs WHERE JobId=@JobId
        COMMIT TRANSACTION 
        RETURN 
    END
	UPDATE dbo.QueueApiJobs 
	SET 
	    JobId=@JobId,
		NextExecuteDate=@NextExecuteDate,
		LastExecuteDate=@LastExecuteDate,
		[Status]=@Status,
		--DbServerIp=@DbServerIp,  not in updates
		CompanyId=@CompanyId,
		UserId=@UserId,
		ScheduleName=@ScheduleName,
		ReportName=@ReportName,
		AttachmentFormat=@AttachmentFormat,
		AttachmentPassword=@AttachmentPassword,
		RecurrencePattern=@RecurrencePattern,
		ScheduleDay=@ScheduleDay,
		ScheduleTime=@ScheduleTime,
		EmailTo=@EmailTo,   
		EmailSubject=@EmailSubject, 
		EmailBody=@EmailBody, 
		ReportId=@ReportId,
		ReportXml=@ReportXml,
		[Xml]=@Xml
	WHERE JobId=@JobId   
	
	IF @@ROWCOUNT = 0 -- no row was updated, so insert 
		INSERT INTO dbo.QueueApiJobs (
			JobId,NextExecuteDate,LastExecuteDate,[Status],DbServerIp,CompanyId,UserId,
			ScheduleName,ReportName,RecurrencePattern,ScheduleDay,ScheduleTime,
			EmailTo,EmailSubject,EmailBody,ReportId,ReportXml,[Xml],Environment,
			AttachmentFormat, AttachmentPassword
		) 
		VALUES (
		    @JobId,@NextExecuteDate,@LastExecuteDate,@Status,@DbServerIp,@CompanyId,@UserId,
		    @ScheduleName,@ReportName,@RecurrencePattern,@ScheduleDay,@ScheduleTime,
		    @EmailTo,@EmailSubject,@EmailBody,@ReportId,@ReportXml,@Xml,@Environment,
		    @AttachmentFormat, @AttachmentPassword
		)
		    
    IF @Status = 0 -- clear the service name 
        UPDATE dbo.QueueApiJobs SET ServiceName = '' WHERE JobId = @JobId
        
    COMMIT TRANSACTION     
END 

GO
/****** Object:  StoredProcedure [dbo].[Start]    Script Date: 8/4/2017 2:21:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************************************
-- Procedure: dbo.Start
--
-- Developer:	DKillough
-- Application:	Exago Scheduler Queue
--
-- Parameters:	ServiceName
--		
-- Description:	Retrieve the next schedule to run  
-- Creation Date: 10/03/2016
-- Returns: next schedule 
--
--Sample execution:

-- Modification Log:
-- Date			Developer			Comments
-- -------		----------------	----------------   
--
******************************************************************************************/
CREATE PROCEDURE [dbo].[Start]
    @ServiceName VARCHAR(200) 
AS
BEGIN
    SET NOCOUNT ON 
    SET XACT_ABORT ON 
    BEGIN TRANSACTION 
    UPDATE dbo.QueueApiLock SET LockHost='SaveJob',LockDateTime=GETDATE() 
    IF @@ROWCOUNT != 1 BEGIN
        DELETE FROM dbo.QueueApiLock 
        INSERT INTO dbo.QueueApiLock (LockHost,LockDateTime) VALUES ('SaveJob',GETDATE())
    END
    UPDATE dbo.QueueApiJobs SET [Status] = 0 
    WHERE [Status] = 1 AND ServiceName = @ServiceName 
    COMMIT TRANSACTION 
END 

GO
/****** Object:  StoredProcedure [dbo].[UpdateReport]    Script Date: 8/4/2017 2:21:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************************************
-- Procedure: dbo.UpdateReport
--
-- Developer: DKillough
-- Application:	Exago Scheduler Queue
-- Description:	Save the passed schedule   
-- Creation Date: 10/03/2016
--  
-- Modification Log:
-- Date			Developer			Comments
-- ----------	----------------	-------------------------
-- 11/01/2016	dkillough			concurrency improvements
--
EXEC dbo.UpdateReport 
******************************************************************************************/
CREATE PROCEDURE [dbo].[UpdateReport]
    @ReportID UNIQUEIDENTIFIER,
    @ReportXML NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON 
    SET XACT_ABORT ON 
    BEGIN TRANSACTION 
    UPDATE dbo.QueueApiLock SET LockHost='UpdateReport',LockDateTime=GETDATE() 
    IF @@ROWCOUNT != 1 BEGIN
        DELETE FROM dbo.QueueApiLock 
        INSERT INTO dbo.QueueApiLock (LockHost,LockDateTime) VALUES ('UpdateReport',GETDATE())
    END
	UPDATE dbo.QueueApiJobs SET ReportXML=@ReportXML WHERE ReportId=@ReportId 
	COMMIT TRANSACTION 
END 

GO
