﻿--------------------------------------------------------------------------------
--- A T_TANITASIORA hiányos, de a releváns T_TANITASIORADIGTAMESZKOZ kitöltve
--------------------------------------------------------------------------------
UPDATE t
  SET 
    t.C_DIGESZKOZTIPUSID = ISNULL(C_DIGESZKOZTIPUSID, 8587)
    ,C_DIGPLATFORMTIPUSID = ISNULL(C_DIGPLATFORMTIPUSID, 8573)
  FROM T_TANITASIORA t 
  INNER JOIN T_TANITASIORADIGTAMESZKOZ todte ON todte.C_TANITASIORAID = t.ID AND todte.TOROLT = 'F'
  INNER JOIN T_FELHASZNALO f ON f.ID = t.C_TANARID AND f.TOROLT = 'F'
  WHERE t.TOROLT = 'F' 
    AND t.C_MEGTARTOTT = 'T' 
    AND (C_DIGESZKOZTIPUSID IS NULL
      or C_DIGPLATFORMTIPUSID IS NULL)

--------------------------------------------------------------------------------
--- A T_TANITASIORA invalid adatot tartalmaz
--------------------------------------------------------------------------------
UPDATE todte
  SET 
    todte.TOROLT = 'T' 
  FROM T_TANITASIORADIGTAMESZKOZ todte  
  WHERE todte.TOROLT = 'F' 
    AND todte.C_DIGTAMESZKOZTIPUSID = 0 

--------------------------------------------------------------------------------
--- A T_TANITASIORA kitöltve, de a releváns T_TANITASIORADIGTAMESZKOZ hiányos
--------------------------------------------------------------------------------
;WITH cte AS (
  SELECT 
    t.id AS tanitasiOraId
    ,t.C_INTEZMENYID AS intezmenyId
    ,t.C_TANEVID AS tanevId
  FROM T_TANITASIORA t 
  LEFT JOIN T_TANITASIORADIGTAMESZKOZ todte ON todte.C_TANITASIORAID = t.ID
  INNER JOIN T_FELHASZNALO f ON f.ID = t.C_TANARID AND F.TOROLT = 'F' 
  WHERE t.TOROLT = 'F' 
    AND t.C_MEGTARTOTT = 'T' 
    AND C_DIGESZKOZTIPUSID IS NOT NULL
    AND todte.id IS NULL
    AND todte.TOROLT = 'F'
  )
  INSERT INTO T_TANITASIORADIGTAMESZKOZ (
    C_DIGTAMESZKOZTIPUSID
    ,C_TANITASIORAID
    ,C_INTEZMENYID
    ,C_TANEVID
    ,TOROLT
    ,SERIAL
    ,LASTCHANGED
    ,CREATED
    ,MODIFIER
    ,CREATOR
    ,ELOZOTANEVIREKORDID
  )
  SELECT
    8600
    ,tanitasiOraId
    ,intezmenyId
    ,tanevId
    ,'F'
    ,0
    ,GETDATE()
    ,GETDATE()
    ,0
    ,0
    ,NULL
  FROM cte
