11 files changed, 324 insertions, 519 deletions

diff --git a/bin/cron-wrapper b/bin/cron-wrapper
new file mode 100755
index 000000000..b93695cb0
--- /dev/null
+++ b/bin/cron-wrapper
@@ -0,0 +1,9 @@
+#!/usr/bin/env perl
+
+BEGIN {    # set all the paths to the perl code
+    use FindBin;
+    require "$FindBin::Bin/../setenv.pl";
+}
+
+exec { $ARGV[0] } @ARGV;
+
diff --git a/bin/export-norwegian-contacts b/bin/export-norwegian-contacts
new file mode 100644
index 000000000..8a7d438e5
--- /dev/null
+++ b/bin/export-norwegian-contacts
@@ -0,0 +1,97 @@
+#!/usr/bin/perl
+
+# export-norwegian-contacts:
+# Export initial contact list from fiksgatami in a format usable by
+# load-norwegian-contact.
+#
+# The format is
+# ID;Name;email-address;Category1,Category2,...
+#
+# Based on script load-contacts copyright (c) 2006 UK Citizens Online
+# Democracy and script load-norwegian-contacts copyright (c) 2011
+# Petter Reinholdtsen.
+# Copyright 2011 Petter Reinholdtsen.
+#
+# $Id: load-norwegian-contacts,v 1.0 2007-08-02 11:44:59 matthew Exp $
+
+use strict;
+use warnings;
+require 5.8.0;
+use open OUT => ':utf8';
+
+# Horrible boilerplate to set up appropriate library paths.
+use FindBin;
+use lib "$FindBin::Bin/../perllib";
+use lib "$FindBin::Bin/../commonlib/perllib";
+
+use mySociety::Config;
+use mySociety::DBHandle qw(dbh select_all);
+use mySociety::MaPit;
+
+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)
+    );
+}
+
+my $datafile = shift;
+
+open(my $fh, '>', $datafile) or die "Unable to write to $datafile";
+
+# Categories used by more than half the areas is used as the default
+# list.
+my $sql =
+    "SELECT category FROM (SELECT category, COUNT(*) from contacts ".
+    "                      WHERE confirmed = 'true' AND deleted = 'false' ".
+    "                      GROUP BY category) as c ".
+    "  WHERE count > (SELECT COUNT(*)/2 FROM contacts ".
+    "                 WHERE category = 'Annet') ".
+    "  ORDER BY category";
+my $defcategoriesref = dbh()->selectcol_arrayref($sql);
+print $fh "0000;default;;", join(',', @{$defcategoriesref}), "\n";
+
+my %categorygroup;
+$sql = "SELECT area_id, email, category FROM contacts ORDER BY category";
+my $contactref = dbh()->selectall_arrayref($sql, { Slice => {} });
+my @area_ids;
+for my $row (@{$contactref}) {
+    my $key = $row->{area_id} .':'. $row->{email};
+    push(@area_ids, $row->{area_id});
+    if (exists $categorygroup{$key}) {
+        push(@{$categorygroup{$key}}, $row->{category});
+    } else {
+        $categorygroup{$key} = [ $row->{category} ];
+    }
+}
+
+my $areas_info = mySociety::MaPit::call('areas', \@area_ids);
+
+my @list;
+for my $key (keys %categorygroup) {
+    my ($area_id, $email) = split(/:/, $key);
+    my $categoriesref = $categorygroup{$key};
+    my $areaname = $areas_info->{$area_id}->{name};
+    my $areadigits = ($area_id < 100) ? 2 : 4;
+    if (identical_lists($defcategoriesref, $categoriesref)) {
+        push(@list,sprintf("%0${areadigits}d;%s;%s\n",
+                           $area_id, $areaname, $email));
+    } else {
+        push(@list, sprintf("%0${areadigits}d;%s;%s;%s\n",
+                            $area_id, $areaname, $email,
+                            join(',', @{$categoriesref})));
+    }
+}
+
+print $fh sort @list;
+
+sub identical_lists {
+    my ($a, $b) = @_;
+    return !(join(',', @{$a}) cmp join(',', @{$b}));
+}
+
+exit 0;
diff --git a/bin/gettext-extract b/bin/gettext-extract
index 24defd014..55623e86c 100755
--- a/bin/gettext-extract
+++ b/bin/gettext-extract
@@ -4,10 +4,8 @@
 # Generate English language .po files from the source code and email templates,
 # for FixMyStreet. Writes the output to appropriate .po files in locale/.
 #
-# Copyright (c) 2008 UK Citizens Online Democracy. All rights reserved.
+# Copyright (c) 2011 UK Citizens Online Democracy. All rights reserved.
 # Email: matthew@mysociety.org; WWW: http://www.mysociety.org/
-#
-# $Id: gettext-extract,v 1.14 2009-11-30 10:21:52 louise Exp $
 
 if [ -e ../../locale ]
 then
@@ -25,56 +23,31 @@ fi
 fi
 fi
 
-# Take chunk of text and escape each line in it for putting in catalogue
-function plain_gettext_escape() {
-    IFS=""
-    while read LINE
-    do
-        LINE=${LINE//\"/\\\"}
-        echo \"$LINE\\n\"
-    done
-}
-
 # File to write to, clear it to start with
 PO=locale/FixMyStreet.po
 rm -f $PO
 
 # Extract from Perl
-xgettext --add-comments=TRANS --language=Perl --keyword=_ --keyword=nget:1,2 --from-code=utf-8 -o $PO `find perllib -name "*.pm"` web/*.cgi bin/send-* db/*.pl web-admin/*.cgi
+xgettext.pl --gnu-gettext --verbose --output $PO --plugin perl=* --plugin tt2 --directory perllib --directory templates/web --directory db --directory bin
 
 # Fix headers
 TEMP=`tempfile`
+NOW=`date +"%Y-%m-%d %H:%M%z"`
 cat $PO | sed "
         s/SOME DESCRIPTIVE TITLE/FixMyStreet original .po file, autogenerated by gettext-extract/;
-        s/YEAR THE PACKAGE'S COPYRIGHT HOLDER/2008 UK Citizens Online Democracy/;
+        s/YEAR THE PACKAGE'S COPYRIGHT HOLDER/2011 UK Citizens Online Democracy/;
         s/PACKAGE package/main FixMyStreet code/;
-        s/FIRST AUTHOR <EMAIL@ADDRESS>, YEAR./Matthew Somerville <matthew@mysociety.org>, 2008-04-15./;
+        s/FIRST AUTHOR <EMAIL@ADDRESS>, YEAR./Matthew Somerville <matthew@mysociety.org>, 2011-06-03./;
 
-        s/PACKAGE VERSION/1.0/;
-        s/Report-Msgid-Bugs-To: /Report-Msgid-Bugs-To: matthew@mysociety.org/;
+        s/PACKAGE VERSION/1.0\\\n\"\n\"Report-Msgid-Bugs-To: matthew@mysociety.org/;
+        s/POT-Creation-Date: YEAR-MO-DA HO:MI+ZONE/POT-Creation-Date: $NOW/;
         s/LL@li.org/team@fixmystreet.com/;
-	s/charset=CHARSET/charset=UTF-8/;
+        s/charset=CHARSET/charset=UTF-8/;
+        s/8bit/8bit\\\n\"\n\"Plural-Forms: nplurals=INTEGER; plural=EXPRESSION;/;
 " >> $TEMP
 mv $TEMP $PO
 
-# XXX The XSL page needs including?
-
-# Extract email templates
-echo >> $PO
-echo '#. Please leave the first word "Subject:" untranslated' >> $PO
-for X in templates/emails/* templates/emails/emptyhomes/* 
-do
-    # TODO: Should check for "*~" type filenames too, and do the *-livesimply case
-    # with wildcards rather than checking per template
-    if [ ! -d "$X" ]
-    then
-        echo >> $PO
-        echo "#: $X" >> $PO
-        echo msgid \"\" >> $PO
-        cat "$X" | plain_gettext_escape >> $PO
-        echo msgstr \"\" >> $PO
-    fi
-done
+echo "$( bin/gettext-nget-patch )" >> $PO
 
 bin/make_emptyhomes_po
 
diff --git a/bin/gettext-nget-patch b/bin/gettext-nget-patch
new file mode 100755
index 000000000..223bcc816
--- /dev/null
+++ b/bin/gettext-nget-patch
@@ -0,0 +1,42 @@
+#!/usr/bin/perl
+#
+# xgettext doesn't deal with TT files, but xgettext.pl doesn't find nget()s, sigh.
+# This will find the nget()s and output a .po file excerpt.
+
+use File::Find qw/find/;
+
+my %out;
+
+find( sub {
+    next unless -f;
+    open (FP, $_) or die $!;
+    while (<FP>) {
+        next unless /nget/;
+        my $line = $.;
+        my $text = $_;
+        do {
+            $text .= <FP>;
+        } until $text =~ /\)/;
+        $text =~ /nget\(\s*"(.*?)"\s*,\s*"(.*?)"\s*,\s*(.*?)\s*\)/s;
+        $out{$1} = {
+            file => $File::Find::name,
+            line => $line,
+            s => $1,
+            p => $2,
+        };
+    }
+    close FP;
+}, 'templates');
+
+foreach (values %out) {
+    print <<EOF;
+
+#: $_->{file}:$_->{line}
+#, perl-format
+msgid "$_->{s}"
+msgid_plural "$_->{p}"
+msgstr[0] ""
+msgstr[1] ""
+EOF
+}
+
diff --git a/bin/handlemail b/bin/handlemail
index c5854a9ab..8b8e03be9 100755
--- a/bin/handlemail
+++ b/bin/handlemail
@@ -47,7 +47,7 @@ if ($data{is_bounce_message}) {
 
 # Not a bounce, send an automatic response
 my $template = 'reply-autoresponse';
-open FP, "$FindBin::Bin/../templates/emails/$template" or exit 75;
+open FP, "$FindBin::Bin/../templates/email/default/$template" or exit 75;
 $template = join('', <FP>);
 close FP;
 
diff --git a/bin/import-flickr b/bin/import-flickr
deleted file mode 100755
index f4a838547..000000000
--- a/bin/import-flickr
+++ /dev/null
@@ -1,112 +0,0 @@
-#!/usr/bin/perl -w
-
-# import-flickr:
-# Get new Flickr photos (uploaded from cameras, hopefully!)
-#
-# Copyright (c) 2007 UK Citizens Online Democracy. All rights reserved.
-# Email: matthew@mysociety.org. WWW: http://www.mysociety.org
-#
-# $Id: import-flickr,v 1.9 2008-10-09 17:18:02 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/../commonlib/perllib";
-use File::Slurp;
-use LWP::Simple;
-
-use Utils;
-use mySociety::AuthToken;
-use mySociety::Config;
-use mySociety::DBHandle qw(dbh select_all);
-use mySociety::EmailUtil;
-use mySociety::Email;
-
-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)
-    );
-}
-
-my $key = mySociety::Config::get('FLICKR_API');
-my $url = 'http://api.flickr.com/services/rest/?method=flickr.photos.search&tags=fixmystreet&extras=geo,machine_tags&api_key=' . $key . '&user_id=';
-my $ids = select_all("select nsid from partial_user where service='flickr'");
-my $result = '';
-foreach (@$ids) {
-    my $api_lookup = get($url . $_->{nsid});
-    next unless $api_lookup;
-    $result .= $api_lookup;
-}
-
-my %ids;
-my $st = select_all('select id from flickr_imported');
-foreach (@$st) {
-    $ids{$_->{id}} = 1;
-}
-
-# XXX: Hmm... Use format=perl now Cal has added it for me! :)
-while ($result =~ /<photo id="([^"]*)" owner="([^"]*)" secret="([^"]*)" server="([^"]*)" farm="([^"]*)" title="([^"]*)".*?latitude="([^"]*)" longitude="([^"]*)".*?machine_tags="([^"]*)"/g) {
-    my ($id, $owner, $secret, $server, $farm, $title, $latitude, $longitude, $machine) = ($1, $2, $3, $4, $5, $6, $7, $8, $9);
-    next if $ids{$id};
-    if ($machine =~ /geo:/ && !$latitude && !$longitude) {
-        # Have to fetch raw tags, as otherwise can't tell if it's negative, or how many decimal places
-        my $url = 'http://api.flickr.com/services/rest/?method=flickr.tags.getListPhoto&api_key=' . $key . '&photo_id=' . $id;
-        my $tags = get($url);
-        ($longitude) = $tags =~ /raw="geo:lon=([^"]*)"/i;
-        ($latitude) = $tags =~ /raw="geo:lat=([^"]*)"/i;
-    }
-    my $url = "http://farm$farm.static.flickr.com/$server/".$id.'_'.$secret.'_m.jpg';
-    my $image = get($url);
-    problem_create($id, $owner, $title, $latitude, $longitude, $image);
-}
-
-sub problem_create {
-    my ($photo_id, $owner, $title, $latitude, $longitude, $image) = @_;
-    my ($name, $email) = dbh()->selectrow_array("select name, email from partial_user where service='flickr' and nsid=?", {}, $owner);
-
-    # set some defaults
-    $name ||= '';
-    $latitude ||= 0;
-    $longitude ||= 0;
-
-    my $id = dbh()->selectrow_array("select nextval('problem_id_seq')");
-    Utils::workaround_pg_bytea("insert into problem
-        (id, postcode, latitude, longitude, title, detail, name,
-         email, phone, photo, state, used_map, anonymous, category, areas)
-        values
-        (?, '', ?, ?, ?, '', ?, ?, '', ?, 'partial', 't', 'f', '', '')", 7,
-        $id, $latitude, $longitude, $title, $name, $email, $image
-    );
-
-    dbh()->do('insert into flickr_imported (id, problem_id) values (?, ?)', {}, $photo_id, $id);
-
-    # XXX: Needs to only send email once to user per batch of photos, not one per photo?
-    my $template = File::Slurp::read_file("$FindBin::Bin/../templates/emails/flickr-submit");
-    my %h = ();
-    my $token = mySociety::AuthToken::store('partial', $id);
-    $h{name} = $name;
-    $h{url} = mySociety::Config::get('BASE_URL') . '/L/' . $token;
-
-    my $body = mySociety::Email::construct_email({
-        _template_ => $template,
-        _parameters_ => \%h,
-        To => $name ? [ [ $email, $name ] ] : $email,
-        From => [ mySociety::Config::get('CONTACT_EMAIL'), 'FixMyStreet' ],
-    });
-
-    my $result = mySociety::EmailUtil::send_email($body, mySociety::Config::get('CONTACT_EMAIL'), $email);
-    if ($result == mySociety::EmailUtil::EMAIL_SUCCESS) {
-        dbh()->commit();
-    } else {
-        dbh()->rollback();
-    }
-}
-
diff --git a/bin/send-alerts b/bin/send-alerts
index c52af4059..89dc18ee7 100755
--- a/bin/send-alerts
+++ b/bin/send-alerts
@@ -5,37 +5,18 @@
 #
 # Copyright (c) 2007 UK Citizens Online Democracy. All rights reserved.
 # Email: matthew@mysociety.org. WWW: http://www.mysociety.org
-#
-# $Id: send-alerts,v 1.5 2010-01-06 16:50:25 louise Exp $
 
 use strict;
 require 5.8.0;
 
 use CGI; # XXX
-
-# Horrible boilerplate to set up appropriate library paths.
-use FindBin;
-use lib "$FindBin::Bin/../perllib";
-use lib "$FindBin::Bin/../commonlib/perllib";
 use CronFns;
 
 use mySociety::Config;
-use mySociety::DBHandle qw(dbh);
-use FixMyStreet::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)
-    );
-}
+use FixMyStreet::App;
 
 my $site = CronFns::site(mySociety::Config::get('BASE_URL'));
 CronFns::language($site);
-my $testing_email = mySociety::Config::get('TESTING_EMAIL');
-FixMyStreet::Alert::email_alerts($testing_email);
+
+FixMyStreet::App->model('DB::AlertType')->email_alerts();
 
diff --git a/bin/send-questionnaires b/bin/send-questionnaires
index d3945bf12..d6e269e32 100755
--- a/bin/send-questionnaires
+++ b/bin/send-questionnaires
@@ -3,115 +3,23 @@
 # send-questionnaires:
 # Send out creator questionnaires
 #
-# Copyright (c) 2007 UK Citizens Online Democracy. All rights reserved.
+# Copyright (c) 2011 UK Citizens Online Democracy. All rights reserved.
 # Email: matthew@mysociety.org. WWW: http://www.mysociety.org
-#
-# $Id: send-questionnaires,v 1.21 2010-01-06 14:44:45 louise 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/../commonlib/perllib";
-use File::Slurp;
 use CGI; # XXX Awkward kludge
 use Encode;
 use CronFns;
 
-use Page;
-use mySociety::AuthToken;
+use FixMyStreet::App;
 use mySociety::Config;
-use mySociety::DBHandle qw(dbh select_all);
-use mySociety::Email;
-use mySociety::Locale;
-use mySociety::MaPit;
-use mySociety::EmailUtil;
-use mySociety::Random qw(random_bytes);
-
-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)
-    );
-}
-
-my ($verbose, $nomail) = CronFns::options();
-my $site = CronFns::site(mySociety::Config::get('BASE_URL'));
-CronFns::language($site);
-
-# Select all problems that need a questionnaire email sending
-my $unsent = select_all(
-    "select id, council, category, title, detail, name, email, cobrand, cobrand_data, lang,
-        extract(epoch from ms_current_timestamp()-created) as created
-    from problem
-    where state in ('confirmed','fixed')
-        and whensent is not null
-        and whensent < ms_current_timestamp() - '4 weeks'::interval
-        and send_questionnaire = 't'
-        and ( (select max(whensent) from questionnaire where problem.id=problem_id) is null
-            or (select max(whenanswered) from questionnaire where problem.id=problem_id) < ms_current_timestamp() - '4 weeks'::interval)
-    order by created desc
-");
-
-foreach my $row (@$unsent) {
-    my @all_councils = split /,|\|/, $row->{council};
-    my $cobrand = $row->{cobrand};
-    my $lang = $row->{lang};
-    Cobrand::set_lang_and_domain($cobrand, $lang, 1); 
-    # Cobranded and non-cobranded messages can share a database. In this case, the conf file 
-    # should specify a vhost to send the reports for each cobrand, so that they don't get sent 
-    # more than once if there are multiple vhosts running off the same database. The email_host
-    # call checks if this is the host that sends mail for this cobrand.
-    next unless (Cobrand::email_host($cobrand));
-    my ($councils, $missing) = $row->{council} =~ /^([\d,]+)(?:\|([\d,]+))?/;
-    my @councils = split /,/, $councils;
-    my $areas_info = mySociety::MaPit::call('areas', \@all_councils);
-    my $template = File::Slurp::read_file("$FindBin::Bin/../templates/emails/questionnaire");
-
-    my %h = map { $_ => $row->{$_} } qw/name title detail category/;
-    $h{created} = Page::prettify_duration($row->{created}, 'week');
-    $h{councils} = join(' and ', map { $areas_info->{$_}->{name} } @councils);
-
-    my $id = dbh()->selectrow_array("select nextval('questionnaire_id_seq');");
-    dbh()->do('insert into questionnaire (id, problem_id, whensent)
-        values (?, ?, ms_current_timestamp())', {}, $id, $row->{id});
-    dbh()->do("update problem set send_questionnaire = 'f' where id=?", {}, $row->{id});
-
-    my $token = mySociety::AuthToken::store('questionnaire', $id);
-    $h{url} = Cobrand::base_url_for_emails($cobrand, $row->{cobrand_data}) . '/Q/' . $token;
-
-    my $sender = Cobrand::contact_email($cobrand);
-    my $sender_name = _(Cobrand::contact_name($cobrand));
-    $sender =~ s/team/fms-DO-NOT-REPLY/;
-    $template = _($template);
-    my $email = mySociety::Locale::in_gb_locale { mySociety::Email::construct_email({
-        _template_ => $template,
-        _parameters_ => \%h,
-        To => [ [ $row->{email}, $row->{name} ] ],
-        From => [ $sender, $sender_name ],
-        'Message-ID' => sprintf('<ques-%s-%s@mysociety.org>', time(), unpack('h*', random_bytes(5, 1))),
-    }) };
 
-    print "Sending questionnaire $id, problem $row->{id}, token $token to $row->{email}\n" if $verbose;
+my %params;
+( $params{verbose}, $params{nomail} ) = CronFns::options();
+$params{site} = CronFns::site(mySociety::Config::get('BASE_URL'));
+CronFns::language($params{site});
 
-    my $result;
-    if ($nomail) {
-        $result = -1;
-    } else {
-        $result = mySociety::EmailUtil::send_email($email, $sender, $row->{email});
-    }
-    if ($result == mySociety::EmailUtil::EMAIL_SUCCESS) {
-        print "  ...success\n" if $verbose;
-        dbh()->commit();
-    } else {
-        print " ...failed\n" if $verbose;
-        dbh()->rollback();
-    }
-}
+FixMyStreet::App->model('DB::Questionnaire')->send_questionnaires( \%params );
 
diff --git a/bin/send-questionnaires-eha b/bin/send-questionnaires-eha
deleted file mode 100755
index 2e0af8f4f..000000000
--- a/bin/send-questionnaires-eha
+++ /dev/null
@@ -1,114 +0,0 @@
-#!/usr/bin/perl -w
-
-# send-questionnaires-eha:
-# Send out creator questionnaires
-#
-# Copyright (c) 2008 UK Citizens Online Democracy. All rights reserved.
-# Email: matthew@mysociety.org. WWW: http://www.mysociety.org
-#
-# $Id: send-questionnaires-eha,v 1.6 2009-09-10 09:10:56 matthew Exp $
-
-use strict;
-require 5.8.0;
-
-use CGI; # XXX
-
-# Horrible boilerplate to set up appropriate library paths.
-use FindBin;
-use lib "$FindBin::Bin/../perllib";
-use lib "$FindBin::Bin/../commonlib/perllib";
-use File::Slurp;
-use CronFns;
-
-use mySociety::AuthToken;
-use mySociety::Config;
-use mySociety::DBHandle qw(dbh select_all);
-use mySociety::Email;
-use mySociety::Locale;
-use mySociety::EmailUtil;
-use mySociety::Random qw(random_bytes);
-
-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)
-    );
-}
-
-# Set up site, language etc.
-my ($verbose, $nomail) = CronFns::options();
-my $site = CronFns::site(mySociety::Config::get('BASE_URL'));
-CronFns::language($site);
-
-send_q('4 weeks');
-send_q('26 weeks');
-
-# ---
-
-sub send_q {
-    my ($period) = @_;
-
-    (my $template = $period) =~ s/ //;
-    $template = File::Slurp::read_file("$FindBin::Bin/../templates/emails/questionnaire-eha-$template");
-
-    my $query = "select id, category, title, detail, name, email, lang
-        from problem
-        where state in ('confirmed', 'fixed')
-            and whensent is not null
-            and send_questionnaire = 't'
-            and whensent < ms_current_timestamp() - '$period'::interval
-            and ";
-    if ($period eq '4 weeks') {
-        $query .= '(select max(whensent) from questionnaire where problem.id=problem_id) is null';
-    } else {
-        $query .= '(select max(whensent) from questionnaire where problem.id=problem_id) is not null';
-    }
-    $query .= ' order by created desc';
-
-    my $unsent = select_all($query);
-    foreach my $row (@$unsent) {
-        my %h = map { $_ => $row->{$_} } qw/name title detail category/;
-
-        mySociety::Locale::change($row->{lang});
-
-        my $id = dbh()->selectrow_array("select nextval('questionnaire_id_seq');");
-        dbh()->do('insert into questionnaire (id, problem_id, whensent)
-            values (?, ?, ms_current_timestamp())', {}, $id, $row->{id});
-        dbh()->do("update problem set send_questionnaire = 'f' where id=?", {}, $row->{id})
-            if $period eq '26 weeks';
-
-        my $token = mySociety::AuthToken::store('questionnaire', $id);
-        $h{url} = mySociety::Config::get('BASE_URL') . '/Q/' . $token;
-
-        my $sender = mySociety::Config::get('CONTACT_EMAIL');
-        $template = _($template);
-        my $email = mySociety::Locale::in_gb_locale { mySociety::Email::construct_email({
-            _template_ => $template,
-            _parameters_ => \%h,
-            To => [ [ $row->{email}, $row->{name} ] ],
-            From => [ $sender, _('Report Empty Homes') ],
-            'Message-ID' => sprintf('<ques-%s-%s@emptyhomes.com>', time(), unpack('h*', random_bytes(5, 1))),
-        }) };
-
-        print "Sending questionnaire $id, problem $row->{id}, token $token to $row->{email}\n" if $verbose;
-
-        my $result;
-        if ($nomail) {
-            $result = -1;
-        } else {
-            $result = mySociety::EmailUtil::send_email($email, $sender, $row->{email});
-        }
-        if ($result == mySociety::EmailUtil::EMAIL_SUCCESS) {
-            print "  ...success\n" if $verbose;
-            dbh()->commit();
-        } else {
-            print " ...failed\n" if $verbose;
-            dbh()->rollback();
-        }
-    }
-}
-
diff --git a/bin/send-reports b/bin/send-reports
index d51276e9d..1af3ba1ea 100755
--- a/bin/send-reports
+++ b/bin/send-reports
@@ -3,18 +3,12 @@
 # send-reports:
 # Send new problem reports to councils
 #
-# Copyright (c) 2007 UK Citizens Online Democracy. All rights reserved.
+# Copyright (c) 2011 UK Citizens Online Democracy. All rights reserved.
 # Email: matthew@mysociety.org. WWW: http://www.mysociety.org
-#
-# $Id: send-reports,v 1.79 2010-01-06 16:50:26 louise 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/../commonlib/perllib";
 use Digest::MD5;
 use Encode;
 use Error qw(:try);
@@ -25,87 +19,67 @@ use LWP::Simple;
 use CGI; # Trying awkward kludge
 use CronFns;
 
+use FixMyStreet::App;
+
 use EastHantsWSDL;
-use Cobrand;
 use Utils;
 use mySociety::Config;
-use mySociety::DBHandle qw(dbh);
-use mySociety::Email;
 use mySociety::EmailUtil;
-use mySociety::Locale;
 use mySociety::MaPit;
-use mySociety::Random qw(random_bytes);
 use mySociety::Web qw(ent);
 
-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)
-    );
-}
-
 # Set up site, language etc.
 my ($verbose, $nomail) = CronFns::options();
 my $base_url = mySociety::Config::get('BASE_URL');
 my $site = CronFns::site($base_url);
 
-my $query = "SELECT id, council, category, title, detail, name, email, phone,
-    used_map, latitude, longitude, (photo is not null) as has_photo, lang, 
-    cobrand, cobrand_data, date_trunc('second', confirmed) as confirmed, postcode
-      FROM problem
-      WHERE state in ('confirmed','fixed')
-        AND whensent IS NULL
-        AND council IS NOT NULL";
-my $unsent = dbh()->selectall_arrayref($query, { Slice => {} });
-
+my $unsent = FixMyStreet::App->model("DB::Problem")->search( {
+    state => [ 'confirmed', 'fixed' ],
+    whensent => undef,
+    council => { '!=', undef },
+} );
 my (%notgot, %note);
-my $cobrand;
-my $cobrand_data;
-foreach my $row (@$unsent) {
+while (my $row = $unsent->next) {
     
-    $cobrand = $row->{cobrand};
-    $cobrand_data = $row->{cobrand_data};
+    my $cobrand = FixMyStreet::Cobrand->get_class_for_moniker($row->cobrand)->new();
+
     # Cobranded and non-cobranded messages can share a database. In this case, the conf file 
     # should specify a vhost to send the reports for each cobrand, so that they don't get sent 
     # more than once if there are multiple vhosts running off the same database. The email_host
     # call checks if this is the host that sends mail for this cobrand.
-    next unless (Cobrand::email_host($cobrand));
-    Cobrand::set_lang_and_domain($cobrand, $row->{lang}, 1);
-    if (dbh()->selectrow_array('select email from abuse where lower(email)=?', {}, lc($row->{email}))) {
-        dbh()->do("update problem set state='hidden' where id=?", {}, $row->{id});
-        dbh()->commit();
+    next unless $cobrand->email_host();
+    $cobrand->set_lang_and_domain($row->lang, 1);
+    if ( $row->is_from_abuser ) {
+        $row->update( { state => 'hidden' } );
         next;
     }
 
     my $send_email = 0;
     my $send_web = 0;
 
-    mySociety::Locale::change($row->{lang});
-
     # Template variables for the email
-    my $email_base_url = Cobrand::base_url_for_emails($cobrand, $cobrand_data);
-    my %h = map { $_ => $row->{$_} } qw/id title detail name email phone category latitude longitude confirmed used_map/;
-    $h{query} = $row->{postcode};
-    $h{url} = $email_base_url . '/report/' . $row->{id};
+    my $email_base_url = $cobrand->base_url_for_emails($row->cobrand_data);
+    my %h = map { $_ => $row->$_ } qw/id title detail name category latitude longitude used_map/;
+    map { $h{$_} = $row->user->$_ } qw/email phone/;
+    $h{confirmed} = DateTime::Format::Pg->format_datetime( $row->confirmed->truncate (to => 'second' ) );
+
+    $h{query} = $row->postcode;
+    $h{url} = $email_base_url . '/report/' . $row->id;
     $h{phone_line} = $h{phone} ? _('Phone:') . " $h{phone}\n\n" : '';
-    if ($row->{has_photo}) {
+    if ($row->photo) {
         $h{has_photo} = _("This web page also contains a photo of the problem, provided by the user.") . "\n\n";
-        $h{image_url} = $email_base_url . '/photo?id=' . $row->{id};
+        $h{image_url} = $email_base_url . '/photo?id=' . $row->id;
     } else {
         $h{has_photo} = '';
         $h{image_url} = '';
     }
-    $h{fuzzy} = $row->{used_map} ? _('To view a map of the precise location of this issue')
+    $h{fuzzy} = $row->used_map ? _('To view a map of the precise location of this issue')
         : _('The user could not locate the problem on a map, but to see the area around the location they entered');
     $h{closest_address} = '';
     
     # If we are in the UK include eastings and northings, and nearest stuff
     $h{easting_northing} = '';
-    if ( mySociety::Config::get('COUNTRY') eq 'GB' ) {
+    if ( $cobrand->country eq 'GB' ) {
 
         ( $h{easting}, $h{northing} ) = Utils::convert_latlon_to_en( $h{latitude}, $h{longitude} );
 
@@ -114,37 +88,42 @@ foreach my $row (@$unsent) {
           = "Easting: $h{easting}\n\n"                   #
           . "Northing: $h{northing}\n\n";
 
-        $h{closest_address} = find_closest($row, $h{latitude}, $h{longitude});
     }
-    $h{closest_address_machine} = $h{closest_address};
+
+    if ( $row->used_map ) {
+        $h{closest_address} = $cobrand->find_closest( $h{latitude}, $h{longitude} );
+    }
 
     my (@to, @recips, $template, $areas_info);
     if ($site eq 'emptyhomes') {
 
-        my $council = $row->{council};
+        my $council = $row->council;
         $areas_info = mySociety::MaPit::call('areas', $council);
         my $name = $areas_info->{$council}->{name};
-        my ($council_email, $confirmed, $note) = dbh()->selectrow_array(
-            "SELECT email,confirmed,note FROM contacts WHERE deleted='f'
-                and area_id=? AND category=?", {}, $council, 'Empty property');
+        my $contact = FixMyStreet::App->model("DB::Contact")->find( {
+            deleted => 0,
+            area_id => $council,
+            category => 'Empty property',
+        } );
+        my ($council_email, $confirmed, $note) = ( $contact->email, $contact->confirmed, $contact->note );
         unless ($confirmed) {
             $note = 'Council ' . $council . ' deleted' unless $note;
             $council_email = 'N/A' unless $council_email;
-            $notgot{$council_email}{$row->{category}}++;
-            $note{$council_email}{$row->{category}} = $note;
+            $notgot{$council_email}{$row->category}++;
+            $note{$council_email}{$row->category} = $note;
             next;
         }
 
         push @to, [ $council_email, $name ];
         @recips = ($council_email);
         $send_email = 1;
-        $template = File::Slurp::read_file("$FindBin::Bin/../templates/emails/submit-eha");
+        $template = File::Slurp::read_file("$FindBin::Bin/../templates/email/emptyhomes/" . $row->lang . "/submit.txt");
 
     } else {
 
         # XXX Needs locks!
-        my @all_councils = split /,|\|/, $row->{council};
-        my ($councils, $missing) = $row->{council} =~ /^([\d,]+)(?:\|([\d,]+))?/;
+        my @all_councils = split /,|\|/, $row->council;
+        my ($councils, $missing) = $row->council =~ /^([\d,]+)(?:\|([\d,]+))?/;
         my @councils = split(/,/, $councils);
         $areas_info = mySociety::MaPit::call('areas', \@all_councils);
         my (@dear, %recips);
@@ -158,18 +137,21 @@ foreach my $row (@$unsent) {
             } elsif ($areas_info->{$council}->{type} eq 'LBO') { # London
                 $send_web = 'london';
             } else {
-                my ($council_email, $confirmed, $note) = dbh()->selectrow_array(
-                    "SELECT email,confirmed,note FROM contacts WHERE deleted='f'
-                        and area_id=? AND category=?", {}, $council, $row->{category});
-                $council_email = essex_contact($row->{latitude}, $row->{longitude}) if $council == 2225;
-                $council_email = oxfordshire_contact($row->{latitude}, $row->{longitude}) if $council == 2237 && $council_email eq 'SPECIAL';
+                my $contact = FixMyStreet::App->model("DB::Contact")->find( {
+                    deleted => 0,
+                    area_id => $council,
+                    category => $row->category
+                } );
+                my ($council_email, $confirmed, $note) = ( $contact->email, $contact->confirmed, $contact->note );
+                $council_email = essex_contact($row->latitude, $row->longitude) if $council == 2225;
+                $council_email = oxfordshire_contact($row->latitude, $row->longitude) if $council == 2237 && $council_email eq 'SPECIAL';
                 unless ($confirmed) {
                     $all_confirmed = 0;
-                    $note = 'Council ' . $row->{council} . ' deleted'
+                    $note = 'Council ' . $row->council . ' deleted'
                         unless $note;
                     $council_email = 'N/A' unless $council_email;
-                    $notgot{$council_email}{$row->{category}}++;
-                    $note{$council_email}{$row->{category}} = $note;
+                    $notgot{$council_email}{$row->category}++;
+                    $note{$council_email}{$row->category} = $note;
                 }
                 push @to, [ $council_email, $name ];
                 $recips{$council_email} = 1;
@@ -179,9 +161,12 @@ foreach my $row (@$unsent) {
         @recips = keys %recips;
         next unless $all_confirmed;
 
-        $template = 'submit-council';
-        $template = 'submit-brent' if $row->{council} eq 2488 || $row->{council} eq 2237;
-        $template = File::Slurp::read_file("$FindBin::Bin/../templates/emails/$template");
+        $template = 'submit.txt';
+        $template = 'submit-brent.txt' if $row->council eq 2488 || $row->council eq 2237;
+        my $template_path = FixMyStreet->path_to( "templates", "email", $cobrand->moniker, $template )->stringify;
+        $template_path = FixMyStreet->path_to( "templates", "email", "default", $template )->stringify
+            unless -e $template_path;
+        $template = File::Slurp::read_file( $template_path );
 
         if ($h{category} eq _('Other')) {
             $h{category_footer} = _('this type of local problem');
@@ -210,19 +195,16 @@ foreach my $row (@$unsent) {
     }
 
     unless ($send_email || $send_web) {
-        die 'Report not going anywhere for ID ' . $row->{id} . '!';
+        die 'Report not going anywhere for ID ' . $row->id . '!';
     }
 
-    my $testing_email = mySociety::Config::get('TESTING_EMAIL');
-    if ($row->{email} eq $testing_email) {
-        @recips = ( $testing_email );
-        $send_web = 0;
-        $send_email = 1;
-    } elsif (mySociety::Config::get('STAGING_SITE')) {
+    if (mySociety::Config::get('STAGING_SITE')) {
         # on a staging server send emails to ourselves rather than the councils
         @recips = ( mySociety::Config::get('CONTACT_EMAIL') );
+        $send_web = 0;
+        $send_email = 1;
     } elsif ($site eq 'emptyhomes') {
-        my $council = $row->{council};
+        my $council = $row->council;
         my $country = $areas_info->{$council}->{country};
         if ($country eq 'W') {
             push @recips, 'shelter@' . mySociety::Config::get('EMAIL_DOMAIN');
@@ -232,7 +214,7 @@ foreach my $row (@$unsent) {
     }
 
     # Special case for this parish council
-    # if ($address && $address =~ /Sprowston/ && $row->{council} == 2233 && $row->{category} eq 'Street lighting') {
+    # if ($address && $address =~ /Sprowston/ && $row->council == 2233 && $row->category eq 'Street lighting') {
     #     $h{councils_name} = 'Sprowston Parish Council';
     #     my $e = 'parishclerk' . '@' . 'sprowston-pc.gov.uk';
     #     @to = ( [ $e, $h{councils_name} ] );
@@ -243,19 +225,17 @@ foreach my $row (@$unsent) {
     my $result = -1;
 
     if ($send_email) {
-        $template = _($template);
-        my $email = mySociety::Locale::in_gb_locale { mySociety::Email::construct_email({
-            _template_ => $template,
-            _parameters_ => \%h,
-            To => \@to,
-            From => [ $row->{email}, $row->{name} ],
-            'Message-ID' => sprintf('<report-%s-%s@mysociety.org>', time(), unpack('h*', random_bytes(5, 1))),
-        }) };
-        if (!$nomail) {
-            $result *= mySociety::EmailUtil::send_email($email, mySociety::Config::get('CONTACT_EMAIL'), @recips);
-        } else {
-            print $email;
-        }
+        $result *= FixMyStreet::App->send_email_cron(
+            {
+                _template_ => $template,
+                _parameters_ => \%h,
+                To => \@to,
+                From => [ $row->user->email, $row->name ],
+            },
+            mySociety::Config::get('CONTACT_EMAIL'),
+            \@recips,
+            $nomail
+        );
     }
 
     if ($send_web eq 'easthants') {
@@ -266,16 +246,15 @@ foreach my $row (@$unsent) {
     } elsif ($send_web eq 'london') {
         $h{message} = construct_london_message(%h);
         if (!$nomail) {
-            $result *= post_london_report(%h);
+            $result *= post_london_report( $row, %h );
         }
     }
 
     if ($result == mySociety::EmailUtil::EMAIL_SUCCESS) {
-        dbh()->do('UPDATE problem SET whensent=ms_current_timestamp(),
-            lastupdate=ms_current_timestamp() WHERE id=?', {}, $row->{id});
-        dbh()->commit();
-    } else {
-        dbh()->rollback();
+        $row->update( {
+            whensent => \'ms_current_timestamp()',
+            lastupdate => \'ms_current_timestamp()',
+        } );
     }
 }
 
@@ -386,10 +365,10 @@ EOF
 }
 
 sub post_london_report {
-    my %h = @_;
+    my ( $problem, %h ) = @_;
     my $phone = $h{phone};
     my $mobile = '';
-    if ($phone =~ /^\s*07/) {
+    if ($phone && $phone =~ /^\s*07/) {
         $mobile = $phone;
         $phone = '';
     }
@@ -438,41 +417,14 @@ sub post_london_report {
     my ($team) = $out =~ /<team>(.*?)<\/team>/;
 
     $org = london_lookup($org);
-    dbh()->do("update problem set external_id=?, external_body=?, external_team=? where id=?", {},
-        $id, $org, $team, $h{id});
+    $problem->external_id( $id );
+    $problem->external_body( $org );
+    $problem->external_team( $team );
     return 0;
 }
 
 # Nearest things
 
-sub find_closest {
-    my ($row, $latitude, $longitude) = @_;
-    my $str = '';
-
-    return '' unless $row->{used_map};
-
-    # Get nearest road-type thing from Bing
-    my $url = "http://dev.virtualearth.net/REST/v1/Locations/$latitude,$longitude?c=en-GB&key=" . mySociety::Config::get('BING_MAPS_API_KEY');
-    my $j = LWP::Simple::get($url);
-    if ($j) {
-        $j = JSON->new->utf8->allow_nonref->decode($j);
-        if ($j->{resourceSets}[0]{resources}[0]{name}) {
-            $str .= "Nearest road to the pin placed on the map (automatically generated by Bing Maps): $j->{resourceSets}[0]{resources}[0]{name}\n\n";
-        }
-    }
-
-    # Get nearest postcode from Matthew's random gazetteer (put in MaPit? Or elsewhere?)
-    $url = "http://gazetteer.dracos.vm.bytemark.co.uk/point/$latitude,$longitude.json";
-    $j = LWP::Simple::get($url);
-    if ($j) {
-        $j = JSON->new->utf8->allow_nonref->decode($j);
-        if ($j->{postcode}) {
-            $str .= "Nearest postcode to the pin placed on the map (automatically generated): $j->{postcode}[0] ($j->{postcode}[1]m away)\n\n";
-        }
-    }
-    return $str;
-}
-
 sub london_lookup {
     my $org = shift;
     my $str = "Unknown ($org)";
diff --git a/bin/update-all-reports b/bin/update-all-reports
new file mode 100755
index 000000000..2263a3d9d
--- /dev/null
+++ b/bin/update-all-reports
@@ -0,0 +1,69 @@
+#!/usr/bin/perl
+
+# update-all-reports:
+# Generate the data for the /reports page
+#
+# Copyright (c) 2011 UK Citizens Online Democracy. All rights reserved.
+# Email: matthew@mysociety.org. WWW: http://www.mysociety.org
+
+use strict;
+use warnings;
+require 5.8.0;
+
+use FixMyStreet::App;
+use File::Path ();
+use File::Slurp;
+use JSON;
+use List::MoreUtils qw(zip);
+
+my $fourweeks = 4*7*24*60*60;
+
+my $problems = FixMyStreet::App->model("DB::Problem")->search(
+    {
+        state => [ 'confirmed', 'fixed' ]
+    },
+    {
+        columns => [
+            'id', 'council', 'state', 'areas',
+            { duration => { extract => "epoch from current_timestamp-lastupdate" } },
+            { age      => { extract => "epoch from current_timestamp-confirmed"  } },
+        ]
+    }
+);
+$problems = $problems->cursor; # Raw DB cursor for speed
+
+my ( %fixed, %open );
+my @cols = ( 'id', 'council', 'state', 'areas', 'duration', 'age' );
+while ( my @problem = $problems->next ) {
+    my %problem = zip @cols, @problem;
+    my @areas;
+    if ( !$problem{council} ) {
+        # Problem was not sent to any council, add to all areas
+        @areas = grep { $_ } split( /,/, $problem{areas} );
+        $problem{councils} = 0;
+    } else {
+        # Add to councils it was sent to
+        (my $council = $problem{council}) =~ s/\|.*$//;
+        @areas = split( /,/, $council );
+        $problem{councils} = scalar @areas;
+    }
+    foreach my $council ( @areas ) {
+        my $duration_str = ( $problem{duration} > 2 * $fourweeks ) ? 'old' : 'new';
+        my $type = ( $problem{duration} > 2 * $fourweeks )
+            ? 'unknown'
+            : ($problem{age} > $fourweeks ? 'older' : 'new');
+        # Fixed problems are either old or new
+        $fixed{$council}{$duration_str}++ if $problem{state} eq 'fixed';
+        # Open problems are either unknown, older, or new
+        $open{$council}{$type}++ if $problem{state} eq 'confirmed';
+    }
+}
+
+my $body = JSON->new->utf8(1)->encode( {
+    fixed => \%fixed,
+    open  => \%open,
+} );
+
+File::Path::mkpath( FixMyStreet->path_to( '../data/' )->stringify );
+File::Slurp::write_file( FixMyStreet->path_to( '../data/all-reports.json' )->stringify, \$body );
+