82 lines
1.9 KiB
Transact-SQL
82 lines
1.9 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.MM.dd.') AS kelt
|
|
FROM T_INTEZMENY_OSSZES i
|
|
INNER JOIN T_INTEZMENYADATOK_OSSZES ia ON ia.C_INTEZMENYID=I.ID AND ia.C_TANEVID = @tanevid
|
|
INNER JOIN T_MUKODESIHELY_OSSZES m ON ia.C_INTEZMENYID = m.C_INTEZMENYID AND m.TOROLT = 'F' AND m.C_TANEVID = @tanevID
|
|
INNER JOIN T_FELADATELLATASIHELY_OSSZES f ON m.ID = f.C_MUKODESIHELYID AND f.TOROLT = 'F' AND f.C_TANEVID = @tanevID
|
|
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 kurzor 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 kurzor
|
|
FETCH NEXT FROM kurzor INTO @TanarId
|
|
|
|
WHILE @@FETCH_STATUS = 0 BEGIN
|
|
INSERT INTO @tabla
|
|
|
|
EXEC sp_GetPedagogusOrarend @intezmenyId, @tanevid, @tanarid, @aktivTanevId
|
|
|
|
FETCH NEXT FROM kurzor INTO @TanarId
|
|
END
|
|
|
|
CLOSE kurzor
|
|
DEALLOCATE kurzor
|
|
|
|
SELECT
|
|
*
|
|
FROM @tabla
|
|
ORDER BY Hetirend, Ora
|
|
|
|
END
|
|
|
|
GO |