WIP route trade finding: does query to find flows

[ypp-sc-tools.main.git] / yarrg / web / routetrade

diff --git a/yarrg/web/routetrade b/yarrg/web/routetrade
index 1c5a3e49db2b184ac019b4a8177cb8838df59040..ad826579c0907e48b219d4270d6e4ec203dbdff8 100644 (file)
--- a/yarrg/web/routetrade
+++ b/yarrg/web/routetrade
@@ -3,24 +3,72 @@ $dbh
 @islandids
 @archipelagoes
 </%args>
+<%perl>
 
-%# So, add code to do right thing here:
+my @flow_conds;
+my @query_params;
 
-<pre>
-Route is as follows:
-
-% foreach my $i (0..$#islandids) {
-%   my $islandid= $islandids[$i];
-%   my $archipelago= $archipelagoes[$i];
-%   $islandid= '<undef>' unless defined $islandid;
-%   $archipelago= '<undef>' unless defined $archipelago;
-%
-  islandid=<% $islandid |h %>  archipelago=<% $archipelago |h %>
-%
-% }
+my $sd_condition= sub {
+       my ($bs, $ix) = @_;
+       my $islandid= $islandids[$ix];
+       if (defined $islandid) {
+               return "${bs}_islands.islandid = $islandid";
+       } else {
+               push @query_params, $archipelagoes[$ix];
+               return "${bs}_islands.archipelago = ?";
+       }
+};
+
+foreach my $src_i (0..$#islandids) {
+       my $src_cond= $sd_condition->('sell',$src_i);
+       my @dst_conds;
+       foreach my $dst_i ($src_i..$#islandids) {
+               push @dst_conds, $sd_condition->('buy',$dst_i);
+       }
+       push @flow_conds, "$src_cond AND (
+                       ".join("
+                    OR ",@dst_conds)."
+               )";
+}
+
+my $stmt= "            
+       SELECT  commods.commodname              commodname,
+               commods.commodid                commodid,
+               commods.unitmass                mass,
+               commods.unitvolume              volume,
+               sell_islands.islandid           org_id,
+               sell_islands.islandname         org_name,
+               sell.price                      org_price,
+               sum(sell.qty)                   org_qty,
+               buy_islands.islandid            dst_id,
+               buy_islands.islandname          dst_name,
+               buy.price                       dst_price,
+               sum(buy.qty)                    dst_qty
+       FROM commods
+       JOIN buy  on commods.commodid = buy.commodid
+       JOIN sell on commods.commodid = sell.commodid
+       JOIN islands as sell_islands on sell.islandid = sell_islands.islandid
+       JOIN islands as buy_islands  on buy.islandid  = buy_islands.islandid
+       WHERE   (
+               ".join("
+          OR   ", @flow_conds)."
+       )
+         AND   buy.price > sell.price
+       GROUP BY commodname, commods.commodid,
+               org_id, org_price, dst_id, dst_price
+     ";
 
+my $sth= $dbh->prepare($stmt);
+$sth->execute(@query_params);
+
+</%perl>
+<pre>
+<% $stmt |h %>
+<% join(' | ',@query_params) |h %>
 </pre>
 
+<& dumpqueryresults, sth =>$sth &>
+
 <%init>
 use CommodsWeb;
 </%init>