USE [TPS_Dev_DB]

GO

​

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

​

ALTER PROCEDURE [dbo].[TPS_sp_MappingGetReport3]

    @uploadbatch_id varchar(100)

AS

BEGIN

    Declare @uploadbatchid varchar(40)

    Declare @templateName nvarchar(255) = ''

​

    select @templateName = tn.TemplateName 

    from [dbo].[TPS_MasterUploadDetails] mud WITH(NOLOCK)

    inner join [dbo].[TPS_TemplateName_List] tn WITH(NOLOCK) on tn.TNID = mud.UploadTemplateType

    where mud.UploadBatchID = @uploadbatch_id

​

    set @uploadbatchid = @uploadbatch_id

​

    DECLARE @pmid nvarchar(max), @Domain nvarchar(max), @BaselineUnqID nvarchar(max), @RequirementStatement nvarchar(max),

            @ResultMapping nvarchar(max), @PolicyDetails nvarchar(max), @MaturityRatings nvarchar(max),

            @Createdby nvarchar(max), @CreatedDate nvarchar(max), @IdentifiedGaps nvarchar(max)

​

    CREATE TABLE #TPS_Report(

        Domain nvarchar(max),

        BaselineUnqID nvarchar(max),

        RequirementStatement nvarchar(max),

        ResultMapping nvarchar(max),

        PolicyDetails nvarchar(max),

        MaturityRatings nvarchar(max),

        IdentifiedGaps nvarchar(max),

        Createdby nvarchar(max),

        CreatedDate nvarchar(max),

        IncludedControls nvarchar(max),

        ExcludedControls nvarchar(max),

        IsExcluded bit

    )

​

    IF @templateName <> 'HITRUST'

    BEGIN

        ALTER TABLE #TPS_Report DROP COLUMN MaturityRatings;

    END

​

    -- Temporary table to store excluded controls

    CREATE TABLE #TPS_ControlStandardsExcluded(

        [CXIID] [bigint] NOT NULL,

        [PMID] [varchar](40) NOT NULL,

        [ControlStandards] [nvarchar](max) NULL,

        [CreatedDate] [datetime] NULL,

        [IsActive] [bit] NOT NULL,

        [ActionType] [varchar](40) NOT NULL,

        [RequirementStatement] [nvarchar](max) NULL,

        [MatchingScore] float

    )

​

    DECLARE cursor_ReportData CURSOR FOR

    Select Domain, BaselineUnqID, RequirementStatement, PMID, Comments

    from [dbo].[TPS_Upload_PolicyMappingData] WITH(NOLOCK)

    where UploadBatchID = @uploadbatchid

​

    OPEN cursor_ReportData;

    FETCH NEXT FROM cursor_ReportData INTO @Domain, @BaselineUnqID, @RequirementStatement, @pmid, @IdentifiedGaps;

​

    WHILE @@FETCH_STATUS = 0

    BEGIN

        set @ResultMapping = ''

        set @Createdby = ''

        set @PolicyDetails = ''

        DECLARE @IncludedControls nvarchar(max) = ''

        DECLARE @ExcludedControls nvarchar(max) = ''

​

        -- Process TPS_ControlStandardsCurrent

        Select 

            @ResultMapping = STRING_AGG(ControlStandards, CHAR(13)),

            @PolicyDetails = STRING_AGG(Concat(ControlStandards, ': ', replace(replace(RequirementStatement, CHAR(149), ''), CHAR(10), ''), CHAR(13), CHAR(13)), ''),

            @Createdby = STRING_AGG(ISNULL(CreatedBy, 'Auto'), CHAR(13)),

            @CreatedDate = STRING_AGG(CreatedDate, CHAR(13))

        from [dbo].[TPS_ControlStandardsCurrent] WITH(NOLOCK)

        where PMID = @pmid and IsActive = 1

​

        -- Process TPS_ControlStandardsIncluded

        Select 

            @IncludedControls = STRING_AGG(Concat(ControlStandards, ': ', replace(replace(RequirementStatement, CHAR(149), ''), CHAR(10), ''), CHAR(13), CHAR(13)), '')

        from [dbo].[TPS_ControlStandardsIncluded] WITH(NOLOCK)

        where PMID = @pmid and IsActive = 1

​

        -- Compare Current and Included tables, populate Excluded

        INSERT INTO #TPS_ControlStandardsExcluded (CXIID, PMID, ControlStandards, CreatedDate, IsActive, ActionType, RequirementStatement, MatchingScore)

        SELECT 

            csc.CSCID,

            csc.PMID,

            csc.ControlStandards,

            csc.CreatedDate,

            csc.IsActive,

            csc.ActionType,

            csc.RequirementStatement,

            csc.MatchingScore

        FROM 

            [dbo].[TPS_ControlStandardsCurrent] csc WITH(NOLOCK)

        LEFT JOIN 

            [dbo].[TPS_ControlStandardsIncluded] csi WITH(NOLOCK)

        ON 

            csc.PMID = csi.PMID AND csc.ControlStandards = csi.ControlStandards

        WHERE 

            csc.PMID = @pmid AND csc.IsActive = 1 AND csi.CSIID IS NULL

​

        -- Process Excluded Controls

        Select 

            @ExcludedControls = STRING_AGG(Concat(ControlStandards, ': ', replace(replace(RequirementStatement, CHAR(149), ''), CHAR(10), ''), CHAR(13), CHAR(13)), '')

        from #TPS_ControlStandardsExcluded

        where PMID = @pmid

​

        IF @templateName = 'HITRUST'

        BEGIN

            set @MaturityRatings = ''

            Select @MaturityRatings = mrl.MaturityRatings

            from [dbo].[TPS_MaturityRatingsList] mrl WITH(NOLOCK)

            inner join [dbo].[TPS_MaturityRatingsMaster] mrm WITH(NOLOCK) on mrl.MRID = mrm.MRID

            where PMID = @pmid

            SET @MaturityRatings = IIF((@MaturityRatings <> '' or @MaturityRatings <> null), @MaturityRatings, 'SELECTION REQUIRED')

​

            Insert into #TPS_Report(Domain, BaselineUnqID, RequirementStatement, ResultMapping, PolicyDetails, MaturityRatings, IdentifiedGaps, Createdby, CreatedDate, IncludedControls, ExcludedControls, IsExcluded)

            Values(@Domain, @BaselineUnqID, @RequirementStatement, @ResultMapping, @PolicyDetails, @MaturityRatings, @IdentifiedGaps, @Createdby, @CreatedDate, @IncludedControls, @ExcludedControls, CASE WHEN @ExcludedControls <> '' THEN 1 ELSE 0 END)

        END

        ELSE

        BEGIN

            Insert into #TPS_Report(Domain, BaselineUnqID, RequirementStatement, ResultMapping, PolicyDetails, IdentifiedGaps, Createdby, CreatedDate, IncludedControls, ExcludedControls, IsExcluded)

            Values(@Domain, @BaselineUnqID, @RequirementStatement, @ResultMapping, @PolicyDetails, @IdentifiedGaps, @Createdby, @CreatedDate, @IncludedControls, @ExcludedControls, CASE WHEN @ExcludedControls <> '' THEN 1 ELSE 0 END)

        END

​

        FETCH NEXT FROM cursor_ReportData INTO @Domain, @BaselineUnqID, @RequirementStatement, @pmid, @IdentifiedGaps;

    END

​

    CLOSE cursor_ReportData;

    DEALLOCATE cursor_ReportData;

​

    SELECT * from #TPS_Report;

    DROP TABLE #TPS_Report;

    DROP TABLE #TPS_ControlStandardsExcluded;

END

​

Exec [dbo].[TPS_sp_MappingGetReport3] @uploadbatch_id = [f5b4f421-f4f5-4255-aca9-9b322fe28807];