250 lines
11 KiB
Transact-SQL
250 lines
11 KiB
Transact-SQL
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
IF OBJECT_ID('dbo.sp_GetOrarendOsztalyonkent') IS NOT NULL BEGIN
|
|
DROP PROCEDURE dbo.sp_GetOrarendOsztalyonkent
|
|
END
|
|
GO
|
|
|
|
CREATE PROCEDURE [dbo].[sp_GetOrarendOsztalyonkent]
|
|
@intezmenyId int,
|
|
@tanevId int,
|
|
@osztalyId int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE
|
|
@TanuloId INT=null,
|
|
@CsakOrarendiOrak INT=1,
|
|
@TanarId INT=null
|
|
|
|
DECLARE @osztalyNev NVARCHAR(MAX)=(SELECT ocs.C_NEV FROM T_OSZTALYCSOPORT_OSSZES OCS WHERE ocs.ID=@osztalyId)
|
|
|
|
DECLARE @AktivtanevId INT = (SELECT ID FROM T_TANEV_OSSZES TANEV WHERE C_AKTIV='T' AND C_INTEZMENYID=@IntezmenyId)
|
|
|
|
DECLARE @HetEleje DATE
|
|
DECLARE @HetVege DATE
|
|
|
|
IF (@tanevId=@AktivtanevId)
|
|
BEGIN
|
|
SET @HetEleje =(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 and nh.C_INTEZMENYID=@intezmenyId)
|
|
SET @HetVege =(SELECT TOP 1 nh.C_HETUTOLSONAPJA 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 and nh.C_INTEZMENYID=@intezmenyId)
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
DECLARE @UtolsoNap DATE = (SELECT TOP 1 C_NAPDATUMA FROM T_NAPTARINAP_OSSZES WHERE C_NAPTIPUSA=1395 AND C_INTEZMENYID=@intezmenyId AND C_TANEVID=@tanevid AND TOROLT='F')
|
|
SET @HetEleje =(SELECT TOP 1 nh.C_HETKEZDONAPJA 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 AND NH.C_INTEZMENYID=@intezmenyId)
|
|
SET @HetVege =(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 AND NH.C_INTEZMENYID=@intezmenyId)
|
|
END
|
|
|
|
|
|
DECLARE
|
|
@KezdoHet INT = DATEPART( ISO_WEEK, @HetEleje),
|
|
@ZaroHet INT = DATEPART( ISO_WEEK, @HetVege);
|
|
|
|
CREATE TABLE #Vegleges (Hetirend INT, Hetnapja INT, Oraszam INT, TargyNev NVARCHAR(MAX), TanarNev NVARCHAR(MAX), TeremNev NVARCHAR(MAX), OsztalyNev NVARCHAR(MAX))
|
|
|
|
SELECT
|
|
Datum,
|
|
ISNULL(TanevHetirend, NapHetirend) Hetirend,
|
|
ISNULL(TanevHetNapja, NapHetNapja) HetNapja,
|
|
CsengetesiRendId
|
|
INTO #TEMPORARENDINAPOK
|
|
FROM (
|
|
SELECT
|
|
NAPTARINAP.Datum Datum,
|
|
napHetirend.C_HETIREND NapHetirend,
|
|
napHetirend.C_HETNAPJA NapHetNapja,
|
|
tanevRendHetirend.C_HETIREND TanevHetirend,
|
|
tanevRendHetirend.C_HETNAPJA TanevHetNapja,
|
|
tanevRendHetirend.C_CSENGETESIRENDID CsengetesiRendId
|
|
FROM /*ÁTÍRANDÓÓÓ*/(
|
|
/*Minden naptári nap, ami az időintervallumba beleesik.*/
|
|
(SELECT C_NAPDATUMA Datum FROM T_NAPTARINAP_OSSZES NAPTARINAP WHERE (TOROLT = 'F' AND C_NAPDATUMA BETWEEN @HetEleje AND @HetVege AND C_INTEZMENYID = @IntezmenyId AND C_TANEVID=@TanevId))
|
|
EXCEPT /*Kivéve, ha van aznap kiemelt típusú tanévrendje esemény, vagy olyan esemény, aminél C_ORARENDINAP = 'F'.*/
|
|
(SELECT C_DATUM Datum FROM T_TANEVRENDJE_OSSZES TANEVRENDJE WHERE (TOROLT = 'F' AND (C_NAPTIPUSA IN (1386,1387,1389,1391,1392,1396,1397,1398,1399,1401) OR C_ORARENDINAP = 'F') AND C_INTEZMENYID = @IntezmenyId AND C_TANEVID=@TanevId))
|
|
UNION /*Azokat a dátumokat vegyük hozzá, amely napokon van C_ORARENDINAP = 'T' és C_ORARENDINAP = 'F' esemény is felvéve.*/
|
|
SELECT Datum
|
|
FROM (
|
|
SELECT
|
|
C_DATUM Datum,
|
|
COUNT (C_DATUM) DatumCounter
|
|
FROM (
|
|
SELECT C_DATUM, C_ORARENDINAP
|
|
FROM T_TANEVRENDJE_OSSZES TANEVRENDJE
|
|
WHERE (TOROLT = 'F') AND C_INTEZMENYID = @IntezmenyId AND C_TANEVID=@TanevId) TANEVRENDJE1
|
|
GROUP BY C_ORARENDINAP, C_DATUM
|
|
) TANEVRENDJE2
|
|
GROUP BY Datum
|
|
HAVING COUNT(Datum) = 2
|
|
|
|
) NAPTARINAP
|
|
LEFT JOIN (SELECT C_NAPDATUMA, C_HETIREND, IIF(C_HETNAPJA = 1407, dbo.fnGetHetNapja(C_NAPDATUMA), C_HETNAPJA) AS C_HETNAPJA from T_NAPTARINAP_OSSZES WHERE TOROLT = 'F') napHetirend on napHetirend.C_NAPDATUMA = NAPTARINAP.Datum
|
|
LEFT JOIN (SELECT C_DATUM, C_HETIREND,C_CSENGETESIRENDID, IIF(C_HETNAPJA = 1407, dbo.fnGetHetNapja(C_DATUM), C_HETNAPJA) AS C_HETNAPJA from T_TANEVRENDJE_OSSZES WHERE TOROLT = 'F') tanevRendHetirend on tanevRendHetirend.C_DATUM = NAPTARINAP.Datum
|
|
) NapokHetirenddel
|
|
|
|
-- Vegzosok utolso tanitasi napja
|
|
DECLARE @VegzosUtolsoTanitasiNap_spGetOrarend DATETIME;
|
|
SELECT @VegzosUtolsoTanitasiNap_spGetOrarend = C_DATUM
|
|
FROM T_TANEVRENDJE_OSSZES
|
|
WHERE TOROLT = 'F'
|
|
AND C_TANEVID = (SELECT ID FROM T_TANEV_OSSZES WHERE TOROLT = 'F' AND C_AKTIV = 'T' AND C_INTEZMENYID = @IntezmenyId)
|
|
AND C_NAPTIPUSA = 1402 AND C_INTEZMENYID = @IntezmenyId AND C_TANEVID=@TanevId
|
|
|
|
|
|
DECLARE @tempCsoportok TABLE (CsoportID int PRIMARY KEY, Oka nvarchar(20))
|
|
-- Kapcsolódó csoportok
|
|
IF @osztalyId IS NOT NULL BEGIN
|
|
INSERT INTO @tempCsoportok (CsoportID, Oka)
|
|
SELECT ID as CsoportID, OKA AS Oka
|
|
FROM fnGetKapcsolodoOsztalycsoportokByDate(@osztalyId,@HetEleje,@HetVege)
|
|
END
|
|
ELSE BEGIN
|
|
INSERT INTO @tempCsoportok (CsoportID, Oka)
|
|
SELECT ID as CsoportID, '' AS Oka
|
|
FROM T_OSZTALYCSOPORT WHERE C_TANEVID = @TanevId
|
|
END
|
|
|
|
-- Órarendi órák
|
|
insert into #vegleges
|
|
Select Hetirend, HetNapja, Oraszam, TargyNev, TanarNev, TeremNev, OsztalyNev
|
|
FROM (
|
|
SELECT
|
|
T_ORARENDIORA_OSSZES.ID Id,
|
|
T_ORARENDIORA_OSSZES.ID ORARENDIID,
|
|
T_ORARENDIORA_OSSZES.C_BONTOTT Bontott,
|
|
T_ORARENDIORA_OSSZES.C_HETIREND Hetirend,
|
|
T_ORARENDIORA_OSSZES.C_HETNAPJA HetNapja,
|
|
T_ORARENDIORA_OSSZES.C_ORAERVENYESSEGKEZDETE ErvenyessegKezdete,
|
|
T_ORARENDIORA_OSSZES.C_ORAERVENYESSEGVEGE ErvenyessegVege,
|
|
T_ORARENDIORA_OSSZES.C_ORAKEZDETE OraKezdete,
|
|
T_ORARENDIORA_OSSZES.C_ORAVEGE OraVege,
|
|
T_CSENGETESIRENDORA_OSSZES.C_ORASZAM Oraszam,
|
|
ISNULL(TARGY.C_ROVIDNEV, targy.C_NEV) TargyNev,
|
|
'F' Megtartott,
|
|
T_CSENGETESIRENDORA_OSSZES.ID CsengetesiRendOraID,
|
|
OSZTALY.C_NEV AS OsztalyNev,
|
|
tcs.OKA AS MegjelenesOka,
|
|
TANAR.C_NYOMTATASINEV TanarNev,
|
|
TEREM.C_NEV TeremNev,
|
|
'OrarendiOra' OraTipus,
|
|
'F' Hianyzas,
|
|
'F' Keses,
|
|
TANAR.ID TanarID
|
|
FROM T_ORARENDIORA_OSSZES
|
|
INNER JOIN @tempCsoportok tcs ON tcs.CsoportID = T_ORARENDIORA_OSSZES.C_OSZTALYCSOPORTID
|
|
INNER JOIN T_CSENGETESIRENDORA_OSSZES on (T_CSENGETESIRENDORA_OSSZES.ID=T_ORARENDIORA_OSSZES.C_CSENGETESIRENDORAID AND T_CSENGETESIRENDORA_OSSZES.C_INTEZMENYID = @IntezmenyId AND T_CSENGETESIRENDORA_OSSZES.C_TANEVID=@TanevId)
|
|
INNER JOIN (select Id,C_TANEVID,C_INTEZMENYID from T_CSENGETESIREND_OSSZES where C_AKTIV = 'T') AktivCsengetesiRend on ( AktivCsengetesiRend.C_TANEVID = @TanevId and AktivCsengetesiRend.C_INTEZMENYID = @IntezmenyId)
|
|
LEFT JOIN T_TANTARGY_OSSZES TARGY ON TARGY.ID = T_ORARENDIORA_OSSZES.C_TANTARGYID
|
|
LEFT JOIN T_FELHASZNALO_OSSZES TANAR ON TANAR.ID = T_ORARENDIORA_OSSZES.C_TANARID
|
|
LEFT JOIN T_OSZTALYCSOPORT_OSSZES OSZTALY ON T_ORARENDIORA_OSSZES.C_OSZTALYCSOPORTID = OSZTALY.ID
|
|
LEFT JOIN T_TEREM_OSSZES TEREM on (TEREM.id = T_ORARENDIORA_OSSZES.C_TEREMID AND TEREM.C_INTEZMENYID = @IntezmenyId AND TEREM.C_TANEVID=@TanevId)
|
|
WHERE T_ORARENDIORA_OSSZES.TOROLT='F'
|
|
AND (T_ORARENDIORA_OSSZES.C_TANORANKIVULIFOGLALKOZAS = 'F' OR T_ORARENDIORA_OSSZES.C_TANORANKIVULIFOGLALKOZAS IS NULL)
|
|
AND T_ORARENDIORA_OSSZES.C_INTEZMENYID = @IntezmenyId
|
|
AND T_ORARENDIORA_OSSZES.C_TANEVID = @TanevId
|
|
AND T_ORARENDIORA_OSSZES.C_ORAERVENYESSEGVEGE >= @HetEleje
|
|
|
|
AND ( @TanuloId IS NULL
|
|
OR EXISTS (
|
|
SELECT 1
|
|
FROM T_TANULOCSOPORT_OSSZES TANCSOP
|
|
WHERE TANCSOP.C_OSZTALYCSOPORTID = T_ORARENDIORA_OSSZES.C_OSZTALYCSOPORTID
|
|
AND TANCSOP.TOROLT = 'F' AND TANCSOP.C_INTEZMENYID = @IntezmenyId AND TANCSOP.C_TANEVID=@TanevId
|
|
AND TANCSOP.C_BELEPESDATUM < GETDATE()
|
|
AND (TANCSOP.C_KILEPESDATUM IS NULL OR TANCSOP.C_KILEPESDATUM >= GETDATE()) -- TÖRÖLT
|
|
AND TANCSOP.C_TANULOID = @TanuloId )
|
|
)
|
|
) OrarendiOrak
|
|
LEFT JOIN T_HELYETTESITESIIDOSZAK_OSSZES on (OrarendiOrak.ID = T_HELYETTESITESIIDOSZAK_OSSZES.C_HELYETTESITETTORARENDID
|
|
And T_HELYETTESITESIIDOSZAK_OSSZES.C_HETSORSZAMA >= @KezdoHet
|
|
And (T_HELYETTESITESIIDOSZAK_OSSZES.C_HETSORSZAMA <= @ZaroHet) And T_HELYETTESITESIIDOSZAK_OSSZES.TOROLT = 'F'
|
|
) WHERE (@TanarId IS NULL OR OrarendiOrak.TanarID = @TanarId Or T_HELYETTESITESIIDOSZAK_OSSZES.C_HELYETTESTANAROKID = @TanarId)
|
|
|
|
|
|
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
|
|
DECLARE @TenylegesOra TABLE (Hetirend INT, Nap INT, OraSzam INT, Foglalkozas NVARCHAR(MAX))
|
|
|
|
DECLARE @Orarend TABLE (osztalyId INT, Ora INT, Hetirend INT, Nap INT, Foglalkozas NVARCHAR(MAX))
|
|
|
|
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 AND oo.C_INTEZMENYID=@intezmenyid)
|
|
|
|
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 AND oo.C_INTEZMENYID=@intezmenyid
|
|
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_INTEZMENYID=@intezmenyid AND oo.C_HETIREND<>1554
|
|
END
|
|
|
|
DECLARE @OsszesOra TABLE (Hetirend INT, Nap INT, Oraszam INT)
|
|
INSERT INTO @OsszesOra
|
|
SELECT DISTINCT oo.Id, Nap.Nap , OraSzam.OraSzam FROM @Hetirendek oo
|
|
CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) OraSzam (OraSzam)
|
|
CROSS JOIN (VALUES (1408),(1409),(1410),(1411),(1412), (1413)) Nap (Nap)
|
|
|
|
|
|
/*KURZOR*/
|
|
DECLARE kur CURSOR FOR
|
|
SELECT Id FROM @Hetirendek
|
|
|
|
DECLARE @HetirendId int
|
|
|
|
OPEN kur
|
|
FETCH NEXT FROM kur INTO @HetirendId
|
|
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
/*tényleges órák*/
|
|
INSERT INTO @TenylegesOra
|
|
SELECT DISTINCT
|
|
@HetirendId
|
|
, veg.Hetnapja
|
|
, veg.Oraszam
|
|
,STUFF((SELECT distinct ', ' + CAST(bveg.TargyNev+' ('+bveg.TanarNev+', '+bveg.TeremNev+')' AS VARCHAR(max))
|
|
FROM #Vegleges bveg
|
|
WHERE bveg.Hetnapja=veg.Hetnapja
|
|
AND bveg.Oraszam=veg.Oraszam
|
|
AND (bveg.Hetirend=@HetirendId OR bveg.Hetirend=1554)
|
|
FOR XML PATH(''), TYPE)
|
|
.value('.','NVARCHAR(MAX)'),1,2,' ') Foglalkozasok
|
|
FROM #Vegleges veg
|
|
FETCH NEXT FROM kur INTO @HetirendId
|
|
END
|
|
CLOSE kur
|
|
DEALLOCATE kur
|
|
|
|
/*Végleges órarend*/
|
|
INSERT INTO @Orarend
|
|
SELECT @osztalyId
|
|
, OsszesOra.Oraszam
|
|
, OsszesOra.Hetirend
|
|
, OsszesOra.Nap
|
|
, TenylegesOra.Foglalkozas
|
|
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
|
|
pv.*
|
|
, dic.C_NAME Hetirend_dname
|
|
, @osztalyNev
|
|
FROM @Orarend
|
|
PIVOT (MAX(Foglalkozas) FOR Nap in([1408],[1409],[1410],[1411],[1412],[1413]))pv
|
|
INNER JOIN (SELECT DISTINCT ID, C_NAME FROM T_DICTIONARYITEMBASE_OSSZES) dic ON dic.id=Hetirend
|
|
ORDER BY Hetirend, Ora
|
|
|
|
DROP TABLE #Vegleges
|
|
DROP TABLE #TEMPORARENDINAPOK
|
|
|
|
END
|
|
|
|
GO |