DROP FUNCTION IF EXISTS fnGetDokumentumTanulokFeljegyzeseiHavibontasban
GO

CREATE FUNCTION fnGetDokumentumTanulokFeljegyzeseiHavibontasban (
   @tanevId int
  ,@osztalyId int
  ,@FelevVege date
  ,@BelepesDatum datetime
  ,@KilepesDatum datetime)
RETURNS @result TABLE (
   TanuloId int
  ,Tipus nvarchar (250)
  ,Szeptember int
  ,Oktober int
  ,November int
  ,December int
  ,Januar int
  ,Februar int
  ,Marcius int
  ,Aprilis int
  ,Majus int
  ,Junius int
  ,Felev int
  ,Ossz  int
)
AS BEGIN

  DECLARE @kategoriaId int = (SELECT C_FELADATKATEGORIAID FROM T_OSZTALYCSOPORT_OSSZES WHERE ID = @osztalyId)

  INSERT INTO @result

  SELECT
     tte.C_TANULOID AS TanuloId
    ,d.C_NAME AS Tipus
    ,SUM(CASE WHEN DATEPART(MONTH, te.C_ESEMENYDATUMA) =  9 THEN 1 ELSE 0 END) AS Szeptember
    ,SUM(CASE WHEN DATEPART(MONTH, te.C_ESEMENYDATUMA) = 10 THEN 1 ELSE 0 END) AS Oktober
    ,SUM(CASE WHEN DATEPART(MONTH, te.C_ESEMENYDATUMA) = 11 THEN 1 ELSE 0 END) AS November
    ,SUM(CASE WHEN DATEPART(MONTH, te.C_ESEMENYDATUMA) = 12 THEN 1 ELSE 0 END) AS December
    ,SUM(CASE WHEN DATEPART(MONTH, te.C_ESEMENYDATUMA) =  1 THEN 1 ELSE 0 END) AS Januar
    ,SUM(CASE WHEN DATEPART(MONTH, te.C_ESEMENYDATUMA) =  2 THEN 1 ELSE 0 END) AS Februar
    ,SUM(CASE WHEN DATEPART(MONTH, te.C_ESEMENYDATUMA) =  3 THEN 1 ELSE 0 END) AS Marcius
    ,SUM(CASE WHEN DATEPART(MONTH, te.C_ESEMENYDATUMA) =  4 THEN 1 ELSE 0 END) AS Aprilis
    ,SUM(CASE WHEN DATEPART(MONTH, te.C_ESEMENYDATUMA) =  5 THEN 1 ELSE 0 END) AS Majus
    ,SUM(CASE WHEN DATEPART(MONTH, te.C_ESEMENYDATUMA) =  6 THEN 1 ELSE 0 END) AS Junius
    ,SUM(CASE WHEN te.C_ESEMENYDATUMA <= @FelevVege THEN 1 ELSE 0 END) AS Felev
    ,SUM(1) AS Ossz
  FROM T_TANULO_TANULOESEMENY tte
    INNER JOIN T_TANULOESEMENY_OSSZES te ON te.ID = tte.C_TANULOESEMENYID AND te.TOROLT = 'F'
    INNER JOIN T_DICTIONARYITEMBASE_OSSZES d ON d.ID = te.C_TIPUS AND d.TOROLT = 'F' AND d.C_TANEVID = @tanevId
    LEFT JOIN  T_TANITASIORA_OSSZES tOra ON tOra.ID = te.C_TANITASIORAID AND tOra.Torolt = 'F'
    LEFT JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = tOra.C_OSZTALYCSOPORTID AND ocs.C_FELADATKATEGORIAID = @kategoriaId AND ocs.Torolt = 'F'
  WHERE
    tte.C_TANULOID IN (SELECT TanuloId FROM fnGetDokumentumOsztalyokCsoportokTanuloi(@tanevId, @osztalyId, 'T'))
    AND te.TOROLT = 'F'
    AND ((te.C_TANITASIORAID IS NOT NULL AND tOra.ID IS NOT NULL AND ocs.ID IS NOT NULL) OR te.C_TANITASIORAID IS NULL)
    AND @BelepesDatum <= te.C_ESEMENYDATUMA
    AND (@KilepesDatum IS NULL OR @KilepesDatum >= te.C_ESEMENYDATUMA)
    AND te.C_TIPUS != 5482
  GROUP BY tte.C_TANULOID, d.C_NAME

  RETURN
END
GO