IF (EXISTS (select proc_name from sysprocedure where proc_name='SpaPrepareGuestBill')) THEN
DROP procedure SpaPrepareGuestBill
END IF

GO

CREATE PROCEDURE SpaPrepareGuestBill(in @place varchar(50),in SpaServiceNo integer,in @SingalInvoice integer,in @SpaResID integer,@AllServices integer)
result(@Disc integer)
begin
  declare @ArticleNumber integer;
  declare @Deposit double;
  declare @Disc double;
  //  declare @DateCr date;
  delete from #Articles;
  select Max(ArticleNumber)+1 into @ArticleNumber from #Articles;
  if @ArticleNumber is null then set @ArticleNumber=1
  end if;
 /* for g as curs1 scroll cursor for
    select GuestNo as @GuestNo from #GuestsForBill do
    for f as curs scroll cursor for
      select B.RegNum as @RegNo,dateadd(day,B.PayedLodgings,B.DateRegDt) as @DateReg,B.Guest as @Guest,
        B.Room as @Room,(B.NumLodgings-B.PayedLodgings) as @NumDays,B.Price as @Price,B.KodCurrency as @KodCurrency,
        IsRest as @IsRest,B.DateRegDt as @RDate from
        Book as B where
        B.WhoPays = @GuestNo and(B.NumLodgings-B.PayedLodgings) <> 0 do
      set @ArticleNumber=@ArticleNumber+FillLodgingsForBill(@RegNo,@DateReg,@Room,@NumDays,@Price,@KodCurrency,@IsRest,
        @ArticleNumber,@place,@RDate) end for end for;  */
  for gg as curs11 scroll cursor for
    select GuestNo as @GuestNo from #GuestsForBill do
    select distinct Max(Contracts.Discount) into @Disc from Contracts,Guests where Guests.Contract = Contracts.ContractNo and Guests.GuestNum = @GuestNo;
    for ff as cursf scroll cursor for
      select SC.CreditNo as @CreditNo,
        S.ServiceName as @ArticleName,
        SC.RoomNum as @Room,
        (SC.SumCredit*C.ExchangeRate) as @Price,
        SC.Service as @Service,
        (NumberServices-PayedServices) as @ArticleQty,
        VS.TipDDS as @TipDDS,
        SC.DateCreditDt as @DateCr,
        SC.AutomaticService as @AutomaticService,
        SpRes.DiscountStn as @DiscountStn
        from ServiceCredits as SC,Services as S,VidSdelki as VS,Currencies as C,
             SpaReservations as SpRes
        where
        SpRes.N=SC.SPaReservationFk and
        SC.WhoPays = @GuestNo and
        (NumberServices-PayedServices) <> 0 and
        SC.Service = S.ServiceNo and
        (((@AllServices=1) and (S.ServiceType=2)) or ((@AllServices=0) and (S.ServiceNo=SpaServiceNo)))and
        S.TipSdelka = VS.N and
        SC.KodCurrency = C.KodCurrency and
        ((@SingalInvoice=0) or ((@SingalInvoice=1) and (SPaReservationFk=@SpaResID))) do
      if(@Room is not null) then
	    if @AutomaticService = 1 then
           set @ArticleName='* '||@ArticleName || @place || cast(@Room as varchar)
		else
           set @ArticleName=@ArticleName || @place || cast(@Room as varchar)
		end if;
      else
        set @ArticleName=@ArticleName
      end if;
      call FillServicesForBill(@ArticleNumber,@ArticleName,@ArticleQty,@Price,@CreditNo,@TipDDS,@Service,@DiscountStn,@DateCr);
      set @ArticleNumber=@ArticleNumber+1 end for end for;
  set @Deposit=0;
  message 'procedure PrepareGuestBill STEP 3' type info to console;
  for gk as cursgk scroll cursor for
    select GuestNo as @GuestNo from #GuestsForBill do
    set @Deposit=@Deposit+CalcDepositSum(@GuestNo)/(1+bvrDDSPart) end for;
  if @Deposit <> 0 then
    insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo,
      CreditNo,ArticleSum,TipDDS,ServiceNo,RegDate) values( @ArticleNumber,'Депозит(и)','бр.',-1,0,-1,@Deposit,0,
      0,0,-@Deposit,2,1005,null)
  end if;
end