From: Michael Andreen <harv@ruin.nu>
Date: Sat, 3 Jul 2010 20:12:40 +0000 (+0200)
Subject: Show value percentage for defenders on call page
X-Git-Url: https://ruin.nu/git/?p=ndwebbie.git;a=commitdiff_plain;h=56ac564207e3abcfcc7929ec155c883a564862c6

Show value percentage for defenders on call page
---

diff --git a/lib/NDWeb/Controller/Calls.pm b/lib/NDWeb/Controller/Calls.pm
index 7b7e523..0ba04a5 100644
--- 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 ON s.name = fs.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
 	});
diff --git a/root/lib/inc/missionlist.tt2 b/root/lib/inc/missionlist.tt2
index f72445d..91e7c31 100644
--- a/root/lib/inc/missionlist.tt2
+++ b/root/lib/inc/missionlist.tt2
@@ -17,6 +17,9 @@
 			<td>[% m.eta %]</td>
 			<td>[% m.amount %]</td>
 			<td>[% m.back %]</td>
+			[% IF m.value_ratio %]
+			<td>[% m.value_ratio %]%</td>
+			[% END %]
 			[% IF m.fleetcatch %]
 			<td class="Hostile">FLEETCATCH!!</td>
 			[% END %]