From dc6cd8e0f865330c1dd3c89122e62f326314f96a Mon Sep 17 00:00:00 2001
From: Michael Andreen <harv@ruin.nu>
Date: Sat, 16 Apr 2016 17:32:01 +0200
Subject: [PATCH] Show how many times an alliance has been targeted by us.

---
 database/functions/hostile_alliances.sql | 33 ++++++++++++++++++++++++
 lib/NDWeb/Controller/Alliances.pm        |  8 ++----
 root/src/alliances/hostile.tt2           |  6 ++++-
 3 files changed, 40 insertions(+), 7 deletions(-)
 create mode 100644 database/functions/hostile_alliances.sql

diff --git a/database/functions/hostile_alliances.sql b/database/functions/hostile_alliances.sql
new file mode 100644
index 0000000..0c760c2
--- /dev/null
+++ b/database/functions/hostile_alliances.sql
@@ -0,0 +1,33 @@
+DROP FUNCTION IF EXISTS hostile_alliances(INT,INT);
+CREATE OR REPLACE FUNCTION hostile_alliances (first INT, last INT)
+RETURNS table(aid INT, alliance TEXT, hostile_count BIGINT, targeted BIGINT, targeted_raids BIGINT)
+AS $SQL$
+WITH hostile_alliances AS (
+	SELECT COALESCE(aid,-1) AS aid, count(*) AS hostile_count
+	FROM calls c
+		JOIN incomings i USING (call)
+		JOIN current_planet_stats s USING (pid)
+	WHERE c.landing_tick BETWEEN $1 + i.eta AND $2 + i.eta
+	GROUP BY aid
+), alliance_targets_1 AS (
+	SELECT COALESCE(aid,-1) AS aid, exists(
+			SELECT pid FROM raid_targets rt
+			JOIN raids r ON rt.raid = r.id
+			WHERE rt.pid = p.pid AND lc.landing_tick BETWEEN r.tick AND r.tick + r.waves - 1
+		) AS raid_target
+	FROM launch_confirmations lc
+		JOIN current_planet_stats p USING (pid)
+		JOIN fleets f USING (fid)
+	WHERE f.mission = 'Attack'
+		AND lc.landing_tick BETWEEN $1 + lc.eta AND $2 + lc.eta
+), alliance_targets AS (
+	SELECT aid, count(*) AS targeted, count(NULLIF(raid_target,false)) AS targeted_raids
+	FROM alliance_targets_1
+	GROUP BY aid
+)
+SELECT aid, alliance, hostile_count, targeted, targeted_raids
+FROM hostile_alliances
+	LEFT JOIN alliance_targets USING (aid)
+	LEFT JOIN alliances USING (aid)
+$SQL$ LANGUAGE SQL STABLE;
+
diff --git a/lib/NDWeb/Controller/Alliances.pm b/lib/NDWeb/Controller/Alliances.pm
index ec6da88..8f0b6c6 100644
--- a/lib/NDWeb/Controller/Alliances.pm
+++ b/lib/NDWeb/Controller/Alliances.pm
@@ -189,12 +189,8 @@ sub hostile : Local {
 	}
 
 	my $query = $dbh->prepare(q{
-		SELECT s.aid AS id,s.alliance AS name,count(*) AS hostile_count
-FROM calls c 
-	JOIN incomings i USING (call)
-	JOIN current_planet_stats s USING (pid)
-WHERE c.landing_tick BETWEEN $1 + i.eta AND $2 + i.eta
-GROUP BY s.aid,s.alliance
+SELECT aid, alliance, hostile_count, targeted, targeted_raids
+FROM hostile_alliances($1,$2)
 ORDER BY hostile_count DESC
 		});
 	$query->execute($begintick,$endtick);
diff --git a/root/src/alliances/hostile.tt2 b/root/src/alliances/hostile.tt2
index 0996ec6..28f1272 100644
--- a/root/src/alliances/hostile.tt2
+++ b/root/src/alliances/hostile.tt2
@@ -13,11 +13,15 @@
 	<tr>
 		<th>Alliance</th>
 		<th>Hostile Counts</th>
+		<th>Targeted by us</th>
+		<th>Raid targets</th>
 	</tr>
 [% FOR a IN alliances %]
 	<tr class="[% loop.count % 2 ? 'even' : 'odd' %]">
-		<td><a href="[% c.uri_for('edit',a.id) %]">[% a.name %]</a></td>
+		<td><a href="[% c.uri_for('edit',a.aid) %]">[% a.alliance %]</a></td>
 		<td>[% a.hostile_count %]</td>
+		<td>[% a.targeted %]</td>
+		<td>[% a.targeted_raids %]</td>
 	</tr>
 [% END %]
 </table>
-- 
2.39.2