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

GO

create function DBA.CalcServiceSum( in @GuestNo integer ) 
returns decimal(12,4)
// Vryshta sumata na neplatenite uslugi za @GuestNo v mestna valuta (leva).
begin
  declare @R decimal(12,4);
  declare @SpaR decimal(12,4);
  declare @TempB decimal(12,4);
  declare @B decimal(12,4);
  declare @HumanKind integer;
  declare @DDS decimal(12,4);
  select sum(if VidSdelki.TipDDS <> 2 then
      (ServiceCredits.NumberServices-ServiceCredits.PayedServices)*ServiceCredits.SumCredit*Currencies.ExchangeRate
    else(1+VatRates.Rate)*(ServiceCredits.NumberServices-ServiceCredits.PayedServices)*ServiceCredits.SumCredit*Currencies.ExchangeRate
    endif)
    into @R from ServiceCredits,Currencies,Services,VidSdelki,VatRates
    where ServiceCredits.WhoPays = @GuestNo
    and ServiceCredits.Service = Services.ServiceNo
    and Services.TipSdelka = VidSdelki.N
	and Services.VatRateN = VatRates.ID
    and ServiceCredits.KodCurrency = Currencies.KodCurrency
    and((ServiceCredits.SPaReservationFk = -1) or(ServiceCredits.SPaReservationFk <> -1 and ServiceCredits.SpaTransferFlag = 1));
  if @R is null then set @R = 0
  end if;
  set @B = 0;
  for f as curs scroll cursor for
    select RegNum as @RegNo from Book where Book.WhoPays = @GuestNo do
    call FillArticleLodgings(@RegNo,0,1,'');
    select sum(if TipDDS = 2 then Qty*Price*(1+VatRate) else Qty*Price endif)
      into @TempB from #ArticleLodgings;
    if @TempB is null then set @TempB = 0
    end if;
    set @B = @B+@TempB end for;
  return(@R+@B)
end
//[v 01.01.11]
//[v 01.07.02] - Niki
//[v 2008.12.10 - MON] - Nixon - da vzima vpredvid cena na mesec