+<&| script &>
+ da_pageload= Date.now();
+</&script>
+
+<%perl>
+
+my $now= time;
+my $loss_per_league= 1e-7;
+
+my @flow_conds;
+my @query_params;
+my %dists;
+
+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 = ?";
+ }
+};
+
+my %islandpair;
+# $islandpair{$a,$b}= [ $start_island_ix, $end_island_ix ]
+
+my $specific= !grep { !defined $_ } @islandids;
+my $confusing= 0;
+
+foreach my $src_i (0..$#islandids) {
+ my $src_isle= $islandids[$src_i];
+ my $src_cond= $sd_condition->('sell',$src_i);
+ my @dst_conds;
+ foreach my $dst_i ($src_i..$#islandids) {
+ my $dst_isle= $islandids[$dst_i];
+ my $dst_cond= $sd_condition->('buy',$dst_i);
+ if ($dst_i==$src_i and !defined $src_isle) {
+ # 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;
+
+ if ($specific && !$confusing &&
+ # With a circular route, do not carry goods round the loop
+ !(($src_i==0 || $src_i==$#islandids) &&
+ $dst_i==$#islandids &&
+ $src_isle == $islandids[$dst_i])) {
+ if ($islandpair{$src_isle,$dst_isle}) {
+ $confusing= 1;
+print "confusing $src_i $src_isle $dst_i $dst_isle\n";
+ } else {
+ $islandpair{$src_isle,$dst_isle}=
+ [ $src_i, $dst_i ];
+ }
+ }
+ }
+ push @flow_conds, "$src_cond AND (
+ ".join("
+ OR ",@dst_conds)."
+ )";
+}
+
+my $stmt= "
+ SELECT sell_islands.islandname org_name,
+ sell_islands.islandid org_id,
+ sell.price org_price,
+ sell.qty org_qty_stall,
+ sell_stalls.stallname org_stallname,
+ sell.stallid org_stallid,
+ sell_uploads.timestamp org_timestamp,
+ buy_islands.islandname dst_name,
+ buy_islands.islandid dst_id,
+ buy.price dst_price,
+ buy.qty dst_qty_stall,
+ buy_stalls.stallname dst_stallname,
+ buy.stallid dst_stallid,
+ buy_uploads.timestamp dst_timestamp,
+".($qa->{ShowStalls} ? "
+ sell.qty org_qty_agg,
+ buy.qty dst_qty_agg,
+" : "
+ (SELECT sum(qty) FROM sell AS sell_agg
+ WHERE sell_agg.commodid = commods.commodid
+ AND sell_agg.islandid = sell.islandid
+ AND sell_agg.price = sell.price) org_qty_agg,
+ (SELECT sum(qty) FROM buy AS buy_agg
+ WHERE buy_agg.commodid = commods.commodid
+ AND buy_agg.islandid = buy.islandid
+ AND buy_agg.price = buy.price) dst_qty_agg,
+")."
+ commods.commodname commodname,
+ commods.commodid commodid,
+ commods.unitmass unitmass,
+ commods.unitvolume unitvolume,
+ dist dist,
+ buy.price - sell.price unitprofit
+ FROM commods
+ JOIN sell ON commods.commodid = sell.commodid
+ JOIN buy ON commods.commodid = buy.commodid
+ JOIN islands AS sell_islands ON sell.islandid = sell_islands.islandid
+ JOIN islands AS buy_islands ON buy.islandid = buy_islands.islandid
+ JOIN uploads AS sell_uploads ON sell.islandid = sell_uploads.islandid
+ JOIN uploads AS buy_uploads ON buy.islandid = buy_uploads.islandid
+ JOIN stalls AS sell_stalls ON sell.stallid = sell_stalls.stallid
+ JOIN stalls AS buy_stalls ON buy.stallid = buy_stalls.stallid
+ JOIN dists ON aiid = sell.islandid AND biid = buy.islandid
+ WHERE (
+ ".join("
+ OR ", @flow_conds)."
+ )
+ AND buy.price > sell.price
+ ORDER BY org_name, dst_name, commodname, unitprofit DESC,
+ org_price, dst_price DESC,
+ org_stallname, dst_stallname
+ ";
+
+my $sth= $dbh->prepare($stmt);
+$sth->execute(@query_params);
+my @flows;
+
+my $distquery= $dbh->prepare("
+ SELECT dist FROM dists WHERE aiid = ? AND biid = ?
+ ");
+my $distance= sub {
+ my ($from,$to)= @_;
+ my $d= $dists{$from}{$to};
+ return $d if defined $d;
+ $distquery->execute($from,$to);
+ $d = $distquery->fetchrow_array();
+ defined $d or die "$from $to ?";
+ $dists{$from}{$to}= $d;
+ return $d;
+};