1 package NDWeb::Controller::Alliances;
5 use parent 'Catalyst::Controller';
9 NDWeb::Controller::Alliances - Catalyst Controller
24 sub index :Path :Args(0) {
25 my ( $self, $c ) = @_;
26 $c->stash(template => 'alliances/list.tt2');
31 my ( $self, $c, $order ) = @_;
34 if (defined $order && $order =~ /^(score|kscore|size|ksize|members|kmem|kxp
35 |kxp|scavg|kscavg|siavg|ksiavg|kxpavg|kvalue|kvalavg)$/x){
38 $order = "score DESC";
40 my $query = $dbh->prepare(q{
41 SELECT aid AS id,alliance AS name,COALESCE(s.score,SUM(p.score)) AS score
42 ,COALESCE(s.size,SUM(p.size)) AS size,s.members,count(p.score) AS kmem
43 ,COALESCE(SUM(p.score),-1) AS kscore
44 ,COALESCE(SUM(p.size),-1) AS ksize
45 ,COALESCE(SUM(p.xp),-1) AS kxp
46 ,COALESCE(SUM(p.value),-1) AS kvalue
47 ,COALESCE(s.score/LEAST(s.members,60),-1) AS scavg
48 ,COALESCE(AVG(p.score)::int,-1) AS kscavg
49 ,COALESCE(s.size/s.members,-1) AS siavg
50 ,COALESCE(AVG(p.size)::int,-1) AS ksiavg
51 ,COALESCE(AVG(p.xp)::int,-1) AS kxpavg
52 ,COALESCE(AVG(p.value)::int,-1) AS kvalavg
54 LEFT OUTER JOIN (SELECT * FROM alliance_stats
55 WHERE tick = (SELECT max(tick) FROM alliance_stats)) s USING (aid)
56 LEFT OUTER JOIN current_planet_stats p USING (alliance,aid)
57 GROUP BY aid,alliance,s.score,s.size,s.members
58 HAVING s.score IS NOT NULL OR count(p.score) > 0
62 $c->stash(alliances => $query->fetchall_arrayref({}) );
66 my ( $self, $c, $id, $order ) = @_;
69 my $query = $dbh->prepare(q{SELECT aid AS id,alliance AS name, relationship FROM alliances WHERE aid = ?});
70 my $a = $dbh->selectrow_hashref($query,undef,$id);
74 if ($order && $order =~ /^((score|size|value|xp|hit_us|race)(rank)?)$/){
79 $c->stash(order => $order);
81 $order .= ' DESC' if $order eq 'hit_us';
83 my $members = $dbh->prepare(q{
84 SELECT pid AS id, coords(x,y,z), nick, ruler, planet, race, size, score, value, xp
85 ,planet_status,hit_us, sizerank, scorerank, valuerank, xprank
86 FROM current_planet_stats p
90 $members->execute($a->{name});
91 $c->stash(members => $members->fetchall_arrayref({}) );
93 my $ticks = $c->req->param('ticks') || 48;
94 $c->stash(showticks => $ticks);
96 $query = $dbh->prepare(q{
97 SELECT salliance, scoords, sender, talliance, tcoords, target
98 ,mission, tick AS landingtick, eta, amount, ingal, username
100 WHERE NOT ingal AND (salliance = $1 OR talliance = $1)
101 AND (mission = 'Defend' OR mission = 'AllyDef')
102 AND COALESCE( talliance <> salliance, TRUE)
103 AND tick > (tick() - $2)
104 ORDER BY tick DESC, mission
106 $query->execute($a->{name}, $ticks);
107 $c->stash(intel => $query->fetchall_arrayref({}) );
112 my ( $self, $c, $id ) = @_;
115 my $members = $dbh->prepare(q{
116 SELECT pid AS id, coords(x,y,z), metal, crystal, eonium, ps.tick
117 ,planet_status,hit_us, sizerank, scorerank, valuerank, xprank
118 FROM current_planet_stats p left outer join current_planet_scans ps using (pid)
122 $members->execute($id);
123 $c->stash(members => $members->fetchall_arrayref({}) );
128 my ( $self, $c, $id ) = @_;
131 my $query = $dbh->prepare(q{SELECT ship, id FROM ship_stats WHERE id >= 0 ORDER BY id ASC;});
133 $c->stash(ships => $query->fetchall_arrayref({}));
135 my $members = $dbh->prepare(q{
137 aus AS (SELECT DISTINCT ON (pid) pid, fid, name,tick
138 FROM fleets f join fleet_scans fs using (fid)
139 WHERE mission = 'Full fleet' and name IN ('Advanced Unit', 'Military')
140 ORDER BY pid,tick DESC)
141 ,ships AS (SELECT pid, tick, ship, amount, id AS ship_id
142 FROM fleet_ships fs JOIN aus USING(fid) JOIN ship_stats USING(ship))
143 SELECT pid, coords(x,y,z), p.race, tick, jsonb_object_agg(ship_id, amount) AS ships
144 FROM ships JOIN current_planet_stats p USING (pid)
146 GROUP BY x,y,z,pid,race,tick;
148 $members->execute($id);
149 $c->stash(members => $members->fetchall_arrayref({}) );
153 sub postallianceupdate : Local {
154 my ( $self, $c, $id, $order ) = @_;
157 my $query = $dbh->prepare(q{SELECT aid,alliance, relationship FROM alliances WHERE aid = ?});
158 my $a = $dbh->selectrow_hashref($query,undef,$id);
161 my $log = $dbh->prepare(q{INSERT INTO forum_posts (ftid,uid,message) VALUES(
162 (SELECT ftid FROM users WHERE uid = $1),$1,$2)
164 if ($c->req->param('crelationship')){
165 my $value = $c->req->param('relationship');
166 $dbh->do(q{UPDATE alliances SET relationship = ? WHERE aid =?}
168 $log->execute($c->user->id
169 ,"HC set alliance: $a->{alliance} ($id) relationship: $value");
171 my $coords = $c->req->param('coords');
172 my $findplanet = $dbh->prepare(q{SELECT pid FROM current_planet_stats
173 WHERE x = ? AND y = ? AND z = ?});
174 my $addplanet = $dbh->prepare(q{
175 UPDATE planets SET alliance = $2, nick = coalesce($3,nick)
179 while ($coords =~ m/(\d+):(\d+):(\d+)(?:\s+nick=(\S+))?/g){
180 my ($planet) = $dbh->selectrow_array($findplanet,undef,$1,$2,$3);
181 $addplanet->execute($planet,$a->{alliance},$4);
183 $nick = "(nick $4)" if defined $4;
184 $text .= "($planet) $1:$2:$3 $nick\n";
187 $log->execute($c->user->id
188 ,"HC added the following planets to alliance $a->{alliance} ($id):\n $text");
192 $c->res->redirect($c->uri_for('edit',$id));
195 sub postremoveallplanets : Local {
196 my ( $self, $c, $id, $order ) = @_;
199 my $query = $dbh->prepare(q{SELECT aid,alliance, relationship FROM alliances WHERE aid = ?});
200 my $a = $dbh->selectrow_hashref($query,undef,$id);
203 my $log = $dbh->prepare(q{INSERT INTO forum_posts (ftid,uid,message) VALUES(
204 (SELECT ftid FROM users WHERE uid = $1),$1,$2)
206 my ($coords) = $dbh->selectrow_array(q{SELECT array_to_string(array_agg(coords(x,y,z)),' ')
207 FROM current_planet_stats where alliance = $1
208 },undef,$a->{alliance});
209 my $removeplanets = $dbh->prepare(q{
210 UPDATE planets SET alliance = NULL
213 $removeplanets->execute($a->{alliance});
214 $log->execute($c->user->id
215 ,"HC cleaned alliance $a->{alliance} (id) :\n\n$coords");
218 $c->res->redirect($c->uri_for('edit',$id));
221 sub hostile : Local {
222 my ( $self, $c, $order ) = @_;
226 my $endtick = $c->stash->{TICK};
227 if ($c->req->param('ticks')){
228 $begintick = $endtick - $c->req->param('ticks');
229 }elsif(defined $c->req->param('begintick') && defined $c->req->param('endtick')){
230 $begintick = $c->req->param('begintick');
231 $endtick = $c->req->param('endtick');
234 my $query = $dbh->prepare(q{
235 SELECT aid, alliance, hostile_count, targeted, targeted_raids
236 FROM hostile_alliances($1,$2)
237 ORDER BY hostile_count DESC
239 $query->execute($begintick,$endtick);
240 $c->stash(alliances => $query->fetchall_arrayref({}) );
241 $c->stash(ticks => $endtick - $begintick);
242 $c->stash(begin_tick => $begintick);
243 $c->stash(end_tick => $endtick);
246 sub resources : Local {
247 my ( $self, $c, $order ) = @_;
250 if (defined $order && $order =~ /^(size|score|resources|hidden|resplanet|hidplanet|nscore|nscore2|nscore3)$/){
253 $order = "resplanet DESC";
256 my $query = $dbh->prepare(q{
257 SELECT aid AS id,alliance AS name,relationship,members,score,size
258 ,resources,hidden,planets
259 ,(resources/planets)::bigint AS resplanet
260 ,(hidden/planets)::bigint AS hidplanet
261 , nscore, nscore2, nscore3
262 FROM alliance_resources
266 $c->stash(alliances => $query->fetchall_arrayref({}));
272 Michael Andreen (harv@ruin.nu)