if (Exists (Select Table_name from SysTable where Table_name='v_SpaProdajbiPoUslugi')) then
  Drop View v_SpaProdajbiPoUslugi
end if

GO

CREATE VIEW "DBA"."v_SpaProdajbiPoUslugi"  as
  select
    SmetkaNo=ART.InvoiceNo,
    FakturaNo=if((INV.TrueInvoiceNo<>0) and(INV.TrueInvoiceNo<1000000000)) then INV.MaskaFaktura||substr('0000000000',1,9-length(INV.TrueInvoiceNo))||INV.TrueInvoiceNo endif,
    DokData=INV.InvoiceDateDt,
    Service=Services.ServiceName,
    Kol=if INV.CancelInvoiceNo>0 then(-1)*ART.Qty else ART.Qty endif,
    EdCena=round(abs(ART.Price)*(1+bvrDDSPart*(if ART.TipDDS=2 then 1 else 0 endif)),4),
    Stoinost=round(Kol*EdCena,4),
    StoinostBDDS=round(Kol*abs(ART.Price),4),
    Discount=ART.Discount,
    EdCenaWithDisc=round(CalcPriceWithDiscount(Discount,EdCena),4),
    StoinostWithDisc=round(Kol*EdCenaWithDisc,4),
    Klient=INV.Whom,
    GuestType='Гост от Хотел',
    Operator=ADMIN.ShortName,
    RoomNo=(if(G.GuestNum<>0) then convert(varchar,(select distinct LastRoom from "dba".v_ArriveStayLeaveGuestBook where GuestNum=G.GuestNum)) else null endif)
    from "DBA".SpaArticles as ART,"dba".Services,"DBA".SpaInvoices as INV,"DBA".ServiceCredits as SC,"DBA".Guests as G
    ,"dba".Admin as ADMIN
    where ART.InvoiceNo=INV.InvoiceNo
    and ART.CreditNo=SC.CreditNo
    and ART.CreditNo<>0
    and INV.KodAdmin=ADMIN.AdminNo
    and INV.InvoiceDateDt>=bvrData1
    and INV.InvoiceDateDt<=bvrData2
    and ART.ServiceNo=Services.ServiceNo
    and SC.SpaGuestTypeFlag=0
    and G.GuestNum=SC.WhoPays

 UNION

  select
    SmetkaNo=ART.InvoiceNo,
    FakturaNo=if((INV.TrueInvoiceNo<>0) and(INV.TrueInvoiceNo<1000000000)) then INV.MaskaFaktura||substr('0000000000',1,9-length(INV.TrueInvoiceNo))||INV.TrueInvoiceNo endif,
    DokData=INV.InvoiceDateDt,
    Service=Services.ServiceName,
    Kol=if INV.CancelInvoiceNo>0 then(-1)*ART.Qty else ART.Qty endif,
    EdCena=round(abs(ART.Price)*(1+bvrDDSPart*(if ART.TipDDS=2 then 1 else 0 endif)),4),
    Stoinost=round(Kol*EdCena,4),
    StoinostBDDS=round(Kol*abs(ART.Price),4),
    Discount=ART.Discount,
    EdCenaWithDisc=round(CalcPriceWithDiscount(Discount,EdCena),4),
    StoinostWithDisc=round(Kol*EdCenaWithDisc,4),
    Klient=INV.Whom,
    GuestType='СПА Гост',
    Operator=ADMIN.ShortName,
    RoomNo=-1
    from "DBA".SpaArticles as ART,"dba".Services,"DBA".SpaInvoices as INV,"DBA".ServiceCredits as SC
    ,"dba".Admin as ADMIN
    where ART.InvoiceNo=INV.InvoiceNo
    and ART.CreditNo=SC.CreditNo
    and ART.CreditNo<>0
    and INV.KodAdmin=ADMIN.AdminNo
    and INV.InvoiceDateDt>=bvrData1
    and INV.InvoiceDateDt<=bvrData2
    and ART.ServiceNo=Services.ServiceNo
    and SC.SpaGuestTypeFlag=1
    
GO 

Delete from Relations where lcase(RelationTable) = lcase('SPA ProdajbiPoUslugi')


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_SpaProdajbiPoUslugi','bvrData1','Спа отчет услуги','От дата','SPA ProdajbiPoUslugi','bvrData1',2,
 GetFreeNInRelations('SPA ProdajbiPoUslugi', Null, Null, 10)
 ,'bvrData1','','',1,0,'1','1','','select bvrData1',56,'',0,0,1,'20060213')

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_SpaProdajbiPoUslugi','bvrData2','Спа отчет услуги','До дата',
 'SPA ProdajbiPoUslugi','bvrData2',2,
 GetFreeNInRelations('SPA ProdajbiPoUslugi', Null, Null, 10)
 ,'bvrData2','','',1,0,'1','1','','select bvrData2',56,'',0,0,1,'20060213')

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_SpaProdajbiPoUslugi','SmetkaNo','Спа отчет услуги','С-ка No',
 'SPA ProdajbiPoUslugi','Account #',2,
 GetFreeNInRelations('SPA ProdajbiPoUslugi', Null, Null, 10),
 'SmetkaNo','','',1,1,'','','','',56,'',0,0,1,'20060213')

 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_SpaProdajbiPoUslugi','FakturaNo','Спа отчет услуги','Ф-ра No',
 'SPA ProdajbiPoUslugi','Invoice Number',2,
 GetFreeNInRelations('SPA ProdajbiPoUslugi', Null, Null, 10),
 'FakturaNo','','',1,0,'','','','',56,'',0,0,1,'20060213')

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_SpaProdajbiPoUslugi','DokData','Спа отчет услуги','Дата',
 'SPA ProdajbiPoUslugi','Date',2,
  GetFreeNInRelations('SPA ProdajbiPoUslugi', Null, Null, 10),
 'DokData','','',0,0,'','','','',72,'',0,0,1,'20060213')

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_SpaProdajbiPoUslugi','Service','Спа отчет услуги','Услуга',
 'SPA ProdajbiPoUslugi','Service',2,
 GetFreeNInRelations('SPA ProdajbiPoUslugi', Null, Null, 10),
 'Service','','',0,0,'','',
 'select ServiceName from Services where ServiceType=2 order By ServiceName','',150,'',0,0,1,'20060213')

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_SpaProdajbiPoUslugi','Kol','Спа отчет услуги','Кол.',
 'SPA ProdajbiPoUslugi','Quant.',2,
 GetFreeNInRelations('SPA ProdajbiPoUslugi', Null, Null, 10),
 'SUM(Kol)','SUM(qrData.Кол.)','0.##',1,0,'','','','',56,'',0,0,1,'20060213')

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_SpaProdajbiPoUslugi','EdCena','Спа отчет услуги','Ед.цена',
 'SPA ProdajbiPoUslugi','Sng. Price',2,
 GetFreeNInRelations('SPA ProdajbiPoUslugi', Null, Null, 10),
 'EdCena','','# ### ##0.00',1,0,'','','','',72,'',0,0,1,'20060213')

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_SpaProdajbiPoUslugi','Stoinost','Спа отчет услуги','Стойност',
 'SPA ProdajbiPoUslugi','Value',2,
 GetFreeNInRelations('SPA ProdajbiPoUslugi', Null, Null, 10),
 'SUM(Stoinost)','SUM(qrData.Стойност)','# ### ##0.00',1,0,'','','','',80,'',0,0,1,'20060213')

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_SpaProdajbiPoUslugi','StoinostBDDS','Спа отчет услуги','Стн. б.ДДС',
 'SPA ProdajbiPoUslugi','Value (no VAT)',0,
 GetFreeNInRelations('SPA ProdajbiPoUslugi', Null, Null, 10),
 'SUM(StoinostBDDS)','SUM(qrData.Стн. б.ДДС)','# ### ##0.00',1,0,'','','','',80,'',0,0,1,'20060213')

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_SpaProdajbiPoUslugi','Discount','Спа отчет услуги','Отстъпка',
 'SPA ProdajbiPoUslugi','Discount',2,
 GetFreeNInRelations('SPA ProdajbiPoUslugi', Null, Null, 10),
 'Discount','','',1,0,'','','','',55,'',0,0,1,'20060213')

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_SpaProdajbiPoUslugi','EdCenaWithDisc','Спа отчет услуги','Цена с отстъпка',
 'SPA ProdajbiPoUslugi','EdCenaWithDisc',2,
 GetFreeNInRelations('SPA ProdajbiPoUslugi', Null, Null, 10),
 'EdCenaWithDisc','','# ### ##0.00',1,0,'','','','',70,'',0,0,1,'20060213')

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_SpaProdajbiPoUslugi','StoinostWithDisc','Спа отчет услуги','Сума с отстъпка',
 'SPA ProdajbiPoUslugi','StoinostWithDisc',2,
 GetFreeNInRelations('SPA ProdajbiPoUslugi', Null, Null, 10),
 'StoinostWithDisc','','# ### ##0.00',1,0,'','','','',70,'',0,0,1,'20060213')

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_SpaProdajbiPoUslugi','Klient','Спа отчет услуги','Клиент',
 'SPA ProdajbiPoUslugi','Client',2,
 GetFreeNInRelations('SPA ProdajbiPoUslugi', Null, Null, 10),
 'Klient','','',0,0,'','',
 'SELECT Whom FROM SpaInvoices GROUP BY Whom','',160,'',0,0,1,'20060213')

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_SpaProdajbiPoUslugi','Operator','Спа отчет услуги','Съставил',
 'SPA ProdajbiPoUslugi','Signed By',2,
 GetFreeNInRelations('SPA ProdajbiPoUslugi', Null, Null, 10),
 'Operator','','',0,0,'','','SELECT ShortName FROM ADMIN','',110,'',0,0,1,'20060213')

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_SpaProdajbiPoUslugi','RoomNo','Спа отчет услуги','Стая No',
 'SPA ProdajbiPoUslugi','RoomNo',2,
 GetFreeNInRelations('SPA ProdajbiPoUslugi', Null, Null, 10),
 'RoomNo','','',1,0,'','','','',50,'',0,0,1,'20060213')

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_SpaProdajbiPoUslugi','GuestType','Спа отчет услуги','Тип гост',
 'SPA ProdajbiPoUslugi','GuestType',2,
 GetFreeNInRelations('SPA ProdajbiPoUslugi', Null, Null, 10),
 'GuestType','','',1,0,'','','select ''Гост от Хотел'' union select ''СПА Гост''','',80,'',0,0,1,'20060213')

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_SpaProdajbiPoUslugi','__Options__','Спа отчет услуги','__Options__','SPA ProdajbiPoUslugi',
 '__Options__',1,
 GetFreeNInRelations('SPA ProdajbiPoUslugi', Null, Null, 10),
 '__Options__','','',0,0,'','','','',1,'',0,0,1,'20060213')

GO

Call InsertPravaSpravki('SPA ProdajbiPoUslugi');