Show how many times an alliance has been targeted by us.

diff --git a/database/functions/hostile_alliances.sql b/database/functions/hostile_alliances.sql
new file mode 100644 (file)
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 ec6da88f2d634d648549dea75564f0209b1f2c3c..8f0b6c67555203131bbcb25b828e7978945eb6a8 100644 (file)
--- 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 0996ec6bed8792820c1b8a2994578d8beeaaa4d5..28f1272f7dc4d99a8bf708c11e85e1b256217650 100644 (file)
--- 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>