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

GO

create function DBA.CalcServiceCreditsSum( in @GuestNo integer ) 
returns decimal(12,4)
// Vryshta sumata na neplatenite dopulnitelni uslugi za @GuestNo v mestna valuta (leva).
begin
  declare @R 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;
  return(@R)
end
//[v 2008.12.09 - MON] - Nixon - created