diff options
Diffstat (limited to 'perllib/FixMyStreet/DB/ResultSet')
| -rw-r--r-- | perllib/FixMyStreet/DB/ResultSet/Alert.pm | 50 | ||||
| -rw-r--r-- | perllib/FixMyStreet/DB/ResultSet/AlertType.pm | 210 | ||||
| -rw-r--r-- | perllib/FixMyStreet/DB/ResultSet/Comment.pm | 41 | ||||
| -rw-r--r-- | perllib/FixMyStreet/DB/ResultSet/Contact.pm | 33 | ||||
| -rw-r--r-- | perllib/FixMyStreet/DB/ResultSet/Nearby.pm | 50 | ||||
| -rw-r--r-- | perllib/FixMyStreet/DB/ResultSet/Problem.pm | 203 | ||||
| -rw-r--r-- | perllib/FixMyStreet/DB/ResultSet/Questionnaire.pm | 149 | ||||
| -rw-r--r-- | perllib/FixMyStreet/DB/ResultSet/User.pm | 8 |
8 files changed, 744 insertions, 0 deletions
diff --git a/perllib/FixMyStreet/DB/ResultSet/Alert.pm b/perllib/FixMyStreet/DB/ResultSet/Alert.pm new file mode 100644 index 000000000..5848265f1 --- /dev/null +++ b/perllib/FixMyStreet/DB/ResultSet/Alert.pm @@ -0,0 +1,50 @@ +package FixMyStreet::DB::ResultSet::Alert; +use base 'DBIx::Class::ResultSet'; + +use strict; +use warnings; + +sub timeline_created { + my ( $rs, $restriction ) = @_; + + my $prefetch = + FixMyStreet::App->model('DB')->schema->storage->sql_maker->quote_char ? + [ qw/alert_type user/ ] : + [ qw/alert_type/ ]; + + return $rs->search( + { + whensubscribed => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + confirmed => 1, + %{ $restriction }, + }, + { + prefetch => $prefetch, + } + ); +} + +sub timeline_disabled { + my ( $rs, $restriction ) = @_; + + return $rs->search( + { + whendisabled => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + %{ $restriction }, + }, + ); +} + +sub summary_count { + my ( $rs, $restriction ) = @_; + + return $rs->search( + $restriction, + { + group_by => ['confirmed'], + select => [ 'confirmed', { count => 'id' } ], + as => [qw/confirmed confirmed_count/] + } + ); +} +1; diff --git a/perllib/FixMyStreet/DB/ResultSet/AlertType.pm b/perllib/FixMyStreet/DB/ResultSet/AlertType.pm new file mode 100644 index 000000000..46009cb85 --- /dev/null +++ b/perllib/FixMyStreet/DB/ResultSet/AlertType.pm @@ -0,0 +1,210 @@ +package FixMyStreet::DB::ResultSet::AlertType; +use base 'DBIx::Class::ResultSet'; + +use strict; +use warnings; + +use File::Slurp; + +use mySociety::DBHandle qw(dbh); +use mySociety::EmailUtil; +use mySociety::Gaze; +use mySociety::Locale; +use mySociety::MaPit; + +# Child must have confirmed, id, email, state(!) columns +# If parent/child, child table must also have name and text +# and foreign key to parent must be PARENT_id +sub email_alerts ($) { + my ( $rs ) = @_; + + my $q = $rs->search( { ref => { -not_like => '%local_problems%' } } ); + while (my $alert_type = $q->next) { + my $ref = $alert_type->ref; + my $head_table = $alert_type->head_table; + my $item_table = $alert_type->item_table; + my $query = 'select alert.id as alert_id, alert.user_id as alert_user_id, alert.lang as alert_lang, alert.cobrand as alert_cobrand, + alert.cobrand_data as alert_cobrand_data, alert.parameter as alert_parameter, alert.parameter2 as alert_parameter2, '; + if ($head_table) { + $query .= " + $item_table.id as item_id, $item_table.text as item_text, + $item_table.name as item_name, $item_table.anonymous as item_anonymous, + $head_table.* + from alert + inner join $item_table on alert.parameter::integer = $item_table.${head_table}_id + inner join $head_table on alert.parameter::integer = $head_table.id + "; + } else { + $query .= " $item_table.*, + $item_table.id as item_id + from alert, $item_table"; + } + $query .= " + where alert_type='$ref' and whendisabled is null and $item_table.confirmed >= whensubscribed + and $item_table.confirmed >= ms_current_timestamp() - '7 days'::interval + and (select whenqueued from alert_sent where alert_sent.alert_id = alert.id and alert_sent.parameter::integer = $item_table.id) is null + and $item_table.user_id <> alert.user_id + and " . $alert_type->item_where . " + and alert.confirmed = 1 + order by alert.id, $item_table.confirmed"; + # XXX Ugh - needs work + $query =~ s/\?/alert.parameter/ if ($query =~ /\?/); + $query =~ s/\?/alert.parameter2/ if ($query =~ /\?/); + $query = dbh()->prepare($query); + $query->execute(); + my $last_alert_id; + my %data = ( template => $alert_type->template, data => '' ); + while (my $row = $query->fetchrow_hashref) { + + my $cobrand = FixMyStreet::Cobrand->get_class_for_moniker($row->{alert_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; + + FixMyStreet::App->model('DB::AlertSent')->create( { + alert_id => $row->{alert_id}, + parameter => $row->{item_id}, + } ); + if ($last_alert_id && $last_alert_id != $row->{alert_id}) { + _send_aggregated_alert_email(%data); + %data = ( template => $alert_type->template, data => '' ); + } + + # create problem status message for the templates + $data{state_message} = + $row->{state} eq 'fixed' + ? _("This report is currently marked as fixed.") + : _("This report is currently marked as open."); + + my $url = $cobrand->base_url_for_emails( $row->{alert_cobrand_data} ); + if ($row->{item_text}) { + $data{problem_url} = $url . "/report/" . $row->{id}; + $data{data} .= $row->{item_name} . ' : ' if $row->{item_name} && !$row->{item_anonymous}; + $data{data} .= $row->{item_text} . "\n\n------\n\n"; + } else { + $data{data} .= $url . "/report/" . $row->{id} . " - $row->{title}\n\n"; + } + if (!$data{alert_user_id}) { + %data = (%data, %$row); + if ($ref eq 'area_problems' || $ref eq 'council_problems' || $ref eq 'ward_problems') { + my $va_info = mySociety::MaPit::call('area', $row->{alert_parameter}); + $data{area_name} = $va_info->{name}; + } + if ($ref eq 'ward_problems') { + my $va_info = mySociety::MaPit::call('area', $row->{alert_parameter2}); + $data{ward_name} = $va_info->{name}; + } + } + $data{cobrand} = $row->{alert_cobrand}; + $data{cobrand_data} = $row->{alert_cobrand_data}; + $data{lang} = $row->{alert_lang}; + $last_alert_id = $row->{alert_id}; + } + if ($last_alert_id) { + _send_aggregated_alert_email(%data); + } + } + + # Nearby done separately as the table contains the parameters + my $template = $rs->find( { ref => 'local_problems' } )->template; + my $query = FixMyStreet::App->model('DB::Alert')->search( { + alert_type => 'local_problems', + whendisabled => undef, + confirmed => 1 + }, { + order_by => 'id' + } ); + while (my $alert = $query->next) { + my $cobrand = FixMyStreet::Cobrand->get_class_for_moniker($alert->cobrand)->new(); + next unless $cobrand->email_host; + + my $longitude = $alert->parameter; + my $latitude = $alert->parameter2; + my $url = $cobrand->base_url_for_emails( $alert->cobrand_data ); + my ($site_restriction, $site_id) = $cobrand->site_restriction( $alert->cobrand_data ); + my $d = mySociety::Gaze::get_radius_containing_population($latitude, $longitude, 200000); + # Convert integer to GB locale string (with a ".") + $d = mySociety::Locale::in_gb_locale { + sprintf("%f", int($d*10+0.5)/10); + }; + my %data = ( template => $template, data => '', alert_id => $alert->id, alert_email => $alert->user->email, lang => $alert->lang, cobrand => $alert->cobrand, cobrand_data => $alert->cobrand_data ); + my $q = "select problem.id, problem.title from problem_find_nearby(?, ?, ?) as nearby, problem, users + where nearby.problem_id = problem.id + and problem.user_id = users.id + and problem.state in ('confirmed', 'fixed') + and problem.confirmed >= ? and problem.confirmed >= ms_current_timestamp() - '7 days'::interval + and (select whenqueued from alert_sent where alert_sent.alert_id = ? and alert_sent.parameter::integer = problem.id) is null + and users.email <> ? + $site_restriction + order by confirmed desc"; + $q = dbh()->prepare($q); + $q->execute($latitude, $longitude, $d, $alert->whensubscribed, $alert->id, $alert->user->email); + while (my $row = $q->fetchrow_hashref) { + FixMyStreet::App->model('DB::AlertSent')->create( { + alert_id => $alert->id, + parameter => $row->{id}, + } ); + $data{data} .= $url . "/report/" . $row->{id} . " - $row->{title}\n\n"; + } + _send_aggregated_alert_email(%data) if $data{data}; + } +} + +sub _send_aggregated_alert_email(%) { + my %data = @_; + + my $cobrand = FixMyStreet::Cobrand->get_class_for_moniker($data{cobrand})->new(); + + $cobrand->set_lang_and_domain( $data{lang}, 1 ); + + if (!$data{alert_email}) { + my $user = FixMyStreet::App->model('DB::User')->find( { + id => $data{alert_user_id} + } ); + $data{alert_email} = $user->email; + } + + my $token = FixMyStreet::App->model("DB::Token")->new_result( { + scope => 'alert', + data => { + id => $data{alert_id}, + type => 'unsubscribe', + email => $data{alert_email}, + } + } ); + $data{unsubscribe_url} = $cobrand->base_url_for_emails( $data{cobrand_data} ) . '/A/' . $token->token; + + my $template = FixMyStreet->path_to( + "templates", "email", $cobrand->moniker, $data{lang}, "$data{template}.txt" + )->stringify; + $template = FixMyStreet->path_to( "templates", "email", $cobrand->moniker, "$data{template}.txt" )->stringify + unless -e $template; + $template = FixMyStreet->path_to( "templates", "email", "default", "$data{template}.txt" )->stringify + unless -e $template; + $template = File::Slurp::read_file($template); + + my $sender = $cobrand->contact_email; + (my $from = $sender) =~ s/team/fms-DO-NOT-REPLY/; # XXX + my $result = FixMyStreet::App->send_email_cron( + { + _template_ => $template, + _parameters_ => \%data, + From => [ $from, _($cobrand->contact_name) ], + To => $data{alert_email}, + }, + $sender, + [ $data{alert_email} ], + 0, + ); + + if ($result == mySociety::EmailUtil::EMAIL_SUCCESS) { + $token->insert(); + } else { + print "Failed to send alert $data{alert_id}!"; + } +} + +1; diff --git a/perllib/FixMyStreet/DB/ResultSet/Comment.pm b/perllib/FixMyStreet/DB/ResultSet/Comment.pm new file mode 100644 index 000000000..70f8027aa --- /dev/null +++ b/perllib/FixMyStreet/DB/ResultSet/Comment.pm @@ -0,0 +1,41 @@ +package FixMyStreet::DB::ResultSet::Comment; +use base 'DBIx::Class::ResultSet'; + +use strict; +use warnings; + +sub timeline { + my ( $rs, $restriction ) = @_; + + my $prefetch = + FixMyStreet::App->model('DB')->schema->storage->sql_maker->quote_char ? + [ qw/user/ ] : + []; + + return $rs->search( + { + state => 'confirmed', + created => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + %{ $restriction }, + }, + { + prefetch => $prefetch, + } + ); +} + +sub summary_count { + my ( $rs, $restriction ) = @_; + + return $rs->search( + $restriction, + { + group_by => ['me.state'], + select => [ 'me.state', { count => 'me.id' } ], + as => [qw/state state_count/], + join => 'problem' + } + ); +} + +1; diff --git a/perllib/FixMyStreet/DB/ResultSet/Contact.pm b/perllib/FixMyStreet/DB/ResultSet/Contact.pm new file mode 100644 index 000000000..6fa6a03a0 --- /dev/null +++ b/perllib/FixMyStreet/DB/ResultSet/Contact.pm @@ -0,0 +1,33 @@ +package FixMyStreet::DB::ResultSet::Contact; +use base 'DBIx::Class::ResultSet'; + +use strict; +use warnings; + +=head2 not_deleted + + $rs = $rs->not_deleted(); + +Filter down to not deleted contacts - which have C<deleted> set to false; + +=cut + +sub not_deleted { + my $rs = shift; + return $rs->search( { deleted => 0 } ); +} + +sub summary_count { + my ( $rs, $restriction ) = @_; + + return $rs->search( + $restriction, + { + group_by => ['confirmed'], + select => [ 'confirmed', { count => 'id' } ], + as => [qw/confirmed confirmed_count/] + } + ); +} + +1; diff --git a/perllib/FixMyStreet/DB/ResultSet/Nearby.pm b/perllib/FixMyStreet/DB/ResultSet/Nearby.pm new file mode 100644 index 000000000..3b3a3d90b --- /dev/null +++ b/perllib/FixMyStreet/DB/ResultSet/Nearby.pm @@ -0,0 +1,50 @@ +package FixMyStreet::DB::ResultSet::Nearby; +use base 'DBIx::Class::ResultSet'; + +use strict; +use warnings; + +sub nearby { + my ( $rs, $c, $dist, $ids, $limit, $mid_lat, $mid_lon, $interval ) = @_; + + my $params = { + state => [ 'confirmed', 'fixed' ], + }; + $params->{'current_timestamp-lastupdate'} = { '<', \"'$interval'::interval" } + if $interval; + $params->{id} = { -not_in => $ids } + if $ids; + $params = { + %{ $c->cobrand->problems_clause }, + %$params + } if $c->cobrand->problems_clause; + + my $attrs = { + join => 'problem', + columns => [ + 'problem.id', 'problem.title', 'problem.latitude', + 'problem.longitude', 'distance', 'problem.state', + 'problem.confirmed' + ], + bind => [ $mid_lat, $mid_lon, $dist ], + order_by => [ 'distance', { -desc => 'created' } ], + rows => $limit, + }; + + my @problems = mySociety::Locale::in_gb_locale { $rs->search( $params, $attrs )->all }; + return \@problems; +} + +# XXX Not currently used, so not migrating at present. +#sub fixed_nearby { +# my ($dist, $mid_lat, $mid_lon) = @_; +# mySociety::Locale::in_gb_locale { select_all( +# "select id, title, latitude, longitude, distance +# from problem_find_nearby(?, ?, $dist) as nearby, problem +# where nearby.problem_id = problem.id and state='fixed' +# site_restriction +# order by lastupdate desc", $mid_lat, $mid_lon); +# } +#} + +1; diff --git a/perllib/FixMyStreet/DB/ResultSet/Problem.pm b/perllib/FixMyStreet/DB/ResultSet/Problem.pm new file mode 100644 index 000000000..ca329ab59 --- /dev/null +++ b/perllib/FixMyStreet/DB/ResultSet/Problem.pm @@ -0,0 +1,203 @@ +package FixMyStreet::DB::ResultSet::Problem; +use base 'DBIx::Class::ResultSet'; + +use strict; +use warnings; + +my $site_restriction; +my $site_key; + +sub set_restriction { + my ( $rs, $sql, $key, $restriction ) = @_; + $site_key = $key; + $site_restriction = $restriction; +} + +# Front page statistics + +sub recent_fixed { + my $rs = shift; + my $key = "recent_fixed:$site_key"; + my $result = Memcached::get($key); + unless ($result) { + $result = $rs->search( { + state => 'fixed', + lastupdate => { '>', \"current_timestamp-'1 month'::interval" }, + } )->count; + Memcached::set($key, $result, 3600); + } + return $result; +} + +sub number_comments { + my $rs = shift; + my $key = "number_comments:$site_key"; + my $result = Memcached::get($key); + unless ($result) { + $result = $rs->search( + { 'comments.state' => 'confirmed' }, + { join => 'comments' } + )->count; + Memcached::set($key, $result, 3600); + } + return $result; +} + +sub recent_new { + my ( $rs, $interval ) = @_; + (my $key = $interval) =~ s/\s+//g; + $key = "recent_new:$site_key:$key"; + my $result = Memcached::get($key); + unless ($result) { + $result = $rs->search( { + state => [ 'confirmed', 'fixed' ], + confirmed => { '>', \"current_timestamp-'$interval'::interval" }, + } )->count; + Memcached::set($key, $result, 3600); + } + return $result; +} + +# Front page recent lists + +sub recent { + my ( $rs ) = @_; + my $key = "recent:$site_key"; + my $result = Memcached::get($key); + unless ($result) { + $result = [ $rs->search( { + state => [ 'confirmed', 'fixed' ] + }, { + columns => [ 'id', 'title' ], + order_by => { -desc => 'confirmed' }, + rows => 5, + } )->all ]; + Memcached::set($key, $result, 3600); + } + return $result; +} + +sub recent_photos { + my ( $rs, $num, $lat, $lon, $dist ) = @_; + my $probs; + my $query = { + state => [ 'confirmed', 'fixed' ], + photo => { '!=', undef }, + }; + my $attrs = { + columns => [ 'id', 'title' ], + order_by => { -desc => 'confirmed' }, + rows => $num, + }; + if (defined $lat) { + my $dist2 = $dist; # Create a copy of the variable to stop it being stringified into a locale in the next line! + my $key = "recent_photos:$site_key:$num:$lat:$lon:$dist2"; + $probs = Memcached::get($key); + unless ($probs) { + $attrs->{bind} = [ $lat, $lon, $dist ]; + $attrs->{join} = 'nearby'; + $probs = [ mySociety::Locale::in_gb_locale { + $rs->search( $query, $attrs )->all; + } ]; + Memcached::set($key, $probs, 3600); + } + } else { + my $key = "recent_photos:$site_key:$num"; + $probs = Memcached::get($key); + unless ($probs) { + $probs = [ $rs->search( $query, $attrs )->all ]; + Memcached::set($key, $probs, 3600); + } + } + return $probs; +} + +# Problems around a location + +sub around_map { + my ( $rs, $min_lat, $max_lat, $min_lon, $max_lon, $interval, $limit ) = @_; + my $attr = { + order_by => { -desc => 'created' }, + columns => [ + 'id', 'title' ,'latitude', 'longitude', 'state', 'confirmed' + ], + }; + $attr->{rows} = $limit if $limit; + + my $q = { + state => [ 'confirmed', 'fixed' ], + latitude => { '>=', $min_lat, '<', $max_lat }, + longitude => { '>=', $min_lon, '<', $max_lon }, + }; + $q->{'current_timestamp - lastupdate'} = { '<', \"'$interval'::interval" } + if $interval; + + my @problems = mySociety::Locale::in_gb_locale { $rs->search( $q, $attr )->all }; + return \@problems; +} + +# Admin functions + +sub timeline { + my ( $rs ) = @_; + + my $prefetch = + FixMyStreet::App->model('DB')->schema->storage->sql_maker->quote_char ? + [ qw/user/ ] : + []; + + return $rs->search( + { + -or => { + created => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + confirmed => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + whensent => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + } + }, + { + prefetch => $prefetch, + } + ); +} + +sub summary_count { + my ( $rs ) = @_; + + return $rs->search( + undef, + { + group_by => ['state'], + select => [ 'state', { count => 'id' } ], + as => [qw/state state_count/] + } + ); +} + +sub unique_users { + my ( $rs ) = @_; + + return $rs->search( { + state => [ 'confirmed', 'fixed' ], + }, { + select => [ { count => { distinct => 'user_id' } } ], + as => [ 'count' ] + } )->first->get_column('count'); +} + +sub categories_summary { + my ( $rs ) = @_; + + my $categories = $rs->search( { + state => [ 'confirmed', 'fixed' ], + whensent => { '<' => \"NOW() - INTERVAL '4 weeks'" }, + }, { + select => [ 'category', { count => 'id' }, { count => \"case when state='fixed' then 1 else null end" } ], + as => [ 'category', 'c', 'fixed' ], + group_by => [ 'category' ], + result_class => 'DBIx::Class::ResultClass::HashRefInflator' + } ); + my %categories = map { $_->{category} => { total => $_->{c}, fixed => $_->{fixed} } } $categories->all; + return \%categories; +} + +1; diff --git a/perllib/FixMyStreet/DB/ResultSet/Questionnaire.pm b/perllib/FixMyStreet/DB/ResultSet/Questionnaire.pm new file mode 100644 index 000000000..e490c77a6 --- /dev/null +++ b/perllib/FixMyStreet/DB/ResultSet/Questionnaire.pm @@ -0,0 +1,149 @@ +package FixMyStreet::DB::ResultSet::Questionnaire; +use base 'DBIx::Class::ResultSet'; + +use strict; +use warnings; +use File::Slurp; +use Utils; +use mySociety::EmailUtil; + +sub send_questionnaires { + my ( $rs, $params ) = @_; + $rs->send_questionnaires_period( '4 weeks', $params ); + $rs->send_questionnaires_period( '26 weeks', $params ) + if $params->{site} eq 'emptyhomes'; +} + +sub send_questionnaires_period { + my ( $rs, $period, $params ) = @_; + + # Select all problems that need a questionnaire email sending + my $q_params = { + state => [ 'confirmed', 'fixed' ], + whensent => [ + '-and', + { '!=', undef }, + { '<', \"ms_current_timestamp() - '$period'::interval" }, + ], + send_questionnaire => 1, + }; + # FIXME Do these a bit better... + if ($params->{site} eq 'emptyhomes' && $period eq '4 weeks') { + $q_params->{'(select max(whensent) from questionnaire where me.id=problem_id)'} = undef; + } elsif ($params->{site} eq 'emptyhomes' && $period eq '26 weeks') { + $q_params->{'(select max(whensent) from questionnaire where me.id=problem_id)'} = { '!=', undef }; + } else { + $q_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" } + ]; + } + + my $unsent = FixMyStreet::App->model('DB::Problem')->search( $q_params, { + order_by => { -desc => 'confirmed' } + } ); + + 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 ($params->{site} eq 'emptyhomes') { + ($template = $period) =~ s/ //; + $template = File::Slurp::read_file( FixMyStreet->path_to( "templates/email/emptyhomes/" . $row->lang . "/questionnaire-$template.txt" )->stringify ); + } else { + $template = FixMyStreet->path_to( "templates", "email", $cobrand->moniker, "questionnaire.txt" )->stringify; + $template = FixMyStreet->path_to( "templates", "email", "default", "questionnaire.txt" )->stringify + unless -e $template; + $template = File::Slurp::read_file( $template ); + } + + my %h = map { $_ => $row->$_ } qw/name title detail category/; + $h{created} = Utils::prettify_duration( time() - $row->confirmed->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 $params->{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/; + + print "Sending questionnaire " . $questionnaire->id . ", problem " + . $row->id . ", token " . $token->token . " to " + . $row->user->email . "\n" + if $params->{verbose}; + + my $result = FixMyStreet::App->send_email_cron( + { + _template_ => $template, + _parameters_ => \%h, + To => [ [ $row->user->email, $row->name ] ], + From => [ $sender, $sender_name ], + }, + $sender, + [ $row->user->email ], + $params->{nomail} + ); + if ($result == mySociety::EmailUtil::EMAIL_SUCCESS) { + print " ...success\n" if $params->{verbose}; + $row->update(); + $token->insert(); + } else { + print " ...failed\n" if $params->{verbose}; + $questionnaire->delete; + } + } +} + +sub timeline { + my ( $rs, $restriction ) = @_; + + return $rs->search( + { + -or => { + whenanswered => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + 'me.whensent' => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + }, + %{ $restriction }, + }, + { + -select => [qw/me.*/], + prefetch => [qw/problem/], + } + ); +} + +sub summary_count { + my ( $rs, $restriction ) = @_; + + return $rs->search( + $restriction, + { + group_by => [ \'whenanswered is not null' ], + select => [ \'(whenanswered is not null)', { count => 'me.id' } ], + as => [qw/answered questionnaire_count/], + join => 'problem' + } + ); +} +1; diff --git a/perllib/FixMyStreet/DB/ResultSet/User.pm b/perllib/FixMyStreet/DB/ResultSet/User.pm new file mode 100644 index 000000000..7e657a936 --- /dev/null +++ b/perllib/FixMyStreet/DB/ResultSet/User.pm @@ -0,0 +1,8 @@ +package FixMyStreet::DB::ResultSet::User; +use base 'DBIx::Class::ResultSet'; + +use strict; +use warnings; + + +1; |