IF (EXISTS (select proc_name from sysprocedure where proc_name='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...';
  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;
  for f1 as curs1 scroll cursor for
    select DateCharge as @DateCharge,
      Sum(AvailRooms-(StayingRooms+ArriveRooms)) as @TotalAv
      //100*Sum(AvailRooms-(StayingRooms+ArriveRooms))/Sum(AvailRooms) as @PercentAv
      from 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.'
end
//[v 00.12.23]
//[v 21.06.2004] Jorko
//[v 26.07.2004] Ivo
//[v 07.02.2006] Jorko

GO

if (Exists (Select Table_name from SysTable where Table_name='v_BusyBookRoomsForData3')) then
  Drop View v_BusyBookRoomsForData3
end if

GO

create view
  "DBA".v_BusyBookRoomsForData3
  as select Room,
    CountPeople=Count(*),
    PriceKind=Max(PriceKind),
    FlagForeign=if Max(Guests.NativeCountry)<>0 and Max(Guests.NativeCountry) is not null then 1 else 0 endif,
    RoomClass=Classes.ShortName,
    ContractNo=max(Guests.Contract),
    ClassID=max(Classes.Class)
    from "DBA".Book,"DBA".Rooms,"DBA".Classes,"dba".Guests
    where Room=RoomNum and RoomCat=Class and Rooms.Status<>1 and Rooms.Status<>3
    and DateRegDt<=bvrData3 and Days(DateRegDt,NumLodgings)>bvrData3
    and Book.Guest=Guests.GuestNum
    group by Room,RoomClass
//[v 00.06.18]
//[v 01.07.18] - Niki - dobaven e dogovor
//[v 01.08.11] - Niki - dobaven e tip staia (ID)