92 lines
2.2 KiB
Transact-SQL
92 lines
2.2 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,
|
|
@isEgyebFoglalkozasok BIT,
|
|
@pedagogusId INT,
|
|
@orarendErvenyessegiDatum DATE
|
|
AS
|
|
BEGIN
|
|
|
|
SELECT DISTINCT
|
|
IntezmenyAdatok.C_NEV as intezmeny
|
|
,ISNULL(IntezmenyAdatok.C_VAROS + ', ', '') + dbo.fnGetDokumentumDatumFormatum(GETDATE()) AS kelt
|
|
,(SELECT C_NYOMTATASINEV FROM T_FELHASZNALO_OSSZES WHERE Id = @pedagogusId) PedagogusNev
|
|
FROM T_INTEZMENY_OSSZES Intezmeny
|
|
INNER JOIN T_INTEZMENYADATOK_OSSZES IntezmenyAdatok ON IntezmenyAdatok.C_INTEZMENYID = Intezmeny.ID AND IntezmenyAdatok.C_TANEVID = @tanevid
|
|
WHERE
|
|
Intezmeny.Id = @intezmenyId
|
|
AND Intezmeny.TOROLT='F'
|
|
|
|
DECLARE @tabla TABLE (
|
|
PedagogusId INT,
|
|
Ora INT,
|
|
Hetirend INT,
|
|
PedagogusNev NVARCHAR(MAX),
|
|
Hetfo NVARCHAR(MAX),
|
|
Kedd NVARCHAR(MAX),
|
|
Szerda NVARCHAR(MAX),
|
|
Csutortok NVARCHAR(MAX),
|
|
Pentek NVARCHAR(MAX),
|
|
Szombat NVARCHAR(MAX),
|
|
HetirendNev NVARCHAR(MAX)
|
|
)
|
|
|
|
SELECT
|
|
Alkalmazott.ID
|
|
,Felhasznalo.C_NYOMTATASINEV Nev
|
|
INTO #Pedagogusok
|
|
FROM T_ALKALMAZOTT_OSSZES Alkalmazott
|
|
INNER JOIN (SELECT DISTINCT C_TANARID FROM T_ORARENDIORA_OSSZES WHERE C_TANEVID = @tanevID AND TOROLT = 'F') OrarendiOra on OrarendiOra.C_TANARID = Alkalmazott.Id
|
|
INNER JOIN T_FELHASZNALO_OSSZES Felhasznalo ON Felhasznalo.Id = Alkalmazott.Id
|
|
WHERE
|
|
Alkalmazott.torolt='F'
|
|
AND Alkalmazott.C_ALTANEVID = @tanevID
|
|
AND (@pedagogusId = -1 OR Alkalmazott.Id = @pedagogusId)
|
|
|
|
DECLARE kurzor CURSOR FOR
|
|
SELECT ID FROM #Pedagogusok
|
|
|
|
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, @isEgyebFoglalkozasok, @orarendErvenyessegiDatum
|
|
|
|
FETCH NEXT FROM kurzor INTO @TanarId
|
|
END
|
|
|
|
CLOSE kurzor
|
|
DEALLOCATE kurzor
|
|
|
|
SELECT
|
|
ID PedagogusId
|
|
,Nev
|
|
,Hetirend
|
|
FROM #Pedagogusok
|
|
CROSS JOIN (SELECT DISTINCT HetirendNev FROM @tabla) Hetirend (Hetirend)
|
|
ORDER BY Nev, Hetirend
|
|
|
|
SELECT
|
|
*
|
|
FROM @tabla
|
|
ORDER BY Hetirend, Ora
|
|
|
|
END
|
|
|
|
GO |