99 lines
2.7 KiB
Transact-SQL
99 lines
2.7 KiB
Transact-SQL
-- =============================================
|
|
-- 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
|
|
|