if (select count(*) from Registers where RegisterName='RoomStatForPeriod')=0 then
insert into Registers(RegisterName,IntStojnost,Komentar)
               values('RoomStatForPeriod',0,'Определя дали може да се задава статус на стая за период и съобтвтно отразяването му в Перспективна заетост и Планиране по номера стаи')
end if

GO

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

GO

create procedure
DBA.FillHotelAvail(in @FromDate date,in @ToDate date)
begin
  declare @L integer;
  declare @i integer;
  declare @FillDate date;
  //
  message '-- FillHotelAvail...' type info to console;
  delete from HotelCharge where "Session" = bvrConnectID and N = 2; //N=2 za da ne trie dannite ot
  if(exists(select Table_name from SysTable where Table_name = 'v_FreeRoomsForData3First')) then
    drop view DBA.v_FreeRoomsForData3First
  end if;
  //
  if(exists(select Table_name from SysTable where Table_name = 'v_BusyRoomsForData3Second')) then
    drop view DBA.v_BusyRoomsForData3Second
  end if;
  //
  //zapalva s dati i roomtypes HotelCharge:
  //=========================================================================================================================
  create view DBA.v_FreeRoomsForData3First
    as select RoomClass as @RoomClass,SUM(Counter) as @Counter from
      v_FreeRoomsForData3
      group by RoomClass;
  create view DBA.v_BusyRoomsForData3Second
    as select RoomClass as @RoomClass,SUM(Counter) as @Counter1 from
      v_BusyRoomsForData3
      group by RoomClass;
  //=========================================================================================================================
  set @FillDate=@FromDate;
  while @FillDate <= @ToDate loop
    set bvrData3=@FillDate;
    //message '---- Date=',bvrData3;
    for f as curs scroll cursor for
      select a.@RoomClass as RoomC,a.@Counter as Count,b.@Counter1 as Count1 from
        v_FreeRoomsForData3First as a,v_BusyRoomsForData3Second as b where
        a.@RoomClass = b.@RoomClass order by
        a.@RoomClass asc do
      //message '---- ',@FillDate,'  ',@RoomClass,'  ',@Counter;
      insert into HotelCharge( N,DateCharge,RoomType,AvailRooms,StayingRooms,ArriveRooms,"Session") values( 
        2,@FillDate,RoomC,Count,Count1,0,bvrConnectID) ;
      //message '=====================================================222222222222222222222222222222222';
      //message @RoomClass;
      //message @Counter;
      //message 'EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEND222222222222222222222';
      set @L=@L end for;
    set @FillDate=@FillDate+1
  end loop;
  drop view DBA.v_FreeRoomsForData3First;
  drop view DBA.v_BusyRoomsForData3Second;
  update HotelCharge,v_AvailTypeRooms set AvailRooms = CountRooms where HotelCharge.RoomType = v_AvailTypeRooms.ClassName and "Session" = bvrConnectID and N = 2;
-- Kozludjov +  
  if (select IntStojnost from Registers where RegisterName='RoomStatForPeriod')=1 then
-- Kozludjov -
  --Maria +
    for ff as cursff scroll cursor for
      select DateCharge as @DateCharge from HotelCharge where "Session" = bvrConnectID and N = 2 group by DateCharge do
      update HotelCharge,v_AvailTypeRooms set AvailRooms = CountRooms-(select isnull(count(distinct R.RoomNum),0) from
          RoomStatusForPeriod as RSFP,
          Classes as C,Rooms as R where
          @DateCharge >= StartDate and
          @DateCharge <= EndDate and
          NStatus <> 0 and NStatus <> 2 and
          R.RoomCat = C.Class and
          R.RoomNum = RSFP.NRoom and
          C.Class = (select Class from Classes where ShortName = v_AvailTypeRooms.ClassName)) where
        HotelCharge.RoomType = v_AvailTypeRooms.ClassName and "Session" = bvrConnectID and N = 2 and HotelCharge.DateCharge = @DateCharge
      end for;
  --Maria -
  end if;
  for f1 as curs1 scroll cursor for
    select DateCharge as @DateCharge,
      Sum(AvailRooms-(StayingRooms+ArriveRooms)) as @TotalAv from
      //100*Sum(AvailRooms-(StayingRooms+ArriveRooms))/Sum(AvailRooms) as @PercentAv
      HotelCharge where
      "Session" = bvrConnectID and N = 2
      group by DateCharge do
    //
    //Izchisljava total
    insert into HotelCharge( N,DateCharge,RoomType,AvailRooms,"Session") values( 
      2,@DateCharge,' Tотал',@TotalAv,bvrConnectId) ;
    set @L=@L end for;
  //
  //Izchisljava %
  //    insert into HotelCharge(DateCharge,RoomType,AvailRooms,"Session") values(
  //    @DateCharge,' %',@PercentAv,bvrConnectId)
  message '-- FillHotelAvail DONE.' type info to console
end
//[v 00.12.23]
//[v 21.06.2004] Jorko
//[v 26.07.2004] Ivo
//[v 07.02.2006] Jorko
//[v 24.03.2008] Maria - ot broia na svobodnite stai izvajdame tezi, koito v zadadenia period ot @FromDate do @ToDate e sus status, v koito ne e za prodajba
//[v 08.07.2008] Kozludjov - da se syobrazqva s registyr RoomStatForPeriod