DROP PROCEDURE IF EXISTS dev.uspGenerateNemKivalaszthatoTanev 
GO

CREATE PROCEDURE dev.uspGenerateNemKivalaszthatoTanev 
   @tanevNev nvarchar(10)
  ,@elsoTanitasiNap nvarchar(4) = '0901'
  ,@utolsoTanitasiNap nvarchar(4) = '0615'
AS BEGIN
  
  -- @tanevNev nvarchar(10) = '2020/2021'
  --,@elsoTanitasiNap nvarchar(4) = '0901'
  --,@utolsoTanitasiNap nvarchar(4) = '0615'
  DECLARE
      @tanevElsoFele nvarchar(4) = SUBSTRING(@tanevNev,1,4)
     ,@tanevMasodikFele nvarchar(4) = SUBSTRING(@tanevNev,6,4)
  
  SET NOCOUNT ON;
  UPDATE T_TANEV SET 
     TOROLT='T' 
    ,LASTCHANGED = GETDATE()
    ,MODIFIER = 0
  WHERE C_ISKIVALASZTHATO = 'F' AND TOROLT ='F' AND C_KOVETKEZO = 'F'
  
  INSERT INTO T_TANEV (
     C_AKTIV
    ,C_ELSOTANITASINAP
    ,C_KEZDONAP
    ,C_KOVETKEZO
    ,C_NEV
    ,C_SORSZAM
    ,C_UTOLSONAP
    ,C_UTOLSOTANITASINAP
    ,C_AKTIVALVA
    ,C_INTEZMENYID
    ,TOROLT
    ,SERIAL
    ,LASTCHANGED
    ,CREATED
    ,MODIFIER
    ,CREATOR
    ,ELOZOTANEVIREKORDID
    ,NNID
    ,C_ISKIVALASZTHATO
  ) SELECT
     'F' AS C_AKTIV 
    ,@tanevElsoFele+@elsoTanitasiNap AS C_ELSOTANITASINAP
    ,@tanevElsoFele+'0901' AS C_KEZDONAP
    ,'F' AS C_KOVETKEZO
    ,@tanevNev AS C_NEV
    ,SUBSTRING(@tanevNev,3,2)*2 AS C_SORSZAM
    ,@tanevMasodikFele+'0831' AS C_UTOLSONAP
    ,@tanevMasodikFele+@utolsoTanitasiNap AS C_UTOLSOTANITASINAP
    ,NULL AS C_AKTIVALVA
    ,i.ID AS C_INTEZMENYID
    ,'F' AS TOROLT
    ,0 AS SERIAL
    ,GETDATE() AS LASTCHANGED
    ,GETDATE() AS CREATED
    ,NULL AS MODIFIER
    ,NULL AS CREATOR
    ,NULL AS ELOZOTANEVIREKORDID
    ,NULL AS NNID
    ,'F' AS C_ISKIVALASZTHATO
  FROM T_INTEZMENY i
  WHERE i.TOROLT= 'F'
  AND NOT EXISTS (
  SELECT 1 FROM T_TANEV tv
  WHERE tv.C_NEV = @tanevNev AND tv.C_INTEZMENYID = i.ID AND tv.TOROLT='F')

  ;DISABLE TRIGGER ALL ON T_DICTIONARYITEMBASE
  SET IDENTITY_INSERT T_DICTIONARYTYPE ON
  INSERT INTO T_DICTIONARYTYPE (
     ID
    ,C_CODENAME
    ,C_DESCRIPTION
    ,C_ISCOLOREDITABLE
    ,C_ISCUSTOMITEMALLOWED
    ,C_NAME
    ,C_INTEZMENYID
    ,C_TANEVID
    ,TOROLT
    ,SERIAL
    ,LASTCHANGED
    ,CREATED
    ,MODIFIER
    ,CREATOR
  ) SELECT 
     dt.ID
    ,dt.C_CODENAME AS C_CODENAME              -- C_CODENAME - nvarchar(30)
    ,dt.C_DESCRIPTION AS C_DESCRIPTION          -- C_DESCRIPTION - nvarchar(255)
    ,dt.C_ISCOLOREDITABLE  AS C_ISCOLOREDITABLE       -- C_ISCOLOREDITABLE - char(1)
    ,dt.C_ISCUSTOMITEMALLOWED  AS C_ISCUSTOMITEMALLOWED   -- C_ISCUSTOMITEMALLOWED - char(1)
    ,dt.C_NAME AS C_NAME                  -- C_NAME - nvarchar(50)
    ,dt.C_INTEZMENYID   AS C_INTEZMENYID           -- C_INTEZMENYID - int
    ,tnHidden.ID AS C_TANEVID               -- C_TANEVID - int
    ,dt.TOROLT  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
  FROM T_DICTIONARYTYPE dt
      INNER JOIN T_TANEV tn ON tn.C_INTEZMENYID=dt.C_INTEZMENYID AND tn.C_AKTIV='T' AND tn.TOROLT='F'
      INNER JOIN T_TANEV tnHidden ON tnHidden.C_INTEZMENYID=dt.C_INTEZMENYID AND tnHidden.C_KOVETKEZO='F' AND tnHidden.C_ISKIVALASZTHATO='F' AND tnHidden.TOROLT='F'
        AND tnHidden.C_NEV = @tanevNev
  WHERE dt.C_TANEVID =  tn.ID
  SET IDENTITY_INSERT T_DICTIONARYTYPE OFF

  SET IDENTITY_INSERT T_DICTIONARYITEMBASE ON
  INSERT INTO T_DICTIONARYITEMBASE (
     ID
    ,C_VALUE
    ,C_NAME
    ,C_NAME_1
    ,C_NAME_2
    ,C_NAME_3
    ,C_NAME_4
    ,C_VISIBLE
    ,C_CODE
    ,C_TYPE
    ,C_PROTECTED
    ,C_COLOR
    ,C_ORDER
    ,C_BGCOLOR
    ,C_DESCRIPTION
    ,C_ISACTIVE
    ,C_SHORTNAME
    ,C_DICTIONARYTYPEID
    ,C_INTEZMENYID
    ,C_TANEVID
    ,TOROLT
    ,SERIAL
    ,LASTCHANGED
    ,CREATED
    ,MODIFIER
    ,CREATOR
    ,ELOZOTANEVIREKORDID
  ) SELECT
     dib.ID
    ,C_VALUE AS C_VALUE                -- C_VALUE - int
    ,C_NAME AS C_NAME                  -- C_NAME - nvarchar(250)
    ,C_NAME_1 AS C_NAME_1               -- C_NAME_1 - nvarchar(250)
    ,C_NAME_2 AS C_NAME_2               -- C_NAME_2 - nvarchar(250)
    ,C_NAME_3 AS C_NAME_3               -- C_NAME_3 - nvarchar(250)
    ,C_NAME_4 AS C_NAME_4               -- C_NAME_4 - nvarchar(250)
    ,C_VISIBLE  AS C_VISIBLE               -- C_VISIBLE - char(1)
    ,C_CODE AS C_CODE                 -- C_CODE - nvarchar(20)
    ,C_TYPE AS C_TYPE                  -- C_TYPE - nvarchar(32)
    ,C_PROTECTED  AS C_PROTECTED             -- C_PROTECTED - char(1)
    ,C_COLOR AS C_COLOR                -- C_COLOR - nvarchar(6)
    ,C_ORDER AS C_ORDER                -- C_ORDER - int
    ,C_BGCOLOR AS C_BGCOLOR              -- C_BGCOLOR - nvarchar(6)
    ,C_DESCRIPTION AS C_DESCRIPTION          -- C_DESCRIPTION - nvarchar(255)
    ,C_ISACTIVE  AS C_ISACTIVE              -- C_ISACTIVE - char(1)
    ,C_SHORTNAME AS C_SHORTNAME            -- C_SHORTNAME - nvarchar(30)
    ,C_DICTIONARYTYPEID   AS C_DICTIONARYTYPEID      -- C_DICTIONARYTYPEID - int
    ,dib.C_INTEZMENYID   AS C_INTEZMENYID           -- C_INTEZMENYID - int
    ,tnHidden.ID   AS C_TANEVID               -- C_TANEVID - int
    ,dib.TOROLT  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
    ,dib.ID AS ELOZOTANEVIREKORDID    -- ELOZOTANEVIREKORDID - int
  FROM T_DICTIONARYITEMBASE dib
      INNER JOIN T_INTEZMENY i ON i.ID=dib.C_INTEZMENYID AND i.TOROLT='F'
      INNER JOIN T_TANEV tn ON tn.C_INTEZMENYID=i.ID AND tn.C_AKTIV='T' AND tn.TOROLT='F'
      INNER JOIN T_TANEV tnHidden ON tnHidden.C_INTEZMENYID=i.ID AND tnHidden.C_KOVETKEZO='F' AND tnHidden.C_ISKIVALASZTHATO='F' AND tnHidden.TOROLT='F'
        AND tnHidden.C_NEV = @tanevNev
  WHERE dib.C_TANEVID = tn.ID AND dib.ID<100000

  SET IDENTITY_INSERT T_DICTIONARYITEMBASE OFF
	-- print 'Szótárelemek létrehozása sikeres!';

  INSERT INTO T_OKTNEVELESIKATEGORIA (
     ID
    ,C_FELADATCSOPORTTANULOOSZTALYK
    ,C_ALINTEZMENYID
    ,C_ALTANEVID
    ,TOROLT
    ,SERIAL
    ,LASTCHANGED
    ,CREATED
    ,MODIFIER
    ,CREATOR
    ,ELOZOTANEVIREKORDID
    ,ELOZOTANEVIREKORDIDA
    ,NNID)
  SELECT
     o.ID
    ,C_FELADATCSOPORTTANULOOSZTALYK
    ,i.ID
    ,tnHidden.ID
    ,'F'
    ,0
    ,GETDATE()
    ,GETDATE()
    ,0
    ,0
    ,o.ID
    ,o.ID
    ,o.NNID
  FROM T_OKTNEVELESIKATEGORIA o
      INNER JOIN T_INTEZMENY i ON i.ID=o.C_ALINTEZMENYID AND i.TOROLT='F'
      INNER JOIN T_TANEV tn ON tn.C_INTEZMENYID=i.ID AND tn.C_AKTIV='T' AND tn.TOROLT='F'
      INNER JOIN T_TANEV tnHidden ON tnHidden.C_INTEZMENYID=i.ID AND tnHidden.C_KOVETKEZO='F' AND tnHidden.C_ISKIVALASZTHATO='F' AND tnHidden.TOROLT='F'
        AND tnHidden.C_NEV = @tanevNev
  WHERE C_ALTANEVID=tn.ID AND o.TOROLT='F' AND o.ID<100000
  

  
  INSERT INTO T_DICTIONARYITEMBASENYELV(
     C_NAME
    ,C_NAME_1
    ,C_NAME_2
    ,C_NAME_3
    ,C_NAME_4
    ,C_NYELVID
    ,C_SHORTNAME
    ,C_DICTIONARYITEMBASEID
    ,C_INTEZMENYID
    ,C_TANEVID
    ,TOROLT
    ,SERIAL
    ,LASTCHANGED
    ,CREATED
    ,MODIFIER
    ,CREATOR
    ,ELOZOTANEVIREKORDID
  ) SELECT
     dibNy.C_NAME
    ,dibNy.C_NAME_1
    ,dibNy.C_NAME_2
    ,dibNy.C_NAME_3
    ,dibNy.C_NAME_4
    ,dibNyelv.ID
    ,dibNy.C_SHORTNAME
    ,dibNew.ID
    ,tnKov.C_INTEZMENYID
    ,tnKov.ID
    ,'F'
    ,0
    ,GETDATE()
    ,GETDATE()
    ,NULL
    ,NULL
    ,dibNy.ID
  FROM T_DICTIONARYITEMBASENYELV dibNy
    INNER JOIN T_TANEV tn ON dibNy.C_TANEVID = tn.ID AND tn.C_AKTIV='T' AND tn.TOROLT='F'
    INNER JOIN T_TANEV tnKov ON tnKov.C_INTEZMENYID = tn.C_INTEZMENYID AND tnKov.C_ISKIVALASZTHATO = 'F' AND tnKov.TOROLT='F'
      AND tnKov.C_NEV = @tanevNev
    INNER JOIN T_DICTIONARYITEMBASE dibNew ON dibNew.ELOZOTANEVIREKORDID=dibNy.C_DICTIONARYITEMBASEID
      AND dibNew.C_TANEVID = tnKov.ID AND dibNew.TOROLT='F'
    INNER JOIN T_DICTIONARYITEMBASE dibNyelv ON dibNyelv.ELOZOTANEVIREKORDID = dibNy.C_NYELVID AND dibNyelv.TOROLT='F'
  WHERE dibNy.C_TANEVID = tn.ID AND dibNy.TOROLT='F' 
    AND NOT EXISTS (SELECT 1 FROM T_DICTIONARYITEMBASENYELV WHERE TOROLT='F' AND C_DICTIONARYITEMBASEID = dibNew.ID AND C_TANEVID = tnKov.ID)
    
  DECLARE tableCursor CURSOR FOR 
  SELECT DISTINCT OBJECT_NAME(parent_object_id) 
  FROM sys.foreign_key_columns 
  WHERE referenced_object_id = OBJECT_ID('T_DICTIONARYITEMBASE') AND OBJECT_NAME(parent_object_id) NOT IN ('T_OKTNEVELESIKATEGORIA','T_DICTIONARYITEMBASENYELV')
  
  DECLARE @tableName nvarchar(80)
    ,@sql nvarchar(max)
    ,@columnList nvarchar(max)
    ,@insertList nvarchar(max)
    
  OPEN tableCursor
  FETCH NEXT FROM tableCursor into @tableName 
  WHILE @@FETCH_STATUS = 0 BEGIN
    
    SELECT @columnList = ISNULL(@columnList + ', ', '') + c.[name]
    FROM sys.tables t
      INNER JOIN sys.columns c ON c.[object_id] = t.[object_id]
    WHERE t.[name] = @tableName
    ORDER BY c.column_id
    
    SET @insertList = @columnList
    SET @insertList = REPLACE(@insertList, 'C_ALTANEVID', 'tnHidden.ID')
    SET @insertList = REPLACE(@insertList, 'SERIAL', '0')
    SET @insertList = REPLACE(@insertList, 'LASTCHANGED', 'GETDATE()')
    SET @insertList = REPLACE(@insertList, 'CREATED', 'GETDATE()')
    SET @insertList = REPLACE(@insertList, 'MODIFIER', 'NULL')
    SET @insertList = REPLACE(@insertList, 'CREATOR', 'NULL')
    SET @insertList = REPLACE(@insertList, 'TOROLT', 'x.TOROLT')
    SET @insertList = REPLACE(@insertList, 'NNID', 'x.NNID')
    SET @insertList = REPLACE(@insertList, 'ELOZOTANEVIREKORDIDA', 'x.ID')
    SET @insertList = REPLACE(@insertList, 'ELOZOTANEVIREKORDID', 'x.ID')
          
  
    SET @sql = N'
	  DISABLE TRIGGER ALL ON ' + @tableName +'
      INSERT INTO ' + @tableName + ' (
        ' + @columnList + ' 
      ) SELECT ' + @insertList + '
      FROM ' + @tableName + ' x
        INNER JOIN T_INTEZMENY i ON i.ID=x.C_ALINTEZMENYID AND i.TOROLT=''F'' 
        INNER JOIN T_TANEV tn ON tn.C_INTEZMENYID=i.ID AND tn.C_AKTIV=''T'' AND tn.TOROLT=''F''
        INNER JOIN T_TANEV tnHidden ON tnHidden.C_INTEZMENYID=i.ID AND tnHidden.C_KOVETKEZO=''F'' AND tnHidden.C_ISKIVALASZTHATO=''F'' AND tnHidden.TOROLT=''F''
          AND tnHidden.C_NEV = @tanevNev
      WHERE x.C_ALTANEVID = tn.ID
        AND x.C_ALINTEZMENYID = i.ID
        AND x.TOROLT = ''F''
        AND x.ID<100000;
	  ENABLE TRIGGER ALL ON ' + @tableName +'
		'
    SET @sql = REPLACE(@sql, 'SELECT ID','SELECT x.ID')

    EXEC sp_executesql 
       @sql,N'
       @tanevNev nvarchar(10)'
      ,@tanevNev = @tanevNev

    SET @columnList = NULL
    FETCH NEXT FROM tableCursor into @tableName 
  END
  
  CLOSE tableCursor
  DEALLOCATE tableCursor

  INSERT INTO T_TANTERV (
  	 C_CSOPORTTIPUSA
  	,C_EVFOLYAMTOL
  	,C_EVFOLYAMIG
  	,C_KERETTANTERVREEPULO
  	,C_NEV
  	,C_MEGJEGYZES
    ,C_VEDETT
  	,C_INTEZMENYID
  	,C_TANEVID
  	,TOROLT
    ,SERIAL
    ,LASTCHANGED
    ,CREATED
    ,MODIFIER
    ,CREATOR
  	,NNID
  	,ELOZOTANEVIREKORDID
  ) SELECT 
	   t.C_CSOPORTTIPUSA AS C_CSOPORTTIPUSA
	  ,t.C_EVFOLYAMTOL AS C_EVFOLYAMTOL
	  ,t.C_EVFOLYAMIG AS C_EVFOLYAMIG
	  ,C_KERETTANTERVREEPULO AS C_KERETTANTERVREEPULO
	  ,t.C_NEV AS C_NEV
	  ,C_MEGJEGYZES AS C_MEGJEGYZES
    ,t.C_VEDETT
    ,i.ID AS C_INTEZMENYID
    ,tnHidden.ID AS C_TANEVID
    ,'F' AS TOROLT
    ,0 AS SERIAL
    ,GETDATE() AS LASTCHANGED
    ,GETDATE() AS CREATED
    ,NULL AS MODIFIER
    ,NULL AS CREATOR
    ,NULL AS NNID
	  ,t.ID AS ELOZOTANEVIREKORDID
  FROM T_TANTERV t 
    INNER JOIN T_INTEZMENY i ON i.ID=t.C_INTEZMENYID AND i.TOROLT='F'
    INNER JOIN T_TANEV tn ON tn.C_INTEZMENYID=i.ID AND tn.C_AKTIV='T' AND tn.TOROLT='F'
    INNER JOIN T_TANEV tnHidden ON tnHidden.C_INTEZMENYID=i.ID AND tnHidden.C_KOVETKEZO='F' AND tnHidden.C_ISKIVALASZTHATO='F' AND tnHidden.TOROLT='F'
      AND tnHidden.C_NEV = @tanevNev
  WHERE t.C_TANEVID = tn.ID  
    AND t.TOROLT = 'F' AND t.C_CSOPORTTIPUSA<100000;
  
  ENABLE TRIGGER ALL ON T_DICTIONARYITEMBASE;

END
GO