IF (EXISTS (select proc_name from sysprocedure where proc_name='CalcServiceSum')) THEN
  Drop function 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 @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+bvrDDSPart)*(ServiceCredits.NumberServices-ServiceCredits.PayedServices)*ServiceCredits.SumCredit*Currencies.ExchangeRate
    endif) into @R
    from ServiceCredits,Currencies,Services,VidSdelki
    where ServiceCredits.WhoPays=@GuestNo
    and ServiceCredits.Service=Services.ServiceNo
    and Services.TipSdelka=VidSdelki.N
    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;
  select(if HumanKind=1 then-1 else-2 endif) into @HumanKind from Guests where GuestNum=@GuestNo;
  select(if VidSdelki.TipDDS<>2 then 1 else(1+bvrDDSPart) endif) into @DDS
    from Services,VidSdelki
    where Services.ServiceNo=@HumanKind and Services.TipSdelka=VidSdelki.N;
  select sum(@DDS*(Book.NumLodgings-Book.PayedLodgings)*Book.Price*Currencies.ExchangeRate) into @B
    from Book,Currencies
    where Book.WhoPays=@GuestNo
    //    and Book.NumLodgings<>0
    and Book.KodCurrency=Currencies.KodCurrency;
  if @B is null then set @B=0
  end if
  ;
  return(@R+@B)
end
//[v 01.01.11]
//[v 01.07.02] - Niki