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