84 lines
No EOL
3.8 KiB
Transact-SQL
84 lines
No EOL
3.8 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspGetTantargyData
|
|
GO
|
|
|
|
CREATE PROCEDURE uspGetTantargyData
|
|
@pTanevId int
|
|
,@pIsRendezoOszlopokKell bit
|
|
AS BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @sql nvarchar(max)
|
|
|
|
SET @sql = N'
|
|
SELECT
|
|
t.ID AS Id
|
|
,t.C_NEV AS Nev
|
|
,t.C_TARGYKATEGORIA AS TargykategoriaTipusId
|
|
,t.C_IMPORTALT AS Importalt
|
|
,IIF(t.C_ISOSZTALYZATTALERTEKELHETO = ''F'' OR t.C_ISSZAZALEKOSANERTEKELHETO = ''F'' OR t.C_ISSZOVEGESENERTEKELHETO = ''F'', ''T'', ''F'') as IsErtekelesKorlatozva
|
|
,t.C_ESLTANTARGYKATEGORIAID AS EslTargykategoriaTipusId
|
|
,t.C_ISNINCSBELOLEORA AS IsNincsBeloleOra
|
|
,t.C_ISOSZTALYNAPLOBANEMLATSZIK AS IsOsztalyNaplobanNemJelenikMeg
|
|
,t.C_ISOSZTALYORARENDBENEMLATSZIK AS IsOsztalyokOrarendjebenMegjelenik
|
|
,t.C_ISTANULMANYIATLAGBANEMSZAMIT AS IsTanulmanyiAtlagbaSzamit
|
|
,t.C_ISAMITARGY AS IsAmiTargy
|
|
,t.C_ISKOLLEGIUMTARGY AS IsKollegiumTargy
|
|
,t.C_ISEGYMITARGY AS IsEgymiTargy
|
|
,t.C_ISFELNOTTOKTATASTARGY AS IsFelnottoktatasTargy
|
|
,t.C_ISMSZGTARGY As IsMszgTargy
|
|
,t.C_ANGOLNEV AS AngolNev
|
|
,t.C_NEMETNEV AS NemetNev
|
|
,max(CASE WHEN tny.C_NYELVID = 500 THEN tny.C_NEV end) AS HorvatNev
|
|
,max(CASE WHEN tny.C_NYELVID = 523 THEN tny.C_NEV end) AS RomanNev
|
|
,max(CASE WHEN tny.C_NYELVID = 536 THEN tny.C_NEV end) AS SzerbNev
|
|
,t.C_GYAKORLATI AS GyakorlatiTargy
|
|
,t.C_ROVIDNEV AS TantargyRovidNev
|
|
,t.C_FOTARGYID AS FoTargyID
|
|
,ft.C_NEV AS FoTargyNev
|
|
,t.C_FOTARGYE AS IsFotargy
|
|
,t.C_ALTANTARGYKENTNYOMTATVANYBAN AS IsAltantargykentBizonyitvanyban
|
|
,tanev.C_NEV AS TanevNev
|
|
,t.C_SORSZAM AS Sorszam
|
|
,t.C_MEGJEGYZES AS Megjegyzes
|
|
,t.C_NEVNYOMTATVANYBAN AS BizonyitvanyNev
|
|
'
|
|
SET @sql += IIF(@pIsRendezoOszlopokKell = 1, N'
|
|
,t.C_SORSZAM AS RENDEZ0
|
|
,CASE WHEN t.C_FOTARGYID IS NULL THEN ISNULL(TargyKategoriaDictionary.C_ORDER, 10000) ELSE ISNULL(FotargyKategoriaDictionary.C_ORDER, 10000) END AS RENDEZ1
|
|
,COALESCE(ft.C_NEVNYOMTATVANYBAN, ft.C_NEV, t.C_NEV) AS RENDEZ2
|
|
,ISNULL(t.C_FOTARGYID, t.ID) AS RENDEZ3
|
|
,t.C_FOTARGYID AS RENDEZ4
|
|
,ISNULL(t.C_NEVNYOMTATVANYBAN, t.C_NEV) AS RENDEZ5', '')
|
|
SET @sql += N'
|
|
FROM T_TANTARGY_OSSZES t
|
|
LEFT JOIN T_TANTARGY_OSSZES ft ON t.C_FOTARGYID = ft.ID
|
|
LEFT JOIN T_TANEV_OSSZES tanev on tanev.ID = t.C_TANEVID and tanev.TOROLT = ''F''
|
|
LEFT JOIN T_TANTARGYNYELV tny ON t.ID = tny.C_TANTARGYID AND tny.C_TANEVID = @pTanevId
|
|
'
|
|
SET @sql += IIF(@pIsRendezoOszlopokKell = 1, N'
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES TargyKategoriaDictionary ON TargyKategoriaDictionary.Id = t.C_TARGYKATEGORIA
|
|
AND TargyKategoriaDictionary.TOROLT = ''F''
|
|
AND TargyKategoriaDictionary.C_TANEVID = t.C_TANEVID
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES FotargyKategoriaDictionary ON FotargyKategoriaDictionary.ID = ft.C_TARGYKATEGORIA
|
|
AND FotargyKategoriaDictionary.TOROLT = ''F''
|
|
AND FotargyKategoriaDictionary.C_TANEVID = ft.C_TANEVID ', '')
|
|
SET @sql += N'
|
|
WHERE t.TOROLT = ''F''
|
|
AND t.C_TANEVID = @pTanevId
|
|
GROUP BY
|
|
t.ID, t.C_NEV, t.C_TARGYKATEGORIA, t.C_IMPORTALT, t.C_ISOSZTALYZATTALERTEKELHETO,
|
|
t.C_ISSZAZALEKOSANERTEKELHETO, t.C_ISSZOVEGESENERTEKELHETO, t.C_ESLTANTARGYKATEGORIAID, t.C_ISNINCSBELOLEORA,
|
|
t.C_ISOSZTALYNAPLOBANEMLATSZIK, t.C_ISOSZTALYORARENDBENEMLATSZIK, t.C_ISTANULMANYIATLAGBANEMSZAMIT, t.C_ISAMITARGY,
|
|
t.C_ISKOLLEGIUMTARGY,t.C_ISEGYMITARGY, t.C_ISFELNOTTOKTATASTARGY,t.C_ISMSZGTARGY, t.C_ANGOLNEV, t.C_NEMETNEV, t.C_GYAKORLATI, t.C_ROVIDNEV, ft.C_NEV, t.C_FOTARGYID, tanev.C_NEV,
|
|
t.C_SORSZAM, t.C_MEGJEGYZES, t.C_NEVNYOMTATVANYBAN, t.C_SORSZAM, ft.C_NEVNYOMTATVANYBAN, t.C_FOTARGYID, t.C_FOTARGYE, t.C_ALTANTARGYKENTNYOMTATVANYBAN'
|
|
|
|
SET @sql += IIF(@pIsRendezoOszlopokKell = 1, N'
|
|
, TargyKategoriaDictionary.C_ORDER, FotargyKategoriaDictionary.C_ORDER', '')
|
|
|
|
EXEC sp_executesql @sql
|
|
,N'
|
|
@pTanevId int
|
|
'
|
|
,@pTanevId = @pTanevId
|
|
|
|
END |