43 lines
1.8 KiB
Transact-SQL
43 lines
1.8 KiB
Transact-SQL
-- =============================================
|
|
-- Description: Tanulók belépési adatai
|
|
-- =============================================
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
IF OBJECT_ID('[dbo].[sp_GetTanuloJelszoAdatok]') IS NOT NULL
|
|
BEGIN
|
|
DROP PROCEDURE [dbo].[sp_GetTanuloJelszoAdatok]
|
|
END
|
|
GO
|
|
|
|
|
|
CREATE PROCEDURE [dbo].[sp_GetTanuloJelszoAdatok]
|
|
@intezmenyId int,
|
|
@tanevId int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
declare @Hozzaferesek table (Osztaly nvarchar(max), OsztalyId int, Intezmeny nvarchar(max), TanuloNev nvarchar(max), Azonosito nvarchar(max), Jelszo nvarchar(max), Link nvarchar(max))
|
|
insert into @Hozzaferesek
|
|
select Osztaly.Osztaly, Osztaly.Osztalyid, ia.c_nev Intezmeny, f.c_nyomtatasinev TanuloNev, fb.c_bejelentkezesinev Azonosito, fb.c_jelszo Jelszo, 'https://'+lower(i.c_azonosito)+'.e-kreta.hu' as Link from t_tanulo t
|
|
inner join t_felhasznalo f on f.id=t.id and f.torolt='F'
|
|
inner join t_felhasznalobelepes fb on fb.c_felhasznaloid=t.id and fb.torolt='F'
|
|
left join (select tcs.c_tanuloid TanuloId, ocs.c_nev as Osztaly, o.Id OsztalyId from t_tanulocsoport tcs
|
|
inner join t_osztaly o on o.id=tcs.c_osztalycsoportid and o.torolt='F' and o.c_alintezmenyid=@intezmenyid and o.c_altanevid=@tanevid
|
|
inner join t_osztalycsoport ocs on ocs.id=o.id and ocs.c_intezmenyid=@intezmenyid and ocs.c_tanevid=@tanevid
|
|
where tcs.torolt='F' and tcs.c_intezmenyid=@intezmenyid and tcs.c_tanevid=@tanevId) Osztaly on Osztaly.Tanuloid=t.id
|
|
inner join t_intezmenyadatok ia on ia.c_intezmenyid=t.c_alintezmenyid and ia.c_tanevid=@tanevId and ia.torolt='F'
|
|
inner join t_intezmeny i on i.id=ia.c_intezmenyid and i.torolt='F'
|
|
where t.c_alintezmenyid=@intezmenyid and t.c_altanevid=@tanevid
|
|
order by TanuloNev
|
|
|
|
select Osztaly, Intezmeny, TanuloNev, Azonosito, Jelszo, Link from @Hozzaferesek
|
|
|
|
END
|
|
GO
|
|
|