--колона Fiscal в Invoices
IF (Not EXISTS(select * from sys.syscolumn as C, sys.systable as T
where LCase(T.table_name) = LCase('Invoices') and LCase(C.column_name) = LCase('Fiscal') and C.table_id=T.table_id)) THEN
  alter table Invoices add Fiscal integer default 0;
  update Invoices set Fiscal=1 where Fiscal=0;
END IF

GO

--колона Fiscal в SpaInvoices
IF (Not EXISTS(select * from sys.syscolumn as C, sys.systable as T
where LCase(T.table_name) = LCase('SpaInvoices') and LCase(C.column_name) = LCase('Fiscal') and C.table_id=T.table_id)) THEN
  alter table SpaInvoices add Fiscal integer default 0;
  update SpaInvoices set Fiscal=1 where Fiscal=0;
END IF

GO

--колона Fiscal в VaucherInvoices
IF (Not EXISTS(select * from sys.syscolumn as C, sys.systable as T
where LCase(T.table_name) = LCase('VaucherInvoices') and LCase(C.column_name) = LCase('Fiscal') and C.table_id=T.table_id)) THEN
  alter table VaucherInvoices add Fiscal integer default 0;
  update VaucherInvoices set Fiscal=1 where Fiscal=0;
END IF

GO

--справка "Издадени сметки"
IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_IzdadeniSmetki')))
THEN Drop view v_IzdadeniSmetki
END IF

GO

create view
  DBA.v_IzdadeniSmetki as
  select SmetkaNo=INV.InvoiceNo,
    FakturaNo=if((INV.TrueInvoiceNo <> 0) and(INV.TrueInvoiceNo < 1000000000)) then INV.MaskaFaktura || substr('0000000000',1,9-length(INV.TrueInvoiceNo)) || INV.TrueInvoiceNo
    else convert(varchar,INV.TrueInvoiceNo)
    endif,
    DokData=INV.InvoiceDateDt,
    --Maria +, 1975
    DokTime=DateFormat(INV.TrueInvoiceHour,'hh:mm'),
    --Maria -
    Klient=INV.Whom,
    DanNo=INV.Bulstat,
    Operator=ADMIN.ShortName,
    SumaBDDS=round(TotalNoVAT,4),
    DDS=round(VAT,4),
    Suma=round(GrandTotal,4),
    PlatVBroi=round(VBroi,4),
    PlatPoSmetka=round(PoSmetka,4),
    Plateni=round(VBroi+PoSmetka,4),
    DDSTypeSt=(if INV.DDSType = 1 then '20%' else '7%' endif), 
	--Maria +, 1982
	Fiscal=(if INV.Fiscal=0 then 'Не' else 'Да' endif)
	--Maria -
	from
    DBA.Invoices as INV,DBA.v_PariPoSmetki as PARI,DBA.Admin where
    INV.InvoiceNo = PARI.SmetkaNo and INV.KodAdmin = ADMIN.AdminNo union all
  select SmetkaNo=INV.VaucherInvoiceNo,
    FakturaNo=if((INV.TrueInvoiceNo <> 0) and(INV.TrueInvoiceNo < 1000000000)) then INV.MaskaFaktura || substr('0000000000',1,9-length(INV.TrueInvoiceNo)) || INV.TrueInvoiceNo
    else convert(varchar,INV.TrueInvoiceNo)
    endif,
    DokData=INV.InvoiceDateDt,
    --Maria +, 1975
    DokTime=DateFormat(INV.TrueInvoiceHour,'hh:mm'),
    --Maria -
    Klient=INV.Whom,
    DanNo=INV.Bulstat,
    Operator=ADMIN.ShortName,
    SumaBDDS=round(GrandTotal,4),
    DDS=round(VAT,4),
    Suma=round(TotalNoVAT,4),
    PlatVBroi=round(VBroi,4),
    PlatPoSmetka=round(PoSmetka,4),
    Plateni=round(VBroi+PoSmetka,4),DDSTypeSt='7%',
	--Maria +, 1982
	Fiscal=(if INV.Fiscal=0 then 'Не' else 'Да' endif)
	--Maria -
	from
    DBA.VaucherInvoices as INV,DBA.v_VtdPariPoSmetki as PARI,DBA.Admin where
    INV.VaucherInvoiceNo = PARI.SmetkaNo and INV.KodAdmin = ADMIN.AdminNo
    
GO

Delete from Relations where lcase(TableName) = lcase('v_IzdadeniSmetki')

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_IzdadeniSmetki','__OPTIONS__','Издадени сметки','__OPTIONS__','Issued Accounts','__OPTIONS__',1,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'__OPTIONS__','','',0,0,'','','','',1,'',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_IzdadeniSmetki','SmetkaNo','Издадени сметки','С-ка No','Issued Accounts','Account #',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'SmetkaNo','','',1,1,'','','','',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_IzdadeniSmetki','FakturaNo','Издадени сметки','Ф-ра No','Issued Accounts','Invoice Number',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'FakturaNo','','',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_IzdadeniSmetki','DokData','Издадени сметки','Дата','Issued Accounts','Date',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'DokData','','',0,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_IzdadeniSmetki','DokTime','Издадени сметки','Час','Issued Accounts','Time',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'DokTime','','',0,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_IzdadeniSmetki','SumaBDDS','Издадени сметки','Стн. б.ДДС','Issued Accounts','Value (no VAT)',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'SUM(SumaBDDS)','SUM(qrData.Стн. б.ДДС)','# ### ##0.00',1,0,'','','','',80,'',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_IzdadeniSmetki','DDS','Издадени сметки','ДДС','Issued Accounts','VAT',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'SUM(DDS)','SUM(qrData.ДДС)','# ### ##0.00',1,0,'','','','',80,'',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_IzdadeniSmetki','Suma','Издадени сметки','Стойност','Issued Accounts','Value',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'SUM(Suma)','SUM(qrData.Стойност)','# ### ##0.00',1,0,'','','','',80,'',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_IzdadeniSmetki','PlatVBroi','Издадени сметки','В брой','Issued Accounts','In Cash',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'SUM(PlatVBroi)','SUM(qrData.В брой)','# ### ##0.00',1,0,'','','','',80,'',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_IzdadeniSmetki','PlatPoSmetka','Издадени сметки','По сметка','Issued Accounts','On Account',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'SUM(PlatPoSmetka)','SUM(qrData.По сметка)','# ### ##0.00',1,0,'','','','',80,'',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_IzdadeniSmetki','Plateni','Издадени сметки','Общо пл.','Issued Accounts','Total Paid',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'SUM(Plateni)','SUM(qrData.Общо пл.)','# ### ##0.00',1,0,'','','','',80,'',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_IzdadeniSmetki','Klient','Издадени сметки','Клиент','Issued Accounts','Client',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'Klient','','',0,0,'','','SELECT Whom FROM Invoices GROUP BY Whom','',160,'',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_IzdadeniSmetki','DanNo','Издадени сметки','ЕИН','Issued Accounts','Tax #',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'DanNo','','',0,0,'','','SELECT Whom FROM Invoices GROUP BY Whom','',88,'',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_IzdadeniSmetki','Operator','Издадени сметки','Съставил','Issued Accounts','Signed By',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'Operator','','',0,0,'','','SELECT ShortName FROM ADMIN','',110,'',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_IzdadeniSmetki','DDSTypeSt','Издадени сметки','ДДС Тип','Issued Accounts','DDSTypeSt',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'DDSTypeSt','','',0,0,0,0,'select ''20%'' union all select ''7%'' ','',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_IzdadeniSmetki','Fiscal','Издадени сметки','Фискализирана','Issued Accounts','Fiscal',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'Fiscal','','',0,0,0,0,'select ''Да'' union all select ''Не'' ','',60,'',0,0,1,today())

Go

delete from InformPermission where RelationN=(
select N from Relations where TableName='v_IzdadeniSmetki'and ColName='__OPTIONS__')

Go

f1: for f1 as curs scroll cursor for select grNo=N from AdminGroups do
  if grNo <> 0 then
    insert into InformPermission(N,RelationN,AdminGroupNo,Stoinost)
    values((select MAX(N+1) from InformPermission),(select N from Relations where TableName='v_IzdadeniSmetki'and ColName='__OPTIONS__'  ),GrNo,1);
  end if;
end for;

GO

--Справка "Издадени сметки по услуги"
IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_ProdajbiPoUslugi')))
THEN Drop view v_ProdajbiPoUslugi
END IF

GO

create view
  DBA.v_ProdajbiPoUslugi as
  select OtchetNumber=DBA.GetOtchetNumber(ART.InvoiceNo),
    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,
--Maria +, 1975
    DokTime=DateFormat(INV.TrueInvoiceHour,'hh:mm'),
--Maria -
    Service=Services.ServiceName,
    /*Kol=if INV.CancelInvoiceNo>0 then(-1)*ART.Qty else ART.Qty endif,*/
    /*Kol=if (ART.totalsum < 0 ) then ((-1)*ART.Qty) else ART.Qty endif,*/
    Kol=if(ART.totalsum > 0) then
      ART.Qty
    else
      if(ART.Qty < 0) then
        ART.Qty
      -- Yanko +
      -- dobaveno, za da ne se pokazva "-" pred kolichestvoto, ako kol.>0, a cenata e 0
      else -- ((-1)*ART.Qty)
        if(ART.Totalsum = 0) then
          ART.QTY
        else((-1)*ART.Qty)
        endif
      endif -- Yanko -
    endif,
    EdCena=round(abs(ART.Price)*((1+VR.Rate)*(if TipDDS = 2 then 1 else 0 endif)),4),
    Stoinost=round(Kol*EdCena,4),
    StoinostBDDS=round(Kol*abs(ART.Price),4),
    DDS=Stoinost-StoinostBDDS,
    Discount=ART.Discount,
    EdCenaWithDisc=round(DBA.CalcPriceWithDiscount(Discount,EdCena),4),
    StoinostWithDisc=round(Kol*EdCenaWithDisc,4),
    Klient=INV.Whom,
    DanNo=INV.WhomDanNum,
    GuestNum=G.GuestNum,
    GuestName=G.Name,
    Komentar=String(DateRegDt,' - ',"Date"(Days(DateRegDt,NumLodgings))),
    Operator=ADMIN.ShortName,
    ContractName=(if(Inv.ContractNo <> 0) then
      (select CC.ShortName from dba.Contracts as CC where CC.ContractNo = Inv.ContractNo)
    else
      Contracts.ShortName
    endif),
    Country=Countries.Description,
    Nationality=HK.Description,
    --Maria
    --RoomNo=convert(varchar,BK.Room),
    RoomNo=R.RoomName,
    --Maria
    --DDSTypeSt=(if ART.DDSType = 1 then '20%' else '7%' endif)
    DDSTypeSt=convert(varchar,DBA.FormatNum(VR.Rate*100,'0.##'))+'%',
	--Maria +,1982
	Fiscal=(if INV.Fiscal=0 then 'Не' else 'Да' endif)
	--Maria -
	from
    DBA.Articles as ART,
    DBA.Services,
    DBA.Invoices as INV,
    DBA.Book as BK,
    --Maria
    DBA.Rooms as R,
    --Maria
    DBA.Guests as G,
    DBA.Admin as ADMIN,
    DBA.Contracts,
    DBA.Countries,
    DBA.HumanKinds as HK,
    DBA.VATRates as VR where
    --Maria
    BK.Room = R.RoomNum and
    --Maria
    ART.InvoiceNo = INV.InvoiceNo and
    Abs(RegNo) = BK.RegNum and
    BK.RegNum <> 0 and
    INV.InvoiceDateDt >= bvrData1 and
    INV.InvoiceDateDt <= bvrData2 and
    BK.Guest = G.GuestNum and
    INV.KodAdmin = ADMIN.AdminNo and
    ART.ServiceNo = Services.ServiceNo and
    G.Contract = Contracts.ContractNo and
    G.HumanKind = HK.HumanKindNo and
    G.NativeCountry = Countries.CountryKod and
    VR.DDSType = INV.DDSType union all
  select OtchetNumber=DBA.GetOtchetNumber(ART.InvoiceNo),
    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,
--Maria +, 1975
    DokTime=DateFormat(INV.TrueInvoiceHour,'hh:mm'),
--Maria -
    Service=Services.ServiceName,
    Kol=if INV.CancelInvoiceNo > 0 then(-1)*ART.Qty else ART.Qty endif,
    EdCena=round(abs(ART.Price)*((1+VR.Rate)*(if ART.TipDDS = 2 then 1 else 0 endif)),4),
    Stoinost=round(Kol*EdCena,4),
    StoinostBDDS=round(Kol*abs(ART.Price),4),
    DDS=Stoinost-StoinostBDDS,
    Discount=ART.Discount,
    EdCenaWithDisc=round(DBA.CalcPriceWithDiscount(Discount,EdCena),4),
    StoinostWithDisc=round(Kol*EdCenaWithDisc,4),
    Klient=INV.Whom,
    DanNo=INV.WhomDanNum,
    GuestNum=G.GuestNum,
    GuestName=G.Name,Komentar='',
    Operator=ADMIN.ShortName,
    ContractName=(if(Inv.ContractNo <> 0) then
      (select CC.ShortName from dba.Contracts as CC where CC.ContractNo = Inv.ContractNo)
    else
      Contracts.ShortName
    endif),
    Country=Countries.Description,
    Nationality=HK.Description,
    --Maria
    --RoomNo=(if(G.GuestNum <> 0) then convert(varchar,(select distinct LastRoom from dba.v_ArriveStayLeaveGuestBook where GuestNum = G.GuestNum)) else null endif),
    RoomNo=(if(G.GuestNum <> 0) then(select distinct LastRoomName from dba.v_ArriveStayLeaveGuestBook where GuestNum = G.GuestNum) else null endif),
    --Maria
    --DDSTypeSt=(if ART.DDSType = 1 then '20%' else '7%' endif)
    DDSTypeSt=convert(varchar,DBA.FormatNum(VR.Rate*100,'0.##'))+'%',
	--Maria +,1982
	Fiscal=(if INV.Fiscal=0 then 'Не' else 'Да' endif)
	--Maria -
    from
    -- RoomNo=(if(G.GuestNum<>0) then convert(varchar,(select distinct Max(B.Room) from "dba".Book as B,"dba".Guests as G where B.WhoPays=G.GuestNum and INV.Whom=G."Name" and G.GuestNum<>0)) else null endif)
    DBA.Articles as ART,
    DBA.Services,
    DBA.Invoices as INV,
    DBA.ServiceCredits as SC,
    DBA.Guests as G,
    DBA.Admin as ADMIN,
    DBA.Contracts,dba.Countries,
    DBA.HumanKinds as HK,
    DBA.VATRates as VR where
    ART.InvoiceNo = INV.InvoiceNo and
    ART.CreditNo = SC.CreditNo and
    ART.CreditNo <> 0 and
    SC.GuestNo = G.GuestNum and
    INV.KodAdmin = ADMIN.AdminNo and
    INV.InvoiceDateDt >= bvrData1 and
    INV.InvoiceDateDt <= bvrData2 and
    --and ART.ServiceNo not in(1004,1005)
    ART.ServiceNo = Services.ServiceNo and
    G.Contract = Contracts.ContractNo and
    G.NativeCountry = Countries.CountryKod and
    G.HumanKind = HK.HumanKindNo and
    VR.DDSType = INV.DDSType union all
  select OtchetNumber=DBA.GetOtchetNumber(ART.InvoiceNo),
    SmetkaNo=ART.InvoiceNo,
    FakturaNo=if((max(INV.TrueInvoiceNo) <> 0) and(max(INV.TrueInvoiceNo) < 1000000000)) then max(INV.MaskaFaktura) || substr('0000000000',1,9-length(max(INV.TrueInvoiceNo))) || max(INV.TrueInvoiceNo) endif,
    DokData=Max(INV.InvoiceDateDt),
--Maria +, 1975
    DokTime=DateFormat(Max(INV.TrueInvoiceHour),'hh:mm'),
--Maria -
    Service=Max(Services.ServiceName),
    Kol=if INV.CancelInvoiceNo > 0 then(-1)*max(ART.Qty) else max(ART.Qty) endif,
    EdCena=round(Max(abs(ART.Price)*((1+VR.Rate)*(if ART.TipDDS = 2 then 1 else 0 endif))),4),
    Stoinost=round(Kol*EdCena,4),
    StoinostBDDS=round(Kol*Max(abs(ART.Price)),4),
    DDS=Stoinost-StoinostBDDS,
    Discount=ART.Discount,
    EdCenaWithDisc=round(DBA.CalcPriceWithDiscount(Discount,EdCena),4),
    StoinostWithDisc=round(Kol*EdCenaWithDisc,4),
    Klient=Max(INV.Whom),
    DanNo=Max(INV.WhomDanNum),
    GuestNum=Max(RM.GuestNo),
    GuestName=Max(G.Name),Komentar='',
    Operator=Max(ADMIN.ShortName),
    ContractName=Max(if(Inv.ContractNo <> 0) then
      (select CC.ShortName from dba.Contracts as CC where CC.ContractNo = Inv.ContractNo)
    else
      Contracts.ShortName
    endif),
    Country=Max(Countries.Description),
    Nationality=Max(HK.Description),
    --Maria
    --RoomNo=max(if(G.GuestNum <> 0) then convert(varchar,(select distinct LastRoom from dba.v_ArriveStayLeaveGuestBook where GuestNum = G.GuestNum)) else null endif),
    RoomNo=max(if(G.GuestNum <> 0) then(select distinct LastRoomName from dba.v_ArriveStayLeaveGuestBook where GuestNum = G.GuestNum) else null endif),
    --Maria
    --DDSTypeSt=(if ART.DDSType = 1 then '20%' else '7%' endif)
    DDSTypeSt=convert(varchar,DBA.FormatNum(Max(VR.Rate)*100,'0.##'))+'%',
	--Maria +,1982
	Fiscal=(if INV.Fiscal=0 then 'Не' else 'Да' endif)
	--Maria -
    from
    --RoomNo=Max((if(G.GuestNum<>0) then convert(varchar,(select distinct Max(B.Room) from "dba".Book as B,"dba".Guests as G where B.WhoPays=G.GuestNum and INV.Whom=G."Name" and G.GuestNum<>0)) else null endif))
    DBA.Articles as ART,
    DBA.Services,
    DBA.Invoices as INV,
    DBA.Guests as G,
    DBA.Admin as ADMIN,
    DBA.Contracts,
    DBA.Countries,
    DBA.ResMoney as RM,
    DBA.HumanKinds as HK,
    DBA.VATRates as VR where
    ART.InvoiceNo = INV.InvoiceNo and
    ART.CreditNo = 0 and
    ART.RegNo = 0 and
    RM.InvoiceNo = INV.InvoiceNo and
    G.GuestNum = RM.GuestNo and
    INV.InvoiceDateDt >= bvrData1 and
    INV.InvoiceDateDt <= bvrData2 and
    G.Contract = Contracts.ContractNo and
    G.NativeCountry = Countries.CountryKod and
    INV.KodAdmin = ADMIN.AdminNo and
    ART.ServiceNo = Services.ServiceNo and
    G.HumanKind = HK.HumanKindNo and
    ART.ServiceNo in( 1004,1005) and
    VR.DDSType = INV.DDSType
    group by ART.InvoiceNo,
    ART.ArticleNo,
    INV.CancelInvoiceNo,
    Discount,
    ART.DDSType,INV.Fiscal union all
  select OtchetNumber=DBA.GetOtchetNumber(VA.InvoiceNo),
    SmetkaNo=VA.InvoiceNo,
    FakturaNo=if((VI.TrueInvoiceNo <> 0) and(VI.TrueInvoiceNo < 1000000000)) then VI.MaskaFaktura || substr('0000000000',1,9-length(VI.TrueInvoiceNo)) || VI.TrueInvoiceNo endif,
    DokData=VI.InvoiceDateDt,
--Maria +, 1975
    DokTime=DateFormat(VI.TrueInvoiceHour,'hh:mm'),
--Maria -
    Service=S.ServiceName,
    Kol=(if VI.CancelInvoiceNo > 0 then-Abs(VA.TotalNights) else Abs(VA.TotalNights) endif),
    EdCena=Round(Abs(if VA.SinglePrice <> 0 then(VA.SinglePrice*Cr.ExchangeRate) else(VA.GrandTotal*Cr.ExchangeRate)/VA.TotalNights endif)*(if VI.VAT = 0 then 1 else(1+VR.Rate) endif),4),
    Stoinost=Round(Kol*EdCena,4),
    StoinostBDDS=Round(Kol*Abs(if VA.SinglePrice <> 0 then(VA.SinglePrice*Cr.ExchangeRate) else(VA.GrandTotal*Cr.ExchangeRate)/VA.TotalNights endif),4),
    DDS=Stoinost-StoinostBDDS,
    Discount=(if VA.SinglePrice <> 0 then VA.Discount else 0 endif),
    EdCenaWithDisc=Round(DBA.CalcPriceWithDiscount(Discount,EdCena),4),
    StoinostWithDisc=Round(Kol*EdCenaWithDisc,4),
    Klient=VI.Whom,
    DanNo=VI.WhomDanNum,
    GuestNum=(if VA.CreditNo > 0 then(select SC.GuestNo from DBA.ServiceCredits as SC where SC.CreditNo = VA.CreditNo) else-1 endif),
    GuestName=(if VA.CreditNo > 0 then(select G.Name from DBA.Guests as G,DBA.ServiceCredits as SC where G.GuestNum = SC.GuestNo and SC.CreditNo = VA.CreditNo)
    -- [v.1 --]
    -- [v.2 23.Mar.2007] - Yanko, dobaveni smetki ot VTD, promeneno DDS da se vzima ot VATRates
    -- [v.3 26.May.2008] - gnikolov, добавена колона ДДС, p1902
    --Maria
    --RoomNo=(if(GuestNum > 0) then convert(varchar,(select distinct vv.LastRoom from dba.v_ArriveStayLeaveGuestBook as vv where vv.GuestNum = GuestNum)) else null endif),
    --Maria
    else '-' endif),Komentar='',Operator=ADMIN.ShortName,
    ContractName=C.ShortName,
    Country=(if GuestNum > 0 then(select CC.Description from DBA.Countries as CC,DBA.Guests as G where CC.CountryKod = G.NativeCountry and G.GuestNum = GuestNum)
    else '-' endif),Nationality=(if GuestNum > 0 then(select HK.Description from DBA.HumanKinds as HK,DBA.Guests as G where HK.HumanKindNo = G.HumanKind and G.GuestNum = GuestNum)
    else '-' endif),RoomNo=(if(GuestNum > 0) then(select distinct vv.LastRoomName from dba.v_ArriveStayLeaveGuestBook as vv where vv.GuestNum = GuestNum) else null endif),
    DDSTypeSt=convert(varchar,DBA.FormatNum(VR.Rate*100,'0.##'))+'%',
	--Maria +,1982
	Fiscal=(if VI.Fiscal=0 then 'Не' else 'Да' endif)
	--Maria - 
	from
    DBA.VaucherArticles as VA,
    DBA.VaucherInvoices as VI,
    DBA.Services as S,
    DBA.VATRates as VR,
    DBA.Admin,
    DBA.Contracts as C,
    DBA.Currencies as Cr where
    VA.InvoiceNo = VI.VaucherInvoiceNo and
    VA.ServiceNo = S.ServiceNo and
    VR.ID = 4 and
    VI.KodAdmin = Admin.AdminNo and
    C.ContractNo = VI.ContractNo and
    VI.InvoiceDateDt >= bvrData1 and
    VI.InvoiceDateDt <= bvrData2 and
    Cr.KodCurrency = VI.KodCurrency
    
GO

Delete from Relations where lcase(TableName) = lcase('v_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_ProdajbiPoUslugi','__OPTIONS__','Издадени сметки по услуги','__OPTIONS__','Issued Accounts by Service','__OPTIONS__',1,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'__OPTIONS__','','',0,0,'','','','',1,'',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_ProdajbiPoUslugi','bvrData1','Издадени сметки по услуги','От дата','Issued Accounts by Service','bvrData1',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'bvrData1','','',1,0,1,1,'','select bvrData1',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_ProdajbiPoUslugi','bvrData2','Издадени сметки по услуги','До дата','Issued Accounts by Service','bvrData2',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'bvrData2','','',1,0,1,1,'','select bvrData2',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_ProdajbiPoUslugi','OtchetNumber','Издадени сметки по услуги','Отчет N','Issued Accounts by Service','OtchetNumber',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'OtchetNumber','','',1,0,'','','','',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_ProdajbiPoUslugi','SmetkaNo','Издадени сметки по услуги','С-ка No','Issued Accounts by Service','Account #',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'SmetkaNo','','',1,1,'','','','',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_ProdajbiPoUslugi','FakturaNo','Издадени сметки по услуги','Ф-ра No','Issued Accounts by Service','Invoice Number',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'FakturaNo','','',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_ProdajbiPoUslugi','DokData','Издадени сметки по услуги','Дата','Issued Accounts by Service','Date',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'DokData','','',0,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_ProdajbiPoUslugi','DokTime','Издадени сметки по услуги','Час','Issued Accounts by Service','Time',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'DokTime','','',0,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_ProdajbiPoUslugi','Service','Издадени сметки по услуги','Услуга','Issued Accounts by Service','Service',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'Service','','',0,0,'','','select ServiceName from Services union
(select ''Нощувка'')  union
(select ''Застраховка'')
','',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_ProdajbiPoUslugi','Kol','Издадени сметки по услуги','Кол.','Issued Accounts by Service','Quant.',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'SUM(Kol)','SUM(qrData.Кол.)','0.##',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_ProdajbiPoUslugi','EdCena','Издадени сметки по услуги','Ед.цена','Issued Accounts by Service','Sng. Price',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'EdCena','','# ### ##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_ProdajbiPoUslugi','Stoinost','Издадени сметки по услуги','Стойност','Issued Accounts by Service','Value',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'SUM(Stoinost)','SUM(qrData.Стойност)','# ### ##0.00',1,0,'','','','',80,'',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_ProdajbiPoUslugi','StoinostBDDS','Издадени сметки по услуги','Стн. б.ДДС','Issued Accounts by Service','Value (no VAT)',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'SUM(StoinostBDDS)','SUM(qrData.Стн. б.ДДС)','# ### ##0.00',1,0,'','','','',80,'',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_ProdajbiPoUslugi','DDS','Издадени сметки по услуги','ДДС','Issued Accounts by Service','DDS',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'SUM(DDS)','SUM(qrData.ДДС)','# ### ##0.00',0,0,0,0,'','',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_ProdajbiPoUslugi','Klient','Издадени сметки по услуги','Клиент','Issued Accounts by Service','Client',0,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'Klient','','',0,0,'','','SELECT Whom FROM Invoices GROUP BY Whom','',160,'',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_ProdajbiPoUslugi','GuestNum','Издадени сметки по услуги','Кл.номер','Issued Accounts by Service','Client Number',0,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'GuestNum','','',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_ProdajbiPoUslugi','GuestName','Издадени сметки по услуги','Име гост','Issued Accounts by Service','Guest Name',0,GetFreeNInRelations('Issued Accounts by Service', 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_ProdajbiPoUslugi','ContractName','Издадени сметки по услуги','Фирма','Issued Accounts by Service','ContractName',0,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'ContractName','','',1,0,'','','select ShortName from Contracts','',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_ProdajbiPoUslugi','Country','Издадени сметки по услуги','Държава','Issued Accounts by Service','Country',0,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'Country','','',1,0,'','','select Description from Countries where Active=1','',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_ProdajbiPoUslugi','Komentar','Издадени сметки по услуги','Коментар','Issued Accounts by Service','Comments',0,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'Komentar','','',0,0,'','','','',170,'',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_ProdajbiPoUslugi','Operator','Издадени сметки по услуги','Съставил','Issued Accounts by Service','Signed By',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'Operator','','',0,0,'','','SELECT ShortName FROM ADMIN','',110,'',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_ProdajbiPoUslugi','Nationality','Издадени сметки по услуги','Националност','Issued Accounts by Service','Nationality',0,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'Nationality','','',0,0,'','','SELECT Description FROM HumanKinds','',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_ProdajbiPoUslugi','RoomNo','Издадени сметки по услуги','Стая No','Issued Accounts by Service','RoomNo',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'RoomNo','','',1,0,'','','','',50,'',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_ProdajbiPoUslugi','Discount','Издадени сметки по услуги','Отстъпка','Issued Accounts by Service','Discount',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'Discount','','',1,0,'','','','',55,'',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_ProdajbiPoUslugi','EdCenaWithDisc','Издадени сметки по услуги','Цена с отстъпка','Issued Accounts by Service','EdCenaWithDisc',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'EdCenaWithDisc','','# ### ##0.00',1,0,'','','','',70,'',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_ProdajbiPoUslugi','StoinostWithDisc','Издадени сметки по услуги','Сума с отстъпка','Issued Accounts by Service','StoinostWithDisc',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'StoinostWithDisc','','# ### ##0.00',1,0,'','','','',70,'',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_ProdajbiPoUslugi','DDSTypeSt','Издадени сметки по услуги','ДДС Тип','Issued Accounts by Service','DDSTypeSt',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'DDSTypeSt','','',0,0,0,0,'select ''20%'' union all select ''7%'' ','',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_ProdajbiPoUslugi','Fiscal','Издадени сметки по услуги','Фискализирана','Issued Accounts by Service','Fiscal',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'Fiscal','','',0,0,0,0,'select ''Да'' union all select ''Не'' ','',60,'',0,0,1,today())

Go

delete from InformPermission where RelationN=(
select N from Relations where TableName='v_ProdajbiPoUslugi'and ColName='__OPTIONS__')

Go

f1: for f1 as curs scroll cursor for select grNo=N from AdminGroups do
  if grNo <> 0 then
    insert into InformPermission(N,RelationN,AdminGroupNo,Stoinost)
    values((select MAX(N+1) from InformPermission),(select N from Relations where TableName='v_ProdajbiPoUslugi'and ColName='__OPTIONS__'  ),GrNo,1);
  end if;
end for;

GO

--справка "Отчет плащания"
IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_PayTypes')))
THEN Drop view v_PayTypes
END IF

GO

create view DBA.v_PayTypes
  as select DataPay=ResMoney.DateMoneyDt,
    PayType=PayTypes.PayTypeName,
    AllSum=ResMoney.Sum*DayRates.ExchangeRate,
    AllSumVlt=ResMoney.Sum,
    Valuta=Currencies.Symbol,
    DayRates.ExchangeRate,
    PTVidName=PTV.Description,
    DocNomer=ResMoney.AccountNumber,
    Smetka=(if ResMoney.VaucherInvoiceNo is null then ResMoney.InvoiceNo else if ResMoney.InvoiceNo is null then ResMoney.VaucherInvoiceNo endif endif), 
   --Maria +, 1982 
    Fiscal=(if ResMoney.VaucherInvoiceNo is null then 
         (if (select Fiscal from Invoices where InvoiceNo=ResMoney.InvoiceNo)=0 then 'Не' else 'Да' endif) 
        else if ResMoney.InvoiceNo is null then 
          (if (select Fiscal from VaucherInvoices where VaucherInvoiceNo=ResMoney.VaucherInvoiceNo)=0 then 'Не' else 'Да' endif)
        endif 
     endif)
   from
   --Maria -
	dba.PayTypes,dba.ResMoney,dba.DayRates,DBA.Currencies,dba.PayTypesVids as PTV where
    ResMoney.Payment = PayTypes.PayTypeN and
    ResMoney.Currency = DayRates.KodCurrency and ResMoney.DateMoneyDt = DayRates.DateDt and
    ResMoney.Currency = Currencies.KodCurrency and
    DateMoneyDt >= bvrData1 and DateMoneyDt <= bvrData2 and
    ResMoney.PTVid = PTV.N
    
GO

Delete from Relations where lcase(TableName) = lcase('v_PayTypes')

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_PayTypes','__OPTIONS__','Отчет плащания','__OPTIONS__','Payment Report','__OPTIONS__',1,GetFreeNInRelations('Payment Report', Null, Null, 10),'__OPTIONS__','','',0,0,'','','','',1,'',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_PayTypes','bvrData1','Отчет плащания','От дата','Payment Report','From Date',2,GetFreeNInRelations('Payment Report', Null, Null, 10),'bvrData1','','',1,0,1,1,'','select Date(Now())-1',32,'',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_PayTypes','bvrData2','Отчет плащания','До дата','Payment Report','To Date',2,GetFreeNInRelations('Payment Report', Null, Null, 10),'bvrData2','','',1,0,1,1,'','select Date(Now())',32,'',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_PayTypes','DataPay','Отчет плащания','Дата','Payment Report','Date',2,GetFreeNInRelations('Payment Report', Null, Null, 10),'DataPay','','',0,1,'','','','',80,'',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_PayTypes','PayType','Отчет плащания','Начин пл.','Payment Report','Type Of Payment',2,GetFreeNInRelations('Payment Report', Null, Null, 10),'PayType','','',0,0,'','','select PayTypeName from PayTypes','',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_PayTypes','DocNomer','Отчет плащания','Номер на документ','Payment Report','Document Number',2,GetFreeNInRelations('Payment Report', Null, Null, 10),'DocNomer','','',1,0,'','','','',110,'',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_PayTypes','Smetka','Отчет плащания','Сметка N','Payment Report','Invoice Number',2,GetFreeNInRelations('Payment Report', Null, Null, 10),'Smetka','','',1,2,'','','','',80,'',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_PayTypes','Fiscal','Отчет плащания','Фискализирана','Payment Report','Fiscal',2,GetFreeNInRelations('Payment Report', Null, Null, 10),'Fiscal','','',1,2,'','',' select ''Не'' union all select ''Да'' ','',80,'',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_PayTypes','PTVidName','Отчет плащания','Вид','Payment Report','Вид',2,GetFreeNInRelations('Payment Report', Null, Null, 10),'PTVidName','','',1,0,'','','','',120,'',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_PayTypes','Valuta','Отчет плащания','Валута','Payment Report','Currency',2,GetFreeNInRelations('Payment Report', Null, Null, 10),'Valuta','','',1,0,'','','SELECT Symbol FROM Currencies','',64,'',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_PayTypes','AllSumVlt','Отчет плащания','Сума влт','Payment Report','Total Currency',2,GetFreeNInRelations('Payment Report', Null, Null, 10),'Sum(AllSumVlt)','','# ### ##0.00',1,0,'','','','',80,'',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_PayTypes','ExchangeRate','Отчет плащания','Курс','Payment Report','Курс',2,GetFreeNInRelations('Payment Report', Null, Null, 10),'ExchangeRate','','# ##0.0000',1,0,'','','','',64,'',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_PayTypes','AllSum','Отчет плащания','Сума лв','Payment Report','Total LC',2,GetFreeNInRelations('Payment Report', Null, Null, 10),'Sum(AllSum)','SUM(qrData.Сума лв)','# ### ##0.00',1,0,'','','','',80,'',0,0,1,today())

Go

delete from InformPermission where RelationN=(
select N from Relations where TableName='v_PayTypes'and ColName='__OPTIONS__')

Go

f1: for f1 as curs scroll cursor for select grNo=N from AdminGroups do
  if grNo <> 0 then
    insert into InformPermission(N,RelationN,AdminGroupNo,Stoinost)
    values((select MAX(N+1) from InformPermission),(select N from Relations where TableName='v_PayTypes'and ColName='__OPTIONS__'  ),GrNo,1);
  end if;
end for;

GO

--Справка "Отчет услуги"
IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_ReportServices')))
THEN Drop view v_ReportServices
END IF

GO

create view DBA.v_ReportServices as
  select Articles.InvoiceNo,
    Service=Services.ServiceName,
    Suma=round(if Articles.TipDDS = 3 then Articles.TotalSum else Articles.TotalSum*(1+VR.Rate) endif,4),
    Invoices.InvoiceDateDt,
    PayTypes.PayTypeName,
    Admin.FullName,
    ContractName=Contracts.FullName,
    Qty=(if Invoices.CancelInvoiceNo > 0 then(-1)*Articles.Qty else Articles.Qty endif),
	--Maria +, 1982
	Fiscal=(if Invoices.Fiscal=0 then 'Не' else 'Да' endif)
	--Maria -
    from
    DBA.Articles,
    DBA.PayTypes,
    DBA.Invoices,
    DBA.Services,
    DBA.Admin,
    DBA.Book,
    DBA.Guests,
    DBA.Contracts,
    DBA.VATRates as VR where
    Articles.InvoiceNo = Invoices.InvoiceNo and
    Invoices.InvoiceDateDt >= bvrData1 and
    Invoices.InvoiceDateDt <= bvrData2 and
    Invoices.PayHow = PayTypes.PayTypeN and
    Articles.ServiceNo = Services.ServiceNo and
    Invoices.KodAdmin = Admin.AdminNo and
    abs(Articles.RegNo) = Book.RegNum and
    Articles.RegNo <> 0 and
    Book.Guest = Guests.GuestNum and
    Guests.Contract *= Contracts.ContractNo and
    VR.DDSType = Articles.DDSType union all
  select Articles.InvoiceNo,
    Service=Services.ServiceName,
    Suma=round(if Articles.TipDDS = 3 then DBA.CalcPriceWithDiscount(Articles.Discount,Articles.Price*Articles.Qty) else DBA.CalcPriceWithDiscount(Articles.Discount,Articles.Price*Articles.Qty)*(1+VR.Rate) endif,4),
    Invoices.InvoiceDateDt,
    PayTypes.PayTypeName,
    Admin.FullName,
    ContractName=Contracts.FullName,
    Qty=(if Invoices.CancelInvoiceNo > 0 then(-1)*Articles.Qty else Articles.Qty endif),
	--Maria +, 1982
	Fiscal=(if Invoices.Fiscal=0 then 'Не' else 'Да' endif)
	--Maria -
    from
    DBA.Articles,
    DBA.PayTypes,
    DBA.Invoices,
    DBA.Services,
    DBA.Admin,
    DBA.ServiceCredits,
    DBA.Guests,
    DBA.Contracts,
    DBA.VATRates as VR where
    Articles.InvoiceNo = Invoices.InvoiceNo and
    Invoices.InvoiceDateDt >= bvrData1 and
    Invoices.InvoiceDateDt <= bvrData2 and
    Invoices.PayHow = PayTypes.PayTypeN and
    Articles.ServiceNo = Services.ServiceNo and
    Invoices.KodAdmin = Admin.AdminNo and
    Articles.CreditNo = ServiceCredits.CreditNo and
    Articles.CreditNo <> 0 and
    ServiceCredits.GuestNo = Guests.GuestNum and
    Guests.Contract *= Contracts.ContractNo and
    VR.DDSType = Articles.DDSType union all
  select InvoiceNo=max(Articles.InvoiceNo),
    Service=max(Services.ServiceName),
    Suma=round(max(if Articles.TipDDS = 3 then Articles.TotalSum else Articles.TotalSum*(1+VR.Rate) endif),4),
    max(Invoices.InvoiceDateDt),
    max(PayTypes.PayTypeName),
    max(Admin.FullName),
    ContractName=max(Contracts.FullName),
    Qty=(if Invoices.CancelInvoiceNo > 0 then(-1)*Articles.Qty else Articles.Qty endif),
	--Maria +, 1982
	Fiscal=(if Invoices.Fiscal=0 then 'Не' else 'Да' endif)
	--Maria - 
	from
    DBA.Articles,
    DBA.PayTypes,
    DBA.Invoices,
    DBA.Services,
    DBA.Admin,
    DBA.ResMoney,
    DBA.Guests,
    DBA.Contracts,
    DBA.VATRates as VR where
    Articles.InvoiceNo = Invoices.InvoiceNo and
    Invoices.InvoiceDateDt >= bvrData1 and
    Invoices.InvoiceDateDt <= bvrData2 and
    Invoices.PayHow = PayTypes.PayTypeN and
    Articles.ServiceNo = Services.ServiceNo and
    Invoices.KodAdmin = Admin.AdminNo and
    Articles.RegNo = 0 and
    Articles.CreditNo = 0 and
    ResMoney.InvoiceNo = Invoices.InvoiceNo and
    Guests.GuestNum = ResMoney.GuestNo and
    Contracts.ContractNo = Guests.Contract and
    Articles.ServiceNo in( 1004,1005) and
    VR.DDSType = Articles.DDSType
    group by Articles.InvoiceNo,
    Articles.ArticleNo,
    Articles.Qty,
    Invoices.CancelInvoiceNo,Invoices.Fiscal union all
  select VA.InvoiceNo,
    Service=S.ServiceName,
    Suma=Round((VA.GrandTotal*Cr.ExchangeRate)*(if(VI.VAT = 0 and VI.WithoutVAT <> 0) then 1 else(1+VR.Rate) endif),4),
    VI.InvoiceDateDt,
    PT.PayTypeName,
    Admin.FullName,
    ContractName=Contracts.FullName,
    Qty=(if VI.CancelInvoiceNo > 0 then-Abs(VA.TotalNights) else Abs(VA.TotalNights) endif),
	--Maria +, 1982
	Fiscal=(if VI.Fiscal=0 then 'Не' else 'Да' endif)
	--Maria -
    from
    DBA.VaucherArticles as VA,
    DBA.VaucherInvoices as VI,
    DBA.PayTypes as PT,
    DBA.VATRates as VR,
    DBA.Services as S,
    DBA.Admin,
    DBA.Contracts,
    DBA.Currencies as Cr where
    VA.InvoiceNo = VI.VaucherInvoiceNo and
    VI.PayHow = PT.PayTypeN and
    VI.KodAdmin = Admin.AdminNo and
    Contracts.ContractNo = VI.ContractNo and
    VI.InvoiceDateDt >= bvrData1 and
    VI.InvoiceDateDt <= bvrData2 and
    VR.ID = 4 and
    VA.ServiceNo = S.ServiceNo and
    Cr.KodCurrency = VI.KodCurrency
    
GO

Delete from Relations where lcase(TableName) = lcase('v_ReportServices')

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_ReportServices','__OPTIONS__','Отчет услуги','__OPTIONS__','Отчет услуги','__OPTIONS__',1,GetFreeNInRelations('Отчет услуги', Null, Null, 10),'__OPTIONS__','','',0,0,'','','','',1,'',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_ReportServices','bvrData1','Отчет услуги','От дата','Отчет услуги','От дата',2,GetFreeNInRelations('Отчет услуги', Null, Null, 10),'bvrData1','','',0,0,1,1,'','select bvrData1',64,'',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_ReportServices','bvrData2','Отчет услуги','До дата','Отчет услуги','До дата',2,GetFreeNInRelations('Отчет услуги', Null, Null, 10),'bvrData2','','',0,0,1,1,'','select bvrData2',64,'',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_ReportServices','InvoiceDateDt','Отчет услуги','Дата','Отчет услуги','Дата',2,GetFreeNInRelations('Отчет услуги', Null, Null, 10),'InvoiceDateDt','','',0,0,'','','','',64,'',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_ReportServices','InvoiceNo','Отчет услуги','Сметка','Отчет услуги','Сметка',2,GetFreeNInRelations('Отчет услуги', Null, Null, 10),'InvoiceNo','','',0,0,'','','','',64,'',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_ReportServices','Fiscal','Отчет услуги','Фискализирана','Отчет услуги','Фискализирана',2,GetFreeNInRelations('Отчет услуги', Null, Null, 10),'Fiscal','','',0,0,'','',' select ''Не'' union all select ''Да'' ','',64,'',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_ReportServices','Service','Отчет услуги','Услуга','Отчет услуги','Услуга',2,GetFreeNInRelations('Отчет услуги', Null, Null, 10),'Service','','',0,101,'','','select distinct Service from v_ReportServices','',200,'',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_ReportServices','Qty','Отчет услуги','Бр.услуги','Отчет услуги','Qty',2,GetFreeNInRelations('Отчет услуги', Null, Null, 10),'Sum(Qty)','SUM(qrData.Бр. услуги)','',1,0,'','','','',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_ReportServices','Suma','Отчет услуги','Сума','Отчет услуги','Сума',2,GetFreeNInRelations('Отчет услуги', Null, Null, 10),'Sum(Suma)','SUM(qrData.Сума)','# ### ##0.00',1,0,'','','','',64,'',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_ReportServices','PayTypeName','Отчет услуги','Начин на плащане','Отчет услуги','PayTypeName',2,GetFreeNInRelations('Отчет услуги', Null, Null, 10),'PayTypeName','','',0,0,'','','select PayTypeName from PayTypes','',80,'',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_ReportServices','FullName','Отчет услуги','Администратор','Отчет услуги','Администратор',2,GetFreeNInRelations('Отчет услуги', Null, Null, 10),'FullName','','',1,0,'','','','',200,'',0,0,1,today())

Go

delete from InformPermission where RelationN=(
select N from Relations where TableName='v_ReportServices'and ColName='__OPTIONS__')

Go

f1: for f1 as curs scroll cursor for select grNo=N from AdminGroups do
  if grNo <> 0 then
    insert into InformPermission(N,RelationN,AdminGroupNo,Stoinost)
    values((select MAX(N+1) from InformPermission),(select N from Relations where TableName='v_ReportServices'and ColName='__OPTIONS__'  ),GrNo,1);
  end if;
end for;