44 lines
No EOL
1.7 KiB
Transact-SQL
44 lines
No EOL
1.7 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspGetFeladatokByDateRange
|
|
GO
|
|
|
|
CREATE PROCEDURE uspGetFeladatokByDateRange
|
|
@pIntezmenyId int
|
|
,@pTanevId int
|
|
,@pDatumTol datetime = NULL
|
|
,@pDatumIg datetime = NULL
|
|
,@pFeladatTipus int = NULL -- NULL - Összes feladat, 0 - Házi Feladat, 1 - Órai feladat, 2 - E-learning feladat, 3 - Nyelvi feladat
|
|
AS BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT
|
|
f.ID AS ID
|
|
,f.C_DATUM AS Datum
|
|
,COALESCE(t.C_ORARENDIORAGROUPID, oo.C_ORARENDIORAGROUPID) AS OrarendiOraGroupId
|
|
,oo.C_ORARENDIORAID
|
|
,f.C_TANITASIORAID AS TanitasiOraId
|
|
,f.C_BEADASHATARIDO AS BeadasHatarido
|
|
,f.C_LATHATOSAGIDOPONT LathatosagIdopont
|
|
,f.C_TANTARGYID AS Tantargy
|
|
,f.C_ALKALMAZOTTID AS Tanar
|
|
,f.C_OSZTALYCSOPORTID AS OsztalyCsoport
|
|
,f.C_ORASZAM AS Oraszam
|
|
,f.C_IDOPONT AS Idopont
|
|
,f.C_FELADATTIPUSID AS FeladatTipusId
|
|
,f.C_GROUPID AS GroupId
|
|
FROM T_DKT_FELADAT f
|
|
LEFT JOIN T_TANITASIORA t ON t.ID = f.C_TANITASIORAID AND t.TOROLT = 'F'
|
|
LEFT JOIN T_ORARENDTELJES oo ON oo.C_TANEVID = f.C_TANEVID
|
|
AND oo.C_INTEZMENYID = f.C_INTEZMENYID
|
|
AND oo.C_TANTARGYID = f.C_TANTARGYID
|
|
AND oo.C_OSZTALYCSOPORTID = f.C_OSZTALYCSOPORTID
|
|
AND oo.C_TANARID = f.C_ALKALMAZOTTID
|
|
AND oo.C_DATUM = f.C_DATUM
|
|
AND (oo.C_ORASZAM = f.C_ORASZAM OR CAST(oo.C_ORAKEZDETE AS time(0)) = CAST(f.C_IDOPONT AS time(0)))
|
|
WHERE f.TOROLT = 'F'
|
|
AND f.C_DATUM BETWEEN @pDatumtol AND @pDatumig
|
|
AND f.C_INTEZMENYID = @pIntezmenyId
|
|
AND f.C_TANEVID = @pTanevId
|
|
AND (@pFeladatTipus IS NULL OR f.C_FELADATTIPUSID = @pFeladatTipus)
|
|
|
|
END
|
|
GO |