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

GO

CREATE VIEW "DBA"."v_RegBaseServicesPeriodForOazis"  as select
    RegDate=DateRegDt,
    GuestName=Guests."Name",
    Account=BK.Guest,
    RoomNo=BK.Room,
    Service='Нощувка и застраховка',
    ServicesNum=NumLodgings,
    ServiceSngPrice=Currencies.ExchangeRate*BK.Price,
    AllSum=BK.NumLodgings*ServiceSngPrice,
    Dogovor=Contracts.ShortName,
    Plateni=ServiceSngPrice*BK.PayedLodgings,
    DateEnd=Days(DateRegDt,NumLodgings)
    from "DBA".Book as BK,"dba".Guests,"DBA".Currencies,"DBA".Contracts
    where Guests.GuestNum=BK.Guest and Guests.Contract=Contracts.ContractNo
    and BK.KodCurrency=Currencies.KodCurrency
    and BK.ContractPays=0   and ( (RegDate>=bvrData1) and (DateEnd<=bvrData2))

Union

select
    RegDate=DateRegDt,
    GuestName=Guests."Name",
    Account=BK.Guest,
    RoomNo=BK.Room,
    Service='Нощувка и застраховка',
    ServicesNum=NumLodgings,
    ServiceSngPrice=Currencies.ExchangeRate*BK.Price,
    AllSum=BK.NumLodgings*CalcPriceWithDiscount(scr.Discount,ServiceSngPrice),
    Dogovor=Contracts.ShortName,
    Plateni=BK.PayedLodgings*CalcPriceWithDiscount(scr.Discount,ServiceSngPrice),
    DateEnd=Days(DateRegDt,NumLodgings)
    from "DBA".Book as BK,"dba".Guests,"DBA".Currencies,"DBA".Contracts,"dba".ServicesForContract as scr
    where Guests.GuestNum=BK.Guest and Guests.Contract=Contracts.ContractNo
    and BK.KodCurrency=Currencies.KodCurrency
    and BK.ContractPays<>0  and BK.ContractPays=scr.ContractNo
    and scr.ServiceNo in(-1,-2) and ( (RegDate>=bvrData1) and (DateEnd<=bvrData2))


GO

Delete from Relations where lcase(RelationTable) = lcase('Guestmovement')

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_RegBaseServicesPeriodForOazis','bvrData1','Движение на гост','От дата','Guestmovement','From Date',2,
 GetFreeNInRelations('Guestmovement', Null, Null, 10),
 'bvrData1','','',0,0,'1','1','','select bvrData1',72,'',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_RegBaseServicesPeriodForOazis','bvrData2','Движение на гост','До дата','Guestmovement','To Date',2,
 GetFreeNInRelations('Guestmovement', Null, Null, 10),
 'bvrData2','','',0,0,'1','1','','select bvrData2',72,'',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_RegBaseServicesPeriodForOazis','RegDate','Движение на гост','Дата','Guestmovement','Date',2,
 GetFreeNInRelations('Guestmovement', Null, Null, 10),
 'RegDate','','',0,1,'','','','',72,'',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_RegBaseServicesPeriodForOazis','DateEnd','Движение на гост','Дата на напускане','Guestmovement','DateEnd',2,
 GetFreeNInRelations('Guestmovement', Null, Null, 10),
 'DateEnd','','',0,1,'','','','',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_RegBaseServicesPeriodForOazis','Dogovor','Движение на гост','Договор','Guestmovement','Contract',2,
 GetFreeNInRelations('Guestmovement', Null, Null, 10),
 'Dogovor','','',0,0,'','','SELECT ShortName FROM Contracts','',104,'',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_RegBaseServicesPeriodForOazis','Account','Движение на гост','Кл.номер','Guestmovement','Client Number',2,
 GetFreeNInRelations('Guestmovement', Null, Null, 10),
 'Account','','',0,1,'','','','',60,'',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_RegBaseServicesPeriodForOazis','GuestName','Движение на гост','Име гост','Guestmovement','Guest Name',2,
 GetFreeNInRelations('Guestmovement', Null, Null, 10),
 'GuestName','','',0,0,'','','SELECT "Name" FROM Guests GROUP BY "Name"','',150,'',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_RegBaseServicesPeriodForOazis','RoomNo','Движение на гост','Стая No','Guestmovement','Room #',2,
 GetFreeNInRelations('Guestmovement', Null, Null, 10),
 'RoomNo','','',0,0,'','','select RoomNum from Rooms','',48,'',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_RegBaseServicesPeriodForOazis','Service','Движение на гост','Услуга','Guestmovement','Service',2,
 GetFreeNInRelations('Guestmovement', Null, Null, 10),
 'Service','','',0,0,'','','select ServiceName from Services union (select ''Нощувка и застраховка'')','',168,'',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_RegBaseServicesPeriodForOazis','ServicesNum','Движение на гост','Кол.','Guestmovement','Quant.',2,
 GetFreeNInRelations('Guestmovement', Null, Null, 10),
 'ServicesNum','','',1,0,'','','','',56,'',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_RegBaseServicesPeriodForOazis','ServiceSngPrice','Движение на гост','Ед.цена','Guestmovement','Sng. Price',2,
 GetFreeNInRelations('Guestmovement', Null, Null, 10),
 'ServiceSngPrice','','# ### ##0.00',1,0,'','','','',72,'',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_RegBaseServicesPeriodForOazis','AllSum','Движение на гост','Сума','Guestmovement','Total',2,
 GetFreeNInRelations('Guestmovement', Null, Null, 10),
 'AllSum','','# ### ##0.00',1,0,'','','','',72,'',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_RegBaseServicesPeriodForOazis','Plateni','Движение на гост','Платено','Guestmovement','PayedTotal',2,
 GetFreeNInRelations('Guestmovement', Null, Null, 10),
 'Plateni','','# ### ##0.00',1,0,'','','','',72,'',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_RegBaseServicesPeriodForOazis','__OPTIONS__','Движение на гост','__OPTIONS__','Guestmovement','__OPTIONS__',1,
GetFreeNInRelations('Guestmovement', Null, Null, 10),
 '__OPTIONS__','','',0,0,'','','','',NULL,'',0,0,1,today())


GO

if (select count(*) from InformPermission where RelationN=(
      select N from Relations where TableName='v_RegBaseServicesPeriodForOazis' and ColName='__OPTIONS__'))=0 then

       insert into InformPermission(N,RelationN,AdminGroupNo,Stoinost)
         values((select MAX(N+1) from InformPermission),(select N from Relations where TableName='v_RegBaseServicesPeriodForOazis' and ColName='__OPTIONS__'),1,1);
end if;