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

GO

create function GetRealDatePayedRentForMonth(in @RegNo integer)
returns date
begin
  declare @SpravkaDate date;
  declare @DiffMonths integer;
  declare @BookRegDate date;
  declare @ResultDate date;
  
  select DateRegDt into @BookRegDate from Book where RegNum=@RegNo;
  
  set @SpravkaDate = date('1.'||bvrSpravkaM||'.'||bvrSpravkaY);
  set @DiffMonths = datediff(month,@BookRegDate,@SpravkaDate);
  
  set @ResultDate = null;
  for f as curs scroll cursor for 
    select
	  Inv.InvoiceNo as @InvoiceNo,
	  Inv.InvoiceDateDt as @InvoiceDateDt
	from
	  Invoices Inv, Articles A
	where
	  A.InvoiceNo=Inv.InvoiceNo
	  and A.RegNo=@RegNo
	  and Inv.CancelInvoiceNo=0
	order by Inv.InvoiceNo asc
  do
    if (@DiffMonths=0) then 
	  set @ResultDate = @InvoiceDateDt
	end if;
	set @DiffMonths=@DiffMonths-1;
  end for;
  
  return (@ResultDate);
end

go

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

GO

create function GetRealDatePayedLodgForMonth(in @RegNo integer)
returns date
begin
  declare @SpravkaDate date;
  declare @DiffDays integer;
  declare @BookRegDate date;
  declare @ResultDate date;
  
  select DateRegDt into @BookRegDate from Book where RegNum=@RegNo;
  
  set @SpravkaDate = date('1.'||bvrSpravkaM||'.'||bvrSpravkaY);
  set @DiffDays = datediff(day,@BookRegDate,@SpravkaDate);
  
  set @ResultDate = null;
  for f as curs scroll cursor for 
    select
	  Inv.InvoiceNo as @InvoiceNo,
	  Inv.InvoiceDateDt as @InvoiceDateDt,
	  A.Qty as @Qty
	from
	  Invoices Inv, Articles A
	where
	  A.InvoiceNo=Inv.InvoiceNo
	  and A.RegNo=@RegNo
	  and Inv.CancelInvoiceNo=0
	order by Inv.InvoiceNo asc
  do
    set @DiffDays = @DiffDays - @Qty;
    if (@DiffDays<=0) then 
	  set @ResultDate = @InvoiceDateDt
	end if;
  end for;
  
  return (@ResultDate);
end
go