annotate .svn/pristine/51/51f64479905fa6ebe754a8ef36329d97b292d2ad.svn-base @ 1519:afce8026aaeb redmine-2.4-integration

Merge from branch "live"
author Chris Cannam
date Tue, 09 Sep 2014 09:34:53 +0100
parents cbb26bc654de
children
rev   line source
Chris@909 1 # Redmine - project management software
Chris@909 2 # Copyright (C) 2006-2011 Jean-Philippe Lang
Chris@909 3 #
Chris@909 4 # This program is free software; you can redistribute it and/or
Chris@909 5 # modify it under the terms of the GNU General Public License
Chris@909 6 # as published by the Free Software Foundation; either version 2
Chris@909 7 # of the License, or (at your option) any later version.
Chris@909 8 #
Chris@909 9 # This program is distributed in the hope that it will be useful,
Chris@909 10 # but WITHOUT ANY WARRANTY; without even the implied warranty of
Chris@909 11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
Chris@909 12 # GNU General Public License for more details.
Chris@909 13 #
Chris@909 14 # You should have received a copy of the GNU General Public License
Chris@909 15 # along with this program; if not, write to the Free Software
Chris@909 16 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
Chris@909 17
Chris@909 18 class QueryColumn
Chris@909 19 attr_accessor :name, :sortable, :groupable, :default_order
Chris@909 20 include Redmine::I18n
Chris@909 21
Chris@909 22 def initialize(name, options={})
Chris@909 23 self.name = name
Chris@909 24 self.sortable = options[:sortable]
Chris@909 25 self.groupable = options[:groupable] || false
Chris@909 26 if groupable == true
Chris@909 27 self.groupable = name.to_s
Chris@909 28 end
Chris@909 29 self.default_order = options[:default_order]
Chris@909 30 @caption_key = options[:caption] || "field_#{name}"
Chris@909 31 end
Chris@909 32
Chris@909 33 def caption
Chris@909 34 l(@caption_key)
Chris@909 35 end
Chris@909 36
Chris@909 37 # Returns true if the column is sortable, otherwise false
Chris@909 38 def sortable?
Chris@909 39 !@sortable.nil?
Chris@909 40 end
Chris@909 41
Chris@909 42 def sortable
Chris@909 43 @sortable.is_a?(Proc) ? @sortable.call : @sortable
Chris@909 44 end
Chris@909 45
Chris@909 46 def value(issue)
Chris@909 47 issue.send name
Chris@909 48 end
Chris@909 49
Chris@909 50 def css_classes
Chris@909 51 name
Chris@909 52 end
Chris@909 53 end
Chris@909 54
Chris@909 55 class QueryCustomFieldColumn < QueryColumn
Chris@909 56
Chris@909 57 def initialize(custom_field)
Chris@909 58 self.name = "cf_#{custom_field.id}".to_sym
Chris@909 59 self.sortable = custom_field.order_statement || false
Chris@909 60 if %w(list date bool int).include?(custom_field.field_format)
Chris@909 61 self.groupable = custom_field.order_statement
Chris@909 62 end
Chris@909 63 self.groupable ||= false
Chris@909 64 @cf = custom_field
Chris@909 65 end
Chris@909 66
Chris@909 67 def caption
Chris@909 68 @cf.name
Chris@909 69 end
Chris@909 70
Chris@909 71 def custom_field
Chris@909 72 @cf
Chris@909 73 end
Chris@909 74
Chris@909 75 def value(issue)
Chris@909 76 cv = issue.custom_values.detect {|v| v.custom_field_id == @cf.id}
Chris@909 77 cv && @cf.cast_value(cv.value)
Chris@909 78 end
Chris@909 79
Chris@909 80 def css_classes
Chris@909 81 @css_classes ||= "#{name} #{@cf.field_format}"
Chris@909 82 end
Chris@909 83 end
Chris@909 84
Chris@909 85 class Query < ActiveRecord::Base
Chris@909 86 class StatementInvalid < ::ActiveRecord::StatementInvalid
Chris@909 87 end
Chris@909 88
Chris@909 89 belongs_to :project
Chris@909 90 belongs_to :user
Chris@909 91 serialize :filters
Chris@909 92 serialize :column_names
Chris@909 93 serialize :sort_criteria, Array
Chris@909 94
Chris@909 95 attr_protected :project_id, :user_id
Chris@909 96
Chris@909 97 validates_presence_of :name, :on => :save
Chris@909 98 validates_length_of :name, :maximum => 255
Chris@909 99 validate :validate_query_filters
Chris@909 100
Chris@909 101 @@operators = { "=" => :label_equals,
Chris@909 102 "!" => :label_not_equals,
Chris@909 103 "o" => :label_open_issues,
Chris@909 104 "c" => :label_closed_issues,
Chris@909 105 "!*" => :label_none,
Chris@909 106 "*" => :label_all,
Chris@909 107 ">=" => :label_greater_or_equal,
Chris@909 108 "<=" => :label_less_or_equal,
Chris@909 109 "><" => :label_between,
Chris@909 110 "<t+" => :label_in_less_than,
Chris@909 111 ">t+" => :label_in_more_than,
Chris@909 112 "t+" => :label_in,
Chris@909 113 "t" => :label_today,
Chris@909 114 "w" => :label_this_week,
Chris@909 115 ">t-" => :label_less_than_ago,
Chris@909 116 "<t-" => :label_more_than_ago,
Chris@909 117 "t-" => :label_ago,
Chris@909 118 "~" => :label_contains,
Chris@909 119 "!~" => :label_not_contains }
Chris@909 120
Chris@909 121 cattr_reader :operators
Chris@909 122
Chris@909 123 @@operators_by_filter_type = { :list => [ "=", "!" ],
Chris@909 124 :list_status => [ "o", "=", "!", "c", "*" ],
Chris@909 125 :list_optional => [ "=", "!", "!*", "*" ],
Chris@909 126 :list_subprojects => [ "*", "!*", "=" ],
Chris@909 127 :date => [ "=", ">=", "<=", "><", "<t+", ">t+", "t+", "t", "w", ">t-", "<t-", "t-", "!*", "*" ],
Chris@909 128 :date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "t-", "t", "w", "!*", "*" ],
Chris@909 129 :string => [ "=", "~", "!", "!~" ],
Chris@909 130 :text => [ "~", "!~" ],
Chris@909 131 :integer => [ "=", ">=", "<=", "><", "!*", "*" ],
Chris@909 132 :float => [ "=", ">=", "<=", "><", "!*", "*" ] }
Chris@909 133
Chris@909 134 cattr_reader :operators_by_filter_type
Chris@909 135
Chris@909 136 @@available_columns = [
Chris@909 137 QueryColumn.new(:project, :sortable => "#{Project.table_name}.name", :groupable => true),
Chris@909 138 QueryColumn.new(:tracker, :sortable => "#{Tracker.table_name}.position", :groupable => true),
Chris@909 139 QueryColumn.new(:parent, :sortable => ["#{Issue.table_name}.root_id", "#{Issue.table_name}.lft ASC"], :default_order => 'desc', :caption => :field_parent_issue),
Chris@909 140 QueryColumn.new(:status, :sortable => "#{IssueStatus.table_name}.position", :groupable => true),
Chris@909 141 QueryColumn.new(:priority, :sortable => "#{IssuePriority.table_name}.position", :default_order => 'desc', :groupable => true),
Chris@909 142 QueryColumn.new(:subject, :sortable => "#{Issue.table_name}.subject"),
Chris@909 143 QueryColumn.new(:author, :sortable => lambda {User.fields_for_order_statement("authors")}, :groupable => true),
Chris@909 144 QueryColumn.new(:assigned_to, :sortable => lambda {User.fields_for_order_statement}, :groupable => true),
Chris@909 145 QueryColumn.new(:updated_on, :sortable => "#{Issue.table_name}.updated_on", :default_order => 'desc'),
Chris@909 146 QueryColumn.new(:category, :sortable => "#{IssueCategory.table_name}.name", :groupable => true),
Chris@909 147 QueryColumn.new(:fixed_version, :sortable => ["#{Version.table_name}.effective_date", "#{Version.table_name}.name"], :default_order => 'desc', :groupable => true),
Chris@909 148 QueryColumn.new(:start_date, :sortable => "#{Issue.table_name}.start_date"),
Chris@909 149 QueryColumn.new(:due_date, :sortable => "#{Issue.table_name}.due_date"),
Chris@909 150 QueryColumn.new(:estimated_hours, :sortable => "#{Issue.table_name}.estimated_hours"),
Chris@909 151 QueryColumn.new(:done_ratio, :sortable => "#{Issue.table_name}.done_ratio", :groupable => true),
Chris@909 152 QueryColumn.new(:created_on, :sortable => "#{Issue.table_name}.created_on", :default_order => 'desc'),
Chris@909 153 ]
Chris@909 154 cattr_reader :available_columns
Chris@909 155
Chris@909 156 named_scope :visible, lambda {|*args|
Chris@909 157 user = args.shift || User.current
Chris@909 158 base = Project.allowed_to_condition(user, :view_issues, *args)
Chris@909 159 user_id = user.logged? ? user.id : 0
Chris@909 160 {
Chris@909 161 :conditions => ["(#{table_name}.project_id IS NULL OR (#{base})) AND (#{table_name}.is_public = ? OR #{table_name}.user_id = ?)", true, user_id],
Chris@909 162 :include => :project
Chris@909 163 }
Chris@909 164 }
Chris@909 165
Chris@909 166 def initialize(attributes = nil)
Chris@909 167 super attributes
Chris@909 168 self.filters ||= { 'status_id' => {:operator => "o", :values => [""]} }
Chris@909 169 end
Chris@909 170
Chris@909 171 def after_initialize
Chris@909 172 # Store the fact that project is nil (used in #editable_by?)
Chris@909 173 @is_for_all = project.nil?
Chris@909 174 end
Chris@909 175
Chris@909 176 def validate_query_filters
Chris@909 177 filters.each_key do |field|
Chris@909 178 if values_for(field)
Chris@909 179 case type_for(field)
Chris@909 180 when :integer
Chris@909 181 errors.add(label_for(field), :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^\d+$/) }
Chris@909 182 when :float
Chris@909 183 errors.add(label_for(field), :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^\d+(\.\d*)?$/) }
Chris@909 184 when :date, :date_past
Chris@909 185 case operator_for(field)
Chris@909 186 when "=", ">=", "<=", "><"
Chris@909 187 errors.add(label_for(field), :invalid) if values_for(field).detect {|v| v.present? && (!v.match(/^\d{4}-\d{2}-\d{2}$/) || (Date.parse(v) rescue nil).nil?) }
Chris@909 188 when ">t-", "<t-", "t-"
Chris@909 189 errors.add(label_for(field), :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^\d+$/) }
Chris@909 190 end
Chris@909 191 end
Chris@909 192 end
Chris@909 193
Chris@909 194 errors.add label_for(field), :blank unless
Chris@909 195 # filter requires one or more values
Chris@909 196 (values_for(field) and !values_for(field).first.blank?) or
Chris@909 197 # filter doesn't require any value
Chris@909 198 ["o", "c", "!*", "*", "t", "w"].include? operator_for(field)
Chris@909 199 end if filters
Chris@909 200 end
Chris@909 201
Chris@909 202 # Returns true if the query is visible to +user+ or the current user.
Chris@909 203 def visible?(user=User.current)
Chris@909 204 (project.nil? || user.allowed_to?(:view_issues, project)) && (self.is_public? || self.user_id == user.id)
Chris@909 205 end
Chris@909 206
Chris@909 207 def editable_by?(user)
Chris@909 208 return false unless user
Chris@909 209 # Admin can edit them all and regular users can edit their private queries
Chris@909 210 return true if user.admin? || (!is_public && self.user_id == user.id)
Chris@909 211 # Members can not edit public queries that are for all project (only admin is allowed to)
Chris@909 212 is_public && !@is_for_all && user.allowed_to?(:manage_public_queries, project)
Chris@909 213 end
Chris@909 214
Chris@909 215 def available_filters
Chris@909 216 return @available_filters if @available_filters
Chris@909 217
Chris@909 218 trackers = project.nil? ? Tracker.find(:all, :order => 'position') : project.rolled_up_trackers
Chris@909 219
Chris@909 220 @available_filters = { "status_id" => { :type => :list_status, :order => 1, :values => IssueStatus.find(:all, :order => 'position').collect{|s| [s.name, s.id.to_s] } },
Chris@909 221 "tracker_id" => { :type => :list, :order => 2, :values => trackers.collect{|s| [s.name, s.id.to_s] } },
Chris@909 222 "priority_id" => { :type => :list, :order => 3, :values => IssuePriority.all.collect{|s| [s.name, s.id.to_s] } },
Chris@909 223 "subject" => { :type => :text, :order => 8 },
Chris@909 224 "created_on" => { :type => :date_past, :order => 9 },
Chris@909 225 "updated_on" => { :type => :date_past, :order => 10 },
Chris@909 226 "start_date" => { :type => :date, :order => 11 },
Chris@909 227 "due_date" => { :type => :date, :order => 12 },
Chris@909 228 "estimated_hours" => { :type => :float, :order => 13 },
Chris@909 229 "done_ratio" => { :type => :integer, :order => 14 }}
Chris@909 230
Chris@909 231 principals = []
Chris@909 232 if project
Chris@909 233 principals += project.principals.sort
Chris@909 234 else
Chris@909 235 all_projects = Project.visible.all
Chris@909 236 if all_projects.any?
Chris@909 237 # members of visible projects
Chris@909 238 principals += Principal.active.find(:all, :conditions => ["#{User.table_name}.id IN (SELECT DISTINCT user_id FROM members WHERE project_id IN (?))", all_projects.collect(&:id)]).sort
Chris@909 239
Chris@909 240 # project filter
Chris@909 241 project_values = []
Chris@909 242 Project.project_tree(all_projects) do |p, level|
Chris@909 243 prefix = (level > 0 ? ('--' * level + ' ') : '')
Chris@909 244 project_values << ["#{prefix}#{p.name}", p.id.to_s]
Chris@909 245 end
Chris@909 246 @available_filters["project_id"] = { :type => :list, :order => 1, :values => project_values} unless project_values.empty?
Chris@909 247 end
Chris@909 248 end
Chris@909 249 users = principals.select {|p| p.is_a?(User)}
Chris@909 250
Chris@909 251 assigned_to_values = []
Chris@909 252 assigned_to_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
Chris@909 253 assigned_to_values += (Setting.issue_group_assignment? ? principals : users).collect{|s| [s.name, s.id.to_s] }
Chris@909 254 @available_filters["assigned_to_id"] = { :type => :list_optional, :order => 4, :values => assigned_to_values } unless assigned_to_values.empty?
Chris@909 255
Chris@909 256 author_values = []
Chris@909 257 author_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
Chris@909 258 author_values += users.collect{|s| [s.name, s.id.to_s] }
Chris@909 259 @available_filters["author_id"] = { :type => :list, :order => 5, :values => author_values } unless author_values.empty?
Chris@909 260
Chris@909 261 group_values = Group.all.collect {|g| [g.name, g.id.to_s] }
Chris@909 262 @available_filters["member_of_group"] = { :type => :list_optional, :order => 6, :values => group_values } unless group_values.empty?
Chris@909 263
Chris@909 264 role_values = Role.givable.collect {|r| [r.name, r.id.to_s] }
Chris@909 265 @available_filters["assigned_to_role"] = { :type => :list_optional, :order => 7, :values => role_values } unless role_values.empty?
Chris@909 266
Chris@909 267 if User.current.logged?
Chris@909 268 @available_filters["watcher_id"] = { :type => :list, :order => 15, :values => [["<< #{l(:label_me)} >>", "me"]] }
Chris@909 269 end
Chris@909 270
Chris@909 271 if project
Chris@909 272 # project specific filters
Chris@909 273 categories = project.issue_categories.all
Chris@909 274 unless categories.empty?
Chris@909 275 @available_filters["category_id"] = { :type => :list_optional, :order => 6, :values => categories.collect{|s| [s.name, s.id.to_s] } }
Chris@909 276 end
Chris@909 277 versions = project.shared_versions.all
Chris@909 278 unless versions.empty?
Chris@909 279 @available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }
Chris@909 280 end
Chris@909 281 unless project.leaf?
Chris@909 282 subprojects = project.descendants.visible.all
Chris@909 283 unless subprojects.empty?
Chris@909 284 @available_filters["subproject_id"] = { :type => :list_subprojects, :order => 13, :values => subprojects.collect{|s| [s.name, s.id.to_s] } }
Chris@909 285 end
Chris@909 286 end
Chris@909 287 add_custom_fields_filters(project.all_issue_custom_fields)
Chris@909 288 else
Chris@909 289 # global filters for cross project issue list
Chris@909 290 system_shared_versions = Version.visible.find_all_by_sharing('system')
Chris@909 291 unless system_shared_versions.empty?
Chris@909 292 @available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => system_shared_versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }
Chris@909 293 end
Chris@909 294 add_custom_fields_filters(IssueCustomField.find(:all, :conditions => {:is_filter => true, :is_for_all => true}))
Chris@909 295 end
Chris@909 296 @available_filters
Chris@909 297 end
Chris@909 298
Chris@909 299 def add_filter(field, operator, values)
Chris@909 300 # values must be an array
Chris@909 301 return unless values.nil? || values.is_a?(Array)
Chris@909 302 # check if field is defined as an available filter
Chris@909 303 if available_filters.has_key? field
Chris@909 304 filter_options = available_filters[field]
Chris@909 305 # check if operator is allowed for that filter
Chris@909 306 #if @@operators_by_filter_type[filter_options[:type]].include? operator
Chris@909 307 # allowed_values = values & ([""] + (filter_options[:values] || []).collect {|val| val[1]})
Chris@909 308 # filters[field] = {:operator => operator, :values => allowed_values } if (allowed_values.first and !allowed_values.first.empty?) or ["o", "c", "!*", "*", "t"].include? operator
Chris@909 309 #end
Chris@909 310 filters[field] = {:operator => operator, :values => (values || [''])}
Chris@909 311 end
Chris@909 312 end
Chris@909 313
Chris@909 314 def add_short_filter(field, expression)
Chris@909 315 return unless expression && available_filters.has_key?(field)
Chris@909 316 field_type = available_filters[field][:type]
Chris@909 317 @@operators_by_filter_type[field_type].sort.reverse.detect do |operator|
Chris@909 318 next unless expression =~ /^#{Regexp.escape(operator)}(.*)$/
Chris@909 319 add_filter field, operator, $1.present? ? $1.split('|') : ['']
Chris@909 320 end || add_filter(field, '=', expression.split('|'))
Chris@909 321 end
Chris@909 322
Chris@909 323 # Add multiple filters using +add_filter+
Chris@909 324 def add_filters(fields, operators, values)
Chris@909 325 if fields.is_a?(Array) && operators.is_a?(Hash) && (values.nil? || values.is_a?(Hash))
Chris@909 326 fields.each do |field|
Chris@909 327 add_filter(field, operators[field], values && values[field])
Chris@909 328 end
Chris@909 329 end
Chris@909 330 end
Chris@909 331
Chris@909 332 def has_filter?(field)
Chris@909 333 filters and filters[field]
Chris@909 334 end
Chris@909 335
Chris@909 336 def type_for(field)
Chris@909 337 available_filters[field][:type] if available_filters.has_key?(field)
Chris@909 338 end
Chris@909 339
Chris@909 340 def operator_for(field)
Chris@909 341 has_filter?(field) ? filters[field][:operator] : nil
Chris@909 342 end
Chris@909 343
Chris@909 344 def values_for(field)
Chris@909 345 has_filter?(field) ? filters[field][:values] : nil
Chris@909 346 end
Chris@909 347
Chris@909 348 def value_for(field, index=0)
Chris@909 349 (values_for(field) || [])[index]
Chris@909 350 end
Chris@909 351
Chris@909 352 def label_for(field)
Chris@909 353 label = available_filters[field][:name] if available_filters.has_key?(field)
Chris@909 354 label ||= field.gsub(/\_id$/, "")
Chris@909 355 end
Chris@909 356
Chris@909 357 def available_columns
Chris@909 358 return @available_columns if @available_columns
Chris@909 359 @available_columns = ::Query.available_columns
Chris@909 360 @available_columns += (project ?
Chris@909 361 project.all_issue_custom_fields :
Chris@909 362 IssueCustomField.find(:all)
Chris@909 363 ).collect {|cf| QueryCustomFieldColumn.new(cf) }
Chris@909 364 end
Chris@909 365
Chris@909 366 def self.available_columns=(v)
Chris@909 367 self.available_columns = (v)
Chris@909 368 end
Chris@909 369
Chris@909 370 def self.add_available_column(column)
Chris@909 371 self.available_columns << (column) if column.is_a?(QueryColumn)
Chris@909 372 end
Chris@909 373
Chris@909 374 # Returns an array of columns that can be used to group the results
Chris@909 375 def groupable_columns
Chris@909 376 available_columns.select {|c| c.groupable}
Chris@909 377 end
Chris@909 378
Chris@909 379 # Returns a Hash of columns and the key for sorting
Chris@909 380 def sortable_columns
Chris@909 381 {'id' => "#{Issue.table_name}.id"}.merge(available_columns.inject({}) {|h, column|
Chris@909 382 h[column.name.to_s] = column.sortable
Chris@909 383 h
Chris@909 384 })
Chris@909 385 end
Chris@909 386
Chris@909 387 def columns
Chris@909 388 # preserve the column_names order
Chris@909 389 (has_default_columns? ? default_columns_names : column_names).collect do |name|
Chris@909 390 available_columns.find { |col| col.name == name }
Chris@909 391 end.compact
Chris@909 392 end
Chris@909 393
Chris@909 394 def default_columns_names
Chris@909 395 @default_columns_names ||= begin
Chris@909 396 default_columns = Setting.issue_list_default_columns.map(&:to_sym)
Chris@909 397
Chris@909 398 project.present? ? default_columns : [:project] | default_columns
Chris@909 399 end
Chris@909 400 end
Chris@909 401
Chris@909 402 def column_names=(names)
Chris@909 403 if names
Chris@909 404 names = names.select {|n| n.is_a?(Symbol) || !n.blank? }
Chris@909 405 names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }
Chris@909 406 # Set column_names to nil if default columns
Chris@909 407 if names == default_columns_names
Chris@909 408 names = nil
Chris@909 409 end
Chris@909 410 end
Chris@909 411 write_attribute(:column_names, names)
Chris@909 412 end
Chris@909 413
Chris@909 414 def has_column?(column)
Chris@909 415 column_names && column_names.include?(column.name)
Chris@909 416 end
Chris@909 417
Chris@909 418 def has_default_columns?
Chris@909 419 column_names.nil? || column_names.empty?
Chris@909 420 end
Chris@909 421
Chris@909 422 def sort_criteria=(arg)
Chris@909 423 c = []
Chris@909 424 if arg.is_a?(Hash)
Chris@909 425 arg = arg.keys.sort.collect {|k| arg[k]}
Chris@909 426 end
Chris@909 427 c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, o == 'desc' ? o : 'asc']}
Chris@909 428 write_attribute(:sort_criteria, c)
Chris@909 429 end
Chris@909 430
Chris@909 431 def sort_criteria
Chris@909 432 read_attribute(:sort_criteria) || []
Chris@909 433 end
Chris@909 434
Chris@909 435 def sort_criteria_key(arg)
Chris@909 436 sort_criteria && sort_criteria[arg] && sort_criteria[arg].first
Chris@909 437 end
Chris@909 438
Chris@909 439 def sort_criteria_order(arg)
Chris@909 440 sort_criteria && sort_criteria[arg] && sort_criteria[arg].last
Chris@909 441 end
Chris@909 442
Chris@909 443 # Returns the SQL sort order that should be prepended for grouping
Chris@909 444 def group_by_sort_order
Chris@909 445 if grouped? && (column = group_by_column)
Chris@909 446 column.sortable.is_a?(Array) ?
Chris@909 447 column.sortable.collect {|s| "#{s} #{column.default_order}"}.join(',') :
Chris@909 448 "#{column.sortable} #{column.default_order}"
Chris@909 449 end
Chris@909 450 end
Chris@909 451
Chris@909 452 # Returns true if the query is a grouped query
Chris@909 453 def grouped?
Chris@909 454 !group_by_column.nil?
Chris@909 455 end
Chris@909 456
Chris@909 457 def group_by_column
Chris@909 458 groupable_columns.detect {|c| c.groupable && c.name.to_s == group_by}
Chris@909 459 end
Chris@909 460
Chris@909 461 def group_by_statement
Chris@909 462 group_by_column.try(:groupable)
Chris@909 463 end
Chris@909 464
Chris@909 465 def project_statement
Chris@909 466 project_clauses = []
Chris@909 467 if project && !project.descendants.active.empty?
Chris@909 468 ids = [project.id]
Chris@909 469 if has_filter?("subproject_id")
Chris@909 470 case operator_for("subproject_id")
Chris@909 471 when '='
Chris@909 472 # include the selected subprojects
Chris@909 473 ids += values_for("subproject_id").each(&:to_i)
Chris@909 474 when '!*'
Chris@909 475 # main project only
Chris@909 476 else
Chris@909 477 # all subprojects
Chris@909 478 ids += project.descendants.collect(&:id)
Chris@909 479 end
Chris@909 480 elsif Setting.display_subprojects_issues?
Chris@909 481 ids += project.descendants.collect(&:id)
Chris@909 482 end
Chris@909 483 project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
Chris@909 484 elsif project
Chris@909 485 project_clauses << "#{Project.table_name}.id = %d" % project.id
Chris@909 486 end
Chris@909 487 project_clauses.any? ? project_clauses.join(' AND ') : nil
Chris@909 488 end
Chris@909 489
Chris@909 490 def statement
Chris@909 491 # filters clauses
Chris@909 492 filters_clauses = []
Chris@909 493 filters.each_key do |field|
Chris@909 494 next if field == "subproject_id"
Chris@909 495 v = values_for(field).clone
Chris@909 496 next unless v and !v.empty?
Chris@909 497 operator = operator_for(field)
Chris@909 498
Chris@909 499 # "me" value subsitution
Chris@909 500 if %w(assigned_to_id author_id watcher_id).include?(field)
Chris@909 501 if v.delete("me")
Chris@909 502 if User.current.logged?
Chris@909 503 v.push(User.current.id.to_s)
Chris@909 504 v += User.current.group_ids.map(&:to_s) if field == 'assigned_to_id'
Chris@909 505 else
Chris@909 506 v.push("0")
Chris@909 507 end
Chris@909 508 end
Chris@909 509 end
Chris@909 510
Chris@909 511 if field =~ /^cf_(\d+)$/
Chris@909 512 # custom field
Chris@909 513 filters_clauses << sql_for_custom_field(field, operator, v, $1)
Chris@909 514 elsif respond_to?("sql_for_#{field}_field")
Chris@909 515 # specific statement
Chris@909 516 filters_clauses << send("sql_for_#{field}_field", field, operator, v)
Chris@909 517 else
Chris@909 518 # regular field
Chris@909 519 filters_clauses << '(' + sql_for_field(field, operator, v, Issue.table_name, field) + ')'
Chris@909 520 end
Chris@909 521 end if filters and valid?
Chris@909 522
Chris@909 523 filters_clauses << project_statement
Chris@909 524 filters_clauses.reject!(&:blank?)
Chris@909 525
Chris@909 526 filters_clauses.any? ? filters_clauses.join(' AND ') : nil
Chris@909 527 end
Chris@909 528
Chris@909 529 # Returns the issue count
Chris@909 530 def issue_count
Chris@909 531 Issue.visible.count(:include => [:status, :project], :conditions => statement)
Chris@909 532 rescue ::ActiveRecord::StatementInvalid => e
Chris@909 533 raise StatementInvalid.new(e.message)
Chris@909 534 end
Chris@909 535
Chris@909 536 # Returns the issue count by group or nil if query is not grouped
Chris@909 537 def issue_count_by_group
Chris@909 538 r = nil
Chris@909 539 if grouped?
Chris@909 540 begin
Chris@909 541 # Rails will raise an (unexpected) RecordNotFound if there's only a nil group value
Chris@909 542 r = Issue.visible.count(:group => group_by_statement, :include => [:status, :project], :conditions => statement)
Chris@909 543 rescue ActiveRecord::RecordNotFound
Chris@909 544 r = {nil => issue_count}
Chris@909 545 end
Chris@909 546 c = group_by_column
Chris@909 547 if c.is_a?(QueryCustomFieldColumn)
Chris@909 548 r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
Chris@909 549 end
Chris@909 550 end
Chris@909 551 r
Chris@909 552 rescue ::ActiveRecord::StatementInvalid => e
Chris@909 553 raise StatementInvalid.new(e.message)
Chris@909 554 end
Chris@909 555
Chris@909 556 # Returns the issues
Chris@909 557 # Valid options are :order, :offset, :limit, :include, :conditions
Chris@909 558 def issues(options={})
Chris@909 559 order_option = [group_by_sort_order, options[:order]].reject {|s| s.blank?}.join(',')
Chris@909 560 order_option = nil if order_option.blank?
Chris@909 561
Chris@909 562 joins = (order_option && order_option.include?('authors')) ? "LEFT OUTER JOIN users authors ON authors.id = #{Issue.table_name}.author_id" : nil
Chris@909 563
Chris@909 564 Issue.visible.scoped(:conditions => options[:conditions]).find :all, :include => ([:status, :project] + (options[:include] || [])).uniq,
Chris@909 565 :conditions => statement,
Chris@909 566 :order => order_option,
Chris@909 567 :joins => joins,
Chris@909 568 :limit => options[:limit],
Chris@909 569 :offset => options[:offset]
Chris@909 570 rescue ::ActiveRecord::StatementInvalid => e
Chris@909 571 raise StatementInvalid.new(e.message)
Chris@909 572 end
Chris@909 573
Chris@909 574 # Returns the journals
Chris@909 575 # Valid options are :order, :offset, :limit
Chris@909 576 def journals(options={})
Chris@909 577 Journal.visible.find :all, :include => [:details, :user, {:issue => [:project, :author, :tracker, :status]}],
Chris@909 578 :conditions => statement,
Chris@909 579 :order => options[:order],
Chris@909 580 :limit => options[:limit],
Chris@909 581 :offset => options[:offset]
Chris@909 582 rescue ::ActiveRecord::StatementInvalid => e
Chris@909 583 raise StatementInvalid.new(e.message)
Chris@909 584 end
Chris@909 585
Chris@909 586 # Returns the versions
Chris@909 587 # Valid options are :conditions
Chris@909 588 def versions(options={})
Chris@909 589 Version.visible.scoped(:conditions => options[:conditions]).find :all, :include => :project, :conditions => project_statement
Chris@909 590 rescue ::ActiveRecord::StatementInvalid => e
Chris@909 591 raise StatementInvalid.new(e.message)
Chris@909 592 end
Chris@909 593
Chris@909 594 def sql_for_watcher_id_field(field, operator, value)
Chris@909 595 db_table = Watcher.table_name
Chris@909 596 "#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{db_table}.watchable_id FROM #{db_table} WHERE #{db_table}.watchable_type='Issue' AND " +
Chris@909 597 sql_for_field(field, '=', value, db_table, 'user_id') + ')'
Chris@909 598 end
Chris@909 599
Chris@909 600 def sql_for_member_of_group_field(field, operator, value)
Chris@909 601 if operator == '*' # Any group
Chris@909 602 groups = Group.all
Chris@909 603 operator = '=' # Override the operator since we want to find by assigned_to
Chris@909 604 elsif operator == "!*"
Chris@909 605 groups = Group.all
Chris@909 606 operator = '!' # Override the operator since we want to find by assigned_to
Chris@909 607 else
Chris@909 608 groups = Group.find_all_by_id(value)
Chris@909 609 end
Chris@909 610 groups ||= []
Chris@909 611
Chris@909 612 members_of_groups = groups.inject([]) {|user_ids, group|
Chris@909 613 if group && group.user_ids.present?
Chris@909 614 user_ids << group.user_ids
Chris@909 615 end
Chris@909 616 user_ids.flatten.uniq.compact
Chris@909 617 }.sort.collect(&:to_s)
Chris@909 618
Chris@909 619 '(' + sql_for_field("assigned_to_id", operator, members_of_groups, Issue.table_name, "assigned_to_id", false) + ')'
Chris@909 620 end
Chris@909 621
Chris@909 622 def sql_for_assigned_to_role_field(field, operator, value)
Chris@909 623 case operator
Chris@909 624 when "*", "!*" # Member / Not member
Chris@909 625 sw = operator == "!*" ? 'NOT' : ''
Chris@909 626 nl = operator == "!*" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''
Chris@909 627 "(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}" +
Chris@909 628 " WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id))"
Chris@909 629 when "=", "!"
Chris@909 630 role_cond = value.any? ?
Chris@909 631 "#{MemberRole.table_name}.role_id IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")" :
Chris@909 632 "1=0"
Chris@909 633
Chris@909 634 sw = operator == "!" ? 'NOT' : ''
Chris@909 635 nl = operator == "!" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''
Chris@909 636 "(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}, #{MemberRole.table_name}" +
Chris@909 637 " WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id AND #{Member.table_name}.id = #{MemberRole.table_name}.member_id AND #{role_cond}))"
Chris@909 638 end
Chris@909 639 end
Chris@909 640
Chris@909 641 private
Chris@909 642
Chris@909 643 def sql_for_custom_field(field, operator, value, custom_field_id)
Chris@909 644 db_table = CustomValue.table_name
Chris@909 645 db_field = 'value'
Chris@909 646 "#{Issue.table_name}.id IN (SELECT #{Issue.table_name}.id FROM #{Issue.table_name} LEFT OUTER JOIN #{db_table} ON #{db_table}.customized_type='Issue' AND #{db_table}.customized_id=#{Issue.table_name}.id AND #{db_table}.custom_field_id=#{custom_field_id} WHERE " +
Chris@909 647 sql_for_field(field, operator, value, db_table, db_field, true) + ')'
Chris@909 648 end
Chris@909 649
Chris@909 650 # Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+
Chris@909 651 def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)
Chris@909 652 sql = ''
Chris@909 653 case operator
Chris@909 654 when "="
Chris@909 655 if value.any?
Chris@909 656 case type_for(field)
Chris@909 657 when :date, :date_past
Chris@909 658 sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), (Date.parse(value.first) rescue nil))
Chris@909 659 when :integer
Chris@909 660 sql = "#{db_table}.#{db_field} = #{value.first.to_i}"
Chris@909 661 when :float
Chris@909 662 sql = "#{db_table}.#{db_field} BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5}"
Chris@909 663 else
Chris@909 664 sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")"
Chris@909 665 end
Chris@909 666 else
Chris@909 667 # IN an empty set
Chris@909 668 sql = "1=0"
Chris@909 669 end
Chris@909 670 when "!"
Chris@909 671 if value.any?
Chris@909 672 sql = "(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + "))"
Chris@909 673 else
Chris@909 674 # NOT IN an empty set
Chris@909 675 sql = "1=1"
Chris@909 676 end
Chris@909 677 when "!*"
Chris@909 678 sql = "#{db_table}.#{db_field} IS NULL"
Chris@909 679 sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter
Chris@909 680 when "*"
Chris@909 681 sql = "#{db_table}.#{db_field} IS NOT NULL"
Chris@909 682 sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
Chris@909 683 when ">="
Chris@909 684 if [:date, :date_past].include?(type_for(field))
Chris@909 685 sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), nil)
Chris@909 686 else
Chris@909 687 if is_custom_filter
Chris@909 688 sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) >= #{value.first.to_f}"
Chris@909 689 else
Chris@909 690 sql = "#{db_table}.#{db_field} >= #{value.first.to_f}"
Chris@909 691 end
Chris@909 692 end
Chris@909 693 when "<="
Chris@909 694 if [:date, :date_past].include?(type_for(field))
Chris@909 695 sql = date_clause(db_table, db_field, nil, (Date.parse(value.first) rescue nil))
Chris@909 696 else
Chris@909 697 if is_custom_filter
Chris@909 698 sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) <= #{value.first.to_f}"
Chris@909 699 else
Chris@909 700 sql = "#{db_table}.#{db_field} <= #{value.first.to_f}"
Chris@909 701 end
Chris@909 702 end
Chris@909 703 when "><"
Chris@909 704 if [:date, :date_past].include?(type_for(field))
Chris@909 705 sql = date_clause(db_table, db_field, (Date.parse(value[0]) rescue nil), (Date.parse(value[1]) rescue nil))
Chris@909 706 else
Chris@909 707 if is_custom_filter
Chris@909 708 sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
Chris@909 709 else
Chris@909 710 sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
Chris@909 711 end
Chris@909 712 end
Chris@909 713 when "o"
Chris@909 714 sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_false}" if field == "status_id"
Chris@909 715 when "c"
Chris@909 716 sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_true}" if field == "status_id"
Chris@909 717 when ">t-"
Chris@909 718 sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0)
Chris@909 719 when "<t-"
Chris@909 720 sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i)
Chris@909 721 when "t-"
Chris@909 722 sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i)
Chris@909 723 when ">t+"
Chris@909 724 sql = relative_date_clause(db_table, db_field, value.first.to_i, nil)
Chris@909 725 when "<t+"
Chris@909 726 sql = relative_date_clause(db_table, db_field, 0, value.first.to_i)
Chris@909 727 when "t+"
Chris@909 728 sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i)
Chris@909 729 when "t"
Chris@909 730 sql = relative_date_clause(db_table, db_field, 0, 0)
Chris@909 731 when "w"
Chris@909 732 first_day_of_week = l(:general_first_day_of_week).to_i
Chris@909 733 day_of_week = Date.today.cwday
Chris@909 734 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
Chris@909 735 sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6)
Chris@909 736 when "~"
Chris@909 737 sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
Chris@909 738 when "!~"
Chris@909 739 sql = "LOWER(#{db_table}.#{db_field}) NOT LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
Chris@909 740 else
Chris@909 741 raise "Unknown query operator #{operator}"
Chris@909 742 end
Chris@909 743
Chris@909 744 return sql
Chris@909 745 end
Chris@909 746
Chris@909 747 def add_custom_fields_filters(custom_fields)
Chris@909 748 @available_filters ||= {}
Chris@909 749
Chris@909 750 custom_fields.select(&:is_filter?).each do |field|
Chris@909 751 case field.field_format
Chris@909 752 when "text"
Chris@909 753 options = { :type => :text, :order => 20 }
Chris@909 754 when "list"
Chris@909 755 options = { :type => :list_optional, :values => field.possible_values, :order => 20}
Chris@909 756 when "date"
Chris@909 757 options = { :type => :date, :order => 20 }
Chris@909 758 when "bool"
Chris@909 759 options = { :type => :list, :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]], :order => 20 }
Chris@909 760 when "int"
Chris@909 761 options = { :type => :integer, :order => 20 }
Chris@909 762 when "float"
Chris@909 763 options = { :type => :float, :order => 20 }
Chris@909 764 when "user", "version"
Chris@909 765 next unless project
Chris@909 766 options = { :type => :list_optional, :values => field.possible_values_options(project), :order => 20}
Chris@909 767 else
Chris@909 768 options = { :type => :string, :order => 20 }
Chris@909 769 end
Chris@909 770 @available_filters["cf_#{field.id}"] = options.merge({ :name => field.name })
Chris@909 771 end
Chris@909 772 end
Chris@909 773
Chris@909 774 # Returns a SQL clause for a date or datetime field.
Chris@909 775 def date_clause(table, field, from, to)
Chris@909 776 s = []
Chris@909 777 if from
Chris@909 778 from_yesterday = from - 1
Chris@909 779 from_yesterday_utc = Time.gm(from_yesterday.year, from_yesterday.month, from_yesterday.day)
Chris@909 780 s << ("#{table}.#{field} > '%s'" % [connection.quoted_date(from_yesterday_utc.end_of_day)])
Chris@909 781 end
Chris@909 782 if to
Chris@909 783 to_utc = Time.gm(to.year, to.month, to.day)
Chris@909 784 s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date(to_utc.end_of_day)])
Chris@909 785 end
Chris@909 786 s.join(' AND ')
Chris@909 787 end
Chris@909 788
Chris@909 789 # Returns a SQL clause for a date or datetime field using relative dates.
Chris@909 790 def relative_date_clause(table, field, days_from, days_to)
Chris@909 791 date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil))
Chris@909 792 end
Chris@909 793 end