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