========== TODO ==========
16:36 <ceb> alpha,byrne,papaya,turtle,jorvik,luthien is my example
-16:46 <ceb> Also trading plan not functional but I guess you know that :-)
-
use POST for update. Hrrm.
LATER OR NOT AT ALL
@archipelagoes
$qa
</%args>
+<&| script &>
+ da_pageload= Date.now();
+</&script>
+
<%perl>
+my $now= time;
my $loss_per_league= 1e-7;
my @flow_conds;
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
- Margin unitprofit MaxQty
+ 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
));
<& 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"}
+ ;
+ }
- $f->{MaxQty}= $f->{'org_qty'} < $f->{'dst_qty'}
- ? $f->{'org_qty'} : $f->{'dst_qty'};
+</%perl>
+<& dumptable:row, qa => $qa, sth => $sth, row => $f &>
+% }
+<& dumptable:end, qa => $qa &>
+% }
+
+<%perl>
+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->{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->{"org_stallid"}= $f->{"dst_stallid"}= 'all'
- if !$qa->{ShowStalls};
-
$f->{ExpectedUnitProfit}=
$f->{'dst_price'} * (1.0 - $loss_per_league) ** $f->{'dist'}
- - $f->{'src_price'};
+ - $f->{'org_price'};
my @uid= $f->{commodid};
foreach my $od (qw(org dst)) {
$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) {
% my $v= $flow->{$col->{Name}};
% $col->{Total} += $v if defined $col->{Total};
% $v='' if !$col->{Text} && !$v;
-% $ts_sortkeys{$ci}{$rowid}= $v;
+% my $sortkey= $col->{SortColKey} ?
+% $flow->{$col->{SortColKey}} : $v;
+% $ts_sortkeys{$ci}{$rowid}= $sortkey;
<td <% $col->{Text} ? '' : 'align=right' %>><% $v |h %>
% }
% }
%
% my $iquery= $dbh->prepare('SELECT islandname FROM islands
% WHERE islandid = ?');
+% my %da_ages;
%
<h1>Voyage trading plan</h1>
<table>
% foreach my $i (0..$#islandids) {
-<tr><td colspan=4><strong>
+<tr><td colspan=3><strong>
% $iquery->execute($islandids[$i]);
% my ($islandname) = $iquery->fetchrow_array();
% if (!$i) {
Sail to <% $islandname |h %>
% }
</strong>
+% my $age_reported= 0;
% foreach my $od (qw(dst org)) {
% my $sign= $od eq 'dst' ? -1 : +1;
% my %todo;
% 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->{Price}= $price;
+% $$todo->{Timestamp}= $f->{"${od}_timestamp"};
% $$todo->{Qty} += $f->{OptQty};
-% $$todo->{Total} = $$todo->{Price} * $$todo->{Qty};
-% $$todo->{Timestamp} = $f->{"${od}_timestamp"};
+% $$todo->{Total}= $$todo->{Price} * $$todo->{Qty};
+% $$todo->{Stalls}= $f->{"${od}Stalls"};
+% }
+% if (%todo && !$age_reported++) {
+% my $age= $now - (values %todo)[0]->{Timestamp};
+% my $cellid= "da_${i}";
+% $da_ages{$cellid}= $age;
+<td colspan=2>\
+(Data age: <span id="<% $cellid %>"><% prettyprint_age($age) %></span>)
% }
% my $total= 0;
% my $dline= 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]];
+% my $avail= $t->{Stalls}{$name};
+<td align=right><% $avail |h %> <% $od eq 'org' ? 'avail.' : 'wanted' %>
+<td><% $name |h %>
+% };
+%
+% $pstall->(0);
+<<% $td %> align=right><% $t->{Price} |h %> each
+<<% $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>
-<td colspan=<% 2+!!$qa->{ShowStalls} %>>
-<% (values %todo)[0]->{Timestamp} %>
-<td align=right>
-% if ($od eq 'org') {
-Outlay
-% } else {
-Proceeds
-% }
+<tr>
+<td colspan=5><td align=right><% $od eq 'org' ? 'Outlay' : 'Proceeds' %>
<td align=right><% $total |h %> total
% }
% }
% }
</table>
+<& query_age:dataages, id2age => \%da_ages &>
%
% } # ========== TRADING PLAN ==========