kreta/Kreta.DataAccess.Migrations/Scripts/Archive/20200304134948_DB_2122/uspFollowUpAlkalmazottVegzettsegKK.sql

DROP PROCEDURE IF EXISTS uspFollowUpAlkalmazottVegzettsegKK
GO

CREATE PROCEDURE uspFollowUpAlkalmazottVegzettsegKK
   @intezmenyId int
  ,@aktTanevId int
  ,@kovetkezoTanevId int
  ,@alkalmazottId int
 
AS
BEGIN
  SET NOCOUNT ON;
	DECLARE 
     @kovAlkalmazottId int
    ,@partialSql nvarchar(max) = N''
    ,@finalSql nvarchar(max) 
    ,@tableMapperSql nvarchar(max)
    ,@colListSql nvarchar(max) = N''

  CREATE TABLE #ColumnMappingKKVegzettseg(
     insertColName nvarchar(128)
    ,selectColName nvarchar(128)
    ,updateColName nvarchar(128)
    ,tableName nvarchar(128)
  )

  SELECT @kovAlkalmazottId = ID 
  FROM T_FELHASZNALO_OSSZES fh
  WHERE fh.ELOZOTANEVIREKORDID = @alkalmazottId AND TOROLT='F'
  
  IF @kovAlkalmazottId IS NULL BEGIN
    RETURN
  END
  
  CREATE TABLE #tables (tableName nvarchar(128))
  CREATE TABLE #colLists (selectColList nvarchar(max),updateColList nvarchar(max),insertColList nvarchar(max),tableName nvarchar(128))

  INSERT INTO #tables VALUES
     ('T_KKTANITOVEZGETTSEG')
    ,('T_KKGYOGYPEDVEGZETTSEG')
    ,('T_KKTANARVEGZETTSEG')
    ,('T_KKTANTARGYKATEGORIA')
    ,('T_KKAMIVEGZETTSEG')
    ,('T_KKELEKTROAKUZENE')
    ,('T_KKKLASSZIKUSZENE')
    ,('T_KKNEPZENE')
    ,('T_KKJAZZZENE')
    ,('T_KKTERULET')
  
  SELECT @tableMapperSql = ISNULL(
  @tableMapperSql+N'
  UNION ALL
  SELECT
     insertColName
    ,selectColName
    ,updateColName
    ,'''+tableName+'''
  FROM fnGetFollowUpColumnMapping (NULL,'''+tableName+''')',
  'SELECT
     insertColName
    ,selectColName
    ,updateColName
    ,'''+tableName+'''
  FROM fnGetFollowUpColumnMapping (NULL,'''+tableName+''')')
  FROM #tables

  INSERT INTO #ColumnMappingKKVegzettseg(
     insertColName
    ,selectColName
    ,updateColName
    ,tableName
  )
  EXEC sp_executesql @tableMapperSql
   
  SELECT @colListSql += N'
    DECLARE @insert'+tableName+'colList nvarchar(max)
    DECLARE @select'+tableName+'colList nvarchar(max)
    DECLARE @update'+tableName+'colList nvarchar(max)
    SELECT @insert'+tableName+'colList = ISNULL(@insert'+tableName+'colList+'',''+insertColname,insertColname) FROM #ColumnMappingKKVegzettseg WHERE tableName = '''+tableName+'''
    SELECT @select'+tableName+'colList = ISNULL(@select'+tableName+'colList+'',''+selectColname,selectColname) FROM #ColumnMappingKKVegzettseg WHERE tableName = '''+tableName+'''
    INSERT INTO #ColumnMappingKKVegzettseg (insertColName,updateColName,tableName) VALUES
      (''TOROLT'',''s.TOROLT'','''+tableName+''')
    SELECT @update'+tableName+'ColList = ISNULL(@update'+tableName+'ColList+'',''+insertColName+'' = ''+updateColName,insertColName+'' = ''+updateColName) 
    FROM #ColumnMappingKKVegzettseg
    WHERE updateColName IS NOT NULL AND tableName = '''+tableName+'''
    SELECT @select'+tableName+'colList,@update'+tableName+'colList,@insert'+tableName+'colList,'''+tableName+''''
  FROM #tables

  INSERT INTO #colLists
  exec sp_executesql @colListSql

  SELECT @partialSql += N'
    INSERT INTO '+tableName+'_OSSZES (
      '+insertColList+'
    ) SELECT 
      '+selectColList+'
    FROM '+tableName+'_OSSZES s
    WHERE C_ALKALMAZOTTID = @alkalmazottId 
      AND TOROLT=''F''
      AND NOT EXISTS (SELECT 1 FROM '+tableName+'_OSSZES t WHERE t.ELOZOTANEVIREKORDID = s.ID)

    UPDATE t SET 
		  '+updateColList+'
	  FROM '+tableName+'_OSSZES s
	  	INNER JOIN '+tableName+'_OSSZES t ON t.ELOZOTANEVIREKORDID = s.ID
	  WHERE s.C_ALKALMAZOTTID=@alkalmazottId'
  FROM #colLists 
  WHERE tableName IN('T_KKTANITOVEZGETTSEG','T_KKGYOGYPEDVEGZETTSEG','T_KKTANARVEGZETTSEG','T_KKAMIVEGZETTSEG')
  --PRINT @partialSql
  SET @finalSql = @partialSql
  SET @partialSql = N''

  SELECT @partialSql += N'
    INSERT INTO '+tableName+'_OSSZES (
      '+insertColList+'
    ) SELECT 
      '+selectColList+'
    FROM '+tableName+'_OSSZES s
      INNER JOIN T_KKAMIVEGZETTSEG_OSSZES tvx ON s.C_KKAMIVEGZETTSEGID = tvx.ID AND tvx.C_ALKALMAZOTTID = @alkalmazottId AND tvx.TOROLT = ''F''
      INNER JOIN T_KKAMIVEGZETTSEG_OSSZES av ON av.ELOZOTANEVIREKORDID = tvx.ID AND av.C_ALKALMAZOTTID = @kovAlkalmazottId  AND av.TOROLT = ''F''
    WHERE s.TOROLT=''F''
      AND NOT EXISTS (SELECT 1 FROM '+tableName+'_OSSZES t WHERE t.ELOZOTANEVIREKORDID = s.ID)

    UPDATE t SET 
		  '+updateColList+'
	  FROM '+tableName+'_OSSZES s
	  	INNER JOIN '+tableName+'_OSSZES t ON t.ELOZOTANEVIREKORDID = s.ID
	    INNER JOIN T_KKAMIVEGZETTSEG_OSSZES v ON v.ID=s.C_KKAMIVEGZETTSEGID AND v.C_ALKALMAZOTTID=@alkalmazottId'
  FROM #colLists 
  WHERE tableName IN('T_KKELEKTROAKUZENE','T_KKKLASSZIKUSZENE','T_KKJAZZZENE','T_KKNEPZENE','T_KKTERULET')

  SET @finalSql += @partialSql
  --PRINT @partialSql
  SET @partialSql = N''

  SELECT @partialSql = N'
  INSERT INTO T_KKTANTARGYKATEGORIA_OSSZES (
    '+insertColList+'
  ) SELECT 
    '+selectColList+'
  FROM T_KKTANTARGYKATEGORIA_OSSZES s
    INNER JOIN T_KKTANARVEGZETTSEG_OSSZES tvx ON s.C_KKTANARVEGZETTSEGID = tvx.ID AND tvx.C_ALKALMAZOTTID = @alkalmazottId AND tvx.TOROLT = ''F''
    INNER JOIN T_KKTANARVEGZETTSEG_OSSZES av ON av.ELOZOTANEVIREKORDID = tvx.ID AND av.C_ALKALMAZOTTID = @kovAlkalmazottId  AND av.TOROLT = ''F''
  WHERE s.TOROLT = ''F''
    AND NOT EXISTS (SELECT 1 FROM T_KKTANTARGYKATEGORIA_OSSZES WHERE ELOZOTANEVIREKORDID = s.ID)
   
  UPDATE t SET 
    '+updateColList+'
  FROM T_KKTANARVEGZETTSEG_OSSZES x
    INNER JOIN T_KKTANTARGYKATEGORIA_OSSZES s ON s.C_KKTANARVEGZETTSEGID = x.ID
	  INNER JOIN T_KKTANTARGYKATEGORIA_OSSZES t ON t.ELOZOTANEVIREKORDID = s.ID
  WHERE x.C_ALKALMAZOTTID = @alkalmazottId'
  FROM #colLists 
  WHERE tableName = 'T_KKTANTARGYKATEGORIA'
  
  SET @finalSql += @partialSql
  --PRINT @partialSql

  exec sp_executesql
    @finalSql,N'
    @intezmenyId int
   ,@aktTanevId int
   ,@kovetkezoTanevId int
   ,@alkalmazottId int
   ,@kovAlkalmazottId int'
   ,@intezmenyId = @intezmenyId
   ,@aktTanevId = @aktTanevId
   ,@kovetkezoTanevId = @kovetkezoTanevId
   ,@alkalmazottId = @alkalmazottId
   ,@kovAlkalmazottId = @kovAlkalmazottId


END
GO