+ my $query = $dbh->prepare(q{SELECT id,name, relationship FROM alliances WHERE id = ?});
+ 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 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_id = ?
+ ORDER BY
+ } . $order);
+ $members->execute($a->{id});
+ $c->stash(members => $members->fetchall_arrayref({}) );
+
+ my $ticks = $c->req->param('ticks') || 48;
+ $c->stash(showticks => $ticks);
+
+ $query = $dbh->prepare(intelquery q{
+ o.alliance AS oalliance ,coords(o.x,o.y,o.z) AS ocoords, i.sender
+ ,t.alliance AS talliance,coords(t.x,t.y,t.z) AS tcoords, i.target
+ },q{NOT ingal AND (o.alliance_id = $1 OR t.alliance_id = $1)
+ AND (i.mission = 'Defend' OR i.mission = 'AllyDef')
+ AND ((( t.alliance_id != o.alliance_id OR t.alliance_id IS NULL OR o.alliance_id IS NULL)))
+ AND i.sender NOT IN (SELECT planet FROM users u NATURAL JOIN groupmembers gm WHERE gid = 8 AND planet IS NOT NULL)
+ AND NOT (i.back IS NOT NULL AND i.back = i.tick + 4)
+ AND i.tick > (tick() - $2)
+ });
+ $query->execute($a->{id}, $ticks);
+ $c->stash(intel => $query->fetchall_arrayref({}) );
+}
+
+sub postallianceupdate : Local {
+ my ( $self, $c, $id, $order ) = @_;
+ my $dbh = $c->model;
+
+ $dbh->begin_work;
+ my $log = $dbh->prepare(q{INSERT INTO forum_posts (ftid,uid,message) VALUES(
+ (SELECT ftid FROM users WHERE uid = $1),$1,$2)
+ });
+ if ($c->req->param('crelationship')){
+ my $value = $c->req->param('relationship');
+ $dbh->do(q{UPDATE alliances SET relationship = ? WHERE id =?}
+ ,undef,$value,$id);
+ $log->execute($c->user->id
+ ,"HC set alliance: $id relationship: $value");
+ }
+ my $coords = $c->req->param('coords');
+ my $findplanet = $dbh->prepare(q{SELECT id FROM current_planet_stats
+ WHERE x = ? AND y = ? AND z = ?});
+ my $addplanet = $dbh->prepare(q{
+ UPDATE planets SET alliance_id = $2, nick = coalesce($3,nick)
+ WHERE id = $1;
+ });
+ my $text = '';
+ while ($coords =~ m/(\d+):(\d+):(\d+)(?:\s+nick=(\S+))?/g){
+ my ($planet) = $dbh->selectrow_array($findplanet,undef,$1,$2,$3);
+ $addplanet->execute($planet,$id,$4);
+ my $nick = '';
+ $nick = "(nick $4)" if defined $4;
+ $text .= "($planet) $1:$2:$3 $nick\n";
+ }
+ if ($text){
+ $log->execute($c->user->id
+ ,"HC added the following planets to alliance $id:\n $text");
+ }
+ $dbh->commit;
+
+ $c->res->redirect($c->uri_for('edit',$id));
+}
+
+sub postremoveallplanets : Local {
+ my ( $self, $c, $id, $order ) = @_;
+ my $dbh = $c->model;
+
+ $dbh->begin_work;
+ my $log = $dbh->prepare(q{INSERT INTO forum_posts (ftid,uid,message) VALUES(
+ (SELECT ftid FROM users WHERE uid = $1),$1,$2)
+ });
+ my ($coords) = $dbh->selectrow_array(q{SELECT CONCAT(coords(x,y,z) || ' ')
+ FROM current_planet_stats where alliance_id = $1
+ },undef,$id);
+ my $removeplanets = $dbh->prepare(q{
+ UPDATE planets SET alliance_id = NULL
+ WHERE alliance_id = $1;
+ });
+ $removeplanets->execute($id);
+ $log->execute($c->user->id
+ ,"HC cleaned alliance $id :\n\n$coords");
+ $dbh->commit;
+
+ $c->res->redirect($c->uri_for('edit',$id));
+}
+
+sub hostile : Local {
+ my ( $self, $c, $order ) = @_;
+ my $dbh = $c->model;
+
+ my $begintick = 0;
+ my $endtick = $c->stash->{TICK};
+ if ($c->req->param('ticks')){
+ $begintick = $endtick - $c->req->param('ticks');
+ }elsif(defined $c->req->param('begintick') && defined $c->req->param('endtick')){
+ $begintick = $c->req->param('begintick');
+ $endtick = $c->req->param('endtick');
+ }
+
+ my $query = $dbh->prepare(q{
+ SELECT s.alliance_id AS id,s.alliance AS name,count(*) AS hostile_count
+FROM calls c
+ JOIN incomings i ON i.call = c.id
+ JOIN current_planet_stats s ON i.sender = s.id
+WHERE c.landing_tick - i.eta > $1 and c.landing_tick - i.eta < $2
+GROUP BY s.alliance_id,s.alliance
+ORDER BY hostile_count DESC
+ });
+ $query->execute($begintick,$endtick);
+ $c->stash(alliances => $query->fetchall_arrayref({}) );
+ $c->stash(ticks => $endtick - $begintick);
+ $c->stash(begin_tick => $begintick);
+ $c->stash(end_tick => $endtick);