76 lines
3.2 KiB
Transact-SQL
76 lines
3.2 KiB
Transact-SQL
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
|