﻿IF OBJECT_ID('stage.sp_MrgPedagogusIKTAdatszolgaltatas') IS NOT NULL BEGIN
  DROP PROCEDURE stage.sp_MrgPedagogusIKTAdatszolgaltatas
END
GO

CREATE PROCEDURE stage.sp_MrgPedagogusIKTAdatszolgaltatas
AS
BEGIN
  MERGE stage.T_PEDAGOGUSIKT_STAGE AS trg
    USING (
      SELECT
         PedagogusIKT.C_INTEZMENYID AS C_INTEZMENYID
        ,tanev.C_NEV AS C_TANEVNEV
        ,PedagogusIKT.ID AS C_PEDAGOGUSIKTID
        ,PedagogusIKT.C_ELSODLEGESIKTESZKOZE AS C_ELSODLEGESIKTESZKOZE
        ,PedagogusIKT.C_IKTESZKOZHASZNALATMODJA AS C_IKTESZKOZHASZNALATMODJA
        ,PedagogusIKT.C_IKTKOMPETENCIASZINTJE AS C_IKTKOMPETENCIASZINTJE
        ,PedagogusIKT.C_ALKALMAZOTTID AS C_ALKALMAZOTTID   
      FROM dbo.T_PEDAGOGUSIKTADATSZOLGALTATAS PedagogusIKT
        INNER JOIN dbo.T_TANEV tanev ON tanev.ID = PedagogusIKT.C_TANEVID
      WHERE PedagogusIKT.TOROLT = 'F'
    ) src ON (trg.C_INTEZMENYID = src.C_INTEZMENYID AND trg.C_PEDAGOGUSIKTID = src.C_PEDAGOGUSIKTID) 
    WHEN MATCHED AND 
         (trg.C_ELSODLEGESIKTESZKOZE <> src.C_ELSODLEGESIKTESZKOZE OR (trg.C_ELSODLEGESIKTESZKOZE IS NULL AND src.C_ELSODLEGESIKTESZKOZE IS NOT NULL) OR (trg.C_ELSODLEGESIKTESZKOZE IS NOT NULL AND src.C_ELSODLEGESIKTESZKOZE IS NULL))
      OR (trg.C_IKTESZKOZHASZNALATMODJA <> src.C_IKTESZKOZHASZNALATMODJA OR (trg.C_IKTESZKOZHASZNALATMODJA IS NULL AND src.C_IKTESZKOZHASZNALATMODJA IS NOT NULL) OR (trg.C_IKTESZKOZHASZNALATMODJA IS NOT NULL AND src.C_IKTESZKOZHASZNALATMODJA IS NULL))
      OR (trg.C_IKTKOMPETENCIASZINTJE <> src.C_IKTKOMPETENCIASZINTJE OR (trg.C_IKTKOMPETENCIASZINTJE IS NULL AND src.C_IKTKOMPETENCIASZINTJE IS NOT NULL) OR (trg.C_IKTKOMPETENCIASZINTJE IS NOT NULL AND src.C_IKTKOMPETENCIASZINTJE IS NULL))
      OR (trg.C_ALKALMAZOTTID <> src.C_ALKALMAZOTTID OR (trg.C_ALKALMAZOTTID IS NULL AND src.C_ALKALMAZOTTID IS NOT NULL) OR (trg.C_ALKALMAZOTTID IS NOT NULL AND src.C_ALKALMAZOTTID IS NULL))
    THEN
      UPDATE SET
         trg.C_ELSODLEGESIKTESZKOZE = src.C_ELSODLEGESIKTESZKOZE
        ,trg.C_IKTESZKOZHASZNALATMODJA = src.C_IKTESZKOZHASZNALATMODJA
        ,trg.C_IKTKOMPETENCIASZINTJE = src.C_IKTKOMPETENCIASZINTJE
        ,trg.C_ALKALMAZOTTID = src.C_ALKALMAZOTTID
        ,trg.LASTCHANGED = GETDATE()
    WHEN NOT MATCHED BY TARGET THEN
      INSERT (
         C_INTEZMENYID 
        ,C_TANEVNEV
        ,C_PEDAGOGUSIKTID
        ,C_ELSODLEGESIKTESZKOZE
        ,C_IKTESZKOZHASZNALATMODJA
        ,C_IKTKOMPETENCIASZINTJE
        ,C_ALKALMAZOTTID
        ,TOROLT
        ,LASTCHANGED
        ,CREATED
      ) VALUES (
         src.C_INTEZMENYID 
        ,src.C_TANEVNEV
        ,src.C_PEDAGOGUSIKTID
        ,src.C_ELSODLEGESIKTESZKOZE
        ,src.C_IKTESZKOZHASZNALATMODJA
        ,src.C_IKTKOMPETENCIASZINTJE
        ,src.C_ALKALMAZOTTID
        ,'F'
        ,GETDATE()
        ,GETDATE()
      )
    WHEN NOT MATCHED BY SOURCE AND trg.TOROLT = 'F' THEN
      UPDATE SET 
        trg.LASTCHANGED = GETDATE(), 
        trg.TOROLT = 'T';
END
GO
