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