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 @ServiceNoLodging integer;
  declare @I integer;
  declare @Disc double;
  select distinct Max(Contracts.Discount) into @Disc from Contracts,Guests,Book where Guests.Contract = Contracts.ContractNo and Guests.GuestNum = Book.Guest and Book.RegNum = @RegNo; //nt
  select(if Guests.HumanKind = 1 then(-1) else(-2) endif) into @ServiceNoLodging from Book,Guests where Book.RegNum = @RegNo and Book.Guest = Guests.GuestNum;
  select(ExchangeRate*@Price) into @ArticlePrice from Currencies where KodCurrency = @KodCurrency;
  set @ArticlePrWithDDS=@ArticlePrice; //nt
  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;
  select V.TipDDS into @TipDDS from Services as S,VidSdelki as V where S.ServiceNo = @ServiceNoLodging and S.TipSdelka = V.N;
  if @TipDDS = 2 then
    set @ArticlePrice=@ArticlePrice*(bvrDDSPart+1); //ako dds-to e vkliucheno v cenata
    set @TipDDS=1
  end if;
  set @ArticlePrice=@ArticlePrice-@PackagePrice;
  if @TipDDS = 1 then
    set @ArticlePrice=@ArticlePrice/(bvrDDSPart+1); //ako dds-to ne e vkliucheno v cenata
    set @TipDDS=2
  end if;
  if @IsRest = 1 then set @ArticleName='Почивка ' || @place || cast(@Room as varchar(5))+' за '+dateformat(@DateReg,'Dd.Mm')
  else
    set @ArticleName='Нощувка ' || @place || cast(@Room as varchar(5))+' от '+dateformat(@DateReg,'Dd.Mm')+' до '+dateformat(@DateReg+@NumDays,'Dd.Mm')
  end if;
  set @ArticlePrWithDDS=@ArticlePrWithDDS-@PackagePrWithDDS; //nt
  insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo,
    CreditNo,ArticleSum,TipDDS,ServiceNo,PriceInvWithDDS,ArticleSumWithDDS,RegDate) values( @ArticleNumber,@ArticleName,'Бр.',
    @NumDays,@NumDays,0,@ArticlePrice,@Disc,@RegNo,0,((@ArticlePrice*@NumDays)-((@ArticlePrice*@NumDays)*@Disc)/100),
    @TipDDS,@ServiceNoLodging,cast(FormatNum(@ArticlePrWithDDS,'0.00') as double),
    ((@ArticlePrWithDDS*@NumDays)-((@ArticlePrWithDDS*@NumDays)*@Disc)/100),@RRDate) ;
  set @I=1;
  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