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