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