IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('FillLodgingsForBill')))
THEN Drop procedure FillLodgingsForBill
END IF

GO

create function DBA.FillLodgingsForBill( in @RegNo integer,in @DateReg date,in @Room integer,in @NumDays integer,in @Price double,in @KodCurrency integer,in @IsRest integer,in @ArticleNumber integer,in @place varchar(50),in @RRDate date ) 
returns integer
// Tazi procedura se izpolzva za popylwane vuv wremennata tablica #Articles
// na noshtuvka(pochivka) i uslugite ot paketa, za opredelen zapis ot Book
// Tia se vika ot PrepareGuestBill, PrepareGroupBill i PrepareReserveBill
begin
  declare @ArticleName varchar(40);
  declare @ArticlePrice double;
  declare @ArticlePrWithDDS double; //nt
  declare @PackagePrice double;
  declare @PackagePrWithDDS double; //nt
  declare @TipDDS integer;
  declare @I integer;
  declare @Disc double;
  --Maria
  declare @RoomName varchar(5);
  --Maria
  --Dobrin
  declare @LodgNum integer;
  declare @RealArticleQty real;
  declare @IsForMonth tinyint;
  --Dobrin
  select Sum(if VidSdelki.TipDDS = 2 then(1+bvrDDSPart)*Packages.Price*Currencies.ExchangeRate else Packages.Price*Currencies.ExchangeRate endif)
    into @PackagePrice from Packages,Currencies,Services,VidSdelki
    where Packages.RegNo = @RegNo and Packages.KodCurrency = Currencies.KodCurrency
    and Packages.ServiceNo = Services.ServiceNo and Services.TipSdelka = VidSdelki.N;
  if @PackagePrice is null then
    set @PackagePrice = 0;
    set @PackagePrWithDDS = 0
  else
    set @PackagePrWithDDS = @PackagePrice
  end if;
  call FillArticleLodgings(@RegNo,@PackagePrice,0,@place);
  set @I = 1;
  for f2 as curs2 scroll cursor for
    select Name as @LArticleName,RealLodgingsNum as @LRealLodgings,Price as @LArticlePrice,Qty as @LQty,
      MaxQty as @LMaxQty,MinQty as @LMinQty,ZIndex as @LZIndex,TipDDS as @LTipDDS,ServiceNoLodging as @ServiceNoLodging,
      RentMonth as @RentMonth,RentYear as @RentYear
      from #ArticleLodgings order by ZIndex asc do
    insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo,
      CreditNo,ArticleSum,TipDDS,ServiceNo,PriceInvWithDDS,ArticleSumWithDDS,RegDate,RealLodgNum,ZIndex,RentMonth,RentYear ) 
      values( @ArticleNumber+@I,@LArticleName,'бр.',@LQty,@LMaxQty,@LMinQty,@LArticlePrice,0,@RegNo,0,@LArticlePrice*@LQty,
      @LTipDDS,@ServiceNoLodging,round(@LArticlePrice,2),
      round(@LArticlePrice*@LQty,2),@RRDate,@LRealLodgings,@LZIndex,@RentMonth,@RentYear ) ;
    set @I = @I+1 end for;
  for f as curs scroll cursor for
    select Packages.ServiceNo as @ServiceNo,Packages.Price as @ServicePrice,Packages.KodCurrency as @ServiceCurrency,
      Services.ServiceName as @ServiceName from Packages,Services
      where RegNo = @RegNo and Packages.ServiceNo = Services.ServiceNo do
    select(ExchangeRate*@ServicePrice) into @ArticlePrice from Currencies where KodCurrency = @ServiceCurrency;
    select distinct Max(Contracts.Discount) into @Disc from Contracts,Guests,Book,Packages where Guests.Contract = Contracts.ContractNo and Guests.GuestNum = Book.Guest and Packages.RegNo = Book.RegNum and Book.RegNum = @RegNo; //nt
    select V.TipDDS into @TipDDS from Services as S,VidSdelki as V where S.ServiceNo = @ServiceNo and S.TipSdelka = V.N;
    if @TipDDS = 1 then
      set @ArticlePrice = @ArticlePrice/(bvrDDSPart+1); //ako dds-to e vkliucheno v cenata
      set @TipDDS = 2
    end if;
    set @ArticlePrWithDDS = @ArticlePrice; //nt
    --Maria +, dopulniteln uslugi
    set @ServiceName = @ServiceName || @place || cast(@Room as varchar(5));
    --Maria -
    insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo,
      CreditNo,ArticleSum,TipDDS,ServiceNo,PriceInvWithDDS,ArticleSumWithDDS,RegDate ) values( @ArticleNumber+@I,@ServiceName,'бр.',
      @NumDays,@NumDays,0,@ArticlePrice,@Disc,-@RegNo,0,((@ArticlePrice*@NumDays)-((@ArticlePrice*@NumDays)*@Disc)/100),
      @TipDDS,@ServiceNo,@ArticlePrWithDDS,((@ArticlePrWithDDS*@NumDays)-((@ArticlePrWithDDS*@NumDays)*@Disc)/100),@RRDate ) ;
    set @I = @I+1;
    set @Disc = 0 end for;
  return(@I)
end
//[v 2008.12.10 - MON] - Nixon - da vzima vpredvid cena na mesec
//[v 2008.12.10 - MON.2] - Nixon - да подрежда
//[v 2009.05.23 - MON.3] - Dobrin - да попълва RentMonth, RentYear в #Articles