DROP PROCEDURE IF EXISTS dbo.uspGetAlkalmazottByVezetoiOraszamOk
GO

CREATE PROCEDURE [dbo].[uspGetAlkalmazottByVezetoiOraszamOk]
	@pTanevId   int
  ,@pNyomtatvanyNyelv int
AS
BEGIN
	SET NOCOUNT ON;
  SELECT 
     ROW_NUMBER() OVER (ORDER BY result.Nev, result.VezetoiOraszamOk) AS Id
    ,result.* 
  FROM
  (
    SELECT 
	     f.C_NYOMTATASINEV AS Nev
      ,CASE @pNyomtatvanyNyelv
        WHEN 2 THEN d.C_NAME_1
        WHEN 1 THEN d.C_NAME_2
        ELSE d.C_NAME
      END AS VezetoiOraszamOk 
      ,0 AS IsIntezmenyAdat
    FROM T_MUNKAUGYIADATOK_OSSZES ma
	    INNER JOIN T_FELHASZNALO_OSSZES f on f.ID = ma.C_ALKALMAZOTTID
		    AND f.TOROLT = 'F'
	    INNER JOIN T_DICTIONARYITEMBASE_OSSZES d on d.ID = ma.C_VEZETOIORASZAMOK
        AND d.TOROLT = 'F'
        AND d.C_TANEVID = ma.C_TANEVID
    WHERE ma.TOROLT = 'F'
	    AND ma.C_TANEVID = @pTanevId
	    AND ma.C_VEZETOIORASZAMOK in (6572,6573,6574,7755,7757,7758)
    UNION
    SELECT 
	     f.C_NYOMTATASINEV AS Nev
      ,CASE @pNyomtatvanyNyelv
        WHEN 2 THEN d.C_NAME_1
        WHEN 1 THEN d.C_NAME_2
        ELSE d.C_NAME
      END AS VezetoiOraszamOk 
      ,0 AS IsIntezmenyAdat
    FROM T_TOVABBIMUNKAUGYIADATOK_OSSZES ma
	    INNER JOIN T_FELHASZNALO_OSSZES f on f.ID = ma.C_ALKALMAZOTTID
		    AND f.TOROLT = 'F'
	    INNER JOIN T_DICTIONARYITEMBASE_OSSZES d on d.ID = ma.C_VEZETOIORASZAMOK
        AND d.TOROLT = 'F'
        AND d.C_TANEVID = ma.C_TANEVID
    WHERE ma.TOROLT = 'F'
	    AND ma.C_TANEVID = @pTanevId
	    AND ma.C_VEZETOIORASZAMOK in (6572,6573,6574,7755,7757,7758)
    UNION
    SELECT
       ia.C_IGAZGATONEVE AS Nev
      ,(SELECT 
          CASE @pNyomtatvanyNyelv
            WHEN 2 THEN d.C_NAME_1
            WHEN 1 THEN d.C_NAME_2
            ELSE d.C_NAME
          END
        FROM T_DICTIONARYITEMBASE_OSSZES d 
        WHERE d.TOROLT = 'F'
        AND d.C_TANEVID = @pTanevId
        AND d.ID = 7755) AS VezetoiOraszamOk 
      ,1 AS IsIntezmenyAdat
    FROM T_INTEZMENYADATOK_OSSZES ia
    WHERE ia.TOROLT = 'F'
      AND ia.C_TANEVID = @pTanevId
  ) result

END