Rails Getting a running balance with ordered list - summation virtual attribute

Question

I'm trying to get a current balance per transaction, a simple summation (Credit's up to record - Debits up to record) on an ordered list.

The challenge is that transactions are often entered out of order, and the transaction logs are sorted by a "Dated" field (showing the date it should have been entered) and by "credits" first (so that it shows payments before bills on the same day).

The association is sorted:

has_many :rent_logs, :order => [:dated, "credit desc"]

Data is presented simular to the following

[ID] Dated  Label   Credit  Debit   Balance [Needs excel summation look]
[20] 1/1/13 payment 600.0          -30 *    [Should be: 600 ]
[1 ] 1/1/13 Rent Due        630.0  -30      [Ok here  : -30 ]
[2 ] 2/1/13 Rent Due        630.0  -660     [Ok here  : -660]
[28] 2/6/13 Late Fee        50.0   -710     [Ok here  : -710]
[7 ] 3/1/13 payment 1200.0         -140*    [Should be: 490 ]
[3 ] 3/1/13 Rent Due        630.0  -140     [Ok here  : -140]

* Indicates massive fail on running balance

I got this by running the method below in the lease model.

def balance_to_date(date)
 ...
  rent_logs.where("dated <= ?", date).sum(:credit) - rent_logs.where("dated <= ?" ,date).sum(:debit)
  #problem with above is that it calculates day by day, rather than record by record.
end

The problem is that I don't want it to get a difference of all the prior through out the date of interest. I want it to get the difference of all the prior through the *current record.

There are no other obvious propertys to do conditions or filters on that I can think of. The best solution that I can think of is an ugly one that probably should get me fired...:

def balance_to_transaction(id)
  balance = rent_logs.where("dated <= ?", date-1.day).sum(:credit) - rent_logs.where("dated <= ?" ,date-1.day).sum(:debit)
  rent_logs.where("dated = ?", date).each do |transaction|
    balance += transaction.credit
    balance -= transaction.debit
    if (id == transaction.id) 
      break
    end
  end
  balance
end

This cannot be the right way to do this?

I'm using Rails 3.2.12, Ruby 1.9.3

Thanks Phil

Answer 1

The problem is that you use the date as condition, which makes no difference between the records in the same day. To solve this use ID to differenciate between the records.

def balance_to_item(date, id)
 ...
  rent_logs.where(["dated < ? or (dated = ? and ID <= ?)", date, date, id]).sum(:credit) - 
  rent_logs.where(["dated < ? or (dated = ? and ID <= ?)", date, date, id]).sum(:debit)
end

You need to specify the transaction item to calculate the balance up to.