108 lines
3.3 KiB
Transact-SQL
108 lines
3.3 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspGetTanuloErtekelesMondatbankSzuresData
|
|
GO
|
|
|
|
CREATE PROCEDURE uspGetTanuloErtekelesMondatbankSzuresData
|
|
@pTanevId int
|
|
,@pTanuloErtekelesTipusIdList nvarchar(max) = NULL
|
|
,@pEvfolyamTipusIdList nvarchar(max) = NULL
|
|
,@pTantargyTipusIdJSON nvarchar(max) = NULL
|
|
,@pRovidNev nvarchar(3) = NULL
|
|
,@pSzoveg nvarchar(4000) = NULL
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
DECLARE @sql nvarchar(max)
|
|
|
|
SET @sql = N''
|
|
|
|
SET @sql += IIF(@pTanuloErtekelesTipusIdList IS NOT NULL, N'
|
|
CREATE TABLE #ErtekelesTipusIdList (
|
|
Id int
|
|
)
|
|
INSERT INTO #ErtekelesTipusIdList
|
|
SELECT value AS Id FROM string_split(''' + @pTanuloErtekelesTipusIdList + ''', '','')
|
|
','')
|
|
|
|
SET @sql += IIF(@pEvfolyamTipusIdList IS NOT NULL, N'
|
|
CREATE TABLE #EvfolyamTipusIdList (
|
|
Id int
|
|
)
|
|
INSERT INTO #EvfolyamTipusIdList
|
|
SELECT value AS Id FROM string_split(''' + @pEvfolyamTipusIdList + ''', '','')
|
|
', '')
|
|
|
|
SET @sql += IIF(@pTantargyTipusIdJSON IS NOT NULL, N'
|
|
CREATE TABLE #TantargyTipus (
|
|
TantargyId int,
|
|
TipusId int
|
|
)
|
|
INSERT INTO #TantargyTipus
|
|
SELECT
|
|
CASE
|
|
WHEN TantargyId > 0 THEN TantargyId
|
|
ELSE NULL
|
|
END
|
|
,TipusId
|
|
FROM OPENJSON(@pTantargyTipusIdJSON)
|
|
WITH (
|
|
TantargyId int ''$.Item1'' ,
|
|
TipusId int ''$.Item2''
|
|
)', '')
|
|
|
|
SET @sql += N'
|
|
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'''
|
|
SET @sql += IIF(@pTantargyTipusIdJSON IS NOT NULL, N'
|
|
INNER JOIN #TantargyTipus tantip ON tantip.TipusId = tem.C_TIPUSID
|
|
AND ISNULL(tantip.TantargyId, -1) = ISNULL(tem.C_TANTARGYID, -1)
|
|
', '')
|
|
set @sql += IIF(@pTanuloErtekelesTipusIdList IS NOT NULL, N'
|
|
INNER JOIN #ErtekelesTipusIdList idlisttet ON (idListtet.Id != 0 AND idlisttet.Id = tem.C_ERTEKELESTIPUSA)
|
|
OR (idlisttet.Id = 0 AND tem.C_ERTEKELESTIPUSA IS NULL)
|
|
', '')
|
|
set @sql += IIF(@pEvfolyamTipusIdList IS NOT NULL, N'
|
|
INNER JOIN #EvfolyamTipusIdList idlistet ON (idListet.Id != 0 AND idlistet.Id = tem.C_EVFOLYAMTIPUSA)
|
|
OR (idlistet.Id = 0 AND tem.C_EVFOLYAMTIPUSA IS NULL)
|
|
', '')
|
|
|
|
SET @sql += N'WHERE tem.C_TANEVID = @pTanevId
|
|
AND tem.TOROLT = ''F'''
|
|
|
|
SET @sql += IIF ( @pRovidNev IS NOT NULL, N'
|
|
AND tem.C_ROVIDNEV = @pRovidNev', N'')
|
|
|
|
SET @sql += IIF (@pSzoveg IS NOT NULL, N'
|
|
AND tem.C_SZOVEG LIKE ''%'' + @pSzoveg + ''%''', N'')
|
|
|
|
EXEC sys.sp_executesql @sql, N'
|
|
@pTanevId int
|
|
,@pTanuloErtekelesTipusIdList nvarchar
|
|
,@pEvfolyamTipusIdList nvarchar
|
|
,@pTantargyTipusIdJSON nvarchar(max)
|
|
,@pRovidNev nvarchar(3)
|
|
,@pSzoveg nvarchar(4000)'
|
|
,@pTanevId = @pTanevId
|
|
,@pTanuloErtekelesTipusIdList = @pTanuloErtekelesTipusIdList
|
|
,@pEvfolyamTipusIdList = @pEvfolyamTipusIdList
|
|
,@pTantargyTipusIdJSON = @pTantargyTipusIdJSON
|
|
,@pRovidNev = @pRovidNev
|
|
,@pSzoveg = @pSzoveg
|
|
END
|
|
|
|
GO
|
|
|