DROP PROCEDURE IF EXISTS uspSzirStatOsztaly
GO

CREATE PROCEDURE uspSzirStatOsztaly
   @pTanevId int
  ,@pIntezmenyid int
AS BEGIN
  
  DECLARE @datum datetime 

  SELECT @datum = LEFT(tv.C_NEV, 4) + '1001' FROM T_TANEV tv WHERE ID = @pTanevId
  
  SELECT 
     ocs.ID AS OsztalyId
    ,ocs.C_NEV AS OsztalyNev
    ,ia.C_OMKOD AS IntezmenyOmAzonosito
    ,ia.C_NEV AS IntezmenyNev
    ,ia.C_VAROS AS IntezenyTelepules
    ,ia.C_IRANYITOSZAM AS IntezmenyIrszam
    ,RTRIM(CONCAT(ia.C_KOZTERULETNEV, ' ', ia.C_KOZTERULETJELLEGENEV,' ', ia.C_HAZSZAM, ' ', ia.C_EMELET, ' ', ia.C_AJTO)) AS IntezmenyCim
    ,ISNULL(NULLIF(ia.C_EMAILCIM, ''), '-') AS IntezmenyEmailcim
    ,CONCAT(ia.C_OMKOD, '/', fhely.C_FELADATELLATASIHELYKOD) AS FelhelyAzonosito
    ,mhely.C_NEV AS FelhelyNev
    ,mhely.C_VAROS AS FelhelyTelepules
    ,mhely.C_IRANYITOSZAM AS FelhelyIrszam
    ,RTRIM(CONCAT(mhely.C_KOZTERULETNEV, ' ', mhely.C_KOZTERULETJELLEGENEV,' ', mhely.C_HAZSZAM, ' ', mhely.C_EMELET, ' ', mhely.C_AJTO)) AS FelhelyCim
    ,ISNULL(NULLIF(mhely.C_EMAILCIM, ''), '-') AS FelhelyEmailcim
    ,fhelytip.C_NAME AS FelhelyTipus
    ,ISNULL(tantervijellemzo.C_NAME, '-') AS OsztalyTanterviJellemzo
    ,LTRIM(RTRIM(REPLACE(ISNULL(NULLIF(kepzesiforma.C_NAME, 'Na'), '-'), '(*)', ''))) AS OsztalyMunkarend
    ,IIF(ocs.C_ISGYOGYPEDAGOGIAILOGOPEDIAI = 'T', 'Igen', 'Nem') AS OsztalyGyogypedagogiai
    ,CASE 
      WHEN ocs.C_ISGYOGYPEDAGOGIAILOGOPEDIAI = 'T' THEN CONCAT('gyógypedagógiai tanterv - ',
        CASE
          WHEN letszam.CNT <= 9 THEN 'nem több mint 9 fős'
          WHEN letszam.CNT BETWEEN 10 AND 20 THEN '10-20 fős'
          WHEN letszam.CNT BETWEEN 21 AND 25 THEN '21-25 fős'
          WHEN letszam.CNT BETWEEN 26 AND 28 THEN '26-28 fős'
          WHEN letszam.CNT BETWEEN 29 AND 34 THEN '29-34 fős'
          WHEN letszam.CNT >= 35 THEN 'legalább 35 fős'
          ELSE '0 fős'
        END)
      WHEN ocs.C_ISGYOGYPEDAGOGIAILOGOPEDIAI = 'F' THEN CONCAT('nem gyógypedagógiai tanterv - ',
        CASE
          WHEN letszam.CNT <= 9 THEN 'nem több mint 9 fős'
          WHEN letszam.CNT BETWEEN 10 AND 20 THEN '10-20 fős'
          WHEN letszam.CNT BETWEEN 21 AND 25 THEN '21-25 fős'
          WHEN letszam.CNT BETWEEN 26 AND 30 THEN '26-30 fős'
          WHEN letszam.CNT BETWEEN 31 AND 34 THEN '31-34 fős'
          WHEN letszam.CNT >= 35 THEN 'legalább 35 fős'
          ELSE '0 fős'
        END)
      ELSE '-'
     END AS OsztalyLetszam
    ,CASE 
      WHEN ocs.C_EVFOLYAMTIPUSA = 7366 AND fhely.C_OKTATASINEVELESIFELADATTIPUS NOT IN (1158, 1178, 7763)
        THEN ISNULL(NULLIF((
          SELECT TOP (1) dib.C_NAME 
          FROM T_TANULOCSOPORT tcs
            INNER JOIN T_TANULOTANUGYIADATOK tta ON tta.C_TANULOCSOPORTID = tcs.ID AND tta.TOROLT = 'F'
            INNER JOIN T_DICTIONARYITEMBASE dib ON dib.ID = tta.C_TANULOEVFOLYAMTIPUSID AND dib.C_TANEVID = tta.C_TANEVID AND dib.TOROLT = 'F'
          WHERE tcs.C_OSZTALYCSOPORTID = ocs.ID
            AND tcs.C_BELEPESDATUM <= @datum 
            AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM > @datum)
            AND tcs.C_TANEVID = @pTanevId
            AND tcs.TOROLT = 'F'
          GROUP BY dib.C_NAME
          ORDER BY COUNT(1) DESC, dib.C_NAME 
        ), 'Na'), '-')
      ELSE ISNULL(NULLIF(evfolyam.C_NAME, 'Na'), '-')
     END AS OsztalyEvfolyam
  FROM T_OSZTALYCSOPORT ocs
    INNER JOIN T_OSZTALY o ON o.ID = ocs.ID AND o.TOROLT = 'F'
    INNER JOIN T_INTEZMENYADATOK ia ON ia.C_TANEVID = ocs.C_TANEVID AND ia.TOROLT = 'F'
    INNER JOIN T_INTEZMENY i ON ia.C_INTEZMENYID = i.ID AND i.TOROLT = 'F'
    INNER JOIN T_FELADATELLATASIHELY fhely ON fhely.C_TANEVID = ia.C_TANEVID AND fhely.ID = ocs.C_FELADATELLATASIHELYID AND fhely.TOROLT = 'F'
    INNER JOIN T_MUKODESIHELY mhely ON mhely.ID = fhely.C_MUKODESIHELYID AND mhely.TOROLT = 'F'
    INNER JOIN T_DICTIONARYITEMBASE fhelytip ON fhelytip.ID = fhely.C_OKTATASINEVELESIFELADATTIPUS AND fhelytip.C_TANEVID = fhely.C_TANEVID AND fhelytip.TOROLT = 'F'
    OUTER APPLY (
      SELECT TOP (1) dib.C_NAME
      FROM (
        SELECT tcs.C_OSZTALYCSOPORTID, tta.C_TANTERVIJELLEMZOID, COUNT(1) AS CNT
        FROM T_TANULOCSOPORT tcs
          INNER JOIN T_TANULOTANUGYIADATOK tta ON tta.C_TANULOCSOPORTID = tcs.ID AND tta.TOROLT = 'F'
        WHERE tcs.C_BELEPESDATUM <= @datum 
          AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM > @datum)
          AND tcs.TOROLT = 'F'
          AND tcs.C_OSZTALYCSOPORTID = ocs.ID
          AND tcs.C_TANEVID = @pTanevId
        GROUP BY tcs.C_OSZTALYCSOPORTID, tta.C_TANTERVIJELLEMZOID
      ) AS x
        INNER JOIN T_DICTIONARYITEMBASE dib ON dib.ID = x.C_TANTERVIJELLEMZOID AND dib.C_TANEVID = @pTanevId
      ORDER BY x.CNT DESC, dib.C_NAME
    ) AS tantervijellemzo
    OUTER APPLY (
      SELECT COUNT(1) AS CNT
      FROM T_TANULOCSOPORT tcs
      WHERE tcs.C_BELEPESDATUM <= @datum 
        AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM > @datum)
        AND tcs.TOROLT = 'F'
        AND tcs.C_OSZTALYCSOPORTID = ocs.ID
        AND ocs.C_TANEVID = @pTanevId
      GROUP BY tcs.C_OSZTALYCSOPORTID
    ) AS  letszam
    OUTER APPLY (
      SELECT TOP (1) dib.C_NAME
      FROM (
        SELECT tcs.C_OSZTALYCSOPORTID, tta.C_TANTERVIJELLEGID, COUNT(1) AS CNT
        FROM T_TANULOCSOPORT tcs
          INNER JOIN T_TANULOTANUGYIADATOK tta ON tta.C_TANULOCSOPORTID = tcs.ID AND tta.TOROLT = 'F'
        WHERE tcs.C_BELEPESDATUM <= @datum 
          AND (tcs.C_KILEPESDATUM IS NULL OR tcs.C_KILEPESDATUM > @datum)
          AND tcs.TOROLT = 'F'
          AND tcs.C_OSZTALYCSOPORTID = ocs.ID
          AND tcs.C_TANEVID = @pTanevId
        GROUP BY tcs.C_OSZTALYCSOPORTID, tta.C_TANTERVIJELLEGID
      ) AS x
        INNER JOIN T_DICTIONARYITEMBASE dib ON dib.ID = x.C_TANTERVIJELLEGID AND dib.C_TANEVID = @pTanevId
      ORDER BY x.CNT DESC, dib.C_NAME
    ) AS kepzesiforma
    INNER JOIN T_DICTIONARYITEMBASE evfolyam ON evfolyam.ID = ocs.C_EVFOLYAMTIPUSA AND evfolyam.C_TANEVID = ocs.C_TANEVID AND evfolyam.TOROLT = 'F'
  WHERE ocs.TOROLT = 'F'
    AND ocs.C_ISTECHNIKAI = 'F'
    AND ocs.C_FELADATKATEGORIAID <> 7556
    AND ocs.C_TANEVID = @pTanevId
    AND ocs.C_INTEZMENYID = @pIntezmenyid
END
GO