| @@ -119,8 +119,8 class Query < ActiveRecord::Base | |||||
| 119 | :list_status => [ "o", "=", "!", "c", "*" ], |
|
119 | :list_status => [ "o", "=", "!", "c", "*" ], | |
| 120 | :list_optional => [ "=", "!", "!*", "*" ], |
|
120 | :list_optional => [ "=", "!", "!*", "*" ], | |
| 121 | :list_subprojects => [ "*", "!*", "=" ], |
|
121 | :list_subprojects => [ "*", "!*", "=" ], | |
| 122 | :date => [ "<t+", ">t+", "t+", "t", "w", ">t-", "<t-", "t-" ], |
|
122 | :date => [ "=", ">=", "<=", "><", "<t+", ">t+", "t+", "t", "w", ">t-", "<t-", "t-" ], | |
| 123 | :date_past => [ ">t-", "<t-", "t-", "t", "w" ], |
|
123 | :date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "t-", "t", "w" ], | |
| 124 | :string => [ "=", "~", "!", "!~" ], |
|
124 | :string => [ "=", "~", "!", "!~" ], | |
| 125 | :text => [ "~", "!~" ], |
|
125 | :text => [ "~", "!~" ], | |
| 126 | :integer => [ "=", ">=", "<=", "><", "!*", "*" ] } |
|
126 | :integer => [ "=", ">=", "<=", "><", "!*", "*" ] } | |
| @@ -268,7 +268,7 class Query < ActiveRecord::Base | |||||
| 268 |
|
268 | |||
| 269 | def add_filter(field, operator, values) |
|
269 | def add_filter(field, operator, values) | |
| 270 | # values must be an array |
|
270 | # values must be an array | |
| 271 |
return unless values |
|
271 | return unless values.nil? || values.is_a?(Array) | |
| 272 | # check if field is defined as an available filter |
|
272 | # check if field is defined as an available filter | |
| 273 | if available_filters.has_key? field |
|
273 | if available_filters.has_key? field | |
| 274 | filter_options = available_filters[field] |
|
274 | filter_options = available_filters[field] | |
| @@ -277,7 +277,7 class Query < ActiveRecord::Base | |||||
| 277 | # allowed_values = values & ([""] + (filter_options[:values] || []).collect {|val| val[1]}) |
|
277 | # allowed_values = values & ([""] + (filter_options[:values] || []).collect {|val| val[1]}) | |
| 278 | # filters[field] = {:operator => operator, :values => allowed_values } if (allowed_values.first and !allowed_values.first.empty?) or ["o", "c", "!*", "*", "t"].include? operator |
|
278 | # filters[field] = {:operator => operator, :values => allowed_values } if (allowed_values.first and !allowed_values.first.empty?) or ["o", "c", "!*", "*", "t"].include? operator | |
| 279 | #end |
|
279 | #end | |
| 280 | filters[field] = {:operator => operator, :values => values } |
|
280 | filters[field] = {:operator => operator, :values => (values || ['']) } | |
| 281 | end |
|
281 | end | |
| 282 | end |
|
282 | end | |
| 283 |
|
283 | |||
| @@ -289,9 +289,9 class Query < ActiveRecord::Base | |||||
| 289 |
|
289 | |||
| 290 | # Add multiple filters using +add_filter+ |
|
290 | # Add multiple filters using +add_filter+ | |
| 291 | def add_filters(fields, operators, values) |
|
291 | def add_filters(fields, operators, values) | |
| 292 | if fields.is_a?(Array) && operators.is_a?(Hash) && values.is_a?(Hash) |
|
292 | if fields.is_a?(Array) && operators.is_a?(Hash) && (values.nil? || values.is_a?(Hash)) | |
| 293 | fields.each do |field| |
|
293 | fields.each do |field| | |
| 294 | add_filter(field, operators[field], values[field]) |
|
294 | add_filter(field, operators[field], values && values[field]) | |
| 295 | end |
|
295 | end | |
| 296 | end |
|
296 | end | |
| 297 | end |
|
297 | end | |
| @@ -300,6 +300,10 class Query < ActiveRecord::Base | |||||
| 300 | filters and filters[field] |
|
300 | filters and filters[field] | |
| 301 | end |
|
301 | end | |
| 302 |
|
302 | |||
|
|
303 | def type_for(field) | |||
|
|
304 | available_filters[field][:type] if available_filters.has_key?(field) | |||
|
|
305 | end | |||
|
|
306 | ||||
| 303 | def operator_for(field) |
|
307 | def operator_for(field) | |
| 304 | has_filter?(field) ? filters[field][:operator] : nil |
|
308 | has_filter?(field) ? filters[field][:operator] : nil | |
| 305 | end |
|
309 | end | |
| @@ -601,12 +605,16 class Query < ActiveRecord::Base | |||||
| 601 | sql = '' |
|
605 | sql = '' | |
| 602 | case operator |
|
606 | case operator | |
| 603 | when "=" |
|
607 | when "=" | |
|
|
608 | if [:date, :date_past].include?(type_for(field)) | |||
|
|
609 | sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), (Date.parse(value.first) rescue nil)) | |||
|
|
610 | else | |||
| 604 | if value.any? |
|
611 | if value.any? | |
| 605 | sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")" |
|
612 | sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")" | |
| 606 | else |
|
613 | else | |
| 607 | # IN an empty set |
|
614 | # IN an empty set | |
| 608 | sql = "1=0" |
|
615 | sql = "1=0" | |
| 609 | end |
|
616 | end | |
|
|
617 | end | |||
| 610 | when "!" |
|
618 | when "!" | |
| 611 | if value.any? |
|
619 | if value.any? | |
| 612 | sql = "(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + "))" |
|
620 | sql = "(#{db_table}.#{db_field} IS NULL OR #{db_table}.#{db_field} NOT IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + "))" | |
| @@ -621,46 +629,58 class Query < ActiveRecord::Base | |||||
| 621 | sql = "#{db_table}.#{db_field} IS NOT NULL" |
|
629 | sql = "#{db_table}.#{db_field} IS NOT NULL" | |
| 622 | sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter |
|
630 | sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter | |
| 623 | when ">=" |
|
631 | when ">=" | |
|
|
632 | if [:date, :date_past].include?(type_for(field)) | |||
|
|
633 | sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), nil) | |||
|
|
634 | else | |||
| 624 | if is_custom_filter |
|
635 | if is_custom_filter | |
| 625 | sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) >= #{value.first.to_i}" |
|
636 | sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) >= #{value.first.to_i}" | |
| 626 | else |
|
637 | else | |
| 627 | sql = "#{db_table}.#{db_field} >= #{value.first.to_i}" |
|
638 | sql = "#{db_table}.#{db_field} >= #{value.first.to_i}" | |
| 628 | end |
|
639 | end | |
|
|
640 | end | |||
| 629 | when "<=" |
|
641 | when "<=" | |
|
|
642 | if [:date, :date_past].include?(type_for(field)) | |||
|
|
643 | sql = date_clause(db_table, db_field, nil, (Date.parse(value.first) rescue nil)) | |||
|
|
644 | else | |||
| 630 | if is_custom_filter |
|
645 | if is_custom_filter | |
| 631 | sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) <= #{value.first.to_i}" |
|
646 | sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) <= #{value.first.to_i}" | |
| 632 | else |
|
647 | else | |
| 633 | sql = "#{db_table}.#{db_field} <= #{value.first.to_i}" |
|
648 | sql = "#{db_table}.#{db_field} <= #{value.first.to_i}" | |
| 634 | end |
|
649 | end | |
|
|
650 | end | |||
| 635 | when "><" |
|
651 | when "><" | |
|
|
652 | if [:date, :date_past].include?(type_for(field)) | |||
|
|
653 | sql = date_clause(db_table, db_field, (Date.parse(value[0]) rescue nil), (Date.parse(value[1]) rescue nil)) | |||
|
|
654 | else | |||
| 636 | if is_custom_filter |
|
655 | if is_custom_filter | |
| 637 | sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) BETWEEN #{value[0].to_i} AND #{value[1].to_i}" |
|
656 | sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) BETWEEN #{value[0].to_i} AND #{value[1].to_i}" | |
| 638 | else |
|
657 | else | |
| 639 | sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_i} AND #{value[1].to_i}" |
|
658 | sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_i} AND #{value[1].to_i}" | |
| 640 | end |
|
659 | end | |
|
|
660 | end | |||
| 641 | when "o" |
|
661 | when "o" | |
| 642 | sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_false}" if field == "status_id" |
|
662 | sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_false}" if field == "status_id" | |
| 643 | when "c" |
|
663 | when "c" | |
| 644 | sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_true}" if field == "status_id" |
|
664 | sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_true}" if field == "status_id" | |
| 645 | when ">t-" |
|
665 | when ">t-" | |
| 646 |
sql = |
|
666 | sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0) | |
| 647 | when "<t-" |
|
667 | when "<t-" | |
| 648 |
sql = |
|
668 | sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i) | |
| 649 | when "t-" |
|
669 | when "t-" | |
| 650 |
sql = |
|
670 | sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i) | |
| 651 | when ">t+" |
|
671 | when ">t+" | |
| 652 |
sql = |
|
672 | sql = relative_date_clause(db_table, db_field, value.first.to_i, nil) | |
| 653 | when "<t+" |
|
673 | when "<t+" | |
| 654 |
sql = |
|
674 | sql = relative_date_clause(db_table, db_field, 0, value.first.to_i) | |
| 655 | when "t+" |
|
675 | when "t+" | |
| 656 |
sql = |
|
676 | sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i) | |
| 657 | when "t" |
|
677 | when "t" | |
| 658 |
sql = |
|
678 | sql = relative_date_clause(db_table, db_field, 0, 0) | |
| 659 | when "w" |
|
679 | when "w" | |
| 660 | first_day_of_week = l(:general_first_day_of_week).to_i |
|
680 | first_day_of_week = l(:general_first_day_of_week).to_i | |
| 661 | day_of_week = Date.today.cwday |
|
681 | day_of_week = Date.today.cwday | |
| 662 | days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week) |
|
682 | days_ago = (day_of_week >= first_day_of_week ? day_of_week - first_day_of_week : day_of_week + 7 - first_day_of_week) | |
| 663 |
sql = |
|
683 | sql = relative_date_clause(db_table, db_field, - days_ago, - days_ago + 6) | |
| 664 | when "~" |
|
684 | when "~" | |
| 665 | sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'" |
|
685 | sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'" | |
| 666 | when "!~" |
|
686 | when "!~" | |
| @@ -698,14 +718,19 class Query < ActiveRecord::Base | |||||
| 698 | end |
|
718 | end | |
| 699 |
|
719 | |||
| 700 | # Returns a SQL clause for a date or datetime field. |
|
720 | # Returns a SQL clause for a date or datetime field. | |
| 701 |
def date_ |
|
721 | def date_clause(table, field, from, to) | |
| 702 | s = [] |
|
722 | s = [] | |
| 703 | if from |
|
723 | if from | |
| 704 |
s << ("#{table}.#{field} > '%s'" % [connection.quoted_date(( |
|
724 | s << ("#{table}.#{field} > '%s'" % [connection.quoted_date((from - 1).to_time.end_of_day)]) | |
| 705 | end |
|
725 | end | |
| 706 | if to |
|
726 | if to | |
| 707 |
s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date( |
|
727 | s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date(to.to_time.end_of_day)]) | |
| 708 | end |
|
728 | end | |
| 709 | s.join(' AND ') |
|
729 | s.join(' AND ') | |
| 710 | end |
|
730 | end | |
|
|
731 | ||||
|
|
732 | # Returns a SQL clause for a date or datetime field using relative dates. | |||
|
|
733 | def relative_date_clause(table, field, days_from, days_to) | |||
|
|
734 | date_clause(table, field, (days_from ? Date.today + days_from : nil), (days_to ? Date.today + days_to : nil)) | |||
|
|
735 | end | |||
| 711 | end |
|
736 | end | |
| @@ -18,17 +18,33 function add_filter() { | |||||
| 18 |
|
18 | |||
| 19 | function toggle_filter(field) { |
|
19 | function toggle_filter(field) { | |
| 20 |
|
|
20 | check_box = $('cb_' + field); | |
| 21 |
|
||||
| 22 |
|
|
21 | if (check_box.checked) { | |
| 23 |
|
|
22 | Element.show("operators_" + field); | |
| 24 |
|
|
23 | Form.Element.enable("operators_" + field); | |
| 25 | $$(".values_" + field).each(function(el){ Form.Element.enable(el)}); |
|
|||
| 26 |
|
|
24 | toggle_operator(field); | |
| 27 |
|
|
25 | } else { | |
| 28 |
|
|
26 | Element.hide("operators_" + field); | |
| 29 | Element.hide("div_values_" + field); |
|
|||
| 30 |
|
|
27 | Form.Element.disable("operators_" + field); | |
| 31 | $$(".values_" + field).each(function(el){ Form.Element.disable(el)}); |
|
28 | enableValues(field, []); | |
|
|
29 | } | |||
|
|
30 | } | |||
|
|
31 | ||||
|
|
32 | function enableValues(field, indexes) { | |||
|
|
33 | var f = $$(".values_" + field); | |||
|
|
34 | for(var i=0;i<f.length;i++) { | |||
|
|
35 | if (indexes.include(i)) { | |||
|
|
36 | Form.Element.enable(f[i]); | |||
|
|
37 | f[i].up('span').show(); | |||
|
|
38 | } else { | |||
|
|
39 | f[i].value = ''; | |||
|
|
40 | Form.Element.disable(f[i]); | |||
|
|
41 | f[i].up('span').hide(); | |||
|
|
42 | } | |||
|
|
43 | } | |||
|
|
44 | if (indexes.length > 0) { | |||
|
|
45 | Element.show("div_values_" + field); | |||
|
|
46 | } else { | |||
|
|
47 | Element.hide("div_values_" + field); | |||
| 32 | } |
|
48 | } | |
| 33 | } |
|
49 | } | |
| 34 |
|
50 | |||
| @@ -41,25 +57,27 function toggle_operator(field) { | |||||
| 41 | case "w": |
|
57 | case "w": | |
| 42 | case "o": |
|
58 | case "o": | |
| 43 | case "c": |
|
59 | case "c": | |
| 44 | Element.hide("div_values_" + field); |
|
60 | enableValues(field, []); | |
| 45 | var v = $$(".values_" + field); |
|
|||
| 46 | if (v.length > 1) {v[1].hide(); Form.Element.disable(v[1])} |
|
|||
| 47 | break; |
|
61 | break; | |
| 48 | case "><": |
|
62 | case "><": | |
| 49 | Element.show("div_values_" + field); |
|
63 | enableValues(field, [0,1]); | |
| 50 | var v = $$(".values_" + field); |
|
64 | break; | |
| 51 | if (v.length > 1) {v[1].show(); Form.Element.enable(v[1])} |
|
65 | case "<t+": | |
|
|
66 | case ">t+": | |||
|
|
67 | case "t+": | |||
|
|
68 | case ">t-": | |||
|
|
69 | case "<t-": | |||
|
|
70 | case "t-": | |||
|
|
71 | enableValues(field, [2]); | |||
| 52 | break; |
|
72 | break; | |
| 53 | default: |
|
73 | default: | |
| 54 | Element.show("div_values_" + field); |
|
74 | enableValues(field, [0]); | |
| 55 | var v = $$(".values_" + field); |
|
|||
| 56 | if (v.length > 1) {v[1].hide(); Form.Element.disable(v[1])} |
|
|||
| 57 | break; |
|
75 | break; | |
| 58 | } |
|
76 | } | |
| 59 | } |
|
77 | } | |
| 60 |
|
78 | |||
| 61 |
function toggle_multi_select( |
|
79 | function toggle_multi_select(el) { | |
| 62 |
|
|
80 | var select = $(el); | |
| 63 |
|
|
81 | if (select.multiple == true) { | |
| 64 |
|
|
82 | select.multiple = false; | |
| 65 |
|
|
83 | } else { | |
| @@ -102,15 +120,19 Event.observe(document,"dom:loaded", apply_filters_observer); | |||||
| 102 | <div id="div_values_<%= field %>" style="display:none;"> |
|
120 | <div id="div_values_<%= field %>" style="display:none;"> | |
| 103 | <% case options[:type] |
|
121 | <% case options[:type] | |
| 104 | when :list, :list_optional, :list_status, :list_subprojects %> |
|
122 | when :list, :list_optional, :list_status, :list_subprojects %> | |
| 105 | <%= select_tag "v[#{field}][]", options_for_select(options[:values], query.values_for(field)), :class => "values_#{field}", :multiple => (query.values_for(field) && query.values_for(field).length > 1) %> |
|
123 | <span class="span_values_<%= field %>"> | |
| 106 | <%= link_to_function image_tag('bullet_toggle_plus.png'), "toggle_multi_select('#{field}');", :style => "vertical-align: bottom;" %> |
|
124 | <%= select_tag "v[#{field}][]", options_for_select(options[:values], query.values_for(field)), :class => "values_#{field}", :id => "values_#{field}_1", :multiple => (query.values_for(field) && query.values_for(field).length > 1) %> | |
|
|
125 | <%= link_to_function image_tag('bullet_toggle_plus.png'), "toggle_multi_select('values_#{field}_1');", :style => "vertical-align: bottom;" %> | |||
|
|
126 | </span> | |||
| 107 | <% when :date, :date_past %> |
|
127 | <% when :date, :date_past %> | |
| 108 | <%= text_field_tag "v[#{field}][]", query.value_for(field), :class => "values_#{field}", :size => 3 %> <%= l(:label_day_plural) %> |
|
128 | <span class="span_values_<%= field %>"><%= text_field_tag "v[#{field}][]", query.value_for(field), :size => 10, :class => "values_#{field}", :id => "values_#{field}_1" %> <%= calendar_for "values_#{field}_1" %></span> | |
|
|
129 | <span class="span_values_<%= field %>"><%= text_field_tag "v[#{field}][]", query.value_for(field, 1), :size => 10, :class => "values_#{field}", :id => "values_#{field}_2" %> <%= calendar_for "values_#{field}_2" %></span> | |||
|
|
130 | <span class="span_values_<%= field %>"><%= text_field_tag "v[#{field}][]", query.value_for(field), :size => 3, :class => "values_#{field}" %> <%= l(:label_day_plural) %></span> | |||
| 109 | <% when :string, :text %> |
|
131 | <% when :string, :text %> | |
| 110 | <%= text_field_tag "v[#{field}][]", query.value_for(field), :class => "values_#{field}", :size => 30 %> |
|
132 | <span class="span_values_<%= field %>"><%= text_field_tag "v[#{field}][]", query.value_for(field), :class => "values_#{field}", :id => "values_#{field}", :size => 30 %></span> | |
| 111 | <% when :integer %> |
|
133 | <% when :integer %> | |
| 112 | <%= text_field_tag "v[#{field}][]", query.value_for(field), :class => "values_#{field}", :size => 3 %> |
|
134 | <span class="span_values_<%= field %>"><%= text_field_tag "v[#{field}][]", query.value_for(field), :class => "values_#{field}", :id => "values_#{field}_1", :size => 3 %></span> | |
| 113 | <%= text_field_tag "v[#{field}][]", query.value_for(field, 1), :class => "values_#{field}", :size => 3 %> |
|
135 | <span class="span_values_<%= field %>"><%= text_field_tag "v[#{field}][]", query.value_for(field, 1), :class => "values_#{field}", :id => "values_#{field}_2", :size => 3 %></span> | |
| 114 | <% end %> |
|
136 | <% end %> | |
| 115 | </div> |
|
137 | </div> | |
| 116 | <script type="text/javascript">toggle_filter('<%= field %>');</script> |
|
138 | <script type="text/javascript">toggle_filter('<%= field %>');</script> | |
| @@ -146,6 +146,34 class QueryTest < ActiveSupport::TestCase | |||||
| 146 | find_issues_with_query(query) |
|
146 | find_issues_with_query(query) | |
| 147 | end |
|
147 | end | |
| 148 |
|
148 | |||
|
|
149 | def test_operator_date_equals | |||
|
|
150 | query = Query.new(:name => '_') | |||
|
|
151 | query.add_filter('due_date', '=', ['2011-07-10']) | |||
|
|
152 | assert_match /issues\.due_date > '2011-07-09 23:59:59(\.9+)?' AND issues\.due_date <= '2011-07-10 23:59:59(\.9+)?/, query.statement | |||
|
|
153 | find_issues_with_query(query) | |||
|
|
154 | end | |||
|
|
155 | ||||
|
|
156 | def test_operator_date_lesser_than | |||
|
|
157 | query = Query.new(:name => '_') | |||
|
|
158 | query.add_filter('due_date', '<=', ['2011-07-10']) | |||
|
|
159 | assert_match /issues\.due_date <= '2011-07-10 23:59:59(\.9+)?/, query.statement | |||
|
|
160 | find_issues_with_query(query) | |||
|
|
161 | end | |||
|
|
162 | ||||
|
|
163 | def test_operator_date_greater_than | |||
|
|
164 | query = Query.new(:name => '_') | |||
|
|
165 | query.add_filter('due_date', '>=', ['2011-07-10']) | |||
|
|
166 | assert_match /issues\.due_date > '2011-07-09 23:59:59(\.9+)?'/, query.statement | |||
|
|
167 | find_issues_with_query(query) | |||
|
|
168 | end | |||
|
|
169 | ||||
|
|
170 | def test_operator_date_between | |||
|
|
171 | query = Query.new(:name => '_') | |||
|
|
172 | query.add_filter('due_date', '><', ['2011-06-23', '2011-07-10']) | |||
|
|
173 | assert_match /issues\.due_date > '2011-06-22 23:59:59(\.9+)?' AND issues\.due_date <= '2011-07-10 23:59:59(\.9+)?/, query.statement | |||
|
|
174 | find_issues_with_query(query) | |||
|
|
175 | end | |||
|
|
176 | ||||
| 149 | def test_operator_in_more_than |
|
177 | def test_operator_in_more_than | |
| 150 | Issue.find(7).update_attribute(:due_date, (Date.today + 15)) |
|
178 | Issue.find(7).update_attribute(:due_date, (Date.today + 15)) | |
| 151 | query = Query.new(:project => Project.find(1), :name => '_') |
|
179 | query = Query.new(:project => Project.find(1), :name => '_') | |
General Comments 0
You need to be logged in to leave comments.
Login now