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

GO

create  procedure
DBA.VaucherExtresType(inout @ArticleNumber integer,in @VaucherNo integer,in @VaucherNum varchar(30),in @BoardPlanNo integer,in @NumAdult integer,in @Numchild integer,in @ServiceCount integer,in @ServiceNum integer,in @NumPeople integer,in @ArticleName varchar(50),in @GuestName varchar(50),in @Disc double,in @HumanKind integer,in @AdultType integer,in @HasExtraBed integer,in @RoomType integer,in @ContractNo integer,in date1 date,in date2 date,in @NumLodgings integer)
begin
  declare @SeassonPriority varchar(200);
  declare @TimePriority varchar(200);
  //  declare date1 date;
  //  declare date2 date;
  declare cdate date;
  declare @Season integer;
  declare br integer;
  declare @SeasonStr varchar(50);
  declare @FreeDays integer;
  declare @OstDays integer;
  declare @SeaIndex integer;
  declare @SeaDays integer;
  declare @FreeDaysStr varchar(50);
  declare @ExtType integer;
  declare @BrSeas integer;
  declare @MaxIndex integer;
  declare @MinIndex integer;
  declare @MaxSeason integer;
  declare @MinSeason integer;
  declare @cIndex integer;
  declare ii integer;
  declare @Sea1 integer;
  declare @brNights integer;
  declare @brFreeDays integer;
  declare @PriceKindStr varchar(50);
  declare @PriceK varchar(50);
  declare @PriceKindInt integer;
  declare @Seasson varchar(50);
  declare @BoardPlan varchar(20);
  declare @Room varchar(20);
  declare @AdditionBed varchar(20);
  declare @RealPrice double;
  declare @Currency integer;
  declare @Adult integer;
  declare @Child integer;
  set @SeassonPriority='';
  set @TimePriority='';
  //  set @OstDays=2;
  set @PriceKindStr='';
  select ExtresCount,ExtresType into @OstDays,@ExtType from Contracts as c where c.ContractNo = @ContractNo;
  //  set @ExtType=3;
  for f5 as curs5 scroll cursor for
    select distinct SeassonNo as @Sea from
      ContractPrices where
      Contract = @ContractNo do
    set @SeassonPriority=@SeassonPriority || @Sea end for;
  //  set date1='20040610';
  //  set date2='20040825';
  set br=0;
  set @TimePriority='';
  set @SeasonStr='';
  set cdate=days(date1,-1);
  set @Season=(select GetSeassonByDate(@ContractNo,date1));
  set @PriceKindInt=(select GetPriceKindByDate(@ContractNo,date1));
  set @PriceK=(cast(@PriceKindInt as char(3)));
  set @PriceKindStr=@PriceKindStr || substr('000',1,3-length(@PriceK)) || @PriceK;
  set @TimePriority=@Season;
  while(cdate < date2) loop
    set br=br+1;
    set cdate=days(cdate,1);
    set @Season=(select GetSeassonByDate(@ContractNo,cdate));
    if(@Season <> substr(@TimePriority,length(@TimePriority),1)) then
      if @SeasonStr = '' then
        set @SeasonStr=@SeasonStr || cast(substr('000',1,3-length(br-1)) as char(3)) || br-1
      else
        set @SeasonStr=@SeasonStr || cast(substr('000',1,3-length(br)) as char(3)) || br
      end if;
      set @TimePriority=@TimePriority || @Season;
      set @PriceKindInt=(select GetPriceKindByDate(@ContractNo,cdate));
      set @PriceK=(cast(@PriceKindInt as char(3)));
      set @PriceKindStr=@PriceKindStr || substr('000',1,3-length(@PriceK)) || @PriceK;
      //          message 'br='||br;
      set br=0
    end if
  end loop;
  if length(@TimePriority) = 1 then
    set br=br-1
  end if;
  set @SeasonStr=@SeasonStr || cast(substr('000',1,3-length(br)) as char(3)) || br;
  set @BrSeas=CEILING(length(@SeasonStr)/3);
  if @ExtType = 0 then
    set @SeaIndex=1;
    set @SeaDays=substr(@SeasonStr,1,3);
    set @FreeDaysStr='';
    while @OstDays > @SeaDays loop
      set @FreeDaysStr=@FreeDaysStr || cast(substr('000',1,3-length(@SeaDays)) as char(3)) || @SeaDays;
      set @OstDays=@OstDays-@SeaDays;
      set @SeaIndex=@SeaIndex+1;
      set @SeaDays=substr(@SeasonStr,(@SeaIndex-1)*3+1,3)
    end loop;
    if @OstDays > 0 then
      set @FreeDaysStr=@FreeDaysStr || cast(substr('000',1,3-length(@OstDays)) as char(3)) || @OstDays
    end if;
    while @SeaIndex < @BrSeas loop
      set @FreeDaysStr=@FreeDaysStr || '000';
      set @SeaIndex=@SeaIndex+1
    end loop
  end if;
  if @ExtType = 1 then
    set @SeaIndex=@BrSeas;
    //      message '@SeaIndex='||@SeaIndex;
    set @SeaDays=substr(@SeasonStr,(@SeaIndex-1)*3+1,3);
    //      message '@SeaDays='||@SeaDays;
    set @FreeDaysStr='';
    //      set @SeaIndex=@SeaIndex-1;
    while @OstDays > @SeaDays loop
      set @FreeDaysStr=cast(substr('000',1,3-length(@SeaDays)) as char(3)) || @SeaDays || @FreeDaysStr;
      set @OstDays=@OstDays-@SeaDays;
      set @SeaIndex=@SeaIndex-1;
      set @SeaDays=substr(@SeasonStr,(@SeaIndex-1)*3+1,3)
    end loop; //        message '@SeaDays in loop='||@SeaDays;
    //        message '@OstDays in loop='||@OstDays;
    if @OstDays > 0 then
      set @SeaIndex=@SeaIndex-1;
      set @FreeDaysStr=cast(substr('000',1,3-length(@OstDays)) as char(3)) || @OstDays || @FreeDaysStr
    end if;
    //      message '@SeaIndex step last='||@SeaIndex;
    while @SeaIndex > 0 loop
      set @FreeDaysStr='000' || @FreeDaysStr;
      set @SeaIndex=@SeaIndex-1
    end loop
  end if;
  if @ExtType = 2 then
    set @FreeDaysStr='';
    set @MaxIndex=0;
    set @cIndex=1;
    set ii=0;
    while ii < length(@TimePriority) loop
      set ii=ii+1;
      set @Season=substr(@TimePriority,ii,1);
      set @cIndex=locate(@SeassonPriority,cast(@Season as char(1)));
      if @MaxIndex < @cIndex then
        set @MaxIndex=@cIndex
      end if
    end loop;
    set @MaxSeason=substr(@SeassonPriority,@MaxIndex,1);
    set ii=0;
    while(ii < length(@TimePriority)) loop
      set ii=ii+1;
      set @Season=substr(@TimePriority,ii,1);
      if @Season <> @MaxSeason then
        set @FreeDaysStr=@FreeDaysStr || '000'
      else
        set @SeaDays=substr(@SeasonStr,(ii-1)*3+1,3);
        if @OstDays > @SeaDays then
          set @OstDays=@SeaDays
        end if;
        set @FreeDaysStr=@FreeDaysStr || cast(substr('000',1,3-length(@OstDays)) as char(3)) || @OstDays
      end if
    end loop
  end if;
  if @ExtType = 3 then
    set @FreeDaysStr='';
    set @MinIndex=20;
    set @cIndex=1;
    set ii=0;
    while ii < length(@TimePriority) loop
      set ii=ii+1;
      set @Season=substr(@TimePriority,ii,1);
      set @cIndex=locate(@SeassonPriority,cast(@Season as char(1)));
      if @MinIndex > @cIndex then
        set @MinIndex=@cIndex
      end if
    end loop;
    set @MinSeason=substr(@SeassonPriority,@MinIndex,1);
    set ii=0;
    while(ii < length(@TimePriority)) loop
      set ii=ii+1;
      set @Season=substr(@TimePriority,ii,1);
      if @Season <> @MinSeason then
        set @FreeDaysStr=@FreeDaysStr || '000'
      else
        set @SeaDays=substr(@SeasonStr,(ii-1)*3+1,3);
        if @OstDays > @SeaDays then
          set @OstDays=@SeaDays
        end if;
        set @FreeDaysStr=@FreeDaysStr || cast(substr('000',1,3-length(@OstDays)) as char(3)) || @OstDays
      end if
    end loop
  end if;
  if @FreeDaysStr is null then
    set @FreeDaysStr=substr('000000000000000000000000',1,length(@TimePriority)*3)
  end if;
  set br=length(@TimePriority);
  set ii=0;
  while ii < br loop
    set ii=ii+1;
    set @Sea1=substr(@TimePriority,ii,1);
    set @brNights=substr(@SeasonStr,(ii-1)*3+1,3);
    set @brFreeDays=substr(@FreeDaysStr,(ii-1)*3+1,3);
    set @brNights=@brNights-@brFreeDays;
    set @PriceKindInt=substr(@PriceKindStr,(ii-1)*3+1,3);
    select Description into @Seasson from PriceSeasons where SeasonNo = @Sea1;
    select BoardPlanName into @BoardPlan from BoardPlan where N = @BoardPlanNo;
    select ShortName into @Room from Classes where Class = @RoomType;
    select Deskription into @AdditionBed from ExtraBed where N = @HasExtraBed;
    select PriceAll,KodCurrency into @RealPrice,@Currency from ContractsPriceList where
      ContractNo = @ContractNo and
      ClassNo = @RoomType and
      PriceKindNo = @PriceKindInt and
      HumanKind = @HumanKind and
      AdultType = @AdultType and
      SeasonNo = @Sea1 and
      BoardPlan = @BoardPlanNo and
      ExtraBed = @HasExtraBed;

    set @RealPrice=(@RealPrice/1.07);
    if @AdultType = 0 then
      set @Adult=@NumPeople;
      set @Child=0
    else
      set @Adult=0;
      set @Child=@NumPeople
    end if;
    if @ExtType = 0 then
      if @brFreeDays > 0 then
        // Tuk sa Nostuvki koito sa bezplatni
        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(
          @ArticleNumber,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan,
          @Room,@AdditionBed,'απ.',@Adult,@Child,
          @brFreeDays,@NumPeople*@brFreeDays,0,0,@ArticleName,
          @GuestName,0,@Disc,@ServiceNum,@Currency,
          @ArticleName,@Seasson,@AdditionBed,@GuestName,@Sea1,
          @HumanKind,@AdultType,@HasExtraBed,@BoardPlanNo,@RoomType,
          1,@VaucherNo) ;
        set @ArticleNumber=@ArticleNumber+1
      end if;
      if @brNights > 0 then
        // Tuk sa Nostuvki koito sa plateni
        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(
          @ArticleNumber,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan,
          @Room,@AdditionBed,'απ.',@Adult,@Child,
          @brNights,@NumPeople*@brNights,@RealPrice,CalcPriceWithDiscount(@Disc,(@NumPeople*@brNights*@RealPrice)),@ArticleName,
          @GuestName,0,@Disc,@ServiceNum,@Currency,
          @ArticleName,@Seasson,@AdditionBed,@GuestName,@Sea1,
          @HumanKind,@AdultType,@HasExtraBed,@BoardPlanNo,@RoomType,
          1,@VaucherNo) ;
        set @ArticleNumber=@ArticleNumber+1
      end if
    else
      if @brNights > 0 then
        // Tuk sa Nostuvki koito sa plateni
        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(
          @ArticleNumber,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan,
          @Room,@AdditionBed,'απ.',@Adult,@Child,
          @brNights,@NumPeople*@brNights,@RealPrice,CalcPriceWithDiscount(@Disc,(@NumPeople*@brNights*@RealPrice)),@ArticleName,
          @GuestName,0,@Disc,@ServiceNum,@Currency,
          @ArticleName,@Seasson,@AdditionBed,@GuestName,@Sea1,
          @HumanKind,@AdultType,@HasExtraBed,@BoardPlanNo,@RoomType,
          1,@VaucherNo) ;
        set @ArticleNumber=@ArticleNumber+1
      end if;
      if @brFreeDays > 0 then
        // Tuk sa Nostuvki koito sa bezplatni
        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(
          @ArticleNumber,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan,
          @Room,@AdditionBed,'απ.',@Adult,@Child,
          @brFreeDays,@NumPeople*@brFreeDays,0,0,@ArticleName,
          @GuestName,0,@Disc,@ServiceNum,@Currency,
          @ArticleName,@Seasson,@AdditionBed,@GuestName,@Sea1,
          @HumanKind,@AdultType,@HasExtraBed,@BoardPlanNo,@RoomType,
          1,@VaucherNo) ;
        set @ArticleNumber=@ArticleNumber+1
      end if
    end if
  end loop
end



GO

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;
  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;
  message 'PrepareVauchersBill Step1111' type info to console;
  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;
  message 'PrepareVauchersBill Step2' type info to console;
  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
    // New block  Joracio
    message 'Step1111111' type info to console;
    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;
    message 'PrepareVauchersBill Step5' type info to console;
    set @VVVAucherNum=cast(@VaucherNum as varchar(30));
    //  if(@TotalSum<>0) then
    select sum(GrandTotal) into @TotalSumForVaucher from #VaucherArticles where VaucherN = @VaucherNo;
    message '@ArticleNumber=' || @ArticleNumber || '    @TotalSumForVaucher=' || @TotalSumForVaucher type info to console;
    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;
    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,null,null,
      null,null,null,null,null,
      null,null,0,0) ;
    set @ArticleNumber=@ArticleNumber+1 end for;
  //set @ArticleNumber=@ArticleNumber+1 end for end for;
  //message 'Total na vaucheri -> '+cast(@TotalSum as varchar);
  //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;
    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,null,null,
      null,null,null,null,null,
      null,null,0,0) ;
    set @ArticleNumber=@ArticleNumber+1;
    set @ADult=0;
    set @Child=0
  end if;
  //Vzema i smetkosva svobodnite uslugi koito sa % ili suma
  message '@ArticleNumber=' || @ArticleNumber || ',@Currency=' || @Currency type info to console;
  message ',@TotalSumForVaucher=' || @TotalSumForVaucher || ',@ContractNo=' || @ContractNo type info to console;
  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 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;
    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
end

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;
  //message cast(@NullDepositCount as varchar);
  //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
  message '@InvoiceNo -> '+cast(@InvoiceNo as varchar) type info to console;
  if @IsInvoice = 1 then
    set @TrueInvoiceNo=GetCounter('TrueInvoiceNo') //TrueVaucherInvoiceNo ivaylo
  else
    set @TrueInvoiceNo=0
  end if;
  //message cast(@TrueInvoiceNo as varchar);
  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
  message 'Faktura('+cast(@TrueInvoiceNo as varchar)+')' type info to console;
  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
    message 'GOST >> ' || @TranslateGuestName type info to console;
    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;
    message 'COUNTERA('+cast(@ArtNum as varchar)+')' type info to console end for;
  message 'IZLIZA OT PYRVIQ FOR...' type info to console;
  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
    message 'Dogovor nomer = '+cast(@ContractNo as varchar) type info to console;
    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;
  message 'Step2' type info to console;
  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
    message 'Step3' type info to console;
    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
  message 'Step4' type info to console;
  if @BillType = -1 then
    message 'MINAVA OT TUKA...' type info to console;
    delete from DBA.ResMoney where VaucherInvoiceNo = @No and DateMoney = 0;
    update VaucherInvoices set CancelInvoiceNo = -1 where VaucherInvoiceNo = @No;
    message '@ResMoneyStatus -> '+cast(@No as varchar) type info to console;
    select max(Status) into @ResMoneyStatus from ResMoney where VaucherInvoiceNo = @No;
    message '@ResMoneyStatus -> '+cast(@ResMoneyStatus as varchar) type info to console
  end if;
  //Nulira depositi, ako e smetka po dogovor
  if(@BillType = 7) 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');
    message 'GostNomer -> '+cast(@mGuestNo as varchar) type info to console;
    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) ;
    message 'Minava prez depozitite ' type info to console end for;
  return(@InvoiceNo)
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 @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=1;
  set @OldForVaucherN=0;
  //  set @TotalSumForVaucher=0;
  //Popylva wremennata tablica za artikulite
  message 'Step1111' type info to console;
  message 'Begin of procedure PreparePrAgainVaucherBill' type info to console;
  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
      message 'Veche Sum Vutre' type info to console;
      set @TotalSumForVaucher=@TotalSumForVaucher+@GrTotal;
      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) ;
      set @ArtNumBr=@ArtNumBr+1 end for;
    // **************************
    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;
  message 'V kraia na fora' type info to console;
  select sum(GrandTotal) into @TotalSum
    from VaucherArticles where
    InvoiceNo = @SmetkaNo and
    ForVaucherN <> 0 and
    VaucherN <> 0;
  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,null,null,
    null,null,null,null,null,null,
    null,null,null,0,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) ;
  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,null,null,
    null,null,null,null,null,null,
    null,null,null,0,0) ;
  set @ArtNumBr=@ArtNumBr+1;
  //  Sega Dopulnitelnite uslugi
  message 'Sega sum tuk Step2' type info to console;
  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
    message 'Veche Sum Vutre Step2' type info to console;
    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) ;
    set @ArtNumBr=@ArtNumBr+1 end for;
  message 'V kraia na fora Step2' type info to console;
  select sum(GrandTotal) into @TotalAddSum
    from VaucherArticles where
    InvoiceNo = @SmetkaNo and
    ForVaucherN = 0 and
    VaucherN = 0;
  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;
  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,null,null,
    null,null,null,null,null,null,
    null,null,null,0,0) ;
  set @ArtNumBr=@ArtNumBr+1;
  //   ***********************  end for ****************************************
  /*  select sum(GrandTotal) into @TotalSum from #VaucherArticles where RowType=5;
  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,@TotalSum,null,
  null,null,null,null,null,null,
  null,null,null,6,0);
  */
  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
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('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;
  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;
    delete from #VaucherArticles where
      RowType = 0 and
      ArticleNumber >= 1 and
      ArticleNumber <= (select max(ArticleNumber) from #VaucherArticles where RowType = 1)
  end if
end