##// END OF EJS Templates
jro_apps » redmine
RSS
Typo....
Jean-Philippe Lang -
r6138:c6da45162128
parent child
Show More
Side by Side Unified
Context file:
r6138:c6da45162128 Loading diff...:
Show file before | Show file after | Hide comments
@@ -1,753 +1,753
1 1 # Redmine - project management software
2 2 # Copyright (C) 2006-2011 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 @caption_key = options[:caption] || "field_#{name}"
31 31 end
32 32
33 33 def caption
34 34 l(@caption_key)
35 35 end
36 36
37 37 # Returns true if the column is sortable, otherwise false
38 38 def sortable?
39 39 !sortable.nil?
40 40 end
41 41
42 42 def value(issue)
43 43 issue.send name
44 44 end
45 45
46 46 def css_classes
47 47 name
48 48 end
49 49 end
50 50
51 51 class QueryCustomFieldColumn < QueryColumn
52 52
53 53 def initialize(custom_field)
54 54 self.name = "cf_#{custom_field.id}".to_sym
55 55 self.sortable = custom_field.order_statement || false
56 56 if %w(list date bool int).include?(custom_field.field_format)
57 57 self.groupable = custom_field.order_statement
58 58 end
59 59 self.groupable ||= false
60 60 @cf = custom_field
61 61 end
62 62
63 63 def caption
64 64 @cf.name
65 65 end
66 66
67 67 def custom_field
68 68 @cf
69 69 end
70 70
71 71 def value(issue)
72 72 cv = issue.custom_values.detect {|v| v.custom_field_id == @cf.id}
73 73 cv && @cf.cast_value(cv.value)
74 74 end
75 75
76 76 def css_classes
77 77 @css_classes ||= "#{name} #{@cf.field_format}"
78 78 end
79 79 end
80 80
81 81 class Query < ActiveRecord::Base
82 82 class StatementInvalid < ::ActiveRecord::StatementInvalid
83 83 end
84 84
85 85 belongs_to :project
86 86 belongs_to :user
87 87 serialize :filters
88 88 serialize :column_names
89 89 serialize :sort_criteria, Array
90 90
91 91 attr_protected :project_id, :user_id
92 92
93 93 validates_presence_of :name, :on => :save
94 94 validates_length_of :name, :maximum => 255
95 95
96 96 @@operators = { "=" => :label_equals,
97 97 "!" => :label_not_equals,
98 98 "o" => :label_open_issues,
99 99 "c" => :label_closed_issues,
100 100 "!*" => :label_none,
101 101 "*" => :label_all,
102 102 ">=" => :label_greater_or_equal,
103 103 "<=" => :label_less_or_equal,
104 104 "><" => :label_between,
105 105 "<t+" => :label_in_less_than,
106 106 ">t+" => :label_in_more_than,
107 107 "t+" => :label_in,
108 108 "t" => :label_today,
109 109 "w" => :label_this_week,
110 110 ">t-" => :label_less_than_ago,
111 111 "<t-" => :label_more_than_ago,
112 112 "t-" => :label_ago,
113 113 "~" => :label_contains,
114 114 "!~" => :label_not_contains }
115 115
116 116 cattr_reader :operators
117 117
118 118 @@operators_by_filter_type = { :list => [ "=", "!" ],
119 119 :list_status => [ "o", "=", "!", "c", "*" ],
120 120 :list_optional => [ "=", "!", "!*", "*" ],
121 121 :list_subprojects => [ "*", "!*", "=" ],
122 122 :date => [ "=", ">=", "<=", "><", "<t+", ">t+", "t+", "t", "w", ">t-", "<t-", "t-" ],
123 123 :date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "t-", "t", "w" ],
124 124 :string => [ "=", "~", "!", "!~" ],
125 125 :text => [ "~", "!~" ],
126 126 :integer => [ "=", ">=", "<=", "><", "!*", "*" ],
127 127 :float => [ "=", ">=", "<=", "><", "!*", "*" ] }
128 128
129 129 cattr_reader :operators_by_filter_type
130 130
131 131 @@available_columns = [
132 132 QueryColumn.new(:project, :sortable => "#{Project.table_name}.name", :groupable => true),
133 133 QueryColumn.new(:tracker, :sortable => "#{Tracker.table_name}.position", :groupable => true),
134 134 QueryColumn.new(:parent, :sortable => ["#{Issue.table_name}.root_id", "#{Issue.table_name}.lft ASC"], :default_order => 'desc', :caption => :field_parent_issue),
135 135 QueryColumn.new(:status, :sortable => "#{IssueStatus.table_name}.position", :groupable => true),
136 136 QueryColumn.new(:priority, :sortable => "#{IssuePriority.table_name}.position", :default_order => 'desc', :groupable => true),
137 137 QueryColumn.new(:subject, :sortable => "#{Issue.table_name}.subject"),
138 138 QueryColumn.new(:author),
139 139 QueryColumn.new(:assigned_to, :sortable => ["#{User.table_name}.lastname", "#{User.table_name}.firstname", "#{User.table_name}.id"], :groupable => true),
140 140 QueryColumn.new(:updated_on, :sortable => "#{Issue.table_name}.updated_on", :default_order => 'desc'),
141 141 QueryColumn.new(:category, :sortable => "#{IssueCategory.table_name}.name", :groupable => true),
142 142 QueryColumn.new(:fixed_version, :sortable => ["#{Version.table_name}.effective_date", "#{Version.table_name}.name"], :default_order => 'desc', :groupable => true),
143 143 QueryColumn.new(:start_date, :sortable => "#{Issue.table_name}.start_date"),
144 144 QueryColumn.new(:due_date, :sortable => "#{Issue.table_name}.due_date"),
145 145 QueryColumn.new(:estimated_hours, :sortable => "#{Issue.table_name}.estimated_hours"),
146 146 QueryColumn.new(:done_ratio, :sortable => "#{Issue.table_name}.done_ratio", :groupable => true),
147 147 QueryColumn.new(:created_on, :sortable => "#{Issue.table_name}.created_on", :default_order => 'desc'),
148 148 ]
149 149 cattr_reader :available_columns
150 150
151 151 named_scope :visible, lambda {|*args|
152 152 user = args.shift || User.current
153 153 base = Project.allowed_to_condition(user, :view_issues, *args)
154 154 user_id = user.logged? ? user.id : 0
155 155 {
156 156 :conditions => ["(#{table_name}.project_id IS NULL OR (#{base})) AND (#{table_name}.is_public = ? OR #{table_name}.user_id = ?)", true, user_id],
157 157 :include => :project
158 158 }
159 159 }
160 160
161 161 def initialize(attributes = nil)
162 162 super attributes
163 163 self.filters ||= { 'status_id' => {:operator => "o", :values => [""]} }
164 164 end
165 165
166 166 def after_initialize
167 167 # Store the fact that project is nil (used in #editable_by?)
168 168 @is_for_all = project.nil?
169 169 end
170 170
171 171 def validate
172 172 filters.each_key do |field|
173 173 if values_for(field)
174 174 case type_for(field)
175 175 when :integer
176 176 errors.add(label_for(field), :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^\d+$/) }
177 177 when :float
178 178 errors.add(label_for(field), :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^\d+(\.\d*)?$/) }
179 179 end
180 180 end
181 181
182 182 errors.add label_for(field), :blank unless
183 183 # filter requires one or more values
184 184 (values_for(field) and !values_for(field).first.blank?) or
185 185 # filter doesn't require any value
186 186 ["o", "c", "!*", "*", "t", "w"].include? operator_for(field)
187 187 end if filters
188 188 end
189 189
190 190 # Returns true if the query is visible to +user+ or the current user.
191 191 def visible?(user=User.current)
192 192 (project.nil? || user.allowed_to?(:view_issues, project)) && (self.is_public? || self.user_id == user.id)
193 193 end
194 194
195 195 def editable_by?(user)
196 196 return false unless user
197 197 # Admin can edit them all and regular users can edit their private queries
198 198 return true if user.admin? || (!is_public && self.user_id == user.id)
199 199 # Members can not edit public queries that are for all project (only admin is allowed to)
200 200 is_public && !@is_for_all && user.allowed_to?(:manage_public_queries, project)
201 201 end
202 202
203 203 def available_filters
204 204 return @available_filters if @available_filters
205 205
206 206 trackers = project.nil? ? Tracker.find(:all, :order => 'position') : project.rolled_up_trackers
207 207
208 208 @available_filters = { "status_id" => { :type => :list_status, :order => 1, :values => IssueStatus.find(:all, :order => 'position').collect{|s| [s.name, s.id.to_s] } },
209 209 "tracker_id" => { :type => :list, :order => 2, :values => trackers.collect{|s| [s.name, s.id.to_s] } },
210 210 "priority_id" => { :type => :list, :order => 3, :values => IssuePriority.all.collect{|s| [s.name, s.id.to_s] } },
211 211 "subject" => { :type => :text, :order => 8 },
212 212 "created_on" => { :type => :date_past, :order => 9 },
213 213 "updated_on" => { :type => :date_past, :order => 10 },
214 214 "start_date" => { :type => :date, :order => 11 },
215 215 "due_date" => { :type => :date, :order => 12 },
216 216 "estimated_hours" => { :type => :float, :order => 13 },
217 217 "done_ratio" => { :type => :integer, :order => 14 }}
218 218
219 219 user_values = []
220 220 user_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
221 221 if project
222 222 user_values += project.users.sort.collect{|s| [s.name, s.id.to_s] }
223 223 else
224 224 all_projects = Project.visible.all
225 225 if all_projects.any?
226 226 # members of visible projects
227 227 user_values += User.active.find(:all, :conditions => ["#{User.table_name}.id IN (SELECT DISTINCT user_id FROM members WHERE project_id IN (?))", all_projects.collect(&:id)]).sort.collect{|s| [s.name, s.id.to_s] }
228 228
229 229 # project filter
230 230 project_values = []
231 231 Project.project_tree(all_projects) do |p, level|
232 232 prefix = (level > 0 ? ('--' * level + ' ') : '')
233 233 project_values << ["#{prefix}#{p.name}", p.id.to_s]
234 234 end
235 235 @available_filters["project_id"] = { :type => :list, :order => 1, :values => project_values} unless project_values.empty?
236 236 end
237 237 end
238 238 @available_filters["assigned_to_id"] = { :type => :list_optional, :order => 4, :values => user_values } unless user_values.empty?
239 239 @available_filters["author_id"] = { :type => :list, :order => 5, :values => user_values } unless user_values.empty?
240 240
241 241 group_values = Group.all.collect {|g| [g.name, g.id.to_s] }
242 242 @available_filters["member_of_group"] = { :type => :list_optional, :order => 6, :values => group_values } unless group_values.empty?
243 243
244 244 role_values = Role.givable.collect {|r| [r.name, r.id.to_s] }
245 245 @available_filters["assigned_to_role"] = { :type => :list_optional, :order => 7, :values => role_values } unless role_values.empty?
246 246
247 247 if User.current.logged?
248 248 @available_filters["watcher_id"] = { :type => :list, :order => 15, :values => [["<< #{l(:label_me)} >>", "me"]] }
249 249 end
250 250
251 251 if project
252 252 # project specific filters
253 253 categories = @project.issue_categories.all
254 254 unless categories.empty?
255 255 @available_filters["category_id"] = { :type => :list_optional, :order => 6, :values => categories.collect{|s| [s.name, s.id.to_s] } }
256 256 end
257 257 versions = @project.shared_versions.all
258 258 unless versions.empty?
259 259 @available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }
260 260 end
261 261 unless @project.leaf?
262 262 subprojects = @project.descendants.visible.all
263 263 unless subprojects.empty?
264 264 @available_filters["subproject_id"] = { :type => :list_subprojects, :order => 13, :values => subprojects.collect{|s| [s.name, s.id.to_s] } }
265 265 end
266 266 end
267 267 add_custom_fields_filters(@project.all_issue_custom_fields)
268 268 else
269 269 # global filters for cross project issue list
270 270 system_shared_versions = Version.visible.find_all_by_sharing('system')
271 271 unless system_shared_versions.empty?
272 272 @available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => system_shared_versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }
273 273 end
274 274 add_custom_fields_filters(IssueCustomField.find(:all, :conditions => {:is_filter => true, :is_for_all => true}))
275 275 end
276 276 @available_filters
277 277 end
278 278
279 279 def add_filter(field, operator, values)
280 280 # values must be an array
281 281 return unless values.nil? || values.is_a?(Array)
282 282 # check if field is defined as an available filter
283 283 if available_filters.has_key? field
284 284 filter_options = available_filters[field]
285 285 # check if operator is allowed for that filter
286 286 #if @@operators_by_filter_type[filter_options[:type]].include? operator
287 287 # allowed_values = values & ([""] + (filter_options[:values] || []).collect {|val| val[1]})
288 288 # filters[field] = {:operator => operator, :values => allowed_values } if (allowed_values.first and !allowed_values.first.empty?) or ["o", "c", "!*", "*", "t"].include? operator
289 289 #end
290 290 filters[field] = {:operator => operator, :values => (values || [''])}
291 291 end
292 292 end
293 293
294 294 def add_short_filter(field, expression)
295 295 return unless expression
296 296 parms = expression.scan(/^(o|c|!\*|!|\*)?(.*)$/).first
297 297 add_filter field, (parms[0] || "="), [parms[1] || ""]
298 298 end
299 299
300 300 # Add multiple filters using +add_filter+
301 301 def add_filters(fields, operators, values)
302 302 if fields.is_a?(Array) && operators.is_a?(Hash) && (values.nil? || values.is_a?(Hash))
303 303 fields.each do |field|
304 304 add_filter(field, operators[field], values && values[field])
305 305 end
306 306 end
307 307 end
308 308
309 309 def has_filter?(field)
310 310 filters and filters[field]
311 311 end
312 312
313 313 def type_for(field)
314 314 available_filters[field][:type] if available_filters.has_key?(field)
315 315 end
316 316
317 317 def operator_for(field)
318 318 has_filter?(field) ? filters[field][:operator] : nil
319 319 end
320 320
321 321 def values_for(field)
322 322 has_filter?(field) ? filters[field][:values] : nil
323 323 end
324 324
325 325 def value_for(field, index=0)
326 326 (values_for(field) || [])[index]
327 327 end
328 328
329 329 def label_for(field)
330 330 label = available_filters[field][:name] if available_filters.has_key?(field)
331 331 label ||= field.gsub(/\_id$/, "")
332 332 end
333 333
334 334 def available_columns
335 335 return @available_columns if @available_columns
336 336 @available_columns = Query.available_columns
337 337 @available_columns += (project ?
338 338 project.all_issue_custom_fields :
339 339 IssueCustomField.find(:all)
340 340 ).collect {|cf| QueryCustomFieldColumn.new(cf) }
341 341 end
342 342
343 343 def self.available_columns=(v)
344 344 self.available_columns = (v)
345 345 end
346 346
347 347 def self.add_available_column(column)
348 348 self.available_columns << (column) if column.is_a?(QueryColumn)
349 349 end
350 350
351 351 # Returns an array of columns that can be used to group the results
352 352 def groupable_columns
353 353 available_columns.select {|c| c.groupable}
354 354 end
355 355
356 356 # Returns a Hash of columns and the key for sorting
357 357 def sortable_columns
358 358 {'id' => "#{Issue.table_name}.id"}.merge(available_columns.inject({}) {|h, column|
359 359 h[column.name.to_s] = column.sortable
360 360 h
361 361 })
362 362 end
363 363
364 364 def columns
365 365 if has_default_columns?
366 366 available_columns.select do |c|
367 367 # Adds the project column by default for cross-project lists
368 368 Setting.issue_list_default_columns.include?(c.name.to_s) || (c.name == :project && project.nil?)
369 369 end
370 370 else
371 371 # preserve the column_names order
372 372 column_names.collect {|name| available_columns.find {|col| col.name == name}}.compact
373 373 end
374 374 end
375 375
376 376 def column_names=(names)
377 377 if names
378 378 names = names.select {|n| n.is_a?(Symbol) || !n.blank? }
379 379 names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }
380 380 # Set column_names to nil if default columns
381 381 if names.map(&:to_s) == Setting.issue_list_default_columns
382 382 names = nil
383 383 end
384 384 end
385 385 write_attribute(:column_names, names)
386 386 end
387 387
388 388 def has_column?(column)
389 389 column_names && column_names.include?(column.name)
390 390 end
391 391
392 392 def has_default_columns?
393 393 column_names.nil? || column_names.empty?
394 394 end
395 395
396 396 def sort_criteria=(arg)
397 397 c = []
398 398 if arg.is_a?(Hash)
399 399 arg = arg.keys.sort.collect {|k| arg[k]}
400 400 end
401 401 c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, o == 'desc' ? o : 'asc']}
402 402 write_attribute(:sort_criteria, c)
403 403 end
404 404
405 405 def sort_criteria
406 406 read_attribute(:sort_criteria) || []
407 407 end
408 408
409 409 def sort_criteria_key(arg)
410 410 sort_criteria && sort_criteria[arg] && sort_criteria[arg].first
411 411 end
412 412
413 413 def sort_criteria_order(arg)
414 414 sort_criteria && sort_criteria[arg] && sort_criteria[arg].last
415 415 end
416 416
417 417 # Returns the SQL sort order that should be prepended for grouping
418 418 def group_by_sort_order
419 419 if grouped? && (column = group_by_column)
420 420 column.sortable.is_a?(Array) ?
421 421 column.sortable.collect {|s| "#{s} #{column.default_order}"}.join(',') :
422 422 "#{column.sortable} #{column.default_order}"
423 423 end
424 424 end
425 425
426 426 # Returns true if the query is a grouped query
427 427 def grouped?
428 428 !group_by_column.nil?
429 429 end
430 430
431 431 def group_by_column
432 432 groupable_columns.detect {|c| c.groupable && c.name.to_s == group_by}
433 433 end
434 434
435 435 def group_by_statement
436 436 group_by_column.try(:groupable)
437 437 end
438 438
439 439 def project_statement
440 440 project_clauses = []
441 441 if project && !@project.descendants.active.empty?
442 442 ids = [project.id]
443 443 if has_filter?("subproject_id")
444 444 case operator_for("subproject_id")
445 445 when '='
446 446 # include the selected subprojects
447 447 ids += values_for("subproject_id").each(&:to_i)
448 448 when '!*'
449 449 # main project only
450 450 else
451 451 # all subprojects
452 452 ids += project.descendants.collect(&:id)
453 453 end
454 454 elsif Setting.display_subprojects_issues?
455 455 ids += project.descendants.collect(&:id)
456 456 end
457 457 project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
458 458 elsif project
459 459 project_clauses << "#{Project.table_name}.id = %d" % project.id
460 460 end
461 461 project_clauses.any? ? project_clauses.join(' AND ') : nil
462 462 end
463 463
464 464 def statement
465 465 # filters clauses
466 466 filters_clauses = []
467 467 filters.each_key do |field|
468 468 next if field == "subproject_id"
469 469 v = values_for(field).clone
470 470 next unless v and !v.empty?
471 471 operator = operator_for(field)
472 472
473 473 # "me" value subsitution
474 474 if %w(assigned_to_id author_id watcher_id).include?(field)
475 475 v.push(User.current.logged? ? User.current.id.to_s : "0") if v.delete("me")
476 476 end
477 477
478 478 sql = ''
479 479 if field =~ /^cf_(\d+)$/
480 480 # custom field
481 481 db_table = CustomValue.table_name
482 482 db_field = 'value'
483 483 is_custom_filter = true
484 484 sql << "#{Issue.table_name}.id IN (SELECT #{Issue.table_name}.id FROM #{Issue.table_name} LEFT OUTER JOIN #{db_table} ON #{db_table}.customized_type='Issue' AND #{db_table}.customized_id=#{Issue.table_name}.id AND #{db_table}.custom_field_id=#{$1} WHERE "
485 485 sql << sql_for_field(field, operator, v, db_table, db_field, true) + ')'
486 486 elsif field == 'watcher_id'
487 487 db_table = Watcher.table_name
488 488 db_field = 'user_id'
489 489 sql << "#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{db_table}.watchable_id FROM #{db_table} WHERE #{db_table}.watchable_type='Issue' AND "
490 490 sql << sql_for_field(field, '=', v, db_table, db_field) + ')'
491 491 elsif field == "member_of_group" # named field
492 492 if operator == '*' # Any group
493 493 groups = Group.all
494 494 operator = '=' # Override the operator since we want to find by assigned_to
495 495 elsif operator == "!*"
496 496 groups = Group.all
497 497 operator = '!' # Override the operator since we want to find by assigned_to
498 498 else
499 499 groups = Group.find_all_by_id(v)
500 500 end
501 501 groups ||= []
502 502
503 503 members_of_groups = groups.inject([]) {|user_ids, group|
504 504 if group && group.user_ids.present?
505 505 user_ids << group.user_ids
506 506 end
507 507 user_ids.flatten.uniq.compact
508 508 }.sort.collect(&:to_s)
509 509
510 510 sql << '(' + sql_for_field("assigned_to_id", operator, members_of_groups, Issue.table_name, "assigned_to_id", false) + ')'
511 511
512 512 elsif field == "assigned_to_role" # named field
513 513 if operator == "*" # Any Role
514 514 roles = Role.givable
515 515 operator = '=' # Override the operator since we want to find by assigned_to
516 516 elsif operator == "!*" # No role
517 517 roles = Role.givable
518 518 operator = '!' # Override the operator since we want to find by assigned_to
519 519 else
520 520 roles = Role.givable.find_all_by_id(v)
521 521 end
522 522 roles ||= []
523 523
524 524 members_of_roles = roles.inject([]) {|user_ids, role|
525 525 if role && role.members
526 526 user_ids << role.members.collect(&:user_id)
527 527 end
528 528 user_ids.flatten.uniq.compact
529 529 }.sort.collect(&:to_s)
530 530
531 531 sql << '(' + sql_for_field("assigned_to_id", operator, members_of_roles, Issue.table_name, "assigned_to_id", false) + ')'
532 532 else
533 533 # regular field
534 534 db_table = Issue.table_name
535 535 db_field = field
536 536 sql << '(' + sql_for_field(field, operator, v, db_table, db_field) + ')'
537 537 end
538 538 filters_clauses << sql
539 539
540 540 end if filters and valid?
541 541
542 542 filters_clauses << project_statement
543 543 filters_clauses.reject!(&:blank?)
544 544
545 545 filters_clauses.any? ? filters_clauses.join(' AND ') : nil
546 546 end
547 547
548 548 # Returns the issue count
549 549 def issue_count
550 550 Issue.count(:include => [:status, :project], :conditions => statement)
551 551 rescue ::ActiveRecord::StatementInvalid => e
552 552 raise StatementInvalid.new(e.message)
553 553 end
554 554
555 555 # Returns the issue count by group or nil if query is not grouped
556 556 def issue_count_by_group
557 557 r = nil
558 558 if grouped?
559 559 begin
560 560 # Rails will raise an (unexpected) RecordNotFound if there's only a nil group value
561 561 r = Issue.visible.count(:group => group_by_statement, :include => [:status, :project], :conditions => statement)
562 562 rescue ActiveRecord::RecordNotFound
563 563 r = {nil => issue_count}
564 564 end
565 565 c = group_by_column
566 566 if c.is_a?(QueryCustomFieldColumn)
567 567 r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
568 568 end
569 569 end
570 570 r
571 571 rescue ::ActiveRecord::StatementInvalid => e
572 572 raise StatementInvalid.new(e.message)
573 573 end
574 574
575 575 # Returns the issues
576 576 # Valid options are :order, :offset, :limit, :include, :conditions
577 577 def issues(options={})
578 578 order_option = [group_by_sort_order, options[:order]].reject {|s| s.blank?}.join(',')
579 579 order_option = nil if order_option.blank?
580 580
581 581 Issue.visible.find :all, :include => ([:status, :project] + (options[:include] || [])).uniq,
582 582 :conditions => Query.merge_conditions(statement, options[:conditions]),
583 583 :order => order_option,
584 584 :limit => options[:limit],
585 585 :offset => options[:offset]
586 586 rescue ::ActiveRecord::StatementInvalid => e
587 587 raise StatementInvalid.new(e.message)
588 588 end
589 589
590 590 # Returns the journals
591 591 # Valid options are :order, :offset, :limit
592 592 def journals(options={})
593 593 Journal.visible.find :all, :include => [:details, :user, {:issue => [:project, :author, :tracker, :status]}],
594 594 :conditions => statement,
595 595 :order => options[:order],
596 596 :limit => options[:limit],
597 597 :offset => options[:offset]
598 598 rescue ::ActiveRecord::StatementInvalid => e
599 599 raise StatementInvalid.new(e.message)
600 600 end
601 601
602 602 # Returns the versions
603 603 # Valid options are :conditions
604 604 def versions(options={})
605 605 Version.visible.find :all, :include => :project,
606 606 :conditions => Query.merge_conditions(project_statement, options[:conditions])
607 607 rescue ::ActiveRecord::StatementInvalid => e
608 608 raise StatementInvalid.new(e.message)
609 609 end
610 610
611 611 private
612 612
613 613 # Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+
614 614 def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)
615 615 sql = ''
616 616 case operator
617 617 when "="
618 618 if value.any?
619 619 case type_for(field)
620 620 when :date, :date_past
621 621 sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), (Date.parse(value.first) rescue nil))
622 622 when :integer
623 623 sql = "#{db_table}.#{db_field} = #{value.first.to_i}"
624 624 when :float
625 625 sql = "#{db_table}.#{db_field} BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5}"
626 626 else
627 627 sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")"
628 628 end
629 629 else
630 630 # IN an empty set
631 631 sql = "1=0"
632 632 end
633 633 when "!"
634 634 if value.any?
635 635 sql = "(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + "))"
636 636 else
637 637 # NOT IN an empty set
638 638 sql = "1=1"
639 639 end
640 640 when "!*"
641 641 sql = "#{db_table}.#{db_field} IS NULL"
642 642 sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter
643 643 when "*"
644 644 sql = "#{db_table}.#{db_field} IS NOT NULL"
645 645 sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
646 646 when ">="
647 647 if [:date, :date_past].include?(type_for(field))
648 648 sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), nil)
649 649 else
650 650 if is_custom_filter
651 651 sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) >= #{value.first.to_f}"
652 652 else
653 653 sql = "#{db_table}.#{db_field} >= #{value.first.to_f}"
654 654 end
655 655 end
656 656 when "<="
657 657 if [:date, :date_past].include?(type_for(field))
658 658 sql = date_clause(db_table, db_field, nil, (Date.parse(value.first) rescue nil))
659 659 else
660 660 if is_custom_filter
661 661 sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) <= #{value.first.to_f}"
662 662 else
663 663 sql = "#{db_table}.#{db_field} <= #{value.first.to_f}"
664 664 end
665 665 end
666 666 when "><"
667 667 if [:date, :date_past].include?(type_for(field))
668 668 sql = date_clause(db_table, db_field, (Date.parse(value[0]) rescue nil), (Date.parse(value[1]) rescue nil))
669 669 else
670 670 if is_custom_filter
671 671 sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
672 672 else
673 673 sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
674 674 end
675 675 end
676 676 when "o"
677 677 sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_false}" if field == "status_id"
678 678 when "c"
679 679 sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_true}" if field == "status_id"
680 680 when ">t-"
681 681 sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0)
682 682 when "<t-"
683 683 sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i)
684 684 when "t-"
685 685 sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i)
686 686 when ">t+"
687 687 sql = relative_date_clause(db_table, db_field, value.first.to_i, nil)
688 688 when "<t+"
689 689 sql = relative_date_clause(db_table, db_field, 0, value.first.to_i)
690 690 when "t+"
691 691 sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i)
692 692 when "t"
693 693 sql = relative_date_clause(db_table, db_field, 0, 0)
694 694 when "w"
695 695 first_day_of_week = l(:general_first_day_of_week).to_i
696 696 day_of_week = Date.today.cwday
697 697 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
698 698 sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6)
699 699 when "~"
700 700 sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
701 701 when "!~"
702 702 sql = "LOWER(#{db_table}.#{db_field}) NOT LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
703 703 else
704 704 raise "Unknown query operator #{operator}"
705 705 end
706 706
707 707 return sql
708 708 end
709 709
710 710 def add_custom_fields_filters(custom_fields)
711 711 @available_filters ||= {}
712 712
713 713 custom_fields.select(&:is_filter?).each do |field|
714 714 case field.field_format
715 715 when "text"
716 716 options = { :type => :text, :order => 20 }
717 717 when "list"
718 718 options = { :type => :list_optional, :values => field.possible_values, :order => 20}
719 719 when "date"
720 720 options = { :type => :date, :order => 20 }
721 721 when "bool"
722 722 options = { :type => :list, :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]], :order => 20 }
723 723 when "int"
724 724 options = { :type => :integer, :order => 20 }
725 when "float",
725 when "float"
726 726 options = { :type => :float, :order => 20 }
727 727 when "user", "version"
728 728 next unless project
729 729 options = { :type => :list_optional, :values => field.possible_values_options(project), :order => 20}
730 730 else
731 731 options = { :type => :string, :order => 20 }
732 732 end
733 733 @available_filters["cf_#{field.id}"] = options.merge({ :name => field.name })
734 734 end
735 735 end
736 736
737 737 # Returns a SQL clause for a date or datetime field.
738 738 def date_clause(table, field, from, to)
739 739 s = []
740 740 if from
741 741 s << ("#{table}.#{field} > '%s'" % [connection.quoted_date((from - 1).to_time.end_of_day)])
742 742 end
743 743 if to
744 744 s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date(to.to_time.end_of_day)])
745 745 end
746 746 s.join(' AND ')
747 747 end
748 748
749 749 # Returns a SQL clause for a date or datetime field using relative dates.
750 750 def relative_date_clause(table, field, days_from, days_to)
751 751 date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil))
752 752 end
753 753 end
General Comments 0
You need to be logged in to leave comments. Login now