Redo aggregation

diff --git a/yarrg/web/lookup b/yarrg/web/lookup
index 5217679c7f4084d7844be4b1609e7be119b9449c..1453a372145ee2989261f757481aee295e052fb1 100755 (executable)
--- a/yarrg/web/lookup
+++ b/yarrg/web/lookup
@@ -74,7 +74,7 @@ my %styles;
                QuerySpecific => 1,
        }, {    Name => 'ShowStalls',
                Before => '',
                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,
                                [ 1, 'Show individual stalls' ],
                        ],
                QuerySpecific => 1,

diff --git a/yarrg/web/routetrade b/yarrg/web/routetrade
index 58f21a1a581a45ab3ae28a2d6caf4e8d991e07f1..91ff421625fd7d4757a6def0a6e471341ac073e1 100644 (file)
--- a/yarrg/web/routetrade
+++ b/yarrg/web/routetrade
@@ -118,21 +118,29 @@ my $stmt= "
        SELECT  sell_islands.islandname                         org_name,
                sell_islands.islandid                           org_id,
                sell.price                                      org_price,
        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,
                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} ? "
                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,
 ")."
                commods.commodname                              commodname,
                commods.commodid                                commodid,


@@ -141,27 +149,23 @@ my $stmt= "
                dist                                            dist,
                buy.price - sell.price                          unitprofit
        FROM commods
                dist                                            dist,
                buy.price - sell.price                          unitprofit
        FROM commods

-       JOIN buy  ON commods.commodid = buy.commodid

        JOIN sell ON commods.commodid = sell.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
        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 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
        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,
        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);
      ";
 
 my $sth= $dbh->prepare($stmt);


@@ -191,7 +195,7 @@ if ($qa->{ShowStalls}) {
 }
 $addcols->({ Text => 1 }, qw(commodname));
 $addcols->({ DoReverse => 1 },
 }
 $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
        ));
 $addcols->({ DoReverse => 1, SortColKey => 'MarginSortKey' },
        qw(     Margin


@@ -212,25 +216,48 @@ $addcols->({ DoReverse => 1 },
 
 <& dumptable:start, qa => $qa, sth => $sth &>
 % {
 
 <& dumptable:start, qa => $qa, sth => $sth &>
 % {

-%   my $f;
-%   while ($f= $sth->fetchrow_hashref()) {
+%   my $got;
+%   while ($got= $sth->fetchrow_hashref()) {

 <%perl>
 
 <%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'}
 
        $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;
        }
 
                $f->{Suppress}= 1;
        }
 

-       push @flows, $f;
-
+}

 </%perl>
 </%perl>

-<& dumptable:row, qa => $qa, sth => $sth, row => $f &>
-%   }
-<& dumptable:end, qa => $qa &>
-% }

 
 % my $optimise= $specific && !$confusing && @islandids>1;
 % if (!$optimise) {
 
 % my $optimise= $specific && !$confusing && @islandids>1;
 % if (!$optimise) {


@@ -362,7 +384,7 @@ foreach my $flow (@flows) {
                );
                        
                push @{ $avail_csts{$cstname}{Flows} }, $flow->{Var};
                );
                        
                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) {
        }
 }
 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) {
 <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) {
 %      $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 };
 %              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->{'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++) {
 %      }
 %      if (%todo && !$age_reported++) {

-<td colspan=2>

 %              my $age= $now - (values %todo)[0]->{Timestamp};
 %              my $cellid= "da_${i}";
 %              $da_ages{$cellid}= $age;
 %              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;
 (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};
 %      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>
 %              $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
 %      }
 %    }
 <td align=right><% $total |h %> total
 %      }
 %    }