-- =============================================
-- Description:	<Előszedjük a tanuló gondviselő adatait>
-- =============================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


IF OBJECT_ID('[dbo].[sp_GetTanuloiGondviseloAdatok]') IS NOT NULL
BEGIN
	DROP PROCEDURE [dbo].[sp_GetTanuloiGondviseloAdatok]
END
GO


CREATE PROCEDURE [dbo].[sp_GetTanuloiGondviseloAdatok]
	 @pTanuloId		INT
	,@pTanevId		INT
	,@pIntezmenyId	INT
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;

	--NOTE: 0. tábla - Gondviselo adatok
	SELECT
		 Gondviselo.ID						GondviseloId
		,Gondviselo.C_NEV					GondviseloNeve
		,Felhasznalo.C_UTOLSOBELEPES		UtolsoBelepes
		,Email.C_EMAILCIM					EmailCim
		,Telefon.C_TELEFONSZAM				Telefonszam
	FROM
		T_GONDVISELO_OSSZES Gondviselo
	LEFT JOIN
		T_FELHASZNALOBELEPES_OSSZES Felhasznalo ON
				Felhasznalo.C_GONDVISELOID = Gondviselo.ID
			AND Felhasznalo.C_INTEZMENYID = @pIntezmenyId
			AND Felhasznalo.C_TANEVID = @pTanevId
			AND Felhasznalo.TOROLT = 'F'
	LEFT JOIN
		T_EMAIL_OSSZES Email ON
				Email.C_GONDVISELOID = Gondviselo.ID
			AND Email.C_INTEZMENYID = @pIntezmenyId
			AND Email.C_TANEVID = @pTanevId
			AND Email.TOROLT = 'F'
	LEFT JOIN
		T_TELEFON_OSSZES Telefon ON
				Telefon.C_GONDVISELOID = Gondviselo.ID
			AND Telefon.C_INTEZMENYID = @pIntezmenyId
			AND Telefon.C_TANEVID = @pTanevId
			AND Telefon.TOROLT = 'F'
	WHERE
			Gondviselo.C_TANULOID = @pTanuloId
		AND Gondviselo.C_INTEZMENYID = @pIntezmenyId
		AND Gondviselo.C_TANEVID = @pTanevId
		AND Gondviselo.TOROLT = 'F'

	--NOTE: 1. tábla - Gondviselo címek
	SELECT
		 Gondviselo.ID						GondviseloId
		,Cim.C_IRANYITOSZAM + ', ' +		--
		 Cim.C_VAROS + ', ' +				--
		 Cim.C_KOZTERULET + ' ' +			--
		 KozteruletJelleg.C_NAME + ' ' +	--
		 ISNULL(Cim.C_HAZSZAM, '')			CimText
		,CimTipus.C_NAME					CimTipus
		,Cim.C_CIMTIPUSA					CimTipusId
	FROM
		T_GONDVISELO_OSSZES Gondviselo
	LEFT JOIN
		T_CIM_OSSZES Cim ON
				Cim.C_GONDVISELOID = Gondviselo.ID
			AND Cim.C_INTEZMENYID = @pIntezmenyId
			AND Cim.C_TANEVID = @pTanevId
			AND Cim.TOROLT = 'F'
	INNER JOIN
		T_DICTIONARYITEMBASE_OSSZES KozteruletJelleg ON
				KozteruletJelleg.ID = Cim.C_KOZTERULETJELLEGE
			AND KozteruletJelleg.C_INTEZMENYID = @pIntezmenyId
			AND KozteruletJelleg.C_TANEVID = @pTanevId
			AND KozteruletJelleg.TOROLT = 'F'
	INNER JOIN
		T_DICTIONARYITEMBASE_OSSZES CimTipus ON
				CimTipus.ID = Cim.C_CIMTIPUSA
			AND CimTipus.C_INTEZMENYID = @pIntezmenyId
			AND CimTipus.C_TANEVID = @pTanevId
			AND CimTipus.TOROLT = 'F'
	WHERE
			Gondviselo.C_TANULOID = @pTanuloId
		AND Gondviselo.C_INTEZMENYID = @pIntezmenyId
		AND Gondviselo.C_TANEVID = @pTanevId
		AND Gondviselo.TOROLT = 'F'

END
GO