##// END OF EJS Templates
jro_apps » redmine
RSS
replace tab to space at app/models/query.rb...
Toshi MARUYAMA -
r10261:95fe4d61c96a
parent child
Show More
Side by Side Unified
Context file:
r10261:95fe4d61c96a Loading diff...:
Show file before | Show file after | Hide comments
@@ -1,962 +1,962
1 # Redmine - project management software
1 # Redmine - project management software
2 # Copyright (C) 2006-2012 Jean-Philippe Lang
2 # Copyright (C) 2006-2012 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 sortable
42 def sortable
43 @sortable.is_a?(Proc) ? @sortable.call : @sortable
43 @sortable.is_a?(Proc) ? @sortable.call : @sortable
44 end
44 end
45
45
46 def value(issue)
46 def value(issue)
47 issue.send name
47 issue.send name
48 end
48 end
49
49
50 def css_classes
50 def css_classes
51 name
51 name
52 end
52 end
53 end
53 end
54
54
55 class QueryCustomFieldColumn < QueryColumn
55 class QueryCustomFieldColumn < QueryColumn
56
56
57 def initialize(custom_field)
57 def initialize(custom_field)
58 self.name = "cf_#{custom_field.id}".to_sym
58 self.name = "cf_#{custom_field.id}".to_sym
59 self.sortable = custom_field.order_statement || false
59 self.sortable = custom_field.order_statement || false
60 self.groupable = custom_field.group_statement || false
60 self.groupable = custom_field.group_statement || false
61 @cf = custom_field
61 @cf = custom_field
62 end
62 end
63
63
64 def caption
64 def caption
65 @cf.name
65 @cf.name
66 end
66 end
67
67
68 def custom_field
68 def custom_field
69 @cf
69 @cf
70 end
70 end
71
71
72 def value(issue)
72 def value(issue)
73 cv = issue.custom_values.select {|v| v.custom_field_id == @cf.id}.collect {|v| @cf.cast_value(v.value)}
73 cv = issue.custom_values.select {|v| v.custom_field_id == @cf.id}.collect {|v| @cf.cast_value(v.value)}
74 cv.size > 1 ? cv : cv.first
74 cv.size > 1 ? cv : cv.first
75 end
75 end
76
76
77 def css_classes
77 def css_classes
78 @css_classes ||= "#{name} #{@cf.field_format}"
78 @css_classes ||= "#{name} #{@cf.field_format}"
79 end
79 end
80 end
80 end
81
81
82 class Query < ActiveRecord::Base
82 class Query < ActiveRecord::Base
83 class StatementInvalid < ::ActiveRecord::StatementInvalid
83 class StatementInvalid < ::ActiveRecord::StatementInvalid
84 end
84 end
85
85
86 belongs_to :project
86 belongs_to :project
87 belongs_to :user
87 belongs_to :user
88 serialize :filters
88 serialize :filters
89 serialize :column_names
89 serialize :column_names
90 serialize :sort_criteria, Array
90 serialize :sort_criteria, Array
91
91
92 attr_protected :project_id, :user_id
92 attr_protected :project_id, :user_id
93
93
94 validates_presence_of :name
94 validates_presence_of :name
95 validates_length_of :name, :maximum => 255
95 validates_length_of :name, :maximum => 255
96 validate :validate_query_filters
96 validate :validate_query_filters
97
97
98 @@operators = { "=" => :label_equals,
98 @@operators = { "=" => :label_equals,
99 "!" => :label_not_equals,
99 "!" => :label_not_equals,
100 "o" => :label_open_issues,
100 "o" => :label_open_issues,
101 "c" => :label_closed_issues,
101 "c" => :label_closed_issues,
102 "!*" => :label_none,
102 "!*" => :label_none,
103 "*" => :label_all,
103 "*" => :label_all,
104 ">=" => :label_greater_or_equal,
104 ">=" => :label_greater_or_equal,
105 "<=" => :label_less_or_equal,
105 "<=" => :label_less_or_equal,
106 "><" => :label_between,
106 "><" => :label_between,
107 "<t+" => :label_in_less_than,
107 "<t+" => :label_in_less_than,
108 ">t+" => :label_in_more_than,
108 ">t+" => :label_in_more_than,
109 "t+" => :label_in,
109 "t+" => :label_in,
110 "t" => :label_today,
110 "t" => :label_today,
111 "w" => :label_this_week,
111 "w" => :label_this_week,
112 ">t-" => :label_less_than_ago,
112 ">t-" => :label_less_than_ago,
113 "<t-" => :label_more_than_ago,
113 "<t-" => :label_more_than_ago,
114 "t-" => :label_ago,
114 "t-" => :label_ago,
115 "~" => :label_contains,
115 "~" => :label_contains,
116 "!~" => :label_not_contains }
116 "!~" => :label_not_contains }
117
117
118 cattr_reader :operators
118 cattr_reader :operators
119
119
120 @@operators_by_filter_type = { :list => [ "=", "!" ],
120 @@operators_by_filter_type = { :list => [ "=", "!" ],
121 :list_status => [ "o", "=", "!", "c", "*" ],
121 :list_status => [ "o", "=", "!", "c", "*" ],
122 :list_optional => [ "=", "!", "!*", "*" ],
122 :list_optional => [ "=", "!", "!*", "*" ],
123 :list_subprojects => [ "*", "!*", "=" ],
123 :list_subprojects => [ "*", "!*", "=" ],
124 :date => [ "=", ">=", "<=", "><", "<t+", ">t+", "t+", "t", "w", ">t-", "<t-", "t-", "!*", "*" ],
124 :date => [ "=", ">=", "<=", "><", "<t+", ">t+", "t+", "t", "w", ">t-", "<t-", "t-", "!*", "*" ],
125 :date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "t-", "t", "w", "!*", "*" ],
125 :date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "t-", "t", "w", "!*", "*" ],
126 :string => [ "=", "~", "!", "!~", "!*", "*" ],
126 :string => [ "=", "~", "!", "!~", "!*", "*" ],
127 :text => [ "~", "!~", "!*", "*" ],
127 :text => [ "~", "!~", "!*", "*" ],
128 :integer => [ "=", ">=", "<=", "><", "!*", "*" ],
128 :integer => [ "=", ">=", "<=", "><", "!*", "*" ],
129 :float => [ "=", ">=", "<=", "><", "!*", "*" ] }
129 :float => [ "=", ">=", "<=", "><", "!*", "*" ] }
130
130
131 cattr_reader :operators_by_filter_type
131 cattr_reader :operators_by_filter_type
132
132
133 @@available_columns = [
133 @@available_columns = [
134 QueryColumn.new(:project, :sortable => "#{Project.table_name}.name", :groupable => true),
134 QueryColumn.new(:project, :sortable => "#{Project.table_name}.name", :groupable => true),
135 QueryColumn.new(:tracker, :sortable => "#{Tracker.table_name}.position", :groupable => true),
135 QueryColumn.new(:tracker, :sortable => "#{Tracker.table_name}.position", :groupable => true),
136 QueryColumn.new(:parent, :sortable => ["#{Issue.table_name}.root_id", "#{Issue.table_name}.lft ASC"], :default_order => 'desc', :caption => :field_parent_issue),
136 QueryColumn.new(:parent, :sortable => ["#{Issue.table_name}.root_id", "#{Issue.table_name}.lft ASC"], :default_order => 'desc', :caption => :field_parent_issue),
137 QueryColumn.new(:status, :sortable => "#{IssueStatus.table_name}.position", :groupable => true),
137 QueryColumn.new(:status, :sortable => "#{IssueStatus.table_name}.position", :groupable => true),
138 QueryColumn.new(:priority, :sortable => "#{IssuePriority.table_name}.position", :default_order => 'desc', :groupable => true),
138 QueryColumn.new(:priority, :sortable => "#{IssuePriority.table_name}.position", :default_order => 'desc', :groupable => true),
139 QueryColumn.new(:subject, :sortable => "#{Issue.table_name}.subject"),
139 QueryColumn.new(:subject, :sortable => "#{Issue.table_name}.subject"),
140 QueryColumn.new(:author, :sortable => lambda {User.fields_for_order_statement("authors")}, :groupable => true),
140 QueryColumn.new(:author, :sortable => lambda {User.fields_for_order_statement("authors")}, :groupable => true),
141 QueryColumn.new(:assigned_to, :sortable => lambda {User.fields_for_order_statement}, :groupable => true),
141 QueryColumn.new(:assigned_to, :sortable => lambda {User.fields_for_order_statement}, :groupable => true),
142 QueryColumn.new(:updated_on, :sortable => "#{Issue.table_name}.updated_on", :default_order => 'desc'),
142 QueryColumn.new(:updated_on, :sortable => "#{Issue.table_name}.updated_on", :default_order => 'desc'),
143 QueryColumn.new(:category, :sortable => "#{IssueCategory.table_name}.name", :groupable => true),
143 QueryColumn.new(:category, :sortable => "#{IssueCategory.table_name}.name", :groupable => true),
144 QueryColumn.new(:fixed_version, :sortable => lambda {Version.fields_for_order_statement}, :groupable => true),
144 QueryColumn.new(:fixed_version, :sortable => lambda {Version.fields_for_order_statement}, :groupable => true),
145 QueryColumn.new(:start_date, :sortable => "#{Issue.table_name}.start_date"),
145 QueryColumn.new(:start_date, :sortable => "#{Issue.table_name}.start_date"),
146 QueryColumn.new(:due_date, :sortable => "#{Issue.table_name}.due_date"),
146 QueryColumn.new(:due_date, :sortable => "#{Issue.table_name}.due_date"),
147 QueryColumn.new(:estimated_hours, :sortable => "#{Issue.table_name}.estimated_hours"),
147 QueryColumn.new(:estimated_hours, :sortable => "#{Issue.table_name}.estimated_hours"),
148 QueryColumn.new(:done_ratio, :sortable => "#{Issue.table_name}.done_ratio", :groupable => true),
148 QueryColumn.new(:done_ratio, :sortable => "#{Issue.table_name}.done_ratio", :groupable => true),
149 QueryColumn.new(:created_on, :sortable => "#{Issue.table_name}.created_on", :default_order => 'desc'),
149 QueryColumn.new(:created_on, :sortable => "#{Issue.table_name}.created_on", :default_order => 'desc'),
150 ]
150 ]
151 cattr_reader :available_columns
151 cattr_reader :available_columns
152
152
153 scope :visible, lambda {|*args|
153 scope :visible, lambda {|*args|
154 user = args.shift || User.current
154 user = args.shift || User.current
155 base = Project.allowed_to_condition(user, :view_issues, *args)
155 base = Project.allowed_to_condition(user, :view_issues, *args)
156 user_id = user.logged? ? user.id : 0
156 user_id = user.logged? ? user.id : 0
157 {
157 {
158 :conditions => ["(#{table_name}.project_id IS NULL OR (#{base})) AND (#{table_name}.is_public = ? OR #{table_name}.user_id = ?)", true, user_id],
158 :conditions => ["(#{table_name}.project_id IS NULL OR (#{base})) AND (#{table_name}.is_public = ? OR #{table_name}.user_id = ?)", true, user_id],
159 :include => :project
159 :include => :project
160 }
160 }
161 }
161 }
162
162
163 def initialize(attributes=nil, *args)
163 def initialize(attributes=nil, *args)
164 super attributes
164 super attributes
165 self.filters ||= { 'status_id' => {:operator => "o", :values => [""]} }
165 self.filters ||= { 'status_id' => {:operator => "o", :values => [""]} }
166 @is_for_all = project.nil?
166 @is_for_all = project.nil?
167 end
167 end
168
168
169 def validate_query_filters
169 def validate_query_filters
170 filters.each_key do |field|
170 filters.each_key do |field|
171 if values_for(field)
171 if values_for(field)
172 case type_for(field)
172 case type_for(field)
173 when :integer
173 when :integer
174 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^[+-]?\d+$/) }
174 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^[+-]?\d+$/) }
175 when :float
175 when :float
176 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^[+-]?\d+(\.\d*)?$/) }
176 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^[+-]?\d+(\.\d*)?$/) }
177 when :date, :date_past
177 when :date, :date_past
178 case operator_for(field)
178 case operator_for(field)
179 when "=", ">=", "<=", "><"
179 when "=", ">=", "<=", "><"
180 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && (!v.match(/^\d{4}-\d{2}-\d{2}$/) || (Date.parse(v) rescue nil).nil?) }
180 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && (!v.match(/^\d{4}-\d{2}-\d{2}$/) || (Date.parse(v) rescue nil).nil?) }
181 when ">t-", "<t-", "t-"
181 when ">t-", "<t-", "t-"
182 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^\d+$/) }
182 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^\d+$/) }
183 end
183 end
184 end
184 end
185 end
185 end
186
186
187 add_filter_error(field, :blank) unless
187 add_filter_error(field, :blank) unless
188 # filter requires one or more values
188 # filter requires one or more values
189 (values_for(field) and !values_for(field).first.blank?) or
189 (values_for(field) and !values_for(field).first.blank?) or
190 # filter doesn't require any value
190 # filter doesn't require any value
191 ["o", "c", "!*", "*", "t", "w"].include? operator_for(field)
191 ["o", "c", "!*", "*", "t", "w"].include? operator_for(field)
192 end if filters
192 end if filters
193 end
193 end
194
194
195 def add_filter_error(field, message)
195 def add_filter_error(field, message)
196 m = label_for(field) + " " + l(message, :scope => 'activerecord.errors.messages')
196 m = label_for(field) + " " + l(message, :scope => 'activerecord.errors.messages')
197 errors.add(:base, m)
197 errors.add(:base, m)
198 end
198 end
199
199
200 # Returns true if the query is visible to +user+ or the current user.
200 # Returns true if the query is visible to +user+ or the current user.
201 def visible?(user=User.current)
201 def visible?(user=User.current)
202 (project.nil? || user.allowed_to?(:view_issues, project)) && (self.is_public? || self.user_id == user.id)
202 (project.nil? || user.allowed_to?(:view_issues, project)) && (self.is_public? || self.user_id == user.id)
203 end
203 end
204
204
205 def editable_by?(user)
205 def editable_by?(user)
206 return false unless user
206 return false unless user
207 # Admin can edit them all and regular users can edit their private queries
207 # Admin can edit them all and regular users can edit their private queries
208 return true if user.admin? || (!is_public && self.user_id == user.id)
208 return true if user.admin? || (!is_public && self.user_id == user.id)
209 # Members can not edit public queries that are for all project (only admin is allowed to)
209 # Members can not edit public queries that are for all project (only admin is allowed to)
210 is_public && !@is_for_all && user.allowed_to?(:manage_public_queries, project)
210 is_public && !@is_for_all && user.allowed_to?(:manage_public_queries, project)
211 end
211 end
212
212
213 def trackers
213 def trackers
214 @trackers ||= project.nil? ? Tracker.find(:all, :order => 'position') : project.rolled_up_trackers
214 @trackers ||= project.nil? ? Tracker.find(:all, :order => 'position') : project.rolled_up_trackers
215 end
215 end
216
216
217 # Returns a hash of localized labels for all filter operators
217 # Returns a hash of localized labels for all filter operators
218 def self.operators_labels
218 def self.operators_labels
219 operators.inject({}) {|h, operator| h[operator.first] = l(operator.last); h}
219 operators.inject({}) {|h, operator| h[operator.first] = l(operator.last); h}
220 end
220 end
221
221
222 def available_filters
222 def available_filters
223 return @available_filters if @available_filters
223 return @available_filters if @available_filters
224
224
225 @available_filters = { "status_id" => { :type => :list_status, :order => 1, :values => IssueStatus.find(:all, :order => 'position').collect{|s| [s.name, s.id.to_s] } },
225 @available_filters = { "status_id" => { :type => :list_status, :order => 1, :values => IssueStatus.find(:all, :order => 'position').collect{|s| [s.name, s.id.to_s] } },
226 "tracker_id" => { :type => :list, :order => 2, :values => trackers.collect{|s| [s.name, s.id.to_s] } },
226 "tracker_id" => { :type => :list, :order => 2, :values => trackers.collect{|s| [s.name, s.id.to_s] } },
227 "priority_id" => { :type => :list, :order => 3, :values => IssuePriority.all.collect{|s| [s.name, s.id.to_s] } },
227 "priority_id" => { :type => :list, :order => 3, :values => IssuePriority.all.collect{|s| [s.name, s.id.to_s] } },
228 "subject" => { :type => :text, :order => 8 },
228 "subject" => { :type => :text, :order => 8 },
229 "created_on" => { :type => :date_past, :order => 9 },
229 "created_on" => { :type => :date_past, :order => 9 },
230 "updated_on" => { :type => :date_past, :order => 10 },
230 "updated_on" => { :type => :date_past, :order => 10 },
231 "start_date" => { :type => :date, :order => 11 },
231 "start_date" => { :type => :date, :order => 11 },
232 "due_date" => { :type => :date, :order => 12 },
232 "due_date" => { :type => :date, :order => 12 },
233 "estimated_hours" => { :type => :float, :order => 13 },
233 "estimated_hours" => { :type => :float, :order => 13 },
234 "done_ratio" => { :type => :integer, :order => 14 }}
234 "done_ratio" => { :type => :integer, :order => 14 }}
235
235
236 principals = []
236 principals = []
237 if project
237 if project
238 principals += project.principals.sort
238 principals += project.principals.sort
239 unless project.leaf?
239 unless project.leaf?
240 subprojects = project.descendants.visible.all
240 subprojects = project.descendants.visible.all
241 if subprojects.any?
241 if subprojects.any?
242 @available_filters["subproject_id"] = { :type => :list_subprojects, :order => 13, :values => subprojects.collect{|s| [s.name, s.id.to_s] } }
242 @available_filters["subproject_id"] = { :type => :list_subprojects, :order => 13, :values => subprojects.collect{|s| [s.name, s.id.to_s] } }
243 principals += Principal.member_of(subprojects)
243 principals += Principal.member_of(subprojects)
244 end
244 end
245 end
245 end
246 else
246 else
247 all_projects = Project.visible.all
247 all_projects = Project.visible.all
248 if all_projects.any?
248 if all_projects.any?
249 # members of visible projects
249 # members of visible projects
250 principals += Principal.member_of(all_projects)
250 principals += Principal.member_of(all_projects)
251
251
252 # project filter
252 # project filter
253 project_values = []
253 project_values = []
254 if User.current.logged? && User.current.memberships.any?
254 if User.current.logged? && User.current.memberships.any?
255 project_values << ["<< #{l(:label_my_projects).downcase} >>", "mine"]
255 project_values << ["<< #{l(:label_my_projects).downcase} >>", "mine"]
256 end
256 end
257 Project.project_tree(all_projects) do |p, level|
257 Project.project_tree(all_projects) do |p, level|
258 prefix = (level > 0 ? ('--' * level + ' ') : '')
258 prefix = (level > 0 ? ('--' * level + ' ') : '')
259 project_values << ["#{prefix}#{p.name}", p.id.to_s]
259 project_values << ["#{prefix}#{p.name}", p.id.to_s]
260 end
260 end
261 @available_filters["project_id"] = { :type => :list, :order => 1, :values => project_values} unless project_values.empty?
261 @available_filters["project_id"] = { :type => :list, :order => 1, :values => project_values} unless project_values.empty?
262 end
262 end
263 end
263 end
264 principals.uniq!
264 principals.uniq!
265 principals.sort!
265 principals.sort!
266 users = principals.select {|p| p.is_a?(User)}
266 users = principals.select {|p| p.is_a?(User)}
267
267
268 assigned_to_values = []
268 assigned_to_values = []
269 assigned_to_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
269 assigned_to_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
270 assigned_to_values += (Setting.issue_group_assignment? ? principals : users).collect{|s| [s.name, s.id.to_s] }
270 assigned_to_values += (Setting.issue_group_assignment? ? principals : users).collect{|s| [s.name, s.id.to_s] }
271 @available_filters["assigned_to_id"] = { :type => :list_optional, :order => 4, :values => assigned_to_values } unless assigned_to_values.empty?
271 @available_filters["assigned_to_id"] = { :type => :list_optional, :order => 4, :values => assigned_to_values } unless assigned_to_values.empty?
272
272
273 author_values = []
273 author_values = []
274 author_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
274 author_values << ["<< #{l(:label_me)} >>", "me"] if User.current.logged?
275 author_values += users.collect{|s| [s.name, s.id.to_s] }
275 author_values += users.collect{|s| [s.name, s.id.to_s] }
276 @available_filters["author_id"] = { :type => :list, :order => 5, :values => author_values } unless author_values.empty?
276 @available_filters["author_id"] = { :type => :list, :order => 5, :values => author_values } unless author_values.empty?
277
277
278 group_values = Group.all.collect {|g| [g.name, g.id.to_s] }
278 group_values = Group.all.collect {|g| [g.name, g.id.to_s] }
279 @available_filters["member_of_group"] = { :type => :list_optional, :order => 6, :values => group_values } unless group_values.empty?
279 @available_filters["member_of_group"] = { :type => :list_optional, :order => 6, :values => group_values } unless group_values.empty?
280
280
281 role_values = Role.givable.collect {|r| [r.name, r.id.to_s] }
281 role_values = Role.givable.collect {|r| [r.name, r.id.to_s] }
282 @available_filters["assigned_to_role"] = { :type => :list_optional, :order => 7, :values => role_values } unless role_values.empty?
282 @available_filters["assigned_to_role"] = { :type => :list_optional, :order => 7, :values => role_values } unless role_values.empty?
283
283
284 if User.current.logged?
284 if User.current.logged?
285 @available_filters["watcher_id"] = { :type => :list, :order => 15, :values => [["<< #{l(:label_me)} >>", "me"]] }
285 @available_filters["watcher_id"] = { :type => :list, :order => 15, :values => [["<< #{l(:label_me)} >>", "me"]] }
286 end
286 end
287
287
288 if project
288 if project
289 # project specific filters
289 # project specific filters
290 categories = project.issue_categories.all
290 categories = project.issue_categories.all
291 unless categories.empty?
291 unless categories.empty?
292 @available_filters["category_id"] = { :type => :list_optional, :order => 6, :values => categories.collect{|s| [s.name, s.id.to_s] } }
292 @available_filters["category_id"] = { :type => :list_optional, :order => 6, :values => categories.collect{|s| [s.name, s.id.to_s] } }
293 end
293 end
294 versions = project.shared_versions.all
294 versions = project.shared_versions.all
295 unless versions.empty?
295 unless versions.empty?
296 @available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }
296 @available_filters["fixed_version_id"] = { :type => :list_optional, :order => 7, :values => versions.sort.collect{|s| ["#{s.project.name} - #{s.name}", s.id.to_s] } }
297 end
297 end
298 add_custom_fields_filters(project.all_issue_custom_fields)
298 add_custom_fields_filters(project.all_issue_custom_fields)
299 else
299 else
300 # global filters for cross project issue list
300 # global filters for cross project issue list
301 system_shared_versions = Version.visible.find_all_by_sharing('system')
301 system_shared_versions = Version.visible.find_all_by_sharing('system')
302 unless system_shared_versions.empty?
302 unless system_shared_versions.empty?
303 @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] } }
303 @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] } }
304 end
304 end
305 add_custom_fields_filters(IssueCustomField.find(:all, :conditions => {:is_filter => true, :is_for_all => true}))
305 add_custom_fields_filters(IssueCustomField.find(:all, :conditions => {:is_filter => true, :is_for_all => true}))
306 end
306 end
307
307
308 add_associations_custom_fields_filters :project, :author, :assigned_to, :fixed_version
308 add_associations_custom_fields_filters :project, :author, :assigned_to, :fixed_version
309
309
310 if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||
310 if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||
311 User.current.allowed_to?(:set_own_issues_private, nil, :global => true)
311 User.current.allowed_to?(:set_own_issues_private, nil, :global => true)
312 @available_filters["is_private"] = { :type => :list, :order => 15, :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]] }
312 @available_filters["is_private"] = { :type => :list, :order => 15, :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]] }
313 end
313 end
314
314
315 Tracker.disabled_core_fields(trackers).each {|field|
315 Tracker.disabled_core_fields(trackers).each {|field|
316 @available_filters.delete field
316 @available_filters.delete field
317 }
317 }
318
318
319 @available_filters.each do |field, options|
319 @available_filters.each do |field, options|
320 options[:name] ||= l("field_#{field}".gsub(/_id$/, ''))
320 options[:name] ||= l("field_#{field}".gsub(/_id$/, ''))
321 end
321 end
322
322
323 @available_filters
323 @available_filters
324 end
324 end
325
325
326 # Returns a representation of the available filters for JSON serialization
326 # Returns a representation of the available filters for JSON serialization
327 def available_filters_as_json
327 def available_filters_as_json
328 json = {}
328 json = {}
329 available_filters.each do |field, options|
329 available_filters.each do |field, options|
330 json[field] = options.slice(:type, :name, :values).stringify_keys
330 json[field] = options.slice(:type, :name, :values).stringify_keys
331 end
331 end
332 json
332 json
333 end
333 end
334
334
335 def add_filter(field, operator, values)
335 def add_filter(field, operator, values)
336 # values must be an array
336 # values must be an array
337 return unless values.nil? || values.is_a?(Array)
337 return unless values.nil? || values.is_a?(Array)
338 # check if field is defined as an available filter
338 # check if field is defined as an available filter
339 if available_filters.has_key? field
339 if available_filters.has_key? field
340 filter_options = available_filters[field]
340 filter_options = available_filters[field]
341 # check if operator is allowed for that filter
341 # check if operator is allowed for that filter
342 #if @@operators_by_filter_type[filter_options[:type]].include? operator
342 #if @@operators_by_filter_type[filter_options[:type]].include? operator
343 # allowed_values = values & ([""] + (filter_options[:values] || []).collect {|val| val[1]})
343 # allowed_values = values & ([""] + (filter_options[:values] || []).collect {|val| val[1]})
344 # filters[field] = {:operator => operator, :values => allowed_values } if (allowed_values.first and !allowed_values.first.empty?) or ["o", "c", "!*", "*", "t"].include? operator
344 # filters[field] = {:operator => operator, :values => allowed_values } if (allowed_values.first and !allowed_values.first.empty?) or ["o", "c", "!*", "*", "t"].include? operator
345 #end
345 #end
346 filters[field] = {:operator => operator, :values => (values || [''])}
346 filters[field] = {:operator => operator, :values => (values || [''])}
347 end
347 end
348 end
348 end
349
349
350 def add_short_filter(field, expression)
350 def add_short_filter(field, expression)
351 return unless expression && available_filters.has_key?(field)
351 return unless expression && available_filters.has_key?(field)
352 field_type = available_filters[field][:type]
352 field_type = available_filters[field][:type]
353 @@operators_by_filter_type[field_type].sort.reverse.detect do |operator|
353 @@operators_by_filter_type[field_type].sort.reverse.detect do |operator|
354 next unless expression =~ /^#{Regexp.escape(operator)}(.*)$/
354 next unless expression =~ /^#{Regexp.escape(operator)}(.*)$/
355 add_filter field, operator, $1.present? ? $1.split('|') : ['']
355 add_filter field, operator, $1.present? ? $1.split('|') : ['']
356 end || add_filter(field, '=', expression.split('|'))
356 end || add_filter(field, '=', expression.split('|'))
357 end
357 end
358
358
359 # Add multiple filters using +add_filter+
359 # Add multiple filters using +add_filter+
360 def add_filters(fields, operators, values)
360 def add_filters(fields, operators, values)
361 if fields.is_a?(Array) && operators.is_a?(Hash) && (values.nil? || values.is_a?(Hash))
361 if fields.is_a?(Array) && operators.is_a?(Hash) && (values.nil? || values.is_a?(Hash))
362 fields.each do |field|
362 fields.each do |field|
363 add_filter(field, operators[field], values && values[field])
363 add_filter(field, operators[field], values && values[field])
364 end
364 end
365 end
365 end
366 end
366 end
367
367
368 def has_filter?(field)
368 def has_filter?(field)
369 filters and filters[field]
369 filters and filters[field]
370 end
370 end
371
371
372 def type_for(field)
372 def type_for(field)
373 available_filters[field][:type] if available_filters.has_key?(field)
373 available_filters[field][:type] if available_filters.has_key?(field)
374 end
374 end
375
375
376 def operator_for(field)
376 def operator_for(field)
377 has_filter?(field) ? filters[field][:operator] : nil
377 has_filter?(field) ? filters[field][:operator] : nil
378 end
378 end
379
379
380 def values_for(field)
380 def values_for(field)
381 has_filter?(field) ? filters[field][:values] : nil
381 has_filter?(field) ? filters[field][:values] : nil
382 end
382 end
383
383
384 def value_for(field, index=0)
384 def value_for(field, index=0)
385 (values_for(field) || [])[index]
385 (values_for(field) || [])[index]
386 end
386 end
387
387
388 def label_for(field)
388 def label_for(field)
389 label = available_filters[field][:name] if available_filters.has_key?(field)
389 label = available_filters[field][:name] if available_filters.has_key?(field)
390 label ||= l("field_#{field.to_s.gsub(/_id$/, '')}", :default => field)
390 label ||= l("field_#{field.to_s.gsub(/_id$/, '')}", :default => field)
391 end
391 end
392
392
393 def available_columns
393 def available_columns
394 return @available_columns if @available_columns
394 return @available_columns if @available_columns
395 @available_columns = ::Query.available_columns.dup
395 @available_columns = ::Query.available_columns.dup
396 @available_columns += (project ?
396 @available_columns += (project ?
397 project.all_issue_custom_fields :
397 project.all_issue_custom_fields :
398 IssueCustomField.find(:all)
398 IssueCustomField.find(:all)
399 ).collect {|cf| QueryCustomFieldColumn.new(cf) }
399 ).collect {|cf| QueryCustomFieldColumn.new(cf) }
400
400
401 if User.current.allowed_to?(:view_time_entries, project, :global => true)
401 if User.current.allowed_to?(:view_time_entries, project, :global => true)
402 index = nil
402 index = nil
403 @available_columns.each_with_index {|column, i| index = i if column.name == :estimated_hours}
403 @available_columns.each_with_index {|column, i| index = i if column.name == :estimated_hours}
404 index = (index ? index + 1 : -1)
404 index = (index ? index + 1 : -1)
405 # insert the column after estimated_hours or at the end
405 # insert the column after estimated_hours or at the end
406 @available_columns.insert index, QueryColumn.new(:spent_hours,
406 @available_columns.insert index, QueryColumn.new(:spent_hours,
407 :sortable => "(SELECT COALESCE(SUM(hours), 0) FROM #{TimeEntry.table_name} WHERE #{TimeEntry.table_name}.issue_id = #{Issue.table_name}.id)",
407 :sortable => "(SELECT COALESCE(SUM(hours), 0) FROM #{TimeEntry.table_name} WHERE #{TimeEntry.table_name}.issue_id = #{Issue.table_name}.id)",
408 :default_order => 'desc',
408 :default_order => 'desc',
409 :caption => :label_spent_time
409 :caption => :label_spent_time
410 )
410 )
411 end
411 end
412
412
413 if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||
413 if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||
414 User.current.allowed_to?(:set_own_issues_private, nil, :global => true)
414 User.current.allowed_to?(:set_own_issues_private, nil, :global => true)
415 @available_columns << QueryColumn.new(:is_private, :sortable => "#{Issue.table_name}.is_private")
415 @available_columns << QueryColumn.new(:is_private, :sortable => "#{Issue.table_name}.is_private")
416 end
416 end
417
417
418 disabled_fields = Tracker.disabled_core_fields(trackers).map {|field| field.sub(/_id$/, '')}
418 disabled_fields = Tracker.disabled_core_fields(trackers).map {|field| field.sub(/_id$/, '')}
419 @available_columns.reject! {|column|
419 @available_columns.reject! {|column|
420 disabled_fields.include?(column.name.to_s)
420 disabled_fields.include?(column.name.to_s)
421 }
421 }
422
422
423 @available_columns
423 @available_columns
424 end
424 end
425
425
426 def self.available_columns=(v)
426 def self.available_columns=(v)
427 self.available_columns = (v)
427 self.available_columns = (v)
428 end
428 end
429
429
430 def self.add_available_column(column)
430 def self.add_available_column(column)
431 self.available_columns << (column) if column.is_a?(QueryColumn)
431 self.available_columns << (column) if column.is_a?(QueryColumn)
432 end
432 end
433
433
434 # Returns an array of columns that can be used to group the results
434 # Returns an array of columns that can be used to group the results
435 def groupable_columns
435 def groupable_columns
436 available_columns.select {|c| c.groupable}
436 available_columns.select {|c| c.groupable}
437 end
437 end
438
438
439 # Returns a Hash of columns and the key for sorting
439 # Returns a Hash of columns and the key for sorting
440 def sortable_columns
440 def sortable_columns
441 {'id' => "#{Issue.table_name}.id"}.merge(available_columns.inject({}) {|h, column|
441 {'id' => "#{Issue.table_name}.id"}.merge(available_columns.inject({}) {|h, column|
442 h[column.name.to_s] = column.sortable
442 h[column.name.to_s] = column.sortable
443 h
443 h
444 })
444 })
445 end
445 end
446
446
447 def columns
447 def columns
448 # preserve the column_names order
448 # preserve the column_names order
449 (has_default_columns? ? default_columns_names : column_names).collect do |name|
449 (has_default_columns? ? default_columns_names : column_names).collect do |name|
450 available_columns.find { |col| col.name == name }
450 available_columns.find { |col| col.name == name }
451 end.compact
451 end.compact
452 end
452 end
453
453
454 def default_columns_names
454 def default_columns_names
455 @default_columns_names ||= begin
455 @default_columns_names ||= begin
456 default_columns = Setting.issue_list_default_columns.map(&:to_sym)
456 default_columns = Setting.issue_list_default_columns.map(&:to_sym)
457
457
458 project.present? ? default_columns : [:project] | default_columns
458 project.present? ? default_columns : [:project] | default_columns
459 end
459 end
460 end
460 end
461
461
462 def column_names=(names)
462 def column_names=(names)
463 if names
463 if names
464 names = names.select {|n| n.is_a?(Symbol) || !n.blank? }
464 names = names.select {|n| n.is_a?(Symbol) || !n.blank? }
465 names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }
465 names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }
466 # Set column_names to nil if default columns
466 # Set column_names to nil if default columns
467 if names == default_columns_names
467 if names == default_columns_names
468 names = nil
468 names = nil
469 end
469 end
470 end
470 end
471 write_attribute(:column_names, names)
471 write_attribute(:column_names, names)
472 end
472 end
473
473
474 def has_column?(column)
474 def has_column?(column)
475 column_names && column_names.include?(column.is_a?(QueryColumn) ? column.name : column)
475 column_names && column_names.include?(column.is_a?(QueryColumn) ? column.name : column)
476 end
476 end
477
477
478 def has_default_columns?
478 def has_default_columns?
479 column_names.nil? || column_names.empty?
479 column_names.nil? || column_names.empty?
480 end
480 end
481
481
482 def sort_criteria=(arg)
482 def sort_criteria=(arg)
483 c = []
483 c = []
484 if arg.is_a?(Hash)
484 if arg.is_a?(Hash)
485 arg = arg.keys.sort.collect {|k| arg[k]}
485 arg = arg.keys.sort.collect {|k| arg[k]}
486 end
486 end
487 c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, o == 'desc' ? o : 'asc']}
487 c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, o == 'desc' ? o : 'asc']}
488 write_attribute(:sort_criteria, c)
488 write_attribute(:sort_criteria, c)
489 end
489 end
490
490
491 def sort_criteria
491 def sort_criteria
492 read_attribute(:sort_criteria) || []
492 read_attribute(:sort_criteria) || []
493 end
493 end
494
494
495 def sort_criteria_key(arg)
495 def sort_criteria_key(arg)
496 sort_criteria && sort_criteria[arg] && sort_criteria[arg].first
496 sort_criteria && sort_criteria[arg] && sort_criteria[arg].first
497 end
497 end
498
498
499 def sort_criteria_order(arg)
499 def sort_criteria_order(arg)
500 sort_criteria && sort_criteria[arg] && sort_criteria[arg].last
500 sort_criteria && sort_criteria[arg] && sort_criteria[arg].last
501 end
501 end
502
502
503 # Returns the SQL sort order that should be prepended for grouping
503 # Returns the SQL sort order that should be prepended for grouping
504 def group_by_sort_order
504 def group_by_sort_order
505 if grouped? && (column = group_by_column)
505 if grouped? && (column = group_by_column)
506 column.sortable.is_a?(Array) ?
506 column.sortable.is_a?(Array) ?
507 column.sortable.collect {|s| "#{s} #{column.default_order}"}.join(',') :
507 column.sortable.collect {|s| "#{s} #{column.default_order}"}.join(',') :
508 "#{column.sortable} #{column.default_order}"
508 "#{column.sortable} #{column.default_order}"
509 end
509 end
510 end
510 end
511
511
512 # Returns true if the query is a grouped query
512 # Returns true if the query is a grouped query
513 def grouped?
513 def grouped?
514 !group_by_column.nil?
514 !group_by_column.nil?
515 end
515 end
516
516
517 def group_by_column
517 def group_by_column
518 groupable_columns.detect {|c| c.groupable && c.name.to_s == group_by}
518 groupable_columns.detect {|c| c.groupable && c.name.to_s == group_by}
519 end
519 end
520
520
521 def group_by_statement
521 def group_by_statement
522 group_by_column.try(:groupable)
522 group_by_column.try(:groupable)
523 end
523 end
524
524
525 def project_statement
525 def project_statement
526 project_clauses = []
526 project_clauses = []
527 if project && !project.descendants.active.empty?
527 if project && !project.descendants.active.empty?
528 ids = [project.id]
528 ids = [project.id]
529 if has_filter?("subproject_id")
529 if has_filter?("subproject_id")
530 case operator_for("subproject_id")
530 case operator_for("subproject_id")
531 when '='
531 when '='
532 # include the selected subprojects
532 # include the selected subprojects
533 ids += values_for("subproject_id").each(&:to_i)
533 ids += values_for("subproject_id").each(&:to_i)
534 when '!*'
534 when '!*'
535 # main project only
535 # main project only
536 else
536 else
537 # all subprojects
537 # all subprojects
538 ids += project.descendants.collect(&:id)
538 ids += project.descendants.collect(&:id)
539 end
539 end
540 elsif Setting.display_subprojects_issues?
540 elsif Setting.display_subprojects_issues?
541 ids += project.descendants.collect(&:id)
541 ids += project.descendants.collect(&:id)
542 end
542 end
543 project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
543 project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
544 elsif project
544 elsif project
545 project_clauses << "#{Project.table_name}.id = %d" % project.id
545 project_clauses << "#{Project.table_name}.id = %d" % project.id
546 end
546 end
547 project_clauses.any? ? project_clauses.join(' AND ') : nil
547 project_clauses.any? ? project_clauses.join(' AND ') : nil
548 end
548 end
549
549
550 def statement
550 def statement
551 # filters clauses
551 # filters clauses
552 filters_clauses = []
552 filters_clauses = []
553 filters.each_key do |field|
553 filters.each_key do |field|
554 next if field == "subproject_id"
554 next if field == "subproject_id"
555 v = values_for(field).clone
555 v = values_for(field).clone
556 next unless v and !v.empty?
556 next unless v and !v.empty?
557 operator = operator_for(field)
557 operator = operator_for(field)
558
558
559 # "me" value subsitution
559 # "me" value subsitution
560 if %w(assigned_to_id author_id watcher_id).include?(field)
560 if %w(assigned_to_id author_id watcher_id).include?(field)
561 if v.delete("me")
561 if v.delete("me")
562 if User.current.logged?
562 if User.current.logged?
563 v.push(User.current.id.to_s)
563 v.push(User.current.id.to_s)
564 v += User.current.group_ids.map(&:to_s) if field == 'assigned_to_id'
564 v += User.current.group_ids.map(&:to_s) if field == 'assigned_to_id'
565 else
565 else
566 v.push("0")
566 v.push("0")
567 end
567 end
568 end
568 end
569 end
569 end
570
570
571 if field == 'project_id'
571 if field == 'project_id'
572 if v.delete('mine')
572 if v.delete('mine')
573 v += User.current.memberships.map(&:project_id).map(&:to_s)
573 v += User.current.memberships.map(&:project_id).map(&:to_s)
574 end
574 end
575 end
575 end
576
576
577 if field =~ /cf_(\d+)$/
577 if field =~ /cf_(\d+)$/
578 # custom field
578 # custom field
579 filters_clauses << sql_for_custom_field(field, operator, v, $1)
579 filters_clauses << sql_for_custom_field(field, operator, v, $1)
580 elsif respond_to?("sql_for_#{field}_field")
580 elsif respond_to?("sql_for_#{field}_field")
581 # specific statement
581 # specific statement
582 filters_clauses << send("sql_for_#{field}_field", field, operator, v)
582 filters_clauses << send("sql_for_#{field}_field", field, operator, v)
583 else
583 else
584 # regular field
584 # regular field
585 filters_clauses << '(' + sql_for_field(field, operator, v, Issue.table_name, field) + ')'
585 filters_clauses << '(' + sql_for_field(field, operator, v, Issue.table_name, field) + ')'
586 end
586 end
587 end if filters and valid?
587 end if filters and valid?
588
588
589 filters_clauses << project_statement
589 filters_clauses << project_statement
590 filters_clauses.reject!(&:blank?)
590 filters_clauses.reject!(&:blank?)
591
591
592 filters_clauses.any? ? filters_clauses.join(' AND ') : nil
592 filters_clauses.any? ? filters_clauses.join(' AND ') : nil
593 end
593 end
594
594
595 # Returns the issue count
595 # Returns the issue count
596 def issue_count
596 def issue_count
597 Issue.visible.count(:include => [:status, :project], :conditions => statement)
597 Issue.visible.count(:include => [:status, :project], :conditions => statement)
598 rescue ::ActiveRecord::StatementInvalid => e
598 rescue ::ActiveRecord::StatementInvalid => e
599 raise StatementInvalid.new(e.message)
599 raise StatementInvalid.new(e.message)
600 end
600 end
601
601
602 # Returns the issue count by group or nil if query is not grouped
602 # Returns the issue count by group or nil if query is not grouped
603 def issue_count_by_group
603 def issue_count_by_group
604 r = nil
604 r = nil
605 if grouped?
605 if grouped?
606 begin
606 begin
607 # Rails3 will raise an (unexpected) RecordNotFound if there's only a nil group value
607 # Rails3 will raise an (unexpected) RecordNotFound if there's only a nil group value
608 r = Issue.visible.count(:group => group_by_statement, :include => [:status, :project], :conditions => statement)
608 r = Issue.visible.count(:group => group_by_statement, :include => [:status, :project], :conditions => statement)
609 rescue ActiveRecord::RecordNotFound
609 rescue ActiveRecord::RecordNotFound
610 r = {nil => issue_count}
610 r = {nil => issue_count}
611 end
611 end
612 c = group_by_column
612 c = group_by_column
613 if c.is_a?(QueryCustomFieldColumn)
613 if c.is_a?(QueryCustomFieldColumn)
614 r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
614 r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
615 end
615 end
616 end
616 end
617 r
617 r
618 rescue ::ActiveRecord::StatementInvalid => e
618 rescue ::ActiveRecord::StatementInvalid => e
619 raise StatementInvalid.new(e.message)
619 raise StatementInvalid.new(e.message)
620 end
620 end
621
621
622 # Returns the issues
622 # Returns the issues
623 # Valid options are :order, :offset, :limit, :include, :conditions
623 # Valid options are :order, :offset, :limit, :include, :conditions
624 def issues(options={})
624 def issues(options={})
625 order_option = [group_by_sort_order, options[:order]].reject {|s| s.blank?}.join(',')
625 order_option = [group_by_sort_order, options[:order]].reject {|s| s.blank?}.join(',')
626 order_option = nil if order_option.blank?
626 order_option = nil if order_option.blank?
627
627
628 issues = Issue.visible.scoped(:conditions => options[:conditions]).find :all, :include => ([:status, :project] + (options[:include] || [])).uniq,
628 issues = Issue.visible.scoped(:conditions => options[:conditions]).find :all, :include => ([:status, :project] + (options[:include] || [])).uniq,
629 :conditions => statement,
629 :conditions => statement,
630 :order => order_option,
630 :order => order_option,
631 :joins => joins_for_order_statement(order_option),
631 :joins => joins_for_order_statement(order_option),
632 :limit => options[:limit],
632 :limit => options[:limit],
633 :offset => options[:offset]
633 :offset => options[:offset]
634
634
635 if has_column?(:spent_hours)
635 if has_column?(:spent_hours)
636 Issue.load_visible_spent_hours(issues)
636 Issue.load_visible_spent_hours(issues)
637 end
637 end
638 issues
638 issues
639 rescue ::ActiveRecord::StatementInvalid => e
639 rescue ::ActiveRecord::StatementInvalid => e
640 raise StatementInvalid.new(e.message)
640 raise StatementInvalid.new(e.message)
641 end
641 end
642
642
643 # Returns the issues ids
643 # Returns the issues ids
644 def issue_ids(options={})
644 def issue_ids(options={})
645 order_option = [group_by_sort_order, options[:order]].reject {|s| s.blank?}.join(',')
645 order_option = [group_by_sort_order, options[:order]].reject {|s| s.blank?}.join(',')
646 order_option = nil if order_option.blank?
646 order_option = nil if order_option.blank?
647
647
648 Issue.visible.scoped(:conditions => options[:conditions]).scoped(:include => ([:status, :project] + (options[:include] || [])).uniq,
648 Issue.visible.scoped(:conditions => options[:conditions]).scoped(:include => ([:status, :project] + (options[:include] || [])).uniq,
649 :conditions => statement,
649 :conditions => statement,
650 :order => order_option,
650 :order => order_option,
651 :joins => joins_for_order_statement(order_option),
651 :joins => joins_for_order_statement(order_option),
652 :limit => options[:limit],
652 :limit => options[:limit],
653 :offset => options[:offset]).find_ids
653 :offset => options[:offset]).find_ids
654 rescue ::ActiveRecord::StatementInvalid => e
654 rescue ::ActiveRecord::StatementInvalid => e
655 raise StatementInvalid.new(e.message)
655 raise StatementInvalid.new(e.message)
656 end
656 end
657
657
658 # Returns the journals
658 # Returns the journals
659 # Valid options are :order, :offset, :limit
659 # Valid options are :order, :offset, :limit
660 def journals(options={})
660 def journals(options={})
661 Journal.visible.find :all, :include => [:details, :user, {:issue => [:project, :author, :tracker, :status]}],
661 Journal.visible.find :all, :include => [:details, :user, {:issue => [:project, :author, :tracker, :status]}],
662 :conditions => statement,
662 :conditions => statement,
663 :order => options[:order],
663 :order => options[:order],
664 :limit => options[:limit],
664 :limit => options[:limit],
665 :offset => options[:offset]
665 :offset => options[:offset]
666 rescue ::ActiveRecord::StatementInvalid => e
666 rescue ::ActiveRecord::StatementInvalid => e
667 raise StatementInvalid.new(e.message)
667 raise StatementInvalid.new(e.message)
668 end
668 end
669
669
670 # Returns the versions
670 # Returns the versions
671 # Valid options are :conditions
671 # Valid options are :conditions
672 def versions(options={})
672 def versions(options={})
673 Version.visible.scoped(:conditions => options[:conditions]).find :all, :include => :project, :conditions => project_statement
673 Version.visible.scoped(:conditions => options[:conditions]).find :all, :include => :project, :conditions => project_statement
674 rescue ::ActiveRecord::StatementInvalid => e
674 rescue ::ActiveRecord::StatementInvalid => e
675 raise StatementInvalid.new(e.message)
675 raise StatementInvalid.new(e.message)
676 end
676 end
677
677
678 def sql_for_watcher_id_field(field, operator, value)
678 def sql_for_watcher_id_field(field, operator, value)
679 db_table = Watcher.table_name
679 db_table = Watcher.table_name
680 "#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{db_table}.watchable_id FROM #{db_table} WHERE #{db_table}.watchable_type='Issue' AND " +
680 "#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{db_table}.watchable_id FROM #{db_table} WHERE #{db_table}.watchable_type='Issue' AND " +
681 sql_for_field(field, '=', value, db_table, 'user_id') + ')'
681 sql_for_field(field, '=', value, db_table, 'user_id') + ')'
682 end
682 end
683
683
684 def sql_for_member_of_group_field(field, operator, value)
684 def sql_for_member_of_group_field(field, operator, value)
685 if operator == '*' # Any group
685 if operator == '*' # Any group
686 groups = Group.all
686 groups = Group.all
687 operator = '=' # Override the operator since we want to find by assigned_to
687 operator = '=' # Override the operator since we want to find by assigned_to
688 elsif operator == "!*"
688 elsif operator == "!*"
689 groups = Group.all
689 groups = Group.all
690 operator = '!' # Override the operator since we want to find by assigned_to
690 operator = '!' # Override the operator since we want to find by assigned_to
691 else
691 else
692 groups = Group.find_all_by_id(value)
692 groups = Group.find_all_by_id(value)
693 end
693 end
694 groups ||= []
694 groups ||= []
695
695
696 members_of_groups = groups.inject([]) {|user_ids, group|
696 members_of_groups = groups.inject([]) {|user_ids, group|
697 if group && group.user_ids.present?
697 if group && group.user_ids.present?
698 user_ids << group.user_ids
698 user_ids << group.user_ids
699 end
699 end
700 user_ids.flatten.uniq.compact
700 user_ids.flatten.uniq.compact
701 }.sort.collect(&:to_s)
701 }.sort.collect(&:to_s)
702
702
703 '(' + sql_for_field("assigned_to_id", operator, members_of_groups, Issue.table_name, "assigned_to_id", false) + ')'
703 '(' + sql_for_field("assigned_to_id", operator, members_of_groups, Issue.table_name, "assigned_to_id", false) + ')'
704 end
704 end
705
705
706 def sql_for_assigned_to_role_field(field, operator, value)
706 def sql_for_assigned_to_role_field(field, operator, value)
707 case operator
707 case operator
708 when "*", "!*" # Member / Not member
708 when "*", "!*" # Member / Not member
709 sw = operator == "!*" ? 'NOT' : ''
709 sw = operator == "!*" ? 'NOT' : ''
710 nl = operator == "!*" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''
710 nl = operator == "!*" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''
711 "(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}" +
711 "(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}" +
712 " WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id))"
712 " WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id))"
713 when "=", "!"
713 when "=", "!"
714 role_cond = value.any? ?
714 role_cond = value.any? ?
715 "#{MemberRole.table_name}.role_id IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")" :
715 "#{MemberRole.table_name}.role_id IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")" :
716 "1=0"
716 "1=0"
717
717
718 sw = operator == "!" ? 'NOT' : ''
718 sw = operator == "!" ? 'NOT' : ''
719 nl = operator == "!" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''
719 nl = operator == "!" ? "#{Issue.table_name}.assigned_to_id IS NULL OR" : ''
720 "(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}, #{MemberRole.table_name}" +
720 "(#{nl} #{Issue.table_name}.assigned_to_id #{sw} IN (SELECT DISTINCT #{Member.table_name}.user_id FROM #{Member.table_name}, #{MemberRole.table_name}" +
721 " WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id AND #{Member.table_name}.id = #{MemberRole.table_name}.member_id AND #{role_cond}))"
721 " WHERE #{Member.table_name}.project_id = #{Issue.table_name}.project_id AND #{Member.table_name}.id = #{MemberRole.table_name}.member_id AND #{role_cond}))"
722 end
722 end
723 end
723 end
724
724
725 def sql_for_is_private_field(field, operator, value)
725 def sql_for_is_private_field(field, operator, value)
726 op = (operator == "=" ? 'IN' : 'NOT IN')
726 op = (operator == "=" ? 'IN' : 'NOT IN')
727 va = value.map {|v| v == '0' ? connection.quoted_false : connection.quoted_true}.uniq.join(',')
727 va = value.map {|v| v == '0' ? connection.quoted_false : connection.quoted_true}.uniq.join(',')
728
728
729 "#{Issue.table_name}.is_private #{op} (#{va})"
729 "#{Issue.table_name}.is_private #{op} (#{va})"
730 end
730 end
731
731
732 private
732 private
733
733
734 def sql_for_custom_field(field, operator, value, custom_field_id)
734 def sql_for_custom_field(field, operator, value, custom_field_id)
735 db_table = CustomValue.table_name
735 db_table = CustomValue.table_name
736 db_field = 'value'
736 db_field = 'value'
737 filter = @available_filters[field]
737 filter = @available_filters[field]
738 return nil unless filter
738 return nil unless filter
739 if filter[:format] == 'user'
739 if filter[:format] == 'user'
740 if value.delete('me')
740 if value.delete('me')
741 value.push User.current.id.to_s
741 value.push User.current.id.to_s
742 end
742 end
743 end
743 end
744 not_in = nil
744 not_in = nil
745 if operator == '!'
745 if operator == '!'
746 # Makes ! operator work for custom fields with multiple values
746 # Makes ! operator work for custom fields with multiple values
747 operator = '='
747 operator = '='
748 not_in = 'NOT'
748 not_in = 'NOT'
749 end
749 end
750 customized_key = "id"
750 customized_key = "id"
751 customized_class = Issue
751 customized_class = Issue
752 if field =~ /^(.+)\.cf_/
752 if field =~ /^(.+)\.cf_/
753 assoc = $1
753 assoc = $1
754 customized_key = "#{assoc}_id"
754 customized_key = "#{assoc}_id"
755 customized_class = Issue.reflect_on_association(assoc.to_sym).klass.base_class rescue nil
755 customized_class = Issue.reflect_on_association(assoc.to_sym).klass.base_class rescue nil
756 raise "Unknown Issue association #{assoc}" unless customized_class
756 raise "Unknown Issue association #{assoc}" unless customized_class
757 end
757 end
758 "#{Issue.table_name}.#{customized_key} #{not_in} IN (SELECT #{customized_class.table_name}.id FROM #{customized_class.table_name} LEFT OUTER JOIN #{db_table} ON #{db_table}.customized_type='#{customized_class}' AND #{db_table}.customized_id=#{customized_class.table_name}.id AND #{db_table}.custom_field_id=#{custom_field_id} WHERE " +
758 "#{Issue.table_name}.#{customized_key} #{not_in} IN (SELECT #{customized_class.table_name}.id FROM #{customized_class.table_name} LEFT OUTER JOIN #{db_table} ON #{db_table}.customized_type='#{customized_class}' AND #{db_table}.customized_id=#{customized_class.table_name}.id AND #{db_table}.custom_field_id=#{custom_field_id} WHERE " +
759 sql_for_field(field, operator, value, db_table, db_field, true) + ')'
759 sql_for_field(field, operator, value, db_table, db_field, true) + ')'
760 end
760 end
761
761
762 # Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+
762 # Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+
763 def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)
763 def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)
764 sql = ''
764 sql = ''
765 case operator
765 case operator
766 when "="
766 when "="
767 if value.any?
767 if value.any?
768 case type_for(field)
768 case type_for(field)
769 when :date, :date_past
769 when :date, :date_past
770 sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), (Date.parse(value.first) rescue nil))
770 sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), (Date.parse(value.first) rescue nil))
771 when :integer
771 when :integer
772 if is_custom_filter
772 if is_custom_filter
773 sql = "(#{db_table}.#{db_field} <> '' AND CAST(#{db_table}.#{db_field} AS decimal(60,3)) = #{value.first.to_i})"
773 sql = "(#{db_table}.#{db_field} <> '' AND CAST(#{db_table}.#{db_field} AS decimal(60,3)) = #{value.first.to_i})"
774 else
774 else
775 sql = "#{db_table}.#{db_field} = #{value.first.to_i}"
775 sql = "#{db_table}.#{db_field} = #{value.first.to_i}"
776 end
776 end
777 when :float
777 when :float
778 if is_custom_filter
778 if is_custom_filter
779 sql = "(#{db_table}.#{db_field} <> '' AND CAST(#{db_table}.#{db_field} AS decimal(60,3)) BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5})"
779 sql = "(#{db_table}.#{db_field} <> '' AND CAST(#{db_table}.#{db_field} AS decimal(60,3)) BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5})"
780 else
780 else
781 sql = "#{db_table}.#{db_field} BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5}"
781 sql = "#{db_table}.#{db_field} BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5}"
782 end
782 end
783 else
783 else
784 sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")"
784 sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")"
785 end
785 end
786 else
786 else
787 # IN an empty set
787 # IN an empty set
788 sql = "1=0"
788 sql = "1=0"
789 end
789 end
790 when "!"
790 when "!"
791 if value.any?
791 if value.any?
792 sql = "(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + "))"
792 sql = "(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + "))"
793 else
793 else
794 # NOT IN an empty set
794 # NOT IN an empty set
795 sql = "1=1"
795 sql = "1=1"
796 end
796 end
797 when "!*"
797 when "!*"
798 sql = "#{db_table}.#{db_field} IS NULL"
798 sql = "#{db_table}.#{db_field} IS NULL"
799 sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter
799 sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter
800 when "*"
800 when "*"
801 sql = "#{db_table}.#{db_field} IS NOT NULL"
801 sql = "#{db_table}.#{db_field} IS NOT NULL"
802 sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
802 sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
803 when ">="
803 when ">="
804 if [:date, :date_past].include?(type_for(field))
804 if [:date, :date_past].include?(type_for(field))
805 sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), nil)
805 sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), nil)
806 else
806 else
807 if is_custom_filter
807 if is_custom_filter
808 sql = "(#{db_table}.#{db_field} <> '' AND CAST(#{db_table}.#{db_field} AS decimal(60,3)) >= #{value.first.to_f})"
808 sql = "(#{db_table}.#{db_field} <> '' AND CAST(#{db_table}.#{db_field} AS decimal(60,3)) >= #{value.first.to_f})"
809 else
809 else
810 sql = "#{db_table}.#{db_field} >= #{value.first.to_f}"
810 sql = "#{db_table}.#{db_field} >= #{value.first.to_f}"
811 end
811 end
812 end
812 end
813 when "<="
813 when "<="
814 if [:date, :date_past].include?(type_for(field))
814 if [:date, :date_past].include?(type_for(field))
815 sql = date_clause(db_table, db_field, nil, (Date.parse(value.first) rescue nil))
815 sql = date_clause(db_table, db_field, nil, (Date.parse(value.first) rescue nil))
816 else
816 else
817 if is_custom_filter
817 if is_custom_filter
818 sql = "(#{db_table}.#{db_field} <> '' AND CAST(#{db_table}.#{db_field} AS decimal(60,3)) <= #{value.first.to_f})"
818 sql = "(#{db_table}.#{db_field} <> '' AND CAST(#{db_table}.#{db_field} AS decimal(60,3)) <= #{value.first.to_f})"
819 else
819 else
820 sql = "#{db_table}.#{db_field} <= #{value.first.to_f}"
820 sql = "#{db_table}.#{db_field} <= #{value.first.to_f}"
821 end
821 end
822 end
822 end
823 when "><"
823 when "><"
824 if [:date, :date_past].include?(type_for(field))
824 if [:date, :date_past].include?(type_for(field))
825 sql = date_clause(db_table, db_field, (Date.parse(value[0]) rescue nil), (Date.parse(value[1]) rescue nil))
825 sql = date_clause(db_table, db_field, (Date.parse(value[0]) rescue nil), (Date.parse(value[1]) rescue nil))
826 else
826 else
827 if is_custom_filter
827 if is_custom_filter
828 sql = "(#{db_table}.#{db_field} <> '' AND CAST(#{db_table}.#{db_field} AS decimal(60,3)) BETWEEN #{value[0].to_f} AND #{value[1].to_f})"
828 sql = "(#{db_table}.#{db_field} <> '' AND CAST(#{db_table}.#{db_field} AS decimal(60,3)) BETWEEN #{value[0].to_f} AND #{value[1].to_f})"
829 else
829 else
830 sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
830 sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
831 end
831 end
832 end
832 end
833 when "o"
833 when "o"
834 sql = "#{Issue.table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{connection.quoted_false})" if field == "status_id"
834 sql = "#{Issue.table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{connection.quoted_false})" if field == "status_id"
835 when "c"
835 when "c"
836 sql = "#{Issue.table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{connection.quoted_true})" if field == "status_id"
836 sql = "#{Issue.table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{connection.quoted_true})" if field == "status_id"
837 when ">t-"
837 when ">t-"
838 sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0)
838 sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0)
839 when "<t-"
839 when "<t-"
840 sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i)
840 sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i)
841 when "t-"
841 when "t-"
842 sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i)
842 sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i)
843 when ">t+"
843 when ">t+"
844 sql = relative_date_clause(db_table, db_field, value.first.to_i, nil)
844 sql = relative_date_clause(db_table, db_field, value.first.to_i, nil)
845 when "<t+"
845 when "<t+"
846 sql = relative_date_clause(db_table, db_field, 0, value.first.to_i)
846 sql = relative_date_clause(db_table, db_field, 0, value.first.to_i)
847 when "t+"
847 when "t+"
848 sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i)
848 sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i)
849 when "t"
849 when "t"
850 sql = relative_date_clause(db_table, db_field, 0, 0)
850 sql = relative_date_clause(db_table, db_field, 0, 0)
851 when "w"
851 when "w"
852 first_day_of_week = l(:general_first_day_of_week).to_i
852 first_day_of_week = l(:general_first_day_of_week).to_i
853 day_of_week = Date.today.cwday
853 day_of_week = Date.today.cwday
854 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
854 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
855 sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6)
855 sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6)
856 when "~"
856 when "~"
857 sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
857 sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
858 when "!~"
858 when "!~"
859 sql = "LOWER(#{db_table}.#{db_field}) NOT LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
859 sql = "LOWER(#{db_table}.#{db_field}) NOT LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
860 else
860 else
861 raise "Unknown query operator #{operator}"
861 raise "Unknown query operator #{operator}"
862 end
862 end
863
863
864 return sql
864 return sql
865 end
865 end
866
866
867 def add_custom_fields_filters(custom_fields, assoc=nil)
867 def add_custom_fields_filters(custom_fields, assoc=nil)
868 return unless custom_fields.present?
868 return unless custom_fields.present?
869 @available_filters ||= {}
869 @available_filters ||= {}
870
870
871 custom_fields.select(&:is_filter?).each do |field|
871 custom_fields.select(&:is_filter?).each do |field|
872 case field.field_format
872 case field.field_format
873 when "text"
873 when "text"
874 options = { :type => :text, :order => 20 }
874 options = { :type => :text, :order => 20 }
875 when "list"
875 when "list"
876 options = { :type => :list_optional, :values => field.possible_values, :order => 20}
876 options = { :type => :list_optional, :values => field.possible_values, :order => 20}
877 when "date"
877 when "date"
878 options = { :type => :date, :order => 20 }
878 options = { :type => :date, :order => 20 }
879 when "bool"
879 when "bool"
880 options = { :type => :list, :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]], :order => 20 }
880 options = { :type => :list, :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]], :order => 20 }
881 when "int"
881 when "int"
882 options = { :type => :integer, :order => 20 }
882 options = { :type => :integer, :order => 20 }
883 when "float"
883 when "float"
884 options = { :type => :float, :order => 20 }
884 options = { :type => :float, :order => 20 }
885 when "user", "version"
885 when "user", "version"
886 next unless project
886 next unless project
887 values = field.possible_values_options(project)
887 values = field.possible_values_options(project)
888 if User.current.logged? && field.field_format == 'user'
888 if User.current.logged? && field.field_format == 'user'
889 values.unshift ["<< #{l(:label_me)} >>", "me"]
889 values.unshift ["<< #{l(:label_me)} >>", "me"]
890 end
890 end
891 options = { :type => :list_optional, :values => values, :order => 20}
891 options = { :type => :list_optional, :values => values, :order => 20}
892 else
892 else
893 options = { :type => :string, :order => 20 }
893 options = { :type => :string, :order => 20 }
894 end
894 end
895 filter_id = "cf_#{field.id}"
895 filter_id = "cf_#{field.id}"
896 filter_name = field.name
896 filter_name = field.name
897 if assoc.present?
897 if assoc.present?
898 filter_id = "#{assoc}.#{filter_id}"
898 filter_id = "#{assoc}.#{filter_id}"
899 filter_name = l("label_attribute_of_#{assoc}", :name => filter_name)
899 filter_name = l("label_attribute_of_#{assoc}", :name => filter_name)
900 end
900 end
901 @available_filters[filter_id] = options.merge({ :name => filter_name, :format => field.field_format })
901 @available_filters[filter_id] = options.merge({ :name => filter_name, :format => field.field_format })
902 end
902 end
903 end
903 end
904
904
905 def add_associations_custom_fields_filters(*associations)
905 def add_associations_custom_fields_filters(*associations)
906 fields_by_class = CustomField.where(:is_filter => true).group_by(&:class)
906 fields_by_class = CustomField.where(:is_filter => true).group_by(&:class)
907 associations.each do |assoc|
907 associations.each do |assoc|
908 association_klass = Issue.reflect_on_association(assoc).klass
908 association_klass = Issue.reflect_on_association(assoc).klass
909 fields_by_class.each do |field_class, fields|
909 fields_by_class.each do |field_class, fields|
910 if field_class.customized_class <= association_klass
910 if field_class.customized_class <= association_klass
911 add_custom_fields_filters(fields, assoc)
911 add_custom_fields_filters(fields, assoc)
912 end
912 end
913 end
913 end
914 end
914 end
915 end
915 end
916
916
917 # Returns a SQL clause for a date or datetime field.
917 # Returns a SQL clause for a date or datetime field.
918 def date_clause(table, field, from, to)
918 def date_clause(table, field, from, to)
919 s = []
919 s = []
920 if from
920 if from
921 from_yesterday = from - 1
921 from_yesterday = from - 1
922 from_yesterday_time = Time.local(from_yesterday.year, from_yesterday.month, from_yesterday.day)
922 from_yesterday_time = Time.local(from_yesterday.year, from_yesterday.month, from_yesterday.day)
923 if self.class.default_timezone == :utc
923 if self.class.default_timezone == :utc
924 from_yesterday_time = from_yesterday_time.utc
924 from_yesterday_time = from_yesterday_time.utc
925 end
925 end
926 s << ("#{table}.#{field} > '%s'" % [connection.quoted_date(from_yesterday_time.end_of_day)])
926 s << ("#{table}.#{field} > '%s'" % [connection.quoted_date(from_yesterday_time.end_of_day)])
927 end
927 end
928 if to
928 if to
929 to_time = Time.local(to.year, to.month, to.day)
929 to_time = Time.local(to.year, to.month, to.day)
930 if self.class.default_timezone == :utc
930 if self.class.default_timezone == :utc
931 to_time = to_time.utc
931 to_time = to_time.utc
932 end
932 end
933 s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date(to_time.end_of_day)])
933 s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date(to_time.end_of_day)])
934 end
934 end
935 s.join(' AND ')
935 s.join(' AND ')
936 end
936 end
937
937
938 # Returns a SQL clause for a date or datetime field using relative dates.
938 # Returns a SQL clause for a date or datetime field using relative dates.
939 def relative_date_clause(table, field, days_from, days_to)
939 def relative_date_clause(table, field, days_from, days_to)
940 date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil))
940 date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil))
941 end
941 end
942
942
943 # Additional joins required for the given sort options
943 # Additional joins required for the given sort options
944 def joins_for_order_statement(order_options)
944 def joins_for_order_statement(order_options)
945 joins = []
945 joins = []
946
946
947 if order_options
947 if order_options
948 if order_options.include?('authors')
948 if order_options.include?('authors')
949 joins << "LEFT OUTER JOIN #{User.table_name} authors ON authors.id = #{Issue.table_name}.author_id"
949 joins << "LEFT OUTER JOIN #{User.table_name} authors ON authors.id = #{Issue.table_name}.author_id"
950 end
950 end
951 order_options.scan(/cf_\d+/).uniq.each do |name|
951 order_options.scan(/cf_\d+/).uniq.each do |name|
952 column = available_columns.detect {|c| c.name.to_s == name}
952 column = available_columns.detect {|c| c.name.to_s == name}
953 join = column && column.custom_field.join_for_order_statement
953 join = column && column.custom_field.join_for_order_statement
954 if join
954 if join
955 joins << join
955 joins << join
956 end
956 end
957 end
957 end
958 end
958 end
959
959
960 joins.any? ? joins.join(' ') : nil
960 joins.any? ? joins.join(' ') : nil
961 end
961 end
962 end
962 end
General Comments 0
You need to be logged in to leave comments. Login now