| @@ -119,8 +119,8 class Query < ActiveRecord::Base | |||
|
|
119 | 119 | :list_status => [ "o", "=", "!", "c", "*" ], |
|
|
120 | 120 | :list_optional => [ "=", "!", "!*", "*" ], |
|
|
121 | 121 | :list_subprojects => [ "*", "!*", "=" ], |
|
|
122 | :date => [ "<t+", ">t+", "t+", "t", "w", ">t-", "<t-", "t-" ], | |
|
|
123 | :date_past => [ ">t-", "<t-", "t-", "t", "w" ], | |
|
|
122 | :date => [ "=", ">=", "<=", "><", "<t+", ">t+", "t+", "t", "w", ">t-", "<t-", "t-" ], | |
|
|
123 | :date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "t-", "t", "w" ], | |
|
|
124 | 124 | :string => [ "=", "~", "!", "!~" ], |
|
|
125 | 125 | :text => [ "~", "!~" ], |
|
|
126 | 126 | :integer => [ "=", ">=", "<=", "><", "!*", "*" ] } |
| @@ -268,7 +268,7 class Query < ActiveRecord::Base | |||
|
|
268 | 268 | |
|
|
269 | 269 | def add_filter(field, operator, values) |
|
|
270 | 270 | # values must be an array |
|
|
271 |
return unless values |
|
|
|
271 | return unless values.nil? || values.is_a?(Array) | |
|
|
272 | 272 | # check if field is defined as an available filter |
|
|
273 | 273 | if available_filters.has_key? field |
|
|
274 | 274 | filter_options = available_filters[field] |
| @@ -277,7 +277,7 class Query < ActiveRecord::Base | |||
|
|
277 | 277 | # allowed_values = values & ([""] + (filter_options[:values] || []).collect {|val| val[1]}) |
|
|
278 | 278 | # filters[field] = {:operator => operator, :values => allowed_values } if (allowed_values.first and !allowed_values.first.empty?) or ["o", "c", "!*", "*", "t"].include? operator |
|
|
279 | 279 | #end |
|
|
280 | filters[field] = {:operator => operator, :values => values } | |
|
|
280 | filters[field] = {:operator => operator, :values => (values || ['']) } | |
|
|
281 | 281 | end |
|
|
282 | 282 | end |
|
|
283 | 283 | |
| @@ -289,9 +289,9 class Query < ActiveRecord::Base | |||
|
|
289 | 289 | |
|
|
290 | 290 | # Add multiple filters using +add_filter+ |
|
|
291 | 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 | 293 | fields.each do |field| |
|
|
294 | add_filter(field, operators[field], values[field]) | |
|
|
294 | add_filter(field, operators[field], values && values[field]) | |
|
|
295 | 295 | end |
|
|
296 | 296 | end |
|
|
297 | 297 | end |
| @@ -299,6 +299,10 class Query < ActiveRecord::Base | |||
|
|
299 | 299 | def has_filter?(field) |
|
|
300 | 300 | filters and filters[field] |
|
|
301 | 301 | end |
|
|
302 | ||
|
|
303 | def type_for(field) | |
|
|
304 | available_filters[field][:type] if available_filters.has_key?(field) | |
|
|
305 | end | |
|
|
302 | 306 | |
|
|
303 | 307 | def operator_for(field) |
|
|
304 | 308 | has_filter?(field) ? filters[field][:operator] : nil |
| @@ -601,11 +605,15 class Query < ActiveRecord::Base | |||
|
|
601 | 605 | sql = '' |
|
|
602 | 606 | case operator |
|
|
603 | 607 | when "=" |
|
|
604 | if value.any? | |
|
|
605 | sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")" | |
|
|
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)) | |
|
|
606 | 610 | else |
|
|
607 | # IN an empty set | |
|
|
608 | sql = "1=0" | |
|
|
611 | if value.any? | |
|
|
612 | sql = "#{db_table}.#{db_field} IN (" + value.collect{|val| "'#{connection.quote_string(val)}'"}.join(",") + ")" | |
|
|
613 | else | |
|
|
614 | # IN an empty set | |
|
|
615 | sql = "1=0" | |
|
|
616 | end | |
|
|
609 | 617 | end |
|
|
610 | 618 | when "!" |
|
|
611 | 619 | if value.any? |
| @@ -621,46 +629,58 class Query < ActiveRecord::Base | |||
|
|
621 | 629 | sql = "#{db_table}.#{db_field} IS NOT NULL" |
|
|
622 | 630 | sql << " AND #{db_table}.#{db_field} <> ''" if is_custom_filter |
|
|
623 | 631 | when ">=" |
|
|
624 | if is_custom_filter | |
|
|
625 | sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) >= #{value.first.to_i}" | |
|
|
632 | if [:date, :date_past].include?(type_for(field)) | |
|
|
633 | sql = date_clause(db_table, db_field, (Date.parse(value.first) rescue nil), nil) | |
|
|
626 | 634 | else |
|
|
627 | sql = "#{db_table}.#{db_field} >= #{value.first.to_i}" | |
|
|
635 | if is_custom_filter | |
|
|
636 | sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) >= #{value.first.to_i}" | |
|
|
637 | else | |
|
|
638 | sql = "#{db_table}.#{db_field} >= #{value.first.to_i}" | |
|
|
639 | end | |
|
|
628 | 640 | end |
|
|
629 | 641 | when "<=" |
|
|
630 | if is_custom_filter | |
|
|
631 | sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) <= #{value.first.to_i}" | |
|
|
642 | if [:date, :date_past].include?(type_for(field)) | |
|
|
643 | sql = date_clause(db_table, db_field, nil, (Date.parse(value.first) rescue nil)) | |
|
|
632 | 644 | else |
|
|
633 | sql = "#{db_table}.#{db_field} <= #{value.first.to_i}" | |
|
|
645 | if is_custom_filter | |
|
|
646 | sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) <= #{value.first.to_i}" | |
|
|
647 | else | |
|
|
648 | sql = "#{db_table}.#{db_field} <= #{value.first.to_i}" | |
|
|
649 | end | |
|
|
634 | 650 | end |
|
|
635 | 651 | when "><" |
|
|
636 | 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}" | |
|
|
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)) | |
|
|
638 | 654 | else |
|
|
639 | sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_i} AND #{value[1].to_i}" | |
|
|
655 | if is_custom_filter | |
|
|
656 | sql = "CAST(#{db_table}.#{db_field} AS decimal(60,3)) BETWEEN #{value[0].to_i} AND #{value[1].to_i}" | |
|
|
657 | else | |
|
|
658 | sql = "#{db_table}.#{db_field} BETWEEN #{value[0].to_i} AND #{value[1].to_i}" | |
|
|
659 | end | |
|
|
640 | 660 | end |
|
|
641 | 661 | when "o" |
|
|
642 | 662 | sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_false}" if field == "status_id" |
|
|
643 | 663 | when "c" |
|
|
644 | 664 | sql = "#{IssueStatus.table_name}.is_closed=#{connection.quoted_true}" if field == "status_id" |
|
|
645 | 665 | when ">t-" |
|
|
646 |
sql = |
|
|
|
666 | sql = relative_date_clause(db_table, db_field, - value.first.to_i, 0) | |
|
|
647 | 667 | when "<t-" |
|
|
648 |
sql = |
|
|
|
668 | sql = relative_date_clause(db_table, db_field, nil, - value.first.to_i) | |
|
|
649 | 669 | when "t-" |
|
|
650 |
sql = |
|
|
|
670 | sql = relative_date_clause(db_table, db_field, - value.first.to_i, - value.first.to_i) | |
|
|
651 | 671 | when ">t+" |
|
|
652 |
sql = |
|
|
|
672 | sql = relative_date_clause(db_table, db_field, value.first.to_i, nil) | |
|
|
653 | 673 | when "<t+" |
|
|
654 |
sql = |
|
|
|
674 | sql = relative_date_clause(db_table, db_field, 0, value.first.to_i) | |
|
|
655 | 675 | when "t+" |
|
|
656 |
sql = |
|
|
|
676 | sql = relative_date_clause(db_table, db_field, value.first.to_i, value.first.to_i) | |
|
|
657 | 677 | when "t" |
|
|
658 |
sql = |
|
|
|
678 | sql = relative_date_clause(db_table, db_field, 0, 0) | |
|
|
659 | 679 | when "w" |
|
|
660 | 680 | first_day_of_week = l(:general_first_day_of_week).to_i |
|
|
661 | 681 | day_of_week = Date.today.cwday |
|
|
662 | 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 | 684 | when "~" |
|
|
665 | 685 | sql = "LOWER(#{db_table}.#{db_field}) LIKE '%#{connection.quote_string(value.first.to_s.downcase)}%'" |
|
|
666 | 686 | when "!~" |
| @@ -696,16 +716,21 class Query < ActiveRecord::Base | |||
|
|
696 | 716 | @available_filters["cf_#{field.id}"] = options.merge({ :name => field.name }) |
|
|
697 | 717 | end |
|
|
698 | 718 | end |
|
|
699 | ||
|
|
719 | ||
|
|
700 | 720 | # Returns a SQL clause for a date or datetime field. |
|
|
701 |
def date_ |
|
|
|
721 | def date_clause(table, field, from, to) | |
|
|
702 | 722 | s = [] |
|
|
703 | 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 | 725 | end |
|
|
706 | 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 | 728 | end |
|
|
709 | 729 | s.join(' AND ') |
|
|
710 | 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 | 736 | end |
| @@ -1,37 +1,53 | |||
|
|
1 | 1 | <script type="text/javascript"> |
|
|
2 | 2 | //<![CDATA[ |
|
|
3 | 3 | function add_filter() { |
|
|
4 |
|
|
|
|
5 |
|
|
|
|
6 |
|
|
|
|
7 |
|
|
|
|
8 |
|
|
|
|
9 |
|
|
|
|
10 |
|
|
|
|
11 | ||
|
|
12 |
|
|
|
|
13 |
|
|
|
|
14 |
|
|
|
|
15 |
|
|
|
|
16 |
|
|
|
|
4 | select = $('add_filter_select'); | |
|
|
5 | field = select.value | |
|
|
6 | Element.show('tr_' + field); | |
|
|
7 | check_box = $('cb_' + field); | |
|
|
8 | check_box.checked = true; | |
|
|
9 | toggle_filter(field); | |
|
|
10 | select.selectedIndex = 0; | |
|
|
11 | ||
|
|
12 | for (i=0; i<select.options.length; i++) { | |
|
|
13 | if (select.options[i].value == field) { | |
|
|
14 | select.options[i].disabled = true; | |
|
|
15 | } | |
|
|
16 | } | |
|
|
17 | 17 | } |
|
|
18 | 18 | |
|
|
19 | 19 | function toggle_filter(field) { |
|
|
20 |
|
|
|
|
21 | ||
|
|
22 | if (check_box.checked) { | |
|
|
23 |
|
|
|
|
24 | Form.Element.enable("operators_" + field); | |
|
|
25 | $$(".values_" + field).each(function(el){ Form.Element.enable(el)}); | |
|
|
26 |
|
|
|
|
27 | } else { | |
|
|
28 | Element.hide("operators_" + field); | |
|
|
29 | Element.hide("div_values_" + field); | |
|
|
30 | Form.Element.disable("operators_" + field); | |
|
|
31 | $$(".values_" + field).each(function(el){ Form.Element.disable(el)}); | |
|
|
20 | check_box = $('cb_' + field); | |
|
|
21 | if (check_box.checked) { | |
|
|
22 | Element.show("operators_" + field); | |
|
|
23 | Form.Element.enable("operators_" + field); | |
|
|
24 | toggle_operator(field); | |
|
|
25 | } else { | |
|
|
26 | Element.hide("operators_" + field); | |
|
|
27 | Form.Element.disable("operators_" + field); | |
|
|
28 | enableValues(field, []); | |
|
|
32 | 29 | } |
|
|
33 | 30 | } |
|
|
34 | 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); | |
|
|
48 | } | |
|
|
49 | } | |
|
|
50 | ||
|
|
35 | 51 | function toggle_operator(field) { |
|
|
36 | 52 | operator = $("operators_" + field); |
|
|
37 | 53 | switch (operator.value) { |
| @@ -41,30 +57,32 function toggle_operator(field) { | |||
|
|
41 | 57 | case "w": |
|
|
42 | 58 | case "o": |
|
|
43 | 59 | case "c": |
|
|
44 | Element.hide("div_values_" + field); | |
|
|
45 | var v = $$(".values_" + field); | |
|
|
46 | if (v.length > 1) {v[1].hide(); Form.Element.disable(v[1])} | |
|
|
60 | enableValues(field, []); | |
|
|
47 | 61 | break; |
|
|
48 | 62 | case "><": |
|
|
49 | Element.show("div_values_" + field); | |
|
|
50 | var v = $$(".values_" + field); | |
|
|
51 | if (v.length > 1) {v[1].show(); Form.Element.enable(v[1])} | |
|
|
63 | enableValues(field, [0,1]); | |
|
|
64 | break; | |
|
|
65 | case "<t+": | |
|
|
66 | case ">t+": | |
|
|
67 | case "t+": | |
|
|
68 | case ">t-": | |
|
|
69 | case "<t-": | |
|
|
70 | case "t-": | |
|
|
71 | enableValues(field, [2]); | |
|
|
52 | 72 | break; |
|
|
53 | 73 | default: |
|
|
54 | Element.show("div_values_" + field); | |
|
|
55 | var v = $$(".values_" + field); | |
|
|
56 | if (v.length > 1) {v[1].hide(); Form.Element.disable(v[1])} | |
|
|
74 | enableValues(field, [0]); | |
|
|
57 | 75 | break; |
|
|
58 | 76 | } |
|
|
59 | 77 | } |
|
|
60 | 78 | |
|
|
61 |
function toggle_multi_select( |
|
|
|
62 |
|
|
|
|
63 |
|
|
|
|
64 |
|
|
|
|
65 |
|
|
|
|
66 |
|
|
|
|
67 |
|
|
|
|
79 | function toggle_multi_select(el) { | |
|
|
80 | var select = $(el); | |
|
|
81 | if (select.multiple == true) { | |
|
|
82 | select.multiple = false; | |
|
|
83 | } else { | |
|
|
84 | select.multiple = true; | |
|
|
85 | } | |
|
|
68 | 86 | } |
|
|
69 | 87 | |
|
|
70 | 88 | function submit_query_form(id) { |
| @@ -102,15 +120,19 Event.observe(document,"dom:loaded", apply_filters_observer); | |||
|
|
102 | 120 | <div id="div_values_<%= field %>" style="display:none;"> |
|
|
103 | 121 | <% case options[:type] |
|
|
104 | 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) %> | |
|
|
106 | <%= link_to_function image_tag('bullet_toggle_plus.png'), "toggle_multi_select('#{field}');", :style => "vertical-align: bottom;" %> | |
|
|
123 | <span class="span_values_<%= field %>"> | |
|
|
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 | 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 | 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 | 133 | <% when :integer %> |
|
|
112 | <%= text_field_tag "v[#{field}][]", query.value_for(field), :class => "values_#{field}", :size => 3 %> | |
|
|
113 | <%= text_field_tag "v[#{field}][]", query.value_for(field, 1), :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> | |
|
|
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 | 136 | <% end %> |
|
|
115 | 137 | </div> |
|
|
116 | 138 | <script type="text/javascript">toggle_filter('<%= field %>');</script> |
| @@ -146,6 +146,34 class QueryTest < ActiveSupport::TestCase | |||
|
|
146 | 146 | find_issues_with_query(query) |
|
|
147 | 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 | 177 | def test_operator_in_more_than |
|
|
150 | 178 | Issue.find(7).update_attribute(:due_date, (Date.today + 15)) |
|
|
151 | 179 | query = Query.new(:project => Project.find(1), :name => '_') |
General Comments 0
You need to be logged in to leave comments.
Login now