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