88 lines
3.5 KiB
Transact-SQL
88 lines
3.5 KiB
Transact-SQL
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
|
|
,LTRIM(SUBSTRING(C_NYOMTATASINEV, C_ANYJANEVEELVALASZTO + 1 , LEN(C_NYOMTATASINEV) - C_ANYJANEVEELVALASZTO)) + ' ' + LTRIM(SUBSTRING(C_NYOMTATASINEV, 0, C_ANYJANEVEELVALASZTO + 1)) NevIdegenNyelven
|
|
,ISNULL(dny.C_NAME, d.C_NAME) AS VezetoiOraszamOkIdegenNyelven
|
|
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
|
|
LEFT JOIN T_DICTIONARYITEMBASENYELV_OSSZES dny ON dny.C_DICTIONARYITEMBASEID = d.ID
|
|
AND dny.C_TANEVID = d.C_TANEVID
|
|
AND dny.C_NYELVID = @pNyomtatvanyNyelv
|
|
AND dny.TOROLT = 'F'
|
|
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
|
|
,LTRIM(SUBSTRING(C_NYOMTATASINEV, C_ANYJANEVEELVALASZTO + 1 , LEN(C_NYOMTATASINEV) - C_ANYJANEVEELVALASZTO)) + ' ' + LTRIM(SUBSTRING(C_NYOMTATASINEV, 0, C_ANYJANEVEELVALASZTO + 1)) NevIdegenNyelven
|
|
,ISNULL(dny.C_NAME, d.C_NAME) AS VezetoiOraszamOkIdegenNyelven
|
|
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
|
|
LEFT JOIN T_DICTIONARYITEMBASENYELV_OSSZES dny ON dny.C_DICTIONARYITEMBASEID = d.ID
|
|
AND dny.C_TANEVID = d.C_TANEVID
|
|
AND dny.C_NYELVID = @pNyomtatvanyNyelv
|
|
AND dny.TOROLT = 'F'
|
|
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
|
|
,CASE @pNyomtatvanyNyelv
|
|
WHEN 2 THEN d.C_NAME_1
|
|
WHEN 1 THEN d.C_NAME_2
|
|
ELSE d.C_NAME
|
|
END AS VezetoiOraszamOk
|
|
,1 AS IsIntezmenyAdat
|
|
,LTRIM(SUBSTRING(ia.C_IGAZGATONEVE, CHARINDEX(' ', ia.C_IGAZGATONEVE) + 1 , LEN(ia.C_IGAZGATONEVE) - CHARINDEX(' ', ia.C_IGAZGATONEVE)))
|
|
+ ' ' + LTRIM(SUBSTRING(ia.C_IGAZGATONEVE, 0, CHARINDEX(' ', ia.C_IGAZGATONEVE) + 1)) AS NevIdegenNyelven
|
|
,ISNULL(dny.C_NAME, d.C_NAME) AS VezetoiOraszamOkIdegenNyelven
|
|
FROM T_INTEZMENYADATOK_OSSZES ia
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES d on d.ID = 7755
|
|
AND d.TOROLT = 'F'
|
|
AND d.C_TANEVID = ia.C_TANEVID
|
|
LEFT JOIN T_DICTIONARYITEMBASENYELV_OSSZES dny ON dny.C_DICTIONARYITEMBASEID = d.ID
|
|
AND dny.C_TANEVID = d.C_TANEVID
|
|
AND dny.C_NYELVID = @pNyomtatvanyNyelv
|
|
AND dny.TOROLT = 'F'
|
|
WHERE ia.TOROLT = 'F'
|
|
AND ia.C_TANEVID = @pTanevId
|
|
) result
|
|
|
|
END |