app/models/time_booking_query.rb
class TimeBookingQuery < Query
include TtQueryOperators
include TtQueryConcern
self.queried_class = TimeBooking
@visible_permission = :index_tt_bookings_list
self.available_columns = [
QueryColumn.new(:project, :sortable => "#{Project.table_name}.name", :groupable => "#{Project.table_name}.name"),
QueryColumn.new(:activity, :caption => :field_tt_booking_activity, :sortable => "#{TimeEntryActivity.table_name}.name", :groupable => "#{TimeEntryActivity.table_name}.name"),
QueryColumn.new(:comments, :caption => :field_tt_comments),
QueryColumn.new(:user, :sortable => "#{User.table_name}.login", :caption => :field_tt_user),
QueryColumn.new(:tt_booking_date, :sortable => "#{TimeBooking.table_name}.started_on", :default_order => 'desc', :caption => :field_tt_date, :groupable => "DATE(#{TimeBooking.table_name}.started_on)"),
QueryColumn.new(:get_formatted_start_time, :caption => :field_tt_start),
QueryColumn.new(:get_formatted_stop_time, :caption => :field_tt_stop),
QueryColumn.new(:get_formatted_time, :caption => :field_tt_time),
QueryColumn.new(:issue, :sortable => "#{Issue.table_name}.subject", :caption => :field_tt_booking_issue, :groupable => "#{Issue.table_name}.subject"),
QueryColumn.new(:fixed_version, :sortable => lambda {Version.fields_for_order_statement}, :groupable => "#{Version.table_name}.name"),
]
def auth_values
add_available_filter 'tt_start_date', :type => :date, :order => 2
add_available_filter 'tt_booking_issue', :type => :list, :order => 4, :values => Issue.visible.all.collect { |s| [s.subject, s.id.to_s] }
add_available_filter 'tt_booking_activity', :type => :list, :order => 4, :values => help.get_activities('').map { |s| [s.name, s.name] }
project_values = []
if project.nil?
if User.current.logged? && User.current.memberships.any?
project_values << ["<< #{l(:label_my_projects).downcase} >>", "mine"]
end
project_values += all_projects_values
add_available_filter("tt_booking_project", :type => :list, :values => project_values) unless project_values.empty?
end
versions = []
if project
versions = project.shared_versions.all
else
# TODO: find a way to get shared_versions without killing the db
versions = Project.visible.includes(:versions).all.flat_map { |project| project.shared_versions.all }
versions.uniq!
end
if versions.any?
add_available_filter "tt_booking_fixed_version",
:type => :list_optional,
:values => versions.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] }.sort
end
end
def default_columns_names
[:project, :activity, :tt_booking_date, :get_formatted_start_time, :get_formatted_stop_time, :issue, :comments, :get_formatted_time]
end
# Returns the bookings count
def booking_count
# TODO refactor includes
TimeBooking.visible.
joins(:project, {:time_entry => [{:issue => :fixed_version}, :activity]}, {:time_log => :user}).
where(statement).
count(:id)
rescue ::ActiveRecord::StatementInvalid => e
raise StatementInvalid.new(e.message)
end
# Returns the bookings count by group or nil if query is not grouped
def booking_count_by_group
r = nil
if grouped?
begin
# Rails3 will raise an (unexpected) RecordNotFound if there's only a nil group value
# for some reason including the :project will result in an "ambiguous column" - error if we try to group by
# "project" and additionally filter by issue. so we have to use a small workaround
# todo figure out the 'rails-way' to avoid ambiguous columns
r = TimeBooking.visible.
joins(:project, {:time_entry => [{:issue => :fixed_version}, :activity]}, {:time_log => :user}).
where(statement).
group(group_by_statement).
count(:id)
rescue ActiveRecord::RecordNotFound
r = {nil => booking_count}
end
custom_field_value()
end
r
rescue ::ActiveRecord::StatementInvalid => e
raise Query::StatementInvalid.new(e.message)
end
# Returns the bookings
# Valid options are :order, :offset, :limit, :include, :conditions
def bookings(options={})
order_option = [group_by_sort_order, options[:order]].flatten.reject(&:blank?)
scope = TimeBooking.visible.
includes([:project, {:time_entry => [{:issue => :fixed_version}, :activity]}, {:time_log => :user}]).
where(statement).
where(options[:conditions]).
order(order_option).
limit(options[:limit]).
offset(options[:offset])
scope.all
rescue ::ActiveRecord::StatementInvalid => e
raise Query::StatementInvalid.new(e.message)
end
# sql statements for where clauses have to be in an "sql_for_#{filed-name}_field" method
# so we have to implement some where-clauses for every new filter here
def sql_for_tt_booking_project_field(field, operator, value)
if value.delete('mine')
value += User.current.memberships.map(&:project_id).map(&:to_s)
end
if operator == "="
"( #{TimeBooking.table_name}.project_id IN (" + value.collect { |val| "'#{connection.quote_string(val)}'" }.join(",") + ") )"
else
"( #{TimeBooking.table_name}.project_id NOT IN (" + value.collect { |val| "'#{connection.quote_string(val)}'" }.join(",") + ") OR #{TimeBooking.table_name}.project_id IS NULL )"
end
end
def sql_for_tt_booking_issue_field(field, operator, value)
sql = "( #{Issue.table_name}.id #{operator == "=" ? 'IN' : 'NOT IN'} (" + value.collect { |val| "'#{connection.quote_string(val)}'" }.join(",") + ") "
if operator == "!"
sql << " OR #{Issue.table_name}.id IS NULL)"
else
sql << ")"
end
end
def sql_for_tt_booking_activity_field(field, operator, value)
"( #{TimeEntryActivity.table_name}.name #{operator == "=" ? 'IN' : 'NOT IN'} (" + value.collect { |val| "'#{connection.quote_string(val)}'" }.join(",") + ") )"
end
def sql_for_tt_booking_fixed_version_field(field, operator, value)
if operator == "="
"( #{Issue.table_name}.fixed_version_id IN (" + value.collect { |val| "'#{connection.quote_string(val)}'" }.join(",") + ") )"
else
"( #{Issue.table_name}.fixed_version_id NOT IN (" + value.collect { |val| "'#{connection.quote_string(val)}'" }.join(",") + ") OR #{Issue.table_name}.fixed_version_id IS NULL )"
end
end
end