97 lines
1.9 KiB
Transact-SQL
97 lines
1.9 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspGetDokumentumTanuloOrarendOsztalyonkent
|
|
GO
|
|
|
|
CREATE PROCEDURE uspGetDokumentumTanuloOrarendOsztalyonkent
|
|
@tanevId int
|
|
,@osztalyId int
|
|
,@aktivTanevId int
|
|
,@isEgyebFoglalkozasok bit
|
|
,@pTanuloId int = NULL
|
|
,@orarendErvenyessegiDatum date
|
|
AS BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE
|
|
@feladatKategoriaId int
|
|
,@tanuloId int
|
|
|
|
DECLARE @tabla TABLE (
|
|
TanuloId int
|
|
,Ora int
|
|
,HetirendId int
|
|
,Hetfo nvarchar (max)
|
|
,Kedd nvarchar (max)
|
|
,Szerda nvarchar (max)
|
|
,Csutortok nvarchar (max)
|
|
,Pentek nvarchar (max)
|
|
,Szombat nvarchar (max)
|
|
,Hetirend nvarchar (max)
|
|
,TanuloNev nvarchar (max)
|
|
)
|
|
|
|
SELECT TOP 1
|
|
@feladatKategoriaId = o.C_FELADATKATEGORIAID
|
|
FROM T_OSZTALYCSOPORT_OSSZES o
|
|
WHERE o.ID = @osztalyId
|
|
|
|
SELECT
|
|
TanuloId
|
|
,C_NYOMTATASINEV AS TanuloNev
|
|
INTO #Tanulok
|
|
FROM fnGetDokumentumOsztalyokCsoportokTanuloi(@tanevId, @osztalyId, 'T') Tanulok
|
|
INNER JOIN T_FELHASZNALO_OSSZES Felhasznalo ON Felhasznalo.Id = Tanulok.TanuloId
|
|
WHERE @pTanuloId IS NULL
|
|
OR @pTanuloId = TanuloId
|
|
|
|
DECLARE kurzor CURSOR
|
|
FOR
|
|
SELECT
|
|
TanuloId
|
|
FROM #Tanulok
|
|
|
|
OPEN kurzor
|
|
|
|
FETCH NEXT FROM kurzor
|
|
INTO @tanuloId
|
|
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
INSERT INTO @tabla
|
|
EXEC sp_GetDokumentumTanuloOrarend
|
|
@tanevID
|
|
,@tanuloId
|
|
,@aktivTanevId
|
|
,@isEgyebFoglalkozasok
|
|
,@orarendErvenyessegiDatum
|
|
,@feladatKategoriaId
|
|
|
|
FETCH NEXT FROM kurzor INTO
|
|
@tanuloId
|
|
END
|
|
|
|
CLOSE kurzor
|
|
DEALLOCATE kurzor
|
|
|
|
SELECT
|
|
TanuloId
|
|
,TanuloNev
|
|
,Hetirend
|
|
FROM #Tanulok Tanulok
|
|
CROSS JOIN(
|
|
SELECT DISTINCT
|
|
Hetirend
|
|
FROM @tabla
|
|
) AS Hetirend(Hetirend)
|
|
ORDER BY
|
|
Hetirend
|
|
|
|
SELECT
|
|
OsztalyokOrarendje.*
|
|
FROM @tabla AS OsztalyokOrarendje
|
|
ORDER BY
|
|
Ora
|
|
|
|
END
|
|
GO
|