kreta/Kreta.DataAccess.Migrations/Scripts/Archive/20180612084817_OM_965/sp_FollowUpAlkalmazottVegzettsegKK.sql

IF OBJECT_ID('sp_FollowUpAlkalmazottVegzettsegKK') IS NOT NULL BEGIN
  DROP PROCEDURE sp_FollowUpAlkalmazottVegzettsegKK
END;
GO

CREATE PROCEDURE sp_FollowUpAlkalmazottVegzettsegKK
  @intezmenyId int,
  @aktTanevId int,
  @kovetkezoTanevId int,
  @alkalmazottId int
 
AS
BEGIN
	DECLARE @kovAlkalmazottId int
  
  SELECT @kovAlkalmazottId = ID 
  FROM T_FELHASZNALO fh
  WHERE fh.ELOZOTANEVIREKORDID = @alkalmazottId
  
  IF @kovAlkalmazottId IS NULL BEGIN
    RETURN
  END
  
  INSERT INTO T_KKTANITOVEZGETTSEG (
     C_KKTANIOTOMUVTERTIPUSID
    ,C_ALKALMAZOTTID
    ,C_INTEZMENYID
    ,C_TANEVID
    ,TOROLT
    ,SERIAL
    ,LASTCHANGED
    ,CREATED
    ,MODIFIER
    ,CREATOR
    ,ELOZOTANEVIREKORDID
    ,NNID
  ) SELECT 
     x.C_KKTANIOTOMUVTERTIPUSID AS C_KKTANIOTOMUVTERTIPUSID -- C_KKTANIOTOMUVTERTIPUSID - int
    ,@kovAlkalmazottId   AS C_ALKALMAZOTTID         -- C_ALKALMAZOTTID - int
    ,@intezmenyId   AS C_INTEZMENYID           -- C_INTEZMENYID - int
    ,@kovetkezoTanevId   AS C_TANEVID               -- C_TANEVID - int
    ,'F'  AS TOROLT                  -- TOROLT - char(1)
    ,0   AS SERIAL                  -- SERIAL - int
    ,getdate() AS LASTCHANGED       -- LASTCHANGED - datetime
    ,getdate() AS CREATED           -- CREATED - datetime
    ,x.MODIFIER AS MODIFIER               -- MODIFIER - int
    ,x.MODIFIER AS CREATOR                -- CREATOR - int
    ,x.ID AS ELOZOTANEVIREKORDID    -- ELOZOTANEVIREKORDID - int
    ,NULL AS NNID                   -- NNID - int
  FROM T_KKTANITOVEZGETTSEG x
  WHERE C_ALKALMAZOTTID = @alkalmazottId 
    AND x.TOROLT = 'F'
    AND NOT EXISTS (
      SELECT 1 
      FROM T_KKTANITOVEZGETTSEG s 
      WHERE C_ALKALMAZOTTID = @kovAlkalmazottId 
        AND s.C_KKTANIOTOMUVTERTIPUSID = x.C_KKTANIOTOMUVTERTIPUSID 
        AND TOROLT = 'F'
      )

  INSERT INTO T_KKGYOGYPEDVEGZETTSEG (
     C_KKGYOGYPEDTERTIPUSID
    ,C_ALKALMAZOTTID
    ,C_INTEZMENYID
    ,C_TANEVID
    ,TOROLT
    ,SERIAL
    ,LASTCHANGED
    ,CREATED
    ,MODIFIER
    ,CREATOR
    ,ELOZOTANEVIREKORDID
    ,NNID
  ) SELECT 
     x.C_KKGYOGYPEDTERTIPUSID AS C_KKGYOGYPEDTERTIPUSID -- C_KKGYOGYPEDTERTIPUSID - int
    ,@kovAlkalmazottId   AS C_ALKALMAZOTTID         -- C_ALKALMAZOTTID - int
    ,@intezmenyId   AS C_INTEZMENYID           -- C_INTEZMENYID - int
    ,@kovetkezoTanevId   AS C_TANEVID               -- C_TANEVID - int
    ,'F'  AS TOROLT                  -- TOROLT - char(1)
    ,0   AS SERIAL                  -- SERIAL - int
    ,getdate() AS LASTCHANGED       -- LASTCHANGED - datetime
    ,getdate() AS CREATED           -- CREATED - datetime
    ,x.MODIFIER AS MODIFIER               -- MODIFIER - int
    ,x.MODIFIER AS CREATOR                -- CREATOR - int
    ,x.ID AS ELOZOTANEVIREKORDID    -- ELOZOTANEVIREKORDID - int
    ,NULL AS NNID                   -- NNID - int
  FROM T_KKGYOGYPEDVEGZETTSEG x
  WHERE C_ALKALMAZOTTID = @alkalmazottId
    AND x.TOROLT = 'F'
    AND NOT EXISTS (
      SELECT 1 
      FROM T_KKGYOGYPEDVEGZETTSEG s 
      WHERE C_ALKALMAZOTTID = @kovAlkalmazottId 
        AND s.C_KKGYOGYPEDTERTIPUSID = x.C_KKGYOGYPEDTERTIPUSID 
        AND TOROLT = 'F'
      )

  
  UPDATE x SET 
     x.C_EGYEB = s.C_EGYEB
    ,x.MODIFIER = s.MODIFIER
  FROM T_KKTANARVEGZETTSEG x
    INNER JOIN T_KKTANARVEGZETTSEG s ON x.C_KKTANARVEGZETTSEGID = s.C_KKTANARVEGZETTSEGID
  WHERE x.C_ALKALMAZOTTID = @kovAlkalmazottId 
    AND s.C_ALKALMAZOTTID = @alkalmazottId
    AND x.TOROLT = 'F'
    AND s.TOROLT = 'F'
  
  INSERT INTO T_KKTANARVEGZETTSEG (
     C_EGYEB
    ,C_KKTANARVEGZETTSEGID
    ,C_ALKALMAZOTTID
    ,C_INTEZMENYID
    ,C_TANEVID
    ,TOROLT
    ,SERIAL
    ,LASTCHANGED
    ,CREATED
    ,MODIFIER
    ,CREATOR
    ,ELOZOTANEVIREKORDID
    ,NNID
  ) SELECT
     x.C_EGYEB AS C_EGYEB -- C_EGYEB - nvarchar(max)
    ,x.C_KKTANARVEGZETTSEGID AS C_KKTANARVEGZETTSEGID -- C_KKTANARVEGZETTSEGID - int
    ,@kovAlkalmazottId   AS C_ALKALMAZOTTID         -- C_ALKALMAZOTTID - int
    ,@intezmenyId   AS C_INTEZMENYID           -- C_INTEZMENYID - int
    ,@kovetkezoTanevId   AS C_TANEVID               -- C_TANEVID - int
    ,'F'  AS TOROLT                  -- TOROLT - char(1)
    ,0   AS SERIAL                  -- SERIAL - int
    ,getdate() AS LASTCHANGED       -- LASTCHANGED - datetime
    ,getdate() AS CREATED           -- CREATED - datetime
    ,x.MODIFIER AS MODIFIER               -- MODIFIER - int
    ,x.MODIFIER AS CREATOR                -- CREATOR - int
    ,x.ID AS ELOZOTANEVIREKORDID    -- ELOZOTANEVIREKORDID - int
    ,NULL AS NNID                   -- NNID - int
  FROM T_KKTANARVEGZETTSEG x
  WHERE C_ALKALMAZOTTID = @alkalmazottId
    AND x.TOROLT = 'F'
    AND NOT EXISTS (
      SELECT 1 
      FROM T_KKTANARVEGZETTSEG s 
      WHERE C_ALKALMAZOTTID = @kovAlkalmazottId 
        AND s.C_KKTANARVEGZETTSEGID = x.C_KKTANARVEGZETTSEGID 
        AND TOROLT = 'F'
      )

  INSERT INTO T_KKTANTARGYKATEGORIA (
     C_KKSZAKTIPUSID
    ,C_KKTANARVEGZETTSEGID
    ,C_INTEZMENYID
    ,C_TANEVID
    ,TOROLT
    ,SERIAL
    ,LASTCHANGED
    ,CREATED
    ,MODIFIER
    ,CREATOR
    ,ELOZOTANEVIREKORDID
    ,NNID
  ) SELECT 
     x.C_KKSZAKTIPUSID AS C_KKSZAKTIPUSID        -- C_KKSZAKTIPUSID - int
    ,tvs.ID   AS C_KKTANARVEGZETTSEGID   -- C_KKTANARVEGZETTSEGID - int
    ,@intezmenyId   AS C_INTEZMENYID           -- C_INTEZMENYID - int
    ,@kovetkezoTanevId   AS C_TANEVID               -- C_TANEVID - int
    ,x.TOROLT  AS TOROLT                  -- TOROLT - char(1)
    ,0   AS SERIAL                  -- SERIAL - int
    ,getdate() AS LASTCHANGED       -- LASTCHANGED - datetime
    ,getdate() AS CREATED           -- CREATED - datetime
    ,x.MODIFIER AS MODIFIER               -- MODIFIER - int
    ,x.MODIFIER AS CREATOR                -- CREATOR - int
    ,x.ID AS ELOZOTANEVIREKORDID    -- ELOZOTANEVIREKORDID - int
    ,NULL AS NNID                   -- NNID - int
  FROM T_KKTANTARGYKATEGORIA x
    INNER JOIN T_KKTANARVEGZETTSEG tvx ON x.C_KKTANARVEGZETTSEGID = tvx.ID AND tvx.C_ALKALMAZOTTID = @alkalmazottId AND tvx.TOROLT = 'F'
    INNER JOIN T_KKTANARVEGZETTSEG tvs ON tvs.C_KKTANARVEGZETTSEGID = tvx.C_KKTANARVEGZETTSEGID AND tvs.C_ALKALMAZOTTID = @kovAlkalmazottId  AND tvs.TOROLT = 'F'
  WHERE x.TOROLT = 'F'
    AND NOT EXISTS (
      SELECT 1
      FROM T_KKTANTARGYKATEGORIA s 
      WHERE tvs.ID = s.C_KKTANARVEGZETTSEGID 
        AND s.TOROLT = 'F' 
        AND s.C_KKSZAKTIPUSID = x.C_KKSZAKTIPUSID
    )

  
  
  INSERT INTO T_KKAMIVEGZETTSEG (
     C_KKAMIVEGZETTSEGTIPUSID
    ,C_ALKALMAZOTTID
    ,C_INTEZMENYID
    ,C_TANEVID
    ,TOROLT
    ,SERIAL
    ,LASTCHANGED
    ,CREATED
    ,MODIFIER
    ,CREATOR
    ,ELOZOTANEVIREKORDID
    ,NNID
  ) SELECT 
     x.C_KKAMIVEGZETTSEGTIPUSID AS C_KKAMIVEGZETTSEGTIPUSID -- C_KKAMIVEGZETTSEGTIPUSID - int
    ,@kovAlkalmazottId   AS C_ALKALMAZOTTID         -- C_ALKALMAZOTTID - int
    ,@intezmenyId   AS C_INTEZMENYID           -- C_INTEZMENYID - int
    ,@kovetkezoTanevId   AS C_TANEVID               -- C_TANEVID - int
    ,'F'  AS TOROLT                  -- TOROLT - char(1)
    ,0   AS SERIAL                  -- SERIAL - int
    ,getdate() AS LASTCHANGED       -- LASTCHANGED - datetime
    ,getdate() AS CREATED           -- CREATED - datetime
    ,x.MODIFIER AS MODIFIER               -- MODIFIER - int
    ,x.MODIFIER AS CREATOR                -- CREATOR - int
    ,x.ID AS ELOZOTANEVIREKORDID    -- ELOZOTANEVIREKORDID - int
    ,NULL AS NNID                   -- NNID - int
  FROM T_KKAMIVEGZETTSEG x
  WHERE C_ALKALMAZOTTID = @alkalmazottId
    AND x.TOROLT = 'F'
    AND NOT EXISTS (
      SELECT 1 
      FROM T_KKAMIVEGZETTSEG s 
      WHERE C_ALKALMAZOTTID = @kovAlkalmazottId 
        AND s.C_KKAMIVEGZETTSEGTIPUSID = x.C_KKAMIVEGZETTSEGTIPUSID 
        AND TOROLT = 'F'
    )
  
  INSERT INTO T_KKELEKTROAKUZENE (
     C_ELEKTRAKUTIPUSID
    ,C_KKAMIVEGZETTSEGID
    ,C_INTEZMENYID
    ,C_TANEVID
    ,TOROLT
    ,SERIAL
    ,LASTCHANGED
    ,CREATED
    ,MODIFIER
    ,CREATOR
    ,ELOZOTANEVIREKORDID
    ,NNID
  ) SELECT 
     x.C_ELEKTRAKUTIPUSID AS C_ELEKTRAKUTIPUSID        -- C_ELEKTRAKUTIPUSID - int
    ,tvs.ID   AS C_KKAMIVEGZETTSEGID   -- C_KKAMIVEGZETTSEGID - int
    ,@intezmenyId   AS C_INTEZMENYID           -- C_INTEZMENYID - int
    ,@kovetkezoTanevId   AS C_TANEVID               -- C_TANEVID - int
    ,x.TOROLT  AS TOROLT                  -- TOROLT - char(1)
    ,0   AS SERIAL                  -- SERIAL - int
    ,getdate() AS LASTCHANGED       -- LASTCHANGED - datetime
    ,getdate() AS CREATED           -- CREATED - datetime
    ,x.MODIFIER AS MODIFIER               -- MODIFIER - int
    ,x.MODIFIER AS CREATOR                -- CREATOR - int
    ,x.ID AS ELOZOTANEVIREKORDID    -- ELOZOTANEVIREKORDID - int
    ,NULL AS NNID                   -- NNID - int
  FROM T_KKELEKTROAKUZENE x
    INNER JOIN T_KKAMIVEGZETTSEG tvx ON x.C_KKAMIVEGZETTSEGID = tvx.ID AND tvx.C_ALKALMAZOTTID = @alkalmazottId AND tvx.TOROLT = 'F'
    INNER JOIN T_KKAMIVEGZETTSEG tvs ON tvx.C_KKAMIVEGZETTSEGTIPUSID = tvx.C_KKAMIVEGZETTSEGTIPUSID AND tvs.C_ALKALMAZOTTID = @kovAlkalmazottId  AND tvs.TOROLT = 'F'
  WHERE x.TOROLT = 'F'
    AND NOT EXISTS (
      SELECT 1
      FROM T_KKELEKTROAKUZENE s 
      WHERE tvs.ID = s.C_KKAMIVEGZETTSEGID 
        AND s.TOROLT = 'F' 
        AND s.C_ELEKTRAKUTIPUSID = x.C_ELEKTRAKUTIPUSID
    )
    
END
GO