42 lines
1.5 KiB
Transact-SQL
42 lines
1.5 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspGetTanuloErtekelesMondatbankData
|
|
GO
|
|
|
|
CREATE PROCEDURE uspGetTanuloErtekelesMondatbankData
|
|
@pTanevId int
|
|
,@pTanuloErtekelesTipusId int = NULL
|
|
,@pEvfolyamTipusId int = NULL
|
|
,@pTantargyId int = NULL
|
|
,@pEvfolyamTipusNaId int
|
|
,@pTipusId int = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT
|
|
tem.ID AS Id
|
|
,tem.C_ROVIDNEV AS RovidNev
|
|
,tem.C_SZOVEG AS Szoveg
|
|
,tem.C_ERTEKELESTIPUSA AS TanuloErtekelesTipusId
|
|
,tem.C_EVFOLYAMTIPUSA AS EvfolyamTipusId
|
|
,tem.C_TANTARGYID AS TantargyId
|
|
,CASE
|
|
WHEN tem.C_TIPUSID = 7358 THEN 'Magatartás'
|
|
WHEN tem.C_TIPUSID = 7359 THEN 'Szorgalom'
|
|
ELSE IIF(tem.C_TANTARGYID IS NULL, 'Mind', tt.C_NEV)
|
|
END AS TantargyNev
|
|
,tem.C_TIPUSID AS TipusId
|
|
,tem.C_IMPORTALT AS Importalt
|
|
FROM T_ERTEKELESMONDATBANK_OSSZES tem
|
|
LEFT JOIN T_TANTARGY_OSSZES tt ON tt.ID = tem.C_TANTARGYID AND tt.C_TANEVID = tem.C_TANEVID AND tt.TOROLT = 'F'
|
|
WHERE (@pTanuloErtekelesTipusId IS NULL OR tem.C_ERTEKELESTIPUSA IS NULL OR tem.C_ERTEKELESTIPUSA = @pTanuloErtekelesTipusId)
|
|
AND (@pEvfolyamTipusId IS NULL OR @pEvfolyamTipusId = @pEvfolyamTipusNaId OR tem.C_EVFOLYAMTIPUSA IS NULL OR tem.C_EVFOLYAMTIPUSA = @pEvfolyamTipusId)
|
|
AND (@pTantargyId IS NULL OR tem.C_TANTARGYID IS NULL OR tem.C_TANTARGYID = @pTantargyId)
|
|
AND (@pTipusId IS NULL OR tem.C_TIPUSID = @pTipusId OR (@pTipusId IS NOT NULL AND tem.C_TIPUSID = 7357 AND tem.C_TANTARGYID IS NULL))
|
|
AND tem.C_TANEVID = @pTanevId
|
|
AND tem.TOROLT = 'F'
|
|
END
|
|
|
|
|
|
GO
|
|
|