X-Git-Url: http://www.chiark.greenend.org.uk/ucgi/~yarrgweb/git?a=blobdiff_plain;f=yarrg%2Fweb%2Fquery_commod;h=a4063c12204e703bbb748e07e3d444c3c0f89bc3;hb=529d79634e714733d47e4cbcd385a727e9fae703;hp=aeee3cee83d0bf8288ee79903ec005e20b36f4fc;hpb=608bc9e07b5c8a961325fd1b8d5b92f65418b949;p=ypp-sc-tools.web-live.git diff --git a/yarrg/web/query_commod b/yarrg/web/query_commod index aeee3ce..a4063c1 100644 --- a/yarrg/web/query_commod +++ b/yarrg/web/query_commod @@ -91,35 +91,82 @@ $someresults->(); foreach my $bs (split /_/, $ARGS{BuySell}) { $bs =~ m/^(buy|sell)$/ or die; $bs= $1; + my ($ascdesc) = ($bs eq 'buy') + ? ('DESC') + : ('ASC'); + + my $islands= $dbh->prepare( + "SELECT islandid, archipelago, islandname, sum(qty) as tqty + FROM $bs NATURAL JOIN islands + WHERE commodid = ? + GROUP BY islandid, + ORDER BY archipelago, islandname" + ); - my $sth= $dbh->prepare( - "SELECT archipelago, islandname, stallname, price, qty - FROM $bs NATURAL JOIN stalls NATURAL JOIN islands - WHERE commodid = ?" + my $offers= $dbh->prepare( + "SELECT stallname, price, qty + FROM $bs NATURAL JOIN stalls + WHERE commodid = ? AND islandid = ? + ORDER BY price $ascdesc" ); # fixme this query is utterly wrong - $sth->execute($commodid); - %perl>
+ | Prices + | Quantities available + | ||||||||
---|---|---|---|---|---|---|---|---|---|---|
Archipelago | Island - | Best stall - | Best price - | Qty at best + | Unique best stall + | Best + | Median + | At best + | Within 10% + | Total |
<% $row->{'archipelago'} %> - | <% $row->{'islandname'} %> - | <% $row->{'stallname'} %> - | <% $row->{'price'} %> - | <% $row->{'qty'} %> +% $islands->execute($commodid); +% my $island; +% while ($island= $islands->fetchrow_hashref) { +% my $islandid= $island->{'islandid'}; +% $offers->execute($commodid, $islandid); +% my ($offer, $bestprice, $median, $marginal, @beststalls); +% my $tqty= $island->{'tqty'}; +% my $cqty= 0; +% my $bestqty= 0; +% my $approxqty= 0; +% while ($offer= $offers->fetchrow_hashref) { +% my $price= $offer->{'price'}; +% my $qty= $offer->{'qty'}; +% defined $bestprice or $bestprice= $price; +% if ($price == $bestprice) { +% $bestqty += $qty; +% push @beststalls, $offer->{'stallname'}; +% } +% $cqty += $qty; +% if ($cqty*2 >= $tqty && !defined $median) { +% $median= $price; +% } +% if ($bestprice*9 <= $price*10 and +% $price*10 <= $bestprice*11) { +% $approxqty += $qty; +% } +% } +% my $nstalls= @beststalls; +% $cqty == $tqty or die "$bs $cqty $tqty $commodid $islandid "; + | ||||||
<% $island->{'archipelago'} |h %> + | <% $island->{'islandname'} |h %> + | <% $nstalls==1 ? $beststalls[0] : "$nstalls offers" |h %> + | <% $bestprice %> + | <% $median %> + | <% $bestqty %> + | <% $approxqty %> + | <% $tqty %> |