﻿IF OBJECT_ID('[dbo].[sp_KIRImportTanarok]', 'P') IS NOT NULL BEGIN
  DROP PROCEDURE [dbo].[sp_KIRImportTanarok]
END
GO

CREATE PROCEDURE [dbo].[sp_KIRImportTanarok]
  -- Add the parameters for the stored procedure here
   @TanarokXML xml
  ,@FeladatellatasiHelyId int
  ,@intezmenyID int
  ,@tanevID int
  ,@userId int
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
  SET XACT_ABORT ON;
  -- XML paraméter értelmezése
  DECLARE @Tanarok TABLE (
     Id int
    ,OktatasiAzonosito varchar(15)
    ,KretaKod varchar(6)
    ,VezetekNev nvarchar(50)  
    ,UtoNev nvarchar(50)
    ,Elotag nvarchar(10)
    ,NevSorrend char(1)
    ,AnyjaNeveElotag nvarchar(10) 
    ,AnyjaNeveVezetekNev nvarchar(50)
    ,AnyjaNeveKeresztNev nvarchar(50)  
    ,AnyjaNeveNevSorrend nvarchar(50)
    ,SzuletesiDatum datetime
    ,SzuletesiHely nvarchar(255)
    ,SzuletesiOrszag int  
	,SzuletesiNeveElotag nvarchar(10) 
    ,SzuletesiNeveVezetekNev nvarchar(50)
    ,SzuletesiNeveKeresztNev nvarchar(50)  
    ,SzuletesiNeveNevSorrend nvarchar(50)
    ,Allampolgarsag int
    ,Nem int
    ,EmailCim nvarchar(255)
    ,Telefonszam nvarchar(100)
    ,AllandoLakcimIranyitoSzam nvarchar(10)  
    ,AllandoLakcimTelepules nvarchar(255)
    ,AllandoLakcimKozteruletNev nvarchar(255)
    ,AllandoLakcimKozteruletJelleg int
    ,AllandoLakcimHazszam nvarchar(20)
    ,TartozkodasiCimTelepules nvarchar(255)
    ,TartozkodasiCimKozteruletNev nvarchar(255)  
    ,TartozkodasiCimKozteruletJelleg int
    ,TartozkodasiCimHazszam nvarchar(20)  
    ,TartozkodasiCimIranyitoszam nvarchar(20)
    ,KeresesiNev nvarchar(255) 
    ,NyomtatasiNev nvarchar(255)
    ,OMAzonosito nvarchar(15) 
    ,Foallas char(1)
    ,KotelezoOraszam nvarchar(15)
	,FoglalkoztatasTipus int
    ,MunkakorTipusa nvarchar(15)
    ,MunkaviszonyTipusa nvarchar(15)	
    ,JogviszonyKezdete datetime
    ,FelhasznaloId int -- Ide kerül be a tényleges ID, amit a DB-ben is van/lesz
    ,isUjTanar bit -- létezik-e már a rendszereben
  )
  
  INSERT INTO @Tanarok (
     Id
    ,OktatasiAzonosito
    ,KretaKod
    ,VezetekNev
    ,UtoNev
    ,Elotag
    ,NevSorrend
    ,AnyjaNeveElotag
    ,AnyjaNeveVezetekNev
    ,AnyjaNeveKeresztNev
    ,AnyjaNeveNevSorrend
    ,SzuletesiDatum
    ,SzuletesiHely
    ,SzuletesiOrszag
    ,SzuletesiNeveElotag
    ,SzuletesiNeveVezetekNev
    ,SzuletesiNeveKeresztNev 
    ,SzuletesiNeveNevSorrend
    ,Allampolgarsag
    ,Nem
    ,EmailCim
    ,Telefonszam
    ,AllandoLakcimIranyitoSzam
    ,AllandoLakcimTelepules
    ,AllandoLakcimKozteruletNev
    ,AllandoLakcimKozteruletJelleg
    ,AllandoLakcimHazszam
    ,TartozkodasiCimTelepules
    ,TartozkodasiCimKozteruletNev
    ,TartozkodasiCimKozteruletJelleg
    ,TartozkodasiCimHazszam
    ,TartozkodasiCimIranyitoszam
    ,KeresesiNev
    ,NyomtatasiNev
    ,OMAzonosito
    ,Foallas
    ,KotelezoOraszam
	,FoglalkoztatasTipus    
	,MunkakorTipusa
    ,MunkaviszonyTipusa	
	,JogviszonyKezdete
    ,isUjTanar
  ) SELECT  
    Id = Tanar.value('(ID)[1]', 'int'),
    OktatasiAzonosito = Tanar.value('(OktatasiAzonosito)[1]', 'varchar(15)'),
    KretaKod = Tanar.value('(KretaKod)[1]', 'varchar(6)'),
    VezetekNev =  Tanar.value('(ViseltNevVezetekNev)[1]', 'nvarchar(50)'),  
    UtoNev =  Tanar.value('(ViseltNevKeresztNev)[1]', 'nvarchar(50)'),
    Elotag =  Tanar.value('(ViseltNevElotag)[1]', 'nvarchar(10)'),  
    NevSorrend =  Tanar.value('(ViseltNevNevSorrend)[1]', 'char(1)'),
    AnyjaNeveElotag =  Tanar.value('(AnyjaNeveElotag)[1]', 'nvarchar(10)'), 
    AnyjaNeveVezetekNev =  Tanar.value('(AnyjaNeveVezetekNev)[1]', 'nvarchar(50)'),
    AnyjaNeveKeresztNev =  Tanar.value('(AnyjaNeveKeresztNev)[1]', 'nvarchar(50)'),  
    AnyjaNeveNevSorrend =  Tanar.value('(AnyjaNeveNevSorrend)[1]', 'nvarchar(50)'),
    SzuletesiDatum =  Tanar.value('(SzuletesiDatum)[1]', 'date'),  
    SzuletesiHely =  Tanar.value('(SzuletesiHely)[1]', 'nvarchar(255)'),
    SzuletesiOrszag =  Tanar.value('(SzuletesiOrszag)[1]', 'int'),  
	SzuletesiNeveElotag = Tanar.value('(SzuletesiNevElotag)[1]', 'nvarchar(10)'), 
    SzuletesiNeveVezetekNev = Tanar.value('(SzuletesiNevVezetekNev)[1]', 'nvarchar(50)'), 
    SzuletesiNeveKeresztNev = Tanar.value('(SzuletesiNevKeresztNev)[1]', 'nvarchar(50)'), 
    SzuletesiNeveNevSorrend = Tanar.value('(SzuletesiNevNevSorrend)[1]', 'nvarchar(50)'), 
    Allampolgarsag =  Tanar.value('(Allampolgarsag)[1]', 'int'),
    Nem =  Tanar.value('(Nem)[1]', 'int'),  
    EmailCim =  Tanar.value('(EmailCim)[1]', 'nvarchar(255)'),
    Telefonszam =  Tanar.value('(Telefonszam)[1]', 'nvarchar(100)'),
    AllandoLakcimIranyitoSzam =  Tanar.value('(AllandoLakcimIranyitoSzam)[1]', 'nvarchar(10)'),  
    AllandoLakcimTelepules =  Tanar.value('(AllandoLakcimTelepules)[1]', 'nvarchar(255)'),
    AllandoLakcimKozteruletNev =  Tanar.value('(AllandoLakcimKozteruletNev)[1]', 'nvarchar(255)'),  
    AllandoLakcimKozteruletJelleg =  Tanar.value('number((AllandoLakcimKozteruletJelleg)[1])', 'int'),
    AllandoLakcimHazszam =  Tanar.value('(AllandoLakcimHazszam)[1]', 'nvarchar(20)'),  
    TartozkodasiCimTelepules =  Tanar.value('(TartozkodasiCimTelepules)[1]', 'nvarchar(255)'),
    TartozkodasiCimKozteruletNev =  Tanar.value('(TartozkodasiCimKozteruletNev)[1]', 'nvarchar(255)'),  
    TartozkodasiCimKozteruletJelleg =  Tanar.value('number((TartozkodasiCimKozteruletJelleg)[1])', 'int'),
    TartozkodasiCimHazszam =  Tanar.value('(TartozkodasiCimHazszam)[1]', 'nvarchar(20)'),  
    TartozkodasiCimIranyitoszam =  Tanar.value('(TartozkodasiCimIranyitoSzam)[1]', 'nvarchar(20)'),
    KeresesiNev =  Tanar.value('(KeresesiNev)[1]', 'nvarchar(255)'), 
    NyomtatasiNev =  ltrim(Tanar.value('(NyomtatasiNev)[1]', 'nvarchar(255)')),
    OMAzonosito =  Tanar.value('(OMAzonosito)[1]', 'nvarchar(15)'), 
    Foallas =  Tanar.value('(Foallas)[1]', 'char(1)'),
    KotelezoOraszam =  Tanar.value('(KotelezoOraszam)[1]', 'nvarchar(15)'), 
	FoglalkoztatasTipus =  Tanar.value('number((FoglalkoztatasTipus)[1])', 'int'),
    MunkakorTipusa =  Tanar.value('(MunkakorTipusa)[1]', 'nvarchar(15)'),
    MunkaviszonyTipusa =  Tanar.value('(MunkaviszonyTipusa)[1]', 'nvarchar(15)'),
	JogviszonyKezdete =  Tanar.value('(JogviszonyKezdete)[1]', 'date'),	
    1 AS isUjTanar
  FROM @TanarokXML.nodes('/KIRTanarok/ImportaltTanarok/Tanar') as Tanarok(Tanar)
  
  
  DECLARE @LinkTable TABLE (felHasznaloId int, kretaKod varchar(6), action nvarchar(20))
  
  BEGIN TRY
    BEGIN TRANSACTION
  
    MERGE T_FELHASZNALO_OSSZES fh
    USING (
      SELECT * FROM @Tanarok
    ) t ON t.KretaKod = fh.C_KRETAKOD AND fh.C_INTEZMENYID = @intezmenyid AND fh.C_TANEVID = @tanevID
    WHEN MATCHED THEN
      UPDATE SET
        fh.C_NEME = t.Nem,
        fh.C_SZULETESIDATUM = t.SzuletesiDatum,
        fh.C_SZULETESINEV = t.SzuletesiNeveVezetekNev + ' ' + t.SzuletesiNeveKeresztNev,
        fh.C_ANYJANEVE = t.AnyjaNeveVezetekNev + ' ' + t.AnyjaNeveKeresztNev,
        fh.C_SZULETESIHELY = t.SzuletesiHely,
        fh.C_ALLAMPOLGARSAGA = t.Allampolgarsag,
        fh.C_KERESESINEV = t.KeresesiNev,
        fh.C_SZULETESIORSZAG = t.SzuletesiOrszag,
        fh.C_SZULETESINEVSORREND = t.NevSorrend,
        fh.C_ANYJANEVESORREND = t.AnyjaNeveNevSorrend,
        fh.C_ALAPERTELMEZETTLCID = 1038,
        fh.C_SZULETESINEVELVALASZTO = LEN(t.SzuletesiNeveVezetekNev),
        fh.C_ANYJANEVEELVALASZTO = LEN(t.AnyjaNeveVezetekNev),
        fh.C_NYOMTATASINEV = t.NyomtatasiNev,
        fh.C_UTONEV = t.UtoNev,
        fh.C_VEZETEKNEV = t.VezetekNev,
        fh.C_ELOTAG = t.Elotag,
        fh.C_NEVSORREND = t.NevSorrend,
        fh.C_NYILVANTARTASKEZDETE = GETDATE(),
        fh.C_OMKOD = t.OMAzonosito,
        fh.C_KRETAKOD = t.KretaKod,
        fh.C_OKTATASIAZONOSITO = t.OktatasiAzonosito,
        fh.TOROLT = 'F',
        fh.SERIAL += 1,
        fh.LASTCHANGED = GETDATE(),
        fh.MODIFIER = @userId 
    WHEN NOT MATCHED THEN
      INSERT (
        [C_NEME],
        [C_SZULETESIDATUM],
        [C_SZULETESINEV],
        [C_ANYJANEVE],
        [C_SZULETESIHELY],
        [C_ALLAMPOLGARSAGA],
        [C_KERESESINEV],
        [C_SZULETESIORSZAG],
        [C_SZULETESINEVSORREND],
        [C_ANYJANEVESORREND],
        [C_ALAPERTELMEZETTLCID],
        [C_SZULETESINEVELVALASZTO],
        [C_ANYJANEVEELVALASZTO],
        [C_NYOMTATASINEV],
        [C_UTONEV],
        [C_VEZETEKNEV],
        [C_ELOTAG],
        [C_NEVSORREND],
        [C_NYILVANTARTASKEZDETE],
        [C_OKTATASIAZONOSITO],
        [C_OMKOD],
        [C_KRETAKOD], 
        [C_INTEZMENYID], 
        [C_TANEVID],
        [MODIFIER],
        [CREATOR]
      ) VALUES (
        t.Nem, 
        t.SzuletesiDatum, 
        t.SzuletesiNeveVezetekNev + ' ' + t.SzuletesiNeveKeresztNev,
        t.AnyjaNeveVezetekNev + ' ' + t.AnyjaNeveKeresztNev, 
        t.SzuletesiHely,  
        t.Allampolgarsag, 
        t.KeresesiNev, 
        t.SzuletesiOrszag, 
		--t.SzuletesiNev,
        t.SzuletesiNeveNevSorrend, 
        t.AnyjaNeveNevSorrend, 
        1038, 
        LEN(t.SzuletesiNeveVezetekNev), 
        LEN(t.AnyjaNeveVezetekNev), 
        t.NyomtatasiNev,
        t.UtoNev, 
        t.VezetekNev, 
        t.Elotag, 
        t.NevSorrend,
        GETDATE(), 
        t.OktatasiAzonosito, 
        t.OMAzonosito,
        t.KretaKod, 
        @intezmenyID, 
        @tanevID,
        NULL, 
        @userId
      )
    OUTPUT INSERTED.ID, t.KretaKod, $action
    INTO @LinkTable(felhasznaloId, kretaKod, action);
    
    UPDATE t
    SET t.FelhasznaloId = x.felhasznaloId
      ,t.isUjTanar = 0
    FROM @Tanarok t
      INNER JOIN @LinkTable x ON x.KretaKod = t.KretaKod
    
    MERGE T_ALKALMAZOTT_OSSZES a
    USING (
      SELECT * FROM @Tanarok
    ) t ON a.ID = t.FelhasznaloId AND a.C_ALINTEZMENYID = @intezmenyid AND a.C_ALTANEVID = @tanevID
    WHEN MATCHED THEN
      UPDATE SET
        a.C_FOALLAS = t.Foallas,
        a.TOROLT = 'F',
        a.SERIAL += 1,
        a.LASTCHANGED = GETDATE(),
        a.MODIFIER = @userId        
    WHEN NOT MATCHED THEN
      INSERT (
        [ID], 
        [C_FOALLAS],
        [C_ALINTEZMENYID], 
        [C_ALTANEVID],
        [MODIFIER],
        [CREATOR]
      ) VALUES (
        t.FelhasznaloId, 
        t.Foallas, 
        @intezmenyID,
        @tanevID, 
        NULL, 
        @userId
      );
    MERGE T_MUNKAUGYIADATOK_OSSZES m
    USING (
      SELECT * FROM @Tanarok
    ) t ON m.C_ALKALMAZOTTID = t.FelhasznaloId AND m.C_INTEZMENYID = @intezmenyid AND m.C_TANEVID = @tanevID
    WHEN MATCHED THEN
      UPDATE SET
        m.C_ALKALMAZASKEZDETE = t.JogviszonyKezdete,
        m.C_KOTELEZOORASZAM = t.KotelezoOraszam,
		m.C_FOGLALKOZTATASTIPUS = t.FoglalkoztatasTipus,
		m.C_MUNKAKORTIPUSA = IIF (t.MunkakorTipusa = 548,  m.C_MUNKAKORTIPUSA, t.MunkakorTipusa ),	 				
		m.C_MUNKAVISZONYTIPUSA = IIF (t.MunkaviszonyTipusa = 643,  m.C_MUNKAVISZONYTIPUSA, t.MunkaviszonyTipusa ),
        m.C_FELADATELLATASIHELYID = @FeladatellatasiHelyId,
        m.TOROLT = 'F',
        m.SERIAL +=  1,
        m.LASTCHANGED = GETDATE(),
        m.MODIFIER = @userId        
    WHEN NOT MATCHED THEN
      INSERT (
        C_ALKALMAZASKEZDETE,
        C_KOTELEZOORASZAM,
		C_FOGLALKOZTATASTIPUS,
        C_MUNKAKORTIPUSA,
        C_MUNKAVISZONYTIPUSA,		
        C_ALKALMAZOTTID,
        C_FELADATELLATASIHELYID,
        C_INTEZMENYID,
        C_TANEVID,
        MODIFIER,
        CREATOR
      ) VALUES (
        t.JogviszonyKezdete,
        t.KotelezoOraszam,
		t.FoglalkoztatasTipus,
        t.MunkakorTipusa,
        t.MunkaviszonyTipusa,		
        t.FelhasznaloId,
        @FeladatellatasiHelyId,
        @intezmenyID,
        @tanevID,
        NULL, 
        @userId
      );
      
      
      MERGE T_CIM_OSSZES c
      USING (
        SELECT * FROM @Tanarok
      ) t ON c.C_FELHASZNALOID = t.FelhasznaloId AND c.C_INTEZMENYID = @intezmenyid AND c.C_TANEVID = @tanevID AND c.C_CIMTIPUSA = 908
      WHEN MATCHED THEN
        UPDATE SET
          c.[C_CIMTIPUSA] = 908,
          c.[C_ORSZAG] = 765,
          c.[C_IRANYITOSZAM] = t.TartozkodasiCimIranyitoszam,
          c.[C_VAROS] = t.TartozkodasiCimTelepules,
          c.[C_KOZTERULET] = t.TartozkodasiCimKozteruletNev,
          c.[C_KOZTERULETJELLEGE] = t.TartozkodasiCimKozteruletJelleg,
          c.[C_HAZSZAM] = t.TartozkodasiCimHazszam,
          c.[TOROLT] = 'F',
          c.SERIAL += 1,
          c.LASTCHANGED = GETDATE(),
          c.MODIFIER = @userId       
      WHEN NOT MATCHED THEN
        INSERT (
          [C_CIMTIPUSA],
          [C_ORSZAG],
          [C_IRANYITOSZAM],
          [C_VAROS],
          [C_KOZTERULET],
          [C_KOZTERULETJELLEGE],
          [C_HAZSZAM],
          [C_FELHASZNALOID],
          [C_INTEZMENYID],
          [C_TANEVID],
          [MODIFIER],
          [CREATOR]
        ) VALUES (
          908, 
          765, 
          t.TartozkodasiCimIranyitoszam,
          t.TartozkodasiCimTelepules,
          t.TartozkodasiCimKozteruletNev,
          t.TartozkodasiCimKozteruletJelleg,
          t.TartozkodasiCimHazszam,
          t.FelhasznaloId,
          @intezmenyID,
          @tanevID,
          NULL,
          @userId
        );   
        
      MERGE T_CIM_OSSZES c
      USING (
        SELECT * FROM @Tanarok
      ) t ON c.C_FELHASZNALOID = t.FelhasznaloId AND c.C_INTEZMENYID = @intezmenyid AND c.C_TANEVID = @tanevID AND c.C_CIMTIPUSA = 907
      WHEN MATCHED THEN
        UPDATE SET
          c.[C_CIMTIPUSA] = 907, 
          c.[C_ORSZAG] = 765,
          c.[C_IRANYITOSZAM] = t.AllandoLakcimIranyitoSzam,
          c.[C_VAROS] = t.AllandoLakcimTelepules,
          c.[C_KOZTERULET] = t.AllandoLakcimKozteruletNev,
          c.[C_KOZTERULETJELLEGE] = t.AllandoLakcimKozteruletJelleg,
          c.[C_HAZSZAM] = t.AllandoLakcimHazszam,
          c.[TOROLT] = 'F',  
          c.SERIAL += 1,
          c.LASTCHANGED = GETDATE(),
          c.MODIFIER = @userId      
      WHEN NOT MATCHED THEN
        INSERT (
          [C_CIMTIPUSA],
          [C_ORSZAG],
          [C_IRANYITOSZAM],
          [C_VAROS],
          [C_KOZTERULET],
          [C_KOZTERULETJELLEGE],
          [C_HAZSZAM],
          [C_FELHASZNALOID],
          [C_INTEZMENYID],
          [C_TANEVID],
          [MODIFIER],
          [CREATOR]
        ) VALUES (
          907,
          765,
          t.AllandoLakcimIranyitoSzam, 
          t.AllandoLakcimTelepules,
          t.AllandoLakcimKozteruletNev,
          t.AllandoLakcimKozteruletJelleg, 
          t.AllandoLakcimHazszam,
          t.FelhasznaloId,
          @intezmenyID,
          @tanevID,
          NULL,
          @userId
        );   
      MERGE T_EMAIL_OSSZES e
      USING (
        SELECT * FROM @Tanarok
      ) t ON e.C_FELHASZNALOID = t.FelhasznaloId AND e.C_INTEZMENYID = @intezmenyid AND e.C_TANEVID = @tanevID
      WHEN MATCHED THEN
        UPDATE SET
          e.C_EMAILCIM = t.EmailCim,
          e.SERIAL += 1,
          e.LASTCHANGED = GETDATE(),
          e.MODIFIER = @userId    
      WHEN NOT MATCHED THEN
        INSERT (
          [C_EMAILTIPUSA],
          [C_EMAILCIM],
          [C_ALAPERTELMEZETT],
          [C_GONDVISELOID],
          [C_FELHASZNALOID],
          [C_INTEZMENYID],
          [C_TANEVID],
          [MODIFIER],
          [CREATOR]
        ) VALUES (
          1025, 
          t.EmailCim, 
          'T', 
          null,
          t.FelhasznaloId,
          @intezmenyID,
          @tanevID,
          NULL,
          @userId
        ); 
      MERGE T_TELEFON_OSSZES tf
      USING (
        SELECT * FROM @Tanarok WHERE Telefonszam IS NOT NULL
      ) t ON tf.C_FELHASZNALOID = t.FelhasznaloId AND tf.C_INTEZMENYID = @intezmenyid AND tf.C_TANEVID = @tanevID
      WHEN MATCHED THEN
        UPDATE SET
          tf.[C_TELEFONSZAM] = t.Telefonszam,
          tf.SERIAL += 1,
          tf.LASTCHANGED = GETDATE(),
          tf.MODIFIER = @userId   
      WHEN NOT MATCHED THEN
        INSERT (
          [C_TELEFONTIPUSA], 
          [C_TELEFONSZAM],
          [C_ALAPERTELMEZETT],
          [C_GONDVISELOID],
          [C_FELHASZNALOID], 
          [C_INTEZMENYID], 
          [C_TANEVID],
          [MODIFIER],
          [CREATOR]
        ) VALUES (
          1019, 
          t.Telefonszam, 
          'T', 
          null,
          t.FelhasznaloId,
          @intezmenyID, 
          @tanevID, 
          NULL, 
          @userId
        ); 
      
      
      DECLARE @szerepkorId int;
      
      SELECT @szerepkorId = ID 
      FROM T_SZEREPKOR_OSSZES 
      WHERE C_SZEREPKORTIPUS = '1760' 
        AND C_INTEZMENYID = @intezmenyID AND C_TANEVID = @tanevID
        
      INSERT INTO T_FELHASZNALO_SZEREPKOR (
         C_FELHASZNALOID
        ,C_SZEREPKORID
      ) SELECT 
         t.FelhasznaloId
        ,@szerepkorId 
      FROM @Tanarok t 
      WHERE NOT EXISTS (SELECT 1 FROM T_FELHASZNALO_SZEREPKOR fsz WHERE fsz.C_FELHASZNALOID = FelhasznaloId AND fsz.C_SZEREPKORID = @szerepkorId)
   
    COMMIT TRANSACTION
--    ROLLBACK TRANSACTION
  END TRY      
  BEGIN CATCH
    IF @@TRANCOUNT > 0 AND XACT_STATE() <> 0 
      ROLLBACK TRANSACTION;
    THROW 
  END CATCH
END