From: Michael Andreen <harv@ruin.nu>
Date: Fri, 13 Mar 2009 09:51:03 +0000 (+0100)
Subject: Show members available ships on def calls
X-Git-Url: https://ruin.nu/git/?p=ndwebbie.git;a=commitdiff_plain;h=3eb2598e22ae88c25f5129f169ccb4461fe6962d

Show members available ships on def calls
---

diff --git a/database/available_ships.sql b/database/available_ships.sql
index e290583..ecd2ad7 100644
--- a/database/available_ships.sql
+++ b/database/available_ships.sql
@@ -1,28 +1,38 @@
-DROP VIEW available_ships;
-CREATE VIEW available_ships AS
-SELECT uid,username, planet, ship
+CREATE TABLE ticks (t INTEGER PRIMARY KEY);
+
+INSERT INTO ticks (SELECT * FROM generate_series(0,10000));
+
+CREATE OR REPLACE VIEW ships_home AS
+SELECT tick, 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
+	SELECT t AS tick, planet,ship,amount
 	FROM (
-		SELECT DISTINCT ON (planet,mission) planet,mission, fid
-		FROM fleets f
-		WHERE tick <= tick()
+		SELECT DISTINCT ON (t,planet,mission) t,planet,mission, fid
+		FROM ticks
+			CROSS JOIN fleets f
+		WHERE tick <= t
 			AND name IN ('Main','Advanced Unit')
 			AND mission = 'Full fleet'
-		ORDER BY planet,mission,tick DESC, fid DESC
+		ORDER BY t,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
+	SELECT t AS tick, planet, ship, SUM(fs.amount) AS amount
+		, 3 - COUNT(DISTINCT fid) AS fleets
+	FROM ticks
+		CROSS JOIN 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
+	WHERE back > t
+		AND landing_tick - eta - 12 < t
+	GROUP BY t,planet,ship
+) o USING (tick,planet,ship)
+WHERE COALESCE(f.amount - o.amount,f.amount) > 0;
 
+CREATE OR REPLACE VIEW available_ships AS
+SELECT uid,username, planet, ship, amount, fleets
+FROM ships_home
+WHERE tick = tick();
diff --git a/lib/NDWeb/Controller/Calls.pm b/lib/NDWeb/Controller/Calls.pm
index 636ac29..6f037ce 100644
--- a/lib/NDWeb/Controller/Calls.pm
+++ b/lib/NDWeb/Controller/Calls.pm
@@ -164,6 +164,16 @@ sub edit : Local {
 		}
 		push @fleets, $fleet;
 	}
+
+	my $available = $dbh->prepare(q{
+SELECT ship,amount from ships_home WHERE planet = $1 AND tick = $2
+		});
+	$available->execute($call->{planet}, $call->{landing_tick});
+	my $fleet = {fid => $call->{member}, mission => 'Available'
+		, name => 'At home', ships => $available->fetchall_arrayref({})
+	};
+	push @fleets, $fleet;
+
 	$c->stash(fleets => \@fleets);
 
 	my $defenders = $dbh->prepare(q{