-- Скрипт 0593
-- 1. Добавен документ - фактура на Английски
-- 2. Промени по смяната на езика при печат на документ
-- 3. Промяна на справки Начислени услуги по договор/Начислени услуго по собственици - да отразяват
--   услугите, начислени дирекно на договор
-- 4. Fix на анулиране на сметка - съобщението, че трябва първо да се анулира друг номер сметка
-- 5. Експорт към счетоводна система Ажур L5
-- 6. Експорт към счетоводна система Zeron
-- 7. Експорт към счетоводна система Work Flow

-- [1] -------------------------------------------------------------------------
if (select count(*) from VidSmetki where N=5) = 0 then
  insert into VidSmetki (N, SmetkaIme, KodCurrency, SmetkaFile, IsFaktura, SvoService, TextPrint, SlujSmetka, Language)
  values (5, 'Фактура англ.', 1, 'HotelFakturaEng.qri', 0, 6, 0, 0, 1);
end if;  

Go

-- [2] -------------------------------------------------------------------------

IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('ArticlesByVidSmetka')))
THEN Drop procedure ArticlesByVidSmetka
END IF

Go

create procedure DBA.ArticlesByVidSmetka(in @VidSmetka integer,in @ExchangeRate double)
// Ako se smeni vida na smetkata tazi procedura zapisva korektnite stojnosti
// i promenia imeto, ako e vuvedeno
begin
  declare @NewName varchar(40);
  declare @Lang integer;
  message 'ArticlesByVidSmetka 1' type info to console;
  select Language into @Lang from VidSmetki where n = @VidSmetka;
  for f as curs scroll cursor for
    select ArticleNumber as @ArticleNumber,ArticlePrice as @ArticlePrice,ArticleName as @ArticleName,
      ArticleSum as @ArticleSum,ServiceNo as @ServiceNo from #Articles do
    set @NewName=null;
    if((@Lang = 1) and(locate(@ArticleName,'Нощувка') > 0) and(locate(@ArticleName,'от') > 0) and
      (locate(@ArticleName,'до') > 0)) then
      if(locate(@ArticleName,'стая') > 0) then
        set @NewName='Lodging in room ' || 
          substr(@ArticleName,locate(@ArticleName,'стая')+5,locate(@ArticleName,' ',locate(@ArticleName,'стая')+5)-locate(@ArticleName,'стая')-5) || ' from ' || 
          substr(@ArticleName,locate(@ArticleName,'от')+3,locate(@ArticleName,' ',locate(@ArticleName,'от')+3)-locate(@ArticleName,'от')-3) || ' to ' || 
          substr(@ArticleName,locate(@ArticleName,'до')+3,20)
      end if;
      if(locate(@ArticleName,'апартамент') > 0) then
        set @NewName='Lodging in room ' || 
          substr(@ArticleName,locate(@ArticleName,'апартамент')+11,locate(@ArticleName,' ',locate(@ArticleName,'апартамент')+11)-locate(@ArticleName,'апартамент')-11) || ' from ' || 
          substr(@ArticleName,locate(@ArticleName,'от')+3,locate(@ArticleName,' ',locate(@ArticleName,'от')+3)-locate(@ArticleName,'от')-3) || ' to ' || 
          substr(@ArticleName,locate(@ArticleName,'до')+3,20)
      end if
    else
      if @Lang = 0 then
        set @NewName=@ArticleName
      else
        select SS.Stoinost into @NewName from VidSmetki as VS,SvoServicesStn as SS where
          VS.N = @VidSmetka and VS.SvoService = SS.Svoistvo and SS.Glava = @ServiceNo
      end if
    end if;
    if(@NewName is null) then
      set @NewName=@ArticleName
    end if;
    message 'ArticlesByVidSmetka step 1 @NewName=' || @NewName || ' @VidSmetka=' || @VidSmetka type info to console;
    if @Lang is null then
      set @Lang=0
    end if;
    message 'ArticlesByVidSmetka @Lang=' || @Lang type info to console;
    update #Articles set TranslateName = @NewName,PriceInvCurrency = @ArticlePrice/@ExchangeRate,
      ArticleSumInvCurrency = @ArticleSum/@ExchangeRate where ArticleNumber = @ArticleNumber end for
//    update #Articles set PriceInvCurrency=@ArticlePrice/@ExchangeRate,
//      ArticleSumInvCurrency=@ArticleSum/@ExchangeRate where ArticleNumber=@ArticleNumber end for
end
//[v. 28.02.2003  ] Jorko
//[v.2 19.Nov.2007] - Maria

Go

-- [3] -------------------------------------------------------------------------

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

GO

create view
  DBA.v_NonPayedServicesByContract( GNum,Guest,DateCredit,Service,Quantity,PrWithoutDisc,
  curKod,SumWithoutDisc,Disc,Discount,PrWithDisc,SumWithDisc,BgSumWithDisc,Dogovor,
  RoomNum,BgNonPayedServiceWithDisc,N) as
  select distinct
  --Maria,1628
    --GNum=g.GuestNum,
    GNum=cast(g.GuestNum as varchar),
  --Maria
    Guest=g.Name,
    DateCredit=b.DateRegDt,
    Service=(if b.IsRest = 0 then 'Нощувка в стая '+convert(varchar,b.Room) else 'Почивка в стая '+convert(varchar,b.Room) endif),
    Quantity=b.NumLodgings,
    PrWithoutDisc=b.Price,
    curKod=cur.Symbol,
    SumWithoutDisc=Quantity*PrWithoutDisc,
    Disc=scr.Discount,
    Discount=scr.Discount,
    PrWithDisc=DBA.CalcPriceWithDiscount(Discount,PrWithoutDisc),
    SumWithDisc=Quantity*PrWithDisc,
    BGSumWithDisc=SumWithDisc*DBA.GetCurrencyRate(DateCredit,cur.KodCurrency),
    Dogovor=c.FullName,
  --Maria, 1628
    --RoomNum=b.Room,
    RoomNum=cast(b.Room as varchar),
  --Maria
    BgNonPayedServiceWithDisc=SumWithDisc-(b.PayedLodgings*PrWithDisc)*DBA.GetCurrencyRate(DateCredit,cur.KodCurrency),
    N=b.Regnum 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 and
    c.GroupNo = 2 union all
  /* Contracts.GroupNo=2 (група Собственици) по искане на Кети */
  /* Внимание ! Не всички бази имат тази група или е възможно номера на групата да е различен !*/
  select distinct
  --Maria,1628
    --GNum=g.GuestNum,
    GNum=cast(g.GuestNum as varchar),
  --Maria
    Guest=g.Name,
    DateCredit=sc.DateCreditDt,
    s.ServiceName,
    Quantity=(select Sum(NumberServices) from dba.ServiceCredits where
      Service = s.ServiceNo and GuestNo = g.GuestNum and
      KodCurrency = cur.KodCurrency and
      CreditNo = sc.CreditNo),
    PrWithoutDisc=sc.SumCredit,
    curKod=cur.Symbol,
    SumWithoutDisc=(Quantity*PrWithoutDisc),
    Disc=(select Discount from dba.ServicesForContract where ServiceNo = s.ServiceNo and ContractNo = c.ContractNo),
    Discount=if Disc is null then 0 else Disc endif,
    PrWithDisc=DBA.CalcPriceWithDiscount(Discount,PrWithoutDisc),
    SumWithDisc=Quantity*PrWithDisc,
    BGSumWithDisc=SumWithDisc*DBA.GetCurrencyRate(DateCredit,cur.KodCurrency),
    Dogovor=c.FullName,
  --Maria, 1628
    --sc.RoomNum,
    cast(sc.RoomNum as varchar),
  --Maria
    BgNonPayedServiceWithDisc=SumWithDisc-(sc.PayedServices*PrWithDisc)*DBA.GetCurrencyRate(DateCredit,cur.KodCurrency),
    N=sc.CreditNo 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
    c.GroupNo = 2 and
    g.GuestNum <> 0
/* Contracts.GroupNo=2 - виж по-горе */
--Maria, 1628
  union all
  select distinct
    GNum='',
    Guest='',
    DateCredit=sc.DateCreditDt,
    s.ServiceName,
    Quantity=(select Sum(NumberServices) from dba.ServiceCredits where
      Service = s.ServiceNo and
      KodCurrency = cur.KodCurrency and
      CreditNo = sc.CreditNo),
    PrWithoutDisc=sc.SumCredit,
    curKod=cur.Symbol,
    SumWithoutDisc=(Quantity*PrWithoutDisc),
    Disc=(select Discount from dba.ServicesForContract where ServiceNo = s.ServiceNo and ContractNo = c.ContractNo),
    Discount=if Disc is null then 0 else Disc endif,
    PrWithDisc=DBA.CalcPriceWithDiscount(Discount,PrWithoutDisc),
    SumWithDisc=Quantity*PrWithDisc,
    BGSumWithDisc=SumWithDisc*DBA.GetCurrencyRate(DateCredit,cur.KodCurrency),
    Dogovor=c.FullName,
    RoomNum='',
    BgNonPayedServiceWithDisc=SumWithDisc-(sc.PayedServices*PrWithDisc)*DBA.GetCurrencyRate(DateCredit,cur.KodCurrency),
    N=sc.CreditNo from
    dba.ServiceCredits as sc,dba.Contracts as c,dba.Services as s,dba.Currencies as cur where
    sc.ContractPays = c.ContractNo and
    sc.DateCreditDt between bvrData1 and bvrData2 and
    sc.GuestNo = 0 and
    sc.ContractPays <> 0 and
    s.ServiceNo = sc.Service and
    sc.KodCurrency = cur.KodCurrency and
    c.GroupNo = 2
// v.1 []
// v.2 [19.Nov.2007] - Maria, usluga po dogovor

Go

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

GO

create view
  DBA.v_UsedServicesByContract( GNum,Guest,DateCredit,Service,Quantity,PrWithoutDisc,
  curKod,SumWithoutDisc,Disc,Discount,PrWithDisc,SumWithDisc,BgSumWithDisc,Dogovor,
  RoomNum,BgPayedServiceWhitDisc,N) as
  select distinct
  --Maria,1628
    --GNum=g.GuestNum,
    GNum=cast(g.GuestNum as varchar),
  --Maria
    Guest=g.Name,
    DateCredit=b.DateRegDt,
    Service=(if b.IsRest = 0 then 'Нощувка в стая '+convert(varchar,b.Room) else 'Почивка в стая '+convert(varchar,b.Room) endif),
    Quantity=b.NumLodgings,
    PrWithoutDisc=b.Price,
    curKod=cur.Symbol,
    SumWithoutDisc=Quantity*PrWithoutDisc,
    Disc=scr.Discount,
    Discount=scr.Discount,
    PrWithDisc=DBA.CalcPriceWithDiscount(Discount,PrWithoutDisc),
    SumWithDisc=Quantity*PrWithDisc,
    BGSumWithDisc=SumWithDisc*DBA.GetCurrencyRate(DateCredit,cur.KodCurrency),
    Dogovor=c.FullName,
  --Maria, 1628
    --RoomNum=b.Room,
    RoomNum=cast(b.Room as varchar),
  --Maria
    BgPayedServiceWhitDisc=(if b.PayedLodgings = 0 then 0 else((b.PayedLodgings*PrWithDisc)*DBA.GetCurrencyRate(DateCredit,cur.KodCurrency)) endif),
    N=b.Regnum 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
  --Maria,1628
    --GNum=g.GuestNum,
    GNum=cast(g.GuestNum as varchar),
  --Maria
    Guest=g.Name,
    DateCredit=sc.DateCreditDt,
    s.ServiceName,
    Quantity=(select Sum(NumberServices) from dba.ServiceCredits where
      Service = s.ServiceNo and GuestNo = g.GuestNum and
      KodCurrency = cur.KodCurrency and
      CreditNo = sc.CreditNo),
    PrWithoutDisc=sc.SumCredit,
    curKod=cur.Symbol,
    SumWithoutDisc=(Quantity*PrWithoutDisc),
    Disc=(select Discount from dba.ServicesForContract where ServiceNo = s.ServiceNo and ContractNo = c.ContractNo),
    Discount=if Disc is null then 0 else Disc endif,
    PrWithDisc=DBA.CalcPriceWithDiscount(Discount,PrWithoutDisc),
    SumWithDisc=Quantity*PrWithDisc,
    BGSumWithDisc=SumWithDisc*DBA.GetCurrencyRate(DateCredit,cur.KodCurrency),
    Dogovor=c.FullName,
  --Maria, 1628
    --sc.RoomNum,
    cast(sc.RoomNum as varchar),
  --Maria
    BgPayedServiceWhitDisc=(if sc.PayedServices = 0 then 0 else((sc.PayedServices*PrWithDisc)*DBA.GetCurrencyRate(DateCredit,cur.KodCurrency)) endif),
    N=sc.CreditNo 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
--Maria, 1628
    union all
  select distinct GNum = '',
    Guest='',
    DateCredit=sc.DateCreditDt,
    s.ServiceName,
    Quantity=(select Sum(NumberServices) from dba.ServiceCredits where
      Service = s.ServiceNo and
      KodCurrency = cur.KodCurrency and
      CreditNo = sc.CreditNo),
    PrWithoutDisc=sc.SumCredit,
    curKod=cur.Symbol,
    SumWithoutDisc=(Quantity*PrWithoutDisc),
    Disc=(select Discount from dba.ServicesForContract where ServiceNo = s.ServiceNo and ContractNo = c.ContractNo),
    Discount=if Disc is null then 0 else Disc endif,
    PrWithDisc=DBA.CalcPriceWithDiscount(Discount,PrWithoutDisc),
    SumWithDisc=Quantity*PrWithDisc,
    BGSumWithDisc=SumWithDisc*DBA.GetCurrencyRate(DateCredit,cur.KodCurrency),
    Dogovor=c.FullName,
    RoomNum= '',
    BgPayedServiceWhitDisc=(if sc.PayedServices = 0 then 0 else((sc.PayedServices*PrWithDisc)*DBA.GetCurrencyRate(DateCredit,cur.KodCurrency)) endif),
    N=sc.CreditNo from
    dba.ServiceCredits as sc,dba.Contracts as c,dba.Services as s,dba.Currencies as cur where
    sc.ContractPays = c.ContractNo and
    sc.DateCreditDt between bvrData1 and bvrData2 and
    sc.GuestNo = 0 and
    sc.ContractPays <> 0 and
    s.ServiceNo = sc.Service and
    sc.KodCurrency = cur.KodCurrency
// v.1 []
// v.2 [19.Nov.2007] - Maria, usluga po dogovor

Go

-- [4] -------------------------------------------------------------------------

IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('PreparePrintAgain')))
THEN Drop procedure PreparePrintAgain
END IF

GO

create procedure
DBA.PreparePrintAgain(in @SmetkaNo integer,in @OkCancel integer)
// Tazi procedura se izpolzva za napylwane na wremennata tablica #Articles
// sus articulite ot opredelena smetka pri owtoren pechat na smetka ili Anulirane na smetka
// kato ako e PrintAgain @OkCancel e 1, a pri Cancel -1
begin
  //Iztriva wremennata tablica za artikulite
  delete from #Articles;
  //Iztriva wremennata tablica za nacinite na plashtane
  delete from #PaymentTypes;
  //Popylva wremennata tablica za artikulite
  //@ArticlePrWithDDS,@ArticlePrWithDDS*@Qty
  for f0 as curs0 scroll cursor for
    select ArticleNo as @ArticleNumber,ArticleName as @ArticleName,Qty as @ArticleQty,Price as @ArticlePrice,
      RegNo as @RegNo,CreditNo as @CreditNo,TotalSum as @ArticleSum,Discount as @Discount,
      TipDDS as @TipDDS,ServiceNo as @Service,Price as @ArticlePrWithDDS,TotalSum as @TotalSumWithDDS,
      RegDate as @RDate from
      Articles where InvoiceNo = @SmetkaNo do
    insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,RegNo,CreditNo,
      ArticleSum,Discount,TipDDS,ServiceNo,PriceInvWithDDS,ArticleSumWithDDS,RegDate) values( 
      @ArticleNumber,@ArticleName,'a?.',@ArticleQty,@ArticleQty,@ArticleQty,
      @OkCancel*@ArticlePrice,@RegNo,@CreditNo,@OkCancel*@ArticleSum,@Discount,@TipDDS,@Service,@ArticlePrWithDDS,@TotalSumWithDDS,@RDate) end for;
  //Popylva wremennata tablica za nachinite na plashtane
  for f1 as curs1 scroll cursor for
    select RM.Sum as @Sum,RM.Currency as @KodCurrency,RM.Payment as @PaymentType,Cr.Symbol as @Symbol,
      RM.AccountNumber as @AccountNumber,RM.GuestNo as @GuestNo,RM.GroupNo as @GroupNo,RM.RoomNo as @RoomNo,RM.ReserveNo as @ReserveNo,
      RM.PTVid as @PTVid from
      DBA.ResMoney as RM,DBA.Currencies as Cr where
      RM.InvoiceNo = @SmetkaNo and
      RM.DateMoney <> 0 and
      RM.Currency = Cr.KodCurrency do
    insert into #PaymentTypes( PaymentType,Sum,KodCurrency,AccountNumber,Symbol,GuestNo,GroupNo,RoomNo,ReserveNo,PTVid) values( 
      @PaymentType,@OkCancel*@Sum,@KodCurrency,@AccountNumber,@Symbol,@GuestNo,@GroupNo,@RoomNo,@ReserveNo,@PTVid) end for
end
//[v.1 00.11.16]
//[v.2 18.02.2003] Jorko
//[v.3 19.Nov.2007] - A.Kozludjov, fix pri anulirane na smetka

Go

-- [5] -------------------------------------------------------------------------

if (Select Count(*) from "DBA".PermissionTypes where Name='Експорт към Ажур L5') = 0 then
  Create variable Tag_ID integer;
  Set Tag_ID = 5600;
  Delete from "DBA".PermissionTypes Where N=Tag_ID;
  Insert Into "DBA".PermissionTypes Values(Tag_ID,'Експорт към Ажур L5',1);
  Delete from "DBA".PermissionTags Where TagNo=Tag_ID;
  Insert Into "DBA".PermissionTags Values(Tag_ID,Tag_ID,'Експорт към Ажур L5');
  Delete from "DBA".Permissions Where PermissionNo=Tag_ID;
  for f1 as curs scroll cursor for Select N from AdminGroups do
    -- Admin ima prava
    if N=1 then Insert Into "DBA".Permissions Values(N,Tag_ID,0)
      -- ostanalite grupi niamat prava po podrazbirane
      else Insert Into "DBA".Permissions Values(N,Tag_ID,1)
    end if;
  end for;
  Drop variable Tag_ID;
end if;

Go

if exists (select * from systable where lcase(table_name)=lcase('ExportSchetAjourRedoveWinH')) then
  drop view ExportSchetAjourRedoveWinH
end if;

Go

create view ExportSchetAjourRedoveWinH as
select distinct
  -- redove za fakturi ot Invoices:
  InvN = I.InvoiceNo,
  PayType = I.PayHow,
  InvNo = I.MaskaFaktura || AlignRightStr(convert(integer,I.TrueInvoiceNo),11,'0'),
  InvDate = DATEFORMAT(I.TrueInvoiceDate,'DD.MM.YYYY'),
  InvoiceValue = FormatNum(I.TotalNoVat, '0.0000'),
  ValueDDS = FormatNum(I.Vat, '0.0000'),
  ValueAll = FormatNum(I.GrandTotal, '0.0000'),
  PndReg = AlignLeftStr(convert(varchar(10),I.TrueInvoiceNo),9,' '),
  DocKind = if I.CancelInvoiceNo > 0 then 'КИ  ' else 'Ф-ра' endif,
  DocKindKod = if I.CancelInvoiceNo > 0 then '3' else '1' endif,
  Partnior = I.Whom,
  PartDanNo = I.WhomDanNum,
  PartBulstat = if (left(I.Bulstat,1) in ('0','1','2','3','4','5','6','7','8','9')) then I.Bulstat else right(I.Bulstat,(Length(I.Bulstat)+1 - patindex('%[0,1,2,3,4,5,6,7,8,9]%',I.Bulstat)))endif,
  PartKtInfo = AlignRightStr(I.ContractNo,7,'0'),
  IsVTD = 0
from
  Invoices as I,
  Articles as A
where
  I.InvoiceNo = A.InvoiceNo
  and I.TrueInvoiceNo <> 0
  and I.TrueInvoiceDate >= bvrData1
  and I.TrueInvoiceDate <= bvrData2
union all
select distinct
  -- redove za invoices ot VTD:
  InvN = I.VaucherInvoiceNo,
  PayType = I.PayHow,
  InvNo = I.MaskaFaktura || AlignRightStr(convert(integer,I.TrueInvoiceNo),11,'0'),
  InvDate = DATEFORMAT(I.TrueInvoiceDate,'DD.MM.YYYY'),
  InvoiceValue = FormatNum(I.GrandTotal * C.ExchangeRate, '0.0000'),
  ValueDDS = FormatNum(I.Vat * C.ExchangeRate, '0.0000'),
  ValueAll = FormatNum(I.TotalNoVat * C.ExchangeRate, '0.0000'),
  PndReg = AlignLeftStr(convert(varchar(10),I.TrueInvoiceNo),9,' '),
  DocKind = if I.CancelInvoiceNo > 0 then 'КИ  ' else 'Ф-ра' endif,
  DocKindKod = if I.CancelInvoiceNo > 0 then '3' else '1' endif,
  Partnior = I.Whom,
  PartDanNo = I.WhomDanNum,
  PartBulstat = if (left(I.Bulstat,1) in ('0','1','2','3','4','5','6','7','8','9')) then I.Bulstat else right(I.Bulstat,(Length(I.Bulstat)+1 - patindex('%[0,1,2,3,4,5,6,7,8,9]%',I.Bulstat)))endif,
  PartKtInfo = AlignRightStr(I.ContractNo,7,'0'),
  IsVTD = 1
from
  VaucherInvoices as I,
  VaucherArticles as A,
  Currencies as C
where
  I.VaucherInvoiceNo = A.InvoiceNo
  and C.KodCurrency = I.KodCurrency
  and I.TrueInvoiceNo <> 0
  and I.TrueInvoiceDate >= bvrData1
  and I.TrueInvoiceDate <= bvrData2
//v.1 [06.Nov.2007]

Go

if exists (select * from systable where lcase(table_name)=lcase('ExportAjurWinH'))
then drop view ExportAjurWinH end if

Go

create view ExportAjurWinH as
select
  InvN,
  NPole = 1,
  ESARW.InvDate,
  PartKt = '0',
  Pole = 'ОПДДС@@'||ESARW.InvNo||'#'||ESARW.InvDate||'#'||'        '||'#'||'202'||'#'||'#'||'987 '||'#'
    ||"Date"(bvrData2)||'#'||ESARW.DocKind||'#'||'   '||'#'||'   '||'#'||'   '||'#'||'   '||'#'||'  @@4111@@'
    ||ESARW.PartKtInfo||'$'||ESARW.Partnior||'$'||ESARW.PartBulstat||'$'||ESARW.PartDanNo||'#'||ESARW.InvNo||'#'
    ||ESARW.InvDate||'@@4532@@@@'||TRIM(ESARW.ValueDDS)||'#'||'0.000'||'#'||'0.000'||'#'||'0.000'||'#'||'0.000'||'#'||'0.000'||'#'||'0.000@@'
from 
  ExportSchetAjourRedoveWinH as ESARW

union all

select
  -- redove ot Invoices:
  InvN,
  NPole = 2,
  InvDate = Max(ESARW.InvDate),
  PartKt = IsNull(cast((select Stoinost from SvoServicesStn where Glava = A.ServiceNo and Svoistvo = 1) as Varchar), '7021'),
  Pole = 'ОПДДС@@'||Max(ESARW.InvNo)||'#'||Max(ESARW.InvDate)||'#'||'        '||'#'||'202'||'#'||'#'
    ||'987 '||'#'||"Date"(bvrData2)||'#'||Max(ESARW.DocKind)||'#'||'   '||'#'||'   '||'#'||'   '||'#'||'   '
    ||'#'||'  @@4111@@'||Max(ESARW.PartKtInfo)||'$'||Max(ESARW.Partnior)||'$'||Max(ESARW.PartBulstat)||'$'
    ||Max(ESARW.PartDanNo)||'#'||Max(ESARW.InvNo)||'#'||Max(ESARW.InvDate)||'@@'||PartKt
    ||'@@@@'||FormatNum(Sum(A.TotalSum), '0.0000')||'#'||'0.000'||'#'||'0.000'||'#'||'0.000'||'#'||'0.000'||'#'
    ||'0.000'||'#'||'0.000@@'
from 
  ExportSchetAjourRedoveWinH as ESARW, Articles as A
where
  A.InvoiceNo = ESARW.InvN
  and ESARW.IsVTD = 0
group by
  InvN, PartKt

union all

select
  -- redove ot VaucherInvoices:
  InvN,
  NPole = 2,
  InvDate = Max(ESARW.InvDate),
  PartKt = IsNull(cast((select Stoinost from SvoServicesStn where Glava = A.ServiceNo and Svoistvo = 1) as Varchar), '7021'),
  Pole = 'ОПДДС@@'||Max(ESARW.InvNo)||'#'||Max(ESARW.InvDate)||'#'||'        '||'#'||'202'||'#'||'#'
    ||'987 '||'#'||"Date"(bvrData2)||'#'||Max(ESARW.DocKind)||'#'||'   '||'#'||'   '||'#'||'   '||'#'||'   '
    ||'#'||'  @@4111@@'||Max(ESARW.PartKtInfo)||'$'||Max(ESARW.Partnior)||'$'||Max(ESARW.PartBulstat)||'$'
    ||Max(ESARW.PartDanNo)||'#'||Max(ESARW.InvNo)||'#'||Max(ESARW.InvDate)||'@@'||PartKt
    ||'@@@@'||FormatNum(Sum(A.GrandTotal * (select ExchangeRate from Currencies where KodCurrency = A.Currency)), '0.0000')
    ||'#'||'0.000'||'#'||'0.000'||'#'||'0.000'||'#'||'0.000'||'#'||'0.000'||'#'||'0.000@@'
from
  ExportSchetAjourRedoveWinH as ESARW, VaucherArticles as A
where
  A.InvoiceNo = ESARW.InvN
  and ESARW.IsVTD = 1
group by
  InvN, PartKt

union all

select distinct
  -- red za smetka 501 (v broi)
  InvN,
  NPole=3,
  InvDate = Max(ESARW.InvDate),
  PartKt = '0',
  Pole = 'ОПДДС@@'||Max(ESARW.InvNo)||'#'||Max(ESARW.InvDate)||'#'||'        '||'#'||'202'||'#'||'#'||'987 '||'#'
    ||"Date"(bvrData2)||'#'||Max(ESARW.DocKind)||'#'||'   '||'#'||'   '||'#'||'   '||'#'||'   '||'#'||'  @@5011@@@@4111@@'
    ||Max(ESARW.PartKtInfo)||'$'||Max(TRIM(ESARW.Partnior))||'$'||Max(TRIM(ESARW.PartBulstat))||'$'
    ||Max(TRIM(ESARW.PartDanNo))||'#'||Max(ESARW.InvNo)||'#'||Max(ESARW.InvDate)||'@@'
    ||FormatNum(Sum(M.Sum * C.ExchangeRate), '0.0000')||'#'||'0.000'||'#'||'0.000'||'#'||'0.000'||'#'||'0.000'||'#'
    ||'0.000'||'#'||'0.000@@'
from
  ExportSchetAjourRedoveWinH as ESARW, ResMoney as M, Currencies as C
where
  ((ESARW.IsVTD = 0 and M.InvoiceNo = ESARW.InvN) or (ESARW.IsVTD = 1 and M.VaucherInvoiceNo = ESARW.InvN))
  and M.Currency = C.KodCurrency
  and M.Payment = 1
group by
  M.Payment, InvN, PartKt

union all

select
  InvN,
  NPole=4,
  ESARW.InvDate,
  PartKt = '0',
  Pole='ДДСПР@@'||ESARW.InvNo||'#'||ESARW.InvDate||'###'||ESARW.DocKindKod||'#'||'0'||'#'||'Продажба на стоки@@'
    ||ESARW.PartKtInfo||'#'||TRIM(ESARW.Partnior)||'#'||TRIM(ESARW.PartBulstat)||'#'||TRIM(ESARW.PartDanNo)||'@@'
    ||TRIM(ESARW.InvoiceValue)||'#'||TRIM(ESARW.ValueDDS)||'#'||'0'||'#'||'0'||'#'||'0'||'#'||'0'||'#'||'0'||'#'
    ||'0'||'#'||'0'||'#'||'0'||'#'||'0'||'#'||'0'||'#'||'0'||'#'||'0'||'#'||'0'||'#'||'0'||'#'||'0'||'#'||'0'||'#'
    ||'0'||'#'||'0'||'@@'
from
  ExportSchetAjourRedoveWinH as ESARW

ORDER BY 3, 1, 2
//v.1 [06.Nov.2007]  

Go

-- [6] -------------------------------------------------------------------------

if (Select Count(*) from "DBA".PermissionTypes where Name='Експорт към Zeron') = 0 then
  Create variable Tag_ID integer;
  Set Tag_ID = 5605;
  Delete from "DBA".PermissionTypes Where N=Tag_ID;
  Insert Into "DBA".PermissionTypes Values(Tag_ID,'Експорт към Zeron',1);
  Delete from "DBA".PermissionTags Where TagNo=Tag_ID;
  Insert Into "DBA".PermissionTags Values(Tag_ID,Tag_ID,'Експорт към Zeron');
  Delete from "DBA".Permissions Where PermissionNo=Tag_ID;
  for f1 as curs scroll cursor for Select N from AdminGroups do
    -- Admin ima prava
    if N=1 then Insert Into "DBA".Permissions Values(N,Tag_ID,0)
      -- ostanalite grupi niamat prava po podrazbirane
      else Insert Into "DBA".Permissions Values(N,Tag_ID,1)
    end if;
  end for;
  Drop variable Tag_ID;
end if;

Go

IF (EXISTS(Select * from systable where LCase(table_name) = LCase('ExportZeronH')))
  THEN Drop View ExportZeronH
END IF

Go

Create View ExportZeronH as
select
  N = 1,
  InvoiceNo = I.InvoiceNo,
  InvoiceDate = I.TrueInvoiceDate,
  InvoiceType = 'HOTEL',
  Pole = I.TrueInvoiceDate || Char(9) || cast(if I.MaskaFaktura = 0 then I.TrueInvoiceNo else
    I.MaskaFaktura || AlignRightStr(convert(integer,I.TrueInvoiceNo),11,'0') endif as Integer)
    || Char(9) || '1' || Char(9)
    || if I.WithoutVAT <> 0 then '0' else '1' endif || Char(9) || 'c' || Char(9) || '702/1'
    || Char(9) || FormatNum(I.TotalNoVat * I.ExchangeRate, '0.00') || Char(9) || 'Продажба хотелски услуги'
    || Char(9) || Trim(I.Whom) || Char(9) || I.Bulstat || Char(9) || Trim(I.MOL) || Char(9)
    || Trim(I.WhomTown)
from
  Invoices as I
where
  I.TrueInvoiceDate >= bvrData1
  and I.TrueInvoiceDate <= bvrData2
  and I.TrueInvoiceNo <> 0
union all
select
  N = 2,
  InvoiceNo = I.InvoiceNo,
  InvoiceDate = I.TrueInvoiceDate,
  InvoiceType = 'HOTEL',
  Pole = I.TrueInvoiceDate || Char(9) || cast(if I.MaskaFaktura = 0 then I.TrueInvoiceNo else
    I.MaskaFaktura || AlignRightStr(convert(integer,I.TrueInvoiceNo),11,'0') endif as Integer)
    || Char(9) || '1' || Char(9)
    || if I.WithoutVAT <> 0 then '0' else '1' endif || Char(9) || 'c' || Char(9) || '453/1'
    || Char(9) || FormatNum(I.Vat * I.ExchangeRate, '0.00') || Char(9) || 'ДДС Продажби' || Char(9) || Trim(I.Whom)
    || Char(9) || I.Bulstat || Char(9) || Trim(I.MOL) || Char(9) || Trim(I.WhomTown)
from
  Invoices as I
where
  I.TrueInvoiceDate >= bvrData1
  and I.TrueInvoiceDate <= bvrData2
  and I.TrueInvoiceNo <> 0
union all
select
  N = 3,
  InvoiceNo = I.InvoiceNo,
  InvoiceDate = I.TrueInvoiceDate,
  InvoiceType = 'HOTEL',
  Pole = I.TrueInvoiceDate || Char(9) || cast(if I.MaskaFaktura = 0 then I.TrueInvoiceNo else
    I.MaskaFaktura || AlignRightStr(convert(integer,I.TrueInvoiceNo),11,'0') endif as Integer)
    || Char(9) || '1' || Char(9)
    || if I.WithoutVAT <> 0 then '0' else '1' endif || Char(9) || 'd' || Char(9) || '402/1'
    || Char(9) || FormatNum(I.GrandTotal * I.ExchangeRate, '0.00') || Char(9) || Trim(I.Whom) || Char(9) || Trim(I.Whom)
    || Char(9) || I.Bulstat || Char(9) || Trim(I.MOL) || Char(9) || Trim(I.WhomTown)
from
  Invoices as I
where
  I.TrueInvoiceDate >= bvrData1
  and I.TrueInvoiceDate <= bvrData2
  and I.TrueInvoiceNo <> 0
union all
select
  N = 1,
  InvoiceNo = I.VaucherInvoiceNo,
  InvoiceDate = I.TrueInvoiceDate,
  InvoiceType = 'VTD',
  Pole = I.TrueInvoiceDate || Char(9) || cast(if I.MaskaFaktura = 0 then I.TrueInvoiceNo else
    I.MaskaFaktura || AlignRightStr(convert(integer,I.TrueInvoiceNo),11,'0') endif as Integer)
    || Char(9) || '1' || Char(9)
    || if I.WithoutVAT <> 0 then '0' else '1' endif || Char(9) || 'c' || Char(9) || '702/1'
    || Char(9) || FormatNum(I.GrandTotal * I.ExchangeRate, '0.00') || Char(9) || 'Продажба туристически услуги'
    || Char(9) || Trim(I.Whom) || Char(9) || I.Bulstat || Char(9) || Trim(I.MOL) || Char(9)
    || Trim(I.WhomTown)
from
  VaucherInvoices as I
where
  I.TrueInvoiceDate >= bvrData1
  and I.TrueInvoiceDate <= bvrData2
  and I.TrueInvoiceNo <> 0
union all
select
  N = 2,
  InvoiceNo = I.VaucherInvoiceNo,
  InvoiceDate = I.TrueInvoiceDate,
  InvoiceType = 'VTD',
  Pole = I.TrueInvoiceDate || Char(9) || cast(if I.MaskaFaktura = 0 then I.TrueInvoiceNo else
    I.MaskaFaktura || AlignRightStr(convert(integer,I.TrueInvoiceNo),11,'0') endif as Integer)
    || Char(9) || '1' || Char(9)
    || if I.WithoutVAT <> 0 then '0' else '1' endif || Char(9) || 'c' || Char(9) || '453/1'
    || Char(9) || FormatNum(I.Vat * I.ExchangeRate, '0.00') || Char(9) || 'ДДС Продажби' || Char(9) || Trim(I.Whom)
    || Char(9) || I.Bulstat || Char(9) || Trim(I.MOL) || Char(9) || Trim(I.WhomTown)
from
  VaucherInvoices as I
where
  I.TrueInvoiceDate >= bvrData1
  and I.TrueInvoiceDate <= bvrData2
  and I.TrueInvoiceNo <> 0
union all
select
  N = 3,
  InvoiceNo = I.VaucherInvoiceNo,
  InvoiceDate = I.TrueInvoiceDate,
  InvoiceType = 'VTD',
  Pole = I.TrueInvoiceDate || Char(9) || cast(if I.MaskaFaktura = 0 then I.TrueInvoiceNo else
    I.MaskaFaktura || AlignRightStr(convert(integer,I.TrueInvoiceNo),11,'0') endif as Integer)
    || Char(9) || '1' || Char(9)
    || if I.WithoutVAT <> 0 then '0' else '1' endif || Char(9) || 'd' || Char(9) || '402/1'
    || Char(9) || FormatNum(I.TotalNoVat * I.ExchangeRate, '0.00') || Char(9) || Trim(I.Whom) || Char(9) || Trim(I.Whom)
    || Char(9) || I.Bulstat || Char(9) || Trim(I.MOL) || Char(9) || Trim(I.WhomTown)
from
  VaucherInvoices as I
where
  I.TrueInvoiceDate >= bvrData1
  and I.TrueInvoiceDate <= bvrData2
  and I.TrueInvoiceNo <> 0
ORDER BY 4, 3, 2, 1
//v.1 [06.Nov.2007]

Go

-- [7] -------------------------------------------------------------------------

if (Select Count(*) from "DBA".PermissionTypes where Name='Експорт към Work Flow') = 0 then
  Create variable Tag_ID integer;
  Set Tag_ID = 5610;
  Delete from "DBA".PermissionTypes Where N=Tag_ID;
  Insert Into "DBA".PermissionTypes Values(Tag_ID,'Експорт към Work Flow',1);
  Delete from "DBA".PermissionTags Where TagNo=Tag_ID;
  Insert Into "DBA".PermissionTags Values(Tag_ID,Tag_ID,'Експорт към Work Flow');
  Delete from "DBA".Permissions Where PermissionNo=Tag_ID;
  for f1 as curs scroll cursor for Select N from AdminGroups do
    -- Admin ima prava
    if N=1 then Insert Into "DBA".Permissions Values(N,Tag_ID,0)
      -- ostanalite grupi niamat prava po podrazbirane
      else Insert Into "DBA".Permissions Values(N,Tag_ID,1)
    end if;
  end for;
  Drop variable Tag_ID;
end if;

Go

if exists (select * from systable where lcase(table_name)=lcase('v_ExportWorkFlowFakturi'))
  then drop view v_ExportWorkFlowFakturi end if

go

create view
  v_ExportWorkFlowFakturi
as
select
    N=I.InvoiceNo,
    RowOrder=1,
    VidFaktura=I.VidSmetka,
    DESCR = 'продажбa',
    PAYTYPE = (Select PayTypeName from PayTypes where PayTypeN = I.PayHow),
    DDSIN = I.WhomDanNum,
    BULSTAT = if (left(I.Bulstat,1) in ('0','1','2','3','4','5','6','7','8','9')) then I.Bulstat else right(I.Bulstat,(Length(I.Bulstat)+1 - patindex('%[0,1,2,3,4,5,6,7,8,9]%',I.Bulstat))) endif,
    NAME = I.Whom,
    TOTALNOVAT = Convert(Varchar(10),FormatNum(Round(Abs(I.TotalNoVat),4),'0.0000')),
    VAT = Convert(Varchar(10),FormatNum(Round(Abs(I.VAT),4),'0.0000')),
    TOTAL = Convert(Varchar(10),FormatNum(Round(Abs(I.GrandTotal),4),'0.0000')),
    INVOICENUM = I.MaskaFaktura || AlignRightStr(Convert(Varchar(9), I.TrueInvoiceNo),9,0),
    IDATE = Convert(Varchar(10),DATEFORMAT(I.TrueInvoiceDate,'DD.MM.YYYY')),
    FNAME = (select first Stoinost from HotelOptions where OptionName = 'FirmName'),
    WHNAME = (select first Stoinost from HotelOptions where OptionName = 'HotelName'),
    MOLNAME = (select first Stoinost from HotelOptions where OptionName = 'FirmMOL')
from Invoices as I
where
    I.TrueInvoiceNo <> 0
    and I.TrueInvoiceDate >= bvrData1
    and I.TrueInvoiceDate <= bvrData2
//v.1 [07.Nov.2007] Yanko

go

if exists (select * from SYSPROCEDURE where lcase(proc_name)=lcase('FillworkFlowExportData'))
  then drop procedure FillworkFlowExportData end if

go

create procedure FillworkFlowExportData()
begin
  Delete from #TempData;
  Insert Into #TempData(D0, D1, D2, D3)
(select
  N,
  RowOrder,
  Kind=VidFaktura,
  ExpLine=CAST(('F'||Char(9)||Convert(Varchar(10),N)||Char(9)||DESCR||Char(9)||PAYTYPE||Char(9)||DDSIN||Char(9)||BULSTAT||Char(9)||
  NAME||Char(9)||TOTALNOVAT||Char(9)||VAT||Char(9)||TOTAL||Char(9)||INVOICENUM||Char(9)||IDATE||Char(9)||
  FNAME||Char(9)||WHNAME||Char(9)||MOLNAME) AS VARCHAR(255))
  from v_ExportWorkFlowFakturi
union all
select
  N = WFF.N,
  RowOrder = 2,
  Kind = WFF.VidFaktura,
  ExpLine = CAST(('U'||Char(9)||Convert(Varchar(10),N)||Char(9)||S.ServiceName||Char(9)||
  'бр'||Char(9)||Convert(Varchar(10),FormatNum(Round(Abs(A.Qty),4),'0.##'))||Char(9)||
  Convert(Varchar(10),FormatNum(Round(if A.TipDDS=1 then A.Price/(1 + VR.Rate) else A.Price endif,4),'0.0000'))||
  if C.KodCurrency <> 1 then C.Symbol else '' endif||Char(9)||
  Convert(Varchar(10),FormatNum(Round(Abs(A.Qty * (1 + A.Discount/100) * (if A.TipDDS=1 then A.Price/(1 + VR.Rate) else A.Price endif)),4),'0.0000'))||Char(9)||
  'Основание за осчетоводяване: '||Char(9)||Char(9)||Char(9)||Char(9)||Char(9)||Char(9)||Char(9)||Char(9)) AS VARCHAR(255))
from v_ExportWorkFlowFakturi as WFF, Articles as A, Services as S, Currencies as C, VatRates as VR
where
  A.InvoiceNo = WFF.N
  and A.ServiceNo = S.ServiceNo
  and A.KodCurrency = C.KodCurrency
  and A.DDSType = VR.DDSType
)
end
//v.1 [07.Nov.2007] Yanko