104 lines
3.7 KiB
Transact-SQL
104 lines
3.7 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspFollowUpCsengetesiRendOra
|
|
GO
|
|
|
|
CREATE PROCEDURE uspFollowUpCsengetesiRendOra
|
|
@intezmenyId int
|
|
,@aktTanevId int
|
|
,@kovetkezoTanevId int
|
|
,@csengetesiRendOraId int
|
|
,@csengetesiRendId int
|
|
,@isVisszaMenoleges bit
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
DECLARE
|
|
@oraSzam int
|
|
,@kovCsengrendOraId int
|
|
,@kovCsengRendId int
|
|
,@insertColList nvarchar(max)
|
|
,@selectColList nvarchar(max)
|
|
,@updateColList nvarchar(max)
|
|
,@sql nvarchar(max)
|
|
|
|
CREATE TABLE #columnMapping(
|
|
insertColName nvarchar(128) COLLATE DATABASE_DEFAULT
|
|
,selectColName nvarchar(128)
|
|
,updateColName nvarchar(128)
|
|
)
|
|
INSERT INTO #columnMapping (
|
|
insertColName
|
|
,selectColName
|
|
,updateColName
|
|
) SELECT
|
|
insertColName
|
|
,selectColName
|
|
,updateColName
|
|
FROM fnGetFollowUpColumnMapping (NULL,'T_CSENGETESIRENDORA')
|
|
|
|
SELECT @oraSzam = C_ORASZAM FROM T_CSENGETESIRENDORA_OSSZES WHERE ID = @csengetesiRendOraId
|
|
SELECT @kovCsengRendId = ID FROM T_CSENGETESIREND_OSSZES WHERE ELOZOTANEVIREKORDID=@csengetesiRendId
|
|
|
|
IF EXISTS (SELECT 1 FROM T_CSENGETESIRENDORA_OSSZES WHERE C_TANEVID=@kovetkezoTanevId AND TOROLT='F' AND C_ORASZAM=@oraSzam AND ISNULL(ELOZOTANEVIREKORDID,0)<>@csengetesiRendOraId AND C_CSENGETESIRENDID=@kovCsengRendId)
|
|
BEGIN
|
|
UPDATE T_CSENGETESIRENDORA_OSSZES SET ELOZOTANEVIREKORDID = NULL WHERE ELOZOTANEVIREKORDID=@csengetesiRendOraId
|
|
UPDATE T_CSENGETESIRENDORA_OSSZES SET ELOZOTANEVIREKORDID = @csengetesiRendOraId WHERE C_TANEVID=@kovetkezoTanevId AND TOROLT='F' AND C_ORASZAM=@oraSzam AND ISNULL(ELOZOTANEVIREKORDID,0)<>@csengetesiRendOraId AND C_CSENGETESIRENDID=@kovCsengRendId
|
|
END
|
|
|
|
SELECT @kovCsengrendOraId = ID FROM T_CSENGETESIRENDORA_OSSZES WHERE ELOZOTANEVIREKORDID=@csengetesiRendOraId
|
|
|
|
IF (SELECT TOROLT FROM T_CSENGETESIRENDORA_OSSZES WHERE ID = @csengetesiRendOraId)='T' OR (SELECT TOROLT FROM T_CSENGETESIRENDORA_OSSZES WHERE ID=@kovCsengrendOraId)='T'
|
|
BEGIN
|
|
RETURN
|
|
END
|
|
|
|
IF @kovCsengrendOraId IS NULL BEGIN
|
|
SELECT @insertColList = ISNULL(@insertColList+','+insertColname,insertColname) FROM #columnMapping
|
|
SELECT @selectColList = ISNULL(@selectColList+','+selectColName,selectColName) FROM #columnMapping
|
|
SET @sql = N'
|
|
INSERT INTO T_CSENGETESIRENDORA_OSSZES (
|
|
'+@insertColList+'
|
|
) SELECT
|
|
'+@selectColList+'
|
|
FROM T_CSENGETESIRENDORA_OSSZES s
|
|
INNER JOIN T_CSENGETESIREND_OSSZES csr ON csr.ELOZOTANEVIREKORDID=s.C_CSENGETESIRENDID AND csr.TOROLT=''F''
|
|
WHERE s.C_TANEVID = @aktTanevId AND s.ID = @csengetesiRendOraId AND s.TOROLT = ''F'''
|
|
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT
|
|
@updateColList = ISNULL(@updateColList+','+insertColName+' = '+updateColname,insertColName+' = '+updateColname)
|
|
FROM #columnMapping
|
|
WHERE updateColname IS NOT NULL
|
|
SET @sql = N'
|
|
UPDATE t SET
|
|
'+@updateColList+'
|
|
FROM T_CSENGETESIRENDORA_OSSZES t
|
|
INNER JOIN T_CSENGETESIRENDORA_OSSZES s ON s.ID = t.ELOZOTANEVIREKORDID AND s.C_TANEVID = @aktTanevId
|
|
WHERE t.TOROLT = ''F'' AND t.C_TANEVID = @kovetkezoTanevId AND t.ELOZOTANEVIREKORDID = @csengetesiRendOraId'
|
|
END
|
|
|
|
--PRINT @sql
|
|
EXEC sp_executesql
|
|
@sql,N'
|
|
@aktTanevId int
|
|
,@kovetkezoTanevId int
|
|
,@csengetesiRendOraId int
|
|
,@csengetesiRendId int
|
|
,@intezmenyId int'
|
|
,@aktTanevId = @aktTanevId
|
|
,@kovetkezoTanevId = @kovetkezoTanevId
|
|
,@csengetesiRendOraId = @csengetesiRendOraId
|
|
,@csengetesiRendId = @csengetesiRendId
|
|
,@intezmenyId = @intezmenyId
|
|
|
|
|
|
SELECT @kovCsengrendOraId = ID FROM T_CSENGETESIRENDORA_OSSZES WHERE ELOZOTANEVIREKORDID=@csengetesiRendOraId AND TOROLT='F'
|
|
|
|
IF @isVisszaMenoleges=1
|
|
BEGIN
|
|
EXEC sp_UpdateOrarendiOraVisszamenolegesen @kovCsengrendOraId
|
|
END
|
|
|
|
END
|
|
GO |