﻿SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

GO

IF OBJECT_ID('[sp_HozzaferesGeneralas]') IS NOT NULL 
BEGIN
  DROP PROCEDURE [sp_HozzaferesGeneralas]
END

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 fb
      SET
         fb.C_BEJELENTKEZESINEV = tbl.C_USERNAME
        ,fb.C_JELSZO = tbl.C_PASSWORD
        ,fb.C_SO = tbl.C_SO
		    ,fb.C_JELSZOVALTOZTATASIDEJE = GETDATE()
      FROM T_FELHASZNALOBELEPES fb
        INNER JOIN @tbl tbl ON tbl.C_ID = fb.C_FELHASZNALOID
      WHERE TOROLT = 'F'
        AND tbl.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()
      FROM T_FELHASZNALOBELEPES fb
        INNER JOIN @tbl tbl ON tbl.C_GONDVISELOID = fb.C_GONDVISELOID
      WHERE TOROLT = 'F'
        AND tbl.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]
        ,[SERIAL]
        ,[LASTCHANGED]
        ,[CREATED]
        ,[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
        ,0 AS SERIAL
        ,NULL LASTCHANGED
        ,GETDATE() AS CREATED
        ,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
