On May 31, 2011, at 8:01 AM, Owen wrote:
> Has any one in the group tried Ruport?
>
> http://www.rubyreports.org/
>
Not with Hobo, but I used it for an extranet project a while back. Overall, the
formatting options were nice - but the grouping and summary stuff didn't play
well with the way my tables were organized (also not helped by hitting some
ActiveRecord bugs).
Regarding the original question:
> So what I figured out is I need to create named_scope.
> To generate report that shows all calls from users within last quarter I do:
> named_scope :show_calls, :from => "(select *, (select count(*) from calls
> where user_id=users.id and created_at >= '" +
> (Date.commercial(Date.today.year, Date.today.cweek, 1) - 11 *
> 7).strftime("%Y-%m-%d") + "' and result != 'nocall') as total_calls
> from users) users", :conditions => "total_calls > 0"
Couple notes on this:
- doing the date calculation as written above will result in weird behavior in
production; the scope gets defined *once* (at class-load time) and the dates
won't update after that. It's a sneaky bug, totally unobservable in development
mode (where the class gets reloaded every request). You'll want to pass a
lambda instead to get the correct behavior.
- passing a lambda also allows you to build scopes with arguments; check the AR
docs for details.
- you may want to consider flipping the way some of these queries are
structured; for instance, transforming the above into a count query on the Call
model might make more sense:
results_hash = Call.this_quarter.successful.count(:group => :user_id, :having
=> 'count_all > 0')
scopes on Call:
named_scope :this_quarter, lambda { :conditions => ['created_at >= ?',
(Time.now.beginning_of_week - 11.weeks).to_date] }
named_scope :successful, :conditions => ['result != ?', 'nocall']
The result of this will be a hash user_id => count of calls. If you really want
user objects, it's easy enough to do that:
users = User.find(results_hash.keys)
users_hash = users.inject({}) { |h, user| h[user.id] = user; h }
users_results_hash = result_hash.inject({}) { |h, v| h[users_hash[v[0]]] =
v[1]; h }
(that may be missing a to_i, depending on your database adapter - some will
coerce the :user_id groups to integers while others return strings)
Hope this helps!
--Matt Jones
--
You received this message because you are subscribed to the Google Groups "Hobo
Users" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/hobousers?hl=en.
