78 lines
2.4 KiB
Transact-SQL
78 lines
2.4 KiB
Transact-SQL
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
CREATE PROCEDURE [dbo].[sp_GetPedagogusOrarendje]
|
|
@PedagogusId int,
|
|
@TanevId int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
declare @tmpTable table (TanarId int, Ora int, Hetirend int, TanarNev nvarchar(255), [1408] nvarchar(255), [1409] nvarchar(255), [1410] nvarchar(255), [1411] nvarchar(255), [1412] nvarchar(255), [1413] nvarchar(255))
|
|
|
|
DECLARE @ID INT = 0
|
|
WHILE (1 = 1)
|
|
BEGIN
|
|
|
|
SELECT TOP 1 @ID = ID FROM T_HETIRENDTIPUS_OSSZES WHERE TOROLT ='F' and ID != 1554 and ID > @ID and C_ALTANEVID = @TanevId ORDER BY ID
|
|
IF @@ROWCOUNT = 0 BREAK;
|
|
|
|
declare @Napok table (NapId int) insert into @Napok select ID from T_DICTIONARYITEMBASE_OSSZES where ID in (1408,1409,1410,1411,1412,1413) and C_TANEVID = @TanevId
|
|
declare @Orak table (OraId int) insert into @Orak select C_ORASZAM from T_CSENGETESIRENDORA where TOROLT = 'F' and C_TANEVID = @TanevId
|
|
declare @TenylegesOrak table (OraId int, NapId int, Leiras nvarchar(MAX), HetirendID int)
|
|
insert into @TenylegesOrak
|
|
select
|
|
csro.C_ORASZAM as ORASZAM
|
|
,oo.C_HETNAPJA as HETNAP
|
|
,ocs.C_NEV + ' - ' + tt.C_NEV + ' - ' + t.C_NEV as MATRIX_DATA
|
|
,@ID AS HetiRendID
|
|
from T_ALKALMAZOTT_OSSZES a
|
|
join T_FELHASZNALO_OSSZES f on f.ID = a.ID
|
|
join T_FOGLALKOZAS_OSSZES fo on fo.C_TANARID = a.ID
|
|
join T_TANTARGY_OSSZES tt on tt.ID = fo.C_TANTARGYID
|
|
join T_OSZTALYCSOPORT_OSSZES ocs on ocs.ID = fo.C_OSZTALYCSOPORTID
|
|
join T_ORARENDIORA_OSSZES oo on oo.C_FOGLALKOZASID = fo.ID
|
|
join T_TEREM_OSSZES t on t.ID = oo.C_TEREMID
|
|
left outer join T_CSENGETESIRENDORA_OSSZES csro on csro.ID = oo.C_CSENGETESIRENDORAID
|
|
where
|
|
a.ID = @PedagogusId
|
|
and a.C_ALTANEVID = @TanevId
|
|
and oo.C_HETIREND in (@ID,1554)
|
|
|
|
insert into @tmpTable
|
|
select * from
|
|
(
|
|
select
|
|
@PedagogusId as TanarId,
|
|
O.OraId as Ora
|
|
,N.NapId as Nap
|
|
,T.Leiras as Leiras
|
|
,@ID as HetiRend
|
|
,(select top 1 f.C_NYOMTATASINEV from T_FELHASZNALO_OSSZES f where f.ID = @PedagogusId and f.C_TANEVID = @TanevId and f.TOROLT = 'F') as TanarNev
|
|
from @Napok N
|
|
cross join @Orak O
|
|
left join @TenylegesOrak T on T.NapId = N.NapId and T.OraId = O.OraId AND T.HetiRendID in (@ID,1554)
|
|
) o1
|
|
PIVOT
|
|
(
|
|
MAX (Leiras)
|
|
FOR [Nap]
|
|
IN ([1408], [1409], [1410], [1411], [1412], [1413])
|
|
) p1
|
|
END
|
|
|
|
select * from @tmpTable
|
|
|
|
END
|
|
|
|
|
|
|
|
|
|
|
|
GO
|
|
|
|
|