Show recevied and sent value on def leeches page

diff --git a/database/def_leeches.sql b/database/def_leeches.sql
new file mode 100644 (file)
index 0000000..74a4c46
--- /dev/null
+++ b/database/def_leeches.sql
@@ -0,0 +1,34 @@
+CREATE OR REPLACE VIEW def_leeches AS
+WITH f AS (
+       SELECT uid,fid,lc.pid,f.pid AS fpid,landing_tick,eta,back, SUM(fs.amount*(s.metal + s.crystal + s.eonium)/100.0) AS value
+       FROM launch_confirmations lc
+               JOIN fleets f USING (fid)
+               JOIN fleet_ships fs USING (fid)
+               JOIN ship_stats s ON fs.ship = s.name
+       WHERE mission = 'Defend'
+       GROUP BY uid,fid,lc.pid,f.pid,landing_tick,eta,back
+), f2 AS (
+       SELECT f.uid
+               ,SUM(f.value / COALESCE(p.value, (SELECT value FROM planet_stats WHERE pid = f.fpid AND tick = landing_tick - eta ORDER BY tick DESC LIMIT 1))) AS sent_value
+       FROM calls c
+               JOIN users u USING (uid)
+               JOIN f USING (pid,landing_tick)
+               LEFT JOIN (SELECT pid AS fpid,value,tick AS landing_tick FROM planet_stats) AS p USING (fpid, landing_tick)
+       GROUP BY f.uid
+)
+SELECT uid,username,defense_points,count(call) AS calls
+       , SUM(fleets) AS fleets, SUM(recalled) AS recalled
+       ,count(NULLIF(fleets,0)) AS defended_calls
+       ,SUM(value)::NUMERIC(4,2) AS value
+       ,sent_value::NUMERIC(4,2)
+FROM (SELECT u.uid,username,defense_points,call,count(f.back) AS fleets
+               , count(NULLIF(f.landing_tick + f.eta -1 = f.back,TRUE)) AS recalled
+               ,SUM(f.value / COALESCE(p.value, (SELECT value FROM planet_stats WHERE pid = f.pid AND tick = landing_tick - eta ORDER BY tick DESC LIMIT 1))) AS value
+       FROM users u
+               JOIN calls c USING (uid)
+               LEFT JOIN f USING (pid,landing_tick)
+               LEFT JOIN (SELECT pid,value,tick AS landing_tick FROM planet_stats) AS p USING (pid, landing_tick)
+       GROUP BY u.uid,username,defense_points,call
+) d
+       LEFT JOIN f2 USING (uid)
+GROUP BY uid,username,defense_points, sent_value

diff --git a/lib/NDWeb/Controller/Calls.pm b/lib/NDWeb/Controller/Calls.pm
index 7203bc11bfe82285750ee66f2266ac828a8ea840..7b7e523204019553f2e8fe7065595004ebf93e89 100644 (file)
--- a/lib/NDWeb/Controller/Calls.pm
+++ b/lib/NDWeb/Controller/Calls.pm
@@ -218,20 +218,10 @@ sub defleeches : Local {
        my ( $self, $c, $type ) = @_;
        my $dbh = $c->model;
 
        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;
 
                });
        $query->execute;
 

diff --git a/root/src/calls/defleeches.tt2 b/root/src/calls/defleeches.tt2
index 12c4e9f52a1f552c001e2bb0a529398390520123..7e6bdfe763d86d3d6c9be2ca033f889545b9f747 100644 (file)
--- a/root/src/calls/defleeches.tt2
+++ b/root/src/calls/defleeches.tt2
@@ -7,6 +7,8 @@
        <th>Recalled missions</th>
        <th>Calls</th>
        <th>Defended calls</th>
        <th>Recalled missions</th>
        <th>Calls</th>
        <th>Defended calls</th>

+       <th title="Compared to own value at the time">Value received</th>
+       <th title="Compared to own value at the time">Value sent</th>

        </tr>
 [% FOR m IN members %]
        <tr align="right" class="[% loop.count % 2 == 0 ? 'even' : 'odd' %]">
        </tr>
 [% FOR m IN members %]
        <tr align="right" class="[% loop.count % 2 == 0 ? 'even' : 'odd' %]">


@@ -16,6 +18,8 @@
        <td>[% m.recalled %]</td>
        <td>[% m.calls %]</td>
        <td>[% m.defended_calls %]</td>
        <td>[% m.recalled %]</td>
        <td>[% m.calls %]</td>
        <td>[% m.defended_calls %]</td>

+       <td>[% m.value %]</td>
+       <td>[% m.sent_value %]</td>

        </tr>
 [% END %]
 </table>
        </tr>
 [% END %]
 </table>