Questionnaire back end templating etc.

9 files changed, 177 insertions, 201 deletions

diff --git a/bin/send-questionnaires b/bin/send-questionnaires
index d3945bf12..0a42afe8b 100755
--- a/bin/send-questionnaires
+++ b/bin/send-questionnaires
@@ -3,115 +3,132 @@
 # 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 FixMyStreet::App;
+
 use Page;
-use mySociety::AuthToken;
 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 $result;
-    if ($nomail) {
-        $result = -1;
+send_q($site, '4 weeks');
+send_q($site, '26 weeks') if $site eq 'emptyhomes';
+
+# ---
+
+sub send_q {
+    my ($site, $period) = @_;
+
+    # Select all problems that need a questionnaire email sending
+    my $params = {
+        state => [ 'confirmed', 'fixed' ],
+        whensent => [
+            '-and',
+            { '!=', undef },
+            { '<', \"ms_current_timestamp() - '$period'::interval" },
+        ],
+        send_questionnaire => 1,
+    };
+    # FIXME Do these a bit better...
+    if ($site eq 'emptyhomes' && $period eq '4 weeks') {
+        $params->{'(select max(whensent) from questionnaire where me.id=problem_id)'} = undef;
+    } elsif ($site eq 'emptyhomes' && $period eq '26 weeks') {
+        $params->{'(select max(whensent) from questionnaire where me.id=problem_id)'} = { '!=', undef };
     } else {
-        $result = mySociety::EmailUtil::send_email($email, $sender, $row->{email});
+        $params->{'-or'} = [
+            '(select max(whensent) from questionnaire where me.id=problem_id)' => undef,
+            '(select max(whenanswered) from questionnaire where me.id=problem_id)' => { '<', \"ms_current_timestamp() - '$period'::interval" }
+        ];
     }
-    if ($result == mySociety::EmailUtil::EMAIL_SUCCESS) {
-        print "  ...success\n" if $verbose;
-        dbh()->commit();
-    } else {
-        print " ...failed\n" if $verbose;
-        dbh()->rollback();
+
+    my $unsent = FixMyStreet::App->model('DB::Problem')->search( $params, {
+        order_by => { -desc => 'created' }
+    } );
+
+    while (my $row = $unsent->next) {
+
+        my $cobrand = FixMyStreet::Cobrand->get_class_for_moniker($row->cobrand)->new();
+        $cobrand->set_lang_and_domain($row->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;
+
+        my $template;
+        if ($site eq 'emptyhomes') {
+            ($template = $period) =~ s/ //;
+            $template = File::Slurp::read_file("$FindBin::Bin/../templates/email/emptyhomes/" . $row->lang . "/questionnaire-$template.txt");
+        } else {
+            $template = File::Slurp::read_file("$FindBin::Bin/../templates/email/" . $cobrand->moniker . "/questionnaire.txt");
+        }
+
+        my %h = map { $_ => $row->$_ } qw/name title detail category/;
+        $h{created} = Page::prettify_duration( time() - $row->created->epoch, 'week' );
+
+        my $questionnaire = FixMyStreet::App->model('DB::Questionnaire')->create( {
+            problem_id => $row->id,
+            whensent => \'ms_current_timestamp()',
+        } );
+
+        # We won't send another questionnaire unless they ask for it (or it was
+        # the first EHA questionnaire.
+        $row->send_questionnaire( 0 )
+            if $site ne 'emptyhomes' || $period eq '26 weeks';
+
+        my $token = FixMyStreet::App->model("DB::Token")->new_result( {
+            scope => 'questionnaire',
+            data  => $questionnaire->id,
+        } );
+        $h{url} = $cobrand->base_url_for_emails($row->cobrand_data) . '/Q/' . $token->token;
+
+        my $sender = $cobrand->contact_email;
+        my $sender_name = _($cobrand->contact_name);
+        $sender =~ s/team/fms-DO-NOT-REPLY/;
+        my $email = mySociety::Locale::in_gb_locale { mySociety::Email::construct_email({
+            _template_ => $template,
+            _parameters_ => \%h,
+            To => [ [ $row->user->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 " . $questionnaire->id . ", problem "
+            . $row->id . ", token " . $token->token . " to "
+            . $row->user->email . "\n"
+            if $verbose;
+
+        my $result;
+        if ($nomail) {
+            $result = -1;
+        } else {
+            $result = mySociety::EmailUtil::send_email($email, $sender, $row->user->email);
+        }
+        if ($result == mySociety::EmailUtil::EMAIL_SUCCESS) {
+            print "  ...success\n" if $verbose;
+            $row->update();
+            $token->insert();
+        } else {
+            print " ...failed\n" if $verbose;
+            $questionnaire->delete;
+        }
     }
 }
 
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/conf/crontab.ugly b/conf/crontab.ugly
index 5e65e1cee..48a46da9c 100644
--- a/conf/crontab.ugly
+++ b/conf/crontab.ugly
@@ -16,7 +16,7 @@ MAILTO=cron-!!(*= $site *)!!@mysociety.org
 0 0-11,13-23                      * * * !!(*= $user *)!! run-with-lockfile -n /data/vhost/!!(*= $vhost *)!!/send-reports.lock /data/vhost/!!(*= $vhost *)!!/fixmystreet/bin/send-reports || echo "stalled?"
 0 12                              * * * !!(*= $user *)!! run-with-lockfile -n /data/vhost/!!(*= $vhost *)!!/send-reports.lock "/data/vhost/!!(*= $vhost *)!!/fixmystreet/bin/send-reports --verbose" || echo "stalled?"
 2 * * * * !!(*= $user *)!! run-with-lockfile -n /data/vhost/!!(*= $vhost *)!!/send-alerts.lock /data/vhost/!!(*= $vhost *)!!/fixmystreet/bin/send-alerts || echo "stalled?"
-0,30 * * * * !!(*= $user *)!! run-with-lockfile -n /data/vhost/!!(*= $vhost *)!!/send-questionnaires.lock /data/vhost/!!(*= $vhost *)!!/fixmystreet/bin/send-questionnaires-eha || echo "stalled?"
+0,30 * * * * !!(*= $user *)!! run-with-lockfile -n /data/vhost/!!(*= $vhost *)!!/send-questionnaires.lock /data/vhost/!!(*= $vhost *)!!/fixmystreet/bin/send-questionnaires || echo "stalled?"
 
 !!(* } elsif ($vhost eq 'cities.fixmystreet.com') { *)!!
 2,7,12,17,22,27,32,37,42,47,52,57 * * * * !!(*= $user *)!! run-with-lockfile -n /data/vhost/!!(*= $vhost *)!!/send-reports.lock /data/vhost/!!(*= $vhost *)!!/fixmystreet/bin/send-reports || echo "stalled?"
diff --git a/templates/emails/questionnaire b/templates/email/default/questionnaire.txt
index 4be8eeaa4..4be8eeaa4 100644
--- a/templates/emails/questionnaire
+++ b/templates/email/default/questionnaire.txt
diff --git a/templates/email/emptyhomes/cy/questionnaire-26weeks.txt b/templates/email/emptyhomes/cy/questionnaire-26weeks.txt
new file mode 100644
index 000000000..b2b40ac80
--- /dev/null
+++ b/templates/email/emptyhomes/cy/questionnaire-26weeks.txt
@@ -0,0 +1,25 @@
+Subject: Holiadur ar eich adroddiad am eiddo gwag
+
+Helo <?=$values['name']?>,
+
+Chwe mis yn ôl, fe wnaethoch adrodd am eiddo gwag ar ReportEmptyHomes.com gan roi'r
+manylion sydd wedi'u cynnwys ar ddiwedd y neges e-bost hon. Er mwyn cadw'n safle'n gyfredol
+ac yn berthnasol, byddwn yn ddiolchgar petaech yn llenwi'r holiadur byr hwn er mwyn
+dweud wrthom ni beth sydd wedi digwydd: 
+
+    <?=$values['url']?>
+
+Peidiwch ag ateb y neges hon; mae blwch sylwadau cyhoeddus
+ar yr holiadur.
+
+Yn gywir,  
+reportemptyhomes.com
+
+Roedd eich adroddiad fel a ganlyn:
+
+<?=$values['title']?>
+
+Math o eiddo: <?=$values['category']?>
+
+<?=$values['detail']?>
+
diff --git a/templates/email/emptyhomes/cy/questionnaire-4weeks.txt b/templates/email/emptyhomes/cy/questionnaire-4weeks.txt
new file mode 100644
index 000000000..c4c9d80aa
--- /dev/null
+++ b/templates/email/emptyhomes/cy/questionnaire-4weeks.txt
@@ -0,0 +1,25 @@
+Subject: Holiadur ar eich adroddiad am eiddo gwag
+
+Helo <?=$values['name']?>,
+
+Bedair wythnos yn ôl, fe wnaethoch adrodd am eiddo gwag ar ReportEmptyHomes.com gan roi'r
+manylion sydd wedi'u cynnwys ar ddiwedd y neges e-bost hon. Er mwyn cadw'n safle'n gyfredol
+ac yn berthnasol, byddwn yn ddiolchgar petaech yn gallu llenwi'r holiadur byr hwn 
+i ddweud wrthom beth sydd wedi digwydd:
+
+    <?=$values['url']?>
+
+Peidiwch ag ateb y neges e-bost hon; mae blwch sylwadau cyhoeddus
+ar yr holiadur.
+
+Yn gywir,  
+reportemptyhomes.com
+
+Roedd eich adroddiad fel a ganlyn:
+
+<?=$values['title']?>
+
+Math o eiddo: <?=$values['category']?>
+
+<?=$values['detail']?>
+
diff --git a/templates/emails/questionnaire-eha-26weeks b/templates/email/emptyhomes/en-gb/questionnaire-26weeks.txt
index 758157ec0..758157ec0 100644
--- a/templates/emails/questionnaire-eha-26weeks
+++ b/templates/email/emptyhomes/en-gb/questionnaire-26weeks.txt
diff --git a/templates/emails/questionnaire-eha-4weeks b/templates/email/emptyhomes/en-gb/questionnaire-4weeks.txt
index 436da73d6..436da73d6 100644
--- a/templates/emails/questionnaire-eha-4weeks
+++ b/templates/email/emptyhomes/en-gb/questionnaire-4weeks.txt
diff --git a/templates/email/fiksgatami/questionnaire.txt b/templates/email/fiksgatami/questionnaire.txt
new file mode 100644
index 000000000..fd16c42b1
--- /dev/null
+++ b/templates/email/fiksgatami/questionnaire.txt
@@ -0,0 +1,23 @@
+Subject: Skjema for din sak på FiksGataMi
+
+Hei <?=$values['name']?>,
+
+for <?=$values['created']?> siden, la du til en sak på FiksGataMi
+med detaljene som vist i denne eposten. For å holde vår nettside oppdatert
+og relevant, vil vi sette pris på om du kunne fylle ut følgende skjema for å
+oppdatere saken:
+
+    <?=$values['url']?>
+
+Vennligst ikke svar på denne eposten; det er muligheter for å kommentere i skjema.
+
+Vennlig hilsen,  
+FiksGataMi-gruppen
+
+Saken du la til var som følger:
+
+<?=$values['title']?>
+
+<?=$values['detail']?>
+
+