116 lines
No EOL
3.5 KiB
Transact-SQL
116 lines
No EOL
3.5 KiB
Transact-SQL
-- =============================================
|
|
-- Author: Zsiga Attila
|
|
-- Create date: 2016.10.21.
|
|
-- Description: Tanév rendje esemény alapján melyik órarendi órák érintettek.
|
|
-- =============================================
|
|
DROP PROCEDURE IF EXISTS sp_GetTanevrendjeErintettOrarendiOrai
|
|
GO
|
|
|
|
CREATE PROCEDURE sp_GetTanevrendjeErintettOrarendiOrai
|
|
@pTanevId INT,
|
|
@pNapTipusa INT,
|
|
@pKezdete DATETIME,
|
|
@pVege DATETIME
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
--TEMP TABLES
|
|
SELECT * INTO #TEMPNAPTARINAP FROM T_NAPTARINAP
|
|
WHERE TOROLT = 'F' AND C_TANEVID = @pTanevId
|
|
---------------
|
|
SELECT * INTO #TEMPFELHASZNALO FROM T_FELHASZNALO
|
|
WHERE TOROLT = 'F' AND C_TANEVID = @pTanevId
|
|
---------------
|
|
SELECT * INTO #TEMPORARENDIORA FROM T_ORARENDIORA
|
|
WHERE TOROLT = 'F' AND C_TANEVID = @pTanevId
|
|
---------------
|
|
SELECT * INTO #TEMPTANEVRENDJE FROM T_TANEVRENDJE
|
|
WHERE TOROLT = 'F' AND C_TANEVID = @pTanevId
|
|
---------------
|
|
SELECT * INTO #TEMPOSZTALYCSOPORT_TANEVRENDJE FROM T_OSZTALYCSOPORT_TANEVRENDJE
|
|
LEFT JOIN #TEMPTANEVRENDJE ON #TEMPTANEVRENDJE.ID = T_OSZTALYCSOPORT_TANEVRENDJE.C_TANEVRENDJEID
|
|
WHERE TOROLT = 'F' AND C_TANEVID = @pTanevId
|
|
---------------
|
|
SELECT * INTO #TEMPOSZTALYCSOPORT FROM T_OSZTALYCSOPORT
|
|
WHERE TOROLT = 'F' AND C_TANEVID = @pTanevId
|
|
---------------
|
|
SELECT
|
|
*
|
|
INTO
|
|
#TEMPTANEVRENDJEGLOBAL
|
|
FROM
|
|
#TEMPTANEVRENDJE
|
|
WHERE
|
|
C_OSSZESCSOPORTRAVONATKOZIK = 'T'
|
|
AND C_DATUM >= @pKezdete
|
|
AND C_DATUM <= @pVege
|
|
AND C_NAPTIPUSA = @pNapTipusa
|
|
---------------
|
|
SELECT
|
|
#TEMPTANEVRENDJE.*,
|
|
#TEMPOSZTALYCSOPORT_TANEVRENDJE.C_OSZTALYCSOPORTID
|
|
INTO
|
|
#TEMPTANEVRENDJEOSZTALYOKRA
|
|
FROM
|
|
#TEMPOSZTALYCSOPORT_TANEVRENDJE
|
|
LEFT JOIN #TEMPOSZTALYCSOPORT ON #TEMPOSZTALYCSOPORT.ID = #TEMPOSZTALYCSOPORT_TANEVRENDJE.C_OSZTALYCSOPORTID
|
|
LEFT JOIN #TEMPTANEVRENDJE ON #TEMPTANEVRENDJE.ID = #TEMPOSZTALYCSOPORT_TANEVRENDJE.C_TANEVRENDJEID
|
|
WHERE
|
|
#TEMPTANEVRENDJE.C_NAPTIPUSA = @pNapTipusa
|
|
AND #TEMPTANEVRENDJE.C_DATUM >= @pKezdete
|
|
AND #TEMPTANEVRENDJE.C_DATUM <= @pVege
|
|
---------------
|
|
SELECT
|
|
ID
|
|
INTO
|
|
#TEMPERINTETTOSZTALYOK
|
|
FROM
|
|
#TEMPOSZTALYCSOPORT
|
|
WHERE
|
|
(
|
|
(#TEMPOSZTALYCSOPORT.ID IN (SELECT #TEMPTANEVRENDJEOSZTALYOKRA.C_OSZTALYCSOPORTID FROM #TEMPTANEVRENDJEOSZTALYOKRA)) OR ((SELECT COUNT (*) FROM #TEMPTANEVRENDJEGLOBAL) > 0)
|
|
)
|
|
---------------
|
|
SELECT
|
|
*
|
|
INTO
|
|
#TEMPERINTETTNAPTARINAPOK
|
|
FROM
|
|
#TEMPNAPTARINAP
|
|
WHERE
|
|
C_NAPDATUMA >= @pKezdete
|
|
AND C_NAPDATUMA <= @pVege
|
|
---------------
|
|
SELECT
|
|
*
|
|
INTO
|
|
#TEMPERINTETTORARENDIORAK
|
|
FROM
|
|
#TEMPORARENDIORA
|
|
WHERE
|
|
C_ORAERVENYESSEGKEZDETE <= @pKezdete
|
|
AND C_ORAERVENYESSEGVEGE >= @pVege
|
|
AND ((C_HETIREND = 1554) OR (1154 IN (SELECT C_HETIREND FROM #TEMPERINTETTNAPTARINAPOK)) OR (C_HETIREND IN (SELECT C_HETIREND FROM #TEMPERINTETTNAPTARINAPOK)))
|
|
AND C_HETNAPJA IN (SELECT C_HETNAPJA FROM #TEMPERINTETTNAPTARINAPOK)
|
|
AND C_OSZTALYCSOPORTID IN (SELECT ID FROM #TEMPERINTETTOSZTALYOK)
|
|
---------------
|
|
|
|
SELECT
|
|
ID AS OrarendiOraId
|
|
FROM
|
|
#TEMPERINTETTORARENDIORAK
|
|
|
|
DROP TABLE #TEMPNAPTARINAP
|
|
DROP TABLE #TEMPFELHASZNALO
|
|
DROP TABLE #TEMPORARENDIORA
|
|
DROP TABLE #TEMPTANEVRENDJE
|
|
DROP TABLE #TEMPOSZTALYCSOPORT_TANEVRENDJE
|
|
DROP TABLE #TEMPOSZTALYCSOPORT
|
|
DROP TABLE #TEMPTANEVRENDJEGLOBAL
|
|
DROP TABLE #TEMPTANEVRENDJEOSZTALYOKRA
|
|
DROP TABLE #TEMPERINTETTOSZTALYOK
|
|
DROP TABLE #TEMPERINTETTNAPTARINAPOK
|
|
DROP TABLE #TEMPERINTETTORARENDIORAK
|
|
END
|
|
GO |