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

GO

create function DBA.CalcContractServiceSum( in @ContractNo integer ) 
returns decimal(12,4)
begin
  declare @R decimal(12,4);
  declare @B decimal(12,4);
  declare @HumanKind integer;
  declare @DDS decimal(12,4);
  declare @Discount decimal(12,4);
  declare @IsForAlServices integer;
  declare @Sum decimal(12,4);
  set @R = 0;
  set @B = 0;
  set @Sum = 0;
  select IsDiscForAll,Discount into @IsForAlServices,@Discount from Contracts where ContractNo = @ContractNo;
  message 'IsDiscForAll -> '+cast(@IsForAlServices as varchar) type info to console;
  message 'Discount -> '+cast(@Discount as varchar) type info to console;
  if(@IsForAlServices = 0) then set @Discount = 0
  end if;
  for f as Curs scroll cursor for
    select VidSdelki.TipDDS as @tipDDS,VatRates.Rate as @VatRate,Services.ServiceNo as @ServiceNo,ServiceCredits.NumberServices as @NumServices,
      ServiceCredits.PayedServices as @NumPayedServices,ServiceCredits.SumCredit as @SumCredits,Currencies.ExchangeRate as @CurrRate
      from ServiceCredits,Currencies,Services,VidSdelki,VatRates
      where ServiceCredits.ContractPays = @ContractNo
      and ServiceCredits.Service = Services.ServiceNo
      and Services.TipSdelka = VidSdelki.N
	  and Services.VatRateN = VatRates.ID
      and ServiceCredits.ContractPays <> 0
      and ServiceCredits.KodCurrency = Currencies.KodCurrency do
    if(@Discount = 0) then
      message 'Minava prez pyrvata proverka za Discount' type info to console;
      select Discount into @Discount from ServicesForContract where ContractNo = @ContractNo and ServiceNo = @ServiceNo
    end if;
    if @tipDDS <> 2 then
      set @Sum = (@NumServices-@NumPayedServices)*@SumCredits*@CurrRate;
      set @R = @R+CalcPriceWithDiscount(@Discount,@Sum);
      set @Sum = 0
    else
      set @Sum = (1+@VatRate)*(@NumServices-@NumPayedServices)*@SumCredits*@CurrRate;
      set @R = @R+CalcPriceWithDiscount(@Discount,@Sum);
      set @Sum = 0
    end if;
    if @IsForAlServices = 0 then set @Discount = 0
    end if end for;
  if @R is null then set @R = 0
  end if;
  for f1 as Curs1 scroll cursor for
    select Book.NumLodgings as @NumServices,Book.PayedLodgings as @NumPayedServices,Book.Price as @SumCredits,Currencies.ExchangeRate as @CurrRate,Book.Guest as @GuestNo
      from Book,Currencies
      where Book.ContractPays = @ContractNo
      and Book.KodCurrency = Currencies.KodCurrency do
    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+VatRates.Rate) endif)
      into @DDS from Services,VidSdelki,VatRates
      where Services.ServiceNo = @HumanKind and Services.TipSdelka = VidSdelki.N and Services.VatRateN = VatRates.ID;
    if(@Discount = 0) then
      message 'Minava prez pyrvata proverka za Discount' type info to console;
      select Discount into @Discount from ServicesForContract where ContractNo = @ContractNo and ServiceNo = @HumanKind
    end if;
    set @Sum = @DDS*(@NumServices-@NumPayedServices)*@SumCredits*@CurrRate;
    set @B = @B+CalcPriceWithDiscount(@Discount,@Sum);
    set @Sum = 0;
    if @IsForAlServices = 0 then set @Discount = 0
    end if end for;
  if @B is null then set @B = 0
  end if;
  return(@R+@B)
end