##// END OF EJS Templates
Merged r15852 and r15863 (#23839)....
Jean-Philippe Lang -
r15482:2f37af12252e
parent child
Show More
@@ -1,1039 +1,1039
1 # Redmine - project management software
1 # Redmine - project management software
2 # Copyright (C) 2006-2016 Jean-Philippe Lang
2 # Copyright (C) 2006-2016 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, :totalable, :default_order
19 attr_accessor :name, :sortable, :groupable, :totalable, :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.totalable = options[:totalable] || false
29 self.totalable = options[:totalable] || false
30 self.default_order = options[:default_order]
30 self.default_order = options[:default_order]
31 @inline = options.key?(:inline) ? options[:inline] : true
31 @inline = options.key?(:inline) ? options[:inline] : true
32 @caption_key = options[:caption] || "field_#{name}".to_sym
32 @caption_key = options[:caption] || "field_#{name}".to_sym
33 @frozen = options[:frozen]
33 @frozen = options[:frozen]
34 end
34 end
35
35
36 def caption
36 def caption
37 case @caption_key
37 case @caption_key
38 when Symbol
38 when Symbol
39 l(@caption_key)
39 l(@caption_key)
40 when Proc
40 when Proc
41 @caption_key.call
41 @caption_key.call
42 else
42 else
43 @caption_key
43 @caption_key
44 end
44 end
45 end
45 end
46
46
47 # Returns true if the column is sortable, otherwise false
47 # Returns true if the column is sortable, otherwise false
48 def sortable?
48 def sortable?
49 !@sortable.nil?
49 !@sortable.nil?
50 end
50 end
51
51
52 def sortable
52 def sortable
53 @sortable.is_a?(Proc) ? @sortable.call : @sortable
53 @sortable.is_a?(Proc) ? @sortable.call : @sortable
54 end
54 end
55
55
56 def inline?
56 def inline?
57 @inline
57 @inline
58 end
58 end
59
59
60 def frozen?
60 def frozen?
61 @frozen
61 @frozen
62 end
62 end
63
63
64 def value(object)
64 def value(object)
65 object.send name
65 object.send name
66 end
66 end
67
67
68 def value_object(object)
68 def value_object(object)
69 object.send name
69 object.send name
70 end
70 end
71
71
72 def css_classes
72 def css_classes
73 name
73 name
74 end
74 end
75 end
75 end
76
76
77 class QueryCustomFieldColumn < QueryColumn
77 class QueryCustomFieldColumn < QueryColumn
78
78
79 def initialize(custom_field)
79 def initialize(custom_field)
80 self.name = "cf_#{custom_field.id}".to_sym
80 self.name = "cf_#{custom_field.id}".to_sym
81 self.sortable = custom_field.order_statement || false
81 self.sortable = custom_field.order_statement || false
82 self.groupable = custom_field.group_statement || false
82 self.groupable = custom_field.group_statement || false
83 self.totalable = custom_field.totalable?
83 self.totalable = custom_field.totalable?
84 @inline = true
84 @inline = true
85 @cf = custom_field
85 @cf = custom_field
86 end
86 end
87
87
88 def caption
88 def caption
89 @cf.name
89 @cf.name
90 end
90 end
91
91
92 def custom_field
92 def custom_field
93 @cf
93 @cf
94 end
94 end
95
95
96 def value_object(object)
96 def value_object(object)
97 if custom_field.visible_by?(object.project, User.current)
97 if custom_field.visible_by?(object.project, User.current)
98 cv = object.custom_values.select {|v| v.custom_field_id == @cf.id}
98 cv = object.custom_values.select {|v| v.custom_field_id == @cf.id}
99 cv.size > 1 ? cv.sort {|a,b| a.value.to_s <=> b.value.to_s} : cv.first
99 cv.size > 1 ? cv.sort {|a,b| a.value.to_s <=> b.value.to_s} : cv.first
100 else
100 else
101 nil
101 nil
102 end
102 end
103 end
103 end
104
104
105 def value(object)
105 def value(object)
106 raw = value_object(object)
106 raw = value_object(object)
107 if raw.is_a?(Array)
107 if raw.is_a?(Array)
108 raw.map {|r| @cf.cast_value(r.value)}
108 raw.map {|r| @cf.cast_value(r.value)}
109 elsif raw
109 elsif raw
110 @cf.cast_value(raw.value)
110 @cf.cast_value(raw.value)
111 else
111 else
112 nil
112 nil
113 end
113 end
114 end
114 end
115
115
116 def css_classes
116 def css_classes
117 @css_classes ||= "#{name} #{@cf.field_format}"
117 @css_classes ||= "#{name} #{@cf.field_format}"
118 end
118 end
119 end
119 end
120
120
121 class QueryAssociationCustomFieldColumn < QueryCustomFieldColumn
121 class QueryAssociationCustomFieldColumn < QueryCustomFieldColumn
122
122
123 def initialize(association, custom_field)
123 def initialize(association, custom_field)
124 super(custom_field)
124 super(custom_field)
125 self.name = "#{association}.cf_#{custom_field.id}".to_sym
125 self.name = "#{association}.cf_#{custom_field.id}".to_sym
126 # TODO: support sorting/grouping by association custom field
126 # TODO: support sorting/grouping by association custom field
127 self.sortable = false
127 self.sortable = false
128 self.groupable = false
128 self.groupable = false
129 @association = association
129 @association = association
130 end
130 end
131
131
132 def value_object(object)
132 def value_object(object)
133 if assoc = object.send(@association)
133 if assoc = object.send(@association)
134 super(assoc)
134 super(assoc)
135 end
135 end
136 end
136 end
137
137
138 def css_classes
138 def css_classes
139 @css_classes ||= "#{@association}_cf_#{@cf.id} #{@cf.field_format}"
139 @css_classes ||= "#{@association}_cf_#{@cf.id} #{@cf.field_format}"
140 end
140 end
141 end
141 end
142
142
143 class Query < ActiveRecord::Base
143 class Query < ActiveRecord::Base
144 class StatementInvalid < ::ActiveRecord::StatementInvalid
144 class StatementInvalid < ::ActiveRecord::StatementInvalid
145 end
145 end
146
146
147 VISIBILITY_PRIVATE = 0
147 VISIBILITY_PRIVATE = 0
148 VISIBILITY_ROLES = 1
148 VISIBILITY_ROLES = 1
149 VISIBILITY_PUBLIC = 2
149 VISIBILITY_PUBLIC = 2
150
150
151 belongs_to :project
151 belongs_to :project
152 belongs_to :user
152 belongs_to :user
153 has_and_belongs_to_many :roles, :join_table => "#{table_name_prefix}queries_roles#{table_name_suffix}", :foreign_key => "query_id"
153 has_and_belongs_to_many :roles, :join_table => "#{table_name_prefix}queries_roles#{table_name_suffix}", :foreign_key => "query_id"
154 serialize :filters
154 serialize :filters
155 serialize :column_names
155 serialize :column_names
156 serialize :sort_criteria, Array
156 serialize :sort_criteria, Array
157 serialize :options, Hash
157 serialize :options, Hash
158
158
159 attr_protected :project_id, :user_id
159 attr_protected :project_id, :user_id
160
160
161 validates_presence_of :name
161 validates_presence_of :name
162 validates_length_of :name, :maximum => 255
162 validates_length_of :name, :maximum => 255
163 validates :visibility, :inclusion => { :in => [VISIBILITY_PUBLIC, VISIBILITY_ROLES, VISIBILITY_PRIVATE] }
163 validates :visibility, :inclusion => { :in => [VISIBILITY_PUBLIC, VISIBILITY_ROLES, VISIBILITY_PRIVATE] }
164 validate :validate_query_filters
164 validate :validate_query_filters
165 validate do |query|
165 validate do |query|
166 errors.add(:base, l(:label_role_plural) + ' ' + l('activerecord.errors.messages.blank')) if query.visibility == VISIBILITY_ROLES && roles.blank?
166 errors.add(:base, l(:label_role_plural) + ' ' + l('activerecord.errors.messages.blank')) if query.visibility == VISIBILITY_ROLES && roles.blank?
167 end
167 end
168
168
169 after_save do |query|
169 after_save do |query|
170 if query.visibility_changed? && query.visibility != VISIBILITY_ROLES
170 if query.visibility_changed? && query.visibility != VISIBILITY_ROLES
171 query.roles.clear
171 query.roles.clear
172 end
172 end
173 end
173 end
174
174
175 class_attribute :operators
175 class_attribute :operators
176 self.operators = {
176 self.operators = {
177 "=" => :label_equals,
177 "=" => :label_equals,
178 "!" => :label_not_equals,
178 "!" => :label_not_equals,
179 "o" => :label_open_issues,
179 "o" => :label_open_issues,
180 "c" => :label_closed_issues,
180 "c" => :label_closed_issues,
181 "!*" => :label_none,
181 "!*" => :label_none,
182 "*" => :label_any,
182 "*" => :label_any,
183 ">=" => :label_greater_or_equal,
183 ">=" => :label_greater_or_equal,
184 "<=" => :label_less_or_equal,
184 "<=" => :label_less_or_equal,
185 "><" => :label_between,
185 "><" => :label_between,
186 "<t+" => :label_in_less_than,
186 "<t+" => :label_in_less_than,
187 ">t+" => :label_in_more_than,
187 ">t+" => :label_in_more_than,
188 "><t+"=> :label_in_the_next_days,
188 "><t+"=> :label_in_the_next_days,
189 "t+" => :label_in,
189 "t+" => :label_in,
190 "t" => :label_today,
190 "t" => :label_today,
191 "ld" => :label_yesterday,
191 "ld" => :label_yesterday,
192 "w" => :label_this_week,
192 "w" => :label_this_week,
193 "lw" => :label_last_week,
193 "lw" => :label_last_week,
194 "l2w" => [:label_last_n_weeks, {:count => 2}],
194 "l2w" => [:label_last_n_weeks, {:count => 2}],
195 "m" => :label_this_month,
195 "m" => :label_this_month,
196 "lm" => :label_last_month,
196 "lm" => :label_last_month,
197 "y" => :label_this_year,
197 "y" => :label_this_year,
198 ">t-" => :label_less_than_ago,
198 ">t-" => :label_less_than_ago,
199 "<t-" => :label_more_than_ago,
199 "<t-" => :label_more_than_ago,
200 "><t-"=> :label_in_the_past_days,
200 "><t-"=> :label_in_the_past_days,
201 "t-" => :label_ago,
201 "t-" => :label_ago,
202 "~" => :label_contains,
202 "~" => :label_contains,
203 "!~" => :label_not_contains,
203 "!~" => :label_not_contains,
204 "=p" => :label_any_issues_in_project,
204 "=p" => :label_any_issues_in_project,
205 "=!p" => :label_any_issues_not_in_project,
205 "=!p" => :label_any_issues_not_in_project,
206 "!p" => :label_no_issues_in_project,
206 "!p" => :label_no_issues_in_project,
207 "*o" => :label_any_open_issues,
207 "*o" => :label_any_open_issues,
208 "!o" => :label_no_open_issues
208 "!o" => :label_no_open_issues
209 }
209 }
210
210
211 class_attribute :operators_by_filter_type
211 class_attribute :operators_by_filter_type
212 self.operators_by_filter_type = {
212 self.operators_by_filter_type = {
213 :list => [ "=", "!" ],
213 :list => [ "=", "!" ],
214 :list_status => [ "o", "=", "!", "c", "*" ],
214 :list_status => [ "o", "=", "!", "c", "*" ],
215 :list_optional => [ "=", "!", "!*", "*" ],
215 :list_optional => [ "=", "!", "!*", "*" ],
216 :list_subprojects => [ "*", "!*", "=" ],
216 :list_subprojects => [ "*", "!*", "=" ],
217 :date => [ "=", ">=", "<=", "><", "<t+", ">t+", "><t+", "t+", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", ">t-", "<t-", "><t-", "t-", "!*", "*" ],
217 :date => [ "=", ">=", "<=", "><", "<t+", ">t+", "><t+", "t+", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", ">t-", "<t-", "><t-", "t-", "!*", "*" ],
218 :date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "><t-", "t-", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", "!*", "*" ],
218 :date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "><t-", "t-", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", "!*", "*" ],
219 :string => [ "=", "~", "!", "!~", "!*", "*" ],
219 :string => [ "=", "~", "!", "!~", "!*", "*" ],
220 :text => [ "~", "!~", "!*", "*" ],
220 :text => [ "~", "!~", "!*", "*" ],
221 :integer => [ "=", ">=", "<=", "><", "!*", "*" ],
221 :integer => [ "=", ">=", "<=", "><", "!*", "*" ],
222 :float => [ "=", ">=", "<=", "><", "!*", "*" ],
222 :float => [ "=", ">=", "<=", "><", "!*", "*" ],
223 :relation => ["=", "=p", "=!p", "!p", "*o", "!o", "!*", "*"],
223 :relation => ["=", "=p", "=!p", "!p", "*o", "!o", "!*", "*"],
224 :tree => ["=", "~", "!*", "*"]
224 :tree => ["=", "~", "!*", "*"]
225 }
225 }
226
226
227 class_attribute :available_columns
227 class_attribute :available_columns
228 self.available_columns = []
228 self.available_columns = []
229
229
230 class_attribute :queried_class
230 class_attribute :queried_class
231
231
232 def queried_table_name
232 def queried_table_name
233 @queried_table_name ||= self.class.queried_class.table_name
233 @queried_table_name ||= self.class.queried_class.table_name
234 end
234 end
235
235
236 def initialize(attributes=nil, *args)
236 def initialize(attributes=nil, *args)
237 super attributes
237 super attributes
238 @is_for_all = project.nil?
238 @is_for_all = project.nil?
239 end
239 end
240
240
241 # Builds the query from the given params
241 # Builds the query from the given params
242 def build_from_params(params)
242 def build_from_params(params)
243 if params[:fields] || params[:f]
243 if params[:fields] || params[:f]
244 self.filters = {}
244 self.filters = {}
245 add_filters(params[:fields] || params[:f], params[:operators] || params[:op], params[:values] || params[:v])
245 add_filters(params[:fields] || params[:f], params[:operators] || params[:op], params[:values] || params[:v])
246 else
246 else
247 available_filters.keys.each do |field|
247 available_filters.keys.each do |field|
248 add_short_filter(field, params[field]) if params[field]
248 add_short_filter(field, params[field]) if params[field]
249 end
249 end
250 end
250 end
251 self.group_by = params[:group_by] || (params[:query] && params[:query][:group_by])
251 self.group_by = params[:group_by] || (params[:query] && params[:query][:group_by])
252 self.column_names = params[:c] || (params[:query] && params[:query][:column_names])
252 self.column_names = params[:c] || (params[:query] && params[:query][:column_names])
253 self.totalable_names = params[:t] || (params[:query] && params[:query][:totalable_names])
253 self.totalable_names = params[:t] || (params[:query] && params[:query][:totalable_names])
254 self
254 self
255 end
255 end
256
256
257 # Builds a new query from the given params and attributes
257 # Builds a new query from the given params and attributes
258 def self.build_from_params(params, attributes={})
258 def self.build_from_params(params, attributes={})
259 new(attributes).build_from_params(params)
259 new(attributes).build_from_params(params)
260 end
260 end
261
261
262 def validate_query_filters
262 def validate_query_filters
263 filters.each_key do |field|
263 filters.each_key do |field|
264 if values_for(field)
264 if values_for(field)
265 case type_for(field)
265 case type_for(field)
266 when :integer
266 when :integer
267 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/\A[+-]?\d+(,[+-]?\d+)*\z/) }
267 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/\A[+-]?\d+(,[+-]?\d+)*\z/) }
268 when :float
268 when :float
269 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/\A[+-]?\d+(\.\d*)?\z/) }
269 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/\A[+-]?\d+(\.\d*)?\z/) }
270 when :date, :date_past
270 when :date, :date_past
271 case operator_for(field)
271 case operator_for(field)
272 when "=", ">=", "<=", "><"
272 when "=", ">=", "<=", "><"
273 add_filter_error(field, :invalid) if values_for(field).detect {|v|
273 add_filter_error(field, :invalid) if values_for(field).detect {|v|
274 v.present? && (!v.match(/\A\d{4}-\d{2}-\d{2}(T\d{2}((:)?\d{2}){0,2}(Z|\d{2}:?\d{2})?)?\z/) || parse_date(v).nil?)
274 v.present? && (!v.match(/\A\d{4}-\d{2}-\d{2}(T\d{2}((:)?\d{2}){0,2}(Z|\d{2}:?\d{2})?)?\z/) || parse_date(v).nil?)
275 }
275 }
276 when ">t-", "<t-", "t-", ">t+", "<t+", "t+", "><t+", "><t-"
276 when ">t-", "<t-", "t-", ">t+", "<t+", "t+", "><t+", "><t-"
277 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^\d+$/) }
277 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^\d+$/) }
278 end
278 end
279 end
279 end
280 end
280 end
281
281
282 add_filter_error(field, :blank) unless
282 add_filter_error(field, :blank) unless
283 # filter requires one or more values
283 # filter requires one or more values
284 (values_for(field) and !values_for(field).first.blank?) or
284 (values_for(field) and !values_for(field).first.blank?) or
285 # filter doesn't require any value
285 # filter doesn't require any value
286 ["o", "c", "!*", "*", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", "*o", "!o"].include? operator_for(field)
286 ["o", "c", "!*", "*", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", "*o", "!o"].include? operator_for(field)
287 end if filters
287 end if filters
288 end
288 end
289
289
290 def add_filter_error(field, message)
290 def add_filter_error(field, message)
291 m = label_for(field) + " " + l(message, :scope => 'activerecord.errors.messages')
291 m = label_for(field) + " " + l(message, :scope => 'activerecord.errors.messages')
292 errors.add(:base, m)
292 errors.add(:base, m)
293 end
293 end
294
294
295 def editable_by?(user)
295 def editable_by?(user)
296 return false unless user
296 return false unless user
297 # Admin can edit them all and regular users can edit their private queries
297 # Admin can edit them all and regular users can edit their private queries
298 return true if user.admin? || (is_private? && self.user_id == user.id)
298 return true if user.admin? || (is_private? && self.user_id == user.id)
299 # Members can not edit public queries that are for all project (only admin is allowed to)
299 # Members can not edit public queries that are for all project (only admin is allowed to)
300 is_public? && !@is_for_all && user.allowed_to?(:manage_public_queries, project)
300 is_public? && !@is_for_all && user.allowed_to?(:manage_public_queries, project)
301 end
301 end
302
302
303 def trackers
303 def trackers
304 @trackers ||= (project.nil? ? Tracker.all : project.rolled_up_trackers).visible.sorted
304 @trackers ||= (project.nil? ? Tracker.all : project.rolled_up_trackers).visible.sorted
305 end
305 end
306
306
307 # Returns a hash of localized labels for all filter operators
307 # Returns a hash of localized labels for all filter operators
308 def self.operators_labels
308 def self.operators_labels
309 operators.inject({}) {|h, operator| h[operator.first] = l(*operator.last); h}
309 operators.inject({}) {|h, operator| h[operator.first] = l(*operator.last); h}
310 end
310 end
311
311
312 # Returns a representation of the available filters for JSON serialization
312 # Returns a representation of the available filters for JSON serialization
313 def available_filters_as_json
313 def available_filters_as_json
314 json = {}
314 json = {}
315 available_filters.each do |field, options|
315 available_filters.each do |field, options|
316 options = options.slice(:type, :name, :values)
316 options = options.slice(:type, :name, :values)
317 if options[:values] && values_for(field)
317 if options[:values] && values_for(field)
318 missing = Array(values_for(field)).select(&:present?) - options[:values].map(&:last)
318 missing = Array(values_for(field)).select(&:present?) - options[:values].map(&:last)
319 if missing.any? && respond_to?(method = "find_#{field}_filter_values")
319 if missing.any? && respond_to?(method = "find_#{field}_filter_values")
320 options[:values] += send(method, missing)
320 options[:values] += send(method, missing)
321 end
321 end
322 end
322 end
323 json[field] = options.stringify_keys
323 json[field] = options.stringify_keys
324 end
324 end
325 json
325 json
326 end
326 end
327
327
328 def all_projects
328 def all_projects
329 @all_projects ||= Project.visible.to_a
329 @all_projects ||= Project.visible.to_a
330 end
330 end
331
331
332 def all_projects_values
332 def all_projects_values
333 return @all_projects_values if @all_projects_values
333 return @all_projects_values if @all_projects_values
334
334
335 values = []
335 values = []
336 Project.project_tree(all_projects) do |p, level|
336 Project.project_tree(all_projects) do |p, level|
337 prefix = (level > 0 ? ('--' * level + ' ') : '')
337 prefix = (level > 0 ? ('--' * level + ' ') : '')
338 values << ["#{prefix}#{p.name}", p.id.to_s]
338 values << ["#{prefix}#{p.name}", p.id.to_s]
339 end
339 end
340 @all_projects_values = values
340 @all_projects_values = values
341 end
341 end
342
342
343 # Adds available filters
343 # Adds available filters
344 def initialize_available_filters
344 def initialize_available_filters
345 # implemented by sub-classes
345 # implemented by sub-classes
346 end
346 end
347 protected :initialize_available_filters
347 protected :initialize_available_filters
348
348
349 # Adds an available filter
349 # Adds an available filter
350 def add_available_filter(field, options)
350 def add_available_filter(field, options)
351 @available_filters ||= ActiveSupport::OrderedHash.new
351 @available_filters ||= ActiveSupport::OrderedHash.new
352 @available_filters[field] = options
352 @available_filters[field] = options
353 @available_filters
353 @available_filters
354 end
354 end
355
355
356 # Removes an available filter
356 # Removes an available filter
357 def delete_available_filter(field)
357 def delete_available_filter(field)
358 if @available_filters
358 if @available_filters
359 @available_filters.delete(field)
359 @available_filters.delete(field)
360 end
360 end
361 end
361 end
362
362
363 # Return a hash of available filters
363 # Return a hash of available filters
364 def available_filters
364 def available_filters
365 unless @available_filters
365 unless @available_filters
366 initialize_available_filters
366 initialize_available_filters
367 @available_filters.each do |field, options|
367 @available_filters.each do |field, options|
368 options[:name] ||= l(options[:label] || "field_#{field}".gsub(/_id$/, ''))
368 options[:name] ||= l(options[:label] || "field_#{field}".gsub(/_id$/, ''))
369 end
369 end
370 end
370 end
371 @available_filters
371 @available_filters
372 end
372 end
373
373
374 def add_filter(field, operator, values=nil)
374 def add_filter(field, operator, values=nil)
375 # values must be an array
375 # values must be an array
376 return unless values.nil? || values.is_a?(Array)
376 return unless values.nil? || values.is_a?(Array)
377 # check if field is defined as an available filter
377 # check if field is defined as an available filter
378 if available_filters.has_key? field
378 if available_filters.has_key? field
379 filter_options = available_filters[field]
379 filter_options = available_filters[field]
380 filters[field] = {:operator => operator, :values => (values || [''])}
380 filters[field] = {:operator => operator, :values => (values || [''])}
381 end
381 end
382 end
382 end
383
383
384 def add_short_filter(field, expression)
384 def add_short_filter(field, expression)
385 return unless expression && available_filters.has_key?(field)
385 return unless expression && available_filters.has_key?(field)
386 field_type = available_filters[field][:type]
386 field_type = available_filters[field][:type]
387 operators_by_filter_type[field_type].sort.reverse.detect do |operator|
387 operators_by_filter_type[field_type].sort.reverse.detect do |operator|
388 next unless expression =~ /^#{Regexp.escape(operator)}(.*)$/
388 next unless expression =~ /^#{Regexp.escape(operator)}(.*)$/
389 values = $1
389 values = $1
390 add_filter field, operator, values.present? ? values.split('|') : ['']
390 add_filter field, operator, values.present? ? values.split('|') : ['']
391 end || add_filter(field, '=', expression.split('|'))
391 end || add_filter(field, '=', expression.split('|'))
392 end
392 end
393
393
394 # Add multiple filters using +add_filter+
394 # Add multiple filters using +add_filter+
395 def add_filters(fields, operators, values)
395 def add_filters(fields, operators, values)
396 if fields.is_a?(Array) && operators.is_a?(Hash) && (values.nil? || values.is_a?(Hash))
396 if fields.is_a?(Array) && operators.is_a?(Hash) && (values.nil? || values.is_a?(Hash))
397 fields.each do |field|
397 fields.each do |field|
398 add_filter(field, operators[field], values && values[field])
398 add_filter(field, operators[field], values && values[field])
399 end
399 end
400 end
400 end
401 end
401 end
402
402
403 def has_filter?(field)
403 def has_filter?(field)
404 filters and filters[field]
404 filters and filters[field]
405 end
405 end
406
406
407 def type_for(field)
407 def type_for(field)
408 available_filters[field][:type] if available_filters.has_key?(field)
408 available_filters[field][:type] if available_filters.has_key?(field)
409 end
409 end
410
410
411 def operator_for(field)
411 def operator_for(field)
412 has_filter?(field) ? filters[field][:operator] : nil
412 has_filter?(field) ? filters[field][:operator] : nil
413 end
413 end
414
414
415 def values_for(field)
415 def values_for(field)
416 has_filter?(field) ? filters[field][:values] : nil
416 has_filter?(field) ? filters[field][:values] : nil
417 end
417 end
418
418
419 def value_for(field, index=0)
419 def value_for(field, index=0)
420 (values_for(field) || [])[index]
420 (values_for(field) || [])[index]
421 end
421 end
422
422
423 def label_for(field)
423 def label_for(field)
424 label = available_filters[field][:name] if available_filters.has_key?(field)
424 label = available_filters[field][:name] if available_filters.has_key?(field)
425 label ||= queried_class.human_attribute_name(field, :default => field)
425 label ||= queried_class.human_attribute_name(field, :default => field)
426 end
426 end
427
427
428 def self.add_available_column(column)
428 def self.add_available_column(column)
429 self.available_columns << (column) if column.is_a?(QueryColumn)
429 self.available_columns << (column) if column.is_a?(QueryColumn)
430 end
430 end
431
431
432 # Returns an array of columns that can be used to group the results
432 # Returns an array of columns that can be used to group the results
433 def groupable_columns
433 def groupable_columns
434 available_columns.select {|c| c.groupable}
434 available_columns.select {|c| c.groupable}
435 end
435 end
436
436
437 # Returns a Hash of columns and the key for sorting
437 # Returns a Hash of columns and the key for sorting
438 def sortable_columns
438 def sortable_columns
439 available_columns.inject({}) {|h, column|
439 available_columns.inject({}) {|h, column|
440 h[column.name.to_s] = column.sortable
440 h[column.name.to_s] = column.sortable
441 h
441 h
442 }
442 }
443 end
443 end
444
444
445 def columns
445 def columns
446 # preserve the column_names order
446 # preserve the column_names order
447 cols = (has_default_columns? ? default_columns_names : column_names).collect do |name|
447 cols = (has_default_columns? ? default_columns_names : column_names).collect do |name|
448 available_columns.find { |col| col.name == name }
448 available_columns.find { |col| col.name == name }
449 end.compact
449 end.compact
450 available_columns.select(&:frozen?) | cols
450 available_columns.select(&:frozen?) | cols
451 end
451 end
452
452
453 def inline_columns
453 def inline_columns
454 columns.select(&:inline?)
454 columns.select(&:inline?)
455 end
455 end
456
456
457 def block_columns
457 def block_columns
458 columns.reject(&:inline?)
458 columns.reject(&:inline?)
459 end
459 end
460
460
461 def available_inline_columns
461 def available_inline_columns
462 available_columns.select(&:inline?)
462 available_columns.select(&:inline?)
463 end
463 end
464
464
465 def available_block_columns
465 def available_block_columns
466 available_columns.reject(&:inline?)
466 available_columns.reject(&:inline?)
467 end
467 end
468
468
469 def available_totalable_columns
469 def available_totalable_columns
470 available_columns.select(&:totalable)
470 available_columns.select(&:totalable)
471 end
471 end
472
472
473 def default_columns_names
473 def default_columns_names
474 []
474 []
475 end
475 end
476
476
477 def column_names=(names)
477 def column_names=(names)
478 if names
478 if names
479 names = names.select {|n| n.is_a?(Symbol) || !n.blank? }
479 names = names.select {|n| n.is_a?(Symbol) || !n.blank? }
480 names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }
480 names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }
481 # Set column_names to nil if default columns
481 # Set column_names to nil if default columns
482 if names == default_columns_names
482 if names == default_columns_names
483 names = nil
483 names = nil
484 end
484 end
485 end
485 end
486 write_attribute(:column_names, names)
486 write_attribute(:column_names, names)
487 end
487 end
488
488
489 def has_column?(column)
489 def has_column?(column)
490 column_names && column_names.include?(column.is_a?(QueryColumn) ? column.name : column)
490 column_names && column_names.include?(column.is_a?(QueryColumn) ? column.name : column)
491 end
491 end
492
492
493 def has_custom_field_column?
493 def has_custom_field_column?
494 columns.any? {|column| column.is_a? QueryCustomFieldColumn}
494 columns.any? {|column| column.is_a? QueryCustomFieldColumn}
495 end
495 end
496
496
497 def has_default_columns?
497 def has_default_columns?
498 column_names.nil? || column_names.empty?
498 column_names.nil? || column_names.empty?
499 end
499 end
500
500
501 def totalable_columns
501 def totalable_columns
502 names = totalable_names
502 names = totalable_names
503 available_totalable_columns.select {|column| names.include?(column.name)}
503 available_totalable_columns.select {|column| names.include?(column.name)}
504 end
504 end
505
505
506 def totalable_names=(names)
506 def totalable_names=(names)
507 if names
507 if names
508 names = names.select(&:present?).map {|n| n.is_a?(Symbol) ? n : n.to_sym}
508 names = names.select(&:present?).map {|n| n.is_a?(Symbol) ? n : n.to_sym}
509 end
509 end
510 options[:totalable_names] = names
510 options[:totalable_names] = names
511 end
511 end
512
512
513 def totalable_names
513 def totalable_names
514 options[:totalable_names] || Setting.issue_list_default_totals.map(&:to_sym) || []
514 options[:totalable_names] || Setting.issue_list_default_totals.map(&:to_sym) || []
515 end
515 end
516
516
517 def sort_criteria=(arg)
517 def sort_criteria=(arg)
518 c = []
518 c = []
519 if arg.is_a?(Hash)
519 if arg.is_a?(Hash)
520 arg = arg.keys.sort.collect {|k| arg[k]}
520 arg = arg.keys.sort.collect {|k| arg[k]}
521 end
521 end
522 if arg
522 if arg
523 c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, (o == 'desc' || o == false) ? 'desc' : 'asc']}
523 c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, (o == 'desc' || o == false) ? 'desc' : 'asc']}
524 end
524 end
525 write_attribute(:sort_criteria, c)
525 write_attribute(:sort_criteria, c)
526 end
526 end
527
527
528 def sort_criteria
528 def sort_criteria
529 read_attribute(:sort_criteria) || []
529 read_attribute(:sort_criteria) || []
530 end
530 end
531
531
532 def sort_criteria_key(arg)
532 def sort_criteria_key(arg)
533 sort_criteria && sort_criteria[arg] && sort_criteria[arg].first
533 sort_criteria && sort_criteria[arg] && sort_criteria[arg].first
534 end
534 end
535
535
536 def sort_criteria_order(arg)
536 def sort_criteria_order(arg)
537 sort_criteria && sort_criteria[arg] && sort_criteria[arg].last
537 sort_criteria && sort_criteria[arg] && sort_criteria[arg].last
538 end
538 end
539
539
540 def sort_criteria_order_for(key)
540 def sort_criteria_order_for(key)
541 sort_criteria.detect {|k, order| key.to_s == k}.try(:last)
541 sort_criteria.detect {|k, order| key.to_s == k}.try(:last)
542 end
542 end
543
543
544 # Returns the SQL sort order that should be prepended for grouping
544 # Returns the SQL sort order that should be prepended for grouping
545 def group_by_sort_order
545 def group_by_sort_order
546 if column = group_by_column
546 if column = group_by_column
547 order = (sort_criteria_order_for(column.name) || column.default_order).try(:upcase)
547 order = (sort_criteria_order_for(column.name) || column.default_order || 'asc').try(:upcase)
548 Array(column.sortable).map {|s| "#{s} #{order}"}
548 Array(column.sortable).map {|s| "#{s} #{order}"}
549 end
549 end
550 end
550 end
551
551
552 # Returns true if the query is a grouped query
552 # Returns true if the query is a grouped query
553 def grouped?
553 def grouped?
554 !group_by_column.nil?
554 !group_by_column.nil?
555 end
555 end
556
556
557 def group_by_column
557 def group_by_column
558 groupable_columns.detect {|c| c.groupable && c.name.to_s == group_by}
558 groupable_columns.detect {|c| c.groupable && c.name.to_s == group_by}
559 end
559 end
560
560
561 def group_by_statement
561 def group_by_statement
562 group_by_column.try(:groupable)
562 group_by_column.try(:groupable)
563 end
563 end
564
564
565 def project_statement
565 def project_statement
566 project_clauses = []
566 project_clauses = []
567 if project && !project.descendants.active.empty?
567 if project && !project.descendants.active.empty?
568 if has_filter?("subproject_id")
568 if has_filter?("subproject_id")
569 case operator_for("subproject_id")
569 case operator_for("subproject_id")
570 when '='
570 when '='
571 # include the selected subprojects
571 # include the selected subprojects
572 ids = [project.id] + values_for("subproject_id").each(&:to_i)
572 ids = [project.id] + values_for("subproject_id").each(&:to_i)
573 project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
573 project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
574 when '!*'
574 when '!*'
575 # main project only
575 # main project only
576 project_clauses << "#{Project.table_name}.id = %d" % project.id
576 project_clauses << "#{Project.table_name}.id = %d" % project.id
577 else
577 else
578 # all subprojects
578 # all subprojects
579 project_clauses << "#{Project.table_name}.lft >= #{project.lft} AND #{Project.table_name}.rgt <= #{project.rgt}"
579 project_clauses << "#{Project.table_name}.lft >= #{project.lft} AND #{Project.table_name}.rgt <= #{project.rgt}"
580 end
580 end
581 elsif Setting.display_subprojects_issues?
581 elsif Setting.display_subprojects_issues?
582 project_clauses << "#{Project.table_name}.lft >= #{project.lft} AND #{Project.table_name}.rgt <= #{project.rgt}"
582 project_clauses << "#{Project.table_name}.lft >= #{project.lft} AND #{Project.table_name}.rgt <= #{project.rgt}"
583 else
583 else
584 project_clauses << "#{Project.table_name}.id = %d" % project.id
584 project_clauses << "#{Project.table_name}.id = %d" % project.id
585 end
585 end
586 elsif project
586 elsif project
587 project_clauses << "#{Project.table_name}.id = %d" % project.id
587 project_clauses << "#{Project.table_name}.id = %d" % project.id
588 end
588 end
589 project_clauses.any? ? project_clauses.join(' AND ') : nil
589 project_clauses.any? ? project_clauses.join(' AND ') : nil
590 end
590 end
591
591
592 def statement
592 def statement
593 # filters clauses
593 # filters clauses
594 filters_clauses = []
594 filters_clauses = []
595 filters.each_key do |field|
595 filters.each_key do |field|
596 next if field == "subproject_id"
596 next if field == "subproject_id"
597 v = values_for(field).clone
597 v = values_for(field).clone
598 next unless v and !v.empty?
598 next unless v and !v.empty?
599 operator = operator_for(field)
599 operator = operator_for(field)
600
600
601 # "me" value substitution
601 # "me" value substitution
602 if %w(assigned_to_id author_id user_id watcher_id).include?(field)
602 if %w(assigned_to_id author_id user_id watcher_id).include?(field)
603 if v.delete("me")
603 if v.delete("me")
604 if User.current.logged?
604 if User.current.logged?
605 v.push(User.current.id.to_s)
605 v.push(User.current.id.to_s)
606 v += User.current.group_ids.map(&:to_s) if field == 'assigned_to_id'
606 v += User.current.group_ids.map(&:to_s) if field == 'assigned_to_id'
607 else
607 else
608 v.push("0")
608 v.push("0")
609 end
609 end
610 end
610 end
611 end
611 end
612
612
613 if field == 'project_id'
613 if field == 'project_id'
614 if v.delete('mine')
614 if v.delete('mine')
615 v += User.current.memberships.map(&:project_id).map(&:to_s)
615 v += User.current.memberships.map(&:project_id).map(&:to_s)
616 end
616 end
617 end
617 end
618
618
619 if field =~ /cf_(\d+)$/
619 if field =~ /cf_(\d+)$/
620 # custom field
620 # custom field
621 filters_clauses << sql_for_custom_field(field, operator, v, $1)
621 filters_clauses << sql_for_custom_field(field, operator, v, $1)
622 elsif respond_to?("sql_for_#{field}_field")
622 elsif respond_to?("sql_for_#{field}_field")
623 # specific statement
623 # specific statement
624 filters_clauses << send("sql_for_#{field}_field", field, operator, v)
624 filters_clauses << send("sql_for_#{field}_field", field, operator, v)
625 else
625 else
626 # regular field
626 # regular field
627 filters_clauses << '(' + sql_for_field(field, operator, v, queried_table_name, field) + ')'
627 filters_clauses << '(' + sql_for_field(field, operator, v, queried_table_name, field) + ')'
628 end
628 end
629 end if filters and valid?
629 end if filters and valid?
630
630
631 if (c = group_by_column) && c.is_a?(QueryCustomFieldColumn)
631 if (c = group_by_column) && c.is_a?(QueryCustomFieldColumn)
632 # Excludes results for which the grouped custom field is not visible
632 # Excludes results for which the grouped custom field is not visible
633 filters_clauses << c.custom_field.visibility_by_project_condition
633 filters_clauses << c.custom_field.visibility_by_project_condition
634 end
634 end
635
635
636 filters_clauses << project_statement
636 filters_clauses << project_statement
637 filters_clauses.reject!(&:blank?)
637 filters_clauses.reject!(&:blank?)
638
638
639 filters_clauses.any? ? filters_clauses.join(' AND ') : nil
639 filters_clauses.any? ? filters_clauses.join(' AND ') : nil
640 end
640 end
641
641
642 # Returns the sum of values for the given column
642 # Returns the sum of values for the given column
643 def total_for(column)
643 def total_for(column)
644 total_with_scope(column, base_scope)
644 total_with_scope(column, base_scope)
645 end
645 end
646
646
647 # Returns a hash of the sum of the given column for each group,
647 # Returns a hash of the sum of the given column for each group,
648 # or nil if the query is not grouped
648 # or nil if the query is not grouped
649 def total_by_group_for(column)
649 def total_by_group_for(column)
650 grouped_query do |scope|
650 grouped_query do |scope|
651 total_with_scope(column, scope)
651 total_with_scope(column, scope)
652 end
652 end
653 end
653 end
654
654
655 def totals
655 def totals
656 totals = totalable_columns.map {|column| [column, total_for(column)]}
656 totals = totalable_columns.map {|column| [column, total_for(column)]}
657 yield totals if block_given?
657 yield totals if block_given?
658 totals
658 totals
659 end
659 end
660
660
661 def totals_by_group
661 def totals_by_group
662 totals = totalable_columns.map {|column| [column, total_by_group_for(column)]}
662 totals = totalable_columns.map {|column| [column, total_by_group_for(column)]}
663 yield totals if block_given?
663 yield totals if block_given?
664 totals
664 totals
665 end
665 end
666
666
667 private
667 private
668
668
669 def grouped_query(&block)
669 def grouped_query(&block)
670 r = nil
670 r = nil
671 if grouped?
671 if grouped?
672 begin
672 begin
673 # Rails3 will raise an (unexpected) RecordNotFound if there's only a nil group value
673 # Rails3 will raise an (unexpected) RecordNotFound if there's only a nil group value
674 r = yield base_group_scope
674 r = yield base_group_scope
675 rescue ActiveRecord::RecordNotFound
675 rescue ActiveRecord::RecordNotFound
676 r = {nil => yield(base_scope)}
676 r = {nil => yield(base_scope)}
677 end
677 end
678 c = group_by_column
678 c = group_by_column
679 if c.is_a?(QueryCustomFieldColumn)
679 if c.is_a?(QueryCustomFieldColumn)
680 r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
680 r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
681 end
681 end
682 end
682 end
683 r
683 r
684 rescue ::ActiveRecord::StatementInvalid => e
684 rescue ::ActiveRecord::StatementInvalid => e
685 raise StatementInvalid.new(e.message)
685 raise StatementInvalid.new(e.message)
686 end
686 end
687
687
688 def total_with_scope(column, scope)
688 def total_with_scope(column, scope)
689 unless column.is_a?(QueryColumn)
689 unless column.is_a?(QueryColumn)
690 column = column.to_sym
690 column = column.to_sym
691 column = available_totalable_columns.detect {|c| c.name == column}
691 column = available_totalable_columns.detect {|c| c.name == column}
692 end
692 end
693 if column.is_a?(QueryCustomFieldColumn)
693 if column.is_a?(QueryCustomFieldColumn)
694 custom_field = column.custom_field
694 custom_field = column.custom_field
695 send "total_for_custom_field", custom_field, scope
695 send "total_for_custom_field", custom_field, scope
696 else
696 else
697 send "total_for_#{column.name}", scope
697 send "total_for_#{column.name}", scope
698 end
698 end
699 rescue ::ActiveRecord::StatementInvalid => e
699 rescue ::ActiveRecord::StatementInvalid => e
700 raise StatementInvalid.new(e.message)
700 raise StatementInvalid.new(e.message)
701 end
701 end
702
702
703 def base_scope
703 def base_scope
704 raise "unimplemented"
704 raise "unimplemented"
705 end
705 end
706
706
707 def base_group_scope
707 def base_group_scope
708 base_scope.
708 base_scope.
709 joins(joins_for_order_statement(group_by_statement)).
709 joins(joins_for_order_statement(group_by_statement)).
710 group(group_by_statement)
710 group(group_by_statement)
711 end
711 end
712
712
713 def total_for_custom_field(custom_field, scope, &block)
713 def total_for_custom_field(custom_field, scope, &block)
714 total = custom_field.format.total_for_scope(custom_field, scope)
714 total = custom_field.format.total_for_scope(custom_field, scope)
715 total = map_total(total) {|t| custom_field.format.cast_total_value(custom_field, t)}
715 total = map_total(total) {|t| custom_field.format.cast_total_value(custom_field, t)}
716 total
716 total
717 end
717 end
718
718
719 def map_total(total, &block)
719 def map_total(total, &block)
720 if total.is_a?(Hash)
720 if total.is_a?(Hash)
721 total.keys.each {|k| total[k] = yield total[k]}
721 total.keys.each {|k| total[k] = yield total[k]}
722 else
722 else
723 total = yield total
723 total = yield total
724 end
724 end
725 total
725 total
726 end
726 end
727
727
728 def sql_for_custom_field(field, operator, value, custom_field_id)
728 def sql_for_custom_field(field, operator, value, custom_field_id)
729 db_table = CustomValue.table_name
729 db_table = CustomValue.table_name
730 db_field = 'value'
730 db_field = 'value'
731 filter = @available_filters[field]
731 filter = @available_filters[field]
732 return nil unless filter
732 return nil unless filter
733 if filter[:field].format.target_class && filter[:field].format.target_class <= User
733 if filter[:field].format.target_class && filter[:field].format.target_class <= User
734 if value.delete('me')
734 if value.delete('me')
735 value.push User.current.id.to_s
735 value.push User.current.id.to_s
736 end
736 end
737 end
737 end
738 not_in = nil
738 not_in = nil
739 if operator == '!'
739 if operator == '!'
740 # Makes ! operator work for custom fields with multiple values
740 # Makes ! operator work for custom fields with multiple values
741 operator = '='
741 operator = '='
742 not_in = 'NOT'
742 not_in = 'NOT'
743 end
743 end
744 customized_key = "id"
744 customized_key = "id"
745 customized_class = queried_class
745 customized_class = queried_class
746 if field =~ /^(.+)\.cf_/
746 if field =~ /^(.+)\.cf_/
747 assoc = $1
747 assoc = $1
748 customized_key = "#{assoc}_id"
748 customized_key = "#{assoc}_id"
749 customized_class = queried_class.reflect_on_association(assoc.to_sym).klass.base_class rescue nil
749 customized_class = queried_class.reflect_on_association(assoc.to_sym).klass.base_class rescue nil
750 raise "Unknown #{queried_class.name} association #{assoc}" unless customized_class
750 raise "Unknown #{queried_class.name} association #{assoc}" unless customized_class
751 end
751 end
752 where = sql_for_field(field, operator, value, db_table, db_field, true)
752 where = sql_for_field(field, operator, value, db_table, db_field, true)
753 if operator =~ /[<>]/
753 if operator =~ /[<>]/
754 where = "(#{where}) AND #{db_table}.#{db_field} <> ''"
754 where = "(#{where}) AND #{db_table}.#{db_field} <> ''"
755 end
755 end
756 "#{queried_table_name}.#{customized_key} #{not_in} IN (" +
756 "#{queried_table_name}.#{customized_key} #{not_in} IN (" +
757 "SELECT #{customized_class.table_name}.id FROM #{customized_class.table_name}" +
757 "SELECT #{customized_class.table_name}.id FROM #{customized_class.table_name}" +
758 " LEFT OUTER JOIN #{db_table} ON #{db_table}.customized_type='#{customized_class}' AND #{db_table}.customized_id=#{customized_class.table_name}.id AND #{db_table}.custom_field_id=#{custom_field_id}" +
758 " LEFT OUTER JOIN #{db_table} ON #{db_table}.customized_type='#{customized_class}' AND #{db_table}.customized_id=#{customized_class.table_name}.id AND #{db_table}.custom_field_id=#{custom_field_id}" +
759 " WHERE (#{where}) AND (#{filter[:field].visibility_by_project_condition}))"
759 " WHERE (#{where}) AND (#{filter[:field].visibility_by_project_condition}))"
760 end
760 end
761
761
762 # Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+
762 # Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+
763 def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)
763 def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)
764 sql = ''
764 sql = ''
765 case operator
765 case operator
766 when "="
766 when "="
767 if value.any?
767 if value.any?
768 case type_for(field)
768 case type_for(field)
769 when :date, :date_past
769 when :date, :date_past
770 sql = date_clause(db_table, db_field, parse_date(value.first), parse_date(value.first), is_custom_filter)
770 sql = date_clause(db_table, db_field, parse_date(value.first), parse_date(value.first), is_custom_filter)
771 when :integer
771 when :integer
772 int_values = value.first.to_s.scan(/[+-]?\d+/).map(&:to_i).join(",")
772 int_values = value.first.to_s.scan(/[+-]?\d+/).map(&:to_i).join(",")
773 if int_values.present?
773 if int_values.present?
774 if is_custom_filter
774 if is_custom_filter
775 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) IN (#{int_values}))"
775 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) IN (#{int_values}))"
776 else
776 else
777 sql = "#{db_table}.#{db_field} IN (#{int_values})"
777 sql = "#{db_table}.#{db_field} IN (#{int_values})"
778 end
778 end
779 else
779 else
780 sql = "1=0"
780 sql = "1=0"
781 end
781 end
782 when :float
782 when :float
783 if is_custom_filter
783 if is_custom_filter
784 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5})"
784 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5})"
785 else
785 else
786 sql = "#{db_table}.#{db_field} BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5}"
786 sql = "#{db_table}.#{db_field} BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5}"
787 end
787 end
788 else
788 else
789 sql = queried_class.send(:sanitize_sql_for_conditions, ["#{db_table}.#{db_field} IN (?)", value])
789 sql = queried_class.send(:sanitize_sql_for_conditions, ["#{db_table}.#{db_field} IN (?)", value])
790 end
790 end
791 else
791 else
792 # IN an empty set
792 # IN an empty set
793 sql = "1=0"
793 sql = "1=0"
794 end
794 end
795 when "!"
795 when "!"
796 if value.any?
796 if value.any?
797 sql = queried_class.send(:sanitize_sql_for_conditions, ["(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (?))", value])
797 sql = queried_class.send(:sanitize_sql_for_conditions, ["(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (?))", value])
798 else
798 else
799 # NOT IN an empty set
799 # NOT IN an empty set
800 sql = "1=1"
800 sql = "1=1"
801 end
801 end
802 when "!*"
802 when "!*"
803 sql = "#{db_table}.#{db_field} IS NULL"
803 sql = "#{db_table}.#{db_field} IS NULL"
804 sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter
804 sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter
805 when "*"
805 when "*"
806 sql = "#{db_table}.#{db_field} IS NOT NULL"
806 sql = "#{db_table}.#{db_field} IS NOT NULL"
807 sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
807 sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
808 when ">="
808 when ">="
809 if [:date, :date_past].include?(type_for(field))
809 if [:date, :date_past].include?(type_for(field))
810 sql = date_clause(db_table, db_field, parse_date(value.first), nil, is_custom_filter)
810 sql = date_clause(db_table, db_field, parse_date(value.first), nil, is_custom_filter)
811 else
811 else
812 if is_custom_filter
812 if is_custom_filter
813 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) >= #{value.first.to_f})"
813 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) >= #{value.first.to_f})"
814 else
814 else
815 sql = "#{db_table}.#{db_field} >= #{value.first.to_f}"
815 sql = "#{db_table}.#{db_field} >= #{value.first.to_f}"
816 end
816 end
817 end
817 end
818 when "<="
818 when "<="
819 if [:date, :date_past].include?(type_for(field))
819 if [:date, :date_past].include?(type_for(field))
820 sql = date_clause(db_table, db_field, nil, parse_date(value.first), is_custom_filter)
820 sql = date_clause(db_table, db_field, nil, parse_date(value.first), is_custom_filter)
821 else
821 else
822 if is_custom_filter
822 if is_custom_filter
823 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) <= #{value.first.to_f})"
823 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) <= #{value.first.to_f})"
824 else
824 else
825 sql = "#{db_table}.#{db_field} <= #{value.first.to_f}"
825 sql = "#{db_table}.#{db_field} <= #{value.first.to_f}"
826 end
826 end
827 end
827 end
828 when "><"
828 when "><"
829 if [:date, :date_past].include?(type_for(field))
829 if [:date, :date_past].include?(type_for(field))
830 sql = date_clause(db_table, db_field, parse_date(value[0]), parse_date(value[1]), is_custom_filter)
830 sql = date_clause(db_table, db_field, parse_date(value[0]), parse_date(value[1]), is_custom_filter)
831 else
831 else
832 if is_custom_filter
832 if is_custom_filter
833 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) BETWEEN #{value[0].to_f} AND #{value[1].to_f})"
833 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) BETWEEN #{value[0].to_f} AND #{value[1].to_f})"
834 else
834 else
835 sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
835 sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
836 end
836 end
837 end
837 end
838 when "o"
838 when "o"
839 sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{self.class.connection.quoted_false})" if field == "status_id"
839 sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{self.class.connection.quoted_false})" if field == "status_id"
840 when "c"
840 when "c"
841 sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{self.class.connection.quoted_true})" if field == "status_id"
841 sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{self.class.connection.quoted_true})" if field == "status_id"
842 when "><t-"
842 when "><t-"
843 # between today - n days and today
843 # between today - n days and today
844 sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0, is_custom_filter)
844 sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0, is_custom_filter)
845 when ">t-"
845 when ">t-"
846 # >= today - n days
846 # >= today - n days
847 sql = relative_date_clause(db_table, db_field, - value.first.to_i, nil, is_custom_filter)
847 sql = relative_date_clause(db_table, db_field, - value.first.to_i, nil, is_custom_filter)
848 when "<t-"
848 when "<t-"
849 # <= today - n days
849 # <= today - n days
850 sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i, is_custom_filter)
850 sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i, is_custom_filter)
851 when "t-"
851 when "t-"
852 # = n days in past
852 # = n days in past
853 sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i, is_custom_filter)
853 sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i, is_custom_filter)
854 when "><t+"
854 when "><t+"
855 # between today and today + n days
855 # between today and today + n days
856 sql = relative_date_clause(db_table, db_field, 0, value.first.to_i, is_custom_filter)
856 sql = relative_date_clause(db_table, db_field, 0, value.first.to_i, is_custom_filter)
857 when ">t+"
857 when ">t+"
858 # >= today + n days
858 # >= today + n days
859 sql = relative_date_clause(db_table, db_field, value.first.to_i, nil, is_custom_filter)
859 sql = relative_date_clause(db_table, db_field, value.first.to_i, nil, is_custom_filter)
860 when "<t+"
860 when "<t+"
861 # <= today + n days
861 # <= today + n days
862 sql = relative_date_clause(db_table, db_field, nil, value.first.to_i, is_custom_filter)
862 sql = relative_date_clause(db_table, db_field, nil, value.first.to_i, is_custom_filter)
863 when "t+"
863 when "t+"
864 # = today + n days
864 # = today + n days
865 sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i, is_custom_filter)
865 sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i, is_custom_filter)
866 when "t"
866 when "t"
867 # = today
867 # = today
868 sql = relative_date_clause(db_table, db_field, 0, 0, is_custom_filter)
868 sql = relative_date_clause(db_table, db_field, 0, 0, is_custom_filter)
869 when "ld"
869 when "ld"
870 # = yesterday
870 # = yesterday
871 sql = relative_date_clause(db_table, db_field, -1, -1, is_custom_filter)
871 sql = relative_date_clause(db_table, db_field, -1, -1, is_custom_filter)
872 when "w"
872 when "w"
873 # = this week
873 # = this week
874 first_day_of_week = l(:general_first_day_of_week).to_i
874 first_day_of_week = l(:general_first_day_of_week).to_i
875 day_of_week = User.current.today.cwday
875 day_of_week = User.current.today.cwday
876 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
876 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
877 sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6, is_custom_filter)
877 sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6, is_custom_filter)
878 when "lw"
878 when "lw"
879 # = last week
879 # = last week
880 first_day_of_week = l(:general_first_day_of_week).to_i
880 first_day_of_week = l(:general_first_day_of_week).to_i
881 day_of_week = User.current.today.cwday
881 day_of_week = User.current.today.cwday
882 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
882 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
883 sql = relative_date_clause(db_table, db_field, - days_ago - 7, - days_ago - 1, is_custom_filter)
883 sql = relative_date_clause(db_table, db_field, - days_ago - 7, - days_ago - 1, is_custom_filter)
884 when "l2w"
884 when "l2w"
885 # = last 2 weeks
885 # = last 2 weeks
886 first_day_of_week = l(:general_first_day_of_week).to_i
886 first_day_of_week = l(:general_first_day_of_week).to_i
887 day_of_week = User.current.today.cwday
887 day_of_week = User.current.today.cwday
888 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
888 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
889 sql = relative_date_clause(db_table, db_field, - days_ago - 14, - days_ago - 1, is_custom_filter)
889 sql = relative_date_clause(db_table, db_field, - days_ago - 14, - days_ago - 1, is_custom_filter)
890 when "m"
890 when "m"
891 # = this month
891 # = this month
892 date = User.current.today
892 date = User.current.today
893 sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month, is_custom_filter)
893 sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month, is_custom_filter)
894 when "lm"
894 when "lm"
895 # = last month
895 # = last month
896 date = User.current.today.prev_month
896 date = User.current.today.prev_month
897 sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month, is_custom_filter)
897 sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month, is_custom_filter)
898 when "y"
898 when "y"
899 # = this year
899 # = this year
900 date = User.current.today
900 date = User.current.today
901 sql = date_clause(db_table, db_field, date.beginning_of_year, date.end_of_year, is_custom_filter)
901 sql = date_clause(db_table, db_field, date.beginning_of_year, date.end_of_year, is_custom_filter)
902 when "~"
902 when "~"
903 sql = sql_contains("#{db_table}.#{db_field}", value.first)
903 sql = sql_contains("#{db_table}.#{db_field}", value.first)
904 when "!~"
904 when "!~"
905 sql = sql_contains("#{db_table}.#{db_field}", value.first, false)
905 sql = sql_contains("#{db_table}.#{db_field}", value.first, false)
906 else
906 else
907 raise "Unknown query operator #{operator}"
907 raise "Unknown query operator #{operator}"
908 end
908 end
909
909
910 return sql
910 return sql
911 end
911 end
912
912
913 # Returns a SQL LIKE statement with wildcards
913 # Returns a SQL LIKE statement with wildcards
914 def sql_contains(db_field, value, match=true)
914 def sql_contains(db_field, value, match=true)
915 queried_class.send :sanitize_sql_for_conditions,
915 queried_class.send :sanitize_sql_for_conditions,
916 [Redmine::Database.like(db_field, '?', :match => match), "%#{value}%"]
916 [Redmine::Database.like(db_field, '?', :match => match), "%#{value}%"]
917 end
917 end
918
918
919 # Adds a filter for the given custom field
919 # Adds a filter for the given custom field
920 def add_custom_field_filter(field, assoc=nil)
920 def add_custom_field_filter(field, assoc=nil)
921 options = field.query_filter_options(self)
921 options = field.query_filter_options(self)
922 if field.format.target_class && field.format.target_class <= User
922 if field.format.target_class && field.format.target_class <= User
923 if options[:values].is_a?(Array) && User.current.logged?
923 if options[:values].is_a?(Array) && User.current.logged?
924 options[:values].unshift ["<< #{l(:label_me)} >>", "me"]
924 options[:values].unshift ["<< #{l(:label_me)} >>", "me"]
925 end
925 end
926 end
926 end
927
927
928 filter_id = "cf_#{field.id}"
928 filter_id = "cf_#{field.id}"
929 filter_name = field.name
929 filter_name = field.name
930 if assoc.present?
930 if assoc.present?
931 filter_id = "#{assoc}.#{filter_id}"
931 filter_id = "#{assoc}.#{filter_id}"
932 filter_name = l("label_attribute_of_#{assoc}", :name => filter_name)
932 filter_name = l("label_attribute_of_#{assoc}", :name => filter_name)
933 end
933 end
934 add_available_filter filter_id, options.merge({
934 add_available_filter filter_id, options.merge({
935 :name => filter_name,
935 :name => filter_name,
936 :field => field
936 :field => field
937 })
937 })
938 end
938 end
939
939
940 # Adds filters for the given custom fields scope
940 # Adds filters for the given custom fields scope
941 def add_custom_fields_filters(scope, assoc=nil)
941 def add_custom_fields_filters(scope, assoc=nil)
942 scope.visible.where(:is_filter => true).sorted.each do |field|
942 scope.visible.where(:is_filter => true).sorted.each do |field|
943 add_custom_field_filter(field, assoc)
943 add_custom_field_filter(field, assoc)
944 end
944 end
945 end
945 end
946
946
947 # Adds filters for the given associations custom fields
947 # Adds filters for the given associations custom fields
948 def add_associations_custom_fields_filters(*associations)
948 def add_associations_custom_fields_filters(*associations)
949 fields_by_class = CustomField.visible.where(:is_filter => true).group_by(&:class)
949 fields_by_class = CustomField.visible.where(:is_filter => true).group_by(&:class)
950 associations.each do |assoc|
950 associations.each do |assoc|
951 association_klass = queried_class.reflect_on_association(assoc).klass
951 association_klass = queried_class.reflect_on_association(assoc).klass
952 fields_by_class.each do |field_class, fields|
952 fields_by_class.each do |field_class, fields|
953 if field_class.customized_class <= association_klass
953 if field_class.customized_class <= association_klass
954 fields.sort.each do |field|
954 fields.sort.each do |field|
955 add_custom_field_filter(field, assoc)
955 add_custom_field_filter(field, assoc)
956 end
956 end
957 end
957 end
958 end
958 end
959 end
959 end
960 end
960 end
961
961
962 def quoted_time(time, is_custom_filter)
962 def quoted_time(time, is_custom_filter)
963 if is_custom_filter
963 if is_custom_filter
964 # Custom field values are stored as strings in the DB
964 # Custom field values are stored as strings in the DB
965 # using this format that does not depend on DB date representation
965 # using this format that does not depend on DB date representation
966 time.strftime("%Y-%m-%d %H:%M:%S")
966 time.strftime("%Y-%m-%d %H:%M:%S")
967 else
967 else
968 self.class.connection.quoted_date(time)
968 self.class.connection.quoted_date(time)
969 end
969 end
970 end
970 end
971
971
972 def date_for_user_time_zone(y, m, d)
972 def date_for_user_time_zone(y, m, d)
973 if tz = User.current.time_zone
973 if tz = User.current.time_zone
974 tz.local y, m, d
974 tz.local y, m, d
975 else
975 else
976 Time.local y, m, d
976 Time.local y, m, d
977 end
977 end
978 end
978 end
979
979
980 # Returns a SQL clause for a date or datetime field.
980 # Returns a SQL clause for a date or datetime field.
981 def date_clause(table, field, from, to, is_custom_filter)
981 def date_clause(table, field, from, to, is_custom_filter)
982 s = []
982 s = []
983 if from
983 if from
984 if from.is_a?(Date)
984 if from.is_a?(Date)
985 from = date_for_user_time_zone(from.year, from.month, from.day).yesterday.end_of_day
985 from = date_for_user_time_zone(from.year, from.month, from.day).yesterday.end_of_day
986 else
986 else
987 from = from - 1 # second
987 from = from - 1 # second
988 end
988 end
989 if self.class.default_timezone == :utc
989 if self.class.default_timezone == :utc
990 from = from.utc
990 from = from.utc
991 end
991 end
992 s << ("#{table}.#{field} > '%s'" % [quoted_time(from, is_custom_filter)])
992 s << ("#{table}.#{field} > '%s'" % [quoted_time(from, is_custom_filter)])
993 end
993 end
994 if to
994 if to
995 if to.is_a?(Date)
995 if to.is_a?(Date)
996 to = date_for_user_time_zone(to.year, to.month, to.day).end_of_day
996 to = date_for_user_time_zone(to.year, to.month, to.day).end_of_day
997 end
997 end
998 if self.class.default_timezone == :utc
998 if self.class.default_timezone == :utc
999 to = to.utc
999 to = to.utc
1000 end
1000 end
1001 s << ("#{table}.#{field} <= '%s'" % [quoted_time(to, is_custom_filter)])
1001 s << ("#{table}.#{field} <= '%s'" % [quoted_time(to, is_custom_filter)])
1002 end
1002 end
1003 s.join(' AND ')
1003 s.join(' AND ')
1004 end
1004 end
1005
1005
1006 # Returns a SQL clause for a date or datetime field using relative dates.
1006 # Returns a SQL clause for a date or datetime field using relative dates.
1007 def relative_date_clause(table, field, days_from, days_to, is_custom_filter)
1007 def relative_date_clause(table, field, days_from, days_to, is_custom_filter)
1008 date_clause(table, field, (days_from ? User.current.today + days_from : nil), (days_to ? User.current.today + days_to : nil), is_custom_filter)
1008 date_clause(table, field, (days_from ? User.current.today + days_from : nil), (days_to ? User.current.today + days_to : nil), is_custom_filter)
1009 end
1009 end
1010
1010
1011 # Returns a Date or Time from the given filter value
1011 # Returns a Date or Time from the given filter value
1012 def parse_date(arg)
1012 def parse_date(arg)
1013 if arg.to_s =~ /\A\d{4}-\d{2}-\d{2}T/
1013 if arg.to_s =~ /\A\d{4}-\d{2}-\d{2}T/
1014 Time.parse(arg) rescue nil
1014 Time.parse(arg) rescue nil
1015 else
1015 else
1016 Date.parse(arg) rescue nil
1016 Date.parse(arg) rescue nil
1017 end
1017 end
1018 end
1018 end
1019
1019
1020 # Additional joins required for the given sort options
1020 # Additional joins required for the given sort options
1021 def joins_for_order_statement(order_options)
1021 def joins_for_order_statement(order_options)
1022 joins = []
1022 joins = []
1023
1023
1024 if order_options
1024 if order_options
1025 if order_options.include?('authors')
1025 if order_options.include?('authors')
1026 joins << "LEFT OUTER JOIN #{User.table_name} authors ON authors.id = #{queried_table_name}.author_id"
1026 joins << "LEFT OUTER JOIN #{User.table_name} authors ON authors.id = #{queried_table_name}.author_id"
1027 end
1027 end
1028 order_options.scan(/cf_\d+/).uniq.each do |name|
1028 order_options.scan(/cf_\d+/).uniq.each do |name|
1029 column = available_columns.detect {|c| c.name.to_s == name}
1029 column = available_columns.detect {|c| c.name.to_s == name}
1030 join = column && column.custom_field.join_for_order_statement
1030 join = column && column.custom_field.join_for_order_statement
1031 if join
1031 if join
1032 joins << join
1032 joins << join
1033 end
1033 end
1034 end
1034 end
1035 end
1035 end
1036
1036
1037 joins.any? ? joins.join(' ') : nil
1037 joins.any? ? joins.join(' ') : nil
1038 end
1038 end
1039 end
1039 end
@@ -1,218 +1,259
1 # Redmine - project management software
1 # Redmine - project management software
2 # Copyright (C) 2006-2016 Jean-Philippe Lang
2 # Copyright (C) 2006-2016 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 require File.expand_path('../../test_helper', __FILE__)
18 require File.expand_path('../../test_helper', __FILE__)
19
19
20 class IssuesTest < Redmine::IntegrationTest
20 class IssuesTest < Redmine::IntegrationTest
21 fixtures :projects,
21 fixtures :projects,
22 :users, :email_addresses,
22 :users, :email_addresses,
23 :roles,
23 :roles,
24 :members,
24 :members,
25 :member_roles,
25 :member_roles,
26 :trackers,
26 :trackers,
27 :projects_trackers,
27 :projects_trackers,
28 :enabled_modules,
28 :enabled_modules,
29 :issue_statuses,
29 :issue_statuses,
30 :issues,
30 :issues,
31 :enumerations,
31 :enumerations,
32 :custom_fields,
32 :custom_fields,
33 :custom_values,
33 :custom_values,
34 :custom_fields_trackers,
34 :custom_fields_trackers,
35 :attachments
35 :attachments
36
36
37 # create an issue
37 # create an issue
38 def test_add_issue
38 def test_add_issue
39 log_user('jsmith', 'jsmith')
39 log_user('jsmith', 'jsmith')
40
40
41 get '/projects/ecookbook/issues/new'
41 get '/projects/ecookbook/issues/new'
42 assert_response :success
42 assert_response :success
43 assert_template 'issues/new'
43 assert_template 'issues/new'
44
44
45 issue = new_record(Issue) do
45 issue = new_record(Issue) do
46 post '/projects/ecookbook/issues',
46 post '/projects/ecookbook/issues',
47 :issue => { :tracker_id => "1",
47 :issue => { :tracker_id => "1",
48 :start_date => "2006-12-26",
48 :start_date => "2006-12-26",
49 :priority_id => "4",
49 :priority_id => "4",
50 :subject => "new test issue",
50 :subject => "new test issue",
51 :category_id => "",
51 :category_id => "",
52 :description => "new issue",
52 :description => "new issue",
53 :done_ratio => "0",
53 :done_ratio => "0",
54 :due_date => "",
54 :due_date => "",
55 :assigned_to_id => "" },
55 :assigned_to_id => "" },
56 :custom_fields => {'2' => 'Value for field 2'}
56 :custom_fields => {'2' => 'Value for field 2'}
57 end
57 end
58 # check redirection
58 # check redirection
59 assert_redirected_to :controller => 'issues', :action => 'show', :id => issue
59 assert_redirected_to :controller => 'issues', :action => 'show', :id => issue
60 follow_redirect!
60 follow_redirect!
61 assert_equal issue, assigns(:issue)
61 assert_equal issue, assigns(:issue)
62
62
63 # check issue attributes
63 # check issue attributes
64 assert_equal 'jsmith', issue.author.login
64 assert_equal 'jsmith', issue.author.login
65 assert_equal 1, issue.project.id
65 assert_equal 1, issue.project.id
66 assert_equal 1, issue.status.id
66 assert_equal 1, issue.status.id
67 end
67 end
68
68
69 def test_create_issue_by_anonymous_without_permission_should_fail
69 def test_create_issue_by_anonymous_without_permission_should_fail
70 Role.anonymous.remove_permission! :add_issues
70 Role.anonymous.remove_permission! :add_issues
71
71
72 assert_no_difference 'Issue.count' do
72 assert_no_difference 'Issue.count' do
73 post '/projects/1/issues', :tracker_id => "1", :issue => {:subject => "new test issue"}
73 post '/projects/1/issues', :tracker_id => "1", :issue => {:subject => "new test issue"}
74 end
74 end
75 assert_response 302
75 assert_response 302
76 end
76 end
77
77
78 def test_create_issue_by_anonymous_with_custom_permission_should_succeed
78 def test_create_issue_by_anonymous_with_custom_permission_should_succeed
79 Role.anonymous.remove_permission! :add_issues
79 Role.anonymous.remove_permission! :add_issues
80 Member.create!(:project_id => 1, :principal => Group.anonymous, :role_ids => [3])
80 Member.create!(:project_id => 1, :principal => Group.anonymous, :role_ids => [3])
81
81
82 issue = new_record(Issue) do
82 issue = new_record(Issue) do
83 post '/projects/1/issues', :tracker_id => "1", :issue => {:subject => "new test issue"}
83 post '/projects/1/issues', :tracker_id => "1", :issue => {:subject => "new test issue"}
84 assert_response 302
84 assert_response 302
85 end
85 end
86 assert_equal User.anonymous, issue.author
86 assert_equal User.anonymous, issue.author
87 end
87 end
88
88
89 # add then remove 2 attachments to an issue
89 # add then remove 2 attachments to an issue
90 def test_issue_attachments
90 def test_issue_attachments
91 log_user('jsmith', 'jsmith')
91 log_user('jsmith', 'jsmith')
92 set_tmp_attachments_directory
92 set_tmp_attachments_directory
93
93
94 attachment = new_record(Attachment) do
94 attachment = new_record(Attachment) do
95 put '/issues/1',
95 put '/issues/1',
96 :notes => 'Some notes',
96 :notes => 'Some notes',
97 :attachments => {'1' => {'file' => uploaded_test_file('testfile.txt', 'text/plain'), 'description' => 'This is an attachment'}}
97 :attachments => {'1' => {'file' => uploaded_test_file('testfile.txt', 'text/plain'), 'description' => 'This is an attachment'}}
98 assert_redirected_to "/issues/1"
98 assert_redirected_to "/issues/1"
99 end
99 end
100
100
101 assert_equal Issue.find(1), attachment.container
101 assert_equal Issue.find(1), attachment.container
102 assert_equal 'testfile.txt', attachment.filename
102 assert_equal 'testfile.txt', attachment.filename
103 assert_equal 'This is an attachment', attachment.description
103 assert_equal 'This is an attachment', attachment.description
104 # verify the size of the attachment stored in db
104 # verify the size of the attachment stored in db
105 #assert_equal file_data_1.length, attachment.filesize
105 #assert_equal file_data_1.length, attachment.filesize
106 # verify that the attachment was written to disk
106 # verify that the attachment was written to disk
107 assert File.exist?(attachment.diskfile)
107 assert File.exist?(attachment.diskfile)
108
108
109 # remove the attachments
109 # remove the attachments
110 Issue.find(1).attachments.each(&:destroy)
110 Issue.find(1).attachments.each(&:destroy)
111 assert_equal 0, Issue.find(1).attachments.length
111 assert_equal 0, Issue.find(1).attachments.length
112 end
112 end
113
113
114 def test_next_and_previous_links_should_be_displayed_after_query_grouped_and_sorted_by_version
115 with_settings :default_language => 'en' do
116 get '/projects/ecookbook/issues?set_filter=1&group_by=fixed_version&sort=priority:desc,fixed_version,id'
117 assert_response :success
118 assert_select 'td.id', :text => '5'
119
120 get '/issues/5'
121 assert_response :success
122 assert_select '.next-prev-links .position', :text => '5 of 6'
123 end
124 end
125
126 def test_next_and_previous_links_should_be_displayed_after_filter
127 with_settings :default_language => 'en' do
128 get '/projects/ecookbook/issues?set_filter=1&tracker_id=1'
129 assert_response :success
130 assert_select 'td.id', :text => '5'
131
132 get '/issues/5'
133 assert_response :success
134 assert_select '.next-prev-links .position', :text => '3 of 5'
135 end
136 end
137
138 def test_next_and_previous_links_should_be_displayed_after_saved_query
139 query = IssueQuery.create!(:name => 'Calendar Query',
140 :visibility => IssueQuery::VISIBILITY_PUBLIC,
141 :filters => {'tracker_id' => {:operator => '=', :values => ['1']}}
142 )
143
144 with_settings :default_language => 'en' do
145 get "/projects/ecookbook/issues?set_filter=1&query_id=#{query.id}"
146 assert_response :success
147 assert_select 'td.id', :text => '5'
148
149 get '/issues/5'
150 assert_response :success
151 assert_select '.next-prev-links .position', :text => '6 of 8'
152 end
153 end
154
114 def test_other_formats_links_on_index
155 def test_other_formats_links_on_index
115 get '/projects/ecookbook/issues'
156 get '/projects/ecookbook/issues'
116
157
117 %w(Atom PDF CSV).each do |format|
158 %w(Atom PDF CSV).each do |format|
118 assert_select 'a[rel=nofollow][href=?]', "/projects/ecookbook/issues.#{format.downcase}", :text => format
159 assert_select 'a[rel=nofollow][href=?]', "/projects/ecookbook/issues.#{format.downcase}", :text => format
119 end
160 end
120 end
161 end
121
162
122 def test_other_formats_links_on_index_without_project_id_in_url
163 def test_other_formats_links_on_index_without_project_id_in_url
123 get '/issues', :project_id => 'ecookbook'
164 get '/issues', :project_id => 'ecookbook'
124
165
125 %w(Atom PDF CSV).each do |format|
166 %w(Atom PDF CSV).each do |format|
126 assert_select 'a[rel=nofollow][href=?]', "/projects/ecookbook/issues.#{format.downcase}", :text => format
167 assert_select 'a[rel=nofollow][href=?]', "/projects/ecookbook/issues.#{format.downcase}", :text => format
127 end
168 end
128 end
169 end
129
170
130 def test_pagination_links_on_index
171 def test_pagination_links_on_index
131 with_settings :per_page_options => '2' do
172 with_settings :per_page_options => '2' do
132 get '/projects/ecookbook/issues'
173 get '/projects/ecookbook/issues'
133
174
134 assert_select 'a[href=?]', '/projects/ecookbook/issues?page=2', :text => '2'
175 assert_select 'a[href=?]', '/projects/ecookbook/issues?page=2', :text => '2'
135 end
176 end
136 end
177 end
137
178
138 def test_pagination_links_on_index_without_project_id_in_url
179 def test_pagination_links_on_index_without_project_id_in_url
139 with_settings :per_page_options => '2' do
180 with_settings :per_page_options => '2' do
140 get '/issues', :project_id => 'ecookbook'
181 get '/issues', :project_id => 'ecookbook'
141
182
142 assert_select 'a[href=?]', '/projects/ecookbook/issues?page=2', :text => '2'
183 assert_select 'a[href=?]', '/projects/ecookbook/issues?page=2', :text => '2'
143 end
184 end
144 end
185 end
145
186
146 def test_issue_with_user_custom_field
187 def test_issue_with_user_custom_field
147 @field = IssueCustomField.create!(:name => 'Tester', :field_format => 'user', :is_for_all => true, :trackers => Tracker.all)
188 @field = IssueCustomField.create!(:name => 'Tester', :field_format => 'user', :is_for_all => true, :trackers => Tracker.all)
148 Role.anonymous.add_permission! :add_issues, :edit_issues
189 Role.anonymous.add_permission! :add_issues, :edit_issues
149 users = Project.find(1).users.uniq.sort
190 users = Project.find(1).users.uniq.sort
150 tester = users.first
191 tester = users.first
151
192
152 # Issue form
193 # Issue form
153 get '/projects/ecookbook/issues/new'
194 get '/projects/ecookbook/issues/new'
154 assert_response :success
195 assert_response :success
155 assert_select 'select[name=?]', "issue[custom_field_values][#{@field.id}]" do
196 assert_select 'select[name=?]', "issue[custom_field_values][#{@field.id}]" do
156 assert_select 'option', users.size + 1 # +1 for blank value
197 assert_select 'option', users.size + 1 # +1 for blank value
157 assert_select 'option[value=?]', tester.id.to_s, :text => tester.name
198 assert_select 'option[value=?]', tester.id.to_s, :text => tester.name
158 end
199 end
159
200
160 # Create issue
201 # Create issue
161 issue = new_record(Issue) do
202 issue = new_record(Issue) do
162 post '/projects/ecookbook/issues',
203 post '/projects/ecookbook/issues',
163 :issue => {
204 :issue => {
164 :tracker_id => '1',
205 :tracker_id => '1',
165 :priority_id => '4',
206 :priority_id => '4',
166 :subject => 'Issue with user custom field',
207 :subject => 'Issue with user custom field',
167 :custom_field_values => {@field.id.to_s => users.first.id.to_s}
208 :custom_field_values => {@field.id.to_s => users.first.id.to_s}
168 }
209 }
169 assert_response 302
210 assert_response 302
170 end
211 end
171
212
172 # Issue view
213 # Issue view
173 follow_redirect!
214 follow_redirect!
174 assert_select ".cf_#{@field.id}" do
215 assert_select ".cf_#{@field.id}" do
175 assert_select '.label', :text => 'Tester:'
216 assert_select '.label', :text => 'Tester:'
176 assert_select '.value', :text => tester.name
217 assert_select '.value', :text => tester.name
177 end
218 end
178 assert_select 'select[name=?]', "issue[custom_field_values][#{@field.id}]" do
219 assert_select 'select[name=?]', "issue[custom_field_values][#{@field.id}]" do
179 assert_select 'option', users.size + 1 # +1 for blank value
220 assert_select 'option', users.size + 1 # +1 for blank value
180 assert_select 'option[value=?][selected=selected]', tester.id.to_s, :text => tester.name
221 assert_select 'option[value=?][selected=selected]', tester.id.to_s, :text => tester.name
181 end
222 end
182
223
183 new_tester = users[1]
224 new_tester = users[1]
184 with_settings :default_language => 'en' do
225 with_settings :default_language => 'en' do
185 # Update issue
226 # Update issue
186 assert_difference 'Journal.count' do
227 assert_difference 'Journal.count' do
187 put "/issues/#{issue.id}",
228 put "/issues/#{issue.id}",
188 :notes => 'Updating custom field',
229 :notes => 'Updating custom field',
189 :issue => {
230 :issue => {
190 :custom_field_values => {@field.id.to_s => new_tester.id.to_s}
231 :custom_field_values => {@field.id.to_s => new_tester.id.to_s}
191 }
232 }
192 assert_redirected_to "/issues/#{issue.id}"
233 assert_redirected_to "/issues/#{issue.id}"
193 end
234 end
194 # Issue view
235 # Issue view
195 follow_redirect!
236 follow_redirect!
196 assert_select 'ul.details li', :text => "Tester changed from #{tester} to #{new_tester}"
237 assert_select 'ul.details li', :text => "Tester changed from #{tester} to #{new_tester}"
197 end
238 end
198 end
239 end
199
240
200 def test_update_using_invalid_http_verbs
241 def test_update_using_invalid_http_verbs
201 subject = 'Updated by an invalid http verb'
242 subject = 'Updated by an invalid http verb'
202
243
203 get '/issues/update/1', {:issue => {:subject => subject}}, credentials('jsmith')
244 get '/issues/update/1', {:issue => {:subject => subject}}, credentials('jsmith')
204 assert_response 404
245 assert_response 404
205 assert_not_equal subject, Issue.find(1).subject
246 assert_not_equal subject, Issue.find(1).subject
206
247
207 post '/issues/1', {:issue => {:subject => subject}}, credentials('jsmith')
248 post '/issues/1', {:issue => {:subject => subject}}, credentials('jsmith')
208 assert_response 404
249 assert_response 404
209 assert_not_equal subject, Issue.find(1).subject
250 assert_not_equal subject, Issue.find(1).subject
210 end
251 end
211
252
212 def test_get_watch_should_be_invalid
253 def test_get_watch_should_be_invalid
213 assert_no_difference 'Watcher.count' do
254 assert_no_difference 'Watcher.count' do
214 get '/watchers/watch?object_type=issue&object_id=1', {}, credentials('jsmith')
255 get '/watchers/watch?object_type=issue&object_id=1', {}, credentials('jsmith')
215 assert_response 404
256 assert_response 404
216 end
257 end
217 end
258 end
218 end
259 end
General Comments 0
You need to be logged in to leave comments. Login now