DROP PROCEDURE IF EXISTS uspGetTanuloAdatok
GO

CREATE PROCEDURE uspGetTanuloAdatok
  @pTanuloIdpUniqueId UNIQUEIDENTIFIER

AS 
BEGIN
  SET NOCOUNT ON;

  SELECT
     f.ID AS TanuloId
    ,f.C_NYOMTATASINEV AS NyomtatasiNev
    ,f.C_SZULETESINEV AS SzuletesiNev
    ,f.C_SZULETESIDATUM AS SzuletesiDatum
    ,f.C_SZULETESIHELY AS SzuletesiHely
    ,f.C_ANYJANEVE AS AnyjaNeve
    ,f.C_IDPEGYEDIAZONOSITO AS IdpEgyediAzonosito
    ,t.C_BANKSZAMLASZAM AS BankszamlaSzam
    ,t.C_BANKSZAMLATULAJDONOSTIPUSID AS BankszamlaTulajdonosTipusId
    ,t.C_BANKSZAMLATULAJNEVE AS BankszamlaTulajdonosNeve
    ,tf.C_TELEFONSZAM AS AlapertelmezettTelefon
    ,e.C_EMAILCIM AS AlapertelmezettEmail
    ,ei.C_ISESZKOZKIOSZTVA AS IsEszkozKiosztva
  FROM T_TANULO t
    INNER JOIN T_FELHASZNALO f ON f.ID = t.ID
    INNER JOIN T_TANEV tv ON f.C_TANEVID = tv.ID
    LEFT JOIN T_TELEFON tf ON tf.C_FELHASZNALOID = f.ID AND tf.C_FELHASZNALOID IS NOT NULL AND tf.C_GONDVISELOID IS NULL AND tf.C_ALAPERTELMEZETT = 'T'
    LEFT JOIN T_EMAIL e ON e.C_FELHASZNALOID = f.ID AND e.C_FELHASZNALOID IS NOT NULL AND e.C_GONDVISELOID IS NULL AND e.C_ALAPERTELMEZETT = 'T'
    LEFT JOIN (
        T_ESZKOZIGENYLES ei 
        INNER JOIN T_GONDVISELO g ON g.ID = ei.C_GONDVISELOID AND g.TOROLT = 'F'
    ) ON ei.C_TANULOID = f.ID AND ei.TOROLT = 'F'
  WHERE t.TOROLT = 'F'
    AND tv.C_AKTIV = 'T'
    AND f.C_IDPEGYEDIAZONOSITO = @pTanuloIdpUniqueId

  SELECT
     c.C_IRANYITOSZAM AS Iranyitoszam
    ,c.C_VAROS AS Varos
    ,c.C_KOZTERULET AS Kozterulet
    ,c.C_KOZTERULETJELLEGENEV AS KozteruletJellegeNev
    ,c.C_HAZSZAM AS HazSzam
    ,c.C_EPULET AS Epulet
    ,c.C_LEPCSOHAZ AS LepcsoHaz
    ,c.C_EMELET AS Emelet
    ,c.C_AJTO AS Ajto
  FROM T_CIM c
    INNER JOIN T_FELHASZNALO f ON f.ID = c.C_FELHASZNALOID
    INNER JOIN T_TANEV t ON t.ID = f.C_TANEVID
  WHERE c.TOROLT = 'F'
    AND t.C_AKTIV = 'T'
    AND f.C_IDPEGYEDIAZONOSITO = @pTanuloIdpUniqueId
    AND c.C_GONDVISELOID IS NULL
END