}
my $available = $dbh->prepare(q{
-SELECT ship,amount from ships_home WHERE planet = $1 AND tick = $2
+SELECT ship,amount from ships_home WHERE pid = $1 AND tick = $2
});
$available->execute($call->{pid}, $call->{landing_tick});
my $fleet = {fid => $call->{member}, mission => 'Available'
$c->stash(fleets => \@fleets);
my $defenders = $dbh->prepare(q{
+WITH lc AS (
+ SELECT fid,uid,back,eta,landing_tick
+ FROM launch_confirmations
+ WHERE pid = $1 AND landing_tick = $2
+), lcp AS (
+ SELECT DISTINCT ON (fid) fid,tick,value
+ FROM lc
+ JOIN users u USING (uid)
+ JOIN planet_stats USING (pid)
+ WHERE tick <= landing_tick - eta
+ ORDER BY fid,tick DESC
+), lcv AS (
+ SELECT fid,(sum(amount*(metal+crystal+eonium)) / value)::int AS value_ratio
+ FROM lcp
+ JOIN fleet_ships fs USING (fid)
+ JOIN ship_stats s USING (ship)
+ GROUP BY fid, value
+)
SELECT DISTINCT ON (x,y,z,pid,name,amount,back) fid,mission, name, eta
, amount, coords(x,y,z) AS coords, landing_tick AS tick, pid AS planet
- ,back, (back <> landing_tick + eta - 1) AS recalled
+ ,back, (back <> landing_tick + eta - 1) AS recalled, value_ratio
FROM fleets f
LEFT OUTER JOIN current_planet_stats s USING (pid)
- JOIN (
- SELECT fid,back,eta,landing_tick
- FROM launch_confirmations
- WHERE pid = $1 AND landing_tick = $2
- ) lc USING (fid)
-WHERE mission = 'Defend'
+ JOIN lc USING (fid)
+ JOIN lcv USING (fid)
+WHERE mission IN ('Defend', 'Fake Defend')
ORDER BY x,y,z
});
my ( $self, $c, $type ) = @_;
my $dbh = $c->model;
- my $query = $dbh->prepare(q{SELECT username,defense_points,count(call) AS calls
- , SUM(fleets) AS fleets, SUM(recalled) AS recalled
- ,count(NULLIF(fleets,0)) AS defended_calls
- FROM (SELECT username,defense_points,call,count(f.back) AS fleets
- , count(NULLIF(f.landing_tick + f.eta -1 = f.back,TRUE)) AS recalled
- FROM users u JOIN calls c USING (uid)
- LEFT OUTER JOIN (
- SELECT lc.pid,landing_tick,eta,back
- FROM launch_confirmations lc JOIN fleets f USING (fid)
- WHERE mission = 'Defend'
- ) f USING (pid,landing_tick)
- GROUP BY username,defense_points,call
- ) d
- GROUP BY username,defense_points ORDER BY fleets DESC, defense_points
+ my $query = $dbh->prepare(q{
+SELECT username, defense_points, calls, fleets, recalled, defended_calls, value, sent_value
+FROM def_leeches
+ORDER BY value DESC NULLS LAST, defense_points
});
$query->execute;
if ($c->req->param('cmd') eq 'Submit'){
my $logmess = '';
if ($c->req->param('ctick')){
+ my $tick = $c->req->param('tick');
$dbh->do(q{UPDATE calls SET landing_tick = ? WHERE call = ?}
- ,undef,$c->req->param('tick'),$call->{call});
+ ,undef,$tick,$call->{call});
$logmess .= "Updated landing tick from [B] $call->{landing_tick} [/B]\n";
}
if ($c->req->param('cinfo')){
+ my $info = $c->req->param('info');
$dbh->do(q{UPDATE calls SET info = ? WHERE call = ?}
- ,undef,$c->req->param('info'),$call->{call});
+ ,undef,$info,$call->{call});
$logmess .= "Updated info\n";
}
if ($c->req->param('ccalc')){
$logmess .= html_escape('Updated calc to: [URL]'.$calc."[/URL]\n");
}
if ($c->req->param('cstatus')){
+ my $status = $c->req->param('status');
$dbh->do(q{UPDATE calls SET status = $1, dc = $2 WHERE call = $3}
- ,undef,$c->req->param('status'),$c->user->id,$call->{call});
+ ,undef,$status,$c->user->id,$call->{call});
$logmess .= "Changed status to: ".$c->req->param('status')."\n";
}
$log->execute($c->user->id,$call->{ftid},$logmess) if $log;