kreta/Kreta.DataAccess.Migrations/Scripts/Archive/20200304134948_DB_2122/uspFollowUpAlkalmazott.sql

DROP PROCEDURE IF EXISTS uspFollowUpAlkalmazott
GO
 
CREATE PROCEDURE uspFollowUpAlkalmazott
   @intezmenyId int
  ,@aktTanevId int
  ,@kovetkezoTanevId int
  ,@alkalmazottId int
AS
BEGIN
  SET NOCOUNT ON;
  BEGIN --DECLARE,CREATE
  DECLARE 
     @ujAlkalmazottId int
    ,@ExcludeInsertColumns nvarchar(max)
    ,@insertColList nvarchar(max)
    ,@selectColList nvarchar(max)
    ,@updateColList nvarchar(max)
    ,@partialSql nvarchar(max)
    ,@fullSql nvarchar(max)
  
  CREATE TABLE #excludedColumns (
     colName nvarchar(128) COLLATE DATABASE_DEFAULT
    ,excludeType nvarchar(10) COLLATE DATABASE_DEFAULT
  )
  CREATE TABLE #columnMappings (
     insertColName nvarchar(128) COLLATE DATABASE_DEFAULT
    ,selectColName nvarchar(128) COLLATE DATABASE_DEFAULT
    ,updateColname nvarchar(128) COLLATE DATABASE_DEFAULT
    ,tableName nvarchar(128) COLLATE DATABASE_DEFAULT
  )
  END
  BEGIN --default return
    DECLARE @eloTag nvarchar(40)=(SELECT C_ELOTAG FROM T_FELHASZNALO WHERE ID = @alkalmazottId)
    IF @eloTag LIKE '%[[]HO%' 
      OR @eloTag LIKE '%[[]BTA%'
      OR @eloTag LIKE '%[[]KGK%' 
      OR @eloTag LIKE '%[[]KA%'
    BEGIN
	    RETURN
    END

    IF (SELECT TOROLT FROM T_FELHASZNALO_OSSZES WHERE ID = @alkalmazottId) = 'T' 
      OR (SELECT TOROLT FROM T_FELHASZNALO_OSSZES WHERE ID = @ujAlkalmazottId) = 'T' 
      OR EXISTS (SELECT 1 FROM T_MUNKAUGYIADATOK WHERE C_ALKALMAZOTTID=@alkalmazottId AND ISNULL(C_ALKALMAZASMEGSZUNESE,'2200-01-01')<=(SELECT C_KEZDONAP FROM T_TANEV WHERE ID = @kovetkezoTanevId))
    BEGIN
      RETURN
    END
  END
  BEGIN --SETUP
  SELECT @ujAlkalmazottId = ID
  FROM T_FELHASZNALO_OSSZES 
  WHERE ELOZOTANEVIREKORDID = @alkalmazottId 
    AND C_TANEVID = @kovetkezoTanevId   
  
  SET @ExcludeInsertColumns = 'FTIP'
    
  INSERT INTO #columnMappings (insertColName,selectColName,updateColname,tableName)
  SELECT insertColName,selectColName,updateColname,'T_FELHASZNALO' FROM fnGetFollowUpColumnMapping (@ExcludeInsertColumns,'T_FELHASZNALO')
  UNION ALL
  SELECT insertColName,selectColName,updateColname,'T_ALKALMAZOTT' FROM fnGetFollowUpColumnMapping (@ExcludeInsertColumns,'T_ALKALMAZOTT')
  UNION ALL
  SELECT insertColName,selectColName,updateColname,'T_MUNKAUGYIADATOK' FROM fnGetFollowUpColumnMapping (@ExcludeInsertColumns,'T_MUNKAUGYIADATOK')

  END

  --====================================================== T_FELHASZNALO==================================================--
  BEGIN
  IF @ujAlkalmazottId IS NULL
    BEGIN
      SELECT @insertColList = ISNULL(@insertColList+','+insertColname,insertColname) FROM #columnMappings WHERE tableName IN ('T_FELHASZNALO')
      SELECT @selectColList = ISNULL(@selectColList+','+selectColName,selectColName) FROM #columnMappings WHERE tableName IN ('T_FELHASZNALO')

      SET @partialSql = N'
        INSERT INTO T_FELHASZNALO_OSSZES (
          '+@insertColList+'
        ) SELECT 
          '+@selectColList+' 
        FROM T_FELHASZNALO_OSSZES s 
        WHERE s.ID = @alkalmazottId 
          AND EXISTS (SELECT 1 FROM T_ALKALMAZOTT_OSSZES WHERE T_ALKALMAZOTT_OSSZES.ID = s.ID AND TOROLT = ''F'')
          AND s.TOROLT = ''F''
        SET @ujAlkalmazottId = SCOPE_IDENTITY()'
    END
    ELSE BEGIN      
      SELECT 
        @updateColList = ISNULL(@updateColList+','+insertColName+' = '+updateColname,insertColName+' = '+updateColname) 
      FROM #columnMappings
      WHERE tableName = 'T_FELHASZNALO' AND updateColname IS NOT NULL

      SET @partialSql =N'
        UPDATE t SET
          '+@updateColList+'
        FROM T_FELHASZNALO_OSSZES t
          INNER JOIN T_FELHASZNALO_OSSZES s ON s.ID = t.ELOZOTANEVIREKORDID
        WHERE t.ID = @ujAlkalmazottId'
    END
    
    SET @updateColList = NULL
    SET @selectColList = NULL
    SET @insertColList = NULL
    --PRINT @partialSql
    SET @fullSql = @partialSql
  END
  --====================================================== T_FELHASZNALO VÉGE=============================================--
  
  --====================================================== T_ALKALMAZOTT==================================================--
  BEGIN 
    IF NOT EXISTS (SELECT 1 FROM T_ALKALMAZOTT_OSSZES WHERE ID = @ujAlkalmazottId) BEGIN
      INSERT INTO #columnMappings VALUES
        ('ID','@ujAlkalmazottId',NULL,'T_ALKALMAZOTT')

      SELECT @insertColList = ISNULL(@insertColList+','+insertColname,insertColname) FROM #columnMappings WHERE tableName IN ('T_ALKALMAZOTT')
      SELECT @selectColList = ISNULL(@selectColList+','+selectColName,selectColName) FROM #columnMappings WHERE tableName IN ('T_ALKALMAZOTT')

      SET @partialSql = N'
        INSERT INTO T_ALKALMAZOTT_OSSZES (
          '+@insertColList+'
        ) SELECT 
          '+@selectColList+'
        FROM T_ALKALMAZOTT_OSSZES s 
        WHERE s.C_ALTANEVID = @aktTanevId AND s.ID = @alkalmazottId'
    END
    ELSE BEGIN
      SELECT 
        @updateColList = ISNULL(@updateColList+','+insertColName+' = '+updateColname,insertColName+' = '+updateColname)
      FROM #columnMappings
      WHERE tableName = 'T_ALKALMAZOTT' AND updateColname IS NOT NULL
       
      SET @partialSql = N'
        UPDATE t SET
          '+@updateColList+'
        FROM T_ALKALMAZOTT_OSSZES t
          INNER JOIN T_ALKALMAZOTT_OSSZES s ON s.ID = t.ELOZOTANEVIREKORDID
        WHERE t.ID = @ujAlkalmazottId'
    END
    
    --PRINT @partialSql
    SET @updateColList = NULL
    SET @selectColList = NULL
    SET @insertColList = NULL
    SET @fullSql += @partialSql
  END
  --====================================================== T_ALKALMAZOTT VÉGE=============================================--
  
  --====================================================== T_MUNKAUGYIADATOK==============================================--
  BEGIN 
    IF NOT EXISTS (SELECT 1 FROM T_MUNKAUGYIADATOK_OSSZES WHERE C_ALKALMAZOTTID = @ujAlkalmazottId) BEGIN
      
      SELECT @insertColList = ISNULL(@insertColList+','+insertColname,insertColname) FROM #columnMappings WHERE tableName IN ('T_MUNKAUGYIADATOK')
      SELECT @selectColList = ISNULL(@selectColList+','+selectColName,selectColName) FROM #columnMappings WHERE tableName IN ('T_MUNKAUGYIADATOK')

      SET @partialSql = N'
        INSERT INTO T_MUNKAUGYIADATOK_OSSZES (
          '+@insertColList+'
        ) SELECT 
          '+@selectColList+'
        FROM T_MUNKAUGYIADATOK_OSSZES s 
          INNER JOIN T_FELADATELLATASIHELY_OSSZES f ON f.ELOZOTANEVIREKORDID = s.C_FELADATELLATASIHELYID AND f.TOROLT=''F''
        WHERE s.TOROLT = ''F'' AND s.C_ALKALMAZOTTID = @alkalmazottId'
    END
    ELSE
    BEGIN
      SELECT 
        @updateColList = ISNULL(@updateColList+','+insertColName+' = '+updateColname,insertColName+' = '+updateColname) 
      FROM #columnMappings
      WHERE tableName = 'T_MUNKAUGYIADATOK' AND updateColname IS NOT NULL

      SET @partialSql = N'
        UPDATE t SET
          '+@updateColList+'
        FROM T_MUNKAUGYIADATOK_OSSZES t
          INNER JOIN T_MUNKAUGYIADATOK_OSSZES s ON s.ID = t.ELOZOTANEVIREKORDID
          INNER JOIN T_FELADATELLATASIHELY_OSSZES f ON f.ELOZOTANEVIREKORDID = s.C_FELADATELLATASIHELYID AND f.TOROLT=''F''
        WHERE t.C_ALKALMAZOTTID = @ujAlkalmazottId'
    END
    --PRINT @partialSql
    SET @fullSql += @partialSql
  END
  --====================================================== T_MUNKAUGYIADATOK VÉGE=========================================--
    
  BEGIN --Cím, Email, Telefon
    EXEC uspFollowUpFelhasznaloCim @intezmenyId,@aktTanevid,@kovetkezoTanevId,@alkalmazottId
    EXEC uspFollowUpFelhasznaloEmail @intezmenyId,@aktTanevid,@kovetkezoTanevId,@alkalmazottId
    EXEC uspFollowUpFelhasznaloTelefon @intezmenyId,@aktTanevid,@kovetkezoTanevId,@alkalmazottId
  END

  EXEC sp_executesql 
     @fullSql,N'
     @alkalmazottId int
    ,@intezmenyId int
    ,@kovetkezoTanevId int
    ,@aktTanevId int
    ,@ujAlkalmazottId int'
    ,@alkalmazottId = @alkalmazottId 
    ,@intezmenyId = @intezmenyId 
    ,@kovetkezoTanevId = @kovetkezoTanevId
    ,@ujAlkalmazottId = @ujAlkalmazottId
    ,@aktTanevId = @aktTanevId

  IF DB_NAME() LIKE '%KRETA[_]KLIK[_]%' BEGIN --sp_FollowUpAlkalmazottVegzettsegKK
		EXEC uspFollowUpAlkalmazottVegzettsegKK
			 @intezmenyId
			,@aktTanevId
			,@kovetkezoTanevId
			,@alkalmazottId
  END
END