Delete from Relations where lcase(RelationTable) = lcase('Free and occupied beds')

Go

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_FreeBeds','__OPTIONS__','Свободни и заети легла','__OPTIONS__','Free and occupied beds','__OPTIONS',1,GetFreeNInRelations('Free and occupied beds', Null, Null, 10),'__OPTIONS__','','',0,0,'','','','',1,'',0,0,1,'29.09.2009')

Go

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_FreeBeds','bvrData2','Свободни и заети легла','Дата','Free and occupied beds','Date',2,GetFreeNInRelations('Free and occupied beds', Null, Null, 10),'bvrData2','','',0,0,1,1,'','select today(*)',100,'',0,0,1,'29.09.2009')

Go

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_FreeBeds','HotelName','Свободни и заети легла','Общежитие','Free and occupied beds','HotelName',0,GetFreeNInRelations('Free and occupied beds', Null, Null, 10),'HotelName','','',0,0,'','','','',100,'',0,0,1,'29.09.2009')

Go

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_FreeBeds','Room','Свободни и заети легла','Стая','Free and occupied beds','Room',2,GetFreeNInRelations('Free and occupied beds', Null, Null, 10),'Room','select RoomName from Rooms','',0,0,'','','','',100,'',0,0,1,'29.09.2009')

Go

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_FreeBeds','RoomClass','Свободни и заети легла','Категория','Free and occupied beds','RoomClass',0,GetFreeNInRelations('Free and occupied beds', Null, Null, 10),'RoomClass','select ShortName from Classes where Valid=1','',0,0,'','','','',100,'',0,0,1,'29.09.2009')

Go

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_FreeBeds','Status','Свободни и заети легла','Статус','Free and occupied beds','Status',0,GetFreeNInRelations('Free and occupied beds', Null, Null, 10),'Status','','',0,0,'','','','',100,'',0,0,1,'29.09.2009')

Go

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_FreeBeds','TotalBeds','Свободни и заети легла','Легла','Free and occupied beds','TotalBeds',2,GetFreeNInRelations('Free and occupied beds', Null, Null, 10),'sum(TotalBeds)','Sum(qrData.Легла)','',0,0,'','','','',100,'',0,0,1,'29.09.2009')

Go

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_FreeBeds','NumPeople','Свободни и заети легла','Студенти','Free and occupied beds','NumPeople',2,GetFreeNInRelations('Free and occupied beds', Null, Null, 10),'sum(NumPeople)','Sum(qrData.Студенти)','',0,0,'','','','',100,'',0,0,1,'29.09.2009')

Go

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_FreeBeds','FreeBeds','Свободни и заети легла','Свободни легла','Free and occupied beds','FreeBeds',2,GetFreeNInRelations('Free and occupied beds', Null, Null, 10),'sum(FreeBeds)','Sum(qrData.Свободни легла)','',0,0,'','','','',100,'',0,0,1,'29.09.2009')

Go

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_FreeBeds','UnUsedBeds','Свободни и заети легла','Неизползваеми','Free and occupied beds','UnUsedBeds',2,GetFreeNInRelations('Free and occupied beds', Null, Null, 10),'SUM(UnUsedBeds)','Sum(qrData.Неизползваеми)','',0,0,'','','','',80,'',0,0,1,'29.09.2009')

Go

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_FreeBeds','PercentUsed','Свободни и заети легла','% заетост','Free and occupied beds','PercentUsed',2,GetFreeNInRelations('Free and occupied beds', Null, Null, 10),'(Avg(NumPeople)+Avg(UnUsedBeds))/Avg(TotalBeds)*100','Sum(qrData.% заетост)','0.00',0,0,'','','','',80,'',0,0,1,'29.09.2009')

Go

delete from InformPermission where RelationN=(
select N from Relations where TableName='v_FreeBeds'and ColName='__OPTIONS__')

Go

f1: for f1 as curs scroll cursor for select grNo=N from AdminGroups do
  if grNo <> 0 then
    insert into InformPermission(N,RelationN,AdminGroupNo,Stoinost)
    values((select MAX(N+1) from InformPermission),(select N from Relations where TableName='v_FreeBeds'and ColName='__OPTIONS__'  ),GrNo,1);
  end if;
end for;


go

Delete from Relations where lcase(RelationTable) = lcase('Free and occupied beds for period')

Go

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('#FreeBedsForPeriod','__OPTIONS__','Свободни и заети легла за период','__OPTIONS__','Free and occupied beds for period','__OPTIONS',1,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'__OPTIONS__','','',0,0,'','','','call FillFreeBedsForPeriod()',1,'',0,0,1,'29.09.2009')

Go

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('#FreeBedsForPeriod','bvrData2','Свободни и заети легла за период','От дата','Free and occupied beds for period','FromDate',2,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'bvrData2','','',0,0,1,1,'','select today(*)',100,'',0,0,1,'29.09.2009')

Go

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('#FreeBedsForPeriod','bvrData1','Свободни и заети легла за период','До дата','Free and occupied beds for period','ToDate',2,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'bvrData1','','',0,0,1,1,'','select today(*)',100,'',0,0,1,'29.09.2009')

Go

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('#FreeBedsForPeriod','CurrentDate','Свободни и заети легла за период','Дата','Free and occupied beds for period','CurrentDate',2,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'CurrentDate','','',0,1,'','','','',100,'',0,0,1,'29.09.2009')

Go

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('#FreeBedsForPeriod','HotelName','Свободни и заети легла за период','Общежитие','Free and occupied beds for period','HotelName',2,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'HotelName','','',0,0,'','','','',100,'',0,0,1,'29.09.2009')

Go

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('#FreeBedsForPeriod','TotalBeds','Свободни и заети легла за период','Легла','Free and occupied beds for period','TotalBeds',2,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'sum(TotalBeds)','Sum(qrData.Легла)','',0,0,'','','','',100,'',0,0,1,'29.09.2009')

Go

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('#FreeBedsForPeriod','Students','Свободни и заети легла за период','Студенти','Free and occupied beds for period','Students',2,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'sum(Students)','Sum(qrData.Студенти)','',0,0,'','','','',100,'',0,0,1,'29.09.2009')

Go

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('#FreeBedsForPeriod','FreeBeds','Свободни и заети легла за период','Свободни легла','Free and occupied beds for period','FreeBeds',2,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'sum(FreeBeds)','Sum(qrData.Свободни легла)','',0,0,'','','','',100,'',0,0,1,'29.09.2009')

Go

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('#FreeBedsForPeriod','Unusable','Свободни и заети легла за период','Неизползваеми','Free and occupied beds for period','Unusable',2,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'Sum(Unusable)','Sum(qrData.Неизползваеми)','',0,0,'','','','',80,'',0,0,1,'29.09.2009')

Go

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('#FreeBedsForPeriod','PercentUsed','Свободни и заети легла за период','% заетост','Free and occupied beds for period','PercentUsed',2,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'Sum(PercentUsed)','(Sum(qrData.Студенти)+Sum(qrData.Неизползваеми))/Sum(qrData.Легла)','0.00',0,0,'','','','',80,'',0,0,1,'29.09.2009')

Go

delete from InformPermission where RelationN=(
select N from Relations where TableName='#FreeBedsForPeriod'and ColName='__OPTIONS__')

Go

f1: for f1 as curs scroll cursor for select grNo=N from AdminGroups do
  if grNo <> 0 then
    insert into InformPermission(N,RelationN,AdminGroupNo,Stoinost)
    values((select MAX(N+1) from InformPermission),(select N from Relations where TableName='#FreeBedsForPeriod'and ColName='__OPTIONS__'  ),GrNo,1);
  end if;
end for;

go

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

GO

create procedure
DBA.FillFreeBedsForPeriod()
begin
  declare @CurrentDate date;
  declare @HotelName varchar(30);
  declare @TotalBeds integer;
  declare @Students integer;
  declare @FreeBeds integer;
  declare @Unusable integer;
  declare @StartDate date;
  declare @PercentUsed numeric (12, 4);
  set bvrData1=bvrData1; --To date
  set bvrData2=bvrData2; --From date
  set @StartDate=bvrData2;
  delete from #FreeBedsForPeriod;
  while(bvrData2 <= bvrData1) loop
    set @CurrentDate=bvrData2;
    set @HotelName=(select distinct HotelName from v_FreeBeds);
    set @TotalBeds=(select sum(TotalBeds) from v_FreeBeds);
    set @Students=(select sum(NumPeople) from v_FreeBeds);
    set @FreeBeds=(select sum(FreeBeds) from v_FreeBeds);
    set @Unusable=(select sum(UnUsedBeds) from v_FreeBeds);
    set @PercentUsed=(select (convert(numeric (12, 4),(Sum(NumPeople)+Sum(UnUsedBeds)))/ Sum(TotalBeds) * 100) from v_FreeBeds);
    insert into #FreeBedsForPeriod( CurrentDate,HotelName,TotalBeds,Students,FreeBeds,Unusable, PercentUsed) values( @CurrentDate,@HotelName,@TotalBeds,@Students,@FreeBEds,@Unusable, @PercentUsed) ;
    set bvrData2=bvrData2+1
  end loop;
  set bvrData2=@StartDate
end

--v1 [29.09.2009] - Milko Dobavena nova kolona PercentUsed 
go

IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_FreeBeds')))
THEN Drop view v_FreeBeds
END IF

GO

create view
  DBA.v_FreeBeds
  as select HotelName=(select H.HotelName from DBA.Hotels as H,DBA.Registers as R where lcase(R.RegisterName) = lcase('hotelid') and Hotel_ID = R.IntStojnost),
    Room=R.RoomName,
    RoomClass=max(CL.ShortName),
    TotalBeds=max(R.NumBeds),
    NumPeople=count(distinct(Guest)),
    Status=(if(max(IsFull) = 1) or(TotalBeds-NumPeople <= 0) then 'Запълнена' else 'Незапълнена' endif),
    UnUsedBeds=(if(max(IsFull) = 1) then TotalBeds-NumPeople else 0 endif),
    FreeBeds=(if((TotalBeds-NumPeople <= 0) or(max(IsFull) = 1)) then 0 else TotalBeds-NumPeople endif),
    PercentUsed=Round((convert(numeric(12,4),(NumPeople+UnusedBeds))/TotalBeds)*100,2)
    from
    DBA.Rooms as R left outer join(dba.Book as B join dba.Guests as G on B.Guest = G.GuestNum) on
    B.NumLodgings <> 0 and R.RoomNum = B.Room and
    (((B.Staying = 1)) or
    ((B.Staying = 2) and(days(B.DateRegDt,B.NumLodgings) <> bvrData2)) or
    ((B.Staying = 3) and(days(B.DateRegDt,B.NumLodgings) <> bvrData2)) or
    ((B.Staying = 3) and(days(B.DateRegDt,B.NumLodgings) = bvrData2) and(B.RegNum = (select Max(B2.RegNum) from dba.Book as B2 where B2.Guest = G.GuestNum)))) and
    B.DateRegDt <= bvrData2 and days(B.DateRegDt,B.NumLodgings) >= bvrData2,
    DBA.Classes as CL where
    R.RoomCat = CL.Class and
    R.Status <> 3 and
    R.Status <> 10
    group by R.RoomName order by
    Room asc

	-- v1[29.09.2009] - Milko - Dobavena kolona procentna zaetost