##// END OF EJS Templates
jro_apps » redmine
RSS
Refactor: Move method to Query model...
Eric Davis -
r3570:bf33b57aa403
parent child
Show More
Side by Side Unified
Context file:
r3570:bf33b57aa403 Loading diff...:
Show file before | Show file after | Hide comments
@@ -1,81 +1,77
1 1 # redMine - project management software
2 2 # Copyright (C) 2006-2007 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 QueriesController < ApplicationController
19 19 menu_item :issues
20 20 before_filter :find_query, :except => :new
21 21 before_filter :find_optional_project, :only => :new
22 22
23 23 def new
24 24 @query = Query.new(params[:query])
25 25 @query.project = params[:query_is_for_all] ? nil : @project
26 26 @query.user = User.current
27 27 @query.is_public = false unless User.current.allowed_to?(:manage_public_queries, @project) || User.current.admin?
28 28 @query.column_names = nil if params[:default_columns]
29 29
30 params[:fields].each do |field|
31 @query.add_filter(field, params[:operators][field], params[:values][field])
32 end if params[:fields]
30 @query.add_filters(params[:fields], params[:operators], params[:values]) if params[:fields]
33 31 @query.group_by ||= params[:group_by]
34 32
35 33 if request.post? && params[:confirm] && @query.save
36 34 flash[:notice] = l(:notice_successful_create)
37 35 redirect_to :controller => 'issues', :action => 'index', :project_id => @project, :query_id => @query
38 36 return
39 37 end
40 38 render :layout => false if request.xhr?
41 39 end
42 40
43 41 def edit
44 42 if request.post?
45 43 @query.filters = {}
46 params[:fields].each do |field|
47 @query.add_filter(field, params[:operators][field], params[:values][field])
48 end if params[:fields]
44 @query.add_filters(params[:fields], params[:operators], params[:values]) if params[:fields]
49 45 @query.attributes = params[:query]
50 46 @query.project = nil if params[:query_is_for_all]
51 47 @query.is_public = false unless User.current.allowed_to?(:manage_public_queries, @project) || User.current.admin?
52 48 @query.column_names = nil if params[:default_columns]
53 49
54 50 if @query.save
55 51 flash[:notice] = l(:notice_successful_update)
56 52 redirect_to :controller => 'issues', :action => 'index', :project_id => @project, :query_id => @query
57 53 end
58 54 end
59 55 end
60 56
61 57 def destroy
62 58 @query.destroy if request.post?
63 59 redirect_to :controller => 'issues', :action => 'index', :project_id => @project, :set_filter => 1
64 60 end
65 61
66 62 private
67 63 def find_query
68 64 @query = Query.find(params[:id])
69 65 @project = @query.project
70 66 render_403 unless @query.editable_by?(User.current)
71 67 rescue ActiveRecord::RecordNotFound
72 68 render_404
73 69 end
74 70
75 71 def find_optional_project
76 72 @project = Project.find(params[:project_id]) if params[:project_id]
77 73 render_403 unless User.current.allowed_to?(:save_queries, @project, :global => true)
78 74 rescue ActiveRecord::RecordNotFound
79 75 render_404
80 76 end
81 77 end
Show file before | Show file after | Hide comments
@@ -1,575 +1,582
1 1 # Redmine - project management software
2 2 # Copyright (C) 2006-2008 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 end
46 46
47 47 class QueryCustomFieldColumn < QueryColumn
48 48
49 49 def initialize(custom_field)
50 50 self.name = "cf_#{custom_field.id}".to_sym
51 51 self.sortable = custom_field.order_statement || false
52 52 if %w(list date bool int).include?(custom_field.field_format)
53 53 self.groupable = custom_field.order_statement
54 54 end
55 55 self.groupable ||= false
56 56 @cf = custom_field
57 57 end
58 58
59 59 def caption
60 60 @cf.name
61 61 end
62 62
63 63 def custom_field
64 64 @cf
65 65 end
66 66
67 67 def value(issue)
68 68 cv = issue.custom_values.detect {|v| v.custom_field_id == @cf.id}
69 69 cv && @cf.cast_value(cv.value)
70 70 end
71 71 end
72 72
73 73 class Query < ActiveRecord::Base
74 74 class StatementInvalid < ::ActiveRecord::StatementInvalid
75 75 end
76 76
77 77 belongs_to :project
78 78 belongs_to :user
79 79 serialize :filters
80 80 serialize :column_names
81 81 serialize :sort_criteria, Array
82 82
83 83 attr_protected :project_id, :user_id
84 84
85 85 validates_presence_of :name, :on => :save
86 86 validates_length_of :name, :maximum => 255
87 87
88 88 @@operators = { "=" => :label_equals,
89 89 "!" => :label_not_equals,
90 90 "o" => :label_open_issues,
91 91 "c" => :label_closed_issues,
92 92 "!*" => :label_none,
93 93 "*" => :label_all,
94 94 ">=" => :label_greater_or_equal,
95 95 "<=" => :label_less_or_equal,
96 96 "<t+" => :label_in_less_than,
97 97 ">t+" => :label_in_more_than,
98 98 "t+" => :label_in,
99 99 "t" => :label_today,
100 100 "w" => :label_this_week,
101 101 ">t-" => :label_less_than_ago,
102 102 "<t-" => :label_more_than_ago,
103 103 "t-" => :label_ago,
104 104 "~" => :label_contains,
105 105 "!~" => :label_not_contains }
106 106
107 107 cattr_reader :operators
108 108
109 109 @@operators_by_filter_type = { :list => [ "=", "!" ],
110 110 :list_status => [ "o", "=", "!", "c", "*" ],
111 111 :list_optional => [ "=", "!", "!*", "*" ],
112 112 :list_subprojects => [ "*", "!*", "=" ],
113 113 :date => [ "<t+", ">t+", "t+", "t", "w", ">t-", "<t-", "t-" ],
114 114 :date_past => [ ">t-", "<t-", "t-", "t", "w" ],
115 115 :string => [ "=", "~", "!", "!~" ],
116 116 :text => [ "~", "!~" ],
117 117 :integer => [ "=", ">=", "<=", "!*", "*" ] }
118 118
119 119 cattr_reader :operators_by_filter_type
120 120
121 121 @@available_columns = [
122 122 QueryColumn.new(:project, :sortable => "#{Project.table_name}.name", :groupable => true),
123 123 QueryColumn.new(:tracker, :sortable => "#{Tracker.table_name}.position", :groupable => true),
124 124 QueryColumn.new(:parent, :sortable => ["#{Issue.table_name}.root_id", "#{Issue.table_name}.lft ASC"], :default_order => 'desc', :caption => :field_parent_issue),
125 125 QueryColumn.new(:status, :sortable => "#{IssueStatus.table_name}.position", :groupable => true),
126 126 QueryColumn.new(:priority, :sortable => "#{IssuePriority.table_name}.position", :default_order => 'desc', :groupable => true),
127 127 QueryColumn.new(:subject, :sortable => "#{Issue.table_name}.subject"),
128 128 QueryColumn.new(:author),
129 129 QueryColumn.new(:assigned_to, :sortable => ["#{User.table_name}.lastname", "#{User.table_name}.firstname", "#{User.table_name}.id"], :groupable => true),
130 130 QueryColumn.new(:updated_on, :sortable => "#{Issue.table_name}.updated_on", :default_order => 'desc'),
131 131 QueryColumn.new(:category, :sortable => "#{IssueCategory.table_name}.name", :groupable => true),
132 132 QueryColumn.new(:fixed_version, :sortable => ["#{Version.table_name}.effective_date", "#{Version.table_name}.name"], :default_order => 'desc', :groupable => true),
133 133 QueryColumn.new(:start_date, :sortable => "#{Issue.table_name}.start_date"),
134 134 QueryColumn.new(:due_date, :sortable => "#{Issue.table_name}.due_date"),
135 135 QueryColumn.new(:estimated_hours, :sortable => "#{Issue.table_name}.estimated_hours"),
136 136 QueryColumn.new(:done_ratio, :sortable => "#{Issue.table_name}.done_ratio", :groupable => true),
137 137 QueryColumn.new(:created_on, :sortable => "#{Issue.table_name}.created_on", :default_order => 'desc'),
138 138 ]
139 139 cattr_reader :available_columns
140 140
141 141 def initialize(attributes = nil)
142 142 super attributes
143 143 self.filters ||= { 'status_id' => {:operator => "o", :values => [""]} }
144 144 end
145 145
146 146 def after_initialize
147 147 # Store the fact that project is nil (used in #editable_by?)
148 148 @is_for_all = project.nil?
149 149 end
150 150
151 151 def validate
152 152 filters.each_key do |field|
153 153 errors.add label_for(field), :blank unless
154 154 # filter requires one or more values
155 155 (values_for(field) and !values_for(field).first.blank?) or
156 156 # filter doesn't require any value
157 157 ["o", "c", "!*", "*", "t", "w"].include? operator_for(field)
158 158 end if filters
159 159 end
160 160
161 161 def editable_by?(user)
162 162 return false unless user
163 163 # Admin can edit them all and regular users can edit their private queries
164 164 return true if user.admin? || (!is_public && self.user_id == user.id)
165 165 # Members can not edit public queries that are for all project (only admin is allowed to)
166 166 is_public && !@is_for_all && user.allowed_to?(:manage_public_queries, project)
167 167 end
168 168
169 169 def available_filters
170 170 return @available_filters if @available_filters
171 171
172 172 trackers = project.nil? ? Tracker.find(:all, :order => 'position') : project.rolled_up_trackers
173 173
174 174 @available_filters = { "status_id" => { :type => :list_status, :order => 1, :values => IssueStatus.find(:all, :order => 'position').collect{|s| [s.name, s.id.to_s] } },
175 175 "tracker_id" => { :type => :list, :order => 2, :values => trackers.collect{|s| [s.name, s.id.to_s] } },
176 176 "priority_id" => { :type => :list, :order => 3, :values => IssuePriority.all.collect{|s| [s.name, s.id.to_s] } },
177 177 "subject" => { :type => :text, :order => 8 },
178 178 "created_on" => { :type => :date_past, :order => 9 },
179 179 "updated_on" => { :type => :date_past, :order => 10 },
180 180 "start_date" => { :type => :date, :order => 11 },
181 181 "due_date" => { :type => :date, :order => 12 },
182 182 "estimated_hours" => { :type => :integer, :order => 13 },
183 183 "done_ratio" => { :type => :integer, :order => 14 }}
184 184
185 185 user_values = []
186 186 user_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
187 187 if project
188 188 user_values += project.users.sort.collect{|s| [s.name, s.id.to_s] }
189 189 else
190 190 project_ids = User.current.projects.collect(&:id)
191 191 if project_ids.any?
192 192 # members of the user's projects
193 193 user_values += User.active.find(:all, :conditions => ["#{User.table_name}.id IN (SELECT DISTINCT user_id FROM members WHERE project_id IN (?))", project_ids]).sort.collect{|s| [s.name, s.id.to_s] }
194 194 end
195 195 end
196 196 @available_filters["assigned_to_id"] = { :type => :list_optional, :order => 4, :values => user_values } unless user_values.empty?
197 197 @available_filters["author_id"] = { :type => :list, :order => 5, :values => user_values } unless user_values.empty?
198 198
199 199 if User.current.logged?
200 200 @available_filters["watcher_id"] = { :type => :list, :order => 15, :values => [["<< #{l(:label_me)} >>", "me"]] }
201 201 end
202 202
203 203 if project
204 204 # project specific filters
205 205 unless @project.issue_categories.empty?
206 206 @available_filters["category_id"] = { :type => :list_optional, :order => 6, :values => @project.issue_categories.collect{|s| [s.name, s.id.to_s] } }
207 207 end
208 208 unless @project.shared_versions.empty?
209 209 @available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => @project.shared_versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }
210 210 end
211 211 unless @project.descendants.active.empty?
212 212 @available_filters["subproject_id"] = { :type => :list_subprojects, :order => 13, :values => @project.descendants.visible.collect{|s| [s.name, s.id.to_s] } }
213 213 end
214 214 add_custom_fields_filters(@project.all_issue_custom_fields)
215 215 else
216 216 # global filters for cross project issue list
217 217 system_shared_versions = Version.visible.find_all_by_sharing('system')
218 218 unless system_shared_versions.empty?
219 219 @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] } }
220 220 end
221 221 add_custom_fields_filters(IssueCustomField.find(:all, :conditions => {:is_filter => true, :is_for_all => true}))
222 222 end
223 223 @available_filters
224 224 end
225 225
226 226 def add_filter(field, operator, values)
227 227 # values must be an array
228 228 return unless values and values.is_a? Array # and !values.first.empty?
229 229 # check if field is defined as an available filter
230 230 if available_filters.has_key? field
231 231 filter_options = available_filters[field]
232 232 # check if operator is allowed for that filter
233 233 #if @@operators_by_filter_type[filter_options[:type]].include? operator
234 234 # allowed_values = values & ([""] + (filter_options[:values] || []).collect {|val| val[1]})
235 235 # filters[field] = {:operator => operator, :values => allowed_values } if (allowed_values.first and !allowed_values.first.empty?) or ["o", "c", "!*", "*", "t"].include? operator
236 236 #end
237 237 filters[field] = {:operator => operator, :values => values }
238 238 end
239 239 end
240 240
241 241 def add_short_filter(field, expression)
242 242 return unless expression
243 243 parms = expression.scan(/^(o|c|!\*|!|\*)?(.*)$/).first
244 244 add_filter field, (parms[0] || "="), [parms[1] || ""]
245 245 end
246
247 # Add multiple filters using +add_filter+
248 def add_filters(fields, operators, values)
249 fields.each do |field|
250 add_filter(field, operators[field], values[field])
251 end
252 end
246 253
247 254 def has_filter?(field)
248 255 filters and filters[field]
249 256 end
250 257
251 258 def operator_for(field)
252 259 has_filter?(field) ? filters[field][:operator] : nil
253 260 end
254 261
255 262 def values_for(field)
256 263 has_filter?(field) ? filters[field][:values] : nil
257 264 end
258 265
259 266 def label_for(field)
260 267 label = available_filters[field][:name] if available_filters.has_key?(field)
261 268 label ||= field.gsub(/\_id$/, "")
262 269 end
263 270
264 271 def available_columns
265 272 return @available_columns if @available_columns
266 273 @available_columns = Query.available_columns
267 274 @available_columns += (project ?
268 275 project.all_issue_custom_fields :
269 276 IssueCustomField.find(:all)
270 277 ).collect {|cf| QueryCustomFieldColumn.new(cf) }
271 278 end
272 279
273 280 # Returns an array of columns that can be used to group the results
274 281 def groupable_columns
275 282 available_columns.select {|c| c.groupable}
276 283 end
277 284
278 285 # Returns a Hash of columns and the key for sorting
279 286 def sortable_columns
280 287 {'id' => "#{Issue.table_name}.id"}.merge(available_columns.inject({}) {|h, column|
281 288 h[column.name.to_s] = column.sortable
282 289 h
283 290 })
284 291 end
285 292
286 293 def columns
287 294 if has_default_columns?
288 295 available_columns.select do |c|
289 296 # Adds the project column by default for cross-project lists
290 297 Setting.issue_list_default_columns.include?(c.name.to_s) || (c.name == :project && project.nil?)
291 298 end
292 299 else
293 300 # preserve the column_names order
294 301 column_names.collect {|name| available_columns.find {|col| col.name == name}}.compact
295 302 end
296 303 end
297 304
298 305 def column_names=(names)
299 306 if names
300 307 names = names.select {|n| n.is_a?(Symbol) || !n.blank? }
301 308 names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }
302 309 # Set column_names to nil if default columns
303 310 if names.map(&:to_s) == Setting.issue_list_default_columns
304 311 names = nil
305 312 end
306 313 end
307 314 write_attribute(:column_names, names)
308 315 end
309 316
310 317 def has_column?(column)
311 318 column_names && column_names.include?(column.name)
312 319 end
313 320
314 321 def has_default_columns?
315 322 column_names.nil? || column_names.empty?
316 323 end
317 324
318 325 def sort_criteria=(arg)
319 326 c = []
320 327 if arg.is_a?(Hash)
321 328 arg = arg.keys.sort.collect {|k| arg[k]}
322 329 end
323 330 c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, o == 'desc' ? o : 'asc']}
324 331 write_attribute(:sort_criteria, c)
325 332 end
326 333
327 334 def sort_criteria
328 335 read_attribute(:sort_criteria) || []
329 336 end
330 337
331 338 def sort_criteria_key(arg)
332 339 sort_criteria && sort_criteria[arg] && sort_criteria[arg].first
333 340 end
334 341
335 342 def sort_criteria_order(arg)
336 343 sort_criteria && sort_criteria[arg] && sort_criteria[arg].last
337 344 end
338 345
339 346 # Returns the SQL sort order that should be prepended for grouping
340 347 def group_by_sort_order
341 348 if grouped? && (column = group_by_column)
342 349 column.sortable.is_a?(Array) ?
343 350 column.sortable.collect {|s| "#{s} #{column.default_order}"}.join(',') :
344 351 "#{column.sortable} #{column.default_order}"
345 352 end
346 353 end
347 354
348 355 # Returns true if the query is a grouped query
349 356 def grouped?
350 357 !group_by.blank?
351 358 end
352 359
353 360 def group_by_column
354 361 groupable_columns.detect {|c| c.name.to_s == group_by}
355 362 end
356 363
357 364 def group_by_statement
358 365 group_by_column.groupable
359 366 end
360 367
361 368 def project_statement
362 369 project_clauses = []
363 370 if project && !@project.descendants.active.empty?
364 371 ids = [project.id]
365 372 if has_filter?("subproject_id")
366 373 case operator_for("subproject_id")
367 374 when '='
368 375 # include the selected subprojects
369 376 ids += values_for("subproject_id").each(&:to_i)
370 377 when '!*'
371 378 # main project only
372 379 else
373 380 # all subprojects
374 381 ids += project.descendants.collect(&:id)
375 382 end
376 383 elsif Setting.display_subprojects_issues?
377 384 ids += project.descendants.collect(&:id)
378 385 end
379 386 project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
380 387 elsif project
381 388 project_clauses << "#{Project.table_name}.id = %d" % project.id
382 389 end
383 390 project_clauses << Project.allowed_to_condition(User.current, :view_issues)
384 391 project_clauses.join(' AND ')
385 392 end
386 393
387 394 def statement
388 395 # filters clauses
389 396 filters_clauses = []
390 397 filters.each_key do |field|
391 398 next if field == "subproject_id"
392 399 v = values_for(field).clone
393 400 next unless v and !v.empty?
394 401 operator = operator_for(field)
395 402
396 403 # "me" value subsitution
397 404 if %w(assigned_to_id author_id watcher_id).include?(field)
398 405 v.push(User.current.logged? ? User.current.id.to_s : "0") if v.delete("me")
399 406 end
400 407
401 408 sql = ''
402 409 if field =~ /^cf_(\d+)$/
403 410 # custom field
404 411 db_table = CustomValue.table_name
405 412 db_field = 'value'
406 413 is_custom_filter = true
407 414 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 "
408 415 sql << sql_for_field(field, operator, v, db_table, db_field, true) + ')'
409 416 elsif field == 'watcher_id'
410 417 db_table = Watcher.table_name
411 418 db_field = 'user_id'
412 419 sql << "#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{db_table}.watchable_id FROM #{db_table} WHERE #{db_table}.watchable_type='Issue' AND "
413 420 sql << sql_for_field(field, '=', v, db_table, db_field) + ')'
414 421 else
415 422 # regular field
416 423 db_table = Issue.table_name
417 424 db_field = field
418 425 sql << '(' + sql_for_field(field, operator, v, db_table, db_field) + ')'
419 426 end
420 427 filters_clauses << sql
421 428
422 429 end if filters and valid?
423 430
424 431 (filters_clauses << project_statement).join(' AND ')
425 432 end
426 433
427 434 # Returns the issue count
428 435 def issue_count
429 436 Issue.count(:include => [:status, :project], :conditions => statement)
430 437 rescue ::ActiveRecord::StatementInvalid => e
431 438 raise StatementInvalid.new(e.message)
432 439 end
433 440
434 441 # Returns the issue count by group or nil if query is not grouped
435 442 def issue_count_by_group
436 443 r = nil
437 444 if grouped?
438 445 begin
439 446 # Rails will raise an (unexpected) RecordNotFound if there's only a nil group value
440 447 r = Issue.count(:group => group_by_statement, :include => [:status, :project], :conditions => statement)
441 448 rescue ActiveRecord::RecordNotFound
442 449 r = {nil => issue_count}
443 450 end
444 451 c = group_by_column
445 452 if c.is_a?(QueryCustomFieldColumn)
446 453 r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
447 454 end
448 455 end
449 456 r
450 457 rescue ::ActiveRecord::StatementInvalid => e
451 458 raise StatementInvalid.new(e.message)
452 459 end
453 460
454 461 # Returns the issues
455 462 # Valid options are :order, :offset, :limit, :include, :conditions
456 463 def issues(options={})
457 464 order_option = [group_by_sort_order, options[:order]].reject {|s| s.blank?}.join(',')
458 465 order_option = nil if order_option.blank?
459 466
460 467 Issue.find :all, :include => ([:status, :project] + (options[:include] || [])).uniq,
461 468 :conditions => Query.merge_conditions(statement, options[:conditions]),
462 469 :order => order_option,
463 470 :limit => options[:limit],
464 471 :offset => options[:offset]
465 472 rescue ::ActiveRecord::StatementInvalid => e
466 473 raise StatementInvalid.new(e.message)
467 474 end
468 475
469 476 # Returns the journals
470 477 # Valid options are :order, :offset, :limit
471 478 def journals(options={})
472 479 Journal.find :all, :include => [:details, :user, {:issue => [:project, :author, :tracker, :status]}],
473 480 :conditions => statement,
474 481 :order => options[:order],
475 482 :limit => options[:limit],
476 483 :offset => options[:offset]
477 484 rescue ::ActiveRecord::StatementInvalid => e
478 485 raise StatementInvalid.new(e.message)
479 486 end
480 487
481 488 # Returns the versions
482 489 # Valid options are :conditions
483 490 def versions(options={})
484 491 Version.find :all, :include => :project,
485 492 :conditions => Query.merge_conditions(project_statement, options[:conditions])
486 493 rescue ::ActiveRecord::StatementInvalid => e
487 494 raise StatementInvalid.new(e.message)
488 495 end
489 496
490 497 private
491 498
492 499 # Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+
493 500 def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)
494 501 sql = ''
495 502 case operator
496 503 when "="
497 504 sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")"
498 505 when "!"
499 506 sql = "(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + "))"
500 507 when "!*"
501 508 sql = "#{db_table}.#{db_field} IS NULL"
502 509 sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter
503 510 when "*"
504 511 sql = "#{db_table}.#{db_field} IS NOT NULL"
505 512 sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
506 513 when ">="
507 514 sql = "#{db_table}.#{db_field} >= #{value.first.to_i}"
508 515 when "<="
509 516 sql = "#{db_table}.#{db_field} <= #{value.first.to_i}"
510 517 when "o"
511 518 sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_false}" if field == "status_id"
512 519 when "c"
513 520 sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_true}" if field == "status_id"
514 521 when ">t-"
515 522 sql = date_range_clause(db_table, db_field, - value.first.to_i, 0)
516 523 when "<t-"
517 524 sql = date_range_clause(db_table, db_field, nil, - value.first.to_i)
518 525 when "t-"
519 526 sql = date_range_clause(db_table, db_field, - value.first.to_i, - value.first.to_i)
520 527 when ">t+"
521 528 sql = date_range_clause(db_table, db_field, value.first.to_i, nil)
522 529 when "<t+"
523 530 sql = date_range_clause(db_table, db_field, 0, value.first.to_i)
524 531 when "t+"
525 532 sql = date_range_clause(db_table, db_field, value.first.to_i, value.first.to_i)
526 533 when "t"
527 534 sql = date_range_clause(db_table, db_field, 0, 0)
528 535 when "w"
529 536 from = l(:general_first_day_of_week) == '7' ?
530 537 # week starts on sunday
531 538 ((Date.today.cwday == 7) ? Time.now.at_beginning_of_day : Time.now.at_beginning_of_week - 1.day) :
532 539 # week starts on monday (Rails default)
533 540 Time.now.at_beginning_of_week
534 541 sql = "#{db_table}.#{db_field} BETWEEN '%s' AND '%s'" % [connection.quoted_date(from), connection.quoted_date(from + 7.days)]
535 542 when "~"
536 543 sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
537 544 when "!~"
538 545 sql = "LOWER(#{db_table}.#{db_field}) NOT LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
539 546 end
540 547
541 548 return sql
542 549 end
543 550
544 551 def add_custom_fields_filters(custom_fields)
545 552 @available_filters ||= {}
546 553
547 554 custom_fields.select(&:is_filter?).each do |field|
548 555 case field.field_format
549 556 when "text"
550 557 options = { :type => :text, :order => 20 }
551 558 when "list"
552 559 options = { :type => :list_optional, :values => field.possible_values, :order => 20}
553 560 when "date"
554 561 options = { :type => :date, :order => 20 }
555 562 when "bool"
556 563 options = { :type => :list, :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]], :order => 20 }
557 564 else
558 565 options = { :type => :string, :order => 20 }
559 566 end
560 567 @available_filters["cf_#{field.id}"] = options.merge({ :name => field.name })
561 568 end
562 569 end
563 570
564 571 # Returns a SQL clause for a date or datetime field.
565 572 def date_range_clause(table, field, from, to)
566 573 s = []
567 574 if from
568 575 s << ("#{table}.#{field} > '%s'" % [connection.quoted_date((Date.yesterday + from).to_time.end_of_day)])
569 576 end
570 577 if to
571 578 s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date((Date.today + to).to_time.end_of_day)])
572 579 end
573 580 s.join(' AND ')
574 581 end
575 582 end
General Comments 0
You need to be logged in to leave comments. Login now