Some help iterating dates and finding matching DB records

[slypants]

I'm looking to create a page which displays expected rent payments and either shows rental income paid by each tenant or an amount outstanding.

I believe the pseudo code for this is:

 

Get the rent payments for the tenant_id from the database

For a particular tenant_id {

  Look-up the start date for their tenancy, and the expected end date

  work out the delta between the two dates

 

  For each year {

      For each month {

        print the year and month

        Find all payments which match the iterated month and year, else write "outstanding" and the expected rental amount

        print the status (if payment is found), amount paid, date of payment and payment reference

      {

  }

  Total up the amount of rent paid and amount of rent outstanding for the tenant

}

 

I'm expecting to print a table which looks something like this:

 

Year  Month  Status      Amount  Date          Reference

2007  11        Paid        400        11/1/2007  PropA/11/A.N.Other

2008  12        Paid        400        12/1/2007  PropA/12/A.N.Other

2008  1          Paid        400        1/1/2008    PropA/1/A.N.Other

2008  2          Paid        400        2/1/2008    PropA/2/A.N.Other

2008  3          Overdue  400        3/1/2008    <null>

2008  4          Overdue  400        4/1/2008    <null>

 

 

Questions I have:

 

1. Is this the correct approach? Should I be mani 8)pulating the dates (in_month, in_year, out_month, out_year) or using arrays or working between 2 dates another way to iterate?

 

2. I'm not sure how to manipulate the dates <$in_month = date("m",(strtotime($db_in_date)+60*60*24*($OFFSET))); >

 

3. How do I find the correct database record matching payment_year (int) and payment_month (int) to match the above

 

4. Any tips on how to efficiently do the status calculation (if record found then paid, else overdue)?

 

Thanks in advance,

Slypants