yarrg/web/routetrade

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