USE [TPS_Dev_DB]

GO

/****** Object: StoredProcedure [dbo].[TPS_sp_GetReport] Script Date: 9/23/2024 11:52:27 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[TPS_sp_GetReport]

@uploadbatch_id varchar(100)

as

​

BEGIN

Declare @uploadbatchid varchar(40)

Declare @templateName nvarchar(255)=''

--Select top(1) @uploadbatchid=UploadBatchID from [dbo].[TPS_MasterUploadDetails] where UploadTemplateType not like 'eGrc_%' order by CreatedDate desc

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),@IncludedStandards nvarchar(max),@ExcludedStandards nvarchar(max)

--set @uploadbatchid='93c3ddfb-c4f3-49ac-bcbd-3afad3a38816'

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),IncludedStandards nvarchar(max),ExcludedStandards nvarchar(max))

IF @templateName<>'HITRUST'

BEGIN

ALTER TABLE #TPS_Report DROP COLUMN MaturityRatings;

END

​

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 @IncludedStandards=''

set @ExcludedStandards=''

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)),@IncludedStandards=STRING_AGG(ControlStandards,CHAR(13)),@ExcludedStandards=STRING_AGG(ControlStandards,CHAR(13)) from [dbo].[TPS_ControlStandardsCurrent] WITH(NOLOCK) where PMID=@pmid and IsActive=1

Select @IncludedStandards=STRING_AGG(ControlStandards,CHAR(13)) from [dbo].[TPS_ControlStandardsIncluded] WITH(NOLOCK) where PMID=@pmid and IsActive=1

Select @ExcludedStandards=STRING_AGG(ControlStandards,CHAR(13)) from [dbo].[TPS_ControlStandardsExcluded] WITH(NOLOCK) where PMID=@pmid and IsActive=1

​

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,IncludedStandards,ExcludedStandards) Values(@Domain,@BaselineUnqID,@RequirementStatement,@ResultMapping,@PolicyDetails,@MaturityRatings,@IdentifiedGaps,@Createdby,@CreatedDate,@IncludedStandards,@ExcludedStandards)

END

ELSE

BEGIN

Insert into #TPS_Report(Domain,BaselineUnqID,RequirementStatement,ResultMapping,PolicyDetails,IdentifiedGaps,Createdby,CreatedDate,IncludedStandards,ExcludedStandards) Values(@Domain,@BaselineUnqID,@RequirementStatement,@ResultMapping,@PolicyDetails,@IdentifiedGaps,@Createdby,@CreatedDate,@IncludedStandards,@ExcludedStandards)

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;

END

​