101 lines
No EOL
3.2 KiB
Transact-SQL
101 lines
No EOL
3.2 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS uspGetNemkotottMunkaidoUtkozes
|
|
GO
|
|
|
|
CREATE PROCEDURE uspGetNemkotottMunkaidoUtkozes
|
|
@pIntezmenyId int
|
|
,@pTanevId int
|
|
,@pTanarId int
|
|
,@pDatum datetime
|
|
,@pKezdet datetime
|
|
,@pVeg datetime
|
|
,@pMindenHetHetirendId int
|
|
,@pHetirendId int = null
|
|
,@pNemKotottMunkaIdoGroupId uniqueidentifier = null
|
|
AS BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE
|
|
@Hetnapja int = (SELECT C_HETNAPJA FROM T_NAPTARINAP WHERE C_TANEVID = @pTanevId AND C_NAPDATUMA = @pDatum AND (@pHetirendId IS NULL OR C_HETIREND = @pHetirendId OR @pHetirendId = @pMindenHetHetirendId))
|
|
,@pKezdetTime time = cast(@pKezdet AS time)
|
|
,@pVegTime time = cast(@pVeg AS time)
|
|
,@pKezdetDate datetime = cast(@pKezdet AS date)
|
|
,@pVegDate datetime = cast(@pVeg AS date)
|
|
,@pVegDate1 datetime = cast(DATEADD(day,1,@pVeg) AS date)
|
|
|
|
IF EXISTS
|
|
(
|
|
SELECT TOP 1 1 FROM T_NEMKOTOTTMUNKAIDO nkm
|
|
INNER JOIN T_NAPTARINAP nn ON CAST(nn.C_NAPDATUMA AS date) = CAST(nkm.C_KEZDETE AS date)
|
|
AND nn.C_HETNAPJA = @Hetnapja
|
|
AND nn.C_TANEVID = @pTanevId
|
|
AND (nn.C_HETIREND = nkm.C_HETIREND OR nkm.C_HETIREND = @pMindenHetHetirendId)
|
|
WHERE nkm.C_TANARID = @pTanarId
|
|
AND @pKezdetTime < cast(nkm.C_VEGE AS time)
|
|
AND @pVegTime > cast(nkm.C_KEZDETE AS time)
|
|
AND cast(nkm.C_KEZDETE AS date) BETWEEN @pKezdetDate AND @pVegDate
|
|
AND nkm.TOROLT = 'F'
|
|
AND (nkm.C_ADMINALTALTOROLT = 'F' OR (nkm.C_ADMINALTALTOROLT = 'T' AND nkm.C_MEGTARTOTT = 'T'))
|
|
AND nkm.C_TANEVID = @pTanevId
|
|
AND (@pHetirendId IS NULL OR (nkm.C_HETIREND = @pHetirendId OR nkm.C_HETIREND = @pMindenHetHetirendId))
|
|
AND (@pNemKotottMunkaIdoGroupId IS NULL OR nkm.C_GROUPID <> @pNemKotottMunkaIdoGroupId)
|
|
)
|
|
BEGIN
|
|
SELECT 1 AS HasUtkozes
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
DROP TABLE IF EXISTS #OrarendTable
|
|
CREATE TABLE #OrarendTable
|
|
(
|
|
Hetirend int
|
|
,HetNapja int
|
|
,OraKezdete datetime
|
|
,OraVege datetime
|
|
,Megtartott char(1)
|
|
,OraTipus nvarchar(200)
|
|
,HelyettesitoTanarID int
|
|
)
|
|
|
|
INSERT INTO #OrarendTable
|
|
(
|
|
Hetirend
|
|
,HetNapja
|
|
,OraKezdete
|
|
,OraVege
|
|
,Megtartott
|
|
,OraTipus
|
|
,HelyettesitoTanarID
|
|
)
|
|
EXEC sp_GetOrarend
|
|
@pIntezmenyId = @pIntezmenyId
|
|
,@pTanevId = @pTanevId
|
|
,@pIdoszakKezdete = @pKezdetDate
|
|
,@pIdoszakVege = @pVegDate1
|
|
,@pTanarId = @pTanarId
|
|
,@pIsHelyettesitesNelkul = 0
|
|
,@pOsztalyCsoportId = NULL
|
|
,@pTanuloId = NULL
|
|
,@pTantargyId = NULL
|
|
,@pTeremId = NULL
|
|
,@pCsakOrarendiOrak = 0
|
|
,@pIsNapirend = NULL
|
|
,@pOszlopok = N'Hetirend,HetNapja,OraKezdete,OraVege,Megtartott,OraTipus,HelyettesitoTanarID'
|
|
|
|
IF EXISTS
|
|
(
|
|
SELECT TOP 1 1 FROM #OrarendTable AS sgo
|
|
WHERE sgo.HetNapja = @Hetnapja
|
|
AND @pKezdetTime < cast(sgo.OraVege AS time) AND @pVegTime > cast(sgo.OraKezdete AS time)
|
|
AND (@pHetirendId IS NULL OR sgo.Hetirend = @pHetirendId OR sgo.Hetirend = @pMindenHetHetirendId)
|
|
AND (sgo.HelyettesitoTanarID IS NULL OR sgo.HelyettesitoTanarID = @pTanarId)
|
|
AND (sgo.OraTipus = 'OrarendiOra' OR sgo.Megtartott = 'T')
|
|
)
|
|
BEGIN
|
|
SELECT 1 AS HasUtkozes
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT 0 AS HasUtkozes
|
|
END
|
|
END
|
|
END |