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

GO

create function DBA.GetExtraSum( in @RoomNo integer ) 
returns double
begin
  //tazi funkciq vryshta sumata, koqto poprincip studenta shte plati, no za momenta oshte ne e nalojitelno
  //naprimer ako sme mesec mart, naema za mesec april shte trqbva da go plati, no ne i mart
  declare rez double;
  declare @NumMonths double;
  declare @PackagePrice double;
  declare @Price double;
  set rez = 0.0;
  for f1 as curs scroll cursor for
    select FloatToDate(Datereg+NumLodgings) as @DLDt,RegNum as @RegNo,Price as @Prc from Book where Room = @RoomNo and Staying = 1 do
    select Sum(if VidSdelki.TipDDS = 2 then(1+VR.Rate)*Packages.Price*Currencies.ExchangeRate else Packages.Price*Currencies.ExchangeRate endif)
      into @PackagePrice from Packages,Currencies,Services join VATRates as VR on Services.VatRateN = VR.ID,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.0
    end if;
    call FillArticleLodgings(@RegNo,@PackagePrice,0,'');
    set rez = rez+(select SUM(Price) from #ArticleLodgings where Qty = 0) end for;
  if rez is null then set rez = 0.0
  end if;
  return(rez)
end
//Dobrin - created 22.05.09