diff options
Diffstat (limited to 'perllib/FixMyStreet/Script')
| -rw-r--r-- | perllib/FixMyStreet/Script/Alerts.pm | 15 | ||||
| -rw-r--r-- | perllib/FixMyStreet/Script/Questionnaires.pm | 3 | ||||
| -rw-r--r-- | perllib/FixMyStreet/Script/Reports.pm | 2 | ||||
| -rwxr-xr-x | perllib/FixMyStreet/Script/UpdateAllReports.pm | 274 |
4 files changed, 284 insertions, 10 deletions
diff --git a/perllib/FixMyStreet/Script/Alerts.pm b/perllib/FixMyStreet/Script/Alerts.pm index 1a760a0c1..c001cc311 100644 --- a/perllib/FixMyStreet/Script/Alerts.pm +++ b/perllib/FixMyStreet/Script/Alerts.pm @@ -6,7 +6,6 @@ use warnings; use DateTime::Format::Pg; use IO::String; -use mySociety::DBHandle qw(dbh); use FixMyStreet::Gaze; use mySociety::Locale; use mySociety::MaPit; @@ -18,8 +17,6 @@ use FixMyStreet::Email; use FixMyStreet::Map; use FixMyStreet::App::Model::PhotoSet; -FixMyStreet->configure_mysociety_dbhandle; - my $parser = DateTime::Format::Pg->new(); # Child must have confirmed, id, email, state(!) columns @@ -65,7 +62,7 @@ sub send() { $query =~ s/\?/alert.parameter/ if ($query =~ /\?/); $query =~ s/\?/alert.parameter2/ if ($query =~ /\?/); - $query = dbh()->prepare($query); + $query = FixMyStreet::DB->schema->storage->dbh->prepare($query); $query->execute(); my $last_alert_id; my %data = ( template => $alert_type->template, data => [], schema => $schema ); @@ -105,7 +102,7 @@ sub send() { my $url = $cobrand->base_url_for_report($row); # this is currently only for new_updates - if ($row->{item_text}) { + if (defined($row->{item_text})) { if ( $cobrand->moniker ne 'zurich' && $row->{alert_user_id} == $row->{user_id} ) { # This is an alert to the same user who made the report - make this a login link # Don't bother with Zurich which has no accounts @@ -143,7 +140,7 @@ sub send() { # this is ward and council problems } else { if ( exists $row->{geocode} && $row->{geocode} && $ref =~ /ward|council/ ) { - my $nearest_st = _get_address_from_gecode( $row->{geocode} ); + my $nearest_st = _get_address_from_geocode( $row->{geocode} ); $row->{nearest} = $nearest_st; } @@ -228,7 +225,7 @@ sub send() { 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"; - $q = dbh()->prepare($q); + $q = FixMyStreet::DB->schema->storage->dbh->prepare($q); $q->execute($latitude, $longitude, $d, $alert->whensubscribed, $alert->id, $alert->user->email); while (my $row = $q->fetchrow_hashref) { $schema->resultset('AlertSent')->create( { @@ -236,7 +233,7 @@ sub send() { parameter => $row->{id}, } ); if ( exists $row->{geocode} && $row->{geocode} ) { - my $nearest_st = _get_address_from_gecode( $row->{geocode} ); + my $nearest_st = _get_address_from_geocode( $row->{geocode} ); $row->{nearest} = $nearest_st; } my $dt = $parser->parse_timestamp( $row->{confirmed} ); @@ -304,7 +301,7 @@ sub _send_aggregated_alert_email(%) { } } -sub _get_address_from_gecode { +sub _get_address_from_geocode { my $geocode = shift; return '' unless defined $geocode; diff --git a/perllib/FixMyStreet/Script/Questionnaires.pm b/perllib/FixMyStreet/Script/Questionnaires.pm index 3f22eb150..ec6139d2d 100644 --- a/perllib/FixMyStreet/Script/Questionnaires.pm +++ b/perllib/FixMyStreet/Script/Questionnaires.pm @@ -16,6 +16,9 @@ sub send { sub send_questionnaires_period { my ( $period, $params ) = @_; + # Don't send if we don't have a fixed state + return unless FixMyStreet::DB::Result::Problem::fixed_states->{fixed}; + my $rs = FixMyStreet::DB->resultset('Questionnaire'); # Select all problems that need a questionnaire email sending diff --git a/perllib/FixMyStreet/Script/Reports.pm b/perllib/FixMyStreet/Script/Reports.pm index 6057807de..1e5fd55bb 100644 --- a/perllib/FixMyStreet/Script/Reports.pm +++ b/perllib/FixMyStreet/Script/Reports.pm @@ -98,7 +98,7 @@ sub send(;$) { $h{osm_url} = Utils::OpenStreetMap::short_url($h{latitude}, $h{longitude}); if ( $row->used_map ) { - $h{closest_address} = $cobrand->find_closest( $h{latitude}, $h{longitude}, $row ); + $h{closest_address} = $cobrand->find_closest($row); $h{osm_url} .= '?m'; } diff --git a/perllib/FixMyStreet/Script/UpdateAllReports.pm b/perllib/FixMyStreet/Script/UpdateAllReports.pm new file mode 100755 index 000000000..1bd069ee8 --- /dev/null +++ b/perllib/FixMyStreet/Script/UpdateAllReports.pm @@ -0,0 +1,274 @@ +package FixMyStreet::Script::UpdateAllReports; + +use strict; +use warnings; + +use FixMyStreet; +use FixMyStreet::DB; + +use File::Path (); +use File::Slurp; +use JSON::MaybeXS; +use List::MoreUtils qw(zip); +use List::Util qw(sum); + +my $fourweeks = 4*7*24*60*60; + +# Age problems from when they're confirmed, except on Zurich +# where they appear as soon as they're created. +my $age_column = 'confirmed'; +if ( FixMyStreet->config('BASE_URL') =~ /zurich|zueri/ ) { + $age_column = 'created'; +} + +sub generate { + my $include_areas = shift; + + my $problems = FixMyStreet::DB->resultset('Problem')->search( + { + state => [ FixMyStreet::DB::Result::Problem->visible_states() ], + bodies_str => \'is not null', + }, + { + columns => [ + 'id', 'bodies_str', 'state', 'areas', 'cobrand', + { duration => { extract => "epoch from current_timestamp-lastupdate" } }, + { age => { extract => "epoch from current_timestamp-$age_column" } }, + ] + } + ); + $problems = $problems->cursor; # Raw DB cursor for speed + + my ( %fixed, %open ); + my @cols = ( 'id', 'bodies_str', 'state', 'areas', 'cobrand', 'duration', 'age' ); + while ( my @problem = $problems->next ) { + my %problem = zip @cols, @problem; + my @bodies; + my @areas; + my $cobrand = $problem{cobrand}; + my $duration_str = ( $problem{duration} > 2 * $fourweeks ) ? 'old' : 'new'; + my $type = ( $problem{duration} > 2 * $fourweeks ) + ? 'unknown' + : ($problem{age} > $fourweeks ? 'older' : 'new'); + my $problem_fixed = + FixMyStreet::DB::Result::Problem->fixed_states()->{$problem{state}} + || FixMyStreet::DB::Result::Problem->closed_states()->{$problem{state}}; + + # Add to bodies it was sent to + @bodies = split( /,/, $problem{bodies_str} ); + + foreach my $body ( @bodies ) { + if ( $problem_fixed ) { + # Fixed problems are either old or new + $fixed{$body}{$duration_str}++; + $fixed{$cobrand}{$body}{$duration_str}++; + } else { + # Open problems are either unknown, older, or new + $open{$body}{$type}++; + $open{$cobrand}{$body}{$type}++; + } + } + + if ( $include_areas ) { + @areas = grep { $_ } split( /,/, $problem{areas} ); + foreach my $area ( @areas ) { + if ( $problem_fixed ) { + $fixed{areas}{$area}{$duration_str}++; + } else { + $open{areas}{$area}{$type}++; + } + } + } + } + + my $body = encode_json( { + 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 ); +} + +sub end_period { + my $period = shift; + FixMyStreet->set_time_zone(DateTime->now)->truncate(to => $period)->add($period . 's' => 1)->subtract(seconds => 1); +} + +sub loop_period { + my ($date, $period, $extra) = @_; + my $end = end_period($period); + my @out; + while ($date <= $end) { + push @out, { n => $date->$period, $extra ? (d => $date->$extra) : () }; + $date->add($period . 's' => 1); + } + return @out; +} + +sub generate_dashboard { + my %data; + + my $end_today = end_period('day'); + my $min_confirmed = FixMyStreet::DB->resultset('Problem')->search({ + state => [ FixMyStreet::DB::Result::Problem->visible_states() ], + }, { + select => [ { min => 'confirmed' } ], + as => [ 'confirmed' ], + })->first->confirmed; + if ($min_confirmed) { + $min_confirmed = $min_confirmed->truncate(to => 'day'); + } else { + $min_confirmed = FixMyStreet->set_time_zone(DateTime->now)->truncate(to => 'day'); + } + + my ($group_by, $extra); + if (DateTime::Duration->compare($end_today - $min_confirmed, DateTime::Duration->new(months => 1)) < 0) { + $group_by = 'day'; + } elsif (DateTime::Duration->compare($end_today - $min_confirmed, DateTime::Duration->new(years => 1)) < 0) { + $group_by = 'month'; + $extra = 'month_abbr'; + } else { + $group_by = 'year'; + } + my @problem_periods = loop_period($min_confirmed, $group_by, $extra); + + my %problems_reported_by_period = stuff_by_day_or_year( + $group_by, 'Problem', + state => [ FixMyStreet::DB::Result::Problem->visible_states() ], + ); + my %problems_fixed_by_period = stuff_by_day_or_year( + $group_by, 'Problem', + state => [ FixMyStreet::DB::Result::Problem->fixed_states() ], + ); + + my (@problems_reported_by_period, @problems_fixed_by_period); + foreach (map { $_->{n} } @problem_periods) { + push @problems_reported_by_period, ($problems_reported_by_period[-1]||0) + ($problems_reported_by_period{$_}||0); + push @problems_fixed_by_period, ($problems_fixed_by_period[-1]||0) + ($problems_fixed_by_period{$_}||0); + } + $data{problem_periods} = [ map { $_->{d} || $_->{n} } @problem_periods ]; + $data{problems_reported_by_period} = \@problems_reported_by_period; + $data{problems_fixed_by_period} = \@problems_fixed_by_period; + + my %last_seven_days = ( + problems => [], + updated => [], + fixed => [], + ); + $data{last_seven_days} = \%last_seven_days; + + my $dtf = FixMyStreet::DB->schema->storage->datetime_parser; + my $eight_ago = $dtf->format_datetime(DateTime->now->subtract(days => 8)); + %problems_reported_by_period = stuff_by_day_or_year('day', + 'Problem', + state => [ FixMyStreet::DB::Result::Problem->visible_states() ], + confirmed => { '>=', $eight_ago }, + ); + %problems_fixed_by_period = stuff_by_day_or_year('day', + 'Comment', + confirmed => { '>=', $eight_ago }, + -or => [ + problem_state => [ FixMyStreet::DB::Result::Problem->fixed_states() ], + mark_fixed => 1, + ], + ); + my %problems_updated_by_period = stuff_by_day_or_year('day', + 'Comment', + confirmed => { '>=', $eight_ago }, + ); + + my $date = DateTime->today->subtract(days => 7); + while ($date < DateTime->today) { + push @{$last_seven_days{problems}}, $problems_reported_by_period{$date->day} || 0; + push @{$last_seven_days{fixed}}, $problems_fixed_by_period{$date->day} || 0; + push @{$last_seven_days{updated}}, $problems_updated_by_period{$date->day} || 0; + $date->add(days => 1); + } + $last_seven_days{problems_total} = sum @{$last_seven_days{problems}}; + $last_seven_days{fixed_total} = sum @{$last_seven_days{fixed}}; + $last_seven_days{updated_total} = sum @{$last_seven_days{updated}}; + + my(@top_five_bodies); + $data{top_five_bodies} = \@top_five_bodies; + + my $bodies = FixMyStreet::DB->resultset('Body')->search; + my $substmt = "select min(id) from comment where me.problem_id=comment.problem_id and (problem_state in ('fixed', 'fixed - council', 'fixed - user') or mark_fixed)"; + while (my $body = $bodies->next) { + my $subquery = FixMyStreet::DB->resultset('Comment')->to_body($body)->search({ + -or => [ + problem_state => [ FixMyStreet::DB::Result::Problem->fixed_states() ], + mark_fixed => 1, + ], + 'me.id' => \"= ($substmt)", + 'me.state' => 'confirmed', + }, { + select => [ + { extract => "epoch from me.confirmed-problem.confirmed", -as => 'time' }, + ], + as => [ qw/time/ ], + rows => 100, + order_by => { -desc => 'me.confirmed' }, + join => 'problem' + })->as_subselect_rs; + my $avg = $subquery->search({ + }, { + select => [ { avg => "time" } ], + as => [ qw/avg/ ], + })->first->get_column('avg'); + push @top_five_bodies, { name => $body->name, days => int($avg / 60 / 60 / 24 + 0.5) } + if defined $avg; + } + @top_five_bodies = sort { $a->{days} <=> $b->{days} } @top_five_bodies; + $data{average} = @top_five_bodies + ? int((sum map { $_->{days} } @top_five_bodies) / @top_five_bodies + 0.5) : undef; + + @top_five_bodies = @top_five_bodies[0..4] if @top_five_bodies > 5; + + my $week_ago = $dtf->format_datetime(DateTime->now->subtract(days => 7)); + my $last_seven_days = FixMyStreet::DB->resultset("Problem")->search({ + confirmed => { '>=', $week_ago }, + })->count; + my @top_five_categories = FixMyStreet::DB->resultset("Problem")->search({ + confirmed => { '>=', $week_ago }, + category => { '!=', 'Other' }, + }, { + select => [ 'category', { count => 'id' } ], + as => [ 'category', 'count' ], + group_by => 'category', + rows => 5, + order_by => { -desc => 'count' }, + }); + $data{top_five_categories} = [ map { + { category => $_->category, count => $_->get_column('count') } + } @top_five_categories ]; + foreach (@top_five_categories) { + $last_seven_days -= $_->get_column('count'); + } + $data{other_categories} = $last_seven_days; + + my $body = encode_json( \%data ); + File::Path::mkpath( FixMyStreet->path_to( '../data/' )->stringify ); + File::Slurp::write_file( FixMyStreet->path_to( '../data/all-reports-dashboard.json' )->stringify, \$body ); +} + +sub stuff_by_day_or_year { + my $period = shift; + my $table = shift; + my %params = @_; + my $results = FixMyStreet::DB->resultset($table)->search({ + %params + }, { + select => [ { extract => \"$period from confirmed", -as => $period }, { count => 'id' } ], + as => [ $period, 'count' ], + group_by => [ $period ], + }); + my %out; + while (my $row = $results->next) { + my $p = $row->get_column($period); + $out{$p} = $row->get_column('count'); + } + return %out; +} + +1; |