Show available ships

diff --git a/database/available_ships.sql b/database/available_ships.sql
new file mode 100644 (file)
index 0000000..e290583
--- /dev/null
+++ b/database/available_ships.sql
@@ -0,0 +1,28 @@
+DROP VIEW available_ships;
+CREATE VIEW available_ships AS
+SELECT uid,username, planet, ship
+       , COALESCE(f.amount - o.amount,f.amount) AS amount
+       , COALESCE(fleets,3) AS fleets
+FROM users u JOIN (
+       SELECT planet,ship,amount
+       FROM (
+               SELECT DISTINCT ON (planet,mission) planet,mission, fid
+               FROM fleets f
+               WHERE tick <= tick()
+                       AND name IN ('Main','Advanced Unit')
+                       AND mission = 'Full fleet'
+               ORDER BY planet,mission,tick DESC, fid DESC
+       ) f
+               JOIN fleet_ships fs USING (fid)
+       
+) f USING (planet) LEFT OUTER JOIN (
+       SELECT planet, ship, SUM(fs.amount) AS amount, 3 - COUNT(DISTINCT fid) AS fleets
+       FROM fleets f
+               JOIN launch_confirmations USING (fid)
+               JOIN fleet_ships fs USING (fid)
+       WHERE back > tick()
+               AND landing_tick - eta - 12 < tick()
+       GROUP BY planet,ship
+) o USING (planet,ship)
+WHERE COALESCE(f.amount - o.amount,f.amount) > 0
+

diff --git a/lib/NDWeb/Controller/Members.pm b/lib/NDWeb/Controller/Members.pm
index 2ab6f2759baf2e66e53ffe7e6cf58968f9833d13..440c51f47021a778f690c3e5a1400b6c23fc83e8 100644 (file)
--- a/lib/NDWeb/Controller/Members.pm
+++ b/lib/NDWeb/Controller/Members.pm
@@ -535,10 +535,19 @@ ORDER BY call_if_needed DESC, LOWER(username)
                });
        $defenders->execute;
 
+       my $available = $dbh->prepare(q{
+SELECT ship,amount FROM available_ships WHERE planet = $1
+               });
+
        my @members;
        while (my $member = $defenders->fetchrow_hashref){
 
                $member->{fleets} = member_fleets($dbh, $member->{uid}, $member->{planet});
+               $available->execute($member->{planet});
+               my $fleet = {fid => $member->{username}, mission => 'Available', name => 'At home'
+                       , ships => $available->fetchall_arrayref({})
+               };
+               push @{$member->{fleets}}, $fleet;
                push @members,$member;
        }
        $c->stash(members => \@members);