59 lines
1.7 KiB
Transact-SQL
59 lines
1.7 KiB
Transact-SQL
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
IF OBJECT_ID('[dbo].[sp_GetPedagogusOrarendOsszes]') IS NOT NULL
|
|
BEGIN
|
|
DROP PROCEDURE [dbo].[sp_GetPedagogusOrarendOsszes]
|
|
END
|
|
GO
|
|
|
|
CREATE PROCEDURE [dbo].[sp_GetPedagogusOrarendOsszes]
|
|
@tanevID INT,
|
|
@intezmenyId INT,
|
|
@aktivTanevId INT
|
|
AS
|
|
BEGIN
|
|
|
|
SELECT DISTINCT ia.C_NEV as intezmeny, m.C_NEV as mukodesihely, d.C_NAME as oktatasiNevelesiFeladatTipus,
|
|
ISNULL(ia.C_VAROS + ', ', '') + FORMAT(GETDATE(), 'yyyy. MMMM dd.', 'hu-hu') AS kelt
|
|
FROM T_INTEZMENY_OSSZES i
|
|
INNER JOIN T_INTEZMENYADATOK_OSSZES IA ON IA.C_INTEZMENYID=I.ID
|
|
INNER JOIN T_MUKODESIHELY_OSSZES m ON IA.C_INTEZMENYID = m.C_INTEZMENYID AND m.TOROLT = 'F'
|
|
INNER JOIN T_FELADATELLATASIHELY_OSSZES f ON m.ID = f.C_MUKODESIHELYID AND f.TOROLT = 'F'
|
|
INNER JOIN (SELECT DISTINCT ID, C_NAME FROM T_DICTIONARYITEMBASE_OSSZES) d ON d.id = f.C_OKTATASINEVELESIFELADATTIPUS
|
|
WHERE i.id=@intezmenyId AND I.TOROLT='F'
|
|
|
|
|
|
DECLARE @tabla TABLE (PedagogusId INT, Ora NVARCHAR(MAX), Hetirend INT, PedagogusNev NVARCHAR(MAX), Hetfo NVARCHAR(MAX), Kedd NVARCHAR(MAX), Szerda NVARCHAR(MAX), Csutortok NVARCHAR(MAX), Pentek NVARCHAR(MAX), Szombat NVARCHAR(MAX), Hetirend_DNAME NVARCHAR(MAX), Rendez_ora INT, Rendez_perc INT)
|
|
|
|
DECLARE kur CURSOR FOR
|
|
SELECT ID FROM T_ALKALMAZOTT_OSSZES a
|
|
INNER JOIN (SELECT DISTINCT c_tanarokid FROM T_FOGLALKOZASOK_TANAROK) ft on ft.c_tanarokid=a.id
|
|
where a.torolt='F' and a.C_ALTANEVID=@tanevID
|
|
|
|
DECLARE @TanarId int
|
|
|
|
OPEN kur
|
|
FETCH NEXT FROM kur INTO @TanarId
|
|
|
|
WHILE @@FETCH_STATUS = 0 BEGIN
|
|
INSERT INTO @tabla
|
|
|
|
EXEC sp_GetPedagogusOrarend @intezmenyId, @tanevid, @tanarid, @aktivTanevId
|
|
|
|
FETCH NEXT FROM kur INTO @TanarId
|
|
END
|
|
|
|
CLOSE kur
|
|
DEALLOCATE kur
|
|
|
|
SELECT
|
|
*
|
|
FROM @tabla
|
|
ORDER BY Hetirend, Ora
|
|
|
|
END
|
|
|
|
GO |