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

GO

CREATE PROCEDURE "DBA"."PrepareRoomBill"(in @RoomNo integer,in @place varchar(50))
// Tazi procedura se izpolzva za napylwane na wremennata tablica #Articles
// sus noshtuvkite ,pochivkite  i uslugite koito triabva da plashtat gostite, koito sa v dadena staia
begin
  declare @ArticleNumber integer;
  declare @Deposit double;
  declare @Disc double; //nt
  //Iztriva wremennata tablica za smetka
  delete from #Articles;
  //
  select Max(ArticleNumber)+1 into @ArticleNumber from #Articles;
  if @ArticleNumber is null then set @ArticleNumber=1
  end if
  ; //Vkarva noshtuvkite, pochivkite i zastrahovkite, koito triabva da plashtat ot gostite ot staiata
  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,(B.NumLodgings-B.PayedLodgings) as @NumDays,B.Price as @Price,B.KodCurrency as @KodCurrency,
      B.IsRest as @IsRest,B.DateRegDt as @RDate
      from Book as B,Book as BB
      where(B.NumLodgings-B.PayedLodgings)<>0 and BB.Room=@RoomNo and B.WhoPays=BB.Guest and BB.Staying=1 do
    //Vmukva vuv Vremennata tablica
    set @ArticleNumber=@ArticleNumber+FillLodgingsForBill(@RegNo,@DateReg,@Room,@NumDays,@Price,@KodCurrency,@IsRest,
    @ArticleNumber,@place,@RDate) end for;
  //Vkarva uslugite, koito triabva da plashtat gostite ot staiata
  for f1 as curs1 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.WhoPays as @WhoPays,
        SC.DateCreditDt as @DateCr
      from ServiceCredits as SC,Services as S,VidSdelki as VS,Currencies as C,Book as B
      where(NumberServices-PayedServices)<>0 and SC.Service=S.ServiceNo and S.TipSdelka=VS.N and SC.KodCurrency=C.KodCurrency
           and SC.WhoPays=B.Guest and B.Staying=1 and B.Room=@RoomNo 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.WhoPays as @WhoPays,
        SC.DateCreditDt as @DateCr
      from ServiceCredits as SC,Services as S,VidSdelki as VS,Currencies as C,Book as B,SpaReservations as spaRes
      where(NumberServices-PayedServices)<>0 and SC.Service=S.ServiceNo and S.TipSdelka=VS.N and SC.KodCurrency=C.KodCurrency
           and SC.WhoPays=B.Guest and B.Staying=1 and B.Room=@RoomNo and
           SC.SPaReservationFk<>-1 and spaRes.N=SC.SPaReservationFk and spaRes.IfPayed=2
  do
    if(@Room is not null) then
      set @ArticleName=@ArticleName||@place||cast(@Room as varchar)
    else
      set @ArticleName=@ArticleName
    end if
    ;
    select distinct Max(Contracts.Discount) into @Disc from Contracts,Guests where Guests.Contract=Contracts.ContractNo and Guests.GuestNum=@WhoPays; //nt
    /*
    if(@Disc<>0) then //nt
    set @Price=CalcPriceWithDiscount(@Disc,@Price)
    end if //nt
    ;
    */
    call FillServicesForBill(@ArticleNumber,@ArticleName,@ArticleQty,@Price,@CreditNo,@TipDDS,@Service,@Disc,@DateCr);
    set @ArticleNumber=@ArticleNumber+1 end for;
  //Izchisliava dali ima depositi i gi vkarva
  select sum(ResMoney.Sum*Currencies.ExchangeRate) into @Deposit
    from ResMoney,Currencies,Book
    where ResMoney.Status=2 and ResMoney.Currency=Currencies.KodCurrency
    and ResMoney.GuestNo=Book.Guest and Book.Staying=1 and Book.Room=@RoomNo;
  if @Deposit is null then set @Deposit=0
  end if
  ;
  set @Deposit=@Deposit/(1+bvrDDSPart);
  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,today(*))
  end if
end