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

GO

create function DBA.GetPayTypePayedRentForPeriod(in @RegNo integer,in @StartDate date,in @EndDate date)
returns integer
begin
  declare @DatePaidTo date;
  declare @PartlyPaidToOnDate date;
  declare @PayType integer;
  set @PartlyPaidToOnDate=null;
  set @DatePaidTo=dateadd(day,-1,(select DateRegDt from Book where RegNum = @RegNo));
  for f as curs scroll cursor for
    select A.RealLodgingsNum as @Lodgings,
      Inv.InvoiceDateDt as @InvoiceDateDt,
      Inv.PayHow as @PayHow
      from Invoices as Inv
      ,Articles as A
      where A.InvoiceNo = Inv.InvoiceNo
      and A.RegNo = @RegNo
      and Inv.CancelInvoiceNo = 0 order by
      Inv.InvoiceNo asc do
    set @DatePaidTo=@DatePaidTo+@Lodgings;
    if(@DatePaidTo >= @EndDate) then
      return(@PayHow)
    end if;
    if(@DatePaidTo >= @StartDate) and(@DatePaidTo <= @EndDate) then
      set @PayType=@PayHow
    end if end for;
  return(@PayType)
end