Untitled

SET LANGUAGE 'English'
SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT, XACT_ABORT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS OFF
GO


--
-- Set transaction isolation level
--
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

--
-- Start Transaction
--
BEGIN TRANSACTION

--
-- Inserting data into table Report.Reports
--

IF NOT EXISTS (SELECT * FROM [Report].[Reports] WHERE Name = 'Correspondence Success Report')

BEGIN
    INSERT INTO [Report].[Reports]
               ([CategoryId]
               ,[Name]
               ,[Description]
               ,[SQL]
               ,[Layout]
               ,[Active]
               ,[Custom_Report]
               ,[CreatedBy]
               ,[CreatedUTCDate]
               ,[LastModifiedBy]
               ,[LastModifiedUtc])
         VALUES
               (2 --Accounting
               ,'Correspondence Success Report'
               ,'A report to see when a correspondence messages has sent successfully'
               ,'EXEC Report.CorrespondenceSuccessReport'
               ,'[[1]]'
               ,1
               ,0
               ,'system'
               ,GETDATE()
                ,NULL
               ,GETDATE())

    IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END

    DECLARE @ReportId INT = @@IDENTITY;

    --
    -- Inserting data into ReportParameter
    --

    DECLARE @endDateId INT;
    SET @endDateId = (SELECT TOP(1) Id from [Report].[Parameter] where ParameterName='EndDate');

    INSERT INTO [Report].[ReportParameter]
               ([ReportId]
               ,[ParameterId]
               ,[DefaultValue]
               ,[FieldLabel]
               ,[SQLParameter]
               ,[NumOrder]
               ,[Mandatory]
               ,[CreatedBy]
               ,[CreatedUTC]
               ,[Modifiedby]
               ,[ModifiedUTC])
         VALUES
               (@ReportId
               ,@endDateId
               ,NULL
               ,NULL
               ,'@d_end_date'
               ,30
               ,1
               ,'system'
               ,GETDATE()
               ,NULL
               ,GETDATE())


    IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END

    --
    -- Inserting data into ReportParameter
    --

    DECLARE @startDateId INT;
    SET @startDateId = (SELECT TOP(1) Id from [Report].[Parameter] where ParameterName='StartDate');

    INSERT INTO [Report].[ReportParameter]
               ([ReportId]
               ,[ParameterId]
               ,[DefaultValue]
               ,[FieldLabel]
               ,[SQLParameter]
               ,[NumOrder]
               ,[Mandatory]
               ,[CreatedBy]
               ,[CreatedUTC]
               ,[Modifiedby]
               ,[ModifiedUTC])
         VALUES
               (@ReportId
               ,@startDateId
               ,NULL
               ,NULL
               ,'@d_start_date'
               ,20
               ,1
               ,'system'
               ,GETDATE()
               ,NULL
               ,GETDATE())

    IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END

    --
    -- Inserting data into CorrespondenceName
    --

    DECLARE @correspondenceNameId INT;
    SET @correspondenceNameId = (SELECT TOP(1) Id from [Report].[Parameter] where ParameterName='CorrespondenceName');

    --
    -- Inserting data into ReportParameter
    --

    INSERT INTO [Report].[ReportParameter]
               ([ReportId]
               ,[ParameterId]
               ,[DefaultValue]
               ,[FieldLabel]
               ,[SQLParameter]
               ,[NumOrder]
               ,[Mandatory]
               ,[CreatedBy]
               ,[CreatedUTC]
               ,[Modifiedby]
               ,[ModifiedUTC])
         VALUES
               (@ReportId
               ,@correspondenceNameId
               ,'All'
               ,NULL
               ,'@v_correspondence_name'
               ,40
               ,1
               ,'system'
               ,GETDATE()
               ,NULL
               ,GETDATE())

    IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END


    --
    -- Inserting data into ReportDataTable
    --

    INSERT INTO [Report].[ReportDataTable]
               ([ReportID]
               ,[DataTableID]
               ,[DataSetTypeId]
               ,[Title])
         VALUES
               (@ReportId
               ,1
               ,1
               ,NULL)

    IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END

    INSERT INTO [Report].[OrgReports](ReportId
        ,[OrgId]
        ,[Active]
        ,[CreatedBy]
        ,[CreatedUtcDate]
        ,[LastModifiedBy]
        ,[LastModifiedUtc])
    SELECT @ReportId, org.Id, 1, 'system', GETUTCDATE(), null, null
    FROM (
	      SELECT Id FROM Organisations
    ) AS org

    IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END

END
--
-- Commit Transaction
--
IF @@TRANCOUNT>0 COMMIT TRANSACTION
SET NOEXEC OFF
GO