DROP PROCEDURE IF EXISTS sp_GetOsztalyokEvesOraszama
GO

CREATE PROCEDURE sp_GetOsztalyokEvesOraszama
  @tanevID int,
  @tanitasiHetekSzamaVegzos int = 32,
  @tanitasiHetekSzamaNemVegzos int = 36,
  @isAltantargyBeszamitasa bit,
  @oktNevelesiKategoriaId int
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @osztalyok TABLE (
     OsztalyId INT
    ,OsztalyNev NVARCHAR(255)
  )

  DECLARE kurzor CURSOR FOR 
  SELECT
     o.ID AS OsztalyId
    ,ocs.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
    ocs.C_TANEVID  = @tanevId
    AND (ocs.C_FELADATKATEGORIAID = @oktNevelesiKategoriaId OR @oktNevelesiKategoriaId IS NULL)
    AND ocs.TOROLT = 'F'  

  DECLARE @OsztalyId int, @OsztalyNev nvarchar(255)

  OPEN kurzor
  FETCH NEXT FROM kurzor INTO @OsztalyId, @OsztalyNev

  WHILE @@FETCH_STATUS = 0 BEGIN
    SELECT DISTINCT
       tanulo.C_NYOMTATASINEV AS TanuloNev
      ,tanulo.C_OKTATASIAZONOSITO  AS OktAzon
      ,tantargy.C_NEV AS TantargyNev
      ,oraszamok.Oraszam AS EvesOraszam
      ,@OsztalyNev AS OsztalyNev
      ,@OsztalyId AS OsztalyId
      ,tantargy.C_SORSZAM as RENDEZ0
      ,CASE WHEN tantargy.C_FOTARGYID IS NULL THEN ISNULL(TargyKategoriaDictionary.C_ORDER, 10000) ELSE ISNULL(FotargyKategoriaDictionary.C_ORDER, 10000) END as RENDEZ1
      ,COALESCE(FotargyTable.C_NEVNYOMTATVANYBAN, FotargyTable.C_NEV, tantargy.C_NEV) as RENDEZ2
      ,ISNULL(tantargy.C_FOTARGYID, tantargy.ID) as RENDEZ3
      ,tantargy.C_FOTARGYID as RENDEZ4
      ,ISNULL(tantargy.C_NEVNYOMTATVANYBAN, tantargy.C_NEV) as RENDEZ5
    FROM fnGetDokumentumTanulokEvesOraszamaiOsztalyonkent(@tanevID, @OsztalyId, @tanitasiHetekSzamaVegzos, @tanitasiHetekSzamaNemVegzos, @isAltantargyBeszamitasa) oraszamok
      INNER JOIN T_FELHASZNALO_OSSZES tanulo ON tanulo.ID = oraszamok.TanuloId AND tanulo.C_TANEVID = @tanevID AND tanulo.TOROLT = 'F'
      INNER JOIN T_TANTARGY_OSSZES tantargy ON tantargy.ID = oraszamok.TantargyID AND tantargy.C_TANEVID = @tanevID AND tantargy.TOROLT = 'F'
      LEFT JOIN T_DICTIONARYITEMBASE_OSSZES TargyKategoriaDictionary ON TargyKategoriaDictionary.Id = tantargy.C_TARGYKATEGORIA
        AND TargyKategoriaDictionary.TOROLT = 'F'
        AND TargyKategoriaDictionary.C_TANEVID = @tanevID
      LEFT JOIN T_TANTARGY_OSSZES FotargyTable ON tantargy.C_FOTARGYID = FotargyTable.ID
      LEFT JOIN T_DICTIONARYITEMBASE_OSSZES FotargyKategoriaDictionary ON FotargyKategoriaDictionary.ID = FotargyTable.C_TARGYKATEGORIA
        AND FotargyKategoriaDictionary.TOROLT = 'F'
        AND FotargyKategoriaDictionary.C_TANEVID = FotargyTable.C_TANEVID
    ORDER BY TanuloNev, RENDEZ0, RENDEZ1, RENDEZ2, RENDEZ3, RENDEZ4, RENDEZ5, TantargyNev;
  FETCH NEXT FROM kurzor INTO @OsztalyId, @OsztalyNev
  END

  CLOSE kurzor
  DEALLOCATE kurzor
END 

GO