﻿IF DB_NAME() NOT LIKE 'KRETA_KLIK%' BEGIN

  INSERT INTO T_VEGZETTSEG (
     C_EGYEB
    ,C_VEGZETTSEGTIPUSID
    ,C_TANARID
    ,C_INTEZMENYID
    ,C_TANEVID
    ,TOROLT
    ,SERIAL
    ,LASTCHANGED
    ,CREATED
    ,MODIFIER
    ,CREATOR
    ,ELOZOTANEVIREKORDID
    ,NNID
  ) SELECT 
     v.C_EGYEB AS C_EGYEB                -- C_EGYEB - nvarchar(255)
    ,v.C_VEGZETTSEGTIPUSID AS C_VEGZETTSEGTIPUSID    -- C_VEGZETTSEGTIPUSID - int
    ,fh.ID   AS C_TANARID               -- C_TANARID - int
    ,v.C_INTEZMENYID   AS C_INTEZMENYID           -- C_INTEZMENYID - int
    ,teNext.ID   AS C_TANEVID               -- C_TANEVID - int
    ,'F'  AS TOROLT                  -- TOROLT - char(1)
    ,0   AS SERIAL                  -- SERIAL - int
    ,GETDATE() AS LASTCHANGED       -- LASTCHANGED - datetime
    ,GETDATE() AS CREATED           -- CREATED - datetime
    ,NULL AS MODIFIER               -- MODIFIER - int
    ,NULL AS CREATOR                -- CREATOR - int
    ,v.ID AS ELOZOTANEVIREKORDID    -- ELOZOTANEVIREKORDID - int
    ,NULL AS NNID                   -- NNID - int
  -- select *
  FROM T_VEGZETTSEG v
    INNER JOIN T_FELHASZNALO fh ON fh.ELOZOTANEVIREKORDID = v.C_TANARID
    INNER JOIN T_TANEV te ON te.ID = v.C_TANEVID AND te.C_NEV = '2017/2018'  AND te.TOROLT = 'F'
    INNER JOIN T_TANEV teNext ON te.C_INTEZMENYID = teNext.C_INTEZMENYID AND teNext.C_NEV = '2018/2019'  AND te.TOROLT = 'F'
  WHERE v.TOROLT = 'F'
    AND NOT EXISTS (SELECT 1 FROM T_VEGZETTSEG WHERE ELOZOTANEVIREKORDID = v.ID)


  INSERT INTO T_KEPESITES (
     C_KEPESITESTIPUSID
    ,C_INTEZMENYID
    ,C_TANEVID
    ,TOROLT
    ,SERIAL
    ,LASTCHANGED
    ,CREATED
    ,MODIFIER
    ,CREATOR
    ,ELOZOTANEVIREKORDID
    ,NNID
  ) SELECT 
     k.C_KEPESITESTIPUSID AS C_KEPESITESTIPUSID     -- C_KEPESITESTIPUSID - int
    ,k.C_INTEZMENYID   AS C_INTEZMENYID           -- C_INTEZMENYID - int
    ,teNext.ID   AS C_TANEVID               -- C_TANEVID - int
    ,'F'  AS TOROLT                  -- TOROLT - char(1)
    ,0   AS SERIAL                  -- SERIAL - int
    ,GETDATE() AS LASTCHANGED       -- LASTCHANGED - datetime
    ,GETDATE() AS CREATED           -- CREATED - datetime
    ,NULL AS MODIFIER               -- MODIFIER - int
    ,NULL AS CREATOR                -- CREATOR - int
    ,k.ID AS ELOZOTANEVIREKORDID    -- ELOZOTANEVIREKORDID - int
    ,NULL AS NNID                   -- NNID - int
  FROM T_KEPESITES k
    INNER JOIN T_TANEV te ON te.ID = k.C_TANEVID AND te.C_NEV = '2017/2018'  AND te.TOROLT = 'F'
    INNER JOIN T_TANEV teNext ON te.C_INTEZMENYID = teNext.C_INTEZMENYID AND teNext.C_NEV = '2018/2019'  AND te.TOROLT = 'F'
  WHERE k.TOROLT = 'F'
    AND NOT EXISTS (SELECT 1 FROM T_KEPESITES WHERE ELOZOTANEVIREKORDID = k.ID)

  INSERT INTO T_KEPESITES_VEGZETTSEG (
     C_KEPESITESID
    ,C_VEGZETTSEGID
    ,ELOZOTANEVIREKORDID
    ,NNID
  ) SELECT 
     k.ID   AS C_KEPESITESID           -- C_KEPESITESID - int
    ,v.ID   AS C_VEGZETTSEGID          -- C_VEGZETTSEGID - int
    ,NULL AS ELOZOTANEVIREKORDID    -- ELOZOTANEVIREKORDID - int
    ,NULL AS NNID                   -- NNID - int
  FROM T_KEPESITES_VEGZETTSEG kv
    INNER JOIN T_VEGZETTSEG v ON v.ELOZOTANEVIREKORDID = kv.C_VEGZETTSEGID
    INNER JOIN T_KEPESITES k ON  k.ELOZOTANEVIREKORDID = kv.C_KEPESITESID
    INNER JOIN T_TANEV te ON te.ID = v.C_TANEVID AND te.ID = k.C_TANEVID AND te.C_NEV = '2018/2019'  AND te.TOROLT = 'F'
  WHERE NOT EXISTS (SELECT 1 FROM T_KEPESITES_VEGZETTSEG kvs WHERE kvs.C_KEPESITESID = k.ID AND kvs.C_VEGZETTSEGID = v.ID)

END

GO

