##// END OF EJS Templates
jro_apps » redmine
RSS
SQL server: non ASCII filter on issue subject does not work (#22405)....
Jean-Philippe Lang -
r14969:c34a1d0f90a6
parent child
Show More
Side by Side Unified
Context file:
r14969:c34a1d0f90a6 Loading diff...:
Show file before | Show file after | Hide comments
@@ -1,1028 +1,1028
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(/^[+-]?\d+$/) }
267 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^[+-]?\d+$/) }
268 when :float
268 when :float
269 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^[+-]?\d+(\.\d*)?$/) }
269 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^[+-]?\d+(\.\d*)?$/) }
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.sorted.to_a : project.rolled_up_trackers
304 @trackers ||= project.nil? ? Tracker.sorted.to_a : project.rolled_up_trackers
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 ||= l("field_#{field.to_s.gsub(/_id$/, '')}", :default => field)
425 label ||= l("field_#{field.to_s.gsub(/_id$/, '')}", :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 grouped? && (column = group_by_column)
546 if grouped? && (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).try(:upcase)
548 column.sortable.is_a?(Array) ?
548 column.sortable.is_a?(Array) ?
549 column.sortable.collect {|s| "#{s} #{order}"}.join(',') :
549 column.sortable.collect {|s| "#{s} #{order}"}.join(',') :
550 "#{column.sortable} #{order}"
550 "#{column.sortable} #{order}"
551 end
551 end
552 end
552 end
553
553
554 # Returns true if the query is a grouped query
554 # Returns true if the query is a grouped query
555 def grouped?
555 def grouped?
556 !group_by_column.nil?
556 !group_by_column.nil?
557 end
557 end
558
558
559 def group_by_column
559 def group_by_column
560 groupable_columns.detect {|c| c.groupable && c.name.to_s == group_by}
560 groupable_columns.detect {|c| c.groupable && c.name.to_s == group_by}
561 end
561 end
562
562
563 def group_by_statement
563 def group_by_statement
564 group_by_column.try(:groupable)
564 group_by_column.try(:groupable)
565 end
565 end
566
566
567 def project_statement
567 def project_statement
568 project_clauses = []
568 project_clauses = []
569 if project && !project.descendants.active.empty?
569 if project && !project.descendants.active.empty?
570 if has_filter?("subproject_id")
570 if has_filter?("subproject_id")
571 case operator_for("subproject_id")
571 case operator_for("subproject_id")
572 when '='
572 when '='
573 # include the selected subprojects
573 # include the selected subprojects
574 ids = [project.id] + values_for("subproject_id").each(&:to_i)
574 ids = [project.id] + values_for("subproject_id").each(&:to_i)
575 project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
575 project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
576 when '!*'
576 when '!*'
577 # main project only
577 # main project only
578 project_clauses << "#{Project.table_name}.id = %d" % project.id
578 project_clauses << "#{Project.table_name}.id = %d" % project.id
579 else
579 else
580 # all subprojects
580 # all subprojects
581 project_clauses << "#{Project.table_name}.lft >= #{project.lft} AND #{Project.table_name}.rgt <= #{project.rgt}"
581 project_clauses << "#{Project.table_name}.lft >= #{project.lft} AND #{Project.table_name}.rgt <= #{project.rgt}"
582 end
582 end
583 elsif Setting.display_subprojects_issues?
583 elsif Setting.display_subprojects_issues?
584 project_clauses << "#{Project.table_name}.lft >= #{project.lft} AND #{Project.table_name}.rgt <= #{project.rgt}"
584 project_clauses << "#{Project.table_name}.lft >= #{project.lft} AND #{Project.table_name}.rgt <= #{project.rgt}"
585 else
585 else
586 project_clauses << "#{Project.table_name}.id = %d" % project.id
586 project_clauses << "#{Project.table_name}.id = %d" % project.id
587 end
587 end
588 elsif project
588 elsif project
589 project_clauses << "#{Project.table_name}.id = %d" % project.id
589 project_clauses << "#{Project.table_name}.id = %d" % project.id
590 end
590 end
591 project_clauses.any? ? project_clauses.join(' AND ') : nil
591 project_clauses.any? ? project_clauses.join(' AND ') : nil
592 end
592 end
593
593
594 def statement
594 def statement
595 # filters clauses
595 # filters clauses
596 filters_clauses = []
596 filters_clauses = []
597 filters.each_key do |field|
597 filters.each_key do |field|
598 next if field == "subproject_id"
598 next if field == "subproject_id"
599 v = values_for(field).clone
599 v = values_for(field).clone
600 next unless v and !v.empty?
600 next unless v and !v.empty?
601 operator = operator_for(field)
601 operator = operator_for(field)
602
602
603 # "me" value substitution
603 # "me" value substitution
604 if %w(assigned_to_id author_id user_id watcher_id).include?(field)
604 if %w(assigned_to_id author_id user_id watcher_id).include?(field)
605 if v.delete("me")
605 if v.delete("me")
606 if User.current.logged?
606 if User.current.logged?
607 v.push(User.current.id.to_s)
607 v.push(User.current.id.to_s)
608 v += User.current.group_ids.map(&:to_s) if field == 'assigned_to_id'
608 v += User.current.group_ids.map(&:to_s) if field == 'assigned_to_id'
609 else
609 else
610 v.push("0")
610 v.push("0")
611 end
611 end
612 end
612 end
613 end
613 end
614
614
615 if field == 'project_id'
615 if field == 'project_id'
616 if v.delete('mine')
616 if v.delete('mine')
617 v += User.current.memberships.map(&:project_id).map(&:to_s)
617 v += User.current.memberships.map(&:project_id).map(&:to_s)
618 end
618 end
619 end
619 end
620
620
621 if field =~ /cf_(\d+)$/
621 if field =~ /cf_(\d+)$/
622 # custom field
622 # custom field
623 filters_clauses << sql_for_custom_field(field, operator, v, $1)
623 filters_clauses << sql_for_custom_field(field, operator, v, $1)
624 elsif respond_to?("sql_for_#{field}_field")
624 elsif respond_to?("sql_for_#{field}_field")
625 # specific statement
625 # specific statement
626 filters_clauses << send("sql_for_#{field}_field", field, operator, v)
626 filters_clauses << send("sql_for_#{field}_field", field, operator, v)
627 else
627 else
628 # regular field
628 # regular field
629 filters_clauses << '(' + sql_for_field(field, operator, v, queried_table_name, field) + ')'
629 filters_clauses << '(' + sql_for_field(field, operator, v, queried_table_name, field) + ')'
630 end
630 end
631 end if filters and valid?
631 end if filters and valid?
632
632
633 if (c = group_by_column) && c.is_a?(QueryCustomFieldColumn)
633 if (c = group_by_column) && c.is_a?(QueryCustomFieldColumn)
634 # Excludes results for which the grouped custom field is not visible
634 # Excludes results for which the grouped custom field is not visible
635 filters_clauses << c.custom_field.visibility_by_project_condition
635 filters_clauses << c.custom_field.visibility_by_project_condition
636 end
636 end
637
637
638 filters_clauses << project_statement
638 filters_clauses << project_statement
639 filters_clauses.reject!(&:blank?)
639 filters_clauses.reject!(&:blank?)
640
640
641 filters_clauses.any? ? filters_clauses.join(' AND ') : nil
641 filters_clauses.any? ? filters_clauses.join(' AND ') : nil
642 end
642 end
643
643
644 # Returns the sum of values for the given column
644 # Returns the sum of values for the given column
645 def total_for(column)
645 def total_for(column)
646 total_with_scope(column, base_scope)
646 total_with_scope(column, base_scope)
647 end
647 end
648
648
649 # Returns a hash of the sum of the given column for each group,
649 # Returns a hash of the sum of the given column for each group,
650 # or nil if the query is not grouped
650 # or nil if the query is not grouped
651 def total_by_group_for(column)
651 def total_by_group_for(column)
652 grouped_query do |scope|
652 grouped_query do |scope|
653 total_with_scope(column, scope)
653 total_with_scope(column, scope)
654 end
654 end
655 end
655 end
656
656
657 def totals
657 def totals
658 totals = totalable_columns.map {|column| [column, total_for(column)]}
658 totals = totalable_columns.map {|column| [column, total_for(column)]}
659 yield totals if block_given?
659 yield totals if block_given?
660 totals
660 totals
661 end
661 end
662
662
663 def totals_by_group
663 def totals_by_group
664 totals = totalable_columns.map {|column| [column, total_by_group_for(column)]}
664 totals = totalable_columns.map {|column| [column, total_by_group_for(column)]}
665 yield totals if block_given?
665 yield totals if block_given?
666 totals
666 totals
667 end
667 end
668
668
669 private
669 private
670
670
671 def grouped_query(&block)
671 def grouped_query(&block)
672 r = nil
672 r = nil
673 if grouped?
673 if grouped?
674 begin
674 begin
675 # Rails3 will raise an (unexpected) RecordNotFound if there's only a nil group value
675 # Rails3 will raise an (unexpected) RecordNotFound if there's only a nil group value
676 r = yield base_group_scope
676 r = yield base_group_scope
677 rescue ActiveRecord::RecordNotFound
677 rescue ActiveRecord::RecordNotFound
678 r = {nil => yield(base_scope)}
678 r = {nil => yield(base_scope)}
679 end
679 end
680 c = group_by_column
680 c = group_by_column
681 if c.is_a?(QueryCustomFieldColumn)
681 if c.is_a?(QueryCustomFieldColumn)
682 r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
682 r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
683 end
683 end
684 end
684 end
685 r
685 r
686 rescue ::ActiveRecord::StatementInvalid => e
686 rescue ::ActiveRecord::StatementInvalid => e
687 raise StatementInvalid.new(e.message)
687 raise StatementInvalid.new(e.message)
688 end
688 end
689
689
690 def total_with_scope(column, scope)
690 def total_with_scope(column, scope)
691 unless column.is_a?(QueryColumn)
691 unless column.is_a?(QueryColumn)
692 column = column.to_sym
692 column = column.to_sym
693 column = available_totalable_columns.detect {|c| c.name == column}
693 column = available_totalable_columns.detect {|c| c.name == column}
694 end
694 end
695 if column.is_a?(QueryCustomFieldColumn)
695 if column.is_a?(QueryCustomFieldColumn)
696 custom_field = column.custom_field
696 custom_field = column.custom_field
697 send "total_for_custom_field", custom_field, scope
697 send "total_for_custom_field", custom_field, scope
698 else
698 else
699 send "total_for_#{column.name}", scope
699 send "total_for_#{column.name}", scope
700 end
700 end
701 rescue ::ActiveRecord::StatementInvalid => e
701 rescue ::ActiveRecord::StatementInvalid => e
702 raise StatementInvalid.new(e.message)
702 raise StatementInvalid.new(e.message)
703 end
703 end
704
704
705 def base_scope
705 def base_scope
706 raise "unimplemented"
706 raise "unimplemented"
707 end
707 end
708
708
709 def base_group_scope
709 def base_group_scope
710 base_scope.
710 base_scope.
711 joins(joins_for_order_statement(group_by_statement)).
711 joins(joins_for_order_statement(group_by_statement)).
712 group(group_by_statement)
712 group(group_by_statement)
713 end
713 end
714
714
715 def total_for_custom_field(custom_field, scope, &block)
715 def total_for_custom_field(custom_field, scope, &block)
716 total = custom_field.format.total_for_scope(custom_field, scope)
716 total = custom_field.format.total_for_scope(custom_field, scope)
717 total = map_total(total) {|t| custom_field.format.cast_total_value(custom_field, t)}
717 total = map_total(total) {|t| custom_field.format.cast_total_value(custom_field, t)}
718 total
718 total
719 end
719 end
720
720
721 def map_total(total, &block)
721 def map_total(total, &block)
722 if total.is_a?(Hash)
722 if total.is_a?(Hash)
723 total.keys.each {|k| total[k] = yield total[k]}
723 total.keys.each {|k| total[k] = yield total[k]}
724 else
724 else
725 total = yield total
725 total = yield total
726 end
726 end
727 total
727 total
728 end
728 end
729
729
730 def sql_for_custom_field(field, operator, value, custom_field_id)
730 def sql_for_custom_field(field, operator, value, custom_field_id)
731 db_table = CustomValue.table_name
731 db_table = CustomValue.table_name
732 db_field = 'value'
732 db_field = 'value'
733 filter = @available_filters[field]
733 filter = @available_filters[field]
734 return nil unless filter
734 return nil unless filter
735 if filter[:field].format.target_class && filter[:field].format.target_class <= User
735 if filter[:field].format.target_class && filter[:field].format.target_class <= User
736 if value.delete('me')
736 if value.delete('me')
737 value.push User.current.id.to_s
737 value.push User.current.id.to_s
738 end
738 end
739 end
739 end
740 not_in = nil
740 not_in = nil
741 if operator == '!'
741 if operator == '!'
742 # Makes ! operator work for custom fields with multiple values
742 # Makes ! operator work for custom fields with multiple values
743 operator = '='
743 operator = '='
744 not_in = 'NOT'
744 not_in = 'NOT'
745 end
745 end
746 customized_key = "id"
746 customized_key = "id"
747 customized_class = queried_class
747 customized_class = queried_class
748 if field =~ /^(.+)\.cf_/
748 if field =~ /^(.+)\.cf_/
749 assoc = $1
749 assoc = $1
750 customized_key = "#{assoc}_id"
750 customized_key = "#{assoc}_id"
751 customized_class = queried_class.reflect_on_association(assoc.to_sym).klass.base_class rescue nil
751 customized_class = queried_class.reflect_on_association(assoc.to_sym).klass.base_class rescue nil
752 raise "Unknown #{queried_class.name} association #{assoc}" unless customized_class
752 raise "Unknown #{queried_class.name} association #{assoc}" unless customized_class
753 end
753 end
754 where = sql_for_field(field, operator, value, db_table, db_field, true)
754 where = sql_for_field(field, operator, value, db_table, db_field, true)
755 if operator =~ /[<>]/
755 if operator =~ /[<>]/
756 where = "(#{where}) AND #{db_table}.#{db_field} <> ''"
756 where = "(#{where}) AND #{db_table}.#{db_field} <> ''"
757 end
757 end
758 "#{queried_table_name}.#{customized_key} #{not_in} IN (" +
758 "#{queried_table_name}.#{customized_key} #{not_in} IN (" +
759 "SELECT #{customized_class.table_name}.id FROM #{customized_class.table_name}" +
759 "SELECT #{customized_class.table_name}.id FROM #{customized_class.table_name}" +
760 " 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}" +
760 " 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}" +
761 " WHERE (#{where}) AND (#{filter[:field].visibility_by_project_condition}))"
761 " WHERE (#{where}) AND (#{filter[:field].visibility_by_project_condition}))"
762 end
762 end
763
763
764 # Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+
764 # Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+
765 def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)
765 def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)
766 sql = ''
766 sql = ''
767 case operator
767 case operator
768 when "="
768 when "="
769 if value.any?
769 if value.any?
770 case type_for(field)
770 case type_for(field)
771 when :date, :date_past
771 when :date, :date_past
772 sql = date_clause(db_table, db_field, parse_date(value.first), parse_date(value.first), is_custom_filter)
772 sql = date_clause(db_table, db_field, parse_date(value.first), parse_date(value.first), is_custom_filter)
773 when :integer
773 when :integer
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)) = #{value.first.to_i})"
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)) = #{value.first.to_i})"
776 else
776 else
777 sql = "#{db_table}.#{db_field} = #{value.first.to_i}"
777 sql = "#{db_table}.#{db_field} = #{value.first.to_i}"
778 end
778 end
779 when :float
779 when :float
780 if is_custom_filter
780 if is_custom_filter
781 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})"
781 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})"
782 else
782 else
783 sql = "#{db_table}.#{db_field} BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5}"
783 sql = "#{db_table}.#{db_field} BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5}"
784 end
784 end
785 else
785 else
786 sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{self.class.connection.quote_string(val)}'"}.join(",") + ")"
786 sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{self.class.connection.quote_string(val)}'"}.join(",") + ")"
787 end
787 end
788 else
788 else
789 # IN an empty set
789 # IN an empty set
790 sql = "1=0"
790 sql = "1=0"
791 end
791 end
792 when "!"
792 when "!"
793 if value.any?
793 if value.any?
794 sql = "(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (" + value.collect{|val| "'#{self.class.connection.quote_string(val)}'"}.join(",") + "))"
794 sql = "(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (" + value.collect{|val| "'#{self.class.connection.quote_string(val)}'"}.join(",") + "))"
795 else
795 else
796 # NOT IN an empty set
796 # NOT IN an empty set
797 sql = "1=1"
797 sql = "1=1"
798 end
798 end
799 when "!*"
799 when "!*"
800 sql = "#{db_table}.#{db_field} IS NULL"
800 sql = "#{db_table}.#{db_field} IS NULL"
801 sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter
801 sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter
802 when "*"
802 when "*"
803 sql = "#{db_table}.#{db_field} IS NOT NULL"
803 sql = "#{db_table}.#{db_field} IS NOT NULL"
804 sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
804 sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
805 when ">="
805 when ">="
806 if [:date, :date_past].include?(type_for(field))
806 if [:date, :date_past].include?(type_for(field))
807 sql = date_clause(db_table, db_field, parse_date(value.first), nil, is_custom_filter)
807 sql = date_clause(db_table, db_field, parse_date(value.first), nil, is_custom_filter)
808 else
808 else
809 if is_custom_filter
809 if is_custom_filter
810 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})"
810 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})"
811 else
811 else
812 sql = "#{db_table}.#{db_field} >= #{value.first.to_f}"
812 sql = "#{db_table}.#{db_field} >= #{value.first.to_f}"
813 end
813 end
814 end
814 end
815 when "<="
815 when "<="
816 if [:date, :date_past].include?(type_for(field))
816 if [:date, :date_past].include?(type_for(field))
817 sql = date_clause(db_table, db_field, nil, parse_date(value.first), is_custom_filter)
817 sql = date_clause(db_table, db_field, nil, parse_date(value.first), is_custom_filter)
818 else
818 else
819 if is_custom_filter
819 if is_custom_filter
820 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})"
820 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})"
821 else
821 else
822 sql = "#{db_table}.#{db_field} <= #{value.first.to_f}"
822 sql = "#{db_table}.#{db_field} <= #{value.first.to_f}"
823 end
823 end
824 end
824 end
825 when "><"
825 when "><"
826 if [:date, :date_past].include?(type_for(field))
826 if [:date, :date_past].include?(type_for(field))
827 sql = date_clause(db_table, db_field, parse_date(value[0]), parse_date(value[1]), is_custom_filter)
827 sql = date_clause(db_table, db_field, parse_date(value[0]), parse_date(value[1]), is_custom_filter)
828 else
828 else
829 if is_custom_filter
829 if is_custom_filter
830 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})"
830 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})"
831 else
831 else
832 sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
832 sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
833 end
833 end
834 end
834 end
835 when "o"
835 when "o"
836 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"
836 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"
837 when "c"
837 when "c"
838 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"
838 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"
839 when "><t-"
839 when "><t-"
840 # between today - n days and today
840 # between today - n days and today
841 sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0, is_custom_filter)
841 sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0, is_custom_filter)
842 when ">t-"
842 when ">t-"
843 # >= today - n days
843 # >= today - n days
844 sql = relative_date_clause(db_table, db_field, - value.first.to_i, nil, is_custom_filter)
844 sql = relative_date_clause(db_table, db_field, - value.first.to_i, nil, 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, nil, - value.first.to_i, is_custom_filter)
847 sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i, is_custom_filter)
848 when "t-"
848 when "t-"
849 # = n days in past
849 # = n days in past
850 sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i, is_custom_filter)
850 sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i, is_custom_filter)
851 when "><t+"
851 when "><t+"
852 # between today and today + n days
852 # between today and today + n days
853 sql = relative_date_clause(db_table, db_field, 0, value.first.to_i, is_custom_filter)
853 sql = relative_date_clause(db_table, db_field, 0, value.first.to_i, is_custom_filter)
854 when ">t+"
854 when ">t+"
855 # >= today + n days
855 # >= today + n days
856 sql = relative_date_clause(db_table, db_field, value.first.to_i, nil, is_custom_filter)
856 sql = relative_date_clause(db_table, db_field, value.first.to_i, nil, 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, nil, value.first.to_i, is_custom_filter)
859 sql = relative_date_clause(db_table, db_field, nil, value.first.to_i, 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, value.first.to_i, value.first.to_i, is_custom_filter)
862 sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i, is_custom_filter)
863 when "t"
863 when "t"
864 # = today
864 # = today
865 sql = relative_date_clause(db_table, db_field, 0, 0, is_custom_filter)
865 sql = relative_date_clause(db_table, db_field, 0, 0, is_custom_filter)
866 when "ld"
866 when "ld"
867 # = yesterday
867 # = yesterday
868 sql = relative_date_clause(db_table, db_field, -1, -1, is_custom_filter)
868 sql = relative_date_clause(db_table, db_field, -1, -1, is_custom_filter)
869 when "w"
869 when "w"
870 # = this week
870 # = this week
871 first_day_of_week = l(:general_first_day_of_week).to_i
871 first_day_of_week = l(:general_first_day_of_week).to_i
872 day_of_week = Date.today.cwday
872 day_of_week = Date.today.cwday
873 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
873 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
874 sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6, is_custom_filter)
874 sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6, is_custom_filter)
875 when "lw"
875 when "lw"
876 # = last week
876 # = last week
877 first_day_of_week = l(:general_first_day_of_week).to_i
877 first_day_of_week = l(:general_first_day_of_week).to_i
878 day_of_week = Date.today.cwday
878 day_of_week = Date.today.cwday
879 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
879 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
880 sql = relative_date_clause(db_table, db_field, - days_ago - 7, - days_ago - 1, is_custom_filter)
880 sql = relative_date_clause(db_table, db_field, - days_ago - 7, - days_ago - 1, is_custom_filter)
881 when "l2w"
881 when "l2w"
882 # = last 2 weeks
882 # = last 2 weeks
883 first_day_of_week = l(:general_first_day_of_week).to_i
883 first_day_of_week = l(:general_first_day_of_week).to_i
884 day_of_week = Date.today.cwday
884 day_of_week = Date.today.cwday
885 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
885 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
886 sql = relative_date_clause(db_table, db_field, - days_ago - 14, - days_ago - 1, is_custom_filter)
886 sql = relative_date_clause(db_table, db_field, - days_ago - 14, - days_ago - 1, is_custom_filter)
887 when "m"
887 when "m"
888 # = this month
888 # = this month
889 date = Date.today
889 date = Date.today
890 sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month, is_custom_filter)
890 sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month, is_custom_filter)
891 when "lm"
891 when "lm"
892 # = last month
892 # = last month
893 date = Date.today.prev_month
893 date = Date.today.prev_month
894 sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month, is_custom_filter)
894 sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month, is_custom_filter)
895 when "y"
895 when "y"
896 # = this year
896 # = this year
897 date = Date.today
897 date = Date.today
898 sql = date_clause(db_table, db_field, date.beginning_of_year, date.end_of_year, is_custom_filter)
898 sql = date_clause(db_table, db_field, date.beginning_of_year, date.end_of_year, is_custom_filter)
899 when "~"
899 when "~"
900 sql = sql_contains("#{db_table}.#{db_field}", value.first)
900 sql = sql_contains("#{db_table}.#{db_field}", value.first)
901 when "!~"
901 when "!~"
902 sql = sql_contains("#{db_table}.#{db_field}", value.first, false)
902 sql = sql_contains("#{db_table}.#{db_field}", value.first, false)
903 else
903 else
904 raise "Unknown query operator #{operator}"
904 raise "Unknown query operator #{operator}"
905 end
905 end
906
906
907 return sql
907 return sql
908 end
908 end
909
909
910 # Returns a SQL LIKE statement with wildcards
910 # Returns a SQL LIKE statement with wildcards
911 def sql_contains(db_field, value, match=true)
911 def sql_contains(db_field, value, match=true)
912 value = "'%#{self.class.connection.quote_string(value.to_s)}%'"
912 queried_class.send :sanitize_sql_for_conditions,
913 Redmine::Database.like(db_field, value, :match => match)
913 [Redmine::Database.like(db_field, '?', :match => match), "%#{value}%"]
914 end
914 end
915
915
916 # Adds a filter for the given custom field
916 # Adds a filter for the given custom field
917 def add_custom_field_filter(field, assoc=nil)
917 def add_custom_field_filter(field, assoc=nil)
918 options = field.query_filter_options(self)
918 options = field.query_filter_options(self)
919 if field.format.target_class && field.format.target_class <= User
919 if field.format.target_class && field.format.target_class <= User
920 if options[:values].is_a?(Array) && User.current.logged?
920 if options[:values].is_a?(Array) && User.current.logged?
921 options[:values].unshift ["<< #{l(:label_me)} >>", "me"]
921 options[:values].unshift ["<< #{l(:label_me)} >>", "me"]
922 end
922 end
923 end
923 end
924
924
925 filter_id = "cf_#{field.id}"
925 filter_id = "cf_#{field.id}"
926 filter_name = field.name
926 filter_name = field.name
927 if assoc.present?
927 if assoc.present?
928 filter_id = "#{assoc}.#{filter_id}"
928 filter_id = "#{assoc}.#{filter_id}"
929 filter_name = l("label_attribute_of_#{assoc}", :name => filter_name)
929 filter_name = l("label_attribute_of_#{assoc}", :name => filter_name)
930 end
930 end
931 add_available_filter filter_id, options.merge({
931 add_available_filter filter_id, options.merge({
932 :name => filter_name,
932 :name => filter_name,
933 :field => field
933 :field => field
934 })
934 })
935 end
935 end
936
936
937 # Adds filters for the given custom fields scope
937 # Adds filters for the given custom fields scope
938 def add_custom_fields_filters(scope, assoc=nil)
938 def add_custom_fields_filters(scope, assoc=nil)
939 scope.visible.where(:is_filter => true).sorted.each do |field|
939 scope.visible.where(:is_filter => true).sorted.each do |field|
940 add_custom_field_filter(field, assoc)
940 add_custom_field_filter(field, assoc)
941 end
941 end
942 end
942 end
943
943
944 # Adds filters for the given associations custom fields
944 # Adds filters for the given associations custom fields
945 def add_associations_custom_fields_filters(*associations)
945 def add_associations_custom_fields_filters(*associations)
946 fields_by_class = CustomField.visible.where(:is_filter => true).group_by(&:class)
946 fields_by_class = CustomField.visible.where(:is_filter => true).group_by(&:class)
947 associations.each do |assoc|
947 associations.each do |assoc|
948 association_klass = queried_class.reflect_on_association(assoc).klass
948 association_klass = queried_class.reflect_on_association(assoc).klass
949 fields_by_class.each do |field_class, fields|
949 fields_by_class.each do |field_class, fields|
950 if field_class.customized_class <= association_klass
950 if field_class.customized_class <= association_klass
951 fields.sort.each do |field|
951 fields.sort.each do |field|
952 add_custom_field_filter(field, assoc)
952 add_custom_field_filter(field, assoc)
953 end
953 end
954 end
954 end
955 end
955 end
956 end
956 end
957 end
957 end
958
958
959 def quoted_time(time, is_custom_filter)
959 def quoted_time(time, is_custom_filter)
960 if is_custom_filter
960 if is_custom_filter
961 # Custom field values are stored as strings in the DB
961 # Custom field values are stored as strings in the DB
962 # using this format that does not depend on DB date representation
962 # using this format that does not depend on DB date representation
963 time.strftime("%Y-%m-%d %H:%M:%S")
963 time.strftime("%Y-%m-%d %H:%M:%S")
964 else
964 else
965 self.class.connection.quoted_date(time)
965 self.class.connection.quoted_date(time)
966 end
966 end
967 end
967 end
968
968
969 # Returns a SQL clause for a date or datetime field.
969 # Returns a SQL clause for a date or datetime field.
970 def date_clause(table, field, from, to, is_custom_filter)
970 def date_clause(table, field, from, to, is_custom_filter)
971 s = []
971 s = []
972 if from
972 if from
973 if from.is_a?(Date)
973 if from.is_a?(Date)
974 from = Time.local(from.year, from.month, from.day).yesterday.end_of_day
974 from = Time.local(from.year, from.month, from.day).yesterday.end_of_day
975 else
975 else
976 from = from - 1 # second
976 from = from - 1 # second
977 end
977 end
978 if self.class.default_timezone == :utc
978 if self.class.default_timezone == :utc
979 from = from.utc
979 from = from.utc
980 end
980 end
981 s << ("#{table}.#{field} > '%s'" % [quoted_time(from, is_custom_filter)])
981 s << ("#{table}.#{field} > '%s'" % [quoted_time(from, is_custom_filter)])
982 end
982 end
983 if to
983 if to
984 if to.is_a?(Date)
984 if to.is_a?(Date)
985 to = Time.local(to.year, to.month, to.day).end_of_day
985 to = Time.local(to.year, to.month, to.day).end_of_day
986 end
986 end
987 if self.class.default_timezone == :utc
987 if self.class.default_timezone == :utc
988 to = to.utc
988 to = to.utc
989 end
989 end
990 s << ("#{table}.#{field} <= '%s'" % [quoted_time(to, is_custom_filter)])
990 s << ("#{table}.#{field} <= '%s'" % [quoted_time(to, is_custom_filter)])
991 end
991 end
992 s.join(' AND ')
992 s.join(' AND ')
993 end
993 end
994
994
995 # Returns a SQL clause for a date or datetime field using relative dates.
995 # Returns a SQL clause for a date or datetime field using relative dates.
996 def relative_date_clause(table, field, days_from, days_to, is_custom_filter)
996 def relative_date_clause(table, field, days_from, days_to, is_custom_filter)
997 date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil), is_custom_filter)
997 date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil), is_custom_filter)
998 end
998 end
999
999
1000 # Returns a Date or Time from the given filter value
1000 # Returns a Date or Time from the given filter value
1001 def parse_date(arg)
1001 def parse_date(arg)
1002 if arg.to_s =~ /\A\d{4}-\d{2}-\d{2}T/
1002 if arg.to_s =~ /\A\d{4}-\d{2}-\d{2}T/
1003 Time.parse(arg) rescue nil
1003 Time.parse(arg) rescue nil
1004 else
1004 else
1005 Date.parse(arg) rescue nil
1005 Date.parse(arg) rescue nil
1006 end
1006 end
1007 end
1007 end
1008
1008
1009 # Additional joins required for the given sort options
1009 # Additional joins required for the given sort options
1010 def joins_for_order_statement(order_options)
1010 def joins_for_order_statement(order_options)
1011 joins = []
1011 joins = []
1012
1012
1013 if order_options
1013 if order_options
1014 if order_options.include?('authors')
1014 if order_options.include?('authors')
1015 joins << "LEFT OUTER JOIN #{User.table_name} authors ON authors.id = #{queried_table_name}.author_id"
1015 joins << "LEFT OUTER JOIN #{User.table_name} authors ON authors.id = #{queried_table_name}.author_id"
1016 end
1016 end
1017 order_options.scan(/cf_\d+/).uniq.each do |name|
1017 order_options.scan(/cf_\d+/).uniq.each do |name|
1018 column = available_columns.detect {|c| c.name.to_s == name}
1018 column = available_columns.detect {|c| c.name.to_s == name}
1019 join = column && column.custom_field.join_for_order_statement
1019 join = column && column.custom_field.join_for_order_statement
1020 if join
1020 if join
1021 joins << join
1021 joins << join
1022 end
1022 end
1023 end
1023 end
1024 end
1024 end
1025
1025
1026 joins.any? ? joins.join(' ') : nil
1026 joins.any? ? joins.join(' ') : nil
1027 end
1027 end
1028 end
1028 end
General Comments 0
You need to be logged in to leave comments. Login now