58 lines
1.7 KiB
Transact-SQL
58 lines
1.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
|
|
g.ID AS GondviseloId
|
|
,g.C_NEV AS GondviseloNeve
|
|
,fhb.C_UTOLSOBELEPES AS UtolsoBelepes
|
|
,e.C_EMAILCIM AS EmailCim
|
|
,t.C_TELEFONSZAM AS Telefonszam
|
|
FROM T_GONDVISELO_OSSZES g
|
|
LEFT JOIN T_FELHASZNALOBELEPES_OSSZES fhb ON fhb.C_GONDVISELOID = g.ID AND fhb.TOROLT = 'F'
|
|
LEFT JOIN T_EMAIL_OSSZES e ON e.C_GONDVISELOID = g.ID AND e.TOROLT = 'F'
|
|
LEFT JOIN T_TELEFON_OSSZES t ON t.C_GONDVISELOID = g.ID AND t.TOROLT = 'F'
|
|
WHERE g.C_TANULOID = @pTanuloId AND g.TOROLT = 'F'
|
|
|
|
--NOTE: 1. tábla - Gondviselo címek
|
|
SELECT
|
|
g.ID AS GondviseloId
|
|
,c.C_IRANYITOSZAM + ', ' +
|
|
c.C_VAROS + ', ' +
|
|
c.C_KOZTERULET + ' ' +
|
|
c.C_KOZTERULETJELLEGENEV + ' ' +
|
|
ISNULL(c.C_HAZSZAM, '') AS CimText
|
|
,dibCTip.C_NAME AS CimTipus
|
|
,c.C_CIMTIPUSA AS CimTipusId
|
|
FROM T_GONDVISELO_OSSZES g
|
|
LEFT JOIN T_CIM_OSSZES c ON c.C_GONDVISELOID = g.ID AND c.TOROLT = 'F'
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES dibCTip ON dibCTip.ID = c.C_CIMTIPUSA AND dibCTip.C_TANEVID = @pTanevId AND dibCTip.TOROLT = 'F' AND dibCTip.C_INTEZMENYID=@pIntezmenyId
|
|
WHERE g.C_TANULOID = @pTanuloId AND g.TOROLT = 'F'
|
|
|
|
END
|
|
GO
|
|
|