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