IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('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
  declare @VatRate numeric(3,3);
  declare @DDSType integer;
  declare @aTipDDS integer;
  --Maria
  --declare @RoomName varchar(5);
  --Maria
  //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,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,Book as BB /*,Rooms as R*/
      where(B.NumLodgings-B.PayedLodgings) <> 0 and BB.Room = @RoomNo and B.WhoPays = BB.Guest and BB.Staying = 1 /*and B.Room=R.RoomNum*/
  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,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,Services as S,VidSdelki as VS,Currencies as C,Book as B,Rooms as R /*M*/ /*M*/
      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) or(SC.SPaReservationFk <> -1 and SC.SpaTransferFlag = 1)) and B.Room = R.RoomNum do /*M*/ /*M*/
    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; //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;
  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,today(),@VatRate,@DDSType ) 
  end if
end