##// END OF EJS Templates
jro_apps » redmine
RSS
Refactor: use an ordered hash to store available filters and remove :order option (#13154)....
Jean-Philippe Lang -
r11142:0be82ea2c45e
parent child
Show More
Side by Side Unified
Context file:
r11142:0be82ea2c45e Loading diff...:
Show file before | Show file after | Hide comments
@@ -1,160 +1,139
1 1 # encoding: utf-8
2 2 #
3 3 # Redmine - project management software
4 4 # Copyright (C) 2006-2013 Jean-Philippe Lang
5 5 #
6 6 # This program is free software; you can redistribute it and/or
7 7 # modify it under the terms of the GNU General Public License
8 8 # as published by the Free Software Foundation; either version 2
9 9 # of the License, or (at your option) any later version.
10 10 #
11 11 # This program is distributed in the hope that it will be useful,
12 12 # but WITHOUT ANY WARRANTY; without even the implied warranty of
13 13 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 14 # GNU General Public License for more details.
15 15 #
16 16 # You should have received a copy of the GNU General Public License
17 17 # along with this program; if not, write to the Free Software
18 18 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
19 19
20 20 module QueriesHelper
21 21 def filters_options_for_select(query)
22 22 options_for_select(filters_options(query))
23 23 end
24 24
25 25 def filters_options(query)
26 26 options = [[]]
27 sorted_options = query.available_filters.sort do |a, b|
28 ord = 0
29 if !(a[1][:order] == 20 && b[1][:order] == 20)
30 ord = a[1][:order] <=> b[1][:order]
31 else
32 cn = (CustomField::CUSTOM_FIELDS_NAMES.index(a[1][:field].class.name) <=>
33 CustomField::CUSTOM_FIELDS_NAMES.index(b[1][:field].class.name))
34 if cn != 0
35 ord = cn
36 else
37 f = (a[1][:field] <=> b[1][:field])
38 if f != 0
39 ord = f
40 else
41 # assigned_to or author
42 ord = (a[0] <=> b[0])
43 end
44 end
45 end
46 ord
47 end
48 options += sorted_options.map do |field, field_options|
27 options += query.available_filters.map do |field, field_options|
49 28 [field_options[:name], field]
50 29 end
51 30 end
52 31
53 32 def available_block_columns_tags(query)
54 33 tags = ''.html_safe
55 34 query.available_block_columns.each do |column|
56 35 tags << content_tag('label', check_box_tag('c[]', column.name.to_s, query.has_column?(column)) + " #{column.caption}", :class => 'inline')
57 36 end
58 37 tags
59 38 end
60 39
61 40 def column_header(column)
62 41 column.sortable ? sort_header_tag(column.name.to_s, :caption => column.caption,
63 42 :default_order => column.default_order) :
64 43 content_tag('th', h(column.caption))
65 44 end
66 45
67 46 def column_content(column, issue)
68 47 value = column.value(issue)
69 48 if value.is_a?(Array)
70 49 value.collect {|v| column_value(column, issue, v)}.compact.join(', ').html_safe
71 50 else
72 51 column_value(column, issue, value)
73 52 end
74 53 end
75 54
76 55 def column_value(column, issue, value)
77 56 case value.class.name
78 57 when 'String'
79 58 if column.name == :subject
80 59 link_to(h(value), :controller => 'issues', :action => 'show', :id => issue)
81 60 elsif column.name == :description
82 61 issue.description? ? content_tag('div', textilizable(issue, :description), :class => "wiki") : ''
83 62 else
84 63 h(value)
85 64 end
86 65 when 'Time'
87 66 format_time(value)
88 67 when 'Date'
89 68 format_date(value)
90 69 when 'Fixnum'
91 70 if column.name == :done_ratio
92 71 progress_bar(value, :width => '80px')
93 72 else
94 73 value.to_s
95 74 end
96 75 when 'Float'
97 76 sprintf "%.2f", value
98 77 when 'User'
99 78 link_to_user value
100 79 when 'Project'
101 80 link_to_project value
102 81 when 'Version'
103 82 link_to(h(value), :controller => 'versions', :action => 'show', :id => value)
104 83 when 'TrueClass'
105 84 l(:general_text_Yes)
106 85 when 'FalseClass'
107 86 l(:general_text_No)
108 87 when 'Issue'
109 88 value.visible? ? link_to_issue(value) : "##{value.id}"
110 89 when 'IssueRelation'
111 90 other = value.other_issue(issue)
112 91 content_tag('span',
113 92 (l(value.label_for(issue)) + " " + link_to_issue(other, :subject => false, :tracker => false)).html_safe,
114 93 :class => value.css_classes_for(issue))
115 94 else
116 95 h(value)
117 96 end
118 97 end
119 98
120 99 # Retrieve query from session or build a new query
121 100 def retrieve_query
122 101 if !params[:query_id].blank?
123 102 cond = "project_id IS NULL"
124 103 cond << " OR project_id = #{@project.id}" if @project
125 104 @query = IssueQuery.find(params[:query_id], :conditions => cond)
126 105 raise ::Unauthorized unless @query.visible?
127 106 @query.project = @project
128 107 session[:query] = {:id => @query.id, :project_id => @query.project_id}
129 108 sort_clear
130 109 elsif api_request? || params[:set_filter] || session[:query].nil? || session[:query][:project_id] != (@project ? @project.id : nil)
131 110 # Give it a name, required to be valid
132 111 @query = IssueQuery.new(:name => "_")
133 112 @query.project = @project
134 113 @query.build_from_params(params)
135 114 session[:query] = {:project_id => @query.project_id, :filters => @query.filters, :group_by => @query.group_by, :column_names => @query.column_names}
136 115 else
137 116 # retrieve from session
138 117 @query = IssueQuery.find_by_id(session[:query][:id]) if session[:query][:id]
139 118 @query ||= IssueQuery.new(:name => "_", :filters => session[:query][:filters], :group_by => session[:query][:group_by], :column_names => session[:query][:column_names])
140 119 @query.project = @project
141 120 end
142 121 end
143 122
144 123 def retrieve_query_from_session
145 124 if session[:query]
146 125 if session[:query][:id]
147 126 @query = IssueQuery.find_by_id(session[:query][:id])
148 127 return unless @query
149 128 else
150 129 @query = IssueQuery.new(:name => "_", :filters => session[:query][:filters], :group_by => session[:query][:group_by], :column_names => session[:query][:column_names])
151 130 end
152 131 if session[:query].has_key?(:project_id)
153 132 @query.project_id = session[:query][:project_id]
154 133 else
155 134 @query.project = @project
156 135 end
157 136 @query
158 137 end
159 138 end
160 139 end
Show file before | Show file after | Hide comments
@@ -1,420 +1,406
1 1 # Redmine - project management software
2 2 # Copyright (C) 2006-2013 Jean-Philippe Lang
3 3 #
4 4 # This program is free software; you can redistribute it and/or
5 5 # modify it under the terms of the GNU General Public License
6 6 # as published by the Free Software Foundation; either version 2
7 7 # of the License, or (at your option) any later version.
8 8 #
9 9 # This program is distributed in the hope that it will be useful,
10 10 # but WITHOUT ANY WARRANTY; without even the implied warranty of
11 11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 12 # GNU General Public License for more details.
13 13 #
14 14 # You should have received a copy of the GNU General Public License
15 15 # along with this program; if not, write to the Free Software
16 16 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
17 17
18 18 class IssueQuery < Query
19 19
20 20 self.queried_class = Issue
21 21
22 22 self.available_columns = [
23 23 QueryColumn.new(:project, :sortable => "#{Project.table_name}.name", :groupable => true),
24 24 QueryColumn.new(:tracker, :sortable => "#{Tracker.table_name}.position", :groupable => true),
25 25 QueryColumn.new(:parent, :sortable => ["#{Issue.table_name}.root_id", "#{Issue.table_name}.lft ASC"], :default_order => 'desc', :caption => :field_parent_issue),
26 26 QueryColumn.new(:status, :sortable => "#{IssueStatus.table_name}.position", :groupable => true),
27 27 QueryColumn.new(:priority, :sortable => "#{IssuePriority.table_name}.position", :default_order => 'desc', :groupable => true),
28 28 QueryColumn.new(:subject, :sortable => "#{Issue.table_name}.subject"),
29 29 QueryColumn.new(:author, :sortable => lambda {User.fields_for_order_statement("authors")}, :groupable => true),
30 30 QueryColumn.new(:assigned_to, :sortable => lambda {User.fields_for_order_statement}, :groupable => true),
31 31 QueryColumn.new(:updated_on, :sortable => "#{Issue.table_name}.updated_on", :default_order => 'desc'),
32 32 QueryColumn.new(:category, :sortable => "#{IssueCategory.table_name}.name", :groupable => true),
33 33 QueryColumn.new(:fixed_version, :sortable => lambda {Version.fields_for_order_statement}, :groupable => true),
34 34 QueryColumn.new(:start_date, :sortable => "#{Issue.table_name}.start_date"),
35 35 QueryColumn.new(:due_date, :sortable => "#{Issue.table_name}.due_date"),
36 36 QueryColumn.new(:estimated_hours, :sortable => "#{Issue.table_name}.estimated_hours"),
37 37 QueryColumn.new(:done_ratio, :sortable => "#{Issue.table_name}.done_ratio", :groupable => true),
38 38 QueryColumn.new(:created_on, :sortable => "#{Issue.table_name}.created_on", :default_order => 'desc'),
39 39 QueryColumn.new(:relations, :caption => :label_related_issues),
40 40 QueryColumn.new(:description, :inline => false)
41 41 ]
42 42
43 43 scope :visible, lambda {|*args|
44 44 user = args.shift || User.current
45 45 base = Project.allowed_to_condition(user, :view_issues, *args)
46 46 user_id = user.logged? ? user.id : 0
47 47
48 48 includes(:project).where("(#{table_name}.project_id IS NULL OR (#{base})) AND (#{table_name}.is_public = ? OR #{table_name}.user_id = ?)", true, user_id)
49 49 }
50 50
51 51 def initialize(attributes=nil, *args)
52 52 super attributes
53 53 self.filters ||= { 'status_id' => {:operator => "o", :values => [""]} }
54 54 end
55 55
56 56 # Returns true if the query is visible to +user+ or the current user.
57 57 def visible?(user=User.current)
58 58 (project.nil? || user.allowed_to?(:view_issues, project)) && (self.is_public? || self.user_id == user.id)
59 59 end
60 60
61 def available_filters
62 return @available_filters if @available_filters
63 @available_filters = {
64 "status_id" => {
65 :type => :list_status, :order => 0,
66 :values => IssueStatus.sorted.all.collect{|s| [s.name, s.id.to_s] }
67 },
68 "tracker_id" => {
69 :type => :list, :order => 2, :values => trackers.collect{|s| [s.name, s.id.to_s] }
70 },
71 "priority_id" => {
72 :type => :list, :order => 3, :values => IssuePriority.all.collect{|s| [s.name, s.id.to_s] }
73 },
74 "subject" => { :type => :text, :order => 8 },
75 "created_on" => { :type => :date_past, :order => 9 },
76 "updated_on" => { :type => :date_past, :order => 10 },
77 "start_date" => { :type => :date, :order => 11 },
78 "due_date" => { :type => :date, :order => 12 },
79 "estimated_hours" => { :type => :float, :order => 13 },
80 "done_ratio" => { :type => :integer, :order => 14 }
81 }
82 IssueRelation::TYPES.each do |relation_type, options|
83 @available_filters[relation_type] = {
84 :type => :relation, :order => @available_filters.size + 100,
85 :label => options[:name]
86 }
87 end
61 def initialize_available_filters
88 62 principals = []
63 subprojects = []
64 versions = []
65 categories = []
66 issue_custom_fields = []
67
89 68 if project
90 69 principals += project.principals.sort
91 70 unless project.leaf?
92 71 subprojects = project.descendants.visible.all
93 if subprojects.any?
94 @available_filters["subproject_id"] = {
95 :type => :list_subprojects, :order => 13,
96 :values => subprojects.collect{|s| [s.name, s.id.to_s] }
97 }
98 principals += Principal.member_of(subprojects)
99 end
72 principals += Principal.member_of(subprojects)
100 73 end
74 versions = project.shared_versions.all
75 categories = project.issue_categories.all
76 issue_custom_fields = project.all_issue_custom_fields
101 77 else
102 78 if all_projects.any?
103 # members of visible projects
104 79 principals += Principal.member_of(all_projects)
105 # project filter
106 project_values = []
107 if User.current.logged? && User.current.memberships.any?
108 project_values << ["<< #{l(:label_my_projects).downcase} >>", "mine"]
109 end
110 project_values += all_projects_values
111 @available_filters["project_id"] = {
112 :type => :list, :order => 1, :values => project_values
113 } unless project_values.empty?
114 80 end
81 versions = Version.visible.find_all_by_sharing('system')
82 issue_custom_fields = IssueCustomField.where(:is_filter => true, :is_for_all => true).all
115 83 end
116 84 principals.uniq!
117 85 principals.sort!
118 86 users = principals.select {|p| p.is_a?(User)}
119 87
120 assigned_to_values = []
121 assigned_to_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
122 assigned_to_values += (Setting.issue_group_assignment? ?
123 principals : users).collect{|s| [s.name, s.id.to_s] }
124 @available_filters["assigned_to_id"] = {
125 :type => :list_optional, :order => 4, :values => assigned_to_values
126 } unless assigned_to_values.empty?
88
89 add_available_filter "status_id",
90 :type => :list_status, :values => IssueStatus.sorted.all.collect{|s| [s.name, s.id.to_s] }
91
92 if project.nil?
93 project_values = []
94 if User.current.logged? && User.current.memberships.any?
95 project_values << ["<< #{l(:label_my_projects).downcase} >>", "mine"]
96 end
97 project_values += all_projects_values
98 add_available_filter("project_id",
99 :type => :list, :values => project_values
100 ) unless project_values.empty?
101 end
102
103 add_available_filter "tracker_id",
104 :type => :list, :values => trackers.collect{|s| [s.name, s.id.to_s] }
105 add_available_filter "priority_id",
106 :type => :list, :values => IssuePriority.all.collect{|s| [s.name, s.id.to_s] }
127 107
128 108 author_values = []
129 109 author_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
130 110 author_values += users.collect{|s| [s.name, s.id.to_s] }
131 @available_filters["author_id"] = {
132 :type => :list, :order => 5, :values => author_values
133 } unless author_values.empty?
111 add_available_filter("author_id",
112 :type => :list, :values => author_values
113 ) unless author_values.empty?
114
115 assigned_to_values = []
116 assigned_to_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
117 assigned_to_values += (Setting.issue_group_assignment? ?
118 principals : users).collect{|s| [s.name, s.id.to_s] }
119 add_available_filter("assigned_to_id",
120 :type => :list_optional, :values => assigned_to_values
121 ) unless assigned_to_values.empty?
134 122
135 123 group_values = Group.all.collect {|g| [g.name, g.id.to_s] }
136 @available_filters["member_of_group"] = {
137 :type => :list_optional, :order => 6, :values => group_values
138 } unless group_values.empty?
124 add_available_filter("member_of_group",
125 :type => :list_optional, :values => group_values
126 ) unless group_values.empty?
139 127
140 128 role_values = Role.givable.collect {|r| [r.name, r.id.to_s] }
141 @available_filters["assigned_to_role"] = {
142 :type => :list_optional, :order => 7, :values => role_values
143 } unless role_values.empty?
144
145 if User.current.logged?
146 @available_filters["watcher_id"] = {
147 :type => :list, :order => 15, :values => [["<< #{l(:label_me)} >>", "me"]]
148 }
129 add_available_filter("assigned_to_role",
130 :type => :list_optional, :values => role_values
131 ) unless role_values.empty?
132
133 if versions.any?
134 add_available_filter "fixed_version_id",
135 :type => :list_optional,
136 :values => versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] }
149 137 end
150 138
151 if project
152 # project specific filters
153 categories = project.issue_categories.all
154 unless categories.empty?
155 @available_filters["category_id"] = {
156 :type => :list_optional, :order => 6,
157 :values => categories.collect{|s| [s.name, s.id.to_s] }
158 }
159 end
160 versions = project.shared_versions.all
161 unless versions.empty?
162 @available_filters["fixed_version_id"] = {
163 :type => :list_optional, :order => 7,
164 :values => versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] }
165 }
166 end
167 add_custom_fields_filters(project.all_issue_custom_fields)
168 else
169 # global filters for cross project issue list
170 system_shared_versions = Version.visible.find_all_by_sharing('system')
171 unless system_shared_versions.empty?
172 @available_filters["fixed_version_id"] = {
173 :type => :list_optional, :order => 7,
174 :values => system_shared_versions.sort.collect{|s|
175 ["#{s.project.name} - #{s.name}", s.id.to_s]
176 }
177 }
178 end
179 add_custom_fields_filters(IssueCustomField.where(:is_filter => true, :is_for_all => true).all)
139 if categories.any?
140 add_available_filter "category_id",
141 :type => :list_optional,
142 :values => categories.collect{|s| [s.name, s.id.to_s] }
180 143 end
181 add_associations_custom_fields_filters :project, :author, :assigned_to, :fixed_version
144
145 add_available_filter "subject", :type => :text
146 add_available_filter "created_on", :type => :date_past
147 add_available_filter "updated_on", :type => :date_past
148 add_available_filter "start_date", :type => :date
149 add_available_filter "due_date", :type => :date
150 add_available_filter "estimated_hours", :type => :float
151 add_available_filter "done_ratio", :type => :integer
152
182 153 if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||
183 154 User.current.allowed_to?(:set_own_issues_private, nil, :global => true)
184 @available_filters["is_private"] = {
185 :type => :list, :order => 16,
155 add_available_filter "is_private",
156 :type => :list,
186 157 :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]]
187 }
188 158 end
159
160 if User.current.logged?
161 add_available_filter "watcher_id",
162 :type => :list, :values => [["<< #{l(:label_me)} >>", "me"]]
163 end
164
165 if subprojects.any?
166 add_available_filter "subproject_id",
167 :type => :list_subprojects,
168 :values => subprojects.collect{|s| [s.name, s.id.to_s] }
169 end
170
171 add_custom_fields_filters(issue_custom_fields)
172
173 add_associations_custom_fields_filters :project, :author, :assigned_to, :fixed_version
174
175 IssueRelation::TYPES.each do |relation_type, options|
176 add_available_filter relation_type, :type => :relation, :label => options[:name]
177 end
178
189 179 Tracker.disabled_core_fields(trackers).each {|field|
190 @available_filters.delete field
180 delete_available_filter field
191 181 }
192 @available_filters.each do |field, options|
193 options[:name] ||= l(options[:label] || "field_#{field}".gsub(/_id$/, ''))
194 end
195 @available_filters
196 182 end
197 183
198 184 def available_columns
199 185 return @available_columns if @available_columns
200 186 @available_columns = self.class.available_columns.dup
201 187 @available_columns += (project ?
202 188 project.all_issue_custom_fields :
203 189 IssueCustomField.all
204 190 ).collect {|cf| QueryCustomFieldColumn.new(cf) }
205 191
206 192 if User.current.allowed_to?(:view_time_entries, project, :global => true)
207 193 index = nil
208 194 @available_columns.each_with_index {|column, i| index = i if column.name == :estimated_hours}
209 195 index = (index ? index + 1 : -1)
210 196 # insert the column after estimated_hours or at the end
211 197 @available_columns.insert index, QueryColumn.new(:spent_hours,
212 198 :sortable => "COALESCE((SELECT SUM(hours) FROM #{TimeEntry.table_name} WHERE #{TimeEntry.table_name}.issue_id = #{Issue.table_name}.id), 0)",
213 199 :default_order => 'desc',
214 200 :caption => :label_spent_time
215 201 )
216 202 end
217 203
218 204 if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||
219 205 User.current.allowed_to?(:set_own_issues_private, nil, :global => true)
220 206 @available_columns << QueryColumn.new(:is_private, :sortable => "#{Issue.table_name}.is_private")
221 207 end
222 208
223 209 disabled_fields = Tracker.disabled_core_fields(trackers).map {|field| field.sub(/_id$/, '')}
224 210 @available_columns.reject! {|column|
225 211 disabled_fields.include?(column.name.to_s)
226 212 }
227 213
228 214 @available_columns
229 215 end
230 216
231 217 def sortable_columns
232 218 {'id' => "#{Issue.table_name}.id"}.merge(super)
233 219 end
234 220
235 221 def default_columns_names
236 222 @default_columns_names ||= begin
237 223 default_columns = Setting.issue_list_default_columns.map(&:to_sym)
238 224
239 225 project.present? ? default_columns : [:project] | default_columns
240 226 end
241 227 end
242 228
243 229 # Returns the issue count
244 230 def issue_count
245 231 Issue.visible.count(:include => [:status, :project], :conditions => statement)
246 232 rescue ::ActiveRecord::StatementInvalid => e
247 233 raise StatementInvalid.new(e.message)
248 234 end
249 235
250 236 # Returns the issue count by group or nil if query is not grouped
251 237 def issue_count_by_group
252 238 r = nil
253 239 if grouped?
254 240 begin
255 241 # Rails3 will raise an (unexpected) RecordNotFound if there's only a nil group value
256 242 r = Issue.visible.count(:joins => joins_for_order_statement(group_by_statement), :group => group_by_statement, :include => [:status, :project], :conditions => statement)
257 243 rescue ActiveRecord::RecordNotFound
258 244 r = {nil => issue_count}
259 245 end
260 246 c = group_by_column
261 247 if c.is_a?(QueryCustomFieldColumn)
262 248 r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
263 249 end
264 250 end
265 251 r
266 252 rescue ::ActiveRecord::StatementInvalid => e
267 253 raise StatementInvalid.new(e.message)
268 254 end
269 255
270 256 # Returns the issues
271 257 # Valid options are :order, :offset, :limit, :include, :conditions
272 258 def issues(options={})
273 259 order_option = [group_by_sort_order, options[:order]].flatten.reject(&:blank?)
274 260
275 261 issues = Issue.visible.where(options[:conditions]).all(
276 262 :include => ([:status, :project] + (options[:include] || [])).uniq,
277 263 :conditions => statement,
278 264 :order => order_option,
279 265 :joins => joins_for_order_statement(order_option.join(',')),
280 266 :limit => options[:limit],
281 267 :offset => options[:offset]
282 268 )
283 269
284 270 if has_column?(:spent_hours)
285 271 Issue.load_visible_spent_hours(issues)
286 272 end
287 273 if has_column?(:relations)
288 274 Issue.load_visible_relations(issues)
289 275 end
290 276 issues
291 277 rescue ::ActiveRecord::StatementInvalid => e
292 278 raise StatementInvalid.new(e.message)
293 279 end
294 280
295 281 # Returns the issues ids
296 282 def issue_ids(options={})
297 283 order_option = [group_by_sort_order, options[:order]].flatten.reject(&:blank?)
298 284
299 285 Issue.visible.scoped(:conditions => options[:conditions]).scoped(:include => ([:status, :project] + (options[:include] || [])).uniq,
300 286 :conditions => statement,
301 287 :order => order_option,
302 288 :joins => joins_for_order_statement(order_option.join(',')),
303 289 :limit => options[:limit],
304 290 :offset => options[:offset]).find_ids
305 291 rescue ::ActiveRecord::StatementInvalid => e
306 292 raise StatementInvalid.new(e.message)
307 293 end
308 294
309 295 # Returns the journals
310 296 # Valid options are :order, :offset, :limit
311 297 def journals(options={})
312 298 Journal.visible.all(
313 299 :include => [:details, :user, {:issue => [:project, :author, :tracker, :status]}],
314 300 :conditions => statement,
315 301 :order => options[:order],
316 302 :limit => options[:limit],
317 303 :offset => options[:offset]
318 304 )
319 305 rescue ::ActiveRecord::StatementInvalid => e
320 306 raise StatementInvalid.new(e.message)
321 307 end
322 308
323 309 # Returns the versions
324 310 # Valid options are :conditions
325 311 def versions(options={})
326 312 Version.visible.where(options[:conditions]).all(
327 313 :include => :project,
328 314 :conditions => project_statement
329 315 )
330 316 rescue ::ActiveRecord::StatementInvalid => e
331 317 raise StatementInvalid.new(e.message)
332 318 end
333 319
334 320 def sql_for_watcher_id_field(field, operator, value)
335 321 db_table = Watcher.table_name
336 322 "#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{db_table}.watchable_id FROM #{db_table} WHERE #{db_table}.watchable_type='Issue' AND " +
337 323 sql_for_field(field, '=', value, db_table, 'user_id') + ')'
338 324 end
339 325
340 326 def sql_for_member_of_group_field(field, operator, value)
341 327 if operator == '*' # Any group
342 328 groups = Group.all
343 329 operator = '=' # Override the operator since we want to find by assigned_to
344 330 elsif operator == "!*"
345 331 groups = Group.all
346 332 operator = '!' # Override the operator since we want to find by assigned_to
347 333 else
348 334 groups = Group.find_all_by_id(value)
349 335 end
350 336 groups ||= []
351 337
352 338 members_of_groups = groups.inject([]) {|user_ids, group|
353 339 user_ids + group.user_ids + [group.id]
354 340 }.uniq.compact.sort.collect(&:to_s)
355 341
356 342 '(' + sql_for_field("assigned_to_id", operator, members_of_groups, Issue.table_name, "assigned_to_id", false) + ')'
357 343 end
358 344
359 345 def sql_for_assigned_to_role_field(field, operator, value)
360 346 case operator
361 347 when "*", "!*" # Member / Not member
362 348 sw = operator == "!*" ? 'NOT' : ''
363 349 nl = operator == "!*" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''
364 350 "(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}" +
365 351 " WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id))"
366 352 when "=", "!"
367 353 role_cond = value.any? ?
368 354 "#{MemberRole.table_name}.role_id IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")" :
369 355 "1=0"
370 356
371 357 sw = operator == "!" ? 'NOT' : ''
372 358 nl = operator == "!" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''
373 359 "(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}, #{MemberRole.table_name}" +
374 360 " WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id AND #{Member.table_name}.id = #{MemberRole.table_name}.member_id AND #{role_cond}))"
375 361 end
376 362 end
377 363
378 364 def sql_for_is_private_field(field, operator, value)
379 365 op = (operator == "=" ? 'IN' : 'NOT IN')
380 366 va = value.map {|v| v == '0' ? connection.quoted_false : connection.quoted_true}.uniq.join(',')
381 367
382 368 "#{Issue.table_name}.is_private #{op} (#{va})"
383 369 end
384 370
385 371 def sql_for_relations(field, operator, value, options={})
386 372 relation_options = IssueRelation::TYPES[field]
387 373 return relation_options unless relation_options
388 374
389 375 relation_type = field
390 376 join_column, target_join_column = "issue_from_id", "issue_to_id"
391 377 if relation_options[:reverse] || options[:reverse]
392 378 relation_type = relation_options[:reverse] || relation_type
393 379 join_column, target_join_column = target_join_column, join_column
394 380 end
395 381
396 382 sql = case operator
397 383 when "*", "!*"
398 384 op = (operator == "*" ? 'IN' : 'NOT IN')
399 385 "#{Issue.table_name}.id #{op} (SELECT DISTINCT #{IssueRelation.table_name}.#{join_column} FROM #{IssueRelation.table_name} WHERE #{IssueRelation.table_name}.relation_type = '#{connection.quote_string(relation_type)}')"
400 386 when "=", "!"
401 387 op = (operator == "=" ? 'IN' : 'NOT IN')
402 388 "#{Issue.table_name}.id #{op} (SELECT DISTINCT #{IssueRelation.table_name}.#{join_column} FROM #{IssueRelation.table_name} WHERE #{IssueRelation.table_name}.relation_type = '#{connection.quote_string(relation_type)}' AND #{IssueRelation.table_name}.#{target_join_column} = #{value.first.to_i})"
403 389 when "=p", "=!p", "!p"
404 390 op = (operator == "!p" ? 'NOT IN' : 'IN')
405 391 comp = (operator == "=!p" ? '<>' : '=')
406 392 "#{Issue.table_name}.id #{op} (SELECT DISTINCT #{IssueRelation.table_name}.#{join_column} FROM #{IssueRelation.table_name}, #{Issue.table_name} relissues WHERE #{IssueRelation.table_name}.relation_type = '#{connection.quote_string(relation_type)}' AND #{IssueRelation.table_name}.#{target_join_column} = relissues.id AND relissues.project_id #{comp} #{value.first.to_i})"
407 393 end
408 394
409 395 if relation_options[:sym] == field && !options[:reverse]
410 396 sqls = [sql, sql_for_relations(field, operator, value, :reverse => true)]
411 397 sqls.join(["!", "!*", "!p"].include?(operator) ? " AND " : " OR ")
412 398 else
413 399 sql
414 400 end
415 401 end
416 402
417 403 IssueRelation::TYPES.keys.each do |relation_type|
418 404 alias_method "sql_for_#{relation_type}_field".to_sym, :sql_for_relations
419 405 end
420 406 end
Show file before | Show file after | Hide comments
@@ -1,792 +1,822
1 1 # Redmine - project management software
2 2 # Copyright (C) 2006-2013 Jean-Philippe Lang
3 3 #
4 4 # This program is free software; you can redistribute it and/or
5 5 # modify it under the terms of the GNU General Public License
6 6 # as published by the Free Software Foundation; either version 2
7 7 # of the License, or (at your option) any later version.
8 8 #
9 9 # This program is distributed in the hope that it will be useful,
10 10 # but WITHOUT ANY WARRANTY; without even the implied warranty of
11 11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 12 # GNU General Public License for more details.
13 13 #
14 14 # You should have received a copy of the GNU General Public License
15 15 # along with this program; if not, write to the Free Software
16 16 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
17 17
18 18 class QueryColumn
19 19 attr_accessor :name, :sortable, :groupable, :default_order
20 20 include Redmine::I18n
21 21
22 22 def initialize(name, options={})
23 23 self.name = name
24 24 self.sortable = options[:sortable]
25 25 self.groupable = options[:groupable] || false
26 26 if groupable == true
27 27 self.groupable = name.to_s
28 28 end
29 29 self.default_order = options[:default_order]
30 30 @inline = options.key?(:inline) ? options[:inline] : true
31 31 @caption_key = options[:caption] || "field_#{name}"
32 32 end
33 33
34 34 def caption
35 35 l(@caption_key)
36 36 end
37 37
38 38 # Returns true if the column is sortable, otherwise false
39 39 def sortable?
40 40 !@sortable.nil?
41 41 end
42 42
43 43 def sortable
44 44 @sortable.is_a?(Proc) ? @sortable.call : @sortable
45 45 end
46 46
47 47 def inline?
48 48 @inline
49 49 end
50 50
51 51 def value(object)
52 52 object.send name
53 53 end
54 54
55 55 def css_classes
56 56 name
57 57 end
58 58 end
59 59
60 60 class QueryCustomFieldColumn < QueryColumn
61 61
62 62 def initialize(custom_field)
63 63 self.name = "cf_#{custom_field.id}".to_sym
64 64 self.sortable = custom_field.order_statement || false
65 65 self.groupable = custom_field.group_statement || false
66 66 @inline = true
67 67 @cf = custom_field
68 68 end
69 69
70 70 def caption
71 71 @cf.name
72 72 end
73 73
74 74 def custom_field
75 75 @cf
76 76 end
77 77
78 78 def value(object)
79 79 cv = object.custom_values.select {|v| v.custom_field_id == @cf.id}.collect {|v| @cf.cast_value(v.value)}
80 80 cv.size > 1 ? cv.sort {|a,b| a.to_s <=> b.to_s} : cv.first
81 81 end
82 82
83 83 def css_classes
84 84 @css_classes ||= "#{name} #{@cf.field_format}"
85 85 end
86 86 end
87 87
88 88 class QueryAssociationCustomFieldColumn < QueryCustomFieldColumn
89 89
90 90 def initialize(association, custom_field)
91 91 super(custom_field)
92 92 self.name = "#{association}.cf_#{custom_field.id}".to_sym
93 93 # TODO: support sorting/grouping by association custom field
94 94 self.sortable = false
95 95 self.groupable = false
96 96 @association = association
97 97 end
98 98
99 99 def value(object)
100 100 if assoc = object.send(@association)
101 101 super(assoc)
102 102 end
103 103 end
104 104
105 105 def css_classes
106 106 @css_classes ||= "#{@association}_cf_#{@cf.id} #{@cf.field_format}"
107 107 end
108 108 end
109 109
110 110 class Query < ActiveRecord::Base
111 111 class StatementInvalid < ::ActiveRecord::StatementInvalid
112 112 end
113 113
114 114 belongs_to :project
115 115 belongs_to :user
116 116 serialize :filters
117 117 serialize :column_names
118 118 serialize :sort_criteria, Array
119 119
120 120 attr_protected :project_id, :user_id
121 121
122 122 validates_presence_of :name
123 123 validates_length_of :name, :maximum => 255
124 124 validate :validate_query_filters
125 125
126 126 class_attribute :operators
127 127 self.operators = {
128 128 "=" => :label_equals,
129 129 "!" => :label_not_equals,
130 130 "o" => :label_open_issues,
131 131 "c" => :label_closed_issues,
132 132 "!*" => :label_none,
133 133 "*" => :label_any,
134 134 ">=" => :label_greater_or_equal,
135 135 "<=" => :label_less_or_equal,
136 136 "><" => :label_between,
137 137 "<t+" => :label_in_less_than,
138 138 ">t+" => :label_in_more_than,
139 139 "><t+"=> :label_in_the_next_days,
140 140 "t+" => :label_in,
141 141 "t" => :label_today,
142 142 "ld" => :label_yesterday,
143 143 "w" => :label_this_week,
144 144 "lw" => :label_last_week,
145 145 "l2w" => [:label_last_n_weeks, {:count => 2}],
146 146 "m" => :label_this_month,
147 147 "lm" => :label_last_month,
148 148 "y" => :label_this_year,
149 149 ">t-" => :label_less_than_ago,
150 150 "<t-" => :label_more_than_ago,
151 151 "><t-"=> :label_in_the_past_days,
152 152 "t-" => :label_ago,
153 153 "~" => :label_contains,
154 154 "!~" => :label_not_contains,
155 155 "=p" => :label_any_issues_in_project,
156 156 "=!p" => :label_any_issues_not_in_project,
157 157 "!p" => :label_no_issues_in_project
158 158 }
159 159
160 160 class_attribute :operators_by_filter_type
161 161 self.operators_by_filter_type = {
162 162 :list => [ "=", "!" ],
163 163 :list_status => [ "o", "=", "!", "c", "*" ],
164 164 :list_optional => [ "=", "!", "!*", "*" ],
165 165 :list_subprojects => [ "*", "!*", "=" ],
166 166 :date => [ "=", ">=", "<=", "><", "<t+", ">t+", "><t+", "t+", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", ">t-", "<t-", "><t-", "t-", "!*", "*" ],
167 167 :date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "><t-", "t-", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", "!*", "*" ],
168 168 :string => [ "=", "~", "!", "!~", "!*", "*" ],
169 169 :text => [ "~", "!~", "!*", "*" ],
170 170 :integer => [ "=", ">=", "<=", "><", "!*", "*" ],
171 171 :float => [ "=", ">=", "<=", "><", "!*", "*" ],
172 172 :relation => ["=", "=p", "=!p", "!p", "!*", "*"]
173 173 }
174 174
175 175 class_attribute :available_columns
176 176 self.available_columns = []
177 177
178 178 class_attribute :queried_class
179 179
180 180 def queried_table_name
181 181 @queried_table_name ||= self.class.queried_class.table_name
182 182 end
183 183
184 184 def initialize(attributes=nil, *args)
185 185 super attributes
186 186 @is_for_all = project.nil?
187 187 end
188 188
189 189 # Builds the query from the given params
190 190 def build_from_params(params)
191 191 if params[:fields] || params[:f]
192 192 self.filters = {}
193 193 add_filters(params[:fields] || params[:f], params[:operators] || params[:op], params[:values] || params[:v])
194 194 else
195 195 available_filters.keys.each do |field|
196 196 add_short_filter(field, params[field]) if params[field]
197 197 end
198 198 end
199 199 self.group_by = params[:group_by] || (params[:query] && params[:query][:group_by])
200 200 self.column_names = params[:c] || (params[:query] && params[:query][:column_names])
201 201 self
202 202 end
203 203
204 204 # Builds a new query from the given params and attributes
205 205 def self.build_from_params(params, attributes={})
206 206 new(attributes).build_from_params(params)
207 207 end
208 208
209 209 def validate_query_filters
210 210 filters.each_key do |field|
211 211 if values_for(field)
212 212 case type_for(field)
213 213 when :integer
214 214 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^[+-]?\d+$/) }
215 215 when :float
216 216 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^[+-]?\d+(\.\d*)?$/) }
217 217 when :date, :date_past
218 218 case operator_for(field)
219 219 when "=", ">=", "<=", "><"
220 220 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?) }
221 221 when ">t-", "<t-", "t-", ">t+", "<t+", "t+", "><t+", "><t-"
222 222 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^\d+$/) }
223 223 end
224 224 end
225 225 end
226 226
227 227 add_filter_error(field, :blank) unless
228 228 # filter requires one or more values
229 229 (values_for(field) and !values_for(field).first.blank?) or
230 230 # filter doesn't require any value
231 231 ["o", "c", "!*", "*", "t", "ld", "w", "lw", "l2w", "m", "lm", "y"].include? operator_for(field)
232 232 end if filters
233 233 end
234 234
235 235 def add_filter_error(field, message)
236 236 m = label_for(field) + " " + l(message, :scope => 'activerecord.errors.messages')
237 237 errors.add(:base, m)
238 238 end
239 239
240 240 def editable_by?(user)
241 241 return false unless user
242 242 # Admin can edit them all and regular users can edit their private queries
243 243 return true if user.admin? || (!is_public && self.user_id == user.id)
244 244 # Members can not edit public queries that are for all project (only admin is allowed to)
245 245 is_public && !@is_for_all && user.allowed_to?(:manage_public_queries, project)
246 246 end
247 247
248 248 def trackers
249 249 @trackers ||= project.nil? ? Tracker.sorted.all : project.rolled_up_trackers
250 250 end
251 251
252 252 # Returns a hash of localized labels for all filter operators
253 253 def self.operators_labels
254 254 operators.inject({}) {|h, operator| h[operator.first] = l(*operator.last); h}
255 255 end
256 256
257 257 # Returns a representation of the available filters for JSON serialization
258 258 def available_filters_as_json
259 259 json = {}
260 260 available_filters.each do |field, options|
261 261 json[field] = options.slice(:type, :name, :values).stringify_keys
262 262 end
263 263 json
264 264 end
265 265
266 266 def all_projects
267 267 @all_projects ||= Project.visible.all
268 268 end
269 269
270 270 def all_projects_values
271 271 return @all_projects_values if @all_projects_values
272 272
273 273 values = []
274 274 Project.project_tree(all_projects) do |p, level|
275 275 prefix = (level > 0 ? ('--' * level + ' ') : '')
276 276 values << ["#{prefix}#{p.name}", p.id.to_s]
277 277 end
278 278 @all_projects_values = values
279 279 end
280 280
281 # Adds available filters
282 def initialize_available_filters
283 # implemented by sub-classes
284 end
285 protected :initialize_available_filters
286
287 # Adds an available filter
288 def add_available_filter(field, options)
289 @available_filters ||= ActiveSupport::OrderedHash.new
290 @available_filters[field] = options
291 @available_filters
292 end
293
294 # Removes an available filter
295 def delete_available_filter(field)
296 if @available_filters
297 @available_filters.delete(field)
298 end
299 end
300
301 # Return a hash of available filters
302 def available_filters
303 unless @available_filters
304 initialize_available_filters
305 @available_filters.each do |field, options|
306 options[:name] ||= l(options[:label] || "field_#{field}".gsub(/_id$/, ''))
307 end
308 end
309 @available_filters
310 end
311
281 312 def add_filter(field, operator, values=nil)
282 313 # values must be an array
283 314 return unless values.nil? || values.is_a?(Array)
284 315 # check if field is defined as an available filter
285 316 if available_filters.has_key? field
286 317 filter_options = available_filters[field]
287 318 filters[field] = {:operator => operator, :values => (values || [''])}
288 319 end
289 320 end
290 321
291 322 def add_short_filter(field, expression)
292 323 return unless expression && available_filters.has_key?(field)
293 324 field_type = available_filters[field][:type]
294 325 operators_by_filter_type[field_type].sort.reverse.detect do |operator|
295 326 next unless expression =~ /^#{Regexp.escape(operator)}(.*)$/
296 327 values = $1
297 328 add_filter field, operator, values.present? ? values.split('|') : ['']
298 329 end || add_filter(field, '=', expression.split('|'))
299 330 end
300 331
301 332 # Add multiple filters using +add_filter+
302 333 def add_filters(fields, operators, values)
303 334 if fields.is_a?(Array) && operators.is_a?(Hash) && (values.nil? || values.is_a?(Hash))
304 335 fields.each do |field|
305 336 add_filter(field, operators[field], values && values[field])
306 337 end
307 338 end
308 339 end
309 340
310 341 def has_filter?(field)
311 342 filters and filters[field]
312 343 end
313 344
314 345 def type_for(field)
315 346 available_filters[field][:type] if available_filters.has_key?(field)
316 347 end
317 348
318 349 def operator_for(field)
319 350 has_filter?(field) ? filters[field][:operator] : nil
320 351 end
321 352
322 353 def values_for(field)
323 354 has_filter?(field) ? filters[field][:values] : nil
324 355 end
325 356
326 357 def value_for(field, index=0)
327 358 (values_for(field) || [])[index]
328 359 end
329 360
330 361 def label_for(field)
331 362 label = available_filters[field][:name] if available_filters.has_key?(field)
332 363 label ||= l("field_#{field.to_s.gsub(/_id$/, '')}", :default => field)
333 364 end
334 365
335 366 def self.add_available_column(column)
336 367 self.available_columns << (column) if column.is_a?(QueryColumn)
337 368 end
338 369
339 370 # Returns an array of columns that can be used to group the results
340 371 def groupable_columns
341 372 available_columns.select {|c| c.groupable}
342 373 end
343 374
344 375 # Returns a Hash of columns and the key for sorting
345 376 def sortable_columns
346 377 available_columns.inject({}) {|h, column|
347 378 h[column.name.to_s] = column.sortable
348 379 h
349 380 }
350 381 end
351 382
352 383 def columns
353 384 # preserve the column_names order
354 385 (has_default_columns? ? default_columns_names : column_names).collect do |name|
355 386 available_columns.find { |col| col.name == name }
356 387 end.compact
357 388 end
358 389
359 390 def inline_columns
360 391 columns.select(&:inline?)
361 392 end
362 393
363 394 def block_columns
364 395 columns.reject(&:inline?)
365 396 end
366 397
367 398 def available_inline_columns
368 399 available_columns.select(&:inline?)
369 400 end
370 401
371 402 def available_block_columns
372 403 available_columns.reject(&:inline?)
373 404 end
374 405
375 406 def default_columns_names
376 407 []
377 408 end
378 409
379 410 def column_names=(names)
380 411 if names
381 412 names = names.select {|n| n.is_a?(Symbol) || !n.blank? }
382 413 names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }
383 414 # Set column_names to nil if default columns
384 415 if names == default_columns_names
385 416 names = nil
386 417 end
387 418 end
388 419 write_attribute(:column_names, names)
389 420 end
390 421
391 422 def has_column?(column)
392 423 column_names && column_names.include?(column.is_a?(QueryColumn) ? column.name : column)
393 424 end
394 425
395 426 def has_default_columns?
396 427 column_names.nil? || column_names.empty?
397 428 end
398 429
399 430 def sort_criteria=(arg)
400 431 c = []
401 432 if arg.is_a?(Hash)
402 433 arg = arg.keys.sort.collect {|k| arg[k]}
403 434 end
404 435 c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, (o == 'desc' || o == false) ? 'desc' : 'asc']}
405 436 write_attribute(:sort_criteria, c)
406 437 end
407 438
408 439 def sort_criteria
409 440 read_attribute(:sort_criteria) || []
410 441 end
411 442
412 443 def sort_criteria_key(arg)
413 444 sort_criteria && sort_criteria[arg] && sort_criteria[arg].first
414 445 end
415 446
416 447 def sort_criteria_order(arg)
417 448 sort_criteria && sort_criteria[arg] && sort_criteria[arg].last
418 449 end
419 450
420 451 def sort_criteria_order_for(key)
421 452 sort_criteria.detect {|k, order| key.to_s == k}.try(:last)
422 453 end
423 454
424 455 # Returns the SQL sort order that should be prepended for grouping
425 456 def group_by_sort_order
426 457 if grouped? && (column = group_by_column)
427 458 order = sort_criteria_order_for(column.name) || column.default_order
428 459 column.sortable.is_a?(Array) ?
429 460 column.sortable.collect {|s| "#{s} #{order}"}.join(',') :
430 461 "#{column.sortable} #{order}"
431 462 end
432 463 end
433 464
434 465 # Returns true if the query is a grouped query
435 466 def grouped?
436 467 !group_by_column.nil?
437 468 end
438 469
439 470 def group_by_column
440 471 groupable_columns.detect {|c| c.groupable && c.name.to_s == group_by}
441 472 end
442 473
443 474 def group_by_statement
444 475 group_by_column.try(:groupable)
445 476 end
446 477
447 478 def project_statement
448 479 project_clauses = []
449 480 if project && !project.descendants.active.empty?
450 481 ids = [project.id]
451 482 if has_filter?("subproject_id")
452 483 case operator_for("subproject_id")
453 484 when '='
454 485 # include the selected subprojects
455 486 ids += values_for("subproject_id").each(&:to_i)
456 487 when '!*'
457 488 # main project only
458 489 else
459 490 # all subprojects
460 491 ids += project.descendants.collect(&:id)
461 492 end
462 493 elsif Setting.display_subprojects_issues?
463 494 ids += project.descendants.collect(&:id)
464 495 end
465 496 project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
466 497 elsif project
467 498 project_clauses << "#{Project.table_name}.id = %d" % project.id
468 499 end
469 500 project_clauses.any? ? project_clauses.join(' AND ') : nil
470 501 end
471 502
472 503 def statement
473 504 # filters clauses
474 505 filters_clauses = []
475 506 filters.each_key do |field|
476 507 next if field == "subproject_id"
477 508 v = values_for(field).clone
478 509 next unless v and !v.empty?
479 510 operator = operator_for(field)
480 511
481 512 # "me" value subsitution
482 513 if %w(assigned_to_id author_id user_id watcher_id).include?(field)
483 514 if v.delete("me")
484 515 if User.current.logged?
485 516 v.push(User.current.id.to_s)
486 517 v += User.current.group_ids.map(&:to_s) if field == 'assigned_to_id'
487 518 else
488 519 v.push("0")
489 520 end
490 521 end
491 522 end
492 523
493 524 if field == 'project_id'
494 525 if v.delete('mine')
495 526 v += User.current.memberships.map(&:project_id).map(&:to_s)
496 527 end
497 528 end
498 529
499 530 if field =~ /cf_(\d+)$/
500 531 # custom field
501 532 filters_clauses << sql_for_custom_field(field, operator, v, $1)
502 533 elsif respond_to?("sql_for_#{field}_field")
503 534 # specific statement
504 535 filters_clauses << send("sql_for_#{field}_field", field, operator, v)
505 536 else
506 537 # regular field
507 538 filters_clauses << '(' + sql_for_field(field, operator, v, queried_table_name, field) + ')'
508 539 end
509 540 end if filters and valid?
510 541
511 542 filters_clauses << project_statement
512 543 filters_clauses.reject!(&:blank?)
513 544
514 545 filters_clauses.any? ? filters_clauses.join(' AND ') : nil
515 546 end
516 547
517 548 private
518 549
519 550 def sql_for_custom_field(field, operator, value, custom_field_id)
520 551 db_table = CustomValue.table_name
521 552 db_field = 'value'
522 553 filter = @available_filters[field]
523 554 return nil unless filter
524 555 if filter[:format] == 'user'
525 556 if value.delete('me')
526 557 value.push User.current.id.to_s
527 558 end
528 559 end
529 560 not_in = nil
530 561 if operator == '!'
531 562 # Makes ! operator work for custom fields with multiple values
532 563 operator = '='
533 564 not_in = 'NOT'
534 565 end
535 566 customized_key = "id"
536 567 customized_class = queried_class
537 568 if field =~ /^(.+)\.cf_/
538 569 assoc = $1
539 570 customized_key = "#{assoc}_id"
540 571 customized_class = queried_class.reflect_on_association(assoc.to_sym).klass.base_class rescue nil
541 572 raise "Unknown #{queried_class.name} association #{assoc}" unless customized_class
542 573 end
543 574 "#{queried_table_name}.#{customized_key} #{not_in} IN (SELECT #{customized_class.table_name}.id FROM #{customized_class.table_name} LEFT OUTER JOIN #{db_table} ON #{db_table}.customized_type='#{customized_class}' AND #{db_table}.customized_id=#{customized_class.table_name}.id AND #{db_table}.custom_field_id=#{custom_field_id} WHERE " +
544 575 sql_for_field(field, operator, value, db_table, db_field, true) + ')'
545 576 end
546 577
547 578 # Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+
548 579 def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)
549 580 sql = ''
550 581 case operator
551 582 when "="
552 583 if value.any?
553 584 case type_for(field)
554 585 when :date, :date_past
555 586 sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), (Date.parse(value.first) rescue nil))
556 587 when :integer
557 588 if is_custom_filter
558 589 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) = #{value.first.to_i})"
559 590 else
560 591 sql = "#{db_table}.#{db_field} = #{value.first.to_i}"
561 592 end
562 593 when :float
563 594 if is_custom_filter
564 595 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5})"
565 596 else
566 597 sql = "#{db_table}.#{db_field} BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5}"
567 598 end
568 599 else
569 600 sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")"
570 601 end
571 602 else
572 603 # IN an empty set
573 604 sql = "1=0"
574 605 end
575 606 when "!"
576 607 if value.any?
577 608 sql = "(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + "))"
578 609 else
579 610 # NOT IN an empty set
580 611 sql = "1=1"
581 612 end
582 613 when "!*"
583 614 sql = "#{db_table}.#{db_field} IS NULL"
584 615 sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter
585 616 when "*"
586 617 sql = "#{db_table}.#{db_field} IS NOT NULL"
587 618 sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
588 619 when ">="
589 620 if [:date, :date_past].include?(type_for(field))
590 621 sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), nil)
591 622 else
592 623 if is_custom_filter
593 624 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) >= #{value.first.to_f})"
594 625 else
595 626 sql = "#{db_table}.#{db_field} >= #{value.first.to_f}"
596 627 end
597 628 end
598 629 when "<="
599 630 if [:date, :date_past].include?(type_for(field))
600 631 sql = date_clause(db_table, db_field, nil, (Date.parse(value.first) rescue nil))
601 632 else
602 633 if is_custom_filter
603 634 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) <= #{value.first.to_f})"
604 635 else
605 636 sql = "#{db_table}.#{db_field} <= #{value.first.to_f}"
606 637 end
607 638 end
608 639 when "><"
609 640 if [:date, :date_past].include?(type_for(field))
610 641 sql = date_clause(db_table, db_field, (Date.parse(value[0]) rescue nil), (Date.parse(value[1]) rescue nil))
611 642 else
612 643 if is_custom_filter
613 644 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) BETWEEN #{value[0].to_f} AND #{value[1].to_f})"
614 645 else
615 646 sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
616 647 end
617 648 end
618 649 when "o"
619 650 sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{connection.quoted_false})" if field == "status_id"
620 651 when "c"
621 652 sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{connection.quoted_true})" if field == "status_id"
622 653 when "><t-"
623 654 # between today - n days and today
624 655 sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0)
625 656 when ">t-"
626 657 # >= today - n days
627 658 sql = relative_date_clause(db_table, db_field, - value.first.to_i, nil)
628 659 when "<t-"
629 660 # <= today - n days
630 661 sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i)
631 662 when "t-"
632 663 # = n days in past
633 664 sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i)
634 665 when "><t+"
635 666 # between today and today + n days
636 667 sql = relative_date_clause(db_table, db_field, 0, value.first.to_i)
637 668 when ">t+"
638 669 # >= today + n days
639 670 sql = relative_date_clause(db_table, db_field, value.first.to_i, nil)
640 671 when "<t+"
641 672 # <= today + n days
642 673 sql = relative_date_clause(db_table, db_field, nil, value.first.to_i)
643 674 when "t+"
644 675 # = today + n days
645 676 sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i)
646 677 when "t"
647 678 # = today
648 679 sql = relative_date_clause(db_table, db_field, 0, 0)
649 680 when "ld"
650 681 # = yesterday
651 682 sql = relative_date_clause(db_table, db_field, -1, -1)
652 683 when "w"
653 684 # = this week
654 685 first_day_of_week = l(:general_first_day_of_week).to_i
655 686 day_of_week = Date.today.cwday
656 687 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
657 688 sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6)
658 689 when "lw"
659 690 # = last week
660 691 first_day_of_week = l(:general_first_day_of_week).to_i
661 692 day_of_week = Date.today.cwday
662 693 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
663 694 sql = relative_date_clause(db_table, db_field, - days_ago - 7, - days_ago - 1)
664 695 when "l2w"
665 696 # = last 2 weeks
666 697 first_day_of_week = l(:general_first_day_of_week).to_i
667 698 day_of_week = Date.today.cwday
668 699 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
669 700 sql = relative_date_clause(db_table, db_field, - days_ago - 14, - days_ago - 1)
670 701 when "m"
671 702 # = this month
672 703 date = Date.today
673 704 sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month)
674 705 when "lm"
675 706 # = last month
676 707 date = Date.today.prev_month
677 708 sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month)
678 709 when "y"
679 710 # = this year
680 711 date = Date.today
681 712 sql = date_clause(db_table, db_field, date.beginning_of_year, date.end_of_year)
682 713 when "~"
683 714 sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
684 715 when "!~"
685 716 sql = "LOWER(#{db_table}.#{db_field}) NOT LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
686 717 else
687 718 raise "Unknown query operator #{operator}"
688 719 end
689 720
690 721 return sql
691 722 end
692 723
693 724 def add_custom_fields_filters(custom_fields, assoc=nil)
694 725 return unless custom_fields.present?
695 @available_filters ||= {}
696 726
697 custom_fields.select(&:is_filter?).each do |field|
727 custom_fields.select(&:is_filter?).sort.each do |field|
698 728 case field.field_format
699 729 when "text"
700 options = { :type => :text, :order => 20 }
730 options = { :type => :text }
701 731 when "list"
702 options = { :type => :list_optional, :values => field.possible_values, :order => 20}
732 options = { :type => :list_optional, :values => field.possible_values }
703 733 when "date"
704 options = { :type => :date, :order => 20 }
734 options = { :type => :date }
705 735 when "bool"
706 options = { :type => :list, :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]], :order => 20 }
736 options = { :type => :list, :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]] }
707 737 when "int"
708 options = { :type => :integer, :order => 20 }
738 options = { :type => :integer }
709 739 when "float"
710 options = { :type => :float, :order => 20 }
740 options = { :type => :float }
711 741 when "user", "version"
712 742 next unless project
713 743 values = field.possible_values_options(project)
714 744 if User.current.logged? && field.field_format == 'user'
715 745 values.unshift ["<< #{l(:label_me)} >>", "me"]
716 746 end
717 options = { :type => :list_optional, :values => values, :order => 20}
747 options = { :type => :list_optional, :values => values }
718 748 else
719 options = { :type => :string, :order => 20 }
749 options = { :type => :string }
720 750 end
721 751 filter_id = "cf_#{field.id}"
722 752 filter_name = field.name
723 753 if assoc.present?
724 754 filter_id = "#{assoc}.#{filter_id}"
725 755 filter_name = l("label_attribute_of_#{assoc}", :name => filter_name)
726 756 end
727 @available_filters[filter_id] = options.merge({
757 add_available_filter filter_id, options.merge({
728 758 :name => filter_name,
729 759 :format => field.field_format,
730 760 :field => field
731 761 })
732 762 end
733 763 end
734 764
735 765 def add_associations_custom_fields_filters(*associations)
736 766 fields_by_class = CustomField.where(:is_filter => true).group_by(&:class)
737 767 associations.each do |assoc|
738 768 association_klass = queried_class.reflect_on_association(assoc).klass
739 769 fields_by_class.each do |field_class, fields|
740 770 if field_class.customized_class <= association_klass
741 771 add_custom_fields_filters(fields, assoc)
742 772 end
743 773 end
744 774 end
745 775 end
746 776
747 777 # Returns a SQL clause for a date or datetime field.
748 778 def date_clause(table, field, from, to)
749 779 s = []
750 780 if from
751 781 from_yesterday = from - 1
752 782 from_yesterday_time = Time.local(from_yesterday.year, from_yesterday.month, from_yesterday.day)
753 783 if self.class.default_timezone == :utc
754 784 from_yesterday_time = from_yesterday_time.utc
755 785 end
756 786 s << ("#{table}.#{field} > '%s'" % [connection.quoted_date(from_yesterday_time.end_of_day)])
757 787 end
758 788 if to
759 789 to_time = Time.local(to.year, to.month, to.day)
760 790 if self.class.default_timezone == :utc
761 791 to_time = to_time.utc
762 792 end
763 793 s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date(to_time.end_of_day)])
764 794 end
765 795 s.join(' AND ')
766 796 end
767 797
768 798 # Returns a SQL clause for a date or datetime field using relative dates.
769 799 def relative_date_clause(table, field, days_from, days_to)
770 800 date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil))
771 801 end
772 802
773 803 # Additional joins required for the given sort options
774 804 def joins_for_order_statement(order_options)
775 805 joins = []
776 806
777 807 if order_options
778 808 if order_options.include?('authors')
779 809 joins << "LEFT OUTER JOIN #{User.table_name} authors ON authors.id = #{queried_table_name}.author_id"
780 810 end
781 811 order_options.scan(/cf_\d+/).uniq.each do |name|
782 812 column = available_columns.detect {|c| c.name.to_s == name}
783 813 join = column && column.custom_field.join_for_order_statement
784 814 if join
785 815 joins << join
786 816 end
787 817 end
788 818 end
789 819
790 820 joins.any? ? joins.join(' ') : nil
791 821 end
792 822 end
Show file before | Show file after | Hide comments
@@ -1,123 +1,115
1 1 # Redmine - project management software
2 2 # Copyright (C) 2006-2013 Jean-Philippe Lang
3 3 #
4 4 # This program is free software; you can redistribute it and/or
5 5 # modify it under the terms of the GNU General Public License
6 6 # as published by the Free Software Foundation; either version 2
7 7 # of the License, or (at your option) any later version.
8 8 #
9 9 # This program is distributed in the hope that it will be useful,
10 10 # but WITHOUT ANY WARRANTY; without even the implied warranty of
11 11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 12 # GNU General Public License for more details.
13 13 #
14 14 # You should have received a copy of the GNU General Public License
15 15 # along with this program; if not, write to the Free Software
16 16 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
17 17
18 18 class TimeEntryQuery < Query
19 19
20 20 self.queried_class = TimeEntry
21 21
22 22 self.available_columns = [
23 23 QueryColumn.new(:project, :sortable => "#{Project.table_name}.name", :groupable => true),
24 24 QueryColumn.new(:spent_on, :sortable => ["#{TimeEntry.table_name}.spent_on", "#{TimeEntry.table_name}.created_on"], :default_order => 'desc', :groupable => true),
25 25 QueryColumn.new(:user, :sortable => lambda {User.fields_for_order_statement}, :groupable => true),
26 26 QueryColumn.new(:activity, :sortable => "#{TimeEntryActivity.table_name}.position", :groupable => true),
27 27 QueryColumn.new(:issue, :sortable => "#{Issue.table_name}.id"),
28 28 QueryColumn.new(:comments),
29 29 QueryColumn.new(:hours, :sortable => "#{TimeEntry.table_name}.hours"),
30 30 ]
31 31
32 32 def initialize(attributes=nil, *args)
33 33 super attributes
34 34 self.filters ||= {}
35 35 add_filter('spent_on', '*') unless filters.present?
36 36 end
37 37
38 def available_filters
39 return @available_filters if @available_filters
40 @available_filters = {
41 "spent_on" => { :type => :date_past, :order => 0 },
42 "comments" => { :type => :text, :order => 5 },
43 "hours" => { :type => :float, :order => 6 }
44 }
38 def initialize_available_filters
39 add_available_filter "spent_on", :type => :date_past
45 40
46 41 principals = []
47 42 if project
48 43 principals += project.principals.sort
49 44 unless project.leaf?
50 45 subprojects = project.descendants.visible.all
51 46 if subprojects.any?
52 @available_filters["subproject_id"] = {
53 :type => :list_subprojects, :order => 1,
47 add_available_filter "subproject_id",
48 :type => :list_subprojects,
54 49 :values => subprojects.collect{|s| [s.name, s.id.to_s] }
55 }
56 50 principals += Principal.member_of(subprojects)
57 51 end
58 52 end
59 53 else
60 54 if all_projects.any?
61 55 # members of visible projects
62 56 principals += Principal.member_of(all_projects)
63 57 # project filter
64 58 project_values = []
65 59 if User.current.logged? && User.current.memberships.any?
66 60 project_values << ["<< #{l(:label_my_projects).downcase} >>", "mine"]
67 61 end
68 62 project_values += all_projects_values
69 @available_filters["project_id"] = {
70 :type => :list, :order => 1, :values => project_values
71 } unless project_values.empty?
63 add_available_filter("project_id",
64 :type => :list, :values => project_values
65 ) unless project_values.empty?
72 66 end
73 67 end
74 68 principals.uniq!
75 69 principals.sort!
76 70 users = principals.select {|p| p.is_a?(User)}
77 71
78 72 users_values = []
79 73 users_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
80 74 users_values += users.collect{|s| [s.name, s.id.to_s] }
81 @available_filters["user_id"] = {
82 :type => :list_optional, :order => 2, :values => users_values
83 } unless users_values.empty?
75 add_available_filter("user_id",
76 :type => :list_optional, :values => users_values
77 ) unless users_values.empty?
84 78
85 79 activities = (project ? project.activities : TimeEntryActivity.shared.active)
86 @available_filters["activity_id"] = {
87 :type => :list, :order => 3, :values => activities.map {|a| [a.name, a.id.to_s]}
88 } unless activities.empty?
80 add_available_filter("activity_id",
81 :type => :list, :values => activities.map {|a| [a.name, a.id.to_s]}
82 ) unless activities.empty?
83
84 add_available_filter "comments", :type => :text
85 add_available_filter "hours", :type => :float
89 86
90 87 add_custom_fields_filters(TimeEntryCustomField.where(:is_filter => true).all)
91 88 add_associations_custom_fields_filters :project, :issue, :user
92
93 @available_filters.each do |field, options|
94 options[:name] ||= l(options[:label] || "field_#{field}".gsub(/_id$/, ''))
95 end
96 @available_filters
97 89 end
98 90
99 91 def available_columns
100 92 return @available_columns if @available_columns
101 93 @available_columns = self.class.available_columns.dup
102 94 @available_columns += TimeEntryCustomField.all.map {|cf| QueryCustomFieldColumn.new(cf) }
103 95 @available_columns += IssueCustomField.all.map {|cf| QueryAssociationCustomFieldColumn.new(:issue, cf) }
104 96 @available_columns
105 97 end
106 98
107 99 def default_columns_names
108 100 @default_columns_names ||= [:project, :spent_on, :user, :activity, :issue, :comments, :hours]
109 101 end
110 102
111 103 # Accepts :from/:to params as shortcut filters
112 104 def build_from_params(params)
113 105 super
114 106 if params[:from].present? && params[:to].present?
115 107 add_filter('spent_on', '><', [params[:from], params[:to]])
116 108 elsif params[:from].present?
117 109 add_filter('spent_on', '>=', [params[:from]])
118 110 elsif params[:to].present?
119 111 add_filter('spent_on', '<=', [params[:to]])
120 112 end
121 113 self
122 114 end
123 115 end
Show file before | Show file after | Hide comments
@@ -1,65 +1,64
1 1 # Redmine - project management software
2 2 # Copyright (C) 2006-2013 Jean-Philippe Lang
3 3 #
4 4 # This program is free software; you can redistribute it and/or
5 5 # modify it under the terms of the GNU General Public License
6 6 # as published by the Free Software Foundation; either version 2
7 7 # of the License, or (at your option) any later version.
8 8 #
9 9 # This program is distributed in the hope that it will be useful,
10 10 # but WITHOUT ANY WARRANTY; without even the implied warranty of
11 11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 12 # GNU General Public License for more details.
13 13 #
14 14 # You should have received a copy of the GNU General Public License
15 15 # along with this program; if not, write to the Free Software
16 16 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
17 17
18 18 require File.expand_path('../../../test_helper', __FILE__)
19 19
20 20 class QueriesHelperTest < ActionView::TestCase
21 21 include QueriesHelper
22 22 include Redmine::I18n
23 23
24 24 fixtures :projects, :enabled_modules, :users, :members,
25 25 :member_roles, :roles, :trackers, :issue_statuses,
26 26 :issue_categories, :enumerations, :issues,
27 27 :watchers, :custom_fields, :custom_values, :versions,
28 28 :queries,
29 29 :projects_trackers,
30 30 :custom_fields_trackers
31 31
32 def test_order
32 def test_filters_options_should_be_ordered
33 33 User.current = User.find_by_login('admin')
34 34 query = IssueQuery.new(:project => nil, :name => '_')
35 35 assert_equal 30, query.available_filters.size
36 36 fo = filters_options(query)
37 37 assert_equal 31, fo.size
38 38 assert_equal [], fo[0]
39 39 assert_equal "status_id", fo[1][1]
40 40 assert_equal "project_id", fo[2][1]
41 41 assert_equal "tracker_id", fo[3][1]
42 42 assert_equal "priority_id", fo[4][1]
43 assert_equal "watcher_id", fo[17][1]
44 assert_equal "is_private", fo[18][1]
43 assert_equal "is_private", fo[17][1]
44 assert_equal "watcher_id", fo[18][1]
45 45 end
46 46
47 def test_order_custom_fields
47 def test_filters_options_should_be_ordered_with_custom_fields
48 48 set_language_if_valid 'en'
49 field = UserCustomField.new(
49 field = UserCustomField.create!(
50 50 :name => 'order test', :field_format => 'string',
51 51 :is_for_all => true, :is_filter => true
52 52 )
53 assert field.save
54 53 User.current = User.find_by_login('admin')
55 54 query = IssueQuery.new(:project => nil, :name => '_')
56 55 assert_equal 32, query.available_filters.size
57 56 fo = filters_options(query)
58 57 assert_equal 33, fo.size
59 58 assert_equal "Searchable field", fo[19][0]
60 59 assert_equal "Database", fo[20][0]
61 60 assert_equal "Project's Development status", fo[21][0]
62 assert_equal "Assignee's order test", fo[22][0]
63 assert_equal "Author's order test", fo[23][0]
61 assert_equal "Author's order test", fo[22][0]
62 assert_equal "Assignee's order test", fo[23][0]
64 63 end
65 64 end
Show file before | Show file after | Hide comments
@@ -1,1211 +1,1216
1 1 # Redmine - project management software
2 2 # Copyright (C) 2006-2013 Jean-Philippe Lang
3 3 #
4 4 # This program is free software; you can redistribute it and/or
5 5 # modify it under the terms of the GNU General Public License
6 6 # as published by the Free Software Foundation; either version 2
7 7 # of the License, or (at your option) any later version.
8 8 #
9 9 # This program is distributed in the hope that it will be useful,
10 10 # but WITHOUT ANY WARRANTY; without even the implied warranty of
11 11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 12 # GNU General Public License for more details.
13 13 #
14 14 # You should have received a copy of the GNU General Public License
15 15 # along with this program; if not, write to the Free Software
16 16 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
17 17
18 18 require File.expand_path('../../test_helper', __FILE__)
19 19
20 20 class QueryTest < ActiveSupport::TestCase
21 21 include Redmine::I18n
22 22
23 23 fixtures :projects, :enabled_modules, :users, :members,
24 24 :member_roles, :roles, :trackers, :issue_statuses,
25 25 :issue_categories, :enumerations, :issues,
26 26 :watchers, :custom_fields, :custom_values, :versions,
27 27 :queries,
28 28 :projects_trackers,
29 29 :custom_fields_trackers
30 30
31 def test_available_filters_should_be_ordered
32 query = IssueQuery.new
33 assert_equal 0, query.available_filters.keys.index('status_id')
34 end
35
31 36 def test_custom_fields_for_all_projects_should_be_available_in_global_queries
32 37 query = IssueQuery.new(:project => nil, :name => '_')
33 38 assert query.available_filters.has_key?('cf_1')
34 39 assert !query.available_filters.has_key?('cf_3')
35 40 end
36 41
37 42 def test_system_shared_versions_should_be_available_in_global_queries
38 43 Version.find(2).update_attribute :sharing, 'system'
39 44 query = IssueQuery.new(:project => nil, :name => '_')
40 45 assert query.available_filters.has_key?('fixed_version_id')
41 46 assert query.available_filters['fixed_version_id'][:values].detect {|v| v.last == '2'}
42 47 end
43 48
44 49 def test_project_filter_in_global_queries
45 50 query = IssueQuery.new(:project => nil, :name => '_')
46 51 project_filter = query.available_filters["project_id"]
47 52 assert_not_nil project_filter
48 53 project_ids = project_filter[:values].map{|p| p[1]}
49 54 assert project_ids.include?("1") #public project
50 55 assert !project_ids.include?("2") #private project user cannot see
51 56 end
52 57
53 58 def find_issues_with_query(query)
54 59 Issue.includes([:assigned_to, :status, :tracker, :project, :priority]).where(
55 60 query.statement
56 61 ).all
57 62 end
58 63
59 64 def assert_find_issues_with_query_is_successful(query)
60 65 assert_nothing_raised do
61 66 find_issues_with_query(query)
62 67 end
63 68 end
64 69
65 70 def assert_query_statement_includes(query, condition)
66 71 assert_include condition, query.statement
67 72 end
68 73
69 74 def assert_query_result(expected, query)
70 75 assert_nothing_raised do
71 76 assert_equal expected.map(&:id).sort, query.issues.map(&:id).sort
72 77 assert_equal expected.size, query.issue_count
73 78 end
74 79 end
75 80
76 81 def test_query_should_allow_shared_versions_for_a_project_query
77 82 subproject_version = Version.find(4)
78 83 query = IssueQuery.new(:project => Project.find(1), :name => '_')
79 84 query.add_filter('fixed_version_id', '=', [subproject_version.id.to_s])
80 85
81 86 assert query.statement.include?("#{Issue.table_name}.fixed_version_id IN ('4')")
82 87 end
83 88
84 89 def test_query_with_multiple_custom_fields
85 90 query = IssueQuery.find(1)
86 91 assert query.valid?
87 92 assert query.statement.include?("#{CustomValue.table_name}.value IN ('MySQL')")
88 93 issues = find_issues_with_query(query)
89 94 assert_equal 1, issues.length
90 95 assert_equal Issue.find(3), issues.first
91 96 end
92 97
93 98 def test_operator_none
94 99 query = IssueQuery.new(:project => Project.find(1), :name => '_')
95 100 query.add_filter('fixed_version_id', '!*', [''])
96 101 query.add_filter('cf_1', '!*', [''])
97 102 assert query.statement.include?("#{Issue.table_name}.fixed_version_id IS NULL")
98 103 assert query.statement.include?("#{CustomValue.table_name}.value IS NULL OR #{CustomValue.table_name}.value = ''")
99 104 find_issues_with_query(query)
100 105 end
101 106
102 107 def test_operator_none_for_integer
103 108 query = IssueQuery.new(:project => Project.find(1), :name => '_')
104 109 query.add_filter('estimated_hours', '!*', [''])
105 110 issues = find_issues_with_query(query)
106 111 assert !issues.empty?
107 112 assert issues.all? {|i| !i.estimated_hours}
108 113 end
109 114
110 115 def test_operator_none_for_date
111 116 query = IssueQuery.new(:project => Project.find(1), :name => '_')
112 117 query.add_filter('start_date', '!*', [''])
113 118 issues = find_issues_with_query(query)
114 119 assert !issues.empty?
115 120 assert issues.all? {|i| i.start_date.nil?}
116 121 end
117 122
118 123 def test_operator_none_for_string_custom_field
119 124 query = IssueQuery.new(:project => Project.find(1), :name => '_')
120 125 query.add_filter('cf_2', '!*', [''])
121 126 assert query.has_filter?('cf_2')
122 127 issues = find_issues_with_query(query)
123 128 assert !issues.empty?
124 129 assert issues.all? {|i| i.custom_field_value(2).blank?}
125 130 end
126 131
127 132 def test_operator_all
128 133 query = IssueQuery.new(:project => Project.find(1), :name => '_')
129 134 query.add_filter('fixed_version_id', '*', [''])
130 135 query.add_filter('cf_1', '*', [''])
131 136 assert query.statement.include?("#{Issue.table_name}.fixed_version_id IS NOT NULL")
132 137 assert query.statement.include?("#{CustomValue.table_name}.value IS NOT NULL AND #{CustomValue.table_name}.value <> ''")
133 138 find_issues_with_query(query)
134 139 end
135 140
136 141 def test_operator_all_for_date
137 142 query = IssueQuery.new(:project => Project.find(1), :name => '_')
138 143 query.add_filter('start_date', '*', [''])
139 144 issues = find_issues_with_query(query)
140 145 assert !issues.empty?
141 146 assert issues.all? {|i| i.start_date.present?}
142 147 end
143 148
144 149 def test_operator_all_for_string_custom_field
145 150 query = IssueQuery.new(:project => Project.find(1), :name => '_')
146 151 query.add_filter('cf_2', '*', [''])
147 152 assert query.has_filter?('cf_2')
148 153 issues = find_issues_with_query(query)
149 154 assert !issues.empty?
150 155 assert issues.all? {|i| i.custom_field_value(2).present?}
151 156 end
152 157
153 158 def test_numeric_filter_should_not_accept_non_numeric_values
154 159 query = IssueQuery.new(:name => '_')
155 160 query.add_filter('estimated_hours', '=', ['a'])
156 161
157 162 assert query.has_filter?('estimated_hours')
158 163 assert !query.valid?
159 164 end
160 165
161 166 def test_operator_is_on_float
162 167 Issue.update_all("estimated_hours = 171.2", "id=2")
163 168
164 169 query = IssueQuery.new(:name => '_')
165 170 query.add_filter('estimated_hours', '=', ['171.20'])
166 171 issues = find_issues_with_query(query)
167 172 assert_equal 1, issues.size
168 173 assert_equal 2, issues.first.id
169 174 end
170 175
171 176 def test_operator_is_on_integer_custom_field
172 177 f = IssueCustomField.create!(:name => 'filter', :field_format => 'int', :is_for_all => true, :is_filter => true)
173 178 CustomValue.create!(:custom_field => f, :customized => Issue.find(1), :value => '7')
174 179 CustomValue.create!(:custom_field => f, :customized => Issue.find(2), :value => '12')
175 180 CustomValue.create!(:custom_field => f, :customized => Issue.find(3), :value => '')
176 181
177 182 query = IssueQuery.new(:name => '_')
178 183 query.add_filter("cf_#{f.id}", '=', ['12'])
179 184 issues = find_issues_with_query(query)
180 185 assert_equal 1, issues.size
181 186 assert_equal 2, issues.first.id
182 187 end
183 188
184 189 def test_operator_is_on_integer_custom_field_should_accept_negative_value
185 190 f = IssueCustomField.create!(:name => 'filter', :field_format => 'int', :is_for_all => true, :is_filter => true)
186 191 CustomValue.create!(:custom_field => f, :customized => Issue.find(1), :value => '7')
187 192 CustomValue.create!(:custom_field => f, :customized => Issue.find(2), :value => '-12')
188 193 CustomValue.create!(:custom_field => f, :customized => Issue.find(3), :value => '')
189 194
190 195 query = IssueQuery.new(:name => '_')
191 196 query.add_filter("cf_#{f.id}", '=', ['-12'])
192 197 assert query.valid?
193 198 issues = find_issues_with_query(query)
194 199 assert_equal 1, issues.size
195 200 assert_equal 2, issues.first.id
196 201 end
197 202
198 203 def test_operator_is_on_float_custom_field
199 204 f = IssueCustomField.create!(:name => 'filter', :field_format => 'float', :is_filter => true, :is_for_all => true)
200 205 CustomValue.create!(:custom_field => f, :customized => Issue.find(1), :value => '7.3')
201 206 CustomValue.create!(:custom_field => f, :customized => Issue.find(2), :value => '12.7')
202 207 CustomValue.create!(:custom_field => f, :customized => Issue.find(3), :value => '')
203 208
204 209 query = IssueQuery.new(:name => '_')
205 210 query.add_filter("cf_#{f.id}", '=', ['12.7'])
206 211 issues = find_issues_with_query(query)
207 212 assert_equal 1, issues.size
208 213 assert_equal 2, issues.first.id
209 214 end
210 215
211 216 def test_operator_is_on_float_custom_field_should_accept_negative_value
212 217 f = IssueCustomField.create!(:name => 'filter', :field_format => 'float', :is_filter => true, :is_for_all => true)
213 218 CustomValue.create!(:custom_field => f, :customized => Issue.find(1), :value => '7.3')
214 219 CustomValue.create!(:custom_field => f, :customized => Issue.find(2), :value => '-12.7')
215 220 CustomValue.create!(:custom_field => f, :customized => Issue.find(3), :value => '')
216 221
217 222 query = IssueQuery.new(:name => '_')
218 223 query.add_filter("cf_#{f.id}", '=', ['-12.7'])
219 224 assert query.valid?
220 225 issues = find_issues_with_query(query)
221 226 assert_equal 1, issues.size
222 227 assert_equal 2, issues.first.id
223 228 end
224 229
225 230 def test_operator_is_on_multi_list_custom_field
226 231 f = IssueCustomField.create!(:name => 'filter', :field_format => 'list', :is_filter => true, :is_for_all => true,
227 232 :possible_values => ['value1', 'value2', 'value3'], :multiple => true)
228 233 CustomValue.create!(:custom_field => f, :customized => Issue.find(1), :value => 'value1')
229 234 CustomValue.create!(:custom_field => f, :customized => Issue.find(1), :value => 'value2')
230 235 CustomValue.create!(:custom_field => f, :customized => Issue.find(3), :value => 'value1')
231 236
232 237 query = IssueQuery.new(:name => '_')
233 238 query.add_filter("cf_#{f.id}", '=', ['value1'])
234 239 issues = find_issues_with_query(query)
235 240 assert_equal [1, 3], issues.map(&:id).sort
236 241
237 242 query = IssueQuery.new(:name => '_')
238 243 query.add_filter("cf_#{f.id}", '=', ['value2'])
239 244 issues = find_issues_with_query(query)
240 245 assert_equal [1], issues.map(&:id).sort
241 246 end
242 247
243 248 def test_operator_is_not_on_multi_list_custom_field
244 249 f = IssueCustomField.create!(:name => 'filter', :field_format => 'list', :is_filter => true, :is_for_all => true,
245 250 :possible_values => ['value1', 'value2', 'value3'], :multiple => true)
246 251 CustomValue.create!(:custom_field => f, :customized => Issue.find(1), :value => 'value1')
247 252 CustomValue.create!(:custom_field => f, :customized => Issue.find(1), :value => 'value2')
248 253 CustomValue.create!(:custom_field => f, :customized => Issue.find(3), :value => 'value1')
249 254
250 255 query = IssueQuery.new(:name => '_')
251 256 query.add_filter("cf_#{f.id}", '!', ['value1'])
252 257 issues = find_issues_with_query(query)
253 258 assert !issues.map(&:id).include?(1)
254 259 assert !issues.map(&:id).include?(3)
255 260
256 261 query = IssueQuery.new(:name => '_')
257 262 query.add_filter("cf_#{f.id}", '!', ['value2'])
258 263 issues = find_issues_with_query(query)
259 264 assert !issues.map(&:id).include?(1)
260 265 assert issues.map(&:id).include?(3)
261 266 end
262 267
263 268 def test_operator_is_on_is_private_field
264 269 # is_private filter only available for those who can set issues private
265 270 User.current = User.find(2)
266 271
267 272 query = IssueQuery.new(:name => '_')
268 273 assert query.available_filters.key?('is_private')
269 274
270 275 query.add_filter("is_private", '=', ['1'])
271 276 issues = find_issues_with_query(query)
272 277 assert issues.any?
273 278 assert_nil issues.detect {|issue| !issue.is_private?}
274 279 ensure
275 280 User.current = nil
276 281 end
277 282
278 283 def test_operator_is_not_on_is_private_field
279 284 # is_private filter only available for those who can set issues private
280 285 User.current = User.find(2)
281 286
282 287 query = IssueQuery.new(:name => '_')
283 288 assert query.available_filters.key?('is_private')
284 289
285 290 query.add_filter("is_private", '!', ['1'])
286 291 issues = find_issues_with_query(query)
287 292 assert issues.any?
288 293 assert_nil issues.detect {|issue| issue.is_private?}
289 294 ensure
290 295 User.current = nil
291 296 end
292 297
293 298 def test_operator_greater_than
294 299 query = IssueQuery.new(:project => Project.find(1), :name => '_')
295 300 query.add_filter('done_ratio', '>=', ['40'])
296 301 assert query.statement.include?("#{Issue.table_name}.done_ratio >= 40.0")
297 302 find_issues_with_query(query)
298 303 end
299 304
300 305 def test_operator_greater_than_a_float
301 306 query = IssueQuery.new(:project => Project.find(1), :name => '_')
302 307 query.add_filter('estimated_hours', '>=', ['40.5'])
303 308 assert query.statement.include?("#{Issue.table_name}.estimated_hours >= 40.5")
304 309 find_issues_with_query(query)
305 310 end
306 311
307 312 def test_operator_greater_than_on_int_custom_field
308 313 f = IssueCustomField.create!(:name => 'filter', :field_format => 'int', :is_filter => true, :is_for_all => true)
309 314 CustomValue.create!(:custom_field => f, :customized => Issue.find(1), :value => '7')
310 315 CustomValue.create!(:custom_field => f, :customized => Issue.find(2), :value => '12')
311 316 CustomValue.create!(:custom_field => f, :customized => Issue.find(3), :value => '')
312 317
313 318 query = IssueQuery.new(:project => Project.find(1), :name => '_')
314 319 query.add_filter("cf_#{f.id}", '>=', ['8'])
315 320 issues = find_issues_with_query(query)
316 321 assert_equal 1, issues.size
317 322 assert_equal 2, issues.first.id
318 323 end
319 324
320 325 def test_operator_lesser_than
321 326 query = IssueQuery.new(:project => Project.find(1), :name => '_')
322 327 query.add_filter('done_ratio', '<=', ['30'])
323 328 assert query.statement.include?("#{Issue.table_name}.done_ratio <= 30.0")
324 329 find_issues_with_query(query)
325 330 end
326 331
327 332 def test_operator_lesser_than_on_custom_field
328 333 f = IssueCustomField.create!(:name => 'filter', :field_format => 'int', :is_filter => true, :is_for_all => true)
329 334 query = IssueQuery.new(:project => Project.find(1), :name => '_')
330 335 query.add_filter("cf_#{f.id}", '<=', ['30'])
331 336 assert_match /CAST.+ <= 30\.0/, query.statement
332 337 find_issues_with_query(query)
333 338 end
334 339
335 340 def test_operator_between
336 341 query = IssueQuery.new(:project => Project.find(1), :name => '_')
337 342 query.add_filter('done_ratio', '><', ['30', '40'])
338 343 assert_include "#{Issue.table_name}.done_ratio BETWEEN 30.0 AND 40.0", query.statement
339 344 find_issues_with_query(query)
340 345 end
341 346
342 347 def test_operator_between_on_custom_field
343 348 f = IssueCustomField.create!(:name => 'filter', :field_format => 'int', :is_filter => true, :is_for_all => true)
344 349 query = IssueQuery.new(:project => Project.find(1), :name => '_')
345 350 query.add_filter("cf_#{f.id}", '><', ['30', '40'])
346 351 assert_match /CAST.+ BETWEEN 30.0 AND 40.0/, query.statement
347 352 find_issues_with_query(query)
348 353 end
349 354
350 355 def test_date_filter_should_not_accept_non_date_values
351 356 query = IssueQuery.new(:name => '_')
352 357 query.add_filter('created_on', '=', ['a'])
353 358
354 359 assert query.has_filter?('created_on')
355 360 assert !query.valid?
356 361 end
357 362
358 363 def test_date_filter_should_not_accept_invalid_date_values
359 364 query = IssueQuery.new(:name => '_')
360 365 query.add_filter('created_on', '=', ['2011-01-34'])
361 366
362 367 assert query.has_filter?('created_on')
363 368 assert !query.valid?
364 369 end
365 370
366 371 def test_relative_date_filter_should_not_accept_non_integer_values
367 372 query = IssueQuery.new(:name => '_')
368 373 query.add_filter('created_on', '>t-', ['a'])
369 374
370 375 assert query.has_filter?('created_on')
371 376 assert !query.valid?
372 377 end
373 378
374 379 def test_operator_date_equals
375 380 query = IssueQuery.new(:name => '_')
376 381 query.add_filter('due_date', '=', ['2011-07-10'])
377 382 assert_match /issues\.due_date > '2011-07-09 23:59:59(\.9+)?' AND issues\.due_date <= '2011-07-10 23:59:59(\.9+)?/, query.statement
378 383 find_issues_with_query(query)
379 384 end
380 385
381 386 def test_operator_date_lesser_than
382 387 query = IssueQuery.new(:name => '_')
383 388 query.add_filter('due_date', '<=', ['2011-07-10'])
384 389 assert_match /issues\.due_date <= '2011-07-10 23:59:59(\.9+)?/, query.statement
385 390 find_issues_with_query(query)
386 391 end
387 392
388 393 def test_operator_date_greater_than
389 394 query = IssueQuery.new(:name => '_')
390 395 query.add_filter('due_date', '>=', ['2011-07-10'])
391 396 assert_match /issues\.due_date > '2011-07-09 23:59:59(\.9+)?'/, query.statement
392 397 find_issues_with_query(query)
393 398 end
394 399
395 400 def test_operator_date_between
396 401 query = IssueQuery.new(:name => '_')
397 402 query.add_filter('due_date', '><', ['2011-06-23', '2011-07-10'])
398 403 assert_match /issues\.due_date > '2011-06-22 23:59:59(\.9+)?' AND issues\.due_date <= '2011-07-10 23:59:59(\.9+)?/, query.statement
399 404 find_issues_with_query(query)
400 405 end
401 406
402 407 def test_operator_in_more_than
403 408 Issue.find(7).update_attribute(:due_date, (Date.today + 15))
404 409 query = IssueQuery.new(:project => Project.find(1), :name => '_')
405 410 query.add_filter('due_date', '>t+', ['15'])
406 411 issues = find_issues_with_query(query)
407 412 assert !issues.empty?
408 413 issues.each {|issue| assert(issue.due_date >= (Date.today + 15))}
409 414 end
410 415
411 416 def test_operator_in_less_than
412 417 query = IssueQuery.new(:project => Project.find(1), :name => '_')
413 418 query.add_filter('due_date', '<t+', ['15'])
414 419 issues = find_issues_with_query(query)
415 420 assert !issues.empty?
416 421 issues.each {|issue| assert(issue.due_date <= (Date.today + 15))}
417 422 end
418 423
419 424 def test_operator_in_the_next_days
420 425 query = IssueQuery.new(:project => Project.find(1), :name => '_')
421 426 query.add_filter('due_date', '><t+', ['15'])
422 427 issues = find_issues_with_query(query)
423 428 assert !issues.empty?
424 429 issues.each {|issue| assert(issue.due_date >= Date.today && issue.due_date <= (Date.today + 15))}
425 430 end
426 431
427 432 def test_operator_less_than_ago
428 433 Issue.find(7).update_attribute(:due_date, (Date.today - 3))
429 434 query = IssueQuery.new(:project => Project.find(1), :name => '_')
430 435 query.add_filter('due_date', '>t-', ['3'])
431 436 issues = find_issues_with_query(query)
432 437 assert !issues.empty?
433 438 issues.each {|issue| assert(issue.due_date >= (Date.today - 3))}
434 439 end
435 440
436 441 def test_operator_in_the_past_days
437 442 Issue.find(7).update_attribute(:due_date, (Date.today - 3))
438 443 query = IssueQuery.new(:project => Project.find(1), :name => '_')
439 444 query.add_filter('due_date', '><t-', ['3'])
440 445 issues = find_issues_with_query(query)
441 446 assert !issues.empty?
442 447 issues.each {|issue| assert(issue.due_date >= (Date.today - 3) && issue.due_date <= Date.today)}
443 448 end
444 449
445 450 def test_operator_more_than_ago
446 451 Issue.find(7).update_attribute(:due_date, (Date.today - 10))
447 452 query = IssueQuery.new(:project => Project.find(1), :name => '_')
448 453 query.add_filter('due_date', '<t-', ['10'])
449 454 assert query.statement.include?("#{Issue.table_name}.due_date <=")
450 455 issues = find_issues_with_query(query)
451 456 assert !issues.empty?
452 457 issues.each {|issue| assert(issue.due_date <= (Date.today - 10))}
453 458 end
454 459
455 460 def test_operator_in
456 461 Issue.find(7).update_attribute(:due_date, (Date.today + 2))
457 462 query = IssueQuery.new(:project => Project.find(1), :name => '_')
458 463 query.add_filter('due_date', 't+', ['2'])
459 464 issues = find_issues_with_query(query)
460 465 assert !issues.empty?
461 466 issues.each {|issue| assert_equal((Date.today + 2), issue.due_date)}
462 467 end
463 468
464 469 def test_operator_ago
465 470 Issue.find(7).update_attribute(:due_date, (Date.today - 3))
466 471 query = IssueQuery.new(:project => Project.find(1), :name => '_')
467 472 query.add_filter('due_date', 't-', ['3'])
468 473 issues = find_issues_with_query(query)
469 474 assert !issues.empty?
470 475 issues.each {|issue| assert_equal((Date.today - 3), issue.due_date)}
471 476 end
472 477
473 478 def test_operator_today
474 479 query = IssueQuery.new(:project => Project.find(1), :name => '_')
475 480 query.add_filter('due_date', 't', [''])
476 481 issues = find_issues_with_query(query)
477 482 assert !issues.empty?
478 483 issues.each {|issue| assert_equal Date.today, issue.due_date}
479 484 end
480 485
481 486 def test_operator_this_week_on_date
482 487 query = IssueQuery.new(:project => Project.find(1), :name => '_')
483 488 query.add_filter('due_date', 'w', [''])
484 489 find_issues_with_query(query)
485 490 end
486 491
487 492 def test_operator_this_week_on_datetime
488 493 query = IssueQuery.new(:project => Project.find(1), :name => '_')
489 494 query.add_filter('created_on', 'w', [''])
490 495 find_issues_with_query(query)
491 496 end
492 497
493 498 def test_operator_contains
494 499 query = IssueQuery.new(:project => Project.find(1), :name => '_')
495 500 query.add_filter('subject', '~', ['uNable'])
496 501 assert query.statement.include?("LOWER(#{Issue.table_name}.subject) LIKE '%unable%'")
497 502 result = find_issues_with_query(query)
498 503 assert result.empty?
499 504 result.each {|issue| assert issue.subject.downcase.include?('unable') }
500 505 end
501 506
502 507 def test_range_for_this_week_with_week_starting_on_monday
503 508 I18n.locale = :fr
504 509 assert_equal '1', I18n.t(:general_first_day_of_week)
505 510
506 511 Date.stubs(:today).returns(Date.parse('2011-04-29'))
507 512
508 513 query = IssueQuery.new(:project => Project.find(1), :name => '_')
509 514 query.add_filter('due_date', 'w', [''])
510 515 assert query.statement.match(/issues\.due_date > '2011-04-24 23:59:59(\.9+)?' AND issues\.due_date <= '2011-05-01 23:59:59(\.9+)?/), "range not found in #{query.statement}"
511 516 I18n.locale = :en
512 517 end
513 518
514 519 def test_range_for_this_week_with_week_starting_on_sunday
515 520 I18n.locale = :en
516 521 assert_equal '7', I18n.t(:general_first_day_of_week)
517 522
518 523 Date.stubs(:today).returns(Date.parse('2011-04-29'))
519 524
520 525 query = IssueQuery.new(:project => Project.find(1), :name => '_')
521 526 query.add_filter('due_date', 'w', [''])
522 527 assert query.statement.match(/issues\.due_date > '2011-04-23 23:59:59(\.9+)?' AND issues\.due_date <= '2011-04-30 23:59:59(\.9+)?/), "range not found in #{query.statement}"
523 528 end
524 529
525 530 def test_operator_does_not_contains
526 531 query = IssueQuery.new(:project => Project.find(1), :name => '_')
527 532 query.add_filter('subject', '!~', ['uNable'])
528 533 assert query.statement.include?("LOWER(#{Issue.table_name}.subject) NOT LIKE '%unable%'")
529 534 find_issues_with_query(query)
530 535 end
531 536
532 537 def test_filter_assigned_to_me
533 538 user = User.find(2)
534 539 group = Group.find(10)
535 540 User.current = user
536 541 i1 = Issue.generate!(:project_id => 1, :tracker_id => 1, :assigned_to => user)
537 542 i2 = Issue.generate!(:project_id => 1, :tracker_id => 1, :assigned_to => group)
538 543 i3 = Issue.generate!(:project_id => 1, :tracker_id => 1, :assigned_to => Group.find(11))
539 544 group.users << user
540 545
541 546 query = IssueQuery.new(:name => '_', :filters => { 'assigned_to_id' => {:operator => '=', :values => ['me']}})
542 547 result = query.issues
543 548 assert_equal Issue.visible.all(:conditions => {:assigned_to_id => ([2] + user.reload.group_ids)}).sort_by(&:id), result.sort_by(&:id)
544 549
545 550 assert result.include?(i1)
546 551 assert result.include?(i2)
547 552 assert !result.include?(i3)
548 553 end
549 554
550 555 def test_user_custom_field_filtered_on_me
551 556 User.current = User.find(2)
552 557 cf = IssueCustomField.create!(:field_format => 'user', :is_for_all => true, :is_filter => true, :name => 'User custom field', :tracker_ids => [1])
553 558 issue1 = Issue.create!(:project_id => 1, :tracker_id => 1, :custom_field_values => {cf.id.to_s => '2'}, :subject => 'Test', :author_id => 1)
554 559 issue2 = Issue.generate!(:project_id => 1, :tracker_id => 1, :custom_field_values => {cf.id.to_s => '3'})
555 560
556 561 query = IssueQuery.new(:name => '_', :project => Project.find(1))
557 562 filter = query.available_filters["cf_#{cf.id}"]
558 563 assert_not_nil filter
559 564 assert_include 'me', filter[:values].map{|v| v[1]}
560 565
561 566 query.filters = { "cf_#{cf.id}" => {:operator => '=', :values => ['me']}}
562 567 result = query.issues
563 568 assert_equal 1, result.size
564 569 assert_equal issue1, result.first
565 570 end
566 571
567 572 def test_filter_my_projects
568 573 User.current = User.find(2)
569 574 query = IssueQuery.new(:name => '_')
570 575 filter = query.available_filters['project_id']
571 576 assert_not_nil filter
572 577 assert_include 'mine', filter[:values].map{|v| v[1]}
573 578
574 579 query.filters = { 'project_id' => {:operator => '=', :values => ['mine']}}
575 580 result = query.issues
576 581 assert_nil result.detect {|issue| !User.current.member_of?(issue.project)}
577 582 end
578 583
579 584 def test_filter_watched_issues
580 585 User.current = User.find(1)
581 586 query = IssueQuery.new(:name => '_', :filters => { 'watcher_id' => {:operator => '=', :values => ['me']}})
582 587 result = find_issues_with_query(query)
583 588 assert_not_nil result
584 589 assert !result.empty?
585 590 assert_equal Issue.visible.watched_by(User.current).sort_by(&:id), result.sort_by(&:id)
586 591 User.current = nil
587 592 end
588 593
589 594 def test_filter_unwatched_issues
590 595 User.current = User.find(1)
591 596 query = IssueQuery.new(:name => '_', :filters => { 'watcher_id' => {:operator => '!', :values => ['me']}})
592 597 result = find_issues_with_query(query)
593 598 assert_not_nil result
594 599 assert !result.empty?
595 600 assert_equal((Issue.visible - Issue.watched_by(User.current)).sort_by(&:id).size, result.sort_by(&:id).size)
596 601 User.current = nil
597 602 end
598 603
599 604 def test_filter_on_project_custom_field
600 605 field = ProjectCustomField.create!(:name => 'Client', :is_filter => true, :field_format => 'string')
601 606 CustomValue.create!(:custom_field => field, :customized => Project.find(3), :value => 'Foo')
602 607 CustomValue.create!(:custom_field => field, :customized => Project.find(5), :value => 'Foo')
603 608
604 609 query = IssueQuery.new(:name => '_')
605 610 filter_name = "project.cf_#{field.id}"
606 611 assert_include filter_name, query.available_filters.keys
607 612 query.filters = {filter_name => {:operator => '=', :values => ['Foo']}}
608 613 assert_equal [3, 5], find_issues_with_query(query).map(&:project_id).uniq.sort
609 614 end
610 615
611 616 def test_filter_on_author_custom_field
612 617 field = UserCustomField.create!(:name => 'Client', :is_filter => true, :field_format => 'string')
613 618 CustomValue.create!(:custom_field => field, :customized => User.find(3), :value => 'Foo')
614 619
615 620 query = IssueQuery.new(:name => '_')
616 621 filter_name = "author.cf_#{field.id}"
617 622 assert_include filter_name, query.available_filters.keys
618 623 query.filters = {filter_name => {:operator => '=', :values => ['Foo']}}
619 624 assert_equal [3], find_issues_with_query(query).map(&:author_id).uniq.sort
620 625 end
621 626
622 627 def test_filter_on_assigned_to_custom_field
623 628 field = UserCustomField.create!(:name => 'Client', :is_filter => true, :field_format => 'string')
624 629 CustomValue.create!(:custom_field => field, :customized => User.find(3), :value => 'Foo')
625 630
626 631 query = IssueQuery.new(:name => '_')
627 632 filter_name = "assigned_to.cf_#{field.id}"
628 633 assert_include filter_name, query.available_filters.keys
629 634 query.filters = {filter_name => {:operator => '=', :values => ['Foo']}}
630 635 assert_equal [3], find_issues_with_query(query).map(&:assigned_to_id).uniq.sort
631 636 end
632 637
633 638 def test_filter_on_fixed_version_custom_field
634 639 field = VersionCustomField.create!(:name => 'Client', :is_filter => true, :field_format => 'string')
635 640 CustomValue.create!(:custom_field => field, :customized => Version.find(2), :value => 'Foo')
636 641
637 642 query = IssueQuery.new(:name => '_')
638 643 filter_name = "fixed_version.cf_#{field.id}"
639 644 assert_include filter_name, query.available_filters.keys
640 645 query.filters = {filter_name => {:operator => '=', :values => ['Foo']}}
641 646 assert_equal [2], find_issues_with_query(query).map(&:fixed_version_id).uniq.sort
642 647 end
643 648
644 649 def test_filter_on_relations_with_a_specific_issue
645 650 IssueRelation.delete_all
646 651 IssueRelation.create!(:relation_type => "relates", :issue_from => Issue.find(1), :issue_to => Issue.find(2))
647 652 IssueRelation.create!(:relation_type => "relates", :issue_from => Issue.find(3), :issue_to => Issue.find(1))
648 653
649 654 query = IssueQuery.new(:name => '_')
650 655 query.filters = {"relates" => {:operator => '=', :values => ['1']}}
651 656 assert_equal [2, 3], find_issues_with_query(query).map(&:id).sort
652 657
653 658 query = IssueQuery.new(:name => '_')
654 659 query.filters = {"relates" => {:operator => '=', :values => ['2']}}
655 660 assert_equal [1], find_issues_with_query(query).map(&:id).sort
656 661 end
657 662
658 663 def test_filter_on_relations_with_any_issues_in_a_project
659 664 IssueRelation.delete_all
660 665 with_settings :cross_project_issue_relations => '1' do
661 666 IssueRelation.create!(:relation_type => "relates", :issue_from => Issue.find(1), :issue_to => Project.find(2).issues.first)
662 667 IssueRelation.create!(:relation_type => "relates", :issue_from => Issue.find(2), :issue_to => Project.find(2).issues.first)
663 668 IssueRelation.create!(:relation_type => "relates", :issue_from => Issue.find(1), :issue_to => Project.find(3).issues.first)
664 669 end
665 670
666 671 query = IssueQuery.new(:name => '_')
667 672 query.filters = {"relates" => {:operator => '=p', :values => ['2']}}
668 673 assert_equal [1, 2], find_issues_with_query(query).map(&:id).sort
669 674
670 675 query = IssueQuery.new(:name => '_')
671 676 query.filters = {"relates" => {:operator => '=p', :values => ['3']}}
672 677 assert_equal [1], find_issues_with_query(query).map(&:id).sort
673 678
674 679 query = IssueQuery.new(:name => '_')
675 680 query.filters = {"relates" => {:operator => '=p', :values => ['4']}}
676 681 assert_equal [], find_issues_with_query(query).map(&:id).sort
677 682 end
678 683
679 684 def test_filter_on_relations_with_any_issues_not_in_a_project
680 685 IssueRelation.delete_all
681 686 with_settings :cross_project_issue_relations => '1' do
682 687 IssueRelation.create!(:relation_type => "relates", :issue_from => Issue.find(1), :issue_to => Project.find(2).issues.first)
683 688 #IssueRelation.create!(:relation_type => "relates", :issue_from => Issue.find(2), :issue_to => Project.find(1).issues.first)
684 689 IssueRelation.create!(:relation_type => "relates", :issue_from => Issue.find(1), :issue_to => Project.find(3).issues.first)
685 690 end
686 691
687 692 query = IssueQuery.new(:name => '_')
688 693 query.filters = {"relates" => {:operator => '=!p', :values => ['1']}}
689 694 assert_equal [1], find_issues_with_query(query).map(&:id).sort
690 695 end
691 696
692 697 def test_filter_on_relations_with_no_issues_in_a_project
693 698 IssueRelation.delete_all
694 699 with_settings :cross_project_issue_relations => '1' do
695 700 IssueRelation.create!(:relation_type => "relates", :issue_from => Issue.find(1), :issue_to => Project.find(2).issues.first)
696 701 IssueRelation.create!(:relation_type => "relates", :issue_from => Issue.find(2), :issue_to => Project.find(3).issues.first)
697 702 IssueRelation.create!(:relation_type => "relates", :issue_to => Project.find(2).issues.first, :issue_from => Issue.find(3))
698 703 end
699 704
700 705 query = IssueQuery.new(:name => '_')
701 706 query.filters = {"relates" => {:operator => '!p', :values => ['2']}}
702 707 ids = find_issues_with_query(query).map(&:id).sort
703 708 assert_include 2, ids
704 709 assert_not_include 1, ids
705 710 assert_not_include 3, ids
706 711 end
707 712
708 713 def test_filter_on_relations_with_no_issues
709 714 IssueRelation.delete_all
710 715 IssueRelation.create!(:relation_type => "relates", :issue_from => Issue.find(1), :issue_to => Issue.find(2))
711 716 IssueRelation.create!(:relation_type => "relates", :issue_from => Issue.find(3), :issue_to => Issue.find(1))
712 717
713 718 query = IssueQuery.new(:name => '_')
714 719 query.filters = {"relates" => {:operator => '!*', :values => ['']}}
715 720 ids = find_issues_with_query(query).map(&:id)
716 721 assert_equal [], ids & [1, 2, 3]
717 722 assert_include 4, ids
718 723 end
719 724
720 725 def test_filter_on_relations_with_any_issues
721 726 IssueRelation.delete_all
722 727 IssueRelation.create!(:relation_type => "relates", :issue_from => Issue.find(1), :issue_to => Issue.find(2))
723 728 IssueRelation.create!(:relation_type => "relates", :issue_from => Issue.find(3), :issue_to => Issue.find(1))
724 729
725 730 query = IssueQuery.new(:name => '_')
726 731 query.filters = {"relates" => {:operator => '*', :values => ['']}}
727 732 assert_equal [1, 2, 3], find_issues_with_query(query).map(&:id).sort
728 733 end
729 734
730 735 def test_statement_should_be_nil_with_no_filters
731 736 q = IssueQuery.new(:name => '_')
732 737 q.filters = {}
733 738
734 739 assert q.valid?
735 740 assert_nil q.statement
736 741 end
737 742
738 743 def test_default_columns
739 744 q = IssueQuery.new
740 745 assert q.columns.any?
741 746 assert q.inline_columns.any?
742 747 assert q.block_columns.empty?
743 748 end
744 749
745 750 def test_set_column_names
746 751 q = IssueQuery.new
747 752 q.column_names = ['tracker', :subject, '', 'unknonw_column']
748 753 assert_equal [:tracker, :subject], q.columns.collect {|c| c.name}
749 754 c = q.columns.first
750 755 assert q.has_column?(c)
751 756 end
752 757
753 758 def test_inline_and_block_columns
754 759 q = IssueQuery.new
755 760 q.column_names = ['subject', 'description', 'tracker']
756 761
757 762 assert_equal [:subject, :tracker], q.inline_columns.map(&:name)
758 763 assert_equal [:description], q.block_columns.map(&:name)
759 764 end
760 765
761 766 def test_custom_field_columns_should_be_inline
762 767 q = IssueQuery.new
763 768 columns = q.available_columns.select {|column| column.is_a? QueryCustomFieldColumn}
764 769 assert columns.any?
765 770 assert_nil columns.detect {|column| !column.inline?}
766 771 end
767 772
768 773 def test_query_should_preload_spent_hours
769 774 q = IssueQuery.new(:name => '_', :column_names => [:subject, :spent_hours])
770 775 assert q.has_column?(:spent_hours)
771 776 issues = q.issues
772 777 assert_not_nil issues.first.instance_variable_get("@spent_hours")
773 778 end
774 779
775 780 def test_groupable_columns_should_include_custom_fields
776 781 q = IssueQuery.new
777 782 column = q.groupable_columns.detect {|c| c.name == :cf_1}
778 783 assert_not_nil column
779 784 assert_kind_of QueryCustomFieldColumn, column
780 785 end
781 786
782 787 def test_groupable_columns_should_not_include_multi_custom_fields
783 788 field = CustomField.find(1)
784 789 field.update_attribute :multiple, true
785 790
786 791 q = IssueQuery.new
787 792 column = q.groupable_columns.detect {|c| c.name == :cf_1}
788 793 assert_nil column
789 794 end
790 795
791 796 def test_groupable_columns_should_include_user_custom_fields
792 797 cf = IssueCustomField.create!(:name => 'User', :is_for_all => true, :tracker_ids => [1], :field_format => 'user')
793 798
794 799 q = IssueQuery.new
795 800 assert q.groupable_columns.detect {|c| c.name == "cf_#{cf.id}".to_sym}
796 801 end
797 802
798 803 def test_groupable_columns_should_include_version_custom_fields
799 804 cf = IssueCustomField.create!(:name => 'User', :is_for_all => true, :tracker_ids => [1], :field_format => 'version')
800 805
801 806 q = IssueQuery.new
802 807 assert q.groupable_columns.detect {|c| c.name == "cf_#{cf.id}".to_sym}
803 808 end
804 809
805 810 def test_grouped_with_valid_column
806 811 q = IssueQuery.new(:group_by => 'status')
807 812 assert q.grouped?
808 813 assert_not_nil q.group_by_column
809 814 assert_equal :status, q.group_by_column.name
810 815 assert_not_nil q.group_by_statement
811 816 assert_equal 'status', q.group_by_statement
812 817 end
813 818
814 819 def test_grouped_with_invalid_column
815 820 q = IssueQuery.new(:group_by => 'foo')
816 821 assert !q.grouped?
817 822 assert_nil q.group_by_column
818 823 assert_nil q.group_by_statement
819 824 end
820 825
821 826 def test_sortable_columns_should_sort_assignees_according_to_user_format_setting
822 827 with_settings :user_format => 'lastname_coma_firstname' do
823 828 q = IssueQuery.new
824 829 assert q.sortable_columns.has_key?('assigned_to')
825 830 assert_equal %w(users.lastname users.firstname users.id), q.sortable_columns['assigned_to']
826 831 end
827 832 end
828 833
829 834 def test_sortable_columns_should_sort_authors_according_to_user_format_setting
830 835 with_settings :user_format => 'lastname_coma_firstname' do
831 836 q = IssueQuery.new
832 837 assert q.sortable_columns.has_key?('author')
833 838 assert_equal %w(authors.lastname authors.firstname authors.id), q.sortable_columns['author']
834 839 end
835 840 end
836 841
837 842 def test_sortable_columns_should_include_custom_field
838 843 q = IssueQuery.new
839 844 assert q.sortable_columns['cf_1']
840 845 end
841 846
842 847 def test_sortable_columns_should_not_include_multi_custom_field
843 848 field = CustomField.find(1)
844 849 field.update_attribute :multiple, true
845 850
846 851 q = IssueQuery.new
847 852 assert !q.sortable_columns['cf_1']
848 853 end
849 854
850 855 def test_default_sort
851 856 q = IssueQuery.new
852 857 assert_equal [], q.sort_criteria
853 858 end
854 859
855 860 def test_set_sort_criteria_with_hash
856 861 q = IssueQuery.new
857 862 q.sort_criteria = {'0' => ['priority', 'desc'], '2' => ['tracker']}
858 863 assert_equal [['priority', 'desc'], ['tracker', 'asc']], q.sort_criteria
859 864 end
860 865
861 866 def test_set_sort_criteria_with_array
862 867 q = IssueQuery.new
863 868 q.sort_criteria = [['priority', 'desc'], 'tracker']
864 869 assert_equal [['priority', 'desc'], ['tracker', 'asc']], q.sort_criteria
865 870 end
866 871
867 872 def test_create_query_with_sort
868 873 q = IssueQuery.new(:name => 'Sorted')
869 874 q.sort_criteria = [['priority', 'desc'], 'tracker']
870 875 assert q.save
871 876 q.reload
872 877 assert_equal [['priority', 'desc'], ['tracker', 'asc']], q.sort_criteria
873 878 end
874 879
875 880 def test_sort_by_string_custom_field_asc
876 881 q = IssueQuery.new
877 882 c = q.available_columns.find {|col| col.is_a?(QueryCustomFieldColumn) && col.custom_field.field_format == 'string' }
878 883 assert c
879 884 assert c.sortable
880 885 issues = q.issues(:order => "#{c.sortable} ASC")
881 886 values = issues.collect {|i| i.custom_value_for(c.custom_field).to_s}
882 887 assert !values.empty?
883 888 assert_equal values.sort, values
884 889 end
885 890
886 891 def test_sort_by_string_custom_field_desc
887 892 q = IssueQuery.new
888 893 c = q.available_columns.find {|col| col.is_a?(QueryCustomFieldColumn) && col.custom_field.field_format == 'string' }
889 894 assert c
890 895 assert c.sortable
891 896 issues = q.issues(:order => "#{c.sortable} DESC")
892 897 values = issues.collect {|i| i.custom_value_for(c.custom_field).to_s}
893 898 assert !values.empty?
894 899 assert_equal values.sort.reverse, values
895 900 end
896 901
897 902 def test_sort_by_float_custom_field_asc
898 903 q = IssueQuery.new
899 904 c = q.available_columns.find {|col| col.is_a?(QueryCustomFieldColumn) && col.custom_field.field_format == 'float' }
900 905 assert c
901 906 assert c.sortable
902 907 issues = q.issues(:order => "#{c.sortable} ASC")
903 908 values = issues.collect {|i| begin; Kernel.Float(i.custom_value_for(c.custom_field).to_s); rescue; nil; end}.compact
904 909 assert !values.empty?
905 910 assert_equal values.sort, values
906 911 end
907 912
908 913 def test_invalid_query_should_raise_query_statement_invalid_error
909 914 q = IssueQuery.new
910 915 assert_raise Query::StatementInvalid do
911 916 q.issues(:conditions => "foo = 1")
912 917 end
913 918 end
914 919
915 920 def test_issue_count
916 921 q = IssueQuery.new(:name => '_')
917 922 issue_count = q.issue_count
918 923 assert_equal q.issues.size, issue_count
919 924 end
920 925
921 926 def test_issue_count_with_archived_issues
922 927 p = Project.generate! do |project|
923 928 project.status = Project::STATUS_ARCHIVED
924 929 end
925 930 i = Issue.generate!( :project => p, :tracker => p.trackers.first )
926 931 assert !i.visible?
927 932
928 933 test_issue_count
929 934 end
930 935
931 936 def test_issue_count_by_association_group
932 937 q = IssueQuery.new(:name => '_', :group_by => 'assigned_to')
933 938 count_by_group = q.issue_count_by_group
934 939 assert_kind_of Hash, count_by_group
935 940 assert_equal %w(NilClass User), count_by_group.keys.collect {|k| k.class.name}.uniq.sort
936 941 assert_equal %w(Fixnum), count_by_group.values.collect {|k| k.class.name}.uniq
937 942 assert count_by_group.has_key?(User.find(3))
938 943 end
939 944
940 945 def test_issue_count_by_list_custom_field_group
941 946 q = IssueQuery.new(:name => '_', :group_by => 'cf_1')
942 947 count_by_group = q.issue_count_by_group
943 948 assert_kind_of Hash, count_by_group
944 949 assert_equal %w(NilClass String), count_by_group.keys.collect {|k| k.class.name}.uniq.sort
945 950 assert_equal %w(Fixnum), count_by_group.values.collect {|k| k.class.name}.uniq
946 951 assert count_by_group.has_key?('MySQL')
947 952 end
948 953
949 954 def test_issue_count_by_date_custom_field_group
950 955 q = IssueQuery.new(:name => '_', :group_by => 'cf_8')
951 956 count_by_group = q.issue_count_by_group
952 957 assert_kind_of Hash, count_by_group
953 958 assert_equal %w(Date NilClass), count_by_group.keys.collect {|k| k.class.name}.uniq.sort
954 959 assert_equal %w(Fixnum), count_by_group.values.collect {|k| k.class.name}.uniq
955 960 end
956 961
957 962 def test_issue_count_with_nil_group_only
958 963 Issue.update_all("assigned_to_id = NULL")
959 964
960 965 q = IssueQuery.new(:name => '_', :group_by => 'assigned_to')
961 966 count_by_group = q.issue_count_by_group
962 967 assert_kind_of Hash, count_by_group
963 968 assert_equal 1, count_by_group.keys.size
964 969 assert_nil count_by_group.keys.first
965 970 end
966 971
967 972 def test_issue_ids
968 973 q = IssueQuery.new(:name => '_')
969 974 order = "issues.subject, issues.id"
970 975 issues = q.issues(:order => order)
971 976 assert_equal issues.map(&:id), q.issue_ids(:order => order)
972 977 end
973 978
974 979 def test_label_for
975 980 set_language_if_valid 'en'
976 981 q = IssueQuery.new
977 982 assert_equal 'Assignee', q.label_for('assigned_to_id')
978 983 end
979 984
980 985 def test_label_for_fr
981 986 set_language_if_valid 'fr'
982 987 q = IssueQuery.new
983 988 s = "Assign\xc3\xa9 \xc3\xa0"
984 989 s.force_encoding('UTF-8') if s.respond_to?(:force_encoding)
985 990 assert_equal s, q.label_for('assigned_to_id')
986 991 end
987 992
988 993 def test_editable_by
989 994 admin = User.find(1)
990 995 manager = User.find(2)
991 996 developer = User.find(3)
992 997
993 998 # Public query on project 1
994 999 q = IssueQuery.find(1)
995 1000 assert q.editable_by?(admin)
996 1001 assert q.editable_by?(manager)
997 1002 assert !q.editable_by?(developer)
998 1003
999 1004 # Private query on project 1
1000 1005 q = IssueQuery.find(2)
1001 1006 assert q.editable_by?(admin)
1002 1007 assert !q.editable_by?(manager)
1003 1008 assert q.editable_by?(developer)
1004 1009
1005 1010 # Private query for all projects
1006 1011 q = IssueQuery.find(3)
1007 1012 assert q.editable_by?(admin)
1008 1013 assert !q.editable_by?(manager)
1009 1014 assert q.editable_by?(developer)
1010 1015
1011 1016 # Public query for all projects
1012 1017 q = IssueQuery.find(4)
1013 1018 assert q.editable_by?(admin)
1014 1019 assert !q.editable_by?(manager)
1015 1020 assert !q.editable_by?(developer)
1016 1021 end
1017 1022
1018 1023 def test_visible_scope
1019 1024 query_ids = IssueQuery.visible(User.anonymous).map(&:id)
1020 1025
1021 1026 assert query_ids.include?(1), 'public query on public project was not visible'
1022 1027 assert query_ids.include?(4), 'public query for all projects was not visible'
1023 1028 assert !query_ids.include?(2), 'private query on public project was visible'
1024 1029 assert !query_ids.include?(3), 'private query for all projects was visible'
1025 1030 assert !query_ids.include?(7), 'public query on private project was visible'
1026 1031 end
1027 1032
1028 1033 test "#available_filters should include users of visible projects in cross-project view" do
1029 1034 users = IssueQuery.new.available_filters["assigned_to_id"]
1030 1035 assert_not_nil users
1031 1036 assert users[:values].map{|u|u[1]}.include?("3")
1032 1037 end
1033 1038
1034 1039 test "#available_filters should include users of subprojects" do
1035 1040 user1 = User.generate!
1036 1041 user2 = User.generate!
1037 1042 project = Project.find(1)
1038 1043 Member.create!(:principal => user1, :project => project.children.visible.first, :role_ids => [1])
1039 1044
1040 1045 users = IssueQuery.new(:project => project).available_filters["assigned_to_id"]
1041 1046 assert_not_nil users
1042 1047 assert users[:values].map{|u|u[1]}.include?(user1.id.to_s)
1043 1048 assert !users[:values].map{|u|u[1]}.include?(user2.id.to_s)
1044 1049 end
1045 1050
1046 1051 test "#available_filters should include visible projects in cross-project view" do
1047 1052 projects = IssueQuery.new.available_filters["project_id"]
1048 1053 assert_not_nil projects
1049 1054 assert projects[:values].map{|u|u[1]}.include?("1")
1050 1055 end
1051 1056
1052 1057 test "#available_filters should include 'member_of_group' filter" do
1053 1058 query = IssueQuery.new
1054 1059 assert query.available_filters.keys.include?("member_of_group")
1055 1060 assert_equal :list_optional, query.available_filters["member_of_group"][:type]
1056 1061 assert query.available_filters["member_of_group"][:values].present?
1057 1062 assert_equal Group.all.sort.map {|g| [g.name, g.id.to_s]},
1058 1063 query.available_filters["member_of_group"][:values].sort
1059 1064 end
1060 1065
1061 1066 test "#available_filters should include 'assigned_to_role' filter" do
1062 1067 query = IssueQuery.new
1063 1068 assert query.available_filters.keys.include?("assigned_to_role")
1064 1069 assert_equal :list_optional, query.available_filters["assigned_to_role"][:type]
1065 1070
1066 1071 assert query.available_filters["assigned_to_role"][:values].include?(['Manager','1'])
1067 1072 assert query.available_filters["assigned_to_role"][:values].include?(['Developer','2'])
1068 1073 assert query.available_filters["assigned_to_role"][:values].include?(['Reporter','3'])
1069 1074
1070 1075 assert ! query.available_filters["assigned_to_role"][:values].include?(['Non member','4'])
1071 1076 assert ! query.available_filters["assigned_to_role"][:values].include?(['Anonymous','5'])
1072 1077 end
1073 1078
1074 1079 context "#statement" do
1075 1080 context "with 'member_of_group' filter" do
1076 1081 setup do
1077 1082 Group.destroy_all # No fixtures
1078 1083 @user_in_group = User.generate!
1079 1084 @second_user_in_group = User.generate!
1080 1085 @user_in_group2 = User.generate!
1081 1086 @user_not_in_group = User.generate!
1082 1087
1083 1088 @group = Group.generate!.reload
1084 1089 @group.users << @user_in_group
1085 1090 @group.users << @second_user_in_group
1086 1091
1087 1092 @group2 = Group.generate!.reload
1088 1093 @group2.users << @user_in_group2
1089 1094
1090 1095 end
1091 1096
1092 1097 should "search assigned to for users in the group" do
1093 1098 @query = IssueQuery.new(:name => '_')
1094 1099 @query.add_filter('member_of_group', '=', [@group.id.to_s])
1095 1100
1096 1101 assert_query_statement_includes @query, "#{Issue.table_name}.assigned_to_id IN ('#{@user_in_group.id}','#{@second_user_in_group.id}','#{@group.id}')"
1097 1102 assert_find_issues_with_query_is_successful @query
1098 1103 end
1099 1104
1100 1105 should "search not assigned to any group member (none)" do
1101 1106 @query = IssueQuery.new(:name => '_')
1102 1107 @query.add_filter('member_of_group', '!*', [''])
1103 1108
1104 1109 # Users not in a group
1105 1110 assert_query_statement_includes @query, "#{Issue.table_name}.assigned_to_id IS NULL OR #{Issue.table_name}.assigned_to_id NOT IN ('#{@user_in_group.id}','#{@second_user_in_group.id}','#{@user_in_group2.id}','#{@group.id}','#{@group2.id}')"
1106 1111 assert_find_issues_with_query_is_successful @query
1107 1112 end
1108 1113
1109 1114 should "search assigned to any group member (all)" do
1110 1115 @query = IssueQuery.new(:name => '_')
1111 1116 @query.add_filter('member_of_group', '*', [''])
1112 1117
1113 1118 # Only users in a group
1114 1119 assert_query_statement_includes @query, "#{Issue.table_name}.assigned_to_id IN ('#{@user_in_group.id}','#{@second_user_in_group.id}','#{@user_in_group2.id}','#{@group.id}','#{@group2.id}')"
1115 1120 assert_find_issues_with_query_is_successful @query
1116 1121 end
1117 1122
1118 1123 should "return an empty set with = empty group" do
1119 1124 @empty_group = Group.generate!
1120 1125 @query = IssueQuery.new(:name => '_')
1121 1126 @query.add_filter('member_of_group', '=', [@empty_group.id.to_s])
1122 1127
1123 1128 assert_equal [], find_issues_with_query(@query)
1124 1129 end
1125 1130
1126 1131 should "return issues with ! empty group" do
1127 1132 @empty_group = Group.generate!
1128 1133 @query = IssueQuery.new(:name => '_')
1129 1134 @query.add_filter('member_of_group', '!', [@empty_group.id.to_s])
1130 1135
1131 1136 assert_find_issues_with_query_is_successful @query
1132 1137 end
1133 1138 end
1134 1139
1135 1140 context "with 'assigned_to_role' filter" do
1136 1141 setup do
1137 1142 @manager_role = Role.find_by_name('Manager')
1138 1143 @developer_role = Role.find_by_name('Developer')
1139 1144
1140 1145 @project = Project.generate!
1141 1146 @manager = User.generate!
1142 1147 @developer = User.generate!
1143 1148 @boss = User.generate!
1144 1149 @guest = User.generate!
1145 1150 User.add_to_project(@manager, @project, @manager_role)
1146 1151 User.add_to_project(@developer, @project, @developer_role)
1147 1152 User.add_to_project(@boss, @project, [@manager_role, @developer_role])
1148 1153
1149 1154 @issue1 = Issue.generate!(:project => @project, :assigned_to_id => @manager.id)
1150 1155 @issue2 = Issue.generate!(:project => @project, :assigned_to_id => @developer.id)
1151 1156 @issue3 = Issue.generate!(:project => @project, :assigned_to_id => @boss.id)
1152 1157 @issue4 = Issue.generate!(:project => @project, :assigned_to_id => @guest.id)
1153 1158 @issue5 = Issue.generate!(:project => @project)
1154 1159 end
1155 1160
1156 1161 should "search assigned to for users with the Role" do
1157 1162 @query = IssueQuery.new(:name => '_', :project => @project)
1158 1163 @query.add_filter('assigned_to_role', '=', [@manager_role.id.to_s])
1159 1164
1160 1165 assert_query_result [@issue1, @issue3], @query
1161 1166 end
1162 1167
1163 1168 should "search assigned to for users with the Role on the issue project" do
1164 1169 other_project = Project.generate!
1165 1170 User.add_to_project(@developer, other_project, @manager_role)
1166 1171
1167 1172 @query = IssueQuery.new(:name => '_', :project => @project)
1168 1173 @query.add_filter('assigned_to_role', '=', [@manager_role.id.to_s])
1169 1174
1170 1175 assert_query_result [@issue1, @issue3], @query
1171 1176 end
1172 1177
1173 1178 should "return an empty set with empty role" do
1174 1179 @empty_role = Role.generate!
1175 1180 @query = IssueQuery.new(:name => '_', :project => @project)
1176 1181 @query.add_filter('assigned_to_role', '=', [@empty_role.id.to_s])
1177 1182
1178 1183 assert_query_result [], @query
1179 1184 end
1180 1185
1181 1186 should "search assigned to for users without the Role" do
1182 1187 @query = IssueQuery.new(:name => '_', :project => @project)
1183 1188 @query.add_filter('assigned_to_role', '!', [@manager_role.id.to_s])
1184 1189
1185 1190 assert_query_result [@issue2, @issue4, @issue5], @query
1186 1191 end
1187 1192
1188 1193 should "search assigned to for users not assigned to any Role (none)" do
1189 1194 @query = IssueQuery.new(:name => '_', :project => @project)
1190 1195 @query.add_filter('assigned_to_role', '!*', [''])
1191 1196
1192 1197 assert_query_result [@issue4, @issue5], @query
1193 1198 end
1194 1199
1195 1200 should "search assigned to for users assigned to any Role (all)" do
1196 1201 @query = IssueQuery.new(:name => '_', :project => @project)
1197 1202 @query.add_filter('assigned_to_role', '*', [''])
1198 1203
1199 1204 assert_query_result [@issue1, @issue2, @issue3], @query
1200 1205 end
1201 1206
1202 1207 should "return issues with ! empty role" do
1203 1208 @empty_role = Role.generate!
1204 1209 @query = IssueQuery.new(:name => '_', :project => @project)
1205 1210 @query.add_filter('assigned_to_role', '!', [@empty_role.id.to_s])
1206 1211
1207 1212 assert_query_result [@issue1, @issue2, @issue3, @issue4, @issue5], @query
1208 1213 end
1209 1214 end
1210 1215 end
1211 1216 end
General Comments 0
You need to be logged in to leave comments. Login now