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

Go

Create procedure
  FillHotelChargeKontingent()
begin
  declare @FillDate date;
  declare @FromDate date;
  declare @ToDate date;
  declare @L integer;
  declare @UpdateRowN integer;
  //
  delete from HotelCharge where "Session" = bvrConnectID and N = 3;
  //zapalva s dati i roomtypes HotelCharge
  set @FromDate='1.' || bvrMesec || '.' || bvrYear;
  set @ToDate='28.' || bvrMesec || '.' || bvrYear;
  if bvrMesec in( 1,3,5,7,8,10,12) then
    set @ToDate='31.' || bvrMesec || '.' || bvrYear
  end if;
  if bvrMesec in( 4,6,9,11) then
    set @ToDate='30.' || bvrMesec || '.' || bvrYear
  end if;
  if bvrMesec = 2 then
    if(mod(bvrYear,4) = 0) and(mod(bvrYear,100) <> 0) then
      set @ToDate='29.' || bvrMesec || '.' || bvrYear
    end if
  end if;
  //
  set @FillDate=@FromDate;
  while @FillDate <= @ToDate loop
    set bvrData3=@FillDate;
   //-- message '---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Date=',bvrData3;
    
    for f1 as curs1 scroll cursor for
      select
        C.ContractNo as @ContractNo,
        C.ClassNo as @ClassNo,
        Cl.ShortName as @ShortName
      from
        Contingent as C,
        Classes as Cl
      where
        C.ClassNo = Cl.Class
    do
      insert into HotelCharge(N,DateCharge,RoomType,AvailRooms,StayingRooms,ArriveRooms,ReservedRooms,
                              ContractNo,"Session",RoomNo,DummyCol)
                       values(3,@FillDate,@ShortName,0,0,0,0,@ContractNo,bvrConnectID,@ClassNo,0)
    end for;

    for f2 as curs2 scroll cursor for
      select
        RoomClass as @RoomClass,
        ContractNo as @ContractNo,
        count(Room) as @Counter,
        max(ClassID) as @ClassID
      from
        v_BusyBookRoomsForData3
      group by
        RoomClass,
        ContractNo
      order by
        RoomClass asc
    do
      --message '---- ',@RoomClass,'  ',@Counter type info to console;
      insert into HotelCharge(N,DateCharge,RoomType,AvailRooms,StayingRooms,ArriveRooms,ReservedRooms,
                              ContractNo,"Session",RoomNo,DummyCol)
                       values(3,@FillDate,@RoomClass,0,@Counter,0,0,@ContractNo,bvrConnectID,@ClassID,1) ;
      set @L=@L
    end for;
    
    -- резервирани стаи
    for f3 as curs3 scroll cursor for
      select
        RP.RoomClass as @ClassID,
        C.ContractNo as @ContractNo,
        CL.ShortName as @RoomClass,
        count(*) as @Counter2
      from
        Reserve as R,
        ReservePlan as RP,
        Contracts as C,
        Classes as CL
      where
        R.ReserveNo = RP.ReserveNo
        and C.ContractNo = R.ContractNo
        and RP.RoomClass = CL.Class
        and C.GroupNo = 0
        and C.Active = 1
        and RP.Status = 1
        and RP.FromDateDt <= @FillDate
        and @FillDate <= RP.FromDateDt+RP.NumDays
      group by
        C.ContractNo,
        RP.RoomClass,
        CL.ShortName
      order by
        RoomClass asc
    do
      //message '>>>>>>>>>>>>>>>>>>>>>>>>>  @FillDate='||@FillDate;
      if (select count(*) from HotelCharge where DateCharge=@FillDate and RoomNo=@ClassID
                                                 and ContractNo = @ContractNo
                                                 and "Session"=bvrConnectID and DummyCol=1)=0 then
      insert into HotelCharge(N,DateCharge,RoomType,AvailRooms,StayingRooms,ArriveRooms,ReservedRooms,
                              ContractNo,"Session",RoomNo,DummyCol)
                       values(3,@FillDate,@RoomClass,0,0,0,@Counter2,@ContractNo,bvrConnectID,@ClassID,1) ;
      else
          update HotelCharge set ReservedRooms = @Counter2 where DateCharge=@FillDate and RoomNo=@ClassID
                 and ContractNo = @ContractNo and "Session"=bvrConnectID and DummyCol=1;
      end if;
    end for;
    
    set @FillDate=@FillDate+1
  end loop
end