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


GO



CREATE VIEW "DBA"."v_UsedServicesByContract"  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,
(if b.PayedLodgings=0 then 0 else ((b.PayedLodgings*PrWithDisc)*GetCurrencyRate(DateCredit,cur.KodCurrency)) endif) 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 
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,
(if sc.PayedServices=0 then 0 else ((sc.PayedServices*PrWithDisc)*GetCurrencyRate(DateCredit,cur.KodCurrency)) endif) 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


GO

if (select count(*) from Relations where TableName='v_UsedServicesByContract' and ColName='BgPayedServiceWhitDisc')=0 then

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_UsedServicesByContract','BgPayedServiceWhitDisc','Начислени услуги по договор',
 'Платени','Начислени услуги по договор','BgPayedServiceWhitDisc',2,
  GetFreeNInRelations('Начислени услуги по договор', Null, Null, 10),
 'BgPayedServiceWhitDisc','','',0,0,'','','','',100,'',0,0,1,today())

end if