IF (Exists(select Table_name from SysTable where LCase(Table_name)=LCase('v_NonPayedServicesByContract'))) THEN
  Drop view v_NonPayedServicesByContract
END IF
GO

CREATE VIEW v_NonPayedServicesByContract(GNum, Guest, DateCredit, Service, Quantity, PrWithoutDisc,
            curKod, SumWithoutDisc, Disc, Discount, PrWithDisc, SumWithDisc, BgSumWithDisc, Dogovor,
            RoomNum, BgNonPayedServiceWithDisc)
AS SELECT distinct
   g.GuestNum as GNum,
g."Name" as Guest,
b.DateRegDt as DateCredit,
(if b.IsRest=0 then 'Нощувка в стая '+cast(b.Room as varchar) else 'Почивка в стая '+cast(b.Room as varchar) endif) as Service,
b.NumLodgings as Quantity,
b.Price as PrWithoutDisc,
cur.Symbol as curKod,
Quantity*PrWithoutDisc as SumWithoutDisc,
scr.Discount as Disc,
scr.Discount as Discount,
CalcPriceWithDiscount(Discount,PrWithoutDisc) as PrWithDisc,
Quantity*PrWithDisc as SumWithDisc,
SumWithDisc*GetCurrencyRate(DateCredit,cur.KodCurrency) as BGSumWithDisc,
c.FullName as Dogovor,
b.Room as RoomNum,
SumWithDisc-(b.PayedLodgings*PrWithDisc)*GetCurrencyRate(DateCredit,cur.KodCurrency) as BgPayedServiceWhitDisc
from "dba".Book as b,"dba".Guests as g,"dba".Contracts as c,"dba".Currencies as cur,"dba".ServicesForContract as scr
where b.ContractPays=c.ContractNo
and b.DateRegDt between bvrData1 and bvrData2
and b.Guest=g.GuestNum
and b.ContractPays=scr.ContractNo
and scr.ServiceNo in(-1,-2)
and b.KodCurrency=cur.KodCurrency
and c.ContractNo<>0
and c.GroupNo=2
/* Contracts.GroupNo=2 (група Собственици) по искане на Кети */
/* Внимание ! Не всички бази имат тази група или е възможно номера на групата да е различен !*/

UNION ALL

select distinct g.GuestNum as GNum,
g."Name" as Guest,
sc.DateCreditDt as DateCredit,
s.ServiceName,
(select Sum(NumberServices) from "dba".ServiceCredits
where Service=s.ServiceNo and GuestNo=g.GuestNum
and KodCurrency=cur.KodCurrency
and CreditNo=sc.CreditNo) as Quantity,
sc.SumCredit as PrWithoutDisc,
cur.Symbol as curKod,
(Quantity*PrWithoutDisc) as SumWithoutDisc,
(select Discount from "dba".ServicesForContract where ServiceNo=s.ServiceNo and ContractNo=c.ContractNo) as Disc,
Discount=if Disc is null then 0 else Disc endif,
CalcPriceWithDiscount(Discount,PrWithoutDisc) as PrWithDisc,
Quantity*PrWithDisc as SumWithDisc,
SumWithDisc*GetCurrencyRate(DateCredit,cur.KodCurrency) as BGSumWithDisc,
c.FullName as Dogovor,
sc.RoomNum,
SumWithDisc-(sc.PayedServices*PrWithDisc)*GetCurrencyRate(DateCredit,cur.KodCurrency) as  BgPayedServiceWhitDisc
from "dba".ServiceCredits as sc,"dba".Contracts as c,"dba".Guests as g,"dba".Services as s,"dba".Currencies as cur
where sc.ContractPays=c.ContractNo
and sc.DateCreditDt between bvrData1 and bvrData2
and sc.GuestNo=g.GuestNum
and sc.ContractPays<>0
and s.ServiceNo=sc.Service
and sc.KodCurrency=cur.KodCurrency
and c.GroupNo=2
/* Contracts.GroupNo=2 - виж по-горе */

ORDER By Guest

GO

Delete from Relations where lcase(RelationTable) = lcase('Начислени услуги по собственици')

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_NonPayedServicesByContract','bvrData1','Начислени услуги по собственици','От дата','Начислени услуги по собственици','bvrData1',2,
 GetFreeNInRelations('Начислени услуги по собственици', Null, Null, 10),
 'bvrData1','','',1,0,'1','1','','select bvrdata1',80,'',0,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_NonPayedServicesByContract','bvrData2','Начислени услуги по собственици','До дата','Начислени услуги по собственици','bvrData1',2,
 GetFreeNInRelations('Начислени услуги по собственици', Null, Null, 10),
 'bvrData2','','',1,0,'1','1','','select bvrdata2',80,'',0,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_NonPayedServicesByContract','DateCredit','Начислени услуги по собственици','Дата','Начислени услуги по собственици','DateCredit',2,
 GetFreeNInRelations('Начислени услуги по собственици', Null, Null, 10),
 'DateCredit','','',0,0,'','','','',80,'',0,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_NonPayedServicesByContract','GNum','Начислени услуги по собственици','Кл. номер','Начислени услуги по собственици','GNum',2,
 GetFreeNInRelations('Начислени услуги по собственици', Null, Null, 10),
 'GNum','','',0,0,'','','','',80,'',0,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_NonPayedServicesByContract','Guest','Начислени услуги по собственици','Гост','Начислени услуги по собственици','Guest',2,
 GetFreeNInRelations('Начислени услуги по собственици', Null, Null, 10),
 'Guest','','',0,2,'','','','',150,'',0,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_NonPayedServicesByContract','Service','Начислени услуги по собственици','Услуга','Начислени услуги по собственици','Service',2,
 GetFreeNInRelations('Начислени услуги по собственици', Null, Null, 10),
 'Service','','',0,0,'','','','',200,'',0,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_NonPayedServicesByContract','Quantity','Начислени услуги по собственици','Кол.','Начислени услуги по собственици','Quantity',2,
 GetFreeNInRelations('Начислени услуги по собственици', Null, Null, 10),
 'PrWithoutDisc','','',2,0,'','','','',40,'',0,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_NonPayedServicesByContract','PrWithoutDisc','Начислени услуги по собственици','Ед. цена без отст.','Начислени услуги по собственици','PrWithoutDisc',2,
 GetFreeNInRelations('Начислени услуги по собственици', Null, Null, 10),
 'Quantity','','# ### ##0.00',2,0,'','','','',110,'',0,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_NonPayedServicesByContract','curKod','Начислени услуги по собственици','Код валута','Начислени услуги по собственици','curKod',2,
 GetFreeNInRelations('Начислени услуги по собственици', Null, Null, 10),
 'curKod','','',0,0,'','','','',50,'',0,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_NonPayedServicesByContract','SumWithoutDisc','Начислени услуги по собственици','Сума без отст.','Начислени услуги по собственици','SumWithoutDisc',2,
 GetFreeNInRelations('Начислени услуги по собственици', Null, Null, 10),
 'SumWithoutDisc','','# ### ##0.00',2,0,'','','','',90,'',0,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_NonPayedServicesByContract','Discount','Начислени услуги по собственици','Отстъпка','Начислени услуги по собственици','Discount',2,
 GetFreeNInRelations('Начислени услуги по собственици', Null, Null, 10),
 'Discount','','0%',2,0,'','','','',70,'',0,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_NonPayedServicesByContract','PrWithDisc','Начислени услуги по собственици','Цена с отст.','Начислени услуги по собственици','PrWithDisc',2,
 GetFreeNInRelations('Начислени услуги по собственици', Null, Null, 10),
 'PrWithDisc','','# ### ##0.00',2,0,'','','','',80,'',0,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_NonPayedServicesByContract','SumWithDisc','Начислени услуги по собственици','Сума с отст.','Начислени услуги по собственици','SumWithDisc',2,
 GetFreeNInRelations('Начислени услуги по собственици', Null, Null, 10),
 'SumWithDisc','','# ### ##0.00',2,0,'','','','',80,'',0,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_NonPayedServicesByContract','BGSumWithDisc','Начислени услуги по собственици','Сума лв.','Начислени услуги по собственици','BGSumWithDisc',2,
 GetFreeNInRelations('Начислени услуги по собственици', Null, Null, 10),
 'SUM(BGSumWithDisc)','SUM(qrData.Сума лв.)','# ### ##0.00',2,0,'','','','',80,'',0,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_NonPayedServicesByContract','Dogovor','Начислени услуги по собственици','Договор','Начислени услуги по собственици','Dogovor',2,
 GetFreeNInRelations('Начислени услуги по собственици', Null, Null, 10),
 'Dogovor','','',0,0,'','','','',120,'',0,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_NonPayedServicesByContract','RoomNum','Начислени услуги по собственици','Стая No','Начислени услуги по собственици','RoomNum',2,
 GetFreeNInRelations('Начислени услуги по собственици', Null, Null, 10),
 'RoomNum','','',0,0,'','','','',80,'',0,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_NonPayedServicesByContract','BgNonPayedServiceWithDisc','Начислени услуги по собственици','Дължима сума','Начислени услуги по собственици','BgNonPayedServiceWithDisc',2,
 GetFreeNInRelations('Начислени услуги по собственици', Null, Null, 10),
 'BgNonPayedServiceWithDisc','','# ### ##0.00',0,0,'','','','',100,'',0,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_NonPayedServicesByContract','__OPTIONS__','Начислени услуги по собственици','__OPTIONS__','Начислени услуги по собственици','__OPTIONS__',1,
GetFreeNInRelations('Начислени услуги по собственици', 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_NonPayedServicesByContract' 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_NonPayedServicesByContract' and ColName='__OPTIONS__'),1,1);
end if;