From 21023ed45c8db0ff06c0106593123d5b84a19c72 Mon Sep 17 00:00:00 2001
From: Ian Jackson <ian@liberator.relativity.greenend.org.uk>
Date: Sat, 22 Aug 2009 15:29:46 +0100
Subject: [PATCH 1/1] Make query more directly useful for display[

---
 yarrg/web/routetrade | 29 +++++++++++++++++------------
 1 file changed, 17 insertions(+), 12 deletions(-)

diff --git a/yarrg/web/routetrade b/yarrg/web/routetrade
index 407cc8c..e6aff44 100644
--- a/yarrg/web/routetrade
+++ b/yarrg/web/routetrade
@@ -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);
-- 
2.30.2