jro_apps/redmine Commit - r13611:db9bf8cd7339

Fixed filtering on date custom field with SQLServer....

Jean-Philippe Lang -

r13611:db9bf8cd7339

parent child

Context file:

r13611:db9bf8cd7339

Collapse all files

app/models/query.rb +35 -25

                    if value.any?
                      case type_for(field)
                      when :date, :date_past
-                       sql = date_clause(db_table, db_field, parse_date(value.first), parse_date(value.first))
+                       sql = date_clause(db_table, db_field, parse_date(value.first), parse_date(value.first), is_custom_filter)
                      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, parse_date(value.first), nil)
+                     sql = date_clause(db_table, db_field, parse_date(value.first), nil, is_custom_filter)
                    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, parse_date(value.first))
+                     sql = date_clause(db_table, db_field, nil, parse_date(value.first), is_custom_filter)
                    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, parse_date(value[0]), parse_date(value[1]))
+                     sql = date_clause(db_table, db_field, parse_date(value[0]), parse_date(value[1]), is_custom_filter)
                    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})"
                    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"
                  when "><t-"
                    # between today - n days and today
-                   sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0)
+                   sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0, is_custom_filter)
                  when ">t-"
                    # >= today - n days
-                   sql = relative_date_clause(db_table, db_field, - value.first.to_i, nil)
+                   sql = relative_date_clause(db_table, db_field, - value.first.to_i, nil, is_custom_filter)
                  when "<t-"
                    # <= today - n days
-                   sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i)
+                   sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i, is_custom_filter)
                  when "t-"
                    # = n days in past
-                   sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i)
+                   sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i, is_custom_filter)
                  when "><t+"
                    # between today and today + n days
-                   sql = relative_date_clause(db_table, db_field, 0, value.first.to_i)
+                   sql = relative_date_clause(db_table, db_field, 0, value.first.to_i, is_custom_filter)
                  when ">t+"
                    # >= today + n days
-                   sql = relative_date_clause(db_table, db_field, value.first.to_i, nil)
+                   sql = relative_date_clause(db_table, db_field, value.first.to_i, nil, is_custom_filter)
                  when "<t+"
                    # <= today + n days
-                   sql = relative_date_clause(db_table, db_field, nil, value.first.to_i)
+                   sql = relative_date_clause(db_table, db_field, nil, value.first.to_i, is_custom_filter)
                  when "t+"
                    # = today + n days
-                   sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i)
+                   sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i, is_custom_filter)
                  when "t"
                    # = today
-                   sql = relative_date_clause(db_table, db_field, 0, 0)
+                   sql = relative_date_clause(db_table, db_field, 0, 0, is_custom_filter)
                  when "ld"
                    # = yesterday
-                   sql = relative_date_clause(db_table, db_field, -1, -1)
+                   sql = relative_date_clause(db_table, db_field, -1, -1, is_custom_filter)
                  when "w"
                    # = this week
                    first_day_of_week = l(:general_first_day_of_week).to_i
                    day_of_week = Date.today.cwday
                    days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
-                   sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6)
+                   sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6, is_custom_filter)
                  when "lw"
                    # = last week
                    first_day_of_week = l(:general_first_day_of_week).to_i
                    day_of_week = Date.today.cwday
                    days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
-                   sql = relative_date_clause(db_table, db_field, - days_ago - 7, - days_ago - 1)
+                   sql = relative_date_clause(db_table, db_field, - days_ago - 7, - days_ago - 1, is_custom_filter)
                  when "l2w"
                    # = last 2 weeks
                    first_day_of_week = l(:general_first_day_of_week).to_i
                    day_of_week = Date.today.cwday
                    days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
-                   sql = relative_date_clause(db_table, db_field, - days_ago - 14, - days_ago - 1)
+                   sql = relative_date_clause(db_table, db_field, - days_ago - 14, - days_ago - 1, is_custom_filter)
                  when "m"
                    # = this month
                    date = Date.today
-                   sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month)
+                   sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month, is_custom_filter)
                  when "lm"
                    # = last month
                    date = Date.today.prev_month
-                   sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month)
+                   sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month, is_custom_filter)
                  when "y"
                    # = this year
                    date = Date.today
-                   sql = date_clause(db_table, db_field, date.beginning_of_year, date.end_of_year)
+                   sql = date_clause(db_table, db_field, date.beginning_of_year, date.end_of_year, is_custom_filter)
                  when "~"
                    sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{self.class.connection.quote_string(value.first.to_s.downcase)}%'"
                  when "!~"
                  end
                end
+               def quoted_time(time, is_custom_filter)
+                 if is_custom_filter
+                   # Custom field values are stored as strings in the DB
+                   # using this format that does not depend on DB date representation
+                   time.strftime("%Y-%m-%d %H:%M:%S")
+                 else
+                   self.class.connection.quoted_date(time)
+                 end
+               end
                # Returns a SQL clause for a date or datetime field.
-               def date_clause(table, field, from, to)
+               def date_clause(table, field, from, to, is_custom_filter)
                  s = []
                  if from
                    if from.is_a?(Date)
                    if self.class.default_timezone == :utc
                      from = from.utc
                    end
-                   s << ("#{table}.#{field} > '%s'" % [self.class.connection.quoted_date(from)])
+                   s << ("#{table}.#{field} > '%s'" % [quoted_time(from, is_custom_filter)])
                  end
                  if to
                    if to.is_a?(Date)
                    if self.class.default_timezone == :utc
                      to = to.utc
                    end
-                   s << ("#{table}.#{field} <= '%s'" % [self.class.connection.quoted_date(to)])
+                   s << ("#{table}.#{field} <= '%s'" % [quoted_time(to, is_custom_filter)])
                  end
                  s.join(' AND ')
                end
                # Returns a SQL clause for a date or datetime field using relative dates.
-               def relative_date_clause(table, field, days_from, days_to)
-                 date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil))
+               def relative_date_clause(table, field, days_from, days_to, is_custom_filter)
+                 date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil), is_custom_filter)
                end
                # Returns a Date or Time from the given filter value

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