}
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
+ SELECT sell_islands.islandname org_name,
+ sell_islands.islandid org_id,
+ sell.price org_price,
+ sum(sell.qty) org_qty,
+ buy_islands.islandname dst_name,
+ buy_islands.islandid dst_id,
+ buy.price dst_price,
+ sum(buy.qty) dst_qty,
+ commods.commodname commodname,
+ commods.commodid commodid,
+ commods.unitmass mass,
+ commods.unitvolume volume,
+ buy.price - sell.price unitprofit,
+ min(sell.qty,buy.qty) tqty,
+ min(sell.qty,buy.qty) * (buy.price-sell.price) profit
FROM commods
JOIN buy on commods.commodid = buy.commodid
JOIN sell on commods.commodid = sell.commodid
OR ", @flow_conds)."
)
AND buy.price > sell.price
- GROUP BY commodname, commods.commodid,
- org_id, org_price, dst_id, dst_price
+ GROUP BY commods.commodid, org_id, org_price, dst_id, dst_price
+ ORDER BY org_name, dst_name, profit DESC, commodname,
+ org_price, dst_price DESC
";
my $sth= $dbh->prepare($stmt);
$sth->execute(@query_params);
+my @flows;
</%perl>
+
% if ($qa->{'debug'}) {
<pre>
<% $stmt |h %>
</pre>
% }
-<& dumpqueryresults, sth =>$sth &>
+<& dumpqueryresults:start, sth => $sth &>
+% my $flow;
+% while ($flow= $sth->fetchrow_hashref()) {
+% push @flows, $flow;
+<& dumpqueryresults:row, sth => $sth, row => $flow &>
+% }
+<& dumpqueryresults:end &>
+
+<%perl>
+
+</%perl>
<%init>
use CommodsWeb;