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_ElaborateReportServices','__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_ElaborateReportServices','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_ElaborateReportServices','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_ElaborateReportServices','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_ElaborateReportServices','InvoiceNo','Подробен отчет услуги','Сметка','Подробен отчет услуги','Сметка',2,GetFreeNInRelations('Подробен отчет услуги', Null, Null, 10),'InvoiceNo','','',0,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_ElaborateReportServices','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_ElaborateReportServices','Service','Подробен отчет услуги','Услуга','Подробен отчет услуги','Услуга',2,GetFreeNInRelations('Подробен отчет услуги', Null, Null, 10),'Service','','',0,101,'','','select distinct Service from v_ElaborateReportServices','',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_ElaborateReportServices','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_ElaborateReportServices','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_ElaborateReportServices','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_ElaborateReportServices','FullName','Подробен отчет услуги','Администратор','Подробен отчет услуги','Администратор',2,GetFreeNInRelations('Подробен отчет услуги', Null, Null, 10),'FullName','','',1,0,'','','','',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_ElaborateReportServices','OtchetDate','Подробен отчет услуги','Дата отчет','Подробен отчет услуги','Дата отчет',2,GetFreeNInRelations('Подробен отчет услуги', Null, Null, 10),'OtchetDate','','',1,101,'','','select OtchetDateDt from Otcheti','',90,'',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_ElaborateReportServices','OtchetNo','Подробен отчет услуги','Номер отчет','Подробен отчет услуги','Номер отчет',2,GetFreeNInRelations('Подробен отчет услуги', Null, Null, 10),'OtchetNo','','',1,101,'','','select OtchetNo from Otcheti','',90,'',0,0,1,today())

Go

delete from InformPermission where RelationN=(
select N from Relations where TableName='v_ElaborateReportServices'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_ElaborateReportServices'and ColName='__OPTIONS__'  ),GrNo,1);
  end if;
end for;

GO

IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_ElaborateReportServices')))
THEN Drop view v_ElaborateReportServices
END IF

GO

create view
  DBA.v_ElaborateReportServices as
select InvoiceNo=(select ExternalNo from DBA.Invoices as Inv1 where Inv1.InvoiceNo = Articles.InvoiceNo),
  InvN = Articles.InvoiceNo, 
    Service="left"(Articles.ArticleName,if charindex('в стая',Articles.ArticleName) = 0 then if charindex('за стая',Articles.ArticleName) = 0 then LENGTH(Articles.ArticleName)
      --Maria +, 1982
      --Maria -
      --Maria +, 1982
      --Maria -
      --Maria +, 1982
      --Maria -
      --Maria +, 1982
      --Maria -
      -- gnikolov - добавено съобразяване с регистър VTDCommonCounter поръчка 2008
      else charindex('за стая',Articles.ArticleName)-2
      endif else charindex('в стая',Articles.ArticleName)-2 endif),
    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),
    OtchetDate=Otcheti.OtchetDateDt,
    OtchetNo=Otcheti.OtchetNo,
    Fiscal=(if Invoices.Fiscal = 0 then 'Не' else 'Да' endif) from
    DBA.Articles,
    DBA.Otcheti,
    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.InvoiceNo >= Otcheti.FromInvoiceNo and Invoices.InvoiceNo <= Otcheti.ToInvoiceNo 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 InvoiceNo=(select ExternalNo from DBA.Invoices as Inv1 where Inv1.InvoiceNo = Articles.InvoiceNo),
  InvN = Articles.InvoiceNo, 
    Service="left"(Articles.ArticleName,if charindex('в стая',Articles.ArticleName) = 0 then if charindex('за стая',Articles.ArticleName) = 0 then LENGTH(Articles.ArticleName)
      else charindex('за стая',Articles.ArticleName)-2
      endif else charindex('в стая',Articles.ArticleName)-2 endif),
    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),
    OtchetDate=Otcheti.OtchetDateDt,
    OtchetNo=Otcheti.OtchetNo,
    Fiscal=(if Invoices.Fiscal = 0 then 'Не' else 'Да' endif) from
    DBA.Articles,
    DBA.Otcheti,
    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.InvoiceNo >= Otcheti.FromInvoiceNo and Invoices.InvoiceNo <= Otcheti.ToInvoiceNo 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=(select ExternalNo from DBA.Invoices as Inv1 where Inv1.InvoiceNo = max(Articles.InvoiceNo)),
  InvN = max(Articles.InvoiceNo), 
    Service=max("left"(Articles.ArticleName,if charindex('в стая',Articles.ArticleName) = 0 then if charindex('за стая',Articles.ArticleName) = 0 then LENGTH(Articles.ArticleName)
      else charindex('за стая',Articles.ArticleName)-2
      endif else charindex('в стая',Articles.ArticleName)-2 endif)),
    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),
    OtchetDate=Otcheti.OtchetDateDt,
    OtchetNo=Otcheti.OtchetNo,
    Fiscal=(if Invoices.Fiscal = 0 then 'Не' else 'Да' endif) from
    DBA.Articles,
    DBA.Otcheti,
    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.InvoiceNo >= Otcheti.FromInvoiceNo and Invoices.InvoiceNo <= Otcheti.ToInvoiceNo 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,Invoices.InvoiceNo,
    Articles.ArticleNo,
    Articles.Qty,
    Invoices.CancelInvoiceNo,Invoices.Fiscal,OtchetDate,OtchetNo union all
select InvoiceNo=(select ExternalNo from DBA.Invoices as Inv1 where Inv1.InvoiceNo = VA.InvoiceNo),
  InvN = 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),OtchetDate='01.01.01',
    OtchetNo=-1,
    Fiscal=(if VI.Fiscal = 0 then 'Не' else 'Да' endif) 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 and
    ((select IntStojnost from DBA.Registers where RegisterName = 'VTDCommonCounter') = 0)
	order by InvN, InvoiceDateDt
	
GO