IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('VaucherDisc'))) THEN
Drop procedure VaucherDisc
END IF


GO

create procedure DBA.VaucherDisc(inout @ArticleNumber integer,in @Currency integer,inout @TotalSumForVaucher1 double,in @ContractNo1 integer)
begin
  declare @BrojHora integer;
  declare @Broi integer;
  declare @BroiVuz integer;
  declare @BroiDeca integer;
  declare @TotalAddSum double;
  declare @Deposit double;
  declare @DiskSum double;
  declare @mnsign integer;
  declare @TotalNights integer;
  declare @Ads integer;
  declare @Childs integer;
  declare @DDSFlag integer;
  declare @ObshtoHora integer;
  set @TotalAddSum=0;
  set @DiskSum=0;
  set @mnsign=1;
  select sum(GrandTotal) into @TotalSumForVaucher1
    from #VaucherArticles where
    RowType = 3;
  set @TotalSumForVaucher1=@TotalSumForVaucher1*1.07;
  select(sum(Adult)+sum(Child)) into @BrojHora
    from #VaucherArticles;
  set @BroiVuz=0;
  set @BroiDeca=0;
  set @Broi=0;
  for fVauch as cursVauch scroll cursor for
    select distinct VaucherN as @Vauch from #VaucherArticles where VaucherN <> 0 do
    select AdultsNumber into @Ads from VtdVauchers where VaucherN = @Vauch;
    select ChildNumber into @Childs from VtdVauchers 
    where VaucherN = @Vauch;
    set @BroiVuz=@BroiVuz+@Ads;
    set @BroiDeca=@BroiDeca+@Childs end for;
  // select sum(Adult) into @Broi from #VaucherArticles;
  // set @BroiVuz=@Broi;
  // select sum(Child) into @Broi from #VaucherArticles;
  //  set @BroiDeca=@Broi;
  for f3 as curs3 scroll cursor for
    select
       DiskForContractNo as @DiskForContractNo,
      (if sign='-' then -20 else -21 endif) as @ServNo,
      DiskName as @ArtName,
      DiskPersent as @TotDisk,
      sign as @sign,
      FreeSum as @FreeSum,
      ServiceCount as @fSrvCouns,
      AdultType as @AdultType,
      ServiceCount as @SrvCouns,NightsOrGuests as @NightsOrGuests from
      OtherDiskForContract where
      ContractNo = @ContractNo1 do
    if(@TotDisk <> 0) then // ako e na procent
      set @DiskSum=(@TotalSumForVaucher1*(@TotDisk-(@TotDisk*7)/107))/100
    else
      set @DiskSum=(@FreeSum-(@FreeSum*7)/107);
    end if;
    if @sign = '+' then
      set @mnsign=1
    else
      set @mnsign=-1
    end if;
    if(@TotDisk = 0) then // ako e na nostuvki, ne e na procent
      select sum(TotalNights) into @SrvCouns from #VaucherArticles where RowType = 1;
      if((@mnsign*@DiskSum*@SrvCouns) <> 0) then
        if @NightsOrGuests = 0 then
          // na nostuvki
          insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,
            VaucherSeason,VaucherBoard,VaucherRoomType,
            VaucherBedType,Measure,Adult,
            Child,Nights,TotalNights,
            SinglePrice,
            GrandTotal,
            ArticleName,
            CreditNo,Discount,ServiceNo,
            Currency,TranslateName,TranslateSeason,
            TranslateBed,RowType,ForVaucherN,OtherDiskN) values(
            @ArticleNumber,0,'','','','','','бр.',
            null,
            null,null,@SrvCouns*@fSrvCouns,
            @DiskSum,
            @mnsign*@DiskSum*@SrvCouns,
            @ArtName,
            0,@TotDisk,@ServNo,
            @Currency,@ArtName,null,
            null,2,0,@DiskForContractNo) ;
          set @TotalAddSum=@TotalAddSum+(@mnsign*@DiskSum*@SrvCouns)
        else // na gosti
          if @AdultType=0 then  set @ObshtoHora=(@BroiVuz+@BroiDeca)
          else if @AdultType=1 then set @ObshtoHora=@BroiVuz
          else  set @ObshtoHora=@BroiDeca end if end if;
          if (((@AdultType=1) and (@BroiVuz>0)) or ((@AdultType=2) and (@BroiDeca>0)) or (@AdultType=0) or (@AdultType is null)) then
            insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,
              VaucherSeason,VaucherBoard,VaucherRoomType,
              VaucherBedType,Measure,Adult,
              Child,Nights,TotalNights,
              SinglePrice,
              GrandTotal,
              ArticleName,
              CreditNo,Discount,ServiceNo,
              Currency,TranslateName,TranslateSeason,
              TranslateBed,RowType,ForVaucherN,OtherDiskN) values(
              @ArticleNumber,0,'','','','','','бр.',
              @BroiVuz,
              @BroiDeca,null,@ObshtoHora,
              @DiskSum,
              @mnsign*@DiskSum*@ObshtoHora,
              @ArtName,
              0,@TotDisk,@ServNo,
              @Currency,@ArtName,null,
              null,2,0,@DiskForContractNo) ;
            set @TotalAddSum=@TotalAddSum+(@mnsign*@DiskSum*(@BroiVuz+@BroiDeca))
          end if;
        end if;
        set @ArticleNumber=@ArticleNumber+1
      end if
    else // ako e na Percent
      if @NightsOrGuests = 0 then
        insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,
          VaucherSeason,VaucherBoard,VaucherRoomType,
          VaucherBedType,Measure,Adult,
          Child,Nights,TotalNights,
          SinglePrice,GrandTotal,ArticleName,
          CreditNo,Discount,ServiceNo,
          Currency,TranslateName,TranslateSeason,
          TranslateBed,RowType,ForVaucherN,OtherDiskN) values(
          @ArticleNumber,0,'','','','','','бр.',
          null,
          null,null,@mnsign*@SrvCouns,
          0,@mnsign*@DiskSum,@ArtName,
          0,@TotDisk,@ServNo,
          @Currency,@ArtName,null,
          null,2,0,@DiskForContractNo) ;
        set @TotalAddSum=@TotalAddSum+(@mnsign*@DiskSum*@SrvCouns)
      else
        insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,
          VaucherSeason,VaucherBoard,VaucherRoomType,
          VaucherBedType,Measure,Adult,
          Child,Nights,TotalNights,
          SinglePrice,GrandTotal,ArticleName,
          CreditNo,Discount,ServiceNo,
          Currency,TranslateName,TranslateSeason,
          TranslateBed,RowType,ForVaucherN,OtherDiskN) values(
          @ArticleNumber,0,'','','','','','бр.',
          null,
          null,null,null,
          0,@mnsign*@DiskSum*(@BroiVuz+@BroiDeca),@ArtName,
          0,@TotDisk,@ServNo,
          @Currency,@ArtName,null,
          null,2,0,@DiskForContractNo) ;
        set @TotalAddSum=@TotalAddSum+(@mnsign*@DiskSum*(@BroiVuz+@BroiDeca))
      end if;
      set @ArticleNumber=@ArticleNumber+1
    end if end for;
  //Smqta GrandTotal na svobodnite uslugi i vmykva prazen red
  if @TotalAddSum <> 0 then
    insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,
      VaucherSeason,VaucherBoard,VaucherRoomType,
      VaucherBedType,Measure,Adult,
      Child,Nights,TotalNights,
      SinglePrice,GrandTotal,ArticleName,
      CreditNo,Discount,ServiceNo,
      Currency,TranslateName,TranslateSeason,
      TranslateBed,RowType,ForVaucherN) values( 
      @ArticleNumber,0,null,
      null,null,null,
      null,'бр.',null,
      null,null,null,
      null,@TotalAddSum,null,
      null,null,null,
      null,null,null,
      null,4,0) ;
    set @ArticleNumber=@ArticleNumber+1;
  end if;


  set @Deposit=0;
  set @Deposit=@Deposit+CalcContractDepositSum(@ContractNo1)/(1.07);
  if @Deposit<>0 then
       insert into #VaucherArticles(ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard,
              VaucherRoomType,VaucherBedType,Measure,Adult,Child,
              Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,
              CreditNo,Discount,ServiceNo,Currency,TranslateName,
              TranslateSeason,TranslateBed,RowType)
       values(@ArticleNumber,0,'','','',
              '','','бр.',0,0,
              -1,-1,@Deposit,(-1)*@Deposit,'Депозити(и)',
              0,0,-8,@Currency,'Депозит(ид)',
              null,null,7);
       set @ArticleNumber=@ArticleNumber+1;
  end if;



end

GO

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('VaucherArticlesByVidSmetka'))) THEN
Drop procedure VaucherArticlesByVidSmetka
END IF

GO

create procedure
DBA.VaucherArticlesByVidSmetka(in @VidSmetka integer,in @oldRate double,in @newRate double,in @NewCurrency integer)
// Ako se smeni vida na smetkata tazi procedura zapisva korektnite stojnosti
// i promenia imeto, ako e vuvedeno
begin
  declare @NewName varchar(40);
  declare @Season varchar(40);
  declare @ExtraBed varchar(40);
  //  declare @ExtraBedN integer;
  declare @Price double;
  declare @Total double;
  declare @NightsTotal double;
  declare @ExtresTotal double;
  //  if @oldRate=@newRate then
  //    return
  //  end if
  set @price=0;
  for f0 as curs0 scroll cursor for
    select ArticleNumber as @ArticleNumber,VaucherN as @VaucherNo,SinglePrice as @ArtPrice,
      ArticleName as @ArticleName,VaucherSeason as @VaucherSeason,VaucherBedType as @VaucherBedType,
      ExtraBed as @ExtraBedN,
      GrandTotal as @ArticleSum,ServiceNo as @ServiceNo,Nights as @Nights,ForVaucherN as @ForVaucherN,
      OtherDiskN as @OtherDiskN 
      from #VaucherArticles do
    set @NewName=null;
    set @Season=null;
    set @ExtraBed=null;
    if @oldRate = @newRate then
      set @Price=@Artprice;
      set @total=@ArticleSum
    else
      set @Price=@Artprice*@oldRate;
      set @price=@price/@newRate;
      set @total=@ArticleSum*@oldRate;
      set @total=@total/@newRate
    end if;
    if(@VaucherNo <> 0) then
      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;
      if(@NewName is null) then
        set @NewName=@ArticleName
      end if;
      select SS.Stoinost into @Season
        from SvoVaucherStn as SS,PriceSeasons as PS,VidSmetki as VS where
        PS.SeasonNo = SS.Glava and
        SS.Svoistvo = VS.SvoService and
        SS.SvoKind = 'sea' and
        VS.N = @VidSmetka and
        PS.Description = @VaucherSeason;
      if(@Season is null) then
        set @Season=@VaucherSeason
      end if;
      select SS.Stoinost into @ExtraBed
        from SvoVaucherStn as SS,ExtraBed as EB,VidSmetki as VS where
        EB.N = SS.Glava and
        SS.Svoistvo = VS.SvoService and
        SS.SvoKind = 'bed' and
        VS.N = @VidSmetka and
        EB.N = @ExtraBedN;
      if(@ExtraBed is null) then
        set @ExtraBed=@VaucherBedType
      end if;
      update #VaucherArticles set TranslateName = @NewName,TranslateSeason = @Season,TranslateBed = @ExtraBed,
        SinglePrice = @Price,GrandTotal = @Total,Currency = @NewCurrency where
        ArticleNumber = @ArticleNumber and VaucherN <> 0
    else
      if @ForVaucherN=0 then
         select SS.Stoinost into @NewName
           from VidSmetki as VS,SvoVaucherStn as SS
           where VS.N = @VidSmetka and VS.SvoService = SS.Svoistvo  and SS.Glava=@OtherDiskN
               and SS.SvoKind = 'srv';
         if(@NewName is null) then
           set @NewName=@ArticleName
         end if;
         update #VaucherArticles set TranslateName = @NewName,SinglePrice = @Price,GrandTotal = @Total,
           Currency = @NewCurrency where
           ArticleNumber = @ArticleNumber
      end if;
    end if end for
//call RefreshPersentTotal()
end
//[v. 01.07.05]
//[v. 23.05.2003] Jorko
//[v. 03.06.2003] Joracio
//[v. 28.06.2003] Jorko poveche sezoni se otchitat