207 lines
7.0 KiB
Transact-SQL
207 lines
7.0 KiB
Transact-SQL
-- ===================================================================
|
|
-- Author: Telek Ákos
|
|
-- Created: 2018.06.21.
|
|
-- Description:
|
|
-- Dictionary elemek FollowUp-ja, insert csak a
|
|
-- 100000 feletti ID-kra, törölt elemekre nem fut le
|
|
-- ===================================================================
|
|
|
|
IF OBJECT_ID('sp_FollowUpDictionaryItemBase') IS NOT NULL BEGIN
|
|
DROP PROCEDURE sp_FollowUpDictionaryItemBase
|
|
END
|
|
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)
|
|
|
|
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
|
|
|
|
|
|
IF @isItemTorolt = 'T' OR @isKovItemTorolt = 'T' BEGIN
|
|
RETURN
|
|
END
|
|
|
|
IF @kovItemId IS NULL AND @pItemId < 100000 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
|
|
|