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)
$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 {
$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
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;
while (my $mission = $query->fetchrow_hashref){
- my @ships;
- $ships->execute($mission->{id});
- if ($ships->rows != 0){
- while (my $ship = $ships->fetchrow_hashref){
- push @ships,$ship;
- }
- $mission->{ships} = \@ships;
- }
push @incomings,$mission;
}
$c->stash(incomings => \@incomings);
$c->stash(cloud => $cloud->html);
$c->stash(css => $cloud->css);
- my $query = $dbh->prepare(q{
+ $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