X-Git-Url: http://www.chiark.greenend.org.uk/ucgi/~yarrgweb/git?a=blobdiff_plain;f=yarrg%2Fweb%2Froutetrade;h=ad826579c0907e48b219d4270d6e4ec203dbdff8;hb=bcb730c8215c49c14f99f0f9c1bd503275ca22f3;hp=1c5a3e49db2b184ac019b4a8177cb8838df59040;hpb=da8db80459e25a474b545779db3fcd0304e68325;p=ypp-sc-tools.main.git 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; -
-Route is as follows: - -% foreach my $i (0..$#islandids) { -% my $islandid= $islandids[$i]; -% my $archipelago= $archipelagoes[$i]; -% $islandid= '' unless defined $islandid; -% $archipelago= ' ' 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> + +<% $stmt |h %> +<% join(' | ',@query_params) |h %>+<& dumpqueryresults, sth =>$sth &> + <%init> use CommodsWeb; %init>