From 0dc4622c02076fcd47acc56ad60511b10fb522b9 Mon Sep 17 00:00:00 2001
From: Michael Andreen <harv@ruin.nu>
Date: Sun, 20 Jun 2010 16:42:39 +0200
Subject: [PATCH] Show recevied and sent value on def leeches page

---
 database/def_leeches.sql      | 34 ++++++++++++++++++++++++++++++++++
 lib/NDWeb/Controller/Calls.pm | 18 ++++--------------
 root/src/calls/defleeches.tt2 |  4 ++++
 3 files changed, 42 insertions(+), 14 deletions(-)
 create mode 100644 database/def_leeches.sql

diff --git a/database/def_leeches.sql b/database/def_leeches.sql
new file mode 100644
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 7203bc1..7b7e523 100644
--- 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 $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;
 
diff --git a/root/src/calls/defleeches.tt2 b/root/src/calls/defleeches.tt2
index 12c4e9f..7e6bdfe 100644
--- 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 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' %]">
@@ -16,6 +18,8 @@
 	<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>
-- 
2.39.2