+sub naps : Local {
+ my ( $self, $c ) = @_;
+ my $dbh = $c->model;
+
+ my $query = $dbh->prepare(q{SELECT pid AS id,coords(x,y,z)
+ ,ruler, p.planet,race, size, score, value
+ , xp, sizerank, scorerank, valuerank, xprank, p.value - p.size*200
+ - COALESCE(ps.metal+ps.crystal+ps.eonium,0)/150
+ - COALESCE(ds.total ,(SELECT COALESCE(avg(total),0)
+ FROM current_development_scans)::int)*1500 AS fleetvalue
+ ,(metal+crystal+eonium)/100 AS resvalue, planet_status,hit_us
+ , alliance,relationship,nick
+ FROM current_planet_stats p
+ LEFT OUTER JOIN current_planet_scans ps USING (pid)
+ LEFT OUTER JOIN current_development_scans ds USING (pid)
+ WHERE planet_status IN ('Friendly','NAP') order by x,y,z asc
+ });
+ $query->execute;
+ $c->stash(planets => $query->fetchall_arrayref({}) );
+}