1 package NDWeb::Controller::Alliances;
5 use parent 'Catalyst::Controller';
11 NDWeb::Controller::Alliances - Catalyst Controller
26 sub index :Path :Args(0) {
27 my ( $self, $c ) = @_;
28 $c->stash(template => 'alliances/list.tt2');
33 my ( $self, $c, $order ) = @_;
36 if (defined $order && $order =~ /^(score|kscore|size|ksize|members|kmem|kxp
37 |kxp|scavg|kscavg|siavg|ksiavg|kxpavg|kvalue|kvalavg)$/x){
40 $order = "score DESC";
42 my $query = $dbh->prepare(q{
43 SELECT DISTINCT a.id,name,COALESCE(s.score,SUM(p.score)) AS score
44 ,COALESCE(s.size,SUM(p.size)) AS size,s.members,count(p.score) AS kmem
45 ,COALESCE(SUM(p.score),-1) AS kscore
46 ,COALESCE(SUM(p.size),-1) AS ksize
47 ,COALESCE(SUM(p.xp),-1) AS kxp
48 ,COALESCE(SUM(p.value),-1) AS kvalue
49 ,COALESCE(s.score/LEAST(s.members,60),-1) AS scavg
50 ,COALESCE(AVG(p.score)::int,-1) AS kscavg
51 ,COALESCE(s.size/s.members,-1) AS siavg
52 ,COALESCE(AVG(p.size)::int,-1) AS ksiavg
53 ,COALESCE(AVG(p.xp)::int,-1) AS kxpavg
54 ,COALESCE(AVG(p.value)::int,-1) AS kvalavg
56 LEFT OUTER JOIN (SELECT * FROM alliance_stats
57 WHERE tick = (SELECT max(tick) FROM alliance_stats)) s ON s.id = a.id
58 LEFT OUTER JOIN current_planet_stats p ON p.alliance_id = a.id
59 GROUP BY a.id,a.name,s.score,s.size,s.members
60 HAVING s.score IS NOT NULL OR count(p.score) > 0
64 $c->stash(alliances => $query->fetchall_arrayref({}) );
65 $c->stash(comma => \&comma_value);
69 my ( $self, $c, $id, $order ) = @_;
72 my $query = $dbh->prepare(q{SELECT id,name, relationship FROM alliances WHERE id = ?});
73 my $a = $dbh->selectrow_hashref($query,undef,$id);
77 if ($order && $order =~ /^((score|size|value|xp|hit_us|race)(rank)?)$/){
82 $c->stash(order => $order);
84 $order .= ' DESC' if $order eq 'hit_us';
86 my $members = $dbh->prepare(q{
87 SELECT id, coords(x,y,z), nick, ruler, planet, race, size, score, value, xp
88 ,planet_status,hit_us, sizerank, scorerank, valuerank, xprank
89 FROM current_planet_stats p
90 WHERE p.alliance_id = ?
93 $members->execute($a->{id});
94 $c->stash(members => $members->fetchall_arrayref({}) );
96 my $ticks = $c->req->param('ticks') || 48;
97 $c->stash(showticks => $ticks);
99 $query = $dbh->prepare(intelquery q{
100 o.alliance AS oalliance ,coords(o.x,o.y,o.z) AS ocoords, i.sender
101 ,t.alliance AS talliance,coords(t.x,t.y,t.z) AS tcoords, i.target
102 },q{NOT ingal AND (o.alliance_id = $1 OR t.alliance_id = $1)
103 AND (i.mission = 'Defend' OR i.mission = 'AllyDef')
104 AND ((( t.alliance_id != o.alliance_id OR t.alliance_id IS NULL OR o.alliance_id IS NULL)))
105 AND i.sender NOT IN (SELECT planet FROM users u NATURAL JOIN groupmembers gm WHERE gid = 8 AND planet IS NOT NULL)
106 AND NOT (i.back IS NOT NULL AND i.back = i.tick + 4)
107 AND i.tick > (tick() - $2)
109 $query->execute($a->{id}, $ticks);
110 $c->stash(intel => $query->fetchall_arrayref({}) );
113 sub postallianceupdate : Local {
114 my ( $self, $c, $id, $order ) = @_;
118 my $log = $dbh->prepare(q{INSERT INTO forum_posts (ftid,uid,message) VALUES(
119 (SELECT ftid FROM users WHERE uid = $1),$1,$2)
121 if ($c->req->param('crelationship')){
122 my $value = $c->req->param('relationship');
123 $dbh->do(q{UPDATE alliances SET relationship = ? WHERE id =?}
125 $log->execute($c->user->id
126 ,"HC set alliance: $id relationship: $value");
128 my $coords = $c->req->param('coords');
129 my $findplanet = $dbh->prepare(q{SELECT id FROM current_planet_stats
130 WHERE x = ? AND y = ? AND z = ?});
131 my $addplanet = $dbh->prepare(q{
132 UPDATE planets SET alliance_id = $2, nick = coalesce($3,nick)
136 while ($coords =~ m/(\d+):(\d+):(\d+)(?:\s+nick=(\S+))?/g){
137 my ($planet) = $dbh->selectrow_array($findplanet,undef,$1,$2,$3);
138 $addplanet->execute($planet,$id,$4);
140 $nick = "(nick $4)" if defined $4;
141 $text .= "($planet) $1:$2:$3 $nick\n";
144 $log->execute($c->user->id
145 ,"HC added the following planets to alliance $id:\n $text");
149 $c->res->redirect($c->uri_for('edit',$id));
152 sub postremoveallplanets : Local {
153 my ( $self, $c, $id, $order ) = @_;
157 my $log = $dbh->prepare(q{INSERT INTO forum_posts (ftid,uid,message) VALUES(
158 (SELECT ftid FROM users WHERE uid = $1),$1,$2)
160 my ($coords) = $dbh->selectrow_array(q{SELECT CONCAT(coords(x,y,z) || ' ')
161 FROM current_planet_stats where alliance_id = $1
163 my $removeplanets = $dbh->prepare(q{
164 UPDATE planets SET alliance_id = NULL
165 WHERE alliance_id = $1;
167 $removeplanets->execute($id);
168 $log->execute($c->user->id
169 ,"HC cleaned alliance $id :\n\n$coords");
172 $c->res->redirect($c->uri_for('edit',$id));
175 sub hostile : Local {
176 my ( $self, $c, $order ) = @_;
180 my $endtick = $c->stash->{TICK};
181 if ($c->req->param('ticks')){
182 $begintick = $endtick - $c->req->param('ticks');
183 }elsif(defined $c->req->param('begintick') && defined $c->req->param('endtick')){
184 $begintick = $c->req->param('begintick');
185 $endtick = $c->req->param('endtick');
188 my $query = $dbh->prepare(q{
189 SELECT s.alliance_id AS id,s.alliance AS name,count(*) AS hostile_count
191 JOIN incomings i ON i.call = c.id
192 JOIN current_planet_stats s ON i.sender = s.id
193 WHERE c.landing_tick - i.eta > $1 and c.landing_tick - i.eta < $2
194 GROUP BY s.alliance_id,s.alliance
195 ORDER BY hostile_count DESC
197 $query->execute($begintick,$endtick);
198 $c->stash(alliances => $query->fetchall_arrayref({}) );
199 $c->stash(ticks => $endtick - $begintick);
200 $c->stash(begin_tick => $begintick);
201 $c->stash(end_tick => $endtick);
204 sub resources : Local {
205 my ( $self, $c, $order ) = @_;
208 if (defined $order && $order =~ /^(size|score|resources|hidden|resplanet|hidplanet|nscore|nscore2|nscore3)$/){
211 $order = "resplanet DESC";
214 my $query = $dbh->prepare(qq{
215 SELECT a.id,a.name,a.relationship,s.members,s.score,s.size
216 ,r.resources,r.hidden,r.planets
217 ,(resources/planets)::bigint AS resplanet
218 ,(hidden/planets)::bigint AS hidplanet
219 ,((resources / 300) + (hidden / 100))::bigint AS scoregain
220 ,(score + (resources / 300) + (hidden / 100))::bigint AS nscore
221 ,((resources/planets*scoremem)/300 + (hidden/planets*scoremem)/100)::bigint AS scoregain2
222 ,(score + (resources/planets*scoremem)/300
223 + (hidden/planets*scoremem)/100)::bigint AS nscore2
224 ,((s.size::int8*(1400-tick())*250)/100 + score + (resources/planets*scoremem)/300
225 + (hidden/planets*scoremem)/100)::bigint AS nscore3
226 ,(s.size::int8*(1400-tick())*250)/100 AS scoregain3
227 FROM (SELECT alliance_id AS id,sum(metal+crystal+eonium) AS resources, sum(hidden) AS hidden, count(*) AS planets
228 FROM planets p join planet_scans c ON p.id = c.planet GROUP by alliance_id) r
229 NATURAL JOIN alliances a
230 LEFT OUTER JOIN (SELECT *,LEAST(members,60) AS scoremem FROM alliance_stats
231 WHERE tick = (SELECT max(tick) FROM alliance_stats)) s ON a.id = s.id
236 while (my $alliance = $query->fetchrow_hashref){
237 push @alliances,$alliance;
239 $c->stash(alliances => \@alliances);
245 Michael Andreen (harv@ruin.nu)