IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('PrepareReserveBill')))
THEN Drop procedure PrepareReserveBill
END IF

GO

create procedure DBA.PrepareReserveBill( in @ReserveNo integer,in @place varchar(50) ) 
begin
  declare @ArticleNumber integer;
  declare @Deposit double;
  declare @Disc double;
  delete from #Articles;
  select Max(ArticleNumber)+1 into @ArticleNumber from #Articles;
  if @ArticleNumber is null then set @ArticleNumber = 1
  end if;
  for f0 as curs0 scroll cursor for
    select B.RegNum as @RegNo,dateadd(day,B.PayedLodgings,B.DateRegDt) as @DateReg,B.Guest as @Guest,B.WhoPays as @WhoPays,
      B.Room as @Room,R.RoomName as @RoomName,(B.NumLodgings-B.PayedLodgings) as @NumDays,B.Price as @Price,B.KodCurrency as @KodCurrency, /*M*/ /*M*/
      B.IsRest as @IsRest,B.DateRegDt as @RDate
      from Book as B left outer join Rooms as R on B.Room = R.RoomNum,Guests /*,Rooms as R*/
      where(B.NumLodgings-B.PayedLodgings) <> 0 and B.WhoPays = Guests.GuestNum and Guests.Reservation = @ReserveNo do /*and B.Room=R.RoomNum*/
    set @ArticleNumber = @ArticleNumber+FillLodgingsForBill(@RegNo,@DateReg,@Room,@NumDays,@Price,@KodCurrency,@IsRest,
      @ArticleNumber,@place,@RDate) end for;
  for f1 as curs1 scroll cursor for
    select SC.CreditNo as @CreditNo,
      S.ServiceName as @ArticleName,
      SC.RoomNum as @Room,
      R.RoomName as @RoomName, /*M*/ /*M*/
      (SC.SumCredit*C.ExchangeRate) as @Price,
      SC.Service as @Service,
      (NumberServices-PayedServices) as @ArticleQty,
      VS.TipDDS as @TipDDS,
      SC.WhoPays as @WhoPays,
      SC.DateCreditDt as @DateCr
      from ServiceCredits as SC left outer join Rooms as R on SC.RoomNum = R.RoomNum,Services as S,VidSdelki as VS,Currencies as C,Guests /*,Rooms as R*/
      where(NumberServices-PayedServices) <> 0 and SC.Service = S.ServiceNo and S.TipSdelka = VS.N and SC.KodCurrency = C.KodCurrency
      and SC.WhoPays = Guests.GuestNum and Guests.Reservation = @ReserveNo do /*and SC.RoomNum=R.RoomNum*/
    if(@Room is not null) then
      --Maria
      //set @ArticleName=@ArticleName || @place || cast(@Room as varchar)
      set @ArticleName = @ArticleName || @place || @RoomName
    else --Maria
      set @ArticleName = @ArticleName
    end if;
    select distinct Max(Contracts.Discount) into @Disc from Contracts,Guests where Guests.Contract = Contracts.ContractNo and Guests.GuestNum = @WhoPays;
    /*
if(@Disc<>0) then
set @Price=CalcPriceWithDiscount(@Disc,@Price)
end if
;
*/
    call FillServicesForBill(@ArticleNumber,@ArticleName,@ArticleQty,@Price,@CreditNo,@TipDDS,@Service,@Disc,@DateCr);
    set @ArticleNumber = @ArticleNumber+1 end for;
  select sum(ResMoney.Sum*Currencies.ExchangeRate)
    into @Deposit from ResMoney left outer join Guests on ResMoney.GuestNo = Guests.GuestNum,Currencies
    where ResMoney.Status = 2 and ResMoney.Currency = Currencies.KodCurrency
    and(ResMoney.ReserveNo = @ReserveNo or Guests.Reservation = @ReserveNo);
  if @Deposit is null then set @Deposit = 0
  end if;
  select if VS.TipDDS = 3 then 0 else VR.Rate endif,
    VR.DDSType,VS.TipDDS into @VatRate,@DDSType,@aTipDDS
    from Services as S join VatRates as VR on S.VatRateN = VR.ID
      join VidSdelki as VS on S.TipSdelka = VS.N
    where S.ServiceNo = 1005;
  set @Deposit = @Deposit/(1+@VatRate);
  if @Deposit <> 0 then
    insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo,
      CreditNo,ArticleSum,TipDDS,ServiceNo,RegDate,VatRate,DDSType ) values( @ArticleNumber,'Депозит(и)','бр.',-1,0,-1,@Deposit,0,
      0,0,-@Deposit,2,1005,null,@VatRate,@DDSType ) 
  end if
end
//[v 18.02.2003]