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

GO

CREATE VIEW v_UsedServicesByContract(GNum, Guest, DateCredit, Service, Quantity, PrWithoutDisc,
            curKod, SumWithoutDisc, Disc, Discount, PrWithDisc, SumWithDisc, BgSumWithDisc, Dogovor,
            RoomNum, BgPayedServiceWhitDisc, N)
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,
   b.Regnum as N
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,
  sc.CreditNo as N
  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 g.GuestNum<>0

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_UsedServicesByContract','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_UsedServicesByContract','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_UsedServicesByContract','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_UsedServicesByContract','GNum','Начислени услуги по договор','Кл. номер','Начислени услуги по договор','GNum',2,
 GetFreeNInRelations('Начислени услуги по договор', Null, Null, 10),
 'GNum','','',0,0,'','','','SELECT bvrGuest',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_UsedServicesByContract','Guest','Начислени услуги по договор','Гост','Начислени услуги по договор','Guest',2,
 GetFreeNInRelations('Начислени услуги по договор', Null, Null, 10),
 'Guest','','',0,2,'0','','','',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_UsedServicesByContract','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_UsedServicesByContract','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_UsedServicesByContract','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_UsedServicesByContract','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_UsedServicesByContract','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_UsedServicesByContract','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_UsedServicesByContract','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_UsedServicesByContract','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_UsedServicesByContract','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_UsedServicesByContract','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_UsedServicesByContract','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_UsedServicesByContract','BgPayedServiceWhitDisc','Начислени услуги по договор','Платено','Начислени услуги по договор','BgPayedServiceWhitDisc',2,
 GetFreeNInRelations('Начислени услуги по договор', Null, Null, 10),
 'BgPayedServiceWhitDisc','','# ### ##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_UsedServicesByContract','N','Начислени услуги по договор','N','Начислени услуги по договор','N',1,
 GetFreeNInRelations('Начислени услуги по договор', Null, Null, 10),
 'N','','',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_UsedServicesByContract','__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_UsedServicesByContract' 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_UsedServicesByContract' and ColName='__OPTIONS__'),1,1);
end if;