-- EXEC MIG_MASK_CONDITIONS_Sp
CREATE OR ALTER PROCEDURE MIG_MASK_CONDITIONS_Sp 
AS 
BEGIN
	TRUNCATE TABLE MIG_MASK_CONDITIONS
	SELECT
		ZZ_ID,
		MASK_ID,
		CLASS_NAME,
		andor,
		open_br,
		SUBSTRING(value,0,CHARINDEX(' ',value)) as keyn,
		RTRIM(SUBSTRING(value,CHARINDEX(' ',value)+1,CHARINDEX(' ',value,CHARINDEX(' ',value)+1)-CHARINDEX(' ',value))) as oper,
		REVERSE(SUBSTRING(LTRIM(REVERSE(value)),0,CHARINDEX(' ',LTRIM(REVERSE(value))))) as valu,
		clos_br
	INTO #TEMP1
	FROM 
		(SELECT 
			ZZ_ID,
			MASK_ID,
			CLASS_NAME,
			andor,
			open_br,
			REPLACE(value, '''', '') as value,
			clos_br
		FROM (
			SELECT 
				ZZ_ID,
				MASK_ID, 
				CLASS_NAME,
				CASE WHEN CHARINDEX(' ',B.value) <= 4 THEN 
					SUBSTRING( B.value,0,4) ELSE 
					'' END  andor,   
				CASE WHEN CHARINDEX('(',B.value) >= 1 THEN 
					1 ELSE 
					0 END AS open_br,
				CASE WHEN CHARINDEX(')',B.value) >= 1 THEN 
					1 ELSE 
					0 END AS clos_br,
				REPLACE(
					REPLACE(
						REPLACE(
							REPLACE(
								REPLACE(
									B.value, 
									'UPPER(', 
									''), 
								'TRG.', 
								''), 
							'(', 
							''), 
						')', 
						''), 
					'AND ', 
					'') AS value
			FROM MASK_RULES  A
				CROSS APPLY MIG_PLM_STRING_SPLIT_Fn(REPLACE(A.DB_QUERY,'AND','^AND'),'^') B
				WHERE CHARINDEX('OR ',B.value) <= 0
			UNION
		
			SELECT 
				ZZ_ID,
				MASK_ID,
				CLASS_NAME, 
				CASE WHEN CHARINDEX(' ',B.value) <= 4 THEN 
					SUBSTRING( B.value,0,4) ELSE 
					'' END  andor,   
				CASE WHEN CHARINDEX('(',B.value) >= 1 THEN 
					1 ELSE 
					0 END AS open_br,
				CASE WHEN CHARINDEX(')',B.value) >= 1 THEN 
					1 ELSE 
					0 END AS clos_br,
				REPLACE(
					REPLACE(
						REPLACE(
							REPLACE(
								REPLACE(
									B.value, 
									'UPPER(', 
									''), 
								'TRG.', 
								''), 
							'(', 
							''), 
						')', 
						''), 
					'OR ', 
					'') AS value
			FROM MASK_RULES  A
				CROSS APPLY MIG_PLM_STRING_SPLIT_Fn(REPLACE(A.DB_QUERY,'OR ','^OR '),'^') B 
				WHERE CHARINDEX('AND ',B.value) <= 0
		) T
	)T1


	INSERT INTO [dbo].[MIG_MASK_CONDITIONS]
           ([mask]
           ,[cnum]
           ,[anor]
		   ,[str_anor]
           ,[open_br]
           ,[str_open_br]
           ,[keyn]
           ,[oper]
           ,[str_oper]
           ,[valu]
           ,[clos_br]
           ,[str_clos_br]
		   ,[updt]) 
	SELECT 
		A.MASK_ID AS mask,
		ROW_NUMBER() OVER(PARTITION BY A.MASK_ID ORDER BY A.ZZ_ID) *10 AS cnum,
		CASE WHEN A.andor = '' THEN 10
			WHEN A.andor = 'AND' THEN 10 
			ELSE 20 END AS anor,
		CASE WHEN A.andor = '' THEN 'AND'
			ELSE A.andor END AS str_anor,
		CASE WHEN A.open_br = 1 THEN 1
			ELSE 2 END AS open_br,
		CASE WHEN A.open_br = 1 THEN '('
			ELSE '' END AS str_open_br,
		M.TAR_FIELD_NAME AS keyn,
		CASE WHEN A.oper = '=' THEN 20
			WHEN A.oper = '<>' THEN 30
			WHEN A.oper = '>' THEN 50
			WHEN A.oper = '>=' THEN 60
			WHEN A.oper = '<' THEN 70
			WHEN A.oper = '<=' THEN 80
			ELSE 140 END AS oper,
		oper AS str_oper,
		A.valu,
		CASE WHEN A.clos_br = 1 THEN 1
			ELSE 2 END AS clos_br,
		CASE WHEN A.clos_br = 1 THEN ')'
			ELSE '' END AS str_clos_br,
		GETDATE()
	FROM	#TEMP1 A
	JOIN	MIG_PROV_TABLE_FIELD_MAPPING M
		ON	A.CLASS_NAME = M.SRC_BASE_TBL_NAME
		AND	A.keyn = M.SRC_BASE_FIELD_NAME
		AND	M.TAR_TABLE_NAME <> 'pdpdm510'
		AND M.TAR_FIELD_NAME NOT IN ('pdpdm500.bara', 'pdpdm500.bvrn', 'pdpdm500.fnm2')
		AND M.TYPE = 'Custom'
	GROUP BY A.ZZ_ID,
		A.MASK_ID,
		A.andor,
		A.open_br,
		M.TAR_FIELD_NAME,
		A.oper,
		A.valu,
		A.clos_br
	
	DROP TABLE #TEMP1

END


