1 package NDWeb::Controller::Members;
7 use parent 'Catalyst::Controller';
13 NDWeb::Controller::Members - Catalyst Controller
28 sub index : Path : Args(0) {
29 my ( $self, $c, $order ) = @_;
32 $c->stash(error => $c->flash->{error});
34 $c->stash(u => $dbh->selectrow_hashref(q{SELECT pid AS planet,defense_points
35 ,attack_points,scan_points,humor_points
36 , (attack_points+defense_points+scan_points/20)::NUMERIC(5,1) as total_points
37 , sms,rank,hostmask,call_if_needed,sms_note,defprio
38 FROM users_defprio WHERE uid = ?
42 $c->stash(groups => $dbh->selectrow_array(q{SELECT array_agg(groupname)
43 FROM groups g NATURAL JOIN groupmembers gm
48 $c->stash(p => $dbh->selectrow_hashref(q{SELECT pid AS id,x,y,z, ruler, planet,race,
49 size, size_gain, size_gain_day,
50 score,score_gain,score_gain_day,
51 value,value_gain,value_gain_day,
52 xp,xp_gain,xp_gain_day,
53 sizerank,sizerank_gain,sizerank_gain_day,
54 scorerank,scorerank_gain,scorerank_gain_day,
55 valuerank,valuerank_gain,valuerank_gain_day,
56 xprank,xprank_gain,xprank_gain_day
57 from current_planet_stats_full p
59 },undef,$c->user->planet)
62 my $calls = $dbh->prepare(q{
63 SELECT * FROM defcalls
64 WHERE uid = $1 AND landing_tick >= tick()
65 ORDER BY landing_tick DESC
67 $calls->execute($c->user->id);
68 $c->stash(calls => $calls->fetchall_arrayref({}) );
70 $c->stash(fleets => member_fleets($dbh, $c->user->id,$c->user->planet));
72 my $announcements = $dbh->prepare(q{SELECT ft.ftid, u.username,ft.subject,
73 count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread,count(fp.fpid) AS posts,
74 date_trunc('seconds',max(fp.time)::timestamp) as last_post,
75 min(fp.time)::date as posting_date, ft.sticky
76 FROM forum_threads ft JOIN forum_posts fp USING (ftid)
77 JOIN users u ON u.uid = ft.uid
78 LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $1) ftv ON ftv.ftid = ft.ftid
80 GROUP BY ft.ftid, ft.subject,ft.sticky,u.username
81 HAVING count(NULLIF(COALESCE(ft.sticky OR fp.time > ftv.time,TRUE),FALSE)) >= 1
82 ORDER BY sticky DESC,last_post DESC
84 $announcements->execute($c->user->id);
85 $c->stash(announcements => $announcements->fetchall_arrayref({}) );
88 sub posthostupdate : Local {
89 my ( $self, $c ) = @_;
92 $dbh->do(q{UPDATE users SET hostmask = ? WHERE uid = ?
93 },undef, html_escape $c->req->param('hostname'), $c->user->id);
95 $c->res->redirect($c->uri_for(''));
98 sub postsmsupdate : Local {
99 my ( $self, $c ) = @_;
102 my $callme = $c->req->param('callme') || 0;
104 UPDATE users SET sms = $1, call_if_needed = $2, sms_note = $3 WHERE uid = $4
105 },undef, html_escape $c->req->param('sms'),$callme
106 ,$c->req->param('smsnote'), $c->user->id);
108 $c->res->redirect($c->uri_for(''));
111 sub postowncoords : Local {
112 my ( $self, $c ) = @_;
115 if ($c->user->planet){
116 $c->flash(error => 'You already have a planet set.'
117 .' Contact a HC if they need to be changed');
118 }elsif (my ($x,$y,$z) = $c->req->param('planet') =~ m/(\d+)\D+(\d+)\D+(\d+)/){
119 my $planet = $dbh->selectrow_array(q{SELECT planetid($1,$2,$3,TICK())
124 $dbh->do(q{UPDATE users SET pid = ? WHERE uid = ?
125 },undef, $planet , $c->user->id);
129 when (/duplicate key value violates/){
130 $c->flash(error => "The coords $x:$y:$z are already in use. Talk to hc if these are really your coords.")
133 $c->flash(error => $@)
137 $c->flash(error => "No planet at coords: $x:$y:$z");
140 $c->flash(error => $c->req->param('planet') . " are not valid coords.");
143 $c->res->redirect($c->uri_for(''));
146 sub postfleetsupdates : Local {
147 my ( $self, $c ) = @_;
150 my $log = $dbh->prepare(q{INSERT INTO forum_posts (ftid,uid,message) VALUES(
151 (SELECT ftid FROM users WHERE uid = $1),$1,$2)
154 if ($c->req->param('cmd') eq 'Recall Fleets'){
155 my $updatefleets = $dbh->prepare(q{UPDATE launch_confirmations
156 SET back = tick() + (tick() - (landing_tick - eta))
157 WHERE uid = ? AND fid = ? AND back >= tick()+eta
160 for my $param ($c->req->param()){
161 if ($param =~ /^change:(\d+)$/){
162 $updatefleets->execute($c->user->id,$1);
163 $log->execute($c->user->id,"Member recalled fleet $1");
166 }elsif ($c->req->param('cmd') eq 'Change Fleets'){
167 my $updatefleets = $dbh->prepare(q{UPDATE launch_confirmations
168 SET back = ? WHERE uid = ? AND fid = ?});
170 for my $param ($c->req->param()){
171 if ($param =~ /^change:(\d+)$/){
172 my $back = $c->req->param("back:$1");
173 $updatefleets->execute($back,$c->user->id,$1);
174 $log->execute($c->user->id,"Member set fleet $1 to be back tick: $back");
180 $c->res->redirect($c->uri_for(''));
183 sub ircrequest : Local {
184 my ( $self, $c ) = @_;
187 $c->stash(reply => $c->flash->{reply});
188 $c->stash(channels => ['scan','members','def']);
191 sub postircrequest : Local {
192 my ( $self, $c ) = @_;
195 if ($c->req->param('channel')){
196 my $query = $dbh->prepare(q{
197 INSERT INTO irc_requests (uid,channel,message) VALUES($1,$2,$3)
199 $query->execute($c->user->id,$c->req->param('channel'),$c->req->param('message'));
202 $c->flash(reply => "Msg sent to: ".$c->req->param('channel'));
203 $c->res->redirect($c->uri_for('ircrequest'));
205 $c->stash(ircmessage => $c->req->param('message'));
206 $c->go('ircrequest');
211 my ( $self, $c, $order ) = @_;
214 $order //= 'total_points';
215 if ($order ~~ /^((?:defense|attack|total|humor|scan|raid)_points)$/
216 || $order ~~ /^(defprio)$/){
219 $order = 'total_points DESC';
222 my $limit = 'LIMIT 10';
223 $limit = '' if $c->check_user_roles(qw/members_points_nolimit/);
225 my $query = $dbh->prepare(q{
226 SELECT username,defense_points,attack_points
227 ,scan_points,humor_points,defprio
228 ,(attack_points+defense_points+scan_points/20)::NUMERIC(4,0) as total_points
229 , count(NULLIF(rc.launched,FALSE)) AS raid_points
230 FROM users_defprio u LEFT OUTER JOIN raid_claims rc USING (uid)
231 WHERE uid IN (SELECT uid FROM groupmembers WHERE gid = 'M')
232 GROUP BY username,defense_points,attack_points,scan_points,humor_points,defprio
233 ORDER BY } . "$order $limit"
236 $c->stash(members => $query->fetchall_arrayref({}));
240 my ( $self, $c, $order ) = @_;
244 if ($order ~~ /^(scre|value|xp|size|race)$/){
247 $order = 'scorerank';
249 $order .= ',race' if $order eq 'racerank';
251 my $limit = 'LIMIT 10';
252 $limit = '' if $c->check_user_roles(qw/members_points_nolimit/);
254 my ($races) = $dbh->selectrow_array(q{SELECT enum_range(null::race)::text[]});
255 $c->stash(races => $races);
256 my $query = $dbh->prepare(q{
258 ,rank() OVER(ORDER BY score DESC) AS scorerank
259 ,rank() OVER(ORDER BY value DESC) AS valuerank
260 ,rank() OVER(ORDER BY xp DESC) AS xprank
261 ,rank() OVER(ORDER BY size DESC) AS sizerank
262 ,rank() OVER(PARTITION BY race ORDER BY score DESC) AS racerank
264 FROM current_planet_stats
265 WHERE alliance = 'NewDawn'
267 ORDER BY } . "$order $limit");
268 my @race = $c->req->param('race');
269 my %race = map { $_ => 1 } @race;
270 $c->stash(race => \%race);
274 $query->execute(\@race);
275 $c->stash(members => $query->fetchall_arrayref({}));
278 sub addintel : Local {
279 my ( $self, $c, $order ) = @_;
281 $c->stash(intel => $c->flash->{intel});
282 $c->stash(scans => $c->flash->{scans});
283 $c->stash(intelmessage => $c->flash->{intelmessage});
286 sub postintel : Local {
287 my ( $self, $c, $order ) = @_;
289 $c->forward('insertintel');
291 $c->res->redirect($c->uri_for('addintel'));
294 sub postintelmessage : Local {
295 my ( $self, $c, $order ) = @_;
297 unless ($c->req->param('subject')){
298 if ($c->req->param('message') =~ /(.*\w.*)/){
299 $c->req->param(subject => $1);
303 my ($coords,$tick) = $c->model->selectrow_array(q{
304 SELECT coords(x,y,z), tick() FROM current_planet_stats WHERE pid = $1
305 }, undef, $c->user->planet);
307 $c->req->param(message => "[i]Posted by $coords at tick $tick [/i]\n\n" . $c->req->param('message'));
308 $c->forward('/forum/insertThread',[12]);
309 $c->forward('/forum/insertPost',[$c->stash->{thread}]);
310 $c->flash(intelmessage => 1);
312 $c->forward('insertintel');
314 $c->res->redirect($c->uri_for('addintel'));
317 sub insertintel : Private {
318 my ( $self, $c, $order ) = @_;
322 my $findscan = $dbh->prepare(q{SELECT scan_id FROM scans
323 WHERE scan_id = LOWER(?) AND tick >= tick() - 168 AND groupscan = ?
325 my $addscan = $dbh->prepare(q{INSERT INTO scans (scan_id,tick,uid,groupscan)
326 VALUES (LOWER(?),tick(),?,?)
328 my $addpoint = $dbh->prepare(q{UPDATE users SET scan_points = scan_points + 1
332 my $intel = $c->req->param('message');
333 while ($intel =~ m{http://[\w.]+/.+?scan(_id|_grp)?=(\w+)}g){
334 my $groupscan = (defined $1 && $1 eq '_grp') || 0;
337 $scan{group} = $groupscan;
338 $findscan->execute($2,$groupscan);
339 if ($findscan->rows == 0){
340 if ($addscan->execute($2,$c->user->id,$groupscan)){
341 $addpoint->execute($c->user->id) unless $groupscan;
345 $scan{message} = 'already exists';
349 my $tick = $c->req->param('tick');
350 unless ($tick =~ /^(\d+)$/){
351 $tick = $c->stash->{game}->{tick};
353 my $addintel = $dbh->prepare(q{INSERT INTO intel
354 (name,mission,tick,target,sender,eta,amount,ingal,back,uid)
355 VALUES($1,$2,$3,planetid($4,$5,$6,$10),planetid($7,$8,$9,$10)
356 ,$11,$12,$13,$14,$15)
359 while ($intel =~ m/(\d+):(\d+):(\d+)\*?\s+(\d+):(\d+):(\d+)
360 \*?\s+(.+)(?:Ter|Cat|Xan|Zik|Etd)?
361 \s+(\d+)\s+(Attack|Defend)\s+(\d+)/gx){
362 my $ingal = ($1 == $4 && $2 == $5) || 0;
363 my $lt = $tick + $10;
364 my $back = ($ingal ? $lt + 4 : undef);
366 $addintel->execute($7,$9,$lt,$1,$2,$3,$4,$5,$6,$tick,$10,$8
367 ,$ingal,$back, $c->user->id);
368 push @intel,"Added $&";
371 push @intel,"Couldn't add $&: ".$dbh->errstr;
375 $c->flash(intel => \@intel);
376 $c->flash(scans => \@scans);
379 sub addincs : Local {
380 my ( $self, $c ) = @_;
381 $c->stash(incs => $c->flash->{incs});
385 sub postincs : Local {
386 my ( $self, $c ) = @_;
391 my $user = $dbh->prepare(q{
392 SELECT uid FROM users u
393 WHERE pid = planetid($1,$2,$3,tick())
394 AND uid IN (SELECT uid FROM groupmembers WHERE gid = 'M')
396 my $call = $dbh->prepare(q{
398 FROM calls WHERE uid = $1 AND landing_tick = tick() + $2
400 my $fleet = $dbh->prepare(q{
403 WHERE pid = planetid($1,$2,$3,tick()) AND amount = $4 and fleet = $5 AND call = $6
405 my $irc = $dbh->prepare(q{
406 INSERT INTO irc_requests (uid,channel,message) VALUES($1,'def',$2)
409 my $msg = $c->req->param('message');
410 while ($msg =~ /expand\s+(\d+):(\d+):(\d+)\*?\s+(\d+):(\d+):(\d+)\s+([^:]*\S+)\s+(?:Ter|Cat|Xan|Zik|Etd)\s+([\d,]+)\s+(\d+)/gc
411 || $msg =~ /(\d+):(\d+):(\d+)\s+(\d+):(\d+):(\d+)\s+\((?:Ter|Cat|Xan|Zik|Etd)\)\s+([^,]*\S+)\s+([\d,]+)\s+(\d+)\s+\(\d+\)/gc){
413 my $inc = {message => $&};
419 my $uid = $dbh->selectrow_array($user,undef,$1,$2,$3);
420 die '<i>No user with these coords</i>' unless $uid;
422 my $call = $dbh->selectrow_array($call,undef,$uid,$9);
424 my $pid = $dbh->selectrow_hashref($fleet,undef,$4,$5,$6,$amount,$7,$call);
425 die '<i>Duplicate</i>' if $pid;
429 $irc->execute($c->user->id, $inc->{message});
430 $inc->{status} = '<b>Added</b>';
433 when (m(^(<i>.*</i>) at )){
443 $c->signal_bots if @incs;
444 $c->flash(incs => \@incs);
445 $c->res->redirect($c->uri_for('addincs'));
448 sub launchConfirmation : Local {
449 my ( $self, $c ) = @_;
451 $c->stash(error => $c->flash->{error});
452 $c->stash(missions => $c->flash->{missions});
455 sub postconfirmation : Local {
456 my ( $self, $c ) = @_;
460 my $findplanet = $dbh->prepare(q{SELECT planetid(?,?,?,tick())});
461 my $addfleet = $dbh->prepare(q{INSERT INTO fleets
462 (name,mission,pid,tick,amount)
463 VALUES ($2,$3,(SELECT pid FROM users WHERE uid = $1),tick(),$4)
466 my $updatefleet = $dbh->prepare(q{
467 UPDATE launch_confirmations SET back = $2 WHERE fid = $1
469 my $addconfirmation = $dbh->prepare(q{INSERT INTO launch_confirmations
470 (fid,uid,pid,landing_tick,eta,back,num) VALUES ($1,$2,$3,$4,$5,$6,$7)
472 my $addships = $dbh->prepare(q{INSERT INTO fleet_ships (fid,ship,amount)
475 my $log = $dbh->prepare(q{INSERT INTO forum_posts (ftid,uid,message) VALUES(
476 (SELECT ftid FROM users WHERE uid = $1),$1,$2)
478 my $return = $dbh->prepare(q{
479 UPDATE launch_confirmations SET back = tick()
480 WHERE uid = $1 AND num = $2 AND back > tick()
482 my $fullfleet = $dbh->prepare(q{INSERT INTO full_fleets
483 (fid,uid) VALUES (?,?)});
485 my @missions = parseconfirmations($c->req->param('mission'), $c->stash->{TICK});
486 for my $m (@missions){
487 if ($m->{mission} eq 'Return'){
488 $c->forward("addReturnFleet", [$m]);
490 $updatefleet->execute($m->{fid},$m->{back});
493 $m->{pid} = $c->user->planet;
495 }elsif ($m->{target} =~ /^(\d+):(\d+):(\d+)$/) {
496 $m->{pid} = $dbh->selectrow_array($findplanet,undef,$1,$2,$3);
498 $m->{warning} = "No planet at $m->{target}, try again next tick.";
503 #Recall fleets with same slot number
504 $return->execute($c->user->id,$m->{num});
506 unless ($m->{mission}){
507 $m->{warning} = "Not on a mission, but matching fleets recalled";
511 $c->forward("findDuplicateFleet", [$m]);
513 $m->{warning} = "Already confirmed this fleet, updating changed information";
514 $updatefleet->execute($m->{fid},$m->{back}) if $m->{pid};
519 $m->{fleet} = $dbh->selectrow_array($addfleet,undef,$c->user->id,$m->{name}
520 ,$m->{mission},$m->{amount});
522 if ($m->{mission} eq 'Full fleet'){
523 $fullfleet->execute($m->{fleet},$c->user->id);
525 $addconfirmation->execute($m->{fleet},$c->user->id,$m->{pid},$m->{tick},$m->{eta},$m->{back},$m->{num});
528 if ($m->{mission} eq 'Attack'){
529 $c->forward("addAttackFleet", [$m]);
530 }elsif ($m->{mission} eq 'Defend'){
531 $c->forward("addDefendFleet", [$m]);
534 for my $ship (@{$m->{ships}}){
535 $addships->execute($m->{fleet},$ship->{ship},$ship->{amount});
537 $log->execute($c->user->id,"Pasted confirmation for $m->{mission} mission to $m->{target}, landing tick $m->{tick}");
539 $c->flash(missions => \@missions);
544 when (/insert or update on table "fleet_ships" violates foreign key constraint "fleet_ships_ship_fkey"\s+DETAIL:\s+Key \(ship\)=\(([^)]+)\)/){
545 $c->flash( error => "'$1' is NOT a valid ship");
548 $c->flash( error => $_);
551 $c->res->redirect($c->uri_for('launchConfirmation'));
554 sub parseconfirmations {
555 my ( $missions, $tick ) = @_;
556 return unless $missions;
560 Ships \s?\t Cla \s?\t T1 \s?\t T2 \s?\t T3 \s?\t Base\ \(i\) \s (?<name>.+?)\ \(i\) \s?\t (?<name>.+?)\ \(i\) \s?\t (?<name>.+?)\ \(i\) \s?\t TOTAL \W+
562 \QTotal Ships in Fleet\E \s?\t (\d+) \s?\t (?<amount>\d+) \s?\t (?<amount>\d+) \s?\t (?<amount>\d+) \W+
563 Mission: \t (?<mission>\w*) \t (?<mission>\w*) \t (?<mission>\w*) \W+
564 Target: \t (?<targets>((\d+:\d+:\d+)?\t)*) \W+
565 \QLaunch Tick:\E \t (?<lts>(\d*\t)*) \W+
566 ETA: \t? (?<etas>([^\t]+\t?)*)
569 my @etas = split /\t/, $+{etas};
570 my @targets = split /\t/, $+{targets};
571 my @lts = split /\t/, $+{lts};
574 name => $match{name}->[$i],
575 mission => $match{mission}->[$i],
576 amount => $match{amount}->[$i],
580 if ($mission{amount} == 0){
581 push @missions,\%mission;
585 $mission{target} = shift @targets;
586 $mission{lt} = shift @lts;
588 when(/^(\d+) (\s+ \(\+\d+\))? \W+
590 \QReturn ETA: \E(Instant|\d+)/sx){
592 $mission{eta} = $1 + $4;
593 $mission{back} = $3 + $mission{eta} - 1;
602 push @missions,\%mission;
607 mission => 'Full fleet',
612 while ($match{ships}->[0] =~ m/((?:\w+ )*\w+)\s+(FI|CO|FR|DE|CR|BS)[^\d]+([\d\s]+)/g){
614 my @amounts = split /\D+/, $3;
615 my $amount = shift @amounts;
616 die "Ships don't sum up properly" if $amounts[3] != $amount + $amounts[0] + $amounts[1] + $amounts[2];
618 push @{$missions[$i]->{ships}},{ship => $ship, amount => $amounts[$i]} if $amounts[$i] > 0;
620 $missions[3]->{amount} += $amounts[3];
626 sub findDuplicateFleet : Private {
627 my ( $self, $c, $m ) = @_;
630 my $findfleet = $dbh->prepare(q{
631 SELECT fid FROM fleets f
632 LEFT JOIN launch_confirmations lc USING (fid)
633 WHERE f.pid = (SELECT pid FROM users WHERE uid = $1) AND mission = $3 AND amount = $4 AND
634 COALESCE(uid = $1 AND num = $2 AND lc.pid = $5 AND landing_tick = $6, TRUE)
636 my $fid = $dbh->selectrow_array($findfleet,undef,$c->user->id,$m->{num}
637 ,$m->{mission},$m->{amount}, $m->{pid}, $m->{tick});
638 $c->forward("matchShips", [$m,$fid]);
639 $m->{fid} = $fid if $m->{match};
642 sub addAttackFleet : Private {
643 my ( $self, $c, $m ) = @_;
646 my $findattacktarget = $dbh->prepare(q{
647 SELECT c.target,c.wave,c.launched
649 JOIN raid_targets t ON c.target = t.id
650 JOIN raids r ON t.raid = r.id
651 WHERE c.uid = ? AND r.tick+c.wave-1 = ? AND t.pid = ?
652 AND r.open AND not r.removed
654 my $launchedtarget = $dbh->prepare(q{
655 UPDATE raid_claims SET launched = TRUE
656 WHERE uid = ? AND target = ? AND wave = ?
658 my $claim = $dbh->selectrow_hashref($findattacktarget,undef,$c->user->id,$m->{tick},$m->{pid});
659 if ($claim->{launched}){
660 $m->{warning} = "Already launched on this target:$claim->{target},$claim->{wave},$claim->{launched}";
661 }elsif(defined $claim->{launched}){
662 $launchedtarget->execute($c->user->id,$claim->{target},$claim->{wave});
663 $m->{warning} = "OK:$claim->{target},$claim->{wave},$claim->{launched}";
665 $m->{warning} = "You haven't claimed this target";
669 sub addDefendFleet : Private {
670 my ( $self, $c, $m ) = @_;
673 my $finddefensetarget = $dbh->prepare(q{
674 SELECT call FROM calls c
675 JOIN users u USING (uid)
676 WHERE u.pid = $1 AND c.landing_tick = $2
678 my $informDefChannel = $dbh->prepare(q{
679 INSERT INTO defense_missions (fleet,call) VALUES (?,?)
681 my $call = $dbh->selectrow_hashref($finddefensetarget,undef,$m->{pid},$m->{tick});
683 $informDefChannel->execute($m->{fleet},$call->{call});
685 $m->{warning} = "No call for $m->{target} landing tick $m->{tick}";
689 sub addReturnFleet : Private {
690 my ( $self, $c, $m ) = @_;
693 my $findfleet = $dbh->prepare(q{
694 SELECT fid FROM fleets f
695 JOIN launch_confirmations lc USING (fid)
696 WHERE uid = $1 AND num = $2 AND amount = $3
699 my $fid = $dbh->selectrow_array($findfleet,undef,$c->user->id,$m->{num}
700 ,$m->{amount}, $m->{tick});
701 $c->forward("matchShips", [$m,$fid]);
704 $m->{warning} = "Return fleet, changed back tick to match the return eta.";
706 $m->{warning} = "Couldn't find a fleet matching this returning fleet, so adding a new fleet that is returning";
710 sub matchShips : Private {
711 my ( $self, $c, $m, $fid ) = @_;
715 my $ships = $dbh->prepare(q{
716 SELECT ship, amount FROM fleet_ships WHERE fid = $1 ORDER BY num
718 $ships->execute($fid);
719 for my $s (@{$m->{ships}}){
720 my $s2 = $ships->fetchrow_hashref;
721 return unless $s->{ship} eq $s2->{ship} && $s->{amount} == $s2->{amount};
727 sub defenders : Local {
728 my ( $self, $c, $order ) = @_;
731 my $defenders = $dbh->prepare(q{
732 SELECT uid,pid AS planet,username, to_char(NOW() AT TIME ZONE timezone,'HH24:MI') AS time
733 ,sms_note, call_if_needed, race, timezone
735 JOIN current_planet_stats p USING (pid)
736 WHERE uid IN (SELECT uid FROM groupmembers WHERE gid = 'M')
737 ORDER BY call_if_needed DESC, username
741 my $available = $dbh->prepare(q{
742 SELECT ship,amount FROM available_ships WHERE pid = $1
746 while (my $member = $defenders->fetchrow_hashref){
748 $member->{fleets} = member_fleets($dbh, $member->{uid}, $member->{planet});
749 $available->execute($member->{planet});
750 my $fleet = {fid => $member->{username}, mission => 'Available', name => 'At home'
751 , ships => $available->fetchall_arrayref({})
753 push @{$member->{fleets}}, $fleet;
754 push @members,$member;
756 $c->stash(members => \@members);
760 my ( $dbh, $uid, $planet ) = @_;
762 my $query = $dbh->prepare(q{
764 SELECT DISTINCT ON (mission,name) fid,name,tick, NULL AS eta
765 ,amount, NULL AS coords, pid AS target, NULL AS back
766 ,NULL AS recalled, mission
768 WHERE pid = $2 AND tick <= tick() AND tick >= tick() - 24
769 AND name IN ('Main','Advanced Unit') AND mission = 'Full fleet'
770 ORDER BY mission,name,tick DESC, fid DESC
772 SELECT fid,name,landing_tick AS tick, eta, amount
773 , coords(x,y,z), lc.pid AS target, back
774 , (back <> landing_tick + eta - 1) AS recalled
775 ,CASE WHEN landing_tick <= tick() OR (back <> landing_tick + eta - 1)
776 THEN 'Returning' ELSE mission END AS mission
777 FROM launch_confirmations lc
778 LEFT OUTER JOIN current_planet_stats t USING (pid)
779 JOIN fleets f USING (fid)
780 WHERE uid = $1 AND f.pid = $2 AND back > tick()
781 AND landing_tick - eta - 12 < tick()
785 my $ships = $dbh->prepare(q{SELECT ship,amount FROM fleet_ships
786 WHERE fid = ? ORDER BY num
789 $query->execute($uid,$planet);
791 while (my $fleet = $query->fetchrow_hashref){
793 $ships->execute($fleet->{fid});
794 while (my $ship = $ships->fetchrow_hashref){
797 $fleet->{ships} = \@ships;
805 Michael Andreen (harv@ruin.nu)