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