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

GO

CREATE PROCEDURE "DBA"."PrepareGuestBill"(in @place varchar(50))
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;
  message 'procedure PrepareGuestBill STEP 1' type info to console;
  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
      message 'PrepareGuestBill ' || @RDate type info to console;
      set @ArticleNumber=@ArticleNumber+FillLodgingsForBill(@RegNo,@DateReg,@Room,@NumDays,@Price,@KodCurrency,@IsRest,
        @ArticleNumber,@place,@RDate) end for end for;
  message 'procedure PrepareGuestBill STEP 2' type info to console;
  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
        from ServiceCredits as SC,Services as S,VidSdelki as VS,Currencies as C
        where
            SC.WhoPays = @GuestNo and
            (NumberServices-PayedServices) <> 0 and
            SC.Service = S.ServiceNo and
            S.TipSdelka = VS.N and
            SC.KodCurrency = C.KodCurrency and
            SC.SPaReservationFk=-1 


        union all


        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
        from ServiceCredits as SC,Services as S,VidSdelki as VS,Currencies as C,SpaReservations as spaRes
        where
            SC.WhoPays = @GuestNo and
            (NumberServices-PayedServices) <> 0 and
            SC.Service = S.ServiceNo and
            S.TipSdelka = VS.N and
            SC.KodCurrency = C.KodCurrency and
            SC.SPaReservationFk<>-1 and spaRes.N=SC.SPaReservationFk and spaRes.IfPayed=2




        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;
      //      if(@Disc<>0) then
      //        set @Price=CalcPriceWithDiscount(@Disc,@Price)
      //      end if
      //
      message 'Tuk sum pri PrepareGuestBill @DateCr=' || @DateCr type info to console;
      call FillServicesForBill(@ArticleNumber,@ArticleName,@ArticleQty,@Price,@CreditNo,@TipDDS,@Service,@Disc,@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;
  message 'procedure PrepareGuestBill STEP 4' type info to console
end
//[v 18.02.2003] Jorko