61 lines
2.1 KiB
Transact-SQL
61 lines
2.1 KiB
Transact-SQL
|
|
DROP PROCEDURE IF EXISTS [dbo].[sp_GetKozossegiJelentkezesLap]
|
|
GO
|
|
|
|
CREATE PROCEDURE [dbo].[sp_GetKozossegiJelentkezesLap]
|
|
@pIntezmenyId INT
|
|
,@pTanevId INT
|
|
,@pOsztalyId INT
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @kategoriaId int = (SELECT C_FELADATKATEGORIAID FROM T_OSZTALYCSOPORT_OSSZES WHERE ID = @pOsztalyId)
|
|
|
|
/* TANULO */
|
|
SELECT
|
|
dbo.fnGetDokumentumDatumFormatum(f.C_SZULETESIDATUM) AS SzulIdo
|
|
,f.C_NYOMTATASINEV AS TanuloNeve
|
|
,f.C_SZULETESINEV AS SzuletesiNev
|
|
,ocs.C_NEV AS Osztaly
|
|
,t.ID AS TanuloId
|
|
,f.C_OKTATASIAZONOSITO AS OktAzon
|
|
INTO #Tanulok
|
|
FROM T_TANULO_OSSZES AS t
|
|
INNER JOIN T_TANTERV_OSSZES AS tt ON tt.ID = t.C_TANTERVID AND tt.TOROLT ='F'
|
|
INNER JOIN T_FELHASZNALO_OSSZES AS f ON f.ID = t.ID AND f.TOROLT = 'F'
|
|
INNER JOIN T_TANULOCSOPORT_OSSZES AS tcs ON tcs.C_TANULOID = t.ID AND tcs.TOROLT = 'F' AND tcs.C_OSZTALYCSOPORTID = @pOsztalyId
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES AS ocs ON ocs.ID = tcs.C_OSZTALYCSOPORTID AND ocs.TOROLT = 'F' AND ocs.C_FELADATKATEGORIAID = @kategoriaId
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES AS d ON d.id=f.C_ALLAMPOLGARSAGA AND d.C_TANEVID = f.C_TANEVID AND d.C_INTEZMENYID = f.C_INTEZMENYID
|
|
WHERE ocs.ID = @pOsztalyId
|
|
AND t.C_ALINTEZMENYID = @pIntezmenyId
|
|
AND t.C_ALTANEVID = @pTanevId
|
|
|
|
SELECT * FROM #Tanulok
|
|
|
|
/* INTEZMENYADATOK */
|
|
SELECT DISTINCT
|
|
ia.C_NEV AS IntezmenyNev
|
|
,ia.C_IGAZGATONEVE AS IgazgatoNev
|
|
,dbo.fnGetDokumentumIntezmenyCime (@pTanevId) AS IntezmenyCim
|
|
,ia.C_TELEFONSZAM AS IntezmenyTelefonszam
|
|
,ia.C_EMAILCIM AS IntezmenyEmail
|
|
,ia.C_OMKOD AS OktAzon
|
|
,tv.C_NEV AS Tanev
|
|
FROM T_TANEV_OSSZES AS tv
|
|
INNER JOIN T_INTEZMENYADATOK_OSSZES AS ia ON tv.ID = ia.C_TANEVID
|
|
WHERE tv.ID = @pTanevId
|
|
|
|
/* GONDVISELO */
|
|
SELECT g.C_NEV AS GondviseloNeve
|
|
,g.C_TANULOID AS TanuloId
|
|
FROM T_GONDVISELO_OSSZES AS g
|
|
INNER JOIN #Tanulok AS Tanulok ON Tanulok.TanuloId = g.C_TANULOID
|
|
WHERE g.C_ISTORVENYESKEPVISELO = 'T'
|
|
AND (g.C_ISCSOKKENTETTGONDVISELO IS NULL OR g.C_ISCSOKKENTETTGONDVISELO = 'F')
|
|
AND g.C_TANEVID = @pTanevId
|
|
END
|
|
GO
|
|
|
|
|