##// END OF EJS Templates
jro_apps » redmine
RSS
Moved build_query_from_params helper to Query#build_from_params....
Jean-Philippe Lang -
r10739:10998c9bae31
parent child
Show More
Side by Side Unified
Context file:
r10739:10998c9bae31 Loading diff...:
Show file before | Show file after | Hide comments
@@ -1,107 +1,107
1 1 # Redmine - project management software
2 2 # Copyright (C) 2006-2012 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 QueriesController < ApplicationController
19 19 menu_item :issues
20 20 before_filter :find_query, :except => [:new, :create, :index]
21 21 before_filter :find_optional_project, :only => [:new, :create]
22 22
23 23 accept_api_auth :index
24 24
25 25 include QueriesHelper
26 26
27 27 def index
28 28 case params[:format]
29 29 when 'xml', 'json'
30 30 @offset, @limit = api_offset_and_limit
31 31 else
32 32 @limit = per_page_option
33 33 end
34 34
35 35 @query_count = IssueQuery.visible.count
36 36 @query_pages = Paginator.new self, @query_count, @limit, params['page']
37 37 @queries = IssueQuery.visible.all(:limit => @limit, :offset => @offset, :order => "#{Query.table_name}.name")
38 38
39 39 respond_to do |format|
40 40 format.html { render :nothing => true }
41 41 format.api
42 42 end
43 43 end
44 44
45 45 def new
46 46 @query = IssueQuery.new
47 47 @query.user = User.current
48 48 @query.project = @project
49 49 @query.is_public = false unless User.current.allowed_to?(:manage_public_queries, @project) || User.current.admin?
50 build_query_from_params
50 @query.build_from_params(params)
51 51 end
52 52
53 53 def create
54 54 @query = IssueQuery.new(params[:query])
55 55 @query.user = User.current
56 56 @query.project = params[:query_is_for_all] ? nil : @project
57 57 @query.is_public = false unless User.current.allowed_to?(:manage_public_queries, @project) || User.current.admin?
58 build_query_from_params
58 @query.build_from_params(params)
59 59 @query.column_names = nil if params[:default_columns]
60 60
61 61 if @query.save
62 62 flash[:notice] = l(:notice_successful_create)
63 63 redirect_to :controller => 'issues', :action => 'index', :project_id => @project, :query_id => @query
64 64 else
65 65 render :action => 'new', :layout => !request.xhr?
66 66 end
67 67 end
68 68
69 69 def edit
70 70 end
71 71
72 72 def update
73 73 @query.attributes = params[:query]
74 74 @query.project = nil if params[:query_is_for_all]
75 75 @query.is_public = false unless User.current.allowed_to?(:manage_public_queries, @project) || User.current.admin?
76 build_query_from_params
76 @query.build_from_params(params)
77 77 @query.column_names = nil if params[:default_columns]
78 78
79 79 if @query.save
80 80 flash[:notice] = l(:notice_successful_update)
81 81 redirect_to :controller => 'issues', :action => 'index', :project_id => @project, :query_id => @query
82 82 else
83 83 render :action => 'edit'
84 84 end
85 85 end
86 86
87 87 def destroy
88 88 @query.destroy
89 89 redirect_to :controller => 'issues', :action => 'index', :project_id => @project, :set_filter => 1
90 90 end
91 91
92 92 private
93 93 def find_query
94 94 @query = IssueQuery.find(params[:id])
95 95 @project = @query.project
96 96 render_403 unless @query.editable_by?(User.current)
97 97 rescue ActiveRecord::RecordNotFound
98 98 render_404
99 99 end
100 100
101 101 def find_optional_project
102 102 @project = Project.find(params[:project_id]) if params[:project_id]
103 103 render_403 unless User.current.allowed_to?(:save_queries, @project, :global => true)
104 104 rescue ActiveRecord::RecordNotFound
105 105 render_404
106 106 end
107 107 end
Show file before | Show file after | Hide comments
@@ -1,173 +1,160
1 1 # encoding: utf-8
2 2 #
3 3 # Redmine - project management software
4 4 # Copyright (C) 2006-2012 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 def filters_options_for_select(query)
22 22 options_for_select(filters_options(query))
23 23 end
24 24
25 25 def filters_options(query)
26 26 options = [[]]
27 27 sorted_options = query.available_filters.sort do |a, b|
28 28 ord = 0
29 29 if !(a[1][:order] == 20 && b[1][:order] == 20)
30 30 ord = a[1][:order] <=> b[1][:order]
31 31 else
32 32 cn = (CustomField::CUSTOM_FIELDS_NAMES.index(a[1][:field].class.name) <=>
33 33 CustomField::CUSTOM_FIELDS_NAMES.index(b[1][:field].class.name))
34 34 if cn != 0
35 35 ord = cn
36 36 else
37 37 f = (a[1][:field] <=> b[1][:field])
38 38 if f != 0
39 39 ord = f
40 40 else
41 41 # assigned_to or author
42 42 ord = (a[0] <=> b[0])
43 43 end
44 44 end
45 45 end
46 46 ord
47 47 end
48 48 options += sorted_options.map do |field, field_options|
49 49 [field_options[:name], field]
50 50 end
51 51 end
52 52
53 53 def available_block_columns_tags(query)
54 54 tags = ''.html_safe
55 55 query.available_block_columns.each do |column|
56 56 tags << content_tag('label', check_box_tag('c[]', column.name.to_s, query.has_column?(column)) + " #{column.caption}", :class => 'inline')
57 57 end
58 58 tags
59 59 end
60 60
61 61 def column_header(column)
62 62 column.sortable ? sort_header_tag(column.name.to_s, :caption => column.caption,
63 63 :default_order => column.default_order) :
64 64 content_tag('th', h(column.caption))
65 65 end
66 66
67 67 def column_content(column, issue)
68 68 value = column.value(issue)
69 69 if value.is_a?(Array)
70 70 value.collect {|v| column_value(column, issue, v)}.compact.join(', ').html_safe
71 71 else
72 72 column_value(column, issue, value)
73 73 end
74 74 end
75 75
76 76 def column_value(column, issue, value)
77 77 case value.class.name
78 78 when 'String'
79 79 if column.name == :subject
80 80 link_to(h(value), :controller => 'issues', :action => 'show', :id => issue)
81 81 elsif column.name == :description
82 82 issue.description? ? content_tag('div', textilizable(issue, :description), :class => "wiki") : ''
83 83 else
84 84 h(value)
85 85 end
86 86 when 'Time'
87 87 format_time(value)
88 88 when 'Date'
89 89 format_date(value)
90 90 when 'Fixnum', 'Float'
91 91 if column.name == :done_ratio
92 92 progress_bar(value, :width => '80px')
93 93 elsif column.name == :spent_hours
94 94 sprintf "%.2f", value
95 95 else
96 96 h(value.to_s)
97 97 end
98 98 when 'User'
99 99 link_to_user value
100 100 when 'Project'
101 101 link_to_project value
102 102 when 'Version'
103 103 link_to(h(value), :controller => 'versions', :action => 'show', :id => value)
104 104 when 'TrueClass'
105 105 l(:general_text_Yes)
106 106 when 'FalseClass'
107 107 l(:general_text_No)
108 108 when 'Issue'
109 109 link_to_issue(value, :subject => false)
110 110 when 'IssueRelation'
111 111 other = value.other_issue(issue)
112 112 content_tag('span',
113 113 (l(value.label_for(issue)) + " " + link_to_issue(other, :subject => false, :tracker => false)).html_safe,
114 114 :class => value.css_classes_for(issue))
115 115 else
116 116 h(value)
117 117 end
118 118 end
119 119
120 120 # Retrieve query from session or build a new query
121 121 def retrieve_query
122 122 if !params[:query_id].blank?
123 123 cond = "project_id IS NULL"
124 124 cond << " OR project_id = #{@project.id}" if @project
125 125 @query = IssueQuery.find(params[:query_id], :conditions => cond)
126 126 raise ::Unauthorized unless @query.visible?
127 127 @query.project = @project
128 128 session[:query] = {:id => @query.id, :project_id => @query.project_id}
129 129 sort_clear
130 130 elsif api_request? || params[:set_filter] || session[:query].nil? || session[:query][:project_id] != (@project ? @project.id : nil)
131 131 # Give it a name, required to be valid
132 132 @query = IssueQuery.new(:name => "_")
133 133 @query.project = @project
134 build_query_from_params
134 @query.build_from_params(params)
135 135 session[:query] = {:project_id => @query.project_id, :filters => @query.filters, :group_by => @query.group_by, :column_names => @query.column_names}
136 136 else
137 137 # retrieve from session
138 138 @query = IssueQuery.find_by_id(session[:query][:id]) if session[:query][:id]
139 139 @query ||= IssueQuery.new(:name => "_", :filters => session[:query][:filters], :group_by => session[:query][:group_by], :column_names => session[:query][:column_names])
140 140 @query.project = @project
141 141 end
142 142 end
143 143
144 144 def retrieve_query_from_session
145 145 if session[:query]
146 146 if session[:query][:id]
147 147 @query = IssueQuery.find_by_id(session[:query][:id])
148 148 return unless @query
149 149 else
150 150 @query = IssueQuery.new(:name => "_", :filters => session[:query][:filters], :group_by => session[:query][:group_by], :column_names => session[:query][:column_names])
151 151 end
152 152 if session[:query].has_key?(:project_id)
153 153 @query.project_id = session[:query][:project_id]
154 154 else
155 155 @query.project = @project
156 156 end
157 157 @query
158 158 end
159 159 end
160
161 def build_query_from_params
162 if params[:fields] || params[:f]
163 @query.filters = {}
164 @query.add_filters(params[:fields] || params[:f], params[:operators] || params[:op], params[:values] || params[:v])
165 else
166 @query.available_filters.keys.each do |field|
167 @query.add_short_filter(field, params[field]) if params[field]
168 end
169 end
170 @query.group_by = params[:group_by] || (params[:query] && params[:query][:group_by])
171 @query.column_names = params[:c] || (params[:query] && params[:query][:column_names])
172 end
173 160 end
Show file before | Show file after | Hide comments
@@ -1,716 +1,731
1 1 # Redmine - project management software
2 2 # Copyright (C) 2006-2012 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, :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.default_order = options[:default_order]
30 30 @inline = options.key?(:inline) ? options[:inline] : true
31 31 @caption_key = options[:caption] || "field_#{name}"
32 32 end
33 33
34 34 def caption
35 35 l(@caption_key)
36 36 end
37 37
38 38 # Returns true if the column is sortable, otherwise false
39 39 def sortable?
40 40 !@sortable.nil?
41 41 end
42 42
43 43 def sortable
44 44 @sortable.is_a?(Proc) ? @sortable.call : @sortable
45 45 end
46 46
47 47 def inline?
48 48 @inline
49 49 end
50 50
51 51 def value(object)
52 52 object.send name
53 53 end
54 54
55 55 def css_classes
56 56 name
57 57 end
58 58 end
59 59
60 60 class QueryCustomFieldColumn < QueryColumn
61 61
62 62 def initialize(custom_field)
63 63 self.name = "cf_#{custom_field.id}".to_sym
64 64 self.sortable = custom_field.order_statement || false
65 65 self.groupable = custom_field.group_statement || false
66 66 @inline = true
67 67 @cf = custom_field
68 68 end
69 69
70 70 def caption
71 71 @cf.name
72 72 end
73 73
74 74 def custom_field
75 75 @cf
76 76 end
77 77
78 78 def value(object)
79 79 cv = object.custom_values.select {|v| v.custom_field_id == @cf.id}.collect {|v| @cf.cast_value(v.value)}
80 80 cv.size > 1 ? cv.sort {|a,b| a.to_s <=> b.to_s} : cv.first
81 81 end
82 82
83 83 def css_classes
84 84 @css_classes ||= "#{name} #{@cf.field_format}"
85 85 end
86 86 end
87 87
88 88 class Query < ActiveRecord::Base
89 89 class StatementInvalid < ::ActiveRecord::StatementInvalid
90 90 end
91 91
92 92 belongs_to :project
93 93 belongs_to :user
94 94 serialize :filters
95 95 serialize :column_names
96 96 serialize :sort_criteria, Array
97 97
98 98 attr_protected :project_id, :user_id
99 99
100 100 validates_presence_of :name
101 101 validates_length_of :name, :maximum => 255
102 102 validate :validate_query_filters
103 103
104 104 class_attribute :operators
105 105 self.operators = {
106 106 "=" => :label_equals,
107 107 "!" => :label_not_equals,
108 108 "o" => :label_open_issues,
109 109 "c" => :label_closed_issues,
110 110 "!*" => :label_none,
111 111 "*" => :label_any,
112 112 ">=" => :label_greater_or_equal,
113 113 "<=" => :label_less_or_equal,
114 114 "><" => :label_between,
115 115 "<t+" => :label_in_less_than,
116 116 ">t+" => :label_in_more_than,
117 117 "><t+"=> :label_in_the_next_days,
118 118 "t+" => :label_in,
119 119 "t" => :label_today,
120 120 "w" => :label_this_week,
121 121 ">t-" => :label_less_than_ago,
122 122 "<t-" => :label_more_than_ago,
123 123 "><t-"=> :label_in_the_past_days,
124 124 "t-" => :label_ago,
125 125 "~" => :label_contains,
126 126 "!~" => :label_not_contains,
127 127 "=p" => :label_any_issues_in_project,
128 128 "=!p" => :label_any_issues_not_in_project,
129 129 "!p" => :label_no_issues_in_project
130 130 }
131 131
132 132 class_attribute :operators_by_filter_type
133 133 self.operators_by_filter_type = {
134 134 :list => [ "=", "!" ],
135 135 :list_status => [ "o", "=", "!", "c", "*" ],
136 136 :list_optional => [ "=", "!", "!*", "*" ],
137 137 :list_subprojects => [ "*", "!*", "=" ],
138 138 :date => [ "=", ">=", "<=", "><", "<t+", ">t+", "><t+", "t+", "t", "w", ">t-", "<t-", "><t-", "t-", "!*", "*" ],
139 139 :date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "><t-", "t-", "t", "w", "!*", "*" ],
140 140 :string => [ "=", "~", "!", "!~", "!*", "*" ],
141 141 :text => [ "~", "!~", "!*", "*" ],
142 142 :integer => [ "=", ">=", "<=", "><", "!*", "*" ],
143 143 :float => [ "=", ">=", "<=", "><", "!*", "*" ],
144 144 :relation => ["=", "=p", "=!p", "!p", "!*", "*"]
145 145 }
146 146
147 147 class_attribute :available_columns
148 148 self.available_columns = []
149 149
150 150 class_attribute :queried_class
151 151
152 152 def queried_table_name
153 153 @queried_table_name ||= self.class.queried_class.table_name
154 154 end
155 155
156 156 def initialize(attributes=nil, *args)
157 157 super attributes
158 158 @is_for_all = project.nil?
159 159 end
160 160
161 # Builds the query from the given params
162 def build_from_params(params)
163 if params[:fields] || params[:f]
164 self.filters = {}
165 add_filters(params[:fields] || params[:f], params[:operators] || params[:op], params[:values] || params[:v])
166 else
167 available_filters.keys.each do |field|
168 add_short_filter(field, params[field]) if params[field]
169 end
170 end
171 self.group_by = params[:group_by] || (params[:query] && params[:query][:group_by])
172 self.column_names = params[:c] || (params[:query] && params[:query][:column_names])
173 self
174 end
175
161 176 def validate_query_filters
162 177 filters.each_key do |field|
163 178 if values_for(field)
164 179 case type_for(field)
165 180 when :integer
166 181 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^[+-]?\d+$/) }
167 182 when :float
168 183 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^[+-]?\d+(\.\d*)?$/) }
169 184 when :date, :date_past
170 185 case operator_for(field)
171 186 when "=", ">=", "<=", "><"
172 187 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && (!v.match(/^\d{4}-\d{2}-\d{2}$/) || (Date.parse(v) rescue nil).nil?) }
173 188 when ">t-", "<t-", "t-", ">t+", "<t+", "t+", "><t+", "><t-"
174 189 add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^\d+$/) }
175 190 end
176 191 end
177 192 end
178 193
179 194 add_filter_error(field, :blank) unless
180 195 # filter requires one or more values
181 196 (values_for(field) and !values_for(field).first.blank?) or
182 197 # filter doesn't require any value
183 198 ["o", "c", "!*", "*", "t", "w"].include? operator_for(field)
184 199 end if filters
185 200 end
186 201
187 202 def add_filter_error(field, message)
188 203 m = label_for(field) + " " + l(message, :scope => 'activerecord.errors.messages')
189 204 errors.add(:base, m)
190 205 end
191 206
192 207 def editable_by?(user)
193 208 return false unless user
194 209 # Admin can edit them all and regular users can edit their private queries
195 210 return true if user.admin? || (!is_public && self.user_id == user.id)
196 211 # Members can not edit public queries that are for all project (only admin is allowed to)
197 212 is_public && !@is_for_all && user.allowed_to?(:manage_public_queries, project)
198 213 end
199 214
200 215 def trackers
201 216 @trackers ||= project.nil? ? Tracker.sorted.all : project.rolled_up_trackers
202 217 end
203 218
204 219 # Returns a hash of localized labels for all filter operators
205 220 def self.operators_labels
206 221 operators.inject({}) {|h, operator| h[operator.first] = l(operator.last); h}
207 222 end
208 223
209 224 # Returns a representation of the available filters for JSON serialization
210 225 def available_filters_as_json
211 226 json = {}
212 227 available_filters.each do |field, options|
213 228 json[field] = options.slice(:type, :name, :values).stringify_keys
214 229 end
215 230 json
216 231 end
217 232
218 233 def all_projects
219 234 @all_projects ||= Project.visible.all
220 235 end
221 236
222 237 def all_projects_values
223 238 return @all_projects_values if @all_projects_values
224 239
225 240 values = []
226 241 Project.project_tree(all_projects) do |p, level|
227 242 prefix = (level > 0 ? ('--' * level + ' ') : '')
228 243 values << ["#{prefix}#{p.name}", p.id.to_s]
229 244 end
230 245 @all_projects_values = values
231 246 end
232 247
233 248 def add_filter(field, operator, values)
234 249 # values must be an array
235 250 return unless values.nil? || values.is_a?(Array)
236 251 # check if field is defined as an available filter
237 252 if available_filters.has_key? field
238 253 filter_options = available_filters[field]
239 254 filters[field] = {:operator => operator, :values => (values || [''])}
240 255 end
241 256 end
242 257
243 258 def add_short_filter(field, expression)
244 259 return unless expression && available_filters.has_key?(field)
245 260 field_type = available_filters[field][:type]
246 261 operators_by_filter_type[field_type].sort.reverse.detect do |operator|
247 262 next unless expression =~ /^#{Regexp.escape(operator)}(.*)$/
248 263 add_filter field, operator, $1.present? ? $1.split('|') : ['']
249 264 end || add_filter(field, '=', expression.split('|'))
250 265 end
251 266
252 267 # Add multiple filters using +add_filter+
253 268 def add_filters(fields, operators, values)
254 269 if fields.is_a?(Array) && operators.is_a?(Hash) && (values.nil? || values.is_a?(Hash))
255 270 fields.each do |field|
256 271 add_filter(field, operators[field], values && values[field])
257 272 end
258 273 end
259 274 end
260 275
261 276 def has_filter?(field)
262 277 filters and filters[field]
263 278 end
264 279
265 280 def type_for(field)
266 281 available_filters[field][:type] if available_filters.has_key?(field)
267 282 end
268 283
269 284 def operator_for(field)
270 285 has_filter?(field) ? filters[field][:operator] : nil
271 286 end
272 287
273 288 def values_for(field)
274 289 has_filter?(field) ? filters[field][:values] : nil
275 290 end
276 291
277 292 def value_for(field, index=0)
278 293 (values_for(field) || [])[index]
279 294 end
280 295
281 296 def label_for(field)
282 297 label = available_filters[field][:name] if available_filters.has_key?(field)
283 298 label ||= l("field_#{field.to_s.gsub(/_id$/, '')}", :default => field)
284 299 end
285 300
286 301 def self.add_available_column(column)
287 302 self.available_columns << (column) if column.is_a?(QueryColumn)
288 303 end
289 304
290 305 # Returns an array of columns that can be used to group the results
291 306 def groupable_columns
292 307 available_columns.select {|c| c.groupable}
293 308 end
294 309
295 310 # Returns a Hash of columns and the key for sorting
296 311 def sortable_columns
297 312 available_columns.inject({}) {|h, column|
298 313 h[column.name.to_s] = column.sortable
299 314 h
300 315 }
301 316 end
302 317
303 318 def columns
304 319 # preserve the column_names order
305 320 (has_default_columns? ? default_columns_names : column_names).collect do |name|
306 321 available_columns.find { |col| col.name == name }
307 322 end.compact
308 323 end
309 324
310 325 def inline_columns
311 326 columns.select(&:inline?)
312 327 end
313 328
314 329 def block_columns
315 330 columns.reject(&:inline?)
316 331 end
317 332
318 333 def available_inline_columns
319 334 available_columns.select(&:inline?)
320 335 end
321 336
322 337 def available_block_columns
323 338 available_columns.reject(&:inline?)
324 339 end
325 340
326 341 def default_columns_names
327 342 []
328 343 end
329 344
330 345 def column_names=(names)
331 346 if names
332 347 names = names.select {|n| n.is_a?(Symbol) || !n.blank? }
333 348 names = names.collect {|n| n.is_a?(Symbol) ? n : n.to_sym }
334 349 # Set column_names to nil if default columns
335 350 if names == default_columns_names
336 351 names = nil
337 352 end
338 353 end
339 354 write_attribute(:column_names, names)
340 355 end
341 356
342 357 def has_column?(column)
343 358 column_names && column_names.include?(column.is_a?(QueryColumn) ? column.name : column)
344 359 end
345 360
346 361 def has_default_columns?
347 362 column_names.nil? || column_names.empty?
348 363 end
349 364
350 365 def sort_criteria=(arg)
351 366 c = []
352 367 if arg.is_a?(Hash)
353 368 arg = arg.keys.sort.collect {|k| arg[k]}
354 369 end
355 370 c = arg.select {|k,o| !k.to_s.blank?}.slice(0,3).collect {|k,o| [k.to_s, (o == 'desc' || o == false) ? 'desc' : 'asc']}
356 371 write_attribute(:sort_criteria, c)
357 372 end
358 373
359 374 def sort_criteria
360 375 read_attribute(:sort_criteria) || []
361 376 end
362 377
363 378 def sort_criteria_key(arg)
364 379 sort_criteria && sort_criteria[arg] && sort_criteria[arg].first
365 380 end
366 381
367 382 def sort_criteria_order(arg)
368 383 sort_criteria && sort_criteria[arg] && sort_criteria[arg].last
369 384 end
370 385
371 386 def sort_criteria_order_for(key)
372 387 sort_criteria.detect {|k, order| key.to_s == k}.try(:last)
373 388 end
374 389
375 390 # Returns the SQL sort order that should be prepended for grouping
376 391 def group_by_sort_order
377 392 if grouped? && (column = group_by_column)
378 393 order = sort_criteria_order_for(column.name) || column.default_order
379 394 column.sortable.is_a?(Array) ?
380 395 column.sortable.collect {|s| "#{s} #{order}"}.join(',') :
381 396 "#{column.sortable} #{order}"
382 397 end
383 398 end
384 399
385 400 # Returns true if the query is a grouped query
386 401 def grouped?
387 402 !group_by_column.nil?
388 403 end
389 404
390 405 def group_by_column
391 406 groupable_columns.detect {|c| c.groupable && c.name.to_s == group_by}
392 407 end
393 408
394 409 def group_by_statement
395 410 group_by_column.try(:groupable)
396 411 end
397 412
398 413 def project_statement
399 414 project_clauses = []
400 415 if project && !project.descendants.active.empty?
401 416 ids = [project.id]
402 417 if has_filter?("subproject_id")
403 418 case operator_for("subproject_id")
404 419 when '='
405 420 # include the selected subprojects
406 421 ids += values_for("subproject_id").each(&:to_i)
407 422 when '!*'
408 423 # main project only
409 424 else
410 425 # all subprojects
411 426 ids += project.descendants.collect(&:id)
412 427 end
413 428 elsif Setting.display_subprojects_issues?
414 429 ids += project.descendants.collect(&:id)
415 430 end
416 431 project_clauses << "#{Project.table_name}.id IN (%s)" % ids.join(',')
417 432 elsif project
418 433 project_clauses << "#{Project.table_name}.id = %d" % project.id
419 434 end
420 435 project_clauses.any? ? project_clauses.join(' AND ') : nil
421 436 end
422 437
423 438 def statement
424 439 # filters clauses
425 440 filters_clauses = []
426 441 filters.each_key do |field|
427 442 next if field == "subproject_id"
428 443 v = values_for(field).clone
429 444 next unless v and !v.empty?
430 445 operator = operator_for(field)
431 446
432 447 # "me" value subsitution
433 448 if %w(assigned_to_id author_id watcher_id).include?(field)
434 449 if v.delete("me")
435 450 if User.current.logged?
436 451 v.push(User.current.id.to_s)
437 452 v += User.current.group_ids.map(&:to_s) if field == 'assigned_to_id'
438 453 else
439 454 v.push("0")
440 455 end
441 456 end
442 457 end
443 458
444 459 if field == 'project_id'
445 460 if v.delete('mine')
446 461 v += User.current.memberships.map(&:project_id).map(&:to_s)
447 462 end
448 463 end
449 464
450 465 if field =~ /cf_(\d+)$/
451 466 # custom field
452 467 filters_clauses << sql_for_custom_field(field, operator, v, $1)
453 468 elsif respond_to?("sql_for_#{field}_field")
454 469 # specific statement
455 470 filters_clauses << send("sql_for_#{field}_field", field, operator, v)
456 471 else
457 472 # regular field
458 473 filters_clauses << '(' + sql_for_field(field, operator, v, queried_table_name, field) + ')'
459 474 end
460 475 end if filters and valid?
461 476
462 477 filters_clauses << project_statement
463 478 filters_clauses.reject!(&:blank?)
464 479
465 480 filters_clauses.any? ? filters_clauses.join(' AND ') : nil
466 481 end
467 482
468 483 private
469 484
470 485 def sql_for_custom_field(field, operator, value, custom_field_id)
471 486 db_table = CustomValue.table_name
472 487 db_field = 'value'
473 488 filter = @available_filters[field]
474 489 return nil unless filter
475 490 if filter[:format] == 'user'
476 491 if value.delete('me')
477 492 value.push User.current.id.to_s
478 493 end
479 494 end
480 495 not_in = nil
481 496 if operator == '!'
482 497 # Makes ! operator work for custom fields with multiple values
483 498 operator = '='
484 499 not_in = 'NOT'
485 500 end
486 501 customized_key = "id"
487 502 customized_class = queried_class
488 503 if field =~ /^(.+)\.cf_/
489 504 assoc = $1
490 505 customized_key = "#{assoc}_id"
491 506 customized_class = queried_class.reflect_on_association(assoc.to_sym).klass.base_class rescue nil
492 507 raise "Unknown #{queried_class.name} association #{assoc}" unless customized_class
493 508 end
494 509 "#{queried_table_name}.#{customized_key} #{not_in} IN (SELECT #{customized_class.table_name}.id FROM #{customized_class.table_name} LEFT OUTER JOIN #{db_table} ON #{db_table}.customized_type='#{customized_class}' AND #{db_table}.customized_id=#{customized_class.table_name}.id AND #{db_table}.custom_field_id=#{custom_field_id} WHERE " +
495 510 sql_for_field(field, operator, value, db_table, db_field, true) + ')'
496 511 end
497 512
498 513 # Helper method to generate the WHERE sql for a +field+, +operator+ and a +value+
499 514 def sql_for_field(field, operator, value, db_table, db_field, is_custom_filter=false)
500 515 sql = ''
501 516 case operator
502 517 when "="
503 518 if value.any?
504 519 case type_for(field)
505 520 when :date, :date_past
506 521 sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), (Date.parse(value.first) rescue nil))
507 522 when :integer
508 523 if is_custom_filter
509 524 sql = "(#{db_table}.#{db_field} <> '' AND CAST(#{db_table}.#{db_field} AS decimal(60,3)) = #{value.first.to_i})"
510 525 else
511 526 sql = "#{db_table}.#{db_field} = #{value.first.to_i}"
512 527 end
513 528 when :float
514 529 if is_custom_filter
515 530 sql = "(#{db_table}.#{db_field} <> '' AND CAST(#{db_table}.#{db_field} AS decimal(60,3)) BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5})"
516 531 else
517 532 sql = "#{db_table}.#{db_field} BETWEEN #{value.first.to_f - 1e-5} AND #{value.first.to_f + 1e-5}"
518 533 end
519 534 else
520 535 sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")"
521 536 end
522 537 else
523 538 # IN an empty set
524 539 sql = "1=0"
525 540 end
526 541 when "!"
527 542 if value.any?
528 543 sql = "(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + "))"
529 544 else
530 545 # NOT IN an empty set
531 546 sql = "1=1"
532 547 end
533 548 when "!*"
534 549 sql = "#{db_table}.#{db_field} IS NULL"
535 550 sql << " OR #{db_table}.#{db_field} = ''" if is_custom_filter
536 551 when "*"
537 552 sql = "#{db_table}.#{db_field} IS NOT NULL"
538 553 sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
539 554 when ">="
540 555 if [:date, :date_past].include?(type_for(field))
541 556 sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), nil)
542 557 else
543 558 if is_custom_filter
544 559 sql = "(#{db_table}.#{db_field} <> '' AND CAST(#{db_table}.#{db_field} AS decimal(60,3)) >= #{value.first.to_f})"
545 560 else
546 561 sql = "#{db_table}.#{db_field} >= #{value.first.to_f}"
547 562 end
548 563 end
549 564 when "<="
550 565 if [:date, :date_past].include?(type_for(field))
551 566 sql = date_clause(db_table, db_field, nil, (Date.parse(value.first) rescue nil))
552 567 else
553 568 if is_custom_filter
554 569 sql = "(#{db_table}.#{db_field} <> '' AND CAST(#{db_table}.#{db_field} AS decimal(60,3)) <= #{value.first.to_f})"
555 570 else
556 571 sql = "#{db_table}.#{db_field} <= #{value.first.to_f}"
557 572 end
558 573 end
559 574 when "><"
560 575 if [:date, :date_past].include?(type_for(field))
561 576 sql = date_clause(db_table, db_field, (Date.parse(value[0]) rescue nil), (Date.parse(value[1]) rescue nil))
562 577 else
563 578 if is_custom_filter
564 579 sql = "(#{db_table}.#{db_field} <> '' AND CAST(#{db_table}.#{db_field} AS decimal(60,3)) BETWEEN #{value[0].to_f} AND #{value[1].to_f})"
565 580 else
566 581 sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_f} AND #{value[1].to_f}"
567 582 end
568 583 end
569 584 when "o"
570 585 sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{connection.quoted_false})" if field == "status_id"
571 586 when "c"
572 587 sql = "#{queried_table_name}.status_id IN (SELECT id FROM #{IssueStatus.table_name} WHERE is_closed=#{connection.quoted_true})" if field == "status_id"
573 588 when "><t-"
574 589 # between today - n days and today
575 590 sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0)
576 591 when ">t-"
577 592 # >= today - n days
578 593 sql = relative_date_clause(db_table, db_field, - value.first.to_i, nil)
579 594 when "<t-"
580 595 # <= today - n days
581 596 sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i)
582 597 when "t-"
583 598 # = n days in past
584 599 sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i)
585 600 when "><t+"
586 601 # between today and today + n days
587 602 sql = relative_date_clause(db_table, db_field, 0, value.first.to_i)
588 603 when ">t+"
589 604 # >= today + n days
590 605 sql = relative_date_clause(db_table, db_field, value.first.to_i, nil)
591 606 when "<t+"
592 607 # <= today + n days
593 608 sql = relative_date_clause(db_table, db_field, nil, value.first.to_i)
594 609 when "t+"
595 610 # = today + n days
596 611 sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i)
597 612 when "t"
598 613 # = today
599 614 sql = relative_date_clause(db_table, db_field, 0, 0)
600 615 when "w"
601 616 # = this week
602 617 first_day_of_week = l(:general_first_day_of_week).to_i
603 618 day_of_week = Date.today.cwday
604 619 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
605 620 sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6)
606 621 when "~"
607 622 sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
608 623 when "!~"
609 624 sql = "LOWER(#{db_table}.#{db_field}) NOT LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'"
610 625 else
611 626 raise "Unknown query operator #{operator}"
612 627 end
613 628
614 629 return sql
615 630 end
616 631
617 632 def add_custom_fields_filters(custom_fields, assoc=nil)
618 633 return unless custom_fields.present?
619 634 @available_filters ||= {}
620 635
621 636 custom_fields.select(&:is_filter?).each do |field|
622 637 case field.field_format
623 638 when "text"
624 639 options = { :type => :text, :order => 20 }
625 640 when "list"
626 641 options = { :type => :list_optional, :values => field.possible_values, :order => 20}
627 642 when "date"
628 643 options = { :type => :date, :order => 20 }
629 644 when "bool"
630 645 options = { :type => :list, :values => [[l(:general_text_yes), "1"], [l(:general_text_no), "0"]], :order => 20 }
631 646 when "int"
632 647 options = { :type => :integer, :order => 20 }
633 648 when "float"
634 649 options = { :type => :float, :order => 20 }
635 650 when "user", "version"
636 651 next unless project
637 652 values = field.possible_values_options(project)
638 653 if User.current.logged? && field.field_format == 'user'
639 654 values.unshift ["<< #{l(:label_me)} >>", "me"]
640 655 end
641 656 options = { :type => :list_optional, :values => values, :order => 20}
642 657 else
643 658 options = { :type => :string, :order => 20 }
644 659 end
645 660 filter_id = "cf_#{field.id}"
646 661 filter_name = field.name
647 662 if assoc.present?
648 663 filter_id = "#{assoc}.#{filter_id}"
649 664 filter_name = l("label_attribute_of_#{assoc}", :name => filter_name)
650 665 end
651 666 @available_filters[filter_id] = options.merge({
652 667 :name => filter_name,
653 668 :format => field.field_format,
654 669 :field => field
655 670 })
656 671 end
657 672 end
658 673
659 674 def add_associations_custom_fields_filters(*associations)
660 675 fields_by_class = CustomField.where(:is_filter => true).group_by(&:class)
661 676 associations.each do |assoc|
662 677 association_klass = queried_class.reflect_on_association(assoc).klass
663 678 fields_by_class.each do |field_class, fields|
664 679 if field_class.customized_class <= association_klass
665 680 add_custom_fields_filters(fields, assoc)
666 681 end
667 682 end
668 683 end
669 684 end
670 685
671 686 # Returns a SQL clause for a date or datetime field.
672 687 def date_clause(table, field, from, to)
673 688 s = []
674 689 if from
675 690 from_yesterday = from - 1
676 691 from_yesterday_time = Time.local(from_yesterday.year, from_yesterday.month, from_yesterday.day)
677 692 if self.class.default_timezone == :utc
678 693 from_yesterday_time = from_yesterday_time.utc
679 694 end
680 695 s << ("#{table}.#{field} > '%s'" % [connection.quoted_date(from_yesterday_time.end_of_day)])
681 696 end
682 697 if to
683 698 to_time = Time.local(to.year, to.month, to.day)
684 699 if self.class.default_timezone == :utc
685 700 to_time = to_time.utc
686 701 end
687 702 s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date(to_time.end_of_day)])
688 703 end
689 704 s.join(' AND ')
690 705 end
691 706
692 707 # Returns a SQL clause for a date or datetime field using relative dates.
693 708 def relative_date_clause(table, field, days_from, days_to)
694 709 date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil))
695 710 end
696 711
697 712 # Additional joins required for the given sort options
698 713 def joins_for_order_statement(order_options)
699 714 joins = []
700 715
701 716 if order_options
702 717 if order_options.include?('authors')
703 718 joins << "LEFT OUTER JOIN #{User.table_name} authors ON authors.id = #{queried_table_name}.author_id"
704 719 end
705 720 order_options.scan(/cf_\d+/).uniq.each do |name|
706 721 column = available_columns.detect {|c| c.name.to_s == name}
707 722 join = column && column.custom_field.join_for_order_statement
708 723 if join
709 724 joins << join
710 725 end
711 726 end
712 727 end
713 728
714 729 joins.any? ? joins.join(' ') : nil
715 730 end
716 731 end
General Comments 0
You need to be logged in to leave comments. Login now