##// END OF EJS Templates
jro_apps » redmine
RSS
Fixed filtering on date custom field with SQLServer....
Jean-Philippe Lang -
r13611:db9bf8cd7339
parent child
Show More
Side by Side Unified
Context file:
r13611:db9bf8cd7339 Loading diff...:
Show file before | Show file after | Hide comments
@@ -648,7 +648,7 class Query < ActiveRecord::Base
648 if value.any?
648 if value.any?
649 case type_for(field)
649 case type_for(field)
650 when :date, :date_past
650 when :date, :date_past
651 sql = date_clause(db_table, db_field, parse_date(value.first), parse_date(value.first))
651 sql = date_clause(db_table, db_field, parse_date(value.first), parse_date(value.first), is_custom_filter)
652 when :integer
652 when :integer
653 if is_custom_filter
653 if is_custom_filter
654 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})"
654 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})"
@@ -683,7 +683,7 class Query < ActiveRecord::Base
683 sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
683 sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter
684 when ">="
684 when ">="
685 if [:date, :date_past].include?(type_for(field))
685 if [:date, :date_past].include?(type_for(field))
686 sql = date_clause(db_table, db_field, parse_date(value.first), nil)
686 sql = date_clause(db_table, db_field, parse_date(value.first), nil, is_custom_filter)
687 else
687 else
688 if is_custom_filter
688 if is_custom_filter
689 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})"
689 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})"
@@ -693,7 +693,7 class Query < ActiveRecord::Base
693 end
693 end
694 when "<="
694 when "<="
695 if [:date, :date_past].include?(type_for(field))
695 if [:date, :date_past].include?(type_for(field))
696 sql = date_clause(db_table, db_field, nil, parse_date(value.first))
696 sql = date_clause(db_table, db_field, nil, parse_date(value.first), is_custom_filter)
697 else
697 else
698 if is_custom_filter
698 if is_custom_filter
699 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})"
699 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})"
@@ -703,7 +703,7 class Query < ActiveRecord::Base
703 end
703 end
704 when "><"
704 when "><"
705 if [:date, :date_past].include?(type_for(field))
705 if [:date, :date_past].include?(type_for(field))
706 sql = date_clause(db_table, db_field, parse_date(value[0]), parse_date(value[1]))
706 sql = date_clause(db_table, db_field, parse_date(value[0]), parse_date(value[1]), is_custom_filter)
707 else
707 else
708 if is_custom_filter
708 if is_custom_filter
709 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})"
709 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})"
@@ -717,64 +717,64 class Query < ActiveRecord::Base
717 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"
717 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"
718 when "><t-"
718 when "><t-"
719 # between today - n days and today
719 # between today - n days and today
720 sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0)
720 sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0, is_custom_filter)
721 when ">t-"
721 when ">t-"
722 # >= today - n days
722 # >= today - n days
723 sql = relative_date_clause(db_table, db_field, - value.first.to_i, nil)
723 sql = relative_date_clause(db_table, db_field, - value.first.to_i, nil, is_custom_filter)
724 when "<t-"
724 when "<t-"
725 # <= today - n days
725 # <= today - n days
726 sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i)
726 sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i, is_custom_filter)
727 when "t-"
727 when "t-"
728 # = n days in past
728 # = n days in past
729 sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i)
729 sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i, is_custom_filter)
730 when "><t+"
730 when "><t+"
731 # between today and today + n days
731 # between today and today + n days
732 sql = relative_date_clause(db_table, db_field, 0, value.first.to_i)
732 sql = relative_date_clause(db_table, db_field, 0, value.first.to_i, is_custom_filter)
733 when ">t+"
733 when ">t+"
734 # >= today + n days
734 # >= today + n days
735 sql = relative_date_clause(db_table, db_field, value.first.to_i, nil)
735 sql = relative_date_clause(db_table, db_field, value.first.to_i, nil, is_custom_filter)
736 when "<t+"
736 when "<t+"
737 # <= today + n days
737 # <= today + n days
738 sql = relative_date_clause(db_table, db_field, nil, value.first.to_i)
738 sql = relative_date_clause(db_table, db_field, nil, value.first.to_i, is_custom_filter)
739 when "t+"
739 when "t+"
740 # = today + n days
740 # = today + n days
741 sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i)
741 sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i, is_custom_filter)
742 when "t"
742 when "t"
743 # = today
743 # = today
744 sql = relative_date_clause(db_table, db_field, 0, 0)
744 sql = relative_date_clause(db_table, db_field, 0, 0, is_custom_filter)
745 when "ld"
745 when "ld"
746 # = yesterday
746 # = yesterday
747 sql = relative_date_clause(db_table, db_field, -1, -1)
747 sql = relative_date_clause(db_table, db_field, -1, -1, is_custom_filter)
748 when "w"
748 when "w"
749 # = this week
749 # = this week
750 first_day_of_week = l(:general_first_day_of_week).to_i
750 first_day_of_week = l(:general_first_day_of_week).to_i
751 day_of_week = Date.today.cwday
751 day_of_week = Date.today.cwday
752 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
752 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
753 sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6)
753 sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6, is_custom_filter)
754 when "lw"
754 when "lw"
755 # = last week
755 # = last week
756 first_day_of_week = l(:general_first_day_of_week).to_i
756 first_day_of_week = l(:general_first_day_of_week).to_i
757 day_of_week = Date.today.cwday
757 day_of_week = Date.today.cwday
758 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
758 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
759 sql = relative_date_clause(db_table, db_field, - days_ago - 7, - days_ago - 1)
759 sql = relative_date_clause(db_table, db_field, - days_ago - 7, - days_ago - 1, is_custom_filter)
760 when "l2w"
760 when "l2w"
761 # = last 2 weeks
761 # = last 2 weeks
762 first_day_of_week = l(:general_first_day_of_week).to_i
762 first_day_of_week = l(:general_first_day_of_week).to_i
763 day_of_week = Date.today.cwday
763 day_of_week = Date.today.cwday
764 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
764 days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week)
765 sql = relative_date_clause(db_table, db_field, - days_ago - 14, - days_ago - 1)
765 sql = relative_date_clause(db_table, db_field, - days_ago - 14, - days_ago - 1, is_custom_filter)
766 when "m"
766 when "m"
767 # = this month
767 # = this month
768 date = Date.today
768 date = Date.today
769 sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month)
769 sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month, is_custom_filter)
770 when "lm"
770 when "lm"
771 # = last month
771 # = last month
772 date = Date.today.prev_month
772 date = Date.today.prev_month
773 sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month)
773 sql = date_clause(db_table, db_field, date.beginning_of_month, date.end_of_month, is_custom_filter)
774 when "y"
774 when "y"
775 # = this year
775 # = this year
776 date = Date.today
776 date = Date.today
777 sql = date_clause(db_table, db_field, date.beginning_of_year, date.end_of_year)
777 sql = date_clause(db_table, db_field, date.beginning_of_year, date.end_of_year, is_custom_filter)
778 when "~"
778 when "~"
779 sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{self.class.connection.quote_string(value.first.to_s.downcase)}%'"
779 sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{self.class.connection.quote_string(value.first.to_s.downcase)}%'"
780 when "!~"
780 when "!~"
@@ -829,8 +829,18 class Query < ActiveRecord::Base
829 end
829 end
830 end
830 end
831
831
832 def quoted_time(time, is_custom_filter)
833 if is_custom_filter
834 # Custom field values are stored as strings in the DB
835 # using this format that does not depend on DB date representation
836 time.strftime("%Y-%m-%d %H:%M:%S")
837 else
838 self.class.connection.quoted_date(time)
839 end
840 end
841
832 # Returns a SQL clause for a date or datetime field.
842 # Returns a SQL clause for a date or datetime field.
833 def date_clause(table, field, from, to)
843 def date_clause(table, field, from, to, is_custom_filter)
834 s = []
844 s = []
835 if from
845 if from
836 if from.is_a?(Date)
846 if from.is_a?(Date)
@@ -841,7 +851,7 class Query < ActiveRecord::Base
841 if self.class.default_timezone == :utc
851 if self.class.default_timezone == :utc
842 from = from.utc
852 from = from.utc
843 end
853 end
844 s << ("#{table}.#{field} > '%s'" % [self.class.connection.quoted_date(from)])
854 s << ("#{table}.#{field} > '%s'" % [quoted_time(from, is_custom_filter)])
845 end
855 end
846 if to
856 if to
847 if to.is_a?(Date)
857 if to.is_a?(Date)
@@ -850,14 +860,14 class Query < ActiveRecord::Base
850 if self.class.default_timezone == :utc
860 if self.class.default_timezone == :utc
851 to = to.utc
861 to = to.utc
852 end
862 end
853 s << ("#{table}.#{field} <= '%s'" % [self.class.connection.quoted_date(to)])
863 s << ("#{table}.#{field} <= '%s'" % [quoted_time(to, is_custom_filter)])
854 end
864 end
855 s.join(' AND ')
865 s.join(' AND ')
856 end
866 end
857
867
858 # Returns a SQL clause for a date or datetime field using relative dates.
868 # Returns a SQL clause for a date or datetime field using relative dates.
859 def relative_date_clause(table, field, days_from, days_to)
869 def relative_date_clause(table, field, days_from, days_to, is_custom_filter)
860 date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil))
870 date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil), is_custom_filter)
861 end
871 end
862
872
863 # Returns a Date or Time from the given filter value
873 # Returns a Date or Time from the given filter value
General Comments 0
You need to be logged in to leave comments. Login now