From 93a978b42c10736ee4cda392001aea0adb351958 Mon Sep 17 00:00:00 2001
From: Michael Andreen <harv@ruin.nu>
Date: Thu, 19 Feb 2009 23:17:42 +0100
Subject: [PATCH] Show available ships

---
 database/available_ships.sql    | 28 ++++++++++++++++++++++++++++
 lib/NDWeb/Controller/Members.pm |  9 +++++++++
 2 files changed, 37 insertions(+)
 create mode 100644 database/available_ships.sql

diff --git a/database/available_ships.sql b/database/available_ships.sql
new file mode 100644
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 2ab6f27..440c51f 100644
--- 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);
-- 
2.39.2