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;
  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,
      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
          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,(@BroiVuz+@BroiDeca),
            @DiskSum,
            @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
    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

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
//  7 depozit
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 ****************************************
  
//------------------------------------------------------------------------------ Ivaylo Depozit
for f4 as curs4 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
      ServiceNo=-8 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) 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,
      7) ;
end for;
//------------------------------------------------------------------------------ Ivaylo Depozit
  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('FinishVaucherProformBill'))) THEN
Drop function FinishVaucherProformBill
END IF


GO


create function
DBA.FinishVaucherProformBill(in @PayHow integer,in @InvoiceType integer,in @InvoiceDate integer,in @KodAdmin integer,in @Whom varchar(50),in @TotalNoVAT double,in @VAT double,in @GrandTotal double,in @WithoutVAT double,in @BillType integer,in @No integer,in @IsInvoice integer,in @VidSmetka integer,in @ExchangeRate double,in @ContractNo integer)
returns integer
//Insertva dannite fakturata vuv Proform i ProVaucherArticles i sled tova po tazi Proforma moje da se izdava faktura za smetka dogovor - vaucher
begin atomic
  declare @ProformNo integer;
  declare @DepositNo integer;
  declare @TrueInvoiceNo integer;
  declare @CancelInvoiceNo integer;
  declare @ServiceNo integer;
  //declare @CreditNo integer;
  declare @ResMoneyStatus integer;
  declare @SmetkaCurrency integer;
  declare @ArtNo integer;
  //Vzima Tipa valuta ot vida smetka
  select KodCurrency into @SmetkaCurrency from VidSmetki where N = @VidSmetka;
  //Iztriva nulevite kolichestva
  delete from #VaucherArticles where TotalNights = 0;
  //Vmukva dannite za fakturata
  set @ProformNo=GetCounter('TrueProfNo');
  //Trqbva da se dobavi i nomera na dogovora pri insert-a na nova smetka
  insert into Proform( ProformNo,PayHow,InvoiceType,InvoiceDate,BankTown,Upolnomosten,
    UpolnomDate,UpolnomNum,KodAdmin,Whom,MOL,WhomTown,WhomDanNum,Bulstat,KodCurrency,
    TotalNoVAT,VAT,GrandTotal,TrueInvoiceNo,WithoutVAT,TrueInvoiceDate,ExchangeRate,VidSmetka,ContractNo) values( 
    @ProformNo,@PayHow,@InvoiceType,@InvoiceDate,'','','','',@KodAdmin,
    @Whom,'','','','',@SmetkaCurrency,@TotalNoVAT,@VAT,@GrandTotal,@TrueInvoiceNo,@WithoutVAT,FloatToDate(@InvoiceDate),@ExchangeRate,@VidSmetka,@ContractNo) ;
  set @ArtNo=1;
  //  return;
  for f1 as curs1 scroll cursor for
    select ArticleNumber,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 from
      #VaucherArticles where
      RowType = 1 order by
      ArticleNumber asc do
    insert into ProVaucherArticles( ProformNo,ArticleNo,VaucherN,
      VaucherNum,VaucherSeason,VaucherBoard,VaucherRoomType,VaucherBedType,Adult,Child,Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,
      SeasonNo,HumanKind,AdultType,ExtraBed,BoardPlan,RoomType) values( 
      @ProformNo,@ArtNo,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan,@Room,@AdditionBed,@Adult,@Child,@Nights,@TotalNights,
      @RealPrice,@GrTotal,@ArticleName,@GuestName,@CreditNo,@Disc,@ServiceNum,@Currency,@TranslateName,@TranslateSeason,@TranslateBed,@TranslateGuestName,
      @SeassonNo,@HumanKind,@AdultType,@HasExtraBed,@ValBoardPlan,@RoomType) ;
    set @ArtNo=@ArtNo+1 end for;
  for f2 as curs2 scroll cursor for
    select ArticleNumber,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 from
      #VaucherArticles where
      RowType = 2 order by
      ArticleNumber asc do
    insert into ProVaucherArticles( ProformNo,ArticleNo,VaucherN,
      VaucherNum,VaucherSeason,VaucherBoard,VaucherRoomType,VaucherBedType,Adult,Child,Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,
      SeasonNo,HumanKind,AdultType,ExtraBed,BoardPlan,RoomType) values( 
      @ProformNo,@ArtNo,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan,@Room,@AdditionBed,@Adult,@Child,@Nights,@TotalNights,
      @RealPrice,@GrTotal,@ArticleName,@GuestName,@CreditNo,@Disc,@ServiceNum,@Currency,@TranslateName,@TranslateSeason,@TranslateBed,@TranslateGuestName,
      @SeassonNo,@HumanKind,@AdultType,@HasExtraBed,@ValBoardPlan,@RoomType) ;
    set @ArtNo=@ArtNo+1 end for;
//------------------------------------------------------------------- ivaylo Depozit
 for f3 as curs3 scroll cursor for
    select ArticleNumber,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 from
      #VaucherArticles where
      RowType = 7 order by
      ArticleNumber asc do
    insert into ProVaucherArticles( ProformNo,ArticleNo,VaucherN,
      VaucherNum,VaucherSeason,VaucherBoard,VaucherRoomType,VaucherBedType,Adult,Child,Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,
      SeasonNo,HumanKind,AdultType,ExtraBed,BoardPlan,RoomType) values( 
      @ProformNo,@ArtNo,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan,@Room,@AdditionBed,@Adult,@Child,@Nights,@TotalNights,
      @RealPrice,@GrTotal,@ArticleName,@GuestName,@CreditNo,@Disc,@ServiceNum,@Currency,@TranslateName,@TranslateSeason,@TranslateBed,@TranslateGuestName,
      @SeassonNo,@HumanKind,@AdultType,@HasExtraBed,@ValBoardPlan,@RoomType) ;
    set @ArtNo=@ArtNo+1 end for;
//------------------------------------------------------------------- ivaylo Depozit
  return(@ProformNo)
end


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  and ServiceNo<>-8
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,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) ;
//------------------------------------------------------------------------------------- Ivaylo Depozit
  for f4 as curs4 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  and ServiceNo=-8
do
     set @ArtNumber=@ArtNumber+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,
      HumanKind,AdultType,ExtraBed,BoardPlan,RoomType,
      RowType) 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,
      7) ;
end for;
//------------------------------------------------------------------------------------- Ivaylo Depozit
  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 and ServiceNo<>-8
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,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) ;
//------------------------------------------------------------------------------------- Ivaylo Depozit
  for f4 as curs4 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 and ServiceNo=-8
do
      set @ArtNumber=@ArtNumber+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,
      HumanKind,AdultType,ExtraBed,BoardPlan,RoomType,
      RowType) 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,
      7) ;
end for;
//------------------------------------------------------------------------------------- Ivaylo Depozit
  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


GO


IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('FinishVaucherBill'))) THEN
Drop function FinishVaucherBill
END IF

GO

create function
DBA.FinishVaucherBill(in @PayHow integer,in @InvoiceType integer,in @InvoiceDate integer,in @KodAdmin integer,in @Whom varchar(50),in @TotalNoVAT decimal(12,2),in @VAT decimal(12,2),in @GrandTotal decimal(12,2),in @BillType integer,in @No integer,in @IsInvoice integer,in @VidSmetka integer,in @ExchangeRate decimal(12,4),in @ContractNo integer)
returns integer
//Insertva dannite za fakturata vuv Invoices i Articles i insertva parite v ResMoney
//@No e syotvetno nomer na grupa, rezervaciq, gost, staia i anulirana smetka, ako smetkata e za grupa, rezervacia, gost, staia ili anulirashta
begin atomic
  declare @InvoiceNo integer;
  declare @DepositNo integer;
  declare @TrueInvoiceNo integer;
  declare @CancelInvoiceNo integer;
  declare @ServiceNo integer;
  declare @CreditNo integer;
  declare @ArtNum integer;
  declare @ResMoneyStatus integer;
  declare @SmetkaCurrency integer;
  declare @NullDepositCount integer;
  declare @InvoiceMask integer; //ivaylo
  if @BillType <> 7 then
    select Count(ServiceNo) into @NullDepositCount from #Articles where ArticleQty = 0 and ServiceNo = 1005
  end if;
  if @BillType = 7 then
    select Count(ServiceNo) into @NullDepositCount from #Articles where ArticleQty = 0 and ServiceNo = 1004
  end if;
  //Vzima Tipa valuta ot vida smetka
  select KodCurrency into @SmetkaCurrency from VidSmetki where N = @VidSmetka;
  //Iztriva nulevite kolichestva
  delete from #VaucherArticles where TotalNights = 0;
  //Vmukva dannite za fakturata
    set @InvoiceNo=GetCounter('InvoiceCounter'); //    //ivaylo
  if @IsInvoice = 1 then
    set @TrueInvoiceNo=GetCounter('TrueInvoiceNo') //TrueVaucherInvoiceNo ivaylo
  else
    set @TrueInvoiceNo=0
  end if;
  if @BillType = -1 then
    set @CancelInvoiceNo=@No
  else
    set @CancelInvoiceNo=0
  end if;
  set @ResMoneyStatus=1;
  set @ArtNum=1;
  select IntStojnost into @InvoiceMask from Registers where RegisterName = 'MaskaFaktura'; //ivaylo
  //Trqbva da se dobavi i nomera na dogovora pri insert-a na nova smetka
  insert into VaucherInvoices( VaucherInvoiceNo,Status,PayHow,InvoiceType,InvoiceDate,BankTown,Upolnomosten,
    UpolnomDate,UpolnomNum,KodAdmin,Whom,MOL,WhomTown,WhomDanNum,Bulstat,KodCurrency,
    TotalNoVAT,VAT,GrandTotal,CancelInvoiceNo,TrueInvoiceNo,WithoutVAT,TrueInvoiceDate,ExchangeRate,VidSmetka,ContractNo,MaskaFaktura) values(
    @InvoiceNo,1,@PayHow,@InvoiceType,@InvoiceDate,'','','','',@KodAdmin,
    @Whom,'','','','',@SmetkaCurrency,@TotalNoVAT,@VAT,@GrandTotal,@CancelInvoiceNo,@TrueInvoiceNo,0,FloatToDate(@InvoiceDate),@ExchangeRate,@VidSmetka,@ContractNo,@InvoiceMask) ;
  //Insertva kato zapis sumata ot vsi4ki vau4eri po dogovor v ServiceKredits kato usluga "VTD"
  set @CreditNo=GetCounter('CreditCounter');
  insert into ServiceCredits( DateCredit,GuestNo,Service,SumCredit,KodCurrency,KodAdmin,NumberServices,CreditNo,PayedServices,WhoPays,GroupPays,ContractPays,RoomPays,PBXLogN,DateCreditDt,RoomNum) 
    select @InvoiceDate,0,ServiceNo,sum(GrandTotal),@SmetkaCurrency,@KodAdmin,1,@CreditNo,1,0,0,@ContractNo,0,null,FloatToDate(@InvoiceDate),null from
      #VaucherArticles where
      VaucherN <> 0
      group by ServiceNo;
  //Insertva zapis vuv Articles za suma s vaucheri po dogovor
  for f0 as curs0 scroll cursor for
    select 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,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
      VaucherN <> 0 order by
      VaucherN asc do
    insert into VaucherArticles( InvoiceNo,ArticleNo,VaucherN,
      VaucherNum,VaucherSeason,VaucherBoard,VaucherRoomType,VaucherBedType,Adult,Child,Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,
      SeasonNo,HumanKind,AdultType,ExtraBed,BoardPlan,RoomType,ForVaucherN) values( 
      @InvoiceNo,@ArtNum,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan,@Room,@AdditionBed,@Adult,@Child,@Nights,@TotalNights,
      @RealPrice,@GrTotal,@ArticleName,@GuestName,@CreditNo,@Disc,@ServiceNum,@SmetkaCurrency,@TranslateName,@TranslateSeason,@TranslateBed,@TranslateGuestName,
      @SeassonNo,@HumanKind,@AdultType,@HasExtraBed,@ValBoardPlan,@RoomType,@ForVaucherN) ;
    set @ArtNum=@ArtNum+1; 
    end for;
  for f2 as curs2 scroll cursor for
    select v.VaucherN as @Vaucher from
      #VaucherArticles as va left outer join
      VtdVauchers as v on
      (v.VaucherN = va.VaucherN) where
      v.VaucherN is not null and
      v.ContractNo = @ContractNo
      group by v.VaucherN do
    if @BillType <> -1 then
      update VtdVauchers set TrueInvoceNo = @TrueInvoiceNo where ContractNo = @ContractNo and VaucherN = @Vaucher;
      update VtdVauchers set InvoiceNo = @InvoiceNo where ContractNo = @ContractNo and VaucherN = @Vaucher;
    else
      update VtdVauchers set TrueInvoceNo = 0,CancelInvoiceNo = @CancelInvoiceNo where ContractNo = @ContractNo and VaucherN = @Vaucher;
      update VtdVauchers set InvoiceNo = 0 where ContractNo = @ContractNo and VaucherN = @Vaucher;
    end if end for;
  for f3 as curs3 scroll cursor for
    select SinglePrice as @ArticlePrice,GrandTotal as @ArticleSum,TotalNights as @ArticleQty,ArticleName as @ArticleName,Discount as @Discount,
      ServiceNo as @Service,TranslateName as @TranslateName,TranslateSeason as @TranslateSeason,
      TranslateBed as @TranslateBed,ForVaucherN as @ForVaucherN from
      #VaucherArticles where(VaucherN = 0) and(Creditno is not null) do
    insert into VaucherArticles( InvoiceNo,ArticleNo,VaucherN,
      VaucherNum,VaucherSeason,VaucherBoard,VaucherRoomType,VaucherBedType,Adult,Child,Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,CreditNo,Discount,ServiceNo,Currency,TranslateName,TranslateSeason,TranslateBed,ForVaucherN) values( 
      @InvoiceNo,@ArtNum,'','','','','','',0,0,@ArticleQty,@ArticleQty,
      @ArticlePrice,@ArticleSum,@ArticleName,0,@Discount,@Service,@SmetkaCurrency,@TranslateName,@TranslateSeason,@TranslateBed,@ForVaucherN) ;
    set @ArtNum=@ArtNum+1 end for;
  //Ako e anulirane na smetka i anuliranata smetka ima slujebni zapisi za markirane na depositi gi iztriva
  //Promenia CancelInvoiceNo na anuliranata smetka na -1
  if @BillType = -1 then
    delete from DBA.ResMoney where VaucherInvoiceNo = @No and DateMoney = 0;
    update VaucherInvoices set CancelInvoiceNo = -1 where VaucherInvoiceNo = @No;
    select max(Status) into @ResMoneyStatus from ResMoney where VaucherInvoiceNo = @No;
  end if;
  //Nulira depositi, ako e smetka po dogovor
  if ((@BillType = 7) or (@BillType =8)) and(@ContractNo <> 0) then
    call CheckContractDeposits(@ContractNo,@KodAdmin,@InvoiceNo,@NullDepositCount)
  end if;
  //Vmukva plashtaniata ot wremennata tablica za plashtania
  for f4 as curs4 scroll cursor for
    select PaymentType as @PaymentType,Sum as @Sum,KodCurrency as @KodCurrency,AccountNumber as @AccountNumber,
      GuestNo as @mGuestNo,GroupNo as @mGroupNo,RoomNo as @mRoomNo,ReserveNo as @mReserveNo,PTVid as @PTVid from
      #PaymentTypes do
    set @DepositNo=GetCounter('DepositCounter');
    insert into ResMoney( ReserveNo,DateMoney,KodAdmin,Sum,Currency,Payment,Note,GuestNo,DepositNo,Status,GroupNo,
      ContractNo,VaucherInvoiceNo,RoomNo,AccountNumber,PTVid) values( 
      @mReserveNo,@InvoiceDate,@KodAdmin,@Sum,@KodCurrency,@PaymentType,'',@mGuestNo,@DepositNo,@ResMoneyStatus,@mGroupNo,@ContractNo,@InvoiceNo,@mRoomNo,@accountNumber,@PTVid) ;
    end for;
  return(@InvoiceNo)
end