kreta/Kreta.DataAccess.Migrations/Scripts/Archive/20181004154041_OM_1443/sp_ImportTanulokGondviseloi.sql

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('[dbo].[sp_ImportTanulokGondviseloi]') IS NOT NULL 
BEGIN
	DROP PROCEDURE [dbo].[sp_ImportTanulokGondviseloi]
END
GO

-- =============================================
-- Description:	<Gondviselõk adatait importálja>
-- =============================================
CREATE PROCEDURE [dbo].[sp_ImportTanulokGondviseloi]
	 @pTanevId		INT
	 ,@pUserId		INT
	,@pIntezmenyId	INT
	,@GondviselokXML xml
AS
BEGIN
  BEGIN TRY
    SET NOCOUNT ON
    SET XACT_ABORT ON

	CREATE TABLE #GondviselokTemp (
		Id int IDENTITY(1,1), 
		TanuloId int,
		TanuloNeve nvarchar(250),
		OktatasiAzonosito nvarchar(50),
		TanuloOsztalya nvarchar(50),
		GondviseloId int,
		GondviseloNeve nvarchar(250),
		GondviseloRokonsagifoka int,
		GondviseloTelefonszama nvarchar(50),
		GondviseloEmailcime nvarchar(50)
    );

	CREATE TABLE #Gondviselok (
		Id int IDENTITY(1,1), 
		TanuloId int,
		TanuloNeve nvarchar(250),
		OktatasiAzonosito nvarchar(50),
		TanuloOsztalya nvarchar(50),
		GondviseloId int,
		GondviseloNeve nvarchar(250),
		GondviseloRokonsagifoka int,
		GondviseloTelefonszama nvarchar(50),
		GondviseloEmailcime nvarchar(50)
    );

    
    INSERT INTO #GondviselokTemp
    SELECT
		sor.value('(TanuloId)[1]', 'int') AS TanuloId,  
		sor.value('(TanuloNeve)[1]', 'nvarchar(200)') AS TanuloNeve,
		sor.value('(OktatasiAzonosito)[1]', 'nvarchar(11)') AS OktatasiAzonosito,
		sor.value('(TanuloOsztalya)[1]', 'nvarchar(50)') AS TanuloOsztalya,
		sor.value('(GondviseloId)[1]', 'int') AS GondviseloId, 
		sor.value('(GondviseloNeve)[1]', 'nvarchar(200)') AS GondviseloNeve,
		sor.value('(GondviseloRokonsagifoka)[1]', 'int') AS GondviseloRokonsagifoka, 
		sor.value('(GondviseloTelefonszama)[1]', 'nvarchar(100)') AS GondviseloTelefonszama,
		sor.value('(GondviseloEmailcime)[1]', 'nvarchar(200)') AS GondviseloEmailcime		 	
  	FROM @GondviselokXML.nodes('/GondviseloImport/GondviseloList/Gondviselo') AS sorok(sor)



  DECLARE @linkTable TABLE (
    insertedId int,
	tempId nvarchar(32)
  );

BEGIN TRANSACTION  


UPDATE g SET
           C_AKTIV ='T'
		   ,C_GONDVISELESKEZDETE			= NULL
           ,C_GONDVISELESVEGE				= NULL
           ,C_GYERMEKETEGYEDULNEVELO		= 'F'
           ,C_NEV							= gondviselok.GondviseloNeve
           ,C_NYUGDIJAS						= 'F'
           ,C_ROKONSAGFOKA					= gondviselok.GondviseloRokonsagifoka
           ,C_ISEMAILERTESITES				= 'F'
           ,C_TANULOID						= gondviselok.TanuloId
           ,C_INTEZMENYID					= @pIntezmenyId
           ,C_TANEVID						= @pTanevId
           ,TOROLT							= 'F'
           ,SERIAL							= SERIAL+1
           ,MODIFIER						= NULL
           ,CREATOR							= @pUserId
		   ,LASTCHANGED						= GETDATE()

FROM T_GONDVISELO g
INNER JOIN #GondviselokTemp AS gondviselok ON gondviselok.GondviseloId = g.Id


	 INSERT INTO #Gondviselok  (
	  TanuloId 
	  ,TanuloNeve 
	  ,OktatasiAzonosito 
	  ,TanuloOsztalya 
	  ,GondviseloId 
	  ,GondviseloNeve 
	  ,GondviseloRokonsagifoka 
	  ,GondviseloTelefonszama 
	  ,GondviseloEmailcime )
	 SELECT* FROM (SELECT DISTINCT
	   TanuloId 
	  ,TanuloNeve 
	  ,OktatasiAzonosito 
	  ,TanuloOsztalya 
	  ,GondviseloId 
	  ,GondviseloNeve 
	  ,GondviseloRokonsagifoka 
	  ,GondviseloTelefonszama 
	  ,GondviseloEmailcime 
	  FROM #GondviselokTemp) AS g;



MERGE  T_GONDVISELO_OSSZES trg
USING  (SELECT * FROM #Gondviselok WHERE GondviseloId = 0) AS src
ON 1 = 2
WHEN NOT MATCHED THEN  
INSERT (C_AKTIV
		   ,C_GONDVISELESKEZDETE		
           ,C_GONDVISELESVEGE			
           ,C_GYERMEKETEGYEDULNEVELO	
           ,C_NEV						
           ,C_NYUGDIJAS					
           ,C_ROKONSAGFOKA				
           ,C_ISEMAILERTESITES
		   ,C_ISCSOKKENTETTGONDVISELO
           ,C_TANULOID					
           ,C_INTEZMENYID				
           ,C_TANEVID					
           ,TOROLT						
           ,SERIAL						
           ,MODIFIER					
           ,CREATOR						
		   ,CREATED
		   ,LASTCHANGED)
		   VALUES 
		   (
		   'T'
		   ,NULL
		   ,NULL
		   ,'F'
		   ,src.GondviseloNeve
		   ,'F'
		   ,src.GondviseloRokonsagifoka
		   ,'F'
		   ,'F'
		   ,src.TanuloId
		   ,@pIntezmenyId
		   ,@pTanevId
		   ,'F'
		   ,0
		   ,@pUserid
		   ,@pUserid
		   ,GETDATE()
		   ,GETDATE()
		   )
OUTPUT INSERTED.ID, src.Id 
INTO @linkTable(insertedId,tempId);

UPDATE g SET g.GondviseloId = link.insertedId FROM #Gondviselok g
INNER JOIN @linkTable  link ON link.tempId = g.Id


      MERGE T_EMAIL_OSSZES trg
        USING (
		  SELECT * FROM #Gondviselok WHERE  GondviseloEmailcime IS NOT NULL) src ON src.GondviseloId = trg.C_GONDVISELOID AND trg.C_TANEVID = @pTanevId AND trg.TOROLT = 'F'
        WHEN MATCHED THEN 
			  UPDATE SET
				trg.C_EMAILTIPUSA		= 1024  --NA
				,trg.C_EMAILCIM			= src.GondviseloEmailcime
				,trg.C_ALAPERTELMEZETT	=	'T'
				,trg.C_GONDVISELOID		= src.GondviseloId
				,trg.C_FELHASZNALOID	= src.TanuloId
				,trg.C_INTEZMENYID		= @pIntezmenyId
				,trg.C_TANEVID			= @pTanevId
				,trg.TOROLT				= 'F'
				,trg.SERIAL				= trg.SERIAL+1
				,trg.LASTCHANGED		= GETDATE()
				,trg.CREATED			= GETDATE()
				,trg.MODIFIER			= @pUserId
				,trg.CREATOR			= @pUserId				
        WHEN NOT MATCHED THEN
			  INSERT (
				 C_EMAILTIPUSA		 
				,C_EMAILCIM			 
				,C_ALAPERTELMEZETT	 
				,C_GONDVISELOID		
				,C_FELHASZNALOID	 
				,C_INTEZMENYID		 
				,C_TANEVID			 
				,TOROLT				 
				,SERIAL				 
				,LASTCHANGED		 
				,CREATED			 
				,MODIFIER			 
				,CREATOR			 
				)
				VALUES
				(
				1024 --NA
				,src.GondviseloEmailcime
				,'T'
				,src.GondviseloID
				,src.TanuloId
				,@pIntezmenyId
				,@pTanevId
				,'F'
				,0
				,GETDATE()
				,GETDATE()
				,@pUserId
				,@pUserId			
				);					 


      MERGE T_TELEFON_OSSZES trg
        USING (
		  SELECT * FROM #Gondviselok WHERE  GondviseloTelefonszama IS NOT NULL) src ON src.GondviseloId = trg.C_GONDVISELOID AND trg.C_TANEVID = @pTanevId AND trg.TOROLT = 'F'
        WHEN MATCHED THEN 
			  UPDATE SET
				 trg.C_TELEFONTIPUSA	= 1019  --Ismeretlen
				,trg.C_TELEFONSZAM		= src.GondviseloTelefonszama
				,trg.C_ALAPERTELMEZETT	=	'T'
				,trg.C_GONDVISELOID		= src.GondviseloId
				,trg.C_FELHASZNALOID	= src.TanuloId
				,trg.C_INTEZMENYID		= @pIntezmenyId
				,trg.C_TANEVID			= @pTanevId
				,trg.TOROLT				= 'F'
				,trg.SERIAL				= trg.SERIAL+1
				,trg.LASTCHANGED		= GETDATE()
				,trg.CREATED			= GETDATE()
				,trg.MODIFIER			= @pUserId
				,trg.CREATOR			= @pUserId				
        WHEN NOT MATCHED THEN
			  INSERT (
				 C_TELEFONTIPUSA		 
				,C_TELEFONSZAM			 
				,C_ALAPERTELMEZETT	 
				,C_GONDVISELOID		
				,C_FELHASZNALOID	 
				,C_INTEZMENYID		 
				,C_TANEVID			 
				,TOROLT				 
				,SERIAL				 
				,LASTCHANGED		 
				,CREATED			 
				,MODIFIER			 
				,CREATOR			 
				)
				VALUES
				(
				1019  --Ismeretlen
				,src.GondviseloTelefonszama
				,'T'
				,src.GondviseloId
				,src.TanuloId
				,@pIntezmenyId
				,@pTanevId
				,'F'
				,0
				,GETDATE()
				,GETDATE()
				,@pUserId
				,@pUserId			
				);					 
    COMMIT TRANSACTION

	SELECT GondviseloId FROM #Gondviselok

  END TRY
  BEGIN CATCH
    IF @@TRANCOUNT > 0 AND XACT_STATE() <> 0 
      ROLLBACK TRANSACTION;
    THROW    
  END CATCH
END

GO