﻿-- ===================================================================
-- Author: Telek Ákos
-- Created: 2018.06.21.
-- Description: 
--   Dictionary elemek FollowUp-ja
-- ===================================================================
DROP PROCEDURE IF EXISTS sp_FollowUpDictionaryItemBase 
GO

CREATE PROCEDURE sp_FollowUpDictionaryItemBase
   @pItemId int
  ,@pCurrentTanevId int
  ,@pNextTanevId int
  ,@pIntezmenyId int
AS 
BEGIN
  DECLARE 
     @typeName nvarchar(64)
    ,@sql nvarchar(max)
    ,@kovItemId int
    ,@isItemTorolt char(1)
    ,@isKovItemTorolt char(1)
    ,@columnList nvarchar(max)
    ,@columnListInsert nvarchar(max)
    ,@itemName nvarchar(500)

  SELECT 
     @typeName = d.C_TYPE
    ,@isItemTorolt = d.TOROLT
  FROM T_DICTIONARYITEMBASE_OSSZES d
  WHERE d.ID = @pItemId 
    AND d.C_INTEZMENYID = @pIntezmenyId 
    AND d.C_TANEVID = @pCurrentTanevId
  
  SELECT 
     @kovItemId = d.ID
    ,@isKovItemTorolt = d.TOROLT
  FROM T_DICTIONARYITEMBASE_OSSZES d
  WHERE d.C_INTEZMENYID = @pIntezmenyId
    AND d.C_TANEVID = @pNextTanevId
    AND d.ELOZOTANEVIREKORDID = @pItemId
  
  SELECT @itemName = C_NAME FROM T_DICTIONARYITEMBASE_OSSZES WHERE ID = @pItemId AND C_TANEVID=@pCurrentTanevId

  IF EXISTS(SELECT 1 FROM T_DICTIONARYITEMBASE_OSSZES WHERE C_TYPE=@typeName AND C_NAME=@itemName AND C_TANEVID=@pNextTanevId AND ISNULL(ELOZOTANEVIREKORDID,0)<>@pItemId AND TOROLT='F') BEGIN
    UPDATE T_DICTIONARYITEMBASE_OSSZES SET ELOZOTANEVIREKORDID = NULL WHERE ELOZOTANEVIREKORDID = @pItemId AND C_TANEVID=@pNextTanevId AND TOROLT='F'
    UPDATE T_DICTIONARYITEMBASE_OSSZES SET ELOZOTANEVIREKORDID = @pItemId WHERE C_NAME=@itemName AND C_TANEVID = @pNextTanevId AND TOROLT='F' AND C_TYPE=@typeName
    SELECT 
       @kovItemId = d.ID
      ,@isKovItemTorolt = d.TOROLT
    FROM T_DICTIONARYITEMBASE_OSSZES d
    WHERE d.C_INTEZMENYID = @pIntezmenyId AND d.C_TANEVID = @pNextTanevId AND d.ELOZOTANEVIREKORDID = @pItemId

    SET @sql=N'
      UPDATE T_'+@typeName+'_OSSZES SET ELOZOTANEVIREKORDID = NULL WHERE ELOZOTANEVIREKORDID = @pItemId AND C_ALTANEVID=@pNextTanevId AND TOROLT=''F''
      UPDATE T_'+@typeName+'_OSSZES SET ELOZOTANEVIREKORDID = @pItemId, ELOZOTANEVIREKORDIDA = @pItemId WHERE ID = @kovItemId AND C_ALTANEVID=@pNextTanevId AND TOROLT=''F''' 
    EXEC sp_Executesql @sql,N'@pItemId int,@kovItemId int,@pNextTanevId int',@pNextTanevId = @pNextTanevId,@pItemId = @pItemId,@kovItemId = @kovItemId
  END

  IF @isItemTorolt = 'T' OR @isKovItemTorolt = 'T' BEGIN
    RETURN
  END  

  IF @kovItemId IS NULL BEGIN  
    INSERT INTO T_DICTIONARYITEMBASE (
       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
      ,NNID
    ) SELECT 
       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
      ,C_INTEZMENYID   AS C_INTEZMENYID           -- C_INTEZMENYID - int
      ,@pNextTanevId   AS C_TANEVID               -- C_TANEVID - int
      ,TOROLT  AS TOROLT                  -- TOROLT - char(1)
      ,0   AS SERIAL                  -- SERIAL - int
      ,GETDATE() AS LASTCHANGED       -- LASTCHANGED - datetime
      ,GETDATE() AS CREATED           -- CREATED - datetime
      ,MODIFIER AS MODIFIER               -- MODIFIER - int
      ,MODIFIER AS CREATOR                -- CREATOR - int
      ,ID AS ELOZOTANEVIREKORDID    -- ELOZOTANEVIREKORDID - int
      ,NULL AS NNID                   -- NNID - int
    FROM T_DICTIONARYITEMBASE_OSSZES d
    WHERE d.ID = @pItemId 
      AND d.C_INTEZMENYID = @pIntezmenyId 
      AND d.C_TANEVID = @pCurrentTanevId
      
    SET @kovItemId = SCOPE_IDENTITY()
    
    SELECT @columnList = ISNULL(@columnList + ', ', '') + COLUMN_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'T_' + @typeName
      AND TABLE_SCHEMA = 'dbo'
    ORDER BY ORDINAL_POSITION
    
    SET @columnListInsert = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(STUFF(@columnList, 1, 2, '@kovItemId'), 'CREATOR', 'MODIFIER'), 'C_ALTANEVID', '@pNextTanevId'), 'ELOZOTANEVIREKORDIDA', 'ID'), 'ELOZOTANEVIREKORDID', 'ID'), 'LASTCHANGED', 'GETDATE()')
    
    SET @sql = N'
      INSERT INTO T_' + @typeName + ' (' + @columnList + ')
      SELECT ' + @columnListInsert + '
      FROM T_' + @typeName + '_OSSZES
      WHERE ID = @pItemId 
        AND C_ALTANEVID = @pCurrentTanevId 
        AND C_ALINTEZMENYID = @pIntezmenyId
    '

    EXEC sys.sp_executesql @sql, N'
       @kovItemId int
      ,@pItemId int
      ,@pCurrentTanevId int
      ,@pNextTanevId int
      ,@pIntezmenyId int'
      ,@kovItemId = @kovItemId
      ,@pItemId = @pItemId
      ,@pCurrentTanevId = @pCurrentTanevId
      ,@pNextTanevId = @pNextTanevId
      ,@pIntezmenyId = @pIntezmenyId
  END
  ELSE BEGIN
    UPDATE kov SET
       kov.C_VALUE = akt.c_VALUE
      ,kov.C_NAME = akt.C_NAME
      ,kov.C_NAME_1 = akt.C_NAME_1
      ,kov.C_NAME_2 = akt.C_NAME_2
      ,kov.C_NAME_3 = akt.C_NAME_3
      ,kov.C_NAME_4 = akt.C_NAME_4
      ,kov.C_VISIBLE = akt.C_VISIBLE
      ,kov.C_CODE = akt.C_CODE
      ,kov.C_PROTECTED = akt.C_PROTECTED
      ,kov.C_COLOR = akt.C_COLOR
      ,kov.C_ORDER = akt.C_ORDER
      ,kov.C_BGCOLOR = akt.C_BGCOLOR
      ,kov.C_DESCRIPTION = akt.C_DESCRIPTION
      ,kov.C_ISACTIVE = akt.C_ISACTIVE
      ,kov.C_SHORTNAME = akt.C_SHORTNAME
    FROM T_DICTIONARYITEMBASE_OSSZES kov
    INNER JOIN T_DICTIONARYITEMBASE_OSSZES akt ON akt.ID = kov.ELOZOTANEVIREKORDID
    WHERE akt.ID = @pItemId
      AND kov.ID = @kovItemId
      AND akt.C_TANEVID = @pCurrentTanevId
      AND kov.C_TANEVID = @pNextTanevId
      AND akt.C_INTEZMENYID = @pIntezmenyId
      AND kov.C_INTEZMENYID = @pIntezmenyId
    
    SET @columnList = ''
    SELECT @columnList += 'kov.' + COLUMN_NAME + ' = akt.' + COLUMN_NAME + ', '
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'T_' + @typeName
      AND TABLE_SCHEMA = 'dbo'
      AND COLUMN_NAME NOT IN ('ID', 'C_ALINTEZMENYID', 'C_ALTANEVID', 'TOROLT', 'ELOZOTANEVIREKORDID', 'ELOZOTANEVIREKORDIDA', 'NNID', 'LASTCHANGED', 'CREATOR', 'CREATED')
    ORDER BY ORDINAL_POSITION 
    
    SET @columnList += 'kov.LASTCHANGED = GETDATE()'
    
    SET @sql = N'
      UPDATE kov SET
        ' + @columnList + '
      FROM T_' + @typeName + '_OSSZES kov
      INNER JOIN T_' + @typeName + '_OSSZES akt ON akt.ID = kov.ELOZOTANEVIREKORDID
      WHERE akt.ID = @pItemId
        AND kov.ID = @kovItemId
        AND akt.C_ALTANEVID = @pCurrentTanevId
        AND kov.C_ALTANEVID = @pNextTanevId
        AND akt.C_ALINTEZMENYID = @pIntezmenyId
        AND kov.C_ALINTEZMENYID = @pIntezmenyId
    '
    EXEC sys.sp_executesql @sql,  N'
       @kovItemId int
      ,@pItemId int
      ,@pCurrentTanevId int
      ,@pNextTanevId int
      ,@pIntezmenyId int'
      ,@kovItemId = @kovItemId
      ,@pItemId = @pItemId
      ,@pCurrentTanevId = @pCurrentTanevId
      ,@pNextTanevId = @pNextTanevId
      ,@pIntezmenyId = @pIntezmenyId
  END
END
GO