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({}) );
87 my ($attackgroups) = $dbh->selectrow_array(q{
88 SELECT array_agg(gid) FROM groupmembers WHERE gid IN ('x','y','z') AND uid = $1
89 }, undef, $c->user->id);
90 $c->stash(attackgroups => $attackgroups);
94 sub posthostupdate : Local {
95 my ( $self, $c ) = @_;
98 $dbh->do(q{UPDATE users SET hostmask = ? WHERE uid = ?
99 },undef, html_escape $c->req->param('hostname'), $c->user->id);
101 $c->res->redirect($c->uri_for(''));
104 sub postattackgroups : Local {
105 my ( $self, $c ) = @_;
108 my @groups = $c->req->param('class');
109 $dbh->do(q{DELETE FROM groupmembers WHERE gid IN ('x','y','z') AND gid <> ALL($1) AND uid = $2
110 },undef, \@groups, $c->user->id);
112 $dbh->do(q{INSERT INTO groupmembers (uid,gid) (
113 SELECT $2, gid FROM unnest($1::text[]) AS gid WHERE gid IN ('x','y','z')
115 SELECT uid,gid FROM groupmembers WHERE uid = $2
116 )},undef, \@groups, $c->user->id);
118 $c->res->redirect($c->uri_for(''));
121 sub postsmsupdate : Local {
122 my ( $self, $c ) = @_;
125 my $callme = $c->req->param('callme') || 0;
127 UPDATE users SET sms = $1, call_if_needed = $2, sms_note = $3 WHERE uid = $4
128 },undef, html_escape $c->req->param('sms'),$callme
129 ,$c->req->param('smsnote'), $c->user->id);
131 $c->res->redirect($c->uri_for(''));
134 sub postowncoords : Local {
135 my ( $self, $c ) = @_;
138 if ($c->user->planet){
139 $c->flash(error => 'You already have a planet set.'
140 .' Contact a HC if they need to be changed');
141 }elsif (my ($x,$y,$z) = $c->req->param('planet') =~ m/(\d+)\D+(\d+)\D+(\d+)/){
142 my $planet = $dbh->selectrow_array(q{SELECT planetid($1,$2,$3,TICK())
147 $dbh->do(q{UPDATE users SET pid = ? WHERE uid = ?
148 },undef, $planet , $c->user->id);
152 when (/duplicate key value violates/){
153 $c->flash(error => "The coords $x:$y:$z are already in use. Talk to hc if these are really your coords.")
156 $c->flash(error => $@)
160 $c->flash(error => "No planet at coords: $x:$y:$z");
163 $c->flash(error => $c->req->param('planet') . " are not valid coords.");
166 $c->res->redirect($c->uri_for(''));
169 sub postfleetsupdates : Local {
170 my ( $self, $c ) = @_;
173 my $log = $dbh->prepare(q{INSERT INTO forum_posts (ftid,uid,message) VALUES(
174 (SELECT ftid FROM users WHERE uid = $1),$1,$2)
177 if ($c->req->param('cmd') eq 'Recall Fleets'){
178 my $updatefleets = $dbh->prepare(q{UPDATE launch_confirmations
179 SET back = tick() + (tick() - (landing_tick - eta))
180 WHERE uid = ? AND fid = ? AND back >= tick()+eta
183 for my $param ($c->req->param()){
184 if ($param =~ /^change:(\d+)$/){
185 $updatefleets->execute($c->user->id,$1);
186 $log->execute($c->user->id,"Member recalled fleet $1");
189 }elsif ($c->req->param('cmd') eq 'Change Fleets'){
190 my $updatefleets = $dbh->prepare(q{UPDATE launch_confirmations
191 SET back = ? WHERE uid = ? AND fid = ?});
193 for my $param ($c->req->param()){
194 if ($param =~ /^change:(\d+)$/){
195 my $back = $c->req->param("back:$1");
196 $updatefleets->execute($back,$c->user->id,$1);
197 $log->execute($c->user->id,"Member set fleet $1 to be back tick: $back");
203 $c->res->redirect($c->uri_for(''));
206 sub ircrequest : Local {
207 my ( $self, $c ) = @_;
210 $c->stash(reply => $c->flash->{reply});
211 $c->stash(channels => ['scan','members','def']);
214 sub postircrequest : Local {
215 my ( $self, $c ) = @_;
218 if ($c->req->param('channel')){
219 my $query = $dbh->prepare(q{
220 INSERT INTO irc_requests (uid,channel,message) VALUES($1,$2,$3)
222 $query->execute($c->user->id,$c->req->param('channel'),$c->req->param('message'));
225 $c->flash(reply => "Msg sent to: ".$c->req->param('channel'));
226 $c->res->redirect($c->uri_for('ircrequest'));
228 $c->stash(ircmessage => $c->req->param('message'));
229 $c->go('ircrequest');
234 my ( $self, $c, $order ) = @_;
237 $order //= 'total_points';
238 if ($order ~~ /^((?:defense|attack|total|humor|scan|raid)_points)$/
239 || $order ~~ /^(defprio)$/){
242 $order = 'total_points DESC';
245 my $limit = 'LIMIT 10';
246 $limit = '' if $c->check_user_roles(qw/members_points_nolimit/);
248 my $query = $dbh->prepare(q{
249 SELECT username,defense_points,attack_points
250 ,scan_points,humor_points,defprio
251 ,(attack_points+defense_points+scan_points/20)::NUMERIC(4,0) as total_points
252 , count(NULLIF(rc.launched,FALSE)) AS raid_points
253 FROM users_defprio u LEFT OUTER JOIN raid_claims rc USING (uid)
254 WHERE uid IN (SELECT uid FROM groupmembers WHERE gid = 'M')
255 GROUP BY username,defense_points,attack_points,scan_points,humor_points,defprio
256 ORDER BY } . "$order $limit"
259 $c->stash(members => $query->fetchall_arrayref({}));
263 my ( $self, $c, $order ) = @_;
267 if ($order ~~ /^(scre|value|xp|size|race)$/){
270 $order = 'scorerank';
272 $order .= ',race' if $order eq 'racerank';
274 my $limit = 'LIMIT 10';
275 $limit = '' if $c->check_user_roles(qw/members_points_nolimit/);
277 my ($races) = $dbh->selectrow_array(q{SELECT enum_range(null::race)::text[]});
278 $c->stash(races => $races);
279 my $query = $dbh->prepare(q{
281 ,rank() OVER(ORDER BY score DESC) AS scorerank
282 ,rank() OVER(ORDER BY value DESC) AS valuerank
283 ,rank() OVER(ORDER BY xp DESC) AS xprank
284 ,rank() OVER(ORDER BY size DESC) AS sizerank
285 ,rank() OVER(PARTITION BY race ORDER BY score DESC) AS racerank
287 FROM current_planet_stats
288 WHERE alliance = 'NewDawn'
290 ORDER BY } . "$order $limit");
291 my @race = $c->req->param('race');
292 my %race = map { $_ => 1 } @race;
293 $c->stash(race => \%race);
297 $query->execute(\@race);
298 $c->stash(members => $query->fetchall_arrayref({}));
301 sub addintel : Local {
302 my ( $self, $c, $order ) = @_;
304 $c->stash(intel => $c->flash->{intel});
305 $c->stash(scans => $c->flash->{scans});
306 $c->stash(intelmessage => $c->flash->{intelmessage});
309 sub postintel : Local {
310 my ( $self, $c, $order ) = @_;
312 $c->forward('insertintel');
314 $c->res->redirect($c->uri_for('addintel'));
317 sub postintelmessage : Local {
318 my ( $self, $c, $order ) = @_;
320 unless ($c->req->param('subject')){
321 if ($c->req->param('message') =~ /(.*\w.*)/){
322 $c->req->param(subject => $1);
326 my ($coords,$tick) = $c->model->selectrow_array(q{
327 SELECT coords(x,y,z), tick() FROM current_planet_stats WHERE pid = $1
328 }, undef, $c->user->planet);
330 $c->req->param(message => "[i]Posted by $coords at tick $tick [/i]\n\n" . $c->req->param('message'));
331 $c->forward('/forum/insertThread',[12]);
332 $c->forward('/forum/insertPost',[$c->stash->{thread}]);
333 $c->flash(intelmessage => 1);
335 $c->forward('insertintel');
337 $c->res->redirect($c->uri_for('addintel'));
340 sub insertintel : Private {
341 my ( $self, $c, $order ) = @_;
345 my $findscan = $dbh->prepare(q{SELECT scan_id FROM scans
346 WHERE scan_id = LOWER(?) AND tick >= tick() - 168 AND groupscan = ?
348 my $addscan = $dbh->prepare(q{INSERT INTO scans (scan_id,tick,uid,groupscan)
349 VALUES (LOWER(?),tick(),?,?)
351 my $addpoint = $dbh->prepare(q{UPDATE users SET scan_points = scan_points + 1
355 my $intel = $c->req->param('message');
356 while ($intel =~ m{http://[\w.]+/.+?scan(_id|_grp)?=(\w+)}g){
357 my $groupscan = (defined $1 && $1 eq '_grp') || 0;
360 $scan{group} = $groupscan;
361 $findscan->execute($2,$groupscan);
362 if ($findscan->rows == 0){
363 if ($addscan->execute($2,$c->user->id,$groupscan)){
364 $addpoint->execute($c->user->id) unless $groupscan;
368 $scan{message} = 'already exists';
372 my $tick = $c->req->param('tick');
373 unless ($tick =~ /^(\d+)$/){
374 $tick = $c->stash->{game}->{tick};
376 my $addintel = $dbh->prepare(q{INSERT INTO intel
377 (name,mission,tick,target,sender,eta,amount,ingal,back,uid)
378 VALUES($1,$2,$3,planetid($4,$5,$6,$10),planetid($7,$8,$9,$10)
379 ,$11,$12,$13,$14,$15)
382 while ($intel =~ m/(\d+):(\d+):(\d+)\*?\s+(\d+):(\d+):(\d+)
383 \*?\s+(.+)(?:Ter|Cat|Xan|Zik|Etd)?
384 \s+(\d+)\s+(Attack|Defend)\s+(\d+)/gx){
385 my $ingal = ($1 == $4 && $2 == $5) || 0;
386 my $lt = $tick + $10;
387 my $back = ($ingal ? $lt + 4 : undef);
389 $addintel->execute($7,$9,$lt,$1,$2,$3,$4,$5,$6,$tick,$10,$8
390 ,$ingal,$back, $c->user->id);
391 push @intel,"Added $&";
394 push @intel,"Couldn't add $&: ".$dbh->errstr;
398 $c->flash(intel => \@intel);
399 $c->flash(scans => \@scans);
402 sub addincs : Local {
403 my ( $self, $c ) = @_;
404 $c->stash(incs => $c->flash->{incs});
408 sub postincs : Local {
409 my ( $self, $c ) = @_;
414 my $user = $dbh->prepare(q{
415 SELECT uid FROM users u
416 WHERE pid = planetid($1,$2,$3,tick())
417 AND uid IN (SELECT uid FROM groupmembers WHERE gid = 'M')
419 my $call = $dbh->prepare(q{
421 FROM calls WHERE uid = $1 AND landing_tick = tick() + $2
423 my $fleet = $dbh->prepare(q{
426 WHERE pid = planetid($1,$2,$3,tick()) AND amount = $4 and fleet = $5 AND call = $6
428 my $irc = $dbh->prepare(q{
429 INSERT INTO irc_requests (uid,channel,message) VALUES($1,'def',$2)
432 my $msg = $c->req->param('message');
433 while ($msg =~ /expand\s+(\d+):(\d+):(\d+)\*?\s+(\d+):(\d+):(\d+)\s+([^:]*\S+)\s+(?:Ter|Cat|Xan|Zik|Etd)\s+([\d,]+)\s+(\d+)/gc
434 || $msg =~ /(\d+):(\d+):(\d+)\s+(\d+):(\d+):(\d+)\s+\((?:Ter|Cat|Xan|Zik|Etd)\)\s+([^,]*\S+)\s+([\d,]+)\s+(\d+)\s+\(\d+\)/gc){
436 my $inc = {message => $&};
442 my $uid = $dbh->selectrow_array($user,undef,$1,$2,$3);
443 die '<i>No user with these coords</i>' unless $uid;
445 my $call = $dbh->selectrow_array($call,undef,$uid,$9);
447 my $pid = $dbh->selectrow_hashref($fleet,undef,$4,$5,$6,$amount,$7,$call);
448 die '<i>Duplicate</i>' if $pid;
452 $irc->execute($c->user->id, $inc->{message});
453 $inc->{status} = '<b>Added</b>';
456 when (m(^(<i>.*</i>) at )){
466 $c->signal_bots if @incs;
467 $c->flash(incs => \@incs);
468 $c->res->redirect($c->uri_for('addincs'));
471 sub launchConfirmation : Local {
472 my ( $self, $c ) = @_;
474 $c->stash(error => $c->flash->{error});
475 $c->stash(missions => $c->flash->{missions});
478 sub postconfirmation : Local {
479 my ( $self, $c ) = @_;
483 my $findplanet = $dbh->prepare(q{SELECT planetid(?,?,?,tick())});
484 my $addfleet = $dbh->prepare(q{INSERT INTO fleets
485 (name,mission,pid,tick,amount)
486 VALUES ($2,$3,(SELECT pid FROM users WHERE uid = $1),tick(),$4)
489 my $updatefleet = $dbh->prepare(q{
490 UPDATE launch_confirmations SET back = $2 WHERE fid = $1
492 my $addconfirmation = $dbh->prepare(q{INSERT INTO launch_confirmations
493 (fid,uid,pid,landing_tick,eta,back,num) VALUES ($1,$2,$3,$4,$5,$6,$7)
495 my $addships = $dbh->prepare(q{INSERT INTO fleet_ships (fid,ship,amount)
498 my $log = $dbh->prepare(q{INSERT INTO forum_posts (ftid,uid,message) VALUES(
499 (SELECT ftid FROM users WHERE uid = $1),$1,$2)
501 my $return = $dbh->prepare(q{
502 UPDATE launch_confirmations SET back = tick()
503 WHERE uid = $1 AND num = $2 AND back > tick()
505 my $fullfleet = $dbh->prepare(q{INSERT INTO full_fleets
506 (fid,uid) VALUES (?,?)});
508 my @missions = parseconfirmations($c->req->param('mission'), $c->stash->{TICK});
509 for my $m (@missions){
510 if ($m->{mission} eq 'Return'){
511 $c->forward("addReturnFleet", [$m]);
513 $updatefleet->execute($m->{fid},$m->{back});
516 $m->{pid} = $c->user->planet;
518 }elsif ($m->{target} =~ /^(\d+):(\d+):(\d+)$/) {
519 $m->{pid} = $dbh->selectrow_array($findplanet,undef,$1,$2,$3);
521 $m->{warning} = "No planet at $m->{target}, try again next tick.";
526 #Recall fleets with same slot number
527 $return->execute($c->user->id,$m->{num});
529 unless ($m->{mission}){
530 $m->{warning} = "Not on a mission, but matching fleets recalled";
534 $c->forward("findDuplicateFleet", [$m]);
536 $m->{warning} = "Already confirmed this fleet, updating changed information";
537 $updatefleet->execute($m->{fid},$m->{back}) if $m->{pid};
542 $m->{fleet} = $dbh->selectrow_array($addfleet,undef,$c->user->id,$m->{name}
543 ,$m->{mission},$m->{amount});
545 if ($m->{mission} eq 'Full fleet'){
546 $fullfleet->execute($m->{fleet},$c->user->id);
548 $addconfirmation->execute($m->{fleet},$c->user->id,$m->{pid},$m->{tick},$m->{eta},$m->{back},$m->{num});
551 if ($m->{mission} eq 'Attack'){
552 $c->forward("addAttackFleet", [$m]);
553 }elsif ($m->{mission} eq 'Defend'){
554 $c->forward("addDefendFleet", [$m]);
557 for my $ship (@{$m->{ships}}){
558 $addships->execute($m->{fleet},$ship->{ship},$ship->{amount});
560 $log->execute($c->user->id,"Pasted confirmation for $m->{mission} mission to $m->{target}, landing tick $m->{tick}");
562 $c->flash(missions => \@missions);
567 when (/insert or update on table "fleet_ships" violates foreign key constraint "fleet_ships_ship_fkey"\s+DETAIL:\s+Key \(ship\)=\(([^)]+)\)/){
568 $c->flash( error => "'$1' is NOT a valid ship");
571 $c->flash( error => $_);
574 $c->res->redirect($c->uri_for('launchConfirmation'));
577 sub parseconfirmations {
578 my ( $missions, $tick ) = @_;
579 return unless $missions;
581 $missions =~ s/\s?,\s?//g;
582 $missions =~ s/\s*([:+])\s*/$1/g;
583 $missions =~ s/\(\s/(/g;
584 $missions =~ s/\s\)/)/g;
585 my $returnetare = qr/(\d+) \s+
587 my $missionetare = qr/(\d+) (\s+ \(\+\d+\))? \s+
589 \QReturn ETA:\E\s*(?:(?<eta>Instant) \s+ Cancel \s+ Order
590 | (?<eta>\d+) \s+ Ticks \s+ Recall \s+ Fleet)/sx;
591 my $etare = qr/(Galaxy:\d+Universe:\d+(?:Alliance:\d+)?
594 my $missre = qr/((?:Fake\ )?\w+)\s*/x;
596 Ships \s+ Cla \s+ T\s?1 \s+ T\s?2 \s+ T\s?3 \s+ Base \s+ \(i\) \s (?<name>.+?) \s+ \(i\) \s+ (?<name>.+?) \s+ \(i\) \s+ (?<name>.+?) \s+ \(i\) \s+ TOTAL \s+
598 \QTotal Ships in Fleet\E \s+ (\d+) \s+ (?<amount>\d+) \s+ (?<amount>\d+) \s+ (?<amount>\d+) \s+
599 Mission: \s* (?<missions>(?:$missre)*) \s*
600 Target: \s* (?<targets>((\d+:\d+:\d+)?\s)*) \s*
601 \QLaunch Tick:\E \s* (?<lts>(\d+\s+)*) \s*
602 ETA: \s* (?<etas>(?:$etare)*)
605 my @targets = split /\s+/, $+{targets};
606 my @lts = split /\s+/, $+{lts};
619 name => $match{name}->[$i],
621 amount => $match{amount}->[$i],
625 if ($mission{amount} == 0){
626 push @slots,\%mission;
631 when(/$missionetare/sx){
633 $mission{eta} = $1 + $+{eta};
634 $mission{back} = $3 + $mission{eta} - 1;
635 $mission{target} = shift @targets;
636 $mission{lt} = shift @lts;
637 $mission{mission} = shift @missions;
639 when(/$returnetare/sx){
643 $mission{target} = shift @targets;
644 $mission{lt} = shift @lts;
645 $mission{mission} = shift @missions;
646 die 'Did you forget some at the end?' if $mission{mission} ne 'Return';
649 push @slots,\%mission;
654 mission => 'Full fleet',
659 while ($match{ships}->[0] =~ m/(\w+)\s+(FI|CO|FR|DE|CR|BS)[^\d]+((?:\d+\s*){5})/g){
661 my @amounts = split /\D+/, $3;
662 my $base = shift @amounts;
663 die "Ships don't sum up properly" if $amounts[3] != $base + $amounts[0] + $amounts[1] + $amounts[2];
665 push @{$slots[$i]->{ships}},{ship => $ship, amount => $amounts[$i]} if $amounts[$i] > 0;
667 $slots[3]->{amount} += $amounts[3];
673 sub findDuplicateFleet : Private {
674 my ( $self, $c, $m ) = @_;
677 my $findfleet = $dbh->prepare(q{
678 SELECT fid FROM fleets f
679 LEFT JOIN launch_confirmations lc USING (fid)
680 WHERE f.pid = (SELECT pid FROM users WHERE uid = $1)
681 AND mission = $3 AND amount = $4 AND (mission <> 'Full fleet' OR tick > $6 - 6)
682 AND COALESCE(uid = $1 AND num = $2 AND lc.pid = $5 AND landing_tick = $6, TRUE)
684 my $fid = $dbh->selectrow_array($findfleet,undef,$c->user->id,$m->{num}
685 ,$m->{mission},$m->{amount}, $m->{pid}, $m->{tick});
686 $c->forward("matchShips", [$m,$fid]);
687 $m->{fid} = $fid if $m->{match};
690 sub addAttackFleet : Private {
691 my ( $self, $c, $m ) = @_;
694 my $findattacktarget = $dbh->prepare(q{
695 SELECT c.target,c.wave,c.launched
697 JOIN raid_targets t ON c.target = t.id
698 JOIN raids r ON t.raid = r.id
699 WHERE c.uid = ? AND r.tick+c.wave-1 = ? AND t.pid = ?
700 AND r.open AND not r.removed
702 my $launchedtarget = $dbh->prepare(q{
703 UPDATE raid_claims SET launched = TRUE
704 WHERE uid = ? AND target = ? AND wave = ?
706 my $claim = $dbh->selectrow_hashref($findattacktarget,undef,$c->user->id,$m->{tick},$m->{pid});
707 if ($claim->{launched}){
708 $m->{warning} = "Already launched on this target:$claim->{target},$claim->{wave},$claim->{launched}";
709 }elsif(defined $claim->{launched}){
710 $launchedtarget->execute($c->user->id,$claim->{target},$claim->{wave});
711 $m->{warning} = "OK:$claim->{target},$claim->{wave},$claim->{launched}";
713 $m->{warning} = "You haven't claimed this target";
717 sub addDefendFleet : Private {
718 my ( $self, $c, $m ) = @_;
721 my $finddefensetarget = $dbh->prepare(q{
722 SELECT call FROM calls c
723 JOIN users u USING (uid)
724 WHERE u.pid = $1 AND c.landing_tick = $2
726 my $informDefChannel = $dbh->prepare(q{
727 INSERT INTO defense_missions (fleet,call) VALUES (?,?)
729 my $call = $dbh->selectrow_hashref($finddefensetarget,undef,$m->{pid},$m->{tick});
731 $informDefChannel->execute($m->{fleet},$call->{call});
733 $m->{warning} = "No call for $m->{target} landing tick $m->{tick}";
737 sub addReturnFleet : Private {
738 my ( $self, $c, $m ) = @_;
741 my $findfleet = $dbh->prepare(q{
742 SELECT fid FROM fleets f
743 JOIN launch_confirmations lc USING (fid)
744 WHERE uid = $1 AND num = $2 AND amount = $3
747 my $fid = $dbh->selectrow_array($findfleet,undef,$c->user->id,$m->{num}
748 ,$m->{amount}, $m->{tick});
749 $c->forward("matchShips", [$m,$fid]);
752 $m->{warning} = "Return fleet, changed back tick to match the return eta.";
754 $m->{warning} = "Couldn't find a fleet matching this returning fleet, so adding a new fleet that is returning";
758 sub matchShips : Private {
759 my ( $self, $c, $m, $fid ) = @_;
763 my $ships = $dbh->prepare(q{
764 SELECT ship, amount FROM fleet_ships WHERE fid = $1 ORDER BY num
766 $ships->execute($fid);
767 for my $s (@{$m->{ships}}){
768 my $s2 = $ships->fetchrow_hashref;
769 return unless $s->{ship} eq $s2->{ship} && $s->{amount} == $s2->{amount};
775 sub defenders : Local {
776 my ( $self, $c, $order ) = @_;
779 my $defenders = $dbh->prepare(q{
780 SELECT uid,pid AS planet,username, to_char(NOW() AT TIME ZONE timezone,'HH24:MI') AS time
781 ,sms_note, call_if_needed, race, timezone
783 JOIN current_planet_stats p USING (pid)
784 WHERE uid IN (SELECT uid FROM groupmembers WHERE gid = 'M')
785 ORDER BY call_if_needed DESC, username
789 my $available = $dbh->prepare(q{
790 SELECT ship,amount FROM available_ships WHERE pid = $1
794 while (my $member = $defenders->fetchrow_hashref){
796 $member->{fleets} = member_fleets($dbh, $member->{uid}, $member->{planet});
797 $available->execute($member->{planet});
798 my $fleet = {fid => $member->{username}, mission => 'Available', name => 'At home'
799 , ships => $available->fetchall_arrayref({})
801 push @{$member->{fleets}}, $fleet;
802 push @members,$member;
804 $c->stash(members => \@members);
808 my ( $dbh, $uid, $planet ) = @_;
810 my $query = $dbh->prepare(q{
812 SELECT DISTINCT ON (mission,name) fid,name,tick, NULL AS eta
813 ,amount, NULL AS coords, pid AS target, NULL AS back
814 ,NULL AS recalled, mission
816 WHERE pid = $2 AND tick <= tick() AND tick >= tick() - 24
817 AND name IN ('Main','Advanced Unit') AND mission = 'Full fleet'
818 ORDER BY mission,name,tick DESC, fid DESC
820 SELECT fid,name,landing_tick AS tick, eta, amount
821 , coords(x,y,z), lc.pid AS target, back
822 , (back <> landing_tick + eta - 1) AS recalled
823 ,CASE WHEN landing_tick <= tick() OR (back <> landing_tick + eta - 1)
824 THEN 'Returning' ELSE mission END AS mission
825 FROM launch_confirmations lc
826 LEFT OUTER JOIN current_planet_stats t USING (pid)
827 JOIN fleets f USING (fid)
828 WHERE uid = $1 AND f.pid = $2 AND back > tick()
829 AND landing_tick - eta - 12 < tick()
833 my $ships = $dbh->prepare(q{SELECT ship,amount FROM fleet_ships
834 WHERE fid = ? ORDER BY num
837 $query->execute($uid,$planet);
839 while (my $fleet = $query->fetchrow_hashref){
841 $ships->execute($fleet->{fid});
842 while (my $ship = $ships->fetchrow_hashref){
845 $fleet->{ships} = \@ships;
853 Michael Andreen (harv@ruin.nu)