IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('CheckPeriodInPeriod')))
THEN Drop procedure CheckPeriodInPeriod
END IF

GO

create function DBA.CheckPeriodInPeriod(in @Per1Beg date,in @Per1End date,in @Per2Beg date,in @Per2End date)
returns integer
begin
  declare @res integer;
  if @Per1Beg <= @Per2End and @Per1End > @Per2Beg then
    set @res=1
  else
    set @res=0
  end if;
  return @res
end

GO

IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('RoomBusessForPeriod')))
THEN Drop procedure RoomBusessForPeriod
END IF

GO

create function DBA.RoomBusessForPeriod(in @Room integer,in @FromDate date,in @ToDate date)
returns integer
begin
  declare i integer;
  declare flag integer;
  declare @Stat integer;
  declare @FlagPast integer;
  set flag=0;
  for F as curs dynamic scroll cursor for
    select FromDateDt as @FFDate,FromDateDt+NumDays as @TTDate,ReserveNo as @ResNo from
      ReservePlan where
      RoomNo = @Room do
    select Status into @Stat from Reserve where ReserveNo = @ResNo;
    select count(*) into @FlagPast from Reserve where
      ReserveNo = @ResNo and
      DateReserveDt >= today(*);
    select CheckPeriodInPeriod(@FFDate,@TTDate,@FromDate,@ToDate) into i;
    message i type info to console;
    message @FFDate || '   ' || @TTDate || '   ' || @FromDate || '   ' || @ToDate type info to console;
    if((i > 0) and(@Stat = 1) and(@FlagPast > 0)) then // @Stat=1 Reservaciata ne e otmenena
      set flag=1
    end if end for;
	if flag = 0 then
	  for f1 as curs1 scroll cursor for
	    select DateRegDt as @ArriveDate, NumLodgings as @NumLodgings 
	      from Book where Room = @Room and Staying = 1 and NumLodgings > 0
	  do
	    select CheckPeriodInPeriod(@ArriveDate, Days(@ArriveDate, @NumLodgings),@FromDate,@ToDate) into i;
	    if i > 0 then 
	      set flag = 1;
	    end if;
	  end for;
	end if;
  return(flag)
end
//[v 24.11.2003] Jorko
//[v 31.10.2003] Jorko
//[v 15.03.2005] Jorko seldia dali reservaciata ne e otmenena i dali ne e minala
//[v 23.07.2008] gnikolov - добавено проверка и на заети стаи не по резервация