68 lines
2.9 KiB
SQL
68 lines
2.9 KiB
SQL
--Hiányzó Rendszerbeállítás update
|
|
UPDATE rbKov SET
|
|
C_ERTEK = rb.C_ERTEK
|
|
,LASTCHANGED = GETDATE()
|
|
,MODIFIER = 0
|
|
FROM T_RENDSZERBEALLITAS rb
|
|
INNER JOIN T_TANEV tn ON tn.ID=rb.C_TANEVID AND tn.C_NEV = '2019/2020' AND tn.C_AKTIV ='T'
|
|
INNER JOIN T_TANEV tnKov ON tnKov.C_KOVETKEZO = 'T' AND tnKov.C_NEV = '2020/2021' AND tnKov.C_INTEZMENYID = tn.C_INTEZMENYID
|
|
INNER JOIN T_RENDSZERBEALLITAS rbKov ON rbKov.ELOZOTANEVIREKORDID = rb.ID AND rbKov.C_TANEVID = tnKov.ID
|
|
CROSS APPLY OPENJSON(rb.C_ERTEK,'$') SingleValueIsDate
|
|
OUTER APPLY OPENJSON(rb.C_ERTEK,'$.Options[0]') ArrayValueContainsDate
|
|
WHERE
|
|
(
|
|
(ISDATE(CAST(SingleValueIsDate.value as nvarchar(1000))) = 1 AND ISNUMERIC(SingleValueIsDate.value) = 0)
|
|
OR
|
|
(ISDATE(CAST(ArrayValueContainsDate.value as nvarchar(1000)))= 1 AND ISNUMERIC(ArrayValueContainsDate.value) = 0)
|
|
)
|
|
--Hiányzó Tantárgy nyelv insert/update
|
|
UPDATE tgynyKov SET
|
|
ELOZOTANEVIREKORDID = tgyny.ID
|
|
,MODIFIER = 0
|
|
,LASTCHANGED = GETDATE()
|
|
FROM T_TANTARGYNYELV tgyny
|
|
INNER JOIN T_TANTARGY tgy ON tgy.ID = tgyny.C_TANTARGYID AND tgy.TOROLT='F'
|
|
INNER JOIN T_TANEV tn ON tn.ID=tgy.C_TANEVID AND tn.C_AKTIV = 'T' AND tn.C_NEV = '2019/2020' AND tn.TOROLT='F'
|
|
INNER JOIN T_DICTIONARYITEMBASE dib ON dib.ID = tgyny.C_NYELVID AND dib.C_TANEVID = tn.ID
|
|
|
|
INNER JOIN T_TANTARGY tgyKov ON tgyKov.ELOZOTANEVIREKORDID = tgy.ID AND tgyKov.TOROLT='F'
|
|
INNER JOIN T_TANEV tnKov ON tnKov.ID=tgyKov.C_TANEVID AND tnKov.C_KOVETKEZO = 'T' AND tnKov.C_NEV = '2020/2021' AND tnKov.TOROLT='F'
|
|
INNER JOIN T_TANTARGYNYELV tgynyKov ON tgynyKov.C_TANTARGYID = tgyKov.ID AND tgynyKov.TOROLT='F' AND tgynyKov.ELOZOTANEVIREKORDID IS NULL
|
|
INNER JOIN T_DICTIONARYITEMBASE dibKov ON dibKov.ID = tgynyKov.C_NYELVID AND dibKov.C_TANEVID = tnKov.ID AND dibKov.ELOZOTANEVIREKORDID = dib.ID
|
|
WHERE tgyny.TOROLT='F'
|
|
|
|
INSERT INTO T_TANTARGYNYELV (
|
|
C_NEV
|
|
,C_NYELVID
|
|
,C_TANTARGYID
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,TOROLT
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
,ELOZOTANEVIREKORDID
|
|
) SELECT
|
|
tgyNy.C_NEV
|
|
,dibKov.ID
|
|
,tgyKov.ID
|
|
,tgy.C_INTEZMENYID
|
|
,tgyKov.C_TANEVID
|
|
,'F'
|
|
,0
|
|
,GETDATE()
|
|
,GETDATE()
|
|
,0
|
|
,0
|
|
,tgyny.ID
|
|
FROM T_TANTARGYNYELV tgyny
|
|
INNER JOIN T_TANTARGY tgy ON tgy.ID = tgyny.C_TANTARGYID AND tgy.TOROLT='F'
|
|
INNER JOIN T_TANEV tn ON tn.ID=tgy.C_TANEVID AND tn.C_AKTIV = 'T' AND tn.C_NEV = '2019/2020' AND tn.TOROLT='F'
|
|
INNER JOIN T_DICTIONARYITEMBASE dib ON dib.ID = tgyny.C_NYELVID AND dib.C_TANEVID = tn.ID
|
|
|
|
INNER JOIN T_TANEV tnKov ON tnKov.C_INTEZMENYID = tn.C_INTEZMENYID AND tnKov.C_KOVETKEZO = 'T' AND tnKov.C_NEV = '2020/2021' AND tnKov.TOROLT='F'
|
|
INNER JOIN T_TANTARGY tgyKov ON tgyKov.ELOZOTANEVIREKORDID = tgy.ID AND tgyKov.TOROLT='F'
|
|
INNER JOIN T_DICTIONARYITEMBASE dibKov ON dibKov.C_TANEVID = tnKov.ID AND dibKov.ELOZOTANEVIREKORDID = dib.ID
|
|
WHERE tgyny.TOROLT='F' AND NOT EXISTS (SELECT 1 FROM T_TANTARGYNYELV WHERE ELOZOTANEVIREKORDID = tgyny.ID AND C_TANEVID = tnKov.ID) |