#!/usr/bin/perl -w -I/home/chris/projects/planning/lib
#
# search:
# Search planning applications.
#
# Copyright (c) 2003 Chris Lightfoot. All rights reserved.
# Email: chris@ex-parrot.com; WWW: http://www.ex-parrot.com/~chris/
#
my $rcsid = ''; $rcsid .= '$Id: search,v 1.4 2003/11/10 22:46:09 chris Exp $';
use strict;
use Error qw(:try);
use CGI qw(-no_xhtml -nosticky);
use CGI::Fast;
use DBI;
use HTML::Entities;
use Planning;
sub search_form ($) {
my ($q) = @_;
my $html = $q->start_form(-method => 'GET')
. $q->start_table({-class => 'formtable'});
# Search near a place.
$html .= $q->Tr(
$q->th("Applications near"),
$q->td("Grid reference ",
$q->textfield(-name => 'gridn', -size => 8), "N",
$q->textfield(-name => 'gride', -size => 8), "E;",
"distance ",
$q->popup_menu(-name => 'dist',
-values => [402, 805, 1610, 2415, 3220, 4025, 4830],
-labels => {
402 => '¼ mile',
805 => '½ mile',
1207 => '¾ mile',
1610 => '1 mile',
2415 => '1½ mile',
3220 => '2 miles',
4025 => '2½ miles',
4830 => '3 miles'
}),
$q->br(),
"(" . $q->a({href => 'position?' . $q->query_string()}, "Select from map") . ".)")
);
# Exact search for application number.
$html .= $q->Tr(
$q->th("Application number"),
$q->td($q->textfield(-name => 'appnumber', -size => 32), "(exact)"),
);
# Various text substring searches.
my @searches = (
['anytext', "Text anywhere"],
['address', "Address"],
['applicant', "Applicant"],
['officer', "Officer"],
['proposal', "Proposal"],
['type', "Type"],
['ward', "Ward"]
);
foreach (@searches) {
my ($field, $name) = @$_;
$html .=
$q->Tr(
$q->th($name),
$q->td($q->textfield(-name => $field, -size => 64))
);
};
# Date of application.
my @months = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
my %mlabels = map { $_ + 1 => $months[$_] } (0 .. 11);
# Before.
$html .=
$q->Tr(
$q->th(
"Before"
), $q->td(
$q->checkbox(-name => 'datebefore', -value => 1, -label => ''),
"Day " . $q->textfield(-name => 'Db', -size => 4)
. "; Month " . $q->popup_menu(-name => 'Mb', -values => [1 .. 12], -labels => \%mlabels)
. "; Year " . $q->textfield(-name => 'Yb', -size => 6)
)
);
# After.
$html .=
$q->Tr(
$q->th(
"After"
), $q->td(
$q->checkbox(-name => 'dateafter', -value => 1, -label => ''),
"Day " . $q->textfield(-name => 'Da', -size => 4)
. "; Month " . $q->popup_menu(-name => 'Ma', -values => [1 .. 12], -labels => \%mlabels)
. "; Year " . $q->textfield(-name => 'Ya', -size => 6)
)
);
$html .= $q->end_table() . $q->p($q->submit('Search')) . $q->end_form();
return $html;
}
try {
my $dbh = Planning::database();
while (my $q = new CGI::Fast()) {
#
# Idea here is that we build a query to select planning applications
# from the database. The parameters supply constraints on the search
# results which are separate AND clauses in the select statement.
#
my $stmt = 'select appnumber from applications';
my @where = ( );
# Application number.
if ($_ = $q->param('appnumber')) {
push(@where, "appnumber = " . $dbh->quote($_));
}
# Geographical query.
my ($N, $E, $r) = ($q->param('gridn'), $q->param('gride'), $q->param('dist'));
if ($N and $E and $r) {
push(@where, sprintf(
q(gridn >= %d and gridn <= %d and
gride >= %d and gride <= %d and
(gridn - %d) * (gridn - %d) + (gride - %d) * (gride - %d) < %d), # no exponentiation in sqlite...
$N - $r, $N + $r,
$E - $r, $E + $r,
$N, $N, $E, $E, $r * $r));
}
# Date query.
if ($q->param('datebefore')) {
my ($Yb, $Mb, $Db) = ($q->param('Yb'), $q->param('Mb'), $q->param('Db'));
push(@where, sprintf(q(date <= '%04d-%02d-%02d'), $Yb, $Mb, $Db)) if ($Yb and $Mb and $Db);
}
if ($q->param('dateafter')) {
my ($Ya, $Ma, $Da) = ($q->param('Ya'), $q->param('Ma'), $q->param('Da'));
push(@where, sprintf(q(date >= '%04d-%02d-%02d'), $Ya, $Ma, $Da)) if ($Ya and $Ma and $Da);
}
# Substring queries.
my @textfields = qw(officer ward type proposal address applicant);
# Text in any field.
my $anytext = $q->param('anytext');
if (defined($anytext) and $anytext !~ m#^\s*$#) {
my $Q = $dbh->quote($anytext);
push(@where, '('
. join(" or ", map { sprintf(q(%s like ('%%' || %s || '%%')), $_, $Q) } @textfields)
. ')');
}
# Text in specific fields.
foreach (@textfields) {
my $qtext = $q->param($_);
if (defined($qtext) and $qtext !~ m#^\s*$#) {
my $Q = $dbh->quote($qtext);
push(@where, sprintf(q(%s like ('%%' || %s || '%%')), $_, $Q));
}
}
# Assemble the query.
if (@where > 0) {
$stmt .= ' where ' . join(" and ", @where);
}
$stmt .= ' order by date desc'; # ascending/descending?
my @ids = map { $_->[0] } @{ $dbh->selectall_arrayref($stmt) };
my $mapq = new CGI("");
my $tbl;
use constant max_image_markers => 25;
use constant max_table_rows => 50;
my ($imgtruncated, $tbltruncated) = (0, 0);
if (@ids) {
# Build up table of planning applications, and the URL for a map
# showing them all.
$tbl = $q->start_table({-class => 'appltable'})
. $q->Tr(
$q->th('Application number'),
# $q->th('Date'),
$q->th('Address'),
$q->th('Proposal')#,
# $q->th('Applicant')
);
my ($nimg, $ntbl) = (0, 0);
my $odd = 0;
foreach (@ids) {
if ($ntbl++ > max_table_rows) {
$tbltruncated = 1;
last;
}
my $A = Planning::Application->new_by_id($dbh, $_);
my ($N, $E) = ($A->gridn(), $A->gride());
my $coords = '';
if ($N and $E) {
if ($nimg++ > max_image_markers) {
$imgtruncated = 1;
} else {
$mapq->append(-name => 'marker', -value => sprintf(q(%d,%d,%s), $N, $E, $A->appnumber()));
$coords = $q->br() . sprintf("Grid ref: %06d,%06d", $N, $E);
}
}
my $a2 = $A->address();
$a2 ||= '';
encode_entities($a2);
$a2 =~ s/,\s+/,
/g;
my $qq = new CGI("");
$qq->param('appnumber', $A->appnumber());
$tbl .= $q->Tr({ -class => $odd ? 'odd1' : 'even1' },
$q->td($q->a({href => $qq->self_url()}, encode_entities($A->appnumber())), $q->br(),
encode_entities($A->date()), $q->br(),
$q->a({-href => encode_entities(sprintf('orightml?appnumber=%s', $A->appnumber()))}, "Original HTML")),
$q->td($a2),
$q->td(encode_entities($A->proposal()))
)
. $q->Tr({ -class => $odd ? 'odd2' : 'even2' },
$q->td({-colspan => 4},
$q->strong("Applicant: "), encode_entities($A->applicant()), $q->br(),
$q->strong("Type: ") . encode_entities($A->type()) . "; "
. $q->strong("Officer: ") . encode_entities($A->officer()) . "; "
. $q->strong("Telephone: ") . encode_entities($A->telno())
. $coords)
);
### $tbl .= $q->Tr($X,
### $q->td($q->a({href => $qq->self_url()}, encode_entities($A->appnumber()))),
### $q->td(encode_entities($A->date())),
### $q->td($a2, $coords),
### $q->td(encode_entities($A->proposal()), $q->br(),
### $q->strong("Type: "), encode_entities($A->type())),
### $q->td(encode_entities($A->applicant()), $q->br(),
### $q->strong("Officer: "), encode_entities($A->officer()), $q->br(),
### $q->strong("Telephone: "), encode_entities($A->telno()))
### );
$odd = !$odd;
}
$tbl .= $q->end_table();
} else {
$tbl = $q->p($q->strong("Your search returned no results."));
}
print $q->header(-type => "text/html",
-charset => 'iso-8859-1',
-expires => '+1d'),
$q->start_html(-title => 'Planning applications search',
-style => { -src => '../std.css' }),
$q->h1('Planning applications search'),
search_form($q),
# $q->pre(encode_entities($stmt)),
$q->h2('Map of applications'),
$imgtruncated
? $q->p("(There are too many applications to show all of them on this map. Only the ", max_image_markers, "most recent are shown.)")
: "",
$q->img({ src => "map?" . $mapq->query_string(),
alt => 'Map of search results',
-width => $Planning::Map::mapimg_width,
-height => $Planning::Map::mapimg_height }),
$q->p({-class => 'smallprint'}, q(Image produced from the Ordnance Survey Get-a-map service. Image reproduced with kind permission of Ordnance Survey and Ordnance Survey of Northern Ireland.)),
$q->h2('List'),
$tbltruncated
? $q->p("(There are too many applications to show all of them in this table. Only the ", max_table_rows, "most recent are shown.)")
: "",
$tbl,
Planning::copyright_blurb($q),
$q->end_html();
$dbh->rollback(); # XXX work around SQLite brokenness
}
$dbh->disconnect();
} catch Error::Simple with {
my $E = shift;
my $msg = sprintf('%s:%d: %s', $E->file(), $E->line(), $E->text());
warn "caught fatal exception: $msg";
warn "aborting";
encode_entities($msg);
print "Status: 500\nContent-Type: text/html; charset=iso-8859-1\n\n",
CGI::start_html(-title => "Cambridge Planning: error",
-style => { -src => '../std.css' }),
CGI::h1("Error"),
q(
Unfortunately, something went wrong. The text of the error was:
), qq($msg), q(
Please try again later.), # Planning::copyright_blurb(new CGI("")), CGI::end_html(); };