DROP PROCEDURE IF EXISTS [dbo].[uspGetTanevKozbenKilepettTanulok]
GO

CREATE PROCEDURE [dbo].[uspGetTanevKozbenKilepettTanulok]
  @tanevID int
AS
BEGIN
SET NOCOUNT ON;

  SELECT 
    C_NEV AS OsztalyNev
  FROM T_OSZTALY_OSSZES o
    INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs on ocs.ID = o.ID AND ocs.TOROLT = 'F' AND ocs.C_TANEVID = @tanevID
  WHERE o.TOROLT = 'F' AND o.C_ALTANEVID = @tanevId

  SELECT 
     ocs.C_NEV AS COLUMN379
    ,f.C_NYOMTATASINEV AS COLUMN20
    ,f.C_OKTATASIAZONOSITO AS TanuloOktAzon
    ,FORMAT(tcs.C_BELEPESDATUM, 'yyyy.MM.dd.') AS COLUMN3784
    ,FORMAT(tcs.C_KILEPESDATUM, 'yyyy.MM.dd.') AS COLUMN1
    ,STUFF((
      SELECT CAST(', ' + C_SZOVEG AS NVARCHAR(MAX)) FROM T_ZARADEK_OSSZES z
      WHERE
                z.C_TANULOCSOPORTID = tcs.ID
                AND C_ISKIVAGYATSOROLASIZARADEK = 'T'
                AND TOROLT = 'F'
                AND C_TANEVID = @tanevID
      FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,2,'') AS COLUMN1557
    ,o.ID AS OsztalyId
    ,f.ID AS TanuloId
  FROM T_TANULOCSOPORT_OSSZES tcs
      INNER JOIN T_OSZTALY_OSSZES o ON tcs.C_OSZTALYCSOPORTID = o.ID AND o.TOROLT = 'F' AND o.C_ALTANEVID = @tanevID
      INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON o.ID = ocs.ID AND ocs.TOROLT = 'F' AND ocs.C_TANEVID = @tanevID
      INNER JOIN T_FELHASZNALO_OSSZES f ON f.ID = tcs.C_TANULOID AND f.TOROLT = 'F' AND f.C_TANEVID = @tanevID
  WHERE
      tcs.C_KILEPESDATUM IS NOT NULL
      AND tcs.C_TANEVID = @tanevId
      AND tcs.TOROLT = 'F'
  ORDER BY f.C_NYOMTATASINEV
   END

GO