If someone leaves an update on a problem, count that as "refreshing" the

problem. Rename db field to lastupdate to make this all clearer. Tidy up
some code.

8 files changed, 34 insertions, 56 deletions

diff --git a/bin/send-reports b/bin/send-reports
index 06aef0d4b..d3089a03c 100755
--- a/bin/send-reports
+++ b/bin/send-reports
@@ -6,7 +6,7 @@
 # Copyright (c) 2007 UK Citizens Online Democracy. All rights reserved.
 # Email: matthew@mysociety.org. WWW: http://www.mysociety.org
 #
-# $Id: send-reports,v 1.37 2007-05-11 09:02:53 matthew Exp $
+# $Id: send-reports,v 1.38 2007-05-15 13:43:20 matthew Exp $
 
 use strict;
 require 5.8.0;
@@ -131,7 +131,7 @@ If you know of an appropriate contact address, please do get in touch. ]\n\n";
     }
     if ($result == mySociety::Util::EMAIL_SUCCESS) {
         dbh()->do('UPDATE problem SET whensent=ms_current_timestamp(),
-            laststatechange=ms_current_timestamp() WHERE id=?', {}, $row->{id});
+            lastupdate=ms_current_timestamp() WHERE id=?', {}, $row->{id});
         dbh()->commit();
     } else {
         dbh()->rollback();
diff --git a/db/schema.sql b/db/schema.sql
index 4b4334000..0b8169b79 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -4,7 +4,7 @@
 -- Copyright (c) 2006 UK Citizens Online Democracy. All rights reserved.
 -- Email: matthew@mysociety.org; WWW: http://www.mysociety.org/
 --
--- $Id: schema.sql,v 1.30 2007-05-09 16:54:48 matthew Exp $
+-- $Id: schema.sql,v 1.31 2007-05-15 13:43:20 matthew Exp $
 --
 
 -- secret
@@ -145,7 +145,7 @@ create table problem (
         or state = 'fixed'
         or state = 'hidden'
     ),
-    laststatechange timestamp not null default ms_current_timestamp(),
+    lastupdate timestamp not null default ms_current_timestamp(),
     whensent timestamp,
     send_questionnaire boolean not null default 't'
 );
@@ -226,7 +226,7 @@ create table comment (
         or state = 'hidden'
     ),
     mark_fixed boolean not null,
-    mark_open boolean not null
+    mark_open boolean not null default 'f'
     -- other fields? one to indicate whether this was written by the council
     -- and should be highlighted in the display?
 );
diff --git a/perllib/Page.pm b/perllib/Page.pm
index e65a08ca0..44e78ed92 100644
--- a/perllib/Page.pm
+++ b/perllib/Page.pm
@@ -6,7 +6,7 @@
 # Copyright (c) 2006 UK Citizens Online Democracy. All rights reserved.
 # Email: matthew@mysociety.org; WWW: http://www.mysociety.org/
 #
-# $Id: Page.pm,v 1.48 2007-05-15 12:40:48 matthew Exp $
+# $Id: Page.pm,v 1.49 2007-05-15 13:43:21 matthew Exp $
 #
 
 package Page;
@@ -414,7 +414,7 @@ sub display_problem_updates {
         foreach my $row (@$updates) {
             $out .= "<div><a name=\"update_$row->{id}\"></a><em>";
             if ($row->{name}) {
-                $out .= "Posted by $row->{name}";
+                $out .= 'Posted by ' . ent($row->{name});
             } else {
                 $out .= "Posted anonymously";
             }
@@ -422,7 +422,7 @@ sub display_problem_updates {
             $out .= ', marked fixed' if ($row->{mark_fixed});
             $out .= ', reopened' if ($row->{mark_open});
             $out .= '</em>';
-            $out .= '<br>' . $row->{text} . '</div>';
+            $out .= '<br>' . ent($row->{text}) . '</div>';
         }
         $out .= '</div>';
     }
diff --git a/web/confirm.cgi b/web/confirm.cgi
index dfb3bfa95..b83452457 100755
--- a/web/confirm.cgi
+++ b/web/confirm.cgi
@@ -6,7 +6,7 @@
 # Copyright (c) 2006 UK Citizens Online Democracy. All rights reserved.
 # Email: matthew@mysociety.org. WWW: http://www.mysociety.org
 #
-# $Id: confirm.cgi,v 1.16 2007-05-09 11:01:44 matthew Exp $
+# $Id: confirm.cgi,v 1.17 2007-05-15 13:43:21 matthew Exp $
 
 use strict;
 require 5.8.0;
@@ -45,14 +45,14 @@ sub main {
         if ($type eq 'update') {
             dbh()->do("update comment set state='confirmed' where id=? and state='unconfirmed'", {}, $id);
             my ($email) = dbh()->selectrow_array("select email from comment where id=?", {}, $id);
-            my ($problem_id, $fixed, $reopen) = dbh()->selectrow_array("select problem_id,mark_fixed,mark_open from comment where id=?", {}, $id);
+            my ($problem_id, $fixed) = dbh()->selectrow_array("select problem_id,mark_fixed from comment where id=?", {}, $id);
             if ($fixed) {
-                dbh()->do("update problem set state='fixed', laststatechange = ms_current_timestamp()
+                dbh()->do("update problem set state='fixed', lastupdate = ms_current_timestamp()
                     where id=? and state='confirmed'", {}, $problem_id);
-            } elsif ($reopen) {
-                dbh()->do("update problem set state='confirmed', laststatechange = ms_current_timestamp()
-                    where id=? and state='fixed'", {}, $problem_id);
-            }
+            } else {
+                dbh()->do("update problem set lastupdate = ms_current_timestamp()
+                    where id=?", {}, $problem_id);
+	    }
             my $salt = unpack('h*', random_bytes(8));
             my $secret = scalar(dbh()->selectrow_array('select secret from secret'));
             my $signed_email = sha1_hex("$problem_id-$email-$salt-$secret");
@@ -68,7 +68,7 @@ EOF
             $out .= $q->p(sprintf(_('You could also <a href="%s">subscribe to the RSS feed</a> of updates by other local people on this problem, or %s if you wish to receive updates by email.'), "/rss/$problem_id", $signup));
             $out .= '</form>';
         } elsif ($type eq 'problem') {
-            dbh()->do("update problem set state='confirmed', confirmed=ms_current_timestamp(), laststatechange=ms_current_timestamp()
+            dbh()->do("update problem set state='confirmed', confirmed=ms_current_timestamp(), lastupdate=ms_current_timestamp()
                 where id=? and state='unconfirmed'", {}, $id);
             my ($email, $council) = dbh()->selectrow_array("select email, council from problem where id=?", {}, $id);
             my $salt = unpack('h*', random_bytes(8));
diff --git a/web/faq.cgi b/web/faq.cgi
index 900ee6dcc..dc2837808 100755
--- a/web/faq.cgi
+++ b/web/faq.cgi
@@ -6,7 +6,7 @@
 # Copyright (c) 2006 UK Citizens Online Democracy. All rights reserved.
 # Email: matthew@mysociety.org. WWW: http://www.mysociety.org
 #
-# $Id: faq.cgi,v 1.17 2007-05-08 11:30:31 matthew Exp $
+# $Id: faq.cgi,v 1.18 2007-05-15 13:43:21 matthew Exp $
 
 use strict;
 require 5.8.0;
@@ -63,9 +63,9 @@ to update the address or addresses we use.')),
         $q->dt(_('Do you remove silly or illegal content?')),
         $q->dd(_('We reserve the right to remove any problems or updates
 which we consider to be inappropriate.')),
-        $q->dt(_("Why doesn't dragging the map work on reporting-a-problem pages in Safari?")),
-        $q->dd(_("There's a bug in Safari to do with setting images on form
-submits, which the map is when reporting a problem. It's fixed in the
+        $q->dt(_("Why doesn't dragging the map work on reporting-a-problem pages in Safari or Konqueror?")),
+        $q->dd(_("There's a bug in these two browsers to do with setting images on form
+submit buttons, which the map uses when reporting a problem. It's fixed in the
 latest nightly build of Safari, so will presumably be fixed in the next
 release. Until then, I've sadly had to disable dragging to avoid people
 dragging an empty square."))
diff --git a/web/index.cgi b/web/index.cgi
index 2df58abe7..292c0699a 100755
--- a/web/index.cgi
+++ b/web/index.cgi
@@ -6,7 +6,7 @@
 # Copyright (c) 2006 UK Citizens Online Democracy. All rights reserved.
 # Email: matthew@mysociety.org. WWW: http://www.mysociety.org
 #
-# $Id: index.cgi,v 1.132 2007-05-15 12:40:51 matthew Exp $
+# $Id: index.cgi,v 1.133 2007-05-15 13:43:21 matthew Exp $
 
 use strict;
 require 5.8.0;
@@ -130,8 +130,8 @@ sub submit_update {
 
     my $id = dbh()->selectrow_array("select nextval('comment_id_seq');");
     dbh()->do("insert into comment
-        (id, problem_id, name, email, website, text, state, mark_fixed, mark_open)
-        values (?, ?, ?, ?, ?, ?, 'unconfirmed', ?, 'f')", {},
+        (id, problem_id, name, email, website, text, state, mark_fixed)
+        values (?, ?, ?, ?, ?, ?, 'unconfirmed', ?)", {},
         $id, $input{id}, $input{name}, $input{email}, '', $input{update},
         $input{fixed}?'t':'f');
     my %h = ();
@@ -553,7 +553,7 @@ sub display_problem {
     my $problem = dbh()->selectrow_hashref(
         "select state, easting, northing, title, detail, name, extract(epoch from confirmed) as time, photo, anonymous,
          extract(epoch from whensent-confirmed) as whensent, council, id,
-         extract(epoch from ms_current_timestamp()-laststatechange) as duration
+         extract(epoch from ms_current_timestamp()-lastupdate) as duration
          from problem where id=? and state in ('confirmed','fixed', 'hidden')", {}, $input{id});
     return display_location($q, 'Unknown problem ID') unless $problem;
     return front_page($q, 'That problem has been hidden from public view as it contained inappropriate public details') if $problem->{state} eq 'hidden';
@@ -594,29 +594,7 @@ sub display_problem {
 </form>
 EOF
 
-    # Display updates
-    my $updates = select_all(
-        "select id, name, extract(epoch from created) as created, text, mark_fixed, mark_open
-         from comment where problem_id = ? and state='confirmed'
-         order by created", $input{id});
-    if (@$updates) {
-        $out .= '<div id="updates">';
-        $out .= '<h2>Updates</h2>';
-        foreach my $row (@$updates) {
-            $out .= "<div><a name=\"update_$row->{id}\"></a><em>";
-            if ($row->{name}) {
-                $out .= "Posted by " . ent($row->{name});
-            } else {
-                $out .= "Posted anonymously";
-            }
-            $out .= " at " . Page::prettify_epoch($row->{created});
-            $out .= ', marked fixed' if ($row->{mark_fixed});
-            $out .= ', reopened' if ($row->{mark_open});
-            $out .= '</em>';
-            $out .= '<br>' . ent($row->{text}) . '</div>';
-        }
-        $out .= '</div>';
-    }
+    $out .= Page::display_problem_updates($input{id});
     $out .= '<h2>Provide an update</h2>';
     $out .= $q->p($q->small('Please note that updates are not sent to the council.'));
     if (@errors) {
@@ -677,7 +655,7 @@ sub map_pins {
         $pins .= Page::display_pin($q, $px, $py, 'red', $count_prob++);
     }
 
-    # XXX: Change to only show problems with extract(epoch from ms_current_timestamp()-laststatechange) < 8 weeks
+    # XXX: Change to only show problems with extract(epoch from ms_current_timestamp()-lastupdate) < 8 weeks
     # And somehow display/link to old problems somewhere else...
     my $current = [];
     if (@$current_map < 9) {
diff --git a/web/questionnaire.cgi b/web/questionnaire.cgi
index fd47ea8dd..6263e20e5 100755
--- a/web/questionnaire.cgi
+++ b/web/questionnaire.cgi
@@ -6,7 +6,7 @@
 # Copyright (c) 2007 UK Citizens Online Democracy. All rights reserved.
 # Email: matthew@mysociety.org. WWW: http://www.mysociety.org
 #
-# $Id: questionnaire.cgi,v 1.7 2007-05-15 11:04:02 matthew Exp $
+# $Id: questionnaire.cgi,v 1.8 2007-05-15 13:43:21 matthew Exp $
 
 use strict;
 require 5.8.0;
@@ -101,12 +101,12 @@ sub submit_questionnaire {
     $new_state = 'confirmed' if $input{been_fixed} eq 'No' && $problem->{state} eq 'fixed';
 
     # Record state change, if there was one
-    dbh()->do("update problem set state=?, laststatechange=ms_current_timestamp()
+    dbh()->do("update problem set state=?, lastupdate=ms_current_timestamp()
         where id=?", {}, $new_state, $problem->{id})
         if $new_state;
 
     # If it's not fixed and they say it's still not been fixed, record time update
-    dbh()->do("update problem set laststatechange=ms_current_timestamp()
+    dbh()->do("update problem set lastupdate=ms_current_timestamp()
         where id=?", {}, $problem->{id})
         if $input{been_fixed} eq 'No' && $problem->{state} eq 'confirmed';
 
diff --git a/web/report.cgi b/web/report.cgi
index 8189573a5..462e16081 100755
--- a/web/report.cgi
+++ b/web/report.cgi
@@ -6,7 +6,7 @@
 # Copyright (c) 2007 UK Citizens Online Democracy. All rights reserved.
 # Email: matthew@mysociety.org. WWW: http://www.mysociety.org
 #
-# $Id: report.cgi,v 1.28 2007-05-14 21:32:32 matthew Exp $
+# $Id: report.cgi,v 1.29 2007-05-15 13:43:21 matthew Exp $
 
 use strict;
 require 5.8.0;
@@ -47,9 +47,9 @@ sub main {
     }
     my (%fixed, %open, %councils);
     my $problem = select_all(
-        "select id, title, detail, council, state, laststatechange, whensent,
-        extract(epoch from ms_current_timestamp()-laststatechange) as duration,
-        extract(epoch from laststatechange-confirmed) as laststateage
+        "select id, title, detail, council, state,
+        extract(epoch from ms_current_timestamp()-lastupdate) as duration,
+        extract(epoch from lastupdate-confirmed) as timetolastupdate
         from problem
         where state in ('confirmed', 'fixed')
             and whensent is not null
@@ -62,7 +62,7 @@ sub main {
         my @council = split /,/, $council;
         my $type = ($row->{duration} > 2 * $fourweeks)
             ? 'unknown'
-            : ($row->{laststateage} > $fourweeks ? 'ongoing' : 'new');
+            : ($row->{timetolastupdate} > $fourweeks ? 'ongoing' : 'new');
         my $duration = ($row->{duration} > 2 * $fourweeks) ? 'old' : 'new';
         foreach (@council) {
             next if $one_council && $_ != $one_council;