USE eaglekb
GO

--Create on_get_items funstion for report profile
IF EXISTS (	SELECT name 
			FROM eaglekb..sysobjects 
			WHERE name = 'on_get_items_ReportProfile'
		   )
	DROP FUNCTION on_get_items_ReportProfile
GO

CREATE FUNCTION [dbo].[on_get_items_ReportProfile]()
RETURNS 
	@res TABLE (type name_type NOT NULL, 
				id id_type NOT NULL, 
				name name_type NOT NULL
				)
	BEGIN
	--get type report profile   
	INSERT @res
    	SELECT	dbo.md_type('Report Profile'),
				CONVERT(VARCHAR(MAX),instance),
				profilename 
		FROM 
				pace_master..report_profiles 
	RETURN
END
GO

--Create on_get_contains funstions for report profile
IF EXISTS (	SELECT name 
			FROM eaglekb..sysobjects 
			WHERE name = 'on_get_contains_ReportProfile'
		  )
	DROP FUNCTION on_get_contains_ReportProfile
GO
CREATE FUNCTION [dbo].[on_get_contains_ReportProfile]()
RETURNS 
	@res TABLE (type name_type NOT NULL,
				id id_type NOT NULL,
				child_type name_type NOT NULL, 
				child_id id_type NOT NULL
				)
BEGIN 
	--Select all the report profile, get value for each tag and insert into table return function
	--DATE_FIELD!	
	--Look for the tag Date_field!
	INSERT @res
	SELECT	DISTINCT 
			'Report Profile',
			--fa.field_attribute_id,
			rp.instance,
			'Field Attribute',
			fa.field_attribute_id 
	FROM 
			pace_master..report_profiles rp
			CROSS APPLY
			rules.dbo.field_attributes fa
			CROSS APPLY
			pace_master.dbo.to_varchar(rp.user_data,',') all_tags
			CROSS APPLY
			pace_master.dbo.to_varchar(all_tags.val,'~') fd_tags
	WHERE 
			fa.field_attribute_id = pace_master.dbo.get_split_value(fd_tags.val,'DATE_FIELD!')
			AND fa.field_attribute_id != -1 
			AND	all_tags.val IS NOT NULL 
			AND	fd_tags.val  IS NOT NULL
			AND	all_tags.val LIKE  '%DATE_FIELD!%' 
			AND	fd_tags.val  LIKE  '%DATE_FIELD!%'

	--SCF_CASH_FLOW_FIELD!	
    --Look for the tag SCF_CASH_FLOW_FIELD! union with the above query
	UNION
	SELECT DISTINCT 
			'Report Profile',
			rp.instance,
			'Field Attribute',
			--convert(varchar(255),'SCF_CASH_FLOW_FIELD!'),
			fa.field_attribute_id 
	FROM 
			pace_master..report_profiles rp
			CROSS APPLY
			rules.dbo.field_attributes fa
			CROSS APPLY
			pace_master.dbo.to_varchar(rp.user_data,',') all_tags
			CROSS APPLY
			pace_master.dbo.to_varchar(all_tags.val,'~') fd_tags
	WHERE 
			fa.field_attribute_id = pace_master.dbo.get_split_value(fd_tags.val,'SCF_CASH_FLOW_FIELD!')
			AND fa.field_attribute_id != -1 
			AND	all_tags.val IS NOT NULL 
			AND	fd_tags.val  IS NOT NULL
			AND	all_tags.val LIKE  '%SCF_CASH_FLOW_FIELD!%' 
			AND	fd_tags.val  LIKE  '%SCF_CASH_FLOW_FIELD!%'

--SCF_MKT_VAL_FIELD!
	--Look for the tag SCF_MKT_VAL_FIELD! union with the above query
	UNION
	SELECT DISTINCT 
			'Report Profile',
			rp.instance,
			'Field Attribute',
			--convert(varchar(255),'SCF_MKT_VAL_FIELD!'),
			fa.field_attribute_id 
	FROM 
			pace_master..report_profiles rp
			CROSS APPLY
			rules.dbo.field_attributes fa
			CROSS APPLY
			pace_master.dbo.to_varchar(rp.user_data,',') all_tags
			CROSS APPLY
			pace_master.dbo.to_varchar(all_tags.val,'~') fd_tags
   	WHERE
			fa.field_attribute_id = pace_master.dbo.get_split_value(fd_tags.val,'SCF_MKT_VAL_FIELD!')
			AND fa.field_attribute_id != -1 
			AND	all_tags.val IS NOT NULL 
			AND	fd_tags.val  IS NOT NULL
			AND	all_tags.val LIKE  '%SCF_MKT_VAL_FIELD!%' 
			AND	fd_tags.val  LIKE  '%SCF_MKT_VAL_FIELD!%'

   
--RECON_FIELDS!
	--Look for the tag RECON_FIELDS! union with the above query
	UNION
	SELECT DISTINCT 
			'Report Profile',
			rp.instance,
			'Field Attribute',
			--convert(varchar(255),'RECON_FIELDS!'),
			fa.field_attribute_id 
	FROM 
			pace_master..report_profiles rp
			CROSS APPLY
			rules.dbo.field_attributes fa
			CROSS APPLY
			pace_master.dbo.to_varchar(rp.user_data,',') all_tags
			CROSS APPLY
			pace_master.dbo.to_varchar(all_tags.val,'~') fd_tags
	WHERE 
			fa.field_attribute_id = pace_master.dbo.get_split_value(fd_tags.val,'RECON_FIELDS!')
			AND fa.field_attribute_id != -1 
			AND	all_tags.val IS NOT NULL 
			AND	fd_tags.val	 IS NOT NULL
			AND	all_tags.val LIKE  '%RECON_FIELDS!%' 
			AND	fd_tags.val  LIKE  '%RECON_FIELDS!%'

--RECON_IDENTIFIER_FIELDS!	
	--Look for the tag RECON_IDENTIFIER_FIELDS!! union with the above query
	UNION
	SELECT DISTINCT 
			'Report Profile',
			rp.instance,
			'Field Attribute',
			--convert(varchar(255),'RECON_IDENTIFIER_FIELDS!'),
			fa.field_attribute_id 
	FROM 
			pace_master..report_profiles rp
			CROSS APPLY
			rules.dbo.field_attributes fa
			CROSS APPLY
			pace_master.dbo.to_varchar(rp.user_data,',') all_tags
			CROSS APPLY
			pace_master.dbo.to_varchar(all_tags.val,'~') fd_tags
	WHERE 
			fa.field_attribute_id = pace_master.dbo.get_split_value(fd_tags.val,'RECON_IDENTIFIER_FIELDS!')
			AND fa.field_attribute_id != -1 
			AND	all_tags.val IS NOT NULL 
			AND	fd_tags.val  IS NOT NULL
			AND	all_tags.val LIKE  '%RECON_IDENTIFIER_FIELDS!%' 
			AND	fd_tags.val  LIKE  '%RECON_IDENTIFIER_FIELDS!%'

--SECHISTDATE!
	--Look for the tag SECHISTDATE!! union with the above query
	UNION
	SELECT DISTINCT 
			'Report Profile',
			rp.instance,
			'Field Attribute',
			--convert(varchar(255),'SECHISTDATE!'),
			fa.field_attribute_id 
	FROM 
			pace_master..report_profiles rp
			CROSS APPLY
			rules.dbo.field_attributes fa
			CROSS APPLY
			pace_master.dbo.to_varchar(rp.user_data,',') all_tags
			CROSS APPLY
			pace_master.dbo.to_varchar(all_tags.val,'~') fd_tags
	WHERE 
			fa.field_attribute_id = pace_master.dbo.get_split_value(fd_tags.val,'SECHISTDATE!')
			AND fa.field_attribute_id != -1 
			AND	all_tags.val IS NOT NULL 
			AND	fd_tags.val  IS NOT NULL
			AND	all_tags.val LIKE  '%SECHISTDATE!%' 
			AND	fd_tags.val  LIKE  '%SECHISTDATE!%'

--FXRATESDATE_CASH!
	--Look for the tag FXRATESDATE_CASH! union with the above query
	UNION
	SELECT DISTINCT 
			'Report Profile',
			rp.instance,
			'Field Attribute',
			--convert(varchar(255),'FXRATESDATE_CASH!'),
			fa.field_attribute_id 
	FROM 
			pace_master..report_profiles rp
			CROSS APPLY
			rules.dbo.field_attributes fa
			CROSS APPLY
			pace_master.dbo.to_varchar(rp.user_data,',') all_tags
			CROSS APPLY
			pace_master.dbo.to_varchar(all_tags.val,'~') fd_tags
	WHERE 
			fa.field_attribute_id = pace_master.dbo.get_split_value(fd_tags.val,'FXRATESDATE_CASH!')
			AND fa.field_attribute_id != -1 
			AND	all_tags.val IS NOT NULL 
			AND	fd_tags.val  IS NOT NULL
			AND	all_tags.val LIKE  '%FXRATESDATE_CASH!%' 
			AND	fd_tags.val  LIKE  '%FXRATESDATE_CASH!%'

--FXRATESDATE_TRADE!
	--Look for the tag FXRATESDATE_TRADE! union with the above query
	UNION
	SELECT DISTINCT 
			'Report Profile',
			rf.instance,
			'Field Attribute',
			--convert(varchar(255),'FXRATESDATE_TRADE!'),
			fa.field_attribute_id 
	FROM 
			pace_master..report_profiles rf
			CROSS APPLY
			rules.dbo.field_attributes fa
			CROSS APPLY
			pace_master.dbo.to_varchar(rf.user_data,',') all_tags
			CROSS APPLY
			pace_master.dbo.to_varchar(all_tags.val,'~') fd_tags
	WHERE 
			fa.field_attribute_id = pace_master.dbo.get_split_value(fd_tags.val,'FXRATESDATE_TRADE!')
			AND fa.field_attribute_id != -1 
			AND	all_tags.val IS NOT NULL 
			AND	fd_tags.val  IS NOT NULL
			AND	all_tags.val LIKE '%FXRATESDATE_TRADE!%' 
			AND	fd_tags.val  LIKE  '%FXRATESDATE_TRADE!%'

--SRCRULE!
	--Look for the tag SRCRULE! union with the above query
	UNION
	SELECT DISTINCT
			'Report Profile',
			rp.instance,
			'SRCRULE',
			--convert(varchar(255),'SRCRULE!'),
			CONVERT(VARCHAR(MAX), srd.source_rule_id)
	FROM 
			pace_master..report_profiles rp
			CROSS APPLY
			pace_master.dbo.to_varchar(rp.user_data,',') all_tags
			CROSS APPLY
			pace_master.dbo.to_varchar(all_tags.val,'~') fd_tags,
			rules..source_rule srd         
	WHERE 
			srd.source_rule_id = pace_master.dbo.get_split_value(fd_tags.val,'SRCRULE!')
			AND	all_tags.val IS NOT NULL 
			AND	fd_tags.val  IS NOT NULL
			AND	all_tags.val LIKE  '%SRCRULE!%' 
			AND	fd_tags.val  LIKE  '%SRCRULE!%'

--FIELDRULE
	--Look for the tag FIELDRULE! union with the above query
	UNION
	SELECT DISTINCT
			'Report Profile',
			rp.instance,
			'Field Rule',
			--convert(varchar(255),'Field Rule'),
			CONVERT(VARCHAR(MAX), sr.stat_rule_id)
	FROM 
			pace_master..report_profiles rp
			CROSS APPLY
			pace_master.dbo.to_varchar(rf.user_data,',') all_tags
			CROSS APPLY
			pace_master.dbo.to_varchar(all_tags.val,'~') fd_tags,
			rules.dbo.statistic_rule sr
	WHERE 
			sr.stat_rule_id = pace_master.dbo.get_split_value(fd_tags.val,'FIELDRULE!')
			AND	all_tags.val IS NOT NULL 
			AND	fd_tags.val  IS NOT NULL
			AND	all_tags.val LIKE  '%FIELDRULE!%' 
			AND	fd_tags.val  LIKE  '%FIELDRULE!%'

--DATE_RULE!!
	--Look for the tag DATE_RULE! union with the above query
	UNION
	SELECT DISTINCT
			'Report Profile',
			rp.instance,
			'Date Rule',
			CONVERT(VARCHAR(MAX), dr.id)
	FROM 
			pace_master..report_profiles rp
			CROSS APPLY
			pace_master.dbo.to_varchar(rp.user_data,',') all_tags
			CROSS APPLY
			pace_master.dbo.to_varchar(all_tags.val,'~') fd_tags,
			rules.dbo.dateranges dr
	WHERE 
			dr.id = pace_master.dbo.get_split_value(fd_tags.val,'DATE_RULE!')
			AND	all_tags.val IS NOT NULL 
			AND	fd_tags.val  IS NOT NULL
			AND	all_tags.val LIKE  '%DATE_RULE!%' 
			AND	fd_tags.val  LIKE  '%DATE_RULE!%'

--C_D_Ranges!
	--Look for the tag C_D_Ranges! union with the above query
	UNION
	SELECT DISTINCT
			'Report Profile',
			rp.instance,
			'C_D_Ranges Date Rule',
			--convert(varchar(255),'C_D_Ranges Date Rule'),
			CONVERT(VARCHAR(MAX), dr.id)
	FROM 
			pace_master..report_profiles rp
			CROSS APPLY
			pace_master..to_varchar(rp.user_data,',') all_tags
			CROSS APPLY
			pace_master..to_varchar(all_tags.val,'!') CD_TAG
			CROSS APPLY
			pace_master..to_varchar(CD_TAG.val,'~') c_d_range,
			rules.dbo.dateranges dr
	WHERE 
			dr.id = c_d_range.val
			AND all_tags.val LIKE '%C_D_RANGES!%' 
			AND cd_tag.val NOT LIKE '%C_D_Ranges%'

--FLDID!
	--Look for the tag FLDID! union with the above query
	UNION
	SELECT DISTINCT
			'Report Profile',
			rp.instance,
			'FLDID',
			--convert(varchar(255),'FLDID'),
			CONVERT(VARCHAR(MAX), fd.field_attribute_id)
	FROM 
			pace_master..report_profiles rp
			CROSS APPLY
			pace_master..to_varchar(rp.user_data,',') all_tags
			CROSS APPLY
			pace_master..to_varchar(all_tags.val,'!') fld_TAG
			CROSS APPLY
			pace_master..to_varchar(fld_TAG.val,'~') fldid,
			rules.dbo.field_attributes fd
	WHERE 
			fd.field_attribute_id = fldid.val
			AND all_tags.val LIKE '%FLDID!%' 
			AND fld_TAG.val NOT LIKE '%FLDID%'


--FD
	--Look for the tag FD and union with the above query
	UNION
	SELECT DISTINCT
			'Report_Profiles',
			rp.instance,
			'Field Attributes',	
			fa.field_attribute_id
	FROM
			pace_master..report_profiles rp
			CROSS APPLY
			--rules.dbo.field_attributes fa
			--	 join
			--rules.dbo.field_process_definition fpd
			--	on fpd.field_attribute_id = fa.field_attribute_id
			--	cross apply
			rules.dbo.field_attributes fa
			CROSS APPLY
			pace_master.dbo.to_varchar(rp.user_data,'`') all_tags
			CROSS APPLY
			pace_master.dbo.to_varchar(all_tags.val,'[') fd_tags
			CROSS APPLY
			pace_master.dbo.to_varchar(all_tags.val,'~') n_tags
			JOIN
			rules.dbo.field_attributes dfa
			ON fa.field_attribute_id = CONVERT(INT,SUBSTRING(fd_tags.val,3,LEN(fd_tags.val)-2))
	WHERE
			rp.user_data IS NOT NULL 
			AND all_tags.val IS NOT NULL 
			AND fd_tags.val IS NOT NULL 
			--rf.instance = @L_RptInst and
			AND LEFT(all_tags.val,2)='FD' 
			AND LEFT(fd_tags.val,2)='FD'
--RID
	--Look for the tag RID
	UNION
	SELECT DISTINCT 
			'Report Profile',
			rp.instance,
			'Report Rule',
			hierarchy_id --dbo.md_type('report_profile'),hierarchy_description 
		FROM 
			pace_master..report_profiles rp
			CROSS APPLY
			rules.dbo.master_hierarchy mh--8832
			CROSS APPLY pace_master..to_varchar(rp.user_data,',') all_tags
			--cross apply pace_master..to_varchar(all_tags.val,'!') RID_TAGS
		WHERE 
			LEFT (all_tags.val,4) = 'RID!'
			AND all_tags.val IS NOT NULL
			AND all_tags.val NOT LIKE '%~%'
			AND	mh.hierarchy_id = SUBSTRING(all_tags.val,5,LEN(all_tags.val)-1)
			--and rf.instance = @L_RptInst

	RETURN
END
GO

/*
-----------------------------------------------------------
run the below query to execute and test report profile bot
---------------------------------------------------------
select * from eaglekb..on_get_contains_ReportProfile(3,'ANALSRC!-1,ASOFDT!12-15-2006,AUTO_BMARK!0,CLIENT_TYPE!CLIENT,DATE_FIELD!103,DATE_RULE!1,DISCARD_OLAP_DATA!-1,ENUCOMP!0,ERX_TEMPLATE!0,EXPLODE2LEVEL!1,FILE_LOC!-1,FILE_SERVER!-1,HISTENT!0,MOD!GEN,NOSEND!-1,NO_CREATE_XML_FILE!-1,OUTCHAN!MBOX,OUTFMT!EGL2,OUTPUTFMTINFO!  ,PRISRC!-1,PUB_GROUPS!0,PUB_RES_PUB_PROF!-1,P_ENTIY_ID!PORTFOLO,P_LIST_TYP!0,P_RPT_RULE!DAT  PFP TEST,REGSRC!-1,REQ!Reserved,RID!32701,RUN_PERIOD!101,SCH_INST!0,SCH_PRINT!0,SECURITY_CRITERIA!#`FP1014[#`FD311[IN[#`STa,b,c,defg[','DATE_FIELD!')


select * from pace_master..to_varchar('ANALSRC!-1,ASOFDT!12-15-2006,AUTO_BMARK!0,CLIENT_TYPE!CLIENT,DATE_FIELD!103,DATE_RULE!1,DISCARD_OLAP_DATA!-1,ENUCOMP!0,ERX_TEMPLATE!0,EXPLODE2LEVEL!1,FILE_LOC!-1,FILE_SERVER!-1,HISTENT!0,MOD!GEN,NOSEND!-1,NO_CREATE_XML_FILE!-1,OUTCHAN!MBOX,OUTFMT!EGL2,OUTPUTFMTINFO!  ,PRISRC!-1,PUB_GROUPS!0,PUB_RES_PUB_PROF!-1,P_ENTIY_ID!PORTFOLO,P_LIST_TYP!0,P_RPT_RULE!DAT  PFP TEST,REGSRC!-1,REQ!Reserved,RID!32701,RUN_PERIOD!101,SCH_INST!0,SCH_PRINT!0,SECURITY_CRITERIA!#`FP1014[#`FD311[IN[#`STa,b,c,defg[',',')
--DATE_FIELD!103
--RID!32701
--FD311

select * from eaglekb..on_get_contains_ReportProfile('DATE_FIELD!') where id = 3


--2nd testing
select * from eaglekb..on_get_contains_ReportProfile() where id = 66

--output result
Report Profile	66	DATE_FIELD!		103
Report Profile	66	Report Rule		54
Report Profile	66	SCF_CASH_FLOW_FIELD!	4216
Report Profile	66	SCF_MKT_VAL_FIELD!	4374

--query the blob table and compare the above output. It was same
select * from pace_master..component_usage_blob where used_component_id = 66
*/