<%args>
$dbh
@islandids
@archipelagoes
</%args>
<%perl>

my @flow_conds;
my @query_params;

my $sd_condition= sub {
	my ($bs, $ix) = @_;
	my $islandid= $islandids[$ix];
	if (defined $islandid) {
		return "${bs}.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) {
		my $dst_cond= $sd_condition->('buy',$dst_i);
		if ($dst_i==$src_i and !defined $islandids[$src_i]) {
			# we always want arbitrage, but mentioning an arch
			# once shouldn't produce intra-arch trades
			$dst_cond=
				"($dst_cond AND sell.islandid = buy.islandid)";
		}
		push @dst_conds, $dst_cond;
	}
	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>