1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
|
#!/usr/bin/perl -w
# send-questionnaires:
# Send out creator questionnaires
#
# Copyright (c) 2007 UK Citizens Online Democracy. All rights reserved.
# Email: matthew@mysociety.org. WWW: http://www.mysociety.org
#
# $Id: send-questionnaires,v 1.8 2008-10-07 16:44:09 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/../../perllib";
use File::Slurp;
use Page;
use mySociety::AuthToken;
use mySociety::Config;
use mySociety::DBHandle qw(dbh select_all);
use mySociety::Email;
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)
);
}
die "Either no arguments, --nomail or --verbose" if (@ARGV>1);
my $nomail = 0;
my $verbose = 0;
$nomail = 1 if (@ARGV==1 && $ARGV[0] eq '--nomail');
$verbose = 1 if (@ARGV==1 && $ARGV[0] eq '--verbose');
$verbose = 1 if $nomail;
# Select all problems that need a questionnaire email sending
my $unsent = select_all(
"select id, council, category, title, detail, name, email,
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 ($councils, $missing) = $row->{council} =~ /^([\d,]+)(?:\|([\d,]+))?/;
my @councils = split /,/, $councils;
my $areas_info = mySociety::MaPit::get_voting_areas_info(\@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} = mySociety::Config::get('BASE_URL') . '/Q/' . $token;
my $sender = mySociety::Config::get('CONTACT_EMAIL');
$sender =~ s/team/fms-DO-NOT-REPLY/;
my $email = mySociety::Email::construct_email({
_template_ => $template,
_parameters_ => \%h,
To => [ [ $row->{email}, $row->{name} ] ],
From => [ $sender, 'FixMyStreet' ],
'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;
} 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();
}
}
|
