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

GO

create function DBA.GetDateCoveredByDeposits( in @RegNo integer ) 
returns date
begin
  declare @DateCovered date;
  declare @WhoPays integer;
  declare @Guest integer;
  declare @DepositSum decimal(12,2);
  declare @ArticlePrice decimal(12,2);
  declare @AddedDays integer;
  select dateadd(day,B.PayedLodgings,B.DateRegDt),B.WhoPays,B.Guest into @DateCovered,@WhoPays,@Guest
    from Book as B
    where B.RegNum = @RegNo;
  if(@Guest = @WhoPays) then
    set @DepositSum = CalcDepositSum(@Guest);
    if(@DepositSum > 0) then
      for f as curs scroll cursor for
        select B.RegNum as @RegNum,
          PK.NachinPlat as @NachinPlat,
          B.DateRegDt as @DateReg,
          B.PayedLodgings as @PayedLodgings
          from Book as B,PriceKinds as PK
          where B.Guest = @Guest and B.WhoPays = @Guest and B.NumLodgings > B.PayedLodgings and B.PriceKind = PK.PriceKind
          order by B.DateRegDt asc do
        if(@DepositSum > 0) then
          set @DateCovered = dateadd(day,@PayedLodgings,@DateReg);
          call FillArticleLodgings(@RegNum,0,1,'');
          for f1 as curs1 scroll cursor for
            select RealLodgingsNum as @RealLodgingsNum,Qty as @Qty,ZIndex as @ZIndex,
              Price as @Price,TipDDS as @TipDDS,VatRate as @VatRate
              from #ArticleLodgings
              order by ZIndex asc do
            if @TipDDS = 2 then
              set @ArticlePrice = @Price*(1+@VatRate)
            else
              set @ArticlePrice = @Price
            end if;
            if(@ArticlePrice <> 0) then
              if(@NachinPlat = 0) then // Ako se plashta na den
                set @AddedDays = convert(integer,(@DepositSum/@ArticlePrice)+.03);
                if @AddedDays > @Qty then
                  set @AddedDays = @Qty
                end if;
                set @DepositSum = @DepositSum-@AddedDays*@ArticlePrice;
                if @DepositSum < @ArticlePrice then
                  set @DepositSum = 0
                end if // ako se plashta na mesec
              else set @DepositSum = @DepositSum-@ArticlePrice;
                if(@DepositSum > 0) then
                  set @AddedDays = @RealLodgingsNum
                else
                  set @AddedDays = 0
                end if end if;
              set @DateCovered = dateadd(day,@AddedDays,@DateCovered)
            end if
          end for end if
      end for // @DepositSum > 0
    end if end if;
  // @DepositSum > 0
  return(@DateCovered)
end