Be more paranoid with param in list context

[ndwebbie.git] / lib / NDWeb / Controller / Calls.pm

diff --git a/lib/NDWeb/Controller/Calls.pm b/lib/NDWeb/Controller/Calls.pm
index 7203bc11bfe82285750ee66f2266ac828a8ea840..48eac7eeb1e1406cadc01214e5f65154b32a31c8 100644 (file)
--- a/lib/NDWeb/Controller/Calls.pm
+++ b/lib/NDWeb/Controller/Calls.pm
@@ -146,16 +146,31 @@ SELECT ship,amount from ships_home WHERE pid = $1 AND tick = $2
        $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)
+       JOIN lc USING (fid)
+       JOIN lcv USING (fid)
 WHERE mission IN ('Defend', 'Fake Defend')
 ORDER BY x,y,z
        });
@@ -218,20 +233,10 @@ sub defleeches : Local {
        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;
 
@@ -263,13 +268,15 @@ sub postcallupdate : Local {
        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')){
@@ -279,8 +286,9 @@ sub postcallupdate : Local {
                        $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;