package FixMyStreet::DB::ResultSet::Problem; use base 'DBIx::Class::ResultSet'; use strict; use warnings; use CronFns; use Utils; use mySociety::Config; use mySociety::MaPit; use FixMyStreet::App; use FixMyStreet::SendReport; my $site_key; sub set_restriction { my ( $rs, $key ) = @_; $site_key = $key; } # 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 => [ FixMyStreet::DB::Result::Problem->fixed_states() ], 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 => [ FixMyStreet::DB::Result::Problem->visible_states() ], confirmed => { '>', \"current_timestamp-'$interval'::interval" }, } )->count; Memcached::set($key, $result, 3600); } return $result; } # Front page recent lists sub recent { my ( $rs ) = @_; return _recent( $rs, 5 ); } sub recent_photos { my ( $rs, $num, $lat, $lon, $dist ) = @_; return _recent( $rs, $num, $lat, $lon, $dist, 1); } sub _recent { my ( $rs, $num, $lat, $lon, $dist, $photos ) = @_; my $key = $photos ? 'recent_photos' : 'recent'; $key .= ":$site_key:$num"; # unconfirmed might be returned for e.g. Zurich, but would mean in moderation, so no photo my @states = grep { $_ ne 'unconfirmed' } FixMyStreet::DB::Result::Problem->visible_states(); my $query = { non_public => 0, state => \@states, }; $query->{photo} = { '!=', undef } if $photos; my $attrs = { order_by => { -desc => 'coalesce(confirmed, created)' }, rows => $num, }; my $probs; my $new = 0; if (defined $lat) { my $dist2 = $dist; # Create a copy of the variable to stop it being stringified into a locale in the next line! $key .= ":$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; } ]; $new = 1; } } else { $probs = Memcached::get($key); unless ($probs) { $probs = [ $rs->search( $query, $attrs )->all ]; $new = 1; } } if ( $new ) { Memcached::set($key, $probs, 3600); } else { # Need to reattach schema so that confirmed column gets reinflated. $probs->[0]->result_source->schema( $rs->result_source->schema ) if $probs->[0]; } 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' }, }; $attr->{rows} = $limit if $limit; my $q = { non_public => 0, state => [ FixMyStreet::DB::Result::Problem->visible_states() ], 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 => [ FixMyStreet::DB::Result::Problem->visible_states() ], }, { select => [ { count => { distinct => 'user_id' } } ], as => [ 'count' ] } )->first->get_column('count'); } sub categories_summary { my ( $rs ) = @_; my $fixed_case = "case when state IN ( '" . join( "', '", FixMyStreet::DB::Result::Problem->fixed_states() ) . "' ) then 1 else null end"; my $categories = $rs->search( { 
<html>
  <head>
    <title>Ping? Pong!</title>
  </head>
  <body>
    <link rel="stylesheet" href="/ping.css">
    <p id="playground">
      <svg id="lines" width="1280" height="736" style="position: absolute; top: 0; left: 0; z-index: 1">
      </svg>
      <img src="tg14-salkart.png" alt="" id="map" />
    </p>
    <script>
      // These are used by ping.js, below.
      var switches_url = "/switches.json";
      var ping_url = "/nettkart.json";
      var draw_linknets = false;
    </script>
    <script type="text/javascript" src="ping.js"></script>
  </body>
</html>
generated by cgit v1.2.3 (git 2.25.1) at 2025-10-01 10:38:51 +0000
} if (! $sender_count) { debug_print("can't send because sender count is zero", $row->id) if $debug_mode; next; } if (mySociety::Config::get('STAGING_SITE') && !mySociety::Config::get('SEND_REPORTS_ON_STAGING')) { # on a staging server send emails to ourselves rather than the bodies %reporters = map { $_ => $reporters{$_} } grep { /FixMyStreet::SendReport::(Email|NI|EmptyHomes)/ } keys %reporters; unless (%reporters) { %reporters = ( 'FixMyStreet::SendReport::Email' => FixMyStreet::SendReport::Email->new() ); } } # Multiply results together, so one success counts as a success. my $result = -1; for my $sender ( keys %reporters ) { debug_print("sending using " . $sender, $row->id) if $debug_mode; $result *= $reporters{ $sender }->send( $row, \%h ); if ( $reporters{ $sender }->unconfirmed_counts) { foreach my $e (keys %{ $reporters{ $sender }->unconfirmed_counts } ) { foreach my $c (keys %{ $reporters{ $sender }->unconfirmed_counts->{$e} }) { $notgot{$e}{$c} += $reporters{ $sender }->unconfirmed_counts->{$e}{$c}; } } %note = ( %note, %{ $reporters{ $sender }->unconfirmed_notes } ); } } unless ($result) { $row->update( { whensent => \'ms_current_timestamp()', lastupdate => \'ms_current_timestamp()', } ); if ( $cobrand->report_sent_confirmation_email && !$h{anonymous_report}) { _send_report_sent_email( $row, \%h, $nomail, $cobrand ); } debug_print("send successful: OK", $row->id) if $debug_mode; } else { my @errors; for my $sender ( keys %reporters ) { unless ( $reporters{ $sender }->success ) { push @errors, $reporters{ $sender }->error; } } $row->update_send_failed( join( '|', @errors ) ); debug_print("send FAILED: " . join( '|', @errors ), $row->id) if $debug_mode; } } if ($debug_mode) { print "\n"; if ($debug_unsent_count) { debug_print("processed all unsent reports (total: $debug_unsent_count)"); } else { debug_print("no unsent reports were found (must have whensent=null and suitable bodies_str & state) -- nothing to send"); } } if ($verbose || $debug_mode) { print "Council email addresses that need checking:\n" if keys %notgot; foreach my $e (keys %notgot) { foreach my $c (keys %{$notgot{$e}}) { print " " . $notgot{$e}{$c} . " problem, to $e category $c (" . $note{$e}{$c}. ")\n"; } } my $sending_errors = ''; my $unsent = FixMyStreet::App->model("DB::Problem")->search( { state => [ 'confirmed', 'fixed' ], whensent => undef, bodies_str => { '!=', undef }, send_fail_count => { '>', 0 } } ); while (my $row = $unsent->next) { my $base_url = mySociety::Config::get('BASE_URL'); $sending_errors .= "* " . $base_url . "/report/" . $row->id . ", failed " . $row->send_fail_count . " times, last at " . $row->send_fail_timestamp . ", reason " . $row->send_fail_reason . "\n"; } if ($sending_errors) { print "The following reports had problems sending:\n$sending_errors"; } } } sub _send_report_sent_email { my $row = shift; my $h = shift; my $nomail = shift; my $cobrand = shift; my $template = FixMyStreet->get_email_template($row->cobrand, $row->lang, 'confirm_report_sent.txt'); FixMyStreet::App->send_email_cron( { _template_ => $template, _parameters_ => $h, To => $row->user->email, From => mySociety::Config::get('CONTACT_EMAIL'), }, mySociety::Config::get('CONTACT_EMAIL'), $nomail, $cobrand ); } sub debug_print { my $msg = shift; my $id = shift || ''; $id = "report $id: " if $id; print "[] $id$msg\n"; } 1;