##// END OF EJS Templates
jro_apps » redmine
RSS
Set @available_filters as an empty hash if nil....
Jean-Philippe Lang -
r15529:c2d97fb4aa11
parent child
Show More
Side by Side Unified
Context file:
r15529:c2d97fb4aa11 Loading diff...:
Show file before | Show file after | Hide comments
@@ -1,1135 +1,1136
1 # Redmine - project management software
1 # Redmine - project management software
2 # Copyright (C) 2006-2016 Jean-Philippe Lang
2 # Copyright (C) 2006-2016 Jean-Philippe Lang
3 #
3 #
4 # This program is free software; you can redistribute it and/or
4 # This program is free software; you can redistribute it and/or
5 # modify it under the terms of the GNU General Public License
5 # modify it under the terms of the GNU General Public License
6 # as published by the Free Software Foundation; either version 2
6 # as published by the Free Software Foundation; either version 2
7 # of the License, or (at your option) any later version.
7 # of the License, or (at your option) any later version.
8 #
8 #
9 # This program is distributed in the hope that it will be useful,
9 # This program is distributed in the hope that it will be useful,
10 # but WITHOUT ANY WARRANTY; without even the implied warranty of
10 # but WITHOUT ANY WARRANTY; without even the implied warranty of
11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 # GNU General Public License for more details.
12 # GNU General Public License for more details.
13 #
13 #
14 # You should have received a copy of the GNU General Public License
14 # You should have received a copy of the GNU General Public License
15 # along with this program; if not, write to the Free Software
15 # along with this program; if not, write to the Free Software
16 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
16 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
17
17
18 class QueryColumn
18 class QueryColumn
19 attr_accessor :name, :sortable, :groupable, :totalable, :default_order
19 attr_accessor :name, :sortable, :groupable, :totalable, :default_order
20 include Redmine::I18n
20 include Redmine::I18n
21
21
22 def initialize(name, options={})
22 def initialize(name, options={})
23 self.name = name
23 self.name = name
24 self.sortable = options[:sortable]
24 self.sortable = options[:sortable]
25 self.groupable = options[:groupable] || false
25 self.groupable = options[:groupable] || false
26 if groupable == true
26 if groupable == true
27 self.groupable = name.to_s
27 self.groupable = name.to_s
28 end
28 end
29 self.totalable = options[:totalable] || false
29 self.totalable = options[:totalable] || false
30 self.default_order = options[:default_order]
30 self.default_order = options[:default_order]
31 @inline = options.key?(:inline) ? options[:inline] : true
31 @inline = options.key?(:inline) ? options[:inline] : true
32 @caption_key = options[:caption] || "field_#{name}".to_sym
32 @caption_key = options[:caption] || "field_#{name}".to_sym
33 @frozen = options[:frozen]
33 @frozen = options[:frozen]
34 end
34 end
35
35
36 def caption
36 def caption
37 case @caption_key
37 case @caption_key
38 when Symbol
38 when Symbol
39 l(@caption_key)
39 l(@caption_key)
40 when Proc
40 when Proc
41 @caption_key.call
41 @caption_key.call
42 else
42 else
43 @caption_key
43 @caption_key
44 end
44 end
45 end
45 end
46
46
47 # Returns true if the column is sortable, otherwise false
47 # Returns true if the column is sortable, otherwise false
48 def sortable?
48 def sortable?
49 !@sortable.nil?
49 !@sortable.nil?
50 end
50 end
51
51
52 def sortable
52 def sortable
53 @sortable.is_a?(Proc) ? @sortable.call : @sortable
53 @sortable.is_a?(Proc) ? @sortable.call : @sortable
54 end
54 end
55
55
56 def inline?
56 def inline?
57 @inline
57 @inline
58 end
58 end
59
59
60 def frozen?
60 def frozen?
61 @frozen
61 @frozen
62 end
62 end
63
63
64 def value(object)
64 def value(object)
65 object.send name
65 object.send name
66 end
66 end
67
67
68 def value_object(object)
68 def value_object(object)
69 object.send name
69 object.send name
70 end
70 end
71
71
72 def css_classes
72 def css_classes
73 name
73 name
74 end
74 end
75 end
75 end
76
76
77 class QueryAssociationColumn < QueryColumn
77 class QueryAssociationColumn < QueryColumn
78
78
79 def initialize(association, attribute, options={})
79 def initialize(association, attribute, options={})
80 @association = association
80 @association = association
81 @attribute = attribute
81 @attribute = attribute
82 name_with_assoc = "#{association}.#{attribute}".to_sym
82 name_with_assoc = "#{association}.#{attribute}".to_sym
83 super(name_with_assoc, options)
83 super(name_with_assoc, options)
84 end
84 end
85
85
86 def value_object(object)
86 def value_object(object)
87 if assoc = object.send(@association)
87 if assoc = object.send(@association)
88 assoc.send @attribute
88 assoc.send @attribute
89 end
89 end
90 end
90 end
91
91
92 def css_classes
92 def css_classes
93 @css_classes ||= "#{@association}-#{@attribute}"
93 @css_classes ||= "#{@association}-#{@attribute}"
94 end
94 end
95 end
95 end
96
96
97 class QueryCustomFieldColumn < QueryColumn
97 class QueryCustomFieldColumn < QueryColumn
98
98
99 def initialize(custom_field, options={})
99 def initialize(custom_field, options={})
100 self.name = "cf_#{custom_field.id}".to_sym
100 self.name = "cf_#{custom_field.id}".to_sym
101 self.sortable = custom_field.order_statement || false
101 self.sortable = custom_field.order_statement || false
102 self.groupable = custom_field.group_statement || false
102 self.groupable = custom_field.group_statement || false
103 self.totalable = options.key?(:totalable) ? !!options[:totalable] : custom_field.totalable?
103 self.totalable = options.key?(:totalable) ? !!options[:totalable] : custom_field.totalable?
104 @inline = true
104 @inline = true
105 @cf = custom_field
105 @cf = custom_field
106 end
106 end
107
107
108 def caption
108 def caption
109 @cf.name
109 @cf.name
110 end
110 end
111
111
112 def custom_field
112 def custom_field
113 @cf
113 @cf
114 end
114 end
115
115
116 def value_object(object)
116 def value_object(object)
117 if custom_field.visible_by?(object.project, User.current)
117 if custom_field.visible_by?(object.project, User.current)
118 cv = object.custom_values.select {|v| v.custom_field_id == @cf.id}
118 cv = object.custom_values.select {|v| v.custom_field_id == @cf.id}
119 cv.size > 1 ? cv.sort {|a,b| a.value.to_s <=> b.value.to_s} : cv.first
119 cv.size > 1 ? cv.sort {|a,b| a.value.to_s <=> b.value.to_s} : cv.first
120 else
120 else
121 nil
121 nil
122 end
122 end
123 end
123 end
124
124
125 def value(object)
125 def value(object)
126 raw = value_object(object)
126 raw = value_object(object)
127 if raw.is_a?(Array)
127 if raw.is_a?(Array)
128 raw.map {|r| @cf.cast_value(r.value)}
128 raw.map {|r| @cf.cast_value(r.value)}
129 elsif raw
129 elsif raw
130 @cf.cast_value(raw.value)
130 @cf.cast_value(raw.value)
131 else
131 else
132 nil
132 nil
133 end
133 end
134 end
134 end
135
135
136 def css_classes
136 def css_classes
137 @css_classes ||= "#{name} #{@cf.field_format}"
137 @css_classes ||= "#{name} #{@cf.field_format}"
138 end
138 end
139 end
139 end
140
140
141 class QueryAssociationCustomFieldColumn < QueryCustomFieldColumn
141 class QueryAssociationCustomFieldColumn < QueryCustomFieldColumn
142
142
143 def initialize(association, custom_field, options={})
143 def initialize(association, custom_field, options={})
144 super(custom_field, options)
144 super(custom_field, options)
145 self.name = "#{association}.cf_#{custom_field.id}".to_sym
145 self.name = "#{association}.cf_#{custom_field.id}".to_sym
146 # TODO: support sorting/grouping by association custom field
146 # TODO: support sorting/grouping by association custom field
147 self.sortable = false
147 self.sortable = false
148 self.groupable = false
148 self.groupable = false
149 @association = association
149 @association = association
150 end
150 end
151
151
152 def value_object(object)
152 def value_object(object)
153 if assoc = object.send(@association)
153 if assoc = object.send(@association)
154 super(assoc)
154 super(assoc)
155 end
155 end
156 end
156 end
157
157
158 def css_classes
158 def css_classes
159 @css_classes ||= "#{@association}_cf_#{@cf.id} #{@cf.field_format}"
159 @css_classes ||= "#{@association}_cf_#{@cf.id} #{@cf.field_format}"
160 end
160 end
161 end
161 end
162
162
163 class Query < ActiveRecord::Base
163 class Query < ActiveRecord::Base
164 class StatementInvalid < ::ActiveRecord::StatementInvalid
164 class StatementInvalid < ::ActiveRecord::StatementInvalid
165 end
165 end
166
166
167 include Redmine::SubclassFactory
167 include Redmine::SubclassFactory
168
168
169 VISIBILITY_PRIVATE = 0
169 VISIBILITY_PRIVATE = 0
170 VISIBILITY_ROLES = 1
170 VISIBILITY_ROLES = 1
171 VISIBILITY_PUBLIC = 2
171 VISIBILITY_PUBLIC = 2
172
172
173 belongs_to :project
173 belongs_to :project
174 belongs_to :user
174 belongs_to :user
175 has_and_belongs_to_many :roles, :join_table => "#{table_name_prefix}queries_roles#{table_name_suffix}", :foreign_key => "query_id"
175 has_and_belongs_to_many :roles, :join_table => "#{table_name_prefix}queries_roles#{table_name_suffix}", :foreign_key => "query_id"
176 serialize :filters
176 serialize :filters
177 serialize :column_names
177 serialize :column_names
178 serialize :sort_criteria, Array
178 serialize :sort_criteria, Array
179 serialize :options, Hash
179 serialize :options, Hash
180
180
181 attr_protected :project_id, :user_id
181 attr_protected :project_id, :user_id
182
182
183 validates_presence_of :name
183 validates_presence_of :name
184 validates_length_of :name, :maximum => 255
184 validates_length_of :name, :maximum => 255
185 validates :visibility, :inclusion => { :in => [VISIBILITY_PUBLIC, VISIBILITY_ROLES, VISIBILITY_PRIVATE] }
185 validates :visibility, :inclusion => { :in => [VISIBILITY_PUBLIC, VISIBILITY_ROLES, VISIBILITY_PRIVATE] }
186 validate :validate_query_filters
186 validate :validate_query_filters
187 validate do |query|
187 validate do |query|
188 errors.add(:base, l(:label_role_plural) + ' ' + l('activerecord.errors.messages.blank')) if query.visibility == VISIBILITY_ROLES && roles.blank?
188 errors.add(:base, l(:label_role_plural) + ' ' + l('activerecord.errors.messages.blank')) if query.visibility == VISIBILITY_ROLES && roles.blank?
189 end
189 end
190
190
191 after_save do |query|
191 after_save do |query|
192 if query.visibility_changed? && query.visibility != VISIBILITY_ROLES
192 if query.visibility_changed? && query.visibility != VISIBILITY_ROLES
193 query.roles.clear
193 query.roles.clear
194 end
194 end
195 end
195 end
196
196
197 class_attribute :operators
197 class_attribute :operators
198 self.operators = {
198 self.operators = {
199 "=" => :label_equals,
199 "=" => :label_equals,
200 "!" => :label_not_equals,
200 "!" => :label_not_equals,
201 "o" => :label_open_issues,
201 "o" => :label_open_issues,
202 "c" => :label_closed_issues,
202 "c" => :label_closed_issues,
203 "!*" => :label_none,
203 "!*" => :label_none,
204 "*" => :label_any,
204 "*" => :label_any,
205 ">=" => :label_greater_or_equal,
205 ">=" => :label_greater_or_equal,
206 "<=" => :label_less_or_equal,
206 "<=" => :label_less_or_equal,
207 "><" => :label_between,
207 "><" => :label_between,
208 "<t+" => :label_in_less_than,
208 "<t+" => :label_in_less_than,
209 ">t+" => :label_in_more_than,
209 ">t+" => :label_in_more_than,
210 "><t+"=> :label_in_the_next_days,
210 "><t+"=> :label_in_the_next_days,
211 "t+" => :label_in,
211 "t+" => :label_in,
212 "t" => :label_today,
212 "t" => :label_today,
213 "ld" => :label_yesterday,
213 "ld" => :label_yesterday,
214 "w" => :label_this_week,
214 "w" => :label_this_week,
215 "lw" => :label_last_week,
215 "lw" => :label_last_week,
216 "l2w" => [:label_last_n_weeks, {:count => 2}],
216 "l2w" => [:label_last_n_weeks, {:count => 2}],
217 "m" => :label_this_month,
217 "m" => :label_this_month,
218 "lm" => :label_last_month,
218 "lm" => :label_last_month,
219 "y" => :label_this_year,
219 "y" => :label_this_year,
220 ">t-" => :label_less_than_ago,
220 ">t-" => :label_less_than_ago,
221 "<t-" => :label_more_than_ago,
221 "<t-" => :label_more_than_ago,
222 "><t-"=> :label_in_the_past_days,
222 "><t-"=> :label_in_the_past_days,
223 "t-" => :label_ago,
223 "t-" => :label_ago,
224 "~" => :label_contains,
224 "~" => :label_contains,
225 "!~" => :label_not_contains,
225 "!~" => :label_not_contains,
226 "=p" => :label_any_issues_in_project,
226 "=p" => :label_any_issues_in_project,
227 "=!p" => :label_any_issues_not_in_project,
227 "=!p" => :label_any_issues_not_in_project,
228 "!p" => :label_no_issues_in_project,
228 "!p" => :label_no_issues_in_project,
229 "*o" => :label_any_open_issues,
229 "*o" => :label_any_open_issues,
230 "!o" => :label_no_open_issues
230 "!o" => :label_no_open_issues
231 }
231 }
232
232
233 class_attribute :operators_by_filter_type
233 class_attribute :operators_by_filter_type
234 self.operators_by_filter_type = {
234 self.operators_by_filter_type = {
235 :list => [ "=", "!" ],
235 :list => [ "=", "!" ],
236 :list_status => [ "o", "=", "!", "c", "*" ],
236 :list_status => [ "o", "=", "!", "c", "*" ],
237 :list_optional => [ "=", "!", "!*", "*" ],
237 :list_optional => [ "=", "!", "!*", "*" ],
238 :list_subprojects => [ "*", "!*", "=" ],
238 :list_subprojects => [ "*", "!*", "=" ],
239 :date => [ "=", ">=", "<=", "><", "<t+", ">t+", "><t+", "t+", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", ">t-", "<t-", "><t-", "t-", "!*", "*" ],
239 :date => [ "=", ">=", "<=", "><", "<t+", ">t+", "><t+", "t+", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", ">t-", "<t-", "><t-", "t-", "!*", "*" ],
240 :date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "><t-", "t-", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", "!*", "*" ],
240 :date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "><t-", "t-", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", "!*", "*" ],
241 :string => [ "=", "~", "!", "!~", "!*", "*" ],
241 :string => [ "=", "~", "!", "!~", "!*", "*" ],
242 :text => [ "~", "!~", "!*", "*" ],
242 :text => [ "~", "!~", "!*", "*" ],
243 :integer => [ "=", ">=", "<=", "><", "!*", "*" ],
243 :integer => [ "=", ">=", "<=", "><", "!*", "*" ],
244 :float => [ "=", ">=", "<=", "><", "!*", "*" ],
244 :float => [ "=", ">=", "<=", "><", "!*", "*" ],
245 :relation => ["=", "=p", "=!p", "!p", "*o", "!o", "!*", "*"],
245 :relation => ["=", "=p", "=!p", "!p", "*o", "!o", "!*", "*"],
246 :tree => ["=", "~", "!*", "*"]
246 :tree => ["=", "~", "!*", "*"]
247 }
247 }
248
248
249 class_attribute :available_columns
249 class_attribute :available_columns
250 self.available_columns = []
250 self.available_columns = []
251
251
252 class_attribute :queried_class
252 class_attribute :queried_class
253
253
254 # Permission required to view the queries, set on subclasses.
254 # Permission required to view the queries, set on subclasses.
255 class_attribute :view_permission
255 class_attribute :view_permission
256
256
257 # Scope of queries that are global or on the given project
257 # Scope of queries that are global or on the given project
258 scope :global_or_on_project, lambda {|project|
258 scope :global_or_on_project, lambda {|project|
259 where(:project_id => (project.nil? ? nil : [nil, project.id]))
259 where(:project_id => (project.nil? ? nil : [nil, project.id]))
260 }
260 }
261
261
262 scope :sorted, lambda {order(:name, :id)}
262 scope :sorted, lambda {order(:name, :id)}
263
263
264 # Scope of visible queries, can be used from subclasses only.
264 # Scope of visible queries, can be used from subclasses only.
265 # Unlike other visible scopes, a class methods is used as it
265 # Unlike other visible scopes, a class methods is used as it
266 # let handle inheritance more nicely than scope DSL.
266 # let handle inheritance more nicely than scope DSL.
267 def self.visible(*args)
267 def self.visible(*args)
268 if self == ::Query
268 if self == ::Query
269 # Visibility depends on permissions for each subclass,
269 # Visibility depends on permissions for each subclass,
270 # raise an error if the scope is called from Query (eg. Query.visible)
270 # raise an error if the scope is called from Query (eg. Query.visible)
271 raise Exception.new("Cannot call .visible scope from the base Query class, but from subclasses only.")
271 raise Exception.new("Cannot call .visible scope from the base Query class, but from subclasses only.")
272 end
272 end
273
273
274 user = args.shift || User.current
274 user = args.shift || User.current
275 base = Project.allowed_to_condition(user, view_permission, *args)
275 base = Project.allowed_to_condition(user, view_permission, *args)
276 scope = joins("LEFT OUTER JOIN #{Project.table_name} ON #{table_name}.project_id = #{Project.table_name}.id").
276 scope = joins("LEFT OUTER JOIN #{Project.table_name} ON #{table_name}.project_id = #{Project.table_name}.id").
277 where("#{table_name}.project_id IS NULL OR (#{base})")
277 where("#{table_name}.project_id IS NULL OR (#{base})")
278
278
279 if user.admin?
279 if user.admin?
280 scope.where("#{table_name}.visibility <> ? OR #{table_name}.user_id = ?", VISIBILITY_PRIVATE, user.id)
280 scope.where("#{table_name}.visibility <> ? OR #{table_name}.user_id = ?", VISIBILITY_PRIVATE, user.id)
281 elsif user.memberships.any?
281 elsif user.memberships.any?
282 scope.where("#{table_name}.visibility = ?" +
282 scope.where("#{table_name}.visibility = ?" +
283 " OR (#{table_name}.visibility = ? AND #{table_name}.id IN (" +
283 " OR (#{table_name}.visibility = ? AND #{table_name}.id IN (" +
284 "SELECT DISTINCT q.id FROM #{table_name} q" +
284 "SELECT DISTINCT q.id FROM #{table_name} q" +
285 " INNER JOIN #{table_name_prefix}queries_roles#{table_name_suffix} qr on qr.query_id = q.id" +
285 " INNER JOIN #{table_name_prefix}queries_roles#{table_name_suffix} qr on qr.query_id = q.id" +
286 " INNER JOIN #{MemberRole.table_name} mr ON mr.role_id = qr.role_id" +
286 " INNER JOIN #{MemberRole.table_name} mr ON mr.role_id = qr.role_id" +
287 " INNER JOIN #{Member.table_name} m ON m.id = mr.member_id AND m.user_id = ?" +
287 " INNER JOIN #{Member.table_name} m ON m.id = mr.member_id AND m.user_id = ?" +
288 " WHERE q.project_id IS NULL OR q.project_id = m.project_id))" +
288 " WHERE q.project_id IS NULL OR q.project_id = m.project_id))" +
289 " OR #{table_name}.user_id = ?",
289 " OR #{table_name}.user_id = ?",
290 VISIBILITY_PUBLIC, VISIBILITY_ROLES, user.id, user.id)
290 VISIBILITY_PUBLIC, VISIBILITY_ROLES, user.id, user.id)
291 elsif user.logged?
291 elsif user.logged?
292 scope.where("#{table_name}.visibility = ? OR #{table_name}.user_id = ?", VISIBILITY_PUBLIC, user.id)
292 scope.where("#{table_name}.visibility = ? OR #{table_name}.user_id = ?", VISIBILITY_PUBLIC, user.id)
293 else
293 else
294 scope.where("#{table_name}.visibility = ?", VISIBILITY_PUBLIC)
294 scope.where("#{table_name}.visibility = ?", VISIBILITY_PUBLIC)
295 end
295 end
296 end
296 end
297
297
298 # Returns true if the query is visible to +user+ or the current user.
298 # Returns true if the query is visible to +user+ or the current user.
299 def visible?(user=User.current)
299 def visible?(user=User.current)
300 return true if user.admin?
300 return true if user.admin?
301 return false unless project.nil? || user.allowed_to?(self.class.view_permission, project)
301 return false unless project.nil? || user.allowed_to?(self.class.view_permission, project)
302 case visibility
302 case visibility
303 when VISIBILITY_PUBLIC
303 when VISIBILITY_PUBLIC
304 true
304 true
305 when VISIBILITY_ROLES
305 when VISIBILITY_ROLES
306 if project
306 if project
307 (user.roles_for_project(project) & roles).any?
307 (user.roles_for_project(project) & roles).any?
308 else
308 else
309 Member.where(:user_id => user.id).joins(:roles).where(:member_roles => {:role_id => roles.map(&:id)}).any?
309 Member.where(:user_id => user.id).joins(:roles).where(:member_roles => {:role_id => roles.map(&:id)}).any?
310 end
310 end
311 else
311 else
312 user == self.user
312 user == self.user
313 end
313 end
314 end
314 end
315
315
316 def is_private?
316 def is_private?
317 visibility == VISIBILITY_PRIVATE
317 visibility == VISIBILITY_PRIVATE
318 end
318 end
319
319
320 def is_public?
320 def is_public?
321 !is_private?
321 !is_private?
322 end
322 end
323
323
324 def queried_table_name
324 def queried_table_name
325 @queried_table_name ||= self.class.queried_class.table_name
325 @queried_table_name ||= self.class.queried_class.table_name
326 end
326 end
327
327
328 def initialize(attributes=nil, *args)
328 def initialize(attributes=nil, *args)
329 super attributes
329 super attributes
330 @is_for_all = project.nil?
330 @is_for_all = project.nil?
331 end
331 end
332
332
333 # Builds the query from the given params
333 # Builds the query from the given params
334 def build_from_params(params)
334 def build_from_params(params)
335 if params[:fields] || params[:f]
335 if params[:fields] || params[:f]
336 self.filters = {}
336 self.filters = {}
337 add_filters(params[:fields] || params[:f], params[:operators] || params[:op], params[:values] || params[:v])
337 add_filters(params[:fields] || params[:f], params[:operators] || params[:op], params[:values] || params[:v])
338 else
338 else
339 available_filters.keys.each do |field|
339 available_filters.keys.each do |field|
340 add_short_filter(field, params[field]) if params[field]
340 add_short_filter(field, params[field]) if params[field]
341 end
341 end
342 end
342 end
343 self.group_by = params[:group_by] || (params[:query] && params[:query][:group_by])
343 self.group_by = params[:group_by] || (params[:query] && params[:query][:group_by])
344 self.column_names = params[:c] || (params[:query] && params[:query][:column_names])
344 self.column_names = params[:c] || (params[:query] && params[:query][:column_names])
345 self.totalable_names = params[:t] || (params[:query] && params[:query][:totalable_names])
345 self.totalable_names = params[:t] || (params[:query] && params[:query][:totalable_names])
346 self
346 self
347 end
347 end
348
348
349 # Builds a new query from the given params and attributes
349 # Builds a new query from the given params and attributes
350 def self.build_from_params(params, attributes={})
350 def self.build_from_params(params, attributes={})
351 new(attributes).build_from_params(params)
351 new(attributes).build_from_params(params)
352 end
352 end
353
353
354 def validate_query_filters
354 def validate_query_filters
355 filters.each_key do |field|
355 filters.each_key do |field|
356 if values_for(field)
356 if values_for(field)
357 case type_for(field)
357 case type_for(field)
358 when :integer
358 when :integer
359 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/\A[+-]?\d+(,[+-]?\d+)*\z/) }
359 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/\A[+-]?\d+(,[+-]?\d+)*\z/) }
360 when :float
360 when :float
361 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/\A[+-]?\d+(\.\d*)?\z/) }
361 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/\A[+-]?\d+(\.\d*)?\z/) }
362 when :date, :date_past
362 when :date, :date_past
363 case operator_for(field)
363 case operator_for(field)
364 when "=", ">=", "<=", "><"
364 when "=", ">=", "<=", "><"
365 add_filter_error(field, :invalid) if values_for(field).detect {|v|
365 add_filter_error(field, :invalid) if values_for(field).detect {|v|
366 v.present? && (!v.match(/\A\d{4}-\d{2}-\d{2}(T\d{2}((:)?\d{2}){0,2}(Z|\d{2}:?\d{2})?)?\z/) || parse_date(v).nil?)
366 v.present? && (!v.match(/\A\d{4}-\d{2}-\d{2}(T\d{2}((:)?\d{2}){0,2}(Z|\d{2}:?\d{2})?)?\z/) || parse_date(v).nil?)
367 }
367 }
368 when ">t-", "<t-", "t-", ">t+", "<t+", "t+", "><t+", "><t-"
368 when ">t-", "<t-", "t-", ">t+", "<t+", "t+", "><t+", "><t-"
369 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^\d+$/) }
369 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^\d+$/) }
370 end
370 end
371 end
371 end
372 end
372 end
373
373
374 add_filter_error(field, :blank) unless
374 add_filter_error(field, :blank) unless
375 # filter requires one or more values
375 # filter requires one or more values
376 (values_for(field) and !values_for(field).first.blank?) or
376 (values_for(field) and !values_for(field).first.blank?) or
377 # filter doesn't require any value
377 # filter doesn't require any value
378 ["o", "c", "!*", "*", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", "*o", "!o"].include? operator_for(field)
378 ["o", "c", "!*", "*", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", "*o", "!o"].include? operator_for(field)
379 end if filters
379 end if filters
380 end
380 end
381
381
382 def add_filter_error(field, message)
382 def add_filter_error(field, message)
383 m = label_for(field) + " " + l(message, :scope => 'activerecord.errors.messages')
383 m = label_for(field) + " " + l(message, :scope => 'activerecord.errors.messages')
384 errors.add(:base, m)
384 errors.add(:base, m)
385 end
385 end
386
386
387 def editable_by?(user)
387 def editable_by?(user)
388 return false unless user
388 return false unless user
389 # Admin can edit them all and regular users can edit their private queries
389 # Admin can edit them all and regular users can edit their private queries
390 return true if user.admin? || (is_private? && self.user_id == user.id)
390 return true if user.admin? || (is_private? && self.user_id == user.id)
391 # Members can not edit public queries that are for all project (only admin is allowed to)
391 # Members can not edit public queries that are for all project (only admin is allowed to)
392 is_public? && !@is_for_all && user.allowed_to?(:manage_public_queries, project)
392 is_public? && !@is_for_all && user.allowed_to?(:manage_public_queries, project)
393 end
393 end
394
394
395 def trackers
395 def trackers
396 @trackers ||= (project.nil? ? Tracker.all : project.rolled_up_trackers).visible.sorted
396 @trackers ||= (project.nil? ? Tracker.all : project.rolled_up_trackers).visible.sorted
397 end
397 end
398
398
399 # Returns a hash of localized labels for all filter operators
399 # Returns a hash of localized labels for all filter operators
400 def self.operators_labels
400 def self.operators_labels
401 operators.inject({}) {|h, operator| h[operator.first] = l(*operator.last); h}
401 operators.inject({}) {|h, operator| h[operator.first] = l(*operator.last); h}
402 end
402 end
403
403
404 # Returns a representation of the available filters for JSON serialization
404 # Returns a representation of the available filters for JSON serialization
405 def available_filters_as_json
405 def available_filters_as_json
406 json = {}
406 json = {}
407 available_filters.each do |field, options|
407 available_filters.each do |field, options|
408 options = options.slice(:type, :name, :values)
408 options = options.slice(:type, :name, :values)
409 if options[:values] && values_for(field)
409 if options[:values] && values_for(field)
410 missing = Array(values_for(field)).select(&:present?) - options[:values].map(&:last)
410 missing = Array(values_for(field)).select(&:present?) - options[:values].map(&:last)
411 if missing.any? && respond_to?(method = "find_#{field}_filter_values")
411 if missing.any? && respond_to?(method = "find_#{field}_filter_values")
412 options[:values] += send(method, missing)
412 options[:values] += send(method, missing)
413 end
413 end
414 end
414 end
415 json[field] = options.stringify_keys
415 json[field] = options.stringify_keys
416 end
416 end
417 json
417 json
418 end
418 end
419
419
420 def all_projects
420 def all_projects
421 @all_projects ||= Project.visible.to_a
421 @all_projects ||= Project.visible.to_a
422 end
422 end
423
423
424 def all_projects_values
424 def all_projects_values
425 return @all_projects_values if @all_projects_values
425 return @all_projects_values if @all_projects_values
426
426
427 values = []
427 values = []
428 Project.project_tree(all_projects) do |p, level|
428 Project.project_tree(all_projects) do |p, level|
429 prefix = (level > 0 ? ('--' * level + ' ') : '')
429 prefix = (level > 0 ? ('--' * level + ' ') : '')
430 values << ["#{prefix}#{p.name}", p.id.to_s]
430 values << ["#{prefix}#{p.name}", p.id.to_s]
431 end
431 end
432 @all_projects_values = values
432 @all_projects_values = values
433 end
433 end
434
434
435 # Adds available filters
435 # Adds available filters
436 def initialize_available_filters
436 def initialize_available_filters
437 # implemented by sub-classes
437 # implemented by sub-classes
438 end
438 end
439 protected :initialize_available_filters
439 protected :initialize_available_filters
440
440
441 # Adds an available filter
441 # Adds an available filter
442 def add_available_filter(field, options)
442 def add_available_filter(field, options)
443 @available_filters ||= ActiveSupport::OrderedHash.new
443 @available_filters ||= ActiveSupport::OrderedHash.new
444 @available_filters[field] = options
444 @available_filters[field] = options
445 @available_filters
445 @available_filters
446 end
446 end
447
447
448 # Removes an available filter
448 # Removes an available filter
449 def delete_available_filter(field)
449 def delete_available_filter(field)
450 if @available_filters
450 if @available_filters
451 @available_filters.delete(field)
451 @available_filters.delete(field)
452 end
452 end
453 end
453 end
454
454
455 # Return a hash of available filters
455 # Return a hash of available filters
456 def available_filters
456 def available_filters
457 unless @available_filters
457 unless @available_filters
458 initialize_available_filters
458 initialize_available_filters
459 @available_filters ||= {}
459 @available_filters.each do |field, options|
460 @available_filters.each do |field, options|
460 options[:name] ||= l(options[:label] || "field_#{field}".gsub(/_id$/, ''))
461 options[:name] ||= l(options[:label] || "field_#{field}".gsub(/_id$/, ''))
461 end
462 end
462 end
463 end
463 @available_filters
464 @available_filters
464 end
465 end
465
466
466 def add_filter(field, operator, values=nil)
467 def add_filter(field, operator, values=nil)
467 # values must be an array
468 # values must be an array
468 return unless values.nil? || values.is_a?(Array)
469 return unless values.nil? || values.is_a?(Array)
469 # check if field is defined as an available filter
470 # check if field is defined as an available filter
470 if available_filters.has_key? field
471 if available_filters.has_key? field
471 filter_options = available_filters[field]
472 filter_options = available_filters[field]
472 filters[field] = {:operator => operator, :values => (values || [''])}
473 filters[field] = {:operator => operator, :values => (values || [''])}
473 end
474 end
474 end
475 end
475
476
476 def add_short_filter(field, expression)
477 def add_short_filter(field, expression)
477 return unless expression && available_filters.has_key?(field)
478 return unless expression && available_filters.has_key?(field)
478 field_type = available_filters[field][:type]
479 field_type = available_filters[field][:type]
479 operators_by_filter_type[field_type].sort.reverse.detect do |operator|
480 operators_by_filter_type[field_type].sort.reverse.detect do |operator|
480 next unless expression =~ /^#{Regexp.escape(operator)}(.*)$/
481 next unless expression =~ /^#{Regexp.escape(operator)}(.*)$/
481 values = $1
482 values = $1
482 add_filter field, operator, values.present? ? values.split('|') : ['']
483 add_filter field, operator, values.present? ? values.split('|') : ['']
483 end || add_filter(field, '=', expression.to_s.split('|'))
484 end || add_filter(field, '=', expression.to_s.split('|'))
484 end
485 end
485
486
486 # Add multiple filters using +add_filter+
487 # Add multiple filters using +add_filter+
487 def add_filters(fields, operators, values)
488 def add_filters(fields, operators, values)
488 if fields.is_a?(Array) && operators.is_a?(Hash) && (values.nil? || values.is_a?(Hash))
489 if fields.is_a?(Array) && operators.is_a?(Hash) && (values.nil? || values.is_a?(Hash))
489 fields.each do |field|
490 fields.each do |field|
490 add_filter(field, operators[field], values && values[field])
491 add_filter(field, operators[field], values && values[field])
491 end
492 end
492 end
493 end
493 end
494 end
494
495
495 def has_filter?(field)
496 def has_filter?(field)
496 filters and filters[field]
497 filters and filters[field]
497 end
498 end
498
499
499 def type_for(field)
500 def type_for(field)
500 available_filters[field][:type] if available_filters.has_key?(field)
501 available_filters[field][:type] if available_filters.has_key?(field)
501 end
502 end
502
503
503 def operator_for(field)
504 def operator_for(field)
504 has_filter?(field) ? filters[field][:operator] : nil
505 has_filter?(field) ? filters[field][:operator] : nil
505 end
506 end
506
507
507 def values_for(field)
508 def values_for(field)
508 has_filter?(field) ? filters[field][:values] : nil
509 has_filter?(field) ? filters[field][:values] : nil
509 end
510 end
510
511
511 def value_for(field, index=0)
512 def value_for(field, index=0)
512 (values_for(field) || [])[index]
513 (values_for(field) || [])[index]
513 end
514 end
514
515
515 def label_for(field)
516 def label_for(field)
516 label = available_filters[field][:name] if available_filters.has_key?(field)
517 label = available_filters[field][:name] if available_filters.has_key?(field)
517 label ||= queried_class.human_attribute_name(field, :default => field)
518 label ||= queried_class.human_attribute_name(field, :default => field)
518 end
519 end
519
520
520 def self.add_available_column(column)
521 def self.add_available_column(column)
521 self.available_columns << (column) if column.is_a?(QueryColumn)
522 self.available_columns << (column) if column.is_a?(QueryColumn)
522 end
523 end
523
524
524 # Returns an array of columns that can be used to group the results
525 # Returns an array of columns that can be used to group the results
525 def groupable_columns
526 def groupable_columns
526 available_columns.select {|c| c.groupable}
527 available_columns.select {|c| c.groupable}
527 end
528 end
528
529
529 # Returns a Hash of columns and the key for sorting
530 # Returns a Hash of columns and the key for sorting
530 def sortable_columns
531 def sortable_columns
531 available_columns.inject({}) {|h, column|
532 available_columns.inject({}) {|h, column|
532 h[column.name.to_s] = column.sortable
533 h[column.name.to_s] = column.sortable
533 h
534 h
534 }
535 }
535 end
536 end
536
537
537 def columns
538 def columns
538 # preserve the column_names order
539 # preserve the column_names order
539 cols = (has_default_columns? ? default_columns_names : column_names).collect do |name|
540 cols = (has_default_columns? ? default_columns_names : column_names).collect do |name|
540 available_columns.find { |col| col.name == name }
541 available_columns.find { |col| col.name == name }
541 end.compact
542 end.compact
542 available_columns.select(&:frozen?) | cols
543 available_columns.select(&:frozen?) | cols
543 end
544 end
544
545
545 def inline_columns
546 def inline_columns
546 columns.select(&:inline?)
547 columns.select(&:inline?)
547 end
548 end
548
549
549 def block_columns
550 def block_columns
550 columns.reject(&:inline?)
551 columns.reject(&:inline?)
551 end
552 end
552
553
553 def available_inline_columns
554 def available_inline_columns
554 available_columns.select(&:inline?)
555 available_columns.select(&:inline?)
555 end
556 end
556
557
557 def available_block_columns
558 def available_block_columns
558 available_columns.reject(&:inline?)
559 available_columns.reject(&:inline?)
559 end
560 end
560
561
561 def available_totalable_columns
562 def available_totalable_columns
562 available_columns.select(&:totalable)
563 available_columns.select(&:totalable)
563 end
564 end
564
565
565 def default_columns_names
566 def default_columns_names
566 []
567 []
567 end
568 end
568
569
569 def default_totalable_names
570 def default_totalable_names
570 []
571 []
571 end
572 end
572
573
573 def column_names=(names)
574 def column_names=(names)
574 if names
575 if names
575 names = names.select {|n| n.is_a?(Symbol) || !n.blank? }
576 names = names.select {|n| n.is_a?(Symbol) || !n.blank? }
576 names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }
577 names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }
577 # Set column_names to nil if default columns
578 # Set column_names to nil if default columns
578 if names == default_columns_names
579 if names == default_columns_names
579 names = nil
580 names = nil
580 end
581 end
581 end
582 end
582 write_attribute(:column_names, names)
583 write_attribute(:column_names, names)
583 end
584 end
584
585
585 def has_column?(column)
586 def has_column?(column)
586 column_names && column_names.include?(column.is_a?(QueryColumn) ? column.name : column)
587 column_names && column_names.include?(column.is_a?(QueryColumn) ? column.name : column)
587 end
588 end
588
589
589 def has_custom_field_column?
590 def has_custom_field_column?
590 columns.any? {|column| column.is_a? QueryCustomFieldColumn}
591 columns.any? {|column| column.is_a? QueryCustomFieldColumn}
591 end
592 end
592
593
593 def has_default_columns?
594 def has_default_columns?
594 column_names.nil? || column_names.empty?
595 column_names.nil? || column_names.empty?
595 end
596 end
596
597
597 def totalable_columns
598 def totalable_columns
598 names = totalable_names
599 names = totalable_names
599 available_totalable_columns.select {|column| names.include?(column.name)}
600 available_totalable_columns.select {|column| names.include?(column.name)}
600 end
601 end
601
602
602 def totalable_names=(names)
603 def totalable_names=(names)
603 if names
604 if names
604 names = names.select(&:present?).map {|n| n.is_a?(Symbol) ? n : n.to_sym}
605 names = names.select(&:present?).map {|n| n.is_a?(Symbol) ? n : n.to_sym}
605 end
606 end
606 options[:totalable_names] = names
607 options[:totalable_names] = names
607 end
608 end
608
609
609 def totalable_names
610 def totalable_names
610 options[:totalable_names] || default_totalable_names || []
611 options[:totalable_names] || default_totalable_names || []
611 end
612 end
612
613
613 def sort_criteria=(arg)
614 def sort_criteria=(arg)
614 c = []
615 c = []
615 if arg.is_a?(Hash)
616 if arg.is_a?(Hash)
616 arg = arg.keys.sort.collect {|k| arg[k]}
617 arg = arg.keys.sort.collect {|k| arg[k]}
617 end
618 end
618 if arg
619 if arg
619 c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, (o == 'desc' || o == false) ? 'desc' : 'asc']}
620 c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, (o == 'desc' || o == false) ? 'desc' : 'asc']}
620 end
621 end
621 write_attribute(:sort_criteria, c)
622 write_attribute(:sort_criteria, c)
622 end
623 end
623
624
624 def sort_criteria
625 def sort_criteria
625 read_attribute(:sort_criteria) || []
626 read_attribute(:sort_criteria) || []
626 end
627 end
627
628
628 def sort_criteria_key(arg)
629 def sort_criteria_key(arg)
629 sort_criteria && sort_criteria[arg] && sort_criteria[arg].first
630 sort_criteria && sort_criteria[arg] && sort_criteria[arg].first
630 end
631 end
631
632
632 def sort_criteria_order(arg)
633 def sort_criteria_order(arg)
633 sort_criteria && sort_criteria[arg] && sort_criteria[arg].last
634 sort_criteria && sort_criteria[arg] && sort_criteria[arg].last
634 end
635 end
635
636
636 def sort_criteria_order_for(key)
637 def sort_criteria_order_for(key)
637 sort_criteria.detect {|k, order| key.to_s == k}.try(:last)
638 sort_criteria.detect {|k, order| key.to_s == k}.try(:last)
638 end
639 end
639
640
640 # Returns the SQL sort order that should be prepended for grouping
641 # Returns the SQL sort order that should be prepended for grouping
641 def group_by_sort_order
642 def group_by_sort_order
642 if column = group_by_column
643 if column = group_by_column
643 order = (sort_criteria_order_for(column.name) || column.default_order || 'asc').try(:upcase)
644 order = (sort_criteria_order_for(column.name) || column.default_order || 'asc').try(:upcase)
644 Array(column.sortable).map {|s| "#{s} #{order}"}
645 Array(column.sortable).map {|s| "#{s} #{order}"}
645 end
646 end
646 end
647 end
647
648
648 # Returns true if the query is a grouped query
649 # Returns true if the query is a grouped query
649 def grouped?
650 def grouped?
650 !group_by_column.nil?
651 !group_by_column.nil?
651 end
652 end
652
653
653 def group_by_column
654 def group_by_column
654 groupable_columns.detect {|c| c.groupable && c.name.to_s == group_by}
655 groupable_columns.detect {|c| c.groupable && c.name.to_s == group_by}
655 end
656 end
656
657
657 def group_by_statement
658 def group_by_statement
658 group_by_column.try(:groupable)
659 group_by_column.try(:groupable)
659 end
660 end
660
661
661 def project_statement
662 def project_statement
662 project_clauses = []
663 project_clauses = []
663 if project && !project.descendants.active.empty?
664 if project && !project.descendants.active.empty?
664 if has_filter?("subproject_id")
665 if has_filter?("subproject_id")
665 case operator_for("subproject_id")
666 case operator_for("subproject_id")
666 when '='
667 when '='
667 # include the selected subprojects
668 # include the selected subprojects
668 ids = [project.id] + values_for("subproject_id").each(&:to_i)
669 ids = [project.id] + values_for("subproject_id").each(&:to_i)
669 project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
670 project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
670 when '!*'
671 when '!*'
671 # main project only
672 # main project only
672 project_clauses << "#{Project.table_name}.id = %d" % project.id
673 project_clauses << "#{Project.table_name}.id = %d" % project.id
673 else
674 else
674 # all subprojects
675 # all subprojects
675 project_clauses << "#{Project.table_name}.lft >= #{project.lft} AND #{Project.table_name}.rgt <= #{project.rgt}"
676 project_clauses << "#{Project.table_name}.lft >= #{project.lft} AND #{Project.table_name}.rgt <= #{project.rgt}"
676 end
677 end
677 elsif Setting.display_subprojects_issues?
678 elsif Setting.display_subprojects_issues?
678 project_clauses << "#{Project.table_name}.lft >= #{project.lft} AND #{Project.table_name}.rgt <= #{project.rgt}"
679 project_clauses << "#{Project.table_name}.lft >= #{project.lft} AND #{Project.table_name}.rgt <= #{project.rgt}"
679 else
680 else
680 project_clauses << "#{Project.table_name}.id = %d" % project.id
681 project_clauses << "#{Project.table_name}.id = %d" % project.id
681 end
682 end
682 elsif project
683 elsif project
683 project_clauses << "#{Project.table_name}.id = %d" % project.id
684 project_clauses << "#{Project.table_name}.id = %d" % project.id
684 end
685 end
685 project_clauses.any? ? project_clauses.join(' AND ') : nil
686 project_clauses.any? ? project_clauses.join(' AND ') : nil
686 end
687 end
687
688
688 def statement
689 def statement
689 # filters clauses
690 # filters clauses
690 filters_clauses = []
691 filters_clauses = []
691 filters.each_key do |field|
692 filters.each_key do |field|
692 next if field == "subproject_id"
693 next if field == "subproject_id"
693 v = values_for(field).clone
694 v = values_for(field).clone
694 next unless v and !v.empty?
695 next unless v and !v.empty?
695 operator = operator_for(field)
696 operator = operator_for(field)
696
697
697 # "me" value substitution
698 # "me" value substitution
698 if %w(assigned_to_id author_id user_id watcher_id).include?(field)
699 if %w(assigned_to_id author_id user_id watcher_id).include?(field)
699 if v.delete("me")
700 if v.delete("me")
700 if User.current.logged?
701 if User.current.logged?
701 v.push(User.current.id.to_s)
702 v.push(User.current.id.to_s)
702 v += User.current.group_ids.map(&:to_s) if field == 'assigned_to_id'
703 v += User.current.group_ids.map(&:to_s) if field == 'assigned_to_id'
703 else
704 else
704 v.push("0")
705 v.push("0")
705 end
706 end
706 end
707 end
707 end
708 end
708
709
709 if field == 'project_id'
710 if field == 'project_id'
710 if v.delete('mine')
711 if v.delete('mine')
711 v += User.current.memberships.map(&:project_id).map(&:to_s)
712 v += User.current.memberships.map(&:project_id).map(&:to_s)
712 end
713 end
713 end
714 end
714
715
715 if field =~ /cf_(\d+)$/
716 if field =~ /cf_(\d+)$/
716 # custom field
717 # custom field
717 filters_clauses << sql_for_custom_field(field, operator, v, $1)
718 filters_clauses << sql_for_custom_field(field, operator, v, $1)
718 elsif respond_to?(method = "sql_for_#{field.gsub('.','_')}_field")
719 elsif respond_to?(method = "sql_for_#{field.gsub('.','_')}_field")
719 # specific statement
720 # specific statement
720 filters_clauses << send(method, field, operator, v)
721 filters_clauses << send(method, field, operator, v)
721 else
722 else
722 # regular field
723 # regular field
723 filters_clauses << '(' + sql_for_field(field, operator, v, queried_table_name, field) + ')'
724 filters_clauses << '(' + sql_for_field(field, operator, v, queried_table_name, field) + ')'
724 end
725 end
725 end if filters and valid?
726 end if filters and valid?
726
727
727 if (c = group_by_column) && c.is_a?(QueryCustomFieldColumn)
728 if (c = group_by_column) && c.is_a?(QueryCustomFieldColumn)
728 # Excludes results for which the grouped custom field is not visible
729 # Excludes results for which the grouped custom field is not visible
729 filters_clauses << c.custom_field.visibility_by_project_condition
730 filters_clauses << c.custom_field.visibility_by_project_condition
730 end
731 end
731
732
732 filters_clauses << project_statement
733 filters_clauses << project_statement
733 filters_clauses.reject!(&:blank?)
734 filters_clauses.reject!(&:blank?)
734
735
735 filters_clauses.any? ? filters_clauses.join(' AND ') : nil
736 filters_clauses.any? ? filters_clauses.join(' AND ') : nil
736 end
737 end
737
738
738 # Returns the sum of values for the given column
739 # Returns the sum of values for the given column
739 def total_for(column)
740 def total_for(column)
740 total_with_scope(column, base_scope)
741 total_with_scope(column, base_scope)
741 end
742 end
742
743
743 # Returns a hash of the sum of the given column for each group,
744 # Returns a hash of the sum of the given column for each group,
744 # or nil if the query is not grouped
745 # or nil if the query is not grouped
745 def total_by_group_for(column)
746 def total_by_group_for(column)
746 grouped_query do |scope|
747 grouped_query do |scope|
747 total_with_scope(column, scope)
748 total_with_scope(column, scope)
748 end
749 end
749 end
750 end
750
751
751 def totals
752 def totals
752 totals = totalable_columns.map {|column| [column, total_for(column)]}
753 totals = totalable_columns.map {|column| [column, total_for(column)]}
753 yield totals if block_given?
754 yield totals if block_given?
754 totals
755 totals
755 end
756 end
756
757
757 def totals_by_group
758 def totals_by_group
758 totals = totalable_columns.map {|column| [column, total_by_group_for(column)]}
759 totals = totalable_columns.map {|column| [column, total_by_group_for(column)]}
759 yield totals if block_given?
760 yield totals if block_given?
760 totals
761 totals
761 end
762 end
762
763
763 private
764 private
764
765
765 def grouped_query(&block)
766 def grouped_query(&block)
766 r = nil
767 r = nil
767 if grouped?
768 if grouped?
768 begin
769 begin
769 # Rails3 will raise an (unexpected) RecordNotFound if there's only a nil group value
770 # Rails3 will raise an (unexpected) RecordNotFound if there's only a nil group value
770 r = yield base_group_scope
771 r = yield base_group_scope
771 rescue ActiveRecord::RecordNotFound
772 rescue ActiveRecord::RecordNotFound
772 r = {nil => yield(base_scope)}
773 r = {nil => yield(base_scope)}
773 end
774 end
774 c = group_by_column
775 c = group_by_column
775 if c.is_a?(QueryCustomFieldColumn)
776 if c.is_a?(QueryCustomFieldColumn)
776 r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
777 r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
777 end
778 end
778 end
779 end
779 r
780 r
780 rescue ::ActiveRecord::StatementInvalid => e
781 rescue ::ActiveRecord::StatementInvalid => e
781 raise StatementInvalid.new(e.message)
782 raise StatementInvalid.new(e.message)
782 end
783 end
783
784
784 def total_with_scope(column, scope)
785 def total_with_scope(column, scope)
785 unless column.is_a?(QueryColumn)
786 unless column.is_a?(QueryColumn)
786 column = column.to_sym
787 column = column.to_sym
787 column = available_totalable_columns.detect {|c| c.name == column}
788 column = available_totalable_columns.detect {|c| c.name == column}
788 end
789 end
789 if column.is_a?(QueryCustomFieldColumn)
790 if column.is_a?(QueryCustomFieldColumn)
790 custom_field = column.custom_field
791 custom_field = column.custom_field
791 send "total_for_custom_field", custom_field, scope
792 send "total_for_custom_field", custom_field, scope
792 else
793 else
793 send "total_for_#{column.name}", scope
794 send "total_for_#{column.name}", scope
794 end
795 end
795 rescue ::ActiveRecord::StatementInvalid => e
796 rescue ::ActiveRecord::StatementInvalid => e
796 raise StatementInvalid.new(e.message)
797 raise StatementInvalid.new(e.message)
797 end
798 end
798
799
799 def base_scope
800 def base_scope
800 raise "unimplemented"
801 raise "unimplemented"
801 end
802 end
802
803
803 def base_group_scope
804 def base_group_scope
804 base_scope.
805 base_scope.
805 joins(joins_for_order_statement(group_by_statement)).
806 joins(joins_for_order_statement(group_by_statement)).
806 group(group_by_statement)
807 group(group_by_statement)
807 end
808 end
808
809
809 def total_for_custom_field(custom_field, scope, &block)
810 def total_for_custom_field(custom_field, scope, &block)
810 total = custom_field.format.total_for_scope(custom_field, scope)
811 total = custom_field.format.total_for_scope(custom_field, scope)
811 total = map_total(total) {|t| custom_field.format.cast_total_value(custom_field, t)}
812 total = map_total(total) {|t| custom_field.format.cast_total_value(custom_field, t)}
812 total
813 total
813 end
814 end
814
815
815 def map_total(total, &block)
816 def map_total(total, &block)
816 if total.is_a?(Hash)
817 if total.is_a?(Hash)
817 total.keys.each {|k| total[k] = yield total[k]}
818 total.keys.each {|k| total[k] = yield total[k]}
818 else
819 else
819 total = yield total
820 total = yield total
820 end
821 end
821 total
822 total
822 end
823 end
823
824
824 def sql_for_custom_field(field, operator, value, custom_field_id)
825 def sql_for_custom_field(field, operator, value, custom_field_id)
825 db_table = CustomValue.table_name
826 db_table = CustomValue.table_name
826 db_field = 'value'
827 db_field = 'value'
827 filter = @available_filters[field]
828 filter = @available_filters[field]
828 return nil unless filter
829 return nil unless filter
829 if filter[:field].format.target_class && filter[:field].format.target_class <= User
830 if filter[:field].format.target_class && filter[:field].format.target_class <= User
830 if value.delete('me')
831 if value.delete('me')
831 value.push User.current.id.to_s
832 value.push User.current.id.to_s
832 end
833 end
833 end
834 end
834 not_in = nil
835 not_in = nil
835 if operator == '!'
836 if operator == '!'
836 # Makes ! operator work for custom fields with multiple values
837 # Makes ! operator work for custom fields with multiple values
837 operator = '='
838 operator = '='
838 not_in = 'NOT'
839 not_in = 'NOT'
839 end
840 end
840 customized_key = "id"
841 customized_key = "id"
841 customized_class = queried_class
842 customized_class = queried_class
842 if field =~ /^(.+)\.cf_/
843 if field =~ /^(.+)\.cf_/
843 assoc = $1
844 assoc = $1
844 customized_key = "#{assoc}_id"
845 customized_key = "#{assoc}_id"
845 customized_class = queried_class.reflect_on_association(assoc.to_sym).klass.base_class rescue nil
846 customized_class = queried_class.reflect_on_association(assoc.to_sym).klass.base_class rescue nil
846 raise "Unknown #{queried_class.name} association #{assoc}" unless customized_class
847 raise "Unknown #{queried_class.name} association #{assoc}" unless customized_class
847 end
848 end
848 where = sql_for_field(field, operator, value, db_table, db_field, true)
849 where = sql_for_field(field, operator, value, db_table, db_field, true)
849 if operator =~ /[<>]/
850 if operator =~ /[<>]/
850 where = "(#{where}) AND #{db_table}.#{db_field} <> ''"
851 where = "(#{where}) AND #{db_table}.#{db_field} <> ''"
851 end
852 end
852 "#{queried_table_name}.#{customized_key} #{not_in} IN (" +
853 "#{queried_table_name}.#{customized_key} #{not_in} IN (" +
853 "SELECT #{customized_class.table_name}.id FROM #{customized_class.table_name}" +
854 "SELECT #{customized_class.table_name}.id FROM #{customized_class.table_name}" +
854 " LEFT OUTER JOIN #{db_table} ON #{db_table}.customized_type='#{customized_class}' AND #{db_table}.customized_id=#{customized_class.table_name}.id AND #{db_table}.custom_field_id=#{custom_field_id}" +
855 " LEFT OUTER JOIN #{db_table} ON #{db_table}.customized_type='#{customized_class}' AND #{db_table}.customized_id=#{customized_class.table_name}.id AND #{db_table}.custom_field_id=#{custom_field_id}" +
855 " WHERE (#{where}) AND (#{filter[:field].visibility_by_project_condition}))"
856 " WHERE (#{where}) AND (#{filter[:field].visibility_by_project_condition}))"
856 end
857 end
857
858
858 # Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+
859 # Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+
859 def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)
860 def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)
860 sql = ''
861 sql = ''
861 case operator
862 case operator
862 when "="
863 when "="
863 if value.any?
864 if value.any?
864 case type_for(field)
865 case type_for(field)
865 when :date, :date_past
866 when :date, :date_past
866 sql = date_clause(db_table, db_field, parse_date(value.first), parse_date(value.first), is_custom_filter)
867 sql = date_clause(db_table, db_field, parse_date(value.first), parse_date(value.first), is_custom_filter)
867 when :integer
868 when :integer
868 int_values = value.first.to_s.scan(/[+-]?\d+/).map(&:to_i).join(",")
869 int_values = value.first.to_s.scan(/[+-]?\d+/).map(&:to_i).join(",")
869 if int_values.present?
870 if int_values.present?
870 if is_custom_filter
871 if is_custom_filter
871 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) IN (#{int_values}))"
872 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) IN (#{int_values}))"
872 else
873 else
873 sql = "#{db_table}.#{db_field} IN (#{int_values})"
874 sql = "#{db_table}.#{db_field} IN (#{int_values})"
874 end
875 end
875 else
876 else
876 sql = "1=0"
877 sql = "1=0"
877 end
878 end
878 when :float
879 when :float
879 if is_custom_filter
880 if is_custom_filter
880 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5})"
881 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5})"
881 else
882 else
882 sql = "#{db_table}.#{db_field} BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5}"
883 sql = "#{db_table}.#{db_field} BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5}"
883 end
884 end
884 else
885 else
885 sql = queried_class.send(:sanitize_sql_for_conditions, ["#{db_table}.#{db_field} IN (?)", value])
886 sql = queried_class.send(:sanitize_sql_for_conditions, ["#{db_table}.#{db_field} IN (?)", value])
886 end
887 end
887 else
888 else
888 # IN an empty set
889 # IN an empty set
889 sql = "1=0"
890 sql = "1=0"
890 end
891 end
891 when "!"
892 when "!"
892 if value.any?
893 if value.any?
893 sql = queried_class.send(:sanitize_sql_for_conditions, ["(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (?))", value])
894 sql = queried_class.send(:sanitize_sql_for_conditions, ["(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (?))", value])
894 else
895 else
895 # NOT IN an empty set
896 # NOT IN an empty set
896 sql = "1=1"
897 sql = "1=1"
897 end
898 end
898 when "!*"
899 when "!*"
899 sql = "#{db_table}.#{db_field} IS NULL"
900 sql = "#{db_table}.#{db_field} IS NULL"
900 sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter
901 sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter
901 when "*"
902 when "*"
902 sql = "#{db_table}.#{db_field} IS NOT NULL"
903 sql = "#{db_table}.#{db_field} IS NOT NULL"
903 sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
904 sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
904 when ">="
905 when ">="
905 if [:date, :date_past].include?(type_for(field))
906 if [:date, :date_past].include?(type_for(field))
906 sql = date_clause(db_table, db_field, parse_date(value.first), nil, is_custom_filter)
907 sql = date_clause(db_table, db_field, parse_date(value.first), nil, is_custom_filter)
907 else
908 else
908 if is_custom_filter
909 if is_custom_filter
909 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) >= #{value.first.to_f})"
910 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) >= #{value.first.to_f})"
910 else
911 else
911 sql = "#{db_table}.#{db_field} >= #{value.first.to_f}"
912 sql = "#{db_table}.#{db_field} >= #{value.first.to_f}"
912 end
913 end
913 end
914 end
914 when "<="
915 when "<="
915 if [:date, :date_past].include?(type_for(field))
916 if [:date, :date_past].include?(type_for(field))
916 sql = date_clause(db_table, db_field, nil, parse_date(value.first), is_custom_filter)
917 sql = date_clause(db_table, db_field, nil, parse_date(value.first), is_custom_filter)
917 else
918 else
918 if is_custom_filter
919 if is_custom_filter
919 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) <= #{value.first.to_f})"
920 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) <= #{value.first.to_f})"
920 else
921 else
921 sql = "#{db_table}.#{db_field} <= #{value.first.to_f}"
922 sql = "#{db_table}.#{db_field} <= #{value.first.to_f}"
922 end
923 end
923 end
924 end
924 when "><"
925 when "><"
925 if [:date, :date_past].include?(type_for(field))
926 if [:date, :date_past].include?(type_for(field))
926 sql = date_clause(db_table, db_field, parse_date(value[0]), parse_date(value[1]), is_custom_filter)
927 sql = date_clause(db_table, db_field, parse_date(value[0]), parse_date(value[1]), is_custom_filter)
927 else
928 else
928 if is_custom_filter
929 if is_custom_filter
929 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) BETWEEN #{value[0].to_f} AND #{value[1].to_f})"
930 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) BETWEEN #{value[0].to_f} AND #{value[1].to_f})"
930 else
931 else
931 sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
932 sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
932 end
933 end
933 end
934 end
934 when "o"
935 when "o"
935 sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{self.class.connection.quoted_false})" if field == "status_id"
936 sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{self.class.connection.quoted_false})" if field == "status_id"
936 when "c"
937 when "c"
937 sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{self.class.connection.quoted_true})" if field == "status_id"
938 sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{self.class.connection.quoted_true})" if field == "status_id"
938 when "><t-"
939 when "><t-"
939 # between today - n days and today
940 # between today - n days and today
940 sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0, is_custom_filter)
941 sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0, is_custom_filter)
941 when ">t-"
942 when ">t-"
942 # >= today - n days
943 # >= today - n days
943 sql = relative_date_clause(db_table, db_field, - value.first.to_i, nil, is_custom_filter)
944 sql = relative_date_clause(db_table, db_field, - value.first.to_i, nil, is_custom_filter)
944 when "<t-"
945 when "<t-"
945 # <= today - n days
946 # <= today - n days
946 sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i, is_custom_filter)
947 sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i, is_custom_filter)
947 when "t-"
948 when "t-"
948 # = n days in past
949 # = n days in past
949 sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i, is_custom_filter)
950 sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i, is_custom_filter)
950 when "><t+"
951 when "><t+"
951 # between today and today + n days
952 # between today and today + n days
952 sql = relative_date_clause(db_table, db_field, 0, value.first.to_i, is_custom_filter)
953 sql = relative_date_clause(db_table, db_field, 0, value.first.to_i, is_custom_filter)
953 when ">t+"
954 when ">t+"
954 # >= today + n days
955 # >= today + n days
955 sql = relative_date_clause(db_table, db_field, value.first.to_i, nil, is_custom_filter)
956 sql = relative_date_clause(db_table, db_field, value.first.to_i, nil, is_custom_filter)
956 when "<t+"
957 when "<t+"
957 # <= today + n days
958 # <= today + n days
958 sql = relative_date_clause(db_table, db_field, nil, value.first.to_i, is_custom_filter)
959 sql = relative_date_clause(db_table, db_field, nil, value.first.to_i, is_custom_filter)
959 when "t+"
960 when "t+"
960 # = today + n days
961 # = today + n days
961 sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i, is_custom_filter)
962 sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i, is_custom_filter)
962 when "t"
963 when "t"
963 # = today
964 # = today
964 sql = relative_date_clause(db_table, db_field, 0, 0, is_custom_filter)
965 sql = relative_date_clause(db_table, db_field, 0, 0, is_custom_filter)
965 when "ld"
966 when "ld"
966 # = yesterday
967 # = yesterday
967 sql = relative_date_clause(db_table, db_field, -1, -1, is_custom_filter)
968 sql = relative_date_clause(db_table, db_field, -1, -1, is_custom_filter)
968 when "w"
969 when "w"
969 # = this week
970 # = this week
970 first_day_of_week = l(:general_first_day_of_week).to_i
971 first_day_of_week = l(:general_first_day_of_week).to_i
971 day_of_week = User.current.today.cwday
972 day_of_week = User.current.today.cwday
972 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
973 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
973 sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6, is_custom_filter)
974 sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6, is_custom_filter)
974 when "lw"
975 when "lw"
975 # = last week
976 # = last week
976 first_day_of_week = l(:general_first_day_of_week).to_i
977 first_day_of_week = l(:general_first_day_of_week).to_i
977 day_of_week = User.current.today.cwday
978 day_of_week = User.current.today.cwday
978 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
979 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
979 sql = relative_date_clause(db_table, db_field, - days_ago - 7, - days_ago - 1, is_custom_filter)
980 sql = relative_date_clause(db_table, db_field, - days_ago - 7, - days_ago - 1, is_custom_filter)
980 when "l2w"
981 when "l2w"
981 # = last 2 weeks
982 # = last 2 weeks
982 first_day_of_week = l(:general_first_day_of_week).to_i
983 first_day_of_week = l(:general_first_day_of_week).to_i
983 day_of_week = User.current.today.cwday
984 day_of_week = User.current.today.cwday
984 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
985 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
985 sql = relative_date_clause(db_table, db_field, - days_ago - 14, - days_ago - 1, is_custom_filter)
986 sql = relative_date_clause(db_table, db_field, - days_ago - 14, - days_ago - 1, is_custom_filter)
986 when "m"
987 when "m"
987 # = this month
988 # = this month
988 date = User.current.today
989 date = User.current.today
989 sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month, is_custom_filter)
990 sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month, is_custom_filter)
990 when "lm"
991 when "lm"
991 # = last month
992 # = last month
992 date = User.current.today.prev_month
993 date = User.current.today.prev_month
993 sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month, is_custom_filter)
994 sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month, is_custom_filter)
994 when "y"
995 when "y"
995 # = this year
996 # = this year
996 date = User.current.today
997 date = User.current.today
997 sql = date_clause(db_table, db_field, date.beginning_of_year, date.end_of_year, is_custom_filter)
998 sql = date_clause(db_table, db_field, date.beginning_of_year, date.end_of_year, is_custom_filter)
998 when "~"
999 when "~"
999 sql = sql_contains("#{db_table}.#{db_field}", value.first)
1000 sql = sql_contains("#{db_table}.#{db_field}", value.first)
1000 when "!~"
1001 when "!~"
1001 sql = sql_contains("#{db_table}.#{db_field}", value.first, false)
1002 sql = sql_contains("#{db_table}.#{db_field}", value.first, false)
1002 else
1003 else
1003 raise "Unknown query operator #{operator}"
1004 raise "Unknown query operator #{operator}"
1004 end
1005 end
1005
1006
1006 return sql
1007 return sql
1007 end
1008 end
1008
1009
1009 # Returns a SQL LIKE statement with wildcards
1010 # Returns a SQL LIKE statement with wildcards
1010 def sql_contains(db_field, value, match=true)
1011 def sql_contains(db_field, value, match=true)
1011 queried_class.send :sanitize_sql_for_conditions,
1012 queried_class.send :sanitize_sql_for_conditions,
1012 [Redmine::Database.like(db_field, '?', :match => match), "%#{value}%"]
1013 [Redmine::Database.like(db_field, '?', :match => match), "%#{value}%"]
1013 end
1014 end
1014
1015
1015 # Adds a filter for the given custom field
1016 # Adds a filter for the given custom field
1016 def add_custom_field_filter(field, assoc=nil)
1017 def add_custom_field_filter(field, assoc=nil)
1017 options = field.query_filter_options(self)
1018 options = field.query_filter_options(self)
1018 if field.format.target_class && field.format.target_class <= User
1019 if field.format.target_class && field.format.target_class <= User
1019 if options[:values].is_a?(Array) && User.current.logged?
1020 if options[:values].is_a?(Array) && User.current.logged?
1020 options[:values].unshift ["<< #{l(:label_me)} >>", "me"]
1021 options[:values].unshift ["<< #{l(:label_me)} >>", "me"]
1021 end
1022 end
1022 end
1023 end
1023
1024
1024 filter_id = "cf_#{field.id}"
1025 filter_id = "cf_#{field.id}"
1025 filter_name = field.name
1026 filter_name = field.name
1026 if assoc.present?
1027 if assoc.present?
1027 filter_id = "#{assoc}.#{filter_id}"
1028 filter_id = "#{assoc}.#{filter_id}"
1028 filter_name = l("label_attribute_of_#{assoc}", :name => filter_name)
1029 filter_name = l("label_attribute_of_#{assoc}", :name => filter_name)
1029 end
1030 end
1030 add_available_filter filter_id, options.merge({
1031 add_available_filter filter_id, options.merge({
1031 :name => filter_name,
1032 :name => filter_name,
1032 :field => field
1033 :field => field
1033 })
1034 })
1034 end
1035 end
1035
1036
1036 # Adds filters for the given custom fields scope
1037 # Adds filters for the given custom fields scope
1037 def add_custom_fields_filters(scope, assoc=nil)
1038 def add_custom_fields_filters(scope, assoc=nil)
1038 scope.visible.where(:is_filter => true).sorted.each do |field|
1039 scope.visible.where(:is_filter => true).sorted.each do |field|
1039 add_custom_field_filter(field, assoc)
1040 add_custom_field_filter(field, assoc)
1040 end
1041 end
1041 end
1042 end
1042
1043
1043 # Adds filters for the given associations custom fields
1044 # Adds filters for the given associations custom fields
1044 def add_associations_custom_fields_filters(*associations)
1045 def add_associations_custom_fields_filters(*associations)
1045 fields_by_class = CustomField.visible.where(:is_filter => true).group_by(&:class)
1046 fields_by_class = CustomField.visible.where(:is_filter => true).group_by(&:class)
1046 associations.each do |assoc|
1047 associations.each do |assoc|
1047 association_klass = queried_class.reflect_on_association(assoc).klass
1048 association_klass = queried_class.reflect_on_association(assoc).klass
1048 fields_by_class.each do |field_class, fields|
1049 fields_by_class.each do |field_class, fields|
1049 if field_class.customized_class <= association_klass
1050 if field_class.customized_class <= association_klass
1050 fields.sort.each do |field|
1051 fields.sort.each do |field|
1051 add_custom_field_filter(field, assoc)
1052 add_custom_field_filter(field, assoc)
1052 end
1053 end
1053 end
1054 end
1054 end
1055 end
1055 end
1056 end
1056 end
1057 end
1057
1058
1058 def quoted_time(time, is_custom_filter)
1059 def quoted_time(time, is_custom_filter)
1059 if is_custom_filter
1060 if is_custom_filter
1060 # Custom field values are stored as strings in the DB
1061 # Custom field values are stored as strings in the DB
1061 # using this format that does not depend on DB date representation
1062 # using this format that does not depend on DB date representation
1062 time.strftime("%Y-%m-%d %H:%M:%S")
1063 time.strftime("%Y-%m-%d %H:%M:%S")
1063 else
1064 else
1064 self.class.connection.quoted_date(time)
1065 self.class.connection.quoted_date(time)
1065 end
1066 end
1066 end
1067 end
1067
1068
1068 def date_for_user_time_zone(y, m, d)
1069 def date_for_user_time_zone(y, m, d)
1069 if tz = User.current.time_zone
1070 if tz = User.current.time_zone
1070 tz.local y, m, d
1071 tz.local y, m, d
1071 else
1072 else
1072 Time.local y, m, d
1073 Time.local y, m, d
1073 end
1074 end
1074 end
1075 end
1075
1076
1076 # Returns a SQL clause for a date or datetime field.
1077 # Returns a SQL clause for a date or datetime field.
1077 def date_clause(table, field, from, to, is_custom_filter)
1078 def date_clause(table, field, from, to, is_custom_filter)
1078 s = []
1079 s = []
1079 if from
1080 if from
1080 if from.is_a?(Date)
1081 if from.is_a?(Date)
1081 from = date_for_user_time_zone(from.year, from.month, from.day).yesterday.end_of_day
1082 from = date_for_user_time_zone(from.year, from.month, from.day).yesterday.end_of_day
1082 else
1083 else
1083 from = from - 1 # second
1084 from = from - 1 # second
1084 end
1085 end
1085 if self.class.default_timezone == :utc
1086 if self.class.default_timezone == :utc
1086 from = from.utc
1087 from = from.utc
1087 end
1088 end
1088 s << ("#{table}.#{field} > '%s'" % [quoted_time(from, is_custom_filter)])
1089 s << ("#{table}.#{field} > '%s'" % [quoted_time(from, is_custom_filter)])
1089 end
1090 end
1090 if to
1091 if to
1091 if to.is_a?(Date)
1092 if to.is_a?(Date)
1092 to = date_for_user_time_zone(to.year, to.month, to.day).end_of_day
1093 to = date_for_user_time_zone(to.year, to.month, to.day).end_of_day
1093 end
1094 end
1094 if self.class.default_timezone == :utc
1095 if self.class.default_timezone == :utc
1095 to = to.utc
1096 to = to.utc
1096 end
1097 end
1097 s << ("#{table}.#{field} <= '%s'" % [quoted_time(to, is_custom_filter)])
1098 s << ("#{table}.#{field} <= '%s'" % [quoted_time(to, is_custom_filter)])
1098 end
1099 end
1099 s.join(' AND ')
1100 s.join(' AND ')
1100 end
1101 end
1101
1102
1102 # Returns a SQL clause for a date or datetime field using relative dates.
1103 # Returns a SQL clause for a date or datetime field using relative dates.
1103 def relative_date_clause(table, field, days_from, days_to, is_custom_filter)
1104 def relative_date_clause(table, field, days_from, days_to, is_custom_filter)
1104 date_clause(table, field, (days_from ? User.current.today + days_from : nil), (days_to ? User.current.today + days_to : nil), is_custom_filter)
1105 date_clause(table, field, (days_from ? User.current.today + days_from : nil), (days_to ? User.current.today + days_to : nil), is_custom_filter)
1105 end
1106 end
1106
1107
1107 # Returns a Date or Time from the given filter value
1108 # Returns a Date or Time from the given filter value
1108 def parse_date(arg)
1109 def parse_date(arg)
1109 if arg.to_s =~ /\A\d{4}-\d{2}-\d{2}T/
1110 if arg.to_s =~ /\A\d{4}-\d{2}-\d{2}T/
1110 Time.parse(arg) rescue nil
1111 Time.parse(arg) rescue nil
1111 else
1112 else
1112 Date.parse(arg) rescue nil
1113 Date.parse(arg) rescue nil
1113 end
1114 end
1114 end
1115 end
1115
1116
1116 # Additional joins required for the given sort options
1117 # Additional joins required for the given sort options
1117 def joins_for_order_statement(order_options)
1118 def joins_for_order_statement(order_options)
1118 joins = []
1119 joins = []
1119
1120
1120 if order_options
1121 if order_options
1121 if order_options.include?('authors')
1122 if order_options.include?('authors')
1122 joins << "LEFT OUTER JOIN #{User.table_name} authors ON authors.id = #{queried_table_name}.author_id"
1123 joins << "LEFT OUTER JOIN #{User.table_name} authors ON authors.id = #{queried_table_name}.author_id"
1123 end
1124 end
1124 order_options.scan(/cf_\d+/).uniq.each do |name|
1125 order_options.scan(/cf_\d+/).uniq.each do |name|
1125 column = available_columns.detect {|c| c.name.to_s == name}
1126 column = available_columns.detect {|c| c.name.to_s == name}
1126 join = column && column.custom_field.join_for_order_statement
1127 join = column && column.custom_field.join_for_order_statement
1127 if join
1128 if join
1128 joins << join
1129 joins << join
1129 end
1130 end
1130 end
1131 end
1131 end
1132 end
1132
1133
1133 joins.any? ? joins.join(' ') : nil
1134 joins.any? ? joins.join(' ') : nil
1134 end
1135 end
1135 end
1136 end
General Comments 0
You need to be logged in to leave comments. Login now