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