IF OBJECT_ID('sp_GetOrarendiOrakForOraosszevonas') IS NOT NULL BEGIN
  DROP PROCEDURE sp_GetOrarendiOrakForOraosszevonas
END
GO

CREATE PROCEDURE [sp_GetOrarendiOrakForOraosszevonas]
	@IntezmenyId int,
	@TanevId int,
	@Date datetime,
	@TanarId int,
  	@OrarendioraId int
AS
BEGIN
	SET NOCOUNT ON;

  DECLARE
     @HetNapja int
    ,@Hetirend int
    ,@CsengetesiRendOraId int
	,@CsengetesiRend int

  SELECT @HetNapja = ora.C_HETNAPJA
		,@Hetirend = ora.C_HETIREND
		,@CsengetesiRendOraId = ora.C_CSENGETESIRENDORAID
		,@CsengetesiRend = ora.C_CSENGETESIRENDID
  FROM T_ORARENDIORA ora
  WHERE ora.ID = @orarendioraId

  SELECT ora.ID					AS OrarendiOraId
		,tantargy.C_NEV			AS Tantargy
		,osztalycsoport.C_NEV	AS Osztalycsoport
  FROM T_ORARENDIORA ora
  INNER JOIN T_TANTARGY tantargy ON ora.C_TANTARGYID = tantargy.ID
  INNER JOIN T_OSZTALYCSOPORT osztalycsoport ON ora.C_OSZTALYCSOPORTID = osztalycsoport.ID
  WHERE @HetNapja = ora.C_HETNAPJA
   AND (@Hetirend = ora.C_HETIREND OR ora.C_HETIREND = 1554 OR @Hetirend = 1554) --minden héten
   AND @CsengetesiRend = ora.C_CSENGETESIRENDID
   AND @CsengetesiRendOraId = ora.C_CSENGETESIRENDORAID
   AND ora.C_INTEZMENYID = @IntezmenyId
   AND ora.C_TANEVID = @TanevId
   AND ((ora.C_ORAERVENYESSEGKEZDETE <= @Date AND ora.C_ORAERVENYESSEGVEGE > @Date) OR (ora.C_ORAERVENYESSEGKEZDETE = ora.C_ORAERVENYESSEGVEGE AND ora.C_ORAERVENYESSEGKEZDETE = @Date))
   AND ora.C_TANARID = @TanarId
   AND ora.TOROLT = 'F'
   AND ora.ID <> @OrarendioraId
END
GO