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