143 lines
4.0 KiB
Transact-SQL
143 lines
4.0 KiB
Transact-SQL
GO
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
|
||
IF OBJECT_ID('sp_AktivTanevValtasa') IS NOT NULL
|
||
BEGIN
|
||
DROP PROCEDURE sp_AktivTanevValtasa
|
||
END
|
||
GO
|
||
|
||
CREATE PROCEDURE sp_AktivTanevValtasa
|
||
@pintezmenyId INT
|
||
,@paktTanevId INT
|
||
,@pkovTanevId INT
|
||
,@pfelhasznaloId INT
|
||
AS
|
||
BEGIN
|
||
SET NOCOUNT ON;
|
||
|
||
INSERT INTO T_FELHASZNALOBELEPES_OSSZES (
|
||
C_KOTELEZOVALTOZTATNI
|
||
,C_MEGHIUSULTBELEPESEK
|
||
,C_UTOLSOBELEPES
|
||
,C_JELSZO
|
||
,C_JELSZOVALTOZTATASIDEJE
|
||
,C_SO
|
||
,C_BEJELENTKEZESINEV
|
||
,C_NEPTUNNAPLOJELSZO
|
||
,C_FELHASZNALOID
|
||
,C_GONDVISELOID
|
||
,C_INTEZMENYID
|
||
,C_TANEVID
|
||
,C_GUID
|
||
,TOROLT
|
||
,SERIAL
|
||
,LASTCHANGED
|
||
,CREATED
|
||
,MODIFIER
|
||
,CREATOR
|
||
,NNID
|
||
) SELECT
|
||
'T' AS C_KOTELEZOVALTOZTATNI
|
||
,0 AS C_MEGHIUSULTBELEPESEK
|
||
,NULL AS C_UTOLSOBELEPES
|
||
,C_JELSZO AS C_JELSZO
|
||
,NULL AS C_JELSZOVALTOZTATASIDEJE
|
||
,C_SO AS C_SO
|
||
,C_BEJELENTKEZESINEV AS C_BEJELENTKEZESINEV
|
||
,C_NEPTUNNAPLOJELSZO AS C_NEPTUNNAPLOJELSZO
|
||
,tmp.NewUserId AS C_FELHASZNALOID
|
||
,NULL AS C_GONDVISELOID
|
||
,@pintezmenyId AS C_INTEZMENYID
|
||
,@pkovTanevId AS C_TANEVID
|
||
,f.C_GUID AS C_GUID
|
||
,TOROLT AS TOROLT
|
||
,0 AS SERIAL
|
||
,GETDATE() AS LASTCHANGED
|
||
,GETDATE() AS CREATED
|
||
,@pfelhasznaloId AS MODIFIER
|
||
,@pfelhasznaloId AS CREATOR
|
||
,NULL AS NNID
|
||
FROM T_FELHASZNALOBELEPES_OSSZES f
|
||
INNER JOIN (
|
||
SELECT
|
||
a.ID AS OldUserId
|
||
,b.ID AS NewUserId
|
||
FROM T_FELHASZNALO_OSSZES a
|
||
INNER JOIN T_FELHASZNALO_OSSZES b ON b.C_SZULETESINEV = a.C_SZULETESINEV
|
||
AND b.C_ANYJANEVE = a.C_ANYJANEVE
|
||
AND b.C_SZULETESIDATUM = a.C_SZULETESIDATUM
|
||
AND b.C_SZULETESIHELY = a.C_SZULETESIHELY
|
||
INNER JOIN T_ALKALMAZOTT_OSSZES alk ON alk.ID = a.ID
|
||
INNER JOIN T_ALKALMAZOTT_OSSZES alk0 ON alk0.ID = b.ID
|
||
WHERE a.C_TANEVID = @paktTanevId
|
||
AND b.C_TANEVID = @pkovTanevId
|
||
AND a.TOROLT = 'F'
|
||
AND b.TOROLT = 'F'
|
||
AND 0 = (SELECT COUNT(1) FROM T_FELHASZNALOBELEPES_OSSZES f2 WHERE f2.C_FELHASZNALOID = b.ID AND f2.TOROLT = 'F')) tmp ON tmp.OldUserId = f.C_FELHASZNALOID
|
||
|
||
INSERT INTO T_FELHASZNALO_SZEREPKOR (C_FELHASZNALOID, C_SZEREPKORID)
|
||
SELECT DISTINCT
|
||
newF.ID
|
||
,newSz.ID
|
||
FROM T_FELHASZNALO_OSSZES newF
|
||
INNER JOIN (
|
||
SELECT
|
||
f.C_SZULETESINEV
|
||
,f.C_ANYJANEVE
|
||
,f.C_SZULETESIDATUM
|
||
,f.C_SZULETESIHELY
|
||
,sz.C_SZEREPKORTIPUS
|
||
FROM T_FELHASZNALO_OSSZES f
|
||
INNER JOIN T_ALKALMAZOTT_OSSZES a ON a.ID = f.ID
|
||
INNER JOIN T_FELHASZNALO_SZEREPKOR fsz ON fsz.C_FELHASZNALOID = f.ID
|
||
INNER JOIN T_SZEREPKOR_OSSZES sz ON sz.ID = fsz.C_SZEREPKORID
|
||
WHERE f.C_UTONEV <> 'Adminisztr<EFBFBD>tor' AND f.C_UTONEV <> 'Fenntart<EFBFBD>'
|
||
AND f.C_TANEVID = @paktTanevId
|
||
AND f.C_INTEZMENYID = @pintezmenyId
|
||
AND f.TOROLT = 'F') oldF ON newF.C_SZULETESINEV = oldF.C_SZULETESINEV
|
||
AND newF.C_ANYJANEVE = oldF.C_ANYJANEVE
|
||
AND newF.C_SZULETESIDATUM = oldF.C_SZULETESIDATUM
|
||
AND newF.C_SZULETESIHELY = oldF.C_SZULETESIHELY
|
||
INNER JOIN T_SZEREPKOR_OSSZES newSz ON newSz.C_SZEREPKORTIPUS = oldF.C_SZEREPKORTIPUS
|
||
AND newSz.C_TANEVID = @pkovTanevId
|
||
AND newSz.C_INTEZMENYID = @pintezmenyId
|
||
WHERE newF.C_INTEZMENYID = @pintezmenyId
|
||
AND newF.C_TANEVID = @pkovTanevId
|
||
|
||
UPDATE a
|
||
SET
|
||
a.C_JELSZO = b.C_JELSZO
|
||
,a.C_SO = b.C_SO
|
||
FROM T_FELHASZNALOBELEPES_OSSZES a
|
||
INNER JOIN T_FELHASZNALOBELEPES_OSSZES b ON b.C_BEJELENTKEZESINEV = a.C_BEJELENTKEZESINEV
|
||
WHERE a.C_BEJELENTKEZESINEV = 'admin'
|
||
AND a.C_TANEVID = @pkovTanevId
|
||
AND b.C_TANEVID = @paktTanevId
|
||
|
||
UPDATE T_FELHASZNALOBELEPES_OSSZES
|
||
SET TOROLT = 'T'
|
||
WHERE C_TANEVID = @paktTanevId
|
||
AND C_FELHASZNALOID <> @pfelhasznaloId;
|
||
|
||
UPDATE T_TANEV_OSSZES
|
||
SET C_AKTIV = 'F'
|
||
WHERE ID = @paktTanevId
|
||
AND C_INTEZMENYID = @pintezmenyId
|
||
AND TOROLT = 'F'
|
||
|
||
UPDATE T_TANEV_OSSZES
|
||
SET
|
||
C_AKTIV = 'T'
|
||
,C_KOVETKEZO = 'F'
|
||
WHERE ID = @pkovTanevId
|
||
AND C_INTEZMENYID = @pintezmenyId
|
||
AND TOROLT = 'F';
|
||
|
||
EXEC sp_SetSystemSettingsDefaultData @pintezmenyId, @pkovTanevId;
|
||
END
|
||
GO
|