+ my ( $self, $c ) = @_;
+ $c->stash(template => 'alliances/list.tt2');
+ $c->forward('list');
+}
+
+sub list : Local {
+ my ( $self, $c, $order ) = @_;
+ my $dbh = $c->model;
+
+ if (defined $order && $order =~ /^(score|kscore|size|ksize|members|kmem|kxp
+ |kxp|scavg|kscavg|siavg|ksiavg|kxpavg|kvalue|kvalavg)$/x){
+ $order = "$1 DESC";
+ } else {
+ $order = "score DESC";
+ }
+ my $query = $dbh->prepare(q{
+ SELECT aid AS id,alliance AS name,COALESCE(s.score,SUM(p.score)) AS score
+ ,COALESCE(s.size,SUM(p.size)) AS size,s.members,count(p.score) AS kmem
+ ,COALESCE(SUM(p.score),-1) AS kscore
+ ,COALESCE(SUM(p.size),-1) AS ksize
+ ,COALESCE(SUM(p.xp),-1) AS kxp
+ ,COALESCE(SUM(p.value),-1) AS kvalue
+ ,COALESCE(s.score/LEAST(s.members,60),-1) AS scavg
+ ,COALESCE(AVG(p.score)::int,-1) AS kscavg
+ ,COALESCE(s.size/s.members,-1) AS siavg
+ ,COALESCE(AVG(p.size)::int,-1) AS ksiavg
+ ,COALESCE(AVG(p.xp)::int,-1) AS kxpavg
+ ,COALESCE(AVG(p.value)::int,-1) AS kvalavg
+ FROM alliances a
+ LEFT OUTER JOIN (SELECT * FROM alliance_stats
+ WHERE tick = (SELECT max(tick) FROM alliance_stats)) s USING (aid)
+ LEFT OUTER JOIN current_planet_stats p USING (alliance,aid)
+ GROUP BY aid,alliance,s.score,s.size,s.members
+ HAVING s.score IS NOT NULL OR count(p.score) > 0
+ ORDER BY
+ } . $order);
+ $query->execute;
+ $c->stash(alliances => $query->fetchall_arrayref({}) );
+}
+
+sub edit : Local {
+ my ( $self, $c, $id, $order ) = @_;
+ my $dbh = $c->model;
+
+ my $query = $dbh->prepare(q{SELECT aid AS id,alliance AS name, relationship FROM alliances WHERE aid = ?});
+ my $a = $dbh->selectrow_hashref($query,undef,$id);
+ $c->stash(a => $a);
+
+
+ if ($order && $order =~ /^((score|size|value|xp|hit_us|race)(rank)?)$/){
+ $order = $1;
+ }else {
+ $order = 'x,y,z';
+ }
+ $c->stash(order => $order);
+
+ $order .= ' DESC' if $order eq 'hit_us';
+
+ my $members = $dbh->prepare(q{
+ SELECT pid AS id, coords(x,y,z), nick, ruler, planet, race, size, score, value, xp
+ ,planet_status,hit_us, sizerank, scorerank, valuerank, xprank
+ FROM current_planet_stats p
+ WHERE p.alliance = ?
+ ORDER BY
+ } . $order);
+ $members->execute($a->{name});
+ $c->stash(members => $members->fetchall_arrayref({}) );
+
+ my $ticks = $c->req->param('ticks') || 48;
+ $c->stash(showticks => $ticks);
+
+ $query = $dbh->prepare(q{
+SELECT salliance, scoords, sender, talliance, tcoords, target
+ ,mission, tick AS landingtick, eta, amount, ingal, username
+FROM full_intel
+WHERE NOT ingal AND (salliance = $1 OR talliance = $1)
+ AND (mission = 'Defend' OR mission = 'AllyDef')
+ AND COALESCE( talliance <> salliance, TRUE)
+ AND tick > (tick() - $2)
+ORDER BY tick DESC, mission
+ });
+ $query->execute($a->{name}, $ticks);
+ $c->stash(intel => $query->fetchall_arrayref({}) );
+}