--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)