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 Hotels H, Registers R where lcase(R.RegisterName)=lcase('hotelid') and Hotel_ID=R.IntStojnost),
    Room=Rooms.RoomName,
    RoomClass=Max(CL.ShortName),
    Status=(if max(IsFull)=0 then 'Незапълнена' else 'Запълнена' endif),
	TotalBeds=max(Rooms.NumBeds),
	NumPeople=count(Book.RegNum),
	FreeBeds=(if TotalBeds-NumPeople < 0 then 0 else TotalBeds-NumPeople endif)
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,
    DBA.Classes as CL,
where
    Rooms.RoomCat = CL.Class and
    Rooms.Status <> 1 and
    Rooms.Status <> 3 and
    Rooms.Status <> 10
group by Rooms.RoomName 
order by Room asc

go

delete from Relations where lcase(TableName) = lcase('v_FreeBeds')

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,today(*))

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',2,GetFreeNInRelations('Free and occupied beds', Null, Null, 10),'HotelName','','',0,0,'','','','',100,'',0,0,1,today(*))

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,today(*))

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',2,GetFreeNInRelations('Free and occupied beds', Null, Null, 10),'RoomClass','select ShortName from Classes where Valid=1','',0,0,'','','','',100,'',0,0,1,today(*))

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',2,GetFreeNInRelations('Free and occupied beds', Null, Null, 10),'Status','','',0,0,'','','','',100,'',0,0,1,today(*))

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,today(*))

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,today(*))

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,today(*))

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