47 lines
1.5 KiB
Transact-SQL
47 lines
1.5 KiB
Transact-SQL
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
|