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.stallid org_stallid,
- sell_stalls.stallname org_stallname,
- sell.qty org_qty,
- buy.stallid dst_stallid,
- buy_stalls.stallname dst_stallname,
- buy.qty dst_qty,
+ sell.qty org_qty_agg,
+ buy.qty dst_qty_agg,
" : "
- sum(sell.qty) org_qty,
- sum(buy.qty) dst_qty,
+ (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,
dist dist,
buy.price - sell.price unitprofit
FROM commods
- JOIN buy ON commods.commodid = buy.commodid
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
-".($qa->{ShowStalls} ? "
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
-".($qa->{ShowStalls} ? "" : "
- GROUP BY commods.commodid, org_id, org_price, dst_id, dst_price
-")."
ORDER BY org_name, dst_name, commodname, unitprofit DESC,
- org_price, dst_price DESC
+ org_price, dst_price DESC,
+ org_stallname, dst_stallname
";
my $sth= $dbh->prepare($stmt);
}
$addcols->({ Text => 1 }, qw(commodname));
$addcols->({ DoReverse => 1 },
- qw( org_price org_qty dst_price dst_qty
+ qw( org_price org_qty_agg dst_price dst_qty_agg
));
$addcols->({ DoReverse => 1, SortColKey => 'MarginSortKey' },
qw( Margin
<& dumptable:start, qa => $qa, sth => $sth &>
% {
-% my $f;
-% while ($f= $sth->fetchrow_hashref()) {
+% my $got;
+% while ($got= $sth->fetchrow_hashref()) {
<%perl>
- $f->{Ix}= @flows;
- $f->{Var}= "f$f->{Ix}";
+ 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),
+ Var => "f".@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>
+foreach my $f (@flows) {
- $f->{MaxQty}= $f->{'org_qty'} < $f->{'dst_qty'}
- ? $f->{'org_qty'} : $f->{'dst_qty'};
+ $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->{Margin}= sprintf "%3.1f%%",
$f->{'dst_price'} * 100.0 / $f->{'org_price'} - 100.0;
- $f->{"org_stallid"}= $f->{"dst_stallid"}= 'all'
- if !$qa->{ShowStalls};
-
$f->{ExpectedUnitProfit}=
$f->{'dst_price'} * (1.0 - $loss_per_league) ** $f->{'dist'}
- $f->{'org_price'};
$f->{Suppress}= 1;
}
- push @flows, $f;
-
+}
</%perl>
-<& dumptable:row, qa => $qa, sth => $sth, row => $f &>
-% }
-<& dumptable:end, qa => $qa &>
-% }
% my $optimise= $specific && !$confusing && @islandids>1;
% if (!$optimise) {
);
push @{ $avail_csts{$cstname}{Flows} }, $flow->{Var};
- $avail_csts{$cstname}{Qty}= $flow->{"${od}_qty"};
+ $avail_csts{$cstname}{Qty}= $flow->{"${od}_qty_agg"};
}
}
foreach my $cstname (sort keys %avail_csts) {
<h1>Voyage trading plan</h1>
<table>
% foreach my $i (0..$#islandids) {
-<tr><td colspan=<% 2+!!$qa->{ShowStalls} %>><strong>
+<tr><td colspan=3><strong>
% $iquery->execute($islandids[$i]);
% my ($islandname) = $iquery->fetchrow_array();
% if (!$i) {
% my $todo= \$todo{ $f->{'commodname'},
% (sprintf "%07d", $price),
% $stallname };
-% $$todo= { } unless $$todo;
+% $$todo= { Qty => 0 } unless $$todo;
% $$todo->{'commodname'}= $f->{'commodname'};
% $$todo->{'stallname'}= $stallname;
% $$todo->{Price}= $price;
% $$todo->{Timestamp}= $f->{"${od}_timestamp"};
% $$todo->{Qty} += $f->{OptQty};
% $$todo->{Total}= $$todo->{Price} * $$todo->{Qty};
+% $$todo->{Stalls}= $f->{"${od}Stalls"};
% }
% if (%todo && !$age_reported++) {
-<td colspan=2>
% my $age= $now - (values %todo)[0]->{Timestamp};
% my $cellid= "da_${i}";
% $da_ages{$cellid}= $age;
+<td colspan=3 align=right>\
(Data age: <span id="<% $cellid %>"><% prettyprint_age($age) %></span>)
% }
% my $total= 0;
% foreach my $tkey (sort keys %todo) {
% my $t= $todo{$tkey};
% $total += $t->{Total};
-<tr class="datarow<% $dline %>"><td>
-% if ($od eq 'org') {
-Collect
-% } else {
-Deliver
-% }
-<td><% $t->{'commodname'} |h %>
-<td align=right><% $t->{Price} |h %> each
-% if ($qa->{ShowStalls}) {
-<td><% $t->{'stallname'} |h %>
+% my $span= 0 + keys %{ $t->{Stalls} };
+% my $td= "td rowspan=$span";
+<tr class="datarow<% $dline %>">
+<<% $td %>><% $od eq 'org' ? 'Collect' : 'Deliver' %>
+<<% $td %>><% $t->{'commodname'} |h %>
+%
+% my @stalls= sort keys %{ $t->{Stalls} };
+% my $pstall= sub {
+% my $name= $stalls[$_[0]];
+<td><% $name |h %>
+% };
+%
+% $pstall->(0);
+<<% $td %> align=right><% $t->{Price} |h %> poe ea.
+<<% $td %> align=right><% $t->{Qty} |h %> unit(s)
+<<% $td %> align=right><% $t->{Total} |h %> total
+%
+% foreach my $stallix (1..$#stalls) {
+<tr class="datarow<% $dline %>">
+% $pstall->($stallix);
% }
-<td align=right><% $t->{Qty} |h %> unit(s)
-<td align=right><% $t->{Total} |h %> total
+%
% $dline ^= 1;
% }
% if (%todo) {
<tr>
-<td colspan=<% 3+!!$qa->{ShowStalls} %>>
-<td align=right>
-% if ($od eq 'org') {
-Outlay
-% } else {
-Proceeds
-% }
+<td colspan=4><td align=right><% $od eq 'org' ? 'Outlay' : 'Proceeds' %>
<td align=right><% $total |h %> total
% }
% }