57 lines
3.4 KiB
Transact-SQL
57 lines
3.4 KiB
Transact-SQL
IF OBJECT_ID('[dbo].[sp_CheckTanarOra]') IS NOT NULL BEGIN
|
|
DROP PROCEDURE [dbo].[sp_CheckTanarOra]
|
|
END
|
|
GO
|
|
|
|
CREATE PROCEDURE [dbo].[sp_CheckTanarOra]
|
|
@tanarId int,
|
|
@start datetime,
|
|
@end datetime,
|
|
@IntezmenyId int,
|
|
@TanevId int
|
|
AS
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
|
|
declare @startDate datetime;
|
|
set @startDate = CAST(@start AS DATE)
|
|
|
|
declare @csengetesiRendId int;
|
|
set @csengetesiRendId = (select C_AKTIVCSENGETESIRENDID from T_NAPTARINAP nn where nn.C_NAPDATUMA = @startDate and nn.C_INTEZMENYID = @IntezmenyId and nn.C_TANEVID = @TanevId and nn.TOROLT = 'F')
|
|
|
|
declare @hetNapId int;
|
|
set @hetNapId = (select C_HETNAPJA from T_NAPTARINAP nn where nn.C_NAPDATUMA = @startDate and nn.C_INTEZMENYID = @IntezmenyId and nn.C_TANEVID = @TanevId and nn.TOROLT = 'F')
|
|
|
|
declare @hetiRendId int;
|
|
set @hetiRendId = (select C_HETIREND from T_NAPTARINAP nn where nn.C_NAPDATUMA = @startDate and nn.C_INTEZMENYID = @IntezmenyId and nn.C_TANEVID = @TanevId and nn.TOROLT = 'F')
|
|
|
|
if(@csengetesiRendId is null) set @csengetesiRendId = (select ID from T_CSENGETESIREND where C_INTEZMENYID = @IntezmenyId and C_TANEVID = @TanevId and C_AKTIV = 'T' and TOROLT = 'F')
|
|
|
|
declare @csengetesiRendOraId int;
|
|
set @csengetesiRendOraId = (select ID from T_CSENGETESIRENDORA where C_CSENGETESIRENDID = @csengetesiRendId and CAST(C_KEZDETE as TIME) = CAST(@start AS Time) and CAST(C_VEGE as TIME) = CAST(@end AS Time) and C_INTEZMENYID = @IntezmenyId and C_TANEVID = @TanevId and TOROLT = 'F')
|
|
|
|
if ((select count(*) from T_ORARENDIORA where C_TANARID = @tanarId and C_CSENGETESIRENDORAID = @csengetesiRendOraId and C_HETNAPJA = @hetNapId and C_HETIREND = @hetiRendId and C_INTEZMENYID = @IntezmenyId and C_TANEVID = @TanevId and TOROLT = 'F' and CAST(@start as date) >= C_ORAERVENYESSEGKEZDETE and CAST(@start as date) <= C_ORAERVENYESSEGVEGE and CAST(@end as date) >= C_ORAERVENYESSEGKEZDETE and CAST(@end as date) <= C_ORAERVENYESSEGVEGE) = 1)
|
|
begin
|
|
declare @orarendiOraId int;
|
|
set @orarendiOraId = (select ID from T_ORARENDIORA where C_TANARID = @tanarId and C_CSENGETESIRENDORAID = @csengetesiRendOraId and C_HETNAPJA = @hetNapId and C_HETIREND = @hetiRendId and C_INTEZMENYID = @IntezmenyId and C_TANEVID = @TanevId and TOROLT = 'F' and CAST(@start as date) >= C_ORAERVENYESSEGKEZDETE and CAST(@start as date) <= C_ORAERVENYESSEGVEGE and CAST(@end as date) >= C_ORAERVENYESSEGKEZDETE and CAST(@end as date) <= C_ORAERVENYESSEGVEGE)
|
|
|
|
declare @orarendiOraGroupId int;
|
|
set @orarendiOraGroupId = (select C_ORARENDIORAGROUPID from T_ORARENDIORA where ID = @orarendiOraId)
|
|
|
|
declare @tanitasiOraId int;
|
|
set @tanitasiOraId = (select ID from T_TANITASIORA where C_ORARENDIORAGROUPID = @orarendiOraGroupId and C_ORAKEZDETE = @start and C_ORAVEGE = @end and C_INTEZMENYID = @IntezmenyId and C_TANEVID = @TanevId and TOROLT = 'F')
|
|
|
|
if(@tanitasiOraId is null)
|
|
select C_FOGLALKOZASID, C_TEREMID, C_TANTARGYID, C_OSZTALYCSOPORTID, ID as C_ORARENDIORAID from T_ORARENDIORA where Id = @orarendiOraId and C_INTEZMENYID = @IntezmenyId and C_TANEVID = @TanevId and TOROLT = 'F'
|
|
else
|
|
select C_FOGLALKOZASID, C_TEREMID, C_TANTARGYID, C_OSZTALYCSOPORTID, ID as C_ORARENDIORAID from T_ORARENDIORA where Id = -1
|
|
end
|
|
else
|
|
begin
|
|
select C_FOGLALKOZASID, C_TEREMID, C_TANTARGYID, C_OSZTALYCSOPORTID, ID as C_ORARENDIORAID from T_ORARENDIORA where Id = -1
|
|
end
|
|
|
|
END
|