89 lines
3.3 KiB
Transact-SQL
89 lines
3.3 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspGetTanuloOsztalyCsoportjai
|
|
GO
|
|
|
|
CREATE PROCEDURE uspGetTanuloOsztalyCsoportjai
|
|
@pTanuloIds nvarchar (max)
|
|
,@pTanevId int
|
|
,@pIsAktiv bit
|
|
,@pFeladatKategoriaId int
|
|
,@pOsztalyfonokIds nvarchar (max)
|
|
,@pOktatasiNevelesiFeladatId int = null
|
|
AS BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE
|
|
@tanevNev nvarchar (30)
|
|
,@sql nvarchar (max) = N''
|
|
|
|
SELECT
|
|
@tanevNev = C_NEV
|
|
FROM T_TANEV
|
|
WHERE ID = @pTanevId
|
|
IF @pTanuloIds IS NOT NULL
|
|
BEGIN
|
|
SET @sql = N'
|
|
CREATE TABLE #TanuloIds(TanuloId int PRIMARY KEY)
|
|
INSERT INTO #TanuloIds(TanuloId)
|
|
SELECT value FROM STRING_SPLIT(@pTanuloIds, '','')
|
|
'
|
|
END
|
|
|
|
SET @sql += N'
|
|
SELECT DISTINCT
|
|
CASE WHEN @pTanuloIds IS NULL THEN -1 ELSE tcs.ID END AS ID
|
|
,tcs.C_OSZTALYCSOPORTID AS OsztalyCsoportId
|
|
,ocs.C_NEV AS Nev
|
|
,ocs.C_EVFOLYAMTIPUSA AS EvfolyamTipus
|
|
,ocs.C_FELADATKATEGORIAID AS FeladatKategoriaId
|
|
,feh.C_OKTATASINEVELESIFELADATTIPUS AS OktatasiNevelesiFeladatId
|
|
,@tanevNev AS Tanev
|
|
,CASE WHEN @pOsztalyfonokIds IS NULL THEN tcs.C_TANULOID ELSE -1 END AS TanuloId
|
|
,tcs.C_BELEPESDATUM AS ErvKezd
|
|
,tcs.C_KILEPESDATUM AS ErvVeg
|
|
,cs.C_TIPUSA AS CsoportTipus
|
|
,ob.C_NEV AS Osztalybontas
|
|
,ocs.C_KEPZESIFORMA AS KepzesiForma
|
|
,CASE WHEN o.ID IS NOT NULL THEN ''F'' ELSE ''T'' END AS IsCsoport
|
|
,o.C_OSZTALYFONOKID AS OsztalyfonokId
|
|
,o.C_OFOHELYETTESID AS OsztalyfonokHelyettesId
|
|
,cs.C_CSOPORTVEZETOID AS CsoportVezetoId
|
|
FROM T_TANULOCSOPORT_OSSZES tcs
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = tcs.C_OSZTALYCSOPORTID '+IIF(@pFeladatKategoriaId IS NULL,'','AND ocs.C_FELADATKATEGORIAID = @pFeladatKategoriaId )')+N'
|
|
AND ocs.TOROLT = ''F''
|
|
AND ocs.C_TANEVID = tcs.C_TANEVID
|
|
INNER JOIN T_FELADATELLATASIHELY feh ON ocs.C_FELADATELLATASIHELYID = feh.ID
|
|
AND feh.TOROLT = ''F''
|
|
AND feh.C_TANEVID = tcs.C_TANEVID
|
|
'+IIF(@pTanuloIds IS NULL,'','INNER JOIN #TanuloIds tanuloIds ON tanuloIds.TanuloId = tcs.C_TANULOID')+N'
|
|
LEFT JOIN T_CSOPORT_OSSZES cs ON cs.ID = ocs.ID
|
|
AND cs.TOROLT = ''F''
|
|
AND cs.C_ALTANEVID = tcs.C_TANEVID
|
|
LEFT JOIN T_OSZTALYCSOPORT_OSSZES ob ON ob.ID = cs.C_OSZTALYBONTASID
|
|
AND ob.TOROLT = ''F''
|
|
AND ob.C_TANEVID = tcs.C_TANEVID
|
|
LEFT JOIN T_OSZTALY_OSSZES o ON ocs.ID = o.ID
|
|
AND o.TOROLT = ''F''
|
|
AND o.C_ALTANEVID = tcs.C_TANEVID
|
|
WHERE tcs.TOROLT = ''F''
|
|
AND tcs.C_TANEVID = @tanevId'
|
|
+ IIF(@pOsztalyfonokIds IS NOT NULL, ' AND o.C_OSZTALYFONOKID IN (SELECT value FROM STRING_SPLIT(@pOsztalyfonokIds, '',''))', '')
|
|
+ IIF(@pOktatasiNevelesiFeladatId IS NOT NULL, ' AND feh.C_OKTATASINEVELESIFELADATTIPUS = @pOktatasiNevelesiFeladatId', '')
|
|
+ IIF(@pIsAktiv = 1, ' AND tcs.C_BELEPESDATUM <= GETDATE() AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM > GETDATE())', '')
|
|
|
|
EXEC sp_executesql @sql, N'
|
|
@pTanuloIds nvarchar (max)
|
|
,@tanevNev nvarchar (30)
|
|
,@tanevId int
|
|
,@pFeladatKategoriaId int
|
|
,@pOsztalyfonokIds nvarchar (max)
|
|
,@pOktatasiNevelesiFeladatId int'
|
|
,@pTanuloIds = @pTanuloIds
|
|
,@tanevNev = @tanevNev
|
|
,@tanevId = @pTanevId
|
|
,@pFeladatKategoriaId = @pFeladatKategoriaId
|
|
,@pOsztalyfonokIds = @pOsztalyfonokIds
|
|
,@pOktatasiNevelesiFeladatId = @pOktatasiNevelesiFeladatId
|
|
|
|
END
|
|
GO
|