91 lines
2.8 KiB
Transact-SQL
91 lines
2.8 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS sp_FollowUpAlkalmazottVegzettseg
|
|
GO
|
|
|
|
CREATE PROCEDURE sp_FollowUpAlkalmazottVegzettseg
|
|
@intezmenyId int,
|
|
@aktTanevId int,
|
|
@kovetkezoTanevId int,
|
|
@vegzettsegId int
|
|
|
|
AS
|
|
BEGIN
|
|
|
|
DECLARE @kovVegzettsegId int=(SELECT ID FROM T_VEGZETTSEG_OSSZES WHERE ELOZOTANEVIREKORDID=@vegzettsegId)
|
|
|
|
IF (SELECT TOROLT FROM T_VEGZETTSEG_OSSZES WHERE ID = @vegzettsegId)='T' OR (SELECT TOROLT FROM T_VEGZETTSEG_OSSZES WHERE ELOZOTANEVIREKORDID = @vegzettsegId)='T' BEGIN
|
|
RETURN
|
|
END
|
|
|
|
IF @kovVegzettsegId IS NULL BEGIN
|
|
INSERT INTO T_VEGZETTSEG_OSSZES(
|
|
C_EGYEB
|
|
,C_VEGZETTSEGTIPUSID
|
|
,C_TANARID
|
|
,C_INTEZMENYID
|
|
,C_TANEVID
|
|
,SERIAL
|
|
,LASTCHANGED
|
|
,CREATED
|
|
,MODIFIER
|
|
,CREATOR
|
|
,ELOZOTANEVIREKORDID
|
|
) SELECT
|
|
C_EGYEB
|
|
,C_VEGZETTSEGTIPUSID
|
|
,fh.ID
|
|
,v.C_INTEZMENYID
|
|
,@kovetkezoTanevId
|
|
,0
|
|
,v.LASTCHANGED
|
|
,v.LASTCHANGED
|
|
,v.MODIFIER
|
|
,v.MODIFIER
|
|
,v.ID
|
|
FROM T_VEGZETTSEG_OSSZES v
|
|
INNER JOIN T_FELHASZNALO_OSSZES fh ON fh.ELOZOTANEVIREKORDID=v.C_TANARID AND fh.TOROLT='F'
|
|
WHERE v.ID=@vegzettsegId
|
|
|
|
INSERT INTO T_KEPESITES_VEGZETTSEG (
|
|
C_KEPESITESID
|
|
,C_VEGZETTSEGID
|
|
)SELECT
|
|
k.ID
|
|
,vKov.ID
|
|
FROM T_VEGZETTSEG_OSSZES v
|
|
INNER JOIN T_KEPESITES_VEGZETTSEG kv ON kv.C_VEGZETTSEGID=v.ID
|
|
INNER JOIN T_KEPESITES_OSSZES k ON k.ELOZOTANEVIREKORDID=kv.C_KEPESITESID AND k.TOROLT='F'
|
|
INNER JOIN T_VEGZETTSEG_OSSZES vKov ON vKov.ELOZOTANEVIREKORDID=v.ID
|
|
WHERE v.ID=@vegzettsegId
|
|
END
|
|
ELSE BEGIN
|
|
UPDATE vKov SET
|
|
C_EGYEB = vOld.C_EGYEB
|
|
,C_VEGZETTSEGTIPUSID = vOld.C_VEGZETTSEGTIPUSID
|
|
,LASTCHANGED = vOld.LASTCHANGED
|
|
,MODIFIER = vOld.MODIFIER
|
|
,SERIAL += 1
|
|
FROM T_VEGZETTSEG_OSSZES vOld
|
|
INNER JOIN T_VEGZETTSEG_OSSZES vKov ON vKov.ELOZOTANEVIREKORDID = vOld.ID AND vOld.ID=@vegzettsegId
|
|
|
|
DELETE kv
|
|
FROM T_KEPESITES_VEGZETTSEG kv
|
|
INNER JOIN T_KEPESITES_OSSZES kKov ON kKov.ID=kv.C_KEPESITESID AND kKov.TOROLT='F'
|
|
INNER JOIN T_KEPESITES_OSSZES kOld ON kOld.ID=kKov.ELOZOTANEVIREKORDID AND kOld.TOROLT='F'
|
|
LEFT JOIN T_KEPESITES_VEGZETTSEG kvOld ON kvOld.C_VEGZETTSEGID=@vegzettsegId AND kvOld.C_KEPESITESID=kOld.ID
|
|
WHERE kv.C_VEGZETTSEGID=@kovVegzettsegId AND kvOld.C_KEPESITESID IS NULL
|
|
|
|
INSERT INTO T_KEPESITES_VEGZETTSEG (
|
|
C_KEPESITESID
|
|
,C_VEGZETTSEGID
|
|
) SELECT
|
|
k.ID
|
|
,vKov.ID
|
|
FROM T_VEGZETTSEG_OSSZES v
|
|
INNER JOIN T_KEPESITES_VEGZETTSEG kv ON kv.C_VEGZETTSEGID=v.ID
|
|
INNER JOIN T_KEPESITES_OSSZES k ON k.ELOZOTANEVIREKORDID=kv.C_KEPESITESID AND k.TOROLT='F'
|
|
INNER JOIN T_VEGZETTSEG_OSSZES vKov ON vKov.ELOZOTANEVIREKORDID=v.ID
|
|
WHERE v.ID=@vegzettsegId AND NOT EXISTS (SELECT 1 FROM T_KEPESITES_VEGZETTSEG WHERE C_VEGZETTSEGID=@kovVegzettsegId AND C_KEPESITESID=k.ID)
|
|
END
|
|
END
|
|
GO
|