Fix in case planets happens to be 0

[ndwebbie.git] / lib / NDWeb / Controller / Stats.pm

diff --git a/lib/NDWeb/Controller/Stats.pm b/lib/NDWeb/Controller/Stats.pm
index a1db2459db9a6e55f21341a8f805dcfe177c00f4..9379e99c0aa8574835eb86ddee63728bf49dae7a 100644 (file)
--- a/lib/NDWeb/Controller/Stats.pm
+++ b/lib/NDWeb/Controller/Stats.pm
@@ -5,6 +5,7 @@ use warnings;
 use parent 'Catalyst::Controller';
 
 use NDWeb::Include;
+use HTML::TagCloud;
 
 =head1 NAME
 
@@ -61,7 +62,7 @@ FROM galaxies g
                        GROUP BY raid,p.tick,x,y
                        ) AS a
                        JOIN galaxies g USING (tick,x,y)
-               WHERE a.count::float / g.planets >= 0.5
+               WHERE a.count::float / NULLIF(g.planets,0) >= 0.5
                ORDER BY x,y,tick
        ) AS raid USING (x,y)
 
@@ -111,6 +112,21 @@ WHERE g.tick = ( SELECT max(tick) AS max FROM galaxies)
 
        $query->execute($x,$y,$z);
        $c->stash(planets => $query->fetchall_arrayref({}) );
+
+       $query = $dbh->prepare(q{
+WITH p AS (
+       SELECT x,y FROM galaxies
+       WHERE tick = tick() AND (x <= $1 AND y < $2 OR x < $1)
+       ORDER BY x DESC, y DESC LIMIT 1
+), n AS (
+       SELECT x,y FROM galaxies
+       WHERE tick = tick() AND (x >= $1 AND y > $2 OR x > $1)
+       ORDER BY x ASC, y ASC LIMIT 1
+)
+TABLE p UNION TABLE n
+               });
+       $query->execute($x,$y);
+       $c->stash(browse => $query->fetchall_arrayref({}) );
 }
 
 sub planet : Local {
@@ -168,7 +184,7 @@ sub planet : Local {
                $c->stash(outgoings => \@missions);
 
                $query = $dbh->prepare(q{
-                       SELECT DISTINCT ON (i.tick,x,y,z,pid,i.name,i.amount) i.id,i.mission, i.name, i.tick,eta
+                       SELECT DISTINCT ON (i.tick,x,y,z,pid,i.name,i.mission,i.amount) i.id,i.mission, i.name, i.tick,eta
                                                , i.amount, coords(x,y,z) AS coords, pid AS planet
                        FROM intel i
                        LEFT OUTER JOIN (planets
@@ -177,7 +193,7 @@ sub planet : Local {
                        WHERE  i.uid = -1
                                AND i.target = ?
                                AND i.tick > tick() - 3
-                       ORDER BY i.tick,x,y,z,pid,i.name,i.amount,i.eta
+                       ORDER BY i.tick,x,y,z,pid,i.name,i.mission,i.amount,i.eta
                });
                $query->execute($id);
                my @incomings;
@@ -259,6 +275,45 @@ sub find : Local {
        }
 }
 
+sub tags : Local {
+       my ( $self, $c ) = @_;
+       my $dbh = $c->model;
+
+       my @tags = $c->req->param('tags');
+       $c->stash(tags => \@tags);
+       my $query = $dbh->prepare(q{
+SELECT tag, count(*)
+FROM planet_tags pt
+WHERE tag <> ALL($1)
+       AND ($3 OR uid = $2)
+GROUP BY tag
+               });
+       $query->execute(\@tags, $c->user->id, $c->check_user_roles('stats_tags_all') // 0);
+       my $cloud = HTML::TagCloud->new;
+       while (my $tag = $query->fetchrow_hashref){
+               my @t = @tags;
+               push @t, $tag->{tag};
+               my %param = (tags => \@t);
+               $cloud->add($tag->{tag}, $c->uri_for('tags',\%param), $tag->{count});
+       }
+       $c->stash(cloud => $cloud->html);
+       $c->stash(css => $cloud->css);
+
+       $query = $dbh->prepare(q{
+WITH p AS (SELECT pid, coords(x,y,z) FROM current_planet_stats
+), t AS (SELECT pid,tag,bool_or(uid = $2) AS own,max(time) AS time
+       FROM planet_tags
+       WHERE ($3 OR uid = $2)
+       GROUP BY pid,tag
+       ORDER BY time DESC
+), tags AS (SELECT pid, array_agg(tag) AS tags
+       FROM t GROUP BY pid
+)
+SELECT p.*k,array_to_string(tags,', ') AS tags FROM p JOIN tags USING (pid) WHERE tags @> $1;
+               });
+       $query->execute(\@tags, $c->user->id, $c->check_user_roles('stats_tags_all') // 0);
+       $c->stash(planets => $query->fetchall_arrayref({}));
+}
 
 =head1 AUTHOR