73 lines
2.3 KiB
Transact-SQL
73 lines
2.3 KiB
Transact-SQL
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
IF OBJECT_ID('[dbo].[sp_GetOsztalyOrarend]') IS NOT NULL
|
|
BEGIN
|
|
DROP PROCEDURE [dbo].[sp_GetOsztalyOrarend]
|
|
END
|
|
GO
|
|
|
|
|
|
CREATE PROCEDURE [dbo].[sp_GetOsztalyOrarend]
|
|
@tanevID INT
|
|
,@intezmenyId INT
|
|
,@aktivTanevId INT
|
|
,@osztalyId INT
|
|
,@isEgyebFoglalkozasok BIT
|
|
,@orarendErvenyessegiDatum DATE
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT DISTINCT
|
|
ia.C_NEV as intezmeny
|
|
,m.C_NEV as mukodesihely
|
|
,d.C_NAME as oktatasiNevelesiFeladatTipus
|
|
,ISNULL(ia.C_VAROS + ', ', '') + dbo.fnGetDokumentumDatumFormatum(GETDATE()) 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 (osztalyId INT, Ora INT, HetirendId INT, Hetfo NVARCHAR(MAX), Kedd NVARCHAR(MAX), Szerda NVARCHAR(MAX), Csutortok NVARCHAR(MAX), Pentek NVARCHAR(MAX), Szombat NVARCHAR(MAX), Vasarnap NVARCHAR(MAX), Hetirend NVARCHAR(MAX),Osztalynev NVARCHAR(MAX))
|
|
|
|
SELECT
|
|
ocs.Id OsztalyId
|
|
,ocs.C_NEV OsztalyNev
|
|
INTO #Osztalyok
|
|
FROM T_OSZTALYCSOPORT_OSSZES ocs
|
|
INNER JOIN T_OSZTALY_OSSZES o ON ocs.Id=o.Id
|
|
WHERE
|
|
ocs.TOROLT='F'
|
|
AND ocs.C_TANEVID = @tanevID
|
|
AND ocs.C_INTEZMENYID = @intezmenyId
|
|
AND ocs.Id = @osztalyId
|
|
|
|
INSERT INTO @tabla
|
|
EXEC sp_GetOrarendOsztalyonkent @intezmenyId, @tanevID, @osztalyId, @aktivTanevId, @isEgyebFoglalkozasok, @orarendErvenyessegiDatum
|
|
|
|
SELECT
|
|
OsztalyId
|
|
,OsztalyNev
|
|
,Hetirend
|
|
FROM #Osztalyok Osztalyok
|
|
CROSS JOIN (SELECT DISTINCT Hetirend FROM @tabla) Hetirend (Hetirend)
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES OsztalyCsoport ON OsztalyCsoport.Id = Osztalyok.OsztalyId
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES EvfolyamTipusa ON EvfolyamTipusa.Id = OsztalyCsoport.C_EVFOLYAMTIPUSA AND EvfolyamTipusa.C_TANEVID = @tanevId
|
|
ORDER BY C_ORDER, C_NEV
|
|
|
|
SELECT
|
|
OsztalyokOrarendje.*
|
|
FROM @tabla OsztalyokOrarendje
|
|
ORDER BY Ora
|
|
|
|
END
|
|
|
|
GO |