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;
--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 -
  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