diff options
| -rw-r--r-- | perllib/Page.pm | 31 | ||||
| -rw-r--r-- | perllib/Problems.pm | 132 | ||||
| -rwxr-xr-x | web/alert.cgi | 6 | ||||
| -rw-r--r-- | web/css/main-scambs.css | 9 | ||||
| -rwxr-xr-x | web/index.cgi | 43 | ||||
| -rwxr-xr-x | web/reports.cgi | 8 |
6 files changed, 169 insertions, 60 deletions
diff --git a/perllib/Page.pm b/perllib/Page.pm index fbeb1afe4..aea037ae7 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.101 2008-05-15 16:09:51 matthew Exp $ +# $Id: Page.pm,v 1.102 2008-05-20 14:39:01 matthew Exp $ # package Page; @@ -21,6 +21,7 @@ use LWP::Simple; use Digest::MD5 qw(md5_hex); use POSIX qw(strftime); use URI::Escape; +use Problems; use mySociety::Config; use mySociety::DBHandle qw/dbh select_all/; use mySociety::EvEl; @@ -84,6 +85,10 @@ sub microsite { } else { mySociety::Locale::gettext_domain('FixMyStreet'); } + + if ($q->{site} eq 'scambs') { + Problems::set_site_restriction('scambs'); + } } =item header Q [PARAM VALUE ...] @@ -670,30 +675,6 @@ sub short_name { return $name; } -sub recent_photos { - my ($num, $e, $n, $dist) = @_; - my $probs; - if ($e) { - $probs = select_all("select id, title - from problem_find_nearby(?, ?, ?) as nearby, problem - where nearby.problem_id = problem.id - and state in ('confirmed', 'fixed') and photo is not null - order by confirmed desc limit $num", $e, $n, $dist); - } else { - $probs = select_all("select id, title from problem - where state in ('confirmed', 'fixed') and photo is not null - order by confirmed desc limit $num"); - } - my $out = ''; - foreach (@$probs) { - my $title = ent($_->{title}); - $out .= '<a href="/?id=' . $_->{id} . - '"><img border="0" src="/photo?tn=1;id=' . $_->{id} . - '" alt="' . $title . '" title="' . $title . '"></a>'; - } - return $out; -} - sub check_photo { my ($q, $fh) = @_; my $ct = $q->uploadInfo($fh)->{'Content-Type'}; diff --git a/perllib/Problems.pm b/perllib/Problems.pm new file mode 100644 index 000000000..a833f9f27 --- /dev/null +++ b/perllib/Problems.pm @@ -0,0 +1,132 @@ +#!/usr/bin/perl +# +# Problems.pm: +# Various problem report database fetching related functions for FixMyStreet. +# +# Copyright (c) 2008 UK Citizens Online Democracy. All rights reserved. +# Email: matthew@mysociety.org; WWW: http://www.mysociety.org/ +# +# $Id: Problems.pm,v 1.1 2008-05-20 14:39:01 matthew Exp $ +# + +package Problems; + +use strict; +use mySociety::DBHandle qw/dbh select_all/; +use mySociety::Web qw/ent/; + +my $site_restriction = ''; +sub set_site_restriction { + my $site = shift; + $site_restriction = ' and council=2260 ' + if $site eq 'scambs'; +} + +# Front page statistics + +sub recent_fixed { + dbh()->selectrow_array("select count(*) from problem + where state='fixed' and lastupdate>ms_current_timestamp()-'1 month'::interval + $site_restriction"); +} + +sub number_comments { + if ($site_restriction) { + dbh()->selectrow_array("select count(*) from comment, problem + where comment.problem_id=problem.id and comment.state='confirmed' + $site_restriction"); + } else { + dbh()->selectrow_array("select count(*) from comment + where state='confirmed'"); + } +} + +sub recent_new { + my $interval = shift; + dbh()->selectrow_array("select count(*) from problem + where state in ('confirmed','fixed') and confirmed>ms_current_timestamp()-'$interval'::interval + $site_restriction"); +} + +# Front page recent lists + +sub recent_photos { + my ($num, $e, $n, $dist) = @_; + my $probs; + if ($e) { + $probs = select_all("select id, title + from problem_find_nearby(?, ?, ?) as nearby, problem + where nearby.problem_id = problem.id + and state in ('confirmed', 'fixed') and photo is not null + $site_restriction + order by confirmed desc limit $num", $e, $n, $dist); + } else { + $probs = select_all("select id, title from problem + where state in ('confirmed', 'fixed') and photo is not null + $site_restriction + order by confirmed desc limit $num"); + } + my $out = ''; + foreach (@$probs) { + my $title = ent($_->{title}); + $out .= '<a href="/?id=' . $_->{id} . + '"><img border="0" src="/photo?tn=1;id=' . $_->{id} . + '" alt="' . $title . '" title="' . $title . '"></a>'; + } + return $out; +} + +sub recent { + select_all("select id,title from problem + where state in ('confirmed', 'fixed') + $site_restriction + order by confirmed desc limit 5"); +} + +# Problems around a location + +sub current_on_map { + my ($min_e, $max_e, $min_n, $max_n) = @_; + select_all( + "select id,title,easting,northing from problem where state='confirmed' + and easting>=? and easting<? and northing>=? and northing<? + $site_restriction + order by created desc limit 9", $min_e, $max_e, $min_n, $max_n); +} + +sub current_nearby { + my ($dist, $ids, $limit, $mid_e, $mid_n) = @_; + select_all( + "select id, title, easting, northing, distance + from problem_find_nearby(?, ?, $dist) as nearby, problem + where nearby.problem_id = problem.id + and state = 'confirmed'" . ($ids ? ' and id not in (' . $ids . ')' : '') . " + $site_restriction + order by distance, created desc limit $limit", $mid_e, $mid_n); +} + +sub fixed_nearby { + my ($dist, $mid_e, $mid_n) = @_; + select_all( + "select id, title, easting, northing, distance + from problem_find_nearby(?, ?, $dist) as nearby, problem + where nearby.problem_id = problem.id and state='fixed' + $site_restriction + order by created desc limit 9", $mid_e, $mid_n); +} + +# Fetch an individual problem + +sub fetch_problem { + my $id = shift; + dbh()->selectrow_hashref( + "select id, easting, northing, council, category, title, detail, (photo is not null) as photo, + used_map, name, anonymous, extract(epoch from confirmed) as time, + state, extract(epoch from whensent-confirmed) as whensent, + extract(epoch from ms_current_timestamp()-lastupdate) as duration + from problem where id=? and state in ('confirmed','fixed', 'hidden') + $site_restriction", {}, $id + ); +} + +1; diff --git a/web/alert.cgi b/web/alert.cgi index a809dceb4..67f8a970f 100755 --- a/web/alert.cgi +++ b/web/alert.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: alert.cgi,v 1.26 2008-05-13 16:13:20 matthew Exp $ +# $Id: alert.cgi,v 1.27 2008-05-20 14:39:01 matthew Exp $ use strict; use Standard; @@ -159,7 +159,7 @@ but will only appear in the "Within the boundary" alert for the county council.' my $checked = ''; $checked = ' checked' if $q->param('feed') && $q->param('feed') eq "local:$x:$y"; - my $pics = Page::recent_photos(5, $e, $n, $dist); + my $pics = Problems::recent_photos(5, $e, $n, $dist); $pics = '<div id="alert_photos">' . $q->h2(_('Photos of recent nearby reports')) . $pics . '</div>' if $pics; my $out = $q->h1(sprintf(_('Local RSS feeds and email alerts for ‘%s’'), $pretty_pc)); @@ -240,7 +240,7 @@ postcode or street name and area:'), '<input type="text" name="pc" value="' . $i return $out if $q->referer() && $q->referer() =~ /fixmystreet\.com/; - my $recent_photos = Page::recent_photos(10); + my $recent_photos = Problems::recent_photos(10); $out .= '<div id="alert_recent">' . $q->h2(_('Some photos of recent reports')) . $recent_photos . '</div>' if $recent_photos; return $out; } diff --git a/web/css/main-scambs.css b/web/css/main-scambs.css index 57f4a0b4b..52f01ffcf 100644 --- a/web/css/main-scambs.css +++ b/web/css/main-scambs.css @@ -1,3 +1,8 @@ +ul#error { + padding-top: 4px; + padding-bottom: 4px; +} + blockquote { border-left: solid 4px #013B63; } @@ -35,3 +40,7 @@ blockquote { right: 0; } +#fixed, #unknown { + margin-right: 400px; +} + diff --git a/web/index.cgi b/web/index.cgi index ceb65df57..d51cbe691 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.197 2008-05-15 16:09:52 matthew Exp $ +# $Id: index.cgi,v 1.198 2008-05-20 14:39:01 matthew Exp $ use strict; use Standard; @@ -80,12 +80,12 @@ sub front_page { $out .= '<br><small>' . $subhead . '</small>' if $subhead ne ' '; $out .= '</p>'; $out .= '<p id="error">' . $error . '</p>' if ($error); - my $fixed = dbh()->selectrow_array("select count(*) from problem where state='fixed' and lastupdate>ms_current_timestamp()-'1 month'::interval"); - my $updates = dbh()->selectrow_array("select count(*) from comment where state='confirmed'"); - my $new = dbh()->selectrow_array("select count(*) from problem where state in ('confirmed','fixed') and confirmed>ms_current_timestamp()-'1 week'::interval"); + my $fixed = Problems::recent_fixed(); + my $updates = Problems::number_comments(); + my $new = Problems::recent_new('1 week'); my $new_text = 'in past week'; if ($new > $fixed) { - $new = dbh()->selectrow_array("select count(*) from problem where state in ('confirmed','fixed') and confirmed>ms_current_timestamp()-'3 days'::interval"); + $new = Problems::recent_new('3 days'); $new_text = 'recently'; } $out .= '<form action="./" method="get" id="postcodeForm">'; @@ -141,12 +141,10 @@ EOF <div id="front_recent"> EOF - my $recent_photos = Page::recent_photos(3); + my $recent_photos = Problems::recent_photos(3); $out .= $q->h2(_('Photos of recent reports')) . $recent_photos if $recent_photos; - my $probs = select_all("select id,title from problem - where state in ('confirmed', 'fixed') - order by confirmed desc limit 5"); + my $probs = Problems::recent(); $out .= $q->h2(_('Recently reported problems')) . ' <ul>' if @$probs; foreach (@$probs) { $out .= '<li><a href="/?id=' . $_->{id} . '">'. ent($_->{title}); @@ -233,6 +231,7 @@ sub submit_problem { } $input{council} = -1 if $q->{site} eq 'emptyhomes'; # Not sent to council + $input{council} = 2260 if $q->{site} eq 'scambs'; # All reports go to S. Cambs push(@errors, _('No council selected')) unless ($input{council} && $input{council} =~ /^(?:-1|[\d,]+(?:\|[\d,]+)?)$/); push(@errors, _('Please enter a subject')) unless $input{title} =~ /\S/; @@ -430,6 +429,7 @@ sub display_form { if ($q->{site} eq 'scambs') { delete $all_councils->{2218}; + return display_location($q, _('That location is not within the boundary of South Cambridgeshire District Council - you can report problems elsewhere in Great Britain using <a href="http://www.fixmystreet.com/">FixMyStreet</a>.')) unless $all_councils->{2260}; } $all_councils = [ keys %$all_councils ]; return display_location($q, _('That spot does not appear to be covered by a council - if it is past the shoreline, for example, please specify the closest point on land.')) unless @$all_councils; @@ -750,12 +750,7 @@ sub display_problem { # Get all information from database return display_location($q, 'Unknown problem ID') if $input{id} =~ /\D/; - my $problem = dbh()->selectrow_hashref( - "select id, easting, northing, council, category, title, detail, (photo is not null) as photo, - used_map, name, anonymous, extract(epoch from confirmed) as time, - state, extract(epoch from whensent-confirmed) as whensent, - extract(epoch from ms_current_timestamp()-lastupdate) as duration - from problem where id=? and state in ('confirmed','fixed', 'hidden')", {}, $input{id}); + my $problem = Problems::fetch_problem($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'; my ($x, $y, $x_tile, $y_tile, $px, $py) = Page::os_to_px_with_adjust($q, $problem->{easting}, $problem->{northing}, $input{x}, $input{y}); @@ -863,10 +858,7 @@ sub map_pins { my $max_e = Page::tile_to_os($x+3); my $max_n = Page::tile_to_os($y+3); - my $current_map = select_all( - "select id,title,easting,northing from problem where state='confirmed' - and easting>=? and easting<? and northing>=? and northing<? - order by created desc limit 9", $min_e, $max_e, $min_n, $max_n); + my $current_map = Problems::current_on_map($min_e, $max_e, $min_n, $max_n); my @ids = (); my $count_prob = 1; my $count_fixed = 1; @@ -886,23 +878,14 @@ sub map_pins { my $current = []; if (@$current_map < 9) { my $limit = 9 - @$current_map; - $current = select_all( - "select id, title, easting, northing, distance - from problem_find_nearby(?, ?, $dist) as nearby, problem - where nearby.problem_id = problem.id - and state = 'confirmed'" . (@ids ? ' and id not in (' . join(',' , @ids) . ')' : '') . " - order by distance, created desc limit $limit", $mid_e, $mid_n); + $current = Problems::current_nearby($dist, join(',', @ids), $limit, $mid_e, $mid_n); foreach (@$current) { my $px = Page::os_to_px($_->{easting}, $x); my $py = Page::os_to_px($_->{northing}, $y, 1); $pins .= Page::display_pin($q, $px, $py, 'red', $count_prob++); } } - my $fixed = select_all( - "select id, title, easting, northing, distance - from problem_find_nearby(?, ?, $dist) as nearby, problem - where nearby.problem_id = problem.id and state='fixed' - order by created desc limit 9", $mid_e, $mid_n); + my $fixed = Problems::fixed_nearby($dist, $mid_e, $mid_n); foreach (@$fixed) { my $px = Page::os_to_px($_->{easting}, $x); my $py = Page::os_to_px($_->{northing}, $y, 1); diff --git a/web/reports.cgi b/web/reports.cgi index 4812fb49a..f9ad1b2a2 100755 --- a/web/reports.cgi +++ b/web/reports.cgi @@ -7,7 +7,7 @@ # Copyright (c) 2007 UK Citizens Online Democracy. All rights reserved. # Email: matthew@mysociety.org. WWW: http://www.mysociety.org # -# $Id: reports.cgi,v 1.16 2008-05-15 16:09:52 matthew Exp $ +# $Id: reports.cgi,v 1.17 2008-05-20 14:39:01 matthew Exp $ use strict; use Standard; @@ -25,6 +25,9 @@ sub main { # Look up council name, if given my $q_council = $q->param('council') || ''; + + $q_council = 2260 if $q->{site} eq 'scambs'; + my ($one_council, $area_type, $area_name); if ($q_council =~ /\D/) { (my $qc = $q_council) =~ s/ and / & /; @@ -199,8 +202,9 @@ sub main { ($all ? $q->a({href => NewURL($q, council=>undef, ward=>undef, all=>undef) }, 'see less detail') : $q->a({href => NewURL($q, council=>undef, ward=>undef, all=>1) }, 'see more details')) . + ($q->{site} eq 'scambs' ? '' : ' or go back and ' . - $q->a({href => '/reports' }, 'show all councils') . + $q->a({href => '/reports' }, 'show all councils') ) . '.'); print "<h2>$name</h2>\n"; if ($open{$one_council}) { |