DROP PROCEDURE IF EXISTS uspGetHazifeladatData
GO

CREATE PROCEDURE uspGetHazifeladatData
   @pTanevId int
  ,@pOraDatumaTol datetime = NULL
  ,@pOraDatumaIg datetime = NULL
  ,@pTanarId int = NULL
  ,@pTantargyId int = NULL
  ,@pOraTema nvarchar(max) = NULL
  ,@pOsztalyCsoportIds nvarchar(max) = NULL
  ,@pHazifeladatSzoveg nvarchar(max) = NULL
  ,@pRogzitesTol datetime = NULL
  ,@pRogzitesIg datetime = NULL
  ,@pHataridoTol datetime = NULL
  ,@pHataridoIg datetime = NULL
  ,@pMindegyikHetTipusId int = 1554
  ,@pFeladatKategoriaId int = NULL
  ,@pFeladatEllatasiHelyId int = NULL
  ,@pIsOnlineOra bit = NULL
  ,@pFeladatTipusId int = NULL
AS BEGIN
  SET NOCOUNT ON;
  
  DECLARE @sql nvarchar(max)

  SET @sql = N'
    SELECT
       oo.ID AS ID
      ,hf.C_DATUM AS OraDatuma 
      ,oo.C_ORASZAM AS OraSorszama
      ,hf.C_BEADASHATARIDO AS HaziFeladatHataridoDatuma
      ,hf.C_ROGZITESIDOPONT AS HaziFeladatRogzitesDatuma
      ,ISNULL(helyettesf.C_NYOMTATASINEV,fh.C_NYOMTATASINEV) AS Tanar
      ,ISNULL(helyettesf.ID,fh.ID) AS TanarId
      ,tgy.C_NEV AS Tantargy
      ,tgy.ID AS TantargyId
      ,NULL AS OraTema
      ,hf.C_SZOVEG AS TanarHazifeladat
      ,ocs.C_NEV AS OsztalyCsoport
      ,ocs.ID AS OsztalyCsoportId
      ,hf.ID AS HazifeladatId
      ,ISNULL(ootOnlineOra.C_BOOLERTEK, ottOnlineOra.C_BOOLDEFAULT) AS IsOnlineOra
      ,C_FELADATTIPUSID AS FeladatTipusId
      ,''F'' AS IsTanitasiOra
      ,oo.TOROLT AS IsToroltOra
    FROM T_DKT_FELADAT_OSSZES hf
      INNER JOIN T_ORARENDIORA_OSSZES oo ON oo.ID = hf.C_ORARENDIORAGROUPID
      INNER JOIN T_TANTARGY_OSSZES tgy ON tgy.ID = oo.C_TANTARGYID AND tgy.TOROLT = ''F''
      LEFT JOIN T_HELYETTESITESIIDOSZAK_OSSZES hisz ON hisz.C_HELYETTESITETTORARENDID = oo.ID 
        AND hisz.C_HELYETTESITESNAPJA = CAST(hf.C_ROGZITESIDOPONT AS date)
        AND hisz.TOROLT = ''F''
      LEFT JOIN T_FELHASZNALO_OSSZES helyettesf ON helyettesf.ID = hisz.C_HELYETTESTANAROKID AND helyettesf.TOROLT = ''F''
      INNER JOIN T_FELHASZNALO_OSSZES fh ON fh.ID = oo.C_TANARID AND fh.TOROLT = ''F''
      INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = oo.C_OSZTALYCSOPORTID AND ocs.TOROLT = ''F''
      INNER JOIN T_ORATULAJDONSAGTIPUS_OSSZES ottOnlineOra ON ottOnlineOra.TOROLT = ''F'' AND ottOnlineOra.C_ALTANEVID = oo.C_TANEVID AND ottOnlineOra.ID = 8615
      LEFT JOIN T_ORARENDIORATULAJDONSAG_OSSZES ootOnlineOra ON ootOnlineOra.C_ORATULAJDONSAGID = ottOnlineOra.ID AND ootOnlineOra.C_TANEVID = ottOnlineOra.C_ALTANEVID AND ootOnlineOra.TOROLT = ''F'' AND ootOnlineOra.C_ORARENDIORAID = oo.ID
    WHERE hf.TOROLT = ''F'' 
      AND hf.C_TANITASIORAID IS NULL
      AND hf.C_TANEVID = @pTanevId'

  SET @sql += IIF(@pOraDatumaTol IS NOT NULL, N'
      AND hf.C_DATUM >= @pOraDatumaTol ', '' )
  SET @sql += IIF(@pOraDatumaIg IS NOT NULL, N'
      AND hf.C_DATUM <= @pOraDatumaIg ', '' )   
  SET @sql += IIF(@pTantargyId IS NOT NULL, N'
      AND tgy.ID = @pTantargyId', '')
  SET @sql += IIF(@pOsztalyCsoportIds IS NOT NULL, N'
      AND ocs.ID IN (' + @pOsztalyCsoportIds + ')', '')
  SET @sql += IIF(@pFeladatKategoriaId IS NULL, N'', N' 
      AND ocs.C_FELADATKATEGORIAID = @pFeladatKategoriaId')
  SET @sql += IIF(@pFeladatEllatasiHelyId IS NULL, N'', N' 
      AND ocs.C_FELADATELLATASIHELYID = @pFeladatEllatasiHelyId')
  SET @sql += IIF(@pHazifeladatSzoveg IS NOT NULL, N'
      AND hf.C_SZOVEG LIKE ''%'' + @pHazifeladatSzoveg + ''%''', '')
  SET @sql += IIF(@pRogzitesTol IS NOT NULL, N'
      AND hf.C_ROGZITESIDOPONT >= @pRogzitesTol', '')
  SET @sql += IIF(@pRogzitesIg IS NOT NULL, N'
      AND hf.C_ROGZITESIDOPONT < @pRogzitesIg + 1', '')
  SET @sql += IIF(@pHataridoTol IS NOT NULL, N'
      AND hf.C_BEADASHATARIDO >= @pHataridoTol', '')
  SET @sql += IIF(@pHataridoIg IS NOT NULL, N'
      AND hf.C_BEADASHATARIDO <= @pHataridoIg', '')
  SET @sql += IIF(@pIsOnlineOra IS NOT NULL, N'
      AND ISNULL(totOnlineOra.C_BOOLERTEK, ottOnlineOra.C_BOOLDEFAULT) = ' + IIF(@pIsOnlineOra = 1, '''T''', '''F'''), '')
  SET @sql += IIF(@pTanarId IS NOT NULL, N'
      AND (helyettesf.ID = @pTanarId OR fh.ID = @pTanarId)', '')
  SET @sql += IIF(@pFeladatTipusId IS NULL, N'', N' 
     AND hf.C_FELADATTIPUSID = @pFeladatTipusId')
  SET @sql += N'
    
    UNION ALL
    SELECT
       tn.ID AS ID
      ,tn.C_DATUM AS OraDatuma
      ,tn.C_ORASZAM AS OraSorszama
      ,hf.C_BEADASHATARIDO AS HaziFeladatHataridoDatuma
      ,hf.C_ROGZITESIDOPONT AS HaziFeladatRogzitesDatuma
      ,ISNULL(helyettesf.C_NYOMTATASINEV, fh.C_NYOMTATASINEV) AS Tanar
      ,ISNULL(helyettesf.ID, fh.ID) AS TanarId
      ,tgy.C_NEV AS Tantargy
      ,tgy.ID AS TantargyId
      ,tn.C_TEMA AS OraTema
      ,hf.C_SZOVEG AS TanarHazifeladat
      ,ocs.C_NEV AS OsztalyCsoport
      ,ocs.ID AS OsztalyCsoportId
      ,hf.ID AS HazifeladatId
      ,ISNULL(totOnlineOra.C_BOOLERTEK, ottOnlineOra.C_BOOLDEFAULT) AS IsOnlineOra
      ,C_FELADATTIPUSID AS FeladatTipusId
      ,''T'' AS IsTanitasiOra
      ,tn.TOROLT AS IsToroltOra
    FROM T_DKT_FELADAT_OSSZES hf
      INNER JOIN T_TANITASIORA_OSSZES tn ON tn.ID = hf.C_TANITASIORAID
      INNER JOIN T_TANTARGY_OSSZES tgy ON tgy.ID = tn.C_TANTARGYID AND tgy.TOROLT = ''F''
      LEFT JOIN T_FELHASZNALO_OSSZES helyettesf ON helyettesf.ID = tn.C_HELYETTESITOTANARID AND helyettesf.TOROLT = ''F''
      INNER JOIN T_FELHASZNALO_OSSZES fh ON fh.ID = tn.C_TANARID AND fh.TOROLT = ''F''
      INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = hf.C_OSZTALYCSOPORTID AND ocs.TOROLT = ''F''
      INNER JOIN T_ORATULAJDONSAGTIPUS_OSSZES ottOnlineOra ON ottOnlineOra.TOROLT = ''F'' AND ottOnlineOra.C_ALTANEVID = tn.C_TANEVID AND ottOnlineOra.ID = 8615
      LEFT JOIN T_TANITASIORATULAJDONSAG_OSSZES totOnlineOra ON totOnlineOra.C_ORATULAJDONSAGID = ottOnlineOra.ID AND totOnlineOra.C_TANEVID = ottOnlineOra.C_ALTANEVID AND totOnlineOra.TOROLT = ''F'' AND totOnlineOra.C_TANITASIORAID = tn.ID
    WHERE hf.TOROLT = ''F''
      AND hf.C_TANEVID = @pTanevId'

  SET @sql += IIF(@pTantargyId IS NOT NULL, N'
      AND tgy.ID = @pTantargyId', '')
  SET @sql += IIF(@pOsztalyCsoportIds IS NOT NULL, N'
      AND ocs.ID IN (' + @pOsztalyCsoportIds + ')', '')
  SET @sql += IIF(@pFeladatKategoriaId IS NULL, N'', N' 
      AND ocs.C_FELADATKATEGORIAID = @pFeladatKategoriaId')
  SET @sql += IIF(@pFeladatEllatasiHelyId IS NULL, N'', N' 
      AND ocs.C_FELADATELLATASIHELYID = @pFeladatEllatasiHelyId')
  SET @sql += IIF(@pHazifeladatSzoveg IS NOT NULL, N'
      AND hf.C_SZOVEG LIKE ''%'' + @pHazifeladatSzoveg + ''%''', '')
  SET @sql += IIF(@pRogzitesTol IS NOT NULL, N'
      AND hf.C_ROGZITESIDOPONT >= @pRogzitesTol', '')
  SET @sql += IIF(@pRogzitesIg IS NOT NULL, N'
      AND hf.C_ROGZITESIDOPONT < @pRogzitesIg + 1', '')
  SET @sql += IIF(@pHataridoTol IS NOT NULL, N'
      AND hf.C_BEADASHATARIDO >= @pHataridoTol', '')
  SET @sql += IIF(@pHataridoIg IS NOT NULL, N'
      AND hf.C_BEADASHATARIDO <= @pHataridoIg', '')
 SET @sql += IIF(@pOraDatumaTol IS NOT NULL, N'
      AND tn.C_DATUM >= @pOraDatumaTol', '')
  SET @sql += IIF(@pOraDatumaIg IS NOT NULL, N'
      AND tn.C_DATUM <= @pOraDatumaIg', '')
  SET @sql += IIF(@pOraTema IS NOT NULL, N'
      AND tn.C_TEMA LIKE ''%'' + @pOraTema + ''%''', '')
  SET @sql += IIF(@pIsOnlineOra IS NOT NULL, N'
      AND ISNULL(totOnlineOra.C_BOOLERTEK, ottOnlineOra.C_BOOLDEFAULT) = ' + IIF(@pIsOnlineOra = 1, '''T''', '''F'''), '')
  SET @sql += IIF(@pTanarId IS NOT NULL, N'
      AND (helyettesf.ID = @pTanarId OR fh.ID = @pTanarId)', '')
  SET @sql += IIF(@pFeladatTipusId IS NULL, N'', N' 
     AND hf.C_FELADATTIPUSID = @pFeladatTipusId')

EXEC sp_executesql @sql, N'
   @pTanevId int
  ,@pOraDatumaTol datetime 
  ,@pOraDatumaIg datetime 
  ,@pTanarId int 
  ,@pTantargyId int 
  ,@pOraTema nvarchar(max) 
  ,@pHazifeladatSzoveg nvarchar(max) 
  ,@pRogzitesTol datetime 
  ,@pRogzitesIg datetime 
  ,@pHataridoTol datetime 
  ,@pHataridoIg datetime
  ,@pMindegyikHetTipusId int
  ,@pFeladatKategoriaId int
  ,@pFeladatEllatasiHelyId int
  ,@pFeladatTipusId int'
  ,@pTanevId = @pTanevId
  ,@pOraDatumaTol = @pOraDatumaTol
  ,@pOraDatumaIg = @pOraDatumaIg
  ,@pTanarId = @pTanarId
  ,@pTantargyId = @pTantargyId
  ,@pOraTema = @pOraTema
  ,@pHazifeladatSzoveg = @pHazifeladatSzoveg
  ,@pRogzitesTol = @pRogzitesTol
  ,@pRogzitesIg = @pRogzitesIg
  ,@pHataridoTol = @pHataridoTol
  ,@pHataridoIg = @pHataridoIg
  ,@pMindegyikHetTipusId = @pMindegyikHetTipusId
  ,@pFeladatKategoriaId = @pFeladatKategoriaId
  ,@pFeladatEllatasiHelyId = @pFeladatEllatasiHelyId
  ,@pFeladatTipusId = @pFeladatTipusId

END
GO