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