﻿DECLARE intezmenytanevekCursor CURSOR LOCAL FOR
SELECT
	 ia.C_INTEZMENYID
	,ia.C_TANEVID
FROM T_INTEZMENYADATOK ia
	JOIN T_TANEV t ON t.ID = ia.C_TANEVID AND t.TOROLT = 'F'
	JOIN T_INTEZMENY i ON i.ID = ia.C_INTEZMENYID AND i.TOROLT = 'F'
WHERE ia.TOROLT = 'F' AND t.C_NEV = '2020/2021'

DECLARE @intezmenyId int, @tanevId int
DECLARE @optionListAlap nVARCHAR(MAX)
DECLARE @optionList nVARCHAR(MAX)
DROP TABLE IF EXISTS #tmpOptions
CREATE TABLE #tmpOptions (options nvarchar(MAX))

DROP TABLE IF EXISTS #tmpAlapOptions
CREATE TABLE #tmpAlapOptions (options nvarchar(MAX))

DROP TABLE IF EXISTS #SelectedOptions
CREATE TABLE #SelectedOptions (SelectedID INT ,IsAlapErtek bit)


OPEN intezmenytanevekCursor
FETCH NEXT FROM intezmenytanevekCursor INTO @intezmenyId, @tanevId
WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @optionListAlap = NULL
		SET @optionList = NULL
		
		DELETE FROM #tmpOptions
		TRUNCATE TABLE #tmpOptions
		
		DELETE FROM #tmpAlapOptions
		TRUNCATE TABLE #tmpAlapOptions
		
		DELETE FROM #SelectedOptions
		TRUNCATE TABLE #SelectedOptions

		INSERT INTO #SelectedOptions(SelectedID, IsAlapErtek)
		SELECT JSON_VALUE(value,'$.Value'), 0 
		FROM  OpenJson((SELECT [value] FROM OpenJson((SELECT C_ERTEK FROM T_RENDSZERBEALLITAS WHERE C_TANEVID = @tanevID AND C_BEALLITASTIPUS = 5441)) WHERE [key] = 'Options')) 
		WHERE JSON_VALUE(value,'$.Selected') = 'true'

		INSERT INTO #SelectedOptions(SelectedID, IsAlapErtek)
		SELECT JSON_VALUE(value,'$.Value'), 1
		FROM  OpenJson((SELECT [value] FROM OpenJson((SELECT C_ALAPERTEK FROM T_RENDSZERBEALLITAS WHERE C_TANEVID = @tanevID AND C_BEALLITASTIPUS = 5441)) WHERE [key] = 'Options')) 
	  WHERE JSON_VALUE(value,'$.Selected') = 'true'

		INSERT INTO #tmpOptions
			SELECT '{"Selected":'+IIF(so.SelectedID IS NULL ,'false','true')+',"Text":"' + ISNULL(d.C_NAME,'') + '","Value":"' + CAST(ISNULL(d.ID,'') as nvarchar(10)) + '"}' 
		  from T_DICTIONARYITEMBASE d
		  LEFT JOIN #SelectedOptions so ON so.SelectedID = d.ID AND IsAlapErtek = 0
		  where d.C_TYPE = 'MunkakorTipus' and d.C_INTEZMENYID = @intezmenyID and d.C_TANEVID = @tanevID and d.TOROLT = 'F'  order by d.C_VALUE
		  
		SELECT @optionList = COALESCE(@optionList + ', ', '') + options FROM #tmpOptions
		

		INSERT INTO #tmpAlapOptions
			SELECT '{"Selected":'+IIF(so.SelectedID IS NULL ,'false','true')+',"Text":"' + ISNULL(d.C_NAME,'') + '","Value":"' + CAST(ISNULL(ID,'') as nvarchar(10)) + '"}' 
		  from T_DICTIONARYITEMBASE d
		  LEFT JOIN #SelectedOptions so ON so.SelectedID = d.ID AND IsAlapErtek = 1
		  where d.C_TYPE = 'MunkakorTipus' and d.C_INTEZMENYID = @intezmenyID and d.C_TANEVID = @tanevID and d.TOROLT = 'F'  order by d.C_VALUE
		  
		SELECT @optionListAlap = COALESCE(@optionListAlap + ', ', '') + options FROM #tmpAlapOptions
		
		UPDATE T_RENDSZERBEALLITAS SET 
		   C_ALAPERTEK = '{"Id":"Alkalmazottak_lathatjak_a_diakok_adatait","Options":[' + ISNULL(@optionListAlap, '') + ']}'
		  ,C_ERTEK = '{"Id":"Alkalmazottak_lathatjak_a_diakok_adatait","Options":[' + ISNULL(@optionList, '') + ']}'
		  WHERE C_BEALLITASTIPUS = 5441 AND C_INTEZMENYID = @intezmenyID AND C_TANEVID = @tanevID

	  FETCH NEXT FROM intezmenytanevekCursor INTO @intezmenyId, @tanevId
	END

CLOSE intezmenytanevekCursor
DEALLOCATE intezmenytanevekCursor
