113 lines
2.8 KiB
Transact-SQL
113 lines
2.8 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
|
|
,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
|
|
,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 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
|
|
|
|
|
|
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
|