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