DROP PROCEDURE IF EXISTS sp_ImportTermek
GO

CREATE PROCEDURE sp_ImportTermek
  @xml xml,
  @userID int,
  @intezmenyID int,
  @tanevID int,
  @mukodesiHelyId int
AS
BEGIN

  BEGIN TRY
    SET NOCOUNT ON
    SET XACT_ABORT ON
  
    DECLARE @tbl TABLE (
      C_JELLEG int,
      C_KAPACITAS int,
      C_NEV nvarchar(50),
      C_TERULET int,
      C_TOBBORATLEHETTARTANI char(1),
      C_VIZSGAKAPACITAS int,
      C_MUKODESIHELYID int,
      C_TEREMFELELOSID int    
    )
    
    INSERT INTO @tbl
      SELECT 
      sor.value('number((JellegId)[1])', 'int') AS C_JELLEG,
      sor.value('xs:decimal((Kapacitas)[1])', 'int') AS C_KAPACITAS,
      sor.value('(TeremNev)[1]', 'nvarchar(50)') AS C_NEV,
      sor.value('number((Terulet)[1])', 'int') AS C_TERULET,
      sor.value('(tobbora)[1]', 'char(1)') AS C_TOBBORATLEHETTARTANI,
      sor.value('number((vizsgakapacitas)[1])', 'int') AS C_VIZSGAKAPACITAS,
      @mukodesiHelyId AS C_MUKODESIHELYID,
      sor.value('number((teremfelelos)[1])', 'int') AS C_TEREMFELELOSID
  	FROM @xml.nodes('/TeremImport/TeremLista/Terem') as sorok(sor)
    
    BEGIN TRANSACTION
      UPDATE t
        SET 
		 t.C_JELLEG = IsNull(s.C_JELLEG, t.C_JELLEG)
		 ,t.C_KAPACITAS = IsNull(s.C_KAPACITAS, t.C_KAPACITAS)
		 ,t.C_NEV = IsNull(s.C_NEV, t.C_NEV)
		 ,t.C_TERULET = IsNull(s.C_TERULET, t.C_TERULET)
		 ,t.C_TOBBORATLEHETTARTANI = IsNull(s.C_TOBBORATLEHETTARTANI, ISNULL(t.C_TOBBORATLEHETTARTANI,'F'))
		 ,t.C_VIZSGAKAPACITAS = IsNull(s.C_VIZSGAKAPACITAS, t.C_VIZSGAKAPACITAS)
		 ,t.C_MUKODESIHELYID = IsNull(s.C_MUKODESIHELYID, t.C_MUKODESIHELYID)
		 ,t.C_TEREMFELELOSID = IsNull(s.C_TEREMFELELOSID, t.C_TEREMFELELOSID)
         ,t.SERIAL = t.SERIAL + 1
         ,t.LASTCHANGED = GETDATE()
         ,t.MODIFIER = @userID
      FROM T_TEREM t
      INNER JOIN @tbl s ON s.C_NEV = t.C_NEV AND s.C_MUKODESIHELYID = t.C_MUKODESIHELYID AND t.C_INTEZMENYID = t.C_INTEZMENYID
        
      INSERT INTO T_TEREM (
         C_AKTIV
        ,C_JELLEG
        ,C_KAPACITAS
        ,C_NEV
        ,C_TERULET
        ,C_TOBBORATLEHETTARTANI
        ,C_VIZSGAKAPACITAS
        ,C_MUKODESIHELYID
        ,C_TEREMFELELOSID
		,C_INTEZMENYID
		,C_TANEVID
        ,MODIFIER
        ,CREATOR
      ) SELECT
        'T' AS C_ACTIVE
        ,C_JELLEG AS C_JELLEG
        ,C_KAPACITAS AS C_KAPACITAS
        ,C_NEV AS C_NEV
        ,C_TERULET AS C_TERULET
        ,ISNULL(C_TOBBORATLEHETTARTANI,'F') AS C_TOBBORATLEHETTARTANI
        ,C_VIZSGAKAPACITAS AS C_VIZSGAKAPACITAS
        ,C_MUKODESIHELYID AS C_MUKODESIHELYID
        ,C_TEREMFELELOSID AS C_TEREMFELELOSID
		,@intezmenyID AS C_INTEZMENYID
		,@tanevID AS C_TANEVID
        ,NULL AS MODIFIER
        ,@userID AS CREATOR
    	FROM @tbl t
      WHERE NOT EXISTS (SELECT 1 FROM T_TEREM WHERE C_NEV = t.C_NEV AND C_MUKODESIHELYID = t.C_MUKODESIHELYID)
    COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
    IF @@TRANCOUNT > 0 AND XACT_STATE() <> 0 
      ROLLBACK TRANSACTION;
    THROW
  END CATCH
END