﻿SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('[dbo].[sp_GetNemKotottMunkaIdoDateByInsert]') IS NOT NULL 
BEGIN
  DROP PROCEDURE [dbo].[sp_GetNemKotottMunkaIdoDateByInsert] 
END  
GO

CREATE PROCEDURE [sp_GetNemKotottMunkaIdoDateByInsert]
	@IntezmenyId int
	,@TanevId int
	,@IdoszakKezdete datetime
	,@IdoszakVege datetime
	,@KivalaszottDatum datetime
	,@HetirendId int
AS
BEGIN
	SET NOCOUNT ON;
		
		DECLARE @tmp TABLE (Hetirend INT NOT NULL, HetNapja INT NOT NULL, HetSorszama INT NOT NULL);
		INSERT @tmp
			SELECT nn.C_HETIREND, nn.C_HETNAPJA, nn.C_HETSORSZAMA
			FROM T_NAPTARINAP nn
			WHERE
				nn.C_INTEZMENYID = @intezmenyId AND nn.C_TANEVID = @tanevId AND nn.C_NAPDATUMA = @KivalaszottDatum
		
		IF(@hetirendId = 1554)
			BEGIN
					SELECT
						C_NAPDATUMA Datum
					FROM T_NAPTARINAP nn
					WHERE
						nn.C_NAPDATUMA BETWEEN @IdoszakKezdete AND @IdoszakVege AND TOROLT = 'F'
						AND C_ORARENDINAP = 'T' AND C_HETNAPJA = (SELECT HetNapja FROM @tmp)
						AND C_INTEZMENYID = @intezmenyId AND C_TANEVID = @tanevId
				EXCEPT
					SELECT
						C_DATUM Datum
					FROM T_TANEVRENDJE_OSSZES TANEVRENDJE
					WHERE
						(TOROLT = 'F' AND (C_NAPTIPUSA IN (1386,1389,1391,1392,1396,1397,1398,1399,1401) OR C_ORARENDINAP = 'F') AND C_INTEZMENYID = @IntezmenyId AND C_TANEVID=@TanevId)
			END
		ELSE
			BEGIN
					SELECT
						C_NAPDATUMA as Datum
					FROM
						T_NAPTARINAP nn
					WHERE
						nn.C_NAPDATUMA BETWEEN @IdoszakKezdete AND @IdoszakVege AND TOROLT = 'F'
						AND C_ORARENDINAP = 'T' AND C_HETNAPJA = (SELECT HetNapja FROM @tmp)
						AND C_INTEZMENYID = @intezmenyId AND C_TANEVID = @tanevId AND C_HETIREND = @hetirendId
				EXCEPT
					SELECT
						C_DATUM Datum
					FROM T_TANEVRENDJE_OSSZES TANEVRENDJE
					WHERE
						(TOROLT = 'F' AND (C_NAPTIPUSA IN (1386,1389,1391,1392,1396,1397,1398,1399,1401) OR C_ORARENDINAP = 'F') AND C_INTEZMENYID = @IntezmenyId AND C_TANEVID=@TanevId)
			END

END