61 lines
2.8 KiB
Transact-SQL
61 lines
2.8 KiB
Transact-SQL
IF OBJECT_ID('fnGetDokumentumTanuloOrGondviseloCim') IS NOT NULL BEGIN
|
|
DROP FUNCTION fnGetDokumentumTanuloOrGondviseloCim
|
|
END
|
|
GO
|
|
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE FUNCTION fnGetDokumentumTanuloOrGondviseloCim (@TanuloOrGondviselo BIT, @pTanevId INT, @pOsztalyId INT)
|
|
|
|
RETURNS @result TABLE
|
|
(
|
|
TanuloId INT,
|
|
Cim NVARCHAR(MAX),
|
|
CimTipusa INT,
|
|
GondviseloId INT
|
|
)
|
|
AS
|
|
BEGIN
|
|
DECLARE @Magyarorszagmegnevezes NVARCHAR(20) = (SELECT dib_MO.C_NAME FROM T_DICTIONARYITEMBASE_OSSZES dib_MO WHERE dib_MO.ID = 765 AND dib_MO.C_TANEVID = @pTanevId AND dib_MO.TOROLT = 'F' )
|
|
IF @TanuloOrGondviselo = 0 BEGIN
|
|
INSERT INTO @result(TanuloId, Cim, CimTipusa, GondviseloId)
|
|
SELECT
|
|
Tanulo.C_TANULOID AS TanuloId
|
|
,ISNULL(dib_orszag.C_NAME,@Magyarorszagmegnevezes) + ', '
|
|
+ cim.C_IRANYITOSZAM + ' ' + cim.C_VAROS + ', ' + cim.C_KOZTERULET + ' ' + cim.C_KOZTERULETJELLEGENEV + ' ' + cim.C_HAZSZAM AS Cim
|
|
,cim.C_CIMTIPUSA AS CimTipusa
|
|
,cim.C_GONDVISELOID AS GondviseloId
|
|
FROM T_TANULOCSOPORT_OSSZES Tanulo
|
|
INNER JOIN T_CIM_OSSZES cim on cim.C_FELHASZNALOID = Tanulo.C_TANULOID AND cim.C_GONDVISELOID IS NULL AND cim.TOROLT = 'F'
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES cimTipusa on cimTipusa.ID = cim.C_CIMTIPUSA AND cimTipusa.C_TANEVID = @pTanevId
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES dib_orszag on dib_orszag.ID = cim.C_ORSZAG AND dib_orszag.C_TANEVID = @pTanevId AND dib_orszag.TOROLT = 'F'
|
|
WHERE Tanulo.C_TANEVID = @pTanevId
|
|
AND Tanulo.C_OSZTALYCSOPORTID = @pOsztalyId
|
|
AND Tanulo.TOROLT = 'F'
|
|
ORDER BY cimTipusa.C_NAME ASC, cim.C_ALAPERTELMEZETT DESC
|
|
END
|
|
|
|
ELSE IF @TanuloOrGondviselo = 1 BEGIN
|
|
INSERT INTO @result(TanuloId, Cim, CimTipusa, GondviseloId)
|
|
SELECT
|
|
Tanulo.C_TANULOID AS TanuloId
|
|
,ISNULL(dib_orszag.C_NAME,@Magyarorszagmegnevezes) + ', '
|
|
+ cim.C_IRANYITOSZAM + ' ' + cim.C_VAROS + ', ' + cim.C_KOZTERULET + ' ' + cim.C_KOZTERULETJELLEGENEV + ' ' + cim.C_HAZSZAM + '.' + ' ' + ISNULL(cim.C_EMELET + '/', ' ') + ISNULL(cim.C_AJTO, ' ') AS Cim
|
|
,cim.C_CIMTIPUSA AS CimTipusa
|
|
,cim.C_GONDVISELOID AS GondviseloId
|
|
FROM T_TANULOCSOPORT_OSSZES Tanulo
|
|
INNER JOIN T_GONDVISELO_OSSZES Gondviselo ON Gondviselo.C_TANULOID = Tanulo.C_TANULOID
|
|
INNER JOIN T_CIM_OSSZES cim ON cim.C_GONDVISELOID = Gondviselo.ID AND cim.TOROLT = 'F'
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES cimTipusa on cimTipusa.ID = cim.C_CIMTIPUSA AND cimTipusa.C_TANEVID = @pTanevId
|
|
LEFT JOIN T_DICTIONARYITEMBASE_OSSZES dib_orszag on dib_orszag.ID = cim.C_ORSZAG AND dib_orszag.C_TANEVID = @pTanevId AND dib_orszag.TOROLT = 'F'
|
|
WHERE Tanulo.C_TANEVID = @pTanevId
|
|
AND Tanulo.C_OSZTALYCSOPORTID = @pOsztalyId
|
|
AND Tanulo.TOROLT = 'F'
|
|
ORDER BY cimTipusa.C_NAME ASC, cim.C_ALAPERTELMEZETT DESC
|
|
END
|
|
|
|
RETURN
|
|
END |