60 lines
2.5 KiB
Transact-SQL
60 lines
2.5 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
|
|
IF @TanuloOrGondviselo = 0 BEGIN
|
|
INSERT INTO @result(TanuloId, Cim, CimTipusa, GondviseloId)
|
|
SELECT DISTINCT
|
|
TanuloId AS TanuloId
|
|
,cim.C_IRANYITOSZAM + ' ' + cim.C_VAROS + ', ' + cim.C_KOZTERULET + ' ' + kozterulet.C_NAME + ' ' + cim.C_HAZSZAM AS Cim
|
|
,cim.C_CIMTIPUSA AS CimTipusa
|
|
,cim.C_GONDVISELOID AS GondviseloId
|
|
FROM fnGetDokumentumOsztalyokCsoportokTanuloi(@pTanevId, @pOsztalyId, 'T') tanulo
|
|
CROSS APPLY (SELECT TOP 1 WITH TIES c.C_IRANYITOSZAM, c.C_VAROS, c.C_KOZTERULET, c.C_HAZSZAM, c.C_KOZTERULETJELLEGE, c.C_CIMTIPUSA, c.C_GONDVISELOID
|
|
FROM T_CIM_OSSZES c
|
|
WHERE c.C_FELHASZNALOID = tanulo.TanuloId
|
|
AND c.C_TANEVID = @pTanevId
|
|
AND c.TOROLT = 'F'
|
|
AND c.C_GONDVISELOID IS NULL
|
|
ORDER BY C_ALAPERTELMEZETT DESC ) cim
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES kozterulet ON kozterulet.Id = cim.C_KOZTERULETJELLEGE AND kozterulet.C_TANEVID = @pTanevId
|
|
END
|
|
|
|
ELSE IF @TanuloOrGondviselo = 1 BEGIN
|
|
INSERT INTO @result(TanuloId, Cim, CimTipusa, GondviseloId)
|
|
SELECT DISTINCT
|
|
tanulo.TanuloId AS TanuloId
|
|
,cim.C_IRANYITOSZAM + ' ' + cim.C_VAROS + ', ' + cim.C_KOZTERULET + ' ' + kozterulet.C_NAME + ' ' + cim.C_HAZSZAM AS Cim
|
|
,cim.C_CIMTIPUSA AS CimTipusa
|
|
,gondviselo.ID AS GondviseloId
|
|
FROM T_GONDVISELO_OSSZES gondviselo
|
|
INNER JOIN fnGetDokumentumOsztalyokCsoportokTanuloi(@pTanevId, @pOsztalyId, 'T') tanulo ON tanulo.TanuloId = gondviselo.C_TANULOID AND gondviselo.TOROLT = 'F'
|
|
CROSS APPLY (SELECT TOP 1 WITH TIES c.C_IRANYITOSZAM, c.C_VAROS, c.C_KOZTERULET, c.C_HAZSZAM, c.C_KOZTERULETJELLEGE, c.C_CIMTIPUSA, c.C_GONDVISELOID
|
|
FROM T_CIM_OSSZES c
|
|
WHERE c.C_GONDVISELOID = gondviselo.ID
|
|
AND c.C_TANEVID = @pTanevId
|
|
AND c.TOROLT = 'F'
|
|
AND c.C_GONDVISELOID IS NOT NULL
|
|
ORDER BY C_ALAPERTELMEZETT DESC ) cim
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES kozterulet ON kozterulet.Id = cim.C_KOZTERULETJELLEGE AND kozterulet.C_TANEVID = @pTanevId
|
|
END
|
|
|
|
RETURN
|
|
END |