chiark / gitweb /
Make query more directly useful for display[
authorIan Jackson <ian@liberator.relativity.greenend.org.uk>
Sat, 22 Aug 2009 14:29:46 +0000 (15:29 +0100)
committerIan Jackson <ian@liberator.relativity.greenend.org.uk>
Sat, 22 Aug 2009 14:29:46 +0000 (15:29 +0100)
yarrg/web/routetrade

index 407cc8c..e6aff44 100644 (file)
@@ -75,18 +75,21 @@ foreach my $src_i (0..$#islandids) {
 }
 
 my $stmt= "            
-       SELECT  commods.commodname              commodname,
-               commods.commodid                commodid,
-               commods.unitmass                mass,
-               commods.unitvolume              volume,
-               sell_islands.islandid           org_id,
-               sell_islands.islandname         org_name,
-               sell.price                      org_price,
-               sum(sell.qty)                   org_qty,
-               buy_islands.islandid            dst_id,
-               buy_islands.islandname          dst_name,
-               buy.price                       dst_price,
-               sum(buy.qty)                    dst_qty
+       SELECT  sell_islands.islandname                         org_name,
+               sell_islands.islandid                           org_id,
+               sell.price                                      org_price,
+               sum(sell.qty)                                   org_qty,
+               buy_islands.islandname                          dst_name,
+               buy_islands.islandid                            dst_id,
+               buy.price                                       dst_price,
+               sum(buy.qty)                                    dst_qty,
+               commods.commodname                              commodname,
+               commods.commodid                                commodid,
+               commods.unitmass                                mass,
+               commods.unitvolume                              volume,
+               buy.price - sell.price                          unitprofit,
+               min(sell.qty,buy.qty)                           tqty,
+               min(sell.qty,buy.qty) * (buy.price-sell.price)  profit
        FROM commods
        JOIN buy  on commods.commodid = buy.commodid
        JOIN sell on commods.commodid = sell.commodid
@@ -99,6 +102,8 @@ my $stmt= "
          AND   buy.price > sell.price
        GROUP BY commodname, commods.commodid,
                org_id, org_price, dst_id, dst_price
+       ORDER BY org_name, dst_name, profit DESC, commodname,
+                org_price, dst_price DESC
      ";
 
 my $sth= $dbh->prepare($stmt);