49 lines
2.2 KiB
Transact-SQL
49 lines
2.2 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspGetNevOktLeNemKotottMunkaido
|
|
GO
|
|
|
|
CREATE PROCEDURE dbo.uspGetNevOktLeNemKotottMunkaido
|
|
@pTanevId int
|
|
,@pFeladatKategoriaId int = NULL
|
|
,@pFeladatEllatasiHelyId int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
DECLARE @sql nvarchar(max)
|
|
|
|
SET @sql = N'
|
|
SELECT
|
|
nkm.ID
|
|
,CONVERT(datetime, nkm.C_KEZDETE, 102) AS Datum
|
|
,CONVERT(datetime, nkm.C_KEZDETE, 114) AS Kezdete
|
|
,CONVERT(datetime, nkm.C_VEGE, 114) AS Vege
|
|
,nkm.C_TANARID AS TanarId
|
|
,f.C_NYOMTATASINEV AS TanarNeve
|
|
,nkm.C_TEVEKENYSEGTIPUSA AS KategoriaId
|
|
,IIF(nkm.C_ISTANARALTALTOROLT = ''T'', ''Törölt'', dict.C_NAME) AS KategoriaNeve
|
|
,nkm.C_ROGZITESDATUMA AS RogzitesDatuma
|
|
,nkm.C_MEGTARTOTT AS Megtartott
|
|
,nkm.C_ISTANARALTALTOROLT AS ToroltTevekenysegek
|
|
FROM T_NEMKOTOTTMUNKAIDO_OSSZES nkm
|
|
INNER JOIN T_FELHASZNALO_OSSZES f ON f.ID = nkm.C_TANARID AND f.C_TANEVID = nkm.C_TANEVID AND f.TOROLT = ''F''
|
|
' + IIF(@pFeladatKategoriaId IS NULL AND @pFeladatEllatasiHelyId IS NULL, N'',
|
|
N' INNER JOIN T_MUNKAUGYIADATOK_OSSZES ma ON ma.C_ALKALMAZOTTID = f.ID AND f.C_TANEVID = ma.C_TANEVID AND ma.TOROLT = ''F''') + '
|
|
' + IIF(@pFeladatKategoriaId IS NULL, N'',
|
|
N' INNER JOIN T_FELADATELLATASIHELY_OSSZES felhely ON felhely.ID = ma.C_FELADATELLATASIHELYID AND felhely.C_TANEVID = ma.C_TANEVID AND felhely.TOROLT = ''F''
|
|
INNER JOIN T_OKTATASINEVELESIFELADAT_OSSZES oktnevfel ON oktnevfel.ID = felhely.C_OKTATASINEVELESIFELADATTIPUS AND felhely.C_TANEVID = oktnevfel.C_ALTANEVID AND oktnevfel.TOROLT = ''F'' ') + '
|
|
INNER JOIN T_DICTIONARYITEMBASE dict ON dict.ID = nkm.C_TEVEKENYSEGTIPUSA AND dict.TOROLT = ''F'' AND dict.C_TANEVID = nkm.C_TANEVID
|
|
WHERE nkm.C_TANEVID = @pTanevId
|
|
' + IIF(@pFeladatKategoriaId IS NULL, N'', N' AND oktnevfel.C_FELADATKATEGORIAID = @pFeladatKategoriaId') + '
|
|
' + IIF(@pFeladatEllatasiHelyId IS NULL, N'', N' AND ma.C_FELADATELLATASIHELYID = @pFeladatEllatasiHelyId') + '
|
|
AND nkm.TOROLT = ''F'''
|
|
|
|
EXEC sp_executesql @sql, N'
|
|
@pTanevId int
|
|
,@pFeladatKategoriaId int
|
|
,@pFeladatEllatasiHelyId int
|
|
'
|
|
,@pTanevId = @pTanevId
|
|
,@pFeladatKategoriaId = @pFeladatKategoriaId
|
|
,@pFeladatEllatasiHelyId = @pFeladatEllatasiHelyId
|
|
|
|
END
|
|
GO
|