92 lines
2.5 KiB
Transact-SQL
92 lines
2.5 KiB
Transact-SQL
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
IF OBJECT_ID('[dbo].[sp_GetTeremOrarendOsszes]') IS NOT NULL
|
|
BEGIN
|
|
DROP PROCEDURE [dbo].[sp_GetTeremOrarendOsszes]
|
|
END
|
|
GO
|
|
CREATE PROCEDURE [dbo].[sp_GetTeremOrarendOsszes]
|
|
@pTanevId INT
|
|
,@pIntezmenyId INT
|
|
,@pAktivTanevId INT
|
|
,@isEgyebFoglalkozasok BIT
|
|
,@orarendErvenyessegiDatum DATE
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
/*INTEZMENY*/
|
|
SELECT
|
|
T_INTEZMENYADATOK_OSSZES.C_NEV IntezmenyNeve
|
|
,T_INTEZMENYADATOK_OSSZES.C_OMKOD IntezmenyOMKod
|
|
FROM T_INTEZMENYADATOK_OSSZES
|
|
WHERE
|
|
T_INTEZMENYADATOK_OSSZES.TOROLT = 'F'
|
|
AND T_INTEZMENYADATOK_OSSZES.C_TANEVID = @pTanevId
|
|
|
|
/*TERMEK*/
|
|
DECLARE @Hetirendek TABLE (Id INT)
|
|
DECLARE @HetirendCount INT = (SELECT COUNT(DISTINCT oo.C_HETIREND) FROM T_ORARENDIORA_OSSZES oo WHERE oo.TOROLT='F' AND oo.C_TANEVID=@pTanevId)
|
|
|
|
IF (@HetirendCount=1)
|
|
BEGIN
|
|
INSERT INTO @Hetirendek
|
|
SELECT DISTINCT oo.c_hetirend FROM T_ORARENDIORA_OSSZES oo WHERE oo.TOROLT='F' AND oo.C_TANEVID = @pTanevId
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
INSERT INTO @Hetirendek
|
|
SELECT DISTINCT oo.C_HETIREND from T_ORARENDIORA_OSSZES oo WHERE oo.TOROLT='F' AND oo.C_TANEVID=@pTanevId AND oo.C_HETIREND <> 1554
|
|
END
|
|
|
|
SELECT
|
|
Terem.Id TeremId
|
|
,Terem.C_NEV TeremNev
|
|
,Hetirend HetirendId
|
|
,HetirendDictionary.C_NAME HetirendNev
|
|
FROM T_TEREM_OSSZES Terem
|
|
CROSS JOIN (SELECT ID FROM @Hetirendek) Hetirend (Hetirend)
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES HetirendDictionary ON HetirendDictionary.id = Hetirend AND HetirendDictionary.C_TANEVID = @pTanevId
|
|
WHERE
|
|
Terem.C_TANEVID = @pTanevId
|
|
AND Terem.TOROLT = 'F'
|
|
ORDER BY TeremNev, HetirendNev
|
|
|
|
DECLARE @tabla TABLE (
|
|
TeremId INT
|
|
,Ora INT
|
|
,HetirendId INT
|
|
,TeremNev NVARCHAR(MAX)
|
|
,Hetfo NVARCHAR(MAX)
|
|
,Kedd NVARCHAR(MAX)
|
|
,Szerda NVARCHAR(MAX)
|
|
,Csutortok NVARCHAR(MAX)
|
|
,Pentek NVARCHAR(MAX)
|
|
,Szombat NVARCHAR(MAX))
|
|
|
|
DECLARE kurzor CURSOR FOR
|
|
SELECT
|
|
ID
|
|
FROM T_TEREM_OSSZES
|
|
WHERE
|
|
TOROLT = 'F'
|
|
AND C_TANEVID = @pTanevId
|
|
AND C_INTEZMENYID = @pIntezmenyId
|
|
|
|
DECLARE @teremId INT
|
|
OPEN kurzor
|
|
FETCH NEXT FROM kurzor INTO @teremId
|
|
WHILE @@FETCH_STATUS = 0 BEGIN
|
|
INSERT INTO @tabla
|
|
EXEC dbo.sp_GetTeremOrarend @pIntezmenyId, @pTanevId, @teremId, @pAktivTanevId, @isEgyebFoglalkozasok, @orarendErvenyessegiDatum, 0
|
|
|
|
FETCH NEXT FROM kurzor INTO @teremId
|
|
END
|
|
CLOSE kurzor
|
|
DEALLOCATE kurzor
|
|
|
|
SELECT * from @tabla ORDER BY Ora
|
|
END
|
|
GO |