97 lines
4.1 KiB
Transact-SQL
97 lines
4.1 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspFollowUpFeladatellatasiHely
|
|
GO
|
|
|
|
CREATE PROCEDURE uspFollowUpFeladatellatasiHely
|
|
@intezmenyId int
|
|
,@aktTanevId int
|
|
,@kovetkezoTanevId int
|
|
,@feladatellatasiHelyId int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
DECLARE
|
|
@kovetkezoFeladatellatasiHelyId int
|
|
,@kovOktatasiNevelesiFeladat int
|
|
,@kovMukodesiHelyId 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_FELADATELLATASIHELY')
|
|
|
|
|
|
SELECT @kovOktatasiNevelesiFeladat=okt.ID FROM T_OKTATASINEVELESIFELADAT_OSSZES okt
|
|
INNER JOIN T_FELADATELLATASIHELY_OSSZES fh ON fh.C_OKTATASINEVELESIFELADATTIPUS=okt.ELOZOTANEVIREKORDID AND fh.ID=@feladatellatasiHelyId AND fh.TOROLT='F'
|
|
WHERE C_ALTANEVID=@kovetkezoTanevId AND okt.TOROLT='F'
|
|
|
|
SELECT @kovMukodesiHelyId=mh.ID FROM T_FELADATELLATASIHELY_OSSZES fh
|
|
INNER JOIN T_MUKODESIHELY_OSSZES mh ON mh.ELOZOTANEVIREKORDID=fh.C_MUKODESIHELYID AND mh.TOROLT='F'
|
|
WHERE fh.ID=@feladatellatasiHelyId AND fh.TOROLT='F'
|
|
|
|
IF EXISTS(SELECT 1 FROM T_FELADATELLATASIHELY_OSSZES WHERE TOROLT='F' AND C_TANEVID=@kovetkezoTanevId AND C_MUKODESIHELYID=@kovMukodesiHelyId AND C_OKTATASINEVELESIFELADATTIPUS=@kovOktatasiNevelesiFeladat AND ISNULL(ELOZOTANEVIREKORDID,0)<>@feladatellatasiHelyId)
|
|
BEGIN
|
|
UPDATE T_FELADATELLATASIHELY_OSSZES SET ELOZOTANEVIREKORDID = NULL WHERE ELOZOTANEVIREKORDID=@feladatellatasiHelyId
|
|
UPDATE T_FELADATELLATASIHELY_OSSZES SET ELOZOTANEVIREKORDID = @feladatellatasiHelyId WHERE TOROLT='F' AND C_TANEVID=@kovetkezoTanevId AND C_MUKODESIHELYID=@kovMukodesiHelyId AND C_OKTATASINEVELESIFELADATTIPUS=@kovOktatasiNevelesiFeladat AND ISNULL(ELOZOTANEVIREKORDID,0)<>@feladatellatasiHelyId
|
|
END
|
|
|
|
SELECT @kovetkezoFeladatellatasiHelyId = ID FROM T_FELADATELLATASIHELY_OSSZES WHERE ELOZOTANEVIREKORDID = @feladatellatasiHelyId
|
|
IF (SELECT TOROLT FROM T_FELADATELLATASIHELY_OSSZES WHERE ID = @feladatellatasiHelyId) = 'T' OR (SELECT TOROLT FROM T_FELADATELLATASIHELY_OSSZES WHERE ID = @kovetkezoFeladatellatasiHelyId) = 'T' BEGIN
|
|
RETURN
|
|
END
|
|
|
|
IF (@kovetkezoFeladatellatasiHelyId 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_FELADATELLATASIHELY_OSSZES (
|
|
'+@insertColList+'
|
|
) SELECT
|
|
'+@selectColList+'
|
|
FROM T_FELADATELLATASIHELY_OSSZES s
|
|
INNER JOIN T_MUKODESIHELY_OSSZES mh ON mh.ELOZOTANEVIREKORDID=s.C_MUKODESIHELYID AND mh.TOROLT=''F''
|
|
INNER JOIN T_OKTATASINEVELESIFELADAT_OSSZES okt ON okt.ELOZOTANEVIREKORDID=s.C_OKTATASINEVELESIFELADATTIPUS AND okt.TOROLT=''F'' AND okt.C_ALTANEVID=@kovetkezoTanevId
|
|
WHERE s.TOROLT=''F'' AND s.ID=@feladatellatasiHelyId'
|
|
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_FELADATELLATASIHELY_OSSZES t
|
|
INNER JOIN T_FELADATELLATASIHELY_OSSZES s ON s.ID = @feladatellatasiHelyId AND s.ID=t.ELOZOTANEVIREKORDID
|
|
INNER JOIN T_OKTATASINEVELESIFELADAT_OSSZES okt ON okt.ELOZOTANEVIREKORDID=s.C_OKTATASINEVELESIFELADATTIPUS AND okt.TOROLT=''F'' AND okt.C_ALTANEVID=@kovetkezoTanevId'
|
|
END
|
|
|
|
--PRINT @sql
|
|
EXEC sp_executesql
|
|
@sql,N'
|
|
@intezmenyId int
|
|
,@aktTanevId int
|
|
,@kovetkezoTanevId int
|
|
,@feladatellatasiHelyId int'
|
|
,@intezmenyId = @intezmenyId
|
|
,@aktTanevId = @aktTanevId
|
|
,@kovetkezoTanevId = @kovetkezoTanevId
|
|
,@feladatellatasiHelyId = @feladatellatasiHelyId
|
|
|
|
END
|
|
GO
|