DROP PROCEDURE IF EXISTS uspSzirStatTanuloEvVege
GO

CREATE PROCEDURE uspSzirStatTanuloEvVege
   @pElozoTanevId int
  ,@pIntezmenyId int
AS BEGIN

  DECLARE @IsSniKezelo bit

  SET @IsSniKezelo = dbo.fnGetRendszerbeallitasEnumBool(1666, @pIntezmenyId, @pElozoTanevId)

  SELECT 
     fh.ID  AS TanuloId
    ,CONCAT(fh.C_NYOMTATASINEV, ' (', fh.C_OKTATASIAZONOSITO ,')') AS TanuloNev
    ,ia.C_OMKOD AS IntezmenyOmAzonosito
    ,ia.C_NEV AS IntezmenyNev
    ,ia.C_VAROS AS IntezmenyTelepules
    ,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(jogviszony.C_NAME, '-') AS TanuloJogviszony
    ,nem.C_NAME AS TanuloNem
    ,ISNULL(besorolas.TANTEVIJELLEMZO, '-') AS TanuloTanterviJellemzo
    ,LTRIM(RTRIM(REPLACE(ISNULL(besorolas.MUNKAREND, '-'), '(*)', ''))) AS TanuloMunkarend
    ,ISNULL(NULLIF(NULLIF(besorolas.C_EVFOLYAMTIPUSA, ''), 'Na'), '-') AS TanuloEvfolyam
    ,ISNULL(besorolas.C_AGAZAT, '-') AS TanuloAgazat 
    ,IIF(PATINDEX ( '% - [1234567890]%' , COALESCE(NULLIF(besorolas.SZKT, 'na'), NULLIF(besorolas.NKT, 'na'), NULLIF(besorolas.OKJ, 'na'), '-') ) = 0, '-', SUBSTRING(COALESCE(NULLIF(besorolas.SZKT, 'na'), NULLIF(besorolas.NKT, 'na'), NULLIF(besorolas.OKJ, 'na'), '-'), PATINDEX ( '% - [1234567890]%' , COALESCE(NULLIF(besorolas.SZKT, 'na'), NULLIF(besorolas.NKT, 'na'), NULLIF(besorolas.OKJ, 'na'), '-') ) + 3, 100)) AS TanuloSzakkepesitesAzon
    ,IIF(PATINDEX ( '% - [1234567890]%' , COALESCE(NULLIF(besorolas.SZKT, 'na'), NULLIF(besorolas.NKT, 'na'), NULLIF(besorolas.OKJ, 'na'), '-') ) = 0, COALESCE(NULLIF(besorolas.SZKT, 'na'), NULLIF(besorolas.NKT, 'na'), NULLIF(besorolas.OKJ, 'na'), '-'), LEFT(COALESCE(NULLIF(besorolas.SZKT, 'na'), NULLIF(besorolas.NKT, 'na'), NULLIF(besorolas.OKJ, 'na'), '-'), PATINDEX ( '% - [1234567890]%' , COALESCE(NULLIF(besorolas.SZKT, 'na'), NULLIF(besorolas.NKT, 'na'), NULLIF(besorolas.OKJ, 'na'), '-') ))) AS TanuloSzakkepesitesNev
    ,allampolg.C_NAME AS TanuloAllampolgarsag
    ,ISNULL(IIF(fh.C_ALLAMPOLGARSAGA = fh.C_ALLAMPOLGARSAGA2, '-', allampolg2.C_NAME), '-') AS TanuloAllampolgarsag2
    ,IIF(@IsSniKezelo = 1, IIF(dk.C_SAJATOSNEVELESU = 'T', 'Igen', 'Nem'), '-') AS TanuloSni
    ,IIF(@IsSniKezelo = 1, IIF(besorolas.C_ISGYOGYPEDAGOGIAILOGOPEDIAI = 'T' AND dk.C_SAJATOSNEVELESU = 'T', 'Igen', 'Nem'), '-') AS TanuloSniGyogypedNevelt
    ,IIF(@IsSniKezelo = 1 AND dk.C_SAJATOSNEVELESU = 'T', IIF(integralt.INTEGRALT = 'T', 'Igen', 'Nem'), '-') AS TanuloSniIntegraltanNevelt
    ,IIF(dk.C_HATRANYOSHELYZETU IS NULL OR dk.C_HATRANYOSHELYZETU IN (1120, 1121), 'Nem', 'Igen') AS TanuloHatranyosHelyzet
    ,CASE ISNULL(szst.C_ISSIKERESTANULMANYOK, elozo_szst.C_ISSIKERESTANULMANYOK) WHEN 'T' THEN 'Igen' WHEN 'F' THEN 'Nem' ELSE IIF(fhely.C_OKTATASINEVELESIFELADATTIPUS IN (1165, 7767), '-', 'Nem') END AS TanuloSikeresTanulmanyok
    ,CASE szst.C_ISSIKERESSZAKMAIVISZGASZAK1 WHEN 'T' THEN 'Igen' WHEN 'F' THEN 'Nem' ELSE IIF(fhely.C_OKTATASINEVELESIFELADATTIPUS IN (1165, 7767), '-', 'Nem') END AS TanuloSikeresSzakmaiVizsgaSzak1
    ,CASE szst.C_ISSIKERESSZAKMAIVISZGASZAK2 WHEN 'T' THEN 'Igen' WHEN 'F' THEN 'Nem' ELSE IIF(fhely.C_OKTATASINEVELESIFELADATTIPUS IN (1165, 7767), '-', 'Nem') END AS TanuloSikeresSzakmaiVizsgaSzak2
    ,CASE szst.C_ISSIKERESSZAKMAIVISZGASZAK3 WHEN 'T' THEN 'Igen' WHEN 'F' THEN 'Nem' ELSE IIF(fhely.C_OKTATASINEVELESIFELADATTIPUS IN (1165, 7767), '-', 'Nem') END AS TanuloSikeresSzakmaiVizsgaSzak3
    ,CASE ISNULL(szst.C_ISSIKERESAGAZATIALAPVIZSGA, elozo_szst.C_ISSIKERESAGAZATIALAPVIZSGA) WHEN 'T' THEN 'Igen' WHEN 'F' THEN 'Nem' ELSE IIF(fhely.C_OKTATASINEVELESIFELADATTIPUS IN (1165, 7767), '-', 'Nem') END AS TanuloSikeresAgazatiVizsga
  FROM T_FELHASZNALO_OSSZES fh 
    INNER JOIN T_TANULO_OSSZES dk ON dk.ID = fh.ID AND dk.TOROLT = 'F'
    INNER JOIN T_INTEZMENYADATOK_OSSZES ia ON ia.C_TANEVID = fh.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_OSSZES fhely ON fhely.C_TANEVID = ia.C_TANEVID AND fhely.TOROLT = 'F'
    INNER JOIN T_MUKODESIHELY_OSSZES mhely ON mhely.ID = fhely.C_MUKODESIHELYID AND mhely.TOROLT = 'F'
    LEFT JOIN T_DICTIONARYITEMBASE_OSSZES fhelytip ON fhelytip.ID = fhely.C_OKTATASINEVELESIFELADATTIPUS AND fhelytip.C_TANEVID = fhely.C_TANEVID AND fhelytip.C_INTEZMENYID = fhely.C_INTEZMENYID AND fhelytip.TOROLT = 'F'
    LEFT JOIN T_DICTIONARYITEMBASE_OSSZES nem ON fh.C_NEME = nem.ID AND nem.C_TANEVID = fh.C_TANEVID AND nem.C_INTEZMENYID = fh.C_INTEZMENYID AND nem.TOROLT='F'
    CROSS APPLY (
      SELECT TOP(1) 
         ocs.ID AS C_OSZTALYID, tcs.C_BELEPESDATUM, tcs.C_KILEPESDATUM
        ,ISNULL(dk.C_JOGVISZONYTIPUSID, tcs.C_JOGVISZONYTIPUSID) AS  C_JOGVISZONYTIPUSID
        ,tantevijellemzotta.C_NAME AS TANTEVIJELLEMZO
        ,IIF(ocs.C_KEPZESIFORMA = 1095, '-', munkarend.C_NAME) AS MUNKAREND
        ,IIF(ocs.C_EVFOLYAMTIPUSA = 7366, evfolyamTta.C_NAME, evfolyamOcs.C_NAME) AS C_EVFOLYAMTIPUSA
        ,COALESCE(NULLIF(agazatSzkt.C_NAME, 'na'), NULLIF(agazatSzktO.C_NAME, 'na'), NULLIF(agazatNkt.C_NAME, 'na'), NULLIF(agazatOkj.C_NAME, 'na'), NULLIF(agazatOkjO.C_NAME, 'na'), '-') AS C_AGAZAT
        ,ocs.C_ISGYOGYPEDAGOGIAILOGOPEDIAI 
        ,ISNULL(szktTta.C_NAME, szktO.C_NAME) AS SZKT
        ,ISNULL(nktTta.C_NAME, nktO.C_NAME) AS NKT
        ,okj.C_NAME AS OKJ
      FROM T_TANULOCSOPORT_OSSZES tcs 
        INNER JOIN T_TANULOTANUGYIADATOK_OSSZES tta ON tta.C_TANULOCSOPORTID = tcs.ID AND tta.TOROLT = 'F'
        INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = tcs.C_OSZTALYCSOPORTID AND ocs.TOROLT = 'F'
        INNER JOIN T_OSZTALY_OSSZES o ON o.ID = ocs.ID AND o.TOROLT = 'F'
     
        LEFT JOIN T_DICTIONARYITEMBASE_OSSZES tantevijellemzotta ON tantevijellemzotta.ID = tta.C_TANTERVIJELLEMZOID AND tantevijellemzotta.C_TANEVID = tta.C_TANEVID AND tantevijellemzotta.C_INTEZMENYID = tta.C_INTEZMENYID AND tantevijellemzotta.TOROLT = 'F'
        LEFT JOIN T_DICTIONARYITEMBASE_OSSZES munkarend ON munkarend.ID = ocs.C_KEPZESIFORMA AND munkarend.C_TANEVID = ocs.C_TANEVID AND munkarend.C_INTEZMENYID = ocs.C_INTEZMENYID AND munkarend.TOROLT = 'F'
        LEFT JOIN T_DICTIONARYITEMBASE_OSSZES evfolyamOcs ON evfolyamOcs.ID = ocs.C_EVFOLYAMTIPUSA AND evfolyamOcs.C_TANEVID = ocs.C_TANEVID AND evfolyamOcs.C_INTEZMENYID = ocs.C_INTEZMENYID AND evfolyamOcs.TOROLT = 'F'
        LEFT JOIN T_DICTIONARYITEMBASE_OSSZES evfolyamTta ON evfolyamTta.ID = tta.C_TANULOEVFOLYAMTIPUSID AND evfolyamTta.C_TANEVID = tta.C_TANEVID AND evfolyamTta.C_INTEZMENYID = tta.C_INTEZMENYID AND evfolyamTta.TOROLT = 'F'
        LEFT JOIN T_DICTIONARYITEMBASE_OSSZES agazatOkj ON agazatOkj.ID = tta.C_AGAZATID AND agazatOkj.C_TANEVID = tta.C_TANEVID AND agazatOkj.C_INTEZMENYID = tta.C_INTEZMENYID AND agazatOkj.TOROLT = 'F'
        LEFT JOIN T_DICTIONARYITEMBASE_OSSZES agazatNkt ON agazatNkt.ID = tta.C_TANULMANYITERULETNKTTIPUSID AND agazatNkt.C_TANEVID = tta.C_TANEVID AND agazatNkt.C_INTEZMENYID = tta.C_INTEZMENYID AND agazatNkt.TOROLT = 'F'
        LEFT JOIN T_DICTIONARYITEMBASE_OSSZES agazatSzkt ON agazatSzkt.ID = tta.C_AGAZATUJSZKTTIPUSID AND agazatSzkt.C_TANEVID = tta.C_TANEVID AND agazatSzkt.C_INTEZMENYID = tta.C_INTEZMENYID AND agazatSzkt.TOROLT = 'F'
        LEFT JOIN T_DICTIONARYITEMBASE_OSSZES agazatOkjO ON agazatOkjO.ID = o.C_AGAZAT AND agazatOkjO.C_TANEVID = o.C_ALTANEVID AND agazatOkjO.C_INTEZMENYID = o.C_ALINTEZMENYID AND agazatOkjO.TOROLT = 'F'
        LEFT JOIN T_DICTIONARYITEMBASE_OSSZES agazatSzktO ON agazatSzktO.ID = o.C_AGAZATUJSZKTTIPUSID AND agazatSzktO.C_TANEVID = o.C_ALTANEVID AND agazatSzktO.C_INTEZMENYID = o.C_ALINTEZMENYID AND agazatSzktO.TOROLT = 'F'
        LEFT JOIN T_DICTIONARYITEMBASE_OSSZES szktTta ON szktTta.ID = tta.C_SZAKMATIPUSID AND szktTta.C_TANEVID = tta.C_TANEVID AND szktTta.C_INTEZMENYID = tta.C_INTEZMENYID AND szktTta.TOROLT = 'F'
        LEFT JOIN T_DICTIONARYITEMBASE_OSSZES szktO ON szktO.ID = o.C_SZAKMATIPUSID AND szktO.C_TANEVID = o.C_ALTANEVID AND szktO.C_INTEZMENYID = o.C_ALINTEZMENYID AND szktO.TOROLT = 'F'
        LEFT JOIN T_DICTIONARYITEMBASE_OSSZES nktTta ON nktTta.ID = tta.C_SZAKKEPESITESNKTTIPUSID AND nktTta.C_TANEVID = tta.C_TANEVID AND nktTta.C_INTEZMENYID = tta.C_INTEZMENYID AND nktTta.TOROLT = 'F'
        LEFT JOIN T_DICTIONARYITEMBASE_OSSZES nktO ON nktO.ID = o.C_SZAKKEPESITESNKTTIPUSID AND nktO.C_TANEVID = o.C_ALTANEVID AND nktO.C_INTEZMENYID = o.C_ALINTEZMENYID AND nktO.TOROLT = 'F'
        LEFT JOIN T_DICTIONARYITEMBASE_OSSZES okj ON okj.ID = tta.C_SZAKKEPESITESID AND okj.C_TANEVID = tta.C_TANEVID AND okj.C_INTEZMENYID = tta.C_INTEZMENYID AND okj.TOROLT = 'F'
      WHERE tcs.C_TANULOID = fh.ID
        AND fhely.ID = ocs.C_FELADATELLATASIHELYID
        AND tcs.TOROLT = 'F'
        AND tcs.C_TANEVID = fh.C_TANEVID
      ORDER BY tcs.C_BELEPESDATUM DESC
    ) besorolas
    LEFT JOIN T_DICTIONARYITEMBASE_OSSZES jogviszony ON jogviszony.ID = besorolas.C_JOGVISZONYTIPUSID AND jogviszony.C_TANEVID = dk.C_ALTANEVID AND jogviszony.C_INTEZMENYID = dk.C_ALINTEZMENYID AND jogviszony.TOROLT = 'F'
    LEFT JOIN T_DICTIONARYITEMBASE_OSSZES allampolg ON allampolg.ID = fh.C_ALLAMPOLGARSAGA AND allampolg.C_TANEVID = dk.C_ALTANEVID AND allampolg.C_INTEZMENYID = dk.C_ALINTEZMENYID AND allampolg.TOROLT = 'F'
    LEFT JOIN T_DICTIONARYITEMBASE_OSSZES allampolg2 ON allampolg2.ID = fh.C_ALLAMPOLGARSAGA2 AND allampolg2.C_TANEVID = dk.C_ALTANEVID AND allampolg2.C_INTEZMENYID = dk.C_ALINTEZMENYID AND allampolg2.TOROLT = 'F'
    LEFT JOIN T_TANULOSNI_OSSZES sni ON sni.C_TANULOID = dk.ID AND sni.TOROLT = 'F'
    LEFT JOIN T_SZIRSTATTANULO_OSSZES szst ON szst.C_TANULOID = dk.ID AND szst.C_FELADATELLATASIHELYID = fhely.ID AND szst.TOROLT = 'F'
      AND szst.C_TANEVID = fh.C_TANEVID
      AND szst.TOROLT = 'F'
    OUTER APPLY (
      SELECT TOP(1) 'T' AS INTEGRALT 
      FROM T_TANULOCSOPORT_OSSZES tcs
        INNER JOIN T_TANULO_OSSZES dk2 ON dk2.ID = tcs.C_TANULOID AND dk2.TOROLT = 'F'
      WHERE tcs.C_OSZTALYCSOPORTID = besorolas.C_OSZTALYID
        AND tcs.C_KILEPESDATUM > besorolas.C_BELEPESDATUM /* NULL vizsgálat nem kell, mert elozo tanévben ki van sorolva az összes diák*/
        AND tcs.C_BELEPESDATUM < besorolas.C_KILEPESDATUM
        AND dk2.C_SAJATOSNEVELESU = 'F'
        AND tcs.TOROLT = 'F'
        AND @IsSniKezelo = 1
    ) integralt
    OUTER APPLY (
      SELECT TOP 1 *
      FROM (
        SELECT
          tcs.C_BELEPESDATUM,
          tcs.ID,
          CASE
            WHEN COUNT(tcs.ID) OVER(PARTITION BY tcs.C_TANULOID) = 1 THEN 1
            WHEN tcs.C_BELEPESDATUM <= CAST(LEFT(v.C_NEV, 4) + '-10-01' AS DATE) AND ISNULL(tcs.C_KILEPESDATUM, '2999-01-01') > CAST(LEFT(v.C_NEV, 4) + '-10-01' AS DATE) THEN 2
            ELSE 3 
          END AS row_order,
          IIF(ISNULL(tta.C_EVISMETLO, t.C_EVISMETLO) = 'T', 'F', 'T') AS C_ISSIKERESTANULMANYOK,
          ISNULL(IIF(tta.C_AGAZATALAPVIZSGAEREDTIPUSID = 8996, 'T', NULL), IIF(t.C_AGAZATIALAPVIZSGAEREDMENYE < 2.0, 'F', NULL)) AS C_ISSIKERESAGAZATIALAPVIZSGA
        FROM T_FELHASZNALO_OSSZES f
        INNER JOIN T_TANEV_OSSZES v on v.ID = f.C_TANEVID AND v.TOROLT = 'F'
        INNER JOIN T_TANULO_OSSZES t ON t.ID = f.ID AND t.TOROLT = 'F'
        INNER JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.C_TANULOID = f.ID AND tcs.TOROLT = 'F'
        INNER JOIN T_TANULOTANUGYIADATOK_OSSZES tta ON tta.C_TANULOCSOPORTID = tcs.ID AND tta.TOROLT = 'F'
        WHERE f.TOROLT = 'F'
        AND f.ELOZOTANEVIREKORDID = fh.ID
      ) sz
      ORDER BY sz.row_order, sz.C_BELEPESDATUM DESC, sz.ID DESC
    ) elozo_szst 
  WHERE fh.TOROLT = 'F'
    AND fh.C_TANEVID = @pElozoTanevId
    AND fh.C_INTEZMENYID = @pIntezmenyId
END
GO