yarrg/web/routetrade

   1 <%args>
   2 $dbh
   3 @islandids
   4 @archipelagoes
   5 $qa
   6 </%args>
   7 <%perl>
   8
   9 my @flow_conds;
  10 my @query_params;
  11
  12 my $sd_condition= sub {
  13         my ($bs, $ix) = @_;
  14         my $islandid= $islandids[$ix];
  15         if (defined $islandid) {
  16                 return "${bs}.islandid = $islandid";
  17         } else {
  18                 push @query_params, $archipelagoes[$ix];
  19                 return "${bs}_islands.archipelago = ?";
  20         }
  21 };
  22
  23 foreach my $src_i (0..$#islandids) {
  24         my $src_cond= $sd_condition->('sell',$src_i);
  25         my @dst_conds;
  26         foreach my $dst_i ($src_i..$#islandids) {
  27                 my $dst_cond= $sd_condition->('buy',$dst_i);
  28                 if ($dst_i==$src_i and !defined $islandids[$src_i]) {
  29                         # we always want arbitrage, but mentioning an arch
  30                         # once shouldn't produce intra-arch trades
  31                         $dst_cond=
  32                                 "($dst_cond AND sell.islandid = buy.islandid)";
  33                 }
  34                 push @dst_conds, $dst_cond;
  35         }
  36         push @flow_conds, "$src_cond AND (
  37                         ".join("
  38                      OR ",@dst_conds)."
  39                 )";
  40 }
  41
  42 my $stmt= "
  43         SELECT  commods.commodname              commodname,
  44                 commods.commodid                commodid,
  45                 commods.unitmass                mass,
  46                 commods.unitvolume              volume,
  47                 sell_islands.islandid           org_id,
  48                 sell_islands.islandname         org_name,
  49                 sell.price                      org_price,
  50                 sum(sell.qty)                   org_qty,
  51                 buy_islands.islandid            dst_id,
  52                 buy_islands.islandname          dst_name,
  53                 buy.price                       dst_price,
  54                 sum(buy.qty)                    dst_qty
  55         FROM commods
  56         JOIN buy  on commods.commodid = buy.commodid
  57         JOIN sell on commods.commodid = sell.commodid
  58         JOIN islands as sell_islands on sell.islandid = sell_islands.islandid
  59         JOIN islands as buy_islands  on buy.islandid  = buy_islands.islandid
  60         WHERE   (
  61                 ".join("
  62            OR   ", @flow_conds)."
  63         )
  64           AND   buy.price > sell.price
  65         GROUP BY commodname, commods.commodid,
  66                 org_id, org_price, dst_id, dst_price
  67      ";
  68
  69 my $sth= $dbh->prepare($stmt);
  70 $sth->execute(@query_params);
  71
  72 </%perl>
  73 % if ($qa->{'debug'}) {
  74 <pre>
  75 <% $stmt |h %>
  76 <% join(' | ',@query_params) |h %>
  77 </pre>
  78 % }
  79
  80 <& dumpqueryresults, sth =>$sth &>
  81
  82 <%init>
  83 use CommodsWeb;
  84 </%init>