jro_apps/redmine Commit - r12202:a4d3da988a3e

Allow filtering with timestamp (#8842)....

Jean-Philippe Lang -

r12202:a4d3da988a3e

parent child

Context file:

r12202:a4d3da988a3e

Collapse all files

app/models/query.rb +27 -12

                     when :date, :date_past
                       case operator_for(field)
                       when "=", ">=", "<=", "><"
-                        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?) }
+                        add_filter_error(field, :invalid) if values_for(field).detect {|v|
+                          v.present? && (!v.match(/\A\d{4}-\d{2}-\d{2}(T\d{2}((:)?\d{2}){,2}(Z|\d{2}:?\d{2})?)?\z/) || parse_date(v).nil?)
+                        }
                       when ">t-", "<t-", "t-", ">t+", "<t+", "t+", "><t+", "><t-"
                         add_filter_error(field, :invalid) if values_for(field).detect {|v| v.present? && !v.match(/^\d+$/) }
                       end
                   if value.any?
                     case type_for(field)
                     when :date, :date_past
-                      sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), (Date.parse(value.first) rescue nil))
+                      sql = date_clause(db_table, db_field, parse_date(value.first), parse_date(value.first))
                     when :integer
                       if is_custom_filter
                         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_i})"
                   sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
                 when ">="
                   if [:date, :date_past].include?(type_for(field))
-                    sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), nil)
+                    sql = date_clause(db_table, db_field, parse_date(value.first), nil)
                   else
                     if is_custom_filter
                       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})"
                   end
                 when "<="
                   if [:date, :date_past].include?(type_for(field))
-                    sql = date_clause(db_table, db_field, nil, (Date.parse(value.first) rescue nil))
+                    sql = date_clause(db_table, db_field, nil, parse_date(value.first))
                   else
                     if is_custom_filter
                       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})"
                   end
                 when "><"
                   if [:date, :date_past].include?(type_for(field))
-                    sql = date_clause(db_table, db_field, (Date.parse(value[0]) rescue nil), (Date.parse(value[1]) rescue nil))
+                    sql = date_clause(db_table, db_field, parse_date(value[0]), parse_date(value[1]))
                   else
                     if is_custom_filter
                       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})"
               def date_clause(table, field, from, to)
                 s = []
                 if from
-                  from_yesterday = from - 1
+                  if from.is_a?(Date)
-                  from_yesterday_time = Time.local(from_yesterday.year, from_yesterday.month, from_yesterday.day)
+                    from = Time.local(from.year, from.month, from.day).beginning_of_day
+                  end
                   if self.class.default_timezone == :utc
-                    from_yesterday_time = from_yesterday_time.utc
+                    from = from.utc
                   end
-                  s << ("#{table}.#{field} > '%s'" % [connection.quoted_date(from_yesterday_time.end_of_day)])
+                  from = from - 1
+                  s << ("#{table}.#{field} > '%s'" % [connection.quoted_date(from)])
                 end
                 if to
-                  to_time = Time.local(to.year, to.month, to.day)
+                  if to.is_a?(Date)
+                    to = Time.local(to.year, to.month, to.day).end_of_day
+                  end
                   if self.class.default_timezone == :utc
-                    to_time = to_time.utc
+                    to = to.utc
                   end
-                  s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date(to_time.end_of_day)])
+                  s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date(to)])
                 end
                 s.join(' AND ')
               end
                 date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil))
               end
+              # Returns a Date or Time from the given filter value
+              def parse_date(arg)
+                if arg.to_s =~ /\A\d{4}-\d{2}-\d{2}T/
+                  Time.parse(arg) rescue nil
+                else
+                  Date.parse(arg) rescue nil
+                end
+              end
               # Additional joins required for the given sort options
               def joins_for_order_statement(order_options)
                 joins = []

test/integration/api_test/issues_test.rb +23 0

@@ -162,6 +162,29 class Redmine::ApiTest::IssuesTest < Redmine::ApiTest::Base
162	end	162	end
163	end	163	end
164		164
		165	def test_index_should_allow_timestamp_filtering
		166	Issue.delete_all
		167	Issue.generate!(:subject => '1').update_column(:updated_on, Time.parse("2014-01-02T10:25:00Z"))
		168	Issue.generate!(:subject => '2').update_column(:updated_on, Time.parse("2014-01-02T12:13:00Z"))
		169
		170	get '/issues.xml',
		171	{:set_filter => 1, :f => ['updated_on'], :op => {:updated_on => '<='},
		172	:v => {:updated_on => ['2014-01-02T12:00:00Z']}}
		173	assert_select 'issues>issue', :count => 1
		174	assert_select 'issues>issue>subject', :text => '1'
		175
		176	get '/issues.xml',
		177	{:set_filter => 1, :f => ['updated_on'], :op => {:updated_on => '>='},
		178	:v => {:updated_on => ['2014-01-02T12:00:00Z']}}
		179	assert_select 'issues>issue', :count => 1
		180	assert_select 'issues>issue>subject', :text => '2'
		181
		182	get '/issues.xml',
		183	{:set_filter => 1, :f => ['updated_on'], :op => {:updated_on => '>='},
		184	:v => {:updated_on => ['2014-01-02T08:00:00Z']}}
		185	assert_select 'issues>issue', :count => 2
		186	end
		187
165	context "/index.json" do	188	context "/index.json" do
166	should_allow_api_authentication(:get, "/projects/private-child/issues.json")	189	should_allow_api_authentication(:get, "/projects/private-child/issues.json")
167	end	190	end

General Comments 0

Write
Preview

You need to be logged in to leave comments. Login now

No TODOs yet

	Site-wide shortcuts
/	Use quick search box
g h	Goto home page
g g	Goto my private gists page
g G	Goto my public gists page
g 0-9	Goto bookmarked items from 0-9
n r	New repository page
n g	New gist page

	Repositories
g s	Goto summary page
g c	Goto changelog page
g f	Goto files page
g F	Goto files page with file search activated
g p	Goto pull requests page
g o	Goto repository settings
g O	Goto repository access permissions settings
t s	Toggle sidebar on some pages