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