IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('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 @VatRate numeric(3,3);
  declare @DDSType integer;
  declare @aTipDDS integer;
  //  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,R.RoomName as @RoomName,(B.NumLodgings-B.PayedLodgings) as @NumDays,B.Price as @Price,B.KodCurrency as @KodCurrency, /*M*/ /*M*/
        IsRest as @IsRest,B.DateRegDt as @RDate
        from Book as B left outer join Rooms as R on B.Room = R.RoomNum /*,Rooms as R*/
        where B.WhoPays = @GuestNo and(B.NumLodgings-B.PayedLodgings) <> 0 do /*and B.Room=R.RoomNum*/
      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,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.DateCreditDt as @DateCr,
        SC.AutomaticService as @AutomaticService
        from ServiceCredits as SC left outer join Rooms as R on SC.RoomNum = R.RoomNum,Services as S,VidSdelki as VS,Currencies as C /*,Rooms as R*/
        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.RoomNum=R.RoomNum*/
        and((SC.SPaReservationFk = -1) or(SC.SPaReservationFk <> -1 and SC.SpaTransferFlag = 1)) do
      if(@Room is not null) then
        if @AutomaticService = 1 then
          --Maria
          --set @ArticleName='* ' || @ArticleName || @place || cast(@Room as varchar)
          set @ArticleName = '* ' || @ArticleName || @place || @RoomName
        else
          --Maria
          --set @ArticleName=@ArticleName || @place || cast(@Room as varchar)
          set @ArticleName = @ArticleName || @place || @RoomName
        end if
      else set @ArticleName = @ArticleName
      end if;
      //      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 end for;
  set @Deposit = 0;
  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;
  for gk as cursgk scroll cursor for
    select GuestNo as @GuestNo from #GuestsForBill do
    set @Deposit = @Deposit+CalcDepositSum(@GuestNo)/(1+@VatRate) end for;
  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] Jorko