diff options
Diffstat (limited to 'perllib/FixMyStreet/DB/ResultSet')
| -rw-r--r-- | perllib/FixMyStreet/DB/ResultSet/Alert.pm | 4 | ||||
| -rw-r--r-- | perllib/FixMyStreet/DB/ResultSet/AlertType.pm | 32 | ||||
| -rw-r--r-- | perllib/FixMyStreet/DB/ResultSet/Comment.pm | 34 | ||||
| -rw-r--r-- | perllib/FixMyStreet/DB/ResultSet/Contact.pm | 4 | ||||
| -rw-r--r-- | perllib/FixMyStreet/DB/ResultSet/Nearby.pm | 18 | ||||
| -rw-r--r-- | perllib/FixMyStreet/DB/ResultSet/Problem.pm | 57 | ||||
| -rw-r--r-- | perllib/FixMyStreet/DB/ResultSet/Questionnaire.pm | 46 |
7 files changed, 99 insertions, 96 deletions
diff --git a/perllib/FixMyStreet/DB/ResultSet/Alert.pm b/perllib/FixMyStreet/DB/ResultSet/Alert.pm index 5848265f1..bb1c61141 100644 --- a/perllib/FixMyStreet/DB/ResultSet/Alert.pm +++ b/perllib/FixMyStreet/DB/ResultSet/Alert.pm @@ -14,7 +14,7 @@ sub timeline_created { return $rs->search( { - whensubscribed => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + whensubscribed => { '>=', \"current_timestamp-'7 days'::interval" }, confirmed => 1, %{ $restriction }, }, @@ -29,7 +29,7 @@ sub timeline_disabled { return $rs->search( { - whendisabled => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + whendisabled => { '>=', \"current_timestamp-'7 days'::interval" }, %{ $restriction }, }, ); diff --git a/perllib/FixMyStreet/DB/ResultSet/AlertType.pm b/perllib/FixMyStreet/DB/ResultSet/AlertType.pm index 0b430008a..25c727e25 100644 --- a/perllib/FixMyStreet/DB/ResultSet/AlertType.pm +++ b/perllib/FixMyStreet/DB/ResultSet/AlertType.pm @@ -30,18 +30,19 @@ sub email_alerts ($) { $item_table.name as item_name, $item_table.anonymous as item_anonymous, $item_table.confirmed as item_confirmed, $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 + from alert, $item_table, $head_table + where alert.parameter::integer = $head_table.id + and $item_table.${head_table}_id = $head_table.id "; } else { $query .= " $item_table.*, $item_table.id as item_id - from alert, $item_table"; + from alert, $item_table + where 1 = 1"; } $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 alert_type='$ref' and whendisabled is null and $item_table.confirmed >= whensubscribed + and $item_table.confirmed >= 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 . " @@ -69,8 +70,6 @@ sub email_alerts ($) { # this is for the new_updates alerts next if $row->{non_public} and $row->{user_id} != $row->{alert_user_id}; - my $hashref_restriction = $cobrand->site_restriction( $row->{cobrand_data} ); - FixMyStreet::App->model('DB::AlertSent')->create( { alert_id => $row->{alert_id}, parameter => $row->{item_id}, @@ -89,10 +88,7 @@ sub email_alerts ($) { $data{state_message} = _("This report is currently marked as open."); } - my $url = $cobrand->base_url( $row->{alert_cobrand_data} ); - if ( $hashref_restriction && $hashref_restriction->{bodies_str} && $row->{bodies_str} ne $hashref_restriction->{bodies_str} ) { - $url = mySociety::Config::get('BASE_URL'); - } + my $url = $cobrand->base_url_for_report($row); # this is currently only for new_updates if ($row->{item_text}) { if ( $cobrand->moniker ne 'zurich' && $row->{alert_user_id} == $row->{user_id} ) { @@ -171,7 +167,6 @@ sub email_alerts ($) { my $longitude = $alert->parameter; my $latitude = $alert->parameter2; - my $hashref_restriction = $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 { @@ -184,7 +179,7 @@ sub email_alerts ($) { and problem.user_id = users.id and problem.state in ($states) and problem.non_public = 'f' - and problem.confirmed >= ? and problem.confirmed >= ms_current_timestamp() - '7 days'::interval + and problem.confirmed >= ? and problem.confirmed >= 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 <> ? order by confirmed desc"; @@ -195,10 +190,7 @@ sub email_alerts ($) { alert_id => $alert->id, parameter => $row->{id}, } ); - my $url = $cobrand->base_url( $alert->cobrand_data ); - if ( $hashref_restriction && $hashref_restriction->{bodies_str} && $row->{bodies_str} ne $hashref_restriction->{bodies_str} ) { - $url = mySociety::Config::get('BASE_URL'); - } + my $url = $cobrand->base_url_for_report($row); $data{data} .= $url . "/report/" . $row->{id} . " - $row->{title}\n\n"; if ( exists $row->{geocode} && $row->{geocode} ) { my $nearest_st = _get_address_from_gecode( $row->{geocode} ); @@ -241,15 +233,13 @@ sub _send_aggregated_alert_email(%) { my $template = FixMyStreet->get_email_template($cobrand->moniker, $data{lang}, "$data{template}.txt"); - my $sender = FixMyStreet->config('DO_NOT_REPLY_EMAIL'); my $result = FixMyStreet::App->send_email_cron( { _template_ => $template, _parameters_ => \%data, - From => [ $sender, _($cobrand->contact_name) ], To => $data{alert_email}, }, - $sender, + undef, 0, $cobrand, $data{lang} diff --git a/perllib/FixMyStreet/DB/ResultSet/Comment.pm b/perllib/FixMyStreet/DB/ResultSet/Comment.pm index 70f8027aa..1b6afb819 100644 --- a/perllib/FixMyStreet/DB/ResultSet/Comment.pm +++ b/perllib/FixMyStreet/DB/ResultSet/Comment.pm @@ -4,19 +4,24 @@ use base 'DBIx::Class::ResultSet'; use strict; use warnings; +sub to_body { + my ($rs, $body_restriction) = @_; + return FixMyStreet::DB::ResultSet::Problem::to_body($rs, $body_restriction); +} + + sub timeline { - my ( $rs, $restriction ) = @_; + my ( $rs, $body_restriction ) = @_; my $prefetch = FixMyStreet::App->model('DB')->schema->storage->sql_maker->quote_char ? [ qw/user/ ] : []; - return $rs->search( + return $rs->to_body($body_restriction)->search( { state => 'confirmed', - created => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, - %{ $restriction }, + created => { '>=', \"current_timestamp-'7 days'::interval" }, }, { prefetch => $prefetch, @@ -25,17 +30,18 @@ sub timeline { } sub summary_count { - my ( $rs, $restriction ) = @_; + my ( $rs, $body_restriction ) = @_; - return $rs->search( - $restriction, - { - group_by => ['me.state'], - select => [ 'me.state', { count => 'me.id' } ], - as => [qw/state state_count/], - join => 'problem' - } - ); + my $params = { + group_by => ['me.state'], + select => [ 'me.state', { count => 'me.id' } ], + as => [qw/state state_count/], + }; + if ($body_restriction) { + $rs = $rs->to_body($body_restriction); + $params->{join} = 'problem'; + } + return $rs->search(undef, $params); } 1; diff --git a/perllib/FixMyStreet/DB/ResultSet/Contact.pm b/perllib/FixMyStreet/DB/ResultSet/Contact.pm index 6fa6a03a0..f402b5461 100644 --- a/perllib/FixMyStreet/DB/ResultSet/Contact.pm +++ b/perllib/FixMyStreet/DB/ResultSet/Contact.pm @@ -4,6 +4,8 @@ use base 'DBIx::Class::ResultSet'; use strict; use warnings; +sub me { join('.', shift->current_source_alias, shift || q{}) } + =head2 not_deleted $rs = $rs->not_deleted(); @@ -14,7 +16,7 @@ Filter down to not deleted contacts - which have C<deleted> set to false; sub not_deleted { my $rs = shift; - return $rs->search( { deleted => 0 } ); + return $rs->search( { $rs->me('deleted') => 0 } ); } sub summary_count { diff --git a/perllib/FixMyStreet/DB/ResultSet/Nearby.pm b/perllib/FixMyStreet/DB/ResultSet/Nearby.pm index a0ccb8a6d..a6b00ef7b 100644 --- a/perllib/FixMyStreet/DB/ResultSet/Nearby.pm +++ b/perllib/FixMyStreet/DB/ResultSet/Nearby.pm @@ -19,12 +19,10 @@ sub nearby { if $interval; $params->{id} = { -not_in => $ids } if $ids; - $params = { - %{ $c->cobrand->problems_clause }, - %$params - } if $c->cobrand->problems_clause; $params->{category} = $category if $category; + $rs = FixMyStreet::DB::ResultSet::Problem::to_body($rs, $c->cobrand->body_restriction); + my $attrs = { prefetch => 'problem', bind => [ $mid_lat, $mid_lon, $dist ], @@ -36,16 +34,4 @@ sub nearby { 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 index 7885c28b3..e9f5d0f8e 100644 --- a/perllib/FixMyStreet/DB/ResultSet/Problem.pm +++ b/perllib/FixMyStreet/DB/ResultSet/Problem.pm @@ -20,6 +20,18 @@ sub set_restriction { $site_key = $key; } +sub to_body { + my ($rs, $bodies) = @_; + return $rs unless $bodies; + unless (ref $bodies eq 'ARRAY') { + $bodies = [ map { ref $_ ? $_->id : $_ } $bodies ]; + } + $rs = $rs->search( + \[ "regexp_split_to_array(bodies_str, ',') && ?", [ {} => $bodies ] ] + ); + return $rs; +} + # Front page statistics sub recent_fixed { @@ -168,9 +180,9 @@ sub timeline { 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" }, + created => { '>=', \"current_timestamp-'7 days'::interval" }, + confirmed => { '>=', \"current_timestamp-'7 days'::interval" }, + whensent => { '>=', \"current_timestamp-'7 days'::interval" }, } }, { @@ -198,7 +210,10 @@ sub unique_users { return $rs->search( { state => [ FixMyStreet::DB::Result::Problem->visible_states() ], }, { - select => [ { count => { distinct => 'user_id' } } ], + select => [ { distinct => 'user_id' } ], + as => [ 'user_id' ] + } )->as_subselect_rs->search( undef, { + select => [ { count => 'user_id' } ], as => [ 'count' ] } )->first->get_column('count'); } @@ -235,8 +250,8 @@ sub send_reports { my $site = $site_override || CronFns::site($base_url); my $states = [ 'confirmed', 'fixed' ]; - $states = [ 'unconfirmed', 'confirmed', 'in progress', 'planned', 'closed' ] if $site eq 'zurich'; - my $unsent = FixMyStreet::App->model("DB::Problem")->search( { + $states = [ 'unconfirmed', 'confirmed', 'in progress', 'planned', 'closed', 'investigating' ] if $site eq 'zurich'; + my $unsent = $rs->search( { state => $states, whensent => undef, bodies_str => { '!=', undef }, @@ -320,15 +335,19 @@ sub send_reports { $cobrand->process_additional_metadata_for_email($row, \%h); } - # XXX Needs locks! - # XXX Only copes with at most one missing body - my ($bodies, $missing) = $row->bodies_str =~ /^([\d,]+)(?:\|(\d+))?/; - my @bodies = split(/,/, $bodies); - $bodies = FixMyStreet::App->model("DB::Body")->search( - { id => \@bodies }, + my $bodies = FixMyStreet::App->model("DB::Body")->search( + { id => $row->bodies_str_ids }, { order_by => 'name' }, ); - $missing = FixMyStreet::App->model("DB::Body")->find($missing) if $missing; + + my $missing; + if ($row->bodies_missing) { + my @missing = FixMyStreet::App->model("DB::Body")->search( + { id => [ split /,/, $row->bodies_missing ] }, + { order_by => 'name' } + )->get_column('name')->all; + $missing = join(' / ', @missing) if @missing; + } my @dear; my %reporters = (); @@ -337,7 +356,7 @@ sub send_reports { my $sender = "FixMyStreet::SendReport::" . $sender_info->{method}; if ( ! exists $senders->{ $sender } ) { - warn "No such sender [ $sender ] for body $body->name ( $body->id )"; + warn sprintf "No such sender [ $sender ] for body %s ( %d )", $body->name, $body->id; next; } $reporters{ $sender } ||= $sender->new(); @@ -400,7 +419,7 @@ sub send_reports { $h{missing} = ''; if ($missing) { $h{missing} = '[ ' - . sprintf(_('We realise this problem might be the responsibility of %s; however, we don\'t currently have any contact details for them. If you know of an appropriate contact address, please do get in touch.'), $missing->name) + . sprintf(_('We realise this problem might be the responsibility of %s; however, we don\'t currently have any contact details for them. If you know of an appropriate contact address, please do get in touch.'), $missing) . " ]\n\n"; } @@ -433,8 +452,8 @@ sub send_reports { unless ($result) { $row->update( { - whensent => \'ms_current_timestamp()', - lastupdate => \'ms_current_timestamp()', + whensent => \'current_timestamp', + lastupdate => \'current_timestamp', } ); if ( $cobrand->report_sent_confirmation_email && !$h{anonymous_report}) { _send_report_sent_email( $row, \%h, $nomail, $cobrand ); @@ -468,7 +487,7 @@ sub send_reports { } } my $sending_errors = ''; - my $unsent = FixMyStreet::App->model("DB::Problem")->search( { + my $unsent = $rs->search( { state => [ 'confirmed', 'fixed' ], whensent => undef, bodies_str => { '!=', undef }, @@ -499,7 +518,7 @@ sub _send_report_sent_email { _template_ => $template, _parameters_ => $h, To => $row->user->email, - From => mySociety::Config::get('CONTACT_EMAIL'), + From => [ mySociety::Config::get('CONTACT_EMAIL'), $cobrand->contact_name ], }, mySociety::Config::get('CONTACT_EMAIL'), $nomail, diff --git a/perllib/FixMyStreet/DB/ResultSet/Questionnaire.pm b/perllib/FixMyStreet/DB/ResultSet/Questionnaire.pm index 63a91697d..bf1c68c49 100644 --- a/perllib/FixMyStreet/DB/ResultSet/Questionnaire.pm +++ b/perllib/FixMyStreet/DB/ResultSet/Questionnaire.pm @@ -22,7 +22,7 @@ sub send_questionnaires_period { whensent => [ '-and', { '!=', undef }, - { '<', \"ms_current_timestamp() - '$period'::interval" }, + { '<', \"current_timestamp - '$period'::interval" }, ], send_questionnaire => 1, }; @@ -34,7 +34,7 @@ sub send_questionnaires_period { } 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" } + '(select max(whenanswered) from questionnaire where me.id=problem_id)' => { '<', \"current_timestamp - '$period'::interval" } ]; } @@ -70,7 +70,7 @@ sub send_questionnaires_period { my $questionnaire = FixMyStreet::App->model('DB::Questionnaire')->create( { problem_id => $row->id, - whensent => \'ms_current_timestamp()', + whensent => \'current_timestamp', } ); # We won't send another questionnaire unless they ask for it (or it was @@ -84,9 +84,6 @@ sub send_questionnaires_period { } ); $h{url} = $cobrand->base_url($row->cobrand_data) . '/Q/' . $token->token; - my $sender = FixMyStreet->config('DO_NOT_REPLY_EMAIL'); - my $sender_name = _($cobrand->contact_name); - print "Sending questionnaire " . $questionnaire->id . ", problem " . $row->id . ", token " . $token->token . " to " . $row->user->email . "\n" @@ -97,9 +94,8 @@ sub send_questionnaires_period { _template_ => $template, _parameters_ => \%h, To => [ [ $row->user->email, $row->name ] ], - From => [ $sender, $sender_name ], }, - $sender, + undef, $params->{nomail}, $cobrand ); @@ -117,32 +113,36 @@ sub send_questionnaires_period { sub timeline { my ( $rs, $restriction ) = @_; + my $attrs; + if (%$restriction) { + $attrs = { + -select => [qw/me.*/], + prefetch => [qw/problem/], + } + } return $rs->search( { -or => { - whenanswered => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, - 'me.whensent' => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + whenanswered => { '>=', \"current_timestamp-'7 days'::interval" }, + 'me.whensent' => { '>=', \"current_timestamp-'7 days'::interval" }, }, %{ $restriction }, }, - { - -select => [qw/me.*/], - prefetch => [qw/problem/], - } + $attrs ); } 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' - } - ); + my $params = { + group_by => [ \'whenanswered is not null' ], + select => [ \'(whenanswered is not null)', { count => 'me.id' } ], + as => [qw/answered questionnaire_count/], + }; + if (%$restriction) { + $params->{join} = 'problem'; + } + return $rs->search($restriction, $params); } 1; |