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
      (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) values( 
            @ArticleNumber,0,'','','','','','бр.',
            null,
            null,null,@SrvCouns*@fSrvCouns,
            @DiskSum,
            @mnsign*@DiskSum*@SrvCouns,
            @ArtName,
            0,@TotDisk,@ServNo,
            @Currency,@ArtName,null,
            null,2,0) ;
          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) values(
              @ArticleNumber,0,'','','','','','бр.',
              @BroiVuz,
              @BroiDeca,null,@ObshtoHora,
              @DiskSum,
              @mnsign*@DiskSum*@ObshtoHora,
              @ArtName,
              0,@TotDisk,@ServNo,
              @Currency,@ArtName,null,
              null,2,0) ;
            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) values( 
          @ArticleNumber,0,'','','','','','бр.',
          null,
          null,null,@mnsign*@SrvCouns,
          0,@mnsign*@DiskSum,@ArtName,
          0,@TotDisk,@ServNo,
          @Currency,@ArtName,null,
          null,2,0) ;
        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) values( 
          @ArticleNumber,0,'','','','','','бр.',
          null,
          null,null,null,
          0,@mnsign*@DiskSum*(@BroiVuz+@BroiDeca),@ArtName,
          0,@TotDisk,@ServNo,
          @Currency,@ArtName,null,
          null,2,0) ;
        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