chiark / gitweb /
Redo aggregation
authorIan Jackson <ijackson@chiark.greenend.org.uk>
Mon, 24 Aug 2009 13:08:04 +0000 (14:08 +0100)
committerIan Jackson <ijackson@chiark.greenend.org.uk>
Mon, 24 Aug 2009 13:08:04 +0000 (14:08 +0100)
yarrg/web/lookup
yarrg/web/routetrade

index 5217679..1453a37 100755 (executable)
@@ -74,7 +74,7 @@ my %styles;
                QuerySpecific => 1,
        }, {    Name => 'ShowStalls',
                Before => '',
-               Values => [     [ 0, 'Show amount at each price' ],
+               Values => [     [ 0, 'Show total quantity at each price' ],
                                [ 1, 'Show individual stalls' ],
                        ],
                QuerySpecific => 1,
index 58f21a1..91ff421 100644 (file)
@@ -118,21 +118,29 @@ 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.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,
@@ -141,27 +149,23 @@ my $stmt= "
                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);
@@ -191,7 +195,7 @@ if ($qa->{ShowStalls}) {
 }
 $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
@@ -212,25 +216,48 @@ $addcols->({ DoReverse => 1 },
 
 <& 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'};
-       $f->{MaxProfit}= $f->{MaxQty} * $f->{'unitprofit'};
-       $f->{MaxCapital}= $f->{MaxQty} * $f->{'org_price'};
+</%perl>
+<& dumptable:row, qa => $qa, sth => $sth, row => $f &>
+%    }
+<& dumptable:end, qa => $qa &>
+% }
 
-       $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;
+<%perl>
+foreach my $f (@flows) {
 
-       $f->{"org_stallid"}= $f->{"dst_stallid"}= 'all'
-               if !$qa->{ShowStalls};
+       $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->{ExpectedUnitProfit}=
                $f->{'dst_price'} * (1.0 - $loss_per_league) ** $f->{'dist'}
@@ -304,13 +331,8 @@ die "$cmpu $uue ?" if length $cmpu > 20;
                $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) {
@@ -362,7 +384,7 @@ foreach my $flow (@flows) {
                );
                        
                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) {
@@ -538,7 +560,7 @@ $addcols->({ Total => 0, DoReverse => 1 }, qw(
 <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) {
@@ -560,19 +582,20 @@ Sail to <% $islandname |h %>
 %              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=2>\
 (Data age: <span id="<% $cellid %>"><% prettyprint_age($age) %></span>)
 %      }
 %      my $total= 0;
@@ -580,30 +603,35 @@ Sail to <% $islandname |h %>
 %      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 colspan=<% 3+!!$qa->{ShowStalls} %>>
-<td align=right>
-%              if ($od eq 'org') {
-Outlay
-%              } else {
-Proceeds
-%              }
+<td colspan=5><td align=right><% $od eq 'org' ? 'Outlay' : 'Proceeds' %>
 <td align=right><% $total |h %> total
 %      }
 %    }