From a82a51695eacf94b7523502b3a48e1de6836fec0 Mon Sep 17 00:00:00 2001 From: Struan Donald Date: Tue, 7 Jun 2011 16:46:05 +0100 Subject: admin timeline --- perllib/FixMyStreet/DB/ResultSet/Problem.pm | 30 +++++++++++++++++++++++++++++ 1 file changed, 30 insertions(+) create mode 100644 perllib/FixMyStreet/DB/ResultSet/Problem.pm (limited to 'perllib/FixMyStreet/DB/ResultSet/Problem.pm') diff --git a/perllib/FixMyStreet/DB/ResultSet/Problem.pm b/perllib/FixMyStreet/DB/ResultSet/Problem.pm new file mode 100644 index 000000000..8d798a7c1 --- /dev/null +++ b/perllib/FixMyStreet/DB/ResultSet/Problem.pm @@ -0,0 +1,30 @@ +package FixMyStreet::DB::ResultSet::Problem; +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( + { + -or => { + created => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + confirmed => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + whensent => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, + %{ $restriction }, + } + }, + { + prefetch => $prefetch, + } + ); +} + +1; -- cgit v1.2.3 From 5813d21b9ac8b358a557ab67722e8b54c769b663 Mon Sep 17 00:00:00 2001 From: Struan Donald Date: Tue, 7 Jun 2011 17:34:08 +0100 Subject: move summary count fetching out to result sets --- perllib/FixMyStreet/DB/ResultSet/Problem.pm | 13 +++++++++++++ 1 file changed, 13 insertions(+) (limited to 'perllib/FixMyStreet/DB/ResultSet/Problem.pm') diff --git a/perllib/FixMyStreet/DB/ResultSet/Problem.pm b/perllib/FixMyStreet/DB/ResultSet/Problem.pm index 8d798a7c1..835ab1b45 100644 --- a/perllib/FixMyStreet/DB/ResultSet/Problem.pm +++ b/perllib/FixMyStreet/DB/ResultSet/Problem.pm @@ -27,4 +27,17 @@ sub timeline { ); } +sub summary_count { + my ( $rs, $restriction ) = @_; + + return $rs->search( + $restriction, + { + group_by => ['state'], + select => [ 'state', { count => 'id' } ], + as => [qw/state state_count/] + } + ); +} + 1; -- cgit v1.2.3 From 2d0503ccfd4c55ea7252e19603fe4732c2f36f9e Mon Sep 17 00:00:00 2001 From: Matthew Somerville Date: Wed, 8 Jun 2011 12:39:40 +0100 Subject: Start moving stuff out of Problems.pm, do JSON as per its better-way comment. --- perllib/FixMyStreet/DB/ResultSet/Problem.pm | 14 ++++++++++++++ 1 file changed, 14 insertions(+) (limited to 'perllib/FixMyStreet/DB/ResultSet/Problem.pm') diff --git a/perllib/FixMyStreet/DB/ResultSet/Problem.pm b/perllib/FixMyStreet/DB/ResultSet/Problem.pm index 835ab1b45..b029caf3b 100644 --- a/perllib/FixMyStreet/DB/ResultSet/Problem.pm +++ b/perllib/FixMyStreet/DB/ResultSet/Problem.pm @@ -40,4 +40,18 @@ sub summary_count { ); } +my $site_restriction; +my $site_key; + +sub set_restriction { + my ( $rs, $sql, $key, $restriction ) = @_; + $site_key = $key; + $site_restriction = $restriction; +} + +sub site_restricted { + my ( $rs ) = @_; + return $rs->search( $site_restriction ); +} + 1; -- cgit v1.2.3 From e19d63897578e31cf40cc185a663cebd8edb9c14 Mon Sep 17 00:00:00 2001 From: Matthew Somerville Date: Wed, 8 Jun 2011 13:37:45 +0100 Subject: Move some more functions. --- perllib/FixMyStreet/DB/ResultSet/Problem.pm | 80 ++++++++++++++++++++++++----- 1 file changed, 66 insertions(+), 14 deletions(-) (limited to 'perllib/FixMyStreet/DB/ResultSet/Problem.pm') diff --git a/perllib/FixMyStreet/DB/ResultSet/Problem.pm b/perllib/FixMyStreet/DB/ResultSet/Problem.pm index b029caf3b..d73d58b82 100644 --- a/perllib/FixMyStreet/DB/ResultSet/Problem.pm +++ b/perllib/FixMyStreet/DB/ResultSet/Problem.pm @@ -4,6 +4,72 @@ 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; +} + +sub site_restricted { + my ( $rs ) = @_; + return $rs->search( $site_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->site_restricted->search( { + state => 'fixed', + lastupdate => { '>', \"current_timestamp-'1 month'::interval" }, + } )->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->site_restricted->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->site_restricted->search( { + state => [ 'confirmed', 'fixed' ] + }, { + columns => [ 'id', 'title' ], + order_by => { -desc => 'confirmed' }, + rows => 5, + } )->count; + Memcached::set($key, $result, 3600); + } + return $result; +} + +# Admin functions + sub timeline { my ( $rs, $restriction ) = @_; @@ -40,18 +106,4 @@ sub summary_count { ); } -my $site_restriction; -my $site_key; - -sub set_restriction { - my ( $rs, $sql, $key, $restriction ) = @_; - $site_key = $key; - $site_restriction = $restriction; -} - -sub site_restricted { - my ( $rs ) = @_; - return $rs->search( $site_restriction ); -} - 1; -- cgit v1.2.3 From 500190d03fcbfd8993f46b61bca9e12a7339dbc2 Mon Sep 17 00:00:00 2001 From: Matthew Somerville Date: Wed, 8 Jun 2011 13:51:17 +0100 Subject: Switch to using c->cobrand->problems to return all problems for a cobrand (so on Barnet only return Barnet problems). --- perllib/FixMyStreet/DB/ResultSet/Problem.pm | 11 +++-------- 1 file changed, 3 insertions(+), 8 deletions(-) (limited to 'perllib/FixMyStreet/DB/ResultSet/Problem.pm') diff --git a/perllib/FixMyStreet/DB/ResultSet/Problem.pm b/perllib/FixMyStreet/DB/ResultSet/Problem.pm index d73d58b82..af850ecd0 100644 --- a/perllib/FixMyStreet/DB/ResultSet/Problem.pm +++ b/perllib/FixMyStreet/DB/ResultSet/Problem.pm @@ -13,11 +13,6 @@ sub set_restriction { $site_restriction = $restriction; } -sub site_restricted { - my ( $rs ) = @_; - return $rs->search( $site_restriction ); -} - # Front page statistics sub recent_fixed { @@ -25,7 +20,7 @@ sub recent_fixed { my $key = "recent_fixed:$site_key"; my $result = Memcached::get($key); unless ($result) { - $result = $rs->site_restricted->search( { + $result = $rs->search( { state => 'fixed', lastupdate => { '>', \"current_timestamp-'1 month'::interval" }, } )->count; @@ -40,7 +35,7 @@ sub recent_new { $key = "recent_new:$site_key:$key"; my $result = Memcached::get($key); unless ($result) { - $result = $rs->site_restricted->search( { + $result = $rs->search( { state => [ 'confirmed', 'fixed' ], confirmed => { '>', \"current_timestamp-'$interval'::interval" }, } )->count; @@ -56,7 +51,7 @@ sub recent { my $key = "recent:$site_key"; my $result = Memcached::get($key); unless ($result) { - $result = $rs->site_restricted->search( { + $result = $rs->search( { state => [ 'confirmed', 'fixed' ] }, { columns => [ 'id', 'title' ], -- cgit v1.2.3 From ba0bd42476b93908529f63bb8b71135ebf7a86ca Mon Sep 17 00:00:00 2001 From: Matthew Somerville Date: Thu, 9 Jun 2011 00:59:23 +0100 Subject: Move the tricky nearby DB call to its own Result/ResultSet, and move more functions. --- perllib/FixMyStreet/DB/ResultSet/Problem.pm | 23 +++++++++++++++++++++++ 1 file changed, 23 insertions(+) (limited to 'perllib/FixMyStreet/DB/ResultSet/Problem.pm') diff --git a/perllib/FixMyStreet/DB/ResultSet/Problem.pm b/perllib/FixMyStreet/DB/ResultSet/Problem.pm index af850ecd0..ea146cd54 100644 --- a/perllib/FixMyStreet/DB/ResultSet/Problem.pm +++ b/perllib/FixMyStreet/DB/ResultSet/Problem.pm @@ -63,6 +63,29 @@ sub recent { return $result; } +# 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" }; + + my @problems = mySociety::Locale::in_gb_locale { $rs->search( $q, $attr )->all }; + return \@problems; +} + # Admin functions sub timeline { -- cgit v1.2.3 From 2f905f6b8f74a5a892b3974e4f0b4076f9b58ddf Mon Sep 17 00:00:00 2001 From: Matthew Somerville Date: Thu, 9 Jun 2011 10:37:41 +0100 Subject: Migrate recent_photos to new ResultSet, nice joining with problem_find_nearby procedure. --- perllib/FixMyStreet/DB/ResultSet/Problem.pm | 39 +++++++++++++++++++++++++++-- 1 file changed, 37 insertions(+), 2 deletions(-) (limited to 'perllib/FixMyStreet/DB/ResultSet/Problem.pm') diff --git a/perllib/FixMyStreet/DB/ResultSet/Problem.pm b/perllib/FixMyStreet/DB/ResultSet/Problem.pm index ea146cd54..0ca8d1127 100644 --- a/perllib/FixMyStreet/DB/ResultSet/Problem.pm +++ b/perllib/FixMyStreet/DB/ResultSet/Problem.pm @@ -51,18 +51,53 @@ sub recent { my $key = "recent:$site_key"; my $result = Memcached::get($key); unless ($result) { - $result = $rs->search( { + $result = [ $rs->search( { state => [ 'confirmed', 'fixed' ] }, { columns => [ 'id', 'title' ], order_by => { -desc => 'confirmed' }, rows => 5, - } )->count; + } )->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 { -- cgit v1.2.3 From 3307a7e64f7d105562a3f61af5ab935589824b9f Mon Sep 17 00:00:00 2001 From: Matthew Somerville Date: Thu, 9 Jun 2011 11:31:17 +0100 Subject: Transfer comment count function. --- perllib/FixMyStreet/DB/ResultSet/Problem.pm | 14 ++++++++++++++ 1 file changed, 14 insertions(+) (limited to 'perllib/FixMyStreet/DB/ResultSet/Problem.pm') diff --git a/perllib/FixMyStreet/DB/ResultSet/Problem.pm b/perllib/FixMyStreet/DB/ResultSet/Problem.pm index 0ca8d1127..1f06acf5a 100644 --- a/perllib/FixMyStreet/DB/ResultSet/Problem.pm +++ b/perllib/FixMyStreet/DB/ResultSet/Problem.pm @@ -29,6 +29,20 @@ sub recent_fixed { 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; -- cgit v1.2.3 From 0e163ffb8e8f9b9fa5ca1193ad2f3965f98926ec Mon Sep 17 00:00:00 2001 From: Struan Donald Date: Fri, 10 Jun 2011 11:54:47 +0100 Subject: remove reference to restriction as now handled elsewhere --- perllib/FixMyStreet/DB/ResultSet/Problem.pm | 3 +-- 1 file changed, 1 insertion(+), 2 deletions(-) (limited to 'perllib/FixMyStreet/DB/ResultSet/Problem.pm') diff --git a/perllib/FixMyStreet/DB/ResultSet/Problem.pm b/perllib/FixMyStreet/DB/ResultSet/Problem.pm index 1f06acf5a..589745dff 100644 --- a/perllib/FixMyStreet/DB/ResultSet/Problem.pm +++ b/perllib/FixMyStreet/DB/ResultSet/Problem.pm @@ -138,7 +138,7 @@ sub around_map { # Admin functions sub timeline { - my ( $rs, $restriction ) = @_; + my ( $rs ) = @_; my $prefetch = FixMyStreet::App->model('DB')->schema->storage->sql_maker->quote_char ? @@ -151,7 +151,6 @@ sub timeline { created => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, confirmed => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, whensent => { '>=', \"ms_current_timestamp()-'7 days'::interval" }, - %{ $restriction }, } }, { -- cgit v1.2.3