##// END OF EJS Templates
jro_apps » redmine
RSS
Avoid N queries when displaying the issue list with custom fields....
Jean-Philippe Lang -
r11993:3172359f9704
parent child
Show More
Side by Side Unified
Context file:
r11993:3172359f9704 Loading diff...:
Show file before | Show file after | Hide comments
@@ -1,478 +1,483
1 1 # Redmine - project management software
2 2 # Copyright (C) 2006-2013 Jean-Philippe Lang
3 3 #
4 4 # This program is free software; you can redistribute it and/or
5 5 # modify it under the terms of the GNU General Public License
6 6 # as published by the Free Software Foundation; either version 2
7 7 # of the License, or (at your option) any later version.
8 8 #
9 9 # This program is distributed in the hope that it will be useful,
10 10 # but WITHOUT ANY WARRANTY; without even the implied warranty of
11 11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 12 # GNU General Public License for more details.
13 13 #
14 14 # You should have received a copy of the GNU General Public License
15 15 # along with this program; if not, write to the Free Software
16 16 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
17 17
18 18 class IssueQuery < Query
19 19
20 20 self.queried_class = Issue
21 21
22 22 self.available_columns = [
23 23 QueryColumn.new(:id, :sortable => "#{Issue.table_name}.id", :default_order => 'desc', :caption => '#', :frozen => true),
24 24 QueryColumn.new(:project, :sortable => "#{Project.table_name}.name", :groupable => true),
25 25 QueryColumn.new(:tracker, :sortable => "#{Tracker.table_name}.position", :groupable => true),
26 26 QueryColumn.new(:parent, :sortable => ["#{Issue.table_name}.root_id", "#{Issue.table_name}.lft ASC"], :default_order => 'desc', :caption => :field_parent_issue),
27 27 QueryColumn.new(:status, :sortable => "#{IssueStatus.table_name}.position", :groupable => true),
28 28 QueryColumn.new(:priority, :sortable => "#{IssuePriority.table_name}.position", :default_order => 'desc', :groupable => true),
29 29 QueryColumn.new(:subject, :sortable => "#{Issue.table_name}.subject"),
30 30 QueryColumn.new(:author, :sortable => lambda {User.fields_for_order_statement("authors")}, :groupable => true),
31 31 QueryColumn.new(:assigned_to, :sortable => lambda {User.fields_for_order_statement}, :groupable => true),
32 32 QueryColumn.new(:updated_on, :sortable => "#{Issue.table_name}.updated_on", :default_order => 'desc'),
33 33 QueryColumn.new(:category, :sortable => "#{IssueCategory.table_name}.name", :groupable => true),
34 34 QueryColumn.new(:fixed_version, :sortable => lambda {Version.fields_for_order_statement}, :groupable => true),
35 35 QueryColumn.new(:start_date, :sortable => "#{Issue.table_name}.start_date"),
36 36 QueryColumn.new(:due_date, :sortable => "#{Issue.table_name}.due_date"),
37 37 QueryColumn.new(:estimated_hours, :sortable => "#{Issue.table_name}.estimated_hours"),
38 38 QueryColumn.new(:done_ratio, :sortable => "#{Issue.table_name}.done_ratio", :groupable => true),
39 39 QueryColumn.new(:created_on, :sortable => "#{Issue.table_name}.created_on", :default_order => 'desc'),
40 40 QueryColumn.new(:closed_on, :sortable => "#{Issue.table_name}.closed_on", :default_order => 'desc'),
41 41 QueryColumn.new(:relations, :caption => :label_related_issues),
42 42 QueryColumn.new(:description, :inline => false)
43 43 ]
44 44
45 45 scope :visible, lambda {|*args|
46 46 user = args.shift || User.current
47 47 base = Project.allowed_to_condition(user, :view_issues, *args)
48 48 scope = includes(:project).where("#{table_name}.project_id IS NULL OR (#{base})")
49 49
50 50 if user.admin?
51 51 scope.where("#{table_name}.visibility <> ? OR #{table_name}.user_id = ?", VISIBILITY_PRIVATE, user.id)
52 52 elsif user.memberships.any?
53 53 scope.where("#{table_name}.visibility = ?" +
54 54 " OR (#{table_name}.visibility = ? AND #{table_name}.id IN (" +
55 55 "SELECT DISTINCT q.id FROM #{table_name} q" +
56 56 " INNER JOIN #{table_name_prefix}queries_roles#{table_name_suffix} qr on qr.query_id = q.id" +
57 57 " INNER JOIN #{MemberRole.table_name} mr ON mr.role_id = qr.role_id" +
58 58 " INNER JOIN #{Member.table_name} m ON m.id = mr.member_id AND m.user_id = ?" +
59 59 " WHERE q.project_id IS NULL OR q.project_id = m.project_id))" +
60 60 " OR #{table_name}.user_id = ?",
61 61 VISIBILITY_PUBLIC, VISIBILITY_ROLES, user.id, user.id)
62 62 elsif user.logged?
63 63 scope.where("#{table_name}.visibility = ? OR #{table_name}.user_id = ?", VISIBILITY_PUBLIC, user.id)
64 64 else
65 65 scope.where("#{table_name}.visibility = ?", VISIBILITY_PUBLIC)
66 66 end
67 67 }
68 68
69 69 def initialize(attributes=nil, *args)
70 70 super attributes
71 71 self.filters ||= { 'status_id' => {:operator => "o", :values => [""]} }
72 72 end
73 73
74 74 # Returns true if the query is visible to +user+ or the current user.
75 75 def visible?(user=User.current)
76 76 return true if user.admin?
77 77 return false unless project.nil? || user.allowed_to?(:view_issues, project)
78 78 case visibility
79 79 when VISIBILITY_PUBLIC
80 80 true
81 81 when VISIBILITY_ROLES
82 82 if project
83 83 (user.roles_for_project(project) & roles).any?
84 84 else
85 85 Member.where(:user_id => user.id).joins(:roles).where(:member_roles => {:role_id => roles.map(&:id)}).any?
86 86 end
87 87 else
88 88 user == self.user
89 89 end
90 90 end
91 91
92 92 def is_private?
93 93 visibility == VISIBILITY_PRIVATE
94 94 end
95 95
96 96 def is_public?
97 97 !is_private?
98 98 end
99 99
100 100 def draw_relations
101 101 r = options[:draw_relations]
102 102 r.nil? || r == '1'
103 103 end
104 104
105 105 def draw_relations=(arg)
106 106 options[:draw_relations] = (arg == '0' ? '0' : nil)
107 107 end
108 108
109 109 def draw_progress_line
110 110 r = options[:draw_progress_line]
111 111 r == '1'
112 112 end
113 113
114 114 def draw_progress_line=(arg)
115 115 options[:draw_progress_line] = (arg == '1' ? '1' : nil)
116 116 end
117 117
118 118 def build_from_params(params)
119 119 super
120 120 self.draw_relations = params[:draw_relations] || (params[:query] && params[:query][:draw_relations])
121 121 self.draw_progress_line = params[:draw_progress_line] || (params[:query] && params[:query][:draw_progress_line])
122 122 self
123 123 end
124 124
125 125 def initialize_available_filters
126 126 principals = []
127 127 subprojects = []
128 128 versions = []
129 129 categories = []
130 130 issue_custom_fields = []
131 131
132 132 if project
133 133 principals += project.principals.sort
134 134 unless project.leaf?
135 135 subprojects = project.descendants.visible.all
136 136 principals += Principal.member_of(subprojects)
137 137 end
138 138 versions = project.shared_versions.all
139 139 categories = project.issue_categories.all
140 140 issue_custom_fields = project.all_issue_custom_fields
141 141 else
142 142 if all_projects.any?
143 143 principals += Principal.member_of(all_projects)
144 144 end
145 145 versions = Version.visible.find_all_by_sharing('system')
146 146 issue_custom_fields = IssueCustomField.where(:is_for_all => true)
147 147 end
148 148 principals.uniq!
149 149 principals.sort!
150 150 users = principals.select {|p| p.is_a?(User)}
151 151
152 152 add_available_filter "status_id",
153 153 :type => :list_status, :values => IssueStatus.sorted.all.collect{|s| [s.name, s.id.to_s] }
154 154
155 155 if project.nil?
156 156 project_values = []
157 157 if User.current.logged? && User.current.memberships.any?
158 158 project_values << ["<< #{l(:label_my_projects).downcase} >>", "mine"]
159 159 end
160 160 project_values += all_projects_values
161 161 add_available_filter("project_id",
162 162 :type => :list, :values => project_values
163 163 ) unless project_values.empty?
164 164 end
165 165
166 166 add_available_filter "tracker_id",
167 167 :type => :list, :values => trackers.collect{|s| [s.name, s.id.to_s] }
168 168 add_available_filter "priority_id",
169 169 :type => :list, :values => IssuePriority.all.collect{|s| [s.name, s.id.to_s] }
170 170
171 171 author_values = []
172 172 author_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
173 173 author_values += users.collect{|s| [s.name, s.id.to_s] }
174 174 add_available_filter("author_id",
175 175 :type => :list, :values => author_values
176 176 ) unless author_values.empty?
177 177
178 178 assigned_to_values = []
179 179 assigned_to_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
180 180 assigned_to_values += (Setting.issue_group_assignment? ?
181 181 principals : users).collect{|s| [s.name, s.id.to_s] }
182 182 add_available_filter("assigned_to_id",
183 183 :type => :list_optional, :values => assigned_to_values
184 184 ) unless assigned_to_values.empty?
185 185
186 186 group_values = Group.all.collect {|g| [g.name, g.id.to_s] }
187 187 add_available_filter("member_of_group",
188 188 :type => :list_optional, :values => group_values
189 189 ) unless group_values.empty?
190 190
191 191 role_values = Role.givable.collect {|r| [r.name, r.id.to_s] }
192 192 add_available_filter("assigned_to_role",
193 193 :type => :list_optional, :values => role_values
194 194 ) unless role_values.empty?
195 195
196 196 if versions.any?
197 197 add_available_filter "fixed_version_id",
198 198 :type => :list_optional,
199 199 :values => versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] }
200 200 end
201 201
202 202 if categories.any?
203 203 add_available_filter "category_id",
204 204 :type => :list_optional,
205 205 :values => categories.collect{|s| [s.name, s.id.to_s] }
206 206 end
207 207
208 208 add_available_filter "subject", :type => :text
209 209 add_available_filter "created_on", :type => :date_past
210 210 add_available_filter "updated_on", :type => :date_past
211 211 add_available_filter "closed_on", :type => :date_past
212 212 add_available_filter "start_date", :type => :date
213 213 add_available_filter "due_date", :type => :date
214 214 add_available_filter "estimated_hours", :type => :float
215 215 add_available_filter "done_ratio", :type => :integer
216 216
217 217 if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||
218 218 User.current.allowed_to?(:set_own_issues_private, nil, :global => true)
219 219 add_available_filter "is_private",
220 220 :type => :list,
221 221 :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]]
222 222 end
223 223
224 224 if User.current.logged?
225 225 add_available_filter "watcher_id",
226 226 :type => :list, :values => [["<< #{l(:label_me)} >>", "me"]]
227 227 end
228 228
229 229 if subprojects.any?
230 230 add_available_filter "subproject_id",
231 231 :type => :list_subprojects,
232 232 :values => subprojects.collect{|s| [s.name, s.id.to_s] }
233 233 end
234 234
235 235 add_custom_fields_filters(issue_custom_fields)
236 236
237 237 add_associations_custom_fields_filters :project, :author, :assigned_to, :fixed_version
238 238
239 239 IssueRelation::TYPES.each do |relation_type, options|
240 240 add_available_filter relation_type, :type => :relation, :label => options[:name]
241 241 end
242 242
243 243 Tracker.disabled_core_fields(trackers).each {|field|
244 244 delete_available_filter field
245 245 }
246 246 end
247 247
248 248 def available_columns
249 249 return @available_columns if @available_columns
250 250 @available_columns = self.class.available_columns.dup
251 251 @available_columns += (project ?
252 252 project.all_issue_custom_fields :
253 253 IssueCustomField
254 254 ).visible.collect {|cf| QueryCustomFieldColumn.new(cf) }
255 255
256 256 if User.current.allowed_to?(:view_time_entries, project, :global => true)
257 257 index = nil
258 258 @available_columns.each_with_index {|column, i| index = i if column.name == :estimated_hours}
259 259 index = (index ? index + 1 : -1)
260 260 # insert the column after estimated_hours or at the end
261 261 @available_columns.insert index, QueryColumn.new(:spent_hours,
262 262 :sortable => "COALESCE((SELECT SUM(hours) FROM #{TimeEntry.table_name} WHERE #{TimeEntry.table_name}.issue_id = #{Issue.table_name}.id), 0)",
263 263 :default_order => 'desc',
264 264 :caption => :label_spent_time
265 265 )
266 266 end
267 267
268 268 if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||
269 269 User.current.allowed_to?(:set_own_issues_private, nil, :global => true)
270 270 @available_columns << QueryColumn.new(:is_private, :sortable => "#{Issue.table_name}.is_private")
271 271 end
272 272
273 273 disabled_fields = Tracker.disabled_core_fields(trackers).map {|field| field.sub(/_id$/, '')}
274 274 @available_columns.reject! {|column|
275 275 disabled_fields.include?(column.name.to_s)
276 276 }
277 277
278 278 @available_columns
279 279 end
280 280
281 281 def default_columns_names
282 282 @default_columns_names ||= begin
283 283 default_columns = Setting.issue_list_default_columns.map(&:to_sym)
284 284
285 285 project.present? ? default_columns : [:project] | default_columns
286 286 end
287 287 end
288 288
289 289 # Returns the issue count
290 290 def issue_count
291 291 Issue.visible.joins(:status, :project).where(statement).count
292 292 rescue ::ActiveRecord::StatementInvalid => e
293 293 raise StatementInvalid.new(e.message)
294 294 end
295 295
296 296 # Returns the issue count by group or nil if query is not grouped
297 297 def issue_count_by_group
298 298 r = nil
299 299 if grouped?
300 300 begin
301 301 # Rails3 will raise an (unexpected) RecordNotFound if there's only a nil group value
302 302 r = Issue.visible.
303 303 joins(:status, :project).
304 304 where(statement).
305 305 joins(joins_for_order_statement(group_by_statement)).
306 306 group(group_by_statement).
307 307 count
308 308 rescue ActiveRecord::RecordNotFound
309 309 r = {nil => issue_count}
310 310 end
311 311 c = group_by_column
312 312 if c.is_a?(QueryCustomFieldColumn)
313 313 r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
314 314 end
315 315 end
316 316 r
317 317 rescue ::ActiveRecord::StatementInvalid => e
318 318 raise StatementInvalid.new(e.message)
319 319 end
320 320
321 321 # Returns the issues
322 322 # Valid options are :order, :offset, :limit, :include, :conditions
323 323 def issues(options={})
324 324 order_option = [group_by_sort_order, options[:order]].flatten.reject(&:blank?)
325 325
326 issues = Issue.visible.
326 scope = Issue.visible.
327 327 joins(:status, :project).
328 328 where(statement).
329 329 includes(([:status, :project] + (options[:include] || [])).uniq).
330 330 where(options[:conditions]).
331 331 order(order_option).
332 332 joins(joins_for_order_statement(order_option.join(','))).
333 333 limit(options[:limit]).
334 offset(options[:offset]).
335 all
334 offset(options[:offset])
335
336 if has_custom_field_column?
337 scope = scope.preload(:custom_values)
338 end
339
340 issues = scope.all
336 341
337 342 if has_column?(:spent_hours)
338 343 Issue.load_visible_spent_hours(issues)
339 344 end
340 345 if has_column?(:relations)
341 346 Issue.load_visible_relations(issues)
342 347 end
343 348 issues
344 349 rescue ::ActiveRecord::StatementInvalid => e
345 350 raise StatementInvalid.new(e.message)
346 351 end
347 352
348 353 # Returns the issues ids
349 354 def issue_ids(options={})
350 355 order_option = [group_by_sort_order, options[:order]].flatten.reject(&:blank?)
351 356
352 357 Issue.visible.
353 358 joins(:status, :project).
354 359 where(statement).
355 360 includes(([:status, :project] + (options[:include] || [])).uniq).
356 361 where(options[:conditions]).
357 362 order(order_option).
358 363 joins(joins_for_order_statement(order_option.join(','))).
359 364 limit(options[:limit]).
360 365 offset(options[:offset]).
361 366 find_ids
362 367 rescue ::ActiveRecord::StatementInvalid => e
363 368 raise StatementInvalid.new(e.message)
364 369 end
365 370
366 371 # Returns the journals
367 372 # Valid options are :order, :offset, :limit
368 373 def journals(options={})
369 374 Journal.visible.
370 375 joins(:issue => [:project, :status]).
371 376 where(statement).
372 377 order(options[:order]).
373 378 limit(options[:limit]).
374 379 offset(options[:offset]).
375 380 preload(:details, :user, {:issue => [:project, :author, :tracker, :status]}).
376 381 all
377 382 rescue ::ActiveRecord::StatementInvalid => e
378 383 raise StatementInvalid.new(e.message)
379 384 end
380 385
381 386 # Returns the versions
382 387 # Valid options are :conditions
383 388 def versions(options={})
384 389 Version.visible.
385 390 where(project_statement).
386 391 where(options[:conditions]).
387 392 includes(:project).
388 393 all
389 394 rescue ::ActiveRecord::StatementInvalid => e
390 395 raise StatementInvalid.new(e.message)
391 396 end
392 397
393 398 def sql_for_watcher_id_field(field, operator, value)
394 399 db_table = Watcher.table_name
395 400 "#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{db_table}.watchable_id FROM #{db_table} WHERE #{db_table}.watchable_type='Issue' AND " +
396 401 sql_for_field(field, '=', value, db_table, 'user_id') + ')'
397 402 end
398 403
399 404 def sql_for_member_of_group_field(field, operator, value)
400 405 if operator == '*' # Any group
401 406 groups = Group.all
402 407 operator = '=' # Override the operator since we want to find by assigned_to
403 408 elsif operator == "!*"
404 409 groups = Group.all
405 410 operator = '!' # Override the operator since we want to find by assigned_to
406 411 else
407 412 groups = Group.find_all_by_id(value)
408 413 end
409 414 groups ||= []
410 415
411 416 members_of_groups = groups.inject([]) {|user_ids, group|
412 417 user_ids + group.user_ids + [group.id]
413 418 }.uniq.compact.sort.collect(&:to_s)
414 419
415 420 '(' + sql_for_field("assigned_to_id", operator, members_of_groups, Issue.table_name, "assigned_to_id", false) + ')'
416 421 end
417 422
418 423 def sql_for_assigned_to_role_field(field, operator, value)
419 424 case operator
420 425 when "*", "!*" # Member / Not member
421 426 sw = operator == "!*" ? 'NOT' : ''
422 427 nl = operator == "!*" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''
423 428 "(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}" +
424 429 " WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id))"
425 430 when "=", "!"
426 431 role_cond = value.any? ?
427 432 "#{MemberRole.table_name}.role_id IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")" :
428 433 "1=0"
429 434
430 435 sw = operator == "!" ? 'NOT' : ''
431 436 nl = operator == "!" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''
432 437 "(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}, #{MemberRole.table_name}" +
433 438 " WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id AND #{Member.table_name}.id = #{MemberRole.table_name}.member_id AND #{role_cond}))"
434 439 end
435 440 end
436 441
437 442 def sql_for_is_private_field(field, operator, value)
438 443 op = (operator == "=" ? 'IN' : 'NOT IN')
439 444 va = value.map {|v| v == '0' ? connection.quoted_false : connection.quoted_true}.uniq.join(',')
440 445
441 446 "#{Issue.table_name}.is_private #{op} (#{va})"
442 447 end
443 448
444 449 def sql_for_relations(field, operator, value, options={})
445 450 relation_options = IssueRelation::TYPES[field]
446 451 return relation_options unless relation_options
447 452
448 453 relation_type = field
449 454 join_column, target_join_column = "issue_from_id", "issue_to_id"
450 455 if relation_options[:reverse] || options[:reverse]
451 456 relation_type = relation_options[:reverse] || relation_type
452 457 join_column, target_join_column = target_join_column, join_column
453 458 end
454 459
455 460 sql = case operator
456 461 when "*", "!*"
457 462 op = (operator == "*" ? 'IN' : 'NOT IN')
458 463 "#{Issue.table_name}.id #{op} (SELECT DISTINCT #{IssueRelation.table_name}.#{join_column} FROM #{IssueRelation.table_name} WHERE #{IssueRelation.table_name}.relation_type = '#{connection.quote_string(relation_type)}')"
459 464 when "=", "!"
460 465 op = (operator == "=" ? 'IN' : 'NOT IN')
461 466 "#{Issue.table_name}.id #{op} (SELECT DISTINCT #{IssueRelation.table_name}.#{join_column} FROM #{IssueRelation.table_name} WHERE #{IssueRelation.table_name}.relation_type = '#{connection.quote_string(relation_type)}' AND #{IssueRelation.table_name}.#{target_join_column} = #{value.first.to_i})"
462 467 when "=p", "=!p", "!p"
463 468 op = (operator == "!p" ? 'NOT IN' : 'IN')
464 469 comp = (operator == "=!p" ? '<>' : '=')
465 470 "#{Issue.table_name}.id #{op} (SELECT DISTINCT #{IssueRelation.table_name}.#{join_column} FROM #{IssueRelation.table_name}, #{Issue.table_name} relissues WHERE #{IssueRelation.table_name}.relation_type = '#{connection.quote_string(relation_type)}' AND #{IssueRelation.table_name}.#{target_join_column} = relissues.id AND relissues.project_id #{comp} #{value.first.to_i})"
466 471 end
467 472
468 473 if relation_options[:sym] == field && !options[:reverse]
469 474 sqls = [sql, sql_for_relations(field, operator, value, :reverse => true)]
470 475 sql = sqls.join(["!", "!*", "!p"].include?(operator) ? " AND " : " OR ")
471 476 end
472 477 "(#{sql})"
473 478 end
474 479
475 480 IssueRelation::TYPES.keys.each do |relation_type|
476 481 alias_method "sql_for_#{relation_type}_field".to_sym, :sql_for_relations
477 482 end
478 483 end
Show file before | Show file after | Hide comments
@@ -1,866 +1,870
1 1 # Redmine - project management software
2 2 # Copyright (C) 2006-2013 Jean-Philippe Lang
3 3 #
4 4 # This program is free software; you can redistribute it and/or
5 5 # modify it under the terms of the GNU General Public License
6 6 # as published by the Free Software Foundation; either version 2
7 7 # of the License, or (at your option) any later version.
8 8 #
9 9 # This program is distributed in the hope that it will be useful,
10 10 # but WITHOUT ANY WARRANTY; without even the implied warranty of
11 11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 12 # GNU General Public License for more details.
13 13 #
14 14 # You should have received a copy of the GNU General Public License
15 15 # along with this program; if not, write to the Free Software
16 16 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
17 17
18 18 class QueryColumn
19 19 attr_accessor :name, :sortable, :groupable, :default_order
20 20 include Redmine::I18n
21 21
22 22 def initialize(name, options={})
23 23 self.name = name
24 24 self.sortable = options[:sortable]
25 25 self.groupable = options[:groupable] || false
26 26 if groupable == true
27 27 self.groupable = name.to_s
28 28 end
29 29 self.default_order = options[:default_order]
30 30 @inline = options.key?(:inline) ? options[:inline] : true
31 31 @caption_key = options[:caption] || "field_#{name}".to_sym
32 32 @frozen = options[:frozen]
33 33 end
34 34
35 35 def caption
36 36 @caption_key.is_a?(Symbol) ? l(@caption_key) : @caption_key
37 37 end
38 38
39 39 # Returns true if the column is sortable, otherwise false
40 40 def sortable?
41 41 !@sortable.nil?
42 42 end
43 43
44 44 def sortable
45 45 @sortable.is_a?(Proc) ? @sortable.call : @sortable
46 46 end
47 47
48 48 def inline?
49 49 @inline
50 50 end
51 51
52 52 def frozen?
53 53 @frozen
54 54 end
55 55
56 56 def value(object)
57 57 object.send name
58 58 end
59 59
60 60 def css_classes
61 61 name
62 62 end
63 63 end
64 64
65 65 class QueryCustomFieldColumn < QueryColumn
66 66
67 67 def initialize(custom_field)
68 68 self.name = "cf_#{custom_field.id}".to_sym
69 69 self.sortable = custom_field.order_statement || false
70 70 self.groupable = custom_field.group_statement || false
71 71 @inline = true
72 72 @cf = custom_field
73 73 end
74 74
75 75 def caption
76 76 @cf.name
77 77 end
78 78
79 79 def custom_field
80 80 @cf
81 81 end
82 82
83 83 def value(object)
84 84 if custom_field.visible_by?(object.project, User.current)
85 85 cv = object.custom_values.select {|v| v.custom_field_id == @cf.id}.collect {|v| @cf.cast_value(v.value)}
86 86 cv.size > 1 ? cv.sort {|a,b| a.to_s <=> b.to_s} : cv.first
87 87 else
88 88 nil
89 89 end
90 90 end
91 91
92 92 def css_classes
93 93 @css_classes ||= "#{name} #{@cf.field_format}"
94 94 end
95 95 end
96 96
97 97 class QueryAssociationCustomFieldColumn < QueryCustomFieldColumn
98 98
99 99 def initialize(association, custom_field)
100 100 super(custom_field)
101 101 self.name = "#{association}.cf_#{custom_field.id}".to_sym
102 102 # TODO: support sorting/grouping by association custom field
103 103 self.sortable = false
104 104 self.groupable = false
105 105 @association = association
106 106 end
107 107
108 108 def value(object)
109 109 if assoc = object.send(@association)
110 110 super(assoc)
111 111 end
112 112 end
113 113
114 114 def css_classes
115 115 @css_classes ||= "#{@association}_cf_#{@cf.id} #{@cf.field_format}"
116 116 end
117 117 end
118 118
119 119 class Query < ActiveRecord::Base
120 120 class StatementInvalid < ::ActiveRecord::StatementInvalid
121 121 end
122 122
123 123 VISIBILITY_PRIVATE = 0
124 124 VISIBILITY_ROLES = 1
125 125 VISIBILITY_PUBLIC = 2
126 126
127 127 belongs_to :project
128 128 belongs_to :user
129 129 has_and_belongs_to_many :roles, :join_table => "#{table_name_prefix}queries_roles#{table_name_suffix}", :foreign_key => "query_id"
130 130 serialize :filters
131 131 serialize :column_names
132 132 serialize :sort_criteria, Array
133 133 serialize :options, Hash
134 134
135 135 attr_protected :project_id, :user_id
136 136
137 137 validates_presence_of :name
138 138 validates_length_of :name, :maximum => 255
139 139 validates :visibility, :inclusion => { :in => [VISIBILITY_PUBLIC, VISIBILITY_ROLES, VISIBILITY_PRIVATE] }
140 140 validate :validate_query_filters
141 141 validate do |query|
142 142 errors.add(:base, l(:label_role_plural) + ' ' + l('activerecord.errors.messages.blank')) if query.visibility == VISIBILITY_ROLES && roles.blank?
143 143 end
144 144
145 145 after_save do |query|
146 146 if query.visibility_changed? && query.visibility != VISIBILITY_ROLES
147 147 query.roles.clear
148 148 end
149 149 end
150 150
151 151 class_attribute :operators
152 152 self.operators = {
153 153 "=" => :label_equals,
154 154 "!" => :label_not_equals,
155 155 "o" => :label_open_issues,
156 156 "c" => :label_closed_issues,
157 157 "!*" => :label_none,
158 158 "*" => :label_any,
159 159 ">=" => :label_greater_or_equal,
160 160 "<=" => :label_less_or_equal,
161 161 "><" => :label_between,
162 162 "<t+" => :label_in_less_than,
163 163 ">t+" => :label_in_more_than,
164 164 "><t+"=> :label_in_the_next_days,
165 165 "t+" => :label_in,
166 166 "t" => :label_today,
167 167 "ld" => :label_yesterday,
168 168 "w" => :label_this_week,
169 169 "lw" => :label_last_week,
170 170 "l2w" => [:label_last_n_weeks, {:count => 2}],
171 171 "m" => :label_this_month,
172 172 "lm" => :label_last_month,
173 173 "y" => :label_this_year,
174 174 ">t-" => :label_less_than_ago,
175 175 "<t-" => :label_more_than_ago,
176 176 "><t-"=> :label_in_the_past_days,
177 177 "t-" => :label_ago,
178 178 "~" => :label_contains,
179 179 "!~" => :label_not_contains,
180 180 "=p" => :label_any_issues_in_project,
181 181 "=!p" => :label_any_issues_not_in_project,
182 182 "!p" => :label_no_issues_in_project
183 183 }
184 184
185 185 class_attribute :operators_by_filter_type
186 186 self.operators_by_filter_type = {
187 187 :list => [ "=", "!" ],
188 188 :list_status => [ "o", "=", "!", "c", "*" ],
189 189 :list_optional => [ "=", "!", "!*", "*" ],
190 190 :list_subprojects => [ "*", "!*", "=" ],
191 191 :date => [ "=", ">=", "<=", "><", "<t+", ">t+", "><t+", "t+", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", ">t-", "<t-", "><t-", "t-", "!*", "*" ],
192 192 :date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "><t-", "t-", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", "!*", "*" ],
193 193 :string => [ "=", "~", "!", "!~", "!*", "*" ],
194 194 :text => [ "~", "!~", "!*", "*" ],
195 195 :integer => [ "=", ">=", "<=", "><", "!*", "*" ],
196 196 :float => [ "=", ">=", "<=", "><", "!*", "*" ],
197 197 :relation => ["=", "=p", "=!p", "!p", "!*", "*"]
198 198 }
199 199
200 200 class_attribute :available_columns
201 201 self.available_columns = []
202 202
203 203 class_attribute :queried_class
204 204
205 205 def queried_table_name
206 206 @queried_table_name ||= self.class.queried_class.table_name
207 207 end
208 208
209 209 def initialize(attributes=nil, *args)
210 210 super attributes
211 211 @is_for_all = project.nil?
212 212 end
213 213
214 214 # Builds the query from the given params
215 215 def build_from_params(params)
216 216 if params[:fields] || params[:f]
217 217 self.filters = {}
218 218 add_filters(params[:fields] || params[:f], params[:operators] || params[:op], params[:values] || params[:v])
219 219 else
220 220 available_filters.keys.each do |field|
221 221 add_short_filter(field, params[field]) if params[field]
222 222 end
223 223 end
224 224 self.group_by = params[:group_by] || (params[:query] && params[:query][:group_by])
225 225 self.column_names = params[:c] || (params[:query] && params[:query][:column_names])
226 226 self
227 227 end
228 228
229 229 # Builds a new query from the given params and attributes
230 230 def self.build_from_params(params, attributes={})
231 231 new(attributes).build_from_params(params)
232 232 end
233 233
234 234 def validate_query_filters
235 235 filters.each_key do |field|
236 236 if values_for(field)
237 237 case type_for(field)
238 238 when :integer
239 239 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^[+-]?\d+$/) }
240 240 when :float
241 241 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^[+-]?\d+(\.\d*)?$/) }
242 242 when :date, :date_past
243 243 case operator_for(field)
244 244 when "=", ">=", "<=", "><"
245 245 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && (!v.match(/^\d{4}-\d{2}-\d{2}$/) || (Date.parse(v) rescue nil).nil?) }
246 246 when ">t-", "<t-", "t-", ">t+", "<t+", "t+", "><t+", "><t-"
247 247 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^\d+$/) }
248 248 end
249 249 end
250 250 end
251 251
252 252 add_filter_error(field, :blank) unless
253 253 # filter requires one or more values
254 254 (values_for(field) and !values_for(field).first.blank?) or
255 255 # filter doesn't require any value
256 256 ["o", "c", "!*", "*", "t", "ld", "w", "lw", "l2w", "m", "lm", "y"].include? operator_for(field)
257 257 end if filters
258 258 end
259 259
260 260 def add_filter_error(field, message)
261 261 m = label_for(field) + " " + l(message, :scope => 'activerecord.errors.messages')
262 262 errors.add(:base, m)
263 263 end
264 264
265 265 def editable_by?(user)
266 266 return false unless user
267 267 # Admin can edit them all and regular users can edit their private queries
268 268 return true if user.admin? || (is_private? && self.user_id == user.id)
269 269 # Members can not edit public queries that are for all project (only admin is allowed to)
270 270 is_public? && !@is_for_all && user.allowed_to?(:manage_public_queries, project)
271 271 end
272 272
273 273 def trackers
274 274 @trackers ||= project.nil? ? Tracker.sorted.all : project.rolled_up_trackers
275 275 end
276 276
277 277 # Returns a hash of localized labels for all filter operators
278 278 def self.operators_labels
279 279 operators.inject({}) {|h, operator| h[operator.first] = l(*operator.last); h}
280 280 end
281 281
282 282 # Returns a representation of the available filters for JSON serialization
283 283 def available_filters_as_json
284 284 json = {}
285 285 available_filters.each do |field, options|
286 286 json[field] = options.slice(:type, :name, :values).stringify_keys
287 287 end
288 288 json
289 289 end
290 290
291 291 def all_projects
292 292 @all_projects ||= Project.visible.all
293 293 end
294 294
295 295 def all_projects_values
296 296 return @all_projects_values if @all_projects_values
297 297
298 298 values = []
299 299 Project.project_tree(all_projects) do |p, level|
300 300 prefix = (level > 0 ? ('--' * level + ' ') : '')
301 301 values << ["#{prefix}#{p.name}", p.id.to_s]
302 302 end
303 303 @all_projects_values = values
304 304 end
305 305
306 306 # Adds available filters
307 307 def initialize_available_filters
308 308 # implemented by sub-classes
309 309 end
310 310 protected :initialize_available_filters
311 311
312 312 # Adds an available filter
313 313 def add_available_filter(field, options)
314 314 @available_filters ||= ActiveSupport::OrderedHash.new
315 315 @available_filters[field] = options
316 316 @available_filters
317 317 end
318 318
319 319 # Removes an available filter
320 320 def delete_available_filter(field)
321 321 if @available_filters
322 322 @available_filters.delete(field)
323 323 end
324 324 end
325 325
326 326 # Return a hash of available filters
327 327 def available_filters
328 328 unless @available_filters
329 329 initialize_available_filters
330 330 @available_filters.each do |field, options|
331 331 options[:name] ||= l(options[:label] || "field_#{field}".gsub(/_id$/, ''))
332 332 end
333 333 end
334 334 @available_filters
335 335 end
336 336
337 337 def add_filter(field, operator, values=nil)
338 338 # values must be an array
339 339 return unless values.nil? || values.is_a?(Array)
340 340 # check if field is defined as an available filter
341 341 if available_filters.has_key? field
342 342 filter_options = available_filters[field]
343 343 filters[field] = {:operator => operator, :values => (values || [''])}
344 344 end
345 345 end
346 346
347 347 def add_short_filter(field, expression)
348 348 return unless expression && available_filters.has_key?(field)
349 349 field_type = available_filters[field][:type]
350 350 operators_by_filter_type[field_type].sort.reverse.detect do |operator|
351 351 next unless expression =~ /^#{Regexp.escape(operator)}(.*)$/
352 352 values = $1
353 353 add_filter field, operator, values.present? ? values.split('|') : ['']
354 354 end || add_filter(field, '=', expression.split('|'))
355 355 end
356 356
357 357 # Add multiple filters using +add_filter+
358 358 def add_filters(fields, operators, values)
359 359 if fields.is_a?(Array) && operators.is_a?(Hash) && (values.nil? || values.is_a?(Hash))
360 360 fields.each do |field|
361 361 add_filter(field, operators[field], values && values[field])
362 362 end
363 363 end
364 364 end
365 365
366 366 def has_filter?(field)
367 367 filters and filters[field]
368 368 end
369 369
370 370 def type_for(field)
371 371 available_filters[field][:type] if available_filters.has_key?(field)
372 372 end
373 373
374 374 def operator_for(field)
375 375 has_filter?(field) ? filters[field][:operator] : nil
376 376 end
377 377
378 378 def values_for(field)
379 379 has_filter?(field) ? filters[field][:values] : nil
380 380 end
381 381
382 382 def value_for(field, index=0)
383 383 (values_for(field) || [])[index]
384 384 end
385 385
386 386 def label_for(field)
387 387 label = available_filters[field][:name] if available_filters.has_key?(field)
388 388 label ||= l("field_#{field.to_s.gsub(/_id$/, '')}", :default => field)
389 389 end
390 390
391 391 def self.add_available_column(column)
392 392 self.available_columns << (column) if column.is_a?(QueryColumn)
393 393 end
394 394
395 395 # Returns an array of columns that can be used to group the results
396 396 def groupable_columns
397 397 available_columns.select {|c| c.groupable}
398 398 end
399 399
400 400 # Returns a Hash of columns and the key for sorting
401 401 def sortable_columns
402 402 available_columns.inject({}) {|h, column|
403 403 h[column.name.to_s] = column.sortable
404 404 h
405 405 }
406 406 end
407 407
408 408 def columns
409 409 # preserve the column_names order
410 410 cols = (has_default_columns? ? default_columns_names : column_names).collect do |name|
411 411 available_columns.find { |col| col.name == name }
412 412 end.compact
413 413 available_columns.select(&:frozen?) | cols
414 414 end
415 415
416 416 def inline_columns
417 417 columns.select(&:inline?)
418 418 end
419 419
420 420 def block_columns
421 421 columns.reject(&:inline?)
422 422 end
423 423
424 424 def available_inline_columns
425 425 available_columns.select(&:inline?)
426 426 end
427 427
428 428 def available_block_columns
429 429 available_columns.reject(&:inline?)
430 430 end
431 431
432 432 def default_columns_names
433 433 []
434 434 end
435 435
436 436 def column_names=(names)
437 437 if names
438 438 names = names.select {|n| n.is_a?(Symbol) || !n.blank? }
439 439 names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }
440 440 # Set column_names to nil if default columns
441 441 if names == default_columns_names
442 442 names = nil
443 443 end
444 444 end
445 445 write_attribute(:column_names, names)
446 446 end
447 447
448 448 def has_column?(column)
449 449 column_names && column_names.include?(column.is_a?(QueryColumn) ? column.name : column)
450 450 end
451 451
452 def has_custom_field_column?
453 columns.any? {|column| column.is_a? QueryCustomFieldColumn}
454 end
455
452 456 def has_default_columns?
453 457 column_names.nil? || column_names.empty?
454 458 end
455 459
456 460 def sort_criteria=(arg)
457 461 c = []
458 462 if arg.is_a?(Hash)
459 463 arg = arg.keys.sort.collect {|k| arg[k]}
460 464 end
461 465 c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, (o == 'desc' || o == false) ? 'desc' : 'asc']}
462 466 write_attribute(:sort_criteria, c)
463 467 end
464 468
465 469 def sort_criteria
466 470 read_attribute(:sort_criteria) || []
467 471 end
468 472
469 473 def sort_criteria_key(arg)
470 474 sort_criteria && sort_criteria[arg] && sort_criteria[arg].first
471 475 end
472 476
473 477 def sort_criteria_order(arg)
474 478 sort_criteria && sort_criteria[arg] && sort_criteria[arg].last
475 479 end
476 480
477 481 def sort_criteria_order_for(key)
478 482 sort_criteria.detect {|k, order| key.to_s == k}.try(:last)
479 483 end
480 484
481 485 # Returns the SQL sort order that should be prepended for grouping
482 486 def group_by_sort_order
483 487 if grouped? && (column = group_by_column)
484 488 order = sort_criteria_order_for(column.name) || column.default_order
485 489 column.sortable.is_a?(Array) ?
486 490 column.sortable.collect {|s| "#{s} #{order}"}.join(',') :
487 491 "#{column.sortable} #{order}"
488 492 end
489 493 end
490 494
491 495 # Returns true if the query is a grouped query
492 496 def grouped?
493 497 !group_by_column.nil?
494 498 end
495 499
496 500 def group_by_column
497 501 groupable_columns.detect {|c| c.groupable && c.name.to_s == group_by}
498 502 end
499 503
500 504 def group_by_statement
501 505 group_by_column.try(:groupable)
502 506 end
503 507
504 508 def project_statement
505 509 project_clauses = []
506 510 if project && !project.descendants.active.empty?
507 511 ids = [project.id]
508 512 if has_filter?("subproject_id")
509 513 case operator_for("subproject_id")
510 514 when '='
511 515 # include the selected subprojects
512 516 ids += values_for("subproject_id").each(&:to_i)
513 517 when '!*'
514 518 # main project only
515 519 else
516 520 # all subprojects
517 521 ids += project.descendants.collect(&:id)
518 522 end
519 523 elsif Setting.display_subprojects_issues?
520 524 ids += project.descendants.collect(&:id)
521 525 end
522 526 project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
523 527 elsif project
524 528 project_clauses << "#{Project.table_name}.id = %d" % project.id
525 529 end
526 530 project_clauses.any? ? project_clauses.join(' AND ') : nil
527 531 end
528 532
529 533 def statement
530 534 # filters clauses
531 535 filters_clauses = []
532 536 filters.each_key do |field|
533 537 next if field == "subproject_id"
534 538 v = values_for(field).clone
535 539 next unless v and !v.empty?
536 540 operator = operator_for(field)
537 541
538 542 # "me" value subsitution
539 543 if %w(assigned_to_id author_id user_id watcher_id).include?(field)
540 544 if v.delete("me")
541 545 if User.current.logged?
542 546 v.push(User.current.id.to_s)
543 547 v += User.current.group_ids.map(&:to_s) if field == 'assigned_to_id'
544 548 else
545 549 v.push("0")
546 550 end
547 551 end
548 552 end
549 553
550 554 if field == 'project_id'
551 555 if v.delete('mine')
552 556 v += User.current.memberships.map(&:project_id).map(&:to_s)
553 557 end
554 558 end
555 559
556 560 if field =~ /cf_(\d+)$/
557 561 # custom field
558 562 filters_clauses << sql_for_custom_field(field, operator, v, $1)
559 563 elsif respond_to?("sql_for_#{field}_field")
560 564 # specific statement
561 565 filters_clauses << send("sql_for_#{field}_field", field, operator, v)
562 566 else
563 567 # regular field
564 568 filters_clauses << '(' + sql_for_field(field, operator, v, queried_table_name, field) + ')'
565 569 end
566 570 end if filters and valid?
567 571
568 572 if (c = group_by_column) && c.is_a?(QueryCustomFieldColumn)
569 573 # Excludes results for which the grouped custom field is not visible
570 574 filters_clauses << c.custom_field.visibility_by_project_condition
571 575 end
572 576
573 577 filters_clauses << project_statement
574 578 filters_clauses.reject!(&:blank?)
575 579
576 580 filters_clauses.any? ? filters_clauses.join(' AND ') : nil
577 581 end
578 582
579 583 private
580 584
581 585 def sql_for_custom_field(field, operator, value, custom_field_id)
582 586 db_table = CustomValue.table_name
583 587 db_field = 'value'
584 588 filter = @available_filters[field]
585 589 return nil unless filter
586 590 if filter[:format] == 'user'
587 591 if value.delete('me')
588 592 value.push User.current.id.to_s
589 593 end
590 594 end
591 595 not_in = nil
592 596 if operator == '!'
593 597 # Makes ! operator work for custom fields with multiple values
594 598 operator = '='
595 599 not_in = 'NOT'
596 600 end
597 601 customized_key = "id"
598 602 customized_class = queried_class
599 603 if field =~ /^(.+)\.cf_/
600 604 assoc = $1
601 605 customized_key = "#{assoc}_id"
602 606 customized_class = queried_class.reflect_on_association(assoc.to_sym).klass.base_class rescue nil
603 607 raise "Unknown #{queried_class.name} association #{assoc}" unless customized_class
604 608 end
605 609 where = sql_for_field(field, operator, value, db_table, db_field, true)
606 610 if operator =~ /[<>]/
607 611 where = "(#{where}) AND #{db_table}.#{db_field} <> ''"
608 612 end
609 613 "#{queried_table_name}.#{customized_key} #{not_in} IN (" +
610 614 "SELECT #{customized_class.table_name}.id FROM #{customized_class.table_name}" +
611 615 " 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}" +
612 616 " WHERE (#{where}) AND (#{filter[:field].visibility_by_project_condition}))"
613 617 end
614 618
615 619 # Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+
616 620 def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)
617 621 sql = ''
618 622 case operator
619 623 when "="
620 624 if value.any?
621 625 case type_for(field)
622 626 when :date, :date_past
623 627 sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), (Date.parse(value.first) rescue nil))
624 628 when :integer
625 629 if is_custom_filter
626 630 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_i})"
627 631 else
628 632 sql = "#{db_table}.#{db_field} = #{value.first.to_i}"
629 633 end
630 634 when :float
631 635 if is_custom_filter
632 636 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})"
633 637 else
634 638 sql = "#{db_table}.#{db_field} BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5}"
635 639 end
636 640 else
637 641 sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")"
638 642 end
639 643 else
640 644 # IN an empty set
641 645 sql = "1=0"
642 646 end
643 647 when "!"
644 648 if value.any?
645 649 sql = "(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + "))"
646 650 else
647 651 # NOT IN an empty set
648 652 sql = "1=1"
649 653 end
650 654 when "!*"
651 655 sql = "#{db_table}.#{db_field} IS NULL"
652 656 sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter
653 657 when "*"
654 658 sql = "#{db_table}.#{db_field} IS NOT NULL"
655 659 sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
656 660 when ">="
657 661 if [:date, :date_past].include?(type_for(field))
658 662 sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), nil)
659 663 else
660 664 if is_custom_filter
661 665 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})"
662 666 else
663 667 sql = "#{db_table}.#{db_field} >= #{value.first.to_f}"
664 668 end
665 669 end
666 670 when "<="
667 671 if [:date, :date_past].include?(type_for(field))
668 672 sql = date_clause(db_table, db_field, nil, (Date.parse(value.first) rescue nil))
669 673 else
670 674 if is_custom_filter
671 675 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})"
672 676 else
673 677 sql = "#{db_table}.#{db_field} <= #{value.first.to_f}"
674 678 end
675 679 end
676 680 when "><"
677 681 if [:date, :date_past].include?(type_for(field))
678 682 sql = date_clause(db_table, db_field, (Date.parse(value[0]) rescue nil), (Date.parse(value[1]) rescue nil))
679 683 else
680 684 if is_custom_filter
681 685 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})"
682 686 else
683 687 sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
684 688 end
685 689 end
686 690 when "o"
687 691 sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{connection.quoted_false})" if field == "status_id"
688 692 when "c"
689 693 sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{connection.quoted_true})" if field == "status_id"
690 694 when "><t-"
691 695 # between today - n days and today
692 696 sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0)
693 697 when ">t-"
694 698 # >= today - n days
695 699 sql = relative_date_clause(db_table, db_field, - value.first.to_i, nil)
696 700 when "<t-"
697 701 # <= today - n days
698 702 sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i)
699 703 when "t-"
700 704 # = n days in past
701 705 sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i)
702 706 when "><t+"
703 707 # between today and today + n days
704 708 sql = relative_date_clause(db_table, db_field, 0, value.first.to_i)
705 709 when ">t+"
706 710 # >= today + n days
707 711 sql = relative_date_clause(db_table, db_field, value.first.to_i, nil)
708 712 when "<t+"
709 713 # <= today + n days
710 714 sql = relative_date_clause(db_table, db_field, nil, value.first.to_i)
711 715 when "t+"
712 716 # = today + n days
713 717 sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i)
714 718 when "t"
715 719 # = today
716 720 sql = relative_date_clause(db_table, db_field, 0, 0)
717 721 when "ld"
718 722 # = yesterday
719 723 sql = relative_date_clause(db_table, db_field, -1, -1)
720 724 when "w"
721 725 # = this week
722 726 first_day_of_week = l(:general_first_day_of_week).to_i
723 727 day_of_week = Date.today.cwday
724 728 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
725 729 sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6)
726 730 when "lw"
727 731 # = last week
728 732 first_day_of_week = l(:general_first_day_of_week).to_i
729 733 day_of_week = Date.today.cwday
730 734 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
731 735 sql = relative_date_clause(db_table, db_field, - days_ago - 7, - days_ago - 1)
732 736 when "l2w"
733 737 # = last 2 weeks
734 738 first_day_of_week = l(:general_first_day_of_week).to_i
735 739 day_of_week = Date.today.cwday
736 740 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
737 741 sql = relative_date_clause(db_table, db_field, - days_ago - 14, - days_ago - 1)
738 742 when "m"
739 743 # = this month
740 744 date = Date.today
741 745 sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month)
742 746 when "lm"
743 747 # = last month
744 748 date = Date.today.prev_month
745 749 sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month)
746 750 when "y"
747 751 # = this year
748 752 date = Date.today
749 753 sql = date_clause(db_table, db_field, date.beginning_of_year, date.end_of_year)
750 754 when "~"
751 755 sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
752 756 when "!~"
753 757 sql = "LOWER(#{db_table}.#{db_field}) NOT LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
754 758 else
755 759 raise "Unknown query operator #{operator}"
756 760 end
757 761
758 762 return sql
759 763 end
760 764
761 765 # Adds a filter for the given custom field
762 766 def add_custom_field_filter(field, assoc=nil)
763 767 case field.field_format
764 768 when "text"
765 769 options = { :type => :text }
766 770 when "list"
767 771 options = { :type => :list_optional, :values => field.possible_values }
768 772 when "date"
769 773 options = { :type => :date }
770 774 when "bool"
771 775 options = { :type => :list, :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]] }
772 776 when "int"
773 777 options = { :type => :integer }
774 778 when "float"
775 779 options = { :type => :float }
776 780 when "user", "version"
777 781 return unless project
778 782 values = field.possible_values_options(project)
779 783 if User.current.logged? && field.field_format == 'user'
780 784 values.unshift ["<< #{l(:label_me)} >>", "me"]
781 785 end
782 786 options = { :type => :list_optional, :values => values }
783 787 else
784 788 options = { :type => :string }
785 789 end
786 790 filter_id = "cf_#{field.id}"
787 791 filter_name = field.name
788 792 if assoc.present?
789 793 filter_id = "#{assoc}.#{filter_id}"
790 794 filter_name = l("label_attribute_of_#{assoc}", :name => filter_name)
791 795 end
792 796 add_available_filter filter_id, options.merge({
793 797 :name => filter_name,
794 798 :format => field.field_format,
795 799 :field => field
796 800 })
797 801 end
798 802
799 803 # Adds filters for the given custom fields scope
800 804 def add_custom_fields_filters(scope, assoc=nil)
801 805 scope.visible.where(:is_filter => true).sorted.each do |field|
802 806 add_custom_field_filter(field, assoc)
803 807 end
804 808 end
805 809
806 810 # Adds filters for the given associations custom fields
807 811 def add_associations_custom_fields_filters(*associations)
808 812 fields_by_class = CustomField.visible.where(:is_filter => true).group_by(&:class)
809 813 associations.each do |assoc|
810 814 association_klass = queried_class.reflect_on_association(assoc).klass
811 815 fields_by_class.each do |field_class, fields|
812 816 if field_class.customized_class <= association_klass
813 817 fields.sort.each do |field|
814 818 add_custom_field_filter(field, assoc)
815 819 end
816 820 end
817 821 end
818 822 end
819 823 end
820 824
821 825 # Returns a SQL clause for a date or datetime field.
822 826 def date_clause(table, field, from, to)
823 827 s = []
824 828 if from
825 829 from_yesterday = from - 1
826 830 from_yesterday_time = Time.local(from_yesterday.year, from_yesterday.month, from_yesterday.day)
827 831 if self.class.default_timezone == :utc
828 832 from_yesterday_time = from_yesterday_time.utc
829 833 end
830 834 s << ("#{table}.#{field} > '%s'" % [connection.quoted_date(from_yesterday_time.end_of_day)])
831 835 end
832 836 if to
833 837 to_time = Time.local(to.year, to.month, to.day)
834 838 if self.class.default_timezone == :utc
835 839 to_time = to_time.utc
836 840 end
837 841 s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date(to_time.end_of_day)])
838 842 end
839 843 s.join(' AND ')
840 844 end
841 845
842 846 # Returns a SQL clause for a date or datetime field using relative dates.
843 847 def relative_date_clause(table, field, days_from, days_to)
844 848 date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil))
845 849 end
846 850
847 851 # Additional joins required for the given sort options
848 852 def joins_for_order_statement(order_options)
849 853 joins = []
850 854
851 855 if order_options
852 856 if order_options.include?('authors')
853 857 joins << "LEFT OUTER JOIN #{User.table_name} authors ON authors.id = #{queried_table_name}.author_id"
854 858 end
855 859 order_options.scan(/cf_\d+/).uniq.each do |name|
856 860 column = available_columns.detect {|c| c.name.to_s == name}
857 861 join = column && column.custom_field.join_for_order_statement
858 862 if join
859 863 joins << join
860 864 end
861 865 end
862 866 end
863 867
864 868 joins.any? ? joins.join(' ') : nil
865 869 end
866 870 end
General Comments 0
You need to be logged in to leave comments. Login now