New fleet infrastructure and possible to list ships at home

diff --git a/Usermgm.pm b/Usermgm.pm
index 1f8545b689c3cf8a4329ba8457a76fa35dae99ba..2663e35e5f8f8e809bffc6a67479f490b554f2b3 100644 (file)
--- 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);