﻿DROP PROCEDURE IF EXISTS uspFollowUpGondviseloCim
GO

CREATE PROCEDURE uspFollowUpGondviseloCim
   @intezmenyId int
  ,@tanevId int
  ,@kovetkezoTanevId int
  ,@gondviseloId int
  ,@kovGondviseloId int = NULL
AS
BEGIN
  SET NOCOUNT ON;

  IF NOT EXISTS (SELECT 1 from T_TANEV where ID = @kovetkezoTanevId and C_KOVETKEZO = 'T' and C_AKTIVALVA is null and C_ISKIVALASZTHATO = 'T')
    BEGIN
      RETURN;
    END

  DECLARE 
    @selectColList nvarchar(max)
    ,@insertColList nvarchar(max)
    ,@sql nvarchar(max)

  CREATE TABLE #columnMapping (
     insertColName nvarchar(max) COLLATE DATABASE_DEFAULT
    ,selectColName nvarchar(max)
  )

  INSERT INTO #columnMapping 
  SELECT insertColName,selectColName 
  FROM fnGetFollowUpColumnMapping (NULL,'T_CIM')
  
  IF @kovGondviseloId IS NULL
  BEGIN
    SELECT @kovGondviseloId = gNext.ID
    FROM 
      T_GONDVISELO_OSSZES g
      INNER JOIN T_GONDVISELO_OSSZES gNext ON gNext.TOROLT='F' AND gNext.ELOZOTANEVIREKORDID = g.ID AND gNext.C_TANEVID = @kovetkezoTanevId AND gNext.C_INTEZMENYID = @intezmenyId
    WHERE 
      g.TOROLT = 'F'
      AND g.ID = @gondviseloId
      AND g.C_INTEZMENYID = @intezmenyId
      AND g.C_TANEVID = @tanevId
  END

  IF @kovGondviseloId IS NOT NULL
  BEGIN
    DELETE FROM T_CIM_OSSZES WHERE 
      C_GONDVISELOID = @kovGondviseloId
      AND C_INTEZMENYID = @intezmenyId
      AND C_TANEVID = @kovetkezoTanevId

    UPDATE #columnMapping SET selectColName = 'fhNew.ID' WHERE insertColName = 'C_FELHASZNALOID'
    UPDATE #columnMapping SET selectColName = 'g.ID' WHERE insertColName = 'C_GONDVISELOID'
    SELECT @insertColList = ISNULL(@insertColList+','+insertColname,insertColname) FROM #columnMapping
    SELECT @selectColList = ISNULL(@selectColList+','+selectColName,selectColName) FROM #columnMapping
    SET @sql =N'
      INSERT INTO T_CIM_OSSZES (
        '+@insertColList+'
      ) SELECT
        '+@selectColList+'
      FROM T_CIM_OSSZES s
        INNER JOIN T_FELHASZNALO_OSSZES fh ON fh.ID = s.C_FELHASZNALOID
        INNER JOIN T_FELHASZNALO_OSSZES fhNew ON fhNew.C_OKTATASIAZONOSITO=fh.C_OKTATASIAZONOSITO AND fhNew.TOROLT=''F'' AND fhNew.C_TANEVID=@kovetkezoTanevId
        INNER JOIN T_GONDVISELO_OSSZES g ON g.ELOZOTANEVIREKORDID=s.C_GONDVISELOID AND g.TOROLT=''F''
      WHERE 
        s.TOROLT = ''F''
        AND s.C_GONDVISELOID = @gondviseloId'
    
    --print @sql
    EXEC sp_executesql 
      @sql,N'
      @intezmenyId int
      ,@tanevId int
      ,@kovetkezoTanevId int
      ,@gondviseloId int'
      ,@intezmenyId = @intezmenyId
      ,@tanevId = @tanevId
      ,@kovetkezoTanevId = @kovetkezoTanevId 
      ,@gondviseloId = @gondviseloId
  END
END
GO