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(200);
  declare @Lang integer;
  declare @PriceInvCurrency numeric(12,6);
  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,VatRate as @VatRate 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;
    set @PriceInvCurrency = @ArticlePrice/@ExchangeRate;
    update #Articles set TranslateName = @NewName,PriceInvCurrency = @PriceInvCurrency,
      ArticleSumInvCurrency = @ArticleSum/@ExchangeRate,
      PriceInvWithDDS = @PriceInvCurrency*(1+@VatRate),
      ArticleSumWithDDS = @PriceInvCurrency*(1+@VatRate)*ArticleQty
      where ArticleNumber = @ArticleNumber
  //    update #Articles set PriceInvCurrency=@ArticlePrice/@ExchangeRate,
  //      ArticleSumInvCurrency=@ArticleSum/@ExchangeRate where ArticleNumber=@ArticleNumber end for
  end for
end
//[v. 28.02.2003  ] Jorko
//[v.2 19.Nov.2007] - Maria