﻿DECLARE intezmenytanevekCursor CURSOR LOCAL FOR 
  SELECT Intezmeny.ID, Tanev.ID
		FROM T_INTEZMENY Intezmeny
    INNER JOIN T_TANEV Tanev ON Tanev.C_INTEZMENYID = Intezmeny.ID
      AND Tanev.TOROLT = 'F'
      AND Tanev.C_NEV = '2019/2020'
      AND Intezmeny.TOROLT = 'F'

DECLARE @intezmenyId int, @tanevId int

OPEN intezmenytanevekCursor  
FETCH NEXT FROM intezmenytanevekCursor INTO @intezmenyId, @tanevId

WHILE @@FETCH_STATUS = 0 
BEGIN  
	PRINT N'INTÉZMÉNY ID: ' + CAST(@intezmenyId AS NVARCHAR(10)) + N', TANÉV ID: ' + CAST(@tanevId AS NVARCHAR(10))

  DECLARE @ISKOLAEGESZSEGUGYIKOORDINATORJOGID INT = NULL
  SELECT TOP 1 @ISKOLAEGESZSEGUGYIKOORDINATORJOGID = ID FROM dbo.T_JOGOSULTSAG WHERE C_JOG = 'IskolaEgeszsegugyiKoordinator' AND C_INTEZMENYID = @intezmenyId AND C_TANEVID = @tanevId
  IF(@ISKOLAEGESZSEGUGYIKOORDINATORJOGID IS NULL)
  BEGIN
  	INSERT INTO dbo.T_JOGOSULTSAG
  	(
  		C_JOG,  C_SZEREPKORTIPUS,[C_INTEZMENYID] , [C_TANEVID],  TOROLT,  SERIAL,  CREATED,  CREATOR 
  	)
  	VALUES
  	(
  		N'IskolaEgeszsegugyiKoordinator', -- C_JOG - nvarchar
  		7789, -- IskolaEgeszsegugyiKoordinator
  		@intezmenyId , @tanevId,
  		'F', -- TOROLT - char
  		0, -- SERIAL - int
  		GETDATE(), -- CREATED - datetime
  		0 -- CREATOR - int
  	)
  	SET @ISKOLAEGESZSEGUGYIKOORDINATORJOGID = SCOPE_IDENTITY()
  END
  DECLARE @VEDONOJOGID INT = NULL
  SELECT TOP 1 @VEDONOJOGID = ID FROM dbo.T_JOGOSULTSAG WHERE C_JOG = 'Vedono' AND C_INTEZMENYID = @intezmenyId AND C_TANEVID = @tanevId
  IF(@VEDONOJOGID IS NULL)
  BEGIN
  	INSERT INTO dbo.T_JOGOSULTSAG
  	(
  		C_JOG,  C_SZEREPKORTIPUS,[C_INTEZMENYID] , [C_TANEVID],  TOROLT,  SERIAL,  CREATED,  CREATOR 
  	)
  	VALUES
  	(
  		N'Vedono', -- C_JOG - nvarchar
  		7790, -- Vedono
  		@intezmenyId , @tanevId,
  		'F', -- TOROLT - char
  		0, -- SERIAL - int
  		GETDATE(), -- CREATED - datetime
  		0 -- CREATOR - int
  	)
  	SET @VEDONOJOGID = SCOPE_IDENTITY()
  END
  DECLARE @ISKOLAORVOSJOGID INT = NULL
  SELECT TOP 1 @ISKOLAORVOSJOGID = ID FROM dbo.T_JOGOSULTSAG WHERE C_JOG = 'Iskolaorvos' AND C_INTEZMENYID = @intezmenyId AND C_TANEVID = @tanevId
  IF(@ISKOLAORVOSJOGID IS NULL)
  BEGIN
  	INSERT INTO dbo.T_JOGOSULTSAG
  	(
  		C_JOG,  C_SZEREPKORTIPUS,[C_INTEZMENYID] , [C_TANEVID],  TOROLT,  SERIAL,  CREATED,  CREATOR 
  	)
  	VALUES
  	(
  		N'Iskolaorvos', -- C_JOG - nvarchar
  		7791, -- Iskolaorvos
  		@intezmenyId , @tanevId,
  		'F', -- TOROLT - char
  		0, -- SERIAL - int
  		GETDATE(), -- CREATED - datetime
  		0 -- CREATOR - int
  	)
  	SET @ISKOLAORVOSJOGID = SCOPE_IDENTITY()
  END
  DECLARE @ISKOLAEGESZSEGUGYIASSZISZTENSJOGID INT = NULL
  SELECT TOP 1 @ISKOLAEGESZSEGUGYIASSZISZTENSJOGID = ID FROM dbo.T_JOGOSULTSAG WHERE C_JOG = 'IskolaEgeszsegugyiAsszisztens' AND C_INTEZMENYID = @intezmenyId AND C_TANEVID = @tanevId
  IF(@ISKOLAEGESZSEGUGYIASSZISZTENSJOGID IS NULL)
  BEGIN
  	INSERT INTO dbo.T_JOGOSULTSAG
  	(
  		C_JOG,  C_SZEREPKORTIPUS,[C_INTEZMENYID] , [C_TANEVID],  TOROLT,  SERIAL,  CREATED,  CREATOR 
  	)
  	VALUES
  	(
  		N'IskolaEgeszsegugyiAsszisztens', -- C_JOG - nvarchar
  		7792, -- IskolaEgeszsegugyiAsszisztens
  		@intezmenyId , @tanevId,
  		'F', -- TOROLT - char
  		0, -- SERIAL - int
  		GETDATE(), -- CREATED - datetime
  		0 -- CREATOR - int
  	)
  	SET @ISKOLAEGESZSEGUGYIASSZISZTENSJOGID = SCOPE_IDENTITY()
  END
  DECLARE @ISKOLAEGESZSEGUGYILEKERDEZOJOGID INT = NULL
  SELECT TOP 1 @ISKOLAEGESZSEGUGYILEKERDEZOJOGID = ID FROM dbo.T_JOGOSULTSAG WHERE C_JOG = 'IskolaEgeszsegugyiLekerdezo' AND C_INTEZMENYID = @intezmenyId AND C_TANEVID = @tanevId
  IF(@ISKOLAEGESZSEGUGYILEKERDEZOJOGID IS NULL)
  BEGIN
  	INSERT INTO dbo.T_JOGOSULTSAG
  	(
  		C_JOG,  C_SZEREPKORTIPUS,[C_INTEZMENYID] , [C_TANEVID],  TOROLT,  SERIAL,  CREATED,  CREATOR 
  	)
  	VALUES
  	(
  		N'IskolaEgeszsegugyiLekerdezo', -- C_JOG - nvarchar
  		7793, -- IskolaEgeszsegugyiLekerdezo
  		@intezmenyId , @tanevId,
  		'F', -- TOROLT - char
  		0, -- SERIAL - int
  		GETDATE(), -- CREATED - datetime
  		0 -- CREATOR - int
  	)
  	SET @ISKOLAEGESZSEGUGYILEKERDEZOJOGID = SCOPE_IDENTITY()
  END

  FETCH NEXT FROM intezmenytanevekCursor INTO @intezmenyId, @tanevId
END  

CLOSE intezmenytanevekCursor  
DEALLOCATE intezmenytanevekCursor
