RSS/email alerts for updates, local problems.

12 files changed, 364 insertions, 75 deletions

diff --git a/bin/send-alerts b/bin/send-alerts
new file mode 100755
index 000000000..a9ccb8dba
--- /dev/null
+++ b/bin/send-alerts
@@ -0,0 +1,35 @@
+#!/usr/bin/perl -w
+
+# send-alerts:
+# Email alerts for Neighbourhood Fix-It
+#
+# Copyright (c) 2007 UK Citizens Online Democracy. All rights reserved.
+# Email: matthew@mysociety.org. WWW: http://www.mysociety.org
+#
+# $Id: send-alerts,v 1.1 2007-01-26 01:01:22 matthew Exp $
+
+use strict;
+require 5.8.0;
+
+# Horrible boilerplate to set up appropriate library paths.
+use FindBin;
+use lib "$FindBin::Bin/../perllib";
+use lib "$FindBin::Bin/../../perllib";
+
+use mySociety::Config;
+use mySociety::DBHandle qw(dbh);
+use mySociety::Alert;
+
+BEGIN {
+    mySociety::Config::set_file("$FindBin::Bin/../conf/general");
+    mySociety::DBHandle::configure(
+        Name => mySociety::Config::get('BCI_DB_NAME'),
+        User => mySociety::Config::get('BCI_DB_USER'),
+        Password => mySociety::Config::get('BCI_DB_PASS'),
+        Host => mySociety::Config::get('BCI_DB_HOST', undef),
+        Port => mySociety::Config::get('BCI_DB_PORT', undef)
+    );
+}
+
+mySociety::Alert::email_alerts();
+
diff --git a/bin/send-reports b/bin/send-reports
index 853b35b25..17ec5692c 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.1 2007-01-20 00:51:49 matthew Exp $
+# $Id: send-reports,v 1.2 2007-01-26 01:01:22 matthew Exp $
 
 use strict;
 require 5.8.0;
@@ -53,7 +53,7 @@ foreach my $row (@$unsent) {
     my $email = mySociety::Email::construct_email({
         _template_ => $template,
         _parameters_ => \%h,
-            From => [mySociety::Config::get('CONTACT_EMAIL'), 'Neighbourhood Fix-It'],
+        From => [mySociety::Config::get('CONTACT_EMAIL'), 'Neighbourhood Fix-It'],
         To => [[$council_email, $area_info->{name}]],
     });
 
diff --git a/conf/httpd.conf b/conf/httpd.conf
index 625a4ca8a..9d4ad9c65 100644
--- a/conf/httpd.conf
+++ b/conf/httpd.conf
@@ -20,7 +20,7 @@
 # Copyright (c) 2006 UK Citizens Online Democracy. All rights reserved.
 # Email: francis@mysociety.org; WWW: http://www.mysociety.org
 # 
-# $Id: httpd.conf,v 1.5 2006-09-28 11:06:42 matthew Exp $
+# $Id: httpd.conf,v 1.6 2007-01-26 01:01:23 matthew Exp $
 
 DirectoryIndex index.cgi
 
@@ -28,9 +28,14 @@ RewriteEngine on
 #RewriteLog /var/log/apache/rewrite.log
 #RewriteLogLevel 3
 
+RewriteRule ^/[Aa]/([0-9A-Za-z]{16}).*$ /alert.cgi?token=$1
 RewriteRule ^/[Cc]/([0-9A-Za-z]{16}).*$ /confirm.cgi?type=update;token=$1
 RewriteRule ^/[Pp]/([0-9A-Za-z]{16}).*$ /confirm.cgi?type=problem;token=$1
 
+RewriteRule ^/rss/([0-9]+)$              /rss.cgi?type=new_updates;id=$1 [QSA]
+RewriteRule ^/rss/([0-9]+)/([0-9]+)$     /rss.cgi?type=local_problems;x=$1;y=$2 [QSA]
+RewriteRule ^/rss/problems$              /rss.cgi?type=new_problems [QSA]
+
 ProxyPass /tilma/ http://tilma.mysociety.org/
 ProxyPassReverse /tilma/ http://tilma.mysociety.org/
 
diff --git a/db/alert_types.sql b/db/alert_types.sql
new file mode 100644
index 000000000..33a2ae093
--- /dev/null
+++ b/db/alert_types.sql
@@ -0,0 +1,24 @@
+insert into alert_type
+(ref, head_sql_query, head_table, head_title, head_link, head_description,
+    item_table, item_where, item_order, item_title, item_link, item_description, template)
+values ('new_updates', 'select * from problem where id=?', 'problem',
+    'Updates on {{title}}', '/?id={{id}}', 'Updates on {{title}}',
+    'comment', 'comment.state=\'confirmed\'', 'created desc',
+    'Update by {{name}}', '/?id={{problem_id}}#comment_{{id}}', '{{text}}', 'alert-update');
+
+insert into alert_type
+(ref, head_sql_query, head_table, head_title, head_link, head_description,
+    item_table, item_where, item_order, item_title, item_link, item_description, template)
+values ('new_problems', '', '',
+    'New problems on Neighbourhood Fix-It', '/', 'The latest problems reported by users',
+    'problem', 'problem.state in (\'confirmed\', \'fixed\')', 'created desc',
+    '{{title}}', '/?id={{id}}', '{{detail}}', 'alert-problem');
+
+insert into alert_type
+(ref, head_sql_query, head_table, head_title, head_link, head_description,
+    item_table, item_where, item_order, item_title, item_link, item_description, template)
+values ('local_problems', '', '',
+    'New local problems on Neighbourhood Fix-It', '/', 'The latest local problems reported by users',
+    'problem_find_nearby(?, ?, 10) as nearby,problem', 'nearby.problem_id = problem.id and problem.state in (\'confirmed\', \'fixed\')', 'created desc',
+    '{{title}}', '/?id={{id}}', '{{detail}}', 'alert-problem');
+
diff --git a/db/schema.sql b/db/schema.sql
index 408ed0da9..8ecaddacd 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.15 2007-01-24 20:55:30 matthew Exp $
+-- $Id: schema.sql,v 1.16 2007-01-26 01:01:23 matthew Exp $
 --
 
 -- secret
@@ -202,7 +202,7 @@ create table comment (
     name text not null,
     email text not null,
     website text,
-    whenposted timestamp not null default ms_current_timestamp(),
+    created timestamp not null default ms_current_timestamp(),
     text text not null,                     -- as entered by comment author
     state text not null check (
         state = 'unconfirmed'
@@ -216,7 +216,7 @@ create table comment (
 );
 
 create index comment_problem_id_idx on comment(problem_id);
-create index comment_problem_id_whenposted_idx on comment(problem_id, whenposted);
+create index comment_problem_id_created_idx on comment(problem_id, created);
 
 -- Tokens for confirmations
 create table token (
@@ -227,3 +227,38 @@ create table token (
     primary key (scope, token)
 );
 
+-- Alerts
+
+create table alert_type (
+    ref text not null primary key,
+    head_sql_query text not null,
+    head_table text not null,
+    head_title text not null,
+    head_link text not null,
+    head_description text not null,
+    item_table text not null,
+    item_where text not null,
+    item_order text not null,
+    item_title text not null,
+    item_link text not null,
+    item_description text not null
+);
+
+create table alert (
+    id serial not null primary key,
+    alert_type text not null references alert_type(ref),
+    parameter text, -- e.g. Problem ID for new updates
+    parameter2 text, -- e.g. Latitude for local problem alerts
+    email text not null,
+    confirmed integer not null default 0,
+    whensubscribed timestamp not null default ms_current_timestamp(),
+    whendisabled timestamp default null
+);
+-- Possible indexes - email, alert_type, whendisabled, unique (alert_type,email,parameter)
+
+create table alert_sent (
+    alert_id integer not null references alert(id),
+    parameter text, -- e.g. Update ID for new updates
+    whenqueued timestamp not null default ms_current_timestamp()
+);
+
diff --git a/perllib/Page.pm b/perllib/Page.pm
index 2837182a2..a0cbf984b 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.32 2007-01-24 20:55:30 matthew Exp $
+# $Id: Page.pm,v 1.33 2007-01-26 01:01:23 matthew Exp $
 #
 
 package Page;
@@ -15,7 +15,10 @@ use strict;
 use Carp;
 use CGI::Fast qw(-no_xhtml);
 use Error qw(:try);
+use File::Slurp;
 use mySociety::Config;
+use mySociety::Email;
+use mySociety::Util;
 use mySociety::WatchUpdate;
 use mySociety::Web qw(ent NewURL);
 BEGIN {
@@ -156,4 +159,34 @@ sub compass ($$$) {
 EOF
 }
 
+# send_email TO (NAME) TEMPLATE-NAME PARAMETERS
+sub send_email {
+    my ($email, $name, $template, %h) = @_;
+    $template = File::Slurp::read_file("$FindBin::Bin/../templates/emails/$template");
+    my $to = $name ? [[$email, $name]] : $email;
+    my $message = mySociety::Email::construct_email({
+        _template_ => $template,
+        _parameters_ => \%h,
+        From => [mySociety::Config::get('CONTACT_EMAIL'), 'Neighbourhood Fix-It'],
+        To => $to,
+    });
+    my $result = mySociety::Util::send_email($message, mySociety::Config::get('CONTACT_EMAIL'), $email);
+    my $out;
+    if ($result == mySociety::Util::EMAIL_SUCCESS) {
+        $out = <<EOF;
+<h1>Nearly Done! Now check your email...</h1>
+<p>The confirmation email <strong>may</strong> take a few minutes to arrive &mdash; <em>please</em> be patient.</p>
+<p>If you use web-based email or have 'junk mail' filters, you may wish to check your bulk/spam mail folders: sometimes, our messages are marked that way.</p>
+<p>You must now click on the link within the email we've just sent you -
+<br>if you do not, your update will not be posted.</p>
+<p>(Don't worry - we'll hang on to your update while you're checking your email.)</p>
+EOF
+    } else {
+        $out = <<EOF;
+<p>I'm afraid something went wrong when we tried to send your email. Please click Back, check your details, and try again.</p>
+EOF
+    }
+    return $out;
+}
+
 1;
diff --git a/templates/emails/alert-confirm b/templates/emails/alert-confirm
new file mode 100644
index 000000000..ca517778c
--- /dev/null
+++ b/templates/emails/alert-confirm
@@ -0,0 +1,14 @@
+Subject: Confirm your alert on Neighbourhood Fix-It
+
+Hi,
+
+Please click on the link below to confirm the alert you just^r
+asked to subscribe to on Neighbourhood Fix-It:
+
+    <?=$values['url']?>
+
+If you can't click the link, copy and paste it to the address
+bar of your web browser.
+
+-- 
+The Neighbourhood Fix-It team
diff --git a/templates/emails/alert-problem b/templates/emails/alert-problem
new file mode 100644
index 000000000..16dd77e5b
--- /dev/null
+++ b/templates/emails/alert-problem
@@ -0,0 +1,11 @@
+Subject: New problems on Neighbourhood Fix-It
+
+The following new problems have been added:
+
+<?=$values['data']?>
+
+-- 
+The Neighbourhood Fix-It team
+
+To stop receiving emails when there are new comments on this problem,
+please follow this link: <?=$values['unsubscribe_url']?>
diff --git a/templates/emails/alert-update b/templates/emails/alert-update
new file mode 100644
index 000000000..c029b3720
--- /dev/null
+++ b/templates/emails/alert-update
@@ -0,0 +1,13 @@
+Subject: New updates on problem - '<?=$values['title']?>'
+
+The following updates have been left on this problem:
+
+<?=$values['data']?>
+
+View or reply to these updates: <?=$values['problem_url']?>
+
+-- 
+The Neighbourhood Fix-It team
+
+To stop receiving emails when there are new comments on this problem,
+please follow this link: <?=$values['unsubscribe_url']?>
diff --git a/web/alert.cgi b/web/alert.cgi
new file mode 100755
index 000000000..e9a173273
--- /dev/null
+++ b/web/alert.cgi
@@ -0,0 +1,83 @@
+#!/usr/bin/perl -w
+
+# alert.cgi:
+# Alert code for Neighbourhood Fix-It
+#
+# Copyright (c) 2007 UK Citizens Online Democracy. All rights reserved.
+# Email: matthew@mysociety.org. WWW: http://www.mysociety.org
+#
+# $Id: alert.cgi,v 1.1 2007-01-26 01:01:23 matthew Exp $
+
+use strict;
+require 5.8.0;
+
+# Horrible boilerplate to set up appropriate library paths.
+use FindBin;
+use lib "$FindBin::Bin/../perllib";
+use lib "$FindBin::Bin/../../perllib";
+
+use Page;
+use mySociety::Alert;
+use mySociety::AuthToken;
+use mySociety::Config;
+use mySociety::DBHandle qw(dbh select_all);
+
+BEGIN {
+    mySociety::Config::set_file("$FindBin::Bin/../conf/general");
+    mySociety::DBHandle::configure(
+        Name => mySociety::Config::get('BCI_DB_NAME'),
+        User => mySociety::Config::get('BCI_DB_USER'),
+        Password => mySociety::Config::get('BCI_DB_PASS'),
+        Host => mySociety::Config::get('BCI_DB_HOST', undef),
+        Port => mySociety::Config::get('BCI_DB_PORT', undef)
+    );
+}
+
+sub main {
+    my $q = shift;
+    my $out = '';
+    if (my $token = $q->param('token')) {
+        my $data = mySociety::AuthToken::retrieve('alert', $token);
+	if (my $id = $data->{id}) {
+	    my $type = $data->{type};
+            if ($type eq 'subscribe') {
+                mySociety::Alert::confirm($id);
+                $out = '<p>You have successfully confirmed your alert.</p>';
+            } elsif ($type eq 'unsubscribe') {
+                mySociety::Alert::delete($id);
+                $out = '<p>You have successfully deleted your alert.</p>';
+            }
+        } else {
+            $out = <<EOF;
+<p>Thank you for trying to confirm your update or problem. We seem to have a
+problem ourselves though, so <a href="/contact">please let us know what went on</a>
+and we'll look into it.
+EOF
+        }
+    } elsif (my $email = $q->param('email')) {
+        # XXX: Need to validate email
+        my $type = $q->param('type');
+	my $alert_id;
+        if ($type eq 'updates') {
+	    my $id = $q->param('id');
+	    $alert_id = mySociety::Alert::create($email, 'new_updates', $id);
+	} elsif ($type eq 'problems') {
+	    $alert_id = mySociety::Alert::create($email, 'new_problems');
+	} else {
+	    throw mySociety::Alert::Error('Invalid type');
+	}
+        my %h = ();
+        $h{url} = mySociety::Config::get('BASE_URL') . '/A/'
+	    . mySociety::AuthToken::store('alert', { id => $alert_id, type => 'subscribe' } );
+        dbh()->commit();
+	$out = Page::send_email($email, undef, 'alert-confirm', %h);
+    } else {
+	$out = 'This should probably show some sort of subscribe page.';
+    }
+
+    print Page::header($q, 'Confirmation');
+    print $out;
+    print Page::footer();
+}
+Page::do_fastcgi(\&main);
+
diff --git a/web/index.cgi b/web/index.cgi
index 9ab36002b..3218c1072 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.56 2007-01-24 20:55:30 matthew Exp $
+# $Id: index.cgi,v 1.57 2007-01-26 01:01:23 matthew Exp $
 
 # TODO
 # Nothing is done about the update checkboxes - not stored anywhere on anything!
@@ -32,7 +32,6 @@ use Page;
 use mySociety::AuthToken;
 use mySociety::Config;
 use mySociety::DBHandle qw(dbh select_all);
-use mySociety::Email;
 use mySociety::GeoUtil;
 use mySociety::Util;
 use mySociety::MaPit;
@@ -76,7 +75,7 @@ sub main {
     } elsif ($q->param('id')) {
         $title = 'Viewing a problem';
         $out = display_problem($q);
-    } elsif ($q->param('pc')) {
+    } elsif ($q->param('pc') || ($q->param('x') && $q->param('y'))) {
         $title = 'Viewing a location';
         $out = display_location($q);
     } else {
@@ -122,7 +121,7 @@ EOF
 
 sub submit_update {
     my $q = shift;
-    my @vars = qw(id name email update updates fixed reopen);
+    my @vars = qw(id name email update fixed reopen);
     my %input = map { $_ => $q->param($_) || '' } @vars;
     my @errors;
     push(@errors, 'Please enter a message') unless $input{update};
@@ -134,8 +133,6 @@ sub submit_update {
     }
     return display_problem($q, @errors) if (@errors);
 
-    my $template = File::Slurp::read_file("$FindBin::Bin/../templates/emails/update-confirm");
-
     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)
@@ -148,34 +145,13 @@ sub submit_update {
     $h{url} = mySociety::Config::get('BASE_URL') . '/C/' . mySociety::AuthToken::store('update', $id);
     dbh()->commit();
 
-    my $email = mySociety::Email::construct_email({
-        _template_ => $template,
-        _parameters_ => \%h,
-        From => [mySociety::Config::get('CONTACT_EMAIL'), 'Neighbourhood Fix-It'],
-        To => [[$input{email}, $input{name}]],
-    });
-    my $result = mySociety::Util::send_email($email, mySociety::Config::get('CONTACT_EMAIL'), $input{email});
-    my $out;
-    if ($result == mySociety::Util::EMAIL_SUCCESS) {
-        $out = <<EOF;
-<h1>Nearly Done! Now check your email...</h1>
-<p>The confirmation email <strong>may</strong> take a few minutes to arrive &mdash; <em>please</em> be patient.</p>
-<p>If you use web-based email or have 'junk mail' filters, you may wish to check your bulk/spam mail folders: sometimes, our messages are marked that way.</p>
-<p>You must now click on the link within the email we've just sent you -
-<br>if you do not, your update will not be posted.</p>
-<p>(Don't worry - we'll hang on to your update while you're checking your email.)</p>
-EOF
-    } else {
-        $out = <<EOF;
-<p>I'm afraid something went wrong when we tried to send your email. Please click Back, check your details, and try again.</p>
-EOF
-    }
+    my $out = Page::send_email($input{email}, $input{name}, 'update-confirm', %h);
     return $out;
 }
 
 sub submit_problem {
     my $q = shift;
-    my @vars = qw(council title detail name email phone pc easting northing updates);
+    my @vars = qw(council title detail name email phone pc easting northing);
     my %input = map { $_ => scalar $q->param($_) } @vars;
     my @errors;
 
@@ -212,8 +188,6 @@ sub submit_problem {
     
     return display_form($q, @errors) if (@errors);
 
-    my $template = File::Slurp::read_file("$FindBin::Bin/../templates/emails/problem-confirm");
-
     my $id = dbh()->selectrow_array("select nextval('problem_id_seq');");
 
     my $image;
@@ -253,35 +227,14 @@ sub submit_problem {
     $h{url} = mySociety::Config::get('BASE_URL') . '/P/' . mySociety::AuthToken::store('problem', $id);
     dbh()->commit();
 
-    my $email = mySociety::Email::construct_email({
-        _template_ => $template,
-        _parameters_ => \%h,
-        From => [mySociety::Config::get('CONTACT_EMAIL'), 'Neighbourhood Fix-It'],
-        To => [[$input{email}, $input{name}]],
-    });
-    my $result = mySociety::Util::send_email($email, mySociety::Config::get('CONTACT_EMAIL'), $input{email});
-    my $out;
-    if ($result == mySociety::Util::EMAIL_SUCCESS) {
-        $out = <<EOF;
-<h1>Nearly Done! Now check your email...</h1>
-<p>The confirmation email <strong>may</strong> take a few minutes to arrive &mdash; <em>please</em> be patient.</p>
-<p>If you use web-based email or have 'junk mail' filters, you may wish to check your bulk/spam mail folders: sometimes, our messages are marked that way.</p>
-<p>You must now click on the link within the email we've just sent you -
-<br>if you do not, your problem will not be posted on the site.</p>
-<p>(Don't worry - we'll hang on to your information while you're checking your email.)</p>
-EOF
-    } else {
-        $out = <<EOF;
-<p>I'm afraid something went wrong when we tried to send your email. Please click Back, check your details, and try again.</p>
-EOF
-    }
+    my $out = Page::send_email($input{email}, $input{name}, 'problem-confirm', %h);
     return $out;
 }
 
 sub display_form {
     my ($q, @errors) = @_;
     my ($pin_x, $pin_y, $pin_tile_x, $pin_tile_y);
-    my @vars = qw(title detail name email phone updates pc easting northing x y skipped council);
+    my @vars = qw(title detail name email phone pc easting northing x y skipped council);
     my %input = map { $_ => $q->param($_) || '' } @vars;
     my %input_h = map { $_ => $q->param($_) ? ent($q->param($_)) : '' } @vars;
     my @ps = $q->param;
@@ -355,7 +308,7 @@ EOF
             . $areas_info->{$councils[0]}->{name} . '</strong>.</p>';
         $out .= '<input type="hidden" name="council" value="' . $councils[0] . '">';
     } else {
-        my $e = 'team@neighbourhoodfixit.com'; # XXX from config
+        my $e = mySociety::Config::get('CONTACT_EMAIL');
         my $list = join(', ', map { $areas_info->{$_}->{name} } @$all_councils);
         my $n = @$all_councils;
         $out .= '<p>We do not yet have details for the council';
@@ -380,7 +333,6 @@ exact location of the problem (ie. on a wall or the floor), and so on.</p>';
     if (@errors) {
         $out .= '<ul id="error"><li>' . join('</li><li>', @errors) . '</li></ul>';
     }
-    my $updates = (!defined($q->param('updates')) || $input{updates}) ? ' checked' : '';
     my $back = NewURL($q, submit_map => undef, "tile_$pin_tile_x.$pin_tile_y.x" => undef,
         "tile_$pin_tile_x.$pin_tile_y.y" => undef, skipped => undef);
     $out .= <<EOF;
@@ -398,8 +350,6 @@ exact location of the problem (ie. on a wall or the floor), and so on.</p>';
 <small>(optional, so the council can get in touch)</small></div>
 <div><label for="form_photo">Photo:</label>
 <input type="file" name="photo" id="form_photo"></div>
-<div class="checkbox"><input disabled type="checkbox" name="updates" id="form_updates" value="1"$updates>
-<label for="form_updates"><s>Receive email when updates are left on this problem</s></label></div>
 <div class="checkbox"><input type="submit" name="submit_problem" value="Submit"></div>
 </fieldset>
 
@@ -461,6 +411,7 @@ EOF
     $out .= <<EOF;
     </ol>
     <h2>Recent problems reported within 10km</h2>
+    <p><a href="/rss/$x,$y?choose=1">RSS feed</a></p>
     <ol id="current" start="$list_start">
 EOF
     foreach (@$current) {
@@ -493,11 +444,11 @@ EOF
 sub display_problem {
     my ($q, @errors) = @_;
 
-    my @vars = qw(id name email update updates fixed reopen x y);
+    my @vars = qw(id name email update fixed reopen x y);
     my %input = map { $_ => $q->param($_) || '' } @vars;
     my %input_h = map { $_ => $q->param($_) ? ent($q->param($_)) : '' } @vars;
-    $input{x} = $input{x} + 0;
-    $input{y} = $input{y} + 0;
+    $input{x} += 0;
+    $input{y} += 0;
 
     # Get all information from database
     my $problem = dbh()->selectrow_arrayref(
@@ -532,18 +483,18 @@ EOF
         $out .= '<p align="center"><img src="/photo?id=' . $input{id} . '"></p>';
     }
 
-    my $back = NewURL($q, id=>undef);
+    my $back = NewURL($q, id=>undef, x=>$x_tile, y=>$y_tile);
     $out .= '<p align="right"><a href="' . $back . '">Back to listings</a></p>';
 
     # Display updates
     my $updates = select_all(
-        "select id, name, extract(epoch from whenposted) as whenposted, text, mark_fixed, mark_open
+        "select id, name, extract(epoch from created) as created, text, mark_fixed, mark_open
          from comment where problem_id = ? and state='confirmed'
-         order by whenposted desc", $input{id});
+         order by created desc", $input{id});
     if (@$updates) {
         $out .= '<div id="updates"> <h2>Updates</h2>';
         foreach my $row (@$updates) {
-            $out .= "<div><em>Posted by $row->{name} at " . prettify_epoch($row->{whenposted});
+            $out .= "<div><em>Posted by $row->{name} at " . prettify_epoch($row->{created});
             $out .= ', marked fixed' if ($row->{mark_fixed});
             $out .= ', reopened' if ($row->{mark_open});
             $out .= '</em>';
@@ -551,12 +502,25 @@ EOF
         }
         $out .= '</div>';
     }
+    $out .= <<EOF;
+<h2>Follow this problem</h2>
+<ul>
+<li><a href="/rss/$input_h{id}?choose=1">RSS feed of updates on this problem</a>
+<li>Receive email when updates are left on this problem.
+<form action="alert" method="post">
+<label class="n" for="alert_email">Email:</label>
+<input type="text" name="email" id="alert_email" value="$input_h{email}" size="30">
+<input type="hidden" name="id" value="$input_h{id}">
+<input type="hidden" name="type" value="updates">
+<input type="submit" value="Subscribe">
+</form>
+</ul>
+EOF
     $out .= '<h2>Provide an update</h2>';
     if (@errors) {
         $out .= '<ul id="error"><li>' . join('</li><li>', @errors) . '</li></ul>';
     }
 
-    $updates = (!defined($q->param('updates')) || $input{updates}) ? ' checked' : '';
     my $fixed = ($input{fixed}) ? ' checked' : '';
     my $reopen = ($input{reopen}) ? ' checked' : '';
     my $fixedline = $state eq 'fixed' ? qq{
@@ -578,8 +542,6 @@ EOF
 <div><label for="form_update">Update:</label>
 <textarea name="update" id="form_update" rows="7" cols="30">$input_h{update}</textarea></div>
 $fixedline
-<div class="checkbox"><input type="checkbox" disabled name="updates" id="form_updates" value="1"$updates>
-<label for="form_updates"><s>Receive email when updates are left on this problem</s></label></div>
 <div class="checkbox"><input type="submit" value="Post"></div>
 </fieldset>
 </form>
diff --git a/web/rss.cgi b/web/rss.cgi
new file mode 100755
index 000000000..757f98e85
--- /dev/null
+++ b/web/rss.cgi
@@ -0,0 +1,74 @@
+#!/usr/bin/perl -w
+
+# rss.cgi:
+# RSS for Neighbourhood Fix-It
+#
+# Copyright (c) 2007 UK Citizens Online Democracy. All rights reserved.
+# Email: matthew@mysociety.org. WWW: http://www.mysociety.org
+#
+# $Id: rss.cgi,v 1.1 2007-01-26 01:01:23 matthew Exp $
+
+use strict;
+require 5.8.0;
+
+# Horrible boilerplate to set up appropriate library paths.
+use FindBin;
+use lib "$FindBin::Bin/../perllib";
+use lib "$FindBin::Bin/../../perllib";
+use URI::Escape;
+
+use Page;
+use mySociety::Config;
+use mySociety::DBHandle qw(dbh);
+use mySociety::Alert;
+use mySociety::Web;
+
+BEGIN {
+    mySociety::Config::set_file("$FindBin::Bin/../conf/general");
+    mySociety::DBHandle::configure(
+        Name => mySociety::Config::get('BCI_DB_NAME'),
+        User => mySociety::Config::get('BCI_DB_USER'),
+        Password => mySociety::Config::get('BCI_DB_PASS'),
+        Host => mySociety::Config::get('BCI_DB_HOST', undef),
+        Port => mySociety::Config::get('BCI_DB_PORT', undef)
+    );
+}
+
+sub main {
+    my $q = shift;
+    my $type = $q->param('type') || '';
+    my $choose = $q->param('choose');
+    if ($choose) {
+        print Page::header($q, 'Choose RSS feed');
+	my $url = $ENV{SCRIPT_URI};
+	$url = uri_escape($url);
+	print <<EOF;
+<ul>
+<li><a href="http://www.bloglines.com/sub?url=$url">Bloglines</a>
+<li><a href="http://google.com/reader/view/feed/$url">Google Reader</a>
+<li><a href="http://add.my.yahoo.com/content?url=$url">My Yahoo!</a>
+<li><a href="http://my.msn.com/addtomymsn.armx?id=rss&ut=$url&tt=CENTRALDIRECTORY&ru=http://rss.msn.com">My MSN</a>
+<li><a href="http://127.0.0.1:5335/system/pages/subscriptions?url=$url">Userland</a>
+<li><a href="http://127.0.0.1:8888/index.html?add_url=$url">Amphetadesk</a>
+<li><a href="http://www.feedmarker.com/admin.php?do=add_feed&url=$url">Feedmarker</a>
+<li><a href="$ENV{SCRIPT_URL}">Plain RSS</a>
+</ul>
+EOF
+	print Page::footer();
+	return;
+    }
+    if ($type eq 'local_problems') {
+        my $x = ($q->param('x') * 5000 / 31);
+        my $y = ($q->param('y') * 5000 / 31);
+        mySociety::Alert::generate_rss($type, $x, $y);
+    } elsif ($type eq 'new_updates') {
+        my $id = $q->param('id');
+        mySociety::Alert::generate_rss($type, $id);
+    } elsif ($type eq 'new_problems') {
+        mySociety::Alert::generate_rss($type);
+    } else {
+        throw Error::Simple('Unknown alert type') unless $type;
+    }
+}
+Page::do_fastcgi(\&main);
+