From: Ian Jackson <ian@liberator.relativity.greenend.org.uk>
Date: Sat, 22 Aug 2009 14:29:46 +0000 (+0100)
Subject: Make query more directly useful for display[
X-Git-Tag: 3.4~148
X-Git-Url: http://www.chiark.greenend.org.uk/ucgi/~yarrgweb/git?p=ypp-sc-tools.db-live.git;a=commitdiff_plain;h=21023ed45c8db0ff06c0106593123d5b84a19c72;hp=0a273ee819a0c5807ed112051da10c19dfa09f70

Make query more directly useful for display[
---

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);