diff options
| -rw-r--r-- | db/schema.sql | 18 | ||||
| -rw-r--r-- | perllib/Page.pm | 4 | ||||
| -rwxr-xr-x | web/index.cgi | 60 |
3 files changed, 46 insertions, 36 deletions
diff --git a/db/schema.sql b/db/schema.sql index 619c7b97b..c160e2e27 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.6 2006-09-22 17:21:36 matthew Exp $ +-- $Id: schema.sql,v 1.7 2006-09-22 17:38:00 matthew Exp $ -- -- secret @@ -81,12 +81,12 @@ create table problem ( -- category integer not null references category(id), name text not null, email text not null, - created timestamp not null, + created timestamp not null default ms_current_timestamp(), state text not null check ( state = 'unconfirmed' - or state = 'confirmed' - or state = 'fixed' - or state = 'hidden' + or state = 'confirmed' + or state = 'fixed' + or state = 'hidden' ) ); @@ -203,17 +203,15 @@ create function problem_find_nearby(double precision, double precision, double p create table comment ( id serial not null primary key, problem_id integer not null references problem(id), - - person_id integer references person(id), name text not null, - + email text not null, website text, whenposted timestamp not null default ms_current_timestamp(), text text not null, -- as entered by comment author state text not null check ( state = 'unconfirmed' - or state = 'confirmed' - or state = 'hidden' + or state = 'confirmed' + or state = 'hidden' ) -- 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 dd03671c1..076e9b330 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.13 2006-09-22 17:21:36 matthew Exp $ +# $Id: Page.pm,v 1.14 2006-09-22 17:38:00 matthew Exp $ # package Page; @@ -65,7 +65,7 @@ sub header ($$%) { <head> <!--[if lt IE 7.]> <script defer type="text/javascript" src="pngfix.js"></script> - <![endif]--> + <![endif]--> <title>Neighbourhood Fix-It</title> <style type="text/css">\@import url("css.css");</style> </head> diff --git a/web/index.cgi b/web/index.cgi index 7decea645..eb79e8337 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.18 2006-09-22 17:21:37 matthew Exp $ +# $Id: index.cgi,v 1.19 2006-09-22 17:38:01 matthew Exp $ use strict; require 5.8.0; @@ -30,16 +30,16 @@ use mySociety::Web qw(ent NewURL); 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) + 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) ); if (!dbh()->selectrow_array('select secret from secret for update of secret')) { - local dbh()->{HandleError}; - dbh()->do('insert into secret (secret) values (?)', {}, unpack('h*', mySociety::Util::random_bytes(32))); + local dbh()->{HandleError}; + dbh()->do('insert into secret (secret) values (?)', {}, unpack('h*', mySociety::Util::random_bytes(32))); } dbh()->commit(); @@ -113,6 +113,11 @@ sub submit_comment { push(@errors, 'Please enter your email') unless $input{email}; return display_problem($q, @errors) if (@errors); + dbh()->do("insert into comment + (problem_id, name, email, website, text, state) + values (?, ?, ?, ?, ?, 'unconfirmed')", + $input{id}, $input{name}, $input{email}, '', $input{comment}); + # Send confirmation email my $out = <<EOF; @@ -137,6 +142,13 @@ sub submit_problem { push(@errors, 'Please enter your email') unless $input{email}; return display_form($q, @errors) if (@errors); + dbh()->do("insert into problem + (postcode, easting, northing, title, detail, name, email, state) + values + ($input{pc}, $input{easting}, $input{northing}, $input{title}, + $input{detail}, $input{name}, $input{email}, 'unconfirmed')", + ); + # Send confirmation email my $out = <<EOF; @@ -163,7 +175,7 @@ sub display_form { } return display($q) unless $input{skipped} || ($pin_x && $pin_y) - || ($input{easting} && $input{northing}); + || ($input{easting} && $input{northing}); my $out = ''; $out .= '<h2>Reporting a problem</h2>'; @@ -172,7 +184,7 @@ sub display_form { } else { my ($px, $py, $easting, $northing); if ($pin_x && $pin_y) { - # Map was clicked on + # Map was clicked on $pin_x -= 254 while $pin_x > 254; $pin_y -= 254 while $pin_y > 254; $pin_y = 254 - $pin_y; @@ -181,12 +193,12 @@ sub display_form { $easting = 5000/31 * ($pin_tile_x + $pin_x/254); $northing = 5000/31 * ($pin_tile_y + $pin_y/254); } else { - # Normal form submission + # Normal form submission $px = os_to_px($input{easting}, $input{x}); - $py = os_to_px($input{northing}, $input{y}); - $easting = $input_h{easting}; - $northing = $input_h{northing}; - } + $py = os_to_px($input{northing}, $input{y}); + $easting = $input_h{easting}; + $northing = $input_h{northing}; + } $out .= display_map($q, $input{x}, $input{y}, 1, 0); $out .= '<p>You have located the problem at the location marked with a yellow pin on the map. If this is not the correct location, simply click on the map again.</p> <p>Please fill in details of the problem below:</p>'; @@ -254,7 +266,7 @@ EOF EOF my $current = select_all( "select id,title,easting,northing from problem where state='confirmed' - order by created desc limit 3"); + order by created desc limit 3"); foreach (@$current) { my $px = os_to_px($_->{easting}, $x); my $py = os_to_px($_->{northing}, $y); @@ -273,7 +285,7 @@ EOF EOF my $fixed = select_all( "select id,title from problem where state='fixed' - order by created desc limit 3"); + order by created desc limit 3"); foreach (@$fixed) { $out .= '<li><a href="' . NewURL($q, id=>$_->{id}, x=>undef, y=>undef) . '">'; $out .= $_->{title}; @@ -313,7 +325,7 @@ sub display_problem { # Get all information from database my $problem = dbh()->selectrow_arrayref( "select easting, northing, title, detail, name, extract(epoch from created) - from problem where id=? and state='confirmed'", {}, $input{id}); + from problem where id=? and state='confirmed'", {}, $input{id}); return display($q, 'Unknown problem ID') unless $problem; my ($easting, $northing, $title, $desc, $name, $time) = @$problem; my $x = $easting / (5000/31); @@ -340,19 +352,20 @@ sub display_problem { # Display comments my $comments = select_all( "select id, name, whenposted, text - from comment where problem_id = ? and state='confirmed' - order by whenposted desc", $input{id}); + from comment where problem_id = ? and state='confirmed' + order by whenposted desc", $input{id}); if (@$comments) { $out .= '<h3>Comments</h3>'; - foreach my $row (@$comments) { - $out .= "$row->{name} $row->{text}"; - } + foreach my $row (@$comments) { + $out .= "$row->{name} $row->{text}"; + } } $out .= '<h3>Add Comment</h3>'; if (@errors) { $out .= '<ul id="error"><li>' . join('</li><li>', @errors) . '</li></ul>'; } my $updates = $input{updates} ? ' checked' : ''; + # XXX: Should we have website too? $out .= <<EOF; <form method="post" action="./"> <fieldset> @@ -480,5 +493,4 @@ sub prettify_epoch { } return $tt; } - |