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


GO


create procedure
DBA.PrepareVauchersBill(in @ContractNo integer,in @place varchar(50))
// TaziF procedura se izpolzva za napylwane na wremennata tablica #VaucherArticles
// S uslugite koito triabva da plashtat gostite nastaneni po vaucheri ot daden dogovor
begin
  declare @Currency integer;
  declare @ArticleNumber integer;
  //  declare @FreeDays integer;
  declare @People integer;
  //  declare @Deposit decimal(12,2);
  declare @Adult integer;
  declare @Child integer;
  declare @exCount integer;
  declare @exType integer;
  declare @PayedLodgings integer;
  declare @valPrice decimal(12,2);
  declare @Disc decimal(12,2);
  declare @TotalSum double;
  //  declare @TotalAddSum decimal(12,2);
  //  declare @DiskSum decimal(12,6);
  //  declare @mnsign integer;
  declare @ActiveSeasson integer;
  declare @IsForAlServices integer;
  //  declare @flag integer;
  //  declare @ostatyk integer;
  declare @dobavka integer;
  declare @MaxPrice decimal(12,2);
  declare @MinPrice decimal(12,2);
  declare @TotalSumForVaucher double;
  declare @ConNo integer;
  declare @VVVAucherNum varchar(30);
  //  declare @NightsOrGuests integer;
  //  declare @BrojHora integer;
  //  declare @Broi integer;
  //  declare @BroiVuz integer;
  //  declare @BroiDeca integer;
  declare @FlagWithSubTotals integer;
  declare @BroiSubTotals integer;
  declare @DateArrive date;
  declare  @LArticleNumber integer; // ivaylo
  declare @MinAn integer;
  delete from #VaucherArticles;
  set bvrBroi=0;
  //  set @FreeDays=0;
  set @People=0;
  set @Adult=0;
  set @Child=0;
  //  set @TotalAddSum=0;
  set @TotalSum=0;
  //  set @DiskSum=0;
  //  set @mnsign=1;
  //  set @ostatyk=0;
  set @dobavka=0;
  //  set @flag=0;
  set @MaxPrice=0;
  set @MinPrice=0;
  set @ActiveSeasson=0;
  set @TotalSumForVaucher=0;
  set @BroiSubTotals=0;

    select IntStojnost into @FlagWithSubTotals
    from Registers where RegisterName = 'VSmetkaSubTotals';

  select Max(ArticleNumber)+1 into @ArticleNumber from #VaucherArticles;

  if @ArticleNumber is null then set @ArticleNumber=1;
  end if;
  //Proverqva dali ima ostanovene otstypka za vsi4ki uslugi ot paketa uslugi po dogovor
  select C.Discount into @Disc from Contracts as C where C.ContractNo = @ContractNo;
  if(@Disc = null) then set @Disc=0
  end if;
  select ExtresCount,ExtresType into @exCount,@exType from Contracts as c where c.ContractNo = @ContractNo;
  for f1 as curs1 scroll cursor for
    select v.VaucherNo as @VaucherNo,v.VaucherNum as @VaucherNum,s.ServiceName as @ArticleName,
      v.ContractNo,v.VaucherBoardPlan as @BoardPlanNo,v.NumAdults as @NumAdult,v.NumChild as @Numchild,
      v.NumLodgings as @ServiceCount,v.ServiceNo as @ServiceNum from
      VauchersForInvoice as v left outer join
      Services as s on
      (v.ServiceNo = s.ServiceNo) where
      v.ContractNo = @ContractNo do
    for f2 as curs2 scroll cursor for
      select AdultType as @AdultType,HumanKind as @HumanKind,RoomType as @RoomType,
        HasExtraBed as @HasExtraBed,min(Name) as @GuestName,count(*) as @NumPeople from
        VaucherGuests where
        VaulcherNo = @VaucherNo
        group by AdultType,HumanKind,RoomType,HasExtraBed order by
        AdultType,HasExtraBed asc do
      select DateArrive into @DateArrive from VtdVauchers where
        VaucherN = @VaucherNo and
        VaucherNum = @VaucherNum;
      call VaucherExtresType(@ArticleNumber,@VaucherNo,cast(@VaucherNum as varchar(30)),@BoardPlanNo,
      @NumAdult,@Numchild,@ServiceCount,@ServiceNum,
      @NumPeople,@ArticleName,@GuestName,@Disc,
      @HumanKind,@AdultType,@HasExtraBed,@RoomType,
      @ContractNo,@DateArrive,days(@DateArrive,@ServiceCount),@ServiceCount);
      end for;
    set @VVVAucherNum=cast(@VaucherNum as varchar(30));
    select sum(GrandTotal) into @TotalSumForVaucher from #VaucherArticles where VaucherN = @VaucherNo;
    //set @ArticleNumber=@ArticleNumber+1;
    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,@TotalSumForVaucher,null,
      null,null,null,null,null,
      null,null,3,0) ;
      set @ArticleNumber=@ArticleNumber+1;
    end for;
  //Smqta GrandTotal na vsi4ki noshtuvki po vaucheri i vmykva prazen red
  select sum(GrandTotal) into @TotalSum from #VaucherArticles where RowType = 3;
  if(@TotalSum <> 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,@TotalSum,null,
      null,null,null,null,'       ',
      null,null,5,0) ;
    set @ArticleNumber=@ArticleNumber+1;
    set @ADult=0;
    set @Child=0;
  end if;
  //Vzema i smetkosva svobodnite uslugi koito sa % ili suma
  call VaucherDisc(@ArticleNumber,@Currency,@TotalSumForVaucher,@ContractNo);
  set @ArticleNumber=@ArticleNumber+1;
  select sum(GrandTotal) into @TotalSum from #VaucherArticles where RowType in( 4,5) ;
  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,@TotalSum,null,
    null,null,null,null,'       ',
    null,null,6,0) ;

  select count(*) into @BroiSubTotals from #VaucherArticles where RowType = 3;
  if @BroiSubTotals = 1 then
    delete from #VaucherArticles where RowType = 3
  end if;
  if @FlagWithSubTotals = 0 then
    delete from #VaucherArticles where RowType = 3;
    delete from #VaucherArticles where
      RowType = 0 and
      ArticleNumber >= 1 and
      ArticleNumber <= (select max(ArticleNumber) from #VaucherArticles where RowType = 1)
  end if;
  delete from #VaucherArticles where RowType = 6;

  //update #VaucherArticles set ArticleNumber = Number(); // Yanko
  while ((Select Max(ArticleNumber) from #VaucherArticles) > 0) loop
    set @MinAn = (select Min(ArticleNumber) from #VaucherArticles);
    if @MinAn > 0 then set @MinAn = 0 end if;
    Update #VaucherArticles set ArticleNumber = @MinAn - 1 where ArticleNumber =
      (Select Min(ArticleNumber) from #VaucherArticles where ArticleNumber > 0);
  end loop;
  Update #VaucherArticles set ArticleNumber = ArticleNumber * (-1);
end