174 lines
6.4 KiB
Transact-SQL
174 lines
6.4 KiB
Transact-SQL
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
IF OBJECT_ID('sp_GetTeremOrarend') IS NOT NULL
|
|
BEGIN
|
|
DROP PROCEDURE sp_GetTeremOrarend
|
|
END
|
|
GO
|
|
|
|
CREATE PROCEDURE sp_GetTeremOrarend
|
|
@intezmenyId INT,
|
|
@tanevId INT,
|
|
@teremId INT,
|
|
@aktivTanevId INT
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
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 = @tanevId
|
|
|
|
DECLARE @UtolsoNap DATE = (SELECT TOP 1 C_DATUM FROM T_TANEVRENDJE_OSSZES WHERE C_NAPTIPUSA=1395 AND C_TANEVID=@tanevid AND TOROLT='F')
|
|
,@ElsoNap DATE = (SELECT TOP 1 C_DATUM FROM T_TANEVRENDJE_OSSZES WHERE C_NAPTIPUSA=1394 AND C_TANEVID=@tanevid AND TOROLT='F')
|
|
,@IdoszakKezdete DATE
|
|
,@IdoszakVege DATE
|
|
|
|
IF (@tanevId = @AktivtanevId AND GETDATE() <= @UtolsoNap)
|
|
BEGIN
|
|
IF (GETDATE() <= @ElsoNap)
|
|
BEGIN
|
|
SET @IdoszakKezdete = (SELECT TOP 1 C_HETKEZDONAPJA FROM T_NAPTARIHET_OSSZES WHERE C_HETSORSZAMA = 1 AND TOROLT = 'F' AND C_TANEVID = @tanevId)
|
|
SET @IdoszakVege = (SELECT TOP 1 C_HETUTOLSONAPJA FROM T_NAPTARIHET_OSSZES WHERE C_HETSORSZAMA = 3 AND TOROLT = 'F' AND C_TANEVID = @tanevId)
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SET @IdoszakKezdete =(SELECT TOP 1 nh.C_HETKEZDONAPJA FROM T_NAPTARIHET_OSSZES nh WHERE CONVERT(DATE, GETDATE())>=CONVERT(DATE, NH.C_HETKEZDONAPJA) AND CONVERT(DATE, GETDATE())<=CONVERT(DATE, nh.C_HETUTOLSONAPJA) and nh.TOROLT='F' and nh.C_TANEVID=@tanevid)
|
|
SET @IdoszakVege = DATEADD(DAY, 13, @IdoszakKezdete)
|
|
END
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SET @IdoszakVege =(SELECT TOP 1 nh.C_HETUTOLSONAPJA FROM T_NAPTARIHET_OSSZES nh WHERE @UtolsoNap>=CONVERT(DATE, nh.C_HETKEZDONAPJA) AND @UtolsoNap<=CONVERT(DATE, nh.C_HETUTOLSONAPJA) AND nh.TOROLT='F' AND nh.C_TANEVID=@tanevId)
|
|
SET @IdoszakKezdete = DATEADD(DAY, -13, @IdoszakVege)
|
|
END
|
|
|
|
DECLARE @temp TABLE (HetirendId INT, HetnapjaId INT, Oraszam NVARCHAR(20), TantargyNev NVARCHAR(MAX), PedagogusNev NVARCHAR(MAX), OsztalyCsoportNev NVARCHAR(MAX))
|
|
INSERT INTO @temp
|
|
SELECT
|
|
OrarendiOra.C_HETIREND HetirendId
|
|
,OrarendiOra.C_HETNAPJA HetnapjaId
|
|
, CAST(DATEPART(HOUR, C_ORAKEZDETE) AS NVARCHAR(10)) + ':' + CAST(FORMAT(DATEPART(MINUTE, C_ORAKEZDETE), '00') AS NVARCHAR(10))
|
|
+ ' -' + char(13) + char(10) + CAST(DATEPART(HOUR, C_ORAVEGE) AS NVARCHAR(10)) + ':' + CAST(FORMAT(DATEPART(MINUTE, C_ORAVEGE), '00') AS NVARCHAR(10)) Oraszam
|
|
,ISNULL(Tantargy.C_NEVNYOMTATVANYBAN, Tantargy.C_NEV) TantargyNev
|
|
,f.C_NYOMTATASINEV TeremNev
|
|
,ocs.C_NEV OsztalyCsoportNev
|
|
FROM T_ORARENDIORA_OSSZES OrarendiOra
|
|
INNER JOIN T_TANTARGY_OSSZES Tantargy ON Tantargy.Id = OrarendiOra.C_TANTARGYID
|
|
INNER JOIN T_FELHASZNALO_OSSZES f ON f.ID = OrarendiOra.C_TANARID
|
|
INNER JOIN T_OSZTALYCSOPORT_OSSZES ocs ON ocs.ID = OrarendiOra.C_OSZTALYCSOPORTID
|
|
WHERE
|
|
OrarendiOra.Torolt = 'F'
|
|
AND OrarendiOra.C_ORAERVENYESSEGKEZDETE <= @IdoszakVege
|
|
AND OrarendiOra.C_ORAERVENYESSEGVEGE >= @IdoszakKezdete
|
|
AND OrarendiOra.C_ORAERVENYESSEGVEGE - OrarendiOra.C_ORAERVENYESSEGKEZDETE > 60
|
|
AND OrarendiOra.C_TEREMID = @teremId
|
|
|
|
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=@tanevId)
|
|
|
|
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=@tanevId
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
INSERT INTO @Hetirendek
|
|
SELECT DISTINCT oo.C_HETIREND from T_ORARENDIORA_OSSZES oo WHERE oo.TOROLT='F' AND oo.C_TANEVID=@tanevId AND oo.C_HETIREND<>1554
|
|
END
|
|
|
|
DECLARE @Orakezdetek TABLE (idotartam NVARCHAR(20), Ora INT, Perc INT)
|
|
INSERT INTO @orakezdetek
|
|
SELECT DISTINCT
|
|
CAST(DATEPART(HOUR, C_ORAKEZDETE) AS NVARCHAR(10)) + ':' + CAST(FORMAT(DATEPART(MINUTE, C_ORAKEZDETE), '00') AS NVARCHAR(10))
|
|
+ ' -' + char(13) + char(10) + CAST(DATEPART(HOUR, C_ORAVEGE) AS NVARCHAR(10)) + ':' + CAST(FORMAT(DATEPART(MINUTE, C_ORAVEGE), '00') AS NVARCHAR(10)) KezdVeg
|
|
,DATEPART(HH, C_ORAKEZDETE)
|
|
,DATEPART(MINUTE, C_ORAKEZDETE)
|
|
FROM T_ORARENDIORA_OSSZES
|
|
WHERE
|
|
C_TANEVID = @tanevId
|
|
AND TOROLT = 'F'
|
|
AND C_ORAERVENYESSEGVEGE - C_ORAERVENYESSEGKEZDETE > 60
|
|
|
|
DECLARE @OsszesOra TABLE (Hetirend INT, Nap INT, Oraszam NVARCHAR(20))
|
|
INSERT INTO @OsszesOra
|
|
SELECT DISTINCT oo.Id, Nap.Nap , OraSzam.OraSzam FROM @Hetirendek oo
|
|
CROSS JOIN (SELECT Idotartam FROM @Orakezdetek) OraSzam (OraSzam)
|
|
CROSS JOIN (VALUES (1408),(1409),(1410),(1411),(1412), (1413)) Nap (Nap)
|
|
|
|
DECLARE @TenylegesOra TABLE (Hetirend INT, Nap INT, OraSzam NVARCHAR(20), Foglalkozas NVARCHAR(MAX))
|
|
DECLARE @Orarend TABLE (TeremId INT, Ora NVARCHAR(20), Hetirend INT, Nap INT, Foglalkozas NVARCHAR(MAX), TeremNev NVARCHAR(MAX))
|
|
|
|
DECLARE kur CURSOR FOR
|
|
SELECT Id FROM @Hetirendek
|
|
|
|
DECLARE @HetirendId int
|
|
|
|
OPEN kur
|
|
FETCH NEXT FROM kur INTO @HetirendId
|
|
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
INSERT INTO @TenylegesOra
|
|
SELECT DISTINCT
|
|
@HetirendId
|
|
,veg.HetnapjaId
|
|
,veg.Oraszam
|
|
,STUFF((SELECT DISTINCT CAST('<b>' + bveg.TantargyNev + '</b>' + ' (' + OsztalyCsoportNev + ', ' + bveg.PedagogusNev + ')' + CHAR(13) + CHAR(10) AS VARCHAR(max))
|
|
FROM @temp bveg
|
|
WHERE bveg.HetnapjaId=veg.HetnapjaId
|
|
AND bveg.Oraszam=veg.Oraszam
|
|
AND (bveg.HetirendId=@HetirendId OR bveg.HetirendId=1554)
|
|
FOR XML PATH(''), TYPE)
|
|
.value('.','NVARCHAR(MAX)'),1,0,'') Foglalkozasok
|
|
FROM @temp veg
|
|
FETCH NEXT FROM kur INTO @HetirendId
|
|
END
|
|
CLOSE kur
|
|
DEALLOCATE kur
|
|
|
|
DECLARE @teremNev NVARCHAR(MAX) = (SELECT C_NEV FROM T_TEREM_OSSZES WHERE ID = @teremId)
|
|
|
|
/*Végleges órarend*/
|
|
INSERT INTO @Orarend
|
|
SELECT @teremId
|
|
, OsszesOra.Oraszam
|
|
, OsszesOra.Hetirend
|
|
, OsszesOra.Nap
|
|
, TenylegesOra.Foglalkozas
|
|
, @teremNev TeremNev
|
|
FROM @OsszesOra OsszesOra
|
|
LEFT JOIN @TenylegesOra TenylegesOra ON OsszesOra.Hetirend=TenylegesOra.Hetirend AND OsszesOra.Nap=TenylegesOra.Nap AND OsszesOra.Oraszam=TenylegesOra.Oraszam
|
|
ORDER BY OsszesOra.Hetirend, OsszesOra.Nap, OsszesOra.Oraszam
|
|
|
|
SELECT
|
|
TeremId
|
|
,pv.Ora
|
|
,Hetirend
|
|
,TeremNev
|
|
,[1408] 'Hetfo'
|
|
,[1409] 'Kedd'
|
|
,[1410] 'Szerda'
|
|
,[1411] 'Csutortok'
|
|
,[1412] 'Pentek'
|
|
,[1413] 'Szombat'
|
|
, dic.C_NAME HETIREND_DNAME
|
|
,ok.Ora Rendez_ora
|
|
,ok.Perc Rendez_perc
|
|
FROM @Orarend
|
|
PIVOT (MAX(Foglalkozas) FOR Nap in([1408],[1409],[1410],[1411],[1412],[1413]))pv
|
|
INNER JOIN T_DICTIONARYITEMBASE_OSSZES dic ON dic.id=Hetirend AND dic.C_TANEVID = @tanevId
|
|
INNER JOIN @Orakezdetek ok ON ok.Idotartam = pv.Ora
|
|
ORDER BY Hetirend, Ora
|
|
|
|
END
|
|
|
|
GO |