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 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

GO


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

GO

create procedure 
DBA.PreparePrAgainVaucherBill(in @SmetkaNo integer,in @OkCancel integer)
// Tazi procedura se izpolzva za napylwane na wremennata tablica #VaucherArticles
// sus articulite ot opredelena smetka pri powtoren pechat na smetka ili Anulirane na smetka po dogovor - vaucher
// kato ako e PrintAgain @OkCancel e 1, a pri Cancel -1
//  Poleto RowType  opredelia kakuv tip e reda
//  1 Red za Vaucherna usluga za daden gost
//  2 Red za Dopulnitelna usluga za daden gost
//  3 SubTotal na Vaucher - suma na nostuvkite
//  4 SubTotal na Extreses - suma na dopulnitelnite uslugi
//  5 Total Vaucher = suma na nostuvkite + suma na dopulnitelnite uslugi
//  6 Super Total  = suma na Vsichko
begin
  declare @ArtNumBr integer;
  declare @TotalSum double;
  declare @TotalAddSum double;
  declare @OldForVaucherN integer;
  declare @TotalSumForVaucher double;
  declare @FlagWithSubTotals integer;
  declare @BroiSubTotals integer;
  declare @LArticleNumber integer; // ivaylo
  declare @MinAn integer;
  //  declare @ArtNum1 integer;
  //Iztriva wremennata tablica za artikulite
  delete from #VaucherArticles;
  //Iztriva wremennata tablica za nacinite na plashtane
  delete from #PaymentTypes;
  set @TotalSum=0;
  set @TotalAddSum=0;
  set @ArtNumBr=0;
  set @OldForVaucherN=0;
  //  set @TotalSumForVaucher=0;
  //Popylva wremennata tablica za artikulite
  for ff2 as cursff2 scroll cursor for
    select distinct VaucherN as @VaN from VaucherArticles where
      InvoiceNo = @SmetkaNo and
      VaucherN <> 0 order by
      VaucherN asc do
    set @TotalSumForVaucher=0;
    for f1 as curs1 scroll cursor for
      select ArticleNo,VaucherN as @VaucherNo,VaucherNum as @VaucherNum,
        VaucherSeason as @Seasson,VaucherBoard as @BoardPlan,VaucherRoomType as @Room,
        VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child,
        Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice,
        GrandTotal as @GrTotal,ArticleName as @ArticleName,Discount as @Disc,
        ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo,
        TranslateName as @TranslateName,TranslateSeason as @TranslateSeason,
        TranslateBed as @TranslateBed,GuestName as @GuestName,
        TranslateGuestName as @TranslateGuestName,SeasonNo as @SeassonNo,
        HumanKind as @HumanKind,AdultType as @AdultType,ExtraBed as @HasExtraBed,
        BoardPlan as @ValBoardPlan,RoomType as @RoomType,ForVaucherN as @ForVaucherN from
        VaucherArticles where
        InvoiceNo = @SmetkaNo and
        ForVaucherN <> 0 and
        VaucherN = @VaN and
        VaucherN <> 0 order by
        ArticleNo asc do
      set @TotalSumForVaucher=@TotalSumForVaucher+@GrTotal;
      set @ArtNumBr=@ArtNumBr+1; 
      insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,
        VaucherBoard,VaucherRoomType,VaucherBedType,Measure,Adult,Child,
        Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,
        GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName,
        TranslateSeason,TranslateBed,TranslateGuestName,
        SeasonNo,ExtraBed,BoardPlan,RoomType,HumanKind,AdultType,
        RowType,ForVaucherN) values( 
        @ArtNumBr,@VaucherNo,@VaucherNum,@Seasson,
        @BoardPlan,@Room,@AdditionBed,'??.',@Adult,@Child,
        @Nights,@TotalNights,@OkCancel*@RealPrice,@OkCancel*@GrTotal,@ArticleName,
        @GuestName,@CreditNo,@Disc,@ServiceNum,@Currency,@TranslateName,
        @TranslateSeason,@TranslateBed,@TranslateGuestName,
        @SeassonNo,@HasExtraBed,@ValBoardPlan,@RoomType,@HumanKind,@AdultType,
        1,@ForVaucherN) ;
      end for;
    // **************************
    set @ArtNumBr=@ArtNumBr+1;
    insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,
      VaucherBoard,VaucherRoomType,VaucherBedType,Measure,Adult,Child,
      Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,
      GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName,
      TranslateSeason,TranslateBed,TranslateGuestName,RowType,ForVaucherN) values( 
      @ArtNumBr,0,null,null,
      null,null,null,null,null,null,
      null,null,null,@OkCancel*@TotalSumForVaucher,null,
      null,null,null,null,null,null,
      null,null,null,3,0) 
      end for;
  select sum(GrandTotal) into @TotalSum
    from VaucherArticles where
    InvoiceNo = @SmetkaNo and
    ForVaucherN <> 0 and
    VaucherN <> 0;
  set @ArtNumBr=@ArtNumBr+1;
  insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,
    VaucherBoard,VaucherRoomType,VaucherBedType,Measure,Adult,Child,
    Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,
    GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName,
    TranslateSeason,TranslateBed,TranslateGuestName,RowType,ForVaucherN) values( 
    @ArtNumBr,0,null,null,
    null,null,null,null,null,null,
    null,null,null,@OkCancel*@TotalSum,null,
    null,null,null,null,null,null,
    null,null,null,5,0) ;
  //  Sega Dopulnitelnite uslugi
  for f2 as curs2 scroll cursor for
    select ArticleNo,VaucherN as @VaucherNo,VaucherNum as @VaucherNum,
      VaucherSeason as @Seasson,VaucherBoard as @BoardPlan,VaucherRoomType as @Room,
      VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child,
      Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice,
      GrandTotal as @GrTotal,ArticleName as @ArticleName,Discount as @Disc,
      ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo,
      TranslateName as @TranslateName,TranslateSeason as @TranslateSeason,
      TranslateBed as @TranslateBed,GuestName as @GuestName,
      TranslateGuestName as @TranslateGuestName,SeasonNo as @SeassonNo,
      HumanKind as @HumanKind,AdultType as @AdultType,ExtraBed as @HasExtraBed,
      BoardPlan as @ValBoardPlan,RoomType as @RoomType,ForVaucherN as @ForVaucherN from
      VaucherArticles where
      InvoiceNo = @SmetkaNo and
      ForVaucherN = 0 and
      VaucherN = 0 order by
      ArticleNo asc do
    set @ArtNumBr=@ArtNumBr+1;
    insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,
      VaucherBoard,VaucherRoomType,VaucherBedType,Measure,Adult,Child,
      Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,
      GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName,
      TranslateSeason,TranslateBed,TranslateGuestName,
      SeasonNo,ExtraBed,BoardPlan,RoomType,HumanKind,AdultType,
      RowType,ForVaucherN) values( 
      @ArtNumBr,@VaucherNo,@VaucherNum,@Seasson,
      @BoardPlan,@Room,@AdditionBed,'??.',@Adult,@Child,
      @Nights,@TotalNights,@OkCancel*@RealPrice,@OkCancel*@GrTotal,@ArticleName,
      @GuestName,@CreditNo,@Disc,@ServiceNum,@Currency,@TranslateName,
      @TranslateSeason,@TranslateBed,@TranslateGuestName,
      @SeassonNo,@HasExtraBed,@ValBoardPlan,@RoomType,@HumanKind,@AdultType,
      2,0) ;
    end for;
  select sum(GrandTotal) into @TotalAddSum
    from VaucherArticles where
    InvoiceNo = @SmetkaNo and
    ForVaucherN = 0 and
    VaucherN = 0;
  set @ArtNumBr=@ArtNumBr+1;
  insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,
    VaucherBoard,VaucherRoomType,VaucherBedType,Measure,Adult,Child,
    Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,
    GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName,
    TranslateSeason,TranslateBed,TranslateGuestName,RowType,ForVaucherN) values( 
    @ArtNumBr,0,null,null,
    null,null,null,null,null,null,
    null,null,null,@OkCancel*@TotalAddSum,null,
    null,null,null,null,null,null,
    null,null,null,4,0) ;
  set @ArtNumBr=@ArtNumBr+1;
  //   ***********************  end for ****************************************
  for f3 as curs3 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.VaucherInvoiceNo = @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;
  select IntStojnost into @FlagWithSubTotals
    from Registers where RegisterName = 'VSmetkaSubTotals';
  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;
  end if;
 //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
//[v 17.05.2003] Jorko
//[v 01.07.2003] Jorko premahva reda na dopylnitelnite otstupki ako e 0.00
//[v 01.08.2003] Jorko prenapisana nacialo s nova ideologia za redovete RowType
//[v 23.12.2003] Jorko preposana pak

GO

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

Go

Create Procedure
DBA.PreparePrintAgainVaucherProform(in @ProformNo integer)
// Tazi procedura se izpolzva za napylwane na wremennata tablica #Articles za povtoren pe4at na proforma
begin
  declare @ArtNumber integer;
  declare @TotalSum decimal(12,2);
  declare @TotalAddSum decimal(12,2);
  declare @SuperTotalSum decimal(12,2);
  declare @BrExtras integer;
  declare @FlagWithSubTotals integer;
  declare @MinAn integer;
  //Iztriva wremennata tablica za artikulite
  delete from #VaucherArticles;
  set @TotalSum=0;
  set @ArtNumber=1;
  //Popylva wremennata tablica za artikulite
  for f2 as curs2 scroll cursor for
    select distinct VaucherN as @VV from ProVaucherArticles where
      VaucherN <> 0 and
      ProformNo = @ProformNo order by
      VaucherN asc do
    set @TotalSum=0;
    for f1 as curs1 scroll cursor for
      select ArticleNo,VaucherN as @VaucherNo,VaucherNum as @VaucherNum,VaucherSeason as @Seasson,
        VaucherBoard as @BoardPlan,VaucherRoomType as @Room,VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child,
        Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice,GrandTotal as @GrTotal,ArticleName as @ArticleName,
        Discount as @Disc,ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo,
        TranslateName as @TranslateName,TranslateSeason as @TranslateSeason,TranslateBed as @TranslateBed,
        GuestName as @GuestName,TranslateGuestName as @TranslateGuestName,SeasonNo as @SeasonNo,
        HumanKind as @HumanKind,AdultType as @AdultType,ExtraBed as @ExtraBed,
        BoardPlan as @BPNum,RoomType as @RoomType from
        ProVaucherArticles where
        ProformNo = @ProformNo and
        VaucherN = @VV order by
        ArticleNo asc do
      insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard,
        VaucherRoomType,VaucherBedType,Measure,Adult,Child,
        Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,
        GuestName,CreditNo,Discount,ServiceNo,Currency,
        TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,SeasonNo,
        HumanKind,AdultType,ExtraBed,BoardPlan,RoomType,
        RowType,ForVaucherN) values( 
        @ArtNumber,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan,
        @Room,@AdditionBed,'απ.',@Adult,@Child,
        @Nights,@TotalNights,@RealPrice,@GrTotal,@ArticleName,
        @GuestName,@CreditNo,@Disc,@ServiceNum,@Currency,
        @TranslateName,@TranslateSeason,@TranslateBed,@TranslateGuestName,@SeasonNo,
        @HumanKind,@AdultType,@ExtraBed,@BPNum,@RoomType,
        1,@VaucherNo) ;
      set @TotalSum=@TotalSum+@GrTotal;
      set @ArtNumber=@ArtNumber+1 end for;
    // end for f1
    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( 
      @ArtNumber,0,null,null,null,
      null,null,'απ.',null,null,
      null,null,null,@TotalSum,null,
      null,null,null,null,null,
      null,null,3,0) ;
    set @ArtNumber=@ArtNumber+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( 
      @ArtNumber,0,null,null,null,
      null,null,'απ.',null,null,
      null,null,null,null,null,
      null,null,null,null,null,
      null,null,0,0) ;
    set @ArtNumber=@ArtNumber+1 end for;
  // end for f2
  select sum(GrandTotal) into @SuperTotalSum
    from #VaucherArticles where
    RowType = 3;
  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( 
    @ArtNumber,0,null,null,null,
    null,null,'απ.',null,null,
    null,null,null,@SuperTotalSum,null,
    null,null,null,null,null,
    null,null,5,0) ;
  set @ArtNumber=@ArtNumber+1;
  // extra discounts
  select count(*) into @BrExtras from ProVaucherArticles where ProformNo = @ProformNo and VaucherN = 0;
  if @BrExtras > 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( 
      @ArtNumber,0,null,null,null,
      null,null,'απ.',null,null,
      null,null,null,null,null,
      null,null,null,null,null,
      null,null,0,0) ;
    set @ArtNumber=@ArtNumber+1
  end if;
  set @TotalAddSum=0;
  for f3 as curs3 scroll cursor for
    select ArticleNo as @ArtNum,VaucherN as @VaucherNo,VaucherNum as @VaucherNum,VaucherSeason as @Seasson,
      VaucherBoard as @BoardPlan,VaucherRoomType as @Room,VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child,
      Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice1,GrandTotal as @GrTotal1,ArticleName as @ArticleName,
      Discount as @Disc,ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo,
      TranslateName as @TranslateName,TranslateSeason as @TranslateSeason,TranslateBed as @TranslateBed,
      GuestName as @GuestName,TranslateGuestName as @TranslateGuestName from
      ProVaucherArticles where
      ProformNo = @ProformNo and
      VaucherN = 0 do // and CreditNo is not null
    insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard,
      VaucherRoomType,VaucherBedType,Measure,Adult,Child,
      Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,
      GuestName,CreditNo,Discount,ServiceNo,Currency,
      TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,SeasonNo,
      HumanKind,AdultType,ExtraBed,BoardPlan,RoomType,
      RowType,ForVaucherN) values( 
      @ArtNumber,0,null,null,null,
      null,null,'απ.',null,null,
      null,@TotalNights,@RealPrice1,@GrTotal1,@ArticleName,
      null,0,@Disc,@ServiceNum,@Currency,
      @TranslateName,null,null,null,null,
      null,null,null,null,null,
      2,@VaucherNo) ;
    set @ArtNumber=@ArtNumber+1;
    set @TotalAddSum=@TotalAddSum+@GrTotal1 end for;
  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( 
    @ArtNumber,0,null,null,null,
    null,null,'απ.',null,null,
    null,null,null,@TotalAddSum,null,
    null,null,null,null,null,
    null,null,4,0) ;
  select IntStojnost into @FlagWithSubTotals
    from Registers where RegisterName = 'VSmetkaSubTotals';
  if @FlagWithSubTotals = 0 then
    delete from #VaucherArticles where RowType = 3;
-- Yanko +    
    /*
    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 = 0;
  //update #VaucherArticles set ArticleNumber = Number();
  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
// [v 1]
// [v 15.Jan.2007] Yanko - poreden N sled iztrivane na red Yanko -


GO


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

Go

create procedure DBA.PrepareValBillByProform(@ProformNo integer)
// Tazi procedura se izpolzva za napylwane na wremennata tablica #VaucherArticles
//se pravi smetka dogovor-vaucher po predvaritelno izbrana proforma
begin atomic
  declare @ArtNumber integer;
  declare @TotalSum double;
  declare @TotalAddSum double;
  declare @SuperTotalSum double;
  declare @BrExtras integer;
  declare @FlagWithSubTotals integer;
  declare @MinAn integer;
  set @ArtNumber=0;
  set @TotalSum=0;
  set @TotalAddSum=0;
  delete from #VaucherArticles;
  //Zima si4ki no6tuvki po vaucheri ot proforma
  for f2 as curs2 scroll cursor for
    select distinct VaucherN as @VV from ProVaucherArticles where
      VaucherN <> 0 and
      ProformNo = @ProformNo order by
      VaucherN asc do
    set @TotalSum=0;
    for f1 as curs1 scroll cursor for
      select ArticleNo,VaucherN as @VaucherNo,VaucherNum as @VaucherNum,VaucherSeason as @Seasson,
        VaucherBoard as @BoardPlan,VaucherRoomType as @Room,VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child,
        Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice,GrandTotal as @GrTotal,ArticleName as @ArticleName,
        Discount as @Disc,ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo,
        TranslateName as @TranslateName,TranslateSeason as @TranslateSeason,TranslateBed as @TranslateBed,
        GuestName as @GuestName,TranslateGuestName as @TranslateGuestName,SeasonNo as @SeasonNo,
        HumanKind as @HumanKind,AdultType as @AdultType,ExtraBed as @ExtraBed,
        BoardPlan as @BPNum,RoomType as @RoomType from
        ProVaucherArticles where
        ProformNo = @ProformNo and
        VaucherN = @VV order by
        ArticleNo asc do
      insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard,
        VaucherRoomType,VaucherBedType,Measure,Adult,Child,
        Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,
        GuestName,CreditNo,Discount,ServiceNo,Currency,
        TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,SeasonNo,
        HumanKind,AdultType,ExtraBed,BoardPlan,RoomType,
        RowType,ForVaucherN) values( 
        @ArtNumber,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan,
        @Room,@AdditionBed,'απ.',@Adult,@Child,
        @Nights,@TotalNights,@RealPrice,@GrTotal,@ArticleName,
        @GuestName,@CreditNo,@Disc,@ServiceNum,@Currency,
        @TranslateName,@TranslateSeason,@TranslateBed,@TranslateGuestName,@SeasonNo,
        @HumanKind,@AdultType,@ExtraBed,@BPNum,@RoomType,
        1,@VaucherNo) ;
      set @TotalSum=@TotalSum+@GrTotal;
      set @ArtNumber=@ArtNumber+1 end for;
    // end for f1
    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( 
      @ArtNumber,0,null,null,null,
      null,null,'απ.',null,null,
      null,null,null,@TotalSum,null,
      null,null,null,null,null,
      null,null,3,0) ;
    set @ArtNumber=@ArtNumber+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( 
      @ArtNumber,0,null,null,null,
      null,null,'απ.',null,null,
      null,null,null,null,null,
      null,null,null,null,null,
      null,null,0,0) ;
    set @ArtNumber=@ArtNumber+1 end for;
  // end for f2
  select sum(GrandTotal) into @SuperTotalSum
    from #VaucherArticles where
    RowType = 3;
  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( 
    @ArtNumber,0,null,null,null,
    null,null,'απ.',null,null,
    null,null,null,@SuperTotalSum,null,
    null,null,null,null,null,
    null,null,5,0) ;
  set @ArtNumber=@ArtNumber+1;
  // extra discounts
  select count(*) into @BrExtras from ProVaucherArticles where ProformNo = 7 and VaucherN = 0;
  if @BrExtras > 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( 
      @ArtNumber,0,null,null,null,
      null,null,'απ.',null,null,
      null,null,null,null,null,
      null,null,null,null,null,
      null,null,0,0) ;
    set @ArtNumber=@ArtNumber+1
  end if;
  set @TotalAddSum=0;
  for f3 as curs3 scroll cursor for
    select ArticleNo as @ArtNum,VaucherN as @VaucherNo,VaucherNum as @VaucherNum,VaucherSeason as @Seasson,
      VaucherBoard as @BoardPlan,VaucherRoomType as @Room,VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child,
      Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice1,GrandTotal as @GrTotal1,ArticleName as @ArticleName,
      Discount as @Disc,ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo,
      TranslateName as @TranslateName,TranslateSeason as @TranslateSeason,TranslateBed as @TranslateBed,
      GuestName as @GuestName,TranslateGuestName as @TranslateGuestName from
      ProVaucherArticles where
      ProformNo = @ProformNo and
      VaucherN = 0 do // and CreditNo is not null
    insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,VaucherSeason,VaucherBoard,
      VaucherRoomType,VaucherBedType,Measure,Adult,Child,
      Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,
      GuestName,CreditNo,Discount,ServiceNo,Currency,
      TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,SeasonNo,
      HumanKind,AdultType,ExtraBed,BoardPlan,RoomType,
      RowType,ForVaucherN) values( 
      @ArtNumber,0,null,null,null,
      null,null,'απ.',null,null,
      null,@TotalNights,@RealPrice1,@GrTotal1,@ArticleName,
      null,0,@Disc,@ServiceNum,@Currency,
      @TranslateName,null,null,null,null,
      null,null,null,null,null,
      2,@VaucherNo) ;
    set @ArtNumber=@ArtNumber+1;
    set @TotalAddSum=@TotalAddSum+@GrTotal1 end for;
  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( 
    @ArtNumber,0,null,null,null,
    null,null,'απ.',null,null,
    null,null,null,@TotalAddSum,null,
    null,null,null,null,null,
    null,null,4,0) ;
  select IntStojnost into @FlagWithSubTotals
    from Registers where RegisterName = 'VSmetkaSubTotals';
  if @FlagWithSubTotals = 0 then
    delete from #VaucherArticles where RowType = 3;
-- Yanko +
    /*
    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 = 0;
  //update #VaucherArticles set ArticleNumber = Number();
  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);  
-- Yanko -
end
// [v 1]
// [v 15.Jan.2007] Yanko - poreden N sled iztrivane na red