﻿DROP PROCEDURE IF EXISTS uspFollowUpAlkalmazottVegzettseg
GO

CREATE PROCEDURE uspFollowUpAlkalmazottVegzettseg
   @intezmenyId int
  ,@aktTanevId int
  ,@kovetkezoTanevId int
  ,@vegzettsegId int
AS
BEGIN
  SET NOCOUNT ON; 
	DECLARE 
     @kovVegzettsegId int
	  ,@selectColListV nvarchar(max)
    ,@insertColListV nvarchar(max)
    ,@updateColListV nvarchar(max)
    ,@selectColListKV nvarchar(max)
    ,@insertColListKV nvarchar(max)
    ,@updateColListKV nvarchar(max)
    ,@sql nvarchar(max)

  SET @kovVegzettsegId = (SELECT ID FROM T_VEGZETTSEG_OSSZES WHERE ELOZOTANEVIREKORDID=@vegzettsegId)
	IF (SELECT TOROLT FROM T_VEGZETTSEG_OSSZES WHERE ID = @vegzettsegId)='T' OR (SELECT TOROLT FROM T_VEGZETTSEG_OSSZES WHERE ELOZOTANEVIREKORDID = @vegzettsegId)='T' BEGIN
	  RETURN
	END
	
  CREATE TABLE #ColumnMapping (selectColName nvarchar(128),insertColName nvarchar(128),updateColName nvarchar(128),tableName nvarchar(128))
  INSERT INTO #ColumnMapping(
     selectColName
    ,insertColname
    ,updateColname
    ,tableName 
  )
  SELECT 
     selectColName
    ,insertColName
    ,updateColName 
    ,'T_VEGZETTSEG'
  FROM fnGetFollowUpColumnMapping (NULL,'T_VEGZETTSEG')
  UNION ALL
  SELECT 
     selectColName
    ,insertColName
    ,updateColName 
    ,'T_KEPESITES_VEGZETTSEG'
  FROM fnGetFollowUpColumnMapping ('ELOZOTANEVIREKORDID','T_KEPESITES_VEGZETTSEG')

  IF @kovVegzettsegId IS NULL BEGIN
    SELECT @insertColListV = ISNULL(@insertColListV+','+insertColname,insertColname) FROM #columnMapping WHERE tableName = 'T_VEGZETTSEG'
    SELECT @selectColListV = ISNULL(@selectColListV+','+selectColName,selectColName) FROM #columnMapping WHERE tableName = 'T_VEGZETTSEG'
    SELECT @insertColListKV = ISNULL(@insertColListKV+','+insertColname,insertColname) FROM #columnMapping WHERE tableName = 'T_KEPESITES_VEGZETTSEG'
    SELECT @selectColListKV = ISNULL(@selectColListKV+','+selectColName,selectColName) FROM #columnMapping WHERE tableName = 'T_KEPESITES_VEGZETTSEG'
	  
    SET @sql = N'
    INSERT INTO T_VEGZETTSEG_OSSZES(
	    '+@insertColListV+'
	  ) SELECT 
	     '+@selectColListV+'
	  FROM T_VEGZETTSEG_OSSZES s 
	    INNER JOIN T_FELHASZNALO_OSSZES fh ON fh.ELOZOTANEVIREKORDID=s.C_TANARID AND fh.TOROLT=''F''
	  WHERE s.ID=@vegzettsegId
	  
	  INSERT INTO T_KEPESITES_VEGZETTSEG (
		  '+@insertColListKV+'
	  ) SELECT 
	    '+@selectColListKV+'
	  FROM T_VEGZETTSEG_OSSZES s
	    INNER JOIN T_KEPESITES_VEGZETTSEG kv ON kv.C_VEGZETTSEGID=s.ID
	    INNER JOIN T_KEPESITES_OSSZES k ON k.ELOZOTANEVIREKORDID=kv.C_KEPESITESID AND k.TOROLT=''F''
	    INNER JOIN T_VEGZETTSEG_OSSZES vKov ON vKov.ELOZOTANEVIREKORDID=s.ID
	  WHERE s.ID = @vegzettsegId'
	END
	ELSE BEGIN
    SELECT 
      @updateColListV = ISNULL(@updateColListV+','+insertColname+' = '+updateColname,insertColName+' = '+updateColname)
    FROM #columnMapping WHERE updateColName IS NOT NULL AND tableName = 'T_VEGZETTSEG'
    SELECT @insertColListKV = ISNULL(@insertColListKV+','+insertColname,insertColname) FROM #columnMapping WHERE tableName = 'T_KEPESITES_VEGZETTSEG'
    SELECT @selectColListKV = ISNULL(@selectColListKV+','+selectColName,selectColName) FROM #columnMapping WHERE tableName = 'T_KEPESITES_VEGZETTSEG'

    DELETE kv
	  FROM T_KEPESITES_VEGZETTSEG kv 
	    INNER JOIN T_KEPESITES_OSSZES kKov ON kKov.ID=kv.C_KEPESITESID AND kKov.TOROLT='F'
	    INNER JOIN T_KEPESITES_OSSZES kOld ON kOld.ID=kKov.ELOZOTANEVIREKORDID AND kOld.TOROLT='F'
	    LEFT JOIN T_KEPESITES_VEGZETTSEG kvOld ON kvOld.C_VEGZETTSEGID=@vegzettsegId AND kvOld.C_KEPESITESID=kOld.ID
	  WHERE kv.C_VEGZETTSEGID=@kovVegzettsegId AND kvOld.C_KEPESITESID IS NULL

    SET @sql =N'
	  UPDATE t SET
	    '+@updateColListV+'
	  FROM T_VEGZETTSEG_OSSZES s
	    INNER JOIN T_VEGZETTSEG_OSSZES t ON t.ELOZOTANEVIREKORDID = s.ID AND s.ID=@vegzettsegId
  
    INSERT INTO T_KEPESITES_VEGZETTSEG (
		  '+@insertColListKV+'
	  ) SELECT 
	    '+@selectColListKV+'
	  FROM T_VEGZETTSEG_OSSZES s
	    INNER JOIN T_KEPESITES_VEGZETTSEG kv ON kv.C_VEGZETTSEGID=s.ID
	    INNER JOIN T_KEPESITES_OSSZES k ON k.ELOZOTANEVIREKORDID=kv.C_KEPESITESID AND k.TOROLT=''F''
	    INNER JOIN T_VEGZETTSEG_OSSZES vKov ON vKov.ELOZOTANEVIREKORDID=s.ID
	  WHERE s.ID=@vegzettsegId AND NOT EXISTS (SELECT 1 FROM T_KEPESITES_VEGZETTSEG WHERE C_VEGZETTSEGID=@kovVegzettsegId AND C_KEPESITESID=k.ID)'
	END

  --PRINT @sql
  exec sp_executesql
     @sql,N'
     @intezmenyId int
    ,@aktTanevId int
    ,@kovetkezoTanevId int
    ,@vegzettsegId int
    ,@kovVegzettsegId int'
    ,@intezmenyId = @intezmenyId 
    ,@aktTanevId = @aktTanevId
    ,@kovetkezoTanevId = @kovetkezoTanevId 
    ,@vegzettsegId = @vegzettsegId 
    ,@kovVegzettsegId = @kovVegzettsegId 


END
GO
