DROP PROCEDURE IF EXISTS uspGetTanuloData
GO

CREATE PROCEDURE uspGetTanuloData
  @pTanevId int

AS 
BEGIN
  SET NOCOUNT ON;

  SELECT
     dk.ID AS Id
    ,dk.C_BEIRASINAPLOSORSZAM AS BeirasiNaploSorszam
    ,dk.C_BEJARO AS IsBejaro
    ,dk.C_DIAKSPORTKOROS AS IsDiaksportkoros
    ,dk.C_EGYEBDONTO AS IsEgyebDonto
    ,dk.C_EGYUTTMUKODESES AS IsEgyuttmukodeses
    ,dk.C_ELOZOINTEZMENY AS ElozoIntezmeny
    ,dk.C_EVISMETLO AS IsEvismetlo
    ,dk.C_ISKOLAIKERETEKKOZOTT AS IsIskolaiKeretekKozott
    ,dk.C_JOGVISZONYATSZUNETELTETO AS IsJogviszonyatSzunetelteto
    ,dk.C_JOGVISZONYVARHATOBEFEJEZESE AS JogviszonyVarhatoBefejezese
    ,dk.C_MAGANTANULO AS IsMagantanulo
    ,dk.C_MAGANTANULOSAGANAKOKA AS MagantanulosaganakOkaId
    ,dk.C_MAGANTANULOSAGKEZDETE AS MagantanulosagKezdete
    ,dk.C_OSZTV AS IsOsztv
    ,dk.C_POLGARISZERZODESES AS IsPolgariSzerzodeses
    ,dk.C_SZAKMAIGYAKORLATON AS IsSzakmaiGyakorlaton
    ,dk.C_SZETVAGAZAT AS IsSzetvAgazat
    ,dk.C_SZINTVIZSGA AS Szintvizsga
    ,dk.C_SZKTV AS IsSzktv
    ,dk.C_TANDIJATFIZETO AS IsTandijatfizeto
    ,dk.C_TANKOTELEZETT AS IsTankotelezett
    ,dk.C_TANKOTELEZETTSEGVEGE AS TankotelezettsegVege
    ,dk.C_TANULOEVFOLYAMTIPUS AS TanuloEvfolyamTipusId
    ,dk.C_TANULOSZERZODESES AS IsTanuloSzerzodeses
    ,dk.C_TECHNIKUSIEVFOLYAM AS IsTechnikusiEvfolyam
    ,dk.C_TERITESIDIJATFIZETO AS IsTeritesiDijatFizeto
    ,dk.C_VENDEG AS IsVendeg
    ,dk.C_NAPLOSORSZAM AS Naplosorszam
    ,dk.C_TORZSLAPSZAM AS Torzslapszam
    ,dk.C_FELVETELTANEVEID AS FelvetelTaneveId
    ,dk.C_BTMPROBLEMAS AS IsBTMProblemas
    ,dk.C_DIAKIGAZOLVANYSZAM AS DiakigazolvanySzam
    ,dk.C_SAJATOSNEVELESU AS IsSNI

    ,fh.ID AS FelhasznaloId
    ,fh.C_NEVSORREND AS FelhasznaloNevsorrend
    ,fh.C_ELOTAG AS FelhasznaloElotag
    ,fh.C_VEZETEKNEV AS FelhasznaloVezeteknev
    ,fh.C_UTONEV AS FelhasznaloKeresztnev
    ,fh.C_SZULETESINEVSORREND AS SzuletesiNevsorrend
    ,fh.C_SZULETESINEVELOTAG AS SzuletesiElotag
    ,fh.C_SZULETESIVEZETEKNEV AS SzuletesiVezeteknev
    ,fh.C_SZULETESIUTONEV AS SzuletesiUtonev
    ,fh.C_SZULETESIORSZAG AS FelhasznaloSzuletesiOrszag
    ,fh.C_SZULETESIHELY AS FelhasznaloSzuletesiHely
    ,fh.C_SZULETESIDATUM AS FelhasznaloSzuletesiIdo
    ,fh.C_ANYJANEVESORREND AS AnyjaNeveNevsorrend
    ,fh.C_ANYJANEVEELOTAG AS AnyjaNeveElotag
    ,fh.C_ANYJAVEZETEKNEVE AS AnyjaVezetekneve
    ,fh.C_ANYJAUTONEVE AS AnyjaUtoneve
    ,fh.C_OKTATASIAZONOSITO AS FelhasznaloOktatasiAzonosito
    ,fh.C_NEME AS NemeId
    ,fh.C_ALLAMPOLGARSAGA AS FelhasznaloAllampolgarsag1
    ,fh.C_ALLAMPOLGARSAGA2 AS FelhasznaloAllampolgarsag2
    ,fh.C_BELUGYALTALAZONOSITOTT AS BelugyAltalAzonositott
    ,fh.C_UTOLSOADATFRISSITESIDOPONTJA AS UtolsoAdatfrissitesIdopontja
    ,fh.C_OMKOD AS OMazonosito
    ,fh.C_TAJSZAM AS TajSzam
    
    ,fh.C_NYILVANTARTASKEZDETE AS NyilvantartasKezdete
    ,fh.C_IMPORTALT AS Importalt

    ,c.ID AS CimId
    ,c.C_CIMTIPUSA AS CimTipusId
    ,c.C_IRANYITOSZAM AS CimIranyitoszam
    ,c.C_VAROS AS CimTelepules
    ,c.C_KOZTERULET AS CimKozterulet
    ,c.C_KOZTERULETJELLEGENEV AS CimKozteruletJellegNev
    ,c.C_HAZSZAM AS CimHazszam
    ,c.C_EMELET AS CimEmelet
    ,c.C_AJTO AS CimAjto
    ,c.C_GONDVISELOID AS CimGondviseloId

    ,e.ID AS EmailId
    ,e.C_EMAILTIPUSA AS EmailTipusId
    ,e.C_EMAILCIM AS EmailEmailCim
    ,e.C_ALAPERTELMEZETT AS EmailAlapertelmezett
    ,e.C_GONDVISELOID AS EmailGondviseloId

    ,t.ID AS TelefonId
    ,t.C_TELEFONTIPUSA AS TelefonTipusId
    ,t.C_TELEFONSZAM AS TelefonTelefonszam
    ,t.C_ALAPERTELMEZETT AS TelefonAlapertelmezett
    ,t.C_GONDVISELOID AS TelefonGondviseloId
  FROM T_TANULO_OSSZES dk
    INNER JOIN T_FELHASZNALO_OSSZES fh ON fh.ID = dk.ID AND fh.C_TANEVID = dk.C_ALTANEVID AND fh.TOROLT = 'F'
    LEFT JOIN T_CIM_OSSZES c ON c.C_FELHASZNALOID = dk.ID AND c.C_TANEVID = dk.C_ALTANEVID AND c.TOROLT = 'F'
    LEFT JOIN T_EMAIL_OSSZES e ON e.C_FELHASZNALOID = dk.ID AND e.C_TANEVID = dk.C_ALTANEVID AND e.TOROLT = 'F'
    LEFT JOIN T_TELEFON_OSSZES t ON t.C_FELHASZNALOID = dk.ID AND t.C_TANEVID = dk.C_ALTANEVID AND t.TOROLT = 'F'
  WHERE dk.C_ALTANEVID = @pTanevId
    AND dk.TOROLT = 'F'
END

GO