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