From 2007f48bd68be9218b8f41acd9ba32d378a1b22c Mon Sep 17 00:00:00 2001 From: Ian Jackson Date: Sun, 23 Aug 2009 13:07:11 +0100 Subject: [PATCH] Allow showing stall names in routetrade --- yarrg/web/lookup | 8 +++- yarrg/web/query_commod | 3 +- yarrg/web/query_route | 7 ++- yarrg/web/routetrade | 99 ++++++++++++++++++++++++++++++++++++------ 4 files changed, 98 insertions(+), 19 deletions(-) diff --git a/yarrg/web/lookup b/yarrg/web/lookup index a56adf7..683fd12 100755 --- a/yarrg/web/lookup +++ b/yarrg/web/lookup @@ -68,10 +68,16 @@ my %styles; QuerySpecific => 1, }, { Name => 'ShowBlank', Before => '', - Values => [ [ 'omit', 'Omit islands with no offers' ], + Values => [ [ 0, 'Omit islands with no offers' ], [ 'show', 'Show all islands' ], ], QuerySpecific => 1, + }, { Name => 'ShowStalls', + Before => '', + Values => [ [ 0, 'Show amount at each price' ], + [ 1, 'Show individual stalls' ], + ], + QuerySpecific => 1, }); foreach my $var (@vars) { diff --git a/yarrg/web/query_commod b/yarrg/web/query_commod index 052d019..0bd36cb 100644 --- a/yarrg/web/query_commod +++ b/yarrg/web/query_commod @@ -97,8 +97,7 @@ foreach my $bs (split /_/, $ARGS{BuySell}) { my ($ascdesc) = ($bs eq 'buy') ? ('DESC') : ('ASC'); - my $joinkind= $ARGS{ShowBlank} eq 'show' - ? 'LEFT OUTER JOIN' : 'INNER JOIN'; + my $joinkind= $ARGS{ShowBlank} ? 'LEFT OUTER JOIN' : 'INNER JOIN'; my $islands= $dbh->prepare( "SELECT islands.islandid AS islandid, archipelago, islandname, sum(qty) as tqty diff --git a/yarrg/web/query_route b/yarrg/web/query_route index ee726a9..b2c52b1 100644 --- a/yarrg/web/query_route +++ b/yarrg/web/query_route @@ -36,6 +36,7 @@ <%args> $quri $dbh +$prselector $routestring => ''; $someresults $emsgokorprint @@ -50,11 +51,13 @@ my %islandid2; my $qa= \%ARGS; +

Specify route

+ +% $prselector->('ShowStalls'); + %#---------- textbox, user enters route as string ---------- % if (!$qa->{Dropdowns}) { -

Specify route

- Enter route (islands, or archipelagoes, separated by |s or commas; abbreviations are OK):
diff --git a/yarrg/web/routetrade b/yarrg/web/routetrade index 9c2a5a4..c5c2ab7 100644 --- a/yarrg/web/routetrade +++ b/yarrg/web/routetrade @@ -98,30 +98,43 @@ my $stmt= " 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, +".($qa->{ShowStalls} ? " + sell.stallid org_stallid, + sell_stalls.stallname org_stallname, + sell.qty org_qty, + buy.stallid dst_stallid, + buy_stalls.stallname dst_stallname, + buy.qty dst_qty, +" : " + sum(sell.qty) org_qty, sum(buy.qty) dst_qty, +")." commods.commodname commodname, commods.commodid commodid, commods.unitmass unitmass, commods.unitvolume unitvolume, - buy.price - sell.price unitprofit, - min(sell.qty,buy.qty) max_qty, - min(sell.qty,buy.qty) * (buy.price-sell.price) max_profit + buy.price - sell.price unitprofit 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 +".($qa->{ShowStalls} ? " + JOIN stalls as sell_stalls on sell.stallid = sell_stalls.stallid + JOIN stalls as buy_stalls on buy.stallid = buy_stalls.stallid +" : "")." WHERE ( ".join(" OR ", @flow_conds)." ) AND buy.price > sell.price +".($qa->{ShowStalls} ? "" : " GROUP BY commods.commodid, org_id, org_price, dst_id, dst_price - ORDER BY org_name, dst_name, max_profit DESC, commodname, +")." + ORDER BY org_name, dst_name, commodname, unitprofit DESC, org_price, dst_price DESC "; @@ -129,9 +142,16 @@ my $sth= $dbh->prepare($stmt); $sth->execute(@query_params); my @flows; -my @columns= qw(org_name dst_name commodname - org_price org_qty dst_price dst_qty - max_qty max_profit); +my @columns; +if ($qa->{ShowStalls}) { + push @columns, qw(org_name org_stallname dst_name dst_stallname); +} else { + push @columns, qw(org_name dst_name); +} +push @columns, qw(commodname + org_price org_qty dst_price dst_qty + unitprofit PctProfit + MaxQty MaxCapital MaxProfit); @@ -154,7 +174,8 @@ my @columns= qw(org_name dst_name commodname <& dumptable:end &> % } -% if (!$specific || $confusing || @islandids<=1) { +% my $optimise= $specific && !$confusing && @islandids>1; +% if (!$optimise) {

% if (@islandids<=1) { @@ -242,8 +263,9 @@ if ($qa->{'debug'}) { my ($ix, $qty) = m/^\s*\d+\s+f(\d+)\s+\S+\s+(\d+)\s/ or die "$_ ?"; my $flow= $flows[$ix] or die; - $flow->{Opt_qty}= $qty; - $flow->{Opt_profit}= $flow->{'unitprofit'} * $qty; + $flow->{OptQty}= $qty; + $flow->{OptProfit}= $flow->{'unitprofit'} * $qty; + $flow->{OptCapital}= $flow->{OptQty} * $flow->{'org_price'}; } print "\n" if $qa->{'debug'}; pipethrough_run_finish($output, 'glpsol'); @@ -252,14 +274,63 @@ if ($qa->{'debug'}) { print join ' ', map { $_->{Optimal} } @flows; -push @columns, qw(Opt_qty Opt_profit); +push @columns, qw(OptQty OptCapital OptProfit); -<& dumptable:literal, cols => \@columns, rows => \@flows &> - % } # ========== OPTIMISATION ========== +% { +% my $cdspan= $qa->{ShowStalls} ? ' colspan=2' : ''; +% my $cdstall= $qa->{ShowStalls} ? 'Stall' : ''; + + +>Collect +>Deliver + + +% foreach my $col (@columns) { +% $flow->{MaxQty}= $flow->{'org_qty'} < $flow->{'dst_qty'} +% ? $flow->{'org_qty'} : $flow->{'dst_qty'}; +% $flow->{MaxProfit}= $flow->{MaxQty} * $flow->{'unitprofit'}; +% $flow->{PctProfit}= sprintf "%3.1f%%", +% $flow->{'dst_price'} * 100.0 / $flow->{'org_price'} +% - 100.0; +% $flow->{MaxCapital}= $flow->{MaxQty} * $flow->{'org_price'}; +
+Collect +Deliver +Profit +Max +% if ($optimise) { +Suggested +% } + +
Island <% $cdstall %> +Island <% $cdstall %> +Commodity +Price +Qty +Price +Qty +Unit +Margin +Qty +Capital +Profit +% if ($optimise) { +Qty +Capital +Profit +% } +% } + +% foreach my $flow (@flows) { +
<% $flow->{$col} |h %> +% } +% } +
+ <%init> use CommodsWeb; use Commods; -- 2.30.2