IF (select count(*) from RoomStatus where RoomStatusNo=10)=0 then
 insert into RoomStatus(RoomStatusNo,Description,Color)
 values(10,'Частна',22222)
end if

GO

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('BusyRoomsForTipAndDate'))) THEN
Drop function BusyRoomsForTipAndDate
END IF

GO

create function 
DBA.BusyRoomsForTipAndDate(in NomType integer,in cdate date)
returns integer
begin
  declare mydate date;
  declare rrnum integer;
  declare rrbusy integer;
  declare a integer;
  declare broi integer;
  declare ss char(100);
  declare @broi integer;
  set broi=0;
  for f as curs scroll cursor for
    select r.RoomNum as @rnum from
      rooms as r,classes as c where
      r.RoomCat = c.class and
      r.status <> 1 and
      r.status <> 3 and
      r.status <> 10 and
      c.class = NomType do
    select count(*) into rrnum
      from book where
      (room = @rnum) and(cdate >= DateRegDt) and
      (cdate <= days("date"(DateRegDt),NumLodgings-1));
    if rrnum <> 0 then
      set broi=broi+1
    end if end for;
  return broi
end

GO

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('CalcFreeRoomsByDateAndType'))) THEN
Drop function CalcFreeRoomsByDateAndType
END IF

GO

create function 
DBA.CalcFreeRoomsByDateAndType(in @Clas integer,in @CDate date)
returns integer
begin
  declare @Free integer;
  declare @Nast integer;
  declare @ResPlan integer;
  declare @ss varchar(50);
  set @Free=0;
  for F as curs dynamic scroll cursor for
    select R.RoomNum as @RoomNo from
      Rooms as R where
      R.status <> 1 and R.status <> 3 and  R.status <> 10 and
      R.RoomCat = @Clas do
    set @ss=@RoomNo;
    select count(RP.RoomNo) into @ResPlan
      from Reserve as R,ReservePlan as RP where
      RP.ReserveNo = R.ReserveNo and
      RP.FromDateDt <= @CDate and
      days(RP.FromDateDt,RP.NumDays) > @CDate and
      RP.FromDateDt >= today(*) and
      RP.RoomClass = @Clas and
      R.Status = 1 and
      RP.RoomNo = @RoomNo;
    set @ss=@ss || '--' || @ResPlan;
    select count(*) into @Nast from Book where
      Room = @RoomNo and
      DateRegDt <= @CDate and
      days(DateRegDt,NumLodgings) > @CDate and
      Staying = 1;
    set @ss=@ss || '--' || @Nast;
    if((@ResPlan = 0) and(@Nast = 0)) then
      set @Free=@Free+1
    end if;
    message @ss type info to console end for;
  return(@Free)
end

GO

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('GetNumBusyRoomsByPerAndRType'))) THEN
Drop function GetNumBusyRoomsByPerAndRType
END IF

GO

create function 
DBA.GetNumBusyRoomsByPerAndRType(in @data1 date,in @data2 date,in @rtype integer,in @All integer)
returns integer
begin
  declare @num integer;
  set @num=0;
  if(@all = 0) then
    for f0 as curs0 scroll cursor for
      select(select Count(RoomNum) from Rooms where RoomNum = b.Room) from
        Rooms as r,Book as b where
        b.DateRegDt between bvrdata1 and bvrData2 and
        b.Room = r.RoomNum and
        r.RoomCat = @rtype and
        r.Status <> 1 and r.Status <> 3 and r.Status <> 10
        group by r.RoomCat,b.Room do
      set @num=@num+1 end for
  else
    set @num=0;
    for f1 as curs1 scroll cursor for
      select(select Count(RoomNum) from Rooms where RoomNum = b.Room) from
        Rooms as r,Book as b where
        b.DateRegDt between bvrdata1 and bvrData2 and
        b.Room = r.RoomNum and
        r.Status <> 1 and r.Status <> 3 and r.Status <> 10
        group by r.RoomCat,b.Room do
      set @num=@num+1 end for
  end if;
  return(@num)
end

GO

IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('v_AvailTypeRooms'))) THEN
Drop View v_AvailTypeRooms
END IF

GO

create view DBA.v_AvailTypeRooms
  as select Classes.ShortName as ClassName,
    Count(*) as CountRooms from
    dba.Rooms,dba.classes where Status <> 1 and Status <> 3 and Status <> 10 and
    Rooms.RoomCat = Classes.Class
    group by Classes.ShortName
    
 
GO

IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('v_Blocking'))) THEN
Drop View v_Blocking
END IF

GO

create view DBA.v_Blocking
  as select Room=RoomNum,
    Class=Max(CL.ShortName),
    /* 'Svobodna' znachi svobodna ot vchera, a ne za dnes:*/
    Status=(if count(Book.RegNum) = 0 then '  Свободна' else 'Напуска' endif) from
    DBA.Rooms as R left outer join dba.Book on Book.Staying = 1 and R.RoomNum = Book.Room and Days(Book.DateRegDt,Book.NumLodgings) = current date,DBA.Classes as CL where
    R.RoomCat = CL.Class and R.Status <> 1 and R.Status <> 3 and R.Status <> 10
    group by RoomNum
    
    
GO

IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('v_BusyRoomsByPeriod'))) THEN
Drop View v_BusyRoomsByPeriod
END IF

GO

create view 
  DBA.v_BusyRoomsByPeriod as
  select RoomTypes=ShortName,BusyNum=DBA.BusyRoomsForDate(bvrData1,bvrData2,cl.class),
    AvailForOneDay=(select count(*) from dba.rooms as r,dba.classes as c where r.RoomCat = c.class and r.status <> 1 and r.status <> 3 and r.status <> 10 and c.class = cl.class)*numdays,
    numdays=(bvrData2-bvrData1)+1,
    AvailForPeriod=(select count(*) from dba.rooms as r,dba.classes as c where r.RoomCat = c.class and r.status <> 1 and r.status <> 3 and r.status <> 10 and c.class = cl.class)*numdays-BusyNum,
    Persent=if AvailForOneDay = 0 then
      if BusyNum = 0 then
        0
      else
        100
      endif
    else
      ((100*BusyNum)/AvailForOneDay)
    endif from
    dba.classes as cl where
    valid <> 0 union
  select RoomTypes='Тотал',BusyNum=sum(DBA.BusyRoomsForDate(bvrData1,bvrData2,cl.class)),
    AvailForOneDay=(select count(*) from dba.rooms as r,dba.classes as c where r.RoomCat = c.class and r.status <> 1 and r.status <> 3 and r.status <> 10)*numdays,
    numdays=(bvrData2-bvrData1)+1,
    AvailForPeriod=(select count(*) from dba.rooms as r,dba.classes as c where r.RoomCat = c.class and r.status <> 1 and r.status <> 3 and r.status <> 10)*numdays-BusyNum,
    Persent=((100*BusyNum)/AvailForOneDay) from
    dba.classes as cl where
    valid <> 0
    
    
GO

IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('v_FreeRooms'))) THEN
Drop View v_FreeRooms
END IF

GO

create view 
  DBA.v_FreeRooms
  as select Room=Rooms.RoomNum,
    Class=Max(CL.ShortName),
    Status=(if count(Book.RegNum) = 0 then '  Свободна' else
      if min(Book.DateRegDt+Book.NumLodgings) = bvrData2 then 'Напуска' else ' Заета' endif
    endif),
    ReserveName=max(Reserve.Name) from
    DBA.Rooms left outer join dba.Book on Book.NumLodgings <> 0 and
    Rooms.RoomNum = Book.Room and
    Days(Book.DateRegDt,Book.NumLodgings) >= bvrData2 and
    Book.DateRegDt <= bvrData2 left outer join
    DBA.ReservePlan on Rooms.RoomNum = ReservePlan.RoomNo and
    ReservePlan.FromDateDt = bvrData2 left outer join
    DBA.Reserve on ReservePlan.ReserveNo = Reserve.ReserveNo,
    DBA.Classes as CL,
    DBA.Reserve where
    Rooms.RoomCat = CL.Class and
    Rooms.Status <> 1 and
    Rooms.Status <> 3 and
    Rooms.Status <> 10
    group by Rooms.RoomNum
    
    
GO

IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('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 Rooms.Status <> 10 and
    DateRegDt <= bvrData3 and Days(DateRegDt,NumLodgings) > bvrData3 and
    Book.Guest = Guests.GuestNum
    group by Room,RoomClass
    
GO

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('FillPercentZaetost'))) THEN
Drop procedure FillPercentZaetost
END IF

GO

create procedure DBA.FillPercentZaetost(in @FromDate date,in @ToDate date)
//Popalva #PercentZaetost
begin
  declare @FDate date;
  declare @BusyBeds integer;
  declare @Res decimal(12,2);
  //
  set @FDate=@FromDate;
  while @ToDate >= @FDate loop
    for f as curs scroll cursor for
      select Rooms.RoomCat as @Class,
        sum(Rooms.NumBeds) as @NumBeds from
        Rooms where
        Rooms.Status <> 1 and
        Rooms.Status <> 3 and
        Rooms.Status <> 10
        group by Rooms.RoomCat do
      select count(RegNum) into @BusyBeds
        from Book,Rooms where
        DateRegDt <= @FDate and
        Days(DateRegDt,NumLodgings) > @FDate and
        Book.Room = Rooms.RoomNum and
        Rooms.RoomCat = @Class and
        Book.IsRest <> 1;
      if(@BusyBeds = 0) or(@NumBeds = 0) then
        set @Res=0
      else
        set @Res=round(100*@BusyBeds/@NumBeds,0)
      end if;
      insert into #PercentZaetost( FDate,ClassNo,BusyBeds,Percent) values( 
        @FDate,@Class,@BusyBeds,string(@Res,'%')) end for;
    set @FDate=Days(@FDate,1)
  end loop
end
//[v 01.06.21]
//[v 02.05.31] Dobaveno e dopylnitelna proverka koqto izkliu`va noshtuvkite
//[v 06.04.05] Safa, Sankt Peterburg - Fix v round(100*@BusyBeds/@NumBeds,0)


GO

if (select count(*) from sys.syscolumns where tname='DateForecast' and cname='OwnedRooms')=0
  then
    alter table DateForecast add OwnedRooms integer default 0
end if;

GO

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('FillBedStat'))) THEN
Drop function FillBedStat
END IF

GO


create procedure DBA.FillBedStat()
//Popalva DateForecast.RoomCount,CrashedRooms,ServiceRooms sus kapaciteta na hotela po legla, avariijnite legla i netyrgowskite legla
begin
  declare @NumBeds integer;
  declare @AvNum integer;
  declare @SrvNum integer;
  declare @OwnedRooms integer;
  select sum(NumBeds) into @NumBeds from Rooms where Status <> 1 and Status <> 3 and Status <> 10;
  select sum(NumBeds) into @AvNum from Rooms where Status = 1;
  select sum(NumBeds) into @SrvNum from Rooms where Status = 3;
  select sum(NumBeds) into @OwnedRooms from Rooms where Status = 10;
  if @NumBeds is null then set @NumBeds=0
  end if;
  if @AvNum is null then set @AvNum=0
  end if;
  if @SrvNum is null then set @SrvNum=0
  end if;
  if @OwnedRooms is null then set @OwnedRooms=0
  end if;
  update DateForecast set
    RoomCount = @NumBeds,
    CrashedRooms = @AvNum,
    ServiceRooms = @SrvNum,
    OwnedRooms=@OwnedRooms
    where
    "Session" = bvrConnectID
end

GO

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('FillRoomStat'))) THEN
Drop function FillRoomStat
END IF

GO

create procedure DBA.FillRoomStat()
//Popalva DateForecast.RoomCount,CrashedRooms,ServiceRooms
begin
  declare @RNum integer;
  declare @AvNum integer;
  declare @SrvNum integer;
  declare @OwnedRooms integer;
  select count(*) into @RNum from Rooms where Status <> 1 and Status <> 3 and Status <> 10;
  select count(*) into @AvNum from Rooms where Status = 1;
  select count(*) into @SrvNum from Rooms where Status = 3;
  select count(*) into @OwnedRooms from Rooms where Status = 10;
  if @RNum is null then set @RNum=0
  end if;
  if @AvNum is null then set @AvNum=0
  end if;
  if @SrvNum is null then set @SrvNum=0
  end if;
  if @OwnedRooms is null then set @OwnedRooms=0
  end if;
  update DateForecast set
    RoomCount = @RNum,
    CrashedRooms = @AvNum,
    ServiceRooms = @SrvNum,
    OwnedRooms=@OwnedRooms
     where
    "Session" = bvrConnectID
end

GO

IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('v_DateForecast'))) THEN
Drop View v_DateForecast
END IF


GO

create view DBA.v_DateForecast
  as select DateForecast.FDate,
    DateForecast.RoomCount,
    DateForecast.BusyRooms,
    DateForecast.LeaveTodayRooms,
    DateForecast.StayingTodayRooms,
    DateForecast.ArrivingTodayRooms,
    DateForecast.BusyRoomsFIT,
    DateForecast.CrashedRooms,
    DateForecast.ServiceRooms,
    DateForecast.OwnedRooms,
    DateForecast.Elderly,
    DateForecast.Children,
    DateForecast.ArrivingElderly,
    DateForecast.ArrivingChildren,
    DateForecast.Breakfasts,
    DateForecast.Lunches,DateForecast.Dinners,
    DateForecast.HalfBoards,
    BusyTonightRooms=DateForecast.BusyTodayRooms,
    ExpFreeRooms=DateForecast.RoomCount-DateForecast.BusyTodayRooms,
    PercBusyRooms=100*BusyTonightRooms/DateForecast.RoomCount,
    TotElderly=DateForecast.Elderly+DateForecast.ArrivingElderly,
    TotChildren=DateForecast.Children+DateForecast.ArrivingChildren from
    DBA.DateForecast where
    DateForecast."Session" = bvrConnectID

GO

if (select count(*) from Relations where TableName='v_DateForecast' and ColName='OwnedRooms')=0 then
 INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
 VALUES('v_DateForecast','OwnedRooms','Дневни прогнози','Частни','Day Forcast','OwnedRooms',2,11325,'Sum(OwnedRooms)','Sum(qrdata.Частни)','',1,0,'','','','',72,'',0,0,1,'20060612')
end if;

GO

IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('v_PercentZaetostBeds'))) THEN
Drop View v_PercentZaetostBeds
END IF

GO

create view DBA.v_PercentZaetostBeds
  as select DateForecast.FDate,
    DateForecast.RoomCount,
    DateForecast.CrashedRooms,
    DateForecast.ServiceRooms,
    DateForecast.OwnedRooms,
    DateForecast.Elderly+DateForecast.Children as Noshtuvki,
    (Noshtuvki*100/DateForecast.RoomCount) as PercentZaetost from
    DBA.DateForecast where
    DateForecast."Session" = bvrConnectID

GO


IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('v_TenDaysForecast'))) THEN
Drop View v_TenDaysForecast
END IF

GO

create view DBA.v_TenDaysForecast
  as select AllRooms=Count(Rooms.RoomNum),
    BsyLstNgtRooms=Sum(if days(Book.DateRegdt,Book.NumLodgings) = bvrdata2 then 1 else 0 endif),
    StayingGuests=Sum(if Book.DateRegdt < bvrData2 and days(Book.DateRegdt,Book.NumLodgings) > bvrdata2 then 1 else 0 endif),
    ArrivingGuests=Sum(if Reserve.DateReserveDt = bvrData2 and Guests.Status = 0 then 1 else 0 endif) from
    DBA.Rooms,DBA.Book,DBA.Guests,DBA.Reserve where
    Rooms.Status <> 1 and Rooms.Status <> 10 and Book.Guest = Guests.GuestNum and Guests.Reservation = Reserve.ReserveNo