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

GO

create view v_RoomsFromRoomStatusForPeriod as
select RoomNum=R.RoomNum,
Class=C.ShortName,
StartDate=RSFP.StartDate,
EndDate=dateadd(day,1,RSFP.EndDate),
Status=RS.Description,
Komentar=RSFP.Komentar
from Rooms R, RoomStatusForPeriod RSFP, Classes C, RoomStatus RS
where R.RoomCat=C.Class and 
      RS.RoomStatusNo=R.Status and
      R.RoomNum=RSFP.NRoom and RSFP.NStatus <> 0 and
      bvrData1 <= RSFP.EndDate and bvrData1 >= RSFP.StartDate

GO

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

GO

create view
  DBA.v_RoomStatusForPeriod as
  select RoomNum=R.RoomNum,
    Class=C.ShortName,
    StartDate=RSFP.StartDate,
    EndDate=dateadd(day,1,RSFP.EndDate),
    Status=RS.Description,
    Komentar=RSFP.Komentar from
    DBA.Rooms as R,DBA.RoomStatusForPeriod as RSFP,DBA.Classes as C,DBA.RoomStatus as RS where
    R.RoomCat = C.Class and
    RS.RoomStatusNo = RSFP.NStatus and
    R.RoomNum = RSFP.NRoom and RSFP.NStatus <> 0 and
    bvrData1 <= RSFP.EndDate and bvrData1 >= RSFP.StartDate union all
  select RoomNum=R.RoomNum,
    Class=C.ShortName,
    StartDate=null,
    EndDate=null,
    Status=RS.Description,Komentar='' from
    DBA.Rooms as R,DBA.RoomStatus as RS,DBA.Classes as C where
    R.Status = RS.RoomStatusNo and R.RoomCat = C.Class and
    (not RoomNum = any(select RoomNum from DBA.v_RoomsFromRoomStatusForPeriod))
    group by RoomNum,Class,Status,StartDate,EndDate,Komentar order by
    RoomNum asc

GO

Delete from Relations where lcase(TableName) = lcase('v_RoomStatusForPeriod')

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_RoomStatusForPeriod','__OPTIONS__','Статус на стаи за период','__OPTIONS__','Статус на стаи за период','__OPTIONS__',1,(select max(N)+5 from Relations),'__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_RoomStatusForPeriod','bvrData1','Статус на стаи за период','Дата','Статус на стаи за период','bvrData1',2,(select max(N)+5 from Relations),'bvrData1','','',1,0,1,1,'','select bvrdata1',100,'',0,0,'',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_RoomStatusForPeriod','RoomNum','Статус на стаи за период','Стая','Статус на стаи за период','RoomNum',2,(select max(N)+5 from Relations),'RoomNum','','',0,1,'','','','',60,'',0,0,'',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_RoomStatusForPeriod','Class','Статус на стаи за период','Тип стая','Статус на стаи за период','Class',2,(select max(N)+5 from Relations),'Class','','',0,0,'','','','',70,'',0,0,'',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_RoomStatusForPeriod','StartDate','Статус на стаи за период','Начална дата','Статус на стаи за период','StartDate',2,(select max(N)+5 from Relations),'StartDate','','',0,2,'','','','',100,'',0,0,'',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_RoomStatusForPeriod','EndDate','Статус на стаи за период','Крайна дата','Статус на стаи за период','EndDate',2,(select max(N)+5 from Relations),'EndDate','','',0,3,'','','','',100,'',0,0,'',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_RoomStatusForPeriod','Status','Статус на стаи за период','Статус','Статус на стаи за период','Status',2,(select max(N)+5 from Relations),'Status','','',0,0,'','','','',80,'',0,0,'',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_RoomStatusForPeriod','Komentar','Статус на стаи за период','Коментар','Статус на стаи за период','Komentar',2,(select max(N)+5 from Relations),'Komentar','','',0,0,'','','','',250,'',0,0,'',today())

Go

delete from InformPermission where RelationN=(
select N from Relations where TableName='v_RoomStatusForPeriod'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_RoomStatusForPeriod'and ColName='__OPTIONS__'  ),GrNo,1);
  end if;
end for;