##// END OF EJS Templates
jro_apps » redmine
RSS
remove trailing white-spaces from app/models/query.rb....
Toshi MARUYAMA -
r6345:3605a1813705
parent child
Show More
Side by Side Unified
Context file:
r6345:3605a1813705 Loading diff...:
Show file before | Show file after | Hide comments
@@ -1,780 +1,780
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 principals = []
226 principals = []
227 if project
227 if project
228 principals += project.principals.sort
228 principals += project.principals.sort
229 else
229 else
230 all_projects = Project.visible.all
230 all_projects = Project.visible.all
231 if all_projects.any?
231 if all_projects.any?
232 # members of visible projects
232 # members of visible projects
233 principals += Principal.active.find(:all, :conditions => ["#{User.table_name}.id IN (SELECT DISTINCT user_id FROM members WHERE project_id IN (?))", all_projects.collect(&:id)]).sort
233 principals += Principal.active.find(:all, :conditions => ["#{User.table_name}.id IN (SELECT DISTINCT user_id FROM members WHERE project_id IN (?))", all_projects.collect(&:id)]).sort
234
234
235 # project filter
235 # project filter
236 project_values = []
236 project_values = []
237 Project.project_tree(all_projects) do |p, level|
237 Project.project_tree(all_projects) do |p, level|
238 prefix = (level > 0 ? ('--' * level + ' ') : '')
238 prefix = (level > 0 ? ('--' * level + ' ') : '')
239 project_values << ["#{prefix}#{p.name}", p.id.to_s]
239 project_values << ["#{prefix}#{p.name}", p.id.to_s]
240 end
240 end
241 @available_filters["project_id"] = { :type => :list, :order => 1, :values => project_values} unless project_values.empty?
241 @available_filters["project_id"] = { :type => :list, :order => 1, :values => project_values} unless project_values.empty?
242 end
242 end
243 end
243 end
244 users = principals.select {|p| p.is_a?(User)}
244 users = principals.select {|p| p.is_a?(User)}
245
245
246 assigned_to_values = []
246 assigned_to_values = []
247 assigned_to_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
247 assigned_to_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
248 assigned_to_values += (Setting.issue_group_assignment? ? principals : users).collect{|s| [s.name, s.id.to_s] }
248 assigned_to_values += (Setting.issue_group_assignment? ? principals : users).collect{|s| [s.name, s.id.to_s] }
249 @available_filters["assigned_to_id"] = { :type => :list_optional, :order => 4, :values => assigned_to_values } unless assigned_to_values.empty?
249 @available_filters["assigned_to_id"] = { :type => :list_optional, :order => 4, :values => assigned_to_values } unless assigned_to_values.empty?
250
250
251 author_values = []
251 author_values = []
252 author_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
252 author_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
253 author_values += users.collect{|s| [s.name, s.id.to_s] }
253 author_values += users.collect{|s| [s.name, s.id.to_s] }
254 @available_filters["author_id"] = { :type => :list, :order => 5, :values => author_values } unless author_values.empty?
254 @available_filters["author_id"] = { :type => :list, :order => 5, :values => author_values } unless author_values.empty?
255
255
256 group_values = Group.all.collect {|g| [g.name, g.id.to_s] }
256 group_values = Group.all.collect {|g| [g.name, g.id.to_s] }
257 @available_filters["member_of_group"] = { :type => :list_optional, :order => 6, :values => group_values } unless group_values.empty?
257 @available_filters["member_of_group"] = { :type => :list_optional, :order => 6, :values => group_values } unless group_values.empty?
258
258
259 role_values = Role.givable.collect {|r| [r.name, r.id.to_s] }
259 role_values = Role.givable.collect {|r| [r.name, r.id.to_s] }
260 @available_filters["assigned_to_role"] = { :type => :list_optional, :order => 7, :values => role_values } unless role_values.empty?
260 @available_filters["assigned_to_role"] = { :type => :list_optional, :order => 7, :values => role_values } unless role_values.empty?
261
261
262 if User.current.logged?
262 if User.current.logged?
263 @available_filters["watcher_id"] = { :type => :list, :order => 15, :values => [["<< #{l(:label_me)} >>", "me"]] }
263 @available_filters["watcher_id"] = { :type => :list, :order => 15, :values => [["<< #{l(:label_me)} >>", "me"]] }
264 end
264 end
265
265
266 if project
266 if project
267 # project specific filters
267 # project specific filters
268 categories = @project.issue_categories.all
268 categories = @project.issue_categories.all
269 unless categories.empty?
269 unless categories.empty?
270 @available_filters["category_id"] = { :type => :list_optional, :order => 6, :values => categories.collect{|s| [s.name, s.id.to_s] } }
270 @available_filters["category_id"] = { :type => :list_optional, :order => 6, :values => categories.collect{|s| [s.name, s.id.to_s] } }
271 end
271 end
272 versions = @project.shared_versions.all
272 versions = @project.shared_versions.all
273 unless versions.empty?
273 unless versions.empty?
274 @available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }
274 @available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }
275 end
275 end
276 unless @project.leaf?
276 unless @project.leaf?
277 subprojects = @project.descendants.visible.all
277 subprojects = @project.descendants.visible.all
278 unless subprojects.empty?
278 unless subprojects.empty?
279 @available_filters["subproject_id"] = { :type => :list_subprojects, :order => 13, :values => subprojects.collect{|s| [s.name, s.id.to_s] } }
279 @available_filters["subproject_id"] = { :type => :list_subprojects, :order => 13, :values => subprojects.collect{|s| [s.name, s.id.to_s] } }
280 end
280 end
281 end
281 end
282 add_custom_fields_filters(@project.all_issue_custom_fields)
282 add_custom_fields_filters(@project.all_issue_custom_fields)
283 else
283 else
284 # global filters for cross project issue list
284 # global filters for cross project issue list
285 system_shared_versions = Version.visible.find_all_by_sharing('system')
285 system_shared_versions = Version.visible.find_all_by_sharing('system')
286 unless system_shared_versions.empty?
286 unless system_shared_versions.empty?
287 @available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => system_shared_versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }
287 @available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => system_shared_versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }
288 end
288 end
289 add_custom_fields_filters(IssueCustomField.find(:all, :conditions => {:is_filter => true, :is_for_all => true}))
289 add_custom_fields_filters(IssueCustomField.find(:all, :conditions => {:is_filter => true, :is_for_all => true}))
290 end
290 end
291 @available_filters
291 @available_filters
292 end
292 end
293
293
294 def add_filter(field, operator, values)
294 def add_filter(field, operator, values)
295 # values must be an array
295 # values must be an array
296 return unless values.nil? || values.is_a?(Array)
296 return unless values.nil? || values.is_a?(Array)
297 # check if field is defined as an available filter
297 # check if field is defined as an available filter
298 if available_filters.has_key? field
298 if available_filters.has_key? field
299 filter_options = available_filters[field]
299 filter_options = available_filters[field]
300 # check if operator is allowed for that filter
300 # check if operator is allowed for that filter
301 #if @@operators_by_filter_type[filter_options[:type]].include? operator
301 #if @@operators_by_filter_type[filter_options[:type]].include? operator
302 # allowed_values = values & ([""] + (filter_options[:values] || []).collect {|val| val[1]})
302 # allowed_values = values & ([""] + (filter_options[:values] || []).collect {|val| val[1]})
303 # filters[field] = {:operator => operator, :values => allowed_values } if (allowed_values.first and !allowed_values.first.empty?) or ["o", "c", "!*", "*", "t"].include? operator
303 # filters[field] = {:operator => operator, :values => allowed_values } if (allowed_values.first and !allowed_values.first.empty?) or ["o", "c", "!*", "*", "t"].include? operator
304 #end
304 #end
305 filters[field] = {:operator => operator, :values => (values || [''])}
305 filters[field] = {:operator => operator, :values => (values || [''])}
306 end
306 end
307 end
307 end
308
308
309 def add_short_filter(field, expression)
309 def add_short_filter(field, expression)
310 return unless expression
310 return unless expression
311 parms = expression.scan(/^(o|c|!\*|!|\*)?(.*)$/).first
311 parms = expression.scan(/^(o|c|!\*|!|\*)?(.*)$/).first
312 add_filter field, (parms[0] || "="), [parms[1] || ""]
312 add_filter field, (parms[0] || "="), [parms[1] || ""]
313 end
313 end
314
314
315 # Add multiple filters using +add_filter+
315 # Add multiple filters using +add_filter+
316 def add_filters(fields, operators, values)
316 def add_filters(fields, operators, values)
317 if fields.is_a?(Array) && operators.is_a?(Hash) && (values.nil? || values.is_a?(Hash))
317 if fields.is_a?(Array) && operators.is_a?(Hash) && (values.nil? || values.is_a?(Hash))
318 fields.each do |field|
318 fields.each do |field|
319 add_filter(field, operators[field], values && values[field])
319 add_filter(field, operators[field], values && values[field])
320 end
320 end
321 end
321 end
322 end
322 end
323
323
324 def has_filter?(field)
324 def has_filter?(field)
325 filters and filters[field]
325 filters and filters[field]
326 end
326 end
327
327
328 def type_for(field)
328 def type_for(field)
329 available_filters[field][:type] if available_filters.has_key?(field)
329 available_filters[field][:type] if available_filters.has_key?(field)
330 end
330 end
331
331
332 def operator_for(field)
332 def operator_for(field)
333 has_filter?(field) ? filters[field][:operator] : nil
333 has_filter?(field) ? filters[field][:operator] : nil
334 end
334 end
335
335
336 def values_for(field)
336 def values_for(field)
337 has_filter?(field) ? filters[field][:values] : nil
337 has_filter?(field) ? filters[field][:values] : nil
338 end
338 end
339
339
340 def value_for(field, index=0)
340 def value_for(field, index=0)
341 (values_for(field) || [])[index]
341 (values_for(field) || [])[index]
342 end
342 end
343
343
344 def label_for(field)
344 def label_for(field)
345 label = available_filters[field][:name] if available_filters.has_key?(field)
345 label = available_filters[field][:name] if available_filters.has_key?(field)
346 label ||= field.gsub(/\_id$/, "")
346 label ||= field.gsub(/\_id$/, "")
347 end
347 end
348
348
349 def available_columns
349 def available_columns
350 return @available_columns if @available_columns
350 return @available_columns if @available_columns
351 @available_columns = Query.available_columns
351 @available_columns = Query.available_columns
352 @available_columns += (project ?
352 @available_columns += (project ?
353 project.all_issue_custom_fields :
353 project.all_issue_custom_fields :
354 IssueCustomField.find(:all)
354 IssueCustomField.find(:all)
355 ).collect {|cf| QueryCustomFieldColumn.new(cf) }
355 ).collect {|cf| QueryCustomFieldColumn.new(cf) }
356 end
356 end
357
357
358 def self.available_columns=(v)
358 def self.available_columns=(v)
359 self.available_columns = (v)
359 self.available_columns = (v)
360 end
360 end
361
361
362 def self.add_available_column(column)
362 def self.add_available_column(column)
363 self.available_columns << (column) if column.is_a?(QueryColumn)
363 self.available_columns << (column) if column.is_a?(QueryColumn)
364 end
364 end
365
365
366 # Returns an array of columns that can be used to group the results
366 # Returns an array of columns that can be used to group the results
367 def groupable_columns
367 def groupable_columns
368 available_columns.select {|c| c.groupable}
368 available_columns.select {|c| c.groupable}
369 end
369 end
370
370
371 # Returns a Hash of columns and the key for sorting
371 # Returns a Hash of columns and the key for sorting
372 def sortable_columns
372 def sortable_columns
373 {'id' => "#{Issue.table_name}.id"}.merge(available_columns.inject({}) {|h, column|
373 {'id' => "#{Issue.table_name}.id"}.merge(available_columns.inject({}) {|h, column|
374 h[column.name.to_s] = column.sortable
374 h[column.name.to_s] = column.sortable
375 h
375 h
376 })
376 })
377 end
377 end
378
378
379 def columns
379 def columns
380 if has_default_columns?
380 if has_default_columns?
381 available_columns.select do |c|
381 available_columns.select do |c|
382 # Adds the project column by default for cross-project lists
382 # Adds the project column by default for cross-project lists
383 Setting.issue_list_default_columns.include?(c.name.to_s) || (c.name == :project && project.nil?)
383 Setting.issue_list_default_columns.include?(c.name.to_s) || (c.name == :project && project.nil?)
384 end
384 end
385 else
385 else
386 # preserve the column_names order
386 # preserve the column_names order
387 column_names.collect {|name| available_columns.find {|col| col.name == name}}.compact
387 column_names.collect {|name| available_columns.find {|col| col.name == name}}.compact
388 end
388 end
389 end
389 end
390
390
391 def column_names=(names)
391 def column_names=(names)
392 if names
392 if names
393 names = names.select {|n| n.is_a?(Symbol) || !n.blank? }
393 names = names.select {|n| n.is_a?(Symbol) || !n.blank? }
394 names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }
394 names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }
395 # Set column_names to nil if default columns
395 # Set column_names to nil if default columns
396 if names.map(&:to_s) == Setting.issue_list_default_columns
396 if names.map(&:to_s) == Setting.issue_list_default_columns
397 names = nil
397 names = nil
398 end
398 end
399 end
399 end
400 write_attribute(:column_names, names)
400 write_attribute(:column_names, names)
401 end
401 end
402
402
403 def has_column?(column)
403 def has_column?(column)
404 column_names && column_names.include?(column.name)
404 column_names && column_names.include?(column.name)
405 end
405 end
406
406
407 def has_default_columns?
407 def has_default_columns?
408 column_names.nil? || column_names.empty?
408 column_names.nil? || column_names.empty?
409 end
409 end
410
410
411 def sort_criteria=(arg)
411 def sort_criteria=(arg)
412 c = []
412 c = []
413 if arg.is_a?(Hash)
413 if arg.is_a?(Hash)
414 arg = arg.keys.sort.collect {|k| arg[k]}
414 arg = arg.keys.sort.collect {|k| arg[k]}
415 end
415 end
416 c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, o == 'desc' ? o : 'asc']}
416 c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, o == 'desc' ? o : 'asc']}
417 write_attribute(:sort_criteria, c)
417 write_attribute(:sort_criteria, c)
418 end
418 end
419
419
420 def sort_criteria
420 def sort_criteria
421 read_attribute(:sort_criteria) || []
421 read_attribute(:sort_criteria) || []
422 end
422 end
423
423
424 def sort_criteria_key(arg)
424 def sort_criteria_key(arg)
425 sort_criteria && sort_criteria[arg] && sort_criteria[arg].first
425 sort_criteria && sort_criteria[arg] && sort_criteria[arg].first
426 end
426 end
427
427
428 def sort_criteria_order(arg)
428 def sort_criteria_order(arg)
429 sort_criteria && sort_criteria[arg] && sort_criteria[arg].last
429 sort_criteria && sort_criteria[arg] && sort_criteria[arg].last
430 end
430 end
431
431
432 # Returns the SQL sort order that should be prepended for grouping
432 # Returns the SQL sort order that should be prepended for grouping
433 def group_by_sort_order
433 def group_by_sort_order
434 if grouped? && (column = group_by_column)
434 if grouped? && (column = group_by_column)
435 column.sortable.is_a?(Array) ?
435 column.sortable.is_a?(Array) ?
436 column.sortable.collect {|s| "#{s} #{column.default_order}"}.join(',') :
436 column.sortable.collect {|s| "#{s} #{column.default_order}"}.join(',') :
437 "#{column.sortable} #{column.default_order}"
437 "#{column.sortable} #{column.default_order}"
438 end
438 end
439 end
439 end
440
440
441 # Returns true if the query is a grouped query
441 # Returns true if the query is a grouped query
442 def grouped?
442 def grouped?
443 !group_by_column.nil?
443 !group_by_column.nil?
444 end
444 end
445
445
446 def group_by_column
446 def group_by_column
447 groupable_columns.detect {|c| c.groupable && c.name.to_s == group_by}
447 groupable_columns.detect {|c| c.groupable && c.name.to_s == group_by}
448 end
448 end
449
449
450 def group_by_statement
450 def group_by_statement
451 group_by_column.try(:groupable)
451 group_by_column.try(:groupable)
452 end
452 end
453
453
454 def project_statement
454 def project_statement
455 project_clauses = []
455 project_clauses = []
456 if project && !@project.descendants.active.empty?
456 if project && !@project.descendants.active.empty?
457 ids = [project.id]
457 ids = [project.id]
458 if has_filter?("subproject_id")
458 if has_filter?("subproject_id")
459 case operator_for("subproject_id")
459 case operator_for("subproject_id")
460 when '='
460 when '='
461 # include the selected subprojects
461 # include the selected subprojects
462 ids += values_for("subproject_id").each(&:to_i)
462 ids += values_for("subproject_id").each(&:to_i)
463 when '!*'
463 when '!*'
464 # main project only
464 # main project only
465 else
465 else
466 # all subprojects
466 # all subprojects
467 ids += project.descendants.collect(&:id)
467 ids += project.descendants.collect(&:id)
468 end
468 end
469 elsif Setting.display_subprojects_issues?
469 elsif Setting.display_subprojects_issues?
470 ids += project.descendants.collect(&:id)
470 ids += project.descendants.collect(&:id)
471 end
471 end
472 project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
472 project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
473 elsif project
473 elsif project
474 project_clauses << "#{Project.table_name}.id = %d" % project.id
474 project_clauses << "#{Project.table_name}.id = %d" % project.id
475 end
475 end
476 project_clauses.any? ? project_clauses.join(' AND ') : nil
476 project_clauses.any? ? project_clauses.join(' AND ') : nil
477 end
477 end
478
478
479 def statement
479 def statement
480 # filters clauses
480 # filters clauses
481 filters_clauses = []
481 filters_clauses = []
482 filters.each_key do |field|
482 filters.each_key do |field|
483 next if field == "subproject_id"
483 next if field == "subproject_id"
484 v = values_for(field).clone
484 v = values_for(field).clone
485 next unless v and !v.empty?
485 next unless v and !v.empty?
486 operator = operator_for(field)
486 operator = operator_for(field)
487
487
488 # "me" value subsitution
488 # "me" value subsitution
489 if %w(assigned_to_id author_id watcher_id).include?(field)
489 if %w(assigned_to_id author_id watcher_id).include?(field)
490 if v.delete("me")
490 if v.delete("me")
491 if User.current.logged?
491 if User.current.logged?
492 v.push(User.current.id.to_s)
492 v.push(User.current.id.to_s)
493 v += User.current.group_ids.map(&:to_s) if field == 'assigned_to_id'
493 v += User.current.group_ids.map(&:to_s) if field == 'assigned_to_id'
494 else
494 else
495 v.push("0")
495 v.push("0")
496 end
496 end
497 end
497 end
498 end
498 end
499
499
500 if field =~ /^cf_(\d+)$/
500 if field =~ /^cf_(\d+)$/
501 # custom field
501 # custom field
502 filters_clauses << sql_for_custom_field(field, operator, v, $1)
502 filters_clauses << sql_for_custom_field(field, operator, v, $1)
503 elsif respond_to?("sql_for_#{field}_field")
503 elsif respond_to?("sql_for_#{field}_field")
504 # specific statement
504 # specific statement
505 filters_clauses << send("sql_for_#{field}_field", field, operator, v)
505 filters_clauses << send("sql_for_#{field}_field", field, operator, v)
506 else
506 else
507 # regular field
507 # regular field
508 filters_clauses << '(' + sql_for_field(field, operator, v, Issue.table_name, field) + ')'
508 filters_clauses << '(' + sql_for_field(field, operator, v, Issue.table_name, field) + ')'
509 end
509 end
510 end if filters and valid?
510 end if filters and valid?
511
511
512 filters_clauses << project_statement
512 filters_clauses << project_statement
513 filters_clauses.reject!(&:blank?)
513 filters_clauses.reject!(&:blank?)
514
514
515 filters_clauses.any? ? filters_clauses.join(' AND ') : nil
515 filters_clauses.any? ? filters_clauses.join(' AND ') : nil
516 end
516 end
517
517
518 # Returns the issue count
518 # Returns the issue count
519 def issue_count
519 def issue_count
520 Issue.visible.count(:include => [:status, :project], :conditions => statement)
520 Issue.visible.count(:include => [:status, :project], :conditions => statement)
521 rescue ::ActiveRecord::StatementInvalid => e
521 rescue ::ActiveRecord::StatementInvalid => e
522 raise StatementInvalid.new(e.message)
522 raise StatementInvalid.new(e.message)
523 end
523 end
524
524
525 # Returns the issue count by group or nil if query is not grouped
525 # Returns the issue count by group or nil if query is not grouped
526 def issue_count_by_group
526 def issue_count_by_group
527 r = nil
527 r = nil
528 if grouped?
528 if grouped?
529 begin
529 begin
530 # Rails will raise an (unexpected) RecordNotFound if there's only a nil group value
530 # Rails will raise an (unexpected) RecordNotFound if there's only a nil group value
531 r = Issue.visible.count(:group => group_by_statement, :include => [:status, :project], :conditions => statement)
531 r = Issue.visible.count(:group => group_by_statement, :include => [:status, :project], :conditions => statement)
532 rescue ActiveRecord::RecordNotFound
532 rescue ActiveRecord::RecordNotFound
533 r = {nil => issue_count}
533 r = {nil => issue_count}
534 end
534 end
535 c = group_by_column
535 c = group_by_column
536 if c.is_a?(QueryCustomFieldColumn)
536 if c.is_a?(QueryCustomFieldColumn)
537 r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
537 r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
538 end
538 end
539 end
539 end
540 r
540 r
541 rescue ::ActiveRecord::StatementInvalid => e
541 rescue ::ActiveRecord::StatementInvalid => e
542 raise StatementInvalid.new(e.message)
542 raise StatementInvalid.new(e.message)
543 end
543 end
544
544
545 # Returns the issues
545 # Returns the issues
546 # Valid options are :order, :offset, :limit, :include, :conditions
546 # Valid options are :order, :offset, :limit, :include, :conditions
547 def issues(options={})
547 def issues(options={})
548 order_option = [group_by_sort_order, options[:order]].reject {|s| s.blank?}.join(',')
548 order_option = [group_by_sort_order, options[:order]].reject {|s| s.blank?}.join(',')
549 order_option = nil if order_option.blank?
549 order_option = nil if order_option.blank?
550
550
551 Issue.visible.find :all, :include => ([:status, :project] + (options[:include] || [])).uniq,
551 Issue.visible.find :all, :include => ([:status, :project] + (options[:include] || [])).uniq,
552 :conditions => Query.merge_conditions(statement, options[:conditions]),
552 :conditions => Query.merge_conditions(statement, options[:conditions]),
553 :order => order_option,
553 :order => order_option,
554 :limit => options[:limit],
554 :limit => options[:limit],
555 :offset => options[:offset]
555 :offset => options[:offset]
556 rescue ::ActiveRecord::StatementInvalid => e
556 rescue ::ActiveRecord::StatementInvalid => e
557 raise StatementInvalid.new(e.message)
557 raise StatementInvalid.new(e.message)
558 end
558 end
559
559
560 # Returns the journals
560 # Returns the journals
561 # Valid options are :order, :offset, :limit
561 # Valid options are :order, :offset, :limit
562 def journals(options={})
562 def journals(options={})
563 Journal.visible.find :all, :include => [:details, :user, {:issue => [:project, :author, :tracker, :status]}],
563 Journal.visible.find :all, :include => [:details, :user, {:issue => [:project, :author, :tracker, :status]}],
564 :conditions => statement,
564 :conditions => statement,
565 :order => options[:order],
565 :order => options[:order],
566 :limit => options[:limit],
566 :limit => options[:limit],
567 :offset => options[:offset]
567 :offset => options[:offset]
568 rescue ::ActiveRecord::StatementInvalid => e
568 rescue ::ActiveRecord::StatementInvalid => e
569 raise StatementInvalid.new(e.message)
569 raise StatementInvalid.new(e.message)
570 end
570 end
571
571
572 # Returns the versions
572 # Returns the versions
573 # Valid options are :conditions
573 # Valid options are :conditions
574 def versions(options={})
574 def versions(options={})
575 Version.visible.find :all, :include => :project,
575 Version.visible.find :all, :include => :project,
576 :conditions => Query.merge_conditions(project_statement, options[:conditions])
576 :conditions => Query.merge_conditions(project_statement, options[:conditions])
577 rescue ::ActiveRecord::StatementInvalid => e
577 rescue ::ActiveRecord::StatementInvalid => e
578 raise StatementInvalid.new(e.message)
578 raise StatementInvalid.new(e.message)
579 end
579 end
580
580
581 def sql_for_watcher_id_field(field, operator, value)
581 def sql_for_watcher_id_field(field, operator, value)
582 db_table = Watcher.table_name
582 db_table = Watcher.table_name
583 "#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{db_table}.watchable_id FROM #{db_table} WHERE #{db_table}.watchable_type='Issue' AND " +
583 "#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{db_table}.watchable_id FROM #{db_table} WHERE #{db_table}.watchable_type='Issue' AND " +
584 sql_for_field(field, '=', value, db_table, 'user_id') + ')'
584 sql_for_field(field, '=', value, db_table, 'user_id') + ')'
585 end
585 end
586
586
587 def sql_for_member_of_group_field(field, operator, value)
587 def sql_for_member_of_group_field(field, operator, value)
588 if operator == '*' # Any group
588 if operator == '*' # Any group
589 groups = Group.all
589 groups = Group.all
590 operator = '=' # Override the operator since we want to find by assigned_to
590 operator = '=' # Override the operator since we want to find by assigned_to
591 elsif operator == "!*"
591 elsif operator == "!*"
592 groups = Group.all
592 groups = Group.all
593 operator = '!' # Override the operator since we want to find by assigned_to
593 operator = '!' # Override the operator since we want to find by assigned_to
594 else
594 else
595 groups = Group.find_all_by_id(value)
595 groups = Group.find_all_by_id(value)
596 end
596 end
597 groups ||= []
597 groups ||= []
598
598
599 members_of_groups = groups.inject([]) {|user_ids, group|
599 members_of_groups = groups.inject([]) {|user_ids, group|
600 if group && group.user_ids.present?
600 if group && group.user_ids.present?
601 user_ids << group.user_ids
601 user_ids << group.user_ids
602 end
602 end
603 user_ids.flatten.uniq.compact
603 user_ids.flatten.uniq.compact
604 }.sort.collect(&:to_s)
604 }.sort.collect(&:to_s)
605
605
606 '(' + sql_for_field("assigned_to_id", operator, members_of_groups, Issue.table_name, "assigned_to_id", false) + ')'
606 '(' + sql_for_field("assigned_to_id", operator, members_of_groups, Issue.table_name, "assigned_to_id", false) + ')'
607 end
607 end
608
608
609 def sql_for_assigned_to_role_field(field, operator, value)
609 def sql_for_assigned_to_role_field(field, operator, value)
610 if operator == "*" # Any Role
610 if operator == "*" # Any Role
611 roles = Role.givable
611 roles = Role.givable
612 operator = '=' # Override the operator since we want to find by assigned_to
612 operator = '=' # Override the operator since we want to find by assigned_to
613 elsif operator == "!*" # No role
613 elsif operator == "!*" # No role
614 roles = Role.givable
614 roles = Role.givable
615 operator = '!' # Override the operator since we want to find by assigned_to
615 operator = '!' # Override the operator since we want to find by assigned_to
616 else
616 else
617 roles = Role.givable.find_all_by_id(value)
617 roles = Role.givable.find_all_by_id(value)
618 end
618 end
619 roles ||= []
619 roles ||= []
620
620
621 members_of_roles = roles.inject([]) {|user_ids, role|
621 members_of_roles = roles.inject([]) {|user_ids, role|
622 if role && role.members
622 if role && role.members
623 user_ids << role.members.collect(&:user_id)
623 user_ids << role.members.collect(&:user_id)
624 end
624 end
625 user_ids.flatten.uniq.compact
625 user_ids.flatten.uniq.compact
626 }.sort.collect(&:to_s)
626 }.sort.collect(&:to_s)
627
627
628 '(' + sql_for_field("assigned_to_id", operator, members_of_roles, Issue.table_name, "assigned_to_id", false) + ')'
628 '(' + sql_for_field("assigned_to_id", operator, members_of_roles, Issue.table_name, "assigned_to_id", false) + ')'
629 end
629 end
630
630
631 private
631 private
632
632
633 def sql_for_custom_field(field, operator, value, custom_field_id)
633 def sql_for_custom_field(field, operator, value, custom_field_id)
634 db_table = CustomValue.table_name
634 db_table = CustomValue.table_name
635 db_field = 'value'
635 db_field = 'value'
636 "#{Issue.table_name}.id IN (SELECT #{Issue.table_name}.id FROM #{Issue.table_name} LEFT OUTER JOIN #{db_table} ON #{db_table}.customized_type='Issue' AND #{db_table}.customized_id=#{Issue.table_name}.id AND #{db_table}.custom_field_id=#{custom_field_id} WHERE " +
636 "#{Issue.table_name}.id IN (SELECT #{Issue.table_name}.id FROM #{Issue.table_name} LEFT OUTER JOIN #{db_table} ON #{db_table}.customized_type='Issue' AND #{db_table}.customized_id=#{Issue.table_name}.id AND #{db_table}.custom_field_id=#{custom_field_id} WHERE " +
637 sql_for_field(field, operator, value, db_table, db_field, true) + ')'
637 sql_for_field(field, operator, value, db_table, db_field, true) + ')'
638 end
638 end
639
639
640 # Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+
640 # Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+
641 def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)
641 def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)
642 sql = ''
642 sql = ''
643 case operator
643 case operator
644 when "="
644 when "="
645 if value.any?
645 if value.any?
646 case type_for(field)
646 case type_for(field)
647 when :date, :date_past
647 when :date, :date_past
648 sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), (Date.parse(value.first) rescue nil))
648 sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), (Date.parse(value.first) rescue nil))
649 when :integer
649 when :integer
650 sql = "#{db_table}.#{db_field} = #{value.first.to_i}"
650 sql = "#{db_table}.#{db_field} = #{value.first.to_i}"
651 when :float
651 when :float
652 sql = "#{db_table}.#{db_field} BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5}"
652 sql = "#{db_table}.#{db_field} BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5}"
653 else
653 else
654 sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")"
654 sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")"
655 end
655 end
656 else
656 else
657 # IN an empty set
657 # IN an empty set
658 sql = "1=0"
658 sql = "1=0"
659 end
659 end
660 when "!"
660 when "!"
661 if value.any?
661 if value.any?
662 sql = "(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + "))"
662 sql = "(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + "))"
663 else
663 else
664 # NOT IN an empty set
664 # NOT IN an empty set
665 sql = "1=1"
665 sql = "1=1"
666 end
666 end
667 when "!*"
667 when "!*"
668 sql = "#{db_table}.#{db_field} IS NULL"
668 sql = "#{db_table}.#{db_field} IS NULL"
669 sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter
669 sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter
670 when "*"
670 when "*"
671 sql = "#{db_table}.#{db_field} IS NOT NULL"
671 sql = "#{db_table}.#{db_field} IS NOT NULL"
672 sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
672 sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
673 when ">="
673 when ">="
674 if [:date, :date_past].include?(type_for(field))
674 if [:date, :date_past].include?(type_for(field))
675 sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), nil)
675 sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), nil)
676 else
676 else
677 if is_custom_filter
677 if is_custom_filter
678 sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) >= #{value.first.to_f}"
678 sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) >= #{value.first.to_f}"
679 else
679 else
680 sql = "#{db_table}.#{db_field} >= #{value.first.to_f}"
680 sql = "#{db_table}.#{db_field} >= #{value.first.to_f}"
681 end
681 end
682 end
682 end
683 when "<="
683 when "<="
684 if [:date, :date_past].include?(type_for(field))
684 if [:date, :date_past].include?(type_for(field))
685 sql = date_clause(db_table, db_field, nil, (Date.parse(value.first) rescue nil))
685 sql = date_clause(db_table, db_field, nil, (Date.parse(value.first) rescue nil))
686 else
686 else
687 if is_custom_filter
687 if is_custom_filter
688 sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) <= #{value.first.to_f}"
688 sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) <= #{value.first.to_f}"
689 else
689 else
690 sql = "#{db_table}.#{db_field} <= #{value.first.to_f}"
690 sql = "#{db_table}.#{db_field} <= #{value.first.to_f}"
691 end
691 end
692 end
692 end
693 when "><"
693 when "><"
694 if [:date, :date_past].include?(type_for(field))
694 if [:date, :date_past].include?(type_for(field))
695 sql = date_clause(db_table, db_field, (Date.parse(value[0]) rescue nil), (Date.parse(value[1]) rescue nil))
695 sql = date_clause(db_table, db_field, (Date.parse(value[0]) rescue nil), (Date.parse(value[1]) rescue nil))
696 else
696 else
697 if is_custom_filter
697 if is_custom_filter
698 sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
698 sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
699 else
699 else
700 sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
700 sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
701 end
701 end
702 end
702 end
703 when "o"
703 when "o"
704 sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_false}" if field == "status_id"
704 sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_false}" if field == "status_id"
705 when "c"
705 when "c"
706 sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_true}" if field == "status_id"
706 sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_true}" if field == "status_id"
707 when ">t-"
707 when ">t-"
708 sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0)
708 sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0)
709 when "<t-"
709 when "<t-"
710 sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i)
710 sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i)
711 when "t-"
711 when "t-"
712 sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i)
712 sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i)
713 when ">t+"
713 when ">t+"
714 sql = relative_date_clause(db_table, db_field, value.first.to_i, nil)
714 sql = relative_date_clause(db_table, db_field, value.first.to_i, nil)
715 when "<t+"
715 when "<t+"
716 sql = relative_date_clause(db_table, db_field, 0, value.first.to_i)
716 sql = relative_date_clause(db_table, db_field, 0, value.first.to_i)
717 when "t+"
717 when "t+"
718 sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i)
718 sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i)
719 when "t"
719 when "t"
720 sql = relative_date_clause(db_table, db_field, 0, 0)
720 sql = relative_date_clause(db_table, db_field, 0, 0)
721 when "w"
721 when "w"
722 first_day_of_week = l(:general_first_day_of_week).to_i
722 first_day_of_week = l(:general_first_day_of_week).to_i
723 day_of_week = Date.today.cwday
723 day_of_week = Date.today.cwday
724 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
724 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
725 sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6)
725 sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6)
726 when "~"
726 when "~"
727 sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
727 sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
728 when "!~"
728 when "!~"
729 sql = "LOWER(#{db_table}.#{db_field}) NOT LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
729 sql = "LOWER(#{db_table}.#{db_field}) NOT LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
730 else
730 else
731 raise "Unknown query operator #{operator}"
731 raise "Unknown query operator #{operator}"
732 end
732 end
733
733
734 return sql
734 return sql
735 end
735 end
736
736
737 def add_custom_fields_filters(custom_fields)
737 def add_custom_fields_filters(custom_fields)
738 @available_filters ||= {}
738 @available_filters ||= {}
739
739
740 custom_fields.select(&:is_filter?).each do |field|
740 custom_fields.select(&:is_filter?).each do |field|
741 case field.field_format
741 case field.field_format
742 when "text"
742 when "text"
743 options = { :type => :text, :order => 20 }
743 options = { :type => :text, :order => 20 }
744 when "list"
744 when "list"
745 options = { :type => :list_optional, :values => field.possible_values, :order => 20}
745 options = { :type => :list_optional, :values => field.possible_values, :order => 20}
746 when "date"
746 when "date"
747 options = { :type => :date, :order => 20 }
747 options = { :type => :date, :order => 20 }
748 when "bool"
748 when "bool"
749 options = { :type => :list, :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]], :order => 20 }
749 options = { :type => :list, :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]], :order => 20 }
750 when "int"
750 when "int"
751 options = { :type => :integer, :order => 20 }
751 options = { :type => :integer, :order => 20 }
752 when "float"
752 when "float"
753 options = { :type => :float, :order => 20 }
753 options = { :type => :float, :order => 20 }
754 when "user", "version"
754 when "user", "version"
755 next unless project
755 next unless project
756 options = { :type => :list_optional, :values => field.possible_values_options(project), :order => 20}
756 options = { :type => :list_optional, :values => field.possible_values_options(project), :order => 20}
757 else
757 else
758 options = { :type => :string, :order => 20 }
758 options = { :type => :string, :order => 20 }
759 end
759 end
760 @available_filters["cf_#{field.id}"] = options.merge({ :name => field.name })
760 @available_filters["cf_#{field.id}"] = options.merge({ :name => field.name })
761 end
761 end
762 end
762 end
763
763
764 # Returns a SQL clause for a date or datetime field.
764 # Returns a SQL clause for a date or datetime field.
765 def date_clause(table, field, from, to)
765 def date_clause(table, field, from, to)
766 s = []
766 s = []
767 if from
767 if from
768 s << ("#{table}.#{field} > '%s'" % [connection.quoted_date((from - 1).to_time.end_of_day)])
768 s << ("#{table}.#{field} > '%s'" % [connection.quoted_date((from - 1).to_time.end_of_day)])
769 end
769 end
770 if to
770 if to
771 s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date(to.to_time.end_of_day)])
771 s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date(to.to_time.end_of_day)])
772 end
772 end
773 s.join(' AND ')
773 s.join(' AND ')
774 end
774 end
775
775
776 # Returns a SQL clause for a date or datetime field using relative dates.
776 # Returns a SQL clause for a date or datetime field using relative dates.
777 def relative_date_clause(table, field, days_from, days_to)
777 def relative_date_clause(table, field, days_from, days_to)
778 date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil))
778 date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil))
779 end
779 end
780 end
780 end
General Comments 0
You need to be logged in to leave comments. Login now