+sub targetlist : Local {
+ my ($self, $c, $alliances, $order) = @_;
+ my $dbh = $c->model;
+
+ $c->stash(comma => \&comma_value);
+ $c->stash(allies => $alliances);
+ $alliances ||= '';
+ my @alliances = split /,/, $alliances;
+
+ $c->forward('listAlliances');
+
+ if ($order && $order =~ /^(sizerank|valuerank|scorerank|xprank|nfvalue|nfvalue2)$/){
+ $order = "$1";
+ }else{
+ $order = "nfvalue";
+ }
+ $order = "p.$order" if $order =~ /rank$/;
+
+ my $query = $dbh->prepare(q{
+SELECT p.id, coords(p.x,p.y,p.z),p.x,p.y,p.alliance, p.score, p.value, p.size, p.xp,nfvalue, nfvalue - sum(pa.value) AS nfvalue2, p.race
+FROM current_planet_stats p
+ JOIN (SELECT g.x,g.y, sum(p.value) AS nfvalue
+ FROM galaxies g join current_planet_stats p on g.x = p.x AND g.y = p.y
+ WHERE g.tick = (SELECT max(tick) from galaxies)
+ AND (planet_status IN ('','Hostile')
+ AND (relationship IS NULL OR relationship IN ('','Hostile')))
+ GROUP BY g.x,g.y
+ ) g ON p.x = g.x AND p.y = g.y
+ JOIN current_planet_stats pa ON pa.x = g.x AND pa.y = g.y
+WHERE p.x <> 200
+ AND p.alliance_id = ANY ($1)
+ AND pa.alliance_id = ANY ($1)
+ AND p.relationship IN ('','Hostile')
+GROUP BY p.id, p.x,p.y,p.z,p.alliance, p.score, p.value, p.size, p.xp, nfvalue,p.race
+ ,p.scorerank,p.valuerank,p.sizerank,p.xprank
+ORDER BY
+ } . $order);
+ $query->execute(\@alliances);
+ $c->stash(planets => $query->fetchall_arrayref({}) );
+ $c->forward('create');
+}
+
+sub posttargetalliances : Local {
+ my ($self, $c) = @_;
+
+ $c->res->redirect($c->uri_for('targetlist',join ',',$c->req->param('alliances')));
+}
+
+sub listAlliances : Private {
+ my ($self, $c) = @_;
+ my @alliances;
+ my $query = $c->model->prepare(q{SELECT id,name FROM alliances
+ WHERE relationship IN ('','Hostile')
+ AND id IN (SELECT alliance_id FROM planets)
+ ORDER BY LOWER(name)
+ });
+ $query->execute;
+ push @alliances,{id => -1, name => ''};
+ while (my $ally = $query->fetchrow_hashref){
+ push @alliances,$ally;
+ }
+ $c->stash(alliances => \@alliances);
+}
+