DROP PROCEDURE IF EXISTS uspGetOsztalyData
GO

CREATE PROCEDURE uspGetOsztalyData
   @pSearchDate datetime
  ,@pTanevId int
AS
BEGIN
  SET NOCOUNT ON;
  
  SELECT
     o.ID
    ,o.C_ALINTEZMENYID
    ,ocs.C_NEV AS OsztalyNev
    ,ocs.C_VEGZOSEVFOLYAM AS VegzosEvFolyam
    ,ofo.C_NYOMTATASINEV AS OsztalyFonok
    ,IIF(ofo.C_NEVSORREND = 'T',
      ofo.C_UTONEV + ' ' + ofo.C_VEZETEKNEV,
      ofo.C_VEZETEKNEV + ' ' + ofo.C_UTONEV) AS OsztalyFonokElotagNelkul
    ,t.C_NEV AS TanevNev
    ,ocs.C_EVFOLYAMTIPUSA AS Evfolyam
    ,CONCAT(ISNULL(TanulokSzama,0), ' (', ISNULL(tcsArchiv.ArchivTanulokSzama, 0), ')') AS TanulokSzama
    ,ISNULL(TanulokSzama, 0) * 1000 + ISNULL(tcsArchiv.ArchivTanulokSzama, 0) AS TanulokSzamaOrder
    ,tt.C_NEV AS TanTerv
    ,te.C_NEV AS TeremNev
    ,de.C_VALUE AS EvfolyamSorszama
    ,ISNULL(cs.CsoportokSzama, 0) AS CsoportokSzama
    ,ocs.C_MUVESZETIAGID AS MuveszetiAgId
    ,ocs.C_FELADATKATEGORIAID AS FeladatKategoriaId
    ,ofo.ID AS OsztalyfonokId
    ,ofoh.ID AS OsztalyfonokHelyettesId
    ,te.ID AS TeremId
    ,ocs.C_KEPZESIFORMA AS KepzesId
    ,tt.ID AS TantervId
    ,o.C_OSZTALYNAPLOMEGNYITASA AS OsztalynaploNyitasa
    ,o.C_OSZTALYNAPLOZARASA AS OsztalynaploZarasa
    ,ocs.C_FELADATELLATASIHELYID AS FeladatEllatasiHelyId
  FROM T_OSZTALY_OSSZES o WITH(NOLOCK)
    INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs WITH(NOLOCK) ON ocs.ID = o.ID AND ocs.TOROLT = 'F'
    LEFT JOIN T_TEREM_OSSZES te ON te.ID = ocs.C_TEREMID AND te.TOROLT = 'F'
    LEFT JOIN T_FELHASZNALO_OSSZES ofo ON ofo.ID = o.C_OSZTALYFONOKID AND ofo.TOROLT = 'F'
    LEFT JOIN T_FELHASZNALO_OSSZES ofoh ON ofoh.ID = o.C_OSZTALYFONOKID AND ofoh.TOROLT = 'F'
    INNER JOIN T_TANEV_OSSZES t ON t.ID = ocs.C_TANEVID
      AND t.ID = @pTanevId
      AND t.TOROLT = 'F'
    LEFT JOIN T_TANTERV_OSSZES tt on tt.ID = o.C_TANTERVID and tt.TOROLT = 'F'
    LEFT JOIN T_DICTIONARYITEMBASE_OSSZES de on de.ID = ocs.C_EVFOLYAMTIPUSA
      AND de.C_TANEVID = ocs.C_TANEVID
      AND de.TOROLT = 'F'
    LEFT JOIN
    (
      SELECT 
        COUNT(DISTINCT C_TANULOID) AS TanulokSzama
        ,C_OSZTALYCSOPORTID
      FROM T_TANULOCSOPORT_OSSZES tcs
        JOIN T_TANEV_OSSZES tanev on tanev.ID = tcs.C_TANEVID AND tanev.TOROLT = 'F'
      WHERE tcs.TOROLT='F'
        AND 
        (
          (
            tcs.C_BELEPESDATUM <= @pSearchDate 
            AND 
            (
              tcs.C_KILEPESDATUM IS NULL or tcs.C_KILEPESDATUM >= CONVERT(DATE, @pSearchDate)
            )
            AND tanev.C_AKTIV = 'T'
          )
          OR tanev.C_AKTIV = 'F'
        )
      GROUP BY tcs.C_OSZTALYCSOPORTID
    ) AS TC ON TC.C_OSZTALYCSOPORTID = ocs.ID
    LEFT JOIN 
    (
      SELECT 
         COUNT(1) AS ArchivTanulokSzama
        ,C_OSZTALYCSOPORTID
      FROM T_TANULOCSOPORT_OSSZES tcs 
      WHERE tcs.TOROLT = 'F'
      GROUP BY C_OSZTALYCSOPORTID
    ) tcsArchiv ON tcsArchiv.C_OSZTALYCSOPORTID = ocs.ID
    LEFT JOIN 
    (
      SELECT 
         COUNT(cs.ID) AS CsoportokSzama
        ,cs.C_OSZTALYBONTASID
        ,cs.C_ALINTEZMENYID
        ,cs.C_ALTANEVID
      FROM T_CSOPORT_OSSZES cs 
      WHERE cs.TOROLT = 'F'
      GROUP BY cs.C_OSZTALYBONTASID, cs.C_ALTANEVID, cs.C_ALINTEZMENYID
    ) cs ON cs.C_OSZTALYBONTASID = o.ID
    AND cs.C_ALTANEVID = o.C_ALTANEVID
    AND cs.C_ALINTEZMENYID = o.C_ALINTEZMENYID
  WHERE o.TOROLT = 'F'
    AND o.C_ALTANEVID = @pTanevId

END