From bcb730c8215c49c14f99f0f9c1bd503275ca22f3 Mon Sep 17 00:00:00 2001
From: Ian Jackson <ian@liberator.relativity.greenend.org.uk>
Date: Sat, 22 Aug 2009 13:53:36 +0100
Subject: [PATCH] WIP route trade finding: does query to find flows

---
 yarrg/web/profitable_trades |  0
 yarrg/web/routetrade        | 74 ++++++++++++++++++++++++++++++-------
 2 files changed, 61 insertions(+), 13 deletions(-)
 mode change 100755 => 100644 yarrg/web/profitable_trades

diff --git a/yarrg/web/profitable_trades b/yarrg/web/profitable_trades
old mode 100755
new mode 100644
diff --git a/yarrg/web/routetrade b/yarrg/web/routetrade
index 1c5a3e4..ad82657 100644
--- a/yarrg/web/routetrade
+++ b/yarrg/web/routetrade
@@ -3,24 +3,72 @@ $dbh
 @islandids
 @archipelagoes
 </%args>
+<%perl>
 
-%# So, add code to do right thing here:
+my @flow_conds;
+my @query_params;
 
-<pre>
-Route is as follows:
-
-% foreach my $i (0..$#islandids) {
-%   my $islandid= $islandids[$i];
-%   my $archipelago= $archipelagoes[$i];
-%   $islandid= '<undef>' unless defined $islandid;
-%   $archipelago= '<undef>' unless defined $archipelago;
-%
-  islandid=<% $islandid |h %>  archipelago=<% $archipelago |h %>
-%
-% }
+my $sd_condition= sub {
+	my ($bs, $ix) = @_;
+	my $islandid= $islandids[$ix];
+	if (defined $islandid) {
+		return "${bs}_islands.islandid = $islandid";
+	} else {
+		push @query_params, $archipelagoes[$ix];
+		return "${bs}_islands.archipelago = ?";
+	}
+};
+
+foreach my $src_i (0..$#islandids) {
+	my $src_cond= $sd_condition->('sell',$src_i);
+	my @dst_conds;
+	foreach my $dst_i ($src_i..$#islandids) {
+		push @dst_conds, $sd_condition->('buy',$dst_i);
+	}
+	push @flow_conds, "$src_cond AND (
+			".join("
+		     OR ",@dst_conds)."
+		)";
+}
+
+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
+	FROM commods
+	JOIN buy  on commods.commodid = buy.commodid
+	JOIN sell on commods.commodid = sell.commodid
+	JOIN islands as sell_islands on sell.islandid = sell_islands.islandid
+	JOIN islands as buy_islands  on buy.islandid  = buy_islands.islandid
+	WHERE	(
+		".join("
+	   OR	", @flow_conds)."
+	)
+	  AND	buy.price > sell.price
+	GROUP BY commodname, commods.commodid,
+		org_id, org_price, dst_id, dst_price
+     ";
 
+my $sth= $dbh->prepare($stmt);
+$sth->execute(@query_params);
+
+</%perl>
+<pre>
+<% $stmt |h %>
+<% join(' | ',@query_params) |h %>
 </pre>
 
+<& dumpqueryresults, sth =>$sth &>
+
 <%init>
 use CommodsWeb;
 </%init>
-- 
2.30.2