]> ruin.nu Git - ndwebbie.git/blob - lib/NDWeb/Controller/Raids.pm
Converted editRaid page
[ndwebbie.git] / lib / NDWeb / Controller / Raids.pm
1 package NDWeb::Controller::Raids;
2
3 use strict;
4 use warnings;
5 use parent 'Catalyst::Controller';
6
7 use POSIX;
8 use NDWeb::Include;
9 use ND::Include;
10
11 =head1 NAME
12
13 NDWeb::Controller::Raids - Catalyst Controller
14
15 =head1 DESCRIPTION
16
17 Catalyst Controller.
18
19 =head1 METHODS
20
21 =cut
22
23
24 =head2 index 
25
26 =cut
27
28 sub index :Path :Args(0) {
29         my ( $self, $c ) = @_;
30         my $dbh = $c->model;
31
32         my $launched = 0;
33         my $query = $dbh->prepare(q{SELECT r.id,released_coords AS releasedcoords,tick,waves*COUNT(DISTINCT rt.id) AS waves,
34                         COUNT(rc.uid) AS claims, COUNT(nullif(rc.launched,false)) AS launched,COUNT(NULLIF(rc.uid > 0,true)) AS blocked
35                 FROM raids r JOIN raid_targets rt ON r.id = rt.raid
36                         LEFT OUTER JOIN raid_claims rc ON rt.id = rc.target
37                 WHERE open AND not removed AND r.id 
38                         IN (SELECT raid FROM raid_access NATURAL JOIN groupmembers WHERE uid = ?)
39                 GROUP BY r.id,released_coords,tick,waves});
40         $query->execute($c->user->id);
41         my @raids;
42         while (my $raid = $query->fetchrow_hashref){
43                 $raid->{waves} -= $raid->{blocked};
44                 $raid->{claims} -= $raid->{blocked};
45                 delete $raid->{blocked};
46                 $launched += $raid->{launched};
47                 push @raids,$raid;
48         }
49         $c->stash(raids => \@raids);
50
51         if ($c->check_user_roles(qw/raids_info/)){
52                 my $query = $dbh->prepare(q{SELECT r.id,open ,tick,waves*COUNT(DISTINCT rt.id) AS waves,
53                         COUNT(rc.uid) AS claims, COUNT(nullif(rc.launched,false)) AS launched ,COUNT(NULLIF(uid > 0,true)) AS blocked
54                 FROM raids r JOIN raid_targets rt ON r.id = rt.raid
55                         LEFT OUTER JOIN raid_claims rc ON rt.id = rc.target
56                 WHERE not removed AND (not open 
57                         OR r.id NOT IN (SELECT raid FROM raid_access NATURAL JOIN groupmembers WHERE uid = ?))
58                 GROUP BY r.id,open,tick,waves});
59                 $query->execute($c->user->id);
60                 my @raids;
61                 while (my $raid = $query->fetchrow_hashref){
62                         $raid->{waves} -= $raid->{blocked};
63                         $raid->{claims} -= $raid->{blocked};
64                         delete $raid->{blocked};
65                         $launched += $raid->{launched};
66                         push @raids,$raid;
67                 }
68                 $c->stash(closedraids => \@raids);
69
70
71                 $query = $dbh->prepare(q{SELECT r.id,tick,waves*COUNT(DISTINCT rt.id) AS waves,
72                         COUNT(rc.uid) AS claims, COUNT(nullif(rc.launched,false)) AS launched ,COUNT(NULLIF(uid > 0,true)) AS blocked
73                 FROM raids r JOIN raid_targets rt ON r.id = rt.raid
74                         LEFT OUTER JOIN raid_claims rc ON rt.id = rc.target
75                 WHERE removed
76                 GROUP BY r.id,tick,waves});
77                 $query->execute;
78                 my @oldraids;
79                 while (my $raid = $query->fetchrow_hashref){
80                         $raid->{waves} -= $raid->{blocked};
81                         $raid->{claims} -= $raid->{blocked};
82                         delete $raid->{blocked};
83                         $launched += $raid->{launched};
84                         push @oldraids,$raid;
85                 }
86                 $c->stash(removedraids => \@oldraids);
87                 $c->stash(launched => $launched);
88         }
89
90 }
91
92 sub view : Local {
93         my ( $self, $c, $raid ) = @_;
94         my $dbh = $c->model;
95
96         $c->forward('findRaid');
97         $raid = $c->stash->{raid};
98
99
100         $c->stash(raid => $raid->{id});
101         my $noingal = '';
102         my $planet;
103         if ($c->user->planet){
104                 my $query = $dbh->prepare("SELECT value, score,x,y FROM current_planet_stats WHERE id = ?");
105                 $planet = $dbh->selectrow_hashref($query,undef,$c->user->planet);
106                 $noingal = "AND NOT (x = $planet->{x} AND y = $planet->{y})";
107         }
108         $c->stash(message => parseMarkup($raid->{message}));
109         $c->stash(landingtick => $raid->{tick});
110         my $targetquery = $dbh->prepare(qq{SELECT r.id, r.planet, size, score, value
111                 , p.x,p.y,p.z, race
112                 , p.value - p.size*200 - 
113                         COALESCE(ps.metal+ps.crystal+ps.eonium,0)/150 - 
114                         COALESCE(ss.total ,(SELECT
115                                 COALESCE(avg(total),0) FROM
116                                 structure_scans)::int)*1500 AS fleetvalue
117                 ,(metal+crystal+eonium)/100 AS resvalue, comment
118                 , hidden, light, medium, heavy
119                 FROM current_planet_stats p 
120                 JOIN raid_targets r ON p.id = r.planet 
121                 LEFT OUTER JOIN planet_scans ps ON p.id = ps.planet
122                 LEFT OUTER JOIN structure_scans ss ON p.id = ss.planet
123                 WHERE r.raid = ?
124                 $noingal
125                 ORDER BY size});
126         $targetquery->execute($raid->{id});
127         my @targets;
128         my %production = (0 => 'None', 35 => 'Light', 65 => 'Medium', 100 => 'High');
129         while (my $target = $targetquery->fetchrow_hashref){
130                 my %target;
131                 if ($planet){
132                         if ($planet->{x} == $target->{x}){
133                                 $target{style} = 'incluster';
134                         }
135                         $target{scorebash} = 'bash' if ($target->{score}/$planet->{score} < 0.4);
136                         $target{valuebash} = 'bash' if ($target->{value}/$planet->{value} < 0.4);
137                         #next if ($target->{score}/$planet->{score} < 0.4) && ($target->{value}/$planet->{value} < 0.4);
138                 }
139                 $target{id} = $target->{id};
140                 $target{race} = $target->{race};
141                 my $num = pow(10,length($target->{score})-2);
142                 $target{score} = "Hidden"; #ceil($target->{score}/$num)*$num;
143                 $num = pow(10,length($target->{value})-2);
144                 $target{value} = "Hidden"; #ceil($target->{value}/$num)*$num;
145                 $num = pow(10,length($target->{size})-2);
146                 $target{size} = floor($target->{size}/$num)*$num;
147                 $num = pow(10,length($target->{fleetvalue})-2);
148                 $target{fleetvalue} = floor($target->{fleetvalue}/$num)*$num;
149                 if (defined $target->{resvalue}){
150                         $num = pow(10,length($target->{resvalue})-2);
151                         $target{resvalue} = floor($target->{resvalue}/$num)*$num;
152                 }
153                 $target{comment} = parseMarkup($target->{comment}) if ($target->{comment});
154                 
155                 $target{hidden} = int($target->{hidden} / 100);
156                 $target{light} = $production{$target->{light}};
157                 $target{medium} = $production{$target->{medium}};
158                 $target{heavy} = $production{$target->{heavy}};
159
160                 my $unitscans = $dbh->prepare(q{ 
161                         SELECT DISTINCT ON (name) i.id,i.name, i.tick, i.amount 
162                         FROM fleets i
163                         WHERE  i.uid = -1
164                                 AND i.sender = ?
165                                 AND i.mission = 'Full fleet'
166                         GROUP BY i.id,i.tick,i.name,i.amount
167                         ORDER BY name,i.tick DESC
168                 });
169                 $unitscans->execute($target->{planet});
170                 my $ships = $dbh->prepare(q{SELECT ship,amount FROM fleet_ships
171                         WHERE id = ? ORDER BY num
172                 });
173                 my @missions;
174                 while (my $mission = $unitscans->fetchrow_hashref){
175                         my @ships;
176                         $ships->execute($mission->{id});
177                         while (my $ship = $ships->fetchrow_hashref){
178                                 push @ships,$ship;
179                         }
180                         push @ships, {ship => 'No', amount => 'ships'} if @ships == 0;
181                         $mission->{ships} = \@ships;
182                         $mission->{amount} =~ s/(^[-+]?\d+?(?=(?>(?:\d{3})+)(?!\d))|\G\d{3}(?=\d))/$1,/g; #Add comma for ever 3 digits, i.e. 1000 => 1,000
183                         delete $mission->{id};
184                         push @missions,$mission;
185                 }
186                 $target{missions} = \@missions;
187
188                 my $query = $dbh->prepare(q{SELECT DISTINCT ON(rid) tick,category,name,amount
189                         FROM planet_data pd JOIN planet_data_types pdt ON pd.rid = pdt.id
190                         WHERE pd.id = $1 AND rid in (1,2,3,4,5,6,9,10,14,15,16,17,18)
191                         ORDER BY rid,tick DESC
192                 });
193                 $query->execute($target->{planet});
194                 while (my $data = $query->fetchrow_hashref){
195                         $data->{amount} =~ s/(^[-+]?\d+?(?=(?>(?:\d{3})+)(?!\d))|\G\d{3}(?=\d))/$1,/g; #Add comma for ever 3 digits, i.e. 1000 => 1,000
196                         $data->{name} =~ s/ /_/g;
197                         $target{$data->{category}.$data->{name}} = $data->{amount};
198                 }
199
200                 my @roids;
201                 my @claims;
202                 my $size = $target{size};
203                 for (my $i = 1; $i <= $raid->{waves}; $i++){
204                         my $roids = floor(0.25*$size);
205                         $size -= $roids;
206                         my $xp = 0;
207                         if ($planet){
208                                 $xp = pa_xp($roids,$planet->{score},$planet->{value},$target->{score},$target->{value});
209                         }
210                         push @roids,{wave => $i, roids => $roids, xp => $xp};
211                         push @claims,{wave => $i}
212                 }
213                 $target{roids} = \@roids;
214                 $target{claims} = \@claims;
215
216                 push @targets,\%target;
217         }
218         @targets = sort {$b->{roids}[0]{xp} <=> $a->{roids}[0]{xp} or $b->{size} <=> $a->{size}} @targets;
219
220         $c->stash(targets => \@targets);
221 }
222
223 sub edit : Local {
224         my ($self, $c, $raid, $order) = @_;
225         my $dbh = $c->model;
226
227         my $query = $dbh->prepare(q{SELECT id,tick,waves,message,released_coords,open,ftid
228                 FROM raids WHERE id = ?
229         });
230         $raid = $dbh->selectrow_hashref($query,undef,$raid);
231
232         $c->stash(raid => $raid);
233
234         $c->stash(errors => $c->flash->{errors});
235
236         my $groups = $dbh->prepare(q{SELECT g.gid,g.groupname,raid FROM groups g LEFT OUTER JOIN (SELECT gid,raid FROM raid_access WHERE raid = ?) AS ra ON g.gid = ra.gid WHERE g.attack});
237         $groups->execute($raid ? $raid->{id} : undef);
238
239         my @addgroups;
240         my @remgroups;
241         while (my $group = $groups->fetchrow_hashref){
242                 if ($group->{raid}){
243                         push @remgroups,$group;
244                 }else{
245                         push @addgroups,$group;
246                 }
247         }
248         $c->stash(removegroups => \@remgroups);
249         $c->stash(addgroups => \@addgroups);
250
251         if ($order =~ /^(score|size|value|xp)rank$/){
252                 $order .= " ASC";
253         }elsif ($order eq 'race'){
254                 $order .= ' ASC';
255         }else {
256                 $order .= 'p.x,p.y,p.z';
257         }
258
259         my $targetquery = $dbh->prepare(qq{SELECT r.id,coords(x,y,z),comment,size,score,value,race,planet_status AS planetstatus,relationship,comment,r.planet, s.scans
260                 FROM raid_targets r
261                         JOIN current_planet_stats p ON p.id = r.planet
262                         LEFT OUTER JOIN ( SELECT planet, array_accum(s::text) AS scans
263                                 FROM ( SELECT DISTINCT ON (planet,type) planet,scan_id,type, tick
264                                         FROM scans
265                                         WHERE tick > tick() - 24
266                                         ORDER BY planet,type ,tick DESC
267                                         ) s
268                                 GROUP BY planet
269                         ) s ON s.planet = r.planet
270                 WHERE r.raid = ?
271                 ORDER BY $order
272                 });
273         my $claims =  $dbh->prepare(q{ SELECT username,launched FROM raid_claims
274                 NATURAL JOIN users WHERE target = ? AND wave = ?});
275
276         $targetquery->execute($raid->{id});
277         my @targets;
278         while (my $target = $targetquery->fetchrow_hashref){
279                 my @waves;
280                 for my $i (1 .. $raid->{waves}){
281                         $claims->execute($target->{id},$i);
282                         my $claimers;
283                         if ($claims->rows != 0){
284                                 my $owner = 0;
285                                 my @claimers;
286                                 while (my $claim = $claims->fetchrow_hashref){
287                                         $claim->{username} .= '*' if ($claim->{launched});
288                                         push @claimers,$claim->{username};
289                                 }
290                                 $claimers = join '/', @claimers;
291                         }
292                         push @waves,{wave => $i, claimers => $claimers};
293                 }
294                 $target->{waves} = \@waves;
295
296                 $target->{scans} = array_expand $target->{scans};
297                 push @targets,$target;
298         }
299         $c->stash(targets => \@targets);
300
301         $c->forward('/listAlliances');
302 }
303
304 sub postraidupdate : Local {
305         my ($self, $c, $raid) = @_;
306         my $dbh = $c->model;
307
308         $dbh->begin_work;
309         $dbh->do(q{UPDATE raids SET message = ?, tick = ?, waves = ? WHERE id = ?}
310                 ,undef,html_escape $c->req->param('message')
311                 ,$c->req->param('tick'),$c->req->param('waves')
312                 ,$raid);
313
314         $c->forward('log',[$raid, 'BC updated raid']);
315
316         my $groups = $dbh->prepare(q{SELECT gid,groupname FROM groups WHERE attack});
317         my $delgroup = $dbh->prepare(q{DELETE FROM raid_access WHERE raid = ? AND gid = ?});
318         my $addgroup = $dbh->prepare(q{INSERT INTO raid_access (raid,gid) VALUES(?,?)});
319
320         $groups->execute();
321         while (my $group = $groups->fetchrow_hashref){
322                 my $query;
323                 next unless defined $c->req->param($group->{gid});
324                 my $command = $c->req->param($group->{gid});
325                 if ( $command eq 'remove'){
326                         $query = $delgroup;
327                 }elsif($command eq 'add'){
328                         $query = $addgroup;
329                 }
330                 if ($query){
331                         $query->execute($raid,$group->{gid});
332                         $c->forward('log',[$raid, "BC '$command' access for $group->{gid} ($group->{groupname})"]);
333                 }
334         }
335         $dbh->commit;
336
337         $c->res->redirect($c->uri_for('edit',$raid));
338 }
339
340 sub postaddtargets : Local {
341         my ($self, $c, $raid) = @_;
342         my $dbh = $c->model;
343
344         my $sizelimit = $c->req->param('sizelimit');
345         $sizelimit = -1 unless $sizelimit;
346
347         my $targets = $c->req->param('targets');
348         my $addtarget = $dbh->prepare(qq{INSERT INTO raid_targets(raid,planet) (
349                 SELECT ?, id FROM current_planet_stats p
350                 WHERE x = ? AND y = ? AND COALESCE(z = ?,TRUE)
351                 AND p.size > ?
352                 )});
353         my @errors;
354         while ($targets =~ m/(\d+):(\d+)(?::(\d+))?/g){
355                 my ($x,$y,$z) = ($1, $2, $3);
356                 eval {
357                         $addtarget->execute($raid,$1,$2,$3,$sizelimit);
358                 };
359
360                 if ($@ =~ /duplicate key value violates unique constraint "raid_targets_raid_key"/){
361                         if ($z){
362                                 push @errors, "Planet already exists: $x:$y:$z";
363                         }else{
364                                 push @errors, "A planet from $x:$y already exists in the raid,"
365                                         ." either remove it or add the planets separately.";
366                         }
367                 }else {
368                         push @errors, $@;
369                 }
370         }
371         if ($c->req->param('alliance') =~ /^(\d+)$/ && $1 != 1){
372                 my $addtarget = $dbh->prepare(qq{INSERT INTO raid_targets(raid,planet) (
373                         SELECT ?,id FROM current_planet_stats p WHERE alliance_id = ? AND p.size > ?)
374                         });
375                 eval {
376                         $addtarget->execute($raid,$1,$sizelimit);
377                         $c->forward('log',[$raid,"BC adding alliance $1 to raid"]);
378                 };
379                 if ($@ =~ /duplicate key value violates unique constraint "raid_targets_raid_key"/){
380                         push @errors, "A planet from this alliance has already been added to the raid,"
381                                 ." either remove it or add the planets separately.";
382                 }else {
383                         push @errors, $@;
384                 }
385         }
386
387         $c->flash(errors => \@errors) if @errors;
388         $c->res->redirect($c->uri_for('edit',$raid));
389 }
390
391 sub posttargetupdates : Local {
392         my ($self, $c, $raid) = @_;
393         my $dbh = $c->model;
394
395         my @errors;
396         my $comment = $dbh->prepare(q{UPDATE raid_targets SET comment = ? WHERE id = ?});
397         my $unclaim =  $dbh->prepare(q{DELETE FROM raid_claims WHERE target = ? AND wave = ?});
398         my $block = $dbh->prepare(q{INSERT INTO raid_claims (target,uid,wave) VALUES(?,-2,?)});
399         my $claim = $dbh->prepare(q{INSERT INTO raid_claims (target,uid,wave)
400                 VALUES($1,(SELECT uid FROM users WHERE username ILIKE $3),$2)
401                 });
402         my $unblock =  $dbh->prepare(q{DELETE FROM raid_claims
403                 WHERE target = ? AND wave = ? AND uid = -2
404                 });
405         my $remove = $dbh->prepare(q{DELETE FROM raid_targets WHERE raid = ? AND id = ?});
406         for $_ ($c->req->param()){
407                 if (/^comment:(\d+)$/){
408                         $comment->execute(html_escape $c->req->param($_),$1);
409                 }elsif(/^unclaim:(\d+):(\d+)$/){
410                         $unclaim->execute($1,$2);
411                         $c->forward('log',[$raid,"BC unclaimed target $1 wave $2."]);
412                 }elsif(/^block:(\d+):(\d+)$/){
413                         $block->execute($1,$2);
414                         $c->forward('log',[$raid,"BC blocked target $1 wave $2."]);
415                 }elsif(/^claim:(\d+):(\d+)$/){
416                         my $target = $1;
417                         my $wave = $2;
418                         my @claims = split /[, ]+/, $c->req->param($_);
419                         for (@claims){
420                                 eval {
421                                         $claim->execute($target,$wave,$_);
422                                 };
423                                 if ($@ =~ /null value in column "uid"/){
424                                         push @errors, "Could not find user: " . html_escape $_;
425                                 }elsif ($@ =~ /more than one row returned by a subquery/){
426                                         push @errors, "This matched several users, please refine: " . html_escape $_;
427                                 }else {
428                                         push @errors, $@;
429                                 }
430                         }
431                         if(@claims){
432                                 $unblock->execute($target,$wave);
433                                 $c->forward('log',[$raid,"BC claimed target $1 wave $2 for @claims."]);
434                         }
435                 }elsif(/^remove:(\d+)$/){
436                         $remove->execute($raid,$1);
437                         $c->forward('log',[$raid,"BC removed target $1"]);
438                 }
439         }
440
441         $c->flash(errors => \@errors) if @errors;
442         $c->res->redirect($c->uri_for('edit',$raid));
443 }
444
445 sub open : Local {
446         my ($self, $c, $raid) = @_;
447
448         $c->model->do(q{UPDATE raids SET open = TRUE, removed = FALSE WHERE id = ?}
449                 ,undef,$raid);
450         $c->forward('log',[$raid, "BC opened raid"]);
451
452         $c->res->redirect($c->req->referer);
453 }
454
455 sub close : Local {
456         my ($self, $c, $raid) = @_;
457
458         $c->model->do(q{UPDATE raids SET open = FALSE WHERE id = ?}
459                 ,undef,$raid);
460         $c->forward('log',[$raid, "BC closed raid"]);
461
462         $c->res->redirect($c->req->referer);
463 }
464
465 sub remove : Local {
466         my ($self, $c, $raid) = @_;
467
468         $c->model->do(q{UPDATE raids SET open = FALSE, removed = TRUE WHERE id = ?}
469                 ,undef,$raid);
470         $c->forward('log',[$raid, "BC removed raid"]);
471
472         $c->res->redirect($c->req->referer);
473 }
474
475 sub showcoords : Local {
476         my ($self, $c, $raid) = @_;
477
478         $c->model->do(q{UPDATE raids SET released_coords = TRUE WHERE id = ?}
479                 ,undef,$raid);
480         $c->forward('log',[$raid, "BC released coords"]);
481
482         $c->res->redirect($c->req->referer);
483 }
484
485 sub hidecoords : Local {
486         my ($self, $c, $raid) = @_;
487
488         $c->model->do(q{UPDATE raids SET released_coords = FALSE WHERE id = ?}
489                 ,undef,$raid);
490         $c->forward('log',[$raid, "BC hid coords"]);
491
492         $c->res->redirect($c->req->referer);
493 }
494
495 sub create : Local {
496         my ($self, $c) = @_;
497         $c->stash(waves => 3);
498         my @time = gmtime;
499         $c->stash(landingtick => $c->stash->{TICK} + 24 - $time[2] + 12);
500 }
501
502 sub postcreate : Local {
503         my ($self, $c) = @_;
504         my $dbh = $c->model;
505
506         my $query = $dbh->prepare(q{INSERT INTO raids (tick,waves,message) VALUES(?,?,?) RETURNING (id)});
507         $query->execute($c->req->param('tick'),$c->req->param('waves')
508                 ,html_escape $c->req->param('message'));
509         my $raid = $query->fetchrow_array;
510         $c->forward('log',[$raid,"Created raid landing at tick: ".$c->req->param('tick')]);
511         $c->res->redirect($c->uri_for('edit',$raid));
512 }
513
514 sub log : Private {
515         my ($self, $c, $raid, $message) = @_;
516         my $dbh = $c->model;
517
518         my $log = $dbh->prepare(q{INSERT INTO forum_posts (uid,ftid,message)
519                 VALUES($1,(SELECT ftid FROM raids WHERE id = $2),$3)
520                 });
521         $log->execute($c->user->id,$raid,$message);
522 }
523
524 sub findRaid : Private {
525         my ( $self, $c, $raid ) = @_;
526         my $dbh = $c->model;
527
528         my $query = $dbh->prepare(q{SELECT id,tick,waves,message,released_coords
529                 FROM raids WHERE id = ? AND open AND not removed AND id IN
530                         (SELECT raid FROM raid_access NATURAL JOIN groupmembers WHERE uid = ?)
531                 });
532         $raid = $dbh->selectrow_hashref($query,undef,$raid,$c->user->id);
533         $c->stash(raid => $raid);
534 }
535
536 =head1 AUTHOR
537
538 Michael Andreen (harv@ruin.nu)
539
540 =head1 LICENSE
541
542 GPL 2.0, or later.
543
544 =cut
545
546 1;