From ca89193ac48c1cb1ea13da93b66a9ef03a51e430 Mon Sep 17 00:00:00 2001
From: Michael Andreen <harv@ruin.nu>
Date: Fri, 20 Feb 2009 21:18:30 +0100
Subject: [PATCH] New fleet infrastructure and possible to list ships at home

---
 Usermgm.pm | 24 ++++++++++++++++--------
 1 file changed, 16 insertions(+), 8 deletions(-)

diff --git a/Usermgm.pm b/Usermgm.pm
index 1f8545b..2663e35 100644
--- a/Usermgm.pm
+++ b/Usermgm.pm
@@ -197,13 +197,21 @@ sub getShips {
 	if (officer() || dc()){
 		my $f = $ND::DBH->prepare(qq{SELECT username,SUM(fs.amount) AS amount
 			FROM users u 
-				JOIN (SELECT DISTINCT ON (sender) sender AS planet,id FROM fleets
+				JOIN (SELECT DISTINCT ON (planet) planet,fid FROM fleets
 					WHERE mission = 'Full fleet' AND name <> 'Unit'
-					ORDER BY sender,tick DESC,id DESC) f  USING (planet) 
-				JOIN fleet_ships fs ON f.id = fs.id
+					ORDER BY planet,tick DESC,fid DESC) f  USING (planet) 
+				JOIN fleet_ships fs USING (fid)
 			WHERE ship ILIKE ?
 			GROUP BY username ORDER BY amount DESC
-		});
+			});
+		if ($command eq 'shipshome'){
+			$f = $ND::DBH->prepare(q{
+SELECT username,SUM(amount) AS amount
+FROM available_ships
+WHERE ship ILIKE ?
+GROUP BY username ORDER BY amount DESC
+			});
+		}
 		$f->execute($ship);
 		my $text;
 		my $i = 0;
@@ -232,14 +240,14 @@ sub getFleet {
 	if (officer() || dc()){
 		my $f = $ND::DBH->prepare(q{SELECT fs.ship, fs.amount, username
 			FROM fleet_ships fs
-				JOIN (SELECT id,username
+				JOIN (SELECT fid,username
 					FROM fleets f
-						JOIN users u ON u.planet = f.sender
+						JOIN users u USING (planet)
 					WHERE mission = 'Full fleet' AND name <> 'Unit'
 						AND username ILIKE $1
-					ORDER BY planet,tick DESC,id DESC
+					ORDER BY planet,tick DESC,fid DESC
 					LIMIT 1
-				) f  USING (id)
+				) f  USING (fid)
 			ORDER BY num
 		});
 		$f->execute($nick);
-- 
2.39.2