113 lines
No EOL
3.3 KiB
Transact-SQL
113 lines
No EOL
3.3 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS [sp_HozzaferesGeneralas]
|
|
GO
|
|
|
|
CREATE PROCEDURE [sp_HozzaferesGeneralas]
|
|
@xml xml
|
|
,@tanevID int
|
|
,@intezmenyID int
|
|
,@userId int
|
|
AS
|
|
BEGIN
|
|
BEGIN TRY
|
|
SET NOCOUNT ON
|
|
SET XACT_ABORT ON
|
|
|
|
DECLARE @tbl TABLE (
|
|
C_ID int
|
|
,C_USERNAME nvarchar(50)
|
|
,C_PASSWORD nvarchar(50)
|
|
,C_GONDVISELOID int
|
|
,C_NEMGENERALHATO char(1)
|
|
,C_SO nvarchar(50))
|
|
INSERT INTO @tbl
|
|
SELECT
|
|
sor.value('number((Id)[1])', 'int') AS C_ID
|
|
,sor.value('(UserName)[1]', 'nvarchar(50)') AS C_USERNAME
|
|
,sor.value('(Password)[1]', 'nvarchar(50)') AS C_PASSWORD
|
|
,sor.value('number((GondviseloId)[1])', 'int') AS C_GONDVISELOID
|
|
,sor.value('(NemGeneralhato)[1]', 'char(1)') AS C_NEMGENERALHATO
|
|
,sor.value('(Salt)[1]', 'nvarchar(50)') AS C_SO
|
|
FROM @xml.nodes('/BelepesiAdatok/BelepesiAdatokLista/BelepesiAdat') as sorok(sor)
|
|
|
|
BEGIN TRANSACTION
|
|
|
|
UPDATE @tbl SET
|
|
C_GONDVISELOID = NULL
|
|
WHERE C_GONDVISELOID < 0
|
|
|
|
UPDATE fb SET
|
|
fb.C_BEJELENTKEZESINEV = tbl.C_USERNAME
|
|
,fb.C_JELSZO = tbl.C_PASSWORD
|
|
,fb.C_SO = tbl.C_SO
|
|
,fb.C_JELSZOVALTOZTATASIDEJE = GETDATE()
|
|
,fb.C_KOTELEZOVALTOZTATNI = 'T'
|
|
,fb.SERIAL += 1
|
|
,fb.LASTCHANGED = GETDATE()
|
|
,fb.MODIFIER = @userId
|
|
FROM T_FELHASZNALOBELEPES fb
|
|
INNER JOIN @tbl tbl ON tbl.C_ID = fb.C_FELHASZNALOID AND tbl.C_GONDVISELOID IS NULL
|
|
WHERE TOROLT = 'F'
|
|
AND fb.C_GONDVISELOID IS NULL
|
|
|
|
UPDATE fb SET
|
|
fb.C_BEJELENTKEZESINEV = tbl.C_USERNAME
|
|
,fb.C_JELSZO = tbl.C_PASSWORD
|
|
,fb.C_SO = tbl.C_SO
|
|
,fb.C_JELSZOVALTOZTATASIDEJE = GETDATE()
|
|
,fb.C_KOTELEZOVALTOZTATNI = 'T'
|
|
,fb.SERIAL += 1
|
|
,fb.LASTCHANGED = GETDATE()
|
|
,fb.MODIFIER = @userId
|
|
FROM T_FELHASZNALOBELEPES fb
|
|
INNER JOIN @tbl tbl ON tbl.C_GONDVISELOID = fb.C_GONDVISELOID AND tbl.C_GONDVISELOID IS NOT NULL
|
|
WHERE TOROLT = 'F'
|
|
AND fb.C_GONDVISELOID IS NOT NULL
|
|
|
|
INSERT INTO [T_FELHASZNALOBELEPES]
|
|
([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]
|
|
,[MODIFIER]
|
|
,[CREATOR])
|
|
SELECT
|
|
'T' AS C_KOTELEZOVALTOZTATNI
|
|
,NULL AS C_MEGHIUSULTBELEPESEK
|
|
,NULL AS C_UTOLSOBELEPES
|
|
,t.C_PASSWORD AS C_JELSZO
|
|
,NULL AS C_JELSZOVALTOZTATASIDEJE
|
|
,t.C_SO AS C_SO
|
|
,t.C_USERNAME C_BEJELENTKEZESINEV
|
|
,NULL AS C_NEPTUNNAPLOJELSZO
|
|
,t.C_ID AS C_FELHASZNALOID
|
|
,IIF(t.C_GONDVISELOID=-1, NULL, (t.C_GONDVISELOID)) AS C_GONDVISELOID
|
|
,@intezmenyID AS C_INTEZMENYID
|
|
,@tanevID AS C_TANEVID
|
|
,'F' AS TOROLT
|
|
,NULL AS MODIFIER
|
|
,@userId AS CREATOR
|
|
FROM @tbl t
|
|
WHERE t.C_NEMGENERALHATO = 'F'
|
|
AND NOT EXISTS (SELECT 1
|
|
FROM T_FELHASZNALOBELEPES fb
|
|
WHERE fb.C_FELHASZNALOID = t.C_ID
|
|
AND (fb.C_GONDVISELOID = t.C_GONDVISELOID OR (fb.C_GONDVISELOID IS NULL AND t.C_GONDVISELOID IS NULL)))
|
|
|
|
COMMIT TRANSACTION
|
|
END TRY
|
|
BEGIN CATCH
|
|
IF @@TRANCOUNT > 0 AND XACT_STATE() <> 0
|
|
ROLLBACK TRANSACTION;
|
|
THROW
|
|
END CATCH
|
|
END
|
|
GO |