##// END OF EJS Templates
Adds scopes for retrieving the appropriate queries (#14790)....
Jean-Philippe Lang -
r15259:275bd514cd21
parent child
Show More
@@ -1,314 +1,307
1 1 # encoding: utf-8
2 2 #
3 3 # Redmine - project management software
4 4 # Copyright (C) 2006-2016 Jean-Philippe Lang
5 5 #
6 6 # This program is free software; you can redistribute it and/or
7 7 # modify it under the terms of the GNU General Public License
8 8 # as published by the Free Software Foundation; either version 2
9 9 # of the License, or (at your option) any later version.
10 10 #
11 11 # This program is distributed in the hope that it will be useful,
12 12 # but WITHOUT ANY WARRANTY; without even the implied warranty of
13 13 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 14 # GNU General Public License for more details.
15 15 #
16 16 # You should have received a copy of the GNU General Public License
17 17 # along with this program; if not, write to the Free Software
18 18 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
19 19
20 20 module QueriesHelper
21 21 include ApplicationHelper
22 22
23 23 def filters_options_for_select(query)
24 24 ungrouped = []
25 25 grouped = {}
26 26 query.available_filters.map do |field, field_options|
27 27 if [:tree, :relation].include?(field_options[:type])
28 28 group = :label_relations
29 29 elsif field =~ /^(.+)\./
30 30 # association filters
31 31 group = "field_#{$1}"
32 32 elsif %w(member_of_group assigned_to_role).include?(field)
33 33 group = :field_assigned_to
34 34 elsif field_options[:type] == :date_past || field_options[:type] == :date
35 35 group = :label_date
36 36 end
37 37 if group
38 38 (grouped[group] ||= []) << [field_options[:name], field]
39 39 else
40 40 ungrouped << [field_options[:name], field]
41 41 end
42 42 end
43 43 # Don't group dates if there's only one (eg. time entries filters)
44 44 if grouped[:label_date].try(:size) == 1
45 45 ungrouped << grouped.delete(:label_date).first
46 46 end
47 47 s = options_for_select([[]] + ungrouped)
48 48 if grouped.present?
49 49 localized_grouped = grouped.map {|k,v| [l(k), v]}
50 50 s << grouped_options_for_select(localized_grouped)
51 51 end
52 52 s
53 53 end
54 54
55 55 def query_filters_hidden_tags(query)
56 56 tags = ''.html_safe
57 57 query.filters.each do |field, options|
58 58 tags << hidden_field_tag("f[]", field, :id => nil)
59 59 tags << hidden_field_tag("op[#{field}]", options[:operator], :id => nil)
60 60 options[:values].each do |value|
61 61 tags << hidden_field_tag("v[#{field}][]", value, :id => nil)
62 62 end
63 63 end
64 64 tags
65 65 end
66 66
67 67 def query_columns_hidden_tags(query)
68 68 tags = ''.html_safe
69 69 query.columns.each do |column|
70 70 tags << hidden_field_tag("c[]", column.name, :id => nil)
71 71 end
72 72 tags
73 73 end
74 74
75 75 def query_hidden_tags(query)
76 76 query_filters_hidden_tags(query) + query_columns_hidden_tags(query)
77 77 end
78 78
79 79 def available_block_columns_tags(query)
80 80 tags = ''.html_safe
81 81 query.available_block_columns.each do |column|
82 82 tags << content_tag('label', check_box_tag('c[]', column.name.to_s, query.has_column?(column), :id => nil) + " #{column.caption}", :class => 'inline')
83 83 end
84 84 tags
85 85 end
86 86
87 87 def available_totalable_columns_tags(query)
88 88 tags = ''.html_safe
89 89 query.available_totalable_columns.each do |column|
90 90 tags << content_tag('label', check_box_tag('t[]', column.name.to_s, query.totalable_columns.include?(column), :id => nil) + " #{column.caption}", :class => 'inline')
91 91 end
92 92 tags << hidden_field_tag('t[]', '')
93 93 tags
94 94 end
95 95
96 96 def query_available_inline_columns_options(query)
97 97 (query.available_inline_columns - query.columns).reject(&:frozen?).collect {|column| [column.caption, column.name]}
98 98 end
99 99
100 100 def query_selected_inline_columns_options(query)
101 101 (query.inline_columns & query.available_inline_columns).reject(&:frozen?).collect {|column| [column.caption, column.name]}
102 102 end
103 103
104 104 def render_query_columns_selection(query, options={})
105 105 tag_name = (options[:name] || 'c') + '[]'
106 106 render :partial => 'queries/columns', :locals => {:query => query, :tag_name => tag_name}
107 107 end
108 108
109 109 def render_query_totals(query)
110 110 return unless query.totalable_columns.present?
111 111 totals = query.totalable_columns.map do |column|
112 112 total_tag(column, query.total_for(column))
113 113 end
114 114 content_tag('p', totals.join(" ").html_safe, :class => "query-totals")
115 115 end
116 116
117 117 def total_tag(column, value)
118 118 label = content_tag('span', "#{column.caption}:")
119 119 value = content_tag('span', format_object(value), :class => 'value')
120 120 content_tag('span', label + " " + value, :class => "total-for-#{column.name.to_s.dasherize}")
121 121 end
122 122
123 123 def column_header(column)
124 124 column.sortable ? sort_header_tag(column.name.to_s, :caption => column.caption,
125 125 :default_order => column.default_order) :
126 126 content_tag('th', h(column.caption))
127 127 end
128 128
129 129 def column_content(column, issue)
130 130 value = column.value_object(issue)
131 131 if value.is_a?(Array)
132 132 value.collect {|v| column_value(column, issue, v)}.compact.join(', ').html_safe
133 133 else
134 134 column_value(column, issue, value)
135 135 end
136 136 end
137 137
138 138 def column_value(column, issue, value)
139 139 case column.name
140 140 when :id
141 141 link_to value, issue_path(issue)
142 142 when :subject
143 143 link_to value, issue_path(issue)
144 144 when :parent
145 145 value ? (value.visible? ? link_to_issue(value, :subject => false) : "##{value.id}") : ''
146 146 when :description
147 147 issue.description? ? content_tag('div', textilizable(issue, :description), :class => "wiki") : ''
148 148 when :done_ratio
149 149 progress_bar(value)
150 150 when :relations
151 151 content_tag('span',
152 152 value.to_s(issue) {|other| link_to_issue(other, :subject => false, :tracker => false)}.html_safe,
153 153 :class => value.css_classes_for(issue))
154 154 else
155 155 format_object(value)
156 156 end
157 157 end
158 158
159 159 def csv_content(column, issue)
160 160 value = column.value_object(issue)
161 161 if value.is_a?(Array)
162 162 value.collect {|v| csv_value(column, issue, v)}.compact.join(', ')
163 163 else
164 164 csv_value(column, issue, value)
165 165 end
166 166 end
167 167
168 168 def csv_value(column, object, value)
169 169 format_object(value, false) do |value|
170 170 case value.class.name
171 171 when 'Float'
172 172 sprintf("%.2f", value).gsub('.', l(:general_csv_decimal_separator))
173 173 when 'IssueRelation'
174 174 value.to_s(object)
175 175 when 'Issue'
176 176 if object.is_a?(TimeEntry)
177 177 "#{value.tracker} ##{value.id}: #{value.subject}"
178 178 else
179 179 value.id
180 180 end
181 181 else
182 182 value
183 183 end
184 184 end
185 185 end
186 186
187 187 def query_to_csv(items, query, options={})
188 188 options ||= {}
189 189 columns = (options[:columns] == 'all' ? query.available_inline_columns : query.inline_columns)
190 190 query.available_block_columns.each do |column|
191 191 if options[column.name].present?
192 192 columns << column
193 193 end
194 194 end
195 195
196 196 Redmine::Export::CSV.generate do |csv|
197 197 # csv header fields
198 198 csv << columns.map {|c| c.caption.to_s}
199 199 # csv lines
200 200 items.each do |item|
201 201 csv << columns.map {|c| csv_content(c, item)}
202 202 end
203 203 end
204 204 end
205 205
206 206 # Retrieve query from session or build a new query
207 207 def retrieve_query(klass=IssueQuery, use_session=true)
208 208 session_key = klass.name.underscore.to_sym
209 209
210 210 if params[:query_id].present?
211 211 cond = "project_id IS NULL"
212 212 cond << " OR project_id = #{@project.id}" if @project
213 213 @query = klass.where(cond).find(params[:query_id])
214 214 raise ::Unauthorized unless @query.visible?
215 215 @query.project = @project
216 216 session[session_key] = {:id => @query.id, :project_id => @query.project_id} if use_session
217 217 sort_clear
218 218 elsif api_request? || params[:set_filter] || !use_session || session[session_key].nil? || session[session_key][:project_id] != (@project ? @project.id : nil)
219 219 # Give it a name, required to be valid
220 220 @query = klass.new(:name => "_", :project => @project)
221 221 @query.build_from_params(params)
222 222 session[session_key] = {:project_id => @query.project_id, :filters => @query.filters, :group_by => @query.group_by, :column_names => @query.column_names, :totalable_names => @query.totalable_names} if use_session
223 223 else
224 224 # retrieve from session
225 225 @query = nil
226 226 @query = klass.find_by_id(session[session_key][:id]) if session[session_key][:id]
227 227 @query ||= klass.new(:name => "_", :filters => session[session_key][:filters], :group_by => session[session_key][:group_by], :column_names => session[session_key][:column_names], :totalable_names => session[session_key][:totalable_names])
228 228 @query.project = @project
229 229 end
230 230 end
231 231
232 232 def retrieve_query_from_session
233 233 if session[:query]
234 234 if session[:query][:id]
235 235 @query = IssueQuery.find_by_id(session[:query][:id])
236 236 return unless @query
237 237 else
238 238 @query = IssueQuery.new(:name => "_", :filters => session[:query][:filters], :group_by => session[:query][:group_by], :column_names => session[:query][:column_names], :totalable_names => session[:query][:totalable_names])
239 239 end
240 240 if session[:query].has_key?(:project_id)
241 241 @query.project_id = session[:query][:project_id]
242 242 else
243 243 @query.project = @project
244 244 end
245 245 @query
246 246 end
247 247 end
248 248
249 249 # Returns the query definition as hidden field tags
250 250 def query_as_hidden_field_tags(query)
251 251 tags = hidden_field_tag("set_filter", "1", :id => nil)
252 252
253 253 if query.filters.present?
254 254 query.filters.each do |field, filter|
255 255 tags << hidden_field_tag("f[]", field, :id => nil)
256 256 tags << hidden_field_tag("op[#{field}]", filter[:operator], :id => nil)
257 257 filter[:values].each do |value|
258 258 tags << hidden_field_tag("v[#{field}][]", value, :id => nil)
259 259 end
260 260 end
261 261 else
262 262 tags << hidden_field_tag("f[]", "", :id => nil)
263 263 end
264 264 if query.column_names.present?
265 265 query.column_names.each do |name|
266 266 tags << hidden_field_tag("c[]", name, :id => nil)
267 267 end
268 268 end
269 269 if query.totalable_names.present?
270 270 query.totalable_names.each do |name|
271 271 tags << hidden_field_tag("t[]", name, :id => nil)
272 272 end
273 273 end
274 274 if query.group_by.present?
275 275 tags << hidden_field_tag("group_by", query.group_by, :id => nil)
276 276 end
277 277
278 278 tags
279 279 end
280 280
281 281 def sidebar_queries
282 unless @sidebar_queries
283 @sidebar_queries = IssueQuery.visible.
284 order("#{Query.table_name}.name ASC").
285 # Project specific queries and global queries
286 where(@project.nil? ? ["project_id IS NULL"] : ["project_id IS NULL OR project_id = ?", @project.id]).
287 to_a
288 end
289 @sidebar_queries
282 @sidebar_queries ||= IssueQuery.visible.global_or_on_project(@project).sorted.to_a
290 283 end
291 284
292 285 def query_links(title, queries)
293 286 return '' if queries.empty?
294 287 # links to #index on issues/show
295 288 url_params = controller_name == 'issues' ? {:controller => 'issues', :action => 'index', :project_id => @project} : {}
296 289
297 290 content_tag('h3', title) + "\n" +
298 291 content_tag('ul',
299 292 queries.collect {|query|
300 293 css = 'query'
301 294 css << ' selected' if query == @query
302 295 content_tag('li', link_to(query.name, url_params.merge(:query_id => query), :class => css))
303 296 }.join("\n").html_safe,
304 297 :class => 'queries'
305 298 ) + "\n"
306 299 end
307 300
308 301 def render_sidebar_queries
309 302 out = ''.html_safe
310 303 out << query_links(l(:label_my_queries), sidebar_queries.select(&:is_private?))
311 304 out << query_links(l(:label_query_plural), sidebar_queries.reject(&:is_private?))
312 305 out
313 306 end
314 307 end
@@ -1,1104 +1,1111
1 1 # Redmine - project management software
2 2 # Copyright (C) 2006-2016 Jean-Philippe Lang
3 3 #
4 4 # This program is free software; you can redistribute it and/or
5 5 # modify it under the terms of the GNU General Public License
6 6 # as published by the Free Software Foundation; either version 2
7 7 # of the License, or (at your option) any later version.
8 8 #
9 9 # This program is distributed in the hope that it will be useful,
10 10 # but WITHOUT ANY WARRANTY; without even the implied warranty of
11 11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 12 # GNU General Public License for more details.
13 13 #
14 14 # You should have received a copy of the GNU General Public License
15 15 # along with this program; if not, write to the Free Software
16 16 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
17 17
18 18 class QueryColumn
19 19 attr_accessor :name, :sortable, :groupable, :totalable, :default_order
20 20 include Redmine::I18n
21 21
22 22 def initialize(name, options={})
23 23 self.name = name
24 24 self.sortable = options[:sortable]
25 25 self.groupable = options[:groupable] || false
26 26 if groupable == true
27 27 self.groupable = name.to_s
28 28 end
29 29 self.totalable = options[:totalable] || false
30 30 self.default_order = options[:default_order]
31 31 @inline = options.key?(:inline) ? options[:inline] : true
32 32 @caption_key = options[:caption] || "field_#{name}".to_sym
33 33 @frozen = options[:frozen]
34 34 end
35 35
36 36 def caption
37 37 case @caption_key
38 38 when Symbol
39 39 l(@caption_key)
40 40 when Proc
41 41 @caption_key.call
42 42 else
43 43 @caption_key
44 44 end
45 45 end
46 46
47 47 # Returns true if the column is sortable, otherwise false
48 48 def sortable?
49 49 !@sortable.nil?
50 50 end
51 51
52 52 def sortable
53 53 @sortable.is_a?(Proc) ? @sortable.call : @sortable
54 54 end
55 55
56 56 def inline?
57 57 @inline
58 58 end
59 59
60 60 def frozen?
61 61 @frozen
62 62 end
63 63
64 64 def value(object)
65 65 object.send name
66 66 end
67 67
68 68 def value_object(object)
69 69 object.send name
70 70 end
71 71
72 72 def css_classes
73 73 name
74 74 end
75 75 end
76 76
77 77 class QueryCustomFieldColumn < QueryColumn
78 78
79 79 def initialize(custom_field)
80 80 self.name = "cf_#{custom_field.id}".to_sym
81 81 self.sortable = custom_field.order_statement || false
82 82 self.groupable = custom_field.group_statement || false
83 83 self.totalable = custom_field.totalable?
84 84 @inline = true
85 85 @cf = custom_field
86 86 end
87 87
88 88 def caption
89 89 @cf.name
90 90 end
91 91
92 92 def custom_field
93 93 @cf
94 94 end
95 95
96 96 def value_object(object)
97 97 if custom_field.visible_by?(object.project, User.current)
98 98 cv = object.custom_values.select {|v| v.custom_field_id == @cf.id}
99 99 cv.size > 1 ? cv.sort {|a,b| a.value.to_s <=> b.value.to_s} : cv.first
100 100 else
101 101 nil
102 102 end
103 103 end
104 104
105 105 def value(object)
106 106 raw = value_object(object)
107 107 if raw.is_a?(Array)
108 108 raw.map {|r| @cf.cast_value(r.value)}
109 109 elsif raw
110 110 @cf.cast_value(raw.value)
111 111 else
112 112 nil
113 113 end
114 114 end
115 115
116 116 def css_classes
117 117 @css_classes ||= "#{name} #{@cf.field_format}"
118 118 end
119 119 end
120 120
121 121 class QueryAssociationCustomFieldColumn < QueryCustomFieldColumn
122 122
123 123 def initialize(association, custom_field)
124 124 super(custom_field)
125 125 self.name = "#{association}.cf_#{custom_field.id}".to_sym
126 126 # TODO: support sorting/grouping by association custom field
127 127 self.sortable = false
128 128 self.groupable = false
129 129 @association = association
130 130 end
131 131
132 132 def value_object(object)
133 133 if assoc = object.send(@association)
134 134 super(assoc)
135 135 end
136 136 end
137 137
138 138 def css_classes
139 139 @css_classes ||= "#{@association}_cf_#{@cf.id} #{@cf.field_format}"
140 140 end
141 141 end
142 142
143 143 class Query < ActiveRecord::Base
144 144 class StatementInvalid < ::ActiveRecord::StatementInvalid
145 145 end
146 146
147 147 include Redmine::SubclassFactory
148 148
149 149 VISIBILITY_PRIVATE = 0
150 150 VISIBILITY_ROLES = 1
151 151 VISIBILITY_PUBLIC = 2
152 152
153 153 belongs_to :project
154 154 belongs_to :user
155 155 has_and_belongs_to_many :roles, :join_table => "#{table_name_prefix}queries_roles#{table_name_suffix}", :foreign_key => "query_id"
156 156 serialize :filters
157 157 serialize :column_names
158 158 serialize :sort_criteria, Array
159 159 serialize :options, Hash
160 160
161 161 attr_protected :project_id, :user_id
162 162
163 163 validates_presence_of :name
164 164 validates_length_of :name, :maximum => 255
165 165 validates :visibility, :inclusion => { :in => [VISIBILITY_PUBLIC, VISIBILITY_ROLES, VISIBILITY_PRIVATE] }
166 166 validate :validate_query_filters
167 167 validate do |query|
168 168 errors.add(:base, l(:label_role_plural) + ' ' + l('activerecord.errors.messages.blank')) if query.visibility == VISIBILITY_ROLES && roles.blank?
169 169 end
170 170
171 171 after_save do |query|
172 172 if query.visibility_changed? && query.visibility != VISIBILITY_ROLES
173 173 query.roles.clear
174 174 end
175 175 end
176 176
177 177 class_attribute :operators
178 178 self.operators = {
179 179 "=" => :label_equals,
180 180 "!" => :label_not_equals,
181 181 "o" => :label_open_issues,
182 182 "c" => :label_closed_issues,
183 183 "!*" => :label_none,
184 184 "*" => :label_any,
185 185 ">=" => :label_greater_or_equal,
186 186 "<=" => :label_less_or_equal,
187 187 "><" => :label_between,
188 188 "<t+" => :label_in_less_than,
189 189 ">t+" => :label_in_more_than,
190 190 "><t+"=> :label_in_the_next_days,
191 191 "t+" => :label_in,
192 192 "t" => :label_today,
193 193 "ld" => :label_yesterday,
194 194 "w" => :label_this_week,
195 195 "lw" => :label_last_week,
196 196 "l2w" => [:label_last_n_weeks, {:count => 2}],
197 197 "m" => :label_this_month,
198 198 "lm" => :label_last_month,
199 199 "y" => :label_this_year,
200 200 ">t-" => :label_less_than_ago,
201 201 "<t-" => :label_more_than_ago,
202 202 "><t-"=> :label_in_the_past_days,
203 203 "t-" => :label_ago,
204 204 "~" => :label_contains,
205 205 "!~" => :label_not_contains,
206 206 "=p" => :label_any_issues_in_project,
207 207 "=!p" => :label_any_issues_not_in_project,
208 208 "!p" => :label_no_issues_in_project,
209 209 "*o" => :label_any_open_issues,
210 210 "!o" => :label_no_open_issues
211 211 }
212 212
213 213 class_attribute :operators_by_filter_type
214 214 self.operators_by_filter_type = {
215 215 :list => [ "=", "!" ],
216 216 :list_status => [ "o", "=", "!", "c", "*" ],
217 217 :list_optional => [ "=", "!", "!*", "*" ],
218 218 :list_subprojects => [ "*", "!*", "=" ],
219 219 :date => [ "=", ">=", "<=", "><", "<t+", ">t+", "><t+", "t+", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", ">t-", "<t-", "><t-", "t-", "!*", "*" ],
220 220 :date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "><t-", "t-", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", "!*", "*" ],
221 221 :string => [ "=", "~", "!", "!~", "!*", "*" ],
222 222 :text => [ "~", "!~", "!*", "*" ],
223 223 :integer => [ "=", ">=", "<=", "><", "!*", "*" ],
224 224 :float => [ "=", ">=", "<=", "><", "!*", "*" ],
225 225 :relation => ["=", "=p", "=!p", "!p", "*o", "!o", "!*", "*"],
226 226 :tree => ["=", "~", "!*", "*"]
227 227 }
228 228
229 229 class_attribute :available_columns
230 230 self.available_columns = []
231 231
232 232 class_attribute :queried_class
233 233
234 234 # Permission required to view the queries, set on subclasses.
235 235 class_attribute :view_permission
236 236
237 # Scope of queries that are global or on the given project
238 scope :global_or_on_project, lambda {|project|
239 where(:project_id => (project.nil? ? nil : [nil, project.id]))
240 }
241
242 scope :sorted, lambda {order(:name, :id)}
243
237 244 # Scope of visible queries, can be used from subclasses only.
238 245 # Unlike other visible scopes, a class methods is used as it
239 246 # let handle inheritance more nicely than scope DSL.
240 247 def self.visible(*args)
241 248 if self == ::Query
242 249 # Visibility depends on permissions for each subclass,
243 250 # raise an error if the scope is called from Query (eg. Query.visible)
244 251 raise Exception.new("Cannot call .visible scope from the base Query class, but from subclasses only.")
245 252 end
246 253
247 254 user = args.shift || User.current
248 255 base = Project.allowed_to_condition(user, view_permission, *args)
249 256 scope = joins("LEFT OUTER JOIN #{Project.table_name} ON #{table_name}.project_id = #{Project.table_name}.id").
250 257 where("#{table_name}.project_id IS NULL OR (#{base})")
251 258
252 259 if user.admin?
253 260 scope.where("#{table_name}.visibility <> ? OR #{table_name}.user_id = ?", VISIBILITY_PRIVATE, user.id)
254 261 elsif user.memberships.any?
255 262 scope.where("#{table_name}.visibility = ?" +
256 263 " OR (#{table_name}.visibility = ? AND #{table_name}.id IN (" +
257 264 "SELECT DISTINCT q.id FROM #{table_name} q" +
258 265 " INNER JOIN #{table_name_prefix}queries_roles#{table_name_suffix} qr on qr.query_id = q.id" +
259 266 " INNER JOIN #{MemberRole.table_name} mr ON mr.role_id = qr.role_id" +
260 267 " INNER JOIN #{Member.table_name} m ON m.id = mr.member_id AND m.user_id = ?" +
261 268 " WHERE q.project_id IS NULL OR q.project_id = m.project_id))" +
262 269 " OR #{table_name}.user_id = ?",
263 270 VISIBILITY_PUBLIC, VISIBILITY_ROLES, user.id, user.id)
264 271 elsif user.logged?
265 272 scope.where("#{table_name}.visibility = ? OR #{table_name}.user_id = ?", VISIBILITY_PUBLIC, user.id)
266 273 else
267 274 scope.where("#{table_name}.visibility = ?", VISIBILITY_PUBLIC)
268 275 end
269 276 end
270 277
271 278 # Returns true if the query is visible to +user+ or the current user.
272 279 def visible?(user=User.current)
273 280 return true if user.admin?
274 281 return false unless project.nil? || user.allowed_to?(self.class.view_permission, project)
275 282 case visibility
276 283 when VISIBILITY_PUBLIC
277 284 true
278 285 when VISIBILITY_ROLES
279 286 if project
280 287 (user.roles_for_project(project) & roles).any?
281 288 else
282 289 Member.where(:user_id => user.id).joins(:roles).where(:member_roles => {:role_id => roles.map(&:id)}).any?
283 290 end
284 291 else
285 292 user == self.user
286 293 end
287 294 end
288 295
289 296 def is_private?
290 297 visibility == VISIBILITY_PRIVATE
291 298 end
292 299
293 300 def is_public?
294 301 !is_private?
295 302 end
296 303
297 304 def queried_table_name
298 305 @queried_table_name ||= self.class.queried_class.table_name
299 306 end
300 307
301 308 def initialize(attributes=nil, *args)
302 309 super attributes
303 310 @is_for_all = project.nil?
304 311 end
305 312
306 313 # Builds the query from the given params
307 314 def build_from_params(params)
308 315 if params[:fields] || params[:f]
309 316 self.filters = {}
310 317 add_filters(params[:fields] || params[:f], params[:operators] || params[:op], params[:values] || params[:v])
311 318 else
312 319 available_filters.keys.each do |field|
313 320 add_short_filter(field, params[field]) if params[field]
314 321 end
315 322 end
316 323 self.group_by = params[:group_by] || (params[:query] && params[:query][:group_by])
317 324 self.column_names = params[:c] || (params[:query] && params[:query][:column_names])
318 325 self.totalable_names = params[:t] || (params[:query] && params[:query][:totalable_names])
319 326 self
320 327 end
321 328
322 329 # Builds a new query from the given params and attributes
323 330 def self.build_from_params(params, attributes={})
324 331 new(attributes).build_from_params(params)
325 332 end
326 333
327 334 def validate_query_filters
328 335 filters.each_key do |field|
329 336 if values_for(field)
330 337 case type_for(field)
331 338 when :integer
332 339 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/\A[+-]?\d+(,[+-]?\d+)*\z/) }
333 340 when :float
334 341 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/\A[+-]?\d+(\.\d*)?\z/) }
335 342 when :date, :date_past
336 343 case operator_for(field)
337 344 when "=", ">=", "<=", "><"
338 345 add_filter_error(field, :invalid) if values_for(field).detect {|v|
339 346 v.present? && (!v.match(/\A\d{4}-\d{2}-\d{2}(T\d{2}((:)?\d{2}){0,2}(Z|\d{2}:?\d{2})?)?\z/) || parse_date(v).nil?)
340 347 }
341 348 when ">t-", "<t-", "t-", ">t+", "<t+", "t+", "><t+", "><t-"
342 349 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^\d+$/) }
343 350 end
344 351 end
345 352 end
346 353
347 354 add_filter_error(field, :blank) unless
348 355 # filter requires one or more values
349 356 (values_for(field) and !values_for(field).first.blank?) or
350 357 # filter doesn't require any value
351 358 ["o", "c", "!*", "*", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", "*o", "!o"].include? operator_for(field)
352 359 end if filters
353 360 end
354 361
355 362 def add_filter_error(field, message)
356 363 m = label_for(field) + " " + l(message, :scope => 'activerecord.errors.messages')
357 364 errors.add(:base, m)
358 365 end
359 366
360 367 def editable_by?(user)
361 368 return false unless user
362 369 # Admin can edit them all and regular users can edit their private queries
363 370 return true if user.admin? || (is_private? && self.user_id == user.id)
364 371 # Members can not edit public queries that are for all project (only admin is allowed to)
365 372 is_public? && !@is_for_all && user.allowed_to?(:manage_public_queries, project)
366 373 end
367 374
368 375 def trackers
369 376 @trackers ||= (project.nil? ? Tracker.all : project.rolled_up_trackers).visible.sorted
370 377 end
371 378
372 379 # Returns a hash of localized labels for all filter operators
373 380 def self.operators_labels
374 381 operators.inject({}) {|h, operator| h[operator.first] = l(*operator.last); h}
375 382 end
376 383
377 384 # Returns a representation of the available filters for JSON serialization
378 385 def available_filters_as_json
379 386 json = {}
380 387 available_filters.each do |field, options|
381 388 options = options.slice(:type, :name, :values)
382 389 if options[:values] && values_for(field)
383 390 missing = Array(values_for(field)).select(&:present?) - options[:values].map(&:last)
384 391 if missing.any? && respond_to?(method = "find_#{field}_filter_values")
385 392 options[:values] += send(method, missing)
386 393 end
387 394 end
388 395 json[field] = options.stringify_keys
389 396 end
390 397 json
391 398 end
392 399
393 400 def all_projects
394 401 @all_projects ||= Project.visible.to_a
395 402 end
396 403
397 404 def all_projects_values
398 405 return @all_projects_values if @all_projects_values
399 406
400 407 values = []
401 408 Project.project_tree(all_projects) do |p, level|
402 409 prefix = (level > 0 ? ('--' * level + ' ') : '')
403 410 values << ["#{prefix}#{p.name}", p.id.to_s]
404 411 end
405 412 @all_projects_values = values
406 413 end
407 414
408 415 # Adds available filters
409 416 def initialize_available_filters
410 417 # implemented by sub-classes
411 418 end
412 419 protected :initialize_available_filters
413 420
414 421 # Adds an available filter
415 422 def add_available_filter(field, options)
416 423 @available_filters ||= ActiveSupport::OrderedHash.new
417 424 @available_filters[field] = options
418 425 @available_filters
419 426 end
420 427
421 428 # Removes an available filter
422 429 def delete_available_filter(field)
423 430 if @available_filters
424 431 @available_filters.delete(field)
425 432 end
426 433 end
427 434
428 435 # Return a hash of available filters
429 436 def available_filters
430 437 unless @available_filters
431 438 initialize_available_filters
432 439 @available_filters.each do |field, options|
433 440 options[:name] ||= l(options[:label] || "field_#{field}".gsub(/_id$/, ''))
434 441 end
435 442 end
436 443 @available_filters
437 444 end
438 445
439 446 def add_filter(field, operator, values=nil)
440 447 # values must be an array
441 448 return unless values.nil? || values.is_a?(Array)
442 449 # check if field is defined as an available filter
443 450 if available_filters.has_key? field
444 451 filter_options = available_filters[field]
445 452 filters[field] = {:operator => operator, :values => (values || [''])}
446 453 end
447 454 end
448 455
449 456 def add_short_filter(field, expression)
450 457 return unless expression && available_filters.has_key?(field)
451 458 field_type = available_filters[field][:type]
452 459 operators_by_filter_type[field_type].sort.reverse.detect do |operator|
453 460 next unless expression =~ /^#{Regexp.escape(operator)}(.*)$/
454 461 values = $1
455 462 add_filter field, operator, values.present? ? values.split('|') : ['']
456 463 end || add_filter(field, '=', expression.split('|'))
457 464 end
458 465
459 466 # Add multiple filters using +add_filter+
460 467 def add_filters(fields, operators, values)
461 468 if fields.is_a?(Array) && operators.is_a?(Hash) && (values.nil? || values.is_a?(Hash))
462 469 fields.each do |field|
463 470 add_filter(field, operators[field], values && values[field])
464 471 end
465 472 end
466 473 end
467 474
468 475 def has_filter?(field)
469 476 filters and filters[field]
470 477 end
471 478
472 479 def type_for(field)
473 480 available_filters[field][:type] if available_filters.has_key?(field)
474 481 end
475 482
476 483 def operator_for(field)
477 484 has_filter?(field) ? filters[field][:operator] : nil
478 485 end
479 486
480 487 def values_for(field)
481 488 has_filter?(field) ? filters[field][:values] : nil
482 489 end
483 490
484 491 def value_for(field, index=0)
485 492 (values_for(field) || [])[index]
486 493 end
487 494
488 495 def label_for(field)
489 496 label = available_filters[field][:name] if available_filters.has_key?(field)
490 497 label ||= queried_class.human_attribute_name(field, :default => field)
491 498 end
492 499
493 500 def self.add_available_column(column)
494 501 self.available_columns << (column) if column.is_a?(QueryColumn)
495 502 end
496 503
497 504 # Returns an array of columns that can be used to group the results
498 505 def groupable_columns
499 506 available_columns.select {|c| c.groupable}
500 507 end
501 508
502 509 # Returns a Hash of columns and the key for sorting
503 510 def sortable_columns
504 511 available_columns.inject({}) {|h, column|
505 512 h[column.name.to_s] = column.sortable
506 513 h
507 514 }
508 515 end
509 516
510 517 def columns
511 518 # preserve the column_names order
512 519 cols = (has_default_columns? ? default_columns_names : column_names).collect do |name|
513 520 available_columns.find { |col| col.name == name }
514 521 end.compact
515 522 available_columns.select(&:frozen?) | cols
516 523 end
517 524
518 525 def inline_columns
519 526 columns.select(&:inline?)
520 527 end
521 528
522 529 def block_columns
523 530 columns.reject(&:inline?)
524 531 end
525 532
526 533 def available_inline_columns
527 534 available_columns.select(&:inline?)
528 535 end
529 536
530 537 def available_block_columns
531 538 available_columns.reject(&:inline?)
532 539 end
533 540
534 541 def available_totalable_columns
535 542 available_columns.select(&:totalable)
536 543 end
537 544
538 545 def default_columns_names
539 546 []
540 547 end
541 548
542 549 def column_names=(names)
543 550 if names
544 551 names = names.select {|n| n.is_a?(Symbol) || !n.blank? }
545 552 names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }
546 553 # Set column_names to nil if default columns
547 554 if names == default_columns_names
548 555 names = nil
549 556 end
550 557 end
551 558 write_attribute(:column_names, names)
552 559 end
553 560
554 561 def has_column?(column)
555 562 column_names && column_names.include?(column.is_a?(QueryColumn) ? column.name : column)
556 563 end
557 564
558 565 def has_custom_field_column?
559 566 columns.any? {|column| column.is_a? QueryCustomFieldColumn}
560 567 end
561 568
562 569 def has_default_columns?
563 570 column_names.nil? || column_names.empty?
564 571 end
565 572
566 573 def totalable_columns
567 574 names = totalable_names
568 575 available_totalable_columns.select {|column| names.include?(column.name)}
569 576 end
570 577
571 578 def totalable_names=(names)
572 579 if names
573 580 names = names.select(&:present?).map {|n| n.is_a?(Symbol) ? n : n.to_sym}
574 581 end
575 582 options[:totalable_names] = names
576 583 end
577 584
578 585 def totalable_names
579 586 options[:totalable_names] || Setting.issue_list_default_totals.map(&:to_sym) || []
580 587 end
581 588
582 589 def sort_criteria=(arg)
583 590 c = []
584 591 if arg.is_a?(Hash)
585 592 arg = arg.keys.sort.collect {|k| arg[k]}
586 593 end
587 594 if arg
588 595 c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, (o == 'desc' || o == false) ? 'desc' : 'asc']}
589 596 end
590 597 write_attribute(:sort_criteria, c)
591 598 end
592 599
593 600 def sort_criteria
594 601 read_attribute(:sort_criteria) || []
595 602 end
596 603
597 604 def sort_criteria_key(arg)
598 605 sort_criteria && sort_criteria[arg] && sort_criteria[arg].first
599 606 end
600 607
601 608 def sort_criteria_order(arg)
602 609 sort_criteria && sort_criteria[arg] && sort_criteria[arg].last
603 610 end
604 611
605 612 def sort_criteria_order_for(key)
606 613 sort_criteria.detect {|k, order| key.to_s == k}.try(:last)
607 614 end
608 615
609 616 # Returns the SQL sort order that should be prepended for grouping
610 617 def group_by_sort_order
611 618 if column = group_by_column
612 619 order = (sort_criteria_order_for(column.name) || column.default_order).try(:upcase)
613 620 Array(column.sortable).map {|s| "#{s} #{order}"}
614 621 end
615 622 end
616 623
617 624 # Returns true if the query is a grouped query
618 625 def grouped?
619 626 !group_by_column.nil?
620 627 end
621 628
622 629 def group_by_column
623 630 groupable_columns.detect {|c| c.groupable && c.name.to_s == group_by}
624 631 end
625 632
626 633 def group_by_statement
627 634 group_by_column.try(:groupable)
628 635 end
629 636
630 637 def project_statement
631 638 project_clauses = []
632 639 if project && !project.descendants.active.empty?
633 640 if has_filter?("subproject_id")
634 641 case operator_for("subproject_id")
635 642 when '='
636 643 # include the selected subprojects
637 644 ids = [project.id] + values_for("subproject_id").each(&:to_i)
638 645 project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
639 646 when '!*'
640 647 # main project only
641 648 project_clauses << "#{Project.table_name}.id = %d" % project.id
642 649 else
643 650 # all subprojects
644 651 project_clauses << "#{Project.table_name}.lft >= #{project.lft} AND #{Project.table_name}.rgt <= #{project.rgt}"
645 652 end
646 653 elsif Setting.display_subprojects_issues?
647 654 project_clauses << "#{Project.table_name}.lft >= #{project.lft} AND #{Project.table_name}.rgt <= #{project.rgt}"
648 655 else
649 656 project_clauses << "#{Project.table_name}.id = %d" % project.id
650 657 end
651 658 elsif project
652 659 project_clauses << "#{Project.table_name}.id = %d" % project.id
653 660 end
654 661 project_clauses.any? ? project_clauses.join(' AND ') : nil
655 662 end
656 663
657 664 def statement
658 665 # filters clauses
659 666 filters_clauses = []
660 667 filters.each_key do |field|
661 668 next if field == "subproject_id"
662 669 v = values_for(field).clone
663 670 next unless v and !v.empty?
664 671 operator = operator_for(field)
665 672
666 673 # "me" value substitution
667 674 if %w(assigned_to_id author_id user_id watcher_id).include?(field)
668 675 if v.delete("me")
669 676 if User.current.logged?
670 677 v.push(User.current.id.to_s)
671 678 v += User.current.group_ids.map(&:to_s) if field == 'assigned_to_id'
672 679 else
673 680 v.push("0")
674 681 end
675 682 end
676 683 end
677 684
678 685 if field == 'project_id'
679 686 if v.delete('mine')
680 687 v += User.current.memberships.map(&:project_id).map(&:to_s)
681 688 end
682 689 end
683 690
684 691 if field =~ /cf_(\d+)$/
685 692 # custom field
686 693 filters_clauses << sql_for_custom_field(field, operator, v, $1)
687 694 elsif respond_to?("sql_for_#{field}_field")
688 695 # specific statement
689 696 filters_clauses << send("sql_for_#{field}_field", field, operator, v)
690 697 else
691 698 # regular field
692 699 filters_clauses << '(' + sql_for_field(field, operator, v, queried_table_name, field) + ')'
693 700 end
694 701 end if filters and valid?
695 702
696 703 if (c = group_by_column) && c.is_a?(QueryCustomFieldColumn)
697 704 # Excludes results for which the grouped custom field is not visible
698 705 filters_clauses << c.custom_field.visibility_by_project_condition
699 706 end
700 707
701 708 filters_clauses << project_statement
702 709 filters_clauses.reject!(&:blank?)
703 710
704 711 filters_clauses.any? ? filters_clauses.join(' AND ') : nil
705 712 end
706 713
707 714 # Returns the sum of values for the given column
708 715 def total_for(column)
709 716 total_with_scope(column, base_scope)
710 717 end
711 718
712 719 # Returns a hash of the sum of the given column for each group,
713 720 # or nil if the query is not grouped
714 721 def total_by_group_for(column)
715 722 grouped_query do |scope|
716 723 total_with_scope(column, scope)
717 724 end
718 725 end
719 726
720 727 def totals
721 728 totals = totalable_columns.map {|column| [column, total_for(column)]}
722 729 yield totals if block_given?
723 730 totals
724 731 end
725 732
726 733 def totals_by_group
727 734 totals = totalable_columns.map {|column| [column, total_by_group_for(column)]}
728 735 yield totals if block_given?
729 736 totals
730 737 end
731 738
732 739 private
733 740
734 741 def grouped_query(&block)
735 742 r = nil
736 743 if grouped?
737 744 begin
738 745 # Rails3 will raise an (unexpected) RecordNotFound if there's only a nil group value
739 746 r = yield base_group_scope
740 747 rescue ActiveRecord::RecordNotFound
741 748 r = {nil => yield(base_scope)}
742 749 end
743 750 c = group_by_column
744 751 if c.is_a?(QueryCustomFieldColumn)
745 752 r = r.keys.inject({}) {|h, k| h[c.custom_field.cast_value(k)] = r[k]; h}
746 753 end
747 754 end
748 755 r
749 756 rescue ::ActiveRecord::StatementInvalid => e
750 757 raise StatementInvalid.new(e.message)
751 758 end
752 759
753 760 def total_with_scope(column, scope)
754 761 unless column.is_a?(QueryColumn)
755 762 column = column.to_sym
756 763 column = available_totalable_columns.detect {|c| c.name == column}
757 764 end
758 765 if column.is_a?(QueryCustomFieldColumn)
759 766 custom_field = column.custom_field
760 767 send "total_for_custom_field", custom_field, scope
761 768 else
762 769 send "total_for_#{column.name}", scope
763 770 end
764 771 rescue ::ActiveRecord::StatementInvalid => e
765 772 raise StatementInvalid.new(e.message)
766 773 end
767 774
768 775 def base_scope
769 776 raise "unimplemented"
770 777 end
771 778
772 779 def base_group_scope
773 780 base_scope.
774 781 joins(joins_for_order_statement(group_by_statement)).
775 782 group(group_by_statement)
776 783 end
777 784
778 785 def total_for_custom_field(custom_field, scope, &block)
779 786 total = custom_field.format.total_for_scope(custom_field, scope)
780 787 total = map_total(total) {|t| custom_field.format.cast_total_value(custom_field, t)}
781 788 total
782 789 end
783 790
784 791 def map_total(total, &block)
785 792 if total.is_a?(Hash)
786 793 total.keys.each {|k| total[k] = yield total[k]}
787 794 else
788 795 total = yield total
789 796 end
790 797 total
791 798 end
792 799
793 800 def sql_for_custom_field(field, operator, value, custom_field_id)
794 801 db_table = CustomValue.table_name
795 802 db_field = 'value'
796 803 filter = @available_filters[field]
797 804 return nil unless filter
798 805 if filter[:field].format.target_class && filter[:field].format.target_class <= User
799 806 if value.delete('me')
800 807 value.push User.current.id.to_s
801 808 end
802 809 end
803 810 not_in = nil
804 811 if operator == '!'
805 812 # Makes ! operator work for custom fields with multiple values
806 813 operator = '='
807 814 not_in = 'NOT'
808 815 end
809 816 customized_key = "id"
810 817 customized_class = queried_class
811 818 if field =~ /^(.+)\.cf_/
812 819 assoc = $1
813 820 customized_key = "#{assoc}_id"
814 821 customized_class = queried_class.reflect_on_association(assoc.to_sym).klass.base_class rescue nil
815 822 raise "Unknown #{queried_class.name} association #{assoc}" unless customized_class
816 823 end
817 824 where = sql_for_field(field, operator, value, db_table, db_field, true)
818 825 if operator =~ /[<>]/
819 826 where = "(#{where}) AND #{db_table}.#{db_field} <> ''"
820 827 end
821 828 "#{queried_table_name}.#{customized_key} #{not_in} IN (" +
822 829 "SELECT #{customized_class.table_name}.id FROM #{customized_class.table_name}" +
823 830 " LEFT OUTER JOIN #{db_table} ON #{db_table}.customized_type='#{customized_class}' AND #{db_table}.customized_id=#{customized_class.table_name}.id AND #{db_table}.custom_field_id=#{custom_field_id}" +
824 831 " WHERE (#{where}) AND (#{filter[:field].visibility_by_project_condition}))"
825 832 end
826 833
827 834 # Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+
828 835 def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)
829 836 sql = ''
830 837 case operator
831 838 when "="
832 839 if value.any?
833 840 case type_for(field)
834 841 when :date, :date_past
835 842 sql = date_clause(db_table, db_field, parse_date(value.first), parse_date(value.first), is_custom_filter)
836 843 when :integer
837 844 int_values = value.first.to_s.scan(/[+-]?\d+/).map(&:to_i).join(",")
838 845 if int_values.present?
839 846 if is_custom_filter
840 847 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) IN (#{int_values}))"
841 848 else
842 849 sql = "#{db_table}.#{db_field} IN (#{int_values})"
843 850 end
844 851 else
845 852 sql = "1=0"
846 853 end
847 854 when :float
848 855 if is_custom_filter
849 856 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5})"
850 857 else
851 858 sql = "#{db_table}.#{db_field} BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5}"
852 859 end
853 860 else
854 861 sql = queried_class.send(:sanitize_sql_for_conditions, ["#{db_table}.#{db_field} IN (?)", value])
855 862 end
856 863 else
857 864 # IN an empty set
858 865 sql = "1=0"
859 866 end
860 867 when "!"
861 868 if value.any?
862 869 sql = queried_class.send(:sanitize_sql_for_conditions, ["(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (?))", value])
863 870 else
864 871 # NOT IN an empty set
865 872 sql = "1=1"
866 873 end
867 874 when "!*"
868 875 sql = "#{db_table}.#{db_field} IS NULL"
869 876 sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter
870 877 when "*"
871 878 sql = "#{db_table}.#{db_field} IS NOT NULL"
872 879 sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
873 880 when ">="
874 881 if [:date, :date_past].include?(type_for(field))
875 882 sql = date_clause(db_table, db_field, parse_date(value.first), nil, is_custom_filter)
876 883 else
877 884 if is_custom_filter
878 885 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) >= #{value.first.to_f})"
879 886 else
880 887 sql = "#{db_table}.#{db_field} >= #{value.first.to_f}"
881 888 end
882 889 end
883 890 when "<="
884 891 if [:date, :date_past].include?(type_for(field))
885 892 sql = date_clause(db_table, db_field, nil, parse_date(value.first), is_custom_filter)
886 893 else
887 894 if is_custom_filter
888 895 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) <= #{value.first.to_f})"
889 896 else
890 897 sql = "#{db_table}.#{db_field} <= #{value.first.to_f}"
891 898 end
892 899 end
893 900 when "><"
894 901 if [:date, :date_past].include?(type_for(field))
895 902 sql = date_clause(db_table, db_field, parse_date(value[0]), parse_date(value[1]), is_custom_filter)
896 903 else
897 904 if is_custom_filter
898 905 sql = "(#{db_table}.#{db_field} <> '' AND CAST(CASE #{db_table}.#{db_field} WHEN '' THEN '0' ELSE #{db_table}.#{db_field} END AS decimal(30,3)) BETWEEN #{value[0].to_f} AND #{value[1].to_f})"
899 906 else
900 907 sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
901 908 end
902 909 end
903 910 when "o"
904 911 sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{self.class.connection.quoted_false})" if field == "status_id"
905 912 when "c"
906 913 sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{self.class.connection.quoted_true})" if field == "status_id"
907 914 when "><t-"
908 915 # between today - n days and today
909 916 sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0, is_custom_filter)
910 917 when ">t-"
911 918 # >= today - n days
912 919 sql = relative_date_clause(db_table, db_field, - value.first.to_i, nil, is_custom_filter)
913 920 when "<t-"
914 921 # <= today - n days
915 922 sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i, is_custom_filter)
916 923 when "t-"
917 924 # = n days in past
918 925 sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i, is_custom_filter)
919 926 when "><t+"
920 927 # between today and today + n days
921 928 sql = relative_date_clause(db_table, db_field, 0, value.first.to_i, is_custom_filter)
922 929 when ">t+"
923 930 # >= today + n days
924 931 sql = relative_date_clause(db_table, db_field, value.first.to_i, nil, is_custom_filter)
925 932 when "<t+"
926 933 # <= today + n days
927 934 sql = relative_date_clause(db_table, db_field, nil, value.first.to_i, is_custom_filter)
928 935 when "t+"
929 936 # = today + n days
930 937 sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i, is_custom_filter)
931 938 when "t"
932 939 # = today
933 940 sql = relative_date_clause(db_table, db_field, 0, 0, is_custom_filter)
934 941 when "ld"
935 942 # = yesterday
936 943 sql = relative_date_clause(db_table, db_field, -1, -1, is_custom_filter)
937 944 when "w"
938 945 # = this week
939 946 first_day_of_week = l(:general_first_day_of_week).to_i
940 947 day_of_week = User.current.today.cwday
941 948 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
942 949 sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6, is_custom_filter)
943 950 when "lw"
944 951 # = last week
945 952 first_day_of_week = l(:general_first_day_of_week).to_i
946 953 day_of_week = User.current.today.cwday
947 954 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
948 955 sql = relative_date_clause(db_table, db_field, - days_ago - 7, - days_ago - 1, is_custom_filter)
949 956 when "l2w"
950 957 # = last 2 weeks
951 958 first_day_of_week = l(:general_first_day_of_week).to_i
952 959 day_of_week = User.current.today.cwday
953 960 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
954 961 sql = relative_date_clause(db_table, db_field, - days_ago - 14, - days_ago - 1, is_custom_filter)
955 962 when "m"
956 963 # = this month
957 964 date = User.current.today
958 965 sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month, is_custom_filter)
959 966 when "lm"
960 967 # = last month
961 968 date = User.current.today.prev_month
962 969 sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month, is_custom_filter)
963 970 when "y"
964 971 # = this year
965 972 date = User.current.today
966 973 sql = date_clause(db_table, db_field, date.beginning_of_year, date.end_of_year, is_custom_filter)
967 974 when "~"
968 975 sql = sql_contains("#{db_table}.#{db_field}", value.first)
969 976 when "!~"
970 977 sql = sql_contains("#{db_table}.#{db_field}", value.first, false)
971 978 else
972 979 raise "Unknown query operator #{operator}"
973 980 end
974 981
975 982 return sql
976 983 end
977 984
978 985 # Returns a SQL LIKE statement with wildcards
979 986 def sql_contains(db_field, value, match=true)
980 987 queried_class.send :sanitize_sql_for_conditions,
981 988 [Redmine::Database.like(db_field, '?', :match => match), "%#{value}%"]
982 989 end
983 990
984 991 # Adds a filter for the given custom field
985 992 def add_custom_field_filter(field, assoc=nil)
986 993 options = field.query_filter_options(self)
987 994 if field.format.target_class && field.format.target_class <= User
988 995 if options[:values].is_a?(Array) && User.current.logged?
989 996 options[:values].unshift ["<< #{l(:label_me)} >>", "me"]
990 997 end
991 998 end
992 999
993 1000 filter_id = "cf_#{field.id}"
994 1001 filter_name = field.name
995 1002 if assoc.present?
996 1003 filter_id = "#{assoc}.#{filter_id}"
997 1004 filter_name = l("label_attribute_of_#{assoc}", :name => filter_name)
998 1005 end
999 1006 add_available_filter filter_id, options.merge({
1000 1007 :name => filter_name,
1001 1008 :field => field
1002 1009 })
1003 1010 end
1004 1011
1005 1012 # Adds filters for the given custom fields scope
1006 1013 def add_custom_fields_filters(scope, assoc=nil)
1007 1014 scope.visible.where(:is_filter => true).sorted.each do |field|
1008 1015 add_custom_field_filter(field, assoc)
1009 1016 end
1010 1017 end
1011 1018
1012 1019 # Adds filters for the given associations custom fields
1013 1020 def add_associations_custom_fields_filters(*associations)
1014 1021 fields_by_class = CustomField.visible.where(:is_filter => true).group_by(&:class)
1015 1022 associations.each do |assoc|
1016 1023 association_klass = queried_class.reflect_on_association(assoc).klass
1017 1024 fields_by_class.each do |field_class, fields|
1018 1025 if field_class.customized_class <= association_klass
1019 1026 fields.sort.each do |field|
1020 1027 add_custom_field_filter(field, assoc)
1021 1028 end
1022 1029 end
1023 1030 end
1024 1031 end
1025 1032 end
1026 1033
1027 1034 def quoted_time(time, is_custom_filter)
1028 1035 if is_custom_filter
1029 1036 # Custom field values are stored as strings in the DB
1030 1037 # using this format that does not depend on DB date representation
1031 1038 time.strftime("%Y-%m-%d %H:%M:%S")
1032 1039 else
1033 1040 self.class.connection.quoted_date(time)
1034 1041 end
1035 1042 end
1036 1043
1037 1044 def date_for_user_time_zone(y, m, d)
1038 1045 if tz = User.current.time_zone
1039 1046 tz.local y, m, d
1040 1047 else
1041 1048 Time.local y, m, d
1042 1049 end
1043 1050 end
1044 1051
1045 1052 # Returns a SQL clause for a date or datetime field.
1046 1053 def date_clause(table, field, from, to, is_custom_filter)
1047 1054 s = []
1048 1055 if from
1049 1056 if from.is_a?(Date)
1050 1057 from = date_for_user_time_zone(from.year, from.month, from.day).yesterday.end_of_day
1051 1058 else
1052 1059 from = from - 1 # second
1053 1060 end
1054 1061 if self.class.default_timezone == :utc
1055 1062 from = from.utc
1056 1063 end
1057 1064 s << ("#{table}.#{field} > '%s'" % [quoted_time(from, is_custom_filter)])
1058 1065 end
1059 1066 if to
1060 1067 if to.is_a?(Date)
1061 1068 to = date_for_user_time_zone(to.year, to.month, to.day).end_of_day
1062 1069 end
1063 1070 if self.class.default_timezone == :utc
1064 1071 to = to.utc
1065 1072 end
1066 1073 s << ("#{table}.#{field} <= '%s'" % [quoted_time(to, is_custom_filter)])
1067 1074 end
1068 1075 s.join(' AND ')
1069 1076 end
1070 1077
1071 1078 # Returns a SQL clause for a date or datetime field using relative dates.
1072 1079 def relative_date_clause(table, field, days_from, days_to, is_custom_filter)
1073 1080 date_clause(table, field, (days_from ? User.current.today + days_from : nil), (days_to ? User.current.today + days_to : nil), is_custom_filter)
1074 1081 end
1075 1082
1076 1083 # Returns a Date or Time from the given filter value
1077 1084 def parse_date(arg)
1078 1085 if arg.to_s =~ /\A\d{4}-\d{2}-\d{2}T/
1079 1086 Time.parse(arg) rescue nil
1080 1087 else
1081 1088 Date.parse(arg) rescue nil
1082 1089 end
1083 1090 end
1084 1091
1085 1092 # Additional joins required for the given sort options
1086 1093 def joins_for_order_statement(order_options)
1087 1094 joins = []
1088 1095
1089 1096 if order_options
1090 1097 if order_options.include?('authors')
1091 1098 joins << "LEFT OUTER JOIN #{User.table_name} authors ON authors.id = #{queried_table_name}.author_id"
1092 1099 end
1093 1100 order_options.scan(/cf_\d+/).uniq.each do |name|
1094 1101 column = available_columns.detect {|c| c.name.to_s == name}
1095 1102 join = column && column.custom_field.join_for_order_statement
1096 1103 if join
1097 1104 joins << join
1098 1105 end
1099 1106 end
1100 1107 end
1101 1108
1102 1109 joins.any? ? joins.join(' ') : nil
1103 1110 end
1104 1111 end
General Comments 0
You need to be logged in to leave comments. Login now