}
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()) {
+% $flow->{Ix}= @flows;
+% $flow->{Var}= "f$flow->{Ix}";
+% push @flows, $flow;
+<& dumpqueryresults:row, sth => $sth, row => $flow &>
+% }
+<& dumpqueryresults:end &>
+% }
+
+<%perl>
+
+my $cplex= "
+Maximize
+
+ totalprofit:
+ ".(join " +
+ ", map { "$_->{profit} $_->{Var}" } @flows)."
+
+Subject To
+";
+
+my %avail_csts;
+foreach my $flow (@flows) {
+ foreach my $od (qw(org dst)) {
+ my $cstname= join '_',
+ 'avail',
+ $flow->{'commodid'},
+ $od,
+ $flow->{"${od}_id"},
+ $flow->{"${od}_price"};
+ push @{ $avail_csts{$cstname}{Flows} }, $flow->{Var};
+ $avail_csts{$cstname}{Qty}= $flow->{"${od}_qty"};
+ }
+}
+foreach my $cstname (sort keys %avail_csts) {
+ my $c= $avail_csts{$cstname};
+ $cplex .= "
+ ". sprintf("%-30s","$cstname:")." ".
+ join("+", @{ $c->{Flows} }).
+ " <= ".$c->{Qty}."\n";
+}
+
+$cplex.= "
+Bounds
+ ".(join "
+ ", map { "$_->{Var} >= 0" } @flows)."
+
+End
+";
+# glpsol --cpxlp /dev/stdin <t.cplex -o /dev/stdout
+
+</%perl>
+<pre>
+<% $cplex |h %>
+</pre>
<%init>
use CommodsWeb;