54 lines
1.9 KiB
Transact-SQL
54 lines
1.9 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS [dbo].[sp_GetKozossegiJelentkezesLap]
|
|
GO
|
|
|
|
CREATE PROCEDURE [dbo].[sp_GetKozossegiJelentkezesLap]
|
|
@pIntezmenyId int
|
|
,@pTanevId int
|
|
,@pOsztalyId int
|
|
,@pTanuloId int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
/* TANULO */
|
|
SELECT
|
|
dbo.fnGetDokumentumDatumFormatum(f.C_SZULETESIDATUM) AS SzulIdo
|
|
,f.C_NYOMTATASINEV AS TanuloNeve
|
|
,f.C_SZULETESINEV AS SzuletesiNev
|
|
,ocs.C_NEV AS Osztaly
|
|
FROM T_TANULO_OSSZES t
|
|
INNER JOIN T_TANTERV_OSSZES tt ON tt.ID = t.C_TANTERVID AND tt.TOROLT ='F'
|
|
INNER JOIN T_FELHASZNALO_OSSZES f ON f.ID = t.ID AND f.TOROLT = 'F'
|
|
INNER JOIN T_TANULOCSOPORT_OSSZES tcs ON tcs.C_TANULOID = t.ID AND tcs.TOROLT = 'F' AND tcs.C_OSZTALYCSOPORTID = @pOsztalyId
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = tcs.C_OSZTALYCSOPORTID AND ocs.TOROLT = 'F' AND ocs.C_FELADATKATEGORIAID = (SELECT C_DEFAULTFELADATKATEGORIAID FROM T_TANEV_OSSZES WHERE ID = @pTanevId)
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES d on d.id=f.C_ALLAMPOLGARSAGA AND d.C_TANEVID = f.C_TANEVID AND d.C_INTEZMENYID = f.C_INTEZMENYID
|
|
WHERE t.ID = @pTanuloId
|
|
AND t.C_ALINTEZMENYID = @pIntezmenyId
|
|
AND t.C_ALTANEVID = @pTanevId
|
|
|
|
/* 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 tv
|
|
INNER JOIN T_INTEZMENYADATOK_OSSZES ia ON tv.ID = ia.C_TANEVID
|
|
WHERE tv.ID = @pTanevId
|
|
|
|
/* GONDVISELO */
|
|
SELECT g.C_NEV AS GondviseloNeve
|
|
FROM T_GONDVISELO_OSSZES g
|
|
WHERE C_TANULOID = @pTanuloId
|
|
AND g.C_ISTORVENYESKEPVISELO = 'T'
|
|
AND (g.C_ISCSOKKENTETTGONDVISELO IS NULL OR g.C_ISCSOKKENTETTGONDVISELO = 'F')
|
|
AND g.C_TANEVID = @pTanevId
|
|
|
|
END
|
|
GO
|
|
|
|
|