DROP PROCEDURE IF EXISTS uspGetTanarTanitottTanuloi
GO

CREATE PROCEDURE uspGetTanarTanitottTanuloi
   @pTanarId int
  ,@pTanevId int
  ,@pBukasAtlag float
  ,@pCsakBukasraAllok bit = 0
  ,@pFoglalkozasId int = NULL
  ,@pFoglalkozasTipusId int = NULL
  ,@pFeladatKategoriaId int = NULL
AS
BEGIN

  SET NOCOUNT ON;

  DECLARE @sql nvarchar (max)

  SET @sql = N'
  SELECT
     fh.ID
    ,fh.C_NYOMTATASINEV AS TanuloNev
    ,IIF(fh.C_NEVSORREND = ''T'', fh.C_UTONEV + '' '' + fh.C_VEZETEKNEV, fh.C_VEZETEKNEV + '' '' + fh.C_UTONEV) AS TanuloNevElotagNelkul
    ,ocs.C_NEV AS FoglalkozasNev
    ,ocs.ID AS OsztalyCsoportId
    ,f.C_FOGLALKOZASTIPUSA AS Tipus
    ,f.C_TANTARGYID AS TantargyId
    ,tgy.C_NEV AS TantargyNev
  FROM T_FOGLALKOZAS_OSSZES f
    INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON f.C_OSZTALYCSOPORTID = ocs.ID
      AND ocs.C_TANEVID = f.C_TANEVID
      AND ocs.TOROLT = ''F''
    INNER JOIN T_TANULOCSOPORT_OSSZES tcs ON ocs.ID = tcs.C_OSZTALYCSOPORTID
      AND tcs.C_BELEPESDATUM < GETDATE()
      AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM >= GETDATE())
      AND tcs.C_TANEVID = f.C_TANEVID
      AND tcs.TOROLT = ''F''
    INNER JOIN T_FELHASZNALO_OSSZES fh ON tcs.C_TANULOID = fh.ID
      AND fh.C_TANEVID = f.C_TANEVID
      AND fh.TOROLT = ''F''
    INNER JOIN T_TANTARGY_OSSZES tgy ON tgy.ID = f.C_TANTARGYID
      AND tgy.C_TANEVID = f.C_TANEVID
      AND tgy.TOROLT = ''F'''

  IF @pCsakBukasraAllok = 1
    SET @sql += N'
    INNER JOIN T_TANULOERTEKELES_OSSZES te ON te.C_OSZTALYCSOPORTID = f.C_OSZTALYCSOPORTID
      AND te.C_ERTEKELESOSZTALYZATID IS NOT NULL
      AND te.C_TANTARGYID = f.C_TANTARGYID
      AND te.C_TANULOID = fh.ID
      AND te.C_TANEVID = f.C_TANEVID
      AND te.TOROLT = ''F''
    INNER JOIN T_OSZTALYZATTIPUS_OSSZES ot ON te.C_ERTEKELESOSZTALYZATID = ot.ID
      AND ot.C_ALTANEVID = f.C_TANEVID
      AND ot.TOROLT = ''F''
    INNER JOIN T_DICTIONARYITEMBASE_OSSZES d ON ot.ID = d.ID
      AND d.C_TANEVID = f.C_TANEVID
      AND d.TOROLT = ''F''
    LEFT JOIN T_ERTEKELESMOD_OSSZES em ON em.ID = te.C_ERTEKELESMODID
      AND em.C_ALTANEVID = f.C_TANEVID
      AND em.TOROLT = ''F'''

  SET @sql += N'
  WHERE f.C_TANEVID = @pTanevId
    AND f.TOROLT = ''F''
    AND f.C_TANARID = @pTanarId
    ' + IIF(@pFoglalkozasId IS NOT NULL, 'AND f.ID = @pFoglalkozasId', '') + '
    ' + IIF(@pFoglalkozasTipusId IS NOT NULL, 'AND f.C_FOGLALKOZASTIPUSA = @pFoglalkozasTipusId', '') + '
    ' + IIF(@pFeladatKategoriaId IS NOT NULL, 'AND ocs.C_FELADATKATEGORIAID = @pFeladatKategoriaId', '') + ''

  IF @pCsakBukasraAllok = 1
    SET @sql += N'
  GROUP BY
     fh.ID
    ,fh.C_NYOMTATASINEV
    ,IIF(fh.C_NEVSORREND = ''T'', fh.C_UTONEV + '' '' + fh.C_VEZETEKNEV, fh.C_VEZETEKNEV + '' '' + fh.C_UTONEV)
    ,ocs.C_NEV
    ,ocs.ID
    ,f.C_FOGLALKOZASTIPUSA
    ,f.C_TANTARGYID
    ,tgy.C_NEV
  HAVING ROUND(SUM(CAST(d.C_VALUE AS float) * CAST(ISNULL(em.C_SULY, 100) AS float) / 100) / SUM(CAST(ISNULL(em.C_SULY, 100) AS float) / 100), 2) < @pBukasAtlag'

  EXEC sp_executesql @sql, N'
     @pTanarId int
    ,@pTanevId int
    ,@pBukasAtlag float
    ,@pFoglalkozasId int
    ,@pFoglalkozasTipusId int
    ,@pFeladatKategoriaId int'
    ,@pTanarId = @pTanarId
    ,@pTanevId = @pTanevId
    ,@pBukasAtlag = @pBukasAtlag
    ,@pFoglalkozasId = @pFoglalkozasId
    ,@pFoglalkozasTipusId = @pFoglalkozasTipusId
    ,@pFeladatKategoriaId = @pFeladatKategoriaId

END

GO