def balance_averages(count=30)
#===== balances
transaction_0 = Transaxtion.find_by_account_id(self.id, :conditions => ["transaxtions.date < ?", count.days.ago.beginning_of_day], :order => "transaxtions.date DESC, transaxtions.id DESC")
transaction_x = Transaxtion.find_by_account_id(self.id, :order => "transaxtions.date DESC, transaxtions.id DESC")
balance_0 = transaction_0 == nil ? nil : transaction_0.balance
balance_x = transaction_x == nil ? nil : transaction_x.balance
balances = Account.find_by_sql "SELECT #{count}-datediff(curdate(),date(transaxtions.date)) as age, avg(balance) as balance FROM transaxtions WHERE account_id = #{self.id} AND transaxtions.date >= '#{count.days.ago.beginning_of_day.to_s(:db)}' GROUP BY date(transaxtions.date)"
#===== empty
if balance_0 == nil && balance_x == nil
balance_array = Array.new(count+2,0.0)
return balance_array
end
#===== part
if balance_0 == nil
balance_0 = balances[0].balance
end
#===== balances
balance_array = Array.new(count)
balances.each {|balance| balance_array[balance.age.to_i] = balance.balance}
#===== fill
fill_balance = balance_0
min_balance = fill_balance
max_balance = fill_balance
0.upto(count) do |day|
fill_balance = balance_array[day] if balance_array[day] != nil
balance_array[day] = fill_balance if balance_array[day] == nil
max_balance = [balance_array[day],max_balance].max
min_balance = [balance_array[day],min_balance].min
end
# puts balance_0
# puts balance_x
# balance_array.each_with_index {|balance,index| puts "#{index} #{balance}"}
#===== min,max,balances
balance_array = [min_balance,max_balance] + balance_array
return balance_array
end
Refactorings
No refactoring yet !
Mark Wilden
December 20, 2007, December 20, 2007 04:24, permalink
No rating.
Login to rate!
Taking a step back, if you create a calendar table containing consecutive dates, you could do a left join from there to "transaxtions" (misspelled, by the way) to do everything in the database query.
///ark
aktxyz.myopenid.com
December 20, 2007, December 20, 2007 13:31, permalink
No rating.
Login to rate!
ok, got it a little better myself, also, I use the left join trick in some other places
def balance_averages(count=30)
#===== balances
transaction_0 = Transaxtion.find_by_account_id(self.id, :conditions => ["transaxtions.date < ?", count.days.ago.beginning_of_day], :order => "transaxtions.date DESC, transaxtions.id DESC")
transaction_x = Transaxtion.find_by_account_id(self.id, :order => "transaxtions.date DESC, transaxtions.id DESC")
balance_0 = transaction_0 ? transaction_0.balance : nil
balance_x = transaction_x ? transaction_x.balance : nil
balances = Account.find_by_sql "SELECT #{count}-datediff(curdate(),date(transaxtions.date)) as age, avg(balance) as balance FROM transaxtions WHERE account_id = #{self.id} AND transaxtions.date >= '#{count.days.ago.beginning_of_day.to_s(:db)}' GROUP BY date(transaxtions.date)"
#===== results
results = [balance_0]
results[0] = balance_0
results[count] = balance_x
balances.each {|balance| results[balance.age.to_i] = balance.balance}
results = results.collect {|result| result || results.last}
results = [results.min,results.max] + results
results
end
Bernardo Rufino
December 27, 2007, December 27, 2007 03:40, permalink
No rating.
Login to rate!
I didn't tried, can have some bugs, but the ideas can be used =D
def balance_averages(count=30, counted=count.days.ago.beginning_of_day)
#===== balances
transaction_x = Transaxtion.find_by_account_id(self.id, :order => "transaxtions.date DESC, transaxtions.id DESC");
transaction_0 = transaction_x.select{|t| t.date < counted;};
balance_x, balance_0 = [transaction_x, transaction_0].map{|t| (t) ? t.balance : nil;};
balances = Account.find_by_sql "SELECT #{count}-datediff(curdate(),date(transaxtions.date)) as age, avg(balance) as balance FROM transaxtions WHERE account_id = #{self.id} AND transaxtions.date >= '#{counted.to_s(:db)}' GROUP BY date(transaxtions.date)";
#===== results
results = [balance_0] + ([nil] * count-1) + [balance_x];
balances.each{|balance| results[balance.age.to_i] = balance.balance;};
results.map!{|result| result || results.last;};
[results.min, results.max] + results
end
I am querying a database for account balances over a the last 30 day period, which I will then show in a pretty chart. I get back a sparse array of values (because some days may not have any transactions to average into a balance). So I need to fill in this sparse array...like this ugly code manages to do in an annoying way