Bug 37749 - query for changes to specific field in last n days not working. Rearrange time-based query UI to be more sane. Patch by mailto:anthony@itia.ntua.gr; r=gerv.

4 files changed, 116 insertions, 100 deletions

diff --git a/Bugzilla/Search.pm b/Bugzilla/Search.pm
index 171a1b824..45c26fdf2 100644
--- a/Bugzilla/Search.pm
+++ b/Bugzilla/Search.pm
@@ -213,64 +213,65 @@ sub init {
         }
     }
 
-
-    if (defined $params->param('changedin')) {
-        my $c = trim($params->param('changedin'));
-        if ($c ne "") {
-            if ($c !~ /^[0-9]*$/) {
-                ThrowUserError("illegal_changed_in_last_x_days",
-                                 { value => $c });
-            }
-            push(@specialchart, ["changedin",
-                                 "lessthan", $c + 1]);
+    # 2003-05-20: The 'changedin' field is no longer in the UI, but we continue
+    # to process it because it will appear in stored queries and bookmarks.
+    my $changedin = $params->param('changedin') || '';
+    $changedin = trim($changedin);
+    if ($changedin) {
+        if ($changedin !~ /^[0-9]*$/) {
+            ThrowUserError("illegal_changed_in_last_x_days",
+                              { value => $changedin });
         }
+        push(@specialchart, ["changedin", "lessthan", $changedin + 1]);
     }
 
+    my $chfieldfrom = $params->param('chfieldfrom') || '';
+    my $chfieldto = $params->param('chfieldto') || '';
     my @chfield = $params->param('chfield');
-
-    if (@chfield) {
-        my $which = lsearch(\@chfield, "[Bug creation]");
-        if ($which >= 0) {
-            splice(@chfield, $which, 1);
-            push(@specialchart, ["creation_ts", "greaterthan",
-                                 SqlifyDate($params->param('chfieldfrom'))]);
-            my $to = $params->param('chfieldto');
-            if (defined $to) {
-                $to = trim($to);
-                if ($to ne "" && $to !~ /^now$/i) {
-                    push(@specialchart, ["creation_ts", "lessthan",
-                                         SqlifyDate($to)]);
+    my $chvalue = $params->param('chfieldvalue') || '';
+    my $lcchfieldfrom = trim(lc($chfieldfrom));
+    my $lcchfieldto = trim(lc($chfieldto));
+    $chvalue = trim($chvalue);
+
+    $lcchfieldfrom = '' if ($lcchfieldfrom eq 'now');
+    $lcchfieldto = '' if ($lcchfieldto eq 'now');
+    if ($lcchfieldfrom ne '' || $lcchfieldto ne '') {
+        my $sql_chfrom = $lcchfieldfrom ? &::SqlQuote(SqlifyDate($lcchfieldfrom)):'';
+        my $sql_chto   = $lcchfieldto   ? &::SqlQuote(SqlifyDate($lcchfieldto))  :'';
+        my $sql_chvalue = $chvalue ne '' ? &::SqlQuote($chvalue) : '';
+        if(!@chfield) {
+            push(@wherepart, "bugs.delta_ts >= $sql_chfrom") if ($sql_chfrom);
+            push(@wherepart, "bugs.delta_ts <= $sql_chto") if ($sql_chto);
+        } else {
+            push(@supptables, "bugs_activity actcheck");
+            my $sql_bugschanged = '';
+            my @list;
+            foreach my $f (@chfield) {
+                if ($f eq "[Bug creation]") {
+                    my @l;
+                    push(@l, "creation_ts >= $sql_chfrom") if($sql_chfrom);
+                    push(@l, "creation_ts <= $sql_chto") if($sql_chto);
+                    $sql_bugschanged = "(" . join(' AND ', @l) . ")";
+                } else {
+                    push(@list, "\nactcheck.fieldid = " . &::GetFieldID($f));
                 }
             }
-        }
-    }
-
-    if (@chfield) {
-        push(@supptables, "bugs_activity actcheck");
-
-        my @list;
-        foreach my $f (@chfield) {
-            push(@list, "\nactcheck.fieldid = " . &::GetFieldID($f));
-        }
-        push(@wherepart, "actcheck.bug_id = bugs.bug_id");
-        push(@wherepart, "(" . join(' OR ', @list) . ")");
-        push(@wherepart, "actcheck.bug_when >= " .
-             &::SqlQuote(SqlifyDate($params->param('chfieldfrom'))));
-        my $to = $params->param('chfieldto');
-        if (defined $to) {
-            $to = trim($to);
-            if ($to ne "" && $to !~ /^now$/i) {
-                push(@wherepart, "actcheck.bug_when <= " .
-                     &::SqlQuote(SqlifyDate($to)));
-            }
-        }
-        my $value = $params->param('chfieldvalue');
-        if (defined $value) {
-            $value = trim($value);
-            if ($value ne "") {
-                push(@wherepart, "actcheck.added = " .
-                     &::SqlQuote($value))
+            if(@list) {
+                $sql_bugschanged .= ' OR ' if($sql_bugschanged ne '');
+                $sql_bugschanged .= "(actcheck.bug_id = bugs.bug_id AND " .
+                                       "(" . join(' OR ', @list) . ")";
+                if($sql_chfrom) {
+                    $sql_bugschanged .= " AND actcheck.bug_when >= $sql_chfrom";
+                }
+                if($sql_chto) {
+                    $sql_bugschanged .= " AND actcheck.bug_when <= $sql_chto";
+                }
+                if($sql_chvalue) {
+                    $sql_bugschanged .= " AND actcheck.added = $sql_chvalue";
+                }
+                $sql_bugschanged .= ')';
             }
+            push(@wherepart, "($sql_bugschanged)");
         }
     }
 
diff --git a/queryhelp.cgi b/queryhelp.cgi
index a4aff1d07..a0e1178aa 100755
--- a/queryhelp.cgi
+++ b/queryhelp.cgi
@@ -800,28 +800,51 @@ bugs numbered:
 <INPUT TYPE="text" NAME="bug_id" VALUE="" SIZE=15>
 </td>
 </tr>
+<tr><td>&nbsp;</td></tr>
 <tr>
-<td>
-Changed in the last <INPUT NAME=changedin SIZE=3 VALUE=""> days
+<td colspan=2>
+Only bugs changed between <INPUT NAME="chfieldfrom" SIZE="10" VALUE="">
+and <INPUT NAME="chfieldto" SIZE="10" VALUE="Now">
 </td>
 </tr>
-<tr>
+<tr valign="top">
 <td>
-Containing at least <INPUT NAME=votes SIZE=3 VALUE=""> votes
+Where one or more of the following changed:
+</td><td>
+<SELECT NAME="chfield" MULTIPLE SIZE=4>
+    <OPTION VALUE="[Bug creation]">[Bug creation]
+    <OPTION VALUE="assigned_to">assigned_to
+    <OPTION VALUE="bug_file_loc">bug_file_loc
+    <OPTION VALUE="bug_severity">bug_severity
+    <OPTION VALUE="bug_status">bug_status
+    <OPTION VALUE="component">component
+    <OPTION VALUE="everconfirmed">everconfirmed
+    <OPTION VALUE="groupset">groupset
+    <OPTION VALUE="keywords">keywords
+    <OPTION VALUE="op_sys">op_sys
+    <OPTION VALUE="priority">priority
+    <OPTION VALUE="product">product
+    <OPTION VALUE="qa_contact">qa_contact
+    <OPTION VALUE="rep_platform">rep_platform
+    <OPTION VALUE="reporter">reporter
+    <OPTION VALUE="resolution">resolution
+    <OPTION VALUE="short_desc">short_desc
+    <OPTION VALUE="status_whiteboard">status_whiteboard
+    <OPTION VALUE="target_milestone">target_milestone
+    <OPTION VALUE="version">version
+    <OPTION VALUE="votes">votes
+</SELECT>
 </td>
 </tr>
 <tr>
 <td>
-Where the field(s)
-<SELECT NAME="chfield" MULTIPLE SIZE=4>
-<OPTION VALUE="[Bug creation]">[Bug creation]<OPTION VALUE="assigned_to">assigned_to<OPTION VALUE="bug_file_loc">bug_file_loc<OPTION VALUE="bug_severity">bug_severity<OPTION VALUE="bug_status">bug_status<OPTION VALUE="component">component<OPTION VALUE="everconfirmed">everconfirmed<OPTION VALUE="groupset">groupset<OPTION VALUE="keywords">keywords<OPTION VALUE="op_sys">op_sys<OPTION VALUE="priority">priority<OPTION VALUE="product">product<OPTION VALUE="qa_contact">qa_contact<OPTION VALUE="rep_platform">rep_platform<OPTION VALUE="reporter">reporter<OPTION VALUE="resolution">resolution<OPTION VALUE="short_desc">short_desc<OPTION VALUE="status_whiteboard">status_whiteboard<OPTION VALUE="target_milestone">target_milestone<OPTION VALUE="version">version<OPTION VALUE="votes">votes
-</SELECT> changed to <INPUT NAME="chfieldvalue" SIZE="10">
+and the result was: <INPUT NAME="chfieldvalue" SIZE="10">
 </td>
 </tr>
+<tr><td>&nbsp;</td></tr>
 <tr>
-<td colspan=2>
-During dates <INPUT NAME="chfieldfrom" SIZE="10" VALUE="">
-to <INPUT NAME="chfieldto" SIZE="10" VALUE="Now">
+<td>
+Containing at least <INPUT NAME=votes SIZE=3 VALUE=""> votes
 </td>
 </tr>
 <tr>
@@ -847,29 +870,23 @@ exclude bugs based on values you enter.
 of which you want to exclude. It would be nice in the future if you could type in ranges, i.e. [1-1000] for 1
 to 1000. Unfortunately, you cannot do that as of now.
 
-<p><b>Changed in the last [text] days</b>
-
-<p>Lets you specify how many days ago - at maximum - a bug could have changed state.
-
 <p><b>At least [text] votes</b>
 
 <p>With this, you can choose how many votes - at minimum - a bug has. 
 
-<p><b>Where the field(s) [fields] changed to [text]</b>
+<a name="changedbetween">
+<p><b>Only bugs changed between</b></p>
 
-<p>With this, you can specify values to search for in fields that exist in the bug If you choose 
-one or more fields, you have to fill out one of the fields to the right. It might 
-be difficult to figure out what these fields mean if you are a newbie to the query. 
-They match various fields within the bug information. Optionally, you can 
-also enter what value you want the field to have changed to if you only entered one field.
-For instance, if the bug changed who it was assigned to from jon\@netscape.com 
-to brian\@netscape.com , you could enter in
-assigned_to changed to brian\@netscape.com.
+<p>Here you can choose what dates the bugs changed. "Now" can be used as an
+entry. Other entries should be in yyyy-mm-dd format, or in relative dates such
+as 1d or 2w or 3m or 4y, which respectively mean 1 day, 2 weeks, 3 months, 4
+years ago. 0d is last midnight, and 0w, 0m, 0y is the beginning of this week,
+month, or year.</p>
 
-<p><b>During dates [text] to [text] </b>
+<p><b>Where one or more of the following changed, and the result was</b></p>
 
-<p>Here, you can choose what dates the fields changed. "Now" can be used as an entry. Other entries should be in
-mm/dd/yyyy or yyyy-mm-dd format.
+<p>With this you can specify which bug fields changed, and optionally to what
+value, between the dates specified above. Leaving blank will match any change.</p>
 
 
 };
diff --git a/template/en/default/search/form.html.tmpl b/template/en/default/search/form.html.tmpl
index efc5dd0b2..3ebe01f05 100644
--- a/template/en/default/search/form.html.tmpl
+++ b/template/en/default/search/form.html.tmpl
@@ -726,12 +726,13 @@ function selectProduct(f) {
 
 
 <dl>
-  <dt>Only bugs changed in the last </dt>
-  <dd><input name="changedin" size="3" value="[% default.changedin.0 FILTER html %]"> days</dd>
-</dl>
-
-<dl>
-  <dt>Only bugs where any of the fields</dt>
+  <dt>Only bugs changed between:</dt>
+  <dd>
+    <input name="chfieldfrom" size="10" value="[% default.chfieldfrom.0 FILTER html %]">
+    and <input name="chfieldto" size="10" value="[% default.chfieldto.0 FILTER html %]">
+    <br>(YYYY-MM-DD or <a href="queryhelp.cgi#changedbetween">relative dates</a>)
+  </dd><br>
+  <dt>where one or more of the following changed:</dt>
   <dd>
     <select name="chfield" multiple="multiple" size="4">
     [% FOREACH field = chfield %]
@@ -740,21 +741,13 @@ function selectProduct(f) {
         [% (field_descs.$field || field) FILTER html %]</option>
     [% END %]
     </select>
-  </dd>
-
-  <dt>were changed between</dt>
-  <dd>
-    <input name="chfieldfrom" size="10" value="[% default.chfieldfrom.0 FILTER html %]">
-    and <input name="chfieldto" size="10" value="[% default.chfieldto.0 FILTER html %]">
-    <br>(YYYY-MM-DD)
-  </dd>
-  <dt>to this value: (optional)</dt>
+  </dd><br>
+  <dt>and the new value was:</dt>
   <dd>
     <input name="chfieldvalue" size="20" value="[% default.chfieldvalue.0 FILTER html %]">
-  </dd>
+  </dd><br>
 </dl>
 
-
        </fieldset>
      </td>
   </tr>
diff --git a/template/en/default/search/search-help.html.tmpl b/template/en/default/search/search-help.html.tmpl
index 557209690..852955533 100644
--- a/template/en/default/search/search-help.html.tmpl
+++ b/template/en/default/search/search-help.html.tmpl
@@ -81,16 +81,21 @@
 { id => "votes", 
   html => "Some bugs can be voted for, and you can limit your search to bugs<br>
            with more than a certain number of votes." },   
-{ id => "changedin", 
-  html => "You can search by when bugs have changed - this field defines the<br>
-           timeframe for the search." },   
 { id => "chfield", 
   html => "You can search for specific types of change - this field define <br>
            which field you are interested in changes for." },   
 { id => "chfieldfrom", 
-  html => "The start time of the timeframe for the change." },   
+  html => "Specify the start and end dates either in YYYY-MM-DD format<br>
+           (optionally followed by HH:mm, in 24 hour clock), or in relative<br>
+           dates such as 1d, 2w, 3m, 4y, which respectively mean one day,<br>
+           two weeks, three months, or four years ago. 0d is last midnight,<br>
+           and 0w, 0m, 0y is the beginning of this week, month, or year." },
 { id => "chfieldto", 
-  html => "The end time of the timeframe for the change." },   
+  html => "Specify the start and end dates either in YYYY-MM-DD format<br>
+           (optionally followed by HH:mm, in 24 hour clock), or in relative<br>
+           dates such as 1d, 2w, 3m, 4y, which respectively mean one day,<br>
+           two weeks, three months, or four years ago. 0d is last midnight,<br>
+           and 0w, 0m, 0y is the beginning of this week, month, or year." },
 { id => "chfieldvalue", 
   html => "The value the field defined above changed to during that time." },   
 ] %]