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}_islands.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                 push @dst_conds, $sd_condition->('buy',$dst_i);
  27         }
  28         push @flow_conds, "$src_cond AND (
  29                         ".join("
  30                      OR ",@dst_conds)."
  31                 )";
  32 }
  33
  34 my $stmt= "
  35         SELECT  commods.commodname              commodname,
  36                 commods.commodid                commodid,
  37                 commods.unitmass                mass,
  38                 commods.unitvolume              volume,
  39                 sell_islands.islandid           org_id,
  40                 sell_islands.islandname         org_name,
  41                 sell.price                      org_price,
  42                 sum(sell.qty)                   org_qty,
  43                 buy_islands.islandid            dst_id,
  44                 buy_islands.islandname          dst_name,
  45                 buy.price                       dst_price,
  46                 sum(buy.qty)                    dst_qty
  47         FROM commods
  48         JOIN buy  on commods.commodid = buy.commodid
  49         JOIN sell on commods.commodid = sell.commodid
  50         JOIN islands as sell_islands on sell.islandid = sell_islands.islandid
  51         JOIN islands as buy_islands  on buy.islandid  = buy_islands.islandid
  52         WHERE   (
  53                 ".join("
  54            OR   ", @flow_conds)."
  55         )
  56           AND   buy.price > sell.price
  57         GROUP BY commodname, commods.commodid,
  58                 org_id, org_price, dst_id, dst_price
  59      ";
  60
  61 my $sth= $dbh->prepare($stmt);
  62 $sth->execute(@query_params);
  63
  64 </%perl>
  65 <pre>
  66 <% $stmt |h %>
  67 <% join(' | ',@query_params) |h %>
  68 </pre>
  69
  70 <& dumpqueryresults, sth =>$sth &>
  71
  72 <%init>
  73 use CommodsWeb;
  74 </%init>