93 lines
3.6 KiB
Transact-SQL
93 lines
3.6 KiB
Transact-SQL
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
IF OBJECT_ID('dbo.sp_GetDokumentumTanitasiNapokSorszama') IS NOT NULL
|
|
BEGIN
|
|
DROP PROCEDURE dbo.sp_GetDokumentumTanitasiNapokSorszama
|
|
END
|
|
GO
|
|
|
|
CREATE PROCEDURE dbo.sp_GetDokumentumTanitasiNapokSorszama
|
|
@tanevId INT,
|
|
@osztalyId INT
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
--Tanítás nélküli hétköznapok
|
|
DECLARE @TanitasNelkuliNapokTanevRendjeTable TABLE (Datum DATE)
|
|
INSERT INTO @TanitasNelkuliNapokTanevRendjeTable
|
|
SELECT
|
|
TanevRendje.C_DATUM
|
|
FROM T_TANEVRENDJE_OSSZES TanevRendje
|
|
INNER JOIN T_NAPTARINAP_OSSZES NaptariNap ON NaptariNap.C_NAPDATUMA = TanevRendje.C_DATUM AND NaptariNap.C_TANEVID = @tanevId AND NaptariNap.TOROLT = 'F'
|
|
WHERE
|
|
TanevRendje.C_TANEVID = @tanevId
|
|
AND TanevRendje.TOROLT = 'F'
|
|
AND TanevRendje.C_OSSZESCSOPORTRAVONATKOZIK = 'T'
|
|
AND TanevRendje.C_NAPTIPUSA NOT IN (1384, 1385, 1389, 1393, 1394, 1395, 1400, 1401, 1402, 1403, 1404, 1649)
|
|
AND (NaptariNap.C_ALAPHETNAPJA IS NULL OR NaptariNap.C_ALAPHETNAPJA BETWEEN 1408 AND 1412)
|
|
|
|
UNION
|
|
|
|
SELECT
|
|
TanevRendje.C_DATUM
|
|
FROM T_TANEVRENDJE_OSSZES TanevRendje
|
|
INNER JOIN T_NAPTARINAP_OSSZES NaptariNap ON NaptariNap.C_NAPDATUMA = TanevRendje.C_DATUM AND NaptariNap.C_TANEVID = @tanevId AND NaptariNap.TOROLT = 'F'
|
|
INNER JOIN T_OSZTALYCSOPORT_TANEVRENDJE OsztalyCsoportTanevRendje ON OsztalyCsoportTanevRendje.C_TANEVRENDJEID = TanevRendje.ID
|
|
WHERE
|
|
TanevRendje.C_TANEVID = @tanevId
|
|
AND TanevRendje.TOROLT = 'F'
|
|
AND TanevRendje.C_OSSZESCSOPORTRAVONATKOZIK = 'F'
|
|
AND TanevRendje.C_NAPTIPUSA NOT IN (1384, 1385, 1389, 1393, 1394, 1395, 1400, 1401, 1402, 1403, 1404, 1649)
|
|
AND OsztalyCsoportTanevRendje.C_OSZTALYCSOPORTID IN (SELECT ID FROM fnGetDokumentumKapcsolodoOsztalycsoportok(@osztalyId, @tanevId, DEFAULT))
|
|
AND (NaptariNap.C_ALAPHETNAPJA IS NULL OR NaptariNap.C_ALAPHETNAPJA BETWEEN 1408 AND 1412)
|
|
|
|
--Hétvégi tanítási napok
|
|
DECLARE @HetvegiTanitasiNapokTanevRendjeTable TABLE (Datum DATE)
|
|
INSERT INTO @HetvegiTanitasiNapokTanevRendjeTable
|
|
SELECT
|
|
TanevRendje.C_DATUM
|
|
FROM T_TANEVRENDJE_OSSZES TanevRendje
|
|
INNER JOIN T_NAPTARINAP_OSSZES NaptariNap ON NaptariNap.C_NAPDATUMA = TanevRendje.C_DATUM AND NaptariNap.C_TANEVID = @tanevId AND NaptariNap.TOROLT = 'F'
|
|
WHERE
|
|
TanevRendje.c_tanevid = @tanevId
|
|
AND TanevRendje.TOROLT = 'F'
|
|
AND TanevRendje.C_OSSZESCSOPORTRAVONATKOZIK = 'T'
|
|
AND TanevRendje.C_NAPTIPUSA IN (1384, 1385, 1389, 1393, 1394, 1395, 1400, 1401, 1402, 1403, 1404, 1649)
|
|
AND (NaptariNap.C_ALAPHETNAPJA IS NULL OR NaptariNap.C_ALAPHETNAPJA IN (1413, 1414))
|
|
|
|
UNION
|
|
|
|
SELECT
|
|
TanevRendje.C_DATUM
|
|
FROM T_TANEVRENDJE_OSSZES TanevRendje
|
|
INNER JOIN T_NAPTARINAP_OSSZES NaptariNap ON NaptariNap.C_NAPDATUMA = TanevRendje.C_DATUM AND NaptariNap.C_TANEVID = @tanevId AND NaptariNap.TOROLT = 'F'
|
|
INNER JOIN T_OSZTALYCSOPORT_TANEVRENDJE OsztalyCsoportTanevRendje ON OsztalyCsoportTanevRendje.C_TANEVRENDJEID = TanevRendje.ID
|
|
WHERE
|
|
TanevRendje.C_TANEVID = @tanevId
|
|
AND TanevRendje.TOROLT = 'F'
|
|
AND TanevRendje.C_OSSZESCSOPORTRAVONATKOZIK = 'F'
|
|
AND TanevRendje.C_NAPTIPUSA IN (1384, 1385, 1389, 1393, 1394, 1395, 1400, 1401, 1402, 1403, 1404, 1649)
|
|
AND OsztalyCsoportTanevRendje.C_OSZTALYCSOPORTID IN (SELECT ID FROM fnGetDokumentumKapcsolodoOsztalycsoportok(@osztalyId, @tanevId, DEFAULT))
|
|
AND (NaptariNap.C_ALAPHETNAPJA IS NULL OR NaptariNap.C_ALAPHETNAPJA IN (1413, 1414))
|
|
|
|
--Result
|
|
SELECT
|
|
C_NAPDATUMA
|
|
FROM T_NAPTARINAP_OSSZES NaptariNap
|
|
WHERE
|
|
NaptariNap.TOROLT = 'F'
|
|
AND C_TANEVID = @tanevId
|
|
AND C_NAPDATUMA NOT IN (SELECT Datum FROM @TanitasNelkuliNapokTanevRendjeTable)
|
|
AND C_ALAPHETNAPJA BETWEEN 1408 AND 1412
|
|
|
|
UNION
|
|
|
|
SELECT
|
|
Datum
|
|
FROM @HetvegiTanitasiNapokTanevRendjeTable
|
|
|
|
END |