+<&| script &>
+ da_pageload= Date.now();
+</&script>
+
+<%perl>
+
+my $loss_per_league= defined $routeparams->{LossPerLeaguePct}
+ ? $routeparams->{LossPerLeaguePct}*0.01 : 1e-7;
+my $loss_per_delay_slot= 1e-8;
+
+my $now= time;
+
+my @flow_conds;
+my @query_params;
+my %dists;
+my $expected_total_profit;
+
+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 $specific= !grep { !defined $_ } @islandids;
+
+my %ipair2subflowinfs;
+# $ipair2subflowinfs{$orgi,$dsti}= [ [$orgix,$distix], ... ]
+
+my @subflows;
+# $subflows[0]{Flow} = { ... }
+# $subflows[0]{Org} = $orgix
+# $subflows[0]{Dst} = $dstix
+
+foreach my $org_i (0..$#islandids) {
+ my $org_isle= $islandids[$org_i];
+ my $org_cond= $sd_condition->('sell',$org_i);
+ my @dst_conds;
+ foreach my $dst_i ($org_i..$#islandids) {
+ my $dst_isle= $islandids[$dst_i];
+ # Don't ever consider sailing things round the houses:
+ next if defined $dst_isle and
+ grep { $dst_isle == $_ } @islandids[$org_i..$dst_i-1];
+ next if defined $org_isle and
+ grep { $org_isle == $_ } @islandids[$org_i+1..$dst_i];
+ my $dst_cond;
+ if ($dst_i==$org_i and !defined $org_isle) {
+ # we always want arbitrage, but mentioning an arch
+ # once shouldn't produce intra-arch trades
+ $dst_cond= "sell.islandid = buy.islandid";
+ } else {
+ $dst_cond= $sd_condition->('buy',$dst_i);
+ }
+ push @dst_conds, $dst_cond;
+
+ if ($specific) {
+ push @{ $ipair2subflowinfs{$org_isle,$dst_isle} },
+ [ $org_i, $dst_i ];
+ }
+ }
+ push @flow_conds, "$org_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;
+};
+
+my @cols= ({ NoSort => 1 });
+
+my $addcols= sub {
+ my $base= shift @_;
+ foreach my $name (@_) {
+ my $col= { Name => $name, %$base };
+ $col->{Numeric}=1 if !$col->{Text};
+ push @cols, $col;
+ }
+};
+
+if ($qa->{ShowStalls}) {
+ $addcols->({ Text => 1 }, qw(
+ org_name org_stallname
+ dst_name dst_stallname
+ ));
+} else {
+ $addcols->({Text => 1 }, qw(
+ org_name dst_name
+ ));
+}
+$addcols->({ Text => 1 }, qw(commodname));
+$addcols->({ DoReverse => 1 },
+ qw( org_price org_qty_agg dst_price dst_qty_agg
+ ));
+$addcols->({ DoReverse => 1, SortColKey => 'MarginSortKey' },
+ qw( Margin
+ ));
+$addcols->({ DoReverse => 1 },
+ qw( unitprofit MaxQty MaxCapital MaxProfit dist
+ ));
+foreach my $v (qw(MaxMass MaxVolume)) {
+ $addcols->({
+ DoReverse => 1, Total => 0, SortColKey => "${v}SortKey" }, $v);
+}
+
+</%perl>
+
+% if ($qa->{'debug'}) {
+<pre>
+<% $stmt |h %>
+<% join(' | ',@query_params) |h %>
+</pre>
+% }
+
+<& dumptable:start, qa => $qa, sth => $sth &>
+% {
+% my $got;
+% while ($got= $sth->fetchrow_hashref()) {
+<%perl>
+
+ my $f= $flows[$#flows];
+ if ( !$f ||
+ $qa->{ShowStalls} ||
+ grep { $f->{$_} ne $got->{$_} }
+ qw(org_id org_price dst_id dst_price commodid)
+ ) {
+ # Make a new flow rather than adding to the existing one
+
+ $f= {
+ Ix => scalar(@flows),
+ %$got
+ };
+ $f->{"org_stallid"}= $f->{"dst_stallid"}= 'all'
+ if !$qa->{ShowStalls};
+ push @flows, $f;
+ }
+ foreach my $od (qw(org dst)) {
+ $f->{"${od}Stalls"}{
+ $got->{"${od}_stallname"}
+ } =
+ $got->{"${od}_qty_stall"}
+ ;
+ }
+
+</%perl>
+<& dumptable:row, qa => $qa, sth => $sth, row => $f &>
+% }
+<& dumptable:end, qa => $qa &>
+% }
+
+<%perl>
+
+my @sail_total;
+
+if (!@flows) {
+ print 'No profitable trading opportunities were found.';
+ return;
+}
+
+foreach my $f (@flows) {
+
+ $f->{MaxQty}= $f->{'org_qty_agg'} < $f->{'dst_qty_agg'}
+ ? $f->{'org_qty_agg'} : $f->{'dst_qty_agg'};
+ $f->{MaxProfit}= $f->{MaxQty} * $f->{'unitprofit'};
+ $f->{MaxCapital}= $f->{MaxQty} * $f->{'org_price'};
+
+ $f->{MaxMassSortKey}= $f->{MaxQty} * $f->{'unitmass'};
+ $f->{MaxVolumeSortKey}= $f->{MaxQty} * $f->{'unitvolume'};
+ foreach my $v (qw(Mass Volume)) {
+ $f->{"Max$v"}= sprintf "%.1f", $f->{"Max${v}SortKey"} * 1e-6;
+ }
+
+ my $sfis= $ipair2subflowinfs{$f->{'org_id'},$f->{'dst_id'}};
+ foreach my $sfi (@$sfis) {
+ my $subflow= {
+ Flow => $f,
+ Org => $sfi->[0],
+ Dst => $sfi->[1],
+ Var => sprintf "f%ss%s_c%d_p%d_%d_p%d_%d",
+ $f->{Ix}, $sfi->[0],
+ $f->{'commodid'},
+ $sfi->[0], $f->{'org_price'},
+ $sfi->[1], $f->{'dst_price'}
+ };
+ push @{ $f->{Subflows} }, $subflow;
+ push @subflows, $subflow;
+ }
+
+ $f->{MarginSortKey}= sprintf "%d",
+ $f->{'dst_price'} * 10000 / $f->{'org_price'};
+ $f->{Margin}= sprintf "%3.1f%%",
+ $f->{'dst_price'} * 100.0 / $f->{'org_price'} - 100.0;
+
+ $f->{ExpectedUnitProfit}=
+ $f->{'dst_price'} * (1.0 - $loss_per_league) ** $f->{'dist'}
+ - $f->{'org_price'};
+
+ $dists{'org_id'}{'dst_id'}= $f->{'dist'};