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