diff options
| -rw-r--r-- | conf/httpd.conf | 34 | ||||
| -rw-r--r-- | db/alert_types.sql | 32 | ||||
| -rwxr-xr-x | web/reports.cgi | 150 |
3 files changed, 169 insertions, 47 deletions
diff --git a/conf/httpd.conf b/conf/httpd.conf index 7e98872f3..46ea123df 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.24 2007-08-24 00:24:14 matthew Exp $ +# $Id: httpd.conf,v 1.25 2007-08-24 23:37:14 matthew Exp $ DirectoryIndex index.cgi @@ -28,9 +28,11 @@ RewriteEngine on #RewriteLog /var/log/apache/rewrite.log #RewriteLogLevel 3 +# End slashes goodbye RewriteRule ^/admin/ - [L] RewriteRule ^(.+)/$ $1 [R=permanent] +# Confirmation tokens 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 @@ -38,21 +40,27 @@ RewriteRule ^/[Qq]/([0-9A-Za-z]{16}).*$ /questionnaire.cgi?token=$1 RewriteRule ^/[Ff]/([0-9A-Za-z]{16}).*$ /flickr.cgi?token=$1 RewriteRule ^/[Ll]/([0-9A-Za-z]{16}).*$ /flickr2.cgi?token=$1 +# RSS feeds for updates on a problem 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/l/([0-9.-]+),([0-9.-]+)$ /rss.cgi?type=local_problems;lat=$1;lon=$2 [QSA] -RewriteRule ^/rss/([0-9]+),([0-9]+)/([0-9]+)$ /rss.cgi?type=local_problems;x=$1;y=$2;d=$3 [QSA] -RewriteRule ^/rss/([0-9]+)/([0-9]+)/([0-9]+)$ /rss.cgi?type=local_problems;x=$1;y=$2;d=$3 [QSA] -RewriteRule ^/rss/l/([0-9]+)/([0-9]+)/([0-9]+)$ /rss.cgi?type=local_problems;lat=$1;lon=$2;d=$3 [QSA] -RewriteRule ^/rss/council/([0-9]+)$ /rss.cgi?type=council_problems;id=$1 [QSA] -#RewriteRule ^/rss/ward/([0-9]+)$ /rss.cgi?type=council_problems;id=$1 [QSA] -RewriteRule ^/rss/problems$ /rss.cgi?type=new_problems [QSA] + +# RSS feeds for new local problems +RewriteRule ^/rss/([0-9]+)[,/]([0-9]+)$ /rss.cgi?type=local_problems;x=$1;y=$2 [QSA] +RewriteRule ^/rss/l/([0-9.-]+)[,/]([0-9.-]+)$ /rss.cgi?type=local_problems;lat=$1;lon=$2 [QSA] +RewriteRule ^/rss/([0-9]+)[,/]([0-9]+)/([0-9]+)$ /rss.cgi?type=local_problems;x=$1;y=$2;d=$3 [QSA] +RewriteRule ^/rss/l/([0-9]+)[,/]([0-9]+)/([0-9]+)$ /rss.cgi?type=local_problems;lat=$1;lon=$2;d=$3 [QSA] +RewriteRule ^/rss/problems$ /rss.cgi?type=new_problems [QSA] + +# RSS feeds for voting areas +RewriteRule ^/rss/council/([0-9]+)$ /rss/reports/$1 [R=permanent] +RewriteRule ^/report$ /reports [R=permanent] +RewriteRule ^/reports/([^/]+)/all$ /reports.cgi?council=$1;all=1 [QSA] +RewriteRule ^/reports/([^/]+)/([^/]+)$ /reports.cgi?council=$1;ward=$2 [QSA] +RewriteRule ^/rss/(reports|area)/([^/]+)/([^/]+)$ /reports.cgi?rss=$1;council=$2;ward=$3 [QSA] +RewriteRule ^/reports/([^/]+)$ /reports.cgi?council=$1 [QSA] +RewriteRule ^/rss/area/([0-9]+)$ /rss.cgi?type=area_problems;id=$1 [QSA] +RewriteRule ^/rss/(reports|area)/([^/]+)$ /reports.cgi?rss=$1;council=$2 [QSA] RewriteRule ^/report/([0-9]+)$ /index.cgi?id=$1 [QSA] -RewriteRule ^/report$ /reports [R=permanent] -RewriteRule ^/reports/([^/]+)/all$ /reports.cgi?council=$1;all=1 [QSA] -RewriteRule ^/reports/([^/]+)/([^/]+)$ /reports.cgi?council=$1;ward=$2 [QSA] -RewriteRule ^/reports/([^/]+)$ /reports.cgi?council=$1 [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 index 9b7fab576..6b9dafa09 100644 --- a/db/alert_types.sql +++ b/db/alert_types.sql @@ -1,3 +1,4 @@ +-- New updates on a particular problem report insert into alert_type (ref, head_sql_query, head_table, head_title, head_link, head_description, @@ -8,6 +9,7 @@ values ('new_updates', 'select * from problem where id=?', 'problem', 'comment', 'comment.state=\'confirmed\'', 'created desc', 'Update by {{name}}', '/?id={{problem_id}}#comment_{{id}}', '{{text}}', 'alert-update'); +-- New problems anywhere on the site insert into alert_type (ref, head_sql_query, head_table, head_title, head_link, head_description, @@ -18,6 +20,7 @@ values ('new_problems', '', '', 'problem', 'problem.state in (\'confirmed\', \'fixed\')', 'created desc', '{{title}}', '/?id={{id}}', '{{detail}}', 'alert-problem'); +-- New problems around a location insert into alert_type (ref, head_sql_query, head_table, head_title, head_link, head_description, @@ -28,6 +31,7 @@ values ('local_problems', '', '', 'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.state in (\'confirmed\', \'fixed\')', 'created desc', '{{title}}', '/?id={{id}}', '{{detail}}', 'alert-problem'); +-- New problems sent to a particular council insert into alert_type (ref, head_sql_query, head_table, head_title, head_link, head_description, @@ -35,7 +39,33 @@ insert into alert_type item_title, item_link, item_description, template) values ('council_problems', '', '', 'New local problems on FixMyStreet', '/reports', 'The latest local problems reported by users', - 'problem', 'problem.state in (\'confirmed\', \'fixed\') and council like \'%\'||?||\'%\'', 'created desc', + 'problem', 'problem.state in (\'confirmed\', \'fixed\') and (council like \'%\'||?||\'%\' + or (council is null and areas like \'%,\'||?||\',%\'))', 'created desc', + '{{title}}', '/?id={{id}}', '{{detail}}', 'alert-problem' +); + +-- New problems within a particular ward sent to a particular council +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 ('ward_problems', '', '', + 'New local problems on FixMyStreet', '/reports', 'The latest local problems reported by users', + 'problem', 'problem.state in (\'confirmed\', \'fixed\') and (council like \'%\'||?||\'%\' + or council is null) and areas like \'%,\'||?||\',%\'', 'created desc', + '{{title}}', '/?id={{id}}', '{{detail}}', 'alert-problem' +); + +-- New problems within a particular voting area (ward, constituency, whatever) +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 ('area_problems', '', '', + 'New local problems on FixMyStreet', '/reports', 'The latest local problems reported by users', + 'problem', 'problem.state in (\'confirmed\', \'fixed\') and areas like \'%,\'||?||\',%\'', 'created desc', '{{title}}', '/?id={{id}}', '{{detail}}', 'alert-problem' ); diff --git a/web/reports.cgi b/web/reports.cgi index 7194685c6..180f361c2 100755 --- a/web/reports.cgi +++ b/web/reports.cgi @@ -2,11 +2,12 @@ # report.cgi: # Display summary reports for FixMyStreet +# And RSS feeds for those reports etc. # # Copyright (c) 2007 UK Citizens Online Democracy. All rights reserved. # Email: matthew@mysociety.org. WWW: http://www.mysociety.org # -# $Id: reports.cgi,v 1.4 2007-08-23 11:58:07 matthew Exp $ +# $Id: reports.cgi,v 1.5 2007-08-24 23:37:15 matthew Exp $ use strict; require 5.8.0; @@ -18,6 +19,7 @@ use lib "$FindBin::Bin/../../perllib"; use URI::Escape; use Page; +use mySociety::Alert; use mySociety::Config; use mySociety::DBHandle qw(dbh select_all); use mySociety::MaPit; @@ -38,56 +40,130 @@ BEGIN { sub main { my $q = shift; my $all = $q->param('all') || 0; - my $one_council = $q->param('council') || ''; - if ($one_council =~ /\D/) { - $one_council =~ s/ and / & /; - $one_council = mySociety::MaPit::get_voting_area_by_name("$one_council ", $mySociety::VotingArea::council_parent_types); - if (keys %$one_council == 1) { - ($one_council) = keys %$one_council; - } else { - $one_council = undef; + my $rss = $q->param('rss') || ''; + + # Look up council name, if given + my $q_council = $q->param('council') || ''; + my ($one_council, $area_type); + if ($q_council =~ /\D/) { + (my $qc = $q_council) =~ s/ and / & /; + $qc = mySociety::MaPit::get_voting_area_by_name("$qc ", $mySociety::VotingArea::council_parent_types); + if (keys %$qc == 1) { + ($one_council) = keys %$qc; + $area_type = $qc->{$one_council}->{type}; } + if (!$one_council) { # Given a false council name + print $q->redirect('/reports'); + return; + } + } elsif ($q_council =~ /^\d+$/) { + $one_council = $q_council; } $all = 0 unless $one_council; + + # Look up ward name, if given + my $q_ward = $q->param('ward') || ''; + my $ward; + if ($one_council && $q_ward) { + my $qw = mySociety::MaPit::get_voting_area_by_name($q_ward, $mySociety::VotingArea::council_child_types); + foreach my $id (sort keys %$qw) { + if ($qw->{$id}->{parent_area_id} == $one_council) { + $ward = $id; + last; + } + } + if (!$ward) { # Given a false ward name + print $q->redirect('/reports/' . short_name($q_council)); + return; + } + } + + # RSS - reports for sent reports, area for all problems in area + if ($rss && $one_council) { + if ($rss eq 'area' && $area_type ne 'DIS' && $area_type ne 'CTY') { + # Two possibilites are the same for one-tier councils, so redirect one to the other + print $q->redirect('/rss/reports/' . short_name($q_council) . ($ward ? '/' . short_name($q_ward) : '')); + return; + } + my $type = 'council_problems'; # Problems sent to a council + my @params; + push @params, $one_council if $rss eq 'reports'; + push @params, $ward ? $ward : $one_council; + if ($ward && $rss eq 'reports') { + $type = 'ward_problems'; # Problems sent to a council, restricted to a ward + } elsif ($rss eq 'area') { + $type = 'area_problems'; # Problems within an area + } + my $url = short_name($q_council); + $url .= '/' . short_name($q_ward) if $ward; + mySociety::Alert::generate_rss($type, "/$url", @params); + return; + } + + my %councils; + if ($one_council) { + %councils = ( $one_council => 1 ); + } else { + # Show all councils on main report page + %councils = map { $_ => 1 } @{mySociety::MaPit::get_areas_by_type($mySociety::VotingArea::council_parent_types)}; + } + my @params; my $where_extra = ''; - if ($one_council) { + if ($ward) { + push @params, $ward; + $where_extra = "and areas like '%,'||?||',%'"; + } elsif ($one_council) { push @params, $one_council; - $where_extra = "and council like '%'||?||'%'"; + $where_extra = "and areas like '%,'||?||',%'"; } - my (%fixed, %open, %councils); my $problem = select_all( - "select id, title, detail, council, state, + "select id, title, detail, council, state, areas, extract(epoch from ms_current_timestamp()-lastupdate) as duration, extract(epoch from ms_current_timestamp()-confirmed) as age from problem where state in ('confirmed', 'fixed') - and whensent is not null $where_extra - order by id + order by id desc ", @params); - my $fourweeks = 4*7*24*60*60; - foreach my $row (@$problem) { - my ($council, $missing) = $row->{council} =~ /^(.*?)(?:\|(.*))?$/; - my @council = split /,/, $council; + + our $fourweeks = 4*7*24*60*60; + our (%fixed, %open); + sub add_row { + my ($row, $councils, $council) = @_; + my $duration = ($row->{duration} > 2 * $fourweeks) ? 'old' : 'new'; my $type = ($row->{duration} > 2 * $fourweeks) ? 'unknown' : ($row->{age} > $fourweeks ? 'older' : 'new'); - my $duration = ($row->{duration} > 2 * $fourweeks) ? 'old' : 'new'; - foreach (@council) { - next if $one_council && $_ != $one_council; - my $entry = [ $row->{id}, $row->{title}, $row->{detail}, scalar @council, $missing ]; - push @{$fixed{$_}{$duration}}, $entry - if $row->{state} eq 'fixed'; - push @{$open{$_}{$type}}, $entry - if $row->{state} eq 'confirmed'; - $councils{$_} = 1; + my $entry = [ $row->{id}, $row->{title}, $row->{detail}, $councils ]; + # Fixed problems are either old or new + push @{$fixed{$council}{$duration}}, $entry if $row->{state} eq 'fixed'; + # Open problems are either unknown, older, or new + push @{$open{$council}{$type}}, $entry if $row->{state} eq 'confirmed'; + } + + my $re_councils = join('|', keys %councils); + foreach my $row (@$problem) { + if (!$row->{council}) { + # Problem was not sent to any council, add to possible councils + while ($row->{areas} =~ /,($re_councils)(?=,)/go) { + add_row($row, 0, $1); + } + } else { + # Add to councils it was sent to + $row->{council} =~ s/\|.*$//; + my @council = split /,/, $row->{council}; + foreach (@council) { + next if $one_council && $_ != $one_council; + add_row($row, scalar @council, $_); + } } } + my $areas_info = mySociety::MaPit::get_voting_areas_info([keys %councils]); if (!$one_council) { print Page::header($q, title=>'Summary reports'); - print $q->p(_('This is a summary of all reports on this site that have been sent to a council; select a particular council to see the reports sent there.')); + print $q->p(_('This is a summary of all reports on this site; select a particular council to see the reports sent there.')); my $c = 0; print '<table cellpadding="3" cellspacing="1" border="0">'; print '<tr><th>Name</th><th>New problems</th><th>Older problems</th> @@ -114,10 +190,17 @@ sub main { print $q->a({href => '/reports' }, 'Show all councils'); print "."; } else { - print Page::header($q, title=>"$name - Summary reports", rss => [ "Problems within $name, FixMyStreet", "/rss/council/$one_council" ]); + my $rss_url = '/rss/reports/' . short_name($name); + my $thing = 'council'; + if ($ward) { + $rss_url .= '/' . short_name($q_ward); + $thing = 'ward'; + $name = ent($q_ward) . ", $name"; + } + print Page::header($q, title=>"$name - Summary reports", rss => [ "Problems within $name, FixMyStreet", $rss_url ]); print $q->p( - $q->a({href => "/rss/council/$one_council"}, '<img align="right" src="/i/feed.png" width="16" height="16" title="RSS feed" alt="RSS feed of problems in this council" border="0" hspace="4">'), - 'This is a summary of all reports for one council. You can ' . + $q->a({ href => $rss_url }, '<img align="right" src="/i/feed.png" width="16" height="16" title="RSS feed" alt="RSS feed of problems in this ' . $thing . '" border="0" hspace="4">'), + 'This is a summary of all reports for one ' . $thing . '. You can ' . ($all ? $q->a({href => NewURL($q, council=>undef, all=>undef) }, 'see less detail') : $q->a({href => NewURL($q, council=>undef, all=>1) }, 'see more details')) . @@ -159,7 +242,7 @@ sub list_problems { print ent($_->[1]); print '</a>'; print ' <small>(sent to both)</small>' if $_->[3]>1; - print ' <small>(sent to none)</small>' if $_->[3]==0; + print ' <small>(not sent to council)</small>' if $_->[3]==0; print '<br><small>' . ent($_->[2]) . '</small>' if $all; print '</li>'; } @@ -178,3 +261,4 @@ sub short_name { $name =~ s/%20/+/g; return $name; } + |