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